第3章关系数据库标准语言SQL

2023/2/51本章内容SQL的数据查询3.3SQL的数据操作3.43.5

SQL的数据控制

SQL概述3.1

存储过程3.7视图

本章小结3.8

SQL的数据定义3.2结构化查询语言（StructuredQueryLanguage，SQL）是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制4个方面，目前已成为关系数据库的标准语言。SQL语言简洁、方便、功能齐全，是目前应用最广的关系数据库语言之一。3.1SQL概述

3.1.1SQL语言的发展简史SQL语言的发展ChamberlinSEQUELSQL大型数据库

SybaseINFORMIXSQLServerOracleDB2INGRES小型数据库

FoxProAccess3.1.2SQL语言的主要特点数据定义语言DDL数据操纵语言DML数据控制语言DCL

一体化

高度非过程化

统一的语法结构

自含式语言嵌入式语言语言简捷,易学易用Select、create、drop、alter、insert、update、delete、grant、revoke3.1.3SQL语言的主要功能（1）数据定义语句（DataDefinitionLanguage，DDL）：用于定义关系数据库的模式、外模式和内模式，以实现对数据库基本表、视图以及索引文件的定义、修改和删除等操作。（2）数据操纵语句（DataManipulationLanguage，DML）：用于完成对数据库表数据的查询和更新操作。其中，数据更新指对数据进行插入、删除和修改操作。（3）数据控制语句（DataControlLanguage，DCL）：用于控制对数据库的访问及服务器的关闭、启动等操作。最常用的语句有GRANT、REVOKE等。

(4)数据查询SQL语言具有数据查询、数据定义、数据操纵和数据控制四种功能

3.1.4SQL对关系数据库模式的支持图3-1SQL语言支持的关系数据库的三级模式结构3.2SQL的数据定义3.2.1SQL的数据类型1．整数数值类型

（1）INT

每个INT类型的数据按4个字节存储，其中1位表示整数值的正负号，其他31位表示整数值的长度和大小，表示范围为−231～231。（2）SMALLINT。每个SMALLINT类型的数据占用2个字节的存储空间，其中1位表示整数值的正负号，其他15位表示整数值的长度和大小，表示范围为−215～215。（3）TINYINT。每个TINYINT类型的数据占用1个字节的存储空间，表示范围为0～255。（4）BIGINT。每个BIGINT类型的数据占用8个字节的存储空间，表示范围为−263～263。（5）BIT。

BIT为可以取值为1、0或NULL的整数数据类型。字符串值TRUE和FALSE可以转换为以下BIT值：TRUE转换为1，FALSE转换为0。

2．精确数值类型DECIMAL[(p[，s)]]和NUMERIC[(p[，s])]都是固定精度和小数位数（定点数）的数据类型，使用最大精度时，有效值范围为−1038+1到1038−1。NUMERIC在功能上等价于DECIMAL。p（精度）是最多可以存储的十进制数字的总位数，包括小数点左边和右边的位数。该精度必须是从1到最大精度38之间的值，默认为18；s（小数位数）是小数点右边可以存储的十进制数字的最大位数，必须是0～p之间的值，仅在指定精度后才可以指定，默认值为0。3．近似浮点数据类型浮点数据类型用于存储十进制小数。浮点数值的数据在SQL中采用上舍入（RoundUp）方式进行存储。所谓上舍入是指当（且仅当）要舍入的数是一个非零数时，对其保留数字部分的最低有效位上的数值加1，并进行必要的进位。若一个数是上舍入数，其绝对值不会减少。（1）REAL（2）FLOAT

4．货币数据类型（1）MONEY。

MONEY型数据可以存储−922337203685477.5808～922337203685477.5807之间的数。（2）SMALLMONEY。

SMALLMONEY只能存储−214

748.3648～214

748.3647之间的数。5．日期时间类型（1）DATETIME。DATETIME用两个4字节的整数存储。（2）SMALLDATETIME。SMALLDATETIME数据类型的精确度低于DATETIME，用两个2字节的整数存储。

6．字符串类型字符数据类型是使用最多的数据类型之一，它可以用来存储各种字母、数字符号、特殊符号。

(1)CHAR

(2)NCHAR

(3)VARCHAR(4)NVARCHAR(5)TEXT(6)NTEXT

7．二进制数据类型

（1）BINARY

（2）VARBINARY。

（3）IMAGE

3.2.2基本表的定义和维护（1）语句格式约定符号

尖括号“<>“中的内容为实际语句；方括号“[]”中的内容为任选项；大括号“{}”或分隔符“|”中的内容为必选其中的一项；[,…n]表示前面的项可重复多次。（2）一般语法规定

SQL中的数据项分隔符为“,”，其字符串常数的定界符用单引号“'”表示。（3）SQL特殊语法规定

SQL的关键词一般使用大写字母表示；语句的结束符为“;”。1．定义基本表CREATETABLE<表名>（<列名><数据类型>[列级完整性约束条件][,<表级完整性约束条件>]）;完整性约束的基本语法格式

