《数据库原理与应用》课件第4章

第4章关系数据库的结构化查询语言4.1SQL概述4.2SQL数据定义4.3SQL数据查询4.4SQL数据操纵4.5SQL的视图、存储过程和触发器4.6SQL数据控制4.7T-SQL编程4.8嵌入式SQL

小结

SQL(StructuredQueryLanguage，结构化查询语言)是关系数据库的标准语言，其功能并非仅仅是查询，它集数据定义、数据查询、数据操纵、数据控制等功能于一体，现已成为关系数据库的国际标准语言。

学习内容：

(1)

SQL语言的发展及特点。

(2)数据定义。

(3)数据查询。

(4)数据操纵。

(5)数据控制。

4.1.1SQL及其标准

SQL最初是在1974年由Boyce和Chambertin提出来的。1975～1979年，IBM公司研制的SystemR实现了这种语言。1979年，ORACLE公司首先提供商用的SQL，IBM公司在DB2和SQL/DS数据库系统中也实现了SQL。4.1SQL概述1986年10月，美国国家标准局(ANSI)的数据库委员会将其作为关系数据库语言的美国标准(SQL-86)，1987年6月，国际标准化组织(ISO)通过了这个标准，并于1989年公布为SQL-89。此后，SQL标准不断完善，先后推出了SQL-92、SQL-99、SQL-2003等标准。4.1.2SQL的特点

SQL之所以受到普遍的公认和欢迎，主要在于它具有以下特点。

1．功能一体化

SQL提供了一系列完整的数据定义和操纵功能，用SQL可以实现数据库生命周期中的全部活动，包括建立数据库、视图、索引，进行查询、更新、维护等操作，控制对数据库和数据库对象的存取，保证数据库安全性控制、数据库一致性和完整性等一系列操作要求。

SQL语句可以大体分为以下三类：

(1)数据定义语言(DataDefinitionLanguage，简称DDL)：用于定义数据库、基本表、视图、索引等结构。

(2)数据操作语言(DataManipulationLanguage，简称DML)：数据操作分成数据查询和数据更新两类，而数据更新又分成插入、删除和修改三种操作。查询语句SELECT是SQL中使用频率最高的语句。

(3)数据控制语言(DataControlLanguage，简称DCL)：包括事务管理功能和数据保护功能，即数据库的恢复、并发控制以及数据库的安全性、完整性控制。

2．语言非过程化

SQL是一种非过程化语言，SQL进行数据操作时，只要提出“做什么”，而无需指明“怎么做”，因此无需了解存取路径。存取路径的选择以及SQL语句的操作过程由系统自动完成。

3．面向集合的操作方式

SQL是一种非过程化语言，它采用集合操作方式，无论是操作对象还是查询更新的结果均是元组的集合。

4．语言简洁，易学易用

SQL是接近英语的自然语言，设计巧妙，完成核心功能的语句只用了9个动词，因此容易学习和使用。

5．SQL既是独立语言(自含式语言)，又是嵌入式语言

作为独立语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作；作为嵌入式语言，SQL语句能够嵌入到高级语言(例如C、COBOL、FORTRAN、PL/1)程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL的语法结构基本上是一致的。4.1.3SQL的基本概念

1．基本表

基本表是独立存在的表，一个关系对应一个基本表，一个或多个基本表对应一个存储文件。

2．视图

视图是从若干个基本表和(或)视图导出的表，它与基本表不同，其本身并不保存数据，数据仍存放在基本表中。在创建视图时，数据库中只存放视图的定义；在使用视图时，才去求对应数据，所以称视图为“虚表”。

3．索引

索引依赖于表的建立，它提供了数据库中编排表中数据的内部方法。一个表的存储由两部分组成：一部分存放表的数据页面；另一部分存放表的索引页面。这样，在给定某列值查询对应数据时，就不必逐个扫描表中的每一行，可通过索引页查找到该列值对应的地址指针，从而在数据页中直接查找，加快了查询速度。没有建立索引的表访问数据时必须逐个扫描。

SQL支持关系数据库的三级模式结构，如图4.1所示。外模式对应的是视图和部分基本表，模式对应于基本表，内模式对应于存储文件。

图4.1SQL的体系结构需要说明的是：

(1)一个基本表可以存储在一个或多个存储文件中，一个存储文件也可存储一个或多个基本表，一个表可以带若干索引，索引也存储在存储文件中。每个存储文件就是外部存储器上的一个物理文件，存储文件的逻辑结构组成了关系数据库的内模式。

(2)在用户看来，基本表和视图一样，都是关系(即表)。4.1.4SQLServer中的T-SQL对标准SQL的扩充

T–SQL(TransactSQL的简写)是MicrosoftSQLServer对标准SQL的扩充。主要体现在以下几个方面：

(1)提供控制流语言作为SQL语句或批处理的一部分。

(2)对于SELECT语句中的COMPUTE子句，当它与聚集函数(SUM、MAX、MIN、AVG、COUNT)结合使用时，能产生小计和总计报告。

(3)支持存储过程。存储过程是可按名称调用的、已编译过的一组T-SQL语句，它能显著提高系统性能。

(4)提供先进的数据完整性机制。

(5)为监视和优化系统性能提供多种全局变量、数据库选项和查询处理选项。

本章主要依据T–SQL的语法进行SQL介绍和举例。

在SQL中用数据定义语言DDL对关系模式及相关对象进行定义(包括建立、删除和修改)，使得数据库中的一些概念，如数据库、表、视图及索引不再是抽象的。4.2SQL数据定义本章以学生—选课数据库作为例子来讲解：

