MySQL基本查询

文章目录

• • 1. 表的增删改查
  • • 1.1 Create
    • • 1.1.1 单行数据 + 全列插入
      • 1.1.2 多行数据 + 指定列插入
      • 1.1.3 插入否则更新
      • 1.1. 4 替换
    • 1.2 Retrieve
    • • 1.2.1 select 列
      • • 1.2.1.1 全列查询
        • 1.2.1.2 指定列查询
        • 1.2.1.3 查询字段为表达式
        • 1.2.1.4 为查询结果指定别名
        • 1.2.1.5 对查询结果去重
      • 1.2.2 where 条件
      • 1.2.3 结果排序
      • 1.2.4 筛选分页结果
    • 1.3 Update
    • 1.4 Delete
    • • 1.4.1 删除数据
      • 1.4.2 截断表
      • 1.4.3 补：redo log、undo log、bin log
    • 1.5 插入查询结果
    • 1.6 聚合函数
    • 1.7 group by
    • 1.8 补：SQL中关键字的执行顺序

1. 表的增删改查

CURD：Create创建, Update更新, Retirve读取, Delete删除

1.1 Create

创建表的语法：

create table [if not exists] table_name (
column1 datatype [constraint],
column2 datatype [constraint],
column3 datatype [constraint],
...
);

• constraint：约束条件，用于限制列的值

案例：

mysql> create table if not exists students(
–> id int unsigned primary key auto_increment comment 'id',
–> stu_num char(20) not null unique comment '学号',
–> name varchar(20) not null default '',
–> qq varchar(20)
–> );
Query OK, 0 rows affected (0.03 sec)

mysql>

向表中插入记录的语法：

insert [into] table_name [(column1, column2, ...)] values (value1, value2, ...);

• 在 table_name 后不加括号和值列表的话，会插入所有列的值，且必须按照表中列的顺序提供值

1.1.1 单行数据 + 全列插入

mysql> insert into students values(100,'1yn001','小明','11223344');
Query OK, 1 row affected (0.02 sec)

mysql> insert into students values(101,'1yn002','小飞','22334455');
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+—–+———+——–+———-+
| id | stu_num | name | qq |
+—–+———+——–+———-+
| 100 | 1yn001 | 小明 | 11223344 |
| 101 | 1yn002 | 小飞 | 22334455 |
+—–+———+——–+———-+
2 rows in set (0.00 sec)
mysql>

1.1.2 多行数据 + 指定列插入

mysql> insert into students(stu_num,name) values('1yn003','小红'),('1yn004','小花');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from students;
+—–+———+——–+———-+
| id | stu_num | name | qq |
+—–+———+——–+———-+
| 100 | 1yn001 | 小明 | 11223344 |
| 101 | 1yn002 | 小飞 | 22334455 |
| 102 | 1yn003 | 小红 | NULL |
| 103 | 1yn004 | 小花 | NULL |
+—–+———+——–+———-+
4 rows in set (0.00 sec)

mysql>

1.1.3 插入否则更新

在插入记录的时候，经常遇到由于主键或者唯一键已经存在而导致插入失败的情况

mysql> insert into students values(100,'1yn005','小林','33445566');
ERROR 1062 (23000): Duplicate entry '100' for key 'students.PRIMARY'

mysql> insert into students values(1004,'1yn004','小刚','33445566');
ERROR 1062 (23000): Duplicate entry '1yn004' for key 'students.stu_num'
mysql>

这种情况可以选择性的进行同步更新操作

insert ... on duplicate key update
column = value(, [column = value]) ...,

mysql> insert into students (id,stu_num,name) values(100,'1yn005','小林')
on duplicate key update stu_num = '1yn005',name = '小林';
Query OK, 2 rows affected (0.00 sec)

mysql> select row_count();
+————-+
| row_count() |
+————-+
| 2 |
+————-+
1 row in set (0.00 sec)

mysql> insert into students (id,stu_num,name) values(100,'1yn005','小林')
on duplicate key update stu_num = '1yn005',name = '小林';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into students (id,stu_num,name) values(104,'1yn001','小明')
on duplicate key update stu_num = '1yn001',name = '小明';
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+—–+———+——–+———-+
| id | stu_num | name | qq |
+—–+———+——–+———-+
| 100 | 1yn005 | 小林 | 11223344 |
| 101 | 1yn002 | 小飞 | 22334455 |
| 102 | 1yn003 | 小红 | NULL |
| 103 | 1yn004 | 小花 | NULL |
| 104 | 1yn001 | 小明 | NULL |
+—–+———+——–+———-+
5 rows in set (0.00 sec)

mysql>

执行完之后，可以通过 row_count() 函数获取受影响的行数，不同受影响行数表示的意义不同

• 0 row affected：表中有冲突数据，但冲突数据的值和 update 的值相等
  • 1 row affected：表中没有数据冲突，数据被插入
  • 2 row affected：表中有冲突数据，不过冲突数据的值与 update 的值不等，并且数据已经被更新

