【MySQL】基本查询

前言
1. 插入
- 1.1 指定列插入
- 1.2 全列插入
- 1.3 插入否则更新
- 1.4 替换
2. 查询
- 2.1 select
- - 2.1.1 全列查询
  - 2.1.2 指定列查询
  - 2.1.3 表达式查询
  - 2.1.4 结果去重
  - 2.1.5 插入查询结果
- 2.2 where
- - 2.2.1 比较和逻辑运算符
  - 2.2.2 条件查询
- 2.3 order by
- 2.4 分页显示
3. 修改
- 3.1 update
4. 删除
- 4.1 delete
- 4.2 truncate
5. 聚合函数
- 5.1 group by
6. 关于sql的执行顺序

前言

本篇主要介绍的是表内容的增删查改，而之前了解的表的相关操作主要是对表结构本身的操作，而非内容

增删查改主要体现在如下四个操作CRUD：Create（创建）、Retrieve（读取）、Update（更新）、Delete（删除）

1. 插入

语法如下：

insert [into] table_name [(colunm[, column], ...)] values (value_list)[, (value_list), ...];value_list: value1[, value2, ...]
[]: 表示括号内的可以省略

创建示例表：

mysql> create table stu(-> id int unsigned primary key auto_increment, -> stu_num int unsigned unique key,-> name varchar(10) not null,-> );
Query OK, 0 rows affected (0.14 sec)mysql> desc stu;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int unsigned | NO   | PRI | NULL    | auto_increment |
| stu_num | int unsigned | YES  | UNI | NULL    |                |
| name    | varchar(10)  | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

后续操作基于该表

1.1 指定列插入

插入一行：

mysql> insert into stu (stu_num, name) values (1001, '钢铁侠');
Query OK, 1 row affected (0.02 sec)
# values左边括号里指定要插入的列名称
# 右边指定要插入的数据
# 两边的数量和数据类型要保持一致，否则报错

插入多行：

mysql> insert stu (stu_num, name) values (1004, '浩克'), -> (1005, '黑寡妇');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
# 中间用逗号隔开即可

1.2 全列插入

插入一行：

mysql> insert into stu values (2, 1002, '美队');
Query OK, 1 row affected (0.00 sec)
# 省略左边的括号即全列插入
# 因此右边括号里的数据的顺序、数量和类型要与列的顺序、数量和属性一一对应# 这种写法等价于全列插入
mysql> insert into stu (id, stu_num, name) values (3, 1003, '雷神');
Query OK, 1 row affected (0.03 sec)
# 全部列指定

插入多行：

mysql> insert stu values (6, 1006, '鹰眼'), -> (7, 1007, '战争机器');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

1.3 插入否则更新

插入的数据可以与主键或者唯一键冲突，如果冲突就更新为新插入的值

当前表的内容：

mysql> select * from stu;
+----+---------+--------------+
| id | stu_num | name         |
+----+---------+--------------+
|  1 |    1001 | 钢铁侠       |
|  2 |    1002 | 美队         |
|  3 |    1003 | 雷神         |
|  4 |    1004 | 浩克         |
|  5 |    1005 | 黑寡妇       |
|  6 |    1006 | 鹰眼         |
|  7 |    1007 | 战争机器     |
+----+---------+--------------+
7 rows in set (0.00 sec)

插入一条记录：

mysql> insert stu values (7, 1008, '冬兵');
ERROR 1062 (23000): Duplicate entry '7' for key 'stu.PRIMARY'
# id是主键，值为7的记录已经存在所以报错

如果就是想更新成新的数据，语法如下：

insert ... on dupplicate key update column=value[, ...];mysql> insert stu values (7, 1008, '冬兵') on duplicate key update stu_num=1008, name='冬兵';
Query OK, 2 rows affected (0.01 sec)
# id列是自增长可以不设置
# 实际上语法允许后面的新数据可以完全与前面insert所插入的数据不同
# 只要保证后面新更新的数据满足约束即可
# 不过逻辑上还是要保持前后数据一致，不然就容易引起混淆
# 唯一键冲突也是一样的	# 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
# 1 row affected: 表中没有冲突数据，数据被插入
# 2 row affected: 表中有冲突数据，并且数据已经被更新

