6.2 Transact-SQL基础

 

6.2  Transact-SQL基础

SQL Server中提供了多种图形和命令行工具，用户可以使用不同的方法来访问数据库，不论是使用企业管理器、使用用户自己编写的应用程序还是使用开发的Web网页来访问数据库，使用这些工具的核心都是Transact-SQL语言。

在讲述Transact-SQL语法和用法之前，首先要了解其语法表示规则。

·    大写：Transact-SQL 关键字。

·    |（竖线）：分隔括号或大括号内的语法项目。只能选择一个项目。

·    [ ]（方括号）：可选语法项目。不必键入方括号。

·    {}（大括号）：必选语法项。不要键入大括号。

·    [ ,…n ]：表示前面的项可重复n次。每一项由逗号分隔。

·    [ ...n ]：表示前面的项可重复n次。每一项由空格分隔。

·  <标签>：语法块的名称。此规则用于对可在语句中的多个位置使用的过长语法或语法单元部分进行分组和标记。

本节讲述SQL语句时，将主要使用SQL查询分析器来输入和执行Transact-SQL语言，从而使用户能够熟悉和熟练地使用SQL语言。

6.2.1  查询分析器

在SQL查询分析器中，用户可以直接输入Transact-SQL语句，执行语句可在结果窗口中查看结果。用户也可以打开包含Transact-SQL语句的文本文件，从而执行这些语句并查看结果。SQL查询分析器可以完成以下动作。

·    创建查询和其他SQL脚本，并针对SQL Server数据库执行它们。

·    由预定义脚本快速创建常用数据库对象。

·    快速复制现有数据库对象。

·    在参数未知的情况下执行存储过程。

·    调试存储过程，调试查询性能问题。

·    在数据库内定位对象（对象搜索功能），或查看和使用对象。

·    快速插入、更新或删除表中的行。

·    为常用查询创建键盘快捷方式。

·    向“工具”菜单添加常用命令。

可以通过选择“开始”→“所有程序”→Microsoft SQL Server→“查询分析器”菜单项直接运行SQL查询分析器，如图6-1所示。

图6-1  从开始菜单启动查询分析器

也可以从SQL Server企业管理器内运行查询分析器，选择企业管理器中的“工具”→“SQL查询分析器”菜单项即可运行，如图6-2所示。

图6-2  从企业管理器启动查询分析器

另外还可以通过运行isqlw命令直接运行SQL 查询分析器，在“开始”菜单中选择“运行”菜单项，弹出如图6-3所示的“运行”对话框，并在其中的文本框中直接输入“isqlw”命令即可。

不论采用何种方式，最终启动的程序是一致的，都会弹出如图6-4所示“连接到SQL Server”对话框。如果要连接到本机的SQL Server，在“SQL Server”标签后的文本框中直接使用“.”即可，如果是要连接远程的SQL Server，则在其后的文本框中输入远程计算机的IP地址。然后在“连接使用”选项组中选择对应的身份验证方式。

      

图6-3  在运行中启动查询分析器                       图6-4  连接到SQL Server

单击“确定”按钮，如果身份验证正确，则会成功连接到服务器，并进入查询分析器，如图6-5所示。

图6-5  查询分析器

SQL查询分析器分为两个窗口，左侧的“对象浏览器”窗口和右侧的“查询”窗口。

在左侧“对象浏览器”窗口中，“对象”选项卡中显示了目前连接SQL Server服务器中所包含的数据库以及数据库对象。此时若要更改所连接的数据库服务器，可选择“文件”→“连接”菜单项，即可弹出如图6-4所示对话框，可以重新选择服务器进行连接。单击“对象浏览器”窗口下方的“模块”标签，则会激活“模块”选项卡，其中显示了查询分析器中包含的各种Transact-SQL语句模版，可以使用这些模版方便快速地创建用户自己的SQL语句，也可以单击工具栏中的“新建查询”按钮右侧的下三角按钮，在弹出的下拉菜单中选择合适的SQL语句模版，从而弹出对应模块的SQL语句。

在右侧的“查询”窗口中，可以直接输入SQL语句，也可以通过单击工具栏中的“打开”按钮或选择主菜单中的“文件”→“打开”菜单项，从而打开一个SQL脚本文件（.sql后缀名的文件），并在窗口中显示其中全部内容，此时单击工具栏中的“执行查询”按钮，或者按F5键，即可执行其中的SQL语句，执行完成后，将在窗口的下部显示查询结果或者出错信息。另外，在执行SQL语句之前，为了防止执行过程中错误发生，还可以单击工具栏中的“分析查询”按钮，从而检查其中的语句是否有语法错误。

6.2.2  数据库的操作语句

使用SQL语句可以创建数据库，并对其进行修改和删除。下面简单介绍有关数据库操作的SQL语句。

1．创建数据库

在SQL语言中，要创建一个新的数据库，可以使用CREATE DATABASE语句来实现，其语法格式如下：

CREATE DATABASE <新数据库名>

[ON (<数据文件描述项>)]

[LOG ON (<日志文件描述项>)]

其中数据库名是必须的，其他参数都是可选的。Transact-SQL语句可以定义多个数据页的属性，创建用户定义的文件组，将辅助数据文件存放在专门的文件组中，以及定义多个事务处理日志文件的属性。

而数据或日志文件描述项的语法格式如下：

(

[ NAME = <逻辑名>,]

FILENAME = <文件路径及名称>

[ , SIZE = <文件大小>]

[ , MAXSIZE = {<最大文件大小>|UNLIMITED}]

[ , FILEGROWTH = <增长增量>]

)

其中文件的路径与名称是必须的，而增长增量可以是以MB、KB、GB或百分比（%）为单位。

使用一条CREATE DATABASE语句即可创建数据库以及存储该数据库的文件。SQL Server是分两步实现这一过程。首先，使用model数据库的副本初始化数据库及其原数据库；然后，使用空页填充数据库的剩余部分，除了包含记录数据库中空间使用情况以外的内部数据页。

如果仅使用“CREATE DATABASE <数据库名>”而不带有其他任何参数，则新数据库的大小将与model数据库大小完全一致。例如，建立一个名为new的数据库，可以输入并执行如下SQL语句：

CREATE DATABASE new

执行该语句后，在窗口下方提示如下消息，如图6-6所示。

CREATE DATABASE 进程正在磁盘'new' 上分配0.75 MB 的空间。

CREATE DATABASE 进程正在磁盘'new_log' 上分配0.49 MB 的空间。

图6-6  创建new数据库

而如果要手动指定数据文件和日志文件，则需要指定相应的参数。如要将new1数据库的数据文件设置为“C:\new1.mdf”，大小为10MB，最大为100MB，每次增长10MB。事务日志为“C:\new1-log.mdf”，大小为10MB，最大为50MB，每次增长5MB。则可以执行如下SQL语句：

CREATE DATABASE new1

ON (

    NAME=new1,

    FILENAME='c:\new1.mdf',

    SIZE=10MB,

    MAXSIZE=100MB,

    FILEGROWTH=10MB

)

LOG ON (

    NAME=new1log,

    FILENAME='c:\new1-log.mdf',

    SIZE=10MB,

    MAXSIZE=50MB,

    FILEGROWTH=5MB

)

系统将会在窗口下方提示相应的数据文件和日志文件的名称和大小，如图6-7所示。

图6-7  创建new1数据库

在左侧的“对象浏览器”窗口中，右击新建数据库所在的SQL Server服务器，在弹出的菜单中选择“刷新”菜单项，即可在数据库列表中看到新建立的数据库new和new1。同时，在企业管理器中，用户也可以通过单击当前SQL Server服务器下的“数据库”节点，看到刚刚新建的数据库，如图6-8所示。

图6-8  查看新建数据库

2．修改数据库

在建立数据库后，可能需要更改数据库的设置，可以使用ALTER DATABASE语句来实现。修改数据库主要包括：在数据库中添加或删除文件和文件组，以及更改文件和文件组的属性等。ALTER DATABASE语句提供了更改数据库名称、文件组名称、数据文件和日志文件的逻辑名称等功能。其简化的语法格式如下：

ALTER DATABASE <数据库名>

{ ADD FILE <文件描述项> [ ,...n ] [ TO FILEGROUP <文件组名> ]

| ADD LOG FILE <文件描述项> [ ,...n ]

| REMOVE FILE <逻辑文件名>

| ADD FILEGROUP <文件组名>

| REMOVE FILEGROUP <文件组名>

| MODIFY FILE <文件描述项>

| MODIFY NAME = <新数据库名>

| MODIFY FILEGROUP <文件组名> {<文件组属性> | NAME = <新文件组名>}

}

（1）增加数据文件。

使用其中的ADD FILE子句可以给已有的数据库增加新的数据文件，其格式如下：

ALTER DATABASE <数据库名>

ADD FILE <文件描述项> [ ,...n ]

[ TO FILEGROUP <文件组名> ]

其中文件描述项与前面创建数据库时的语法格式一致，而TO FILEGROUP子句则可将数据文件添加到指定的数据库文件组中。例如，要给刚才建立的new1数据库增加一个5MB大小的新数据文件，可以执行下列SQL语句：

ALTER DATABASE new1

ADD FILE

(

    NAME=new2,

    FILENAME='c:\new2.mdf',

    SIZE=5MB,

    MAXSIZE=100MB,

    FILEGROWTH=5MB

)

执行上述语句后，即可将new2.mdf数据文件添加到new1数据库中，执行结果如图6-9所示，系统提示如下：

以5.00 MB 为单位在磁盘'new2' 上扩展数据库。

图6-9  增加数据文件

（2）增加事务日志文件。

使用ADD LOG FILE子句可以向已有的数据库增加新的事务日志文件，其语法格式如下：

ALTER DATABASE <数据库名>

ADD LOG FILE <文件描述项> [ ,...n]

例如，要给new1数据库增加两个5MB大小的新数据文件，可以执行下列SQL语句：

ALTER DATABASE new1

ADD LOG FILE

(

    NAME=new1log2,

    FILENAME='c:\newlog2.mdf',

    SIZE=5MB,

    MAXSIZE=100MB,

    FILEGROWTH=5MB

),

(

    NAME=new1log3,

    FILENAME='c:\newlog3.mdf',

    SIZE=5MB,

    MAXSIZE=100MB,

    FILEGROWTH=5MB

)

（3）增加文件组。

