Transact-SQL语法速查手册

第1章 Transact-SQL基础

1.1 标识符

一、常规标识符

1. 命名规则：

l 第一个字母必须是Unicode2.0标准定义的字母、下划线、at符号（@）和数字符号（#）；

l 后续字符可以是Unicode2.0标准定义的字母、来自基本拉丁字母或其他国家/地区脚本的十进制数字、at符号（@）、美元符号（$）、数字符号（#）或下划线；

l 标识符不能是Transact-SQL的保留字；

l 不允许嵌入空格或其他特殊字符；

l 标识符长度不大于128字符，本地临时表标识符不大于116字符。

注：SQL Server2000中，常规标识符的格式规则取决于数据库的兼容级别，兼容级别可以用系统存储过程sp_dbcmptlevel来设置。以上是兼容级别为80时的规则。

2. 特殊字符说明

以下字符位于标识符开始位置时具有特殊意义：

l 以@符号开始的标识符表示局部变量或参数；

l 以@@符号开始的标识符表示全局变量；

l 以#符号开始的标识符表示临时表或过程；

l 以##符号开始的标识符表示全局临时对象；

l --单行注释；

l /*…*/多行注释。（多行注释不能跨越批处理）

二、分隔标识符

1. 命名规则：

l 标识符长度不大于128字符（不包括分隔符字符），本地临时表标识符不大于116字符；

l 标识符的主体可以是包含当前代码页内字母(分隔符本身除外)的任意组合。

2. 分隔标识符类型：

l 被引用的标识符用双引号分隔开，例如SELECT * FROM “My Table”；

l 括在括号中的标识符用方括号分隔，例如SELECT * FROM [My Table]。

三、使用标识符

在SQL Server2000中，一个对象的全称语法格式为：

server.database.owner.object

其中，server为服务器名，database为数据库名，owner为所有者，object为对象名。默认情况下，server为本地服务器，database为当前数据库，owner为在指定的数据库中与当前连接会话的登陆标识相对应的数据库用户或者数据库所有者。

四、通配符

1. %—包含零个或更多字符的任意字符串

例如：查找姓名中包含有“华”字的所有学生

WHERE sname LIKE ‘%华%’

2. _—任何单个字符

例如：查找姓王，名字包含3个字的学生

WHERE sname LIKE ‘王__’

3. [] —指定范围或集合中的任何单个字符

例如：查找姓刘和姓王，名字包含3个字的学生

WHERE sname LIKE ‘[刘,王]__’

4. [^]—不属于指定范围或集合的任何单个字符

例如：查找除姓刘和姓王以外，名字包含3个字的学生其他学生

WHERE sname LIKE ‘[^刘,王]__’

五、系统全局变量

变量名称

说明

@@CONNECTIONS

返回自SQL Server本次启动以来，所接受的连接或试图连接的次数

@@CPU_BUSY

返回自SQL Server本次启动以来，CPU工作的时间，单位为毫秒

@@CURSOR_ROWS

返回游标打开后，游标中的行数

@@DATEFIRST

返回SET DATAFIRST参数的当前值

@@DBTS

返回当前数据库的当前timestamp数据类型的值

@@ERROR

返回上次执行SQL语句产生的错误编号

@@FETCH_STATUS

返回FETCH语句游标的状态

@@IDENTITY

返回最新插入的IDENTITY列值

@@IDLE

返回自SQL Server本次启动以来，CPU空闲的时间，单位为毫秒

@@IO_BUSY

返回自SQL Server本次启动以来，CPU处理输入和输出操作的时间，单位为毫秒

@@LANGID

返回本地当前使用的语言标识符

@@LANGUAGE

返回当前使用的语言名称

@@LOCK_TIMEOUT

返回当前的锁定超时设置，单位为毫秒

@@MAX_CONNECTIONS

返回SQL Server允许同时连接的最大用户数目

@@MAX PRECISION

返回当前服务器设置的decimal和numeric数据类型使用的精度

@@NESTLEVEL

返回当前存储过程的嵌套层数

@@OPTIONS

返回当前SET选项信息

@@PACK_RECEIVED

返回自SQL Server本次启动以来，通过网络读取的输入数据包数目

@@PACK_SENT

返回自SQL Server本次启动以来，通过网络发送的输出数据包数目

@@PACKET_ERRORS

返回自SQL Server本次启动以来，SQL Server中出现的网络数据包的错误数据

@@PROCID

