SQL复习-第一章

---

本系列文章是阅读数据库系统概率所作的复习材料，供本人之后复习。

---

一、SQL介绍

SQL是一种查询语言，但除了数据库查询，还有个者定义数据结构，修改数据库中的数据以及安全性约束条件等等。

SQL包含一下几个部分：

二、使用步骤

1.SQL查询语言概况

SQL主要包含两种语言。

• DDL(Data definition language, DDL): DDL提供定义关系模式，删除关系，以及修改关系的命令。(CREATE创建对象，ALTER改变数据库结构，DROP删除对象，TRUNCATE截断内容，COMMENT备注)
• DML(Data manipulation language, DML): DML提供从数据库中查询信息，再数据库插入元组，删除元组等。(SELECT选择，DELETE删除，INSERT插入,LOCK线程锁定表(分读锁，写锁)，CALL调用存储过程)

除此之外，SQL还包含：
完整性 ： DDL包含定义完整性约束的命令，保存至数据库中的数据必须满足所定义的完整性约束。破坏完整性约束的更新是不允许的。
视图定义：DDL包含定义视图的命令。
事务控制：DDL包括定义事务的开始和结束的命令
授权：DDL包含定义对关系和视图的访问权限的命令

嵌入式SQL和动态SQL：嵌入式SQL和动态SQL定义SQL语句如何嵌入到通用语言，C,C++等。

2.SQL数据定义

---

数据库中的关系合集必须由DDL指定给系统，DDL不仅能够定义一组关系，也能够定义每一个关系信息。

• 每个关系的模式
• 每个属性的取值范围
• 完整性约束
• 每个关系维护的索引合集
• 每个关系的安全性和权限信息
• 每个关系在磁盘上的物理存储结构

---

2.1 数据基本类型

• char(n)：固定长度的字符串，用户指定长度n。
• varchar(n)：可变长度的字符串，用户指定最大长度n。(character varying). 与char的区别就是比如属性A（attribute A）类型是char(10)。如果存入"Avi"，该字符串后会追加7个空格来使其达到10个字符的长度，而如果是varchar(10)，就不会增加空格。
• int：整数
• smallint：小整数类型
• numeric(p,d)：定点数。精度由用户指定。这个数有p位数组，其中d位是在小数点右边。比如numeric(3,1)表示3位数字，有一个小数位。比如33.5。
• real，double precison：浮点数和双精度浮点数
• float(n)：精度至少为n位的浮点数

每一种类型都可能包含空值Null，空值表示一个缺失值，可能的情况下，我们希望禁止加入空值

当比较字符串的时候，如果是char之间的比较，如果长度不同，会自动在短的后面加速额外空格使其长度一致。比如char(10) 输入"Avi"与char(100)的“Avi”，会在char(10)的“Avi”后面加90空格。

当char与varchar之间比较，一般会在varchar后面加空格是的相等。但又可能不是这样，取决于数据库系统。所以建议始终在varchar之间来比较两个字符串。

2.2 基本模式定义

用create 创建一个table department。

CREATE table department(
(dept_name varchar(20))
(building varchar(15))
(budget bumeric(12,2))
(primary key (dept_name ))
)

attribute	domain
dept_name	varchar(20)
building	varchar(15)
budget	numeric(12,2)
完整性约束
primary key	(dept_name)

create table的通用都是 几个attribute 后接domain，最后面接上完整性描述。

SQL支持许多不同的完整性约束。

完整性约束	意义
primary key	主键声明的属性A1,A2…必须非空且唯一，没有一个元组tuple在主键上是Null并且没有两个元组是相同饿。(声明主键是可选的，但是每一个关系指定一个主键会更好)
foreign key	声明关系中任意元组在属性上的取值必须对应关系s中某元组在主键属性的取值。列子如下图(图片来源书籍：数据库系统概念)

course 表当中的 foreign key(dept_name) reference departments 表示course表中的dept_name 必然存在于department表的主键*(dept_name)*中。如果没有这一个完整性约束，那么就可能指定一个不存在的department。

not null：也是一个完整性约束，用来表示该属性attribute的取值不允许空值，把空值排除在了domain之外。一般再用domain后面，比如(A1,D1 not null)。

SQL禁止波坏完整性约束的任何数据库更新。比如

• 主键插入空值null,或者插入两个相同的主键
• foreign key 指定一个不存在的属性
• not null 插入空值

SQL会阻止这三种事情的发生。

3.SQL基本查询