使用ADD FILEGROUP子句可以向已有的数据库中增加新的文件组，其语法格式如下：

ALTER DATABASE <数据库名>

ADD FILEGROUP <文件组名>

例如，要在上述示例所建的new1数据库中建立新的文件组newgroup，并将一个5MB的文件添加到该文件组，可以执行如下SQL语句：

ALTER DATABASE new1

ADD FILEGROUP newgroup

ALTER DATABASE new1

ADD FILE

(

    NAME=new3,

    FILENAME='c:\new3.mdf',

    SIZE=5MB,

    MAXSIZE=100MB,

    FILEGROWTH=5MB

)

TO FILEGROUP newgroup

（4）调整文件组属性。

使用MODIFY FILEGROUP子句可以调整数据库中已有的文件组的属性，其语法格式如下：

ALTER DATABASE <数据库名>

MODIFY FILEGROUP <文件组名> <文件组属性>

其中，文件组属性的值可以取READ、READWRITE、DEFAULT，分别代表只读文件组、读写文件组和默认文件组。

（5）调整数据文件或事务日志文件的属性。

使用其中的MODIFY FILE子句可以调整数据库中已有的数据文件或事务日志文件的属性，其格式如下：

ALTER DATABASE <数据库名>

MODIFY FILE <文件描述项>

例如，要将new1中的new2数据文件的大小更改为10MB，增长的幅度改为10MB，可以执行如下SQL语句：

ALTER DATABASE new1

MODIFY FILE

(

    NAME=new2,

    SIZE=10MB,

    FILEGROWTH=10MB

)

（6）删除数据文件、事务日志文件和文件组。

使用ALTER DATABASE语句中的REMOVE FILE子句可以删除数据库中已有的数据文件、事务日志文件以及文件组，其格式如下：

ALTER DATABASE <数据库名>

REMOVE FILE <文件引用名>

例如，要将new1数据库中的new3文件删除，可以执行如下语句：

ALTER DATABASE new1

REMOVE FILE new3

3．使用数据库

若要使用一个数据库，只需要使用USE语句指定数据库名即可，语法如下：

USE <数据库名>

例如，要使用new数据库，则可以执行如下语句：

USE new

4．删除数据库

若要删除数据库，只需要使用DROP语句指定数据库名即可，语法如下：

DROP DATABASE <数据库名> [ ,...n]

使用“DROP DATABASE”语句可以同时删除一个或多个数据库。例如，要删除new和new1这两个数据库，则可以执行如下SQL语句：

DROP DATABASE new, new1

6.2.3  表的操作语句

SQL语言可以使用CREATE TABLE语句来创建表，也可以使用ALTER TABLE语句对表进行修改，还可以使用DROP TABLE语句对表进行删除的操作。

1．创建表

使用CREATE TABLE语句可以创建表，其语法如下：

CREATE TABLE <表名> (<字段描述表>)

字段描述表的格式如下：

<列名> <数据类型> [ NULL | NOT NULL ] [ PRIMARY KEY | UNIQUE ]

·    表名：要创建新表的名称。

·    列名：字段的名称。

·    数据类型：指定字段的数据类型。

·    NULL：字段中允许空值。

·    NOT NULL：字段中不允许空值。

·    PRIMARY KEY：设置字段为主键，是通过唯一索引对给定的一列或多列强制实体完整性的约束。对于每个表只能创建一个PRIMARY KEY约束。

·    UNIQUE：指定字段的唯一性，是通过唯一索引为给定的一列或多列提供实体完整性的约束。一个表可以有多个UNIQUE约束。

例如，要在new数据库中创建student表，可以执行如下SQL语句：

USE new

CREATE TABLE student (

    sid int,

    sname char(8),

    sex char(2),

    classno int

)

执行上述语句后，将建立一个包括4个字段的student数据表，其中sid的数据类型为整数型，sname为字符型，且长度为8，sex为字符型且长度为2，classno为整型。关于字段的数据类型，将在6.2节中详细介绍。

除了可以设置字段的数据类型外，SQL语句还可以通过设置一些属性参数来对字段进行限制。例如，可以将字段设置为主键，可以限制字段不能为空，还可以设置字段的唯一性。常用的属性参数如下。

·    NULL：限制字段可以为NULL（空）。

·    NOT NULL：限制字段不能为NULL（空）。

·    PRIMARY KEY：设置字段为主键。通过唯一索引对给定的一列或多列强制实体完整性约束。

·    UNIQUE：指定字段具有唯一性。

例如，下面的SQL语句可以在new数据库中建立一个teacher表，并指定tid为主键，而且tname非空。

USE new

CREATE TABLE teacher (

    tid int NOT NULL PRIMARY KEY,

    tname char(8) NOT NULL,

    sex char(2),

    classno int

)

除了指定字段的非空性和唯一性外，还可以指定字段参考其他表的字段，这就需要将两个表建立关联，此时可以通过如下格式语句实现。

FOREIGN KEY REFERENCES ref_table (ref_column)

其中，“ref_table”指定要关联的表，“ref_column”指定要关联的字段名称。

例如，要在new数据库中建立class表，并将teacher字段关联到teacher表中的tno字段。可以执行如下语句：

USE new