[CONSTRAINT<约束名>]<约束类型>

NULL/NOTNULL

UNIQUE

PRIMARYKEY

FOREIGNKEY

CHECK2．完整性约束条件（1）NULL|NOTNULL约束

NULL表示“不知道”、“不确定”或“没有数据”的意思主键列不允许出现空值[CONSTRAINT<约束名>][NULL|NOTNULL](2)UNIQUE约束（惟一约束）

指明基本表在某一列或多个列的组合上的取值必须惟一UNIQUE用于定义列约束

[

CONSTRAINT<约束名>]UNIQUE

UNIQUE用于定义表约束[CONSTRAINT<约束名>]UNIQUE（<列名>[{,<列名>}]）

(3)PRIMARYKEY约束（主键约束）

用于定义基本表的主键，起惟一标识作用PRIMARYKEY与UNIQUE的区别：一个基本表中只能有一个PRIMARYKEY，但可多个UNIQUE对于指定为PRIMARYKEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL对于指定为PRIMARYKEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL不能为NULL不能重复PRIMARYKEY用于定义列约束

CONSTRAINT<约束名>PRIMARYKEYPRIMARYKEY用于定义表约束[CONSTRAINT<约束名>]PRIMARYKEY(<列名>[{,<列名>}])

(4)CHECK约束

CHECK约束用来检查字段值所允许的范围在建立CHECK约束时，需要考虑以下几个因素：一个表中可以定义多个CHECK约束。每个字段只能定义一个CHECK约束。在多个字段上定义的CHECK约束必须为表约束。当执行INSERT、UNDATE语句时CHECK约束将验证数据。

[CONSTRAINT<约束名>]CHECK(<条件>)

(5)FOREIGNKEY约束（外键约束）[CONSTRAINT<约束名>]FOREIGNKEYREFERENCES<主表名>(<列名>[{,<列名>}])外部键从表主键主表引用

【例3-1】

创建部门表D，由部门号（Dno）和部门（Dept）两个属性组成。

CREATETABLED(DnoCHAR(10),DeptCHAR(15));【例3-2】建立一个课程表C，由课程号（Cno）、课程名（Cn）、课时数（Ct）、开课学期（Term）、课程性质（Cx）和课程先行课（Cpno）组成，其中课程号不能为空。

CREATETABLEC(CnoCHAR(8)CONSTRAINTcno_consNOTNULL,CnCHAR(20),CtINTDEFAULT64,TermCHAR(20)NULL,CxCHAR(20),CpnoCHAR(8));【例3-3】

建立选课表SC，由学号（Sno）、课程号（Cno）和成绩（Score）组成。一个学生某一门课程的成绩应该是唯一的，即Sno+Cno为唯一键。

CREATETABLESC(SnoCHAR(8)NOTNULL,CnoCHAR(8)NOTNULL,ScoreFLOAT(1),UNIQUE(Sno,Cno));【例3-4】

创建部门表D，由部门号（Dno）、部门（Dept）两个属性组成，其中部门号为主关键字，部门名称为唯一键。

CREATETABLED(DnoCHAR(10)PRIMARYKEY, DeptCHAR(15)UNIQUE);【例3-5】创建学生表S，由学号（Sno）、姓名（Sn）、性别（Sex）、年龄（Age）、籍贯（BP）和部门号（Dno）6个属性组成，其中学号为主关系键，部门号（Dno）为部门表D的外部关系键。

CREATETABLES(SnoCHAR(10)PRIMARYKEY, SnCHAR(12), SexCHAR(4), AgeINT, BPCHAR(20), DnoCHAR(10), FOREIGNKEY(Dno)REFERENCESD(Dno));【例3-6】

创建教师表T，由工号（Tno）、姓名（Tn）、性别（Sex）、年龄（Age）、职称（Prof）和部门号（Dno）6个属性组成，其中工号为主关系键，部门号（Dno）为部门表D的外部关系键，年龄为空或取0～120之间的整数。

CREATETABLET(TnoCHAR(10)PRIMARYKEY,TnCHAR(12),SexCHAR(4),AgeINT,ProfCHAR(20),DnoCHAR(10),FOREIGNKEY(Dno)REFERENCESD(Dno),CHECK((AgeISNULL)OR(AgeBETWEEN0AND120)));3．修改基本表SQL语言用ALTERTABLE语句修改表的结构，其语法格式如下：ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束>]][DROP<完整性约束名>][MODIFY<列名><数据类型>];其中：（1）ADD子句用于添加新字段或新的完整性约束条件。（2）DROP子句用于删除已存在的完整性约束条件。（3）MODIFY子句用于修改属性的数据类型。注意：不论基本表中是否有数据，新增的列一律为空。【例3-7】

向学生表S中增加出生日期属性（BD），其数据类型为日期型。

ALTERTABLESADDBDDATE;【例3-8】

