【数据库系统概念】第三章 SQL语句(下)~重要!!!

2024-01-10 08:43:54

文章目录

  • 本文仅供零基础考试前预习使用
  • 本文不知抄袭了多少篇文章~侵删,(考试期间整理的,现在大抵是找不到各路大神了)
  • 本人对系列文章内容不具备任何解释权~考完即忘,如有疑问请勿评论

【数据库系统概念】第三章 SQL语句(上)~重要!!!

3.5 集合运算

??SQL作用在关系上的union、intersect和except运算对应于数学集合论中的交,并和差,我们现在来构造包含在两个集合上使用union, intersect和except运算的查询。

all 保留各种重复

在这里插入图片描述

3.5.1 并运算 union

  • UNION 运算符将两个或更多查询的结果组合起来,并?成?个结果集,其中包含来? UNION 中参与查询的提取行
  • 🔑UNION 基本规则(后续intersect和except/minus规则类似)
    • 所有查询的列数和列顺序必须相同。
    • 每个查询中涉及表的列的数据类型必须相同或兼容。
    • 通常返回的列名取自第?个查询。
    • 默认会去除相同行,只保留一行。需要保留相同行,使?用UNION ALL
找出2017年秋季开课或2018春季开课的课程
  • 没有all
(select coursr_id
from section
where semester='Fall' and year=2017)
union
(select coursr_id
from section
where semester='spring' and year=2018)
找出2017年秋季开课或2018春季开课的课程或者两个学期都开设的课程
  • 有all
(select coursr_id
from section
where semester='Fall' and year=2017)
union all
(select coursr_id
from section
where semester='spring' and year=2018)
  • 代码2的结果里,两个学期都开课的课程会出现两次

3.5.2 交运算 intersect

在这里插入图片描述

找出在2017年秋季和2018春季都开课的课程
(select coursr_id
from section
where semester='Fall' and year=2017)
intersect
(select coursr_id
from section
where semester='spring' and year=2018)

??如果想保留所有重复,则用intersect all替换intersect。

3.5.3 差运算 except

在这里插入图片描述

找出在2017年秋季开课但是不在2018春季开课的课程
(select coursr_id
 from section
 where semester='Fall' and year=2017)
except
(select coursr_id
 from section
 where semester='spring' and year=2018)

如果想保留所有重复,则用except all替换except。

3.6 空值

??空值给关系运算带来了特殊的问题,包括算术运算、比较运算和集合运算。SQL将涉及空置的各种比较运算结果都视为unknown,这创建了True和False之外的第三个逻辑谓词。

针对空值的一些处理(where子句,select子句等)

在这里插入图片描述

3.7 聚集函数

? 聚集函数是以值的一个集合(集或多重集)为输入、返回单个值的函数。SQL提供了五个固有聚集函数:
在这里插入图片描述

  • sum和 avg 的输人必须是数字集,但其他运算符还可用在字数据类型的集合上,如字符串。

3.7.1 基本聚集avg、min、max、sum、count

select 函数符号(<distinct> 目标列表达式) <as 新属性名>
from 关系名
<where P>

实质上是返回输入的一个汇集结果,一般常用上述五种函数。
在这里插入图片描述

3.7.2 分组聚集group by

??有时候我们不仅希望将聚集函数作用在单个元组集上,而且也希望将其作用到一组元组集上;在SQL中可用group by子句实现这个愿望。group by子句中给出的一个或多个属性是用来构造分组的。在group by子句中的所有属性上取值相同的元组将被分在一个组中。 示例如下:

  • 格式:group by 列名 <having 条件表达式>
    • group by 将表中的元组按指定列值相等的原则分组,然后在每一分组上使用聚集函数,得到单一值
    • having 则对分组进行选择,只将聚集函数作用到满足条件的分组上

在这里插入图片描述

