数据库原理及应用教学辅助第05章-关系数据库的结构化查询语言SQL课件.ppt

1、 本章主要内容本章介绍关系数据库标准语言SQL。主要内容包括：数据定义、数据操纵、数据控制和数据约束等。(1)SQL数据库的体系结构，SQL的组成。(2)SQL的数据定义：SQL模式、基本表和索引的创建和撤销。(3)SQL的数据查询:SELECT语句的句法，SELECT语句的几种形式及各种限定，基本表的联接操作。(4)SQL的数据更新：插入、删除和修改语句。(5)视图的创建和撤消，对视图更新操作的限制。(6)嵌入式SQL:预处理方式，使用规定，使用技术，卷游标，动态SQL语句。关系数据库的结构化查询语言SQL5.1 SQL概述5.2 SQL的数据定义语言 5.3 SQL数据查询 5.4 SQL

2、聚集函数(Aggregation)5.5 SQL数据更新 5.6 SQL中的视图 5.7 嵌入式SQL 本章小结 5.1 SQL概述1)SQL的发展历程SQL语言1974年由Boyce和Chamberlin提出，并首先在IBM公司研制的关系数据库系统System R上实现。1986年10月，经美国国家标准局(ANSI)的数据库委员会批准了SQL作为关系数据库语言的美国标准，并公布了标准SQL文本。1987年6月国际标准化组织(ISO)将其采纳为国际标准，称为“SQL86”。相继出现了“SQL89”、“SQL2(SQL92)”、“SQL3”。SQL已成为关系数据库领域中的一个主流语言：首先，各个

3、数据库产品厂家纷纷推出了自己的支持SQL的软件或与SQL接口的软件。其次,SQL在数据库以外的其他领域也受到了重视。不少软件产品将SQL的数据检索功能与面向对象技术、图形技术、软件工程工具、软件开发工具、人工智能语言等相结合，开发出功能更强的软件产品。2)SQL数据库的体系结构视图视图V1用户用户1视图视图V2基本表基本表B1基本表基本表B2基本表基本表B3基本表基本表B4存储文件存储文件S1存储文件存储文件S2存储文件存储文件S3存储文件存储文件S4用户用户2用户用户3用户用户4SQL用户用户外模式外模式模式模式内模式内模式SQL数据库的体系结构的特征：一个SQL模式是表和约束的集合。一个表

4、（TABLE）是行的集合。每行是列的序列，每列对应一个数据项。一个表可以是一个基本表，也可以是一个视图。一个基本表可以跨一个或多个存储文件，一个存储文件也可存储一个或多个基本表。用户可以用SQL语句对视图和基本表进行查询等操作。SQL用户可以是应用程序，也可以是终端用户。3)SQL的组成(1)数据定义语言(Data Definition Language，简称DDL)用于定义SQL模式、基本表、视图和索引。(2)查询语言(Query Language，简称QL)用于数据查询。(3)数据操纵语言(Data Manipulation Language，简称DML)用于数据的增、删、修改。(4)数据

5、控制语言(Data Control Language，简称DCL)用于数据访问权限的控制。5.2SQL的数据定义语言 5.2.1 数据类型 5.2.2 基本表模式的定义 5.2.3 基本表的修改和删除 5.2.4 域 5.2.5 索引的建立和删除 5.2.1 数据类型 数据类型数据类型说明符说明符备注备注定义字符串定义字符串CHAR(n)按固定长度按固定长度n存储字符串，如果实际字符串长度长小于存储字符串，如果实际字符串长度长小于n，后，后面填空格符；如果实际字符串长大于面填空格符；如果实际字符串长大于n，则报错。，则报错。变长字符串变长字符串VARCHAR(n)按实际字符串长度存储，但字符长

6、度不得超过按实际字符串长度存储，但字符长度不得超过n，则报错。，则报错。整数整数INT常见的长整数，字长常见的长整数，字长32位位短整数短整数SMALLINT字长字长16位位十进制数十进制数DECIMAL(n,d)n为十进制数总位数为十进制数总位数(不包括小数点不包括小数点)，d为小数据点后的十进为小数据点后的十进制位数制位数浮点数浮点数FLOAT一般指双精度浮点数，即字长一般指双精度浮点数，即字长64位位定长位串定长位串BIT(n)二进制位串，长度为二进制位串，长度为n，n的缺省值为的缺省值为1变长位串变长位串BITVARING(n)按实际二进制位串存储，但最长不得超过按实际二进制位串存储，