CREATE TABLE class (

    cid int NOT NULL PRIMARY KEY,

    cname char(8) NOT NULL,

    department char(10),

    teacher int FOREIGN KEY REFERENCES teacher(tid)

)

执行上述语句后，将会在new数据库中创建class表，并将teacher字段关联到teacher表中的tno字段。但是应当注意，teacher表应该存在，否则将会报错，如图6-10所示，并给出如下错误信息。

服务器: 消息1767，级别16，状态1，行2

外键'FK__class__teacher__7A9C383C' 引用了无效的表'teacher'

服务器: 消息1750，级别16，状态1，行2

未能创建约束。请参阅前面的错误信息。

图6-10  创建class表失败

可以执行如下语句先创建teacher表，再创建class表。

USE new

CREATE TABLE teacher (

    tid int NOT NULL PRIMARY KEY,

    tname char(8) NOT NULL,

    sex char(2),

    classno int

)

 

CREATE TABLE class (

    cid int NOT NULL PRIMARY KEY,

    cname char(8) NOT NULL,

    department char(10),

    teacher int FOREIGN KEY REFERENCES teacher(tid)

)

输入后，再执行该段SQL语句，系统将在“查询”窗口下方提示“命令已成功完成”的信息，表明已经成功创建了teacher表和class表。右击左侧“对象浏览器”窗口中的new数据库中“用户表”节点，并在弹出的快捷菜单中选择“刷新”选项，则程序将列出刚刚建立的两张表，如图6-11所示。

图6-11  创建class表成功

另外，还可以通过SELECT语句新建一张表，并将查获的符合条件的数据保存到该新表中，具体语句将在6.2.5节中介绍。

2．修改表

SQL语言可以通过ALTER TABLE语句来修改表的结构，其语法如下：

ALTER TABLE <表名>

{ [ ALTER COLUMN <列名> {<新数据类型> [ NULL | NOT NULL ] | {ADD | DROP } ROWGUIDCOL } ]

    | ADD { [ <列定义> ] | <列名>  } [ ,...n ]

    | [ WITH CHECK | WITH NOCHECK ] ADD { < 表约束> } [ ,...n ]

    | DROP { [ CONSTRAINT ] <限制名>  | COLUMN <列名> } [ ,...n ]

    | { CHECK | NOCHECK } CONSTRAINT  { ALL | <限制名> [ ,...n ] }

}

（1）在表中增加新列。

可以通过ALTER TABLE语句中的ADD子句来给已有的数据表中添加新的列，其基本语法如下：

ALTER TABLE <表名>

ADD <新增列名> <新增列的类型> <限制描述项> [ ,...n ]

其中，可以同时添加多个列，而列的限制描述项有如下几种类型。

·    PRIMARY KEY：设置字段为主键。通过唯一索引对给定的一列或多列强制实体完整性约束。

·    UNIQUE：指定字段具有唯一性。

·    FOREIGN KEY：外键描述。

·    CHECK：通过限制可输入到一列或多列中的可能值强制域完整性的约束。

·    DEFAULT：定义默认值。

值得注意的是，在创建新表时可以指定NULL和NOT NULL限制，但是在修改表的时候，一般不能在没有DEFAULT限制时指定NOT NULL限制，这主要是考虑到插入新列时，表中已经有数据库，而不指定新列的值就使得新列上的值都为NULL，因而无法满足NOT NULL的限制。

例如，要在new数据库中的student表中添加sage列，数据类型为smallint，且允许NULL值，则可以执行如下SQL语句：

USE new

ALTER TABLE student

ADD sage smallint NULL

（2）从表中删除列和限制。

可以通过ALTER TABLE语句中的DROP子句可以删除数据表中已有的列和限制，其基本语法如下：

ALTER TABLE <表名>

DROP { [ CONSTRAINT ] <限制名>  | COLUMN <列名> } [ ,...n ]

其中，CONSTRAINT是指限制，而COLUMN是指字段，应该注意的是，可以同时删除多个列或限制。

例如，要删除student表中的sage列，可以执行如下SQL语句：

USE new

ALTER TABLE student

DROP COLUMN sage

再如，要删除class表中的PK_class和FK_class_teacher的限制，可以执行如下SQL语句：

USE new

ALTER TABLE student

DROP CONSTRAINT PK_class, FK_class_teacher

3．删除表

使用SQL语言删除表比企业管理器删除表要容易得多，删除表的语法如下：

DROP TABLE <表名>

例如，要删除new数据库中的class表，可以执行如下SQL语句：

USE new

DROP TABLE class

6.2.4  数据的操作语句

在创建了数据库和表后，就可以对其中的数据进行操作了，下面简单介绍表数据的插入、修改和删除语句。

1．插入数据

向表中添加数据时应该注意两点：首先要注意用户权限，只有sysadmin角色成员、数据库和数据库对象所有者及其授权用户才有权限向表中添加数据；其次拥有了权限，在插入数据时还要注意数据格式，不同的数据类型，插入数据的格式也不相同，应严格遵守各自的格式要求。

Transact-SQL语言中使用INSERT语句向表或视图中插入新的数据行，其语法格式如下：

INSERT [INTO] <表名>( <列名表> ) VALUES (<字段值表>)

