第第3 3章关系数据标准语言章关系数据标准语言SQL SQL 本章目录n3.1 SQL 3.1 SQL 概述概述n3.2 3.2 数据定义数据定义n3.3 3.3 数据查询数据查询n3.4 3.4 数据更新数据更新n3.5 3.5 视图视图n3.6 3.6 查询优化查询优化 n熟练掌握熟练掌握SQLSQL的数据定义功能的数据定义功能；；n熟练掌握熟练掌握SQLSQL的单表、多表的查询功能的单表、多表的查询功能；；n熟练掌握熟练掌握SQLSQL的数据更新功能；的数据更新功能；n理解索引的作用理解索引的作用及索引的管理；及索引的管理；n掌握视图的概念掌握视图的概念及视图的管理；及视图的管理；n理解查询优化理解查询优化的意义、的意义、方法及步骤方法及步骤 学习目标学习目标 3.1 SQL概述nSQLSQL（（Structured Query LanguageStructured Query Language）） 结构化查询语言，是关系数据库的标准语言结构化查询语言，是关系数据库的标准语言nSQLSQL是一个通用的、功能极强的关系数据库是一个通用的、功能极强的关系数据库 语语言言 SQLSQL标准的进展过程标准的进展过程 标准标准 大致页数大致页数 发布日期发布日期nSQL/86 1986.10nSQL/89(FIPS 127-1) 120页页 1989年年nSQL/92 622页页 1992年年nSQL99 1700页页 1999年年nSQL2003 3600页页 2003年年 3.1 SQL 概述nSQL语言支持数据库三级模式结构 3.2 数据定义n3.2.1 3.2.1 基本数据类型基本数据类型n3.2.2 3.2.2 基本表的创建、删除与修改基本表的创建、删除与修改 n3.2.3 3.2.3 索引的创建与删除索引的创建与删除 数据类型•系统定义数据类型系统定义数据类型charvarcharncharnvarchartextntextmoneysmallmoneybinaryVarbinaryimageUniqueidentifier (GUID)timestampsql_variantintsmallinttinyintBigintbitnumericdecimalfloatrealSQL Server SQL Server 2000 2000 数据类型数据类型Tabledatetimesmalldatetime 1. 1. 整数数据类型整数数据类型bigint,int,smallint,tinyintbigint,int,smallint,tinyint，，bitbit。
n存存储储精精确确的的整整数数值值由由数数字字0 0～～9 9、、正正负负号号组组成nBigintBigint（（长整型长整型）） ：：8 8个字节nintint ( (整型整型) ) ：：4 4个字节nsmallintsmallint ( (短整型短整型) :2) :2个字节nTinyintTinyint( (微整型微整型) :1) :1个字节nBitBit（（位）：只能取位）：只能取0 0、、1 1 或或 NULLNULL 2.2.浮点数据类型（近似数值型）浮点数据类型（近似数值型）二种：二种：float,realfloat,real大小范围不同大小范围不同n存存储储实实数数值值由由数数字字0 0～～9 9、、正正负负号号、、小小数点组成数点组成n说明方法：说明方法：float [ (n) ]:float [ (n) ]:nreal real ：相当于：相当于float(1-24)float(1-24) 3.精确数值数据类型二种：二种：decimal,numericdecimal,numeric 等价n存存储储实实数数值值由由数数字字0 0～～9 9、、正正负负号号、、小小数数点点组成。
最大精度组成最大精度3838位n说明方法：说明方法： decimaldecimal（（p,s)p,s)或或 numericnumeric（（p,s)p,s) p p指指数数据据中中的的位位数数总总和和，，包包括括整整数数部部分分和和小小数数部分，部分，不包括小数点不包括小数点 S S指小数点右边能存储的数字位数的最大值指小数点右边能存储的数字位数的最大值 4. 字符数据类型 char, char, ncharnchar 、、varcharvarchar, , nvarcharnvarchar、、 texttext、、 ntextntextn常量用单引号括起来常量用单引号括起来nCharChar：：≤≤80008000个字符，固定长度个字符，固定长度nVarcharVarchar: :可变长度其大小为输入数据的字可变长度其大小为输入数据的字 节的实际长度不包括尾随空格）节的实际长度不包括尾随空格）nText:Text:固定长度固定长度1616个字节nn n开头的是开头的是UnicodeUnicode类型 5.货币型数据类型n二种：二种：money,smallmoneymoney,smallmoney。
n大小范围不同大小范围不同n美元、英镑、日元、欧元美元、英镑、日元、欧元n货币常量前加币符货币常量前加币符 6. 6. 二进制数据类型二进制数据类型三种：三种：binary,varbinarybinary,varbinary、、imageimagen存储位串需要存储十六进制数时使用存储位串需要存储十六进制数时使用n表示方法：表示方法： binary binary （（n)n) 存储定长的位串存储定长的位串 varbinaryvarbinary （（n)n) 存储可变长的位串存储可变长的位串 ImageImage实际是可变长度的二进制数据类型实际是可变长度的二进制数据类型 7. 时间日期型数据类型n二种：二种：datetime,smalldatetimedatetime,smalldatetimen说明方法：说明方法： datetimedatetime用用8 8个个字字节节存存储储日日期期时时间间日日期期范范围围1753/1/11753/1/1～～9999/12/319999/12/31精度到3/1003/100秒 smalldatetimesmalldatetime用用4 4个字节存储。
范围个字节存储范围1900/1/11900/1/1～～2079/12/312079/12/31精度到分精度到分n常常量量用用单单引引号号引引起起，，有有多多种种格格式式分分隔隔符符可可为为- -或或/ / 8. 其他几种数据类型ntimestamptimestamp时时间间标标识识类类型型：：它它提提供供数数据据库库范范围围内内的的唯唯一一值值，，反反应应数数据据库库中中数数据据修修改改的的相相对对顺顺序序一一个个表表只只能能有有一一个个timestamptimestamp类类型型，，其其内内容会自动更新容会自动更新nsql_variantsql_variant可变数据类型可变数据类型 可可以以在在单单独独的的字字段段、、变变量量中中存存储储不不同同类类型型的的数数据据 但但 text,ntext,image,timestamptext,ntext,image,timestamp, ,以以 及及sql_variantsql_variant类型的值除外最长类型的值除外最长80168016字节 其他几种数据类型uniqueidentifieruniqueidentifier全局性唯一标识数据类型：用全局性唯一标识数据类型：用于存储一个由于存储一个由1616个字节组成的二进制数字，其数个字节组成的二进制数字，其数值格式类似于值格式类似于1C1AE361-7F2C-11D6-97AD-1C1AE361-7F2C-11D6-97AD-00E03C68608E00E03C68608E，，该识别码称为全局唯一标识该识别码称为全局唯一标识符（符（GUIDGUID :Globally Unique :Globally Unique IdentfierIdentfier) )。
可以用可以用NEWIDNEWID函数产生函数产生GUIDGUID的值 ntabletable数据类型类似于一个临时表，用于数据类型类似于一个临时表，用于存储一个结果集这种数据类型只能用于存储一个结果集这种数据类型只能用于定义局部变量和用于用户定义函数的返回定义局部变量和用于用户定义函数的返回值，值，不能在表中定义字段不能在表中定义字段其他几种数据类型 3.2.2基本表的创建、删除与修改 SQLSQL的数据定义语句的数据定义语句 操作对象操作对象操作方式操作方式创建创建删除删除修改修改表表CREATE CREATE TABLETABLEDROP DROP TABLETABLEALTER ALTER TABLETABLE视图视图CTEATE CTEATE VIEWVIEWDROP VIEWDROP VIEW 索引索引CREATE CREATE INDEXINDEXDROP DROP INDEXINDEX 数据库的物理实现数据库的物理实现( (数据库文件数据库文件) )……数据库的逻辑组件数据库的逻辑组件( (数据库对象数据库对象) )… SQL ServerSQL Server数据库概述数据库概述nSQL ServerSQL Server中的数据库中的数据库 SalesSales数据库数据库SQL ServerSQL Server表表视图视图SalesDat1.SalesDat1.mdfmdfSalesDat2.SalesDat2.ndfndfSalesLog1.SalesLog1.ldfldf主数据文件主数据文件次数据文件次数据文件日志文件日志文件索引索引存储过程存储过程用户视图用户视图物理视图物理视图数据库的用户视图和物理视图数据库的用户视图和物理视图视图视图 SQL Server SQL Server 数数 据据 库库 概概 述述1. SQL Server1. SQL Server中的数据库对象中的数据库对象 SQL ServerSQL Server提供了很多逻辑组件，这些逻辑组件通常被称提供了很多逻辑组件，这些逻辑组件通常被称为为数据库对象数据库对象。
对象对象作用作用表表数据库中数据的实际存放处所数据库中数据的实际存放处所视图视图定制复杂或常用的查询，以便用户使用；限定用户只能查定制复杂或常用的查询，以便用户使用；限定用户只能查看表中的特定行或列；为用户提供统计数据而不展示细节看表中的特定行或列；为用户提供统计数据而不展示细节索引索引加快从表或视图中检索数据的效率加快从表或视图中检索数据的效率存储过程存储过程提高性能；封装数据库的部分或全部细节；帮助在不同的提高性能；封装数据库的部分或全部细节；帮助在不同的数据库应用程序之间实现一致的逻辑数据库应用程序之间实现一致的逻辑约束、规则、默约束、规则、默认值和触发器认值和触发器确保数据库的数据完整性；确保数据库的数据完整性；强制执行业务规则强制执行业务规则登录、用户、角登录、用户、角色和组色和组保障数据安全的基础保障数据安全的基础 SQL ServerSQL Server数据库概述数据库概述2. SQL Server2. SQL Server中的数据库文件中的数据库文件主数据文件主数据文件* *. .mdfmdf仅有一个仅有一个事务日志文件事务日志文件* *. .ldfldf一到多个一到多个 次数据文件次数据文件* *. .ndfndf零到多个零到多个次数据文件次数据文件* *. .ndfndf零到多个零到多个( (仅仅有有一一个个) )主主文文件件组组( (零零到到多多个个) )次次文文件件组组事事务务日日志志一一个个数数据据库库的的文文件件集集数据库的文件组成数据库的文件组成 SQL ServerSQL Server数据库概述数据库概述(1)(1)数据文件数据文件 数据文件是存放数据和数据库对象的文件。
有一个文数据文件是存放数据和数据库对象的文件有一个文件被定义为主数据文件件被定义为主数据文件(Primary Database File)(Primary Database File)，，扩扩展名为展名为MDFMDF，，用来存储数据库的启动信息和部分或全用来存储数据库的启动信息和部分或全部数据 其他数据文件被称为次数据文件其他数据文件被称为次数据文件(Secondary (Secondary Database File) Database File) 扩展名为扩展名为NDFNDF，，用来存储主数据文件用来存储主数据文件没存储的其他数据没存储的其他数据 SQL ServerSQL Server数据库概述数据库概述(2)(2)事务日志事务日志事务日志文件是用来记录数据库更新信息的文件事务日志文件是用来记录数据库更新信息的文件这些更新信息这些更新信息( (日志日志) )可用来恢复数据库可用来恢复数据库事务日志文件最小为事务日志文件最小为 512 KB512 KB，，扩展名为扩展名为LDFLDF每个数据库可以有一个或多个事务日志文件每个数据库可以有一个或多个事务日志文件 SQL Server数据库概述数据库概述(3)(3)文件组文件组 SQL ServerSQL Server允许对文件进行分组，以便于允许对文件进行分组，以便于管理和数据的分配／放置。
所有数据库都至少管理和数据的分配／放置所有数据库都至少包含一个主文件组，所有系统表都分配在主文包含一个主文件组，所有系统表都分配在主文件组中用户可以定义额外的文件组用户可以定义额外的文件组 数据库的创建数据库的创建使用使用Transact-SQLTransact-SQL语句创建数据库，命令格式：语句创建数据库，命令格式：CREATE DATABASE CREATE DATABASE database_name database_name[ [ONON [< [[,...n]]>[,...n]] [,< [,[,...n]]>[,...n]]] ][ [LOG ONLOG ON {< {[,...n]}>[,...n]}] ] 命令行中符号的含义命令行中符号的含义n| | （（竖线）分隔括号或大括号内的语法项目只能选竖线）分隔括号或大括号内的语法项目只能选择一个项目择一个项目n[ ][ ] （方括号）可选语法项目方括号）可选语法项目不必键入方括号不必键入方括号。
n{ }{ }（大括号）必选语法项大括号）必选语法项不要键入大括号不要键入大括号n[ ,...[ ,...n n ] ]表示前面的项可重复表示前面的项可重复 n n 次n< <标签标签> > ::= ::=语法块的名称此规则用于对可在语句中语法块的名称此规则用于对可在语句中的多个位置使用的过长语法或语法单元部分进行分组的多个位置使用的过长语法或语法单元部分进行分组和标记适合使用语法块的每个位置由括在尖括号内和标记适合使用语法块的每个位置由括在尖括号内的标签表示：的标签表示：< <标签标签> > < ∷=>∷= [ [PRIMARYPRIMARY] ] ( [ ( [ NAMENAME= =逻辑名逻辑名] ] [, [, FILENAMEFILENAME=‘=‘物理文件名和路径物理文件名和路径’’] ] [, [,SIZESIZE= =文件初始容量文件初始容量] ] [, [,MAXSIZEMAXSIZE={={最大容量最大容量| |UNLIMITEDUNLIMITED}] }] [, [,FILEGROWTHFILEGROWTH= =递增值递增值] ) [1,…n]] ) [1,…n]数据库的创建数据库的创建 n< ∷=>∷= FILEGROUP FILEGROUP filegroup_namefilegroup_name < >4.2 4.2 数据库的创建数据库的创建 数据库的创建数据库的创建例例1 1 最简形式的创建数据库最简形式的创建数据库( (不指定文件不指定文件) )语句。
语句CREATE DATABASE SalesCREATE DATABASE Sales例例2 2 不指定不指定SIZESIZE创建数据库创建数据库CREATE DATABASE Sales2CREATE DATABASE Sales2ON ON ( NAME=Sales2_dat,( NAME=Sales2_dat, FILENAME='C:\DataBase\Sales2.mdf') FILENAME='C:\DataBase\Sales2.mdf') 数据库的创建数据库的创建例例3 3 创建简单的数据库创建简单的数据库CREATE DATABASE Sales3CREATE DATABASE Sales3ON ON ( NAME=Sales3_dat,( NAME=Sales3_dat, FILENAME='C:\DataBase\Sales3.mdf', FILENAME='C:\DataBase\Sales3.mdf', SIZE=4, SIZE=4, MAXSIZE=10, MAXSIZE=10, FILEGROWTH=1 ) FILEGROWTH=1 ) 数据库的创建数据库的创建例例4-4 4-4 创建指定数据文件和事务日志文件的数据库。
创建指定数据文件和事务日志文件的数据库CREATE DATABASE Sales4CREATE DATABASE Sales4ON ON ( NAME=Sales4_dat,( NAME=Sales4_dat, FILENAME='C:\DataBase\sales4dat.mdf', FILENAME='C:\DataBase\sales4dat.mdf', SIZE=10000KB, SIZE=10000KB, MAXSIZE=500000KB, MAXSIZE=500000KB, FILEGROWTH=5 ) FILEGROWTH=5 )LOG ONLOG ON( NAME=Sales4_log,( NAME=Sales4_log, FILENAME='C:\DataBase\sales4log.ldf', FILENAME='C:\DataBase\sales4log.ldf', SIZE=5, SIZE=5, MAXSIZE=25, MAXSIZE=25, FILEGROWTH=5 ) FILEGROWTH=5 ) 使用使用Transact-SQLTransact-SQL建立表建立表 CREATECREATE TABLETABLE < <表名表名> >（（{ {< <列定义列定义> > | [ | [计算列定义计算列定义] ] | [ | [，，……n]……n] | [< | [<表级完整性约束表级完整性约束>] >] } } ）） 表名表名：：database_namedatabase_name. owner. . owner. table_nametable_namedatabase_namedatabase_name：：用于指定在其中创建表的用于指定在其中创建表的数据数据 库名称。
库名称 ownerowner：：用于指定新建表的所有者的用户名用于指定新建表的所有者的用户名 table_nametable_name：：用于指定新建的表的名称用于指定新建的表的名称使用使用Transact-SQLTransact-SQL建立表建立表 < <列定义列定义> >::= { ::= { 列名列名 数据类型数据类型 } } [ [ COLLATECOLLATE < collation_name > ] < collation_name > ] [ [ DEFAULTDEFAULT 常量表达式常量表达式 ] ] |[ |[ IDENTITY IDENTITY [ ( [ ( 种子种子, ,种子增量种子增量) ] ]) ] ] [ [ ROWGUIDCOLROWGUIDCOL] ] [ [ < < column_constraintcolumn_constraint > > ] [ ...n ] ] [ ...n ]使用使用Transact-SQLTransact-SQL建立表建立表 列定义选项说明列定义选项说明n数据类型：定义字段数据类型和长度，长度需要数据类型：定义字段数据类型和长度，长度需要使用括号使用括号( )( )括起来括起来nCOLLATECOLLATE < collation_name > < collation_name >：：指定排序规则，指定排序规则，默认为数据库的默认值。
只有字符型字段才能指默认为数据库的默认值只有字符型字段才能指定排序规则定排序规则nDEFAULTDEFAULT 常量表达式：定义字段的默认值常量表达式：定义字段的默认值nIDENTITYIDENTITY ( ( 种子种子 , ,增量增量) )：定义字段标识属性，：定义字段标识属性，允许字段自动编号种子值和增量默认为允许字段自动编号种子值和增量默认为1 1一个表中只允许有一个标识字段表中只允许有一个标识字段 列定义列定义CREATE TABLE test1CREATE TABLE test1( (编号编号 intint IDENTTITYIDENTTITY, ,学号学号 char(8)char(8)成绩成绩 numeric(3,1) numeric(3,1) DEFAULT 0)DEFAULT 0)CREATE TABLE test2( 编号编号 int IDENTTITY（（1，，2））,学号学号 char(8)，，成绩成绩 numeric(3,1)，， DEFAULT 0) nROWGUIDCOLROWGUIDCOL：：设置字段由设置字段由SQL ServerSQL Server自动自动产生一个全局惟一值，只有字段数据类型为产生一个全局惟一值，只有字段数据类型为uniqueidentifieruniqueidentifier时，才可设置该选项。
时，才可设置该选项列定义选项说明列定义选项说明 < <列定义列定义> >::= { ::= { 列名列名 数据类型数据类型 } } [ [ COLLATECOLLATE < collation_name > ] < collation_name > ] [ [ [ [ DEFAULTDEFAULT 常量表达式常量表达式 ] ] |[ |[ IDENTITY IDENTITY [ ( [ ( 种子种子, ,种子增量种子增量)] ])] ] [ [ ROWGUIDCOLROWGUIDCOL] ] [ [ < < column_constraintcolumn_constraint > > ] [ ...n ] ] [ ...n ]使用使用Transact-SQLTransact-SQL建立表建立表 n约束约束(1) (1) 非空值约束非空值约束(Not Null) (Not Null) (2) (2) 默认约束默认约束(Default) (Default) (3) (3) 惟一性约束惟一性约束(Unique (Unique ））(4) (4) 主键约束主键约束(Primary Key) (Primary Key) (5) (5) 外键约束外键约束(Foreign Key)(Foreign Key)(6) (6) 检查约束（检查约束（checkcheck））SQL ServerSQL Server表概述表概述 < < column_constraintcolumn_constraint>::=[>::=[CONSTRAINT CONSTRAINT 约束名约束名] ] { { [ [ NULLNULL | | NOT NULLNOT NULL ] ]| [ { | [ { PRIMARY KEY PRIMARY KEY | | UNIQUE UNIQUE } } | [ | [ CLUSTEREDCLUSTERED | | NONCLUSTEREDNONCLUSTERED ] ] ] ] | [ [ | [ [ FOREIGN KEYFOREIGN KEY] ] REFERENCESREFERENCES 参考表参考表 [ ( [ ( 参考列参考列 ) ] ]) ] ] | | CHECKCHECK [ [ NOT FOR REPLICATIONNOT FOR REPLICATION ] ] ( logical_conditions ( logical_conditions ) ) } } 列约束列约束 建立职工表并包含一个约束名为建立职工表并包含一个约束名为salarysalarycreate table create table 职工职工( ( 仓库号仓库号 char(5) primary key,char(5) primary key, 职工号职工号 char (5),char (5), 工资工资 intint default 1200 default 1200 constraint salaryconstraint salary checkcheck( (工资工资>=1000 and >=1000 and 工资工资<=5000) <=5000) ) ) create table create table 职工职工( ( 仓库号仓库号 char(5) char(5) foreign keyforeign key ( (仓库号仓库号) ) referencesreferences 仓库仓库( (仓库号仓库号), ), 职工号职工号 char(5) char(5) primary keyprimary key, , 工资工资 intint constraint salary constraint salary checkcheck ( (工资工资>=1000 >=1000 and and 工资工资<=5000) <=5000) default 1200 default 1200) )建立职工表建立职工表 使用使用Transact-SQLTransact-SQL建立表建立表 CREATE TABLECREATE TABLE < <表名表名> >（（{ {< <列定义列定义> > | [ | [计算列定义计算列定义] ] | [ | [，，……n]……n] | [< | [<表级完整性约束表级完整性约束>] >] } } ）） 表的创建与维护表的创建与维护例例 对计算列使用表达式。
对计算列使用表达式CREATE TABLECREATE TABLE salaryssalarys( ( 姓名姓名 varchar(10),varchar(10), 基本工资基本工资 money,money, 奖金奖金 money,money, 总计总计 ASAS 基本工资基本工资+ +奖金奖金) ) 表的创建与维护表的创建与维护例例 定义表定义表autouserautouser自动获取用户名称自动获取用户名称CREATE TABLE CREATE TABLE autouserautouser( ( 编号编号 intint identity(1,1) NOT NULL, identity(1,1) NOT NULL, 用户代码用户代码 varchar(18),varchar(18), 登录时间登录时间 ASAS GetdateGetdate( ),( ), 用户名用户名 AS AS User_nameUser_name( )( )) ) 使用使用Transact-SQLTransact-SQL建立表建立表 CREATE TABLECREATE TABLE < <表名表名> >（（{ {< <列定义列定义> > | [ | [计算列定义计算列定义] ] | [ | [，，……n]……n] | [< | [<表级完整性约束表级完整性约束>] >] } } ）） < < column_constraintcolumn_constraint>::=>::= [ [CONSTRAINT CONSTRAINT 约束名约束名] ] { {| [ { | [ { PRIMARY KEY PRIMARY KEY | | UNIQUE UNIQUE } } | [ | [ CLUSTEREDCLUSTERED| |NONCLUSTEREDNONCLUSTERED ] ] ] ] | [ [ | [ [ FOREIGN KEYFOREIGN KEY] ] REFERENCESREFERENCES 参考表参考表 [ ( [ ( 参考列参考列 ) ] ]) ] ] | | CHECKCHECK [ [ NOT FOR REPLICATIONNOT FOR REPLICATION ] ] ( logical_conditions ( logical_conditions ) ) } } 表级完整性约束表级完整性约束 表级完整性约束表级完整性约束CREATE TABLE CREATE TABLE 职工职工( ( 仓库号仓库号 char(5) char(5) foreign keyforeign key ( (仓库号仓库号) ) references references 仓库仓库( (仓库号仓库号), ), 职工号职工号 char(5),char(5), 工资工资 intint constraint salary constraint salary checkcheck( (工资工资>=1000 and >=1000 and 工资工资<=5000) default 1200,<=5000) default 1200,primary key (primary key (仓库号仓库号, ,职工号职工号) )) ) 表的创建与维护表的创建与维护例例 创建临时表。
创建临时表CREATE TABLE CREATE TABLE # #studentsstudents( ( 学号学号 varchar(8),varchar(8), 姓名姓名 varchar(10),varchar(10), 性别性别 varchar(2),varchar(2), 班级班级 varchar(10)varchar(10)) ) 使用使用Transact-SQLTransact-SQL建立表建立表 CREATE TABLECREATE TABLE < <表名表名> >（（{ {< <列定义列定义> > | [ | [计算列定义计算列定义] ] | [ | [，，……n]……n] | [< | [<表级完整性约束表级完整性约束>] >] } } ）） 练习练习建立一个建立一个““学生选课学生选课””表表SCSC，，它由学号它由学号SnoSno、、课程号课程号CnoCno，，修课成绩修课成绩GradeGrade组成，其中组成，其中( (SnoSno, , CnoCno) )为主码CREATE TABLE SCCREATE TABLE SC( ( SnoSno CHAR(5) , CHAR(5) , CnoCno CHAR(3) , CHAR(3) , Grade Grade intint, , Primary key ( Primary key (SnoSno, , CnoCno) )) ) 订购单订购单字段名字段名数据类型数据类型主关键字主关键字外部关外部关键字键字参照的参照的表表取值说明取值说明职工号职工号char(5)char(5)是是职工职工不允许不允许为空为空供应供应商名商名char(5)char(5)是是供应商供应商不允许不允许为空为空订购订购单号单号char(5)char(5)是是不允许不允许为空为空订购订购日期日期datetimedatetime默认值为系默认值为系统日期统日期 CREATE TABLE CREATE TABLE 订购单订购单( ( 职工号职工号 char(5) char(5) FOREIGN KEYFOREIGN KEY REFERENCES REFERENCES 职工职工 NOT NULLNOT NULL, , 供应商名供应商名 char(5) char(5) FOREIGN KEYFOREIGN KEY REFERENCESREFERENCES 供应商供应商 NOT NULLNOT NULL, , 订购单号订购单号 char(5) char(5) PRIMARY KEYPRIMARY KEY, , 订购日期订购日期 datetimedatetime DEFAULT GETDATEDEFAULT GETDATE( )( ) ) ) 修改表结构修改表结构——ALTER TABLEALTER TABLE语句语句ALTER TABLEALTER TABLE < <表名表名> > ALTER ALTER COLUMNCOLUMN < <列名列名> <> <数据类型数据类型> [<> [<列属性列属性>]>] | |ADDADD < <列名列名> <> <数据类型数据类型> [<> [<列级完整性约束列级完整性约束>]>] | |ADDADD < <表级完整性约束表级完整性约束> > | |DROPDROP < <完整性约束名完整性约束名> > | |DROP DROP COLUMNCOLUMN < <列名列名> > 修改字段定义修改字段定义ALTERALTER TABLE table TABLE table [ [ALTER COLUMN ALTER COLUMN column_namecolumn_name {new_data_type [({new_data_type [( precision [ , scale ])]precision [ , scale ])] [ NULL | NOT NULL ] [ NULL | NOT NULL ] | {ADD | DROP } ROWGUIDCOL } | {ADD | DROP } ROWGUIDCOL } ] ] 注：注： ALTER COLUMN ALTER COLUMN 一次只能修改一个字段一次只能修改一个字段 例如例如: : 下面的语句可以将下面的语句可以将““成绩成绩””数据表中的数据表中的““学学号号””字段修改为字段修改为char(10)char(10)和和NOT NULLNOT NULL ALTER TABLEALTER TABLE 成绩成绩 ALTER ALTER COLUMNCOLUMN 学号学号 char(10) char(10) NOT NULLNOT NULL 修改字段修改字段 续续| | ADDADD { [ < { [ <列定义列定义>] >] | | 列名列名 ASAS 计算列表达式计算列表达式 } } [ , ... n ] [ , ... n ]| [ | [ WITH CHECK | WITH NOCHECK] WITH CHECK | WITH NOCHECK] ADDADD { < { <表约束表约束> } [ , ... n] > } [ , ... n] nWITH CHECKWITH CHECK | | WITH NOCHECKWITH NOCHECK用于指用于指定已经存在于表中的数据是否需要使用新添定已经存在于表中的数据是否需要使用新添加的或者刚启用的加的或者刚启用的 FOREIGN KEY FOREIGN KEY 或或 CHECK CHECK 约束进行验证。
约束进行验证 添加字段添加字段[ [例例] ] 向向StudentStudent表表增增加加““入入学学时时间间””列列，，其其数数据类型为日期型据类型为日期型 ALTER TABLEALTER TABLE Student Student ADDADD ScomeScome DATETIME DATETIME不不论论表表中中原原来来是是否否已已有有数数据据，，新新增增加加的的列列一一律律为空值 添加字段添加字段 如果新字段不允许空（如果新字段不允许空（NOT NULLNOT NULL），），又没又没有设置默认值，则不能添加新字段有设置默认值，则不能添加新字段 例如，下面的语句可以为例如，下面的语句可以为““成绩成绩””数据表添加数据表添加一个字段一个字段 ALTER TABLEALTER TABLE 成绩成绩 ADDADD 班级班级 varchar(20) varchar(20) not nullnot null DEFAULTDEFAULT ‘04 ‘04级计算机应用级计算机应用' ' CREATE TABLECREATE TABLE xsxs ( a INT) ( a INT) ALTER ALTER TABLETABLE xsxs ADD ADD b VARCHAR(20)b VARCHAR(20) NULLNULL CONSTRAINT CONSTRAINT b b UNIQUEUNIQUE向表中添加具有向表中添加具有 UNIQUE UNIQUE 约束的新列。
约束的新列 向表中的现有列上添加约束向表中的现有列上添加约束 CREATE TABLE CREATE TABLE xsxs( a INT) ( a INT) ALTER TABLEALTER TABLE xsxs WITH NOCHECK ADDWITH NOCHECK ADD CONSTRAINTCONSTRAINT a_checka_check CHECKCHECK ( a > 1) ( a > 1) 利用利用 WITH NOCHECK WITH NOCHECK 来防止对现有行验来防止对现有行验证约束，从而允许该约束的添加证约束，从而允许该约束的添加 删除表字段定义删除表字段定义| | DROPDROP { [ { [ CONSTRAINTCONSTRAINT ] constraint_name ] constraint_name | | COLUMNCOLUMN column } [ ,...n ] column } [ ,...n ] | | { { CHECK | NOCHECKCHECK | NOCHECK } CONSTRAINT } CONSTRAINT { { ALLALL | constraint_name [ ,...n ] } | constraint_name [ ,...n ] }} } { CHECK | NOCHECK} CONSTRAINT{ CHECK | NOCHECK} CONSTRAINT：：用用于指定启用或禁用于指定启用或禁用FOREIGN KEYFOREIGN KEY或者或者CHECKCHECK约束。
约束ALL ALL ：：用于指定使用用于指定使用 NOCHECK NOCHECK 选项禁用所选项禁用所有的约束，或者使用有的约束，或者使用 CHECK CHECK 选项启用所有约选项启用所有约束 表的创建与维护表的创建与维护例例 更改表以添加新列，然后再删除该列更改表以添加新列，然后再删除该列ALTER TABLEALTER TABLE employee employeeADDADD email varchar(20) NULL email varchar(20) NULLALTER TABLEALTER TABLE employee employeeDROP COLUMNDROP COLUMN email email 例如：例如： alteralter tabletable 学生学生 addadd 英语英语 intint constraintconstraint encjencj defaultdefault 80 80现要删除英语列，如何做？现要删除英语列，如何做？Alter tableAlter table 学生学生 drop drop constraint constraint encjencjAlter tableAlter table 学生学生 dropdrop column column 英语英语 订购单订购单字段名字段名数据类型数据类型主关键字主关键字外部关外部关键字键字参照的参照的表表取值说明取值说明职工号职工号char(5)char(5)是是职工职工不允许不允许为空为空供应供应商名商名char(5)char(5)是是供应商供应商不允许不允许为空为空订购订购单号单号char(5)char(5)是是不允许不允许为空为空订购订购日期日期datetimedatetime默认值为系默认值为系统日期统日期 CREATE TABLE CREATE TABLE 订购单订购单( ( 职工号职工号 char(5) char(5) FOREIGN KEYFOREIGN KEY REFERENCES REFERENCES 职工职工 NOT NULLNOT NULL, , 供应商名供应商名 char(5) char(5) FOREIGN KEYFOREIGN KEY REFERENCESREFERENCES 供应商供应商 NOT NULLNOT NULL, , 订购单号订购单号 char(5) char(5) PRIMARY KEYPRIMARY KEY, , 订购日期订购日期 datetimedatetime DEFAULT GETDATEDEFAULT GETDATE( )( ) ) ) 表的创建与维护表的创建与维护n使用使用Transact-SQLTransact-SQL语句删除表语句删除表语句格式如下：语句格式如下：DROP TABLEDROP TABLE table_nametable_name其中，其中，table_nametable_name是要删除的表名。
是要删除的表名注意：注意：(1) (1) 定义有外键约束的表必须先删除外键约束，才定义有外键约束的表必须先删除外键约束，才 能删除2) (2) 系统表不能使用系统表不能使用DROP TABLEDROP TABLE语句删除语句删除 表的创建与维护表的创建与维护例例 删除当前数据库内的表删除当前数据库内的表USE SalesUSE SalesGOGODROP TABLEDROP TABLE employee employee例例 删除另外一个数据库内的表删除另外一个数据库内的表DROP TABLEDROP TABLE Sales.dbo.employeeSales.dbo.employee本例删除本例删除SalesSales数据库内的数据库内的employeeemployee表可以在任何数据库内执行此操作在任何数据库内执行此操作 n索引概述索引概述n索引的操作索引的操作3.2.3 3.2.3 索引的管理索引的管理 索引概述索引概述n索引的基本概念索引的基本概念 索引索引是对数据库表中一个或多个列的值进行排序的结是对数据库表中一个或多个列的值进行排序的结构，可以利用索引快速访问数据库表中的特定信息。
构，可以利用索引快速访问数据库表中的特定信息索引名称）（索引名称）索引项索引项记录号记录号索引值索引值1 1记录号记录号1 1索引值索引值2 2记录号记录号2 2……………………索引值索引值n n记录号记录号n n 建立索引的目的有以下几点：建立索引的目的有以下几点：(1)(1)加速数据检索加速数据检索(2)(2)加速连接、加速连接、ORDER BYORDER BY和和GROUP BYGROUP BY等等操作操作(3)(3)查询优化器依赖于索引起作用查询优化器依赖于索引起作用(4)(4)强制实行的惟一性强制实行的惟一性索引概述索引概述 q惟一索引和非惟一索引惟一索引和非惟一索引q聚集索引和非聚集索引聚集索引和非聚集索引q单列索引和复合索引单列索引和复合索引索引的分类索引的分类 索引概述索引概述n索引的分类索引的分类1 1．．惟一性索引惟一性索引 在表中建立惟一性索引时，组成该在表中建立惟一性索引时，组成该索引的字段或字段组合在表中具有惟一索引的字段或字段组合在表中具有惟一值，也就是说，对于表中的任何两行记值，也就是说，对于表中的任何两行记录来说，录来说，索引键的值都是各不相同索引键的值都是各不相同。
2 2．主键索引．主键索引表中通常有一个字段或一些字段的组合，其表中通常有一个字段或一些字段的组合，其值用来惟一标识表中的每一行记录，该字段或值用来惟一标识表中的每一行记录，该字段或字段组合称为表的主键字段组合称为表的主键不能有空值不能有空值NULLNULL 表中设表中设主键后主键后将自动创建主键索引将自动创建主键索引 ，它是，它是 唯一索引的特殊类型唯一索引的特殊类型索引概述索引概述 索引概述索引概述3 3．聚集索引．聚集索引 在聚集索引中，在聚集索引中，表中各记录的物理顺序与键值的表中各记录的物理顺序与键值的逻辑逻辑( (索引索引) )顺序相同顺序相同只有在表中建立了一个聚集索只有在表中建立了一个聚集索引后，数据才会按照索引键值指定的顺序存储到表中引后，数据才会按照索引键值指定的顺序存储到表中 由于一个表中的数据只能按照一种顺序来存储，由于一个表中的数据只能按照一种顺序来存储，所以在所以在一个表中只能建立一个聚集索引一个表中只能建立一个聚集索引 索引的操作索引的操作3 3．使用．使用Transact-SQLTransact-SQL语句创建索引语句创建索引 create create [unique] [clustered|[unique] [clustered| nonclusterednonclustered] ] index index < <索引名索引名> > on on 表名表名( (列名列名[ [asc|descasc|desc],…n)],…n) 说明：说明：nUniqueUnique、、clusteredclustered、、nonclusterednonclustered用来指定用来指定索引的类型，无这些选项时系统默认创建的是非索引的类型，无这些选项时系统默认创建的是非唯一索引、非聚集索引唯一索引、非聚集索引n由由 ntextntext、、text text 或或 image image 数据类型组成的列不数据类型组成的列不能指定为索引列。
能指定为索引列nASC/DESC ASC/DESC 确定索引列的排序方法，默认为确定索引列的排序方法，默认为ASCASC 索引的操作索引的操作例例 按按employeeemployee表的表的employee_nameemployee_name 列建立非聚集索引列建立非聚集索引CREATE NONCLUSTERED CREATE NONCLUSTERED INDEX INDEX name_idxname_idx ON ON employee(employee_name)employee(employee_name) 例例14: SC14: SC表按学号升序和课程号降序建表按学号升序和课程号降序建 唯一索引唯一索引CREATE UNIQUE INDEX CREATE UNIQUE INDEX SCnoSCno ON ON SC(SnoSC(Sno ASC ASC，，CnoCno DESC) DESC) 索引的操作索引的操作 索引的操作索引的操作n 删除索引删除索引 索引会减慢索引会减慢INSERTINSERT、、UPDATEUPDATE和和DELETEDELETE语句的执行速度。
如果发现索语句的执行速度如果发现索引阻碍整体性能或不再需要索引，则可引阻碍整体性能或不再需要索引，则可将其删除将其删除 索引的操作索引的操作2 2．使用．使用Transact-SQLTransact-SQL语句删除索引语法格式：语句删除索引语法格式： DROP INDEXDROP INDEX 表名表名. .索引名索引名[ ,...n ][ ,...n ]各选项的含义：各选项的含义：(1)(1)表名表名：索引列所在的表索引列所在的表2)(2)索引名：要删除的索引名称索引名必须符合索引名：要删除的索引名称索引名必须符合标识符的规则标识符的规则 索引的操作索引的操作例例 删除删除employeeemployee表内名为表内名为index_2index_2的索引USE SalesUSE SalesIF EXISTSIF EXISTS (SELECT name FROM (SELECT name FROMsysindexessysindexes WHERE name = 'index_2') WHERE name = 'index_2')DROP INDEXDROP INDEX employee. index_2 employee. index_2GOGO 3.33.3数据查询数据查询n3.3.13.3.1语句格式语句格式 SELECTSELECT [ALL|DISTINCT] < [ALL|DISTINCT] <目标列表达式目标列表达式> > [ [，，< <目标列表达式目标列表达式>] …>] …FROM FROM < <表名或视图名表名或视图名>[>[，， < <表名或视图名表名或视图名> ] …> ] …[ [ WHEREWHERE < <条件表达式条件表达式> ]> ][ [ GROUP BYGROUP BY < <列名列名1> [ 1> [ HAVING HAVING < <条件表达式条件表达式> ] ]> ] ][ [ ORDER BYORDER BY < <列名列名2> [ ASC|DESC ] ]2> [ ASC|DESC ] ]；； 3.3 3.3 数据查询数据查询 n3.3.1 3.3.1 单表查询单表查询n3.3.2 3.3.2 连接查询连接查询n3.3.3 3.3.3 嵌套查询嵌套查询n3.3.4 3.3.4 集合查询集合查询n3.3.5 Select3.3.5 Select语句的一般形式语句的一般形式 3.3.1 单表查询单表查询 n查询仅涉及一个表：查询仅涉及一个表：n一、一、 选择表中的若干列选择表中的若干列n二、二、 选择表中的若干元组选择表中的若干元组n三、三、 ORDER BYORDER BY子句子句n四、四、 聚集函数聚集函数n五、五、 GROUP BYGROUP BY子句子句 一、一、 选择表中的若干列选择表中的若干列n查询指定列查询指定列[ [例例1] 1] 查询全体学生的学号与姓名。
查询全体学生的学号与姓名SELECT SELECT SnoSno，，SnameSnameFROM StudentFROM Student；； [ [例例2] 2] 查询全体学生的姓名、学号、所在系查询全体学生的姓名、学号、所在系SELECT SELECT SnameSname，，SnoSno，，SdeptSdeptFROM StudentFROM Student；； 2. 2. 查询全部列查询全部列n选出所有属性列：选出所有属性列：n在在SELECTSELECT关键字后面列出所有列名关键字后面列出所有列名 n将将< <目标列表达式目标列表达式> >指定为指定为 * *[ [例例3] 3] 查询全体学生的详细记录查询全体学生的详细记录SELECT * FROM StudentSELECT * FROM Student；； 3. 3. 查询经过计算的值查询经过计算的值 nSELECTSELECT子句的子句的< <目标列表达式目标列表达式> >可以为：可以为：q算术表达式算术表达式q字符串常量字符串常量q函数函数q列别名列别名 [ [例例4] 4] 查全体学生的姓名及其出生年份。
查全体学生的姓名及其出生年份SELECT SELECT SnameSname，，2004-Sage2004-SageFROM StudentFROM Student；；输出结果：输出结果： SnameSname 无列名无列名 李勇李勇 19841984 刘晨刘晨 19851985 王敏王敏 19861986 张立张立 1985 1985 查询经过计算的值（续）查询经过计算的值（续） 查询经过计算的值（续）查询经过计算的值（续）[ [例例5]5]查查询询全全体体学学生生的的姓姓名名、、出出生生年年份份和和所所有有系系，，要要求求用用小小写写字字母母 表示所有系名表示所有系名SELECT SELECT Sname,‘YearSname,‘Year of Birth: ' of Birth: '，，2004-Sage2004-Sage，，LOWER(SdeptLOWER(Sdept) FROM Student) FROM Student；；输出结果：输出结果： SnameSname 无列名无列名 无列名无列名 LOWER(SdeptLOWER(Sdept) ) 李勇李勇 Year of Birth: 1984 Year of Birth: 1984 cscs 刘晨刘晨 Year of Birth: 1985 Year of Birth: 1985 isis 王敏王敏 Year of Birth: 1986 Year of Birth: 1986 mama 张立张立 Year of Birth: 1985 Year of Birth: 1985 is is 操作查询的列名操作查询的列名 用用户户可可以以根根据据实实际际需需要要对对查查询询数数据据的的列列标标题题进进行行修修改改，，或或者为没有标题的列加上临时的标题。
者为没有标题的列加上临时的标题对列名进行操作有对列名进行操作有3 3种方式：种方式：（（1 1））采采用用符符合合ANSIANSI规规则则的的标标准准方方法法，，在在列列表表达达式式后后面面给给出列名2 2）用）用““=”=”来连接列表达式来连接列表达式 （（3 3）用）用ASAS关键字来连接列表达式和指定的列名关键字来连接列表达式和指定的列名 查询经过计算的值（续）查询经过计算的值（续）n使用列使用列别名别名改变查询结果的列标题改变查询结果的列标题: : SELECT SELECT SnameSname NAMENAME，，'Year of Birth: ’ 'Year of Birth: ’ BIRTHBIRTH，， 2000-Sage 2000-Sage BIRTHDAYBIRTHDAY，，LOWER(SdeptLOWER(Sdept) ) DEPARTMENTDEPARTMENT FROM Student FROM Student；；输出结果：输出结果： NAME BIRTH BIRTHDAY DEPARTMENTNAME BIRTH BIRTHDAY DEPARTMENT ------- ---------------- ------------- ------------------ ------- ---------------- ------------- ------------------ 李勇李勇 Year of Birth: 1984 Year of Birth: 1984 cscs 刘晨刘晨 Year of Birth: 1985 isYear of Birth: 1985 is 王敏王敏 Year of Birth: 1986 maYear of Birth: 1986 ma 张立张立 Year of Birth: 1985 isYear of Birth: 1985 is select select 图书代号图书代号=title_id, =title_id, 原价原价=price, =price, 现价现价= price-price*0.3= price-price*0.3from titlesfrom titles操作查询的列名操作查询的列名 select title_id as select title_id as 图书代号图书代号, ,price as price as 原价原价, ,price-price*0.3 as price-price*0.3 as 现价现价from titlesfrom titles操作查询的列名操作查询的列名 select title_id select title_id 图书代号图书代号, ,price price 原价原价, price-price*0.3 , price-price*0.3 现价现价from titlesfrom titles操作查询的列名操作查询的列名 3.3.1 单表查询单表查询 n查询仅涉及一个表：查询仅涉及一个表：n一、一、 选择表中的若干列选择表中的若干列n二、二、 选择表中的若干元组选择表中的若干元组n三、三、 ORDER BYORDER BY子句子句n四、四、 聚集函数聚集函数n五、五、 GROUP BYGROUP BY子句子句 数据查询数据查询n语句格式语句格式 SELECTSELECT [ALL|DISTINCT] < [ALL|DISTINCT] <目标列表达式目标列表达式> > [ [，，< <目标列表达式目标列表达式>] …>] …FROM FROM < <表名或视图名表名或视图名>[>[，， < <表名或视图名表名或视图名> ] …> ] …[ [ WHEREWHERE < <条件表达式条件表达式> ]> ][ [ GROUP BYGROUP BY < <列名列名1> [ 1> [ HAVING HAVING < <条件表达式条件表达式> ] ]> ] ][ [ ORDER BYORDER BY < <列名列名2> [ ASC|DESC ] ]2> [ ASC|DESC ] ]；； 二、选择表中的若干元组二、选择表中的若干元组n1. 1. 消除取值重复的行消除取值重复的行 如果没有指定如果没有指定DISTINCTDISTINCT关键词，则缺省为关键词，则缺省为ALL ALL [ [例例6] 6] 查询选修了课程的学生学号。
查询选修了课程的学生学号 SELECT SELECT SnoSno FROM SC FROM SC；；等价于：等价于：SELECT SELECT ALL ALL SnoSno FROM SC FROM SC；； SnoSno200215121200215121200215121200215121200215121200215121200215122200215122200215122200215122执行上面的执行上面的SELECTSELECT语句后，结果为：语句后，结果为： 消除取值重复的行（续）消除取值重复的行（续）n指定指定DISTINCTDISTINCT关键词，去掉表中重复的行关键词，去掉表中重复的行 SELECT SELECT DISTINCTDISTINCT SnoSno FROM SC FROM SC；； 执行结果：执行结果： SnoSno200215121200215121200215122200215122 2.2.查询满足条件的元组查询满足条件的元组查查 询询 条条 件件谓谓 词词比比 较较= =，，> >，，< <，，>=>=，，<=<=，，!=!=，，<><>，，!>!>，，!
］查询计算机科学系全体学生的名单 SELECT SELECT SnameSname FROM Student FROM Student WHERE WHERE SdeptSdept=‘CS’=‘CS’；； [ [例例8] 8] 查询所有年龄在查询所有年龄在2020岁以下的学生姓名及其年龄岁以下的学生姓名及其年龄 SELECT SELECT Sname,SageSname,Sage FROM Student FROM Student WHERE Sage < 20 WHERE Sage < 20；； ［例［例9 9］］ 查询考试成绩有不及格的学生的查询考试成绩有不及格的学生的学号 SELECT SELECT DISTINCTDISTINCT SnoSno FROM SC FROM SC WHERE Grade<60 WHERE Grade<60；； (1) (1) 比较大小比较大小 （（2）确定范围）确定范围n谓词谓词: : BETWEEN … AND …BETWEEN … AND … NOT BETWEEN … AND … NOT BETWEEN … AND …[ [例例10] 10] 查询年龄在查询年龄在20~2320~23岁（包括岁（包括2020岁和岁和2323岁）岁）之间的学生的姓名、系别和年龄之间的学生的姓名、系别和年龄 SELECT SELECT SnameSname，，SdeptSdept，，SageSageFROM StudentFROM StudentWHERE Sage BETWEEN 20 AND 23WHERE Sage BETWEEN 20 AND 23；； [ [例例11] 11] 查查询询年年龄龄不不在在20~2320~23岁岁之之间间的的学学生生姓姓名、系别和年龄名、系别和年龄 SELECT SELECT SnameSname，，SdeptSdept，，SageSage FROM Student FROM Student WHERE Sage NOT BETWEEN WHERE Sage NOT BETWEEN 20 AND 23 20 AND 23；； (3) 确定集合确定集合n谓词：谓词：IN , NOT IN <>, NOT IN <值表值表> > [ [例例12]12]查询信息系（查询信息系（ISIS）、）、数学系（数学系（MAMA））和和计算机科学系（计算机科学系（CSCS））学生的姓名和性别。
学生的姓名和性别SELECT SELECT SnameSname，，SsexSsexFROM StudentFROM StudentWHERE WHERE SdeptSdept IN IN ( 'IS'( 'IS'，，'MA''MA'，，'CS' );'CS' ); [ [例例13]13]查查询询既既不不是是信信息息系系、、数数学学系系，，也也不不是是计计算算机科学系的学生的姓名和性别机科学系的学生的姓名和性别SELECT SELECT SnameSname，，SsexSsexFROM StudentFROM Student WHERE WHERE SdeptSdept NOT NOT ININ ( 'IS' ( 'IS'，，'MA''MA'，，'CS' )'CS' ) (4)字符匹配字符匹配n谓词谓词:[NOT] LIKE:[NOT] LIKE ‘< ‘<匹配串匹配串>’>’ [ESCAPE ‘ < [ESCAPE ‘ <换码字符换码字符>’]>’]1)1)匹配串为固定字符串匹配串为固定字符串[ [例例14] 14] 查询学号为查询学号为200215121200215121的学生的详细情况。
的学生的详细情况 SELECT * SELECT * FROM Student FROM Student WHERE WHERE SnoSno LIKELIKE ‘200215121' ‘200215121'；；等价于：等价于： SELECT * SELECT * FROM Student FROM Student WHERE WHERE SnoSno = ' 200215121 ' = ' 200215121 '；； 通配符通配符解释解释例子例子_一个字符一个字符Like 'L _ '%任意长度任意长度Like 'AMS-%'[ ]指定范围指定范围内内的任的任一个一个字符字符 Like 'AB0[1-5] '[ ^ ]指定范围指定范围外外的任的任一个一个字符字符 Like 'AB[^1-5] '模式匹配符LIKE判断列值是否与指定的字符通配格式相符NOT LIKE 2) 匹配串为含通配符的字符串匹配串为含通配符的字符串 字符匹配（续）字符匹配（续） 2) 2) 匹配串为含通配符的字符串匹配串为含通配符的字符串 [ [例例15] 15] 查询所有姓刘学生的姓名、学号和性别。
查询所有姓刘学生的姓名、学号和性别 SELECT SELECT SnameSname，，SnoSno，，SsexSsex FROM Student FROM Student WHERE WHERE SnameSname LIKE ‘LIKE ‘刘刘%’%’；；[ [例例16] 16] 查询姓查询姓" "欧阳欧阳" "且全名为三个汉字的学生的姓名且全名为三个汉字的学生的姓名 SELECT SELECT SnameSname FROM Student FROM Student WHERE WHERE SnameSname LIKE 'LIKE '欧阳欧阳_' _'；； 字符匹配（续）字符匹配（续）[ [例例17] 17] 查询名字中第查询名字中第2 2个字为个字为" "阳阳" "字的学生的姓名和学号字的学生的姓名和学号 SELECT SELECT SnameSname，，SnoSno FROM Student FROM Student WHERE WHERE SnameSname LIKE ‘__LIKE ‘__阳阳%%’’；；[ [例例18] 18] 查询所有不姓刘的学生姓名。
查询所有不姓刘的学生姓名 SELECT SELECT SnameSname，，SnoSno，，SsexSsex FROM Student FROM Student WHERE WHERE SnameSname NOT LIKENOT LIKE ' '刘刘%'%'；； 例例：：查查询询借借阅阅信信息息表表（（borrowinfborrowinf））中中图图书书编编号以号以A A到到F F的字符开头的所有借阅者信息的字符开头的所有借阅者信息 SELECT * FROM SELECT * FROM borrowinfborrowinfWHERE WHERE 图书编号图书编号 LIKE LIKE ‘‘[A-[A-F]%F]%’’ 字符匹配（续）字符匹配（续）3) 3) 使用换码字符将通配符转义为普通字符使用换码字符将通配符转义为普通字符 [ [例例19] 19] 查询查询DB_DesignDB_Design课程的课程号和学分课程的课程号和学分 SELECT SELECT CnoCno，，CcreditCcredit FROM Course FROM Course WHERE WHERE CnameCname LIKE 'DB LIKE 'DB\ \_Design' _Design' ESCAPE '\‘ESCAPE '\‘ [ [例例20] 20] 查询以查询以"DB_""DB_"开头，且倒数第开头，且倒数第3 3个字个字符为符为 i i的课程的详细情况。
的课程的详细情况 SELECT * FROM CourseSELECT * FROM Course WHERE WHERE CnameCname LIKE LIKE 'DB'DB\ \_%i_ _’ _%i_ _’ ESCAPE ' \’ESCAPE ' \’ ESCAPE 'ESCAPE '＼＼' ' 表示表示“ “ ＼＼” ” 为换码字符为换码字符 (5) 涉及空值的查询涉及空值的查询n谓词：谓词： IS NULL IS NULL 或或 IS NOT NULLIS NOT NULLn “IS” “IS” 不能用不能用 “ “=” =” 代替代替 [ [例例21] 21] 某些学生选修课程后没有参加考试，所以有选某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩查询缺少成绩的学生的学课记录，但没有考试成绩查询缺少成绩的学生的学号和相应的课程号号和相应的课程号 SELECT SELECT SnoSno，，CnoCno FROM SC FROM SC WHERE Grade IS NULL WHERE Grade IS NULL (6) 多重条件查询多重条件查询n逻辑运算符：逻辑运算符：ANDAND和和 OROR来联结多个查询条件来联结多个查询条件n ANDAND的优先级高于的优先级高于ORORn 可以用括号改变优先级可以用括号改变优先级n可用来实现多种其他谓词可用来实现多种其他谓词n [NOT] IN[NOT] INn [NOT] BETWEEN … AND … [NOT] BETWEEN … AND … 多重条件查询（续）多重条件查询（续）n改写改写[ [例例12]12][ [例例12] 12] 查询信息系（查询信息系（ISIS）、）、数学系（数学系（MAMA））和计算和计算机科学系（机科学系（CSCS））学生的姓名和性别。
学生的姓名和性别可改写为：可改写为：SELECT SELECT SnameSname，，SsexSsexFROM StudentFROM StudentWHERE WHERE SdeptSdept= ' IS ' OR = ' IS ' OR SdeptSdept= ' MA' = ' MA' OR OR SdeptSdept= ' CS '= ' CS '；； 3.3.1 单表查询单表查询 n查询仅涉及一个表：查询仅涉及一个表：n一、一、 选择表中的若干列选择表中的若干列n二、二、 选择表中的若干元组选择表中的若干元组n三三、、 ORDER BYORDER BY子句子句n四、四、 聚集函数聚集函数n五、五、 GROUP BYGROUP BY子句子句 三、三、ORDER BY子句子句 nORDER BYORDER BY子句子句n可以按一个或多个属性列排序可以按一个或多个属性列排序n升序：升序：ASCASC；；降序：降序：DESCDESC；；缺省值为升序缺省值为升序n当排序列含空值时当排序列含空值时nASCASC：：排序列为空值的元组最排序列为空值的元组最先先显示显示nDESCDESC：：排序列为空值的元组最排序列为空值的元组最后后显示显示 [ [例例24] 24] 查查询询选选修修了了3 3号号课课程程的的学学生生的的学学号号及及其其成绩，查询结果按分数降序排列。
成绩，查询结果按分数降序排列 SELECT SELECT SnoSno，，GradeGrade FROM SC FROM SC WHERE WHERE CnoCno= ' 3 '= ' 3 ' ORDER BY Grade DESC ORDER BY Grade DESC；； ORDER BY子句子句 （续）（续） ［［例例2525］］查查询询全全体体学学生生情情况况，，查查询询结结果果按按所所在在系系的的系系号号升升序序排排列列，，同同一一系系中中的的学学生生按按年年龄龄降降序序排列 SELECT *SELECT * FROM Student FROM Student ORDER BY ORDER BY SdeptSdept，，Sage DESCSage DESC；； 3.4.1 单表查询单表查询 n查询仅涉及一个表：查询仅涉及一个表：n一、一、 选择表中的若干列选择表中的若干列n二、二、 选择表中的若干元组选择表中的若干元组n三、三、 ORDER BYORDER BY子句子句n四、四、 聚集函数聚集函数n五、五、 GROUP BYGROUP BY子句子句 五、五、GROUP BY子句子句 nGROUP BYGROUP BY子句分组：子句分组： 细化聚集函数的作用对象细化聚集函数的作用对象q 未未对对查查询询结结果果分分组组，，聚聚集集函函数数将将作作用用于于整整个个查查询询结果结果q 对查询结果分组后，聚集函数将分别作用于每个对查询结果分组后，聚集函数将分别作用于每个组组 q作用对象是查询的中间结果表作用对象是查询的中间结果表q按指定的一列或多列值分组，值相等的为一组按指定的一列或多列值分组，值相等的为一组 GROUP BY子句（续）子句（续）[ [例例31] 31] 求各个课程号及相应的选课人数。
求各个课程号及相应的选课人数 SELECT SELECT CnoCno，，COUNT(SnoCOUNT(Sno) ) 选课人数选课人数 FROM SCFROM SC GROUP BY GROUP BY CnoCno；； 查询结果：查询结果： CnoCno 选课人数选课人数1 221 22 2 342 34 3 443 44 4 334 33 5 485 48 GROUP BY子句（续）子句（续）[ [例例32] 32] 查询选修了查询选修了3 3门以上课程的学生学号门以上课程的学生学号 SELECT SELECT SnoSno FROM SC FROM SC GROUP BY GROUP BY SnoSno HAVING COUNT(*) >3 HAVING COUNT(*) >3；； 提提 问问1 1、什么是索引？如何创建索引？、什么是索引？如何创建索引？2 2、什么是聚集索引？、什么是聚集索引？3 3、查询语句的一般格式？、查询语句的一般格式？4 4、查询语句中的、查询语句中的* *和和allall有何区别？有何区别？5 5、、LikeLike有何作用？有哪些通配符？各有什么作有何作用？有哪些通配符？各有什么作用用? ? GROUP BY子句（续）子句（续）nHAVINGHAVING短语与短语与WHEREWHERE子句的区别：子句的区别：q作用对象不同作用对象不同qWHEREWHERE子子句句作作用用于于基基表表或或视视图图，，从从中中选择满足条件的元组选择满足条件的元组qHAVINGHAVING短短语语作作用用于于组组，，从从中中选选择择满满足足条件的组。
条件的组 select select snosno, count(*) as num,, count(*) as num,sum(score),max(scoresum(score),max(score), ), min(scoremin(score) ) from sc group by from sc group by snosno having having avg(scoreavg(score)>=60)>=60查询平均成绩及格的学生所选课程的数量、总分查询平均成绩及格的学生所选课程的数量、总分及最高和最低分及最高和最低分 3.3.1 3.3.1 单表查询单表查询 n查询仅涉及一个表：查询仅涉及一个表：n一、一、 选择表中的若干列选择表中的若干列n二、二、 选择表中的若干元组选择表中的若干元组n三、三、 ORDER BYORDER BY子句子句n四、四、 聚集函数聚集函数n五、五、 GROUP BYGROUP BY子句子句 四、聚集函数四、聚集函数 统计函数统计函数 语义语义 COUNTCOUNT(*) (*) 统计表的记录个数统计表的记录个数 COUNT(DISTINCT|ALL) >) 统计一列中值统计一列中值不为不为NULLNULL值值的个数的个数 SUMSUM(DISTINCT|ALL<(DISTINCT|ALL<列名列名>) >) 计算一列值的总和（计算一列值的总和（此列必须为数值此列必须为数值型型）） AVGAVG(DISTINCT|ALL<(DISTINCT|ALL<列名列名>) >) 计算一列值的平均值（计算一列值的平均值（此列必须为数此列必须为数值型值型）） MAXMAX(DISTINCT|ALL<(DISTINCT|ALL<列名列名>) >) 给出一列值中的最大值给出一列值中的最大值 MINMIN(DISTINCT|ALL<(DISTINCT|ALL<列名列名>) >) 给出一列值中的最小值给出一列值中的最小值 COUNTCOUNT函数函数nCOUNTCOUNT函数返回列中的记录个数。
函数返回列中的记录个数n函数格式：函数格式： COUNT ( [ ALL | DISTINCT ]COUNT ( [ ALL | DISTINCT ] 列名列名 | *)| *)说明：说明：（（1 1））COUNTCOUNT函函数数默默认认使使用用ALLALL参参数数，，它它将将重重复复计计算算相相同的值，但不包含值为同的值，但不包含值为NULLNULL的行2 2））使使用用DISTINCTDISTINCT时时，，相相同同的的值值只只计计数数一一次次，，且且不不包包含含NULLNULL值 （（3 3））在在COUNTCOUNT函函数数中中只只引引用用一一个个列列名名时时，，将将返返回回该该列列中中行行记记录录的的个个数数，，但但不不包包含含值值为为NULLNULL的行4 4））在在COUNTCOUNT函函数数中中可可以以使使用用 * * 做做参参数数，，它它表表示示返返回回表表中中的的所所有有行行数数，，包包括括含含有有NULLNULL值值的行COUNTCOUNT函数函数 例：例：select select count(*)count(*) as as 人数人数 from from 教师教师select select count(count(民族民族) ) 民族数民族数 from from 教师教师select select count(distinct count(distinct 民族民族) ) 民族数民族数 from from 教师教师集合函数集合函数 注意：注意：nWhereWhere子句中不能使用聚集函数子句中不能使用聚集函数n聚集函数除聚集函数除count(*)count(*)外，都不处理空值外，都不处理空值 Transact-SQLTransact-SQL提提供供了了连连接接操操作作符符JOINJOIN，，用用于于从从两两张张或或多多张张数数据据表表的的连连接接中中获获取取数数据据。
两两张张表表之之间间的的连连接接可可以以有以下几种不同的方式：有以下几种不同的方式：q内连接内连接 [ [INNERINNER] JOIN] JOINq外连接外连接 LEFT [LEFT [OUTEROUTER] JOIN ] JOIN RIGHT [ RIGHT [OUTEROUTER] JOIN] JOIN FULL [ FULL [OUTEROUTER] JOIN] JOINq自连接自连接 [ [INNERINNER] JOIN] JOINq交叉连接交叉连接 CROSS JOINCROSS JOIN3.3.3 连接查询连接查询 3.3.3连接查询（续）连接查询（续）一、内连接（等值与非等值连接查询）一、内连接（等值与非等值连接查询） 二、自身连接二、自身连接三、外连接三、外连接四、复合条件连接四、复合条件连接 （（2 2））JOINJOIN和和ONON关键字建立连接的关键字建立连接的命令格式：命令格式：SELECTSELECT select_list select_list FROM FROM table1 table1 [ [INNERINNER] ] JOINJOIN table2 table2 ONON table1.column1 table1.column1 < <比较运算符比较运算符> > table2. Column2 table2. Column2SELECTSELECT 列名列表列名列表 FROMFROM table1, table2table1, table2 WHERE WHERE table1.column1table1.column1 < <比较运算符比较运算符> > table2.column2table2.column2（（1）一般格式：）一般格式：一、内连接（等值与非等值连接查询）一、内连接（等值与非等值连接查询） 一、一、内连接（内连接（等值与非等值连接查询等值与非等值连接查询 ) )n等值连接：连接运算符为等值连接：连接运算符为= =[ [例例33] 33] 查询每个学生及其选修课程的情况查询每个学生及其选修课程的情况SELECT Student.*SELECT Student.*，，SC.*SC.*FROM StudentFROM Student，，SCSCWHERE WHERE Student.SnoStudent.Sno = = SC.SnoSC.Sno；； 等值与非等值连接查询（续）等值与非等值连接查询（续）SnoSnoSnameSnameSsexSsexSageSageSdeptSdeptSnoSnoCnoCnoGradeGrade200215121200215121李勇李勇男男2020CSCS2002151212002151211 19292200215121200215121李勇李勇男男2020CSCS2002151212002151212 28585200215121200215121李勇李勇男男2020CSCS2002151212002151213 38888200215122200215122刘晨刘晨女女1919CSCS2002151222002151222 29090200215122200215122刘晨刘晨女女1919CSCS2002151222002151223 38080 等值与非等值连接查询（续）等值与非等值连接查询（续）n自然连接：自然连接：n[ [例例34]34]对对[ [例例33]33]用自然连接完成。
用自然连接完成 SELECT SELECT Student.SnoStudent.Sno，，SnameSname，，SsexSsex，，SageSage，， SdeptSdept，，CnoCno，，GradeGrade FROM Student FROM Student，，SCSC WHERE WHERE Student.SnoStudent.Sno = = SC.SnoSC.Sno；； 连接查询（续）连接查询（续）一、一、内连接内连接( (等值与非等值连接查询等值与非等值连接查询 ) )二、自身连接二、自身连接三、外连接三、外连接四、复合条件连接四、复合条件连接 二、自身连接二、自身连接 n自身连接：一个表与其自己进行连接自身连接：一个表与其自己进行连接n需要需要给表起别名给表起别名以示区别以示区别n由于所有属性名都是同名属性，因此必须使用由于所有属性名都是同名属性，因此必须使用别名前缀别名前缀[ [例例35]35]查询每一门课的间接先修课（即先修课的先修课）查询每一门课的间接先修课（即先修课的先修课） SELECT SELECT FIRST.CnoFIRST.Cno，，SECOND.CpnoSECOND.Cpno FROM Course FROM Course FIRSTFIRST，，Course Course SECONDSECOND WHERE WHERE FIRST.CpnoFIRST.Cpno = = SECOND.CnoSECOND.Cno；； cnocnocnamecnamecpnocpno1 1数据库数据库5 52 2数学数学3 3信息系统信息系统1 14 4操作系统操作系统6 65 5数据结构数据结构7 76 6数据处理数据处理7 7C C语言语言6 6cnocnocnamecnamecpnocpno1 1数据库数据库5 52 2数学数学3 3信息系统信息系统1 14 4操作系统操作系统6 65 5数据结构数据结构7 76 6数据处理数据处理7 7C C语言语言6 6firstfirstsecondsecond 查查询询booksbooks表表中中至至少少有有两两本本相相同同书书名名的的所所有有图图书书的的信信息，包括编号、书名和作者。
息，包括编号、书名和作者 SELECT distinct a.SELECT distinct a.编号编号,a.,a.书名书名,a.,a.作者作者FROM books FROM books a a JOINJOIN books books b bONON a. a.书名书名=b.=b.书名书名WHEREWHERE a. a.编号编号<>b.<>b.编号编号自身连接自身连接 编号编号书名书名作者作者0001计算机网络计算机网络李书李书0002多媒体技术多媒体技术方正方正0003计算机网络计算机网络张方张方0004计算机网络计算机网络汪洋汪洋编号编号书名书名作者作者0001计算机网络计算机网络 李书李书0002多媒体技术多媒体技术 方正方正0003计算机网络计算机网络 张方张方0004计算机网络计算机网络 汪洋汪洋自身连接自身连接booksbooksbooksbooks 连接查询（续）连接查询（续）一、一、内连接内连接( (等值与非等值连接查询等值与非等值连接查询 ) )二、自身连接二、自身连接三、外连接三、外连接四、复合条件连接四、复合条件连接 三、外连接三、外连接n外连接与内连接的区别外连接与内连接的区别q内连接操作只输出满足连接条件的元组内连接操作只输出满足连接条件的元组q外连接操作以指定表为连接主体，将主体表中不外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出满足连接条件的元组一并输出[ [例例 36] 36] 改写改写[ [例例33]33] SELECT SELECT Student.SnoStudent.Sno，，SnameSname，，SsexSsex，， SageSage，，SdeptSdept，，CnoCno，，GradeGrade FROM Student LEFT JOIN SC ON FROM Student LEFT JOIN SC ON ( (Student.SnoStudent.Sno= =SC.SnoSC.Sno) )；； 外连接（续外连接（续）） 执行结果：执行结果： Student.SnoStudent.SnoSnameSnameSsexSsexSageSageSdeptSdeptCnoCnoGradeGrade200215121200215121李勇李勇男男2020CSCS1 19292200215121200215121李勇李勇男男2020CSCS2 28585200215121200215121李勇李勇男男2020CSCS3 38888200215122200215122刘晨刘晨女女1919CSCS2 29090200215122200215122刘晨刘晨女女1919CSCS3 38080200215123200215123王敏王敏女女1818MAMANULLNULLNULLNULL200215125200215125张张立立男男1919ISISNULLNULLNULLNULL 外连接（续外连接（续）） n 左外连接左外连接列列出出左左边边关关系系（（如如本本例例StudentStudent））中中所所有有的的元元组组 n 右外连接右外连接列出右边关系中所有的元组列出右边关系中所有的元组 n全外连接全外连接 全外连接返回两个表的所有元组。
全外连接返回两个表的所有元组 Use Use 成绩管理成绩管理select * from select * from tableatablea a a right joinright join tablebtableb b on a . c = b . c b on a . c = b . cselect * from select * from tableatablea a a full joinfull join tablebtableb b on a . c = b. c b on a . c = b. c外连接外连接 ntablea tablebA AB BC C1 12 23 33 32 21 14 43 35 5C CD DE E3 37 78 84 46 65 58 87 72 2外连接 连接查询（续）连接查询（续）一、等值与非等值连接查询一、等值与非等值连接查询 二、自身连接二、自身连接三、外连接三、外连接四、复合条件连接四、复合条件连接 3.3 数据查询数据查询 n3.3.1 3.3.1 单表查询单表查询n3.3.2 3.3.2 连接查询连接查询n3.3.3 3.3.3 嵌套查询嵌套查询n3.3.4 3.3.4 集合查询集合查询n3.3.5 Select3.3.5 Select语句的一般形式语句的一般形式 嵌套查询嵌套查询(续续)n嵌套查询概述嵌套查询概述q一个一个SELECT-FROM-WHERESELECT-FROM-WHERE语句称为一语句称为一个个查询块查询块q将一个查询块嵌套在另一个查询块的将一个查询块嵌套在另一个查询块的WHEREWHERE子句或子句或HAVINGHAVING短语的条件中的查短语的条件中的查询称为询称为嵌套查询嵌套查询 嵌套查询嵌套查询(续续) SELECT SELECT SnameSname /*/*外层查询外层查询/ /父查询父查询* */ / FROM Student FROM Student WHERE WHERE SnoSno IN IN （（SELECT SELECT SnoSno FROM SC FROM SC WHERE WHERE CnoCno= ' 2 '= ' 2 '））；；/*/*内层查询内层查询/ /子查询子查询* */ / 嵌套查询嵌套查询(续续)q子查询的限制子查询的限制Ø不能使用不能使用ORDER BYORDER BY子句子句q层层嵌套方式反映了层层嵌套方式反映了 SQLSQL语言的结构化语言的结构化q有些嵌套查询可以用连接运算替代有些嵌套查询可以用连接运算替代 嵌套查询的分类嵌套查询的分类n不相关子查询：不相关子查询： 子查询的查询条件不依赖于父查询，子查询的查询条件不依赖于父查询，由里向外逐层处理。
即每个子查询在上由里向外逐层处理即每个子查询在上一级查询处理之前求解，一级查询处理之前求解，子查询的结果子查询的结果用于建立其父查询的查找条件用于建立其父查询的查找条件 嵌套查询（续）嵌套查询（续）n相关子查询相关子查询：：子查询的查询条件依赖于父查询子查询的查询条件依赖于父查询q首先取外层查询中表的第一个元组，根据它与首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表子句返回值为真，则取此元组放入结果表q然后再取外层表的下一个元组然后再取外层表的下一个元组q重复这一过程，直至外层表全部检查完为止重复这一过程，直至外层表全部检查完为止 3.4.3 嵌套查询嵌套查询 一、一、 带有带有ININ谓词的子查询谓词的子查询 二、二、 带有比较运算符的子查询带有比较运算符的子查询 三、三、 带有带有ANYANY（（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询 四、四、 带有带有EXISTSEXISTS谓词的子查询谓词的子查询 一、带有一、带有ININ谓词的子查询谓词的子查询[ [例例39] 39] 查询与查询与““刘晨刘晨” ” 在同一个系学习的学生。
在同一个系学习的学生①① 确定确定““刘晨刘晨””所在系名所在系名 SELECT SELECT SdeptSdept FROM Student FROM Student WHERE WHERE SnameSname= ' = ' 刘晨刘晨 ' '；； 结果为：结果为： CSCS 带有带有ININ谓词的子查询（续）谓词的子查询（续）② ② 查找所有在查找所有在CSCS系学习的学生系学习的学生 SELECT SELECT SnoSno，，SnameSname，，SdeptSdept FROM Student FROM Student WHERE WHERE SdeptSdept= ' CS '= ' CS '；； 结果为：结果为：SnoSnameSdept200215121李勇李勇CS200215122刘晨刘晨CS 带有带有IN谓词的子查询（续）谓词的子查询（续）将第一步查询嵌入到第二步查询的条件中将第一步查询嵌入到第二步查询的条件中 SELECT SELECT SnoSno，，SnameSname，，SdeptSdept FROM StudentFROM Student WHERE WHERE SdeptSdept ININ ( ( SELECT SELECT SdeptSdept FROM Student FROM Student WHERE WHERE SnameSname= ‘ = ‘ 刘晨刘晨 ’ ’) ) 此查询为不相关子查询。
此查询为不相关子查询 带有带有IN谓词的子查询（续）谓词的子查询（续） 用自身连接完成用自身连接完成[ [例例39]39]查询要求查询要求 SELECT SELECT S1S1.Sno.Sno，，S1S1.Sname.Sname，，S1S1.Sdept.Sdept FROM Student FROM Student S1S1，，Student Student S2S2 WHERE WHERE S1S1.Sdept = .Sdept = S2S2.Sdept AND.Sdept AND S2S2.Sname = '.Sname = '刘晨刘晨' '；； 带有带有IN谓词的子查询（续）谓词的子查询（续）[ [例例40]40]查询选修了课程名为查询选修了课程名为““信息系统信息系统””的学生学号和姓名的学生学号和姓名 SELECT SELECT SnoSno，，SnameSname ③ ③ 最后在最后在StudentStudent关系中关系中 FROM Student FROM Student 取出取出SnoSno和和SnameSname WHERE WHERE SnoSno IN IN (SELECT (SELECT SnoSno ② ② 然后在然后在SCSC关系中找出选关系中找出选 FROM SC FROM SC 修了修了3 3号课程的学生学号号课程的学生学号 WHERE WHERE CnoCno IN IN (SELECT (SELECT CnoCno ① ① 首先首先在在CourseCourse关系中找出关系中找出 FROM Course FROM Course ““信息系统信息系统””的课程号，为的课程号，为3 3号号 WHERE WHERE CnameCname= ‘= ‘信息系统信息系统’’ ) ) ) ) 带有带有IN谓词的子查询（续）谓词的子查询（续）用连接查询实现用连接查询实现[ [例例40]40] SELECT SELECT SnoSno，，SnameSname FROM Student FROM Student，，SCSC，，CourseCourse WHERE WHERE Student.SnoStudent.Sno = = SC.SnoSC.Sno AND AND SC.CnoSC.Cno = = Course.CnoCourse.Cno AND AND Course.CnameCourse.Cname=‘=‘信息系统信息系统’’；； 复复 习习n1 1、什么是相关子查询？、什么是相关子查询？n2 2、什么是不相关子查询？、什么是不相关子查询？n3 3、左外连接和右外连接有何区别？、左外连接和右外连接有何区别？ 3.4.3 3.4.3 嵌套查询嵌套查询一、带有一、带有ININ谓词的子查询谓词的子查询 二、带有比较运算符的子查询二、带有比较运算符的子查询三、带有三、带有ANYANY（（SOMESOME）或）或ALLALL谓词的子谓词的子 查询查询四、带有四、带有EXISTSEXISTS谓词的子查询谓词的子查询 二、带有比较运算符的子查询二、带有比较运算符的子查询n 当能确切知道当能确切知道内层查询返回单值内层查询返回单值时，时，可用比较运算符（可用比较运算符（>，，<，，=，，>=，，<=，，!=或或< >）。
n与与ANY或或ALL谓词配合使用谓词配合使用 带有比较运算符的子查询（续）带有比较运算符的子查询（续）例：假设一个学生（无重名）只可能在一个系学习，并例：假设一个学生（无重名）只可能在一个系学习，并且必须属于一个系，则在且必须属于一个系，则在[ [例例39]39]可以可以用用 = = 代替代替ININ ：： SELECT SELECT SnoSno，，SnameSname，，SdeptSdept FROM Student FROM Student WHERE WHERE SdeptSdept = = (SELECT (SELECT SdeptSdept FROM Student FROM Student WHERE WHERE SnameSname= ‘= ‘刘晨刘晨’’) )；； 带有比较运算符的子查询（续）带有比较运算符的子查询（续）［例［例4141］找出每个学生超过他选修课程平均成绩］找出每个学生超过他选修课程平均成绩的课程号。
的课程号 SELECT SELECT SnoSno，， CnoCno FROM SC x FROM SC x WHERE Grade >=(SELECT WHERE Grade >=(SELECT AVG(GradeAVG(Grade) ) FROM SC y FROM SC y WHERE WHERE y.Snoy.Sno= =x.Snox.Sno); );相关子查询相关子查询 带有比较运算符的子查询（续）带有比较运算符的子查询（续）n可能的执行过程：可能的执行过程： 1. 1. 从外层查询中取出从外层查询中取出SCSC的一个元组的一个元组x x，，将元组将元组x x的的SnoSno值值（（200215121200215121）传送给内层查询传送给内层查询 SELECT SELECT AVG(GradeAVG(Grade) ) FROM SC y FROM SC y WHERE WHERE y.Snoy.Sno='200215121';='200215121';2. 2. 执行内层查询，得到值执行内层查询，得到值8888（近似值），用该值代替内层查询，（近似值），用该值代替内层查询，得到外层查询：得到外层查询： SELECT SELECT SnoSno，， CnoCno FROM SC x FROM SC x WHERE Grade >=88 WHERE Grade >=88；； 带有比较运算符的子查询（续）带有比较运算符的子查询（续）3. 3. 执行这个查询，得到执行这个查询，得到 （（200215121200215121，，1 1）） （（200215121200215121，，3 3）） 4.4.外层查询取出下一个元组重复做上述外层查询取出下一个元组重复做上述1 1至至3 3步骤，直步骤，直到外层的到外层的SCSC元组全部处理完毕。
结果为元组全部处理完毕结果为: : （（200215121200215121，，1 1）） （（200215121200215121，，3 3）） （（200215122200215122，，2 2）） 带有比较运算符的子查询（续）带有比较运算符的子查询（续） 子查询一定要跟在比较符之后子查询一定要跟在比较符之后 错误错误的例子：的例子： SELECT SELECT SnoSno，，SnameSname，，SdeptSdept FROM Student FROM Student WHERE ( SELECT WHERE ( SELECT SdeptSdept FROM Student FROM Student WHERE WHERE SnameSname= ‘ = ‘ 刘晨刘晨 ’ ’ ) ) = = SdeptSdept 3.3.3 3.3.3 嵌套查询嵌套查询 一、带有一、带有ININ谓词的子查询谓词的子查询 二、二、 带有比较运算符的子查询带有比较运算符的子查询 三、三、 带有带有ANYANY（（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询 四、四、 带有带有EXISTSEXISTS谓词的子查询谓词的子查询 三、带有三、带有ANYANY（（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询 n< <列值列值> <> <比较运算符比较运算符> [ALL|ANY|SOME] <> [ALL|ANY|SOME] <内部查询内部查询> > ALL ALL ：：列值必须和内部查询结果集的每一个值进行列值必须和内部查询结果集的每一个值进行比较，比较，只有每一次的比较结果都为真只有每一次的比较结果都为真时，比较时，比较结果才结果才为真。
为真 ANYANY、、SOME SOME ：：列值和内部查询结果集的值进行比列值和内部查询结果集的值进行比较，较，只要有一次为真，比较结果就为真只要有一次为真，比较结果就为真 带有带有ANYANY（（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询 （续）（续） [ [例例42] 42] 查询其他系中比计算机科学某查询其他系中比计算机科学某一一学生年龄小的学学生年龄小的学 生姓名和年龄生姓名和年龄 SELECT SELECT SnameSname，，SageSage FROM Student FROM Student WHERE Sage < WHERE Sage < ANYANY (SELECT Sage (SELECT Sage FROM Student FROM Student WHERE WHERE SdeptSdept= ' CS ')= ' CS ') AND AND SdeptSdept <> ‘CS ' <> ‘CS ' 带有带有ANYANY（（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询（续）（续）用聚集函数实现用聚集函数实现[ [例例42] 42] SELECT SELECT SnameSname，，SageSage FROM Student FROM Student WHERE Sage < WHERE Sage < (SELECT (SELECT MAX(Sage)MAX(Sage) FROM Student FROM Student WHERE WHERE SdeptSdept= ‘CS ')= ‘CS ') AND AND SdeptSdept <> ' CS ’; <> ' CS ’; 带有带有ANYANY（（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询 （续）（续）[ [例例43] 43] 查询其他系中比计算机科学系查询其他系中比计算机科学系所有所有学生年龄学生年龄 都小的学生姓名及年龄。
都小的学生姓名及年龄方法一：用方法一：用ALLALL谓词谓词 SELECT SELECT SnameSname，，SageSage FROM Student FROM Student WHERE Sage WHERE Sage < ALL< ALL (SELECT Sage (SELECT Sage FROM Student FROM Student WHERE WHERE SdeptSdept= ' CS ')= ' CS ') AND AND SdeptSdept <> ' CS ’ <> ' CS ’ 带有带有ANYANY（（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询 （续）（续） 方法二：用聚集函数方法二：用聚集函数 SELECT SELECT SnameSname，，SageSage FROM Student FROM Student WHERE Sage < WHERE Sage < ( SELECT ( SELECT MIN(SageMIN(Sage) ) FROM Student FROM Student WHERE WHERE SdeptSdept= ' CS ' )= ' CS ' ) AND AND SdeptSdept <>' CS ' <>' CS ' ALL|ANY比较运算符与比较运算符与allall、、anyany一起使用时的情况一起使用时的情况比比较较运算符运算符修修饰饰符符子子查询结查询结果中取的果中取的值值> >、、>=>=ALLALLANYANY最大最大值值最小最小值值= =ANYANY相当于相当于IN,IN,取所有取所有值值!=!=或或<><>ALLALL相当于相当于NOT IN,NOT IN,取所有取所有值值< <、、<=<=ALLALLANYANY最小最小值值最大最大值值 3.4.3 嵌套查询嵌套查询 一、带有一、带有ININ谓词的子查询谓词的子查询 二、二、 带有比较运算符的子查询带有比较运算符的子查询 三、三、 带有带有ANYANY（（SOMESOME）或）或ALLALL谓词的子查询谓词的子查询 四、四、 带有带有EXISTSEXISTS谓词的子查询谓词的子查询 带有带有EXISTSEXISTS谓词的子查询谓词的子查询( (续）续）n1. EXISTS1. EXISTS谓词谓词n存在量词存在量词 n带有带有EXISTSEXISTS谓词的子查询不返回任何数据，只产生谓词的子查询不返回任何数据，只产生逻辑真值逻辑真值““true”true”或逻辑假值或逻辑假值““false”false”。
Ø若内层查询结果非空，则外层的若内层查询结果非空，则外层的WHEREWHERE子句返回子句返回真值真值Ø若内层查询结果为空，则外层的若内层查询结果为空，则外层的WHEREWHERE子句返回子句返回假值假值n由由EXISTSEXISTS引出的子查询，引出的子查询，其目标列表达式通常都用其目标列表达式通常都用* * n2. NOT EXISTS2. NOT EXISTS谓词谓词Ø若内层查询结果非空，则外层的若内层查询结果非空，则外层的WHEREWHERE子子句返回假值句返回假值Ø若内层查询结果为空，则外层的若内层查询结果为空，则外层的WHEREWHERE子子句返回真值句返回真值 带有带有EXISTS谓词的子查询谓词的子查询(续）续）例：例：[ [例例39]39]查询与查询与““刘晨刘晨””在同一个系学习的学生在同一个系学习的学生 可以用带可以用带EXISTSEXISTS谓词的子查询替换：谓词的子查询替换： SELECT SELECT SnoSno，，SnameSname，，SdeptSdept FROM Student S1 FROM Student S1 WHERE EXISTS WHERE EXISTS ( SELECT *( SELECT * FROM Student S2 FROM Student S2 WHERE S2.Sdept = S1.Sdept AND WHERE S2.Sdept = S1.Sdept AND S2.Sname = ‘ S2.Sname = ‘刘晨刘晨’’) ) 带有带有EXISTS谓词的子查询谓词的子查询(续）续）[ [例例44]44]查询所有选修了查询所有选修了1 1号课程的学生姓名。
号课程的学生姓名思路分析：思路分析：n本查询涉及本查询涉及StudentStudent和和SCSC关系关系n在在StudentStudent中依次取每个元组的中依次取每个元组的SnoSno值，用此值去值，用此值去检查检查SCSC关系关系n若若SCSC中存在这样的元组，其中存在这样的元组，其SnoSno值等于此值等于此Student.SnoStudent.Sno值，并且其值，并且其CnoCno= '1'= '1'，，则取此则取此Student.SnameStudent.Sname送入结果关系送入结果关系 带有带有EXISTS谓词的子查询谓词的子查询(续）续）n用嵌套查询用嵌套查询 SELECT SELECT SnameSname FROM FROM StudentStudent WHERE EXISTS WHERE EXISTS ( SELECT ( SELECT * * FROM SC FROM SC WHERE WHERE SnoSno= =Student.SnoStudent.Sno AND AND CnoCno= ' 1 ‘= ' 1 ‘ ) ) 带有带有EXISTS谓词的子查询谓词的子查询(续）续）n用连接运算用连接运算SELECT SELECT SnameSnameFROM Student, SCFROM Student, SCWHERE WHERE Student.SnoStudent.Sno= =SC.SnoSC.Sno AND AND SC.CnoSC.Cno= '1';= '1'; 带有带有EXISTS谓词的子查询谓词的子查询(续）续）[ [例例45] 45] 查询没有选修查询没有选修1 1号课程的学生姓名。
号课程的学生姓名 SELECT SELECT SnameSname FROM FROM StudentStudent WHERE NOT EXISTS WHERE NOT EXISTS (SELECT * (SELECT * FROM SC FROM SC WHERE WHERE SnoSno = = StudentStudent. .SnoSno AND AND CnoCno='1')='1')；； 带有带有EXISTS谓词的子查询谓词的子查询(续）续）n 不同形式的查询间的替换不同形式的查询间的替换n一些带一些带EXISTSEXISTS或或NOT EXISTSNOT EXISTS谓词的子查询谓词的子查询不能被其他形式不能被其他形式的子查询等价替换的子查询等价替换n所有带所有带ININ谓词、比较运算符、谓词、比较运算符、ANYANY和和ALLALL谓词的子查询谓词的子查询都都能用能用带带EXISTSEXISTS谓词的子查询谓词的子查询等价等价替换替换n 用用EXISTS/NOT EXISTSEXISTS/NOT EXISTS实现全称量词实现全称量词( (难点难点) )SQLSQL语言中没有全称量词语言中没有全称量词 （（For allFor all））可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： ( ( x)P ≡ x)P ≡ ( ( x( x( P)) P)) 带有带有EXISTS谓词的子查询谓词的子查询(续）续）[例例46] 查查询询选选修修了了全全部部课课程程的的学学生生姓姓名名。
选选取取所所有有无无未未选选课课程程的的学学生或者没有一门课程不选修的学生生或者没有一门课程不选修的学生) SELECT SELECT SnameSname FROM Student FROM Student WHERE NOT EXISTS WHERE NOT EXISTS （（ SELECT * FROM CourseSELECT * FROM Course WHERE NOT EXISTS WHERE NOT EXISTS ( ( SELECT * FROM SCSELECT * FROM SC WHERE WHERE SnoSno= = Student.SnoStudent.Sno AND AND CnoCno= = Course.CnoCourse.Cno ）） ））/* Course/* Course中不存在这样的课程中不存在这样的课程* */ //*/*该学生没有选修该学生没有选修* */ / [ [例例46] 46] 查询选修了全部课程的学生姓名。
查询选修了全部课程的学生姓名 Select Select SnameSname from student from student where where SnoSno IN IN ( select ( select SnoSno from SC from SC group by group by SnoSno having count(*) = having count(*) = (select count(*) from course ) (select count(*) from course ) ) ) 带有带有EXISTSEXISTS谓词的子查询谓词的子查询( (续）续） 用用EXISTS/NOT EXISTSEXISTS/NOT EXISTS实现逻辑蕴函实现逻辑蕴函qSQLSQL语言中没有蕴函语言中没有蕴函(Implication)(Implication)逻辑运逻辑运算算q可以利用谓词演算将逻辑蕴函谓词等价转换可以利用谓词演算将逻辑蕴函谓词等价转换为：为： p p q ≡ q ≡ p∨q p∨q 带有带有EXISTSEXISTS谓词的子查询谓词的子查询( (续）续） [例例47]查询至少选修了学生查询至少选修了学生s3选修的全部课程的学生号码选修的全部课程的学生号码解题思路：解题思路：n用逻辑蕴函表达：查询学号为用逻辑蕴函表达：查询学号为x的学生，对所有的课程的学生，对所有的课程y，，只要只要s3学生选修了课程学生选修了课程y，则，则x也选修了也选修了y。
n形式化表示：形式化表示：用用P表示谓词表示谓词 “学生学生s3选修了课程选修了课程y”用用q表示谓词表示谓词 “学生学生x选修了课程选修了课程y”则上述查询为则上述查询为: ( y) p q 带有带有EXISTS谓词的子查询谓词的子查询(续）续）n等价变换：等价变换： ( ( y)y)p p q q ≡ ≡ ( ( y y ( ( ( (p p q q )) )) ≡ ≡ ( ( y (y ( ( ( p∨ q) )) p∨ q) )) ≡ ≡ y(p∧y(p∧ q)q)变变换换后后语语义义：：不不存存在在这这样样的的课课程程y y，，学学生生S3S3选修了选修了y y，，而学生而学生x x没有选 带有带有EXISTSEXISTS谓词的子查询谓词的子查询( (续）续） n用用NOT EXISTSNOT EXISTS谓词表示：谓词表示： SELECT DISTINCT SELECT DISTINCT SnoSno FROM SC FROM SC SCXSCX WHERE NOT EXISTS WHERE NOT EXISTS (SELECT * (SELECT * FROM SC FROM SC SCYSCY WHERE WHERE SCY.SCY.SnoSno = ' s3 ' AND = ' s3 ' AND NOT EXISTS NOT EXISTS ( SELECT * ( SELECT * FROM SC SCZ FROM SC SCZ WHERE WHERE SCZ.SnoSCZ.Sno= =SCXSCX.Sno.Sno AND AND SCZ.CnoSCZ.Cno= =SCYSCY.Cno.Cno)) ))/*/*不存在不存在s3s3学的一门课程学的一门课程* */ //*/*该学生没有学该学生没有学* */ / 3.3 数据查询数据查询 n3.3.1 3.3.1 单表查询单表查询n3.3.2 3.3.2 连接查询连接查询n3.3.3 3.3.3 嵌套查询嵌套查询n3.3.4 3.3.4 集合查询集合查询n3.3.5 Select3.3.5 Select语句的一般形式语句的一般形式 3.4.4 集合查询集合查询n集合操作的种类集合操作的种类q并操作并操作UNIONUNIONq交操作交操作INTERSECTINTERSECTq差操作差操作EXCEPTEXCEPTn参参加加集集合合操操作作的的各各查查询询结结果果的的列列数数必必须须相相同同；；对应项的数据类型也必须相同对应项的数据类型也必须相同 集合查询（续）集合查询（续）[ [例例48] 48] 查询计算机科学系的学生及年龄不大于查询计算机科学系的学生及年龄不大于1919岁的学生。
岁的学生方法一：方法一： SELECT *SELECT * FROM Student FROM Student WHERE WHERE SdeptSdept= 'CS'= 'CS' UNION UNION SELECT * SELECT * FROM Student FROM Student WHERE Sage<=19 WHERE Sage<=19；；nUNIONUNION：：将多个查询结果合并起来时，系统自动去掉重复元组将多个查询结果合并起来时，系统自动去掉重复元组nUNION ALLUNION ALL：：将多个查询结果合并起来时，保留重复元组将多个查询结果合并起来时，保留重复元组 . . 集合查询（续）集合查询（续）[ [例例50] 50] 查询计算机科学系的学生与年龄不大于查询计算机科学系的学生与年龄不大于1919岁的学生的交集岁的学生的交集 SELECT *SELECT *FROM StudentFROM StudentWHERE WHERE SdeptSdept='CS' ='CS' INTERSECTINTERSECTSELECT *SELECT *FROM StudentFROM StudentWHERE Sage<=19 WHERE Sage<=19 集合查询（续）集合查询（续）n[ [例例50] 50] 实际上就是查询计算机科学系中年龄不大实际上就是查询计算机科学系中年龄不大于于1919岁的学生岁的学生 SELECT *SELECT * FROM StudentFROM Student WHERE WHERE SdeptSdept= 'CS' AND = 'CS' AND Sage<=19Sage<=19；； 集合查询（续）集合查询（续）[ [例例52] 52] 查询计算机科学系的学生与年龄不大于查询计算机科学系的学生与年龄不大于1919岁岁的学生的差集。
的学生的差集 SELECT *SELECT * FROM Student FROM Student WHERE WHERE SdeptSdept='CS'='CS' EXCEPT EXCEPT SELECT * SELECT * FROM Student FROM Student WHERE Sage <=19; WHERE Sage <=19; 集合查询（续）集合查询（续） [ [例例52]52]实际上是查询计算机科学系中年龄大于实际上是查询计算机科学系中年龄大于1919岁的学生岁的学生 SELECT *SELECT * FROM Student FROM Student WHERE WHERE SdeptSdept= 'CS' AND Sage>19= 'CS' AND Sage>19；； 重定向输出重定向输出(INTO)(INTO)语法格式：语法格式：INTO INTO new_table new_table[ [例例] ]对部门表对部门表departmentdepartment和员工表和员工表employee,employee,查询出查询出““市场部市场部””所有员工的信息，并将结果存入所有员工的信息，并将结果存入testtest表中。
表中SELECTSELECT e.* e.* INTO INTO test testFROM FROM employee e employee e JOINJOIN department d department dON ON e.department_id = e.department_id = d.dapartment_idd.dapartment_idWHERE WHERE d.department_name = 'd.department_name = '市场部市场部' '重定向输出重定向输出(INTO)(INTO) 3.3 数据查询数据查询 n3.3.1 3.3.1 单表查询单表查询n3.3.2 3.3.2 连接查询连接查询n3.3.3 3.3.3 嵌套查询嵌套查询n3.3.4 3.3.4 集合查询集合查询n3.3.5 Select3.3.5 Select语句的一般形式语句的一般形式 3.3.5 SELECT语句的一般格式语句的一般格式 SELECTSELECT [ALL|DISTINCT] [ALL|DISTINCT] < <目标列表达式目标列表达式> [> [别名别名] [ ] [ ，，< <目标列表达式目标列表达式> [> [别名别名]] …]] … FROM FROM < <表名或视图名表名或视图名> [> [别名别名] ] [ [ ，，< <表名或视图名表名或视图名> [> [别名别名]] …]] … [ [WHEREWHERE < <条件表达式条件表达式>]>] [ [GROUP BYGROUP BY < <列名列名1>1> [ [HAVINGHAVING < <条件表达式条件表达式>]]>]] [ [ORDER BYORDER BY < <列名列名2> [ASC|DESC] 2> [ASC|DESC] 3.4 数据更新数据更新 n3.4.1 3.4.1 数据插入数据插入n3.4.2 3.4.2 数据修改数据修改n3.4.3 3.4.3 数据删除数据删除 3.4.1 3.4.1 数据插入数据插入 (1)(1)使用使用VALUESVALUES子句向表中插入一行：子句向表中插入一行：INSERT INTOINSERT INTO < <表名表名>[(>[(列名列名1[,1[,列名列名2]…)]2]…)]VALUES VALUES ( (常量常量1[,1[,常量常量2]… )2]… )(2)(2)使用子查询向表中插入多行：使用子查询向表中插入多行：INSERT INTOINSERT INTO < <表名表名>[(>[(列名列名1[,1[,列名列名2]…)]2]…)]子查询子查询 表中数据的维护表中数据的维护例例 使用简单的使用简单的INSERTINSERT语句。
语句USE SalesUSE SalesGOGOINSERTINSERT Supplier SupplierVALUESVALUES (‘S001’,‘ (‘S001’,‘华科电子有限公司华科电子有限公司’’,‘,‘施宾彬施宾彬 ',' ','朝阳路朝阳路5656号号','2636565')','2636565') 例例 显式指定列列表显式指定列列表INSERTINSERT Sales.dbo.SupplierSales.dbo.Supplier (supplier_id,supplier_name, (supplier_id,supplier_name, linkman_name, address)linkman_name, address)VALUESVALUES ('S001 ',' ('S001 ','华科电子有限公司华科电子有限公司',' ','施宾彬施宾彬 ',' ','朝阳路朝阳路5656号号') ') 3.4.1 数据插入数据插入例例3.40 3.40 对每一个系，查询学生的平均年龄，并把结对每一个系，查询学生的平均年龄，并把结果存入数据库。
果存入数据库 CREATE TABLECREATE TABLE CLASSAGE(st_depidCLASSAGE(st_depid CHAR(8) NOT NULLCHAR(8) NOT NULL，，avgageavgage INT); INT); INSERT INTOINSERT INTO CLASSAGECLASSAGE（（st_depid,avgagest_depid,avgage）） SELECT SELECT st_depidst_depid, , AVG(st_ageAVG(st_age) FROM ) FROM student GROUP BY student GROUP BY st_depidst_depid 3.4.2 数据修改数据修改 一般格式为：一般格式为： UPDATEUPDATE < <表名表名> > SET SET < <列名列名1>1>＝＝< <表达式表达式1>1> [,< [,<列名列名2>2>＝＝< <表达式表达式2>]…2>]… [ [WHEREWHERE < <条件表达式条件表达式>]>]；； 例例 使用简单使用简单的的UPDATEUPDATE语句。
语句 UPDATEUPDATE Customer2Customer2 SET SET linkman_name=' linkman_name='佚名佚名', ', address=NULLaddress=NULL, , telephone=‘’telephone=‘’例例 在在UPDATEUPDATE语句中使用语句中使用WHEREWHERE子句 UPDATEUPDATE Customer2 Customer2 SET SET telephone='0731-'+telephone telephone='0731-'+telephone WHEREWHERE LENLEN(telephone)=7(telephone)=73.4.2 数据修改数据修改 3.4.2 数据修改数据修改使用子查询更新表中数据使用子查询更新表中数据例例 3.43 3.43 将低于平均成绩的课程号为将低于平均成绩的课程号为““015”015”的的课程的成绩提高课程的成绩提高10%10%UPDATEUPDATE score score SETSET st_scorest_score = = st_scorest_score *1.1 *1.1 WHERE WHERE sub_nosub_no=‘015’AND =‘015’AND st_scorest_score < < ( (SELECT SELECT avg(st_scoreavg(st_score) FROM ) FROM score WHERE score WHERE sub_nosub_no=‘015’=‘015’) ) 3.4.3 数据删除数据删除 一般格式为：一般格式为： DELETE FROMDELETE FROM < <表名表名> > [ [WHEREWHERE < <条件表达式条件表达式>]>]nDELETEDELETE语句不能删除表定义本身，表的定义仍在语句不能删除表定义本身，表的定义仍在数据字典中。
数据字典中nDELETEDELETE语句不能删除单个字段的值，只能删除整语句不能删除单个字段的值，只能删除整行数据要删除单个字段的值，可以采用行数据要删除单个字段的值，可以采用UPDATEUPDATE语句，将其修改为语句，将其修改为NULLNULL 3.4.3 数据删除数据删除1.1.单记录删除单记录删除例例3.44 3.44 删除学号为删除学号为““070322”070322”的学生信息的学生信息DELETE FROMDELETE FROM student student WHERE WHERE st_nost_no= ‘070322’= ‘070322’2.2.多记录删除多记录删除例例3.45 3.45 删除所有课程信息记录删除所有课程信息记录DELETE FROMDELETE FROM subject subject如果该语句执行成功，如果该语句执行成功，subjectsubject表将成为一个空表表将成为一个空表 提提 问问n1 1、、anyany和和allall谓词有何区别？谓词有何区别？n2 2、如何从表中插入、修改、删除数据？、如何从表中插入、修改、删除数据？ 3.5 视图视图 n3.5.1 3.5.1 视图的创建与撤消视图的创建与撤消n3.5.2 3.5.2 视图的更新视图的更新n3.5.3 3.5.3 视图的查询视图的查询 3.5 3.5 视视 图图n定义：虚表，是从一个或几个基本表（或视图）定义：虚表，是从一个或几个基本表（或视图）导出的表导出的表n只存放视图的定义，只存放视图的定义，不存放视图对应的数据不存放视图对应的数据n基表中的数据发生变化，从视图中查询出的数基表中的数据发生变化，从视图中查询出的数据也随之改变据也随之改变 一、建立视图一、建立视图n语句格式语句格式 CREATE VIEWCREATE VIEW < <视图名视图名> [(<> [(<列名列名> [> [，，< <列名列名>]…)]>]…)] ASAS < <子查询子查询> > [ [WITH CHECK OPTIONWITH CHECK OPTION] ]；；n组成视图的属性列名：全部省略或全部指定组成视图的属性列名：全部省略或全部指定n子查询不允许含有子查询不允许含有ORDER BYORDER BY子句、子句、DISTINCTDISTINCT 短语短语强制所有通过视图修改强制所有通过视图修改的数据满足的数据满足selectselect语句语句中指定的选择条件。
中指定的选择条件 建立视图（续）建立视图（续）nRDBMSRDBMS执行执行CREATE VIEWCREATE VIEW语句时只是语句时只是把把视图定义存入数据字典视图定义存入数据字典，，并不执行其中并不执行其中的的SELECTSELECT语句语句n在对视图查询时，按视图的定义从基本表在对视图查询时，按视图的定义从基本表中将数据查出中将数据查出 建立视图（续）建立视图（续） [ [例例1] 1] 建立信息系学生的视图建立信息系学生的视图 CREATE VIEWCREATE VIEW IS_StudentIS_Student AS AS SELECT SELECT SnoSno，，SnameSname，，SageSage FROM Student FROM Student WHERE WHERE SdeptSdept= 'IS'= 'IS'；； 建立视图（续）建立视图（续）[ [例例2]2]建立信息系学生的视图，并要求进行修改和建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生插入操作时仍需保证该视图只有信息系的学生 。
CREATE VIEWCREATE VIEW IS_Student IS_Student AS AS SELECT SELECT SnoSno，，SnameSname，，SageSage FROM Student FROM Student WHERE WHERE SdeptSdept= 'IS'= 'IS' WITH CHECK OPTIONWITH CHECK OPTION；； 建立视图（续）建立视图（续）对对IS_StudentIS_Student视图的更新操作：视图的更新操作：n修改操作：自动加上修改操作：自动加上SdeptSdept= 'IS'= 'IS'的条件的条件n删除操作：自动加上删除操作：自动加上SdeptSdept= 'IS'= 'IS'的条件的条件n插入操作：自动检查插入操作：自动检查SdeptSdept属性值是否为属性值是否为'IS' 'IS' q如果不是，则拒绝该插入操作如果不是，则拒绝该插入操作q如果没有提供如果没有提供SdeptSdept属性值，则自动定义属性值，则自动定义SdeptSdept为为'IS''IS' 建立视图（续）建立视图（续）n基于多个基表的视图基于多个基表的视图[ [例例3] 3] 建立信息系选修了建立信息系选修了1 1号课程的学生视图。
号课程的学生视图 CREATE VIEWCREATE VIEW IS_S1 IS_S1(Sno(Sno，，SnameSname，，Grade)Grade) AS AS SELECT SELECT Student.SnoStudent.Sno，，SnameSname，，GradeGrade FROM Student FROM Student，，SCSC WHERE WHERE SdeptSdept= 'IS' AND= 'IS' AND Student.SnoStudent.Sno= =SC.SnoSC.Sno AND AND SC.CnoSC.Cno= '1'= '1'；； 建立视图（续）建立视图（续）n基于视图的视图基于视图的视图n[ [例例4] 4] 建立信息系选修了建立信息系选修了1 1号课程且成绩在号课程且成绩在9090分以上的学生的视图。
分以上的学生的视图 CREATE VIEWCREATE VIEW IS_S2 IS_S2 AS AS SELECT SELECT SnoSno，，SnameSname，，GradeGrade FROM FROM IS_S1IS_S1 WHERE Grade>=90 WHERE Grade>=90；； 建立视图（续）建立视图（续）n带表达式的视图带表达式的视图n[ [例例5] 5] 定义一个反映学生出生年份的视图定义一个反映学生出生年份的视图 CREATE VIEW CREATE VIEW BT_S(SnoBT_S(Sno，，SnameSname，，SbirthSbirth) ) AS AS SELECT SELECT SnoSno，，SnameSname，，2000-Sage2000-Sage FROM Student FROM Student；；派生属性列也称为派生属性列也称为虚拟列虚拟列----SbirthSbirth 带表达式的视图必须带表达式的视图必须明确定义明确定义组成视图的各个组成视图的各个属性列名属性列名 建立视图（续）建立视图（续）n分组视图分组视图[ [例例6] 6] 将学生的学号及他的平均成绩定义为一个将学生的学号及他的平均成绩定义为一个 视图视图 CREAT VIEW CREAT VIEW S_G(SnoS_G(Sno，，GavgGavg) ) AS AS SELECT SELECT SnoSno，，AVG(Grade)AVG(Grade) FROM SC FROM SC GROUP BY GROUP BY SnoSno；； 建立视图（续）建立视图（续）n不指定属性列不指定属性列[ [例例7]7]将将StudentStudent表中所有女生记录定义为一个视图表中所有女生记录定义为一个视图 CREATE CREATE VIEWVIEW F_Student(F_SnoF_Student(F_Sno，， namename，， sexsex，，ageage，，dept)dept) AS AS SELECT *SELECT * FROM Student FROM Student WHERE WHERE SsexSsex=‘=‘女女’’ 二、删除视图二、删除视图n语句的格式：语句的格式：DROP VIEW [ > [ CASCADECASCADE ] ]；；q该语句从数据字典中删除指定的视图定义该语句从数据字典中删除指定的视图定义q如果该视图上还导出了其他视图，使用如果该视图上还导出了其他视图，使用CASCADECASCADE级联删除语句，把该视图和由它导级联删除语句，把该视图和由它导出的所有视图一起删除出的所有视图一起删除 q删除基表时，由该基表导出的所有视图定义都删除基表时，由该基表导出的所有视图定义都必须显式地使用必须显式地使用DROP VIEWDROP VIEW语句删除语句删除 删除视图删除视图(续）续）［例［例8 8］］ 删除视图删除视图BT_SBT_S：： DROP VIEW BT_SDROP VIEW BT_S；； 删除视图删除视图IS_S1IS_S1：：DROP VIEW IS_S1DROP VIEW IS_S1；；拒绝执行拒绝执行级级联删除：联删除：DROP VIEW IS_S1 CASCADE; DROP VIEW IS_S1 CASCADE; 3.6.2 查询视图查询视图n用户角度：查询视图与查询基本表相同用户角度：查询视图与查询基本表相同nRDBMSRDBMS实现视图查询的方法实现视图查询的方法q视图消解法（视图消解法（View ResolutionView Resolution））n进行有效性检查进行有效性检查n转换成等价的对基本表的查询转换成等价的对基本表的查询n执行执行修正修正后的查询后的查询 查询视图（续）查询视图（续）[ [例例9] 9] 在信息系学生的视图中找出年龄小于在信息系学生的视图中找出年龄小于2020岁的学生。
岁的学生 SELECT SELECT SnoSno，，SageSage FROM IS_Student FROM IS_Student WHERE Sage<20 WHERE Sage<20；；IS_StudentIS_Student视图的定义视图的定义 ( (参见视图定义例参见视图定义例1)1) 查询视图（续查询视图（续））视图消解转换后的查询语句为：视图消解转换后的查询语句为： SELECT SELECT SnoSno，，Sage Sage FROM Student FROM Student WHERE WHERE SdeptSdept= 'IS' AND Sage<20= 'IS' AND Sage<20；； 查询视图（续）查询视图（续）[ [例例10] 10] 查询选修了查询选修了1 1号课程的信息系学生号课程的信息系学生SELECT SELECT IS_Student.SnoIS_Student.Sno，，SnameSnameFROM FROM IS_StudentIS_Student，，SCSCWHERE WHERE IS_Student.SnoIS_Student.Sno = =SC.SnoSC.Sno AND AND SC.CnoSC.Cno= '1'= '1'；； 查询视图（续）查询视图（续）n视图消解法的局限视图消解法的局限q有些情况下，视图消解法不能生成有些情况下，视图消解法不能生成正确查询。
正确查询 查询视图（续）查询视图（续）[ [例例11]11]在在S_GS_G视图中查询平均成绩在视图中查询平均成绩在9090分以上的学号和平分以上的学号和平均成绩均成绩SELECT * FROM SELECT * FROM S_GS_GWHERE WHERE GavgGavg>=90>=90；；S_GS_G视图的子查询定义：视图的子查询定义： CREATE VIEWCREATE VIEW S_G ( S_G (SnoSno，，GavgGavg) ) AS AS SELECT SELECT SnoSno，，AVG(Grade)AVG(Grade)FROM SCFROM SCGROUP BY GROUP BY SnoSno；； 查询转换查询转换 错误：错误：SELECT SELECT SnoSno，，AVG(Grade)AVG(Grade)FROM SCFROM SCWHERE WHERE AVG(Grade)>=90AVG(Grade)>=90GROUP BY GROUP BY SnoSno；；正确：正确：SELECT SELECT SnoSno，，AVG(Grade)AVG(Grade)FROM SCFROM SCGROUP BY GROUP BY SnoSnoHAVING AVG(Grade)>=90HAVING AVG(Grade)>=90；； 更新视图（续）更新视图（续）[ [例例12] 12] 将信息系学生视图将信息系学生视图IS_StudentIS_Student中学号中学号 200215122200215122的学生姓名改为的学生姓名改为““刘辰刘辰””。
UPDATE UPDATE IS_StudentIS_StudentSET SET SnameSname= '= '刘辰刘辰' 'WHERE WHERE SnoSno= ' 200215122 '= ' 200215122 '；；转换后的语句：转换后的语句：UPDATE UPDATE StudentStudentSET SET SnameSname= '= '刘辰刘辰' 'WHERE WHERE SnoSno= ' 200215122 ' AND = ' 200215122 ' AND SdeptSdept= 'IS'= 'IS' 更新视图（续）更新视图（续）[ [例例13] 13] 向信息系学生视图向信息系学生视图IS_SIS_S中插入一个新的学中插入一个新的学生记录：生记录：200215129200215129，赵新，，赵新，2020岁岁INSERTINSERTINTO INTO IS_StudentIS_StudentVALUES(‘95029’VALUES(‘95029’，，‘‘赵新赵新’’，，20)20)；；转换为对基本表的更新：转换为对基本表的更新：INSERTINSERTINTO INTO StudentStudent(Sno(Sno，，SnameSname，，SageSage，，SdeptSdept) )VALUES(‘200215129 'VALUES(‘200215129 '，，' '赵新赵新' '，，2020，，'IS''IS' ) )；； 更新视图（续）更新视图（续）[ [例例14]14]删除信息系学生视图删除信息系学生视图IS_StudentIS_Student中学号中学号为为 200215129200215129的记录的记录 DELETE FROM DELETE FROM IS_StudentIS_StudentWHERE WHERE SnoSno= ' 200215129 '= ' 200215129 '；；转换为对基本表的更新：转换为对基本表的更新：DELETE FROM DELETE FROM StudentStudentWHERE WHERE SnoSno= ' 200215129 ' AND = ' 200215129 ' AND SdeptSdept= 'IS'= 'IS'；； 更新视图（续）更新视图（续）更新视图的限制：一些视图是不可更新的更新视图的限制：一些视图是不可更新的（（P72P72））例：视图例：视图S_GS_G为不可更新视图。
为不可更新视图UPDATE S_GUPDATE S_GSET SET GavgGavg=90=90WHERE WHERE SnoSno= =‘‘200215121200215121’’ 视图的更新无法转换成对基本表视图的更新无法转换成对基本表SCSC的更新的更新 更新视图（续）更新视图（续）n允许对允许对行列子集视图行列子集视图进行更新进行更新从单个基本表中从单个基本表中导出导出, ,只是去掉了只是去掉了某些行或列某些行或列, ,保留保留了主码的视图了主码的视图v对其他类型视图的更新不同系统有不同限制对其他类型视图的更新不同系统有不同限制 3.5.4 3.5.4 视图的作用视图的作用n1. 1. 视图能够简化用户的操作视图能够简化用户的操作n2. 2. 视图使用户能以多种角度看待同一数据视图使用户能以多种角度看待同一数据 n3. 3. 视图对重构数据库提供了一定程度的逻辑独视图对重构数据库提供了一定程度的逻辑独 立性立性 n4. 4. 视图能够对机密数据提供安全保护视图能够对机密数据提供安全保护n5. 5. 适当的利用视图可以更清晰的表达查询适当的利用视图可以更清晰的表达查询 3.6 查询优化查询优化 n3.6.1 3.6.1 查询优化的必要性查询优化的必要性n3.6.2 3.6.2 查询优化的一般准则查询优化的一般准则n3.6.3 3.6.3 优化算法优化算法n3.6.4 3.6.4 优化的一般步骤优化的一般步骤 3.6.1 查询优化的必要性查询优化的必要性例例3.53 3.53 查询选修了查询选修了““011”011”号课程的学生姓名。
号课程的学生姓名SELECTSELECT student.st_namestudent.st_name FROM FROM student,scorestudent,scoreWHEREWHERE student.st_nostudent.st_no= =score.st_noscore.st_no AND AND score.sub_noscore.sub_no=‘011’=‘011’该查询可用以下该查询可用以下3 3种等价的关系代数表达式来完成：种等价的关系代数表达式来完成：nπ πst_namest_name(σ(σ student. student. st_nost_no = score. = score. st_nost_no ∧ score. ∧ score. sub_nosub_no = ‘011’= ‘011’ ( (student×scorestudent×score)) ))nπst_name(σπst_name(σ score. score. sub_nosub_no = ‘011’ = ‘011’ (student (student ∞ score)) score))nπst_name(studentπst_name(student ∞ σ σ score. score. sub_nosub_no = ‘011’ = ‘011’ (score)) (score)) 3.6.2 查询优化的一般准则查询优化的一般准则 (1) (1) 选择运算应尽早执行。
选择运算应尽早执行2) (2) 把投影运算和选择运算同时进行把投影运算和选择运算同时进行 (3) (3) 把投影操作与它前面或后面的一个双把投影操作与它前面或后面的一个双目运算结合起来目运算结合起来 3.6.2 查询优化的一般准则查询优化的一般准则(4) (4) 在执行连接运算之前，可对需要连接的在执行连接运算之前，可对需要连接的关系进行适当地预处理，如建索引或排序关系进行适当地预处理，如建索引或排序5) (5) 把笛卡尔乘积和其后的选择运算合并成把笛卡尔乘积和其后的选择运算合并成为连接运算，以避免扫描笛卡尔乘积的中为连接运算，以避免扫描笛卡尔乘积的中间结果例：例：б бStudent.SnoStudent.Sno= =SC.SnoSC.Sno ( (StudentStudent××SCSC) ) Student ∞SC Student ∞SC(6) (6) 存储公用子表达式存储公用子表达式 3.6.3 3.6.3 优化算法优化算法( (代数优化代数优化) )(1) (1) 连接、笛卡尔积交换律连接、笛卡尔积交换律nE1 E2≡E2 E1 E1 E2≡E2 E1 nE1 E2≡E2 E1 E1 E2≡E2 E1 nE1×E2≡E2×E1E1×E2≡E2×E1(2) (2) 连接、笛卡尔积的结合律连接、笛卡尔积的结合律n(E1 E2) E3≡E1 (E2 E3)(E1 E2) E3≡E1 (E2 E3)n(E1 E2) E3≡E1 (E2 E3) (E1 E2) E3≡E1 (E2 E3) n(E1×E2) × E3 ≡ E1 × (E2×E3)(E1×E2) × E3 ≡ E1 × (E2×E3)FFF1F1F2F2 3.6.3 3.6.3 优化算法优化算法(3) (3) 投影的串接投影的串接nΠ Π A1,A2, ... ,AnA1,A2, ... ,An (π (π B1,B2, ... ,BmB1,B2, ... ,Bm (E))≡π (E))≡π A1,A2, ... ,AnA1,A2, ... ,An (E) (E)(4) (4) 选择的串接选择的串接nб бF1F1（（б бF2F2（（E E））））≡≡б бF1∧F2F1∧F2 (E) (E)n选择的串接律说明选择条件可以合并，这样一次就选择的串接律说明选择条件可以合并，这样一次就可检查全部条件。
可检查全部条件 3.6.3 优化算法(5) (5) 选择和投影的交换选择和投影的交换①①假设：选择条件假设：选择条件F F只涉及属性只涉及属性A1A1，，……，，AnAnnб бF F (π (π A1,A2, ... ,AnA1,A2, ... ,An (E))≡π (E))≡π A1,A2, ... ,AnA1,A2, ... ,An (б (бF F (E)) (E))②②假设：假设：F F中有不属于中有不属于A1A1，，……，，AnAn的属性的属性B B1 1,…,,…,B BmmnΠ Π A1,A2, ... ,AnA1,A2, ... ,An (б (бF F (E))≡π (E))≡π A1,A2, ... ,AnA1,A2, ... ,An (б (бF F (π (π A1,A2, ... ,An, B1,B2, ... ,BmA1,A2, ... ,An, B1,B2, ... ,Bm (E))) (E))) 3.6.3 优化算法(6) (6) 选择对笛卡尔积的分配律选择对笛卡尔积的分配律①①假设：假设：F F中涉及的属性都是中涉及的属性都是E1E1中的属性中的属性n б бF F (E1×E2)≡б (E1×E2)≡бF F (E1)×E2 (E1)×E2 ②②假设：假设：F=F1∧F2F=F1∧F2，并且，并且F1F1只涉及只涉及E1E1中的属性，中的属性，F2F2只涉只涉及及E2E2中的属性，则由上面的等价变换规则中的属性，则由上面的等价变换规则1 1，，4 4，，6 6可推出：可推出：n б бF F (E1×E2) ≡б (E1×E2) ≡бF1F1(E1)×б(E1)×бF2F2 (E2) (E2) ③③假设：假设： F=F1∧F2F=F1∧F2，，并且并且 F1F1只涉及只涉及E1E1中的属性，中的属性，F2F2只只涉涉及及E1E1和和E2E2两者的属性，则可得到下列式子：两者的属性，则可得到下列式子：n б бF F (E1×E2)≡б (E1×E2)≡бF2F2(б(бF1F1(E1)×E2)(E1)×E2) 3.6.3 优化算法(7) (7) 选择对并的分配律选择对并的分配律假设假设E=E1∪E2E=E1∪E2，，E1E1，，E2E2有相同的属性名，则：有相同的属性名，则：n б бF F(E1∪E2)≡б(E1∪E2)≡бF F (E1)∪б(E1)∪бF F (E2) (E2)(8) (8) 选择对集合差运算的分配律选择对集合差运算的分配律假设假设E1E1与与E2E2有相同的属性名，则：有相同的属性名，则：n б бF F (E1 (E1－－E2)≡бE2)≡бF F (E1) (E1)－－б бF F (E2) (E2) 3.6.3 优化算法(9) (9) 选择对自然连接的分配律选择对自然连接的分配律nб бF F (E1 E2)≡б (E1 E2)≡бF F (E1) б(E1) бF F (E2) (E2)(10) (10) 投影对笛卡尔积的分配投影对笛卡尔积的分配假设假设E1E1和和E2E2是两个关系表达式，是两个关系表达式，A1A1，，……，，AnAn是是E1E1的的属性，属性，B1B1，，……，，BmBm是是E2E2的属性。
则：的属性则：nΠ Π A1,A2, …,An,B1,B2, …,BmA1,A2, …,An,B1,B2, …,Bm（（E1×E2)≡E1×E2)≡ Π Π A1,A2, …,AnA1,A2, …,An（（E1)×Π E1)×Π B1,B2, …,BmB1,B2, …,Bm (E2) (E2) 3.6.3 3.6.3 优化算法优化算法(11) (11) 投影对并的分配律投影对并的分配律假设假设E1E1和和E2E2有相同的属性名则：有相同的属性名则： π π A1,A2, …,AnA1,A2, …,An (E1∪E2)≡π (E1∪E2)≡π A1,A2, …,AnA1,A2, …,An (E1)∪π (E1)∪π A1,A2, …,AnA1,A2, …,An (E2) (E2) 小结小结n1-2: 1-2: 连接、笛卡尔积的交换律、结合律连接、笛卡尔积的交换律、结合律n3 3：： 合并或分解投影运算合并或分解投影运算n4 4：： 合并或分解选择运算合并或分解选择运算n5-95-9：： 选择运算与其他运算交换选择运算与其他运算交换n5 5，，1010，，1111：： 投影运算与其他运算交换投影运算与其他运算交换 3.6.3 3.6.3 优化算法优化算法关系表达式的优化算法如下：关系表达式的优化算法如下：n输入：一个代数表达式的语法树。
输入：一个代数表达式的语法树n输出：计算该表达式的一个优化序列输出：计算该表达式的一个优化序列 3.6.3 3.6.3 优化算法优化算法方法：依次执行以下步骤方法：依次执行以下步骤1)(1)分解选择运算分解选择运算: :使用规则使用规则(4)(4)把形如把形如б б F1∧F2…∧FnF1∧F2…∧Fn (E) (E)的子的子表达式转换成表达式转换成б бF1F1 (б (бF2F2(…(б(…(бFnFn(E))…)) (E))…)) (2)(2)通过交换选择运算，将其尽可能移到叶端通过交换选择运算，将其尽可能移到叶端: :对对每个选择操作，使用规则每个选择操作，使用规则(4)~ (9)(4)~ (9)尽可能把选择操作移到树尽可能把选择操作移到树的叶端3)(3)通过交换投影运算，将其尽可能移到叶端通过交换投影运算，将其尽可能移到叶端: :对对每个投影操作，使用规则每个投影操作，使用规则(3)(3)、、(10)(10)、、(11)(11)和和(5)(5)中的一般形中的一般形式尽可能把投影操作移向叶端式尽可能把投影操作移向叶端 (4)(4)合并串接的选择和投影，以便能同时执行合并串接的选择和投影，以便能同时执行或在一次扫描中完成：或在一次扫描中完成：使用规则使用规则(3) ~(5)(3) ~(5)把把选择和投影的串接合并成单个选择、单个选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影，使多个选投影或一个选择后跟一个投影，使多个选择或投影能同时执行，或在一次扫描中全择或投影能同时执行，或在一次扫描中全部完成。
部完成 3.6.3 3.6.3 优化算法优化算法(5)(5)将内结点分组将内结点分组n每个二元运算（每个二元运算（×、 、∪、－）结点与其直接祖先的一）结点与其直接祖先的一元运算结点（元运算结点（б或或π）分为一组分为一组n如果其后代结点直到叶子全是一元运算，则也并入该组如果其后代结点直到叶子全是一元运算，则也并入该组n当二元运算是笛卡尔积，而且其后的选择不能与它结合成当二元运算是笛卡尔积，而且其后的选择不能与它结合成等值连接时，则不能将选择与这个二元运算组成同一组等值连接时，则不能将选择与这个二元运算组成同一组6)(6)生成一个程序，每组结点的计算是程序中的一步，各步的生成一个程序，每组结点的计算是程序中的一步，各步的顺序是任意的，只要保证任何一组不会在它的后代组之前顺序是任意的，只要保证任何一组不会在它的后代组之前计算 3.6.4 3.6.4 优化的一般步骤优化的一般步骤1 1．把查询转换成某种内部表示．把查询转换成某种内部表示 2 2．代数优化：把语法树转换成标准（优化．代数优化：把语法树转换成标准（优化) ) 形式形式 3 3．物理优化：选择低层的存取路径．物理优化：选择低层的存取路径 4 4．生成查询计划．生成查询计划 3.6.4 3.6.4 优化的一般步骤优化的一般步骤 （（1)1)将查询转换成某种内部表示，通常是语法树。
将查询转换成某种内部表示，通常是语法树project(st_nameproject(st_name) )select(scoreselect(score. . sub_nosub_no = = 011011 ) )join(studentjoin(student. . st_nost_no = score. = score. st_nost_no) )studentstudent图图3-33-3把把SQLSQL语句转换成语法树语句转换成语法树scorescore结果结果π πst_namest_name σ σscorescore. . sub_nosub_no = = 011011 σ σstudentstudent. . st_nost_no = score. = score. st_nost_no××studenstudent t图图3-43-4关系代数语法树关系代数语法树scorescore 3.6.4 优化的一般步骤优化的一般步骤(2)(2)根据一定的等价变换规则把语法树转换成标准根据一定的等价变换规则把语法树转换成标准（优化（优化) )形式形式n将图将图3-4 3-4 语法树优化成图语法树优化成图3-53-5。
π πst_namest_name σ σscorescore. . sub_nosub_no = = 011011 σ σstudentstudent. . st_nost_no = score. = score. st_nost_no××studentstudent图图3-53-5优化后的语法树优化后的语法树scorescore 3.6.4 优化的一般步骤优化的一般步骤(3) (3) 选择底层的操作算法选择底层的操作算法 优化器查找数据字典获得当前数据库状态信息优化器查找数据字典获得当前数据库状态信息 q选择字段上是否有索引选择字段上是否有索引 q连接的两个表是否有序连接的两个表是否有序q连接字段上是否有索引连接字段上是否有索引 然后根据一定的优化规则选择存取路径然后根据一定的优化规则选择存取路径 3.6.4 优化的一般步骤优化的一般步骤(4) (4) 生成查询计划生成查询计划 查询计划也称查询执行方案，是由一查询计划也称查询执行方案，是由一系列内部操作组成的这些内部操作按一系列内部操作组成的这些内部操作按一定的次序构成查询的一个执行方案。
定的次序构成查询的一个执行方案 举例举例【【例例】】供应商数据库中有：供应商、零供应商数据库中有：供应商、零件、项目、供应四个基本表（关系）：件、项目、供应四个基本表（关系）：S(SnoS(Sno，，SnameSname，，StatusStatus，，City)City)P(PnoP(Pno，，PnamePname，，ColorColor，，Weight)Weight)J(JnoJ(Jno，，JnameJname，，City)City)SPJ(SnoSPJ(Sno，，PnoPno，，JnoJno，，Qty)Qty) 举例举例n用户有一查询语句：检索使用上海供应商生产的用户有一查询语句：检索使用上海供应商生产的红色零件的工程号红色零件的工程号1) (1) 试写出该查询的关系代数表达式；试写出该查询的关系代数表达式；(2) (2) 试写出查询优化的关系代数表达式；试写出查询优化的关系代数表达式；(3) (3) 画出该查询初始的关系代数表达式的语法树；画出该查询初始的关系代数表达式的语法树；(4) (4) 使用优化算法，对语法树进行优化，并画出优使用优化算法，对语法树进行优化，并画出优化后的语法树。
化后的语法树 举例举例解解: :n(1)(1)该查询的关系代数表达式如下该查询的关系代数表达式如下: :n(2)(2)查询优化的关系代数表达式如下查询优化的关系代数表达式如下: :n(3)(3)该查询初始的关系代数表达式的语法树如图所示该查询初始的关系代数表达式的语法树如图所示n(4)(4)优化后的语法树如图所示优化后的语法树如图所示)((''''PSPJScolorcityJno∞∞红上海=Ù=sp)))(()())((('',,''PSPJSColorPnoJnoPnoSnocitySnoJno红红上海上海= == =s sp pp ps sp pp p∞∞ 图图2-21 2-21 优化前优化前图图2-22 2-22 优化后优化后 。