第三章-数据库系统原理与设计

“数据库系统原理及应用”课程第三章关系数据库语言SQL主讲人：李俊山教授联系方式：743419办公地点：训练楼516房间SQL语句的分类：

数据定义语句：用于定义数据库的逻辑结构，包括定义基本表、定义视图和定义索引。

数据查询语句：用于按不同查询条件实现对数据库中数据的检索查询。

数据操纵语句：用于更改和操作表中的数据，包括数据插入、数据修改、数据删除及数据查询。

数据控制语句：用于实现用户授权、基本表和视图授权、事务控制、完整性和安全性控制等。

3.1.1表的定义、修改与撤消

1.表的定义

表的定义语句格式为：

CREATETABLE<表名>（<列名1><数据类型>[<列1的完整性约束>][，<列名2><数据类型>[<列2的完整性约束>]，……，<列名n><数据类型>[<列n的完整性约束>]，[<表的完整性约束>]]）；

其中：

●典型的<数据类型>

（a）CHAR(m)：长度为m的字符(串)型数据，长度不够时用空白字符补充，不超过240。（b）VARCHAR(m)：长度小于等于m的字符(串)型数据，长度不够时不补充其它字符。（c）INT/INTEGER：长整型数据。（d）DATE：日期型数据，形式为YYYY-MM-DD，分别表示年、月、日。其中：

●典型的<列的完整性约束>（a）NULL：指出该列可以为空值。（b）NOTNULL：指出该列不能为空值。每一个表中至少应有一个列的可选项为NOTNULL。（c）PRIMARYKEY：指出该列名为表的主键。（d）DEFAULT：给所在的列设置一个缺省值。（e）CHECK：指出该列的值只能取CHECK约束条件范围的值。例3.2创建图1.30所示的教学管理数据库系统中的学生关系表S，可用如下的表定义语句定义：CREATETABLES（S#CHAR(9)PRIMARYKEY，SNAMECHAR(10)NOTNULL，SSEXCHAR(2)CHECK(SSEXIN(′男′，′女′))，SBIRTHINDATENOTNULL，PLACEOFBCHAR(16)，SCODECHAR(5)NOTNULL，CLASSCHAR(5)NOTNULL）；其中：

●

<表的完整性约束>

(a)表的主键约束

格式为：PRIMARYKEY(<列名表>)当该表的主键由2个或2个以上属性组成时，表的主键必须由表的完整性约束给出。

例3.3创建教学管理数据库系统中的学习关系SC，可用如下的表定义语句定义：CREATETABLESC（S#CHAR(9),C#CHAR(7)，GRADEINTDEFAULT(0),PRIMARYKEY(S#，C#)）；

●

<表的完整性约束>

(b)表的外键约束

格式为：FOREIGNKEY(<列名1>)REFERENCE<表名>（<列名2>）

本子句定义了一个列名为“<列名1>”的外键，它与表“<表名>”中的“<列名2>”相对应，且“<列名2>”在表“<表名>”中是主键。

例3.4教学管理数据库系统中的学习关系SC，可重新用如下的表定义语句定义如下：CREATETABLESC（S#CHAR(9),C#CHAR(7)，GRADEINTDEFAULT(0),PRIMARYKEY(S#，C#)，FOREIGNKEY(C#)REFERENCESC(C#))；

●

<表的完整性约束>

(c)表检验CHECK约束

格式为：CHECK(<值的约束条件>)

表检验约束CHECK子句的含义和格式与列检验约束相同，所不同的是，表检验约束CHECK子句是一个独立的子句而不是子句中的一部分。表检验约束CHECK子句中的<值的约束条件>不仅可以是一个条件表达式，而且还可以是一个包含SELECT语句的SQL语句。例3.5教学管理数据库系统中的学习关系SC，还可用如下的表定义语句定义如下：CREATETABLESC（S#CHAR(9),C#CHAR(7)，GRADEINTDEFAULT(0),PRIMARYKEY(S#，C#)，FOREIGNKEY(C#)REFERENCESC（C#）,CHECK(GRADEBETWEEN0AND100))；2.表的修改

(1)改变表名

修改表名的语句格式为：RENAME<原表名>TO<新表名>；

2.表的修改

(2)增加列

在表的最后一列后面增加新的一列，但不允许将一个列插入到原表的中间。

增加列语句的格式为：ALTERTABLE<表名>ADD<增加的列名><数据类型>；例3.7给专业表SS增加一个新属性NOUSE_COLUMN，设其数据类型为NUMERIC(8，1)。

语句应为：ALTERTABLESSADDNOUSE_COLUMNNUMERIC(8，1)；2.表的修改

(3)删除列

删除表中不再需要的列，语句格式为：ALTERTABLE<表名>DROP<删除的列名>[CASCADE|RESTRICT]；其中，可选项“[CASCADE|RESTRICT]”是删除方式。当选择CASCADE时，表示在删除名为“<表名>”的表中的列“<删除的列名>”时，所有引用到该列的视图或有关约束也一起被删除；当选择RESTRICT时，表示当没有视图或有关约束引用列“<删除的列名>”时，该列才能被删除，否则拒绝该删除操作。例3.8删除专业表SS中增加的属性NOUSE_COLUMN的两种删除语句分别为：ALTERTABLESSDROPNOUSE_COLUMNCASCADE；ALTERTABLESSDROPNOUSE_COLUMNRESTRICT；2.表的修改

(4)修改列的定义

修改属性列的定义语句只用于修改列的类型和长度，列的名称不能改变。当表中已有数据时，不能缩短列的长度，但可以增加列的长度。

