第3章 关系数据库标准语言.

Presentation on theme: "第3章 关系数据库标准语言."— Presentation transcript:

1 第3章 关系数据库标准语言

2 关系数据库语言 － SQL SQL(Structured Query Language)结构化查询语言，1974年Boyce和Chamberlin提出，首先在IBM 公司的关系数据库系统System R上实现。 特点：功能丰富、使用方便、灵活、语言简洁易学,应用系统范围广，统一标准。 1986年，ANSI数据库委员会X3H2批准了SQL作为数据库语言的美国标准，ISO随后也提出了同样的决定。

3 SQL概述 标准化 现状 有关组织 有关标准 大部分DBMS产品都支持SQL，成为操作数据库的标准语言 有方言，支持程度不同
ANSI(American Natural Standard Institute) ISO(International Organization for Standardization) 有关标准 SQL-86：“数据库语言SQL” SQL-89：“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持。 SQL-92：“数据库语言SQL”，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 SQL-3：正在讨论中的新的标准，将增加对面向对象模型的支持。 现状 大部分DBMS产品都支持SQL，成为操作数据库的标准语言 有方言，支持程度不同

4 INSERT，UPDATE，DELETE
SQL概述 主要特点： 1. 语言简洁、易学易用：核心功能只有9个动词，语法简单，接近英语。 SQL功能 操作符 数据查询 SELECT 数据定义 CREATE，ALTER ,DROP 数据操纵 INSERT，UPDATE，DELETE 数据控制 GRANT，REVOKE

5 SQL概述 主要特点： 2. 高度非过程化的语言：用户只需提出“干什么”，至于“怎么干”由DBMS解决；用户只需要早查询语句中提出需要什么，DBMS即可按路径存取，并把结果返回给用户。 3. 面向集合的语言：每一个SQL的操作对象是一个或多个关系，操作的结果也是一个关系。 4. 即可独立使用，又可嵌入到宿主语言中使用，具有自主型和宿主型两种特点。

6 SQL概述 5. 具有查询、操作、定义和控制四种语言一体化的特点。它只向用户提供一种语言，但该语言具有上述多种功能，且每中操作只需一种操作符。

7 SQL语言支持的关系数据库的三级模式结构
用户 View V1 View V2 外模式 Base Table B1 Base Table B2 Base Table B3 Base Table B4 模式 Stored File S1 Stored File S1 Stored File S1 Stored File S1 内模式 SQL语言支持的关系数据库的三级模式结构

8 SQL的功能 一、数据定义（DDL） 二、数据操纵（DML） 三、数据控制（DCL） 定义、删除、修改关系模式（基本表）
定义、删除视图（View） 定义、删除索引（Index） 二、数据操纵（DML） 数据查询 数据增、删、改 三、数据控制（DCL） 用户访问权限的授予、收回

9 一、SQL的数据定义（DDL） 数据定义语言（Data Definition Language） Create、Drop、Alter
定义一组关系（基本表）、说明各关系的信息 各关系的模式 各属性的值域 完整性约束 索引 安全性和权限 各关系在磁盘上的物理存储结构

10 1. 模式的创建和撤销 1.SQL模式的创建 模式定义为基本表的集合。定义模式实际上定义了一个命名空间，在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。 SQL模式由模式名和模式拥有者的用户名或帐号来确定 Create schema <模式名> authorization <用户名> 注意：在大多数DBMS中，采用creat database来代替SCHEMA。即创建数据库。

11 1. 模式的创建和撤销 [例1]：创建“学生-课程”模式（数据库） [例2]：创建一个名为“book”的数据库，其中文件均存储在D盘根目录
create database 学生_课程 [例2]：创建一个名为“book”的数据库，其中文件均存储在D盘根目录 Create Database book On(Name=book, FileName='d:\book.mdf') Log on(name=book_log, FileName='d:\book_log.ldf');

