DBMS-SQL：聚集函数、嵌套子查询、数据库修改

SQL

SQL：结构化查询语言，分为以下几个部分。

·数据定义语言（Data-Definition Language, DDL）:SQL DDL提供定义定义关系模式、删除关系以及修改关系模式的命令。

·数据操纵语言（Data-Manipulation Language, DML）：SQL DML包括查询语言，以及在数据库中插入元组、删除元组和修改元组的命令。

·完整性（integrity）：SQL DDL包括定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束，不允许破坏完整性约束的更新。

·视图定义（view definition）：SQL DDL包括定义视图的命令。

·事务控制（transaction control）：SQL包括定义事务的开始和结束的命令。

·嵌入式SQL和动态SQL（embedded SQL and dynamic SQL）：嵌入式和动态SQL定义SQL语句如何嵌入到通用编程语言，如C/C++/Java中。

·授权（authorization）：SQL DDL包括定义对关系和视图的访问权限的命令。

 

SQL数据定义

SQL DDL定义关系及每个关系的信息，包括每个关系的模式、每个属性的名称/取值类型、声明完整性约束、每个关系维护的索引集合、每个关系的安全性和权限信息、每个关系在磁盘上的物理存储结构。

1. 属性的基本取值类型

SQL标准支持多种固有类型，主要包括：

·char(n)：固定长度（用户指定n）的字符串，等价全称character。

存入字符串长度不够时会追加空格使其达到固定长度；

比较长度不同的char类型值时会自动在短值后追加空格使长度一致。

·varchar(n)：可变长度（用户指定最大长度n）的字符串，等价全称character varying。

Varchar类型不会增加空格。

·int：整数类型，等价全称integer。

·smallint：小整数类型。

·numeric(p, d)：精度由用户指定（加上一个符号位有p位数字，其中d位数字在小数点右边）的定点数。

·real, double precision：浮点数与双精度浮点数。

·float(n)：精度至少为n位的浮点数。

SQL也提供nvarchar类型存放使用Unicode表示的多语言数据。

每种类型都可能包含空值，表示一个缺失的值，应尽量避免加入空值。

2. 基本模式定义

1）create table

create table命令定义SQL关系。通用形式为：

create table r
    (A1 D1,
     A2 D2,
           ...,
     An Dn,
    <完整性约束1>
           ...,
      <完整性约束k>);

--r为关系名，Ai为关系r模式中的一个属性名，Di为属性Ai的域

e.g. 创建具有三个属性的关系department，指明dept_name属性是department关系的主码。

create table department
           (dept_name varchar (20),
           building varchar (15),
           budget numeric (12, 2)
           primary key (dept_name));

破坏完整性约束的任何数据库更新将会被标记错误并禁止。SQL支持多种完整性约束，主要包括：

·primary key(A1, A2, …, Am) 表示属性A1, A2, …, Am构成关系的主码。

主码属性必须非空且唯一（声明可选，但不能取空值，关系中没有两个元组在所有主码属性上取值相同）。

·foreign key(A1, A2, …, Am) references s 表示关系中任意元组在属性(A1, A2, …, Am)上的取值必须对应于关系s中某元组在主码属性上的取值。

·not null:（e.g. name varchar (20) not null）表示在该属性上不允许空值。

2）insert

insert命令可将数据加载到关系中。

e.g. 

insert into instructor
           values (10211, 'Smith', 'Biology', 66000);
--值被给出的顺序应遵循对应属性在关系模式中列出的顺序

3）delete

delete命令可从关系中删除元组。

e.g.

delete from student; --从student关系中删除所有元组
delete from r; --保留关系r但删除r中所有元组

4）drop table

drop table命令可从数据库中删除关于被去掉关系的所有信息。

e.g.

drop table r; 
--删除r的所有元组以及r的模式，除非用create table重建r否则没有元组可以插入r中

5）alter table

alter table命令为已有关系增加属性，关系中在所有元组在新属性上的取值将被设为null。

e.g.

alter table r add A D; --为关系r添加域为D的属性A
alter table r drop A; --为关系r中去掉属性A，很多数据库系统并不支持

SQL查询的基本结构

SQL查询可包含三种类型的子句：

·select子句：用于列出查询结果中所需要的属性

