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

第五章关系数据库标准语言——SQL第五章关系数据库标准语言——SQL本章教学目标、重点和难点

本章教学目标：使学生了解SQL的功能和语言特点；熟练掌握用SQL进行表的定义、数据查询和数据更新，掌握SQL的数据控制方法。2.教学重点：SQL的数据定义、数据查询、数据维护和数据控制表示方法3.教学难点：用SQL实现嵌套查询、组合查询、使用分组和SQL函数查询。本章教学目标、重点和难点本章教学目标：使学生了解SQL的功本章内容SQL的功能及特点数据定义语句数据查询语句数据更新语句嵌入式SQL数据控制机制和语句本章内容SQL的功能及特点SQL的功能及特点19741975-19791986-198719921999由Boyce和Chamberlin提出IBM公司研制了关系DBMS原型，并实现该语言美国批准SQL作为美国标准，随后ISO也通过这一标准公布了SQL-92标准公布了SQL-99，也称作SQL3SQL的功能及特点19741975-19791986-198SQL的功能及特点数据定义数据控制数据操纵SQL语句分类SQL的功能及特点数据定义数据控制数据操纵SQL语句分类SQL的功能及特点SQL语句分类数据定义数据操纵数据控制定义关系数据库的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。包括数据查询和数据更新两种数据操作语句：数据查询指对数据库中的数据查询、统计、分组、排序操作；数据更新指数据的插入、删除、修改等数据维护操作。通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。SQL的功能及特点SQL语句分类数据定义数据操纵数据控制定义SQL的功能及特点SQL的特点1）SQL具有自主式语言和嵌入式语言两种使用方式；2）SQL具有语言简洁、易学易用的特点；SQL功能命令动词数据定义（数据模式定义、删除、修改）Create、Drop、Alter数据操纵（数据查询和维护）Select、Insert、Update、Delete数据控制（数据存取控制权和收权）Grant、RemoveSQL的功能及特点SQL的特点SQL功能命令动词数据定义（数SQL的功能及特点SQL的特点3）SQL支持三级模式结构存储文件1存储文件2基本表1基本表2基本表3基本表4视图1视图2SQL外模式模式内模式SQL的功能及特点SQL的特点存储文件1存储文件2基本表1基数据定义语句操作对象创建语句删除语句修改语句基本表CreatetableDroptableAltertable索引CreateindexDropindex视图CreateviewDropview数据库CreatedatabaseDropdatabaseAlterdatabase1.语句格式约定符号尖括号<>中括号[]大括号{}或分隔符|[,…n]实际语义任选项必选项，必选其中一项前面的项可重复多次数据定义语句操作对象创建语句删除语句修改语句基本表Creat数据定义语句2.一般语法规定SQL中的数据项（包括列项、表和视图）分隔符为“,”，其字符串常数的定界符用单引号“’”表示3.SQL特殊语法规定SQL的关键词一般使用大写字母表示；语句的结束符为“;”语句一般应采用格式化书写方式数据定义语句2.一般语法规定数据定义语句基本表的定义和维护1.基本表的定义定义基本表语句的一般格式为：CREATETABLE[〈库名〉]〈表名〉(〈列名〉〈数据类型〉[〈列级完整性约束条件〉][，〈列名〉〈数据类型〉[〈列级完整性约束条件〉]][，…n]

[，〈表级完整性约束条件〉][，…n])；数据定义语句基本表的定义和维护定义基本表语句的一般格式为：数据定义语句建立基本表：学生(学号，姓名，年龄，性别，所在系)；

课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).CREATETABLE课程(课程号CHAR(5)PRIMARYKEY，

课程名CHAR(20)，

先行课CHAR(5)

)；数据定义语句建立基本表：CREATETABLE课程(课数据定义语句（1）SQL支持的数据类型类型表示类型说明数值型数据SMALLINT半字长二进制证书，15bit数据INTEGER或INT全字长（4字长）整数，31bit数据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支持的数据类型类型表示类型说明数值型数据定义语句（2）列级完整性的约束条件1）NOTNULL或NULL约束NOTNULL约束不允许字段值为空而NULL约束允许字段值为空。2）UNIQUE约束UNIQUE约束是惟一性约束，即不允许列中出现重复的属性值。3）DEFAULT约束DEFAULT为默认值约束。DEFAULT〈约束名〉〈默认值〉FOR〈列名〉4）CHECK约束CHECK为检查约束。

CONSTRAINT〈约束名〉CHECK(〈约束条件表达式〉)数据定义语句（2）列级完整性的约束条件数据定义语句建立基本表：学生(学号，姓名，年龄，性别，所在系)；

课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).CREATETABLE学生(学号CHAR(5)NOTNULLUNIQUE，

姓名CHAR(8)NOTNULL，

年龄SMALLINT，

性别CHAR(2)，

所在系CHAR(20)，

DEFAULTC120FOR年龄，

CONSTRAINTC2CHECK(性别IN('男'，'女')))；

