第五章关系数据库SQL语言.ppt

1、,第5章 关系数据库标准语言SQL,(Structured Query Language简称SQL),本章重要概念,SQL数据库的体系结构，SQL的组成。 SQL的数据定义：SQL模式、基本表和索引的创建和撤销。 SQL的数据查询；SELECT语句的句法，SELECT语句的三种形式及各种限定，基本表的联接操作，SQL3中的递归查询。 SQL的数据更新：插入、删除和修改语句。 视图的创建和撤销，对视图更新操作的限制。 数据控制,本章概述,SQL是关系数据库的标准语言，对关系模型的发展和商用DBMS的研制起着重要的作用。 SQL语言是介乎于关系代数和元组演算之间的一种语言。本章详细介绍SQL的核心

2、部分内容：数据定义、数据查询、数据更新和数据控制,学习目的与要求,掌握SQL的功能特点体系。 掌握利用SQL进行数据定义、修改、删除等。 熟练掌握SQL语言中的SELECT完成数据的查询。 理解视图和数据控制的概念并会使用。,关系数据库语言SQL,5.1 SQL概述 5.2 数据定义 5.3 数据查询 5.4 数据更新 5.5 视图 5.6 数据控制,5.1 SQL概述,5.1.1 SQL的特点 5.1.2 SQL数据库的基本概念,SQL的发展历程（1）,1970年，美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型。 1972年，IBM公司开始研制实验型关系数据库管理系统SY

3、STEM R，配制的查询语言称为SQUARE (Specifying Queries As Relational Expression )语言，在语言中使用了较多的数学符号。 1974年，Boyce和Chamberlin把SQUARE修改为SEQUEL (Structured English QUEry Language )语言。后来SEQUEL简称为SQL (Structured Query Language )，即“结构化查询语言”，现在SQL已经成为一个标准 。 1986年10月，美国国家标准局(ANSI)颁布了美国标准的SQL语言。1987年，国际标准化组织(ISO)采纳为国际标准。这

4、两个标准合称为SQL86。,SQL发展历程(2),1989年4月，ISO颁布了增强完整性特征的SQL89标准。 此后，ISO对标准进行了大量的修改和扩充，在1992年推出了新的标准SQL2(或称为SQL92) SQL的标准化工作还在继续，新的标准已被命名为SQL3。,SQL标准的进展过程,标准 大致页数 发布日期 SQL/86 1986.10 SQL/89(FIPS 127-1) 120页 1989年 SQL/92 622页 1992年 SQL99 1700页 1999年 SQL2003 2003年,5.1.1 SQL 的特点,1.综合统一 集数据定义语言（DDL），数据操纵语言（DML），数

5、据控制语言（DCL）功能于一体。 可以独立完成数据库生命周期中的全部活动： 定义关系模式，插入数据，建立数据库； 对数据库中的数据进行查询和更新； 数据库重构和维护 数据库安全性、完整性控制等 用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。 数据操作符统一,2.高度非过程化,非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径 SQL只要提出“做什么”，无须了解存取路径。 存取路径的选择以及SQL的操作过程由系统自动完成。,3.面向集合的操作方式,非关系数据模型采用面向记录的操作方式，操作对象是一条记录 SQL采用集合操作方式 操作对象、查找结果可以是元组的集合 一

6、次插入、删除、更新操作的对象可以是元组的集合,4.以同一种语法结构提供多种使用方式,SQL是独立的语言 能够独立地用于联机交互的使用方式 SQL又是嵌入式语言 SQL能够嵌入到高级语言（例如C，C+，Java）程序中，供程序员设计程序时使用,5.语言简洁，易学易用,SQL功能极强，完成核心功能只用了9个动词,SQL的功能,数据定义（DDL-Data Description Language） 定义、删除、修改关系模式（基本表） 定义、删除视图（View） 定义、删除索引（Index） 数据操纵（DML-Data Manipulation Language） 数据查询 数据增、删、改 数据控制（