通过如下函数可以获取最近一次操作所影响的行：

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

1.4 替换

作用与上面的更新相同，而且写法更简单，也就是把insert换成replace即可完成不存在就插入，冲突就替换：

mysql> replace stu values (7, 1007, '战争机器');
Query OK, 2 rows affected (0.01 sec)
# 1 row affected: 表中没有冲突数据，数据被插入
# 2 row affected: 表中有冲突数据，删除后重新插入

2. 查询

2.1 select

查询最常用的语句就是select，其中包含大量的选项和关键字，语法如下：

select[distinct] {* | {column [, column] ...}[from table_name][where...][order by column [ASC | DESC], ...]limit...
*: 全列查询
from: 表示查询哪张表
distinct: 结果去重
order by: 排序

先创建一个示例表并插入测试数据：

mysql> create table exam(-> id int unsigned primary key auto_increment,-> name varchar(10) not null,-> chinese float unsigned default 0.0,-> math float unsigned default 0.0,-> english float unsigned default 0.0-> );
Query OK, 0 rows affected, 3 warnings (0.06 sec)mysql> desc exam;
+---------+----------------+------+-----+---------+----------------+
| Field   | Type           | Null | Key | Default | Extra          |
+---------+----------------+------+-----+---------+----------------+
| id      | int unsigned   | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10)    | NO   |     | NULL    |                |
| chinese | float unsigned | YES  |     | 0       |                |
| math    | float unsigned | YES  |     | 0       |                |
| english | float unsigned | YES  |     | 0       |                |
+---------+----------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)mysql> insert exam (name, chinese, math, english) values-> ('唐三藏', 67, 98, 56),-> ('孙悟空', 87, 78, 77),-> ('猪悟能', 88, 98, 90),-> ('曹孟德', 82, 84, 67),-> ('刘玄德', 55, 85, 45),-> ('孙权', 70, 73, 78),-> ('宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

2.1.1 全列查询

查询所有列所有行信息：

mysql> select * from exam;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
|  6 | 孙权      |      70 |   73 |      78 |
|  7 | 宋公明    |      75 |   65 |      30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)

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

查询的列越多，意味着需要传输的数据量越大
可能会影响到索引的使用，相当于暴力遍历所有数据

2.1.2 指定列查询

只想查询id和名字那两列：

mysql> select id, name from exam;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 唐三藏    |
|  2 | 孙悟空    |
|  3 | 猪悟能    |
|  4 | 曹孟德    |
|  5 | 刘玄德    |
|  6 | 孙权      |
|  7 | 宋公明    |
+----+-----------+
7 rows in set (0.00 sec)
# 多列之间用逗号隔开
# 查询列的顺序可以随意

2.1.3 表达式查询

select比较特殊，后面可以接子句也可以接表达式，比如：

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)mysql> select name, 1+1 from exam;
+-----------+-----+
| name      | 1+1 |
+-----------+-----+
| 唐三藏    |   2 |
| 孙悟空    |   2 |
| 猪悟能    |   2 |
| 曹孟德    |   2 |
| 刘玄德    |   2 |
| 孙权      |   2 |
| 宋公明    |   2 |
+-----------+-----+
7 rows in set (0.00 sec)

给math列+10分：

mysql> select math+10 from exam;
+---------+
| math+10 |
+---------+
|     108 |
|      88 |
|     108 |
|      94 |
|      95 |
|      83 |
|      75 |
+---------+
7 rows in set (0.00 sec)

还可以列出表达式计算出三门课的总分：

mysql> select chinese, math, english, math+chinese+english from exam;
+---------+------+---------+----------------------+
| chinese | math | english | math+chinese+english |
+---------+------+---------+----------------------+
|      67 |   98 |      56 |                  221 |
|      87 |   78 |      77 |                  242 |
|      88 |   98 |      90 |                  276 |
|      82 |   84 |      67 |                  233 |
|      55 |   85 |      45 |                  185 |
|      70 |   73 |      78 |                  221 |
|      75 |   65 |      30 |                  170 |
+---------+------+---------+----------------------+
7 rows in set (0.00 sec)

