2023年6月21日发(作者：)
数据库系统概念第三章SQL介绍⽂章⽬录第 3 章 SQL 介绍SQL 语⾔是⼀种查询语⾔，不但可以查询数据库，还可以定义数据结构、修改数据库中的数据以及定义安全性约束。3.1 SQL 查询语⾔概览SQL 最早的版本是由 IBM 开发的，它最初被叫作
Sequel，随着发展，其名称演变成 SQL（Structured
Query
Language，结构化查询语⾔）。现在有许多产品⽀持 SQL 语⾔。SQL 已经明显确⽴了⾃⼰作为标准的关系数据库语⾔的地位。SQL 语⾔有⼏个部分：数据定义语⾔。SQL DDL 提供定义关系模式、删除关系以及修改关系模式的命令。数据操纵语⾔。SQL DML 提供从数据库中查询信息以及在数据库中插⼊元组、删除元组、修改元组的能⼒。完整性。SQL DDL 包括定义完整性约束的命令，保存在数据库中的数据必须满⾜所定义的完整性约束。破坏完整性约束的更新是不允许的。视图定义。SQL DDL 包括定义视图的命令。事务管理。SQL 包括定义事务的开始点和结束点的命令。嵌⼊式 SQL 和 动态SQL。嵌⼊式和动态 SQL 定义 SQL 语句如何嵌⼊诸如 C 、C ++ 和 Java 这样的通⽤编程语⾔。授权。SQL DDL 包括定义对关系和视图的访问权限的命令。3.2 SQL 数据定义数据库中的关系集合是⽤数据定义语⾔（DDL）定义的。SQL DDL 不仅能够定义关系的集合，还能够定义有关每个关系的信息，包括：每个关系的模式每个属性的取值范围完整性约束为每个关系维护的索引集合每个关系的安全性和权限信息每个关系在磁盘上的物理存储结构3.2.1 基本类型SQL 标准⽀持多种固有类型char(n)：具有⽤户指定长度的字符串。也可以使⽤全程形式 character。varchar(n)：具有⽤户指定的最⼤长度
n 的可变长度的字符串。等价的全称形式 character varying。int：整数，全称形式是 integer。smallint：⼩整数。numeric(p，d)：具有⽤户指定精度的定点数。这个数有
p 为数字（加⼀个符号位），并且⼩数点右边有
p 位中的
d 位数字。numeric(3,1) 可以存储 44.5，共3个数字，其中⼩数点后有1位。real，double precision：浮点数与双精度浮点数，精度依赖于机器。float(n)：精度⾄少为
n 位数字的浮点数。每种类型都可能包含空值（null）。char 数据存放固定长度的字符串。如果存放字符串不⾜规定长度，则⾃动添加空格填充。但相同条件下，varchar 则不会追加空格。当⽐较两个 char 类型的值时，如果它们的长度不同，⽐较时会将短值⾃动填充使它们长度相同。当⽐较⼀个 char 类型和⼀个 varchar 类型时，你可能认为会将 varchar 类型⾃动填充使之与 char 类型长度⼀致，但实际上此过程可能不会发⽣，具体情况取决于数据库系统。即便两个类型存储相同的值，⽐较结果也可能为假，所以最好始终使⽤ varchar 类型进⾏⽐较，避免出现这样的问题。3.2.2 基本模式定义我们通过使⽤ create table 命令来定义 SQL 关系。create table department (dept_name varchar(20), buliding varchar(15), budget numeric(12,2), primary key(dept_name));上⾯创建的关系具有三个属性，并且指明了
dept_name 属性为
depaterment 关系的主码。create table 命令的通⽤形式是：create table
r(A1
D1，
A2
D2， ……，
An
Dn， < 完整性约束1 >， …… < 完整性约束n >)；其中
r 是关系名，每个
An 是关系
r 的模式中的⼀个属性名，Dn 是属性
An 的域。create table 的语句最后出现了分号，在很多SQL 实现中，分号是可选的。SQL ⽀持许多不同的完整性约束，本⼩结我们只讨论少数⼏种：primary key：主码声明表⽰属性
Aj1 , … ,
Ajm 构成关系的主码。主码必须是⾮空且唯⼀的。foreign key references
s：外码声明表⽰关系中任意元组在属性 (
Ak1 , … ,
Akm ) 上的取值必须对应于关系
s 中某元组在主码属性上的取值。not null：⼀个属性上的⾮空约束表明在该属性上不允许存在空值。SQL 禁⽌破坏完整性约束的任何数据库更新。⼀个新创建的关系最初是空的。向关系中插⼊元组、更新元组以及删除元组是通过数据操纵语句 insert 、update 和 delete 来完成的。如果要从 SQL 数据库中去掉⼀个关系，我们使⽤ drop table 命令。从数据库中删除关于被去掉关系的所有信息。命令drop table r是⽐delete from r更强的语句。后者保留关系
r ，但删除
r 中的所有元组。前者不仅删除
r 中的所有元组，还要删除关系
r 的模式。除⾮⽤ create table 命令重新创建
r ，否则没有元组可以插⼊
r 。我们使⽤ alter table 命令为已有关系增加属性。关系中的所有元组在新属性上的取值将被赋为
null 。命令。格式为alter table r add AD；其中
r 是现有关系的名称，A 是待添加属性的名称，D 是带添加属性的名称。我们可以通过命令alter table r drop A从关系中去掉属性。很多数据库系统并不⽀持去掉属性，尽管他们允许去掉整张表。3.3 SQL 查询的基本结构SQL 查询的基本结构由三个字句构成：select 、from 和 where 。查询以在 from ⾃⼰中列出的关系作为其输⼊，在这些关系上进⾏where 和 select 字句中指定的运算，然后产⽣⼀个关系作为结果。3.3.1 单关系查询在关系模型的形式化数学定义中，关系是⼀个集合。因此，重复的元组不会出现在关系中。在实践中，去除重复是相当费时的。我们可以在 selecet 后插⼊关键字 distinct ，使得每个查询结果只能出现⼀次。select distinct dept_namefrom instructor;SQL 允许我们使⽤关键字 all 来显⽰指明不去除重复：select all dept_namefrom instructor;select 字句还可带含有 + 、- 、* 、/select ID,name,depe_name,salary * 1.1from instructor;返回⼀个与
instructor 关系⼀样的关系，只是
salary 属性的值是原来的 1.1 倍。但这并不会导致
instructor 关系发⽣任何改变。where 字句允许我们只选出那些在 from 字句的结果关系中满⾜特定谓词的元组。select namefrom instructorwhere dept_name = '.' and salary > 70000;SQL 允许在 where 字句中使⽤逻辑连词 and 、or 和 not 。逻辑连词的运算对象可以使⽐较运算符的表达式。SQL 允许我们使⽤⽐较运算符来⽐较字符串、算术表达式以及特殊类型，⽐如⽇期类型。3.3.2 多关系查询现在我们考虑涉及多个关系的 SQL 查询的通⽤形式。正如我们在前⾯已经看到的，⼀个 SQL 查询可以包括三中类型的字句：select字句、from 字句和 where 字句。它们的作⽤如下：select 字句⽤于列出查询结果中所需要的属性。from 字句是在查询求值中需要访问的关系列表。where 字句是作⽤在 from 字句中的关系的属性上的谓词。尽管各字句必须以 select 、from 、where 的次序写出，但理解查询所代表的运算的最容易的⽅式是以运算的顺序来考察各字句：⾸先是 from ，然后是 where ，最后是 select 。通过 from 字句定义了⼀个在该字句中所列出关系上的笛卡尔积。它可以⽤关系代数来形式化地定义，单页可以理解为⼀个迭代过程，此过程可为 from 字句的结果关系产⽣元组。for each 元组 t1 in 关系 r1 for each 元组 t2 in 关系 r2 ··· for each 元组 tm in 关系 rm 把 t1,t2,···,tm 连接成单个元组 t 把 t 加⼊结果关系中此结果关系具有来⾃ from ⼦句中所有关系的所有属性。通过笛卡尔积把来⾃不同的关系中相互没有关系的元组组合到⼀起。但其结果可能是⼀个⾮常庞⼤的关系，创建这样的笛卡尔积通常是没有意义的。取⽽代之的是在 where ⼦句中使⽤谓词来限制笛卡尔积所创建的组合，只留下那些对所需答案有意义的组合。通常来说，⼀个 SQL 查询的含义可以理解如下：1. 为 from ⼦句中列出的关系产⽣笛卡尔积。2. 在步骤 1 的结果上应⽤ where ⼦句中指定的谓词。3. 对于步骤 2 的结果中的每个元组，输出 select ⼦句中指定的属性（或表达式的结果）。3.4 附加的基本运算3.4.1 更名运算as ⼦句即可出现在 select ⼦句中，也可以出现在 from ⼦句中。as ⼦句在重命名关系时特别有⽤。重命名关系的⼀个原因是把⼀个肠的关系名替换成短的，这样在查询中的其他地⽅使⽤起来就更为⽅便。重命名关系的另⼀个原因是为了适⽤于需要⽐较同⼀个关系中的元组的情况。例如select distinct om instructor as T,instructor as Swhere > and _name = 'Biology';上述查询中，T 和
S 可以被认为是
instructor 关系的两份拷贝，但更准确地说，他们被声明为
instructor 关系的别名，也就是另外的名称。像
T 和
S 那样被⽤来命名重命名关系的标识在
SQL 标准中被称作相关名称，但通常也被称作表别名，或相关变量，或元组变量。3.4.2 字符串运算在
SQL 标准中，字符串上的相等运算是⼤⼩写敏感的，但在例如
MySQL 和
SQLServer 中，在匹配字符串时并不区分⼤⼩写。这种缺省⽅式是可以在数据库级或特定属性级修改的。SQL 还允许在字符串上作⽤多种函数，例如连接字符串（“||”）、提取⼦串、计算字符串长度、⼤⼩写转换（upper(s) 和 lower(s) ）、去掉字符串后⾯的空格（trim(s) ）等。不同数据库系统所提供的字符串函数集是不同的。在字符串上可以使⽤ like 运算符来实现模式匹配百分号( % ) ：% 字符匹配任意⼦串。下划线( _ ) ：_ 字符匹配任意⼀个字符。模式是⼤⼩写敏感的，也就是说，⼤写字符与⼩写字符不匹配，反之亦然，例如：‘Intro%’ 匹配以 ‘Intro’ 打头的任意字符串‘%Comp%’ 匹配以 ‘Comp’ ⼦串的任意字符串。‘_ _ _’ 匹配致函三个字符的任意字符串。‘_ _ _ %’ 匹配⾄少含有三个字符的任意字符串。为了使模式能够包含特殊的模式字符 ( 即 % 和 _ ) ，SQL 允许定义转义字符。我们在 like ⽐较运算中使⽤ escape 关键字来定义转义字符。为了说明这⼀⽤法，考虑以下模式，其中使⽤反斜线 ( ) 作为转义字符：like ‘ab%cd%’ escape ‘’ 匹配以 “ab%cd” 开头的所有字符串。like ‘abcd%’ escape ‘’ 匹配以 “abcd” 开头的所有字符串。SQL 允许我们通过使⽤ not like ⽐较运算符来搜索不匹配项。⼀些实现还提供 like 运算的变种，它不区分⼤⼩写。3.4.3 select 字句中的属性说明星号 ” * “ 可以⽤在 select ⼦句中表⽰ ” 所有的属性 “。3.4.4 排列元组的显⽰次序order by ⼦句的可以让查询结果中的元组按排列顺序显⽰，order by 字句按升序列出显⽰项。我们可以⽤ desc 表⽰降序，或⽤ asc表⽰升序。select *from instructororder by salary desc,name asc;3.4.5 ⼦句谓词为了简化 where ⼦句，SQL 提供 between ⽐较运算符来说明⼀个值⼩于或等于某个值，同时⼤于或等于另⼀个值。where salarybetween 90000 and 100000; 可以取代
where salary <= 100000 and salary >= 90000;类似地，我们还可以使⽤ not between。SQL 允许我们使⽤符号 (v1，v2，···，vn) 表⽰⼀个包含值
v1，v2，···，vn 的 n 维元组；该符号被称为⾏构造器。在元组上可以运⽤⽐较运算符，并按字典顺序进⾏⽐较运算。例如，当
a1 <=
b1 且
a2 <=
b2 时，(a1，a2) <= (b1，b2) 为真。3.5 集合运算SQL 作⽤在关系上的 union、intersect 和 except 运算对应于数学集合论中的 ∪ 、 ∩ 、- 运算。3.5.1 并运算(select course_id from section where semester = 'Fall' and year= 2017)union(select course_id from section where semester = 'Spring' and year= 2018);与 select ⼦句不同，union 运算⾃动去除重复。如果我们想保留所有的重复项，就必须⽤ union all 代替 union 。3.5.2 交运算(select course_id from section where semester = 'Fall' and year= 2017)intersect(select course_id from section where semester = 'Spring' and year= 2018);intersect 运算⾃动去除重复。如果我们想保留所有的重复项，就必须⽤ intersect all 代替 intersect 。3.5.3 差运算(select course_id from section where semester = 'Fall' and year= 2017)except(select course_id from section where semester = 'Spring' and year= 2018);except 运算从其第⼀个输⼊中输出不出现在第⼆个输⼊中的所有元组，即执⾏差操作。如果我们想保留所有的重复项，就必须⽤ except all 代替 except 。3.6 空值SQL 将涉及空值的任何⽐较运算的结果视为 unknown（既不是谓词 is null，也不是 is not null）。这创建了除
true 和
false 之外的第三种逻辑值。and：true and
unknown 的结果是
unknown，false and
unknown 的结果是
false， ⽽
unknown and
unknown 的结果是unknown。or：true or
unknown 的结果是
true，false or
unknown 的结果是
unknow， ⽽
unknown or
unknown 的结果是
unknown。not：not
unknow 的结果是
unknown。SQL 在谓词中使⽤特殊的关键字 null 来测试空值。如果谓词 is not null 所作⽤的值⾮空，那么谓词为真。SQL 允许我们通过使⽤ is unknown 和 is not unknown ⼦句来测试⼀个⽐较运算的结果是否为 unknown，⽽不是 true 或 false。当⼀个查询使⽤ select distinct ⼦句时，重复元组必须去除。如果这两个值都⾮空且相等，或者都为空，那么他们被认为是相同的。使⽤ distinct ⼦句时仅保留这样的相同元组的⼀份拷贝。在谓词中 “ null = null ” 会返回 unknown，⽽不是 true。3.7 聚集函数聚集函数 是以值集（集合或多重集合）为输⼊并返回单个值的函数。SQL 提供了五个标准的固有聚集函数。平均值：avg。最⼩值：min。最⼤值：max。总和：sum。计数：count。sum 和 avg 的输⼊必须是数字集，但其他运算符可以作⽤在⾮数字数据类型的集合上，⽐如字符串。3.7.1 基本聚集计算平均值时保留重复项是很重要的。有时在计算聚集函数之前必须先去重，如果确实想去除重复项，可在聚集表达是种使⽤关键字 distinct。我们经常使⽤聚集函数 count 来计算⼀个关系中元组的数量。在 SQL 中该函数的写法是 count(*) 。select count(*)from course;SQL 不允许在⽤ count(*) 时使⽤ distinct。在⽤ max 和 min 时使⽤ distinct 是合法的，尽管结果并⽆差别。3.7.2 分组聚集有时候我们不仅希望将聚集函数作⽤在单个元组集上⽽且还希望将其作⽤在⼀组元组集上；在 SQL 中可使⽤ group by ⼦句实现这个愿望。group by ⼦句中给出的⼀个或多个属性是⽤来构造分组的。在分组⼦句中的属性上取值相同的元组将被分在⼀个组内。select dept_name,avg(salary)asavg_salaryfrom instructorgroup by dept_name;当 SQL 查询使⽤分组时，⼀个很重要的事情是确保出现在 select 语句中但没有被聚集的属性只能出现在 group by ⼦句中的那些属性。换句话说，任何没有出现在 group by ⼦句中的属性如果出现在 select ⼦句中，它只能作为聚集函数的参数，否则这样的查询就是错误的。同时，在 SQL 中⽤ “ /* */ ” 包含⽂本的⽅式编写注释，同样也可以⽤ “ --注释内容 ” 的⽅法。3.7.3 having ⼦句有时候，对分组限定条件⽐对元组限定条件更有⽤。为了针对单个元组进⾏限定，我们可以使⽤ SQL 的 having ⼦句。在 having ⼦句中可以使⽤聚集函数，⽽ where ⼦句中⽆法使⽤聚集函数。select dept_name,avg(salary)as avg_salaryfrom instructorgroup by dept_namehaving avg(salary) > 42000;与 select ⼦句的情况相类似，任何出现在 having ⼦句中，但没有被聚集的属性必须出现在 group by ⼦句中，否则就是查询错误的。包含聚集、group by 或 having ⼦句的查询的含义可通过下述运算序列来定义：与不带聚集的查询情况类似，⾸先根据 from ⼦句来计算出⼀个关系。如果出现了 where ⼦句，where ⼦句中的谓词将应⽤到 from ⼦句的结果关系上。如果出现了 group by ⼦句，满⾜ where 谓词的元组通过 group by ⼦句将被放⼊分组中。如果没有 group by ⼦句，满⾜where 谓词的整个元组集被当成⼀个分组。如果出现了 having ⼦句，它将应⽤到每个分组上；不满⾜ having ⼦句谓词的分组将被去掉。select ⼦句利⽤剩下的分组产⽣查询结果中的元组，记载每个分组上应⽤聚集函数来得到单个结果的元组。3.7.4 对空值和布尔值的聚集SQL 标准并不认为总和本⾝为
null，⽽是认为 sum 运算符应忽略其输⼊的
null 值。空集的 count 运算值为 0 。布尔数据类型，它可以取 true、false 和 unknown 三种值。3.8 嵌套⼦循环SQL 提供嵌套⼦查询机制。⼦查询是嵌套在另⼀个查询中的 select-from-where 表达式。通过将⼦查询嵌套在 where ⼦句中，通常可以⽤⼦循环来执⾏对集合成员资格的测试、对象合法的⽐较以及对集合集数的确定。3.8.1 集合成员资格SQL 允许测试元组在关系中的成员资格。连接词 in 测试集合成员规则，这⾥的集合是由 select ⼦句产⽣的⼀组值构成的。连接词not in 测试集合成员资格的缺失。select distinct course_idfrom sectionwhere sememster = 'Fall' and year= 2017 and course_id in (select course_id from section where semester = 'Spring' and year= 2018);在这⾥我们需要使⽤ distinct，因为 intersect 运算在缺省情况下是去除重复的。3.8.2 集合⽐较“ ⾄少⽐某⼀个要⼤ ” 在 SQL 中⽤ >some 表⽰。select distinct om instructor as T,instructor as Swhere > and _name = 'Bioligy';SQL 也允许
I1。只有最新的 SQL 实现才⽀持 lateral ⼦句。3.8.6 with ⼦句with ⼦句提供了⼀种定义临时关系的⽅式，这个定义只对包含 with ⼦句的查询有效。with max_budgest (value) as (select max(budget) from department)select budgetfrom department,max_budgestwhere = max_;该查询中的 with ⼦句定义了临时关系
max_budget，此关系包含定义了此关系的⼦查询结果元组。此关系只能在同⼀查询的后⾯部分使⽤。我们也能使⽤ from ⼦句或 where ⼦句中的嵌套⼦查询来编写上述查询。但是⽤嵌套⼦查询会是的查询更加晦涩难懂。with ⼦句使查询在逻辑上更加清晰，它还允许在⼀个查询内的多个地⽅使⽤这种临时关系。3.8.7 标量⼦查询SQL 还允许查询出现在返回单个值的表达时能够出现的任何地⽅，只要该⼦查询只返回⼀个包含单个属性的元组；这样的⼦查询称为标量⼦查询。select dept_name, (select count(*) from instructor where _name = _name) as num_instructorsfrom department;上⾯实例中的⼦查询保证只返回单个值，因为它使⽤了不带 group by 的 count(*) 聚集函数。此例也说明了相关变量的使⽤，即使使⽤外层查询的 from ⼦句中的关系的属性。标量⼦查询还可以出现在 select 、where 和 having ⼦句中。也就是不使⽤聚集函数来定义标量⼦查询。在编译时并⾮总能判断⼀个⼦查询返回的结果中是否有多个元组；如果在⼦查询被执⾏后其结果中不⽌⼀个元组，则产⽣⼀个运⾏时错误。从技术上将标量⼦查询的结果类型仍然是关系，尽管其中只包含单个元组。挡在表达式中使⽤标量⼦查询时，他出现的位置是期望单个值出现的地⽅，SQL 就从该关系中包含单属性的单个元组中隐式地去除相应的值，并返回该值。3.8.8 不带 from ⼦句的标量某些查询需要计算，但不需要引⽤任何关系。类似地，某些查询可能有包含 from ⼦句的⼦查询，但⾼层查询不需要 from ⼦句。(select count(*) from teaches) / (select count(*) from instructor);尽管在⼀些系统中这样写是合法的，但其他系统会由于缺少 from ⼦句⽽报错。在后⼀种情况下，可以创建⼀个特殊的虚拟关系，例如创建包含单个元组的
dual 关系。这使得前⾯的查询可以写为：select (select count(*) from teachers) / (select count(*) from instructor)from dual;Oracle 针对上述⽤途提供了⼀个称作
dual 的预定义关系，它包含单个元组（此关系具有单个属性，但这与我们的⽤途⽆关）。上述查询是⽤⼀个整数除以另⼀个整数，在⼤多数据库上，其结果都会是⼀个整数，这就会导致精度缺失。可以将两个⼦查询结果都乘以1.0，将其转换为浮点数。3.9 数据库的修改到⽬前为⽌我们的注意⼒都集中在对数据库信息的抽取上。现在我们将展⽰如何⽤ SQL 来增加、删除或修改信息。3.9.1 删除删除请求的表达⽅式与查询⾮常类似。我们只能删除整个元组，⽽不能只删除某些属性上的值。delete from rwhere P;其中
P 代表⼀个谓词，⽽
r 代表⼀个关系。delete 语句⾸先从
r 中找出使
P(t) 为真的所有元组
t，然后把它们从
r 中删除。where⼦句可以省略，在省略的情况下
r 中的所有元组都将被删除。⼀条 delete 命令只能作⽤于⼀个关系。如果我们想从多个关系中删除元组，必须为每个关系是⽤⼀条 delete 命令。删除⼀个关系中的所有元组，关系仍然存在，只是它变成空的了。虽然我们⼀次只能从⼀个关系中删除元组，但是在 delete 的 where ⼦句中嵌套的 select-from-where 中，可以引⽤任意数量的关系。delete 请求可以包含嵌套的 select，该 select 引⽤待删除元组的关系。3.9.2 插⼊要往关系中插⼊数据，要么指定待插⼊的元组，要么写⼀条查询语句来⽣成待插⼊的元组集合。待插⼊元组的属性值必须在相应属性的域中存在。类似地，带插⼊元组的属性数量也必须是正确的。最简单的 insert 语句是插⼊⼀个元组的请求。插⼊⼀条信息：Computer Science 系开设的名为 “ Database System ” 的课程CS-437 有 4 个学时。insert into course values('CS-437','Database System','',4);为了⽅便那些可能不记得关系属性排列顺序的⽤户，SQL 允许在 insert 语句中指定属性。insert into course (course_id,title,dapt_name,credits) values('CS-437','Database System','',4);insert into course (title,course_id,credits,dapt_name) values('Database System','CS-437',4,'');SQL 还⽀持在查询结果的基础上插⼊元组。例如让 Music 系每个修满 144 学时的学⽣成为 Music 系的教师，⼯资为 18000 美元：insert into instructor
select ID,name,dept_name,18000 from student where dept_name = 'Music' and tot_cred > 144;
如果在插⼊的关系上没有主码约束，这样的请求就可能会插⼊⽆数元组。insert into student select * from student;如果没有主码约束，上述请求会重新插⼊
student 中的第⼀个元组，产⽣该元组的第⼆份拷贝。由于没有这第⼆份拷贝 现在成为
student 的⼀部分，select 语句可能找到它，于是第三份拷贝被插⼊
student 中。这第三份拷贝有可能被 select 语句发现，于是有插⼊第四份拷贝，如此等等，⽆限循环。在执⾏插⼊之前先完成 select 语句的执⾏可以避免这样的问题。这样，如果在
student 关系上没有主码约束，那么上述 insert 语句就只是把
student 关系中的每个元组都复制⼀遍。有可能对待插⼊元组只给出了模式中的某些属性的值，其余属性将被赋空值
null 。⼤多数关系数据库产品都有特殊的 “ bulk loader ” ⼯具，它可以向关系中插⼊⼀个⼤的元组集合。这些⼯具允许从格式化的⽂本⽂件中读出数据，并且它们的执⾏速度⽐等价的插⼊语句序列要快得多。3.9.3 更新在某些情况下，我们可能希望在不改变⼀个元组所有值的情况下改变其某个属性的值，外达到这⼀⽬的，可以使⽤ update 语句。与使⽤ insert 、delete 类似，我们可以通过使⽤查询来选出待更新的元组。假设要进⾏年度⼯资增长，所有教师的⼯资将增长 5%：update instructorset salary= salary * 1.05;上⾯的更新语句将在
instructor 关系的每个元祖上各执⾏⼀次。update 语句的 where ⼦句可以包含 select 语句的 where ⼦句中的任何合法结构。和 insert 、delete 类似，update 语句中嵌套的select 可以引⽤待更新的关系。同样，SQL ⾸先检查关系中的所有元组，看它们是否应该被更新，然后才执⾏更新。SQL 提供 case 结构，我们可以利⽤它在单条 update 语句中执⾏前⾯的更新，以避免更新次序引发的问题，case 语句的⼀般结构如下：case when pred1 then result1 when pred2 then result2 ··· when predn then resultn else result0end当
i 是第⼀个满⾜的
pred1，pred2，···，predn 时，此操作就会返回
resulti ；如果没有⼀个谓词可以满⾜，则运算返回
result0 。case 语句可以⽤在应该出现值的任何地⽅。update instructorset salary = case when salary <= 100000 then salary * 1.05 else salary * 1.03end标量⼦查询在 SQL 中更新语句中⾮常有⽤，他们可以⽤在 set ⼦句中。例如我们想把每个
student 元组的
tot_cred 属性值设为该⽣成功学完的课程学分总和。我们假设如果⼀名学⽣在某门课程上的成绩既不是 ‘ F ’ 也不是空，那么他就完成了这门课程：update studentset tot_cred = ( select sum(credits) from takes,course where = and _id = _id and <> 'F' and is not null);如果⼀名学⽣没有成功学完任何课程，上述语句将把其
tot_cred 属性值置为空。如果想把这样的属性值置为 0 ，我们可以⽤另⼀条update 语句来把空值替换为 0 。更好的替代⽅案是把上述查询中的 “ select sum(credits) ” ⼦句替换为如下使⽤ case 表达式的select ⼦句：select case when sum(credits) is not null then sum(credits) else 0 end许多系统都⽀持 coalesce 函数，我们将在后⾯的 4.5.2 节详细描述该函数，他它提供了⼀种⽤其他只替换
null 的简洁⽅法。在上⾯的⽰例中，我们可以使⽤ coalesce(sum(credits),0) 来代替 case 表达式；该表达式如果不为空，则返回聚集结果 sum(credits)，否则返回 0 。
2023年6月21日发(作者：)
数据库系统概念第三章SQL介绍⽂章⽬录第 3 章 SQL 介绍SQL 语⾔是⼀种查询语⾔，不但可以查询数据库，还可以定义数据结构、修改数据库中的数据以及定义安全性约束。3.1 SQL 查询语⾔概览SQL 最早的版本是由 IBM 开发的，它最初被叫作
Sequel，随着发展，其名称演变成 SQL（Structured
Query
Language，结构化查询语⾔）。现在有许多产品⽀持 SQL 语⾔。SQL 已经明显确⽴了⾃⼰作为标准的关系数据库语⾔的地位。SQL 语⾔有⼏个部分：数据定义语⾔。SQL DDL 提供定义关系模式、删除关系以及修改关系模式的命令。数据操纵语⾔。SQL DML 提供从数据库中查询信息以及在数据库中插⼊元组、删除元组、修改元组的能⼒。完整性。SQL DDL 包括定义完整性约束的命令，保存在数据库中的数据必须满⾜所定义的完整性约束。破坏完整性约束的更新是不允许的。视图定义。SQL DDL 包括定义视图的命令。事务管理。SQL 包括定义事务的开始点和结束点的命令。嵌⼊式 SQL 和 动态SQL。嵌⼊式和动态 SQL 定义 SQL 语句如何嵌⼊诸如 C 、C ++ 和 Java 这样的通⽤编程语⾔。授权。SQL DDL 包括定义对关系和视图的访问权限的命令。3.2 SQL 数据定义数据库中的关系集合是⽤数据定义语⾔（DDL）定义的。SQL DDL 不仅能够定义关系的集合，还能够定义有关每个关系的信息，包括：每个关系的模式每个属性的取值范围完整性约束为每个关系维护的索引集合每个关系的安全性和权限信息每个关系在磁盘上的物理存储结构3.2.1 基本类型SQL 标准⽀持多种固有类型char(n)：具有⽤户指定长度的字符串。也可以使⽤全程形式 character。varchar(n)：具有⽤户指定的最⼤长度
n 的可变长度的字符串。等价的全称形式 character varying。int：整数，全称形式是 integer。smallint：⼩整数。numeric(p，d)：具有⽤户指定精度的定点数。这个数有
p 为数字（加⼀个符号位），并且⼩数点右边有
p 位中的
d 位数字。numeric(3,1) 可以存储 44.5，共3个数字，其中⼩数点后有1位。real，double precision：浮点数与双精度浮点数，精度依赖于机器。float(n)：精度⾄少为
n 位数字的浮点数。每种类型都可能包含空值（null）。char 数据存放固定长度的字符串。如果存放字符串不⾜规定长度，则⾃动添加空格填充。但相同条件下，varchar 则不会追加空格。当⽐较两个 char 类型的值时，如果它们的长度不同，⽐较时会将短值⾃动填充使它们长度相同。当⽐较⼀个 char 类型和⼀个 varchar 类型时，你可能认为会将 varchar 类型⾃动填充使之与 char 类型长度⼀致，但实际上此过程可能不会发⽣，具体情况取决于数据库系统。即便两个类型存储相同的值，⽐较结果也可能为假，所以最好始终使⽤ varchar 类型进⾏⽐较，避免出现这样的问题。3.2.2 基本模式定义我们通过使⽤ create table 命令来定义 SQL 关系。create table department (dept_name varchar(20), buliding varchar(15), budget numeric(12,2), primary key(dept_name));上⾯创建的关系具有三个属性，并且指明了
dept_name 属性为
depaterment 关系的主码。create table 命令的通⽤形式是：create table
r(A1
D1，
A2
D2， ……，
An
Dn， < 完整性约束1 >， …… < 完整性约束n >)；其中
r 是关系名，每个
An 是关系
r 的模式中的⼀个属性名，Dn 是属性
An 的域。create table 的语句最后出现了分号，在很多SQL 实现中，分号是可选的。SQL ⽀持许多不同的完整性约束，本⼩结我们只讨论少数⼏种：primary key：主码声明表⽰属性
Aj1 , … ,
Ajm 构成关系的主码。主码必须是⾮空且唯⼀的。foreign key references
s：外码声明表⽰关系中任意元组在属性 (
Ak1 , … ,
Akm ) 上的取值必须对应于关系
s 中某元组在主码属性上的取值。not null：⼀个属性上的⾮空约束表明在该属性上不允许存在空值。SQL 禁⽌破坏完整性约束的任何数据库更新。⼀个新创建的关系最初是空的。向关系中插⼊元组、更新元组以及删除元组是通过数据操纵语句 insert 、update 和 delete 来完成的。如果要从 SQL 数据库中去掉⼀个关系，我们使⽤ drop table 命令。从数据库中删除关于被去掉关系的所有信息。命令drop table r是⽐delete from r更强的语句。后者保留关系
r ，但删除
r 中的所有元组。前者不仅删除
r 中的所有元组，还要删除关系
r 的模式。除⾮⽤ create table 命令重新创建
r ，否则没有元组可以插⼊
r 。我们使⽤ alter table 命令为已有关系增加属性。关系中的所有元组在新属性上的取值将被赋为
null 。命令。格式为alter table r add AD；其中
r 是现有关系的名称，A 是待添加属性的名称，D 是带添加属性的名称。我们可以通过命令alter table r drop A从关系中去掉属性。很多数据库系统并不⽀持去掉属性，尽管他们允许去掉整张表。3.3 SQL 查询的基本结构SQL 查询的基本结构由三个字句构成：select 、from 和 where 。查询以在 from ⾃⼰中列出的关系作为其输⼊，在这些关系上进⾏where 和 select 字句中指定的运算，然后产⽣⼀个关系作为结果。3.3.1 单关系查询在关系模型的形式化数学定义中，关系是⼀个集合。因此，重复的元组不会出现在关系中。在实践中，去除重复是相当费时的。我们可以在 selecet 后插⼊关键字 distinct ，使得每个查询结果只能出现⼀次。select distinct dept_namefrom instructor;SQL 允许我们使⽤关键字 all 来显⽰指明不去除重复：select all dept_namefrom instructor;select 字句还可带含有 + 、- 、* 、/select ID,name,depe_name,salary * 1.1from instructor;返回⼀个与
instructor 关系⼀样的关系，只是
salary 属性的值是原来的 1.1 倍。但这并不会导致
instructor 关系发⽣任何改变。where 字句允许我们只选出那些在 from 字句的结果关系中满⾜特定谓词的元组。select namefrom instructorwhere dept_name = '.' and salary > 70000;SQL 允许在 where 字句中使⽤逻辑连词 and 、or 和 not 。逻辑连词的运算对象可以使⽐较运算符的表达式。SQL 允许我们使⽤⽐较运算符来⽐较字符串、算术表达式以及特殊类型，⽐如⽇期类型。3.3.2 多关系查询现在我们考虑涉及多个关系的 SQL 查询的通⽤形式。正如我们在前⾯已经看到的，⼀个 SQL 查询可以包括三中类型的字句：select字句、from 字句和 where 字句。它们的作⽤如下：select 字句⽤于列出查询结果中所需要的属性。from 字句是在查询求值中需要访问的关系列表。where 字句是作⽤在 from 字句中的关系的属性上的谓词。尽管各字句必须以 select 、from 、where 的次序写出，但理解查询所代表的运算的最容易的⽅式是以运算的顺序来考察各字句：⾸先是 from ，然后是 where ，最后是 select 。通过 from 字句定义了⼀个在该字句中所列出关系上的笛卡尔积。它可以⽤关系代数来形式化地定义，单页可以理解为⼀个迭代过程，此过程可为 from 字句的结果关系产⽣元组。for each 元组 t1 in 关系 r1 for each 元组 t2 in 关系 r2 ··· for each 元组 tm in 关系 rm 把 t1,t2,···,tm 连接成单个元组 t 把 t 加⼊结果关系中此结果关系具有来⾃ from ⼦句中所有关系的所有属性。通过笛卡尔积把来⾃不同的关系中相互没有关系的元组组合到⼀起。但其结果可能是⼀个⾮常庞⼤的关系，创建这样的笛卡尔积通常是没有意义的。取⽽代之的是在 where ⼦句中使⽤谓词来限制笛卡尔积所创建的组合，只留下那些对所需答案有意义的组合。通常来说，⼀个 SQL 查询的含义可以理解如下：1. 为 from ⼦句中列出的关系产⽣笛卡尔积。2. 在步骤 1 的结果上应⽤ where ⼦句中指定的谓词。3. 对于步骤 2 的结果中的每个元组，输出 select ⼦句中指定的属性（或表达式的结果）。3.4 附加的基本运算3.4.1 更名运算as ⼦句即可出现在 select ⼦句中，也可以出现在 from ⼦句中。as ⼦句在重命名关系时特别有⽤。重命名关系的⼀个原因是把⼀个肠的关系名替换成短的，这样在查询中的其他地⽅使⽤起来就更为⽅便。重命名关系的另⼀个原因是为了适⽤于需要⽐较同⼀个关系中的元组的情况。例如select distinct om instructor as T,instructor as Swhere > and _name = 'Biology';上述查询中，T 和
S 可以被认为是
instructor 关系的两份拷贝，但更准确地说，他们被声明为
instructor 关系的别名，也就是另外的名称。像
T 和
S 那样被⽤来命名重命名关系的标识在
SQL 标准中被称作相关名称，但通常也被称作表别名，或相关变量，或元组变量。3.4.2 字符串运算在
SQL 标准中，字符串上的相等运算是⼤⼩写敏感的，但在例如
MySQL 和
SQLServer 中，在匹配字符串时并不区分⼤⼩写。这种缺省⽅式是可以在数据库级或特定属性级修改的。SQL 还允许在字符串上作⽤多种函数，例如连接字符串（“||”）、提取⼦串、计算字符串长度、⼤⼩写转换（upper(s) 和 lower(s) ）、去掉字符串后⾯的空格（trim(s) ）等。不同数据库系统所提供的字符串函数集是不同的。在字符串上可以使⽤ like 运算符来实现模式匹配百分号( % ) ：% 字符匹配任意⼦串。下划线( _ ) ：_ 字符匹配任意⼀个字符。模式是⼤⼩写敏感的，也就是说，⼤写字符与⼩写字符不匹配，反之亦然，例如：‘Intro%’ 匹配以 ‘Intro’ 打头的任意字符串‘%Comp%’ 匹配以 ‘Comp’ ⼦串的任意字符串。‘_ _ _’ 匹配致函三个字符的任意字符串。‘_ _ _ %’ 匹配⾄少含有三个字符的任意字符串。为了使模式能够包含特殊的模式字符 ( 即 % 和 _ ) ，SQL 允许定义转义字符。我们在 like ⽐较运算中使⽤ escape 关键字来定义转义字符。为了说明这⼀⽤法，考虑以下模式，其中使⽤反斜线 ( ) 作为转义字符：like ‘ab%cd%’ escape ‘’ 匹配以 “ab%cd” 开头的所有字符串。like ‘abcd%’ escape ‘’ 匹配以 “abcd” 开头的所有字符串。SQL 允许我们通过使⽤ not like ⽐较运算符来搜索不匹配项。⼀些实现还提供 like 运算的变种，它不区分⼤⼩写。3.4.3 select 字句中的属性说明星号 ” * “ 可以⽤在 select ⼦句中表⽰ ” 所有的属性 “。3.4.4 排列元组的显⽰次序order by ⼦句的可以让查询结果中的元组按排列顺序显⽰，order by 字句按升序列出显⽰项。我们可以⽤ desc 表⽰降序，或⽤ asc表⽰升序。select *from instructororder by salary desc,name asc;3.4.5 ⼦句谓词为了简化 where ⼦句，SQL 提供 between ⽐较运算符来说明⼀个值⼩于或等于某个值，同时⼤于或等于另⼀个值。where salarybetween 90000 and 100000; 可以取代
where salary <= 100000 and salary >= 90000;类似地，我们还可以使⽤ not between。SQL 允许我们使⽤符号 (v1，v2，···，vn) 表⽰⼀个包含值
v1，v2，···，vn 的 n 维元组；该符号被称为⾏构造器。在元组上可以运⽤⽐较运算符，并按字典顺序进⾏⽐较运算。例如，当
a1 <=
b1 且
a2 <=
b2 时，(a1，a2) <= (b1，b2) 为真。3.5 集合运算SQL 作⽤在关系上的 union、intersect 和 except 运算对应于数学集合论中的 ∪ 、 ∩ 、- 运算。3.5.1 并运算(select course_id from section where semester = 'Fall' and year= 2017)union(select course_id from section where semester = 'Spring' and year= 2018);与 select ⼦句不同，union 运算⾃动去除重复。如果我们想保留所有的重复项，就必须⽤ union all 代替 union 。3.5.2 交运算(select course_id from section where semester = 'Fall' and year= 2017)intersect(select course_id from section where semester = 'Spring' and year= 2018);intersect 运算⾃动去除重复。如果我们想保留所有的重复项，就必须⽤ intersect all 代替 intersect 。3.5.3 差运算(select course_id from section where semester = 'Fall' and year= 2017)except(select course_id from section where semester = 'Spring' and year= 2018);except 运算从其第⼀个输⼊中输出不出现在第⼆个输⼊中的所有元组，即执⾏差操作。如果我们想保留所有的重复项，就必须⽤ except all 代替 except 。3.6 空值SQL 将涉及空值的任何⽐较运算的结果视为 unknown（既不是谓词 is null，也不是 is not null）。这创建了除
true 和
false 之外的第三种逻辑值。and：true and
unknown 的结果是
unknown，false and
unknown 的结果是
false， ⽽
unknown and
unknown 的结果是unknown。or：true or
unknown 的结果是
true，false or
unknown 的结果是
unknow， ⽽
unknown or
unknown 的结果是
unknown。not：not
unknow 的结果是
unknown。SQL 在谓词中使⽤特殊的关键字 null 来测试空值。如果谓词 is not null 所作⽤的值⾮空，那么谓词为真。SQL 允许我们通过使⽤ is unknown 和 is not unknown ⼦句来测试⼀个⽐较运算的结果是否为 unknown，⽽不是 true 或 false。当⼀个查询使⽤ select distinct ⼦句时，重复元组必须去除。如果这两个值都⾮空且相等，或者都为空，那么他们被认为是相同的。使⽤ distinct ⼦句时仅保留这样的相同元组的⼀份拷贝。在谓词中 “ null = null ” 会返回 unknown，⽽不是 true。3.7 聚集函数聚集函数 是以值集（集合或多重集合）为输⼊并返回单个值的函数。SQL 提供了五个标准的固有聚集函数。平均值：avg。最⼩值：min。最⼤值：max。总和：sum。计数：count。sum 和 avg 的输⼊必须是数字集，但其他运算符可以作⽤在⾮数字数据类型的集合上，⽐如字符串。3.7.1 基本聚集计算平均值时保留重复项是很重要的。有时在计算聚集函数之前必须先去重，如果确实想去除重复项，可在聚集表达是种使⽤关键字 distinct。我们经常使⽤聚集函数 count 来计算⼀个关系中元组的数量。在 SQL 中该函数的写法是 count(*) 。select count(*)from course;SQL 不允许在⽤ count(*) 时使⽤ distinct。在⽤ max 和 min 时使⽤ distinct 是合法的，尽管结果并⽆差别。3.7.2 分组聚集有时候我们不仅希望将聚集函数作⽤在单个元组集上⽽且还希望将其作⽤在⼀组元组集上；在 SQL 中可使⽤ group by ⼦句实现这个愿望。group by ⼦句中给出的⼀个或多个属性是⽤来构造分组的。在分组⼦句中的属性上取值相同的元组将被分在⼀个组内。select dept_name,avg(salary)asavg_salaryfrom instructorgroup by dept_name;当 SQL 查询使⽤分组时，⼀个很重要的事情是确保出现在 select 语句中但没有被聚集的属性只能出现在 group by ⼦句中的那些属性。换句话说，任何没有出现在 group by ⼦句中的属性如果出现在 select ⼦句中，它只能作为聚集函数的参数，否则这样的查询就是错误的。同时，在 SQL 中⽤ “ /* */ ” 包含⽂本的⽅式编写注释，同样也可以⽤ “ --注释内容 ” 的⽅法。3.7.3 having ⼦句有时候，对分组限定条件⽐对元组限定条件更有⽤。为了针对单个元组进⾏限定，我们可以使⽤ SQL 的 having ⼦句。在 having ⼦句中可以使⽤聚集函数，⽽ where ⼦句中⽆法使⽤聚集函数。select dept_name,avg(salary)as avg_salaryfrom instructorgroup by dept_namehaving avg(salary) > 42000;与 select ⼦句的情况相类似，任何出现在 having ⼦句中，但没有被聚集的属性必须出现在 group by ⼦句中，否则就是查询错误的。包含聚集、group by 或 having ⼦句的查询的含义可通过下述运算序列来定义：与不带聚集的查询情况类似，⾸先根据 from ⼦句来计算出⼀个关系。如果出现了 where ⼦句，where ⼦句中的谓词将应⽤到 from ⼦句的结果关系上。如果出现了 group by ⼦句，满⾜ where 谓词的元组通过 group by ⼦句将被放⼊分组中。如果没有 group by ⼦句，满⾜where 谓词的整个元组集被当成⼀个分组。如果出现了 having ⼦句，它将应⽤到每个分组上；不满⾜ having ⼦句谓词的分组将被去掉。select ⼦句利⽤剩下的分组产⽣查询结果中的元组，记载每个分组上应⽤聚集函数来得到单个结果的元组。3.7.4 对空值和布尔值的聚集SQL 标准并不认为总和本⾝为
null，⽽是认为 sum 运算符应忽略其输⼊的
null 值。空集的 count 运算值为 0 。布尔数据类型，它可以取 true、false 和 unknown 三种值。3.8 嵌套⼦循环SQL 提供嵌套⼦查询机制。⼦查询是嵌套在另⼀个查询中的 select-from-where 表达式。通过将⼦查询嵌套在 where ⼦句中，通常可以⽤⼦循环来执⾏对集合成员资格的测试、对象合法的⽐较以及对集合集数的确定。3.8.1 集合成员资格SQL 允许测试元组在关系中的成员资格。连接词 in 测试集合成员规则，这⾥的集合是由 select ⼦句产⽣的⼀组值构成的。连接词not in 测试集合成员资格的缺失。select distinct course_idfrom sectionwhere sememster = 'Fall' and year= 2017 and course_id in (select course_id from section where semester = 'Spring' and year= 2018);在这⾥我们需要使⽤ distinct，因为 intersect 运算在缺省情况下是去除重复的。3.8.2 集合⽐较“ ⾄少⽐某⼀个要⼤ ” 在 SQL 中⽤ >some 表⽰。select distinct om instructor as T,instructor as Swhere > and _name = 'Bioligy';SQL 也允许
I1。只有最新的 SQL 实现才⽀持 lateral ⼦句。3.8.6 with ⼦句with ⼦句提供了⼀种定义临时关系的⽅式，这个定义只对包含 with ⼦句的查询有效。with max_budgest (value) as (select max(budget) from department)select budgetfrom department,max_budgestwhere = max_;该查询中的 with ⼦句定义了临时关系
max_budget，此关系包含定义了此关系的⼦查询结果元组。此关系只能在同⼀查询的后⾯部分使⽤。我们也能使⽤ from ⼦句或 where ⼦句中的嵌套⼦查询来编写上述查询。但是⽤嵌套⼦查询会是的查询更加晦涩难懂。with ⼦句使查询在逻辑上更加清晰，它还允许在⼀个查询内的多个地⽅使⽤这种临时关系。3.8.7 标量⼦查询SQL 还允许查询出现在返回单个值的表达时能够出现的任何地⽅，只要该⼦查询只返回⼀个包含单个属性的元组；这样的⼦查询称为标量⼦查询。select dept_name, (select count(*) from instructor where _name = _name) as num_instructorsfrom department;上⾯实例中的⼦查询保证只返回单个值，因为它使⽤了不带 group by 的 count(*) 聚集函数。此例也说明了相关变量的使⽤，即使使⽤外层查询的 from ⼦句中的关系的属性。标量⼦查询还可以出现在 select 、where 和 having ⼦句中。也就是不使⽤聚集函数来定义标量⼦查询。在编译时并⾮总能判断⼀个⼦查询返回的结果中是否有多个元组；如果在⼦查询被执⾏后其结果中不⽌⼀个元组，则产⽣⼀个运⾏时错误。从技术上将标量⼦查询的结果类型仍然是关系，尽管其中只包含单个元组。挡在表达式中使⽤标量⼦查询时，他出现的位置是期望单个值出现的地⽅，SQL 就从该关系中包含单属性的单个元组中隐式地去除相应的值，并返回该值。3.8.8 不带 from ⼦句的标量某些查询需要计算，但不需要引⽤任何关系。类似地，某些查询可能有包含 from ⼦句的⼦查询，但⾼层查询不需要 from ⼦句。(select count(*) from teaches) / (select count(*) from instructor);尽管在⼀些系统中这样写是合法的，但其他系统会由于缺少 from ⼦句⽽报错。在后⼀种情况下，可以创建⼀个特殊的虚拟关系，例如创建包含单个元组的
dual 关系。这使得前⾯的查询可以写为：select (select count(*) from teachers) / (select count(*) from instructor)from dual;Oracle 针对上述⽤途提供了⼀个称作
dual 的预定义关系，它包含单个元组（此关系具有单个属性，但这与我们的⽤途⽆关）。上述查询是⽤⼀个整数除以另⼀个整数，在⼤多数据库上，其结果都会是⼀个整数，这就会导致精度缺失。可以将两个⼦查询结果都乘以1.0，将其转换为浮点数。3.9 数据库的修改到⽬前为⽌我们的注意⼒都集中在对数据库信息的抽取上。现在我们将展⽰如何⽤ SQL 来增加、删除或修改信息。3.9.1 删除删除请求的表达⽅式与查询⾮常类似。我们只能删除整个元组，⽽不能只删除某些属性上的值。delete from rwhere P;其中
P 代表⼀个谓词，⽽
r 代表⼀个关系。delete 语句⾸先从
r 中找出使
P(t) 为真的所有元组
t，然后把它们从
r 中删除。where⼦句可以省略，在省略的情况下
r 中的所有元组都将被删除。⼀条 delete 命令只能作⽤于⼀个关系。如果我们想从多个关系中删除元组，必须为每个关系是⽤⼀条 delete 命令。删除⼀个关系中的所有元组，关系仍然存在，只是它变成空的了。虽然我们⼀次只能从⼀个关系中删除元组，但是在 delete 的 where ⼦句中嵌套的 select-from-where 中，可以引⽤任意数量的关系。delete 请求可以包含嵌套的 select，该 select 引⽤待删除元组的关系。3.9.2 插⼊要往关系中插⼊数据，要么指定待插⼊的元组，要么写⼀条查询语句来⽣成待插⼊的元组集合。待插⼊元组的属性值必须在相应属性的域中存在。类似地，带插⼊元组的属性数量也必须是正确的。最简单的 insert 语句是插⼊⼀个元组的请求。插⼊⼀条信息：Computer Science 系开设的名为 “ Database System ” 的课程CS-437 有 4 个学时。insert into course values('CS-437','Database System','',4);为了⽅便那些可能不记得关系属性排列顺序的⽤户，SQL 允许在 insert 语句中指定属性。insert into course (course_id,title,dapt_name,credits) values('CS-437','Database System','',4);insert into course (title,course_id,credits,dapt_name) values('Database System','CS-437',4,'');SQL 还⽀持在查询结果的基础上插⼊元组。例如让 Music 系每个修满 144 学时的学⽣成为 Music 系的教师，⼯资为 18000 美元：insert into instructor
select ID,name,dept_name,18000 from student where dept_name = 'Music' and tot_cred > 144;
如果在插⼊的关系上没有主码约束，这样的请求就可能会插⼊⽆数元组。insert into student select * from student;如果没有主码约束，上述请求会重新插⼊
student 中的第⼀个元组，产⽣该元组的第⼆份拷贝。由于没有这第⼆份拷贝 现在成为
student 的⼀部分，select 语句可能找到它，于是第三份拷贝被插⼊
student 中。这第三份拷贝有可能被 select 语句发现，于是有插⼊第四份拷贝，如此等等，⽆限循环。在执⾏插⼊之前先完成 select 语句的执⾏可以避免这样的问题。这样，如果在
student 关系上没有主码约束，那么上述 insert 语句就只是把
student 关系中的每个元组都复制⼀遍。有可能对待插⼊元组只给出了模式中的某些属性的值，其余属性将被赋空值
null 。⼤多数关系数据库产品都有特殊的 “ bulk loader ” ⼯具，它可以向关系中插⼊⼀个⼤的元组集合。这些⼯具允许从格式化的⽂本⽂件中读出数据，并且它们的执⾏速度⽐等价的插⼊语句序列要快得多。3.9.3 更新在某些情况下，我们可能希望在不改变⼀个元组所有值的情况下改变其某个属性的值，外达到这⼀⽬的，可以使⽤ update 语句。与使⽤ insert 、delete 类似，我们可以通过使⽤查询来选出待更新的元组。假设要进⾏年度⼯资增长，所有教师的⼯资将增长 5%：update instructorset salary= salary * 1.05;上⾯的更新语句将在
instructor 关系的每个元祖上各执⾏⼀次。update 语句的 where ⼦句可以包含 select 语句的 where ⼦句中的任何合法结构。和 insert 、delete 类似，update 语句中嵌套的select 可以引⽤待更新的关系。同样，SQL ⾸先检查关系中的所有元组，看它们是否应该被更新，然后才执⾏更新。SQL 提供 case 结构，我们可以利⽤它在单条 update 语句中执⾏前⾯的更新，以避免更新次序引发的问题，case 语句的⼀般结构如下：case when pred1 then result1 when pred2 then result2 ··· when predn then resultn else result0end当
i 是第⼀个满⾜的
pred1，pred2，···，predn 时，此操作就会返回
resulti ；如果没有⼀个谓词可以满⾜，则运算返回
result0 。case 语句可以⽤在应该出现值的任何地⽅。update instructorset salary = case when salary <= 100000 then salary * 1.05 else salary * 1.03end标量⼦查询在 SQL 中更新语句中⾮常有⽤，他们可以⽤在 set ⼦句中。例如我们想把每个
student 元组的
tot_cred 属性值设为该⽣成功学完的课程学分总和。我们假设如果⼀名学⽣在某门课程上的成绩既不是 ‘ F ’ 也不是空，那么他就完成了这门课程：update studentset tot_cred = ( select sum(credits) from takes,course where = and _id = _id and <> 'F' and is not null);如果⼀名学⽣没有成功学完任何课程，上述语句将把其
tot_cred 属性值置为空。如果想把这样的属性值置为 0 ，我们可以⽤另⼀条update 语句来把空值替换为 0 。更好的替代⽅案是把上述查询中的 “ select sum(credits) ” ⼦句替换为如下使⽤ case 表达式的select ⼦句：select case when sum(credits) is not null then sum(credits) else 0 end许多系统都⽀持 coalesce 函数，我们将在后⾯的 4.5.2 节详细描述该函数，他它提供了⼀种⽤其他只替换
null 的简洁⽅法。在上⾯的⽰例中，我们可以使⽤ coalesce(sum(credits),0) 来代替 case 表达式；该表达式如果不为空，则返回聚集结果 sum(credits)，否则返回 0 。
发布评论