·学生S(学号sno，姓名sname，性别sex，年龄age，民族nation，籍贯place，所属班级clname)，如表4.1所示。

·课程C(课程编号cno，课程名称cname，学期term，学时period，学分credit)，如表4.2所示。

·选课SC(学号sno，课程编号cno，成绩grade)，如表4.3所示。表4.1学生表S表4.2课程表C表4.3选课表SC4.2.1SQLServer中数据库的创建、删除与修改

1．创建数据库

创建数据库，就是在中、大型数据库管理系统中开辟一片存储空间，用于存放数据

库中的数据库对象，包括表、视图、存储过程、触发器及与数据库安全性有关的控制机制等。创建数据库命令的语法格式如下：

CREATEDATABASE数据库名

[ON

[PRIMARY]

[<filespec>[,…n]]

[,<filegroup>[,…n]]

] /*ON子句指定数据库文件和文件组属性*/

[LOGON{<filespec>[,…n]}] /*LOGON子句指定日志文件属性*/

[FORLOAD|FORATTACH]

<filespec>::=

(NAME='逻辑文件名',

FILENAME='数据库文件名'

[,SIZE=size]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROWTH=growth_increament])具体说明：

·[]中的内容为任选项。

·

｜表示或者，可选一个或不选。

·PRIMARY用来指定主文件，若不指定主文件，则数据文件中的第一个文件将成为主文件。

·逻辑文件名是数据库创建后在所有SQL语句中使用的名称。

·数据库文件名是在创建物理文件时使用的路径和文件名。

·size是数据文件的初始大小。

·max_size指定文件的最大容量；UNLIMITED关键字指明文件大小不限(仅受磁盘空间限制)。growth_increament指出文件每次增长的增量，有百分比和空间值两种，不指定FILEGROWTH参数时，默认值为10%，最小值为64KB；指定值growth_increament应为64KB的倍数，但不能超过MAXSIZE的值。数据文件大小的单位默认值为MB。

·[,…n]表示可以有n个与前面相同的描述，描述之间以逗号隔开。

·FORLOAD子句说明从一个备份库向新建的数据库中加载数据，用以与早期版本的MicrosoftSQLServer兼容；FORATTACH子句说明从已有的数据文件向数据库添加数据，必须指定主数据文件。

例4.1

用SQL命令创建一个教学数据库student，数据文件的逻辑名称为student_Data，数据文件物理地存放在D盘的根目录下，文件名为student_Data.mdf，数据文件的初始存储空间大小为10MB，最大存储空间为50MB，存储空间自动增长量为5MB；日志文件的逻辑名称为student_Log，日志文件物理地存放在D盘的根目录下，文件名为student_Log.ldf，初始存储空间大小为10MB，最大存储空间为25MB，存储空间自动增长量为5MB。

SQL命令如下：

CREATEDATABASEstudent

ON

( NAME=student_Data,

FILENAME='D:\student_data.mdf',

SIZE=10,

MAXSIZE=50,

FILEGROWTH=5)

LOGON

( NAME=student_Log,

FILENAME='D:\student_Log.ldf',

SIZE=5,

MAXSIZE=25,

FILEGROWTH=5)

2.修改数据库

修改数据库命令的语法格式如下：

ALTERDATABASE数据库名

{ADDFILE<filespec>[,…n][TOFILEGROUPfilegroup_name]

/*在文件组中增加数据文件*/

|ADDLOGFILE<filespec>[,…n] /*增加日志文件*/

|REMOVEFILElogical_file_name /*删除数据文件*/

|ADDFILEGROUPfilegroup_name /*增加文件组*/

|REMOVEFILEGROUPfilegroup_name /*删除文件组*/

|MODIFYFILE<filespec> /*更改文件属性*/

|MODIFYNAME=new_dbname /*数据库更名*/

}具体说明：

·ADDFILE子句：向数据库添加数据文件，文件属性在<filespec>中列出。关键字TOFILEGROUP将指定添加的数据文件加到文件组filegroup_name(文件组名)中，若省略，则为主文件组。

·ADDLOGFILE子句：向数据库添加日志文件，该文件属性在<filespec>中列出。

·REMOVEFILE子句：从数据库中删除数据文件，数据文件的逻辑文件名由其中的参数logical_file_name给出。当删除一个数据文件时，逻辑文件和物理文件全部被删除。

·ADDFILEGROUP子句：向数据库添加文件组，由参数filegroup_name给出新添加的文件组名。

·REMOVEFILEGROUP子句：从数据库中删除文件组，由参数filegroup_name给出被删除的文件组名。只有当文件组为空时才能删除。

·MODIFYFILE子句：更改数据文件的属性，被更改文件的逻辑名由<filespec>的NAME给出，在该子句中，NAME属性不可改，可以更改的属性包括FILENAME、SIZE、MAXSIZE和FILEGROWTH，一次只能更改其中的一个属性。

·MODIFYNAME子句：更改数据库名，新的数据库名由参数new_dbname给出。

例4.2

将例4.1的数据库student中主数据文件的大小改为不限制，增长方式改为每次增长15MB。

SQL语句如下：

ALTERDATABASEstudent

MODIFYFILE

(NAME='student_Data',

MAXSIZE=UNLIMITED)

/*第一次修改，将主数据文件的大小改为不限制*/

ALTERDATABASEstudent

MODIFYFILE

(NAME='student_Data',

FILEGROWTH=15MB)