删除S表中Sno列必须取唯一值的约束。

ALTERTABLESDROPUNIQUE(Sno);【例3-9】

将学生表S中的Age属性列的数据类型改为SMALLINT。

ALTERTABLESMODIFYAgeSMALLINT;

4．删除基本表

SQL语言用DROPTABLE语句删除表，其语法格式如下：DROPTABLE<表名>

【例3-10】

删除学生表S。

DROPTABLES;

索引的作用1.索引的分类

加快查询速度保证行的惟一性聚集索引与非聚集索引唯一索引复合索引聚集索引：查询速度快非聚集索引：更新速度快排列的结果存储在表中只有一个排列的结果不存储在表中可以有多个有UNIQUE，自动建立非聚集的惟一索引有PRIMARYKEY，自动建立聚集索引将两个或多个字段组合起来建立的索引，单独的字段允许有重复的值3.2.3索引的定义和维护

非叶级Page12-RootPage37Page28叶级

（键值）Page41Page51Page61Page71Akhtar...MartinAkhtarBarrConFunkFunk4:706:014:705:034:704:014:706:024:704:02MartinSmith...SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02AkhtarGanio...GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03堆Page707Page808Page70901020304..................AkhtarFunkSmithMatey...Page704Page705Page706010203.....................ConnFunkWhite......010203.....................RuddWhiteBarr......010203.....................SmithOtaJones......01020304..................MartinPhuaJonesSmith...010203.....................GanioJonesHall......MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01非聚集索引FileID#41、非聚集索引idindid=2rootsysindexesSELECTlastname,firstnameFROMmemberWHERElastnameBETWEEN'Masters'AND'Rudd'

非叶级第12页–根第37页第28页叶级

（键值）第41页第51页第61页第71页Akhtar...MartinAkhtarBarrConFunkFunk4:706:014:705:034:704:014:706:024:704:02MartinSmith...SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02AkhtarGanio...GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03堆第707页第708页第709页01020304..................AkhtarFunkSmithMatey...第704页第705页第706页010203.....................ConnFunkWhite......010203.....................RuddWhiteBarr......010203.....................SmithOtaJones......01020304..................MartinPhuaJonesSmith...010203.....................GanioJonesHall......MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01非聚集索引FileID#4MartinMartin04...MateyMatey4:706:0402...PhuaPhua4:708:0201...RuddRudd4:705:0102...OtaOta4:707:02聚集索引第140页–根第100页第120页第130页第141页第145页AkhtarBarrConFunkFunk...23345678253413341534.....................MartinMartinOtaPhuaRudd...12347778587878786078.....................SmithSmithSmithWhiteWhite...14345778797822341634.....................AkhtarGanio…Akhtar…MartinMartinSmith…第110页GanioHallJonesJonesJones...76788078243459782634.....................2、聚集索引SELECTlastname,firstnameFROMmemberWHERElastname='Ota'聚集索引第140页–根第100页第120页第130页第141页第145页AkhtarBarrConFunkFunk...23345678253413341534.....................MartinMartinOtaPhuaRudd...12347778587878786078.....................SmithSmithSmithWhiteWhite...14345778797822341634.....................AkhtarGanio…Akhtar…MartinMartinSmith…第110页GanioHallJonesJonesJones...76788078243459782634.....................MartinOta5878...Martinidindid=1rootsysindexesLastName上的聚集索引FirstName上的非聚集索引非叶级叶级

（聚集键值）AaronDeanna…Aaron...JoseJoseNina…DeannaDonDougDaumHallHampton……AaronAdamAmieConBarrBaldwin……JoseJudyMikeLugoKaethlerNash……BarrAdamCoxDaumArletteDeanna………………KimKobaraLaBrieShaneLindaRyan………………NagataNashNixonSusanneMikeToby………………BarrKimNagataO’Melia3、使用基于聚集索引的非聚集索引查找行idindid=2rootsysindexesSELECTlastname,firstname,phoneFROMmemberWHEREfirstname='Mike'姓氏上的

聚集索引名字上的

非聚集索引非叶级叶级

（聚集键值）AaronDeanna…Aaron...JoseJoseNina…DeannaDonDougDaumHallHampton……AaronAdamAmieConBarrBaldwin……JoseJudyMikeLugoKaethlerNash……BarrAdamCoxDaumArletteDeanna………………KimKobaraLaBrieShaneLindaRyan………………NagataNashNixonSusanneMikeToby………………BarrKimNagataO’MeliaMikeNashNagataNashMike…

CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[次序][{,<列名>}][次序]…)

2．建立索引建立惟一索引

建立聚集索引

ASC或DESC，默认为ASC【例3-11】在学生表S的姓名（Sn）列上建立一个聚集索引Studentname，且表S中的记录按照Sn值的升序存放。

CREATECLUSTERINDEXStudentnameONS(snASC);【例3-12】

为表SC在Sno和Cno上建立一个唯一索引SCI。

