第08章 结构化语言SQL基础-08

第8章结构化语言SQL基础学习目标

了解SQL语言的产生与发展认识SQL语言的特点深入理解SQL语言所涉及的基本概念熟练使用SQL语言进行数据库定义、数据检索、数据插入、数据修改、数据删除等操作。1第8章结构化语言SQL基础教学内容

8.1SQL概述8.2数据定义8.3数据操纵8.4数据检索8.5事务处理本章小结返回总目录28.1SQL概述返回目录SQL(StructuredQueryLanguage）结构化查询语言1974年由Boyce和Chamberlin提出，首先在IBM公司的关系数据库系统SystemR上实现。SQL功能丰富、使用方便、灵活、语言简洁易学，最终发展成为关系数据库标准语言。数据库厂商推出的大部分DBMS产品都支持SQL，使得不同的数据库系统之间有了共同的操作基础。3（1）SQL特点SQL介于关系代数与关系演算之间，集数据查询、数据操纵、数据定义和数据控制功能于一体，国际标准。主要特点有如下几条：①综合统一：SQL语言使集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL功能于一体，关系模型的实体及实体间的联系均用关系表示，其查询、插入、删除、修改都只有一种操作符。②高度非过程化的语言：用户只需提出“干什么”，至于“怎么干”由DBMS解决。③面向集合的语言：每一个SQL的操作对象是一个或多个关系，操作的结果也是一个关系。④以一种语法结构提供两种操作方式：即可独立使用，又可嵌入到宿主语言中使用，具有自主型和宿主型两种特点。⑤语言简捷，易学易用：SQL设计很巧妙，核心功能只需9个动词。在语言上接近英语，因此很容易学习。返回目录4SQL语言的动词表8-1SQL语言的动词返回目录SQL功能操作符数据查询SELECT数据定义CREATE，DROP，ALTER数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE58.2数据定义－－8.2.1创建数据库数据库是一个存放数据的表和支持这些数据的存储、检索、安全性和完整性的逻辑成分所组成的集合。数据库对象包括组成数据库的逻辑成分，如：表、视图、索引等。ANSI标准SQL建立数据库结构（模式）的命令是：CREATESTUCOUHEMAAUTHORIZATION<创建者>；例如，创建者是李平的命令如下：CREATESTUCOUHEMAAUTHORIZATION<李平>；但是大部分的DBMS，如DB2，XDB，DBASEIV创建数据库命令语法格式如下：CREATEDATABASE<DATABASE_NAME>；返回目录68.2.2表及其创建表类似纸面上的一张表：它由行和列组成。字段的数目是固定的，每个字段都有一个名字。行的数目是变化的，它反映在任意时刻里存储的数据量。每个字段都有一个数据类型。数据类型约束可以赋予一个字段的可能数值的集合，并且约束为存储在字段里的数据赋以语义，这样它就可以用于计算。返回目录7

（1）表的定义语句格式CREATETABLE<表名><列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；<表名>：所要定义的基本表的名字；<列名>：组成该表的各个属性（列）；<列级完整性约束条件>：涉及相应属性列的完整性约束条件；<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件；返回目录8数据类型返回目录数据类型符号标识半字长二进制整数Smallint全字长二进制整数Int

或Integer双字长浮点型Float最大长度为n变长字符串VarChar(n)日期型DateIBMDB2SQL支持的部分数据类型9

[例1]建立一个“学生”表Student，它由学号Number、姓名Name、性别Sex、年龄Age、所在系Department五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。CREATETABLEStudent

（NumberCHAR（5）NOTNULLUNIQUE，

NameCHAR（20）UNIQUE，

SexCHAR（1），

AgeINT，

DepartmentCHAR（15））；建立表时，常用完整性约束主要有以下几种：主码约束：PRIMARYKEY；唯一性约束：UNIQUE；非空值约束：NOTNULL；其中PRIMARYKEY定义某属性为表主码，它的与UNIQUE的区别是：定义为主码的属性一定是互不相同的，即PRIMARYKEY约束包含UNIQUE约束。UNIQUE约束只是表示该属性的值互不相同，它不是主码。返回目录

（1）表的定义例子10

（2）删除基本表格式：DROPTABLE<表名>;

基本表被删除以后，基本表中的数据、表上建立的索引都删除了，表上的视图往往仍然保留，但却无法引用。删除基本表时系统会从数据字典中删去有关该基本表及其索引的描述。[例2]删除Student表

DROPTABLEStudent;返回目录11

（3）修改基本表格式：ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][MODIFY<列名><数据类型>]；其中：<表名>：要修改的基本表；ADD子句：增加新列和新的完整性约束条件；DROP子句：删除指定的完整性约束条件；MODIFY子句：用于修改列名和数据类型；返回目录12

