关系数据库标准语言.ppt

1、2021年3月10日5时30分,周口师范学院计算机科学系,1,第3章 关系数据库标准语言SQL,2021年3月10日5时30分,周口师范学院计算机科学系,2,在数据库系统中，数据查询是最常用的操作。SQL是目前使用最广泛的数据库语言。利用SQL可以通过功能强大而又简捷的查询语言与数据库打交道，从数据库中得到想要的数据。 SQL语句主要被用于进行数据库的查询、定义、操纵和控制几个方面，是一种功能齐全的数据库语言。 SQL的数据定义是指对关系模式一级的定义。数据操纵是指对关系中的具体数据进行增、删、改等更新操作。数据控制是指对数据访问权限的授予与取消,2021年3月10日5时30分,周口师范学院计

2、算机科学系,3,3.1 SQL语言基本知识 3.1.1 SQL的发展史 1974年IBM圣约瑟实验室的Boyce和Chamberlin为关系数据库管理系统System-R设计的一种查询语言，当时称为SEQUEL语言（Structured English Query Language），后简称为SQL。 1981年IBM推出关系数据库系统SQL/DS后，SQL得到了广泛应用。 1986年美国国家标准协会（ANSI）公布了第一个SQL标准SQL86。 1987年，ISO通过SQL86标准,2021年3月10日5时30分,周口师范学院计算机科学系,4,1989年，ISO制定SQL89标准，SQL89

3、标准在SQL86基础上增补了完整性描述。 1990年，我国制定等同SQL89的国家标准。 1992年，ISO制定SQL92标准，即SQL2。 1999年，ANSI制定SQL3标准。 SQL成为国际标准后，对数据库以外的领域产生了很大的影响，有不少软件产品将SQL语言的数据查询功能与图形功能、软件工程工具、软件开发工具、人工智能程序结合起来。SQL已成为数据库领域中一个主流语言,2021年3月10日5时30分,周口师范学院计算机科学系,5,在许多软件产品中，软件厂商都对SQL的基本命令集进行了扩充，将其扩展成嵌入式SQL语言。SQL Server 2000中使用Transact-SQL语言与数据

4、库服务器打交道,2021年3月10日5时30分,周口师范学院计算机科学系,6,3.1.2 SQL的特点 SQL具有如下特点： 1综合统一。 2高度非过程化。 3面向集合的操作方式。 4以同一种语法结构提供两种使用方式 5. 语言简捷、易学易用。 SQL语言功能极强，但由于设计巧妙，语言十分简捷，完成核心功能只用了9个动词，如下表示，SQL语言接近英语口语，因此容易学习，容易使用,2021年3月10日5时30分,周口师范学院计算机科学系,7,SQL语言的动词,2021年3月10日5时30分,周口师范学院计算机科学系,8,SQL语言支持关系数据库三级模式结构，如下图示。其中外模式对应于视图和部分基

5、本表，模式对应于基本表，内模式对应于存储文件。 基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一个（或多个）基本表对应一个存储文件。一个表可以带若干个索引，索引也存放在存储文件中。 存储文件的逻辑结构组成了关系数据库的内模式，存储文件的物理结构是任意的，对用户是透明的。 视图是从一个或几个基本表导出的表。它本身不独立存储在数据库中，是一个虚表。即数据库中只存放视图的定义而不存放视图对应的数据,2021年3月10日5时30分,周口师范学院计算机科学系,9,外模式,内模式,模式,2021年3月10日5时30分,周口师范学院计算机科学系,10,3.1.3 SQL的分类 1、数据定义语言（

6、DDL：Data Definition Language） 创建、修改或删除数据库中各种对象，包括表、视图、索引等。 2、查询语言（QL：Query Language） 按照指定的组合、条件表达式或排序检索已存在的数据库中的数据，但并不改变数据库中数据。 3、数据操纵语言（DML：Data Manipulation Language ） 对已经存在的数据库进行记录的插入、删除、修改等操作,2021年3月10日5时30分,周口师范学院计算机科学系,11,4、数据控制语言（DCL：Data Control Language） 用来授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对

7、数据库进行监视 注意：在书写各种SQL 命令时，命令中所涉及的标点符号，如括号、逗号、分号、圆点（英文句号）等都应是英文半角，如果写成中文全角符号，则会在执行命令时出错,2021年3月10日5时30分,周口师范学院计算机科学系,12,3.2 数据定义 数据定义命令用于建立数据库和建立、修改、 删除基本表,SQL的数据定义语句,2021年3月10日5时30分,周口师范学院计算机科学系,13,3.2.1 定义、删除与修改基本表 一、定义基本表 建立数据库最重要的一步就是定义一些基本表。SQL语言使用CREATE TABLE 语句定义基本表，其一般格式如下： CREATE TABLE （列级完整性约

8、束条件 列级完整性约束条件表级完整性约束条件）; 其中是所要定义的基本表的名字，它可以由一个或多个属性（列）组成,2021年3月10日5时30分,周口师范学院计算机科学系,14,建表的同时通常还可以定义与该表有关的完整性约束条件。这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级上也可以定义在表级上,2021年3月10日5时30分,周口师范学院计算机科学系,15,例1 建立一个“学生”表Student,它由学号Sno,姓名Sname,性别Ssex,年

9、龄Sdept、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。 CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20) UNIQUE, Ssex CHAR(1), Sage INT, Sdept CHAR(15); 其中：NOT NULL指的是该列的值不能为空值, UNIQUE指的是该列的值唯一,2021年3月10日5时30分,周口师范学院计算机科学系,16,Student,2021年3月10日5时30分,周口师范学院计算机科学系,17,例2：建立如下表所示学生基本情况表的命令是： cr

10、eate table jbqk (number char(8)not null, name char(8)not null, sex char(2)not null, Birthday datetime, department char(12,jbqk,2021年3月10日5时30分,周口师范学院计算机科学系,18,2021年3月10日5时30分,周口师范学院计算机科学系,19,建立课程表的命令是： create table course(c_number char(4)not null, c_name char(20) not null, period decimal(3,0), t_num

11、ber char(4) not null); 建立教师表（teacher）的命令是:： create table teacher(t_number char(4) not null, t_name char(8), title char(10); 建立选课表（sle_course）的命令是:： Create table sle_course(s_number char(8) not null, c_number char(4), score decimal(3,0,2021年3月10日5时30分,周口师范学院计算机科学系,20,二、修改基本表 随着应用环境和应用需求的变化，有时需要修改已建立好

12、的基本表，SQL语言使用ALTER TABLE 语句定义基本表，其一般格式如下： ALTERTABLE ADD 列级完整 性约束条件 DROP MODIFY,2021年3月10日5时30分,周口师范学院计算机科学系,21,例1：向Student表中增加“入学时间”列，其数据类型为日期型。 ALTER TABLE Student ADD Scome DATE; 不论基本表中原来是否已有数据，新增加的列一律为空值。 例2：将Student表中年龄的数据类型改为短整型 ALTER TABLE Student MODIFY Sage SMALLINT,2021年3月10日5时30分,周口师范学院计算机

13、科学系,22,例3 将Student表中学生姓名必须取唯一值的约束删除 ALTER TABLE Student DROP UNIQUE(Sname); SQL没有提供删除属性列的语句，用户只能间接实现这一功能，即先把表中要保留的列及其内容复制到一个新表中，然后删除原表，再将新表重命名为原表名,2021年3月10日5时30分,周口师范学院计算机科学系,23,三、 数据表的删除 DROP TABLE ; 例4：删除Student表，使用如下命令格式： DROP TABLE Student; 注意： 基本表定义一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除掉。因此执行删除基本表的操作一定

14、要格外小心,2021年3月10日5时30分,周口师范学院计算机科学系,24,3.2.2 索引的建立与删除 索引是对数据库表中一个或多个列的值进行排序的结构。可以利用索引快速访问数据库表中的特定信息。 一、建立索引语句 CREATE UNIQUECLUSTER INDEX ON (列名次序,列名次序); 说明：索引可以建立在一列和多列之上，索引顺序可以是ASC（升序）或DESC（降序），缺省值是ASC(升序) 。UNIQUE表示每一个索引值对应唯一的数据记录,2021年3月10日5时30分,周口师范学院计算机科学系,25,UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。 CLUSTER

15、表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。 例如，执行下面的索引语句： CREATE CLUSTER INDEX Stusname ON Student(Sname); 将会在Student表的Sname列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放,2021年3月10日5时30分,周口师范学院计算机科学系,26,用户可以在最常查询的列上建立聚簇索引以提高查询效率。显然在一个基本表上最多只能建立一个聚簇索引。建立聚簇索引后，更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立

16、聚簇索引,2021年3月10日5时30分,周口师范学院计算机科学系,27,例1：为学生数据库中的Student,Course,SC三个表建立索引，其中，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,2021年3月10日5时30分,周口师范学院计算机科学系,2

17、8,Student,a,设有一个学生-课程数据库，包括学生关系Student,课程关系Course和选修关系SC。如下图示,68,2021年3月10日5时30分,周口师范学院计算机科学系,29,Course,b,2021年3月10日5时30分,周口师范学院计算机科学系,30,c,SC,2021年3月10日5时30分,周口师范学院计算机科学系,31,二、删除索引 索引一经建立，就由系统使用和维护它，不需用户干预。建立索引是为了减少查询操作时间，但如果数据增、删、改频繁，系统会花费许多时间来维护索引，这时，可以删除一些不必要的索引 DROP INDEX ; 注意：该命令不能删除由CREATE TA

18、BLE或者ALTER TABLE命令创建的主键和唯一性约束索引，也不能删除系统表中的索引。 例2、删除例1中建立的索引Stusno DROP INDEX Stusno,2021年3月10日5时30分,周口师范学院计算机科学系,32,3.3 数据查询语言,数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能,2021年3月10日5时30分,周口师范学院计算机科学系,33,SQL语言的查询语句一般格式是： SELECT ALL|DISTINCT , FROM , WHERE GROUP BY HAVING ORDER BY ASC|D

19、ESC; 下面对该命令进行一些说明,2021年3月10日5时30分,周口师范学院计算机科学系,34,1命令含义 从FROM子句指定的基本表或视图中，根据WHERE子句的条件表达式查找出满足该条件的元组（记录），再按照SELECT子句指定的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP BY子句，则将结果按“列名1”的值进行分组，该属性列值相等的元组为一个组；如果GROUP BY子句带有短语HAVING，则只有满足短语指定条件的分组才会输出。如果有ORDER BY子句，则结果表要按照的值进行升序和降序排列,2021年3月10日5时30分,周口师范学院计算机科学系,35,SELECT

20、 ALL|DISTINCT实现的是对表的投影操作， WHERE 中实现的是选择操作。 FROM ,实现的是对指定表的广义笛卡尔积,2021年3月10日5时30分,周口师范学院计算机科学系,36,2目标列表达式 列表达式可以是“列名1,列名2”的形式；如果FROM子句指定了多个表，则列名应是“表名.列名”的形式。 列表达式可以使用SQL提供的库函数形成表达式，常用的函数如下： COUNT(*)：统计记录条数。 COUNT(列名)：统计一列值得个数。 SUM(列名):计算某一数值型列的值的总和。 AVG(列名):计算某一数值型列的值的平均值 MAX(列名):计算某一数值型列的值的最大值 DISTI

21、NCT参数：表示在结果集中，查询出的内容相同的记录只留下一条。 MIN(列名):计算某一数值型列的值的最小值,2021年3月10日5时30分,周口师范学院计算机科学系,37,3.3.1 单表查询 单表查询是指仅涉及一个表的查询。 一、选择表中的若干列 选择表中的全部列和部分列，就是投影运算。 1、查询指定列 在很多情况下，用户只对表中的一部分属性列感兴趣，这时可以通过在SELECT子句的中指定要查询的属性,2021年3月10日5时30分,周口师范学院计算机科学系,38,例1：查询全体学生的学号和姓名。 SELECT Sno,Sname FROM Student; 例2：查询全体学生的姓名、学号

22、、所在系。 SELECT Sname， Sno,Sdept FROM Student,2021年3月10日5时30分,周口师范学院计算机科学系,39,2、查询全部列 将表中所有属性列都选出来，可以有两种方法。一种方法是在SELECT关键字后列出所有的列名。如果列的显示顺序与其在基表中的顺序相同，也可以简单地将指定为*。 例3：查询全体学生的详细记录。 SELECT * SELECT Sno,Sname， FROM Student FROM Student,2021年3月10日5时30分,周口师范学院计算机科学系,40,3、查询经过计算的值 SELECT子句的不仅可以是表中的属性列，也可以是表达

23、式。 例4：查全体学生的姓名及其出生年份 SELECT Sname，2004-Sage FROM Student; 输出的结果为： 不仅可以是算术表达式，还可以是字符串常量、函数等,2021年3月10日5时30分,周口师范学院计算机科学系,41,例5：查询全体学生的姓名，出生年份和所在系，要求用小写字母表示所在系名。 SELECT Sname，Year of Birth, 2004-Sage,ISLOWER(Sdept) FROM Student; 输出结果为： 用户可以通过指定别名来改变查询结果的列标题。这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如，对上例，可定义如下,202

24、1年3月10日5时30分,周口师范学院计算机科学系,42,SELECT Sname NAME，Year of Birth; BIRTH,2004-Sage BIRTHDAY, ISLOWER(Sdept) DEPARTMENT FROM Student; 结果为,c,2021年3月10日5时30分,周口师范学院计算机科学系,43,a,b,2021年3月10日5时30分,周口师范学院计算机科学系,44,二、选择表中的若干元组 1、消除取值重复的行 两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行。如果想去掉表中的重复行，必须在SELECT子句中指定DISTINCET短语。如果

25、没有指定DISTINCET短语，则缺省为ALL，即保留结果表中取值重复的行。 例 6：查询选修了课程的学生学号。 SELECT Sno FROM SC,2021年3月10日5时30分,周口师范学院计算机科学系,45,a,该查询结果里包含了许多重复行,如果想去掉表中的重复行，则,SELECT DISTINCT Sno,FROM SC,执行结果为,b,2021年3月10日5时30分,周口师范学院计算机科学系,46,2、查询满足条件的元组 查询满足条件的元组可以通过WHERE子句来实现，WHERE子句常用的查询条件如下表示,2021年3月10日5时30分,周口师范学院计算机科学系,47,常用的查询条

26、件,2021年3月10日5时30分,周口师范学院计算机科学系,48,例7：查询考试成绩不及格学生的学号。 SELECT DISTINCT Sno FROM SC WHERE Grade 60; 例8：查询年龄在2023之间的学生的姓名，系别和年龄。 SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23 例9：查询年龄不在2023之间的学生的姓名，系别和年龄。 SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23,2021年3月10日

27、5时30分,周口师范学院计算机科学系,49,例10：查询计算机系、信息系学生的姓名、系别和年龄。 SELECT Sname,Sdept,Sage FROM Student WHERE Sdept IN(CS,IS); 例11：查询不是计算机系、也不是信息系的学生的姓名、系别和年龄。 SELECT Sname,Sdept,Sage FROM Student WHERE Sdept NOTIN(CS,IS,2021年3月10日5时30分,周口师范学院计算机科学系,50,谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下： NOT LIKE ESCAPE 其含义是查找指定的属性列值与相匹配的元

28、组。 可以是一个完整的字符串，也可以含有通配符和。其中： (百分号)代表任意长度(可以为)的字符串，如：a%b表示以a开头，以b结尾的任意长度的字符串。 _(下划线)代表任意单个字符。a_b表示以a开头，以b结尾长度为的任意字符串,2021年3月10日5时30分,周口师范学院计算机科学系,51,例12：查询所有姓“王”的学生信息。 SELECT * FROM Student WHERE Sname like 王%; 例13：查询名字中第二个字为“阳”的学生姓名和学号。 SELECT Sname,Sno FROM Student WHERE Sname like _ _阳%; 如果用户要查询的字

29、符串本身就含有%或_ 就要使用ESCAPE 对通配符进行转义,2021年3月10日5时30分,周口师范学院计算机科学系,52,例14：查询DB_Design课程的课程号和学分 SELECT Cno,Ccredit FROM Course WHERE Cname like DB_Design ESCAPE ; 其中：ESCAPE 短语表示为换码字符，这样匹配串中紧跟在后面的字符“_”不再具有通配符的含义，转义为普通的“_”字符。 例15：查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况 SELECT FROM Course WHERE Cname like DB_%i_ _ ESCAP

30、E,2021年3月10日5时30分,周口师范学院计算机科学系,53,例16 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno,Cno FROM SC WHERE Grade IS NULL 例17查询所有有成绩的学生的学号和相应的课程号。 SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL,2021年3月10日5时30分,周口师范学院计算机科学系,54,多重条件查询 逻辑运算符AND和OR可用来联结多个查询条件， AND优先级高于OR，但用户可用括号改变优先级。 例18 查询

31、计算机系年龄在20岁以下的学生姓名 SELECT SnameFROM Student WHERE Sdept=CSAND Sage20; 例19：查询计算机系、信息系学生的姓名、系别和年龄。 SELECT Sname,Sdept,Sage FROM Student WHERE Sdept =CS OR Sdept=IS,2021年3月10日5时30分,周口师范学院计算机科学系,55,三、对查询结果排序 用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列，缺省值为升序。 例19 查询选修了号课程的学号及其成绩，查询结果按分数的降序排列。 SELEC

32、T Sno,GradeFROM SC WHERE Cno=3 ORDER BY Grade DESC; 对于空值，若按升序，含空值的元组将最后显示，若按降序排，空值的元组将最先显示,2021年3月10日5时30分,周口师范学院计算机科学系,56,例19 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 SELECT FROM Student ORDER BY Sdept,Sage DESC,2021年3月10日5时30分,周口师范学院计算机科学系,57,四、查询中集函数的使用 说明：DISTINCT：取消列中的重复值 ALL：为默认状态，表示不取消重复值,202

33、1年3月10日5时30分,周口师范学院计算机科学系,58,例20：查询学生总人数。 SELECT COUNT(*) FROM Student; 例21：计算95001学生的平均成绩。 SELECT AVG(Grade) FROM SC WHERE Sno = 95001,2021年3月10日5时30分,周口师范学院计算机科学系,59,5查询结果的分组与排序 GROUP BY子句将查询结果表按某一列或多列值分组，值相等的为一组。 分组的目的是为了细化集函数的作用对象，如果未对查询结果分组，集函数将作用于整个查询结果。分组后集函数将作用在每一个组上，即每一组都有一个函数值,2021年3月10日5时

34、30分,周口师范学院计算机科学系,60,例22：查询选修了2门以上课程的学生学号,并且查询结果按照升序排列。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 2 ORDER BY Sno ASC; WHERE与HAVING的区别在于作用的对象不同。WHERE子句作用于基本表或视图，从中选择满足条件的记录；HAVING短语作用于分组，从中选出满足条件的组,2021年3月10日5时30分,周口师范学院计算机科学系,61,3.3.2 连接查询 前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询，连接查询是关系数据库中最主要

35、的查询，包括等值连接、自然连接、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询,2021年3月10日5时30分,周口师范学院计算机科学系,62,从概念上讲，DBMS执行连接操作的过程是：首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第2个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第2个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕为止,2021年3月10日5时30分,周口师范学院计

36、算机科学系,63,一、等值与非等值连接。 连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为： . . 其中主要有：=，=，称为连接字段， 连接条件中的各个连接字段类型必须是可比的，但不必是相同的。 Eg,2021年3月10日5时30分,周口师范学院计算机科学系,64,在本例中，SELECT子句与WHERE子句中的属性名前都加上了表名前缀，这是为了避免混淆。如果属性名在参加连接的各表中是唯一的，则可以省略表名前缀。 连接运算中有两种特殊情况，一种为自然连接，另一种为广义笛卡尔积（连接）。 广义笛卡尔积是不带连接谓词的连接。两个表的广义笛卡尔积即是两表中元组的交叉乘积，其连接的结

37、果会产生一些没有意义的元组，所以这种运算实际很少使用。 若在等值连接中把目标列中重复的属性列去掉则为自然连接,2021年3月10日5时30分,周口师范学院计算机科学系,65,二、自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接。 在通常的连接操作中，只有满足连接条件的元组才能作为结果输出,2021年3月10日5时30分,周口师范学院计算机科学系,66,三、外连接 外连接的表示方法为，在连接谓词的某一边加符号*（有的数据库系统中用+）。外连接就好象为符号*所在边的表增加一个“万能的”行，这个行全部由空值组成。它可以和另一边的表中所有不满足条件的元组进行

38、连接（从而使得另一个表在连接的过程中不会有元组丢失）。 如果外连接符出现在连接条件的右边，称其为右外连接，如果外连接符出现在连接条件的左边，则称其为左外连接,2021年3月10日5时30分,周口师范学院计算机科学系,67,四、复合条件的连接 上面各个连接查询中，WHERE子句中只有一个条件，即连接谓词。 WHERE子句中可以有多个连接条件，称为复合条件连接。 连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者称为多表连接,2021年3月10日5时30分,周口师范学院计算机科学系,68,例23 查询每个学生及其所选课程的情况 Select Student.*,

39、SC.* From Student, SC Where Student.Sno = SC.Sno; 例24 查询每个学生及其所选课程的情况（用自然连接） Select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade From Student, SC Where Student.Sno = SC.Sno; 例25 查询所有比李勇年龄大的学生姓名、年龄 Select X.Sname, X.Sage From Student X, Student Y Where X.Sage Y.Sage And Y.Sname=李勇,结果,结果,2021年3月10日5时30

40、分,周口师范学院计算机科学系,69,该查询的执行结果,2021年3月10日5时30分,周口师范学院计算机科学系,70,该查询的执行结果,2021年3月10日5时30分,周口师范学院计算机科学系,71,例26 查询每一门课的间接先修课（即先修课的先修课）。 Select FIRST.Cno ,SECOND.Cpno From Course FIRST, Course SECOND Where FIRST.Cpno=SECOND.Cno; 例27 查询所有学生的学号、姓名、选修的课程和成绩 Select Student.Sno, Sname, Cname, Grade From Student,

41、SC, Course Where Student.Sno=SC.Sno(+) And SC.Cno=Course.Cno(,2021年3月10日5时30分,周口师范学院计算机科学系,72,例28 查询选修2号课程且成绩在90分以上的所有学生。 SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade90,2021年3月10日5时30分,周口师范学院计算机科学系,73,例29 查询每个学生的学号、姓名、选修的课程名及成绩。 SELECT Student.Sno, Sn

42、ame，Cname,Grade FROM Student, SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno,2021年3月10日5时30分,周口师范学院计算机科学系,74,该查询的执行结果,2021年3月10日5时30分,周口师范学院计算机科学系,75,2021年3月10日5时30分,周口师范学院计算机科学系,76,3.3.3嵌套查询,在SQL语言中，一个SELECT-FROM-WHERE 称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。上层的查询块称为外层查询或父查询

43、，下层查询称为内层查询或子查询。外层查询利用子查询来获取检索条件的条件值，并根据这个条件值确定外层查询的结果集合。SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询，通过子查询，可以利用一系列简单查询来构造一个复杂查询。 子查询中的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序,2021年3月10日5时30分,周口师范学院计算机科学系,77,嵌套查询一般的求解方法是由里向外处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。以层层嵌

44、套的方式来构造程序正是SQL(Structured Query Language)中“结构化”的含义所在,2021年3月10日5时30分,周口师范学院计算机科学系,78,一、带有IN谓词的子查询 在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。 例30 查询与刘晨在同一个系学习的学生。 Select Sno, Sname, Sdept From Student Where Sdept IN (Select Sdept From Student Where Sname = 刘晨,2021年3月10日5时30分,周口师范学院计算机科学系,79,本例中的查询也可以

45、用自身连接来完成： SELECT S1.Sno,S1.Sname,S1.Sdept FROM Student S1,Student S2 WHERE S1.Sdept=S2 .Sdept AND S2 .Sname=“刘晨”； 本例中父查询和子查询均引用了Student表，可以像自身连接那样用别名将父查询中的Student表与子查询中的Student表区分开,2021年3月10日5时30分,周口师范学院计算机科学系,80,Select Sno, Sname, Sdept From Student S1 Where S1.Sdept IN (Select Sdept From Student S

46、2 Where S2.Sname = 刘晨,2021年3月10日5时30分,周口师范学院计算机科学系,81,例31 查询选修了课程名为信息系统的学生的学号和姓名。 SELECT Sno, Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = 信息系统,首先在Course,关系中找出“信息,系统”的课程号,结果为3号,然后在SC关系中找出选修,了3号课程的学生学号,最后在Student关系中取,出Sno和Sname,2021年3月10日5时30分

47、,周口师范学院计算机科学系,82,同样本查询也可以用连接查询来实现： SELECT Sno, Sname FROM Student, SC, Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname=“信息系统,2021年3月10日5时30分,周口师范学院计算机科学系,83,从例30和例31可以看到，查询涉及多个关系时，用嵌套查询逐步求解，层次清楚，易于构造，具有结构化程序设计的优点。 有些嵌套查询可以用连接运算替代，有些是不能替代的。到底采用哪种方法用户可以根据自己的习惯确定。 例30和例31中的各个子查询都

48、只执行一次，其结果用于父查询。子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。这种子查询是最简单的一类子查询,2021年3月10日5时30分,周口师范学院计算机科学系,84,二、带有比较运算符的子查询,带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用, =, 等比较运算符。 例如在例30中，由于一个学生只可能在一个系学习，也就是说内查询的结果是一个值，因此可以用=代替IN，其SQL语句如下,2021年3月10日5时30分,周口师范学院计算机科学系,85,SELECT Sno, Sname, Sdept FROM Stu

49、dent WHERE Sdept = (SELECT Sdept FROM Student WHERE Sname = 刘晨); 需要注意的是，子查询一定要跟在比较符之后,2021年3月10日5时30分,周口师范学院计算机科学系,86,三、带有ANY或ALL谓词的子查询 子查询返回单值时可以用比较运算符，而使用ANY或ALL谓词时则必须同时使用比较运算符。其语义为： ANY 大于子查询结果中的某个值 ALL 大于子查询结果中的所有值 =ANY 大于等于子查询结果中的某个值 =ALL 大于等于子查询结果中的所有值 =ANY 小于等于子查询结果中的某个值,2021年3月10日5时30分,周口师范学

50、院计算机科学系,87,ANY 不等于子查询结果中的某个值 !=(或)ALL 不等于子查询结果中的任何一 个值,2021年3月10日5时30分,周口师范学院计算机科学系,88,例32 查询其他系中比信息系中某一学生年龄小的学生姓名和年龄。 Select Sname, Sage From Student Where Sdept IS AND Sage Any(Select Sage From Student Where Sdept = IS)； DBMS执行此查询时，首先处理子查询找出IS系中所有学生的年龄，构成一个集合(19, 18)。然后处理父查询，找所有不是IS系且年龄小于19或18的学生,

51、2021年3月10日5时30分,周口师范学院计算机科学系,89,本查询也可以用集函数来实现。首先用子查询找出IS系中最大年龄（19）,然后在父查询中查所有非IS系且年龄小于19岁的学生姓名及年龄。SQL语句如下： Select Sname, Sage From Student Where Sdept IS AND Sage (Select MAX(Sage) From Student Where Sdept = IS,2021年3月10日5时30分,周口师范学院计算机科学系,90,例33 查询其他系中比信息系中所有学生年龄都小的学生姓名和年龄。 Select Sname, Sage From

52、Student Where Sdept IS And Sage All(Select Sage From Student Where Sdept = IS)； 本查询也可以用集函数实现。SQL语句,2021年3月10日5时30分,周口师范学院计算机科学系,91,事实上，用集函数实现子查询通常比直接用ANY或ALL查询效率要高。 ANY与ALL与集函数的对应关系如下表示： ANY, ALL谓词与集函数及IN谓词的等价转换关系,2021年3月10日5时30分,周口师范学院计算机科学系,92,四、带有EXISTS谓词的子查询 EXISTS代表存在量词 。带有EXISTS谓词的子查询不返回任何数据，只

53、产生逻辑真值“true”或逻辑假值“false”. 例34 查询选择了1号课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1,2021年3月10日5时30分,周口师范学院计算机科学系,93,使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。 由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名没有实际意义。 这类子查询的查询条件依赖于外层父查询的某个属性值

54、，称这类查询为相关子查询,2021年3月10日5时30分,周口师范学院计算机科学系,94,从概念上讲，相关子查询的一般处理过程是： 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取表的下一个元组；重复这一过程，直至外层表全部检查完为止。 与EXISTS谓词相对应的是NOT EXISTS谓词。使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的 WHERE子句返回真值，否则返回假值,2021年3月10日5时30分,周口师范学院计算机科学系,95,例35 查询没有选择1号课程的学生姓名。 SELECT

55、Sname FROM Student WHERE Not EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno And Cno=1)； 一些带有EXISTS或NOT EXISTS谓词的子查询不能被其它形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带有EXISTS或NOT EXISTS谓词的子查询等价替换,2021年3月10日5时30分,周口师范学院计算机科学系,96,查询与刘晨在同一个系学习的学生。 SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (SELEC

56、T Sdept FROM Student WHERE Sname = 刘晨,2021年3月10日5时30分,周口师范学院计算机科学系,97,SELECT Sno, Sname, Sdept FROM Student S1 WHERE EXISTS (SELECT * FROM Student S2 WHERE S2.Sdept=S1.Sdept AND S2.Sname = 刘晨,2021年3月10日5时30分,周口师范学院计算机科学系,98,由于带EXISTS词的相关子查询只关心内层查询是否有返回值，并不需要查具体值因此其效率并不一定低于不相关子查询，有时是高效的方法,2021年3月10日5

57、时30分,周口师范学院计算机科学系,99,例36 查询选修了全部课程的学生姓名 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno,某个课程没选,2021年3月10日5时30分,周口师范学院计算机科学系,100,3.3.4集合查询,SELECT语句的查询结果是元组的集合，所以多个SELECT语句的可进行集合操作。集合操作主要包括并操作UNION、交操作INTERSEC

58、T和差操作MINUS. 例37 查询选修了1号或2号课程的学生 SELECT Sno FROM SC WHERE Cno=1 UNION SELECT Sno FROM SC WHERE Cno=2,2021年3月10日5时30分,周口师范学院计算机科学系,101,标准SQL中没有直接提供集合交操作和集合差操作，但可以用其他方法来实现。 例38 查询选修了1号课程的学生和选修了2号课程的学生的交集 SELECT Sno FROM SC WHERE Cno=1 AND Sno IN (SELECT Sno FROM SC WHERE Cno=2,2021年3月10日5时30分,周口师范学院计算机

59、科学系,102,例39 查询计算机系的学生与年龄不大于19岁的学生的差集。 即查询计算机系中年龄大于19岁的学生。 SELECT * FROM Student WHERE Sdept = CS AND Sage 19,2021年3月10日5时30分,周口师范学院计算机科学系,103,3.4 数据更新,SQL中数据更新包括插入、修改数据和删除数据三条语句： INSERT语句 UPDATE语句 DELETE语句,2021年3月10日5时30分,周口师范学院计算机科学系,104,3.4.1 Insert语句,SQL的数据插入语句INSERT通常有两种形式。一种是插入一个元组，另一种是插入子查询结果。

60、后者可以一次插入多个元组 一、插入单个元组 INSERT INTO （,） VALUES(,); 其功能是将新元组插入指定表中。其中新记录属性列1的值为常量1，属性列2的值为常量2，INTO子句中没有出现的属性列，新记录在这些列上将取空值。如果INTO子句中没有任何列名，则新插入的记录必须在每个属性列上均有值,2021年3月10日5时30分,周口师范学院计算机科学系,105,例1 将一个新学生记录（学号：95020;姓名：陈冬；性别：男；所在系：IS;年龄：18岁）插入到Student表中。 INSERT INTO Student Values (95020, 陈冬, 男,IS, 18); 例