数据库系统原理——SQL数据查询语言(DQL)

一. 内容概述

二. 单表查询

SQL查询语句的基本结构包括3个子句：select、from、where，其中

• select子句对应于关系代数中的投影运算，用来指定查询结果中所需的属性表达式
• from子句对应于关系代数中的笛卡尔积，用来给出查询所涉及的表，表可以是基本表、视图、查询表
• where子句对应于关系代数中的选择运算，用来指定查询结果元组所需要满足的选择条件

对于SQL语句select、from子句是必须的，其他是可选的
基本语法：

select A1, A2, A3, ..., An
from R1, R2, ..., Rn
where P

A1,A2, ..., An代表需要查找的属性或表达式
R1, R2, ..., Rn代表查询所涉及的表
P代表谓词(即选择条件)，如果省略where子句，表示P为真
SQL查询结果允许包含重复元组

SQL 查询的执行过程：

1. 首先对R1, R2, ..., Rn执行笛卡尔积
2. 然后在笛卡尔积中选择使得谓词P为真的记录
3. 再在A1, A2, A3, ..., An属性列中进行投影运算，不消除重复元组

如果需要消除重复元组，使用distinct关键字

2.1 投影运算

2.1.1 查询指定列

1. 定义
   选取表中的全部列或指定列，称为关系代数的投影运算
2. 举例

2.1.2 消除重复元组

1. 定义
   SQL语句查询默认不消除重复列，因为消除重复元组需要消耗资源
   需要消除重复元组，可以使用distinct
2. 举例

不消除重复元组来查询所有学院的名称：

select institute
from class

查询结果一如下：

消除重复元组来查询所有学院的名称

select distinct institute
from class

查询结果二如下：

2.1.3 查询所有列

1. 查询方法
   方法一：

将所有的列在select子句中列出，可以改变顺序

方法二：

使用 * 表示所有的属性，按表定义的顺序显示属性

2.1.4 给属性列取别名

1. 定义
   为属性列取别名之后，属性上方的列名就会改变，适合于那些经过计算的列
2. 举例：as可以省略

select institute 所属学院, classNo 班级编号, className 班级名称
from Class

或
select institute as 所属学院, classNo as 班级编号, className as 班级名称
from Class

查询结果如下图：

2.1.5 查询经过计算的列

1. 定义
   select 子句可以使用属性、常数、函数、表达式，如果是函数和表达式，则先计算函数和表达式的值，然后将计算的结果显示出来
2. 举例

查询每门课的课程号、课程名(将课程名中大写字母变为小写)以及周课时(周课时=课时数/16)

select courseNo 课程号, lower(courseName) 课程名, courseHour/16 周课时
from Course

查询结果如下图：

2.2 选择运算

where子句可以实现关系代数中的选择运算，用于查询满足选择条件的元组

where子句中常用的查询条件运算符如下：

• 比较范围：>, >=, <, <=, =, <>（或！=）
• 范围查询：between… and
• 集合查询：in
• 空值查询：IS null
• 字符匹配查询：like
• 逻辑查询：and， or，not

2.2.1 比较运算

1. 定义
   使用比较运算符：>, >=, <, <=, =, <>（或！=）实现相应的比较运算
2. 举例

2.2.2 范围查询

1. 定义
   between… and可用于查询属性值在某一个范围内的元组，
   not between… and可用查询属性值不在某一个范围内的元组
2. 举例

在score表中查询成绩在80~90分之间的同学学号、课程号、成绩

select studentNo, courseNo, score
from Score
where score between 80 and 90

查询解决如下：

2.2.3 集合查询

1. 定义
   in可用于查询属性值在某个集合内的元组
   not in可用于查询属性值不在某个集合内的元组
2. 适用范围
   in后面可以是具体的集合
   in后面也可以是查询出来的元组集合
3. 当in后面的集合是具体的集合

在score表中查询选修了001,005,003课程的同学学号、课程号、相应成绩

select studentNo, courseNo, score
from Score
where courseNo in ('001', '005', '003')

查询结果如下：

1. 当in后面的集合是查询出来的元组集合
   详细内容见嵌套子查询

2.2.4 空值查询

1. 定义
   SQL支持空值查询
   空值表示未知或不确定的值，空值为null
   is null用于查询属性值为空值 （is不能用=替换）
   not is null用于查询属性值不为空值
2. 举例

在course表中查询先修课程为空值的课程信息

select *
from Course
where priorCourse is null

查询结果如下：

2.2.5 字符匹配查询

1. 定义

对于字符型数据，like可用于字符匹配查询

like的语法格式：

[not] like <匹配字符串> [escape <换码字符>]

2. 查询含义

查询指定属性列值与<匹配字符串>相匹配的元组

<匹配字符串>可以是一个具体的字符， 也可以包括通配符%和_
  
  符号%表示任意长度的字符串
  符号_表示任意一个字符

3. 举例

• 例一：查询一个具体的字符

