MySQL基础（用的贼鸡儿多）

2022-11-15 07:40:25

整理有点乱，业余也玩玩系统，经常碰见这些玩意，有点烦，老是记不住

MySQL 基础语法

一、连接 MYSQL
格式： mysql -h 主机地址 -u 用户名 -p 用户密码、
1、连接到本机上的 MYSQL。
在Linux终端键入命令 mysql -uroot -p，回车后提示你输密码，如果刚安装好MYSQL，终极用户 root 是没有密码的，故直接回车即可进入到 MYSQL 中了，MYSQL 的提示符是：mysql>

2、连接到远程主机上的 MYSQL。假设远程主机的 IP 为：10.1.9.231，用户名为 root,密码为 123456。则键入以下命令：
mysql -h10.1.9.231 -uroot -p123456
（注:u 和 root 可以不用加空格，也可以加空格）
help show 显示允许的show 语句
mysql>show grants;用来显示授予用户的安全权限

3、退出 MYSQL 命令： exit （回车）

二、修改密码（Linux环境中）
格式：mysqladmin -u 用户名 -p 旧密码 password 新密码 ( 注：因为开始时 root 没有密码，所以-p 旧密码一项就能省略了。)
1、将 root 的密码改为 abcdefg。mysqladmin -uroot -p123456 password abcdefg

三、增加新用户及基础命令（注意：和上面不同，下面的因为是 MYSQL 环境中的命令，所以后面都带一个分号作为命令结束符）
格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"

MySQL>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypassword'WITHGRANT OPTION
予任何主机访问数据的权限

1、增加一个用户 jira 密码为 password，让他能在所有主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以 root 用户连入 MYSQL，然后键入以下命令：
grant select,insert,update,delete on *.* to jira@"%" Identified by "password";
但1中增加的用户是十分危险的，你想如某个人知道 jira 的密码，那么他就能在internet 上的任何一台计算机上登录你的 mysql 数据库并对你的数据能为所欲为了，解决办法见例 2。

2、增加一个用户 test 密码为 abc,让他只能在 localhost 上登录，并能对数据库 mysql进行查询、插入、修改、删除的操作（localhost 指本地主机，即 MYSQL 数据库所在的那台主机），这样用户即使用知道 test 的密码，他也无法从 internet 上直接访问数据库，只能通过 MYSQL 主机上的 web 页来访问了。
grant select,insert,update,delete on *.* to test@localhost identified by "abc";
如果你不想 test 有密码，能再打一个命令将密码消掉。
grant select,insert,update,delete on *.* to test@localhost identified by "";

3、下面来看看 MYSQL 中有关数据库方面的操作。注意：必须首先登录到 MYSQL 中，以下操作都是在 MYSQL 的提示符下进行的，而且每个命令以分号结束。

在 mysql>提示符下输入 quit 能随时退出交互操作界面：mysql> quit

Bye
你也能用 control-D 退出。

5、多行语句
一条命令能分成多行输入，直到出现分号“；”为止：
mysql> select
-> USER()
-> ,
-> now()
->;
+--------------------+---------------------+
| USER() | now() |
+--------------------+---------------------+
| ODBC@localhost | 2001-05-17 22:59:15 |
+--------------------+---------------------+
1 row in set (0.06 sec)
mysql>
注意中间的逗号和最后的分号的使用方法。

6、一行多命令
输入如下命令：
mysql> SELECT USER(); SELECT NOW();
+------------------+
| USER() |
+------------------+
| ODBC@localhost |
+------------------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2001-05-17 23:06:15 |
+---------------------+
1 row in set (0.00 sec)
mysql>
注意中间的分号，命令之间用分号隔开。

