SQL Server 2005

--创建从表
select distinct party_id,companytype,company,paddr,tel,fax,zip,email,pperson into info from hgdata
select distinct hs_id,hs into table_hs from hgdata
select distinct party_id,hs_id into table_party from hgdata
select distinct party_id,company into table_company from hgdata
/*第二章 视图和索引*/
--创建索引
create index info_index on info(party_id)
create index table_hs_index on table_hs(hs_id)
create index company_index on table_company(company)
create index party_index on table_party(party_id)
--查询从表数据
select company from info
select hs from table_hs
select company from table_company
select party_id from table_party
--根据从表关系查询公司信息
select * from info where party_id in(select party_id from table_company where company like '%塑料%')
select * from info where party_id in(select party_id from table_party where hs_id in(select hs_id from table_hs where hs like'%其%'))
/*第三章 游标 事务 锁*/
--创建游标
declare cur cursor --声明游标名称
for
select party_id,company from table_company where company like '%北京%'
open cur --打开游标
declare @party_id char(20),@company char(100) --声明查询列的变量
fetch next from cur into @party_id,@company  --从游标中提取数据fetch
while @@fetch_status=0 --@@fetch_status=0时，说明fetch语句成功提取数据
begin
 print(@party_id + isnull(@company,'不详'))  --打印
 fetch next from cur into @party_id,@company  --从游标中循环提取数据fetch
end
close cur --关闭游标
deallocate cur --释放游标
go
--事务处理实例
use B
create table account
(
 accid int,  /*编号*/
 [type] varchar(10), /*类型*/
 balance decimal(10,2) /*余额*/
)
insert into account values(33,'saving',3000)
insert into account values(33,'checking',1500)
select * from account
--编写T-SQL语句块，从账号为33的储蓄账户取出1000转入该帐户的支票账户
declare @accid int,@num decimal(10,2)
set @accid=33
set @num=1000
begin try
 begin tran
  update account set balance=balance-@num where accid=@accid and type='saving'
  update account set balance=balance+@num where accid=@accid and type='checking'
 commit tran
end try
begin catch
 rollback tran
end catch
go
/*第四章 存储过程*/
--创建存储过程,编写T-SQL语句块，从账号为33的储蓄账户取出1000转入该帐户的支票账户
create procedure savingTochecking
@accid int,
@num decimal(10,2) --指定转账金额
as
declare @bal decimal(10,2)
begin try
 select @bal=balance from account where accid=@accid and type='saving'
 if @num>@bal --如果取出金额大于账上余额
  return 1 --直接返回状态码1
 begin tran
 update account set balance=balance-@num where accid=@accid and type='saving'--取出金额
 update account set balance=balance+@num where accid=@accid and type='checking'--转入金额
 commit tran --提交事务
 return 0 --事务提交成功，返回0
end try
begin catch
  rollback tran --事务回滚
  return 2  --发生错误，返回2
end catch
go
--执行存储过程savingTochecking
use B
declare @retcode int
begin
 execute @retcode=savingTochecking 33,1500
 if @retcode=0
  print'转账成功！'
 else if @retcode=1
  print'转账金额大于帐户余额，转账失败！'
 else
  print'数据库发生错误，转账失败！'
end
go
--创建存储过程(插入数据)
use B
select * from users
create procedure adduser
 @loginname varchar(20),
 @username varchar(20),
 @password varchar(20),
 @age int,
 @address varchar(50)
as
begin try
 begin tran
  insert into users(loginname,username,password,age,address) values(@loginname,@username,@password,@age,@address)
  commit tran
end try
begin catch
 rollback tran
end catch
go
use b
adduser 'qkx','qkx','111',21,'石家庄'
--创建存储过程(查询)
use cndata
create procedure search
@company varchar(200)
as
declare @temSQL varchar(2000)
 set @temSQL='select * from info where company like ''%'+@company+'%'''
execute(@temSQL)
go
search '北京'
--创建存储过程(通用方法)
create procedure searches
@tablecolumn varchar(200),--声明列名
@tablename varchar(20),--声明表名
@tablewhere varchar(100),--声明where条件
@orderby varchar(100),--声明排序字段
@ordertype int=0--声明排序方式，0为升序，非0为降序
as
declare @temSQL varchar(2000)
 set @temSQL='select '+@tablecolumn+' from '+@tablename+' '
 if @tablewhere!=''
  begin
   set @temSQL=@temSQL+'where '+@tablewhere
  end
 if @orderby!=''
  begin
   if @ordertype=0
    set @temSQL=@temSQL+' order by '+@orderby+' asc'
   else
    set @temSQL=@temSQL+' order by '+@orderby+' desc'
  end