/*第二次修改，将增长方式改为每次增长15MB*/

3．删除数据库

删除数据库命令的语法格式如下：

DROPDATABASEdatabase_name

此句的功能是删除指定的数据库，其中，database_name是指定要删除的数据库名。4.2.2基本表的创建、删除与修改

1．创建基本表

创建基本表，就是定义基本表的结构，可用CREATETABLE语句实现，其格式如下：

CREATETABLE<基本表名>

(<列名1><数据类型>[<列级完整性约束>]

[,<列名2><数据类型>[<列级完整性约束>]]…

[<表级完整性约束>])这里，<基本表名>就是所定义的基本表的名字。基本表至少由一列组成，列类型可以是DBMS支持的基本数据类型，也可以是用户自定义的数据类型，各DBMS支持的数据类型并不完全相同。例如，SQLServer支持如表4.4所示的主要数据类型。表4.4SQLServer标准数据类型不同DBMS实现用户自定义数据类型时有所不同，需用时，可参考有关资料。

完整性约束是指对某一列或若干列的输入值实行限制而设置的约束条件，对某一列设置的约束条件称为列级完整性约束，对若干列设置的约束条件称为表级完整性约束。完整性约束主要有：

·NULL/NOTNULL，限制列取值空/非空。

·DEFAULT，给定列的默认值。

·UNIQUE，限制列取值不重复。

·CHECK，限制列的取值范围。

·PRIMARYKEY，指定本列为主键。

·FOREIGNKEY，定义本列为引用其他表的外键。使用形式如下：

［FOREIGNKEY(<外键列名>)］REFERENCES<外表名>(<外表列名>)

这些定义都可在前面加“CONSTRAINT

<约束名>”，使用形式如下：

[CONSTRAINT<约束名>]<约束类型>

其中，约束类型有NULL/NOTNULL、UNIQUE、CHECK、PRIMARYKEY及FOREIGNKEY。说明：

·NULL表示“不知道”、“不确定”或“没有数据”的意思。

·UNIQUE指明基本表在某一列或多个列的组合上的取值必须唯一。

·一个基本表中只能有一个PRIMARYKEY，但可有多个UNIQUE。

·对于指定为PRIMARYKEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的唯一键，则允许为NULL。

·FOREIGNKEY所引用的列必须是有PRIMARYKEY约束或UNIQUE约束的列。

例4.3

定义学生选课数据库的学生表、课程表和选课表。

SQL命令如下：

CREATETABLES

(sno varchar(12) constraints_primprimarykey,

sname varchar(20) constraints_consnotnull,

sex varchar(10) default'男',

age int check(age>=0andage<=120),

nation varchar(20) notnull,

clname varchar(20),

place varchar(20) notnull

)

等价于

CREATETABLES

(sno varchar(12) primarykey,

sname varchar(20) notnull,

sex varchar(10) default'男',

age int check(age>=0andage<=120),

nation varchar(20) notnull,

clname varchar(20),

place varchar(20) notnull

)

也就是说，CONSTRAINT可以不要。

CREATETABLEC

(cno varchar(12) notnull,

cname varchar(20) notnull,

term varchar(10) notnull,

period varchar(10) notnull,

credit real,

primarykey(cno)

)

CREATETABLESC

(sno varchar(12),

cno varchar(12),

grade real check(grade>=0andgrade<=100),

primarykey(sno,cno),

foreignkey(sno)referencess(sno),

foreignkey(cno)referencesc(cno)

)

2．修改基本表

基本表建立后，有时需要对基本表结构进行修改。SQL中，ALTERTABLE语句可对表增加新列，改变原列类型，增加或删除完整性约束，其语句格式如下：

ALTERTABLE<表名>

[ALTERCOLUMN<列名><新数据类型>]

|[ADD<列名><数据类型>[约束]]

|[DROPCOLUMN<列名>]]

|[ADD[constraint约束名]

|[DROP[constraint]<约束名>]

其中，

·ADD子句为基本表增加一新列。

·ALTERCOLUMN子句修改基本表中原有列定义。

·DROPCOLUMN子句删除基本表中原有列。

·ADDCONSTRAINT子句增加表级新约束。

·DROPCONSTRAINT子句删除原有表级约束。

例4.4

在学生表中增加“联系电话”列，数据类型为字符型，长度为11。

SQL命令如下：

ALTERTABLES

ADDPhoneCHAR(11)

注意：新增加的列的约束不能定义为“NOTNULL”，因为基本表在增加一列后，原有元组在新增加的列上的值都被定义为空值(NULL)。

例4.5

删除课程表中的credit列。

SQL命令如下：

ALTERTABLEC

DROPCOLUMNcredit

3．删除基本表

DROPTABLE语句可删除不需要的表，其一般格式如下：

DROPTABLE<表名>

例4.6

删除学生表。

SQL命令如下：

DROPTABLES

表一旦删除，其结构及表中数据将全部被删除，所以删除表时要谨慎。4.2.3创建与使用索引

1．索引的作用

建立索引是加快查询速度的有效手段。用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。一般说来，建立与删除索引由数据库管理员DBA或表的拥有者(即建立表的人)负责完成。使用索引时可以在检索数据的过程中使用查询优化器，提高系统性能。系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能选择索引。索引并不是必需的，索引是为了加速检索而创建的一种存储结构。使用索引的主要优点就是可以大幅度提高对数据库表中数据的查询速度。每个索引在一个表的数据页面以外建立索引页面，在索引页面中的行包含了对应表中数据行的逻辑指针，通过该指针可以直接检索到数据行，以此加速对物理数据的检索。合理地规划和使用索引能较大程度地提高数据操作的速度，但对索引的不当使用却可能降低数据操作的速度。通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。如果应用程序非常频繁地更新数据，或磁盘空间有限，那么最好限制索引的数量。索引是有用的(可以加快数据访问速度。如没有索引，在查找一个主题的信息时，必须一次一页地扫描整个文本)，但索引要耗用磁盘空间，并增加系统开销和维护费用。在使用索引时要考虑以下事实和要点：

(1)修改一个索引列上的数据时，要更新相关的索引。

(2)维护索引需要时间和资源，所以不要创建不经常使用的索引。

(3)小表上的索引没有多少好处。

2．索引的分类

1)聚集索引

