SQL学习分享（进阶篇）

前言：本文综合难易程度与使用频率与大家一起学习SQL进阶的内容，其中涉及到的概念比较多，为此作者于文章末尾准备了SQL必练50题及答案供大家参考。

准备工作：入门篇的单一数据表已不满足本章要求，需要新建数据表

        入门篇中我们已经建立了一个student表，基于本章需求，需要新建三个数据表并且需要增加几个新同学，建表语句及插入数据语句如下所示，可以直接粘贴至Navicat中运行：

-- 创建course数据表
CREATE TABLE course ( c_id INT PRIMARY KEY, 
c_name VARCHAR ( 10 ) NOT NULL, 
t_id VARCHAR INT NOT NULL );
-- 创建score数据表
CREATE TABLE score (
s_id INT,
c_id INT,
score INT);
-- 创建teacher数据表
CREATE TABLE teacher (
t_id INT,
t_name VARCHAR ( 10 ));
-- 清空student数据表并插入数据
DELETE FROM student;
INSERT INTO student VALUES(1,'胡图图','男',6);
INSERT INTO student VALUES(2,'牛爷爷','男',55);
INSERT INTO student VALUES(3,'壮壮妈','女',33);
INSERT INTO student VALUES(4,'胡英俊','男',32);
INSERT INTO student VALUES(5,'壮壮','男',7);
INSERT INTO student VALUES(6,'张小丽','女',31);
INSERT INTO student VALUES(7,'小怪','男',2);
-- 向course数据表中插入数据
INSERT INTO course VALUES(1,'厨艺',2);
INSERT INTO course VALUES(2,'体育',3);
INSERT INTO course VALUES(3,'生活',1);
-- 向score数据表中插入数据
INSERT INTO score VALUES(1,1,30);
INSERT INTO score VALUES(1,2,90);
INSERT INTO score VALUES(1,3,50);
INSERT INTO score VALUES(2,1,60);
INSERT INTO score VALUES(2,2,50);
INSERT INTO score VALUES(2,3,80);
INSERT INTO score VALUES(3,1,80);
INSERT INTO score VALUES(3,2,50);
INSERT INTO score VALUES(3,3,80);
INSERT INTO score VALUES(4,1,70);
INSERT INTO score VALUES(4,2,60);
INSERT INTO score VALUES(4,3,70);
INSERT INTO score VALUES(5,1,90);
INSERT INTO score VALUES(5,2,25);
INSERT INTO score VALUES(5,3,55);
INSERT INTO score VALUES(6,1,95);
INSERT INTO score VALUES(6,2,65);
INSERT INTO score VALUES(6,3,100);
-- 向teacher数据表中插入数据
INSERT INTO teacher VALUES(1,'图图妈');
INSERT INTO teacher VALUES(2,'神厨小福贵');
INSERT INTO teacher VALUES(3,'健康哥哥');

第一章：SQL必知（约束、通配符、AND/OR、子查询、IN/EXISTS）

一、约束

常见约束共有以下六种：非空约束，唯一约束，默认值约束，主键约束，外键约束和检查约束

1.非空约束（NOT NULL）

• 注解：在向表中插入数据时，收到非空约束的列插入的值不可为空。
• 特点：
  • 强制性：当向非空约束列中输入空值时语句无法运行并报错。
• 用途：确保表中重要字段的完整性，避免因为缺失数据而导致的错误。

2. 唯一约束（UNIQUE）

• 注解：唯一约束用于保证表中某个列或列组合的值不重复。唯一约束的列可以为NULL值，但整个列中只能有一个NULL值。
• 用途：确保表中某个字段或字段组合的唯一性，常用于邮箱、电话号码等字段。

3.默认值约束（DEFAULT）

• 注解：默认约束用于为表中的列指定一个默认值（在约束后定义，需要符合数据格式）。当插入新记录时，如果某个列没有指定值，则会自动填充默认值。
• 特点：
  • 自动性：当插入新记录且未指定某列的值时，自动使用默认值。
• 用途：为表中的字段提供默认值，确保数据的完整性和一致性。

4. 主键约束（PRIMARY KEY）

• 注解：主键约束用于唯一标识数据库表中的每一条记录。主键列的值必须唯一，且不允许为空，主键是从候选键中选择而来的。
• 特点：
  • 一个表只能有一个主键，一个主键可以由多个列组成（复合主键）。
  • 最小性：候选键的最小性限定键中不能包含多余的列，删去任何一列都会使键失效。
• 用途：确保表中每条记录的唯一性，便于数据的查找、更新和删除。

5. 外键约束（FOREIGN KEY）

• 注解：外键所指向的列在此表中不作为主键，但是在其他表中作为主键存在。
• 特点：
  • 关联性：假设有父、子两表，表主键列在子表中作为外键列，在对子表中的外键列更改时需要满足更改后的数据在父表主键中存在；在对父表主键进行删改时，如果涉及到子表中使用到的数据，数据库会阻止该操作。因此在使用外键约束时要格外慎重。
• 用途：实现表之间的关联和数据完整性，确保一个表中的数据在另一个表中也有相应的记录。

