SQL Server 2000 程序设计学习笔记--第七章 T-SQL程序设计

第七章 T-SQL程序设计

实例
use pubs
select * from authors
select * from sales
go

GO命令
将在GO之前的SQL语句交由SQL Server来执行
将一批SQL语句进行分段来执行，可以使用GO命令将SQL语句分段

7.1 T-SQL语法惯例

7.2 SELECT语句

范例
Select * FROM cust

使用AS关键字
范例
SELECT Cus_id AS 客户编号,Cus_Name AS 客户名称,Cus_tel AS 联络电话 FROM cust
GO

Select ... where 语句
1 between ...and
2 like 及通配符
3 运算符(如AND OR NOT等)
4 order by 子句
5 group by子句

Between ...and子句
进行范围搜索以便返回介于两个指定数值之间的所有值

范例
从sales表格中，找出 qty字段数据数值介于30至40之间的所有记录
use pubs
go select * from sales where qty between 30 and 40
go

Like及统配符
like子句和一般字符和统配符一起使用，用来 判断所指定的字符，字符串是否与特定模式是否相符。

使用统配符要比使用=与!=字符串比较运算符能让like运算符更有弹性
select * from sales where qty=30
select * from sales where qty!=30(select * from sales where qty<>30)
统配符号：% _ [] [^]

范例
找出在sales表格中符合在title_id 字段中第一个字符为T的所有记录
USE pubs
GO
SELECT * FROM sales WHERE title_id LIKE 'T%'
GO
找出第一个字符为T，而以3218为结尾的所有记录
use pubs
go
select * from sales where title_id like 'T_3218'
go

select * from sales where title_id like 'T%3218'

7.3 ORDER BY语句

范例
依客户编号的大小来显示数据
select * from cust order by cus_id
go

关键字ASC 又小到大  DESC由大到小

将数据由小到大的方式进行排序
select * from cust order by cust_id asc
将数据由大到小的方式进行排序
select * from cust order by cust_id desc

范例
将ORDER BY 语句和BETWEEN ...AND 语句整合应用
在order_mon 表格中，找出ord_qty订单数量字段值是介于100至200之间的所有数据，并且以由小至大的排列方式来显示数据
select * from order_mon where ord_qty between 100 and 200 order by ord_id asc
go

7.4 GROUP BY语句与汇总函数
范例
select ord_id,ord_quy from order ORDER BY ord_id
go
GROUP BY语句就是用来将查询结果根据所设置的条件进行分组的操作并且搭配汇总函数求出组的平均值 最大值或最小值等

修改以上范例，使用汇总函数SUM将同一字段值求总和

select ord_id as 订单编号,SUM(ord_quy) as总订单数量 from order group by ord_id
go

汇总函数
将某特定的一组数值进行计算并将结果以单一值来返回
除了COUNT函数之外，期于的汇总函数会忽略所有Null的值,一般来说汇总函数通常与SELECT语句的GROUP BY一起使用

Sum汇总函数
求和
SUM汇总函数只能针对数值类型的字段来做运算,求和过程中将字段含有NULL值的字段值忽略以及会包含重复的值。
如果不希望在执行求和运算时，连同重复的记录也进行可以加上DISTINCT关键字将重复的记录仅计算一次

范例
将表格以ord_id字段为分组的根据，然后再算出各族ord_id的总订单数量
select ord_id SUM(ord_quy) as 总订单数量 from ord_cust group by ord_id order by ord_id
go
将表格以cust_id字段为分组的根据，然后再算出各组ord_id的总订单数量，在计算总和时，会将重复数据仅计算一次
select cust_id,SUM(DISTINCT pay1) AS pay1 From pay_list GROUP BY cust_id ORDER BY cust_id
go

AVG汇总函数
求平均值
求平均值过程中，会忽略字段内含有NULL的字段值 去除重复DISTINCT

范例
以cust_id字段为分组的根据，然后再算出各组cust_id的pay1的平均值

select cust_id,AVG(pay1) as pay1 from pay_list group by cust_id order by cust_id
go

COUNT汇总函数
COUNT返回组内的总笔数。COUNT(*)会返回指定表中的行数目而不删除重复项目，他会个别的计算每个行，包括了包含Null值的行，COUNT(*)无任何参数，且不可搭配DISTINCT关键字一起使用，只有当在COUNT汇总函数内有指定表达式或字段名称时，才可以使用DISTINCT关键字

范例
select count(*) from pay_list
go

select count(DISTINCT cust_id) from pay_list
go

select count(pay1) as pay1 from pay_list group by cust_id
go

select count(distinct pay1) as pay1 from pay_list group by cust_id
go

select cust_id,count(pay1) as pay11 from pay_list group by cust_id

select cust_id,count(distinct pay1) as pay11 from pay_list group by cust_id
MIN汇总函数
返回所指定的字段或表达式结果中最小的值。MIN汇总函数会忽略任何的空值
范例
求出pay1字段的最小值
select MIN(pay1) AS pay1 FROM pay_list
go

MAX函数汇总
MAX函数汇总会返回所指定的字段或表达式结果中最大的值。MAX汇总函数会忽略任何的空值。
范例
求出pay1字段的最大值
select MAX(pay1) AS pay1 FROM pay_list
go

CUBE ROLLUP 及COMPUTE关键词
CUBE 关键词是用来将所要进行查询的各分组字段内的数据求和的运算。
COMPUTE关键字则是用来将分组做小计，也就是说将字段内进行分组并且将每一组生成一或多个总和 平均值等

范例
select cust_id,SUM(pay1) AS pay11 from pay_list GROUP BY pay1 WITH CUBE
go
以上代码导致错误因为在SUM汇总函数所指定的字段名称pay1与CUBE关键词一并使用所导致发生的错误消息
修改
SELECT cust_id,SUM(pay1) as pay11 FROM pay_list GROUP BY cust_id WITH CUBE
GO

ROLLUP关键词
将GROUP BY后面所加的第一个字段名称来求和而其他的字段并不做
ROLLUP以及CUBE关键词二者之间不同的地方就是ROLLUP只会将GROUP BY后面的第一个字段名称求总和而CUBE则会将每一个字段都求总和的操作

范例
SELECT 厂商编号,产品名称,SUM(数量) AS 总数量 FROM proced GROUP BY 厂商编号，产品名称 WITH ROLLUP
GO

COMPUTE关键词
用来将分组小计也就是在字段内进行分组,并且将每一组生成一或多个总和 平均值等
范例
select 厂商编号,产品名称,数量 from proced compute sum(数量)
go

以厂商编号作为分类根据时
select 厂商编号,产品名称,数量 from proced order by 厂商编号 COMPUTE SUM(数量) BY 厂商编号
go

7.5 HAVING语句

HAVING语句是用来指定组或汇总的搜索条件的。HAVING语句一般只能与 SELECT语句一起使用，并且他通常是会在GROUP BY子句中使用
。

范例
计算出其进货的总数量大于200的厂商编号及产品名称
SELECT 厂商编号,产品名称,SUM(数量) AS 数量 FROM proced GROUP BY 厂商编号,产品名称 HAVING SUM(数量)>100
GO

7.6 DISTINCT及ALL关键词

范例
SELECT DISTINCT FROM proced
go

7.7 ESCAPE关键词
如果想将统配符百分号符号视为字符而非统配符时，可以使用ESCAPE关键词再加上百分号符号即可。

范例
在customs表格中，找出符合折扣为50%的所有记录
use misdb
go
select * from customs where discl like '50%' escape '%'
go

7.8 批注
方式一：以/*开始以*/结束
方式二：-- 批注一行
范例：
编写批注的正确用法使用"/*"和"*/"
/*编写批注的用法*/
/*USE Pubs
GO*/
select * from authors
go

7.9 区域变量与全局变量

SQL SERVER中，变量可分为两种一是区域变量另一种是全局变量
在SQL SERVER中，区域变量一般都是由用户自己定义的，其表示方式是以"@"来作为变量名称的开头。而全局变量则是由系统提供的，并且其表示方式是以"@@"来作为变量名称的。
区域变量的生命周期是使用DECLARE语句开始，直到此批处理执行结束时就结束。

DECLARE @TEST int --区域变量@TEST生命周期开始
@TEST=5
SELECT * FROM authors WHERE au_id=@TEST
GO                --批处理结束执行，@TEST变量的生命周期结束
SELECT * FROM authors
GO

区域变量
要使用区域变量,首先要先将区域变量进行声明，在声明区域变量之后系统会将区域变量值初始设置为NULL值，然后再使用SET或SELECT语句来进行设置变量值。将变量进行声明时，你必须要使用DECLARE语句来进行声明。
DECLARE @TEST int   --声明变量TEST及其数据类型
SET @TEST=5         --设置变量TEST的值为5

声明多个变量名称之间使用逗号来分割。
DECLARE @A1 char(10),@a2 int,@a3 char(10)

使用SELECT语句设置变量时有以下两种方法
一 直接设置变量值
DECLARE @TEST int
SELECT @TEST=5
二 将查询结果指定给变量，但此种方式必须要求其查询结果是单一值
DECLARE @TEST int
SELECT @TEST=数量 From customer WHERE cust_id='A001'

使用SELECT语句来指定变量值时，若你有多个变量要指定多个变量值时，每个变量之间可以用逗号来分割开来。

7.10 流程控制

BETIN...END
用来将一串T-SQL语句使用BEGIN...END的关键词括在一起,以便让被括在一起之内的T-SQL语句可以被执行。

如果所要执行的T-SQL语句只有一行，就不用使用BEGIN...END语句来执行，因为只有一行T-SQL语句就不需要被括在一起来执行。

语法
BEGIN{
sql_statement | statement_block
}
END

BEGIN...END语句组可以是镶套的。

范例
--BEGIN...END语句的使用范例
use pubs
go
begin
select * from authors where au_id like ' 2* '
end

begin
select cust_id,cust_name from custom order by cust_id
end

IF...ELSE语句

语法
IF Boolean_expression
{sql_statement|statement_block}
else
{sql_statement|statement_block}
如果要执行的T-SQL语句不只一行时，就必须要使用BEGIN...END语句组

范例
--IF...ELSE语句的使用范例
if (select avg(总成绩) from 学生成绩<60)
print '你的总成绩不及格'
else
print '你的总成绩几个，恭喜你!'

--镶套IF...ELSE语句的使用范例
if (select sum(订单数量) from 订单表格)>1000
print '他们是最佳客户'
else
if(select sum(订单数量) from 订单表格)>500
print '必须与他们保持联络'
else
print '再想想办法吧!!'

WHILE循环 BREAK及CONTINUE语句

语法声明
while Boolean_expression
{sql_statement|statement_block}
[BREAK]--退出最内层的WHILE循环
{sql_statement|statement_block}
[CONTINUE]--重新执行WHILE循环忽略掉在CONTINUE之后的任何T-SQL语句
如果有两个或两个以上的WHILE循环组成镶套WHILE循环时，内层的BREAK语句会退出到下一个外层循环。首先在较内层循环结束处之后的T-SQL语句会被执行，然后开始下一个外层的循环。

范例
--使用WHILE CONTINUE BREAK语句的使用范例
while (select avg(单价) from 订单表格)<60
begin
set 单价=单价*4
if(select max(单价) from 订单表格)>80
break
else
continue
end

GOTO 语句
GOTO语句可以用来改变执行流程至某个标签名称

语法声明
定义标签名称时
label :
要改变执行时
goto label

标签名称摆在GOTO语句之前或之后并没有特别限制。
注意GOTO 语句只能在WHILE或IF...ELSE语句内往外跳至标签名称处而不能由外面语句跳进IF...ELSE或WHILE语句内。

范例
--GOTO语句的使用范例
if (select max(单价) from 订单表格)>100
goto max_label
else
goto min_label
max_label:
print '太贵了'
min_label:
print '价格合理'

RETURN 语句
将目前正在执行的批处理T-SQL语句，退出程序或语句中断,并返回或不返回数值。
当RETURN语句与存储过程一起使用时，其RETURN并不会返回一个NULL值。但当某程序尝试返回NULL值时，则会生成警告消息，接着会返回一个为0的数值。

语法声明
return [integer_expression]
integer_expression指要返回的整数值

范例
--return的使用范例
create procedurecheck_maxdata
as
if(select state from authors where au_id='123-34-45')
return 1
else
return 2