聚集索引按照索引的字段排列记录，并且将排列好的结果存储在表中，且只有一个。聚集索引确定表中数据的物理顺序。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引)。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理存储上相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集(物理排序)，避免每次查询该列时都进行排序，从而节省成本。

当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一学号sno查找特定学生的最快速的方法，就是在sno列上创建聚集索引或PRIMARYKEY约束。

2)非聚集索引

非聚集索引按照索引的字段排列记录，但排列的结果不存储在表中，而是另外存储，可以有多个。也就是说，数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。

索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储(这可以由聚集索引规定)。如果在表中没有创建聚集索引，则无法保证这些行具有任何特定的顺序。

聚集索引与非聚集索引的比较：聚集索引查询速度快，因为在表中，数据按照索引的顺序存储，检索效率比普通索引高，但对数据新增/修改/删除的影响比较大；非聚集索引不影响表中的数据存储顺序，检索效率比聚集索引低，对数据新增/修改/删除的影响很少，也就是说更新速度快。可以在一个表上建立多个非聚集索引，但一个表只能建立一个聚集索引。

3)唯一索引

唯一索引可以确保索引列不包含重复的值。在建表时，数据库管理系统根据UNIQUE自动建立非聚集的唯一索引，根据PRIMARYKEY建立聚集索引。

4)复合索引

复合索引是将两个或多个字段组合起来建立的索引，单独的字段允许有重复的值。

注意：

(1)改变表中的数据(如增加或删除记录)时，索引将自动更新。

(2)索引建立后，在查询使用该列时，系统将自动使用索引进行查询。

(3)索引数目无限制，但索引越多，更新数据的速度越慢。对于仅用于查询的表可多建索引，对于数据更新频繁的表则应少建索引。

3．索引的创建

创建索引使用CREATEINDEX语句，其一般格式如下：

CREATE[UNIQUE][CLUSTERED]

INDEX<索引名>ON<基本表名>

(<列名>[<次序>][，<列名>[<次序>]]…)

其中：

·UNIQUE表示创建唯一索引；CLUSTERED表示创建聚集索引。

·基本表名为要建索引的表名，索引可建在基本表的一列或多列上，各列之间用逗号分隔。

·每个<列名>后可用<次序>指定索引值的排列顺序，<次序>可为ASC(升序)或DESC(降序)，缺省值为ASC。

例4.7

在学生表S的性别SEX列上创建非聚集索引。

SQL命令如下：

CREATEINDEXSEX_INDONS(SEX)

4．索引的删除

建立索引可减少查询操作的时间，但也增加了系统维护索引的时间，对数据增、删、改操作频繁时更是如此，所以索引不是越多越好，不必要的索引应该删除。删除索引可用DROPINDEX语句，其格式如下：

DROPINDEX<表名.索引名>

例4.8

删除S表中的SEX_IND索引。

SQL命令如下：

DROPINDEXS.SEX_IND

4.3.1SELECT语句格式及基本使用

数据查询是数据库的核心操作。SQL的数据查询仅有一条SELECT语句，但功能极强，使用方式灵活，是用途最广泛的一条语句。

其一般格式如下：

SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…4.3SQL数据查询

FROM<基本表名或视图名>[,<基本表名或视图名>]…

[WHERE<行条件表达式>]

[GROUPBY<列名1>[HAVING<组条件表达式>]]

[ORDERBY<列名2>[ASC|DESC]]

说明：

(1)

SELECT语句中前两个子句是必选项，其他可省略。[ALL

|

DISTINCT]为可选项，缺省为ALL。如果选择DISTINCT，则消除结果集中重复的行。

(2)整个SELECT语句的含义是：根据WHERE子句的筛选条件表达式，从FROM子句指定的表中找出满足条件的记录，再按SELECT语句中指定的字段次序，筛选出记录中的字段值并构造一个显示结果表。

(3)如果有GROUP子句，则将结果按<列名1>的值进行分组，该值相等的记录为一个组。如果GROUP子句带HAVING短语，则只有满足指定条件的组才会显示输出。

(4)如有ORDER子句，则结果还要按<列名2>的值的升序或降序排序。4.3.2简单查询

1．查询全部列

例4.9

查询全部学生的信息。

SQL命令如下：

SELECT*FROMS

其中，用“*”表示全部列，不用逐一列出，也可以写为：

SELECTsno,sname,sex,age,nation,clname,placeFROMS

该语句的执行结果为：2．查询指定列

例4.10

查询全体学生的学号与姓名。

SQL命令如下：

SELECTsno,snameFROMS

3．改变列标题

格式：

列名|表达式[AS]列标题

功能是为列或表达式取一个别名。

例4.11

查询学生的学号和姓名，要求得到标题为学号及姓名的表格。

SQL命令如下：

