SQL Server 数据库的高级操作
SQL Server 数据库的高级操作
(1) 批处理
(2) 变量
(3) 逻辑控制
(4) 函数
(5) 高级查询
*/
(1)批处理
将多条SQL语句作为一个整体去编译，生成一个执行计划，然后，执行！
理解批处理的关键在于"编译"，对于由多条语句组成的一个批处理，
如果在编译时，其中，有一条出现语法错误，将会导致编译失败！
create table t
(
a int,
b int
)
-- 注释
-- 如果多行注释中包含了批处理的标识符go
-- 在编译的过程中代码将会被go分割成多个部分来分批编译
-- 多行注释的标记将会被分隔而导致编译出错
-- 以下几条语句是三个非常经典的批处理
-- 你猜一下会添加几条记录！
/*
insert into t values (1,1)
go
*/
insert into t values (2,2)
go
/*
insert into t values (3,3)
*/
go
-- 查询看添加了几条记录
select * from t
truncate table t
(2)变量
-- 全局变量
SQL Server中全局变量由系统定义、系统维护，用户一般仅可对其进行读取！
-- 查看SQL Server版本
print @@version
-- 服务器名称
print @@servername
-- 系统错误编号
insert into t values ('a','a')
print @@error
insert into t values ('a','a')
if @@error = 245
print 'Error'
-- SQL Server 版本的语言信息
print @@LANGUAGE
-- 一周的第一天从星期几算起
print @@datefirst
-- CPU 执行命令所耗费时间的累加
print @@cpu_busy
-- 获取最近添加的标识列的值
create table tt
(
a int identity(3, 10),
b int
)
insert into tt (b) values (1)
print @@identity
select * from tt
-- 局部变量
局部变量由用户定义，仅可在同一个批处理中调用和访问
declare @intAge tinyint
set @intAge = 12
print @intAge
declare @strName varchar(12)
select @strName = 'state'
print @strName
select au_lname, @strName from authors
(3)逻辑控制
-- IF条件判断
declare @i int
set @i = 12
if (@i > 10)
begin -- {
print 'Dadadada!'
print 'Dadadada!'
end -- }
else
begin
print 'XiaoXiao!'
print 'XiaoXiao!'
end
-- While循环控制
declare @i int;
set @i = 12;
print @i
return;
while (@i < 18)
begin
print @i;
set @i = @i + 1;
if @i < 17
continue;
if @i > 15
break;
end;
-- CASE 分支判断
select au_lname, state, '犹他州' from authors where state = 'UT'
select au_lname, state, '密西西比州' from authors where state = 'MI'
select au_lname, state, '肯塔基州' from authors where state = 'KS'
select au_lname, state,
case state
when 'UT' then '犹他州'
when 'MI' then '密西西比州'
when 'KS' then '肯塔基州'
when 'CA' then '加利福利亚'
else state
end
from authors
(4.1)系统函数
-- 获取指定字符串中左起第一个字符的ASC码
print ascii('ABCDEF')
-- 根据给定的ASC码获取相应的字符
print char(65)
-- 获取给定字符串的长度
print len('abcdef')
-- 大小写转换
print lower('ABCDEF')
print upper('abcdef')
-- 去空格
print ltrim(' abcd dfd df ')
print rtrim(' abcd dfd df ')
-- 求绝对值
print abs(-12)
-- 幂
-- 3 的 2 次方
print power(3,2)
print power(3,3)
-- 随机数
-- 0 - 1000 之间的随机数
print rand() * 1000
-- 获取圆周率
print pi()
-- 获取系统时间
print getdate()
-- 获取3天前的时间
print dateadd(day, -3 , getdate())
-- 获取3天后的时间
print dateadd(day, 3 , getdate())
-- 获取3年前的时间
print dateadd(year, -3 , getdate())
-- 获取3年后的时间
print dateadd(year, 3 , getdate())
-- 获取3月后的时间
print dateadd(month, 3 , getdate())
-- 获取9小时后的时间
print dateadd(hour, 9 , getdate())
-- 获取9分钟后的时间
print dateadd(minute, 9 , getdate())
-- 获取指定时间之间相隔多少年
print datediff(year, '2005-01-01', '2008-01-01')
-- 获取指定时间之间相隔多少月
print datediff(month, '2005-01-01', '2008-01-01')
-- 获取指定时间之间相隔多少天
print datediff(day, '2005-01-01', '2008-01-01')
-- 字符串合并
print 'abc' + 'def'
print 'abcder'
print 'abc' + '456'
print 'abc' + 456
-- 类型转换
print 'abc' + convert(varchar(10), 456)
select title_id, type, price from titles
-- 字符串连接必须保证类型一致(以下语句执行将会出错)
-- 类型转换
select title_id + type + price from titles
-- 正确
select title_id + type + convert(varchar(10), price) from titles
print '123' + convert(varchar(3), 123)
print '123' + '123'
print convert(varchar(12), '2005-09-01',110)
-- 获取指定时间的特定部分
print year(getdate())
print month(getdate())
print day(getdate())
-- 获取指定时间的特定部分
print datepart(year, getdate())
print datepart(month, getdate())
print datepart(day, getdate())
print datepart(hh, getdate())
print datepart(mi, getdate())
print datepart(ss, getdate())
print datepart(ms, getdate())
-- 获取指定时间的间隔部分
-- 返回跨两个指定日期的日期和时间边界数
print datediff(year, '2001-01-01', '2008-08-08')
print datediff(month, '2001-01-01', '2008-08-08')
print datediff(day, '2001-01-01', '2008-08-08')
print datediff(hour, '2001-01-01', '2008-08-08')
print datediff(mi, '2001-01-01', '2008-08-08')
print datediff(ss, '2001-01-01', '2008-08-08')
-- 在向指定日期加上一段时间的基础上，返回新的 datetime 值
print dateadd(year, 5, getdate())
print dateadd(month, 5, getdate())
print dateadd(day, 5, getdate())
print dateadd(hour, 5, getdate())
print dateadd(mi, 5, getdate())
print dateadd(ss, 5, getdate())
-- 其他
print host_id()
print host_name()
print db_id('pubs')
print db_name(5)
-- 利用系统函数作为默认值约束
drop table ttt
create table ttt
(
stu_name varchar(12),
stu_birthday datetime default (getdate())
)
alter table ttt
add constraint df_ttt_stu_birthday default (getdate()) for stu_birthday
insert into ttt values ('ANiu', '2005-04-01')
insert into ttt values ('ANiu', getdate())
insert into ttt values ('AZhu', default)
sp_help ttt
select * from ttt
（4.2）自定义函数
select title_id
from titles
where type = 'business'
select stuff(title_id,1,3,'ABB'), type
from titles
where type = 'business'
select count(title_id) from titles where type = 'business'
select title_id from titles where type = 'business'
select *,count(dbo.titleauthor.title_id)
FROM dbo.authors INNER JOIN
dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id
select au_id, count(title_id)
from titleauthor
group by au_id
SELECT dbo.authors.au_id, COUNT(dbo.titleauthor.title_id) AS '作品数量'
FROM dbo.authors left outer JOIN
dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id
GROUP BY dbo.authors.au_id
order by '作品数量'
-- 自定义函数的引子（通过这个子查询来引入函数的作用）
-- 子查询
-- 统计每个作者的作品数
-- 将父查询中的作者编号传入子查询
-- 作为查询条件利用聚合函数count统计其作品数量
select au_lname,
(select count(title_id)
from titleauthor as ta
where ta.au_id = a.au_id
) as TitleCount
from authors as a
order by TitleCount
-- 是否可以定义一个函数
-- 将作者编号作为参数统计其作品数量并将其返回
select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount
from authors
order by TitleCount
-- 根据给定的作者编号获取其相应的作品数量
create function GetTitleCountByAuID(@au_id varchar(12))
returns int
begin
return (select count(title_id)
from titleauthor
where au_id = @au_id)
end
-- 利用函数来显示每个作者的作品数量
create proc pro_CalTitleCount
as
select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount
from authors
order by TitleCount
go
-- 执行存储过程
execute pro_CalTitleCount
-- vb中函数定义格式
function GetTitleCountByAuID(au_id as string) as integer
.
GetTitleCountByAuID = ?
end function
-- SALES 作品销售信息
select * from sales
-- 根据书籍编号查询其销售记录（其中，qty 表示销量）
select * from sales where title_id = 'BU1032'
-- 根据书籍编号统计其总销售量（其中，qty 表示销量）
select sum(qty) from sales where title_id = 'BU1032'
-- 利用分组语句（group by），根据书籍编号统计每本书总销售量（其中，qty 表示销量）
select title_id, sum(qty) from sales group by title_id
-- 是否可以考虑定义一个函数根据书籍编号来计算其总销售量
-- 然后，将其应用到任何一条包含了书籍编号的查询语句中
select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales
from titles
order by TotalSales
-- 定义一个函数根据书籍编号来计算其总销售量
create function GetTotalSaleByTitleID(@tid varchar(24))
returns int
begin
return(select sum(qty) from sales where title_id = @tid)
end
-- 统计书籍销量的前10位
-- 其中，可以利用函数计算结果的别名作为排序子句的参照列
select top 10 title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales
from titles
order by TotalSales desc
-- 根据书籍编号计算其销量排名
create function GetTheRankOfTitle(@id varchar(20))
returns int
begin
return(select count(TotalSales)
from titles
where ToalSales >(
select TotalSales
from titles
where title_id=@id))
end
-- 根据书籍编号计算其销量排名
select dbo.GetTheRankOfTitle('pc1035') from titles
select count(title_id) + 1
from titles
where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID('pc1035')
-- 删除函数
drop function GetRankByTitleId
-- 根据书籍编号计算其销量排名
create function GetRankByTitleId(@tid varchar(24))
returns int
begin
return (select count(title_id) + 1
from titles
where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID(@tid))
end
-- 在查询语句中利用函数统计每本书的总销量和总排名
select title_id, title,
dbo.GetTotalSaleByTitleID(title_id) as TotalSales,
dbo.GetRankByTitleId(title_id) as TotalRank
from titles
order by TotalSales desc
-- 查看表结构
sp_help titles
-- 查看存储过程的定义内容
sp_helptext GetRankByTitleId
sp_helptext sp_helptext
sp_helptext xp_cmdshell
-- [ORDER DETAILS] 订单详细信息
select * from [order details]
select * from [order details] where productid = 23
-- 根据产品编号在订单详细信息表中统计总销售量
select sum(quantity) from [order details] where productid = 23
-- 构造一个函数根据产品编号在订单详细信息表中统计总销售量
create function GetTotalSaleByPID(@Pid varchar(12))
returns int
begin
return(select sum(quantity) from [order details] where productid = @Pid)
end
select * from products
-- 在产品表中查询，统计每一样产品的总销量
select productid, productname, dbo.GetTotalSaleByPID(productid) from products
--
CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
END
SELECT * FROM LargeOrderShippers( $500 )
-- 根据作者编号计算其所得版权费
create function fun_RoyalTyper ( @au_id id)
returns int
as
begin
declare @rt int
select @rt = sum(royaltyper) from titleauthor where au_id = @au_id
return (@rt)
end
go
select top 1 au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as '版权费'
from authors
order by dbo.fun_RoyalTyper(au_id) desc
go
create function fun_MaxRoyalTyper_Au_id ()
returns id
as
begin
declare @au_id id
select @au_id = au_id
from authors
order by dbo.fun_RoyalTyper(au_id)
return(@au_id)
end
go
select dbo.fun_MaxRoyalTyper_Au_id()
go
select au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as '版权税'
from authors
where au_id = dbo.fun_MaxRoyalTyper_Au_id()
go
(5)高级查询
select title_id, price from titles
-- 查找最高价格
select max(price) from titles
-- 查找最贵书籍的价格(排序)，如果存在多本价格最贵的书，此方法将会遗漏
select top 1 title_id, price
from titles
order by price desc
-- 查找最贵书籍的价格(子查询)
select title_id, price
from titles
where price = (select max(price) from titles)
-- 查询指定出版社出版的书(连接)
select p.pub_name as '出版社', t.title as '书籍名称'
from publishers as p join titles as t on p.pub_id = t.pub_id
where pub_name = 'New Moon Books'
-- 查询指定出版社出版的书(子查询)
select title
from titles
where pub_id = (select pub_id
from publishers
where pub_name = 'New Moon Books')
-- 查询指定出版社出版的书(分开查询)
select title from titles where pub_id = '0736'
select pub_id
from publishers
where pub_name = 'New Moon Books'
-- 重点
-- 理解相关子查询的基础
--
select * from titles where type = 'business'
select * from titles where type = 'business123'
select * from titles where 1 = 1
-- 在订单表中寻找满足以下条件的订单编号以及相应的客户编号
-- 在详细订单表中存在对应的订单编号并且其中包含产品编号为23的产品
-- 然后将产品编号为23的产品订购量返回判断是否大于20
USE northwind
SELECT orderid, customerid
FROM orders AS or1
WHERE 20 < (SELECT quantity FROM [order details] AS od
WHERE or1.orderid = od.orderid
AND od.productid = 23)
GO
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
(
SELECT royaltyper FROM titleauthor
WHERE titleauthor.au_ID = authors.au_id
)
select authors.au_lname,authors.au_fname
from authors join titleauthor on titleauthor.au_ID=authors.au_id
where titleauthor.royaltyper =100
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE type = 'popular_comp'))
select distinct t.type, a.au_lname, a.au_fname
from authors as a join titleauthor as ta on a.au_id = ta.au_id
join titles as t on ta.title_id = t.title_id
where t.type = 'business'
-- 查找类型为'business'或是'trad_cook'类型的书籍
select * from titles where type = 'business'
select * from titles where type = 'trad_cook'
-- 查找类型为'business'或是'trad_cook'类型的书籍(Or)
select * from titles
where type = 'business' or type = 'trad_cook'
-- 查找类型为'business'或是'trad_cook'类型的书籍(In)
select * from titles
where type in ('business', 'trad_cook')
-- 查找来自'KS'或是'UT'的作者
select au_lname, state from authors
where state = 'KS'
select au_lname, state from authors
where state = 'UT'
-- 查找来自'KS'或是'UT'的作者(Or)
select au_lname, state from authors
where state = 'UT' or state = 'KS'
-- 查找来自'KS'或是'UT'的作者(In)
select au_lname, state from authors
where state in ('UT', 'KS')
select au_lname, state from authors
where state not in ('UT', 'KS')
-- 查找出版了类型为'business'类型的书籍的出版社
SELECT pub_id FROM titles WHERE type = 'business'
SELECT pub_id,pub_name
FROM publishers
WHERE pub_id IN ('1389', '0736')
-- 查找出版了类型为'business'类型的书籍的出版社(In和子查询)
SELECT pub_id,pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')
SELECT title, advance
FROM titles
WHERE advance >
(
SELECT MAX(advance)
FROM publishers INNER JOIN titles ON
titles.pub_id = publishers.pub_id
WHERE pub_name = 'Algodata Infosystems'
)
SELECT title, advance
FROM titles
WHERE advance > all
(
SELECT advance
FROM publishers INNER JOIN titles ON
titles.pub_id = publishers.pub_id
WHERE pub_name = 'Algodata Infosystems'
and advance is not null
)
declare @i int
set @i = 12
if @i < null
print 'DDDDD'
else
print 'XXXXX'
SELECT advance
FROM publishers INNER JOIN titles ON
titles.pub_id = publishers.pub_id
WHERE pub_name = 'Algodata Infosystems'
select title_id, price from titles
where price > all
(
select price from titles where type = 'business'
)
select title_id, price from titles
where price >
(
select max(price) from titles where type = 'business'
)
select title_id, price from titles
where price > any
(
select price from titles where type = 'business'
)
select title_id, price from titles
where price >
(
select min(price) from titles where type = 'business'
)
select price from titles where type = 'business'
if exists(select * from titles where type = '123')
print 'ZZZZZ'
else
print 'BBBBB'
if exists(select * from authors
where city = 'Berkeley' and state ='UT')
print 'Welcome'
else
print 'Bye-Bye'
-- 筛选出'business'以及'trad_cook'类型的书籍(联合查询)
select title_id, type from titles where type = 'business'
union
select title_id, type from titles where type = 'trad_cook'
-- 统计'business'类型的书籍的总价(联合查询)
select title, price from titles where type = 'business'
union
select '合计:', sum(price) from titles where type = 'business'
-- 统计所有书籍的类型剔除重复(Distinct)
select distinct type from titles
-- 作者记录的复制(Select Into)
select * into au from authors
select * from au
-- 查看数据表结构(Select Into并没有对数据表的约束进行复制)
sp_help authors
sp_help au
-- 分页(子查询的经典应用之一)
-- Jobs 职务信息表（pubs 数据库）
-- 在实际项目中，显示职务信息时，而职务信息量非常庞大，可能需要将其分为若干个页面来显示
-- 比如：每页显示4条记录，那么，第一页将显示1，2，3，4，第二页将显示5，6，7，8。。。。。
-- 显示所有信息
SELECT * FROM jobs
-- 显示前 4 信息
select top 4 * from jobs
-- 显示前 8 信息
select top 8 * from jobs
-- 显示前 12 信息
select top 12 * from jobs
-- 寻找规律，每一页的信息源于前（页面大小 * 页码）条信息的反序结果的前 页面大小 条记录
-- 比如：第二页就是前 8 条记录的反序结果的前 4 条
select top 4 *
from (select top 8 * from jobs) as tt
order by job_id desc
-- 当然，对于期望按升序显示查询结果的要求可以对查询结果进行再次排序
select * from
(select top 4 *
from (select top 8 * from jobs) as tt
order by job_id desc) as stt
order by job_id
-- SQL 命令中不支持在 select 的查询列表中直接使用局部变量
-- 比如：select top @PageSize * from jobs
-- 那么，可以考虑对sql命令进行拼装，然后，利用系统存储过程 sp_executesql 来执行
exec sp_executesql N'Select * from jobs'
-- 存储过程的实现
-- 其中，@CurrentPageSize用于确定最后一页的页面大小
create proc proGetJobsByPage
@CurrentPageSize int,
@PageSize int,
@CurrentPage int
as
Declare @strSql nvarchar(400)
set @strSql = 'select * from
(select top ' + convert(nvarchar(4), @CurrentPageSize) + ' *
from (select top ' + convert(nvarchar(4),(@PageSize * @CurrentPage)) + ' * from jobs) as tt
order by job_id desc) as stt
order by job_id'
exec sp_executesql @strSql
go
-- 测试
exec proGetJobsByPage 2, 4, 4
(6)存储过程
-- 扩展存储过程
-- 查询系统目录下文件信息
xp_cmdshell 'dir *.*'
-- 启动Windows系统服务
xp_cmdshell 'net start iisadmin'
(7)游标
-- 游标的五个基本操作步骤：
-- 声明
declare cur_titles cursor
for select title, price from titles
-- 打开
open cur_titles
-- 提取
fetch cur_titles
fetch next from cur_titles
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
-- 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书
-- 这一段为批处理版
-- 与批处理版相比，存储过程版更方便调试以及代码的重用
-- 声明
declare cur_titles cursor
for select title, price from titles
-- 打开
open cur_titles
declare @title varchar(80)
declare @price numeric(9,4)
declare @title_temp varchar(80)
declare @price_temp numeric(9,4)
-- 提取
fetch cur_titles into @title, @price
fetch cur_titles into @title_temp, @price_temp
while @@fetch_status = 0
begin
if @price < @price_temp
begin
set @price = @price_temp
set @title = @title_temp
end
fetch cur_titles into @title_temp, @price_temp
end
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
-- 显示处理结果
print '最贵的书是: ' + @title + ' ' + '价格是: ' + convert(varchar(12),@price)
go
-- 定义一个存储过程
-- 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书（游标具体应用的经典）
-- 这段存储过程的实现代码相对下面的实现方式略有不同
-- 代码重复，但是思路更清晰
create procedure pro_GetMaxTitle
as
-- 声明
declare cur_titles cursor
for select title, price from titles
-- 打开
open cur_titles
-- 存储最贵的书籍信息
declare @title varchar(80)
declare @price numeric(9,4)
-- 存储从游标中提取出来的书籍的信息
declare @title_temp varchar(80)
declare @price_temp numeric(9,4)
-- 提取
fetch cur_titles into @title, @price
-- 判断是否存在书籍信息
if @@fetch_status <> 0
begin
print '没有书籍信息!'
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
-- 结束存储过程
return
end
fetch cur_titles into @title_temp, @price_temp
-- 判断是否只存在一本书
if @@fetch_status <> 0
begin
-- 显示处理结果
print '最贵的书是: ' + @title + ' ' + '价格是: ' + convert(varchar(12),@price)
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
-- 结束存储过程
return
end
while @@fetch_status = 0
begin
if @price < @price_temp
begin
set @price = @price_temp
set @title = @title_temp
end
fetch cur_titles into @title_temp, @price_temp
end
-- 显示处理结果
print '最贵的书是: ' + @title + ' ' + '价格是: ' + convert(varchar(12),@price)
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
go
-- 定义一个存储过程
-- 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书
-- 相对上面的实现方式，以下实现方式更简洁
create procedure pro_GetMaxTitle
as
-- 声明
declare cur_titles cursor
for select title, price from titles
-- 打开
open cur_titles
-- 存储最贵的书籍信息
declare @title varchar(80)
declare @price numeric(9,4)
-- 存储从游标中提取出来的书籍的信息
declare @title_temp varchar(80)
declare @price_temp numeric(9,4)
-- 提取
fetch cur_titles into @title, @price
-- 判断是否存在书籍信息
if @@fetch_status = 0
begin
print '没有书籍信息!'
goto errNoTitles
end
fetch cur_titles into @title_temp, @price_temp
-- 判断是否只存在一本书
if @@fetch_status = 0
begin
goto errOnlyOne
end
while @@fetch_status = 0
begin
if @price < @price_temp
begin
set @price = @price_temp
set @title = @title_temp
end
fetch cur_titles into @title_temp, @price_temp
end
errOnlyOne:
-- 显示处理结果
print '最贵的书是: ' + @title + ' ' + '价格是: ' + convert(varchar(12),@price)
errNoTitles:
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
go
-- 根据作者编号查看其相应的作品年销售量
-- 低于5000,提示: 销售量太低
-- 高于5000,提示: 销售量太高
create procedure pro_sales_avg (@au_id id)
as
if exists(select au_id from authors where au_id = @au_id)
begin
declare TempSales cursor
for
select title, ytd_sales
from titleauthor ta join titles t
on ta.title_id = t.title_id
where au_id = @au_id
open TempSales
declare @t varchar(80)
declare @y int
fetch TempSales
into @t, @y
while @@fetch_status = 0
begin
if 5000 > @y
print @t + ' ' + convert(varchar(5),@y) + ' 销售量太低'
else
print @t + ' ' + convert(varchar(5),@y) + ' 销售量太高'
fetch TempSales
into @t, @y
end
close TempSales
deallocate TempSales
end
else
print '作者编号无效！'
go
exec pro_sales_avg '213-46-8915'
/*
示例
A. 使用简单游标和语法
打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项，FETCH NEXT 是唯一可用的提取选项。
*/
DECLARE authors_cursor CURSOR
FOR
SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
/*
B. 使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。
*/
SET NOCOUNT ON
DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80), @title varchar(80)
PRINT "-------- Utah Authors report --------"
DECLARE authors_cursor CURSOR
FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname
PRINT @message
-- Declare an inner cursor based
-- on au_id from the outer cursor.
DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title
IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>"
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END
CLOSE titles_cursor
DEALLOCATE titles_cursor
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO
-------- Utah Authors report --------
----- Books by Author: Anne Ringer
The Gourmet Microwave
Is Anger the Enemy?
----- Books by Author: Albert Ringer
Is Anger the Enemy?
Life Without Fear
(8)触发器
-- 设定数据库的递归触发器
alter database pubs
set recursive_triggers on
go
-- 创建数据表，并设定主键、外键以及缺省约束
create table emp_mgr
(
Emp char(30) primary key,
Mgr char(30) null foreign key references emp_mgr(Emp),
NoOfReports int default 0
)
go
-- 创建插入触发器
create trigger emp_marins
on emp_mgr
for insert
as
declare @e char(30),@m char(30)
declare cur_mgr cursor for
select emp_mgr.emp
from emp_mgr,inserted
where emp_mgr.emp = inserted.mgr
open cur_mgr
fetch next from cur_mgr into @e
while @@fetch_status = 0
begin
update emp_mgr
set emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1
where emp_mgr.emp = @e
fetch next from cur_mgr into @e
end
close cur_mgr
deallocate cur_mgr
go
-- 查看数据表相关触发器
sp_helptrigger emp_mgr
go
create trigger emp_mgrupd
on emp_mgr
for update
as
if update (mgr)
begin
update emp_mgr
set emp_mgr.NoOfReports = emp_mgr.NoofReports + 1
from inserted
where emp_mgr.emp = inserted.mgr
update emp_mgr
set emp_mgr.NoOfReports = emp_mgr.NoOfReports -1
from deleted
where emp_mgr.emp = deleted.mgr
end
go
insert emp_mgr(emp,mgr) values ('Harry',null)
insert emp_mgr(emp,mgr) values ('Alice','Harry')
insert emp_mgr(emp,mgr) values ('Paul','Alice')
insert emp_mgr(emp,mgr) values ('Joe','Alice')
insert emp_mgr(emp,mgr) values ('Dave','Joe')
go
select * from emp_mgr
go
update emp_mgr
set mgr = 'Harry'
where emp = 'Dave'
go
select * from emp_mgr
go
(1) 批处理
(2) 变量
(3) 逻辑控制
(4) 函数
(5) 高级查询
*/
(1)批处理
将多条SQL语句作为一个整体去编译，生成一个执行计划，然后，执行！
理解批处理的关键在于"编译"，对于由多条语句组成的一个批处理，
如果在编译时，其中，有一条出现语法错误，将会导致编译失败！
create table t
(
a int,
b int
)
-- 注释
-- 如果多行注释中包含了批处理的标识符go
-- 在编译的过程中代码将会被go分割成多个部分来分批编译
-- 多行注释的标记将会被分隔而导致编译出错
-- 以下几条语句是三个非常经典的批处理
-- 你猜一下会添加几条记录！
/*
insert into t values (1,1)
go
*/
insert into t values (2,2)
go
/*
insert into t values (3,3)
*/
go
-- 查询看添加了几条记录
select * from t
truncate table t
(2)变量
-- 全局变量
SQL Server中全局变量由系统定义、系统维护，用户一般仅可对其进行读取！
-- 查看SQL Server版本
print @@version
-- 服务器名称
print @@servername
-- 系统错误编号
insert into t values ('a','a')
print @@error
insert into t values ('a','a')
if @@error = 245
print 'Error'
-- SQL Server 版本的语言信息
print @@LANGUAGE
-- 一周的第一天从星期几算起
print @@datefirst
-- CPU 执行命令所耗费时间的累加
print @@cpu_busy
-- 获取最近添加的标识列的值
create table tt
(
a int identity(3, 10),
b int
)
insert into tt (b) values (1)
print @@identity
select * from tt
-- 局部变量
局部变量由用户定义，仅可在同一个批处理中调用和访问
declare @intAge tinyint
set @intAge = 12
print @intAge
declare @strName varchar(12)
select @strName = 'state'
print @strName
select au_lname, @strName from authors
(3)逻辑控制
-- IF条件判断
declare @i int
set @i = 12
if (@i > 10)
begin -- {
print 'Dadadada!'
print 'Dadadada!'
end -- }
else
begin
print 'XiaoXiao!'
print 'XiaoXiao!'
end
-- While循环控制
declare @i int;
set @i = 12;
print @i
return;
while (@i < 18)
begin
print @i;
set @i = @i + 1;
if @i < 17
continue;
if @i > 15
break;
end;
-- CASE 分支判断
select au_lname, state, '犹他州' from authors where state = 'UT'
select au_lname, state, '密西西比州' from authors where state = 'MI'
select au_lname, state, '肯塔基州' from authors where state = 'KS'
select au_lname, state,
case state
when 'UT' then '犹他州'
when 'MI' then '密西西比州'
when 'KS' then '肯塔基州'
when 'CA' then '加利福利亚'
else state
end
from authors
(4.1)系统函数
-- 获取指定字符串中左起第一个字符的ASC码
print ascii('ABCDEF')
-- 根据给定的ASC码获取相应的字符
print char(65)
-- 获取给定字符串的长度
print len('abcdef')
-- 大小写转换
print lower('ABCDEF')
print upper('abcdef')
-- 去空格
print ltrim(' abcd dfd df ')
print rtrim(' abcd dfd df ')
-- 求绝对值
print abs(-12)
-- 幂
-- 3 的 2 次方
print power(3,2)
print power(3,3)
-- 随机数
-- 0 - 1000 之间的随机数
print rand() * 1000
-- 获取圆周率
print pi()
-- 获取系统时间
print getdate()
-- 获取3天前的时间
print dateadd(day, -3 , getdate())
-- 获取3天后的时间
print dateadd(day, 3 , getdate())
-- 获取3年前的时间
print dateadd(year, -3 , getdate())
-- 获取3年后的时间
print dateadd(year, 3 , getdate())
-- 获取3月后的时间
print dateadd(month, 3 , getdate())
-- 获取9小时后的时间
print dateadd(hour, 9 , getdate())
-- 获取9分钟后的时间
print dateadd(minute, 9 , getdate())
-- 获取指定时间之间相隔多少年
print datediff(year, '2005-01-01', '2008-01-01')
-- 获取指定时间之间相隔多少月
print datediff(month, '2005-01-01', '2008-01-01')
-- 获取指定时间之间相隔多少天
print datediff(day, '2005-01-01', '2008-01-01')
-- 字符串合并
print 'abc' + 'def'
print 'abcder'
print 'abc' + '456'
print 'abc' + 456
-- 类型转换
print 'abc' + convert(varchar(10), 456)
select title_id, type, price from titles
-- 字符串连接必须保证类型一致(以下语句执行将会出错)
-- 类型转换
select title_id + type + price from titles
-- 正确
select title_id + type + convert(varchar(10), price) from titles
print '123' + convert(varchar(3), 123)
print '123' + '123'
print convert(varchar(12), '2005-09-01',110)
-- 获取指定时间的特定部分
print year(getdate())
print month(getdate())
print day(getdate())
-- 获取指定时间的特定部分
print datepart(year, getdate())
print datepart(month, getdate())
print datepart(day, getdate())
print datepart(hh, getdate())
print datepart(mi, getdate())
print datepart(ss, getdate())
print datepart(ms, getdate())
-- 获取指定时间的间隔部分
-- 返回跨两个指定日期的日期和时间边界数
print datediff(year, '2001-01-01', '2008-08-08')
print datediff(month, '2001-01-01', '2008-08-08')
print datediff(day, '2001-01-01', '2008-08-08')
print datediff(hour, '2001-01-01', '2008-08-08')
print datediff(mi, '2001-01-01', '2008-08-08')
print datediff(ss, '2001-01-01', '2008-08-08')
-- 在向指定日期加上一段时间的基础上，返回新的 datetime 值
print dateadd(year, 5, getdate())
print dateadd(month, 5, getdate())
print dateadd(day, 5, getdate())
print dateadd(hour, 5, getdate())
print dateadd(mi, 5, getdate())
print dateadd(ss, 5, getdate())
-- 其他
print host_id()
print host_name()
print db_id('pubs')
print db_name(5)
-- 利用系统函数作为默认值约束
drop table ttt
create table ttt
(
stu_name varchar(12),
stu_birthday datetime default (getdate())
)
alter table ttt
add constraint df_ttt_stu_birthday default (getdate()) for stu_birthday
insert into ttt values ('ANiu', '2005-04-01')
insert into ttt values ('ANiu', getdate())
insert into ttt values ('AZhu', default)
sp_help ttt
select * from ttt
（4.2）自定义函数
select title_id
from titles
where type = 'business'
select stuff(title_id,1,3,'ABB'), type
from titles
where type = 'business'
select count(title_id) from titles where type = 'business'
select title_id from titles where type = 'business'
select *,count(dbo.titleauthor.title_id)
FROM dbo.authors INNER JOIN
dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id
select au_id, count(title_id)
from titleauthor
group by au_id
SELECT dbo.authors.au_id, COUNT(dbo.titleauthor.title_id) AS '作品数量'
FROM dbo.authors left outer JOIN
dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id
GROUP BY dbo.authors.au_id
order by '作品数量'
-- 自定义函数的引子（通过这个子查询来引入函数的作用）
-- 子查询
-- 统计每个作者的作品数
-- 将父查询中的作者编号传入子查询
-- 作为查询条件利用聚合函数count统计其作品数量
select au_lname,
(select count(title_id)
from titleauthor as ta
where ta.au_id = a.au_id
) as TitleCount
from authors as a
order by TitleCount
-- 是否可以定义一个函数
-- 将作者编号作为参数统计其作品数量并将其返回
select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount
from authors
order by TitleCount
-- 根据给定的作者编号获取其相应的作品数量
create function GetTitleCountByAuID(@au_id varchar(12))
returns int
begin
return (select count(title_id)
from titleauthor
where au_id = @au_id)
end
-- 利用函数来显示每个作者的作品数量
create proc pro_CalTitleCount
as
select au_id, au_lname, dbo.GetTitleCountByAuID(au_id) as TitleCount
from authors
order by TitleCount
go
-- 执行存储过程
execute pro_CalTitleCount
-- vb中函数定义格式
function GetTitleCountByAuID(au_id as string) as integer
.GetTitleCountByAuID = ?
end function
-- SALES 作品销售信息
select * from sales
-- 根据书籍编号查询其销售记录（其中，qty 表示销量）
select * from sales where title_id = 'BU1032'
-- 根据书籍编号统计其总销售量（其中，qty 表示销量）
select sum(qty) from sales where title_id = 'BU1032'
-- 利用分组语句（group by），根据书籍编号统计每本书总销售量（其中，qty 表示销量）
select title_id, sum(qty) from sales group by title_id
-- 是否可以考虑定义一个函数根据书籍编号来计算其总销售量
-- 然后，将其应用到任何一条包含了书籍编号的查询语句中
select title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales
from titles
order by TotalSales
-- 定义一个函数根据书籍编号来计算其总销售量
create function GetTotalSaleByTitleID(@tid varchar(24))
returns int
begin
return(select sum(qty) from sales where title_id = @tid)
end
-- 统计书籍销量的前10位
-- 其中，可以利用函数计算结果的别名作为排序子句的参照列
select top 10 title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales
from titles
order by TotalSales desc
-- 根据书籍编号计算其销量排名
create function GetTheRankOfTitle(@id varchar(20))
returns int
begin
return(select count(TotalSales)
from titles
where ToalSales >(
select TotalSales
from titles
where title_id=@id))
end
-- 根据书籍编号计算其销量排名
select dbo.GetTheRankOfTitle('pc1035') from titles
select count(title_id) + 1
from titles
where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID('pc1035')
-- 删除函数
drop function GetRankByTitleId
-- 根据书籍编号计算其销量排名
create function GetRankByTitleId(@tid varchar(24))
returns int
begin
return (select count(title_id) + 1
from titles
where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID(@tid))
end
-- 在查询语句中利用函数统计每本书的总销量和总排名
select title_id, title,
dbo.GetTotalSaleByTitleID(title_id) as TotalSales,
dbo.GetRankByTitleId(title_id) as TotalRank
from titles
order by TotalSales desc
-- 查看表结构
sp_help titles
-- 查看存储过程的定义内容
sp_helptext GetRankByTitleId
sp_helptext sp_helptext
sp_helptext xp_cmdshell
-- [ORDER DETAILS] 订单详细信息
select * from [order details]
select * from [order details] where productid = 23
-- 根据产品编号在订单详细信息表中统计总销售量
select sum(quantity) from [order details] where productid = 23
-- 构造一个函数根据产品编号在订单详细信息表中统计总销售量
create function GetTotalSaleByPID(@Pid varchar(12))
returns int
begin
return(select sum(quantity) from [order details] where productid = @Pid)
end
select * from products
-- 在产品表中查询，统计每一样产品的总销量
select productid, productname, dbo.GetTotalSaleByPID(productid) from products
--
CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
END
SELECT * FROM LargeOrderShippers( $500 )
-- 根据作者编号计算其所得版权费
create function fun_RoyalTyper ( @au_id id)
returns int
as
begin
declare @rt int
select @rt = sum(royaltyper) from titleauthor where au_id = @au_id
return (@rt)
end
go
select top 1 au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as '版权费'
from authors
order by dbo.fun_RoyalTyper(au_id) desc
go
create function fun_MaxRoyalTyper_Au_id ()
returns id
as
begin
declare @au_id id
select @au_id = au_id
from authors
order by dbo.fun_RoyalTyper(au_id)
return(@au_id)
end
go
select dbo.fun_MaxRoyalTyper_Au_id()
go
select au_lname, au_fname, dbo.fun_RoyalTyper(au_id) as '版权税'
from authors
where au_id = dbo.fun_MaxRoyalTyper_Au_id()
go
(5)高级查询
select title_id, price from titles
-- 查找最高价格
select max(price) from titles
-- 查找最贵书籍的价格(排序)，如果存在多本价格最贵的书，此方法将会遗漏
select top 1 title_id, price
from titles
order by price desc
-- 查找最贵书籍的价格(子查询)
select title_id, price
from titles
where price = (select max(price) from titles)
-- 查询指定出版社出版的书(连接)
select p.pub_name as '出版社', t.title as '书籍名称'
from publishers as p join titles as t on p.pub_id = t.pub_id
where pub_name = 'New Moon Books'
-- 查询指定出版社出版的书(子查询)
select title
from titles
where pub_id = (select pub_id
from publishers
where pub_name = 'New Moon Books')
-- 查询指定出版社出版的书(分开查询)
select title from titles where pub_id = '0736'
select pub_id
from publishers
where pub_name = 'New Moon Books'
-- 重点
-- 理解相关子查询的基础
--
select * from titles where type = 'business'
select * from titles where type = 'business123'
select * from titles where 1 = 1
-- 在订单表中寻找满足以下条件的订单编号以及相应的客户编号
-- 在详细订单表中存在对应的订单编号并且其中包含产品编号为23的产品
-- 然后将产品编号为23的产品订购量返回判断是否大于20
USE northwind
SELECT orderid, customerid
FROM orders AS or1
WHERE 20 < (SELECT quantity FROM [order details] AS od
WHERE or1.orderid = od.orderid
AND od.productid = 23)
GO
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
(
SELECT royaltyper FROM titleauthor
WHERE titleauthor.au_ID = authors.au_id
)
select authors.au_lname,authors.au_fname
from authors join titleauthor on titleauthor.au_ID=authors.au_id
where titleauthor.royaltyper =100
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE type = 'popular_comp'))
select distinct t.type, a.au_lname, a.au_fname
from authors as a join titleauthor as ta on a.au_id = ta.au_id
join titles as t on ta.title_id = t.title_id
where t.type = 'business'
-- 查找类型为'business'或是'trad_cook'类型的书籍
select * from titles where type = 'business'
select * from titles where type = 'trad_cook'
-- 查找类型为'business'或是'trad_cook'类型的书籍(Or)
select * from titles
where type = 'business' or type = 'trad_cook'
-- 查找类型为'business'或是'trad_cook'类型的书籍(In)
select * from titles
where type in ('business', 'trad_cook')
-- 查找来自'KS'或是'UT'的作者
select au_lname, state from authors
where state = 'KS'
select au_lname, state from authors
where state = 'UT'
-- 查找来自'KS'或是'UT'的作者(Or)
select au_lname, state from authors
where state = 'UT' or state = 'KS'
-- 查找来自'KS'或是'UT'的作者(In)
select au_lname, state from authors
where state in ('UT', 'KS')
select au_lname, state from authors
where state not in ('UT', 'KS')
-- 查找出版了类型为'business'类型的书籍的出版社
SELECT pub_id FROM titles WHERE type = 'business'
SELECT pub_id,pub_name
FROM publishers
WHERE pub_id IN ('1389', '0736')
-- 查找出版了类型为'business'类型的书籍的出版社(In和子查询)
SELECT pub_id,pub_name
FROM publishers
WHERE pub_id IN
(SELECT pub_id
FROM titles
WHERE type = 'business')
SELECT title, advance
FROM titles
WHERE advance >
(
SELECT MAX(advance)
FROM publishers INNER JOIN titles ON
titles.pub_id = publishers.pub_id
WHERE pub_name = 'Algodata Infosystems'
)
SELECT title, advance
FROM titles
WHERE advance > all
(
SELECT advance
FROM publishers INNER JOIN titles ON
titles.pub_id = publishers.pub_id
WHERE pub_name = 'Algodata Infosystems'
and advance is not null
)
declare @i int
set @i = 12
if @i < null
print 'DDDDD'
else
print 'XXXXX'
SELECT advance
FROM publishers INNER JOIN titles ON
titles.pub_id = publishers.pub_id
WHERE pub_name = 'Algodata Infosystems'
select title_id, price from titles
where price > all
(
select price from titles where type = 'business'
)
select title_id, price from titles
where price >
(
select max(price) from titles where type = 'business'
)
select title_id, price from titles
where price > any
(
select price from titles where type = 'business'
)
select title_id, price from titles
where price >
(
select min(price) from titles where type = 'business'
)
select price from titles where type = 'business'
if exists(select * from titles where type = '123')
print 'ZZZZZ'
else
print 'BBBBB'
if exists(select * from authors
where city = 'Berkeley' and state ='UT')
print 'Welcome'
else
print 'Bye-Bye'
-- 筛选出'business'以及'trad_cook'类型的书籍(联合查询)
select title_id, type from titles where type = 'business'
union
select title_id, type from titles where type = 'trad_cook'
-- 统计'business'类型的书籍的总价(联合查询)
select title, price from titles where type = 'business'
union
select '合计:', sum(price) from titles where type = 'business'
-- 统计所有书籍的类型剔除重复(Distinct)
select distinct type from titles
-- 作者记录的复制(Select Into)
select * into au from authors
select * from au
-- 查看数据表结构(Select Into并没有对数据表的约束进行复制)
sp_help authors
sp_help au
-- 分页(子查询的经典应用之一)
-- Jobs 职务信息表（pubs 数据库）
-- 在实际项目中，显示职务信息时，而职务信息量非常庞大，可能需要将其分为若干个页面来显示
-- 比如：每页显示4条记录，那么，第一页将显示1，2，3，4，第二页将显示5，6，7，8。。。。。
-- 显示所有信息
SELECT * FROM jobs
-- 显示前 4 信息
select top 4 * from jobs
-- 显示前 8 信息
select top 8 * from jobs
-- 显示前 12 信息
select top 12 * from jobs
-- 寻找规律，每一页的信息源于前（页面大小 * 页码）条信息的反序结果的前 页面大小 条记录
-- 比如：第二页就是前 8 条记录的反序结果的前 4 条
select top 4 *
from (select top 8 * from jobs) as tt
order by job_id desc
-- 当然，对于期望按升序显示查询结果的要求可以对查询结果进行再次排序
select * from
(select top 4 *
from (select top 8 * from jobs) as tt
order by job_id desc) as stt
order by job_id
-- SQL 命令中不支持在 select 的查询列表中直接使用局部变量
-- 比如：select top @PageSize * from jobs
-- 那么，可以考虑对sql命令进行拼装，然后，利用系统存储过程 sp_executesql 来执行
exec sp_executesql N'Select * from jobs'
-- 存储过程的实现
-- 其中，@CurrentPageSize用于确定最后一页的页面大小
create proc proGetJobsByPage
@CurrentPageSize int,
@PageSize int,
@CurrentPage int
as
Declare @strSql nvarchar(400)
set @strSql = 'select * from
(select top ' + convert(nvarchar(4), @CurrentPageSize) + ' *
from (select top ' + convert(nvarchar(4),(@PageSize * @CurrentPage)) + ' * from jobs) as tt
order by job_id desc) as stt
order by job_id'
exec sp_executesql @strSql
go
-- 测试
exec proGetJobsByPage 2, 4, 4
(6)存储过程
-- 扩展存储过程
-- 查询系统目录下文件信息
xp_cmdshell 'dir *.*'
-- 启动Windows系统服务
xp_cmdshell 'net start iisadmin'
(7)游标
-- 游标的五个基本操作步骤：
-- 声明
declare cur_titles cursor
for select title, price from titles
-- 打开
open cur_titles
-- 提取
fetch cur_titles
fetch next from cur_titles
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
-- 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书
-- 这一段为批处理版
-- 与批处理版相比，存储过程版更方便调试以及代码的重用
-- 声明
declare cur_titles cursor
for select title, price from titles
-- 打开
open cur_titles
declare @title varchar(80)
declare @price numeric(9,4)
declare @title_temp varchar(80)
declare @price_temp numeric(9,4)
-- 提取
fetch cur_titles into @title, @price
fetch cur_titles into @title_temp, @price_temp
while @@fetch_status = 0
begin
if @price < @price_temp
begin
set @price = @price_temp
set @title = @title_temp
end
fetch cur_titles into @title_temp, @price_temp
end
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
-- 显示处理结果
print '最贵的书是: ' + @title + ' ' + '价格是: ' + convert(varchar(12),@price)
go
-- 定义一个存储过程
-- 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书（游标具体应用的经典）
-- 这段存储过程的实现代码相对下面的实现方式略有不同
-- 代码重复，但是思路更清晰
create procedure pro_GetMaxTitle
as
-- 声明
declare cur_titles cursor
for select title, price from titles
-- 打开
open cur_titles
-- 存储最贵的书籍信息
declare @title varchar(80)
declare @price numeric(9,4)
-- 存储从游标中提取出来的书籍的信息
declare @title_temp varchar(80)
declare @price_temp numeric(9,4)
-- 提取
fetch cur_titles into @title, @price
-- 判断是否存在书籍信息
if @@fetch_status <> 0
begin
print '没有书籍信息!'
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
-- 结束存储过程
return
end
fetch cur_titles into @title_temp, @price_temp
-- 判断是否只存在一本书
if @@fetch_status <> 0
begin
-- 显示处理结果
print '最贵的书是: ' + @title + ' ' + '价格是: ' + convert(varchar(12),@price)
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
-- 结束存储过程
return
end
while @@fetch_status = 0
begin
if @price < @price_temp
begin
set @price = @price_temp
set @title = @title_temp
end
fetch cur_titles into @title_temp, @price_temp
end
-- 显示处理结果
print '最贵的书是: ' + @title + ' ' + '价格是: ' + convert(varchar(12),@price)
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
go
-- 定义一个存储过程
-- 利用游标遍历所有书籍信息，通过冒泡排序法进行比较，找出最高价格的书
-- 相对上面的实现方式，以下实现方式更简洁
create procedure pro_GetMaxTitle
as
-- 声明
declare cur_titles cursor
for select title, price from titles
-- 打开
open cur_titles
-- 存储最贵的书籍信息
declare @title varchar(80)
declare @price numeric(9,4)
-- 存储从游标中提取出来的书籍的信息
declare @title_temp varchar(80)
declare @price_temp numeric(9,4)
-- 提取
fetch cur_titles into @title, @price
-- 判断是否存在书籍信息
if @@fetch_status = 0
begin
print '没有书籍信息!'
goto errNoTitles
end
fetch cur_titles into @title_temp, @price_temp
-- 判断是否只存在一本书
if @@fetch_status = 0
begin
goto errOnlyOne
end
while @@fetch_status = 0
begin
if @price < @price_temp
begin
set @price = @price_temp
set @title = @title_temp
end
fetch cur_titles into @title_temp, @price_temp
end
errOnlyOne:
-- 显示处理结果
print '最贵的书是: ' + @title + ' ' + '价格是: ' + convert(varchar(12),@price)
errNoTitles:
-- 关闭
close cur_titles
-- 释放
deallocate cur_titles
go
-- 根据作者编号查看其相应的作品年销售量
-- 低于5000,提示: 销售量太低
-- 高于5000,提示: 销售量太高
create procedure pro_sales_avg (@au_id id)
as
if exists(select au_id from authors where au_id = @au_id)
begin
declare TempSales cursor
for
select title, ytd_sales
from titleauthor ta join titles t
on ta.title_id = t.title_id
where au_id = @au_id
open TempSales
declare @t varchar(80)
declare @y int
fetch TempSales
into @t, @y
while @@fetch_status = 0
begin
if 5000 > @y
print @t + ' ' + convert(varchar(5),@y) + ' 销售量太低'
else
print @t + ' ' + convert(varchar(5),@y) + ' 销售量太高'
fetch TempSales
into @t, @y
end
close TempSales
deallocate TempSales
end
else
print '作者编号无效！'
go
exec pro_sales_avg '213-46-8915'
/*
示例
A. 使用简单游标和语法
打开该游标时所生成的结果集包括 pubs 数据库的 authors 表中的所有行和列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为没指定 SCROLL 选项，FETCH NEXT 是唯一可用的提取选项。
*/
DECLARE authors_cursor CURSOR
FOR
SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
/*
B. 使用嵌套游标生成报表输出
下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。
*/
SET NOCOUNT ON
DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80), @title varchar(80)
PRINT "-------- Utah Authors report --------"
DECLARE authors_cursor CURSOR
FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname
PRINT @message
-- Declare an inner cursor based
-- on au_id from the outer cursor.
DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title
IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>"
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END
CLOSE titles_cursor
DEALLOCATE titles_cursor
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO
-------- Utah Authors report --------
----- Books by Author: Anne Ringer
The Gourmet Microwave
Is Anger the Enemy?
----- Books by Author: Albert Ringer
Is Anger the Enemy?
Life Without Fear
(8)触发器
-- 设定数据库的递归触发器
alter database pubs
set recursive_triggers on
go
-- 创建数据表，并设定主键、外键以及缺省约束
create table emp_mgr
(
Emp char(30) primary key,
Mgr char(30) null foreign key references emp_mgr(Emp),
NoOfReports int default 0
)
go
-- 创建插入触发器
create trigger emp_marins
on emp_mgr
for insert
as
declare @e char(30),@m char(30)
declare cur_mgr cursor for
select emp_mgr.emp
from emp_mgr,inserted
where emp_mgr.emp = inserted.mgr
open cur_mgr
fetch next from cur_mgr into @e
while @@fetch_status = 0
begin
update emp_mgr
set emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1
where emp_mgr.emp = @e
fetch next from cur_mgr into @e
end
close cur_mgr
deallocate cur_mgr
go
-- 查看数据表相关触发器
sp_helptrigger emp_mgr
go
create trigger emp_mgrupd
on emp_mgr
for update
as
if update (mgr)
begin
update emp_mgr
set emp_mgr.NoOfReports = emp_mgr.NoofReports + 1
from inserted
where emp_mgr.emp = inserted.mgr
update emp_mgr
set emp_mgr.NoOfReports = emp_mgr.NoOfReports -1
from deleted
where emp_mgr.emp = deleted.mgr
end
go
insert emp_mgr(emp,mgr) values ('Harry',null)
insert emp_mgr(emp,mgr) values ('Alice','Harry')
insert emp_mgr(emp,mgr) values ('Paul','Alice')
insert emp_mgr(emp,mgr) values ('Joe','Alice')
insert emp_mgr(emp,mgr) values ('Dave','Joe')
go
select * from emp_mgr
go
update emp_mgr
set mgr = 'Harry'
where emp = 'Dave'
go
select * from emp_mgr
go
