第04章 SQL Server与数据查询

SQLServer与数据查询

第4章4.1概述4.2SQLServer的数据表达4.3基本查询4.4复杂查询4.5实例与分析小结4.1SQL概述

4.1.1SQL的特点4.1.2实例数据库4.1.1SQL的特点

1、综合统一

2、高度非过程化

3、面向集合的操作方式

4、一种语法，两种使用方式

6、支持三级模式结构

5、语言简捷，易学易用

SQL特点1:综合统一

SQL语言将数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能集于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动SQL特点2：高度非过程化用SQL语言进行数据操作时，只要提出“做什么”，而无需指明“怎么做”。SQL特点3：

面向集合的操作方式SQL语言操作的对象和操作的结果都用关系表示。SQL特点4:

一种语法，两种使用方式

SQL语言既是自含式语言，又是嵌入式语言。SQL特点5:

语言简捷,易学易用

完成核心功能只用9个动词，SQL语言接近英语句子。SQL特点6:支持三级模式结构SQL命令视图1视图2基本表1基本表2基本表3基本表4存储文件1存储文件2外模式模式内模式SQL特点6：支持三级模式结构

外模式对应于视图(View)，模式对应于基本表，内模式对应于存储文件。其中：⑴基本表。基本表是数据库中独立存在的表，每个基本表对应一个关系模式和关系。基本表由SQL语言的CREATETABLE命令创建。一个（或多个）基本表对应一个存储文件，一个基本表可以建立若干索引，它们都依附于基本表且存放在存储文件中。⑵视图（虚拟表）。视图对应于外模式(用户模式)，它是由一个或几个基本表导出的表——虚拟表，由SQL语言的CREATEVIEW命令创建。视图中存放着视图的定义及其关联的基本表名称等信息而不存放视图对应的具体数据。用户可以在视图上再定义视图。⑶存储文件。若干个基本表组成一个存储文件。存储文件的逻辑结构组成了关系数据库的内模式。视图机制的主要优点:

①简化用户的操作。 ②多角度看待同一数据。 ③视图为数据库重构提供了一定程度的逻辑独立性。 ④对数据安全保护。4.1.2实例数据库本章为了举例方便，我们定义学生选课数据库的三个关系如下表：表4-1关系StudentSNoSName

SSex

SAge

SDept

S01S02S03S04S05S06王建平刘华范林军李伟黄河长江男女女男男男211918191820自动化自动化计算机数学数学数学4.1.2实例数据库CNo

CName

CPNo

CreditC01C02C03C04C05C06C07英语数据结构数据库DB_设计C++网络原理操作系统C05C02C03C07C054223333SNo

CNo

GradeS01S01S02S02S02S03S03S04C01C03C01C02C03C01C02C039284909482729075表4-2关系Course表4-3关系SC4.1.2实例数据库表中具体意思解释：1.表4-1是学生基本信息表，各属性的含义为：

SNo：学号，SName：姓名，SSex：性别，Sage：年龄：SDept：系别2.表4-2是课程信息表，各属性的含义为：

CNo：课程号，CName：课程名，CPNo：前导课，Credit：学分3.表4-3是成绩信息表，各属性的含义为：SNo：学号，CNo：课程号，Grade：成绩4.2SQLServer的数据表达4.2.1SQLServer的数据类型4.2.2SQL的数据表达

4.2.3

完整性约束的SQL实现

4.2.1SQLServer的数据类型

BINARY：每个字符占1个字节。BIT：1个位或者1个字节。TINYINT：1个字节。表示0到255之间的整数。MONEY：8个字节。DATETIME：8个字节。表示1000年到9999年的日期或者时间。REAL：4个字节单精度浮点数。FLOAT：8个字节双精度浮点数。SMALLINT：2个字节的短整数。INTEGER：4个字节的长整数。DECIMAL：17个字节的数字数据类型。TEXT：每一字符两字节。IMAGE：视实际需要而定。CHARACTER：每一字符两字节。长度从0到255个字符。1.SQLServer的基本数据类型：

SQLServer的数据类型有30多种，其中13种基本数据类型如下：

