2周零基础搞定SQL——复杂查询 复杂sql 语句编写经验

恭喜你，又闯过了一关，学完了上一篇文章的汇总分析，今天我们将攻克的是如何用SQL学会复杂查询。但是学习开始之前，你首先要明确4个概念：

• 是什么：这个知识点是什么？基本概念要知道；
• 如何用：如何应用到实际的数据分析中；
• 有什么用：在怎样的数据场景下使用这个知识；
• 注意事项：这个知识点使用时需要注意哪些规则和细节。

接下来，我们开始学习今天的内容吧!

1.视图

a.什么是视图：

表：存放实际数据

视图：基于 SQL 语句的结果集的可视化表，存放的是SQL查询语句。详细来说：

• 从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。
• 从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表，当查询连接断开时，这张虚拟表就会被自动删除。
• 从数据库系统外部来看，视图是由一张或多张表中的数据组成的，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。

需要注意的是：

• 视图是一种数据库对象，用户可以象查询普通表一样查询视图。
• 视图内其实没有存储任何数据，它只是对表的一个查询。
• 视图的定义保存在数据字典内。创建视图所基于的表为“基表”。
• 视图一经定义以后，就可以像表一样被查询、修改、删除和更新。

说了这么多，是不是感觉都不是人话，没关系，我们总结一下：

• 视图包含行和列，是一张虚拟表，，但就像真正的表一样。视图中的字段是一个或多个数据库中真实表中的字段。
• 您可以添加 SQL 语句，在哪里添加，并将语句连接到视图，或者您可以呈现数据，就像数据来自单个表一样。

视图是数据库中一个比较重要的组成部分，在隔离实体表的前提下还可以让用户查询到需要的数据，可以起到保护底层数据的作用。同时针对不同的人群，视图也可以起到简化用户对数据的理解。

那么如何创建视图呢？

CREATE VIEW语句

CREATE VIEW view_name(column_name1,column_name2,...)
AS
SELECT column_name(s) -- select子句使用在as关键字后面 
FROM table_name
WHERE condition

注意：视图总是显示最新数据！每当用户查询视图时，数据库引擎就使用视图的 SQL 语句重新构建数据。

栗子：

CREATE VIEW 按性别汇总(性别,人数)
AS
SELECT 性别,COUNT(*)
FROM student 
GROUP BY 性别;

b.如何使用视图

如果你在工作中需要经常使用“按性别汇总”这个查询，则可以不用每一次都去输入一遍SQL语句，只要如上例所示，创建一个视图，并在from字句中使用视图名称代替表名称。

SELECT 性别,人数
FROM 按性别汇总;

对于重复性工作来说，这简直太提高效率了，有木有？

那么如何删除视图呢？

选中要删除的视图，鼠标右键选择删除视图就可以啦！

c.视图有什么用

举个实际生活中的例子，如果生活中偶尔需要搭车，打车是可以接受的。但如果家里有小孩，小孩需要搭车上学，每天搭车就不现实了，此时我们就需要拥有一辆私家车。同样的，如果我们在工作中需要频繁的重复使用某些查询语句，那么一遍遍录入SQL语句显然比较麻烦，这个时候就可以把这个SQL语句保存为视图，变为自己的私家车，随用随取显然就很方便啦！

• 在进行复杂的查询和汇总分析时，使用视图可以很好地帮助我们提高效率！
• 视图中的数据可以随着原表的变化自动更新，可以保证数据的最新状态！
• 视图不需要保存数据，可以节省存储空间！

d.注意事项

• 避免在视图的基础上再去创建视图，多重视图会降低SQL的性能和效率；
• 不能往视图里面插入数据，否则会报错。

好啦，关于视图的内容就这些啦，下面你需要行动起来，按照前面说的实际操作一遍，体验一下视图带给你的嗖嗖嗖的效率提升。

练习完了，我们来继续学习！

2.子查询

