SQL(Structured Query Language,结构化查询语言)
2.1 SQL查询语言概览
SQL语言包含
- 数据定义语言(Data-Definition Language,DDL)。SQL DDL提供定义关系模式、删除关系以及修改关系模式的命令。
- 数据操纵语言(Data-Manipulation Language,DML)。SQL DML提供从数据库中查询信息以及在数据库中插入元组、删除元组、修改元组的能力
- 完整性(interity)：SQL DDL包含定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束。破坏完整性约束的更新是不允许的。
- 视图定义(view definition):SQL DDL包含定义视图的命令
- 事务控制(transaction control)。SQL包含定义事务的开始点和结束点的命令。
- 嵌入式SQL(embedded SQL)和动态SQL(dynamic SQL)：嵌入式和动态SQL定义SQL语句如何嵌入诸如C、C++和Java这样的通用编程语言中。
- 授权(authorization):SQL DDL包括定义对关系和视图的访问权限的命令。
2.2 SQL数据定义
2.2.1 基本类型
- char(n)：具有用户指定长度n的固定长度的
字符串，也可以使用全称形式character - varchar(n)：最大长度为n的可变长度的
字符串 - int:整数
- smallint：小整数(依赖于机器的整数类型的子集)
- numeric(p,d)：具有用户指定精度的定点数。这个数字有p位数(算上一个符号位)，并且小数点右边有p位中的d位数字
- real，double precision：浮点数与双精度浮点数，精度依赖于机器
- float(n)：精度至少为n位数字的浮点数
- null value：
2.2.2 基本模式定义
使用create table来定义SQL关系： c r e a t e t a b l e r ( A 1 D 1 , A 2 D 2 , ⋯ , A n D n , < 完整性约束 1 > , ⋯ ) create\ table\ r(A_1D_1,A_2D_2,\cdots,A_nD_n,<完整性约束1>,\cdots) create table r(A1D1,A2D2,⋯,AnDn,<完整性约束1>,⋯)
r是关系的名字，A表示关系中的一个属性名，D表示这个关系域的值是什么类型
E.g.:
create table branch
(branch_name char(15) not null,
branch_city char(30),
assets int)
完整性约束:
- primary key(
A
j
1
,
A
j
2
,
⋯
,
A
j
m
A_{j1},A_{j2},\cdots,A_{jm}
Aj1,Aj2,⋯,Ajm):主码声明表示这些属性构成关系的主码。主码属性必须是
非空且唯一的;也就是说没有元组会在主码属性上取空值，并且关系中也没有两个元组会在所有主码属性上取值都相同，一般都加上主码声明 - foreign key( A k 1 , A k 2 , ⋯ , A k n A_{k1},A_{k2},\cdots,A_{kn} Ak1,Ak2,⋯,Akn)references s:外码声明表示关系中任意元组在这些属性上的取值必须对应于关系 s s s中某元组在主码属性上的取值
- not null:一个属性上的非空约束表明该属性上不允许存在空值。
- chece§:P是一个谓词，即判断条件
E.g:
create table instructor
(ID varchar(5),
name varchar(20)not null,
dept_name varchar(20),
salary numeric(8,2),
primary key(ID),
check(salary>=0))
基本操作：
1.新建的表是空的，使用insert into r values 来插入元素：
insert into instructor values('10211','Smith','Computer Science',66000)
如果违背了完整性约束，将会插入失败
2.delete:删除表中的所有元组(关系不会被删除)
delete from instructor
3.alter table：为已有关系增加属性
alter table r add A D;
r是现有关系的名称，A是待添加属性的名称，D是待添加属性的类型，我们也可以通过命令
alter table r drop A;
从关系中去掉属性。很多数据库并不支持去掉属性，尽管它们允许去掉整张表。
2.3 SQL查询的基本结构
2.3.1 单关系查询
找出所有教师的姓名：
select name
from instructor
其结果是由属性名为 n a m e name name的单个属性构成的关系
可以在 s e l e c t select select后插入关键字 d i s t i n c t distinct distinct去除重复，也可使用 a l l all all显式知名不去除重复(默认是不去重的)
select distinct dept_name
from instructor
s e l e c t select select语句还可以带 + 、 − 、 ∗ 、 / +、-、*、/ +、−、∗、/运算符的算术表达式，运算对象可以是常数或元组的属性
select ID,name,dept_name,salary*1.1
from instructor
w h e r e where where子句允许我们只选出那些在 f r o m from from子句的结果关系中满足特定谓词的元组，例：找出计算机系中工资超过70 000美元的老师姓名
select name
from instuctor
where dept_name='Comp.Sci.' and salary > 70000
SQL允许在 w h e r e where where子句中使用逻辑连词and、or和not，逻辑连词的对象可以是包含比较运算符<,<=,>,>=,=和<>的表达式，也允许使用比较运算符来比较字符串、 算术表达式以及特殊类型，比如日期
2.3.2 多关系查询
作为示例，如果我们想查询“所有老师的姓名，以及他们所在系得名称和系所在额建筑的名称”，系所在建筑的名称在的department关系的building属性给出。则需要instructor的元组和department的元组匹配，使得department元组在department原则在dept_name上的取值相配于instructor元组在dept_name上的取值。
SQL为：
select name,instructor.dept_name,building
from instructor,department
where instructor.dept_name=department.dept_name;
注意到，dept_name属性既出现在instructor中也出现在department关系中，用关系名作前缀来注明所指的是哪个属性。同时要求二者的dept_name相同保证选取的元组是合理的。
基本查询语句形式：
$$
\begin{align}
select\ &A_1,A_2,\cdots,A_n\
from\ &r_1,r_2,\cdots,r_m\
where\ &P
\end{align}
$$
from子句其实就是将这些关系的元组一一连接起来，不做任何处理
natural join
即自然连接
select name, course_id
from instructor natural join teaches;
//1
select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;
//2
select name,title
from instructor natural join teaches natural join course;
//3
select name,title
from (instructor natural join teaches join course using(course_id))
//4
查询1是容易的，就是将两个表自然连接后(ID相同的才会拼在一起)，选出名字和课程代码。
查询2，3，4是相似的，但我们可以发现查询3是错的，因为instructor和teaches自然连接后，和course有了两个相同的属性：dept_name、course_id。在后续和course的自然连接中，只有者两个属性都相同才会拼接在一起，但实际上，计算机系的老师也可能在生物系开课，所以这个查询时不合实际情况的，故为错误的。
查询2是容易理解的，就是将自然连接后的新表再和course做笛卡尔积，要求course_id相等才会加入最终表
注意到查询4出现了一个新的词组join…using(x)，意思为用x属性做自然连接，如查询4中，便是只要求course_id相同即可。
2.4 附加的基本运算
SQL还支持几种附加的基本运算
2.4.1 更名运算
SQL提供了一种重命名结果关系中的属性的方式，使用形如
a
s
as
as的子句：
o
l
d
−
n
a
m
e
a
s
n
e
w
−
n
a
m
e
old-name\ \ as\ \ new-name
old−name as new−name
as子句既可以出现在select子句中，也可出现在form子句中。
as子句可以把一个长的关系名替换成短的，这样再查询中的其他地方使用起来就更为方便。
另一个作用是为了适用于需要比较同一个关系中的元组的情况。为此我们需要把一个关系跟它自身进行笛卡尔积，如果不充命名，就不可能把一个元组与其他元组区分开来。例如：找到所有工资至少比Biology系某一位老师的工资要高的老师。
select distinct T.name
from instructor as T,instructor as S
where T.salary>S.salary and S.dept_name='Biology'
通过as语句，可以实现同一个表之间的比较。
像例中的T和S那样被用来重命名关系的标识再SQL标准中被称作相关名称，但通常也被称作表别名或相关变量，或元组变量
2.4.2 字符串运算
SQL使用一对单引号来标识字符串，例如’Computer’。如果单引号是字符串的组成部分，就使用两个单引号表示，例如’It’‘s right’，就是字符串"It’s right"。
SQL允许在字符串上作用多种函数，例如连接字符串(使用"||")、提取子串、计算字符串长度、大小写转换(用upper(s)函数转换为大写，用lower(s)函数转换为小写)、去掉字符串后面的空格(使用trim(s))等。
在字符串上可以使用like来实现模式匹配：
- 百分号(%)：%字符匹配任意字串
- 下划线(_)：_字符匹配任意一个字符
模式是大小写敏感的，也就是说，大写字符与小写字符不匹配，反之亦然，例：
- 'Intro%'匹配以“Intro”打头的任意字符串
- '%Comp%'匹配包含"Comp"子串的任意字符串
- '_ _ _%'匹配至少有3个字符的任意字符串
考虑查询"找出所在建筑名称中包含子串’Watson’的所有系名"
select dept_name
from department
where building like '%Watson%'
此外，SQL还允许定义转义字符,用escape关键字来定义转义字符
- like ‘ab\%cd%’ escape ‘\’ 匹配以"ab%cd"开头的所有字符串
- like ‘ab\cd%’ escape ‘\’ 匹配以"ab\cd"开头的所有字符串
同时可使用not like来搜索不匹配项。
2.4.3 select子句中的属性说明
星号"*"可以在select子句中表示“所有的属性”。
2.4.4 排列元组的显示次序
order by子句可以让查询结果中的元组按照排列顺序显示，用desc表示降序，或用asc表示升序。
select *
from instructor
order by salary desc,name asc;
按salary降序排列，如果salary相同，就按name升序排列。
2.4.5 where子句谓词
SQL 提供between比较运算符来说明一个值小于或等于某个值，同时大于或等于另一个值
例如：找到贷款额在100000和90000之间的贷款号码
select loan_number
from loan
where amount between 90000 and 100000
类似地，可以使用not between比较运算符
SQL允许我们用符号 ( v 1 , v 2 , ⋯ , v n ) (v_1,v_2,\cdots,v_n) (v1,v2,⋯,vn)来包含一个n维元组，该符号被称为行构造器。在元组上可以使用比较运算符，并将字典顺序进行比较运算。例如 a 1 ≤ b 1 ， a_1\le b_1， a1≤b1，且 a 2 ≤ b 2 a_2\le b_2 a2≤b2时， ( a 1 , a 2 ) ≤ ( b 1 , b 2 ) (a_1,a_2)\le(b_1,b_2) (a1,a2)≤(b1,b2)为真。
select name,course_id
from instructor,teaches
where (instructor.ID,dept_name)=(teaches.ID,'Biology');
2.5 集合运算
SQL作用在关系上地union、intersect和except运算对应于数学集合论中地 ∪ 、 ∩ \cup、\cap ∪、∩和 − - −运算。集合运算默认是没有重复元素，可在关键字后加all来保留重复元素。
如果一个元组在r中出现m次，s中出现n次，那么
- m+n 次 在 r union all s
- min(m,n) 次 在 r intersect all s
- max(0,m-n)次 在r except all s
例子：
-
Find all customers who have a loan, an account, or both
(select customer_name from depositor) union all (select customer_name from borrower) -
Find all customers who have both a loan and an account.
(select customer_name from depositor) intersect all (select customer_name from borrower) -
Find all customers who have an account but no loan.
(select customer_name from depositor) except all (select customer_name from borrower)
2.6 空值
任何含null的比较远算的结果都是unknown(既不是谓词is null也不是is not null)
逻辑运算：
- 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
如果where子句谓词对一个元组计算出false或unknown,就不能加入结果中
可使用null来查找空值：
select name
from instructor
where salary is null
也可使用unknown来测试一个比较运算是否为unknown：
select name
from instructor
where salary >10000 is unknown
值得注意的是，在谓词"null= null"会返回unknown，但在select distinct 中，对于元组{(‘A’,null),(‘A’,null)}，则认为这两份拷贝是相同的，这两种情况对null的处理不同
2.7 聚集函数
聚集函数(aggregate function)是以值集(集合或多重集合)为输入并返回单个值得函数，SQL提供了5个标准得固有聚集函数。
- 平均值：avg
- 最小值：min
- 最大值：max
- 总和：sum
- 计数：count
sum和avg得输入必须是数字集，其他运算符可以作用在非数字数据类型得集合上，比如字符串
2.7.1 基本聚集
例如：计算计算机系教师的平均工资：
select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp.Sci.';
可以使用as子句给计算的属性取个有意义的名字，计算平均值时要保留重复项，不然显然是错的。
当然也可以在其他的聚集函数中去重，例如：找出在2018年春季学期授课的教师总数，显然，无论教几门课，教师都应该只被计算一次。
select count(distinct ID)
from teaches
where semester='Spring' and year='2018';
此外，SQL不允许在使用count(*)时使用distinct。在max和min时使用distinct时合法的，尽管结果没有区别。同样，我们也可以显式地写出all来表示要保留重复项
2.7.2 分组聚集
可以使用group by子句将聚集函数作用在某一组元组集上。group by子句中给出一个或多个属性用来构造分组。在分组子句中的所有属性上取值相同的元组将被分在一个组内。
例如：找出每个系的平均工资
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name;
稍微复杂一点的例子：找出每个系在2018年春季学期授课的教师人数。由于授课信息在teaches关系中，所以要和instructor连接一下。
select dept_name,count(distinct instructor.ID) as instr_count
from instructor ,teaches
where instructor.ID = teaches.ID and
semester='Spring' and year='2018'
group by dept_name
注意：使用分组时，确保出现在select语句中但没有在聚集函数中的属性，只能是出现在group by子句中的那些属性，否则是错误查询，例如：
/*错误查询*/
select dept_name,ID,avg(salary)
from instructor
group by dept_name；
2.7.3 having 子句
having子句是用来筛选分好的组，例如，我们可能只对平均工资大于42000美元的系感兴趣，这个约束条件并不针对单个元组，而是针对group by 构成的每个分组。
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000
类似的，任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中
另一个例子：找到至少有三个账户且居住在Harrison的客户的平均存款
select depositor.customer_name,avg(balance)
from depositor,account,customer
where depositor.account_number=account.account_number and
depositor.account_number=customer.account_number and
customer_city ='Harrison'
group by depositor.customer_name
having count(distinct depositor.account_number)>=3
2.7.4 对空值和布尔值的聚集
按照以下规则处理空值：除了count(*)之外所有的聚集函数都忽略输入集合中的空值，并规定空集的count运算值为0，并且当作用在空集上时，其他所有聚集运算返回一个空值。
布尔数据类型：true、false、unknown,聚集函数some和every可应用于布尔值的集合，并分别计算这些值的析取(or)和合取(and)
2.8 嵌套子查询
通过将子查询嵌套在where子句中，可以用子查询来执行对集合成员资格的测试、对集合的比较以及对集合基数的去欸的确定
2.8.1 集合成员资格
SQL允许测试元组在关系中的成员资格。连接词in测试集合成员资格，这里的集合是由select子句产生的一组值构成的。连接词not in测试集合成员资格的缺失。
例如：找出所有在2017年秋季学期开课但不在2018年春季学期开课的课程，可写为
select distinct course_id
from section
where semster='Fall' and year=2017 and
course_id not in(select course_id
from section
where semester='Spring' and year=2018)
找出选修了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')
2.8.2 集合比较
some表示某一，all表示全部，可以使用>,<,>=,<=,=,<>(不等于)
例如：找出工资至少比Biology系某位教师的工资要高的所有教师的姓名
select name
from instructor
where salary > some(select salary
from instructor
where dept_name='Biology')
找出平均工资最高的系
select dept_name
from instructor
group by dept_name
having avg(salary) >= all(select avg(salary)
from instructor
group by dept_name )
2.8.3 空关系测试
SQL可测试一个子查询的结果中是否存在元组。exists结构在作为参数的子查询非空时返回true
e x i s t s r ⇔ r ≠ ∅ exists\ r\ \Leftrightarrow\ r\ne\varnothing exists r ⇔ r=∅
n o t e x i s t s r ⇔ r = ∅ not\ exists\ r\Leftrightarrow r=\varnothing not exists r⇔r=∅
例如：找出在2017秋季学期和2018春季学期都开课的所有课程
select course_id
from section as S
where semester='Fall' and year=2017 and
exists(select *
from section as T
where semester='Spring' and year=2018 and
S.course_id=T.course_id);
我们知道 n o t e x i s t s ( X − Y ) ⇔ X − Y = ∅ ⇔ X ⊆ Y not\ exists(X-Y)\Leftrightarrow X-Y=\varnothing \Leftrightarrow X\subseteq Y not exists(X−Y)⇔X−Y=∅⇔X⊆Y
对于查询：找出选修了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))
/*找到学生S.ID选修的所有课*/
那么我们就可以说“关系A包含关系B”等价于“not exists(B except A)”
2.8.4 重复元组存在性测试
unique、not unique用于测试子查询结果中是否存在重复元组
查询：找出在2017年最多开设一次的所有课程
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=2017);
2.9 数据库的修改
主要是增加、删除或修改信息
2.9.1 删除
只能删除整个元组，而不能只删除某些属性上的值
d
e
l
e
t
e
f
r
o
m
r
w
h
e
r
e
P
;
\begin{align*} &delete\ from \ r\\ &where\ P; \end{align*}
delete from rwhere P;
其中
P
P
P代表一个为此，r代表一个关系，delete语句首先从r中找出使
P
(
t
)
P(t)
P(t)为真的所有元组
t
t
t,然后把它们从
r
r
r中删除，where子句可以省略，在省略的情况下
r
r
r中的所有元组都将被删除出。
一条delete命令只能作用于一个关系，如果我们想从多个关系中删除元组，必须为每个关系使用一条delete命令
2.9.2 插入
形如
insert into course(course_id,title,dept_name,credits)
values('CS-437','Darabase Systems','Comp.Sci.',4)
r后面的属性值可随意交换，注意对应好values中值的顺序就行。也可不写，则按默认的属性顺序插入
大多数情况适合select语句使用的
例如，我们想让Music系每个修满144学时的学生称为Music系的教师，工资为18000美元
insert into instructor
select ID,name,dept_name,18000
from student
where dept_name='Music' and tot_cred>144;
2.9.3 更新
形如
u
p
d
a
t
e
r
s
e
t
E
x
p
r
e
s
s
i
o
n
s
w
h
e
r
e
P
;
\begin{align*} &update\ r\\ &set\ Expressions\\ &where\ P; \end{align*}
update rset Expressionswhere P;
其中where子句也可以包含select语句中的where子句的任何合法结构(包括嵌套的select)
例如：给工资低于平均值的教师涨5%的工资
update instructor
set salary=salary*1.05
where salary <(select avg(salary)
from instructor);
此外，SQL提供case结构，可利用单条update语句进行多种情况的更新，以避免更新次序引发的问题，形如：
u
p
d
a
t
e
r
s
e
t
A
=
c
a
s
e
w
h
e
n
p
r
e
d
1
t
h
e
n
r
e
s
u
l
t
1
w
h
e
n
p
r
e
d
2
t
h
e
n
r
e
s
u
l
t
2
⋯
w
h
e
n
p
r
e
d
n
t
h
e
n
r
e
s
u
l
t
n
e
l
s
e
r
e
s
u
l
t
0
e
n
d
\begin{align*} &update\ r\\ &set\ A = case\\ &\ \ when\ pred_1 \ then\ result_1\\ &\ \ when\ pred_2 \ then\ result_2\\ &\ \ \cdots\\ &\ \ when\ pred_n \ then\ result_n\\ &\ \ else\ result_0\\ &end \end{align*}
update rset A=case when pred1 then result1 when pred2 then result2 ⋯ when predn then resultn else result0end
例如
update instructor
set salary = case
when salary <=100000 then salary *1.05
else salary*1.03
end