2009年 11月 10日Designed by Tao Hongcai 1
第四章 SQL结构化查询语言
学习目的和要求
◆ 定义子语言
◆ 操纵子语言
◆ MS SQL SERVER和 SYBASE中的 T-SQL
2009年 11月 10日Designed by Tao Hongcai 2
4.1 SQL语言特点及分级
一, SQL语言的特点
② DML,SQL子集，插入、初除、修改和查询表中数据。
③ 嵌入 (Embedded)与动态 (Dynamic) SQL,嵌入 SQL允许 SQL代码
从宿主语言 (Host Language)程序中调用；动态 SQL允许在运行时
(run-time)构造幵执行。
⑤ Security,控制用户访问 DB对象的机制。
④ Triggers,在 SQL:1999中支持，一组由 DBMS执行的操作。
⑥ Transaction Management,相应命令，显式控制事务执行。
⑦ Client-Server Execution & Remote DB Access,客户应用程序如
何连接 DB和如何访问 DB中的数据。
① DDL,SQL子集，创建、修改和初除数据库、表和视图；定义
ICs；非标准索引的创建与初除。
2009年 11月 10日Designed by Tao Hongcai 3
二, SQL-92的分级
② Intermediate SQL,包含 SQL-92近一半的新特点。
③ Full SQL,完全版 SQL。
① Entry SQL,接近 SQL-89。
2009年 11月 10日Designed by Tao Hongcai 4
4.2 定义子语言 DDL
回答如下问题,
1.定义子语言的三个命令关键字？
2.数据库定义？
3.表定义？
5.索引定义？
4.视图定义？
2009年 11月 10日Designed by Tao Hongcai 5
一,定义子语言的三个命令关键字
说明：
(1) Create
(2) Drop
(3) Alter
① 各个命令关键字后应紧跟所要定义的对象关键字，如数据库
为 DATABASE、表为 TABLE、视图为 VIEW、索引为 INDEX。
例如,CREATE DATABASE,DROP DATABASE,ALTER DATABASE
等等。
② SQL SERVER中可用此三命令的数据库对象有,DATABASE、
TABLE,VIEW,INDEX,TRIGGER,PROCEDURE,RULE,DEFAULT、
FUNCTION。
③ SQL SERVER中，有几个数据库对象没有 ALTER命令，如：
INDEX,RULE,DEFAULT没有 ALTER。
2009年 11月 10日Designed by Tao Hongcai 6
二,定义数据库
1.数据库及日志
数据库,在中 /大型数据库系统中，数据库是一个存储空
间，用于存放数据库中的 数据库对象,包括表、视图、索引、
存储过程、触发器、与数据库安全性有关的控制机制以及其它
对象等。
日志（ Log）,是数据库故障恢复的重要手段和方法。用
于记录对数据库的各种操作及所涉及的相关数据，实际上也需
要一个存储空间。为安全起见，一般与数据库分开存放。
2009年 11月 10日Designed by Tao Hongcai 7
CREATE DATABASE 数据库名
[ ON
[ < filespec > [,...n ] ]
]
[ LOG ON { < filespec > [,...n ] } ]
Create Database命令语法,
2.创建数据库 （ CREATE DATABASE）
< filespec >,:=
[ PRIMARY ]
( [ NAME = 逻辑名,]
FILENAME = ‘OS文件的路径及名字 ’
[,SIZE = 文件刜始大小 ]
[,MAXSIZE = { 最大值 | UNLIMITED } ]
[,FILEGROWTH = 文件大小增量值 ] )
[,...n ]
2009年 11月 10日Designed by Tao Hongcai 8
Create Database StuData
On Primary
( Name = StuFile1,
Filename = `c:＼ production＼ data＼ StuFile1.mdf',
Size = 10MB,
MaxSize = 1000MB,
FileGrowth = 10MB),
( Name = StuFile2,
Filename = `c:＼ production＼ data＼ StuFile2.ndf',
Size = 10MB,
MaxSize = 1000MB,
FileGrowth = 10％ )
Log On
( Name = Stulog,
Filename = `c:＼ production＼ data＼ Stulog.ldf',
Size = 10MB,
MaxSize = 1000MB,
FileGrowth = 10MB)
示例,
2009年 11月 10日Designed by Tao Hongcai 9
① 主数据文件扩展名为,mdf；
说明,
② 次数据文件扩展名为,ndf；
③ 日志文件扩展名均为,ldf。
2009年 11月 10日Designed by Tao Hongcai 10
ALTER DATABASE 数据库名
{ ADD FILE < filespec > [,...n ]
| MODIFY FILE < filespec >
| REMOVE FILE 逻辑文件名
| ADD LOG FILE < filespec > [,...n ]
| MODIFY NAME = 新数据库名
}
<filespec>::=
( NAME = 逻辑文件名
[,NEWNAME = 新逻辑文件名 ]
[,FILENAME = ‘OS文件的路径及名字 ’ ]
[,SIZE =文件的刜始大小 ]
[,MAXSIZE =最大的文件尺寸
[,FILEGROWTH = 文件大小增量 ] )
3.修改数据库 （ ALTER DATABASE）
Alter Database命令语法,
2009年 11月 10日Designed by Tao Hongcai 11
DROP DATABASE 数据库名 [,...n ]
① sp_helpdb [数据库名 ]
4.删除数据库 （ DROP DATABASE）
Drop Database命令语法,
5.MS SQL SERVER中与数据库有关的系统存储过程
② sp_renamedb 数据库旧名，数据库新名
2009年 11月 10日Designed by Tao Hongcai 12
三,定义表
1.创建表 （ CREATE TABLE）
CREATE TABLE [ 数据库名,[ 拥有者 ], |拥有者, ] 表名
( { < 列定义 >
| 列名 AS 列计算表达式
| < 表级约束 > }
| [ { PRIMARY KEY | UNIQUE } [,...] ]
)
<列定义 >,:=
{ <列名 > <列类型 > [NULL|NOT NULL] }
[ [ DEFAULT 常数表达式 ]
| [ IDENTITY [ ( 刜值,步长 ) [ NOT FOR REPLICATION ] ] ] ]
[ ROWGUIDCOL]
[ < 列级约束 > ] [,.,]
Create Table 命令语法,
2009年 11月 10日Designed by Tao Hongcai 13
<列级约束 >,:=[ CONSTRAINT 约束名 ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] ]
| [ [ FOREIGN KEY ]
REFERENCES 参照表 [ (参照列 ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ] ]
| CHECK ( 逻辑表达式 )
}
<表级约束 >,:= [ CONSTRAINT 约束名 ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ] {(列名 [ASC | DESC] [,..,])}
| FOREIGN KEY [ ( 列名 [,..,] ) ]
REFERENCES 参照表 [ ( 参照列 [,..,] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
| CHECK ( 条件表达式 )
}
2009年 11月 10日Designed by Tao Hongcai 14
2.修改表 （ ALTER TABLE）
ALTER TABLE table
{ [ ALTER COLUMN 列名 { 新数据类型 [ NULL | NOT NULL ] } ]
| ADD { [ < 列定义 > ] | 列名 AS 计算表达式 } [,...]
| [ WITH CHECK | WITH NOCHECK ]
ADD { <表级约束 > } [,..,]
| DROP { [ CONSTRAINT ] 约束名
| COLUMN 列名 } [,..,]
| { CHECK | NOCHECK } CONSTRAINT { ALL | 约束名 [,..,] }
| { ENABLE | DISABLE } TRIGGER { ALL | 触发器名 [,...n ] }
}
Alter Table 命令语法,
DROP TABLE 表名
3.删除表 （ DROP TABLE）
Drop Table命令语法,
2009年 11月 10日Designed by Tao Hongcai 15
四,定义视图
1.创建 /修改视图 （ CREATE/ALTER VIEW）
CREATE/ALTER VIEW [ [拥有者 ], |拥有者,] 视图名 [ (视图列表 ) ]
AS
SQL 查询语句
[ WITH CHECK OPTION ]
Create/Alter View 命令语法,
2.删除视图 （ DROP VIEW）
DROP VIEW 视图名
Drop View 命令语法,
2009年 11月 10日Designed by Tao Hongcai 16
五,定义索引
1.索引
概念, 索引是关于数据位置信息的关键字表。
目的, 加快查询速度。
类型, 聚簇 (Clustered)索引、非聚簇 (Nonclustered)索引和唯一索
引。
聚簇索引,表中数据与索引存储在相邻物理空间，且表中行的
物理顺序与索引顺序保持一致。每张表最多只能建一个聚簇索引。
索引建立原则：
(4) 优先建立主键列的索引。
(1) 为数据量大的表建立索引；
(2) 一张表所建索引个数应适量；
(3) 掌握建立索引的时机；
唯一索引,被索引的列不能有相同值出现。
2009年 11月 10日Designed by Tao Hongcai 17
2.创建索引 （ CREATE INDEX）
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX <索引名 >
ON < 表名 | 视图名 > ( 列名 [ ASC | DESC ] [,..,] )
Create Index 命令语法,
DROP INDEX 索引名
3.删除索引 （ DROP INDEX）
Drop Index 命令语法,
2009年 11月 10日Designed by Tao Hongcai 18
4.3 操纵子语言 DML
回答如下问题,
1.操纵子语言的四个命令关键字？
2.数据插入？
3.数据修改？
5.数据查询？
4.数据初除？
2009年 11月 10日Designed by Tao Hongcai 19
一,操纵子语言的四个命令关键字
说明：
① 以上四个命令，均是针对数据的操作。具体说来，其
操纵的对象是：表以及满足条件的视图（如：基于一张表的视
图等）。
(4) Select
(1) Insert
(2) Update
(3) Delete
② 在定义了约束或限制的情况下，数据的, 增初改, 操
作必须遵守相关的限制条件。
2009年 11月 10日Designed by Tao Hongcai 20
二,数据插入 （ INSERT）
1.数据插入命令基本语法
INSERT [INTO] 表名或视图名 [(列名表 )] <数据值 >
说明,<数据值 >的写法决定具体的插入方式。
2.数据插入的两种方式
INSERT [INTO] 表名或视图名 [(列名表 )]
VALUES (列值表 )
(1) 插入一行
(2) 插入一行或多行
INSERT [INTO] 表名或视图名 [(列名表 )]
SELECT子句
2009年 11月 10日Designed by Tao Hongcai 21
三,数据修改 （ UPDATE）
1.数据修改命令的语法
UPDATE
{
表名 [ WITH ( < 表更新选项 > [,..n ] ) ]
| 视图名
}
SET
{ 列名 = { 表达式 | DEFAULT | NULL } } [,...n ]
{ { [ FROM { < 源表 > } [,...n ] ]
[ WHERE < 条件表达式 > ] }
}
<表更新选项 >,:= { PAGLOCK | READCOMMITTED | ROWLOCK |
TABLOCK | TABLOCKX | UPDLOCK }
2009年 11月 10日Designed by Tao Hongcai 22
< 源表 >,:=
表名 [ [ AS ] 别名 ] [ WITH ( < 表更新选项 > [,...n ] ) ]
| 视图名 [ [ AS ] 别名 ]
| < 表连接 >
< 表连接 >,:=
< 源表 > < 连接类型 > < 源表 > ON < 条件表达式 >
| < 源表 > CROSS JOIN < 源表 >
| < 表连接 >
< 连接类型 >,:=
[ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ] JOIN
2009年 11月 10日Designed by Tao Hongcai 23
2.数据修改示例
UPDATE publishers
SET city = 'Atlanta',state = 'GA'
示例 1,简单的更新
UPDATE titles SET price = price * 2
示例 2,带 WHERE子句的更新
UPDATE authors
SET state = 'PC',city = 'Bay City'
WHERE state = 'CA' AND city = 'Oakland'
2009年 11月 10日Designed by Tao Hongcai 24
UPDATE titles
SET ytd_sales = ( SELECT SUM(qty)
FROM sales
WHERE sales.title_id = titles.title_id AND
sales.ord_date IN ( SELECT MAX(ord_date)
FROM sales )
)
FROM titles,sales
示例 3,利用其他表数据的更新
2009年 11月 10日Designed by Tao Hongcai 25
示例 4,带连接的更新
CREATE TABLE s (ColA INT,ColB DECIMAL(10,3))
CREATE TABLE t (ColA INT PRIMARY KEY,ColB DECIMAL(10,3))
INSERT INTO s VALUES (1,10.0)
INSERT INTO s VALUES (1,20.0)
INSERT INTO t VALUES (1,0.0)
UPDATE t
SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
2009年 11月 10日Designed by Tao Hongcai 26
三,数据删除 （ DELETE）
1.数据删除命令的语法
DELETE [ FROM ]
{ 表名 [WITH ( < 表更新选项 > [,..n ] )]
| 视图名
}
[ FROM { < 源表 > } [,...n ] ]
[ WHERE { < 条件表达式 > } ]
2.分别基于 SQL-92,T-SQL的数据删除命令示例
⑴ SQL-92的写法
DELETE FROM titleauthor
WHERE title_id IN ( SELECT title_id
FROM titles
WHERE title LIKE '%computers%')
2009年 11月 10日Designed by Tao Hongcai 27
⑵ T-SQL的写法
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'
2009年 11月 10日Designed by Tao Hongcai 28
四,数据查询 （ SELECT）
1.查询命令语法
SELECT 查询列表
[ INTO 新表名 ]
FROM <源表 >
[ WHERE 条件表达式 ]
[ GROUP BY 分组表达式 ]
[ HAVING 组内数据条件表达式 ]
[ ORDER BY 排序表达式 [ ASC | DESC ] ]
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | SUM } ( 表达式 ) } [,...n ]
[ BY expression [,...n ] ]
]
说明,查询主要由一些子句构成，如,SELECT子句,INTO
子句 (Clause),FROM子句,WHERE子句,GROUP BY子句、
HAVING子句,ORDER BY子句,COMPUTE子句等。
2009年 11月 10日Designed by Tao Hongcai 29
2.SELECT子句
SELECT [ ALL | DISTINCT ] < select_list >
说明：
< select_list >,:=
{ *
| { 表名 | 视图名 | 表别名 }.*
| { 列名 | 表达式 } [ [ AS ] 列别名 ]
| 列别名 = 表达式
} [,...n ]
(1) ALL表示重复行可出现于结果中,DISTINCT则相反,ALL为
缺省情况；
(2) ＊表示 FROM子句的表、视图中的所有列均出现于结果中，
其顺序遵照表、视图中的顺序。
(3) 表达式中可用聚集函数。
2009年 11月 10日Designed by Tao Hongcai 30
3.INTO子句
作用,创建一新表幵将查询的结果数据插入其中。
说明：
语法,[ INTO 新表名 ]
(1) SELECT…… INTO不能与 COMPUTE一起使用；
(2) 可用 SELECT…… INTO创建一张与 FROM子句中的表具有相
同结构的、名字不同的新表。如果不需要数据，可令 WHERE子句条
件为永假即可。
2009年 11月 10日Designed by Tao Hongcai 31
4.FROM子句
作用,指定在 SELECT,DELETE及 UPDATE语句中所用的表、
视图及表连接等。
算术比较符,=,<,>,<=或 !>,>=或 !<,<>或 !=
语法,同前面 DELETE及 UPDATE中的 FROM语法。
逻辑比较符,AND,OR,NOT
5.WHERE子句
作用,指定限定行的查询条件。
特殊运算符,IN,NOT IN,BETWEEN… AND…, LIKE,NOT
LIKE,IS NULL,IS NOT NULL,SOME|ANY,ALL,EXISTS,NOT
EXISTS。
2009年 11月 10日Designed by Tao Hongcai 32
② 如希望 ％, _以常规字符出现，则应用 [ ]将其括起或用转义
符, $”，如,like,70$%” 或 like,70[%]” ===> 70%。
（ 1） LIKE：
① ％ （匹配任意一串字符）,_（匹配任意一个字符）,[ ]
（取其中任意单个字符）,^（非）。
③ ^应与 [ ]联用，如,[^a-f]或 [^abcdef]表示 a-f这几个字母不能
出现。
（ 2） SOME|ANY：
语法,表达式 { = | < > | ! = | > | > = | ! > | < | < = | ! < }
{ SOME | ANY } (子查询 )
（ 3） ALL：
语法,表达式 { = | < > | ! = | > | > = | ! > | < | < = | ! < }
ALL (子查询 )
2009年 11月 10日Designed by Tao Hongcai 33
含意,检查子查询是否有行返回，有则 TRUE，否则 FALSE；如
带 NOT则相反。
（ 4） [NOT] EXISTS：
（ 5） [NOT] IN：
语法,[NOT] EXISTS (子查询 )
含意,检查测试表达式的值是否在子查询或列表中，是则 TRUE，
否则 FALSE；如带 NOT则相反。
语法,测试表达式 [ NOT ] IN
(
子查询
| 表达式 [,...n ]
)
2009年 11月 10日Designed by Tao Hongcai 34
6.聚集函数
作用,用于对数据集合迚行统计，如求：总和、平均值、
最大值、最小值、行数。
COUNT ( DISTINCT | ALL 表达式 ) 返回非空表达式值的行数
COUNT (*) 返回结果的行数，
含 NULL行和重复行
MAX (DISTINCT | ALL 表达式 ) 非空表达式值的最大值
MIN (DISTINCT | ALL 表达式 ) 非空表达式值的最小值
SUM (DISTINCT | ALL 表达式 ) 非空表达式值的总和
AVG (DISTINCT | ALL 表达式 ) 非空表达式值的平均值
用法,一般用于 SELECT子句,HAVING子句和 ORDER BY子
句中。
函数形式及功能：
2009年 11月 10日Designed by Tao Hongcai 35
7.GROUP BY子句与 HAVING子句
GROUP BY子句作用,用于对查询的结果数据集合迚行
,分组, 或, 分组统计,,如对各个分组求：总和、平均值、
最大值、最小值、行数。
GROUP BY子句用法,一般与, 聚集函数, 联用。
说明：
HAVING子句作用,用于对分组数据集合的再筛选。
HAVING子句用法,须与 GROUP BY联用，不能单独使用。
(1) 要求 SELECT子句的列表中，除了使用聚集函数的列之外，
其余各列都必须出现在 Group By子句的列表中 ；
(2) 注意 WHERE,GROUP BY及 HAVING三个子句的执行顺序及含
意。 WHERE用于对 FROM子句结果设置过滤条件； GROUP BY用于对
WHERE子句的结果分组； HAVING则对 GROUP BY分组的结果再过滤。
2009年 11月 10日Designed by Tao Hongcai 36
8.ORDER BY子句
作用,用于对结果集迚行排序。
说明,ORDER BY中的列一般应在 SELECT子句中。
语法,ORDER BY { 排序表达式 [ ASC | DESC ] } [,...n]
9.COMPUTE子句与 COMPUTE BY子句
概念,COMPUTE用于对查询的结果数据集合最后迚行, 总
计,,如果带有 BY，则还可迚行, 小计, 。 COMPUTE BY可以
与 COMPUTE联用。
说明,COMPUTE BY应与 ORDER BY联用，且 COMPUTE BY
后的表达式应与 ORDER BY后的表达式（或其子集）内容与顺
序一致。
示例,如有 ORDER BY a,b,c 则可用如下任一 COMPUTE BY形式,
COMPUTE BY a,b,c
COMPUTE BY a,b
COMPUTE BY a
2009年 11月 10日Designed by Tao Hongcai 37
10.嵌套子查询 （ Nested Sub-Query）
概念,一个查询语句的查询结果作为另一个查询语句的条
件，这样的 SELECT语句为, 子查询,,亦称, 嵌套查询, 。
SELECT <查询列表 >
[ INTO <新表名 > ]
FROM <基表名 |视图名 > [ 别名 ] ……
WHERE <列名或列表达式 > <比较运算符 >
（
SELECT <查询列 >
FROM <基表名 |视图名 > [ 别名 ] ……
WHERE <条件表达式 >
[ GROUP BY <分组内容 >]
[ HAVING <组内条件 >]
）
[ GROUP BY <分组内容 >]
[ HAVING <组内条件 >]
[ ORDER BY <排序列名 >[ ASC | DESC ] ]
2009年 11月 10日Designed by Tao Hongcai 38
说明：
(1)子查询之中允许嵌套另一个子查询，但最多嵌套 255层，幵
且总是从嵌套层次最深的一层开始执行，然后再执行它的直接上一
层，直至完成整个查询。
(2) 子查询返回为单值时，可用, 算术比较符, ；返回多列或
多行值，则用 IN,ANY,ALL,EXISTS等运算符。
相关子查询,子查询与来自主查询（或称外查询）的列有
关。意味着子查询是重复执行的，每一次处理外查询结果的一
行。
示例,SELECT au_lname,au_fname
FROM authors
WHERE 100 IN
( SELECT royaltyper
FROM titleauthor
WHERE titleauthor.au_ID = authors.au_id)
2009年 11月 10日Designed by Tao Hongcai 39
11.UNION查询
概念,将两个或多个查询的结果合幵成一个结果返回 。
语法,<SELECT语句 > UNION [ALL] <SELECT语句 >
说明：
(1) 用 UNION合幵的结果集应有相同的结构，即列数相同、对应
列数据类型兼容。
(2) 最后结果的列名取与第一个 SELECT语句返回的列名。
(3) 默认情况下，最后结果会去掉重复行；但如有 ALL选项，则
保留重复行。
(4) 一般,UNION个数不限，且按从左至右顺序执行。
(5) 当用 UNION时，各个 SELECT语句不能有 ORDER BY和
COMPUTE子句，而只能在最后一个 SELECT语句后带一个 ORDER BY
和 COMPUTE子句，它们是针对最后结果的。不过，各个 SELECT语句
可用 GROUP BY和 HAVING子句。
2009年 11月 10日Designed by Tao Hongcai 40
4.4 Transact-SQL语言
讲解内容,
1.T-SQL语言简介
2.T-SQL编程
3.T-SQL提供的函数
5.T-SQL存储过程
4.T-SQL游标
2009年 11月 10日Designed by Tao Hongcai 41
一,T-SQL语言简介
T-SQL的存储过程,由 SQL语句组成，分系统存储过程和
用户自定义存储过程。
T-SQL组成,SQL语句、函数和存储过程。
T-SQL的子语言,数据定义语言 DDL （ Data Definition
Language）,数据操纵语言 DML（ Data Manipulation Language）
和数据控制语言 DCL（ Data Control Language）。
T-SQL的函数,系统函数、聚集函数、数学函数、字符串
函数、数据类型转换函数和日期函数等。
2009年 11月 10日Designed by Tao Hongcai 42
二,T-SQL编程
表示及概念,@变量名，由用户定义幵使用。
1.注释 (Comment)方式
(1) 多行时，用 /* …, */
(2) 单行时，用 --
2.变量
局部变量定义,DECLARE @变量名 类型 [,… n]
（ 1）局部变量
说明：
(1) 变量一旦定义，系统自动赋 NULL值。
(2) 对局部变量的赋值用 SELECT语句。
2009年 11月 10日Designed by Tao Hongcai 43
表示及概念,@@变量名,由系统定义，用户可使用之。
3.SELECT用法
作用,用于指明系统运行过程中的运行状态，用户只能引
用，不能修改和定义。
（ 2）全局变量
常用的全局变量,
(1) @@error：返回最后一个语句产生的错误码。
(2) @@rowcount：返回语句执行后受影响的行数。
(3) @@version,SQL SERVER版本号。
(4) @@trancount：事务计数。
(5) @@transtate：事务状态。
(1) 查看表或视图的内容；
(2) 执行函数，返回值到客户端,SELECT db_name()
2009年 11月 10日Designed by Tao Hongcai 44
4.流程控制语句
① 直接赋值,Select @xxxx =2。一次只能一个。
② 从表中提取值赋给变量（应保证是唯一行且类型一
致）。
(3)给变量赋值
(4)查看变量值,Select @变量
(1) IF (条件关系式 ) ELSE [IF],如果关系式含有 SELECT语句，
则该 SELECT语句须用圆括号括起，其嵌套级别最多为 150；
(2) BEGIN,.,END,用于界定由多条 SQL语句组成的语句块；
示例,SELECT @tp=price FROM titles T WHERE T.id=?Bu1032?
(3) WHILE,..BREAK/CONTINUE
2009年 11月 10日Designed by Tao Hongcai 45
5.信息显示
(4) GOTO,..,跳到用户定义的标号处；
(5) RETURN,无条件退出；
(6) IF EXISTS/IF NOT EXISTS,是否有数据存在
(7) WAITFOR,延迟某段时间
示例,WAITFOR DELAY '00:30:00? /* 延迟 30分钟 */
语法,WAITFOR { DELAY 日期时间格式的时间值
| TIME 时间 | … }
(1) Print ?…,.?
(2) Print @msg
(3)用参数嵌套形式加入不同的值：
Print ?…,%1!…,.%2!…,?,@参数 1,@参数 2,·····
2009年 11月 10日Designed by Tao Hongcai 46
6.RAISERROR
用途,系统有很多系统信息及其代码（代码值在两万以
下），用户也可用 RAISERROR，自己定义错误信息及其代码
（其代码值应在两万以上）。
语法,RAISERROR 错误代码
{ ?错误信息 ’ | 局部变量 } [,参数表 ]
三,T-SQL提供的函数
1.字符串操作
(1) 连接操作, +
(2) convert函数
作用,将表达式按指定风格转换成指定的类型，其中的 style只
用于日期类型到字符串的转换。
语法,convert(类型符，表达式 [,style])
2009年 11月 10日Designed by Tao Hongcai 47
2.有关日期的几个函数
(3)日期 ===> 字符串
(1) getdate()
(2) datename()与 datepart()
datename(日期元素,日期表达式 ),以字符串形式返回日期
元素指定的日期的名字
style取值 输出格式
2 yy.mm.dd
3 dd/mm/yy
4 dd.mm.yy
5 dd-mm-yy
102 yyyy.mm.dd
103 dd/mm/yyyy
104 dd.mm.yyyy
105 dd-mm-yyyy
示例：
convert(char(10),pubdate,105)
其中,pubdate为日期型。
用途,将服务器 OS的时间送客户机。
示例,SELECT getdate()
datepart(日期元素,日期表达式 ),以数字形式返回日期元素
指定的日期的名字
2009年 11月 10日Designed by Tao Hongcai 48
日期元素及其指定返回的日期部分为：
示例：
Select datetime(mm,pubdate) from titiles where title_id=?Bu1032?
返回 June，如用 datepart则返回 6。
① yy,返回日期表达式中的年 year或年数。
② qq,返回日期表达式表示的季 quarter或季数。
③ mm,返回日期表达式中的月 month或月数。
④ dw,返回日期表达式表示的星期几 day of week。
⑤ dy,返回日期表达式表示的一年中的第几天 day of year
⑥ dd,返回日期表达式中的天或天数 day。
⑦ wk,返回日期表达式表示的一年中的第几个星期 week
或星期数。
⑧ hh,返回日期表达式中的小时 hour或小时数。
2009年 11月 10日Designed by Tao Hongcai 49
3.数学函数
(3) dateadd()与 datediff()
dateadd(日期元素，数值，日期表达式 ),将数值转换成日
期元素指定的部分加到日期表达式上返回。
datediff(日期元素，较早日期表达式，较晚日期表达式 )：
两个日期相减后，按日期元素指定部分转化后返回。
函 数 示 例 结 果
abs(数值表达式 ) abs(-100) 100
ceiling(数值表达式 ) ceiling(99.2) 100
floor(数值表达式 ) floor(99.2) 99
round(数值表达式，整数表达式 ) round(66.2387,2) 66.24
exp(浮点表达式 ) exp(0) 1
rand([整数 ]) rand(23)
log(浮点表达式 ) log(1) 0
pi() pi() 3.141?592?65...
power(数值表达式，指数表达式 ) power(2,10) 1?024
sqrt(数值表达式 ) sqrt(4) 2
sin(浮点表达式 ),cos(浮点表达式 ),tan(浮点表达式 ) sin(pi()) 0
2009年 11月 10日Designed by Tao Hongcai 50
四,T-SQL游标
Declare 游标名 CURSOR
FOR Select语句
1.游标生命周期
定义,Open Cursor,Fetch,Close cursor,Deallocate
2.定义游标
语法：
3.打开游标,打开后指针指向游标首位
语法,OPEN 游标名
用法,Fetch一次，指针下移一行。同时有二个全局变量将
受影响，即,@@sqlstate,0—成功； 1—失败； 2—No more data。
@@rowcount,每移一次累加一。
4.FETCH
语法,Fetch 游标名 Into 局部变量列表
2009年 11月 10日Designed by Tao Hongcai 51
6.Close和 Deallocate
7.用游标对数据操作
语法,Close 游标名； Deallocate Cursor 游标名
(1) Delete 表名 Where Current of 游标名
(2) Update 表名 Set 列名 =值 [,····] Where Current of 游标名
说明,初除后指针不动，下面的行自动上移。
8.游标示例
DECLARE books_csr CURSOR FOR
SELECT title_id,type,price FROM titles
(1) 游标定义
(2) 游标使用
2009年 11月 10日Designed by Tao Hongcai 52
Declare @title_id tid,@type char(12),@price money
Open books_csr
Fetch books_csr into @title_id,@type,@price
While @@sqlstatus != 2
Begin
if @@sqlstatus = 1
begin
raiserror 30001 ?select failed?
return
end
if @type= ?business?
select @title_id,@type,@price,convert(money,@price*1.08)
else if @type = ?mod_cook?
select @title_id,@type,@price
fetch books_csr into @title_id,@type,@price
End
2009年 11月 10日Designed by Tao Hongcai 53
五,T-SQL存储过程
可在命令行或批中调用。非第一条时应加 exec
1.创建存贮过程
Create Proc 过程名
As SQL语句
[Return]
2.执行
3.查看
sp_helptext 过程名
4.改名
sp_rename 旧过程名，新名
2009年 11月 10日Designed by Tao Hongcai 54
(1) 创建
5.删除
Drop Proc 过程名
6.带传递参数的存贮过程
Create Proc 过程名
( @变量 类型 [,…, ] )
As SQL 语句
Return
(2) 传递参数、执行
exec 过程名 参数值 1 [，参数值 2,······]
7.从存储过程返回值
(1) 创建
2009年 11月 10日Designed by Tao Hongcai 55
Create Proc 过程名
( @变量 类型 [,…, ]
@变量 类型 OUTPUT)
As SQL 语句
Return
(2) 调用
先定义一个局部变量，用于接收输出值。
8.对存贮过程的限制
(1)某些命令不能放在过程中
Create View/Default/Rule/Trigger/Proc等。
(2)不能在同一过程中刚初除一个 DB对象，又创建同名的
对象。
9.存贮过程示例
2009年 11月 10日Designed by Tao Hongcai 56
CREATE PROC num_sales
( @book_id char(6),/* 输入参数 */
@tot_sales int OUTPUT ) /* for output */
AS
SELECT @tot_sales=sum(qty)
FROM salesdetail
WHERE title_id = @book_id
RETURN
(1) 存储过程创建
(2) 调用
DECLARE @total int
EXEC num_sales @book_id=“pe53995”,@tot_sales=@total
OUTPUT
或 EXEC num_sales,pe53995”,@total OUTPUT
── The End ──
第四章 SQL结构化查询语言
学习目的和要求
◆ 定义子语言
◆ 操纵子语言
◆ MS SQL SERVER和 SYBASE中的 T-SQL
2009年 11月 10日Designed by Tao Hongcai 2
4.1 SQL语言特点及分级
一, SQL语言的特点
② DML,SQL子集，插入、初除、修改和查询表中数据。
③ 嵌入 (Embedded)与动态 (Dynamic) SQL,嵌入 SQL允许 SQL代码
从宿主语言 (Host Language)程序中调用；动态 SQL允许在运行时
(run-time)构造幵执行。
⑤ Security,控制用户访问 DB对象的机制。
④ Triggers,在 SQL:1999中支持，一组由 DBMS执行的操作。
⑥ Transaction Management,相应命令，显式控制事务执行。
⑦ Client-Server Execution & Remote DB Access,客户应用程序如
何连接 DB和如何访问 DB中的数据。
① DDL,SQL子集，创建、修改和初除数据库、表和视图；定义
ICs；非标准索引的创建与初除。
2009年 11月 10日Designed by Tao Hongcai 3
二, SQL-92的分级
② Intermediate SQL,包含 SQL-92近一半的新特点。
③ Full SQL,完全版 SQL。
① Entry SQL,接近 SQL-89。
2009年 11月 10日Designed by Tao Hongcai 4
4.2 定义子语言 DDL
回答如下问题,
1.定义子语言的三个命令关键字？
2.数据库定义？
3.表定义？
5.索引定义？
4.视图定义？
2009年 11月 10日Designed by Tao Hongcai 5
一,定义子语言的三个命令关键字
说明：
(1) Create
(2) Drop
(3) Alter
① 各个命令关键字后应紧跟所要定义的对象关键字，如数据库
为 DATABASE、表为 TABLE、视图为 VIEW、索引为 INDEX。
例如,CREATE DATABASE,DROP DATABASE,ALTER DATABASE
等等。
② SQL SERVER中可用此三命令的数据库对象有,DATABASE、
TABLE,VIEW,INDEX,TRIGGER,PROCEDURE,RULE,DEFAULT、
FUNCTION。
③ SQL SERVER中，有几个数据库对象没有 ALTER命令，如：
INDEX,RULE,DEFAULT没有 ALTER。
2009年 11月 10日Designed by Tao Hongcai 6
二,定义数据库
1.数据库及日志
数据库,在中 /大型数据库系统中，数据库是一个存储空
间，用于存放数据库中的 数据库对象,包括表、视图、索引、
存储过程、触发器、与数据库安全性有关的控制机制以及其它
对象等。
日志（ Log）,是数据库故障恢复的重要手段和方法。用
于记录对数据库的各种操作及所涉及的相关数据，实际上也需
要一个存储空间。为安全起见，一般与数据库分开存放。
2009年 11月 10日Designed by Tao Hongcai 7
CREATE DATABASE 数据库名
[ ON
[ < filespec > [,...n ] ]
]
[ LOG ON { < filespec > [,...n ] } ]
Create Database命令语法,
2.创建数据库 （ CREATE DATABASE）
< filespec >,:=
[ PRIMARY ]
( [ NAME = 逻辑名,]
FILENAME = ‘OS文件的路径及名字 ’
[,SIZE = 文件刜始大小 ]
[,MAXSIZE = { 最大值 | UNLIMITED } ]
[,FILEGROWTH = 文件大小增量值 ] )
[,...n ]
2009年 11月 10日Designed by Tao Hongcai 8
Create Database StuData
On Primary
( Name = StuFile1,
Filename = `c:＼ production＼ data＼ StuFile1.mdf',
Size = 10MB,
MaxSize = 1000MB,
FileGrowth = 10MB),
( Name = StuFile2,
Filename = `c:＼ production＼ data＼ StuFile2.ndf',
Size = 10MB,
MaxSize = 1000MB,
FileGrowth = 10％ )
Log On
( Name = Stulog,
Filename = `c:＼ production＼ data＼ Stulog.ldf',
Size = 10MB,
MaxSize = 1000MB,
FileGrowth = 10MB)
示例,
2009年 11月 10日Designed by Tao Hongcai 9
① 主数据文件扩展名为,mdf；
说明,
② 次数据文件扩展名为,ndf；
③ 日志文件扩展名均为,ldf。
2009年 11月 10日Designed by Tao Hongcai 10
ALTER DATABASE 数据库名
{ ADD FILE < filespec > [,...n ]
| MODIFY FILE < filespec >
| REMOVE FILE 逻辑文件名
| ADD LOG FILE < filespec > [,...n ]
| MODIFY NAME = 新数据库名
}
<filespec>::=
( NAME = 逻辑文件名
[,NEWNAME = 新逻辑文件名 ]
[,FILENAME = ‘OS文件的路径及名字 ’ ]
[,SIZE =文件的刜始大小 ]
[,MAXSIZE =最大的文件尺寸
[,FILEGROWTH = 文件大小增量 ] )
3.修改数据库 （ ALTER DATABASE）
Alter Database命令语法,
2009年 11月 10日Designed by Tao Hongcai 11
DROP DATABASE 数据库名 [,...n ]
① sp_helpdb [数据库名 ]
4.删除数据库 （ DROP DATABASE）
Drop Database命令语法,
5.MS SQL SERVER中与数据库有关的系统存储过程
② sp_renamedb 数据库旧名，数据库新名
2009年 11月 10日Designed by Tao Hongcai 12
三,定义表
1.创建表 （ CREATE TABLE）
CREATE TABLE [ 数据库名,[ 拥有者 ], |拥有者, ] 表名
( { < 列定义 >
| 列名 AS 列计算表达式
| < 表级约束 > }
| [ { PRIMARY KEY | UNIQUE } [,...] ]
)
<列定义 >,:=
{ <列名 > <列类型 > [NULL|NOT NULL] }
[ [ DEFAULT 常数表达式 ]
| [ IDENTITY [ ( 刜值,步长 ) [ NOT FOR REPLICATION ] ] ] ]
[ ROWGUIDCOL]
[ < 列级约束 > ] [,.,]
Create Table 命令语法,
2009年 11月 10日Designed by Tao Hongcai 13
<列级约束 >,:=[ CONSTRAINT 约束名 ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] ]
| [ [ FOREIGN KEY ]
REFERENCES 参照表 [ (参照列 ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ] ]
| CHECK ( 逻辑表达式 )
}
<表级约束 >,:= [ CONSTRAINT 约束名 ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ] {(列名 [ASC | DESC] [,..,])}
| FOREIGN KEY [ ( 列名 [,..,] ) ]
REFERENCES 参照表 [ ( 参照列 [,..,] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
| CHECK ( 条件表达式 )
}
2009年 11月 10日Designed by Tao Hongcai 14
2.修改表 （ ALTER TABLE）
ALTER TABLE table
{ [ ALTER COLUMN 列名 { 新数据类型 [ NULL | NOT NULL ] } ]
| ADD { [ < 列定义 > ] | 列名 AS 计算表达式 } [,...]
| [ WITH CHECK | WITH NOCHECK ]
ADD { <表级约束 > } [,..,]
| DROP { [ CONSTRAINT ] 约束名
| COLUMN 列名 } [,..,]
| { CHECK | NOCHECK } CONSTRAINT { ALL | 约束名 [,..,] }
| { ENABLE | DISABLE } TRIGGER { ALL | 触发器名 [,...n ] }
}
Alter Table 命令语法,
DROP TABLE 表名
3.删除表 （ DROP TABLE）
Drop Table命令语法,
2009年 11月 10日Designed by Tao Hongcai 15
四,定义视图
1.创建 /修改视图 （ CREATE/ALTER VIEW）
CREATE/ALTER VIEW [ [拥有者 ], |拥有者,] 视图名 [ (视图列表 ) ]
AS
SQL 查询语句
[ WITH CHECK OPTION ]
Create/Alter View 命令语法,
2.删除视图 （ DROP VIEW）
DROP VIEW 视图名
Drop View 命令语法,
2009年 11月 10日Designed by Tao Hongcai 16
五,定义索引
1.索引
概念, 索引是关于数据位置信息的关键字表。
目的, 加快查询速度。
类型, 聚簇 (Clustered)索引、非聚簇 (Nonclustered)索引和唯一索
引。
聚簇索引,表中数据与索引存储在相邻物理空间，且表中行的
物理顺序与索引顺序保持一致。每张表最多只能建一个聚簇索引。
索引建立原则：
(4) 优先建立主键列的索引。
(1) 为数据量大的表建立索引；
(2) 一张表所建索引个数应适量；
(3) 掌握建立索引的时机；
唯一索引,被索引的列不能有相同值出现。
2009年 11月 10日Designed by Tao Hongcai 17
2.创建索引 （ CREATE INDEX）
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX <索引名 >
ON < 表名 | 视图名 > ( 列名 [ ASC | DESC ] [,..,] )
Create Index 命令语法,
DROP INDEX 索引名
3.删除索引 （ DROP INDEX）
Drop Index 命令语法,
2009年 11月 10日Designed by Tao Hongcai 18
4.3 操纵子语言 DML
回答如下问题,
1.操纵子语言的四个命令关键字？
2.数据插入？
3.数据修改？
5.数据查询？
4.数据初除？
2009年 11月 10日Designed by Tao Hongcai 19
一,操纵子语言的四个命令关键字
说明：
① 以上四个命令，均是针对数据的操作。具体说来，其
操纵的对象是：表以及满足条件的视图（如：基于一张表的视
图等）。
(4) Select
(1) Insert
(2) Update
(3) Delete
② 在定义了约束或限制的情况下，数据的, 增初改, 操
作必须遵守相关的限制条件。
2009年 11月 10日Designed by Tao Hongcai 20
二,数据插入 （ INSERT）
1.数据插入命令基本语法
INSERT [INTO] 表名或视图名 [(列名表 )] <数据值 >
说明,<数据值 >的写法决定具体的插入方式。
2.数据插入的两种方式
INSERT [INTO] 表名或视图名 [(列名表 )]
VALUES (列值表 )
(1) 插入一行
(2) 插入一行或多行
INSERT [INTO] 表名或视图名 [(列名表 )]
SELECT子句
2009年 11月 10日Designed by Tao Hongcai 21
三,数据修改 （ UPDATE）
1.数据修改命令的语法
UPDATE
{
表名 [ WITH ( < 表更新选项 > [,..n ] ) ]
| 视图名
}
SET
{ 列名 = { 表达式 | DEFAULT | NULL } } [,...n ]
{ { [ FROM { < 源表 > } [,...n ] ]
[ WHERE < 条件表达式 > ] }
}
<表更新选项 >,:= { PAGLOCK | READCOMMITTED | ROWLOCK |
TABLOCK | TABLOCKX | UPDLOCK }
2009年 11月 10日Designed by Tao Hongcai 22
< 源表 >,:=
表名 [ [ AS ] 别名 ] [ WITH ( < 表更新选项 > [,...n ] ) ]
| 视图名 [ [ AS ] 别名 ]
| < 表连接 >
< 表连接 >,:=
< 源表 > < 连接类型 > < 源表 > ON < 条件表达式 >
| < 源表 > CROSS JOIN < 源表 >
| < 表连接 >
< 连接类型 >,:=
[ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ] JOIN
2009年 11月 10日Designed by Tao Hongcai 23
2.数据修改示例
UPDATE publishers
SET city = 'Atlanta',state = 'GA'
示例 1,简单的更新
UPDATE titles SET price = price * 2
示例 2,带 WHERE子句的更新
UPDATE authors
SET state = 'PC',city = 'Bay City'
WHERE state = 'CA' AND city = 'Oakland'
2009年 11月 10日Designed by Tao Hongcai 24
UPDATE titles
SET ytd_sales = ( SELECT SUM(qty)
FROM sales
WHERE sales.title_id = titles.title_id AND
sales.ord_date IN ( SELECT MAX(ord_date)
FROM sales )
)
FROM titles,sales
示例 3,利用其他表数据的更新
2009年 11月 10日Designed by Tao Hongcai 25
示例 4,带连接的更新
CREATE TABLE s (ColA INT,ColB DECIMAL(10,3))
CREATE TABLE t (ColA INT PRIMARY KEY,ColB DECIMAL(10,3))
INSERT INTO s VALUES (1,10.0)
INSERT INTO s VALUES (1,20.0)
INSERT INTO t VALUES (1,0.0)
UPDATE t
SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
2009年 11月 10日Designed by Tao Hongcai 26
三,数据删除 （ DELETE）
1.数据删除命令的语法
DELETE [ FROM ]
{ 表名 [WITH ( < 表更新选项 > [,..n ] )]
| 视图名
}
[ FROM { < 源表 > } [,...n ] ]
[ WHERE { < 条件表达式 > } ]
2.分别基于 SQL-92,T-SQL的数据删除命令示例
⑴ SQL-92的写法
DELETE FROM titleauthor
WHERE title_id IN ( SELECT title_id
FROM titles
WHERE title LIKE '%computers%')
2009年 11月 10日Designed by Tao Hongcai 27
⑵ T-SQL的写法
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'
2009年 11月 10日Designed by Tao Hongcai 28
四,数据查询 （ SELECT）
1.查询命令语法
SELECT 查询列表
[ INTO 新表名 ]
FROM <源表 >
[ WHERE 条件表达式 ]
[ GROUP BY 分组表达式 ]
[ HAVING 组内数据条件表达式 ]
[ ORDER BY 排序表达式 [ ASC | DESC ] ]
[ COMPUTE
{ { AVG | COUNT | MAX | MIN | SUM } ( 表达式 ) } [,...n ]
[ BY expression [,...n ] ]
]
说明,查询主要由一些子句构成，如,SELECT子句,INTO
子句 (Clause),FROM子句,WHERE子句,GROUP BY子句、
HAVING子句,ORDER BY子句,COMPUTE子句等。
2009年 11月 10日Designed by Tao Hongcai 29
2.SELECT子句
SELECT [ ALL | DISTINCT ] < select_list >
说明：
< select_list >,:=
{ *
| { 表名 | 视图名 | 表别名 }.*
| { 列名 | 表达式 } [ [ AS ] 列别名 ]
| 列别名 = 表达式
} [,...n ]
(1) ALL表示重复行可出现于结果中,DISTINCT则相反,ALL为
缺省情况；
(2) ＊表示 FROM子句的表、视图中的所有列均出现于结果中，
其顺序遵照表、视图中的顺序。
(3) 表达式中可用聚集函数。
2009年 11月 10日Designed by Tao Hongcai 30
3.INTO子句
作用,创建一新表幵将查询的结果数据插入其中。
说明：
语法,[ INTO 新表名 ]
(1) SELECT…… INTO不能与 COMPUTE一起使用；
(2) 可用 SELECT…… INTO创建一张与 FROM子句中的表具有相
同结构的、名字不同的新表。如果不需要数据，可令 WHERE子句条
件为永假即可。
2009年 11月 10日Designed by Tao Hongcai 31
4.FROM子句
作用,指定在 SELECT,DELETE及 UPDATE语句中所用的表、
视图及表连接等。
算术比较符,=,<,>,<=或 !>,>=或 !<,<>或 !=
语法,同前面 DELETE及 UPDATE中的 FROM语法。
逻辑比较符,AND,OR,NOT
5.WHERE子句
作用,指定限定行的查询条件。
特殊运算符,IN,NOT IN,BETWEEN… AND…, LIKE,NOT
LIKE,IS NULL,IS NOT NULL,SOME|ANY,ALL,EXISTS,NOT
EXISTS。
2009年 11月 10日Designed by Tao Hongcai 32
② 如希望 ％, _以常规字符出现，则应用 [ ]将其括起或用转义
符, $”，如,like,70$%” 或 like,70[%]” ===> 70%。
（ 1） LIKE：
① ％ （匹配任意一串字符）,_（匹配任意一个字符）,[ ]
（取其中任意单个字符）,^（非）。
③ ^应与 [ ]联用，如,[^a-f]或 [^abcdef]表示 a-f这几个字母不能
出现。
（ 2） SOME|ANY：
语法,表达式 { = | < > | ! = | > | > = | ! > | < | < = | ! < }
{ SOME | ANY } (子查询 )
（ 3） ALL：
语法,表达式 { = | < > | ! = | > | > = | ! > | < | < = | ! < }
ALL (子查询 )
2009年 11月 10日Designed by Tao Hongcai 33
含意,检查子查询是否有行返回，有则 TRUE，否则 FALSE；如
带 NOT则相反。
（ 4） [NOT] EXISTS：
（ 5） [NOT] IN：
语法,[NOT] EXISTS (子查询 )
含意,检查测试表达式的值是否在子查询或列表中，是则 TRUE，
否则 FALSE；如带 NOT则相反。
语法,测试表达式 [ NOT ] IN
(
子查询
| 表达式 [,...n ]
)
2009年 11月 10日Designed by Tao Hongcai 34
6.聚集函数
作用,用于对数据集合迚行统计，如求：总和、平均值、
最大值、最小值、行数。
COUNT ( DISTINCT | ALL 表达式 ) 返回非空表达式值的行数
COUNT (*) 返回结果的行数，
含 NULL行和重复行
MAX (DISTINCT | ALL 表达式 ) 非空表达式值的最大值
MIN (DISTINCT | ALL 表达式 ) 非空表达式值的最小值
SUM (DISTINCT | ALL 表达式 ) 非空表达式值的总和
AVG (DISTINCT | ALL 表达式 ) 非空表达式值的平均值
用法,一般用于 SELECT子句,HAVING子句和 ORDER BY子
句中。
函数形式及功能：
2009年 11月 10日Designed by Tao Hongcai 35
7.GROUP BY子句与 HAVING子句
GROUP BY子句作用,用于对查询的结果数据集合迚行
,分组, 或, 分组统计,,如对各个分组求：总和、平均值、
最大值、最小值、行数。
GROUP BY子句用法,一般与, 聚集函数, 联用。
说明：
HAVING子句作用,用于对分组数据集合的再筛选。
HAVING子句用法,须与 GROUP BY联用，不能单独使用。
(1) 要求 SELECT子句的列表中，除了使用聚集函数的列之外，
其余各列都必须出现在 Group By子句的列表中 ；
(2) 注意 WHERE,GROUP BY及 HAVING三个子句的执行顺序及含
意。 WHERE用于对 FROM子句结果设置过滤条件； GROUP BY用于对
WHERE子句的结果分组； HAVING则对 GROUP BY分组的结果再过滤。
2009年 11月 10日Designed by Tao Hongcai 36
8.ORDER BY子句
作用,用于对结果集迚行排序。
说明,ORDER BY中的列一般应在 SELECT子句中。
语法,ORDER BY { 排序表达式 [ ASC | DESC ] } [,...n]
9.COMPUTE子句与 COMPUTE BY子句
概念,COMPUTE用于对查询的结果数据集合最后迚行, 总
计,,如果带有 BY，则还可迚行, 小计, 。 COMPUTE BY可以
与 COMPUTE联用。
说明,COMPUTE BY应与 ORDER BY联用，且 COMPUTE BY
后的表达式应与 ORDER BY后的表达式（或其子集）内容与顺
序一致。
示例,如有 ORDER BY a,b,c 则可用如下任一 COMPUTE BY形式,
COMPUTE BY a,b,c
COMPUTE BY a,b
COMPUTE BY a
2009年 11月 10日Designed by Tao Hongcai 37
10.嵌套子查询 （ Nested Sub-Query）
概念,一个查询语句的查询结果作为另一个查询语句的条
件，这样的 SELECT语句为, 子查询,,亦称, 嵌套查询, 。
SELECT <查询列表 >
[ INTO <新表名 > ]
FROM <基表名 |视图名 > [ 别名 ] ……
WHERE <列名或列表达式 > <比较运算符 >
（
SELECT <查询列 >
FROM <基表名 |视图名 > [ 别名 ] ……
WHERE <条件表达式 >
[ GROUP BY <分组内容 >]
[ HAVING <组内条件 >]
）
[ GROUP BY <分组内容 >]
[ HAVING <组内条件 >]
[ ORDER BY <排序列名 >[ ASC | DESC ] ]
2009年 11月 10日Designed by Tao Hongcai 38
说明：
(1)子查询之中允许嵌套另一个子查询，但最多嵌套 255层，幵
且总是从嵌套层次最深的一层开始执行，然后再执行它的直接上一
层，直至完成整个查询。
(2) 子查询返回为单值时，可用, 算术比较符, ；返回多列或
多行值，则用 IN,ANY,ALL,EXISTS等运算符。
相关子查询,子查询与来自主查询（或称外查询）的列有
关。意味着子查询是重复执行的，每一次处理外查询结果的一
行。
示例,SELECT au_lname,au_fname
FROM authors
WHERE 100 IN
( SELECT royaltyper
FROM titleauthor
WHERE titleauthor.au_ID = authors.au_id)
2009年 11月 10日Designed by Tao Hongcai 39
11.UNION查询
概念,将两个或多个查询的结果合幵成一个结果返回 。
语法,<SELECT语句 > UNION [ALL] <SELECT语句 >
说明：
(1) 用 UNION合幵的结果集应有相同的结构，即列数相同、对应
列数据类型兼容。
(2) 最后结果的列名取与第一个 SELECT语句返回的列名。
(3) 默认情况下，最后结果会去掉重复行；但如有 ALL选项，则
保留重复行。
(4) 一般,UNION个数不限，且按从左至右顺序执行。
(5) 当用 UNION时，各个 SELECT语句不能有 ORDER BY和
COMPUTE子句，而只能在最后一个 SELECT语句后带一个 ORDER BY
和 COMPUTE子句，它们是针对最后结果的。不过，各个 SELECT语句
可用 GROUP BY和 HAVING子句。
2009年 11月 10日Designed by Tao Hongcai 40
4.4 Transact-SQL语言
讲解内容,
1.T-SQL语言简介
2.T-SQL编程
3.T-SQL提供的函数
5.T-SQL存储过程
4.T-SQL游标
2009年 11月 10日Designed by Tao Hongcai 41
一,T-SQL语言简介
T-SQL的存储过程,由 SQL语句组成，分系统存储过程和
用户自定义存储过程。
T-SQL组成,SQL语句、函数和存储过程。
T-SQL的子语言,数据定义语言 DDL （ Data Definition
Language）,数据操纵语言 DML（ Data Manipulation Language）
和数据控制语言 DCL（ Data Control Language）。
T-SQL的函数,系统函数、聚集函数、数学函数、字符串
函数、数据类型转换函数和日期函数等。
2009年 11月 10日Designed by Tao Hongcai 42
二,T-SQL编程
表示及概念,@变量名，由用户定义幵使用。
1.注释 (Comment)方式
(1) 多行时，用 /* …, */
(2) 单行时，用 --
2.变量
局部变量定义,DECLARE @变量名 类型 [,… n]
（ 1）局部变量
说明：
(1) 变量一旦定义，系统自动赋 NULL值。
(2) 对局部变量的赋值用 SELECT语句。
2009年 11月 10日Designed by Tao Hongcai 43
表示及概念,@@变量名,由系统定义，用户可使用之。
3.SELECT用法
作用,用于指明系统运行过程中的运行状态，用户只能引
用，不能修改和定义。
（ 2）全局变量
常用的全局变量,
(1) @@error：返回最后一个语句产生的错误码。
(2) @@rowcount：返回语句执行后受影响的行数。
(3) @@version,SQL SERVER版本号。
(4) @@trancount：事务计数。
(5) @@transtate：事务状态。
(1) 查看表或视图的内容；
(2) 执行函数，返回值到客户端,SELECT db_name()
2009年 11月 10日Designed by Tao Hongcai 44
4.流程控制语句
① 直接赋值,Select @xxxx =2。一次只能一个。
② 从表中提取值赋给变量（应保证是唯一行且类型一
致）。
(3)给变量赋值
(4)查看变量值,Select @变量
(1) IF (条件关系式 ) ELSE [IF],如果关系式含有 SELECT语句，
则该 SELECT语句须用圆括号括起，其嵌套级别最多为 150；
(2) BEGIN,.,END,用于界定由多条 SQL语句组成的语句块；
示例,SELECT @tp=price FROM titles T WHERE T.id=?Bu1032?
(3) WHILE,..BREAK/CONTINUE
2009年 11月 10日Designed by Tao Hongcai 45
5.信息显示
(4) GOTO,..,跳到用户定义的标号处；
(5) RETURN,无条件退出；
(6) IF EXISTS/IF NOT EXISTS,是否有数据存在
(7) WAITFOR,延迟某段时间
示例,WAITFOR DELAY '00:30:00? /* 延迟 30分钟 */
语法,WAITFOR { DELAY 日期时间格式的时间值
| TIME 时间 | … }
(1) Print ?…,.?
(2) Print @msg
(3)用参数嵌套形式加入不同的值：
Print ?…,%1!…,.%2!…,?,@参数 1,@参数 2,·····
2009年 11月 10日Designed by Tao Hongcai 46
6.RAISERROR
用途,系统有很多系统信息及其代码（代码值在两万以
下），用户也可用 RAISERROR，自己定义错误信息及其代码
（其代码值应在两万以上）。
语法,RAISERROR 错误代码
{ ?错误信息 ’ | 局部变量 } [,参数表 ]
三,T-SQL提供的函数
1.字符串操作
(1) 连接操作, +
(2) convert函数
作用,将表达式按指定风格转换成指定的类型，其中的 style只
用于日期类型到字符串的转换。
语法,convert(类型符，表达式 [,style])
2009年 11月 10日Designed by Tao Hongcai 47
2.有关日期的几个函数
(3)日期 ===> 字符串
(1) getdate()
(2) datename()与 datepart()
datename(日期元素,日期表达式 ),以字符串形式返回日期
元素指定的日期的名字
style取值 输出格式
2 yy.mm.dd
3 dd/mm/yy
4 dd.mm.yy
5 dd-mm-yy
102 yyyy.mm.dd
103 dd/mm/yyyy
104 dd.mm.yyyy
105 dd-mm-yyyy
示例：
convert(char(10),pubdate,105)
其中,pubdate为日期型。
用途,将服务器 OS的时间送客户机。
示例,SELECT getdate()
datepart(日期元素,日期表达式 ),以数字形式返回日期元素
指定的日期的名字
2009年 11月 10日Designed by Tao Hongcai 48
日期元素及其指定返回的日期部分为：
示例：
Select datetime(mm,pubdate) from titiles where title_id=?Bu1032?
返回 June，如用 datepart则返回 6。
① yy,返回日期表达式中的年 year或年数。
② qq,返回日期表达式表示的季 quarter或季数。
③ mm,返回日期表达式中的月 month或月数。
④ dw,返回日期表达式表示的星期几 day of week。
⑤ dy,返回日期表达式表示的一年中的第几天 day of year
⑥ dd,返回日期表达式中的天或天数 day。
⑦ wk,返回日期表达式表示的一年中的第几个星期 week
或星期数。
⑧ hh,返回日期表达式中的小时 hour或小时数。
2009年 11月 10日Designed by Tao Hongcai 49
3.数学函数
(3) dateadd()与 datediff()
dateadd(日期元素，数值，日期表达式 ),将数值转换成日
期元素指定的部分加到日期表达式上返回。
datediff(日期元素，较早日期表达式，较晚日期表达式 )：
两个日期相减后，按日期元素指定部分转化后返回。
函 数 示 例 结 果
abs(数值表达式 ) abs(-100) 100
ceiling(数值表达式 ) ceiling(99.2) 100
floor(数值表达式 ) floor(99.2) 99
round(数值表达式，整数表达式 ) round(66.2387,2) 66.24
exp(浮点表达式 ) exp(0) 1
rand([整数 ]) rand(23)
log(浮点表达式 ) log(1) 0
pi() pi() 3.141?592?65...
power(数值表达式，指数表达式 ) power(2,10) 1?024
sqrt(数值表达式 ) sqrt(4) 2
sin(浮点表达式 ),cos(浮点表达式 ),tan(浮点表达式 ) sin(pi()) 0
2009年 11月 10日Designed by Tao Hongcai 50
四,T-SQL游标
Declare 游标名 CURSOR
FOR Select语句
1.游标生命周期
定义,Open Cursor,Fetch,Close cursor,Deallocate
2.定义游标
语法：
3.打开游标,打开后指针指向游标首位
语法,OPEN 游标名
用法,Fetch一次，指针下移一行。同时有二个全局变量将
受影响，即,@@sqlstate,0—成功； 1—失败； 2—No more data。
@@rowcount,每移一次累加一。
4.FETCH
语法,Fetch 游标名 Into 局部变量列表
2009年 11月 10日Designed by Tao Hongcai 51
6.Close和 Deallocate
7.用游标对数据操作
语法,Close 游标名； Deallocate Cursor 游标名
(1) Delete 表名 Where Current of 游标名
(2) Update 表名 Set 列名 =值 [,····] Where Current of 游标名
说明,初除后指针不动，下面的行自动上移。
8.游标示例
DECLARE books_csr CURSOR FOR
SELECT title_id,type,price FROM titles
(1) 游标定义
(2) 游标使用
2009年 11月 10日Designed by Tao Hongcai 52
Declare @title_id tid,@type char(12),@price money
Open books_csr
Fetch books_csr into @title_id,@type,@price
While @@sqlstatus != 2
Begin
if @@sqlstatus = 1
begin
raiserror 30001 ?select failed?
return
end
if @type= ?business?
select @title_id,@type,@price,convert(money,@price*1.08)
else if @type = ?mod_cook?
select @title_id,@type,@price
fetch books_csr into @title_id,@type,@price
End
2009年 11月 10日Designed by Tao Hongcai 53
五,T-SQL存储过程
可在命令行或批中调用。非第一条时应加 exec
1.创建存贮过程
Create Proc 过程名
As SQL语句
[Return]
2.执行
3.查看
sp_helptext 过程名
4.改名
sp_rename 旧过程名，新名
2009年 11月 10日Designed by Tao Hongcai 54
(1) 创建
5.删除
Drop Proc 过程名
6.带传递参数的存贮过程
Create Proc 过程名
( @变量 类型 [,…, ] )
As SQL 语句
Return
(2) 传递参数、执行
exec 过程名 参数值 1 [，参数值 2,······]
7.从存储过程返回值
(1) 创建
2009年 11月 10日Designed by Tao Hongcai 55
Create Proc 过程名
( @变量 类型 [,…, ]
@变量 类型 OUTPUT)
As SQL 语句
Return
(2) 调用
先定义一个局部变量，用于接收输出值。
8.对存贮过程的限制
(1)某些命令不能放在过程中
Create View/Default/Rule/Trigger/Proc等。
(2)不能在同一过程中刚初除一个 DB对象，又创建同名的
对象。
9.存贮过程示例
2009年 11月 10日Designed by Tao Hongcai 56
CREATE PROC num_sales
( @book_id char(6),/* 输入参数 */
@tot_sales int OUTPUT ) /* for output */
AS
SELECT @tot_sales=sum(qty)
FROM salesdetail
WHERE title_id = @book_id
RETURN
(1) 存储过程创建
(2) 调用
DECLARE @total int
EXEC num_sales @book_id=“pe53995”,@tot_sales=@total
OUTPUT
或 EXEC num_sales,pe53995”,@total OUTPUT
── The End ──