注意事项

  • 出现在select语句中,没有被聚集的属性必须出现在group by子句中 (分组属性,组内值相同)

    select dept_name, avg(salary)as avg_salary
    from instructor
    group by dept_name
    having avg(salary)>42000;
    
  • 出现在having子语中,但没有被聚集的属性必须出现在group by子句中 (小组筛选,也必须用分组属性)

3.7.3 having子句

? having用于对分组设定限定条件

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

having和where的区别
  • where执行在group by之前,针对原始的数据筛选
  • having只能用在group by之后,针对分组后的内容筛选
  • where后的条件表达式里不允许使用聚集函数,而having可以。
语法逻辑
  • 最先根据from子句计算出一个关系
  • 如果有where,将where中的谓词应用到关系上
  • 如果有group by,满足where谓词的元组通过group by子句形成分组;没有group by,则满足where谓词的整个元组当作一个分组
  • 如果有having,将应用到每个分组上;不满足having谓词的将被抛弃
  • select利用剩下的分组产生查询结果元组,在每个分组上应用聚集函数来得到结果元组
找出2009年讲授的每个课程段,至少有2名学生选课的总学分平均值
select course_id,sec_id,semester,year,avg(tot_cred)
from takes natural join student
where year=2009
group by course_id,semester,year,sec_id
having count(ID) >= 2
列出每一年龄组中男生超过50人的人数
 select SAGE,count(SNO)
 from S
 where SEX = ‘男’
 group by SAGE
 having count(*) > 50

3.7.4 对空值和布尔值的聚集

??总而言之,SQL按以下原则处理空值:除了 count(*)外所有的聚集函数都忽略输入集合中的空值。由于空值被忽略,有可能造成参加函数运算的输入值集合为空集。规定空集的count运算值为0,其他所有聚集运算在输入为空集的情况下返回一个空值。

  • 空值
    • null参与聚集运算,除了**count(*)**以外的所有聚集函数都忽略输入中的空值
    • 对于聚集函数,若输入集合为空,count返回0,其他函数返回空值
  • 布尔值
    • 布尔数据类型:true、false、unknown
    • 聚集函数some和every分别计算布尔值的析取(or)和合取(and)

3.8 嵌套子查询

??SQL提供嵌套子查询机制。子查询是嵌套在另一个查询中的select-from-where表达式。子查询嵌套在where子句中,通常用于对集合的成员资格、集合的比较以及集合的基数进行检查。

3.8.1 集合成员资格 in和not in

??SQL允许测试元组在关系中的成员资格。连接词in测试元组是否是集合中的成员,集合是由select子句产生的一组值构成的。连接词not in则测试元组是否不是集合中的成员。

连接词in测试元组是否是集合中的成员
找出2017秋季和2018春季都开课的课程id
select distinct course_id
from section
where semester= 'Fall' and year=2017 and
		course_id in (select course_id
				      from section
                      where semester = 'Spring' and year=2018)
连接词not in测试元组是否不是集合中的成员
找出2017秋季开课,但2018春季不开课的课程id
select distinct course_id
from section
where semester= 'Fall' and year=2017 and
		course_id not in (select course_id
				      	  from section
                      	  where semester = 'Spring' and year=2018)
in/not in可以用于枚举集合
列出张军和王红同学的所有信息
select *
from S
where SNAME in ('张军','王红')
列出既不叫Mozart, 也不叫Einstein的教师姓名
select distinct name
from instructor
where name not in ('Mozart','Einstein')
任意关系中测试集合成员资格
找出选修了ID为10101教师的课的不同的学生总数
select count(distinct ID)
from takes
where (course_id,sec_id,semester,year) in (select course_id,sec_id,semester,year
                                           from teaches
                                           where teaches.ID=10101)

3.8.2 集合的比较

some(子查询)

短语“至少比某一个要大”在SQL中用> some表示。考虑下面的语句转换

  • 含义:“至少比某一个要大”
  • 形式:(<some,<=some,=,<>some,>=some, >some)
  • 特殊: =some 等价于in; 但<> some不等价于not in

在这里插入图片描述

