第三章关系数据库标准语

1、1 第三章第三章 关系数据库标准关系数据库标准 语言语言SQLSQL 2 教学内容： SQL语言概述； 数据定义DDL，数据查询QL，数据更新DML和数据控制 语言DCL的功能； 视图定义和使用； 要求掌握： 1、DDL，DML，DCL的语法结构； 2、会用SQL语言表达各种查询处理要求； 3、会使用视图 教学重点及难点： 数据查询，子查询 3 第三章第三章 关系数据库标准语言关系数据库标准语言SQL 第一节 SQL概述 第二节 数据定义 第三节 数据查询 第四节 数据更新 第五节 视图 4 第一节 SQL概述 1、什么是SQL语言 SQL语言是结构化查询语言，Structured Query

2、 Language, 简称SQL。 它是介于关系代数和关系演算之间的语言。 5 SQL起源 19741974年，年，IBMIBM的的Ray BoyceRay Boyce和和Don ChamberlinDon Chamberlin将将 CoddCodd关系数据库的关系数据库的1212条准则的数学定义以简单的条准则的数学定义以简单的 关键字语法表现出来，里程碑式地提出了关键字语法表现出来，里程碑式地提出了 SQL(Structured Query Language)SQL(Structured Query Language)语言。语言。 19761976年年IBM E.F.CoddIBM E.F.

3、Codd发表了一篇里程碑的论文发表了一篇里程碑的论文“R R 系统系统: :数据库关系理论数据库关系理论”，介绍了关系数据库理论，介绍了关系数据库理论 和查询语言和查询语言SQLSQL。 19771977年年OracleOracle开发了第一个商用开发了第一个商用SQLSQL关系数据库管关系数据库管 理系统理系统 6 (1)1974年，IBM的Boyce和Chamberlin为关系数据库原型系统System-R设计的一种查 询语言； 2 2、SQLSQL语言的发展语言的发展 ()1986年，ANSI公布第一个SQL标准：SQL86； ()1987年，ISO通过SQL86标准； ()1989年，

4、ISO制定SQL89标准； ()1990年，我国制定等同SQL89的国家标准； ()1992年，ISO制定SQL92标准，即SQL2； ()1999年，ANSI制定SQL3标准，即SQL3； (8 )2003年，ANSI制定SQL2003标准。 SQL语言是关系数据库的标准语言 7 3 3、SQLSQL的特点的特点 （1 1）综合统一）综合统一 集数据定义语言（集数据定义语言（DDLDDL），数据操纵语言），数据操纵语言 （DMLDML），数据控制语言（），数据控制语言（DCLDCL）功能于一体。）功能于一体。 可以独立完成数据库生命周期中的全部活动：可以独立完成数据库生命周期中的全部活动：

5、定义关系模式，插入数据，建立数据库；定义关系模式，插入数据，建立数据库； 对数据库中的数据进行查询和更新；对数据库中的数据进行查询和更新； 数据库重构和维护数据库重构和维护 数据库安全性、完整性控制等数据库安全性、完整性控制等 用户数据库投入运行后，可根据需要随时逐步用户数据库投入运行后，可根据需要随时逐步 修改模式，不影响数据的运行。修改模式，不影响数据的运行。 数据操作符统一数据操作符统一 8 （2 2）高度非过程化）高度非过程化 非关系数据模型的数据操纵语言非关系数据模型的数据操纵语言“面向过面向过 程程”，必须制定存取路径，必须制定存取路径 SQLSQL只要提出只要提出“做什么做什么”

6、，无须了解存取路，无须了解存取路 径。径。 存取路径的选择以及存取路径的选择以及SQLSQL的操作过程由系的操作过程由系 统自动完成。统自动完成。 9 （3 3）面向集合的操作方式面向集合的操作方式 非关系数据模型采用面向记录的操作方式，非关系数据模型采用面向记录的操作方式， 操作对象是一条记录操作对象是一条记录 SQLSQL采用集合操作方式采用集合操作方式 操作对象、查找结果可以是元组的集合操作对象、查找结果可以是元组的集合 一次插入、删除、更新操作的对象可以是元组一次插入、删除、更新操作的对象可以是元组 的集合的集合 10 （4 4）以同一种语法结构提供多种使用方以同一种语法结构提供多种使

7、用方 式式 SQLSQL是独立的语言是独立的语言 能够独立地用于联机交互的使用方式能够独立地用于联机交互的使用方式 SQLSQL又是嵌入式语言又是嵌入式语言 SQLSQL能够嵌入到高级语言（例如能够嵌入到高级语言（例如C C，C+C+， JavaJava）程序中，供程序员设计程序时使用）程序中，供程序员设计程序时使用 11 （5 5）语言简洁，易学易用语言简洁，易学易用 SQLSQL功能极强，完成核心功能只用了功能极强，完成核心功能只用了9 9个动词。个动词。 12 4 4、SQLSQL语言性质语言性质 (1)SQL语言 一种关系数据库语言 (2)SQL语言 一个应用程序开发语言， (3)SQ

