大三 sql server 自我总结

use JXGL

(1) select sname,age, s.sdept, cname from S join sc on s.sno = sc.sno join c on sc.cno = c.cno

 select s.sno, sname, cname, grade from S join sc on s.sno = sc.sno join c on sc.cno = c.cno

–嵌套查询

select sno,sname,sdept from s where sdept  = any(select sdept from s where sname='s1')

select sname,s.sno,c.cno,cname,grade from S join sc on s.sno = sc.sno join c on sc.cno = c.cno where grade > any(select AVG(grade) from sc group by sno having grade != null)

go

go
select sno,AVG(grade) as 'average' from sc group by sno 
go

select * from sc 

 (6) and (7) (无关子查询)	思路: 所有表自然连接, 然后, in 符合条件的表, 注意是: in 后面是 cno 临时表
 select sname from s join sc on s.sno = sc.sno where sc.cno in (select c.cno from sc join c on sc.cno = c.cno where cname != '应用数学')


等价于(嵌套查询) 思路: 查询结果, 成为一张临时表, 于原来的s表, 自然连接

select sname from s join (select cname,c.cno,sc.sno from sc join c on sc.cno = c.cno where cname = '应用数学') as f on s.sno = f.sno

select sname,s.sno,cno from s join sc on s.sno = sc.sno

select sno,cname,c.cno from sc join c on sc.cno = c.cno where cname like '应用数学'
-- 游标操作

游标 读取
declare cs_cursor scroll cursor
for select sno,sname,age,sex,sdept from S --结果集
for read only -- 对数据的 操作方式
open cs_cursor	-- 打开游标
fetch from cs_cursor //取下行
fetch first from cs_cursor
fetch absolute 4 from cs_cursor
fetch prior from cs_cursor

close cs_cursor	--关闭游标
deallocate cs_cursor --释放游标

– 游标的修改 和删除(同理 update 变 delete)

declare cs_cursor scroll cursor
for select sno,sname,age,sex,sdept from S
for update of sname,sex --定义类型为更新, 且 更新的范围 为sname, sex

open cs_cursor

fetch first from cs_cursor
update s set sname= 's1何',sex='男' where current of cs_cursor
fetch first from cs_cursor --查看修改的结果

close cs_cursor
deallocate cs_cursor


--游标的遍历

declare cs scroll cursor
for select s.sno, sname, grade from s, sc where s.sno = sc.sno -- 相当于 自然连接
open cs

declare @no char(9),@name char(8), @grade int
fetch next from cs into @no, @name , @grade
while @@fetch_status = 0
begin
	print @no + '' + @name + '' + @grade
	fetch next from cs into @no, @name , @grade
end
close cs
deallocate cs


select * from s, sc, c  -- 与下等价(逗号隔开, 相当于 笛卡儿积)
select * from (s join sc on s.sno is not null) join c on c.cno

declare cs scroll cursor
for select s.sno, sname, grade from s, sc where s.sno = sc.sno -- 相当于 自然连接
open cs
declare @no char(14),@name char(8), @grade int
fetch next from cs into @no, @name , @grade
while @@fetch_status = 0	-- 返回上次执行结果的状态 0 成功 -1 失败 或者 不在结果集中  -2 被读取的行不存在
begin
	print @no + '' + @name + '' + str(@grade)
	fetch next from cs into @no, @name , @grade
end
close cs
deallocate cs

declare @name varchar(50),@path varchar(256),@fileName varchar(256),@fileData varchar(20)

select @path = 'D:\path', @fileData = CONVERT(varchar(20), getDate(), 112)

declare dbc cursor 

