视图使用like失效_SQL复杂查询：子查询和视图

本章内容汇总

一．视图 VIEW

当某个SQL查询语句需要频繁使用时，可以保存为视图。

视图中存放的是SQL 查询语句。使用视图时，会运行视图的SQL查询语句创建出一张临时表。当客户端和数据库的连接断开时，视图会失效。

-- 新建视图
CREAT view 视图名称 (视图列名1,视图列名2)
as
select 查询语句

练习

CREATE view 按性别汇总(性别,人数)
as
SELECT 性别,count(*)
FROM student
GROUP BY 性别;

定义后的视图使用方式：用法同表，直接FROM+ 视图名称

/*从视图中查询数据*/
Select 性别,人数
FROM 按性别汇总;

视图可以有效的减少操作次数，提高工作效率。

视图会随着原始数据的更新自动更新，保证数据的准确性。

不需要提取数据，可以节省存储数据的空间。

二．子查询

子查询是指在一个select语句中嵌入另一个select语句。子查询是一次性的视图，在SQL语句查询结束后便消失作用。

包含子查询的SQL语句运行顺序是先运行子查询，再运行外部查询语句。

子查询可被用在select子句，from子句，where子句中

其中在where子句中可与运算符in，any，some，all一起使用，构建复杂的查询条件。子查询在any、some和all后一起使用时必须与“=”“<>”“>”等比较运算符一起使用。

any（子查询）和some（子查询）功能一致，表示子查询中的任何一个值。

all（子查询）表示子查询中的全部值。

练习

/*哪些学生的成绩比课程0002的全部成绩里的任何一个高呢*/
SELECT 学号,成绩
FROM score
WHERE 成绩 > any(SELECT 成绩 FROM score where 课程号='0002');

-

/*哪些学生的成绩比课程0002的全部成绩都高呢*/
SELECT 成绩
FROM score
WHERE 成绩> all(SELECT 成绩 FROM score where 课程号='0002');

子查询使用注意事项：

①比较运算符使用时，不可以使用以下形式a>3*all(b)，因为all(b)是一个数据集，并不是一个数值，可以用a/3>all(b)来进行比较

②要避免子查询的层层嵌套，不利于后续的维护，且SQL语句性能也会很差

③子查询名称可以省略，但要尽量养成良好的工作习惯不要省略，方便SQL语句的阅读。

三．标量子查询

标量子查询指只能返回一行一列查询结果的子查询，即单一值的子查询。因此可以和比较运算符一起使用。

练习：

/*大于平均成绩学生的学号和成绩*/
SELECT 学号,成绩
FROM score
WHERE 成绩>(SELECT AVG(成绩) as '平均成绩'
            FROM SCORE
      );

-

/*介于差生平均成绩和优等生平均成绩之间的学生成绩*/
/*差生（成绩<=60），优等生（成绩>80）*/
SELECT 学号,成绩
FROM score
WHERE 成绩 between (SELECT AVG(成绩) as '差生平均成绩' FROM score WHERE 成绩<=60)
           and (SELECT AVG(成绩)as '优等生平均成绩' FROM score WHERE 成绩>90);

四．关联子查询

当每个组里进行比较时使用关联子查询，通过关联条件进行比较。关联条件一定要写在子查询里。

/*查找出每个课程中大于平均成绩的学生*/
SELECT 学号,成绩,课程号
FROM score as S1
WHERE 成绩>(SELECT avg(成绩)
            FROM score as S2
   WHERE S1.课程号= S2.课程号
            GROUP BY 课程号);

子查询中的别名仅在子查询中有效。上例中S2仅在子查询中有效。

关联子查询中的GROUP BY 子句可以省略。

五．各种函数

可根据实际需要，查询语句中灵活运用各种函数。常用的4种函数：汇总函数、算数函数、字符串函数、日期函数

六. 复杂查询书写思路和运行顺序

1. 书写思路：翻译成大白话——写出分析思路——写出对应的SQL

2. 运行顺序:

有子查询时，子查询优先运行。查询语句整体运行顺序如下：