数据库第三章.ppt

《数据库第三章.ppt》由会员分享，可在线阅读，更多相关《数据库第三章.ppt（215页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第三章第三章关系数据库标准语言关系数据库标准语言SQLSQL语言语言主要内容3.1SQL概述概述3.2查询语句查询语句3.3更新语句更新语句3.4SQLDDL3.5SQLDCL3.6视图视图3.7嵌入式嵌入式SQL 3.1SQL概述概述一、一、SQL的发展及现状的发展及现状二、二、SQL数据库的体系结构数据库的体系结构三、三、SQL的功能的功能四、四、SQL的形式的形式五、五、SQL的特点的特点3.1SQL概述概述一、一、SQL的发展及现状的发展及现状19741974年，由年，由BoyceBoyce和和ChamberlinChamberlin提出提出1975197919751979，IBM S

2、an Jose Research LabIBM San Jose Research Lab的关系的关系 数据库管理系统原型数据库管理系统原型System RSystem R实施了这种语言实施了这种语言SQL-86SQL-86是第一个是第一个SQLSQL标准标准SQL-89SQL-89、SQL-92(SQL2)SQL-92(SQL2)、SQL-99(SQL3)SQL-99(SQL3)大部分大部分DBMSDBMS产品都支持产品都支持SQLSQL，成为操作数据库的成为操作数据库的 标准语言标准语言二、二、SQL数据库的体系结构数据库的体系结构SQLSQL用户用户BaseTableBaseTableB

3、1B1ViewV1ViewV1ViewV2ViewV2BaseTableBaseTableB2B2BaseTableBaseTableB3B3BaseTableBaseTableB4B4StoredFileStoredFileS1S1StoredFileStoredFileS1S1StoredFileStoredFileS1S1StoredFileStoredFileS1S1外模式外模式模式模式内模式内模式SQL语言支持的关系数据库的三级模式结构语言支持的关系数据库的三级模式结构二、二、SQL数据库的体系结构数据库的体系结构基本概念：基本概念：1、用户可以用、用户可以用SQL语言对视图语言对视

4、图(View)和基本表和基本表(Base Table)进行查询等操作，在用户观点里，视图和表一样，进行查询等操作，在用户观点里，视图和表一样，都是关系。都是关系。2、视图视图是从一个或多个基本表中导出的表，本身不存是从一个或多个基本表中导出的表，本身不存储在数据库中，只有其定义，可以将其理解为一个虚表。储在数据库中，只有其定义，可以将其理解为一个虚表。3、基本表基本表是本身独立存在的表，每个基本表对应一个是本身独立存在的表，每个基本表对应一个存储文件，一个表可以带若干索引，存储文件及索引组存储文件，一个表可以带若干索引，存储文件及索引组成了关系数据库的内模式。成了关系数据库的内模式。SQLSQ

5、L用户用户BaseTableBaseTableB1B1ViewV1ViewV1ViewV2ViewV2BaseTableBaseTableB2B2BaseTableBaseTableB3B3BaseTableBaseTableB4B4StoredFileStoredFileS1S1StoredFileStoredFileS1S1StoredFileStoredFileS1S1StoredFileStoredFileS1S1外模式外模式模式模式内模式内模式三、三、SQL的功能的功能n 数据定义（数据定义（DDLDDL）n定义、删除、修改关系模式（基本表）定义、删除、修改关系模式（基本表）n定义、

6、删除视图（定义、删除视图（ViewView）n定义、删除索引（定义、删除索引（IndexIndex）n 数据操纵（数据操纵（DMLDML）n数据查询数据查询n数据增、删、改数据增、删、改n 数据控制（数据控制（DCLDCL）n用户访问权限的授予、收回用户访问权限的授予、收回四、四、SQL的组成的组成(分类分类)DATABASEDATABASE TABLE TABLECREATECREATE VIEW VIEW INDEX INDEX SQL DDL SQL DDL：ALTERALTERDROPDROP SQL QUERY SQL QUERY：SELECT FROM SELECT FROM IN