之前的我们的学习中，查询条件都是针对具体的数值，如成绩大于60分。那么如果我们要查询出成绩大于平均成绩的学生，要怎么做呢？这个时候就需要用到子查询了。

a.什么是子查询

子查询（Sub Query）或者说内查询（Inner Query），也可以称作嵌套查询（Nested Query）,子查询用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。

简单来说子查询就是一次性的视图，即在from子句中直接写定义视图的SQL查询语句。也就是在一个select查询语句中嵌套了另一个select查询语句。

举颗栗子：

SELECT 性别,人数
FROM(
     SELECT 性别 ,COUNT(*) AS 人数
     FROM student
     GROUP BY 性别
     ) AS 按性别汇总 ;

图片中红框中的语句就是一个子查询，而按性别汇总就是这个子查询也就是这个一次性视图的名字。

需要注意的是，当SQL语句中有子查询的时候，先运行子查询，再运行外部的查询语句。

b.如何使用子查询

刚刚我们学习的子查询是放到from子句里面，其实子查询可以放到where子句中，与运算符in、any、all一起使用，从而构建出复杂的查询条件。使用的方法是在in、any、all的括号里面输入子查询语句。

栗子：

在score表中找出每门课程里成绩最低的学号。

首先，我们按照上次说的用SQL解决业务问题的步骤来对这个问题进行一个拆解：

a).把业务问题解读成通俗易懂的大白话：

找出每门课程里最低的成绩，并显示最低成绩对应的学号；

b).写出分析思路（按步骤分解）:

• 关键词：每门课程，最低成绩，学号；
• 从哪张表——>score；
• 每门课程——>课程号——>按课程号分组；
• 最低成绩——>查找每门课程对应的成绩的最小值；
• 查询结果：学号，课程号，最低成绩。

c).写出对应的SQL子句:

找出每门课程的最低分。

SELECT 课程号,MIN(成绩)
FROM score as b
GROUP BY 课程号;

在score表中查找这些值对应的学号

SELECT *
FROM score as a
WHERE 成绩 in(80,50,40);

可以看出查询结果并不符合条件，对比两个上面两个查询结果，当两个表中课程号与成绩对应相等时，即为我们想要的查询结果。由于子查询先运行，得到最终的SQL语句如下

SELECT *
FROM score as a -- 将表命名为a
WHERE 成绩 =(
	SELECT MIN(成绩)
	FROM score as b -- 将表命名为b
	WHERE b.课程号 = a.课程号) 
ORDER BY 课程号;

上面的查询结果中课程号为“0001”的有2行数据，是因为最小成绩80分有两个。这里面用到了关联子查询，我们后面会讲到。

下面我们来看一下any、all关键字如何与子查询一起使用。any、all关键字必须与比较运算符（=、<>、>、>=、<、<=）一起使用。

栗子：

any（子查询）与some（子查询）相同，这里面我们以any为例。

select 列名1
from 表名1
where 列名1 > any(子查询);

哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？

a).把业务问题解读成通俗易懂的大白话：

选出成绩大于课程“0002”的全部成绩里的任意一个的成绩，并显示学号。

b).写出分析思路（按步骤分解）:

• 关键词：课程“0002”，成绩，学号；
• 从哪张表——>score；
• 课程0002——>课程号——>根据课程号查询课程0002的全部成绩；
• 成绩——>查找成绩大于课程“0002”的全部成绩里的任意一个的成绩即符合条件；
• 查询结果：学号，成绩。

c).写出对应的SQL子句:

查找出课程0002的全部成绩

SELECT 成绩
FROM score
WHERE 课程号 = '0002'

比任意一个课程0002的成绩高的学生（any（子查询））

SELECT 学号,成绩
from score
where 成绩> ANY(
	SELECT 成绩
	FROM score
	WHERE 课程号 = '0002'
);

同样的，如果我们要查询哪些学生的成绩比课程0002的全部成绩里的成绩都高呢？

此时我们使用all（子查询）