修改列定义语句格式为：ALTERTABLE<表名>MODIFY<列名><新的数据类型及其长度>；例3.8将专业表SS中的专业名称SSNAME(30)修改为SSNAME(40)，即长度增加10。ALTERTABLESSMODIFYSSNAMEVARCHAR(40)；3.表的撤销

表的撤消就是将不再需要的表或定义有错误的表删除掉。当一个表被撤消时，该表中的数据也一同被撤消（删除）。

撤消表的语句格式为：DROPTABLE<表名>[CASCADE|RESTRICT]；其中,CASCADE表示在撤消表“<表名>”时，所有引用这个表的视图或有关约束也一起被撤消；RESTRICT表示在没有视图或有关约束引用该表的属性列时，表“<表名>”才能被撤消，否则拒绝该撤消操作。3.1.2数据的插入、修改、删除、提交与撤消1.数据的插入

向表中插入一行数据的单元组值插入方式的INSERT语句格式为：INSERTINTO<表名>[（<列名表>）]

VALUES（<值表>）；其中：(1)如果选择可选项“[（<列名表>）]”，表示在插入一个新元组时，只向由<列名i>指出的列中插入数据，其他没有列出的列不插入数据(为空值)，且“<列名表>”中必须至少包括表中那些列约束为“NOTNULL”的列和主键列。如果不选择该可选项，则默认表中所有的列都要插入数据。

(2)<值表>指出要插入列的具体值。如果选择了可选项[<列名表>]，则<值表>中的属性列值必须与<列名表>中的属性列名一一对应；如果没有选择可选项[<列名表>]，则<值表>中的属性列值必须与<列名表>中的属性列名一一对应。

例3.10给学习关系SC中插入王丽丽同学（学号为200401003）学习计算机网络课（课程号为C403001）的成绩（89分）。INSERTINTOSC（S#，C#，GRADE）VALUES（’200401003’，’C403001’，89）；

或：INSERTINTOSC

VALUES（’200401003’，’C403001’，89）；3.1.2数据的插入、修改、删除、提交与撤消2.数据的修改

语句格式为：UPDATE<表名>SET<列名1>＝<表达式1>[，<列名2>＝<表达式2>,…,<列名n>＝<表达式n>][WHERE<条件>]其中，“<列名i>＝<表达式i>”指出将列“<列名i>”的值修改成<表达式i>。可选项“[WHERE<条件>]”中的<条件>指定修改有关列的数据时所应满足的条件。当不选择该选项时，表示修改表中全部元组中相应列的数据。

例3.13将学生关系S中的学生名字“王丽丽”改为“王黎丽”。UPDATESSETSNAME＝’王黎丽’WHERES#＝’200401003’；例3.14将所有女同学的专业改为S0404。UPDATESSETSCODE＝’S0404’WHERESSEX＝’女’；

3.1.2数据的插入、修改、删除、提交与撤消3.数据的删除

语句格式为：DELETEFROM<表名>[WHERE<条件>]其中，可选项“[WHERE<条件>]”中的<条件>指定所删元组应满足的条件。当不选该可选项时，表示删除表中全部数据。例3.15在学生关系S中删除学号为200403001的学生信息。DELETEFROMSWHERES#=‘200403001’；例3.16删除专业关系中的全部信息。DELETEFROMSS；3.1.2数据的插入、修改、删除、提交与撤消4.数据的提交

工作区概念：数据提交的概念：就是把用户对数据库中数据的更新结果永久地保存到数据库中。

显式提交:

隐式提交：3.1.2数据的插入、修改、删除、提交与撤消5.数据的撤销

命令格式:

3.2.1简单查询1.SELECT查询语句

SQL查询语句的基本格式为：SELECT<列名表>FROM<表名表>[WHERE<条件>]3.2.1简单查询2.无条件查询

例3.17查询教学管理数据库中全部学生的基本信息。SELECT*FROMS；

例3.19查询课程关系C中的记录数，也即开课的总门数。SELECTCOUNT(*)FROMC；SQL语言中常用的聚合函数主要有：

（1）COUNT(*)计算元组的个数；

（2）COUNT(列名)计算某一列中数据的个数；

（3）COUNTDISTINCT(列名)计算某一列中不同值的个数；

（4）SUM(列名)计算某一数据列中值的总和；

（5）AVG(列名)计算某一数据列中值的平均值；

（6）MIN(列名)求(字符、日期、属性列)的最小值；（7）MAX(列名)求(字符、日期、属性列)的最大值；例3.20计算所有学生所学课程的最高分数、最低分数和平均分数。

SELECTMAX(GRADE)，MIN(GRADE)，AVG(GRADE)FROMSC；3.2.1简单查询3.单条件查询

例3.21查询所有学习计算机网络课(课程号为C403001)的学生的学号和成绩。

SELECTS#，GRADEFROMSCWHEREC#=‘C403001’；表3.1条件表达式中的关系比较符运算符含义=!=或<>>>=<<=ISNULLISNOTNULL等于不等于大于大于等于小于小于等于是空值不是空值3.2.1简单查询4.多条件查询

例3.22查询选修了计算机网络课(课程号为C403001)或信息安全技术课(课程号为C403002)的学生的学号。SELECT#SFROMSCWHEREC#=‘C403001’ORC#=‘C403002’；(关系表见下页)学生关系模式：S（S#，SNAME，SSEX，SBIRTHIN，PLACEOFB，SCODE，CLASS）专业关系模式：SS（SCODE#，SSNAME）课程关系模式：C（C#，CNAME，CLASSH）设置关系模式：CS（SCODE#，C#）学习关系模式：SC（S#，C#，GRADE）教师关系模式：T（T#，TNAME，TSEX，TBIRTHIN，TITLEOF，TRSECTION，TEL）讲授关系模式：TEACH（T#，C#）图1.30图1.9的教学管理数据库系统的概念模式

