SQL-Server-2005数据库原理及应用教程第4章-关系数据库标准语言--SQL

第4章关系数据库标准语言

——SQL

4.1SQL概述

4.1.1SQL语言的特点 SQL语言之所以能够得到广泛的应用，并成为国际上的数据库主流语言，是因为它是一个综合的、功能强大的、易学易用的语言。主要特点包括以下几个方面。1．综合统一2．高度非过程化3．面向集合的操作方式4．两种使用方式，统一的语法结构5．语言简洁、易学易用4.1.2SQL数据库的三级模式结构SQL语言支持关系数据库的三级模式结构，其中视图对应外模式，基本表对应模式，存储文件对应内模式。①用户可以对视图(View)和基本表(BaseTable)进行查询等操作。②视图是从一个或几个基本表导出的表，它本身不独立存储在数据库中，即数据库中只存储视图的定义，不存储对应的数据。因此视图是一张虚拟表。③基本表是本身独立存在的表，每个基本表都有与之对应的存储文件。一个基本表可以跨越若干个存储文件，一个存储文件也可以存放若干个基本表。④SQL用户可以是应用程序，也可以是最终用户。SQL用户也可以作为独立的用户接口，供交互环境下的终端用户使用。4.2SQL的数据定义

SQL语言的数据定义(DDL)功能包括：定义基本表、定义视图和定义索引。4.2.1基本表的定义在SQL中基本表的定义功能包括基本表的创建、修改和删除三种语句。1．基本表的创建SQL语言使用CREATETABLE语句创建基本表，格式为：CREATETABLE<表名>(<列名><数据类型>[列级完整性约束条件][,<列名><数据类型>[列级完整性约束条件]]…[,<表级完整性约束条件>])；其中，<表名>表示所要创建的基本表的名称，<列名>为表中属性的名称，可用英文也可用汉字。每个属性都有数据类型，不同的关系数据库系统所支持的数据类型存在部分差异，但一般都支持表4-3所列的一些主要数据类型。建表的时候还可以同时定义与该表有关的完整性约束条件，包括表级完整性约束条件与列级完整性约束条件。列级完整性约束条件是针对属性值设置的限制条件，SQL的列级完整性约束条件有以下几种。1)NULL或NOTNULL约束2)UNIQUE约束3)DEFAULT约束4)CHECK约束表级完整性约束条件是指涉及到关系中两列或两列以上的限制条件。SQL的表级完整性约束条件有以下几种。1)UNIQUE约束2)PRIMARYKEY约束3)FOREIGNKEY约束2．基本表的修改在数据库的实际应用中，随着应用环境和需求的变化，经常需要修改基本表的结构，包括修改属性列的宽度和数据类型，增加新的属性，增加新的约束条件，删除原有的约束条件等。SQL语言使用ALTERTABLE语句修改基本表，一般格式为：ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束条件]][DROP<完整性约束名>][MODIFY<列名><数据类型>];其中，<表名>是已经存在且要修改的表。ADD子句用于增加新列，但新属性不能定义为NOTNULL，因为表在新增加一列后，原来的原组在新增的列上的值都为空值。DROP子句用于删除指定的完整性约束条件。MODIFY子句用于修改原有的列名和数据类型，修改表中原有的列的定义可能会破坏原有的数据。3．基本表的删除当不再需要某个基本表时，可以使用DROPTABLE语句删除它。删除基本表语句的一般格式为：DROPTABLE<表名>;该语句将一个基表的定义，连同表中的所有数据记录、索引以及由此表导出的所有视图全部删除，并释放相应的存储空间。4.3SQL的数据查询SQL数据查询是SQL语言中最重要、最丰富，也是最灵活的内容。SQL语言中提供了SELECT语句进行数据库的查询，它具有数据查询、统计、分组和排序的功能。一般格式为：SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[,<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；①SQL查询中的子句顺序：SELECT，FROM，WHERE，GROUPBY，HAVING和ORDERBY。其中SELECT和FROM是必须的，而HAVING子句只能和GROUPBY子句搭配起来使用。②SELECT子句对应的是关系代数中的投影运算，用来列出查询结果中的属性。其输出可以是列名、表达式或集函数(AVG，COUNT，MAX，MIN和SUM)。DISTINCT选项可以保证查询结果集中不存在重复元组。③FROM子句对应的是关系代数中的笛卡儿积，它列出的是表达式求值过程中必须扫描的关系。在FROM子句出现多个基本表或视图时，系统首先执行笛卡儿积操作。④WHERE子句对应的是关系代数中的选择谓词。WHERE子句的条件表达式中常用的查询条件如下4.3.1简单查询简单查询是指在查询过程中只涉及一个基本表或视图的查询，它是一种最简单的查询。1．最简单的查询最简单的查询是指查询一张表，并且只有SELECT子句和FROM子句，没有WHERE子句，表示被查询的对象是关系中的所有元组。例4-12

SELECT(Pno,Pname,Pcity,Ptelephone)FROMProvider;在这里，当查询列的显示顺序与其在基本表中顺序相同时，也可以简单地将<目标列表达式>指定为*。因此上面的语句等价于：SELECT*FROMProvider;2．DISTINCT短语按照关系的一般理论，重复的元组是不会出现在关系中的。但是，重复元组的删除是相当费时的，所以SQL允许在关系和查询中出现重复。在SELECT语句中缺省或ALL是保留重复元组的，因此一般情况下不写ALL。如果想要删除重复，则必须使用DISTINCT短语。重复元组一般是在投影的时候产生的。例4-14查询供应商所在城市。SELECTPcityFROMProvider;3．表达式的查询SELECT子句可以包含算术表达式、字符串常量和函数等。例4-15查询所有工程的工程名和首付款(预算的50%)。SELECTProjectname,首付款,Budget*0.5首付FROMProject;4．WHERE子句WHERE子句的作用是选择满足条件的元组。1）比较运算SQL提供的比较运算符主要有：=、<>、!=、<、<=、>、>=，以及BETWEEN…AND…。SQL允许使用比较运算符、比较算术表达式和字符串等特殊类型数据(如日期)等。例4-16查询天津市供应商的名称和联系电话。SELECTPname,PtelephoneFORMProviderWHEREPcity='天津';2）逻辑运算SQL在WHERE子句中可以使用逻辑运算符AND，OR和NOT，逻辑运算的对象是包含比较运算符的表达式。例4-19查询供应数量在5000-10000之间的供应商代码。SELECTPnoFROMSupplyWHEREAccount>=5000ANDAccount<=10000;3）谓词ININ表示查找的条件包含在集合中。NOTIN表示查找的条件不包含在集合中。例4-21查询天津和北京的供应商信息。SELECT*FROMProviderWHEREPcityIN('天津','北京');4）字符串操作字符串也可以进行比较运算。若两个字符串具有相同的字符序列，则这两个字符串是相等的。当对两个字符串用“小于或大于”比较时，实际上是对这两个字符串按字母顺序依次进行字母大小比较，例如'FHGE'>'FHFF'SQL还提供了使用操作符LIKE的模式匹配的字符串比较功能，胜于查找指定的属性列值与<匹配串>相匹配的原组。：[NOT]LIKE'<匹配串>'[ESCAPE'<换码字符>']例4-22查询所有绿色的零件的信息。SELECT*FROMAccessoryWHEREAcolorLIKE'绿';5）ORDERBY子句在查询中，经常要求将查询结果按照某种规则进行排序。SQL通过在查询基本结构中加入ORDERBY子句实现对查询结果按一个或多个列排序，格式为：ORDERBY列名1[ASC|DESC],列名2[ASC|DESC],…列名n[ASC|DESC]；ASC代表的是升序，DESC代表的是降序，默认是升序，ASC可以省略。例4-28查询使用了“零件编号”为0043的“项目编号”及“供应数量”，并将查询结果按“项目编号”升序排列，同一个项目按“供应数量”降序排列。SELECTProjectno,AccountFROMSupplyWHEREAno='0043'ORDERBYProjectno,AccountDESC;6）使用集函数为了方便用户，增强检索功能，SQL提供了许多集函数例4-29查询供销商的个数。SELECTCOUNT(*)FROMProvider;例4-30查询为项目供应了零件的供应商的个数。SELECTCOUNT(DISTINCTPno)FROMSupply;供应商为一个项目供应了一种零件，在Supply表中就有一条记录。一个供应商要供应多个项目多种零件，为避免重复计算供应商个数，必须在COUNT函数中使用DISTINCT短语。4.3.2连接查询

连接查询是指涉及两个或两个以上的基本表或视图的连接操作而实现的查询。连接查询包括等值连接、自然连接、求笛卡尔积、一般连接、外连接、内连接、左连接、右连接和自然连接等多种。1．等值连接和非等值连接查询连接查询中，用来连接两个表的条件称为连接条件或连接谓词。连接条件的一般格式为：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>其中，比较运算符主要有：=、>、<、>=、<=和!=。连接谓词中的列名称为连接字段。连接条件中，连接字段类型必须是可比的，但连接字段不一定是同名的。当连接运算符为“=”时，该连接操作称为等值连接；否则，使用其他运算符的连接运算称为非等值连接。当等值连接中的连接字段相同，并且在SELECT语句中去除了重复字段时，该连接操作为自然连接。例4-35查询供应商的情况及其所供应的项目和零件。SELECTProvider.*,Supply.*FROMSupply,ProviderWHERESupply.Pno=Provider.Pno;说明：①该题的目标列中含供应商表的全部属性和供应零件表的全部属性；②由于目标列中有供应商表中的供应商编号和供应零件表中的供应商编号两个相同属性名的属性，故它的连接操作是等值连接，如果在SELECT语句中将重复属性去掉，该操作即为自然连接操作；③连接操作的连接条件必须在WHERE子句中写出，如果使用了两个表查询，但WHERE子句中无连接条件，则结果为广义笛卡尔积操作结果。2．自身连接查询连接操作不只是在两个表之间进行，一个表也可以进行自身连接操作。表自身的连接操作称为自身连接。例4-38查询所有与“0065”零件颜色相同的零件名称。SELECTB.AnameFROMAccessoryA,AccessoryBWHEREA.Ano='0065'ANDAcolor=B.Acolor;说明：①自身连接实际上是将一个表作为两个表来处理，为了对它们进行区分，在查询语句中需要使用表的别名，在本例中的A和B分别是Accessory表的别名，A和B分别作为独立表使用；②该题的连接条件是A表中的颜色与B表中的颜色等值连接，由于A和B都是零件的别名，所以该例是自然连接操作；③数据库系统在执行本例的自然连接操作时，首先按别名形成两个独立的表A和B，然后根据要求连接成结果表；3．外部连接查询在前面的连接示例中，结果集中只保留了符合连接条件的元组，而去除了两个表中没有对应的或匹配的元组情况，这种连接称为内连接。如果要求查询结果集中保留非匹配的元组，这就是外部连接操作。SQL的外部连接分为左连接和右连接两种：左连接是在结果集中保留连接表达式左集中的非匹配记录；右连接是在结果集中保留连接表达式右集中的非匹配记录。左连接符号为“*=”，右连接符号为“=*”。外部连接中不匹配的分量在结果集中使用NULL表示。可以将例4-35改写为：SELECTProvider.*,Supply.*FROMSupply,ProviderWHERESupply.Pno=*Provider.Pno;或SELECTProvider.*,Supply.*FROMSupply,ProviderWHEREProvider.Pno*=Supply.Pno;4.3.3嵌套查询在SQL语言中，一个SELECT…FROM…WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。在嵌套语句中，上层的查询块称为外层查询或父查询，下层查询块称为内层查询或子查询。SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。需要特别注意的是，子查询的SELECT语句中不能使用ORDERBY子句，ORDERBY子句只能对最终查询结果排序。嵌套查询一般的处理方法是由里向外处理，即每个子查询在上一级查询处理之前处理，子查询的结果用于建立起父查询的查找条件。1．使用IN操作符的嵌套查询在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。例4-39查询使用了零件“螺钉”的项目编号和名称。SELECTProjectno,ProjectnameFROMProjectWHEREProjectnoIN(SELECTProjectnoFROMSupply WHEREAnoIN(SELECTAno FROMAccessory WHEREAname='螺钉'));该题的执行步骤是，首先在零件表中查询出螺钉的零件编号(可能为多个)，然后根据这个(些)零件编号在项目表中找出项目编号，最后根据项目编号在项目表中找出其名称；2．使用比较运算符的嵌套查询IN操作符用于一个值与多个值的比较，而比较符用于一个值与另一个值之间的比较。当比较运算符后面的值需要通过查询才能得到的，就需要使用比较符嵌套查询。例4-40查询在“001”工程中比“2948”零件供应量大的零件编号。SELECTAnoFROMSupplyWHEREProjectno='001'ANDAccount>(SELECTAccountFROMSupply WHEREAno='2948'ANDProjectno='001');3．使用ANY或ALL操作符的嵌套查询ANY或ALL操作符时必须与比较运算符配合使用，格式为：<字段><比较符>[ANY|ALL]<子查询>ANY和ALL与比较运算符结合及语义如表4.16所示4.3.4组合查询SELECT语句的查询结果是元组的集合，因此，可以将多个SELECT语句的查询结果进行组合操作。组合操作主要包括并操作UNION、交操作INTERSECT和差MINUS操作。例4-47查询蓝色或绿色的零件编号。SELECTAnoFROMAccessoryWHEREAcolor='蓝'UNIONSELECTAnoFROMAccessoryWHEREAcolor='绿';①由于组合查询的整体是一个查询，故只能在最后一条语句的后面加结束符号“；”，而不能在每个分查询子句后加结束符号；②组合查询中的每个分查询结果集的结构应一致；4.4SQL的数据更新数据更新是指数据的插入、删除和修改操作。SQL的数据更新语句主要包括INSERT(插入)、UPDATE(修改)和DELETE(删除)三种。4.4.1插入语句

SQL的数据插入语句INSERT有两种形式：一种是一次插入一个元组，另一种是一次插入一个子查询的结果。1．插入单个元组插入单个元组的INSERT语句的格式为：INSERTINTO<表名>[(<属性列1>[，<属性列2>…])VALUES(<常量1>[，<常量2>]…);其功能是将新元组插入到指定表中，其中新插入的元组的<属性列1>的值为<常量1>，<属性列2>的值为<常量2>，…。INTO子句中没有出现的属性列，新记录在这些列上将为空值。注意在表定义时说明了NOTNULL的属性列不能取空值，否则会出错。如果INTO子句中没有指明任何列名，则新插入的记录必须在每个属性列上均有值。例4-50零件表中插入一条新记录(零件编号：3000；零件名称：涡轮；产地：南京；规格：W_h21；颜色：红)INSERTINTOAccessoryVALUES('3000','涡轮','南京','W_h21','红');2．插入子查询的结果集SQL

允许将子查询的结果嵌到数据插入语句中，以便将查询得到的结果集作为数据插入到数据表中。插入子查询结果集的INSERT语句格式为：INSERTINTO<表名>[(<属性列1>[，<属性列2>…])<子查询>;例4-52求每项工程所用零件的总数量。CREATETABLEProductsum(ProjectnoCHAR(10),SumINT);INSERTINTOProductsumSELECTProjectno,SUM(Account)FROMSupplyGROUPBYProjectno;说明：本例首先用CREATETABLE语句建立了Productsum基本表，然后使用INSERT语句将查询得到的结果集插入到Productsum基本表中。4.4.2删除语句数据删除语句的一般格式为：

DELETE FROM<表名> [WHERE<条件>];DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，表示删除表中全部的元组。注意DELETE语句与DROPTABLE语句的区别，DELETE语句删除的是表中的数据，而DROPTABLE语句删除的是表的结构。例4-53删除编号为“007”的工程记录。DELETEFROMProjectWHEREProjectno='007';例4-54删除所有的供应零件记录。DELETEFROMSupply;4.4.3修改语句数据修改语句的一般格式为：

UPDATE<表名>SET<列名>=<表达式>[，<列名2>=<表达式2>]…[WHERE<条件>];SQL数据修改语句的功能是将<表名>中那些符合WHERE子句条件的元组的某些列，用SET子句中给出的表达式的值替代。如果UPDATE语句中无WHERE子句，则表示要修改指定表中的全部元组。在UPDATE语句的WHERE子句中可以嵌入查询语句。例4-56将工程表中全部工程的预算增加10%。UPDATEProjectSETBudget=Budget*1.1;4.5视图视图是由基本表或其他视图导出的虚表。数据库中只存放视图的定义，视图并不存放数据，用户在视图中看到的数据存放在相关的基本表中。视图定义之后，和基本表一样可以被用户查询，但通过视图来更新基本表中的数据有一定的限制。视图的作用主要有以下三个方面。（1）可以减轻普通用户的工作。普通用户关心的数据往往是对基本表进行计算的，或统计的，或多表连接后的信息，数据库管理员可把这些数据做成视图，使这些视图直接满足普通用户的要求。（2）视图是数据库系统的一种安全机制。对不同的用户，提供不同的视图，不让用户直接面对基本表，使得某些重要或机密的数据不出现在无权看这些数据的用户的视图上。(3)视图可以提高数据的逻辑独立性。在数据库需要重构时，可以减少对用户和应用程序的影响。4.5.1视图的定义和删除1．定义视图SQL语言定义视图的一般格式为：CREATEVIEW<视图名>[(<列名1>[,<列名2>]…)]AS<子查询>[WITHCHECKOPTION];其中<子查询>可以是任意复杂的SELECT查询语句，但不同的是在视图中的<子查询>通常不允许含有ORDERBY子句和DISTINCT短语。<列名>是<子查询>的结果在该视图中的属性名称，它必须与<子查询>中的目标列相对应。它要么全部省略要么全部指定，但若<子查询>中的目标列是集函数、表达式和多表连接的同名列时，必须全部指定<列名>，另外若视图要用与<子查询>中的目标列名不同的新名时，也必须全部指定<列名>。WITHCHECKOPTION选项表示在对视图更新操作时要保证操作的数据满足视图定义的谓词条件。例4-59建立红色零件的视图。CREATEVIEW红色零件ASSELECTAno,Aname,Aplace,AspecFROMAccessoryWHEREAcolor='红';例4-60建立一个“002”工程所需零件的视图，其属性包括工程代码，工程名称，所需零件名称，用量，并要求进行修改时须保证该视图只有“002”工程所需的零件。CREATEVIEW工程零件(工程代码,工程名称,所需零件名称,用量)ASSELECTSupply.Projectno,Projectname,Aname,AccountFROMSupply,Project,AccessoryWHERESupply.Projectno=Project.ProjectnoANDSupply.Ano=Accessory.AnoWITHCHECKOPTION;2．删除视图SQL中视图删除的一般格式为：DROPVIEW<视图名>;当基本表删除后，由该基本表导出的所有视图（定义）没有被删除，但均已无法使用。删除这些视图（定义）需要显式地使用DROPVIEW语句。视图删除后，视图的定义将从数据字典中删