第三章-SQL语言

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

2、919751979，IBM San 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数据库的体系结构数据库的体系结构 用户用户外模式外模式模式

3、模式内模式内模式SQL语言支持的关系数据库的三级模式结构语言支持的关系数据库的三级模式结构二、二、SQL数据库的体系结构数据库的体系结构 基本概念：基本概念：1、用户可以用、用户可以用SQL语言对视图语言对视图(View)和基本表和基本表(Base Table)进行查询等操作，在用户观点里，视图和表一样，进行查询等操作，在用户观点里，视图和表一样，都是关系。都是关系。2、视图视图是从一个或多个基本表中导出的表，本身不存是从一个或多个基本表中导出的表，本身不存储在数据库中，只有其定义，可以将其理解为一个虚表。储在数据库中，只有其定义，可以将其理解为一个虚表。3、基本表基本表是本身独立存在的表，每

4、个基本表对应一个是本身独立存在的表，每个基本表对应一个存储文件，一个表可以带若干索引，存储文件及索引组存储文件，一个表可以带若干索引，存储文件及索引组成了关系数据库的内模式。成了关系数据库的内模式。用户用户外模式外模式模式模式内模式内模式三、三、SQL的功能的功能n 数据定义（数据定义（DDLDDL）n定义、删除、修改关系模式（基本表）定义、删除、修改关系模式（基本表）n定义、删除视图（定义、删除视图（ViewView）n定义、删除索引（定义、删除索引（IndexIndex）n 数据操纵（数据操纵（DMLDML）n数据查询数据查询n数据增、删、改数据增、删、改n 数据控制（数据控制（DCLDC

5、L）n用户访问权限的授予、收回用户访问权限的授予、收回四、四、SQL的组成的组成(分类分类) DATABASEDATABASE TABLE TABLECREATECREATE VIEW VIEW INDEX INDEX SQL DDL SQL DDL：ALTERALTERDROPDROP SQL QUERY SQL QUERY：SELECT SELECT FROM FROM INSERT INSERT SQLSQL SQL DML SQL DML：UPDATE UPDATE DELETE DELETE GRANTGRANT SQL DCL SQL DCL：REVOKEREVOKEAUDITAU

6、DIT 嵌入式嵌入式SQLSQL：五、五、SQL的形式的形式n 交互式交互式SQLSQLn一般一般DBMSDBMS都提供联机交互工具都提供联机交互工具n用户可直接键入用户可直接键入SQLSQL命令对命令对 数据库进行操作数据库进行操作n由由DBMSDBMS来进行解释来进行解释n 嵌入式嵌入式SQLSQLn能将能将SQLSQL语句嵌入到高级语言（宿主语言）语句嵌入到高级语言（宿主语言）n使应用程序充分利用使应用程序充分利用SQLSQL访问数据库的能力、宿主访问数据库的能力、宿主 语言的过程处理能力语言的过程处理能力n一般需要预编译，将嵌入的一般需要预编译，将嵌入的SQLSQL语句转化为宿主语言语

7、句转化为宿主语言编译器能处理的语句编译器能处理的语句六、六、SQL的特点的特点 4. 4. 具有查询、操作、定义和控制四种语言一体化的特点。具有查询、操作、定义和控制四种语言一体化的特点。它只向用户提供一种语言，但该语言具有上述多种功能，它只向用户提供一种语言，但该语言具有上述多种功能，且每种操作只需一种操作符。且每种操作只需一种操作符。1.1. 高度非过程化的语言：高度非过程化的语言：用户只需提出用户只需提出“干什么干什么”，至于，至于“怎么干怎么干”由由DBMSDBMS解决；用户只需要早查询语句中提出需解决；用户只需要早查询语句中提出需 要什么，要什么，DBMSDBMS即可按路径存取，并把