·from子句：一个查询求值中需要访问的关系列表（输入）。

·where子句：一个作用在from子句汇总关系的属性上的谓词。

典型SQL查询形式（各子句必须以select、from、where次序写出）：

select A1, A2, …, An
from r1, r2, …, rn
where P;

--Ai代表一个属性，ri代表一个关系，P为一个谓词，如果省略where子句P为true。

一个SQL查询的含义可以理解为：

1）为from子句列出的关系产生笛卡尔积。

2）在步骤1结果上应用where子句中指定的谓词。

3）对步骤2结果中的每个元组，输出select子句汇总指定的属性（或表达式的结果）。

1. 单关系查询

e.g.

select dept_name
from instructor;

SQL查询结果允许会出现重复（默认保留重复），如果需要去除重复可使用dinstinct关键词。

e.g.

select distinct dept_name
from instructor;

如果需要显式指明保留重复，可使用all关键词。

e.g.

select all dept_name
from instructor;

select子句可带含有+-*/运算符的算术表达式，运算对象可以使常数或元组的属性。

e.g. 返回一个与instructor一样的关系，属性salary的值是原来的1.1倍（并不导致对instructor关系的任何改变）

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

where子句允许我们选出那些在from子句的结果关系中满足特定谓词的元组。

e.g.

select name
from instructor
where dept_name = ‘Comp.Sci.’ and salary > 70000;

SQL允许在where子句中使用逻辑连词and、or和not，运算对象可以使包含运算符<、<=、>、>=、=和<>的表达式。

2. 多关系查询

从多个关系中查询信息，需要访问的关系都列在from子句中，并在where子句中指定匹配条件。

e.g.

select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name = department.dept_name;
/*dept_name属性出现在instructor和department关系汇总，关系名被用作前缀来说明使用的是哪个属性*/

3. select *

星号*可用在select子句中表示”所有的属性“，形如select *的select子句表示from子句结果关系的所有属性都被选中。

e.g. 选中instructor关系中的所有属性

select instructor. *
from instructor, teaches
where instructor.ID = teaches.ID;

4. 自然连接（natural join）

自然连接运算作用于两个关系，只考虑那些在两个关系模式中都出现的属性上取值相同的元组对，并产生一个关系作为结果。

结果中那些在两个关系模式中都出现的属性只出现一次，不会重复列出。

列出属性顺序：两个关系中的共同属性-只出现在第一个关系模式中的属性-只出现在第二个关系模式中的属性

e.g.

select name, course_id
from instructor natural join teaches;
--结果等价from instructor, teaches where instructor.ID = teaches.ID;

SQL支持自然连接操作，一个SQL查询的from子句中可以用自然连接将多个关系结合在一起

e.g.

select A1, A2, …, An
from r1 natural join r2 natural join … natural join rm
where P;

所以from子句的一般形式：

from E1, E2, …, En
--Ei为单个关系或一个包含自然连接的表达式

Join…using：允许用户指定需要哪些列相等，给定一个属性名列表，连个输入都必须具有指定名称的属性

e.g.

r1 join r2 using (A1, A2)
/*与r1和r2自然连接类似，但只要在t1.A1=t2.A1并且t1.A2=t2.A2成立的前提下，r1的元组t1和r2的元组t2就能匹配。即使r1和r2都具有名为A3的属性，也不需要考虑。*/

 

SQL附加基本运算

1. 更名运算

SQL提供对属性和关系的重命名

as子句（old-name as new-name）提供重命名结果关系中属性的方法，既可出现在select子句，也可出现在from子句中。

e.g.

select name as instructor_name, course_id
from instructor teaches
where instructor.ID = teaches.ID;

select T.name, S.course_id
from instructor as T, teaches as S --重命名关系
where T.ID = S.ID;  

重命名关系适用于需要比较同一关系中元组的情况

e.g.

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = ‘Biology’; --不能使用instructor.salary的写法，因为引用不清

相关名称（correlation name）：被用来重命名关系的标识符（如T和S），是关系（如instructor）的别名（即关系的拷贝）。也称作表别名（table alias）、相关变量（correlation variable）或元组变量（tuple variable）。

 

2. 字符串运算