表3.2逻辑运算符

运算符含义NOTANDOR逻辑非逻辑与逻辑或例3.23若有学生关系S1（S#，SNAME，SSEX，SAGE，PLACEOFB，SCODE，CLASS），即该学生关系中给出的不是出生年月，而是年龄。要求查询年龄在21岁至25岁之间学生的基本信息。SELECT*FROMS1WHEREAGE>=18ANDAGE<=25；

上例中：这种把某数值型列的值限定在某个数值区间的比较查询条件（<、<=、>、>=）可以用比较运算符“BETWEEN…AND”来表示，格式为：<数值型列名>BETWEEN<数值区间下限值>AND<数值区间上限值>例3.23的查询可以改写为：

SELECT*FROMS1WHEREAGEBETWEEN18AND25；5.分组查询

在SQL语言中，把元组按某个或某些列上相同的值分组，然后再对各组进行相应操作的查询方式称为分组查询。

分组查询的语句格式为：SELECT<列名表>FROM<表名表>[WHERE<条件>][GROUPBY<列名表>[HAVING<分组条件>]]；

（1）GROUPBY子句

GROUPBY子句用来将列的值分成若干组，从而控制查询的结果排序。

例3.24计算各个同学的平均分数。SELECT#S，AVG(GRADE)FROMSCGROUPBY#S；

例3.25计算每个专业的男、女生分别有多少人数。SELECTSCODE，SSEX，COUNT(*)FROMSGROUPBYSCODE，SSEX；

（2）HAVING子句

在数据查询中，有时只希望选择满足一定条件的分组。一般是利用GROUPBY子句进行分组，利用HAVING子句判断分组应满足的条件。例3.26查询学生总数超过300人的专业及其具体的总人数。

SELECTSCODE，COUNT(*)FROMSGROUPBYSCODEHAVINGCOUNT(*)>300；6.结果排序

通常，SELECT的查询结果是按元组在数据库中的存储顺序给出的。但有时用户希望按照某种约定的顺序给出查询结果，ORDERBY子句可以实现查询结果的排序显示功能，其语句格式为：SELECT<列名表>FROM<表名表>[WHERE<条件>]

ORDERBY<列名>[ASC/DESC][,<列名>[，ASC/DESC]]；例3.27按学号递增的顺序显示学生的基本信息。SELECT*FROMSORDERBYS#ASC；例3.28按学号递增、课程成绩递减的顺序显示学生的课程成绩。

SELECTS#，C#，GRADEFROMSCORDERBYS#ASC，GRADEDESC；7.字符串的匹配

在WHERE子句的条件表达式中，实现两个字符串的部分字符的相等比较。比较运算符的格式为：

<列名>LIKE‘[字符串1]通配符[字符串2]’其中：（1）下划线_：在字符串比较时，如果有一个字符可以任意，则在该字符位置上用下划线表示。（2）百分号%：在字符串比较时，如果有一个长度大于等于0子字符串可以任意，则在该子字符串位置上用百分号表示。例3.29查询学生关系S中姓李的学生的学号和姓名。SELECTS#，SNAMEFROMSWHERESNAMELIKE‘李%’；8.比较完整的SELECT查询语句

SELECT<列名或列表达式序列>FROM<表名表>[WHERE<条件>][GROUPBY<列名表>][HAVING<分组条件>]][ORDERBY<列名>[ASC/DESC][,<列名>[，ASC/DESC]]]；3.2.2SQL语言中的常用函数及其使用方式1.日期型函数

日期是SQL语言中的标准数据类型（DATE）。

函数

功能sysdatelast_daynext_day(d，w_day)add_months(d，n)months_between(d1,d2)to_char(d，new_d)

取当前日期和时间取本月最后一天计算日期d之后第一周指定星期几(由w_day指定)的日期取在日期d的基础上后推n个月的日期计算日期d1和d2之间相差的月数将日期d的缺省格式“DD-MON-YYYY”表示成指定的新格式new_d表3.3常用的日期函数

例3.30查询出生日期在1982年1月1日到1982年12月31日之间的所有学生的学号、姓名和出生日期，并按日期的递增顺序排列。SELECTS#，SNAME，SBIRTHINFROMSWHERESBIRTHINBETWEEN‘1-JUN-1982’AND’31-DEC-1982’ORDERBYSBIRTHIN；表3.4常用的日期显示格式

日期显示格式（模式）示例缺省格式MM/DD/YYYYYY/MM/DDDYDDMONYYDayMonDD

23-FEB-9902/23/991999/02/23WED12JAN88WednesdayJan12

例3.31在上例中，出生日期的缺省显示方式不符合我国的日期表示习惯，可用日期转换函数函数to_char将其转换成“年/月/日”的表示形式。SELECTS#，SNAME，to_char(SBIRTHIN，’YYYY”/”MM”/”DD’)FROMSWHERESBIRTHINBETWEEN‘1-JUN-1982’AND’31-DEC-1982’ORDERBYSBIRTHIN；2.字符串函数表3.5常用的字符串函数

函数

功能length(str)initcap(str)lower(str)upper(str)replace(str，str1，str2)substr(str，m，n)itrim(str)ascii(str)

求字符串的长度将字符串str中的第一个字符转换成大写字符将字符串str中的大写字母转换成小写字母将字符串str中的小写字母转换成大写字符将字符串str中的所有str1换成str2从字符串str的第m个字符开始取出n个字符截掉字符串str尾部的空格取字符串str的ASCII码值

3.2.2SQL语言中的常用函数及其使用方式3.算术函数表3.6常用的算术函数