CREATEUNIQUEINDEXSCIONSC(sno,cno);DROPINDEX数据表名.索引名

3．删除索引不能删除由CREATE或ALTER命令创建的索引，也不能删除系统表中的索引【例3-13】

删除表S中的Studentname索引。

DROPINDEXStudentname;3.3SQL数据查询

SELECT命令的格式与基本使用

SELECT[ALL|DISTINCT][TOPN[PERCENT][WITHTIES]] 〈列名〉[AS别名1][{，〈列名〉[AS别名2]}] [INTO新表名]

FROM〈表名1或视图名1〉[[AS]表1别名][{，〈表名2或视图名2〉[[AS]表2别名]}] [WHERE〈检索条件〉] [GROUPBY<列名1>[HAVING<条件表达式>]] [ORDERBY<列名2>[ASC|DESC]]投影选取3.3.1单表查询

1．选择表中的若干列选择列即为关系代数中的投影操作，得到的目标列为表中的部分或全部列。（1）选择列。【例3-14】

查询全体学生的学号与姓名。

SELECTSno,SnFROMS;SnoSn100101姜珊100102李思100103孙浩100104周强100105李斌100106黄琪【例3-15】

查询全体学生的所有字段信息

SELECT*FROMS;

等价于：

SELECTSno,Sn,Sex,Age,BP,DnoFROMS（2）用DISTINCT删除重复的行。

【例3-16】

查询选课表SC中无重复的学号。

SELECTDISTINCTSnoFROMSC;Sno100101100102100103【例3-17】

查询全体学生的姓名、出生年份、籍贯，要求用中文表示所有属性。

SELECTSnAS姓名,2010−AgeAS出生年份,BPAS籍贯FROMS;姓名出生年份籍贯姜珊1992湖南李思1993江苏孙浩1989江苏周强1990新疆李斌1991河南黄琪1989湖北（3）修改查询结果中的列标题字段名|表达式|函数名AS别名运算符含义=、>、<、>=、<=、!=、<>比较大小BETWEEN…AND…确定范围AND、OR、NOT多重条件。也可以把AND、OR和NOT结合起来，使用圆括号来组成复杂的表达式IN|NOTIN确定集合LIKE|NOTLIKE字符匹配。Like是用于查找与指定字符串相匹配的字符串，可使用通配符%与_，一个_只代表1个字符，一个%可代表多个字符。注意：只允许在Like子句中使用通配符ISNULL空值表3-1WHERE子句中条件表达式常用的运算符

2．选择表中的若干行

表中查询满足条件的行可通过WHERE子句来实现。（1）表达式比较【例3-18】

查询性别为“女”的学生的学号与姓名。

SELECTCno,CnFROMWHERESex='女';SnoSn100101姜珊100102李思【例3-19】

查询年龄在20岁以下的学生的情况。

SELECT*FROMSWHEREage<20;（2）确定范围BETWEEN…AND…和NOTBETWEEN…AND…用来确定查询范围，意指“在…和…之间”或“不在…和…之间”的数据。【例3-20】

查询年龄在18～20之间的学生的情况。

SELECT*FROMSWHEREAgeBETWEEN18AND20;（3）多重条件查询。

使用逻辑运算符AND、OR和NOT,其优先级由高到低为NOT、AND、OR，但用户可以使用括号改变优先级。【例3-21】

查询年龄在18～20之间的女生的情况。

SELECT*

FROMS

WHERE(AgeBETWEEN18AND20)ANDSex='女';（4）确定集合。

IN|NOTIN运算符用于查找列值属于指定集合的元组。【例3-22】

查询选修了课程号为“150101”或“150102”的学生的学号、课程号和成绩。

SELECTSno,Cno,ScoreFROMSC

WHERECnoIN('150101','150102');

等价于：SELECTSno,Cno,ScoreFROMSCWHERECno='150101'ORCno='150102';（5）字符串匹配当不知道完全精确的值时，用户可以使用LIKE或NOTLIKE进行部分匹配查询（也称模糊查询)<属性名>LIKE<字符串常量>

通配符功能实例%代表0个或多个字符ab%,ab后可接任意字符串_（下划线）代表1个字符a_b,a与b之间可有1个字符[]表示在某一范围的字符[0～9]：0～9之间的字符[^]表示不在某一范围的字符[^0～9]：不在0～9之间的字符表3-2通配符及其功能【例3-23】

查询所有姓“张”的教师的教师号和姓名

SECLECTTno,TnFROMTWHERETnLIKE'张%';（6）涉及空值查询。某个字段没有值称之为具有空值（NULL）空值不同于零和空格，它不占任何存储空间【例3-24】查询没有考试成绩的学生的学号和相应的课程号。

SELECTSno,Cno

FRONSC

WHEREScoreISNULL;注意：这里的空值条件为ScoreISNULL，不能写成Score=NULL3．对查询结果进行排序

使用ORDERBY子句即可实现【例3-26】