WAITFOR语句
WAITFOR语句是用来指定触发语句组，存储过程或事物执行的时间，时间间隔或事件。执行WAITFOR语句之后，必须等到指定的时间或指定的事件发生后，才能使用到SQL Server 的连接。
如果 想知道目前谁在使用或等待时，可以使用系统存储过程sp_who来了解。

声明语法
WAITFOR{DELAY 'time'|TIME 'time'}
参数说明
DELAY用来设置要SQL Server必须要等待所到指定的时数已过时，才可以使用SQL Server的连接
time 用来指定要等待的时间
只能指定时间而不能设置日期。其等待时间的设置最多可指定到24小时。

TIME用来指示SQL Server等待到所指定的时间。

范例
--此范例会在下午6：00时会执行select_data 存储过程
begin
waitfor TIME '18:00'
EXEC select_data
end

begin
waitfor TIME '15:20'
print 'ninhao'
end

范例
--WAITFOR DELAY的使用范例
use pubs
go
--设置要sql server 等待一分钟再执行下列语句
waitfor delay '00:01:00'
select * from authors
go

7.11 CASE,RAISERROR语句

CASE
根据条件列表之后来执行表达式，其CASE可分为两种格式：
1 一种格式是属于简单的CASE格式，这种CASE会比较表达式与一组简单表达式，以便决定结果。
2 另一种格式是属于搜索的CASE格式，这种格式会评估一组布尔表达式,以便决定其结果。

简单CASE声明
CASE input_expression
WHEN when_expression THEN result_expression
[..n]
[
ELSE else_result_expression
END

如果想使用搜索CASE格式时，可以根据下面的语法来进行声明
CASE
WHEN Boolean_expression THEN result_expression
[..n]
[
ELSE else_result_expression
END

CASE如果没有ELSE且没有任何比较运算其结果为真时CASE语句会返回一个NULL值

范例
--此范例是介绍如果使用简单的CASE格式
use
pubs
go
select 产品类别=
case type1
when '水果' then sum1=sum1+1
when '五金' then sum2=sum2+1
when '药品' then sum3=sum3+1
else sum0=sum0+1
end
go

范例
--此范例会介绍如果使用搜索CASE格式

USE MIS
GO
SELECT 语文成绩=
CASE
WHEN scorel<59 THEN '不及格'
WHEN scorel>60 and scorel<=70 THEN '中上成绩'
WHEN scorel>=91 and scorel<+100 THEN '成绩优良'
END
GO

RAISERROR 语句
RAISERROR语句会返回用户所自定义的错误消息，并且设置系统旗标，记录曾发生过的错误。
使用RAISERROR指定用户自定义的错误消息时，请使用大于50，000的错误消息代码和0至18的重要性层级。

使用系统存储过程sp_addmessage来创建自定义的错误消息

RAISERROR语句其语法声明

使用RAISERROR语句来创建并返回用户自定义的错误信息时，请使用sp_addmessage来添加用户自定义的错误消息，并使用sp_dropmessage来删除用户自定义的错误消息。
发生错误时，错误代码会放置在@@ERROR函数中，此函数存放着最近生成的错误代码。

范例
--使用RAISERROR的范例
--使用sp_addmessage来创建自定义错误消息，其中76548是指错误代码，9是代表严重程度，而"错误消息测试"则是代表消息内容
EXEC sp_addmessage 76548,9,'错误消息测试'
GO
RAISERROR(76548,9,1)

7.12 EXEC 语句

用来执行存储过程或T-SQL语句的批处理程序等语句。

范例
--执行系统存储过程sp_who的方式
EXEC sp_who
go

范例
--执行程序test_pro,并传递一参数

EXEC test_pro case_id  --其中test_pro为程序名称，而case_id 为参数
GO

范例
--使用多个参数以及一个输出参数
--其中a01,b01指的是要给test_pro程序使用的参数
--而@a1则是指要输出的参数名称，我们使用@test来接受@a1所返回的值
EXEC test_pro 'a01','b01',@a1=@test OUTPUT

范例
--执行远程程序test1,并传递一个参数
--其中Mis_Server.mis.dbo.order1指的是要执行在远程服务器mis_server,在mis@t1指的是接受自执行后的order1程序状态值
EXEC @t1=Mis_Server.mis.dbo.order1 '981-098'
GO

范例
--以字符串方式
EXEC('THIS'+'A098.18364.47')
GO