MySQL基础（三）表关系及数据的增删改查

这一部分主要使用SQL中的DML，数据库操作语言（data manipulation language），对数据库数据进行增、删、改、查操作，作为前提，先介绍关系型数据库中的表关系。

关系型数据库（RDBMS）

MySQL作为关系型数据库，数据是存储在表中的，假如设计一个订单系统，可能包含以下信息：

• 供应商（Vendors）
• 产品目录（Products）
• 客户列表（Customers）
• 订单（Orders）
• 可能还会为产品打上一些标签（比如，新品，进口商品，本地特产等）

每个表中都有个特殊的列（字段），称为主键（Primary Key），其值用来唯一标识表中的一行数据（记录）。表中还可以有称为外键（Foreign Key）的列，引用同一个表或不同表中某行的主键。因此，通过外键，表与表之间是有关联关系的，这也是建立关系型数据库模型的基础。

下面我们试着设计一下这个订单系统的表结构，就拿其中的供应商（Vendors）和产品目录（Products）来说：

供应商表（Vendors）：

列 说明 主键/外键 vendor_id 供应商唯一ID， PK vend_name 供应商名字 vend_address 供应商地址

产品目录表（Products）：

列 说明 主键/外键 prod_id 产品唯一ID PK prod_name 产品名字 prod_price 产品价格 vendor_id 供应商ID（关联Vendors表的vendor_id列） FK

Products表存放产品记录，每个产品都有prod_name, prod_price，并且有唯一的prod_id作为主键。除此之外，还有一个外键vendor_id，引用Vendors表中的vend_id，通过这种方式，为每个产品指定了供应商。我们不需要在产品表中再添加更多的列来存储其供应商的具体信息，以后直接根据这个外键，去Vendors表中查询即可。

从中我们可以看出，关系型数据库在存储数据时是很高效的，避免了数据冗余。而且，关系型数据库的可伸缩性远比非关系型数据库要好。比如，对数据库中供应商名字，地址等信息的修改，可以只更新Vendors表中的记录，相关表中的记录不用改动。

但是另一方方面，将数据分别存放在不同的表中有时是很复杂的，我们需要查询多个关联的表，才能拿到我们想要的数据。因此，在设计表结构，以及编写SQL语句时，需要很好地理这种关联关系，知道什么信息存在什么表中。

表关系种类

一对多

比如上面的供应商和产品，一个供应商可能提供多个产品，而一个产品只能属于一个供应商，在多的一边（产品表）通过Foreign Key 描述这种关系。

一对一

比如，一张订单对应一张订单详情；一篇文章摘要，对应一篇具体的文章内容。一对一关系的Foreign Key建在关系的哪边都行。

多对多

这个是相对复杂的一种，但是也很好理解。还是上面的例子，比如给产品打标签，一个产品可能有多个标签（”进口“， ”新品“），而一个标签下可以对应多个产品（进口商品可能有很多）。

特别要注意的是，如果要描述多对多关系，需要借助第三张表，这个表称为关系表，这张表实际存储时，看起来是这样的：

id (PK) prod_id (FK) tag_id (FK) 1 1 1 2 1 2 3 2 1 4 3 1

除了自增的主键id，它还有两个外键，一个prod_id关联产品表，一个tag_id关联标签表。

插入数据（INSERT INTO）

这里将以教学管理为例，来创建数据库，创建表，插入数据。最后一部分的综合练习，将基于接下来要创建的数据。

创建表并定义约束关系

首先，创建一个数据库，就命名为school吧

CREATE DATABASE school CHARSET utf8;

关于表创建和约束的更多详细，请参考MySQL基础（二）数据库、表的创建及操作

下面我们我们将创建5张表，分别是：

• 班级表（Class）
• 课程表（Course）
• 成绩表（Score）
• 学生表（Student）
• 教师表（Teacher）

其中班级表和课程表是一对多关系；班级表和学生表是一对多关系；教师表和课程表是一对多关系；学生表和课程表是多对多关系。

-- --------------------------- 创建班级表-- -------------------------CREATE TABLE Class(  cls_id        int          NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK  caption       char(30)     NOT NULL );-- --------------------------- 创建课程表-- -------------------------CREATE TABLE Course(  crs_id        int          NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK  crs_name      char(30)     NOT NULL ,  teacher_id    int          NOT NULL  -- FK );-- --------------------------- 创建成绩表-- -------------------------CREATE TABLE Score(  scr_id        int          NOT NULL PRIMARY KEY AUTO_INCREMENT ,  -- PK  num           tinyint      NOT NULL ,  student_id    int          NOT NULL ,  -- FK  course_id     int          NOT NULL    -- FK);-- --------------------------- 创建学生表-- -------------------------CREATE TABLE Student(  std_id        int          NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK  gender        enum('女', '男')   NOT NULL ,  std_name      char(30)     NOT NULL ,  class_id      int          NOT NULL  -- FK);-- --------------------------- 创建教师表-- -------------------------CREATE TABLE Teacher(  tea_id        int          NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK  tea_name      char(30)     NOT NULL );-- --------------------------- 定义外键-- -------------------------ALTER TABLE CourseADD CONSTRAINT FK_Course_TeacherFOREIGN KEY (teacher_id) REFERENCES Teacher (tea_id);ALTER TABLE ScoreADD CONSTRAINT FK_Score_StudentFOREIGN KEY (student_id) REFERENCES Student (std_id);ALTER TABLE ScoreADD CONSTRAINT FK_Score_CourseFOREIGN KEY (course_id) REFERENCES Course (crs_id);ALTER TABLE StudentADD CONSTRAINT FK_Student_ClassFOREIGN KEY (class_id) REFERENCES Class (cls_id);