其中，INTO为可选关键字，但一般SQL语言均使用INTO关键字，故不推荐省略INTO关键字，“列名表”可以是新插入行的一列或多列列名列表，此时“字段值表”则为与列名表对应的相应字段的值表，“列名表”也可以为空，此时将插入全部字段，“字段值表”将指定所有列的数据。

例如，dic表包括id、word、mean 3个字段，若要插入word和mean两个字段的值，则可以通过如下语句插入：

INSERT INTO dic(word, mean) VALUES ('一', '第一个数字')

若要插入全部字段值，可以执行如下语句：

INSERT INTO dic VALUES (1, '一', '第一个数字')

2．修改数据

Transact-SQL中的UPDATE语句可用于修改表中数据，该语句的语法结构如下：

UPDATE <表名>

SET <被修改的列名>=<表达式> [ ,...]

WHERE <条件>

其功能是对满足WHERE子句中条件的记录按SET子句中的要求修改指定列的数据。若要将dic表中id为1的记录的解释（mean）改为“汉语中第一个数字”，可以执行以下语句：

UPDATE dic

SET mean= '汉语中第一个数字'

WHERE id=1

3．删除数据

Transact-SQL中DELETE和TRUNCATE TABLE均可以删除表中的数据，其中DELETE语句的语法格式如下：

DELETE  [ FROM ] <表名>

[WHERE <条件>]

执行该语句可以将符合WHERE子句中条件的记录删除。这里FROM为可选关键字，一般的SQL语句在删除数据时是包含FROM关键字的，而在Transact-SQL语言中，可以省略该关键字，既可以写成“DELETE FROM”，也可以省略成“DELETE”，但为了与其他SQL语言兼容，推荐使用“DELETE FROM”关键字来删除数据。

例如，若要删除dic表中id为1的记录，可以执行如下语句：

DELETE FROM dic

WHERE id=1

TRUNCATE TABLE语句的格式如下：

TRUNCATE TABLE <表名>

与DELETE语句不同的是，TRUNCATE TABLE语句删除指定表中的所有数据行，但表结构及其所有索引继续保留，为该表所定义约束、规则、默认和触发器仍然有效。另外，TRUNCATE TABLE语句的删除速度比DELETE要快，因为DELETE语句每删除一行都要把删除操作记录到日志中，而TRUNCATE TABLE语句则是通过释放表数据页面的方法来删除表中数据，只在释放页面做一次事务日志记录，所以TRUNCATE TABLE语句删除数据后无法恢复，而DELETE操作之后可以恢复原来数据。

6.2.5  数据查询的语句

在建立好数据库、创建表、插入数据之后，用户对数据库最频繁使用的是数据查询功能。SQL语言中的SELECT语句主要用来对数据库进行查询并返回符合用户查询标准的结果数据，其基本语法格式如下：

SELECT <查询项列表>

[INTO <新表名>]

FROM <数据源表或视图>

[WHERE <条件>]

[ORDER BY <排序表达式> [ ASC | DESC ]]

1．查询表中所有列

若要查看某张表中的所有数据，可以使用如下格式来查询表中所有的列：

SELECT * FROM <数据源表或视图>

在这里，用“*”来代替表中的任何列。

例如，要查看new数据库中dic表中的所有数据，可以执行如下SQL语句：

SELECT * FROM dic

单击工具栏中的“执行查询”按钮或按F5键，将会在结果窗格的“网格”选项卡中以表格形式显示dic表中的所有内容，如图6-12所示。

图6-12  查询所有记录

图6-12中的数据是广韵同音字表，包括各小韵的序号、目次、声母、等、呼、韵部、调、反切、韵摄、字、古韵罗马字、有女罗马字、推导拼音、平水韵目等字段。

2．查询表中指定的列

在实际使用中，有时并不需要表中的所有列的数据，或者为了保密，故意不让用户看到某些列的数据，使用如下格式就可以查询表中指定的列，从而仅显示这些列上的数据。

SELECT <列名表> FROM <数据源表或视图>

其中，列名表可以包含多个列，每个列名之间用英文逗号“,”隔开。例如，执行下列语句，可以仅查询显示dic表中的字、反切、推导拼音等3列的数据。

SELECT 字,反切,推導拼音FROM dic

最终结果如图6-13所示。

图6-13  查询指定列

在这里仅显示了dic表中3列数据，而且这3列的排列顺序也与原表中的不同，将“反切”列放到了“字”列后面。这便是SELECT子句选择显示某些列的灵活性的体现，同时，SELECT在显示指定列时，还可以指定列名，语法格式如下：

SELECT <列名> AS <新列名> FROM <数据源表或视图>

例如，要将刚才的3列分别改名再显示，可以执行如下语句：

SELECT 字AS 同音字表, 反切AS 古代注音, 推導拼音AS 现代注音

FROM dic

输入完成后，按F5键，或单击“执行查询”按钮，即可在“查询”窗口下部的“网格”选项卡中以表格的形式显示结果，如图6-14所示。

图6-14  查询指定新列名

从图6-14中可以看出，3列的名称分别被改成了“同音字表”、“古代注音”、“现代注音”，通过AS关键字就可以用自定义的名称来显示指定的列，同时，还可以通过“=”来指定新列名，其语法格式如下：

SELECT <新列名>=<列名> FROM <数据源表或视图>

依照这种语法，上述带AS关键字的语句可以转写成：

SELECT 同音字表=字,古代注音=反切, 现代注音=推導拼音

FROM dic

另外，不但表中原有的字段可以出现在“列名”中，表达式的值也可以作为查询结果显示，这样使得查询结果不再限于表中原有的数据，而能够显示经过计算后得到的值，其语法格式如下：

SELECT <表达式> FROM <数据源表或视图>

例如，要将“反切”列中每个值后都加上“切”字，比如，将“德红”变成“德红切”，可以执行如下语句：

SELECT 字, 反切+'切', 推導拼音

FROM dic

执行后，得到如图6-15所示结果，其中“反切”列中每条字段值后都加上了“切”字。

图6-15  查询表达式的值

但是，这里表达式的列名显示为“无列名”，不能直观地反映该列的含义，与前面直接查询原来列一样，在该SELECT语句中也可以使用AS关键字或者使用“=”来指定该表达式列的列名，从而能够直观反映该列的含义。例如，要将该列仍指定为“反切”，可以执行如下SQL语句：

SELECT 字, 反切+'切' AS 反切, 推導拼音

FROM dic

按F5键或单击工具栏中的“执行查询”按钮，可得到如图6-16所示结果。图6-16中第二列的列名由“无列名”变成了“反切”，从而更加直观地显示表达式的值。

图6-16  查询表达式并指定列名

表达式中可以包含加减乘除算术运算符、字符连接等多种运算符，还可以包括各种内置的函数，具体将在6.3节中进行介绍。

3．查询特定记录

在SELECT语句中添加WHERE子句可以使查询只返回满足条件的记录，而不用每次都显示表中全部的记录，其语法格式如下：

SELECT <查询项列表>

FROM <数据源表或视图>

WHERE <条件>

这里WHERE子句后指定记录必须满足的条件，当某一行记录满足该条件时，将会显示在结果网格中，通常可以使用“=”、“>”、“<”、“>=”等比较运算符，或者使用“AND”、“OR”、“NOT”“LIKE”等逻辑运算符来指定条件。

例如，若要查看dic中所有入声的字，即“調”的列值为“入”的行，可以执行如下的语句：

SELECT *

FROM dic

WHERE 調='入'

执行该段语句后，结果如图6-17所示。

此时，查获的均为古代的入声字，若要查看共查获的行数，可以将表格翻至最后一页，查看最大的编号，也可以单击窗口下方的“消息”标签从而激活“消息”选项卡，其中提示了总共影响的行数，本例中提示“（所影响的行数为693 行）”，表明共查获了693条记录，具体结果如图6-18所示。

图6-17  查询特定条件

图6-18  所影响的行数

条件表达式中常用的运算符还有“LIKE”逻辑运算符，利用它可以使字段匹配某种模式，并返回其所在行的记录。通过与“_”和“%”连用，其中“_”可匹配任意一个字符，而“%”可以匹配任意零个到多个字符。

例如，要查询“瑞”的读音情况，可以执行如下语句：

SELECT *

FROM dic

WHERE 字LIKE '%瑞%'

其中“字LIKE '%瑞%'”这个条件表达式就可以匹配“字”列中包含“瑞”的行，而且“瑞”的左侧为零个或多个字符，右侧也必须为零个或多个字符，也就是只要该字段的值包含“瑞”字，就符合条件，最终运行结果如图6-19所示。

图6-19  使用LIKE运算符查询

从图6-19中可以看出，“瑞”在广韵中是与“睡”同音的，事实上，在当代许多方言中两者也是同音的，难怪要把“Swiss”音译成“瑞士”，还是有其语音上的根据的。

另外，在WHERE子句中还经常使用“AND”逻辑运算符来连接多个条件，来匹配需要同时满足的条件，或者使用“OR”运算符来匹配仅满足其中一个条件即可。例如，“正”在普通话中是个多音字，一般读成zheng4，但在“正月”等词中要读成zheng1，要查询“正”在广韵是否有平声（zheng1）的读法，可以使用AND逻辑运算符来查询既包含“正”字又为平声的记录，具体语句如下：

SELECT *

FROM dic

WHERE 調='平' AND 字LIKE '%正%'

执行后，显示如图6-20所示查询结果，这表明，当时也是有zheng1这一读音的。

图6-20  使用AND运算符查询

关于WHERE中可以使用的其他运算符，将在6.3节中详细介绍。

4．对查询结果进行排序

除了查询指定的记录，SELECT语句中还可以使用ORDER BY子句对查询的结果进行排序，带ASC参数时为升序，带DESC参数时为降序，不带任何参数时即为默认方式，将按升序排列，其语法格式如下：

SELECT <查询项列表>

FROM <数据源表或视图>

[WHERE <条件>]

[ORDER BY <排序表达式> [ ASC | DESC ]]

例如，若要将dic中的记录按照“推導拼音”列值中的字母进行升序排列，可以执行如下语句：

SELECT *

FROM dic

ORDER BY 推導拼音ASC

查询结果如图6-21所示。

图6-21  对查询结果升序排列

从图6-21中可以看出，排在最前面的拼音并不是a，而是NULL，即SQL Server将空值排在最前端，若要忽略这些拼音为NULL的行，则可以在查询语句中使用WHERE语句对记录进行筛选，具体语句如下：

SELECT *

FROM dic