7、但最长不得超过n位，否则报错位，否则报错日期日期DATE格式为格式为“yyyymmdd”,yyyy表示年份，范围为表示年份，范围为00019999;mm表示月份，范围为表示月份，范围为112;dd表示日，范围为表示日，范围为131。时间时间TIME格式为格式为“hhmmss”,hh表示小时，范围为表示小时，范围为024；mm为分钟，为分钟，ss表示秒，范围都是表示秒，范围都是059。时标时标TIMESTAMP格式为格式为“yyyymmddhhmmssnnnnnn”，其中，其中“nnnnnn”表示表示微秒，范围为微秒，范围为099999，其他符号的意义同上。，其他符号的意义同上。5.2.2 数据

8、表模式的定义 1）定义数据库的语句格式为:CREATE DATABASE database_name ON (NAME=file_name,FILENAME=os_file_name ,SIZE=size ,MAXSIZE=max_size|UNLIMITED ,FILEGROWTH=growth_increment )LOG ON (NAME=logical_ file_name,FILENAME=os_file_name ,SIZE=size ,MAXSIZE=max_size|UNLIMITED ,FILEGROWTH=growth_increment )事例 CREATE DATABA

9、SE studb ON (NAME=studb_dat,FILENAME=d:studb.mdf,SIZE=4,MAXSIZE=10,FILEGROWTH=1)LOG ON (NAME=studb_log,FILENAME=d:studb_log.LDF,SIZE=1,FILEGROWTH=10%);2）基本表SQL定义语句格式 定义基本表的语句格式为:CREATE TABLE(NOT NULL UNIQUE ,属性名2类型2 NOT NULL UNIQUE)其他参数；例5-1：学生成绩数据库含有三张表：学生关系：S(SNO,SNAME,SEX,AGE,DNAME)课程关系：C(CNO,CNA

10、ME,CREDIT,PRE_CNO)选课关系：SC(SNO,CNO,SCORE)可用下列SQL语句来实现：SQL事例 CREATE TABLE S (SNO CHAR(6)PRIMARY KEY，SNAME CHAR(8)NOT NULL，AGE SMALLINT，SEX CHAR(1)，DNAME VARCHAR(12)；CREATE TABLE C (CNO CHAR(2)NOT NULL,CNAME VARCHAR(24)NOT NULL,CREDIT SMALLINT,PRE_CNO CHAR(2),PRIMARY KEY(CNO);CREATE TABLE SC (SNO CHAR(

11、6)NOT NULL,CNO CHAR(2)NOT NULL,SCORE SMALLINT,PRIMARY KEY(SNO,CNO),FOREIGN KEY(SNO)REFERENCES S ON DELETE CASCADE,FOREIGN KEY(CNO)REFERENCES C ON DELETE RESTRICT);3)主关键字定义 方法1：一个关系的主关键字由一个或几个属性构成，在CREATE TABLE中使用保留字PRIMARY KEY声明主关键字：(1)在列出关系模式的属性时，在属性及其类型后加上保留字PRIMARY KEY，表示该属性是主关键字；(2)在列出关系模式的所有属性后

12、，再附加一个声明：PRIMARY KEY(，)如果关键字由多个属性构成，则必须使用方法(2)。SQL事例 CREATE TABLE S (SNO CHAR(6)PRIMARY KEY，/*第一种方式第一种方式*/SNAME CHAR(8)NOT NULL，AGE SMALLINT，SEX CHAR(1)，DNAME VARCHAR(12)；CREATE TABLE C (CNO CHAR(2)NOT NULL,CNAME VARCHAR(24)NOT NULL,CREDIT SMALLINT,PRE_CNO CHAR(2),PRIMARY KEY(CNO);/*第二种方式第二种方式*/CREA

13、TE TABLE SC (SNO CHAR(6)NOT NULL,CNO CHAR(2)NOT NULL,SCORE SMALLINT,PRIMARY KEY(SNO,CNO),/*第二种方式第二种方式*/FOREIGN KEY(SNO)REFERENCES S ON DELETE CASCADE,FOREIGN KEY(CNO)REFERENCES C ON DELETE RESTRICT);主关键字定义 方法2：使用保留字UNIQUE来说明关键字。它可以出现在PRIMARY KEY出现的任何地方，可以在同一个关系模式中出现多次。例如，在不出现同名同姓的情况下也可以将上面的定义改写为：CRE

14、ATE TABLE S (SNO CHAR(6)UNIQUE，SNAME CHAR(8)UNIQUE，SEX CHAR(1)，AGE SMALLINT，DNAME VARCHAR(12)；4)外部关键字的定义 在SQL中，有两种方法用于说明一个外部关键字：方法1:在表的属性名和类型后面直接用REFERENCES说明它参照了某个表的某些属性(必须是主关键字)，其格式为：REFERENCES 表名(属性)这种方法主要在外部关键字只有一个属性时可以使用。方法2：在CREATE TABLE语句的属性列表后面增加一个或几个外部关键字说明，其格式为：FOREIGN KEY 属性 REFERENCES 表名

15、()其中，第一个“属性”是外部关键字，第二个“属性”是被参照的属性。CREATE TABLE SC (SNO CHAR(6)NOT NULL,CNO CHAR(2)NOT NULL,SCORE SMALLINT,PRIMARY KEY(SNO,CNO),FOREIGN KEY(SNO)REFERENCES S ON DELETE CASCADE,FOREIGN KEY(CNO)REFERENCES C ON DELETE RESTRICT);基本表定义格式图示 CREATE TABLE表表名名 1（列列名名 1类类型型 1DEFAULT字字值值USERNULLNOT NULLUNIQUE，PR

16、IMARY KEY（列列表表名名 1），FOREIGN KEY（列列表表名名 2 2）外外键键名名REFERENCES表表名名 2ON DELETERESTRICTCASCADESET NULL，CHECK（条条件件）(;5.2.3 基本表的修改和删除 1)增加新的属性 ALTER TABLE 表的创建者名.表名 ADD属性名类型；例如：在表S中增加属性“BIRTHDATE”,“HOSTADDR”和“COMMADDR”：ALTER TABLE S ADD BIRTFIDATE DATE；ALTER TABLE S ADD HOSTADDR VARCHAR(32)；ALTER TABLE S A

17、DD COMMADDR VARCHAR(32)；2)删除原有属性的语句为 ALTER TABLE 表名表名DROP属性名属性名 CASCADE|RESTRICT；例如：在表S中删除“AGE”：ALTER TABLE S DROP AGE；3)基本表的删除在SQL中删除一个无用表的操作是非常简单的，其语句格式为：DROP TABLE 表名；主键修改4)补充定义主键 由于SQL并不要求每个表都定义主键，在需要时可以通过补充定义主键命令来定义主键。ALTER TABLE 表名 ADD PRIMARY KEY()；5)撤销主键定义利用下列的主键撤销命令可以暂时撤销主键定义：ALTER TABLE 表名

