第3章 关系数据库语言SQL.doc

优质文本第3章 关系数据库语言SQL3.1 根本内容分析3.1.1 本章重要概念1SQL数据库的体系结构，SQL的组成。2SQL的数据定义：SQL模式、根本表和索引的创立和撤销。3SQL的数据查询；SELECT语句的句法，SELECT语句的三种形式及各种限定，根本表的联接操作，SQL3中的递归查询。4SQL的数据更新：插入、删除和修改语句。5视图的创立和撤消，对视图更新操作的限制。6嵌入式SQL：预处理方式，使用规定，使用技术，卷游标，动态SQL语句。3.1.2 本章的重点篇幅1教材中P97的例3.8SELECT语句。2教材中P123的例3.31和P123的例3.32嵌入式SQL。3.1.3 重要内容分析SELECT语句是SQL的核心内容，对于该语句考生应掌握以下内容。1SELECT语句的来历在关系代数中最常用的式子是以下表达式：A1,An(F(R1××Rm)这里R1、Rm为关系，F是公式，A1、An为属性。针对上述表达式，SQL为此设计了SELECTFROMWHERE句型：SELECT A1，AnFROM R1，RmWHERE F这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。2SELECT语句中出现的根本表名，应理解为根本表中的元组变量，而列名应理解为元组分量。3SELECT语句的语义有三种情况，下面以学生表SS#，SNAME，AGE，SEX为例说明。第一种情况：SELECT语句中未使用分组子句，也未使用聚合操作，那么SELECT子句的语义是对查询的结果执行投影操作。譬如：SELECT S#，SNAMEFROM SWHERE SEX='M'；第二种情况：SELECT语句中未使用分组子句，但在SELECT子句中使用了聚合操作，此时SELECT子句的语义是对查询结果执行聚合操作。譬如：SELECT COUNT*，AVGAGEFROM SWHERE SEX='M'；该语句是求男同学的人数和平均年龄。第三种情况：SELECT语句使用了分组子句和聚合操作有分组子句时必有聚合操作，此时SELECT子句的语义是对查询结果的每一分组去做聚合操作。譬如：SELECT AGE，COUNT*FROM SWHERE SEX='M'GROUP BY AGE；该语句是求男同学每一年龄的人数。4SELECT语句中使用分组子句的先决条件是要有聚合操作。但执行聚合操作不一定要用分组子句。譬如求男同学的人数，此时聚合值只有一个，因此不必分组。但同一个聚合操作的值有多个时，必须使用分组子句。譬如求每一年龄的学生人数。此时聚合值有多个，及年龄有关，因此必须分组。3.2 教材中习题3的解答3.1 名词解释·根本表：实际存储在数据库中的表，称为根本表。·视图：是从根本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是数据库中只存放视图的定义而不存放视图的数据。·实表：是对根本表的别称。·虚表：是对视图的别称。·相关子查询：SELECT语句嵌套时，子查询中查询条件依赖于外层查询中的值，因此子查询要反复求值供外层查询使用。这种子查询称为相关子查询。·联接查询：查询时要从多个根本表中提取数据，此时把多个根本表写在同一层的FROM子句中，这种查询形式称为联接查询。·嵌套查询：查询时要从多个根本表中提取数据，此时把多个根本表分别放在不同层次上的FROM子句中，这种查询形式称为嵌套查询。·交互式SQL：在终端交互方式使用的SQL语言。·嵌入式SQL：嵌入在高级语言的程序中使用的SQL语言。·共享变量：嵌入的SQL语句和主语言语句间传递信息的变量，称为共享变量。共享变量先由主语言程序定义，再用SQL的说明语句说明，然后SQL语句就可使用这些变量。·游标：游标是及某一查询相联系的符号名。游标有游标关系和游标指针两层含义。在游标翻开时，游标指针指向查询结果的第一个记录之前。·卷游标：在游标推进时，可以进退自如的游标。3.2 对于教学数据库的三个根本表 S(S#,SNAME,AGE,SEX) SC(S#,C#,GRADE) C(C#,CNAME,TEACHER)试用SQL的查询语句表达以下查询：检索LIU老师所授课程的课程号和课程名。检索年龄大于23岁的男学生的学号和姓名。检索学号为S3学生所学课程的课程名及任课教师名。检索至少选修LIU老师所授课程中一门课程的女学生姓名。检索WANG同学不学的课程的课程号。检索至少选修两门课程的学生学号。检索全部学生都选修的课程的课程号及课程名。检索选修课程包含LIU老师所授课程的学生学号。解：SELECT C#, CNAMEFROM CWHERE TNAME=LIU; SELECT S#, SNAMEFROM SWHERE AGE>23 AND SEX=M;SELECT CNAME，TEACHERFROM SC, CWHERE SC.C#=C.C# AND S#=S3;SELECT SNAME联接查询方式FROM S, SC, CWHERE S.S#=SC.S# AND SC.C#=C.C# AND SEX=FAND TNAME=LIU;或：SELECT SNAME嵌套查询方式FROM SWHERE SEX=FAND S# IN (SELECT S#FROM SCWHERE C# IN (SELECT C#FROM CWHERE TNAME=LIU);或：SELECT SNAME存在量词方式FROM SWHERE SEX=FAND EXISTS (SELECT *FROM SCWHERE SC.S#=S.S#AND EXISTS (SELECT * FROM C WHERE C.C#=SC.C#AND TNAME=LIU);SELECT C#FROM CWHERE NOT EXISTS(SELECT * FROM S, SC WHERE S.S#=SC.S# AND SC.C#=C.C#AND SNAME=WANG); SELECT DISTINCT X.S#FROM SC AS X, SC AS YWHERE X.S#=Y.S# AND X.C#!=Y.C#; SELECT C#, CNAMEFROM CWHERE NOT EXISTS(SELECT *FROM SWHERE NOT EXISTS(SELECT * FROM SC WHERE S#=S.S# AND C#=C.C#);在1974年的SYSTEM R系统中，曾使用过“集合包含的语法，即集合1CONTAINS集合2用这种语法也能写出此题的SELECT语句，即：SELECT C#，CNAMEFROM CWHERE SELECT S# FROM SC WHERE C#=C.C# CONTAINSSELECT S# FROM S；由于判断“集合1CONTAINS集合2及“NOT EXISTS集合2EXCEPT集合1是等价的，因此此题的SELECT语句也能这样写：SELECT C#，CNAMEFROM CWHERE NOT EXISTSSELECT S# FROM SEXCEPTSELECT S# FROM SC WHERE C#=C.C#； SELECT DISTINCT S#FROM SC AS XWHERE NOT EXISTS(SELECT * FROM C WHERE TNAME=LIUAND NOT EXISTS(SELECT * FROM SC AS Y WHERE Y.S#=X.S# AND Y.C#=C.C#);及类似，此题的SELECT语句也能这样写：SELECT DISTINCT S#FROM SC XWHERE NOT EXISTSSELECT C# FROM C WHERE TEACHER=LIUEXCEPTSELECT C# FROM SC Y WHERE Y.S#=X.S#；3.3 对于第3.2题中的8个查询语句，试给出SELECT语句的图示形式。解：为了说明问题，这里先用高级语言的算法形式表示其执行过程，再给出图示形式。下面给出、的算法及图示形式。 如果把三个关系S、SC、C看成三个文件，那么可以看出这个查询语句的SELECT语句实际上是一个三重循环。从而可得这个查询的算法形式如下：for关系S的每个元组dowhich:=false;if S.SEX=F thenfor 关系SC的每个元组，且NOT which doif SC.S#=S.S# thenfor 关系C的每个元组，且NOT which doif C.C#=SC.C#，且TEACHER=LIU thenprint(S.SNAME); which:=true; ;这个算法可以用图3.1表示。SS#SNAMEAGESEXSCS#C#GRADECC#CNAMETEACHER_XP.F_X_Y_YLIU图3.1 for 关系S的每个元组 do if S.SNAME=WANG thenfor 关系C的每个元组 dowhich:=false; for 关系SC的每个元组，且NOT which doif SC.S# =S.S# ，且SC.C#=C.C# then which := true；if NOT which then printS.SNAME；这个算法可以用图3.2表示。图中“表示“NOT EXISTS，即“不存在满足此条件的元组SS#SNAMEAGESEXCC#CNAMETEACHERSCS#C#GRADE_XWANGP._Y_X_Y图3.2 for 关系C的每个元组 do which1 := false； for 关系S的每个元组，且NOT which1 do which2 := false；for 关系SC的每个元组，且NOT which2 do if SC.S# =S.S#， 且SC.C# =C.C# then which2 := true；if NOT which2 then which1:=true；if NOT which1 then printC.C#,C.CNAME； ；这个算法可以用图3.3表示。CC#CNAMETEACHERSS#SNAMEAGESEXSCS#C#GRADEP._XP._Y_Y_X图3.3 for 关系SC的每个元组x do which1 := false； for 关系C的每个元组y，且NOT which1 do if y.TEACHER=LIUthen which2 := false；for 关系SC的每个元组z，且NOT which2 doif z.S# =x.S#，且z.C# =y.C# then which2 := true；if NOT which2 then which1:=true；if NOT which1 then printx.S#；这个算法可以用图3.4表示。SCS#C#GRADECC#CNAMETEACHERSCS#C#GRADEP._XP._YLIU_X_Y图3.43.4 设有两个根本表RA，B，C和SA，B，C，试用SQL查询语句表达以下关系代数表达式： RS RS RS R×SA,B(R) B,CS 3=3 课 1，63=4R×S 1，2，3R S R÷CS解： (SELECT * FROM R)UNION(SELECT * FROM S)； (SELECT * FROM R)INTERSECT(SELECT * FROM S)； (SELECT * FROM R)MINUS(SELECT * FROM S)； SELECT *FROM R, S； SELECT R.A, R.B, S.CFROM R, SWHERE R.B=S.B； SELECT R.A, S.CFROM R, SWHERE R.C=S.A； SELECT R.*R.*表示R中全部属性FROM R, SWHERE R.C=S.C；R÷CS的元组表达式如下： t |uvwRu Sv Rw w1=u1 w2=u2 w3=v3 t1=u1 t2=u2据此，可写出SELECT语句：SELECT A, BFROM R RXWHERE NOT EXISTS( SELECT *FROM SWHERE NOT EXISTS( SELECT *FROM R RYWHERE RY.A=RX.A AND RY.B=RX.B AND RY.C=S.C)；3.5 设有两个关系RA，B和SA，C，试用SQL查询语句表示以下域表达式： a |$bRab b=17 abc | Rab Sac a |$c$b1$b2SacRab1 Rcb2 b1>b2解：SELECT AFROM RWHERE B=17；SELECT R.A, R.B, S.CFROM R, SWHERE R.A=S.A；SELECT S.AFROM S, R RX, R RYWHERE S.A=RX.A AND RX.B>RY.B；3.6 试表达SQL语言的关系代数特点和元组演算特点。答：SQL的关系代数特点如下： 有关系代数运算的并、交、差、自然联接等运算符； FROM子句表达了笛卡尔积操作，WHERE子句表达了选择操作，SELECT子句表达了投影操作。SQL的元组演算特点如下： FROM子句中的根本表名应视为“元组变量，属性名应视为“元组分量； 有存在量词EXISTS符号。3.7 试用SQL查询语句表达以下对3.2题中三个根本表S、SC、C的查询： 在表C中统计开设课程的教师人数。 求选修C4课程的女学生的平均年龄。 求LIU老师所授课程的每门课程的平均成绩。 统计每个学生选修课程的门数超过5门的学生才统计。要求输出学生学号和选修门数，查询结果按门数降序排列，假设门数相同，按学号升序排列。 检索学号比WANG同学大，而年龄比他小的学生姓名。 在表SC中检索成绩为空值的学生学号和课程号。 检索姓名以L打头的所有学生的姓名和年龄。 求年龄大于女同学平均年龄的男学生姓名和年龄。 求年龄大于所有女同学年龄的男学生姓名和年龄。解：SELECT COUNT(DISTINCT TEACHER)FROM C；SELECT AVG(AGE)FROM S, SCWHERE S.S#=SC.S# AND C#=C4 AND SEX=F；SELECT C.C#，AVG(GRADE)FROM SC，CWHERE SC.C#=C.C# AND TEACHER=LIUGROUP BY C.C#；SELECT S#, COUNT(C#)FROM SCGROUP BY S#HAVING COUNT(*)>5ORDER BY 2 DESC, 1；SELECT SNAMEFROM SWHERE S#>ALL(SELECT S# FROM S WHERE SNAME=WANG) AND AGE<ALL(SELECT S#FROM SWHERE SNAME=WANG)；SELECT S#, C#FROM SCWHERE GRADE IS NULL；SELECT SNAME, AGEFROM SWHERE SNAME LIKE L%；SELECT SNAME, AGEFROM SWHERE SEX=MAND AGE>(SELECT AVG(AGE)FROM SWHERE SEX=F)；SELECT SNAME, AGEFROM SWHERE SEX=M AND AGE>ALL(SELECT AGEFROM SWHERE SEX=F)；3.8 对于下面的关系R和S，试求出以下各种联接操作的执行结果：R NATURAL INNER JOIN SR NATURAL RIGHT OUTER JOIN SR RIGHT OUTER JOIN S USINGCR INNER JOIN SR FULL OUTER JOIN S ON falseRABCSBCDa1b1c1b1c1d1a2b2c2b2c2d2a3b3c3b4c4d4解：ABCDABCDAR.BCS.BDa1b1c1d1a1b1c1d1a1b1c1b1d1a2b2c2d2a2b2c2d2a2b2c2b2d2nullb4c4d4nullnullc4b4d4AR.BR.CS.BS.CDAR.BR.CS.BS.CDa1b1c1b1c1d1a1b1c1nullnullnulla1b1c1b2c2d2a2b2c2nullnullnulla1b1c1b4c4d4a3b3c3nullnullnulla2b2c2b1c1d1nullnullnullb1c1d1a2b2c2b2c2d2nullnullnullb2c2d2a2b2c2b4c4d4nullnullnullb4c4d4a3b3c3b1c1d1a3b3c3b2c2d2a3b3c3b4c4d43.9 SQL2提供CASE表达式操作，这个操作类似于程序设计语言中的多分支选择结构，其句法如下：CASEWHEN 条件1 THEN 结果1WHEN 条件2 THEN 结果2WHEN 条件n THEN 结果nELSE 结果mEND如果自上而下“条件i首先被满足，那么这个操作返回值“结果i可以是某个表达式的值；如果没有一个条件被满足，那么返回值“结果m。在根本表SCS#，C#，GRADE中，GRADE值是百分制。如果欲转换成“成绩等第，那么规那么如下：假设GRADE40那么等第为F，假设40GRADE60那么等第为C，假设60GRADE80那么等第为B，假设80GRADE那么等第为A。试写出以下两个查询语句： 检索每个学生的学习成绩，成绩显示时以等第SCORE形式出现。 检索每个等第的学生人次数。解： SELECT S#，C#，CASEWHEN GRADE >= 80 THEN 'A'WHEN GRADE >= 60 THEN 'B'WHEN GRADE >= 40 THEN 'C'ELSE 'F'END AS SCOREFROM SC； SELECT SCORE，COUNTS#FROM SELECT S#，C#，CASEWHEN GRADE >= 80 THEN 'A'WHEN GRADE >= 60 THEN 'B'WHEN GRADE >= 40 THEN 'C'ELSE 'F'ENDFROM SC AS RESULTS#，C#，SCOREGROUP BY SCORE；3.10 用第3.9题给出的CASE操作在以下更新语句中完成SC表中的元组更新： 假设课程号为C5那么增加6分，假设课程号为C8那么增加10分，其他一律增加5分。 假设C4课程的成绩低于该门课平均成绩时，提高5%，否那么提高4%。解： UPDATE SCSET GRADE = GRADE + CASEWHEN C# ='C5' THEN 6WHEN C# ='C8' THEN 10ELSE 5END ； UPDATE SCSET GRADE = GRADE * CASEWHEN GRADE <SELECT AVGGRADEFROM SCWHERE C# ='C4'THEN 1.05ELSE 1.04END WHERE C# ='C4'；3.11 设零件之间有组合联系，其关系模式如下：PARTP#，PNAME，SUBP#，TOTAL其属性表示零件编号、零件名称、所需子零件编号及数量。设临时关系WP#，SUBP#的属性分别表示零件编号、这种零件的直接或间接子零件编号。 试写出表示关系W的规那么。 写出计算W的递归查询语句。解： W(x, y)PART(x, g, y, h)W(x, y)W(x, z) W(z, y)WITH RECURSIVE W(P#, SUBP#) AS(SELECT P#, SUBP# FROM PART)UNION(SELECT W1.P#, W2.SUBP# FROM W AS W1, W AS W2 WHERE W1.SUBP#=W2.P#)SELECT * FROM W；3.12 试用SQL更新语句表达对3.2题教学数据库中关系S、SC、C的更新操作： 往关系C中插一个课程元组'C8'，'VC+'，'BAO'。 检索所授每门课程平均成绩均大于80分的教师姓名，并把检索到的值送往另一个已存在的表FACULTYTNAME。 在SC中删除尚无成绩的选课元组。 把选修LIU老师课程的女同学选课元组全部删去。 把MATHS课不及格的成绩全改为60分。 把低于所有课程总平均成绩的女同学成绩提高5。 在表SC中修改C4课程的成绩，假设成绩小于等于70分时提高5，假设成绩大于70分时提高4用两种方法实现，一种方法是用两个UPDATE语句实现，另一种方法是用带CASE操作的一个UPDATE语句实现。 在表SC中，当某个成绩低于全部课程的平均成绩时，提高5%。解： INSERT INTO CVALUES('C8'，'VC+'，'BAO')； INSERT INTO FACULTY(TNAME)SELECT DISTINCT TEACHERFROM (SELECT TEACHER, C.C#, AVG(GRADE)FROM S, SCWHERE SC.C#=C.C#GROUP BY TEACHER, C.C#)AS RESULT(TEACHER, C#, AVG_GRADE) AS XWHERE 80<=ALL(SELECT AVG_GRADEFROM RESULT AS YWHERE Y.TEACHER=X.TEACHER)； DELETE FROM SCWHERE GRADE IS NULL； DELETE FROM SCWHERE S# IN(SELECT S# FROM S WHERE SEX='F')AND C# IN(SELECT C# FROM C WHERE TEACHER='LIU')； UPDATE SCSET GRADE=60WHERE GRADE<60AND C# IN(SELECT C# FROM C WHERE CNAME='MATHS')； UPDATE SCSET GRADE=GRADE*1.05WHERE S# IN(SELECT S# FROM S WHERE SEX='F')AND GRADE<(SELECT AVG(GRADE) FROM SC)； 用两个UPDATE语句实现：UPDATE SCSET GRADE=GRADE*1.04WHERE C#='C4' AND GRADE>70；UPDATE SCSET GRADE=GRADE*1.05WHERE C#='C4' AND GRADE<=70；这两个UPDATE语句的顺序不能颠倒。用一个UPDATE语句实现：UPDATE SCSET GRADE=GRADE*CASEWHEN GRADE>70 THEN 1.04ELSE 1.05ENDWHERE C#='C4'； UPDATE SCSET GRADE=GRADE*1.05WHERE GRADE<(SELECT AVG(GRADE) FROM SC)；3.13 设数据库中有三个关系：职工表EMPE#，ENAME，AGE，SEX，ECITY，其属性分别表示职工工号、姓名、年龄、性别和籍贯。工作表WORKSE#，C#，SALARY，其属性分别表示职工工号、工作的公司编号和工资。公司表COMPC#，CNAME，CITY，其属性分别表示公司编号、公司名称和公司所在城市。试用SQL语句写出以下操作： 用CREATE TABLE语句创立上述三个表，需指出主键和外键。 检索超过50岁的男职工的工号和姓名。 假设每个职工只能在一个公司工作，检索工资超过1000元的男性职工工号和姓名。 假设每个职工可在多个公司工作，检索在编号为C4和C8公司兼职的职工工号和姓名。 检索在“联华公司工作、工资超过1000元的男性职工的工号和姓名。 假设每个职工可在多个公司工作,检索每个职工的兼职公司数目和工资总数.显示E#，NUM，SUM_SALARY，分别表示工号、公司数目和工资总数。 工号为E6的职工在多个公司工作，试检索至少在E6职工兼职的所有公司工作的职工工号。 检索联华公司中低于本公司平均工资的职工工号和姓名。 在每一公司中为50岁以上职工加薪100元假设职工为多个公司工作，可重复加。 在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。解：CREATE TABLE EMP( E# CHAR(4) NOT NULL,ENAME CHAR(8) NOT NULL,AGESMALLINT,SEXCHAR(1),ECITYCHAR(20),PRIMARY KEY(E#)；CREATE TABLE COMP( C# CHAR(4) NOT NULL,CNAME CHAR(20) NOT NULL,CITYCHAR(20),PRIMARY KEY(C#)；CREATE TABLE WORKS( E# CHAR(4) NOT NULL, C#CHAR(4) NOT NULL, SALARYSMALLINT,PRIMARY KEY(E#, C#),FOREIGN KEY(E#) REFERENCES EMP(E#),FOREIGN KEY(C#) REFERENCES COMP(C#)；SELECT E#, ENAMEFROM EMPWHERE AGE>50 AND SEX='M'；SELECT EMP.E#, ENAMEFROM EMP, WORKSWHERE EMP.E#=WORKS.E# AND SALARY>1000；SELECT A.E#, A.ENAMEFROM EMP A, WORKS B, WORKS CWHERE A.E#=B.E# AND B.E#=C.E#AND B.C#='C4' AND C.C#='C8'；SELECT A.E#, A.ENAMEFROM EMP A, WORKS B, COMP CWHERE A.E#=B.E# AND B.C#=C.C#AND CNAME='联华公司' AND SALARY>1000AND SEX='M'；SELECT E#, COUNT(C#) AS NUM, SUM(SALARY) AS SUM_SALARYFROM WORKS GROUP BY E#；SELECT X.E#FROM WORKS XWHERE NOT EXISTS(SELECT *FROM WORKS YWHERE E#='E6'AND NOT EXISTS(SELECT *FROM WORKS ZWHERE Z.E#=X.E#AND Z.C#=Y.C#)；SELECT A.E#, A.ENAMEFROM EMP A, WORKS B, COMP CWHERE A.E#=B.E# AND B.C#=C.C# AND CNAME='联华公司'AND SALARY<(SELECT AVG(SALARY)FROM WORKS, COMPWHERE WORKS.C#=COMP.C#AND CNAME='联华公司'；UPDATE WORKSSET SALARY=SALARY+100WHERE E# IN (SELECT E# FROM EMP WHERE AGE>50)；DELETE FROM WORKSWHERE E# IN (SELECT E# FROM EMP WHERE AGE>60)；DELETE FROM EMPWHERE AGE>60；3.14 对第3.13题中的关系建立一个有关女职工信息的视图EMP_WOMAN，属性包括E#，ENAME，C#，CNAME，SALARY。然后对视图EMP_WOMAN操作，检索每一位女职工的工资总数。假设每个职工可在多个公司兼职解：CREATE VIEW EMP_WOMANAS SELECT A.E#, A.ENAME, C.C#, CNAME, SALARYFROM EMP A, WORKS B, COMP CWHERE A.E#=B.E# AND B.C#=C.C# AND SEX='F'；SELECT E#，SUM(SALARY)FROM EMP_WOMANGROUP BY E#；3.15 在第1章中提到的仓库管理数据库中有五个根本表:零件PART(P#，PNAME，COLOR，WEIGHT)工程PROJECTJ#，JNAME，DATE供给商SUPPLIERS#，SNAME，SADDR供给P_P(J#，P#，TOTAL)采购P_S(P#，S#，QUANTITY) 试用SQL DDL语句定义上述五个根本表，需说明主键和外键。 试将PROJECT、P_P、PART三个根本表的联接定义为一个视图VIEW1,将PART、P_S、SUPPLIER三个根本表的联接定义为一个视图VIEW2。 试在上述两个视图的根底上进行查询操作：a检索上海的供给商所供给的零件的编号和名称。b检索工程J4所用零件的供给商的编号和名称。解：CRE