定义外键的方式

注意：定义外键的数据类型一定要和关联表主键的数据类型一致！

建表时定义

CREATE TABLE Course(  crs_id        int          PRIMARY KEY AUTO_INCREMENT ,   crs_name      char(30)     NOT NULL ,  teacher_id    int          NOT NULL ,  CONSTRAINT FK_Course_Teacher  -- 外键名  FOREIGN KEY (teacher_id)  -- 外键字段  REFERENCES Teacher (tea_id)  -- 关联表主键字段);

建表后定义

CREATE TABLE Course(  crs_id        int          PRIMARY KEY AUTO_INCREMENT ,   crs_name      char(30)     NOT NULL ,  teacher_id    int          NOT NULL );ALTER TABLE CourseADD CONSTRAINT FK_Course_Teacher  -- 外键名Foreign KEY (teacher_id) REFERENCES Teacher (tea_id);  -- 外键字段和关联表主键字段

删除外键

格式

ALTER TABLE 表名DROP FOREIGN KEY 外键名；

示例

ALTER TABLE CourseDROP FOREIGN KEY FK_Course_Teacher;

插入数据

插入单条数据

格式

INSERT INTO 表名 [(fld1, fld2, ..., fldn)] VALUES (val1, val2, ..., valn);

注意：表名后的字段名可以省略，但建议加上，VALUES的值将依次匹配字段名，而不一定非要按照表中字段的实际次序；有默认值的字段或自增字段可不提供值。另外，对于自增id，如果插入数据时指定了非连续的值，比如表记录最后一行id是3，插入数据时指定id是9，那么以后再插入数据时，id将从9开始自增。

插入多条数据

格式

INSERT INTO 表名 [(fld1, fld2, ..., fldn)] VALUES (val1, val2, ..., valn),       (val1, val2, ..., valn),        -- ...       (val1, val2, ..., valn);

示例

下面，我们将为上面创建的那5张表填充数据：

-- ---------------------------- 插入班级数据-- --------------------------INSERT INTO Class (caption)VALUES ('三年二班'), ('三年三班'), ('一年二班'), ('二年九班');-- ---------------------------- 插入教师数据-- --------------------------INSERT INTO Teacher (tea_name)VALUES ('张磊老师'), ('李平老师'), ('刘海燕老师'), ('朱云海老师'), ('李杰老师');-- --------------------------- 插入课程数据-- -------------------------INSERT INTO Course (crs_name, teacher_id)VALUES ('生物', 1), ('物理', 2), ('体育', 3), ('美术', 2);-- --------------------------- 插入学生数据-- -------------------------INSERT INTO Student (gender, std_name, class_id)VALUES ('男', '理解', 1), ('女', '钢蛋', 1), ('男', '张三', 1), ('男', '张一', 1),       ('女', '张二', 1), ('男', '张四', 1), ('女', '铁锤', 2), ('男', '李三', 2),        ('男', '李一', 2), ('女', '李二', 2), ('男', '李四', 2), ('女', '如花', 3),       ('男', '刘三', 3), ('男', '刘一', 3), ('女', '刘二', 3), ('男', '刘四', 3);-- ---------------------------- 插入成绩数据-- --------------------------INSERT INTO Score (num, student_id, course_id)VALUES (10,1,1),(9,1,2),(66,1,4),(8,2,1),(68,2,3),(99,2,4),(77,3,1),(66,3,2),       (87,3,3),(99,3,4),(79,4,1),(11,4,2),(67,4,3),(100,4,4),(79,5,1),(11,5,2),       (67,5,3),(100,5,4),(9,6,1),(100,6,2),(67,6,3),(100,6,4),(9,7,1),(100,7,2),       (67,7,3),(88,7,4),(9,8,1),(100,8,2),(67,8,3),(88,8,4),(91,9,1),(88,9,2),       (67,9,3),(22,9,4),(90,10,1),(77,10,2),(43,10,3),(87,10,4),(90,11,1),(77,11,2),       (43,11,3),(87,11,4),(90,12,1),(77,12,2),(43,12,3),(87,12,4),(87,13,3);

修改和删除数据

修改数据（UPDATE）

格式：

UPDATE 表名SET fld1=val1, fld2=val2, ... -- 赋值，多个字段之间以逗号分隔WHERE 条件;  -- 过滤条件

注意：如果没有WHERE子句，将更新所有的行！

示例：

-- 将学号为2，课程id为1 的成绩加10分UPDATE scoreSET num = num + 10WHERE student_id=2 and course_id=1;

删除数据

DELETE

DELETE FROM 表名WHERE 条件;  -- 过滤条件

删除记录：先查出所有符合条件的记录，然后一条条删除，数据很多比较慢。如果有自增id，再插入新数据时，id会在原有的基础上自增。

TRUNCATE

如果需要删除整张表的记录，那么使用TRUNCATE速度更快，它是直接删除表，然后新建一张带原来字段的表。

