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

资源描述

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

《第3章关系数据库语言SQL.docx》由会员分享，可在线阅读，更多相关《第3章关系数据库语言SQL.docx（53页珍藏版）》请在冰豆网上搜索。

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

第3章关系数据库语言SQL

3.1基本内容分析

3.1.1本章重要概念

（1）SQL数据库的体系结构，SQL的组成。

（2）SQL的数据定义：

SQL模式、基本表和索引的创建和撤销。

（3）SQL的数据查询；SELECT语句的句法，SELECT语句的三种形式及各种限定，基本表的联接操作，SQL3中的递归查询。

（4）SQL的数据更新：

插入、删除和修改语句。

（5）视图的创建和撤消，对视图更新操作的限制。

（6）嵌入式SQL：

预处理方式，使用规定，使用技术，卷游标，动态SQL语句。

3.1.2本章的重点篇幅

（1）教材中P97的例3.8（SELECT语句）。

（2）教材中P123的例3.31和P123的例3.32（嵌入式SQL）。

3.1.3重要内容分析

SELECT语句是SQL的核心内容，对于该语句考生应掌握下列内容。

1．SELECT语句的来历

在关系代数中最常用的式子是下列表达式：

πA1,…,An（σF（R1×…×Rm））

这里R1、…、Rm为关系，F是公式，A1、…、An为属性。

针对上述表达式，SQL为此设计了SELECT—FROM—WHERE句型：

SELECTA1，…，An

FROMR1，…，Rm

WHEREF

这个句型是从关系代数表达式演变来的，但WHERE子句中的条件表达式F要比关系代数中公式更灵活。

2．SELECT语句中出现的基本表名，应理解为基本表中的元组变量，而列名应理解为元组分量。

3．SELECT语句的语义有三种情况，下面以学生表S（S#，SNAME，AGE，SEX）为例说明。

第一种情况：

SELECT语句中未使用分组子句，也未使用聚合操作，那么SELECT子句的语义是对查询的结果执行投影操作。

譬如：

SELECTS#，SNAME

FROMS

WHERESEX='M'；

第二种情况：

SELECT语句中未使用分组子句，但在SELECT子句中使用了聚合操作，此时SELECT子句的语义是对查询结果执行聚合操作。

譬如：

SELECTCOUNT（*），AVG（AGE）

FROMS

WHERESEX='M'；

该语句是求男同学的人数和平均年龄。

第三种情况：

SELECT语句使用了分组子句和聚合操作（有分组子句时必有聚合操作），此时SELECT子句的语义是对查询结果的每一分组去做聚合操作。

譬如：

SELECTAGE，COUNT（*）

FROMS

WHERESEX='M'

GROUPBYAGE；

该语句是求男同学每一年龄的人数。

4．SELECT语句中使用分组子句的先决条件是要有聚合操作。

但执行聚合操作不一定要用分组子句。

譬如求男同学的人数，此时聚合值只有一个，因此不必分组。

但同一个聚合操作的值有多个时，必须使用分组子句。

譬如求每一年龄的学生人数。

此时聚合值有多个，与年龄有关，因此必须分组。

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老师所授课程的学生学号。

解：

①SELECTC#,CNAME

FROMC

WHERETNAME=’LIU’;

2SELECTS#,SNAME

FROMS

WHEREAGE>23ANDSEX=’M’;

③SELECTCNAME，TEACHER

FROMSC,C

WHERESC.C#=C.C#ANDS#=’S3’;

④SELECTSNAME（联接查询方式）

FROMS,SC,C

WHERES.S#=SC.S#ANDSC.C#=C.C#

ANDSEX=’F’ANDTNAME=’LIU’;

或：

SELECTSNAME（嵌套查询方式）

FROMS

WHERESEX=’F’

ANDS#IN（SELECTS#

FROMSC

WHEREC#IN（SELECTC#

FROMC

WHERETNAME=’LIU’））;

或：

SELECTSNAME（存在量词方式）

FROMS

WHERESEX=’F’

ANDEXISTS（SELECT*

FROMSC

WHERESC.S#=S.S#

ANDEXISTS（SELECT*

FROMC

WHEREC.C#=SC.C#

ANDTNAME=’LIU’））;

⑤SELECTC#

FROMC

WHERENOTEXISTS

（SELECT*

FROMS,SC

WHERES.S#=SC.S#ANDSC.C#=C.C#

ANDSNAME=’WANG’）;

6SELECTDISTINCTX.S#

FROMSCASX,SCASY

WHEREX.S#=Y.S#ANDX.C#!

=Y.C#;

7SELECTC#,CNAME

FROMC

WHERENOTEXISTS

（SELECT*

FROMS

WHERENOTEXISTS

（SELECT*

FROMSC

WHERES#=S.S#ANDC#=C.C#））;

在1974年的SYSTEMR系统中，曾使用过“集合包含”的语法，即

（集合1）CONTAINS（集合2）

用这种语法也能写出本题的SELECT语句，即：

SELECTC#，CNAME

FROMC

WHERE（SELECTS#FROMSCWHEREC#=C.C#）