execute(@temSQL)
go
use cndata
searches 'party_id,company','info','company like ''%北京%''','paddr',0
searches '列名1,列名2···','表名','列名 like ''(两个''代表一个')%关键字%''(两个''代表一个')'，'排序字段(列)',0或非0(0为升序,非0为降序)
--查看存储过程
sp_helptext 'searches'
/*第五章 触发器*/
--创建触发器(简单)
use B
select * from users
create trigger users_trigger on users
for delete
as
select * from deleted
print'插入成功!'
go
adduser '小Q','爱上','11',19,'腾讯'
delete from users where id=3
--专业类别表
create table major(
 majorId int not null identity(1,1) primary key, --专业编号
 majorName varchar(50) not null --专业名称
)
--学生表
create table student(
 stuId char(6) not null primary key, --学号
 stuName varchar(20) not null, --学生姓名
 majorId int, --专业编号
 sex char(2) not null default'男', --性别
 credit int,   --总学分
 remark varchar(100), --备注
 constraint fk_majorId foreign key(majorId) references major(majorId) --外键
)
--专业状态表
create table major_stat(
 majorName varchar(50), --专业名称
 totStu int, --专业学生人数
 totCredit int --专业总学分
)
--创建触发器
use B
create trigger update_major_stat
on student
for insert,delete,update
as
begin
 delete from major_stat --先删除major_stat表中所有记录
 insert into major_stat --按专业分组统计学生人数和总积分，然后将统计结果插入到表中
 select majorName,count(*),sum(credit) from student s inner join major m on s.majorId=m.majorId group by majorName
end
--对表进行操作
select * from major
select * from major_stat
select * from student
insert into major values('管理')
insert into student values('A0009','Jane','3','女',68,null)
update student set credit=credit+2 where majorId=1
--查询触发器
sp_helptext 'update_major_stat'
--课程表
create table course(
 couId char(3)not null primary key,--课程编号
 couName varchar(20) not null,--课程名称
 couHour int not null,--课时
 couCredit int--学分
)
--学生选课表
create table stu_course(
 stuId char(6) not null primary key,--学生编号
 couId char(3) not null,--课程编号
 stuName varchar(20) not null,--学生姓名
 score decimal(4,1),--成绩
 constraint fk_couId foreign key(couId)references course(couId)
)
select * from course
select * from stu_course
insert into course values('001','软件',8,6)
insert into stu_course values('A0001','001','Alex',89)
--创建Insert触发器
create trigger insert_trigger
on stu_course
for insert
as
if(select score from inserted)>=60
begin
 --声明触发器中使用的变量
 declare @stuid char(6),@couid char(3),@coucredit int
 --从inserted表中找到学生编号课程编号，存放到变量中
 select @stuid=stuid,@couid=couid from inserted
 --从课程表中根据课程编号找到该门课程的学分
 select @coucredit=coucredit from course where couid=@couid
 --在学生表中根据学生编号为学生添加分数
 update stu_course set score=score+@coucredit where stuid=@stuid
end
--测试insert触发器的效果
select * from stu_course where stuid='A0002'
select * from course where couid=001
--插入数据
insert into stu_course values('A0002','001','sa',70)
select * from stu_course where stuid='A0002'--结果为sa添加了6分，即coucredit的值，也就是触发器中score=score+@coucredit
--创建update触发器
alter trigger updateCouhour
on course
for update
as
declare @oldnum int,@newnum int
begin
 --从deleted表中找出旧的课时数
 select @oldnum=couhour from deleted
 --inserted表中找到更新的课时数
 select @newnum=couhour from inserted
 --如果更新的课时数超出了规定范围，通过rollback语句将原update语句回退
 if @newnum>@oldnum or @newnum<50
 begin
  print'课时数不能高于原课时数并且不能低于50'
  rollback tran
 end
end
select * from course
--测试update触发器的效果
update course set couhour=couhour+2 where couid=001
--列级update触发器
create trigger noupdate
on course
for update
as
if update(coucredit)
begin
 print'不能修改coucredit列'
 rollback tran
end
--测试列级update触发器
update course set coucredit=10 where couId='001'
--delete触发器
create trigger delmajor
on major
for delete
as
if(select majorid from deleted)=1
begin
 print'不能删除软件专业!'
 rollback tran
end
--测试delete触发器
--首先删除外键drop constraint fk_majorId，才能测试delete触发器
delete from major where majorid=1
select * from users
--delete触发器的另一个作用是：备份数据，它可以将删除的数据备份到另一个表中
/*复制users表结构,where条件表示将源表中满足条件的记录复制到目标中。
由于1=2不成立，所以该语句只复制表结构，而不复制任何记录*/
select * into quit_users from student where 1=2
--查询备份数据
select * from quit_users
--创建delete触发器
if exists(select name from sys.all_objects where name='DelUserBackUp' and type='TR')
 drop trigger DelUserBackUp
go
create trigger DelUserBackUp
on student
for delete
as
insert into quit_users select * from deleted
--测试创建delete触发器
delete from student where stuid='A0008'
--查询
select * from student
select * from quit_users
/*创建inserted of触发器,inserted of 触发器只执行触发器中的操作语句，
而不执行触发器的SQL语句，从而替代原始的SQL语句的造作*/
if exists(select name from sys.all_objects where name='DelUserBackUp'and type='TR')
 drop trigger DelUserBackUp
go
create trigger DelUserBackUp
on student
instead of delete --声明instead of触发器
as
declare @stuid char(6)
begin try
 --从 deleted表中找到将要删除的学生编号
 select @stuid=stuid from deleted
 --删除该学生的所有成绩记录
 delete from stu_course where stuid=@stuid --有错，stu_course表跟studentb表无关，所以执行结果不正确
 --删除学生记录
 delete from student where stuid=@stuid
 --将删除学生的记录备份到quit_users表
 insert into quit_users select * from deleted
 commit tran --提交事务
 print'操作成功!'
end try
begin catch
 rollback tran --回退事务
end catch
--测试inserted of触发器
set nocount on --不显示影响行数的信息
go
delete from student where stuid='A0003'
--查询
select * from student
select * from quit_users

--雇员表
create table employee(
 empid char(5) primary key, --员工编号
 [name] varchar(20), --员工姓名
 age int --员工年龄
)
--员工任职历史表
create table jobs(
 id int identity(1,1)primary key, --学号
 empid char(5), --员工编号
 job varchar(30), --职务
 salary money, --工资
 -- 可写可不写constraint fk_empid
 foreign key(empid)references employee(empid) --外键
)
--查询
select * from employee
select * from jobs
--创建两个表连接的视图
create view emp_job
as
select e.empid,e.name,e.age,j.job,j.salary from employee e inner join jobs j on e.empid=j.empid
go

select * from emp_job
--向视图中添加数据
insert into emp_job values('A0001','Alex',21,'程序员',2000)
/*肯定无法插入数据，
此时，instead of 触发器可以解决这个问题，
将对试图的插入操作替换成对多个基表的插入操作。*/
--代码如下：
--创建 instead of 触发器
if exists(select name from sys.all_objects where name='insert_emp_job' and type='TR')
 drop trigger insert_emp_job
go
create trigger insert_emp_job
on emp_job
instead of insert
as
insert into employee select empid,name,age from inserted
insert into jobs select empid,job,salary from inserted
go
--测试instead of 触发器
insert into emp_job values('A0002','Kevin',20,'运动员',15000000)
select * from employee
select * from jobs
select * from emp_job
--查询触发器
sp_helptext 'insert_emp_job'
--关闭触发器(view)视图,目前视图不能进行开启和关闭触发器
alter view emp_job
disable trigger insert_emp_job
--开启触发器(view)视图
alter view emp_job
enable tigger insert_emp_job
--关闭触发器(table)表
alter table student
disable trigger DelUserBackUp
--开启触发器(table)表
alter table student
enable trigger DelUserBackUp
/*第六章 XML操作*/
--RAW 模式
/*结果集的每个记录都转换成叫做<raw>的XML元素，所检索的每一列都将表示为一个属性*/
select * from student for xml raw
--AUTO 模式
/*结果集每个记录都转换成以from子句中的元素来命名的XML元素，
所检索的每一列都将表示一个属性*/
select * from student for xml auto
--EXPLICIT 模式
/*为格式化XML提供许多控制，不过，EXPLICIT模式的使用语法要复杂的多，
XSLT是一个比较常用的XML装换方式*/
--1.使用ELEMENTS选项
select * from student for xml auto,elements
--2.制定根元素
select * from student for xml auto,elements,root('info')
--3.包含XMLSchema
select * from student for xml auto,xmlschema
/*3.将子查询的结果以XML格式内嵌在外层查询中，
通过Type描述语法可以将查询的结果以XML数据类型而不是varchar类型返回*/

/*查询,通过XPath描述返回的XML文件结构*/
select stuid as "@id",--属性
 stuname as "*",--文本或内容
 sex as "sex/text()",--子元素，文本
 credit as "credit/text()",--子元素，文本
 remark as "remark/text()"--子元素，文本
from student
for xml path,root('student')
/*openxml函数，是一个T-SQL函数，它能够从XML流构建关系行集合，它是使用某种形式的XPath*/
--author表
create table author(
 auid int primary key,
 aname varchar(20),
 tel varchar(15),
 address varchar(50)
)
declare @xml xml
declare @doc int
set @xml='
 <author>
  <auid>101</auid>
  <aname>Alex</aname>
  <tel>222255</tel>
  <address>石家庄</address>
 </author>'
--使用存储过程读取XML文本，在内存中创建XMLDOM，并返回DOM的引用
exec sp_xml_preparedocument @doc output,@xml
--使用openxml函数打开dom，并从中获取数据，插入到author表中
insert into author
select auid,aname,tel,address from openxml(@doc,'/author',2)
/*with子句用于指定要从该XML文档获取的指定以及要转换的数据类型，
with也可用于在XML中用XPath表达式来映射属性或元素，或用于为要用于某一查询的XML字段取别名。*/
with(auid int, aname varchar(20),tel varchar(15),address varchar(50))--转换为与表中的类型
--使用系统存储过程将内存中的XMLDOC删除
exec sp_xml_removedocument @doc
--查询author表
select * from author
/*定义xml类型的字段*/
create table report(
 rid int identity(1,1) primary key,
 comment xml --XML类型的字段
)
insert into report values('<comment>数据已存入</comment>')--well-formed
select * from report
/*在文本类型与XML类型之间可以进行隐式或显示转换*/
declare @xml xml,@str nvarchar(1000)
set @str=
N'<台词>
 <演员 id="1">落花有意</演员>
 <演员 id="2">流水无情</演员>
  </台词>'
set @xml = @str --隐式转换
insert into report values(@xml)

set @xml = cast(@str as xml)--显示转换，也可用covert函数进行转换
select @xml
/*xml索引*/
set arithabort on
go
--由于在report表中已有主键聚集索引，所以可以为XML结构的数据字段创建XML索引
--创建主键索引
create primary xml index xmlindex_comment on report(comment)
--查询索引是否存在
select * from sys.indexes where name='xmlindex_comment' and object_id = object_id('report'/*表名*/)
--当在查询中使用XQuery
select rid,comment.query('/台词/演员') as reuslt from report
select * from report
--在创建主键索引后，可以在主键索引之上在创建三种索引：Path索引，Property索引和value索引
--创建Path索引
create xml index  xmlindex_comment_path--Path索引名
on report(comment)
using xml index xmlindex_comment--主键索引名
for path
--通过Alter命令重建索引
alter index xmlindex_comment on report rebuild
--修改索引设置选项
alter index xmlindex_comment on report set(allow_row_locks=on)
--删除索引
drop index xmlindex_commnet on report
/*XQuery的应用*/
--project表
create table project(
 pid int identity(1,1) primary key,
 data xml,
 date datetime,
 title nvarchar(20)
)
--插入数据
insert into project values(N'
 <程序员列表>
  <程序员 编号="3011">
   <姓名>韦小宝</姓名>
   <技术>Java</技术>
   <技术>Oracle</技术>
   <电子邮箱>[e-mail]</电子邮箱>
  </程序员>
   <程序员 编号="3048">
    <姓名>小龙女</姓名>
    <技术>JSP</技术>
    <电子邮箱>[e-mail]</电子邮箱>
  </程序员>
 </程序员列表>',
 '2008-12-12','电子商务平台'
)
select * from project
/*查询XML文档的几种方法*/
--1./程序员列表/程序员:直接从根元素开始，查找<程序员列表>下所有的<程序员>子元素
select data.query('/程序员列表/程序员')as programmers from project
--2.(/程序员列表/程序员)[1]:获取根元素<程序员列表>下的<程序员>子元素集合中的第一个<程序员>子元素
select data.query('/程序员列表/程序员[1]') as programmers from project
--3.(/程序员列表/程序员/@编号)[1]: 获取根元素<程序员列表>下的第一个<程序员>子元素的"编号"属性
select data.value('(/程序员列表/程序员/@编号)[1]','int')as programmerID from project
--4./程序员列表/程序员[@编号="3048"]:获取根元素<程序员列表>下所有的"编号"属性值为3048的<程序员>子元素
select data.query('/程序员列表/程序员[@编号="3048"]') as com from project
/*FLWOR(缩写):for,let,where,order by,return*/
declare @xml xml
set @xml=''--必须给变量赋值,否则不会执行其后的query运算
select @xml.query('<hello>大煞风景</hello>')

/*XQuery的组成语法有两个最重要的部分，一是：XPath路径查询描述，
二是:W3C所定义的循环操作选取数据的法则：总共有for,let,where,order by,return等关键字,
字母首字母合为FLWOR*/
--支持let语法是用来赋予变量值
let $a :="hello"
return $a
--for循环和return返回值
declare @xml xml
set @xml=''
select @xml.query(N'
 <循环>
 {
  for $i in(1,2,3)
  return $i
 }
 </循环>
')as 爱上
--通过for创建双循环，并以cancat函数连接字符串作为返回值
select data.query(N'
<循环>
{
 for $i in
  for $j in(1,2,3)
  return $j+1
 return
  if($i!=4) then
   concat(string($i),",")
  else
   $i
}
</循环>
')as loop from project

--在表project的字段内容，在for循环内加上where条件，并参照data字段的数据，取出<技术>子元素个数大于1的<程序员>元素
select data.query(N'
 for $i in (/程序员列表/程序员)where count($i/技术)>1
 return $i
')from project
--按"编号"的大小排序,降序(descending),升序(ascending)
select data.query(N'
 for $i in (/程序员列表/程序员)order by($i/@编号)descending
 return
 <pro id="{string($i/@编号)}" name="{$i/姓名}"/>
')from project
--SQuery 条件运算,与一般的语言类似，提供了if then else 条件运算
select data.query(N'
 for $i in(/程序员列表/程序员)
 return
 if($i/技术[2])
 then
  <程序员 姓名="{$i/姓名}" 技术="{$i/技术}" 技术数 ="两门以上"/>
 else
  <程序员 姓名="{$i/姓名}" 技术="{$i/技术}" 技术数 ="一门"/>
')from project
select * from project
/*value:从XML文件中返回单一值。value有两个参数，一是定义查询单一节点的XPath路径，
另一个是指定返回值的T-SQL数据类型。*/
select data.value(N'(//姓名)[1]','varchar(1000)') as pro_name from project
/*exist:如果XQuery语法的执行结果返回至少一个以上的XML节点，则exist函数运算结果返回1，
若没有符号的节点则返回0，若exist函数执行所针对XML数据类型的变量或字段是null，则返回null*/
select * from project where data exist(N'/程序员列表/程序员[姓名="小龙女"]')=1
/*modify:用来执行微软自行扩展W3CXquery的语法所加入的XMLDML。
insert:在一段XML语法中插入另一段XML，
插入的可以是元素，属性，说明，processing instruction，CDATA，text*/
update project set data.modify(N'
insert
<程序员 编号="3182">
 <姓名>杨过</姓名>
 <技术>Ajax</技术>
 <电子邮箱>[e-mail]</电子邮箱>
</程序员>
as last
into(/程序员列表)[1]
')where pid=1
--查询
select data from project
/*nodes:可以将XML数据内容分离后，改以关系数据表的XML数据字段显示。
也就是将XML节点内容对应成单一字段。*/
/*XQuery操作符：
一般操作符：加(+),减(-),乘(*),除(/)，
布尔操作符：true,false
通用比较操作符：等于(=),不等于(!=),小于(<),大于(>),大于等于(>=),小于等于(<=)
值比较操作符：等于(eq),不等于(ne),小于(lt),大于(gt),小于等于(le),大于等于(ge)
节点比较操作符：is，操作符两边等，返回TRUE，不等返回FALSE
节点顺序操作符：左边的操作数位置是否在右边的操作符之前(>>),
左边的操作数位置是否在右边的操作符之后(<<)*/