数据库系统概论(第四版)王珊萨师煊chp3-1

数据库系统概论AnIntroductiontoDatabaseSystem章关系数据库标准语言SQL第三章

关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结3.1SQL概述SQL语言于1974年由Boyce等提出，并于1975～1979年在IBM公司研制的SystemR数据库管理系统上实现，现已成为国际标准。很多数据库厂商都对SQL语句进行了再开发和扩展标准SQL命令包括查询SELECT插入INSERT更新UPDATE删除DELETE创建CREATE对象删除DROP可被用来完成几乎所有的数据库操作3.1SQL概述3.1.1SQL发展3.1.2SQL特点3.1.3SQL查询基本概念3.1.1SQL发展SQL-86：第一个SQL标准，由美国国家标准局(AmericanNationalStandardInstitute，简称ANSI)公布，1987年国际标准化组织(InternationalOrganizationforStandardization，简称ISO)通过。该标准也称为SQL-1SQL-92：在1992年，由ISO和ANSI对SQL-86进行重新修订，该标准也称为SQL-2SQL-99：在1999年，该版本在SQL-2的基础上，扩展了诸多功能，包括递归、触发、面向对象技术等。该标准也称为SQL-3

SQL-2003：该标准是最新的标准，也称SQL-4，于2003年发布3.1.1SQL发展SQL语言由4部分组成数据定义语言DDL(DataDefinitionLanguage)定义数据库的逻辑结构，包括数据库、基本表、视图和索引等，扩展DDL还支持存储过程、函数、对象、触发器等的定义DDL包括3类语言，即定义、修改和删除数据操纵语言DML(DataManipuplationLanguage)对数据库的数据进行检索和更新，其中更新操作包括插入、删除和修改数据数据控制语言DCL(DataControlLanguage)对数据库的对象进行授权、用户维护(包括创建、修改和删除)、完整性规则定义和事务定义等其它主要是嵌入式SQL语言和动态SQL语言的定义，规定了SQL语言在宿主语言中使用的规则扩展SQL还包括数据库数据的重新组织、备份与恢复等功能3.1.2SQL特点综合统一集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体高度非过程化面向集合的操作方式采用集合操作方式，其操作对象、查找结果都是元组的集合

同一种语法结构提供两种使用方式SQL语言既是自含式语言，又是嵌入式语言。在两种不同的使用方式下，其语法结构基本上是一致的语言简洁，易学易用SQL语言的动词非常少，主要包括：数据查询SELECT；数据定义CREATE、DROP、ALTER；数据操纵INSERT、UPDATE、DELETE；数据控制GRANT、REVOKE3.1.3SQL查询基本概念SQL语言支持三级模式结构，外模式对应视图和部分基本表，模式对应基本表，内模式对应存储文件SQL视图1视图2基本表1基本表2基本表3基本表4存储文件1存储文件2外模式模式内模式3.1.3SQL查询基本概念基本表数据库中独立存在的表称为基本表在SQL中一个关系对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引索引存放在存储文件中视图指从一个或几个基本表(或视图)导出的表，是虚表只存放视图的定义而不存放对应数据查询表指查询结果对应的表第三章

关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结3.2学生-课程数据库学生-课程模式S-T:

学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

课程表：Course(Cno,Cname,Cpno,Ccredit)

学生选课表：SC(Sno,Cno,Grade)

Student表学号Sno姓名Sname性别

Ssex年龄

Sage所在系

Sdept200215121200215122200215123200515125李勇刘晨王敏张立男女女男20191819CSCSMAISCourse表课程号Cno课程名Cname先行课Cpno学分Ccredit1234567数据库数学信息系统操作系统数据结构数据处理PASCAL语言516764243424SC表学号Sno

课程号

Cno

成绩

Grade200215121200215121200215121200215122200215122123239285889080第三章

关系数据库标准语言SQL3.1SQL概述3.2学生-课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结写出图中每一个关系的外键。如关系：选课，两个外键：学号和课程号学生，外键：系号；授课，外键：职工号，授课课号教师，外键：系号教师和工资表，形式上职工号即是主键又是外键，语义上应该是职工号在教师表中做主键，在工资表中即是主键又是外键写出和关系表达式：“学生⋈选课”以及“学生⋈选课⋈课程”，等价的使用笛卡尔积和选择运算的关系表达式写出和关系表达式：“学生⋈选课”以及“学生⋈选课⋈课程”，等价的使用笛卡尔积和选择运算的关系表达式σ学生.学号=选课.学号（学生×选课）σ学生.学号=选课.学号∧选课.课程号=课程.课程号（学生×选课×课程）查询男学生的姓名、选修的课程名、成绩查询男学生的姓名、选修的课程名、成绩π姓名，课程名称，成绩（σ性别=’男’（学生⋈选课⋈课程））查询教师的姓名、职称、应发工资查询教师的姓名、职称、应发工资π姓名，职称，应发工资（教师⋈工资）查询学分大于3的所有课程名称、及其对应的授课老师姓名和老师所在的系名查询学分大于3的所有课程名称、及其对应的授课老师姓名和老师所在的系名π课程名称，姓名，系名称（σ学分>3（系部⋈教师⋈授课⋈课程））查询所有老师的授课情况，显示老师姓名、课程名称、学分查询所有老师的授课情况，显示老师姓名、课程名称、学分π姓名，课程名称，学分((教师leftoutterjoin授课)leftoutterjoin课程)1.查询学生的所有信息select*from学生2.查询每个学生的姓名和籍贯select姓名,籍贯from学生3.查询1985年以后出生的学生的学号、姓名和出生日期select学号,姓名,出生日期from学生whereyear(出生日期)>19854.查询家庭住址“海淀区”的同学的姓名和家庭住址select姓名,家庭住址from学生where家庭住址like'%海淀区%‘5.查询学号前两位是“05”的学生的姓名和系号select姓名,系号from学生where学号like'05%'6.查询职称是“教授”或“副教授”的教师的姓名、性别和职称select姓名,性别,职称from教师where职称='教授‘or职称='副教授'7.查询女学生的姓名、生日，将结果中各列的标题分别指定为“Name”和“Birthday”select姓名Name,出生日期Birthdayfrom学生where性别='女'8.查询民族代码为“01”、“03”、“30”的学生的姓名、学号、民族代码select姓名,学号,民族代码from学生where民族代码='01‘or民族代码='03‘or民族代码='30'9.查询选了课的学生的学号，（不能有重复值）selectdistinct学号from选课数据查询语句格式

SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；

SQL查询SQL基本结构包括3个子句：SELECT子句对应投影运算，指定查询结果中所需要的属性或表达式FROM子句

对应笛卡尔积，给出查询所涉及的表，表可以是基本表、视图或查询表WHERE子句对应选择运算，指定查询结果元组所需要满足的选择条件SELECT和FROM是必须的，其他是可选的SQL查询基本语法为：SELECTA1,A2,...,AnFROMR1,R2,...,RmWHEREPA1,A2,...,An代表需要查找的属性或表达式R1,R2,...,Rm代表查询所涉及的表P代表谓词(即选择条件)，如果省略WHERE子句，表示P为真SQL的查询结果中允许包含重复元组SQL执行过程：首先对R1,R2,...,Rm执行笛卡尔积然后在笛卡尔积中选择使得谓词P为真的记录再在A1,A2,...,An属性列中进行投影运算，不消除重复元组如需消除重复元组，必须使用关键字DISTINCT刚才描述的SQL查询执行过程只是逻辑上的，在具体执行时会进行优化处理，查询优化的内容详见第9章。3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5Select语句的一般形式

3.4.1单表查询查询仅涉及一个表：一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句一、选择表中的若干列查询指定列

[例1]查询全体学生的学号与姓名。

SELECTSno，Sname FROMStudent；

该查询的执行过程是：从Student表中依次取出每个元组对每个元组仅选取Sno、Sname两个属性的值，形成一个新元组最后将这些新元组组织为一个结果关系输出

[例2]查询全体学生的姓名、学号、所在系。

