SQL从入门到精通

KI9I9

贡献于2014-06-14

字数:0 关键词: SQL

 SQL概述  SQL语言的组成  探索 SQL 环境 SQL 是 Structured Query Language 的缩写,其中文含义为“结构化查询语言”。 SQL 虽然被称为查询语言,但其功能不仅仅是查询,也有很多其他功能。目前流行的所 有数据库系统几乎都支持 SQL 语言,换句话说,学会 SQL 语言后,便可以操作当前流 行的所有数据库系统。这也是为什么要学习 SQL 语言的原因。 SQL 语言基础 第 章 SQL 技术与网络数据库开发详解 ·24· 3.1 SQL 概述 人与人交互必须使用某种人类的自然语言,如英语、汉语和蒙语等。人与数据库交互就不能使用 人类的自然语言了,而需要使用 SQL 语言。人们使用 SQL 语言可以告诉具体的数据库系统要干什么 工作,让其返回什么数据等。 3.1.1 SQL 的历史 SQL 语言是 20 世纪 70 年代由 Boyce 和 Chamberlin 提出的。1979 年,IBM 公司第一个开发出 SQL 语言,并将其作为 IBM 关系数据库原型 System R 的关系语言,实现了关系数据库中的信息检索。20 世纪 80 年代初,美国国家标准局(ANSI)开始着手制定 SQL 标准,并在 1986 年 10 月公布了最早的 SQL 标准。标准的出台使 SQL 作为标准的关系数据库语言的地位得到加强。扩展的标准版本是 1989 年发表的 SQL-89,之后还有 1992 年制定的版本 SQL-92 和 1999 年 ISO 发布的版本 SQL-99。 SQL 标准几经修改和完善,其功能更加强大,但目前很多数据库系统只支持 SQL-99 的部分特征, 而大部分数据库系统都能支持 1992 年制定的 SQL-92。 3.1.2 SQL 的特点 目前,SQL 语言已经成为几乎所有主流数据库管理系统的标准语言,所以其魅力是可想而知的。 SQL 语言不仅功能强大,而且容易掌握。下面是其最主要的 5 个特点。 1.具有综合统一性 SQL 语言格式统一,能够独立完成数据库系统使用过程中的数据录入、关系模式的定义、数据库 的建立,以及数据查询、插入、删除、更新、数据库重构与数据库安全性控制等一系列操作的要求, 为用户提供了开发数据库应用系统的良好环境。用户在数据库投入运行后,还可根据需要随时修改数 据模式,而不影响数据库的运行,使系统具有良好的可扩充性。 2.非过程化语言 SQL 语言与 C、COBOL、Basic 等语言不同,它不是一种完全的语言。SQL 语言并不能编写通用 的程序,因为它没有普通过程化语言中的 IF 和 FOR 等语句,只是一种操作数据库的语言,属于非过 程化语言。 3.语言简洁,用户容易接受 SQL 语言十分简洁,完成主要功能只需使用 9 个动词,如表 3.1 所示。虽然 SQL 只使用 9 个动词, 但其功能强大、设计精巧、语言语句简洁,使用户非常容易接受。 4.以一种语法结构提供两种使用方式 SQL 语言既是自含式语言,又是嵌入式语言,且在两种不同的使用方式下,SQL 语言的语法结构 基本上是一致的。作为自含式语言,能够独立地用于联机交互的使用方式,用户可以在终端键盘上直 第 3 章 SQL语言基础 ·25· 接输入 SQL 命令对数据库进行操作。作为嵌入式语言,SQL 语句能够嵌入到高级语言中,为程序员的 程序设计提供了方便。 表 3.1 SQL 的 9 个核心动词 SQL 功能 动 词 数据定义 DD CREATE、DROP、ALTER 数据查询 DQ SELECT 数据更新 DM INSERT、UPDATE、DELETE 数据控制 DC GRANT、REVOKE 5.面向集合的操作方式 非关系数据模型采用的是面向记录的操作方式,任何一个操作其对象都是一条记录。SQL 语言采 用集合操作方式,不仅查找结果可以是元组的集合,而且一次插入、删除、更新操作的对象也可以是 元组的集合。 3.1.3 SQL 的功能 虽然查询是 SQL 语言最主要的功能,但并不是其全部,SQL 还包含数据定义、数据操纵和数据控 制等功能。下面列出了 SQL 的 4 个主要功能及其解释。 1.数据定义 用 DDL 语言定义关系数据库的逻辑结构,即模式、内模式、外模式,可以实现对表、视图、索引 文件的定义、修改与删除等操作。 2.数据操作 用 DML 语言可实现数据查询与数据更新操作。数据查询包括对数据的查询、分类、排序、统计与 检索等操作,数据更新包括对数据的插入、删除与修改等操作。 3.数据控制 用 DCL 语言可控制数据的安全性、完整性与事物控制等内容。SQL 语言通过对数据库用户的授权 与收回授权语句来实现有关数据的存取控制,以确保数据库的安全性与完整性。 4.支持嵌入式 SQL 语句的使用 在几乎全部编程语言中都可以直接使用 SQL 语句操作数据库。 3.2 SQL 语言的组成 SQL 语言集数据定义语言 DDL(Data Definition Language)、数据查询语言 DQL(Data Query Language)、数据操纵语言 DML(Data Manipulation Language)和数据控制语言 DCL(Data Control Language)的功能于一体,可以完成数据库系统的所有操作。 SQL 技术与网络数据库开发详解 ·26· 1.数据定义语言——DDL 数据定义语言 DDL 用于创建、删除和管理数据库、数据表以及视图与索引。DDL 语句通常包括 对每个对象的创建(CREATE)、修改(ALTER)以及删除(DROP)等命令,表 3.2 中列出了 DDL 语言的主体语句及其功能。 表 3.2 DDL 的主体语句 操 作 对 象 语 句 功 能 CREATE TABLE 新建数据表 ALTER TABLE 修改数据表 表 DROP TABLE 删除数据表 CREATE VIEW 新建视图 视图 DROP VIEW 删除视图 CREATE INDEX 新建索引 索引 DROP INDEX 删除索引 CREATE SCHEMA 新建模式 模式 DROP SCHEMA 删除模式 CREATE DOMAIN 新建数据值域 ALTER DOMAIN 修改域定义 域 DROP DOMAIN 删除域 CREATE PROCEDURE 新建存储过程 存储过程 DROP PROCEDURE 删除存储过程 CREATE TRIGGER 新建触发器 触发器 DROP TRIGGER 删除触发器 2.数据查询语言——DQL 数据查询语言DQL用于查询检索数据库中的数据。该语言使用SELECT语句达到查询数据的目的。 使用 SELECT 语句除了可以简单的查询数据外,还可以排序数据、连接多个数据表、统计汇总数据等。 SELECT 语句由一系列必选或可选的子句组成,例如,FROM 子句、WHERE 子句、ORDER BY 子句、 GROUP BY 子句和 HAVING 子句等。 3.数据操纵语言——DML 数据操纵语言 DML 用于插入数据、修改数据和删除数据。该语言由 3 种不同的语句组成,分别是 INSERT、UPDATE 和 DELETE 语句。INSERT 语句用于向表插入数据,UPDATE 语句用于修改表中 的数据,而 DELETE 语句用于删除表中的数据。 4.数据控制语言——DCL 数据控制语言(DCL)是用来设置或者更改数据库用户或角色权限的语句,这些语句包括 GRANT、 DENY 和 REVOKE 等语句。其中,GRANT 用于授予用户访问权限,DENY 用于拒绝用户访问,而 REVOKE 用于解除用户访问权限。 第 3 章 SQL语言基础 ·27· 3.3 探索 SQL 环境 由于 SQL 语言的强大功能及其通用性,当前流行的所有数据库系统、大部分高级编程语言都支持 SQL 语言。 3.3.1 了解 SQL 执行环境 SQL 语言提供了两种不同的执行方式:一种是联机交互式执行,就是用户在某数据库系统的 SQL 执行工具中把 SQL 作为独立语言交互式执行,例如,在 SQL Server 的查询分析器中、Oracle 的 SQL*Plus 中等;另一种执行方式是将 SQL 语言融入到某中高级语言(例如,VB、VC、Delphi、Java 等)中使 用,这样便可利用高级语言的过程结构弥补 SQL 语言在实现复杂应用方面的不足。 1.联机交互式执行 几乎所有数据库系统中都有专门或可以执行 SQL 语句的工具,例如,Visual FoxPro 的命令窗口、 SQL Server 的查询分析器、Oracle 的 SQL*Plus 等。在这些工具中用户可以直接编写并执行 SQL 命令, 此时,数据库会马上给出相应执行结果,例如,本书第 2 章中介绍的执行 SQL 语句的方法。 2.嵌入式执行 在一些编程语言中,可以将 SQL 语句嵌入到程序中执行,例如,在 C 语言中嵌入 SQL 语句。在 这种方式下使用的 SQL 语句被称为嵌入式 SQL,而嵌入 SQL 的高级语言被称为主语言或宿主语言。 由于 SQL 语言是基于关系数据模型的语言,而高级语言基于整型数值、实型数值、字符、记录与数组 等数据类型,两者之间有很大的差别,因此必须做一些规定使得能在高级语言的程序中嵌入 SQL 语句。 SQL 语言和宿主语言之间通过设定公共变量来传递信息,这些公共变量先由宿主语言定义,再用 SQL 语言的 DECLEAR 语句声明后程序就可以引用这些变量了。嵌入式 SQL 有以下语法规定(假设宿 主语言为 C 语言): 为了能区分 SQL 语言语句与宿主语言语句,所有 SQL 语句都加前缀 EXEC SQL,以“;”为结 束标记。 在嵌入式 SQL 中可以引用宿主语言的程序变量,但所有变量必须在 SQL 语句 BEGIN DECLEAR SECTION 与 END DECLEAR SECTION 之间进行说明,并且在 SQL 语句中引用宿 主语言的程序变量时宿主语言的变量前加冒号(:);使用自身变量,则不需要加冒号。 处理多条记录时可以使用游标。游标的操作有:声明游标、打开游标、滚动游标并提取当前记 录值和关闭游标等。 3.3.2 了解 SQL 数据库的层次结构 SQL 语言支持关系数据库三级模式结构,其层次结构如图 3.1 所示。 所有基本表构成了数据库的模式。视图与部分基本表构成了数据库的外模式。数据库的存储文件 SQL 技术与网络数据库开发详解 ·28· 与其索引文件构成了关系数据库的内模式。 图 3.1 SQL 数据库层次结构 在 SQL 中,关系模式(对应模式)称为“基本表”,存储模式(对应内模式)称为“存储文件”, 子模式(外模式)称为“视图”,元组(或记录)称为“行”,属性(或字段)称为“字段”。 基本表是独立存在的,在 SQL 中一个关系对应一个表。一个或多个基本表对应一个存储文件,每 个表有若干索引,索引也存放在存储文件中。视图是从一个或多个基本表导出的虚拟表,视图本身不 独立存储在数据库中,数据库中只存储视图的定义而不存储对应的数据,视图对应的数据被存放在基 本表中。用户可以用 SQL 语句对视图和基本表进行查询等操作。存储文件的逻辑结构组成了关系数据 库的内模式,所以其物理结构是任意的,对用户是透明的。 3.3.3 在 SQL 环境中命名对象 在 SQL 环境中命名对象要遵循一定的规则,例如,对象名称不允许超过 128 个字符等。除此之外, SQL-99 标准中还制定了两类不同的命名规则,即正则标识符规则和定界标识符规则。其中,首选采用 的命名规则是正则标识符规则,其规定的约束如下。 标识符名(对象名)不区分英文字母的大小写,例如,id 和 ID 是相同的。 标识符只允许使用字母、数字和下划线(_),例如,stu_id、vw_computer_boy 等都是合法的 标识符。 不允许使用 SQL 保留的关键字。 说明:正则标识符规则中不区分大小写的原因是,所有名称在存储时都被 SQL 自动修改成大写。 命名对象时,除可以采用正则标识符规则外,也可以采用定界标识符规则,不过需要注意的是, 当选择了一种命名规则后就应该从始至终都坚持这一命名规则,而不是混合使用两种命名规则。下面 是定界标识符规则的约束。 标识符必须放在一组双引号中,例如“id”。 引号不会被存入数据库,而其他所有字符都按原样存入数据库。 名称区分大小写,例如“id”和“ID”是不同的。 允许使用大部分字符,其中还包括空格。 可以使用 SQL 保留的关键字。  数据类型  数据表基础  表逻辑设计  表的创建(CREATE TABLE)  表结构的修改(ALTER TABLE)  表的删除、截断与重命名  创建与删除数据库 数据表也被称为表或基本表,是数据库最基本的用于存储数据的对象。可以认为关 系数据库中的数据表是以行和列组成的二维表格,通常人们将行称为记录,将列称为 字段。 本章将主要介绍数据库中的数据类型、表结构、逻辑设计、表的创建语句、修改表 结构的语句和数据库的创建语句等。 数据表 第 章 SQL 技术与网络数据库开发详解 ·30· 4.1 数 据 类 型 在创建数据表时,需要用到数据类型。因此,在介绍创建表之前,本节将介绍一些 SQL 支持的数 据类型。 4.1.1 字符型数据 字符型数据是数据库中最常用的数据类型之一,有时人们将其成为字符串。例如,一个存储学生 信息的表中,学生姓名、来源地、所属院系等都是字符型数据。字符型数据可由以下几类符号组成。 字母:小写字母 a~z 与大写字母 A~Z 共 52 个。 数字:0~9 共 10 个。 空白符:空格符、制表符、换行符等统称为空白符。空白符只在字符常量和字符串常量中起作 用。在其他地方出现时,只起间隔作用,编译程序对它们忽略。因此在程序中使用空白符与否, 对程序的编译不发生影响,但在程序中适当的地方使用空白符将增加程序的清晰性和可读性。 标点、特殊字符与汉字:在数据表中允许存储标点、特殊字符与汉字。 在 SQL 语言中,字符型数据被放在单引号(' ')中,用于区别其他类型的数据。例如,' home '、 '张三'、' 047122813810 '、' 123_**^ ' 等都是字符型数据。每个字符型数据都有长度,其长度是该字符 型数据的字符个数,例如,' home '的长度为 4,' 047122813810 '的长度为 12 等。不过,需要注意的是, 每个汉字占两个字符的位置,例如,'张三'的长度是 4,而不是 2。 注意:虽然电话号码 047122813810 看起来是数字,但因为将其放在了单引号内,所以是字符型数据。 这里所说的单引号,必须是英文输入法状态下的单引号。 存放字符型数据的变量被称为字符型变量。在数据库中有一种特殊的字符型变量——字符型字段 变量。由于还没有真正接触到字段的概念,因此关于字段变量将在后面的章节中进行讲解。 4.1.2 数字型数据 数字型数据就是通常所说的数字,它可以由 0~9 之间的数字、正负符号与小数点(.)组成。例如, 100、23.234、-123、-58.42 等都是数字型数据。数字型数据不允许放在任何定界符之内。数字型数据 除了上述形式以外,有时也可以用浮点形式的科学记数法表示,例如,3.46E+03 等。在具体的数据库 系统中,数字型数据又被详细分为整数型数据、浮点型数据和货币型数据等。数字型数据与字符型数 据一样也有长度。例如,100 的长度为 3,23.234 的长度为 6(数字型数据长度包含小数点),-123 的 长度为 3 等。 4.1.3 日期时间数据 SQL 中还有一种日期时间数据。例如,2009-03-10 12:25:30、2010 年 09 月 15 日、01/JAN/2010、 第 4 章 数据表 ·31· 22:30:10 等。它们用来表示日期和时间。在不同的数据库系统中,日期时间数据的表示法有所不同。 在 Access 中,日期时间数据是被包含在井号(#)中的,例如#2009-03-10#。而 SQL Server 和 Oracle 中并没有专门区别日期时间数据的定界符,因为它们可以识别日期时间格式的字符型数据。 4.1.4 二进制数据 在计算机中所有数据都被保存为二进制数据,如前面介绍的字符型数据、数字型数据和日期时间 数据等,其实在计算机中都是以二进制数据的形式存放的。二进制数据还可以表示图形图像、视频动 画和其他类型的文件等。当前流行的所有数据库系统都支持二进制数据。 4.1.5 图片、声音、视频数据 数据库中可以存储图片、声音、视频等多媒体数据,但需要相应数据类型的支持。例如,Access 中提供了 OLE 对象类型,用于存放多媒体数据,在 SQL Server 2000 中提供了 IMAGE 数据类型,通常 用于存放图片等。 4.1.6 自定义数据类型 除了数据库系统提供的数据类型以外,用户还可以根据自己的需要自定义数据类型。SQL 中的 CREATE TYPE 就是用于自定义数据类型的语句。不过遗憾的是,并非全部的数据库系统都支持 CREATE TYPE 语句,例如,SQL Server 2000 中就不可以使用 CREATE TYPE 语句定义用户数据类型。 4.1.7 Access 中的数据类型 Access 中有 10 种数据类型,即字符、备注、数字、日期/时间、货币、自动编号、是/否、OLE 对 象、超链接和查询向导。下面分别对常用的前 7 种数据类型进行介绍。 1.字符类型 用于存储字符型数据。该类型允许的字符型数据的最大长度是 255。可以设置“字段大小”属性控 制可输入的最大字符长度。 Access 中有两种字符数据类型,即 VARCHAR 和 CHAR 类型。VARCHAR 数据类型用于存放可 变长度字符串,CHAR 数据类型用于存放固定长度的字符串。例如,如下两条语句: 姓名 VARCHAR(20) 和 姓名 CHAR(20) 都声明了“姓名”是一个字符类型的字段,其后括号内的 20 代表了该字段中能够输入的最大长度。 SQL 技术与网络数据库开发详解 ·32· 说明:VARCHAR 和 CHAR 的区别是:假设当“姓名”字段中最长的值为“孛尔吉济特”时,前者会 自动调整“姓名”字段的长度为 10(一个汉字占两个字节的位置),而后者则仍旧保持字段长 度为 20,在没有达到 20 长度的字段值后会自动添加空格,例如,因为姓名“张三”没有达到 20 长度,因此,在其后添加 16 个空格。 技巧:虽然 VARCHAR 能够自动调整字段长度,以此达到节省空间的目的,但是,在查询检索方面, 查询 CHAR 类型的数据会比查询 VARCHAR 类型的数据更快。因此,应当将经常查询的字符字 段设置为 CHAR 类型。当然,如果更注重节省空间的话,应当使用 VARCHAR 类型。 2.备注类型 因为字符类型的最大长度是 255,所以没办法存储更长的文字说明(例如,要存储一个人的简历)。 此时,便可以使用备注类型。该类型允许字段能够存储长达 64000 个字符的内容。但 Access 不能对备 注字段进行排序或索引。在备注字段中虽然可以搜索文本,但不如在有索引的字符字段中搜索得快。 Access 中的备注类型被称为 MEMO。例如,下面的语句声明了“简历”字段为备注类型的字段。 简历 MEMO 注意:不能给 MEMO 类型的数据设置长度。 3.数字类型 用于存储数字型数据。Access 中有 6 种数字数据类型。表 4.1 列出了这些数字数据类型及其说明。 表 4.1 Access 中的数字数据类型 数字数据类型 说 明 BYTE (字节型)整数,从 0~255 SMALLINT (整型)整数,大约为从-32000~32000 INTEGER(或 NUMBER) (长整型)整数,大约为从-2000000000~2000000000 MONEY(或 CURRENCY) (货币型)整数和 4 个小数位。正负 900000000000000 使用货币格式自动格式化 REAL (单精度型)浮点数字,大约为从 1.4E-45~3.4E+38 FLOAT (双精度型)浮点数字,大约为从 4.9E-324~1.8E+308 下面的语句声明了“基本工资”字段为货币类型。 基本工资 MONEY 或 基本工资 CURRENCY 4.日期/时间类型 用于存储日期值、时间值或日期时间混合值。 Access 中的日期/时间类型是 DATETIME,例如, 下面的语句将设置“出生日期”字段为日期/时间类型。 出生日期 DATETIME 这里需要注意的是,在向 DATETIME 类型的字段输入值时,必须遵循固定的日期/时间格式。 第 4 章 数据表 ·33· 表 4.2 中列出了可供选择的日期/时间类型与格式。 表 4.2 日期/时间类型与格式 类 型 格 式 常规日期 YYYY-MM-DD HH:MM:SS 长日期 YYYY 年 MM 月 DD 日 中日期 YY-MM-DD 短日期 YYYY-MM-DD 长时间 HH:MM:SS 中时间 下午 HH:MM 短时间 HH:MM 5.自动编号类型 此类型是一种特殊类型,每当向表格添加新记录时,Access 会自动插入唯一顺序或者随机编号, 即在自动编号字段中指定某一数值。自动编号一旦被指定,就会永久地与记录连接。如果删除了表格 中含有自动编号字段的一个记录后,Access 并不会为表格自动编号字段重新编号。当添加某一记录时, Access 不再使用已被删除的自动编号字段的数值,而是重新按递增的规律赋值。 例如,下面的语句将 设置“编号”字段为自动编号类型。 编号 COUNTER 6.是/否类型 针对于某一字段中只包含两个不同的可选值而设立的字段。例如,下面的语句将设置“婚否”字 段为是/否类型。 婚否 YESNO 7.OLE 对象类型 此类型的字段允许单独地“链接”或“嵌入”OLE 对象, OLE 对象字段最大可为 1GB,主要受 磁盘空间限制。下面的语句将设置“照片”字段为 OLE 对象类型。 照片 IMAGE 4.1.8 MySQL 中的数据类型 MySQL 支持多种数据类型,可以分为数字类型、字符类型、日期和时间类型和复合类型 4 大类。 1.数字类型 TINYINT 类型:可以存放-128~127 之间的所有正负整数。该类型的数据,在内存中占用 1 个字节的空间,即使用 8 位二进制数表示,其中的 1 位二进制数表示整数值的正负号,其他 7 位表示整数值的长度和大小。 TINYINT UNSIGNED 类型:无符号的 TINYINT 类型,可以存放 0~255 之间的所有整数。该 类型的数据也只占用内存中的 1 个字节。 SQL 技术与网络数据库开发详解 ·34· SMALLINT 类型:用于保存-32768~32767 之间的所有正负整数。该类型的数据,在内存中占 用 2 个字节的空间。 SMALLINT UNSIGNED 类型:无符号的 SMALLINT 类型,可以存放 0~65535 之间的所有整 数。该类型的数据,在内存中占用 2 个字节的空间。 MEDIUMINT 类型:用于存储-8388608~8388607 之间的所有正负整数。该类型的数据,在内 存中占用 3 个字节的空间。 MEDIUMINT UNSIGNED 类型:无符号的 MEDIUMINT 类型,用于存放 0~16777215 之间的 所有整数。该类型的数据,在内存中占用 3 个字节的空间。 INT 或 INTEGER 类型:用于存放-2147483648~2147483647 之间的所有正负整数。该类型的 数据,在内存中占用 4 个字节的空间。 INT UNSIGNED 或 INTEGER UNSIGNED 类型:无符号的 INT 或 INTEGER 类型,用于存 放 0~4294967295 之间的所有整数。该类型的数据,在内存中占用 4 个字节的空间。 BIGINT 类型:用于存放-9223372036854775808~9223372036854775807 之间的所有正负整数。 该类型的数据,在内存中占用 8 个字节的空间。 BIGINT UNSIGNED 类型:无符号的 BIGINT,可以存放 0~18446744073709551615 之间的所 有整数。该类型的数据,在内存中占用 8 个字节的空间。 FLOAT 类型:用于存放数据范围为-3.402823466E+38 ~-1.175494351E-38 , 0 , 1.175494351E-38~3.402823466E+38 之间的浮点数。该类型的数据,在内存中占用 4 个字节的 空间。 DOUBLE 或 DOUBLE PRECISION 或 REAL 类型:用于存放数据范围为 -1.7976931348623157E+308 ~-2.2250738585072014E-308 , 0 , 2.2250738585072014E-308 ~ 1.7976931348623157E+308 之间的浮点数。该类型的数据,在内存中占用 8 个字节的空间。 DECIMAL[(M,[D])] 或 NUMERIC(M,D)类型:由 M(整个数字的长度,包括小数点、小数点 左边的位数、小数点右边的位数,但不包括负号)和 D(小数点右边的位数)决定的数字数据 类型,M 默认为 10,D 默认为 0。 2.字符串类型 CHAR(M) [BINARY] 或 NCHAR(M) [BINARY]类型:用于保存定长的字符串,其中,M 表示 字符串的最大长度,其范围为 1~255,字符串中的每个字符占用 1 个字节的存储空间。默认 为 BINARY,则表示不分大小写字母。NCHAR 表示使用默认的字符集。当输入的字符串个数 小于 M,则数据库系统将以空格补足,但在取出来时末尾的空格将自动去掉。 [NATIONAL] VARCHAR(M) [BINARY]类型:用于存放变长的字符串,占用的存储空间范围 为 0~255 字节,M 的取值范围为 1~255。如果没有 BINARY 项,默认为 BINARY,则表示 不分大小写字母。当输入的字符串个数小于 M,则数据库系统将以空格补足,但在取出来时 末尾的空格将自动去掉。 TINYBLOB 类型:用于保存不超过 255 个字符的二进制字符串,所占用的存储空间范围为 0~ 255 字节。 TINYTEXT 类型:用于存储短文字符串,所占用的存储空间范围为 0~255 字节。 第 4 章 数据表 ·35· BLOB 类型:用于存储二进制的长文本数据,所占用的存储空间范围为 0~65535 字节。 TEXT 类型:用于存储长文本数据,所占用的存储空间范围为 0~65535 字节。 MEDIUMBLOB 类型:用于存储二进制形式的中等长度的长文本数据,所占用的存储空间范 围为 0~16777215 字节。 MEDIUMTEXT 类型:用于存储中等长度的长文本数据,所占用的存储空间范围为 0~16777215 字节。 LONGBLOB 类型:用于保存二进制形式的极大长度的长文本数据,所占用的存储空间范围为 0~4294967295 字节。 LONGTEXT 类型:用于保存极大长度的长文本数据,所占用的存储空间范围为 0~4294967295 字节。 3.日期与时间类型 DATE 类型:用于存储日期数据,日期数据的范围为 1000-01-01 至 9999-12-31。每个 DATE 类型的数据占用 3 字节的存储空间,其输入格式为“年-月-日(YYYY-MM-DD)”。 DATETIME 类型:用于存储混合日期和时间数据,日期和时间数据的范围为 1000-01-01 00:00:00 至 9999-12-31 23:59:59。每个 DATETIME 类型的数据占用 8 字节的存储空间,其输 入格式为“年-月-日 时-分-秒(YYYY-MM-DD HH:MM:SS)”。 TIME 类型:用于存储时间数据或持续时间的数据,时间数据的范围为-838:59:59 至 838:59:59。 每个 TIME 类型的数据占用 3 字节的存储空间,其输入格式为“时-分-秒(HH:MM:SS)”。 YEAR 类型:用于存储年份数据,年份的取值范围为 1901~2155。每个 YEAR 类型的数据占 用 1 字节的存储空间,其输入格式为“年(YYYY)”。 TIMESTAMP 类型:用于存储混合日期和时间值、时间戳,混合日期和时间数据、时间戳的范 围为 1970-01-01 00:00:00 - 2037 年的某个时候。每个 TIMESTAMP 类型的数据占用 8 字节的 存储空间,其输入格式为“年-月-日 时-分-秒(YYYY-MM-DD HH:MM:SS)”。 4.复合类型 ENUM('value1','value2',…)类型:用于存储从预先定义的字符集合中选取互斥的数据值,可 以有 65535 个不同的值。 SET('value1','value2',…)类型:用于存储从预先定义的字符集合中选取任意数目的值,最多 有 64 个成员。 4.1.9 SQL Server 中的数据类型 SQL Server 中的数据类型非常丰富,下面列出了常用的几种数据类型供读者参阅。 1.整数数据类型 整数数据类型是较常用的数据类型之一。 INT(INTEGER)数据类型:用于存放-2147483648~2147483647 之间的所有正负整数。该类 型的数据,在内存中占用 4 个字节。 SMALLINT 数据类型:用于存放-32768~32767 之间的所有正负整数。该类型的数据,在内存 SQL 技术与网络数据库开发详解 ·36· 中占用 2 个字节。 TINYINT 数据类型:用于存放 0~255 之间的所有整数。该类型的数据,在内存中占用 1 个 字节。 BIGINT 数据类型:用于存放-9223372036854775808~9223372036854775807 之间的所有正负 整数。该类型的数据,在内存中占用 8 个字节。 2.浮点数据类型 浮点数据类型也是比较常用的数据类型之一。该数据类型用于存放带有小数点的数值。 DECIMAL[ p [s] ]数据类型:用于存放浮点数据,其精度非常高,可以保留到浮点数据的最小 有效数字,但是也有一定限制,详细内容请参阅相关 SQL Server 的书籍。这里的 p 代表浮点 数的总位数,但是不包括小数点;s 代表小数点后的位数。 NUMERIC 数据类型:与 DECIMAL 数据类型基本相同,有关详细区别请参阅相关书籍。 REAL 数据类型:用于存放精度在 1~7 之间的浮点数。该类型数据的范围是-3.40E -38~3.40E +38。 FLOAT 数据类型:用于存放精度在 8~15 之间的浮点数。该类型的数据的范围是-1.79E -308~ 1.79E +308。 3.二进制数据类型 BINARY(n)数据类型:用于存放二进制数据。其中,n 表示数据的长度,取值范围为 1~8000。 VARBINARY(n)数据类型:与 BINARY 类型基本相同。不同的是该数据类型存放可变长度 二进制数据。 4.字符数据类型 可以说字符数据类型是所有数据类型中使用最多的数据类型。SQL Server 中有两种常用字符数据 类型,分别是 VARCHAR 和 CHAR 类型。VARCHAR 数据类型存放可变长度字符串,CHAR 数据类 型存放固定长度的字符串。例如,如下两条语句: 姓名 VARCHAR(20) 和 姓名 CHAR(20) 都声明了“姓名”是一个字符类型的字段,其后括号内的 20 代表该字段中能够输入的最大长度。 说明:VARCHAR 和 CHAR 的区别是,假设当“姓名”字段中最长的值为“孛尔吉济特”时,前者会 自动调整“姓名”字段的长度为 10(一个汉字占两个字节的位置),而后者则仍旧保持字段长 度为 20。在没有达到 20 长度的字段值后会自动添加空格,例如,因为姓名“张三”没有达到 20 长度,因此,在其后添加 16 个空格。 技巧:虽然 VARCHAR 能够自动调整字段长度,以此达到节省空间的目的,但是,在查询检索方面, 查询 CHAR 类型的数据会比查询 VARCHAR 类型的数据更快。因此,应当将经常查询的字符字 段设置为 CHAR 类型。当然,如果更注重节省空间的话,应当使用 VARCHAR 类型。 第 4 章 数据表 ·37· 5.文本和图形数据类型 SQL Server 中常用的文本和图形数据类型是 TEXT 和 IMAGE 类型。 TEXT 数据类型:用于存放大量的文本数据。 IMAGE 数据类型:用于存放大量的二进制数据,通常用来存储图像。 6.日期和时间数据类型 DATETIME 数据类型:用于存放日期时间数据,可以说是日期和时间的组合。其数据格式为 “YYYY-MM-DD HH:MM:SS”。该类型数据的日期时间范围是,公元 1753 年 1 月 1 日 0 时~ 公元 9999 年 12 月 31 日 23 时 59 分 59 秒,其精度为百分之三秒。 SMALLDATETIME 数据类型:与 DATETIME 数据类型相似,但是精度只能精确到分钟,其 日期时间范围是 1900 年 1 月 1 日~2079 年 6 月 6 日。 7.货币数据类型 MONEY 数据类型:实际上,该类型的数据是一种特殊的 DECIMAL 数据,它有 4 位小数。 该类型的范围是-922337203685477.5808 ~+922337203685477.5807,数据精度为万分之一货 币单位。 SMALLMONEY 数据类型类:与 MONEY 类型相似,但是其取值范围是-214748.3648~ +214748.3647。 4.1.10 Oracle 中的数据类型 Oracle 中有许多种数据类型,其基本类型可分为字符数据类型、数字数据类型、日期时间类型、 LOB、RAW 等 5 大类型。 1.字符数据类型 CHAR 类型:用于存放字符串数据,定义形式为 CHAR[(n)]。以 CHAR 类型存放的字符串中 的每个字符和符号占用一个字节的存储空间。n 表示所有字符所占的存储空间,n 的取值范围 为 1~2000,即最多可容纳 2000 个字符。若不指定 n 值,则系统默认值为 1。如果所输入的字 符串的字符个数小于 n,则系统自动在实际字符串后添加空格来填满设定好的空间,但在取出 来时末尾的空格将自动去掉。若输入的数据过长,将会截掉其超出部分。 VARCHAR2 类型:用于存放可变长的字符串,具体定义时指明最大长度 n,这种数据类型可 以放数字、字母以及 ASCII 码字符集(或者 EBCDIC 等数据库系统接受的字符集标准)中的 所有符号。如果数据长度没有达到最大值 n,Oracle 会根据数据大小自动调节字段长度,如果 数据前后有空格,Oracle 会自动将其删去。 LONG 型:用来存放可变长度的字符串数据,最多能存储占用 2GB 空间的文本数据。需要注 意的是在一个表中只能有一字段可以为 LONG 型,并且 LONG 类型的字段不能被定义为主键 或唯一约束,也不能使用 LONG 类型的字段建立索引,过程或存储过程不能接受 LONG 数据 类型的参数。 SQL 技术与网络数据库开发详解 ·38· 2.数字数据类型 NUMBER 类型用于存放可变长的数值,允许正负值和 0 值,格式为 NUMBER(P,S),其中,P 表 示数据的总长度,取值范围为 1~38,S 表示小数的位数,取值范围为-84~127 之间的数字。如 NUMBER (8,2),则这个字段的总长度是 8,可 以 有 2 位小数,如果数值超出了位数限制,多余的位数就会被截取。 例如,NUMBER (6,3),输入 45.12378,则保存到字段中的数值是 45.124;又例如,NUMBER (4,0), 输入 1565.316,真正保存的数据是 1565。 3.日期和时间类型 DATE 类型:用于存放日期和时间数据,该数据类型的范围是,公元前 4712 年 1 月 1 日~公 元 9999 年 12 月 31 日。 TIMESTAMP 类型:与 DATE 基本相同,但是可以返回当前的时区。 4.RAW 数据类型 此类数据类型主要用于存储二进制数据。 RAW 类型:用于存放基于字节的二进制数据,最多能存放 2000 个字节,没有默认大小,所 以在使用时要指定大小,可以建立索引。 LONG RAW 类型:用于存放可变长度的二进制数据,最多能存放 2GB,受的限制和 LONG 类 型一样。 5.LOB 数据类型 主要有 CLOB、BLOB 和 BFILE 3 种子类型。 CLOB 类型(CHARACTER LOB):用于存放大量字符数据,可以存放非结构化的 XML 文档。 BLOB 类型(BINARY LOB):可以存放较大的二进制对象,如图形、音视频剪辑等数据。 BFILE 类型(BINARY FILE):能够将二进制文件存放在数据库外部的操作系统文件中, BFILE 字段存储一个 BFILE 定位器,指向位于服务器文件系统上的二进制文件,支持的文件最大为 4GB。 4.2 数据表基础 数据表又被称为表。在关系型数据库系统中,一个关系就是一个表,表结构指的就是数据库的关 系模型。表是若干列(Column)和若干行(Row)的集合,每一行代表一个唯一的记录,每一列代表 一个字段。在确定表结构时首先要定义表的字段,即定义字段名、数据类型及其宽度,其次输入行 (记录)。 4.2.1 记录和字段 关系数据库中的数据表,其实很像人们生活中的二维表格,甚至有人会说它就是二维表格。数据 表由行和列组成,通常人们将行称为记录,而将列称为字段,如图 4.1 所示。 第 4 章 数据表 ·39· 图 4.1 数据表 每个字段中的数据必须具有相同的数据类型,且每个字段都有字段名,如图 4.1 中的“学号”、“姓 名”等就是字段名。关系数据库中规定,在同一个表内不能有重复的字段。实际上,表内也不应该有 重复的记录,只是多数数据库管理系统不会强制这点而已。 说明:有些专家认为数据表的行和列不应该称为记录和字段,而应当称为行(Row)和列(Column)。 4.2.2 表结构 一个非空数据表实际上由两部分组成,分别是表结构和其内的数据。可以认为表结构由表中所有 字段的字段信息组成,这些信息包括字段名、字段类型、字段大小和字段约束、表约束等信息。创建 一个数据表,其实就是在创建其表结构。因此,在创建表时必须告诉 DBMS,表包括哪些字段,每个 字段的数据类型和大小等。例如,观察下面创建表的 SQL 语句,就会发现这一点。 CREATE TABLE test ( 学号 char(4), 姓名 char(20), ); 该 SQL 语句创建一个有两个字段的数据表 Test,两个字段的字段名分别为“学号”和“姓名”, 数据类型都是字符型,长度分别为 4 和 20。 4.3 表逻辑设计 数据表的设计是数据库设计的主要部分。表逻辑设计的好坏将会影响数据库系统最终的运行效果、 数据安全以及完整性等问题。对于数据库系统开发人员来说,必须将表的逻辑结构设计得尽量完美, 因为开发人员与最终用户看待数据的方式不一样。表的逻辑结构设计必须满足用户的需求,能使用户 准确理解数据的本质和容易掌握,并且没有二义性。E-R 模型将帮助系统开发人员能很好地完成表逻 辑设计。 4.3.1 E-R 模型图 E-R 是 Entity-Relationship 的缩写,即实体-关系。E-R 模型是一种自上而下的数据库设计方法。一 SQL 技术与网络数据库开发详解 ·40· 个完整的数据库系统的 E-R 模型图是由若干局部 E-R 模型图组合而成的。 1.局部 E-R 模式设计 在 E-R 方法中将局部概念结构图称为局部 E-R 模型图。局部 E-R 模式的设计过程如图 4.2 所示。 图 4.2 局部 E-R 模式的设计过程 例如,学校的综合数据库中的教师管理部分与课程管理的局部 E-R 模型图,如图 4.3 与图 4.4 所示。 图 4.3 教师管理局部 E-R 图 图 4.4 课程管理局部 E-R 图 2.合并局部 E-R 模型图 合并方法有两种:一种是一次合并多个局部 E-R 模型图;另一种是逐步合并局部 E-R 模型图,如 图 4.5 与图 4.6 所示。由于一次合并法方法复杂而难度大,所以常用的合并法是逐步合并法。 无论采用哪种方法,合并局部 E-R 模型图的准则是先解决局部 E-R 模型图的冲突,合并成初步 E-R 模型图。其次是进行初步 E-R 模型图的优化与修改,而最终得到全局 E-R 模型图。 例如,合并教师管理与课程管理局部 E-R 模型图后,得到的 E-R 模型图如图 4.7 所示。 第 4 章 数据表 ·41· 图 4.5 一次合并 图 4.6 逐步合并 图 4.7 合并后的 E-R 图 4.3.2 规范化与范式 规范化是一种用来产生数据表集合的技术,通过规范化表将具有符合用户需求的属性。规范化通 常作为对表结构的一系列测试来决定其是否满足和符合给定范式。数据库逻辑结构设计产生的结果应 该满足规范化要求,以使关系模式的设计合理,达到冗余少和提高查询效率的目的,所以对数据库进 行规范化非常重要。对数据库的规范化先要确定规范化级别,然后按要求进行并且要达到这一级别。 一般情况下,规范化处理主要进行以下 3 个步骤: (1)确定数据依赖:通过数据依赖表示出数据项之间的关系。此项工作在需求分析阶段完成。 (2)定义键,并消除冗余的关系:此项工作在概要设计阶段完成。 (3)确定范式级别:规范化必须要达到范式级别。 范式,简称 NF(Normal Form),是满足一定条件的关系模式。范式是规范化确定的级别,数据 库设计的范式有多种,常用的有第一范式(1NF)、第二范式(2NF)和第三范式(3NF)。所有范式 都基于数据表中的字段之间的关系。 第一范式:若关系模式 P 的所有属性的值域中每个值都是不可再分解的值,则称 P 为第一范 式。第一范式是最低的规范化要求,数据表不能存在相同的记录,需设定一个关键字,并且要 求每个字段都不可再分解。 第二范式:若关系模式 P 是第一范式,P 的表以及每个非主键字段都可以由构成主键的全部字 段得到,则称 P 为第二范式。第二范式可以消除大量的冗余数据,并对数据表可以进行异常的 SQL 技术与网络数据库开发详解 ·42· 插入和删除。 第三范式:若关系模式 P 是第二范式,且每个非主属性都不传递依赖于 P 的候选键,则称 P 是第三范式。第三范式的关系不具有多义性,其属性值唯一,且每个非主属性必须依赖于整个 主键而不能依赖于其他关系中的属性。 4.4 表的创建(CREATE TABLE) SQL 语言中创建表将用 CREATE TABLE 语句来实现。CREATE TABLE 语句可以定义各种表的结 构、约束以及继承等内容。 4.4.1 使用 CREATE TABLE 语句创建表 CREATE TABLE 将在当前数据库创建一个新的、初始为空的数据表,该表将由发出此命令的用户 所有。下面是 CREATE TABLE 语句的基本语法格式。 CREATE TABLE <表名> ( <字段名 1> <数据类型> [NOT NULL] [DEFAULT <默认值>], [<字段名 2> <数据类型> [NOT NULL] [DEFAULT <默认值>], …… <字段名 n> <数据类型>…… ); 具体说明如下: NOT NULL:为可选项,如果在某字段后加上该项,则向表添加数据时,必须给该字段输入内 容,即不能为空。 DEFAULT<默认值>:为可选项,如果在某字段后加上该项,则向表添加数据时,如果不向该 字段添加数据,系统就会自动用默认值填充该字段。 下面通过一个例题介绍 CREATE TABLE 语句的使用法。 【例 4.1】创建一个 Student 表,设置其学号、姓名和性别 3 个字段不能为空,并且给性别字段指 定默认值为“男”。 运行环境为 Access 或 SQL Server 时,其创建语句如下所示。 CREATE TABLE student ( 学号 char(4) NOT NULL , 姓名 char(20) NOT NULL, 性别 char(2) NOT NULL DEFAULT '男', 出生日期 datetime, 来源地 varchar(50), 联系方式 1 char(12), 联系方式 2 char(12), 第 4 章 数据表 ·43· 所属院系 char(20) ); 运行环境为 Oracle 时,其创建语句如下所示。 CREATE TABLE student ( 学号 char(4) NOT NULL , 姓名 char(20) NOT NULL, 性别 char(2) NOT NULL, 出生日期 date, 来源地 varchar2(50), 联系方式 1 char(12), 联系方式 2 char(12), 所属院系 char(20) ); 注意:Oracle 的有些数据类型名称和 Access、SQL Server 的数据类型名称不同。例如,上面语句中的 日期时间型,在 Oracle 中是 date,而在 Access 和 SQL Server 中是 datetime;又例如,变长字符 型在 Oracle 中使用的是 varchar2,而 在 Access 和 SQL Server 中是 varchar。因此,在创建数据表 时应当注意所使用的数据库管理系统中的数据类型名称。 4.4.2 创建带有主键的表 在数据表中能够唯一识别记录的字段,都会被人们设置为主键,例如,“学号”字段。当某个字 段被设置为主键后,该字段中就不能再有重复值,也不能有空值,数据库管理系统将强制执行这一规 则,这就是主键约束。在创建数据表时,设置主键的方法有两种,下面通过例题介绍具体的方法。 【例 4.2】创建以“学号”字段作为主键的 Student 表。设 SQL 运行环境为 SQL Server。 方法一: CREATE TABLE student ( 学号 char(4) CONSTRAINT firstkey PRIMARY KEY NOT NULL , 姓名 char(20) NOT NULL, 性别 char(2) NOT NULL, 出生日期 datetime, 来源地 char(50), 联系方式 1 char(12), 联系方式 2 char(12), 所属院系 char(20) ); 方法二: CREATE TABLE student ( 学号 char(4) NOT NULL , 姓名 char(20) NOT NULL, 性别 char(2) NOT NULL, 出生日期 datetime, 来源地 char(50), SQL 技术与网络数据库开发详解 ·44· 联系方式 1 char(12), 联系方式 2 char(12), 所属院系 char(20), CONSTRAINT xh PRIMARY KEY(学号) ); 说明:在运行上面的语句时,可能会因为 student 表已经存在而失败。此时,应当先将现有的 student 表删除,然后再执行上面的创建语句。删除 student 表的 SQL 语句如下所示。 DROP TABLE student; 如果想设置多个字段为主键,则必须使用上例的方法二。例如,要创建一个存放学生多门成绩的 Score 表,此时,其主键应该是“学号”和“课号”两个字段的联合,因为只有学号和课号联合起来, 才能识别唯一记录。 【例 4.3】创建 Score 表,并设置“学号”和“课号”两个字段为联合主键。 CREATE TABLE score ( 学号 char(4) , 课号 char(3), 考试成绩 decimal(6 ,2), 平时成绩 decimal(6 ,2), CONSTRAINT xh_kh PRIMARY KEY(学号,课号) ); 注意:表中的主键必须设置为 NOT NULL。 4.5 表结构的修改(ALTER TABLE) 在数据库操作时,可能需要更改表结构,例如,修改某字段的数据类型、添加新字段、删除指定 字段等。ALTER TABLE 语句可以完成这些要求,本节将介绍该语句的详细用法。 4.5.1 ALTER TABLE 语句格式 使用 ALTER TABLE 语句可以修改字段的类型和长度,可以添加新字段,还可以删除不需要的字 段等。下面分别介绍使用 ALTER TABLE 修改字段、添加字段和删除字段的语法格式。 1.修改字段的语法格式 ALTER TABLE 表名 ALTER COLUMN 字段名 数据类型[(长度)]; 具体说明如下: 字段名:需要修改的字段名称。 数据类型:需要修改字段的新数据类型。 第 4 章 数据表 ·45· 长度:需要修改字段的长度。该项为可选项,当需要修改的字段类型为带长度的数据类型时必 须定义其长度,例如,字符类型。 注意:在 Oracle 中,修改字段的语法格式为: ALTER TABLE 表名 MODIFY 字段名 数据类型[(长度)]; 2.添加字段的语法格式 ALTER TABLE 表名 ADD 字段名 数据类型[(长度)]; 具体说明如下: 字段名:需要添加的字段名称。 数据类型:需要添加字段的数据类型。 长度:需要添加字段的长度。其余说明与上面的相同。 3.删除字段的语法格式 ALTER TABLE 表名 DROP COLUMN 字段名; 具体说明如下:字段名为要删除的字段名。 注意:使用 ALTER COLUMN 时要更改的字段不能是:数据类型为 text、image、ntext 或 timestamp 的 字段、表的 ROWGUIDCOL 字段、计算字段或用于计算字段中的字段、被复制字段、用在索引 中的字段。 4.5.2 增加新字段 前面介绍了使用 ALTER TABLE 语句增加新字段的语法格式,下面通过例题说明其具体用法。 【例 4.4】在 SQL Server 的 Student 表中,增加新字段“政治面貌”,该字段的类型为字符型,长 度为 10。其 SQL 语句如下所示。 ALTER TABLE student ADD 政治面貌 char(10) ; 运行上面的语句后,通过 SQL Server 的企业管理器查看 Student 表结构,如图 4.8 所示。从图中看 到“政治面貌”字段已经被添加到 Student 表内。 【例 4.5】在 Oracle 的 Student 表中,增加新字段“政治面貌”。 ALTER TABLE student ADD 政治面貌 char(10) ; SQL 技术与网络数据库开发详解 ·46· 使用下面的语句查看 Student 的表结构。 DESC student 说明:DESCRIBE 命令用于查看表定义。使用全称 DESCRIBE 或缩写 DESC 均可。 运行结果如图 4.9 所示。 图 4.8 添加“政治面貌”字段后的表结构 图 4.9 添加“政治面貌”字段后的表结构 观察上面的两个例题会发现,Oracle 和 SQL Server 的添加新字段的 SQL 语句是相同的,因此,本 书约定如果语句相同,则只用 SQL Server 举例。 注意:在 Oracle 中,必须在语句末尾添加分号(;),而在 SQL Server 中分号是可有可无的。 4.5.3 增加带有默认值的新字段 在使用 ALTER TABLE 语句添加新字段的同时,也可以给该字段设置默认值。 【例 4.6】在 Student 表中,添加新字段“婚否”的同时,给其设置默认值“否”。 第 4 章 数据表 ·47· ALTER TABLE student ADD 婚否 char(2) DEFAULT '否'; 运行上面的语句后,使用 SQL Server 的企业管理器查看表结构,如图 4.10 所示。 图 4.10 添加“婚否”字段后的表结构 4.5.4 修改字段的类型和宽度 ALTER TABLE 语句形式可以改变字段的数据类型和宽度。满足以下情况的字段是不可以更改其 数据类型的: 数据类型为 TEXT、IMAGE、NTEXT 或 TIMESTAMP 的字段; 有 UNIQUE 约束的字段。 设置默认值的字段。 重复的字段。 计算的或用在计算的字段中。 用于 CHECK 约束的字段。 【例 4.7】将 Student 表的字段“政治面貌”的数据类型改变为变长字符型,宽度为 6。 (1)如果运行环境为 SQL Server,则其语句如下所示。 ALTER TABLE Student ALTER COLUMN 政治面貌 varchar(6); 运行上面的语句后,使用 SQL Server 的企业管理器查看表结构,如图 4.11 所示。 (2)如果运行环境为 Oracle,则其语句如下所示。 ALTER TABLE Student MODIFY 政治面貌 varchar2(6); SQL 技术与网络数据库开发详解 ·48· 使用下面的语句查看 Student 的表结构。 DESC student 运行结果如图 4.12 所示。 图 4.11 修改“政治面貌”字段后的表结构 图 4.12 修改“政治面貌”字段后的表结构 注意:将字段的当前数据类型转换为另一种数据类型时,字段中当前已有的数据必须与新数据类型相 互兼容。 4.5.5 删除字段 SQL 语句为 ALTER TABLE 语句提供了 DROP COLUMN 子句来完成删除数据表中的字段。 【例 4.8】从 Student 表中删除字段“政治面貌”与“婚否”。 ALTER TABLE student DROP COLUMN 政治面貌; 第 4 章 数据表 ·49· 运行上面的语句后,使用 SQL Server 的企业管理器查看表结构,这时字段“政治面貌”已不存在, 如图 4.13 所示。 图 4.13 删除字段“政治面貌”后的表结构 4.5.6 删除带有默认值的字段 ALTER TABLE…DROP COLUMN 语句形式不能删除数据表中有主键约束和默认值的字段。 【例 4.9】使用 ALTER TABLE…DROP COLUMN 语句直接删除 Student 表的“婚否”字段。 ALTER TABLE student DROP COLUMN 婚否; 运行结果如图 4.14 所示。运行出错的原因是“婚否”字段被设置了默认值。 图 4.14 删除字段“婚否”的操作失败 注意:在图中的对象‘DF__student__婚否__05D8E0BE’是字段“婚否”的默认值名称。实际上,这 个名称并不是在例 4.7 中声明的,是系统自动为字段“婚否”的默认值生成的。 为了删除带有约束和默认值的字段,必须先删除约束和取消默认值,其语法格式如下所示。 ALTER TABLE 表名 DROP CONSTRAINT 约束名|默认值名 【例 4.10】使用 ALTER TABLE 语句删除 Student 表的“婚否”字段。 SQL 技术与网络数据库开发详解 ·50· (1)删除“婚否”字段的默认值。 ALTER TABLE student DROP CONSTRAINT DF__student__婚否__05D8E0BE (2)执行删除“婚否”字段的语句如下。 ALTER TABLE student DROP COLUMN 婚否; 运行结果如图 4.15 所示。 图 4.15 删除字段“婚否”的语句成功执行 4.5.7 更改主键 使用 ALTER TABLE…ADD 语句形式可以给数据表添加主键(PRIMARY KEY)约束。添加主键 约束的语法格式如下所示。 ALTER TABLE 表名 ADD CONSTRAINT 主键约束名 PRIMARY KEY (<字段名 1> [,<字段名 2>,…]) 说明:主键约束名由用户指定,PRIMARY KEY子句可以设置联合主键约束。 【例 4.11】为 Student 表中的“学号”字段添加主键约束。 ALTER TABLE student ADD CONSTRAINT xh_1 PRIMARY KEY(学号); 如果需要改变数据表当前的主键约束时,则应当先删除其当前的主键约束,然后再使用上述方法 添加新的主键约束,例如下面的例题所示。 【例 4.12】将上例中设置的 Student 表中的“学号”字段的主键约束改变为“学号”与“姓名”字 段的联合主键约束。 (1)删除当前主键约束。 ALTER TABLE student DROP CONSTRAINT xh_1; 第 4 章 数据表 ·51· (2)添加字段联合主键约束。 ALTER TABLE student ADD CONSTRAINT xh_xm PRIMARY KEY(学号,姓名) 注意:被设置主键约束的字段必须设置 NOT NULL 约束。 4.6 表的删除、截断与重命名 对表可进行删除与重命名操作,SQL 语言提供了 DROP TABLE 语句进行表删除操作,提供了 RENAME TABLE 语句进行表重命名操作。 4.6.1 删除表 当不再需要数据库中的某表时,就应当删除该表,释放该表所占有的资源。在 SQL 语言中,删除 数据表使用 DROP TABLE 语句。例如,下面的语句用于删除 Student 表。 DROP TABLE student; 说明:有时,在使用 DROP TABLE 语句删除数据表时会出现删除失败的情况。导致删除失败的绝大多 数原因是该表可能与数据库中的其他表存在联系。此时,应当先解除表之间的联系,然后再使 用 DROP TABLE 语句删除表。 4.6.2 截断表 使用 DROP TABLE 语句会将表彻底地删除掉,包括表内的数据和表本身。但有时,用户可能希望 只删除表中的数据,而不删除表本身。这时可以使用 TRUNCATE 语句将表截断,即删除其中的所有 数据。例如,下面的语句将截断 Student 表。 TRUNCATE TABLE student; 注意:使用 SQL 语言中的 DELETE 语句也能删除表中的所有数据,但是使用 TRUNCATE 语句会得到 更快的速度,而且在 Oracle 中,TRUNCATE 语句会重置表的存储空间。关于 DELETE 语句和 TRUNCATE 语句的具体内容与差别,可以查看本书第 15 章的内容。 4.6.3 重命名表 表的名称在创建时便被赋予了,但是,后期可能会因为各种原因需要重命名表。重命名表在 Oracle 中可以使用 RENAME 语句完成。在 SQL Server 中,则要使用 SP_RENAME 完成。例如,要将表 Student 重命名为 Stu_info,则在 Oracle 和 SQL Server 中使用的语句分别如下所示。 (1)在 Oracle 中重命名 Student 表。 RENAME student TO stu_info; SQL 技术与网络数据库开发详解 ·52· (2)在 SQL Server 中重命名 Student 表。 SP_RENAME student , stu_info 4.7 创建与删除数据库 在创建数据库对象之前,必须先创建数据库。数据库中包含数据表、视图、索引、查询、规则、 默认值等数据库对象,并且对这些对象进行同一管理。 4.7.1 创建数据库 在面向对象的关系型数据库管理系统中,一般情况下用户使用环境中的工具创建数据库,如 SQL Server 2000 中可以使用企业管理器新建一个数据库,其操作方法简单且方便。用户也可以使用 SQL 语 言中的 CREATE DATABASE 语句创建数据库,其基本语法格式如下所示。 CREATE DATABASE ; 【例 4.13】在 SQL Server 中,使用 CREATE DATABASE 语句创建一个 Test 数据库。 CREATE DATABASE test; 运行结果如图 4.16 所示。 图 4.16 创建 Test 数据库 启动企业管理器,从中可以查看创建数据库的结果,如图 4.17 所示。 图 4.17 创建数据库的结果 第 4 章 数据表 ·53· 4.7.2 删除数据库 删除数据库可以使用 DROP DATABASE 语句。其简单语法格式如下所示。 DROP DATABASE ; 【例 4.14】在 SQL Server 中,使用 DROP DATABASE 语句删除 Test 数据库。 DROP DATABASE test; 运行结果如图 4.18 所示。 图 4.18 删除 Test 数据库 通过企业管理器,查看删除数据库的结果,如图 4.19 所示。图中已经找不到 Test 数据库,这表明 数据库被成功地删除了。 图 4.19 删除数据库的结果 4.7.3 创建本书使用的数据表 本书多数实例使用了 College 数据库的 Student、Teacher、Course 和 Score 等数据表。下面列出创 建这些数据表的 SQL 语句,供读者参考。 1.创建 Student 表的 SQL 语句 (1)SQL Server 或 Access 环境。 CREATE TABLE Student SQL 技术与网络数据库开发详解 ·54· ( 学号 char(4) NOT NULL , 姓名 char(20) NOT NULL, 性别 char(2) NOT NULL, 出生日期 smalldatetime, 来源地 varchar(50), 联系方式 1 char(12), 联系方式 2 char(12), 所属院系 char(20), PRIMARY KEY(学号) ); (2)Oracle 环境。 CREATE TABLE Student ( 学号 char(4) NOT NULL , 姓名 char(20) NOT NULL, 性别 char(2) NOT NULL, 出生日期 date, 来源地 varchar2(50), 联系方式 1 char(12), 联系方式 2 char(12), 所属院系 char(20), PRIMARY KEY(学号) ); 2.创建 Teacher 表的 SQL 语句 (1)SQL Server 或 Access 环境。 CREATE TABLE Teacher ( 教工号 char(6) NOT NULL , 姓名 char(20) NOT NULL, 性别 char(2) NOT NULL, 年龄 integer, 职称 char(8), PRIMARY KEY(教工号) ); (2)Oracle 环境。 CREATE TABLE Teacher ( 教工号 char(6) NOT NULL , 姓名 char(20) NOT NULL, 性别 char(2) NOT NULL, 年龄 number, 职称 char(8), PRIMARY KEY(教工号) ); 第 4 章 数据表 ·55· 3.创建 Course 表的 SQL 语句 (1)SQL Server 或 Access 环境。 CREATE TABLE Course ( 课号 char(3) NOT NULL, 课名 char(30) NOT NULL, 类型 char(10) NOT NULL, 学分 integer NOT NULL, PRIMARY KEY(课号) ); (2)Oracle 环境。 CREATE TABLE Course ( 课号 char(3) NOT NULL, 课名 char(30) NOT NULL, 类型 char(10) NOT NULL, 学分 number NOT NULL, PRIMARY KEY(课号) ); 4.创建 Score 表的 SQL 语句 (1)SQL Server 或 Access 环境。 CREATE TABLE score ( 学号 char(4) REFERENCES student (学号) NOT NULL, 课号 char(3) REFERENCES course (课号) NOT NULL, 考试成绩 decimal(9 ,2), 平时成绩 decimal(9 ,2), ); (2)Oracle 环境。 CREATE TABLE score ( 学号 char(4) REFERENCES student (学号) NOT NULL, 课号 char(3) REFERENCES course (课号) NOT NULL, 考试成绩 number(9 ,2), 平时成绩 number(9 ,2), ); 说明:如果考虑国际标准化、书写方便等因素,在命名字段时应当尽量使用英文或英文缩写。本书为 了方便读者阅读,所以使用了汉字命名字段。  索引基础  索引的创建和使用  索引的删除 除表以外,索引可能就是大型数据库系统中最重要的对象了。索引是一种树型结构, 如果使用正确,可以减少定位和查询数据所需的 I/O 操作。另一种说法是,索引可以加 快表中查找数据记录的速度。 索引 第 章 SQL 技术与网络数据库开发详解 ·58· 5.1 索 引 基 础 索引是一种数据库对象。在有大量记录的数据表中查询数据时,如果使用索引可以很快查到想要 的数据。索引还有另外一种用途,那就是强制数据的唯一性。 5.1.1 使用索引的原因 对于大部分数据库用户来说索引是一个非常陌生的概念。因为普通用户很少特意去使用索引,只 有那些管理着海量数据的 DBA 才会去特意创建索引和使用索引。使用索引有两个主要的原因: 提供唯一的码值。 提高查询性能。 当用户创建带有 PRIMARY KEY 或 UNIQUE 约束的数据表时,SQL Server 或 Oracle 早已经在后 台为该表自动创建了唯一索引,并以此强制数据的唯一性。 使用索引能够提高性能的原因其实也很好理解。例如,要查询本书中关于 Oracle 的 DECODE 函数 的内容,可以使用两种方法。一种是从第 1 页开始一页一页地向后找;另一种是在目录中先找到 DECODE 函数所在的页数,然后,直接翻到该页上。可想而知,在书比较厚的情况下,采用第二种方 法很快就能找到需要的内容。这里的索引就像本书的目录,因此使用索引会提高查询性能。 当然,假设本书只有 3 页,则使用第一种方法会更实惠。这就表明数据表中的记录越多,使用索 引就会得到越大的效益。反之,使用索引就没有什么价值了。 5.1.2 索引的种类 SQL Server 和 Oracle 等大型数据库系统,按存储结构的不同将索引分为两类,即聚簇索引和非聚 簇索引。 1.聚簇索引 一个聚簇索引就是一个在物理上与表融合在一起的视图。表和视图共享相同的存储区域。聚簇索 引在物理上以索引顺序重新整理了数据的行。这种体系结构中的一个表只允许有一个聚簇索引。 在 SQL Server 中,删除和重建一个聚簇索引对于改造一个表来说是一个常用的技术,这是一种保 证数据页在磁盘上邻近的方法,同时,也是重建表中一些空闲空间的好方法。 SQL Server 的聚簇索引和 Oracle 的聚簇索引完全不同。Oracle 聚簇索引在一个 Oracle 块中同时存 储两个或多个表中的数据。在建立聚簇索引时,先创建一个聚簇,然后在该聚簇上创建一个索引,最 后在 CREATE TABLE 语句中指定该表存储在这个聚簇上。聚簇码通常是用来连接这两个或多个表的 连接字段。也就是说,如果用户需要使用两个表中的数据,那么只需要存取这一个 Oracle 块即可,而 不需要先访问一个表,然后再访问另一个表。在 SQL Server 中没有与 Oracle 相似的结构。 2.非聚簇索引 在非聚簇索引中,索引数据和表数据在物理上是分离的,表中的记录并不按照索引中的顺序存储。 第 5 章 索引 ·59· 非聚簇索引的查询效率相对于聚簇索引来说比较低,但由于一个数据表只能创建一个聚簇索引,所以 当用户需要使用多个索引时就只能创建非聚簇索引了。 5.2 索引的创建和使用 本节将介绍创建索引前应当注意的内容,以及创建索引的标准语法和 SQL Server、Oracle 中的扩 展语法。此外,还介绍了创建和使用非聚簇索引和唯一索引的内容。 5.2.1 创建索引前应当注意的内容 实际上,使用索引会提高查询性能这句话是有前提的,就是说并不是所有情况下使用索引都能提 高查询性能。所以,在创建并使用索引前应当注意下面的几点内容。 对于只有少量数据记录的表或在 Oracle 中占有小于 10 个 Oracle 块的表来说,使用索引查询数 据没有任何好处。应当省掉存取和使用索引块的开销,直接执行全表扫描得到表中的所有数据, 这样会更快一些。 如果索引字段中有很多不同的数据值和空值时,使用索引会极大地提高性能。 如果执行查询后,返回的数据记录很少,则索引可以优化该查询。比较好的情况是返回记录数 少于全部数据的 25%(根据 DBMS 的不同配置,该数字有所不同)。如果返回的数据记录很 多,则使用索引不会得到太多的好处。 索引可以提高查询数据的速度,但它也降低了数据的更新速度。因此,如果要进行大量的更新 操作,在执行更新操作前应该删除一些不必要的索引,在更新完毕后再重新创建索引,这样会 提高效率。 索引也会占用数据库空间,所以在设计数据库的可用空间时应当考虑索引所占用的空间。 在某字段上创建索引时,应当考虑是否经常使用该字段筛选记录。如果不是,则不应该创建索 引,因为该索引不会起什么作用,反而在修改数据时会影响性能。 尽量不要对经常需要更新或修改的字段创建索引,更新索引的开销会降低期望获得的性能。 尽量不要将索引与表存储在同一个驱动器上,分开存储会避免访问冲突,从而能够提高性能。 5.2.2 创建索引的 SQL 语句 在不同的 DBMS 中,创建索引通常都可以使用两种方式:一是 GUI 方式,二是 SQL 命令方式。 本小节要介绍的是使用 SQL 命令方式建立索引的方法。使用 SQL 语句创建索引的语法如下所示。 CREATE INDEX index_name ON table_name ( column [ ,...n ] ) 具体说明如下: index_name:索引的名称。在 SQL Server 中,索引的名称在表内必须唯一,但在数据库中不 必唯一。在 Oracle 中,索引的名称在用户内必须唯一。 SQL 技术与网络数据库开发详解 ·60· table_name:包含将要在其上创建索引的字段的表。 column:将要在其上创建索引的字段。这个位置可以放置多个字段,此时,创建的索引被称为 复合索引。 SQL Server 和 Oracle 对上面创建索引的语句有不同的扩展。例如,在 SQL Server 中,创建索引的 语法格式为: CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [ WITH { PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB } [ ,...n] ] [ ON filegroup ] 而在 Oracle 中,创建索引的语法格式为: CREATE [ UNIQUE ] INDEX index_name ON table_name ( column [ ASC | DESC ] [ ,...n ] ) [INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace_name] [STORAGE storage_clause] [PCTFREE integer] [NOSORT] [RECOVERABLE | UNRECOVERABLE] [PARALLEL parallel_clause] 关于 SQL Server 和 Oracle 中创建索引的详细语法说明,请读者参考这两个数据库管理系统的参考 手册,在此不作详细说明。 5.2.3 创建和使用非聚簇索引 在前面的内容中曾经提到,非聚簇索引的性能不如聚簇索引的性能好,但由于一个表只能创建一 个聚簇索引,因此还需要使用非聚簇索引。实际上,从大量的数据中查询数据时,使用非聚簇索引比 不使用索引要好得多。下面通过例题说明创建和使用非聚簇索引的方法,以及使用索引后查询性能的 改变。首先,创建例题中将要使用的 TestIndex 表,其创建语句如下所示。 CREATE TABLE testindex ( c1 char(1), c2 int ); 将上述语句,输入 SQL Server 的查询分析器中运行,其运行结果如图 5.1 所示。然后,使用下面 的语句向表 TestIndex,插入 100000(十万)条随机数据记录。 /*声明整数变量@x*/ DECLARE @x int /*给变量@x 赋初值 1*/ 第 5 章 索引 ·61· SELECT @x=1 /*循环十万次*/ WHILE @x<=100000 BEGIN /*向数据表 TestIndex 插入随机数*/ INSERT INTO testindex VALUES(CHAR(65+ROUND(RAND()*24,0)),ROUND(RAND()*100,0)) /*给变量@x 重新赋值*/ SELECT @x=@x+1 END 说明:SQL Server 中,在“/*”和“*/”之间的所有语句都是注释语句。也可以使用“--”注释语句。 运行结果如图 5.2 所示。 图 5.1 创建 TestIndex 表的运行结果 图 5.2 向 TestIndex 表插入 100000 条记录的运行结果 【例 5.1】比较在字段 c2 上创建非聚簇索引之前和创建索引之后的查询性能。 (1)在字段 c2 上创建非聚簇索引之前,运行下面的语句。 /*声明日期时间型变量@x*/ DECLARE @x datetime /*赋给变量@x 当前系统时间*/ SELECT @x=GETDATE() /*执行查询语句,查找 c2 等于 10 的所有数据记录*/ SELECT c2 FROM testindex WHERE c2=10 /*显示查询语句所花费的时间*/ SELECT GETDATE()-@x 运行结果如图 5.3 所示。 SQL 技术与网络数据库开发详解 ·62· (2)在字段 c2 上创建非聚簇索引,其创建语句如下所示。 CREATE INDEX idx_testindex_c2 ON testindex(c2) 运行结果如图 5.4 所示。 图 5.3 创建索引前查询花费的时间 图 5.4 创建非聚簇索引的运行结果 说明:图 5.3 的结果为多次运行结果中,查询花费时间最短的一次。 (3)再次运行下面的语句。 /*声明日期时间型变量@x*/ DECLARE @x datetime /*赋给变量@x 当前系统时间*/ SELECT @x=GETDATE() /*执行查询语句,查找 c2 等于 10 的所有数据记录*/ SELECT c2 FROM testindex WHERE c2=10 /*显示查询语句所花费的时间*/ SELECT GETDATE()-@x 运行结果如图 5.5 所示。 图 5.5 创建索引后查询花费的时间 比较图 5.3 和图 5.5 中的结果可以发现,本例中使用了非聚簇索引后,大大提高了查询性能。 5.2.4 创建和使用唯一索引 前面提到过,使用索引的主要原因之一就是提供唯一的字段值。唯一索引强制表中任意两条记录 的索引值互不相同。创建唯一索引需要使用 UNIQUE 关键字。下面通过例题说明创建和使用唯一索引 的方法。首先,创建例题中将要使用的 TestUni 表,其创建语句如下所示。 CREATE TABLE testuni ( 第 5 章 索引 ·63· c1 int, c2 int ); 【例 5.2】在 TestUni 表的 c1 字段上创建一个唯一索引,并试验其效果。 (1)创建唯一索引 idx_testuni_c1。 CREATE UNIQUE INDEX idx_testuni_c1 ON testuni(c1) 运行结果如下所示。 命令已成功完成。 (2)执行如下插入语句,向表 TestUni 插入两条记录。 INSERT INTO testuni VALUES (10,20); INSERT INTO testuni VALUES (20,20) 运行结果如下所示。 (所影响的行数为 1 行) (所影响的行数为 1 行) (3)执行下面的插入语句,准备向表 TestUni 插入一条记录。 INSERT INTO testuni VALUES (10,50); 运行结果如下所示。 服务器: 消息 2601,级别 14,状态 3,行 1 不能在具有唯一索引 'idx_testuni_c1' 的对象 'testuni' 中插入重复键的行。 语句已终止。 通过本例可以知道,当向有唯一索引的表中字段(C1)插入非唯一值时,便会出现错误,这就很 好地保持了数据完整性。 5.3 索引的删除 索引是一把双刃剑,虽然它提高了查询速度,但也降低了更新数据的速度,因为每当更新数据时, 都要维护一次索引。因此,当不再使用索引或者要向表插入大量数据时,应当删除索引。在 SQL 中, 删除索引的语法如下所示。 DROP INDEX index_name 【例 5.3】删除 TestUni 表的唯一索引 idx_testuni_c1。 DROP INDEX testuni.idx_testuni_c1 SQL 技术与网络数据库开发详解 ·64· 删除了唯一索引后再向表 TestUni 插入前面的数据。 INSERT INTO testuni VALUES (10,50); 运行下面的语句,查看 TestUni 表的内容。 SELECT * FROM testuni 运行结果如图 5.6 所示。 图 5.6 TestUni 表内容 从运行结果可以看出,删除了唯一索引后,就可以向表中过去的索引字段(C1)中插入相同值了。  SELECT 语句的组成结构  查询数据  排序数据 查询数据是数据库操作中最重要的操作之一,具体实现查询操作要使用 SQL 语言 中的 SELECT 语句。从本章开始将详细介绍 SELECT 语句的用法。 查询数据——SELECT 语句 第 章 SQL 技术与网络数据库开发详解 ·66· 6.1 SELECT 语句的组成结构 一条 SELECT 语句可以很简单,也可以很复杂。一个较复杂的查询操作可以使用多种方法完成, 即 SELECT 语句的编写方法也是灵活多样的,就像一道数学题有多种解法一样,所以 SELECT 语句没 有绝对的固定格式。 6.1.1 最基本的语法格式 SQL 语言中的 SELECT 查询语句用来从数据表中查询数据,其完整的语法格式由一系列的可选子 句组成。下面首先介绍 SELECT 语句最基本的语法格式。 SELECT * FROM table_source 具体说明如下: SELECT 关键字后的“*”,代表查询数据表中的所有(字段)的内容。在这个位置也可以指 定要查询的字段名列表。 FROM 关键字后的 table_source,指明要从哪个表查询数据。 所有 SELECT 语句必须有 SELECT 子句和 FROM 子句,书写时可以将两个子句写在一行中。 【例 6.1】查询 Student 数据表中的所有内容。 SELECT * FROM student 在 SQL Server 的查询分析器中输入上面的语句并执行后,其结果如图 6.1 所示。 图 6.1 例 6.1 运行结果 注意:在使用 SQL Server 的查询分析器时,首先要记得从工具栏的数据库下拉列表框中选择正确的数 据库。上面的例题中应当选择 College 数据库。 第 6 章 查询数据——SELECT 语句 ·67· 说明:如果在 Oracle 环境中运行 SELECT 语句,则应当在语句的末尾加上分号(;)。Oracle 要求在所 有命令后都加分号。 6.1.2 带有主要子句的语法格式 前面介绍了 SELECT 语句最基本的语法格式,实际上 SELECT 语句的完整语法格式要比其复杂得 多。下面将经常用到的主要子句的语法格式归纳如下。 SELECT [DISTINCT | ALL] select_list FROM table_source [WHERE search_condition ] [GROUP BY group_by_expression ] [HAVING search_condition ] [ORDER BY order_expression [ ASC | DESC ] ] 具体说明如下: SELECT 子句:必选子句。可选关键字 DISTINCT 用于去除查询结果集中的重复值所在的记录; 关键字 ALL 用于返回查询结果集中的全部记录,它是默认的关键字,即当没有任何关键字时 返回全部记录。select_list 为星号(*),或者用逗号分隔的字段名列表,或者引用字段名的表 达式,或者其他表达式(常量或函数)。该子句决定了结果集中应该有什么字段。 FROM 子句:必选子句。其中 table_source 可以是一个基本表名称,或者一个视图名称,或者 为用逗号分隔的基本表名称列表,或者为视图名列表,或者为基本表名和视图名混合列表。该 子句决定了要从哪个(哪些)数据源查询数据。 WHERE 子句:可选子句。其中 search_condition 为条件表达式。该子句用于指定查询条件, DBMS 将满足条件的行显示出来(或者添加到结果集中)。 GROUP BY 子句:可选子句。其中 group_by_expression 为一个字段名,或者用逗号分隔的字 段名列表。该子句用于按 group_by_expression 分组(分类)查询到的数据。 ORDER BY 子句:可选子句。该子句用于按 order_expression 排序查询结果。如果其后有 ASC (默认值),则按升序排序结果;如果其后有 DESC,则按降序排序结果。如果没有该子句, 查询结果将以添加记录时的顺序显示。 注意:如果 SELECT 语句中有 ORDER BY 子句,则必须将其放在所有子句的后面。 6.1.3 SELECT 各子句的执行顺序 当 SELECT 语句被 DBMS 执行时,其子句会按照固定的先后顺序执行,了解这一顺序对学习本书 后面的内容会有一定的帮助。假设 SELECT 语句带有所有的子句,则其执行顺序为: (1)FROM 子句。 (2)WHERE 子句。 (3)GROUP BY 子句。 (4)HAVING 子句。 SQL 技术与网络数据库开发详解 ·68· (5)SELECT 子句。 (6)ORDER BY 子句。 了解了各子句的执行顺序后,来看一下它们的基本工作原理。SELECT 语句的各子句中 FROM 子 句是首先被执行的,通过 FROM 子句获得一个虚拟表,然后通过 WHERE 子句从刚才的虚拟表中获取 满足条件的记录,生成新的虚拟表。将新虚拟表中的记录通过 GROUP BY 子句分组后得到更新的虚拟 表,而后 HAVING 子句在最新的虚拟表中筛选出满足条件的记录组成另一个虚拟表。从上一步得到的 虚拟表中,SELECT 子句根据 select_list 将指定的列提取出来组成更新的虚拟表,最后 ORDER BY 子 句对其进行排序得出最终的虚拟表。通常,人们将最终的虚拟表称为查询结果集。 6.1.4 关于 SELECT 语句的一些说明 编写 SELECT 语句比较自由,因为它没有太多固定格式的要求,但还是需要对编写语句进行一些 说明。下面列出编写 SELECT 语句时需要了解的一些说明。 SELECT 语句与其他 SQL 语句一样,不区分大小写,例如,select 和 SELECT 是相同的。大多 数开发人员遵循一种规则——对 SQL 关键字全部使用大写,而对字段名和表名全部使用小写 (假设字段名和表名为英文单词),这样会使得代码更易于阅读和维护。 注意:虽然 SQL 关键字不区分大小写,但是字段名、表名或者值可能需要区分大小写,这由具体的 DBMS 或者具体的操作系统要求决定。 SQL 语句可以在一行上写出全部语句内容,也可以分成多行编写,SELECT 语句也是如此。许 多开发人员认为将 SQL 语句分成多行更容易阅读和调试。 多数 DBMS 不需要在单条 SQL 语句后加分号,但是有些 DBMS 要求必须加分号,例如 Oracle 数据库系统。 注意:Sybase Adaptive Server 不允许 SQL 语句以分号结束。 6.2 查 询 数 据 本节将介绍使用 SELECT 的基本格式查询基本表中的单字段、多字段和全部字段的方法,此外, 还有使用 DISTINCT 关键字去除重复信息,以及根据当前字段值计算出新字段值和命名新字段的方法。 6.2.1 查询单字段的方法 有时用户只希望查询表中某一个字段的内容,而不是全部,这时可以使用 SELECT 语句。下面通 过一个例题说明使用 SELECT 语句查询单字段的方法。 【例 6.2】查询 Student 表中有哪些院系的学生。 本题只要查询出 Student 表中“所属院系”字段的值,即可知道有哪些院系的学生,这就应当使用 单字段 SELECT 语句,如下所示。 第 6 章 查询数据——SELECT 语句 ·69· SELECT 所属院系 FROM student 运行结果如图 6.2 所示。 通过本例引出查询单字段的语法格式,其语法格式如下所示。 SELECT 字段名 FROM table_source 上例的运行结果,其实并不很理想,因为有很多的重复值影响了查看效果。 6.2.2 去除重复信息——DISTINCT 查询中如果有过多的重复值会影响查看效果,下面介绍去掉重复值的方法。去除重复值,需要使 用 DISTINCT 关键字。 【例 6.3】将例题 6.2 运行结果中的重复值去掉。 SELECT DISTINCT 所属院系 FROM student 运行结果如图 6.3 所示。本例题在“所属院系”字段名前加了 DISTINCT 关键字后,便去除了该 字段的重复值。如果只想查看某字段的不同值,则应当在该字段名前加上 DISTINCT 关键字。 图 6.2 查询“所属院系”的结果 图 6.3 查询中去掉重复值的结果 DISTINCT 关键字不仅可以去除重复值,也有排序数据的功能。例如,图 6.3 中的查询结果就是按 “所属院系”字段升序排列的。但是,DISTINCT 关键字的排序功能是不可靠的,因此,如果需要排 序查询结果,则应当使用 ORDER BY 子句,明确指出排序的根据和方式。 注意:使用 DISTINCT 关键字会使查询效率下降,因此尽量避免使用它,在需要去除重复信息时可以 使用 GROUP BY 子句。关于 GROUP BY 子句的详细内容请参看本书后面章节的内容。 使用 DISTINCT 关键字会使查询效率下降的原因是:在去除重复值之前,首先要对查询结果集进 行排序操作,将相同值的记录放在一起分为很多组,然后再删除每组第一条记录以外的其他记录,以 此达到去掉重复值的目的。在此,排序操作是降低效率的主要原因。 6.2.3 查询多字段 在实际应用中,除了查询单字段数据以外,更需要查询的是多字段数据。例如,要查询学号、姓 SQL 技术与网络数据库开发详解 ·70· 名和所属院系 3 个字段的数据等。查询多字段数据的语法格式如下所示。 SELECT 字段名 1, 字段名 2, 字段名 3…… FROM table_source 在 SELECT 关键字后列出需要查询的所有字段的名称,并使用逗号(,)将这些字段名隔开。字段 名列表中字段名的顺序可以和表中字段名的顺序不一致。 注意:字段名之间的逗号(,)必须是英文输入法下的逗号。在 SQL 语句中使用的其他符号也都必须是 英文输入法状态下的符号。 【例 6.4】查询 Student 表中所有学生的“姓名”、“性别”和“来源地”3 个字段。 SELECT 姓名, 性别, 来源地 FROM student 运行结果如图 6.4 所示。 图 6.4 例 6.4 查询结果 SELECT 子句中的字段名列表决定了查询结果集中要包含哪些字段,其顺序决定了查询结果集中 的字段顺序。 上例只查询了 Student 表中 3 个指定字段,如果想要查询该表中所有字段,难道需要列出表中所有 字段名吗? 6.2.4 查询所有字段 查询表中所有字段时,在 SELECT 子句中使用通配符——星号(*)代替字段名列表即可。其语法 格式如下。 SELECT * FROM table_source 【例 6.5】查询 Score 表中所有字段的数据。 SELECT * FROM score 运行结果如图 6.5 所示。 第 6 章 查询数据——SELECT 语句 ·71· 图 6.5 例 6.5 查询结果 说明:一般,除非确实需要表中的每个列,否则尽量避免使用星号(*)通配符。因为查询太多的字段 通常会降低查询和应用程序的性能。本书中多处使用星号(*)是为了让读者清楚地查看表中的 所有数据,以便更好地理解各种语句。 6.2.5 根据现有字段值计算新字段值 有时表中没有存储用户需要的数据,但这些数据又可以通过对现有数据的计算获得。例如,score 表中没有课程的总成绩,但是可以通过“考试成绩”和“平时成绩”两个字段计算得出总成绩。获得 总成绩的公式如下。 总成绩=考试成绩*0.7+平时成绩*0.3 说明:在表达式中,使用星号(*)代表数学表达式中的乘号(×);使用斜杠(/)代表数学表达式 中的除号(÷)。 【例 6.6】查询每个学生每门课程的总成绩。 SELECT 学号,课号, 考试成绩*0.7+平时成绩*0.3 FROM score 运行结果如图 6.6 所示。 从图中会看到一个奇怪的现象——第三列没有字段名,这是因为第三列是通过计算得出的新列, 而并非是表原有的列,所以没有字段名。没有字段名的列会给用户带来很多不便,如无法引用该列等。 通过本例还应该知道,SELECT 子句中除了可以放置数据表原有的字段外,还可以放置表达式, 后面还会学习在字段列表中放置常量。 SQL 技术与网络数据库开发详解 ·72· 图 6.6 例 6.6 查询结果 6.2.6 命名新列(别名)——AS 例 6.6 中计算得出的第三列没有字段名,如果不做相应处理会给以后的使用带来很多麻烦。所以本 小节将介绍如何命名新得到的列。下面通过一个例题说明命名新列的方法。 【例 6.7】查询每个学生每门课程的总成绩。 SELECT 学号,课号, 考试成绩*0.7+平时成绩*0.3 AS 总成绩 FROM score 运行结果如图 6.7 所示。 从运行结果中可以看到,新列有了字段名。在 SQL Server 中命名新列时可以使用 AS 关键字。上 面语句中的 AS 后的字符串“总成绩”就是新列的字段名。关键字 AS 不仅可以命名新列,而且还可以 给现有字段取别名。 【例 6.8】查询 Student 表中所有学生的“姓名”、“性别”和“来源地”3 个字段,并将结果集 中的“姓名”字段改为“学生姓名”。 SELECT 姓名 AS 学生姓名, 性别, 来源地 FROM student 运行结果如图 6.8 所示。本例中,使用 AS 关键字将现有字段“姓名”取别名为“学生姓名”,这 种设置别名操作不会改变 Student 表中原来的字段名,它只对查询结果集有作用。 设置别名时需要注意的一点是,如果别名是以数字或者特殊符号开头,例如以等号(=)开头,则 应当将别名放入双引号中。 说明:Oracle 不使用 AS 关键字设置别名。在 Oracle 中设置别名时不使用 AS,而是简单地将别名使用 空格与字段名分开。例如,在 Oracle 中例 6.8 的 SELECT 语句应当被编写为如下形式。 SELECT 姓名 学生姓名, 性别, 来源地 FROM student 第 6 章 查询数据——SELECT 语句 ·73· 实际上,在 SQL Server 中给字段取别名时也可以省略 AS 关键字。 图 6.7 例 6.7 查询结果 图 6.8 例 6.8 查询结果 6.2.7 将查询结果保存为新表 有时为了以后使用方便,需要将查询结果保存起来。例如,为了以后方便查询总成绩,可以将前 面例题 6.7 的查询结果保存起来。但需要注意的是,在 SQL Server 和 Oracle 中将查询结果保存为新表 的语法格式不一样。 下面是 SQL Server 的语法格式,在 SELECT 子句的后面、FROM 子句的前面加了一个“INTO” 关键字,关键字的后面紧跟用于保存查询结果的新表的名字。 SELECT * (或字段列表) INTO 新表名 FROM table_source …… 说明:Access 中将查询结果保存为新表的语法格式也是如此。 下面是 Oracle 的语法格式,在 SELECT 语句前加上 CREATE TABLE 语句用于创建新表。这种语 法格式是 SQL 标准的语法格式。 CREATE TABLE 新表名 AS SELECT *(或字段列表) FROM table_source …… 下面看一道具体的例题,并假设运行环境为 SQL Server。 【例 6.9】从 Score 表中,查询每个学生每门课程的总成绩,并将查询结果保存为 TotalScore 表。 SQL 技术与网络数据库开发详解 ·74· SELECT 学号,课号, 考试成绩*0.7+平时成绩*0.3 AS 总成绩 INTO totalscore FROM score 该语句运行后会出现类似下面的提示文字。 (所影响的行数为 54 行) 表示查询结果已经被保存到了 TotalScore 表中。使用下面的查询语句可以查看 TotalScore 表中的 内容。 SELECT * FROM totalscore 运行结果如图 6.9 所示。 图 6.9 例 6.9 查询结果 该例题中的 SELECT 子句中使用了‘INTO’关键字,将查询结果保存到了指定的 TotalScore 表中, 使用这种方法也可以对表进行备份。 注意:将查询结果保存为表时应当考虑到修改、添加和删除等问题。例如,当修改了某学生 Score 表 中的“考试成绩”字段的值时,还应当修改 TotalScore 表中的总成绩等。 6.2.8 连接字段 在数据库应用中,有时需要将多个字段连接(拼接)为一个字段(单值)。例如,报表中只有一 个位置,而又希望将多个字段的信息显示出来,此时便需要连接字段。下面通过一个例题介绍将多个 字段连接为一个字段的方法。 第 6 章 查询数据——SELECT 语句 ·75· 【例 6.10】从 Student 表中查询所有学生的姓名和来源地,并且将这两个字段连接为一个字段。 SELECT 姓名+来源地 FROM student 运行结果如图 6.10 所示。 从图中可以看出,两个字段通过加号(+)已经连接成一个字段了。但是,有几个问题需要解决。 (1)需要给新字段设置字段名。 (2)姓名和来源地之间的距离太大,应当缩小距离。 (3)应当将来源地放进括号内,与姓名隔开。 第一个问题可以使用 AS 关键字解决,例如使用下面的语句。 SELECT 姓名+来源地 AS 姓名及来源地 FROM student 运行结果如图 6.11 所示。第二个问题由字段值尾随的空格引起,所以需要使用 RTRIM 函数去除 字段值右侧的空格,例如使用下面的语句。 图 6.10 连接字段后的结果 图 6.11 给连接字段设置了别名后的结果 SELECT RTRIM(姓名)+RTRIM(来源地) AS 姓名及来源地 FROM student 运行结果如图 6.12 所示。 第三个问题的解决方法为将括号当作字符串连接进去,例如使用下面的语句。 SELECT RTRIM(姓名)+'('+RTRIM(来源地)+')' AS 姓名及来源地 FROM student 运行结果如图 6.13 所示。上面使用加号(+),将多个字段和常量(字符串括号为常量)连接到 了一起。实际上有些 DBMS 连接字段或常量时不是使用加号,而是使用两个竖杠(||)。 图 6.12 去除空格后的结果 图 6.13 加入括号后的结果 SQL 技术与网络数据库开发详解 ·76· 说明:Access、SQL Server 和 Sybase 使用“+”,DB2、Oracle、PostgreSQL 和 Sybase 使用“||” 连接 字段。MySQL 既不用“+”,也不用“||”,而是使用 CONCAT 函数连接字段。例如在 MySQL 中实现上面的例题时,SELECT 语句应当写为: SELECT CONCAT(RTRIM(姓名) , '(' , RTRIM(来源地) ,')' ) AS 姓名及来源地 FROM student 6.3 排 序 数 据 在数据库应用中,为了方便查看,有时需要将查询结果按某种规律排序。例如,按出生日期排序 查询结果,以便查看学生大小;按总成绩排序查询结果,以便得到学生名次等。下面介绍怎样在 SELECT 语句中使用 ORDER BY 子句排序查询结果。 6.3.1 按单字段排序 在 SQL 语言中,ORDER BY 子句用来排序数据。下面从单字段排序介绍排序的方法。单字段排序 即按某一个字段排序查询结果。例如,按学号排序、按出生日期排序等。 【例 6.11】从 Student 表中查询所有学生的学号、姓名、来源地和出生日期,并按出生日期排序 结果。 SELECT 学号,姓名,来源地,出生日期 FROM student ORDER BY 出生日期 运行结果如图 6.14 所示。 图 6.14 例 6.11 查询结果 观察图 6.14,查询结果已经按出生日期排序,而且是按照从小到大的顺序排列。按单字段排序时, 需要用哪个字段排序就将其字段名写在 ORDER BY 子句后即可。ORDER BY 子句后的字段名,可以 不在 SELECT 子句的字段名列表中,例如下面的语句。 SELECT 姓名,出生日期 FROM student ORDER BY 来源地 该语句将查询结果集按“来源地”字段进行了排序,而“来源地”字段并不在字段名列表中。 第 6 章 查询数据——SELECT 语句 ·77· 6.3.2 设置排序方向 排序数据有两种方式:第一种是将数据按从小到大的顺序排列,这叫升序;第二种是将数据按从 大到小的顺序排列,这叫降序。在 ORDER BY 子句中使用 ASC 关键字指定升序,使用 DESC 关键字 指定降序。如果没有使用关键字,则默认排序方式是升序。 【例 6.12】从 Course 表中查询所有内容。要求将查询结果按照学分降序排序。 SELECT * FROM course ORDER BY 学分 DESC 运行结果如图 6.15 所示。 图 6.15 例 6.12 查询结果 观察图 6.15,结果集已经按学分降序排序。如果想得到降序排序,则应当在 ORDER BY 子句中的 字段名后加上关键字 DESC。 在此需要说明一点,如果排序字段中有 NULL 值,则 NULL 值为最小值,当升序排序时它会在最 前面,而降序排序时它会在最后面。不过,这是对 SQL Server 而言的,如果是 Oracle 系统则正好与此 相反。Oracle 在升序排序时将 NULL 值放在最后,而在降序时则放在最前。 6.3.3 按多字段排序 有时按单字段排序不能满足人们的需求,原因是单字段排序不能解决相同值问题。例如,Course 表中有很多课程的学分是相同的,此时单用学分排序不会得到满意的结果。如果用学分和课号两个字 段排序,则会将学分相同的记录用课号字段排序,这就解决了相同值问题。 【例 6.13】从 Course 表中查询所有内容。要求将查询结果按照学分降序排序,当学分相同时按照 课号升序排序。 SELECT * FROM Course ORDER BY 学分 DESC,课号 运行结果如图 6.16 所示。 上面的语句中,学分后有关键字 DESC,因此结果集先按学分降序排序。当遇到学分相同的记录 时,便用课号进行排序,因为课号后没有任何关键字,所以按课号升序排序。例如,“邓小平理论”、 “心理学”和“教育学”的学分相同,此时按课号对这 3 门课程进行了升序排序。 SQL 技术与网络数据库开发详解 ·78· 6.3.4 按字段位置排序 在实际应用中,有时也需要按字段位置排序。因为,SELECT 关键字后并非都是字段名,也可能 是表达式。如果希望按表达式的值排序,而又没有给表达式取别名,则可以按字段位置排序。 【例 6.14】从 Student 表中查询学生的学号、姓名和年龄,并按年龄降序排序记录。 SELECT 学号,姓名,DATEDIFF(year, 出生日期, GETDATE( )) FROM Student ORDER BY 3 DESC 运行结果如图 6.17 所示。 图 6.16 例 6.13 查询结果 图 6.17 例 6.14 查询结果 说明:表达式 DATEDIFF(year, 出生日期, GETDATE( ))的作用是返回“出生日期”字段值和当前系统 时间的年份差值。GETDATE 函数的返回值是当前系统时间。DATEDIFF 函数和 GETDATE 函 数均为 SQL Server 的函数。 上面的语句中,因为表达式 DATEDIFF(year, 出生日期, GETDATE( ))在字段名列表中的位置是 3, 所以 ORDER BY 子句中的 3 DESC,表示使用表达式 DATEDIFF(year, 出生日期, GETDATE( ))的值降 序排序记录。 技巧:当字段名比较冗长或者拼写比较复杂时,在 ORDER BY 子句中使用字段位置会节省拼写时间和 减少拼写出错的概率。 注意:有些 DBMS 可能不支持按字段位置排序,因此在使用前应仔细阅读具体 DBMS 的说明文档。 其实,本例除了使用位置排序以外,在 ORDER BY 子句后可以直接放置表达式来排序,如下面的 语句所示。 SELECT 学号,姓名,DATEDIFF(year, 出生日期, GETDATE( )) FROM Student ORDER BY DATEDIFF(year, 出生日期, GETDATE( )) DESC 运行结果与按位置排序的运行结果相同。  条件表达式  使用 WHERE 关键字设置查询条件  排序条件查询的结果  TOP与 ROWNUM 的使用 在日常工作中,数据库的查询并非只是简单地查询所有记录,多数情况下是指定搜 索条件查询需要的数据,例如,查找计算机系的所有学生;查找 4 学分的所有课程等。 在查询语句中,指定条件需要使用WHERE 子句。本章将介绍编写条件表达式的方法和 使用 WHERE 子句查询所需数据的一些简单方法。 条件查询 第 章 SQL 技术与网络数据库开发详解 ·80· 7.1 条件表达式 条件表达式是使用条件运算符将常量、字段值、函数以及字段名连接起来的表达式。条件表达式 的值只有两种,分别是真(True)和假(False)。因为只要用到条件查询就要编写条件表达式,所以 了解条件表达式的组成,掌握其编写方法非常重要。本节将介绍条件表达式的相关内容。 7.1.1 指针与字段变量的概念 为了后面能很好地说明 WHERE 子句中条件表达式的工作原理,首先介绍两个概念——指针与字 段变量。指针是人们虚拟出来的一个箭头(或者标记),实际上它并不存在。指针可以指向数据表中 的任何一条记录,当指针指向某条记录时该记录就被称为当前记录。例如,指针指向了第 3 条记录时, 第 3 条记录就会成为当前记录,如图 7.1 所示,当前记录为第 3 条记录(学号为 0002 的记录)。 图 7.1 指针示意图 了解了指针和当前记录后,下面介绍字段变量。在表达式中出现的字段名其实就是字段变量,称 其为字段变量的原因是字段名的值会随着指针的移动而变化。例如,在图 7.1 中,姓名字段的当前值为 “李四”,而如果指针移动到了第 4 条记录上,姓名字段的当前值就会变为“马六”,所以表达式中 将字段名作为变量来使用。 7.1.2 条件表达式 如果要使用 WHERE 子句,则必须学会编写条件表达式。条件表达式其实是关系表达式、逻辑(布 尔)表达式和几个 SQL 特殊条件表达式的统称。条件表达式只有真(True)和假(False)两种值。在 学习编写条件表达式之前,首先应当了解条件运算符。下面的表 7.1 列出了 SQL 语言中使用的条件运 算符。 表 7.1 条件运算符 运 算 符 说 明 举 例 关系运算符 = 等于 姓名='王五',学分=4,出生日期='05/29/1973' < 小于 考试成绩<90 第 7 章 条件查询 ·81· 续表 运 算 符 说 明 举 例 关系运算符 <= 小于等于 出生日期<='01/01/1974' > 大于 平时成绩>90 >= 大于等于 平时成绩>=80 <>或!= 不等于 所属院系<>'中文系' 逻辑(布尔)运算符 NOT 非 NOT 考试成绩<90 AND 与(而且) 考试成绩>80 AND 平时成绩>=90 OR 或 平时成绩=100 OR 考试成绩>95 SQL 特殊条件运算符 IN 在某个集合中 学分 IN (2,3,4) NOT IN 不在某个集合中 所属院系 NOT IN('中文系','外语系') BETWEEN 在某个范围内 学分 BETWEEN 2 AND 3 NOT BETWEEN 不在某个范围内 学号 NOT BETWEEN '0001' AND '0005' LIKE 与某种模式匹配 姓名 LIKE '%三%' NOT LIKE 不与某种模式匹配 课名 NOT LIKE '%基础%' IS NULL 是 NULL 值 联系方式 2 IS NULL IS NOT NULL 不是 NULL 值 联系方式 2 IS NOT NULL 1.关系运算符 使用关系运算符编写条件表达式时,需要注意字段的类型。如果是字符类型的字段,则必须与字 符型常量相比较,例如: 姓名='王五' 因为姓名是字符型字段,所以一定要注意将“王五”放进单引号中,将其变为字符串。该表达式 在指针指向 Student 表的第 2 条记录时为真,其他情况下均为假。因为只有在指针指向第 2 条记录时, 字段变量“姓名”的值才会为'王五',此时表达式便成为: '王五'='王五' 因此,表达式的结果为真。 如果是数值类型的字段,则必须与数值型常量比较,例如: 学分=4 在此,绝对不可以将数值 4 放进单引号内,因为学分是数值型常量。 使用关系运算符编写条件表达式时,最需要注意的是日期型字段。有些 DBMS 中支持日期型常量, 例如 Access。所以在 Access 中编写 1974 年 1 月 1 日之前出生的条件表达式为: 出生日期<#01/01/1974# 而有些 DBMS 中,没有日期型常量的概念,例如,SQL Server 和 Oracle 数据库系统,但是这类数 SQL 技术与网络数据库开发详解 ·82· 据库管理系统能够识别日期格式的字符串。例如,在 SQL Server 中编写 1974 年 1 月 1 日之前出生的条 件表达式为: 出生日期<’01/01/1974’ 在条件表达式中如果使用了日期型字段,则应当查看具体 DBMS 对日期型字段如何处理的说明。 2.逻辑运算符 逻辑运算符在条件表达式中也是举足轻重的,多条件复合查询、多表连接等都需要用到逻辑运算 符。3 个逻辑运算符中,NOT 的优先级最高,其次是 AND,最后是 OR 运算符。如果表达式中,既有 逻辑运算符又有关系运算符,则所有关系运算符的优先级都比逻辑运算符的高。 (1)NOT 运算符 NOT 运算符用于求反,其运算规则如下所示。 NOT True=False NOT False = True 例如,想要查询非计算机系的所有学生,这时条件表达式可以写为如下形式。 NOT 所属院系='计算机系' (2)AND 运算符 条件表达式中的 AND 表示“与”,或者可以说是表示“而且”。其运算规则如下所示。 True AND True = True True AND False = False False AND True = False False AND False = False 从上面可以看出,使用 AND 运算符的表达式只有在两边都是真时,结果才会为真。AND 运算符 可以表示“而且”,例如,想要查询平时成绩大于等于 90 分,而且考试成绩大于等于 80 分的记录, 条件表达式可以写为如下形式。 平时成绩>=90 AND 考试成绩>=80 (3)OR 运算符 条件表达式中的 OR 运算符表示“或”,其运算规则如下所示。 True OR True = True True OR False = True False OR True = True False AND False = False 从上面可以看出,使用 OR 运算符的表达式,只要一边为真,则结果就会为真。OR 运算符表示“或 者”,例如,想要查询来源地是北京市或者所属院系为物理系的学生,条件表达式可以写为如下形式。 来源地='北京市' OR 所属院系='物理系' 上面简单介绍了 NOT、AND 和 OR 3 个逻辑运算符,关于逻辑运算符的详细用法请读者查看本书 第 8 章的内容。 第 7 章 条件查询 ·83· 3.SQL 特殊条件运算符 关于特殊条件运算符的详细内容请查看本书后面的内容。 7.2 使用 WHERE 关键字设置查询条件 本节将介绍 WHERE 子句的用法,并通过几个实例,带领读者学习使用 WHERE 子句设定查询条 件,查询数值数据、字符数据、日期数据和空值等的方法。 7.2.1 WHERE 子句用法 WHERE 子句用来设置搜索条件,例如,想要从数据表中查找来自内蒙古的所有学生,则可以编 写如下带有 WHERE 子句的 SELECT 语句。 SELECT * FROM student WHERE 来源地='内蒙古自治区' 该语句运行结果如图 7.2 所示。 图 7.2 来源地为内蒙古的所有学生 从图中可以看出,查询结果集中只有来源地是内蒙古自治区的学生,其他非内蒙古籍的学生全部 被筛选掉了,这与 WHERE 子句的执行原理有关系。下面通过刚才的例子,说明 WHERE 子句的执行 原理。为了方便参考,表 7.2 列出了 Student 表的部分内容。 表 7.2 Student 表部分内容 学 号 姓 名 性 别 出 生 日 期 来 源 地 …… 所 属 院 系 0001 张三 男 1973-5-29 广东省 …… 中文系 0003 王五 女 1975-9-1 辽宁省 …… 物理系 0002 李四 女 1980-1-8 浙江省 …… 外语系 0007 马六 男 1975-7-12 浙江省 …… 外语系 0004 周七 女 1977-9-21 北京市 …… 计算机系 0005 刘八 女 1979-8-30 海南省 …… 中文系 0008 杨九 男 1980-2-17 重庆市 …… 计算机系 0009 吴一 男 1976-11-1 内蒙古自治区 …… 外语系 0006 赵二 女 1978-2-2 江苏省 …… 中文系 0010 徐零 女 1981-1-1 内蒙古自治区 …… 计算机系 本例中,WHERE 子句按照如下步骤执行。 SQL 技术与网络数据库开发详解 ·84· (1)将指针指向 Student 表的第 1 条记录,此时,字段变量“来源地”的值为“广东省”,此时 条件表达式变为: '广东省'='内蒙古自治区' 因为该条件表达式的值为 False,所以这条记录被筛选掉,没有进入查询结果集中。 (2)指针向下移动指向第 2 条记录,与上面的原因相同,这条记录也被过滤掉。 (3)指针不断向下移动,将条件表达式的值为 False 的记录全部筛选掉。 (4)当指针移到第 8 条记录时,字段变量“来源地”的值为“内蒙古自治区”,此时条件表达式 变为: '内蒙古自治区'='内蒙古自治区' 因为条件表达式的值为 True,所以这条记录没有被筛选掉,成为进入查询结果集的第 1 条记录。 (5)指针继续向下移动,将第 9 条记录筛选掉,又将第 10 条记录添加到查询结果集中。 (6)指针再次向下移动时,遇到了数据表结束标记,WHERE 子句结束执行。 综上所述,WHERE 子句的工作原理为:从表中的第 1 条记录开始向下搜索直到遇见结束标记为 止。在此过程中,将条件表达式的值为 False 的当前记录筛选掉,而将条件表达式的值为 True 的当前 记录添加到查询结果集中。 下面是带有 WHERE 子句的 SELECT 语句的语法格式。 SELECT [DISTINCT | ALL] select_list FROM table_source WHERE 条件表达式 其中,WHERE 后的“条件表达式”就是前面 7.1 节介绍的条件表达式。 7.2.2 查询数值数据 本小节将通过几个例题说明使用 WHERE 子句查询数值数据的方法。 【例 7.1】从 Course 表中,查询所有 3 学分的课程信息。 SELECT * FROM course WHERE 学分=3 运行结果如图 7.3 所示。 从图中看出,结果集中有 3 条记录,这 3 条记录的学分都是 3,满足 WHERE 子句中的条件。而 其他不是 3 学分的课程信息都被筛选掉了。 说明:因为“学分”字段是数值型字段,因此必须与数值常量比较,所以表达式学分=3,不能写为学 分='3',或者其他形式。 【例 7.2】从 Course 表中,查询所有学分不小于 3 的课程的课名和课号。 SELECT 课名,课号 FROM course 第 7 章 条件查询 ·85· WHERE 学分>=3 运行结果如图 7.4 所示。从图中看出,结果集中的字段顺序(课名,课号)是根据 SELECT 子句后 的字段列表顺序产生的,而并不是只能按照源表的字段顺序(课号,课名,……)排列。结果集中的 5 条 记录都满足了条件:学分不小于 3。其他不满足条件的记录都被筛选掉了。 图 7.3 例 7.1 查询结果 图 7.4 例 7.2 查询结果 【例 7.3】从 Score 表中,查询总成绩大于等于 90 的学生学号和这门课的课号。计算总成绩的公 式为:总成绩=考试成绩*0.7+平时成绩*0.3。 SELECT 学号,课号, 考试成绩*0.7+平时成绩*0.3 AS 总成绩 FROM Score WHERE 考试成绩*0.7+平时成绩*0.3>=90 运行结果如图 7.5 所示。 图 7.5 例 7.3 查询结果 注意:上面 WHERE 子句中的条件表达式不可以写为如下形式。 总成绩>=90 因为 WHERE 子句在 SELECT 子句之前执行,所以在 WHERE 子句执行时并没有执行给计算字段 (考试成绩*0.7+平时成绩*0.3)取别名的操作。 7.2.3 查询字符型数据 前面介绍了如何查询数值型数据的方法,下面仍旧通过几个例题介绍怎样查询字符型数据的方法。 【例 7.4】从 Student 表中,查询名叫“张三”的学生。 SELECT * FROM student WHERE 姓名='张三' SQL 技术与网络数据库开发详解 ·86· 运行结果如图 7.6 所示。 图 7.6 例 7.4 查询结果 说明:因为“姓名”字段是字符型字段,因此必须与字符常量比较,所以必须用单引号(')括住 “张三”。 【例 7.5】从 Student 表中,查询非计算机系的所有学生。 SELECT * FROM student WHERE 所属院系<>'计算机系' 运行结果如图 7.7 所示。 图 7.7 例 7.5 查询结果 上面 WHERE 子句的条件表达式中使用了不等于(< >)符号。有些 DBMS 中,不等于也可以用一 个感叹号加一个等于号(!=)表示。 【例 7.6】从 Course 表中,查询课号大于“003”的课程信息。 SELECT * FROM course WHERE 课号>'003' 运行结果如图 7.8 所示。 图 7.8 例 7.6 查询结果 字符串比较大小,其实是在比较每个字符的 ASCII 码值,ASCII 码大的字符为大。人们经常使用 的字符里数字字符“0”的 ASCII 码是 48,“1”的 ASCII 码是 49 等,依此类推向后递增;大写英文 字母“A”的 ASCII 码是 65,“B”的 ASCII 码是 66 等,依此类推向后递增;小写英文字母“a”的 ASCII 码是 97,“b”的 ASCII 码是 98 等,依此类推向后递增。因此,每个排列的后面的字符都比前 面的要大。汉字比较大小时比较的是拼音,例如,“张”比“王”大,因为“z”大于“w”。 【例 7.7】从 Student 表中,查询姓名按拼音排在“马六”后的所有学生的姓名、来源地和所属 院系。 第 7 章 条件查询 ·87· SELECT 姓名,来源地,所属院系 FROM student WHERE 姓名>'马六' 运行结果如图 7.9 所示。 图 7.9 例 7.7 查询结果 7.2.4 查询日期数据 使用 WHERE 子句也能查询日期型数据。但需要注意的是:在不同的 DBMS 中编写查询日期型数 据的条件表达式也不同。 【例 7.8】从 Student 表中,查询 1977 年 1 月 1 日之后出生的学生姓名、联系方式和所属院系。 (1)如果运行环境为 SQL Server,则 SELECT 语句编写如下。 SELECT 姓名,联系方式 1,联系方式 2,所属院系 FROM student WHERE 出生日期>' 01/01/1977' 运行结果如图 7.10 所示。 图 7.10 例 7.8 查询结果 (2)如果运行环境为 Access,则 SELECT 语句编写如下。 SELECT 姓名,联系方式 1,联系方式 2,所属院系 FROM student WHERE 出生日期># 01/01/1977# 说明:在 Access 中,日期型数据必须被包含在井字符(##)中。 (3)如果运行环境为 Oracle,则 SELECT 语句编写如下。 SELECT 姓名,联系方式 1,联系方式 2,所属院系 FROM student WHERE 出生日期>'01-JAN-1977' SQL 技术与网络数据库开发详解 ·88· 说明:在 Oracle 中,日期型数据必须被包含在单引号('')中。而且,Oracle 中日期的默认格式为 DD-MON-YY,其中 DD 代表日,MON 代表月,并且必须是英文月份名的简写,YY 代表用两 位数字表示的年份,在此建议读者使用四位数字的年份。 在此还需要提醒读者一个问题,有些 DBMS 的日期型数据中包含时间,例如 Oracle 和 SQL Server, 因此在使用等值(=)查询日期时应当注意。例如,下面的 SELECT 语句只能查询 1980 年 1 月 8 日 0 点 0 分 0 秒出生的人。 SELECT * FROM student WHERE 出生日期='01/08/1980' 如果想查询 1980 年 1 月 8 日内出生的所有人,则需要使用其他方法。下面列出一种比较通用的 方法。 SELECT * FROM student WHERE 出生日期>='01/08/1980' AND 出生日期<'01/09/1980' 在 SELECT 语句中,AND 运算符的详细使用方法将在本书后面的内容中介绍。 7.2.5 按范围查询数据(BETWEEN) 有时需要查询某个范围内的数据,此时可以在 WHERE 子句中使用 BETWEEN 运算符,该运算符 需要两个值,即范围的开始值和结束值。 【例 7.9】从 Score 表中,查询考试成绩在 70~80 分之间的所有学生的学号和这门课程的课号和 考试成绩。 SELECT 学号,课号,考试成绩 FROM score WHERE 考试成绩 BETWEEN 70 AND 80 运行结果如图 7.11 所示。 图 7.11 例 7.9 查询结果 说明:BETWEEN 运算符包含开始值和结束值。 【例 7.10】从 Student 表中,查询 1977 年 1 月 1 日~1980 年 1 月 1 日之间出生的学生姓名、出生 第 7 章 条件查询 ·89· 日期和所属院系。假设执行 SQL 的环境为 SQL Server。 SELECT 姓名,出生日期,所属院系 FROM student WHERE 出生日期 BETWEEN ' 01/01/1978' AND ' 01/01/1980' 运行结果如图 7.12 所示。 图 7.12 例 7.10 查询结果 7.2.6 查询空值 数据库操作中,有时需要查询表中的空值或者非空值,此时可以使用 IS NULL(IS NOT NULL) 运算符。 【例 7.11】从 Student 表中,查询“联系方式 2”字段为空的所有学生的信息。 SELECT * FROM student WHERE 联系方式 2 IS NULL 运行结果如图 7.13 所示。 图 7.13 例 7.11 查询结果 注意:查询空值不能写为(字段名=NULL)。 【例 7.12】从 Student 表中,查询“联系方式 2”字段不为空的学生姓名、所有联系方式和所属 院系。 SELECT 姓名, 联系方式 1, 联系方式 2, 所属院系 FROM student WHERE 联系方式 2 IS NOT NULL 运行结果如图 7.14 所示。 图 7.14 例 7.12 查询结果 SQL 技术与网络数据库开发详解 ·90· 7.3 排序条件查询的结果 在第 6 章中介绍了排序查询结果的方法,其实排序带有 WHERE 子句的查询结果与其大同小异, 只是应当牢记一点——ORDER BY 子句必须放在 WHERE 子句的后面。 【例 7.13】从 Student 表中,查询“联系方式 2”字段不为空的学生学号、姓名、所有联系方式和 所属院系,并且按学号升序进行排序。 SELECT 学号,姓名, 联系方式 1, 联系方式 2, 所属院系 FROM student WHERE 联系方式 2 IS NOT NULL ORDER BY 学号 运行结果如图 7.15 所示。 图 7.15 例 7.13 查询结果 注意:如果 SELECT 语句中有 ORDER BY 子句,则必须将其放在 WHERE 子句之后。 7.4 TOP 与 ROWNUM 的使用 在数据库操作中,有时需要限制查询返回的记录个数。SQL Server 中的 TOP 关键字和 Oracle 中的 ROWNUM 可以完成这个任务。 1.SQL Server 中的 TOP SQL Server 中的 TOP 关键字可以限制返回到结果集中的记录个数。下面通过例题介绍 TOP 关键 字的用法。 【例 7.14】从 Student 表中,查询生日最大的前 5 名学生的姓名和联系方式 1。 SELECT TOP 5 姓名,联系方式 1 FROM student ORDER BY 出生日期 运行结果如图 7.16 所示。 TOP 关键字除了上述用法以外,还有一种用法: TOP n PERCENT 第 7 章 条件查询 ·91· 其含义为从顶部开始获取结果集的百分之 N。例如,下面的语句查询 Student 表中以出生日期排序 后,前 30%的学生信息。 SELECT TOP 30 PERCENT 姓名,联系方式 1 FROM student ORDER BY 出生日期 运行结果如图 7.17 所示。 图 7.16 例 7.14 查询结果 1 图 7.17 例 7.14 查询结果 2 2.Oracle 在 Oracle 中使用 ROWNUM 限制返回的记录个数。例如,下面的语句用于返回 Student 表中的前 5 条记录。 SELECT * FROM student WHERE ROWNUM<6;  组合 WHERE 子句  使用 IN 运算符  NOT运算符  实现模糊查询 本章将介绍如何使用 WHERE 子句设置更高级的查询条件。例如,查询计算机系的 所有女生;查询中文系或者外语系的所有男生等。此外,还将介绍使用 IN、NOT、LIKE 3 个运算符和使用通配符进行模糊查询的方法。 高级条件查询 第 章 SQL 技术与网络数据库开发详解 ·94· 8.1 组合 WHERE 子句 本节将教会读者使用 AND 和 OR 运算符设置高级查询条件的具体方法。AND 和 OR 两个运算符 可以将单独的条件表达式组合在一起,形成复杂、强大的搜索条件表达式。这种表达式将会满足用户 很多的查询需求。 8.1.1 AND 运算符 如前所述,AND 运算符只有当两边操作数均为 True 时,最后结果才为 True。根据 AND 的这种运 算规则,人们使用 AND 描述“与”(而且)的关系,即既满足第一个条件且满足第二个条件时才会通 过审核。下面的几个例题使用 AND 完成了一些复杂的查询任务。 【例 8.1】从 Student 表中查询计算机系的所有女生,并将结果按学号升序排序。 分析:使用前面所学的知识,只能完成查询计算机系的所有学生或者查询所有女生,而并不能完成查 询不但是计算机系的学生,而且还是女生的任务。这就需要组合这两个条件,因为这两个条件 是“而且”的关系,所以使用 AND 运算符连接。具体的 SELECT 语句如下。 SELECT * FROM student WHERE 所属院系='计算机系' AND 性别='女' ORDER BY 学号 运行结果如图 8.1 所示。 图 8.1 例 8.1 运行结果 图 8.1 中只有两条记录,这两条记录既满足了是计算机系的学生,又满足了是女生的条件。 【例 8.2】从 Student 表中查询 1975 年出生的所有学生,并将结果按出生日期升序排序。 (1)如果 SQL 运行环境为 Access,则因为日期型数据中没有时间,可以使用如下 SELECT 语句 查询。 SELECT * FROM student WHERE 出生日期>= #01/01/1975# AND 出生日期<= #12/31/1975# ORDER BY 出生日期 (2)如 果 SQL 运行环境为 SQL Server,则因为日期型数据中有时间,所以应当使用如下 SELECT 语句查询。 SELECT * 第 8 章 高级条件查询 ·95· FROM student WHERE 出生日期>='01/01/1975' AND 出生日期<'01/01/1976' ORDER BY 出生日期 (3)如果 SQL 运行环境为 Oracle,也因为日期型数据中有时间,所以应当使用如下 SELECT 语 句查询。 SELECT * FROM student WHERE 出生日期>='01/JAN/1975' AND 出生日期<'01/JAN/1976' ORDER BY 出生日期 本例运行结果如图 8.2 所示。 图 8.2 例 8.2 运行结果 上面两个例题的搜索条件中只用了一个 AND 运算符,实际上根据需要可以使用多个 AND 组合条 件,例如下面的例题。 【例 8.3】从 Student 表中查询 1975 年出生的所有女生,并将结果按出生日期升序排序。假设 SQL 运行环境为 SQL Server。 SELECT * FROM student WHERE 出生日期>='01/01/1975' AND 出生日期<'01/01/1976' AND 性别='女' ORDER BY 出生日期 运行结果如图 8.3 所示。 图 8.3 例 8.3 运行结果 8.1.2 OR 运算符 OR 运算符只有当两边操作数均为 False 时,最后结果才为 False,只要一边是 True,则最后结果为 True。根据 OR 的这种运算规则,人们使用 OR 运算符描述“或”(或者)的关系,即当满足任何一个 条件就可以通过审核。下面的几个例题使用 OR 运算符完成了一些复杂的查询任务。 【例 8.4】从 Student 表中查询中文系的所有学生和外语系的所有学生,并将结果按学号升序排序。 分析:本题两个条件的关系其实是“或”,因为满足任何一个条件就可以通过审核。 SELECT * FROM student SQL 技术与网络数据库开发详解 ·96· WHERE 所属院系='中文系' OR 所属院系='外语系' ORDER BY 学号 运行结果如图 8.4 所示。 图 8.4 例 8.4 运行结果 查询结果中既包含了中文系的所有学生,又包含了外语系的所有学生。这是因为中文系的学生满 足表达式: 所属院系='中文系' 即为 True,所以整个条件表达式: 所属院系='中文系' OR 所属院系='外语系' 变成了 True OR False 根据 OR 的运算规则,最终条件表达式的值为 True,所以所有中文系的学生都进入了查询结果集 中。同理,所有外语系的学生也都进入了查询结果集,而其他院系的学生都被筛选掉了。 8.1.3 AND 与 OR 的优先顺序问题 WHERE 子句中可以包含任意数量的 AND 和 OR 运算符,并且允许两者结合使用。下面的例题组 合了 AND 和 OR 两个运算符,解决了一个查询任务。 【例 8.5】从 Student 表中查询中文系和外语系的所有女生。 分析:前面已经介绍了查询中文系和外语系的学生需要使用 OR 运算符,又因为要查询这两个系的女 生,所以还得使用 AND 运算符。编写如下的 SELECT 语句。 SELECT * FROM student WHERE 所属院系='中文系' OR 所属院系='外语系' AND 性别='女' ORDER BY 学号 运行结果如图 8.5 所示。 图 8.5 例 8.5 运行结果 1 第 8 章 高级条件查询 ·97· 查看运行结果后会发现一个男生进入了查询结果集中,导致这一错误的根源是运算符的优先级问 题。在表达式中,如果同时出现了 AND 和 OR 两种运算符,则并非从左到右按顺序运算,而是优先执 行 AND,然后执行 OR 运算符。 了解了运算符的优先级后,上面错误的原因就很容易地找到了。因为上面的条件表达式与下面的 表达式等价。 所属院系='中文系' OR (所属院系='外语系' AND 性别='女') 而该表达式的意思是:中文系的所有学生和外语系的所有女生,因此,查询结果集中出现了中文 系的男生。为了让 OR 运算符优先执行,可以使用括号,下面的 SELECT 语句是正确的查询语句。 SELECT * FROM student WHERE (所属院系='中文系' OR 所属院系='外语系') AND 性别='女' ORDER BY 学号 技巧:在有多种运算符的组合条件表达式中,尽量使用括号,即使计算机可能不需要这些括号,但这 样会方便人们阅读和理解复杂的条件表达式,同时也会减小出错的概率。 运行结果如图 8.6 所示。 图 8.6 例 8.5 运行结果 2 8.2 使用 IN 运算符 在查询中,有时会遇到这样一种查询任务——指定的字段值只要属于某个集合,就将该记录查询 出来。此时,会用到 IN 运算符。 8.2.1 使用 IN 运算符 IN 运算符的运算规则是:当 X 在集合{Value1, Value2,……ValueN}中时,表达式 X IN (Value1, Value2,……ValueN) 为 True,而 X 不在集合{Value1, Value2,……ValueN}中时,上面的表达式为 False。例如: 8 IN (2,5,8,13) 因为 8 在集合{2,5,8,13}中,所以表达式的值为 True。对于 7 IN (2,5,8,13) 因为 7 不在集合{2,5,8,13}中,所以表达式的值为 False。下面通过一个例题感受一下使用 IN SQL 技术与网络数据库开发详解 ·98· 运算符查询数据的方法。 【例 8.6】从 Course 表中查询学分为 2、3、4 的课程的信息,并按学分降序,课号升序排序。 SELECT * FROM course WHERE 学分 IN (2,3,4) ORDER BY 学分 DESC,课号 运行结果如图 8.7 所示。 图 8.7 例 8.6 运行结果 说明:在 IN 运算符表达式中,集合必须用圆括号括住,并且各元素之间用逗号(,)分隔。 本例演示了使用 IN 运算符查询数值型数据的方法,下面再看一个使用 IN 运算符查询字符型数据 的例题。 【例 8.7】从 Student 表中查询中文系、外语系和计算机系的所有学生,并按院系降序排列。 SELECT * FROM student WHERE 所属院系 IN ('中文系','外语系','计算机系') ORDER BY 所属院系 DESC 运行结果如图 8.8 所示。 图 8.8 例 8.7 运行结果 IN 运算符还有一个反向运算符——NOT IN。下面的例题使用 NOT IN 运算符解决了一个查询任务。 【例 8.8】从 Student 表中,查询除中文系、外语系和计算机系以外的其他系的学生,并按院系降 序排列。 SELECT * FROM student WHERE 所属院系 NOT IN ('中文系','外语系','计算机系') ORDER BY 所属院系 DESC 运行结果如图 8.9 所示。 第 8 章 高级条件查询 ·99· 图 8.9 例 8.8 运行结果 8.2.2 使用 IN 运算符的优点 通过前面几个例题的学习,读者一定会感觉到 IN 运算符和 OR 运算符实现的功能是相同的。那么 为什么使用 IN 运算符呢?因为 IN 运算符有如下优点。 当条件很多时,使用 IN 运算符会使语句更加简洁、清楚。例如,如果将例题 8.7,使用 OR 运 算符改写则其语句为: SELECT * FROM student WHERE 所属院系='中文系' OR 所属院系='外语系' OR 所属院系='计算机系' ORDER BY 所属院系 DESC 很明显,此时使用 IN 运算符会比 OR 运算符简洁、清楚得多。 IN 运算符的执行速度要比 OR 运算符更快。 IN 运算符最大的优点是:其后条件列表集合中,可以放置其他 SELECT 语句,即子查询。下 面通过一个例题体现该优点。 【例 8.9】从 Score 表中,查询所有学生的“心理学”的考试成绩和平时成绩,并按考试成绩降序 排列,当考试成绩相同时按平时成绩降序排列。 分析:因为 Score 表中没有课名只有课号,因此,必须从 Course 表中找到“心理学”的课号,然后根 据这一课号从 Score 表中查询考试成绩和平时成绩。 SELECT 学号,考试成绩,平时成绩 FROM score WHERE 课号 IN (SELECT 课号 FROM course WHERE 课名='心理学') ORDER BY 考试成绩 DESC,平时成绩 DESC 运行结果如图 8.10 所示。 图 8.10 例 8.9 运行结果 SQL 技术与网络数据库开发详解 ·100· SELECT 语句中,子查询 SELECT 课号 FROM course WHERE 课名='心理学 的查询结果是“心理学”的课号,如果将其单独运行,则结果如图 8.11 所示。 图 8.11 子查询运行结果 因此,整个 SELECT 语句就会变成如下形式。 SELECT 学号,考试成绩,平时成绩 FROM score WHERE 课号 IN (‘002’) ORDER BY 考试成绩 DESC,平时成绩 DESC 所以,本例的 SELECT 语句查出了所有学生的“心理学”的考试成绩和平时成绩。关于子查询的 详细内容可查看本书后面的内容。 8.3 NOT 运算符 NOT 运算符的作用是对其后的表达式求反。下面通过两个例题介绍 NOT 运算符的使用方法。 【例 8.10】从 Student 表中查询来源地不是北京市和广东省的所有学生。 SELECT * FROM student WHERE NOT (来源地='北京市' OR 来源地='广东省') 运行结果如图 8.12 所示。 图 8.12 例 8.10 运行结果 如果本例中不使用 NOT 运算符,而采用不等于(< >)运算符,则大多数初学者可能会编写如下 的 SELECT 语句。 SELECT * FROM student WHERE 来源地<>'北京市' OR 来源地<>'广东省' 第 8 章 高级条件查询 ·101· 运行结果如图 8.13 所示。 图 8.13 使用“<>”运算符得到的运行结果 1 从图 8.13 中可以看到,结果集中包含了北京市和广东省的学生,因为当指针指向广东省的学生时, 表达式 来源地<>'北京市’ 的值为 True,根据 OR 运算符的规则,整个表达式的值也为 True,所以广东省的学生被包含进了结果 集,因为类似的原因,北京市的学生也被包含进了结果集中。所以,上面使用不等于运算符的 SELECT 语句是错误的,下面的语句才是正确语句。 SELECT * FROM student WHERE 来源地<>'北京市' AND 来源地<>'广东省' 运行结果如图 8.14 所示。 图 8.14 使用“<>”运算符得到的运行结果 2 NOT 运算符不仅可以对表达式求反,还可以和一些特殊运算符结合使用,例如前面介绍的 IS NOT NULL、NOT BETWEEN 和 NOT IN 等就是结合使用的例子。下面演示 NOT BETWEEN 的用法。 【例 8.11】从 Student 表中,查询出生日期不在 1978—1980 年之间(包含 1978 和 1980 年)的所 有学生。 (1)假设 SQL 运行环境为 SQL Server,则其 SELECT 语句如下所示。 SELECT * FROM student WHERE 出生日期 NOT BETWEEN '01/01/1978' AND '12/31/1980' 运行结果如图 8.15 所示。 SQL 技术与网络数据库开发详解 ·102· 图 8.15 例 8.11 运行结果 说明:在本书中,因为出生日期的时间部分都为 0,所以上面的查询能够正确运行。如果时间部分不为 0,则应当编写另外的语句查询,下面列出一种具体的方法,其中,DATEPART 函数的相关内 容可查看本书函数部分的内容。 SELECT * FROM student WHERE DATEPART(YY,出生日期) NOT BETWEEN 1978 AND 1980 (2)假设 SQL 运行环境为 Access,则其 SELECT 语句如下所示。 SELECT * FROM student WHERE 出生日期 NOT BETWEEN #01/01/1978# AND #12/31/1980# (3)假设 SQL 运行环境为 Oracle,则其 SELECT 语句如下所示。 SELECT * FROM student WHERE 出生日期 NOT BETWEEN '01/JAN/1978' AND '31/DEC/1980' 注意:在设置查询条件时,应尽量避免使用否定条件,例如 NOT BETWEEN、NOT IN 等,因为有些 DBMS 不能优化这些条件查询。 8.4 实现模糊查询 有时只知道需要查询内容的一部分,例如,只知道某学生姓名中含有“三”字,而并不清楚完整 姓名是什么,此时如果想要查询该学生的信息,用前面所学的内容是很难做到的,使用通配符和 LIKE 运算符可以解决这类问题。本节将介绍 LIKE 运算符和几种通配符的使用方法。 8.4.1 LIKE 运算符 结合使用 LIKE 运算符和通配符可以对表进行模糊查询,即仅仅使用查询内容的一部分查询数据 库中存储的数据。当然,LIKE 运算符也可以单独使用,单独使用时,其功能与等于运算符(=)相同。 不过,需要注意的是 LIKE 运算符只支持字符型数据。下面的例题演示了 LIKE 运算符的使用方法,因 为没有使用通配符,实际上没有什么太大意义,只是演示了使用方法而已。 【例 8.12】从 Student 表中查询中文系所有学生的信息,并按学号升序排序。 SELECT * FROM student 第 8 章 高级条件查询 ·103· WHERE 所属院系 LIKE '中文系' ORDER BY 学号 运行结果如图 8.16 所示。 图 8.16 例 8.12 运行结果 LIKE 运算符也可以和 NOT 结合使用,例如,下面的例题演示了 NOT LIKE 的用法。 【例 8.13】从 Student 表中查询不是中文系的学生的信息,并按所属院系升序排序。 SELECT * FROM student WHERE 所属院系 NOT LIKE '中文系' ORDER BY 所属院系 运行结果如图 8.17 所示。 图 8.17 例 8.13 运行结果 本例中 NOT LIKE 的功能和不等于(< >)运算符相同。 8.4.2 “%”通配符 在 SQL 语言中,使用百分号(%)通配符代表 0 个或多个字符。下面列出了几个典型的例子供读 者参考,如表 8.1 所示。 表 8.1 百分号(%)通配符举例 百分号(%)通配符举例 说 明 匹配字符串举例 a% 代表头字母为“a”的所有字符串 “a”、“abc”、“amer mend uu?”等 %NBA% 代表含有“NBA”的所有字符串 “NBA 篮球明星”、“进入 NBA 的姚明”、 “巴特尔与 NBA”等 %nm 代表最后两个字母为“nm”的所有字符串 “nm”、“123nm” A%Z 代表头字母为“A”,最后一个字母为“Z” 的所有字符串 “AZ”、“ABCDZ”、“A1212DFAFZ”等 %1983% 代表含有 1983 的字符串或者日期时间型 数据 “生于 1983 年”、03/20/1983 说明:如果 SQL 运行环境为 Access,则使用星号(*)通配符代替百分号(%)通配符。 SQL 技术与网络数据库开发详解 ·104· 下面的例题演示了结合使用“%”和 LIKE 运算符,实现模糊查询功能的具体方法。 【例 8.14】从 Student 表中,查询所有姓名中包含“三”字的学生信息。 SELECT * FROM student WHERE 姓名 LIKE '%三%' 运行结果如图 8.18 所示。 图 8.18 例 8.14 运行结果 1 为了更好地体现本例效果,下面在数据表中插入两条新记录,插入语句如下。 INSERT INTO student(学号, 姓名, 性别, 出生日期) VALUES ('0011', '周三丰', '男', '12/20/1981') INSERT INTO student(学号, 姓名, 性别, 出生日期) VALUES('0012', '三宝', '男', '05/15/1983') 执行下面的查询,查看插入结果。 SELECT * FROM student 运行结果如图 8.19 所示。 图 8.19 插入新记录后的 Student 表 再次运行下面的查询语句。 第 8 章 高级条件查询 ·105· SELECT * FROM student WHERE 姓名 LIKE '%三%' 运行结果如图 8.20 所示。 图 8.20 例 8.14 运行结果 2 从图中可以看到结果集中包含了所有姓名中含有“三”字的学生。如果将“%三%”中的第一个“%” 去掉,则查询结果会是什么呢?下面做一个实验,将上面的查询语句改为如下的语句并运行。 SELECT * FROM student WHERE 姓名 LIKE '三%' 运行结果如图 8.21 所示。 图 8.21 例 8.14 运行结果 3 这次的运行结果中只包含了一条记录,因为字符串“三%”只代表头一个字为“三”的所有字符 串。如果将查询语句改为下面的语句: SELECT * FROM student WHERE 姓名 LIKE '%三' 则只能查询最后一个字为“三”的所有学生。其运行结果如图 8.22 所示。 图 8.22 例 8.14 运行结果 4 8.4.3 使用“%”通配符查询日期型数据 有时,使用“%”通配符查询日期时间型数据会很方便,例如,查询 1983 年出生的所有学生、查 询 9 月份出生的所有学生等。下面通过几个例题介绍查询日期时间型数据的具体方法。 【例 8.15】从 Student 表中,查询出生于 1980 年的所有学生。 SELECT * FROM student WHERE 出生日期 LIKE '%1980%' 运行结果如图 8.23 所示。 SQL 技术与网络数据库开发详解 ·106· 图 8.23 例 8.15 运行结果 注意:本例中的“%1980%”不可以写为“1980%”或“%1980”。 【例 8.16】从 Student 表中,查询出生于 9 月份的所有学生。 SELECT * FROM student WHERE 出生日期 LIKE '09%' 运行结果如图 8.24 所示。 图 8.24 例 8.16 运行结果 【例 8.17】从 Student 表中,查询出生于 1975 年 9 月份的所有学生。 SELECT * FROM student WHERE 出生日期 LIKE '09%1975%' 运行结果如图 8.25 所示。 图 8.25 例 8.17 运行结果 虽然使用“%”通配符查询年份和月份比较方便,但是查询日却并不理想。例如,查询每个月 1 日出生的所有学生。此时,使用“%”通配符则不会得到理想的结果。 8.4.4 “_”通配符 “%”通配符可以代表 0 个或多个字符,但是它不能代表指定个数的字符。例如,需要查询姓“周”, 且名字由两个字组成的所有学生。如果使用“%”,则只能查询所有姓“周”的学生,但不能确定名 字只有两个字。例如,下面的 SELECT 语句。 SELECT * FROM student WHERE 姓名 LIKE '周%' 运行结果如图 8.26 所示。 图 8.26 所有姓“周”的学生 第 8 章 高级条件查询 ·107· 因为上述原因出现了下划线(_)通配符,它只代表任意一个字符(包括 0 个字符)。例如, “周_”代表以“周”字开头的,最多由两个汉字组成的字符串。 说明:如果 SQL 运行环境为 Access,则使用问号(?)通配符代替下划线(_)通配符。 【例 8.18】从 Student 表中,查询姓“周”,而且名字最多由 3 个字组成的学生。 SELECT * FROM student WHERE 姓名 LIKE '周__' 注意:“周”后有两个“_”通配符。 运行结果如图 8.27 所示。 图 8.27 例 8.18 运行结果 【例 8.19】从 Student 表中,查询姓“周”,而且名字必须是 3 个字的学生。 SELECT * FROM student WHERE 姓名 LIKE '周__' AND 姓名 NOT LIKE '周__' 注意:第一个“周”后有两个“_”通配符,第二个“周”后有一个“_”通配符。 运行结果如图 8.28 所示。 图 8.28 例 8.19 运行结果 “_”通配符也可以不与字符组合,而单独使用。 【例 8.20】从 Student 表中,查询名字最多由两个字组成的所有学生。 SELECT * FROM student WHERE 姓名 LIKE ' __' 运行结果如图 8.29 所示。 图 8.29 例 8.20 运行结果 SQL 技术与网络数据库开发详解 ·108· 8.4.5 “[]”通配符 如果 SQL 运行环境为 SQL Server、Access 或 Sybase Adaptive Server,则可以在 LIKE 运算符中使 用一种特殊的通配符——方括号([])。表 8.2 列出了方括号通配符的一些例子和说明。 表 8.2 方括号通配符举例 举 例 说 明 [NR]% 代表以 “N”或“R” 字母开头的所有字符串 [a-d]%ing 代表以“a”、“b”、“c”、“d”字母开头,以“ing”结尾的所有字符串 [c-emn]% 代表以“c”、“d”、“e”、“m”和“n”字母开头的所有字符串 N[^B]% 代表以 “N”字母开头,并且第二个字母不是“B”的所有字符串 %197[5-9]% 代表 1975—1979 等 5 个数字 [1][012]% 代表 10、11、12 等 3 个数字 下面是两个使用方括号通配符查询数据的例题。 【例 8.21】从 Student 表中,查询姓“张”、“李”或“周”的所有学生,并按姓名升序排序。 SELECT * FROM student WHERE 姓名 LIKE '[张李周]%' ORDER BY 姓名 运行结果如图 8.30 所示。 图 8.30 例 8.21 运行结果 查询结果集中,包含了所有的姓“张”、“李”、“周”的学生。如果在方括号内的第一个位置 输入符号“^”,则表示取反向值。下面看一个取反向值的例题。 【例 8.22】从 Student 表中,查询除姓“张”、“李”或“周”以外的所有学生,并按姓名升序排序。 SELECT * FROM student WHERE 姓名 LIKE '[^张李周]%' ORDER BY 姓名 运行结果如图 8.31 所示。 图 8.31 例 8.22 运行结果 第 8 章 高级条件查询 ·109· 【例 8.23】从 Student 表中,查询 1—5 月份出生的所有学生,并按出生日期升序排序。 SELECT * FROM student WHERE 出生日期 LIKE '[0][1-5]%' ORDER BY 出生日期 运行结果如图 8.32 所示。 图 8.32 例 8.23 运行结果 说明:如果 SQL 运行环境为 Access,则使用感叹号(!)代替(^)。例如,集合否定[^张李周]应当写 为[!张李周]。 8.4.6 SQL Server、Access 和 Oracle 中的通配符比较 SQL Server、Access 和 Oracle 中使用的通配符有些是不一样的。SQL Server 和 Access 中允许使用 的通配符要多于 Oracle。表 8.3 对这 3 个 DBMS 中能够使用的通配符进行了汇总和比较。 表 8.3 通配符比较 SQL Server Access Oracle 说 明 百分号(%) 星号 (*) 百分号(%) 代表 0 个或多个任意字符 下划线(_) 问号 (?) 下划线(_) 代表一个任意字符 (无) 井号 (#) (无) 代表一个数字字符 方括号([]) 方括号([]) (无) 代表指定的范围 反方括号([^]) 反方括号([!]) (无) 代表指定的范围之外 虽然 Oracle 中没有方括号和反方括号,但有时可以使用逻辑表达式得到相同的效果。例如: 字段名 LIKE [a-z]% 或 字段名 LIKE [^a-z]% 可以使用逻辑表达式 字段名>='a' AND 字段名<='z' 或 字段名<='a' OR 字段名>='z' 得到相同的效果。 8.4.7 定义转义字符 前面学习了几种通配符的使用方法,知道了“%5%”代表包含 5 的所有字符串,如果想要查询最 SQL 技术与网络数据库开发详解 ·110· 后两个字符为 5%的所有字符串呢?即将“%5%”中第二个“%”看作普通字符,而不是通配符,此时 应该定义和使用转义字符。在不同的环境下,定义转义字符的方法也不同。下面学习定义转义字符的 具体方法。 1.SQL Server 环境 如果运行环境为 SQL Server,则使用 ESCAPE 关键字定义转义字符。例如,要查询最后两个字符 为百分之五(5%)的所有字符串,其 LIKE 语句为: LIKE '%5#%' ESCAPE '#' 其中, ESCAPE '#' 定义了转义字符“#”,它表示紧跟着“#”后的“%”为普通字符,而并非是通配符。 注意:只有紧跟在转义字符后面的通配符才被看作为转义字符,例如,如果上面的 LIKE 语句为: LIKE '%5#%%' ESCAPE '#' 则表示要查询的是包含百分之五(5%)的所有字符串。这里最后一个“%”仍当作通配符来使 用,只有紧跟着“#”的“%”(第二个)才被当作普通字符。 2.Access 环境 如果运行环境为 Access,则使用方括号([])定义转义字符,将需要看作普通字符的通配符,放入 方括号内即可。例如,要查询包含问号(?)的所有字符串,则在 Access 中编写 LIKE 语句为: LIKE '*[?]*' 说明:Access 中,将问号(?)当作通配符,与前面介绍的下划线(_)通配符的效果相同。 又例如,要查询所有包含“C#”的字符串,则其 LIKE 语句为: LIKE '*C[#]*' 说明:Access 中,将井号(#)也当作通配符,它代表一个数字字符。 3.Oracle 环境 如果运行环境为 Oracle,则使用反斜杠(\)作为转义字符。要查询最后两个字符为百分之五(5%) 的所有字符串,在 Oracle 中,编写其 LIKE 语句为: LIKE '%5\%' 此时需要注意,反斜杠作为转义字符时应当先将其激活。激活的方法为在 SQL Plus 中使用如下 命令: set escape \ ; 定义了转义字符后,要查询所有包含“SQBT_999”的字符串,则其 LIKE 语句为: LIKE '%SQBT\_999%'  SQL函数的说明  SQL Server 的函数  Oracle 的函数  将 NULL 更改为其他值的函数  IF…ELSE 逻辑函数 除了在 SQL 查询中使用正常的表达式外,在具体的数据库管理系统中,还可以使 用任意的内置函数或者用户编写的存储数据库函数。用户可以使用函数来执行计算或基 于输入参数的其他操作,也可以将某种数据转换成其他数据类型或显示格式。本章将通 过具体的例子介绍如何使用这些函数。 SQL 函数的使用 第 章 SQL 技术与网络数据库开发详解 ·112· 9.1 SQL 函数的说明 在介绍函数的使用之前,先应知道 SQL 函数是不通用的。SQL 函数与前面所讲的 SQL 语句不同, 它们在不同的数据库管理系统中不能通用,因为每一个数据库管理系统都有一套自己的 SQL 函数,只 有很少的函数在大多数 DBMS 中都能使用。为了让读者感受这一点,表 9.1 列出了一些例子。 表 9.1 不同 DBMS 的函数差异 功 能 Access 函数 SQL Server 函数 Oracle 函数或语句 MySQL 函数 获取字符串的某部分 MID( ) SUBSTRING( ) SUBSTR( ) SUBSTRING( ) 获取当前日期 NOW( ) GETDATE( ) SYSDATE CURDATE( ) 转换数据类型 有多个具体函数,例如 CDATE( )用于将字符 转换为日期,CSTR( ) 将数字转换为字符 CONVERT( ) 有多个具体函数,例如 TO_DATE( )用于将字符串 转换为日期,TO_CHAR( ) 将数字或日期转换为字符串 CONVERT( ) 由于 SQL 函数不通用,因此,如果在数据库应用程序的开发中使用了数据库函数,则其通用性和 移植性就会变得很差,所以在软件开发时应尽量采用程序设计语言中的函数,避免使用数据库函数。 9.2 SQL Server 的函数 本节将介绍 SQL Server 中的类型转换函数、日期函数、数值函数和字符函数,并对较经常使用的 函数举例说明。 9.2.1 类型转换函数 转换函数将具体 DBMS 的数据值转换成其他数据类型或对其进行格式化。经常用到的转换是,将 日期和数字转换成指定的字符串格式,或者将字符串转换成有效的日期或数值。 在 SQL Server 中,使用 CONVERT( )和 CAST( )两个函数转换数据类型。 1.CONVERT( )函数 CONVERT( )函数的语法格式为: CONVERT( datatype[(length)],expression,[style]) 其中,datatype 为数据类型,如果是 CHAR、VARCHAR、BINARY 或 VARBINARY 数据类型, 则可以选择 length 参数设置长度;expression 为表达式,如果要将日期型数据转换为字符型数据,还可 以使用 style 参数设置日期显示格式。style 参数的取值与日期显示格式如表 9.2 所示。 第 9 章 SQL函数的使用 ·113· 表 9.2 style 参数取值及对应日期格式 style 值(返回 yy) style 值(返回 yyyy) 标 准 显 示 格 式 0(或者 100) 默认标准 mon dd yy hh:mi AM(或 PM) 1 101 美国 mm/dd/yy 2 102 ANSI yy.mm.dd 3 103 英国/法国 dd/mm/yy 4 104 德国 dd.mm.yy 5 105 意大利 dd-mm-yy 6 106 dd mon yy 7 107 mon dd,yy 8 108 hh:mi:ss 9(或者 109) 默认标准+毫秒 mon dd,yyyy hh:mi:ss:ms AM(或 PM) 10 110 美国 mm-dd-yy 11 111 日本 yy/mm/dd 12 112 ISO Yymmdd 13(或者 113) 欧洲默认+毫秒 dd mon yyyy hh: mi:ss:ms(24 小时) 14 114 hh: mi:ss:ms(24 小时) 说明:style 参数可以取两类值,如果从第一类取值,则返回日期的年份为 2 位;如果从第二类取值, 则返回日期年份为 4 位。 当把一个日期转换为字符串时,CONVERT( )函数默认的输出格式是“mon dd yy hh:mi AM(或 PM)”。从表中可见,CONVERT( )函数将日期转换为字符串时提供了大量的日期时间显示格式,这给 用户提供了很大的方便。 表 9.3 列出了几个使用 CONVERT( )函数转换数据类型的例子,供读者参考。 表 9.3 CONVERT( )函数的例子 功 能 函 数 实 现 字符到数字 CONVERT(numeric,'15') 数字到字符 CONVERT(char,12 ) 字符到日期 CONVERT(datetime,'15-09-1977' ),CONVERT(datetime,'SEP 15,1977' ) 日期到字符 CONVERT(char,GETDATE( ) ),CONVERT(char,GETDATE( ),102 ) 十六进制到二进制 CONVERT(binary,'3C') ) 二进制到十六进制 CONVERT(char,二进制字段) 获取当前系统时间 CONVERT(char,GETDATE( ),8) 【例 9.1】从 Student 表中,查询所有学生的姓名、出生日期,并将日期转换为德国标准日期格式 的字符串显示,其中年份为 4 位。要求查询结果按出生日期升序排序。 分析:将出生日期转换为字符串,应当使用 CONVERT( )函数,因为需要按照德国日期格式显示,所 以从表 9.2 中查找设置德国标准的 style 值,又因为年份要求是 4 位,所以选择 style 参数的值为 104。下面是具体的 SELECT 语句。 SQL 技术与网络数据库开发详解 ·114· SELECT 姓名,CONVERT(CHAR,出生日期,104) AS 生日 FROM student ORDER BY 出生日期 运行结果如图 9.1 所示。 图 9.1 例 9.1 运行结果 提示:如果 SQL 运行环境为 Oracle,则设置别名时不能使用 AS 关键字,将 SELECT 中的 AS 去掉 即可。 2.CAST( )函数 CAST( )函数是 SQL92 标准函数。使用 CAST( )函数也可以转换数据类型,但是在格式化日期时间 数据方面不如 CONVERT( )函数方便。CAST( )函数的语法格式为: CAST (expression AS datatype[(length)]) 其中,expression 为表达式,datatype 为数据类型。如果是 CHAR、VARCHAR、BINARY 或 VARBINARY 数据类型,则可以选择 length 参数设置长度。 【例 9.2】从 Student 表中,查询所有学生的姓名、出生日期,并将日期转换为字符串显示。要求 查询结果按出生日期升序排序。 SELECT 姓名,CAST(出生日期 AS char) AS 生日 FROM student ORDER BY 出生日期 运行结果如图 9.2(a)所示。 从图中可以看出,出生日期已经被转换为字符串。如果只想要日期部分而不想要时间,则可以在 类型后设置长度。上面的 SELECT 语句可以写为如下形式。 SELECT 姓名,CAST(出生日期 AS char(10)) AS 生日 FROM student ORDER BY 出生日期 运行结果如图 9.2(b)所示。 本例中,因为只给“出生日期”分配了 10 个字节的长度,所以只把前面的日期部分留下,而后面 的时间部分就被自动截掉了。 注意:CAST( )函数不能改变原表字段的数据类型。 第 9 章 SQL函数的使用 ·115· (a) (b) 图 9.2 例 9.2 运行结果 9.2.2 日期函数 日期函数允许操作日期时间值。SQL Server支持的日期函数有GETDATE、DATEADD、DATEDIFF、 DATENAME 和 DATEPART 等。 1.GETDATE 函数 GETDATE 函数用于获取当前系统时间。其格式为: GETDATE ( ) 例如,在查询分析器中输入如下 SELECT 语句并运行后,即可获得当前系统时间。 SELECT GETDATE() 运行结果如图 9.3 所示。 图 9.3 使用 GETDATE 获取当前时间 2.DATEADD 函数 DATEADD 函数用于在指定日期上增加年、月、日或者时间等,其返回值为日期型数据。其格 式为: DATEADD(datepart,number,date) 其中,datepart 参数规定在日期的哪个部分(如年份、月份等)增加数值。表 9.4 列出了 datepart 参数的可用值。 表 9.4 datepart 参数的可用值 datepart 参数值 参数值可用缩写 参数值范围 Year yy, yyyy 1753~9999 quarter qq, q 1~4 Month mm, m 1~12 Day of year dy, y 1~366 SQL 技术与网络数据库开发详解 ·116· 续表 datepart 参数值 参数值可用缩写 参数值范围 Day dd, d 1~31 Week wk, ww 0~51 Weekday dw 1~7(1 为星期日) Hour hh 0~23 minute mi, n 0~59 second ss, s 0~59 millisecond ms 0~999 了解 datepart 参数的可用值后,就可以控制在日期的哪个部分增加值。例如: DATEADD(year,10,GETDATE()) 是在当前时间的“年”上增加了 10 年,并返回 10 年后的日期。而 DATEADD(month,10,GETDATE()) 是在当前时间的“月”上增加了 10 个月,并返回 10 个月后的日期。 说明:datepart 参数值也可以使用缩写。例如,DATEADD(mm,10,GETDATE())也是在当前时间上增加 10 个月。 【例 9.3】从 Student 表中,查询所有学生的姓名、出生日期、出生后的第 10000 天和出生后的第 800 个月,要求查询结果按出生日期升序排序。 SELECT 姓名, 出生日期, DATEADD(DAY,10000,出生日期) AS 出生后第 10000 天, DATEADD(MONTH,800,出生日期) AS 出生后第 800 月 FROM student ORDER BY 出生日期 运行结果如图 9.4 所示。 图 9.4 例 9.3 运行结果 3.DATEDIFF 函数 DATEDIFF 函数用于获取两个日期间的差,并返回数值数据。其格式为: DATEDIFF(datepart,date1,date2) 第 9 章 SQL函数的使用 ·117· 其中,datepart 参数的说明同上,date1 和 date2 是日期或者日期格式的字符串。 【例 9.4】从 Student 表中,查询所有学生的姓名、出生日期和年龄。要求查询结果按出生日期降 序排序。 SELECT 姓名, 出生日期, DATEDIFF(year,出生日期,GETDATE()) AS 年龄 FROM student ORDER BY 出生日期 DESC 运行结果如图 9.5 所示。 图 9.5 例 9.4 运行结果 查询语句中的 DATEDIFF 函数如下: DATEDIFF(year,出生日期,GETDATE()) 返回的是当前时间和出生日期之间的年份的差距,即年龄。如果写成如下形式: DATEDIFF(month,出生日期,GETDATE()) 则返回的是当前时间和出生日期之间的月份的差距,即返回相差多少个月。 4.DATENAME 函数 DATENAME 函数用于获取日期的一部分,并以字符串形式返回。其格式为: DATENAME (datepart,date) 其中,datepart 参数的说明同上,date 是日期或者日期格式的字符串。例如,假设当前日期为 2008 年 3 月 25 日,则 DATENAME (month,GETDATE( ))的结果为字符串'03',DATENAME (dd,GETDATE( )) 的结果为字符串'25'。 注意:假设当前日期为 2008 年 3 月 5 日,则 DATENAME (dd,GETDATE( ))返回的结果为字符串'5', 而并非是'05'。 【例 9.5】从 Student 表中,查询每个月 1 号出生的所有学生。要求查询结果按出生日期降序排序。 SELECT * FROM student WHERE DATENAME(day,出生日期)='1' ORDER BY 出生日期 DESC SQL 技术与网络数据库开发详解 ·118· 注意:DATENAME 函数返回的是字符串,因此必须与字符串('1')比较。 运行结果如图 9.6 所示。 图 9.6 例 9.5 运行结果 5.DATEPART 函数 DATEPART 函数用于获取日期的一部分,并以整数值返回。其格式为: DATEPART (datepart,date) 其中,datepart 参数的说明同上,date 是日期或者日期格式的字符串。例如,假设当前日期为 2008 年 3 月 25 日,则 DATEPART (month,GETDATE( ))的结果为数值 3,DATEPART (dd,GETDATE( ))的 结果为数值 25。 【例 9.6】从 Student 表中查询每个月 1 号出生的所有学生。要求查询结果按出生日期降序排序。 SELECT * FROM student WHERE DATEPART (day,出生日期)=1 ORDER BY 出生日期 DESC 注意:DATEPART 函数返回的是数值,因此必须与数值(1)比较。 运行结果如图 9.7 所示。 图 9.7 例 9.6 运行结果 SQL Server 中除上述日期时间函数以外,还有 YEAR、MONTH、DAY 等 3 个函数,分别用于获 取日期数据的年份、月份和日期部分,这 3 个函数的返回值都是数值型。 9.2.3 数学函数 数学函数允许操作数值数据。表 9.5 中列出了常用的 SQL Server 数学函数及其说明供读者参考。 表 9.5 数学函数及其说明 函 数 参 数 说 明 ABS (numeric_表达式) 绝对值 ACOS (float_表达式) 返回以弧度表示的角度值。该角度值的余弦为给定的 float 表达式, 本函数亦称反余弦 ASIN (float_表达式) 返回以弧度表示的角度值。该角度值的正弦为给定的 float 表达式, 亦称反正弦 第 9 章 SQL函数的使用 ·119· 续表 函 数 参 数 说 明 ATAN (float_表达式) 返回以弧度表示的角度值。该角度值的正切为给定的 float 表达式, 亦称反正切 ATN2 (float_表达式,float_表达式) 返回以弧度表示的角度值。该角度值的正切介于两个给定的 float 表 达式之间,亦称反正切 COS (float_表达式) 返回给定表达式中给定角度(以弧度为单位)的三角余弦值 SIN (float_表达式) 返回给定角度(以弧度为单位)的三角正弦值(近似值) COT (float_表达式) 返回给定 float 表达式中指定角度(以弧度为单位)的三角余切值 TAN (float_表达式) 返回 float 表达式的正切值 CEILING (numeric_表达式) 返回大于或等于所给数字表达式的最小整数 DEGREES (numeric_表达式) 当给出以弧度为单位的角度时,返回相应的以度数为单位的角度 EXP (float_表达式) 返回所给的 float 表达式的指数值 FLOOR (numeric_表达式) 返回小于或等于所给数字表达式的最大整数 LOG (float_表达式) 返回给定 float 表达式的自然对数 LOG10 (float_表达式) 返回给定 float 表达式的以 10 为底的对数 PI ( ) 返回π的常量值 POWER (numeric_表达式 , y) 返回给定数字表达式的 y 次方 RADIANS (numeric_表达式) 对于在数字表达式中输入的度数值返回弧度值 RAND ([seed]) 返回 0~1 的随机 float 值 ROUND (numeric_表达式 , length) 返回数字表达式并四舍五入为指定的长度或精度 SIGN (numeric_表达式) 返回给定表达式的正(+1)、零(0)或负(-1)号 SQRT (float_表达式) 返回给定表达式的平方根 【例 9.7】使用数学函数,计算 30°角的正弦值。 分析:首先使用 RADIANS 函数计算 30°的弧度值,其次对弧度值使用 SIN 函数求正弦值,最后对结 果进行四舍五入计算。在查询分析器中输入如下 SELECT 语句并运行。 SELECT ROUND(SIN(RADIANS(30.0)),1) AS "30°的正弦值" 说明:将 AS 后的别名(30°的正弦值)放入双引号的原因是别名中有数字。 运行结果如图 9.8 所示。 图 9.8 例 9.7 运行结果 9.2.4 字符函数 字符函数允许操作字符数据。表 9.6 中列出了常用的 SQL Server 字符函数及其说明供读者参考。 SQL 技术与网络数据库开发详解 ·120· 表 9.6 字符函数及其说明 函 数 参 数 说 明 ASCII (char_表达式) 返回字符表达式结果的最左边字符的 ASCII 码 CHAR (integer_表达式) 返回 ASCII 码为指定整数的字符 CHARINDEX (char_表达式 1,char_表达式 2[,start]) 返回字符表达式 1 在字符表达式 2 中的起始位置。start 参数指定从字符表达式 2 的哪个位置开始向后寻找 DIFFERENCE (char_表达式,char_表达式) 比较两个字符串的相似性,返回从 0~4 的值,值为 4 时是最好的匹配 LEFT (char_表达式,integer_表达式) 返回字符串左面的指定个数的字符 LOWER (char_表达式) 将字符串表达式中的所有大写字母全部转换成小写 字母 LTRIM (char_表达式) 删除字符串左边所有的空格 REPLICATE (char_表达式,integer_表达式) 以指定的次数重复字符表达式 REVERSE (char_表达式) 返回字符表达式的逆序 RIGHT (char_表达式,integer_表达式) 返回字符串右面的指定个数的字符 RTRIM (char_表达式) 删除字符串右边所有的空格 SOUNDEX (char_表达式) 返回由 4 个字符组成的代码(SOUNDEX)以评估两 个字符串的相似性 SPACE (integer_表达式) 返回一个由重复空格组成的字符串。空格数等于 ,若整数表达式为负数,则返回一个 空字符串 STR (float_expression [ , length [ , decimal ] ]) 由数字数据转换来的字符数据。length 是总长度,包 括小数点、符号、数字或空格,默认值为 10。decimal 是小数点右边的位数 STUFF (char_表达式,start,length,char_表 达式) 删除指定长度的字符并在指定的起始点插入另一组 字符 SUBSTRING (表达式,start,length) 返回表达式中 start 位置开始的 length 长度的子串,该 子串可能是字符串,也可能是二进制字符串 UPPER (char_表达式) 将字符串表达式中的所有小写字母全部转换成大写 字母 为了让读者感受使用字符函数查询数据的方便,在此使用一个 Foreign_teacher 表(外籍教师表) 举例说明,如表 9.7 所示。 表 9.7 Foreign_teacher 表 tid tname sex country birth hiredate tel email 0001 Tom Green m USA 1967-1-21 2003-8-15 13722112908 tomcat@yahoo.com.cn 0002 Jack White m UK 1972-5-1 2006-3-10 13722112903 jack111@sina.com 0003 Marry Yang f Canada 1977-12-30 2006-3-10 13722112905 marry_771230@yahoo.com.cn 0004 Siqinbater m Mongolia 1981-9-14 2008-2-20 13722112906 brjdsiqin@yahoo.com.cn 第 9 章 SQL函数的使用 ·121· 续表 tid tname sex country birth hiredate tel email 0005 Napoleon m France 1961-10-12 2005-6-30 13722111840 NULL 0006 Gadameren m Germany 1968-4-6 2001-2-10 13722115566 NULL 0007 Wulanqiqige f USA 1979-9-30 2007-12-1 13722119999 wulan@163.com 创建 Foreign_teacher 表结构的 SQL 语句和插入记录的 SQL 语句如下所示。 CREATE TABLE foreign_teacher ( tid char(6) NOT NULL, tname char(20) NOT NULL, sex char(1) NOT NULL, country varchar(30) NOT NULL, birth smalldatetime, hiredate smalldatetime, tel char(15), email varchar(80) ) INSERT INTO foreign_teacher (tid, tname, sex, country, birth , hiredate, tel, email) VALUES ('0001', ' TomGreen ', 'm', 'USA', '1967-1-21', '2003-8-15', '13722112908', ' tomcat@yahoo.com.cn ') …… 【例 9.8】从 Foreign_teacher 表中,查询 tom green 老师的联系电话(tel)和电子邮件(email)。 分析:人们经常会忽视英文字母的大小写,例如,将 Tom Green 写为 tom green 等,此时,如果数据库 管理系统没有自动转换匹配的功能,则会将这两个字符串看作是不同人的姓名,从而导致查询 出错。为了解决这类问题,应当将数据库中的字符串的所有字母转换为大写(或小写)字母, 然后与大写(小写)字母的字符串比较。例如,可以使用下面的 SELECT 语句。 SELECT tname,tel,email FROM foreign_teacher WHERE UPPER(tname)='TOM GREEN' SQL 技术与网络数据库开发详解 ·122· 说明:SQL Server 2000 可自动转换大小写字母进行匹配,但为了保险起见,查询英文字符串时建议使 用上述方法进行查询。 运行结果如图 9.9 所示。 图 9.9 例 9.8 运行结果 【例 9.9】从 Foreign_teacher 表中查询所有教师的姓名(tname)、国家(country)、雇佣日期(hiredate) 和联系电话(tel),并将姓名和国家合并为一列显示。 SELECT tname+'('+country+')' AS "姓名(国家)", hiredate AS 雇佣日期, tel AS 联系电话 FROM foreign_teacher 说明:将 AS 后的别名(姓名(国家))放入双引号的原因是别名中含有圆括号。 运行结果如图 9.10 所示。 图 9.10 例 9.9 运行结果 1 从图 9.10 中看到姓名和国家之间的距离较大,这种问题由姓名(tname)字段后的尾随空格引起, 处理的方法是使用 RTRIM 函数将姓名的尾随空格去掉,然后再拼接,如下所示。 SELECT RTRIM(tname)+'('+country+')' AS "姓名(国家)", hiredate AS 雇佣日期, tel AS 联系电话 FROM foreign_teacher 运行结果如图 9.11 所示。 图 9.11 例 9.9 运行结果 2 在查询数据时,会遇到这样一种问题——要查询所有德国籍教师时,记错了德国的英文名字,将 Germany 错记为 Germeny,从而查询出错。要处理这类问题,可以使用 SOUNDEX 函数,因为该函数 能对字符串进行发音比较而不是字母比较。 第 9 章 SQL函数的使用 ·123· 【例 9.10】从 Foreign_teacher 表中,查询所有德国籍教师的姓名(tname)、出生日期(birth)和 电子邮件(email)。 SELECT tname,birth,email FROM foreign_teacher WHERE SOUNDEX(country)=SOUNDEX('Germeny') 运行结果如图 9.12 所示。 图 9.12 例 9.10 运行结果 可见,即使拼错了德国的英文名字,使用 SOUNDEX 函数还是能够查到正确的结果。但是,使用 SOUNDEX 函数查找汉字就不行了,如下面的例题所示。 【例 9.11】从 Student 表中查询名叫“张三”的学生的所有信息,将“张三”故意写错为读音相似 的“张叁”,以便测试 SOUNDEX 函数对汉字的支持。 SELECT * FROM student WHERE SOUNDEX(姓名)= SOUNDEX('张叁') 运行结果如图 9.13 所示。 图 9.13 例 9.11 运行结果 从运行结果可以看出 SOUNDEX 函数并不支持汉字的读音比较。 9.3 Oracle 的函数 本节将介绍 Oracle 中的类型转换函数、日期函数、数值函数和字符函数,并对较经常使用的函数 举例说明。 9.3.1 类型转换函数 Oracle 中的类型转换函数要比 SQL Server 的多一些。表 9.8 列出了 Oracle 的类型转换函数及其简 SQL 技术与网络数据库开发详解 ·124· 单说明供读者参考。 表 9.8 Oracle 的类型转换函数 函 数 参 数 说 明 CHARTOROWID (string) 将一个 AAAAAAAA.BBBB.CCCC 格式的字符串转换为 ROWID 类型 CONVERT (string,目标字符集,源字符集) 将源字符集的字符串转换为目标字符集的字符串 HEXTORAW (string) 将一个用字符串表示的 16 进制数转换成其字节值 RAWTOHEXT (raw_value) 将一个原始列值转换成 16 进制字符串 ROWIDTOCHAR (rowid) 将 ROWID 伪列的值转换为可显示字符串 TO_CHAR (number[,format]) 将一个数值转换成字符串 TO_CHAR (date,format) 按照 format 格式,将一个日期转换为字符串 TO_DATE (string,format) 按照 format 格式,将一个字符串转换为日期 TO_LABEL (string,format) 将字符串转换成 MLSLABEL 数据类型 TO_MULTI_BYTE (string) 将一个单字节字符串转换成支持多字节字符集语言中的 多字节字符串 TO_NUMBER (string[,format]) 将一个数字字符串转换为相应数值 TO_SINGLE_BYTE (string) 将多字节字符转换成相应的单字节字符 下面详细介绍经常使用的 TO_CHAR 和 TO_DATE 函数。 1.TO_CHAR 函数 TO_CHAR 函数可以将一个数值或者日期转换为指定格式的字符串。 (1)将数值转换为字符串 使用 TO_CHAR 函数将数值转换为字符串的语法格式如下所示。 TO_CHAR(number[,format]) 如果不指定格式(format),Oracle 将会把 number 转换成最简单的字符串形式,如果是负数则在 前面加一个减号(-)。不过在多数情况下,用户还是想以特定的格式显示 number,因此,需要设置 format 参数。表 9.9 列出了绝大多数 Oracle 可用的数值格式,并通过例子说明一个给定数字的结果字 符串形式。 表 9.9 Oracle 的数值格式模型 元 素 说 明 示 例 值 结 果 9 返回指定位数的数值,前导 0 显示为空格 9999 128 -256 1234567 456.655 '128' '-256' '####' '457' 9 插入小数点 9999.99 128 -256 1234567 456.655 '128.00' '-256.00' '#######' '456.66' 第 9 章 SQL函数的使用 ·125· 续表 元 素 说 明 示 例 值 结 果 9 在结果字符串的指定位置插入 逗号 9,999,999 128 1234567 '0.68' ' 128' '1,234,567' ' 1' $ 返回值前面加一个美元符号 $99,999 128 '$128' B 结果的整数部分,如果是 0 就显 示成空格 B9999.9 128 -256 0.44 '128.0' '-256.0' .4 MI 返回末尾带减号的负数 9999MI 128 -256 '128' '256-' S9999 128 -256 '+128' '-256' S 返回带有正负号的数值 9999S 128 -256 '128+' '256-' PR 用尖扩号包围负数 9999PR 128 -256 '128' '<256>' D 在指定位置插入小数点 9999D99 128 -256 76.238 '128.00' '-256.00' '76.24' G 在当前位置插入分组符 9G999 128 -256 -1234 '128' '-256' '-1,234' C 在指定位置返回 ISO 货币符号 C999 128 USD128 L 在指定位置返回国家货币符号 L9,999 1234 $1,234 EEEE 以科学计数法表示数值 9.9EEEE 27 128 0.078 2.7E+01 1.3E+02 7.8E-02 (2)将日期转换为字符串 使用 TO_CHAR 函数将日期转换为字符串的语法格式如下所示。 TO_CHAR(date,format) TO_CHAR 函数按 format 参数指定的格式将日期转换成相应的字符串形式。表 9.10 列出了 Oracle 的日期格式化元素。 表 9.10 Oracle 的日期格式化元素 元 素 说 明 AD(或 A.D.) AD(或 A.D.)指示符 AM(或 A.M.) AM(或 A.M.)指示符 SQL 技术与网络数据库开发详解 ·126· 续表 元 素 说 明 BC(或 B.C.) BC(或 B.C.)指示符 CC 日期的世纪部分 D 星期几(1~7) DAY 星期中每一天的名字 DD 月中的天数(1~31) DDD 年中的天数(1~365) DY 星期几的缩写(SUN~SAT) IW ISO 标准的年中的星期 IYY,IY I ISO 年的最后三、二、一位 IYYY ISO 年 HH(或 HH12) 小时(1~12) HH24 小时(0~23) MI 分钟(0~59) MM 月份(1~12) MONTH 月份名 MON 月份名的缩写 RM 月份的罗马数字表示(I~XII) RR 年的最后两位 Q 年的季度 SS 秒(0~59) SSSSS 从午夜计算的秒数(0~86399) W 月中的星期数(1~5) WW 年中的星期数(1~53) Y 年份的最后 1 位 YY 年份的最后 2 位 YYY 年份的最后 3 位 YYYY 年份的最后 4 位 【例 9.12】从 Foreign_teacher 表中,查询所有 2007 年以前雇佣的外籍教师的教师编号(tid)、姓 名(tname)和国家(country)。 SELECT tid 教师编号, tname 姓名, country 国家 FROM Foreign_teacher WHERE TO_NUMBER(TO_CHAR(hiredate,'YYYY'))<2007 注意:在 Oracle 中给字段取别名时,不能使用“AS”关键字,直接用空格隔开即可。 运行结果如下所示。 第 9 章 SQL函数的使用 ·127· 教师编号 姓名 国家 ------------------------------------------------- 0001 Tom Green USA 0002 Jack White UK 0003 Marry Yang Canada 0005 Napoleon France 0006 Gadameren Germany 本例中首先使用 TO_CHAR 函数提取 hiredate 的年份并转换为字符串,然后使用 TO_NUMBER 函 数将得到的字符串转换为数值与 2007 比较。 2.TO_DATE 函数 TO_ DATE 函数根据给定的格式将一个字符串转换成日期值,其语法格式如下所示。 TO_ DATE(string,format) 表 9.10 中的掩码元素同样适合 format 参数。 【例 9.13】从 Foreign_teacher 表中,查询所有 60 年代出生的教师姓名、出生日期和国家,并按生 日升序排序。 SELECT tname 姓名, birth 生日, country 国家 FROM Foreign_teacher WHERE birth BETWEEN TO_DATE('1960-1-1 ', 'yyyy-mm-dd ') AND TO_DATE('1969-12-31 ', 'yyyy-mm-dd ') ORDER BY birth 运行结果如下所示。 姓名 生日 国家 ------------------------------------------------------- Napoleon 12-10 月-61 France Tom Green 121-1 月-67 USA Gadameren 06-4 月-68 00:00:00 Germany 本例中使用了 TO_DATE 函数将日期格式的字符串转换成日期型数值与 birth 进行了比较。 9.3.2 日期函数 Oracle 有 ADD_MONTHS 、LAST_DAY 、MONTHS_BETWEEN、NEW_TIME、NEXT_DAY、 ROUND、TRUNC 等日期函数,下面详细介绍这些函数的内容。 1.ADD_MONTHS 函数 ADD_MONTHS 函数的格式为: ADD_MONTHS (date,number) 该函数用于在参数 date 上加上 number 个月返回一个新月值。如果 number 为负数,则返回值为 date SQL 技术与网络数据库开发详解 ·128· 之前几个月的日期。下面的例子返回 2008 年 3 月 27 日的 6 个月以后的日期。 SELECT ADD_MONTHS (TO_DATE('2008-3-27', 'yyyy-mm-dd '),6) FROM dual 又例如,下面的例子返回 2008 年 3 月 27 日 3 个月以前的日期。 SELECT ADD_MONTHS (TO_DATE('2008-3-27' , 'yyyy-mm-dd '),-3) FROM dual 2.LAST_DAY 函数 LAST_DAY 函数的格式为: LAST_DAY (date) 该函数用于获取 date 所在月份最后一天的日期。下面的例子返回 2015 年 2 月份的最后一天的日期。 SELECT LAST_DAY (TO_DATE('2015-02-01', 'yyyy-mm-dd ')) FROM dual 3.MONTHS_BETWEEN 函数 MONTHS_BETWEEN 函数的格式为: MONTHS_BETWEEN (date1,date2) 该函数用于获取两个日期 date1 和 date2 之间的月份。如果两个日期月份内的天数相同,例如两个 都是某月的 20 日,则该函数会返回一个整数;否则,返回一个带有小数的数值,就是以每天为 1/31 月来计算月中剩余的天数。如果 date2 比 date1 早(date1>date2),则返回负数。 【例 9.14】从 Foreign_teacher 表中查询所有至少工作 3 年的教师姓名、雇佣日期和国家,并按雇 佣日期升序排序。 SELECT tname 姓名, hiredate 雇佣日期, country 国家 FROM Foreign_teacher WHERE MONTHS_BETWEEN (hiredate,SYSDATE)>=12*3 ORDER BY hiredate 说明:Oracle 中使用 SYSDATE 获取当前系统时间。 运行结果如下所示。 姓名 雇佣日期 国家 -------------------------------------------------------- Gadameren 10-2 月-01 Germany Tom Green 15-8 月-03 USA 如果 SQL 运行环境为 SQL Server,则上面 SELECT 语句的条件表达式 MONTHS_BETWEEN (hiredate,SYSDATE)>=12*3 被替换为 DATEDIFF(month,hiredate,GETDATE())>=12*3 第 9 章 SQL函数的使用 ·129· 即可。 4.NEW_TIME 函数 NEW_TIME 函数的格式为: NEW_TIME (date,zone1,zone2) 该函数用于将 zone1 时区的日期时间 date 转换成 zone2 时区的日期时间。表 9.11 列出了 Oracle 所 有有效的时区供读者参考。 表 9.11 Oracle 有效时区 代 码 描 述 ADT 大西洋夏时制时间 AST 大西洋标准时间 BDT 白令海夏时制时间 BST 白令海标准时间 CDT 中部夏时制时间 CST 中部标准时间 EDT 东部夏时制时间 EST 东部标准时间 GMT 格林威治标准时间 HDT 阿拉斯加/夏威夷夏时制时间 HST 阿拉斯加/夏威夷标准时间 MDT 山区夏时制时间 MST 山区标准时间 NST 纽芬兰标准时间 PDT 太平洋夏时制时间 PST 太平洋标准时间 YDT 育空夏时制时间 YST 育空标准时间 5.NEXT_DAY 函数 NEXT_DAY 函数的格式为: NEXT_DAY(date,day) 该函数返回离指定日期(date)最近的星期(day)的日期。例如,下面的例子返回离 2010 年 5 月 4 日最近的星期一的日期。 SELECT NEXT_DAY (TO_DATE('2010-05-04', 'yyyy-mm-dd '),2) FROM dual 运行结果为: NEXT_DAY(TO_DA ------------------------ 03-5 月-10 SQL 技术与网络数据库开发详解 ·130· 说明:在 Oracle 中,星期日属于每个星期的第一天,所以星期一为 2。 6.ROUND 函数 ROUND 函数的格式为: ROUND (date,format) 该函数能够把 date 四舍五入到最接近格式元素指定的形式。例如,如果想把当前时间(2007-3-27 3:22:32)四舍五入到最近的小时,可以用如下查询语句。 SELECT ROUND (SYSDATE,'HH') FROM dual 运行结果如下所示。 ROUND (SYSDATE,'HH') --------------------------------- 2007-03-27 3:00:00 说明:Oracle 的日期格式默认为“DD-MON-YY”,如果想改为“yyyy-mm-dd hh24:mi:ss”,则应当使 用如下语句更改会话。 ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'; 9.3.3 数值函数 表 9.12 中列出了常用的 Oracle 数值函数及其说明供读者参考。 表 9.12 常用 Oracle 数值函数及其说明 函 数 参 数 说 明 ABS (number) 返回绝对值 CEIL (number) 返回与给定参数相等或比给定参数大的最小整数 COS、SIN、TAN (number) 返回给定角度(以弧度为单位)的三角余弦值、正弦值和正切值 COSH、SINH、TANH (number) 返回给定角度的反余弦值、反正弦值和反正切值 EXP (number) 返回所给值的指数值 FLOOR (number) 返回与给定参数相等或比给定参数小的最大整数 LN (number) 返回给定参数的自然对数 LOG (base, number) 返回给定数值的以 base 为底的对数 MOD (n,m) 返回 n 除 m 的模 POWER (x , y) 返回 x 的 y 次方 ROUND (number , length) 返回 number,并四舍五入为指定的长度或精度 SIGN (number) 返回给定数值的正(+1)、零(0)或负(-1)号 SQRT (number) 返回给定数值的平方根 TRUNC (number , decimal-pluces) 返回值为按 decimal-pluces 截断的给定数值 第 9 章 SQL函数的使用 ·131· 【例 9.15】使用 POWER 函数求 9 的 6 次方。 SELECT POWER(9,6) FROM dual; 运行结果如下所示。 POWER(9,6) ------------------- 531441 9.3.4 字符函数 表 9.13 中列出了常用的 Oracle 字符函数及其说明供读者参考。 表 9.13 常用 Oracle 字符函数及其说明 函 数 参 数 说 明 CHR (number) 返回与所给数值参数相等的字符 CONCAT (string1,string2) 返回字符串连接结果 INITCAP (string) 该函数将参数的第一个字母变为大写,其他的字母则转换 成小写 INSTR (input_string,search_string[,n[,m]]) 从输入字符串的第 n 个字符开始查找搜索字符串的第 m 次 出现 LENGTH (string) 返回输入字符串的字符数 LOWER (string) 将输入字符串全部转换为小写字母 LPAD (string, n [,pad_chars]) 在输入字符串的左边填充上 pad_chars 指定的字符,将其拉 伸至 n 个字符长 LTRIM (string) 从输入字符串中删除所有前导空格,即左边的空格 NLSSORT (string) 对输入字符串的各个字符进行排序 REPLACE (string , search_string [,replace_string]) 将输入字符串中出现的所有 search_string 都替换为 replace_string,如果不指定 replace_string,则删除全部 search_string RPAD (string, n [,pad_chars]) 在输入字符串的右边填充上 pad_chars 指定的字符,将其拉 伸至 n 个字符长 RTRIM (string) 从输入字符串中删除右边的所有空格 SOUNDEX (string) 返回所有在发音上与输入字符串相似的字符串 SUBSTR (string , start [, length]) 返回输入字符串中从第 start 位开始 length 长的一部分 UPPER (string) 将输入字符串全部转换成大写字母 【例 9.16】使用 SUBSTR 函数截取身份证号码中的生日信息。 SELECT SUBSTR ('150102197709142019',7,6) FROM dual; 运行结果如下所示。 SUBSTR (' SQL 技术与网络数据库开发详解 ·132· -------------- 19770914 9.4 将 NULL 更改为其他值的函数 数据库操作中,有时需要将表中某字段的 NULL 值全部更改为其他值,这样有利于进行各种运算 和统计。对于这种功能,DBMS 给用户提供了相应的函数,只是在不同的 DBMS 中函数的名称和用法 稍有不同。 9.4.1 SQL Server 的 ISNULL 函数 SQL Server 中的 ISNULL 函数可以将 NULL 值更改为其他值,其语法如下所示。 ISNULL ( check_expression , replacement_value ) 具体说明如下: check_expression:将被检查是否为 NULL 值的表达式。check_expression 可以是任何类型的。 replacement_value:当 check_expression 为 NULL 值时将返回该表达式。replacement_value 必 须与 check_expresssion 具有相同的数据类型。 【例 9.17】试验 SQL Server 中的 ISNULL 函数。假设有一个数据表 TestNull,如表 9.14 所示。 表 9.14 TestNull 表内容 C1 C2 10 NULL 20 200 NULL NULL 其创建语句和插入语句分别如下所示。 CREATE TABLE testnull ( c1 int, c2 int ); INSERT INTO testnull VALUES (10,NULL); INSERT INTO testnull VALUES (20,200); INSERT INTO testnull VALUES (NULL,NULL); 第 9 章 SQL函数的使用 ·133· 下面的语句将 C2 字段的所有 NULL 值显示为 0。 SELECT c1, ISNULL(c2,0) FROM testnull; 运行结果如图 9.14 所示。 图 9.14 查询 TestNull 表的结果 注意:上面的查询语句,并不能将 C2 字段的 NULL 值更改为 0,而只是将 NULL 值显示为 0。 说明:Access 中相对应 ISNULL 函数的是 NZ 函数。 9.4.2 Oracle 的 NVL 函数 Oracle 中对应 SQL Server 的 ISNULL 函数的是 NVL 函数。下面通过一个例题说明其用法。 【例 9.18】试验 Oracle 中的 NVL 函数,将 C2 字段的所有 NULL 值显示为 1000。 SELECT c1, nvl(c2,1000) FROM testnull; 运行结果如下所示。 C1 NVL(C2,1000) -------------- ------------------ 10 1000 20 200 30 1000 9.5 IF…ELSE 逻辑函数 IF…ELSE 逻辑函数指的是根据判断条件返回不同结果的函数。Oracle 中的 DECODE 和 SQL Server 中的 CASE 就是这种函数。 9.5.1 DECODE 函数 Oracle 中的 DECODE 是一个比较重要的函数。可以使用该函数翻译数据,也可以动态地使查询以 一种特殊的方式执行。下面是 DECODE 函数的基本语法。 DECODE(表达式,值 1,返回值 1,值 2,返回值 2,…,值 n,返回值 n,默认返回值) SQL 技术与网络数据库开发详解 ·134· 具体说明如下: 当“表达式=值 1”,则 DECODE 函数的返回值为“返回值 1”;当“表达式=值 2” ,则 DECODE 函数的返回值为“返回值 2”,依此类推。 如果表达式不与任何值相等,则 DECODE 函数的返回值为“默认返回值”。 DECODE 函数最明显的用途是将查询到的值翻译成一种更具描述性的值。 【例 9.19】查询 foreign_teacher 表中美国籍外教的姓名和性别,并使用 DECODE 函数,将性别 (sex)字段的值“m”显示为“男”,将“f”显示为“女”。 SELECT tname 外教姓名, DECODE(sex , 'm' , '男' , 'f' , '女' , '错误数据') 性别 FROM foreign_teacher WHERE country='USA' ORDER BY sex; 运行结果如下所示。 外教姓名 性别 ----------------- --------- Tom Green 男 Wulanqiqige 女 说明:目前 Oracle 的新版本中也加入了 CASE 函数,其功能与第 10 章 SQL Server 的 CASE 函数的用 法相同。 9.5.2 CASE 函数 SQL Server 中对应 DECODE 函数的是 CASE 函数,其语法如下所示。 CASE WHEN 条件表达式 1 THEN 返回值 1 WHEN 条件表达式 2 THEN 返回值 2 …… WHEN 条件表达式 n THEN 返回值 n ELSE 返回值 n+1 END 具体说明如下: 当“条件表达式 1”成立时,CASE 函数的返回值为“返回值 1”;当“条件表达式 2”成立时, CASE 函数的返回值为“返回值 2”,依此类推。 如果条件表达式 1~n 都不成立,则 CASE 函数的返回值为“返回值 n+1”。 【例 9.20】查 询 foreign_teacher 表中美国籍外教的姓名和性别,并使用 CASE 函数,将性别(sex) 字段的值“m”显示为“男”,将“f”显示为“女”。 SELECT tname 外教姓名, 性别 = CASE 第 9 章 SQL函数的使用 ·135· WHEN sex = 'm' THEN '男' WHEN sex = 'f' THEN '女' ELSE '错误数据' END FROM foreign_teacher WHERE country='USA' ORDER BY sex; 运行结果如图 9.15 所示。 图 9.15 例 9.20 运行结果 CASE 函数实际上还有一种形式,例如,上面的 SELECT 语句还可以写为如下形式。 SELECT tname 外教姓名, 性别 = CASE sex WHEN 'm' THEN '男' WHEN 'f' THEN '女' ELSE '错误数据' END FROM foreign_teacher WHERE country='USA' ORDER BY sex;

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享文档获得金币 ] 1 人已下载

下载文档

相关文档