关系数据库语言SQL.ppt

1、第三章 关系数据库语言SQL,3.1 SQL概述 3.2 SQL的数据定义功能 3.3 SQL的数据查询功能 3.4 SQL的数据操纵功能 3.5 视图操作,小结,SQL （Structured Query Language）是结构化查询语言的缩写，SQL 简洁、方便实用、功能齐全，已成为目前应用最广的关系数据库语言。 SQL的功能包括数据查询、数据操纵、数据定义和数据控制四个部分。本章将结合实例讲解怎样利用SQL编写程序实现对数据库的定义、查询、更新及控制操作。 其次，将介绍视图的概念，以及利用SQL对视图进行操作。,本章概要,3.1 SQL 概 述,一、SQL的发展及标准化 SQL语言是当

2、前最为成功、应用最为广泛的关系数据库语言，其发展主要经历了以下几个阶段： 1974年，由IBM公司的Chamberlin和Boyce提出，当时称为SEQUEL(Structured English Query Language)； 197579年，IBM公司对其进行了修改，并用于其SYSTEM R关系数据库系统中； 1981年 IBM推出其商用关系关系数据库SQL/DS，并将其名字改为SQL。由于SQL语言功能强大，简洁易用，因此得到了广泛的使用； 1986年，第一个SQL标准由ANSI（美国国家标准局）公布，称为SQL86。之后，SQL标准不断修改和完善，又推出SQL89、SQL92、SQL

3、99等。,基本表（Base Table）：是独立且实际存在的表。一个关系对应一个基本表，一个或多个基本表对应一个存储文件。视图（View）：是一个虚拟的表，是从一个或几个基本表导出的表。它本身不独立存在于数据库中，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。 索引（Index)：是定义在基本表上的快速查找路径。一个表可以有若干个索引，它们也存放在存储文件中。,二、 有关SQL的基本概念,可以在S基础上定义一个年龄情况视图SA_V SA_V(SNO,SNAME,SAGE) 它是从S 表中在SNO

4、,SNAME,SAGE上投影得到的。 在数据库中只存有SA_V的定义，而不存放该视图的定义。 在用户看来，视图是通过不同路径去看一个实际表，就象一个窗口一样，我们通过窗户去看外面的高楼，可以看到高楼的不同部分，而透过视图可以看到数据库中自己感兴趣的内容。,例如：学生数据库中有学生基本情况表S S(SNO,SNAME,SSEX,SAGE,SDEPT) 此表为基本表，对应一个存储文件。,SQL语言支持数据库的三级模式结构，如图3.1所示。其中外模式对应于视图和部分基本表，模式对应于全部基本表，内模式对应于存储文件。,SQL语言类似于英语的自然语言，简洁易用。 SQL语言是一种非过程语言，即用户只要

5、提出“干什么”，不必关心具体操作过程，也不必了解数据的存取路径，只要指明所需的数据即可。 SQL语言是一种面向集合的语言，每个命令的操作对象是一个或多个关系，结果也是一个关系。 SQL语言既是自含式语言，又是嵌入式语言。可独立使用，也可嵌入到宿主语言中。 自含式语言可以独立使用对数据库操作，适用于终端用户、DBA等； 嵌入式语言嵌入在高级语言中使用，供应用程序员开发应用程序。 SQL语言具有数据查询、定义、操纵和控制四种功能一体化的特点。,三、 SQL语言的主要特点,3.2 SQL的数据定义功能,SQL使用数据定义语言（Data Definition Language，简称DDL）实现其数据定

6、义功能，可对数据库模式、基本表、视图、索引等对象进行创建、修改和撤消。,当用SQL语句定义表时，需要为表中的每一个字段设置一个数据类型，用来指定字段所存放的数据是整数、字符串、货币或是其它类型的数据。SQL的数据类型主要支持以下数据类型： 1. 整型数据类型：依整数数值的范围大小，有BIT, INT , SMALLINT, TINYINT四种。 2. 精确数值类型：用来定义可带小数部分的数字，有NUMERIC和DECIMAL两种。二者相同，但建议使用DECIMAL。如：123.0、8000.56,一、SQL的数据类型,4. 日期时间数据类型：用来表示日期与时间，依时间范围与精确程度可分为DAT

