spl语言说明教程课件

第5章关系数据库标准语言—SQL关系数据库标准语言—SQL5.1SQL概述及特点5.2数据定义语句

5.3数据查询语句5.4数据更新语句5.5嵌入式SQL5.6数据控制机制和语句5.1SQL概述及特点1.SQL的主要功能

(1)数据定义功能

定义关系数据库的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。

(2)数据操纵功能

包括数据查询和数据更新两种数据操作语句：数据查询指对数据库中的数据查询、统计、分组、排序操作；数据更新指数据的插入、删除、修改等数据维护操作。

(3)数据控制功能

通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。SQL功能极强，完成核心功能只用了9个动词。5.2数据定义语句5.2.1基本表的定义和维护

1.定义基本表

定义基本表语句的一般格式为：CREATETABLE[〈库名〉]〈表名〉(〈列名〉〈数据类型〉[〈列级完整性约束条件〉]，〈列名〉〈数据类型〉[〈列级完整性约束条件〉]][，…n]

[，〈表级完整性约束条件〉][，…n])；类型表示类型说明数值型数据SMALLINT半字长二进制整数。15bits数据INTEGER或INT全字长（四字长）整数。31bits数据DECIMAL(p[，q])十进制数，共p位，其中小数点后q位。0≤q≤p，q=0时可省略不写FLOAT双字长浮点数字符型数据CHARTER(n)或CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串特殊数据类型GRAPHIC(n)长度为n的定长图形字符串VARGRAPHIC(n)最大长度为n的变长图形字符串日期时间型DATE日期型，格式为YYYY-MM-DDTIME时间型，格式为HH.MM.SSTIMESTAMP日期加时间(1)SQL支持的数据类型(3)表级完整性约束条件涉及到关系中多个列的限制条件。

1)UNIQUE约束。惟一性约束。

2)PRIMARYKEY约束。定义主码，保证惟一性和非空性。CONTRAINT〈约束名〉PRIMARYKEY[CLUSTERED](〈列组〉)

3)FOREIGNKEY约束。用于定义参照完整性。

CONTRAINT〈约束名〉FOREIGNKEY(〈外码〉)

REFERENCES〈被参照表名〉(〈与外码对应的主码名〉)

CREATETABLE学生( 学号CHAR(5)NOTNULLUNIQUE，

姓名CHAR(8)NOTNULL， 年龄SMALLINT，

性别CHAR(2)， 所在系CHAR(20)，

DEFAULTC120FOR年龄，

CONSTRAINTC2CHECK(性别IN(‘男’，‘女’)))；

建立基本表：学生(学号，姓名，年龄，性别，所在系)；

课程(课程号，课程名，先行课)；

选课(学号，课程号，成绩).CREATETABLE选课( 学号CHAR(5)， 课程号CHAR(5)，

成绩SMALLINT，

CONSTRAINTC3CHECK(成绩BETWEEN0AND100)，

CONSTRAINTC4PRIMARYKEY(学号，课程号)，

CONSTRAINTC5FOREIGNKEY(学号)REFERENCES学生(学号)，

CONSTRAINTC6FOREIGNKEY(课程号)REFERENCES课程(课程号))；实体完整性定义关系模型的实体完整性CREATETABLE中用PRIMARYKEY定义单属性构成的码有两种说明方法定义为列级约束条件定义为表级约束条件对多个属性构成的码只有一种说明方法定义为表级约束条件实体完整性定义(续)(2)在表级定义主码CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(20)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，

PRIMARYKEY(Sno));实体完整性定义(续)［例］将SC表中的Sno，Cno属性组定义为码CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，

PRIMARYKEY(Sno，Cno)/*只能在表级定义主码*/);实体完整性检查和违约处理(续)检查记录中主码值是否唯一的一种方法是进行全表扫描实体完整性检查和违约处理(续)索引参照完整性定义关系模型的参照完整性定义在CREATETABLE中用FOREIGNKEY短语定义哪些列为外码用REFERENCES短语指明这些外码参照哪些表的主码参照完整性定义(续)例如，关系SC中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码。Sno，Cno分别参照引用Student表的主码和Course表的主码［例］定义SC中的参照完整性CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，/*在表级定义实体完整性*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)，/*在表级定义参照完整性*/

