数据库课件关系数据库标准语言SQL语言

资源描述

《数据库课件关系数据库标准语言SQL语言》由会员分享，可在线阅读，更多相关《数据库课件关系数据库标准语言SQL语言（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 I

7、NSERT 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

11、)CHAR(n)，VAR 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(expressionexpres

12、sion, , startstart, , 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.

13、2查询语句查询语句数据查询是数据库应用的核心功能数据查询是数据库应用的核心功能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

15、ON| UNION| 查徇结果的集查徇结果的集合运算合运算 SELECT SELECT ORDER 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.求学生的全部信息求学生的

16、全部信息 SELECT S#,SN,SA,SDSELECT S#,SN,SA,SD FROM SFROM S；(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.查全体学生的姓名、出生年份和所有系，要求用查全

17、体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名小写字母表示所有系名 SELECTSELECT Sname Sname , Year 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 DISTI

18、NCT S#FROM SFROM S；Where 子句子句运算符运算符比较：比较：、=、=、not+确定范围：确定范围：BetweenAandB、NotBetweenAandB确定集合：确定集合：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 BETWE

19、EN 18 AND 22 WHERE SA BETWEEN 18 AND 22；(WHERE SA=18 AND SA=18 AND SA=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

20、AND MIN(GR)60 AND MIN(GR)60；Having 与与 Where的区别的区别nWhere 决定哪些元组被选择参加运算，作用于关系中的元组nHaving 决定哪些分组符合要求，作用于分组4 4、排序、排序1)1)用用ORDER BYORDER BY子句对查询结果按照一个子句对查询结果按照一个或多个列的值进行升或多个列的值进行升/ /降排列输出降排列输出2)2)升序为升序为ASCASC；降序降序为为DESCDESC3)3)空值将作为最大值排序空值将作为最大值排序例例1 1：对选修对选修C5C5课程的学生按成绩降序课程的学生按成绩降序排列，同分数者按学号升序排列排列，同分数者按

21、学号升序排列SELECT S#, GRSELECT S#, GRFROM SCFROM SCWHERE C#=C5WHERE 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级学生级学生中选修课程中

22、选修课程在在5门以上门以上且都及格的且都及格的学生号及总学生号及总平均分，并平均分，并按平均成绩按平均成绩排序。排序。A1, ., An( p(R1.Rm)SelectA1,A2,.,AnFromR1,R2,.,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)

24、连接条件一：连接条件一：2)表名表名1.列名列名1比较运算符比较运算符表名表名2.列名列名22)连接条件二：连接条件二：3)表名表名1.列名列名1BETWEENBETWEEN 表名表名2.2.列名列名2 2 4)4) 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

25、SA S1 A CS 20 S2 B CS 21 S3 C MA 19 S4 D CI 19 S5 E MA 20 . . . . S在表在表1中找到第一个元组，然后从头开始扫描表中找到第一个元组，然后从头开始扫描表2，查找到，查找到满足条件的元组即进行串接并存入结果表中；再继续扫描表满足条件的元组即进行串接并存入结果表中；再继续扫描表2，依次类推，直到表依次类推，直到表2末尾。再从表末尾。再从表1中取第二个元组，重复上述中取第二个元组，重复上述的操作，直到表的操作，直到表1中的元组全部处理完毕。中的元组全部处理完毕。例题例题例题例题SELECTSELECT ENAME, SAL, GRADE

26、 ENAME, SAL, GRADE FROMFROM EMP, SALGRADE EMP, SALGRADE WHEREWHERE 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：求选课情况，要求输出学号、姓名、课求选课情况，要求输出学号、姓名

27、、课程名与成绩程名与成绩2、单表连接、单表连接：用表别名把一个表定义为两个不同的表进行连接。用表别名把一个表定义为两个不同的表进行连接。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 ANDAND W W.SAL .SAL M M.SAL.SAL；例例1：求比本部门职工工资低的部门负责人求比本部门职工工资低的部门负责人例题例题例题例题SELECTSELEC

28、T FIRSTFIRST.C#.C#，THIRDTHIRD.CNAME.CNAMEFROMFROM C C FIRSTFIRST， C C SECOND SECOND , C, C THIRD THIRDWHEREWHERE FIRSTFIRST.PC# = .PC# = SECONDSECOND.C# .C# ANDAND SECONDSECOND.PC# = .PC# = THIRDTHIRD.C#.C#例例2：求每门课的间接先修课名（即先修课的求每门课的间接先修课名（即先修课的先修课）先修课）3、外连接、外连接：在连接条件的某侧加上在连接条件的某侧加上()或或(+)，表示该侧所对应的表中

29、可形成一个，表示该侧所对应的表中可形成一个各数据项均为空值的万能替代行，用来与另一侧对应的表中所有不满足条各数据项均为空值的万能替代行，用来与另一侧对应的表中所有不满足条件的元组进行连接。外连接符件的元组进行连接。外连接符()或（或（+）出现在左侧称为右外连接、出现）出现在左侧称为右外连接、出现在右侧称为左连接、两侧都出现的称为全外连接。在右侧称为左连接、两侧都出现的称为全外连接。SELECTSELECT DNAME DNAME，ENAMEENAME，JOBJOBFROMFROM DEPT DEPT，EMPEMPWHEREWHERE DEPT.DEPTNO = EMP.DEPTNO(*) DE

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

31、（部门以空值出现）例题例题例题例题SELECTSELECT DNAME DNAME，ENAMEENAME，JOBJOBFROMFROM DEPT DEPT，EMPEMPWHEREWHERE DEPT.DEPTNO DEPT.DEPTNO(*)(*)=EMP.DEPTNO=EMP.DEPTNO(*)(*)；例例3：求各部门名及职工名、工种，要求输出求各部门名及职工名、工种，要求输出无职工的部门和未分配部门的职工无职工的部门和未分配部门的职工4、复合条件连接、复合条件连接：WHEREWHERE子句中除了连接条件，还有其它限制条件。子句中除了连接条件，还有其它限制条件。SELECTSELECT SN

32、, 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分的学生分的学生名名与成绩与成绩连接条件连接条件限制条件限制条件三、三、嵌套查询嵌套查询在在SELECT FROM WHERESELECT FROM WHERE语句结构的语句结构的 WHEREWHERE子句中可嵌入一个子句中可嵌入一个SELECTSELECT语句块语句块其上层其上层查询称为外层查询或父查询查