18、 DROP PRIMARY KEY；外键修改6)补充定义外键ALTER TABLE 表名 ADD FOREIGN KEY 属性 REFERENCES 表名()ON DELETE RESTRICT|CASCADE|SET NULL;7)撤消外键定义 ALTER TABLE 表名 DROP FOREIGN KEY 5.2.4 域 定义一个域的方法是:CREATE DOMAIN 域名 AS 数据类型;通过已定义域名来说明一个属性类型，其格式是：属性名1，属性名2，域名；例如：为表S的HOSTADDR和COMMADDR定义一个域ADDRDOMAIN：CREATE DOMAIN ADDRDOMAIN A

19、S VARCHAR(36)DEFAULT unknow；用域“ADDRDOMAIN”来说明属性“HOSTADDR”和“COMMADDR”的类型。HOSTADDR，COMMADDR ADDRDOMAIN；删除一个域定义的语句是：DROP DOMAIN域名；5.2.5 索引的建立和删除建立索引的语句格式为：CREATE UNIQUE INDEX 索引名 ON 基本表名(ASC|DESC,ASC|DESC)；例如，对表S建立以下索引 CREATE UNIQUE INDEX SNO_INDEX ON S(SNO)；CREATE UNIQUE INDEX SNAME_ADDR_INDEX ON S(SN

20、AME ASC，HOSTADDR DESC)；删除索引的语句格式：DROP INDEX 索引名；5.3 SQL数据查询 5.3.1 ALPHA关系演算语言5.3.2 SQL查询语句格式 5.3.3 简单查询 5.3.4 复杂查询 5.3.1 ALPHA关系演算语言语名的基本格式是：操作语句工作空间名（表达式）：操作条件（1）简单检索 例查询所有被选修课程的课程号。GETW（SC.SNO）例查询所有学生的数据。GETW（STUDENT）（2）限定的检索 例查询计算机系中年龄小于20岁的学生的学号和年龄。GETW（STUDENT.SNO，STUDENT.AGE）：STUDENT.DNAME=计算机