（3）修改基本表－－－例子[例4]向Student表增加“入学时间”列，其数据类型为日期型。

ALTERTABLEStudentADDStuCouomeDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。

删除属性列可以分为直接删除或间接删除间接删除就是把表中要保留的列及其内容复制到一个新表中，然后删除原表，再将新表重命名为原表名直接删除属性列就比较简单。修改原有的列定义有可能会破坏已有数据，所以有些DBMS不允许直接删除属性列，这时，就必须采用间接删除。返回目录138.2.3约束数据类型是约束我们可以在表里存储什么类型的数据的一种方法。数据类型提供的约束实在是太粗糙。一个包含产品价格的字段可能应该只接受正数。但是没有哪种数据类型只接受正数。可能需要根据其他字段或者行的数据来约束字段数据。比如，在一个包含产品信息的表中，每个产品编号都应该只有一行。SQL允许你在字段和表上定义约束。约束给予你所需要对数据施加的一切控制。在一个字段里存储会违反约束的数据，那么就会给出一个错误信息。适用于数值来自缺省值的情况。返回目录14

（1）检查约束最常见的约束类型。允许在某个字段里的数值必须满足一个任意的表达式。检查约束用关键字CONSTRAINT，它后面跟着一个标识符，表示这个约束一个独立的名字。然后再跟着约束定义。比如，要强制一个正数的产品价格，在ORACLE中你可以用：CREATETABLEproducts(

product_noCHAR（8）,nameCHAR（8）,priceNUMBER（7）CONSTRAINTc1CHECK(price>0));约束定义在数据类型后面。一个检查约束由一个关键字CHECK后面跟着一个放在圆括弧里的表达式组成。检查约束表达式应该包含受约束的字段，否则这个约束就没什么意义了。返回目录15（2）非空约束与唯一约束非空约束简单地声明一个字段必须不能是空值，前面讲建表的时候已经提过非空约束是约束的一种类型非空约束总能写成一个字段约束。非空约束在功能上等效于创建一个检查约束CHECK(column_nameISNOTNULL)非空约束可不用明确的名字一个字段可以有多个约束。只要在一个约束后面继续写另外一个就可以了唯一约束UNIQUE保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的返回目录16（4）主码主码约束是唯一约束和非空约束的组合。所以，下面两个表定义接受同样的数据：CREATETABLEproducts(

product_noINTUNIQUENOTNULL,nameCHAR(8),priceNUMBER（7）);CREATETABLEproducts(

product_noPRIMARYKEY,nameCHAR(8),priceNUMBER（7）);返回目录17（4）主码主码也可以约束多于一个字段；其语法类似唯一约束：CREATETABLEexample(aINT,bINT,cINT,PRIMARYKEY(a,c));主码表示一个字段或者是若干个字段的组合可以用于表中的数据行的唯一标识。表最多可以有一个主码，但是它可以有多个唯一和非空约束。每个表都必须有一个主码。返回目录18（5）外码1约束字段数值必须匹配另外一个表中某些行出现的数值。假设我们有个产品表：CREATETABLEproducts(

product_noPRIMARYKEY,nameCHAR(8),priceNUMBER（7）);假设有一个存储这些产品的订单的表。我们想保证订单表只包含实际存在的产品。因此我们在订单表中定义一个外码约束引用产品表：CREATETABLEorders(

order_idINTPRIMARYKEY,

product_noINTREFERENCESproducts(product_no),quantityINT);不可能创建任何其product_no

没有在产品表中出现的订单。在这种情况下我们把订单表叫做参照表，而产品表是被参照表。返回目录19（5）外码2一个表可以包含多于一个外码约束。外码删除相关方式：①级联删除（cascades）：即将参照关系中的所有外码值与被参照关系中要删除元组值相对应的元组一起删除。②受限删除（restricted）：即当参照关系中没有任何元组的外码值与要删除的被参照关系的元组的主码值相同时，系统才执行删除操作，否则拒绝此删除操作。③置空值删除：即删除被参照关系的元组，并将参照关系中的元组所有与被参照关系中被删除元组主码值相同的外码值置为空值。受限删除和级联删除是两种最常见的选项。返回目录208.2.4索引及其创建建立索引是加快查询速度的有效手段，建立索引必须由DBA或表的属主（即建立表的人）根据需要建立，有些DBMS自动建立以下列上的索引。