返回当前的存储过程标识符

@@REMSERVER

返回注册记录中显示的远程数据服务器名称

@@ROWCOUNT

返回上一个语句所处理的行数

@@SERVERNAME

返回运行SQL Server的本地服务器名称

@@SERVICENAME

返回SQL Server运行时注册键名称

@@SPID

返回服务器处理标识符

@@TEXTSIZE

返回当前TESTSIZE选项的设置值

@@TIMETICKS

返回一个计时单位的微秒数，操作系统的一个计时单位是31.25毫秒

@@TOTAL_ERRORS

返回自SQL Server本次启动以来，磁盘的读写错误次数

@@TOTAL_READ

返回自SQL Server本次启动以来，读磁盘的次数

@@TOTAL_WRITE

返回自SQL Server本次启动以来，写磁盘的次数

@@TRANCOUNT

返回当前连接的有效事务数

@@VERSION

返回当前SQL Server服务器的日期，版本和处理器类型

1.2 数据类型

类别

数据类型

字节

取值范围

精度

整数型

bigint

8

int

4

smallint

2

tinyint

1

0~255

逻辑数值型

bit

1,0或NULL

小数数据型

decimal

numeric

货币型

money

8

4

smallmoney

4

4

近似数值型

float

4

7

real

8

15

字符型

char

1~8000

varchar

text

Unicode字符型

nchar

1~8000

nvarchar

ntext

二进制数据

binary

1~8000

varbinary

1~8000

image

日期时间型

datetime

smalldatetime

其他类型

cursor

sal_variant

table

timestamp

uniqueidentifier

1.3 运算符

SQL Server中的运算夫及其优先级如下所示（优先级由高到低）

l +（正）、-（负）、~（按位取反）

l *（乘）、/（除）、%（取模，及求余）

l +（加）、+（连接）、-（减）

l =、>、<、>=、<=、<>、!= 、!< 、!>

l ^（位异或）、&（位与）、|（位或）

l NOT

l AND

l ALL、ANY、BETWEEN、IN、LIKE、OR、SOME

l =（赋值）

注：所有运算符均遵循左结合型，即同一表达式中若出现多个优先级相同的运算符时，将按从左到右的顺序对表达式进行求值

1.4 语句

一、BEGIN…END

1.【格式】

BEGIN

{

         sql语句|语句块

}

END

2.【示例】

BEGIN

         DECLARE @MyVar float

         SET @MyVar = 456.256

         BEGIN

                   PRINT CAST (@MyVar AS varchar(12))

         END

END

二、IF…ELSE

1.【格式】

IF Boolean_expression

         {sql语句|语句块}

[ELSE

         {sql语句|语句块}]

2.【示例】

USE School

IF (SELECT AVG(degree) FROM score WHERE cno=’3-105’)>80

BEGIN

                   PRINT ‘课程：3-105’

                   PRINT ‘考试成绩还不错’

END

ELSE

BEGIN

         PRINT ‘课程：3-105’

         PRINT ‘考试成绩一般’

         END

三、CASE

1.【简单CASE格式】

CASE input_expression

WHEN when_expression THEN result_expression

[…n]

         [ELSE else_result_expression]

END

2.【简单CASE示例】

USE School

GO

SELECT tname AS ‘姓名’,depart AS ‘单位’,

         CASE prof

                   WHEN ‘教授’ THEN ‘高级职称’

                   WHEN ‘副教授’ THEN ‘高级职称’

                   WHEN ‘讲师’ THEN ‘中级职称’

                   WHEN ‘助教’ THEN ‘初级职称’

         END AS ‘职称类型’

FROM teacher

GO

3.【搜索CASE格式】

CASE

WHEN Boolean_expression THEN result_expression

[…n]

         [ELSE else_result_expression]

END

4.【搜索CASE示例】

USE School

GO

SELECT sno AS ‘学号’,cno AS ‘课程号’,

         CASE

                   WHEN degree>=90 THEN ‘A’

                   WHEN degree>=80 THEN ‘B’

                   WHEN degree>=70 THEN ‘C’

                   WHEN degree>=60 THEN ‘D’

                   WHEN degree<60 THEN ‘E’

         END AS ‘成绩’

FROM score ORDER BY sno

GO

四、WHILE

1.【格式】

WHILE Boolean_expression

{sql语句|语句块}

[BREAK]

{sql语句|语句块}

[CONTINUE]

2.【示例】

DECLARE @s int,@i int

SET @i = 0

SET @s = 0

WHILE @i<=100

         BEGIN

                   SET @s = @s+@i

                   SET @i = @i+1

         END

PRINT ‘1+2+…+100=’+CAST(@s AS char(25))

五、GOTO

1.【格式】

GOTO lable

2.【示例】

DECLARE @avg float

USE School

If(SELECT COUNT(*) FROM score WHERE sno=’108’)=0

         GOTO label1

BEGIN

         PRINT ‘108学号学生的平均成绩：’

         SELECT @avg=AVG(degree) FROM score WHERE sno=’108’

         PRINT @avg

         RETURN

END

Label1:

         PRINT ‘108学号的学生无成绩’

六、RETURN

1.【格式】

RETURN [integer_expression]

2.【示例】

USE School

GO

CREATE PROC MyPro @no char(5)

AS RETURN(SELECT AVG(degree)FROM score WHERE sno=@no)

七、WAITFOR

1.【格式】

WAITFOR {DELAY ‘time’ | TIME ‘time’}

2.【示例】

BEGIN

         WAITFOR TIME ‘1:10:00’

         PRINT ‘现在是1:10:00’

END

1.5 函数

一、内置函数

函数分类

函数

说明

聚合函数

COUNT

SUM

AVG

MIN

MAX

配置函数

游标函数

日期和时间函数

DAY

MONTH

YEAR

GETDATE

数学函数

元数据函数

行集函数

安全性函数

字符串函数

系统函数

系统统计函数

文本和图像函数

二、用户自定义函数

1.【格式】

2.【示例】

USE test

GO

CREATE FUNCTION CubicVolume

--输入参数

(@CubeLength decimal(4,1),@CubeWidth decimal(4,1),@CubeHeight decimal(4,1))

RETURNS decimal(12,3)   --返回立方体的体积

AS

         BEGIN

                  RETURN(@CubeLength*@CubeWidth*CubeHeight)

         END

GO

第2章 Transact-SQL 语句

2.1 Transact-SQL语句一览表

语句类型

语句

说明

数据查询语言DQL

SELECT

从数据库表中检索数据和列

数据操作语言DML

INSERT

向数据库表中添加新数据行

UPDATE

更新数据库表中的数据

DELETE

从数据库表中删除数据行

数据定义语言DDL

CREATE DATABASE

创建数据库

ALTER DATABASE

修改数据库

DROP DATABASE

删除数据库

CREATE TABLE

创建数据库表

ALTER TABLE

修改数据库表结构

DROP TABLE

从数据库中删除表

CREATE VIEW

创建视图

ALTER VIEW

修改视图

DROP VIEW

从数据库中删除试图

CREATE INDEX

创建索引

DROP INDEX

删除索引

CREATE PROCEDURE

创建存储过程

DROP PROCEDURE

删除存储过程

CREATE TRIGGER

创建触发器

DROP TRIGGER

删除触发器

CREATE SCHEMA

添加模式

DROP SCHEMA

删除模式

CREATE DOMAIN

创建数据值域

ALTER DOMAIN

改变域定义

DROP DOMAIN

删除域

数据控制语言DCL

CRANT

授予用户访问权限

DENY

拒绝用户访问

REVOKE

解除用户访问权限

事务控制

COMMIT

结束当前事务

ROLLBACK

终止当前事务

SET TRANSACTION

定义当前事务数据访问特征

程序化SQL

DECLARE

为查询设定游标

EXPLAN

为查询描述数据访问计划

OPEN

检索查询结果打开一个游标

FETCH

检索一行查询结果

CLOSE

关闭游标

PREPARE

为动态执行准备SQL语句

EXECUTE

动态执行SQL语句

DESCRIBE

描述准备好的查询

2.2 数据查询语言DQL

一、SELECT

1.【格式】

SELECT 列名表

[INTO 新表名]

FROM 表或视图名

[WHERE 查询限定条件]

[GROUP BY 分组表达式]

[HAVING 分组条件]

[ORDER BY 次序表达式]

2.【示例】查询score表中至少有5名学生选修的并以3开头的课程号的平均分数。

SELECT cno,AVG(degree) AS ‘平均分’

FROM score

WHERE cno LIKE ‘3%’

GROUP BY cno

HAVING COUNT(*)>5

注：SQL的执行顺序是：

①执行WHERE子句，从表中选取行；

②由GROUP BY对选区的行进行分组；

③执行聚合函数；

④执行HAVING子句选区满足条件的分组（HAVING子句聚合函数的作用域为每一分组）。

2.3 数据操作语言

一、INSERT

1.【格式】

INSERT INTO <table_name>

[(<column_name>[{,<column_name>}…])]

VALUES(<value>[{,<value>}…])

注：“<>”为必选项，“[]”为可选项，“{}”为可重复出现项。

2.【示例】

INSERT INTO score

(sno,cno,score)

VALUES(1001,254,’95’)

二、UPDATE

1.【格式】

UPDATE<table_name>

SET<set clause expression>[{,< set clause expression >}…]

[WHERE <search_condition>]

注：“<>”为必选项，“[]”为可选项，“{}”为可重复出现项。

2.【示例】

UPDATE 商品信息

SET 产地=

(SELECT 供应商所在城市

FROM 供应商信息

WHERE 供应商名称=’南方电讯’),

备注=’货亿到’

WHERE 产地=’上海市’

三、DELETE

1.【格式】

DELETE FROM<table_name>

[WHERE <search_condition>]

注：“<>”为必选项，“[]”为可选项，“{}”为可重复出现项。

2.【示例】

DELETE FROM 业务员信息

WHERE 业务员姓名 LIKE ‘黄__’

2.4 数据定义语言

一、CREATE DATABASE

1.【格式】

CREATE DATABASE database_name

[

[ON [filespec]]

[ LOG ON [filespec]]

]

filespec定义为

([NAME=logical_file_name,]

FILENAME=’os_file_name’

[,SIZE=size]

[,MAXSIZE={max_size | UNLIMITED}]

[,FILEGROWTH=growth_increment])

2.【示例】

CREATE DATABASE School

ON

(NAME= School,

FILENAME='d:/Program Files/Microsoft SQL Server/MSSQL/Data/ School_Data.mdf',

SIZE=50MB,

MAXSIZE=2000,

FILEGROWTH=20)

LOG ON

(NAME= School_Log,

FILENAME='d:/Program Files/Microsoft SQL Server/MSSQL/Data/ School_Log.ldf',

SIZE=50MB,

MAXSIZE=2000,

FILEGROWTH=20)

二、ALTER DATABASE

1.【格式】

ALTER DATABASE database_name

{ ADD FILE filespec

| ADD LOG FILE filespec

| REMOVE FILE logical_file_name

| MODIFY FILE filespec

| MODIFY NAME=new_dbname

}

filespec定义为

([NAME=logical_file_name,]

FILENAME=’os_file_name’

[,SIZE=size]

[,MAXSIZE={max_size | UNLIMITED}]

[,FILEGROWTH=growth_increment])

2.【示例】

三、DROP DATABASE

1.【格式】

DROP DATABASE database_name

2.【示例】

DROP DATABASE School

四、CREATE TABLE

1.【格式】

CREATE TABLE table_name

(

         column_name1 data_type [NULL | NOT NULL] [PRIMARY | UNIQUE][FOREIGN KEY [(column_name)]] REFERENCES ref_table [(ref_column)]

         [column_name2 data_type…]

         …

)

2.【示例】

CREATE TABLE book (

         bid int NOT NULL PRIMARY KEY,

         bname char(8) NOT NULL,

         authorid int FOREIGN KEY REFERENCES authors(authorid)

)

五、ALTER TABLE

1.【格式】

ALTER TABLE table_name

ADD [column_name data_type]

         [PRIMARY KEY | CONSTRAIN]

         [FOREIGN KEY (column_name)

         REFERENCES ref_table(ref_column)]

DROP [CONSTRAINT] constraint_name | COLUMN column_name

2.【示例】

ALTER TABLE book ADD price int

六、DROP TABLE

1.【格式】

DROP TABLE table_name

2.【示例】

DROP TABLE book

七、CARETE VIEW

1.【格式】

CREATE VIEW [database_name.][owner.]view_name [(column [,…n])]

[WITH view_attribute [,…n]]

AS

Select_statement

[WITH CHECK OPTION]

view_attribute定义为：

{ENCRYPTION | SCHEMABINDING | VIEW_METADATA}

2.【示例】

USE School

GO

CREATE VIEW st2_degree

AS

SELECT student.sname AS ‘姓名’,course.cname AS ‘课程’,score.degree AS ‘成绩’

FROM student,course,score