21、 STUDENT.AGE20ALPHA关系演算语言（3）带排序的检索 例查询计算机系学生的学号、年龄，并按降序排序。GET W(STUDENT.SNO,STUDENT.AGE):STUDENT.DNAME=计算机 DOWN STUDENT.AGE（4）带定额的检索例取出一个计算机系学生的学号。GET W（1）（STUDENT.SNO）：STUDENT.DNAME=计算机所谓带定额的检索是指检索出指定个数的元组，方法是在W后括号中加上定额数量。排序和定额可以一起使用。例查询计算机系年龄最大的3个学生的学号及年龄。GETW（3）（STUDENT.SNO，STUDENT.AGE）：STUDENT.D

22、NAME=计算机 DOWN STUDENT.AGEALPHA关系演算语言（5）用元组变量的检索因为元组变量是在某一关系范围内变化的，所以元组变量又称为范围变量。元组变量主要有两个方面的用途：简化关系名。操作条件中使用量词时必须用元组变量。例查询计算机系学生的姓名。RANGEStudentXGET W（X.SNAME）:X.DNAME=计算机这里元组变量X的作用是简化关系名Student。（6）用存在量词的检索 例查询选修C2课程的学生姓名。RANGESCX GET W（STUDENT.SNAME）:存在X（X.SNO=Student.SNO X.CNO=C2）例查询选修了其直接先行课程为C2课

23、程的学生学号。RANGECourseCX GET W（SC.SNO）:存在CX（CX.SNO=SC.SNO CX.PRE_CNO=C2）例查询至少选修一门其先行课程为C1课程的学生姓名。RANGECourseCX SCSCX GET W（Student.SNAME）:存在存在SCX（SCX.SNO=Student.SNO存在存在CX（CX.CNO=SC.CNOCX.PRE_CNO=C1）本例中的元组关系演算公式可以变换为前束范式的形式：GET W（Student.SNAME）:存在存在SCX存在存在CX（SCX.SNO=Student.SNO CX.CNO=SC.CNOCX.PRE_CNO=C

24、1）（7）带有多个关系的表达式的检索上面所举的各个例子中，虽然查询时可能会涉及多个关系，即公式中可以涉及多个关系，但查询都只在一个关系中，即表达式中只有一个关系。表达式中可以有多个关系的。例查询成绩为90分以上的学生姓名和课程名。本查询所要求的结果学生姓名和课程名分别在Student和Course两个关系中。RANGESCSCXGET W（Student.SNAME,Course.CNAME）:存在SCX（SCX.SCORE90SCX.SNO=Student.SNOCourse.CNO=SCX.CNO）（8）用全称量词的检索 例查询没有选修C1课程的学生姓名。本查询所要求的结果学生姓名和课程名

25、分别在Student和Course两个关系中。RANGECourseCXGET W（Student.SNAME）:任意SCX（SCX.SNOStudent.SNOSCX.CNOC1）本例实际上可以用存在量词来表示：GET W（Student.SNAME）:存在SCX（SCX.SNOStudent.SNOSCX.CNOC1）（9）用两种量词的检索 例查询了选修全部课程的学生姓名。RANGECourseCX SC SCX GET W（Student.SNAME）:任意 CX 存在 SCX（SCX.SNOStudent.SNOSCX.CNOCX.CNO）（10）用蕴函的检索 例查询至少选修了学生S1