12 1.模式的创建和撤销 2.SQL模式的撤消（删除） 撤消SQL模式或数据库所属的基本表、视图等元素
drop schema <模式名> [cascade|restrict] Cascade(级联式方式）：执行drop语句时，把SQL模式下的基本表、视图、索引等所有元素全部撤消 Restrict(约束式方式）：执行drop语句时，只有当SQL模式中没有任何下属元素时，才能撤消模式，否则，拒绝执行 drop语句。 drop database <模式名> [cascade|restrit]

13 [例2] DROP database 学生_课程 CASCADE； 删除模式学生_课程 同时该模式中定义的表也被删除
1.模式的创建和撤销 [例2] DROP database 学生_课程 CASCADE； 删除模式学生_课程 同时该模式中定义的表也被删除

14 SQLServer提供的基本数据类型 类型 数据类型 描述 存储长度 统一编码字符型 Char(n) 1——8000字符 varchar
text 1——231字符 Nchar(n) 字符型数据,可识别4000个字符 Ntext 字符型数据,可识别10亿个字符 日期时间型 datetime —— 期间日期和时间 smalldatetime —— 期间的日期和时间 int/interger -231——231之间的整数 4Byte smallint -215——215之间的整数（-32768——32767） 2Byte tinyint 0——255之间的整数 1Byte Bigint —— 8Byte 整形 bit 0、1或NULL，用于描述类似yes或No 精确数值型 decimal ——1038-1范围内数值型数据，必须指定范围和精度 numberic 同decimal 货币型 money -9229亿——9229亿之间的货币，精确到货币单位的万分之一 8 Byte 近似数值型 Float(n) 近似数值类型。-1.79E308——1.79E308 Real 近似数值类型。-3.4E38——3.4E38 二进制数据类型 Binary[(n)] 长度可达8000Byte的定长二进制数据 image 存储变长二进制数据。最大可达20亿字节 特殊数据类型 timestamp 用于创建一个数据库范围内的唯一数码 uniqueidentifier 用于存储一个全局唯一标识

15 2.基本表的创建、修改和撤销 1、基本表的创建 create table 表名（
列名 数据类型 [default 缺省值] [not null] [，列名 数据类型 [default 缺省值] [not null]] …… [，primary key（列名 [，列名] …）] [，foreign key （列名 [，列名] …） references 表名 （列名 [，列名] …）] [，check（条件）]）

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

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

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

19 [例3] 建立“学生”表Student，学号是主码，姓名取值唯一。
2.基本表的创建、修改和撤销 [例3] 建立“学生”表Student，学号是主码，姓名取值唯一。 CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) UNIQUE, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) );

20 ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4),
2.基本表的创建、修改和撤销 [例4] 建立一个“课程”表Course CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT, FOREIGN KEY (Cpno) REFERENCES Course(Cno) );

21 2.基本表的创建、修改和撤销 [例5] 建立一个“学生选课”表SC CREATE TABLE SC (Sno CHAR(9),
Cno CHAR(4), Grade SMALLINT, PRIMARY KEY (Sno,Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) );

22 3.修改基本表 alter table 表名 [add 列名 类型] /* 增加新列*/ [drop 列名[cascade|restrict]] /*删除*/列 [modify 列名 类型] /*修改列定义*/ cascade：该列的视图和约束也要一起删除 restrict：没有视图和约束才能删除

23 3.修改基本表 [例6]向Student表增加“入学时间”列，其数据类型为日期型。
ALTER TABLE Student ADD S_entrance DATE; 不论基本表中原来是否已有数据，新增加的列一律为空值。  [例7]将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。 ALTER TABLE Student ALTER COLUMN Sage INT； [例8]增加课程名称必须取唯一值的约束条件。 ALTER TABLE Course ADD UNIQUE(Cname);

24 DROP TABLE <表名>［RESTRICT| CASCADE］； RESTRICT：删除表是有限制的。
4.删除基本表 DROP TABLE <表名>［RESTRICT| CASCADE］； RESTRICT：删除表是有限制的。 欲删除的基本表不能被其他表的约束所引用 如果存在依赖该表的对象，则此表不能被删除 CASCADE：删除该表没有限制。 在删除基本表的同时，相关的依赖对象一起删除

25 4.删除基本表 [例9] 删除Student表 DROP TABLE Student CASCADE ; 基本表定义被删除，数据被删除
表上建立的索引、视图、触发器等一般也将被删除

26 SQL的功能 一、数据定义（DDL） 二、数据操纵（DML） 三、数据控制（DCL） 定义、删除、修改关系模式（基本表）
定义、删除视图（View） 定义、删除索引（Index） 二、数据操纵（DML） 数据查询 数据增、删、改 三、数据控制（DCL） 用户访问权限的授予、收回

27 二、数据操纵（DML） 数据查询 数据增、删、改

28 查询的类型 1、单表查询 2、连接查询 3、嵌套查询

29 1.SQL的数据查询 二、数据操纵（DML） 基本句型 select A1 , A2 , … , An
from R1 , R2 , … , Rm where F  ∏A1 , A2 , … , An(p(r1  r2  …  rm))

30 1.SQL的数据查询 语句格式 [，<目标列表达式>] …
SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] … FROM <表名或视图名>[， <表名或视图名> ] … [ WHERE <条件表达式> ] [ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ]；

31 Select语句的含义 结果集 1、SELECT子句 根据 Select 子句中的属性列表，对上述结果作投影（π）操作
对 From 子句中的各关系，作笛卡儿积(×) 对 Where 子句中的逻辑表达式进行选择（σ）运算，找出符合条件的元组 结果集 查询操作的对象是关系，结果还是一个关系，是一个结果集，是一个动态数据集

32 对应于关系代数的投影运算，用以列出查询结果集中的期望属性 目标列形式：可以为列名，* ，算术表达式，聚合函数。
1、SELECT子句 说明： 对应于关系代数的投影运算，用以列出查询结果集中的期望属性 目标列形式：可以为列名，* ，算术表达式，聚合函数。 (1) “*”：表示“所有的属性”。 示例：检索学生的姓名，年龄和性别 SELECT Sno,Sage,Ssex FROM Student; (SELECT * FROM Student ；)

33 (2)、select中有含，， ， 的算术表达式
关系定义为：TEACHER(TNO,TNAME,SAL,EDEPT) 示例：给出所有老师的姓名及税后工资额。 select TNAME，SAL  from TEACHER

34 1、SELECT子句 重复元组的处理 语法约束：缺省为保留重复元组，也可用关键字all显式指明。若要去掉重复元组，可用关键字distinct或unique指明。 示例：找出所有选修了课程的学生。 select distinct sno from SC

35 格式old_name as new_name
1、SELECT子句 列的改名操作 格式old_name as new_name 为关系和属性重新命名，可出现在select和from子句中。 为结果集中的某个属性改名 使结果集更具可读性 例1： Select sno as 学号,cno as 课程号,grade as 成绩 From SC 例2: Select sno,sname,year(getdate())-Sage as birthday From Student

36 WHERE子句 说明：查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表所示。 查询条件 比较 确定范围
确定集合 字符匹配 空值 多重条件 谓词 =，>,<,>=,<=,!=,!<,NOT+上述比较运算 BETWEEN… AND Not BETWEEN AND IN,NOT IN LIKE,NOT LIKE IS NULL,IS NOT NULL AND,OR

37 WHERE子句 (1) 比较 示例：查IS系全体学生的名单 SELECT Sname FROM Student
WHERE Sdept=‘IS' 查所有年龄在20岁以下的学生姓名及其年龄 SELECT sname, Sage FROM Student WHERE Sage <20

38 WHERE子句 (2)确定范围 查询年龄在18至23岁之间的学生的姓名、系别、和年龄 SELECT sname, Sdept, Sage
FROM Studenr WHERE Sage BETWEEN 18 AND 23 与BETWEEN...AND...相对的谓词是 NOT BETWEEN...AND...

39 WHERE子句 (3) 确定集合 (谓词：IN <值表>, NOT IN <值表> )
查物理系（WL）、数学系（XS）和计算机系（JSJ）的学生的姓名和性别 SELECT Sname,Ssex,Sdept FROM Student WHERE Sdept IN ('WL','SX','JSJ') [例 ]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。 SELECT Sname,Ssex FROM Student Where Sdept not IN ('IS','Ma')

40 WHERE子句 (4)多重条件查询 逻辑运算符AND和OR可用来联结多个查询条件。如果这两个运算符同时出现在同一个WHERE条件子句中，则AND的优先级高于OR，但用户可以用括号改变优先级。 [例23] 查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept= 'CS' AND Sage<20；

41 WHERE子句 谓词： IS NULL 或 IS NOT NULL “IS” 不能用 “=” 代替
[例21] 某些学生选修课程后没有参加考试，所以有选课记录，但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL [例22] 查所有有成绩的学生学号和课程号。 WHERE Grade IS NOT NULL；

42 WHERE子句 (6) 字符串的匹配操作like like：找出满足给定匹配条件的字符串。 格式：列名 [not] like “字符串”
匹配规则： “%” ：匹配零个或多个字符。 “＿”：匹配任意单个字符。 escape ：定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待。如escape “\”，是定义了 \ 作为转义字符，则可用\%去匹配%，用\＿去匹配＿，用\ \去匹配 \ 。 大小写敏感

43 WHERE子句 示例：列出姓刘的学生的学号、姓名。 Select sno，sname From Student
Where sname LIKE ‘刘%’ Sname Sno Sage Ssex Sdept 刘勇 95001 22 男 Cs 张新 95002 21 女 刘晨 95003 If 李立 95004 23 if Sname Sno Sage Ssex Sdept 刘勇 95001 22 男 Cs 刘晨 95003 女 If

44 WHERE子句 示例 例：列出张姓且单名的学生的学号、姓名。 Select sno，sname From Student
Where sname LIKE ‘张_ ’

45 WHERE子句 查以”DB_”开头，且倒数第三个字符为i的课程的详细情况
SELECT * FROM Course WHERE Cname LIKE ’DB\_%i__’ ESCAPE ’\’; 注意这里的匹配字符串’DB\_%i__’。第一个_前面有换码字符\，所以它被转义为普通的_字符。而%、第二个_和第三个_前面均没有换码字符\，所以它们仍作为通配符。其执行结果为： Cno Cname Ccredit DB_Design DB_Programing 13  DB_DBMS Design

46 WHERE子句 转义符： escape 例：列出课程名称中带有‘_’的课号及课名。 Select cno，cname From Course
Where cname LIKE ‘%\_%’ escape ‘\’ 列出名称中含有4个字符以上，且倒数第3个字符是d，倒数第2个字符是_的课程的所有信息。 select * from c where CNAME like “% ＿d \＿ ＿”

47 聚合函数 为了进一步方便用户，增强检索功能，SQL提供了许多聚合函数，主要包括： COUNT([DISTINCT|ALL] *) 统计元组个数 COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数 SUM([DISTINCT|ALL] <列名>) 计算一列值的总和（此列必须是数值型） AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值（此列必须是数值型） MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值 MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值

48 聚合函数 1、求男学生的总人数和平均年龄 举例：统计选修了课程的学生人数
SELECT COUNT(*),AVG(SAGE) FROM Student WHERE SSEX=‘男’ 举例：统计选修了课程的学生人数 SELECT COUNT(DISTINCT S#) FROM SC 举例：如果有一个grade值为空下面的结果相同吗？ SELECT count(grade) as 成绩人数, count(*) as 总人数 , avg(grade) as 平均分数 from sc

49 聚合函数 示例 列出老师的最高、最低、平均工资。 select DNO，max(SAL)，min(SAL)，avg(SAL)
from TEACHER

50 聚合函数 Null在聚集函数中的角色 Count 为 0 其余均忽略 Null Sum：不将其计入 Avg：具有 Null 的元组不参与
Max / Min：不参与 举例： SELECT count(sno),count(sname) from s

51 Group By字句 GROUP BY子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。 　　 对查询结果分组的目的是为了细化集合函数的作用对象。如果未对查询结果分组，集合函数将作用于整个查询结果，即整个查询结果只有一个函数值。如果用GROUP分了组，集合函数将作用于每一个组，即每一组都有一个函数值。

52 Group By字句 查询各个课程号以及相应的选课人数 GROUP BY举例：
该SELECT语句对SC表按Cno的取值进行分组，所有具有相同Cno值的元组为一组，然后对每一组作用集函数COUNT以求得该组的学生人数。 查询为： SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno

53 Having 可以针对聚合函数的结果值进行筛选，它是作用于分组计算的结果集
跟在Group By子句的后面，有GROUP BY 才有HAVING 例：列出选修两门（含）以上课程的学生的学号 SELECT sno FROM SC GROUP BY sno HAVING COUNT(cno)>=2

54 Having 例：列出具有两门（含）以上不及格的学生的学号、不及格的课目数。 Select sno,count(cno) From SC
只选出有不及格的学生 Select sno,count(cno) From SC Where grade < 60 Group By sno Having count(cno) >= 2

55 Having 列出及格的学生的平均成绩。 select SNO,avg(GRADE) from SC group by SNO
having min(GRADE) >= 60

56 Having Having 与 Where的区别 Where 决定哪些元组被选择参加运算，作用于关系中的元组

57 命令order by 列名 [asc | desc] 示例
SELECT Sno,GRADE FROM SC WHERE Cno='C1' ORDER BY GRADE DESC,Sno; ORDER BY 子句表示结果要排序,它必须在所有其它子句之后作为最后一个子句出现

58 练习： 查询男生和女生的平均年龄 统计各系部学生的人数
Select Ssex,AVG(sage) as 平均年龄 From Student Group By Ssex select Sdept,count(sno) as 系部人数 from student group by Sdept

59 查询的类型 1、单表查询 2、连接查询 3、嵌套查询

60 select student.sno,sname from student,sc,course
连接查询 在查询中，同时涉及两个或两个以上的表，要根据表中数据的情况作连接。 示例：查询学习课程名为‘MATHS’的学生学号和姓名。 select student.sno,sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and cname='MATHS'

61 等值连接和自然连接 例：查询所有学生的选课情况 等值连接： 去除重复的列 内连接 查看连接查询执行过程
SELECT * FROM Student,SC where (Student.Sno=SC.Sno); 去除重复的列 SELECT student.sno,sname,cno,grade FROM Student,SC where (Student.Sno=SC.Sno); 内连接 FROM Student inner join SC on (Student.Sno=SC.Sno);

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

63 [例]查询每一门课的间接先修课（即先修课的先修课）
自身连接 [例]查询每一门课的间接先修课（即先修课的先修课） SECOND表（Course表） FIRST表（Course表） Cno Cname Cpno Ccredit 1 数据库 5 4 2 数学 3 信息系统 操作系统 6 数据结构 7 数据处理 PASCAL语言 Cno Cname Cpno Ccredit 1 数据库 5 4 2 数学 3 信息系统 操作系统 6 数据结构 7 数据处理 PASCAL语言

64 自身连接 SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno; 查询结果：

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

66 外连接 在SQLServer中执行如下命令：左外连接 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

67 列出左边关系（如本例Student）中所有的元组 右外连接 列出右边关系中所有的元组
左外连接 列出左边关系（如本例Student）中所有的元组 右外连接 列出右边关系中所有的元组 请大家 写出查询命令来完成如下右外连接 SELECT sc.sno,Grade,course.cno,course.Cname,course.Ccredit FROM SC right OUTER JOIN course ON (sc.Cno=Course.Cno);

68 查询的类型 1、单表查询 2、连接查询 3、嵌套查询

69 嵌套查询 嵌套查询 一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

70 带有IN谓词的子查询 例 检索学习课程号为2的学生姓名 SELECT Sname /*外层查询/父查询*/ FROM Student
例　检索学习课程号为2的学生姓名 SELECT Sname /*外层查询/父查询*/ FROM Student WHERE Sno IN （SELECT Sno /*内层查询/子查询*/ FROM SC WHERE Cno= ' 2 '）；

71 带有IN谓词的子查询 [例 ]查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno，Sname ③ 最后在Student关系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno ② 然后在SC关系中找出选 FROM SC 修了3号课程的学生学号 WHERE Cno IN (SELECT Cno ① 首先在Course关系中找出 FROM Course “信息系统”的课程号，为3号 WHERE Cname= ‘信息系统’ ) );