WHERE student.sno=score.sno AND course.cno=score.cno

GO

八、ALTER VIEW

1.【格式】

ALTER VIEW [database_name.][owner.]view_name [(column [,…n])]

[WITH view_attribute [,…n]]

AS

select_statement

[WITH CHECK OPTION]

view_attribute定义为：

{ENCRYPTION | SCHEMABINDING | VIEW_METADATA}

2.【示例】

ALTER VIEW 珠宝信息_VIEW(商品名称,单价)

AS

SELECT 名称编号,单价

FROM 商品信息

WHERE 单价>1000

九、DROP VIEW

1.【格式】

DROP VIEW {view_name} [,…n]

2.【示例】

USE test

GO

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS _

                            WHERE TABLE_NAME=’View1’)

DROP VIEW View1

GO

十、CREATE INDEX

1.【格式】

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name

ON {table_name | ivew_name} (column [ASC | DESC][,…n])

[WITH index_option [,…n]]

[ON filegroup]

index_option定义为

{ PAD_INDEX | FILLFACTOR=fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB}

2.【示例】

USE School

IF EXISTS(SELECT name FROM sysindexes WHERE name=’IDX_tno’)

         DROP INDEX teacher.IDX_tno

GO

USE School

CREATE INDEX IDX_tno ON teacher(tno)

GO

十一、DROP INDEX

1.【格式】

DROP INDEX ‘table.index | view.index’ [,…n]

2.【示例】

USE test

GO

DROP INDEX table1.Idx1

GO

十二、CREATE PROCEDURE

1.【格式】

CREATE PROC[EDURE] procedure_name [; number]

[{@parameter data_type} [VARYING ][ = default][OUTPUT]]

[,…n]

[WITH

         {RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}]

[FOR REPLICATION]

AS sql_statement [,…n]

2.【示例】

USE School

IF EXISTS(SELECT name FROM sysobjects WHERE name=’stud_degree’ AND type=’P’)

         DROP PROCEDURE stud_degree

GO

USE school

GO

CREATE PROCEDURE stud_degree

AS

         SELECT student.sno,student.sname,course.cname,score.degree

         FROM student,course,score

         WHERE student.sno=score.sno AND course.cno=score.cno

         ORDER BY student.sno

GO

十三、ALTER PROCEDURE

1.【格式】

2.【示例】

十四、DROP PROCEDURE

1.【格式】

DROP PROCEDURE procedure_name

2.【示例】

DROP PROCEDURE test_ret

十五、CREATE TRIGGER

1.【格式】

CREATE TRIGGER trigger_name ON { table_name | view_name }

[WITH ENCRYPTION]

{

         { {FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}

                   [WITH APPEND]

                   [NOT FOR REPLICATION]

                   AS

                   [{IF UPDATE (column)

                            [{ AND | OR UPDATE (column)}

                                     […n]

                   | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)

                            {comparison_operator} column_bitmask […n]

                   }]

                  sql_statement […n]

         }

}

2.【示例】

CREATE TRIGGER [Hotel_Department_Update] ON dbo.Hotel_Department

FOR INSERT, UPDATE

AS

IF NOT EXISTS (SELECT * FROM Hotel WHERE ItemID IN (SELECT ItemID FROM inserted))

         BEGIN

                   PRINT '更新数据库失败，所属记录不存在！'

                  ROLLBACK TRAN                 --撤消操作

         END

         RETURN

GO

十六、ALTER TRIGGER

1.【格式】

ALTER TRIGGER trigger_name ON (table_name | view_name)

[WITH ENCRYPTION]

{

         {(FOR | AFTER | INSTEAD OF ) {[DELETE] [,] [INSERT] [,] [UPDATE]}

                   [NOT FOR REPLICATION]

                   AS

                  Sql_statement […n]

         }

         |

         {(FOR | AFTER | INSTEAD OF) {[INSERT] [,] [UPDATE] }

                   [NOT FOR REPLICATION]

                   AS

                   {IF UPDATE (column)

                   [{AND | OR} UPDATE (column)]

                   […n]

                   | IF (COLUMNS_UPDATED() {bitwise_operator}

                            updated_bitmask)

                  {comparison_operator} column_bitmask […n]

                   }

                  sql_statement […n]

         }

}

2.【示例】

十七、DROP TRIGGER

1.【格式】

DROP TRIGGER {trigger_name} [,…n]

2.【示例】

DROP TRIGGER trig1