7、DCL- Data Control Language ） 用户访问权限的授予、收回,5.1.2 SQL 的基本概念,基本表 本身独立存在的表 SQL中一个关系就对应一个基本表 一个(或多个)基本表对应一个存储文件 一个表可以带若干索引 存储文件 逻辑结构组成了关系数据库的内模式 物理结构是任意的，对用户透明 视图 从一个或几个基本表导出的表 数据库中只存放视图的定义而不存放视图对应的数据 视图是一个虚表 用户可以在视图上再定义视图,SQL数据库的体系结构,关系数据库的三级模式实例,基本数据类型,数值型 字符串型 位串型 时间型,INTEGER 长整型(INT) SMALLINT 短整数 REA

8、L 浮点数 DOUBLE PRECISION 双精度浮点数 FLOAT(n) 浮点数，精度至少为n NUMERIC(m, n) 定点数，由m位数字组成，小数点后有n位数字,CHAR(n) 长度为n的定长字符串 VARCHAR(n) 最大长度为n的变长字符串,BIT(n) 长度为n的二进制位串 BIT VARYING(n) 最大长度为n的变长二进制位串,DATE 日期型，yyyy-mm-dd TIME 时间型，hh.mm.ss,5.2 数据定义,关系数据库系统支持三级模式结构，它的模式、外模式和内模式中的基本对象有表、视图和索引。因此SQL的数据定义功能包括定义表、定义视图和定义索引，如表5-1