PRIMARYKEYUNIQUE索引的维护由DBMS自动完成，DBMS自动选择是否使用索引以及使用哪些索引。返回目录21（1）建立索引语句格式：CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>（<列名>[<次序>][,<列名>[<次序>]]…）； 用<表名>指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔<次序>指定索引值的排列次序，升序用ASC表示，降序用DESC表示。缺省值是ASC。UNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引。返回目录22（1）建立索引－－－例子[例1]为学生-课程数据库中的Student，Course，STUCOU三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，STUCOU表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStuNumberONStudent（Number）；CREATEUNIQUEINDEXCouCounumONCourse（Counum）；CREATEUNIQUEINDEXSTUCOUnoONSTUCOU（NumberASC，CounumDESC）；返回目录23（1）建立索引3对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。[例2]CREATECLUSTERINDEXStuNameONStudent（Name）；在Student表的Name（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Name值的升序存放。在一个基本表上最多只能建立一个聚簇索引，聚簇索引的用途是对于某些类型的查询，可以提高查询效率。很少对基表进行增删操作，很少对其中的变长列进行修改操作，可以建立聚簇索引。返回目录24（2）删除索引DROPINDEX<索引名>；删除索引时，系统会从数据字典中删去有关该索引的描述。[例3]删除Student表的StuName索引。

DROPINDEXStuName

；返回目录258.2.5存储过程与触发器保证数据库中数据：完整性一致性提高应用的性能，常常采用存储过程和触发器技术。返回目录26存储过程是一组为了完成特定功能的SQL语句集，编译后存储在数据库中用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。是实现特定功能的程序体，不同的应用程序都可以通过名称和参数调用存储过程对存储过程的修改完善不会影响应用程序，存储过程可以提高系统的可移植性。存储过程是经过预编译和优化过的程序代码，存储过程能够实现较快的执行速度。返回目录27TSQL命令创建存储过程语法格式：CREATEPROCEDUREprocedure_name[;number] [{@parameterdata_type}

[VARYING][=default][OUTPUT] ][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]|[FORREPLICATION]ASsql_statement[...n]返回目录28参数说明：procedure_name：是要创建的存储过程的名字；Number：用一个整数用来区别一组同名的存储过程；@parameter：存储过程的参数；Data_type：参数的数据类型；VARYING：用于指定作为输出OUTPUT参数支持的结果集，仅应用于游标型参数；Default：用于指定参数的默认值。；OUTPUT：表明该参数是一个返回参数；RECOMPILE：存储过程每执行一次都又要重新编译；ENCRYPTION：表示SQLServer加密了syStuCouomments

表，该表的text字段是包含CREATEPROCEDURE语句的存储过程文本；FORREPLICATION：用于指定不能在订阅服务器上执行为复制创建的存储过程；AS：用于指定该存储过程要执行的操作；sql_statement：是存储过程中要包含的任意数目和类型的Transact-SQL语句；返回目录29存储过程－－－例子[例1]该例是创建一个存储过程，实现将数据库中的人员代码的自动生成，每增加一个新人员，就从自动生成库t_table.number中得到新人员的人员代码，将这个值存入局部变量str

中，作为新记录的personnel_id；然后执行存储过程，使t_table.number的值加1，以备下一个调用。创建存储过程：CREATEPROCEDUREget_numberAS Updatet_tableSetnumber=number+1COMMIT调用存储过程：selectt_table.numberinto:strfromt_table;DECLAREsp_getnumberPROCEDUREFORget_number;Executesp_getnumber;Closesp_getnumber;返回目录30

