sqlserver.sql

SQLServer宝典

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: