/*
/*
数据库总结：
--查询，是操作中的重点及精华部分，由于业务的多样性，导致查询方案的多样性，但是已有的关系必须通过各种手段在查询中予以表达
--常见查询手段
/*
1.普通查询
select 结果集列表表达式
from 值域范围
where 结果集筛选表达式
group by 统计结果集分组条件表达式
order by 结果集排序条件表达式
2.子查询(在查询内部嵌套其它辅助查询的定义，例如：其它查询作为计算列、值域、条件等)
3.联合查询(表联接join table)
inner join内联接----重点，后续的外联接只在特殊数据情况下使用
left outer join左外联接
right outer join右外联接
full outer join全外联接
4.SQL编程查询，通过编写具有执行逻辑的多行代码来获取希望得到的查询结果或操作结果
4.1---View视图
4.2---UDF(User Defined Function)用户定义函数
4.3---SP(Stored Procedure)存储过程
4.4---Trigger触发器
4.5---Cursor游标
4.6---Transaction事务
*/
---------------------------------------------------------------------
常见的基本语句：
select 字段 from 表名 where 条件
insert into 表名(字段) values(内容)
update 表名 set 字段=新内容 where 条件
drop from 表名
truncate table 表名--整表清空，不写日志，速度极快但很危险
--我们通常通过主键来删除内容
---------------------------------------------------------------------
对表格进行处理：
1.创建表格
create table 表名
create table ProdutPriceAndStock--表与表之间的1对1关系
(
productid int not null primary key foreign key references Product(productid),
--产品标示号，既当主键有当外键体现了一对一的关系
price money not null,
productcount int not null--产品当前库存数量
)
2.删除表格
drop table 表名
3.增加表格字段列
alter table 表名
add 字段列名 字段类型
4.删除表格字段列
alter table 表名
drop column 列名
5.修改表格字段列属性
alter table 列名
alter column 列名 字段属性 约束
6.删除表格字段列约束
alter table 表名
drop constraint 约束名
7.修改表格字段列约束
alter table 表名
alter column 列名 字段属性 约束 default 0 for 列名
---------------------------------------------------------------------
程序段编程
--程序三要素：定义数据容器、赋值、使用
begin--程序段的开始
-----------定义
--declare @局部变量名称 数据类型
--特殊定义 --表数据类型
declare @infotable table
(
username nvarchar(20),
userpwd nvarchar(50)
)
-----------赋值
set @num=5 ---使用常量来赋值
set @num=(select count(accountid) from AccountInfo)---使用子查询来赋值
select @num=30,@str='hello',@isgood=1,@onechar='X'--同时对多个数据容器赋值，往往针对表中不同字段来赋值
--针对表类型的变量，必须使用操作表数据的语句来赋值
-----------使用
--在服务器端的控制台中输出，笑话ijiyunxing是无人可以看到作为重要的调试手段
--通过简单程序来完成运算及简单的业务逻辑
--大兔3元每只，小兔一元三只，百元买白兔
begin
declare @bigcount int,@smallcount int
declare @infotable table
(
大兔数量 int,
小兔数量 int
)
set @bigcount=1
set @smallcount=100-@bigcount
while(@bigcount<=100)
begin
if((@bigcount*3+@smallcount/3)=100)
begin
print '祝贺你找到答案了'
print '大兔：'+Convert(nvarchar(10),@bigcount)+'只'
print '小兔：'+Convert(nvarchar(10),@smallcount)+'只'
insert into @infotable(大兔数量,小兔数量) values(@bigcount,@smallcount)
break--跳出循环
end
else
begin
print 'bigcount='+Convert(nvarchar(20),@bigcount)+',非答案'
end
set @bigcount=@bigcount+1
set @smallcount=100-@bigcount
end
select * from @infotable
end
go
---------------------------------------------------------------------
建立视图
目的：创建视图其主要目的是为了查询使用
create view 视图名
as
select .........
单表视图
多表视图---主要考查表的链接
inner join内联接----重点，后续的外联接只在特殊数据情况下使用
left outer join左外联接
right outer join右外联接
full outer join全外联接
--交叉联接
select * from Category as c cross join Product as p
go
--内连接inner join----on，寻找有效地数据对应项
select * from Category as c, Product as p
where c.categoryid=p.p_c_id
go
--
select categoryname as 分类名称 ,count(productid) as 分类数量
from Category as c ,Product as p
where c.categoryid=p.p_c_id
group by categoryname
go
select * from Category as c
--full outer全外联接
--left outer左
--right outer右
join Product as p
on c.categoryid=p.p_c_id
go
---------------------------------------------------------------------
--UDF user defined function 用户自定义函数
--UDF--标量函数，内嵌表值函数，多语句标志函数
--可以接受参数，完成各种常见的查询任务，根据任务结果的不同性质来合理选用不同的函数方式
--标量函数：查询结果为独立的标量值，例如：数字。文字
--我们希望了解任何一个商品分类的目前产品数量
--可变量：产品分类名称
--查询目标：产品数量---int--标量------------------FN
create function QueryProductCountByCategory---函数名
(@Categoryname nvarchar(10))---形参字段
returns int-----返回值
with encryption---加密
as
begin
declare @count int --返回变量值
declare @categoryid nvarchar(15)
set @categoryid=(select categoryid from Category where categoryname=@categoryname)
if(@categoryid is null)
begin
set @count=-1--利用无效的返回值来表示参数有误
end
else
begin
set @count=(select count(productid)from Product where p_c_id=@categoryid)
--得到大于等于0的有效结果值
end
return @count
end
go
-如果我们查询的目标是一个可以直接得到的结果集表格
--内嵌表值函数------------------------------------IN
--我们希望了解任何一个产品分类的所有产品信息，何解
create function QueryProductInfoByCategory---函数名
(@categoryname nvarchar(10))---形参字段
returns table
with encryption
as
return
(select * from ProductInfoView where 分类名=@categoryname)
go
--如果我们发现有时候要得到的结果不方便进行直接查询得到，我们可以将查询过程分散，
使用多于具有标志函数来完成全过程
--使用多语句表值函数------------------------------TF
--Multistatement Table_valued Functions
--我们想要知道目前的有效用户数量，何解
create function QueryAccountInfoByEnable()
returns @infotable table
(数量分类 nvarchar(20),本类数量 int)
with encryption
as
begin
declare @enablecount int,@disablecount int
set @enablecount =(select count(accountid)from AccountInfo where [enable]=1)
set @disablecount=(select count(accountid)from AccountInfo where [enable]=0)
insert into @infotable(数量分类,本类数量)
values('有效用户',@enablecount)
insert into @infotable(数量分类,本类数量)
values('无效用户',@disablecount)
return
end
go
---------------------------------------------------------------------
--创建存储过程，接受参数，完成合理的执行过程，执行过程默认可以不提供返回值，默认等效于void方法
--------------SP
create procedure RegAccountInfo
@变量名 变量类型(实参)
with encryption
as
begin
..................
end
go
execute SP名 (形参)
go
---------------------------------------------------------------------
--触发器：一种特殊的存储过程，满足某些对数据的发生变化的操作条件时会自动执行，
--触发器的设计者需要编写合理的完整代码以表示自动执行的逻辑达到维护数据完整性的要求
1.触发器是绑定在一个表中的
2.触发器的执行条件为：所绑定的表中数据发生了变化，而不是查询
3.常见的触发器的执行时机为：改变的代码在缓存中产生执行效果之后触发
4.触发过程中有两个内部辅助表：inserted与deleted,分别表示本次操作将会加入的新数据及操作中
可能被删除的就数据，任何一次update操作都会同时导致inserted,delected发生改变，着两个表格
的格式与本表完全一致
create trigger AddSaleScoreTrigger
on SaleLog --on后面要写的是绑定表，绑定表的变化导致触发的执行
for insert --描述触发的前提
as
begin
declare @buycount int ,@buyprice money,@buyaccount nvarchar(20)
declare @oldscore money,@newscore money
select @buycount=salecount,
@buyprice=saleprice,
@buyaccount=accountid
from inserted --从最新变化的内部数据表中获取关键数据
set @oldscore=(select buyscore from AccountInfo where accountid=@buyaccount)
set @newscore=@oldscore+@buycount*@buyprice
update AccountInfo set buyscore=@newscore
where accountid=@buyaccount
end
go
---------------------------------------------------------------------------------------
/*
游标：针对指定的数据范围，可以进行分行的依次操作，操作次数取决于范围的容量
fetch--取得某行的操作权
fetch next--取得下一行的操作权
fetch_status--取得操作权的结果之状态返回值，0表示成功，非0表示失败，
例如达到了数据范围的结尾等均会导致失败
事务：一系列操作的集合，具有整体全部成功或全部失败的特点。必须同时具备ACID四大基本属性
A原子性(Atomicity)本系列操作的目标是完成独立的任务，所有操作内容为了本任务，不可以分割--复制文件中途取消
C一致性(Consistentency)在操作目标中，而偶们得到的结果与操作描述的结果必须保持一致，数据处理过程中不得有丢失或扭曲
I隔离性(Isolation)在多线程环境中，事务执行过程与其他事务执行过程相互分离的
D持久性(Durability)事务操作完毕之后，如果提交成功，则其产生的影响必须持久保持
隐形事务：常见的所有SQL语句均会导致自动生成隐形事务，以保证某一条语句的执行效果完整性。例如：insert into''''
显示事务：通过明确的关键语句代码，指定事务执行范围，在范围之内的一列写执行代码受到自定义事务的控制，
可以明确的对本事务的执行开始、保存、回滚、提交、验证等操作
begin transaction--启动事务，显示事务开始控制
rollback transaction--回滚事务，整个事务会抹除
commit transation --提交事务，确认本事务有效，并尝试保证其持久性
@@error--针对事务过程中的任何一条执行，监测器执行过程是否有误
*/
use MyDB
GO
--假设有两个由于历史原因造成的用户数据表需要归并，
create table UserInfoOne
(
userid nvarchar(20) not null primary key,
userpwd nvarchar(30) not null
)
go
create table UserInfoTwo
(
userid nvarchar(20) not null primary key,
userpwd nvarchar(30) not null
)
go
delete from UserInfoOne
delete from UserInfoTwo
go
insert into UserInfoOne(userid,userpwd) values ('mike','mike2122')
insert into UserInfoOne(userid,userpwd) values ('jie','jie2122')
insert into UserInfoOne(userid,userpwd) values ('hong','hong2122')
go
insert into UserInfoTwo(userid,userpwd) values ('mike','mike2122')
insert into UserInfoTwo(userid,userpwd) values ('xiao','xiao2122')
insert into UserInfoTwo(userid,userpwd) values ('yun','yun2122')
go
select * from UserInfoOne
select* from UserInfoTwo
go
/*
在存储过程或触发器中使用 Transact-SQL 游标的典型过程为：
声明 Transact-SQL 变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够大的变量来保存列返回的值，并声明变量的类型为可从列数据类型隐式转换得到的数据类型。
使用 DECLARE CURSOR 语句将 Transact-SQL 游标与 SELECT 语句相关联。另外，DECLARE CURSOR 语句还定义游标的特性，例如游标名称以及游标是只读还是只进。
使用 OPEN 语句执行 SELECT 语句并填充游标。
使用 FETCH INTO 语句提取单个行，并将每列中的数据移至指定的变量中。然后，其他 Transact-SQL 语句可以引用那些变量来访问提取的数据值。Transact-SQL 游标不支持提取行块。
使用 CLOSE 语句结束游标的使用。关闭游标可以释放某些资源，例如游标结果集及其对当前行的锁定，但如果重新发出一个 OPEN 语句，则该游标结构仍可用于处理。由于游标仍然存在，此时还不能重新使用该游标的名称。DEALLOCATE 语句则完全释放分配给游标的资源，包括游标名称。释放游标后，必须使用 DECLARE 语句来重新生成游标。
*/
--假设操作目标：userinfotwo->userinfoone
--使用游标分行处理
begin
--声明游标
declare MoveUserInfoTwo cursor
for
select userid,userpwd from UserInfoTwo
--打开游标，准备开始读取操作
open MoveUserInfoTwo
--执行读取
declare @userid varchar(20) ,@userpwd nvarchar(30)
fetch next from MoveUseRInfoTwo into @userid,@userpwd
while(@@fetch_status=0)
begin
insert into UserInfoOne(userid,userpwd) values(@userid,@userpwd)
fetch next from MoveUserInfoTwo into @userid,@userpwd
end
--完成游标操作，关闭游标
close MoveUserInfoTwo
--释放游标
deallocate MoveUserInfoTwo
end
go
--游标结合事务操作
begin transaction MoveUserInfoTrans
begin
declare @errorcount int
set @errorcount=0
--声明游标
declare MoveUserInfoTwo cursor
for
select userid, userpwd from UserInfoTwo
--打开游标
open MoveUserInfoTwo
--执行读取
declare @userid nvarchar(20),@userpwd nvarchar(30)
fetch next from MoveUserInfoTwo into @userid,@userpwd
while(@@fetch_status=0)
begin
insert into UserInfoOne(userid,userpwd) values(@userid,@userpwd)
if(@@error!=0)
begin
set @errorcount=@errorcount+1
break
end
fetch next from MoveUserInfoTwo into @userid,@userpwd
end
--完成游标操作关闭游标
close MoveUserInfoTwo
--释放游标
deallocate MoveUserInfoTwo
--验证本次事务的操作过程
if(@errorcount=0)
begin
commit transaction MoveUserInfoTrans
print'事务提交成功'
end
else
begin
rollback transaction MoveUserInfoTrans
print'执行过程有误，事务已回滚'
end
end
go
select * from UserInfoOne