7、SERT INSERT SQLSQL SQL DML SQL DML：UPDATE UPDATE DELETE DELETE GRANTGRANT SQL DCL SQL DCL：REVOKEREVOKEAUDITAUDIT 嵌入式嵌入式SQLSQL：五、五、SQL的形式的形式n 交互式交互式SQLSQLn一般一般DBMSDBMS都提供联机交互工具都提供联机交互工具n用户可直接键入用户可直接键入SQLSQL命令对命令对 数据库进行操作数据库进行操作n由由DBMSDBMS来进行解释来进行解释n 嵌入式嵌入式SQLSQLn能将能将SQLSQL语句嵌入到高级语言（宿主语言）语句嵌入到高级语言（宿主语

8、言）n使应用程序充分利用使应用程序充分利用SQLSQL访问数据库的能力、宿主访问数据库的能力、宿主 语言的过程处理能力语言的过程处理能力n一般需要预编译，将嵌入的一般需要预编译，将嵌入的SQLSQL语句转化为宿主语言语句转化为宿主语言编译器能处理的语句编译器能处理的语句六、六、SQL的特点的特点4.4.具有查询、操作、定义和控制四种语言一体化的特点。具有查询、操作、定义和控制四种语言一体化的特点。它只向用户提供一种语言，但该语言具有上述多种功能，它只向用户提供一种语言，但该语言具有上述多种功能，且每种操作只需一种操作符。且每种操作只需一种操作符。1.1.高度非过程化的语言：高度非过程化的语言：

9、用户只需提出用户只需提出“干什么干什么”，至于至于“怎么干怎么干”由由DBMSDBMS解决；用户只需要早查询语句中提出需解决；用户只需要早查询语句中提出需 要什么，要什么，DBMSDBMS即可按路径存取，并把结果返回给用户。即可按路径存取，并把结果返回给用户。2.2.面向集合的语言：面向集合的语言：每一个每一个SQLSQL的操作对象是一个或多个关的操作对象是一个或多个关 系，操作的结果也是一个关系。系，操作的结果也是一个关系。3.3.一种语法结构，两种使用方式：一种语法结构，两种使用方式：即可独立使用，又可嵌入到即可独立使用，又可嵌入到 宿主语言中使用，具有自主型和宿主型两种特点。宿主语言中使

10、用，具有自主型和宿主型两种特点。五、五、SQL的特点的特点5 5.语言简洁、易学易用：核心功能只有语言简洁、易学易用：核心功能只有9 9个动词，个动词，语法简单，语法简单，接近英语。接近英语。SQLSQL功能功能 动词动词 数据库查询数据库查询 SELECTSELECT 数据定义数据定义 CREATECREATE，DROP DROP，ALTER ALTER 数据操纵数据操纵 INSERTINSERT，UPDATEUPDATE，DELETEDELETE 数据控制数据控制 GRANTGRANT，REVOKEREVOKE(一一)数据类型数据类型1、字符型：、字符型：CHAR(n)CHAR(n)，VA

11、R CHAR VAR CHAR 2、数字型：数字型：INT,SMALLINT,REALINT,SMALLINT,REAL3、日期型：日期型：DATE,TIMEDATE,TIME(二二)函数函数1.数字函数数字函数:2.ABS(X),SQRT(X),RAND(X),LOG(X),ABS(X),SQRT(X),RAND(X),LOG(X),2.字符函数：字符函数：3.LENGTH(X$),LOWER(X$),UPPER(X$),LENGTH(X$),LOWER(X$),UPPER(X$),4.4.SUBSTRING(SUBSTRING(expressionexpression,startstart

12、,lengthlength)3.3.分组函数：分组函数：COUNT(COUNT(),MAX(X),MIN(X),AVG(X),),MAX(X),MIN(X),AVG(X),4.4.SUM(X)SUM(X)(三三)运算符运算符1.句法操作符句法操作符：&2.数值操作符数值操作符：+,3.逻辑操作符逻辑操作符：=,IN,ANY,ALL,IN,ANY,ALL,4.查询表达式操作符查询表达式操作符：UNION,UNION,5.其它操作符其它操作符：,(+),n 基本结构基本结构SelectA1,A2,.,AnFromR1,R2,.,RmWhereP3.2查询语句查询语句数据查询是数据库应用的核心功能数

13、据查询是数据库应用的核心功能SelectSelectWhereWhereFromFrom A1,A2,.,An(p(R1R2.Rm)3.2查询语句查询语句语句格式语句格式(P72)SELECT SELECT ALL|DISTINCT ALL|DISTINCT 表名表名.*|.*|列名列名|表达式表达式 AS AS 新列名新列名 INTO:INTO:主变量主变量1,:1,:主变量主变量22FROM FROM 表名表名 表别名表别名,WHERE WHERE 条件表达式条件表达式|子查询子查询 GROUP BY GROUP BY 列名列名1,1,HAVING HAVING 分组表达式分组表达式UNI

14、ON|INTERSECT|EXCEPTSELECTFROMUNION|INTERSECT|EXCEPTSELECTFROMORDER BY ORDER BY 列名列名|列序号列序号 ASC|DESC,ASC|DESC,；SELECTFROMSELECTFROM常用语句执行过程常用语句执行过程SELECT SELECT 投影投影 FROM TABLEFROM TABLE内存内存 WHERE WHERE 选取元组选取元组 GROUP GROUP 分组分组 HAVING HAVING 选择分组选择分组 UNION|UNION|查徇结果的集查徇结果的集合运算合运算 SELECT SELECT ORDE

15、R BY ORDER BY 排序输出排序输出A1,.,An(p(R1.Rm)SelectA1,A2,.,AnFromR1,R2,.,RmWhereP一、单表查询一、单表查询1.选择表中的列选择表中的列2.选择表中的行选择表中的行 3.分组与组函数分组与组函数 4.4.排排 序序一、单表查询一、单表查询1.选择表中的列选择表中的列例例1.1.求学生所在系及姓名求学生所在系及姓名SELECT SD,SN SELECT SD,SN FROM SFROM S；例例2.求学生的全部信息求学生的全部信息 SELECT S#,SN,SA,SDSELECT S#,SN,SA,SD FROM SFROM S；(

16、SELECT*)(SELECT*)SELECT SELECT SnameSname NAME,Year of Birth:BIRTH,NAME,Year of Birth:BIRTH,2003-Sage BIRTHDAY,2003-Sage BIRTHDAY,LOWER(LOWER(SdeptSdept)DEPARTMENT)DEPARTMENT FROM Student;FROM Student;例例3.3.查全体学生的姓名、出生年份和所有系，要求用查全体学生的姓名、出生年份和所有系，要求用 小写字母表示所有系名小写字母表示所有系名 SELECTSELECT Sname Sname,Year

17、 of Birth:,Year of Birth:,2003-Sage,LOWER(2003-Sage,LOWER(SdeptSdept)FROM Student;FROM Student;2.选择表中的行选择表中的行3.1)消除重复行消除重复行:DISTINCT (DISTINCT (缺省为缺省为ALL)ALL)例例:求选修了课程的学生号求选修了课程的学生号SELECT DISTINCT S#SELECT DISTINCT S#FROM SFROM S；Where 子句子句运算符运算符比较：比较：、=、=、not+确定范围：确定范围：BetweenAandB、NotBetweenAandB确

18、定集合：确定集合：IN、NOTIN字符匹配：字符匹配：LIKE，NOTLIKE空值：空值：ISNULL、ISNOTNULL多重条件：多重条件：AND、OR、NOT用WHERE子句实现复杂查询条件例例:求年龄在求年龄在1822(含含18,22)之间的学生名及之间的学生名及年龄年龄（或不在（或不在1822之间之间)SELECT SN,SASELECT SN,SA FROM S FROM S WHERE SA BETWEEN 18 AND 22 WHERE SA BETWEEN 18 AND 22；(WHERE SA=18 AND SA=18 AND SA=22)；(WHERE SA NOT BET

19、WEEN 18 AND 22)(WHERE SA NOT BETWEEN 18 AND 22)；(WHERE SA22)(WHERE SA22)；例：例：求计算机系年龄小于求计算机系年龄小于19的姓名及年龄的姓名及年龄SELECT SN,SASELECT SN,SA FROM S FROM S WHERE SD=CS AND SA19 WHERE SD=CS AND SA5 HAVING COUNT(C#)5 AND MIN(GR)60 AND MIN(GR)60；Having 与与 Where的区别的区别nWhere 决定哪些元组被选择参加运算，作用于关系中的元组nHaving 决定哪些分组

20、符合要求，作用于分组4 4、排序、排序1)1)用用ORDER BYORDER BY子句对查询结果按照一个子句对查询结果按照一个或多个列的值进行升或多个列的值进行升/降排列输出降排列输出2)2)升序为升序为ASCASC；降序降序为为DESCDESC3)3)空值将作为最大值排序空值将作为最大值排序例例1 1：对选修对选修C5C5课程的学生按成绩降序课程的学生按成绩降序排列，同分数者按学号升序排列排列，同分数者按学号升序排列SELECT S#,GRSELECT S#,GRFROM SCFROM SCWHERE C#=C5WHERE C#=C5ORDER BY GR DESCORDER BY GR D