触发器一种特殊类型的存储过程与存储过程的区别：触发器主要是通过事件进行触发而被执行的而存储过程可以通过存储过程名字被直接调用执行。对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQL就会自动执行触发器所定义的SQL语句能够实现由主码和外码所不能保证的复杂的参照完整性和数据的一致性。能够实现比CHECK语句更为复杂的约束。返回目录31触发与约束的区别触发（tigger）也称为事件一条件一动作规则（event－condition－actionrules）或ECA规则，在三个方面不同于前面讨论的约束类型。①当数据库编程人员所指定的某些事件发生时才对触发程序进行测试。允许的事件种类通常为对特定关系的插入、删除或修改。②不是直接阻止事件的发生，而是由触发程序对条件进行测试。如果条件不满足，则什么也不做，否则，为响应该事件就会进行与该触发相关的处理。③如果触发条件得到满足，就由DBMS执行与该触发相关的动作。于是该动作可能阻止事件的发生或撤消事件（如删除插入的元组）。实际上，动作可能是数据库操作的任何序列。返回目录32触发器－－－例子[例2]我们将写出应用于表Farmer(name，address，cert，networth)的SQL3触发程序。触发程序是由对networth属性的修改而启动的。该规则的作用是对降低农民存收入的任何尝试加以阻止。

l）CREATETRIGGERNetgetworthTrigger2）AFTERUPDATEOFnetworthONFarmer3）REFERENCING4）OLDASOldTuple，

5）NEWASNewTuple6）WHEN（OldTget＞NewTget）

7）UPDATEFarmer8）SETnetworth=OldTuple．networth9）WHEREcert=NewTuple.cert10）FOREACHROW返回目录33触发器－－－例子1）行给出具有关键字CREATETRIGGER和触发程序名的说明。2）行给出了触发事件，即修改关系Farmer中的属性netget。3）到5）行是为该触发程序的条件和动作部分如何引用旧元组（修改前的元组）和新元组（修改后的元组）提供一种方法。根据4）行和5）行中的说明，将用OldTuple和NewTuple分别引用这两个元组。在条件和动作部分，这些名称可以像在普通SQL查询的FROM子句中说明的元组变量一样使用。6）行是触发程序的条件部分。它表明只有在新的农民存收入低于旧的净农民存收入时才执行动作。

