MySQL DDL 、DML、DQL语句

DDL：数据定义语言

注意：对表操作的时候需要进入到对应的数据库里面去。
创建表：CREATE TABLE [IF NOT EXISTS] 'tbl_name' ( 字段1 修饰符, col2 字段2 修饰符, ...)

例如：创建一张名为stu1的表，表中包含的字段有id，name和age，id的数据类型是int，且是主键并且自动增长。

1. mysql> create table stu1 (id int primary key auto_increment,name varchar(20) not null, age tinyint unsigned);
2. Query OK, 0 rows affected (0.06 sec)
4. mysql> show tables;
5. +-------------------+
6. | Tables_in_student |
7. +-------------------+
8. | stu1              |
9. +-------------------+
10. 1 row in set (0.00 sec)

复制代码

查看表

• 查看表列表
  
• 查看创建表的命令
  
• 查看表结构（字段）信息
  
• 查看表属性信息
  

查看表：show tables [from db_name]

注意：不加db_name，默认查看的是当前数据库里面的所有表。

1. mysql> show tables from student;
2. +-------------------+
3. | Tables_in_student |
4. +-------------------+
5. | stu1              |
6. +-------------------+
7. 1 row in set (0.00 sec)

复制代码

查看创建表的命令：SHOW CREATE TABLE tbl_name