33、询称为外层查询或父查询其下层其下层查询称为内层查询或子查询查询称为内层查询或子查询 SQLSQL语言允许使用多重嵌套查询语言允许使用多重嵌套查询在在子查询中不允许使用子查询中不允许使用ORDER BYORDER BY子句子句嵌套查询的实现一般是从里到外，即先进行嵌套查询的实现一般是从里到外，即先进行子查询，再把其结果用于父查询作为条件子查询，再把其结果用于父查询作为条件1、返回单个值的子查询、返回单个值的子查询：方法一：方法一： SELECTSELECT SN, SA SN, SAFROMFROM S SWHEREWHERE SD SD = = ( ( SELECTSELECT SD SD

34、 FROMFROM S S WHEREWHERE SN = SN = 刘力刘力）；）；例例1：求与求与刘力刘力同一个系的学生名，年龄同一个系的学生名，年龄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：求与求

35、与刘力刘力同一个系的学生名，年龄同一个系的学生名，年龄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分的学生分的学生方法一：方法一：例题例题例题例题SELECTSELECT S.* S.*FROMFROM S S，SCSCWHEREWHERE S.S#=SC.S# S.S

36、#=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 FROMFROM S S WHEREWHERE SD=CS) SD=CS)；例例2：求比计算机系中某一学生年龄小的其他求比计算机系中某一学生年龄小的其他系的学生系的

37、学生例题例题例题例题方法二：方法二：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#部门中工种与部门中工种与SALES部门中任一部门中任一工工种相同的职工种相同的职工例题例题例题例题SELECTSELECT ENAME ENAME，JOBJ

38、OB，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)；例题例题例题例题SELECTSELECT * * FROMFROM EMP EMP WHEREWHERE SAL SAL =

39、= = = ( (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与与MILLERMILLER两个之间的两个之间的职工职工例题例

40、题例题例题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：求工种与在纽约的职工的工种相同的职工求工种与在纽约的职工的

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

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

43、HERE 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 C

44、1 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 先在外层查询中取先在外层查询中取S S表的第一个元组（记录），用该记录的表的第一个元组（记录），用该记录的相关的属性值（在内层相关的属性值（在内层WHEREWHERE子句中给定的）处理内层查询，若子句中给定的）处理内层查询，若外层的外层的WHEREWHERE子句返回子句返回TRUETRUE值，则此元组

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

46、e n NOT EXISTSNOT EXISTS ：若子查询结果为空，返回若子查询结果为空，返回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

47、SN SNFROMFROM S SWHEREWHERE NOT EXISTSNOT EXISTS( ( 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：查询至少选修了查询至

48、少选修了S1所所选的全部课程的学生选的全部课程的学生名名P: S1选修了课程CXQ: 学生SX选修了课程CX则: CX(PQ)表示学生SX选修了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 NO

49、T 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 QFROM语句中使用子查询语句中使用子查询(SQL2)在FROM语句中使用子查询，对查询结果定义表名及列名例：求平均成绩超过80分的学号及平均成绩SELECTS#，AVG_GFROM(SELECTS#,AVG(ALL G) FROMSC GROUPBYS# ) ASRA(S#,AVG_G)WHEREAVG_G80;SELECTS

50、#,AVG(ALL G)FROMSCGROUP BYS#HAVINGAVG(ALL G)80五、五、SQL的集合操作的集合操作n 属性个数必须一致属性个数必须一致n 对应的类型必须一致对应的类型必须一致n 属性名无关属性名无关n 最终结果集采用第一个结果的属性名最终结果集采用第一个结果的属性名n 缺省为自动去除重复元组缺省为自动去除重复元组n 除非显式说明除非显式说明ALLn OrderBy放在整个语句的最后放在整个语句的最后SQL的集合操作的集合操作并并例例1 1：查询计算机系的学生：查询计算机系的学生或者或者年龄不大于年龄不大于1919岁的学生，岁的学生，并按年龄倒排序。并按年龄倒排序。

51、SELECTSELECT * *FROMFROM S SWHEREWHERE SD=CS SD=CSUNIONUNIONSELECTSELECT * *FROMFROM S SWHEREWHERE AGE=19 AGE=19ORDER BYORDER BY AGE DESC AGE DESCSQL的集合操作的集合操作交交例例2 2：查询计算机系的学生：查询计算机系的学生并且并且年龄不大于年龄不大于1919岁的学生，岁的学生，并按年龄倒排序。并按年龄倒排序。( (SELECTSELECT * * FROM FROM S S WHERE WHERE SD=CS SD=CS) ) INTERSEC

52、INTERSEC( (SELECTSELECT * * FROM FROM S S WHERE WHEREAGE=19AGE80(ALL GRADE)80) )；二、删除操作二、删除操作格式：格式： DELETE FROMDELETE FROM 表名表名 WHEREWHERE 条件条件；只能对整个元组操作，不能只删除某些属性上只能对整个元组操作，不能只删除某些属性上的值的值只能对一个关系起作用，若要从多个关系中删只能对一个关系起作用，若要从多个关系中删除元组，则必须对每个关系分别执行删除命令除元组，则必须对每个关系分别执行删除命令从关系从关系r 中删除满足中删除满足P的元组，只是删除数据，的

53、元组，只是删除数据，而不是定义而不是定义例例1：删除学号为删除学号为9501995019的学生。的学生。 1、删除单个元组、删除单个元组：二、删除操作二、删除操作DELETE FROMDELETE FROM SCSCWHEREWHERE S# = 95019; S# = 95019;DELETE FROMDELETE FROM S SWHEREWHERE S# = 95019; S# = 95019;例例2：删除选课但无成绩的学生的选课信息删除选课但无成绩的学生的选课信息2、删除多个元组、删除多个元组：二、删除操作二、删除操作DELETE FROMDELETE FROM SC SCWHEREW

54、HERE GRADE GRADE IS NULLIS NULL; ;DELETE FROMDELETE FROM SC SC 清空清空SCSC表表例例3：删除选修删除选修C4C4且成绩小于该课程的平均成绩的记且成绩小于该课程的平均成绩的记录录3、带子查询的删除语句：、带子查询的删除语句：二、删除操作二、删除操作 DELETE FROMDELETE FROM SC SC WHEREWHERE C#= C#=C4 C4 AND AND G G 80(ALL GRADE)80) )二、删除基本表二、删除基本表格式：格式： DROP TABLEDROP TABLE 表名表名 CASCADECASCAD

55、E| |RESTRICTRESTRICT ；删除一个表，及与该表相关的索引、视图、码删除一个表，及与该表相关的索引、视图、码和外部码。和外部码。 CASCADECASCADE 连同引用该表的视图、完整连同引用该表的视图、完整性约束一起自动撤消性约束一起自动撤消 RESTRICTRESTRICT无引用时，才可撤消无引用时，才可撤消三、修改基本表三、修改基本表改变表名改变表名增加列增加列改变列的数据类型改变列的数据类型删除列的约束删除列的约束删除列删除列改变列名改变列名基本表的修改操作：基本表的修改操作：三、修改基本表三、修改基本表格式：格式：ALTER TABLEALTER TABLE 表名表

56、名 ADDADD 新列名新列名类型类型( (长度长度)NOT NULLNOT NULL 列约束列约束, MODIFYMODIFY 旧列名旧列名类型类型( (长度长度) ) DROPDROP 列约束列约束 RENAMERENAME 旧表名旧表名 TOTO 新表名新表名增加新列，修改列，删除列的完整性约束；增加新列，修改列，删除列的完整性约束；改变表名及列名改变表名及列名。例题例题例题例题例例1 1：在在S S表中增加一个入学时间，为日期型表中增加一个入学时间，为日期型 ALETRALETR TABLETABLE S S ADDADD SCOME DATE SCOME DATE；例例2

57、2：把把SASA列的类型改为半字长整数列的类型改为半字长整数ALETR TABLEALETR TABLE S S MODIFYMODIFY SA SMALLINT SA SMALLINT；例例3 3：删除对删除对SNSN列的唯一约束列的唯一约束 ALETR TABLEALETR TABLE S S DROPDROP UNIQUE(SN) UNIQUE(SN)；删除列、改变列名删除列、改变列名对对删除列、改变列名删除列、改变列名的操作，可采取如下的操作，可采取如下步骤实现：步骤实现：1)1)CREATE TABLECREATE TABLE ASAS ( (子查询子查询) )建一新表建一新表2)2

58、)删除旧表删除旧表3)3)改变表名改变表名四、定义索引四、定义索引格式：格式： CREATECREATEUNIQUECLUSTER UNIQUECLUSTER INDEXINDEX 索引名索引名 ONON 表名表名 ( (列名列名 ASC|DESC,)ASC|DESC,)；对指定的表的列建立索引对指定的表的列建立索引. . UNIQUE UNIQUE 表示索引值唯一表示索引值唯一 CLUSTER CLUSTER 表示索引是聚簇索引表示索引是聚簇索引索引一旦建立，交由系统使用和维护索引一旦建立，交由系统使用和维护例题例题例题例题 CREATECREATE UNIQUE UNIQUE INDEX

59、INDEX SIDX SIDX ONON S(S# ASC) S(S# ASC)； CREATECREATE CLUSTER CLUSTER INDEXINDEX SNCDX SNCDXONON S(SNAME ASC) S(SNAME ASC)； CREATE 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)；五、删除索引五、

60、删除索引格式：格式： DROPDROP INDEXINDEX 索引名；索引名；例例： DROP INDEX CIDXDROP INDEX CIDX；3.5数据控制数据控制一、授一、授权权GRANTGRANT二、收回权限二、收回权限REVOKEREVOKE一、授一、授权权对象对象对象类型对象类型操作权限操作权限属性列TABLESELECT, INSERT, UPDATE, DELETE， ALL PRIVILEGES视图TABLESELECT, INSERT, UPDATE, DELETE ，ALL PRIVILEGES基本表TABLESELECT, INSERT, UPDATE, ALTER,

61、 INDEX,DELETE ALL PRIVILEGES数据库数据库数据库数据库DATABASEDATABASECREATETABCREATETAB1 1、DATABASEDATABASE： GRANT GRANT CREATETABCREATETAB ONON DATABASEDATABASE 数据库名数据库名 TOTO 用户名用户名, ,|PUBLICPUBLIC WITH GRANT OPTION WITH GRANT OPTION；授予用户建立表的权限授予用户建立表的权限由由DBADBA授权，只有授权，只有CREATETABCREATETAB一种权限一种权限 WITH GRANT

65、拥有该表或视图的用户授权格式：格式：例题例题例题例题例例1 1：GRANT GRANT CREATETABCREATETAB ON ON DATABASEDATABASE 教学系统教学系统 TOTO USER1 USER1；例例2 2：GRANTGRANT SELECTSELECT, ,INSERTINSERT ONON TABLETABLE SC SC TO TO USER1,USER2,USER5 USER1,USER2,USER5 WITH GARNT OPTION WITH GARNT OPTION；例例3 3：GRANTGRANT ALLALL PRIVILEGESPRIVILEGE

66、S ONON TABLETABLE SC SC TOTO USER3,USER4,USER7 USER3,USER4,USER7；例题例题例题例题例例4 4：把查询把查询StuStu表和修改学生学号的表和修改学生学号的权限授给用户权限授给用户U4U4GRANTGRANT UPDATEUPDATE( (SnoSno), ), SELECTSELECT ONON TABLETABLE StuStu TOTO U4; U4; 二、二、ORACLEORACLE授权机制授权机制在在ORACLE ORACLE 中对用户的授权分为中对用户的授权分为系统级、表级、视图级系统级、表级、视图级 1 1、系统级：

67、系统级：格式：格式：GRANT GRANT CONNECT,RESOURCE,DBACONNECT,RESOURCE,DBA TO TO 用户名用户名, ,| |PUBLICPUBLIC IDENTIFIED BY IDENTIFIED BY 口令，口令，；作用：作用：授予用户对授予用户对ORACLEORACLE系统的操作权限系统的操作权限CONNECTCONNECT：允许用户登录数据库，在获允许用户登录数据库，在获得表级或视图级授权后可作相应操作，得表级或视图级授权后可作相应操作，但不能建立表但不能建立表(CREATE TABLE)CREATE TABLE)RESOURCERESOURCE

