【大数据学习心得】原创SQL教程（下）

本人自己码的数据库基础教程，每个步骤都有说明和代码辅助，大家如果需要可以自行食用（因字数超限分两篇发）。

六， 数据表的关联查询

在第五部分已经介绍完了单个数据表的查询，但是有时往往需要关联数据表进行查询。一个简单的场景是，当在某个业务中需要查出所有的订单时，往往需要附带订单创建人和完成人的信息，而这些信息与订单信息分别在两个表中。是时候让关联查询上场表演了。

与excel类似地，SQL也有表的横向扩展和纵向扩展的区别，如不同区域订单产品表往往使用的是纵向扩展增加记录行数即可，而上文提到的订单信息和创建人、完成人信息的合并则需要通过横向扩展增加字段实现。而纵向扩展和横向扩展的综合就是全连接。

1. UNION纵向扩展表

有些数据被分别存在了不同的表里，做分析时需要先做汇总，此时会用到UNION关键字做纵向连接。纵向连接只能连接表中的相同字段。其基本语法如下：

-- UNION纵向连接的基本语法
-- SELECT ...
-- UNION [ALL|DISTINCT]
-- SELECT ...
-- ...

UNION连接默认为ALL，ALL表示不去重，可以省略不写，DISTINCT表示去重，根据实际业务场景而定。

为了演示纵向表连接，我们新建一个elective_course表，重新导入一波选修课程数据，然后将两张course表连接在一起。