select 学号,成绩
FROM score
where 成绩 > all(
SELECT 成绩
FROM score
where 课程号='0002'
);

c.子查询有什么用

如果频繁使用一个子查询，可以将子查询保存为视图，从打车变为私家车，避免一遍遍输入子查询语句，大大提高效率。

d.注意事项

• 子查询必须括在圆括号中;
• 子查询不能直接用在集合函数中，对于all（子查询）得到的是一个集合（N行数据），所以对于一个比较，可以写成a/3>all(子查询)，而不可以写成a>3*all(子查询)；
• 避免使用多层嵌套子查询，一般不要超过三层。超过的话可以考虑使用临时表将子查询结果先保存，再和其他查询进行关联；
• 尽量不要省略子查询的名称，方便使用及查阅：

select...
from...
子查询as子查询名称

• 子查询的 SELECT 子句中只能有一个列，除非主查询中有多个列，用于与子查询选中的列相比较;
• 子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可以起到同 ORDER BY 相同的作用;
• 返回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符;
• BETWEEN 操作符不能同子查询一起使用，但是 BETWEEN 操作符可以用在子查询中。

3.标量子查询

a.什么是标量子查询

子查询得到结果是一个数据（一行一列）。

举颗栗子：

查询大于平均成绩的学生的学号和成绩。

由于where子句中不能够使用汇总函数，所以不能使用如下语法：

此时就需要使用标量子查询，来返回平均值这个数据。

所以正确的语句应该是：

select 学号,成绩
FROM score
where 成绩 > (
		SELECT avg(成绩)
		FROM score  -- avg(成绩)=72.375,即此子查询返回的值为72.375
);

再来颗栗子：

假设成绩<=60的为差生，成绩>=80的为优等生，那么如何查询成绩在优等生平均成绩和差生平均成绩之间的学生的学号和成绩呢？

依然是用标量子查询结合between关键字来进行查询：

SELECT 学号,成绩 
from score 
where 成绩 BETWEEN(
		SELECT avg(成绩)
		FROM score
		where 成绩 <=60) AND
		(SELECT avg(成绩)
		FROM score
		where 成绩>=80);

b.如何使用标量子查询

标量子查询除了可以用在where语句中，通常任何使用单一值的地方，都可以使用标量子查询。

栗子：

SELECT 学号,成绩,(select avg(成绩)
		 FROM score ) as 平均成绩
FROM score;

c.标量子查询有什么用

标量子查询由于返回的是一个数值，所以可以和比较运算符以及in、any、all、between等关键字一起使用，构建复杂的查询条件。

d.注意事项

其中括号内的select每次只返回一条，若是返回多条就会报错。

到这里，标量子查询你掌握了嘛！为了更好的掌握，赶快把上面的栗子练起来吧！

4.关联子查询

a.什么是关联子查询

在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，关联子查询的信息流是双向的，外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录，之后外部查询根据返回的记录做出决策。

上面描述的关联子查询的基本逻辑，你看懂了吗？反正我是没看懂！

下面我们看一个栗子：

查找出每个课程中大于对应课程平均成绩的学生。

a).把业务问题解读成通俗易懂的大白话：

• 按课程号分组得到每门课程的平均成绩；
• 再在每门课程中找出大于该门课程平均成绩的成绩及对应的学号。

b).写出分析思路（按步骤分解）:

• 关键词：课程号，平均成绩，成绩，学号；
• 从哪张表——>score；
• 课程号，平均成绩——>根据课程号查询每门课程的平均成绩（avg、group by）；
• 成绩——>查找每门课程中找出大于该门课程平均成绩的成绩（where）；
• 查询结果：学号，成绩。

c).写出对应的SQL子句:

查找出每门课程的平均成绩

SELECT  课程号,avg(成绩)
FROM score
GROUP BY 课程号;

在每门课程这个分组数据里找出大于该门课程平均成绩的成绩