CONTAINS

（SELECTS#FROMS）；

由于判断“（集合1）CONTAINS（集合2）”与“NOTEXISTS（（集合2）EXCEPT（集合1））”是等价的，因此本题的SELECT语句也能这样写：

SELECTC#，CNAME

FROMC

WHERENOTEXISTS

（（SELECTS#FROMS）

EXCEPT

（SELECTS#FROMSCWHEREC#=C.C#））；

8SELECTDISTINCTS#

FROMSCASX

WHERENOTEXISTS

（SELECT*

FROMC

WHERETNAME=’LIU’

ANDNOTEXISTS

（SELECT*

FROMSCASY

WHEREY.S#=X.S#ANDY.C#=C.C#））;

与⑦类似，本题的SELECT语句也能这样写：

SELECTDISTINCTS#

FROMSCX

WHERENOTEXISTS

（（SELECTC#FROMCWHERETEACHER=’LIU’）

EXCEPT

（SELECTC#FROMSCYWHEREY.S#=X.S#））；

3.3对于第3.2题中的8个查询语句，试给出SELECT语句的图示形式。

解：

为了说明问题，这里先用高级语言的算法形式表示其执行过程，再给出图示形式。

下面给出④、⑤、⑦、⑧的算法及图示形式。

④如果把三个关系S、SC、C看成三个文件，那么可以看出这个查询语句的SELECT语句实际上是一个三重循环。

从而可得这个查询的算法形式如下：

for关系S的每个元组do

{which:

=false;

ifS.SEX=’F’then

for关系SC的每个元组，且NOTwhichdo

ifSC.S#=S.S#then

for关系C的每个元组，且NOTwhichdo

ifC.C#=SC.C#，且TEACHER=’LIU’then

{print（S.SNAME）;which:

=true;}

};

这个算法可以用图3.1表示。

SNAME

AGE

SEX

GRADE

CNAME

TEACHER

LIU

图3.1

⑤for关系S的每个元组do

　｛ifS.SNAME=’WANG’then

for关系C的每个元组do

{which:

=false;

for关系SC的每个元组，且NOTwhichdo

ifSC.S#=S.S#，且SC.C#=C.C#thenwhich:

=true；

ifNOTwhichthenprint（S.SNAME）；

}；

这个算法可以用图3.2表示。

图中“┐”表示“NOTEXISTS”，即“不存在满足此条件的元组”

SNAME

AGE

SEX

CNAME

TEACHER

GRADE

WANG

P._Y

┐

图3.2

⑦for关系C的每个元组do

　｛which1:

=false；

for关系S的每个元组，且NOTwhich1do

{which2:

=false；

for关系SC的每个元组，且NOTwhich2do

ifSC.S#=S.S#，且SC.C#=C.C#thenwhich2:

=true；

ifNOTwhich2thenwhich1:

=true；

}；

ifNOTwhich1thenprint（C.C#,C.CNAME）；

}；

这个算法可以用图3.3表示。

CNAME

TEACHER

SNAME

AGE

SEX

GRADE

P._X

┐

图3.3

⑧for关系SC的每个元组xdo

　｛which1:

=false；

for关系C的每个元组y，且NOTwhich1do

{ify.TEACHER=’LIU’then

{which2:

=false；

for关系SC的每个元组z，且NOTwhich2do

ifz.S#=x.S#，且z.C#=y.C#thenwhich2:

=true；

ifNOTwhich2thenwhich1:

=true；

}；

ifNOTwhich1thenprint（x.S#）；

}

}；

这个算法可以用图3.4表示。

GRADE

CNAME

TEACHER

GRADE

P._X

┐

LIU

┐

图3.4

3.4设有两个基本表R（A，B，C）和S（A，B，C），试用SQL查询语句表达下列关系代数表达式：

R∪S

R∩S

R－S④R×S⑤πA,B（R）πB,C（S）

3=3课

⑥π1，6（σ3=4（R×S）⑦π1，2，3（RS）⑧R÷πC（S）

解：

①（SELECT*FROMR）

UNION

（SELECT*FROMS）；

②（SELECT*FROMR）

INTERSECT

（SELECT*FROMS）；

③（SELECT*FROMR）

MINUS

（SELECT*FROMS）；

④SELECT*

FROMR,S；

⑤SELECTR.A,R.B,S.C

FROMR,S

WHERER.B=S.B；

⑥SELECTR.A,S.C

FROMR,S

WHERER.C=S.A；

⑦SELECTR.*（R.*表示R中全部属性）

FROMR,S

WHERER.C=S.C；

⑧R÷πC（S）的元组表达式如下：

{t|（∃u）（∀v）（∃w）（R（u）∧S（v）∧R（w）∧w[1]=u[1]∧w[2]=u[2]∧w[3]=v[3]∧t[1]=u[1]∧t[2]=u[2]）}

据此，可写出SELECT语句：

SELECTA,B

FROMRRX

WHERENOTEXISTS

（SELECT*

FROMS

WHERENOTEXISTS

（SELECT*

FROMRRY