SQL使用一对单引号来标示字符串，e.g. ‘Computer’。如果单引号是字符串的组成部分，则用两个单引号字符表示该单引号，e.g.”it’’s right“表示字符串”it’s right“。

SQL标准中字符串的相等运算大小写敏感。但一些数据库系统如MySQL和SQL Server匹配字符串时并不区分大小写，可在数据库级或特定属性级修改这种默认方式。

SQL允许字符串上的多种函数，参考数据库系统手册上所支持字符串函数集的详细信息。

like操作符可在字符串上实现模式匹配，模式由两个特殊字符百分号%（匹配任意子串）和下划线_（匹配任意一个字符）来描述，模式大小写敏感。

e.g.

·’Intro%’ 匹配任何以”Intro”打头的字符串。

·’% Comp%’匹配任何包含“Comp”子串的字符串（如’Intro. To Computer Science’，’Computational Biology’）。

·’___’匹配只含三个字符的字符串。

·’___%’匹配至少含三个字符的字符串。

SQL允许在like运算中使用escape关键字来定义转义字符，直接放在特殊字符（%和_）前面表示该字符被当成普通字符。

e.g. 使用反斜线作为转义字符

like ‘ab%cd%’ escape ‘’ --匹配所有以”ab%cd”开头的字符串
like ‘ab\cd%’ escape ‘’ --撇配所有以”abcd”开头的字符串

 

3. 排列元组显示次序

使用order by子句可让查询结果中元组按排列顺序显示。

e.g. 按字幕顺序列出在Physics系的所有教师

select name
from instructor
where dept_name = ‘Physics’
order by name;

SQL查询结果可按特定属性进行排序。order by子句默认使用升序。如果要说明排序顺序，可用desc表示降序，asc表示升序。排序还可在多个属性上进行。

e.g. 按salary降序列出整个instructor关系，如果有几位教师的工资相同，就按姓名升序排列

select *
from instructor
order by salary desc, name asc;

 

4. where子句谓词

为简化where子句，SQL提供between比较运算符说明一个值位于一个指定闭区间。

e.g. 找出工资在90000美元和100000美元之间的教师的姓名

select name
from instructor
where salary between 90000 and 100000;
--等价where salary<=100000 and salary>=900000;

类似，还可使用not between运算符。

SQL允许使用记号(v1, v2, …, vn)表示一个分量值分别为v1, v2, …, vn的n维元组，在元组上可运用比较运算符按字典顺序进行比较运算。如：(a1, a2) <= (b1, b2)在a1 <=b1且a2 <= b2时为真。

类似，当两个元组在所有属性上相等时，=运算结果为真。

e.g.

select name, corse_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, ‘Biology’);
--等价where instructor.ID = teaches.ID and dept_name = ‘Biology’;

集合运算

SQL作用在关系上的union、intersect和except运算对应于数学集合论中的并、交、差运算。

1. 并运算

e.g. 找出在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代替。

e.g.

(select course_id
 from section
 where semester = ‘Fall’ and year = 2009)
union all
(section course_id
 from section
 where semester = ‘Spring’ and year = 2010);

2. 交运算

e.g. 找出在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代替，结果中出现的重复元组数等于在c1和c2中出现的重复次数最少的那个。

3. 差运算

except运算从其第一个输入中输出所有不出现在第二个输入中的元组（执行集差操作）。

e.g. 找出在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)

except运算自动去除重复，如果想保留所有重复，必须用except all代替，结果中的重复元组数等于在c1中出现的重复元组数减去在c2中出现的重复元组数（前提是此差为正，否则不出现）。

空值

可在谓词中使用关键词null测试空值：

 e.g. 找出instructor关系中salary为空值的所有教师

select name
from instructor
where salary is null;

如果谓词is not null所作用的值非空，则它为真。 

如果算术表达式的任一输入为空，则该算术表达式结果为空。

e.g. 

/*如果tryint实际上为NULL，则结果为NULL，表达式tryint=1无法为真。*/
SELECT * 
FROM testtab
WHERE tryint = 1;

/*如果tryint实际上为NULL，则结果为NULL，表达式tryint!=1也无法为真。*/
SELECT *
FROM testtab
WHERE tryint = 1;

 

