第04章 SQL Server与数据查询

SQLServer与数据查询,SQL特点:综合统一,SQL语言将数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能集于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动,SQL语言简捷，易学易用,SQL数据定义,定义语句格式,CREATETABLE（，）；：所要定义的基本表的名字：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件,例题,例建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15)；,SQLServer的数据类型,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种基本数据类型如下：,SQLServer的数据类型,2.SQLServer数据的分类用法(1)二进制数据类型:二进制数据包括：Binary、Varbinary和Image。Binaryn(2)字符数据类型:字符数据类型包括：Char，Varchar和Text。(3)Unicode数据类型:Unicode类型包括：Nchar、Nvarchar和Ntext。(4)日期和时间数据类型:日期和时间数据类型包括：Data、Time、Datetime和Smalldatetime等。,SQLServer的数据类型,2.SQLServer数据的分类用法(6)货币数据表示正的或者负的货币数量:货币数据的数据类型是：Money和Smallmney。(7)特殊数据类型:3种特殊数据类型：Timestamp、Bit和Uniqueidentifier。,完整性约束的SQL实现,SQLServer把各种完整性约束作为数据库模式定义的一部分，由DBMS维护。SQLServer支持三种完整性约束：,实体完整性和主码（1）属性后增加关键字CREATETABLEStudentInfo(StudentIDCHAR(8)PRIMARYKEY,StudentNameVARCHAR(10),StudentexBIT)（2）加入定义主码子句CREATETABLEStudentInfo(StudentIDchar(8),StudentNamevarchar(10),Studentexbit,PRIMARYKEY(StudentID)提示：如果主码只含有单个属性，上面的两种方法都可以使用。如果主码由多个属性组成，只能使用第二种方法。,完整性约束的SQL实现,2.参照完整性和外码在更新数据时，参照完整性保持表间已定义的关系。参照完整性基于外码与主码之间或者外码与唯一码之间的关系，确保码值在所有表中一致。强制参照完整性时，SQLServer禁止用户进行下列操作：当主表中没有关联的记录时，将记录添加到相关表中。更改主表中的值并导致相关表中的记录孤立。从主表中删除记录，但仍存在与该记录匹配的相关记录。,完整性约束的SQL实现,（1）外码约束的说明：说明外部码的方法有两种：在属性说明后直接加上REFERENCES，后跟对应表的主码说明。在CREATETABLE语句的属性清单后，加上外码的说明子句。,完整性约束的SQL实现,CREATETABLERelaationInfo(RealationIDINTIdentity(1,1),StudentIDCHAR(8)REFERENCESStudentInfo(StudentID),Identity(1,1)表示自增，起始值是1，自动增加量为1.作用，当RealationID没有赋值的时候，DMBS会自动进行赋值CREATETABLERelaationInfo(RealationIDINTIdentity(1,1),StudentIDCHAR(8).FOREIGNKEY(StudentID)REFERENCESStudentInfo(StudentID),.),完整性约束的SQL实现,（2）参照完整性约束的实现策略：当用户的操作违反了上述规则时，SQLServer提供了两种可选方案供数据库实现者使用：RESTRICT（限制策略）和CASCADE(级联策略)。a限制策略：SQLServer的默认策略，任何违反参照完整性的更新均被系统拒绝。b级联策略：当用户删除或者更新外码指向的键时，通过在REFERENCES子句后添加ONDELETE和ONUPDATE子句实现。,完整性约束的SQL实现,ONDELETENOACTIONONUPDATENOACTION如果有级联，就返回错误，不动作ONDELETECASEADEONUPDATECASCADE如果级联，就联动删除和更新,完整性约束的SQL实现,3.用户定义完整性约束SQLServer提供非空约束、属性的CHECK约束、元组的CHECK约束、约束（CONSTTAINT）和触发器（Trigger）等来实现用户的完整性要求。（1）属性的CHECK约束：使用CHECK子句可保证属性满足某些前提条件。（2）基于元组的约束：CHECK约束涉及到表中的多个属性。,完整性约束的SQL实现,CHECK(Insure+Fund18ANDSage18NOTBETWEEN18AND22;whereSdeptIN(自动化,数学,计算机)；,条件查询,还有用LIKE和NOTLIKE构成谓词条件的语法格式如下：NOTLIKEESCAPE其含义是查找指定的属性列值与向匹配的元组。可以是一个完整的字符串，也可以含有通配符%和-,其意义如下：%(百分号):代表任意长度(长度可以为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=C03ORDERBYGradeDESC对于空值，若按升序排，含空值的元组最先显示。若按降序排，空值的元组最后显示。,使用集函数,在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子句中将两(多)个表的属性列名，作为连接条件实现，其格式通常为：.或者.BETWEEN.AND.说明：当连接运算符为“=”时，称为等值连接，其它为非等值连接。连接条件中列名对应属性的类型必须是可比的，但列名不必是相同的。,不同表之间的连接查询,例查询每个学生的学号(SNo)、姓名(SName)、选修的课程名(CName)及成绩(Grade)。本查询涉及三个表的连接操作，完成该查询的SQL语句如下：SELECTStudent.SNo，SName，CName，GradeFROMStudent，SC，CourseWHEREStudent.SNo=SC.SNoANDSC.CNo=Course.CNo,DBMS执行连接操作的过程是：首先在表1中找到第1个元组，然后对表2从头开始扫描逐一查找满足连接条件的元组，找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。表2全部查找完后，再找表1中第2个元组，然后再对表2从头开始扫描，重复上述操作，直到表1中的全部元组都处理完毕为止。,自身连接查询,自身连接：将同一个表与自己进行连接的查询，其基本方法是在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自动化ANDSAgeANY大于子查询结果中的某个值ALL大于子查询结果中的所有值=ANY大于等于子查询结果中的某个值=ALL大于等于子查询结果中的所有值）ANY不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值,带有ANY或ALL谓词的子查询,例查询其他系中比信息系其中某一个学生年龄小的学生姓名和年龄SELECTSname，SageFROMStudentWHERESageIS;,带有ANY或ALL谓词的子查询,ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系,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*FROMStudentWHERESAge20提示：可以使用EXCEPT实现集合的差操作运算，用法同UNION。,4.5实例与分析,实例1仓库管理实例2SQL的集合处理方式与宿主语言实例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语句处理的是记录集合，而宿主语言语句一次只能处理一条记录，因此通过游标机制来协调，并将集合操作转换成单记录处理方式。,实例2分析,游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果(元组的集合)，每个游标区都有一个名字(相当于一个文件)。用户可以通过访问游标逐一获取元组(数据记录)，并将其赋给主变量，交由主语言作进一步处理。与游标有关的SQL语句有4个：游标定义，游标打开，游标推进，游标关闭等。,实例3描述,嵌入式SQL的DML语句何时不必涉及到游标?何时必须涉及到游标?,实例3分析,本实例是对在宿主语言中如何使用游标机制理解。（1）不涉及游标的DML语句有下面几种情况：INSERT、DELETE、UPDATE语句，只要加上前缀标识和结束标识，就能嵌入在宿主语言程序中使用。对于SELECT语句，如果已知查询结果肯定是单个元组，也可直接嵌入在主程序中使用。,实例3分析,(2)涉及游标的DML语句有下面几种情况：当SELECT语句查询结果是多个元组时，必须用游标机制把多个元组一次一个地传送给主程序处理。对游标指向元组进行修改或删除操作时，涉及到游标。,实例4书店管理,假设有一个小书店，书店的管理者要对书店的经营状况了如指掌，需要建立一个数据库，其中包括两个表：存书（书号、书名、出版社、出版日期、作者、书价、进价、数量）销售（日期、书号、数量，金额）,实例4要求,