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

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

【数据库系统概念】第三章 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 总结