WHERE 推導拼音IS NOT NULL

ORDER BY 推導拼音ASC

其中，“IS NOT NULL”表示该表达式的值不为空，而相反的，“IS NULL”则表示该表达式的值为空，使用了该WHERE后，即可筛选出推导拼音不为空的行，并且按其升序排列，结果如图6-22所示。

图6-22  对使用WHERE的查询结果升序排列

在对结果进行排序时，有时候会遇到相同值的记录行，如图6-22所示推导拼音字段，有4行的值均为ai4，这几个字该如何排列，在默认情况下是按照原表中记录的顺序排列的，但用户也可以指定其他列作为次要的排列依据，比如，可以指定按照序号进行降序排列，这样就可以在按推导拼音排列时值相同的情况下，按照序号列的值进行降序排序，可以执行如下语句：

SELECT *

FROM dic

WHERE 推導拼音IS NOT NULL

ORDER BY 推導拼音ASC, 序號DESC

运行结果如图6-23所示，4个推导拼音值为“ai4”的列，按照序号降序排列，其序号依次为2534、2533、2424、2423。

这种多列排序在实际应用中非常常见。比如，在给学生成绩进行排名时，首先按照学生的考试得分从高到低排列，如果两人的分数相同时，一般情况下可能会按照姓名排列，若存放成绩和学生信息的数据表为student，其中包括sname（学生姓名）、sex（性别）、age（年龄）、score（成绩）4个字段，则可以执行如下语句：

SELECT sname, score

FROM student

ORDER BY score DESC, sname ASC

图6-23  对查询结果进行多列排序

再例如，如果要查询一个编辑部所有编辑的信息，他们的职位高低都一样，则一般会按照姓名排列，如果出现同名同姓的情况，则按年龄从大到小排列。假设存储该编辑部所有员工信息的表为staff，其中包含name（姓名）、sex（性别）、age（年龄）、salary（工资）、position（职位）等列，则可以执行如下语句查询所有编辑的信息，并按一定的方式排序。

SELECT *

FROM staff

WHERE postion='编辑'

ORDER BY name ASC, age DESC

5．多表查询

在数据查询时，经常会涉及到提取两个或多个表中的数据，这便是多表查询。在多表查询时，可以在FROM子句指名要包含的多个表或视图，然后就可以在多张表中进行筛选查询，其语法格式如下：

SELECT <查询项列表>

FROM <数据表或视图列表>

[WHERE <条件>]

[ORDER BY <排序表达式> [ ASC | DESC ]]

例如，现在有两张表，一张为classinfo表，其中记录了班级的信息，包含cid（班级编号）、cname（班级名称）、department（系别）、teacher（班主任的教师编号）等字段，另一张为teacherinfo表，其中记录了教师的信息，包含tid（教工号）、tname（教师姓名）、salary（薪水）、position（教师职称）等字段信息。若要查询所有班级的班级名称和班主任的姓名，并按班级号排列，则可以在这两张表上执行如下语句：

SELECT cname, tname

FROM classinfo, teacherinfo

WHERE teacher=tid

ORDER BY cid

在多表查询时，有时候存在着重名的列，这时就需要区分究竟某一列是属于哪一张表，可以在列名前加上表名，并用点号“.”隔开，比如，classinfo表中cid列，就可以写成classinfo.cid，而studentinfo表中cid列，就可以写成studentinfo.cid。通过这种表前缀的方式，来区分多张表中相同名称的字段。例如要查询所有的学生姓名及其所在班级的名称，并按学号进行排列，假设studentinfo表中包含sid（学号）、sname（学生姓名）、cid（所在班级编号）、sage（学生年龄）、ssex（学生性别）等字段，则可以执行如下SQL查询语句：

SELECT classinfo.cname, studentinfo.sname

FROM classinfo, studentinfo

WHERE classinfo.cid=studentinfo.cid

ORDER BY studentinfo.sid

SQL为了简化输入，允许在查询中使用表的的别名，从而缩写表名，使得用户可以快速地输入SQL语句而执行相应的操作，其语法格式如下：

SELECT <查询项列表>

FROM <数据表或视图列表> [AS] <别名>

[WHERE <条件>]

[ORDER BY <排序表达式> [ ASC | DESC ]]

与列的别名一样，可以使用AS关键字引出表或视图的别名，也可以省略AS，在表名后直接添加别名，并用空格与原表名隔开。例如，将上述查询所有的学生姓名及其所在班级的名称的SQL语句中的studentinfo简称为s，将classinfo简称为c，则原SQL语句可以改写成如下格式：

SELECT c.cname, s.sname

FROM classinfo AS c, studentinfo AS s

WHERE c.cid=s.cid

ORDER BY s.sid

引入了表的别名后，可以在查询中很方便地使用表的别名进行查询。

上面举的几个例子中，都是通过等于号“=”将不同表中的列进行等值连接，这种查询可称为“等值连接查询”。

除了等值连接外，还可以通过非等值连接将多张表连接起来，并进行查询。所谓非等值连接，是指表之间的连接关系不是通过“=”连接，而是通过其他关系连接的，比如“>”“LIKE”等。