6.检查约束（CHECK）

• 注解：检查约束用于限制表中某个列或列组合的值范围或格式。通过指定条件表达式，确保列中的值符合特定的要求。
• 用途：确保表中数据的准确性和合理性，如年龄范围、邮箱格式等。

7.添加约束的方式

在创建数据表时添加约束：在列名与格式后直接增加约束即可。

在已存在的表上添加约束：区分对列定义的约束与作为独立对象添加的约束。

对列定义约束（NOT NULL,DEFAULT）时，遵循以下格式：

ALTER TABLE 表名  
MODIFY COLUMN 列名 数据类型 NOT NULL/DEFAULT 默认值;

作为独立对象添加的约束遵循以下格式：

ALTER TABLE 表名  
ADD CONSTRAINT 约束名 UNIQUE (列名1, 列名2, ...)/PRIMARY KEY (列名)/CHECK (条件);

其中外键形式由于涉及父表子表，格式较为特殊：

ALTER TABLE 子表名  
ADD CONSTRAINT 约束名 FOREIGN KEY (子表列名) REFERENCES 父表名(父表列名);

二、通配符

        通配符是一种具有特殊含义的字符，在SQL语句中常搭配like使用，用于执行模糊搜索指令，以替代一个或多个字符，在SQL中通配符有以下两种形式。

1. 百分号（%）

• 用途：可以代表零个、一个或多个字符，是SQL中最常用的通配符之一。
• 示例：
  • SELECT * FROM 表名 WHERE 列名 LIKE 'abc%'：匹配以"abc"开头的任意字符。
  • SELECT * FROM 表名 WHERE 列名 LIKE '%abc'：匹配以"abc"结尾的任意字符。
  • SELECT * FROM 表名 WHERE 列名 LIKE '%abc%'：匹配包含"abc"的任意字符。

2. 下划线（_）

• 用途：仅代表单个的字符，与百分号（%）不同，下划线只能匹配一个字符。
• 示例：
  • SELECT * FROM 表名 WHERE 列名 LIKE 'a_'：匹配以"a"开头的两个字符的字符串。
  • SELECT * FROM 表名 WHERE 列名 LIKE '_a'：匹配以"a"结尾的两个字符的字符串。

三、AND/OR

        AND & OR 运算符用于基于一个以上的条件对记录进行过滤，其中优先级AND高于OR，但是可以使用“（）”来改变运算顺序（“（）”优先级最高）。

四、子查询

        子查询是一种嵌套在其他查询（如SELECT、INSERT、UPDATE、DELETE等）中的查询语句。分为非关联子查询与关联子查询，非关联子查询指子查询可以独立于外部查询进行执行和评估。由于它不依赖于外部查询的具体行数据，因此只需要执行一次。

        关联子查询的执行与外部查询的每一行数据相关联。它需要根据外部查询的每一行数据进行多次评估。在关联子查询中，子查询中通常会引用外部查询中的某些列，这些列的值在每次外部查询迭代时都会改变。

        最简单的区分方法就是看子查询中是否存在外查询中的列，如果不存在，则说明子查询的执行与外查询无关，为非关联子查询，否则即为关联子查询。

五、IN/EXISTS

1.IN的用途（值匹配）（column_name(s)表示一个或多个列名）

SELECT column_name(s)  
FROM table_name  
WHERE column_name IN (value1, value2, ...);  
  
-- 或者使用子查询  
SELECT column_name(s)  
FROM table_name  
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

        如上述语句所示，IN用于在WHERE子句中指定一个值列表，这个列表内的值可以是直接给出的，也可以是子查询得到的，当查询列中某值等于列表中某值时，返回该值对应行。

2.EXISTS的用途（返回行）

SELECT column_name(s)  
FROM table_name  
WHERE EXISTS  
  (SELECT 1 FROM another_table WHERE another_table.column_name = table_name.column_name);

        如上述语句所示，EXISTS作为逻辑运算符，用于检验子查询是否返回行，如果子查询至少返回一行，则EXISTS结果为真，返回主查询数据。“select 1”用于检查是否存在相匹配的记录。

第二章：SQL必会（连接、函数、分组）

一、连接（别名）

        之前我们已经学会了如何在一张表中进行查询操作，但实际应用中我们常需要对多张表进行查询操作，这里要使用连接操作，连接操作用于合并两个或多个表的行（当对应列有匹配值时）。常见的连接共有四种：内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（LEFT JOIN）与全连接（FULL JOIN）。以下是连接语句示例（以student表与score表为例）：

SELECT s_name,s_age
FROM student AS st
INNER JOIN score as sc
ON st.s_id=sc.s_id
WHERE sc.score>60;

        其中SELECT后接抽取目标列，FROM后接连接表student，AS用于命名表别名，st为student表别名，INNER JOIN表示内连接后连接表score，ON后接连接条件即两表间公共字段，这里要注意书写字段前缀，因为连接时公共字段会重复，因此需要用表别名作前缀用于区分。

        这里由于两表中公共字段s_id均为01—06，因此四种连接结果相同，连接结果如下所示，左图为有筛选，右图为无筛选。通过连接定义可知，当两表连接公共字段的数据范围一致时，四种连接的结果相同，当左表范围大于右表时，需要左连接才能使左表范围全部解释，其他连接也同理。