数据定义语句建立基本表：CREATETABLE学生(学数据定义语句（3）表级完整性的约束条件1）UNIQUE约束UNIQUE约束是惟一性约束，当要求列祖的值不能有重复值时，使用UNIQUE约束定义。2）PRIMARYKEY约束定义主码，保证惟一性和非空性。CONTRAINT〈约束名〉PRIMARYKEY[CLUSTERED](〈列组〉)3）FOREIGNKEY约束用于定义参照完整性。

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

REFERENCES〈被参照表名〉(〈与外码对应的主码名〉)数据定义语句（3）表级完整性的约束条件数据定义语句建立基本表：学生(学号，姓名，年龄，性别，所在系)；

课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).CREATETABLE选课(学号CHAR(5)，

课程号CHAR(5)，

成绩SMALLINT，

CONSTRAINTC3CHECK(成绩BETWEEN0AND100)，

CONSTRAINTC4PRIMARYKEY(学号，课程号)，

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

CONSTRAINTC6FOREIGNKEY(课程号)REFERENCES课程(课程号))；数据定义语句建立基本表：CREATETABLE选课(学数据定义语句2.修改基本表ALTERTABLE〈表名〉

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

[DROP〈完整性约束名〉]

[MODIFY(〈列名〉〈数据类型〉[，…n])]；使用ADD子句增加新列

【例5-2】向课程表中增加“学时”字段。

ALTERTABLE课程ADD学时SMALLINT；使用MODIFY子句修改列的原定义：MODIFY子句主要用于加宽原列的宽度使用DROP子句删除指定的完整性约束条件

【例5-3】删除学生表中对年龄的默认值的定义。

ALTERTABLE学生DROPC1；数据定义语句2.修改基本表ALTERTABLE〈表名〉

数据定义语句3.删除基本表删除基本表的一般格式为：DROPTABLE〈表名〉；数据定义语句3.删除基本表删除基本表的一般格式为：DROP数据定义语句索引的定义和维护1.索引的作用使用索引可以明显地加快数据查询的速度；先将索引文件读入内存，根据索引项找到元组的地址，然后再根据地址将元组数据直接读入计算机。由于索引文件中只含有索引项和元组地址，所以文件小，一般可一次读入内存索引文件中的索引项是经过排序的，可以很快地找到索引项值和元组地址数据定义语句索引的定义和维护使用索引可以明显地加快数据查询的数据定义语句索引的定义和维护1.索引的作用使用索引可以明显地加快数据查询的速度；

使用索引可保证数据的惟一性；使用索引可以加快连接速度。2.建立索引的原则索引的建立和维护由DBA和DBMS完成；大表应当建索引，小表则不必建索引；对于一个基本表，不要建立过多的索引；根据查询要求建索引。数据定义语句索引的定义和维护使用索引可以明显地加快数据查询的数据定义语句3.建立索引的格式建立格式为：

CREATE[UNIQUE][CLUSTER]INDEX〈索引名〉ON〈表名〉(〈列名〉[〈次序〉][，〈列名〉[〈次序〉]]…)；<表名>是要建索引的基本表的名字；每个<列名>后面还可以用<次序>指定索引值的排列次序，次序可选ASC（升序）或DESC（降序），默认值为ASC；UNIQUE表示该索引的每一个索引值只对应唯一的数据记录；CLUSTER表示要建立的索引是聚簇索引，使基本表中数据的物理顺序与索引项的排列顺序一致。数据定义语句3.建立索引的格式建立格式为：

CREATE[数据定义语句3.建立索引的格式【例5-4】为学生_课程数据库中的学生、课程和选课三个表建立索引。其中，学生表按学号升序建立索引；课程表按课程号升序建惟一索引；选课表按学号升序和课程号降序建惟一索引。

CREATEUNIQUEINDEX学号ON学生(学号)；

CREATEUNIQUEINDEX课程号ON课程(课程号)；

CREATEUNIQUEINDEX选课号ON选课(学号ASC，课程号DESC)；数据定义语句3.建立索引的格式【例5-4】为学生_课程数数据定义语句4.删除索引删除索引格式为：DROPINDEX〈索引名〉；例：删除学生表的学生姓名索引DROPINDEX学生姓名数据定义语句4.删除索引删除索引格式为：DROPI数据定义语句视图的定义和维护1.视图的优点视图是根据子模式设计的关系，由一个或几个基本表导出的虚表。视图能够简化用户的操作；视图机制可以减少用户对数据库中数据结构的调整操作，使用户将注意力集中在所关心的数据上。2）视图机制可以使用户以不同的方式看待同一数据；当多用户共享同一个数据库的数据时，通过视图机制可以实现各个用户对数据的不同使用要求。3）视图可以对机密的数据提供安全保护数据定义语句视图的定义和维护视图能够简化用户的操作；数据定义语句1.视图的优点4）视图对数据库的重构提供了一定程度的逻辑独立性；在关系数据库中，数据库的重构往往是不可避免的。但由于视图机制，将为应用程序提供了原来使用的关系，保持外模式。原表：学生（学号，姓名，性别，年龄，所在系）分成：SX（学号，姓名，年龄）SY（学号，性别，所在系）CREATEVIEW学生(学号，姓名，性别，年龄，所在系）ASSELECTSX.学号，SX.姓名，SX.性别，SY.年龄，SY.所在系FROMSX,SYWHERESX.学号=SY.学号；数据定义语句1.视图的优点4）视图对数据库的重构提供了一定程数据定义语句2.视图定义的格式一般格式为：CREATEVIEW〈视图名〉[(列名组)]AS〈子查询〉

[WITHCHECKOPTION]；（1）WITHCHECKOPTION选项表示在对视图进行UPDATE、INSERT和DELETE操作时，要保证操作的数据满足视图定义中的谓词条件。该谓词条件是视图子查询中的WHERE子句的条件。数据定义语句2.视图定义的格式一般格式为：（1）WITHC数据定义语句2.视图定义的格式一般格式为：CREATEVIEW〈视图名〉[(列名组)]AS〈子查询〉

[WITHCHECKOPTION]；（2）组成视图的属性列名全部省略或者全部指定若省略了视图的各个属性列名，则由子查询中的SELECT子句的目标列代替必须明确指定列名的情况①某个目标列是集函数或列表达式；②子查询中使用多个表（或视图），并且目标列中含有相同的属性名；③需要在视图中改用新的、更合适的列名。数据定义语句2.视图定义的格式一般格式为：（2）组成视图的属数据定义语句2.视图定义的格式【例5-6】建立计算机系学生的视图。

CREATEVIEW计算机系学生

ASSELECT学号，姓名，年龄

FROM学生

WHERE所在系=‘计算机系’；数据定义语句2.视图定义的格式【例5-6】建立计算机系学生的数据定义语句2.视图定义的格式【例5-7】由学生、课程和选课三个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩CREATEVIEW学生成绩(学号，姓名，课程名，成绩)ASSELECT学生.学号，学生.姓名，课程.课程名，选课.成绩

FORM学生，课程，选课

WHERE学生.学号=选课.学号AND程.课程名=选课.课程号

AND学生.所在系=‘计算机系’；数据定义语句2.视图定义的格式【例5-7】由学生、课程和选课数据定义语句2.视图定义的格式【例5-8】将学生的学号、总成绩、平均成绩定义成一个视图。CREATEVIEW学生成绩统计(学号，总成绩，平均成绩)ASSELECT学号，SUM（成绩），AVG（成绩）

FORM选课

GROUPBY学号；3.视图的删除DROPVIEW<视图名>数据定义语句2.视图定义的格式【例5-8】将学生的学号、总成数据定义语句4.视图的查询和维护视图可以和基本表一样被查询，其使用方法与基本表相同，但利用视图进行数据增、删、改操作，会受到一定的限制。一般的数据库系统不支持对下列几种情况的视图进行数据更新操作。1）由两个以上基本表导出的视图2）视图的字段来自字段表达式函数3）视图中有分组子句或使用了DISTINCT短语4）视图定义中有嵌套查询，且内层查询中涉及了与外层一样的导出该视图的基本表5）在一个不允许更新的视图上定义的视图数据定义语句4.视图的查询和维护视图可以和基本表一样被查询，数据查询语句数据查询的基本语法1.SELECT语句的语法SELECT〈目标列组〉FROM〈数据源〉[WHERE〈元组选择条件〉][GROUPBY〈分列组〉[HAVING〈组选择条件〉]][ORDERBY〈排序列1〉〈排序要求1〉[，…n]]；数据查询语句数据查询的基本语法SELECT〈目标列组〉数据查询语句1.SELECT语句的语法SELECT子句：指明目标列（字段、表达式、函数表达式、常量）。基本表中相同的列名表示为：〈表名〉.〈列名〉FROM子句：指明数据源。表间用“，”分割。数据源不在当前数据库中，使用“〈数据库名〉.〈表名〉”表示。一表多用，用别名标识。定义表别名：〈表名〉.〈别名〉WHERE子句：元组选择条件。数据查询语句1.SELECT语句的语法SELECT子句：指明数据查询语句1.SELECT语句的语法学生课程库结构为：学生(学号，姓名，年龄，所在系)；

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

选课(学号，课程号，成绩).【例5-9】求数学系学生的学号和姓名。

SELECT学号，姓名

FROM学生

WHERE所在系=‘数学系’；【例5-10】求选修了课程的学生学号。

SELECTDISTINCT学号

FROM选课；数据查询语句1.SELECT语句的语法学生课程库结构为：学生数据查询语句1.SELECT语句的语法(4)GROUPBY子句：结果集分组。当目标列中有统计函数，则统计为分组统计，否则为对整个结果集统计。子句后带上HAVING子句表达组选择条件（带函数的表达式）。(5)ORDERBY子句：排序。当排序要求为ASC时升序排序；排序要求为DESC时降序排列。【例5-11】求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列。

SELECT学号，成绩

FROM选课

WHERE课程号=‘C1’

ORDERBY成绩DESC，学号ASC；数据查询语句1.SELECT语句的语法(4)GROUPB数据查询语句2.SELECT语句的操作符(1)算术操作符

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

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

数据查询语句2.SELECT语句的操作符(1)算术操作符

数据查询语句语义操作符使用格式或示例在[不在]其中[NOT]IN〈字段〉IN（〈数据表|子查询〉）任何一个ANY〈字段〉〈比较符〉ANY（数据表|子查询）例：〈字段〉>ANY（数据表|子查询）全部(每个)ALL〈字段〉〈比较符〉ALL（数据表|子查询）例：〈字段〉>ALL（数据表|子查询）[不]存在EXISTSEXISTS(〈子查询〉)在[不在]范围[NOT]BETWEEN…AND…〈字段〉[NOT]BETWEEN小值AND大值是[不是]空值IS[NOT]NULL〈字段〉IS[NOT]NULL模式比较[NOT]LIKE〈字段〉[NOT]LIKE〈字符常数〉其中，“_”单字符通配符和“%”多字符通配符与运算AND〈条件1〉AND〈条件2〉或运算OR〈条件1〉OR〈条件2〉非运算NOTNOT〈条件〉数据查询语句语义操作符使用格式或示例在[不在]其中[NOT]数据查询语句(4)组合查询操作符SQL的组合查询操作符是针对传统关系运算的操作符，包括UNION（并查询）、MINUS（差查询）和INTERSECT（交查询）其格式为：<查询1><组合操作符><查询2>1）UNION：并查询，并在结果集中去掉重复行。2)MINUS：差查询操作。

3)INTERSECT：交查询操作。

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

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

格式为：ALL〈字段〉或

ALL〈字段组〉

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

格式为：DISTINCT〈字段〉或

DISTINCT〈字段组〉数据查询语句(4)组合查询操作符数据查询语句【例5-12】求选修课程C1且成绩在80～90之间的学生学号和成绩，并将成绩乘以系数0.8输出。

SELECT学号，成绩*0.8

FROM选课

WHERE课程号=‘C1’AND

成绩BETWEEN80AND90；【例5-13】求数学系或计算机系姓张的学生的信息。

SELECT*

FROM学生

WHERE所在系IN(‘数学系’，‘计算机系’)AND姓名LIKE‘张%’；1.简单查询实例数据查询语句【例5-12】求选修课程C1且成绩在80～90之数据查询语句【例5-14】求缺少了成绩的学生的学号和课程号。

SELECT学号，课程号

FROM选课

WHERE成绩ISNULL；数据查询语句【例5-14】求缺少了成绩的学生的学号和课程号。数据查询语句2.连接查询实例连接查询中的连接条件通过WHERE子句表达，连接条件和元组选择条件之间用AND（与）操作符衔接(1)等值连接和非等值连接

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

比较运算符：=、>、<、>=、<=和!=；列名称为连接字段。【例5-15】查询每个学生的情况以及他(她)所选修的课程。

SELECT学生.*，选课.*

FROM学生，选课

WHERE学生.学号=选课.学号；数据查询语句2.连接查询实例(1)等值连接和非等值连接

数据查询语句【例5-16】求学生的学号、姓名、选修的课程名及成绩。

SELECT学生.学号，姓名，课程名，成绩

FROM学生，课程，选课

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

AND课程.课程号=选课.课程号；【例5-17】求选修C1课程且成绩为90分以上的学生学号、姓名及成绩。

SELECT学生.学号，姓名，成绩

FROM学生，选课

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

AND课程号=‘C1’AND成绩>90；数据查询语句【例5-16】求学生的学号、姓名、选修的课程名及数据查询语句(2)自身连接操作

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

例如，课程表中的先行课是在数据查询语句(2)自身连接操作【例5-18】查询每一门课的间接先行课。

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

FROM课程A，课程B

WHEREA.先行课=B.课程号

课程号课程名先行课

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

C1计算机引论

C2Pascal语言C1C2Pascal语言C1C3数据结构C2C3数据结构C2C4数据库C3C4数据库C3C5软件工程C4C5软件工程C4数据查询语句(2)自身连接操作【例5-18】查询每一门课的数据查询语句(2)自身连接操作【例5-18】查询每一门课的间接先行课。

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

FROM课程A，课程B

WHEREA.先行课=B.课程号

A.课程号A.课程名B.先行课C2Pascal语言

C3数据结构C1C4数据库C2C5软件工程C3数据查询语句(2)自身连接操作【例5-18】查询每一门课的数据查询语句(3)外部连接操作左外部连接操作是在结果集中保留连接表达式左表中的非匹配记录；右外部连接操作是在结果集中保留连接表达式右表中的非匹配记录。外部连接符号为“*=”，右外部连接符号为“=*”。外部连接中不匹配的分量用NULL表示。职工号姓名性别年龄所在部门

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

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

数据查询语句(3)外部连接操作左外部连接操作是在结数据查询语句(3)外部连接操作职工号姓名性别年龄所在部门部门名称电话1010李勇男2011生产科5661012王敏女2212计划科5781014张立男2113一车间467内连接的结果集数据查询语句(3)外部连接操作职工号姓名性别年龄所在部门部数据查询语句(3)外部连接操作左连接的结果集职工号姓名性别年龄所在部门部门名称电话1010李勇男2011生产科5661011刘晨女19

1012王敏女2212计划科5781014张立男2113一车间467数据查询语句(3)外部连接操作左连接的结果集职工号姓名性别数据查询语句(3)外部连接操作右连接的结果集职工号姓名性别年龄所在部门部门名称电话1010李勇男2011生产科5661012王敏女2212计划科5781014张立男2113一车间467科研所数据查询语句(3)外部连接操作右连接的结果集职工号姓名性别数据查询语句(3)外部连接操作【例5-19】用SQL表达职工和部门之间的内连接、左外部连接和右外部连接的语句内连接：

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

FROM职工，部门

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

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

FROM职工，部门

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

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

FROM职工，部门

WHERE职工.所在部门=*部门.部门号；数据查询语句(3)外部连接操作【例5-19】用SQL表达职数据查询语句3.嵌套查询在SQL语言中，一个SELECT…FROM…WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。(1)使用IN操作符的嵌套查询

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

SELECT学号，姓名

FROM学生

WHERE学号IN(SELECT学号

FROM选课

WHERE课程号IN(SELECT课程号

FROM课程

WHERE课程名=‘高等数学’))；数据查询语句3.嵌套查询在SQL语言中，一个SELECT…F数据查询语句3.嵌套查询在SQL语言中，一个SELECT…FROM…WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。(1)使用IN操作符的嵌套查询

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

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

SELECT学生.学号，姓名

FROM学生，课程，选课

WHERE学生.学号=课程.学号AND课程.课程号=选课.课程号

AND课程.课程名=‘高等数学’；数据查询语句3.嵌套查询在SQL语言中，一个SELECT…F数据查询语句3.嵌套查询(2)使用比较符的嵌套查询【例5-21】求C1课程的成绩高于张三的学生学号和成绩。

SELECT学号，成绩

FROM选课

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

FROM选课

WHERE课程号=‘C1’AND学号=

(SELECT学号

FROM学生

WHERE姓名='张三'))；数据查询语句3.嵌套查询(2)使用比较符的嵌套查询数据查询语句(3)使用ANY或ALL操作符的嵌套查询其格式为：<字段><比较符>[ANY|ALL]<子查询>操作符语意>ANY大于子查询结果中的某个值，即表示大于查询结果中最小值>ALL大于子查询结果中的所有值，即表示大于查询结果中最大值<ANY小于子查询结果中的某个值，即表示小于查询结果中最大值<ALL小于子查询结果中的所有值，即表示小于查询结果中最小值>=ANY大于等于子查询结果中的某个值，即表示大于等于结果集中最小值>=ALL大于等于子查询结果中的所有值，即表示大于等于结果集中最大值数据查询语句(3)使用ANY或ALL操作符的嵌套查询操作符数据查询语句(3)使用ANY或ALL操作符的嵌套查询其格式为：<字段><比较符>[ANY|ALL]<子查询>操作符语意<=ANY小于等于子查询结果中的某个值，即表示小于等于结果集中最大值<=ALL小于等于子查询结果中的所有值，即表示小于等于结果集中最小值=ANY等于子查询结果中的某个值，即相当于IN=ALL等于子查询结果中的所有值(通常没有实际意义)!=(或<>)ANY不等于子查询结果中的某个值，!=(或<>)ALL不等于子查询结果中的任何一个值，即相当于NOTIN数据查询语句(3)使用ANY或ALL操作符的嵌套查询操作符数据查询语句(3)使用ANY或ALL操作符的嵌套查询其格式为：<字段><比较符>[ANY|ALL]<子查询>【例5-22】求其他系中比计算机系某一学生年龄小的学生。

SELECT*

FROM学生

WHERE年龄<ANY(SELECT年龄

FROM学生

WHERE所在系=‘计算机系’)AND所在系<>‘计算机系’；数据查询语句(3)使用ANY或ALL操作符的嵌套查询【例5数据查询语句(3)使用ANY或ALL操作符的嵌套查询其格式为：<字段><比较符>[ANY|ALL]<子查询>【例5-23】求其他系中比计算机系学生年龄都小的学生。

SELECT*

FROM学生

WHERE年龄<ALL(SELECT年龄

FROM学生

WHERE所在系='计算机系')AND所在系<>'计算机系'；数据查询语句(3)使用ANY或ALL操作符的嵌套查询【例5数据查询语句(4)使用EXISTS操作符的嵌套查询【例5-24】求选修了C2课程的学生姓名。

SELECT姓名

FROM学生

WHEREEXISTS(SELECT*

FROM选课

WHERE学生.学号=学号AND课程号='C2')；Select姓名From学生，选课Where学生.学号=选课.学号and课程号=‘c2’数据查询语句(4)使用EXISTS操作符的嵌套查询【例5-数据查询语句(4)使用EXISTS操作符的嵌套查询【例5-25】求没有选修C2课程的学生姓名。

SELECT姓名

FROM学生

WHERENOTEXISTS(SELECT*

FROM选课

WHERE学生.学号=学号AND课程号=‘C2’)；不能写成以下：Select姓名From学生，选课Where学生.学号=选课.学号and课程号<>‘c2’数据查询语句(4)使用EXISTS操作符的嵌套查询【例5-数据查询语句(4)使用EXISTS操作符的嵌套查询【例5-26】查询选修了全部课程的学生的姓名。

SELECT姓名

FROM学生

WHERENOTEXISTS(SELECT*

FROM课程

WHERENOTEXISTS

(SELECT*

FROM选课

WHERE学生.学号=学号AND

课程.课程号=课程号))；由于SQL没有全称量词的操作符，该题转换为查询这样的学生：没有一门课他不选修。数据查询语句(4)使用EXISTS操作符的嵌套查询【例5-数据查询语句(4)使用EXISTS操作符的嵌套查询【例5-27】求至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。

SELECT学号，姓名

FROM学生

WHERENOTEXISTS(SELECT*FROM选课选课1WHERE选课1.学号='S2'ANDNOTEXISTS(SELECT*FROM选课选课2WHERE学生.学号=选课2.学号

AND选课2.课程号=选课1.课程号)；数据查询语句(4)使用EXISTS操作符的嵌套查询【例5-数据查询语句4.组合查询Union并操作，Intersect交操作，MINUS差操作【例5-28】求选修了C1课程或选修了C2课程的学生学号。

SELECT学号

FROM选课

WHERE课程号=‘C1’

UNION

SELECT学号

FROM选课

WHERE课程号=‘C2’；Select学号From选课Where课程号=‘C1’or课程号=‘C2’数据查询语句4.组合查询【例5-28】求选修了C1课程或选修数据查询语句4.组合查询【例5-29】求选修C1课程，并且也选修C2课程的学生学号。

SELECT学号

FROM选课

WHERE课程号='C1'

INTERSECT

SELECT学号

FROM选课

WHERE课程号='C2'；数据查询语句4.组合查询【例5-29】求选修C1课程，并且也数据查询语句4.组合查询【例5-30】求选修了C1课程但没有选修C2课程的学生学号。SELECT学号

FROM选课

WHERE课程号='C1'

MINUS

SELECT学号

FROM选课

WHERE课程号='C2'；数据查询语句4.组合查询【例5-30】求选修了C1课程但没有数据查询语句4.组合查询【例5-30】求选修了C1课程但没有选修C2课程的学生学号。本例也可以用下面的EXISTS嵌套查询表示。

SELECT学号

FROM选课选课1

WHERE课程号='C1'ANDNOTEXISTS

(SELECT学号

FROM选课选课2

WHERE选课1.学号=选课2.学号AND选课2.课程号='C2')；数据查询语句4.组合查询【例5-30】求选修了C1课程但没有数据查询语句5.使用分组和SQL函数查询函数功能AVG(〈数值表达式〉)求与字段相关的数值表达式的平均值SUM(〈数值表达式〉)求与字段相关的数值表达式的和值MIN(〈字段表达式〉)求字段表达式的最小值MAX(〈字段表达式〉)求字段表达式的最大值COUNT(*|〈字段〉)求记录行数(*)，或求不是NULL的字段的行数数据查询语句5.使用分组和SQL函数查询函数功能AVG(〈数数据查询语句5.使用分组和SQL函数查询【例5-31】求学生的总人数。

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

SELECTCOUNT(DISTINCT学号)

FROM选课；数据查询语句5.使用分组和SQL函数查询【例5-31】求学生数据查询语句5.使用分组和SQL函数查询【例5-33】求课程和选修该课程的人数。

SELECT课程号，COUNT(学号)

FROM选课

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

SELECT学号

FROM选课

GROUPBY学号HAVINGCOUNT(*)>3；数据查询语句5.使用分组和SQL函数查询【例5-33】求课程数据更新语句SQL的数据插入功能1.使用常量插入单个元组格式为：

INSERT

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

VALUES(〈常量1〉[，〈常量2〉]…)；数据更新语句SQL的数据插入功能格式为：

INSER数据更新语句SQL的数据插入功能1.使用常量插入单个元组【例5-35】将一个新学生记录(学号：‘98010’，姓名：‘张三’，年龄：20，所在系：‘计算机系’)插入到学生表中。

INSERT

INTO学生

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

INSERT

INTO选课(学号，课程号)

VALUES('98011'，'C10')；数据更新语句SQL的数据插入功能【例5-35】将一个新学生记数据更新语句2.在表中插入子查询的结果集INSERT

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

〈子查询〉；【例5-37】求每个系学生的平均年龄，并把结果存入数据库中。

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

平均年龄SMALLINT)；

INSERT

INTO系平均年龄

SELECT所在系，AVG(ALL年龄)

FROM学生

GROUPBY所在系；数据更新语句2.在表中插入子查询的结果集【例5-37】求每个数据更新语句3.SQL的数据修改功能UPDATE〈表名〉

SET〈列名〉=〈表达式〉[，〈列名〉=〈表达式〉][，…n]

[WHERE〈条件〉]；【例5-38】将学生表中全部学生的年龄加上2岁。

UPDATE学生

SET年龄=年龄+2；

【例5-39】将选课表中的数据库课程的成绩乘以1.2。

UPDATE选课

SET成绩=成绩*1.2

WHERE课程号=(SELECT课程号

FROM课程

WHERE课程名='数据库')；数据更新语句3.SQL的数据修改功能【例5-38】将学生表中数据更新语句3.SQL的数据删除功能DELETE

FROM〈表名〉

[WHERE〈条件〉]；【例5-40】删除艺术系的学生记录及选课记录。

DELETE

FROM选课

WHERE学号IN(SELECT学号

FROM学生

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

DELETE

FROM学生

WHERE所在系='艺术系'；数据更新语句3.SQL的数据删除功能【例5-40】删除艺术系嵌入式SQL嵌入式SQL的特点1.嵌入式SQL应注意的问题

1)SQL和主语言的配合问题。

2)合理选择主语言。

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

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