21、ESC，S#ASCS#ASC；例例2 2：求每个学生的总分并按总分降序排列求每个学生的总分并按总分降序排列,学号升序学号升序SELECT S#SELECT S#，SUM(ALL GR)SUM(ALL GR)FROM SCFROM SCGROUP BY S#GROUP BY S#ORDER BY 2 DESCORDER BY 2 DESC，S#ASCS#ASC；求求95级学生级学生中选修课程中选修课程在在5门以上门以上且都及格的且都及格的学生号及总学生号及总平均分，并平均分，并按平均成绩按平均成绩排序。排序。A1,.,An(p(R1.Rm)SelectA1,A2,.,AnFromR1,R2,.,

22、RmWhereP一、单表查询一、单表查询1.选择表中的列选择表中的列2.选择表中的行选择表中的行 3.分组与组函数分组与组函数 4.4.排排 序序主要内容主要内容主要内容主要内容二、连接查询二、连接查询多表连接查询、单表连接查询、多表连接查询、单表连接查询、外连接查询、复合条件连接查询外连接查询、复合条件连接查询.三、嵌套查询三、嵌套查询一、单表查询一、单表查询返回单个值的子查询返回单个值的子查询返回一组值的子查询返回一组值的子查询多重子查询多重子查询二、连接查询二、连接查询多表连接查询、单表连接查询、多表连接查询、单表连接查询、外连接查询、复合条件连接查询外连接查询、复合条件连接查询.*示范

23、表示范表EMP(EMP(EMPNOEMPNO,ENAME,MGR,JOB,SAL,DEPTNO,ENAME,MGR,JOB,SAL,DEPTNO)DEPT(DEPT(DEPTNODEPTNO,DNAME,CITY,DNAME,CITY)SALGRADE(SALGRADE(GRADEGRADE,LOSAL,HISAL,LOSAL,HISAL)1、多表连接：、多表连接：1)连接条件一：连接条件一：2)表名表名1.列名列名1比较运算符比较运算符表名表名2.列名列名22)连接条件二：连接条件二：3)表名表名1.列名列名1BETWEENBETWEEN 表名表名2.2.列名列名2 2 4)4)ANDAND

24、 表名表名2.2.列名列名3 3连接条件中的列名称为连接字段，其各连接条件中的列名称为连接字段，其各字段应是可比的。字段应是可比的。执行过程：执行过程：学号 课程号 学习成绩 S#C#GRADE S1 C1 A S1 C2 A S1 C3 A S1 C5 B S2 C1 B S2 C2 C .SC学号 学生姓名 所属系名 学生年龄 S#SNAME SD SA S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20.S在表在表1中找到第一个元组，然后从头开始扫描表中找到第一个元组，然后从头开始扫描表2，查找到，查找到满足条件的元组即进行串接并

25、存入结果表中；再继续扫描表满足条件的元组即进行串接并存入结果表中；再继续扫描表2，依次类推，直到表依次类推，直到表2末尾。再从表末尾。再从表1中取第二个元组，重复上述中取第二个元组，重复上述的操作，直到表的操作，直到表1中的元组全部处理完毕。中的元组全部处理完毕。例题例题例题例题SELECTSELECT ENAME,SAL,GRADE ENAME,SAL,GRADE FROMFROM EMP,SALGRADE EMP,SALGRADE WHEREWHERE SAL SAL BETWEENBETWEEN LOSAL LOSAL ANDAND HISAL HISAL；SELECTSELECT S.