TRUNCATE TABLE 表名;

单表查询（SELECT）

基本格式：

SELECT fld, ...    -- 选择字段FROM tablename     -- 选择表[WHERE ...]        -- WHERE 条件[GROUP BY ...]     -- 分组[HAVING ...]       -- HAVING 条件[ORDER BY ...]     -- 排序LIMIT ... ;        -- 限制条数

WHERE子句

数据库表一般包含大量的数据，很少需要检索表中的所有行，通常会根据需要指定过滤条件。WHERE子句就是用来指定过滤条件的。

WHERE子句操作符

操作符 说明 操作符 说明 = 等于 LIKE ‘_…%’ 模糊搜索 != 不等于 _ 通配符：匹配一个字符 > 大于 % 通配符：匹配任意字符任意个数 >= 大于等于 AND 逻辑与 < 小于 NOT 逻辑非 <= 小于等于 OR 逻辑或 BETWEEN x AND y 在x与y之间（左右包含） REGEXP 正则匹配 IN (val1, val2, …) 指定条件范围

AS 显示别名

更改列名显示

格式：

SELECT fieldname AS fieldname_new FROM tablename;

示例：

SELECT tea_id as ID, tea_name as 教师姓名 FROM Teacher;+----+------------+| ID | 教师姓名   |+----+------------+|  1 | 张磊老师   ||  2 | 李平老师   ||  3 | 刘海燕老师 ||  4 | 朱云海老师 ||  5 | 李杰老师   |+----+------------+5 rows in set (0.00 sec)-- 也可以省略ASSELECT tea_id ID, tea_name 教师姓名 FROM Teacher;

列值相加

假如有学生各科成绩表，使用别名表示学生总分，使用+将列值相加

SELECT name as 姓名, math + chinese + english as 总成绩 FROM ExamResult;  -- 表不存在，这里只是示例

ORDER BY 排序

根据指定列排序，ASC升序，DESC降序

-- 查询成绩表中课程id为2的分数和学生，并以分数倒序显示SELECT num AS 分数, student_id AS 学生ID FROM ScoreWHERE course_id = 2ORDER BY num DESC;+------+--------+| 分数 | 学生ID |+------+--------+|  100 |      6 ||  100 |      7 ||  100 |      8 ||   88 |      9 |-- ......+------+--------+11 rows in set (0.14 sec)

LIMIT 限制查询条数

-- 查询学生表中3个姓张的学生SELECT std_id, gender, std_nameFROM StudentWHERE std_name LIKE '张%'LIMIT 3;+--------+--------+----------+| std_id | gender | std_name |+--------+--------+----------+|      3 | 男     | 张三     ||      4 | 男     | 张一     ||      5 | 女     | 张二     |+--------+--------+----------+3 rows in set (0.28 sec)

使用LIMIT 可以提高数据库性能。对于上面的这个模糊搜索，即使已经找到了，也将遍历整个数据库，如果数据库中存储了海量数据，这种全表扫描将极大影响性能。使用LIMIT后，找到指定条数的记录后，就会停止查找。

LIMIT [offset,] rows其实可以接受两个参数，第一参数是偏移。默认不写是偏移0。在最后综合练习的第20题，你将看到它的应用。

聚合函数

聚合函数用来汇总数据：

函数 说明 AVG() 返回某列的平均值 COUNT() 返回某列的行数 MAX() 返回某列的最大值 MIN() 返回某列的最小值 SUM() 返回某列子和

AVG

-- 从成绩表中，查找学生id是3的学生的平均成绩SELECT AVG(num) AS avg_numFROM ScoreWHERE student_id=3;+---------+| avg_num |+---------+| 82.2500 |+---------+

COUNT

该函数有两种使用方式：

• COUNT(*) 对表中行的数目进行计数
• COUNT(column) 对特定列中具有值的行进行计数（会忽略列值为NULL的行）

-- 查找学生总数SELECT COUNT(*) AS total_num FROM Student;+-----------+| total_num |+-----------+|        16 |+-----------+

MAX MIN

• MAX()一般用来找出最大的数值或日期，对于文本数据，将返回该列排序后的最后一行
• 如果SELECT选择多列，对其中一列使用MAX()统计最大值，比如下面这个例子，查询出来的某列具有最大值的行和其它列所在的行，并不是同一行

-- 统计课程3的最高成绩SELECT student_id, MAX(num)FROM ScoreWHERE course_id=3;+------------+----------+| student_id | MAX(num) |+------------+----------+|          2 |       87 |+------------+----------+-- 事实上，课程3最高成绩对应的学生id是3SELECT student_id, numFROM ScoreWHERE course_id=3ORDER BY num DESCLIMIT 3;+------------+-----+| student_id | num |+------------+-----+|          3 |  87 ||         13 |  87 ||          2 |  68 |+------------+-----+-- 第一次查询结果中的2，只是第一次出现课程3的那一行的student_idSELECT student_id, numFROM ScoreWHERE course_id=3LIMIT 3;+------------+-----+| student_id | num |+------------+-----+|          2 |  68 ||          3 |  87 ||          4 |  67 |+------------+-----+

MIN()函数同理，不赘述。

求最大值和最小值，有时候通过DESC ASC排序，配合LIMIT更方便，比如最后综合练习中的19题。

SUM