FOREIGNKEY(Cno)REFERENCESCourse(Cno)

/*在表级定义参照完整性*/);违约处理参照完整性违约处理1.拒绝(NOACTION)执行默认策略2.级联(CASCADE)操作3.设置为空值（SET-NULL）对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值违约处理(续)［例］显式说明参照完整性的违约处理示例CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，PRIMARYKEY（Sno，Cno）， FOREIGNKEY(Sno)REFERENCESStudent(Sno) ONDELETECASCADE/*级联删除SC表中相应的元组*/ONUPDATECASCADE，/*级联更新SC表中相应的元组*/FOREIGNKEY(Cno)REFERENCESCourse(Cno) ONDELETENOACTION /*当删除course表中的元组造成了与SC表不一致时拒绝删除*/ONUPDATECASCADE /*当更新course表中的cno时，级联更新SC表中相应的元组*/)；用户定义的完整性用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求RDBMS提供，而不必由应用程序承担属性上的约束条件的定义CREATETABLE时定义列值非空（NOTNULL）列值唯一（UNIQUE）检查列值是否满足一个布尔表达式（CHECK）属性上的约束条件的定义(续)2.列值唯一［例］建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码CREATETABLEDEPT(DeptnoNUMERIC(2)，DnameCHAR(9)UNIQUE，/*要求Dname列值唯一*/LocationCHAR(10)，PRIMARYKEY(Deptno))；属性上的约束条件的定义(续)3.用CHECK短语指定列值应该满足的条件［例7］Student表的Ssex只允许取“男”或“女”。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(8)NOTNULL，SsexCHAR(2)CHECK(SsexIN(‘男’，‘女’)

)，/*性别属性Ssex只允许取'男'或'女'*/SageSMALLINT，SdeptCHAR(20));属性上的约束条件检查和违约处理插入元组或修改属性的值时，RDBMS检查属性上的约束条件是否被满足如果不满足则操作被拒绝执行元组上的约束条件的定义在CREATETABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件元组上的约束条件的定义(续)［例9］当学生的性别是男时，其名字不能以Ms.打头。CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(8)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，PRIMARYKEY(Sno)，

CHECK(Ssex='女'ORSnameNOTLIKE'Ms.%')/*定义了元组中Sname和Ssex两个属性值之间的约束条件*/)；性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立；当性别是男性时，要通过检查则名字一定不能以Ms.打头元组上的约束条件检查和违约处理插入元组或修改属性的值时，RDBMS检查元组上的约束条件是否被满足如果不满足则操作被拒绝执行完整性约束命名子句CONSTRAINT约束CONSTRAINT<完整性约束条件名>［PRIMARYKEY短语|FOREIGNKEY短语|CHECK短语］完整性约束命名子句(续)［例］建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。CREATETABLEStudent(SnoNUMERIC(6)

CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)，SnameCHAR(20)

CONSTRAINTC2NOTNULL，SageNUMERIC(3)

CONSTRAINTC3CHECK(Sage<30)，SsexCHAR(2)

CONSTRAINTC4CHECK(SsexIN('男'，'女'))，CONSTRAINTStudentKeyPRIMARYKEY(Sno))；在Student表上建立了5个约束条件，包括主码约束（命名为StudentKey）以及C1、C2、C3、C4四个列级约束。2.修改基本表和删除基本表

ALTERTABLE〈表名〉

[ADD(〈新列名〉〈数据类型〉[完整性约束][，…n])]

[DROP〈完整性约束名〉]

[MODIFY(〈列名〉〈数据类型〉[，…n])]；

向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDS_entranceDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。

增加课程名称必须取唯一值的约束条件。

ALTERTABLECourseADDUNIQUE(Cname);