26、S#S.S#，SNSN，CNCN，G GFROMFROM S S，C C，SCSCWHEREWHERE S.S#=SC.S#S.S#=SC.S#ANDAND C.C#=SC.C#C.C#=SC.C#；例例2：求每个职工的工资级别求每个职工的工资级别例例1：求选课情况，要求输出学号、姓名、课求选课情况，要求输出学号、姓名、课程名与成绩程名与成绩2、单表连接、单表连接：用表别名把一个表定义为两个不同的表进行连接。用表别名把一个表定义为两个不同的表进行连接。SELECTSELECT W W.ENAME.ENAME，W W.SAL.SAL，M M.ENAME.ENAME，M M.SAL.SALFROM

27、FROM EMP EMP W W ，EMP EMP M MWHEREWHERE W W.MGR=.MGR=M M.EMPNO.EMPNO ANDAND W W.SAL .SAL M M.SAL.SAL；例例1：求比本部门职工工资低的部门负责人求比本部门职工工资低的部门负责人例题例题例题例题SELECTSELECT FIRSTFIRST.C#.C#，THIRDTHIRD.CNAME.CNAMEFROMFROM C C FIRSTFIRST，C C SECOND SECOND,C,C THIRD THIRDWHEREWHERE FIRSTFIRST.PC#=.PC#=SECONDSECOND.C#

28、.C#ANDAND SECONDSECOND.PC#=.PC#=THIRDTHIRD.C#.C#例例2：求每门课的间接先修课名（即先修课的求每门课的间接先修课名（即先修课的先修课）先修课）3、外连接、外连接：在连接条件的某侧加上在连接条件的某侧加上()或或(+)，表示该侧所对应的表中可形成一个，表示该侧所对应的表中可形成一个各数据项均为空值的万能替代行，用来与另一侧对应的表中所有不满足条各数据项均为空值的万能替代行，用来与另一侧对应的表中所有不满足条件的元组进行连接。外连接符件的元组进行连接。外连接符()或（或（+）出现在左侧称为右外连接、出现）出现在左侧称为右外连接、出现在右侧称为左连接、两

29、侧都出现的称为全外连接。在右侧称为左连接、两侧都出现的称为全外连接。SELECTSELECT DNAME DNAME，ENAMEENAME，JOBJOBFROMFROM DEPT DEPT，EMPEMPWHEREWHERE DEPT.DEPTNO=EMP.DEPTNO(*)DEPT.DEPTNO=EMP.DEPTNO(*)；例例1：求各部门名及职工名、工种，要求输出求各部门名及职工名、工种，要求输出无职工的部门（职工以空值出现）无职工的部门（职工以空值出现）例题例题例题例题SELECTSELECT DNAME DNAME，ENAMEENAME，JOBJOBFROMFROM DEPT DEPT，

30、EMPEMPWHEREWHERE DEPT.DEPTNO DEPT.DEPTNO(*)(*)=EMP.DEPTNO=EMP.DEPTNO；例例2：求各部门名及职工名、工种，要求输出求各部门名及职工名、工种，要求输出未分配部门的职工（部门以空值出现）未分配部门的职工（部门以空值出现）例题例题例题例题SELECTSELECT DNAME DNAME，ENAMEENAME，JOBJOBFROMFROM DEPT DEPT，EMPEMPWHEREWHERE DEPT.DEPTNO DEPT.DEPTNO(*)(*)=EMP.DEPTNO=EMP.DEPTNO(*)(*)；例例3：求各部门名及职工名、工

31、种，要求输出求各部门名及职工名、工种，要求输出无职工的部门和未分配部门的职工无职工的部门和未分配部门的职工4、复合条件连接、复合条件连接：WHEREWHERE子句中除了连接条件，还有其它限制条件。子句中除了连接条件，还有其它限制条件。SELECTSELECT SN,GR SN,GRFROMFROM S,SC S,SCWHEREWHERE S.S#=SC.S#S.S#=SC.S#ANDAND SC.C#=C6 SC.C#=C6 ANDAND SC.GR90 SC.GR90；例例1：求选修求选修C6C6课程且成绩超过课程且成绩超过9090分的学生分的学生名名与成绩与成绩 连接条件连接条件限制条件限

