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关系：$createtabler(A_1{D}_1,A_2{D}_2,\cdots ,A_n{D}_n,<完整性约束1>,\cdots )$

r是关系的名字，A表示关系中的一个属性名，D表示这个关系域的值是什么类型

E.g.:

create table branch
(branch_name char(15) not null,branch_city char(30),assets		 int)

完整性约束:

• primary key($A_{j1},A_{j2},\cdots ,A_{jm}$):主码声明表示这些属性构成关系的主码。主码属性必须是非空且唯一的;也就是说没有元组会在主码属性上取空值，并且关系中也没有两个元组会在所有主码属性上取值都相同，一般都加上主码声明
• foreign key($A_{k1},A_{k2},\cdots ,A_{kn}$)references 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 namefrom   instructor

其结果是由属性名为$name$的单个属性构成的关系

可以在$select$后插入关键字$distinct$去除重复，也可使用$all$显式知名不去除重复(默认是不去重的)

	select distinct dept_namefrom instructor

$select$语句还可以带$+、-、\ast 、/$运算符的算术表达式，运算对象可以是常数或元组的属性

	select ID,name,dept_name,salary*1.1from instructor

$where$子句允许我们只选出那些在$from$子句的结果关系中满足特定谓词的元组，例：找出计算机系中工资超过70 000美元的老师姓名

	select namefrom instuctorwhere dept_name='Comp.Sci.' and salary > 70000

SQL允许在$where$子句中使用逻辑连词and、or和not，逻辑连词的对象可以是包含比较运算符<,<=,>,>=,=和<>的表达式，也允许使用比较运算符来比较字符串、 算术表达式以及特殊类型，比如日期

2.3.2 多关系查询

​ 作为示例，如果我们想查询“所有老师的姓名，以及他们所在系得名称和系所在额建筑的名称”，系所在建筑的名称在的department关系的building属性给出。则需要instructor的元组和department的元组匹配，使得department元组在department原则在dept_name上的取值相配于instructor元组在dept_name上的取值。

​ SQL为：

	select name,instructor.dept_name,buildingfrom instructor,departmentwhere 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}
$$

• select 用于列出查询结果中所需的元素

• from 是在查询求值中需要访问的关系列表

• where子句是作用在from子句中的关系的属性上的谓词

from子句其实就是将这些关系的元组一一连接起来，不做任何处理

natural join

即自然连接

图1 关系表

    select name, course_idfrom instructor natural join teaches;//1select name, titlefrom instructor natural join teaches, coursewhere teaches.course_id = course.course_id;//2select name,titlefrom instructor natural join teaches natural join course;//3select name,titlefrom (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提供了一种重命名结果关系中的属性的方式，使用形如$as$的子句：
$$old-nameasnew-name$$
as子句既可以出现在select子句中，也可出现在form子句中。

as子句可以把一个长的关系名替换成短的，这样再查询中的其他地方使用起来就更为方便。

另一个作用是为了适用于需要比较同一个关系中的元组的情况。为此我们需要把一个关系跟它自身进行笛卡尔积，如果不充命名，就不可能把一个元组与其他元组区分开来。例如：找到所有工资至少比Biology系某一位老师的工资要高的老师。

	select distinct T.namefrom instructor as T,instructor as Swhere 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_namefrom departmentwhere 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 instructororder by salary desc,name asc;

按salary降序排列，如果salary相同，就按name升序排列。

2.4.5 where子句谓词

SQL 提供between比较运算符来说明一个值小于或等于某个值，同时大于或等于另一个值

例如：找到贷款额在100000和90000之间的贷款号码

	select loan_numberfrom loanwhere amount between 90000 and 100000

类似地，可以使用not between比较运算符