［例］修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40可以先删除原来的约束条件，再增加新的约束条件ALTERTABLEStudentDROPCONSTRAINTC1;ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN900000AND999999)，ALTERTABLEStudentDROPCONSTRAINTC3;ALTERTABLEStudentADDCONSTRAINTC3CHECK(Sage<40)；5.2.2索引的定义和维护1.索引的作用

1)使用索引可以明显地加快数据查询的速度。

2)使用索引可保证数据的惟一性。

3)使用索引可以加快连接速度。2.建立索引的原则

1)索引的建立和维护由DBA和DBMS完成。

2)大表应当建索引，小表则不必建索引。

3)对于一个基本表，不要建立过多的索引。

4)根据查询要求建索引。

3.建立和删除索引的格式建立格式为：

CREATE[UNIQUE][CLUSTER]INDEX〈索引名〉ON〈表名〉(〈列名〉[〈次序〉][，〈列名〉[〈次序〉]]…)；为学生_课程数据库中的学生、课程和选课三个表建立索引。其中，学生表按学号升序建立索引；课程表按课程号升序建惟一索引；选课表按学号升序和课程号降序建惟一索引。

CREATEUNIQUEINDEXstusnoON学生(学号)；

CREATEUNIQUEINDEXcoursnoON课程(课程号)；

CREATEUNIQUEINDEXscnoON选课(学号ASC，课程号DESC)；

删除索引格式为： DROPINDEX〈索引名〉；删除索引时，系统会从数据字典中删去有关该索引的描述。[例]删除Student表的Stusno索引 DROPINDEXStusno；3.视图的删除、查询和维护视图删除语句的一般格式为：

DROPVIEW〈视图名〉；

视图可以和基本表一样被查询，其使用方法与基本表相同，但利用视图进行数据增、删、改操作，会受到一定的限制。

5.3数据查询语句5.3.1数据查询的基本语法1.SELECT语句的语法SELECT〈目标列组〉FROM〈数据源〉[WHERE〈元组选择条件〉][GROUPBY〈分列组〉[HAVING〈组选择条件〉]][ORDERBY〈排序列1〉〈排序要求1〉[，…n]]；语法说明(1)SELECT子句：指明目标列（字段、表达式、函数表达式、常量）。基本表中相同的列名表示为：〈表名〉.〈列名〉(2)FROM子句：指明数据源。表间用“，”分割。数据源不在当前数据库中，使用“〈数据库名〉.〈表名〉”表示。一表多用，用别名标识。定义表别名：〈表名〉〈别名〉(3)WHERE子句：元组选择条件。(4)GROUPBY子句：结果集分组。当目标列中有统计函数，则统计为分组统计，否则为对整个结果集统计。子句后带上HAVING子句表达组选择条件（带函数的表达式）。(5)ORDERBY子句：排序。当排序要求为ASC时升序排序；排序要求为DESC时降序排列。2.SELECT语句的操作符(1)算术操作符

+（加号）、－（减号）、*（乘号）和/（除号）。(2)比较操作符

=（等于）、>（大于）、<（小于）、<=（小于等于）、>=（大于等于）、!=（不等于）、<>（小于大于）、!>（不大于）和!<（不小于），共9种操作符。

(3)逻辑操作符

语义操作符使用格式或示例在[不在]其中[NOT]IN〈字段〉IN（〈数据表|子查询〉）任何一个ANY〈字段〉〈比较符〉ANY（数据表|子查询）全部(每个)ALL〈字段〉〈比较符〉ALL（数据表|子查询）[不]存在EXISTSEXISTS(〈子查询〉)在[不在]范围BETWEEN…AND…〈字段〉BETWEEN小值AND大值是[不是]空值IS[NOT]NULL〈字段〉IS[NOT]NULL模式比较[NOT]LIKE〈字段〉LIKE〈字符常数〉其中，“_”单字符通配符和“%”多字符通配符与运算AND〈条件1〉AND〈条件2〉或运算OR〈条件1〉OR〈条件2〉非运算NOTNOT〈条件〉(4)组合查询操作符和其他SQL操作符〈查询1〉〈组合操作符〉〈查询2〉