SELECTsnoAS学号,snameAS姓名

FROMS

该语句的执行结果为4．查询经过计算的值

例4.12

查询学生的姓名和出生年份，要求得到标题为姓名及出生年的表格。

SQL命令如下：

SELECTsnameAS姓名,2009-ageAS出生年份FROMS

该语句的执行结果为

5．过滤掉重复元组

例4.13

列出表SC中所有的学号(即所有已经选课的学生的学号)。

SQL命令如下：

SELECTDISTINCTsnoFROMSC

(可以自己比较一下SELECTDISTINCTSnoFROMSC与SELECTTsnoFROMSC的区别。)

6．ORDERBY子句

用户可以用ORDERBY子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列，默认值为升序。

例4.14

查询全体学生情况，查询结果按年龄降序排列。

SELECT*

FROMS

ORDERBYageDESC;4.3.3条件查询

当要在表中查询满足某些条件的数据时，需要使用WHERE子句。

WHERE子句中的行条件表达式中常使用的操作符如表4.5所示。表4.5SQL条件表达式中涉及的符号1．比较大小

例4.15

查询选修课程号为c01的学生的学号和成绩。

SQL命令如下：

SELECTsno,grade

FROMSC

WHEREcno='c01'

例4.16

查询成绩高于85分的学生的学号、课程号和成绩。

SQL命令如下：

SELECTsno,cno,grade

FROMSC

WHEREgrade>85

2．多重条件查询

例4.17

查询选修课程号为c01或c03，且分数大于等于85分的学生的学号、课程号和成绩。

SQL命令如下：

SELECTsno,cno,grade

FROMSC

WHERE(cno='c01'orcno='c03')and(grade>=85)

3．确定范围

谓词BETWEEN…AND…和NOTBETWEEN…AND…可以用来查找属性值在(或不在)指定范围内的元组。其中，BETWEEN后是范围的下限(即低值)；AND后是范围的上限(即高值)。

例4.18

查找课程号为c01，且成绩在75～89分之间的学生的学号和成绩。

语句一：

SELECTsnoas学号,gradeas成绩

FROMSC

WHEREcno='c01'ANDgradeBETWEEN75AND89语句二：

SELECTsnoas学号,gradeas成绩

FROMSC

WHEREcno='c01'ANDgrade>=75ANDgrade<=89

上述两个语句用不同的方式表达了同样的意思，都是正确的。

4．确定集合

谓词IN可以用来查找属性值属于指定集合的元组。与IN相对的谓词是NOTIN，用于查找属性值不属于指定集合的元组。

例4.19

查找课程号为c01、c02、c03的学生的学号、课程号和成绩。

语句一：

SELECTsnoas学号,cnoas课程号,gradeas成绩

FROMSC

WHEREcnoIN('c01','c02','c03')

语句二：

SELECTsnoas学号,cnoas课程号,gradeas成绩

FROMSC

WHEREcno='c01'ORcno='c02'ORcno='c03'

5．部分匹配查询

谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下：

列名[NOT]LIKE'<匹配串>'[ESCAPE'<转义字符>']

其含义是查找列值与＜匹配串＞相匹配的元组。＜匹配串＞可以是一个完整的字符串，也可以是含有通配符的字符串。

SQLServer的通配符有以下几个：

·%：代表任意多个字符。

·_(下划线)：代表单个字符。

·[]：代表指定范围内的单个字符，[]中可以是单个字符(如[abcde])，也可以是字符范围(如[a-e])。

·[^]：代表不在指定范围内的单个字符，[^]中可以是单个字符(如[^abcde])，也可以是字符范围(如[^a-e])。

如果要查找的字符串正好含有通配符，比如下划线或百分号，就需要使用一个特殊子句来告诉系统这里的下划线或百分号是一个普通的字符，而不是一个通配符，这个特殊的子句就是ESCAPE。其中，“转义字符”可以是任何有效的字符。

例4.20

查询所有姓李的学生的个人信息。

SQL命令如下：

SELECT*

FROMS

WHEREsnameLIKE'李%'

该语句的执行结果为例4.21

查找含有“数”的课程名的课程信息。

SQL命令如下：

SELECT*

FROMC

WHEREcnameLIKE'%数%'

例4.22

查找学号含有“5”的学生的基本信息。

SQL命令如下：

SELECT*

FROMS

WHEREsnoLIKE'%[5]%'该语句的执行结果为

例4.23

查询学生表中姓张、姓李和姓刘的学生的情况。

SQL命令如下：

SELECT*

FROMS

WHEREsnameLIKE'[张李刘]%'

例4.24

查询学生表中名字的第2个字为“小”或“大”的学生的姓名和学号。

SQL命令如下：

SELECT*

FROMS

WHEREsnameLIKE'_[小大]%'

例4.25

查询学生表中所有不姓李的学生。

SQL命令如下：

SELECT*

FROMS

WHEREsnameNOTLIKE'李%'

例4.26

从学生表中查询学号的最后一位不是1、2、3的学生的信息。

SQL命令如下：

SELECT*FROMS

WHEREsnoLIKE'%[^123]'

例4.27

查询DB_Design课程的课程号和学分。

SQL命令如下：

SELECTcno,credit

FROMC

WHEREcnameLIKE'DB\_Design'ESCAPE'\'

其中，ESCAPE'\'短语表示“\”为换码字符，这样，匹配串中紧跟在“\”后面的字符“_”不再具有通配符的含义，而转义为普通的“_”字符。

6．空值查询