存储过程: 
	无参数(简单类):
		use JXGL
		go
		create procedure s_grade
		as 
		select s.sno,sname,cname,grade
		from s join sc on s.sno=sc.sno join c on sc.cno = c.cno
		go

		execute s_grade --使用

	传参,且有返回值
		use JXGL
		declare @result real,@sno char(12)
		set @sno = '171001'
		set @result = 1.1
		 exec t_1  @sno,@result output --此处 output 指定, result 在 存储过程中, 需要被 返回(也就是赋值)(声明参数 需要有output 关键字)
		 -- 存储过程中, 必须有 output, 才能指定, 或则会报错
		 -- 存储过程中, 的return 语句 ,不能返回空值, 否则会有警告
		 -- 存储过程, 定义了, output, 外部 可以不用定义 output
		print @result

	return 的使用
		exec @result = 存储过程名 参数1, 参数2....

		例: 
			use JXGL
			declare @result real,@sno char(12)
			set @sno = '171001'
			set @result = 1.1
			 exec @result= t_1  @sno,@result --此处 output 指定, result 在 存储过程中, 需要被 返回(也就是赋值)(声明参数 需要有output 关键字)
			 -- 
			print @result

	output 的使用 :  其实相当于 C语言中的 参数, 传入的是指针
		

带 异常处理的 的 存储过程

use EDUC
go

create proc addStudentInfo
	@sno char(8),@sname char(8), @sex char(2),@s_native varchar(50), 
	@birthday smalldatetime, @dno char(4), @classno char(4),@entime smalldatetime,
	@home varchar(50), @tel char(12)
as
begin try
insert into Student_info values 
(
	@sno, @sname, @sex,@s_native, @birthday , @dno, 
	@classno, @entime, @home, @tel 
)

print '插入成功一条信息到Student_info中'
end try
begin catch
print '学号'+ @sno + '插入失败到 student_info'
end catch

触发器

创建
for
instead of
after

use JXGL
go
create trigger siu
on s
instead of 
insert, update, delete
as
	print '不给你瞎操作'
go

修改 create ---> alter
删除 
	drop trigger ['触发器名字']
启用
alter table s
enable trigger all(所有, 或者制定, 单个的名字)

关闭
disable trigger all

测试用例
use JXGL

go
create proc insertToS
@sno varchar(10), @sname char(8), @sex char(2), @age smallint, @sdept varchar(50)
as 
	insert into S values(@sno,@sname,@sex,@age,@sdept)
	print '成功插入了'
go

测试用例 2

use JXGL

go
declare @mtran varchar(20)
select @mtran = 'my_tran'
begin transaction @mtran
	exec insertToS '0000','haha', '男', 17 ,'计算机'
	print 1/0;
commit tran @mtran

GO语句的作用

如果只是执行一条语句，有没有GO都一样
如果多条语句之间用GO分隔开就不一样了
每个被GO分隔的语句都是一个单独的事务，一个语句执行失败不会影响其它语句执行。



GO是分批处理的意思，语句太长的时候使用它分批处理：

隔离, 事务的作用, 使得, 先后顺序不影响结果

object_id(‘对象的引用路径’) 查看 id

视图(本质一张查询结果生成的表)

-- 创建
create view V_name
as
	select .... where... --条件 1
	with check option -- 表示 对 视图 update, insert, delete 操作时, 需要满足, 子查询 条件( 条件1)

-- 改 alter

-- 删除 drop view v_name

使用技巧:
	视图, 等价表 来使用
	并且, 可以制定, 需要的部分

索引

use JXGL
create unique index clustered i_name --一般不能用, 因为聚集索引只能有一个, 对于有 主键的表, 自动生成了
on s(sno)

事务的处理

use JXGL
-- 匿名事务: 只能使用一次
go
begin tran

commit tran

-- 隐式事务开关
set implicit_transactions on / off



go
declare @mtran varchar(20)
select @mtran = 'my_tran' -- 取名
begin tran @mtran
	save tran  point
	-- 事务保存点 
	insert into S values ('0005','haha', '男', 17 ,'计算机')
	print 1/0
	rollback tran point
	-- 回滚 事务 点
commit tran @mtran --提交事务

go


-- 发现: 1 与 2 等价
场景 1:
begin tran
	-- 内容
rollback tran 

场景 2:
begin tran
		save tran pointName
		-- content
		rollback tran pointName
commit tran

-- 问题: 那为什么, 还要 有 二?

-- 问题 : 以下事务不起 预期的 作用
begin tran
	-- content
commit


-- 场景 3: 不可使用, 必须要在, 事务 的 域内
save tran pointName
		-- content
rollback tran pointName