CH3 Introduction to SQL
文章目录
将在CH3和CH4中分别给出SQL语句的学习笔记，先给出SQL学习的一个小框架。
S Q L 概 览 { C H 3 { 数 据 定 义 语 言 D D L — — 定 义 关 系 模 式 ， 删 除 关 系 ， 修 改 关 系 数 据 操 纵 语 言 D M L — — 查 询 ， 插 入 ， 删 除 ， 修 改 C H 4 { 连 接 视 图 完 整 性 约 束 类 型 系 统 授 权 C H 5 { 编 程 语 言 中 访 问 S Q L 触 发 器 过 程 SQL概览\begin{cases} CH3\begin{cases}数据定义语言DDL——定义关系模式，删除关系，修改关系\\数据操纵语言DML——查询，插入，删除，修改\end{cases}\\ CH4\begin{cases}连接\\视图\\完整性约束\\类型系统\\授权\end{cases}\\ CH5\begin{cases}编程语言中访问SQL\\触发器\\过程\end{cases} \end{cases} SQL概览⎩⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎨⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎧CH3{数据定义语言DDL——定义关系模式，删除关系，修改关系数据操纵语言DML——查询，插入，删除，修改CH4⎩⎪⎪⎪⎪⎪⎪⎨⎪⎪⎪⎪⎪⎪⎧连接视图完整性约束类型系统授权CH5⎩⎪⎨⎪⎧编程语言中访问SQL触发器过程
3.1数据定义语言 DDL
数据定义语言 data-definition language (DDL)给与了关于数据表规范的信息：
- 每个关系的模式
- 每个属性的取值类型
- 完整性约束
- 每个关系维护的索引集合
- 每个关系的安全性和权限信息
- 每个关系在磁盘上的物理存储结构
3.2.1 基本类型
-
c h a r ( n ) char(n) char(n)
固定长度字符串，用户指定长度n。也可以用全名：character
-
v a r c h a r ( n ) varchar(n) varchar(n)
可变长度字符串，用户指定最大长度n。等价于character varing
-
i n t int int
整数类型：和机器相关的整数的有限子集
-
s m a l l i n t small int smallint
小整数类型
-
n u m e r i c ( p , d ) numeric(p,d) numeric(p,d)
定点数，精度由用户指定。这个数有p位数字（加上一个符号位），其中d位在小数点右边。
例： n u m e r i c ( 3 , 1 ) numeric(3,1) numeric(3,1)可以存放44.5，但不能存放444.5或0.32
-
r e a l , d o u b l e , p r e c i s i o n real, double, precision real,double,precision
浮点数与双精度浮点数，精度与机器相关
-
f l o a t ( n ) float(n) float(n)
精度至少为n的浮点数
3.2.2 基本模式定义
1、属性及其类型的定义
SQL关系的定义采用 c r e a t e t a b l e create\quad table createtable命令，如下：
create table department
(dept_name varchar(20),
building varchar(5),
budget numeric(12,2),
primary key(dept_name));
下面给出create table的通用形式：
create table r(A_1 D_1,A_2 D_2,...,A_n D_n,
(integrity-constraint_1),
(integrity-constraint_2),
...
(integrity-constraint_k),)
其中A_i为模式中的属性名，D_i为属性A_i的类型及可选择的约束，用于限制所允许的A_i的集合
create table后以分号结尾，SQL语句大都如此。
2、完整性约束 integrity constraint
完整性约束保证被授权的改变数据库的操作不会造成数据一致性的丢失。
-
not null把空值排除在该属性的域之外。
-
primary key(A_1,...,A_n)PRIMARY KEY Constrants(Entity integrity)
属性 A 1 ， . . . , A n A_1，...,A_n A1，...,An构成改关系的主码。主码包含了两方面信息：
{ 非 空 没 有 两 个 元 组 主 码 \begin{cases} 非空\\ 没有两个元组主码 \end{cases} {非空没有两个元组主码 -
foreign key(A_m,...，A_n)reference rFOREIGN KEY Constrants(Referential integrity)
表明关系中任意元组在属性 A m , . . . , A n A_m,...,A_n Am,...,An上的取值必须对应关系 r r r中某元组在主码上的取值。
-
UNIQUE Constrants
任意两行不可以相同
-
DEFAULT Constraints
默认取值
-
CHAECK Constraints
语法：
CONSTRAINTS <constraint_name> <constraint>
加上完整性约束之后create table语句可以写成下面的形式：
create table instructor
(ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department);
SQL禁止任何破坏完整性约束的更新，例如：
- 新插入或新修改的元组的主码为空值
- 新插入的元组在
department关系中没有对应的dept_name
3、表的更新
插入 insert
insert into instructor values('12110','Simith','Biology',668938);
上述操作的属性值前一般不用写属性名，但要严格服从在定义表的模式时的顺序
删除数据 delete
delete from student
这个操作只是删除了表中的数据，表本身仍存在。
删除表 drop table
drop table r
变更 Alter
alter table r add A D
- A代表新增的属性名，D代表类型
- 新增之后所有的值会被默认赋值成null
alter table r drop A
A是table r中的一个属性名，这个去除的属性必须不是被其他的表所支持的，比如不能是其他的表的外键。
3.3 SQL查询的基本结构
3.3.1 单关系查询
select A1,A2,...,An
from r1,r2,...,rm
where P
其中： A 1 , A 2 , . . . , A n A_1,A_2,...,A_n A1,A2,...,An表示属性名， r 1 , r 2 , . . . , r m r_1,r_2,...,r_m r1,r2,...,rm表示表名。P是约束条件，谓词逻辑构成。
select name from instructor
需要注意的是SQL语句中是不区分大小写的，即name = NAME = Name
select语句注意要点：
-
sql允许重复的语句出现在查询结果关系中。
若不希望重复，可加入
distinct关键字select distinct dept_name from instructordistinct关键字会强制去重。使用
all关键字会强制不去重，这同时也是默认的，所以我们一般不写all关键字，但是恰当性地使用distinct关键字select all dept_name from instructor -
select选择时使用select*表示选出所有的属性 -
select可以加上+ - * /这些算术表达式。 -
select A as C这是一种起别名的方式，比如下面：
select ID,name,dept_name, sal* 12 as 'year_sal'
from instructor
起别名时 pre_name as B可以直接写成pre_name B，省略as
select中的where子句有多种写法
select from和 where语句的书写顺序为：from where select
3.3.2 多关系查询
多关系查询是指在多个表中同时查找
select * from instructor,teachers
是将两个关系进行笛卡尔乘积之后的结果予以返回。
做笛卡尔乘积后，属性的名称需要注意改变：不同名的不需要改变，同名属性加上表名，避免混淆。
where子句用来限制笛卡尔乘积所建立的组合，只留下哪些对所需答案有意义的组合。
1、自连接查询
example 1
- Find the supervisor of “Bob”
select supervisor from emp-super where person = 'Bob'
-
Find the supervisor of the supervisor of “Bob”
select S.supervisor from emp-super T, emp-super S where T.person = 'Bob' and T.supervisor = S.person
3.4 附加的基本运算
3.4.1字符串运算
%表示任意多个字符_表示一个字符
例：
找出所有名字中包含子串"dar"的教师的姓名：
select name from instructor where name like '%dar%'
如果特别的要用到含有’%'的字符的话使用转义字符即可：
- Match the string “100%”
select ...from ... where ... like '100\%' escape '\'
特别注意：
- 字符串内的内容是大小写敏感的
- Pattern matching examples: ‘Intro%’ matches any string beginning with “Intro”.
- ‘%Comp%’ 匹配那些含有子串“Comp” 的字符串.
- ‘_ _ _’ 匹配那些只有3个字符的字符串
- ‘_ _ _ %’ 匹配那些至少含有3个字符的字符串
3.4.2 排列元组的显示次序
order by提供了使查询结果按照一定次序排序的功能。
oeder by后跟具体的属性名称，默认是升序。也可以改为降序
- 升序 asc
- 降序 desc
例：
select *
from instructor
order by salary desc, name asc;
3.5 集合运算
SQL作用在关系上的union，intersect和
ept对应 ⋃ 、 ⋂ 、 − \bigcup、\bigcap、 - ⋃、⋂、−
上述的三个操作均自动去重。要想保留重复值，必须写成：union all,intersect all和except all才能保留重复值。
例
-
找出在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) -
找出那些既在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) -
找出在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)
在上面的筛选中，不会出现重复的课程号，因为intersect，union和except三个操作自动去重。假如存在某一个课程在2009年秋季的三个时段均有开课，那么也只会出现一次。若想保留重复，必须加上(操作名) all，比如union all的all关键字。
例
-
找出所有教职工工资中比最大工资小的值
select salary from instructor as T, instructor as S where T.salary < S.salary -
找出所有的可能薪资
select distinct salary from instructor -
找出教职工的最大薪资
(select distinct salary from instructor) except (select salary from instructor as T, instructor as S where T.salary < S.salary)最大薪资的另一种求法
3.6 空值
3.6.1 在算术式中
在算术式子中（即包含+,-,*,/等），若表达式的任意输入为空，则表达式的结果为空。
5 + null = returns null
3.6.2 逻辑表达式
- 比较运算：
1 < null = unknown - and：
true and null = unkonwn - or：
true or null = true - not：
not unknown = unknown - 判断是否为空：
is null - 判断是否为未知：
is unknown
OR:
- (unknown or true) = true
- (unknown or false) = unknown
- (unknown or unknown) = unknown
AND:
- (true and unknown) = unknown,
- (false and unknown) = false,
- (unknown and unknown) = unknown
NOT: (not unknown) = unknown
3.7 聚集函数
聚集函数：以值的一个集合为输入，返回单个值的函数。SQL中有五个聚集函数。
- 平均值：avg
- 最小值：min
- 最大值：max
- 总和：sum
- 计数：count
sum和avg的输入必须是数字集，但其他运算符还可以作用在非数字数据类型的集合上。
3.7.1 基本聚集
例：
-
求计算机部门的平均薪资
select avg(salary) from instructor where dept_name = 'Comp.Sci'重命名：
select avg(salary) as avg_salary from instructor where dept_name = 'Comp.Sci' -
求在2010年春季学期教授一门课程的老师的数量
select count(distinct ID) from teaches where semestor = 'Spring' and year = 2010注意加上
distinct关键字：想删除重复元组，在聚集表达式中使用关键词：distinct -
求表格有多少行
select count(*) from course在打开表格前查看表格的规模是一个好习惯。
3.7.2 分组聚集
group by子句中给出的一个或者多个属性是用来构造分组的。
group by子句中所有属性上取值相同的元组被分在一个组中。
例：
-
查找每个系的平均工资
select dept_name, avg(salary) as avg_sal from instructor group by dept_name; -
注意！！！
出现在select子句中的属性，要么是group by中列出的属性，要么是被聚集的属性。
s e l e c t { g r o u p b y 后 面 的 属 性 属 性 在 聚 集 函 数 中 select \begin{cases} group\text{ }by后面的属性\\ 属性在聚集函数中 \end{cases} select{group by后面的属性属性在聚集函数中
这样的写法是错误的：select ID, dept_name, avg(salary) from instructor group by dept_name;
当我们不写group by子句时，整个关系被当做一个分组。
3.7.3 having子句
由于where子句的执行会在分组函数group by之前，所以where子句中不能进行有关聚集函数的筛选。
而having子句是在形成分组之后才起作用，因此可以使用聚集函数进行分组限定条件。
例：
-
筛选出教师平均工资超过42000的系：
select dept_name, avg(salary) as avg_salary from instructor group by dept_name having avg(salary) > 42000;
和select相似，任何出现在having子句中，但是没有被聚集的属性必须出现在group by中。
3.7.4 对空值和布尔值的聚集
1.空值
除了count(*)以外的所有聚集函数都忽略输入集合中的空值。
sum(),avg(),max(),min()都忽略null值。
由于忽略null值，可能最后筛选时，由于输入为空，最后也返回一个空值。
但是count在输入为空是会返回0。
2.布尔值
boolean数据类型有三种值：true, false, unknown
有两个聚集函数：some和every
3.查询语句的执行顺序
⑤ s e l e c t [all/distinct][*]... ① f r o m < t a b l e n a m e 1 > [ < t a b l e n a m e 2 > ] . . . ② w h e r e < c o n d i t i o n _ e x p > ③ g r o u p b y < c o l u m n _ n a m e s > ④ h a v i n g < c o n d i t i o n _ e x p > ⑥ o r d e r b y < c o l u m n _ n a m e 1 > < c o l u m n _ n a m e 2 > . . . ⑤select\text{ [all/distinct][*]...}\\ ①from <table_name1>[<table_name2>]...\\ ②where <condition\_exp>\\ ③group\text{ }by <column\_names>\\ ④having <condition\_exp>\\ ⑥order\text{ }by<column\_name1><column\_name2>... ⑤select [all/distinct][*]...①from<tablename1>[<tablename2>]...②where<condition_exp>③group by<column_names>④having<condition_exp>⑥order by<column_name1><column_name2>...
其中from就代表了进行笛卡尔乘积。
3.8 嵌套子查询
子查询：子查询是嵌套在另一个查询中的select-from-where表达式。
- where中的子查询：对成员资格，集合的比较，集合基数进行检查
- from子句嵌套
- 标量子查询：其他的任意地方
3.8.1 集合成员资格
SQL允许测试元组在关系中的成员资格。
连接词in测试元组是否为集合中的成员。
连接词not in测试元组是否不是集合中的成员。
连
接
词
{
i
n
n
o
t
i
n
连接词\begin{cases} in\\ not\text{ }in \end{cases}
连接词{innot in
集合：select子句产生。
例：
-
找出在2009年秋季和2010年春季同时开课的所有课程
上次：使用union解决，若使用嵌套子查询：
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); -
找出在2009年秋季开课但不在2010年春季开课的所有课程
上次，使用except解决，若使用嵌套子查询：
select distinct course_id from section where semester = 'Fall' and year = 2009 and course_id not in (select course_id from section where semester = 'Spring' and year = 2010);
连接词in和not in也能用于枚举集合
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);
3.8.2 集合的比较
1.some关键字
例：找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高。
之前写法：
select name
from instructor as T, instructor as S
where S.dept_name = 'Biology' and T.salary > S.salary;
至少某一个要大使用>some关键字表示。另一种比较方式：
select name
from instructor as T
where T.salary > some(select salary
from instructor as S
where S.dept_name = 'Biology')
子查询：
(select salary
from instructor as S
where S.dept_name = 'Biology')
产生生物系的所有教师的所有工资的集合。
- some的一些表达：
- =some 恒等于 in
- <>some 不意味着 not in
2.all关键字
例：
找出满足下面条件的所有教师的姓名，他们的工资比Biology系每个教师的工资都要高。
结构**>all**对应：比所有的都要大。
select name
from instructor as T
where T.salary > all(select salary
from instruct S
where S.dept_name = 'Biology')
类似于some, SQL允许>all，<=all， >=all， =all， <>all比较。
其中：
- <>all等价于not in
- =all 并不等价于in
例：
找出有最高平均余额的支行
select branch_name, max(avg_balance)
from (select avg(balance) as avg_balance, branch_name
from account
group by branch_name) as T;
select branch_name
from account
group by branch_name
having avg(balance) >= all(select avg(balance)
from account
group by branch_name);
3.8.3 空关系测试
exists结构测试子查询的结果中是否存在元组。
exists结构在作为参数的子查询非空时返回true- 子查询为空时返回
false not exists与之相反
e x i s t s r ⇔ r ≠ ∅ n o t e x i s t s r ⇔ r ＝ ∅ exists\text{ }r \Leftrightarrow\text{ }r≠\emptyset\\ not\text{ }exists\text{ }r \Leftrightarrow\text{ }r＝\emptyset\\ exists r⇔ r=∅not exists r⇔ r＝∅
例：
找出在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);
注意区别exists结构与in结构的区别。
in写法中筛选的是sourse_id，进行与外界无关的单独筛选，判断外层结果其是否在子关系中exists结构中，是对子查询的内容是否为空的判断。
上述查询说明：
- 外层查询的一个相关名称（这里的S）可以用在
where子句的子查询中。这种使用了来自外层查询相关名称的子查询称为相关子查询 - 而在子查询中使用的别名，则不能在外层使用。
- 子查询中使用的别名，不能在别的子查询中使用。
例：
找出在2009 年秋季开课但是不在2010年春季开课的所有课程：
not in子句和except结构均使用过，这里使用not exists结构
select course_id
from section S
where course_id = 2009 and semester = 'Fall' and
not exists (select *
from section T
where semester = 'Spring' and year = 2010 and
S.course_id = T.course_id);
例：
找出所有选修了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));
这个例子非常巧妙的求出了选修了所有课程的学生。
子查询1：
select course_id from course where dept_name = 'Biology'
筛选出所有的生物系开课的学生。
子查询2：
select T.course_id from takes as T where S.ID = T.ID
筛选出所有学生S选择的课程。
except子句
两个关系相减：
- 若为空关系，说明这个学生选了所有的’Biology’系的课程；
- 若不为空，说明这个学生存在部分的’Biology’系课程未选择；
这样使用not exists子句即可得到判断。
3.8.4 重复元组存在性测试
SQL提供了一个布尔函数，测试子查询的结果是否存在重复元组——unique结构。
- 查询结果中没有重复的元组，那么
unique结构返回true值 - 反之，返回
false
例：
找到所有在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);
注意：unique结构在空集上的计算结果为真值。
unique结构未被广泛实现，mysql中不能使用该结构。
mysql中unique可以用下面的方式等价表示：
select T.course_id
from course as T
where 1>=(select count(course_id)
from section as S
where S.course_id = T.course_id and
S.year = 2009);
查询结果如下：
mysql> select T.course_id
-> from course as T
-> where 1>=(select count(course_id)
-> from section as S
-> where S.course_id = T.course_id and
-> S.year = 2009);
+-----------+
| course_id |
+-----------+
| BIO-101 |
| BIO-301 |
| BIO-399 |
| CS-101 |
| CS-315 |
| CS-319 |
| CS-347 |
| EE-181 |
| FIN-201 |
| HIS-351 |
| MU-199 |
| PHY-101 |
+-----------+
12 rows in set (0.00 sec)
另外，要注意外层查询一定要从course表格中查询，从section表格中查询会出现重复。如下：
mysql> select T.course_id
-> from section as T
-> where 1>=(select count(course_id)
-> from section as S
-> where S.course_id = T.course_id and
-> S.year = 2009);
+-----------+
| course_id |
+-----------+
| CS-101 |
| CS-101 |
| FIN-201 |
| MU-199 |
| BIO-101 |
| BIO-301 |
| HIS-351 |
| CS-319 |
| CS-347 |
| EE-181 |
| CS-319 |
| PHY-101 |
| CS-315 |
+-----------+
13 rows in set (0.00 sec)
例：
找出所有在2009年最少开设两次的课程
select T.course_id
from course as T
where not unique(select *
from section as S
where S.course_id = T.course_id and
year = 2009);
等价的在mysql中的写法及其结果如下：
mysql> select C.course_id
-> from course as C
-> where 2<=(select count(course_id)
-> from section as S
-> where C.course_id = S.course_id and
-> year = 2009);
+-----------+
| course_id |
+-----------+
| CS-190 |
+-----------+
1 row in set (0.00 sec)
注意：对一个关系中unique为假的定义时：当且仅当关系中存在着两个元组t1和t2，且t1=t2。
由于在t1或者t2的某个域为空时，判断t1=t2为假，所以尽管一个元组有多个副本，只要该元组有一个属性为空，那unique测试可能为真。
比如如果是下面的表格：
伪代码说明嵌套子查询的顺序
另外对于这样的子查询，给出一个伪代码说明查询的逻辑顺序：
最值查询小结
例：找到最大的账户余额
- 使用关系代数
Π b a l a n c e ( a c c o u n t ) − Π A 1 . b a l a n e ( σ A 1. b a l a n c e < A 2. b a l a n c e ( ρ A 1 ( a c c o u n t ) × ρ A 2 ( a c c o u n t ) ) ) \Pi_{balance}(account) - \Pi_{A_1.balane}(\sigma_{A1.balance<A2.balance}(\rho_{A1}(account)\times\rho_{A2}(account))) Πbalance(account)−ΠA1.balane(σA1.balance<A2.balance(ρA1(account)×ρA2(account)))
-
直接翻译算术表达
select balance from account except (select A1.balance from account as A1, account as A2 where A1.balance<A2.balance); -
聚集函数
select max(balance) from account; -
集合比较
select A1.balance from account as A1 where A1.balance>=all(select A2.balance from account as A2) -
not exists结构select A1.balance from account as A1 where not exists (select balance from account as A2 where A1.balance < A2.balance);
3.8.5 from子句中的子查询
SQL语句中允许from子句中出现子查询，即select-from-where结构。
例：
找出系平均工资超过42000美元的那些系中的教师的平均工资。
-
having子句select dept_name, avg(salary) as avg_sal from instructor group by dept_name having avg_salary > 42000; -
from语句中使用子查询select dept_name, avg_sal from (select dept_name, avg(salary) as avg_sal from instructor group by dept_name) where avg_sal > 42000;在mysql中不允许这样的写法，mysql子查询中的别名不能这样直接用在外层查询。
改写如下：
select dept_name, avg_sal from (select dept_name, avg(salary) as avg_sal from instructor group by dept_name) as dept_avg where avg_sal > 42000;即：mysql中的from后的子查询必须加上as作别名。where后的子查询则不必。
上面的子查询可以用having子句代替，这里举出一个不能使用having子句只能使用from子句的嵌套子查询实现的例子。
例：
找出所有系中工资总额最大的系。
select dept_name, max(sum_sal) as max_sum_sal
from (select dept_name, sum(salary) as sum_sal
from instructor
group by dept_name) as dept_sum_sal;
另外，要说明的是：from子查询中不能使用来自from子句外面的其他关系的相关变量。
在SQL 2003中允许from子句中的子查询使用关键词lateral作为前缀，以便访问from子句中在他前面的表或者子查询中的属性。
例：
打印每位老师的姓名，以及他们的工资和所在的系的平均工资。
select name, salary, avg_sal
from instructor I1, lateral(select avg(salary) as avg_sal
from instructor I2
where I2.dept_name = I1.dept_name);
目前只有少部分数据库支持lateral关键字，比如IBM DB2。（测试：在mysql 8.0版本中也可通过）
select I1.name, I1.salary, dep.avg_sal
from instructor I1, lateral(select avg(salary) as avg_sal
from instructor I2
where I2.dept_name = I1.dept_name) as dep;
select I1.name, I1.salary, dep.avg_sal
from instructor I1, lateral(select avg(salary) as avg_sal
from instructor I2
group by I1.name) as dep;
低版本的mysql这里可以使用外连接实现。
select I1.name, I1.salary, dept_avg.avg_sal
from instructor I1
left outer join (select dept_name, avg(salary) as avg_sal
from instructor I2 group by dept_name) as dept_avg
on I1.dept_name = dept_avg.dept_name;
3.8.6 with子句
with子句提供定义临时关系的方法，这个定义只对包含with子句的查询有效。
例：
找出具有最大预算值的系：
- 法一
select dept_name, max(budget)
from department;
- 法二：with子句
with max_budget(value) as
(select max(budget)
from department)
select budget
from department, max_budget
where department.budget = max_budget.value;
例：
找出所有的工资总额大于所有系平均工资总额的系：
with dept_total(dept_name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name)
dept_total_avg(value) as
(select avg(value)
from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value>=dept_total_avg.value;
3.8.7 select标量子查询
SQL允许子查询出现在返回单个值的表达式能够出现的任何地方。这样的子查询称为标量子查询。
比如之前写的select max(salary)，这个结果也是一个关系，但由于是一个值，所以可以放在标量子查询select中。
例：
列举出所有的系以及他们拥有的教师数：
select dept_name,
(select count(*) from instructor
where department.dept_name = instructor.dept_name)
as num_instructor
from department;
每次每个dept_name都要在子查询中进行筛选，所以对于每个学院来说每次嵌套子查询只返回单个值。
这也说明了对相关变量的使用：即使用在外层查询中的from子句的关系中的属性，如department.dept_name。
标量子查询可以用在select，where和having子句中。在编译时不一定能判断出来子查询的结果是否是含有多个元组，如果判断不出来，则会出现运行时错误。
另外，虽然标量子查询只有单个元组，但其本质还是一个关系。
3.9 数据库的修改
3.9.1 删除
删除请求只能删除整个元组，而不能只删除某些属性上的值。同时关系本身不会被改变。
基本结构：delete from r where P；其中r是表名，P为谓词。
-
删除表中的所有的元组：
delete from instructor; -
删除表中满足某些条件的元组：
delete from instructor where dept_name = 'Finance';delete from instructor where dept_name in (select dept_name from department where building = 'Waston');
例：
删除工资低于大学平均工资的所有的教师记录
delete from instructor
where salary < (select avg(salary)
from instructor);
这里的删除的顺序很重要，删除必须是在所以的元组都得到测试后，即确定了所有的不满足条件的元组后，然后一把删除这些元组。而不是测试一个删除一个，那样avg(salary)会发生变化，最后结果取决于删除元组的顺序。
3.9.2 插入
1.插入单个元组
insert语句实现单个元组插入请求：
-
不写属性，属性顺序要求与关系模式中的属性排列顺序一致
insert into course values('CS-437', 'Databasa Systems', 'Comp.Sci', 4); -
写属性，可以按照任意顺序插入属性
insert into course(title, course_id, credits, dept_name) values('Database Systems', 'CS-437', 4, 'Comp.Sci')
2.批量插入元组集合
想让Music系每个修满144学分的学生称为Music系的老师：
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and tot_cred > 144;
需要说明的是插入操作是在select操作执行完之后再进行的，即筛选出所有满足条件的学生后，一把插入到isntructor表中；这一点和删除操作类似。否则，在下面的语境中会出现问题：
设student表没有主码约束：
insert into student
select *
from student;
如果一筛一插，那么会插入无数个元组。
3.含有空值的insert
待插入元组中的某些未赋值属性被赋予空值，考虑请求：
insert into student
values('3003', 'Green', 'Finance', null);
3.9.3 更新
需求：在不改变整个元组的情况下改变其部分属性值。使用update语句。
待更新的元组可以使用查询语句找出。
1.全部操作与条件操作
- 全部操作
update instructor
set salary = salary * 1.05;
- 条件操作
update instructor
set salary = salary * 1.05
where salary < 70000;
条件操作的where子句还可以写成嵌套子查询等任意合法的where子句的结构。
2.updata顺序造成的问题
例：
给工资超过100000美元的教师涨3%，其余教师涨5%
update instructor
set salary = salary * 1.05
where salary > 100000;
update instructor
set salary = salary * 1.03
where salary <= 100000;
这两个更新的顺序非常重要，如果翻过来，那么可能工资较低的教师的工资被涨了8%
3.case结构
针对上面的更新顺序的问题，SQL提出了case结构。避免顺序引发问题
case结构的一般语法：
case
when pred_1 then result_1
when pred_2 then result_2
...
when pred_n then result_n
else result_0
end
那么上面的需求就可以改成下面的写法：
update instructor
set salary = case
when salary < 1000000 then salary * 1.05
else salary * 1.03
end
此外表量子查询在SQL更新语句中也非常有用，他们可以用在set字句中。如下所示：
update student S
set tot_cred = (select sum(credits)
from takes T natural join course
where T.ID = S.ID and
T.grade <> 'F' and
T.grade is not null)
如果一个学生没有上完任何课，那么上述更新语句将把其tot_cred属性值设置为空。设置为0其实更满足我们的要求：
update student S
set tot_cred = (select case
when sum(credits) is not null then sum(credits)
else 0
end
from takes T natural join course
when T.ID = S.ID and
T.grade <> 'F' and
T.grade is not null)
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