26、所选课程的学生的学号。RANGECourseCX SC SCX SC SCY GET W（Student.SNO）:任意任意 CX（存在（存在 SCX（SCX.SNOS1SCX.CNOCX.CNO）存在存在 SCY（SCY.SNOStudent.SNOSCY.CNOCX.CNO）（11）集函数 提供了COUNT、TOTAL、MAX、MIN、AVG等集函数。例查询学生所在系的数目 GETW（COUNT（Student.DNAME）例查询计算机系学生的平均年龄 GETW（AVG（Student.AGE）：Student.DNAME=计算机）5.3.2 SQL查询语句格式 查询语句的基本部分:SEL

27、ECT FROM(或视图)WHERE 条件表达式；其SQL语句为：SELECT A1，,An FROM R1，,Rm WHERE F)RR(n1A,Am1F关系代数条件表达式F 条件表达式F中可使用下列运算符：算术比较运算符：、或！。逻辑运算符：AND、OR、NOT。集合运算符：IN、NOT IN。谓词：EXISTS(存在量词)、ALL、SOME、UNIQUE。聚合函数：AVG、MIN、MAX、SUM、COUNT。F中运算对象还可以是另一个SELECT语句，即SELECT语句可以嵌套。5.3.3简单查询 最简单的SQL查询只涉及到一个关系，类似于关系代数中的选择运算。例如：关系代数中的选择运算

28、DNAME计算机(S)的SQL查询语句如下例5.5。例5.5：在表S中找出计算机系学生的学号、姓名等信息。SELECT SNO，SNAME，AGE，SEX，DNAME FROM S WHERE DNAME计算机；例5.5 查询结果(a)学生关系表SNOSNAMEAGESEXDNAMES1程宏程宏19M M计算机计算机S3刘莎莎刘莎莎18F F电子电子S4李刚李刚20M M自动化自动化S6蒋天峰蒋天峰19M M电气电气S9王敏王敏20F F计算机计算机SNOSNAMEAGESEXDNAMES1程宏程宏19M M计算机计算机S9王敏王敏20F F计算机计算机(b)例5-5查询结果用通配符“*”简化

29、表示所有属性名:例5.6：求计算机系学生的详细信息。SELECT*FROM S WHERE DNAME=计算机；1)SQL中的投影 利用SELECT子句指定属性的功能完成关系代数中的投影运算。例5.7：在表S中找出计算机系学生的学号和姓名。SELECT SNO,SNAME FROM S WHERE DNAME=计算机；在SELECT子句中增加保留字“AS”和相应的别名,使结果表中的某些属性名不同于基本表中的属性名，如：例5.8：将例5-7结果表中的SNO换名为学号，将SNAME改为姓名:SELECT SNO AS 学号，SNAME AS 姓名 FROM S WHERE DNAME计算机；SEL

30、ECT子句中可以出现计算表达式，从而可以查询经过计算的值。例5.9：求学生的学号和出生年份。SELECT SNO，2006-AGE FROM S；SELECT后面可以是属性名，也可以是属性名与常数组成的算术表达式，还可以是字符串。例5.10：将例5.9改为：SELECT SNO,BIRTH_ YEAR：，2006-AGE FROM S；2)SQL中的选择运算 通过在WHERE子句中指定相应的条件表达式，完成关系代数中的选择运算。例5.11：列出表S中计算机系年龄小于20岁的学生的情况。SELECT*FROM S WHERE DNAME=计算机 AND AGE20；3)字符串的比较 SQL还提供

31、了根据模式匹配原理比较字符串的能力，其格式是：s LIKE p当且仅当字符串s与模式p相匹配时，表达式s LIKE p的值才为真。当p中含有时，它可以与s中任何序列的0个或多个字符进行匹配；而当p中出现符号“_”时，它可以与s中任何一个字符匹配。p中的字符只与s中相应位置上的字符匹配。例5.12：在表S中找出其姓名中含有“李”的学生。SELECT*FROM S WHERE SNAME LIKE李；在我们MIS/ERP系统中大都是采用这种模式匹配功能来实现模糊查询的。SQL查询语句格式图示S E L E C T基基 表表 名名.D IS T IN C T视视 图图 名名.表表 达达 式式列列 名