8、选择数据库并显示当前选择的数据库
mysql> USE mysql
Database changed
mysql>
(USE 和 QUIT 命令不必分号结束。）
mysql> select database();
+---------------+
| database()|
+---------------+
| mysql |
+---------------+
1 row in set (0.00 sec)

9、显示当前数据库中存在的表
mysql> SHOW TABLES;

10、显示表的内容
mysql>select * from mysql;
myslq>select * from mysql limit 5；查询前五行
mysql>select * from mysql limit 5, 5; limit带一个值表示从第一行开始，带两个值表示从指定的行号为第一个值开始

这是一些最常用的最基本的操作命令，通过多次练习就能牢牢掌捂了。

2、创建一个数据库 lsh
mysql> CREATE DATABASE lsh;
注意不同操作系统对大小写的敏感。

3、选择你所创建的数据库
mysql> USE lsh
Database changed
此时你已进入你刚才所建立的数据库 lsh.

4、创建一个数据库表
首先看目前你的数据库中存在什么表：
mysql> SHOW TABLES;
Empty set (0.00 sec)
说明刚才建立的数据库中还没有数据库表。下面来创建一个数据库表 mytable,我们要建立一个你公司员工的生日表，表的内容包含员工姓名、性别、出生日期、出生城市。
mysql> CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1),birth DATE, birthaddr VARCHAR(20));
Query OK, 0 rows affected (0.00 sec)
由于 name、birthadd 的列值是变化的，因此选择 VARCHAR，其长度不一定是 20。能选择从1 到 255 的所有长度，如果以后需要改动他的字长，能使用 ALTER TABLE 语句。性别只需一个字符就能表示："m"或"f"，因此选用 CHAR(1);birth 列则使用 DATE 数据类型。
创建了一个表后，我们能看看刚才做的结果，用 SHOW TABLES 显示数据库中有哪些表：
mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| mytable|
+---------------------+
mysql>SHOW COLUMNS FROM xiaohua；

6、往表中加入记录
我们先用 SELECT 命令来查看表中的数据：
mysql> select * from mytable;
Empty set (0.00 sec)
这说明刚才创建的表还没有记录。
加入一条新记录：
mysql> insert into mytable values (’abccs’,’f’,’1977-07-07’,’china’);
Query OK, 1 row affected (0.05 sec)
再用上面的 SELECT 命令看看发生了什么变化。我们能按此方法一条一条地将所有员工的记录加入到表中。

7、用文本方式将数据装入一个数据库表
如果一条一条地输入，非常麻烦。我们能用文本文件的方式将所有记录加入你的数据库表中。创建一个文本文件“mysql.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以在CREATE TABLE 语句中列出的列次序给出，例如：
abccs f 1977-07-07 china
mary f 1978-12-12 usa
tom m 1970-09-02 usa

8、从数据库表中检索信息实际上，前面我们已用到了 SELECT 语句，他用来从数据库表中检索信息。select 语句格式一般为：
SELECT 检索关键词 FROM 被检索的表 WHERE 检索条件(可选)
以前所使用的"* "表示选择所有的列。下面继续使用我们在上篇文章中创建的表 mytable：
9、查询所有数据：
mysql> select * from mytable;
+----------+------+------------+----------+
| name | sex | birth | birthaddr |
+----------+------+------------+--------+
| abccs|f| 1977-07-07 | china |
| mary |f| 1978-12-12 | usa |
| tom |m| 1970-09-02 | usa |
+----------+------+------------+----------+
3 row in set (0.00 sec)
10、通配符（%：表示任何字符出现任意次数 _：表示匹配任意字符但是只能匹配一个）

11、修正错误记录：
如果 tom 的出生日期有错误，应该是 1973－09－02，则能用 update 语句来修正：
mysql> update mytable set birth = "1973-09-02" where name = "tom";
再用 9 中的语句看看是否已更正过来。

12、选择特定行
上面修改了 tom 的出生日期，我们能选择 tom 这一行来看看是否已有了变化：
mysql> select * from mytable where name = "tom";
+--------+------+------------+------------+
| name |sex | birth | birthaddr |
+--------+------+------------+------------+
| tom|m| 1973-09-02 | usa|
+--------+------+------------+------------+
1 row in set (0.06 sec)
上面 WHERE 的参数指定了检索条件。我们还能用组合条件来进行查询：
mysql> SELECT * FROM mytable WHERE sex = "f" AND birthaddr = "china";
+--------+------+------------+------------+
| name |sex | birth | birthaddr |
+--------+------+------------+------------+
| abccs |f| 1977-07-07 | china |
+--------+------+------------+------------+
1 row in set (0.06 sec)