32、制条件三、三、嵌套查询嵌套查询在在SELECT FROM WHERESELECT FROM WHERE语句结构的语句结构的 WHEREWHERE子句中可嵌入一个子句中可嵌入一个SELECTSELECT语句块语句块 其上层其上层查询称为外层查询或父查询查询称为外层查询或父查询 其下层其下层查询称为内层查询或子查询查询称为内层查询或子查询 SQLSQL语言允许使用多重嵌套查询语言允许使用多重嵌套查询 在在子查询中不允许使用子查询中不允许使用ORDER BYORDER BY子句子句嵌套查询的实现一般是从里到外，即先进行嵌套查询的实现一般是从里到外，即先进行子查询，再把其结果用于父查询作为条件子查询，

33、再把其结果用于父查询作为条件1、返回单个值的子查询、返回单个值的子查询：方法一：方法一：SELECTSELECT SN,SA SN,SAFROMFROM S SWHEREWHERE SD SD=(SELECTSELECT SD SD FROMFROM S S WHEREWHERE SN=SN=刘力刘力）；）；例例1：求与求与刘力刘力同一个系的学生名，年龄同一个系的学生名，年龄1、返回单个值的子查询、返回单个值的子查询：方法二：方法二：SELECTSELECT FIRST.SN,FIRST.SA FIRST.SN,FIRST.SAFROMFROM S FIRST,S SECOND S FIRST

34、,S SECONDWHEREWHERE FIRST.SD=SECOND.SD FIRST.SD=SECOND.SD ANDAND SECOND.SN=SECOND.SN=刘力刘力；例例1：求与求与刘力刘力同一个系的学生名，年龄同一个系的学生名，年龄2、返回一组值的子查询、返回一组值的子查询：SELECTSELECT *FROMFROM S SWHEREWHERE S#S#ININ (SELECTSELECT S#S#FROM FROM SC SC WHEREWHERE C#=C6 C#=C6 ANDAND GR GR90)90)；例例1：求选修求选修C6C6课程且成绩超过课程且成绩超过9090

35、分的学生分的学生方法一：方法一：例题例题例题例题SELECTSELECT S.*S.*FROMFROM S S，SCSCWHEREWHERE S.S#=SC.S#S.S#=SC.S#ANDAND GR GR90 90 ANDAND C#=C6 C#=C6；例例1：求选修求选修C6C6课程且成绩超过课程且成绩超过9090分的学分的学生生方法二（方法二（连接查询连接查询）：）：例题例题例题例题方法一：方法一：SELECTSELECT *FROMFROM S SWHEREWHERE SD!=CS SD!=CS ANDAND SA SA ANY ANY (SELECTSELECT SA SA FROM

36、FROM S S WHEREWHERE SD=CS)SD=CS)；例例2：求比计算机系中某一学生年龄小的其他求比计算机系中某一学生年龄小的其他系的学生系的学生例题例题例题例题方法二：方法二：SELECTSELECT *FROMFROM S SWHEREWHERE SD!=CS SD!=CS ANDAND SA SA (SELECTSELECT MAX(SA)MAX(SA)FROMFROM S S WHEREWHERE SD=CS)SD=CS)；例例2：求比计算机系中某一学生年龄小的其他求比计算机系中某一学生年龄小的其他系的学生系的学生3、多重子查询多重子查询：例例1：求求10#部门中工种与部门

37、中工种与SALES部门中任一部门中任一工工种相同的职工种相同的职工例题例题例题例题SELECTSELECT ENAME ENAME，JOBJOB，SALSAL FROMFROM EMP EMP WHEREWHERE DEPTNO=10 DEPTNO=10 ANDAND JOB JOB ININ (SELECTSELECT JOB JOB FROMFROM EMP EMP WHEREWHERE DEPTNO DEPTNO=（SELECTSELECT DEPTNO DEPTNO FROMFROM DEPT DEPT WHEREWHERE DNAME=SALES)DNAME=SALES)；例题例题例