7）行到9）行构成动作部分：它们是普通的SQL修改语句，而具有的功能是将该农民的存收入恢复到修改以前的值。9）行的WHERE子句保证只影响到修改的元组（具有特定cert的元组）。10）行表明了要求，即每当修改元组，该触发程序都会启动一次。如果没有这一行，那么，每个SQL语句都使触发程序执行一次而无论发生多少改变元组的触发事件。返回目录34触发器－－－相关解释AFTER：其规则的动作将在触发事件之后执行BEFORE：WHEN中的条件在触发事件之前检验。如果条件为真观u执行触发程序的动作。此外，无论条件是否为真，都将执行触发程序修改的事件。INSTEADOF：（如果符合WHEN中的条件）会执行动作，而永远不会执行触发事件。触发事件：UPDATEINSERTDELETE。。任意数量用分号分开的SQL语句组成触发器。返回目录358.3数据操纵数据操纵数据插入数据更新返回目录368.3.1数据插入语句格式：INSERTINTO<表名>[（<属性列1>[，<属性列2>…）]VALUES（<常量1>[，<常量2>]…）功能：将新元组插入指定表中。37数据插入－－－例子

[例1]将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERTINTOStudentVALUES（'95020'，'陈冬'，'男'，'IS'，18）；没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致，指定部分属性列：插入的元组在其余属性列上取空值。VALUES子句提供的值必须与INTO子句匹配，包括值的个数和值的类型。DBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则，即实体完整性和参照完整性。破坏完整性规则的则拒绝插入。返回目录388.3.2数据更新数据更新包括数据修改和数据删除。（1）数据修改语句格式：UPDATE<表名>SET<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；功能：修改指定表中满足WHERE子句条件的元组。SET子句：指定修改方式、要修改的列、修改后取值。WHERE子句：指定要修改的元组、缺省表示要修改表中的所有元组。返回目录39（1）数据修改－－－例子

[例1]将学生95001的年龄改为22岁。

UPDATEStudentSETAge=22WHERENumber='95001'；DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则、实体完整性、主码不允许修改、用户定义的完整性、NOTNULL约束、UNIQUE约束、值域约束等。返回目录40（2）数据删除（2）数据删除

DELETEFROM<表名>[WHERE<条件>]；功能：删除指定表中满足WHERE子句条件的元组。WHERE子句：指定要删除的元组，缺省表示要修改表中的所有元组。DBMS在执行删除语句时会检查所删除的元组是否破坏表上已定义的完整性规则：主要是参照完整性，一般有三种处理方式：级连删除，受限删除，置空值删除返回目录418.4数据检索语句格式：SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；SELECT子句：指定要显示的属性列；FROM子句：指定查询对象（基本表或视图）；WHERE子句：指定查询条件；GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数；HAVING短语：筛选出只有满足指定条件的组；ORDERBY子句：对查询结果表按指定列值的升序或降序排序。返回目录428.4.1单表查询

单表查询的查询仅涉及一个表是一种最简单的查询操作分成以下五种情况：选择表中的若干列；选择表中的若干元组；对查询结果排序；使用集函数查询；对查询结果分组。返回目录43（1）选择表中的若干列1查询表中指定列。例子如下：[例1]查询全体学生的学号与姓名。SELECTNumber，NameFROMStudent；

当然也可以查询表中的全部列，查询表中的全部列时可以将表中的全部列名列出，也可以简单地用*代替。例子如下：[例2]查询全体学生的详细记录。SELECT*FROMStudent；返回目录44（1）选择表中的若干列2查询经过计算的值，此时SELECT子句的<目标列表达式>为表达式、算术表达式、字符串常量、函数、列别名等。[例3]查全体学生的姓名及其出生年份。SELECTName，2000-AgeFROMStudent；

输出结果：

Name2000-Age----------------------

张小鹏1976

李敏1977

王名1978

张立1978返回目录45（1）选择表中的若干列3将一个常量串表达式放入查询结果。[例4]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTName，'YearofBirth:'，2000-Age，ISLOWER（Department）FROMStudent；输出结果：

Name'YearofBirth:'2000-AgeISLOWER（Department）

----------------------------------------------

张小鹏YearofBirth:1976cs

李敏YearofBirth:1977is

王名YearofBirth:1978ma

张立YearofBirth:1977is返回目录46（1）选择表中的若干列4用列别名改变查询结果的列标题。请看下面的例子：SELECTNameNAME，'YearofBirth:’BIRTH，2000-AgeBIRTHDAY，ISLOWER（Department）DEPARTMENTFROMStudent；输出结果：

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

张小鹏YearofBirth:1976cs

李敏YearofBirth:1977is

王名YearofBirth:1978ma

张立YearofBirth:1977is返回目录47（2）选择表中的若干元组1选择表中的若干元组可以消除取值重复的行。此时可以在SELECT子句中使用DISTINCT短语。假设STUCOU表中有下列数据：

NumberCounumGrade---------------------9500119295001285950013889500229095002380返回目录48（2）选择表中的若干元组2

[例5]查询选修了课程的学生学号。SELECTDISTINCTNumberFROMSTUCOU;

结果：

Number-------9500195002注意DISTINCT短语的作用范围是所有目标列。请看以下例子：例：查询选修课程的各种成绩错误的写法：SELECTDISTINCTCounum，DISTINCTGradeFROMSTUCOU;正确的写法：SELECTDISTINCTCounum，GradeFROMSTUCOU;

返回目录49（2）选择表中的若干元组3选择元组最常见的就是查询满足条件的元组，条件可以是比较大小的条件，此时在WHERE子句的<比较条件>中使用比较运算符：=、>、<、>=、<=、!=、<>、!>、!<或逻辑运算符NOT+比较运算符。[例6]查询所有年龄在20岁以下的学生姓名及其年龄。SELECTName，AgeFROMStudentWHEREAge<20；返回目录50（2）选择表中的若干元组4选择元组还可以使用谓词BETWEEN…AND…或NOTBETWEEN…AND…确定元组范围。[例7]查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTName，Department，AgeFROMStudentWHEREAgeBETWEEN20AND23；要想查询年龄不在20~23岁之间的学生姓名、系别和年龄，只需在上例的BETWEEN前加上NOT即可。返回目录51（2）选择表中的若干元组5选择元组还可以使用谓词IN<值表>,NOTIN<值表>来确定一个集合。<值表>是用逗号分隔的一组取值。[例8]查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTName，SexFROMStudentWHEREDepartmentIN（'IS'，'MA'，'CS'）;返回目录52（2）选择表中的若干元组6可以用[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]进行字符串匹配。<匹配串>：当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词，用!=或<>运算符取代NOTLIKE谓词。通配符有如下两种：%（百分号）：代表任意长度（长度可以为0）的字符串；_（下横线）：代表任意单个字符；ESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE'<换码字符>'短语对通配符进行转义。返回目录53（2）选择表中的若干元组7[例9]查询所有姓刘学生的姓名、学号和性别。

SELECTName，Number，SexFROMStudentWHERENameLIKE‘刘%’；使用换码字符将通配符转义为普通字符的例子：[例10]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；返回目录54（2）选择表中的若干元组8涉及空值的查询，使用谓词ISNULL或ISNOTNULL可以进行涉及空值的查询，“ISNULL”不能用“=NULL”代替。[例11]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECTNumber，CounumFROMSTUCOUWHEREGradeISNULL；返回目录55（2）选择表中的若干元组9选择元组时可以用逻辑运算符AND和OR来联结多个查询条件形成多重条件查询AND的优先级高于OR，也可以用括号改变优先级，可用来实现多种其他谓词。[例12]查询计算机系年龄在20岁以下的学生姓名。

SELECTNameFROMStudentWHEREDepartment='CS'ANDAge<20；返回目录56（3）对查询结果排序使用ORDERBY子句可以按一个或多个属性列排序。升序：ASC；降序：DESC；[例13]查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTNumber，GradeFROMSTUCOUWHERECounum='3'ORDERBYGradeDESC；[例14]查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYDepartment，AgeDESC；返回目录57（4）使用集函数集函数有如下主要的五类：COUNT（*）用于计数；COUNT（[DISTINCT|ALL]<列名>）：用于计数；SUM（[DISTINCT|ALL]<列名>）：用于计算总和； AVG（[DISTINCT|ALL]<列名>）：用于计算平均值；MAX（[DISTINCT|ALL]<列名>）：用于求最大值；MIN（[DISTINCT|ALL]<列名>）：用于求最小值；DISTINCT短语：在计算时要取消指定列中的重复值；ALL短语：不取消重复值；ALL为缺省值。返回目录58（4）使用集函数

[例15]查询学生总人数。

SELECTCOUNT（*）

FROMStudent；

[例16]查询选修了课程的学生人数。SELECTCOUNT（DISTINCTNumber）FROMSTUCOU；注意：用DISTINCT以避免重复计算学生人数。[例17]计算1号课程的学生平均成绩。SELECTAVG（Grade）FROMSTUCOUWHERECounum='1'；返回目录59（5）对查询结果分组1使用GROUPBY子句对元组进行分组，细化集函数的作用对象，如果未对查询结果分组，集函数将作用于整个查询结果，对查询结果分组后，集函数将分别作用于每个组。[例18]求各个课程号及相应的选课人数。

SELECTCounum，COUNT（Number）

FROMSTUCOUGROUPBYCounum；

结果：

CounumCOUNT（Number）

122 234 344 433 548GROUPBY子句的作用对象是查询的中间结果表，分组方法：按指定的一列或多列值分组，值相等的为一组。

返回目录60（5）对查询结果分组2使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数。使用HAVING短语筛选最终输出结果。[例19]查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数

SELECTNumber,COUNT（*）

FROMSTUCOUWHEREGrade>=90GROUPBYNumberHAVINGCOUNT（*）>=3;使用HAVING短语筛选最终输出结果时，只有满足HAVING短语指定条件的组才输出。注意HAVING短语与WHERE子句的区别：作用对象不同：WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。返回目录618.4.2复杂查询（1）连接查询：同时涉及多个表的查询称为连接查询，用来连接两个表的条件称为连接条件或连接谓词。一般格式：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

比较运算符：=、>、<、>=、<=、!=；[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>连接谓词中的列名称为连接字段，连接条件中的各连接字段类型必须是可比的，但不必是相同的。返回目录62连接操作的执行过程1①嵌套循环法（NESTED-LOOP）：首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕。返回目录63连接操作的执行过程2②排序合并法（SORT-MERGE）：常用于=连接，首先按连接属性对表1和表2排序，对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续，重复上述操作，直到表1或表2中的全部元组都处理完毕为止。64连接操作的执行过程3③索引连接（INDEX-JOIN）：对表2按连接字段建立索引，对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。65SQL中连接查询的主要类型广义笛卡尔积：不带连接谓词的连接，往往没有实际意义，所以很少使用。条件连接查询：带连接谓词的连接，格式如下：[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>其中比较运算符一般有>、<、>=、<=、!=、=等几种。[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。返回目录66连接查询－－－例子

[例1]查询每个学生及其选修课程的情况。SELECTStudent.*，STUCOU.*FROMStudent，STUCOUWHEREStudent.Number=STUCOU.Number

；返回目录67③自身连接一个表与自己进行连接，称为表的自身连接，这时候需要给表起别名以示区别，由于所有属性名都是同名属性，因此必须使用别名前缀。

[例2]查询每一门课的间接先修课（即先修课的先修课）

SELECTFIRST.Counum，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Counum；FIRST，SECOND都是Course表的别名。返回目录68④外连接（OuterJoin）普通连接操作只输出满足连接条件的元组，外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。

[例3]查询每个学生及其选修课程的情况包括没有选修课程的学生。用外连接操作实现。

SELECTStudent.Number

，Name，Sex，Age，Department，Counum

，GradeFROMStudent，STUCOUWHEREStudent.Number=STUCOU.Number（*）；结果如下：Student.Number NameSexAgeDepartmentCounumGrade95001张小鹏男20CS19295001张小鹏男20CS28595001张小鹏男20CS38895002李敏女19IS29095002李敏女19IS38095003王敏女18MA95004张立男19IS在表名后面加外连接操作符（*）或（+）指定非主体表，非主体表有一“万能”的虚行，该行全部由空值组成，虚行可以和主体表中所有不满足连接条件的元组进行连接。由于虚行各列全部是空值，因此与虚行连接的结果中返回目录69(2)嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

SELECTName 外层查询/父查询

FROMStudentWHERENumberIN

（SELECTNumber内层查询/子查询

FROMSTUCOUWHERECounum='2'）；嵌套查询的子查询是有限制的：子查询不能使用ORDERBY子句。层层嵌套方式反映了SQL语言的结构化，有些嵌套查询可以用连接运算替代。返回目录70嵌套查询分类嵌套查询分为：不相关子查询和相关子查询。不相关子查询即子查询的查询条件不依赖于父查询；相关子查询的子查询的查询条件依赖于父查询。不相关子查询的求解方法是是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。相关子查询的求解方法是首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。返回目录71①带有IN谓词的子查询[例4]查询与“李敏”在同一个系学习的学生。构造嵌套查询：将第一步查询嵌入到第二步查询的条件中。

SELECTNumber，Name，DepartmentFROMStudentWHEREDepartmentIN

（SELECTDepartmentFROMStudentWHEREName=‘李敏’）；结果为：NumberNameDepartment95001李敏IS95004张立IS返回目录72①带有IN谓词的子查询查询可以用用自身连接完成。

SELECTS1.Number，S1.Name，S1.DepartmentFROMStudentS1，StudentS2WHERES1.Department=S2.DepartmentANDS2.Name='李敏'；当然父查询和子查询中的表均可以定义别名，进行区分。

SELECTNumber，Name，DepartmentFROMStudentS1WHERES1.DepartmentIN

（SELECTDepartmentFROMStudentS2WHERES2.Name=‘李敏’）；返回目录73①带有IN谓词的子查询[例5]查询选修了课程名为“信息系统”的学生学号和姓名。

SELECTNumber，Name③最后在Student关系中

FROMStudent取出Number和NameWHERENumberIN

（SELECTNumber②然后在STUCOU关系中找出选

FROMSTUCOU修了3号课程的学生学号

WHERECounumIN

（SELECTCounum①首先在Course关系中找出“信

FROMCourse息系统”的课程号，结果为3号

WHERECname=‘信息系统’））;结果：Number Name---------95001张小鹏

95002李敏返回目录74②带有EXISTS谓词的子查询：1)EXISTS谓词：2)NOTEXISTS谓词3)不同形式的查询间的替换4)用EXISTS/NOTEXISTS实现全称量词751)EXISTS谓词：即存在量词