某个字段没有值称之为具有空值(NULL)。空值不同于零或空格，它不占任何存储空间。

例4.28

查找考试成绩为空的学生的学号和课程号。

SQL命令如下：

SELECTsnoAS学号，cnoAS课程号

FROMSC

WHEREgradeISNULL该语句的执行结果为4.3.4聚合查询

聚集函数是以一个值集合为输入，返回单个值的函数。SQL提供了许多聚集函数，主要有：

·MIN(<表达式>)，求(字符、日期、数值列)的最小值。

·MAX(<表达式>)，求(字符、日期、数值列)的最大值。

·COUNT(*)，计算选中结果的行数。

·COUNT([ALL∣DISTINCT]<表达式>)，计算所有/不同值的个数。

·SUM([ALL∣DISTINCT]<表达式>)，计算所有/不同列值的总和。

·AVG([ALL∣DISTINCT]<表达式>)，计算所有/不同列值的平均值。

例4.29

查询选修了c01号课程的学生的最高分和最低分。

SQL命令如下：

SELECTMAX(grade)最高分,MIN(grade)最低分

FROMSCWHEREcno='c01'

该语句的执行结果为

例4.30

求学号为20080101的学生的总分和平均分。

SQL命令如下：

SELECTSUM(grade)AS总分,AVG(grade)AS平均分

FROMSC

WHERE(sno='20080101')

该语句的执行结果为4.3.5分组查询

GROUPBY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。

注意：

(1)

GROUPBY子句中的分组依据的列必须是表中存在的列名，不能使用AS子句指派的结果集列的别名。带有GROUPBY子句的SELECT语句的查询列表中只能出现分组依据列或统计函数，因为分组后每个组只返回一行结果。

(2)

HAVING子句用于对分组后的结果进行再过滤，它的功能有点像WHERE子句，但它用于组而不是单个记录。在HAVING子句中可以使用统计函数，但在WHERE子句中则不能。HAVING通常与GROUPBY子句一起使用。

(3)

WHERE子句用来筛选FROM子句中指定的数据源所产生的行数据。GROUPBY子句用来对经WHERE子句筛选后的结果数据进行分组。HAVING子句用来对分组后的结果数据进行筛选。对于可以在分组操作之前应用的搜索条件，在WHERE子句中指定它们更有效，这样可以减少参与分组的数据行。应用在HAVING子句中指定的搜索条件应该是那些必须在执行分组操作之后应用的搜索条件。建议将所有行搜索条件放在WHERE子句中而不是HAVING子句中。

例4.31

统计每门课程选修的人数，并列出课程号和选课人数。

SQL命令如下：

SELECTcnoAS课程号,COUNT(sno)AS选课人数

FROMSC

GROUPBYcno

该语句首先对查询结果按cno的值分组，所有具有相同cno值的元组归为一组，然后再对每一组使用COUNT函数进行计算，求得每组的学生人数。结果如图4.2所示。

图4.2GROUPBY子句的使用

例4.32

统计选修人数大于2的课程号和人数。

SQL命令如下：

SELECTcnoAS课程号,COUNT(*)AS选课人数

FROMSC

GROUPBYcno

HAVING(COUNT(*)>=2)

GROUPBY子句按cno的值分组，所有具有相同cno的元组为一组，对每一组使用函数COUNT进行计算，

统计出每位学生选课的门数。

HAVING子句去掉不满足COUNT(*)>=2的组。结果如图4.3所示。

图4.3GUOUPBY子句与HAVING子句的使用

例4.33

查询每名学生的选课门数和平均成绩。

SQL命令如下：

SELECTsno学号,COUNT(*)选课门数,AVG(Grade)平均成绩

FROMSC

GROUPBYsno

GROUPBY子句按sno的值分组，所有具有相同sno的元组为一组，对每一组使用函数COUNT进行计算，统计出每位学生选课的门数。结果如图4.4所示。

图4.4含有空值的分组统计注意，学号为20080102的学生平均成绩没有计算“NULL”值的平均成绩。聚合函数消除了空值。

例4.34

统计每个班的学生人数和平均年龄。

SQL命令如下：

SELECTclnameAS班名,COUNT(*)AS学生人数,AVG(age)AS平均年龄

FROMS

GROUPBYclname

例4.35

利用带WHERE子句的分组统计每个班的女生人数。

SQL命令如下：

SELECTclnameAS班名,COUNT(*)女生人数

FROMS

WHEREsex='女'

GROUPBYcname

例4.36

按多列分组，统计每个班的男生人数和女生人数以及男生的最大年龄和女生的最大年龄，结果按系名升序排序。

SQL命令如下：

SELECTclnameAS班名,sexAS性别,COUNT(*)AS人数,MAX(age)AS最大年龄

FROMS

GROUPBYclname,sex

ORDERBYclname

例4.37

查询软件工程和网络工程班的学生人数。

SQL命令如下：

SELECTclnameAS班名,COUNT(*)

FROMS

GROUPBYclname

HAVINGclnameIN('软件工程','网络工程')

或

SELECTclnameAS班名,COUNT(*)

FROMS

WHEREclnameIN('软件工程','网络工程')

GROUPBYclname

第二种写法比第一种写法效率要高，因为参与分组的数据比较少。4.3.6连接查询

连接查询是一种涉及多表的查询，和单表查询的区别是：连接查询中的FROM子句后要指定查询涉及的多个表或视图，WHERE子句中必须指定多个表或视图两两相连的连接条件，在引用表中有同名列时，要在列名前加表名，格式为“表名.列名”，以示区别。