在所有的SQL语句前面加上前缀EXECSQL；SQL语句的结束标志随主语言的不同而不同嵌入式SQL嵌入式SQL的特点1.嵌入式SQL应注意的问题嵌入式SQL嵌入式SQL的特点2.SQL嵌入主语言时必须解决的三个问题

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

1)主语言通过主变量向SQL语句提供参数。

2)SQL语句的当前工作状态和运行环境数据要返馈给应用程序。

(3)使用游标解决SQL一次一集合的操作与主语言一次一记录操作的矛盾

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。嵌入式SQL嵌入式SQL的特点2.SQL嵌入主语言时必须解嵌入式SQL不用游标的SQL语句1.几种不需要使用游标的SQL语句

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

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

EXECSQLBEGINDECLARESECTION；

EXECSQLENDDECLARESECTION；

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

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

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

独立的数据删除和修改语句不需要使用游标；与查询语句配合，删除或修改查询到的当前记录的操作，与游标有关。

嵌入式SQL不用游标的SQL语句1.几种不需要使用游标的S嵌入式SQL2.不用游标的查询语句

EXECSQLSELECT[ALL|DISTINCT]〈目标列表达式〉[，…n]

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

FROM〈表名或视图名〉[，…n]

[WHERE〈条件表达式〉]；

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

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