，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值；若内层查询结果为空，则返回假值。由EXISTS引出的子查询，其目标列表达式通常都用*[例6]用嵌套查询所有选修了1号课程的学生姓名。

SELECTNameFROMStudentWHEREEXISTS

（SELECT*FROMSTUCOU/*相关子查询*/WHERENumber=Student.NumberANDCounum='1'）；思路分析：本查询涉及Student和STUCOU关系。在Student中依次取每个元组的Number值，用此值去检查STUCOU关系。若STUCOU中存在这样的元组，其Number值等于此Student.Number值，并且其Counum='1'，则取此Student.Name送入结果关系。返回目录762)NOTEXISTS谓词

[例7]查询没有选修1号课程的学生姓名。

SELECTNameFROMStudentWHERENOTEXISTS

（SELECT*FROMSTUCOUWHERENumber=Student.NumberANDCounum='1'）；返回目录773)不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换。但是所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

[例8]查询与“李敏”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换：

SELECTNumber，Name，DepartmentFROMStudentS1WHEREEXISTS

SELECT*FROMStudentS2WHERES2.Department=S1.DepartmentANDS2.Name='李敏'；>返回目录784)用EXISTS/NOTEXISTS实现全称量词1SQL语言中没有全称量词

，可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：（

x）P≡