72 带有IN谓词的子查询 练习２： 列出同时选修了1号和2号课程的学生的学号 练习１：查询没有选修课程的所有学生的学号和姓名
Select sno,sname From student Where sno NOT IN ( select distinct s# From sc) 练习２： 列出同时选修了1号和2号课程的学生的学号 select SNO from SC where SC.CNO = '1‘　 and　 SNO 　 in (select SNO from SC where　 CNO = '2')

73 不相关子查询： 子查询的查询条件不依赖于父查询
由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 上述带有in的子查询都属于不相关子查询

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

75 假设一个学生只可能在一个系学习，并且必须属于一个系，则在[例]可以用 = 代替IN ：
带有比较运算符的子查询 假设一个学生只可能在一个系学习，并且必须属于一个系，则在[例]可以用 = 代替IN ： = 例：查询和刘晨在同一个系学习的学生学号姓名和系部。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept in (SELECT Sdept WHERE Sname= ‘刘晨’)；

76 带有比较运算符的子查询 ［例 ］找出每个学生超过他自己选修课程平均成绩的课程号。 SELECT Sno， Cno FROM SC x
WHERE Grade >=(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno); 相关子查询 in后的子查询与外层查询无关，每个子查询执行一次，称之为无关子查询，而这里的子查询与外层查询有关，需要执行多次，称之为相关子查询

77 带有ANY（SOME）或ALL谓词的子查询
是比较运算符 表达式的值至少与子查询结果中的一个值相比满足比较运算符 表达式  all （子查询） 表达式的值与子查询结果中的所有的值相比都满足比较运算符

78 带有ANY（SOME）或ALL谓词的子查询
前面所学in可以等效与=some 例 ：检索学习课程号为2的学生学号与姓名 Select sno,sname from Student where sno in (select sno from sc where cno=‘2’) Select sno,sname from Student where sno =some (select sno from sc where cno=‘2’)

79 带有ANY（SOME）或ALL谓词的子查询
例 ：检索至少有一门成绩超过学生 一门成绩的学生学号 select distinct sno from sc where grade >some (select grade from sc where sno=‘ ’) 例 ：检索不学2号课程的学生姓名 可以用not in Select sname from student where sno<>all　(select sno from sc where cno='2')

80 带有ANY（SOME）或ALL谓词的子查询
例 ：找出平均成绩最高的学生号 select SNO，avg( Grade) from SC group by SNO having avg( Grade) >= all （select avg(Grade) from SC group by SNO） 求出每个人的均分

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

82 例：查询和“刘晨”同学在同一个系部的 学生的学号、姓名、系部 SELECT Sno，Sname，Sdept FROM Student S1
带有EXISTS谓词的子查询 例：查询和“刘晨”同学在同一个系部的 学生的学号、姓名、系部 SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S1.Sdept=S2. Sdept AND S2.Sname= ‘ 刘晨 ')； 动画演示

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

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

85 带有EXISTS谓词的子查询 [例 ] 查询选修了全部课程的学生姓名。
在表Student中找学生，在Course中不存在一门课，该学生没有学。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ） ）； 请思考还有没有别的实现思路？

86 参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同
集合查询 集合操作的种类 并操作UNION 交操作INTERSECT 差操作EXCEPT 参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同 采用集合的观点，可以多一种解决思路

87 集合查询 例：查询姓“张”的学生及年龄不大于19岁的学生 Select sno,sname as Name ,age From S Where sname like '张%' Union Select sno,sname,age Where age <=19 Order By age

88 集合查询 示例 求选修了001或002号课程的学生号。 (select SNO from SC where CNO = 'c1')
union where CNO = 'c2')

89 求选修了001和002号而没有选003号课程的学生号。 集合查询 （select SNO from SC
where CNO = ‘C1’ or CNO = ‘C2’ ） except where CNO = ‘C3’）

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

91 二、数据操纵（DML） 数据查询 数据维护： 增 删 改