3)在WHERE子句的条件表达式中可以使用主变量。嵌入式SQL2.不用游标的查询语句

EXECSQLSE嵌入式SQL2.不用游标的查询语句

EXECSQLSELECT[ALL|DISTINCT]〈目标列表达式〉[，…n]

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

FROM〈表名或视图名〉[，…n]

[WHERE〈条件表达式〉]；

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

5)INTO子句中的主变量后面跟有指示变量时如果查询结果列值为NULL，指示变量为负值，结果列不向该主变量赋值；如果传递正常，指示变量的值为0；如果主变量宽度不够，则指示变量的值为数据截断前的宽度。

6)如果查询结果并不是单条记录，则程序出错。嵌入式SQL2.不用游标的查询语句

EXECSQLSE嵌入式SQL2.不用游标的查询语句

EXECSQLSELECT[ALL|DISTINCT]〈目标列表达式〉[，…n]

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

FROM〈表名或视图名〉[，…n]

[WHERE〈条件表达式〉]；

【例5-41】查询学号为主变量givesno、课号为主变量givecno的值的学生选课记录。

EXECSQLSELECT学号，课程号，成绩

INTO:Sno，:Cno，:grade:gradeid

FROM选课

WHERE学号=:givesnoAND课程号=:givecno；嵌入式SQL2.不用游标的查询语句