14、对行进行排序我们能对表中的记录按生日大小进行排序：（默认是升序排列）
mysql> SELECT name, birth FROM mytable ORDER BY birth;
+----------+------------+
| name | birth |
+----------+------------+
| tom | 1973-09-02 |
| abccs| 1977-07-07 |
| mary | 1978-12-12 |
+----------+------------+
3 row in set (0.00 sec)
我们能用 DESC 来进行逆序排序：
mysql> SELECT name, birth FROM mytable ORDER BY birth DESC;
+----------+------------+
| name | birth |
+----------+------------+
| mary | 1978-12-12 |
| abccs| 1977-07-07 |
| tom | 1973-09-02 |
+----------+------------+
3 row in set (0.00 sec)
15、行计数
数据库经常要统计一些数据，如表中员工的数目，我们就要用到行计数函数 COUNT()。
COUNT()函数用于对非 NULL 结果的记录进行计数：
mysql> SELECT COUNT(*) FROM mytable;
+----------+
| COUNT(*) |
+----------+
|3 |
+----------+
1 row in set (0.06 sec)
员工中男女数量：
mysql> SELECT sex, COUNT(*) FROM mytable GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| f|2 |
| m|1 |
+------+----------+
2 row in set (0.00 sec)
注意我们使用了 GROUP BY 对 SEX 进行了分组。

五、多表简单查询
1、查看第一个表 mytable 的内容：
mysql> select * from mytable;
+----------+------+------------+-----------+
| name | sex | birth | birthaddr |
+----------+------+------------+-----------+
| abccs|f | 1977-07-07 | china |
| mary |f | 1978-12-12 | usa |
| tom |m | 1970-09-02 | usa |
+----------+------+------------+-----------+

2、创建第二个表 title（包括作者、文章标题、发表日期）:
mysql> create table title(writer varchar(20) not null, title varchar(40) not null,senddate date);
向该表中填加记录，最后表的内容如下：
mysql> select * from title;
+--------+-------+------------+
| writer | title | senddate |
+--------+-------+------------+
| abccs | a1| 2000-01-23 |
| mary | b1| 1998-03-21 |
| abccs | a2| 2000-12-04 |
| tom| c1| 1992-05-16 |
| tom| c2| 1999-12-12 |
+--------+-------+------------+
5 rows in set (0.00sec)

3、多表查询
目前我们有了两个表: mytable 和 title。利用这两个表我们能进行组合查询：例如我们要查询作者 abccs 的姓名、性别、文章：
mysql> SELECT name,sex,title FROM mytable,title WHERE name=writer AND name=’abccs’;
+-------+------+-------+
| name | sex | title |
+-------+------+-------+
| abccs | f| a1|
| abccs | f| a2|
+-------+------+-------+
上面例子中，由于作者姓名、性别、文章记录在两个不同表内，因此必须使用组合来进行查询。必须要指定一个表中的记录怎么和其他表中的记录进行匹配。注意：如果第二个表 title 中的 writer 列也取名为 name（和 mytable 表中的 name 列相同）而不是 writer 时，就必须用 mytable.name 和 title.name 表示，以示差别。

有时我们要对数据库表和数据库进行修改和删除，能用如下方法实现：
4、增加一列：
如在前面例子中的 mytable 表中增加一列表示是否单身 single:
mysql> alter table mytable add column single char(1);

5、修改记录
将 abccs 的 single 记录修改为“y”：
mysql> update mytable set single=’y’ where name=’title’;
目前来看看发生了什么：
mysql> select * from mytable;
+----------+------+------------+-----------+--------+
| name | sex | birth | birthaddr | single |
+----------+------+------------+-----------+--------+
| abccs|f | 1977-07-07 | china | y |
| mary |f | 1978-12-12 | usa | NULL |
| tom |m | 1970-09-02 | usa | NULL |
+----------+------+------------+-----------+--------+

6、增加记录
前面已讲过怎么增加一条记录，为便于查看，重复和此：
mysql> insert into mytable values (’abc’,’f’,’1966-08-17’,’china’,’n’);
Query OK, 1 row affected (0.05 sec)
查看一下：
mysql> select * from mytable;
+----------+------+------------+-----------+--------+
| name | sex | birth | birthaddr | single |
+----------+------+------------+-----------+--------+
| abccs|f | 1977-07-07 | china | y |
| mary |f | 1978-12-12 | usa | NULL |
| tom |m | 1970-09-02 | usa | NULL |
| abc |f | 1966-08-17 | china | n |
+----------+------+------------+-----------+--------+

7、修改表中字段长度
如： title表里的title 字段原来长度是 100个字符，现长度要改成130个字符
alter table title modify column title varchar(130);