-- 计算订单号为20005的订单总金额SELECT SUM(item_price*quantity) AS total_priceFROM OrderItemsWHERE order_num = 20005;

注意：

如上所示，利用标准的算术操作符，所有的聚集函数都可以用来执行多个列上的计算！

ALL & DISTINCT

• ALL 对所有行执行计算，是默认行为，不需要指定
• DISTINCT 只对包含不同列值的行进行计算（因此聚合函数必须指定列名），需要显示地指定该参数

以查询平均成绩为例，二者的结果是不一样的，DISTINCT参数会过滤掉重复的成绩再计算

SELECT AVG(num) FROM Score;  -- 默认行为是 ALL+----------+| AVG(num) |+----------+|  67.2979 |+----------+SELECT AVG(DISTINCT num) FROM Score;  +-------------------+| AVG(DISTINCT num) |+-------------------+|           60.2941 |+-------------------+

在最后综合练习的第11题中，你也将看到相关应用。

组合聚合函数

SELECT语句可以根据需要包含多个聚集函数，比如：

SELECT COUNT(*) as num_items,       MIN(num) as score_min,       MAX(num) AS score_max,       AVG(num) AS score_avgFROM Score;+-----------+-----------+-----------+-----------+| num_items | score_min | score_max | score_avg |+-----------+-----------+-----------+-----------+|        47 |         9 |       100 |   67.2979 |+-----------+-----------+-----------+-----------+

其它内置函数

文本处理函数

函数 说明 UPPER() 大写 LOW() 小写 LENGTH() 返回字符串长度（Bytes） SOUNDEX() 返回字符串的SOUNDEX值 CONCAT() 拼接字段/字符串

LENGTH()

长度是字节（在utf8编码中，一个中文字符占3个字节）

SOUNDEX()

将任何文本字符串转化为描述其语言表示的字母数字模式的算法，SOUNDEX 考虑了类似的发音字符和音节，使得能够对字符串进行发音比较，而不是字母比较。（看这个介绍就知道，不支持中文，实测确实如此，很遗憾…）这里给出一个例子：

假如有一张顾客表，其联系名为Michael Green，但如果记错了，输入了 Michelle Green 来查找，肯定是找不到的。如果用SOUNDEX()函数进行搜索，就可以解决这个问题，它匹配所有发音类似于Michael Green的联系名

SELECT cust_name, cust_contactFROM CustomersWHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

CONCAT()

MySQL使用CONCAT函数来拼接字段和字符串（其它DBMS中可能使用 + 和 ||）

-- 将tea_id字段与tea_name字段联合显示SELECT CONCAT('(', tea_id, ')', tea_name) AS '(ID)姓名'FROM Teacher;+---------------+| (ID)姓名      |+---------------+| (1)张磊老师   || (2)李平老师   || (3)刘海燕老师 || (4)朱云海老师 || (5)李杰老师   |+---------------+

日期和时间处理函数

这一块儿的函数比较多，要用的时候可以根据需求，去查询文档

GROUP BY分组和 HAVING过滤

GROUP BY可以将数据分为多个逻辑组，再对每个组进行聚合计算

注意区别WHERE和HAVING：

• WHERE过滤行，HAVING过滤分组
• WHERE在数据分组前进行过滤， HAVING在数据分组后进行过滤，结合GROUP BY子句使用

-- 具有两个以上的产品，且产品价格大于4的供应商SELECT vend_id, COUNT(*) AS num_prodsFROM ProductsWHERE prod_price >= 4   -- 产品价格大于4GROUP BY vend_id        -- 根据供应商分组HAVING COUNT(*) >= 2    -- 具有两个以上的产品-- 从成绩表中，查询每门课的平均成绩SELECT AVG(num) AS 'score_avg', course_idFROM ScoreGROUP BY course_id;+-----------+-----------+| score_avg | course_id |+-----------+-----------+|   54.2500 |         1 ||   65.0909 |         2 ||   64.4167 |         3 ||   85.2500 |         4 |+-----------+-----------+4 rows in set (0.00 sec)

InnoDb 外键约束支持的ON语句

这里将建立外键的表称为子表，外键引用的表称为父表（主表）。

外键约束是双重的：

• 对子表来说，如果引用的记录在父表中找不到，那么不允许在子表中插入或更新数据
• 对父表来说，如果某记录被子表引用，那么该记录不能被随便删除（具体取决于子表在定义外键约束时的ON语句）

外键约束的四种ON语句方式：

CASCADE

级联删除：如果父表的记录被删除，那么子表中对应的记录也被自动删除

FOREIGN KEY (xx_id) REFERENCES parent_tablename (id)ON DELETE CASCADE;

SET NULL

删除父表中某条记录时，将子表中引用了该记录的外键设为NULL

FOREIGN KEY (xx_id) REFERENCES parent_tablename (id)ON DELETE SET NULL;

RESTRICT

如果子表中引用了父表记录，则不允许对父表中的该记录进行删除。

NO ACTION

在MySQL中同RESTRICT。

组合查询（UNION）

UNION

通过UNION操作符，可以执行多个SELECT查询，并将结果作为一个查询结果返回，这就是组合查询（也称并查询，复合查询 compound query）。

UNION使用场景：

• 在一个查询中从不同的表返回结果
• 对一个表执行多个查询，按一个查询返回结果

使用形式：