38、题例题SELECTSELECT *FROMFROM EMP EMP WHEREWHERE SAL SAL=(SELECTSELECT MIN(SAL)MIN(SAL)FROMFROM EMP EMP WHERE WHERE ENAME ENAME ININ(CLARK(CLARK，MILLER)MILLER)ANDAND SAL SAL=(SELECTSELECT MAX(SAL)MAX(SAL)FROMFROM EMP EMP WHEREWHERE ENAME ENAME ININ（CLARK,MILLER)CLARK,MILLER)；例例2：求工资介于求工资介于CLARKCLARK与与MI

39、LLERMILLER两个之间的两个之间的职工职工例题例题例题例题SELECTSELECT DEPTNO,ENAME,JOB,SAL DEPTNO,ENAME,JOB,SAL FROMFROM EMP EMP WHEREWHERE JOB JOB ININ (SELECTSELECT JOB JOB FROMFROM EMP EMP WHEREWHERE DEPTNO DEPTNO ININ (SELECTSELECT DEPTNO DEPTNO FROMFROM DEPT DEPT WHEREWHERE CITY CITY=NEW YORK)NEW YORK)；例例3：求工种与在纽约的职工的工

40、种相同的职工求工种与在纽约的职工的工种相同的职工总 结连接查询连接查询多表连接查询、单表连接查询、多表连接查询、单表连接查询、外连接查询、复合条件连接查询外连接查询、复合条件连接查询嵌套查询嵌套查询返回单个值的子查询返回单个值的子查询返回一组值的子查询返回一组值的子查询多重子查询多重子查询四、带有四、带有EXISTS的相关子查询的相关子查询不相关子查询不相关子查询：子查询的查询条件不依赖于：子查询的查询条件不依赖于父查询的称为不相关子查询。父查询的称为不相关子查询。相关子查询相关子查询：子查询的查询条件依赖于外层父：子查询的查询条件依赖于外层父查询的某个属性值的称为相关子查询查询的某个属性值的

41、称为相关子查询(CorrelatedCorrelated Subquery Subquery),),带带EXISTSEXISTS的子查询就的子查询就是相关子查询是相关子查询EXISTSEXISTS表示存在量词表示存在量词 带有带有EXISTSEXISTS的子查询不返回任何记录的数据，的子查询不返回任何记录的数据，只返回逻辑值只返回逻辑值 True True 或或 FalseFalse 例题例题例题例题不相关子查询：不相关子查询：SELECT SELECT SN SNFROMFROM S SWHEREWHERE S#S#ININ (SELECTSELECT S#S#FROMFROM SC SC

42、WHEREWHERE C#=C1)C#=C1)；例例1：求所有选修了求所有选修了C1C1课程的学生名。课程的学生名。例题例题例题例题相关子查询：相关子查询：SELECTSELECT SN SNFROMFROM S SWHEREWHERE EXISTSEXISTS (SELECTSELECT *FROMFROM SC SC WHEREWHERE S.S#=SC.S#S.S#=SC.S#ANDAND C#=C1)C#=C1)；例例1：求所有选修了求所有选修了C1C1课程的学生名。课程的学生名。执行过程：执行过程：学号 课程号 学习成绩 S#C#GRADE S1 C1 A S1 C2 A S1 C3

43、 A S1 C5 B S2 C1 B S2 C2 C .SC学号 学生姓名 所属系名 学生年龄 S#SNAME SD SA S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20.S 先在外层查询中取先在外层查询中取S S表的第一个元组（记录），用该记录的表的第一个元组（记录），用该记录的相关的属性值（在内层相关的属性值（在内层WHEREWHERE子句中给定的）处理内层查询，若子句中给定的）处理内层查询，若外层的外层的WHEREWHERE子句返回子句返回TRUETRUE值，则此元组送入结果的表中。值，则此元组送入结果的表中。然然后再取下一个

44、元组；重复上述过程直到外层表的记录全部遍历后再取下一个元组；重复上述过程直到外层表的记录全部遍历一次为止。一次为止。说明：说明：n 不关心子查询的具体内容，因此用不关心子查询的具体内容，因此用 SELECTSELECT*n ExistsExists+子查询用来判断该子查询是否返回元组子查询用来判断该子查询是否返回元组n 当子查询的结果集非空时，当子查询的结果集非空时，Exists Exists 为为 TrueTruen 当子查询的结果集为空时，当子查询的结果集为空时，Exists Exists 为为 FalseFalse n NOT EXISTSNOT EXISTS ：若子查询结果为空，返回若