8、L语言 一个DBMS 是 提供数据的定义、查询、更新和控制等功能。 不是 只提供对数据库的操作能力，不能完成屏幕控制、菜单管理、报表生成等 功能， 可成为应用开发语言的一部分. 它是DBMS为用户提供的交互语言。 不是 13 5 5、 关系数据库的体系结构关系数据库的体系结构 SQL语言支持关系数据库三级模式结构，但术语与传统的关系模型术语不同。 在关系模型中 模式 内模式 外模式 在SQL中 “基本表” “存储文件” “视图”或“基本表” 14 SQL 视图V1视图V2 基本表1基本表2基本表3基本表4 存储文件S1存储文件S2存储文件S4存储文件S3 外模式 模式 内模式 SQL对关系数据

9、库模式的支持 15 6 6、SQLSQL语言的分类语言的分类 SQL语言的命令通常分为四类 1）数据定义语言（） 2）查询语言（） 3）数据操纵语言（） 4）数据控制语言（） 16 1）数据定义语言（） 创建、修改或删除数据库中各种对象，包括SQL模式、基本表、视图、索引等。 命令： SCHEMASCHEMA TABLE TABLE VIEWVIEW INDEXINDEX （1 1）CREATE CREATE （2 2）DROPDROP SCHEMASCHEMA TABLE TABLE VIEWVIEW INDEXINDEX （）ALTER TABLE 17 2）查询语言（） 按照指定的组合、

10、条件表达式或排序检索已存在的数据库中数据，不改变数据 库中数据。 命令：SELECTFROMWHERE 18 3）数据操纵语言（） 对已经存在的数据库进行元组的插入、删除、修改等操作。 命令：INSERT、UPDATE、DELETE 4）数据控制语言（） 用来授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、 对数据库进行监视。 命令：GRANT、REVOKE、 COMMIT、ROLLBACK 19 第二节第二节 数据定义数据定义 SQLSQL的数据定义部分包括对的数据定义部分包括对SQLSQL 模式、基本表、视图和索引的创建和模式、基本表、视图和索引的创建和 撤销操作。撤销操

11、作。 20 一、一、 SQLSQL提供的基本数据类型提供的基本数据类型 数值型 Integer(int)：长整数。 smallint：短整数。 numeric（p，d）：定点数，共p位（不包括小数点），右边d位。 real：取决于机器精度的浮点数。 double precision：取决于机器精度的双精度浮点数。 float(n)：浮点数，精度至少为n位数字 21 字符型 char(n)固定长度为n的字符串。 varchar(n)最大长度为n的可变长字符串。 日期/时间型 date：日期（年、月、日），格式YYYY-MM-DD。 time：时间（小时、分、秒）,格式HH:MM:SS。 22 二