二、分组（GROUP BY）

        分组（GROUP BY）是SQL中与函数高度相关的操作，在SQL执行顺序中，首先由FROM调用数据表，而后使用WHERE筛选，在使用GROUP BY对筛选结果分组后，进行函数运算，再使用HAVING筛选运算结果经过表达式计算后由SELECT抽取目标列，最后使用ORDER BY排序生成结果。上述归根结底还是对SQL执行顺序的复述，只有对这种基础且关键的东西熟记于心，才能做到在编写SQL语句时得心应手，当遇到问题时先走一遍执行顺序往往可以得到新的思路。

        尽管分组操作与函数操作高度相关，但是两者均可以独立作用，其中单独使用分组操作的情况非常少，比如使用分组达到排序的效果，但限制非常多且不如专门的排序（ORDER BY）操作好用，因此可以不考虑单独使用分组操作的情况。在使用GROUP BY子句与聚合函数时，SQL中做了很多限定条件：

• 在 SELECT 列表中，除了聚合函数计算的列之外，所有其他列都必须包含在 GROUP BY 子句中。（当GROUP BY中为某表主键时例外）
• 如果 GROUP BY 子句省略，那么整个结果集被视为一个分组，这通常在使用聚合函数但不需要按特定列分组时很有用。
• GROUP BY 子句可以包含多个列，这意味着结果集将基于这些列的组合值进行分组。

        上述第一条中的例外是基于GROUP BY子句的性质得来的，上述条件也都是为了满足这个性质，我们不妨称之为对应唯一性，这个性质是指对于GROUP BY子句中的“分组限定列”的每一数据值，查询列中都只能查询到唯一对应数据值。这个性质保证了分组后的结果中，“分组限定列”中的值都是唯一的，达到分组效果。单从文字理解或许比较晦涩难懂，不妨用实例帮助我们理解，依旧要使用student表与score表。下面分别是student表与score表，其中student表主键为s_id，score表无主键。

使用同一格式同种功能的GROUP BY分组语句分别对两表进行查询。

-- 以student表主键s_id作为分组列查询
SELECT s_name,s_sex,s_age
FROM student
GROUP BY s_id;
-- 以score表非主键列s_id作为分组列查询
SELECT c_id,s_score
FROM score
GROUP BY s_id;
-- 以student表非主键列s_name作为分组列查询
SELECT s_id,s_sex,s_age
FROM student
GROUP BY s_name;
-- 以score表非主键列c_id作为分组列查询
SELECT s_id,s_score
FROM score
GROUP BY c_id;

查询结果如下所示：

        我们发现只有使用某表主键列作为GROUP BY后查询列时，该表中非主键列不在分组列中时也可以作为查询列输出；如果某列满足候选键条件例如第三次查询中，在此表中姓名可以唯一决定某一列）但是没有作主键声明，也无法例外，这就是主键的特殊之处，也是GROUP BY子句的例外，大家了解即可。

三、常用聚合函数（COUNT( ); SUM( ); AVG( ); MAX( ); MIN( )；）

        聚合函数用于对一组值执行计算并返回对应组的单个值。聚合函数自动忽略 NULL 值，但 COUNT(*) 除外，它会计算包括 NULL 在内的所有行。

• AVG(column)：返回指定列的平均值。仅用于数字列并自动忽略NULL值。
• COUNT(column) 或 COUNT(*)：返回指定列中非NULL值的数量（COUNT(column)）或表中的行数（包括NULL值，COUNT(*)）。
• MAX(column)：返回指定列的最大值，忽略NULL值。可用于数字、字符和日期时间列。
• MIN(column)：返回指定列的最小值，忽略NULL值。可用于数字、字符和日期时间列。
• SUM(column)：返回指定列中所有值的总和，忽略NULL值。仅用于数字列。

        注意聚合函数返回的是分组后对应组中对数据处理后的单个数值也就是一行数据，这要求查询列数据与分组列数据要一一对应，如果分组列中的一个数据对应多个查询列中的数据，将会导致查询得到的数据过多无法被一行装载，导致系统报错，这与GROUP BY性质要求一致。

        为帮助大家理解聚合函数结合GROUP BY子句的应用，下面以score表为例，为大家展示单独使用聚合函数和联合使用聚合函数加GROUP BY子句的查询语句与结果：

score数据表

-- 从score表中抽取成绩最高者的学号与成绩 
SELECT MAX(s_score) AS '最高分'
FROM score;

-- 从score表中抽取各学生最高的分数
SELECT s_id,MAX(s_score)
FROM score
GROUP BY s_id;

-- 从score表中抽取各科最高的分数 
SELECT c_id,MAX(s_score)
FROM score
GROUP BY c_id;

        现在我们已经大致了解了如何进行基本的SQL操作，需要通过一定量的练习来帮助我们熟悉并理解相关过程，第三篇文章我为大家准备了SQL必练50题，供大家学习参考。