SQL查询的基本结构由三个子句构成：select，from，where。查询的输入是在from给出。在给出的这些输入之上，通过where和select中指定的运算然后产生查询结构。如何理解查询最容易的方式是从from，然后where，最后select。

3.1 单关系(from一个table)查询

select name 
from instructor

在instructor输入中，找到name属性。有可能有些instructor的name是重复的。在关系模型中，关系是一个集合，重复的元组不会出现在关系中。由于去除重复非常费时，所以SQL允许关系以及结果出重复。使用distinct强行删除重复，使用all显式指明不去除重复。

select distinct name 
from instructor

select all name 
from instructor

select 允许加减乘除。

select name，salay * 1.1
from instructor

where 允许我们选出from子句的结果关系中满足特定谓语的元组。比如所有在computer science系并且工资超过7000美元的教师的姓名。

select name
from instructor
where dept_name = 'computer science' and salary > 7000

这样感觉from是代表主语教师，select表示宾语名字(教师的名字)，where 表示谓语(谓语动词，给教师加一些限制)，属于computer science的教师并且工资大于7000.
where 允许用逻辑连词 and, or, not, 比较运算符号<,>,=等等。

3.2 多关系(from多个table)查询

select name,instructor,dept_name,building
from instructor,department
where instructor.dept_name = department.dept_name

代码目的是找到所有教师，以及他们所在系的名称以及系建筑的名称。查看instructor表中，可以发现dept_name可以得到系名，但是系建筑building在department表中。所以instructor中的每个元组必须于department元组匹配，即department上dept_name的取值等于instructor的dept_name取值。因为dept_name在instructor和department都有，所以用instructor.dept_name 和 department.dept_name 代表使用的哪一个表的属性，如果不是相同的，就可以不用table名座前缀。

现在回想select,from,where之间的作用

• select列出查询结构中所需要的属性attribute
• form是一个查询求值所需要的访问关系表(table) 其实就是查询输入的table
• where是一个作用在from子句中关系table的属性attribute上面的谓词

通过from定义了一个在该子句中所列出关系上的笛卡尔积。比如instructor和teaches的笛卡尔积就是：

select name,course_id
from instructor,teaches
where instructor.id= teaches.id

(instuctor.ID,instuctor.name,instuctor.dept_name,instuctor.salary, 
teaches.ID,teaches.course_id,teaches.sec_id,teaches.semester,teaches.year)

笛卡尔积将instructor和teaches中相互没有关联组合起来。instructor中的每一个元组都要和teaches中所有元组进行组合。比如instructor里面有5个元组，teaches中有6个元组。那么一共30个元组。

where中的谓词就是用来限制笛卡尔积所建立的组合，只留下那些所需答案有意义的组合。select就是输出这些所限制的笛卡尔积的attribute。

所以说，一个SQL查询含义理解如下：

• 为from列出的关系产生笛卡尔积
• 在笛卡尔积上应用where来限制笛卡尔积所建立的组合，通过应用where上面的谓词
• 在上述条件之上，输出select指定的attribute或者表达式的结果(salary * 1.1)

3.3 自然连接

由于上述产生的笛卡尔积过于大，为了简化工作。采用自然连接。在查询事列中，需要instructor和teaches组合信息，匹配条件是instructor.id = teaches.id。这是两个table中有相同名称的所有属性。在这种情况下，我们可以使用自然连接。

自然连接(natural join): 作用于两个table,产生一个table作为结果。不同于两个关系上的笛卡尔积(把第一个关系的每一个元组于第二个关系的所有元组进行连接)，自然连接只考虑在两个table中都出现的属性上取值相同的元组。 上面的示例也变成如下：

上面的查询也可以变成如下

select name,course_id
from instructor natural join teaches

注意，自然连接只是在from子句起作用，为了减小笛卡尔积。
示例：查询教师的名字以及他们所讲授课程的名字

select name,title
from instructor natural join teaches, course
where teaches.course_id = course.course_id 

但是上面这个不等同于下面这个

select name,title
from instructor natural join teaches natural join course

这是因为第一个是先计算instructor和teaches的自然连接，在和course的笛卡尔积。是自然连接与course关系的笛卡尔积。而第二个是instructor和teaches的自然连接(ID,name,dept_name,salary,course_id,sec_id)， course(course_id,title,dept_name,credits)这两者自然连接的结果。 而这个两个的自然连接需要dept_name相等并且course_id相等。

而第一个可以等于下面的：

select name,title
from (instructor natural join teaches) natural join course using (course_id)

natural join 后面加一个course_id 表明用这个做自然连接。

3.4 附加运算

3.4.1 改名运算