32、名 1 1*,F R O M表表 创创 建建 者者.基基 表表 名名视视 图图 名名别别 名名,W H E R E条条 件件 1 1G R O U P B Y列列 名名 2 2,H A V IN G条条 件件 2 2O R D E R B Y列列 名名 3 3列列 序序 号号A S CD E S C,SQL中SELECT语句的格式 SELECT DISTINCT FROM(或视图)WHERE 条件表达式；GROUP BY HAVING ORDER BY 属性列1ASC|DESC,整个SELECT语句的执行过程如下：(1)读取FROM子句中基本表、视图的数据，执行笛卡儿积操作。(2)选取满足WH

33、ERE子句中给出的条件表达式的元组。(3)按GROUP子句中指定的属性列的值分组，同时提取满足HAVING子句中组条件表达式的那些元组。(4)按SELECT子句中给出的属性列或列表达式求值输出。(5)ORDER子句对输出的结果进行排序，按ASC升序排列或DESC降序排列。5.3.4 复杂查询 例5.14：查询所有学生的情况以及他们选修课程的课程号和得分。SELECT S.*,SC.CNO,SC.SCORE FROM S,SC WHERE S.SNOSC.SNO；其中,WHERE后面的条件称为连接条件或连接谓词。例5.15：自然连接查询 SELECT S.SNO，S.SNAME，C.CNAME，

34、SC.SCORE FROM S，C，SC WHERE S.SNOSC.SNO AND C.CNOSC.CNO；1)SQL中的笛卡儿积和连接运算（a）学生关系学生关系SSNOSNAMEAGESEXDNAMES1程宏程宏19M计算机计算机S3刘莎莎刘莎莎18F电子电子S4李刚李刚20M自动化自动化S6蒋天峰蒋天峰19M电气电气S9王敏王敏20F计算机计算机（b）课程关系课程关系CCNOCNAMECREDITPRE_CNOC1计算机基础计算机基础3 C2C语言语言3C1C3电子学电子学4C1C4数据结构数据结构4C2（c）选课关系选课关系SCSNOCNOSCORES1C178S1C288S1C376

35、S1C486S3C264S3C387S4C379S6C188S6C368S9C278S9C483（d）自然连接查询的结果表自然连接查询的结果表S.SNOS.SNAMEC.CNAMESC.SCORES1程宏程宏计算机基础计算机基础78S1程宏程宏C语言语言88S1程宏程宏电子学电子学76S1程宏程宏数据结构数据结构86S3刘莎莎刘莎莎C语言语言64S3刘莎莎刘莎莎电子学电子学87S4李刚李刚电子学电子学79S6蒋天峰蒋天峰计算机基础计算机基础88S6蒋天峰蒋天峰电子学电子学68S9王敏王敏C语言语言78S9王敏王敏数据结构数据结构832)元组变量 表存在自身连接，即查询涉及同一个关系R的两个甚至

36、更多个元组。SQL采用的方法是在FROM子句中为R的每一个出现值指定一个别名(Alias)，称之为元组变量(Tuple Variable)，其格式是：FROM AS 然后在SELECT和WHERE子句中使用该别名指定属性。例5.16：在表C中求每一门课程的间接先行课。SELECT FIRST.CNO，SECOND.PRE_CNO FROM C AS FIRST，C AS SECOND WHERE FIRST.PRE_CNOSECOND.CNO；元组变量查询事例 CNOPRE_CNOC1C2C1C3C1C4C2(b)SECOND(a)FIRSTCNOPRE_CNOC1C2C1C3C1C4C2(c

37、)结果表结果表CNOPRE_CNOC4C1本例中的查询实际上是一种推理，即，若用谓词PC(x,y)表示y是x的先行课程，谓词PPC(x,z)表示z是x的间接先行课，则上述查询完成的推理可表示为：)z,x(PPC)z,y(PC)y,x(PC(zyx3)SQL查询中的并、交、差运算 SQL提供了相应的运算符:UNION,INTERSECT,EXCEPT,分别对应于集合运算的、(并、交、差)。(1)并运算的SQL查询语句例5.17：求选修了课程C2或C4的学生的学号和姓名。(SELECT S.SNO，S.SNAME FROM S，SC WHERE S.SNO=SC.SNO AND CNOC2)UNI

38、ON (SELECT S.SNO，S.SNAME FROM S，SC WHERE S.SNO=SC.SNO AND CNOC4)；交、差运算SQL(2)交运算的SQL查询语句例5.18：求选修了课程C2和C4的学生的学号和姓名。(SELECT S.SNO，S.SNAME FROM S，SC WHERE S.SNOSC.SNO AND CNO=C2)INTERSECT (SELECT S.SNO，S.SNAME FROM S，SC WHERE S.SNOSC.SNO AND CNOC4)；(3)差运算的SQL查询语句例5.19：求选修了课程C2但没有选修课程C4的学生的学号和姓名。(SELECT

39、 S.SNO，S.SNAME FROM S，SC WHERE S.SNOSC.SNO AND CNOC2)EXCEPT (SELECT S.SNO，S.SNAME FROM S，SC WHERE S.SNO=SC.SNO AND CNO=C4)；（a）学生关系学生关系SSNOSNAMEAGESEXDNAMES1程宏程宏19M计算机计算机S3刘莎莎刘莎莎18F电子电子S4李刚李刚20M自动化自动化S6蒋天峰蒋天峰19M电气电气S9王敏王敏20F计算机计算机（c）选课关系选课关系SCSNOCNOSCORES1C178S1C288S1C376S1C486S3C264S3C387S4C379S6C18

40、8S6C368S9C278S9C483查询例子4)子查询 WHERE子句中的条件表达式可以是标量数据，也可以是一个SELECT-FROM-WHERE查询块构成的子查询。(1)产生标量值的子查询例5.20：求选修了学生S3所选修的课程的那些学生的学号。SELECT SNO FROM SC WHERE CNO (SELECT CNO FROM SC WHERE SNOS3)；选课关系选课关系SCSNOCNOSCORES1C178S1C288S1C376S1C486S3C26487S3C3S4C379S6C188S6C368S9C278S9C483查询例子(2)包含几个关系的条件 EXISTS R是