更新某一行中的一个列
在表中增加一列的语句格式为：
ALTER TABLE 表名字 ADD COLUMN 列名字数据类型约束;
或：
ALTER TABLE 表名字 ADD 列名字数据类型约束;
例如：ALTER TABLE employee ADD height INT(4) DEFAULT 170;
新增加的列，被默认放置在这张表的最右边。如果要把增加的列插入在指定位置，则需要在语句的最后使用AFTER关键词(“AFTER 列1” 表示新增的列被放置在 “列1” 的后面)。

删除表中的一列和刚才使用的新增一列的语句格式十分相似，只是把关键词 ADD 改为 DROP ，语句后面不需要有数据类型、约束或位置信息。具体语句格式：
ALTER TABLE 表名字 DROP COLUMN 列名字;
或： ALTER TABLE 表名字 DROP 列名字;

我们把刚才新增的 test 删除：
ALTER TABLE employee DROP test;

要修改一列的数据类型，除了使用刚才的 CHANGE 语句外，还可以用这样的 MODIFY 语句：

ALTER TABLE 表名字 MODIFY 列名字新数据类型;

8、修改表中某一列值（）
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 原值

修改表中某些值（值为随机数）
update url set name= floor(1 + rand()*200)
大多数时候我们需要做修改的不会是整个数据库或整张表，而是表中的某一个或几个数据，这就需要我们用下面这条命令达到精确的修改：

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
比如，我们要把 Tom 的 age 改为 21，salary 改为 3000：

UPDATE employee SET age=21,salary=3000 WHERE name='Tom';

9、删除记录
用如下命令删除表中的一条记录：
mysql> delete from mytable where name=’abc’;
delete from mytabel 删除表中所有数据
DELETE 从表中删除满足由 where 给出的条件的一条记录。再显示一下结果：
mysql> select * from mytable;
+----------+------+------------+-----------+--------+
| name | sex | birth | birthaddr | single |
+----------+------+------------+-----------+--------+
| abccs|f | 1977-07-07 | china | y |
| mary |f | 1978-12-12 | usa | NULL |
| tom |m | 1970-09-02 | usa | NULL |
+----------+------+------------+-----------+--------+

10、删除表：
mysql> drop table ****(表 1 的名字)，***表 2 的名字;
能删除一个或多个表，小心使用。

11、数据库的删除：
mysql> drop database 数据库名;
小心使用。

12、数据库的导出：
mysqldump -u 用户名 -p 数据库名表名> 导出的文件名

13、导出一个数据库结构
命令：mysqldump -u root -p -d --add-drop-table mysql >test_db.sql
-d 没有数据 --add-drop-table 在每个create 语句之前增加一个drop table
[root@TS-DEV bin]# ./mysqldump -uroot -p -d --add-drop-table mysql > /tmp/david/david_db.sql
Enter password:

14、导入数据库

使用mysql 命令先创建要导入的数据库 renee。

[root@TS-DEV bin]# ./mysql -uroot -p -D renee < /tmp/david/david_myclass.sql
Enter password:

15、重命名一张表
RENAME TABLE 原名 TO 新名字;

ALTER TABLE 原名 RENAME 新名;

ALTER TABLE 原名 RENAME TO 新名;

16、索引
索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。
当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录。这样做会消耗大量数据库系统时间，并造成大量磁盘 I/O 操作。

而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度。

对一张表中的某个列建立索引，有以下两种语句格式：

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);

CREATE INDEX 索引名 ON 表名字 (列名);
我们用这两种语句分别建立索引：

ALTER TABLE employee ADD INDEX idx_id (id); #在employee表的id列上建立名为idx_id的索引

CREATE INDEX idx_name ON employee (name); #在employee表的name列上建立名为idx_name的索引
这里我们使用命令 SHOW INDEX FROM 表名字; 查看刚才新建的索引：

在使用 SELECT 语句查询的时候，语句中 WHERE 里面的条件，会自动判断有没有可用的索引。
比如有一个用户表，它拥有用户名(username)和个人签名(note)两个字段。其中用户名具有唯一性，并且格式具有较强的限制，我们给用户名加上一个唯一索引；个性签名格式多变，而且允许不同用户使用重复的签名，不加任何索引。
这时候，如果你要查找某一用户，使用语句 select * from user where username=? 和 select * from user where note=? 性能是有很大差距的，对建立了索引的用户名进行条件查询会比没有索引的个性签名条件查询快几倍，在数据量大的时候，这个差距只会更大。
一些字段不适合创建索引，比如性别，这个字段存在大量的重复记录无法享受索引带来的速度加成，甚至会拖累数据库，导致数据冗余和额外的 CPU 开销。