7、ETIME与SMALLDATETIME两种。如：1998-06-08 15:30:00 5. 字符串数据类型：用来表示字符串的字段。包括：CHAR, VARCHAR, TEXT三种，如：“数据库” 6. UNICODE字符串数据类型：UNICODE是双字节文字编码标准，包括NCHAR, NVARCHAR与NTEXT三种。与字符串数据类型相类似，但UNICODE的一个字符用2字节存储，而一般字符数据用一个字节存储。,3. 近似浮点数值数据类型：当数值的位数太多时，可用 此数据类型来取其近似值，用FLOAT和REAL两种。 如：1.23E+10,8. 货币数据类型：用来定义与货币有关的数据，分为

8、MONEY 与SMALLMONEY两种，如：123.0000 9.标记数据类型：有UNIQUEIDENTIFIER ，TIMESTAMP两种，此数据类型通常系统自动产生，而不是用户输入的，TIMESTAMP记录数据更新的时间戳印，而UNIQUEIDENTIFIER用来识别每一批数据的唯一性。,7. 二进制数据类型：用来定义二进制码的数据。有： BINARY, VARBINARY，IMAGE 三种，通常用 十六进制表示：如：5F3C,各种数据类型的有关规定如下表：,一、创建数据库模式 模式定义语句如下： CREATE SCHEMA AUTHORIZATION ,3.2.1 模式的定义和删除,例1

9、CREATE SCHEMA “S-T” AUTHORIZATION ZHANG;例2CREATE SCHEMA AUTHORIZATION ZHANG;,删除模式语句如下： DROP SCHEMA ,二、删除模式,例3 DROP SCHEMA ZHANG CASCADE ;,3.2.2 基本表的定义、删除和修改,一、定义基本表 语句格式 CREATE TABLE （ ， ， ）； ：所要定义的基本表的名字 ：组成该表的各个属性（列） ：涉及相应属性列的完整性约束条件 ：涉及一个或多个属性列的完整性约束条件,例题,例1 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Sse

