使用了无效的sql语句_SQL之复杂查询

本章将介绍以下知识点：

1. 视图
2. 子查询
3. 标量子查询
4. 关联子查询
5. 各种函数
6. 本章注意事项

---

1. 视图

视图定义：

从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图保存的是SELECT语句（视图本身不存储数据）。

视图的优点：

• 视图本身不存储数据，节省存放数据的空间。运行视图时会运行视图里的SQL查询语句，创建出一张临时表；
• 频繁使用的SELECT查询语句可存储在视图中供下次使用，可提高工作效率。

创建视图的 CREATE VIEW 语句：

CREATE VIEW 视图名称(<视图列名1>,<视图列名2>, ......)
AS
<SELECT语句>;

注意事项：

• 视图中AS不能省略；
• SELECT语句中列的顺序和视图中列的顺序是一一对应的，列的名称可以不一样，但是关系是一一对应的；
• 避免在视图的基础上创建视图，因为多重视图会降低SQL的性能；
• 定义视图时不能使用ORDER BY子句；
• 视图和表需要同时进行更新，因此，通过汇总得到的视图无法进行更新。

练习：

1）创建视图：按性别汇总学生人数

第一步：

第二步：运行后右键点击视图-刷新

双击视图即可看见按性别汇总视图设置成功

下次如果要调用视图的sql语句，直接在查询窗口输入下图所示sql语句即可

如果想要删除该视图：

2. 子查询

子查询定义：

将用来定义视图的SELECT查询语句直接用于FROM子句中。

子查询的特点：

子查询的特点就是一次性视图（SELECT 语句）。与视图不同，子查询在SELECT语句执行完毕后就会消失。

子查询vs.视图：

若偶尔使用该查询语句，则可以使用子查询；若频繁使用该查询语句，可创建视图方便下次使用。

我们可以将上个例子的视图转换成子查询的形式：

FROM括号内的子查询作为内层查询会首先执行得到一个临时表，然后再运行外层的查询。

另外，我们还可以在一些逻辑运算符后使用子查询语句来达到我们的查询目的：

• ...in(子查询)
• ...any(子查询)
• ...all(子查询)

注意事项：

• 尽量避免使用多层嵌套的子查询，因为多重嵌套不仅难读懂，还会降低SQL的性能；
• ALL()得到的是一个集合，不能使用a>一个数字如3*ALL(),可以写成a/3>all(b)；

练习：

1）哪些学生的成绩与课程0003的全部成绩里的任意一个相同呢？

方法一：

方法二：

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

3）哪些学生的成绩比课程0002的全部成绩里的都高呢？

3. 标量子查询

标量子查询的定义：

标量子查询就是返回单一值的子查询。

如果我们想要查询大于平均成绩学生的学号和成绩，如果在WHERE子句中使用AVG()聚合函数，这时就会出现如下报错

报错：聚合函数是无效的。

这是因为WHERE子句中不能使用聚合函数，这时标量子查询就可以派上用场了。

本例中括号中的就是标量子查询--只能返回一行一列的子查询

练习：

1）查询成绩介于差生（成绩<=60）的平均成绩和优等生（成绩>=80）的平均成绩的学生有哪些

另外，标量子查询的书写位置不仅仅局限于WHERE子句中，通常任何可以使用单一值的位置都可以使用：

注意事项：

• 标量子查询不能返回多行结果，返回的是单一值，可以与比较运算符一起使用；
• WHERE子句中不能使用聚合函数。

4. 关联子查询

关联子查询的定义：

关联子查询就是会在细分的组内进行比较时使用的子查询。

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

注意事项：

• 起到关键作用的就是在子查询中添加的WHERE子句的条件 ：WHERE S1.课程号 = S2.课程号，该条件只能在子查询的内部使用，因为内部可以看到外部的S1表，而外部看不到内部S2表；
• 使用关联子查询时，通常会使用“限定”或者“限制”这类语言，在细分的组内进行比较。

5. 各种函数

下面总结一下SQL中常用的函数：汇总函数、算术函数、字符串函数、日期函数

5.1 汇总函数

5.2 算术函数

5.3 字符串函数

5.4 日期函数

1) 当前日期和时间：

2) 当前日期：

3) 当前时间：

4) 获取当前日期的年份：

5) 获取当前日期的月份：

6) 获取当前日期是哪一天：

7) 获取当前日期对应星期几:

函数不用死记硬背，需要用时可通过搜索引擎查找。

6. 本章注意事项

• 视图中AS不能省略；
• SELECT语句中列的顺序和视图中列的顺序是一一对应的，列的名称可以不一样，但是关系是一一对应的；
• 避免在视图的基础上创建视图，因为多重视图会降低SQL的性能；
• 定义视图时不能使用ORDER BY子句；
• 视图和表需要同时进行更新，因此，通过汇总得到的视图无法进行更新；
• 尽量避免使用多层嵌套的子查询，因为多重嵌套不仅难读懂，还会降低SQL的性能；
• ALL()得到的是一个集合，不能使用a>一个数字如3*ALL(),可以写成a/3>all(b)；
• 标量子查询不能返回多行结果，返回的是单一值，可以与比较运算符一起使用；
• WHERE子句中不能使用聚合函数；
• 起到关键作用的就是在子查询中添加的WHERE子句的条件 ：WHERE S1.课程号 = S2.课程号，该条件只能在子查询的内部使用，因为内部可以看到外部的S1表，而外部看不到内部S2表；
• 使用关联子查询时，通常会使用“限定”或者“限制”这类语言，在细分的组内进行比较；
• 函数不用死记硬背，需要用时可通过搜索引擎查找。