12、、二、SQLSQL模式的创建和删除模式的创建和删除 创建SQL模式即定义一命名空间，在这个空间中可以进一步定义该模式 包含的数据库对象，例如基本表、视图、索引等。 23 CREATE SCHEMA AUTHORIZATION 如果没有指定 ， 隐含为 Create schema authorization wang 1、创建模式： 、删除模式： DROP SCHEMA Drop schema wang cascade CASCADE (级联）方式： 删除模式的同时把模式中所有的数据库对象全部删除 RESTRICT (约束）方式： 只有当模式中没有任何下属对象时才能执行 24 3 3、在在CRE

13、ATE SCHEMACREATE SCHEMA中可以接受中可以接受CREATE CREATE TABLETABLE，CREATE VIEWCREATE VIEW和和GRANTGRANT子句。子句。 CREATE SCHEMA CREATE SCHEMA AUTHORIZATION AUTHORIZATION | 25 为用户ZHANG创建一个模式TEST，并在 其中定义了一个表TAB1。 CREATE SCHEMA TEST AUTHORIZATION ZHANG CREATE TABLE TAB1(COL1 SMALLINT， COL2 INT， COL3 CHAR(20)， COL4 NU

14、MERIC(10，3)， COL5 DECIMAL(5，2) )； 26 三、基本表的创建、修改和撤销三、基本表的创建、修改和撤销 CREATE TABLE ( 列级完整性约束条件 ,列级完整性约束条件 ,表级完整性约束条件 )； 1、创建基本表： 完整性约束条件被存入系统的数据字典中。 如果完整性约束条件涉及到该表的多个属性列时，必须在表级定义该约束条件， 否则既可以定义在列级，也可以定义在表级。 27 常用完整性约束 主码约束： PRIMARY KEY 唯一性约束：UNIQUE 非空值约束：NOT NULL 参照完整性约束：FOREIGN KEY REFERENCES 检查子句：CHECK

15、 保留字不能用作表名、列名等 28 例1：建立学生关系表 S(SNO,SN,age,sex) CREATE TABLE S (SNO CHAR(4) PRIMARY KEY, SN CHAR(8) NOT NULL, AGE SMALLINT, SEX CHAR(1) )； 29 例2：建立课程关系表 C(CNO,CN,T) CREATE TABLE C (CNO CHAR(4) PRIMARY KEY, CN CHAR(8) UNIQUE, T CHAR(10) )； 30 例3：建立选课关系表 SC(SNO,CNO,G) CREATE TABLE SC (SNO CHAR(4), CNO

16、CHAR(4), G SMALLINT, PRIMARY KEY (SNO,CNO), FOREIGN KEY(SNO)REFERENCES S(SNO), FOREIGN KEY(CNO)REFERENCES C(CNO), CHECK (G IS NULL) OR (G BETWEEN 0 AND 100) )； 31 2.2.基本表结构的修改基本表结构的修改 1）对表增加列： ALTER TABLE ADD 完整性约束,； 例：ALTER TABLE S ADD ADDR CHAR(20)； 不论原表中是否已存在数据，新增加的列一律为空值； 32 snosnagesexaddr s1wa

17、ng18Fnull s2li19Mnull null S 33 2）对表增加新的完整性约束条件： ALTER TABLE ADD ； 例：ALTER TABLE C ADD UNIQUE (CN)； 34 3）删除指定的完整性约束条件 语法：ALTER TABLE DROP ; SQL不提供删除列的语法，Oracle中允许删除列 通过实验回答以下问题： （1）在SQL Server2008中，删除列是否允许？ （2）删除主键所在的列是否允许？ 35 4）修改原有列定义： ALTER TABLE ALTER COLUMN ； 例：ALTER TABLE S ALTER COLUMN age in

18、t； 36 3.3.基本表的撤销基本表的撤销 语法: DROP TABLE CASCADE|RESTRICT 例：DROP TABLE S CASCADE 当删除表时，表的数据、表上建立的索引和视图都自动被删除。 RESTRICT：如存在依赖该表的对象（视图、索引、触发器、存储过程、 约束等），此表不能被删除。 CASCADE：删除该表的同时，相关的依赖对象被同时删除。 37 序序 号号 标准及主流数据库标准及主流数据库 依赖基本表的对象依赖基本表的对象 SQL99SQL99ORACLE 9iORACLE 9iMS SQLMS SQL SERVER SERVER 20002000 R RC C

19、C C 1.1.索引索引无规定无规定 2.2.视图视图 保留保留 保留保留 保留保留 3.3.DEFAULTDEFAULT，PRIMARY KEYPRIMARY KEY，CHECKCHECK（只含（只含 该表的列）该表的列）NOT NULL NOT NULL 等约束等约束 4.4.Foreign KeyForeign Key 5.5.TRIGGERTRIGGER 6.6.函数或存储过程函数或存储过程 保留保留 保留保留 保留保留 DROP TABLE时，SQL99 与 2个RDBMS的处理策略比较 R表示RESTRICT , C表示CASCADE 表示不能删除基本表，表示能删除基本表，保留表示

20、删除基本 表后，还保留依赖对象 38 4 4、模式与表、模式与表 1 1）每一个基本表都属于某一个模式）每一个基本表都属于某一个模式 2 2）一个模式包含多个基本表）一个模式包含多个基本表 3 3）定义基本表所属模式）定义基本表所属模式 n方法一：在表名中明显地给出模式名方法一：在表名中明显地给出模式名 Create table “S-T”.StudentCreate table “S-T”.Student（.）; /; /* *模模 式名为式名为 S-TS-T* */ / Create table “S-T”.CourseCreate table “S-T”.Course（.）; ; Cre

21、ate table “S-T”.SCCreate table “S-T”.SC（.）; ; n方法二：在创建模式语句中同时创建表方法二：在创建模式语句中同时创建表 n方法三：设置所属的模式方法三：设置所属的模式 39 4 4）创建基本表（其他数据库对象也一样）时，若没有）创建基本表（其他数据库对象也一样）时，若没有 指定模式，系统根据指定模式，系统根据搜索路径搜索路径来确定该对象所属的来确定该对象所属的 模式。模式。 5 5）搜索路径包含一组模式的列表。）搜索路径包含一组模式的列表。RDBMSRDBMS会使用该列会使用该列 表中表中第一个存在的模式第一个存在的模式作为数据库对象的模式名，作为数

22、据库对象的模式名， 若搜索路径中的模式名都不存在，系统给出错误。若搜索路径中的模式名都不存在，系统给出错误。 6 6）搜索路径的当前默认值是：）搜索路径的当前默认值是：$user$user， PUBLICPUBLIC；首先；首先 搜索与用户名相同的模式，如不存在，则使用搜索与用户名相同的模式，如不存在，则使用 PUBLICPUBLIC模式模式 40 7 7） DBADBA用户可以设置搜索路径，用户可以设置搜索路径， 然后定义基本表然后定义基本表 SET search_path TO “S-T”SET search_path TO “S-T”，PUBLICPUBLIC； Create table

23、 StudentCreate table Student（.）; ; 结果建立了结果建立了S-T.StudentS-T.Student基本表。基本表。 RDBMSRDBMS发现搜索路径中第一个模式名发现搜索路径中第一个模式名S-TS-T存在，就存在，就 把该模式作为基本表把该模式作为基本表StudentStudent所属的模式。所属的模式。 41 四、索引的创建和撤销四、索引的创建和撤销 建立索引是加快查询速度的有效手段 建立索引 DBA或表的属主（即建立表的人）根据需要 建立 有些DBMS自动建立以下列上的索引 PRIMARY KEY UNIQUE 维护索引 DBMS自动完成 使用索引 DB

24、MS自动选择是否使用索引以及使用哪些 索引 42 RDBMSRDBMS中索引一般采用中索引一般采用B+B+树、树、HASHHASH索引来实现索引来实现 nB+B+树索引具有动态平衡的优点树索引具有动态平衡的优点 nHASHHASH索引具有查找速度快的特点索引具有查找速度快的特点 采用采用B+B+树，还是树，还是HASHHASH索引则由具体的索引则由具体的RDBMSRDBMS来决定来决定 索引是关系数据库的内部实现技术，属于内模式索引是关系数据库的内部实现技术，属于内模式 的范畴的范畴 CREATE INDEXCREATE INDEX语句定义索引时，可以定义索引是语句定义索引时，可以定义索引是

25、唯一索引、非唯一索引或聚簇索引唯一索引、非唯一索引或聚簇索引 43 B B树和树和B+B+树树 B B树树 能自动保持与数据文件大小适应的索引能自动保持与数据文件大小适应的索引 层次层次 平衡树平衡树 能对所使用的存储空间进行管理，使每能对所使用的存储空间进行管理，使每 个块处于全满半满之间个块处于全满半满之间 B+B+树树 特殊的特殊的B B树，内部结点只存储索引块树，内部结点只存储索引块 叶结点用指针连接叶结点用指针连接 44 指向码值为指向码值为 3131的记录的记录指向码值为指向码值为 3737的记录的记录 指向码值为指向码值为 4141的记录的记录 指向顺序集上的指向顺序集上的 下一

26、个叶结点下一个叶结点 典型的叶结点典型的叶结点 指向码值指向码值 K 23K 23指向码值指向码值 23K31 23K31 指向码值指向码值 31K4331K43 指向码值指向码值 K43K43 典型的非叶结点典型的非叶结点 参数 n=3: 每个块存放码值的最大 个数:3 最小码数：m 最小指针数：p 45 B+B+树指针和码的数量树指针和码的数量 最大指针数最大指针数最大码数最大码数 最小指针数最小指针数 ( (指向数据指向数据) ) 最小码数最小码数 内部结点内部结点n + 1n + 1n n ( (n + 1) / 2n + 1) / 2 ( (n + 1) / 2n + 1) / 2

27、-1-1 叶结点叶结点n + 1n + 1n n (n + 1) / 2(n + 1) / 2 (n + 1) / 2(n + 1) / 2 根结点根结点n + 1n + 1n n1 11 1 46 B+树中的查找 查找键为37的记录 13 37 313743 找到37 键值为37的记录 IO数：4 若把第一、第二层结点保存在缓冲区 IO数：2 47 B+树中的查找(若索引块全在 磁盘) 查找键为37的记录 读入root节点,IO=1 根据条件13 37,读取下一 节点块 读入下一节点,IO=2 根据条件313743, 读取下一节点块 读入下一节点,IO=3,找到 键值37,读取记录指针, 读

28、取记录所在的块 Main Memory Disk 读入数据块,IO=4,根据记 录指针找到键值为37的记录 48 1 1、索引的创、索引的创 建建 语法：CREATE UNIQUE CLUSTER INDEX ON ( ASC|DESC ,ASC|DESC ) 49 唯一索引唯一索引 例例1:1: CREATE CREATE UNIQUE UNIQUE INDEX ST ON S(SNO)INDEX ST ON S(SNO) 其中其中UNIQUEUNIQUE表示要求列表示要求列SNOSNO的值在基本表的值在基本表S S 中不重复。中不重复。 例例2: 2: CREATE CREATE UNIQ

29、UEUNIQUE INDEX SC_INDDEX INDEX SC_INDDEX ON SC(SNO ASC, CNO DESC) ON SC(SNO ASC, CNO DESC) 缺省时，表示升序。缺省时，表示升序。 50 聚簇索引是指索引项的顺序与表中记录的物理顺序一聚簇索引是指索引项的顺序与表中记录的物理顺序一 致的索引组织。致的索引组织。 例例3 3：在：在StudentStudent表的表的SnameSname（姓名）列上建立一个聚簇索（姓名）列上建立一个聚簇索 引引 CREATE CLUSTER INDEX Stusname CREATE CLUSTER INDEX Stusnam

30、e ON Student(Sname) ON Student(Sname)； StudentStudent表中的记录将按照表中的记录将按照SnameSname值的升序存放值的升序存放 聚簇索引聚簇索引 51 SQL ServerSQL Server中的聚簇索引中的聚簇索引 一种特殊的平衡一种特殊的平衡 树，与前面的平衡树，与前面的平衡 树相比，差别在于树相比，差别在于 索引的叶子级。在索引的叶子级。在 聚集索引中，聚集索引中，叶子叶子 级并不包括索引键级并不包括索引键 和指针；它们就是和指针；它们就是 数据本身数据本身。这个差。这个差 异意味着数据并不异意味着数据并不 存储在堆结构中。存储在堆

31、结构中。 它们存储在索引的它们存储在索引的 叶子级，并按索引叶子级，并按索引 键进行排序。键进行排序。 52 建立建立聚集索引的好处聚集索引的好处 聚集索引对于那些经常要搜索范围值的列特别聚集索引对于那些经常要搜索范围值的列特别 有效。使用聚集索引找到包含第一个值的行后，有效。使用聚集索引找到包含第一个值的行后， 便可以确保包含后续索引值的行在物理相邻。便可以确保包含后续索引值的行在物理相邻。 如果对从表中检索的数据进行排序时经常要用如果对从表中检索的数据进行排序时经常要用 到某一列，则可以将该表在该列上聚集（物理到某一列，则可以将该表在该列上聚集（物理 排序），避免每次查询该列时都进行排序，

32、从排序），避免每次查询该列时都进行排序，从 而节省成本。而节省成本。 53 在最经常查询的列上建立聚簇索引以提高在最经常查询的列上建立聚簇索引以提高 查询效率查询效率 一个基本表上最多只能建立一个聚簇索引一个基本表上最多只能建立一个聚簇索引 经常更新的列不宜建立聚簇索引经常更新的列不宜建立聚簇索引 54 2 2、索引的撤销、索引的撤销 语法：DROP INDEX 例: DROP INDEX SC_INDDEX * 删除索引时，系统会同时从数据字典中删除有关该索引的描述。 * 索引建立后由系统维护，不需用户干预。 55 第三节第三节 数据查询数据查询 SQL的查询语句基本语法结构： SELECT

33、- FROM- WHERE SELECT A1,A2,An FROM R1,R2,Rn WHERE SELECT用于检索和统计数据 56 SELECT SELECT 语句完整的句法：语句完整的句法： SELECT DISTINCT 列表达式,列表达式 FROM 表名或视图名 ，表名或视图名 WHERE 条件表达式 (条件子句) GROUP BY 列名1 (分组子句) HAVING 组条件表达式 (组条件子句) ORDER BY 列名2ASC|DESC. (排序子句) 57 一、单表查询一、单表查询 例1：查看学生全部信息 1、选择表中若干列(SELECT) （1）查询指定列（或全部列） sel

34、ect sno,sname,age,sex from s； 或：select * from s； 58 select sno,sname from s ； 例例2 2：查询全部学生的学号和姓名。：查询全部学生的学号和姓名。 例3：查询全部学生的姓名和学号。 select sname,sno from s ； 59 select sno,sname, from S ; 若为新的属性取名，则可写为 select sno,sname,2011-age from S 例例4 4：查询学生学号、姓名和出生年份。：查询学生学号、姓名和出生年份。 （2）查询经过计算的列 2011-age as 出生年份 6

35、0 2、选择表中若干元组(WHERE) （1）取消值重复的行 例5：查询选修了课程的学生学号 sno 95001 95001 95001 95002 95002 Select distinct sno from sc; sno 95001 95002 用DISTINCT取消重复的值 Select sno from sc; 61 （2）查询满足条件的元组 查询满足条件的元组是通过WHERE子句实现。在WHERE子句中常用的查询条件如 表所示。 查询条件查询条件谓词谓词 比比 较较 =、 、 = 、 = 、 、!= 、! 、!18 and sex=女； 63 常用谓词常用谓词-like-like

36、用法: 列名like/not like 字符串 列必须为字符串； 通配符：“_” 可代表任一单个字符； “%” 可代表任意多个字符 例8：查询赵明同学的学号。 select sno from s where sname like 赵明； =? 64 通配符的使用： 例9：查询所有姓赵的学生的学号和姓名。 Select sno,sname From s Where sname like 赵% 例10：查询学号第二位是1的所有学生姓名。 Select sname From s Where sno like _1%; 65 例11：查询课程名为“DB_DE”的课程号。 Select cno From

37、 c Where cname like DB_DE 注意：上例中 后面的_ 不具有通配符含义 Where cname like DB_DE ESCAPE ; 问题：like何时可用代替？ 换码字符 66 如果如果LIKE后面的匹配串中不含通配符，则后面的匹配串中不含通配符，则 可用：可用： = 取代取代 LIKE != 或或 取代取代 NOT LIKE 67 常用谓词常用谓词null 用法：where sno is NULL/not NULL select sno from sc where grade is null； 例12：查询选修了课程但没参加 考试的学生的学号。 68 例13：查询所

38、有有成绩的学生学号和课程号 Select sno,cno From sc Where grade is not null; 69 含义：查找属性值在指定范围内的元组 用法： A Between B and C 等价于（A=B and AC OR AB) 常用谓词between 70 select sno,sname from s where age between 17 and 18 例例1414：查询所有年龄在：查询所有年龄在1717到到1818岁之间的学岁之间的学 生的学号和姓名。生的学号和姓名。 where age=17 71 select sno,sname from s where

39、age not between 17 and 18 例15：查询所有年龄不在17到18 岁之间的学生的学号和姓名。 where age18 or age17 72 用法：用来查找属性值属于指定集合的元组 not in sno in(95001,95002,95003) 常用谓词in 73 例16：查询选修了课程号1或2的学生的学号 Select sno From sc Where cno in (1, 2); 例17：查询年龄不是17岁和18岁的学生的学号 Select sno From s Where age not in (17, 18); where cno=1 or cno=2 whe

40、re age!=17 and age!=18 74 3、对查询结果排序(Order by) select sno,sname,2011-age as 出生年份 from S order by 出生年份,sno desc 例18：查询学生学号、姓名和出生年份,并按出生年份的升序排列，出生年份相同时，按 学号的降序排列 注意：对于排序列含空值 若按升序排列，含空值的元组将最后显示。 若按降序排列，含空值的元组将最先显示。 75 4 4、使用集合函数（、使用集合函数（5 5个）个） 1）count count (distinct)： 统计一列中值的个数,不计算空值 Distinct:表示计算时要取消

41、指定列中的重复值 count (distinct *)： 计算元组的个数，不管列值是否为空 76 2） sum(distinct)： 计算一列的总和(此列必须是数值型) 3） avg(distinct)： 计算一列的平均值(此列必须是数值型) 4） max(distinct)： 求一列值中的最大值 5） min(distinct)： 求一列值中的最小值 除count(*)外，都跳过空值而只处理非空值。 77 snocnograde s1c280 s2c270 s3c2 s4c290 Select avg(grade) From sc Where cno=c2此查询的结果为80 78 Selec

42、t count(*), AVG(age) From s Where sex=男 Select count( sno) From sc 例例1919：求男同学的总人数和平均年龄：求男同学的总人数和平均年龄 例例2020：统计选修了课程的学生人数：统计选修了课程的学生人数 distinct 79 Select max(grade), min(grade) From sc Where cno=c2 例例2121：求选修课程：求选修课程c2c2的学生的最的学生的最 高分和最低分。高分和最低分。 80 例例2222：求每个同学平均分求每个同学平均分 select sno , avg(grade) fro

43、m SC 5、对查询结果分组 Group by子句将查询结果按某一列或多列值分组，值相等的为一组。分组的目的 是细化集合函数的作用对象，如果未分组，集合函数作用于整个查询结果。分组后作 用于每一组，即每一组有一个函数值。 (group by与having) group by sno； 81 如果分组后还要求按一定条件对这些组进行选择，最终只输出满足条件的组，则 可以使用HAVING子句指定选择条件。 select sno from sc group by sno having count(cno)3 例23：查询选修课程在三门以上的 同学学号 82 WHEREWHERE子句子句与与HAVING

44、HAVING子句子句的的区别区别 （1 1）WHEREWHERE子句作用于基本表或视图，子句作用于基本表或视图， 从中选择满足条件的元组。从中选择满足条件的元组。 （2 2）GROUP BYGROUP BY对对WHEREWHERE的结果进行分组的结果进行分组 （3 3）HAVINGHAVING子句作用于组，从中选择满子句作用于组，从中选择满 足条件的组，即对分组数据进一步筛足条件的组，即对分组数据进一步筛 选。选。 83 二、多表查询（连接查询）二、多表查询（连接查询） 1、连接查询：同时涉及多个表的查询 2、连接条件或连接谓词：用来连接两个表的条 件 一般格式： . . . BETWEEN

45、. AND . 3、连接字段：连接谓词中的列名称 连接条件中的各连接字段类型必须是可比的， 但名字不必是相同的 84 连接操作的执行过程连接操作的执行过程 嵌套循环法嵌套循环法(NESTED-LOOP)(NESTED-LOOP) 首先在表首先在表1 1中找到第一个元组，然后从头开始中找到第一个元组，然后从头开始 扫描表扫描表2 2，逐一查找满足连接件的元组，找到，逐一查找满足连接件的元组，找到 后就将表后就将表1 1中的第一个元组与该元组拼接起来，中的第一个元组与该元组拼接起来， 形成结果表中一个元组。形成结果表中一个元组。 表表2 2全部查找完后，再找表全部查找完后，再找表1 1中第二个元组

46、，然中第二个元组，然 后再从头开始扫描表后再从头开始扫描表2 2，逐一查找满足连接条，逐一查找满足连接条 件的元组，找到后就将表件的元组，找到后就将表1 1中的第二个元组与中的第二个元组与 该元组拼接起来，形成结果表中一个元组。该元组拼接起来，形成结果表中一个元组。 重复上述操作，直到表重复上述操作，直到表1 1中的全部元组都处理中的全部元组都处理 完毕完毕 85 排序合并法排序合并法(SORT-MERGE)(SORT-MERGE) 常用于常用于= =连接连接 首先按连接属性对表首先按连接属性对表1 1和表和表2 2排序排序 对表对表1 1的第一个元组，从头开始扫描表的第一个元组，从头开始扫描

47、表2 2， 顺序查找满足连接条件的元组，找到后就将顺序查找满足连接条件的元组，找到后就将 表表1 1中的第一个元组与该元组拼接起来，形中的第一个元组与该元组拼接起来，形 成结果表中一个元组。当遇到表成结果表中一个元组。当遇到表2 2中第一条中第一条 大于表大于表1 1连接字段值的元组时，对表连接字段值的元组时，对表2 2的查询的查询 不再继续不再继续 86 排序合并法排序合并法 找到表找到表1 1的第二条元组，然后从刚才的中断的第二条元组，然后从刚才的中断 点处继续顺序扫描表点处继续顺序扫描表2 2，查找满足连接条件，查找满足连接条件 的元组，找到后就将表的元组，找到后就将表1 1中的第一个元

48、组与中的第一个元组与 该元组拼接起来，形成结果表中一个元组。该元组拼接起来，形成结果表中一个元组。 直接遇到表直接遇到表2 2中大于表中大于表1 1连接字段值的元组时，连接字段值的元组时， 对表对表2 2的查询不再继续的查询不再继续 重复上述操作，直到表重复上述操作，直到表1 1或表或表2 2中的全部元中的全部元 组都处理完毕为止组都处理完毕为止 87 索引连接索引连接(INDEX-JOIN)(INDEX-JOIN) 对表对表2 2按连接字段建立索引按连接字段建立索引 对表对表1 1中的每个元组，依次根据其连中的每个元组，依次根据其连 接字段值查询表接字段值查询表2 2的索引，从中找到满的索引

49、，从中找到满 足条件的元组，找到后就将表足条件的元组，找到后就将表1 1中的第中的第 一个元组与该元组拼接起来，形成结一个元组与该元组拼接起来，形成结 果表中一个元组果表中一个元组 88 连接查询的种类连接查询的种类 等值与非等值连接查询等值与非等值连接查询 自身连接自身连接 外连接外连接 复合条件连接复合条件连接 89 例24：查询每个学生及其选修课程的情况 SELECT Student.*，SC.* FROM Student，SC WHERE Student.Sno = SC.Sno； 1. 等值连接：连接运算符为= Student.SStudent.S nono 200215121200

50、215121 200215121200215121 200215121200215121 200215122200215122 200215122200215122 SnameSname 李勇李勇 李勇李勇 李勇李勇 刘晨刘晨 刘晨刘晨 SsexSsex 男男 男男 男男 女女 女女 SageSage 2020 2020 2020 1919 1919 SdeptSdept CSCS CSCS CSCS CSCS CSCS SC.SnoSC.Sno 200215121200215121 200215121200215121 200215121200215121 20021512220021512

51、2 200215122200215122 CnoCno 1 1 2 2 3 3 2 2 3 3 GradeGrade 9292 8585 8888 9090 8080 90 连接的两种语法连接的两种语法 1、ANSI SQL-89语法语法 SELECT Student.SELECT Student.* *，SC.SC.* * FROM Student FROM Student，SCSC WHERE Student.Sno = SC.Sno WHERE Student.Sno = SC.Sno； 2、ANSI SQL-92语法语法 SELECT Student.SELECT Student.*

52、*，SC.SC.* * FROM Student FROM Student JOINJOIN SC SC ONON Student.Sno = SC.Sno Student.Sno = SC.Sno； 91 例25：查询女学生的学号、姓名、成绩： S.Sno,Sname,Grade（sex=女 s.sno=sc.sno (SSC)） 解： SELECT S.SNO,SNAME,GRADE FROM S, SC WHERE S.SNO=SC.SNO AND SEX=女； 关系代数： 92 select s.sno,sname from s，sc where s.sno=sc.sno and (c

53、no=c2 or cno=c4)； 例例26:26:查询选修查询选修c2c2或或c4c4课程的学生学号和姓名课程的学生学号和姓名 Sno，Sname(Cno=C2 Cno=C4 (S SC) 关系代数： 93 例例2727：找出平均成绩：找出平均成绩9090以上的女生姓名以上的女生姓名 select sname from s，sc where s.sno=sc.sno and sex= 女 group by sname having avg(grade)90 ； Oracle中，GROUP BY子句中的列一定要出现在SELECT中 94 例27：找出平均成绩90以上的女 生姓名 select

54、sno,sname from s，sc where s.sno=sc.sno and sex= 女 group by sno having avg(grade)90 ； () 注意：如果使用了分组子句，则查询列表中的每个列要么是分组依据列 （GROUP BY），要么是聚集函数 95 group by sno,sname having avg(grade)90 ； select sno,sname from s，sc where s.sno=sc.sno and sex= 女 96 select sname from s where sno in (select sno from s，sc wh

55、ere s.sno=sc.sno and sex= 女女 group by sno having avg(grade)90) ； 97 SELECT C.CNO, C.CNAME, AVG(Grade) FROM SC ,C WHERE SC.CNO=C.CNO and TName=LIU 例例2828：求：求LIULIU老师所授课程的名称及每老师所授课程的名称及每 门课程的学生平均成绩。门课程的学生平均成绩。 GROUP BY C.CNO,C.CNAME 注意：如果使用了分组子句，则查询列表中的每个列要么是分组依据列（GROUP BY），要么是聚集函数 98 select age, coun

56、t(distinct s.sno) from s,sc where s.sno=sc.sno group by age 例例29:29: 统计每一年龄选修课程的学生人数统计每一年龄选修课程的学生人数 99 SELECT FIRST.Cno，SECOND.Cpno FROM Course FIRST，Course SECOND WHERE FIRST.Cpno = SECOND.Cno； 例例3030：查询每一门课的间接先修课（即先修课查询每一门课的间接先修课（即先修课 的先修课）的先修课） 2. 自身连接 同一个表的不同元组之间的连接称为自身连接。必须给表取别名，当作两个不同 的表来处理。 1

57、00 自身连接（续）自身连接（续） FIRST FIRST表（表（CourseCourse表）表） CnoCno Cname CnameCpnoCpnoCcreditCcredit 1 1数据库数据库 5 5 4 4 2 2数学数学 2 2 3 3信息系统信息系统 1 1 4 4 4 4操作系统操作系统 6 6 3 3 5 5数据结构数据结构 7 7 4 4 6 6数据处理数据处理 2 2 7 7PASCALPASCAL语言语言 6 6 4 4 101 自身连接（续）自身连接（续） CnoCno Cname CnameCpnoCpnoCcreditCcredit 1 1数据库数据库 5 5 4

58、 4 2 2数学数学 2 2 3 3信息系统信息系统 1 1 4 4 4 4操作系统操作系统 6 6 3 3 5 5数据结构数据结构 7 7 4 4 6 6数据处理数据处理 2 2 7 7PASCALPASCAL语言语言 6 6 4 4 SECOND SECOND表（表（CourseCourse表）表） 102 自身连接（续）自身连接（续） 查询结果：查询结果： CnoCno 1 1 3 3 5 5 PcnoPcno 7 7 5 5 6 6 103 select x.sno from sc as x，sc as y where x.sno=y.sno and o=c2 and o=c4 ； 例

59、31：查询至少选修c2和c4课程的学 生学号。 1(1=4 2=C2 5=C4 (SCSC) 关系代 数： 104 select s.sno，sname from s , sc ，c where s.sno=sc.sno and o=o and ame=DB ； 例例3232：查询选修课程名为：查询选修课程名为DBDB的学生学号和姓名。的学生学号和姓名。 3. 多表的连接 Sno,Sname(Cname=DB(S SC C)关系代 数： 105 3 3、外连接、外连接 外连接与普通连接的区别外连接与普通连接的区别 普通连接操作只输出满足连接条件的元普通连接操作只输出满足连接条件的元 组组 外连

60、接操作以指定表为连接主体，将主外连接操作以指定表为连接主体，将主 体表中不满足连接条件的元组一并输出体表中不满足连接条件的元组一并输出 106 外连接（续）外连接（续） 左外连接左外连接 列出左边关系中所有的元组，右边关系中未有列出左边关系中所有的元组，右边关系中未有 匹配的列用匹配的列用NULLNULL作为占位符作为占位符 右外连接右外连接 列出右边关系中所有的元组，左边关系中未有列出右边关系中所有的元组，左边关系中未有 匹配的列用匹配的列用NULLNULL作为占位符作为占位符 全外连接全外连接 列出两边关系中所有的元组，未有匹配的列用列出两边关系中所有的元组，未有匹配的列用 NULLNUL