SELECT 学号,课程号,成绩 
FROM score as s1
where 成绩>(
	SELECT  avg(成绩)
	FROM score as s2
	where s1.课程号=s2.课程号
	GROUP BY 课程号
);

b.如何使用关联子查询

从上面的栗子我们可以看出：

• 关联子查询会在细分的组内进行比较时使用。
• 关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。

初学SQL的人都会觉得SQL的关联子查询难以理解，为什么？这是有原因的。

关联子查询的执行逻辑和通常的SELECT语句的执行逻辑完成不一样。这就是SQL关联子查询难以理解的原因。

我们分析一下上面的栗子的SQL的运行顺序：

先执行主查询：

SELECT 学号,课程号,成绩 
FROM score as s1;

从主查询的“课程号”先取第一个值=‘0001’，通过where s1.课程号=s2.课程号传入子查询，子查询变成：

	SELECT  avg(成绩)
	FROM score as s2
	where s2.课程号='0001'
	GROUP BY 课程号

第一次子查询结果：

从子查询得到的结果AVG(成绩)=80.0000，返回主查询：

SELECT 学号,课程号,成绩 
FROM score as s1
where 成绩>80 and 课程号='0001';

第一次整个语句的结果：

即课程号为0001的没有成绩大于课程0001平均成绩80.0000的数据；

然后，课程号取第二个值，得到整个语句的第二结果，依次类推，把课程号全取值一遍，就得到了整个语句的结果集。结果如下：

到这你可能会问：每条记录都执行，第四行的课程号和第三行的课程号都是0002，那么他们在子查询中会重复计算一次平均成绩进行比较，这样会不会涉及重复计算？答案是不会，效率并没有降低，SQL已经对此进行过优化。

总结：

1、关联子查询的执行逻辑完全不同于正常的SELECT语句。

2、关联子查询执行逻辑如下：

• 先从主查询的score表中课程号列取出第一个值，进入子查询中，得到子查询结果，然后返回父查询，判断父查询的where子句条件，则返回整个语句的第1条结果。
• 重复上述操作，直到所有主查询中的score表中课程号列记录取完为止。得出整个语句的结果集，就是最后的答案。

c.关联子查询有什么用

在每个组里对数据进行比较。

关联子查询和GROUP BY都可以进行分组，但在应用场景上，有明显的不同。有GROUP BY的SELECT字句中，只能选择被GROUP BY的列，也就是分组列或者聚合函数操作的结果；但是关联子查询则相当于进行一次GROUP BY再JOIN回去一样的。在有关联子查询的query里，SELECT的列是不受限制的。关联子查询的分组，只是将分组查询出来的信息作为新列加上去，不对分组内容进行聚合。

d.注意事项

关联子查询的结合条件如果未出现在子查询之中就会发生错误。

对于初学者，关联子查询可能比较难理解，所以需要我们多多联系啦！下面把上面讲的栗子实际操作一遍，理解一下关联子查询的执行逻辑吧！Action！

5.如何用SQL解决业务问题

这个之前我们有讲过，你需要做的就是不断重复这个过程养成习惯！把简单重复到极致就是经典！把今天所有的栗子都按下面的方法联系一遍吧！加油！

• 把业务问题解读成通俗易懂的大白话
• 写出分析思路（按步骤分解）
• 写出对应的SQL子句

6.各种函数

SQL提供了很多现成函数，大家不需要全部记住，做到了解即可。工作中用到，可以求助万能的百度哦！常用的大致可分为单行函数和分组函数。

• 单行函数为处理一条数据，输出一个结果，如对字符串的处理等。
• 分组函数又称聚合函数、统计函数或组函数，是对多条记录的统计结果，如求和等。

a.单行函数

根据处理的数据类型不同，单行函数又可细分为字符函数、数学函数、日期函数等

字符函数

LENGTH(str) 返回字符串长度

SELECT LENGTH('hello'); -- 结果为5
SELECT LENGTH(last_name); -- 结果为last_name字段的长度

