《关系数据库标准语言SQL-new简化.ppt》由会员分享，可在线阅读，更多相关《关系数据库标准语言SQL-new简化.ppt（145页珍藏版）》请在金锄头文库上搜索。
1、第四章第四章 SQL语言语言第四章第四章 SQL语言语言4.1 SQL4.1 SQL概述概述概述概述4.2 4.2 示例数据库示例数据库示例数据库示例数据库4.3 SQL4.3 SQL数据定义功能数据定义功能数据定义功能数据定义功能4.4 SQL4.4 SQL数据查询功能数据查询功能数据查询功能数据查询功能4.5 SQL4.5 SQL数据修改功能数据修改功能数据修改功能数据修改功能4.6 SQL4.6 SQL数据控制功能数据控制功能数据控制功能数据控制功能4.7 4.7 嵌入式嵌入式嵌入式嵌入式SQLSQL及存储过程及存储过程及存储过程及存储过程4.8 ODBC 4.8 ODBC 编程编程编程
2、编程4.1 SQL概述概述历史历史l1974年，由Boyce和Chamber提出。l1975-1979年，在System R上实现，由IBM的San Jose研究室研制，称为Sequel，现在称为SQL (Struceured Query Languang),结构化查询语言，是关系数据库的标准语言; SQL是一个通用的、功能极强的关系数据库语言。4.1 SQL概述概述有关标准有关标准SQL-86：“数据库语言SQL”， 1986.10SQL-89：“具有完整性增强的数据库语言SQL”，增加了对完整性约束的支持。 1989年SQL-92：“数据库语言SQL”，是SQL-89的超集，增加了许多新特
3、性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 1992年SQL-3：又称SQL-99，增加对面向对象模型的支持。 1999年SQL2003 2003年4.1 SQL概述概述SQLSQL语言的特点语言的特点1.1.综合统一综合统一n集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。n可以独立完成数据库生命周期中的全部活动： 定义关系模式，插入数据，建立数据库； 对数据库中的数据进行查询和更新； 数据库重构和维护 数据库安全性、完整性控制等n用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。n单一的结构-关系，数据操作符统一
4、4.1 SQL概述概述2.高度非过程化高度非过程化l非关系数据模型的数据操纵语言“面面向向过过程程”，必须制定存取路径l用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。l 存取路径的选择以及SQL的操作过程由系统自动完成。4.1 SQL概述概述3.面向集合的操作方式面向集合的操作方式l非关系数据模型采用面向记录的操作方式，操作对象是一条记录lSQL采用集合操作方式 操作对象、查找结果可以是元组的集合 一次插入、删除、更新操作的对象可以是元组的集合4.1 SQL概述概述4.以同一种语法结构提供多种使用方式以同一种语法结构提供多种使用方式lSQL是独立的语言 能够独立地用于联机交互的
5、使用方式lSQL又是嵌入式语言 SQL能够嵌入到高级语言（例如C，C+，Java）程序中，供程序员设计程序时使用4.1 SQL概述概述5. 5. 语言简洁，易学易用语言简洁，易学易用 SQL功能极强，完成核心功能只用了9个动词。4.1 SQL概述概述SQL语言的基本概念语言的基本概念 1.SQL支持关系数据库三级模式结构。SQL视图视图2视图视图1基本表基本表2基本表基本表1基本表基本表3基本表基本表4存储文件存储文件2存储文件存储文件1外模式外模式模模 式式内模式内模式4.1 SQL概述概述2.基本表n本身独立存在的表nSQL中一个关系就对应一个基本表n一个(或多个)基本表对应一个存储文件n
6、一个表可以带若干索引3.存储文件n逻辑结构组成了关系数据库的内模式n物理结构是任意的，对用户透明4.视图n从一个或几个基本表导出的表n数据库中只存放视图的定义而不存放视图对应的数据n视图是一个虚表n用户可以在视图上再定义视图4.2 示例数据库示例数据库DEPT(DNO , DNAME , DEAN) 系表系表S(SNO , SNAME , SEX , AGE , DNO,ADRESS) 学生表学生表COURSE(CNO ,CN ,PCNO ,CREDIT,GRADE) -课程表课程表SC(SNO , CNO , SCORE) -学生选课表学生选课表PROF(PNO, PNAME, AGE, D
7、NO , SAL) -教师表教师表PC(PNO , CNO) -教师授课表教师授课表学生成绩及教师任课数据库学生成绩及教师任课数据库4.3 SQL数据定义功能数据定义功能4.3.1 数据库的建立与撤消数据库的建立与撤消4.3.2 域定义域定义4.3.3 基本表的定义基本表的定义4.3.4 索引的定义索引的定义4.3.5 数据库的建立与撤消数据库的建立与撤消4.3.6 SQL数据定义特点数据定义特点4.3 SQL数据定义功能数据定义功能SQL的数据定义功能: 域定义、表定义、视图和索引的定义。4.3.1 数据库的建立与撤消数据库的建立与撤消有的数据库系统支持多库。建立一个新数据库建立一个新数据库
8、 create database数据库名示例: create database student指定当前数据库指定当前数据库 database数据库名关闭当前数据库关闭当前数据库 close database数据库名撤消一个数据库撤消一个数据库 drop database数据库名示例:drop database student4.3.2域定义域定义域类型（域类型（SQL-92） SQL中域的概念用数据类型来实现，定义表的属性时需要指明其数据类型及长度 。lchar（n）：固定长度的字符串。lvarchar（n）：可变长字符串。lint：整数。lsmallint：小整数类型。lnumeric（p，
9、d）：定点数，小数点左边p位，右边q位。lreal：浮点数，取决于机器精度。ldoubleprecision：双精度浮点数，取决于机器精度。ldate：日期（年、月、日）。ltime：时间（小时、分、秒）。linterval：两个date或time类型数据之间的差。4.3.2域定义域定义域定义域定义l格式createdomain域名数据类型l示例create domainperson-namechar（20）类似C语言中：typedefADDRESS_LISTcharname10;chartelephone20;charlocation20charemail20;ADDRESS_LISTtom
10、;4.3.3 基本表的定义基本表的定义基本表的定义（基本表的定义（CREATE）createtable表名（列名数据类型default缺省值notnull，列名数据类型default缺省值notnull，primarykey（列名，列名），foreignkey（列名，列名）references表名（列名，列名），check（条件）4.3.3 基本表的定义基本表的定义示例示例1 1 建立“学生”表S，学号是主码，姓名取值唯一。 CREATE TABLE S (Sno CHAR(9) PRIMARY KEY， /* 列级完整性约束条件*/ Sname CHAR(20) UNIQUE， /* Sna
11、me取唯一值*/ sex CHAR(2)， age SMALLINT， DNO CHAR(20) )； 4.3.3 基本表的定义基本表的定义l示例示例2 create domain person_name char(20)create table PROF( PNO char(10), PNAME person_name not null, SAL int, AGE int, DNO char(10), primary key (PNO), foreign key (DNO) references DEPT(DNO), check (SAL 0)4.3.3 基本表的定义基本表的定义示例示例3
12、3 建立一个“学生选课”表SCCREATETABLESC(SnoCHAR(9)，CnoCHAR(4)，SCORESMALLINT，PRIMARYKEY(Sno，Cno)，/*主码由两个属性构成，必须作为表级完整性进行定义*/FOREIGNKEY(Sno)REFERENCESS(Sno)，/*表级完整性约束条件，Sno是外码，被参照表是S*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/);4.3.3 基本表的定义基本表的定义修改基本表定义（修改基本表定义（ALTER）l格式： ALTER TABLE AD
13、D完整性约束-增加新列DROP-删除列ALTERCOLUMN；-修改列定义4.3.3 基本表的定义基本表的定义l示例1 向prof表增加“location”列，字符类型为字符型。alter tablealter table PROFaddadd LOCATION char(30) 不论基本表中原来是否已有数据，新增加的列的值一律为空值。l示例2 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。 ALTER TABLE S ALTER COLUMN age INT；l示例3 增加课程名称必须取唯一值的约束条件。 ALTER TABLE Course ADD UNIQUE(Cnam
14、e);4.3.3 基本表的定义基本表的定义撤消基本表定义（撤消基本表定义（drop）l格式drop table表名RESTRICT|CASCADE；nRESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除nCASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除危险撤消基本表后，基本表的定义、表中数据、索引、以及由此表导出的视图的定义都被删除。4.3.3 基本表的定义基本表的定义示例示例1 1 删除删除S S表表DROP TABLE Student CASCADE;n基本表定义被删除，数据被删除n表上建立的索引、视
15、图、触发器等一般也将被删除示例示例2 2 若表上建有视图，选择若表上建有视图，选择RESTRICTRESTRICT时表不能删除时表不能删除 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM S WHERE Sdept=IS； DROP TABLE Student RESTRICT; -ERROR: cannot drop table Student because other objects depend on it4.3.3 基本表的定义基本表的定义示例示例3 3 如果选择如果选择CASCADECASCADE时可以删除表，视图也自动时可
16、以删除表，视图也自动被删除被删除 DROP TABLE S CASCADE; -NOTICE: drop cascades to view IS_Student SELECT * FROM IS_Student; -ERROR: relation IS_Student does not exist4.3.4 索引的定义索引的定义建立索引的目的：加快查询速度建立索引的目的：加快查询速度谁可以建立索引谁可以建立索引lDBA或表的属主（即建立表的人）lDBMS一般会自动建立以下列上的索引PRIMARYKEYUNIQUE谁谁 维护索引维护索引DBMS自动完成使用索引使用索引DBMS自动选择是否使用索引
17、以及使用哪些索引CREATEINDEX语句定义索引时，可以定义索引是唯一、非唯一或聚簇索引 4.3.4索引的定义索引的定义索引的定义索引的定义l格式createunique|distinctclusterindex索引名on表名(列名asc|desc,列名asc|desc)unique|distinct：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新。 cluster：聚集索引，表中元组按索引项的值排序并物理地聚集在一起。一个基本表上只能建一个聚集索引。asc|desc：索引表中索引值的排序次序，缺省为asc
18、。4.3.4索引的定义索引的定义示例示例1 : CREATE CLUSTER INDEX Stusname ON S (Sname)；-在S表的Sname（姓名）列上建立一个聚簇索引示例示例2 为学生为学生-课程数据库中的课程数据库中的S，Course，SC三个表建立索引。三个表建立索引。CREATEUNIQUEINDEXStusnoONS(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；-S表按学号升序建唯一索引-Course表按课程号升序建唯一索引-SC表按学号升序和课程
19、号降序建唯一索引4.3.4索引的定义索引的定义索引的删除索引的删除l格式：dropindex索引名删除索引时，系统会从数据字典中删去有关该索引的描述。示例1：删除S表的Stusname索引DROPINDEXStusname；4.3.4索引的定义索引的定义索引的有关说明索引的有关说明l可以动态地定义索引，即可以随时建立和删除索引。l不允许用户在数据操作中引用索引。索引如何使用完全由系统决定，这支持了数据的物理独立性。l在最经常查询的列上建立聚簇索引以提高查询效率。l经常更新的列不宜建立聚簇索引。l应该在使用频率高的、经常用于连接的列上建索引。l一个表上可建多个索引。索引可以提高查询效率，但索引过
20、多耗费空间，且降低了插入、删除、更新的效率。4.3.5 SQL数据定义特点数据定义特点SQL中，任何时候都可以执行一个数据定义语句，随时修改数据库结构。数据库定义不断增长（不必一开始就定义完整）。数据库定义随时修改（不必一开始就完全合理）。可进行增加索引、撤消索引的实验，检验其对效率的影响。4.4 SQL数据查询功能数据查询功能4.4.1 SQL数据查询基本结构数据查询基本结构4.4.2单表查询4.4.3连接查询4.4.4嵌套查询4.4.5集合查询4.4.6Select语句的一般形式4.4 SQL数据查询功能数据查询功能语句格式语句格式 SELECT ALL|DISTINCT ，FROM，WH
21、EREGROUPBYHAVINGORDERBYASC|DESC；4.4.1 SQL数据查询基本结构数据查询基本结构基本结构基本结构select A1 , A2 , , Anfrom r1 , r2 , , rmwhereP A1 , A2 , , An(p(r1 r2 rm)示例示例给出所有老师的姓名。select PNAME from PROF?4.4.2单表查询查询仅涉及一个表：查询仅涉及一个表：n一、选择表中的若干列n二、From子句n三、选择表中的若干元组n四、ORDERBY子句n五、聚集函数n六、GROUPBY子句n七、更名运算4.4.2 单表查询单表查询-选择表中的若干列选择表中的
22、若干列目标列形式目标列形式：可以为指定列名，所有属性列(*) ，目标列表达式。l查询指定列查询指定列例2查询全体学生的姓名、学号、所在系代码。例1查询全体学生的学号与姓名。SELECTSno，SnameFROMS；SELECTSname，Sno，DnoFROMS；4.4.2 单表查询单表查询-选择表中的若干列选择表中的若干列l查询所有属性列：n在SELECT关键字后面列出所有列名 n将指定为 *，表示“所有的属性”例3查询全体学生的详细记录。SELECT Sno，Sname，sex，age，Dno FROM S；或SELECT * FROM S；两者完全等价。4.4.2 单表查询单表查询-选择
23、表中的若干列选择表中的若干列 例例11查询全体学生的姓名、出生年份和查询全体学生的姓名、出生年份和所有地址所有地址，要求用小写字母表示所有地址要求用小写字母表示所有地址SELECT Sname， Year of Birth: ， 2007 - age， LOWER(adress)FROM S；-LOWER() :为SQL SERVER函数输出结果：输出结果：Sname 无列名 无列名 无列名 李勇 Year of Birth: 1984 abc刘晨 Year of Birth: 1985 bcv王敏 Year of Birth: 1986 mmm张立 Year of Birth: 1985 k
24、kk目标列表达式目标列表达式可以为：算术表达式(带，)字符串常量函数列别名l使用更名运算使用更名运算old_name as new_name为关系和属性重新命名，可出现在select和from子句中。As可省略。SELECT Sname， Year of Birth: as BIRTH ， 2007 age BIRTHDAY ， LOWER(adress) AdressFROM S；-LOWER() :为SQL SERVER函数输出结果：输出结果：Sname BIRTH BIRTHDAY Adress李勇 Year of Birth: 1984 abc刘晨 Year of Birth: 198
25、5 bcv王敏 Year of Birth: 1986 mmm张立 Year of Birth: 1985 kkk4.4.2单表查询查询仅涉及一个表：查询仅涉及一个表：n一、选择表中的若干列n二、From子句n三、选择表中的若干元组n四、ORDERBY子句n五、聚集函数n六、GROUPBY子句n七、更名运算4.4.2 单表查询单表查询from子句子句说明说明from子句列出查询的对象表。当目标列取自多个表时，在不混淆的情况下可以不用显式指明来自哪个关系。示例示例例：找出工资低于500的职工的姓名、工资、系别。 select PNAME , SAL , DNAME from PROF , DEP
26、T where SAL ，=，=，!=，!，!，=，=，!=，!，!；NOT+上述比较运算符上述比较运算符例例1 查询计算机科学系全体学生的姓名。查询计算机科学系全体学生的姓名。SELECT Sname FROM S WHERE Dno=CS；例例2 查询考试成绩有不及格的学生的学号。查询考试成绩有不及格的学生的学号。SELECTDISTINCTSnoFROMSCWHEREGrade=20ANDage=23?SELECTSname，Dno，ageFROMSWHEREageNOTBETWEEN20AND23；4.4.2 单表查询单表查询-选择表中的若干元组选择表中的若干元组(3) .确定集合确定
27、集合格式：表达式格式：表达式 IN , 表达式表达式 NOT IN 例例1查询信息系（查询信息系（IS）、）、数学系（数学系（MA）和计算机科学系（和计算机科学系（CS）学生的姓名学生的姓名和性别。和性别。SELECTSname，sexFROMSWHEREDnoIN(IS，MA，CS);例例2查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，sexFROMSWHEREDno=ISorDno=MAOrDno=CS?SELECTSname，SsexFROMStudent WHERESdeptNOT
28、IN(IS，MA，CS)4.4.2 单表查询单表查询-选择表中的若干元组选择表中的若干元组（4）.字符串的处理字符串的处理格格式式：列列名名 NOT LIKE ESCAPE 1)匹配串为固定字符串匹配串为固定字符串例1查询学号为200215121的学生的详细情况。SELECT*FROMSWHERESnoLIKE200215121；等价于：SELECT*FROMStudentWHERESno=200215121；4.4.2 单表查询单表查询-选择表中的若干元组选择表中的若干元组2) 匹配串为含通配符的字符串匹配串为含通配符的字符串l匹配规则：“%”：匹配零个或多个字符。“”：匹配任意单个字符。例
29、例2 查询所有姓刘学生的姓名、查询所有姓刘学生的姓名、 学号和性别。学号和性别。SELECT Sname，Sno，sex FROM S WHERE Sname LIKE 刘%； 例例3 查询姓查询姓“欧阳欧阳”且全名为且全名为 三个汉字的学生的姓名。三个汉字的学生的姓名。SELECT Sname FROM SWHERE Sname LIKE 欧阳_ _例例4 查询名字中第查询名字中第2个字个字为为“阳阳”字的学生的姓名和学号字的学生的姓名和学号SELECT Sname，SnoFROM SWHERE Sname LIKE _ _阳%；例例5 查询所有不姓刘的学生查询所有不姓刘的学生姓名。姓名。S
30、ELECT Sname，Sno，sexFROM SWHERE Sname NOT LIKE 刘%；4.4.2 单表查询单表查询-选择表中的若干元组选择表中的若干元组3) 使用换码字符将通配符转义为普通字符使用换码字符将通配符转义为普通字符 定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待。如看待。如escape “”，是定义了是定义了 作为转义字符，则可用作为转义字符，则可用%去匹配去匹配%，用，用去匹配，用去匹配，用 去匹配去匹配 。ESCAPE表示“”为换码字符 例例6 查询查询DB_Design课程的课程号和学分。课
31、程的课程号和学分。 SELECT Cno，credit FROM Course WHERE Cname LIKE DB Design ESCAPE ；例例7 查询以查询以DB_开头，且倒数第开头，且倒数第3个字符为个字符为 i的课程的详细情况。的课程的详细情况。 SELECT * FROM Course WHERE Cname LIKE DB %I ESCAPE ；4.4.2 单表查询单表查询-选择表中的若干元组选择表中的若干元组（5）.空值的处理空值的处理格式：格式：表达式ISNULL或表达式ISNOTNULL测试指定列的值是否为空值。n“IS” 不能用不能用 “=” 代替代替 例例1 某些
32、学生选修课程后没有参加考试，所以有选课记录，但没某些学生选修课程后没有参加考试，所以有选课记录，但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECT Sno，Cno FROM SC WHERE Grade IS NULL例例2 查所有有成绩的学生学号和课程号查所有有成绩的学生学号和课程号。SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；4.4.2 单表查询单表查询-选择表中的若干元组选择表中的若干元组注意事项注意事项l除isnotnull之外，空值不满足任何查找条件。l如果nul
33、l参与算术运算，则该算术表达式的值为null。l如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown。l如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null。 例：select sum(SAL) from PROF例：select count(*) from PROF4.4.2 单表查询单表查询-选择表中的若干元组选择表中的若干元组（6 6）. .多条件查询多条件查询逻辑运算符：逻辑运算符：ANDAND和和 OROR来联结多个查询条件来联结多个查询条件AND的优先级高于OR 可以用括号改变优先级可用来实现多种其他谓词可用来实现多种其他谓
34、词NOTINNOTBETWEENAND 例例1 1 查询计算机系年龄在查询计算机系年龄在2020岁以下的学生姓名。岁以下的学生姓名。 SELECT Sname FROM S WHERE dno= CS AND Sage20；4.4.2 单表查询单表查询-选择表中的若干元组选择表中的若干元组 例例2 2 查询信息系（查询信息系（ISIS）、）、数学系（数学系（MAMA）和计算机科和计算机科学系（学系（CSCS）学生的姓名和性别。学生的姓名和性别。可改写为：可改写为：SELECT Sname，sexFROM SWHERE dno= IS OR dno= MA OR dno= CS ；SELECT
35、Sname，sexFROM SWHERE dno IN ( IS，MA，CS )4.4.2单表查询查询仅涉及一个表：查询仅涉及一个表：n一、选择表中的若干列n二、From子句n三、选择表中的若干元组n四、ORDERBY子句n五、聚集函数n六、GROUPBY子句n七、更名运算4.4.2 单表查询单表查询- ORDER BYORDER BY子句子句 格式格式： order by 列名列名 asc | desclORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序l当排序列含空值时（一般来说）ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示不同
36、的不同的DBMS的规的规则有所不一样，如则有所不一样，如MS SQL SERVER的规则刚好相反的规则刚好相反。例例1 查询选修了查询选修了3 3号课程的学生的学号及其成绩，查询结果按分数降序排列号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC；4.4.2 单表查询单表查询- ORDER BYORDER BY子句子句例例2 查询全体学生情况，查询结果按所在系的查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。系号升序排列，同一系中的学生按年龄降序
37、排列。 SELECT * FROM S ORDER BY dno，Sage DESC；4.4.2单表查询查询仅涉及一个表：查询仅涉及一个表：n一、选择表中的若干列n二、From子句n三、选择表中的若干元组n四、ORDERBY子句n五、聚集函数n六、GROUPBY子句n七、更名运算4.4.2 单表查询单表查询-聚集函数聚集函数聚集函数：聚集函数：l计数计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）l计算总和计算总和SUM（DISTINCT|ALL）l计算平均值计算平均值AVG（DISTINCT|ALL）l最大最小值最大最小值 MAX（DISTINCT|ALL）
38、MIN（DISTINCT|ALL）4.4.2 单表查询单表查询-聚集函数聚集函数 例例1 1 查询学生总人数。查询学生总人数。SELECT COUNT(*) FROM S； 例例2 2 查询选修了课程的学生人数。查询选修了课程的学生人数。SELECT COUNT(DISTINCT Sno) FROM SC； 例例3 3 计算计算1 1号课程的学生平均成绩。号课程的学生平均成绩。SELECT AVG(Grade) FROM SC WHERE Cno= 1 ；4.4.2 单表查询单表查询-聚集函数聚集函数 例例4 4 查询选修查询选修1 1号课程的学生最高分数。号课程的学生最高分数。 SELECT
39、 MAX(Grade) FROM SC WHER Cno= 1 ； 例例5 5查询学生查询学生200215012200215012选修课程的总学分数。选修课程的总学分数。 SELECT SUM(credit) FROM SC， Course WHER Sno=200215012 AND SC.Cno=Course.Cno;4.4.2单表查询查询仅涉及一个表：查询仅涉及一个表：n一、选择表中的若干列n二、From子句n三、选择表中的若干元组n四、ORDERBY子句n五、聚集函数n六、GROUPBY子句n七、更名运算4.4.2 单表查询单表查询- GROUP BYGROUP BY子句子句 分组命令
40、分组命令group by 列名列名 having 条件表达式条件表达式 group by将表中的元组按指定一列或多列上的值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值。having则对分组进行选择，只将聚集函数作用到满足条件的分组上。 例例1 1 求各个课程号及相应的选课人数。求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；查询结果：CnoCOUNT(Sno)1222343444335484.4.2 单表查询单表查询- GROUP BYGROUP BY子句子句l未对查询结果分组，聚集函数将作用于整个查询结果l对查
41、询结果分组后，聚集函数将分别作用于每个组l作用对象是查询的中间结果表l按指定的一列或多列值分组，值相等的为一组lhaving 子句对 group by 子句所确定的行组进行控制,having 子句条件中只允许涉及常量,聚组函数或group by 子句中的列.4.4.2 单表查询单表查询- GROUP BYGROUP BY子句子句 例例2 2 列出所有老师的最高、最低、平均工资列出所有老师的最高、最低、平均工资。 select max(SAL)，min(SAL)，avg(SAL) from PROF 例例3 3 列出各系的老师的最高、最低、平均工资列出各系的老师的最高、最低、平均工资。 sele
42、ct DNO，max(SAL)，min(SAL)，avg(SAL) from PROF group by DNO4.4.2 单表查询单表查询- GROUP BYGROUP BY子句子句 例例4 4 查询选修了查询选修了3 3门以上课程的学生学号。门以上课程的学生学号。HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组HAVING短语作用于组，从中选择满足条件的组。 例例6 6 列出所有同学的及格列出所有同学的及格课程的平均成绩。课程的平均成绩。select SNO,avg(grade) from SC where grade =60 gr
43、oup by SNO 例例5 5 列出所有课程均及格列出所有课程均及格的学生的平均成绩。的学生的平均成绩。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3；select SNO，avg(SCORE) from SC group by SNO having min(SCORE) = 604.4.2 单表查询单表查询- GROUP BYGROUP BY子句子句l? 求选修了课程的学生人数。 select count (SNO ) from SC select PNAME，max(SAL) from PROF select DNO，avg(SAL)
44、 from PROF group by DNO where AGE 604.4.2单表查询查询仅涉及一个表：查询仅涉及一个表：n一、选择表中的若干列n二、From子句n三、选择表中的若干元组n四、ORDERBY子句n五、聚集函数n六、GROUPBY子句n七、更名运算4.4.2 单表查询单表查询- 更名运算更名运算格式格式old_name as new_name为关系和属性重新命名，可出现在select和from子句中。示例示例l属性更名例：给出所有老师的姓名、所纳税额及税后工资额。 select PNAME，SAL0.05 as taxi， SAL*0.95 as incomingfrom P
45、ROF4.4.2 单表查询单表查询- 更名运算更名运算l关系更名找出工资比所在系主任工资高的老师姓名及工资。 select P1.PNAME，P1.SALfrom PROF as P1，PROF as P2，DEPT where P1.DNO = DEPT.DNOand DEPT.DEAN = P2.PNO and P1.SAL P2.SAL 注：as可选。4.4.3 连接查询连接查询一、等值与非等值连接查询二、自身连接三、外连接四、复合条件连接4.4.3 连接查询连接查询连接查询：同时涉及多个表的查询。连接查询：同时涉及多个表的查询。连接条件或连接谓词：用来连接两个表的条件连接条件或连接谓词
46、：用来连接两个表的条件一般格式：一般格式： . 1 .2 . 1 BETWEENBETWEEN . 2 ANDAND .3连接字段：连接谓词中的列名称连接字段：连接谓词中的列名称v连连接接条条件件中中的的各各连连接接字字段段类类型型必必须须是是可可比比的的，但但名名字字不不必必是是相相同的同的4.4.3 连接查询连接查询学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121李勇男20CS200215122刘晨女19IS200215123王敏女18MA200215125张立男19IS课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息
47、系统144操作系统635数据结构746数据处理27PASCAL语言64学号Sno课程号Cno成绩Grade2002151211922002151212852002151213882002151222902002151223804.4.3 连接查询连接查询一、等值与非等值连接查询 二、自身连接三、外连接四、复合条件连接4.4.3 连接查询连接查询-等值与非等值连接查等值与非等值连接查询询 等值连接：等值连接：连接运算符为连接运算符为= = 例例1 1 查询每个学生及其选修课程的情况查询每个学生及其选修课程的情况 SELECTS.*，SC.*FROMS，SCWHERES.Sno=SC.Sno；S.
48、SnoSnameSsexSageSdeptSC.SnoCnoGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122刘晨女19CS200215122290200215122刘晨女19CS2002151223804.4.3 连接查询连接查询-等值与非等值连接查等值与非等值连接查询询自然连接：自然连接：例2对例1用自然连接完成。SELECTS.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMS，SCWHERES.Sno=SC.Sn
49、o；4.4.3 连接查询连接查询一、等值与非等值连接查询 二、自身连接三、外连接四、复合条件连接4.4.3 连接查询连接查询-自身连接自身连接自身连接：一个表与其自己进行连接l需要给表起别名以示区别l由于所有属性名都是同名属性，因此必须使用别名前缀例1查询每一门课的间接先修课（即先修课的先修课）查询每一门课的间接先修课（即先修课的先修课）SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.Cpno = SECOND.Cno；CnoCnameCpnoCcredit1数据库数据库543信息系统信息系统144操
50、作系统操作系统635数据结构数据结构746数据处理数据处理27PASCALPASCAL语言语言64CnoCnameCpnoCcredit1数据库数据库543信息系统信息系统144操作系统操作系统635数据结构数据结构746数据处理数据处理27PASCALPASCAL语言语言64FIRST表（Course表）FIRST表（Course表）SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.Cpno = SECOND.Cno；CnoPcno173556结果为结果为:4.4.3 连接查询连接查询-自身连接自身连
51、接4.4.3 连接查询连接查询一、等值与非等值连接查询二、自身连接三、外连接四、复合条件连接4.4.3 连接查询连接查询外连接外连接l例：列出老师的有关信息，包括姓名、工资、所教授的课程P#,PN,SAL,C#,CN(PROF)PCC)500李三P04600700800SAL孙立P03钱广P02赵明P01PNP#化学C03数学C02物理C01CNC#P04C02P02C02P01C01P#C#数学C02500李三P04数学C02700钱广P02物理C01800赵明P01CNC#SALPNP#问问题题：有有关关P03号号职职工工的的姓姓名名和和工工资资信信息息没没有显示出来有显示出来4.4.3
52、连接查询连接查询外连接外连接外连接外连接l为避免自然连接时因失配而发生的信息丢失，可以假定往参与连接的一方表中附加一个取值全为空值的行，它和参与连接的另一方表中的任何一个未匹配上的元组都能匹配，称之为外连接外连接=自然连接+失配的元组l外连接的形式：左外连接、右外连接、全外连接左外连接=自然连接+左侧表中失配的元组右外连接=自然连接+右侧表中失配的元组全外连接=自然连接+两侧表中失配的元组4.4.3 连接查询连接查询外连接外连接500李三P04600700800SAL孙立P03钱广P02赵明P01PNP#化学C03数学C02物理C01CNC#P04C02P02C02P01C01P#C#数学C0
53、2500李三P04数学C02700钱广P02物理C01800赵明P01CNC#SALPNP#所有老师的信息所有老师的信息4.4.3 连接查询连接查询外连接外连接500李三P04600700800SAL孙立P03钱广P02赵明P01PNP#化学C03数学C02物理C01CNC#P04C02P02C02P01C01P#C#数学C02500李三P04数学C02700钱广P02物理C01800赵明P01CNC#SALPNP#所有课程的信息所有课程的信息4.4.3 连接查询连接查询外连接外连接500李三P04600700800SAL孙立P03钱广P02赵明P01PNP#化学C03数学C02物理C01CN
54、C#P04C02P02C02P01C01P#C#数学C02500李三P04数学C02700钱广P02物理C01800赵明P01CNC#SALPNP#所有老师和所有老师和课程的信息课程的信息4.4.3 连接查询连接查询外连接外连接外连接与普通连接的区别外连接与普通连接的区别l普通连接操作只输出满足连接条件的元组l外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出连接类型连接类型连接条件连接条件inner joinleft outer joinright outer joinfull outer joinon 例例查询每个学生及其选修课程的情况查询每个学生及其选修课程的情况SEL
55、ECT Student.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno)4.4.3 连接查询连接查询复合条件连接复合条件连接复合条件连接：复合条件连接：WHEREWHERE子句中含多个连接条件子句中含多个连接条件例例1 查询选修查询选修2号课程且成绩在号课程且成绩在90分以上的所有学生分以上的所有学生SELECTS.Sno,SnameFROMS,SCWHERES.Sno=SC.SnoAND/*连接谓词*/SC.Cno=2ANDSC.Grade90；/*其他限定条件*
56、/4.4.3 连接查询连接查询复合条件连接复合条件连接例例2查询每个学生的学号、姓名、选修的课程名及成绩查询每个学生的学号、姓名、选修的课程名及成绩SELECTS.Sno，Sname，Cname，GradeFROMS，SC，Course/*多表连接多表连接*/WHERES.Sno=SC.SnoandSC.Cno=Course.Cno；4.4.4 嵌套查询嵌套查询 一、一、 带有带有ININ谓词的子查询谓词的子查询 二、二、 带有比较运算符的子查询带有比较运算符的子查询 三、三、 带有带有ANYANY（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询 四、四、 带有带有EXISTSE
57、XISTS谓词的子查询谓词的子查询4.4.4 嵌套查询嵌套查询嵌套查询概述嵌套查询概述l一个SELECT-FROM-WHERE语句称为一个查询块查询块l将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询嵌套查询。如下例如下例:SELECT Sname FROM S WHERE Sno IN （SELECT Sno FROM SC WHERE Cno= 2 ）；）；子查询子查询父查询父查询说明说明:子查询的限制子查询的限制不能使用不能使用ORDER BY子句子句层层嵌套方式反映了层层嵌套方式反映了 SQL语言的结构化语言的结构化有些嵌套查询可以用有些嵌套查
58、询可以用 连接运算替代连接运算替代4.4.4 嵌套查询嵌套查询嵌套查询求解方法嵌套查询求解方法不相关子查询：子查询的查询条件不依赖于父查询不相关子查询：子查询的查询条件不依赖于父查询由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。相关子查询：子查询的查询条件依赖于父查询相关子查询：子查询的查询条件依赖于父查询l首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表l然后再取外层表的下一个元组l重复这一过程，直至外层表全部检查完为止4.4.4 嵌套查询嵌套查询-带有带有ININ谓词
59、的子查询谓词的子查询 in 子查询子查询-不相关子查询不相关子查询表达式表达式 not in （子查询）子查询）判断表达式的值是否在子查询的结果中。例例1 1 ：查询与：查询与“刘晨刘晨”在同一个系学习的学生。在同一个系学习的学生。4.4.4 嵌套查询嵌套查询-带有带有ININ谓词的子查询谓词的子查询查询与查询与“刘晨刘晨”在同一个系学习的学生。在同一个系学习的学生。 确定“刘晨”所在系名 查找所有在IS系学习的学生SELECT Sdept FROM S WHERE Sname= 刘晨 ；结果为： CSSELECT Sno，Sname，Sdept FROM Student WHERE Sdep
60、t= CS ；SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘晨 )；4.4.4 嵌套查询嵌套查询-带有带有ININ谓词的子查询谓词的子查询也可以用自身连接完成也可以用自身连接完成 例例11查询要求查询要求： SELECT S1.Sno，S1.Sname，S1.Sdept FROM S S1，S S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 刘晨刘晨；4.4.4 嵌套查询嵌套查询-带有带有ININ谓词的子查询谓词的子查
61、询例例2 2 ：查询选修了课程名为查询选修了课程名为“信息系统信息系统”的学生学号和姓的学生学号和姓名。名。 SELECT Sno，Sname 最后在最后在Student关系中关系中 FROM S 取出取出Sno和和Sname WHERE Sno IN (SELECT Sno 然后在然后在SC关系中找出选关系中找出选 FROM SC 修了修了3号课程的学生学号号课程的学生学号 WHERE Cno IN (SELECT Cno 首先在首先在Course关系中找出关系中找出 FROM Course “信息系统信息系统”的课程号，为的课程号，为3号号 WHERE Cname= 信息系统信息系统 )
62、);4.4.4 嵌套查询嵌套查询-带有带有ININ谓词的子查询谓词的子查询也可以用连接查询实现也可以用连接查询实现 例例22 SELECT Sno，Sname FROM S，SC，Course WHERE S.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=信息系统信息系统；4.4.4 嵌套查询嵌套查询-带有比较运算符的子查询带有比较运算符的子查询当能确切知道内层查询返回单值时，可用比较运算符当能确切知道内层查询返回单值时，可用比较运算符（ ， =，=，!=!=或或），），子查询一定要跟在比子查询一定要跟在比较符之后较符之后。与与AN
63、YANY或或ALLALL谓词配合使用谓词配合使用假设一个学生只可能在一个系学习，并且必须属于一个系，在假设一个学生只可能在一个系学习，并且必须属于一个系，在查查询与询与“刘晨刘晨”在同一个系学习的学生时在同一个系学习的学生时可以可以用用 = 代替代替IN ：SELECT Sno，Sname，Sdept FROM S WHERE Sdept = (SELECT Sdept FROM S WHERE Sname= 刘晨刘晨)；4.4.4 嵌套查询嵌套查询-带有比较运算符的子查询带有比较运算符的子查询例例3 3 ：找出每个学生超过他选修课程平均成绩的课程号找出每个学生超过他选修课程平均成绩的课程号。
64、 SELECT Sno， Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);相关子查询相关子查询 v可能的执行过程：可能的执行过程： 1.从外层查询中取出SC的一个元组x，将元组x的Sno值传送给内层查询。2. 执行内层查询，得到值，用该值代替内层查询，得到外层查询：3.执行这个查询4.外层查询取出下一个元组重复做上述1至3步骤，直到外层的SC元组全部处理完毕。4.4.4 嵌套查询嵌套查询-带有带有ANYANY（SOMESOME）或）或ALLALL谓词的子查谓词的子查询询谓词语义谓词语义lANY
65、：任意一个值lALL：所有值l格式为：表达式表达式 比较运算符比较运算符 ANY|ALLANY|ALL （子查询子查询）需要配合使用比较运算符需要配合使用比较运算符 ANY ANY大于子查询结果中的某个值大于子查询结果中的某个值 ALL ALL大于子查询结果中的所有值大于子查询结果中的所有值 ANY ANY小于子查询结果中的某个值小于子查询结果中的某个值 ALL= ANY= ANY大于等于子查询结果中的某个值大于等于子查询结果中的某个值 = ALL= ALL大于等于子查询结果中的所有值大于等于子查询结果中的所有值= ANY= ANY小于等于子查询结果中的某个值小于等于子查询结果中的某个值 =
66、ALL= ALL小于等于子查询结果中的所有值小于等于子查询结果中的所有值= ANY= ANY等于子查询结果中的某个值等于子查询结果中的某个值 =ALL=ALL等于子查询结果中的所有值（通常没有实际意义）等于子查询结果中的所有值（通常没有实际意义）!=!=（或（或）ANYANY不等于子查询结果中的某个值不等于子查询结果中的某个值!=!=（或（或）ALLALL不等于子查询结果中的任何一个值不等于子查询结果中的任何一个值4.4.4 嵌套查询嵌套查询-带有带有ANYANY（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询例例4 ：查询其他系中比计算机科学某查询其他系中比计算机科学某一一学生
67、年龄小的学生姓名和学生年龄小的学生姓名和年龄。年龄。 SELECT Sname，Sage FROM S WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= CS ) AND Sdept CS ; /*父查询块中的条件父查询块中的条件 */执行过程：执行过程： 1.RDBMS执行此查询时，首先处理子查询，找出 CS系中所有学生的年龄，构成一个集合如(20，19) 2. 处理父查询，找所有不是CS系且年龄小于20 或 19的学生用聚集函数实现用聚集函数实现：SELECT Sname，Sage FROM S WHERE Sage (SELECT
68、 MAX(Sage) FROM S WHERE Sdept= CS ) AND Sdept CS ;4.4.4 嵌套查询嵌套查询-带有带有ANYANY（SOMESOME）或）或ALLALL谓词的子查谓词的子查询询例例5 ：查询其他系中比计算机科学系查询其他系中比计算机科学系所有所有学生年龄都小学生年龄都小的学生姓名及年龄。的学生姓名及年龄。方法一：用方法一：用ALLALL谓词谓词 SELECT Sname，Sage FROM S WHERE Sage ALL (SELECT Sage FROM S WHERE Sdept= CS ) AND Sdept CS ;方法二：用聚集函数方法二：用聚集
69、函数 SELECT Sname，Sage FROM Student WHERE Sage (SELECT MIN(Sage) FROM Student WHERE Sdept= CS ) AND Sdept CS ;4.4.4 嵌套查询嵌套查询-带有带有ANYANY（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询 ANY（或（或SOME），），ALL谓词与聚集函数、谓词与聚集函数、IN谓谓词的等价转换关系词的等价转换关系 。 = 或或!= =ANY IN - MAXMIN= MINALL - NOT IN MINMAX= MAX4.4.4 嵌套查询嵌套查询-带有带有EXISTSE
70、XISTS谓词的子查询谓词的子查询测试集合是否为空测试集合是否为空not exists （子查询子查询）1. EXISTS1. EXISTS谓词谓词n存在量词 n带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“truetrue”或逻辑假值“falsefalse”。若内层查询结果非空，则外层的WHERE子句返回真值若内层查询结果为空，则外层的WHERE子句返回假值n由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2. NOT EXISTS2. NOT EXISTS谓词谓词若内层查询结果非空，则外层的WHERE子句返
71、回假值若内层查询结果为空，则外层的WHERE子句返回真值4.4.4 嵌套查询嵌套查询-带有带有EXISTSEXISTS谓词的子查询谓词的子查询例例6 6：查询所有选修了查询所有选修了1 1号课程的学生姓名。号课程的学生姓名。SELECT Sname FROM S WHERE EXISTS (SELECT * FROM SC WHERE Sno=S.Sno AND Cno= 1 )；思路分析：思路分析：n本查询涉及本查询涉及S S和和SCSC关系关系n在在S S中依次取每个元组的中依次取每个元组的SnoSno值，用此值去检查值，用此值去检查SCSC关系关系n若若SCSC中存在这样的元组，其中存在
72、这样的元组，其SnoSno值等于此值等于此S.S.SnoSno值，并且值，并且其其CnoCno= = 1 1，则取此则取此S.S.SnameSname送入结果关系。送入结果关系。可以用连接运算实现可以用连接运算实现 SELECT Sname FROM S, SCWHERE S.Sno=SC.Sno AND SC.Cno= 1;4.4.4 嵌套查询嵌套查询-带有带有EXISTSEXISTS谓词的子查询谓词的子查询 SELECT Sname FROM S WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM S
73、C WHERE Sno= Student.Sno AND Cno= Course.Cno ） ）；例例7 7：查询选修了全部课程的学生姓名。查询选修了全部课程的学生姓名。任任意意课课程程，所所求求学学生生选之。选之。 不不存存在在任任何何一一门门课课程程，所求学生没有选之所求学生没有选之。4.4.4 嵌套查询嵌套查询-带有带有EXISTSEXISTS谓词的子查询谓词的子查询 select SNAME from S where not exists（select CNO from COURSE where exists（select * from SC where SC.CNO = COURSE
74、.CNO and SC.SNO = 001） and not exists（select * from SC where SC.CNO = COURSE.CNO and SC.SNO = S.SNO）例例7 7：列出至少选修了列出至少选修了001号学生选修的所有课程的学生名。号学生选修的所有课程的学生名。语义为语义为：任任意意课课程程，001号号学学生生选之，所求学生选之。选之，所求学生选之。 不不存存在在这这样样的的课课程程y y，学学生生001001选选修修了了y y，而而学生学生x x没有选。没有选。4.4.4 嵌套查询嵌套查询-集合基数的测试集合基数的测试测试集合是否存在重复元组测试集
75、合是否存在重复元组unique (子查询）子查询）如果子查询结果中没有重复元组，则返回true。例例8 8：找出所有只教授一门课程的老师姓名。找出所有只教授一门课程的老师姓名。 select PNAME from PROF where unique（select PNO from PC where PC.PNO = PROF.PNO)4.4.4 嵌套查询嵌套查询-集合基数的测试集合基数的测试例例9 9：找出至少选修了两门课程的学生姓名找出至少选修了两门课程的学生姓名。 select SNAME from S where not unique（select SNO from SC where S
76、C.SNO = S.SNO)4.4.5 集合查询集合查询集合操作的种类集合操作的种类l并操作： UNIONl交操作： INTERSECTl差操作： EXCEPT参参加加集集合合操操作作的的各各查查询询结结果果的的列列数数必必须须相相同同；对应项的数据类型也必须相同。对应项的数据类型也必须相同。4.4.5 集合查询集合查询例例1 1 查询计算机科学系的学生及年龄不大于查询计算机科学系的学生及年龄不大于1919岁的学生。岁的学生。方法一：方法一： SELECT * FROM S WHERE Sdept= CS UNION SELECT * FROM S WHERE Sage=19；nUNIONUN
77、ION：将多个查询结果合并起来时，系统自动去掉重复将多个查询结果合并起来时，系统自动去掉重复元组。元组。nUNION ALLUNION ALL：将多个查询结果合并起来时，保留重复元组将多个查询结果合并起来时，保留重复元组 方法二：方法二： SELECT DISTINCT * FROM S WHERE Sdept= CS OR Sage=19；4.4.5 集合查询集合查询例例2 查询计算机科学系的学生与年龄不大于查询计算机科学系的学生与年龄不大于1919岁的学生岁的学生的交集。的交集。SELECT *FROM SWHERE Sdept=CS INTERSECTSELECT *FROM SWHER
78、E Sage=19 实际上就是查询计算机科学系中实际上就是查询计算机科学系中年龄不大于年龄不大于1919岁的学生岁的学生SELECT * FROM S WHERE Sdept= CS AND Sage=19；4.4.5 集合查询集合查询例例3 3 查询计算机科学系的学生与年龄不大于查询计算机科学系的学生与年龄不大于1919岁的学岁的学生的差集。生的差集。 SELECT * FROM S WHERE Sdept=CS EXCEPT SELECT * FROM S WHERE Sage 19；派生关系（派生关系（）命令命令（子查询）（子查询） as 关系名（列名，列名，关系名（列名，列名，）SQL
79、-92中，允许在from子句中使用子查询表达式，这时可将该子查询的结果命名为一个临时关系临时关系加以引用。示例示例l找出平均成绩及格的学生。先求出每个学生的平均成绩，再从中找出及格的学生 select SNAME ， avg（SCORE） from S，SC where SC.SNO = S.SNO group by SC .SNO派生关系（派生关系（）select SNAME ， AVG_SCOREfrom (select SNAME ， avg（SCORE） from S，SC where SC.SNO = S.SNO group by SC .SNO) as result(SNAME ，
80、 AVG_SCORE )where AVG_SCORE = 60派生关系Vs视图？视图（视图（）定义视图定义视图create view view_name(列名列名，列名，列名 ) as （查询表达式）查询表达式）with check option视图的属性名缺省为子查询结果中的属性名，也可以显式指明。withcheckoption指明当对视图进行insert，update时，要检查是否满足视图定义中的条件。撤消视图撤消视图drop view view_name视图（视图（）示例示例create view COMPUTER_PROFas (select PNO , PNAME , SAL fr
81、om PROF，DEPT where PROF.PNO = DEPT.PNO and DEPT.DNAME = “计算机系”）create view DEPTSAL( DNO, LOW, HIGH, AVERAGE, TOTAL )as ( select DNO, min(SAL), max(SAL), avg(SAL), sum(SAL) from PROF group by DNO )视图（视图（）l给出计算机系工资超过800的老师姓名。select PNAMEfrom COMPUTER_PROFwhere SAL 800l给出计算机系老师的最低、最高、平均工资以及工资总额。select
82、LOW , HIGH , AVERAGE , TOTALfrom DEPTSAL , DEPTwhere DEPTSAL.DNO = DEPT.DNO and DEPT.DNAME =“计算机系”SQL的数据修改功能的数据修改功能插入插入删除删除修改修改视图更新视图更新插入操作（插入操作（）命令命令insert into 表名表名 （列名（列名，列名，列名values （值值 ，值，值）插入一条指定好值的元组insert into 表名表名 （列名（列名，列名，列名（子查询）子查询）插入子查询结果中的若干条元组示例示例linsert into PROF values ( P123, “王明”,
83、 35, D08, 498 )linsert into PROF (PNO, PNAME, DNO) values ( P123, “王明”, D08 )思考：SAL取何值？如何防止插入带有空值的元组？插入操作（插入操作（）l将平均成绩大于90的学生加入到EXCELLENT中。insert into EXCELLENT ( SNO, GRADE)select SNO , avg(SCORE)from SCgroup by (SNO)having avg(SCORE) 90l lFORBIDDEN(INFORMIX) insert into PROFselect *from PROF若支持，则完
84、成查询后，再执行修改操作不支持修改在子查询中出现的表删除操作（删除操作（）命令命令delete from表名where条件表达式从表中删除符合条件的元组，如果没有where语句，则删除所有元组。示例示例l清除所有选课记录delete from SCl删除王明老师所有的任课记录。delete from PCwhere PNO in (select PNO from PROF where PNAME = “王明”) 删除操作（删除操作（）l删除低于平均工资的老师记录。delete from PROFwhere SAL 2000 update PROF set SAL = SAL * 0.95 wh
85、ere SAL 0：取到主变量的值发生了截断，指示变量的值是截断前的字符串的实际长度。需要解决的几个问题（需要解决的几个问题（）指示变量的用法：声明与宿主变量的声明方式一样，在数据操纵语句中，在宿主变量和指示变量之间加(: )或关键字indicator。EXEC SQL BEGIN DECLARE SECTIONint prof_no;char prof_name30;int salary;short name_id;shortsal_id;EXEC SQL END DECLARE SECTIONEXEC SQL select PNAME , SAL into :prof_name : nam
86、e_id , :salary: sal_id from PROF where PNO = : prof_no ; 需要解决的几个问题（需要解决的几个问题（）SQL与主语言之间操作方式的协调与主语言之间操作方式的协调SQL：一次一集合。C语言：一次一记录。l游标：在查询结果的记录集合中移动的指针。若一个SQL语句返回单个元组，则不用游标。若一个SQL语句返回多个元组，则使用游标。l不需要游标的数据操作结果是一个元组的select语句 EXEC SQL select PNAME , SAL into :prof_name : name_id , :salary: sal_id from PROF
87、where PNO = : prof_no ;需要解决的几个问题（需要解决的几个问题（）insert语句 EXEC SQL insert into PROF values (:prof_no, :prof_name , :salary , :dept_no , : salary) ;delete语句 EXEC SQL delete from PROF values PNO :prof_no ;update语句 EXEC SQL update PROF set SAL = :salary where PNO = : prof_no ;需要解决的几个问题（需要解决的几个问题（）l需要游标的数据操作
88、当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组。活动集：活动集：selecT语句返回的元组的集合。当当前前行行：活动集中当前处理的那一行。游标即是指向当前行的指针。游标分类：滚动游标：游标的位置可以来回移动，可在活动集中取任意元组。非滚动游标：只能在活动集中顺序地取下一个元组。更新游标：数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁。需要解决的几个问题（需要解决的几个问题（）定义与使用游标的语句declare：定义一个游标，使之对应一个select语句。declare游标名scrollcursor forselect语句for upda
89、teof列表名forupdate任选项，表示该游标可用于对当前行的修改与删除。open：打开一个游标，执行游标对应的查询，结果集合为该游标的活动集。open游标名需要解决的几个问题（需要解决的几个问题（）fetch：在活动集中将游标移到特定的行，并取出该行数据放到相应的宿主变量中。fetch next | prior | first | last | current | relative n | absolute m游标名into 宿主变量表close：关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句。close游标名free：删除游标，以后便不能再对该游标执行open语
90、句了free游标名需要解决的几个问题（需要解决的几个问题（）SQL语句执行信息反馈语句执行信息反馈l良好的应用程序必须提供对错误的处理,应用程序需要知道SQL语句是否正确执行了，发生错误时的错误代码，执行时遇到特殊情况时的警告信息。lSQL通讯域SQLCA是一结构,每一嵌入SQL语句的执行情况在其执行完成后写入USERCA结构中的各变量中, 根据SQLCA中的内容可以获得每一嵌入SQL语句执行后的信息,应用程序就可以做相应的处理。l为了说明 (USERCA),必须在应用程序中包括:EXEC SQL INCLUDE SQLCA;EXEC SQL INCLUDE SQLCA;作业作业4.2 c，g， h，j