1．内连接

根据连接的操作符不同，内连接可分为：等值连接、自然连接和非等值连接，它是组合两个表最常用的方法。

等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

非等值连接：在连接条件中使用除等于号运算符以外的其他比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。自然连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

注意，连接还有一个特例就是自连接，自连接是在同一张表内进行自身连接，即将同一个表中的不同行连接起来。自连接可以看作是一张表的两个副本之间进行的连接。在自连接中，必须为两个表分别指定别名，格式为“<源表名>[AS]<表别名>”，使它们在逻辑上成为两张表。如果为表指定了别名，那么查询语句中其他所有用到表名的地方都要使用此别名。

内连接有以下两种形式的语法：

SELECT<字段列表>

FROM<表1>[INNER]JOIN<表2>

ON<表1.列1><关系运算符><表2.列2>

或

SELECT<字段列表>

FROM<表1>,<表2>

WHERE<表1.列1><关系运算符><表2.列2>

例4.38

查询选修课程号为c03课程的学生学号、姓名和成绩。

方法1：

SELECTS.sno,sname,grade

FROMS,SC

WHERE(S.sno=SC.sno)AND(SC.cno='c03')

方法2：

SELECTS.sno,sname,grade

FROMSINNERJOINSC

ONS.sno=SC.sno

WHERE(SC.cno='c03')

该语句的执行结果为

例4.39

查询选修课程号为c03课程的学生的基本信息和成绩。

方法1：

SELECT*

FROMS,SC

WHERE(S.sno=SC.sno)AND(SC.cno='c03')

该语句的执行结果为方法2：

SELECTS.*,cno,grade

FROMS,SC

WHERE(S.sno=SC.sno)AND(SC.cno='c03')

注意：S.*表示选取S表的所有属性。

该语句的执行结果为

例4.40

查询学生选修课程的信息，包括学号、姓名、课程名及成绩。

方法1：

SELECTS.sno,sname,clname,grade

FROMS,C,SC

WHERES.sno=SC.snoandSC.cno=C.cno

方法2：

SELECTS.sno,S.sname,C.cname,SC.grade

FROMSINNERJOINSC

ONS.sno=SC.snoINNERJOINC

ONSC.cno=C.cno

例4.41

查询选修了课程号为c01和c03两门课程的学生的学号(自连接)。

方法1：

SELECTsc1.sno

FROMSCASsc1,SCASsc2

WHEREsc1.sno=sc2.snoAND(o='c01')AND(o='c03')

方法2：

SELECTsc1.sno

FROMSCsc1INNERJOINSCsc2ONsc1.sno=sc2.sno

WHERE(o='c01')AND(o='c03')

例4.42

查询每个学生考试的平均成绩，并列出学生的学号、姓名和平均成绩。

SQL命令如下：

SELECTS.snoAS学号,snameAS姓名,AVG(grade)AS平均成绩

FROMS,SC

WhereS.sno=SC.sno

GROUPBYS.sno,sname

2．外连接

在自然连接中，只有在两个表中匹配的行才能在结果集中出现，而在外连接中，可以只限制一个表，而对另外一个表不加限制(即所有的行都出现在结果集中)。

外连接分为左外连接、右外连接和全外连接三种形式：

·左外连接是对连接条件中左边的表不加限制，即左边的表为外表，右边的表为内表。

·右外连接是对连接条件中右边的表不加限制，即右边的表为外表，左边的表为内表。

·全外连接是对连接条件中的两个表都不加限制，即所有两个表中的行都会包括在结果集中。

左外连接的语法为

SELECT<字段列表>

FROM<表1>LEFT[OUTER]JOIN<表2>

ON<表1.列1>=<表2.列2>

右外连接的语法为

SELECT<字段列表>

FROM<表1>RIGHT[OUTER]JOIN<表2>

ON<表1.列1>=<表2.列2>

全外连接的语法为

SELECT<字段列表>

FROM<表1>FULL[OUTER]JOIN<表2>

ON<表1.列1>=<表2.列2>

例4.43

查询学生的选课情况，包括选修了课程的学生和没有选修课程的学生。

SQL命令如下：

SELECTS.sno,sname,cno,grade

FROMSLEFTOUTERJOINSC

ONS.cno=SC.sno该语句的执行结果为

例4.44

查询哪些课程没有人选，并列出课程号和课程名。

SQL命令如下：

SELECTC.cno,cname

FROMCLEFTJOINSC

ONC.cno=SC.cno

WHERESC.cnoISNULL

该语句的执行结果为

3．交叉连接

交叉连接(CrossJoin)也叫非限制连接，它将两个表不加任何约束地组合起来，在数学上就是两个表的笛卡儿积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。

交叉连接的语法如下：

SELECT<字段列表>

FROM<表1>CROSSJOIN<表2>

或者

SELECT<字段列表>

FROM<表1>,<表2>在实际应用中，使用交叉连接产生的结果集一般没什么意义，但在数据库理论研究上有重要的作用。4.3.7集合查询

在关系代数中，我们学习了交、并、差及笛卡儿积(上面讲的交叉连接)集合运算，相应地，在SQL的查询操作符中有UNION(并操作)、INTERSECT(交操作)和EXCEPT(差操作)(注：并不是所有的数据库管理系统都支持这些操作)。进行这些操作的前提条件是操作的关系是相容的，即必须含有相同的属性集。

例4.45

查询选修了c01或c02两门课的学生的学号和姓名。

SQL命令如下：

SELECTS.snoas学号,snameas姓名

FROMS,SC