Unknown用于处理对包含空值的关系的查询，涉及空值的任何比较运算的结果视为unknown，是除true和false之外第三个逻辑值。布尔运算也可以处理unknown值：

·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，则该元组不能被加入到结果集中。

 可使用子句is unknown和is not unknown测试一个表达式的结果是否为unknown。

 如果两个元组在所有属性上取值相等，那么它们就被当做相同元组，即使某些值为空。

聚集函数

 聚集函数是以值的一个集合（集或多重集）为输入，返回单个值的函数。SQL提供五个固有聚集函数：

 平均值（avg）、最小值（min）、最大值（max）、总和（sum）和计数（count）。

 sum和avg的输入必须是数字集。

1. 基本聚集

e.g. 找出Computer Science系教师的平均工资

select avg (salary) --可用as子句赋名 select avg (salary) as avg_salary
from instructor
where dept_name = ‘Comp.Sci’;
--该查询结果为一个具有单属性、只包含一个元组的关系。

 计算平均值时需注意保留重复元组。如果计算聚集函数前需要先删除重复元组，可在聚集表达式中使用关键词distinct。

 e.g. 找出在2010年春季学期讲授课程的教师总数（不论讲授几个，一个教师只应被计算一次）

select count (distinct ID)
from teaches
where semester = ‘Spring’ and year = 2010;

 聚集函数count常用于计算一个关系中元组的个数。

 e.g. 找出course关系中的元组数

select count(*)
from course;

 SQL不允许在count(*)中使用distinct，允许在max和min中使用distinct（结果无差别）。可使用关键词all替代distinct说明保留重复元组（all为默认）。

 聚集函数对空值的处理原则：除了count(*)外所有的聚集函数都忽略集合中的空值，规定空集的count运算值为0，其他所有聚集运算在输入值集合为空集的情况下返回一个空值。

 聚集函数some和every可用于处理布尔（Boolean）数据类型（可取true、false、unknown三个值）。

  2. 分组聚集

 group by子句用于构造分组，在group by子句指定所有属性上取值相同的元组将被分在一个组中。因此利用group by子句可以把关系进行分组，对每个分组单独作用指定的聚集函数，不使用group by子句时整个关系被当做是一个分组。

 另：SQL还支持在分组上的集合运算。

 e.g. 找出每个系的平均工资

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

e.g. 找出每个系在2010年春季学期讲授一门课程的教师人数 

select dept_name (distinct ID) as instr_count
from instructor natural join teaches
where semester = ‘Spring’ and year = 2010
group by dept_name;

当SQL查询使用分组需要保证出现在select语句中但没有被聚集的属性只能出现在group by子句内部。i.e. 任何没有出现在group by子句中的属性如果出现在select子句中，只能出现在聚集函数内部，否则是错误查询。

3. having子句

 having子句中的谓词在形成分组后才起作用，可表达针对group by子句构成的分组的查询。

e.g. 找出教师平均工资超过42000美元的系

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

与select子句类似，任何出现在having子句中，但没有但没有被聚集的属性必须出现在group by子句内部，否则是错误查询。

包含聚集、group by或having子句的查询的含义可定义为以下操作序列：

 1）根据from子句计算出一个关系。

 2）如果出现where子句，将where子句中的谓词应用到from子句的结果关系上。

 3）如果出现group by子句，满足where谓词的元组通过group by子句形成分组。如果没有group by子句，满足where谓词的整个元组集被当作一个分组。

 4） 如果出现having子句，将having子句应用到每个分组上，不满足having子句谓词的分组将被抛弃。

 5） select子句在剩下的每个分组上应用聚集函数得到单个结果元组，产生查询结果。

 

嵌套子查询

 子查询是嵌套在另一个查询中的select-from-where表达式，SQL支持在外层查询的where和from子句中嵌套子查询，并且在一个表达式返回的单个值所允许出现的任何地方支持标量子查询。

 a. where子句中的嵌套子查询

 1. 使用嵌套子查询检查集合成员资格【where子句，in和not in】

 连接词in测试元组是否是集合中的成员，not in测试元组是否不是集合中的成员。集合指由where子句中嵌套的子查询中的select子句产生的一组值（子查询结果）

 e.g. 从2009年春季开课的所有课程集合（子查询结果）中找出那些在2009年秋季开课的课程

