SQL Server 中的 SQL 语言是 Transact-SQL 语言。它包括以下主要组成部分:
数据定义语言(Data Definition Language,DDL)
数据操纵语言(Data Manipulation Language,DML)
数据控制语言(Data Control Language,DCL)
系统存储过程(System Stored Procedure)
一些附加的语言元素
本节主要介绍 Transact-SQL 语言比标准 SQL 语言扩展或加强的部分内容。
6.2.1 数据定义语言(DDL)
数据定义语言 是指用来定 义和管理数 据库以及 数 据库中的各种 对象的语句 ,包括 CREATE、ALTER 和 DROP 等语句。在 SQL Server 中,数据库对象包括表、视图、触发器、
存储过程、规则、缺省、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通
过使用 CREATE、ALTER、DROP 等语句来完成。
1.创建数据库的命令
命令格式:CREATE DATABASE <数据库名> [ON [PRIMARY] [<文件 1>[,<文件 2>……]][,<文件组和文件>]] [LOG ON <文件>] [COLLATE <排序规则名称>] [FOR LOAD | FOR ATTACH]
说明:
(1)PRIMARY 表示定义数据主文件,其后是关于数据文件的描述。如果没有 PRIMARY 字样,其中第一个文件为主文件。 户定义文件组及文件组中的文件。格式为:FILEGROUP <文件组名> <文件 1>[,<文件 2>……]。
(4)LOG 子句中定义的文件指日志文件名。
(5)COLLATE 指定默认的排序规则,排序规则可以是 Windows 排序规则,也可以是 SQL Server 排序规则。如果没有指定排序规则,则以 SQL Server 实例的排序规则为排序规则。
(6)FOR LOAD 指可以从备份数据库中加载。FOR ATTACH 是将已脱机的数据库重新联机。
【例 6.1】创建数据库:waremanage,数据文件初始大小为 1M,最大为 10M,如果需要 增加空间,每次增加 1M。逻辑文件同样设置。
CREATE DATABASE waremanage ON(NAME= 'waremanage_data',FILENAME='c:\program files\Microsoft sql server\data\ waremanage_data.mdf',SIZE=1,MAXSIZE=10,FILEGROWTH=1) LOG ON (NAME='waremanage_log',FILENAME='waremanage_data.ldf',SIZE=1,MAXSIZE=
10,FILEGROWTH=1)
(2)列定义的一般格式是:<列名> <数据类型> [<宽度>] [NOT [NULL]] [CONSTRAINT 子句] [UNIQUE 子句] [PRIMARY 子句] [FOREIGN 子句] [DEFAULT 子句]
1)CONSTRAINT 子句是可选关键字,表示 PRIMARY KEY、NOT NULL、UNIQUE、
FOREIGN KEY 或 CHECK 约束定义的开始,同时定义索引名称。格式为:CONSTRAINT <
索引名>。
2)UNIQUE 表示唯一性,表示该列不允许有重复值。它和主键不同的是,主键除不允许 有重复值外,还不允许有空值。
3)PRIMARY 子句说明该列为主键。格式:PRIMARY KEY CLUSTERED。
4)DEFAULT 子句定义默认值,如果该列不允许空值,而在录入时又未说明该列的值,
则自动用默认值填充;否则填入 NULL。
5)FOREIGN 子句定义外键,其格式为:FOREIGN KEY <外键名> REFERENCES <主表 名称>(主键名称)。其中外键名可以是多个列的列名,用逗号分隔,但其数量与类型必须与
(5)CHECK 子句为表级约束,说明列自定义约束条件。其格式为:CHECK (<约束条件 表达式>)。
其中约束条件表达式如下所示:
1)<字段名> IN (<值表>)。
2)<字段名> <关系符><数据值>。
3)<字段名> LIKE <匹配表达式>。
约束条件表达式可以由多个表达式组成,彼此间用 AND、OR 连接。
(6)ON { <文件组名> | DEFAULT }定义存储表的文件组,该文件组必须在数据库中存在。
如果使用“DEFAULT”或没有该子句,表示存储在默认文件组中。
(7)TEXTIMAGE_ON { <文件组名> | DEFAULT } 定义 text、ntext、image 等类数据所 存储的文件组名称。
【例 6.2】在数据库 waremanage 中创建关于出版物的表 publishers(pub_id, pub_name, author ,unitprice, unit)。
在选择数据库 waremanage 之后在查询分析器中可以输入如下语句建表:
CREATE TABLE publishers (pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED CHECK (pub_id IN ('0389', '0736', '0877', '1622', '1756') OR pub_id LIKE '20[0-9][0-9]'),pub_name varchar(40) NULL, author varchar(20) NULL, unitprice int NULL, unit varchar(30) NULL DEFAULT('USA') )
说明:
(1)NOT NULL 表示在输入数据时不允许空值,NULL 表示在输入数据时允许空值。
(2)CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED 建立主键约束,同时建 立主键索引,索引名为 UPKCL_pubind。CLUSTERED 表示索引类型为聚集索引。
(3)CHECK (pub_id IN ('0389', '0736', '0877', '1622', '1756') OR pub_id LIKE '20[0-9][0-9]') 子句建立用户定义约束,将建立如下表达式:([pub_id] = '1756' or ([pub_id] = '1622' or ([pub_id]
= '0877' or ([pub_id] = '0736' or [pub_id] = '0389'))) or [pub_id] like '20[0-9][0-9]')
like '20[0-9][0-9]')表示出版物编号头两位为“20”,后两位的每一位由 0 到 9 之间的数字 构成。
3.修改数据表与删除数据表
修改数据表与删除数据表的语句和标准 SQL 语言中修改数据表与删除数据表的语句基本 相同,但修改数据表的添加新列、修改列属性等语句中可包括添加约束、修改约束的内容。
【例 6.3】为 publishers 的 unitprice 创建一个名为 CK_publishers 的约束,要求控制 unitprice 的值在 10~1000 之间。
ALTER TABLE publishers ADD CONSTRAINT CK_publishers CHECK (unitprice>=10 AND unitprice<=1000)
4.建立视图
语句格式:CREATE VIEW [<数据库名>.][<属主名>.] <视图名>[({<列名>})] [WITH <视图 属性>] AS <子查询> [WITH CHECK OPTION]
说明:
(1){<列名>}定义视图中的列名,可以与表中列名不相同,列名间用逗号分隔。
(2)视图属性包括①ENCRYPTION:表示加密包含 CREATE VIEW 语句文本的系统表列;
②SCHEMABINDING:将视图绑定在架构上,要求<SELECT 子句>包含表、视图等两部分的 名称;③VIEW_METADATA:表示在某些查询中可以返回元数据信息。
(3)子查询与 WITH CHECK OPTION 见 4.7 节相关内容。
5.修改视图
语句格式:ALTER VIEW [<数据库名>.][<属主名>.] <视图名>[({<列名>})] [WITH <视图 属性>] AS <子查询> [WITH CHECK OPTION]
不难发现,修改视图语句的参数和建立视图的语句参数是相同的。
【例 6.4】修改原有表 publication 的视图 VIEW_pub 的内容,要求输出 pub_id、pub_name 与 unitprice,显示条件是 unitprice 大于 100。
ALTER VIEW waremanage. VIEW_pub AS SELECT pub_id, pub_name, unitprice FROM publishers WHERE unitprice>100
6.建立索引
语句格式:CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX <索引名称>
ON {<表名> | <视图名>} ({<列名>[ASC | DESC]}) [{WITH <索引选项>} ] [ON <文件组>]
说明:
(1)UNIQUE 选项表示创建唯一索引。
(2)CLUSTERED | NONCLUSTERED 选项表示创建聚集或非聚集索引。如果是聚集索 引,则数据表中数据物理存储时按该索引规定的顺序排列。
(3)索引选项如 IGNORE_DUP_KEY 表示向属于聚集索引的列插入重复值时将发出警告 并拒绝执行。又如 DROP_EXISTING 表示除去并重建先前存在的聚集或非聚集索引。
【例 6.5】为表 publishers 建立关于 pub_id 的聚集索引。
CREATE UNIQUE CLUSTERED INDEX pub_pub_id (pub_id ASC) WITH IGNORE_DUP_KEY 6.2.2 数据操纵语言(DML)
数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句,这些语句包括 SELECT、INSERT、UPDATE、DELETE 等。在默认情况下,只有 sysadmin、dbcreator、db_owner 或 db_datawriter 等角色的成员才有权利执行数据操纵语言。
1.插入数据语句
语句基本结构:INSERT INTO ({<表名> | <视图名>} {[<列名>]}) VALUES ({DEFAULT | NULL | <表达式>})
说明:
(1){[<列名>]}指多个列的名字,彼此用逗号分隔。如果该项省略,表示对表的所有字段。
(2){DEFAULT | NULL | <表达式>}表示多个“DEFAULT | NULL | <表达式>”的集合,
彼此用逗号分隔,其数量与顺序要和{[<列名>]}一致,如果语句中省略{[<列名>]},那么要和 表定义的结构中列的数量与顺序一致。
“DEFAULT | NULL | <表达式>”表示可以是“DEFAULT”,或者是 NULL,或者是<表达 式>。“DEFAULT”表示按表定义中关于列默认值的定义填入。NULL 表示填入“空”,只有表 定义中允许为空值的列允许填入。<表达式>指当前可以计算得到具体数据的计算式或函数式。
2.修改数据语句
语句基本结构:UPDATE {<表名> | <视图名>} SET {列名=<表达式> | DEFAULT | NULL}
[FROM {<表名>}] [WHERE <条件表达式>]
【例 6.6】 如果有成绩表 1(学号,姓名,总分)和成绩表 2(学号,姓名,C 语言分数),
(1)INTO <新表名>子句将查询结果传入一个新数据表中,将产生一个新数据表。
(2)COMPUTE <聚集函数>的结构为:
COMPUTE { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM } ( <表
式中所有值的和;VAR 表示表达式中所有值的统计方差;VARP 表示表达式中所有值的填充 统计方差。
BY <表达式>中的<表达式>通常是列名,表示分类汇总的分组字段。
(3)FOR < BROWSE 或 XML 选项>子句格式为:
FOR { BROWSE | XML { RAW | AUTO | EXPLICIT } [ , XMLDATA ] [ , ELEMENTS ] [ , BINARY BASE64 ]}
说明:
1)FOR BROWSE 指定当查看 DB-Library 浏览模式游标中的数据时允许更新。如果表包 含时间戳列(用 timestamp 数据类型定义的列),表有唯一索引且 FOR BROWSE 选项在 SELECT 语句的最后发送到 SQL Server,则可以在应用程序中浏览该表。
2)FOR XML 指定查询结果将作为 XML 文档返回。必须指定下列 XML 模式之一:RAW、
AUTO、EXPLICIT。其中,RAW:获得查询结果并将结果集内的各行转换为 XML 元素,用 一般标识符 <row /> 作为元素标记。AUTO:以简单的嵌套 XML 树返回查询结果。在 FROM 子句内,每个在 SELECT 子句中至少有一列被列出的表都表示为一个 XML 元素。SELECT 子句中列出的列映射到适当的元素特性。EXPLICIT:指定显式定义所得到的 XML 树的形状。
使用此种模式,要求以一种特定的方式编写查询,以便显式指定有关期望的嵌套的附加信息。
3)XMLDATA 表示返回架构,但不将根元素添加到结果中。如果指定了 XMLDATA,它 将被追加到文档上。
4)ELEMENTS 表示指定列作为子元素返回。否则,列将映射到 XML 特性。
5)BINARY BASE64 表示指定查询返回二进制 base64 编码格式的二进制数据。使用 RAW 和 EXPLICIT 模式检索二进制数据时,必须指定该选项。这是 AUTO 模式中的默认值。
【例 6.7】 如果有表 titles,包括 title、type、price、advance、ytd_sales 等字段,显示含有 本年度截止到现在的当前销售额的行,然后按 type 分类以递减顺序计算书籍的平均价格和预 付款总额,最后计算全部书籍的平均价格和预付款总额。
SELECT CAST(title AS char(20)) AS title, type, price, advance FROM titles WHERE ytd_sales IS NOT NULL ORDER BY type DESC COMPUTE AVG(price), SUM(advance) BY type COMPUTE SUM(price), SUM(advance)
6.2.3 数据控制语言(DCL)
数据控制语言(DCL)是用来设置或者更改数据库用户或角色权限的语句,包括 GRANT、
DENY 、 REVOKE 等 语 句 , 在 默 认 状 态 下 , 只 有 sysadmin 、 dbcreator 、 db_owner 或 db_securityadmin 等角色的成员才有权利执行数据控制语言。
1.GRANT 语句
GRANT 语句是授权语句,它可以把语句权限或者对象权限授予给其他用户和角色。
(1)授予语句权限的语句。
语句格式:GRANT {ALL | <语句>} TO <用户名>
说明:“语句”包括:CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、
CREATE PROCEDURE 、 CREATE RULE、 CREATE TABLE 、 CREATE VIEW 、 BACKUP DATABASE、BACKUP LOG。
(2)授予对象权限的语句。 或 UPDATE。列列表可以与 SELECT 和 UPDATE 权限一起提供。如果列列表未与 SELECT 和 UPDATE 权限一起提供,那么该权限应用于表、视图或表值函数中的所有列。在存储过程上
语句格式:REVOKE [ GRANT OPTION FOR ] { ALL [ PRIVILEGES ] | <对象权限>} { [ (< 值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是 Transact-SQL 命令 执行后的状态值。
(2)赋值运算符。Transact-SQL 中只有一个赋值运算符,即等号(=)。赋值运算符使我 们能够将数据值指派给特定的对象。另外,还可以使用赋值运算符在列标题和为列定义值的表 达式之间建立关系。
(3)位运算符。位运算符使我们能够在整型数据或者二进制数据(image 数据类型除外)
之间执行位操作。此外,在位运算符左右两侧的操作数不能同时是二进制数据。位运算符包括:
之间执行位操作。此外,在位运算符左右两侧的操作数不能同时是二进制数据。位运算符包括: