Presentation is loading. Please wait.

Presentation is loading. Please wait.

An Introduction to Database System

Similar presentations


Presentation on theme: "An Introduction to Database System"— Presentation transcript:

1 An Introduction to Database System
数据库系统概论 An Introduction to Database System 第三章 关系数据库标准语言SQL 内蒙古民族大学 计算机科学与技术学院

2 目 录 SQL概述 1 2 学生-课程数据库 数据定义 3 数据查询 4 5 数据更新 视图 6 7 小结

3 3.1 SQL概述 SQL(Structured Query Language) 结构化查询语言,是关系数据库的标准语
库语言。

4 SQL概述(续) SQL 的产生与发展 SQL的特点 SQL的基本概念

5 SQL标准的进展过程 标准 大致页数 发布日期 SQL/86 1986.10 SQL/89(FIPS 127-1) 120页 1989年
标准 大致页数 发布日期 SQL/ SQL/89(FIPS 127-1) 页 年 SQL/ 页 年 SQL 页 年 SQL 年

6 3.1.2 SQL的特点 综合统一 集数据定义语言(DDL),数据操纵语言 (DML),数据控制语言(DCL)功能于一体。
可以独立完成数据库生命周期中的全部活动: 定义关系模式,插入数据,建立数据库; 对数据库中的数据进行查询和更新; 数据库重构和维护 数据库安全性、完整性控制等 用户数据库投入运行后,可根据需要随时逐步 修改模式,不影响数据的运行。 数据操作符统一

7 2.高度非过程化 非关系数据模型的数据操纵语言“面向过程”,必须制定存取路径 SQL只要提出“做什么”,无须了解存取路径。

8 3.面向集合的操作方式 非关系数据模型采用面向记录的操作方式,操作对象是一条记录 SQL采用集合操作方式
操作对象、查找结果可以是元组的集合 一次插入、删除、更新操作的对象可以是元组的集合

9 4.以同一种语法结构 提供多种使用方式 SQL是独立的语言 能够独立地用于联机交互的使用方式 SQL又是嵌入式语言
SQL能够嵌入到高级语言(例如C,C++,Java)程序中,供程序员设计程序时使用

10 5.语言简洁,易学易用 SQL功能极强,完成核心功能只用了9个动词。

11 3.1.3 SQL的基本概念 SQL支持关系数据库三级模式结构 SQL 视图2 视图1 基本表2 基本表1 基本表3 基本表4 存储文件2
存储文件1 外模式 模 式 内模式

12 SQL的基本概念 基本表 存储文件 视图 本身独立存在的表 SQL中一个关系就对应一个基本表 一个(或多个)基本表对应一个存储文件
一个表可以带若干索引 存储文件 逻辑结构组成了关系数据库的内模式 物理结构是任意的,对用户透明 视图 从一个或几个基本表导出的表 数据库中只存放视图的定义而不存放视图对应的数据 视图是一个虚表 用户可以在视图上再定义视图

13 3.2 学生-课程数据库 学生-课程模式 S-T : 学生表:Student(Sno,Sname,Ssex,Sage,Sdept)
课程表:Course(Cno,Cname,Cpno,Ccredit) 学生选课表:SC(Sno,Cno,Grade)

14 Student表 学 号 Sno 姓 名 Sname 性 别 Ssex 年 龄 Sage 所 在 系 Sdept 200215121
学 号 Sno 姓 名 Sname 性 别 Ssex 年 龄 Sage 所 在 系 Sdept 李勇 刘晨 王敏 张立 20 19 18 CS MA IS

15 Course表 课程号 Cno 课程名 Cname 先行课 Cpno 学分 Ccredit 1 2 3 4 5 6 7 数据库 数学
信息系统 操作系统 数据结构 数据处理 PASCAL语言

16 SC表 学 号 Sno 课程号 Cno 成绩 Grade 1 2 3 92 85 88 90 80

17 3.3 数据定义 SQL的数据定义功能: 模式定义、表定义、视图和索引的定义

18 3.3 数据定义 3.3.1 模式的定义与删除 3.3.2 基本表的定义、删除与修改 3.3.3 索引的建立与删除

19 一、定义模式 [例1]定义一个学生-课程模式S-T CREATE SCHEMA “S-T” AUTHORIZATION WANG;
为用户WANG定义了一个模式S-T [例2]CREATE SCHEMA AUTHORIZATION WANG; <模式名>隐含为用户名WANG 若没有指定<模式名>,那么<模式名>隐含为<用户名>

20 定义模式(续) 定义模式实际上定义了一个命名空间 在这个空间中可以定义该模式包含的数据库对象,例如基本表、视图、索引等。
在CREATE SCHEMA中可以接受CREATE TABLE,CREATE VIEW和GRANT子句。 CREATE SCHEMA <模式名> AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]

21 定义模式(续) [例3] CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1(COL1 SMALLINT, COL2 INT, COL3 CHAR(20), COL4 NUMERIC(10,3), COL5 DECIMAL(5,2) ); 为用户ZHANG创建了一个模式TEST,并在 其中定义了一个表TAB1。

22 二、删除模式 DROP SCHEMA <模式名> <CASCADE|RESTRICT>
的数据库对象全部删除 RESTRICT(限制):如果该模式中定义了下属的数据 库对象(如表、视图等),则拒 绝该删除语句的执行。 当该模式中没有任何下属的对象时 才能执行。

23 删除模式(续) [例4] DROP SCHEMA ZHANG CASCADE; 删除模式ZHANG 同时该模式中定义的表TAB1也被删除

24 3.3.2 基本表的 定义、删除与修改 一、定义基本表 <数据类型>[ <列级完整性约束条件> ]
CREATE TABLE <表名>(<列名> <数据类型>[ <列级完整性约束条件> ] [,<列名> <数据类型>[ <列级完整性约束条件>] ] … [,<表级完整性约束条件> ] ); 如果完整性约束条件涉及到该表的多个属性列, 则必须定义在表级上,否则既可以定义在列级也 可以定义在表级。

25 学生表Student [例5] 建立“学生”表Student,学号是主码,姓名取值 唯一。 CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件*/ Sname CHAR(20) UNIQUE,/* Sname取唯一值* Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ); 主码

26 课程表Course [例6] 建立一个“课程”表Course CREATE TABLE Course
( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4) , Ccredit SMALLINT, FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); 先修课 Cpno是外码 被参照表是Course 被参照列是Cno

27 学生选课表SC [例7] 建立一个“学生选课”表SC CREATE TABLE SC (Sno CHAR(9), Cno CHAR(4),
Grade SMALLINT, PRIMARY KEY (Sno,Cno), /* 主码由两个属性构成,必须作为表级完整性进行定义*/

28 学生选课表SC FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 表级完整性约束条件, Cno是外码, 被参照表是Course*/ );

29 二、数据类型 SQL中域的概念用数据类型来实现 定义表的属性时 需要指明其数据类型及长度 选用哪种数据类型 取值范围 要做哪些运算

30 二、数据类型 数据类型 含义 CHAR(n) 长度为n的定长字符串 VARCHAR(n) 最大长度为n的变长字符串 INT
长整数(也可以写作INTEGER) SMALLINT 短整数 NUMERIC(p,d) 定点数,由p位数字(不包括符号、小数点)组成,小数后面有d位数字 REAL 取决于机器精度的浮点数 Double Precision 取决于机器精度的双精度浮点数 FLOAT(n) 浮点数,精度至少为n位数字 DATE 日期,包含年、月、日,格式为YYYY-MM-DD TIME 时间,包含一日的时、分、秒,格式为HH:MM:SS

31 三、模式与表 每一个基本表都属于某一个模式; 一个模式包含多个基本表; 定义基本表所属模式 方法一:在表名中明显地给出模式名
Create table “S-T”.Student(......); /*模式名为 S-T*/ Create table “S-T”.Cource(......); Create table “S-T”.SC(......); 方法二:在创建模式语句中同时创建表 方法三:设置所属的模式

32 模式与表(续) 创建基本表(其他数据库对象也一样)时,若没有指定模式,系统根据搜索路径来确定该对象所属的模式
RDBMS会使用模式列表中第一个存在的模式作为数据库对象的模式名 若搜索路径中的模式名都不存在,系统将给出错误 显示当前的搜索路径: SHOW search_path; 搜索路径的当前默认值是:$user, PUBLIC

33 模式与表(续) DBA用户可以设置搜索路径,然后定义基本表 SET search_path TO “S-T”,PUBLIC;
Create table Student(......); 结果建立了S-T.Student基本表。 RDBMS发现搜索路径中第一个模式名S-T存在,就把该模式作为基本表Student所属的模式。

34 四、修改基本表 ALTER TABLE <表名> [ ADD <新列名> <数据类型>
[ 完整性约束 ] ] [ DROP <完整性约束名> ] [ ALTER COLUMN<列名> <数据类型> ];

35 修改基本表(续) [例8] 向Student表增加“入学时间”列,其数据类型为 日期型。
ALTER TABLE Student ADD S_entrance DATE; 不论基本表中原来是否已有数据,新增加的列一 律为空值。 

36 修改基本表(续) [例9]将年龄的数据类型由字符型(假设原来的数据类 型是字符型)改为整数。 ALTER TABLE Student
ALTER COLUMN Sage INT; [例10]增加课程名称必须取唯一值的约束条件。 ALTER TABLE Course ADD UNIQUE(Cname);

37 五、删除基本表 DROP TABLE <表名> [RESTRICT| CASCADE]; RESTRICT:删除表是有限制的。
欲删除的基本表不能被其他表的约束所引用 如果存在依赖该表的对象,则此表不能被删除 CASCADE:删除该表没有限制。 在删除基本表的同时,相关的依赖对象一 起删除

38 删除基本表(续) [例11] 删除Student表 DROP TABLE Student CASCADE ; 基本表定义被删除,数据被删除
表上建立的索引、视图、触发器等一般也将被删除

39 删除基本表(续) [例12]若表上建有视图,选择RESTRICT时 表不能删除. CREATE VIEW IS_Student AS
SELECT Sno,Sname,Sage FROM Student WHERE Sdept='IS'; DROP TABLE Student RESTRICT; --ERROR: cannot drop table Student because other objects depend on it

40 删除基本表(续) [例12]如果选择CASCADE时可以删除表,视图也自 动被删除 DROP TABLE Student CASCADE;
--NOTICE: drop cascades to view IS_Student SELECT * FROM IS_Student; --ERROR: relation " IS_Student " does not exist

41 DROP TABLE时,SQL99 与 3个RDBMS的处理策略比较
删除基本表(续) DROP TABLE时,SQL99 与 3个RDBMS的处理策略比较 标准及主流数据库的处理方式 依赖基本表的对象 SQL99 Kingbase ES ORACLE 9i MS SQL SERVER 2000 R C 1. 索引 无规定 2. 视图 × 保留 3. DEFAULT,PRIMARY KEY,CHECK(只含该表的列)NOT NULL 等约束 4. Foreign Key 5. TRIGGER 6. 函数或存储过程 R表示RESTRICT , C表示CASCADE; ‘×’表示不能删除基本表,‘√’表示能删除基本表,‘保留’表示删除基本表后,还保留依赖对象

42 3.3.3 索引的建立与删除 DBMS自动完成 建立索引的目的:加快查询速度 谁可以建立索引 DBA 或 表的属主(即建立表的人)
PRIMARY KEY UNIQUE 谁维护索引 DBMS自动完成  使用索引 DBMS自动选择是否使用索引以及使用哪些索引

43 索 引 RDBMS中索引一般采用B+树、HASH索引来实现 B+树索引具有动态平衡的优点 HASH索引具有查找速度快的特点
索 引 RDBMS中索引一般采用B+树、HASH索引来实现 B+树索引具有动态平衡的优点 HASH索引具有查找速度快的特点 采用B+树,还是HASH索引 则由具体的RDBMS来决定 索引是关系数据库的内部实现技术,属于内模式的范畴 CREATE INDEX语句定义索引时,可以定义索引是唯一索引、非唯一索引或聚簇索引

44 一、建立索引 语句格式 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>
(<列名>[<次序>][,<列名>[<次序>] ]…);

45 建立索引(续) [例13] CREATE CLUSTER INDEX Stusname ON Student(Sname);
在最经常查询的列上建立聚簇索引以提高查询效率 一个基本表上最多只能建立一个聚簇索引 经常更新的列不宜建立聚簇索引

46 建立索引(续) [例14]为学生-课程数据库中的Student,Course,SC 三个表建立索引。
CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Course(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC); Student表按学号升序建唯一索引 Course表按课程号升序建唯一索引 SC表按学号升序和课程号降序建唯一索引

47 二、删除索引 DROP INDEX <索引名>; 删除索引时,系统会从数据字典中删去有 关该索引的描述。
[例15] 删除Student表的Stusname索引 DROP INDEX Stusname;

48 3.4 数据查询 语句格式 SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] …
FROM <表名或视图名>[, <表名或视图名> ] … [WHERE <条件表达式> ][ GROUP BY <列名1> [HAVING <条件表达式> ] ] [ORDER BY <列名2> [ ASC|DESC ] ]; 

49 3.4 数据查询 3.4.1 单表查询 3.4.2 连接查询 3.4.3 嵌套查询 3.4.4 集合查询
3.4 数据查询 3.4.1 单表查询 3.4.2 连接查询 3.4.3 嵌套查询 3.4.4 集合查询 3.4.5 Select语句的一般形式

50 3.4.1 单表查询 查询仅涉及一个表: 一、 选择表中的若干列 二、 选择表中的若干元组 三、 ORDER BY子句 四、 聚集函数
单表查询 查询仅涉及一个表: 一、 选择表中的若干列 二、 选择表中的若干元组 三、 ORDER BY子句 四、 聚集函数 五、 GROUP BY子句

51 一、选择表中的若干列 1. 查询指定列 [例1] 查询全体学生的学号与姓名。 SELECT Sno,Sname FROM Student;
[例1] 查询全体学生的学号与姓名。 SELECT Sno,Sname FROM Student;  [例2] 查询全体学生的姓名、学号、所在系。 SELECT Sname,Sno,Sdept FROM Student;

52 2. 查询全部列 选出所有属性列: 在SELECT关键字后面列出所有列名 将<目标列表达式>指定为 *
[例3] 查询全体学生的详细。 SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student; SELECT * FROM Student;

53 3. 查询经过计算的值 SELECT子句的<目标列表达式>可以为: 算术表达式 字符串常量 函数 列别名

54 查询经过计算的值(续) [例4] 查全体学生的姓名及其出生年份。 SELECT Sname,2004-Sage
[例4] 查全体学生的姓名及其出生年份。 SELECT Sname,2004-Sage /*假定当年的年份为2004年*/ FROM Student; 输出结果: Sname Sage 李勇 刘晨 王敏 张立

55 查询经过计算的值(续) [例5] 查询全体学生的姓名、出生年份和所有系,要 求用小写字母表示所有系名。
SELECT Sname,‘Year of Birth: ',2004-Sage, LOWER(Sdept) FROM Student; 输出结果: Sname 'Year of Birth:' 2004-Sage ISLOWER(Sdept) 李勇 Year of Birth: cs 刘晨 Year of Birth: is 王敏 Year of Birth: ma 张立 Year of Birth: is

56 查询经过计算的值(续) 使用列别名改变查询结果的列标题:
SELECT Sname NAME,‘Year of Birth: ’ BIRTH, 2000-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT FROM Student;

57 查询经过计算的值(续) NAME BIRTH BIRTHDAY DEPARTMENT 输出结果:
李勇 Year of Birth: cs 刘晨 Year of Birth: is 王敏 Year of Birth: ma 张立 Year of Birth: is

58 二、选择表中的若干元组 消除取值重复的行 如果没有指定DISTINCT关键词,则缺省 为ALL。 [例6] 查询选修了课程的学生学号。
[例6] 查询选修了课程的学生学号。 SELECT Sno FROM SC; 等价于: SELECT ALL Sno FROM SC;

59 消除取值重复的行(续) 指定DISTINCT关键词,去掉表中重复的行 SELECT DISTINCT Sno FROM SC; 执行结果:

60 2.查询满足条件的元组 表3.4 常用的查询条件 查 询 条 件 谓 词 比 较
谓 词 比 较 =,>,<,>=,<=,!=,<>,!>,!<; NOT+上述比较运算符 确定范围 BETWEEN AND,NOT BETWEEN AND 确定集合 IN,NOT IN 字符匹配 LIKE,NOT LIKE 空 值 IS NULL,IS NOT NULL 多重条件 (逻辑运算) AND,OR,NOT

61 (1) 比较大小 [例7] 查询计算机科学系全体学生的名单 SELECT Sname FROM Student
WHERE Sdept=‘CS’; [例8]查询所有年龄在20岁以下的学生姓名及 其年龄。 SELECT Sname,Sage FROM Student WHERE Sage < 20; [例9]查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade<60;

62 (2)确定范围 谓词: BETWEEN … AND … NOT BETWEEN … AND …
[例10] 查询年龄在20~23岁(包括20岁和23岁)之 间的学生的姓名、系别和年龄 SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23; [例11] 查询年龄不在20~23岁之间的学生姓名、系别 和年龄 SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;

63 (3) 确定集合 谓词:IN <值表>, NOT IN <值表>
[例12]查询信息系(IS)、数学系(MA)和计算机 科学系(CS)学生的姓名和性别。 SELECT Sname,Ssex FROM Student WHERE Sdept IN ( 'IS','MA','CS' ); [例13]查询既不是信息系、数学系,也不是计算机科 学系的学生的姓名和性别。 SELECT Sname,SsexFROM Student WHERE Sdept NOT IN ( 'IS','MA','CS' );

64 (4)字符匹配 谓词: [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
匹配串为固定字符串 [例14]查询学号为 的学生的详细情况 SELECT * FROM Student WHERE Sno LIKE ‘ '; 等价于: SELECT * FROM Student WHERE Sno = ' ';

65 字符匹配(续) 2) 匹配串为含通配符的字符串 [例15]查询所有姓刘学生的姓名、学号和性别. SELECT Sname,Sno,Ssex
FROM Student WHERE Sname LIKE ‘刘%’; [例16] 查询姓“欧阳”且全名为三个汉字的学生 的姓名。 SELECT Sname FROM Student WHERE Sname LIKE '欧阳__';

66 字符匹配(续) [例17] 查询名字中第2个字为“阳”字的学生 的姓名和学号。 SELECT Sname,Sno FROM Student
[例17] 查询名字中第2个字为“阳”字的学生 的姓名和学号。 SELECT Sname,Sno FROM Student WHERE Sname LIKE ‘__阳%’; [例18] 查询所有不姓刘的学生姓名。 SELECT Sname,Sno,Ssex WHERE Sname NOT LIKE '刘%';

67 字符匹配(续) 3) 使用换码字符将通配符转义为普通字符 [例19] 查询DB_Design课程的课程号和学分。
SELECT Cno,Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\‘; [例20] 查询以“DB_”开头,且倒数第3个字符为 i的 课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE 'DB\_%i_ _' ESCAPE ' \ ‘; 注:ESCAPE ‘\’ 表示“ \” 为换码字符。

68 (5) 涉及空值的查询 谓词: IS NULL 或 IS NOT NULL “IS” 不能用 “=” 代替
[例21] 某些学生选修课程后没有参加考试,所以有选 课记录,但没有考试成绩。查询缺少成绩的 学生的学号和相应的课程号。 SELECT Sno,Cno FROM SC WHERE Grade IS NULL [例22] 查所有有成绩的学生学号和课程号。 SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL;

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

70 多重条件查询(续) [例23] 查询计算机系年龄在20岁以下的学生 姓名。 SELECT Sname FROM Student
[例23] 查询计算机系年龄在20岁以下的学生 姓名。 SELECT Sname FROM Student WHERE Sdept= 'CS' AND Sage<20;

71 多重条件查询(续) 改写[例12] [例12] 查询信息系(IS)、数学系(MA)和计算 机科学系(CS)学生的姓名和性别。
SELECT Sname,Ssex FROM Student WHERE Sdept IN ( 'IS','MA','CS' ) 可改写为: FROM Student WHERE Sdept= 'IS' OR Sdept= 'MA' OR Sdept= ' CS ';

72 三、ORDER BY子句 ORDER BY子句 可以按一个或多个属性列排序 升序:ASC;降序:DESC;缺省值为升序 当排序列含空值时

73 ORDER BY子句(续) [例24] 查询选修了3号课程的学生的学号及其成绩, 查询结果按分数降序排列。
SELECT Sno,Grade FROM SC WHERE Cno= ' 3 ' ORDER BY Grade DESC; [例25]查询全体学生情况,查询结果按所在系的系 号升序排列,同一系中的学生按年龄降序排 列。 SELECT * FROM Student ORDER BY Sdept,Sage DESC;

74 四、聚集函数 聚集函数: 计数 计算总和 计算平均值 最大最小值 MAX([DISTINCT|ALL] <列名>)
COUNT([DISTINCT|ALL] *) COUNT([DISTINCT|ALL] <列名>) 计算总和 SUM([DISTINCT|ALL] <列名>) 计算平均值 AVG([DISTINCT|ALL] <列名>) 最大最小值 MAX([DISTINCT|ALL] <列名>) MIN([DISTINCT|ALL] <列名>)

75 聚集函数 (续) [例26] 查询学生总人数。 SELECT COUNT(*) FROM Student;
[例26] 查询学生总人数。 SELECT COUNT(*) FROM Student;  [例27] 查询选修了课程的学生人数。 SELECT COUNT(DISTINCT Sno) FROM SC; [例28] 计算1号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno= ' 1 ';

76 聚集函数 (续) [例29] 查询选修1号课程的学生最高分数。 SELECT MAX(Grade) FROM SC
[例29] 查询选修1号课程的学生最高分数。 SELECT MAX(Grade) FROM SC WHERE Cno= ‘ 1 ’; [例30]查询学生 选修课程的总学分数。 SELECT SUM(Ccredit) FROM SC, Course WHERE Sno=' ' AND SC.Cno=Course.Cno;

77 五、GROUP BY子句 细化聚集函数的作用对象 GROUP BY子句分组: 未对查询结果分组,聚集函数将作用于整个查询 结果
对查询结果分组后,聚集函数将分别作用于每个组 作用对象是查询的中间结果表 按指定的一列或多列值分组,值相等的为一组

78 GROUP BY子句(续) [例31] 求各个课程号及相应的选课人数。 SELECT Cno,COUNT(Sno) FROM SC
[例31] 求各个课程号及相应的选课人数。 SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;  查询结果: Cno COUNT(Sno)

79 GROUP BY子句(续) [例32] 查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno
HAVING COUNT(*) >3;  

80 GROUP BY子句(续) 作用对象不同 HAVING短语与WHERE子句的区别: WHERE子句作用于基表或视图,从中选择满足条件的元组

81 3.4.2 连接查询 连接查询:同时涉及多个表的查询 连接条件或连接谓词:用来连接两个表的条件; 一般格式:
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

82 3.4.2 连接查询 连接查询:同时涉及多个表的查询 连接字段:连接谓词中的列名称 连接条件中的各连接字段类型必须是可比的,
但名字不必是相同的。

83 连接操作的执行过程 嵌套循环法(NESTED-LOOP) 首先在表1中找到第一个元组,然后从头 开始扫描表2,逐一查找满足连接件的元
组,找到后就将表1中的第一个元组与该 元组拼接起来,形成结果表中一个元 组。

84 连接操作的执行过程 表2全部查找完后,再找表1中第二个元 组,然 后再从头开始扫描表2,逐一查找满足连接条
件的元组,找到后就将表1中的第二个元组与 该元组拼接起来,形成结果表中一个元组。 重复上述操作,直到表1中的全部元组都处理 完毕 。

85 排序合并法(SORT-MERGE) 常用于 = 连接 首先按连接属性对表1和表2排序
对表1的第一个元组,从头开始扫描表2,顺序查找满足连接条件的元组,找到后就将表1中的第一个元组与该元组拼接起来,形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时,对表2的查询不再继续。

86 排序合并法 找到表1的第二条元组,然后从刚才的中断点处继 续顺序扫描表2,查找满足连接条件的元组,找到
后就将表1中的第一个元组与该元组拼接起来,形 成结果表中一个元组。直接遇到表2中大于表1连接 字段值的元组时,对表2的查询不再继续。 重复上述操作,直到表1或表2中的全部元组都处理 完毕为止 。

87 索引连接(INDEX-JOIN) 对表2按连接字段建立索引; 对表1中的每个元组,依次根据其连接字段 值查询表2的索引,从中找到满足条件的元
组,找到后就将表1中的第一个元组与该元 组拼接起来,形成结果表中一个元组。

88 连接查询(续) 一、等值与非等值连接查询 二、自身连接 三、外连接 四、复合条件连接

89 一、等值与非等值连接查询 等值连接:连接运算符为 = [例33] 查询每个学生及其选修课程的情况 SELECT Student.*,SC.*
[例33] 查询每个学生及其选修课程的情况 SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno = SC.Sno;

90 等值与非等值连接查询(续) 查询结果: Student.Sno Sname Sse Sage Sdept SC.Sno Cno Grade
李勇 20 CS 1 92 2 85 3 88 刘晨 19 90 80

91 等值与非等值连接查询(续) 自然连接: [例34] 对[例33]用自然连接完成。 SELECT
[例34] 对[例33]用自然连接完成。 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno = SC.Sno;

92 二、自身连接 自身连接:一个表与其自己进行连接 需要给表起别名以示区别 由于所有属性名都是同名属性,因此必须使用别名前缀
[例35]查询每一门课的间接先修课(即先修课的先修课) SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno = SECOND.Cno;

93 自身连接(续) FIRST表(Course表) Cno Cname Cpno Ccredit 1 数据库 5 4 2 数学 3 信息系统
3 信息系统 操作系统 6 数据结构 7 数据处理 PASCAL语言

94 自身连接(续) SECOND表(Course表) Cno Cname Cpno Ccredit 1 数据库 5 4 2 数学 3 信息系统
3 信息系统 操作系统 6 数据结构 7 数据处理 PASCAL语言

95 自身连接(续) 查询结果: Cno Pcno 1 7 3 5 6

96 三、外连接 外连接与普通连接的区别 普通连接操作只输出满足连接条件的元组 外连接操作以指定表为连接主体,将主体表中不
满足连接条件的元组一并输出 [例 36] 改写[例33] SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

97 外连接(续) 执行结果: Student.Sno Sname Ssex Sage Sdept Cno Grade 200215121 李勇
20 CS 1 92 2 85 3 88 刘晨 19 90 80 王敏 18 MA NULL 张立 IS

98 外连接(续) 左外连接 列出左边关系(如本例Student)中所有的元组 右外连接 列出右边关系中所有的元组

99 四、复合条件连接 复合条件连接: WHERE子句中含多个连接条件 [例37]查询选修2号课程且成绩在90分以上的所有学生
SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND /* 连接谓词*/ SC.Cno= ‘2’ AND SC.Grade > 90; /* 其他限定条件 */

100 复合条件连接(续) SELECT Student.Sno,Sname,Cname,Grade [例38]查询每个学生的学号、姓名、选修的课程
名及成绩 SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course /*多表连接*/ WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno;

101 3.4.3 嵌套查询(续) 嵌套查询概述 一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

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

103 嵌套查询(续) 子查询的限制 不能使用ORDER BY子句 层层嵌套方式反映了 SQL语言的结构化 有些嵌套查询可以用连接运算替代

104 嵌套查询求解方法 不相关子查询: 子查询的查询条件不依赖于父查询
由里向外,逐层处理。即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其父查询的查找条件。

105 嵌套查询求解方法(续) 相关子查询:子查询的查询条件依赖于父查询
首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表 然后再取外层表的下一个元组 重复这一过程,直至外层表全部检查完为止

106 3.4.3 嵌套查询 一、带有IN谓词的子查询 二、 带有比较运算符的子查询 三、 带有ANY(SOME)或ALL谓词的子查询
嵌套查询 一、带有IN谓词的子查询 二、 带有比较运算符的子查询 三、 带有ANY(SOME)或ALL谓词的子查询 四、 带有EXISTS谓词的子查询

107 一、带有IN谓词的子查询 [例39] 查询与“刘晨”在同一个系学习的学生。 此查询要求可以分步来完成 ① 确定“刘晨”所在系名
[例39] 查询与“刘晨”在同一个系学习的学生。 此查询要求可以分步来完成 ① 确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname= ' 刘晨 '; 结果为: CS

108 带有IN谓词的子查询(续) ② 查找所有在IS系学习的学生。 SELECT Sno,Sname,Sdept FROM Student
WHERE Sdept= ‘ CS ’; 结果为: Sno Sname Sdept 李勇 CS 刘晨

109 带有IN谓词的子查询(续) 将第一步查询嵌入到第二步查询的条件中 SELECT Sno,Sname,Sdept FROM Student
WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= ‘ 刘晨 ’); 此查询为不相关子查询。

110 带有IN谓词的子查询(续) 用自身连接完成[例39]查询要求 SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = '刘晨';

111 带有IN谓词的子查询(续) [例40]查询选修了课程名为“信息系统”的学生学号 和姓名 WHERE Sno IN
SELECT Sno,Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname= ‘信息系统’ ) );

112 带有IN谓词的子查询(续) 用连接查询实现[例40] SELECT Sno,Sname FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=‘信息系统’;

113 二、带有比较运算符的子查询 当能确切知道内层查询返回单值时,可 用比较运算符(>,<,=,>=,<=,!=或
< >)。 与ANY或ALL谓词配合使用。

114 带有比较运算符的子查询 例:假设一个学生只可能在一个系学习,并且必须 属于一个系,则在[例39]可以用 = 代替IN :
SELECT Sno,Sname,Sdept FROM Student WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname= ‘刘晨’);

115 带有比较运算符的子查询(续) 子查询一定要跟在比较符之后 错误的例子:
SELECT Sno,Sname,Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= ‘ 刘晨 ’ ) = Sdept;

116 带有比较运算符的子查询(续) [例41]找出每个学生超过他选修课程平均成绩的课程号。 SELECT Sno, Cno FROM SC x
WHERE Grade >=(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno); 相关子查询

117 带有比较运算符的子查询(续) 可能的执行过程:
1. 从外层查询中取出SC的一个元组x,将元组x的Sno值( )传送给内层查询。 SELECT AVG(Grade) FROM SC y WHERE y.Sno=' '; 2. 执行内层查询,得到值88(近似值),用该值代替内层查询,得到外层查询: SELECT Sno, Cno FROM SC x WHERE Grade >=88;

118 带有比较运算符的子查询(续) 3. 执行这个查询,得到 (200215121,1)
( ,3) 4.外层查询取出下一个元组重复做上述1至3步骤,直到外层的SC元组全部处理完毕。结果为: ( ,2)

119 三、带有ANY(SOME) 或ALL谓词的子查询
谓词语义 ANY:任意一个值 ALL:所有值

120 带有ANY(SOME) 或ALL谓词的子查询 (续)
需要配合使用比较运算符 > ANY 大于子查询结果中的某个值 > ALL 大于子查询结果中的所有值 < ANY 小于子查询结果中的某个值 < ALL 小于子查询结果中的所有值 >= ANY 大于等于子查询结果中的某个值 >= ALL 大于等于子查询结果中的所有值

121 带有ANY(SOME) 或ALL谓词的子查询 (续)
需要配合使用比较运算符 <= ANY 小于等于子查询结果中的某个值 <= ALL 小于等于子查询结果中的所有值 = ANY 等于子查询结果中的某个值 =ALL 等于子查询结果中的所有值 (通常没有实际意义)

122 带有ANY(SOME) 或ALL谓词的子查询 (续)
需要配合使用比较运算符 !=(或<>)ANY 不等于子查询结果中的某个值 !=(或<>)ALL 不等于子查询结果中的任何 一个值

123 带有ANY(SOME) 或ALL谓词的子查询 (续)
[例42] 查询其他系中比计算机科学某一学生年龄小 的学生姓名和年龄 SELECT Sname,Sage FROM Student WHERE Sage < ANY (SELECT Sage FROM Student WHERE Sdept= ' CS ') AND Sdept <> ‘CS ' ; /*父查询块中的条件 */

124 带有ANY(SOME) 或ALL谓词的子查询 (续)
结果: 执行过程: 1.RDBMS执行此查询时,首先处理子查询,找出 CS系中所有学生的年龄,构成一个集合(20,19) 2. 处理父查询,找所有不是CS系且年龄小于 20 或 19的学生 Sname Sage 王敏 18 张立 19

125 带有ANY(SOME) 或ALL谓词的子查询 (续)
用聚集函数实现[例42] SELECT Sname,Sage FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept= ‘CS ') AND Sdept <> ' CS ’;

126 带有ANY(SOME) 或ALL谓词的子查询 (续)
[例43] 查询其他系中比计算机科学系所有学生年 龄都小的学生姓名及年龄。 方法一:用ALL谓词 SELECT Sname,Sage FROM Student WHERE Sage < ALL (SELECT Sage FROM Student WHERE Sdept= ' CS ') AND Sdept <> ' CS ’;

127 带有ANY(SOME) 或ALL谓词的子查询 (续)
方法二:用聚集函数 SELECT Sname,Sage FROM Student WHERE Sage < (SELECT MIN(Sage) FROM Student WHERE Sdept= ' CS ') AND Sdept <>' CS ’;

128 带有ANY(SOME) 或ALL谓词的子查询 (续)
IN谓词的等价转换关系 = <>或!= < <= > >= ANY IN -- <MAX <=MAX >MIN >= MIN ALL NOT IN <MIN <= MIN >MAX >= MAX

129 四、带有EXISTS 谓词的子查询(续) 1. EXISTS谓词 存在量词 带有EXISTS谓词的子查询不返回任何数据,只
产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空,则外层的WHERE子句返回真值; 若内层查询结果为空,则外层的WHERE子句返回假值; 由EXISTS引出的子查询,其目标列表达式通常 都用* ,因为带EXISTS的子查询只返回真值 或假值,给出列名无实际意义。

130 四、带有EXISTS 谓词的子查询(续) 2. NOT EXISTS谓词 若内层查询结果非空,则外层的 WHERE子句返回假值;
若内层查询结果为空,则外层的 WHERE子句返回真值;

131 带有EXISTS谓词的子查询(续) [例44]查询所有选修了1号课程的学生姓名。 思路分析: 本查询涉及Student和SC关系
在Student中依次取每个元组的Sno值,用此值去检查SC关系 若SC中存在这样的元组,其Sno值等于此 Student.Sno值,并且其Cno= ‘1’,则取 此Student.Sname送入结果关系

132 带有EXISTS谓词的子查询(续) 用嵌套查询 SELECT Sname FROM Student WHERE EXISTS
FROM SC WHERE Sno=Student.Sno AND Cno= ' 1 ');

133 带有EXISTS谓词的子查询(续) 用连接运算 SELECT Sname FROM Student, SC
WHERE Student.Sno=SC.Sno AND SC.Cno= '1';

134 带有EXISTS谓词的子查询(续) [例45] 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student
[例45] 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno='1');

135 带有EXISTS谓词的子查询(续) 不同形式的查询间的替换 用EXISTS/NOT EXISTS实现全称量词(难点)
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换 用EXISTS/NOT EXISTS实现全称量词(难点) SQL语言中没有全称量词 (For all) 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词: (x)P ≡  ( x( P))

136 带有EXISTS谓词的子查询(续) [例39]查询与“刘晨”在同一个系学习的学生。 可以用带EXISTS谓词的子查询替换:
SELECT Sno,Sname,Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = ‘刘晨’);

137 带有EXISTS谓词的子查询(续) [例46] 查询选修了全部课程的学生姓名。 SELECT Sname FROM Student
WHERE NOT EXISTS (SELECT * FROM Course (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno));

138 带有EXISTS谓词的子查询(续) 用EXISTS/NOT EXISTS实现逻辑蕴函(难点)
SQL语言中没有蕴函(Implication)逻辑运算 可以利用谓词演算将逻辑蕴函谓词等价转换 为: p  q ≡  p∨q

139 带有EXISTS谓词的子查询(续) [例47]查询至少选修了学生200215122选修的 全部课程的学生号码。 解题思路:
课程y,只要 学生选修了课程y,则x也 选修了y。 形式化表示: 用P表示谓词 “学生 选修了课程y” 用q表示谓词 “学生x选修了课程y” 则上述查询为: (y) p  q

140 带有EXISTS谓词的子查询(续) 等价变换: (y)p  q ≡  (y ((p  q ))

141 带有EXISTS谓词的子查询(续) 用NOT EXISTS谓词表示: SELECT DISTINCT Sno FROM SC SCX
WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = ' ' AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno));

142 3.4.4 集合查询 1. 集合操作的种类: 并操作UNION 交操作INTERSECT 差操作EXCEPT
1. 集合操作的种类: 并操作UNION 交操作INTERSECT 差操作EXCEPT 2. 参加集合操作的各查询结果的列数必须相 同;对应项的数据类型也必须相同。

143 集合查询(续) [例48] 查询计算机科学系的学生及年龄不大于19岁 的学生。 方法一: SELECT * FROM Student
[例48] 查询计算机科学系的学生及年龄不大于19岁 的学生。 方法一: SELECT * FROM Student WHERE Sdept= 'CS' UNION SELECT * FROM Student WHERE Sage<=19; UNION:将多个查询结果合并起来时,系统自动去 掉重复元组。 UNION ALL:将多个查询结果合并起来时, 保留重复元组。

144 集合查询(续) 方法二: SELECT DISTINCT * FROM Student
WHERE Sdept= 'CS' OR Sage<=19;

145 集合查询(续) [例49] 查询选修了课程1或者选修了课程2的 学生。 SELECT Sno FROM SC WHERE Cno=' 1 '
[例49] 查询选修了课程1或者选修了课程2的 学生。 SELECT Sno FROM SC WHERE Cno=' 1 ' UNION WHERE Cno= ' 2 ';

146 集合查询(续) [例50] 查询计算机科学系的学生与年龄不大 于19岁的学生的交集。 SELECT * FROM Student
[例50] 查询计算机科学系的学生与年龄不大 于19岁的学生的交集。 SELECT * FROM Student WHERE Sdept='CS' INTERSECT WHERE Sage<=19

147 集合查询(续) [例50] 实际上就是查询计算机科学系中年龄 不大于19岁的学生 SELECT * FROM Student
WHERE Sdept= 'CS' AND Sage<=19;

148 集合查询(续) [例51] 查询选修课程1的学生集合与选修课 程2的学生集合的交集。 FROM SC WHERE Cno=' 1 '
[例51] 查询选修课程1的学生集合与选修课 程2的学生集合的交集。 SELECT Sno FROM SC WHERE Cno=' 1 ' INTERSECT WHERE Cno='2 ';

149 集合查询(续) [例51]实际上是查询既选修了课程1又选修了 课程2的学生 SELECT Sno FROM SC
WHERE Cno=' 1 ' AND Sno IN (SELECT Sno FROM SC WHERE Cno=' 2 ');

