一、利用SQL语言表达复杂查询之(NOT)IN子查询

1.1 子查询

  • 子查询：出现在Where子句中的Select语句被成为子查询(Subquery)，子查询返回了一个集合，可以通过与这个集合的比较来确定另一个查询集合。
  • 三种类型的子查询：(NOT)IN-子查询；-some/-All子查询；(NOT)EXISTS子查询

1.2 IN与NOT IN谓词子查询

  • 基本语法：
表达式 [not] in (子查询)
  • 语法中，表达式的最简单的形式就是列名或常数
  • 语义：判断某一表达式的值是否在子查询的结果中

1.3 非相关子查询

  • 带有子查询的Select语句区分为内层和外层

mysql 复杂查询案例 复杂的sql查询_SQL

  • 非相关子查询：内层查询独立进行，没有涉及任何外层查询相关信息的子查询

1.4 相关子查询

  • 相关子查询：内层查询需要依靠外层查询的某些变量作为限定条件才能进行的子查询
  • 外层向内层传递的参量需要使用外层的表名或表别名来限定
  • 注意：相关子查询只能由外层向内层传递参数，而不能反之；这也称为变量的作用域原则

二、利用SQL语言表达复杂查询

-Some与

-All子查询

2.1 

-Some与

-All谓词子查询

  • 基本语法：
表达式 比较运算符 some (子查询)
表达式 比较运算符 all (子查询)
  • 是比较运算符
  • 语义：将表达式的值与子查询的结果进行比较
  • 如果表示的值至少与子查询结果的某一个值相比较满足关系关系，则“表达式 some (子查询)”的结果为真
  • 如果表达式的值和子查询结果的所有值相比较都满足关系，则“表达式  all (子查询)”的结果为真

2.2 注意事项

  • 在SQL标准中，有 any谓词，但由于其语义的模糊性，所以被 some替代以求更明晰
  • 等价变换
  • 表达式 = some (子查询) 相当于 表达式 in (子查询)
  • 表达式 not in (子查询) 相当于 表达式 <> some (子查询)

三、利用SQL语言表达复杂查询 --(NOT) EXISTS子查询

3.1 EXISTS 与NOT EXISTS谓词子查询

  • 基本语法：
[not] Exists (子查询);
  • 语义：子查询结果中有无元组存在
  • 利用exists和not exists子查询的时候可以适当的进行语义的转换为更加方便

四、利用SQL语言进行结果计算与聚集计算

4.1 结果计算

  • selet-from-where 语句中，select子句后面不仅可以是列名，而且可以是一些计算表达式或聚集函数，表明在投影的同时直接进行一些计算
select 列名|expr|agfunc(列名) [[,列名|expr|agfunc(列名)]...]
from 表名1 [,表名2]
[where 检索条件];
  • expr可以是常量、列名、或有常量、列名、特殊函数及算术运算符构成的算术运算式。特殊函数的使用需要结合各DBMS的说明书
  • agfunc()是一些聚集函数

4.2 应用聚集函数进行统计计算

4.2.1 聚集函数

  • SQL提供了五个作用在简单列值集合上的内置聚集函数agfunc，分别是：COUNT、SUM、AVG、MAX、MIN

mysql 复杂查询案例 复杂的sql查询_mysql 复杂查询案例_02

五、利用SQL语言进行 分组查询与分组过滤

5.1 分组查询

  • 分组：SQL可以将检索到的元组按照某一条件进行分类，具有相同条件值的元组划到一个组或一个集合中，同时处理多个组或集合的聚集运算
  • 分组的基本语法
select 列名|expr|agfunc(列名) [[,列名|expr|agfunc(列名)]...]
from 表名1 [,表名2]
[where 检索条件]
[group by 分组条件];
  • 分组条件可以是：列名1，列名2,...

5.2 分组过滤

  • 聚集函数是不允许用于where子句中的：where子句是对每一运足进行条件过滤，而不是对集合进行条件过滤
  • 分组过滤：若要对集合(即分组)进行条件过滤，即满足条件的集合/分组留下，不满足条件的集合/分组剔除
  • Having子句，有称为分组过滤子句。需要有group by子句支持，换句话说，没有group by子句，便不能用having子句
select 列名|expr|agfunc(列名) [[,列名|expr|agfunc(列名)]...]
from 表名1 [,表名2]
[where 检索条件]
[group by 分组条件 [having 分组过滤条件]];

5.3 分组过滤条件与WHERE条件之对比

  • 每一分组检查满足与否的条件要用having子句表达。注意：不是每一行都检查，所以使用having子句一定要有group by子句
  • 每一行都要检查满足与否的条件要用where子句表达

六、利用SQL语言实现关系代数操作

6.1 并-交-差的处理

  • SQL语言：并运算UNION，交运算INTERSECT，差运算EXPECT
  • 基本语法形式