EXECSQLSE嵌入式SQL2.不用游标的数据维护语句

(1)不用游标的数据删除语句

【例5-42】删除学号由主变量Sno决定的学生记录。

EXECSQLDELETE

FROM学生

WHERE学号=:Sno；嵌入式SQL2.不用游标的数据维护语句

(1)不用游标嵌入式SQL2.不用游标的数据维护语句

(2)不用游标的数据维护语句

【例5-43】将计算机系所有学生的年龄都加上主变量Raise。

EXECSQLUPDATE学生

SET年龄=年龄+:Raise

WHERE所在系=‘计算机系’；【例5-44】将计算机系学生的年龄置空。

Raiseid=-1EXECSQLUPDATE学生

SET年龄=年龄+:Raise:Raiseid；嵌入式SQL2.不用游标的数据维护语句

(2)不用游标嵌入式SQL2.不用游标的数据维护语句

(3)不用游标的数据插入语句

【例5-45】将学号为主变量Sno、课程号为Cno的选课记录，插到库中。

EXECSQLINSERTINTO选课

VALUES(:Sno，:Cno)；嵌入式SQL2.不用游标的数据维护语句

(3)不用游标嵌入式SQL使用游标的SQL游标机制用于解决SQL查询结果为集合而主语言处理方式为记录方式的矛盾。在处理中，必须使用游标的SQL语句有两种：一种是查询结果为多条记录的SELECT语句，另一种是使用游标的DELETE语句和UPDATE语句。嵌入式SQL使用游标的SQL游标机制用于解决SQL查嵌入式SQL1.游标的4个命令(1)定义游标命令

EXECSQLDECLARE〈游标名〉CURSOR

FOR〈子查询〉

[FORUPDATEOF〈字段名1〉[，…n]]；

删除和修改数据的语句中，WHERE为：

WHERECURRENTOF〈游标名〉

【例5-46】定义按主变量DEPT查询系里学生的游标。

EXECSQLDECLARESXCURSORFOR

SELECT*

FROM学生

WHERE所在系=:DEPT；嵌入式SQL1.游标的4个命令嵌入式SQL(2)打开游标命令

EXECSQLOPEN〈游标名〉；

执行对应的查询语句，并将游标指向结果集的第一条记录前。打开的游标处于活动状态，可以被推进。【例5-47】打开SX游标。

DEPT='计算机系'

EXECSQLOPENSX；嵌入式SQL(2)打开游标命令

EXECSQLOPE嵌入式SQL(3)推进游标命令

EXECSQLFETCH〈游标名〉INTO〈主变量组〉；

将游标下移一行，读出当前的记录，将当前记录的各数据项值放到INTO后的主变量组中。【例5-48】将打开的指向系的游标向前推进。

EXECSQLFETCHSX

INTO:Sno，:Sname，:Sage，:Sdept；(4)关闭游标命令

EXECSQLCLOSE〈游标名〉；嵌入式SQL(3)推进游标命令

EXECSQL数据控制机制和语句数据控制是系统通过对数据库用户的使用权限加以限制而保证数据安全的重要措施。授权拒绝访问收权数据控制机制和语句数据控制是系统通过对数据库用户的使用权限加数据控制机制和语句数据控制机制授权定义存权处理查权操作具有授权资格的用户，如数据库管理员DBA或建表户DBO，通过数据控制语言DCL，将授权决定告知数据库管理系统。数据库管理系统DBMS把授权的结果编译后存入数据字典中。当用户提出操作请求时，系统要在数据字典中查找该用户的数据操作权限，当用户拥有该操作权时才能执行其操作，否则系统将拒绝其操作。数据控制机制和语句数据控制机制授权定义存权处理查权操作具有授数据控制机制和语句1.授权语句

系统授权的授权语句格式为：

GRANT〈系统特权组〉To〈用户组〉|PUBLIC

[WITHGRANTOPTION]；

1)PUBLIC指数据库的所有用户。