4.2.1SQLServer的数据类型2.SQLServer数据的分类用法(1)二进制数据类型二进制数据包括：Binary、Varbinary和Image。(2)字符数据类型字符数据类型包括：Char，Varchar和Text。(3)Unicode数据类型Unicode类型包括：Nchar、Nvarchar和Ntext。(4)日期和时间数据类型日期和时间数据类型包括：Data、Time、Datetime和Smalldatetime等。(5)数字数据类型数字数据类型包括正数和负数、小数（浮点数）和整数。(6)货币数据表示正的或者负的货币数量。货币数据的数据类型是：Money和Smallmoney。(7)特殊数据类型3种特殊数据类型：Timestamp、Bit和Uniqueidentifier。4.2.2SQL的数据表达SQLServer的数据表达可以通过表达式实现。表达式是标识符、值和运算符的组合，SQLServer可以对其求值以获取结果。表达式是常量、函数、列名、变量、子查询、CASE、NULL、IF及其组合。4.2.2SQL的数据表达例如：在以下SELECT语句中，对于结果集中的每一行，SQLServer可以将LastName

解析为一个值。因此，它是一个表达式。USEAdventureWorksSELECTLastNameFROMAdventureWorks.Person.Contact表达式可以是计算。例如：Price*1.5；或Price+Sales_tax。在表达式中，请用单引号将字符和Datetime值括起来。4.2.2SQL的数据表达例如：在以下SELECT语句中，LIKE子句的字符Bai%必须用单引号括起来。USEAdventureWorksSELECTLastName,FirstName

FROMAdventureWorks.Person.Contact

WHERELastNameLIKE'Bai%'4.2.3完整性约束的SQL实现

SQLServer把各种完整性约束作为数据库模式定义的一部分，由DBMS维护，这样既有效保护了数据库，提高完整性检测的效率，又减轻了编程人员的负担。SQLServer支持三种完整性约束：实体完整性和主码（1）属性后增加关键字CREATETABLEStudentInfo(StudentIDCHAR(8)PRIMARYKEY,

StudentNameVARCHAR(10),StudentSexBIT)（2）加入定义主码子句CREATETABLEStudentInfo(StudentID

char(8),

StudentName

varchar(10),Studentex

bit,

PRIMARYKEY(StudentID))提示：如果主码只含有单个属性，上面的两种方法都可以使用。如果主码由多个属性组成，只能使用第二种方法。4.2.3完整性约束的SQL实现2.参照完整性和外码在更新数据时，参照完整性保持表间已定义的关系。参照完整性基于外码与主码之间或者外码与唯一码之间的关系，确保码值在所有表中一致。强制参照完整性时，SQLServer禁止用户进行下列操作：

当主表中没有关联的记录时，将记录添加到相关表中。

更改主表中的值并导致相关表中的记录孤立。

从主表中删除记录，但仍存在与该记录匹配的相关记录。4.2.3完整性约束的SQL实现（1）外码约束的说明：说明外部码的方法有两种：a．在属性说明后直接加上REFERENCES，后跟对应表的主码说明。b．在CREATETABLE语句的属性清单后，加上外码的说明子句。4.2.3完整性约束的SQL实现（2）参照完整性约束的实现策略：当用户的操作违反了上述规则时，SQLServer提供了两种可选方案供数据库实现者使用：RESTRICT（限制策略）和CASCADE(级联策略)。a．限制策略：SQLServer的默认策略，任何违反参照完整性的更新均被系统拒绝。b．级联策略：当用户删除或者更新外码指向的键时，通过在REFERENCES子句后添加ONDELETE和ONUPDATE子句实现。4.2.3完整性约束的SQL实现3.用户定义完整性约束SQLServer提供非空约束、属性的CHECK约束、元组的CHECK约束、约束（Constraint）和触发器（Trigger）等来实现用户的完整性要求。（1）属性的CHECK约束：使用CHECK子句可保证属性满足某些前提条件。（2）基于元组的约束：CHECK约束涉及到表中的多个属性。4.3SQL的数据查询

4.3.1基本SQL语句4.3.2查询语句4.3.1基本SQL命令