如果觉得表达式列名太长可以后接as xxx进行重命名：

mysql> select chinese, math, english, math+chinese+english as total from exam;
+---------+------+---------+-------+
| chinese | math | english | total |
+---------+------+---------+-------+
|      67 |   98 |      56 |   221 |
|      87 |   78 |      77 |   242 |
|      88 |   98 |      90 |   276 |
|      82 |   84 |      67 |   233 |
|      55 |   85 |      45 |   185 |
|      70 |   73 |      78 |   221 |
|      75 |   65 |      30 |   170 |
+---------+------+---------+-------+
7 rows in set (0.00 sec)# as可以省略，另外所有列后面都可以重命名
mysql> select chinese 语文, math 属性, english 英语, math+chinese+english 总分 from exam;
+--------+--------+--------+--------+
| 语文   | 属性   | 英语   | 总分   |
+--------+--------+--------+--------+
|     67 |     98 |     56 |    221 |
|     87 |     78 |     77 |    242 |
|     88 |     98 |     90 |    276 |
|     82 |     84 |     67 |    233 |
|     55 |     85 |     45 |    185 |
|     70 |     73 |     78 |    221 |
|     75 |     65 |     30 |    170 |
+--------+--------+--------+--------+
7 rows in set (0.00 sec)

2.1.4 结果去重

在select后加上distinct：

# 去重前:
mysql> select math from exam;
+------+
| math |
+------+
|   98 |
|   78 |
|   98 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
7 rows in set (0.00 sec)
# 去重后:
mysql> select distinct  math from exam;
+------+
| math |
+------+
|   98 |
|   78 |
|   84 |
|   85 |
|   73 |
|   65 |
+------+
6 rows in set (0.02 sec)

2.1.5 插入查询结果

insert和select组合使用，语法如下：

insert into table_name [(column [, column ...])] select...

示例表：

mysql> create table dup_tab ( id int not null );
Query OK, 0 rows affected (0.07 sec)mysql> insert dup_tab values (1), (1), (2), (2), (3), (3);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> select * from dup_tab;
+----+
| id |
+----+
|  1 |
|  1 |
|  2 |
|  2 |
|  3 |
|  3 |
+----+
6 rows in set (0.00 sec)

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

# 创建一个与前表结构相同的表
mysql> create table no_dup_tab like dup_tab;
Query OK, 0 rows affected (0.06 sec)
# 把查询到去重后的记录插入表中
# 这里是全列插入，也可以按列插入
mysql> insert into no_dup_tab -> select distinct * from  dup_tab;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from no_dup_tab;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)
# 由于需求要的是dup_tab中没有重复的数据
# 所以需要把no_dup_tab重命名成dup_tab
# 把原来的dup_tab随便改成其它不冲突的名字
mysql> rename table dup_tab to other, no_dup_tab to dup_tab;
Query OK, 0 rows affected (0.10 sec)mysql> select * from dup_tab;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

2.2 where

大多数情况下并不是无脑的直接把整列整列的数据取出来，而是需要带上条件，只有满足条件才进行显示，也就说按条件筛选会影响记录的行数或者说条数

2.2.1 比较和逻辑运算符

比较运算符：

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，null不安全，null参与任何运算的结果都是null，即使是null=null
<=>	等于，NULL 安全，null <=> null的结果是 true(1)
!=, <>	不等于
between a and b	范围匹配，[a, b]，如果 a <= value <= b，返回 true(1)
in (option, …)	如果是 option 中的任意一个，返回 true(1)
xx is null	xx是null
xx is not null	xx不是null
(列) like	模糊匹配，% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：

运算符	说明
and	多个条件必须都为 true(1)，结果才是 true(1)
or	任意一个条件为 true(1), 结果为 true(1)
not	条件为 true(1)，结果为 false(0)

2.2.2 条件查询

查询所有英语成绩不及格的同学及其英语成绩：

mysql> select name, english from exam -> where english < 60;
+-----------+---------+
| name      | english |
+-----------+---------+
| 唐三藏    |      56 |
| 刘玄德    |      45 |
| 宋公明    |      30 |
+-----------+---------+
3 rows in set (0.00 sec)

语文成绩在80~90之间的同学以及语文成绩：