-- 创建学生选课表
CREATE TABLE IF NOT EXISTS `elective_course`(
    `id` INT(8) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键',
    `course_num` INT(8) NOT NULL COMMENT '课程编号',
    `course_name` VARCHAR(20) NOT NULL COMMENT '课程编号',
    `teacher` VARCHAR(10) NOT NULL COMMENT '课程编号',
    `stu_num` VARCHAR(5) NOT NULL COMMENT '选课学生学号'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '学生选课情况表';
​
-- 添加学生选课数据
INSERT INTO elective_course(course_num, course_name, teacher, stu_num) 
VALUES(10011, '影视鉴赏', '猪八戒', 20001),
      (10011, '影视鉴赏', '猪八戒', 20003),
      (10011, '影视鉴赏', '猪八戒', 20006),
      (10012, '三十六计', '唐僧', 20001),
      (10012, '三十六计', '唐僧', 20002),
      (10012, '三十六计', '唐僧', 20005),
      (10012, '三十六计', '唐僧', 20006),
      (10013, '中国古典音乐', '孙悟空', 20001),
      (10013, '中国古典音乐', '孙悟空', 20003),
      (10013, '中国古典音乐', '孙悟空', 20004),
      (10013, '中国古典音乐', '孙悟空', 20005),
      (10013, '中国古典音乐', '孙悟空', 20006),
      (10014, '佛家经典导读', '沙悟净', 20001);
      
-- 查询学生的所有选课信息
SELECT course_num, course_name, teacher, stu_num FROM course
UNION
SELECT course_num, course_name, teacher, stu_num FROM elective_course;
​
-- 查询开设的所有课程和任课老师（DISTINCT去重）
SELECT course_num, course_name, teacher FROM course
UNION DISTINCT
SELECT course_num, course_name, teacher FROM elective_course;

2. JOIN横向扩展表

当要查询的字段在两个及以上表中存储，而这些表又可以通过某些字段相互关联时，可以使用JOIN关键字横向连接多个表，表现为表格的横向扩展。

当使用JOIN或INNER JOIN时，表示的是取两个表格的交集，即关联字段的值在两个表格中都能找到相应的记录的数据。当某个关联字段的值只在某一张表格中出现时，扩展后的表格会将整条记录删除。

-- JOIN横向连接的基本语法
-- SELECT 字段 FROM 表1 JOIN 表2 ON 表1.关联字段 = 表2.关联字段;
​
-- 查询学生选修课的情况
SELECT a.course_num, a.course_name, a.teacher, b.stuNum, b.`name`, b.age, b.birthday
FROM elective_course a
JOIN stu_info b
ON a.stu_num = b.stuNum;

3. LEFT JOIN 和 RIGHT JOIN

LEFT JOIN 和 RIGHT JOIN是另外的两种横向扩展表的方式，与JOIN不同的是，LEFT JOIN不仅包括两张表的交集，还包括表1独有的数据，在表2中没有与之对应的数据时，使用NULL填充；同理RIGHT JOIN不仅包括两张表的交集，还包括表2独有的数据，在表1中没有与之对应的数据时，使用NULL填充。

-- 假设新转学过来一名同学，他还没来得及选课：
INSERT INTO stu_info(stuNum, `name`, age, birthday) VALUES(20010, '小琳', 18, '2002-03-01');
​
-- 分别用LEFT JOIN和RIGHT JOIN执行一下上面的查询学生选修课情况的语句，对比下有什么不同：
SELECT a.course_num, a.course_name, a.teacher, b.stuNum, b.`name`, b.age, b.birthday
FROM elective_course a
LEFT JOIN stu_info b
ON a.stu_num = b.stuNum;
​
SELECT a.course_num, a.course_name, a.teacher, b.stuNum, b.`name`, b.age, b.birthday
FROM elective_course a
RIGHT JOIN stu_info b
ON a.stu_num = b.stuNum;

使用LEFT JOIN 会查询出部分学生学号无法匹配的信息，则学生信息的对应栏目中全部是NULL。请注意这里查询结果中的NULL指的是没有对应的信息，与字段本身不允许为空的限制是两个概念。

使用RIGHT JOIN可以查看到刚刚添加的学生小琳因为还没有选课，所以她的课程信息也被填充为NULL。

4. SQL实现全连接查询

两个表的全连接查询结果即为两个表的并集，只要在一个表中有记录，查询的结果中就会有一条相应的记录。MySQL中本身没有提供全连接查询的功能，但是可以通过左连接、右连接和UNION纵向扩展间接实现全连接。

-- MySQL全连接语法
-- SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 表1.关联字段 = 表2.关联字段;
-- UNION
-- SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 表1.关联字段 = 表2.关联字段;
​
-- 全连接查询
SELECT a.course_num, a.course_name, a.teacher, b.stuNum, b.`name`, b.age, b.birthday
FROM elective_course a
LEFT JOIN stu_info b
ON a.stu_num = b.stuNum
UNION
SELECT a.course_num, a.course_name, a.teacher, b.stuNum, b.`name`, b.age, b.birthday
FROM elective_course a
RIGHT JOIN stu_info b
ON a.stu_num = b.stuNum;

5. SQL查询语句的执行顺序

至此SQL常用的查询操作已经介绍完毕了。这么多关键词就像四则运算符一样，总要有个优先级。前面已经说过，SQL语句的语法符合英语的习惯，并不能按照简单地从前到后地执行，这一节我们来聊聊SQL查询语句的执行顺序。

SQL语句的执行顺序：
​
(1)FROM:记录FROM后的前两个表
​
(2)ON:记录表连接的字段
​
(3)JOIN:连接表操作
​
(4)WHERE:条件筛选数据
​
(5)GROUP BY:将筛选出的数据做个分组
​
(6)聚合函数:执行SQL语句中的聚合函数
​
(7)HAVING:对分组后的数据再进行筛选
​
(8)SELECT:查找要查询的字段
​
(9)DISTINCT:对查找出的数据去重
​
(10)ORDER BY:对查找出的数据排序
​
(11)LIMIT:限制查询数量

了解SQL查询语句的执行顺序的一个好处是：后执行的语句可以享有先执行语句的成果，包括计算结果、别名等。举个栗子：由于FROM最先执行，则如果在执行FROM时给表格起了个别名，则其它的命令中都可以使用【别名.字段】的格式访问到表中的数据。又如聚合函数的执行顺序在WHERE之后，HAVING之前，则在WHERE子句中就不能直接使用聚合函数的结果，这也是增加了HAVING关键字进行二次筛选的一个重要原因。

了解SQL查询语句的执行顺序的另一个好处是：SQL查询语句的执行顺序完全符合我们处理数据的一般逻辑。可以试一下先搭好整个SQL内容的框架，按照SQL查询语句的执行顺序采用填空的方式补充内容，你就能写出很多按照语法顺序无法写出的查询语句。这也是我们在开始写SQL时都是用【SELECT *】 做占位，等查询的主体内容写完以后再填充要查询的字段的原因。

七，学了数据库不能不知道的那些知识

本节知识不是重点，只要不是去应聘web开发或数据开发，基本用不到。但是既然学了数据库，对这些概念和功能还是要有一些基本的认识滴！

1. 事务

想象这样一种场景，如果你在银行ATM取钱，你已经提交了取钱申请但是恰好ATM断电了，你并没有取到这笔钱，如果此时账户上的金额发生了改变，你一定会勃然大怒然后去找银行理论。但实际上为了避免这种尴尬，只要你没取到钱，你账户上的金额是不会发生改变的，我们可以把取钱的一整套流程看做一个事务，当事务完结了（即钱已经顺利取出），才会去修改账户余额，否则不会对账户的余额做任何的修改。

MySQL也提供了事务的机制：如两个相互关联的指标A和B，我们希望对A和B要么同时修改两个，要么不修改，则对A和B的修改就成为了一个事务。开启事务后，单独对A和对B的修改都不会直接体现在数据上，只有提交后才会一起写入数据库。

MySQL的事务具有四大特性：

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

-- 开启事务
BEGIN TRANSACTION;
START TRANSACTION;
​
-- 提交事务
COMMIT;
COMMIT WORK;
​
-- 回滚、结束事务
ROLLBACK;
ROLLBACK WORK;
​
-- 新建一个事务保存点，一个事务中允许创建多个保存点
SAVEPOINT identifier;
​
-- 删除一个事务保存点
RELEASE SAVEPOINT identifier;
​
-- 回滚到事务保存点的位置
ROLLBACK TO identifier;

2. 视图

试想这样一种场景，你平时需要查询的数据基本在相同的两张表中，两张表没有建立任何外键关联，则你每个SQL语句都要使用JOIN对两张表做关联查询。此种方式一方面产生了大量的冗余语句，另一方面也增加了数据库的负载。有没有一种更好方法既能满足你的需求，又能减少数据库的无用功呢？

MySQL提供了视图的功能完美解决了上述问题。 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

啥？上述介绍看不懂？那我们用人话翻译吧：

(1)视图是个虚拟表，在数据库中并不真实存在；

(2)视图是通过查询语句产生的；

(3)视图可以用于查询，且只要定义了视图，就可以重复使用；

(4)引用视图时，数据会动态生成，所以不用担心自己对数据库的修改通过视图无法查询。

-- 创建视图基本语法
-- CREATE VIEW 视图名 AS SELECT语句
-- 使用视图做查询的基本语法
-- SELECT 字段名 FROM 视图名;
-- 删除视图的常用语法
-- DROP VIEW 视图名;
​
-- 创建视图
-- 注意：创建视图进行多表合并时并不会带表名，因而不同表有相同名称字段时是无法直接合并的
-- 此时可以通过指定字段做合并或给相同名称字段起一个别名
CREATE VIEW view1 AS 
SELECT a.course_num, a.course_name, a.teacher, b.stuNum, b.`name`, b.age, b.birthday 
FROM course a 
LEFT JOIN stu_info b 
on a.stu_num = b.stuNum;
​
-- 使用视图
SELECT * FROM view1;
​
-- 删除视图
DROP VIEW view1;

3. 索引

如果把一个数据表比作一个字典的话，那么索引就是这个字典的目录。如果没有索引，在无序数据表中只能遍历，其时间复杂度是O(n)；在有序的数据表中的查找数据可用二分法，其时间复杂度是O(logn)；而如果有索引，则可以直接定位要查数据的“页码”，其时间复杂度是O(1)，使用索引进行查询可以提高查询性能，减少查询所需时间。

既然索引可以提高查询效率，是不是建的越多越好呢？建立索引不是没有代价的，它会占用一定的内存空间，可以说是用空间换时间的一个典例。所以索引也不是越多越好。把常用于搜索的字段建为索引，可以达到时间和空间的相对平衡，是一个比较合理的做法。

MySQL中索引有很多种类。本节只简单列举一下分类和使用方法，不做过多的拓展。

主键索引—Primary Key

之前创建数据表时建立的id字段即为表的主键，主键本身就是常见的一种索引。

-- 创建主键索引
-- 创建表时创建
字段名 数据类型 PRIMARY KEY
PRIMARY KEY(字段名)
​
-- 向表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
​
-- 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

外键索引—Foreign Key

外键索引通常用于数据表之间的关联。如在上文的学生表和选课表的学号字段建立外键索引，则一个表中的数据变化，另一个表也会随之变化。

外键索引的另一个用途是在web开发时通过外键可以快速的从一个表访问另一个表的数据，这种做法消耗的资源比JOIN表连接还要少。

建立外键索引需要遵守两个规则：

1. 两张表的关联字段需要是同一类型的字段；

2. 被关联字段必须是key的一种，通常是主键，也可以是其它具有标识的字段。

普通索引—Index

如果一个表中某个字段经常参与查询，而这个字段又无法作为主键或外键索引，则一个比较简单的做法是使其成为一条普通索引。

-- 创建普通索引
CREATE INDEX 索引名 ON 表名(字段名);
​
-- 删除普通索引
DROP INDEX 索引名 ON 表名;
​
-- 查看普通索引
SHOW INDEX FROM 表名 \G;

唯一索引—Unique

如果表中的某个字段经常参与查询而又具备唯一性，则可以创建唯一索引。唯一索引的删除和查看命令与普通索引一致，但是在创建时需要添加UNIQUE关键字。

-- 创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段名);