在select和from子句中，可以用as改名。

select name as instructor_name,title
from (instructor natural join teaches) natural join course using (course_id)

个人感觉as 就像python里面的copy一样。

3.4.2 字符串运算

SQL用单引号表示字符串，如果字符串里面有单引号，那就用’‘表示，比如it’'s right. SQL标准中，大小是敏感的，但不同数据库系统可能不一样，有些数据库对大小写不敏感。

字符串可以使用like实现模式比配：

• % 匹配任意子串
• _ 匹配任意字符

模式对大小写敏感。SQL使用like表达模式，比如查询任何包含’Comp’的所有系名。

select dept_name
from department
where building like '%Comp%'

如果字符串中包含%,_,使用\来转义字符，比如ab%cd%就是匹配以ab%cd开头的字符串。还有not like 表示不是这个开头。

3.4.2 select子句的属性说明

*在select表示所有属性。

3.4.4 排列元组的显示次序

使用order by表示对查询结果元组按照排列顺序显示。

select name
from instructor
where dept_name = 'Physics'
order by name

加desc 降序，asc 升序，还可以多个属性上表示，如按照salary降序排列，如果salary相同的情况下，按照name升序排

select name
from instructor
where dept_name = 'Physics'
order by salary desc,name asc

3.4.5 where子句谓词

between and 表示值在两个值之间，相似的还有not between

where salary between 9000 and 10000

括号运算()

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')

(a1,a2) = (b1,b2) 等于 a1 = b1 以及 a2 = b2

3.5 集合运算

SQL中的union, intersect 和 except是数学中的 $\cup$, $\cap$ 以及 $-$。

找出2009秋季开始所有课程

select course_id
from section
where semester = 'Fall' and year = 2009

找出2010春季开始所有课程

select course_id
from section
where semester = 'spring' and year = 2010

并运算union，找出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 自动除去重复的，如果要重复就是用union all。

交运算intersect ：找到2009年秋和2010年春同时开课的课程

(select course_id
from section
where semester = 'Fall' and year = 2009)
intersect
(select course_id
from section
where semester = 'spring' and year = 2010)

intersect也是自动去除重复，保留重复就要intersect all

差运算except：找到2009年秋开课但不在2010春开课的课程(因为有些课程可能在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)

expect也是自动去除重复，保留重复就要expect all

3.6 空集

空值会带来一系列错误，算术运算，比较运算和集合运算。
对于算术运算：任何涉及到+，-，*，/一个空值，都是一个空值。
对于比较运算：任何涉及到比较<，>等都是unknown。
在集合运算中，where子句使用and, or, not 的布尔运算。

and	or	not
true and unknown = unknown	true or unknown = true	not unknown = unknown
false and unknown = false	false or unknown = unknown
unknown and unknown = unknown	unknown or unknown = unknown

3.7 聚集函数

SQL提供5个聚集函数。

• avg 平均值
• min 最小值
• max 最大值
• sum 总和
• count 计数

sum 和 avg输入必须是数字集。其他可以在字符串上用。

3.7.1 基本聚合

select avg(salary) as avg_salary
from instructor
where dept_name = 'Computer'

在计算平均值是，保留重复数组很重要。