10、x、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATE TABLE Student(Sno CHAR(5) NOT NULL UNIQUE， Sname CHAR(20) UNIQUE， Ssex CHAR(2) ，Sage INT，Sdept CHAR(15)；,定义基本表（续）,常用完整性约束 主码约束： PRIMARY KEY 唯一性约束：UNIQUE 非空值约束：NOT NULL 参照完整性约束PRIMARY KEY与 UNIQUE的区别？,例题 （续）,例2 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Gr

11、ade组成，其中(Sno, Cno)为主码。CREATE TABLE SC(Sno CHAR(5) ,Cno CHAR(3) , Grade int,Primary key (Sno, Cno)，FOREIGN KEY（ Sno）REFERENGES Student（ Sno），FOREIGN KEY（ Cno）REFERENGES Course（ Cno） );,例题 （续）,二、删除基本表,DROP TABLE RESTRICT|CASCADE; 基本表删除 数据、表上的索引都删除表上的视图往往仍然保留，但 无法引用删除基本表时，系统会从数据字典中删去有关该 基本表及其索引的描述 （标准中

12、没有，认为表建立后就永久存在）,例题,例5 删除Student表DROP TABLE Student CASCADE;,三、修改基本表,ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN ；：要修改的基本表 ADD子句：增加新列和新的完整性约束条件 DROP子句：删除指定的完整性约束条件 ALTER COLUMN子句：用于修改列名和数据类型,例题,例1 向Student表增加“入学时间”列，其数据类型为日期型。ALTER TABLE Student ADD Scome DATE；不论基本表中原来是否已有数据，新增加的列一律为空值。,语句格式（续）,删除属性列直接/间

13、接删除 把表中要保留的列及其内容复制到一个新表中 删除原表 再将新表重命名为原表名 直接删除属性列:(新) 例：ALTER TABLE Student Drop Scome；,例2 将年龄的数据类型改为半字长整数。ALTER TABLE Student ALTER COLUMN Sage SMALLINT；注：修改原有的列定义有可能会破坏已有数据,例题,例3 删除学生姓名必须取唯一值的约束。ALTER TABLE Student DROP UNIQUE(Sname)；,一、索引的作用 在日常生活中我们会经常遇到索引，例如图书目录、词典索引等。借助索引，人们会很快地找到需要的东西。 索引是数据库

14、随机检索的常用手段，它实际上就是记录的关键字与其相应地址的对应表。 例如，当我们要在本书中查找有关“SQL查询”的内容时，应该先通过目录找到“SQL查询”所对应的页码，然后从该页码中找出所要的信息。这种方法比直接翻阅书的内容要快。 如果把数据库表比作一本书，则表的索引就如书的目录一样，通过索引可大大提高查询速度。,3.2.3 设计、创建和维护索引,二、索引的分类,聚集索引：要求行的物理存储顺序与索引顺序完全相同，一个表只允许创建一个聚簇索引。检索速度快但索引时间长。 非聚集索引：具有完全独立于数据行的结构，不用将物理数据页中的数据按行排序。索引速度快但占用空间多。 唯一索引：指表中每一个索引值

15、只对应唯一的数据记录。 复合索引：是将两个或两个以上的字段组合起来建立的索引。,在SQL中，可用CREATE INDEX建立索引，其语法格式为：CREATE UNIQUE CLUSTER INDEX ON ( 次序 , 次序)其中： UNIQUE表明建立唯一索引。 CLUSTER表示建立聚集索引。 次序用来指定索引值的排列顺序，可为ASC(升序)或DESC(降序)，缺省值为ASC。,三、建立索引,例18：为表SC在SNO和CNO上建立唯一索引。,CREATE UNIQUE INDEX SCI ON SC(SNO,CNO) 说明：执行此命令后，为SC表建立一个索引名为SCI的唯一索引， 此索引为

16、SNO和CNO两列的复合索引。由于有UNIQUE的限制，所 以该索引在(SNO,CNO)组合列的排序上具有唯一性，不存在重复值。例19：为教师表T在TN上建立聚集索引。 CREATE CLUSTER INDEX TI ON T(TN) 说明：执行此命令后，为T表建立一个索引名为TI的聚集索引，T表中的记录将按照TN值的升序存放。,1、索引建立后，在查询使用该列时，系统将自动使用索引进行查询。 2、改变表中的数据（如增加或删除记录）时，索引将自动更新。 3、 索引数目无限制，但索引越多，数据更新时，系统会花费许多时间来维护索引。因此应该删除一些不必要的索引。,注 意：,删除索引的语句是DROP

17、INDEX，其语法格式为：DROP INDEX ON 例20： 删除表SC的索引SCI。 DROP INDEX SCI ON SC,四、删除索引,3.3 SQL数据查询功能,3.3.1 概述 3.3.2 单表查询 3.3.3 连接查询 3.3.4 嵌套查询 3.3.5 集合查询 3.3.6 小结,3.3.1 概述,数据查询是数据库中最常见、最核心的操作。SQL提供SELECT语句，进行数据库的查询。通过查询操作可得到用户所需的信息。 SELECT语句的一般格式为：SELECTALL| DISTINCT列名,列名 FROM表名或视图名,表名或视图名WHERE检索条件GROUP BY HAVING

18、 ORDER BY ASC|DESC; 说明：SELECT语句对表进行查询，查询的结果是仍是一个表。,根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。 如果有GROUP子句，则将查询结果按照相同的值进行分组。 如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。 如果有ORDER子句，查询结果还要按照的值进行排序。,SELECT语句的执行过程是:,图1.12 关系模型实例T（教师表）,S（学生表）,C（课程表）,SC（选课表） TC（授课表）,一、简单查询 SELECT语句中不包括WH

19、ERE子句的查询称为简单查询，也称为投影查询。例21：查询全体学生的学号、姓名和年龄。 SELECT SNO, SN, AGE FROM S 例22：查询学生的全部信息。 SELECT SNO, SN, SEX, AGE, DEPT FROM S或 SELECT * FROM S 说明：用“ * ”表示S表的全部列名，而不必逐一列出。 例23：查询选修了课程的学生号。 SELECT DISTINCT SNO FROM SC 说明：使用DISTINCT，查询结果中的重复行被去掉。,3.3.2 单表查询,当要在表中找出满足某些条件的行时，则需使用WHERE子句指定查询条件。 WHERE子句中，条件

20、表达式的格式如下：,二、条件查询,例24：查询选修课程号为C1的学生的学号和成绩。 SELECT SNO, SCORE FROM SC WHERE CNO=C1例25：查询成绩高于85分的学生的学号、课程号和成绩。 SELECT SNO,CNO, SCORE FROM SC WHERE SCORE85,1、在条件中比较大小,当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR或NOT将其连结成复合的逻辑表达式。 其优先级由高到低为：NOT、AND、OR，但用户可以使用括号改变优先级。例26：查询选修C1或C2且分数大于等于85分学生的的学号、课程号和成绩。 SELEC

21、T SNO，CNO， SCORE FROM SC WHERE（CNO=C1 OR CNO=C2） ANDSCORE=85,2、多重条件查询,例27：查询工资在1000至1500之间的教师的教师号、姓名及职称。 SELECT TNO,TN,PROF FROM T WHERE SAL BETWEEN 1000 AND 1500 等价于 WHERE SAL=1000 AND SAL=1500例28：查询工资不在1000至1500之间的教师的教师号、姓名及职称。 SELECT TNO,TN,PROF FROM T WHERE SAL NOT BETWEEN 1000 AND 1500,3、确定范围的条

22、件查询,利用IN子句可以查询属性值属于指定集合的元组。例29：查询选修C1或C2号课程的学生的学号、课程号和成绩。 SELECT SNO, CNO, SCORE FROM SC WHERE CNO IN(C1, C2) 说明：此语句也可以使用逻辑运算符OR实现。 SELECT SNO, CNO, SCORE FROM SC WHERE CNO=C1 OR CNO= C2S,4、集合查询,例30：查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。 SELECT SNO, CNO, SCORE FROM SC WHERE CNO NOT IN(C1, C2) 等价于： SELECT SN

23、O, CNO, SCORE FROM SC WHERE CNOC1 AND CNO C2,利用NOT IN可以查询指定集合外的元组。,上例均属于完全匹配查询，当不知道完全精确的値时，用户还可以使用LIKE或NOT LIKE进行部分匹配查询（也称模糊查询）。 (NOT)LIKE定义的一般格式为：LIKE 其中：中可以包含如下两个特殊符号：*（%）：表示任意长度的字符串；?（-）：表示任意单个字符。,5、部分匹配查询,SELECT TNO, TN FROM T WHERE TN LIKE 张*例32：查询姓名中第二个汉字是“力”的教师号和姓名。 SELECT TNO, TN FROM T WHER

24、E TN LIKE ?力*,例31：查询所有姓张的教师的教师号和姓名。,某个数据项没有值称其具有空值（NULL）。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。 例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。例33：查询没有考试成绩的学生的学号和相应的课程号。 SELECT SNO, CNO FROM SC WHERE SCORE IS NULL 注意：这里的空值条件为IS NULL，不能写成SCORE =NULL。,6、空值查询,SQL提供了许多库函数，增强了基本检索能力。常用的库函数，如下

25、表所示：,三、利用库函数及统计汇总查询,SELECT SUM(SCORE) AS TotalSCORE, AVG(SCORE) AS AveSCORE FROM SC WHERE SNO = S1注意：函数SUM和AVG只能对数值型字段进行计算。例35：求计算机系学生的总数。SELECT COUNT(SNO) FROM SWHERE DEPT=计算机,例34：求学号为S1学生的总分和平均Studenttudenttudenttudent。,SELECT MAX(SCORE) AS MaxSCORE, MIN(SCORE) AS MinSCORE, MAX(SCORE) - MIN(SCORE)

26、 AS Diff FROM SC WHERE CNO = C1,例36：求选修C1号课程的最高分、最低分及之间相差的分数。,SELECT COUNT(DISTINCT DEPT) AS DeptNum FROM S 说明：加入关键字DISTINCT后表示消去重复行，可计算字段 “DEPT“不同值的数目。COUNT函数对空值不计算，但对零进行计算。例38：利用特殊函数COUNT(*)求计算机系学生的总数。 SELECT COUNT(*) FROM S WHERE DEPT=计算机 说明：COUNT（*）用来统计元组的个数不消除重复行，不允许使用DISTINCT关键字。,例37： 求学校中共有多少

27、个系。,用户可以利用GROUP BY子句将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。例39：查询各位教师的教师号及其任课的门数。 SELECT TNO,COUNT(*) AS C_NUM FROM TC GROUP BY TNO 说明：GROUP BY子句按TNO的值分组，所有具有相同TNO的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。,四、对查询结果进行分组,例40：查询选修两门以上课程的学生学号和选课门数。 SELECT SNO,COUNT(*) AS SC_NUM FROM SC GROUP BY SNO HA

28、VING COUNT(*)=2 说明： GROUP BY子句按SNO的值分组，所有具有相同SNO的元组为一组，对每一组使用函数COUNT进行计算，统计出每位学生选课的门数。 HAVING子句去掉不满足COUNT（*）=2的组。,若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。,WHERE与HAVING子句的根本区别在于作用对象不同： WHERE子句作用于基本表或视图，从中选择满足条件的元组； HAVING子句作用于组，选择满足条件的组，必须用于GROUP BY子句之后，但GROUP BY子句可没有HAVING子句。,当在一个SQL查询中同时使用WHERE子句、GROUP BY

29、子句和HAVING子句时，其顺序是WHERE GROUP BY HAVING。,当需要对查询结果排序时，应该使用ORDER BY子句。ORDER BY子句必须出现在其它子句之后，排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序。例41：查询选修C1 课程的学生学号和成绩，并按成绩降序排列。 SELECT SNO, SCORE FROM SC WHERE CNO=C1 ORDER BY SCORE DESC,五、对查询结果进行排序,SELECT SNO,CNO, SCORE FROM SC WHERE CNO IN (C2 ,C3, C4,C5) ORDER BY SNO, SCOR

30、E DESC,例42：查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。,SELECT SNO，SUM(SCORE) AS TotalSCORE， COUNT（*） AS SC_NUM FROM SC WHERE SCORE=60 GROUP BY SNO HAVING COUNT(*)=3 ORDER BY SUM(SCORE) DESC 说明：此语句为分组排序，执行过程如下： （FROM）取出整个SC。 （WHERE）筛选SCORE=60的元组。 （GROUP BY）将选出的元组按SNO分组。 （HAVING）筛选选课三门以上的分组。

31、 （SELECT）以剩下的组中提取学号和总成绩。 （ORDER BY）将选取结果排序。,例43：求选课在三门以上且各门课程均及格的学生的学号及其总成绩和选课门数，查询结果按总成绩降序列出。,前面的查询都是针对一个表进行的，当查询同时涉及两个以上的表时，要对这些表设置联系，进行连接查询。 数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。 表的连接方法有两种： 方法1：表之间满足一定的条件的连接查询，此时FROM子句中指明进行连接的表名，WHERE子句指明连接的列名及其连接条件。 方法2：利用关键字J

32、OIN进行连接。,3.3.3数据表连接及连接查询,例44：查询“刘伟”老师的教师号、姓名以及所讲授的课程号。 方法1： SELECT T.TNO ,TN,CNO FROM T,TC WHERE T.TNO = TC. TNO AND TN=刘伟 说明： 在WHERE条件中，T.TNO = TC.TNO 为连接条件，TNO为连接字段。连接条件的一般格式为： . . 当比较运算符为“时，称为等值连接，其他情况为非等值连接。 引用列名TNO时要加上表名前缀，是因为两个表中的列名相同，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。如果列名是唯一的，比如TN，就不必须加前缀。 该查询的操作过程

33、是将T表中的TNO和TC表中的TNO相等的行连接，同时选取TN为“刘伟“的行，然后再在TNO 、TN和CNO列上投影。,1、等值连接与非等值连接,SELECT T.TNO,TN,CNO FROM T INNER JOIN TC ON T.TNO=TC.TNO WHERE T.TN=刘伟 说明：INNER JOIN表示连接T表和TC表，ON子句指出连接字段。例45：查询所有选课学生的学号、姓名、选课名称及成绩。 SELECT S.SNO,SN,CN,SCORE FROM S,C,SC WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO 说明：本例涉及三个表，WHERE子句中有

34、两个连接条件。当有两个以上的表进行连接时，称为多表连接。,方法2：,当一个表与其自已进行连接操作时，称为表的自身连接。 例46：查询所有比刘伟工资高的教师姓名、工资和刘伟的工资。 分析：要查询的内容均在同一表T中，可以将表T分别取两个别名，一个是X，一个是Y。将X, Y 中满足比刘伟工资高的行连接起来。这实际上是同一表T的自身连接。 方法1： SELECT X.TN , X.SAL AS SAL_a , Y.SAL AS SAL_b FROM T AS X ,T AS Y WHERE X.SALY.SAL AND Y.TN=刘伟 方法2： SELECT X.TN, X.SAL,Y.SAL FR

35、OM T AS X INNER JOIN T AS Y ON X.SALY.SAL WHERE Y.TN=刘伟,2、自身连接,在通常的连接操作中，只有满足连接条件的元组才能作为查询结果输出。如例45中的查询结果只包括有选课记录的学生，没有选课的学生不会出现在结果中。 但有时想列出每个学生的基本情况（包括没有选课的学生）及选课学生的选课情况，若某个学生没有选课，则只输出其基本情况，其选课信息可为空值，这时就需要使用外连接（OUTER JOIN）。,3、外连接,SELECT S.SNO,SN,CNO,SCORE FROM S LEFT OUTER JOIN SC ON (S.SNO=SC.SNO)

36、 or FROM S LEFT OUTER JOIN SC USING (SNO) 说明： LEFT （OUTER） JOIN表示显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边表中的数据行会以NULL来显示，此称为左连接； 则查询结果只包括所有的学生，没有选课的同学的选课信息显示为空。,例47：查询所有学生的学号、姓名、课程号及成绩。（没有选课的同学的选课信息显示为空）,在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。嵌套查询可以将一系列简单查询构成复杂查询，增强查询能力。 子查询的嵌套

37、层次最多可达到255层，以层层嵌套的方式构造查询充分体现了SQL“结构化”的特点。 嵌套查询在执行时由里向外处理，每个子查询是在上一级外部查询处理之前完成，父查询要用到子查询的结果。,3.3.4 嵌套查询,当子查询的返回值只有一个时，可以使用比较运算符=、 =、=、!= 将父查询和子查询连接起来。例48：查询与刘伟教师职称相同的教师号、姓名。 SELECT TNO,TN FROM T WHERE PROF=(SELECT PROF FROM TWHERE TN=刘伟),1、返回一个值的子查询,说明： 此查询相当于分成两个查询块来执行。先执行子查询： SELECT PROF FROM T WHE

38、RE TN=刘伟 子查询向主查询只返回一个值，即刘伟教师的职称“讲师”，然后以此作为父查询的条件，相当于再执行父查询, 查询所有职称为“讲师”的教师号、姓名。即再执行下面的语句： SELECT TNO,TN FROM T WHERE PROF=讲师,如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY或ALL。其具体含义详见以下各例。（1）带有ANY的子查询 ANY的含义为任意一个。 例49：查询讲授课程号为C5的教师姓名。 SELECT TN FROM T WHERE TNO=ANY(SELECT TNO FROM TC WHERE C

39、NO=C5),2、返回一组值的子查询,说明：在该例中，先执行子查询，找到讲授课程号为C5的教师号，再执行父查询，查询讲授课程号为C5的教师号对应的教师的姓名。,SELECT TN FROM T,TC WHERE T.TNO=TC.TNO AND TC.CNO=C5 说明：对于同一查询可使用子查询和连接查询两种方法来解决，可根据习惯任意选用。 在该例中，可以使用IN代替“=ANY”。 SELECT TN FROM T WHERE TNO IN(SELECT TNO FROM TC WHERE CNO=C5) S,该例也可以使用前面所讲的连接操作来实现：,SELECT TN,SAL FROM T

40、WHERE SALANY（SELECT SALFROM TWHERE DEPT=计算机）AND DEPT!= 计算机 /S注意：此行是父查询中的条件*/ 说明：在该例中，先执行子查询，找到计算机系中所有教师的工资集合；再执行父查询，查询所有不是计算机系且工资高于计算机系任一教师工资的教师姓名和工资。,例50：查询其它系中比计算机系任一教师工资高的教师的姓名和工资。,SELECT TN,SAL FROM T WHERE SAL(SELECT MIN(SAL )FROM TWHERE DEPT=计算机)AND DEPT!= 计算机 说明：先执行子查询，利用库函数MIN找到计算机系中所有教师的最低工

41、资，再执行父查询，查询所有不是计算机系且工资高于计算机系最低工资的教师。,此查询也可以写成：,ALL的含义为全部。 例51：查询其它系中比计算机系所有教师工资都高的教师的姓名和工资。 SELECT TN,SAL FROM T WHERE SALALL(SELECT SALFROM TWHERE DEPT=计算机) AND DEPT!= 计算机 说明：子查询找到计算机系中所有教师的工资集合(1500,900)； 父查询找到所有不是计算机系且工资高于1500的教师姓名和工资。,（2）带有ALL的子查询,SELECT TN,SAL FROM T WHERE SAL(SELECT MAX(SAL )F

42、ROM TWHERE DEPT=计算机)AND DEPT!= 计算机 说明：库函数MAX的作用是找到计算机系中所有教师的最高工资1500。,此查询也可以写成：,SELECT TN FROM T WHERE C5 !=ALL(SELECT CNO FROM TCWHERE TNO=T.TNO) 说明： !=ALL的含义为不等于子查询结果中的任何一个值，也可使用NOT IN代替!=ALL。 子查询包含普通子查询和相关子查询。 前面所讲的子查询均为普通子查询，而本例中子查询的查询条件引用了父查询表中的属性值（T表的TNO值），我们把这类查询称为相关子查询。,例52：查询不讲授课程号为C5的教师姓名。

43、,普通子查询的执行顺序是： 首先执行子查询，然后把子查询的结果作为父查询的查询条件的值。 普通子查询只执行一次，而父查询所涉及的所有记录行都与其查询结果进行比较以确定查询结果集合。 相关子查询的执行顺序是： 首先选取父查询表中的第一行记录，内部的子查询利用此行中相关的属性值进行查询， 然后父查询根据子查询返回的结果判断此行是否满足查询条件。如果满足条件，则把该行放入父查询的查询结果集合中。重复执行这一过程，直到处理完父查询表中的每一行数据。 由此可以看出，相关子查询的执行次数是由父查询表的行数决定的。如上例表T中每的一行即每个教师记录都要执行一次子查询以确定该教师是否讲授C5这门课，当 C5不

44、是该教师所任课时，则该教师被选取。,普通子查询和相关子查询的执行方式不同：,3、带有EXISTS的子查询 EXISTS表示存在量词，带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。 当子查询的Studenttudenttudenttudent查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。NOT EXISTS与此相反。 含有IN的查询通常可用EXISTS表示。,（以下几例均为相关子查询）,SELECT TN FROM T WHERE EXISTS(SELECT * FROM TCWHERE TNO=T.TNO AND CNO=C5) 说明：当子查询T

45、C表存在一行记录满足其WHERE子句中的条件时，则父查询便得到一个TN值，重复执行以上过程，直到得出最后结果。,例53：查询讲授课程号为C5的教师姓名。,SELECT SN FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS(SELECT * FROM SCWHERE SNO = S.SNOAND CNO=C.CNO) 说明：选出这样一些学生名单，没有一门课程是他不选的。,例54：查询选修所有课程的学生姓名。,3.4 SQL的数据操纵功能,SQL语言的数据操纵（更新）语句DML主要包括插入数据、修改数据和删除数据三种语句。一、插入

46、数据记录 插入数据是把新的记录插入到一个存在的表中。插入数据使用语句INSERT INTO，可分为以下几种情况。,语法格式为：INSERT INTO (,) VALUES() 其中： 是指要插入新记录的表。 是可选项，指定待添加数据的列 VALUES子句指定待添加数据的具体值。 列名的排列顺序不一定要和表定义时的顺序一致。但当指定列名表时VALUES子句值的排列顺序必须和列名表中的列名排列顺序一致，个数相等，数据类型一一对应。,1、插入一行新记录,INSERT INTO S VALUES (S7,郑冬,女,21,计算机) 说明： 必须用逗号将各个数据分开，字符型数据要用单引号括起来。 INTO

47、子句中没有指定列名，则新插入的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。 S,例55：在S表中插入一Studenttudenttudenttudent学生记录（学号：S7；姓名：郑冬；性别：女；年龄：21；系别：计算机）。,例56：在SC表中插入一条选课记录（S7,C1Studenttudenttudenttudent）。 INSERT INTO SC (SNO,CNO) VALUES (s7,c1) 说明：将VALUES子句中的值按照INTO子句中指定列名的顺序插入到表中对于INTO子句中没有出现的列，则新插入的记录在这些列上将取空值，如上

48、例的SCORE即赋空值。但在表定义时有NOT NULL约束的属性列不能取空值。,2、插入一行的部分数据值,插入多行记录用于表间的拷贝，将一个表中的数据抽取数行插入另一表中，可以通过子查询来实现。 插入数据的命令语法格式为： INSERT INTO (,) 子查询,3、插入多行记录,首先，建立新表AVGSAL，用来存放系名和各系的平均工资。 CREATE TABLE AVGSAL (DEPT VARCHAR(20), AVGSAL SMALLINT) 然后，利用子查询求出T表中各系的平均工资，把结果存放在新表AVGSAL中。 INSERT INTO AVGSAL(DEPT, AVGSAL) SELECT DEPT,AVG(SAL) FROM T GROUP BY DEPT,例57：求出各系教师的平均工资，把结果存放在新表AVGSAL中。,SQL语言可以使用UPDATE语句对表中的一行或多行记录的某些列值进行修改，其语法格式为：UPDATE SET = ,=WHERE 其中： 是指要修改的表。 SET子句给出要修改的列及其修改后的值。 WHERE子句指定待修改的记录应当满足的条件，WHERE子句省略时，则修改表中的所有记录。,二、修改数据记录,例58：把刘伟教师转到信息系。 UPDATE T SET DEPT=信息 WHERE TN=刘伟,1、修改一行,