数据库系统原理(四) SQL查询
文章目录
SQL查询的基本结构
SQL查询的基本结构由3个子句构成:select,from,where
- SELECT A1,A2,…,An
- From r1,r2,…,rm
- WHERE P
上述查询语句等价于关系代数表达式:
∏
A
1
,
A
2
,
.
.
.
,
A
n
(
σ
p
(
r
1
×
r
2
×
.
.
.
.
.
×
r
m
)
)
\prod_{A_1,A_2,...,A_n}{(\sigma_{p}(r_1\times r_2\times .....\times r_m))}
A1,A2,...,An∏(σp(r1×r2×.....×rm))
查询的输入是在from子句中列出的关系,在这些关系中进行where和select子句中指定的运算,然后产生一个关系作为结果。
select子句
- 例:找出所有的教师的名字
select name
from instructor;
表示成关系代数表达式:
∏
n
a
m
e
i
n
s
t
r
u
c
t
o
r
\prod_{name}{instructor}
name∏instructor
-
注意:SQL不允许在属性名称中使用字符“-”,例如,使用dept_name代替dept-name
-
SQL不区分字母的大小写,因此可以使用大写字母或小写字母命名表,属性等。
-
SQL允许在关系以及SQL表达式结果中出现重复的元组(代数表达式中不允许)
-
如果要强行去除重复,可在select后加入关键词distinct
例,查询instructor关系中所有系名,并去除重复
select distinct dept_name from instructor;
-
-
SQL也允许使用关键词all来显示知名不去除重复(SQL默认就是all)
-
星号 “*****” 在select语句中,可以用来表示“所有的属性”
-
例,
select * from instructor;
-
-
select子句还可带含有+,-,*,/运算符的算数表达式,运算对象可以是常数或元组的属性
- 例
select ID,name,salary*1.05 from instructor
where子句
-
where子句允许我们只选出那些在from子句的结果关系中满足特定谓词的元组
- 例,找出所有在Computer Science系并且工资超过70000美元的教师的姓名
select name from instructor where dept_name = C.S and salary>70000 ;
-
上述SQL查询语句,对应的关系代数表达式为:
∏ n a m e ( σ d e p t n a m e = ′ C . S ′ ∧ s a l a r y > 70000 ( i n s t r u c t o r ) ) \prod_{name}{(\sigma_{dept_name = 'C.S'\wedge salary>70000}{(instructor)})} name∏(σdeptname=′C.S′∧salary>70000(instructor))
-
SQL允许where子句在使用逻辑连词and,or和not,也可以使用between指定范围查询。逻辑连词的运算对象可以是包含比较运算符<,<=,>,>=和<>的表达式
例: 找出工资在90 000美元和100 000美元之间的教师的姓名
select name from instructor where salary<=100000 and salary >= 90000;
或者:
select name from instructor where salary between 90000 and 100000;
from子句
-
from子句是一个查询求值中需要访问的关系列表,通过from子句定义了一个在该子句中所列出关系上的笛卡尔积
- 例,找出关系instructor和teaches的笛卡尔积
select * from instructor,teacher;
-
一般为了去除表笛卡尔积中无用的元组,还应该对其进行限制
select name,course_id
from instructor,teaches
where instuctor.ID = teacher.ID and instructor.name = 'C.S';
-- 要求同名属性的值相同,去除无用元组
更名运算
-
SQL提供可为关系和属性重新命名的机制,即使用as子句:
old_name as new_name
as子句既可以出现在select子句中,也可以出现在from子句中
-
例,将属性name重命名为instructor_name
select name as instructor_name from instructor,teaches where instructor.ID = teaches.ID ;
-
使用更名运算,对关系重命名
- 例,找出所有教师,以及他们所讲授课程的标识
select T.name,S.course_id from instructor as T,teaches as S where T.ID =S.ID; -- 为了引用简洁
- 例,找出所有教师名,他们的工资至少比Biology系某一个教师的工资要高
select distinct T.name from instructor as T,instructor as S where T.salary > S.salary and S.dept_name = 'Biology' -- 为了区分
字符串运算
- 对字符串进行的最通常的操作是操作符 like 的模式匹配,使用两个特殊的字符来描述模式:
- 百分号 (%):匹配任意子串
- 下划线 (_): 匹配任意一个字符
- 例,找出所在建筑名称中包含子串"Watson"的所有系名
select dept_name
from department
where building like '%Watson%';
-- 前后可以匹配任意字符
-
为使模式中能够包含特殊字符(即%和_),SQL允许定义转义字符。我们在like比较运算中使用escape关键词来定义转义字符
- 例,使用反斜线(\)作为转义字符
like "ab\%cd%" escape '\' --匹配所有以“ab%cd”开头的字符串 like 'ab\\cd' escape '\' --匹配所有以"ab\cd"开头的字符串
-
SQL还允许在字符串上有多种函数,例如串联("||"),提取子串,计算字符串长度,大小写转换(用upper(s)将字符串s转换为大写或用lower(s)将字符串s转换为小写),去掉字符串后面的空格(使用trim(s))
排列元组的显示此许
-
SQL为用户提供了一些对关系中元组显示次序的空值。order by子句就可以让查询结果中元组按排列顺序显示
- 例,按字母顺序列出在Physics系的所有教师
select name from instructor where dept_name='physics' order by name;
-
order by子句默认使用升序。要说明排序顺序,我们可以用desc表示降序,或者用asc表示升序
- 例,按salary的降序列出整个instructor关系,如果有几位教师的工资相同,就将他们按姓名升序排列
select * from instructor order by salary desc,name asc; -- 先按收入降序排列,收入相等按姓名字母升序排列
重复(不是很理解)
- 在关系模式的形式化数学定义中,关系是一个集合。因此,重复的元组不会出现在关系中。但在实践中,包含重复元组的关系是有用的
- 可以用关系运算符多重集版本(Multiset version)来定义SQL查询的复本定义,在此定义几个关系代数运算符的多重集版本,一直多重集关系r1和r2
- 选择 σ(r1):如果在r1中有元组t1的c1个复本,而且t1满足选择σ,那么有c1个t1的复本出现在结果中。
- 投影 ∏a(r):如果r1中t1的每个复本,在∏a(r1)中都有一个∏a(t1)的复本与其对应,其中∏a(t1)表示单个元组t1的投影
- 交 r1×r2:如果有c1个t1的复本在r1中且有c2个t2的复本在r2中,那么有c1*c2个t1·t2元组的复本在 r1×r2中
集合运算
- SQL作用在关系union,intersect和except运算对应于数学集合论中的∪,∩,-运算
- uion,intersect和except运算与select子句不同,他们会自动去除重复
- 如果想保留所有重复,必须使用union all,intersect all 和except all
- 假设一个元组在关系r中重复出现了m此,在关系s中重复出现了n次,那么这个元组重复出现
- 在 r union all s中,重复出现m+n次
- 在r intersect all s中,重复出现 min(m,n)次
- 在r except all s中,重复出现max(0,m-n)次
例1,找出在2009秋季开课,或者在2010年春季开课或两个学期都开课的所有课程
(select course_id
from section
where semester = 'Fall' and year = 2009)
union
(select couse_id
from section
where semester = 'Spring' and year = 2010
);
空值
-
SQL允许使用null表示属性缺失。我们在谓词中可以使用特殊的关键词null测试空值,也可以使用is not null测试非空值
- 例,找出instructor关系中元组在属性salary上取空值的教师名
select name from instructor where salary is null; --不能使用salary=null,因为null = null结果不一定
-
控制的存在给聚集运算的处理也带来了麻烦。聚集函数根据以下原则处理空值。
- 除了count(*)外所有的聚集函数都忽略集合中的空值
- 规定:空值的count运算符值位0,其他所有聚集运算在输入为空集的情况下返回一个空值
聚集函数
-
聚集函数是以值的一个集合(集或多重集)为输入,返回单个值的函数。SQL提供了五个固有聚集函数:
- 平均值:avg
- 最小值:min
- 最大值:max
- 总和:sum
- 计数:count
- 其中,sum和avg的输入必须是数字集,但其他运算符还可作用在非数字数据类型的集合上,如字符串
-
分组聚集(group by):group by子句中给出的一个或多个属性是用来构造分组的,在group by子句中给出的一个或多个属性是用来构造分组的,在group by子句中的所有属性上取值相同的元组将被分为一个组中
-
having子句类似于where子句,但其是对分组限定条件,而不是对元组限定条件。having子句中的谓词在形成分组后才起作用,因此可以使用聚集函数。
例1,找出Computer Science系教师的平均工资
select avg(salary) as avg_salary --平均后没有相应的属性名,要重命名
from instructor
where dept_name = 'C.S';
上述SQL查询等价于关系代数表达式:
g
a
v
g
(
s
a
l
a
r
y
)
(
σ
d
e
p
t
n
a
m
e
=
′
C
.
S
′
(
i
n
s
t
r
u
c
t
o
r
)
)
g_{avg(salary)}{(\sigma_{dept_name='C.S'}{(instructor)})}
gavg(salary)(σdeptname=′C.S′(instructor))
例2,找出每个系的平均工资
select avg(salary) as avg_salary
from instructor
group by dept_name;
注意:任何没有出现在group by子句中的属性,如果出现在select子句中的话,他只能出现在聚集函数的内部。
例3,找出教师平均工资超过42000美元的系
select avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;
注意:同上
嵌套子查询
SQL提供嵌套子查询机制。子查询是嵌套在另一个查询中的select-from-where表达式。子查询嵌套在where子句中,通常用于对集合的成员资格,集合的比较以及集合基数进行检查。主要用于:
- 集合成员资格
- 集合的比较
- 空关系测试
- 重复元组存在性测试
- from子句的子查询
- with子句
集合成员资格
-
SQL允许测试元组在关系中的成员资格。连接词 in 测试元组是否是集合中的成员,集合是由select子句产生的一组值构成,对应的还有 not in
- 例1,找出在2009年秋季和2010年春季学期同时开课的所有课程
select distinct course_id from section where semester ='spring' and year = 2009 and course_id in (select course_id from section where semester = 'autumn' and year = 2010);
- 例2,找出(不同的)学生总数,他们选修了ID为10101的教师所讲授的课程
select count(distinct stu_id) from take where (course_id,sec_id,semester,year) in (select course_id,sec_id,semester,year from teach where tea_id =10101);
集合的比较
-
考虑“查询满足下面条件的的所有教师的姓名,他们的工资至少比Biology系某一教师的工资要高”,在前面,我们将此查询写作:
select name from instructor as T,instructor as S where T.salary>S.salary and S.dept_name = 'Biology';
但是SQL提供了另一种方式书写上面的查询。短语“至少比某一个要大”在SQL中用 >some 来表示,则此查询还可写作
select name from instructor where salary >some (select salary from instructor where dept_nmae = 'Biology');
-
***some***子句:代表元组中的某一个值。(=some)≡ in,但是(≠some)!≡ not in
-
考虑查询“找出满足下面条件的所有教师的姓名,他们比Biology系每个教师的工资都高”,在SQL中,结构***>all***对应于词组“比所有都大”,则
select name from instructor where salary >all (select salary from instructor where dept_name = 'Biology');
-
***all***子句:代表元组中的每一个值。(≠all)≡ not in 但是(=all)!≡ in
例,找出平均工资最高的系
select dept_name from instructor group by dept_name having avg(salary) >all (select avg(salary) from instructor group by dept_name);
空关系测试**(不是很理解)**
-
SQL还有一个特性可测试一个子查询的结果中是否存在元组。***exists***结构在作为参数的子查询为空时返回true值
- exists r <=> r ≠ 空集
- not exists r <=> r = 空集
-
我们还以使用***not exists*** 结构模拟集合包含(即超集)操作“关系A包含关系B”写成“not exists(B except A)”
-
例,找出在2009年秋季学期和2010年春季学期同时开课的所有课程,使用exists结构
select course_id from section as S where semester = 'autumn' and year = 2009 and exists (select course_id from section as T where semester = 'spring' and year = 2010 and S.course_id = T.course_id); --由于不能线性执行,该语句的时间开销比较大
-
例,找出选修了Biology系开设的所有课程的学生,使用except结构**(不是很理解)**
select stu_id from student as S where not exists ((select course_id from course where dept_name="Biology") except (select T.course_id from takes as T where S.ID= T.ID)); --难度较大
重复元组存在性测试
-
SQL提供了一个布尔函数,用于测试在一个子查询的结果中是否存在重复元组。如果作为参数的子查询的结果中没有重复的元组***unique***结构将返回true值
-
例1,找出所有在2009年最多开设一次的课程
select course_id from course as T where unique (select course_id from course as S T.couse_id = S.course_id and S.year = 2009 );
注意:也可以将上述语句中的***unique***换成***1>=***
-
例2,找出所有在2009年最少开设两次的课程
select course_id from course as T where not unique (select course_id from course as S T.couse_id = S.course_id and S.year = 2009 );
-
-
unqiue,not unique 在oracle8,sql sever7中不支持
from子句中子查询
-
SQL允许在from子句中使用子查询表达式。任何select-from-where表达式返回的结果都是关系,因而可以被插入到另一个select-from-where中任何关系可以出现的位置
-
例,找出系平均工资超出42000美元的那些系的教师的平均工资。
在前面的的聚集函数中,我们用了having 写此查询,现在用***from***子句中使用子查询来重写
select dept_name,avg_salary from (select dept_name,avg(salary) from instructor group by dept_name )as dept_avg(dept_name,avg_salary) --必须用as生成临时视图 where avg_salary>42000;
-
例,找出所有系中工资总额最大的系
在此,***having***语句无能为力。但我们可以使用***from***的子查询写出:
select max(dept_salary) from (select dept_name,sum(salary) from instructor group by dept_name )as dept_sum(dept_name,dept_salary);
-
with子句(不是很理解)
**with子句提供定义临时关系的方法,这个定义只对包含***with***子句的查询有效
-
例,找出具有最大预算值的系
with max_budget(value) as --定义临时关系 (select max(budget) from departement) select budget --使用临时关系 from department,max_budeget where departmen.budget = max_budget.value;
-
例,找出工资总额大于平均值的系**(不是很理解)**
with dept_total(dept_name,dept_sum) as--每个系的工资总和 (select dept_name, sum(salary) from instructor group by dept_name) with dept_avg(avg_sum) as --所系的平均工资 (select avg(dept_sum) from dept_total) select dept_name from dept_total,dept_avg --ppt中是from dept_total A,dept_avg where dept_total>=dept_avg; -- where A.value>=B.value
数据库的修改
删除
-
删除请求的表达与查询非常类似。我们只能删除整个元组,而不能只删除某些属性上的值。SQL用以下语句表示删除:
delete from r where p; -- p代表一个谓词,r代表一个关系
-
例1,从instructor关系中删除与Finance系教师相关的所有元组
delete from instructor where dept_name = 'Finance';
-
例2,从instructor关系中删除所有这样的教师元组,他们在位于Wastion大楼的系工作
delete from instructor where dept_name in (select dept_name from department where building = 'Watson')
-
例3,删除工资低于大学平均工资的教师记录
delete from instructor where salary < (select avg(salary) from instructor);
问题:当我们从instructor关系中删除元组时,平均工资就会改变
SQL的解决方案:
首先,计算出平均工资,并找出要删除的所有元组
然后,删除上述找到的所有元组(不重新计算平均工资,也不重新测试元组是否符合删除条件)
在同一SQL语句内,除非外层查询的元组变量引入内层查询,否则内层查询只进行一次。
-
插入
-
SQL允许使用***insert***语句,向关系中插入元组,形式如下:
insert int r [(c1,c2,...)] values(e1,e2,...) insert into r[(c1,c2,...)] select e1,e2,...from...;
-
例1,假设我们要插入的信息时Computer Science系开设的名为"Database System"的课程CS-437,他有4个学分
insert into course values ("CS-437","Database System",'C.S',4); --或者 insert into course(course_id,title,dept_name,credits)--指定属性 values ("CS-437","Database System",'C.S',4); --未知属性的值可以设为null
-
假设我们想让Music系每个修满144学分的学生称为Music系的教师,其工资为18000美元
insert into instructor select ID,name,dept_name,18000 --插入搜索到的值 from student where dept_name='Music' and tot_cred >144;
-
更新
-
SQL允许使用***update***语句,在不改变整个元组的情况下改变其部分属性的值,形式如下:
update r set <c1=e1,[c2=e2,...]> [where <condition>];
-
例1,假设给工资超过100000美元的教师涨3%的工资其余教师涨5%,可以写:
update instructor set salary = salary*1.03 where salary>100000; update instructor set salary = salary*1.05 where salary<=100000; --两条语句顺序十分重要,不能调换
-
-
SQL提供***case***结构,避免更新次序引发的问题,形式如下
case when pred1 then result1 when pred2 then result2 ... when predn then resultn else result0 end
因此上例查询可重新写为
update instrutor set salary = case when salary<=100000 then salary*10.5 else salary*1.03 end