SELECTSname，Sno，Sdept FROMStudent；2.查询全部列选出所有属性列：在SELECT关键字后面列出所有列名将<目标列表达式>指定为*[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；给属性列取别名可为属性列取一个便于理解的列名，如用中文来显示列名为属性列取别名特别适合经过计算的列[例3.4]查询所有学生的所属系、学号和姓名，要求用中文显示列名SELECTSdept所属系,Sno学号,Sname姓名FROMStudent该查询可使用AS关键字取别名：SELECTSdeptAS所属系,SnoAS学号, SnameAS姓名

FROMStudent3.查询经过计算的值SELECT子句的<目标列表达式>可以为：算术表达式字符串常量函数列别名[例4]查全体学生的姓名及其出生年份。SELECTSname，2004-Sage/*假定当年的年份为2004年*/FROMStudent；

输出结果：

Sname2004-Sage

李勇 1984

刘晨 1985

王敏 1986

张立 1985查询经过计算的值（续）查询经过计算的值（续）查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名:

SELECTSnameNAME，'YearofBirth:’

BIRTH,

year(GETDATE())-SageBIRTHDAY,LOWER(Sdept)DEPARTMENT FROM

Student；函数lower()将大写字母改为小写字母函数getdate()获取当前系统的日期函数year()提取日期中的年份输出结果：

NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------

李勇YearofBirth:1984cs

刘晨YearofBirth:1985is

王敏YearofBirth:1986ma

张立YearofBirth:1985is3.4.1单表查询查询仅涉及一个表：一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句二、选择表中的若干元组1.消除取值重复的行

如果没有指定DISTINCT关键词，则缺省为ALL

[例6]查询选修了课程的学生学号。

SELECTSnoFROMSC； 等价于：

SELECTALLSnoFROMSC； 执行上面的SELECT语句后，结果为：

Sno 200215121 200215121 200215121 200215122 200215122消除取值重复的行（续）指定DISTINCT关键词，去掉表中重复的行

SELECTDISTINCTSnoFROMSC；执行结果：

Sno 200215121 200215122选择运算WHERE子句可实现关系代数中的选择运算WHERE常用的查询条件有：比较运算：>、>=、<、<=、=、<>(或!=)范围查询：BETWEEN...AND集合查询：IN空值查询：ISnull字符匹配查询：LIKE逻辑查询：AND、OR、NOT(1)比较运算使用比较运算符>、>=、<、<=、=、<>(或!=)［例7］查询计算机科学系全体学生的名单。

SELECTSnameFROMStudentWHERESdept=‘CS’；

选择运算该查询的执行过程可能有多种方法：全表扫描法依次取出Student表中的每个元组判断该元组的Sdept属性值是否等于CS若是则将该元组的Sname属性取出，形成一个新元组最后将所有新元组组织为一个结果关系输出该方法适用于小表，或者该表未在Sdept属性列上建索引索引搜索法如果该表在Sdept属性列上建有索引，且满足条件的记录不多，则可使用索引搜索法来检索数据具体使用何种方法由数据库管理系统的查询优化器来选择，[例8]查询所有出生日期在2000年以前的学生姓名及其年龄。

[例8]查询所有出生日期在2000年以前的学生姓名及其年龄。

SELECTSname，SageFROMStudentWHERE

year(GETDATE())-Sage<2000；［例9］查询考试成绩有不及格的学生的学号。

SELECTDISTINCTSnoFROMSCWHEREGrade<60；（2）确定范围BETWEEN...AND用于查询属性值在某一个范围内的元组NOTBETWEEN...AND用于查询属性值不在某一个范围内的元组BETWEEN后是属性的下限值，AND后是属性的上限值[例10]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄

(3)确定集合IN用于查询属性值在某个集合内的元组NOTIN用于查询属性值不在某个集合内的元组

IN后面是集合，可以是具体的集合，也可以是查询出来的元组集合。[例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECTSname，Ssex FROMStudent WHERESdeptIN('IS'，'MA'，'CS');[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。(4)字符匹配字符匹配查询LIKE用于字符匹配查询，语法格式为：[NOT]LIKE<匹配字符串>[ESCAPE<换码字符>]查询的含义是：如果在LIKE前没有NOT，则查询指定的属性列值与<匹配字符串>相匹配的元组；如果在LIKE前有NOT，则查询指定的属性列值不与<匹配字符串>相匹配的元组。<匹配字符串>可以是一个具体的字符串，也可以包括通配符%和_%表示任意长度的字符串ab%，表示所有以ab开头的任意长度的字符串；zhang%ab，表示以zhang开头，以ab结束，中间可以是任意个字符的字符串。符号_(下划线)表示任意一个字符ab_，表示所有以ab开头的3个字符的字符串，其中第3个字符为任意字符；a__b表示所有以a开头，以b结束的4个字符的字符串，且第2、3个字符为任意字符。(4)字符匹配谓词：

[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]匹配串为固定字符串[例14]查询学号为200215121的学生的详细情况。

SELECT*FROMStudentWHERESnoLIKE‘200215121'；等价于：

SELECT*FROMStudentWHERESno='200215121'；字符匹配（续）2)匹配串为含通配符的字符串[例15]查询所有姓刘学生的姓名、学号和性别。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘刘%’；[例16]查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE'欧阳__'；字符匹配（续）[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。[例18]查询所有不姓刘的学生姓名。字符匹配（续）[例17]查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECTSname，SnoFROMStudentWHERESnameLIKE‘__阳%’；[例18]查询所有不姓刘的学生姓名。

SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'刘%'；字符匹配（续）3)使用换码字符将通配符转义为普通字符

[例19]查询DB_Design课程的课程号和学分。

SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\‘；[例20]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

ESCAPE'＼'表示“＼”为换码字符

字符匹配（续）3)使用换码字符将通配符转义为普通字符

[例20]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\‘；

ESCAPE'＼'表示“＼”为换码字符

(5)涉及空值的查询谓词：

ISNULL或ISNOTNULL“IS”不能用“=”代替

[例21]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECTSno，CnoFROMSCWHEREGradeISNULL[例22]查所有有成绩的学生学号和课程号。

SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；(6)多重条件查询逻辑运算符：AND和OR来联结多个查询条件

AND的优先级高于OR

可以用括号改变优先级可用来实现多种其他谓词

[NOT]IN[NOT]BETWEEN…AND…多重条件查询（续）[例23]查询计算机系年龄在20岁以下的学生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；多重条件查询（续）改写[例12][例12]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改写为：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；3.4.1单表查询查询仅涉及一个表：一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句三、ORDERBY子句ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示ORDERBY子句（续）[例24]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；［例25］查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。P79

［例25］查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。P79

SELECT*FROMStudentORDERBYSdept，SageDESC；3.4.1单表查询查询仅涉及一个表：一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句四、聚集函数聚集函数：计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>） 计算平均值AVG（[DISTINCT|ALL]<列名>）最大最小值

MAX（[DISTINCT|ALL]<列名>）

MIN（[DISTINCT|ALL]<列名>）聚集函数（续）[例26]查询学生总人数。

[例27]查询选修了课程的学生人数。

[例28]计算1号课程的学生平均成绩。

[例29]查询选修1号课程的学生最高分数。

聚集函数：计数COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）计算总和SUM（[DISTINCT|ALL]<列名>） 计算平均值AVG（[DISTINCT|ALL]<列名>）最大最小值

MAX（[DISTINCT|ALL]<列名>）

MIN（[DISTINCT|ALL]<列名>）聚集函数（续）[例26]查询学生总人数。

SELECTCOUNT(*)FROMStudent；

[例27]查询选修了课程的学生人数。

SELECTCOUNT(DISTINCTSno)FROMSC；

[例28]计算1号课程的学生平均成绩。

SELECTAVG(Grade)FROMSCWHERECno='1'；聚集函数（续）[例29]查询选修1号课程的学生最高分数。

SELECTMAX(Grade)FROMSCWHERCno=‘1’；［例30］查询学生200215012选修课程的总学分数。

SELECTSUM(Ccredit)FROMSC，CourseWHERSno='200215012'ANDSC.Cno=Course.Cno;3.4.1单表查询查询仅涉及一个表：一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句五、GROUPBY子句GROUPBY子句分组：细化聚集函数的作用对象未对查询结果分组，聚集函数将作用于整个查询结果对查询结果分组后，聚集函数将分别作用于每个组作用对象是查询的中间结果表按指定的一列或多列值分组，值相等的为一组例如，求每位同学的平均成绩。分组查询—GROUPBYSELECT

Sno,avg(Grade)

平均成绩

FROM

SC

group

by

SnoGROUPBY子句（续）[例31]求各个课程号及相应的选课人数。P79

SELECT