mysql> select name, chinese from exam -> where chinese between 80 and 90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)mysql> select name, chinese from exam -> where chinese >= 80 and chinese <= 90;
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 孙悟空    |      87 |
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
3 rows in set (0.00 sec)

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

mysql> select name, math from exam -> where math in (58, 59, 98, 99);
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)mysql> select name, math from exam -> where math = 58-> or math = 59-> or math = 98-> or math = 99;
+-----------+------+
| name      | math |
+-----------+------+
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
2 rows in set (0.00 sec)

姓孙的同学及孙某同学：

mysql> select name from exam where name like '孙%';
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 孙权      |
+-----------+
2 rows in set (0.00 sec)mysql> select name from exam where name like '孙_';
+--------+
| name   |
+--------+
| 孙权   |
+--------+
1 row in set (0.00 sec)

语文成绩好于英语成绩的同学：

mysql> select name, chinese, english from exam-> where chinese > english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 唐三藏    |      67 |      56 |
| 孙悟空    |      87 |      77 |
| 曹孟德    |      82 |      67 |
| 刘玄德    |      55 |      45 |
| 宋公明    |      75 |      30 |
+-----------+---------+---------+
5 rows in set (0.00 sec)

总分在 200 分以下的同学：

mysql> select *, chinese + math + english total from exam -> where (chinese + math + english) < 200;
+----+-----------+---------+------+---------+-------+
| id | name      | chinese | math | english | total |
+----+-----------+---------+------+---------+-------+
|  5 | 刘玄德    |      55 |   85 |      45 |   185 |
|  7 | 宋公明    |      75 |   65 |      30 |   170 |
+----+-----------+---------+------+---------+-------+
2 rows in set (0.00 sec)
# where子句后也可以接表达式
# 注意where后面不能使用列别名
# 因为where的执行顺序先于select

如果表达式很复杂可以把相关条件用括号括起来，表示一个单元

语文成绩 > 80 并且不姓孙的同学：

mysql> select name, chinese from exam-> where chinese > 80 and name not like '孙%';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| 猪悟能    |      88 |
| 曹孟德    |      82 |
+-----------+---------+
2 rows in set (0.01 sec)
# not name like '孙%';
# 顺序调换一下也可以

孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80：

mysql> select *, chinese + math + english total -> from exam -> where (name like '孙_') -> or (chinese + math + english > 200  -> and chinese < math -> and english > 80);
+----+-----------+---------+------+---------+-------+
| id | name      | chinese | math | english | total |
+----+-----------+---------+------+---------+-------+
|  3 | 猪悟能    |      88 |   98 |      90 |   276 |
|  6 | 孙权      |      70 |   73 |      78 |   221 |
+----+-----------+---------+------+---------+-------+
2 rows in set (0.00 sec)

查询语文成绩为空的同学：

mysql> select name from exam where chinese is null;
+--------+
| name   |
+--------+
| 刘备   |
+--------+
1 row in set (0.00 sec)

2.3 order by

如果不加排序，那么select查询出来的记录的顺序是随机的，但实际上可以通过order by子句对查找出来的记录按照某一列进行排序，升序或降序

语法：

# asc 为升序（从小到大）
# desc为降序（从大到小）
# 默认为 asc
select... from table_name [where...]
order by column [asc|desc], [...];

同学及数学成绩，按数学成绩升序显示：

mysql> select name, math-> from exam-> order by math asc;
+-----------+------+
| name      | math |
+-----------+------+
| 宋公明    |   65 |
| 孙权      |   73 |
| 孙悟空    |   78 |
| 曹孟德    |   84 |
| 刘玄德    |   85 |
| 唐三藏    |   98 |
| 猪悟能    |   98 |
+-----------+------+
8 rows in set (0.01 sec)
# 降序只需要把asc换成desc

null默认是最小的

查询同学各门成绩，依次按数学降序，语文升序，英语升序的方式显示：