1.向表中添加单个INSERTINTO<表名>[(<属性列1>[，<属性列2>…]]VALUES(<常量1>[，<常量2>]…)；说明：该命令的功能是将一个新元组添加到指定的表中,且新元组属性列1的值为常量1，属性列2的值为常量2，…。

INTO子句中没有出现的属性列，其新元组在这些列上将取空值。但若在CREATE定义表时使用了NOTNULL约束的属性列不能取空值，否则会出错。

如果INTO子句中没有指明任何列名，则新插入的元组必须在每个属性列上均有值。4.3.1建立与删除索引

2建立索引：使用CREATEINDEX命令，其语句格式为：CREATE[UNIQUE][CLUSTER]INDEX<索引名>

ON<表名>(<列名>[<排序方式>][，<列名>[<排序方式>]]…)；

说明：(1)<表名>是要为其创建索引的基本表的名字。索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>还可以用<排序方式>来指定索引值按照ASC(升序)或DESC(降序)的方式排序，缺省值为ASC。(2)UNIQUE表明每一个索引值只对应唯一的一个元组，即索引值相同的元组只索引一次。(3)CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中元组的物理顺序一致的索引组织。4.3.1删除索引

3.使用DROPINDEX语句，其一般格式为：

DROPINDEX<索引名>；4.3.2查询语句SELECT语句的一般格式：SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；说明：根据WHERE子句的条件，从FROM子句指定的基本表或视图中找出满足条件的元组，再按目标列表达式规定的属性列选出元组中对应的属性值形成结果表。如果有GROUP子句，则将结果按<列名1>的值进行分组。如果GROUP子句带HAVING短语，则只输出满足指定条件的组。如果有ORDER子句，则结果表还要按<列名2>的值升序或降序方式排序。1.简单的选择与投影查询

无条件查询

条件查询

查询结果排序

集函数的使用

查询结果分组无条件查询

在SELECT语句中不使用[WHERE<条件表达式>]字句。例1查询全体学生的详细记录。即无条件选择。SELECT*FROMStudent

--这里“*”代表所有属性，All代表所有元祖例2查询全体学生的姓名(SName)、学号(SNo)、所在系(SDept)。即无条件的投影。SELECTSName，SNo，SDeptFROMStudent此例表明，查询结果输出列的顺序可以按照用户要求改变，而不必与表的顺序一样。条件查询（1）在SELECT语句中使用[WHERE<条件表达式>]子句。常见的条件有：(1)比较条件。例如：

whereSdept=‘数学’；

whereSage>18ANDSage<=22;(2)谓词条件。例如：whereSageBETWEEN18AND22；whereSage>18NOTBETWEEN18AND22;

whereSdeptIN(‘自动化’,‘数学’,’计算机’)；条件查询（2）还有用LIKE和NOTLIKE构成谓词条件的语法格式如下：[NOT]LIKE’<匹配串>’[ESCAPE’<换码字符>’]其含义是查找指定的属性列值与<匹配串>向匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和-,其意义如下：

①%(百分号):

代表任意长度(长度可以为0)的字符串。例如a%b表示以a开头，以b结尾的任意长度的字符串。如acb，abcdb，ab等都满足该匹配串。 ②_(下横线):

代表任意单个字符。例如a_b

表示以a开头，以b结尾的长度为3的任意字符串。如acb，adb等都满足该匹配串。此外，还有使用ISNULL和ISNOTNULL谓词进行有关空值判断的谓词条件，如：

WHEREGradeISNULL;注意：以上的“IS”不能用等号“=”代替。查询结果排序使用ORDERBY字句以及DESC或ASC命令关键字。例

查询选修了C03号课程的学生的学号(SNo)和成绩(Grade)，并按成绩降序排列。SELECTSNo，GradeFROMReoprtsWHERECNo='C03'ORDERBYGradeDESC对于空值，若按升序排，含空值的元组最先显示。若按降序排，空值的元组最后显示。使用集函数

在SELECT语句中使用COUNT()、AVG等集函数例

计算选修C01号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECNo='C01'注意，成绩为空值的学生不参与计算。查询结果分组在SELECT语句中使用GROUPBY字句将查询结果表按照某一列或多列的值分组，使其列值相等的为一组。还可以用HAVING短语指定结果组满足的条件。例

查询选修了3门或3门以上课程的学生学号(SNo)。SELECTSNoFROMSCGROUPBYSNoHAVINGCOUNT(*)>=3GROUPBY子句先按SNo进行分组，再用集函数COUNT对每一组计数。HAVING指定选择组的条件，只有满足条件(COUNT(*)>=3，表示选课数超过3门)的组才被选出来。4.4复杂查询4.4.1连接查询4.4.2嵌套查询4.4.3集合查询4.4.1连接查询

不同表之间的连接查询

自身连接查询

外连接查询不同表之间的连接查询一般是在WHERE字句中WHERE子句中将两(多)个表的属性列名，作为连接条件实现，其格式通常为：

[<表名1>.]<列名1

><比较运算符>[<表名2>.]<列名2>或者[<表名1>.]<列名1>BETWEEN

[<表名2>.]<列名2>AND[<表名2>.]<列名3>说明：⑴当连接运算符为“=”时，称为等值连接，其它为非等值连接。连接条件中列名对应属性的类型必须是可比的，但列名不必是相同的。⑵DBMS执行连接操作的过程是：首先在表1中找到第1个元组，然后对表2从头开始扫描逐一查找满足连接条件的元组，找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。表2全部查找完后，再找表1中第2个元组，然后再对表2从头开始扫描，逐一查找满足连接条件的元组，找到后就将表1中的第2个元组与该元组拼接起来，形成结果表中的一个元组。重复上述操作，直到表1中的全部元组都处理完毕为止。不同表之间的连接查询例

查询每个学生的学号(SNo)、姓名(SName)、选修的课程名(CName)及成绩(Grade)。本查询涉及三个表的连接操作，完成该查询的SQL语句如下：SELECTStudent.SNo，SName，CName，GradeFROMStudent，SC，CourseWHEREStudent.SNo=SC.SNoANDSC.CNo=Course.CNo自身连接查询自身连接：将同一个表与自己进行连接的查询，其基本方法是在SELECT语句的FROM子句中将同一个表取两个不同的别名。比如：

SELECTA.cno,A.cname,B.pre_CnoFROMCoursesA,CoursesBWHEREA.pre_Cno=B.cno;外连接

选定连接操作中涉及的某个表A为基准，即使另一个表B中没有与之匹配的记录，其结果表中也要求包括表A的所有元组。若表B中没有与之匹配的记录，结果表中涉及表B的属性列全部取空值。

外连接例

把书上例3.37中的等值连接改为左连接。SELECTStudent.SNo，SName，SSex，SAge，SDept，CNo，GradeFROMStudent，SCWHEREStudent.SNo=SC.SNo(*)4.4.2嵌套查询嵌套查询：将一个查询块(SELECT-FROM-WHERE)嵌套在另一个查询块的WHERE子句或HAVING短语条件中的查询。2、常用的嵌套查询⑴带谓词IN的嵌套查询⑵带有比较运算符的嵌套查询⑶带谓词ANY或ALL的嵌套查询⑷带谓词EXISTS的嵌套查询

4.4.2嵌套查询1.带谓词IN的嵌套查询例

查询选修了编号为“C02”的课程的学生姓名(SName)和所在系(SDept)。SELECTSName，SDeptFROMStudentWHERESNoIN(SELECTSNoFROMSCWHERECNo='C02')在本例中，SELECTSNoFROMSCWHERECNo='C02称为子查询，SELECTSNameFROMStudentWHERESNoIN(…)称为外层查询或父查询。SQL语言允许多层嵌套查询。4.4.2嵌套查询

嵌套查询一般的求解方法是由里向外处理。即先执行子查询，后执行父查询。子查询的结果用于建立父查询的查找条件。嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。以层层嵌套的方式来构造程序正是SQL(StructurredQueryLanguage)中“结构化”的含义所在。4.4.2嵌套查询2.带有比较运算符的嵌套查询带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用>，<，=，>=，<=，!=或<>等比较运算符。例

将例3.42改为带有比较运算符的嵌套查询。由于一个学生只可能在一个系学习，因此子查询的结果是一个值，因此可以用=代替IN。SELECTSNo,SName，SDeptFROMStudentWHERESDept=(SELECTSDeptFROMStudentWHERESName=’王建平’)4.4.2嵌套查询3.带谓词ANY或ALL的嵌套查询例

查询非自动化系的不超过自动化系所有学生的年龄的学生姓名和年龄。SELECTSName，SageFROMStudentWHERESDept<>'自动化'ANDSAge<=ALL(SELECTSAgeFROMStudentWHERESDept='自动化')4.4.2嵌套查询4.带谓词EXISTS的嵌套查询带谓词EXISTS的子查询不返回任何数据，只产生逻辑值真(True)或逻辑值假(False)。例

查询所有选修了编号为“C01”课程的学生姓名(SName)和所在系(SDept)。本查询的SQL语句是：SELECTSName，SDeptFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESNo=Student.SNoANDCNo='C01')4.4.3集合查询1、并操作查询。标准SQL提供了并操作运算命令UNION，将多个SELECT语句的结果进行传统的集合并操作。但这个操作要求参加UNION操作的各个结果表的列数必须相同且对应属性的数据类型也相同。2、标准SQL中没有直接提供集合的交和差的操作，但可用其它条件查询来实现

4.4.3集合查询例

查询计算机科学系的学生或年龄不大于20岁的学生信息。SELECT*FROMStudentWHERESDept='计算机'UNIONSELECT*FROMStudentWHERESAge<=20本查询实际上是求计算机科学系的所有学生或年龄不大于20岁的学生的并集。使用UNION将多个查询结果合并起来时，系统会自动去掉重复的元组。注意：参加UNION操作的各结果表的列数必须相同且对应属性的数据类型也必须相同。4.4.3集合查询例

查询数学系的学生与年龄不大于20岁的学生的差集。本查询的等价说法是，查询数学系中年龄大于20岁的学生。SELECT*FROMStudentWHERESDept='计算机'ANDSAge>20提示：可以使用EXCEPT实现集合的差操作运算，用法同UNION。4.5实例与分析实例1

仓库管理实例2

SQL的集合处理方式与宿主语言实例3

在宿主语言中如何使用游标机制实例4书店管理实例1仓库管理仓库管理关系模型中有如下5个关系模式：零件：PART(P#,Pname,Color,Weight)项目：PROJECT(J#,Jname,Pdate)供应商：SUPPLIER(S#,SName,Saddr)供应：P_P(J#,P#,Total)采购：P_S(P#,S#,Quantity)实例1仓库管理----问题（1）试用SQL的DDL语句定义上述5个基本表，并说明主码和外码。（2）试将PROJECT、P_P、PART3个基本表的自然联接定义和一个视图VIEW1，PART，P_S，SUPPLIER这3个基本表的自然联接定义为一个视图VIEW2。（3）检索上海的供应商所供应的零件的编号和名字。（4）检索项目J4所用零件的供应商的编号和名字。实例1仓库管理----分析分析：本实例主要运用SQL语言定义基本表、视图和表示查询需求等命令及其有关概念的理解和应用能力。实例1仓库管理----解答（1）(1)CREATETABLEPART（P#CHAR(6)PRIMARYKEY，

PnameCHAR(10)NOTNULL,ColorCHAR(6)，

WeightFLOAT(5))CREATETABLEPROJECT（J#CHAR(6)PRIMARYKEY，

JnameCHAR(12)NOTNULL,PdateDATE)CREATETABLESUPPLIER（S#CHAR(8)PRIMARYKEY，

SnaneCHAR(12)NOTNULL,SaddrVARCHAR(30))实例1仓库管理----解答（1）CREATETABLEP_P（

J#CHAR(6)REFERENCESPROJECT(J#)，

P#CHAR(6)REFERENCESPART(P#)，

TotalINTEGER，

PRIMARYKEY(J#，P#))CREATETABLEP_S（

P#CHAR(6)REFERENCESPART(P#)，

S#CHAR(8)REFERENCESSUPPLIER(S#)，

QuantityINTEGER，

PRIMARYKEY(P#，S#))实例1仓库管理----解答（2）

(2)CREATEVIEWVIEW1ASSELECTPROJECT.*,P_P.P#,P_P.Total,PART.Pname,

PART.Color,PART.WeightFROMPROJECT,P_P,PARTWHEREPROJECT.J#=P_P.J#ANDP_P.P#=PART.P#CREATEVIEWVIEW2ASSELERCTPART.*,P_S.Quantity,SUPPLIER.SName,SUPPLIER.SaddrFROMPART,P_S,SUPPLIERWHEREPART.P#=P_S.P#ANDP_S.S#=SUPPLIER.S#实例1仓库管理----解答（3-4）（3）SELECTP#,PnameFROMVIEW2WHERESaddr=’上海’（4）SELECTS#,SNameFROMVIEW2WHEREP#IN(SELECTP#FROMVIEW1WHEREJ#=’J4’)实例2描述SQL的集合处理方式与宿主语言的单记录处理方式之间如何协调?实例2分析本题主要是对SQL的集合处理方式与宿主语言的单记录处理方式的理解，以及游标的概念和使用方法。由于SQL语句处理的是记录集合，而宿主语言语句一次只能处理一条记录，因此通过游标机制来协调，并将集合操作转换成单记录