150 集合查询(续) [例52] 查询计算机科学系的学生与年龄不大 于19岁的学生的差集。 SELECT * FROM Student
[例52] 查询计算机科学系的学生与年龄不大 于19岁的学生的差集。 SELECT * FROM Student WHERE Sdept='CS' EXCEPT SELECT * WHERE Sage <=19;

151 集合查询(续) [例52]实际上是查询计算机科学系中年龄大 于19岁的学生 SELECT * FROM Student
WHERE Sdept= 'CS' AND Sage>19;

152 3.4.5 SELECT语句的一般格式 SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ,<目标列表达式> [别名]] … FROM <表名或视图名> [别名] [ ,<表名或视图名> [别名]] … [WHERE <条件表达式>] [GROUP BY <列名1> [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC]

153 3.5 数 据 更 新 插入数据 修改数据 删除数据

154 插入数据 两种插入数据方式 1. 插入元组 2. 插入子查询结果 可以一次插入多个元组

155 一、插入元组 语句格式 INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>] … ) 功能 将新元组插入指定表中

156 插入元组(续) INTO子句 VALUES子句 提供的值必须与INTO子句匹配 属性列的顺序可与表定义中的顺序不一致 没有指定属性列
指定部分属性列 VALUES子句 提供的值必须与INTO子句匹配 值的个数 值的类型

157 插入元组(续) [例1]将一个新学生元组(学号:200215128;姓 名:陈冬;性别:男;所在系:IS;年龄:
18岁)插入到Student表中。 INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES (' ','陈冬','男','IS',18);

158 插入元组(续) [例2]将学生张成民的信息插入到Student表 中。 INSERT INTO Student
VALUES (‘ ’, ‘张成民’, ‘男’,18,'CS');

159 插入元组(续) [例3] 插入一条选课记录( '200215128','1 ') INSERT INTO SC(Sno,Cno)
VALUES (‘ ’,‘ 1 ’); RDBMS将在新插入记录的Grade列上自动地赋空值 或者 INSERT INTO SC VALUES (' ',' 1 ',NULL);

160 二、插入子查询结果 语句格式 INSERT INTO <表名>
[(<属性列1> [,<属性列2>… )] 子查询; 功能 将子查询结果插入指定表中

161 插入子查询结果(续) INTO子句(与插入元组类似) 子查询 SELECT子句目标列必须与INTO子句匹配 值的个数 值的类型

162 插入子查询结果(续) [例4] 对每一个系,求学生的平均年龄,并把 结果存入数据库。 第一步:建表
[例4] 对每一个系,求学生的平均年龄,并把 结果存入数据库。 第一步:建表 CREATE TABLE Dept_age (Sdept CHAR(15) /* 系名*/ Avg_age SMALLINT); /*学生平均年龄*/

163 插入子查询结果(续) 第二步:插入数据 INSERT INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage) FROM Student GROUP BY Sdept;

164 插入子查询结果(续) RDBMS在执行插入语句时会检查所插元 组是否破坏表上已定义的完整性规则: 实体完整性 参照完整性 用户定义的完整性
NOT NULL约束 UNIQUE约束 值域约束

165 3.5.2 修改数据 语句格式 UPDATE <表名> SET <列名>=
修改数据 语句格式 UPDATE <表名> SET <列名>= <表达式>[,<列名>=<表达式>]… [WHERE <条件>]; 功能 修改指定表中满足WHERE子句条件的元组

166 修改数据(续) SET子句 指定修改方式 要修改的列 修改后取值 WHERE子句 指定要修改的元组 缺省表示要修改表中的所有元组

167 修改数据(续) 三种修改方式 1. 修改某一个元组的值 2. 修改多个元组的值 3. 带子查询的修改语句

168 1. 修改某一个元组的值 [例5] 将学生200215121的年龄改为22岁 UPDATE Student SET Sage=22
[例5] 将学生 的年龄改为22岁 UPDATE Student SET Sage=22 WHERE Sno=' ';

169 2. 修改多个元组的值 [例6] 将所有学生的年龄增加1岁 UPDATE Student SET Sage= Sage+1;

170 3. 带子查询的修改语句 [例7] 将计算机科学系全体学生的成绩置 零。 UPDATE SC SET Grade=0 WHERE 'CS'=
[例7] 将计算机科学系全体学生的成绩置 零。 UPDATE SC SET Grade=0 WHERE 'CS'= (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno);

171 修改数据(续) RDBMS在执行修改语句时会检查修改操 作是否破坏表上已定义的完整性规则: 实体完整性 主码不允许修改 用户定义的完整性
NOT NULL约束 UNIQUE约束 值域约束

172 3.5.3 删除数据 语句格式 DELETE FROM <表名> 功能 WHERE子句:指定要删除的元组
删除数据 语句格式 DELETE FROM <表名> [WHERE <条件>]; 功能 删除指定表中满足WHERE子句条件的元组 WHERE子句:指定要删除的元组 缺省表示要删除表中的全部元组,表的定 义仍在字典中。

173 删除数据(续) 三种删除方式 1. 删除某一个元组的值 2. 删除多个元组的值 3. 带子查询的删除语句

174 1. 删除某一个元组的值 [例8] 删除学号为200215128的学生记录。 DELETE FROM Student
[例8] 删除学号为 的学生记录。 DELETE FROM Student WHERE Sno= ‘ ’;

175 2. 删除多个元组的值 [例9] 删除所有的学生选课记录。 DELETE FROM SC;

176 3. 带子查询的删除语句 [例10] 删除计算机科学系所有学生的选课记 录。 DELETE FROM SC WHERE 'CS'=
[例10] 删除计算机科学系所有学生的选课记 录。 DELETE FROM SC WHERE 'CS'= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno);

177 3.6 视 图 视图的特点 虚表,是从一个或几个基本表(或视图)导出的表; 只存放视图的定义,不存放视图对应的数据;
3.6 视 图 视图的特点 虚表,是从一个或几个基本表(或视图)导出的表; 只存放视图的定义,不存放视图对应的数据; 基表中的数据发生变化,从视图中查询出的数据也随之改变;

178 3.6 视 图 基于视图的操作 查询 删除 受限更新 定义基于该视图的新视图

179 3.6 视 图 定义视图 查询视图 更新视图 视图的作用

180 定义视图 建立视图 删除视图

181 一、建立视图 语句格式 CREATE VIEW <视图名>[(<列名> [,<列名>]…)]
AS <子查询> [WITH CHECK OPTION]; 组成视图的属性列名:全部省略或全部指定 子查询不允许含有ORDER BY子句和DISTINCT短语

182 建立视图(续) RDBMS执行CREATE VIEW语句时只是把视图定义存入数据字典,并不执行其中的SELECT语句。
在对视图查询时,按视图的定义从基本表中将数据查出。