mysql> select name, math, chinese, english from exam-> order by math desc, chinese asc, english asc;
+-----------+------+---------+---------+
| name      | math | chinese | english |
+-----------+------+---------+---------+
| 唐三藏    |   98 |      67 |      56 |
| 猪悟能    |   98 |      88 |      90 |
| 刘玄德    |   85 |      55 |      45 |
| 曹孟德    |   84 |      82 |      67 |
| 孙悟空    |   78 |      87 |      77 |
| 孙权      |   73 |      70 |      78 |
| 宋公明    |   65 |      75 |      30 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)

要注意的是多字段排序时，排序的优先级从左往右依次降低，换句话说当最左边的排序规则无法比较出谁大谁小时，也就是相等的情况，这时按照第二个排序规则进行排序以此类推，否则就按照当前规则进行排序，后面就不看了

查询同学及总分，由高到低：

mysql> select name, chinese + math + english total -> from exam -> order by total desc;
+-----------+-------+
| name      | total |
+-----------+-------+
| 猪悟能    |   276 |
| 孙悟空    |   242 |
| 曹孟德    |   233 |
| 唐三藏    |   221 |
| 孙权      |   221 |
| 刘玄德    |   185 |
| 宋公明    |   170 |
+-----------+-------+
7 rows in set (0.02 sec)
# where后面不能使用别名而order by可以是因为order的优先级比select低
# 也就是select的执行在order by之前，所以能用

查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示：

mysql> select name, math-> from exam-> where (name like '孙%') or (name like '曹%')-> order by math desc;
+-----------+------+
| name      | math |
+-----------+------+
| 曹孟德    |   84 |
| 孙悟空    |   78 |
| 孙权      |   73 |
+-----------+------+
3 rows in set (0.00 sec)

2.4 分页显示

可以认为表中的每条记录都有一个下标，下标从0开始，如果想按行数查看表中的记录可以通过limit来指定一次显示多少行，这种一次查询显示表中的一部分记录的行为就叫做分页

除了分页显示也可以分页修改和删除，取决于使用在哪个子句中

比如一次选择查看3条记录：

mysql> select * from exam limit 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 唐三藏    |      67 |   98 |      56 |
|  2 | 孙悟空    |      87 |   78 |      77 |
|  3 | 猪悟能    |      88 |   98 |      90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)

如果只带一个参数，意味着从表头开始也就是0号下标开始向后查看n条连续的记录，如果想选择指定的下标开始可以使用如下写法：

mysql> select * from exam limit 2, 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  3 | 猪悟能    |      88 |   98 |      90 |
|  4 | 曹孟德    |      82 |   84 |      67 |
|  5 | 刘玄德    |      55 |   85 |      45 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
# 等价于下面这种写法
# limit 3 offset 2;# 最开始带一个参数的，其实可以认为前面省略了一个0

从2号下标也就是第3行开始向后查询3行

分页查看的目的是为了提高查询效率和用户体验，如果表中的记录很多一次查看完毕可能会导致数据库服务器卡顿甚至卡死，导致用户体验变差，所以为了避免这种情况建议在查表时进行分页查看

3. 修改

3.1 update

如果想修改表中已经插入的数据，可以使用update语句，该语句允许更改一个或多个表中的行的一个或多个列的值

基本语法如下：

update table_name  
set column1 = value1, column2 = value2, ...  
where...
order by...value: 可以是表达式

它是对查询到的结果列值进行更新，所以尽量要带where筛选出指定的列进行更新，否则就是对整表更新

将孙悟空的数学更新成80分：

mysql> update exam-> set math=80-> where name='孙悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分：

mysql> update exam -> set math=60, chinese=70-> where name='曹孟德';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

将总成绩倒数前三的 3 位同学的数学成绩加上 30 分：

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

把所有同学的语文成绩变成原来的2倍：

# 不带where子句相当于整表更新 
mysql> update exam-> set chinese = chinese * 2;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

最后想说的是要慎用update，一定注意要带上where子句，否则误操作会导致整个表的数据丢失

4. 删除

4.1 delete

delete语法如下：

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

使用建议与update一致，要带上where子句对符合条件的记录进行删除，否则就是整表删除，非常危险

注意这里删除的整表中的内容而不是表本身

删除孙悟空同学的成绩：

mysql> delete from exam-> where name='孙悟空';
Query OK, 1 row affected (0.01 sec)

删除倒数第一名的成绩：