1)UNION：并查询，并在结果集中去掉重复行。

2)MINUS：差查询操作。

3)INTERSECT：交查询操作。

4)*：取全部字段。格式为：*或〈表名〉.*

5)ALL：全部。保留重复值（有统计函数时要求计算重复值）。

格式为：ALL〈字段〉或ALL〈字段组〉

6)DISTINCT：去掉重复值。在结果集中去掉重复值，或在统计函数中不计重复值。

格式为：DISTINCT〈字段〉或DISTINCT〈字段组〉

5.3.2数据查询实例学生课程库结构为：学生(学号，姓名，年龄，所在系)；课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).1.简单查询：查询过程中只涉及到一个表的查询语句。

【例】求数学系学生的学号和姓名。

SELECT学号，姓名

FROM学生

WHERE所在系=‘数学系’；

【例】求选修了课程的学生学号。

SELECTDISTINCT学号

FROM选课；【例】求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。

SELECT学号，成绩

FROM选课

WHERE课程号=‘C1’

ORDERBY成绩DESC，学号ASC；【例】求选修课程C1且成绩在80～90之间的学生学号和成绩，并将成绩乘以系数0.8输出。

SELECT学号，成绩*0.8FROM选课WHERE课程号='C1'AND成绩BETWEEN80AND90；【例】求数学系或计算机系姓张的学生的信息。

SELECT*

FROM学生

WHERE所在系IN(‘数学系’，‘计算机系’)AND姓名LIKE‘张%’；【例】求缺少了成绩的学生的学号和课程号。

SELECT学号，课程号

FROM选课

WHERE成绩ISNULL；

2.连接查询

连接查询中的连接条件通过WHERE子句表达，连接条件和元组选择条件之间用AND（与）操作符衔接。(1)等值连接和非等值连接

[〈表名1〉.]〈列名1〉〈比较运算符〉[〈表名2〉.]〈列名2〉

比较运算符：=、>、<、>=、<=和!=；列名称为连接字段。

【例】查询每个学生的情况以及他(她)所选修的课程。

SELECT学生.*，选课.*

FROM学生，选课

WHERE学生.学号=选课.学号；

【例】求学生的学号、姓名、选修的课程名及成绩。

SELECT学生.学号，姓名，课程名，成绩FROM学生，课程，选课WHERE学生.学号=选课.学号AND课程.课程号=选课.课程号；【例】求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。SELECT学生.学号，姓名，成绩FROM学生，选课WHERE学生.学号=选课.学号AND课程号=‘C1’AND成绩>90；(2)自身连接

例如，课程表中的先行课是在上学期应开设的，先行课的先行课，即间接先行课应提前一学年开设。如果求查询某门课的间接先行课或全部课程的间接先行课，就需要对课程表进行自身连接。课程号课程名先行课C1计算机引论—C2PASCAL语言C1C3数据结构C2C4数据库C3C5软件工程C4课程的先行关系链为：C5→C4→C3→C2→C1，课程的间接关系链为：C5→C3→C1。

课程号课程名先行课

课程号课程名先行课C1计算机引论

C1计算机引论

C2Pascal语言C1C2Pascal语言C1C3数据结构C2C3数据结构C2C4数据库C3C4数据库C3C5软件工程C4C5软件工程C4A.课程号A.课程名B.先行课C2Pascal语言

C3数据结构C1C4数据库C2C5软件工程C3AB结果【例】查询每一门课的间接先行课。

SELECTA.课程号，A.课程名，B.先行课

FROM课程A，课程B

WHEREA.先行课=B.课程号