--等价于在2009年秋季和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);

e.g. 找出所有在2009年秋季开课但不在2010年春季开课的课程

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

e.g. 找出（不同的）学生总数，他们选修了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);

in和not in操作符也可用于枚举集合。

e.g. 找出既不叫”Mozart”, 也不叫”Einstein”的教师的姓名

select distinct name
from instructor
where name not in (‘Mozart’, ‘Einstein’);

2. 使用嵌套子查询进行集合的比较【where子句 some和all】

SQL中可用>some表示短语“至少比某一个要大”。同样，SQL也允许<some, <=some, >=some, =some和<>some。

=some等价于in，<>并不等价于not in。

类似于some，SQL也允许<all, <=all, >=all, =all和<>all。

<>all等价于not in，但=all并不等价于in。

e.g. 找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高

select name
from instructor
where salary > some (select salary
                                 from instructor
                                 where dept_name = ‘Biology’);

e.g. 找出平均工资最高的系

select dept_name
from instructor
group by dept_name
having avg (salary) >= all (select avg (salary)
                  from instructor
                  group by dept_name);

3. 使用嵌套子查询进行空关系测试【where子句 exists和not exists】

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

Not exists结构可模拟集合包含（超集）操作：’关系A包含关系B’可写作”not exists (B except A)”。

e.g. 找出在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);

e.g. 找出选修了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.couse_id
               from takes as T
               where S.ID = T.ID ) );

相关子查询（correlated subquery）：SQL中，来自外层查询的一个相关名称（如S）可用在where子句子查询中，使用了来自外层查询相关名称的子查询称为相关子查询。

4. 使用嵌套子查询进行重复元组存在性测试【where子句 unique和not unique】

unique结构在作为参数的子查询结果中没有重复的元组时返回ture，当且仅当在关系中存在着两个元组t1和t2且t1 = t2时对一个关系的unique测试结果为假。

由于在t1或t2的某个域为空时，判断t1 = t2为假，所以尽管一个元组有多个副本，只要该元组有一个属性为空，unique测试就有可能为真。

使用unique或not unique结构可测试在一个子查询的结果中是否存在重复元组。

e.g. 找出所有在2009年最多开设一次的课程

/*如果某门课程不在2009年开设，子查询会返回一个空的结果，unique谓词在空集上计算出真值*/
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);

e.g. 找出所有在2009年最少开设两次的课程

select T.course_id
from course as T
where not unique ( select R.course_id
              from section as R
              where T.course_id = R.course_id and
                   R.year = 2009);

b. from子句中的嵌套子查询

e.g. 找出系平均工资超过42000美元的那些系中教师的平均工资（from子句嵌套子查询代替having子句）

/*from子句中的子查询计算出了每个系的平均工资*/
select dept_name, avg_salary
from (select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name)
where avg_salary > 42000;
/*可用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 avg_salary > 42000;

e.g. 找出在所有系中工资总额最大的系

select max (tot_salary)
from (select dept_name, sum(salary)
    from instructor
    group by dept_name) as dept_total (dept_name, tot_salary);

from子句嵌套的子查询不能使用来自外层查询的相关变量。SQL: 2003允许使用lateral关键词作为前缀以便访问from子句其他关系的相关变量。

e.g. 打印每位教师的姓名以及他们的工资和所在系的平均工资

select name, salary, avg_salary
from instructor I1, lateral (select avg(salary) as avg_salary
                   from instructor I2
                   where I2. dept_name = I1.dept_name);

c. with子句嵌套子查询

with子句提供定义临时关系的方法，该临时定义只对包含with子句的查询有效。

e.g. 找出具有最大预算值的系

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

e.g. 找出所有工资总额大于所有系平均工资总额的系

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;

标量子查询

SQL允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回包含单个属性的单个元组，这样的子查询成为标量子查询（scalar subquery）。

标量子查询的结果类型原则上仍然是关系，但标量子查询出现在表达式中单个值出现的地方，SQL就从该关系中包含单属性的单元组中去除其包含的单个元组的值，并返回该值。

标量子查询可以出现在select、where和having子句中，如果在标量子查询被执行后其结果中不止有一个元组，则产生运行错误。

e.g. 列出所有的系以及它们拥有的教师数

select dept_name,
( select count( * )
 from instructor
 where department.dept_name = instructor.dept_name)
 as num_instructors
from department;

数据库的修改

删除

delete from r
where P;
/*P代表一个谓词，r代表一个关系。Delete语句首先从r中找出所有时P(t)为真的元组t，然后把它们从r中删除。*/