八，综合测试

请自行连接云数据库并建立自己的数据库，在数据库中新建数据表和导入数据，并完成以下9道查询练习题，其中新建数据表和导入数据的代码已经给出，可以直接复制执行。

CREATE TABLE `student` (
  `Id` int unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) NOT NULL,
  `Sex` varchar(4) DEFAULT NULL,
  `Birth` year DEFAULT NULL,
  `Department` varchar(20) NOT NULL,
  `Address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
CREATE TABLE `score` (
  `Stu_id` int NOT NULL,
  `C_name` varchar(20) DEFAULT NULL,
  `Grade` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
​
INSERT INTO score VALUES(901, '计算机',98);
INSERT INTO score VALUES(901, '英语', 80);
INSERT INTO score VALUES(902, '计算机',65);
INSERT INTO score VALUES(902, '中文',88);
INSERT INTO score VALUES(903, '中文',95);
INSERT INTO score VALUES(904, '计算机',70);
INSERT INTO score VALUES(904, '英语',92);
INSERT INTO score VALUES(905, '英语',94);
INSERT INTO score VALUES(906, '计算机',90);
INSERT INTO score VALUES(906, '英语',85);
​
-- 问题：
-- 1.查询student表的第2条到第4条记录
​
-- 2.从student表中查询计算机系和英语系学生的信息
​
-- 3.从student表中查询每个院系有多少人
​
-- 4.查询李四的考试科目(C_name)和考试成绩（Grade）
​
-- 5.计算每个学生的总成绩
​
-- 6.查询湖南的学生的姓名、年龄、院系和考试科目及成绩
​
-- 7.查询所有学生的信息和考试信息
​
-- 8.将计算机考试成绩按从高到低进行排序
​
-- 9.查询计算机成绩低于95的学生信息
​
-- 思考题：如果使用视图应该怎么做
​