找出工资至少比Biology系某一个教师的工资要高的老师姓名
select name
from instructor
where salary > some (select salary
                    from instructor
                    where dept_name='Biology')
all

结构>all对应于词组“比所有的都大”。

  • 含义:意思为“比所有的都大”
  • 形式: (<all ,<=all ,=all ,<>all ,>=all , >all )
  • 特殊:<>all 等价于 not in; 但=all不等价于in

在这里插入图片描述

找出平均成绩最高的学生号
select SNO
from SC
group by SNO
having avg(GRADE) >= all (select avg(GRADE)
				          from SC
     		              group by SNO)

3.8.3 空关系测试 exists

??SQL还有一个特性可测试一个子查询的结果中是否存在元组。exists结构在作为参数的子查询非空时返回true值。

在这里插入图片描述
??我们可以用not exists结构测试子查询结果集中是否不存在元组。not exists可以表示集合之间的包含关系。
在这里插入图片描述

3.8.4 重复元组存在性测试 unique

??SQL提供一个布尔函数,用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询结果中没有重复的元组,unique结构将返回true值。
在这里插入图片描述

3.8.5 from语句中的子查询

? SQL允许在from子句中使用子查询表达式。在此采用的主要观点是:任何select-from-where表达式返回的结果都是关系,因而可以被插入到另一个select-from-where中任何关系可以出现的位置。

在这里插入图片描述

3.8.6 with子句 定义临时关系

With子句提供定义临时关系的方法,这个定义只对包含with子句的查询有效。

查出所有工资总和大于所有系工资总额平均值的系
with dept_total (dept_name,value) as
    (select dept_name, sum(salary)
     from instructor
	 group by dept_name),
	 dept_total_avg(value) as
	(select avg(value)
     from dept_total)
select dept_name
from dept_total,dept_total_avg
where dept_total.value >= dept_total_avg.value

3.8.7 标量子查询

  • 只返回包含单个属性的单个元组的子查询。
  • 可出现在返回单个值的表达式能够出现 的任何地方
  • 可以出现在select、where和having子句中
  • 标量子查询中可以使用外层的关系
列出所有的系以及它们拥有的教师数
select dept_name, (select count(*)
                   from instructor
	               where department.dept_name =instructor.dept_name) 
	               as num_instructors
from department

3.9 数据库的修改

3.9.1 删除 delete

在数据库中,我们智能删除整个元组,而不能删除某些属性上的值。

在这里插入图片描述

3.9.2 插入 insert

? 要往关系中插入数据,我们可以指定待插入的元组,或者写一条查询语句来生成待插入的元组集合。显然,待插入元组的属性值必须在相应属性的域中。同样,待插入元组的分量数也必须是正确的。
在这里插入图片描述在这里插入图片描述

3.9.3 更新 update

??有些情况下,我们可能希望在不改变整个元组的情况下改变其部分属性的值。为达到这一目的, 可以使用update语句。与使用insert ,delete类似,待更新的元组可以用查询语句找到。
在这里插入图片描述

case

在这里插入图片描述
在这里插入图片描述

个例题

   /*关系表:
   person(driver_id,name,address)
   car(liscense_plate,model,year)
   accident(report_number,year,location)
   owns(driver_id,liscense_plate)
   participated(report_number,liscense_plate,driver_id,damage_amount)*/
   
   /*题目一:找出2017年出过交通事故的人员ID及其发生的事故次数*/
   select distinct driver_id as '车主ID' , count(license_plate) as '事故次数'
   from participated
   where report_number in (select report_number
                           from accident
                           where year = 2017)
   group by driver_id
   having report_number is not null
   /*题目二:删除ID为12345的人拥有的年份为2010的所有汽车*/
   delete from car
   where license_plate = (select license_plate
                          from owns
                          where driver_id = '12345')

3.10 总结

在这里插入图片描述

文章来源:https://blog.csdn.net/m0_63834988/article/details/135419490
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。