183 建立视图(续) [例1] 建立信息系学生的视图。 CREATE VIEW IS_Student AS
[例1] 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept= 'IS';

184 建立视图(续) [例2]建立信息系学生的视图,并要求进行修改和 插入操作时仍需保证该视图只有信息系的学生
CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept= 'IS‘ WITH CHECK OPTION;

185 建立视图(续) 对IS_Student视图的更新操作: 修改操作:自动加上Sdept= 'IS'的条件
如果不是,则拒绝该插入操作 如果没有提供Sdept属性值,则自动定义 Sdept为'IS‘ IS_Student是一个行列子集视图。

186 建立视图(续) 基于多个基表的视图 [例3] 建立信息系选修了1号课程的学生视图。
[例3] 建立信息系选修了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';

187 建立视图(续) 基于视图的视图 [例4] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。 CREATE VIEW IS_S2 AS
[例4] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。 CREATE VIEW IS_S2 AS SELECT Sno,Sname,Grade FROM IS_S1 WHERE Grade>=90;

188 建立视图(续) 带表达式的视图 [例5] 定义一个反映学生出生年份的视图。CREATE VIEW BT_S(Sno,Sname,Sbirth) AS SELECT Sno,Sname,2000-Sage FROM Student;

189 建立视图(续) 分组视图 [例6] 将学生的学号及他的平均成绩定义为一个 视图 假设SC表中“成绩”列Grade为数字型
[例6] 将学生的学号及他的平均成绩定义为一个 视图 假设SC表中“成绩”列Grade为数字型 CREATE VIEW S_G(Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;

190 建立视图(续) 不指定属性列 [例7]将Student表中所有女生记录定义为一个视图
CREATE VIEW F_Student(F_Sno,name,sex,age,dept) AS SELECT * FROM Student WHERE Ssex=‘女’;

191 建立视图(续) 不指定属性列 缺点: 修改基表Student的结构后,Student表与 F_Student视图的映象关系被破坏,导致该视
图不能正确工作。

192 二、删除视图 语句的格式: DROP VIEW <视图名>; 该语句从数据字典中删除指定的视图定义
如果该视图上还导出了其他视图,使用 CASCADE级联删除语句,把该视图和由 它导出的所有视图一起删除 ; 删除基表时,由该基表导出的所有视图定 义都必须显式地使用DROP VIEW语句删 除。

193 删除视图(续) [例8]删除视图BT_S:DROP VIEW BT_S; 删除视图IS_S1:DROP VIEW IS_S1; 拒绝执行
级联删除: DROP VIEW IS_S1 CASCADE;

194 3.6.2 查询视图 用户角度: 查询视图与查询基本表相同 RDBMS实现视图查询的方法 视图消解法(View Resolution)
查询视图 用户角度: 查询视图与查询基本表相同 RDBMS实现视图查询的方法 视图消解法(View Resolution) 进行有效性检查 转换成等价的对基本表的查询 执行修正后的查询

195 查询视图(续) SELECT Sno,Sage [例9] 在信息系学生的视图中找出年龄小于20 岁的学生。 FROM IS_Student
[例9] 在信息系学生的视图中找出年龄小于20 岁的学生。 SELECT Sno,Sage FROM IS_Student WHERE Sage<20; IS_Student视图的定义 (参见视图定义例1)

196 查询视图(续) 视图消解转换后的查询语句为: SELECT Sno,Sage FROM Student
WHERE Sdept= 'IS' AND Sage<20;

197 查询视图(续) [例10] 查询选修了1号课程的信息系学生 SELECT IS_Student.Sno,Sname
[例10] 查询选修了1号课程的信息系学生 SELECT IS_Student.Sno,Sname FROM IS_Student,SC WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1';

198 查询视图(续) 视图消解法的局限 有些情况下,视图消解法不能生成正确 查询。

199 查询视图(续) [例11]在S_G视图中查询平均成绩在90分以上的学生 学号和平均成绩 SELECT * FROM S_G
WHERE Gavg>=90; S_G视图的子查询定义: CREATE VIEW S_G (Sno,Gavg) AS SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno;

200 查询转换 错误: 正确: SELECT Sno,AVG(Grade) FROM SC WHERE AVG(Grade)>=90
GROUP BY Sno; 正确: SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90;

201 3.6.3 更新视图(续) [例12] 将信息系学生视图IS_Student中学号 200215122的学生姓名改为“刘辰”。
UPDATE IS_Student SET Sname= '刘辰' WHERE Sno= ' '; 转换后的语句: UPDATE Student SET Sname= '刘辰' WHERE Sno= ' ‘ AND Sdept= 'IS';

202 更新视图(续) [例13] 向信息系学生视图IS_S中插入一个新的 学生记录:200215129,赵新,20岁。 INSERT
INTO IS_Student VALUES(‘95029’,‘赵新’,20); 转换为对基本表的更新: INTO Student(Sno,Sname,Sage,Sdept) VALUES(‘ ','赵新',20,'IS' );

203 更新视图(续) [例14] 删除信息系学生视图IS_Student中学 号为200215129的记录。 DELETE
FROM IS_Student WHERE Sno= ‘ ’; 转换为对基本表的更新: FROM Student WHERE Sno= ' ' AND Sdept= 'IS';

204 更新视图(续) 更新视图的限制:一些视图是不可更新的, 因为对这些视图的更新不能唯一地有意义地 转换成对相应基本表的更新。
例:视图S_G为不可更新视图。 UPDATE S_G SET Gavg=90 WHERE Sno= ‘ ’; 这个对视图的更新无法转换成对基本 表SC的更新。

205 更新视图(续) 允许对行列子集视图进行更新 对其他类型视图的更新不同系统有不同 限制

206 3.6.4 视图的作用 1. 视图能够简化用户的操作; 2. 视图使用户能以多种角度看待同一数据;
视图的作用 1. 视图能够简化用户的操作; 2. 视图使用户能以多种角度看待同一数据; 3. 视图对重构数据库提供了一定程度的逻辑 独立性; 4. 视图能够对机密数据提供安全保护; 5. 适当的利用视图可以更清晰的表达查询;

207 3.7 小结 SQL语言 数据定义 CREATE/SCHEMA/TABLE/INDEX/VIEW 数据查询 SELECT
3.7 小结 SQL语言 数据定义 CREATE/SCHEMA/TABLE/INDEX/VIEW 数据查询 SELECT 数据更新 INSERT/DELETE/UPDATE 数据控制 GRANT/REVOKE

208 下课了。。。 休息一会儿。。。


Download ppt "An Introduction to Database System"

Similar presentations


Ads by Google