1. mysql> show create table stu1 \G
2. *************************** 1. row ***************************
3.        Table: stu1
4. Create Table: CREATE TABLE `stu1` (
5.   `id` int(11) NOT NULL AUTO_INCREMENT,
6.   `name` varchar(20) NOT NULL,
7.   `age` tinyint(3) unsigned DEFAULT NULL,
8.   PRIMARY KEY (`id`)
9. ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
10. 1 row in set (0.00 sec)

复制代码

查看表的结构(字段)信息：

• desc tb_name
  
• SHOW COLUMNS FROM [db_name.]tb_name
  

1. mysql> desc stu1;
2. +-------+---------------------+------+-----+---------+----------------+
3. | Field | Type                | Null | Key | Default | Extra          |
4. +-------+---------------------+------+-----+---------+----------------+
5. | id    | int(11)             | NO   | PRI | NULL    | auto_increment |
6. | name  | varchar(20)         | NO   |     | NULL    |                |
7. | age   | tinyint(3) unsigned | YES  |     | NULL    |                |
8. +-------+---------------------+------+-----+---------+----------------+
9. 3 rows in set (0.00 sec)

复制代码

查看表的属性信息：show table status like 'tb_name'

注意：mysq客户端的ego--(\G)命令可以垂直显示结果

1. *************************** 1. row ***************************
2.            Name: stu1
3.          Engine: InnoDB
4.         Version: 10
5.      Row_format: Compact
6.            Rows: 6
7. Avg_row_length: 2730
8.     Data_length: 16384
9. Max_data_length: 0
10.    Index_length: 0
11.       Data_free: 0
12. Auto_increment: 10
13.     Create_time: 2022-09-09 00:56:44
14.     Update_time: NULL
15.      Check_time: NULL
16.       Collation: utf8_general_ci
17.        Checksum: NULL
18. Create_options:
19.         Comment:
20. 1 row in set (0.00 sec)

复制代码

修改表：

1. #修改表名
2. ALTER TABLE students RENAME s1;
4. #添加字段
5. ALTER TABLE s1 ADD phone varchar(11) AFTER name;
7. #修改字段类型
8. ALTER TABLE s1 MODIFY phone int;
10. #修改字段名称和类型
11. ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
13. #删除字段
14. ALTER TABLE s1 DROP COLUMN mobile;
16. #修改字符集
17. ALTER TABLE s1 character set utf8;
19. #修改数据类型和字符集
20. ALTER TABLE s1 change name name varchar(20) character set utf8;
22. #添加字段
23. ALTER TABLE students ADD gender ENUM('m','f');
25. alter table student modify is_del bool default false;
28. #修改字段名和类型
29. ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
31. #删除字段
32. ALTER TABLE students DROP age;
34. #查看表结构
35. DESC students;
37. #新建表无主键，添加和删除主键
38. CREATE TABLE t1 SELECT * FROM students;
40. ALTER TABLE t1 add primary key (stuid);
42. ALTER TABLE t1 drop primary key ;
45. #添加外键
46. ALTER TABLE students add foreign key(TeacherID) references teachers(tid);
48. #删除外键
49. SHOW CREATE TABLE students
51. #查看外键名
52. ALTER TABLE students drop foreign key <外键名>;

复制代码

DML:数据操纵语言（insert、updete、delete）

注意：设计字符类型的数据类型，进行操作的时候要加上引号（单双都可以）
INSERT 语句：insert tb_name(col1...coln) values （value1...valuen）

例如：

1. mysql> insert stu1(name,age) values('tom',10);
2. Query OK, 1 row affected (0.01 sec)

复制代码

全值插入：不指定col

1. mysql> insert stu1 values(3,'BOB',20);
2. Query OK, 1 row affected (0.00 sec)

复制代码

UPDATE 语句

注意：使用update语句的时候需要指定限制条件，不然将修改所有行的指定字段

1. mysql> update stu1 set name='bob' where name='BOB';
2. Query OK, 2 rows affected (0.00 sec)
3. Rows matched: 2  Changed: 2  Warnings: 0
5. mysql> select * from stu1;
6. +----+------+------+
7. | id | name | age  |
8. +----+------+------+
9. |  1 | tom  |   10 |
10. |  2 | bob  |   20 |
11. |  3 | bob  |   20 |
12. +----+------+------+
13. 3 rows in set (0.00 sec)

复制代码

可以通过在配置文件指定选项来避免这个错误。

1. [root@centos8 ~]#vim /etc/my.cnf
2. [mysql]
3. safe-updates

复制代码

删除指定的记录：

1. ysql> delete from stu1 where id=3 ;
2. Query OK, 1 row affected (1.68 sec)
4. mysql> select * from stu1 ;
5. +----+------+------+
6. | id | name | age  |
7. +----+------+------+
8. |  1 | tom  |   10 |
9. |  2 | bob  |   20 |
10. +----+------+------+
11. 2 rows in set (0.00 sec)

复制代码

删除数据： delete from tb_name where 限制条件

注意：不加限制条件会清空表里面的所有数据。

1. mysql> delete from stu1 where  id=5;
2. Query OK, 1 row affected (0.00 sec)
4. mysql> select * from stu1;
5. +----+--------+------+
6. | id | name   | age  |
7. +----+--------+------+
8. |  1 | tom    |   10 |
9. |  2 | bob    |   20 |
10. |  4 | bob2   |   22 |
11. |  6 | bob5=3 |   25 |
12. |  7 | bob5   |   26 |
13. |  8 | tom2   | NULL |
14. +----+--------+------+
15. 6 rows in set (0.00 sec)

复制代码

清空数据表，保留表结构的方法；

• delete from tb_name（不会缩减数据文件的大小）
  
• TRUNCATE TABLE tbl_name（会自动缩减数据文件的大小）
  
• 缩减表的大小：OPTIMIZE TABLE tb_name
  

DQL：数据查询语言（select）

select查询

• 单表操作
  
• 多表操作
  

针对单表操作：

简单查询：select 需要查询得字段  from tb_name  where 限制条件

• 指定字段别名
  

范例：字段显示的时候使用别名

1. mysql> select id as '编号',name as '名字',age as '年龄' from stu1;
2. +--------+--------+--------+
3. | 编号   | 名字   | 年龄   |
4. +--------+--------+--------+
5. |      1 | tom    |     10 |
6. |      2 | bob    |     20 |
7. |      3 | bob    |     20 |
8. +--------+--------+--------+
9. 3 rows in set (0.00 sec)

复制代码

• select可以实现加减乘除运算
  

1. mysql> select 1+2+3*4-5+9;
2. +-------------+
3. | 1+2+3*4-5+9 |
4. +-------------+
5. |          19 |
6. +-------------+
7. 1 row in set (0.00 sec)

复制代码

• select可以实现比较的操作（大于、小于、等于等）
  

1. mysql> select 1>99;
2. +------+
3. | 1>99 |
4. +------+
5. |    0 |
6. +------+
7. 1 row in set (0.00 sec)
9. mysql> select 1<99;
10. +------+
11. | 1<99 |
12. +------+
13. |    1 |
14. +------+
15. 1 row in set (0.00 sec)

复制代码

• 空查询: IS NULL, IS NOT NULL
  

1. mysql> select * from stu1 where age  between 20 and 25;
2. +----+--------+------+
3. | id | name   | age  |
4. +----+--------+------+
5. |  2 | bob    |   20 |
6. |  4 | bob2   |   22 |
7. |  5 | bob2=3 |   24 |
8. |  6 | bob5=3 |   25 |
9. +----+--------+------+
10. 4 rows in set (0.00 sec)

复制代码

• DISTINCT： 去除重复行
  

1. mysql> select * from stu1 where age in(10,20,25);
2. +----+--------+------+
3. | id | name   | age  |
4. +----+--------+------+
5. |  1 | tom    |   10 |
6. |  2 | bob    |   20 |
7. |  6 | bob5=3 |   25 |
8. +----+--------+------+
9. 3 rows in set (0.00 sec)

复制代码

• like： 模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符
  

1. mysql> select * from stu1 where age is null;
2. +----+------+------+
3. | id | name | age  |
4. +----+------+------+
5. |  8 | tom2 | NULL |
6. +----+------+------+
7. 1 row in set (0.01 sec)
9. mysql> select * from stu1 where age is NOT null;
10. +----+--------+------+
11. | id | name   | age  |
12. +----+--------+------+
13. |  1 | tom    |   10 |
14. |  2 | bob    |   20 |
15. |  4 | bob2   |   22 |
16. |  5 | bob2=3 |   24 |
17. |  6 | bob5=3 |   25 |
18. |  7 | bob5   |   26 |
19. +----+--------+------+
20. 6 rows in set (0.00 sec)

复制代码

• 逻辑操作符：NOT，AND，OR，XOR
  

group by：根据指定的条件把查询结果进行"分组"以用于做"聚合"运算

group by通常结合聚合函数来使用。常用聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计
注意：

• 一旦对表进行分组以后，select后面的字段要么是聚合函数要么就是分组的字段。
  
• group by（分组后）的后面加条件必须用having
  
• gtoup by（分组前）的前面加条件可以用where
  

例如：按照姓名来进行分组，统计每个姓名都有多少人。

1. mysql> select * from stu1;
2. +----+--------+------+
3. | id | name   | age  |
4. +----+--------+------+
5. |  1 | tom    |   10 |
6. |  2 | bob    |   20 |
7. |  4 | bob2   |   22 |
8. |  5 | bob2=3 |   24 |
9. |  6 | bob5=3 |   25 |
10. |  7 | bob5   |   26 |
11. |  8 | tom2   | NULL |
12. |  9 | bob    |   20 |
13. +----+--------+------+
14. 8 rows in set (0.00 sec)
16. mysql> select distinct * from stu1;
17. +----+--------+------+
18. | id | name   | age  |
19. +----+--------+------+
20. |  1 | tom    |   10 |
21. |  2 | bob    |   20 |
22. |  4 | bob2   |   22 |
23. |  5 | bob2=3 |   24 |
24. |  6 | bob5=3 |   25 |
25. |  7 | bob5   |   26 |
26. |  8 | tom2   | NULL |
27. |  9 | bob    |   20 |
28. +----+--------+------+
29. 8 rows in set (0.00 sec)

复制代码

ORDER BY: 根据指定的字段对查询结果进行排序

• 升序：ASC
  
• 降序：DESC
  

1. #like 后面的字符需要用引号括起来，可以是单引号，也可以是双引号
2. mysql> SELECT * from stu1 where age like '1%';
3. +----+------+------+
4. | id | name | age  |
5. +----+------+------+
6. |  1 | tom  |   10 |
7. +----+------+------+
8. 1 row in set (0.00 sec)

复制代码

LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count行,offset默为值为0

例如：limit 3，5表示的就是跳过前三个，只显示五条记录。实现分页显示。

1. mysql> select * from stu1;
2. +----+--------+------+
3. | id | name   | age  |
4. +----+--------+------+
5. |  1 | tom    |   10 |
6. |  2 | bob    |   20 |
7. |  4 | bob2   |   22 |
8. |  5 | bob2=3 |   24 |
9. |  6 | bob5=3 |   25 |
10. |  7 | bob5   |   26 |
11. |  8 | tom2   | NULL |
12. |  9 | bob    |   20 |
13. +----+--------+------+
14. 8 rows in set (0.00 sec)
16. mysql> select name , count(*) from stu1 group by name;
17. +--------+----------+
18. | name   | count(*) |
19. +--------+----------+
20. | bob    |        2 |
21. | bob2   |        1 |
22. | bob2=3 |        1 |
23. | bob5   |        1 |
24. | bob5=3 |        1 |
25. | tom    |        1 |
26. | tom2   |        1 |
27. +--------+----------+

复制代码

例如：显示年龄最小的五个（会自动去掉重复的）

1. mysql> select * from stu1 order by age desc;
2. +----+--------+------+
3. | id | name   | age  |
4. +----+--------+------+
5. |  7 | bob5   |   26 |
6. |  6 | bob5=3 |   25 |
7. |  5 | bob2=3 |   24 |
8. |  4 | bob2   |   22 |
9. |  2 | bob    |   20 |
10. |  9 | bob    |   20 |
11. |  1 | tom    |   10 |
12. |  8 | tom2   | NULL |
13. +----+--------+------+
14. 8 rows in set (0.00 sec)
16. mysql> select * from stu1 order by age asc;
17. +----+--------+------+
18. | id | name   | age  |
19. +----+--------+------+
20. |  8 | tom2   | NULL |
21. |  1 | tom    |   10 |
22. |  2 | bob    |   20 |
23. |  9 | bob    |   20 |
24. |  4 | bob2   |   22 |
25. |  5 | bob2=3 |   24 |
26. |  6 | bob5=3 |   25 |
27. |  7 | bob5   |   26 |
28. +----+--------+------+
29. 8 rows in set (0.00 sec)

复制代码

多表查询：查询的结果来自于多张表。

多表查询的方法：

• 子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
  
• 联合查询：UNION  ，两张表纵向合并形成一个大表
  
• 交叉连接：笛卡尔乘积 CROSS JOIN ，横向连接，把第一个表的每条记录都和第二张表进行组合，从而形成一个大表
  
• 内连接：取两张表得交集（都符合条件得那一部分）
  
• 外连接：outer inner
  左外连接：左边表的全部内容+交集部分，FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
  右外连接：右边表的全部内容+交集部分，FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
  

子查询：一个查询结果作为另一个查询的条件。

例如：

1. mysql> select * from stu1;
2. +----+---------+------+
3. | id | name    | age  |
4. +----+---------+------+
5. |  1 | tom     |   10 |
6. |  2 | bob     |   20 |
7. |  4 | bob2    |   22 |
8. |  6 | bob5=3  |   25 |
9. |  7 | bob5    |   26 |
10. |  8 | tom2    | NULL |
11. | 10 | liyi    |   25 |
12. | 11 | lier    |   26 |
13. | 12 | zhangwu |   22 |
14. | 13 | xiaosi  |   30 |
15. | 14 | wuad    |   40 |
16. +----+---------+------+
17. 11 rows in set (0.00 sec)
19. mysql> select * from stu1 limit 3,5;
20. +----+--------+------+
21. | id | name   | age  |
22. +----+--------+------+
23. |  6 | bob5=3 |   25 |
24. |  7 | bob5   |   26 |
25. |  8 | tom2   | NULL |
26. | 10 | liyi   |   25 |
27. | 11 | lier   |   26 |
28. +----+--------+------+
29. 5 rows in set (0.00 sec)

复制代码

union：联合查询 ，将两张表纵向合并，合成一个新的大表

前提：

• 字段（列）需要保持一致。
  
• 数据类型要匹配
  

1. mysql> select * from stu1  order by age;
2. +----+--------+------+
3. | id | name   | age  |
4. +----+--------+------+
5. |  8 | tom2   | NULL |
6. |  1 | tom    |   10 |
7. |  2 | bob    |   20 |
8. |  9 | bob    |   20 |
9. |  4 | bob2   |   22 |
10. |  5 | bob2=3 |   24 |
11. |  6 | bob5=3 |   25 |
12. |  7 | bob5   |   26 |
13. +----+--------+------+
14. 8 rows in set (0.00 sec)
16. mysql> select * from stu1  order by age limit 5;
17. +----+------+------+
18. | id | name | age  |
19. +----+------+------+
20. |  8 | tom2 | NULL |
21. |  1 | tom  |   10 |
22. |  2 | bob  |   20 |
23. |  9 | bob  |   20 |
24. |  4 | bob2 |   22 |
25. +----+------+------+
26. 5 rows in set (0.00 sec)

复制代码

交叉连接: cross join (横向笛卡尔积)

• 横向合并：把第一个表的每条记录都和第二张表进行组合，从而形成一个大表。（笛卡尔乘积）
  
• 最终生成的记录数：A表的记录数*B表的记录数
  

注意：交叉连接慎用，同意造成数据库死机

1. mysql> select * from stu1 where age >(select avg(age) from stu1);
2. +----+--------+------+
3. | id | name   | age  |
4. +----+--------+------+
5. |  4 | bob2   |   22 |
6. |  5 | bob2=3 |   24 |
7. |  6 | bob5=3 |   25 |
8. |  7 | bob5   |   26 |
9. +----+--------+------+
10. 4 rows in set (0.00 sec)

复制代码

内连接：inner join 取两张表横向合并交集（两张表都符合条件的部分）

注意：内连接的条件要使用on来进行连接。

1. mysql> select * from stu1 union select * from teach;
2. +----+-----------+------+
3. | id | name      | age  |
4. +----+-----------+------+
5. |  1 | tom       |   10 |
6. |  2 | bob       |   20 |
7. |  4 | bob2      |   22 |
8. |  5 | bob2=3    |   24 |
9. |  6 | bob5=3    |   25 |
10. |  7 | bob5      |   26 |
11. |  8 | tom2      | NULL |
12. |  1 | zhang san |   40 |
13. |  2 | li si     |   45 |
14. |  3 | wang wu   |   46 |
15. +----+-----------+------+
16. 10 rows in set (0.00 sec)

复制代码

外连接：outer join

• 左外连接：left join
  
• 右外连接：right join
  

左外连接：left join 左边表的全部内容+交集部分

1. mysql> select * from stu1
2.     -> cross join
3.     -> teach;
4. +----+--------+------+-----+-----------+------+
5. | id | name   | age  | TID | NAME      | age  |
6. +----+--------+------+-----+-----------+------+
7. |  1 | tom    |   10 |   1 | zhang san |   40 |
8. |  1 | tom    |   10 |   2 | li si     |   45 |
9. |  1 | tom    |   10 |   3 | wang wu   |   46 |
10. |  2 | bob    |   20 |   1 | zhang san |   40 |
11. |  2 | bob    |   20 |   2 | li si     |   45 |
12. |  2 | bob    |   20 |   3 | wang wu   |   46 |
13. |  4 | bob2   |   22 |   1 | zhang san |   40 |
14. |  4 | bob2   |   22 |   2 | li si     |   45 |
15. |  4 | bob2   |   22 |   3 | wang wu   |   46 |
16. |  5 | bob2=3 |   24 |   1 | zhang san |   40 |
17. |  5 | bob2=3 |   24 |   2 | li si     |   45 |
18. |  5 | bob2=3 |   24 |   3 | wang wu   |   46 |
19. |  6 | bob5=3 |   25 |   1 | zhang san |   40 |
20. |  6 | bob5=3 |   25 |   2 | li si     |   45 |
21. |  6 | bob5=3 |   25 |   3 | wang wu   |   46 |
22. |  7 | bob5   |   26 |   1 | zhang san |   40 |
23. |  7 | bob5   |   26 |   2 | li si     |   45 |
24. |  7 | bob5   |   26 |   3 | wang wu   |   46 |
25. |  8 | tom2   | NULL |   1 | zhang san |   40 |
26. |  8 | tom2   | NULL |   2 | li si     |   45 |
27. |  8 | tom2   | NULL |   3 | wang wu   |   46 |
28. +----+--------+------+-----+-----------+------+
29. 21 rows in set (0.00 sec)

复制代码

右外连接： right join 右边表的全部内容+交集部分

1. mysql> select * from stu1 inner join teach on stu1.id=teach.TID;
2. +----+------+------+-----+-----------+------+
3. | id | name | age  | TID | NAME      | age  |
4. +----+------+------+-----+-----------+------+
5. |  1 | tom  |   10 |   1 | zhang san |   40 |
6. |  2 | bob  |   20 |   2 | li si     |   45 |
7. +----+------+------+-----+-----------+------+
8. 2 rows in set (0.00 sec)
10. #挑选对应想要的字段
11. mysql> select stu1.id,stu1.name,teach.name  from stu1 inner join teach on stu1.id=teach.TID;
12. +----+------+-----------+
13. | id | name | name      |
14. +----+------+-----------+
15. |  1 | tom  | zhang san |
16. |  2 | bob  | li si     |
17. +----+------+-----------+
18. 2 rows in set (0.00 sec)
21. 对表起别名：直接在表名后面加别名
22. mysql> select s.id,s.name,t.name  from stu1 s inner join teach t on s.id=t.TID;
23. +----+------+-----------+
24. | id | name | name      |
25. +----+------+-----------+
26. |  1 | tom  | zhang san |
27. |  2 | bob  | li si     |
28. +----+------+-----------+
29. 2 rows in set (0.00 sec)

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！