mysql> delete from exam-> order by chinese + math + english asc-> limit 1;
Query OK, 1 row affected (0.01 sec)

删除整表：

delete from exam;
# 如果有自增长则不会受到影响

4.2 truncate

截断表，语法如下：

truncate [table] table_name

也是删除整表，与delete类似，同样注意慎用

与delete的区别在于：

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

简单解释第二点，数据库是包含如下几个日志的：

第一个是bin log（二进制日志）：bin log即binary log，是MySQL中比较重要的日志之一，也称为变更日志（update log）。它记录了所有更新数据库的语句（如DDL和DML语句），并以二进制的形式保存在磁盘中。但是，它不包括没有修改任何数据的语句（如SELECT、SHOW等）。

主要功能：

数据恢复：MySQL可以通过bin log恢复某一时刻的误操作的数据。
数据复制：MySQL的数据备份或主从复制、集群高可用、读写分离等功能都是基于bin log的重放实现的。

记录格式：binlog日志有三种格式：statement、row和mixed。

第二个是redo log（重做日志）：redo log是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。当MySQL实例挂了或宕机时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

主要功能：

崩溃恢复：在MySQL实例崩溃后，redo log用于恢复未提交的事务，确保数据的一致性。
提高性能：redo log是顺序写入的，相比随机写入的数据页，其刷盘速度更快，从而提高了数据库的并发能力。

存储形式：硬盘上存储的redo log日志文件以日志文件组的形式出现，每个日志文件大小相同，采用环形数组形式循环写入。

第三个是undo log（撤销日志）：undo log是一种用于数据库管理系统中的数据恢复机制。它记录了对数据库表的修改操作，以便在发生错误或意外情况时能够恢复到之前的状态。

主要功能：

数据恢复：当系统崩溃或发生故障时，undo log用于将数据库恢复到某个特定的状态，确保数据的一致性和完整性。
事务回滚：在事务执行过程中，如果遇到错误或需要回滚，undo log提供了必要的信息来撤销已经执行的修改操作。

记录内容：undo log记录了每个事务的修改操作，包括事务ID、操作类型、数据行号、数据内容以及时间戳等。

truncate操作不会被保存在日志里，所以效率更快也更危险，因为无法回滚

5. 聚合函数

聚合函数用于对一组值执行计算，并返回单个值，常用于统计分析和查找最大最小值等，常见的如下：

count([distinct] expr)，返回查询到的数据的数量
sum([distinct] expr)，返回查询到的数据的总和，不是数字没有意义
avg([distinct] expr)，返回查询到的数据的平均值，不是数字没有意义
max([distinct] expr)，返回查询到的数据的最大值，不是数字没有意义
min([distinct] expr)，返回查询到的数据的最小值，不是数字没有意义

聚合函数通常用于 select列表、having子句或子查询中，而不能直接在 where子句中使用，因为where子句在数据分组之前对每一行进行评估
统计班级共有多少同学：

mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.11 sec)

统计去重后的数学成绩的个数：

mysql> select count(distinct math) from exam;
+----------------------+
| count(distinct math) |
+----------------------+
|                    4 |
+----------------------+
1 row in set (0.01 sec)

统计数学总分：

mysql> select sum(math) from exam;
+-----------+
| sum(math) |
+-----------+
|       454 |
+-----------+
1 row in set (0.00 sec)

统计数学不及格的人数：

mysql> select count(math) from exam where math < 60;
+-------------+
| count(math) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

统计数学平均分：

mysql> select sum(math) / count(*) from exam;
+----------------------+
| sum(math) / count(*) |
+----------------------+
|                 90.8 |
+----------------------+
1 row in set (0.00 sec)mysql> select avg(math) from exam;
+-----------+
| avg(math) |
+-----------+
|      90.8 |
+-----------+
1 row in set (0.00 sec)

5.1 group by

分组是指对表中的数据进行分组，分组之后方便对其进行聚合统计

语法：

select column1, column2, .. from table 
[where exp]
group by column [, ...];
[having exp]
# 若有多个列，分组逻辑是从左到右，都在前一个分组的结果之上再继续分组having：是对聚合后统计的结果，进行条件筛选
需要与where进行区分，它俩的相同点在于都是条件判断
不同点在于执行顺序不同，作用不同
where先执行，它是在某个表中筛选出满足条件的行
而having则是在分组完之后，对分组查询的结果进行条件筛选
最终筛选出满足条件的行