68、：允许建表允许建表DBADBA：超级用户超级用户 2 2、表级：格式与标准、表级：格式与标准SQLSQL类似类似3 3、表级：格式与标准、表级：格式与标准SQLSQL类似类似例：例：GRANT CONNECT,RESOURCE TO XUNSGRANT CONNECT,RESOURCE TO XUNS； GRANT TO USER6 IDENTIFIED BY QWEGRANT TO USER6 IDENTIFIED BY QWE；三、收回授权三、收回授权由由DBADBA或授于或授于DBADBA权限的用户对其他用权限的用户对其他用户进行权限回收。户进行权限回收。1 1、格式：格式：REVOK

70、C；4 4、格式：格式： REVOKE (DBA,RESOURCEREVOKE (DBA,RESOURCE,CONNECT),CONNECT)FROM FROM 用户名用户名,| |PUBLICPUBLIC；例题例题例题例题例例1 1 把用户把用户U4U4修改学生学号的权限收回修改学生学号的权限收回REVOKEREVOKE UPDATEUPDATE( (SnoSno) ) ONON TABLETABLE StuStu FROMFROM U4; U4; 例例2 2 收回所有用户对表收回所有用户对表SCSC的查询权限的查询权限REVOKEREVOKE SELECTSELECT ONON TABLE

71、TABLE SC SC FROMFROM PUBLIC; PUBLIC; 例例3 3 把用户把用户U5U5对对SCSC表的表的INSERTINSERT权限收回权限收回REVOKEREVOKE INSERTINSERT ONON TABLETABLE SC SC FROMFROM U5; U5; 四、审计四、审计(AUDIT)以以ORACLEORACLE为例，分为系统级审计和用为例，分为系统级审计和用户级审计两种户级审计两种1 1、系统级审计：、系统级审计：由由DBADBA进行，可监测登进行，可监测登录要求、录要求、GRANTGRANT、REVOKEREVOKE及其他数据库级及其他数据库级的操作

72、的操作2 2、用户级审计：、用户级审计：由用户设置，主要针对由用户设置，主要针对用户自己创建的表或视图进行审计，监用户自己创建的表或视图进行审计，监测对表或视图的访问及其他操作。测对表或视图的访问及其他操作。3 3、设置审计：、设置审计：格式：格式：AUDIT AUDIT 操作列表操作列表 ONON 表名表名| |视图名视图名| |SYSTEMSYSTEM4 4、取消审计：取消审计：格式：格式：NOAUDIT NOAUDIT 操作列表操作列表 ONON 表名表名| |视图名视图名| |SYSTEMSYSTEM5 5、审计结果存放：审计结果存放：内容在数据字典表内容在数据字典表SYS.AUDIT

73、_TRAILSYS.AUDIT_TRAIL中；中；可用可用 SELECT * FROM SYS.AUDIT_TRAILSELECT * FROM SYS.AUDIT_TRAIL；查看。查看。3.6视图视图视视图图是是从从一一个个或或几几个个表表( (或或视视图图) )导导出出的一个特殊的表。的一个特殊的表。视图是一个虚表视图是一个虚表数据库中只存放视图的定义数据库中只存放视图的定义视图对应的数据仍存放在原来的表中视图对应的数据仍存放在原来的表中随随着着表表中中数数据据的的变变化化，视视图图的的数数据据随随之之改变。改变。对视图的查询与基本表一样对视图的查询与基本表一样对视图的更新将受到一定的限

74、制对视图的更新将受到一定的限制视图视图一、视图概念一、视图概念二、定义、删除视图二、定义、删除视图三、查询视图三、查询视图四、更新视图四、更新视图五、视图的作用五、视图的作用一、视图概念一、视图概念一、视图概念一、视图概念视视视视图图图图3.6 视图是一个虚表视图是一个虚表数据库中只存放视图的定义数据库中只存放视图的定义视图对应的数据仍存放在原来的表中视图对应的数据仍存放在原来的表中随着表中数据的变化，视图的数据随之改变。随着表中数据的变化，视图的数据随之改变。对视图的查询与基本表一样对视图的查询与基本表一样对视图的更新将受到一定的限制对视图的更新将受到一定的限制基本表基本表1

75、基本表基本表2视图视图视图概念示意图二、视图定义二、视图定义二、视图定义二、视图定义视视视视图图图图3.61 1、建立视图、建立视图格式：格式：CREATE VIEW CREATE VIEW 视图名视图名(列名列名,),) AS AS 子查询子查询 WITH CHECK OPTION|READ ONLYWITH CHECK OPTION|READ ONLY；表示视图表示视图不可更新不可更新下述必须指定全部列名：下述必须指定全部列名：下述必须指定全部列名：下述必须指定全部列名：某个目标列是组函数或表达式某个目标列是组函数或表达式某个目标列是组函数或表达式某个目标列是组函数或表达式多表连接

76、时，目标列中出现同名列多表连接时，目标列中出现同名列多表连接时，目标列中出现同名列多表连接时，目标列中出现同名列不用原基本表的列名作为视图名不用原基本表的列名作为视图名不用原基本表的列名作为视图名不用原基本表的列名作为视图名在定义视图时要么指定全部视图列，要么全部省略不写；如果省略了视在定义视图时要么指定全部视图列，要么全部省略不写；如果省略了视在定义视图时要么指定全部视图列，要么全部省略不写；如果省略了视在定义视图时要么指定全部视图列，要么全部省略不写；如果省略了视图的属性列名，则视图的列名与子查询列名相同。图的属性列名，则视图的列名与子查询列名相同。图的属性列名，则视图的列名与子查询列

77、名相同。图的属性列名，则视图的列名与子查询列名相同。子查询中通常不包含子查询中通常不包含子查询中通常不包含子查询中通常不包含ORDERBYORDERBY和和和和DISTINCTDISTINCT子句。子句。子句。子句。虚拟列：经过各种计算派生出的数据所设置的派生属性列。虚拟列：经过各种计算派生出的数据所设置的派生属性列。虚拟列：经过各种计算派生出的数据所设置的派生属性列。虚拟列：经过各种计算派生出的数据所设置的派生属性列。二、视图定义二、视图定义二、视图定义二、视图定义视视视视图图图图3.62 2、视图分类、视图分类、视图分类、视图分类行列子集视图：行列子集视图：行列子集视图：行列子集视图

78、：从单个基本表导出，保留基本表的码，但去掉其它的从单个基本表导出，保留基本表的码，但去掉其它的从单个基本表导出，保留基本表的码，但去掉其它的从单个基本表导出，保留基本表的码，但去掉其它的某些列和部分行的视图。某些列和部分行的视图。某些列和部分行的视图。某些列和部分行的视图。表表表表达达达达式式式式视视视视图图图图：带虚拟列的视图。：带虚拟列的视图。：带虚拟列的视图。：带虚拟列的视图。分分分分组组组组视视视视图图图图：子查询目标表带有组函数或子查询带有子查询目标表带有组函数或子查询带有子查询目标表带有组函数或子查询带有子查询目标表带有组函数或子查询带有GROUPBYGROUPB