查询所有教师的姓名、性别、年龄、职称，并按照年龄降序排列，年龄相同的按照职称降序排列

SELECTTn,Sex,Age,ProfFROMTORDERBYAgeDESC,ProfDESC;4．使用库函数及统计汇总查询函数名功能COUNT([DISTINCT|ALL]*)统计元组数量COUNT([DISTINCT|ALL]<列名>)统计一列中值的数量SUM([DISTINCT|ALL]<列名>)计算一列值的总和（此列必须是数值型）MAX([DISTINCT|ALL]<列名>)计算一列值的平均值（此列必须是数值型）AVG([DISTINCT|ALL]<列名>)求一列值中的最大值MIN([DISTINCT|ALL]<列名>)求一列值中的最小值【例3-27】

求学号为“100101”的学生的总分和平均分SELECTSUM(Score)ASTOTAL,AVG(Score)ASAVG

FROMSCWHERE(Sno='100101');【例3-28】

求信电学院学生的总数

SELECTCOUNT(Sno)FROMSWHEREDEPT='信电';

或

SELECTCOUNT(*)FROMSWHEREDEPT='信电';注意：二者的区别是，COUNT(*)统计的是行数，而COUNT(Sno)统计的是Sno具有值（非空）的行数。本例中Sno是主关系键，其值不能为空，所以二者等价。其他情况下，二者不一定等价。【例3-31】统计教师人数在2人以上的职称名称。

SELECTCOUNT(*)AS人数，ProfFROMTGROUPBYProfHAVINGCOUNT(*)>1;【例3-32】查询选修课程在3门以上，并且各门课程均及格的学生学号和平均成绩，查询结果按照平均成绩降序输出。

SELECTSno,AVG(Score)AS平均成绩FROMSC

WHEREScore>=60GROUPBYSnoHAVINGCOUNT(*)>=3ORDERBY2DESC;5．对查询结果进行分组计算

使用GROUPBY子句和HAVING子句实现。

3.3.2连接查询连接查询：一个查询需要对多个表进行操作表之间的连接：连接查询的结果集或结果表连接字段：数据表之间的联系是通过表的字段值来体现的连接操作的目的：从多个表中查询数据表的连接方法:

表之间满足一定条件的行进行连接时，FROM子句指明进行连接的表名，WHERE子句指明连接的列名及其连接条件利用关键字JOIN进行连接：当将JOIN关键词放于FROM子句中时，应有关键词ON与之对应，以表明连接的条件INNERJOIN显示符合条件的记录，此为默认值LEFT（OUTER）JOIN为左（外）连接，用于显示符合条件的数据行以及左边表中不符合条件的数据行，此时右边数据行会以NULL来显示RIGHT（OUTER）JOIN右（外）连接，用于显示符合条件的数据行以及右边表中不符合条件的数据行。此时左边数据行会以NULL来显示FULL（OUTER）JOIN显示符合条件的数据行以及左边表和右边表中不符合条件的数据行。此时缺乏数据的数据行会以NULL来显示CROSSJOIN将一个表的每一个记录和另一表的每个记录匹配成新的数据行JION的分类

【例3-33】查询每个学生及其选修课程的情况。

SELECTS.*,SC.*FROMS,SCWHERES.sno=SC.sno;

采用JOIN连接查询的SQL语句为：

SELECTS.*,SC.*FROMSINNERJOINSCONS.sno=SC.sno;

注意：连接运算可以使用关系代数中的连接运算1．WHERE连接查询方式

2．JOIN连接查询方式

SELECT[选取字段|表达式]…

FROM<表名>INNER|LEFTOUTER|RIGHTOUTER|FULLOUTER|CROSSJOIN<表名>ON连接条件

【例3-34】

现在有两个表：St和Sc（如下所示），则INNER、LEFT、RIGHT、FULLOUTER连接查询的结果如图3-13所示。St表Sc表SnuSnAgeSnuCnuScore100101姜珊1810010115010190100102李思1710010215010286100103孙浩211001011501038810010415010365图3-12自然连接查询结果图SnuSnAgeCnuScore100101姜珊1815010190100102李思1715010286100102李思1715010388（a）SELECT*FROMStINNERJOINScONSt.Snu=Sc.SnuSnuSnAgeCnuScore100101姜珊1815010190100102李思1715010286100102李思1715010388100103孙浩21（b）SELECT*FROMStLEFTJOINScONSt.Snu=Sc.SnuSnuSnAgeCnuScore100101姜珊1815010190100102李思1715010286100102李思171501038810010415010365（c）SELECT*FROMStRIGHTJOINScONSt.Snu=Sc.SnuSnuSnAgeCnuScore100101姜珊1815010190100102李思1715010286100102李思1715010388100103孙浩2110010415010365

（d）SELECT*FROMStFULLJOINScONSt.Snu=Sc.Snu【例3-35】