关系代数:外连接如果把舍弃的元组也保存在结果关系中，而在其他属性上填空值(Null)，这种连接就叫做外连接（OUTERJOIN）。左外连接如果只把左边关系R中要舍弃的元组保留就叫做左外连接(LEFTOUTERJOIN或LEFTJOIN)右外连接如果只把右边关系S中要舍弃的元组保留就叫做右外连接(RIGHTOUTERJOIN或RIGHTJOIN)。loan-numberamountL-170L-230L-260300040001700customer-nameloan-numberJonesSmithHayesL-170L-230L-155branch-nameDowntownRedwoodPerryridge关系loan关系borrower例：内连接

loanBorrowerloanborrower左外连接loan-numberamountL-170L-23030004000customer-nameJonesSmithbranch-nameDowntownRedwoodloan-numberamountL-170L-230L-260300040001700customer-nameJonesSmithnullbranch-nameDowntownRedwoodPerryridge右外连接loan

borrowerloan-numberamountL-170L-230Lullcustomer-nameJonesSmithHayesloan-numberamountL-170L-230L-260L-155300040001700nullcustomer-nameJonesSmithnullHayesloan

borrower全外连接branch-nameDowntownRedwoodnullbranch-nameDowntownRedwoodPerryridgenull(3)外部连接

左外部连接操作是在结果集中保留连接表达式左表中的非匹配记录；右外部连接操作是在结果集中保留连接表达式右表中的非匹配记录。外部连接符号为“*=”，右外部连接符号为“=*”。外部连接中不匹配的分量用NULL表示。职工号姓名性别年龄所在部门

部门号部门名称电话1010李勇男201111生产科5661011刘晨女19

12计划科5781012王敏女221213一车间4671014张立男211314科研所

职工表部门表

连接的结果集

职工号姓名性别年龄所在部门部门名称电话1010李勇男2011生产科5661012王敏女2212计划科5781014张立男2113一车间467职工号姓名性别年龄所在部门部门名称电话1010李勇男2011生产科5661011刘晨女19

1012王敏女2212计划科5781014张立男2113一车间467内连接的结果集

左外部连接的结果集内连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门=部门.部门号；左外部连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门*=部门.部门号；右外部连接：

SELECT职工.*，部门名称，电话

FROM职工，部门

WHERE职工.所在部门=*部门.部门号；用SQL表达职工和部门之间的内连接、左外部连接和右外部连接的语句3.嵌套查询使用IN操作符的嵌套查询

【例5-20】求选修了高等数学的学生学号和姓名。

SELECT学号，姓名FROM学生