用count计算一个关系元组的个数。SQL是count(')

select count(')
from course

3.7.2 分组聚集

找出每个系的平均工资。

select department, avg(salary) as avg_salary
from instructor
group by dept_name

使用group by将相同dept_name组成一组。

另外以过列子：查询每个系在2010年春季讲授一门课程的教师人数。（老师教授的课程在teaches中，得到系名需要在和instructor进行连接）

select dept_name, count (distinct ID) as instr_name 
from instructor natural join teaches
where semester = 'Spring' and year = 2010
group by dept_name

当使用查询分组group by的时候，一个很重要的事情是需要保证**出现在select语句中但没有被聚集的属性只能出现在group by子句中的属性。**也就是任何没有出现在group by子句属性如果在select，那个这个属性一定是在聚集函数内部。比如

#错误查询(ID不是聚集函数内部,salary可以，在avg内部)
select dept_name, ID, avg(salary)
from instructor 
group by dept_name

在一个特定分组dept_name中，每一个教师都有一个不同的ID，那就无法确定哪一个ID作为输出。

3.7.3 having子句

有时候，对分组限定条件比元组限定条件更加有用。对教授平均工资超过42000美元，不是针对单个元组，而是group by构成的分组。having子句的谓词在形成分组后才起作用。

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000

与where是对元组的限定，在from后面，having是对group by之后的分组限定。这两种使用地点不同。

having和group by的关系与刚刚上面说的group by 和 select关系类似。任何出现在having子句但是不是在聚集函数里必须出现在group by中。

整个查询流程就是：

• 最早由from计算出一个关系
• where子句中的谓词将会被应用在from子句上
• 如果出现group by，满足where的元组会通过group by形成分组，如果没有group by，满足where谓词的整个元组都会被当成一个分组
• 如果出现having，他会用的group by 所形成的每个分组上，不满足having的分组会被抛弃
• select子句就会利用剩下的分组查询结果的元组。

3.7.4 对于空值以及布尔值的聚集

聚集函数处理空值原则：除了count(*)以为的所有聚集函数都要忽略输入集合中的空值。

3.8 嵌套子查询

子查询就是另一个查询中的select-from-where的表示。

3.8.1 集合成员资格

in表示测试元组是整个关系的成员，not in表示不是。
回想之前示例：找出2009秋季开始所有课程和2010春季或者两个学期都开课的所有课程。
换一种方式思考：
查找在2009秋季开始所有课程，看他是否也是2010春季开课的课程中的成员。是就返回，不是就舍弃。
在2010春季开课的课程。

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)

3.8.2 集合的比较

查询满足下面条件的所有教授姓名，他们的工资至少比biology系得某一个教授要高。

select distinct T.name
from instructor as T, instructor as S
where T.salary>S.salary and S.dept_name = 'Biology'

至少比某一个要大用>some表示。

select distinct T.name
from instructor 
where salary >some ( select salary
from instructor
where dept_name = 'Biology'
)

也支持<some, <= some等。其中=some = in。

3.8.3 空关系检测

测试一个子查询的结构是否存在某一个元组。使用exists，返回结构true。示例：找出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)

3.8.4 重复元组存在性测试

测试一个子查询的结构是否存在重复元组。使用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
)

等价之前的 1>=：

select T.course_id
from course as T
where 1 >= (
select R.course_id
from section as R
where T.course_id = R.course_id and R.year = 2009
)

3.8.5 from子句的子查询

SQL允许在from子句中使用子查询表达式。重点：任何select-from-where表达式返回的结果都是关系，因此可以插入到另一个select-from-where关系出现的任何位置。

示例：找出系平均工资超过7000的那些系中教师的平均工资：

select dept_name, avg_salary
from (
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name )
where salary > 7000

我们不需要having子句，form子查询就已经计算出每个系的平均工资了。还可以使用as对子查询重命名。

select dept_name, avg_salary
from 
( select dept_name, avg(salary)
from instructor
group by dept_name ) as dept_avg(dept_name,avg_salary)
where salary > 7000

3.8.6 with子句

with提供定义临时关系的方法。示例找到最大预算的系。

with max_budget(value) as (select max(budget) from department)
select budget
from department,max_budget
where department.budget = max_budget.value

3.8.7 标量子查询

SQL允许子查询出现在返回单个值得表达式能够出现得任何地方，只要该查询只返回包含单个元组。这样得查询叫标量子查询。

3.9 数据库修改

3.9.1 数据库删除

SQL只能删除整个元组，不能删除某一个attribute。示例：

delete from r
where p

如果没有where，就会删除r表格中得所有元组。但是r还是存在得。delete只能作用在关系上面。
示例：位于waston大楼的系工作的老师

delete from instructor
where dept_name in 
(select dept_name
from department
where building = 'waston')

3.9.2 数据库插入

插入数据，先指定插入的关系，然后再插入元组。显然插入的元组必须再相对应属性的值域中。且数量也要相对应。

insert into course
values ('cs', 'database', 'computer',4)

或者

insert into course(course_id,title,dept_name,credits)
values ('cs', 'database', 'computer',4)

考虑有些赋空值

insert into course(course_id,title,dept_name,credits)
values ('cs', 'database', 'computer',null)

在查询基础上插入元组：
示例：让学分大于144的人成为music系老师。

insert into instructor
select ID, name, dept_name, 18000
from student
values dept_name - 'Music' and tot_cred >144

3.9.2 数据库更新

改变元组的值，用update。
比如

update instructor
set salary = 1000

示例：工资超过10000上涨3%,低于10000上涨5%，用case

update instructor
set salary = case
			where salary >10000 then salary = salary*1.03
			else salary*1.05
			end

如果将空值null设置为0，那么可以

update students
set tot_cred = 
(select case
	when sum(credits) is not null then sum(credits)
	else 0
	end

总结

SQL的第三章内容，参考书：数据库系统概念第6版。