9、所示。,5.2.1 定义、删除与修改基本表,1. 基本表的创建,CREATE TABLE ( 列级完整性约束条件 列级完整性约束条件 表级完整性约束条件 ) ;,由以下四种子句组成： PRIMARY KEY 主键约束 UNIQUE 唯一值约束 FOREIGN KEY 外键约束 CHECK 检查约束,【例-1】建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。,CREATE TABLE student(Sno CHAR(10) NOT NULLUNIQUE， Sname C

10、HAR(20) UNIQUE， Ssex CHAR(2) ， Sage INT， Sdept CHAR(15),5.2.1 定义、删除与修改基本表,5.2.1 定义、删除与修改基本表,2删除基本表,DROP TABLE ,删除指定的表，包括表结构与表记录。,例5-2 删除例5-1建立的学生表Student。,DROP TABLE Student,5.2.1 定义、删除与修改基本表,3. 修改基本表,ALTER TABLE ADD 完整性约束条件 DROP COLUMN ALTER COLUMN ;,增加新列和新的完 整性约束条件,删除指定的完整性 约束条件列,用于修改列名和数 据类型,对现有的

11、表设置外键,alter table enroll add constraint sno foreign key (sno) references student(sno) 删除外键 alter table enroll drop constraint sno,同步更新问题,CREATE TABLE 人员基本信息表( 身份证号 char(18) PRIMARY KEY ); CREATE TABLE 考核工资表_200801( ID int PRIMARY KEY, 身份证号 char(18) REFERENCES 人员基本信息表( 身份证号) ON UPDATE CASCADE ON DELE

12、TE CASCADE ); CREATE TABLE 考核工资表_200802( ID int PRIMARY KEY, 身份证号 char(18) REFERENCES 人员基本信息表( 身份证号) ON UPDATE CASCADE ON DELETE CASCADE ),5.2.1 定义、删除与修改基本表,例5-3 对例5-1建好的学生表Student增加一个列（字段）电话号码Stel。,ALTER TABLE Student ADD Stel Char(12),ALTER TABLE Student DROP COLUMN Stel,例5-4 删除上例加的电话号码Stel这个列（字段）

13、。,例5-5 将例5-1建好学生表Student中的所在系sdept的宽度调整为8。,ALTER TABLE Student ALTER COLUMN Sdept CHAR(8),5.2.2建立与删除索引,建立索引的目的：加快查询速度 谁可以建立索引 DBA 或 表的属主（即建立表的人） DBMS一般会自动建立以下列上的索引 PRIMARY KEY UNIQUE 谁 维护索引 DBMS自动完成 使用索引 DBMS自动选择是否使用索引以及使用哪些索引,索引,RDBMS中索引一般采用B+树、HASH索引来实现 B+树索引具有动态平衡的优点 HASH索引具有查找速度快的特点 采用B+树，还是HASH

14、索引 则由具体的RDBMS来决定 索引是关系数据库的内部实现技术，属于内模式的范畴 CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引,5.2.2建立与删除索引,1.索引的建立,CREATE UNIQUE CLUSTER INDEX ON (, )；,此索引的每一个索引值只对 应唯一的数据记录，所以若 选择此项则相应的列一定是 主关键字,表示要建立的索引是聚簇索 引，意为索引项的顺序是与 表中记录的物理顺序一致的 索引组织。,返回,5.2.2建立与删除索引,【例5-6】将例5-1建好学生表Student中的年龄age按升 序建立索引。,CREATE INDEX

15、 S_INDEX ON Student(age),【例5-7】将例5-6建好的索引删除。,DROP INDEX Student.S_INDEX,删除索引时，系统会从数据字典中删去有关该索引的描述。,5.3 查询,A1, , An(F (R1Rm),考察如下关系代数表达式中的常用形式：,在SQL中设计了如下SELECT-FROM-WHERE句型：,算术比较运算符 逻辑运算符 集合成员资格运算符 IN, NOT IN 聚合函数(集函数) AVG, MIN, MAX, SUM, COUNT SELECT语句,SELECT A1,An FROM R1,Rm WHERE F,5.3 查询,SELECT

16、ALL| DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC | DESC ,5.3.1 SELECT语句的一般格式,5.3.2 单表查询(1),例5-8 选择student表中的所有列。命令和结果如图5-1所示。,例5-9 选择student表中的sno,sname,department列。命令和结果如图5-2所示。,例5-10选择student表中所有系的名字，去掉重复行。命令和结果如图5-3（a）所示。,1、选择数据表中的列,5.3.2 单表查询(2),2选择表中的行（记录）,例5-11 现有student表，如例5-8图所示。查询所

17、有计算机系的学生。,5.3.2 单表查询(3),3、条件表达式的构成,常用查询条件,5.3.2 单表查询(4),（1）范围条件用BETWEENAND表示,【例5-12】在student表中，查询年龄在20岁到22岁之间的学生的姓名。查询命令和结果，如图5-5所示。,SELECT sname,age FROM student where age BETWEEN 20 AND 22,格式： NOT BETWEEN AND ,5.3.2 单表查询(5),（2）组属条件用IN表示,【例5-13】在student表中，查询家在湖南和山东的学生的学号，姓名，性别和籍贯。查询命令和查询结果，如图5-6（a）

18、所示。,SELECT sno,sname,sex,bplace FROM student WHERE bplace IN (湖南,山东),格式： NOT IN （）（）,5.3.2 单表查询(6),（2）组属条件用IN表示,【例5-14】在student表和如图5-16所示的enroll表中。查询成绩在80分以上的学生的学号和姓名。,SELECT sno，sname FROM student WHERE sno IN(SELECT sno FROM enroll WHERE grade80),格式： NOT IN （）（）,5.3.2 单表查询(7),（3）模式匹配条件用LIKE表示,【例5-

19、15】在student表中，查询所有姓张的学生。,SELECT * FROM student WHERE sname LIKE 张%,格式： NOT LIKE ,(4) 涉及空值的查询,谓词： IS NULL 或 IS NOT NULL “IS” 不能用 “=” 代替 练习1：某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno,Cno FROM enroll WHERE Grade IS NULL 练习2： 查所有有成绩的学生学号和课程号。 SELECT Sno,Cno FROM enroll WHERE Grade

20、IS NOT NULL；,5.3.2 单表查询(8),4.查询中集函数的使用,表5-3 常用集函数及其功能,函数SUM和AVG所涉及的属性必须是数值型的。 为消除重复值，可以在函数变量前加上DISTINCT。 特殊函数COUNT(*)用来统计元组数。,5.3.2 单表查询(9),4.查询中集函数的使用,例5-16 查询在student表中查询学生的总人数。查询命令如下：,SELECT COUNT(*) FROM student,例5-17 计算student表中学生的平均年龄。查询 命令如下:,SELECT AVG(age) FROM student,聚集函数 （续）,练习1： 查询选修了课程

21、的学生人数。 SELECT COUNT(DISTINCT Sno) FROM enroll； 练习2： 计算c1号课程的学生平均成绩。 SELECT AVG(Grade) FROM enroll WHERE Cno= c1 ； 练习3： 查询选修1号课程的学生最高分数。 SELECT MAX(Grade) FROM enroll WHER Cno= c1 ；,5.3.2 单表查询(10),5.查询结果分组,GROUP BY子句将查询结果表按指定列的值分组，值相等的为一组。分组的目的是为了将集函数的作用对象细化，分组后集函数将作用在每一个组上，也就是说每个组都有一个函数值。,例5-18 查询st

22、udent表中每个系在三个以上的学生的所在系。查询命令如下:,SELECT department FROM student GROUP BY department HAVING COUNT(*) =3,作用：把FROM子句中的关系按分组属性分为若干组，同一组内所有的元组在分组属性上具有相同值。 分组属性可以是单个属性，也可以是多个属性的组合。,HAVING与WHERE子句的 作用类似，不同之处： WHERE后面的谓词是对一 个元组的限制条件，而 HAVING子句后面的谓词则 是对一组元组的限制条件。,GROUP BY子句（续）,练习： 求各个课程号及相应的选课人数。 SELECT Cno,CO

23、UNT(Sno) FROM enroll GROUP BY Cno；,5.3.2 单表查询(11),6. 查询结果的排序,ORDER BY子句按其后所跟的列名将查询结果进行排序。查询结果将首先按的值排序，若该列的值相同，则再按的值排序，以此类推。其后带ASC表示按值的升序排列查询结果，若带DESC则按值的降序排列查询结果。若不指定排序方式，则默认按升序排列。,例 5-19 在student表中按年龄的升序查询出所有 学生的记录。查询命令,SELECT * FROM student ORDER BY age asc,ORDER BY子句 （续）,练习1： 查询选修了c3号课程的学生的学号及其成绩

24、，查询结果按分数降序排列。 SELECT Sno,Grade FROM enrolls WHERE Cno=c3 ORDER BY Grade DESC； 练习2： 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM Student ORDER BY department,age DESC；,5.3.3 连接查询,连接查询：同时涉及多个表的查询 连接条件或连接谓词：用来连接两个表的条件 一般格式： . . . BETWEEN . AND . 连接字段：连接谓词中的列名称 连接条件中的各连接字段类型必须是可比的，但名字不必是相同的,连接操

25、作的执行过程,嵌套循环法(NESTED-LOOP) 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。 重复上述操作，直到表1中的全部元组都处理完毕,连接操作示例,Relations R, S:,A,B, ,1 2 4 1 2,C,D, ,a a b a b,B,1 3 1 2 3,D,a a a b b,E, ,R,S,5.3.3 连接查询

26、(1),在实际应用中，查询所涉及的数据经常存在于多个表中，这时就涉及到两个表或两个以上的表的查询,course,enrolls,5.3.3 连接查询(2),【例5-20】查询所有学生所选的课程名称，查询命令如下：,SELECT sno,o,cname,grade FROM enroll,course WHERE o=o,【例5-21】找出所有成绩在90分以上的学生的姓名， 列出学号、姓名和成绩。查询命令如下：,SELECT student.sno,sname, grade FROM student,enroll WHERE student.sno=enroll.sno and grade=90

27、,5.3.3 连接查询(3),【例5-22】查询所有学生所选课程的成绩，列出课程名、学生学号和姓名。查询命令如下:,SELECT student.sno,sname, cname,grade FROM student,course,enroll WHERE student.sno=enroll.sno and o=o,等值与非等值连接查询 自身连接 外连接 复合条件连接,连接查询（补充）,例 查询每个学生及其选修课程号的情况。,SELECT Student.*, Enroll.* FROM Student, Enroll WHERE Student.Sno = enroll.Sno,连接条件（

28、连接谓词）,表名前缀,例 对上例用自然连接完成。,SELECT Student.Sno, Sname, sex, age, department, Cno, Grade FROM Student, enroll WHERE Student.Sno = enroll.Sno,仍需显式写出表达式,消除结果中的重复属性列,等值与非等值连接查询 自身连接 外连接 复合条件连接,例 查询与胡峰在同一个系学习的学生。,select s1.sno,s1.sname,s1.department from student s1,student s2 where s1.department=s2.departme

29、nt and s2.sname=胡峰,自连接,表的别名,等值与非等值连接查询 自身连接 外连接 复合条件连接,外连接（续）,左外连接 列出左边关系中所有的元组 右外连接 列出右边关系中所有的元组,外连接,外连接与普通连接的区别 普通连接操作只输出满足连接条件的元组 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出,试问下列SQL语句的运行结果是什么？,select student.sno,sname,grade from student full outer join enroll on student.sno=enroll.sno and grade90 select s

30、tudent.sno,sname,grade from student left outer join enroll on student.sno=enroll.sno and grade90 select student.sno,sname,grade from student right outer join enroll on student.sno=enroll.sno and grade90,等值与非等值连接查询 自身连接 外连接 复合条件连接（WHERE子句中有多个条件的连接操作）,5.3.4 嵌套查询,在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。将一个查

31、询块嵌套在另一个查询块的WHERE的条件中的查询称为嵌套查询。处于内层的查询称为子查询。嵌套查询命令在执行时，每个子查询在上一级查询处理之前求解，也就是从里向外查，先由子查询得到一组值的集合，外查询再从这个集合中得到新的查询条件的结果集。,嵌套查询(续),SELECT Sname /*外层查询/父查询*/ FROM Student WHERE Sno IN （SELECT Sno /*内层查询/子查询*/ FROM enroll WHERE Cno= c2 ）；,嵌套查询(续),子查询的限制 不能使用ORDER BY子句 层层嵌套方式反映了 SQL语言的结构化 有些嵌套查询可以用连接运算替代,

32、嵌套查询求解方法,不相关子查询： 子查询的查询条件不依赖于父查询 由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。,嵌套查询求解方法（续）,相关子查询：子查询的查询条件依赖于父查询 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表 然后再取外层表的下一个元组 重复这一过程，直至外层表全部检查完为止,5.3.4 嵌套查询,【例5-23】查询成绩在80分以下的学生名单。查询命令如下。,SELECT * FROM student WHERE sno IN( SELECT sno

33、 FROM enrolls WHERE grade80 ),5.3.4 嵌套查询,【例5-24】查询那些同学选修了数据库这门课。查询命令如下：,SELECT sno,sname FROM student WHERE sno IN(SELECT sno FROM enroll WHERE cno IN(SELECT cno FROM course WHERE cname=数据库) ）;,【例5-25】查询那些学生选修了三门以上的课程。查询命 令如下：,SELECT sno,sname FROM student WHERE sno IN(SELECT sno FROM enroll GROUP B

34、Y sno HAVING count(*)=3),带有IN谓词的子查询 带有比较运算符的子查询 带有ANY或ALL谓词的子查询 带有EXISTS谓词的子查询,嵌套查询(补充),SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM enroll WHERE Cno = c2 );,例 查询选修了课程c2的学生姓名。,子查询 (其中不能使用ORDER BY子句),带有IN谓词的子查询 带有比较运算符的子查询（,=.) 带有ANY或ALL谓词的子查询 带有EXISTS谓词的子查询,嵌套查询,当能确切知道内层查询返回单值时，可用比较运算符（，=

35、，）。 与ANY或ALL谓词配合使用,例 查询与“胡峰”在同一个系学习的学生。,SELECT Sno, Sname，department FROM Student WHERE department =,SELECT department FROM Student WHERE Sname=胡峰 ;,),(,子查询要在比较符之后,假设一个学生只可能在一个系学习，并且必须属于一个系， 则可以用 = 代替IN,带有比较运算符的子查询（续）,子查询一定要跟在比较符之后 错误的例子：,SELECT Sno, Sname，department FROM Student WHERE,(SELECT depar

36、tment FROM Student WHERE Sname=胡峰 )=department,SELECT S1.Sno, S1.Sname，S1.Sdept FROM Student S1,Student S2 WHERE S1.department=S2.department AND S2.Sname=胡峰 ;,可用（自身）连接完成上例查询,带有IN谓词的子查询 带有比较运算符的子查询 带有ANY或ALL谓词的子查询 带有EXISTS谓词的子查询,嵌套查询,带有ANY（SOME）或ALL谓词的子查询,谓词语义 ANY：任意一个值 ALL：所有值,带有ANY（SOME）或ALL谓词的子查询

37、（续）,需要配合使用比较运算符 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值 = ANY大于等于子查询结果中的某个值 = ALL大于等于子查询结果中的所有值 ）ANY不等于子查询结果中的某个值 !=（或）ALL不等于子查询结果中的任何一个值,例 查询其他系中比计算机系某一学生年龄小的学生姓名和年龄。,SELECT Sname, age FROM Student WHERE age 计算机 ORDER BY age DESC ;,MAX(age),通常效率要高！,带有IN谓词的子查询 带有比较运算符的子查询 带有ANY或ALL谓词的子查询 带有EXISTS谓词的子查询,嵌套查询

38、,带有EXISTS谓词的子查询(续）,1. EXISTS谓词 存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则外层的WHERE子句返回真值 若内层查询结果为空，则外层的WHERE子句返回假值 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义 2. NOT EXISTS谓词 若内层查询结果非空，则外层的WHERE子句返回假值 若内层查询结果为空，则外层的WHERE子句返回真值,例 查询所有选修了c1号课程的学生姓名。,思路分析： 本查询涉及Stu

39、dent和enroll关系 在Student中依次取每个元组的Sno值，用此值去检查enroll关系 若enrolls中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= c1，则取此Student.Sname送入结果关系,例 查询所有选修了c1号课程的学生姓名。,SELECT Sname FROM Student WHERE EXISTS ( SELECT * FROM enroll WHERE Sno=Student.Sno AND Cno=c1);,相关子查询 （子查询的查询条件依赖父查询的某个属性值）,例 查询所有没有选修c1号课程的学生姓名。,SELECT Sn

40、ame FROM Student WHERE NOT EXISTS ( SELECT * FROM enroll WHERE Sno=Student.Sno AND Cno=c1);,例 查询选修了全部课程的学生姓名。,SELECT Sname FROM Student WHERE NOT EXISTS ( SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM enroll WHERE Sno=Student.Sno AND Cno=Course.Cno);,找这样的学生：没有一门课程是他不选的！,例 查询至少选修了学生95022选修的全部课

41、程的 学生号码。,SELECT DISTINCT Sno FROM enroll X WHERE NOT EXISTS ( SELECT * FROM enroll Y WHERE Y.Sno=95022 AND NOT EXISTS (SELECT * FROM enroll Z WHERE Z.Sno=X.Sno AND Z.Cno=Y.Cno);,不存在这样的课：95022选了而我没选！,例：查询与“胡峰”在同一个系学习的学生。 可以用带EXISTS谓词的子查询替换： SELECT Sno,Sname,department FROM Student S1 WHERE EXISTS (SE

42、LECT * FROM Student S2 WHERE S2.department=S1.department AND S2.Sname = 胡峰),带有EXISTS谓词的子查询(续）,不同形式的查询间的替换 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换 用EXISTS/NOT EXISTS实现全称量词(难点) SQL语言中没有全称量词 （For all） 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： (x)P ( x( P),补充 集合查询 UN

43、ION、INTERSECT、 EXCEPT,集合操作的种类 并操作UNION 交操作INTERSECT 差操作EXCEPT 参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同,查询计算机科学系的学生及年龄不大于19岁的学生。,方法一： SELECT * FROM Student WHERE department= 计算机 UNION SELECT * FROM Student WHERE age=19；UNION：将多个查询结果合并起来时，系统自动去掉重复元组。UNION ALL：将多个查询结果合并起来时，保留重复元组,集合查询（续）,方法二： SELECT DISTINCT

44、* FROM Student WHERE department= 计算机 OR age=19；,集合查询（续）,例 查询选修了课程C1或者选修了课程C2的学生。 SELECT Sno FROM enroll WHERE Cno=c1 UNION SELECT Sno FROM enroll WHERE Cno= c2 ,集合查询（续）,例 查询计算机科学系的学生与年龄不大于19岁的学生的交集 SELECT * FROM Student WHERE department=计算机 INTERSECT SELECT * FROM Student WHERE age=19,集合查询（续）,例50 实际

45、上就是查询计算机科学系中年龄不大于19岁的学生 SELECT * FROM Student WHERE department= 计算机 AND age=19；,集合查询（续）,例 查询选修课程c1的学生集合与选修课程c2的学生集合的交集 SELECT Sno FROM enroll WHERE Cno=c1 INTERSECT SELECT Sno FROM enroll WHERE Cno=c2 ,集合查询（续）,例实际上是查询既选修了课程c1又选修了课程c2的学生 SELECT Sno FROM enroll WHERE Cno=c1 AND Sno IN (SELECT Sno FROM

46、 enroll WHERE Cno=c2),集合查询（续）,例 查询计算机科学系的学生与年龄不大于19岁的学生的差集。 SELECT * FROM Student WHERE department=计算机 EXCEPT SELECT * FROM Student WHERE age =19;,集合查询（续）,例实际上是查询计算机科学系中年龄大于19岁的学生 SELECT * FROM Student WHERE department=计算机 AND age =19 ；,内连接查询INNER JOIN ON,SELECT FROM INNER JOIN ON WHERE ,选择籍贯为山东学生的全

47、部成绩,SELECT s.*,e.sno,e.grade FROM Student as s inner join enroll as e on s.sno=e.sno where s.bplace=山东,查询所学每一门课程成绩均高于该课程平均成绩的学生信息,select a.SNO,a.SNAME,a.CNO,a.GRADE,b.该课程平均成绩 from ( select b.SNO,a.SNAME,b.CNO,b.GRADE from Student a right outer join enroll b on a.SNO = b.SNO ) a inner join (select cn

48、o, avg(a.GRADE) as 该课程平均成绩 from enroll a group by cno) b on o=o where a.grade=b.该课程平均成绩,内连接是用比较运算符比较表中的列值，返回符合连接条件的数据行，从而将两个表连接成一个新表,select distinct enroll.sno,sname,o,grade from course,enroll,student where o=o and student.sno=enroll.sno and grade(select avg(grade) from enroll where o=o),从student表中，

49、查询计算机系所有学生的学号、姓名和考试总成绩，并按照考试总成绩降序排序,SELECT sno,sname,(select sum(grade) from enroll e where e.sno=s.sno) as zcj FROM student s where department=计算机 order by zcj DESC,用内连接完成上题,SELECT sname,b.zcj FROM student a inner join (select sno,sum(grade) zcj from enroll group by sno) as b on b.sno=a.sno where d

50、epartment=计算机 order by zcj DESC,若计算机系学生中有没有选课的学生,SELECT student.sno,student.sname,sum(grade) zcj FROM student left outer join enroll on student.sno=enroll.sno where department=计算机 group by student.sno,student.sname order by zcj desc,5.4 数据更新,5.4.1 插入数据,5.4.2 修改数据,5.4.3 删除数据,5.4.1 插入数据,两种插入数据方式 1. 插入

51、元组 2. 插入子查询结果 可以一次插入多个元组,(1)单行插入语句,语句格式 INSERT INTO (，) VALUES ( ， ) 功能 将新元组插入指定表中,插入元组（续）,INTO子句 属性列的顺序可与表定义中的顺序不一致 没有指定属性列 指定部分属性列 VALUES子句 提供的值必须与INTO子句匹配 值的个数 值的类型,(2) 多行插入语句格式,语句格式 INSERT INTO ( ， ) 子查询； 功能 将子查询结果插入指定表中,插入子查询结果（续）,INTO子句(与插入元组类似) 子查询 SELECT子句目标列必须与INTO子句匹配 值的个数 值的类型,5.4.1 数据插入,

52、【例5-26】给例5-1建好的表Student插入学生王强的信息,INSERT INTO Student(sno,sname,sex,age,department) VALUES(2005012,王强,男,18,计算机),【例5-27】将学生马丽的信息插入Student表中。,INSERT INTO Student VALUES(2005062,马丽,女,19,国贸),【例5-28】对enrolls个表每个学生都要选修操作系统c2这门课，将选课信息加入表enrolls中。,INSERT INTO enrolls（sno,cno） SELECT sno,c2FROM student,插入子查询结

53、果（续）,练习：对每一个系，求学生的平均年龄，并把结果存入数据库。 第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15) /* 系名*/ Avg_age SMALLINT)； /*学生平均年龄*/,插入子查询结果（续）,第二步：插入数据 INSERT INTO Dept_age(Sdept，Avg_age) SELECT department，AVG(age) FROM Student GROUP BY department；,对每一个系，求学生的学生人数，并把结果存入数据库。,select department,count(*) as srs into t

54、emp from student group by department Create table temp as(select department,count(*) as srs from student group by department),UPDATE SET =，= WHERE ；,5.4.2 修改数据(1),UPDATE给出要修改的表 名。SET关键字后面给出表 中一些要修改的列及相应的 表达式，每个表达式的值就 是对应列被修改的新值,修改数据（续）,SET子句 指定修改方式 要修改的列 修改后取值 WHERE子句 指定要修改的元组 缺省表示要修改表中的所有元组,修改数据（续）

55、,三种修改方式 1. 修改某一个元组的值 2. 修改多个元组的值 3. 带子查询的修改语句,5.4.2 修改数据,【例5-29】给例5-1的student表中的所有学生的年龄加一岁。,UPDATE student SET age=age+1,【例5-30】 给enroll这个表中选修了操作系统这门课的学生 的成绩修改为60分。,UPDATE enroll SET grade=60 WHERE cno IN (SELECT cno FROM course WHERE cname=操作系统),利用INNER JOIN更新表中数据,update enroll set grade=100 from e

56、nroll inner join student on student.sno=enroll.sno where sname=刘文,带子查询的修改语句,练习： 将计算机科学系全体学生的成绩置零。 UPDATE enroll SET Grade=0 WHERE 计算机= (SELECT department FROM Student WHERE Student.Sno=enroll.Sno)；,UPDATE enroll SET grade=101 FROM student INNER JOIN enroll ON student.sno=enroll.sno where department=

57、计算机 UPDATE enroll SET grade=101 FROM student,enroll WHERE student.sno=enroll.sno and department=计算机,修改数据（续）,RDBMS在执行修改语句时会检查修改操作 是否破坏表上已定义的完整性规则 实体完整性 主码不允许修改 用户定义的完整性 NOT NULL约束 UNIQUE约束 值域约束,5.4.3 删除数据(1),DELETE FROM WHERE ；,从FROM所指定的表中删除 满足WHERE子句所指定条 件的元组。若无WHERE子 句，则表示删除指定表中的 所有元组，但不删除表结构 ，表结构仍在数据字典中,5.4.3 删除数据,功能 删除指定表中满足WHERE子句条件的元组 WHERE子句 指定要删除的元组 缺省表示要删除表中的全部元组，表的定义仍在字典中,删除数据（续）,三种删除方式 1. 删除某一个元组的值 2. 删除多个元组的值 3. 带子查询的删除语句,5.4.3 删除数据,【例