数据库原理 | 3. 关系数据库标准语言SQL

思维导图

• 关系数据库标准语言SQL
  • SQL概述
    • SQL的特点
  • 数据定义
    • 创建基本表
    • 删除基本表
    • 修改基本表
  • 查询
    • 概述
    • 单表查询
      • 查询所有字段
      • 查询指定列
      • 查询满足条件的元组
        • 比较大小
        • 确定范围
        • 确定集合
        • 字符匹配
        • 空值
        • 多重条件
      • 对查询结果排序
      • 使用集函数
      • 对查询结果分组
        • 使用HAVING短语筛选最终输出结果
      • 限制查询结果数量
    • 连接查询
      • 广义笛卡尔积
      • 等值与非等值连接查询
        • 等值连接
        • 自然连接
        • 自身连接
      • 外连接
    • 嵌套查询
      • 嵌套查询概述
      • 嵌套查询分类及求解方法
        • 不相关子查询
        • 相关子查询
      • 引出子查询的谓词
        • 带有IN谓词的子查询
        • 带有比较运算符的子查询
          • 带有EXISTS谓词的子查询
    • 集合查询
      • 并操作
      • 交操作
      • 差操作
      • 对集合操作结果的排序
  • 数据更新
    • 插入数据
      • 插入单个元组
      • 插入子查询结果
    • 修改数据
      • 修改某一个元组的值
      • 修改多个元组的值
      • 带子查询的修改语句
    • 删除数据
      • 删除某一个元组的值
      • 删除多个元组的值
      • 带子查询的删除语句
  • 视图
    • 定义视图
    • 删除视图
    • 查询视图
    • 更新视图
      • 更新视图的限制
    • 视图的作用
      • 简化用户的操作
      • 使用户能以多种角度看待同一数据
      • 对重构数据库提供了一定程度的逻辑独立性
      • 对机密数据提供安全保护

SQL概述

• SQL是英文（Structured Query Language）的缩写，意思为结构化查询语言，它包括了数据定义、查询、操纵和控制四种功能。
• 本章介绍了SQL语言的一些基本操作命令，包括数据定义语句（DDL）、数据操纵语句（DML）及权限的操作，重点和难点是数据操纵语句中的查询操作。

SQL的特点

1. 综合统一
2. 高度非过程化
3. 面向集合的操作方式
4. 以同一种语法结构提供两种使用方法
5. 语言简洁，易学易用

数据定义

创建基本表

CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
      [,<列名> <数据类型>[ <列级完整性约束条件>] ] …      [,<表级完整性约束条件> ]);

• <表名>：所要定义的基本表的名字
• <列名>：组成该表的各个属性（列）
• <列级完整性约束条件>：涉及相应属性列的完整性约束条件
• <表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

示例

CREATE TABLE BookRecord (
  BookNo  char(30) not null UNIQUE,
  BookName char(200),
  Publisher Char(100),
  Author   Char(30),
  SalesPrice  Money,
  Quantity  Int,
  Discount  Decimal(2,2),
  PRIMARY KEY(BookNo),
  FOREIGN KEY(ID) references Book(ID))

• 其中NOT NULL指的是该列的值不能为空值。
• UNIQUE 指的是不能重复。
• PRIMARY KEY是主码。
• FOREIGN KEY……references是外码。

删除基本表

DROP TABLE <表名> CASCADE/RESTRICT;

• 基本表删除。数据、表上的索引都删除表上的视图往往仍然保留，但无法引用。
• 删除基本表时，系统会从数据字典中删去有关该基本表及其索引的描述

修改基本表

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY/ALTER COLUMN <列名> <数据类型> ];

• <表名>：要修改的基本表
• ADD子句：增加新列和新的完整性约束条件
• DROP子句：删除指定的完整性约束条件
• MODIFY子句：用于修改列名和数据类型

【例】 向BookRecord表增加一个“出版日期”的PublishiDate列。

ALTER TABLE BookRecord ADD PublishiDate datetime NULL;

不论基本表中原来是否已有数据，新增加的列一律为空值。

【例】 将折扣的数据类型改为整数