17、视图
视图是从一个或多个表中导出来的表，是一种虚拟存在的表。它就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。
注意理解视图是虚拟的表：
数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；
视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；
在使用视图的时候，可以把它当作一张表。
创建视图的语句格式为：

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;
可见创建视图的语句，后半句是一个 SELECT 查询语句，所以视图也可以建立在多张表上，只需在 SELECT 语句中使用子查询或连接查询，这些在之前的实验已经进行过。
现在我们创建一个简单的视图，名为 v_emp，包含v_name，v_age，v_phone三个列：
CREATE VIEW v_emp (v_name,v_age,v_phone) AS SELECT name,age,phone FROM employee;

18、导入

此处是导入一个纯数据文件，该文件中将包含与数据表字段相对应的多条数据，这样可以快速导入大量数据，除此之外，还有用 SQL 语句的导入方式，语法为：source *.sql 这是实验中经常用到的。两者之间的不同是：数据文件导入方式只包含数据，导入规则由数据库系统完成；SQL 文件导入相当于执行该文件中包含的 SQL 语句，可以实现多种操作，包括删除，更新，新增，甚至对数据库的重建。

数据文件导入，可以把一个文件里的数据保存进一张表。导入语句格式为：

LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字;
现在 SQL6 目录下有一个名为 in.txt 的文件，我们尝试把这个文件中的数据导入数据库 mysql_shiyan 的 employee 表中。

由于导入导出大量数据都属于敏感操作，根据 mysql 的安全策略，导入导出的文件都必须在指定的路径下进行，在 mysql 终端中查看路径变量：

注意到 secure_file_priv 变量指定安全路径为 /var/lib/mysql-files/ ，要导入数据文件，需要将该文件移动到安全路径下

现在执行导入语句，文件中的数据成功导入 employee 表：

19、导出
导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中。导出语句基本格式为：

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;
注意：语句中 “文件路径” 之下不能已经有同名文件。

现在我们把整个 employee 表的数据导出到 /var/lib/mysql-files/ 目录下，导出文件命名为 out.txt 具体语句为：

SELECT * INTO OUTFILE '/var/lib/mysql-files/out.txt' FROM employee;

报错的原因是mysql指定了导出的路径/var/lib/mysql-files/
mysqldump 是 MySQL 用于备份数据库的实用程序。它主要产生一个 SQL 脚本文件，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

20、备份
使用 mysqldump 备份的语句：

mysqldump -u root -p 数据库名>备份文件名; #备份整个数据库

mysqldump -u root 数据库名表名字>备份文件名; #备份整个表
mysqldump 是一个备份工具，因此该命令是在终端中执行的，而不是在 mysql 交互环境下

21、恢复
mysql -u root -p test < bak.sql

减少列输出（对于重复的列，减少输出列）
mysql> SELECT DISTINCT owner FROM pet;

22、扩展

模式匹配
SQL模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
要想找出正好包含5个字符的名字，使用“_”模式字符
SELECT * FROM pet WHERE name LIKE '——————————';

扩展正则表达式的一些字符是：
‘.’匹配任何单个的字符。
字符类“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
“ *”匹配零个或多个在它前面的东西。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配的任何数量的数字，而“.*”匹配任何数量的任何东西。
如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。为了说明扩展正则表达式如何工作，下面使用REGEXP重写上面所示的LIKE查询：
为了找出以“b”开头的名字，使用“^”匹配名字的开始：
mysql> SELECT * FROM pet WHERE name REGEXP '^b';

如果你想强制使REGEXP比较区分大小写，使用BINARY关键字使其中一个字符串变为二进制字符串。该查询只匹配名称首字母的小写‘b’。
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

为了找出以“fy”结尾的名字，使用“$”匹配名字的结尾：
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

为了找出包含一个“w”的名字，使用以下查询：
mysql> SELECT * FROM pet WHERE name REGEXP 'w';

既然如果一个正则表达式出现在值的任何地方，他就会被模式匹配，就不必在先前的查询中在模式的两侧放置一个通配符以使得它匹配整个值，就像你使用了一个SQL模式那样。
为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间：
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

你也可以使用“{n}”重复n次操作符,重写前面的查询：
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

码农公寓

相关文章