（

x（

P））

[例9]查询选修了全部课程的学生姓名。

SELECTNameFROMStudentWHERENOTEXISTS

（SELECT*FROMCourseWHERENOTEXISTS

（SELECT*FROMSTUCOUWHERENumber=Student.NumberANDCounum=Course.Counum）；返回目录794)用EXISTS/NOTEXISTS实现全称量词2SQL语言中也没有蕴函（Implication）逻辑运算，同样可以利用谓词演算将逻辑蕴函谓词等价转换为：

p

q≡

p∨q

。[例10]查询至少选修了学生95002选修的全部课程的学生号码。解题思路：用逻辑蕴函表达。查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。形式化表示：用P表示谓词“学生95002选修了课程y”；q表示谓词“学生x选修了课程y”； 则上述查询为：（

y）p

q等价变换： （

y）p

q≡

（

y（

（p

q））

≡

（

y（

（

p∨q）

≡

y（p∧

q）返回目录804)用EXISTS/NOTEXISTS实现全称量词3变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。用NOTEXISTS谓词表示：

SELECTDISTINCTNumberFROMSTUCOUSTUCOUXWHERENOTEXISTS

（SELECT*FROMSTUCOUSTUCOUYWHERESTUCOUY.Number='95002'ANDNOTEXISTS