SELECT column1, column2, ...FROM tablenameWHERE ...UNION           -- 使用UNION连接多个查询SELECT column1, column2, ...FROM tablenameWHERE ...

比如，从学生表中查询性别为“男”，或者 姓”李“的学生：

SELECT std_id, std_name, gender FROM Student WHERE gender = '男'UNIONSELECT std_id, std_name, gender FROM Student WHERE std_name LIKE '李%';+--------+----------+--------+| std_id | std_name | gender |+--------+----------+--------+|      1 | 理解     | 男     ||      3 | 张三     | 男     ||      4 | 张一     | 男     ||      6 | 张四     | 男     ||      8 | 李三     | 男     ||      9 | 李一     | 男     ||     11 | 李四     | 男     ||     13 | 刘三     | 男     ||     14 | 刘一     | 男     ||     16 | 刘四     | 男     ||     10 | 李二     | 女     |+--------+----------+--------+

当然以上要求也可以用WHERE子句来做，可能还更简洁，用OR连接两个条件即可。事实上，UNION和WHERE常常是可以互换的，但是对于较为复杂的过滤条件，或者从多个表中检索数据时，使用UNION可能会更简单。

可以看到UNION非常容易使用，但在进行组合时，要注意几条规则：

• UNION必须由两条或以上的SELECT语句组成，中间用UNION连接
• UNION中的每个查询必须包含相同的列，表达式或聚合函数（次序可以不同）
• 列数据类型必须兼容

UNION ALL

默认UNION会从查询结果中取出重复的行，如果不希望这么做，可以使用UNION ALL

对组合查询排序

在组合查询中，如果要对查询结果排序，只能使用一条ORDER BY子句，且必须位于最后一条SELECT语句之后。

多表查询（SELECT）

内联结（INNER JOIN）

多表查询需要利用联结，使多个表返回一组输出。联结是SQL中最重要，最强大的特性！

最基本的方式是，在查询时，指定要联结的所有表，以及关联它们的方式即可。

基本格式：

SELECT column1, column2, ...    -- 选择位于不同表中要检索的列FROM tablename1, tablename2     -- 要联结的表WHERE ...                       -- 指定联结条件，非常重要！！！

我们看一个例子：

SELECT crs_name, tea_nameFROM Course, TeacherWHERE Course.teacher_id = Teacher.tea_id;  -- 完全限定列名+----------+------------+| crs_name | tea_name   |+----------+------------+| 生物     | 张磊老师   || 物理     | 李平老师   || 体育     | 刘海燕老师 || 美术     | 李平老师   |+----------+------------+

完全限定列名：用一个句点分隔表名和列名，以避免列名混淆。

笛卡儿积

如果没有WHERE子句指定联结条件，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能匹配在一起。这样返回的结果称为笛卡儿积，检索出的行的数目等于两个表行数的乘积。

内联接

上面这种联结方式称为等值联结（equijoin），也叫内联接（inner join），它基于两个表之间的相等测试。

我们也可以使用另一种的语法，明确指定联结的类型：

SELECT column1, column2, ...              -- 选择位于不同表中要检索的列FROM tablename1 INNER JOIN tablename2     -- 要联结的表ON ...                                    -- ON指定联结条件，非常重要！！！

下面的SELECT语句返回与上面例子完全相同的结果：

SELECT crs_name, tea_nameFROM Course INNER JOIN TeacherON Course.teacher_id = Teacher.tea_id;

在联结中使用别名

之前的AS别名也可以用在联结中，好处是：

• 缩短SQL语句
• 允许在一条SQL语句中多次使用相同的表（下面自联结会用到）

我们将上面的例子用AS别名改写下：

SELECT crs_name, tea_nameFROM Course AS c INNER JOIN Teacher AS tON c.teacher_id = t.tea_id;

这样做还有一个好处就是，一旦联结的多张表中出现了字段名重复，可以通过别名.字段名（完全限定列名）的方式加以区分：

SELECT a.name, b.nameFROM TableA AS a INNER JOIN TableB AS bON a.fk = b.pk;

自联结（self-join）

自联结（self-join），联结的两张表是相同的表，通过AS别名来区分。

在最后的综合练习第8题，第21题，你将看到它的应用。

外联结（outer-join)

假如有一个父表，和一个子表，子表通过外键引用父表中的记录。但并不是父表中的所有记录都会被引用，比如顾客表（父表）和订单表（子表），肯定会有没有下单的顾客。如果在统计顾客的订单数时，希望将没有下单的顾客也包括进来，那么用内联结就不合适。这个时候就要用到外联结。

左外联结（LEFT JOIN）

在内联结的基础上，增加左边有，右边没有的结果，没有的部分显示为Null。注意：左右指的是联结条件的两段。

使用左外连接来查询所有顾客的订单，包括那些没下单的顾客时，可以这样写

SELECT Customers.cust_id, Orders.order_numFROM Customers LEFT JOIN OrdersON Customers.cust_id = Orders.cust_id;    -- 联结条件

右外联结（RIGHT JOIN）

在内联结的基础上，增加右边有，左边没有的结果，没有的部分显示为Null。

如果将联结条件顺序调换一下，左右联结的效果是一样的。

SELECT Customers.cust_id, Orders.order_numFROM Customers RIGHT JOIN OrdersON Orders.cust_id = Customers.cust_id;    -- 联结条件