UPPER(str) | LOWER(str) 转换为大/小写

SELECT UPPER('hello'); -- 结果为'HELLO'
SELECT LOWER('HeLLo'); -- 结果为'hello'

CONCAT(str1,str2) 拼接字符串

SELECT CONCAT('调音师','真好看'); -- 结果为 '调音师真好看'

REPLACE(str,old,new) 字符串替换

 SELECT REPLACE('my name is xiaoxiao','xiao','da'); -- 结果为'my name is dada'

SUBSTR(str,pos,len) 截取字符串，字符串索引从1开始

SELECT SUBSTR('my name is xiaoming',4,4); -- 结果为'name'
SELECT SUBSTR('my name is xiaoming',4); -- 结果为'name is xiaoming'

算术函数：

ROUND(X,D) 四舍五入，保留指定位数，默认为保留整数

SELECT ROUND(-1.65); -- -2
 SELECT ROUND(-1.65,1); -- -1.

abs(X），取绝对值

select abs(-100); -- 100

MOD(X1,X2) 求余数

SELECT MOD(10,3); -- 1,符号与被除数一致
SELECT MOD(-10,3); -- -1
SELECT MOD(10,-3); -- 1

TRUNCATE(X) 截取保留指定小数位

SELECT TRUNCATE(2.666,1);  -- 2.6

日期函数：

NOW()返回当前的日期和时间、CURTIME()返回当前的时间、CURDATE() 当前日期

SELECT NOW(); -- 2019-10-19 14:40:54
SELECT CURTIME(); -- 14:40:54
SELECT CURDATE(); -- 2019-10-19

YEAR() | MONTH() | DAY() | HOUR() | MINUTE() | SECOND()

SELECT YEAR('2019-10-19 14:40:54'); -- 2019

DATEDIFF(date1,date2) | TIMEDIFF(date1,date2) 两个日期相差的天数|两个时刻相差的时间

SELECT DATEDIFF('2019-10-17','2019-10-1'); -- 结果为16，前面的减后面的
SELECT TIMEDIFF('2019-10-17 15:30:2','2019-10-1 7:0:0');  -- 结果为 392:30:02

DAYNAME(date) 返回date的星期名

SELECT DAYNAME(CURRENT_DATE); -- 当前星期几

DAYOFWEEK(date)| DAYOFMONTH(date) | DAYOFYEAR(date) 返回date所代表的一星期中的第几天(1~7)|返回date是一个月的第几天(1~31)|返回date是一年的第几天(1~366)

STR_TO_DATE(str,format) |DATE_FORMAT(date,format) 字符串按格式转为日期|日期按格式转为字符串

SELECT STR_TO_DATE('10-1 2019','%c-%d %Y'); -- 2019-10-01
SLECT DATE_FORMAT(NOW(),'%Y.%m.%d'); -- 2019.10.19

b.分组函数

分组函数又称为聚合函数、统计函数、组函数，所有分组函数对null值处理为忽略它，而非当做0。常用的分组函数有以下几个：

• SUM() 对数值型数据求和，用+相加，符合+的运算法则
• AVG() 对数值型数据求平均
• MAX() 对所有可比较类型求最大值
• MIN() 对所有可比较类型求最小值
• COUNT() 统计非空个数

在第二讲汇总分析中有讲过，分组函数的调用为：

SELECT SUM(成绩) FROM score;
SELECT COUNT(*) FROM score; -- 统计表的总行数

好啦，这一章的内容到这儿就结束了，好长，有木有？不过不要怕，挺住，多练习，一切都不是事儿！

下面开始我们这章的练习吧：https://sqlzoo.net/

运行结果，为什么小数点后面有很多0？ 这是因为用了字符串拼接函数concat导致的。

所有的练习都完成啦，真棒！最后，依然一张思维导图，回顾一下今天学习的内容！

好啦，祝你早安，午安，晚安！我们下次见哦！