8、结果返回给用户。即可按路径存取，并把结果返回给用户。2. 2. 面向集合的语言：面向集合的语言：每一个每一个SQLSQL的操作对象是一个或多个关的操作对象是一个或多个关 系，操作的结果也是一个关系。系，操作的结果也是一个关系。3.3.一种语法结构，两种使用方式：一种语法结构，两种使用方式：即可独立使用，又可嵌入到即可独立使用，又可嵌入到 宿主语言中使用，具有自主型和宿主型两种特点。宿主语言中使用，具有自主型和宿主型两种特点。五、五、SQL的特点的特点 5. 5. 语言简洁、易学易用：核心功能只有语言简洁、易学易用：核心功能只有9 9个动词，个动词，语法简单，语法简单， 接近英语。接近英语。 S

9、QLSQL功能功能 动词动词 数据库查询数据库查询 SELECTSELECT 数据定义数据定义 CREATECREATE，DROP DROP ，ALTER ALTER 数据操纵数据操纵 INSERTINSERT，UPDATEUPDATE，DELETEDELETE 数据控制数据控制 GRANTGRANT，REVOKEREVOKE(一一) 数据类型数据类型 1、字符型：、字符型：CHAR(n)CHAR(n)，VAR CHAR VAR CHAR 2、数字型：、数字型：INT, SMALLINT,REALINT, SMALLINT,REAL3、日期型：、日期型：DATE,TIMEDATE,TIME(二

10、二) 函数函数 1. 数字函数数字函数:ABS(X),SQRT(X),RAND(X),LOG(X),ABS(X),SQRT(X),RAND(X),LOG(X), 2. 字符函数：字符函数： LENGTH(X$),LOWER(X$),UPPER(X$),LENGTH(X$),LOWER(X$),UPPER(X$), SUBSTRING( SUBSTRING(expressionexpression, , startstart, , lengthlength) )3.3.分组函数：分组函数：COUNT(COUNT(),MAX(X),MIN(X),AVG(X),),MAX(X),MIN(X),AVG