注意该操作只能是插入或更新

1.1. 4 替换

如果主键 或者 唯一键

• 没有冲突，则直接插入
• 如果冲突，则删除后再插入

replace into table_name(c1, c2, ...) values(v1, v2, ...)

mysql> replace into students(stu_num,name) values('1yn005','xiaolin');
Query OK, 2 rows affected (0.01 sec)

mysql> replace into students(stu_num,name) values('1yn006','小艺');
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+—–+———+———+———-+
| id | stu_num | name | qq |
+—–+———+———+———-+
| 101 | 1yn002 | 小飞 | 22334455 |
| 102 | 1yn003 | 小红 | NULL |
| 103 | 1yn004 | 小花 | NULL |
| 104 | 1yn001 | 小明 | NULL |
| 105 | 1yn005 | xiaolin | NULL |
| 106 | 1yn006 | 小艺 | NULL |
+—–+———+———+———-+
6 rows in set (0.00 sec)

mysql>

同样也可以通过 row_count() 函数获取受影响的行数，不同受影响行数表示的意义不同：

• 1 row affected：表中没有数据冲突，数据被插入
• 2 row affected：表中有冲突数据，删除后重新插入

1.2 Retrieve

语法：

select [distinct] column_name [, column_name] ... from table_name [where condition] [order by column_name [asc|desc]] [limit offset, row_count]

案例：