假设课程表C的结构如下，查询每一门课程的间接先修课（即先修课的先修课）。CnuCno课程名Ct课时Cpno先修课程150101数据结构64150104150102操作系统48150103150103数据库原理64150101150104离散数学64150105150105高等数学803．自身连接查询自身连接是指相互连接的表物理上为同一张表，要为这张表取两个别名以方便操作。方法1:

SELECTfirst.Cno,second.Cpno

FROMCASfirst,CASsecond

WHEREfirst.Cpno=second.Cno;方法2:

SELECTfirst.Cno,second.Cpno

FROMCASfirstINNERJOINCASsecond

ONfirst.Cpno=second.Cno;查询结果如图3-14所示。CnoCpno1501011501051501021501011501031501044.外连接

而在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列。符合连接条件的数据将直接返回到结果集中，对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中。【例3-36】

查询所有学生的选课情况（没有选课的学生只输出学生的基本信息）。方法一：使用WHERE连接。

SELECTS.Sno,Sn,Sex,Age,Dno,SC.Cno,ScoreFROMS,SCWHERES.Sno=SC.Sno(*);

方法二：使用JOIN连接。

SELECTS.Sno,Sn,Sex,Age,Dno,SC.Cno,ScoreFROMSLEFTJOINSCONS.Sno=SC.Sno;左外部连接右外部连接SnuSnSexAgeDeptCnoScore100101姜珊女180115010160100101姜珊女180115010270100102李思女170215010280100103孙浩男210315010372100104周强男2004100105李斌男1901100106黄琪男2102图3-15外连接查询结果3.3.3嵌套查询

在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询。返回一个值的子查询使用比较运算符（=,>,<,>=,<=,!=）嵌套查询通常分为不相关子查询和相关子查询两类。（1）不相关子查询。子查询的查询条件不依赖于父查询。子查询可以独立运行，并且只执行一次，执行完毕后将值传递给外部查询。【例3-37】查询选修“150102”号课程的学生姓名。

SELECTSnFROMSWHERESnoIN(SELECTSnoFROMSCWHERECno='150102');（2）相关子查询子查询的查询条件依赖于父查询。子查询不能独立运行，必须依靠父查询数据，并且外部查询执行一行，子查询就执行一次。【例3-38】

查询每个部门中教师年龄大于本部门学生平均年龄的教师的姓名、部门号和年龄。

SELECTTn,Dept,AgeFROMTWHEREAge>(SELECTAVG(Age)FROMSWHERET.Dept=S.Dept);

嵌套查询中最常见的是不相关子查询1．带有IN谓词的子查询

带有IN谓词的子查询是指父查询和子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。【例3-39】

查询与“王红”职称相同的教师。本查询可以分成如下两步完成：（1）确定“王红”的职称。

SELECTProfFROMTWHERETn='王红';（2）确定与“王红”职称相同的教师。

SELECTTnFROMTWHEREProfIN(SELECTProfFROMTWHERETn='王红'）；使用复合连接条件查询来实现：

SELECTt1.TnFROMTASt1,TASt2WHEREt1.Prof=fANDt2.Tn='王红';2．带有比较运算符的子查询使用>、<、=、>=、<=、!=或<>等比较运算符。带有比较运算符的子查询是指父查询和子查询之间用比较运算符进行连接。

【例3-41】查询与“王红”职称相同的教师。

SELECTT.TnFROMTWHEREProf=(SELECTProfFROMTWHERETn='王红');

3．带有ANY或ALL谓词的子查询当子查询的返回值为一个集合时，除了可以使用IN连接词以外，还可以使用ANY或ALL谓词。注意，使用ANY或ALL谓词时必须同时使用比较运算符。表达式含义>ANY大于子查询中的某个值<ANY小于子查询中的某个值>=ANY大于等于子查询中的某个值<=ANY小于等于子查询中的某个值=ANY等于子查询中的某个值!=ANY或<>ANY不等于子查询中的某个值>ALL大于子查询中的所有值<ALL小于子查询中的所有值>=ALL大于等于子查询中的所有值<=ALL小于等于子查询中的所有值=ALL等于子查询中的所有值!=ALL或<>ALL不等于子查询中的所有值【例3-42】

查询其他部门中比“信电学院”任意一个（其中某一个）学生年龄小的学生的姓名和年龄。方法1:

SELECTSn,AgeFROMSWHEREDept<>'信电'ANDAge<ANY(SELECTAgeFROMSWHEREDept='信电');

方法2:

SELECTSn,AgeFROMSWHEREdept<>'信电'ANDAge<(SELECTMAX(Age)FROMSWHEREdept='信电');【例3-43】

查询其他部门中比“信电学院”所有学生年龄都小的学生的姓名和年龄。方法1:

SELECTSn,AgeFROMSWHEREDept<>'信电'ANDAge<ALL(SELECTAgeFROMSWHEREDept='信电');方法2：

SELECTSn,AgeFROMSWHEREDept<>'信电'ANDAge<(SELECTMIN(Age)FROMSWHEREDept='信电');4．带有EXISTS谓词的子查询

带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。当子查询的的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。NOTEXISTS与此相反。含有IN的查询通常可用EXISTS表示，但反过来不一定。【例3-44】

查询所有选修了“150102”号课程的学生的姓名和部门号。

SELECTSn,DeptFROMSWHEREEXISTS(SELECT*FROMSCWHERESno=S.SnoANDCno='150102');【例3-45】

查询所有选修了全部课程的学生的姓名和部门。

SELECTsn,deptFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESno=S.SnoANDCno=C.Cno));