函数功能abs(n)ceil(n)floor(n)mod(m,n)power(m,n)round(n,m)sign(n)sqrt(n)求n的绝对值求大于等于数值n的最小整数求小于等于数值n的最大整数取余数。若n=0，则取m求m的n次方四舍五入，保留m位n=0，返回0；n>n，返回1；n<0，返回-1求n的平方

4.空值显示与空值函数例3.33查询学习关系SC中分数GRADE为空值的课程号。SELECTC#FROMSCWHEREGRADEISNULL；3.2.3SQL高级查询技术1.多表联接查询（二元查询/多元查询）SELECTS.S#，SNAMEFROMS，SCWHERES.S#=SC.S#ANDC#=‘C401001’；例3.37查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。例3.38查询选修了“信息安全技术”课程的学生的学号与姓名。SELECTS.S#，SNAMEFROMS，SC，CWHERES.S#=SC.S#ANDSC.C#=C.C#ANDCNAME=‘信息安全技术’；3.2.3SQL高级查询技术2.外联接查询

学号姓名性别出生年月籍贯专业代码班级200401001张华男14-dec-82北京S0401200401200401002李建平男20-aug-82上海S0401200401200401003王丽丽女02-feb-83上海S0401200401200402001杨秋红女09-may-83西安S0402200402200402002吴志伟男30-jun-82南京S0402200402200402003李涛男25-jun-83西安S0402200402200403001赵晓艳女11-mar-82长沙S0403200403专业代码专业名称S0401计算机科学S0402指挥自动化S0403网络工程S0404信息研究学生关系S：专业关系SS注意：虽然存在信息研究专业，但学生关系的当前值中没有信息研究专业的学生。

对于查询：

SELECTSS.SCODE#，SS.SSNAME，S#，SNAMEFROMSS，SWHERESS.SCODE#=S.SCODEORDERBYSCODE#；

有查询结果：SCODESSNAMES#SNAME

S0401计算机科学200401001张华S0401计算机科学200401002李建平S0401计算机科学200401003王丽丽S0402指挥自动化200402001杨秋红S0402指挥自动化200402002吴志伟S0402指挥自动化200402003李涛S0403网络工程200403001赵晓艳

由于信息研究专业没有学生，所以查询结果丢失了信息研究的专业信息，为此，SQL引入了外联接查询。

外联接查询包括：左外联接和右外联接。

(1)左外联接(Left-outerJoin)

RS=(RS)∪(R1(?…?))其中：

R1=R-πR（RS）(2)右外联接(Right-outerJoin)

RS=(RS)∪((?…?)S1)其中：S1=S-πS（RS）

外联接查询包括：左外联接和右外联接。

设“R.A=S.B”是WHERE子句中的等值条件表达式，则：（1）左外联接的表示形式为R.A(+)=S.B，其含义是假设在等值条件右边的关系S中有一个“全空”的元组，且该元组可与等值条件左边的关系R中任何不满足等值联接条件的元组匹配构成一个新的元组；（2）右外联接的表示形式为R.A=S.B(+)，其含义是假设在等值条件左边的关系R中有一个“全空”的元组，且该元组可与等值条件右边的关系S中任何不满足等值联接条件的元组匹配构成一个新的元组。这时，上述的查询语句：

SELECTSS.SCODE#，SS.SSNAME，S#，SNAMEFROMSS，SWHERESS.SCODE#=S.SCODEORDERBYSCODE#；

就可以改写成：

SELECTSS.SCODE#，SS.SSNAME，S#，SNAMEFROMSS，SWHERESS.SCODE#(+)=S.SCODE#ORDERBYSCODE#；

查询出的结果就包含了：S0404信息研究nullnull尽管信息研究专业没有学生，但专业仍然存在！3.嵌套查询

即：在SQL语言中，如果在一个SELECT语句的WHERE子句中嵌入了另一个SELECT语句，则称为嵌套查询。WHERE子句中的SELECT语句称为子查询。例3.39查询张华同学（学号为200401001）的那个班的女同学的基本信息。SELECT*FROMSWHERECLASS=(SELECTCLASSFROMSWHERES#=‘200401001’)ANDSSEX=‘女’；4.谓词演算查询表3.7常用的谓词操作符操作符说明betweenAandBnotbetweenAandBlikeinnotinanysomeallexistsnotexists

某列的数值区间是[A，B]某列的数值区间在[A，B]外两个字符串的部分字符相等，其余可以任意某列的某个值属于集合成员中的一个成员某列的值不属于集合成员中的任何一个成员某列的值满足一个条件即可满足集合中的某些值某列的值满足子查询中所有值的记录总存在一个值满足条件不存在任何值满足条件

4.谓词演算查询

（1）IN和NOTIN谓词

条件表达式格式为：<集合1>IN<集合2><集合1>NOTIN<集合2>

前者的含义：如果集合1中的数据是集合2中的成员，那么逻辑值为true，否则为false。

后者的含义：如果集合1中的数据不是集合2中的成员，那么逻辑值为true，否则为false。例3.40查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。SELECTS.S#，SNAMEFROMSWHERES#IN(SELECTS#FROMSCWHEREC#=‘C401001’)；例3.41利用嵌套查询实现例3.38，也即查询选修了“信息安全技术”课程的学生的学号与姓名。SELECTS.S#，SNAMEFROMSWHERES#IN(SELECTS#FROMSCWHEREC#IN(SELECTC#FROMCWHERECNAME=‘信息安全技术’));思路：选修了“信息安全技术”课的课程号；选修了该课程（号）的学生的学号4.谓词演算查询

（2）ANY和SOME谓词

条件表达式格式为：<列数据>θANY<集合><列数据>θSOME<集合>其中，θ是算术比较运算符<、<=、>、>=、=、!=。其含义是：比较运算符θ左边的数据与右边集合中的某个或某些元素是否满足θ运算，满足则为真，不满足则为假。

在SQL语言中，ANY和SOME具有相同的含义，早期的版本用的是ANY，新的版本都改为SOME，有些商用数据库版本的SQL语言中，同时保存了ANY和SOME两个谓词。

例3.42查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。SELECTS.S#，SNAMEFROMSWHERES#=ANY(SELECTS#FROMSCWHEREC#=‘C401001’)；其中，“=ANY”的作用相当于IN。4.谓词演算查询

（3）ALL谓词

条件表达式格式为：<列数据>θALL<集合>含义是：比较运算符θ左边的数据与右边集合中的所有元素是否满足θ运算，满足其逻辑值为true，不满足其逻辑值为false。

。

例3.43查询考试成绩大于网络工程专业（专业代码为S0403）所有学生的课程成绩的学生的基本信息。SELECTS.S#，SNAME，SSEX，SBIRTHIN，PLACEOFB，SCODE，CLASSFROMS，SCWHERES.S#=SC.S#ANDGRADE>ALL(SELECTGRADEFROMS，SCWHERES.S#=SC.S#ANDSCODE=’S0403’；

4.谓词演算查询

（4）EXISTS和NOTEXISTS谓词

条件表达式格式为：

EXISTS（<集合>）

NOTEXISTS（<集合>）

前者的含义是：当集合中至少存在一个元素（非空）时，其逻辑值为true，否则为false；

后者的含义是：当集合中不存在任何元素（为空）时，其逻辑值为true，否则为false。

通常用于测试子查询是否有返回结果。例3.44查询所有学习了数据结构课（课程号为C401001）的学生的学号和姓名。SELECTS.S#，SNAMEFROMSWHEREEXISTS(SELECT*FROMSCWHERESC.S#=S.S#ANDC#=‘C401001’)；

直观的意义为：查询的是那些，在学习关系中存在所学课程为数据结构的学生的学号和姓名。5.查询结果的并、交、差操作

（1）并操作

即，指将两个或多个SELECT语句的查询结果组合在一起作为总的查询结果输出。语句格式为：

SELECT<列名表>FROM<表名表>[WHERE<条件>][UNION[ALL]{SELECT语句}…]；

其中，如果不选择可选项ALL，则在输出总查询结果时重复的行会自动被取掉。如果选择可选项ALL，则表示将全部行合并输出，也即不取掉重复行。

例3.46合并学生关系和专业关系中的专业代码。SELECTSCODE#FROMSUNIONSELECTSCODE#FROMSS；

5.查询结果的并、交、差操作

（2）交操作

查询结果的交操作是指将同时属于两个或多个SELECT语句的查询结果作为总的查询结果输出。查询结果交操作的基本数据单位是行。其语句格式为：SELECT<列名表>FROM<表名表>[WHERE<条件>][INTERSECT{SELECT语句}…]；例3.47查询有成绩的学生的学号。SELECTS#FROMSINTERSECTSELECTS#FROMSC[WHEREGRADEISNOTNULL];5.查询结果的并、交、差操作

（3）差操作

是指从第一个SELECT语句的查询结果中去掉属于第二个SELECT语句查询结果的行作为总的查询结果输出。其语句格式为：SELECT<列名表>FROM<表名表>[WHERE<条件>]

[MINUS{SELECT语句}]；

例3.48查询没有成绩的学生的学号。SELECTS#FROMSMINUSSELECTS#FROMSC[WHEREGRADEISNOTNULL];

课堂综合练习：

1、查询全部学生的学号、姓名、所学课程号和成绩。

2、查询没有选修任何课程的学生的学号和姓名。

3、找出选修了全部课程的学生的学号和姓名。

堂综合练习答案：

1、查询全部学生的学号、姓名、所学课程号和成绩。SELECTS.S#，SNAME，C#，GRADEFROMS，SCWHEREC#IN(SELECTC#FROMSCWHERES.S#=SC.S#)；

堂综合练习答案：

2、查询没有选修任何课程的学生的学号和姓名。SELECTS#，SNAMEFROMSWHERENOTEXISTS(SELECT*FROMSCWHERESC.S#=S.S#)；

堂综合练习答案：

3、找出选修了全部课程的学生的学号和姓名（没有一们可他不选）。

SELECTS#，SNAMEFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESC.S#=S.S#ANDSC.C#=C.C#)；

第三章第三次课3.3.1视图的概念

视图是由数据库中满足一定条件约束的数据组成的“虚表”，它可以由某个表中满足一定条件的行组成，也可以由某个（或某些）表的某些列组成，还可以由若干个表经过一定的运算而形成。图3.1视图与基本表的映射关系视图3视图4视图1视图2表1表2表3表43.3.2视图的定义

视图的定义语句格式为：

CREATEVIEW<视图名>[(<视图列名表>)]AS<SELECT语句>[WITHREADONLY|WITHCHECKOPTION]；其中，当选择[WITHREADONLY]可选项时，表示该视图被定义成一个只读，不能进行插入、删除和修改操作。当选择[WITHCHECKOPTION]可选项时，用户必须保证每当向该视图中插入或更新数据时，所插入或更新的数据能够从该视图查询出来。

例3.49教学安排视图：TA(C#，CNAME，CLASSH，TNAME，TRSECTION）的创建语句为：

CREATEVIEWTAASSELECTC#，CNAME，CLASSH，TNAME，TRSECTIONFROMC，TEACH，TWHEREC.C#=TEACH.C#ANDTEACH.T#=T.T#；其中，只有视图名TA，没有<视图列名表>选项说明视图TA的列名表与SELECT语句中的列名表相同。例3.49（续）平均成绩视图A_GRADE(S#，SNAME，AVG_GRADE)的创建语句为：CREATEVIEWA_GRADE（S#，SNAME，AVG_GRADE）ASSELECTS.S#，SNAME，AVG(GRADE)FROMS，SCWHERES.S#=SC.S#ANDGRADEISNOTNULL；GROUPBYS.S#ORDERBYS.S#；

3.3.3在视图上进行查询操作

例3.50利用视图A_GRADE查询学生所学课程的平均成绩。SELECT*FROMA_GRADE；

如果用查询语句实现查询学生所学课程的平均成绩，其查询语句应为：SELECTS.S#，SNAME，AVG(GRADE)FROMS，SCWHERES.S#=SC.S#ANDGRADEISNOTNULL；GROUPBYS.S#ORDERBYS.S#；3.3.4在视图上进行数据更新

在视图上进行数据更新操作(INSERT、DELETE、UPDATE)时应注意以下几点：（1）如果某视图是建立在多个表的基础上，那么不允许对该视图进行更新操作。（2）如果在某视图的定义中使用了GROUPBY子句和聚合函数，那么不允许对该视图进行更新操作。（3）如果视图是建立在单个表的基础上，且视图的属性列个数与表的属性列个数相同，或少于表的属性列个数时，且所少的列是表中可为空值(NULL)的列，则可在该视图上进行更新操作。3.3.6用户视图对数据库设计和系统性能带来的影响

（1）用户视图给数据库应用系统的设计提供了极大的优越性

用户视图的建立，会使在应用程序中用到并反复出现的含有复杂关系联接和投影的查询语句被简单的视图查询语句所代替。这样不仅简化了用户接口，使应用程序中的SQL语句变得简单明了，清晰可读；而且可以使应用程序员把编写应用程序的主要精力集中在对数据的处理、分析和用户界面的实现上，方便应用程序的设计。3.3.6用户视图对数据库设计和系统性能带来的影响

（2）实现了数据库的逻辑数据独立性

由于不同的用户具有不同用户视图，这样就使一个用户视图的变化不会影响另一些用户视图。当数据库的逻辑结构（概念模型）或存储结构（存储模型）发生变化时，并且这些变化与某一或某些用户的视图无关时，就不必改变该用户的应用程序；当这些变化与某一或某些用户的视图有关时，可通过改变基本表到用户视图之间的映射关系，也即通过重新定义用户视图而使用户视图保持不变或稍有变化，从而不必修改应用程序或少量修改应用程序。这样就实现了数据库的逻辑数据独立性。3.3.6用户视图对数据库设计和系统性能带来的影响

（3）可起到了对数据库中数据进行安全保护的作用

由于不同用户具有不同的用户视图，这样就使得在各个用户视图中只出现他自己关心的那部分数据，其它数据对他来说是不能使用的。而且系统给用户使用的是视图而不是在数据库中存储有数据的表。这样就起到了对数据库中数据进行安全保护的作用。3.4.1数据插入操作（数据的导入）把从某个或某些表中查询出的数据插入到另一个表中去。语句格式为：INSERTINTO<表名>[（<列名表>）]）<子查询>；

其中，<子查询>是一个合法的SELECT查询语句。例3.51设在教学管理数据库系统中，要建立某些临时表来辅助有关管理过程。若其中有一个临时表用于暂存选修了“信息安全技术”课程的学生的学号、姓名、专业名称和班级，表的其格式为：S_C（S#，SNAME，SSNAME，CLASS）例3.51（续）则将从有关表中查询出的有关数据组成的记录插入该表的插入语句为：INSERTINTOS_C(S#，SNAME，SSNAME，CLASS)SELECTS.S#，SNAME，SSNAME，CLASSFROMS，SSWHERESS.SCODE#=S.SCODEANDS#IN(SELECTS#FROMSCWHEREC#IN(SELECTC#FROMCWHERECNAME=‘信息安全技术’))；例3.52设在教学管理数据库系统中，有一个临时表用于暂存平均成绩大于等于80分的女同学的学号和平均成绩。临时表的格式为：S_AVG(S#,AVG_GRADE)则将从有关表中查询出的有关数据组成的记录插入该表的插入语句为：INSERTINTOS_AVG(S#,AVG_GRAGE)SELECTS#,AVG(GRADE)FROMSCWHERES#IN(SELECTS#FROMSWHERESSEX=‘女’）GROUPBYS#HAVINGAVG(GRADE)>=80；3.4.2数据更新操作用子查询表示条件的数据更新操作。语句格式为：

UPDATE<表名>SET<列名1>＝<表达式1>[，<列名2>＝<表达式2>,…,<列名n>＝<表达式n>]WHERE<条件>；

其中，WHERE条件中包含有SELECT查询子句。例3.53为了加强对高水平尖子人才的重点培养，学校拟将计算机应用技术专业(专业代码为：S0401)中，各门课程均在85分以上的学生单独编为200400班，所以需要修改学生基本情况数据库中的“班级”属性。其实现语句为：UPDATESSETCLASS=‘200400’WHERESCODE=‘S0401’ANDS#IN(SELECTS#FROMSCWHEREGRADE>85)；例3.54由于试题难度原因，需要将“计算机网络”课的成绩提高5%。其实现语句为：UPDATESCSETGRADE=GRADE*1.05WHEREC#IN(SELECTC#FROMCWHERECNAME=‘计算机网络’）；

3.5.1嵌入式SQL的概念

交互式SQL：是非过程语言，大多数语句的执行都是独立的，与上下文无关的；无法满足绝大多数应用所需的过程性要求。

嵌入式SQL：引入过程性结构和把SQL语言嵌入到C、PASCAL等高级语言的机制。

宿主语言：在SQL嵌入形式中用到高级语言。

应用程序：含有嵌入SQL语句的高级语言应用程序称为宿主应用程序，简称为宿主程序或应用程序。3.5.2嵌入式SQL遇到的问题

（1）当SQL语言语句嵌入到C语言后，宿主程序中就包含了不属于高级语言的一部分成份。换句话说，宿主程序不再是普通的高级语言源程序了，显然也不能用通常的编译、连接方式对其进行处理。如何实现宿主程序的编译是要解决的问题之一。

3.5.2嵌入式SQL遇到的问题

（2）SQL语言是非过程性语言，大多数SQL语句的执行是独立的，与上下文无关的。而C语言是过程性语言，程序的执行机制是顺序、分支和循环，当前语句的执行与前面语句的执行结果有关，各语句的执行是上下文有关的。那么，当把SQL语句嵌入到C语言后，如何来确定SQL语句的执行顺序是要解决的问题之一。

3.5.2嵌入式SQL遇到的问题

（3）SQL语言语句中的属性名是表示关系的属性及其当前值的集合型“参变量”。而C语言语句中的变量既有单值变量，又有数组变量。如何建立两种语言变量之间联系是要解决的问题之一。（4）SQL语句是面向集合的，一次查询的结果往往有许多行。而C语言的数据处理方式原则上是一次处理一个记录的顺序方式。如何使它们在对数据的保持和处理上相一致是要解决的问题之一。3.5.3嵌入式SQL的实现

两种实现途径：

（1）建立全新的编译程序，使之可直接编译SQL语句；

（2）采用预处理方式，把宿主程序中的SQL语句转换成可为高级语言调用的函数形式：

后一种途径的实现需要：

提供预编译器；

提供实现所有SQL语句的函数库。3.5.3嵌入式SQL的实现（续）

基于预处理方式的数据库应用程序从编写到执行的过程为：

（1）编写包含有嵌入式SQL语句的宿主语言应用程序；（2）利用预编译器对应用程序进行预编译，产生含有高级语言函数调用的“普通”高级语言源程序；（3）利用高级语言编译器编译由(2)生成的高级语言源程序，生成相应的目标文件；（4）对生成的目标文件和所需的函数库进行连接，生成可执行文件；（5）运行可执行文件形式的应用程序。3.5.4SQL语句的嵌入形式

3.5.5宿主程序的组成与结构

预编译单元：在嵌入式SQL中，将宿主应用程序中的一个源程序文件称为一个预编译单元。宿主程序的组成：具有与C语言一样的组成结构，一个宿主应用程序可以分成若干个源程序文件，每个源程序文件可以是一个独立的预编译单元，可以将这些经过独立预编译的程序再分别独立编译，并生成各自的目标文件，最后将这些目标文件连接成一个完整的执行文件。

3.5.5宿主程序的组成与结构

预编译单元的组成结构：

（1）说明部分（DECLARE部分）（2）通信区定义部分（3）系统连接部分

（4）程序体程序首部程序体1.说明部分（DECLARE部分）

（1）主语言变量、宿主变量、宿主变量的使用规则：主语言变量：宿主变量：在SQL语句中使用的主语言的变量称为宿主变量。

例3.55根据由宿主变量S1给出的学生学号，查询、学生的姓名、性别和出生年月。

EXECSQLSELECTSNAME，SSEX，SBIRTHINFROMS

INTO:SNAME1，:SSEX1，:SBIRTHWHERES#=:S1；由本例说明：宿主变量是主语言和SQL语句都可以对其赋值和引用其值的变量。

宿主变量的使用规则：

①宿主变量必须在DECLARE部分定义。②在SQL语句中使用宿主变量时，必须在仅接宿主变量的前面写一个冒号“：”；在纯C语言语句中使用宿主变量时，与非宿主变量一样不加任何标记。③宿主变量不能是SQL语言中的保留字。④一个宿主变量在一条SQL语句中只能使用一次。⑤宿主变量可以带指示变量。1.说明部分（DECLARE部分）（续）（2）指示变量

是一个用来指示所指宿主变量的值或条件的整型变量。宿主变量利用指示变量赋空值或检测是否是空值。

指示变量的使用规则为：

①指示变量必须在DECLARE部分定义。②在SQL语句中使用指示变量时，必须在仅接指示变量的前面写一个冒号“：”；在纯C语言语句（也即，主语言）中使用指示变量时，不用加任何标记。③指示变量不能是SQL语言中的保留字。④指示变量必须定义成2字节的整型变量，如short，int类型。⑤在SQL语句中，指示变量必须紧跟在所指宿主变量之后，中间不能有逗号或空格。例3.56宿主变量带有指示变量的例子。根据由宿主变量S1给出的学号查询学生姓名、性别和出生年月。

EXECSQLSELECTSNAME，SSEX，SBIRTHINFROMSINTO:SNAME1:SNAME2，:SSEX1:SSEX2，

:SBIRTH:SBIRTH2WHERES#=:S1；1.说明部分（DECLARE部分）（续）（3）DECLARE的格式

用DECLARE定义宿主变量和指示变量的描述格式为：EXECSQLBEGINDECLARESECTION；<定义宿主变量和指示变量语句> EXECSQLENDDECLARESECTION；1.说明部分（DECLARE部分）（续）（4）作为宿主变量的指针和C语言一样，宿主变量的指针是由星号“*”和变量名来说明的。比如：EXECSQLBEGINDECLARESECTION；intGRADE1，GRADE2，*intptr；EXECSQLENDDECLARESECTION；但在SQL语句中，指针变量前不写星号（星号是隐含的），而要在变量名前加一个冒号“：”。比如：SELECTINT_PTRINTO:intptrFROM…1.说明部分（DECLARE部分）（续）（5）VARCHAR伪类型VARCHAR伪类型用于说明称为伪类型变量的变长字符串。伪类型变量在DECLARE部分说明后，就可以在宿主程序中被引用了。比如：EXECSQLBEGINDECLARESECTION；VARCHARuid[20]；EXECSQLENDDECLARESECTION；相当于定义了一个结构体变量：struct{unsignedintlen；unsignedchararr[20]；}uid;在C语言中对该结构体变量的引用方式为：uid.len是无符号整数类型，用于表示（存放）伪类型变量定义的字符串的长度。uid.arr用于表示（存放）伪类型变量定义的字符串。例3.57给出例3.56的语句中的主变量说明。EXECSQLBEGINDECLARESECTION；VARCHARSNAME1[10]，SSEX1[2]，S1[9]；dateSBIRTH；intSNAME2，SSEX2，SBIRTH2；EXECSQLENDDECLARESECTION；

（以上是例3.57的正确形式，注意改错！）

2.SQL通信区及其定义

（1）问题的提出

在基于预处理的宿主语言中，宿主程序中的一条SQL语句的执行实质上是执行一次函数调用，通过相应的函数调用实现该SQL语句的功能。函数在执行过程中可能出现各种情况，所以就引入了SQL通信区SQLCA，利用SQL通信区来返回函数调用的执行状态信息，在返回后根据返回信息，决定下一步的执行流程或对错误的处理。

2.SQL通信区及其定义

（2）SQL通信区的定义

在宿主程序的相应部分用加上语句：EXECSQLINCLUDESQLCA；

2.SQL通信区及其定义

（3）SQLCA返回的信息类别

①任务完成，一切正常；②任务完成，但有例外，也即有警告信息；③有某种错误，任务没有完成。

2.SQL通信区及其定义

（4）对SQLCA返回信息的判别方式

WHENEVER语句：

WHENEVER语句格式：

EXECSQLWHENEVER[SQLERROR|SQLWARING|NOTFOUND][STOP|CONTINUE|GOTO<语句标号>]；3.系统连接（数据库登录）CONNECT语句：CONNECT语句格式：

EXECSQLCONNECT：<用户名>IDENTIFIEDBY：<口令>；或

EXECSQLCONNECT：<用户名>/<口令>；4.程序体

在程序首部（DECLARE部分、SQL通信区、系统连接部分）之后，就是程序体。程序体是主语言语句和由“EXECSQL”为开始标识的SQL语句（DDL语句、DML语句、DCL语句）组成。例3.58宿主应用程序系统连接（注册登录）示例。

#include<stdio.h>#include<string,h>

EXECSQLBEGINDECLARESECTION；/*说明SQL变量*/VARCHARuid[10]；/*uid中存放用户名*/VARCHARpwd[8]；/*pwd中存放口令*/

EXECSQLENDDECLARESECTION；

EXECSQLINCLUDESQLCA；/*说明SQL通信区*/

Main(){strcpy(uid.arr,”scott”)；/*初始化用户名:scott*/uid.len=strlen(uid.arr)/*初始化uid的长度值*/strcpy(pwd.arr,”tigger”)；/*初始化口令:tigger*/pwd.len=strlen(pwd.arr)；/*初始化pwd的长度值*/

EXECSQLCONNECT:uidIDENTIFIEDBY:pwd；

/*注册登录数据库*/Printf(“SuccessfullyconnectedtoSQL\n”)；

EXECSQLCOMMITWORKRELEASE;/*提交并释放数据库*/exit(0)；}3.5.6数据更新及其嵌入式SQL应用程序设计

（1）数据的插入例3.59：向数据库中已存在的课程关系表中插入一个记录的应用程序。

#include<stdio.h>#include<string.h>EXECSQLBEGINDECLARESECTION；/*说明SQL变量*/VARCHARuid[10]；/*uid中存放用户名*/VARCHARpwd[8]；/*pwd中存放口令*/VARCHARcnum[7]；/*定义课程号CNUM为伪类型*/VARCHARcname[30]；/*定义课程名CNAME为伪类型*/intclassh；/*定义学时数CLASSH为整型数*/EXECSQLENDDECLARESECTION；EXECSQLINCLUDESQLCA；/*说明SQL通信区*/Main()Main(){strcpy(uid.arr，”scott”)；/*初始化用户名：scott*/uid.len=strlen(uid.arr)；/*初始化uid的长度值*/strcpy(pwd.arr，”tigger”)；/*初始化口令：tigger*/pwd.len=strlen(pwd.arr)；/*初始化pwd的长度值*/

EXECSQLCONNECT:uidIDENTIFIEDBY:pwd；/*注册登录数据库*/printf(“用户‘%s’已成功地连接到数据库！\n”，uid.arr)；While(1){prinft(“\n\n输入课程号：”)；scanf(”%s”，&cnum)；cnum.len=strlen(cnum.arr)；/*设置课程号CNUM的长度*/prinft(“\n\n输入课程名称：”)；scanf(”%s”，&cname)；cname.len=strlen(cname.arr)；/*设置课程名CNAME的长度*/prinft(“\n\n输入课程的学时数：”)；scanf(”%d”，&classh)；

EXECSQLINSERTINTOC(C#，CNAME，CLASSH)VALUES(:cnum，:cname，:classh)；EXECSQ