SQL允许我们用符号$(v_1,v_2,\cdots ,v_n)$来包含一个n维元组，该符号被称为行构造器。在元组上可以使用比较运算符，并将字典顺序进行比较运算。例如$a_1\le b_1，$且$a_2\le b_2$时，$(a_1,a_2)\le (b_1,b_2)$为真。

	select name,course_idfrom instructor,teacheswhere (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 namefrom instructorwhere salary is null

也可使用unknown来测试一个比较运算是否为unknown：

	select namefrom instructorwhere 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_salaryfrom instructorwhere dept_name = 'Comp.Sci.';

可以使用as子句给计算的属性取个有意义的名字，计算平均值时要保留重复项，不然显然是错的。

当然也可以在其他的聚集函数中去重，例如：找出在2018年春季学期授课的教师总数，显然，无论教几门课，教师都应该只被计算一次。

	select count(distinct ID)from teacheswhere 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_salaryfrom instructorgroup by dept_name;

稍微复杂一点的例子：找出每个系在2018年春季学期授课的教师人数。由于授课信息在teaches关系中，所以要和instructor连接一下。

	select dept_name,count(distinct instructor.ID) as instr_countfrom instructor ,teacheswhere instructor.ID = teaches.ID andsemester='Spring' and year='2018'group by dept_name

注意：使用分组时，确保出现在select语句中但没有在聚集函数中的属性，只能是出现在group by子句中的那些属性，否则是错误查询，例如：

	/*错误查询*/select dept_name,ID,avg(salary)from instructorgroup by dept_name；

2.7.3 having 子句

having子句是用来筛选分好的组，例如，我们可能只对平均工资大于42000美元的系感兴趣，这个约束条件并不针对单个元组，而是针对group by 构成的每个分组。

	select dept_name,avg(salary) as avg_salaryfrom instructorgroup by dept_namehaving avg(salary) > 42000

类似的，任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中

另一个例子：找到至少有三个账户且居住在Harrison的客户的平均存款

	select depositor.customer_name,avg(balance)from depositor,account,customerwhere depositor.account_number=account.account_number anddepositor.account_number=customer.account_number andcustomer_city ='Harrison'group by depositor.customer_namehaving 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_idfrom sectionwhere semster='Fall' and year=2017 andcourse_id not in(select course_idfrom sectionwhere semester='Spring' and year=2018)

找出选修了ID为10101的教师所讲授的课程段的(不同)学生的总数：

	select count(distinct ID)from takeswhere (course_id,sec_id,semester,year) in(select course_id,sec_id,semester,yearfrom teacheswhere teaches.ID='10101')

2.8.2 集合比较

some表示某一，all表示全部，可以使用>,<,>=,<=,=,<>(不等于)

例如：找出工资至少比Biology系某位教师的工资要高的所有教师的姓名

	select namefrom instructorwhere salary > some(select salaryfrom  instructorwhere dept_name='Biology')

找出平均工资最高的系

	select dept_namefrom instructorgroup by dept_namehaving avg(salary) >= all(select avg(salary)from instructorgroup by dept_name )

2.8.3 空关系测试

SQL可测试一个子查询的结果中是否存在元组。exists结构在作为参数的子查询非空时返回true

$existsr\iff r\ne \varnothing$

$notexistsr\iff r=\varnothing$

例如：找出在2017秋季学期和2018春季学期都开课的所有课程

	select course_idfrom section as Swhere semester='Fall' and year=2017 andexists(select *from section as Twhere semester='Spring' and year=2018 andS.course_id=T.course_id);

我们知道$notexists(X-Y)\iff X-Y=\varnothing \iff X\subseteq Y$

对于查询：找出选修了Biology系开设的所有课程的所有学生。(即生物系的课是学生的课的子集)

	select S.ID,S.namefrom student as Swhere not exists((select course_idfrom coursewhere dept_name='Biology')/*找出生物系的所有课*/except(select T.course_idfrom takes as Twhere S.ID=T.ID))/*找到学生S.ID选修的所有课*/

那么我们就可以说“关系A包含关系B”等价于“not exists(B except A)”

2.8.4 重复元组存在性测试

unique、not unique用于测试子查询结果中是否存在重复元组

查询：找出在2017年最多开设一次的所有课程

	select T.course_idfrom course as Twhere unique (select R.course_idfrom section as Rwhere T.course_id=R.course_id andR.year=2017);

2.9 数据库的修改

主要是增加、删除或修改信息

2.9.1 删除

只能删除整个元组，而不能只删除某些属性上的值
$$\begin{array}{rl}& deletefromr\\ & whereP;\end{array}$$
其中$P$代表一个为此，r代表一个关系，delete语句首先从r中找出使$P(t)$为真的所有元组$t$,然后把它们从$r$中删除，where子句可以省略，在省略的情况下$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 instructorselect ID,name,dept_name,18000from studentwhere dept_name='Music' and tot_cred>144;

2.9.3 更新

形如
$$\begin{array}{rl}& updater\\ & setExpressions\\ & whereP;\end{array}$$
其中where子句也可以包含select语句中的where子句的任何合法结构(包括嵌套的select)

例如：给工资低于平均值的教师涨5%的工资

	update instructorset salary=salary*1.05where salary <(select avg(salary)from instructor);

此外，SQL提供case结构，可利用单条update语句进行多种情况的更新，以避免更新次序引发的问题，形如：
$$\begin{array}{rl}& updater\\ & setA=case\\ & whenpre{d}_{1}thenresul{t}_1\\ & whenpre{d}_{2}thenresul{t}_2\\ & \cdots \\ & whenpre{d}_{n}thenresul{t}_n\\ & elseresul{t}_0\\ & end\end{array}$$
例如

	update instructorset salary = casewhen salary <=100000 then salary *1.05else salary*1.03end