在student表中查询蒙古族的同学学号、姓名

select studentNo, studentName
from Student
where nation like '蒙古族'

等价于：

select studentNo, studentName
from Student
where nation = '蒙古族'

查询结果如下：

• 例二：查询没有具体长度的字符串

在class表中查询班级名称中含有会计的班级信息

select *
from Class
where className like '%会计%'

查询结果如下：

• 例三：查询具体长度的字符串

在student表中查询所有姓王且全名为3个汉子的同学的学号和姓名
select studentNo, studentName
from Student
where studentName like '王__'

查询结果如下：

• 例四：查询带有通配符%或_的字符串

在班级class表中串行班级名称含有"16_"符号的班级名称

select className
from Class
where className like '%16\_%' escape '\'

注：escape'\' 表示\为换码字符，有了这个之后16后面的_就不是通配字符了，而是普通要查询的字符

查询结果如下：

2.2.6 逻辑运算

1. 使用and、or、not逻辑运算符分别实现逻辑与、或、非运算
2. 举例

2.3 排序运算

1. 定义
   SQL支持排序运算，通过使用order by子句实现
   其语法为：

order by <表达式1> [asc|desc]
 
 默认情况下按升序排序
 表达式1：可以是属性、函数、表达式
 
 若表达式1后面还有表达式2的话表示：
 先按表达式1排序，再在表达式1值相等的情况下按表达式2排序

1. 举例

2.4 查询表

1. 定义
   SQL中from子句后面可以是基本表、视图、查询表
2. 举例

查询1905年出生的女学生基本信息

select studentNo, studentName, birthday
from (select * from Student where sex='女') A
where year(birthday)=1905

等价于
select studentNo, studentName, birthday
from Student
where year(birthday)=1905 and sex='女'

注：from子句之后是一个查询表，表示对该查询的查询结果——查询表进行查询，必须为查询表取一个别名（该名称称为元组变量）

执行结果如下：

2.5 聚合查询

SQL通过聚合函数实现统计功能，通过分组子句实现分类功能，将统计和分组组合在一起实现查询功能

2.5.1 聚合函数

1. SQL提供的聚合函数如下：

• count（ [distinct | all] * | <列名> ）

统计关系的元组个数或一列中值的个数

• sum（[distinct | all] <列名>）

统计一列中值的总和（此列必须为数值型）

• avg（[distinct | all] <列名>）

统计一列中值的平均值（此列必须为数值型）

• max（[distinct | all] <列名>）

统计一列中值的最大值

• min（[distinct | all] <列名>）

统计一列中值的最小值

注：

默认是没有distinct即all谓词，表示不能消除<列名>取重复值的元组
指定了distinct就是在计算时首先消除<列名>取重复值的元组

2. 举例

1. 例一：查询学生总人数

情况一
select count(*)
from Student

查询结果如下：

情况二
select count(*) 学生人数
from Student

查询结果如下：

总结：

使用聚合函数的时候，对计算列要取一个别名，便于理解

 在聚合函数遇到空值时，除了count(*)外所有的函数皆跳过空值

1. 例二：查询选课总人数

情况一
select count(studentNo) 学生人数
from Score

查询结果如下：

情况二
select count(distinct studentNo) 学生人数
from Score

查询结果如下：

总结：使用聚合函数的时候，为了消除重复元组，必须使用distinct短语

2.5.2 分组聚合

在SQL查询中，往往需要对数据进行分组运算，分组运算的目的是为了
细化聚合函数的作用对象

如果不分组查询，聚合函数将作用域整个查询结果。查询结果是按组聚合输出

如果对查询结果进行分组，则聚合函数分别作用与每个组，查询结果是按组聚合输出

1. SQL语句中通过使用group by 和having子句来实现分组运算

• group by 子句

作用：对查询结果按某一列或某几列进行分组，值相等的分为一组

• having子句

作用：对分组的结果进行选择，仅输出满足条件的组
        必须与group by子句配合使用

2. 举例

1. 举例一

查询每个同学的选课门数、平均分、最高分

select studentNo, count(*) 门数, avg(score) 平均分, max(score) 最高分
from Score
group by studentNo

注：查询的结果按学号分组，将相同学号值的元组作为一组，然后对每组进行相应的计数、求平均值、求最大值

查询结果如下：

1. 举例二

查询平均分在75分以上的每个同学的选课门数、平均分、最高分

select studentNo, count(*) 门数, avg(score) 平均分, max(score) 最高分
from Score
group by studentNo
having avg(score)>75

查询结果如下：

三. 连接查询

在现实应用中大多会涉及多个关系的查询，这时候就需要使用连接运算或子查询

3.1 连接运算

3.1.1 分类

3.1.2 等值与非等值连接

等值与非等值连接运算是在where子句中加入连接多个关系的连接条件

1. 等值连接

1. 格式

where 表1.属性名=表2.属性名 and 表3.属性名=表4.属性名 ...
 
 where子句连接谓词中的属性是连接属性，必须具有可比性

