连接查询、存储过程、游标、触发器

use master

go

--判断GMcre数据库是否存在，如存在则删除

if exists(select*from sysdatabases where name='GMcre') drop database GMcre

go

create database GMcre

go

use GMcre

go

--书籍信息表

create table Book

(

Bid int primary key identity(0,1),

Bname varchar(20),--书记名称

Bprice Money,--书籍价格

BfaXingDate datetime default(datename(yy,getdate()))--书籍发行日期

)

--作者信息表

create table Author

(

Aid int primary key identity(0,1),

Aname varchar(8),--作者姓名

Bname varchar(20),--著作书籍

Asex char(2),--作者性别

Bid int references Book(Bid)--外键列

)

insert into Book values('小兵传奇',14.7,getdate())

insert into Book values('二战之风云',18,'1878-12-09')

insert into Book values('纳粹奋斗史',29,getdate())

insert into Author values('笨徒弟','玲玲','女',0)

insert into Author values('败天','二战之风云','男',1)

insert into Author values('沧月','纳粹奋斗史','女',2)

insert into Author values('司马光','资治通鉴','男',0)

insert into Author values('司马迁','史记','男',1)

insert into Author values('当年明月','明朝那些事儿','女',2)

select*from Book

select*from Author

-----------联合查询

--内查询

select*from Book b inner join Author a on b.Bid=a.Aid

--左查询-

select*from Book b left join Author a on b.Bid=a.Aid

--右查询

select*from Book b right join Author a on b.Bid=a.Aid

go

-------------------------------------------------创建存储过程，注意约束-----------

select*from sys.procedures--查询所有存储过程

--判断名为B_A的存储过程是否存在

if exists(select*from sys.procedures where name='B_A') drop proc B_A--若存在则删除该存储过程

go

create proc B_A

@bid int,

@aid int,

@Aname int

as

begin

delete Author where aid=@aid

update Book set BfaXingDate=GETDATE()where Bid=@bid

update Author set Aname='羞花'where aid=@Aname

end

go

--执行存储过程

exec B_A0,2,0

select*from Book

select*from Author

--查询所有存储过程

select*from sys.procedures

go

go

--删除存储过程

drop proc B_A

select*from Book

select*from Author

----------------------------------------------------------游标

-------------------------------------------------------------------------------------

----------------------------

------------------------------------------------------游标的默认读取方法，不加参数为FORWARD_ONLY例如

---declare gmyoubiao Cursor For select * from Author 同等于declare gmyoubiao Cursor forward_only For select * from Author 他们只支持fetch next 向下一行读取数据

--declare 声明。Cursor 表示这是一个游标

declare gmyoubiao Cursor For select*from Author--定义游标gmyoubiao。游标对象为表Author 里的数据

open gmyoubiao--打开游标

--先根据表Author定义个局部变量

declare@id int

declare@name varchar(38)

declare@bookname varchar(38)

declare@asex varchar(10)

declare@waijian int

fetch next from gmyoubiao into@id,@name,@bookname,@asex,@waijian--

将游标向下移行，获取的数据放入之前定义的个变量中

--打印出获取的值

print@id

print@name

print@bookname

print@asex

close gmyoubiao--关闭游标

deallocate gmyoubiao--删除游标

go

----------------------------参数为SCROLL 的游标，SCROLL支持游标在定义的数据集中向任何方向，或任何位置移动

declare gmyoubiao Cursor scroll For select*from Author--定义游标gmyoubiao。游标对象为表Author 里的数据

open gmyoubiao--打开游标

--先根据表Author定义个局部变量

declare@id int

declare@name varchar(38)

declare@bookname varchar(38)

declare@asex varchar(10)

declare@waijian int

--取下一行，并把数据赋值给变量 next

fetch next from gmyoubiao into@id,@name,@bookname,@asex,@waijian

print@id print@name print@bookname print@asex

--取最后一行，并把数据赋值给变量last

fetch last from gmyoubiao into@id,@name,@bookname,@asex,@waijian

print@id print@name print@bookname print@asex

--取第一行，并把数据赋值给变量first

fetch first from gmyoubiao into@id,@name,@bookname,@asex,@waijian

print@id print@name print@bookname print@asex

--取上一行，并把数据赋值给变量prior --就当前来说这个取到的是空数据，适用于while 循环

fetch prior from gmyoubiao into@id,@name,@bookname,@asex,@waijian

print@id print@name print@bookname print@asex

--取第三行，并把数据赋值给变量(absolute x) 其中X代表你想指向第几行

fetch absolute3 from gmyoubiao into@id,@name,@bookname,@asex,@waijian print@id print@name print@bookname print@asex

--取相对目前来说的上一行，并把数据赋值给变量(relative x) 同理适用于while循环，x 代表上面的第几行

fetch relative-1 from gmyoubiao into@id,@name,@bookname,@asex,@waijian print@id print@name print@bookname print@asex

close gmyoubiao--关闭游标

deallocate gmyoubiao--删除游标

--------------------------------------------------------------带while 循环的游标，通过进行判断游标是否获取到数据进行循环遍历-----

go

declare gmyoubiao Cursor For select*from Author--定义游标gmyoubiao。游标对象为表Author 里的数据

open gmyoubiao--打开游标

--先根据表Author定义个局部变量

declare@id int

declare@name varchar(38)

declare@bookname varchar(38)

declare@asex varchar(10)

declare@waijian int

fetch next from gmyoubiao into@id,@name,@bookname,@asex,@waijian--

将游标向下移行，获取的数据放入之前定义的个变量中

while@@fetch_status=0 --判断是否成功获取数据,0 FETCH 语句成功,-1 FETCH 语句失败或此行不在结果集中,-2 被提取的行不存在

begin

print@id print@name print@bookname print@asex

fetch next from gmyoubiao into@id,@name,@bookname,@asex,@waijian--将游标向下移行,获取的数据重新放入之前定义的个变量中

print@id print@name print@bookname print@asex

end

close gmyoubiao--关闭游标

deallocate gmyoubiao--删除游标

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------触发器-------------

select*from sys.triggers--查询所有触发器

go

-------------------------创建添加触发器-------------------

if exists(select*from sys.triggers where name='BA_trig_insert') drop trigger BA_trig_insert

go

create trigger BA_trig_insert

on Book--为BOOK表创建触发器

for insert--插入添加触发器

as

declare@Bid int,@name varchar(8);

select@Bid=bid,@name=Bname from inserted;

insert into Author values('明月',@name,'男',@Bid);

print'添加书籍成功';

print'需要查询数据吗,以下为你添加的信息';

select*from inserted;

go

insert into Book values('风云',15,GETDATE());

----------------------创建删除触发器---------------------

go

if exists(select*from sys.triggers where name='BA_trig_delete') drop trigger BA_trig_delete

go

create trigger BA_trig_delete

on Author--为Auhto创建一个触发器

for delete--当被删除信息时

as

declare@bid int;

select@bid=Bid from deleted;

delete Book where Bid=@bid;

print'被删除信息如下';

select*from deleted;

go

delete Author where Bid=3

drop trigger BA_trig_delete

select*from Book

select*from Author

-----如不再用此数据库请取消注释运行下列代码自动删除数据库

--use master

--go

--if exists(select * from sysdatabases where name='GMcre' )

-- drop database GMcre