SQL
- 3.2数据定义
- 基本类型
- 基本模式定义
- create table
- 添加
- insert into
- 添加属性
- 删除
- delete
- drop table
- 去掉属性
- 3.3SQL查询的基本结构
- 单关系查询
- select基本结构
- select 详细结构
- 自然连接
- natural join
- join...using
- 3.4附加的基本运算
- 更名运算
- 字符串运算
- like模式匹配
- escape定义转义字符
- ```'^ [0-9]+$'```纯数字字符串查找
- select 子句中的属性说明
- 排序元组的显示次序
- where子句谓词
- 3.5集合运算
- 并运算 ``union``
- 交运算 ``intersect (all)``
- 差运算``except (all)``
- 3.6空值
- 测试空值
- 3.7聚集函数/分组函数
- 基本聚集
- 分组聚集``group by``
- ``having``子句
- 对空值和布尔值的聚集
- 空值
- 布尔值
- 嵌套子查询
- 集合成员资格
- 集合的比较
- 比较运算符``some``
- 比较运算符``all``
- 空关系测试
- ```exists```
- ```not exists```
- 重复元组存在性测试
- from子句中的子查询
- with子句
- 标量子查询
- 3.9数据库的修改
- 删除
- 插入
- 更新
- ``update``
- ``case结构``
3.2数据定义
基本类型
基本模式定义
create table
定义SQL关系
create table r
(A1 D1,
A2 D2,
<完整性约束>,);
完整性约束:
- primary key:声明主码,非空且唯一
- foreign key references s:声明外码
- check:详见第四章check语句
- not null : 属性不允许空值
示例:大学生数据库部分SQL数据定义
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key (dept_name));
create table course
(course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0),
primary key (course_id),
foreign key (dept_name) references department);
create table instructor
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department);
create table section
(course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),
primary key (course_id,sec_id,semester,year),
foreign key (course_id) references course);
create table teaches
(ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
primary key (ID, course_id,sec_id, semester,year),
foreign key (course_id , sec_id,semester,year) references section,
foreign key (ID) references instructor);
添加
insert into
插入数据
值应遵循对应属性在关系模式中列出的顺序
insert into instructor
values(10211,'Smith','Biology',66000);
添加属性
alter table r add A D;
删除
delete
删除所有元组
delete from student;
drop table
去掉关系,删除的是关于被去掉关系的所有信息
去掉属性
alter table r drop A;
3.3SQL查询的基本结构
单关系查询
select基本结构
select 属性名1,属性名2,...,属性名n
from 关系名1,关系名2,...,关系名n
<where P>
-
select子句用于列出查询结果中所需要的属性
-
from子句是在查询求值中需要访问的关系列表
-
where子句是作用在from子句中的关系属性上的谓词
- where⼦句⽤于过滤记录,即缩小访问数据的范围。
- where后跟⼀个返回 true 或 false 的条件。
-
P是一个谓词,可以理解为条件表达式
select 详细结构
select <all|distinct> 目标列表达式1,目标列表达式2,...
from 关系名1,关系名2,...
<where 行条件表达式>
<group by 属性名序列 <having 组条件表达式>>
<limit a,b>
<order by 属性名 <ASC|DESC>>
- 目标列表达式包括:属性名,算术表达式,聚集函数,* ( * 表示“所有的属性”)
- 读取from子句的表或视图做笛卡尔积,from子句列出查询需要访问的关系列表
- where子句找出满足条件表达式的元组
- 比较运算符:<,<=,>,>=,= ,<>,!
- 逻辑运算符:and,or,not
- 按group by子句指定列名分组,值相等的元组为一组,每个组产生结果表中的一条记录。可在每组中用聚集函数。如果group by子句带having短语,则只有满足指定条件的组才予输出
- 按select子句中给出的列名或表达式求值输出
- 若要去掉重复元组,可用关键字distinct指明
- 为保留重复元组,可用关键字all显式指明
- 第⼀个参数a为起始行,从 0 开始;
- 第⼆个参数b为返回的总行数,即返回第a+1~a+b行
- 按order子句列名的值升或降序,
- asc :升序(默认)
- desc:降序
自然连接
natural join
- 定义:作用于两个关系,并产生一个关系作为结果。
- 只考虑在两个关系模式上都出现的属性上取值相同的元组对
- 属性出现的顺序:
- 先是两个关系模式中的共同属性(只出现一次)
- 只出现在第一个关系模式中的属性
- 只出现在第二个关系模式中的属性
select A1,A2,...An
from r1 natural join r2 natural join...
where P;
例:列出教师的名字以及他们所讲授课程的名字
select name,title
from instructor natural join teaches,course
where teaches.course_id=course.course_id
解释:
- 先计算instructor和teaches的自然连接,再计算该结果个course的笛卡尔积
- where从这个结果中取出:来自连接结果的课程标识与来自course关系的课程标识相匹配
select name,title
from instructor natural join teaches natural join course
会计算出错误结果:
- instructor和teaches的自然连接包括属性(ID,name,dept_name,salary,course_id,sec_id)
- course包含的属性(course_id,title,dept_name,credits)
- 二者自然连接的结果,需要既在depy_name上取值相同,又在course_id上取值相同
- 因此结果只输出将是所讲授的课程是他所在系的课程的(教师姓名,课程名称)结果对
join…using
select 属性名1,属性名2,...,属性名n
from 关系名1 join 关系名2 using (属性名1,属性名2,...属性名n)
- 按指定属性连接,不考虑未指定的共同属性
- 只考虑关系名2与关系名1在属性名1…属性名n上的匹配,其他的共有属性不考虑
- 这里的关系名可以是自然连接后的关系,即
from(关系名1 join 关系名2)join 关系名3 using (属性名1,属性名2,...属性名n)
3.4附加的基本运算
更名运算
old-name as new-name
- 可以出现在select子句中,也可以在from子句中
- 可用于重命名关系
- 用于需要比较同一个关系中的元组的情况
#重命名关系
select T.name,S.course_id
from instructor as T,teaches as S
where T.ID=S.ID;
#比较同一个关系中的元组
select distinct T.name
from instructor as T, instructor as S
where T.salary>S.salary and S.dept_name='Biology';
#T\S 被称为表别名/相关名称/相关变量/元组变量
字符串运算
- 单引号标示字符串,两个单引号字符来区分字符串中的单引号
- 字符串相等运算大小写敏感
- 函数
- 串联
||
- 提取字串
- 计算字符串长度
- 大小写转换:
upper(s) \ lower(s)
- 去掉字符串后的空格
trim(s)
- 串联
like模式匹配
大小写敏感
- 百分号(%):匹配任意字串
- 下划线(_):匹配任意一个字符
举例说明:
Intro%
匹配任何以“Intro”开头的字符串
%comp%
匹配任何包含“comp”的字符串
___%
匹配至少包含三个字符的字符串
___
匹配只包含三个字符的字符串
select 属性名
from 关系名
where 属性名 <not> like 字符串模式
escape定义转义字符
转义字符放在特殊字符前面,表示该特殊字符被当成普通字符
like 'ab\%cd%' escape '\'
匹配所有以ab%cd开头的字符串
like 'ab\\cd%' escape '\'
匹配所有以ab\cd开头的字符串
'^ [0-9]+$'
纯数字字符串查找
示例:检查字符串上是否全为数字
select 属性名
from 关系名
where 属性名 like (属性名,'^[0-9]+$')
select 子句中的属性说明
*
表示所有属性
形如
select*
的 select字句表示from子句结果关系的所有属性都被选中
select instructor.*
from instructor,teaches
where instructor.ID=teaches.ID;
# 表示instructor 中的所有属性都被选中
排序元组的显示次序
可以按多个列进行排序,并且为每个列指定不同的排序方式,默认为升序
select*
from instructor
order by salary desc,name asc;
where子句谓词
between
表示大于等于条件1并且小于等于条件2的条件
not between
表示既不大于等于条件1也不小于等于条件2的条件
格式:where 属性 <not> between 条件1 and 条件2...and 条件n
示例:列出Biology系教课的教师名和课程标识
select name,course_id
from instructor,teaches
where instructor.ID=teaches.ID and dept_name='Biology';
可写成:
select name,course_id
from instructor,teaches
where(instructor.ID, dept_name)=(teaches.ID,'Biology');
3.5集合运算
并运算 union
- 基本规则:
- 所有查询的列数和列顺序必须相同
- 每个查询中涉及的表的列的数据类型必须相同或兼容
- 通常返回的列名取自第一个查询
- 默认去除相同行,只保留一行。使用union all保留相同行。
- 只能包含一个order by 子句,必须位于语句的最后
- 将查询之后的行放在一起(垂直放置)
找出2009年秋季开课或2010春季开课的课程或者两个学期都开设的课程
(select course_id
from section
where semester='Fall' and year=2009)
union
(select course_id
from section
where semester='Sping' and year=2010);
交运算 intersect (all)
找出2009年秋季和2010春季都开设的课程
(select course_id
from section
where semester='Fall' and year=2009)
intersect
(select course_id
from section
where semester='Sping' and year=2010);
差运算except (all)
从第一个输入中输出所有不出现在第二个输出中的元组。
找出2009年秋季开课但2010春季不开设的课程
(select course_id
from section
where semester='Fall' and year=2009)
except
(select course_id
from section
where semester='Sping' and year=2010);
3.6空值
- 将涉及空值的任何比较运算的的结果视为unknown
- 算数运算中:任一输入为空,则算数表达式的结果为空。
- and:
- true and unknown = unknown
- false and unknown = false
- unknown and unknown = unknown
- or:
- true or unknown = true
- false or unknown = unknown
- unknown or unknown = unknown
- not:
- not unknown = unknown
- 可用
is (not) unknown
测试一个表达式的结果是否为unknown
测试空值
is (not) null
- 若谓词
is not null
所作用的值非空,那么为真。
找出instructor关系中salary为空值的所有教师
select name
from instructor
where salary is null;
- 如果元组在所有属性上取值相同或都为空值,就被当作相同组
3.7聚集函数/分组函数
- 以值的一个集合作为输入,返回单个值的函数。
- 五个固有聚集函数:
- 平均值:avg(数字集)
- 最小值:min
- 最大值:max
- 总和:sum(数字集)
- 计数:count
count(具体字段)
:表示统计该字段下所有不为null的元素总数count(*)
:统计表当中总行数
- 必须先分组才能使用,默认整张表是一组,因此不能直接使用在where后面
- 分组函数自动忽略null
基本聚集
格式:
select 函数符号(<distinct> 目标列表达式) <as 新属性名>
from 关系名
<where P>
分组聚集group by
- 在group by 子句中的所有属性上取值相同的元组将被分到一个组中
- 出现在select子句中但没有被聚集的属性只能是出现在group by子句中的那些属性:就是在select语句中,如果有group by语句的话,select后面只能跟:参加分组的字段,以及分组函数
- 先执行group by 再执行select
找出每个系在2010年春季学期讲授一门课程的教师人数
分析:由于有关每位教师在每个学期讲授每个课程段的信息在teaches关系中,但是这些信息要与来自instructor关系的信息进行连接,才能得到每位教师所在系名
select dept_name,count(distinct ID) as instr_count
from instructor natural join teaches
where semester ='Sping' and year=2010
group by dept_name;
/*错误查询*/
select dept_name,ID,avg(salary)
from instructor
group by dept_name;
//在一个特定分组(dept_name)中的每位教师都有一个不同的ID,由于每个分组只输出一个元组,无法确定选哪个作为ID输出。
having
子句
- having子句中的谓词在形成分组后才起作用
- 任何出现在having子句中,但没有被聚集的属性必须出现在group by子句中
- having和where的区别
- where执行在group by之前,针对原始的数据筛选
- having只能用在group by之后,针对分组后的内容筛选
- where后的条件表达式里不允许使用聚集函数,而having可以。
- 代码逻辑
- 最先根据from子句计算出一个关系
- 如果有where,将where中的谓词应用到关系上
- 如果有group by,满足where谓词的元组通过group by子句形成分组;
- 没有group by,则满足where谓词的整个元组当作一个分组
- 如果有having,将应用到每个分组上;不满足having谓词的将被抛弃
- select利用剩下的分组产生查询结果元组,在每个分组上应用聚集函数来得到结果元组
对空值和布尔值的聚集
空值
- null参与聚集运算,除了
count(*)
以外的所有聚集函数都忽略输入中的空值 - 对于聚集函数,若输入集合为空,count返回0,其他函数返回空值
布尔值
- 布尔数据类型:true、false、unknown
- 聚集函数
some
和every
分别计算布尔值的析取(or)和合取(and)
嵌套子查询
子查询是嵌套在另一个查询中的select-from-where表达式,通过将子查询嵌套在where子句中,可以实现对集合成员资格的测试、对集合的比较以及对集合基数的确定
集合成员资格
in/(not in)
测试元组是否是/(不是)集合中的成员in/not 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);
列出既不叫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);
集合的比较
比较运算符some
- 至少比某一个要大:
>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
不等价于in; 但<> all
等价于not in
空关系测试
exists
- 在作为参数的子查询非空时,返回true
列出2009秋季和2010同时开课的所有课程id
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)
not exists
- 测试子查询结果集中是否不包含元组
- 用于模拟集合包含(超集)的逻辑测试
- 若关系A包含关系B(A为B的超集),则
not exists (B except A)
为 true - 对于B except A,可以表达:在B中存在,但在A中不存在的记录;
- 若关系A包含关系B(A为B的超集),则
如果B是A的子集,则B except A结果为空集,则not exists (B except A)为true.
找出选修了Biology系开设的所有课程的学生
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
from takes as T
where S.ID=T.ID)
重复元组存在性测试
unique
:- 作为参数的子查询中没有重复的元组,返回true
找出所有在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);
//另一种等价表达方式
select T.course_id
from course as T
where 1>=(
select count(R.course_id)
from section as R
where T.course_id=R.course_id and R.year=2009);
not unique
:
找出所有在2009年最少开设两次的课程
select T.course_id
from coourse as T
where not unique(
select R.course_id
from section as R
where T.course_id=R.course_id and R.year=2009);
from子句中的子查询
(课本P54,55,详细例子解释)
with子句
- with子句提供定义临时关系的方法,此定义只对包含with子句的查询有效
标量子查询
- 只返回包含单个属性的单个元组的子查询
- 可出现在返回单个值的表达式能够出现 的任何地方
3.9数据库的修改
删除
- 元组的删除:
delete from 关系名 <where 条件表达式>
- 清空表中的数据:truncate 关系名
删除instructor中Finance系的教工元组
delete from instructor
where dept_name = 'Finance'
删除instructor中,所在系位于Watson大楼的教工元组
delete from instructor
where dept_name in (select dept_name
from department
where building = 'Watson')
插入
- 插入一条:
insert into 关系名 <(属性名[,属性名]...)> values (值[,值]...)
- 插入若干:
insert into 关系名 <(属性名[,属性名]...)> ()子查询
让Music系每个修满144学分的学生成为Music系的教师,其工资为18000美元
insert into instructor
select ID,name,dept_name,18000
from student
where dept_name='Music' and tot_cred>144;
#即在查询结果的基础上插入元组
更新
update
- 格式:
update 关系名 set 属性名 = 表达式/子查询 <,属性名= 表达式/子查询...> <where 条件表达式>
- 规则:首先检查关系中的所有元组,看是否应该被更新,然后执行
- update中语句的顺序重要
给工资超过100000美元的教师涨3%的工资,其余教师涨5%
若改变两条语句的顺序,就变成了工资略少于000美元的教师涨8%的工资
update instructor
set salary=salary*1.03
where salary>100000;
update instructor
set salary=salary*1.05
where salary<100000;
case结构
- 在一条update语句中执行多种更新,避免更新次序引发的问题
- 结构:
当i是第一个满足的 p r e d i pred_i predi时,此操作返回 r e s u l t i result_i resulti
若没有满足的,返回 r e s u l t 0 result_0 result0
case
when pred1 then result1
when pred2 then result2
...
when predn then resultn
else result0
end
- 标量子查询在 SQL更新语句中也非常有用,它们可以用在 set子句中。
我们把每个 student 元组的 tot_cred 属性值设为该生成功学完的课程学分的总和。我们假设如果一个学生在某门课程上的成绩既不是’F’,也不是空,那么他成功学完了这门课程
#使用set子句中的子查询
update student S
set tot_cred =(
select sum(credits)
from takes natural join course
where S. ID = takes. ID and
takes. grade <> ’F’and
takes. grade is not null);
注意子查询使用了来自update语句中的相关变量S。
如果一个学生没有成功学完任何课程,上述更新语句将把其tot_cred
属性值设为空。
如果想把这样的属性值设为0的话,我们可以使用另一条 update语句来把空值替换为0。
更好的方案是把上述子查询中的“select sum(credits)”子句替换为如下使用case 表达式的 select 子句:
select case
when sum(credits) is not null then sum(credits)
else 0
end