全外联结

在内连接的基础上增加左边有右边没有的，和右边有左边没有的结果。MySQL是不支持的FULL JOIN的！但是可以通过UNIO组合左外联结和右外联结，达到同样的效果。

子查询（subquery）

子查询就是嵌套在其它查询中的查询，注意：MySQL4.1之前的版本是不支持这一特性的。

使用场景：

• 利用子查询进行过滤
• 创建计算字段
• 更多使用查看后面综合练习

利用子查询进行过滤

将一条SELECT语句的返回结果作为另一条SELECT语句的WHERE子句

SELECT clnFROM tablenameWHERE cln IN (SELECT cln              FROM tablename              WHERE cln IN (SELECT cln                            FROM tablename                            WHERE 查询条件));

在这里，子查询由内向外处理，对于嵌套子查询的数目也没有限制，但在实际使用时，出于性能考虑，不要嵌套太多的子查询。

作为计算字段

假如有一个顾客表Customers和订单表Orders，Orders表通过cust_id外键引用Customers表中的cust_id主键。要求查询Customers表中每个顾客的订单总数。

要完成以上需求，需要以下两步：

• 从Customers表中检索出顾客列表
• 对于检索出的每个顾客，统计其在Orders表中的订单数目

下面利用子查询完成这一需求

SELECT cust_name,       (SELECT COUNT(*)        FROM Orders        WHERE Orders.cust_id = Customers.cust_id) AS ordersFROM Customers;+---------------+--------+| cust_name     | orders |+---------------+--------+| Village Toys  |      2 || Kids Place    |      0 || Fun4All       |      1 || Fun4All       |      1 || The Toy Store |      1 |+---------------+--------+

说明：orders是一个计算字段，它由子查询建立，该子查询对检索出的每个顾客执行一次。这里也使用了完全限定列名，以避免列名混淆。

通过联结，也可以完成这一需求：

SELECT cust_name, COUNT(Orders.order_num) AS ordersFROM Customers LEFT JOIN OrdersON Customers.cust_id = Orders.cust_idGROUP BY Customers.cust_id;

要注意的一点是，这里COUNT后不能用*，否则就是对行作统计，必须指定列名，这样如果该列没有值（顾客没有订单NULL），就不会被统计进去。

综合练习

这里将以插入数据部分创建的教学管理数据为基础，进行练习。

1. 将所有的课程的名称以及对应的任课老师姓名打印出来

SELECT crs_name, tea_nameFROM Course LEFT JOIN TeacherON Course.teacher_id = Teacher.tea_id;

+----------+------------+| crs_name | tea_name   |+----------+------------+| 生物     | 张磊老师   || 物理     | 李平老师   || 体育     | 刘海燕老师 || 美术     | 李平老师   |+----------+------------+4 rows in set (0.00 sec)

2. 查询学生表中男女生各有多少人？

SELECT gender, COUNT(*) AS totalFROM StudentGROUP BY gender;

+--------+-------+| gender | total |+--------+-------+| 女     |     6 || 男     |    10 |+--------+-------+2 rows in set (0.00 sec)

3. 查询物理成绩等于100的学生的姓名

SELECT std_name, numFROM Student INNER JOIN ScoreON Score.course_id = (SELECT crs_id FROM Course WHERE crs_name='物理')AND Score.num = 100AND Score.student_id = Student.std_id;

+----------+-----+| std_name | num |+----------+-----+| 张四     | 100 || 铁锤     | 100 || 李三     | 100 |+----------+-----+3 rows in set (0.00 sec)

4. 查询平均成绩大于八十分的同学的姓名和平均成绩

-- 将成绩根据学生id分组SELECT std_name, AVG(Score.num)FROM Student LEFT JOIN ScoreON Student.std_id = Score.student_idGROUP BY Score.student_idHAVING AVG(Score.num)>80;

+----------+----------------+| std_name | AVG(Score.num) |+----------+----------------+| 张三     |        82.2500 || 刘三     |        87.0000 |+----------+----------------+2 rows in set (0.04 sec)

5. 查询所有学生的学号，姓名，选课数，总成绩

SELECT std_id, std_name, COUNT(Score.course_id), SUM(Score.num)FROM Student LEFT JOIN ScoreON Student.std_id = Score.student_idGROUP BY Student.std_id;

+--------+----------+------------------------+----------------+| std_id | std_name | COUNT(Score.course_id) | SUM(Score.num) |+--------+----------+------------------------+----------------+|      1 | 理解     |                      3 |             85 ||      2 | 钢蛋     |                      3 |            185 ||      3 | 张三     |                      4 |            329 ||      4 | 张一     |                      4 |            257 ||      5 | 张二     |                      4 |            257 ||      6 | 张四     |                      4 |            276 ||      7 | 铁锤     |                      4 |            264 ||      8 | 李三     |                      4 |            264 ||      9 | 李一     |                      4 |            268 ||     10 | 李二     |                      4 |            297 ||     11 | 李四     |                      4 |            297 ||     12 | 如花     |                      4 |            297 ||     13 | 刘三     |                      1 |             87 ||     14 | 刘一     |                      0 |           NULL ||     15 | 刘二     |                      0 |           NULL ||     16 | 刘四     |                      0 |           NULL |+--------+----------+------------------------+----------------+16 rows in set (0.00 sec)