41、一个条件，当且仅当R非空时，该条件为真。EXISTS相当于离散数学中的存在量词。s IN R为真，当且仅当s等于R中的一个值。类似地，s NOT IN R为真，当且仅当s不等于R中的值。IN的含义相当于集合论中的“属于”()。类似地,s NOT IN R,表示s不属于R。sALL R为真，当且仅当s大于关系R中的每一个值。同样可以使用其他五个比较运算符(=、=、=、)。例如,sALL R，表示s NOT IN R。sANY R为真，当且仅当s至少大于关系R中的一个值。同样可以使用其他五个比较运算符(=、=、=、)。例如,s=ANY R表示s IN R。IN查询事例 例5.21：使用运算符IN，

42、求选修了“数据结构”课程的学生的学号和姓名。SELECT SNO,SNAME FROM S WHERE SNO IN (SELECT SNO FROM SC WHERE CNO IN (SELECT CNO FROM C WHERE CNAME=数据结构)；(a)学生关系学生关系SSNOSNAMEAGESEXDNAMES1程宏程宏19M M计算机计算机S3刘莎莎刘莎莎18F F电子电子S4李刚李刚20M M自动化自动化S6蒋天峰蒋天峰19M M电气电气S9王敏王敏20F F计算机计算机(b)课程关系课程关系CCNOCNAMEDNAMETNAMEC1计算机基础计算机基础计算机计算机孙立孙立C2C

43、语言语言计算机计算机胡恒胡恒C3电子学电子学电子电子钱敏钱敏C4数据结构数据结构计算机计算机丁伟丁伟(c)选课关系选课关系SCSNOCNOSCORES3C387S4C379S1C288S9C483S1C376S6C368S1C178S6C188S3C264S1C486S9C278查询结果 执行执行III层层C.CNOC4执行执行II层层SC.SNOS9S1结果表结果表S.SNOS.SNAMES9王敏王敏S1程宏程宏(d)查询过程与结果查询过程与结果EXISTS查询事例 例5.22：使用存在量词EXISTS求选修了C2课程的学生的姓名。SELECT SNAME FROM S WHERE EXIS

44、TS (SELECT*FROM SC WHERE S.SNO=SC.SNO AND CNOC2)；学生关系学生关系SSNOSNAMEAGESEXDNAMES1程宏程宏19M计算机计算机S3刘莎莎刘莎莎18F电子电子S4李刚李刚20M自动化自动化S6蒋天峰蒋天峰19M电气电气S9王敏王敏20F计算机计算机选课关系选课关系SCSNOCNOSCORES1C178S1C288S1C376S1C486S3C264S3C387S4C379S6C188S6C368S9C278S9C483EXISTS查询事例 例5.23：用NOT EXISTS求没有选修C3课程的学生的姓名。SELECT SNAME FROM

45、 S WHERE NOT EXISTS (SELECT*FROM SC WHERE S.SNOSNO AND CNO=C3)；学生关系学生关系SSNOSNAMEAGESEXDNAMES1程宏程宏19M计算机计算机S3刘莎莎刘莎莎18F电子电子S4李刚李刚20M自动化自动化S6蒋天峰蒋天峰19M电气电气S9王敏王敏20F计算机计算机选课关系选课关系SCSNOCNOSCORES1C178S1C288S1C376S1C486S3C264S3C387S4C379S6C188S6C368S9C278S9C483(3)关于全称量词和逻辑蕴涵 在SQL中，通常将带有全称量词的谓词转换为带有存在量词的谓词：)