11、(X), SUM(X) SUM(X)(三三) 运算符运算符1. 句法操作符句法操作符：& 2. 数值操作符数值操作符：+, 3. 逻辑操作符逻辑操作符：=,IN,ANY,ALL, IN,ANY,ALL, 4. 查询表达式操作符查询表达式操作符： UNION,UNION,5. 其它操作符其它操作符 ：, (+) ,n 基本结构基本结构Select A1, A2, ., AnFrom R1, R2, ., RmWhere P3.2 查询语句查询语句 数据查询是数据库应用的核心功能数据查询是数据库应用的核心功能 A1, A2, ., An( p(R1R2.Rm)3.2 查询语句查询语句 语句格式语句

12、格式 (P72) SELECT SELECT ALL|DISTINCT ALL|DISTINCT 表名表名.* *| |列名列名| |表达式表达式AS AS 新列名新列名INTO :INTO :主变量主变量1,:1,:主变量主变量22 FROM FROM 表名表名 表别名表别名, , WHERE WHERE 条件表达式条件表达式| |子查询子查询 GROUP BY GROUP BY 列名列名1, 1, HAVING HAVING 分组表达式分组表达式UNION|INTERSECT |EXCEPTSELECTUNION|INTERSECT |EXCEPTSELECTFROMFROM ORDER

13、BY ORDER BY 列名列名| |列序号列序号 ASC|DESC, ASC|DESC, ；SELECTSELECTFROMFROM常用语句执行过程常用语句执行过程 SELECTSELECT 投影投影 FROMFROM TABLE TABLE内存内存 WHEREWHERE 选取元组选取元组 GROUPGROUP 分组分组 HAVINGHAVING 选择分组选择分组 UNION|UNION| 查徇结果的集查徇结果的集合运算合运算 SELECTSELECT ORDER BY ORDER BY 排序输出排序输出A1, ., An( p(R1.Rm) Select A1, A2, ., An Fro

14、m R1, R2, ., Rm Where P一、单表查询一、单表查询 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；(SELECT (SELECT * *) )SELECT SnameSELECT

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

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

17、A and B 确定集合：确定集合：IN、NOT IN 字符匹配：字符匹配：LIKE，NOT LIKE 空值：空值：IS NULL、IS NOT NULL 多重条件：多重条件：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 S

18、A=22)；(WHERE SA NOT BETWEEN 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 决定哪些元组被选择参加

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

20、C#=C5ORDER BY GR DESCORDER BY GR DESC，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 门以上门以上且都及格的且都及格的学生号及总学生号及总平均分，并平均分，并按平均成绩按平均成绩排序。排序。A

21、1, ., An( p(R1.Rm) Select A1, A2, ., An From R1, R2, ., Rm Where P一、单表查询一、单表查询 1. 选择表中的列选择表中的列 2. 选择表中的行选择表中的行 3. 分组与组函数分组与组函数 4. 4. 排排 序序二、连接查询二、连接查询多表连接查询、单表连接查询、多表连接查询、单表连接查询、外连接查询、复合条件连接查询外连接查询、复合条件连接查询.三、嵌套查询三、嵌套查询一、单表查询一、单表查询返回单个值的子查询返回单个值的子查询返回一组值的子查询返回一组值的子查询多重子查询多重子查询二、连接查询二、连接查询多表连接查询、单表连接

22、查询、多表连接查询、单表连接查询、外连接查询、复合条件连接查询外连接查询、复合条件连接查询.* 示范表示范表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) 连接条件一：连接条件一：表名表名1. 列名列名1 比较运算符比较运算符 表名表名2.列名列名22) 连接条件二：连

23、接条件二：表名表名1.列名列名1 BETWEENBETWEEN 表名表名2.2.列名列名2 2 ANDAND 表名表名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 .

24、 . . . S 在表在表1中找到第一个元组，然后从头开始扫描表中找到第一个元组，然后从头开始扫描表2，查找到，查找到满足条件的元组即进行串接并存入结果表中；再继续扫描表满足条件的元组即进行串接并存入结果表中；再继续扫描表2，依次类推，直到表依次类推，直到表2末尾。再从表末尾。再从表1中取第二个元组，重复上述中取第二个元组，重复上述的操作，直到表的操作，直到表1中的元组全部处理完毕。中的元组全部处理完毕。SELECTSELECT ENAME, SAL, GRADE ENAME, SAL, GRADE FROMFROM EMP, SALGRADE EMP, SALGRADE WHEREWHERE

25、 SAL SAL BETWEENBETWEEN LOSAL LOSAL ANDAND HISAL HISAL；SELECTSELECT S.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、单表连接、单表连接 ： 用表别名把一个表定义为两个不同的表进行连接。用表别名把一个表定义为两个不同的表

26、进行连接。SELECTSELECT W W.ENAME.ENAME，W W.SAL.SAL，M M.ENAME.ENAME，M M.SAL.SALFROMFROM EMP EMP W W ，EMP EMP M MWHEREWHERE W W.MGR = .MGR = M M.EMPNO.EMPNO W W.SAL .SAL M M.SAL.SAL；例例1：求比本部门职工工资低的部门负责人求比本部门职工工资低的部门负责人SELECTSELECT FIRSTFIRST.C#.C#，THIRDTHIRD.CNAME.CNAMEFROMFROM C C FIRSTFIRST， C C SECOND S

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

28、对应的表中所有不满足条件的元组进行连接。外连接符件的元组进行连接。外连接符()或（或（+）出现在左侧称为右外连接、出现）出现在左侧称为右外连接、出现在右侧称为左连接、两侧都出现的称为全外连接。在右侧称为左连接、两侧都出现的称为全外连接。SELECTSELECT DNAME DNAME，ENAMEENAME，JOBJOBFROMFROM DEPT DEPT，EMPEMPWHEREWHERE DEPT.DEPTNO = EMP.DEPTNO( DEPT.DEPTNO = EMP.DEPTNO(* *) )； 例例1：求各部门名及职工名、工种，要求输出求各部门名及职工名、工种，要求输出 无职工的部门

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

30、PWHEREWHERE DEPT.DEPTNO DEPT.DEPTNO( (* *) )=EMP.DEPTNO=EMP.DEPTNO( (* *) )；例例3：求各部门名及职工名、工种，要求输出求各部门名及职工名、工种，要求输出 无职工的部门和未分配部门的职工无职工的部门和未分配部门的职工4、复合条件连接、复合条件连接 ： WHEREWHERE子句中除了连接条件，还有其它限制条件。子句中除了连接条件，还有其它限制条件。SELECTSELECT SN, GR SN, GRFROMFROM S, SC S, SCWHEREWHERE S.S#=SC.S# S.S#=SC.S# ANDAND SC.

31、C#=C6 SC.C#=C6 ANDAND SC.GR90 SC.GR90；例例1：求选修求选修C6C6课程且成绩超过课程且成绩超过9090分的学分的学生名生名 与成绩与成绩 连接条件连接条件限制条件限制条件三、三、嵌套查询嵌套查询 在在SELECT SELECT FROM FROM WHERE WHERE语句结构的语句结构的 WHEREWHERE子句中可嵌入一个子句中可嵌入一个SELECTSELECT语句块语句块 其上层其上层查询称为外层查询或父查询查询称为外层查询或父查询 其下层其下层查询称为内层查询或子查询查询称为内层查询或子查询 SQLSQL语言允许使用多重嵌套查询语言允许使用多重嵌套

32、查询 在在子查询中不允许使用子查询中不允许使用ORDER BYORDER BY子句子句 嵌套查询的实现一般是从里到外，即先进行嵌套查询的实现一般是从里到外，即先进行 子查询，再把其结果用于父查询作为条件子查询，再把其结果用于父查询作为条件1、返回单个值的子查询、返回单个值的子查询 ：方法一：方法一： SELECTSELECT SN, SA SN, SAFROMFROM S SWHEREWHERE SD SD ( ( SELECTSELECT SD SD FROMFROM S S WHEREWHERE SN = SN = 刘刘力力）；）；例例1：求与求与刘力刘力同一个系的学生名，年龄同一个系的学

33、生名，年龄1、返回单个值的子查询、返回单个值的子查询 ：方法二：方法二： SELECTSELECT FIRST.SN, FIRST.SA FIRST.SN, FIRST.SAFROMFROM S FIRST , S SECOND S FIRST , S SECONDWHEREWHERE FIRST.SD = SECOND.SD FIRST.SD = SECOND.SD ANDAND SECOND.SN = SECOND.SN = 刘力刘力；例例1：求与求与刘力刘力同一个系的学生名，年龄同一个系的学生名，年龄2、返回一组值的子查询、返回一组值的子查询：SELECTSELECT * *FROMFR

34、OM S SWHEREWHERE S# S# ININ ( ( SELECTSELECT S# S# FROM FROM SC SC WHEREWHERE C#=C6 C#=C6 ANDAND GR GR90 )90 )；例例1：求选修求选修C6C6课程且成绩超过课程且成绩超过9090分的学分的学生生方法一：方法一：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分

35、的学分的学生生方法二（方法二（ 连接查询连接查询 ）：）：方法一：方法一： SELECTSELECT * *FROMFROM S SWHEREWHERE SD!=CS SD!=CS ANDAND SA SA ANYANY ( ( SELECTSELECT SA SA FROMFROM S S WHEREWHERE SD=CS) SD=CS)；例例2：求比计算机系中某一学生年龄小的其他求比计算机系中某一学生年龄小的其他 系的学生系的学生方法二：方法二： SELECTSELECT * *FROMFROM S SWHEREWHERE SD!=CS SD!=CS ANDAND SA SA ( ( SE

36、LECTSELECT MAX(SA)MAX(SA) FROMFROM S S WHEREWHERE SD=CS) SD=CS)；例例2：求比计算机系中某一学生年龄小的其他求比计算机系中某一学生年龄小的其他 系的学生系的学生3、多重子查询多重子查询 ：例例1：求求10#部门中工种与部门中工种与SALES部门中任一部门中任一工工 种相同的职工种相同的职工SELECTSELECT ENAME ENAME，JOBJOB，SALSAL FROMFROM EMP EMP WHEREWHERE DEPTNO = 10 DEPTNO = 10 ANDAND JOB JOB ININ ( (SELECTSELE

37、CT JOB JOB FROMFROM EMP EMP WHEREWHERE DEPTNO DEPTNO （SELECTSELECT DEPTNO DEPTNO FROMFROM DEPT DEPT WHEREWHERE DNAME=SALES) DNAME=SALES)； SELECTSELECT * * FROMFROM EMP EMP WHEREWHERE SAL SAL ( (SELECTSELECT MIN(SAL) MIN(SAL) FROMFROM EMP EMP WHERE WHERE ENAME ENAME ININ (CLARK (CLARK，MILLER)MILLER)

38、ANDAND SAL SAL ( (SELECTSELECT MAX(SAL) MAX(SAL) FROMFROM EMP EMP WHEREWHERE ENAME ENAME ININ（CLARK,MILLER)CLARK,MILLER)； 例例2：求工资介于求工资介于CLARKCLARK与与MILLERMILLER两个之间的两个之间的职工职工 SELECTSELECT DEPTNO,ENAME,JOB,SAL DEPTNO,ENAME,JOB,SAL FROMFROM EMP EMP WHEREWHERE JOB JOB ININ ( (SELECTSELECT JOB JOB FROMF

39、ROM EMP EMP WHEREWHERE DEPTNO DEPTNO ININ ( (SELECTSELECT DEPTNO DEPTNO FROMFROM DEPT DEPT WHEREWHERE CITY CITY NEW YORK) NEW YORK)； 例例3：求工种与在纽约的职工的工种相同的职工求工种与在纽约的职工的工种相同的职工总 结连接查询连接查询多表连接查询、单表连接查询、多表连接查询、单表连接查询、外连接查询、复合条件连接查询外连接查询、复合条件连接查询嵌套查询嵌套查询返回单个值的子查询返回单个值的子查询返回一组值的子查询返回一组值的子查询多重子查询多重子查询四、带有四、

40、带有EXISTS的相关子查询的相关子查询 不相关子查询不相关子查询：子查询的查询条件不依赖于：子查询的查询条件不依赖于 父查询的称为不相关子查询。父查询的称为不相关子查询。 相关子查询相关子查询：子查询的查询条件依赖于外层父：子查询的查询条件依赖于外层父 查询的某个属性值的称为相关子查询查询的某个属性值的称为相关子查询 ( (Correlated SubqueryCorrelated Subquery),),带带EXISTSEXISTS 的子查询就的子查询就 是相关子查询是相关子查询 EXISTSEXISTS表示存在量词表示存在量词 带有带有EXISTSEXISTS的子查询不返回任何记录的数据

41、，的子查询不返回任何记录的数据， 只返回逻辑值只返回逻辑值 True True 或或 FalseFalse 不相关子查询：不相关子查询： SELECT SELECT SN SNFROMFROM S SWHEREWHERE S# S#ININ ( ( SELECTSELECT S# S# FROMFROM SC SC WHEREWHERE C# = C1 ) C# = C1 )； 例例1：求所有选修了求所有选修了C1C1课程的学生名。课程的学生名。相关子查询：相关子查询： SELECTSELECT SN SNFROMFROM S SWHEREWHERE EXISTSEXISTS ( ( SELE

42、CTSELECT * * 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 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 . .

43、. . S 先在外层查询中取先在外层查询中取S S表的第一个元组（记录），用该记录的表的第一个元组（记录），用该记录的相关的属性值（在内层相关的属性值（在内层WHEREWHERE子句中给定的）处理内层查询，若子句中给定的）处理内层查询，若外层的外层的WHEREWHERE子句返回子句返回TRUETRUE值，则此元组送入结果的表中。值，则此元组送入结果的表中。然然后再取下一个元组；重复上述过程直到外层表的记录全部遍历后再取下一个元组；重复上述过程直到外层表的记录全部遍历一次为止。一次为止。 说明：说明： n 不关心子查询的具体内容，因此用不关心子查询的具体内容，因此用 SELECTSELECT *

44、 *n ExistsExists + + 子查询用来判断该子查询是否返回元组子查询用来判断该子查询是否返回元组n 当子查询的结果集非空时，当子查询的结果集非空时，Exists Exists 为为 TrueTruen 当子查询的结果集为空时，当子查询的结果集为空时，Exists Exists 为为 FalseFalse n NOT EXISTSNOT EXISTS ：若子查询结果为空，返回：若子查询结果为空，返回TRUETRUE值，值， 否则返回否则返回 FALSEFALSESELECTSELECT SN SNFROMFROM S SWHEREWHERE NOT EXISTSNOT EXISTS

45、( ( 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( ( SELECTSELECT * * FROMFROM C C WHEREWHERE NOT EXISTSNOT EXISTS ( ( SELECTSELECT * * FROMFROM SC SC WHEREWH

46、ERE 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选修了S1所选的全部课程(PQ)= P QX(PQ)= (X ( (PQ) = (X ( ( P Q) = (X (P Q)即： X(PQ)= (X (P Q)SELECTSELECT SN

47、SNFROMFROM S SW H E R EW H E R E N O T E X I S T SN O T E X I S T S (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#ANDAND SCX.C#=SCY.C#) SCX.C#=SCY.C#)； Q

48、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 对应的类型必须一致对应的类型必须一致n 属性名无关属性名无关n 最终结果集采用第一个结果的属性名最终结

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

50、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 表名表名 WHERE

51、WHERE 条件条件 ； 只能对整个元组操作，不能只删除某些属性上只能对整个元组操作，不能只删除某些属性上 的值的值 只能对一个关系起作用，若要从多个关系中删只能对一个关系起作用，若要从多个关系中删 除元组，则必须对每个关系分别执行删除命令除元组，则必须对每个关系分别执行删除命令 从关系从关系 r 中删除满足中删除满足P的元组，只是删除数据，的元组，只是删除数据， 而不是定义而不是定义例例1：删除学号为删除学号为9501995019的学生。的学生。 1、删除单个元组、删除单个元组 ：二、删除操作二、删除操作DELETE FROMDELETE FROM SCSCWHEREWHERE S# = 9

52、5019; S# = 95019;DELETE FROMDELETE FROM S SWHEREWHERE S# = 95019; S# = 95019;例例2：删除选课但无成绩的学生的选课信息删除选课但无成绩的学生的选课信息2、删除多个元组、删除多个元组 ：二、删除操作二、删除操作DELETE FROMDELETE FROM SC SCWHEREWHERE GRADE GRADE IS NULLIS NULL; ;DELETE FROMDELETE FROM SC SC 清空清空SCSC表表例例3：删除选修删除选修C4C4且成绩小于该课程的平均成绩的记且成绩小于该课程的平均成绩的记录录3、带

53、子查询的删除语句：、带子查询的删除语句：二、删除操作二、删除操作 DELETE FROMDELETE FROM SC SC WHEREWHERE C#= C#=C4 C4 AND AND G G 80(ALL GRADE)80) )二、删除基本表二、删除基本表格式：格式： DROP TABLEDROP TABLE 表名表名 CASCADECASCADE| |RESTRICTRESTRICT ； 删除一个表，及与该表相关的索引、视图、码删除一个表，及与该表相关的索引、视图、码 和外部码。和外部码。 CASCADE CASCADE 连同引用该表的视图、完整连同引用该表的视图、完整 性约束一起自动撤

54、消性约束一起自动撤消 RESTRICTRESTRICT无引用时，才可撤消无引用时，才可撤消三、修改基本表三、修改基本表 改变表名改变表名 增加列增加列 改变列的数据类型改变列的数据类型 删除列的约束删除列的约束 删除列删除列 改变列名改变列名 基本表的修改操作：基本表的修改操作：三、修改基本表三、修改基本表格式：格式：ALTER TABLEALTER TABLE 表名表名 ADDADD 新列名新列名 类型类型( (长度长度)NOT NULLNOT NULL 列约束列约束, MODIFYMODIFY 旧列名旧列名 类型类型( (长度长度) ) DROPDROP 列约束列约束 RENAMERENA

55、ME 旧表名旧表名 TOTO 新表名新表名 增加新列，修改列，删除列的完整性约束；增加新列，修改列，删除列的完整性约束； 改变表名及列名改变表名及列名。 例例1 1：在在S S表中增加一个入学时间，为日期型表中增加一个入学时间，为日期型 ALETRALETR TABLETABLE S S ADDADD SCOME DATE SCOME DATE；例例2 2：把把SASA列的类型改为半字长整数列的类型改为半字长整数ALETR TABLEALETR TABLE S S MODIFYMODIFY SA SMALLINT SA SMALLINT；例例3 3：删除对删除对SNSN列的唯一约束列的唯一约束

56、 ALETR TABLEALETR TABLE S S DROPDROP UNIQUE(SN) UNIQUE(SN)；删除列、改变列名删除列、改变列名 对对删除列、改变列名删除列、改变列名的操作，可采取如下的操作，可采取如下 步骤实现：步骤实现：1)1)CREATE TABLECREATE TABLE ASAS ( (子查询子查询) )建一新表建一新表2)2)删除旧表删除旧表3)3)改变表名改变表名四、定义索引四、定义索引格式：格式： CREATECREATEUNIQUECLUSTER UNIQUECLUSTER INDEXINDEX 索引名索引名 ONON 表名表名 ( (列名列名 ASC|

57、DESC,ASC|DESC,) )； 对指定的表的列建立索引对指定的表的列建立索引. . UNIQUE UNIQUE 表示索引值唯一表示索引值唯一 CLUSTER CLUSTER 表示索引是聚簇索引表示索引是聚簇索引 索引一旦建立，交由系统使用和维护索引一旦建立，交由系统使用和维护 CREATECREATE UNIQUE UNIQUE INDEXINDEX SIDX SIDX ONON S(S# ASC) S(S# ASC)； CREATECREATE CLUSTER CLUSTER INDEXINDEX SNCDX SNCDXONON S(SNAME ASC) S(SNAME ASC)； C

58、REATE CREATE UNIQUE UNIQUE INDEXINDEX CIDX CIDX ONON C(C# ASC) C(C# ASC)； CREATECREATE UNIQUE UNIQUE INDEXINDEX SCIDX SCIDXONON SC(S# ASC,C# DESC) SC(S# ASC,C# DESC)；五、删除索引五、删除索引格式：格式： DROPDROP INDEXINDEX 索引名；索引名；例例： DROP INDEX CIDXDROP INDEX CIDX；3.5 数据控制数据控制一、授一、授 权权 GRANTGRANT二、收回权限二、收回权限 REVOKER

59、EVOKE一、授一、授 权权对象对象对象类型对象类型操作权限操作权限属性列TABLESELECT, INSERT, UPDATE, DELETE， ALL PRIVILEGES视图TABLESELECT, INSERT, UPDATE, DELETE ，ALL PRIVILEGES基本表TABLESELECT, INSERT, UPDATE, ALTER, INDEX,DELETE ALL PRIVILEGES1 1、DATABASEDATABASE： GRANT GRANT CREATETABCREATETAB ONON DATABASEDATABASE 数据库名数据库名 TOTO 用户名用

60、户名, ,| |PUBLICPUBLIC WITH GRANT OPTION WITH GRANT OPTION； 授予用户建立表的权限授予用户建立表的权限 由由DBADBA授权，只有授权，只有CREATETABCREATETAB一种权限一种权限 WITH GRANT OPTIONWITH GRANT OPTION允许用户转授权允许用户转授权( (权限传播权限传播) ) PUBLIC PUBLIC表示所有用户表示所有用户格式：格式：2 2、TABLETABLE级的基本表级的基本表GRANTGRANTSELECTSELECT| |INSERTINSERT| |UPDATEUPDATE| |DEL