子查询 {Union [ALL]|Intersect [ALL]|except [ALL] 子查询}
  • 交运算符Intersect并没有增强SQL的表达能力，没有Intersect，SQL可以使用其他方式表达同样的查询需求。只是有了Intersect，SQL也可以用其他方式表达同样的查询需求，实施有了Intersect更容易表达一些，但增强了SQL语言的不唯一性
  • 差运算Expect也没有增强SQL的表达能力，没有Except，SQL也可以用其他方式表达同样的查询需求。只是有了Except更容易表达一些，但增加了SQL语言的不唯一性。
  • UNION运算符是Entry-SQL92的一部分
  • INTERSECT、EXCEPT运算符是Full-SQL92的一部分
  • 它们都是Core-SQL99的一部分，但有些DBMS并不支持这些运算，使用时要注意

6.2 空值的处理

  • 空值是其值不知道、不确定、不存在的值
  • 数据库中有了空值，会影响许多方面，如影响聚集函数运算的正确性，不能参与算术、比较或逻辑运算等
  • 在SQL标准中和许多现流行的DBMS中，空值被用一种特殊的符号Null来标记，使用特殊的空值检测函数来活的某列的值是否为空值
  • 空值检测
is [not] null
  • 测试指定列的值是否为空值
  • 现行DBMS的空值处理小结
  • 除is [not] null之外，空值不满足任何查找条件
  • 如null参与算术运算，则该算术运算表达式的值为null
  • 如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown
  • 如果null参与聚集运算，则除count(*)之外其他聚集函数都忽略null

6.3 内连接、外连接

  • 关系代数运算中，有连接运算，又分连接和外连接
  • 标准SQL语言中连接运算通常是采用
select 列名 [[,列名]...]
from 表名1, 表名2,...
where 检索条件;
  • 即相当于采用

mysql 复杂查询案例 复杂的sql查询_SQL_03

  • SQL的高级语法中引入了内连接和外连接运算，具体形式：
select 列名 [[,列名]...]
from b表名1 [NATURAL]
[INNER|{LEFT|RIGHT|FULL}[OUTER]] JOIN 表名2
{ON 连接条件|Using (Colname{, Colname...})}
[where 检索条件]...;

mysql 复杂查询案例 复杂的sql查询_mysql 复杂查询案例_04

  • Inner Join：即关系代数中的-连接运算
  • Left Outer Join, Right Outer Join, Full Outer Join：即关系代数中的外连接运算
  • 如“表1 Left Outer Join 表2”，则连接后，表1的任何元组t都会出现在结果表中，如果2中有满足连接条件的元组s，则t与s连接；否则t与空值元组连接
  • 如“表1 Right Outer Join 表2”，则连接后，表2的任何元素s都会出现在结果表中，如表1中有满足连接条件的元组t，则t与s连接；否则s与空值元组连接
  • 如“表1 Full Outer Join 表2”，是前两者的并
  • 连接中使用natural
  • 出现在结果关系中的两个连接关系的元组在公共属性上取值相等，且公共属性值出现一次
  • 连接中使用on<连接条件>
  • 出现在结果关系中的两个连接关系的元组取值满足连接条件，且公共属性只出现两次
  • 连接中使用using(col1,col2,...,coln)
  • (col1,col2,...,coln)是两个连接关系的公共属性的子集，元组在(col1,col2,...,coln)上取值相等，且(col1,col2,...,coln)只出现一次

七、SQL语言之视图及其应用

7.1 SQL-视图的概念和结构

  • 回顾：三级模式两级映像结构
  • 对应概念模式的数据在SQL中被称为基本表(Table)，而对应外模式的数据称为视图(View)。视图不仅包含外模式，而且包含其E-C映像

mysql 复杂查询案例 复杂的sql查询_元组_05

  • SQL数据库结构
  • 基本表是实际存储于文件中的表，基本表中的数据是需要存储的
  • 视图在SQL中只存储其由基本表导出视图所需要的公式，即由基本表产生视图的映像信息，其数据并不存储，而是在运行过程中动态产生与维护的
  • 对驶入数据的更改最终要反映在对基本表的更改上

mysql 复杂查询案例 复杂的sql查询_子查询_06

  • 视图需要“先定义，再使用”
  • 定义视图
create view view_name [(列名[, 列名]...)]
as 子查询 [with check option];
  • 如果视图的属性名缺省，则默认为子查询结果中的属性名；也可以显式的指明其所拥有的列名
  • with check option指明当对视图进行insert，update，delete时，要检查进行insert/update/delete的元组是否满足视图定义中子查询中定义的条件表达式

7.2 SQL-视图的更新问题

  • SQL视图更新：是比较复杂的问题，因视图不保存数据，对视图的更新最终要反映到对基本表的更新上，而有时，视图定义的映射不是可逆的
  • SQL视图更新的可执行性
  • 如果视图的select目标列包含聚集函数，则不能更新
  • 如果视图的select子句使用了unique或distinct，则不能更新
  • 如果视图中包括了group by子句，则不能更新
  • 如果视图中包括经算术表达式计算出来的列，则不能更新
  • 如果视图是由单个表的列构成，但并没有包括主键，则不能更新
  • 对于单一Table自己构成的视图，即如果视图是从单个基本表使用选择、投影操作导出的，并且包含了基本表的主键，则可以更新

7.3 视图的撤销

  • 撤销视图
drop view view_name;