WHERERY.A=RX.AANDRY.B=RX.BANDRY.C=S.C））；

3.5设有两个关系R（A，B）和S（A，C），试用SQL查询语句表示下列域表达式：

①{a|（b）（R（ab）∧b=‘17’）}

②{abc|R（ab）∧S（ac）}

③{a|（c）（b1）（b2）（S（ac）∧R（ab1）∧R（cb2）∧b1>b2）}

解：

①SELECTA

FROMR

WHEREB=17；

②SELECTR.A,R.B,S.C

FROMR,S

WHERER.A=S.A；

③SELECTS.A

FROMS,RRX,RRY

WHERES.A=RX.AANDRX.B>RY.B；

3.6试叙述SQL语言的关系代数特点和元组演算特点。

答：

SQL的关系代数特点如下：

1有关系代数运算的并、交、差、自然联接等运算符；

2FROM子句体现了笛卡尔积操作，WHERE子句体现了选择操作，SELECT子句体现了投影操作。

SQL的元组演算特点如下：

1FROM子句中的基本表名应视为“元组变量”，属性名应视为“元组分量”；

2有存在量词EXISTS符号。

3.7试用SQL查询语句表达下列对3.2题中三个基本表S、SC、C的查询：

在表C中统计开设课程的教师人数。

求选修C4课程的女学生的平均年龄。

求LIU老师所授课程的每门课程的平均成绩。

统计每个学生选修课程的门数（超过5门的学生才统计）。

要求输出学生学号和选修门数，查询结果按门数降序排列，若门数相同，按学号升序排列。

检索学号比WANG同学大，而年龄比他小的学生姓名。

在表SC中检索成绩为空值的学生学号和课程号。

检索姓名以L打头的所有学生的姓名和年龄。

求年龄大于女同学平均年龄的男学生姓名和年龄。

求年龄大于所有女同学年龄的男学生姓名和年龄。

解：

SELECTCOUNT（DISTINCTTEACHER）

FROMC；

SELECTAVG（AGE）

FROMS,SC

WHERES.S#=SC.S#ANDC#=’C4’ANDSEX=’F’；

SELECTC.C#，AVG（GRADE）

FROMSC，C

WHERESC.C#=C.C#ANDTEACHER=‘LIU’

GROUPBYC.C#；

SELECTS#,COUNT（C#）

FROMSC

GROUPBYS#

HAVINGCOUNT（*）>5

ORDERBY2DESC,1；

SELECTSNAME

FROMS

WHERES#>ALL（SELECTS#

FROMS

WHERESNAME=’WANG’）

ANDAGE

FROMS

WHERESNAME=’WANG’）；

SELECTS#,C#

FROMSC

WHEREGRADEISNULL；

SELECTSNAME,AGE

FROMS

WHERESNAMELIKE’L%’；

SELECTSNAME,AGE

FROMS

WHERESEX=’M’

ANDAGE>（SELECTAVG（AGE）

FROMS

WHERESEX=’F’）；

SELECTSNAME,AGE

FROMS

WHERESEX=’M’

ANDAGE>ALL（SELECTAGE

FROMS

WHERESEX=’F’）；

3.8对于下面的关系R和S，试求出下列各种联接操作的执行结果：

①RNATURALINNERJOINS

②RNATURALRIGHTOUTERJOINS

③RRIGHTOUTERJOINSUSING（C）

④RINNERJOINS

⑤RFULLOUTERJOINSONfalse

解：

①

②

③

R.B

S.B

null

④

R.B

R.C

S.B

S.C

⑤

R.B

R.C

S.B

S.C

null

3.9SQL2提供CASE表达式操作，这个操作类似于程序设计语言中的多分支选择结构，其句法如下：

CASE

WHEN条件1THEN结果1

WHEN条件2THEN结果2

……

WHEN条件nTHEN结果n

ELSE结果m

END

如果自上而下“条件i”首先被满足，那么这个操作返回值“结果i”（可以是某个表达式的值）；如果没有一个条件被满足，那么返回值“结果m”。

在基本表SC（S#，C#，GRADE）中，GRADE值是百分制。

如果欲转换成“成绩等第”，则规则如下：

若GRADE＜40则等第为F，若40≤GRADE＜60则等第为C，若60≤GRADE＜80则等第为B，若80≤GRADE则等第为A。

试写出下列两个查询语句：

①检索每个学生的学习成绩，成绩显示时以等第（SCORE）形式出现。

②检索每个等第的学生人次数。

解：

①SELECTS#，C#，CASE

WHENGRADE>=80THEN'A'

WHENGRADE>=60THEN'B'

WHENGRADE>=40THEN'C'

ELSE'F'

ENDASSCORE

FROMSC；

②SELECTSCORE，COUNT（S#）

FROM（SELECTS#，C#，CASE

WHENGRADE>=80THEN'A'

WHENGRADE>=60THEN'B'

WHENGRADE>=40THEN'C'

ELSE'F'

END

FROMSC）ASRESULT（S#，C#，SCORE）

GROUPBYSCO

展开阅读全文

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

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