目录
3、插入否则更新（ON DUPLICATE KEY UPDATE）
column列表中的表达式中也可以包含多个表中已有的字段，这时我们就可以通过表达式计算出更多有意义的数据。如下：
2. 具体的执行语序逻辑（先 WHERE（筛选行），后 SELECT（选择列）。）
3. TRUNCATE 与 DELETE 的区别：（重点！！！）
Q2: 为什么SELECT中可以使用GROUP BY的别名？
CRUD 是数据库操作的四种基本动作的缩写，也就是说表的增删查改简称CRUD，分别代表：
Create（创建）、Retrieve（读取）、Update（更新）、Delete（删除）
CRUD的操作对象是对表当中的数据，是典型的DML（Data Manipulation Language）数据操作语言。
一、插入数据（Create）
📌 基本语法
INSERT [INTO] table_name
[(column1 [, column2] ...)]
VALUES
(value_list1) [, (value_list2)] ...说明一下：
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
SQL中的每个value_list都表示插入的一条记录（
value1 [, value2] ...），每个value_list都由若干待插入的列值组成。 -
SQL中的column列表，用于指定每个value_list中的各个列值应该插入到表中的哪一列。
示例表结构
为了进行演示，下面创建一个学生表，表当中包含自增长的主键id、学号、姓名和QQ号。如下：
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);
创建表完毕后查看表结构，可以看到表结构如下：
1、单行数据 + 全列插入
插入记录时，值的数量、顺序必须与表定义中的列完全一致。若某些列具有默认值（如自增 id），可省略其插入，MySQL 将自动处理。
-- 插入两条记录
INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO students VALUES (101, 10001, '孙悟空', '11111');
-- 查看插入结果
SELECT * FROM students;输出示例：
也就是说，下面使用insert语句向学生表中插入记录，每次向表中插入一条记录，并且插入记录时不指定column列表，表示按照表中默认的列顺序进行全列插入，因此插入的每条记录中的列值需要按表列顺序依次列出。如下：
2、多行数据 + 指定列插入
使用insert语句也可以一次向表中插入多条记录，插入的多条记录之间使用逗号隔开，并且插入记录时可以只指定某些列进行插入。未指定的列将采用默认值或 NULL。如下：
INSERT INTO students (id, sn, name) VALUES
(102, 20001, '曹孟德'),
(103, 20002, '孙仲谋');
-- 查看插入结果
SELECT * FROM students;输出示例：
说明一下： 在插入记录时，只有允许为空的列（比如NOT NULL或者有DEFAULT值时）或自增长字段可以不指定值插入，不允许为空的列必须指定值插入，否则会报错。
3、插入否则更新（ON DUPLICATE KEY UPDATE）
向表中插入记录时，如果待插入记录中的主键或唯一键已经存在，那么就会因为主键冲突或唯一键冲突导致插入失败。这时可以选择性的进行同步更新操作：
-
如果表中没有冲突数据，则直接插入数据。
-
如果表中有冲突数据，则将表中的数据进行更新。
插入否则更新的SQL语句
INSERT ... ON DUPLICATE UPDATE column1=value1 [, column2=value2] ...;说明一下：（可以选择性的进行同步更新操作的语法）
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
SQL中INSERT之后语法与之前使用的INSERT语句相同。
-
UPDATE后面的column=value，表示当插入记录出现冲突时需要更新的列值。
当插入数据导致主键冲突或唯一键冲突时，可以选择执行更新操作。
比如向学生表中插入记录时，如果没有出现主键冲突则直接插入记录，如果出现了主键冲突，则将表中冲突记录的学号和姓名进行更新。如下：
-- 尝试插入冲突数据（如主键 id=100 已存在）
INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';
-- 查看受影响行数（使用 MySQL 函数）
SELECT ROW_COUNT();
执行插入否则更新的SQL后，可以通过受影响的数据行数来判断本次数据的插入情况：
-
0 rows affected：表中有冲突数据，但冲突数据的值和指定更新的值相同。
-
1 row affected：表中没有冲突数据，数据直接被插入。
-
2 rows affected：表中有冲突数据，并且数据已经被更新。
也可以通过，ROW_COUNT() 返回值的说明来判断本次数据的插入情况：
-
0：冲突存在，但更新值与原值相同
-
1：无冲突，新数据被插入
-
2：冲突存在，并且数据已更新。
如上面的输出的2，此时我们可以查看表中的内容，发现已经发生改变了：
4、替换（REPLACE）
-
如果表中没有冲突数据，则直接插入数据。
-
如果表中有冲突数据，则先将表中的冲突数据删除，然后再插入数据。
也就是说，REPLACE 语句在遇到主键或唯一键冲突时，会先删除原有记录，再插入新记录。
要达到上述效果，只需要在插入数据时将SQL语句中的INSERT改为REPLACE即可。比如：
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
执行替换数据的SQL后，向上面一样可以通过受影响的数据行数来判断本次数据的插入情况：
-
1 row affected：表中没有冲突数据，数据直接被插入。
-
2 rows affected：表中有冲突数据，冲突数据被删除后重新插入。
也可以通过REPLACE 的返回（ROW_COUNT() 函数）影响行数来判断本次数据的插入情况：
-
1：无冲突，数据被插入
-
2：有冲突，原有记录被删除后重新插入
在上面的操作后，我们若此时想使用ROW_COUNT() 函数来查看插入情况的话，会发现如下情况，并不是所想的2：
所以我们理一下思路，好好认识一下ROW_COUNT() 函数：
ROW_COUNT() 函数的作用
ROW_COUNT() 函数用于返回上一条SQL语句所影响的数据行数。这里的“影响”指的是：
-
DML 语句（数据操作语言）：如
INSERT,UPDATE,DELETE,REPLACE。-
对于
UPDATE ... SET ...，返回的是被更改的行数。 -
对于
REPLACE INTO，逻辑是“先尝试插入，如果唯一键冲突则删除再插入”。所以REPLACE语句影响了 2 行：1 行被删除，1 行被插入。因此它返回2 rows affected。此时如果马上执行SELECT ROW_COUNT()，它会返回2。
-
-
DDL 语句（数据定义语言）：如
CREATE TABLE,ALTER TABLE。对于这类语句，如果成功，ROW_COUNT()通常返回 0。 -
DQL 语句（数据查询语言）：即
SELECT语句。这是关键所在！
为什么 ROW_COUNT() 返回了 -1？
让我们看一下执行命令的顺序：
-
REPLACE INTO students ...-> 成功，影响 2 行。此时ROW_COUNT()的值是2。 -
SELECT * FROM students-> 这是一条查询语句，成功返回了 4 行数据。-
SELECT查询语句不会设置ROW_COUNT()为它返回的行数（4）。 -
相反，对于
SELECT语句，MySQL 会将ROW_COUNT()的值设置为 -1。 -
所以，上一条语句（
SELECT * ...）执行完后，ROW_COUNT()的值被覆盖成了-1。
-
-
SELECT ROW_COUNT()-> 查询当前ROW_COUNT()的值。因为它紧接在SELECT * FROM students之后，所以自然就返回了-1。
如何得到正确的结果？
如果想获取 REPLACE 语句影响的行数，必须在执行 REPLACE 语句后立即执行 SELECT ROW_COUNT()，中间不能执行任何其他语句（尤其是其他的 SELECT 语句）。如下：
小结
-
使用
INSERT插入单行或多行数据，可全列插入或指定列插入； -
遇到键冲突时，可用
ON DUPLICATE KEY UPDATE进行更新操作； -
REPLACE是“先删除再插入”的操作，适用于需要强制替换的场景。
这些操作提供了灵活的数据插入方式，可根据实际需求选择合适的方法。
二、Retrieve (数据查询)
基本语法：
SELECT
[DISTINCT] {* | column1 [, column2, ...]}
FROM table_name
[WHERE condition]
[ORDER BY column [ASC | DESC], ...]
[LIMIT count];说明一下：
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
{ }中的 | 代表可以选择左侧的语句或右侧的语句。
示例表结构与数据：
为了进行演示，下面创建一个成绩表，表当中包含自增长的主键id、姓名以及该同学的语文成绩、数学成绩和英语成绩。如下：
-- 创建成绩表
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese FLOAT DEFAULT 0.0 COMMENT '语文成绩',
math FLOAT DEFAULT 0.0 COMMENT '数学成绩',
english FLOAT DEFAULT 0.0 COMMENT '英语成绩'
);
创建表完毕后查看表结构，可以看到表结构如下：
接下来向表中插入几条测试记录，以供我们进行查找。如下：
-- 插入测试数据
INSERT INTO exam_result (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);
1、SELECT 列操作
1. 全列查询
在查询数据时直接用*（通配符）代替column列表，表示进行全列查询，这时将会显示被筛选出来的记录的所有列信息。如下：
SELECT * FROM exam_result;
注意：不建议在生产环境中使用全列查询（*），原因：被查询到的数据需要通过网络从MySQL服务器传输到本主机，查询列越多，需要传输的数据量越大，可能影响索引使用效率，降低查询性能。（索引待后面课程讲解）
2. 指定列查询
可以按任意顺序指定需要查询的列：
在查询数据时也可以只对指定的列进行查询，这时将需要查询的列在column列表列出即可。如下：
SELECT id, name, english FROM exam_result;
3. 查询字段为表达式
不包含字段的表达式：
查询数据时，column列表中除了能罗列表中存在的列名外，我们也可以将表达式罗列到column列表中。如下：
SELECT id, name, 10 FROM exam_result;
因为select不仅能够用来查询数据，还可以用来计算某些表达式或执行某些函数。如下：
select 10+20;
如果我们将表达式罗列到column列表，那么每当一条记录被筛选出来时就会执行这个表达式，然后将表达式的计算结果作为这条记录的一个列值进行显示。
包含单个字段的表达式：
column列表中的表达式可以包含表中已有的字段，这时每当一条记录被筛选出来时，就会将记录中对应的列值提供给表达式进行计算。如下：
SELECT id, name, english + 10 FROM exam_result;
包含多个字段的表达式：
column列表中的表达式中也可以包含多个表中已有的字段，这时我们就可以通过表达式计算出更多有意义的数据。如下：
SELECT id, name, chinese + math + english FROM exam_result;
4. 为查询结果指定别名
为查询结果指定别名的SQL语句如下：
SELECT column [AS] alias_name [...] FROM table_name;
说明一下：SQL中大写的表示关键字，[ ]中代表的是可选项。
使用 AS 关键字或空格为列指定别名，提高结果可读性：
比如查询成绩表中的数据时，将每条记录中的三科成绩相加，然后将计算结果对应的列指定别名为“总分”。如下：
SELECT id, name, chinese + math + english AS 总分 FROM exam_result;或者：
SELECT id, name, chinese + math + english 总分 FROM exam_result;
5. 结果去重
使用 DISTINCT 关键字去除重复值：
SELECT DISTINCT math FROM exam_result;查询成绩表时指定查询数学成绩对应的列，可以看到数学成绩中有重复的分数（98分重复）。如果想要对查询结果进行去重操作，可以在SQL中的select后面带上distinct。如下：
注意：DISTINCT 会对所有选定的列进行去重操作，如果指定多列，则会基于这些列的组合值进行去重。
最佳实践建议
-
尽量指定需要查询的具体列，避免使用
SELECT * -
为复杂的表达式列使用有意义的别名
-
仅在需要时使用
DISTINCT，因为它会增加查询的计算开销（注意！！！） -
表达式查询可以用于计算字段、数据转换和格式化输出
2、WHERE 条件查询
WHERE 子句用于筛选满足特定条件的记录，是 SQL 查询中最常用的过滤机制。
1. 添加where子句的区别
-
如果在查询数据时没有指定where子句，那么会直接将表中所有的记录作为数据源来依次执行select语句。
-
如果在查询数据时指定了where子句，那么在查询数据时会先根据where子句筛选出符合条件的记录，然后将符合条件的记录作为数据源来依次执行select语句。
where子句中可以指明一个或多个筛选条件，各个筛选条件之间用逻辑运算符AND或OR进行关联，下面给出了where子句中常用的比较运算符和逻辑运算符。
也就是说！！！WHERE 子句的执行顺序在 SELECT 子句之前！！！（这个很重要！！！要记住！！！）
2. 具体的执行语序逻辑（先 WHERE（筛选行），后 SELECT（选择列）。）
-
FROM：首先确定要查询的是哪张表。 -
WHERE：然后，数据库引擎会扫描整个表，并根据WHERE子句中指定的条件过滤出行。只有满足条件的行才会被保留下来。 -
SELECT：最后，对WHERE阶段过滤后得到的行结果集，执行SELECT语句，选择出需要显示的列、进行计算或赋予别名等。
3. 比较运算符
| 运算符 | 说明 |
|---|---|
>, >=, <, <= | 大于，大于等于，小于，小于等于 |
= | 等于，NULL 不安全（例如NULL = NULL 结果为 NULL） |
<=> | 等于，NULL 安全（例如NULL <=> NULL 结果为 TRUE(1)） |
!=, <> | 不等于 |
BETWEEN a0 AND a1 | 范围匹配 [a0, a1]，如果 a0 <= value <= a1 返回 TRUE(1) |
IN (option, ...) | 如果是 option 中的任意一个，返回 TRUE(1) |
IS NULL | 是 NULL |
IS NOT NULL | 不是 NULL |
LIKE | 模糊匹配。% 表示任意多个字符；_ 表示任意一个字符 |
4. 逻辑运算符
| 运算符 | 说明 |
|---|---|
AND | 多个条件必须都为 TRUE(1)，结果才是 TRUE(1) |
OR | 任意一个条件为 TRUE(1)，结果为 TRUE(1) |
NOT | 条件为 TRUE(1)，结果为 FALSE(0) |
5. 查询案例演示
1. 英语不及格的同学及英语成绩 (< 60)
在where子句中指明筛选条件为英语成绩小于60，在select的column列表中指明要查询的列为姓名和英语成绩。如下：
SELECT name, english FROM exam_result WHERE english < 60;
2. 语文成绩在 [80, 90] 分的同学
使用 AND 连接条件：
在where子句中指明筛选条件为语文成绩大于等于80并且小于等于90，在select的column列表中指明要查询的列为姓名和语文成绩。如下：
SELECT name, chinese FROM exam_result
WHERE chinese >= 80 AND chinese <= 90;
使用 BETWEEN ... AND ...（推荐）：
此外，这里也可以使用BETWEEN a0 AND a1来指明语文成绩的的所在区间。如下：
SELECT name, chinese FROM exam_result
WHERE chinese BETWEEN 80 AND 90;
3. 数学成绩是 58、59、98 或 99 分的同学
使用 OR 连接条件：
在where子句中指明筛选条件为数学成绩等于58或59或98或99，在select的column列表中指明要查询的列为姓名和数学成绩。如下：
SELECT name, math FROM exam_result
WHERE math = 58 OR math = 59 OR math = 98 OR math = 99;
使用 IN 条件（更简洁）：
此外，这里也可以通过IN(58, 59, 98, 99)的方式来判断数学成绩是否符合筛选要求。如下：
SELECT name, math FROM exam_result
WHERE math IN (58, 59, 98, 99);
4. 姓孙的同学及孙某同学
% 匹配任意多个字符（包括 0 个）：
在where子句中通过模糊匹配来判断当前同学是否姓孙（需要用到%来匹配多个字符），在select的column列表中指明要查询的列为姓名。如下：
SELECT name FROM exam_result WHERE name LIKE '孙%';
_ 匹配严格的一个任意字符：
在where子句中通过模糊匹配来判断当前同学是否为孙某（需要用到_来严格匹配单个字符），在select的column列表中指明要查询的列为姓名。如下：
SELECT name FROM exam_result WHERE name LIKE '孙_';
5. 语文成绩好于英语成绩的同学
SELECT name, chinese, english FROM exam_result
WHERE chinese > english;
6. 总分在 200 分以下的同学
在select的column列表中添加表达式查询，查询的表达式为语文、数学和英语成绩之和，为了方便观察可以将表达式对应的列指定别名为“总分”，在where子句中指明筛选条件为三科成绩之和小于200。如下：
SELECT name, chinese + math + english AS 总分
FROM exam_result
WHERE chinese + math + english < 200;
重要提示：WHERE 条件中不能使用列别名，必须使用原始表达式。回顾上面刚开始讲解的select和where的执行顺序结论，我们应该知道：
-
查询数据时是先根据where子句筛选出符合条件的记录。
-
然后再将符合条件的记录作为数据源来依次执行select语句。
也就是说，where子句的执行是先于select语句的，所以在where子句中不能使用别名，如果在where子句中使用别名，那么在查询数据时就会产生报错。如下：
SELECT name, chinese + math + english AS 总分 FROM exam_result WHERE 总分 < 200;
7. 语文成绩 > 80 并且不姓孙的同学
在where子句中指明筛选条件为语文成绩大于80，并且通过模糊匹配和not来保证该同学不姓孙，在select的column列表中指明要查询的列为姓名和语文成绩。如下：
SELECT name, chinese FROM exam_result
WHERE chinese > 80 AND name NOT LIKE '孙%';
8. 复杂条件查询：孙某同学，或者总成绩 > 200 且语文 < 数学且英语 > 80
该题目的要求是查询成绩，被查询的人要么是孙某，要么总成绩大于200分并且语文成绩小于数学成绩并且英语成绩大于80分，查询时需要用到模糊匹配、表达式查询和逻辑运算符。如下：
SELECT name, chinese, math, english, chinese + math + english AS 总分
FROM exam_result
WHERE name LIKE '孙_' OR (
chinese + math + english > 200
AND chinese < math
AND english > 80
);
9. NULL 值查询
示例数据：
准备测试表，这里用之前演示新增数据的学生表来演示NULL查询，学生表中的内容如下：
SELECT * FROM students;
查询QQ号已知的同学：
在where子句中指明筛选条件为QQ号不为NULL，在select的column列表中指明要查询的列为姓名和QQ号。如下：
SELECT name, qq FROM students WHERE qq IS NOT NULL;
查询QQ号未知的同学：
在where子句中指明筛选条件为QQ号为NULL，在select的column列表中指明要查询的列为姓名和QQ号。如下：
SELECT name, qq FROM students WHERE qq<=>NULL;
需要注意的是，在与NULL值作比较的时候应该使用<=>运算符，使用=运算符无法得到正确的查询结果。如下：
SELECT name, qq FROM students WHERE qq=NULL;
NULL 值比较的特殊性：
因为=运算符是NULL不安全的，使用=运算符将任何值与NULL作比较，得到的结果都是NULL。如下：
-- 使用 = 比较（NULL 不安全）
SELECT NULL = NULL, NULL = 1, NULL = 0;
但是<=>运算符是NULL安全的，使用<=>运算符将NULL和NULL作比较得到的结果为TRUE(1)，将非NULL值与NULL作比较得到的结果为FALSE(0)。如下：
-- 使用 <=> 比较（NULL 安全）
SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
6. 使用建议
-
NULL 处理：查询 NULL 值时必须使用
IS NULL或IS NOT NULL -
范围查询：优先使用
BETWEEN替代多个AND连接，更简洁易读 -
多值查询：使用
IN替代多个OR连接，提高可读性和性能 -
模糊查询：注意
%和_的区别，%更通用，_更精确 -
复杂条件：适当使用括号明确运算优先级，避免逻辑错误
通过合理使用 WHERE 条件，可以精确筛选出需要的数据，提高查询效率和数据准确性。
3、结果排序 (ORDER BY)
1. 语法
SELECT ... FROM table_name [WHERE ...]
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;2. 排序规则
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
ASC：升序（从小到大）-默认选项 -
DESC：降序（从大到小）
重要提示： 没有 ORDER BY 子句的查询，返回结果的顺序是未定义的，切勿依赖这种顺序进行业务逻辑处理。
3. 排序案例演示
同学及数学成绩，按数学成绩升序显示
在select的column列表中指明要查询的列为姓名和数学成绩，在order by子句中指明按照数学成绩进行升序排序。如下：
SELECT name, math FROM exam_result ORDER BY math ASC;
-- 或简写为（ASC 为默认值）
SELECT name, math FROM exam_result ORDER BY math;
NULL 值的排序处理
NULL 值在升序排序中：
查询同学及其QQ号，按QQ号升序显示，在select的column列表中指明要查询的列为姓名和QQ号，在order by子句中指明按照QQ号进行升序排序。如下：
SELECT name, qq FROM students ORDER BY qq;
说明一下：NULL 被视为最小值（比任何值都小），出现在结果集最前面。
NULL 值在降序排序中：
查询同学及其QQ号，按QQ号降序显示，在select的column列表中指明要查询的列为姓名和QQ号，在order by子句中指明按照QQ号进行降序排序。如下：
SELECT name, qq FROM students ORDER BY qq DESC;
说明一下：NULL 被视为最小值（比任何值都小），降序时出现在结果集最后面。
多字段排序（优先级排序）
按数学成绩降序、英语成绩升序、语文成绩升序显示：
在select的column列表中指明要查询的列为姓名、数学成绩、英语成绩和语文成绩，在order by子句中指明依次按照数学成绩排降序、英语成绩排升序和语文成绩排升序。如下：
SELECT name, math, english, chinese FROM exam_result
ORDER BY math DESC, english ASC, chinese ASC;
可以看到显示结果是按照数学成绩进行降序排序的，而相同的数学成绩之间则是按照英语成绩进行升序排序的，以此类推。order by子句中可以指明按照多个字段进行排序，每个字段都可以指明按照升序或降序进行排序，各个字段之间使用逗号隔开，排序优先级与书写顺序相同。比如上述SQL中，当两条记录的数学成绩相同时就会按照英语成绩进行排序，如果这两条记录的英语成绩也相同就会继续按照语文成绩进行排序，以此类推。
排序优先级说明：
-
首先按
math降序排列 -
当
math相同时（如唐三藏和猪悟能），按english升序排列 -
如果还有相同，再按
chinese升序排列
使用表达式和别名排序
使用表达式排序：
在select的column列表中指明要查询的列为姓名和总分（表达式查询），在order by子句中指明按照总分进行降序排序。如下：
SELECT name, chinese + english + math AS total_score
FROM exam_result
ORDER BY chinese + english + math DESC;
需要注意的是，在order by子句中可以使用select中指定的别名：
-
查询数据时是先根据where子句筛选出符合条件的记录。
-
然后再将符合条件的记录作为数据源来依次执行select语句。
-
最后再通过order by子句对select语句的执行结果进行排序。
也就是说，order by子句的执行是在select语句之后的，所以在order by子句中可以使用别名。如下：
使用列别名排序（推荐）：
SELECT name, chinese + english + math AS 总分
FROM exam_result
ORDER BY 总分 DESC;
结合 WHERE 条件进行排序
查询姓孙或姓曹的同学数学成绩，按数学成绩降序排列：
对于这种比较长的题目，我们可以先对题目进行分析：
-
题目的前半句描述的是查询，后半句描述的是排序。
-
在排序的时候必须要有数据，因此可以先完成前面的查询动作，然后再根据题目要求进行排序。
先完成查询：查询数据时，在where子句中指明筛选条件为姓孙或姓曹（模糊匹配），在select的column列表中指明要查询的列为姓名和数学成绩。如下：
SELECT name, math FROM exam_result
WHERE name LIKE '孙%' OR name LIKE '曹%';
再完成排序：当查询到目标数据后再在查询SQL后添加order by子句，在order by子句中指明按照数学成绩进行降序排序。如下：
SELECT name, math FROM exam_result
WHERE name LIKE '孙%' OR name LIKE '曹%'
ORDER BY math DESC;
4. 排序最佳实践
-
明确指定排序方向：即使使用默认的
ASC，也建议显式写出，提高代码可读性 -
合理使用别名：在
ORDER BY子句中使用列别名，使代码更简洁 -
多字段排序：根据业务需求确定字段优先级顺序
-
NULL 值处理：了解 NULL 在排序中的特殊行为，必要时使用
IS NULL条件单独处理 -
性能考虑：对经常排序的字段建立索引（后面会讲解到索引），提高查询效率
5. 执行顺序提醒（超级重要！！！回顾上面的知识点！！！）
SELECT name, chinese + math + english AS 总分 -- 3. 选择列并计算别名
FROM exam_result -- 1. 从表中获取数据
WHERE chinese > 60 -- 2. 过滤数据
ORDER BY 总分 DESC; -- 4. 对结果进行排序通过合理使用 ORDER BY 子句，可以确保查询结果按照业务需求有序展示，提升数据可读性和实用性。
4、筛选分页结果 (LIMIT & OFFSET)
1. 语法说明
通过上面的表来对比其中的规律变化，深刻理解这些规则的使用：
限制返回记录数：从第 0 条记录开始，返回 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
指定起始位置（传统写法）：
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;-
从第 s 条记录开始，返回 n 条结果
-
注意： 起始下标 s 从 0 开始计算
指定起始位置（推荐写法）：
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;-
从第 s 条记录开始，返回 n 条结果
-
语法更明确，建议使用此格式
说明一下：
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
查询SQL中各语句的执行顺序为：where、select、order by、limit。
-
limit子句在筛选记录时，记录的下标从0开始。
2. 重要建议
安全查询实践： 对未知数据量的表进行查询时，务必添加 LIMIT 子句，避免全表扫描导致数据库性能问题。
-- 危险：可能返回海量数据
SELECT * FROM large_table;
-- 安全：限制返回记录数
SELECT * FROM large_table LIMIT 100;
-- 更安全：探索性查询只返回少量数据
SELECT * FROM unknown_table LIMIT 1;建议： 对未知表进行查询时最好在查询SQL后加上limit 1，避免在查询全表数据时因为表中数据过大而导致数据库卡死。
3. 分页查询案例演示
按 id 分页，每页显示 3 条记录，分别显示第1、2、3页
这里使用成绩表中的数据来演示分页查询，成绩表中的内容如下：
第 1 页（记录 1-3）：查询第1页记录时在查询全表数据的SQL后，加上limit子句指明从第0条记录开始，向后筛选出3条记录。如下：
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id
LIMIT 3 OFFSET 0;
第 2 页（记录 4-6）：查询第2页记录时在查询全表数据的SQL后，加上limit子句指明从第3条记录开始，向后筛选出3条记录。如下：
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id
LIMIT 3 OFFSET 3;
第 3 页（记录 7-9）：查询第3页记录时在查询全表数据的SQL后，加上limit子句指明从第6条记录开始，向后筛选出3条记录。如下：
SELECT id, name, math, english, chinese FROM exam_result
ORDER BY id
LIMIT 3 OFFSET 6;
说明一下： 如果从表中筛选出的记录不足n个，则筛选出几个就显示几个（只返回实际存在的记录）。
4. 分页计算公式
在实际应用中，通常使用以下公式计算分页参数：
LIMIT size OFFSET (page - 1) * size -- 第 page 页，每页 size 条记录
示例： LIMIT 3 OFFSET (2-1)*3 -- 即 LIMIT 3 OFFSET 3（查询第 2 页，每页 3 条记录）
5. 不同写法的对比
传统写法：
-- 第 2 页，每页 3 条
SELECT * FROM exam_result LIMIT 3, 3;推荐写法：
-- 第 2 页，每页 3 条（更清晰易懂）
SELECT * FROM exam_result LIMIT 3 OFFSET 3;6. 结合 WHERE 和 ORDER BY 的完整示例
查询数学成绩前 3 名的学生：
SELECT name, math FROM exam_result
ORDER BY math DESC
LIMIT 3;
查询数学成绩第 4-6 名的学生：
SELECT name, math FROM exam_result
ORDER BY math DESC
LIMIT 3 OFFSET 3;
7. 分页最佳实践
-
始终使用 ORDER BY：确保分页结果顺序一致，避免出现"跳页"现象
-
使用推荐语法：优先使用
LIMIT n OFFSET s格式，提高代码可读性 -
性能优化：对排序字段建立索引，提高大数据量下的分页性能
-
参数验证：在应用层验证页码和页大小参数，避免非法值
-
边界处理：处理最后一页记录数不足的情况
8. 执行顺序回顾（同样重要！！！需要重点记住！！！）
SELECT name, math -- 4. 选择指定列
FROM exam_result -- 1. 从表获取数据
WHERE chinese > 60 -- 2. 过滤记录
ORDER BY math DESC -- 3. 排序结果
LIMIT 3 OFFSET 3; -- 5. 分页限制通过合理使用 LIMIT 和 OFFSET，可以实现高效的数据分页查询，提升用户体验和系统性能。
三、Update (数据更新)
1、语法
UPDATE table_name
SET column1 = expr1 [, column2 = expr2, ...]
[WHERE condition]
[ORDER BY column [ASC|DESC]]
[LIMIT count];说明一下
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
SQL中的column=expr，表示将记录中列名为column的值修改为expr。
-
在修改数据之前需要先找到待修改的记录，update语句中的where、order by和limit就是用来定位数据的。
1、单字段更新：将孙悟空同学的数学成绩变更为 80 分
查看原数据：在修改数据之前，先查看孙悟空同学当前的数学成绩。
SELECT name, math FROM exam_result WHERE name = '孙悟空';
执行更新：
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
验证更新结果：
SELECT name, math FROM exam_result WHERE name = '孙悟空';
在update语句中指明要将筛选出来的记录的数学成绩改为80分，并在修改后再次查看数据确保数据成功被修改。
2、多字段更新：将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
查看原数据：在修改数据之前，先查看曹孟德同学当前的数学成绩和语文成绩。
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
执行更新：
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
验证更新结果：
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
在update语句中指明要将筛选出来的记录的数学成绩改为60分，语文成绩改为70分，并在修改后再次查看数据确保数据成功被修改。
3、基于表达式更新：将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
查看原数据（按总分升序排列）：
SELECT name, math, chinese + math + english AS 总分
FROM exam_result
ORDER BY 总分 LIMIT 3;
执行更新（使用表达式）：
UPDATE exam_result SET math = math + 30
ORDER BY chinese + math + english ASC
LIMIT 3;
注意：MySQL 不支持 += 语法，必须使用完整的表达式
查看特定学生的更新结果：
SELECT name, math, chinese + math + english AS 总分
FROM exam_result
WHERE name IN ('宋公明', '刘玄德', '曹孟德');
重新按总分排序查看：
SELECT name, math, chinese + math + english AS 总分
FROM exam_result
ORDER BY 总分 LIMIT 3;
思考题： 为什么更新后再次按总分排序时，原来的倒数前三名不再是倒数前三？
答案： 因为数学成绩增加后，这些学生的总分也相应提高，排名发生了变化。
4. 全表更新：将所有同学的语文成绩更新为原来的 2 倍
⚠️ 重要警告： 没有 WHERE 子句的 UPDATE 语句会更新整个表，请谨慎使用！
查看原数据：在修改数据之前，先查看所有同学的语文成绩。
SELECT * FROM exam_result;
执行全表更新：
UPDATE exam_result SET chinese = chinese * 2;
验证更新结果：
SELECT * FROM exam_result;
在update语句中指明要将筛选出来的记录的语文成绩变为原来的2倍，并在修改后再次查看数据确保数据成功被修改。
2、UPDATE 操作最佳实践
-
始终备份数据：在执行 UPDATE 前，特别是全表更新时，先备份数据
-
使用事务：在生产环境中，将 UPDATE 操作放在事务中，便于回滚（后面会学到）
START TRANSACTION; UPDATE exam_result SET math = math + 10 WHERE name = '孙悟空'; -- 检查结果，如果正确则提交，否则回滚 COMMIT; -- 或 ROLLBACK; -
先 SELECT 后 UPDATE：先用 SELECT 验证 WHERE 条件，再执行 UPDATE
-
限制影响范围：使用 LIMIT 子句限制最大更新行数
-
避免全表更新：除非确实需要，否则务必添加 WHERE 条件
3、安全更新模式
MySQL 的安全模式会阻止没有 WHERE 条件的 UPDATE/DELETE 操作：
-- 检查当前安全模式设置
SHOW VARIABLES LIKE 'sql_safe_updates';
-- 启用安全模式（推荐）
SET sql_safe_updates = 1;
通过遵循这些最佳实践，可以确保 UPDATE 操作的安全性和准确性。
四、Delete (数据删除)
1、删除数据 (DELETE)
语法：
DELETE FROM table_name
[WHERE condition]
[ORDER BY column [ASC|DESC]]
[LIMIT count];说明一下：
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
在删除数据之前需要先找到待删除的记录，delete语句中的where、order by和limit就是用来定位数据的。
删除特定数据：删除孙悟空同学的考试成绩
在删除数据之前，先查看孙悟空同学的相关信息，然后在delete语句中指明删除孙悟空对应的记录，并在删除后再次查看数据确保数据成功被删除。如下：
查看原数据：
SELECT * FROM exam_result WHERE name = '孙悟空';
执行删除操作：
DELETE FROM exam_result WHERE name = '孙悟空';
验证删除结果：
SELECT * FROM exam_result WHERE name = '孙悟空';
删除整张表数据
⚠️ 重要警告： 删除整表的这个操作要慎用！建议先备份数据。
准备测试表和数据：创建一张测试表，表中包含一个自增长的主键id和姓名。如下：
-- 创建测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
查看创建的表对应的描述信息：
desc for_delete;
向表中插入一些测试数据用于删除。如下：
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
-- 查看测试数据
SELECT * FROM for_delete;
删除整表数据：
在delete语句中只指明要删除数据的表名，而不通过where、order by和limit指明筛选条件，这时将会删除整张表的数据。如下：
DELETE FROM for_delete;
验证删除结果：
SELECT * FROM for_delete;
测试自增ID的连续性：
再向表中插入一些数据，在插入数据时不指明自增长字段的值，这时会发现插入数据对应的自增长id值是在之前的基础上继续增长的。如下：
-- 插入新数据，自增ID在原值上继续增长
INSERT INTO for_delete (name) VALUES ('D');
SELECT * FROM for_delete;
查看表结构确认自增值：
查看创建表时的相关信息时可以看到，有一个AUTO_INCREMENT=n的字段，该字段表示下一次插入数据时自增长字段的值应该为n。如下：
SHOW CREATE TABLE for_delete\G
当通过delete语句删除整表数据时，不会重置AUTO_INCREMENT=n字段，因此删除整表数据后插入数据对应的自增长id值会在原来的基础上继续增长。如下：
2、截断表 (TRUNCATE)
1. 语法
TRUNCATE [TABLE] table_name;2. 说明一下：（红色标注的部分后面学到会理解深刻的）
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
truncate只能对整表操作，不能像delete一样针对部分数据操作。
-
truncate实际上不对数据操作，所以比delete更快。
-
truncate在删除数据时不经过真正的事务，所以无法回滚。
-
truncate会重置AUTO_INCREMENT=n字段。
⚠️ 重要警告： 此操作不可逆，务必谨慎使用！
3. TRUNCATE 与 DELETE 的区别：（重点！！！）
| 特性 | DELETE | TRUNCATE |
|---|---|---|
| 操作粒度 | 可删除部分数据（使用 WHERE） | 只能整表操作 |
| 性能 | 较慢，逐行删除 | 极快，直接释放数据页 |
| 事务支持 | 支持事务，可回滚 | 不经过事务，无法回滚 |
| 自增ID | 保持原自增值 | 重置自增值 |
| 触发器 | 会触发 DELETE 触发器 | 不会触发触发器 |
| 返回信息 | 返回受影响行数 | 返回 0 行受影响 |
4. TRUNCATE 操作演示
准备测试表和数据：创建一张测试表，表中包含一个自增长的主键id和姓名。如下：
-- 创建测试表
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
-- 查看表的描述
desc for_truncate;
向表中插入一些测试数据用于删除。如下：
-- 插入测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
-- 查看测试数据
SELECT * FROM for_truncate;
在truncate语句中只指明要删除数据的表名，这时便会删除整张表的数据，但由于truncate实际不对数据操作，因此执行truncate语句后看到影响行数为0。如下：
截断整表数据：
TRUNCATE for_truncate;
验证删除结果：
SELECT * FROM for_truncate;
测试自增ID重置：
再向表中插入一些数据，在插入数据时不指明自增长字段的值，这时会发现插入数据对应的自增长id值是重新从1开始增长的。如下：
-- 插入新数据，自增ID从1重新开始
INSERT INTO for_truncate (name) VALUES ('D');
SELECT * FROM for_truncate;
查看表结构确认自增值重置：
查看创建表时的相关信息时也可以看到，有一个AUTO_INCREMENT=n的字段，该字段表示下一次插入数据时自增长字段的值应该为n。如下：
SHOW CREATE TABLE for_truncate\G
但是当通过truncate语句删除整表数据时，会重置AUTO_INCREMENT字段，因此截断表后插入数据对应的自增长id值会重新从1开始增长。如下：我们发现auto increment不见了，也就是说若此时插入数据的话就从1开始增长。
5. 删除操作最佳实践
-
数据备份：执行重要删除操作前务必备份数据
-
使用事务：DELETE 操作建议放在事务中
START TRANSACTION; DELETE FROM exam_result WHERE name = '孙悟空'; -- 确认无误后提交 COMMIT; -- 或回滚 -- ROLLBACK; -
先查询后删除：先用 SELECT 验证要删除的数据
-- 安全删除流程 SELECT * FROM table_name WHERE condition; -- 先查看 DELETE FROM table_name WHERE condition; -- 再删除 -
启用安全模式：防止误操作
SET sql_safe_updates = 1; -- 要求DELETE/UPDATE必须有WHERE条件 -
权限控制：严格管理数据库用户的删除权限
6. 应用场景选择
-
删除特定记录：使用
DELETE + WHERE -
清空表但保留结构：需要重置自增ID时用
TRUNCATE，需要可回滚时用DELETE -
大量数据删除：性能要求高时用
TRUNCATE，需要控制删除量时用DELETE + LIMIT
通过合理选择删除方式并遵循安全实践，可以确保数据删除操作的安全性和效率。
五、插入查询结果
1、语法
INSERT INTO target_table [(column1, column2, ...)]
SELECT column1, column2, ...
FROM source_table
[WHERE condition];说明一下
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
SQL的作用是将筛选出来的记录插入到指定的表当中。
-
SQL中的column，表示将筛选出的记录的各个列插入到表中的哪一列。
这种语法允许将 SELECT 查询的结果直接插入到目标表中，非常适合数据迁移、备份和去重操作。
2、案例：删除表中的重复记录
业务场景：需要清理表中的重复数据，确保每条记录只保留一份。
实现步骤：
1. 创建原始数据表并插入测试数据
创建一张测试表，表中包含id和姓名。如下：
-- 创建原始表
CREATE TABLE duplicate_table (
id INT,
name VARCHAR(20)
);
-- 查看创建的表信息
desc duplicate_table;
向测试表中插入一些测试数据，数据中存在重复的记录。如下：
-- 插入包含重复记录的测试数据
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'), -- 重复记录
(200, 'bbb'),
(200, 'bbb'), -- 重复记录
(200, 'bbb'), -- 重复记录
(300, 'ccc');
-- 查看原始数据（包含重复记录）
SELECT * FROM duplicate_table;
现在要求删除测试表中重复的数据，思路如下：
-
创建一张临时表，该表的结构与测试表的结构相同。
-
以去重的方式查询测试表中的数据，并将查询结果插入到临时表中。
-
将测试表重命名为其他名字，再将临时表重命名为测试表的名字，实现原子去重操作。
2. 创建空表（结构与原表相同）
由于临时表的结构与测试表相同，因此在创建临时表的时候可以借助like进行创建。如下：
-- 使用 LIKE 关键字创建结构相同的空表
CREATE TABLE no_duplicate_table LIKE duplicate_table;
查看临时表，我们可以看到临时表的结构与测试表相同：
desc no_duplicate_table;
3. 将去重数据插入到新表
通过插入查询语句将去重查询后的结果插入到临时表中，由于临时表和测试表的结构相同，并且select进行的是全列查询，因此在插入时不用在表名后指明column列表。如下：
-- 使用 DISTINCT 去重后插入新表
INSERT INTO no_duplicate_table
SELECT DISTINCT * FROM duplicate_table;
我们查看临时表中的内容，可以看到从测试表筛选出来的不重复数据全都成功插入到了临时表中了：
SELECT DISTINCT * FROM no_duplicate_table;
4. 原子性表重命名操作
将测试表重命名为其他名字（相当于对去重前的数据进行备份，如果不需要可以直接删除），将临时表重命名为测试表的名字，这时便完成了表中数据的去重操作。如下：
-- 使用单个语句原子性地重命名表
RENAME TABLE
duplicate_table TO old_duplicate_table,
no_duplicate_table TO duplicate_table;
5. 验证最终结果
SELECT * FROM duplicate_table;
6. 清理临时表（可选）
DROP TABLE old_duplicate_table;
3、方法优势分析
方案优点：
-
数据安全：原表数据保留在备份表中，可随时恢复
-
操作原子性：RENAME TABLE 操作是原子的，避免中间状态
-
业务连续：表名保持不变，应用程序无需修改
-
性能优化：比在原表上直接去重更高效
六、聚合函数
聚合函数用于对一组值执行计算并返回单个汇总值，是数据分析中的重要工具。
1、常用聚合函数
| 函数 | 说明 |
|---|---|
COUNT([DISTINCT] expr) | 返回查询到的数据数量 |
SUM([DISTINCT] expr) | 返回查询到的数据总和（仅对数字有意义） |
AVG([DISTINCT] expr) | 返回查询到的数据平均值（仅对数字有意义） |
MAX([DISTINCT] expr) | 返回查询到的数据最大值（仅对数字有意义） |
MIN([DISTINCT] expr) | 返回查询到的数据最小值（仅对数字有意义） |
注意： 除 COUNT 外，其他聚合函数遇到 NULL 值时会自动忽略。聚合函数可以在select语句中使用，此时select每处理一条记录时都会将对应的参数传递给这些聚合函数。
2、案例演示
1. 统计班级共有多少同学
准备测试表，这里用之前的学生表来进行演示，学生表中的内容如下：
在select语句中使用count函数，并将*作为参数传递给count函数，这时便能统计出表中的记录条数。如下：
使用 COUNT(*) 统计：COUNT(*) 统计所有行数，包括 NULL 值
SELECT COUNT(*) FROM students;
在select语句中使用count函数，并将表达式作为参数传递给count函数，这时也可以统计出表中的记录条数。如下：
使用 COUNT(1) 统计：COUNT(1) 与 COUNT(*) 效果相同，都是统计总行数
SELECT COUNT(1) FROM students;
这种写法相当于在查询表中数据时，自行新增了一列列名为特定表达式的列，我们就是在用count函数统计该列中有多少个数据，等价于统计表中有多少条记录。如下：
SELECT * , 1 FROM students;
2. 统计班级收集的 QQ 号数量
在select语句中使用count函数统计qq列中数据的个数，这时便能统计出表中QQ号的个数。如下：
SELECT COUNT(qq) FROM students;
说明一下： 如果count函数的参数是一个确定的列名，那么count函数将会忽略该列中的NULL值（COUNT(column) 只统计该列非 NULL 值的数量）。
3. 统计数学成绩分数个数
准备测试表，这里用之前的成绩表来进行演示，成绩表中的内容如下：
统计全部数学成绩数量：
在select语句中使用count函数统计math列中数据的个数，这时便能统计出表中的数学成绩的个数。如下：
SELECT COUNT(math) FROM exam_result;
统计去重后的数学成绩数量：
在使用count函数时（包括其他聚合函数），在传递的参数之前加上distinct，这时便能统计出表中数学成绩去重后的个数。如下：
DISTINCT 关键字用于去除重复值后再计数：
SELECT COUNT(DISTINCT math) FROM exam_result;
4. 统计数学成绩总分
统计所有数学成绩总分：
在select语句中使用sum函数统计math列中数据的总和，这时便能统计出表中的数学成绩的总和。如下：
SELECT SUM(math) FROM exam_result;
统计不及格（<60分）的数学总分：
在where子句中指明筛选条件为数学成绩小于60分，在select语句中使用sum函数统计math列中数据的总和。如下：
SELECT SUM(math) FROM exam_result WHERE math < 60;
当没有匹配记录时，SUM 函数返回 NULL
改进写法（使用 COALESCE 处理 NULL）：
计算 exam_result 表中所有数学成绩不及格（math < 60）学生的数学成绩总和，如果没有人不及格，则返回 0。
SELECT COALESCE(SUM(math), 0) AS 不及格数学总分
FROM exam_result
WHERE math < 60;COALESCE(SUM(math), 0)：这是整个语句的精髓，用于处理上面提到的 NULL 值问题。
-
COALESCE函数： 这个函数接受多个参数，并返回第一个非NULL的值。 -
工作逻辑：
-
首先计算
SUM(math)。 -
如果存在不及格学生，
SUM(math)会返回一个具体的数字（比如 150）。COALESCE(150, 0)会返回第一个参数150。 -
如果不存在不及格学生，
SUM(math)返回NULL。COALESCE(NULL, 0)发现第一个参数是NULL，于是继续检查第二个参数0，这是一个非NULL值，因此函数最终返回0。
-
简单来说，COALESCE 确保了查询结果永远是一个有意义的数字（总和或0），而不是令人困惑的 NULL。
5. 统计平均总分
在select语句中使用avg函数计算总分的平均值。如下：
SELECT AVG(chinese + math + english) AS 平均总分 FROM exam_result;
分别统计各科平均分：
SELECT
AVG(chinese) AS 语文平均分,
AVG(math) AS 数学平均分,
AVG(english) AS 英语平均分
FROM exam_result;
6. 返回英语最高分
在select语句中使用max函数查询英语成绩最高分。如下：
SELECT MAX(english) AS 英语最高分 FROM exam_result;
找出英语最高分的学生：
SELECT name, english
FROM exam_result
WHERE english = (SELECT MAX(english) FROM exam_result);
7. 返回 70 分以上的数学最低分
在where子句中指明筛选条件为数学成绩大于70分，在select语句中使用min函数查询数学成绩最低分。如下：
SELECT MIN(math) AS 70分以上数学最低分
FROM exam_result
WHERE math > 70;
3、高级应用场景
场景1：多维度统计
SELECT
COUNT(*) AS 总人数,
AVG(math) AS 数学平均分,
MAX(math) AS 数学最高分,
MIN(math) AS 数学最低分,
SUM(math) AS 数学总分
FROM exam_result;
场景2：条件聚合
SELECT
COUNT(*) AS 总人数,
COUNT(CASE WHEN math >= 90 THEN 1 END) AS 数学优秀人数,
COUNT(CASE WHEN math < 60 THEN 1 END) AS 数学不及格人数
FROM exam_result;CASE WHEN math >= 90 THEN 1 END：
-
这是一个
CASE表达式，它会为表中的每一行进行判断。 -
判断逻辑：如果当前行的
math字段值大于等于 90，那么此表达式的结果就返回1（THEN 1）。如果不满足条件，由于没有ELSE子句，表达式将默认返回NULL。 -
可以理解为：为每个学生打标签，优秀的学生标记为
1，不优秀的学生标记为NULL。
COUNT( ... )：
-
COUNT(column_name)函数的特点是：它只计算指定列中非NULL值的数量。 -
当把上一步的
CASE表达式放入COUNT()中时，COUNT函数会忽略所有的NULL值，只去数有多少个1。 -
最终效果：这就相当于数出了有多少个学生满足
math >= 90的条件。
COUNT(CASE WHEN math < 60 THEN 1 END) AS 数学不及格人数
逻辑与上一项完全相同，只是条件变成了 math < 60。它会：
-
为不及格的学生生成
1，及格的学生生成NULL。 -
COUNT函数数出所有非NULL（即1）的个数，得到不及格人数。
场景3：去重统计
SELECT
COUNT(math) AS 总成绩数,
COUNT(DISTINCT math) AS 不重复成绩数,
COUNT(*) - COUNT(DISTINCT math) AS 重复成绩数
FROM exam_result;
4、使用注意事项
✅ 最佳实践：
-
明确统计意图：根据需求选择合适的聚合函数
-
处理 NULL 值：使用
COALESCE或IFNULL处理可能的 NULL 结果 -
性能优化：对聚合字段建立索引提高查询效率
-
结果验证：理解每个聚合函数的计算逻辑，避免误解
⚠️ 常见误区：
COUNT 的区别：
-
COUNT(*)：统计所有行数 -
COUNT(column)：统计该列非 NULL 值的数量 -
COUNT(1)或者COUNT(常量)：统计所有行数
空集处理：无数据时，COUNT 返回 0，其他聚合函数返回 NULL
-- 无数据时，COUNT 返回 0，其他聚合函数返回 NULL
SELECT
COUNT(math) AS cnt,
SUM(math) AS total,
AVG(math) AS avg_val
FROM exam_result WHERE math > 100;DISTINCT 使用：DISTINCT 会增加计算开销，仅在必要时使用
聚合函数是 SQL 数据分析的基础，熟练掌握后可以高效完成各种统计汇总任务。
七、GROUP BY 子句的使用
1、基本概念
GROUP BY 子句用于对查询结果进行分组，通常与聚合函数配合使用，实现对每个分组的统计计算。
2、语法
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;说明一下：
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
查询SQL中各语句的执行顺序为：where、group by、select、order by、limit。
-
group by后面的列名，表示按照指定列进行分组查询。
3、测试数据准备
我现在使用已经打包好了的经典的 Oracle 9i 测试表进行测试（大家没有的话可以将下面的SQL整合成一个SQL文件也是一样的，自行操作；如果嫌麻烦就直接复制到MySQL中测试）：
首先使用rz命令将本地电脑的测试SQL文件拉取到远端服务器当前工作目录上，如下：
成功拉取到云服务器上后，我们可以看到当前目录有了所需要的SQL文件，可以通过vim进去查看其中的内容：
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);
然后我们回到MySQL中，导入刚刚的SQL文件，我们使用source命令+SQL文件所在的路径，如下：
source /root/scott_data.sql
然后我们可以查看建立的数据库和里面的表，如下已经成功创建相关的数据库和表：
上述SQL中创建了一个名为scott的数据库，在该数据库中分别创建了部门表（dept）、员工表（emp）和工资等级表（salgrade），并分别向三张表中插入了一些数据用于查询。
创建经典的 Oracle 9i 测试表结构：
EMP 员工表结构：
CREATE TABLE EMP (
empno INT PRIMARY KEY, -- 员工编号
ename VARCHAR(50), -- 员工姓名
job VARCHAR(50), -- 职位
mgr INT, -- 经理编号
hiredate DATE, -- 入职日期
sal DECIMAL(10,2), -- 工资
comm DECIMAL(10,2), -- 奖金
deptno INT -- 部门编号
);
-- 插入示例数据
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);员工表（emp）的表结构和表中的内容如下：
DEPT 部门表结构：
CREATE TABLE DEPT (
deptno INT PRIMARY KEY, -- 部门编号
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门地点
);
INSERT INTO DEPT VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');其中部门表（dept）的表结构和表中的内容如下：
SALGRADE 工资等级表结构：
CREATE TABLE SALGRADE (
grade INT PRIMARY KEY, -- 等级
losal DECIMAL(10,2), -- 最低工资
hisal DECIMAL(10,2) -- 最高工资
);
INSERT INTO SALGRADE VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);工资等级表（salgrade）的表结构和表中的内容如下：
4、GROUP BY 案例演示
显示每个部门的平均工资和最高工资
在group by子句中指明按照部门号进行分组，在select语句中使用avg函数和max函数，分别查询每个部门的平均工资和最高工资。如下：
SELECT deptno, AVG(sal) AS 平均工资, MAX(sal) AS 最高工资
FROM emp
GROUP BY deptno;
说明一下： 上述SQL会先将表中的数据按照部门号进行分组，然后各自在组内做聚合查询得到每个组的平均工资和最高工资。
显示每个部门的每种岗位的平均工资和最低工资
在group by子句中指明依次按照部门号和岗位进行分组，在select语句中使用avg函数和min函数，分别查询每个部门的每种岗位的平均工资和最低工资。如下：
SELECT deptno, job, AVG(sal) AS 平均工资, MIN(sal) AS 最低工资
FROM emp
GROUP BY deptno, job
ORDER BY deptno, job;
说明一下：
-
group by子句中可以指明按照多个字段进行分组，各个字段之间使用逗号隔开，分组优先级与书写顺序相同。
-
比如上述SQL中，当两条记录的部门号相同时，将会继续按照岗位进行分组。
5、HAVING 子句的使用
含有having子句的SQL如下：
HAVING 子句用于对 GROUP BY 的分组结果进行筛选，类似于 WHERE，但作用于分组后的数据
SELECT ... FROM table_name [WHERE ...] [GROUP BY ...] [HAVING ...] [order by ...] [LIMIT ...];说明一下：
-
SQL中大写的表示关键字，[ ]中代表的是可选项。
-
SQL中各语句的执行顺序为：where、group by、select、having、order by、limit。
-
having子句中可以指明一个或多个筛选条件。
having子句和where子句的区别
-
where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面。
-
where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选。
-
where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名。
-
SQL中各语句的执行顺序
统计每个部门的平均工资
在group by子句中指明按照部门号进行分组，在select语句中使用avg函数查询每个部门的平均工资。如下：
SELECT deptno, AVG(sal) AS 平均工资 FROM emp GROUP BY deptno;
显示平均工资低于2000的部门及其平均工资
-
先统计每个部门的平均工资。
-
然后通过having子句筛选出平均工资低于2000的部门。
SELECT deptno, AVG(sal) AS 平均工资
FROM emp
GROUP BY deptno
HAVING 平均工资 < 2000;
显示员工数量超过3人的部门
SELECT deptno, COUNT(*) AS 员工数量
FROM emp
GROUP BY deptno
HAVING COUNT(*) > 3;
显示平均工资高于2500且员工数超过1人的部门
SELECT deptno, COUNT(*) AS 员工数量, AVG(sal) AS 平均工资
FROM emp
GROUP BY deptno
HAVING 平均工资 > 2500 AND 员工数量 > 1;
复杂分组统计
按部门和职位统计，并筛选出平均工资大于1500的分组
SELECT deptno, job, COUNT(*) AS 人数, AVG(sal) AS 平均工资
FROM emp
GROUP BY deptno, job
HAVING 平均工资 > 1500
ORDER BY deptno, 平均工资 DESC;
结合 WHERE 和 HAVING 的完整示例
-- 统计各部门销售人员(SALESMAN)的平均工资，且只显示平均工资>1400的部门
SELECT deptno, AVG(sal) AS 销售平均工资
FROM emp
WHERE job = 'SALESMAN' -- 先过滤出销售人员
GROUP BY deptno -- 按部门分组
HAVING 销售平均工资 > 1400; -- 再过滤分组结果
6、GROUP BY 使用规则和最佳实践
重要规则：
-
SELECT 中的非聚合列：必须出现在 GROUP BY 子句中
-
聚合函数位置：可以出现在 SELECT、HAVING 子句中
-
执行顺序：WHERE → GROUP BY → HAVING → SELECT → ORDER BY
性能优化建议：
-
索引优化：对 GROUP BY 的列建立索引
-
减少分组字段：只对必要的列进行分组
-
先过滤后分组：使用 WHERE 提前减少数据量
-
避免过度分组：只在需要统计时使用 GROUP BY
7、WHERE vs HAVING 的区别
| 特性 | WHERE | HAVING |
|---|---|---|
| 执行时机 | 在分组前过滤 | 在分组后过滤 |
| 使用聚合函数 | 不能直接使用 | 可以直接使用 |
| 性能影响 | 减少分组数据量，提高性能 | 对已分组的数据过滤 |
| 适用场景 | 过滤原始记录 | 过滤分组结果 |
-- WHERE 和 HAVING 的典型使用场景
SELECT deptno, AVG(sal) AS avg_sal
FROM EMP
WHERE sal > 1000 -- 先过滤掉工资<=1000的员工
GROUP BY deptno
HAVING avg_sal > 2000; -- 再过滤平均工资>2000的部门通过合理使用 GROUP BY 和 HAVING，可以实现复杂的数据分组统计和分析需求。
八、SQL查询中各关键字的执行顺序（超重要！！！）
在SQL查询中，虽然我们书写的顺序是 SELECT...FROM...WHERE...，但数据库的实际执行顺序完全不同。理解这个顺序对于编写高效的SQL语句至关重要。
1、完整的执行顺序
1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE|ROLLUP (MySQL不支持)
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. LIMIT/OFFSET2、详细示例讲解
测试数据准备
-- 创建部门表
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50),
location VARCHAR(50)
);
-- 创建员工表
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary DECIMAL(10,2),
dept_id INT,
hire_date DATE
);
-- 插入测试数据
INSERT INTO departments VALUES
(1, '技术部', '北京'),
(2, '销售部', '上海'),
(3, '人事部', '广州'),
(4, '财务部', '深圳');
INSERT INTO employees VALUES
(1, '张三', 8000, 1, '2020-01-15'),
(2, '李四', 12000, 1, '2019-03-20'),
(3, '王五', 6000, 2, '2021-06-10'),
(4, '赵六', 9000, 2, '2020-11-05'),
(5, '钱七', 7500, 2, '2022-02-28'),
(6, '孙八', 11000, 3, '2018-09-12'),
(7, '周九', 8500, NULL, '2021-12-01');
3、分步执行顺序详解
1. FROM - 确定数据源
作用：确定查询的主要表
-- 示例：查询所有员工信息
SELECT * FROM employees;
-- 执行过程：
-- 第一步：FROM employees - 加载整个employees表到内存
-- 数据准备：7条员工记录
2. ON - 连接条件
作用：指定表连接的条件（如果有多表连接）
-- 示例：员工和部门连接
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- 执行过程：
-- 1. FROM employees, departments - 加载两个表
-- 2. ON e.dept_id = d.dept_id - 应用连接条件
-- 连接结果：
-- 张三-技术部, 李四-技术部, 王五-销售部, 赵六-销售部,
-- 钱七-销售部, 孙八-人事部, 周九-NULL
3. JOIN - 执行连接操作
作用：根据ON条件执行具体的连接类型（INNER, LEFT, RIGHT等）
-- 继续上面的例子
-- 3. JOIN - 执行LEFT JOIN操作，保留左表所有记录4. WHERE - 行级过滤
作用：过滤掉不满足条件的行（在分组前执行）
SELECT emp_name, salary, dept_id
FROM employees
WHERE salary > 8000 AND dept_id IS NOT NULL;
-- 执行过程：
-- 1. FROM employees - 加载7条记录
-- 2. WHERE salary > 8000 AND dept_id IS NOT NULL - 过滤
-- 过滤后剩余：李四(12000,1), 赵六(9000,2), 孙八(11000,3)
重要特性：WHERE中不能使用聚合函数
-- 错误示例（WHERE中不能使用聚合函数）
SELECT dept_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000; -- 报错！
-- 正确应该使用HAVING
SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 8000;
5. GROUP BY - 分组
作用：将数据按指定列分组
SELECT dept_id, AVG(salary) as avg_salary, COUNT(*) as emp_count
FROM employees
WHERE dept_id IS NOT NULL -- 先过滤掉NULL部门
GROUP BY dept_id;
-- 执行过程：
-- 1. FROM employees - 7条记录
-- 2. WHERE dept_id IS NOT NULL - 过滤后剩6条
-- 3. GROUP BY dept_id - 按部门分组：
-- 部门1: 张三(8000), 李四(12000) → 平均10000
-- 部门2: 王五(6000), 赵六(9000), 钱七(7500) → 平均7500
-- 部门3: 孙八(11000) → 平均11000
6. HAVING - 分组后过滤
作用：对分组后的结果进行过滤
SELECT dept_id, AVG(salary) as avg_salary, COUNT(*) as emp_count
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING AVG(salary) > 8000 AND COUNT(*) >= 1;
-- 执行过程：
-- 1-5步同上，得到三个分组
-- 6. HAVING AVG(salary) > 8000 - 过滤分组
-- 保留：部门1(10000), 部门3(11000)
-- 淘汰：部门2(7500)
7. SELECT - 选择列
作用：确定最终返回的列，可以包含表达式和别名
SELECT
dept_id,
AVG(salary) as avg_salary,
COUNT(*) as emp_count,
AVG(salary) * 1.1 as next_year_salary -- 表达式
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING AVG(salary) > 8000;
-- 执行过程：
-- 在分组过滤后，对每个分组计算SELECT中的表达式
8. DISTINCT - 去重
作用：去除重复行
-- 查询所有存在的部门ID（去重）
SELECT DISTINCT dept_id
FROM employees
WHERE dept_id IS NOT NULL;
-- 执行过程：
-- 1-7步执行完后，对结果进行去重
-- 原始数据: 1, 1, 2, 2, 2, 3
-- DISTINCT后: 1, 2, 3
9. ORDER BY - 排序
作用：对最终结果排序
SELECT dept_id, AVG(salary) as avg_salary
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING AVG(salary) > 7000
ORDER BY avg_salary DESC; -- 按平均工资降序
-- 执行过程：
-- 所有过滤分组完成后，对最终结果排序
-- 结果：部门3(11000), 部门1(10000), 部门2(7500)
10. LIMIT/OFFSET - 分页
作用：限制返回的行数，用于分页
SELECT emp_name, salary
FROM employees
WHERE salary > 7000
ORDER BY salary DESC
LIMIT 3 OFFSET 0; -- 第一页，每页3条
-- 执行过程：
-- 所有操作完成后，最后进行分页限制
4、完整综合示例
复杂查询示例
SELECT
d.dept_name,
COUNT(e.emp_id) as employee_count,
AVG(e.salary) as avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.hire_date >= '2020-01-01' -- 2020年后入职
AND e.salary IS NOT NULL
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(e.emp_id) > 0
AND AVG(e.salary) > 7000
ORDER BY avg_salary DESC
LIMIT 10;执行顺序分解：
-
FROM departments d - 加载部门表(4条)
-
LEFT JOIN employees e - 左连接员工表
-
ON d.dept_id = e.dept_id - 应用连接条件
-
WHERE - 过滤2020年前入职和salary为NULL的记录
-
GROUP BY - 按部门分组
-
HAVING - 过滤员工数>0且平均工资>7000的部门
-
SELECT - 选择要显示的列（在此之后可以使用select中的别名了）
-
ORDER BY - 按平均工资降序排序
-
LIMIT - 限制返回10条
5、常见面试问题
Q1: WHERE和HAVING的区别？
A:
-
WHERE在分组前过滤，HAVING在分组后过滤
-
WHERE中不能使用聚合函数，HAVING中可以
-
WHERE影响分组前的数据量，HAVING影响最终结果
Q2: 为什么SELECT中可以使用GROUP BY的别名？
A: 因为执行顺序是GROUP BY → HAVING → SELECT → ORDER BY，所以ORDER BY中可以使用SELECT的别名，但WHERE中不能。
Q3: 如何优化SQL性能？
A:
-
在WHERE条件字段加索引
-
先使用WHERE过滤大量数据，再进行GROUP BY
-
避免在WHERE中对字段进行函数操作
理解这个执行顺序有助于编写更高效、可读性更好的SQL查询语句。
九、别名的使用和定义规则（超重要！！！）
核心原则：别名的作用域从其定义点开始，向后生效。
1、表别名规则
-
定义位置： 在
FROM或JOIN子句中。 -
使用规则： 只能在定义该别名之后的书写的SQL部分中使用。
-
✅ 可以用在： 定义它的子句之后的任何地方。
-
在
FROM table_a AS a中定义的别名a，可以在后续的JOIN、WHERE、SELECT中使用。 -
在
JOIN table_b AS b中定义的别名b，可以在后续的另一个JOIN、WHERE、SELECT中使用。
-
-
❌ 不可以用在： 定义它的子句之前的书写的部分。
-
在
JOIN中定义的别名b，绝不能在FROM或其他先于它书写的JOIN中使用。
-
-
2、列别名规则
-
定义位置： 主要在
SELECT子句中。 -
使用规则： 只能在逻辑执行顺序晚于
SELECT的子句中使用。-
✅ 可以用在：
ORDER BY。 -
❌ 不可以用在：
WHERE,GROUP BY,HAVING（因为这些子句在SELECT之前执行）。
-
最终总结：表别名遵循“书写顺序”，必须先定义，后使用。列别名遵循“执行顺序”，必须在 SELECT 之后才能使用。
十、分组查询的核心概念
1、分组的目的与本质
分组的目的：对数据进行分类汇总，便于进行聚合统计。
分组的本质：按照指定列的不同取值，将数据划分为多个逻辑上的"子表"，然后对每个子表分别进行统计计算。
2、分组机制详解
分组依据：以指定列（如deptno）的不同行数据值作为分组标准
-
同一组内的该列数据值完全相同
-
每组数据可以被视为一个独立的逻辑单元进行聚合计算
分组过程：将原始表按照分组条件在逻辑上拆分成多个子表
-- 示例：按部门分组统计
SELECT deptno, COUNT(*), AVG(sal)
FROM emp
GROUP BY deptno;-
相当于将员工表按部门拆分成多个"部门子表"
-
然后分别对每个部门子表进行人数统计和平均工资计算
分组与聚合的关系：先分组，再聚合
-
GROUP BY定义如何划分组别 -
聚合函数（COUNT、SUM、AVG等）对每个组别进行统计计算
3、HAVING子句的作用
HAVING用于对分组聚合后的结果进行条件筛选：
-
WHERE：在分组前对原始数据进行筛选 -
HAVING：在分组后对聚合结果进行筛选
-- 筛选出平均工资大于5000的部门
SELECT deptno, AVG(sal) as avg_salary
FROM emp
GROUP BY deptno
HAVING avg_salary > 5000;4、"MySQL一切皆表"的重要理念
表的广义理解：
-
不仅限于磁盘上真实存在的物理表
-
中间查询结果、最终输出结果都是逻辑上的"表"
-
每个逻辑表都可以继续进行查询操作
统一的处理方式：
-
只要掌握单表的增删改查（CURD）
-
就能处理所有SQL场景，包括复杂的多层嵌套查询
-
对任意列进行条件筛选，对聚合结果进行筛选都遵循相同逻辑
实际应用启示：
-
将复杂查询分解为多个逻辑表的连续操作
-
每个操作阶段都产出新的逻辑表供后续使用
-
最终结果也是一个逻辑表，可以继续参与其他查询
5、核心要点总结
-
分组是基于列值的逻辑划分
-
聚合统计在分组基础上进行
-
HAVING用于聚合后的条件筛选
-
所有查询结果都是可继续操作的表结构
扫一扫
2212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