46、x(p(x()x(p)x()x(q)x(p)x(q)x(p从而利用SQL中WHERE子句的条件表达式中的EXISTS和NOT EXISTS即可实现该运算。SQL中也没有蕴涵(Implication)逻辑运算，可以利用下面的等价公式把蕴涵运算转换为非或运算：逻辑蕴涵事例 例5.24：求至少选修了学生S2所选修的全部课程的学生的学号。设Cy是学生S2选修课程的集合。如果用p表示谓词“学生S2选修课程Cy”，q表示谓词“学生Sx选修课程Cy”。则查询问题可表述为：SELECT DISTINCT SNOFROM SC AS SC_AWHERE NOT EXISTS (SELECT*FROM SC AS

47、 SC_B WHERE SC_B.SNOS2 AND NOT EXISTS (SELECT*FROM SC AS SC_C WHERE SC_C.SNOSC_A.SNO AND SC_C.CNO=SC_B.CNO)；)qp(Cy(qp)Cy()qp(Cy()qp)Cy(查询条件 SQL可以表达复杂的查询条件，进行多表连接的复杂查询运算。NOT简简单单条条件件简简单单条条件件ANDOR（）a a 条条件件比较条件比较条件between条件between条件like条件like条件in条件in条件exists条件exists条件（）b 简单条件 b 简单条件表表达达式式ISNOTNULL=表表达达

48、式式ALLANYSOME(子子查查询询块块)（）c c 比比较较条条件件项项项项*/（）d 表达式 d 表达式查询条件基基表表名名.视视图图名名.常常量量特特殊殊常常量量表表达达式式标标量量函函数数聚聚集集函函数数列列名名()（）e e 项项USERTIMESTAMPTIMEZONETIMEDATECURRENT（）f 特殊常量 f 特殊常量、日期 时间函数日期 时间函数、数字 字符函数数字 字符函数（）g 标量函数 g 标量函数AVGM AXM INSUMCO UNT()DISTINCT列列 名名(DISTINCT*列列 名名)（）h h 聚聚 集集 函函 数数查询条件比比较较条条件件b b

49、e et tw we ee en n条条件件l li ik ke e条条件件i in n条条件件e ex xi is st ts s条条件件（）b b 简简单单条条件件表达式表达式NOTBETWEEN表达式表达式AND表达式表达式（）i between条件 i between条件表达式表达式NOTLIKE字符串字符串（）j like条件 j like条件表表达达式式NOTIN子子查查询询块块常常量量（）k k i in n条条件件(),NOTEXISTS子查询块子查询块（）l exists条件 l exists条件()5.4 聚集函数(Aggregation)5.4.1 聚集函数的运算符 5.

50、4.2 数据分组 5.4.3 数据排序 5.4.1 聚集函数的运算符 AVG(属性列)，求某列上值的平均值。MAX(属性列)，求某列值中的最大值。MIN(属性列)，求某列值中的最小值。SUM(属性列)，求某一列上值的总和。COUNT(*)，计算元组的个数。COUNT(属性列)，计算某列值中的个数。注意，除非使用DISTINCT,否则，重复元组的个数也计算在内。AVGMAXMINSUMCOUNT()DISTINCT列列名名(DISTINCT*列列名名)聚聚集集函函数数SQL提供了下列的聚集函数：SELECT MAX(SCORE),MIN(SCORE)FROM SCSELECT COUNT(*)F