数据库系统概念(第6版) -- 第三章SQL复习总结
数据库系统概念(第6版) – 第三章SQL复习总结+作业解析 ---- 超好的学习sql的文章!!!
一些比较简单的就不再赘述了, 本文总结一些概念和书上的例子, 同时也会补充一些例子(包括作业题目), 希望对同样备考这门课程的同学有所帮助(顺便点个赞哟~~).
本文组织基本按照图书页码顺序,可对照参考,一些书上的例子及其修改也会给出.
查询语句
# select 子句用于列出查询结果中所需要的属性
# from 子句是一个查询求值中需要访问的关系列表
# where 子句是一个作用在from字句关系的属性上的谓词
上述是核心指导思想. 复杂的,嵌套的,都根据此.
可以简单阐明一下:
select A
from B
where C
上述B是一个关系(可以看做是表或者其一部分), C是一个谓语(条件) ,A是查询结果中所需要列出的属性(集合),
那么整个查询过程就是: 从关系B中查询满足条件C的子关系, 在根据select子句 投影所需要展示出结果的属性A
举个例子 :
select name
from instructor
where salary > 60000;
通俗来说: 上述sql查询就是从instructor 这张表中, 找出满足salary 大于 60000 的元组(记录), 并将他的name 显示出来.这样, 工资大于60000的老师姓名就是结果关系了
===下面开始正题
自然连接(p38 3.3.3 )
- 是一个作用于两个关系的运算, 产生一个关系作为结果.
- 它将第一个关系的每个元祖与第二个关系的所有元组进行连接; 但它只考虑那些在两个关系模式都出现的属性上取值相同的元组对.
eg1: 找出计算系中所有上了课的老师名称和课程标识
之前我们这么写
#希望找出Computer Science 系的教师名和课程标识
select name , course_id
from instructor,teaches
where instructor.ID = teaches.ID and instructor.dept_name = 'Comp. Sci.';
我们先不考虑只是计算机系, 任何系均可:
#希望找出所有上课的老师名称和课程标识
select name , course_id
from instructor,teaches
where instructor.ID = teaches.ID
上述即是将instructor关系和teaches关系做自然连接, 同时要求的是两个关系ID属性的值相等.
其实可以更简洁地写作:
select name, course_id
from instrutor natural join teaches;
(ps: 我们用到的关系包括instructor, teaches, course, department, 下面补充介绍一下关系结构,有书的同学可跳过)
instructor
ID varchar(5)
name varchar(20)
dept_name varchar(20)
salary decimal(8,2)
老师关系,其中ID是主键, dept_name作外键约束
department
dept_name varchar(20)
building varchar(15)
budget decimal(12,2)
老师所在系, 其中系名作为主键, 有所在building 和系预算
teaches
ID varchar(5)
course_id varchar(8)
sec_id varchar(8)
semester varchar(6)
year decimal(4,0)
老师授课关系, 包括有老师编号, 课程号等
course
course_id varchar(8)
title varchar(50)
dept_name varchar(20)
credits decimal(2,0)
课程, 包括有课程号, 课程名, 开课系,学分
下面我们继续…
eg2 我现在想知道所有上课老师的名称所在系的预算
我们可以分析, 所有上课老师的名称可以通过上面的instructor和 teaches 自然连接得到, 那么我现在此基础上再和department进行自然连接才能得到相应的老师所在洗预算
我们第一次自然连接,实际上是通过ID相等做的, 但是第二次得通过dept_name相等来进行.
事实上, SQL提供了一种自然连接构造形式, 允许用户来指定需要哪些列相等. 下面请看解答
select DISTINCT name, budget
from (instructor natural join teaches) join department using (dept_name);
表明instructor 先和 teaches进行自然连接, 其得到的关系结果在和department进行自然连接, 此次指定的是dept_name相等.
更名运算( 3.4.1 p40)
as 字句进行别名操作
old-name as new-name
为啥要用别名操作? 其中一个原因就是把长的关系名替换成短的, 还是" 查找所有授课老师的姓名和课程id" 例子:
select T.name , S.course_id
from instructor as T, teaches as S
where T.ID = S.ID;
我们先前在name和course_id上是省略了关系名的, 但有时可能两个关系有相同属性名, 不得不把关系名补全时, 这种方式优势就体现了.
另一个原因是适用于比较同个关系中的元组情况. 为此我们需要吧一个关系跟他自身进行笛卡尔积运算.
eg3 找出一些老师的姓名, 他们的工资至少比Biology系的某一个教师工资要高
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology'
在上面的查询语句中, T是我们需要找出的关系, S是我们被比较的, 两者虽然都是instructor关系,但是却是区分需要分开的.
where子句谓词(3.4.5 p42)
between … and …
用于说明是小于等于某个值, 同时又大雨等于某个值的.
集合运算(3.5 p43)
交: union 并: intersect 差: except
sql中的集合运算是作用在关系上的, 也就是说在多个查询结果(得到的结果是关系)中可用, 在from子句中也可用
eg4 找出在2009年秋季开课, 或者在2010春季开课或两个学期都开课的所有课程,
(select course_id
from section
where semester = 'Fall' and year = 2009)
union
(select course_id
from section
where semester = 'Spring' and year = 2010);
如果想保留所有重复,必须在 union 后面添加一个 all
eg5 找出在2009年秋季开课但不在2010年春季开课的所有课程
(select course_id
from section
where semester = 'Fall' and year = 2009)
except
(select course_id
from section
where semester = 'Spring' and year = 2010);
聚集函数 (3.7 p46)
聚集函数是以值的一个集合(集或多重集)为输入, 返回单个值的函数, sql 提供5个固有聚集函数:
- 平均值: avg
- 最小值: min
- 最大值: max
- 总和: sum
- 计数: count
我们通过查询, 或者说写在 select 后面的语句, 如果只有一个属性, 那么我们可以得到在 from 子句中的关系中, 满足where子句条件的 , 投影在 select 子句中, 但是可以对多个结果套上聚集函数加以运算
eg6 找出 Computer Science 系老师的平均工资
select avg(salary)
from instructor
where dept_name = 'Comp. Sci.';
这样在得到所有计算机系老师的工资后, 又通过avg函数得到了其平均工资
分组聚集
有时不仅希望将聚集函数作用在单个元组集上, 而且希望将其作用到一组元组集上, group by 子句中给出的一个或多个属性即可用来构造分组.
在group by 子句中的所有属性上取值相同的元组将被分在一个组中.
eg7 找出每个系的平均工资
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name;
group by 子句中的属性就是dept_name , 则满足dept_name相同的所有元组即为一组.
在使用group by 需要注意的一点就是, 在select中出现的属性, 要么被聚集函数聚集, 要么也出现在group by子句中(书上说得微绕, 我换了一种).
也就是说, 下面是错误的
select dept_name,ID, avg(salary)
from instructor
group by dept_name;
这也很好理解, 在展示结果时, 每一行是通过dept_name来进行分割的(也就是说不同的行有不同的dept_name) , 但并未保证其他属性相同啊, 这里明显一个系可能有多个老师(多个ID)啊~
having 子句
有时我们也想对group by 分组的结果进行一些条件限定, 比如之前我们用where 找出工资大于60000的老师, 现在我们想找出平均工资超过60000的系:
eg8 找出平均工资超过60000的系的名称
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg_salary > 60000;
这样使用having子句就行了 , 它和where类似, 也是提供谓词, 只是它只在形成分组后有效.
下面来看看当一个查询语句变复杂后(既有 group by 和 having)时操作定义:
- 与不带聚集的查询情况类似, 最先根据from子句来计算出一个关系.
- 如果出现了where子句, where子句中的谓词将应用到from子句的结果关系上.
- 如果出现了group by子句, 满足where谓词的元组通过group by子句形成分组.如果没有group by ,满足where谓词的整个元组集被当做一个分组.
- 如果出现having子句, 他将应用到每个分组上; 不满足having子句谓词的分组将被抛弃
- select利用剩下分组产生出查询结果的元组, 即在每个分组上应用聚集函数来得到单个结果元组.
eg9 对于在2009 年教授的每个课程段, 如果课程段有至少2名学生逃课, 找出选修课程段的所有学生的总学分(tot_cred)的平均值
select course_id, semester,year, sec_id,avg(tot_cred)
from takes natural join student
where year = 2009
group by course_id, semester, year, sec_id
having count(ID) >= 2;
===下面是比较复杂的
嵌套子查询(3.8 p48)
子查询是嵌套在另一个查询中的select-from-where 表达式.
- 嵌套在where中, 由于where子句本身是一个谓词, 嵌套后依然如此, 因此通常是对于集合的成员资格比较, 或者对集合基数进行检查. 换句话说, 嵌套在where里面的子语句得到的关系, 依然是外层用的作为条件的一部分.
- 嵌套在from中, 由于from 描述的是在某些关系中进行查询, 而select-from-where本身得到的结果就是关系, 那么很自然而然的可以将这个结果关系放到外层的from语句中.
集合成员资格
连接词 in 测试元组是否是集合中的成员, 集合是由select子句产生的一组值构成的, not in 则测试元组是否不是集合中的成员.
eg10 . 之前的例子, 2009 秋季和2010春季同时开课的所有课程. 可用where嵌套查询进行操作
select distinct course_id
from section
where semester = 'Fall' and year = 2009 and
course_id in (select course_id
from section
where semester = 'Spring' and year = 2010);
可以看到, 子查询先找出了满足开课在2010年春的课程id, 将其结果元组作为结果, 在外层查询的where语句中用 连接词 in 来测试.
外层查询在既满足2009秋开课课程id的同时 , 还得满足 in 连接的集合, 即2010秋开课的课程id.
eg11 找出(不同的)学生总数, 他们选择了ID为 10101的教师所教授的课程段
select count (distinct ID)
from takes
where (course_id , sec_id , semester , year) in (select course_id, sec_id , semester , year
from takes
where teaches.ID = 10101);
在sql中测试任意(多个)关系的成员资格也是可以的.
集合的比较
sql 提供了 至少比某一个大 , 可用 > some 表示.下面是用词方法嵌套查询的方式:
eg12 之前的例子, 找出所有工资比Biology系的某一教师高的所有教师姓名:
select name
from instructor
where salary > some (
select salary
from instructor
where dept_name = 'Biology'
);
子查询
select salary
from instructor
where dept_name = 'Biology'
产生Bio系的所有老师的工资的集合. 当外层查询元组的salary值至少比Bio系的某一老师salary高时, 外层where中 > some 比较为真.
eg13 找出所有工资比Biology系的任一教师高的所有教师姓名:
select name
from instructor
where salary > all (
select salary
from instructor
where dept_name = 'Biology'
);
现在进行一些稍稍复杂的一些查询:
eg14 找出平均工资最高的系的名称
select dept_name
from instructor
group by dept_name
having avg(salary) >= all (
select avg(salary)
from instructor
group by dept_name
);
空关系测试
sql还可测试一个子查询的结果是否存在元组. exist 结构在作为参数的子查询非空时返回true.
eg15. 还是那个例子 找出2009年秋季学期和2010学期同时开课的所有课程
select course_id
from section as S
where semester = 'Fall' and year = 2009 and exists(
select * from section as T
where semester = 'Spring' and year = 2010 and S.course_id = T.course_id);
上述查询还说明了一个sql特性，来自外层查询的一个相关名称（上例中的S）可以用在where子句的子查询中。
使用来自外层查询相关名称的子查询被称作 相关子查询 。
我们可以用not exists 结构测试子查询结果集中是否不存在元组。我们可以用not exist 结构模拟集合包含操作：
关系A包含关系B ： not exists （B except A）
eg16 找出选修了BioLogy系开设的所有课程的学生的id和姓名， 使用except结构
select S.ID, S.name
from student as S
where not exists ((select course_id
from course
where dept_name = 'Biology')
except
(select T, course_id
where takes as T
where S.ID = T.ID));
首先我们来看看为啥关系A包含关系B用sql可以是：not exists （B except A）
exist()结构是当括号里面为非空时返回一个true的，即当存在时返回true，即not exist() 是当其括号里面不存在时返回true.
不存在某集合是B与A的差集,说明集合A比集合B大,集合A包含集合B.
上述sql中,except后面的子查询找出所有学生选的所有课程, 这是A. except前面的是选出生物系开设的所有课程的ID.
重复元组存在性测试( 3.8.4 p52)
sql提供一个布尔函数，用于测试在一个子查询结果中是否存在重复元组。如果作为参数的子查询结构中没有重复元组，unique结构返回true
eg17 找出所有在2009年开设最多一次的课程
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id = R.course_id and
R.year = 2009);
包含在unique结构体中的结果就只能是1条或者0条，即找出刚好在2009的最多开一次的。
当然，也可以用not unique来找出重复的选项，not unique返回true当且仅当找到了重复的。。
比如我们想找所有在2009年至少开设两次的课程就可以把eg17的unique前面加个not。
where子句中的嵌套子查询小结（精华）
首先我们要明白where后面接的是谓词，也就是说是执行查询的”筛选条件“，满足where语句为真的才会被选出，这是指导思想。
我们知道了多个谓词之间可以用and or等进行逻辑操作。
where嵌套子句的使用主要是几个方面的使用
-
集合成员资格–就是说判断子查询得到的结果作为一个集合用于判断 – in，这时用的结构体是
select … from … where 属性A in(子查询B )
表示当外层查询的属性A在子查询B找得到结果时（包含于B），满足条件选出。
-
集合的比较–某些属性（集合）和子查询得到的属性（集合）进行比较 . > some 大于某一个，<all 比任一地小
select … from … where 属性A < all (子查询B)
表示外层的属性A小于任何一个内层查询的结果集。
-
空关系测试 – 就是说子查询的是存在或者不存在时，where语句才会为true或false – exists
select … from … where exists (子查询B)
当子查询B有结果时，where语句为真，被外层查询选出
-
重复元组存在性测试 – 就是说子查询结果有重复时，where语句才会为true或false – unique
select … from … where unique (子查询B)
当子查询B的结果集无重复时，where语句整个为真，被外层查询选出。
这里面还提到个概念 – 相关子查询。 指的是外层的一个关系取个别名，他可以是放到内层的where子查询中使用的。
from子句中的子查询
本身在select-from-where结构中，from后面接的就是关系，通俗说就是从哪些“表”中去筛选。同时，查询的结果本身也是个关系，那么自然而然地就可以在from语句中嵌套子语句。
eg18 找出系平均工资超过42000的那些系中教师的平均工资
select dept_name. ava_salary
from (select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name)
where avg_salary > 42000;
上述例子中，from语句后面跟了个子查询，该子查询产生的结果关系是什么呢？是根据分组的系名和该系的平均工资，顺便把平均工资起了个别名。外层紧接着对这个产生的关系进行where筛选，筛选出平均工资大于42000的。
eg19 找出所有系中工资总额最大的系
select max(tot_salary)
from (select dept_name , sum(salary)
from instructor
group by dept_name) as dept_tatal(dept_name, tot_salary);
还是先看from子句中，它首先根据dept_name分组，得到每个系的总工资，注意这里它将from嵌套的子查询结构作为一个新关系dept_name暂存起来，他有两个属性，一个是dept_name,另一个是tot_salary，而它正好也是sum(salary)的别名。然后外层使用时，直接对tot_salary求取最大值，也就相当于求平均值中的最大的那一个。
with 子句
with 子句提供临时定义关系的方法，这个定义只对包含with子句的查询有效。什么意思？就是说有时我们在select中嵌套一个select可能会没那么好的可读性，我们可以先用with给一个查询的结果关系起个别名，然后再带进去我们之前的外层查询，请看例子：
eg20 找出具有最大预算的系
whit max_budget(value) as
(select max(budget)
from department)
select budget
from department,budget = max_budget.value;
第三章后面的就不说了。。下面来看看一些课后习题
===下面是课后习题部分
3.8 考虑图中的银行数据库，其中加下划线的是主码。为这个关系数据库构造处如下sql查询
branch( branch_name , branch_city , assets )
customer( customer_name, customer_street, customer_city )
loan( loan_number, branch_name,amount )
borrower(customer_name , loan_number)
account(account_number, branch_name , balance)
depositor(customer_name , account_number)
a. 找出有账户但无贷款的客户。
【答】可以看到，若一个客户有账户，则会在depositor中有记录，若他有贷款，则在loan中有记录
(select customer_name
from depositor)
except
(select customer_name
from loan)
b. 找出与“smith”住在同一个城市，同一个街道的所有客户的名字。
【答】
with smith_city_street(customer_city, customers_street) as(
select customer_city, customer_street
from customer
where customer_name = 'smith')
select customer_name
from customer
where customer_city = smith_city_street.customer_city
and
customer_street = smith_city_street.scustomers_street;
c. 找出所有支行名称，这些支行中都有居住在“Har”的客户所开设的账户。
【答】
select branch_name
from (account natural join depositor ) join account using account_number
where customer in(
select customer_name
from customer nature join depositor
where customer_city = 'Har'
);
呃。。貌似上面的麻烦了
select branch_name
from (account natural join depositor ) join customer using customer_name
where customer_city = 'Har';
3.9 考虑下图雇员数据库，其中下划线是主码。为下面每个查询写出sql表达式：
a.
select empolyee_name,city
from empolyee natural join works
where company_name = 'First Bank';
b.
select *
from employee natural join works
where company_name = 'First Bank' and salary > 10000;
c.
select employee_name
from works
where company <> 'First Bank';
d.
select employee_name
from works
where salary > all (select salary
from works
where company_name = 'small bank');
e.
先写出子查询： 找出small bank所在城市
select city
from company as C2
where C2.company_name = 'small bank';
根据这些城市，来找位于这些城市的所有公司
select C1.company_name
from company as C1
where C1.company_name in (select city
from company as C2
where C2.company_name = 'small bank')
and C1.company_name = C2.company_name ;
另一种写法是用包含关系：A包含B ==> not exists (B except A)
select C1.company_name
from company as C1
where not exists(
(select city
from company
where company_name = 'small bank')
except
(select city
from company as C2
where C1.company_name = C2.company_name)
)
f. 找出雇员最多的公司
雇员最多，使用sum按照公司名进行分组，再聚集
先找出每个公司的雇员个数
select company_name , count(employee_name)
from works
group by company_name;
此时得到的关系是公司名称和该公司的雇员人数，现在再找出最大的即可。可以使用with
with com_num(company_name , number) as (
select company_name , count(employee_name)
from works
group by company_name
)
select max(com_num.number)
from com_num;
定义了一个包含有公司名称和该公司雇员人数的关系，再使用聚集函数sum找出这个关系中人数最大的那条记录。
e.找出平均工资高于“fist bank”平均工资的那些公司
先来个查询，把每个公司的平均工资搞出来再说。
select company_name, avg(salary)
from works
group by company_name;
得到一个公司名称和该公司平均工资的关系。下面在该关系中找出比frst bank平均工资高的公司名称即可。
当然，还需要一个嵌套查询找出fisrt bank的平均工资
with com_avg(company_name , com_avg) as
(select company_name, avg(salary)
from works
group by company_name)
select C1.company_name ,C1.com_avg
from com_avg as C1
where C1.com_avg > (
select C2.com_avg
from com_avg as C2
where C2.company_name = 'first bank'
);
补充： 为“ first bank” 所有工资超过100000的经理工资增长3%
update works as T
set T.salary = T.salary * 1.03
where T.employee_name in(
select manager_name
from manages
) and T.salary > 100000
and T.company_name = 'first bank';
至此，第三章较复杂部分已全部介绍完毕，有关sql的其他dml操作可类比。希望对学数据库的同学有所帮助哦~
上一篇： Java继承