如果省略where子句，则r中所有元组都被删除。（关系本身仍然存在，但变为空。）

SQL只能删除整个元组，而不能只删除某些属性上的值。           

Delete命令只能作用于一个关系，如果想从多个关系中删除元组，必须在每个关系上使用一条delete命令。

e.g. 从instructor关系中删除与Finance系教师相关的所有元组

delete from instructor
where dept_name = ‘Finance’;

e.g. 删除所有工资在13000美元到15000美元之间的教师

delete from instructor
where salary between 13000 and 15000;

注意delete结果依赖于元组被处理的顺序。如执行删除前一般应该先进行所有元祖测试，如果有些元组在其余元组未被测试前仙贝删除，平均值可能发生改变。

e.g. 删除工资低于大学平均工资的教师记录

/*该delete语句首先测试instructor关系中的每一个元组，然后删除所有符合条件的元组*/
delete from instructor
where salary < (select avg (salary)
            from instructor);

插入

insert语句插入元组时，带插入元组的属性值必须在相应属性域中，待插入元组的分量数必须正确。

待插入元组属性值的排列顺序和关系模式汇总的属性排列顺序应当一致。

e.g 

insert into course
    values(‘CS-437’, ‘Database Systems’, ‘Comp.Sci.’, 4);

SQL也允许在insert语句中指定属性。

e.g. 与前例功能相同的insert语句

insert into course(course_id, title, dept_name, credits)
    values(‘CS-437’, ‘Database Systems’, ‘Comp.Sci.’, 4);
insert into course(title, course_id, credits, dept_name)
    values(‘Database Systems’, ;CS-437, 4, ‘Comp.Sci.’);

如果待插入元组中只给出了模式中部分属性的值，其余属性将被赋空值（null）。

e.g. 插入一个在Finance系、ID为3003的学生的元组，其tot_cred值未知

insert into student
    values(‘3003’, ‘Green’, ‘Finance’, null);

可用select选出一个元组集合，在查询结果基础上插入元组。

e.g. 让Music系每隔修满144学分的学生成为Music系教师，其工资为18000美元

insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = ‘Music’ and tot_cred > 144;

注意：在执行插入之前应先执行完select语句。如果在执行select语句的同时执行插入操作，可能会插入无数元组。

e.g. 如果student上没有主码约束该请求可能会插入无数元组

insert into student
select *
from student;

更新

使用update语句可在不改变整个元组的情况下改变其部分属性的值。

e.g. 所有教师的工资增长5%（更新语句将在instructor关系的每个元组上执行一次）

update instructor
set salary = salary*1.05;

e.g. 只给那些工资低于70000美元的教师涨工资

update instructor
set salary = salary*1.05
where salary < 70000;

update语句中嵌套的select可以引用待更新的关系，SQL首先检查关系中所有元组，看它们是否应该被更新，然后才执行更新。

e.g. 对工资低于平均数的教师涨5%工资

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

可利用SQL提供的case结构在一条update语句汇总执行多种更新，避免更新次序引发的问题。Case语句可用在任何应该出现该值的地方。

Case语句一般格式：（当i第一个满足时，此操作就会返回result，如果没有一个谓词可以满足则返回result0。）

case
    when pred1 then result1
    when pred2 then result2
    …
    when predn then result
    else result0
end

e.g. 给工资超过100000美元的教师涨3%的工资，其余教师涨5%的工资

update instructor
set salary = case
              when salary <= 100000 then salary*1.05
              else salary*1.03
    end

标量子查询常用于SQL更新语句的set子句中。

e.g. 把每隔student元组的tot_cred属性设为该学生成功学完的课程学分的总和。假设如果一个学生在某门课程上的成绩既不是’F’也不是空，那么他成功学完了这门课程。

update student S
set tot_cred = (
        select sum(credits)
        from takes natural join course
        where S.ID = takes.ID and
        takes.grade <> ‘F’ and
        takes.grade is not null);