3.3.4集合查询并操作UNION、交操作INTERSECT和差操作MINUS。SQLServer的Transact-SQL语言只提供UNION运算符，实现并操作。

1．UNION操作使用UNION操作符将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。参加合并查询的各子查询的使用的表结构应该相同。【例3-46】

查询年龄在30岁以上和部门号为“01”的所有教师的情况。

SELECT*FROMTWHEREAge>30UNIONSELECT*FROMTWHEREDno='01';

2．集合差/集合交运算

EXCEPT运算符可以实现集合差操作，即从左查询中返回右查询没有找到的所有非重复值。INTERSECT运算符可实现集合交操作，即返回INTERSECT操作符左右两边的两个查询都返回的所有非重复值。【例3-47】

查询所有非信电学院的学生。

SELECT*FROMSEXCEPTSELECT*FROMSWHEREDno=(SELECTDnoFROMDWHEREDept='信电');【例3-48】

查询所有职称为“讲师”的女教师。方法1:

SELECT*FROMTWHERESex='女'INTERSECTSELECT*FROMTWHEREProf='讲师';方法2:

SELECT*FROMTWHERESex='女'ANDProf='讲师';

3.4SQL的数据操作数据操作添加数据（INSERTINTO）修改数据（UPDATE）删除数据（DELETE）

3.4.1插入数据用SQL命令插入数据

INSERTINTO

1．插入单个元组INSERTINTO<表名>[(<列名1>[,<列名2>…])]VALUES(<值>)

【例3-50】

在学生表S中插入一条学生记录（学号：100108，姓名：朱良）。

INSERTINTOS(Sno,Sn)VALUES('100108','朱良');必须用逗号将各个数据分开，字符型数据要用单引号括起来。如果INTO子句中没有指定列名，则新添加的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。INSERTINTO<表名>[(<列名1>[,<列名2>…])]子查询

2．插入多个元组【例3-51】

将每一个部门学生的平均年龄存入新表SAVGA中（本表包括两个属性列：部门号（Dno）和平均年龄（Avgage））。

CREATETABLESAVGA(DnoCHAR(15)AvgageINT);INSERTINTOSAVGASELECTDno,AVG(Age)FROMSGROUPBYDno;3.4.2修改数据用SQL命令修改数据 UPDATE<表名> SET<列名>=<表达式>[,<列名>=<表达式>]… [WHERE<条件>]

1．修改单个或部分元组的值

【例3-52】

将学生“100107”的年龄改为22岁。

UPDATESSETAge=22WHERESno='100107';2．修改全部元组的值【例3-53】

将所有学生的年龄增加1岁。

UPDATESSETAge=Age+1;3．利用子查询修改部分元组的值【例3-54】

将信电学院全体学生的成绩置0。

UPDATESCSETScore=0WHERESnoIN(SELECTSnoFROMSWHEREDno=(SELECTDnoFROMDWHEREDept='信电'));3.4.3删除数据DELETEFROM<表名>[WHERE<条件>]1．删除单个或多个元组【例3-55】

删除学号为“100107”和“100108”的学生的记录。

DELETEFROMS

WHERESno='100107'ORSno='100108';

2．删除全部元组的值

【例3-56】

删除所有学生的选课记录。

DELETEFROMSC;3．带子查询的删除语句子查询同样也可以嵌套在DELETE语句中，用以构造执行删除操作的条件。

【例3-57】

删除信电学院所有学生的选课记录。

DELETEFROMSCWHERESnoIN(SELECTSnoFROMSWHEREDno=(SELECTDnoFROMDWHEREDept='信电'))；3.5视图视图是虚表，其数据不进行存储，其记录来自基本表，只在数据库中存储其定义。3.5.1建立视图CREATEVIEW<视图名>[(<视图列表>)] AS<子查询>

【例3-58】

建立信电学院教师的视图，并要求进行修改和插入操作时仍保持该视图只有信电学院教师。

CREATEVIEWITteacherASSELECTTno,Tn,ProfFROMTWHEREDno=(SELECTDnoFROMDWHEREDept='信电')WITHCHECKOPTION;

2．多表视图建立在多个基本表（视图）上，这样的视图称为多表视图。

【例3-59】

建立选修了“150102”号课程的所有女生的视图。

CREATEVIEWFStu(Sno,Sn,Sex)ASSELECTS.Sno,Sn,Sex,BPFROMS,SCWHERESex='女'ANDS.Sno=SC.SnoANDSC.Sno='150102';