按列进行分组，但在实际分组时，是用该列不同行的数据来进行分组的，分组后，每一组该列的值一定是相同的，这意味着每组都可以被聚合统计

可以这么理解，分组就是分表，根据当前列的值，把一张整表分成了一个个子表，然后再分别对这些子表进行聚合统计

举个例子，把成绩表按照男女分组，然后分别统计其总分或者平均分等，所以分组本质是为了统计

是否要分组是需求决定的

示例表：

mysql> select * from scores;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  1 | 张三   | male   |    90 |
|  2 | 李四   | male   |    85 |
|  3 | 王五   | male   |    95 |
|  4 | 赵六   | female |    88 |
|  5 | 孙七   | female |    92 |
|  6 | 周八   | female |    87 |
+----+--------+--------+-------+
6 rows in set (0.00 sec)

查询男女学生中的最高和最低成绩：

mysql> select gender, max(score) 最高, min(score) 最低 -> from scores-> group by gender;
+--------+--------+--------+
| gender | 最高   | 最低   |
+--------+--------+--------+
| male   |     95 |     85 |
| female |     92 |     87 |
+--------+--------+--------+
2 rows in set (0.00 sec)

一般在group by后出现的列，才能被select，还有聚合函数，除此之外出现其它列会报错，因为SQL引擎将无法确定如何为该分组生成这个列的值，因此会报错

换句话说被分到一个组里说明该列的值都是相同，那么该组内的记录行数大概率是小于（小概率等于）表中总体的行数，那如果要查询其它列，行数不匹配自然无法查询，所以会报错，而且分组都是用来聚合统计的，如果像按照特定条件查询某些应该使用where而不是group by

整表就可以看成一组

最后，不要单纯地认为只有磁盘上导入到mysql的表才叫表，只要是能被查询出来的，都是表，mysql下一切皆是表

6. 关于sql的执行顺序

首先肯定是先执行from，也就是找到要查询哪张表，然后再执行判断条件，带着条件去筛选对应的行，把满足的行留下了，不满足的过滤掉，最后执行select也就是筛选出要查询的列，如果有order by则在select之后执行，当然也可以先排序，但这意味着排序的数据可能有很多是不需要的，既浪费了时间也浪费了空间，因此是被select过滤出来之后，再对数据进行排序

from -> where -> group by -> having -> select -> order by -> limit

具体来说：
from/join：这是SQL语句执行的第一步。首先，from子句指定了要查询的表或视图。如果存在join操作，则会将多个表连接起来。在连接表时，通常会使用on子句来指定连接条件。如果from子句中有多个表，则它们的连接顺序可能会影响查询的性能，尽管数据库管理系统会尝试优化这一点。

where ：在from/join之后，where子句会对结果进行筛选，只保留满足条件的行。where子句在分组和聚合之前应用，因此它可以减少需要处理的数据量。

group by：接下来，如果查询中包含了group by子句，则会根据指定的列将结果集分组。每个分组会作为一个单独的记录来处理，并且可以对每个分组应用聚合函数（如sum、avg、count等）。

having：having子句用于对分组后的结果进行筛选。与where子句不同，having子句可以在分组后对聚合函数的结果进行筛选。

select：在选择阶段，select子句会指定要从结果集中返回的列。如果查询中包含了聚合函数，则这些函数的结果也会在这一步被计算出来。

distinct：如果SELECT子句中包含了distinct关键字，则会去除结果集中的重复行。需要注意的是，如果查询中使用了group by子句，则通常不需要再使用distinct，因为group by已经确保了每个分组的唯一性。

order by：最后，order by子句会根据指定的列对结果集进行排序。排序操作可能会非常消耗资源，因此除非对结果集的顺序有明确要求，否则应尽量避免使用order by。

limit/offset（可选）：在某些情况下，查询结果可能需要被限制为一定数量的行，或者从结果集的特定位置开始返回行。这时可以使用limit和offset子句来实现。