WHERES.sno=SC.snoandcno='c01'

UNION

SELECTS.snoas学号,snameas姓名

FROMS,SC

WHERES.sno=SC.snoandcno='c02'4.3.8子查询

子查询是指一条SELECT语句作为另一条SELECT语句的一部分，外层的SELECT语句被称为外部查询，内层的SELECT语句被称为内部查询(或子查询)。子查询的返回值为单值时，可用“算术比较符”；返回的值为多列或多行值时，则用IN、ANY、ALL、EXISTS等运算符。

子查询的执行过程为：首先执行子查询，子查询得到的结果集不被显示出来，而是传递给外部查询并作为外部查询的条件来使用，然后执行外部查询并显示查询结果集。

注意：

(1)子查询是一个SELECT查询，它嵌套在SELECT、INSERT、UPDATE、DELETE语句的WHERE、HAVING子句或其他子查询中。

(2)子查询的SELECT查询使用圆括号括起来。

(3)子查询语句可以出现在任何能够使用表达式的地方，通常情况下，子查询语句用在外层查询的WHERE子句或HAVING子句中。

1．带有IN谓词的子查询

带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，以判断某个属性列值是否在子查询的结果中的查询。

一般格式为

列名[NOT]IN(子查询)

例4.46

查询与张强在同一个班的学生。

SQL命令如下：

SELECTsno,sname

FROMS

WHEREclnameIN

(SELECTclname

FROMS

WHEREsname='张强')

该语句的执行结果为

例4.47

查询选修了“高等数学”课程的学生的选课门数和平均成绩。

SQL命令如下：

SELECTsnoAS学号,COUNT(*)AS选课门数,AVG(grade)AS平均成绩

FROMSC

WHEREsnoIN(

SELECTsnoFROMSCJOINC

ONC.cno=SC.cno

WHEREcname='高等数学')

GROUPBYsno该语句的执行结果为

例4.48

查找网络工程班没选修课程号为c03课程的学生情况。

SQL命令如下：

SELECT*

FROMS

WHEREclname='网络工程'AND

snoNOTIN

(SELECTsnoFROMSC

WHEREcno='c03')

2．带有比较运算符的子查询

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可以用>、<、=、>=、<=、!=或<>等比较运算符。

利用子查询进行比较测试时，通过比较运算符(=、<>、<、>、<=、<=)将一个表达式的值与子查询返回的值进行比较。要求子查询语句返回的值必须是单值。

一般格式为

WHERE表达式比较运算符(子查询)

例4.49

查询选修了c01课程且成绩高于此课程的平均成绩的学生的学号和成绩。

SQL命令如下：

SELECTsnoAS学号,gradeAS成绩

FROMSC

WHEREcno='C01'

ANDgrade>(SELECTAVG(grade)

FROMSC

WHEREcno='c01')

3．带有ANY(some)或ALL谓词的子查询

使用ANY(some)或ALL谓词时必须同时使用比较运算符。ANY表示任意一个值，ALL表示全部值。

例4.50

查询选修课程号为c03的学生的姓名。

SQL命令如下：

SELECTsnameAS姓名

FROMS

WHEREsno=ANY(SELECTsno

FROMSC

WHEREcno='C03')等价于：

SELECTsnameas姓名

FROMS,SC

WHERES.sno=SC.snoandcno='c03'

例4.51

查询比软件工程班级成绩高的学生的学号、姓名、课程号和成绩。

SQL命令如下：

SELECTS.snoAS学号,snameAS姓名,cno,gradeAS成绩

FROMS,SC

WHERES.sno=SC.snoANDgrade>ALL

(SELECTgrade

FROMS,SC

WHERES.sno=SC.snoANDclname='软件工程')

4．带有EXISTS谓词的子查询

一般形式如下：

WHERE[NOT]EXISTS(子查询)

带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。

EXISTS：当子查询中有满足条件的数据时，返回真值，否则返回假值。NOTEXISTS：当子查询中有满足条件的数据时，返回假值，否则返回真值。

例4.52

查询选修了c02号课程的学生的姓名。

SQL命令如下：

SELECTsnameAS姓名

FROMS

WHEREEXISTS(SELECT*

FROMSC

WHEREsno=S.snoANDcno='c02')等价于：

SELECTsnameAS姓名

FROMS,SC

WHERES.sno=SC.snoANDcno='c02'4.3.9将查询结果存储到表中

使用SELECT语句中的INTO子句可以在查询的基础上创建新表，INTO子句首先创建一个新表，然后用查询的结果填充新表。其语法格式为

SELECT<字段列表>

INTO<新表名>

FROM<参与查询的表>

[WHERE<查询选择的条件>]

[GROUPBY<分组表达式>]

[HAVING<分组查询条件>]

[ORDERBY<排序表达式>[ASC∣DESC]]

由于新表的结构由<字段列表>定义，因此<字段列表>中的每一列必须有名称，如果是一个表达式，则应该为其指定别名。

例4.53

将没有选修c08课程的学生的学号和姓名插入到c08table表中。

SQL命令如下：

SELECTS.snoAS学号,snameAS姓名

INTOc08table

FROMS

WHEREsnoNOTIN(SELECTsnoFROMSCWHEREcno='c08')

例4.54

查询所有学生的学号、姓名、总分和平均分，并将查询结果存放到一个新的数据表stotal中。

SQL命令如下：

SELECTS.snoAS学号,snameAS姓名,SUM(grade)AS总分,AVG(grade)AS平均分总分

INTOstotal

FROMS,SC