【例3-60】

建立信电学院所有职称为“讲师”的教师及所任课程的视图。

CREATEVIEWITea(Tno,Tn,TC.Cno)ASSELECTITteacher.Tno,Tn,TC.cnoFROMITteacher,TCWHEREITteacher.Tno=TC.TnoANDITteache.Prof='讲师';

3.带表达式的视图定义视图时可以根据应用的需要，设置一些派生属性列。因这些派生属性在基本表中并不实际存在，所以有时也称它们为虚拟列。带虚拟列的视图称为带表达式的视图。

【例3-61】

定义一个反映学生出生年份的视图。

CREATEVIEWSBir(Sno,Sn,Sbirth) AS SELECTSno,Sn,2010-S.Age FROMS4．分组视图

使用带有集函数和GROUPBY字句的查询来定义的视图称为分组视图。

【例3-62】定义一个反映学生的学号及其平均成绩的视图。

CREATEVIEWStuAvg(Sno,Savg)ASSELECTSno,AVG(Score)FROMSCGROUPBYSno;3.5.2查询视图视图定义后，对视图的查询操作如同对基本表的查询操作一样。

【例3-63】

查找视图ITteacher中职称为“教授”的教师号和姓名。

方法1：SELECTTno,TnFROMITteacherWHERE(Prof='教授')方法2：SELECTTno,TnFROMTWHERE(Prof='教授'ANDDno=(SELECTDnoFROMDWHEREDept='信电'));视图的建立简化了查询操作3.5.3删除视图视图建好后，若删除导出此视图的基本表，该视图也将失效，但一般不会被自动删除。删除视图通常需要显式地使用DROPVIEW语句进行。

DROPVIEW<视图名>【例3-64】

删除视图Itteacher。

DROPVIEWITteacher注意：执行该语句后，ITteacher视图的定义将从数据字典中删除。由ITteacher视图导出的ITea视图的定义虽然在数据字典中，但该视图已无法使用，所以也应该删除。3.5.4更新视图由于视图是一张虚表，所以对视图的更新，最终转换成对基本表的更新。其语法格式如同对基本表的更新操作一样。视图的优点

添加修改

删除INSERTUPDATE

DELETE利于数据保密

简化查询操作保证数据的逻辑独立性1．UPDATE操作

【例3-65】

将信电学院教师的视图中的工号为“05”的教师的姓名修改为“张宾”。方法1:

UPDATEITteacherSETTn='张宾'WHERETno='05';方法2:

UPDATETSETTn='张宾'WHERETno='05'ANDdept='信电';2．INSERT操作

【例3-66】

向信电学院教师的视图插入一条新记录（“07”，“张平”，“副教授”）。

INSERTINTOITteacherVALUES('07','张平','副教授');

DBMS自动将其转换为对基本表T的更新，因为本视图是信电学院教师的视图，所以系统自动会将部门号“01”放入VALUES子句中，更新语句为：

INSERTINTOT(Tno,Tn,Prof,Dno)

VALUES('07','张平','副教授','01');3．DELETE操作【例3-67】

将向信电学院教师的视图中插入的记录（“07”，“张平”，“副教授”）删除。

DELETEFROMITteacherWHERETno='07';DBMS将其转换为对基本表的更新，同理根据例3-66要将表达式“Dept='信电'”放入WHERE条件表达式中，转换后的更新语句为：

DELETEFROMTWHERETno='07'ANDDno=(SELECTDnoFROMDWHEREDept='信电'));

3.5.5视图的作用1．视图能使用户以多种角度看待同一数据2．视图能够简化用户的操作3．视图为重构数据库提供一定程度逻辑独立性视图可以使应用程序和数据库表在一定程度上独立。4．视图能够为机密数据提供安全保护

3.6SQL的数据控制1．SQL的用户

SQL中的用户分为两种：SQL服务器用户（DBA）和数据库用户。在SQLServer中，有3种特殊的用户：DBA、DBO和一般用户。DBA对整个系统有操作权限；DBO对其所建立的数据库具有全部操作权限；一般用户对给定的数据库只有被授权的操作权限。3.6.1授权权限

角色角色是多种权限的集合，当要为某一用户同时授予或收回多项权限时，则可以把这些权限定义为一个角色。这样就简化了管理数据库用户权限的工作。系统权限：数据库用户能够对数据库系统进行某种特定的操作的权力对象权限：数据库用户在指定的数据库对象上进行某种特定的操作的权力

如：查询、添加、修改和删除2．权限与角色3．用户授权GRANT<权限>|<角色>[,<权限>|<角色>]…[ON对象类型><对象名>]TO<用户名>|<角色>|PUBLIC[,<用户名>|<角色>]…[WITHGRANTOPTION];【例3-68】

把对表S的INSERT和UPDATE权限授予用户User1和User2，并允许他们将此权限授予其他用户。

GRANTINSERT,UPDATEONTABLESTOUser1,U