例如，假设score1记录了全校高三学生第一次模拟考试的成绩，包括sid（学号）、chinese（语文成绩）、maths（数学成绩）、english（英语成绩）等字段，scoreline中记录了全校高三学生模拟考试的成绩达标分数线，其中包括id（模拟考试的序数）、chinese（语文达标线）、maths（数学达标线）、english（英语达标线）等字段。若要查询第一次模拟考试三门都达标的学生的学号，则可以执行如下语句：

SELECT x.sid

FROM score1 AS x, scoreline AS y

WHERE y.id=1 AND x.chinese > y.chinese AND x.english > y.english AND x.maths > y.maths

ORDER BY x.sid

在数据查询时，有时候需要将同一个表进行连接，这种连接称之为自连接。进行自连接时，就如同两张分开的表一样，可以把其中一张表的某行与同一张表中的另一行连接起来。这时，由于两张表完全同名，故需要用别名来区分这两张表。

例如，在dic表中，要查询出所有与“垂”字的声母完全相同的汉字的记录，则可以执行如下SQL语句：

SELECT y.*

FROM dic AS x, dic AS y

WHERE x.字LIKE '%垂%' AND x.聲=y.聲

执行结果如图6-24所示。

图6-24  自连接查询

图中将同一张数据表dic当作x和y两张表使用，进行自连接查询。若在刚才的查询中，要求不包括“垂”所在行，则必须在WHERE子句中将该行去除，则可以执行如下SQL语句：

SELECT y.*

FROM dic AS x, dic AS y

WHERE x.字LIKE '%垂%' AND x.聲=y.聲AND x.序號!= y.序號

在WHERE子句中添加了“AND x.序號!= y.序號”条件，从而将“垂”字所在的记录行排除在查询结果外。

6．生成表查询

在实际应用中，有时需要将查询的结果直接保存到一张新的表中，从而便于日后对其进行独立的维护和查询，这便是所谓的生成表查询。这一点与视图不同，视图并不独立，当原表的数据改变时，视图中的数据也会发生相应的变化，用户可以通过视图来查看原表中的数据以及数据的变化，但将查询所得的结果插入到一张新的数据表时，新表就与原表分离了，原表中的任何数据变化都不会反应在这张新表中。所以用户可以根据实际需要，建立视图或者进行生成表查询。

生成表查询一般是通过SELECT语句中的INTO子句来实现，其语法格式如下：

SELECT <查询项列表>

INTO <新表名>

FROM <数据源表或视图>

[WHERE <条件>]

[ORDER' BY <排序表达式> [ ASC | DESC ]]

执行语句后，将以INTO后的“新表名”参数建立新表，并用SELECT的查询结果集填充该表，而且新表的结构由选择列表中表达式的特性定义。

例如，要将dic表中所有“調”为“入”的记录都保存为到“ru”表中，则可以查询所有“調='入'”的记录行，并配合使用“INTO ru”子句来实现，具体的SQL语句如下：

SELECT *

INTO ru

FROM dic

WHERE 調='入'

执行该段SQL语句后，运行结果如图6-25所示。

图6-25  生成表查询

与前面所有的SELECT查询不同的是，系统没有显示“网格”选项卡中的表格内容，而是直接显示“消息”选项卡中的内容，并提示“（所影响的行数为693 行）”，表明向新表中插入了693行记录。当单击“网格”标签激活“网格”选项卡时，其中也并无任何内容显示，这表明，系统并没有向用户返回查询结果，而是直接生成一张新表，并将结果插入至新表中。

此时，若要查询新表中有无数据，可以运行如下语句，并得到如图6-26所示运行结果。

SELECT *

FROM ru

图6-26  查询新生成的表

由图6-26可以发现，其中“序號”列的编号不再连续，“調”列全部都为“入”，显得毫无意义，而且整个排列顺序也显得杂乱无章，所以考虑在生成表查询时，使用ORDER BY子句，从而向新表中插入有序的记录，方便日后的查询和处理，因此可以执行如下语句：

SELECT 目次, 聲, 等, 呼, 韻部, 反切, 攝, 字, 古韻羅馬字, 有女羅馬字, 推導拼音, 平水韻目

INTO ru

FROM dic

WHERE 調='入'

ORDER BY 推導拼音

执行后，系统提示错误，如图6-27所示。

服务器: 消息2714，级别16，状态6，行1

数据库中已存在名为'ru' 的对象。

图6-27  生成表查询时出错

根据系统提示，SELECT生成表查询时，不能将查询的结果插入到已有的数据表中，而应插入到新表中，如果确认已经存在的重名的表不再需要，则可以在生成表查询前先使用DROP TABLE语句删除该表，再执行生成表查询，具体的SQL语句如下：

DROP TABLE ru

SELECT 目次, 聲, 等, 呼, 韻部, 反切, 攝, 字, 古韻羅馬字, 有女羅馬字, 推導拼音, 平水韻目

INTO ru

FROM dic

WHERE 調='入'

ORDER BY 推導拼音

执行成功后，再执行“SELECT * FROM ru”语句，就可查看新的ru表中的数据，如图6-28所示。其中，不再包含“調”“序號”等列，而且所有数据默认已按推导拼音列升序排列，从而更加方便日后的查询与处理。

图6-28  有序插入生成表

本节中仅介绍了这些基础的SELECT查询语句，高级的SELECT查询将在介绍了Transact-SQL程序设计基础方面的知识之后再介绍。