WHERE学号IN(SELECT学号FROM选课

WHERE课程号IN(SELECT课程号

FROM课程

WHERE课程名=‘高等数学'))；

该题也可以使用下面的连接查询表达。

SELECT学生.学号，姓名FROM学生，课程，选课

WHERE学生.学号=课程.学号AND课程.课程号=选课.课程号AND课程.课程名='高等数学'；(2)使用比较符的嵌套查询【例】求C1课程的成绩高于张三的学生学号和成绩。

SELECT学号，成绩

FROM选课

WHERE课程号=‘C1’AND成绩>(SELEC成绩FROM选课

WHERE课程号=‘C1’AND学号=(SELECT学号FROM学生WHERE姓名='张三'))；(3)使用ANY或ALL操作符的嵌套查询格式为：〈字段〉〈比较符〉[ANY|ALL]〈子查询〉

操作符语意>ANY大于子查询结果中的某个值，即表示大于查询结果中最小值>ALL大于子查询结果中的所有值，即表示大于查询结果中最大值<ANY小于子查询结果中的某个值，即表示小于查询结果中最大值<ALL小于子查询结果中的所有值，即表示小于查询结果中最小值>=ANY大于等于子查询结果中的某个值，即表示大于等于结果集中最小值>=ALL大于等于子查询结果中的所有值，即表示大于等于结果集中最大值<=ANY小于等于子查询结果中的某个值，即表示小于等于结果集中最大值<=ALL小于等于子查询结果中的所有值，即表示小于等于结果集中最小值=ANY等于子查询结果中的某个值，即相当于IN=ALL等于子查询结果中的所有值(通常没有实际意义)!=(或<>)ANY不等于子查询结果中的某个值，!=(或<>)ALL不等于子查询结果中的任何一个值，即相当于NOTIN【例】求其他系中比计算机系某一学生年龄小的学生。

SELECT*

FROM学生

WHERE年龄<ANY(SELECT年龄FROM学生

WHERE所在系=‘计算机系’)AND所在系<>‘计算机系’；【例】求其他系中比计算机系学生年龄都小的学生。

SELECT*

FROM学生

WHERE年龄<ALL(SELECT年龄FROM学生

WHERE所在系='计算机系')AND所在系<>'计算机系'；(4)使用EXISTS操作符的嵌套查询【例】求选修了C2课程的学生姓名。

SELECT姓名FROM学生

WHEREEXISTS(SELECT*FROM选课

WHERE学生.学号=学号AND课程号='C2')；【例】求没有选修C2课程的学生姓名。

SELECT姓名

FROM学生

WHERENOTEXISTS(SELECT*FROM选课

WHERE学生.学号=学号AND课程号='C2')；【例】查询选修了全部课程的学生的姓名。

SELECT姓名FROM学生

WHERENOTEXISTS (SELECT*FROM课程

WHERENOTEXISTS (SELECT*

FROM选课

WHERE学生.学号=学号AND课程.课程号=课程号))；【例】求至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。SELECT学号，姓名FROM学生WHERENOTEXISTS(SELECT*FROM选课选课1WHERE选课1.学号='S2'ANDNOTEXISTS(SELECT*FROM选课选课2WHERE学生.学号=选课2.学号AND选课2.课程号=选课1.课程号)；不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换用EXISTS/NOTEXISTS实现全称量词(难点)SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：(x)P≡(x(P))

4.组合查询【例】求选修了C1课程或选修了C2课程的学生学号。

SELECT学号FROM选课WHERE课程号=‘C1’

UNION

SELECT学号FROM选课WHERE课程号=‘C2’【例】求选修C1课程，并且也选修C2课程的学生学号。

SELECT学号FROM选课WHERE课程号=‘C1’

INTERSECT

SELECT学号FROM选课WHERE课程号='C2'；【例】求选修了C1课程但没有选修C2课程的学生学号。

SELECT学号FROM选课WHERE课程号=‘C1’

MINUS

SELECT学号FROM选课WHERE课程号=‘C2’；

本例也可以用下面的EXISTS嵌套查询表示。

SELECT学号

FROM选课选课1

WHERE课程号=‘C1’ANDNOTEXISTS

(SELECT学号FROM选课选课2

WHERE选课1.学号=选课2.学号AND选课2.课程号='C2')；

GROUPBY子句分组：细化聚集函数的作用对象未对查询结果分组，聚集函数将作用于整个查询结果对查询结果分组后，聚集函数将分别作用于每个组作用对象是查询的中间结果表按指定的一列或多列值分组，值相等的为一组5.使用分组和SQL函数查询函数功能AVG(〈数值表达式〉)求与字段相关的数值表达式的平均值SUM(〈数值表达式〉)求与字段相关的数值表达式的和值MIN(〈字段表达式〉)求字段表达式的最小值MAX(〈字段表达式〉)求字段表达式的最大值COUNT(*|〈字段〉)求记录行数(*)，或求不是NULL的字段的行数【例】求学生的总人数。

SELECTCOUNT(*)FROM学生；【例】求选修了课程的学生人数。

SELECTCOUNT(DISTINCT学号)

FROM选课；【例】求课程和选修该课程的人数。

SELECT课程号，COUNT(学号)

FROM选课

GROUPBY课程号；【例】求选修课超过3门课的学生学号。

SELECT学号

FROM选课

GROUPBY学号HAVINGCOUNT(*)>3；HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组HAVING短语作用于组，从中选择满足条件的组。5.4数据更新语句5.4.1数据插入语句

1.使用常量插入单个元组

格式为：

INSERT

INTO〈表名〉[(〈属性列1〉[，〈属性列2〉…)]

VALUES(〈常量1〉[，〈常量2〉]…)；【例】将一个新学生记录(学号：‘98010’，姓名：‘张三’，年龄：20，所在系：‘计算机系’)插入到学生表中。

INSERT

INTO学生

VALUES(‘98010’，‘张三’，20，‘计算机系’)；【例】插入一条选课记录(学号：'98011'，课程号：'C10'，成绩不详)。

INSERT

INTO选课(学号，课程号)

VALUES('98011'，'C10')；

2.在表中插入子查询的结果集

INSERT

INTO〈表名〉[(〈属性列1〉[，〈属性列2〉]…)]

〈子查询〉；

【例】求每个系学生的平均年龄，把结果存入数据库中。

CREATETABLE系平均年龄(系名称CHAR(20)，

平均年龄SMALLINT)；

INSERT

INTO系平均年龄

SELECT所在系，AVG(ALL年龄)

FROM学生

GROUPBY所在系；5.4.2数据修改语句UPDATE〈表名〉SET〈列名〉=〈表达式〉[，〈列名〉=〈表达式〉][，…n][WHERE〈条件〉]；

【例】将学生表中全部学生的年龄加上2岁。

UPDATE学生

SET年龄=年龄+2；【例】将选课表中的数据库课程的成绩乘以1.2。

UPDATE选课

SET成绩=成绩*1.2

WHERE课程号=(SELECT课程号

FROM课程

WHERE课程名='数据库')；5.4.3数据删除语句

DELETE

FROM〈表名〉

[WHERE〈条件〉]；

【例】删除艺术系的学生记录及选课记录。

DELETE

FROM选课

WHERE学号IN(SELECT学号

FROM学生

WHERE所在系='艺术系')；

DELETE

FROM学生WHERE所在系='艺术系'；5.5视图视图的特点虚表，是从一个或几个基本表（或视图）导出的表只存放视图的定义，不存放视图对应的数据基表中的数据发生变化，从视图中查询出的数据也随之改变视图基于视图的操作查询删除受限更新定义基于该视图的新视图一、建立视图语句格式

CREATEVIEW

<视图名>[(<列名>[，<列名>]…)]AS<子查询>[WITHCHECKOPTION]；组成视图的属性列名：全部省略或全部指定子查询不允许含有ORDERBY子句和DISTINCT短语建立视图（续）RDBMS执行CREATEVIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。建立视图（续）[例]建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'；建立视图（续）[例]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；建立视图（续）对IS_Student视图的更新操作：修改操作：自动加上Sdept='IS'的条件删除操作：自动加上Sdept='IS'的条件插入操作：自动检查Sdept属性值是否为'IS'如果不是，则拒绝该插入操作如果没有提供Sdept属性值，则自动定义Sdept为'IS'建立视图（续）基于多个基表的视图[例]建立信息系选修了1号课程的学生视图。CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'；建立视图（续）基于视图的视图 [例]建立信息系选修了1号课程且成绩在90分以上的学生的视图。CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；建立视图（续）带表达式的视图[例]定义一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2000-SageFROMStudent；建立视图（续）分组视图[例]将学生的学号及他的平均成绩定义为一个视图 假设SC表中“成绩”列Grade为数字型CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；建立视图（续）不指定属性列[例]将Student表中所有女生记录定义为一个视图CREATEVIEWF_Student(F_Sno，name，sex，age，dept)AS

SELECT*FROMStudentWHERESsex=‘女’；缺点：修改基表Student的结构后，Student表与F_Student视图的映象关系被破坏，导致该视图不能正确工作。二、删除视图语句的格式： DROPVIEW<视图名>；该语句从数据字典中删除指定的视图定义如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除删除基表时，由该基表导出的所有视图定义都必须显式地使用DROPVIEW语句删除删除视图(续）［例］删除视图BT_S：DROPVIEWBT_S；

删除视图IS_S1：DROPVIEWIS_S1；拒绝执行级联删除：DROPVIEWIS_S1CASCADE;查询视图用户角度：查询视图与查询基本表相同RDBMS实现视图查询的方法视图消解法（ViewResolution）进行有效性检查转换成等价的对基本表的查询执行修正后的查询查询视图（续）[例]在信息系学生的视图中找出年龄小于20岁的学生。SELECTSno，SageFROMIS_StudentWHERESage<20；IS_Student视图的定义(参见视图定义例1)查询视图（续）视图消解转换后的查询语句为：SELECTSno，SageFROMStudentWHERESdept='IS'ANDSage<20；查询视图（续）[例]查询选修了1号课程的信息系学生SELECTIS_Student.Sno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno='1'；查询视图（续）视图消解法的局限有些情况下，视图消解法不能生成正确查询。

查询视图（续）[例]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩SELECT*FROMS_GWHEREGavg>=90；S_G视图的子查询定义：CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；查询转换错误：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno；正确：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90；更新视图（续）[例]将信息系学生视图IS_Student中学号200215122的学生姓名改为“刘辰”。UPDATEIS_StudentSETSname='刘辰'WHERESno='200215122'；转换后的语句：UPDATEStudentSETSname='刘辰'WHERESno='200215122'ANDSdept='IS'；更新视图（续）[例]向信息系学生视图IS_S中插入一个新的学生记录：200215129，赵新，20岁INSERTINTOIS_StudentVALUES(‘95029’，‘赵新’，20)；转换为对基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES(‘200215129'，'赵新'，20，'IS')；更新视图（续）[例]删除信息系学生视图IS_Student中学号为200215129的记录DELETEFROMIS_StudentWHERESno='200215129'；转换为对基本表的更新：DELETEFROMStudentWHERESno='200215129'ANDSdept='IS'；更新视图（续）更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新例：视图S_G为不可更新视图。UPDATES_GSETGavg=90WHERESno=‘200215121’；这个对视图的更新无法转换成对基本表SC的更新更新视图（续）允许对行列子集视图进行更新对其他类型视图的更新不同系统有不同限制视图的作用1.视图能够简化用户的操作2.视图使用户能以多种角度看待同一数据3.视图对重构数据库提供了一定程度的逻辑独立性4.视图能够对机密数据提供安全保护5.适当的利用视图可以更清晰的表达查询5.5嵌入式SQL5.5.1嵌入式SQL的特点

1.嵌入式SQL应注意的问题

1)SQL和主语言的配合问题。2)合理选择主语言。

2.SQL嵌入主语言时必须解决的三个问题

(1)如何区别SQL和主语言

(2)使数据库的工作单元与程序工作单元之间能够通信

1)主语言通过主变量向SQL语句提供参数。 2)SQL语句的当前工作状态和运行环境数据要返馈给应用程序。

(3)使用游标解决SQL一次一集合的操作与主语言一次一记录操作的矛盾5.5.2不用游标的SQL语句1.几种不需要使用游标的SQL语句

(1)用于说明主变量的说明性语句

SQL的说明性语句主要有两条：

EXECSQLBEGINDECLARESECTION；

EXECSQLENDDECLARESECTION；

(2)数据定义和数据控制语句

(3)查询结果为单记录的查询语句

(4)数据的插入语句和某些数据删除、修改语句

独立的数据删除和修改语句不需要使用游标；与查询语句配合，删除或修改查询到的当前记录的操作，与游标有关。2.不用游标的查询语句EXECSQLSELECT[ALL|DISTINCT]〈目标列表达式〉[，…n]

INTO〈主变量〉[〈指示变量〉][，…n]

FROM〈表名或视图名〉[，…n][WHERE〈条件表达式〉]；

1)在语句开始前要加EXECSQL前缀。

2)该查询语句中又扩充了INTO子句。

3)在WHERE子句的条件表达式中可以使用主变量。

4)由于查询的结果集中只有一条记录，该语句中不必有排序和分组子句。

5)INTO子句中的