45、子查询结果为空，返回TRUETRUE值，值，否则返回否则返回 FALSEFALSE例题例题例题例题SELECTSELECT SN SNFROMFROM S SWHEREWHERE NOT EXISTSNOT EXISTS(SELECTSELECT*FROMFROM C C WHEREWHERE S.S#=SC.S#S.S#=SC.S#ANDAND C#=C1)C#=C1)；例例2 2：列出没有选列出没有选C1C1课程的学生的学号、姓名课程的学生的学号、姓名例题例题例题例题SELECTSELECT SN SNFROMFROM S SWHEREWHERE NOT EXISTSNOT EXISTS(

46、SELECTSELECT*FROMFROM C C WHEREWHERE NOT EXISTSNOT EXISTS (SELECTSELECT*FROMFROM SC SC WHEREWHERE S.S#=SC.S#S.S#=SC.S#ANDAND C.C#=SC.C#)C.C#=SC.C#)；例例3：查询选修了所有课程的学生的姓名查询选修了所有课程的学生的姓名这门课他没选这门课他没选这样的课是不存在的这样的课是不存在的例题例题例题例题例例4：查询至少选修了查询至少选修了S1所所选的全部课程的学生选的全部课程的学生名名P:S1选修了课程CXQ:学生SX选修了课程CX则:CX(PQ)表示学生SX

47、选修了S1所选的全部课程(PQ)=PQX(PQ)=(X(PQ)=(X(P Q)=(X(P Q)即：X(PQ)=(X(P Q)SELECTSELECT SN SNFROMFROM S SWHEREWHERE NOT EXISTSNOT EXISTS (X(SELECTSELECT*FROMFROM SC SCX SC SCXS1S1选课选课 WHEREWHERE SCX.S#=S1SCX.S#=S1 P PANDAND NOT EXISTSNOT EXISTS(SELECTSELECT*FROMFROM SC SCSCYSCY WHEREWHERE S.S#=SCY.S#S.S#=SCY.S#A

48、NDAND SCX.C#=SCY.C#)SCX.C#=SCY.C#)；Q QFROM语句中使用子查询语句中使用子查询(SQL2)在FROM语句中使用子查询，对查询结果定义表名及列名例：求平均成绩超过80分的学号及平均成绩SELECTS#，AVG_GFROM(SELECTS#,AVG(ALL G)FROMSC GROUPBYS#)ASRA(S#,AVG_G)WHEREAVG_G80;SELECTS#,AVG(ALL G)FROMSCGROUP BYS#HAVINGAVG(ALL G)80五、五、SQL的集合操作的集合操作n 属性个数必须一致属性个数必须一致n 对应的类型必须一致对应的类型必须一致

49、n 属性名无关属性名无关n 最终结果集采用第一个结果的属性名最终结果集采用第一个结果的属性名n 缺省为自动去除重复元组缺省为自动去除重复元组n 除非显式说明除非显式说明ALLn OrderBy放在整个语句的最后放在整个语句的最后SQL的集合操作的集合操作并并例例1 1：查询计算机系的学生：查询计算机系的学生或者或者年龄不大于年龄不大于1919岁的学生，岁的学生，并按年龄倒排序。并按年龄倒排序。SELECTSELECT *FROMFROM S SWHEREWHERE SD=CS SD=CSUNIONUNIONSELECTSELECT *FROMFROM S SWHEREWHERE AGE=19

50、AGE=19ORDER BYORDER BY AGE DESC AGE DESCSQL的集合操作的集合操作交交例例2 2：查询计算机系的学生：查询计算机系的学生并且并且年龄不大于年龄不大于1919岁的学生，岁的学生，并按年龄倒排序。并按年龄倒排序。(SELECTSELECT *FROM FROM S S WHERE WHERE SD=CS SD=CS)INTERSECINTERSEC(SELECTSELECT *FROM FROM S S WHERE WHEREAGE=19AGE80(ALL GRADE)80)；二、删除操作二、删除操作格式：格式：DELETE FROMDELETE FROM