2)WITHGRANTOPTION:获得权限的用户可以把该权限再授予别的用户。

对象特权的授权格式为：

GRANTALLPRIVILIGES|〈对象特权组〉ON〈对象名〉

TO〈用户组〉|PUBLIC

[WITHGRANTOPTION]；

1)ALLPRIVILIGES指所有的对象特权。

2)对象名指操作的对象标识，它包括表名、视图名和过程名等。数据控制机制和语句1.授权语句

系统授权的授权语句格式为：数据控制机制和语句【例5-51】把修改学生学号和查询学生表的权力授予用户王平。

GRANTUPDATE(学号)，SELECTON学生TO王平；【例5-52】把建立数据库和备份数据库的权力赋给用户王平。

GRANTCREATEDATABASE，

BACKUPDATABASETO王平；数据控制机制和语句【例5-51】把修改学生学号和查询学生表的数据控制机制和语句2.收权语句

REVOKE语句的一般格式为：

REVOKE〈权限组〉|ALLPRIVILIGES[ON〈对象名〉]

FROM〈用户名组〉|PUBLIC；

其中：ON子句用于指定被收回特权的对象；ALLPRIVILIGES指收回所有特权；PUBLIC指所有用户。

【例5-53】将用户王平的可以在学生表中修改学生学号的权利收回。

REVOKEUPDATE(学号)ON学生FROM王平；数据控制机制和语句2.收权语句

REVOKE语句的一般格数据控制机制和语句3.拒绝访问语句

DENYALL[PRIVILIGES]|〈权限组〉[ON〈对象名〉]TO〈用户组〉|PUBLIC；

其中：ON子句用于说明对象特权的对象名；对象名指的是表名、视图名、视图和表的字段名或者过程名。数据控制机制和语句3.拒绝访问语句

DENYALL[第五章关系数据库标准语言——SQL第五章关系数据库标准语言——SQL本章教学目标、重点和难点

本章教学目标：使学生了解SQL的功能和语言特点；熟练掌握用SQL进行表的定义、数据查询和数据更新，掌握SQL的数据控制方法。2.教学重点：SQL的数据定义、数据查询、数据维护和数据控制表示方法3.教学难点：用SQL实现嵌套查询、组合查询、使用分组和SQL函数查询。本章教学目标、重点和难点本章教学目标：使学生了解SQL的功本章内容SQL的功能及特点数据定义语句数据查询语句数据更新语句嵌入式SQL数据控制机制和语句本章内容SQL的功能及特点SQL的功能及特点19741975-19791986-198719921999由Boyce和Chamberlin提出IBM公司研制了关系DBMS原型，并实现该语言美国批准SQL作为美国标准，随后ISO也通过这一标准公布了SQL-92标准公布了SQL-99，也称作SQL3SQL的功能及特点19741975-19791986-198SQL的功能及特点数据定义数据控制数据操纵SQL语句分类SQL的功能及特点数据定义数据控制数据操纵SQL语句分类SQL的功能及特点SQL语句分类数据定义数据操纵数据控制定义关系数据库的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。包括数据查询和数据更新两种数据操作语句：数据查询指对数据库中的数据查询、统计、分组、排序操作；数据更新指数据的插入、删除、修改等数据维护操作。通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。SQL的功能及特点SQL语句分类数据定义数据操纵数据控制定义SQL的功能及特点SQL的特点1）SQL具有自主式语言和嵌入式语言两种使用方式；2）SQL具有语言简洁、易学易用的特点；SQL功能命令动词数据定义（数据模式定义、删除、修改）Create、Drop、Alter数据操纵（数据查询和维护）Select、Insert、Update、Delete数据控制（数据存取控制权和收权）Grant、RemoveSQL的功能及特点SQL的特点SQL功能命令动词数据定义（数SQL的功能及特点SQL的特点3）SQL支持三级模式结构存储文件1存储文件2基本表1基本表2基本表3基本表4视图1视图2SQL外模式模式内模式SQL的功能及特点SQL的特点存储文件1存储文件2基本表1基数据定义语句操作对象创建语句删除语句修改语句基本表CreatetableDroptableAltertable索引CreateindexDropindex视图CreateviewDropview数据库CreatedatabaseDropdatabaseAlterdatabase1.语句格式约定符号尖括号<>中括号[]大括号{}或分隔符|[,…n]实际语义任选项必选项，必选其中一项前面的项可重复多次数据定义语句操作对象创建语句删除语句修改语句基本表Creat数据定义语句2.一般语法规定SQL中的数据项（包括列项、表和视图）分隔符为“,”，其字符串常数的定界符用单引号“’”表示3.SQL特殊语法规定SQL的关键词一般使用大写字母表示；语句的结束符为“;”语句一般应采用格式化书写方式数据定义语句2.一般语法规定数据定义语句基本表的定义和维护1.基本表的定义定义基本表语句的一般格式为：CREATETABLE[〈库名〉]〈表名〉(〈列名〉〈数据类型〉[〈列级完整性约束条件〉][，〈列名〉〈数据类型〉[〈列级完整性约束条件〉]][，…n]

[，〈表级完整性约束条件〉][，…n])；数据定义语句基本表的定义和维护定义基本表语句的一般格式为：数据定义语句建立基本表：学生(学号，姓名，年龄，性别，所在系)；

课程(课程号，课程名，先行课)；选课(学号，课程号，成绩).CREATETABLE课程(课程号CHAR(5)