一如既往，我们汇总一下，这篇的知识点。学习四步骤：是什么？如何用？有什么用？注意事项。这样就可以很好的从来源到使用技能，做到心中有数。
- 视图
- 子查询
- 标量子查询
- 关联子查询
- 如何用SQL解决业务问题（汇总前面知识点）
- 各种函数（帮助我们解决复杂查询）
下面我们就来一一攻破。
一、视图
A.什么是视图：
以上可以看到表：存放实际数据；视图：存放的是SQL查询语句。详细来说：
- 当客户端查询表内数据，会使用视图运行其SQL语句。
- 从数据库系统内部来看，一个视图是SQL查询语句的临时表，当查询连接断开时，这张虚拟表就会被自动删除。
- 所以视图并不存放数据，他存放的是SQL查询语句。
需要注意的是：
- 视图是一种数据库对象，用户可以象查询普通表一样查询视图。
- 视图内其实没有存储任何数据，它只是对表的一个查询。
- 视图的定义保存在数据字典内。创建视图所基于的表为“基表”。
- 视图一经定义以后，就可以像表一样被查询、修改、删除和更新。
视图是数据库中一个比较重要的组成部分，在隔离实体表的前提下还可以让用户查询到需要的数据，可以起到保护底层数据的作用。同时针对不同的人群，视图也可以起到简化用户对数据的理解。
B.如何创建视图呢？
CREATE VIEW 视图名词(<视图列名1>,<视图列名2>,...)
AS
SELECT column_name -- 查询语句
FROM table_name
WHERE condition
如下，在查询窗口写好查询语句，创建按性别汇总的视图，运行成功后，刷新表结构，就出现新创建的视图“按性别汇总”。
如何使用视图呢？
如果你在工作中需要经常使用“按性别汇总”这个查询，则可以不用每一次都去输入一遍SQL语句，只要如上例所示，创建一个视图，并在from字句中使用视图名称代替表名称。
SELECT 性别,人数
FROM 按性别汇总;
如何删除视图呢？
选中要删除的视图，鼠标右键选择删除视图就可以啦！
C.视图有什么用
如果我们在工作中需要频繁的重复使用某些查询语句，那么一遍遍录入SQL语句显然比较麻烦，这个时候就可以把这个SQL语句保存为视图，随用随取显然就很方便啦！
- 在进行复杂的查询和汇总分析时，使用视图可以很好地帮助我们提高效率；
- 视图中的数据可以随着原表的变化自动更新，可以保证数据的最新状态；
- 视图不需要保存数据，可以节省存储空间。
D.注意事项
- 避免在视图的基础上再去创建视图，多重视图会降低SQL的性能和效率；
- 不能往视图里面插入数据，否则会报错。
二.子查询
之前的我们的学习中，查询条件都是针对具体的数值，如成绩大于60分，可以直接查询。
那么如果我们要查询出成绩大于平均成绩的学生，要怎么做呢？这个时候就需要用到子查询了。
A.什么是子查询
子查询（Sub Query）或者说内查询（Inner Query），简单来说就是一次性的视图，即在from子句中直接写定义视图的SQL查询语句。(也就是在一个select查询语句中嵌套了另一个select查询语句。)
按性别对学生汇总，并查询性别对应的人数
图片中蓝框中的语句就是一个子查询，而按性别汇总就是这个子查询也就是这个一次性视图的名字。需要注意的是，当SQL语句中有子查询的时候，先运行子查询，再运行外部的查询语句。
B.如何使用子查询
刚刚我们学习的子查询是放到from子句里面，其实子查询可以放到where子句中，与运算符in、any、all一起使用，从而构建出复杂的查询条件。使用的方法是在in()、any()、all()的括号里面输入子查询语句。
栗子：在score表中找出每门课程里成绩最低的学号
我们按照上次说的用SQL解决业务问题的步骤来对这个问题进行一个拆解：
- 把业务问题解读成通俗易懂的大白话：
找出每门课程里最低的成绩，并显示最低成绩对应的学号；
2.写出分析思路（按步骤分解）:
- 关键词：每门课程，最低成绩，学号；
- 从哪张表——>score；
- 每门课程——>按课程号分组；
- 最低成绩——>每门课程分组下的成绩的最小值；
- 查询结果：学号，课程号，最低成绩。
3.写出对应的SQL子句:
#查询每门课程分组下成绩最小值
SELECT 课程号,MIN(成绩)
FROM score
GROUP BY 课程号;
#查询这些最小值的学号
SELECT 学号,课程号,成绩
FROM score
WHERE 成绩 in(80,50,40);
#step3 结构第1、2步，把课程号分组下最低成绩作为子查询
SELECT *
FROM score
WHERE 成绩 in (
SELECT MIN(成绩)
FROM score
group by 课程号) ;
上面两种查询结果一直，子查询更方便查询复杂的语句。
any、all关键字如何与子查询一起使用
any、all关键字必须与比较运算符（=、<>、>、>=、<、<=）一起使用。any（子查询）与some（子查询）相同，这里面我们以any为例。
select 列名1
from 表名1
where 列名1 > any(子查询);
栗子，哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？
1.把业务问题解读成通俗易懂的大白话：
选出成绩大于课程“0002”的全部成绩里的任意一个的成绩即可，并显示学号。
2.写出分析思路（按步骤分解）:
- 关键词：课程“0002”，成绩，学号；
- 从哪张表——>score；
- 课程0002——>查询课程0002的全部成绩；
- 查找成绩——>大于课程“0002”的全部成绩里的任意一个的成绩；
- 查询结果：学号，成绩。
3.写出对应的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(子查询)；
- 避免使用多层嵌套子查询，一般不要超过三层。超过的话可以考虑使用临时表将子查询结果先保存，再和其他查询进行关联；
- 尽量不要省略子查询的名称，方便使用及查阅：
#SQL运行顺序
select 查询结果 ----step2 在运行select语句
-----------------
from 哪个表
where查询条件
group by 分组 ----step1 先运行from表操作
having 对分组结果指定条件
order by对查询结果排序
----------------------
limit从查询结果中取出指定行 ----step3 最后对查询结果操作
三.标量子查询
A.什么是标量子查询
栗子：查询大于平均成绩的学生的学号和成绩。
由于where子句中不能够使用汇总函数（where 成绩>avg(成绩)），此时就需要使用标量子查询，来返回平均值这个数据。子查询得到结果是一个数据（一行一列）。
所以正确的语句应该是：
select 学号,成绩
FROM score
where 成绩 > (
SELECT avg(成绩)
FROM score -- avg(成绩)=72.375,此子查询为一行一列结果
);
栗子：查询成绩在优等生平均成绩和差生平均成绩之间的学生有哪些？假设成绩<=60的为差生，成绩>=80的为优等生。
#用标量子查询结合between关键字来进行查询
SELECT 学号,成绩
from score
where 成绩 BETWEEN(
SELECT avg(成绩)
FROM score
where 成绩 <=60) AND
(SELECT avg(成绩)
FROM score
where 成绩>=80);
B.如何使用标量子查询
通常任何使用单一值得地方，都可以使用标量子查询。
SELECT 学号,成绩,
(select avg(成绩) FROM score ) as 平均成绩
FROM score;
C.标量子查询有什么用
子查询不同于视图，视图经常使用可比作私家车，子查询偶尔使用如同出门偶尔打车。子查询又可以分多行子查询和单一值标量子查询。标量子查询由于返回的是一个数值，所以可以同比较运算符以及in、any、all、between等关键字一起使用，构建复杂的查询条件。
D.注意事项
其中括号内的select每次只返回一条，若是返回多条就会报错。
四.关联子查询
A.什么是关联子查询
在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，关联子查询的信息流是双向的，外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录，之后外部查询根据返回的记录做出决策。
栗子：查找出每个课程中大于对应课程平均成绩的学生
1.把业务问题解读成通俗易懂的大白话：
- 按课程号分组得到每门课程的平均成绩；
- 再在每门课程中找出大于该门课程平均成绩的成绩及对应的学号。
2.写出分析思路（按步骤分解）:
- 关键词：课程号，平均成绩，成绩，学号；
- 从哪张表——>score；
- 课程号，平均成绩——>根据课程号查询每门课程的平均成绩；
- 成绩——>查找每门课程中,大于该门课程平均成绩的成绩；
- 查询结果：学号，成绩。
3.写出对应的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子句一样，也可以对表中的数据进行切分。
关联子查询执行逻辑如下：
- 先从主查询的score表中课程号列取出第一个值，进入子查询中，得到子查询结果，然后返回父查询，判断父查询的where子句条件，则返回整个语句的第1条结果。
- 重复上述操作，直到所有主查询中的score表中课程号列记录取完为止。得出整个语句的结果集，就是最后的答案。
C.关联子查询有什么用
在每个组里比较(关联子查询关联条件)。
关联子查询和GROUP BY都可以进行分组，但在应用场景上，有明显的不同。有GROUP BY的SELECT字句中，只能选择被GROUP BY的列，也就是分组列或者聚合函数操作的结果；但是关联子查询则相当于进行一次GROUP BY再JOIN回去一样的。在有关联子查询的query里，SELECT的列是不受限制的。关联子查询的分组，只是将分组查询出来的信息作为新列加上去，不对分组内容进行聚合。
D.注意事项
关联子查询的结合条件如果未出现在子查询之中就会发生错误。
五.如何用SQL解决业务问题
这个之前辅助训练过，不断重复这个过程养成习惯！
六.各种函数
SQL提供了很多现成函数，大家不需要全部记住，使用的时候去搜索即可。常用的大致可分为单行函数和分组函数。
- 单行函数为处理一条数据，输出一个结果，如对字符串的处理等。
- 分组函数又称聚合函数、统计函数或组函数，是对多条记录的统计结果，如求和等。
A.分组函数
分组函数又称为聚合函数、统计函数、组函数，所有分组函数对null值处理为忽略它，而非当做0。常用的分组函数有以下几个：
- SUM() 对数值型数据求和，用+相加，符合+的运算法则
- AVG() 对数值型数据求平均
- MAX() 对所有可比较类型求最大值
- MIN() 对所有可比较类型求最小值
- COUNT() 统计非空个数
分组函数的调用为：
SELECT SUM(成绩) FROM score;
SELECT COUNT(*) FROM score; -- 统计表的总行数
B.字符函数
LENGTH(str) 返回字符串长度
SELECT LENGTH('hello'); -- 结果为5
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'
C.算术函数：
ROUND(X,D) 四舍五入，保留指定位数，默认为保留整数
SELECT ROUND(-1.65); -- -2
abs(X），取绝对值
select abs(-100); -- 100
MOD(X1,X2) 求余数
SELECT MOD(10,3); -- 1,符号与被除数一致
TRUNCATE(X) 截取保留指定小数位
SELECT TRUNCATE(2.666,1); -- 2.6
D.日期函数：
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
