• 0

  • 153

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

猿人不正经

不想写代码

2星期前

一. 内容概述

在这里插入图片描述

二. 单表查询

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

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

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')
    

    查询结果如下:
    在这里插入图片描述

  4. 当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排序
    
  2. 举例

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(*)外所有的函数皆跳过空值
    
  2. 例二:查询选课总人数

    情况一
    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
    
    注:查询的结果按学号分组,将相同学号值的元组作为一组,然后对每组进行相应的计数、求平均值、求最大值
    

    查询结果如下:
    在这里插入图片描述

  2. 举例二

    查询平均分在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子句连接谓词中的属性是连接属性,必须具有可比性
    
  2. 举例

    查询会计学院全体同学的学号、姓名、籍贯、班级编号、所在班级名称
    
    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 '%系统%'
			)
	 )

在这里插入图片描述

  1. 总结:in的查询过程(从底层到最上层)
    在这里插入图片描述

4.1.5 比较运算符的子查询

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

比较关系中使用any和all

两者的比较:
在这里插入图片描述
注:在比较运算符中,

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

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

  1. 定义

    SQL查询提供了量词运算

     量词分为两种:存在量词和全称量词
    
  2. 查询过程
    在这里插入图片描述

5.1 集合运算

  1. 定义

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

6.1 SQL查询的一般格式

select [all|distinct] <目标列> [别名]...
from <表名|视图名|查询表> [别名]
[where 条件表达式]
[group by 列名1...]
[having <条件表达式>]
[order by <列名> [asc|desc]]
免责声明:文章版权归原作者所有,其内容与观点不代表Unitimes立场,亦不构成任何投资意见或建议。

153

相关文章推荐

未登录头像

暂无评论