数据库开发答疑200问学习笔记
----------------------------------细节学习------------------------------------------
set nocount on -- 返回受影响的行数，当设置为on的时候可以减少网络流量，用处不是很大
--=================================学习临时表=========================================
if object_id('tempdb..#yu') is not null
drop table #yu
create table #yu
(
name nvarchar(100) not null unique,
sex bit not null,
age int
)
insert into #yu values('余','1',12)
insert into #yu values('联','0',22)
declare @yu nvarchar(100) --定义变量@yu用set语句赋值
set @yu='name'
declare @sql nvarchar(200) --定义变量@sql用select语句赋值
select @sql='select '+@yu+' from #yu' --这里是字符串拼接赋值
--判断定义临时表#lian
if object_id('tempdb..#lian') is not null
drop table #lian
create table #lian
(
name nvarchar(100) not null unique,
)
insert into #lian exec(@sql) --用exec执行sql语句添加到另外一个临时表中去
if exists(select name from tempdb..sysobjects where name like '#lian%' and type='u')
drop table #lian
--============================================================================================
----------------------------------------------游标的学习--------------------------------------
--定义3个变量接收每一行的数据
declare @q nvarchar(100)
declare @w bit
declare @e int
declare youbiao cursor --这里可以加个global表示游标是全局的，表示在引用此游标的变量结束后结束，也可以显示的释放和关闭
for select * from #yu ----好处就是可以再存储过程或触发器等中定义全局游标，如果在存储过程等结束的时候没有关闭游标，那么在其他地方可以再用此游标
open youbiao --打开游标
fetch next from youbiao into @q,@w,@e --依次让每一条数据填充定义的3个变量
while(@@fetch_status=0) --判断游标的取值是否合法，0 fetch语句成功，-1 fetch语句失败或此行不在结果集中，-2被提取的行不存在
begin
print @q
print @w
print @e
update #yu set name=@q+'lala' where current of youbiao
fetch next from youbiao into @q,@w,@e --继续接收下一条数据，循环处理
end
close youbiao --关闭游标
deallocate youbiao --删除游标
---------------------------------------------索引学习--------------------------------------------
-----非聚集索引
if exists(select name from sysindexes where name='yuyu') --判断要创建的索引是否存在
drop index admin.yuyu --删除索引
go
create index yuyu on admin(username) --新建一个单列非聚集索引
if exists(select name from sysindexes where name='lianlian') --判断要创建的索引是否存在
drop index admin.lianlian --删除索引
go
create clustered unique index lianlian on admin(username asc,password desc) --新建一个多列非聚集索引
----注意：聚集索引最好不要用在经常更改的列
----聚集索引就是在创建索引时在index前加上一个clustered，也可以在单列或者多列上创建
----但是要记住是创建聚集索引的时候一定要检查是否已经存在聚集索引，不管它是否重名，
----因为一个表只能有一个聚集索引
----创建带填充因子的索引
if exists(select name from sysindexes where name='kk')
drop index admin.kk
go
create nonclustered index kk --非聚集索引
on admin(username)
with fillfactor=100 --包括填充因子
drop index admin.kk
---------------------------------学习创建计算列------------------------------------------------
create table #en
(
a int,
b int, --------------注意：计算列无需为它定义数据类型它会根据计算的结果自己找合适的类型
c as a+b, ---定义整型计算列
d as a*9, ---定义整型计算列
shijian datetime,
时间差 as datediff(day,shijian,getdate()),
e as case a --用case分支条件,给e赋值
when 0 then a --具体的分支语句
when 1 then b
else (a+b)*a
end --结束标志
)
insert into #en values(9,2,'2009-3-12') --在插入的时候只需插入定义的列，无需添加计算列
select * from #en
drop table #en
-----------------------------------学习跨数据库跨服务器查询(分布式查询2种方法)----------------------
--前言：对所有的分布式查询基本上都是基于【opendatasource】【openrowset】
--【sp_addlinkedserver，sp_addlinkedsrvlogin，openquery】
--他们同时可以实现对Acesss,EXCEL,TXT文本，DB2,ORACLE的操作
-------------实现跨数据库的查询
select * from db_news.dbo.pinglun ,gradesys.dbo.admin
where db_news.dbo.pinglun.id *= gradesys.dbo.admin.userid
--特殊连接器名称法(在引用远程不是很频繁的时候用此法)：实例
--http://www.cnblogs.com/McJeremy/archive/2008/11/07/1328739.html
--http://www.dotnetxx.cn/shownews.aspx?id=53
------------实现跨服务器的查询(opendatasource函数)
--1：
select A.*,B.* from
opendatasource('sqloledb', 'data source=210.38.202.16; user id=sa;password=sgu3197').netshop.dbo.notice A ,
opendatasource('sqloledb', 'data source=.;user id=sa;password=sa').db_news.dbo.pinglun B
where A.id *=B.id
--2：查询Excel 电子表
select* from opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="H:\班级文件\mm.xls";User ID=admin;Password=;Extended properties=Excel 5.0')...mm$
------------实现跨服务器的查询(openrowset函数)
select A.* from
openrowset('sqloledb', '210.38.202.16'; 'sa'; 'sgu3197', 'select * from netshop.dbo.notice') as A
go
--连接服务器法：实例，可以用于EXECEL和ORALC
--详解：http://msdn.microsoft.com/zh-cn/library/ms190479.aspx
--------http://hi.baidu.com/aweiwenyuan/blog/item/9aec70f3798c42c80b46e049.html
---删除连接服务器
exec sp_droplinkedsrvlogin 'srv_lnk',null
exec sp_dropserver 'srv_lnk'
---创建连接服务器
exec sp_addlinkedserver 'srv_lnk','','sqloledb','210.38.202.16'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','sgu3197'
---这个允许调用链接服务器上的存储过程
exec sp_serveroption 'srv_lnk','rpc out','true'
go
---连接后的操作，其中srv_lnk是远程数据库的别名
---1：openquery：http://hi.baidu.com/freeperson/blog/item/d5c70c34513ed7335ab5f5ff.html
insert into openquery(srv_lnk, 'SELECT title, content FROM msgs')values ('title', 'content')
select * from openquery(srv_lnk,'select * from netshop.dbo.notice')
---2：远程存储过程，
exec srv_lnk.netshop.dbo.guocheng1
----------------------------------
-----------------------------------视图的学习----------------------------------------------------
---1视图的定义
create view arm as select * from admin ,adminurl ---创建一个视图来至2个基表
update arm set [group]='超级用户le' where userid=1 ---如果修改视图中的内容，
--当只影响单个基表，那么相应的基表也会改变，如果涉及到几个基表那么视图的修改不会成功
drop view arm --删除视图
select * from arm
exec sp_depends 'admingroup' ---查看其他对象或者本对象是否有相互引用查询
----通过修改视图的信息修改基表,用instead of触发器
create trigger chufaqi on arm --定义触发器
instead of insert --类型是instead of（而after和for触发器不能用在视图触发器中）
as insert into adminurl select url,urlname,comment from inserted --这里可以写上逻辑的代码
insert into arm values(2,'2','44','l',5,'d','s','d') --事例插入，其实只是插入了adminurl表
drop trigger chufaqi --删除触发器
------------------------------4种方法判断类似表的存在--------------------------------------------
---判断表是否存在
if exists(select * from information_schema.tables where table_name='admin')
print '1'
---判断视图是否存在
if exists(select * from information_schema.views where table_name='arm')
print '2'
---函数法判断
if object_id('admin') is not null
print '2'
---查询法判断
if exists(select name from sysobjects where name='admin' and type='u')
print '3'
---存储过程发
exec sp_tables @table_name='admin',@table_type="'table'"
print @@rowcount
----顺便详细学习3中方法确定是否存在元数据
---方法一：系统函数法 object_id('要查的元数据名字')，
---有必要的时候会在前面加上数据库的名字。如：object_id('tempdb..#yu')
if object_id('arm') is not null drop view arm
else print '1'
---方法二：查询系统表
if exists(select name from sysobjects where name='arm' and type='v') drop view arm
else print '2'
--方法三：系统存储过程
---用此方法判断视图
exec sp_tables @table_name='arm' , @table_type="'view'"
if @@rowcount=0 print '无'
else print '有'
---用此方法判断表的存在
exec sp_tables @table_name='admin' ,@table_type="'table'"
print @@rowcount
---object_id,sysobjects主要用在表，视图，临时表中，sysobjects与type搭配，U表示临时表，V表示视图
---sysindexes这个用在定义的索引上，没有type，直接来
---------------------------------------------权限管理-------------------------------------
grant select on admin to public ---授什么权限在那个对象（表，视图，存储过程等）给那个对象（角色，用户等）
revoke select on admin to public ---删除权限
---===================================触发器学习==============================================
--http://topic.csdn.net/t/20030918/22/2276397.html
--在表上可以定义多个for或者after触发器，但是只能定义一个instead of触发器但instead of触发器可以定义在视图上，而after和for触发器不能定义在视图上
--在定义多行操作的触发器的时候，现在知道的方法是建立一个游标来存储临时表的记录集，然后逐条处理
--触发器是一种特殊的存储过程，可以把触发器本身和触发这个触发器的语句看成一个事物来处理
--after和for 是在sql代码和约束运行完以后才执行的触发器，而instead of是在之前执行的
-- After触发器：触发时机在资料已变动完成后，它将对变动资料进行必要的
--善后与处理，若发现有错误，则用事务回滚（Rollback Transaction)
--将此次操作所更动的资料全部恢复。
-- Istead of 触发器：触发时机在资料变动前发生，且资料如何变动取决于触发器
--INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义
---**********************触发器的临时表*******************************
-- 虚拟表Inserted 虚拟表Deleted
-- 新增时 存放新增的记录 不存储记录
-- 修改时 存放用来更新的新记录 存放更新前的记录
-- 删除时 不存储记录 存放被删除的记录
---判断触发器的存在
if exists(select name from sysobjects where name='触发器名' and type='tr')
drop trigger 触发器名 --删除触发器
if update(num) --判断某列是否更改
rollback transaction --回滚事务，好像是每个触发器相当一个事物来处理的
if @@rowcount=0 return --可以判断是否删除或者添加成功，然后执行触发器
set nocount on --作用是取消计数器，增加网络流量
---******************************现在做一个例子进行巩固**********************************
---************************************************************************************
----创建一个具有判断是处理单条记录还是处理多条记录的触发器，并且在分添加，删除，修改的处理
create trigger chufa on adminurl
for insert,delete,update ---创建一个insert,delete,update综合的触发器
as ---这里需要说明的是用for或者instead of都不会影响@@rowcount因为它是不管先处理那个都要先写入inserted或者deleted表中，然后计算@@rowcount
begin
---************************
if @@rowcount=0 return ---第一块：判断是否有改变项
---************************
---************************ ---第二块：判断是否是单条操作
if @@rowcount=1 ---判定是单条记录操作，并且确定是什么操作（添加，删除或更新）
begin
if((select count(*) from inserted)=1 and (select count(*) from deleted)=0)
print '更新'
else if((select count(*) from inserted)=0 and (select count(*) from deleted)=1)
print '删除'
else if((select count(*) from inserted)=1 and (select count(*) from deleted)=0)
print '添加'
end
---************************
---************************ 判断是否是多条操作，具体是哪个操作，和具体的处理，
---************************ 在这里写了一个关于多记录插入的例子，用到了游标的处理
else
begin
if((select count(*) from inserted)>1 and (select count(*) from deleted)>1)
print '多条更新'
else if((select count(*) from inserted)=0 and (select count(*) from deleted)>0)
print '多条删除'
else if((select count(*) from inserted)>1 and (select count(*) from deleted)=0)
begin
print '添加多条'
declare @m int
set @m=0
declare @n int
declare youbiao cursor for select [id] from inserted
open youbiao
fetch next from youbiao into @n
while(@@fetch_status=0)
begin
set @m=@m+2
fetch next from youbiao into @n
end
close youbiao
deallocate youbiao
print @m
end
end
end
---***********************
---***********************
drop trigger chufa ---删除触发器
select * from adminurl
--------------实例实验
insert into adminurl select * ,'' from #n ---注意语句中的''，当查询表里面没有添加表的字段的时候可以用常量代替，它不会影响查询操作
insert into adminurl select url,urlname,comment from adminurl where id in (15,8,6,4,1) --多条插入，检验触发器对多条插入的相应
insert into adminurl select url,urlname,comment from adminurl where id=8 --单条插入
----------*********另外一个特别的例子*******--------------
---问题的描述：
---我有一个表，可能由其他表的触发器来插入、修改和删除，现在我希望在这个表加一个触发器，
---来拒绝不是通过触发器的操作，但是怎么知道是不是通过触发器的操作呢？
---解决方法：
---在你的入庫表（RKB）、出庫表（CKB）等表的触發器代碼中的一開始加入如下代碼:
declare @temptable varchar(50) --定义一个变量
set @temptable = '##temp'+cast(@@spid as varchar) --@@spid是进程数，组合起来是为了定义一个全局临时表
exec ('create table '+@temptable+'(a1 int)') --定义一个全局临时表
--..........................
--.......................... --这些没有写的代码是RKB，CKB表上的添加删除更新时对KCB的具体操作
--在庫存表（KCB）上創建如下的觸發器:
create trigger trigger_KCB on KCB
for insert,update,delete
as
declare @temptable varchar(50)
set @temptable = '##temp'+cast(@@spid as varchar) --定义一个和上面那个触发器一样值的变量
if object_id('tempdb.dbo.'+@temptable) is not null --查看在添加删除修改的时候是否有全局临时表
exec ('drop table '+@temptable) --如果存在就把临时表删除，为下一次操作做准备
else --如果不存在，那么它就不是通过触发器来添加删除修改KCB的
rollback --那么就回滚，不让其继续操作
----******************又一个经典例子：实现对于多行操作的时候进行统计****************
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1 ---判断是否是单行操作
BEGIN
UPDATE PurchaseOrderHeader ---更新表中的一个字段
SET SubTotal = SubTotal + LineTotal ---直接用+没有用select语句，因为是单条记录所以只有一个值
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID
END
ELSE ---说明是多行插入
BEGIN
UPDATE PurchaseOrderHeader
SET SubTotal = SubTotal + ---开始更新
(SELECT SUM(LineTotal) ---实现分类累计
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID
= inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN --这里有点没有明白
(SELECT PurchaseOrderID FROM inserted)
------------------------------------定义级联删除（级联更新一样）---------------------------------
-----问题的提出：表A中的No与表B中的字段No对应（作为外键），表B中的No与表C中的No对应（作为外键），用级联参考完整性
alter table b drop constraint 原来的约束名 --删除原来的约束
alter table b add constraint con_b --添加外键
foreign key(no) references a(no) on delete cascade --实现级联删除
go
alter table c drop constraint 原来的约束名 --删除原来的约束
alter table c add constraint con_c --添加外键
foreign key(no) references b(no) on delete cascade --实现级联删除
--========================================约束的学习=================================================
--有4种约束
--分别是：主键，外键，唯一性，检查，默认
--********创建一个表************
create table stuinfo
(
stuid char(8) not null,
number int,
stuname nvarchar(50),
stusex nvarchar(2)
)
drop table stuinfo --删除表
----添加主键
alter table stuinfo add constraint z primary key(stuid)
----删除主键
alter table stuinfo drop constraint z
----添加外键
alter table stuinfo add constraint w foreign key(number) references adminurl(id)
---删除外键
alter table stuinfo drop constraint w
---添加唯一性
alter table stuinfo add constraint wy unique(stuid)
---删除唯一性
alter table stuinfo drop constraint wy
---添加默认约束
alter table stuinfo add constraint mr default '无' for stuname
---删除默认约束
alter table stuinfo drop constraint mr
---添加检查约束
alter table stuinfo add constraint jc check(number>061101321000 and number<061101321999)
---删除检查约束
alter table stuinfo drop constraint jc
--*********直接在建表中就给以约束***************
create table stuinfo1
(
stuid char(8) not null primary key,
stuname varchar(10) unique,
stusex char(2) default '男',
stuage tinyint check(stuage>10 and stuage<40),
stutel char(14)
)
CREATE PROCEDURE dizeng AS
update lab set id=id+2
GO
exec dizeng
----====================================存储过程学习==========================================
@@IDENTITY --返回最后插入的标识值
select * from adminurl
---存储过程中不能创建：触发器，视图，规则，默认值，存储过程，但是可以创建临时表
---存储过程的参数可以是默认值，也就是在参数后面直接加上（=默认值）如果有like可以再默认值里加上通配符
---对于通配符也就是正则表达式里面那些字符表达式(%,[],^,|.....）
-----------实例1:实现模糊查询和提取前几条记录------------
if exists(select * from sysobjects where name='guocheng' and type='p') ---判定存储过程是否存在
drop proc guocheng
go
create proc guocheng
@id int ,
@name nvarchar(50)
as
set nocount on
set rowcount @id --设置要查询多少条数据
--select * from adminurl where urlname like @name --(1)这里的通配符是在传参数的时候带上的
select * from adminurl where urlname like '%'+@name+'%' --(2)这里的通配符是程序自带的，推荐这个
go
exec guocheng 4 ,'%管理%' ---执行（1）的时候
exec guocheng 5 ,'管理' ---执行（2）的时候
----------------------------
----------实例2：加密存储过程和实现另外一种模糊查询（用到系统函数）-------------
if exists(select name from sysobjects where name='guocheng2' and type='p')
drop proc guocheng2 --判断是否存在
go
create proc guocheng2
@name nvarchar(100)
with encryption ---实现对存储过程加密，以后谁也看不到内容，所以事先要有备份
as
set nocount on
select * from adminurl where charindex(@name,urlname)>0 --chaindex的作用相当于Like @name
go
drop proc guocheng2
exec guocheng2 '功能'
-----------------------------
----------------存储过程的几种返回值（output,return,select）-----------------
--(1)output存储过程[注意在.NET中是怎样接受的]
if exists(select name from sysobjects where name='guocheng3' and type='p')
drop proc guocheng3 --判断是否存在
go
create proc guocheng3
@n int output, ---申明是输出参数
@name nvarchar(50)
with encryption ---加密
as
set nocount on --不显示记录数，提高网络
select * from adminurl where urlname like '%'+@name+'%'
set @n=@@rowcount --赋值
go
--开始测试
declare @n int --定义输出参数
exec guocheng3 @n output ,'管理'
print @n --验证是否输出参数已经赋值
----（2）return存储过程,切记return返回的必须是整型值[注意在.NET中是怎样接受的]
if exists(select name from sysobjects where name='guocheng4' and type='p')
drop proc guocheng4
go
create proc guocheng4
@name nvarchar(50),
@n int
with encryption
as
set nocount on
set rowcount @n
select * from adminurl where urlname like '%'+@name+'%'
if(@@rowcount>0)
return 1
else
return 0
go
----开始测试
declare @m int
exec @m=guocheng4 '管理',4
print @m
------(3):带返回游标的存储过程,并且游标只能是output类型
--【1.定义】
if exists(select * from sysobjects where name='guocheng5' and type='p')
drop proc guocheng5 ---判断存在否
go
create proc guocheng5
@youbiao cursor varying output ---定义一个游标输出参数，varying表示可以变化的
as
set @youbiao=cursor forward_only --forward_only表示从第一条开始往下
--[static]（这里可以添加）
for select comment from adminurl --static表示建立一个临时副本，不允许修改基表，如果没有就可以修改基表
open @youbiao --打开游标
go
--【2.使用】
if exists(select name from sysobjects where name='guocheng6' and type='p')
drop proc guocheng6
go
create proc guocheng6 --用来调用guocheng5
as
declare @n nvarchar(100) --定义一个变量用于接收游标的移动的每条记录
declare @youbiao2 cursor --定义一个游标作参数，用于上面那个存储过程
exec guocheng5 @youbiao=@youbiao2 output --赋值给定义个游标
fetch next from @youbiao2 into @n --每条记录赋值
while(@@fetch_status=0)
begin
if(@n='2m')
update adminurl set comment=comment+'M' where current of @youbiao2 --能进行修改的前提是上面定义的游标没有static
else
update adminurl set comment=comment+'O' where current of @youbiao2 --能进行修改的前提是上面定义的游标没有static
fetch next from @youbiao2 into @n --循环赋值
end
close @youbiao2
deallocate @youbiao2
go
exec guocheng6 --开始执行存储过程6
---------------------------------------------------------
--------创建一个带默认值的带判断的存储过程
if exists(select name from sysobjects where name='guocheng7' and xtype='p')
drop proc guocheng7
go
create proc guocheng7
@name nvarchar(100)=null, ----定义一个默认值是空的输入参数
@n int output ----定义一个输出参数
as
if @name is null ----判断参数是否为空
begin
print 'error!!!'
return
end
select @n=count(*) from adminurl where urlname like '%'+@name+'%' ---给输出参数赋值
print @n
go
declare @m int ----定义临时变量
exec guocheng7 '管理',@m ----执行
exec guocheng7 @n=@m ---执行带默认值的,但是不能写成 exec guocheng7 @m
-----------执行远程存储过程--------
---创建连接服务器
exec sp_addlinkedserver 'srv_lnk','','sqloledb','210.38.202.16'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','sgu3197'
---这个允许调用链接服务器上的存储过程
exec sp_serveroption 'srv_lnk','rpc out','true'
go
---执行远程存储过程，其中srv_lnk是远程数据库的别名
exec srv_lnk.netshop.dbo.guocheng1
----------------------------------
--------设置或撤销自动执行存储过程---
use master --必须设置这个数据库
exec sp_procoption '存储过程名字','startup','on' --设置自动执行的存储过程
exec sp_procoption '存储过程名字','startup','off' --取消自动执行的存储过程
go
----------------------------------
--------------------作业的学习，主要用企业管理器做，代码简要看看就OK-------------------------------------
-- 2009-7-4/20:48 上生成的脚本
-- 由: YU\Administrator
-- 服务器: (LOCAL)
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- 删除同名的警报（如果有的话）。
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'实验')
IF (@JobID IS NOT NULL)
BEGIN
-- 检查此作业是否为多重服务器作业
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- 已经存在，因而终止脚本
RAISERROR (N'无法导入作业“实验”，因为已经有相同名称的多重服务器作业。', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- 删除［本地］作业
EXECUTE msdb.dbo.sp_delete_job @job_name = N'实验'
SELECT @JobID = NULL
END
BEGIN
-- 添加作业
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'实验', @owner_login_name = N'YU\Administrator', @description = N'作业的实验', @category_name = N'[Uncategorized (Local)]', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- 添加作业步骤
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'第一步', @command = N'exec dizeng', @database_name = N'B2CSystem', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- 添加作业调度
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'调度', @enabled = 1, @freq_type = 4, @active_start_date = 20090704, @active_start_time = 204400, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- 添加目标服务器
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
---------------*************************函数的学习*********************-----------------------
------先实现一个标量的取代递归的函数----
--问题的描述
/*
有这样一张树形结构表，
如：C18 数码摄像机 是在 C12 数码产品 类别下
而 C12 数码产品 又在C2 IT产品 类别下！
C2 IT产品 在 000(根节点下)
即分类为 C2 IT产品-C12 数码产品-C18 数码摄像机
现在假使有这样一种需要 ，通过SQLserver平台，达到给定ClassID得到其所有父节点的ClassID，
并且通过“_”连接起来！如 C18 的结果为 C2_C12_C18
*/
---判断函数的存在
--注意xtype:fn标量函数，if：内嵌表函数，tf表函数
if exists(select name from sysobjects where name='diguihanshu' and xtype='fn')
drop function diguihanshu
go
create function diguihanshu -----虽然要制造递归，但是不能在同一个函数定义，只能借助另外定义一个函数
(
@n int
)
returns int ----这里必须是returns，不需要指明变量名
as
begin ----因为是标量函数必须以begin开始和end结束
declare @fid int
select @fid=fid from lab where id=@n
return @fid
end
if exists(select name from sysobjects where name='diaoyongdigui' and xtype='fn')
drop function diaoyongdigui
go
create function diaoyongdigui
(
@n int
)
returns nvarchar(100)
as
begin --标量函数以begin开始
declare @fid int
set @fid=dbo.diguihanshu(@n) ---调用的时候要指明拥有函数的拥有者，如：dbo
declare @bianhao nvarchar(100)
set @bianhao=cast(@n as nvarchar(100))
set @bianhao=cast(@fid as nvarchar(100))+'_'+@bianhao
while(@fid<>1) ---判定是否为1，因为根节点是1，这里的循环就是可以取代递归的方法
begin
set @bianhao=cast(dbo.diguihanshu(@fid) as nvarchar(100))+'_'+@bianhao
set @fid=dbo.diguihanshu(@fid)
end
return @bianhao
end
print dbo.diaoyongdigui(189)
---------------------------------------------------
-----------内联表函数-----------------
if exists(select name from sysobjects where name='nl' and type='Tf')---TF表示返回表函数
drop function nl
go
create function nl --定义参数
(
@name nvarchar(100),
@age int,
@number nvarchar(100)
)
returns @biao table --返回表，要有表名和具体的列
(
[name] nvarchar(100),
age int,
number nvarchar(100)
)
as
begin --开始一些操作
insert into @biao values(@name,@age,@number)
insert into @biao values(@name,@age+1,@number)
return
end
select * from nl('d',223,'dfdfsd') --调用
-----------------------------------------------------------------------------------------------
-----------------------------***********事务的学习************----------------------------------
----创建和回滚到事务保存点--------------------------
begin tran kaishi ---事务开始运行
insert into lab values(1,22)
if(@@rowcount>0) ---判断是否执行成功
save tran baocun1 ---建立一个事务保存点
insert into lab values(2,2) ---故意插入一个错误信息
if(@@rowcount<0)
rollback tran baocun1 ---回滚到baocun1的状态
--rollback tran ---回滚整个事务
commit tran ---完成整个存储过程
---------------------------------------------------
--如何启动分布式事务
--http://www.cnblogs.com/chnking/archive/2007/04/04/699891.html
--用事务恢复数据库
----------------------------------------------------------------------------------------------
---------------------***************实现定时备份数据库************-----------------------------
--步骤：http://www.cnblogs.com/guodapeng/archive/2008/05/22/1204982.html
--备份数据库，示例：
--一般是先建立一个作业，然后把下面的代码添加到作业中步骤的命令中去
BACKUP DATABASE B2CSystem
TO DISK = 'c:\B2CSystem.bak'
---------------------------------------------------------------------------------------------
------------------**************全文索引和全文检索***************-----------------------------
--但是一般没有启动全文检索，所以下面的全文检索不能运行，但是也要写在这里，相当于一个系列的学习
--这里没有学好，以后有机会碰到在研究
--用and, or, and not
select * from adminurl
-------对比
select * from adminurl where urlname like '%信息%' --正常情况
--下面运行出错
select * from adminurl where contains(urlname,'信息') --检查单词
select * from adminurl where contains(urlname,'"信息 管理"')--检索短语
select * from adminurl where contains(*,'"信息 管理"')--所有列检索短语
select * from adminurl where contains(urlname,'信息 or 管理')--2个单词
select * from adminurl where contains(urlname,'"信息 理论" or "管理 理论"')--2个单词
select * from adminurl where contains(urlname,'信息 and not ("管理*")')
select * from adminurl where contains(urlname,'信息 and 管理')
select * from adminurl where contains(urlname,'信息*')--表示与“信息”有关的都符合
select * from adminurl where freetext(urlname,'信息*')--和contains差不多
-------
-----------------------**********常用语法规则****************--------------------------------------
---------------***********常见系统函数
--cast 和 convert(数据类型转换)
--app_name（返回当前会话应用程序）
declare @m nvarchar(200)
set @m=app_name()
print @m
--结果为：SQL 查询分析器
-----------------------
--coalesce(返回参数中第一个非空表达式)
--getdate()(返回发当前时间和日期）
--user_name()(返回当前用户)和current_user一样
print user_name()
print current_user
--datalength(返回表达式所占字节数)
print datalength('dssdsdsdsds')
--@@error(返回出错的行0表示没错误)
print @@error
--@@identity（返回最后操作的标识符值）
select @@identity
--@@rowcount(返回上一语句所影响的行数)
print @@rowcount
--charindex(如果列有与表达式相同的则返回大于0的数)
charindex(列名，'表达式')
------------***********常用时间函数
--dateadd(在指定的日期上加上一些时间)
select dateadd(day,10,getdate())
--datediff(返回2个日期的时间间隔)
select datediff(day,'2009-06-7',getdate())
--datename(返回指定日期的日期部分的字符串)
select datename(month,getdate())
--datepart(返回指定日期的日期部分的整数)
select datepart(month,getdate())
--day,month,year(返回给定日期的天，月，年)
select day(getdate())
select month(getdate())
select year(getdate())
--getdate(返回当前时间)
----------*****************字符串函数
--ASCII(返回字符串最左边的字符的ASCII码值)
select ASCII('ad')
--charindex（返回表达式1在表达式2的位置)
select charindex('联','余联涛')
--left(返回从字符串左边开始指定个数的字符)
select left('fdfwedsfxzfwefdsf',3)
--right(返回从字符串右边开始指定个数的字符)
select right('ddffd',3)
--len(返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格)
select len('asaadasdass ')
--lower(将大写字符数据转换为小写字符数据后返回字符表达式)
select lower('DFfFsFdFfFsSEdF')
--upper(将小写字符数据转换为大写字符数据后返回字符表达式)
select upper('sDfFsGeEgCr')
--ltrim,rtrim(分别截断所有(开头)尾随空格后返回一个字符串)
select ltrim(' asaadasdass ')
select rtrim(' asaadasdass ')
--patindex（返回指定表达式中某模式第一次出现的起始位置；
--如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零）
select patindex('%我%','上的撒发的我是否为')
--replace(用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式)
select replace('上的撒发的我是否为','的','地')
--replicate(以指定的次数重复字符表达式)
select replicate('上的',2)
--reverse(反转)
select reverse('余联涛')
--stuff(删除指定长度的字符并在指定的起始点插入另一组字符)
select stuff('dhdnefg',2,3,'ffffffff')
--substring(返回字符、binary、text 或 image 表达式的一部分)
select substring('lakemfbc',2,5)
--soundex(返回由四个字符组成的代码 (SOUNDEX) 以评估两个字符串的相似性)
--difference(以整数返回两个字符表达式的 SOUNDEX 值之差)
select soundex('Green'),soundex('Greene'), difference('Green','Greene')
GO
--************case函数
--------------第一种写法
create table #yu
(
a int,
b int,
c nvarchar(100),
d as case a
when 1 then a
when 2 then b
else (a+b)
end
)
-------------第二种写法
create table #yu
(
a int,
b int,
c nvarchar(100),
d as case
when a=1 then a
when a=2 then b
else (a+b)
end
)
---***********waitfor(指定触发语句块、存储过程或事务执行的时间、时间间隔或事件)
begin
waitfor time '16:11' --执行开始等到16：11分才开始执行下面的语句
print 'OK'
end
begin
waitfor delay '00:00:02' --等到10秒后开始执行下面的
print 'OK'
end
---**********************提取前几条数据
select top 5 * from proinfo --提取前5条
select top 15 percent * from proinfo --提取记录的百分之15
set rowcount 4 --设置提取4条,在下个set rowcount之前一直有效
set rowcount 0 -- 关闭了设置
select * from proinfo
select * from proinfo order by id desc
--******汇总函数cube,rollup,compute,compute by
--举例
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
--其他几个不一一举例，在帮助文档里面有
----*********范围以外知识
--select ...into 表 (把查询后的结果添加到在查询中新建一张的表中)
select * into #yu from proinfo --执行完后，这里的#yu就是建立的新表
select * from #yu
drop table #yu
declare @m int
declare @n nvarchar(100)
set @m=5
set @n='select '+convert(nvarchar(20),@m)+'1'
exec(@n)
declare @m int
set @m=5
select @m+1
----随机函数
select rand() -- 返回一个随机数
select * from adminurl order by newid() --返回随机排列的集合,最好不要用在记录较多的表中
---实现多个数据库同步
--http://www.cnblogs.com/freeliver54/archive/2007/02/09/645514.html
--http://www.pconline.com.cn/pcjob/other/data/others/0512/733605.html
--步骤1： 企业管理器->选中一台server(以名为HUIQIN的server为例)->工具->复制->配置发布、订阅服务器和分发
--------->一步一步完成（这样会出现一个复制监视器）
--步骤2：选中指定的服务器->工具->复制->创建和管理发布->选择要创建出版物的数据库，然后单击[创建发布]->下一步到‘快照发布’为止->根据提示选择完成
--步骤2：选中指定的服务器->工具->复制->请求订阅-》下一步（统会提示检查SQL SERVER代理服务的运行状态）-》完成
select name from sysobjects where type='u'
--------------**************数据的导入与导出**************-----------------------------------
----------先把目录写下
--数据转换服务：导入导出工具
--数据转换服务（DTS）
--用DTS导入导出向导复制数据
--创建DTS包
--用数据转换服务（DTS）设计器复制数据库表
--执行数据驱动的查询任务
--执行大容量插入任务
--如何使用BCP和BULK INSERT
--如何优化大容量复制性能
--如何在DTS中使用ActiveX脚本
--在DTS包中使用全局变量
--如何使用查找查询
--如何从外部数据查询DTS包
------------------------*************数据库备份****************----------------------------------
--完全备份时对整个数据库的备份，占用时间和空间资源比较多
--差异备份只是备份和最近一次完全备份的不同的地方，时间和空间上占用较少
--日志备份，记录上次日志备份后的日志的备份，它能让系统回到过去的任何一个时刻，时间和空间占有较少
--1.完全备份数据库
BACKUP DATABASE mydb TO DISK ='C:\DBBACK\mydb.BAK'
--2.还原完全数据库
USE master
RESTORE DATABASE mydb FROM DISK='C:\DBBACK\mydb.BAK' WITH REPLACE
--注意：很多时候不能直接还原,因为数据不是独占打开.可能用到下面的过程
--Kill掉访问某个数据库的连接
CREATE PROC KillSpid
(@DBName varchar)
AS
BEGIN
DECLARE @SQL varchar
DECLARE @SPID int
SET @SQL='DECLARE CurrentID CURSOR FOR SELECT spid FROM sysprocesses WHERE dbid=db_id('''+@DBName+''') '
FETCH NEXT FROM CurrentID INTO @SPID
WHILE @@FETCH_STATUS <>-1
BEGIN
exec('KILL '+@SPID)
FETCH NEXT FROM CurrentID INTO @SPID
END
CLOSE CurrentID
DEALLOCATE CurrentID
END
--当kill掉用户后最好使用单用户操作数据库
exec SP_DBOPTION @DBName,'single user','true'
---在定时备份那里注意要启动代理服务器
---差异备份（必须先是备份了完全备份后才能进行差异备份）
backup database kk to disk='c:\kk.bak' with differential
restore database db_news from disk='c:\db_news.bak'
--在还原的时候先还原最近一次完全备份，然后在还原差异备份
---在定时备份那里注意要启动代理服务器
--日志备份
backup log db_news to disk='c:\db_news_log.bak' with format
GO
--日志还原
RESTORE DATABASE kk FROM DISK='c:\kk.bak' WITH REPLACE,NORECOVERY
--这里是恢复数据库，因为要还原日志必须先还原数据库或者差异数据还原,NORECOVERY很重要
RESTORE LOG kk FROM DISK='c:\kk.bak' WITH RECOVERY
GO
-------sqlserver只有MDF文件恢复数据库的方法
--在查询中执行下列语句
EXEC sp_attach_single_file_db @dbname = 'testdb', @physname = 'E:\Test\testdb.MDF'
--注:'testdb' 为恢复的数据库名
--'E:\Test\testdb.MDF' 为MDF文件的物理路径
----------******************用openXML处理XML文档的问题***********************--------------------
--更多的信息在查询分析器的帮助文档中（这里只简单介绍2种常用的方法【属性法】和【元素法】）
--http://www.mscto.com/Sql/2009010344417.html
--1.可以减少对数据库的频繁操作，比如在购物系统中，顾客选多种商品的时候，先把这些商品放入一个已经定义好
--的XML文档中去，在选完后提交数据库的时候用insert into 表 select ....from openxml......实现一次性提交多条
--数据，减少数据库的压力
--2.在这里我说个解决方案：现在服务器建立两个XML文档，一个放已经注册的用户在选购商品的时候添加进去的商品
--在提交的时候直接提交到该客户对应的数据库中去，另外一个记录未注册用户在选购商品时添加进去的XML文档（用户名就以时间为准），
--在用户提交订单的时候，把此信息提交到另外一个临时用户表中去（但是有个问题就是如果出现并发的时候怎么处理，这个还没有解决）
--但是这个可以用在顾客选购商品的时候可以提供一边选购，一边浏览自己已经选购的商品功能（其实用一个字符串也能记录）
declare @idoc int --定义下面在内存中的句柄
declare @doc varchar(1000) --接收XML字符串
set @doc='<ShoppingCart>
<Purchase ProductID="7" Price="10.00" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>
<Purchase ProductID="99" Price="25.00" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>
<Purchase ProductID="32" Price="12.00" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>
<Purchase ProductID="11" Price="90.00" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>
<Purchase ProductID="7" Price="50.00" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>
<Purchase ProductID="8" Price="67.35" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>
<Purchase ProductID="45" Price="29.99" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>
<Purchase ProductID="54" Price="49.49" SaleDate="10/11/2006" SaleBatchID = "4523" CustomerID = "2398"/>
</ShoppingCart>'
exec sp_xml_preparedocument @idoc output,@doc --在内存中建立行列集，输出句柄@idoc
--查询
SELECT ProductID,Price,SaleDate,SaleBatchID,CustomerID
FROM
OPENXML (@idoc,'/ShoppingCart/Purchase') --开始调用，@idoc是内存中句柄，后面那个参数是XPATH表达式
WITH
(
ProductID INT,
Price MONEY,
SaleDate SMALLDATETIME,
SaleBatchID INT,
CustomerID INT
)
EXEC sp_xml_removedocument @idoc --删除内存中的句柄
------------------实例2(可以根据../的方法调节前后元素的关系，达到符合的标准)
declare @idoc int
declare @doc varchar(1000)
set @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
exec sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity',
ContactName varchar(20) 'http://www.cnblogs.com/@ContactName')
EXEC sp_xml_removedocument @idoc
--------------实例3
--如果在 flags 设置为 2 时（表示 element-centric 映射）如果在 XML 文档中，
--<CustomerID> 和 <ContactName> 是子元素，则 element-centric 映射将检索值。
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer>
<CustomerID>VINET</CustomerID>
<ContactName>Paul Henriot</ContactName>
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer>
<CustomerID>LILAS</CustomerID>
<ContactName>Carlos Gonzlez</ContactName>
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',2)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @idoc
----------实例4
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
EXEC sp_xml_removedocument @idoc
----实例5
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" >
<ContactName>Paul Henriot</ContactName>
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" >
<ContactName>Carlos Gonzlez</ContactName>
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',3)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @idoc
浙公网安备 33010602011771号