1. 举例

查询会计学院全体同学的学号、姓名、籍贯、班级编号、所在班级名称

select studentNo, studentName, native, Student.classNo, className
from Student , Class 
where Student.classNo = Class.classNo and institute = '会计学院'

注：在连接操作中如果涉及多个表的相同属性名，必须在相同属性名前加上表名来区分

2. 非等值连接

基本不用

3.1.3 自然连接

1. 定义
   SQL不支持自然连接，完成自然连接要在等值连接的基础上消除重复列

3.1.4 自表连接

1. 定义
   某个表与自己进行连接
2. 举例

在student表中查找与李宏冰同学在同一班级的同学姓名、班级编号、出生日期

select b.studentName, b.classNo, b.birthday
from Student a, Student b
where a.studentName = '李宏冰' and a.classNo=b.classNo

注：可能不太好理解，可以通过举例，做一个小表来演示理解

查询结果如下：

3.1.5 外连接

1. 定义

在一般的连接中，只有满足连接条件的元组才被检索出来

但在实际生活中，往往需要将不满足连接条件的元组也检索出来，只是在相应的位置用空值替代，这种查询称为外连接查询

2. 分类

3. 左外连接

1. 定义
   在SQL查询中的from子句中，写在左边的表称为左关系
   左外连接的连接结果中包含左关系中的所有元组，对于左关系中没有连接上的元组，其右关系中相应的属性用空值替代
2. 举例

情况一：使用一般查询来查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名、按班级名称排序

select className, institute, studentNo, studentName 
from Class a, Student b
where a.classNo = b.classNo and grade = 2015
order by className

查询结果如下：

注：虽然class表里面存在金融管理15-01班级，但是student表里面没有任一学生是这个班级的，所以查询结果就没有显示金融管理15-01班级

情况二：使用左外连接查询来查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名、按班级名称和学号排序

select className, institute, studentNo, studentName 
from Class a left outer
join Student b
on a.classNo = b.classNo 
where grade = 2015
order by className, studentNo

查询结果如下：

4. 右外连接

右外连接的连接结果中包含右关系中的所有元组，对于右关系中没有连接上的元组，其左关系中相应的属性用空值替代

5. 全外连接

全外连接的连接结果中包含左右关系中的所有元组，对于左关系中没有连接上的元组，其右关系中的相应属性用空值替代。对于右关系中没有连接上的元组，其左关系中的相应属性用空值替代

4.1 嵌套子查询

4.1.1. 定义

在SQL查询中，一个select-from-where查询语句称为一个查询块，将一个查询块嵌入到另一个查询块where子句或having子句，称为嵌套子查询

4.1.2. 使用子查询的目的

1. 元素与集合间的属于关系
2. 集合之间的包含和相等关系
3. 集合的存在关系
4. 元素与集合元素之间的关系

4.1.3. SQL嵌套查询的分类

1. 非相关子查询
   指子查询的结果不依赖于上层的查询
2. 相关子查询
   指当上层查询的元组发生变化，其子查询必须重新执行

4.1.4. 使用in的子查询

1. 定义
   由于select语句的查询结果是一个元组的集合，因此可以嵌套select语句到in子句中
2. 举例 ：二层嵌套

在学生表student中，将学号出现在成绩表score中（表明该学生选修过课程）的学生姓名查询出来

select studentName
from Student
where Student.studentNo 
  in (select score.studentNo from Score)

注：本例where子句用于检测元素与集合间的属于关系，
  其中student.studentNo为元素，
  in表示属于，
  嵌套select语句查询结果为选修过课程的所有学生的学号集合

3. 举例：三层嵌套

查询选修课程名中包含“系统”的课程的同学学号、姓名、班级编号

select studentNo, studentName, classNo
from Student
where studentNo in 
  (select studentNo from Score 
    where courseNo in
      (select courseNo from Course
        where courseName like '%系统%'
      )
   )

4. 总结：in的查询过程（从底层到最上层）

4.1.5 比较运算符的子查询

元素与集合元素之间还存在更为复杂的关系，如比较关系

比较关系中使用any和all

两者的比较：

注：在比较运算符中，

=any等价于in谓词
！=all等价于not in谓词

4.1.6 使用存在量词exists的子查询

1. 定义
   SQL查询提供了量词运算

量词分为两种：存在量词和全称量词

1. 查询过程

5.1 集合运算

1. 定义
   SQL支持集合运算
   select语句的查询结果是集合，多个select语句的结果可以进行集合操作，传统的集合操作主要包含并union、交intersect、差except运算

6.1 SQL查询的一般格式

SQL查询语句select共6个子句，其中select和from是必须的，其他的是可选项，他们必须严格按照如下顺序排列

select [all|distinct] <目标列> [别名]...
from <表名|视图名|查询表> [别名]
[where 条件表达式]
[group by 列名1...]
[having <条件表达式>]
[order by <列名> [asc|desc]]