92 数据维护——插入数据 1、插入单个元组 插入单个元组的INSERT语句的格式为： INSERT INTO <表名> [(<属性列1>[,<属性列2>...)] VALUES (<常量1> [,<常量2>]...) 如果某些属性列在INTO子句中没有出现，则新记录在这些列上将取空值。但必须注意的是，在表定义时说明了NOT NULL的属性列不能取空值。否则会出错。 如果INTO子句中没有指明任何列名，则新插入的记录必须在每个属性列上均有值。

93 2、数据维护——插入数据 示例：一次只插入一个元组 按关系模式的属性顺序 按指定的属性顺序，也可以只添加部分属性（非Null属性为必需）
Insert Into S Values ('s8','张三',21,'M','JSJ') 按指定的属性顺序，也可以只添加部分属性（非Null属性为必需） Insert Into S( sno, sname, age) Values ('S11','李四', 26 )

94 数据维护——插入数据 2、插入子查询结果 子查询不仅可以嵌套在SELECT语句中，用以构造父查询的条件，也可以嵌套在INSERT语句中，用以生成要插入的数据。 插入子查询结果的INSERT语句的格式为： INSERT INTO <表名> [(<属性列1> [,<属性列2>...)] 子查询； 其功能是以批量插入，一次将子查询的结果全部插入指定表中。

95 数据维护——插入数据 首先要在数据库中建立一个新表csstudent ，然后执行 insert into csstudent select * from student where sdept='cs'

96 2、数据维护——数据删除 删除语句的一般格式为： DELETE FROM <表名> [WHERE <条件>]; DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，表示删除表中全部元组，但表的定义仍在字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。

97 2、数据维护——数据删除 以元组为单位删除数据 一般只对一个关系进行操作，若要从多个关系中删除元组，则必须对每个关系分别执行删除命令
Delete From r [ Where P ]

98 2、数据维护——数据删除 删除单个元组 例：删除学号为S11的学生的选课信息 Delete From SC Where sno = ‘S11’ 删除多个元组 例：删除选课而未参加考试的学生的选课信息 Where grade is null

99 2、数据维护——数据删除 删除整个关系中的所有数据 例：删除所有学生的选课信息 Delete From SC
例：删除所有比95001年龄小的学生记录 Delete From Student Where sage < (select sage from student where sno = '95001')

100 2、数据维护——数据删除 删除低于平均工资的老师记录。 delete from PROF
where SAL < (select avg(SAL) from PROF)

101 2、数据维护——数据修改 命令 update 表名 set 列名 = 表达式 | 子查询 列名 = [，表达式 | 子查询]…
[where 条件表达式] 指定对哪些列进行更新，以及更新后的值是什么。 其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用<表达式>的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。

102 2、数据维护——数据修改 针对符合条件的元组,修改其属性值 示例：将S1学生转入HX系 Update S Set dept = 'HX'
Where sno = 'S1‘ 示例：所有学生年龄加1 Update Stuudent Set Sage = Sage + 1

103 例：将选修C1课程的学生的成绩改为该课程的平均成绩
2、数据维护——数据修改 例：将选修C1课程的学生的成绩改为该课程的平均成绩 Update SC Set grade = (Select avg(grade) From SC Where cno = 'C1') Where cno = 'C1' 先计算avg，再做Update

104 视图 简化用户的操作 不同的用户可从不同的角度看待同一数据 支持一定的逻辑数据独立性 数据的安全性

105 子查询不允许含有ORDER BY子句和DISTINCT短语
一、建立视图 语句格式(属于DDL，数据定义语言) CREATE VIEW <视图名> [(<列名> [，<列名>]…)] AS <子查询> [WITH CHECK OPTION]； 组成视图的属性列名：全部省略或全部指定 子查询不允许含有ORDER BY子句和DISTINCT短语

106 一、建立视图 [例1] 建立信息系学生的视图。并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。
[例1] 建立信息系学生的视图。并要求进行修改和插入操作时仍需保证该视图只有信息系的学生 。 Create View Cs_Student (学号,姓名,年龄,系部) As Select sno,sname,sage,Sdept From Student Where sdept=‘CS‘ WITH CHECK OPTION; Create View Cs_Student As Select sno,sname,sage,Sdept From Student Where Dept=‘CS‘ WITH CHECK OPTION; 若省略视图的列名表，则自动获得Select查询返回的列名

107 一、建立视图 基于多个基表的视图 [例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'；

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

109 查询视图: 与基本表的查询相同 更新视图 对其他类型视图的更新不同系统有不同限制
如果视图是从单个基本表只使用选择、投影操作导出的，并且包含了基本表的主键，那么这样的视图称为“行列子集视图” 不是所有视图都是可更新的 基于联接查询的视图不可更新 使用了函数的视图不可更新 使用了分组操作的视图不可更新 只有“行列子集视图” 才是可更新的 对其他类型视图的更新不同系统有不同限制

110 索引 对于一个基本表，根据需要建立若干个索引 建立索引的两个主要目的：加快查询速度、保证行的唯一性 1. 建立索引 语法格式：
CREATE [UNIQUE] INDEX <索引名> ON <表名>(<列名>[{,<列名>}]) 若指定[UNIQUE]选项,系统将保证基本表中的任意两行记录在索引列上或组合的索引列上具有唯一值 例：为SC表在SID和CID列上建立唯一索引 CREATE UNIQUE INDEX SC1 ON SC(SID,CID);

111 索引 的 定义和撤销 unique（distinct）：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新。 asc/desc：索引表中索引值的排序次序，缺省为asc。 示例： create index s-index on S（SID）

112 索引的定义和撤销 索引的有关说明 2. 删除索引 可以动态地定义索引，即可以随时建立和删除索引。
DROP INDEX <索引名> 索引的有关说明 可以动态地定义索引，即可以随时建立和删除索引。 不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性。 应该在使用频率高的、经常用于连接的列上建索引。 一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率。