（SELECT*FROMSTUCOUSTUCOUZWHERESTUCOUZ.Number=STUCOUX.NumberANDSTUCOUZ.Counum=STUCOUY.Counum））；返回目录81(3)集合查询标准SQL直接支持的集合操作只有并操作（UNION）；一般商用数据库支持的集合操作种类有：并操作（UNION）交操作（INTERSECT）差操作（MINUS）。返回目录82①并操作：将两个以上的查询结果合并起来。语法形式： <查询块>UNION<查询块>参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同。[例11]查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT*FROMStudentWHEREDepartment='CS'UNIONSELECT*FROMStudentWHEREAge<=19；返回目录83②交操作标准SQL中没有提供集合交操作，但可用其他方法间接实现。[例12]查询选修课程1的学生集合与选修课程2的学生集合的交集。本例实际上是查询既选修了课程1又选修了课程2的学生。

SELECTNumberFROMSTUCOUWHERECounum='1'ANDNumberIN

（SELECTNumberFROMSTUCOUWHERECounum='2'）；返回目录84（3）

差操作：标准SQL中也没有提供集合差操作，同样可用其他方法间接实现。[例13]查询学生姓名与教师姓名的差集。本例实际上是查询学校中未与教师同名的学生姓名。

SELECTDISTINCTNameFROMStudentWHERENameNOTIN

（SELECTTname FROMTeacher）;返回目录85①插入子查询结果的语句格式1INSERTINTO<表名>[（<属性列1>[，<属性列2>…）]子查询；功能是将子查询结果插入指定表中。[例14]对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表；

CREATETABLEDeptage

（DepartmentCHAR（15）/*系名*/

AvgageSMALLINT）；/*学生平均年龄*/第二步：插入数据；

INSERTINTODeptage（Department，Avgage）

SELECTDepartment，AVG（Age）

FROMStudentGROUPBYDepartment；返回目录86①插入子查询结果的语句格式2INTO子句（与插入单条元组类似）：指定要插入数据的表名及属性列，属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组指定部分属性列：插入的元组在其余属性列上取空值。子查询中的SELECT子句目标列必须与INTO子句匹配，包括值的个数和值的类型。87②带子查询的修改语句格式与修改语句基本相同，只是在WHERE子句中可以嵌入SELECT子句。[例15]将计算机科学系全体学生的成绩置零。

UPDATESTUCOUSETGrade=0WHERE'CS'=

（SELECTDepartmentFROMStudentWHEREStudent.Number=STUCOU.Number）；返回目录88③带子查询的删除语句：格式与删除语句基本相同，只是在WHERE子句中可以嵌入SELECT子句。[例16]删除计算机科学系所有学生的选课记录。

DELETEFROMSTUCOUWHERE'CS'=

（SELETEDepart