ALTER TABLE BookRecord MODIFY Discount int;

修改原有的列定义有可能会破坏已有数据。

【例】 删除字段“出版日期” PublishiDate列

ALTER TABLE BookRecord DROP COLUMN PublishiDate;

【例】 删除关于书号必须取唯一值的约束

ALTER TABLE BookRecord DROP   UNIQUE(BookNo);

查询

概述

SELECT [ALL|DISTINCT] <目标列表达式>
                                                [，<目标列表达式>] …
FROM <表名或视图名>[, <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];


SELECT  [ All | DINSTINCT]  <目标列表达式> [别名]  [ , <目标列表达式> [别      名] ]  . . .
FROM <表名或视图名>  [别名] [ , <表名或视图名>[别名] ] . . .
[WHERE <条件表达式>]
[GROUP BY <列名1> ]
[HAVING <条件表达式> ]
[ORDER BY <列名2> [ ASC| DESC ]]
[LIMIT 字句]

select [straight_join][sql_small_result][sql_big_result
[high_priority]
[ distinct | distinctrow | all ]
select_expression, . . .
[ into {outfile | dumpfile} ‘file_name’ export_options ]
[ from table_references
[ where where_definition]
[ group by col_name, . . . ]
[ having where_definition]
[ order by { unsigned_integer | col_name | formula } [ ASC | DESC] , . . . ]
[limit [ offset, ] rows]
[procedure procedure_name] ]

• SELECT子句：指定要显示的属性列
• FROM子句：指定查询对象(基本表或视图)
• WHERE子句：指定查询条件
• GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。
• HAVING短语：筛选出只有满足指定条件的组
• ORDER BY子句：对查询结果表按指定列值的升序或降序排序
• SELECT子句的<目标列表达式>为表达式
  • 算术表达式
  • 字符串常量
  • 函数
  • 列别名
  • $\cdots$

单表查询

查询仅涉及一个表，是一种最简单的查询操作

1. 选择表中的若干列
2. 选择表中的若干元组
3. 对查询结果排序
4. 使用集函数
5. 对查询结果分组

查询所有字段

查询所有字段是指查询表中的所有字段的数据，有两种方式：一种是列出表中的所有字段，另一种是使用通配符*来查询。

技巧：通过使用通配符*，可以查询表中所有字段的数据，这种方式比较简单，尤其是数据库表中的字段很多时，这种方式更加明显。但是从显示结果顺序的角度 来讲，使用通配符*不够灵活。如果要改变显示字段的顺序，可以选择使用第一种方式。

查询指定列

[例] 查询全体学生的学号与姓名。

SELECT Sno,Sname
FROM Student;

[例] 查询全体学生的姓名、学号、所在系。

SELECT Sname,Sno,Sdept
FROM Student;

查询满足条件的元组

比较大小

在WHERE子句的<比较条件>中使用比较运算符

• =,>,<,>=,<=,!= 或 <>,!>,!<,
• 逻辑运算符NOT + 比较运算符

[例] 查询所有年龄在20岁以下的学生姓名及其年龄。

SELECT Sname,Sage
FROM    Student
WHERE Sage < 20;

或

SELECT Sname,Sage
FROM    Student
WHERE NOT Sage >= 20;

确定范围

使用谓词BETWEEN … AND … NOT BETWEEN … AND …

[例] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECT Sname,Sdept,Sage
FROM   Student
WHERE Sage BETWEEN 20 AND 23;

确定集合

• 使用谓词IN <值表>, NOT IN <值表>
• <值表>：用逗号分隔的一组取值

[例]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECT Sname,Ssex
FROM  Student
WHERE Sdept IN ('IS','MA','CS');

字符匹配

[NOT] LIKE  ‘<匹配串>’  [ESCAPE ‘<换码字符>’]

• <匹配串>：指定匹配模板
• 匹配模板：固定字符串或含通配符的字符串。当匹配模板为固定字符串时，可以用= 运算符取代 LIKE 谓词,用 != 或 < >运算符取代 NOT LIKE 谓词

使用LIKE关键字和使用“＝”的效果是一样的。但是，这只对匹配一个完整的字符串这种情况有效。如果字符串中包含了通配符，就不能这样进行替换了。

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	不在字符列中

[例] 查询所有不姓刘的学生姓名、学号和性别。

SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';

空值

• 使用谓词 IS NULL 或 IS NOT NULL
  • IS NULL 不能用 = NULL 代替

[例] 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;

多重条件

• 用逻辑运算符AND和OR来联结多个查询条件
  • AND的优先级高于OR
  • 可以用括号改变优先级
• 可用来实现多种其他谓词
  • [NOT] IN
  • [NOT] BETWEEN … AND …

[例] 查询计算机系年龄在20岁以下的学生姓名。

SELECT Sname
FROM  Student
WHERE Sdept= ‘CS’ AND Sage<20;

[例] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECT Sname,Sdept,Sage
FROM Student
WHERE  Sage>=20 AND Sage<=23;

对查询结果排序

• 使用ORDER BY子句
  • 可以按一个或多个属性列排序
  • 升序：ASC；降序：DESC；缺省值为升序
• 当排序列含空值时
  • ASC：排序列为空值的元组最后显示
  • DESC：排序列为空值的元组最先显示

[例] 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECT Sno,Grade
FROM  SC
WHERE Cno = '3'
ORDER BY Grade DESC;

Sno	Grade
95010
95024
95007	92
95003	82
95010	82
95009	75
95014	61
95002	55

[例] 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT  *
FROM  Student
ORDER BY Sdept,Sage DESC;

使用集函数

• 5类主要集函数
• 计数
  • COUNT([DISTINCT|ALL] *)
  • COUNT([DISTINCT|ALL] <列名>)
    • DISTINCT取消重复值，ALL是全部值。缺省是ALL。
• 计算总和
  • SUM([DISTINCT|ALL] <列名>)
• 计算平均值
  • AVG([DISTINCT|ALL] <列名>)
• 求最大值
  • MAX([DISTINCT|ALL] <列名>)
• 求最小值
  • MIN([DISTINCT|ALL] <列名>)

对查询结果分组

使用GROUP BY子句分组
细化集函数的作用对象

• 未对查询结果分组，集函数将作用于整个查询结果
• 对查询结果分组后，集函数将分别作用于每个组

[例] 求各个课程号及相应的选课人数。

SELECT Cno,COUNT(Sno)
FROM    SC
GROUP BY Cno;

结果

Cno	COUNT(Sno)
1	22
2	34
3	44
4	33
5	48

使用HAVING短语筛选最终输出结果

• 只有满足HAVING短语指定条件的组才输出
• HAVING短语与WHERE子句的区别：作用对象不同
  • WHERE子句作用于基表或视图，从中选择满足条件的元组。
  • HAVING短语作用于组，从中选择满足条件的组。

[例] 查询选修了3门以上课程的学生学号。

SELECT Sno
FROM  SC
GROUP BY Sno
HAVING  COUNT(*) >3;

[例] 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。

SELECT Sno,COUNT(*)
FROM SC
WHERE Grade>=90
GROUP BY Sno
HAVING COUNT(*)>=3;

限制查询结果数量

当使用SELECT语句返回的结果集中行数很多时，为了便于用户对结果数据的浏览和操作，可以使用LIMIT子句来限制被SELECT语句返回的行数。
语法格式：

LIMIT {[offset,] row_count | row_count OFFSET offset}

其中，offset为可选项，默认为数字0，用于指定返回数据的第一行在SELECT语句结果集中的偏移量，其必须是非负的整数常量。注意，SELECT语句结果集中第一行（初始行）的偏移量为0而不是1。row_count用于指定返回数据的行数，其也必须是非负的整数常量。若这个指定行数大于实际能返回的行数时，MySQL将只返回它能返回的数据行。
row_count OFFSET offset是MySQL5.0开始支持的另外一种语法，即从第offset+1行开始，取row_count行。

例：在student表中查找前3位同学的信息(按学号从小到大排序）。

SELECT * FROM STUDENT ORDER BY sno LIMIT 3;

例：在student表中查找从第3位同学开始的3位学生的信息。

SELECT * FROM student ORDER BY sno LIMIT 2,3;

连接查询

• 同时涉及多个表的查询称为连接查询
• 用来连接两个表的条件称为连接条件或连接谓词
• 一般格式：
• [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
  • 比较运算符：=、>、<、>=、<=、!=
• [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
• 连接字段
  • 连接谓词中的列名称为连接字段
  • 连接条件中的各连接字段类型必须是可比的，但不必是相同的
• SQL中连接查询的主要类型
  • 广义笛卡尔积
  • 等值连接(含自然连接)
  • 非等值连接查询
  • 自身连接查询
  • 外连接查询
  • 复合条件连接查询

广义笛卡尔积

• 不带连接谓词的连接
• 很少使用

例：

SELECT  Student.* ,SC.*
FROM     Student, SC;

等值与非等值连接查询

等值连接、自然连接、非等值连接

[例] 查询每个学生及其选修课程的情况。

SELECT  Student.*,SC.*
FROM     Student,SC
WHERE  Student.Sno = SC.Sno;

等值连接

• 连接运算符为 = 的连接操作
  • [<表名1>.]<列名1> = [<表名2>.]<列名2>
  • 任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。

自然连接

等值连接的一种特殊情况，把目标列中重复的属性列去掉。

[例] 对上例中查询条件用自然连接完成。

SELECT  Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM    Student,SC
WHERE  Student.Sno = SC.Sno;

自身连接

• 一个表与其自己进行连接，称为表的自身连接
• 需要给表起别名以示区别
• 由于所有属性名都是同名属性，因此必须使用别名前缀

[例] 查询每一门课的间接先修课（即先修课的先修课）

SELECT  FIRST.Cno,SECOND.Cpno
FROM  Course  FIRST,Course  SECOND
WHERE FIRST.Cpno = SECOND.Cno;

FIRST和SECOND表

Cno	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	PASCAL语言	6	4

结果

Cno	Cpno
1	7
3	5
5	6

外连接

• 外连接与普通连接的区别
  • 普通连接操作只输出满足连接条件的元组
  • 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出

[例] 查询每个学生及其选修课程的情况包括没有选修课程的学生——用外连接操作

SELECT  Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM    Student, SC
WHERE  Student.Sno = SC.Sno(*);
from  表1 left out join 表2 on (正常连接条件)
               right

• 在表名后面加外连接操作符(*)或(+)指定非主体表
• 非主体表有一“万能”的虚行，该行全部由空值组成
• 虚行可以和主体表中所有不满足连接条件的元组进行连接
• 由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值
• 左外连接
  • 外连接符出现在连接条件的左边
• 右外连接
  • 外连接符出现在连接条件的右边

嵌套查询

嵌套查询概述

SELECT Sname    //外层查询/父查询
FROM Student
WHERE Sno IN
(SELECT Sno     //内层查询/子查询
FROM SC
WHERE Cno= ' 2 ');

• 子查询的限制
  • 不能使用ORDER BY子句
• 层层嵌套方式反映了 SQL语言的结构化
• 有些嵌套查询可以用连接运算替代
• 不相关子查询
  • 子查询的查询条件不依赖于父查询
• 相关子查询
  • 子查询的查询条件依赖于父查询

嵌套查询分类及求解方法

不相关子查询

是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

相关子查询

首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。

引出子查询的谓词

带有IN谓词的子查询

[例] 查询与“刘晨”在同一个系学习的学生。

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept  IN
>(SELECT Sdept
FROM Student
WHERE Sname= ‘ 刘晨 ’);

此查询为不相关子查询。DBMS求解该查询时也是分步去做的。
父查询和子查询中的表均可以定义别名，把两个FROM的参数改成Student S1/S2。

[例]查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Sno,Sname
FROM    Student
WHERE Sno  IN
         (SELECT Sno
          FROM    SC
          WHERE  Cno IN
              (SELECT Cno
              FROM Course
              WHERE Cname= ‘信息系统’));

上述过程也可以用连接查询。

SELECT Sno,Sname
     FROM    Student,SC,Course
     WHERE Student.Sno = SC.Sno  AND
                     SC.Cno = Course.Cno AND
                     Course.Cname=‘信息系统’;

带有比较运算符的子查询

• 当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。
• 与ANY或ALL谓词配合使用
  • ANY：任意一个值
  • ALL：所有值

例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在上例中可以用 = 代替IN ：

SELECT Sno,Sname,Sdept
FROM    Student
WHERE Sdept =(
SELECT Sdept
FROM   Student
WHERE Sname= '刘晨');

组合	效果
=ANY	和IN等价
<>ALL	和NOT IN等价
>ANY	大于最小的(>MIN)
<ANY	小于最大的(<MAX)
>ALL	大于最大的(>MAX)
<ALL	小于最小的(<MIN)
=ALL	很少使用，若返回值唯一，则和“=”相等，若不唯一，结果为空

表格来源

带有EXISTS谓词的子查询

• EXISTS谓词
  • 存在量词 $\exists$
  • 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
    • 若内层查询结果非空，则返回真值
    • 若内层查询结果为空，则返回假值
  • 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
• NOT EXISTS谓词

[例] 用嵌套查询查询所有选修了1号课程的学生姓名。

SELECT Sname
FROM Student
WHERE EXISTS
(SELECT *
FROM SC          /*相关子查询*/
WHERE Sno=Student.Sno AND  Cno= ' 1 ');

思路分析：
本查询涉及Student和SC关系。
在Student中依次取每个元组的Sno值，用此值去检查SC关系。
若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= ‘1’，则取此Student.Sname送入结果关系。

[例] 查询没有选修1号课程的学生姓名。

SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno = Student.Sno  AND Cno='1');
//此例用连接运算难于实现

• 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
• 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
• 用EXISTS/NOT EXISTS实现全称量词(难点)
  • SQL语言中没有全称量词$\forall$ （For all）
  • 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：
  • $(\forall x) P \equiv \neg(\exists x(\neg P))$
• 用EXISTS/NOT EXISTS实现逻辑蕴函(难点)
  • SQL语言中没有蕴函(Implication)逻辑运算
  • 可以利用谓词演算将逻辑蕴函谓词等价转换为：
  • $p\rightarrow q \equiv \neg p \vee q$

集合查询

• 标准SQL直接支持的集合操作种类
  • 并操作(UNION)
• 一般商用数据库支持的集合操作种类
  • 并操作(UNION)
  • 交操作(INTERSECT)
  • 差操作(MINUS)

并操作

<查询块>
UNION
<查询块>

参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同。
等价于：

SELECT  DISTINCT  *
FROM Student
WHERE Sdept= 'CS'  OR  Sage<=19;

[例] 查询选修了课程1或者选修了课程2的学生。

SELECT Sno
FROM SC
WHERE Cno=' 1 '
UNION
SELECT Sno
FROM SC
WHERE Cno= ' 2 ';

等价于：

SELECT  DISTINCT  Sno
FROM SC
WHERE Cno=' 1 '  OR  Cno= ' 2 ';

交操作

标准SQL中用INTERSECT实现交操作，也可用其他方法间接实现。

[例] 查询计算机科学系的学生与年龄不大于19岁的学生的交集
本例实际上就是查询计算机科学系中年龄不大于19岁的学生

SELECT *
FROM Student
WHERE Sdept= 'CS' AND Sage<=19;

差操作

标准SQL中用EXCEPT实现差操作，也可用其他方法间接实现。

[例] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。
本例实际上是查询计算机科学系中年龄大于19岁的学生

SELECT *
FROM Student
WHERE Sdept= 'CS' AND
Sage>19;

对集合操作结果的排序

• ORDER BY子句只能用于对最终查询结果排序，不能对中间结果排序
• 任何情况下，ORDER BY子句只能出现在最后
• 对集合操作结果排序时，ORDER BY子句中用数字指定排序属性

数据更新

插入数据

插入单个元组

INSERT
INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>]    …           )

功能：将新元组插入指定表中。

[例] 将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。

INSERT
INTO Student
VALUES ('95020','陈冬','男','IS',18);

插入子查询结果

INSERT
INTO <表名>  [(<属性列1> [,<属性列2>…  )]
子查询;

功能：将子查询结果插入指定表中。

[例] 对每一个系，求学生的平均年龄，并把结果存入数据库。

第一步：建表

CREATE  TABLE  Deptage
(Sdept  CHAR(15)           /* 系名*/
Avgage SMALLINT);  /*学生平均年龄*/

第二步：插入数据

INSERT
INTO  Deptage(Sdept,Avgage)
SELECT  Sdept,AVG(Sage)
FROM  Student
GROUP BY Sdept；

修改数据

UPDATE  <表名>
SET  <列名>=<表达式>[，<列名>=<表达式>]…
[WHERE <条件>]；

功能：修改指定表中满足WHERE子句条件的元组

修改某一个元组的值

[例] 将学生95001的年龄改为22岁。

UPDATE  Student
SET Sage=22
WHERE  Sno=' 95001 ';

修改多个元组的值

[例] 将所有学生的年龄增加1岁。

UPDATE Student
SET Sage= Sage+1;

带子查询的修改语句

[例] 将计算机科学系全体学生的成绩置100。

UPDATE SC
SET  Grade=100
WHERE  'CS'=
(SELETE Sdept
FROM  Student
WHERE  Student.Sno = SC.Sno);

删除数据

DELETE
FROM   <表名>
[WHERE <条件>]；

功能：删除指定表中满足WHERE子句条件的元组

WHERE子句：

• 指定要删除的元组
• 缺省表示要修改表中的所有元组

删除某一个元组的值

[例] 删除学号为95019的学生记录。

DELETE
FROM Student
WHERE Sno='95019';

删除多个元组的值

[例] 删除2号课程的所有选课记录。

DELETE
FROM SC；
WHERE Cno='2';

[例] 删除所有的学生选课记录。

DELETE
FROM SC;

带子查询的删除语句

[例] 删除计算机科学系所有学生的选课记录。

DELETE
FROM SC
WHERE  'CS'=
(SELETE Sdept
FROM Student
WHERE Student.Sno=SC.Sno);

视图

• 视图的特点
  • 虚表，是从一个或几个基本表（或视图）导出的表
  • 只存放视图的定义，不会出现数据冗余
  • 基表中的数据发生变化，从视图中查询出的数据也随之改变
• 基于视图的操作
  • 定义
  • 查询
  • 删除
  • 受限更新
  • 定义基于该视图的新视图

定义视图

CREATE  VIEW
<视图名>  [(<列名>  [,<列名>]…)]
AS  <子查询>
[WITH  CHECK  OPTION];

[例] 建立信息系学生的视图。

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM   Student
WHERE  Sdept= 'IS';

从单个基本表导出，只是去掉了基本表的某些行和某些列，保留了码。把这样的视图叫做行列子集视图。

• WITH CHECK OPTION
  • 透过视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）

[例] 建立信息系选修了1号课程的学生视图。这是一个基于多个基表的视图。

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM  Student,SC
WHERE  Sdept= 'IS' AND
Student.Sno=SC.Sno AND
SC.Cno= '1';

[例] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。这是一个基于视图的视图。

CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM  IS_S1
WHERE  Grade>=90;

[例] 定义一个反映学生出生年份的视图。这是一个带表达式的视图。

CREATE  VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2020-Sage  //2020-Sage就等于出生年份
FROM  Student;

设置一些派生属性列, 也称为虚拟列——Sbirth，带表达式的视图必须明确定义组成视图的各个属性列名

[例] 将学生的学号及他的平均成绩定义为一个视图。这是一个分组视图。
假设SC表中“成绩”列Grade为数字型

CREAT  VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM  SC
GROUP BY Sno;

删除视图

• DROP VIEW <视图名>；
  • 该语句从数据字典中删除指定的视图定义
  • 由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除
  • 删除基表时，由该基表导出的所有视图定义都必须显式删除

查询视图

• 从用户角度：查询视图与查询基本表相同
• DBMS实现视图查询的方法
  • 实体化视图（View Materialization）
    • 有效性检查：检查所查询的视图是否存在
    • 执行视图定义，将视图临时实体化，生成临时表
    • 查询视图转换为查询临时表
    • 查询完毕删除被实体化的视图(临时表)

[例] 在信息系学生的视图中找出年龄小于20岁的学生。

SELECT   Sno,Sage
FROM      IS_Student
WHERE   Sage<20;

IS_Student视图的定义 (视图定义例1)：

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM  Student
WHERE  Sdept= 'IS';

[例] 查询信息系选修了1号课程的学生

SELECT Sno,Sname
FROM   IS_Student,SC
WHERE  IS_Student.Sno = SC.Sno AND SC.Cno = '1';

更新视图

• 用户角度：更新视图与更新基本表相同
• DBMS实现视图更新的方法
  • 视图实体化法（View Materialization）
    • 先实例化视图，也就是从基本表中查出一个临时表，然后再根据查询条件在临时表中查找数据。
  • 视图消解法（View Resolution）
    • 将查询语句与视图的定义相结合，生成对基本表的查询语句，再到基本表中查出数据。来源
• 指定WITH CHECK OPTION子句后
  • DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新

[例] 将信息系学生视图IS_Student中学号95002 的学生姓名改为“刘辰”。

UPDATE  IS_Student
SET  Sname= '刘辰'
WHERE  Sno= '95002';

转换后的语句：

UPDATE  Student
SET Sname= '刘辰'
WHERE Sno= '95002' AND Sdept= 'IS';
//因为在定义视图时，有Sdept= 'IS'语句

更新视图的限制

一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新(对两类方法均如此)

例：视图S_G为不可更新视图。

CREATE VIEW S_G (Sno,Gavg)
AS
SELECT  Sno,AVG(Grade)
FROM    SC
GROUP BY Sno;
//不能直接修改平均分

对于如下更新语句：

UPDATE S_G
SET    Gavg=90
WHERE  Sno= '95001';

无论实体化法还是消解法都无法将其转换成对基本表SC的更新。

---

实际系统对视图更新的限制

• 允许对行列子集视图进行更新
• 对其他类型视图的更新不同系统有不同限制
• DB2对视图更新的限制：
  • 若视图是由两个以上基本表导出的，则此视图不允许更新。
  • 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
  • 若视图的字段来自集函数，则此视图不允许更新。
  • 若视图定义中含有GROUP BY子句，则此视图不允许更新。
  • 若视图定义中含有DISTINCT短语，则此视图不允许更新。
  • 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。
  • 一个不允许更新的视图上定义的视图也不允许更新

视图的作用

简化用户的操作

• 当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作
  • 基于多张表连接形成的视图
  • 基于复杂嵌套查询的视图
  • 含导出属性的视图

使用户能以多种角度看待同一数据

视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的需要

对重构数据库提供了一定程度的逻辑独立性

• 物理独立性与逻辑独立性的概念【数据库原理 | 1. 概述】
  • 物理独立性是指用户的应用程序与存储在磁盘上的数据库中数据是相互独立的。即，数据在磁盘上怎样存储由DBMS管理，用户程序不需要了解，应用程序要处理的只是数据的逻辑结构，这样当数据的物理存储改变了，应用程序不用改变。
  • 逻辑独立性是指用户的应用程序与数据库的逻辑结构是相互独立的，即，当数据的逻辑结构改变时，用户程序也可以不变。
• 视图在一定程度上保证了数据的逻辑独立性
• 视图只能在一定程度上提供数据的逻辑独立性
  • 由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。

对机密数据提供安全保护

• 对不同用户定义不同视图，使每个用户只能看到他有权看到的数据
• 通过WITH CHECK OPTION对关键数据定义操作时间限制

[例] 建立1号课程的选课视图，并要求透过该视图进行的更新操作只涉及1号课程，同时对该视图的任何操作只能在工作时间进行。

CREATE VIEW IS_SC
AS
SELECT Sno，Cno，Grade
FROM  SC
WHERE  Cno= '1'
AND  TO_CHAR(SYSDATE,'HH24')  BETWEEN  9  AND  17
AND  TO_CHAR(SYSDATE,'D')  BETWEEN  2  AND  6
WITH CHECK OPTION;