SQL Server宝典(2)

/*

北大青鸟 株洲奥特培训中心

黄宁 老师

2004年04月01日

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 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

dim strName string
declare @au_id varchar(12)

-- 根据给定的作者编号获取其相应的作品数量
create function GetTitleCountByAuID(@au_id varchar(12))
returns int
begin
 return (select count(title_id)
  from titleauthor
  where au_id = @au_id)
end

select * from sales

select * from sales where title_id = 'BU1032'

select sum(qty) from sales where title_id = 'BU1032'

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

select top 10 title_id, title, dbo.GetTotalSaleByTitleID(title_id) as TotalSales
from titles
order by TotalSales desc

create function getpaixu(@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.getpaixu('pc1035') from titles

select count(title_id) + 1
from titles
where dbo.GetTotalSaleByTitleID(title_id) > dbo.GetTotalSaleByTitleID('pc1035')

drop function GetRankByTitleId

alter 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

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

select p.pub_name, t.*
from titles as t join publishers as p on t.pub_id = p.pub_id
where p.pub_name = 'hello'

select * from publishers

select p.pub_name, t.*
from titles as t join publishers as p on t.pub_id = p.pub_id
where p.pub_name = 'Algodata Infosystems'

alter procedure GetTitlesByPub(@PubName varchar(36))
with encryption
as
select p.pub_name, t.*
from titles as t join publishers as p on t.pub_id = p.pub_id
where p.pub_name = @PubName
go

sp_helptext GetTitlesByPub

execute GetTitlesByPub 'hello'
execute GetTitlesByPub 'Algodata Infosystems'

alter procedure TestNull
as
print 'Hello World!'
go

execute TestNull

alter function TestNullFun()
returns int
with encryption
begin
 return(1)
end

sp_helptext TestNullFun

(5)高级查询

(6)存储过程

xp_cmdshell 'dir *.*'

xp_cmdshell 'net start iisadmin'

use northwind
go

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 )