6. 查询姓李老师的个数

SELECT COUNT(*) AS Li_numsFROM TeacherWHERE tea_name LIKE '李%';

+---------+| Li_nums |+---------+|       2 |+---------+1 row in set (0.00 sec)

7. 查询没有报李平老师课的学生姓名

-- 找出选了李平课的学生，取反; 联结之后再过滤SELECT std_nameFROM StudentWHERE std_id NOT IN (SELECT Score.student_id                     FROM Score                     WHERE Score.course_id in (SELECT crs_id                                               FROM Course LEFT JOIN Teacher                                               ON Course.teacher_id = Teacher.tea_id                                               WHERE tea_name LIKE '李平%'));

8. 查询物理课程比生物课程高的学生的学号

-- 物理成绩和生物成绩作两列才能比较；学号一致SELECT Phy.student_idFROM (SELECT student_id, crs_name, num       FROM Score LEFT JOIN Course      ON Score.course_id = Course.crs_id      WHERE crs_name LIKE '物理') AS PhyINNER JOIN (SELECT student_id, crs_name, num           FROM Score LEFT JOIN Course           ON Score.course_id = Course.crs_id           WHERE crs_name LIKE '生物') AS BioON Phy.student_id = Bio.student_idWHERE Phy.num > Bio.num;

+------------+| student_id |+------------+|          6 ||          7 ||          8 |+------------+3 rows in set (0.00 sec)

9. 查询没有同时选修物理课程和体育课程的学生姓名

-- 找到同时选了的同学取反SELECT std_nameFROM StudentWHERE std_id NOT IN (SELECT Phy.student_id                         FROM (SELECT student_id                               FROM Score LEFT JOIN Course                               ON Course.crs_id = Score.course_id                               WHERE crs_name LIKE '物理') AS Phy                         INNER JOIN (SELECT student_id                                     FROM Score LEFT JOIN Course                                    ON Course.crs_id = Score.course_id                                    WHERE crs_name LIKE '体育') AS Gym                         ON Phy.student_id = Gym.student_id);                         -- 因为是同时选了，所以Phy和Gym联结要用INNER JOIN, 否则会出现只选了一门的

+----------+| std_name |+----------+| 理解     || 钢蛋     || 刘三     || 刘一     || 刘二     || 刘四     |+----------+6 rows in set (0.01 sec)

10. 查询挂科超过两门(包括两门)的学生姓名和班级

-- 筛选课程分数<60 按学生分组，计数SELECT Student.std_name, Class.captionFROM Student LEFT JOIN ClassON Student.class_id = Class.cls_idWHERE Student.std_id IN (SELECT student_id                         FROM Score                         WHERE num < 60                         GROUP BY student_id                         HAVING COUNT(*) >= 2);

+----------+----------+| std_name | caption  |+----------+----------+| 理解     | 三年二班 |+----------+----------+1 row in set (0.07 sec)

11. 查询选修了所有课程的学生姓名

-- 学生分组 成绩记录等于课程数的SELECT DISTINCT std_name  -- 需要加 DISTICT 去重，否则每个名字出现多次FROM Student INNER JOIN ScoreON Student.std_id = Score.student_idWHERE Student.std_id IN (SELECT student_id                         FROM Score                          GROUP BY student_id                         HAVING COUNT(student_id) = (SELECT COUNT(crs_name) FROM Course));

+----------+| std_name |+----------+| 张三     || 张一     || 张二     || 张四     || 铁锤     || 李三     || 李一     || 李二     || 李四     || 如花     |+----------+10 rows in set (0.00 sec)

12. 查询李平老师教的课程的所有成绩记录

SELECT numFROM ScoreWHERE course_id IN (SELECT Course.crs_id                    FROM Course INNER JOIN Teacher                    ON Course.teacher_id = Teacher.tea_id                    WHERE tea_name LIKE '李平%');

+-----+| num |+-----+|   9 ||  66 ||  11 ||  11 |-- ...|  87 ||  87 ||  87 |+-----+23 rows in set (0.08 sec)

13. 查询选课学生都选修了的课程号和课程名

-- 所有学生都选了的课程id：根据课程id分类计数，找出结果等于选课学生数的SELECT crs_id, crs_nameFROM Course INNER JOIN (SELECT course_id FROM Score                        GROUP BY course_id                        HAVING COUNT(course_id) = (SELECT COUNT(DISTINCT student_id) FROM Score)) AS TmpON Course.crs_id = Tmp.course_id;

Empty set (0.00 sec)

14. 查询每门课程被选修的次数

SELECT course_id, COUNT(course_id)FROM ScoreGROUP BY course_id;

+-----------+------------------+| course_id | COUNT(course_id) |+-----------+------------------+|         1 |               12 ||         2 |               11 ||         3 |               12 ||         4 |               12 |+-----------+------------------+4 rows in set (0.00 sec)

15. 查询只选修了一门课程的学生姓名和学号

SELECT std_id, std_nameFROM Student INNER JOIN (SELECT student_id                         FROM Score                         GROUP BY student_id                         HAVING COUNT(student_id)=1) AS TmpON Student.std_id = Tmp.student_id;

+--------+----------+| std_id | std_name |+--------+----------+|     13 | 刘三     |+--------+----------+1 row in set (0.00 sec)

16. 查询所有学生考出的成绩并按从高到低排序（成绩去重）

SELECT DISTINCT numFROM ScoreORDER BY num DESC;

+-----+| num |+-----+| 100 ||  99 |-- ...|  10 ||   9 |+-----+17 rows in set (0.00 sec)

17. 查询平均成绩大于85的学生姓名和平均成绩

SELECT std_name, AVG(num)FROM Student INNER JOIN ScoreON Student.std_id = Score.student_idGROUP BY Student.std_idHAVING AVG(num) > 85;                  

+----------+----------+| std_name | AVG(num) |+----------+----------+| 刘三     |  87.0000 |+----------+----------+1 row in set (0.00 sec)

18. 查询生物成绩不及格的学生姓名和对应生物分数

SELECT std_name, numFROM Student INNER JOIN (SELECT student_id, num                         FROM Score INNER JOIN Course                         ON Score.course_id = Course.crs_id                         WHERE crs_name LIKE '生物' AND num < 60) AS TmpON Student.std_id = Tmp.student_id;

+----------+-----+| std_name | num |+----------+-----+| 理解     |  10 || 钢蛋     |  18 || 张四     |   9 || 铁锤     |   9 || 李三     |   9 |+----------+-----+5 rows in set (0.00 sec)

19. 查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

-- 求最大值可以考虑用DESC 倒序SELECT std_nameFROM Student INNER JOIN (SELECT student_id            FROM Score             INNER JOIN (SELECT crs_id                        FROM Course INNER JOIN Teacher                        ON Course.teacher_id = Teacher.tea_id                        WHERE tea_name LIKE '李平%') AS Tmp            ON Score.course_id = Tmp.crs_id            GROUP BY student_id            ORDER BY AVG(num) DESC LIMIT 1) AS RESON Student.std_id = RES.student_id;                        

+----------+| std_name |+----------+| 张四     |+----------+1 row in set (0.00 sec)

20. 查询每门课程成绩最好的前两名学生姓名

-- 将SELECT结果作为字段名; LIMIT m, n 取出第二高的分数SELECT *FROM ScoreLEFT JOIN (SELECT scr_id,                   (SELECT  num                   FROM Score AS s2                   WHERE s2.course_id = s1.course_id                   ORDER BY num DESC LIMIT 1) AS first_num,                  (SELECT DISTINCT num                   FROM Score AS s2                   WHERE S2.course_id = s1.course_id                   ORDER BY num DESC LIMIT 1,1) AS second_num           FROM score as s1) AS TON Score.scr_id = T.scr_idWHERE Score.num <= T.first_numAND Score.num >= T.second_num;

21. 查询同一个学生不同课程但成绩相同的学生的学号，课程号，成绩

-- 自联结SELECT A.student_id, A.course_id, A.numFROM Score AS A, Score AS BWHERE A.course_id != B.course_idAND A.student_id = B.student_idAND A.num = B.num;

+------------+-----------+-----+| student_id | course_id | num |+------------+-----------+-----+|          6 |         2 | 100 ||          6 |         4 | 100 |+------------+-----------+-----+2 rows in set (0.00 sec)

22. 查询没学过“李平”老师课程的学生姓名以及选修的课程名称

SELECT std_name, crs_name, tea_nameFROM StudentINNER JOIN (SELECT student_id, crs_name, tea_name            FROM Score             INNER JOIN (SELECT crs_id, crs_name, tea_name                        FROM Course                        INNER JOIN (SELECT tea_id, tea_name                                    FROM  Teacher                                    WHERE tea_name NOT LIKE '李平%') AS T                        ON Course.teacher_id = T.tea_id) AS C            ON Score.course_id = C.crs_id) AS SON Student.std_id = S.student_idORDER BY std_name;

+----------+----------+------------+| std_name | crs_name | tea_name   |+----------+----------+------------+| 刘三     | 体育     | 刘海燕老师 || 如花     | 体育     | 刘海燕老师 || 如花     | 生物     | 张磊老师   |-- ...| 铁锤     | 生物     | 张磊老师   || 铁锤     | 体育     | 刘海燕老师 |+----------+----------+------------+24 rows in set (0.00 sec)

23. 任课最多的老师中学生单科成绩最高的学生姓名

-- 任课最多的老师课程id下的学生SELECT std_name, num, crs_nameFROM Student INNER JOIN (SELECT student_id, num, crs_name            FROM Score             INNER JOIN (SELECT crs_id, crs_name                        FROM Course                        INNER JOIN (SELECT teacher_id                                    FROM course                                    GROUP BY teacher_id                                    ORDER BY COUNT(teacher_id) DESC LIMIT 1) AS T                        ON Course.teacher_id = T.teacher_id) AS C            ON Score.course_id = C.crs_id) AS SON Student.std_id = S.student_idORDER BY num DESC LIMIT 8;  -- 数据问题，同时有很多最高的。。。

+----------+-----+----------+| std_name | num | crs_name |+----------+-----+----------+| 李三     | 100 | 物理     || 张四     | 100 | 美术     || 铁锤     | 100 | 物理     || 张二     | 100 | 美术     || 张四     | 100 | 物理     || 张一     | 100 | 美术     || 张三     |  99 | 美术     || 钢蛋     |  99 | 美术     |+----------+-----+----------+8 rows in set (0.00 sec)