mysql> create table if not exists exam_result(
–> id int unsigned primary key auto_increment,
–> name varchar(20) not null default '',
–> chinese float default 0.0,
–> math float default 0.0,
–> english float default 0.0
–> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into exam_result(name,chinese,math,english) values
–> ('小赵',68,99,57),
–> ('小钱',88,79,78),
–> ('小孙',89,99,91),
–> ('小李',83,85,68),
–> ('小周',56,86,46),
–> ('小吴',71,74,79),
–> ('小郑',76,66,31);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql>

1.2.1 select 列

1.2.1.1 全列查询

通常情况下不建议使用 * 进行全列查询

• 查询的列越多，意味着需要传输的数据量越大
• 可能会影响到索引的使用(后面会详细介绍)

mysql> select * from exam_result;
+—-+——–+———+——+———+
| id | name | chinese | math | english |
+—-+——–+———+——+———+
| 1 | 小赵 | 68 | 99 | 57 |
| 2 | 小钱 | 88 | 79 | 78 |
| 3 | 小孙 | 89 | 99 | 91 |
| 4 | 小李 | 83 | 85 | 68 |
| 5 | 小周 | 56 | 86 | 46 |
| 6 | 小吴 | 71 | 74 | 79 |
| 7 | 小郑 | 76 | 66 | 31 |
+—-+——–+———+——+———+
7 rows in set (0.00 sec)

mysql>

注：

• * 是通配符，表示选择所有列
• 其余常见通配符有：? 表示任意单个字符，_ 表示任意字符串

1.2.1.2 指定列查询

指定列的顺序不需要按照表定义的顺序来查询，可以自由指定

mysql> select id,name,english from exam_result;
+—-+——–+———+
| id | name | english |
+—-+——–+———+
| 1 | 小赵 | 57 |
| 2 | 小钱 | 78 |
| 3 | 小孙 | 91 |
| 4 | 小李 | 68 |
| 5 | 小周 | 46 |
| 6 | 小吴 | 79 |
| 7 | 小郑 | 31 |
+—-+——–+———+
7 rows in set (0.01 sec)

mysql>

1.2.1.3 查询字段为表达式

mysql> select id,name,60 from exam_result;
+—-+——–+—-+
| id | name | 60 |
+—-+——–+—-+
| 1 | 小赵 | 60 |
| 2 | 小钱 | 60 |
| 3 | 小孙 | 60 |
| 4 | 小李 | 60 |
| 5 | 小周 | 60 |
| 6 | 小吴 | 60 |
| 7 | 小郑 | 60 |
+—-+——–+—-+
7 rows in set (0.00 sec)

mysql>

mysql> select id,name,math+5 from exam_result;
+—-+——–+——–+
| id | name | math+5 |
+—-+——–+——–+
| 1 | 小赵 | 104 |
| 2 | 小钱 | 84 |
| 3 | 小孙 | 104 |
| 4 | 小李 | 90 |
| 5 | 小周 | 91 |
| 6 | 小吴 | 79 |
| 7 | 小郑 | 71 |
+—-+——–+——–+
7 rows in set (0.00 sec)

mysql>

mysql> select id,name,chinese+math+english from exam_result;
+—-+——–+———————-+
| id | name | chinese+math+english |
+—-+——–+———————-+
| 1 | 小赵 | 224 |
| 2 | 小钱 | 245 |
| 3 | 小孙 | 279 |
| 4 | 小李 | 236 |
| 5 | 小周 | 188 |
| 6 | 小吴 | 224 |
| 7 | 小郑 | 173 |
+—-+——–+———————-+
7 rows in set (0.00 sec)

mysql>

1.2.1.4 为查询结果指定别名

语法：

select column_name [as] alias_name [...] from table_name;

mysql> select id,name,chinese+math+english as total_score from exam_result;
+—-+——–+————-+
| id | name | total_score |
+—-+——–+————-+
| 1 | 小赵 | 224 |
| 2 | 小钱 | 245 |
| 3 | 小孙 | 279 |
| 4 | 小李 | 236 |
| 5 | 小周 | 188 |
| 6 | 小吴 | 224 |
| 7 | 小郑 | 173 |
+—-+——–+————-+
7 rows in set (0.00 sec)

mysql>

1.2.1.5 对查询结果去重

语法：

select distinct column_name [, column_name] ... from table_name [where condition];

mysql> select math from exam_result;
+——+
| math |
+——+
| 99 |
| 79 |
| 99 |
| 85 |
| 86 |
| 74 |
| 66 |
+——+
7 rows in set (0.00 sec)

mysql> select distinct math from exam_result;
+——+
| math |
+——+
| 99 |
| 79 |
| 85 |
| 86 |
| 74 |
| 66 |
+——+
6 rows in set (0.09 sec)

mysql>

1.2.2 where 条件

在 MySQL 中，有下面的运算符

• 比较运算符

• 逻辑运算符

案例：

• 英语不及格的同学及英语成绩

mysql> select name,english from exam_result where english < 60;
+——–+———+
| name | english |
+——–+———+
| 小赵 | 57 |
| 小周 | 46 |
| 小郑 | 31 |
+——–+———+
3 rows in set (0.00 sec)

mysql>

• 语文成绩在 [80,90] 分的同学及语文成绩

mysql> select name,chinese from exam_result where chinese between 80 and 90;
+——–+———+
| name | chinese |
+——–+———+
| 小钱 | 88 |
| 小孙 | 89 |
| 小李 | 83 |
+——–+———+
3 rows in set (0.00 sec)

mysql> select name,chinese from exam_result where chinese >= 80 and chinese <= 90;
+——–+———+
| name | chinese |
+——–+———+
| 小钱 | 88 |
| 小孙 | 89 |
| 小李 | 83 |
+——–+———+
3 rows in set (0.00 sec)

mysql>

• 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

mysql> select name,math from exam_result where math=58 or math=59 or math=98 or math= 99;
+——–+——+
| name | math |
+——–+——+
| 小赵 | 99 |
| 小孙 | 99 |
+——–+——+
2 rows in set (0.00 sec)

mysql> select name,math from exam_result where math in(58,59,98,99);
+——–+——+
| name | math |
+——–+——+
| 小赵 | 99 |
| 小孙 | 99 |
+——–+——+
2 rows in set (0.00 sec)

mysql>

• 匹配任意多个(包括0个)字符

mysql> select name from exam_result where name like '%孙%';
+——–+
| name |
+——–+
| 小孙 |
+——–+
1 row in set (0.00 sec)

mysql>

• 匹配严格的一个任意字符

mysql> select name from exam_result where name like '_赵';
+——–+
| name |
+——–+
| 小赵 |
+——–+
1 row in set (0.00 sec)

mysql>

• 语文成绩好于英语成绩的同学及其语文和英语成绩

mysql> select name,chinese,english from exam_result where chinese > english;
+——–+———+———+
| name | chinese | english |
+——–+———+———+
| 小赵 | 68 | 57 |
| 小钱 | 88 | 78 |
| 小李 | 83 | 68 |
| 小周 | 56 | 46 |
| 小郑 | 76 | 31 |
+——–+———+———+
5 rows in set (0.00 sec)

mysql>

• 总分在 200 分以下的同学

mysql> select name,chinese+math+english from exam_result where chinese+math+english < 200;
+——–+———————-+
| name | chinese+math+english |
+——–+———————-+
| 小周 | 188 |
| 小郑 | 173 |
+——–+———————-+
2 rows in set (0.00 sec)

mysql> select name,chinese+math+english as total from exam_result where total < 200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
mysql>

可以看到在where条件中是无法使用别名的，这是因为where子句在select子句之前执行，此时别名还未被定义

• 语文成绩 > 80 并且名字中不带 “孙” 的同学

mysql> select name,chinese from exam_result where chinese < 80 and name not like '%孙%';
+——–+———+
| name | chinese |
+——–+———+
| 小赵 | 68 |
| 小周 | 56 |
| 小吴 | 71 |
| 小郑 | 76 |
+——–+———+
4 rows in set (0.00 sec)

mysql>

• 名字中带 “孙” 的同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 75

mysql> select *,chinese+math+english from exam_result where name='%孙%' or (chinese+math+english > 200 and chinese < math and english > 75);
+—-+——–+———+——+———+———————-+
| id | name | chinese | math | english | chinese+math+english |
+—-+——–+———+——+———+———————-+
| 3 | 小孙 | 89 | 99 | 91 | 279 |
| 6 | 小吴 | 71 | 74 | 79 | 224 |
+—-+——–+———+——+———+———————-+
2 rows in set (0.00 sec)

mysql>

• null 的查询

mysql> select * from students;
+—–+———+———-+———-+
| id | stu_num | name | qq |
+—–+———+———-+———-+
| 101 | 1yn002 | 小飞 | 22334455 |
| 102 | 1yn003 | 小红 | NULL |
| 103 | 1yn004 | 小花 | NULL |
| 104 | 1yn001 | 小明 | NULL |
| 109 | 1yn066 | xiaofang | NULL |
+—–+———+———-+———-+
5 rows in set (0.00 sec)

mysql>

• 查询QQ号已知的同学的姓名

mysql> select name from students where qq is not null;
+——–+
| name |
+——–+
| 小飞 |
+——–+
1 row in set (0.00 sec)

mysql>

• null 和 null 的比较，= 和 <=> 的区别
• = 是 null 不安全的
• <=> 是 null 安全的

mysql> select null = null, null = 1, null = 0;
+————-+———-+———-+
| null = null | null = 1 | null = 0 |
+————-+———-+———-+
| NULL | NULL | NULL |
+————-+———-+———-+
1 row in set (0.00 sec)

mysql> select null <=> null, null <=> 1, null <=> 0;
+—————+————+————+
| null <=> null | null <=> 1 | null <=> 0 |
+—————+————+————+
| 1 | 0 | 0 |
+—————+————+————+
1 row in set (0.00 sec)

mysql>

1.2.3 结果排序

语法：

select ... from table_name [where ...] order by column_name [asc|desc], [...]

• asc 全称 Ascending，升序
• desc 全称 Descending，降序

注：没有 order by 字句的查询结果，返回的顺序是未定义的，千万不要依赖于这个顺序

• 查询所有同学的数学成绩，并按照数学成绩升序显示

mysql> select name,math from exam_result order by math asc;
+——–+——+
| name | math |
+——–+——+
| 小郑 | 66 |
| 小吴 | 74 |
| 小钱 | 79 |
| 小李 | 85 |
| 小周 | 86 |
| 小赵 | 99 |
| 小孙 | 99 |
+——–+——+
7 rows in set (0.00 sec)

mysql>

• 同学及QQ号，按QQ号排序显示

mysql> select name,qq from students order by qq;
+———-+———-+
| name | qq |
+———-+———-+
| 小红 | NULL |
| 小花 | NULL |
| 小明 | NULL |
| xiaofang | NULL |
| 小飞 | 22334455 |
+———-+———-+
5 rows in set (0.00 sec)

mysql>

null 被视为比任何值都小

• 同学们各门成绩，依次按照数学降序，英语升序，语文升序的方式显示

mysql> select name,math,english,chinese from exam_result order by math desc,english asc,chinese asc;
+——–+——+———+———+
| name | math | english | chinese |
+——–+——+———+———+
| 小赵 | 99 | 57 | 68 |
| 小孙 | 99 | 91 | 89 |
| 小周 | 86 | 46 | 56 |
| 小李 | 85 | 68 | 83 |
| 小钱 | 79 | 78 | 88 |
| 小吴 | 74 | 79 | 71 |
| 小郑 | 66 | 31 | 76 |
+——–+——+———+———+
7 rows in set (0.00 sec)

mysql>

• 查询同学及总分，按总分由高到低排序

mysql> select name,chinese+math+english as total from exam_result order by total desc;
+——–+——-+
| name | total |
+——–+——-+
| 小孙 | 279 |
| 小钱 | 245 |
| 小李 | 236 |
| 小赵 | 224 |
| 小吴 | 224 |
| 小周 | 188 |
| 小郑 | 173 |
+——–+——-+
7 rows in set (0.00 sec)

mysql>

在 order by 子句中可以使用列别名

• 查询名字中包含 “孙” 的同学或者 包含 “吴” 的同学数学成绩，结果按照数学成绩由高到低显示

mysql> select name,math from exam_result where name like '%孙%' or name like '%吴%' order by math desc;
+——–+——+
| name | math |
+——–+——+
| 小孙 | 99 |
| 小吴 | 74 |
+——–+——+
2 rows in set (0.00 sec)

mysql>

1.2.4 筛选分页结果

语法：

从 0 开始，筛选 n 条结果

select ... from table_name [where ...] [order by ...] limit n

从 s 开始，筛选 n 条结果

select ... from table_name [where ...] [order by ...] limit s,n

从 s 开始，筛选 n 条结果

select ... from table_name [where ...] [order by ...] limit n offset s

当对未知表进行查询时，为避免全表数据过大导致数据库卡死，可以加一条 limit 1

• 对 id 进行分页，每页显示三条记录，分别显示第 1、2、3 页

mysql> select * from exam_result order by id limit 3 offset 0;
+—-+——–+———+——+———+
| id | name | chinese | math | english |
+—-+——–+———+——+———+
| 1 | 小赵 | 68 | 99 | 57 |
| 2 | 小钱 | 88 | 79 | 78 |
| 3 | 小孙 | 89 | 99 | 91 |
+—-+——–+———+——+———+
3 rows in set (0.00 sec)

mysql> select * from exam_result order by id limit 3 offset 3;
+—-+——–+———+——+———+
| id | name | chinese | math | english |
+—-+——–+———+——+———+
| 4 | 小李 | 83 | 85 | 68 |
| 5 | 小周 | 56 | 86 | 46 |
| 6 | 小吴 | 71 | 74 | 79 |
+—-+——–+———+——+———+
3 rows in set (0.00 sec)

mysql> select * from exam_result order by id limit 3 offset 6;
+—-+——–+———+——+———+
| id | name | chinese | math | english |
+—-+——–+———+——+———+
| 7 | 小郑 | 76 | 66 | 31 |
+—-+——–+———+——+———+
1 row in set (0.00 sec)

mysql>

如果结果不足三个，不会有影响

1.3 Update

语法：

update table_name set column = expr [,column = expr ...] [where ...] [order by ...] [limit ....]

对查询到的结果列进行列值更新

案例

• 将名字中含有 “孙” 的同学的数学成绩更新为 90

mysql> select name,math from exam_result where name like '%孙%';
+——–+——+
| name | math |
+——–+——+
| 小孙 | 99 |
+——–+——+
1 row in set (0.00 sec)

mysql> update exam_result set math = 90 where name like '%孙%';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select name,math from exam_result where name like '%孙%';
+——–+——+
| name | math |
+——–+——+
| 小孙 | 90 |
+——–+——+
1 row in set (0.00 sec)

mysql>

• 将 “小吴” 的数学成绩更新为 61分，语文成绩更新为 81 分

mysql> select * from exam_result where name = '小吴';
+—-+——–+———+——+———+
| id | name | chinese | math | english |
+—-+——–+———+——+———+
| 6 | 小吴 | 71 | 74 | 79 |
+—-+——–+———+——+———+
1 row in set (0.00 sec)

mysql> update exam_result set math = 61,chinese = 81 where name = '小吴';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from exam_result where name = '小吴';
+—-+——–+———+——+———+
| id | name | chinese | math | english |
+—-+——–+———+——+———+
| 6 | 小吴 | 81 | 61 | 79 |
+—-+——–+———+——+———+
1 row in set (0.00 sec)

mysql>

• 将总成绩倒数前三的三位同学的数学成绩上加上 10 分

mysql> select name,math,chinese+math+english as total from exam_result order by total asc;
+——–+——+——-+
| name | math | total |
+——–+——+——-+
| 小郑 | 66 | 173 |
| 小周 | 86 | 188 |
| 小吴 | 61 | 221 |
| 小赵 | 99 | 224 |
| 小李 | 85 | 236 |
| 小钱 | 79 | 245 |
| 小孙 | 90 | 270 |
+——–+——+——-+
7 rows in set (0.00 sec)

mysql> update exam_result set math=math+10 order by chinese+math+english asc limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select name,math,chinese+math+english as total from exam_result order by total asc;
+——–+——+——-+
| name | math | total |
+——–+——+——-+
| 小郑 | 76 | 183 |
| 小周 | 96 | 198 |
| 小赵 | 99 | 224 |
| 小吴 | 71 | 231 |
| 小李 | 85 | 236 |
| 小钱 | 79 | 245 |
| 小孙 | 90 | 270 |
+——–+——+——-+
7 rows in set (0.00 sec)

mysql>

MySQL 中不支持 +=、-=、*=、/= 这样的操作符

• 将所有同学的语文成绩更新为原来的两倍

mysql> select name,chinese from exam_result;
+——–+———+
| name | chinese |
+——–+———+
| 小赵 | 68 |
| 小钱 | 88 |
| 小孙 | 89 |
| 小李 | 83 |
| 小周 | 56 |
| 小吴 | 81 |
| 小郑 | 76 |
+——–+———+
7 rows in set (0.00 sec)

mysql> update exam_result set chinese=chinese*2;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7 Changed: 7 Warnings: 0

mysql> select name,chinese from exam_result;
+——–+———+
| name | chinese |
+——–+———+
| 小赵 | 136 |
| 小钱 | 176 |
| 小孙 | 178 |
| 小李 | 166 |
| 小周 | 112 |
| 小吴 | 162 |
| 小郑 | 152 |
+——–+———+
7 rows in set (0.00 sec)

mysql>

更新全表的语句慎用 !

1.4 Delete

1.4.1 删除数据

语法：

delete from table_name [where ...] [order by ...] [limit ....]

案例

• 删除 “小孙” 同学的考试成绩记录

mysql> delete from exam_result where name = '小孙';
Query OK, 1 row affected (0.01 sec)

mysql> select * from exam_result;
+—-+——–+———+——+———+
| id | name | chinese | math | english |
+—-+——–+———+——+———+
| 1 | 小赵 | 68 | 99 | 57 |
| 2 | 小钱 | 88 | 79 | 78 |
| 4 | 小李 | 83 | 85 | 68 |
| 5 | 小周 | 56 | 96 | 46 |
| 6 | 小吴 | 81 | 71 | 79 |
| 7 | 小郑 | 76 | 76 | 31 |
+—-+——–+———+——+———+
6 rows in set (0.00 sec)

mysql>

• 删除整张表数据

mysql> create table if not exists delete_test(
–> id int unsigned primary key auto_increment,
–> name varchar(20)
–> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into delete_test(name) values('A'),('B'),('C'),('D');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from delete_test;
+—-+——+
| id | name |
+—-+——+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+—-+——+
4 rows in set (0.00 sec)

mysql> delete from delete_test;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from delete_test;
Empty set (0.00 sec)

mysql> insert into delete_test(name) values('A'),('B'),('C'),('D');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from delete_test;
+—-+——+
| id | name |
+—-+——+
| 5 | A |
| 6 | B |
| 7 | C |
| 8 | D |
+—-+——+
4 rows in set (0.00 sec)

mysql>

1.4.2 截断表

语法：

truncate [table] table_name

注意：此操作慎用

• 只能对整张表操作，不能像 delete 一样对部分数据操作
• 实际上 MySQL 并不对数据操作，所以比 delete 更快，但是 truncate 在删除数据的时候，并不经过真正的事物，所以无法回滚
• 会重置 auto_increment 选项

mysql> create table if not exists truncate_test(
–> id int unsigned primary key auto_increment,
–> name varchar(20)
–> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into truncate_test(name) values('A'),('B'),('C'),('D');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from truncate_test;
+—-+——+
| id | name |
+—-+——+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
+—-+——+
4 rows in set (0.00 sec)

mysql> truncate truncate_test;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from truncate_test;
Empty set (0.00 sec)

mysql> show create table truncate_test\\G
*************************** 1. row ***************************
Table: truncate_test
Create Table: CREATE TABLE `truncate_test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql>

mysql> insert into truncate_test(name) values('D'),('E'),('F'),('G');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from truncate_test;
+—-+——+
| id | name |
+—-+——+
| 1 | D |
| 2 | E |
| 3 | F |
| 4 | G |
+—-+——+
4 rows in set (0.00 sec)

mysql> show create table truncate_test\\G
*************************** 1. row ***************************
Table: truncate_test
Create Table: CREATE TABLE `truncate_test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql>

• 通过查看表结构可以看到，auto_increment 值已经被重置为 5
• 在截断表的时候，影响的行数是 0，所以实际上并没有对数据真正操作，而是直接清空了表中的所有数据

1.4.3 补：redo log、undo log、bin log

1. redo log（重做日志）

redo log 是 InnoDB 存储引擎特有的日志，用于保证事务的 持久性（Durability）

核心思想：

即使数据库突然宕机，也能把已经提交的事务恢复回来

MySQL 写数据时不会直接写磁盘，而是： 1️⃣ 修改 内存中的数据页（buffer pool） 2️⃣ 先把修改记录写入 redo log 3️⃣ 后续再慢慢刷盘

这样做是因为：

• 顺序写日志比随机写数据页快很多

如果数据库突然崩溃：

• 内存数据丢失
• 但 redo log 还在

数据库启动后会：

根据 redo log 重新执行已提交事务的修改

这个过程叫：crash recovery（崩溃恢复）

2. undo log（回滚日志）

undo log 用于： 1️⃣ 事务回滚（rollback） 2️⃣ MVCC（多版本并发控制）

它记录的是：

数据被修改之前的旧值

例如，原数据 id = 1, name = 张三

执行：

update user set name='李四' where id=1;

undo log 会记录：id=1 原来的 name=张三，如果事务回滚 rollback，数据库就可以根据 undo log 恢复成 id=1, name=张三

undo log 的两个作用

3. bin log（二进制日志）

bin log 是 MySQL Server 层的日志（不是 InnoDB 专属）

主要用途： 1️⃣ 主从复制（Master-Slave Replication） 2️⃣ 数据恢复（point in time recovery）

它记录的是：

所有对数据库产生变化的 SQL 或数据变更

例如：

insert into user values(1,'张三');
update user set name='李四' where id=1;
delete from user where id=1;

这些操作都会写入 bin log

bin log 三种格式

生产环境最常用：row，因为更安全

总结： redo log → 已做的事要能恢复 undo log → 做错的事可以撤销 bin log → 所有操作要被记录下来

4. 数据删除时发生了什么？

假设执行：

delete from user where id=10;

① 写 undo log

• 记录：删除之前的数据
• 作用：事务回滚可以恢复

② 修改数据页（标记删除）

• InnoDB 不会立刻真正删除数据，而是：标记为 delete
• 这样是为了：支持 MVCC

③ 写 redo log

• 记录：删除操作
• 如果数据库崩溃，可以恢复。

④ 写 bin log

• 记录：delete from user where id=10

所以一次 delete 实际会涉及：undo log、redo log、bin log

5. 主从同步（Master-Slave Replication）

主从同步是 MySQL 的 数据复制机制

结构：

主库（Master）
│
bin log
│
▼
从库（Slave）

流程：

• 第一步：主库执行 SQL insert/update/delete，并写入 bin log
• 第二步：从库有一个线程 IO Thread，它会从主库拉取 bin log 并写入本地 relay log（中继日志）
• 第三步：从库的 SQL Thread 中读取 relay log，然后执行相同操作

最终就能够实现：主库数据 → 自动同步到从库

1.5 插入查询结果

语法：

insert into table_name [column [, column...]] select column [, column...] from another_table where condition;

案例：删除表中的重复记录，重复的数据只能有一份

mysql> create table if not exists duplicate_table(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into duplicate_table values
–> (100,'aaaaa'),
–> (100,'aaaaa'),
–> (200,'bbbbb'),
–> (200,'bbbbb'),
–> (300,'ccccc'),
–> (400,'ddddd')
–> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from duplicate_table;
+——+——-+
| id | name |
+——+——-+
| 100 | aaaaa |
| 100 | aaaaa |
| 200 | bbbbb |
| 200 | bbbbb |
| 300 | ccccc |
| 400 | ddddd |
+——+——-+
6 rows in set (0.00 sec)

mysql> create table if not exists no_duplicate_table(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into no_duplicate_table select distinct * from duplicate_table;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from no_duplicate_table;
+——+——-+
| id | name |
+——+——-+
| 100 | aaaaa |
| 200 | bbbbb |
| 300 | ccccc |
| 400 | ddddd |
+——+——-+
4 rows in set (0.00 sec)

mysql> rename table duplicate_table to old_duplicate_table,
–> no_duplicate_table to duplicate_table;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from duplicate_table;
+——+——-+
| id | name |
+——+——-+
| 100 | aaaaa |
| 200 | bbbbb |
| 300 | ccccc |
| 400 | ddddd |
+——+——-+
4 rows in set (0.01 sec)

mysql> select * from old_duplicate_table;
+——+——-+
| id | name |
+——+——-+
| 100 | aaaaa |
| 100 | aaaaa |
| 200 | bbbbb |
| 200 | bbbbb |
| 300 | ccccc |
| 400 | ddddd |
+——+——-+
6 rows in set (0.00 sec)

mysql>

重命名表可以以原子操作进行，确保表名的更改要么完全成功，要么完全失败

1.6 聚合函数

除 count 外，其它聚合函数，如果括号内的字段不是数字，聚合函数将忽略这些值，结果无意义

• 统计班级共有多少同学

mysql> select count(*) as 总人数 from students;
+———–+
| 总人数 |
+———–+
| 5 |
+———–+
1 row in set (0.00 sec)

mysql>

• 统计班级收集的QQ号有多少

mysql> select count(qq) from students;
+———–+
| count(qq) |
+———–+
| 1 |
+———–+
1 row in set (0.00 sec)

mysql>

null 不会纳入结果

• 统计本次考试的数学成绩分数个数

mysql> select count(math) from exam_result;
+————-+
| count(math) |
+————-+
| 6 |
+————-+
1 row in set (0.01 sec)

mysql>

• 统计数学成绩总分

mysql> select sum(math) from exam_result;
+———–+
| sum(math) |
+———–+
| 506 |
+———–+
1 row in set (0.00 sec)

mysql>

• 统计平均总分

mysql> select avg(chinese+math+english) from exam_result;
+—————————+
| avg(chinese+math+english) |
+—————————+
| 219.5 |
+—————————+
1 row in set (0.01 sec)

mysql>

• 返回英语成绩最高分

mysql> select max(english) from exam_result;
+————–+
| max(english) |
+————–+
| 79 |
+————–+
1 row in set (0.01 sec)

mysql>

• 返回 70 分以上的数学最低分

mysql> select min(math) from exam_result where math >= 70;
+———–+
| min(math) |
+———–+
| 71 |
+———–+
1 row in set (0.00 sec)

mysql>

1.7 group by

在查询的时候，可以使用 group by 子句对指定列进行分组查询

select column1,column2,... from table_name group by column1,column2,...

案例：

创建一个雇员信息表(来自 Oracle 9i 的经典测试表)，文末附带 SQL 文件内容

• EMP 员工表

• DEPT 部门表

• SALGRADE 工资等级表

• 显示每个部门的平均工资和最高工资

mysql> select deptno,avg(sal) 平均工资,max(sal) 最高工资 from emp group by deptno;
+——–+————–+————–+
| deptno | 平均工资 | 最高工资 |
+——–+————–+————–+
| 20 | 2175.000000 | 3000.00 |
| 30 | 1566.666667 | 2850.00 |
| 10 | 2916.666667 | 5000.00 |
+——–+————–+————–+
3 rows in set (0.00 sec)

mysql>

• 显示每个部门的每种岗位的平均工资和最低工资

mysql> select deptno,job,avg(sal) 平均工资,min(sal) 最低工资 from emp group by deptno,job order by deptno;
+——–+———–+————–+————–+
| deptno | job | 平均工资 | 最低工资 |
+——–+———–+————–+————–+
| 10 | CLERK | 1300.000000 | 1300.00 |
| 10 | MANAGER | 2450.000000 | 2450.00 |
| 10 | PRESIDENT | 5000.000000 | 5000.00 |
| 20 | ANALYST | 3000.000000 | 3000.00 |
| 20 | CLERK | 950.000000 | 800.00 |
| 20 | MANAGER | 2975.000000 | 2975.00 |
| 30 | CLERK | 950.000000 | 950.00 |
| 30 | MANAGER | 2850.000000 | 2850.00 |
| 30 | SALESMAN | 1400.000000 | 1250.00 |
+——–+———–+————–+————–+
9 rows in set (0.00 sec)

mysql>

• 显示平均工资低于 2000 的部门和其平均工资

mysql> select deptno,avg(sal) 平均工资 from emp group by deptno;
+——–+————–+
| deptno | 平均工资 |
+——–+————–+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+——–+————–+
3 rows in set (0.00 sec)

mysql> select deptno,avg(sal) 平均工资 from emp group by deptno where avg(sal) < 2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where avg(sal) < 2000' at line 1
mysql> select deptno,avg(sal) 平均工资 from emp group by deptno having avg(sal) < 2000;
+——–+————–+
| deptno | 平均工资 |
+——–+————–+
| 30 | 1566.666667 |
+——–+————–+
1 row in set (0.00 sec)

mysql>

补：

在 SQL 的世界里，“表” 的概念应该是广义的、分层次的。我们不应该仅仅将磁盘上真实存在的物理表视为表，而应该认识到：SQL 执行的每一个阶段产生的中间结果，本质上都是逻辑上的虚拟表

从物理表开始，经过 where 筛选得到原始表的子集，经过 group by 分组形成新的数据结构，经过聚合函数计算生成统计结果，再到 having 对聚合结果进行筛选，每一步操作都是将一个虚拟表转换为另一个虚拟表

where 和 having 的本质是相同的 —— 都是对表进行条件筛选，区别仅在于它们所操作的表不同

• where 操作的是原始数据表，而 having 操作的是分组聚合后的表

建立 “一切皆表” 的思维模式非常重要：后面我们对各种 SQL 操作的理解都可以用其作为指导思想

• 子查询是在虚拟表上的再次查询
• 视图是持久化的虚拟表
• 显示的结果是最终的虚拟表

输入一张或多张表，经过一系列关系运算，输出一张新表

1.8 补：SQL中关键字的执行顺序

在 SQL 查询的执行过程中，关键字的执行顺序是：

理解上述执行顺序之后，再来看之前上面出现过的几个问题

一、where 无法使用聚合函数

例如：

select deptno, avg(sal)
from emp
where avg(sal) > 2000
group by deptno;

这条 SQL 是 错误的

原因在于执行顺序：

当 SQL 执行到 where 时：

where avg(sal) > 2000

此时 avg(sal) 还没有被计算出来，因为聚合函数是在 group by 之后 才计算的

所以：

where avg(sal) ...

在逻辑上是 不存在的数据，因此 SQL 不允许这样写

正确写法：

select deptno, AVG(sal)
from emp
group by deptno
having avg(sal) > 2000;

having 是在 group by 之后执行的，此时聚合函数已经计算完成，因此可以使用

注意，where、having 和 group by 可以同时出现，只是 where 不能使用聚合函数，比如说：

mysql> select deptno,job,avg(sal) myavg from emp where ename != 'SMITH' group by deptno,job having myavg < 2000;
+——–+———-+————-+
| deptno | job | myavg |
+——–+———-+————-+
| 30 | SALESMAN | 1400.000000 |
| 20 | CLERK | 1100.000000 |
| 30 | CLERK | 950.000000 |
| 10 | CLERK | 1300.000000 |
+——–+———-+————-+
4 rows in set (0.00 sec)

mysql>

二、为什么在 where 中无法使用别名，在 order by 中却可以使用呢？

答案同样是关键字的执行顺序

• where 字句是要对原始数据进行筛选，而别名是将筛选后的数据起别名，数据都还没筛选出来，更别说别名了
• order by 字句是对结果进行排序，排序的前提是已经有合适的数据，而数据早都已经被起好别名了，order by 直接就可以使用

scott.sql：

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
`dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
`loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
`ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
`hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
`sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);

DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(11) DEFAULT NULL COMMENT '等级',
`losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
`hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);

insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);