79、Y子句的视图。子句的视图。子句的视图。子句的视图。例例1 1：建立计算机系学生视图建立计算机系学生视图CREATE VIEW CS_SCREATE VIEW CS_SAS (SELECT S#AS (SELECT S#，SNSN，SEXSEX，SASAFROM SFROM SWHERE SD=CS)WHERE SD=CS)WITH CHECK OPTIONWITH CHECK OPTION；( (行列子集视图行列子集视图) )例例2 2：建建立立计计算算机机系系选选修修计计算算机机体体系系结结构构课程的学生视图课程的学生视图CREATE VIEWCREATE VIEWCS_SCA(S#,NAM

80、E,GR)CS_SCA(S#,NAME,GR)AS SELECT S.S#,SN,GRAS SELECT S.S#,SN,GRFROM S,SC,CFROM S,SC,CWHERE S.S#=SC.S#WHERE S.S#=SC.S# AND C.C#=SC.C# AND C.C#=SC.C# AND S.SD=CS AND S.SD=CS AND CN= AND CN=计算机体系结构计算机体系结构；例例3 3：建立学生出生年份的视图建立学生出生年份的视图CREATE CREATE VIEW VIEW BT_S(SNO,NAME,BIRTH)BT_S(SNO,NAME,BIRTH)AS SEL

81、ECT S#,SN,AS SELECT S#,SN,2003-SA2003-SA FROM S FROM S；( (表达式视图表达式视图) )( (虚拟虚拟列列) )例例4 4：建立学生平均成绩视图建立学生平均成绩视图CREATE VIEW S_AVG_G(SNOCREATE VIEW S_AVG_G(SNO，AVG_G)AVG_G)AS AS SELECT S#SELECT S#，AVG(ALL GR)AVG(ALL GR)FROM SCFROM SCGROUP BY S#GROUP BY S#；( (分组视图分组视图) )三、查询视图三、查询视图三、查询视图三、查询视图视视视视图图图图3.

82、6视图消解视图消解视图消解视图消解 ( ( ( (View Resolution)View Resolution)View Resolution)View Resolution)在对视图查询时，在对视图查询时，在对视图查询时，在对视图查询时，DBMSDBMS将进行有效性检查将进行有效性检查将进行有效性检查将进行有效性检查（表及视图）。若存在，则从数据字典中（表及视图）。若存在，则从数据字典中（表及视图）。若存在，则从数据字典中（表及视图）。若存在，则从数据字典中取出视取出视取出视取出视图定义图定义图定义图定义，并把定义中的子查询与用户查询结合起，并把定义中的子查询与用户查询结合起，并把定义中的

83、子查询与用户查询结合起，并把定义中的子查询与用户查询结合起来来来来转换为等价的对基本表的查询转换为等价的对基本表的查询转换为等价的对基本表的查询转换为等价的对基本表的查询，然后再执行。，然后再执行。，然后再执行。，然后再执行。例例1 1：求计算机系年龄小于求计算机系年龄小于2020的学生的学生SELECT S#SELECT S#，SNSNFROM CS_SFROM CS_SWHERE SA20WHERE SA20；视图视图消解消解SELECT S#SELECT S#，SNSNFROM SFROM SWHERE SD=CS AND SA20WHERE SD=CS AND SA=90WHERE

84、AVG_G=90；( (系统转换后系统转换后)SELECT S#SELECT S#，AVGAVG（ALL GRALL GR）FROM SCFROM SCWHERE AVGWHERE AVG(ALL GRALL GR)=90=90GROUP BY S#GROUP BY S#；SELECT S#,AVG(ALL GR)SELECT S#,AVG(ALL GR)FROM SCFROM SCGROUP BY S#GROUP BY S#HAVING AVG(ALL GR)=90HAVING AVG(ALL GR)=90；( (正确的转换结果正确的转换结果) )四、更新视图四、更新视图四、更新视图四、更新

85、视图视视视视图图图图3.61 1、更新视图即通过视图插入、更新视图即通过视图插入、更新视图即通过视图插入、更新视图即通过视图插入( (INSERTINSERT) )、删除删除删除删除( (DELETEDELETE) )和修改和修改和修改和修改( (UPDATEUPDATE) )数据，实质上转换为对基数据，实质上转换为对基数据，实质上转换为对基数据，实质上转换为对基本表的更新。本表的更新。本表的更新。本表的更新。2 2、为了防止用户对超出视图范围的基本表的数据、为了防止用户对超出视图范围的基本表的数据、为了防止用户对超出视图范围的基本表的数据、为了防止用户对超出视图范围的基本表的数据进行操作，在

86、定义视图时，应加上进行操作，在定义视图时，应加上进行操作，在定义视图时，应加上进行操作，在定义视图时，应加上WITHCHECKWITHCHECKOPTIONOPTION子句，则在视图上更新数据时，子句，则在视图上更新数据时，子句，则在视图上更新数据时，子句，则在视图上更新数据时，DBMSDBMS将将将将检查视图定义中的条件，不满足将拒绝执行。检查视图定义中的条件，不满足将拒绝执行。检查视图定义中的条件，不满足将拒绝执行。检查视图定义中的条件，不满足将拒绝执行。例例1 1：SC_SSC_S视图的视图的刘茜刘茜的年龄改为的年龄改为2020 转换前：转换前：UPDATE CS_SUPDATE CS

87、_SSET SA=20SET SA=20WHERE SN=WHERE SN=刘茜刘茜；转换后：转换后：UPDATE UPDATE S SSET SA=20SET SA=20WHERE SN=WHERE SN=刘茜刘茜AND SD=CSAND SD=CS；例例2 2：在在CS_SCS_S中插入中插入990075,990075,吴迪吴迪, ,女女,19,19的学生记录的学生记录转换前：转换前：INSERT INT CS_SINSERT INT CS_SVALUES(990075,VALUES(990075,吴吴迪迪 ,女女,19),19)；转换后：转换后：INSERT INTO INSER

88、T INTO S SVALUES(990075,VALUES(990075,吴迪吴迪,19,19,CS,CS,女女)；例例3：删除删除CS_S中年龄大于中年龄大于23的学生的学生转换前：转换前：DELETE FROM CS_SDELETE FROM CS_SWHERE SA23WHERE SA23；转换后：转换后：DELETE FROM DELETE FROM S SWHERE SA23WHERE SA23AND SD=CSAND SD=CS；例例4：修改修改990075学生平均成绩为学生平均成绩为90转换前：转换前：UPDATE S_AVG_GUPDATE S_AVG_GSET AVG_

89、G=90SET AVG_G=90WHERE WHERE SNO=990075SNO=990075；不可转换。不可转换。左边程序操作失败。左边程序操作失败。3 3、一一般般情情况况下下，行行列列子子集集视视图图是是可可更更新新的的，所所以各以各RDBSRDBS均只允许对行列子集视图进行更新。均只允许对行列子集视图进行更新。4 4、不可更新的视图（各系统不太一致）不可更新的视图（各系统不太一致）由多个表导出的视图，不可更新由多个表导出的视图，不可更新视图的列来自表达式或常数，不可插、改、可删视图的列来自表达式或常数，不可插、改、可删视图列是来自组函数，不可更新视图列是来自组函数，不可更新视图定义

90、中含有视图定义中含有GROUP BYGROUP BY子句，不可更新子句，不可更新视视图图定定义义中中内内层层嵌嵌套套的的表表与与查查询询目目标标同同一一个个表表，不可更新不可更新在不允许更新的视图上定义的视图，不可更新在不允许更新的视图上定义的视图，不可更新五、视图优点五、视图优点五、视图优点五、视图优点视视视视图图图图3.6 提供数据的逻辑独立性提供数据的逻辑独立性提供数据的逻辑独立性提供数据的逻辑独立性提供数据的安全保护功能提供数据的安全保护功能提供数据的安全保护功能提供数据的安全保护功能简化用户的操作简化用户的操作简化用户的操作简化用户的操作（对系统构成的视图，用户不必关心各表间的

91、联系）（对系统构成的视图，用户不必关心各表间的联系）（对系统构成的视图，用户不必关心各表间的联系）（对系统构成的视图，用户不必关心各表间的联系）同一数据多种用法同一数据多种用法同一数据多种用法同一数据多种用法3.73.7 嵌入嵌入嵌入嵌入式式式式SQLSQL语句语句语句语句3.7.1 3.7.1 嵌入式嵌入式嵌入式嵌入式SQLSQL的一般形式的一般形式的一般形式的一般形式3.7.2 3.7.2 嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间语句与主语言之间语句与主语言之间语句与主语言之间的通信的通信的通信的通信3.7.3 3.7.3 不用游标的不用游标的不用游标的不用游标的SQLSQL

92、语句语句语句语句3.7.4 3.7.4 使用游标的使用游标的使用游标的使用游标的SQLSQL语句语句语句语句3.7.5 3.7.5 动态动态动态动态SQLSQL简介简介简介简介n n SQLSQL语言提供了两种不同的使用方式：语言提供了两种不同的使用方式：语言提供了两种不同的使用方式：语言提供了两种不同的使用方式：n n 交互式交互式交互式交互式n n 嵌入式嵌入式嵌入式嵌入式n n 为什么要引入嵌入式为什么要引入嵌入式为什么要引入嵌入式为什么要引入嵌入式SQLSQLn n SQL SQL语言是非过程性语言语言是非过程性语言语言是非过程性语言语言是非过程性语言n n 事务处理应用需要高级语言事

93、务处理应用需要高级语言事务处理应用需要高级语言事务处理应用需要高级语言n n 这两种方式细节上有差别，在程序设计的环境这两种方式细节上有差别，在程序设计的环境这两种方式细节上有差别，在程序设计的环境这两种方式细节上有差别，在程序设计的环境下，下，下，下，SQLSQL语句要做某些必要的扩充语句要做某些必要的扩充语句要做某些必要的扩充语句要做某些必要的扩充3.73.7嵌入嵌入嵌入嵌入式式式式SQLSQL语句语句语句语句嵌入式嵌入式SQL将将SQLSQL语言嵌入到某种高级语言中使语言嵌入到某种高级语言中使用，利用高级语言的过程性结构来弥补用，利用高级语言的过程性结构来弥补SQLSQL语言实现复杂应用

94、方面的不足。语言实现复杂应用方面的不足。这种方式下使用的这种方式下使用的SQLSQL语言称为嵌入语言称为嵌入式式SQL(Embedded SQL)SQL(Embedded SQL)。嵌入嵌入SQLSQL的高级语言称为主语言或宿的高级语言称为主语言或宿主语言。主语言。嵌入式嵌入式嵌入式嵌入式SQLSQL的一般形式的一般形式的一般形式的一般形式 3.7.1n n 为了区分为了区分为了区分为了区分SQLSQL语句与主语言语句，需要语句与主语言语句，需要语句与主语言语句，需要语句与主语言语句，需要: : n n 前缀：前缀：前缀：前缀：EXEC SQLEXEC SQLn n 结束标志：随主语言的不同

95、而不同结束标志：随主语言的不同而不同结束标志：随主语言的不同而不同结束标志：随主语言的不同而不同n n 以以以以C C为主语言的嵌入式为主语言的嵌入式为主语言的嵌入式为主语言的嵌入式SQLSQL语句的一般形式语句的一般形式语句的一般形式语句的一般形式 EXEC SQLEXEC SQL SQL ; ;例：例：例：例：EXEC SQL DROP TABLE Student EXEC SQL DROP TABLE Student EXEC SQL DROP TABLE Student EXEC SQL DROP TABLE Student ; ;n n 以以以以COBOLCOBOL作为主语言的嵌入式

96、作为主语言的嵌入式作为主语言的嵌入式作为主语言的嵌入式SQLSQL语句的一般形式语句的一般形式语句的一般形式语句的一般形式 EXEC SQL SQLEXEC SQL END-EXECEND-EXEC例：例：例：例： EXEC SQL DROP TABLE Student END-EXECEXEC SQL DROP TABLE Student END-EXEC嵌入式嵌入式嵌入式嵌入式SQLSQL的一般形式的一般形式的一般形式的一般形式 3.7.1 说明性语句说明性语句说明性语句说明性语句嵌入嵌入嵌入嵌入SQLSQL语句语句语句语句数据定义数据定义数据定义数据定义可执行语句可执行语句可执行语句

97、可执行语句数据控制数据控制数据控制数据控制数据操纵数据操纵数据操纵数据操纵 n n 允许出现可执行的高级语言语句的地方，都可以写可执行允许出现可执行的高级语言语句的地方，都可以写可执行允许出现可执行的高级语言语句的地方，都可以写可执行允许出现可执行的高级语言语句的地方，都可以写可执行SQLSQL语句语句语句语句n n 允许出现说明语句的地方，都可以写说明性允许出现说明语句的地方，都可以写说明性允许出现说明语句的地方，都可以写说明性允许出现说明语句的地方，都可以写说明性SQLSQL语句语句语句语句嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间

98、的通信语句与主语言之间的通信3.7.21. 1. SQLSQL通信区通信区通信区通信区向主语言传递向主语言传递向主语言传递向主语言传递SQLSQL语句的执行状态信息语句的执行状态信息语句的执行状态信息语句的执行状态信息主语言能够据此控制程序流程主语言能够据此控制程序流程主语言能够据此控制程序流程主语言能够据此控制程序流程2. 2. 主变量主变量主变量主变量 1 1）主语言向）主语言向）主语言向）主语言向SQLSQL语句提供参数语句提供参数语句提供参数语句提供参数 2 2）将）将）将）将SQLSQL语句查询数据库的结果交主语言进一步处理语句查询数据库的结果交主语言进一步处理语句查询数据库的结果交

99、主语言进一步处理语句查询数据库的结果交主语言进一步处理3. 3. 游标游标游标游标解决集合性操作语言与过程性操作语言的不匹配解决集合性操作语言与过程性操作语言的不匹配解决集合性操作语言与过程性操作语言的不匹配解决集合性操作语言与过程性操作语言的不匹配1. 1. SQL SQL 通信区通信区通信区通信区n n SQLCASQLCA： SQL Communication Area SQL Communication Arean n SQLCA SQLCA是一个数据结构是一个数据结构是一个数据结构是一个数据结构n n SQLCASQLCA的用途的用途的用途的用途n n SQLSQL语句执行后，语句执

100、行后，语句执行后，语句执行后，DBMSDBMS反馈给应用程序信息反馈给应用程序信息反馈给应用程序信息反馈给应用程序信息n n 描述系统当前工作状态描述系统当前工作状态描述系统当前工作状态描述系统当前工作状态n n 描述运行环境描述运行环境描述运行环境描述运行环境n n 这些信息将送到这些信息将送到这些信息将送到这些信息将送到SQLSQL通信区通信区通信区通信区SQLCASQLCA中中中中n n 应用程序从应用程序从应用程序从应用程序从SQLCASQLCA中取出这些状态信息，据此决定中取出这些状态信息，据此决定中取出这些状态信息，据此决定中取出这些状态信息，据此决定接下来执行的语句接下来执行的语

101、句接下来执行的语句接下来执行的语句嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2DBMSDBMS工作状态、运行信息工作状态、运行信息SQLCASQLCA应用程序应用程序后继语句后继语句1. 1. SQL SQL 通信区通信区通信区通信区嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2n n SQLCASQLCA的内容的内容的内容的内容n n 与所执行的与所执行的与所执行的与所执行的SQLSQL语句有关语句有关语句有关语句有

102、关n n 与该与该与该与该SQLSQL语句的执行情况有关语句的执行情况有关语句的执行情况有关语句的执行情况有关例：在执行删除语句例：在执行删除语句例：在执行删除语句例：在执行删除语句DELETEDELETE后，不同的执行情况，后，不同的执行情况，后，不同的执行情况，后，不同的执行情况，SQLCASQLCA中有不同的信息：中有不同的信息：中有不同的信息：中有不同的信息：n n 违反数据保护规则，操作拒绝违反数据保护规则，操作拒绝违反数据保护规则，操作拒绝违反数据保护规则，操作拒绝n n 没有满足条件的行，一行也没有删除没有满足条件的行，一行也没有删除没有满足条件的行，一行也没有删除没有满足条件的

103、行，一行也没有删除n n 成功删除，并有删除的行数成功删除，并有删除的行数成功删除，并有删除的行数成功删除，并有删除的行数n n 无条件删除警告信息无条件删除警告信息无条件删除警告信息无条件删除警告信息n n 由于各种原因，执行出错由于各种原因，执行出错由于各种原因，执行出错由于各种原因，执行出错1. 1. SQL SQL 通信区通信区通信区通信区n n SQLCASQLCA的使用方法的使用方法的使用方法的使用方法n n SQLSQL语句执行后，语句执行后，语句执行后，语句执行后，DBMSDBMS反馈给应用程序信息反馈给应用程序信息反馈给应用程序信息反馈给应用程序信息n n 描述系统当前工作状

104、态描述系统当前工作状态描述系统当前工作状态描述系统当前工作状态n n 描述运行环境描述运行环境描述运行环境描述运行环境n n 这些信息将送到这些信息将送到这些信息将送到这些信息将送到SQLSQL通信区通信区通信区通信区SQLCASQLCA中中中中n n 应用程序从应用程序从应用程序从应用程序从SQLCASQLCA中取出这些状态信息，据此决定中取出这些状态信息，据此决定中取出这些状态信息，据此决定中取出这些状态信息，据此决定接下来执行的语句接下来执行的语句接下来执行的语句接下来执行的语句嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主

105、语言之间的通信3.7.2SQLCASQLCA在应用程序中用在应用程序中用EXEC SQL INCLUDE SQLCAEXEC SQL INCLUDE SQLCA 加以定义加以定义 SQLCODESQLCODE：SQLCASQLCA中的一个存放返回代码中的一个存放返回代码的变量；每次的变量；每次SQLSQL语句执行后均返回一个语句执行后均返回一个值，表示该值，表示该SQLSQL语句执行是否成功，以及语句执行是否成功，以及不成功的原因。不成功的原因。通常用预定义的常量通常用预定义的常量SUCCESSSUCCESS表示成功；表示成功；否则否则在在SQLCODESQLCODE中将存放错误代码。中将存放

106、错误代码。 2. 2. 主变量主变量主变量主变量嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2什么是主变量什么是主变量嵌入式嵌入式嵌入式嵌入式SQLSQL语句中可以使用主语言的程序变语句中可以使用主语言的程序变语句中可以使用主语言的程序变语句中可以使用主语言的程序变量来输入或输出数据量来输入或输出数据量来输入或输出数据量来输入或输出数据在在在在SQLSQL语句中使用的主语言程序变量简称为语句中使用的主语言程序变量简称为语句中使用的主语言程序变量简称为语句中使用的主语言程序变量简称为主变量（主变量（主变量（主

107、变量（HostVariableHostVariable）2. 2. 主变量主变量主变量主变量嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2n n主变量的类型主变量的类型n n输入主变量输入主变量输入主变量输入主变量n n由应用程序对其赋值，由应用程序对其赋值，由应用程序对其赋值，由应用程序对其赋值，SQLSQL语句引用语句引用语句引用语句引用n n输出主变量输出主变量输出主变量输出主变量n n由由由由SQLSQL语句赋值或设置状态信息，返回给应用程序语句赋值或设置状态信息，返回给应用程序语句赋值或设置状态信

108、息，返回给应用程序语句赋值或设置状态信息，返回给应用程序n n一个主变量有可能既是输入主变量又是输出主变量一个主变量有可能既是输入主变量又是输出主变量一个主变量有可能既是输入主变量又是输出主变量一个主变量有可能既是输入主变量又是输出主变量2. 2. 主变量主变量主变量主变量嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2n n主变量的用途主变量的用途主变量的用途主变量的用途n n输入主变量输入主变量输入主变量输入主变量n n 指定向数据库中插入的数据指定向数据库中插入的数据指定向数据库中插入的数据指定向数据库

109、中插入的数据n n 将数据库中的数据修改为指定值将数据库中的数据修改为指定值将数据库中的数据修改为指定值将数据库中的数据修改为指定值n n 指定执行的操作指定执行的操作指定执行的操作指定执行的操作n n 指定指定指定指定WHEREWHERE子句或子句或子句或子句或HAVINGHAVING子句中的条件子句中的条件子句中的条件子句中的条件n n输出主变量输出主变量输出主变量输出主变量n n 获取获取获取获取SQLSQL语句的结果数据语句的结果数据语句的结果数据语句的结果数据n n 获取获取获取获取SQLSQL语句的执行状态语句的执行状态语句的执行状态语句的执行状态2. 2. 主变量主变量主变量主变

110、量嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2n n指示变量指示变量指示变量指示变量n n一个主变量可以附带一个指示变量（一个主变量可以附带一个指示变量（一个主变量可以附带一个指示变量（一个主变量可以附带一个指示变量（Indicator Indicator VariableVariable）n n什么是指示变量什么是指示变量什么是指示变量什么是指示变量n n整型变量整型变量整型变量整型变量n n用来用来用来用来“ “指示指示指示指示” ”所指主变量的值或条件所指主变量的值或条件所指主变量的值或条件所指主变

111、量的值或条件n n指示变量的用途指示变量的用途指示变量的用途指示变量的用途n n输入主变量可以利用指示变量赋空值输入主变量可以利用指示变量赋空值输入主变量可以利用指示变量赋空值输入主变量可以利用指示变量赋空值n n输出主变量可以利用指示变量检测出是否空值，值输出主变量可以利用指示变量检测出是否空值，值输出主变量可以利用指示变量检测出是否空值，值输出主变量可以利用指示变量检测出是否空值，值是否被截断是否被截断是否被截断是否被截断2. 2. 主变量主变量主变量主变量嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2

112、定义主变量：定义主变量：EXEC SQL BEGIN DECLARE SECTIONEXEC SQL BEGIN DECLARE SECTION. . (说明主变量和指示变量).EXEC SQL END DECLARE SECTIONEXEC SQL END DECLARE SECTION引引用用主主变变量量：在在SQLSQL语语句句中中引引用用主主变变量量和和指指示示变变量量时，其变量名前应加时，其变量名前应加：。3. 3. 游游游游标标标标嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2n n为什么要

113、使用游标为什么要使用游标为什么要使用游标为什么要使用游标n n SQLSQL语言与主语言具有不同数据处理方式语言与主语言具有不同数据处理方式语言与主语言具有不同数据处理方式语言与主语言具有不同数据处理方式n nSQLSQL语言是面向集合的，一条语言是面向集合的，一条语言是面向集合的，一条语言是面向集合的，一条SQLSQL语句原则上语句原则上语句原则上语句原则上可以产生或处理多条记录可以产生或处理多条记录可以产生或处理多条记录可以产生或处理多条记录n n主语言是面向记录的，一组主变量一次只能存主语言是面向记录的，一组主变量一次只能存主语言是面向记录的，一组主变量一次只能存主语言是面向记录的，一组

114、主变量一次只能存放一条记录放一条记录放一条记录放一条记录（cursor）3. 3. 游游游游标标标标嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2n n 什么是游标什么是游标n n游标是系统为用户开设的一个数据缓冲区，存游标是系统为用户开设的一个数据缓冲区，存游标是系统为用户开设的一个数据缓冲区，存游标是系统为用户开设的一个数据缓冲区，存放放放放SQLSQL语句的执行结果语句的执行结果语句的执行结果语句的执行结果n n每个游标区都有一个名字每个游标区都有一个名字每个游标区都有一个名字每个游标区都有一个名字

115、n n用户可以用用户可以用用户可以用用户可以用SQLSQL语句逐一从游标中获取记录，语句逐一从游标中获取记录，语句逐一从游标中获取记录，语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理并赋给主变量，交由主语言进一步处理并赋给主变量，交由主语言进一步处理并赋给主变量，交由主语言进一步处理（cursor）实例实例实例实例嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2例：带有嵌入式例：带有嵌入式例：带有嵌入式例：带有嵌入式SQLSQL的一小段的一小段的一小段的一小段C C程序程序程序程序 . .EXE

116、C SQL INCLUDE SQLCA; EXEC SQL INCLUDE SQLCA; /* (1) /* (1) 定义定义定义定义SQLSQL通信区通信区通信区通信区 */ */EXEC SQL BEGIN DECLARE SECTION; EXEC SQL BEGIN DECLARE SECTION; /* (2) /* (2) 说明主变量说明主变量说明主变量说明主变量 */ */ CHAR title_id(7);CHAR title_id(7); CHAR title(81); CHAR title(81); INT royalty; INT royalty;EXEC SQL END

117、 DECLARE SECTION;EXEC SQL END DECLARE SECTION;嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2main()main() EXEC SQL DECLARE C1 CURSOR FOR EXEC SQL DECLARE C1 CURSOR FOR SELECT tit_id, tit, SELECT tit_id, tit, roy roy FROM titles; FROM titles; /* (3) /* (3) 游标操作（定义游标）游标操作（定义游标）游标操作

118、（定义游标）游标操作（定义游标）*/*/ /* /* 从从从从titlestitles表中查询表中查询表中查询表中查询 tit_id, tit,tit_id, tit, roy roy */*/ EXEC SQL OPEN C1; EXEC SQL OPEN C1; /* (4) /* (4) 游标操作（打开游标）游标操作（打开游标）游标操作（打开游标）游标操作（打开游标）*/*/嵌入式嵌入式嵌入式嵌入式SQLSQL语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信语句与主语言之间的通信3.7.2 for(;)for(;) EXEC SQL FETCH C1 INTO :tit

119、le_id, :title, :royalty; EXEC SQL FETCH C1 INTO :title_id, :title, :royalty; /* (5) /* (5) 游标操作（将当前数据放入主变量并推进游标指针）游标操作（将当前数据放入主变量并推进游标指针）游标操作（将当前数据放入主变量并推进游标指针）游标操作（将当前数据放入主变量并推进游标指针）*/*/ if (if (sqlcasqlca. .sqlcodesqlcode SUCCESS) SUCCESS) /* (6) /* (6) 利用利用利用利用SQLCASQLCA中的状态信息决定何时退出循环中的状态信息决定何时退出

120、循环中的状态信息决定何时退出循环中的状态信息决定何时退出循环 */ */ break;break; printf printf(Title ID: %s, Royalty: %d, :title_id, :royalty);(Title ID: %s, Royalty: %d, :title_id, :royalty); printf printf(Title: %s, :title);(Title: %s, :title); /* /* 打印查询结果打印查询结果打印查询结果打印查询结果 */ */ EXEC SQL CLOSE C1; EXEC SQL CLOSE C1; /* (7) /*

121、 (7) 游标操作（关闭游标）游标操作（关闭游标）游标操作（关闭游标）游标操作（关闭游标）*/*/ 不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3n n不用游标的不用游标的SQL语句的种类语句的种类n n 说明性语句说明性语句说明性语句说明性语句n n 数据定义语句数据定义语句数据定义语句数据定义语句n n 数据控制语句数据控制语句数据控制语句数据控制语句n n 查询结果为单记录的查询结果为单记录的查询结果为单记录的查询结果为单记录的SELECTSELECT语句语句语句语句n n 非非非非CURRENTCURRENT形式的形式的形式的形式的UPDATEU

122、PDATE语句语句语句语句n n 非非非非CURRENTCURRENT形式的形式的形式的形式的DELETEDELETE语句语句语句语句n n INSERTINSERT语句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3n n 说明性语句是专为在嵌入式说明性语句是专为在嵌入式说明性语句是专为在嵌入式说明性语句是专为在嵌入式SQLSQL中说明主变量、中说明主变量、中说明主变量、中说明主变量、SQLCASQLCA等而设置的等而设置的等而设置的等而设置的n n 说明主变量说明主变量说明主变量说明主变量 1. 1. EXEC SQL BEGIN DECL

123、ARE SECTION; EXEC SQL BEGIN DECLARE SECTION; 2. EXEC SQL END DECLARE SECTION; 2. EXEC SQL END DECLARE SECTION;n n 这两条语句必须配对出现，相当于一个括号，两条语这两条语句必须配对出现，相当于一个括号，两条语这两条语句必须配对出现，相当于一个括号，两条语这两条语句必须配对出现，相当于一个括号，两条语句中间是主变量的说明句中间是主变量的说明句中间是主变量的说明句中间是主变量的说明n n 说明说明说明说明SQLCASQLCA 3. EXEC SQL INCLUDE SQLCA 3. EX

124、EC SQL INCLUDE SQLCA一、说明性语句一、说明性语句一、说明性语句一、说明性语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3例1 建立一个“学生”表Student EXEC SQL CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Ssex CHAR(1), Sage INT, Sdept CHAR(15);二、数据定义语句二、数据定义语句二、数据定义语句二、数据定义语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.

125、3n n 数据定义语句中不允许使用主变量数据定义语句中不允许使用主变量例：下列语句是错误的例：下列语句是错误的 EXEC SQL DROP TABLE :table_name;二、数据定义语句二、数据定义语句二、数据定义语句二、数据定义语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3例2 把查询Student表权限授给用户U1 EXEC SQL GRANT SELECT ON TABLE Student TO U1;三、数据控制语句三、数据控制语句三、数据控制语句三、数据控制语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语

126、句3.7.3n 语句格式EXEC SQL SELECT ALL|DISTINCT ,. INTO ,. FROM , . WHERE GROUP BY HAVING ORDER BY ASC|DESC;四、查询结果为单记录的四、查询结果为单记录的四、查询结果为单记录的四、查询结果为单记录的SELECTSELECTSELECTSELECT语句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3例3 根据学生号码查询学生信息。假设已将要查询的学生的学号赋给了主变量givensno EXEC SQL SELECT Sno, Sname, Ssex, S

127、age, Sdept INTO :Hsno, :Hname, :Hsex, :Hage, :Hdept FROM Student WHERE Sno=:givensno;nHsno, Hname, Hsex, Hage, Hdept和givensno均是主变量，并均已在前面的程序中说明过了。四、查询结果为单记录的四、查询结果为单记录的四、查询结果为单记录的四、查询结果为单记录的SELECTSELECTSELECTSELECT语句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3例4 查询某个学生选修某门课程的成绩。假设已将要查询的学生的学号赋给

128、了主变量givensno，将课程号赋给了主变量givencno。 EXEC SQL SELECT Sno, Cno, Grade INTO :Hsno, :Hcno, :Hgrade:Gradeid FROM SC WHERE Sno=:givensno AND Cno=:givencno;四、查询结果为单记录的四、查询结果为单记录的四、查询结果为单记录的四、查询结果为单记录的SELECTSELECTSELECTSELECT语句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3n非CURRENT形式的UPDATE语句n使用主变量n SET子句n

129、WHERE子句n使用指示变量n SET子句n非CURRENT形式的UPDATE语句可以操作多条元组五、非五、非五、非五、非CURRENTCURRENTCURRENTCURRENT形式的形式的形式的形式的UPDATEUPDATEUPDATEUPDATE语句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3例5 将全体学生1号课程的考试成绩增加若干分。假设增加的分数已赋给主变量Raise EXEC SQL UPDATE SC SET Grade=Grade+:Raise WHERE Cno=1;五、非五、非五、非五、非CURRENTCURRENTC

130、URRENTCURRENT形式的形式的形式的形式的UPDATEUPDATEUPDATEUPDATE语句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3例6 修改某个学生1号课程的成绩。假设该学生的学号已赋给主变量givensno，修改后的成绩已赋给主变量newgrade。 EXEC SQL UPDATE SC SET Grade=:newgrade WHERE Sno=:givensno;五、非五、非五、非五、非CURRENTCURRENTCURRENTCURRENT形式的形式的形式的形式的UPDATEUPDATEUPDATEUPDATE语

131、句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3例7 将计算机系全体学生年龄置NULL值 Sageid=-1; EXEC SQL UPDATE Student SET Sage=:Raise:Sageid WHERE Sdept=CS;五、非五、非五、非五、非CURRENTCURRENTCURRENTCURRENT形式的形式的形式的形式的UPDATEUPDATEUPDATEUPDATE语句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3n非CURRENT形式的DELETE语句n使用主变量n W

132、HERE子句n非CURRENT形式的DELETE语句可以操作多条元组六、非六、非六、非六、非CURRENTCURRENTCURRENTCURRENT形式的形式的形式的形式的DELETEDELETEDELETEDELETE语句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3例8 某个学生退学了，现要将有关他的所有选课记录删除掉。假设该学生的姓名已赋给主变量stdname EXEC SQL DELETE FROM SC WHERE Sno= (SELECT Sno FROM Student WHERE Sname=:stdname);六、非六、非

133、六、非六、非CURRENTCURRENTCURRENTCURRENT形式的形式的形式的形式的DELETEDELETEDELETEDELETE语句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3n非CURRENT形式的INSERT语句n使用主变量n VALUES子句n使用指示变量n VALUES子句n非CURRENT形式的INSERT语句一次只能输入一条元组七、七、七、七、INSERTINSERTINSERTINSERT语句语句语句语句不使用游标的不使用游标的不使用游标的不使用游标的SQLSQL语句语句语句语句3.7.3例9 某个学生新选修了某门

134、课程，将有关记录插入SC表假设学生的学号已赋给主变量stdno，课程号已赋给主变量couno gradeid=-1; EXEC SQL INSERT INTO SC(Sno, Cno, Grade) VALUES(:stdno, :couno, :gr:gradeid);n由于该学生刚选修课程，尚未考试，因此成绩列为空。所以本例中用指示变量指示相应的主变量为空值。七、七、七、七、INSERTINSERTINSERTINSERT语句语句语句语句使用游标的使用游标的使用游标的使用游标的SQLSQL语句语句语句语句3.7.4n 必须使用游标的SQL语句n 查询结果为多条记录的SELECT语句n CU

135、RRENT形式的UPDATE语句n CURRENT形式的DELETE语句使用游标的使用游标的使用游标的使用游标的SQLSQL语句语句语句语句3.7.4n 使用游标的步骤n 1. 说明游标n 2. 打开游标n 3. 移动游标指针，然后取当前记录n 4. 关闭游标一、一、一、一、查询结果为多条记录的查询结果为多条记录的查询结果为多条记录的查询结果为多条记录的SELECTSELECTSELECTSELECT语句语句语句语句使用游标的使用游标的使用游标的使用游标的SQLSQL语句语句语句语句3.7.4n 使用DECLARE语句n 语句格式EXEC SQL DECLARE CURSOR FOR ;n

136、功能n是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。1. 1. 1. 1. 说明游标说明游标说明游标说明游标使用游标的使用游标的使用游标的使用游标的SQLSQL语句语句语句语句3.7.4n 使用OPEN语句n 语句格式 EXEC SQL OPEN ;n 功能n 打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中n 这时游标处于活动状态，指针指向查询结果集中第一条记录之前2. 2. 2. 2. 打开游标打开游标打开游标打开游标使用游标的使用游标的使用游标的使用游标的SQLSQL语句语句语句语句3.7.4n 使用FETCH语句n 语句格式

137、EXEC SQL FETCH NEXT|PRIOR| FIRST|LAST FROM INTO ,.;3. 3. 3. 3. 移动游标指针，然后取当前记录移动游标指针，然后取当前记录移动游标指针，然后取当前记录移动游标指针，然后取当前记录使用游标的使用游标的使用游标的使用游标的SQLSQL语句语句语句语句3.7.4n 功能n 指定方向推动游标指针，然后将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。n NEXT|PRIOR|FIRST|LAST：指定推动游标指针的方式。n NEXT：向前推进一条记录n PRIOR：向回退一条记录n FIRST：推向第一条记录n LAST：推向最后一条

138、记录n 缺省值为NEXT3. 3. 3. 3. 移动游标指针，然后取当前记录移动游标指针，然后取当前记录移动游标指针，然后取当前记录移动游标指针，然后取当前记录使用游标的使用游标的使用游标的使用游标的SQLSQL语句语句语句语句3.7.4n n说明说明说明说明(1) (1) 主变量必须与主变量必须与主变量必须与主变量必须与SELECTSELECT语句中的目标列表达式具有一一对语句中的目标列表达式具有一一对语句中的目标列表达式具有一一对语句中的目标列表达式具有一一对应关系应关系应关系应关系(2) (2) FETCHFETCH语句通常用在一个循环结构中，通过循环执行语句通常用在一个循环结构中，通

139、过循环执行语句通常用在一个循环结构中，通过循环执行语句通常用在一个循环结构中，通过循环执行FETCHFETCH语句逐条取出结果集中的行进行处理语句逐条取出结果集中的行进行处理语句逐条取出结果集中的行进行处理语句逐条取出结果集中的行进行处理(3) (3) 为进一步方便用户处理数据，现在一些关系数据库管理系为进一步方便用户处理数据，现在一些关系数据库管理系为进一步方便用户处理数据，现在一些关系数据库管理系为进一步方便用户处理数据，现在一些关系数据库管理系统对统对统对统对FETCHFETCH语句做了扩充，允许用户向任意方向以任意步长语句做了扩充，允许用户向任意方向以任意步长语句做了扩充，允许用户向任

140、意方向以任意步长语句做了扩充，允许用户向任意方向以任意步长移动游标指针移动游标指针移动游标指针移动游标指针3. 3. 3. 3. 移动游标指针，然后取当前记录移动游标指针，然后取当前记录移动游标指针，然后取当前记录移动游标指针，然后取当前记录使用游标的使用游标的使用游标的使用游标的SQLSQL语句语句语句语句3.7.4n 使用CLOSE语句n 语句格式 EXEC SQL CLOSE ;n 功能n 关闭游标，释放结果集占用的缓冲区及其他资源n 说明n 游标被关闭后，就不再和原来的查询结果集相联系n 被关闭的游标可以再次被打开，与新的查询结果相联系4. 4. 4. 4. 关闭游标关闭游标关闭游标

141、关闭游标例1 查询某个系全体学生的信息（学号、姓名、性别和年龄）。要查询的系名由用户在程序运行过程中指定，放在主变量deptname中. EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; . /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/ . . EXEC SQL END DECLARE SECTION; . . gets(deptname); /* 为主变量deptname赋值 */ . EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname,

142、Ssex, Sage FROM Student WHERE SDept=:deptname; /* 说明游标 */ EXEC SQL OPEN SX /* 打开游标 */ WHILE(1) /* 用循环结构逐条处理结果集中的记录 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/if (sqlca.sqlcode SUCCESS) break; /* 若所有查询结果均已处理完或出现 SQL语句错误，则退出循环 */ /* 由主语言语句进行进一步处理 */ .

143、 . ; EXEC SQL CLOSE SX; /* 关闭游标 */ . .例2 查询某些系全体学生的信息。 . EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; . /* 说明主变量 deptname,HSno,HSname,HSsex,HSage等*/ . EXEC SQL END DECLARE SECTION; . EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname; /* 说明游标 *

144、/ WHILE (gets(deptname)!=NULL) /* 接收主变量deptname的值 */ /* 下面开始处理deptname指定系的学生信息, 每次循环中deptname可具有不同的值 */ EXEC SQL OPEN SX /* 打开游标 */ WHILE (1) /* 用循环结构逐条处理结果集中的记录 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/ if (sqlca.sqlcode SUCCESS) break; /* 若所有查询结果

145、均已处理完或出现 SQL语句错误，则退出循环 */ /* 由主语言语句进行进一步处理 */ . . ; /* 内循环结束 */ EXEC SQL CLOSE SX; /* 关闭游标 */ ; /* 外循环结束 */ . .动态动态动态动态SQLSQL简介简介简介简介3.7.5n 用户可以在程序运行过程中根据实际需要输入WHERE子句或HAVING子句中某些变量的值。n 语句中主变量的个数与数据类型在预编译时都是确定的，只有是主变量的值是程序运行过程中动态输入的。静态静态静态静态SQLSQLSQLSQL特点特点特点特点动态动态动态动态SQLSQL简介简介简介简介3.7.51. 什么是动态嵌入式

146、SQLn动态SQL方法允许在程序运行过程中临时“组装”SQL语句。2. 应用范围n在预编译时下列信息不能确定时n SQL语句正文n 主变量个数n 主变量的数据类型n SQL语句中引用的数据库对象（列、索引、基本表、视图等）动态动态动态动态 SQL SQL SQL SQL 动态动态动态动态SQLSQL简介简介简介简介3.7.53. 动态SQL的形式n语句可变n 临时构造完整的SQL语句n条件可变n WHERE子句中的条件n HAVING短语中的条件n数据库对象、查询条件均可变n SELECT子句中的列名n FROM子句中的表名或视图名n WHERE子句中的条件n HAVING短语中的条件动态

147、动态动态动态 SQL SQL SQL SQL 动态动态动态动态SQLSQL简介简介简介简介3.7.54. 常用动态SQL语句n EXECUTE IMMEDIATEn PREPAREn EXECUTEn DESCRIBEn 使用动态SQL技术更多的是涉及程序设计方面的知识，而不是SQL语言本身动态动态动态动态 SQL SQL SQL SQL 关系数据库标准语言关系数据库标准语言第第3章章3.1SQL概述概述3.2查询语句查询语句3.3更新语句更新语句3.4SQLDDL3.5SQLDCL3.6视图视图3.7嵌入式嵌入式SQL 数据库上机实验操作说明数据库上机实验操作说明1、98下在开始菜单中选择

148、注销，重新以相应的用户名登录下在开始菜单中选择注销，重新以相应的用户名登录例如例如用户名用户名user6密码密码pass62000下下直接以相应的用户名登录直接以相应的用户名登录用户名用户名user6密码密码pass6注：一机房注：一机房user11002、在开始菜单在开始菜单/程序程序-MicrosoftSQL7.0/QueryAnalyzerSQLSERVER:server1(第一机房）第一机房）connectioninformation选择第一个选项选择第一个选项-WindowsNT然后按然后按OK3、sample数据库中，数据库中，Student,Course,SC,DEPT,EMP,SALGRADE,数据库上机实验操作说明数据库上机实验操作说明1、win2000登录登录2、在开始菜单在开始菜单/程序程序-MicrosoftSQL7.0/QueryAnalyzerSQLSERVER:f3-svrLoginName:USER2Password:USER2connectioninformation选择第二个选项选择第二个选项-SQLSERVER然后按然后按OK3、STU1、WORK1数据库中，数据库中，S,C,SC;DEPT,EMP,SALGRADEF3-SVRSQL SERVERCONNECTIONUSER2USER2选择数据库选择数据库

展开阅读全文

数据库课件关系数据库标准语言SQL语言

最新文档