- 浏览: 427854 次
文章分类
最新评论
java 从零开始,学习笔记之基础入门<SQL_Server_视图_函数_存储过程_触发器等>(二十二)
注释_运算符_函数
注释 --单行注释 /* *多行注释 * * */
--定义局部变量 --declare 局部变量名类型[(长度)] = 默认值; --给局部变量赋值 -- set @局部变量名= 值 --通过select语句给局部变量赋值 declare @name varchar(32); begin select @name=authorName from tb_author where authorId = 1; print '取到的值:'+@name; end;
运算符 declare @n int, @m int;--定义多个变量变量与变量之间用','分开 begin set @n=3; set @m=5; print @n+@m; end;
-函数 --定义一个整型的值,我将其装换成字符串 declare @k int , @kk varchar(32) set @k=12; begin set @kk=convert(varchar,@k); print '转换'+@kk; end;
--采用无名快的形式,对tb_author表中的某一条记录进行更新 --更新的数据自己设定 select * from tb_author;
declare @aid int, @aname varchar(32), @atel varchar(32) begin set @aid=2; set @aname='java1201'; set @atel='9999999'; update tb_author set authorName=@aname,authorTel=@atel where authorId=@aid print'更新完毕!';
end; |
视图
--视图是一张虚拟的表,在数据库中不具体的存视图,它可以代表的是一个原表,操作视图 --就是操作原表 --删除视图不删除原表 --视图的创建一般用于对表的查询或者更新可以隐藏表的具体字段 --创建视图的语法 --create view 视图名(列名,列名...) as sql语句; --对tb_author表创建一个查询的视图 create view v_author as select * from tb_author; --查询视图就显示作者表中的所有信息创建的v_authotr视图代表的就是查询tb_author表的所有记录 select * from v_author;
--指定视图的列名 create view vv_author1 (authorId,authorName,authorTel) as select * from tb_author; select * from vv_author1;
--以下三种情况必须指定视图的列名 --子函数查询的是一个表达式不是单纯的属性 create view v_author_count(num) as select count(*) from tb_author; --多表查询中查询语句有多个同名的列,那么创建视图必须要有视图 --需要在视图中为某一列取新名称则需要加入属性名 --删除视图 --drop view 视图名称
--通过对视图的插入数据往tb_author表中插入一条数据 insert into v_author values('cw','123456'); --视图是不能存储插入的数据的,最终转变成对原表的插入 select * from tb_author; select * from v_author; --视图一般在关系型数据库中主要用作查询操作,不推荐使用更新操作(CRUD); |
事务 --再对数据库操作的时候,可以把一个或者多个操作看做是一个事务 --事务在开启之后,在事务中所作的操作,要么全部成功,要么全部失败 --如果全部成功则提交事务,如果其中有某一个操作失败,则事务回滚
--开启事务: begein transaction --提交事务: commit transaction --回滚事务: rollback transation
--事务的四种特性: --原子性 --一致性 --隔离性 --永久性
--往tb_author表中插入一条记录 --在往插入之前,对插入的数据进行判断,判断是否是'周易',如果是周易则事务回滚,不是则事务提交
begin transaction --定义一个变量 declare @aname varchar(32);--定义一个字符串的变量 set @aname='周易';--给局部变量赋值 if @aname='周' begin print '保存没成功'; rollback transaction;--回滚事务,数据没有真正的保存到数据库中 end; else begin insert into tb_author values (@aname,'12345678'); commit;--提交事务,数据真正的保存到数据库中 print '保存成功'; end;
select * from tb_author;
|
存储过程
--存储过程是为了完成特定的操作而汇集的多条sql程序 --存储过程创建之后,编译之后存储在数据库中,那么存储过程只需要一次编译就可以了 --第二次使用只需要调用编译后的存储过程即可 --为什么要使用存储过程以及使用存储过程的好处? --1 可以使用更少网络的带宽,减少响应时间 --2 可以提高数据的查询效率 --创建一个存储过程的语法格式 --create procedure/proc 存储过程名as --定义变量 --begin --执行操作 --end
--存储过程的分类 --无参的存储过程 --创建一个无参的存储过程,用来查询tb_author这个表 create procedure selectAuthor as begin select * from tb_author; end; --调用无参的存储过程 exec selectAuthor; execute selectAuthor; --含输入参数的存储过程 --根据输入参数Id返回id所在的那条记录 create proc selectAuthorById @id int as begin select *from tb_author where authorId=@id; end; --调用含有输入参数的存储过程 exec selectAuthorById 5;
--含输出参数的存储过程 --返回表中记录的条数 create proc getcount @outcount int output as begin select @outcount=count(*) from tb_author; end;
--调用含有输出参数的存储过程 --必须定义一个变量来接收输出的参数 declare @n int begin exec getcount @n output; print @n; end;
--含输入输出参数的存储过程 --根据authorid返回姓名和电话 create proc inandout @id int,@name varchar(32) output,@tel varchar(32) output as begin select @name=authorName,@tel=authorTel from tb_author where authorId=@id; end; --调用含有输入参数和输出参数的存储过程 declare @name varchar(32), @tel varchar(32) begin exec inandout 3 ,@name output ,@tel output; print @name +' '+@tel; end;
--分页的存储过程 --tb_author表进行分页 --分页的sql语句authorId authorName authorTel select top 5 * from tb_author where authorId not in ( select top((2-1)*5) authorId from tb_author ) order by authorId desc
--所传递的参数 --每页显示记录的条数作为参数 --当前页数作为输入参数 --输出参数是查询到的多条记录如果要返回多条记录则采用数据库的游标
create proc fenye @pagenum int , @pagesize int as --定义一个变量为表中总的记录条数
declare @maxcount int,--最大的记录条数 @maxnum int --最多能分的页数 begin --取到最大的记录条数赋值给 select @maxcount=count(*) from tb_author; print @maxcount; --求最大的页数 if @maxcount%@pagesize=0 set @maxnum=@maxcount/@pagesize; else set @maxnum=@maxcount/@pagesize+1; print @maxnum;
--判断输入的页数如果小于则将页数赋值为 --如果大于最大页数则将页数赋值成最大页数 if @pagenum<1 set @pagenum=1;
if @pagenum>@maxnum set @pagenum=@maxnum;
print @pagenum;
select top (@pagesize) * from tb_author where authorId not in ( select top((@pagenum-1)*@pagesize) authorId from tb_author ) end;
drop proc fenye;
exec fenye 1,5; |
触发器
--什么是触发器 --触发器是对表进行插入删除修改时自动执行的过程 --触发器是在对表进行操作的时候自动执行,不需要手动执行 --触发器是定义在一个表上与表相关联 --触发器是一个事务也可以按照条件进行事务的回滚与提交
--为什么要使用触发器 --实例:现某公司有一个系统,设计一个在职员工表,和一个离职员工表,那么当员工A申请离职 --那么在公司的这个系统中在职员工表中就应该删除离职的这个用户,并且在离职表中添加离职 --的这个用户,使用触发器实现这一过程,当在职表中删除一条记录,触发器自动的将删除的员工自动 --添加到离职表中去,这样可以保证数据的一致性.
--触发器的类型 --insert触发器 --delete触发器 --update触发器
--触发器的语法格式 create trigger 触发器的名称on 表名 [WITH ENCRYPTION] for [insert],[delete],[update] as begin
end; --在职员工表tb_on_people(pid(int)(编号),pname(varchar)(姓名),ptel(varchar)(电话),page(int))(年龄) --离职员工表tb_off_people(pid(int)(编号),pname(varchar)(姓名),pworkage(int))(工作年限);1998-2 create table tb_on_people( pid int identity(1,1), pname varchar(32) not null, ptel varchar(32) not null, page int, pinwork varchar(32) not null, primary key(pid) ) create table tb_off_people( pid int, pname varchar(32) not null, pworkage int ) --往在职表中插入条记录 insert into tb_on_people values('sbb','123456',20,'2011-7-1'); insert into tb_on_people values('cw','654321',22,'2010-7-1'); insert into tb_on_people values('wm','987654',18,'2011-8-1'); --在在职员工表上创建delete触发器,当对在职员工表进行删除操作时,会自动的出发删除触发器将 --删除的信息插入到离职员工表中去 create trigger peopleout on tb_on_people for delete as declare @id int, @name varchar(32), @inwork varchar(32), @worktime int begin --在deleted表中取出对应的数据 select @id=pid,@name=pname,@inwork=pinwork from deleted; set @worktime=datepart(yyyy,convert(varchar,getdate()))-datepart(yyyy,@inwork);
insert into tb_off_people values(@id,@name,@worktime); end; --deleted 表 --当执行删除的触发器的时候,会自动的在内存当中创建一个deleted表来存放删除的数据 --我们可以从这个deleted表中取出删除的数据,当触发器执行完成,就自动的删除deleted表 --deleted表中的字段和你删除数据的表的字段一致 --inserted表 select * from tb_off_people; select * from tb_on_people; drop table tb_on_people; drop table tb_off_people; delete from tb_on_people where pid=2;
--insert 触发器 --如果是insert触发器,对某一张表新插入的数据会保存在inserted表中 --那么我们就可以在调用insert触发器的时候从inserted表中取出新插入的数据 --现在对在职员工表进行操作,对插入的数据中年龄进行限制,限制年龄必须大于岁 --否则插入不成功给个提示信息 create trigger checkPeopleAge on tb_on_people for insert as declare @age int; begin --从insert表中取出插入的age值,并赋值给@age select @age=page from inserted; if @age<18 begin --事务回滚 rollback; print '你还忒嫩了'; end; else begin --事务提交 commit; end; end;
insert into tb_on_people values('战神','0000000',12,'2012-2-1'); select * from tb_on_people;
--更新触发器 --分别从insert表和deleted表中取出更新前和更新后的数据显示出来 create trigger updatePeople on tb_on_people for update as declare @id int,@name varchar(32),@age int ,@tel varchar(32),@work varchar(32) begin select @id=pid,@name=pname,@tel=ptel,@age=page,@work=pinwork from deleted; select pid,pname,ptel,page,pinwork from inserted; print @id; print @name print @tel print @age print @work end; --更新将id=2的记录更新 update tb_on_people set pname='张三' ,ptel='10086' ,page=30,pinwork='1970-1-1' where pid=2; |
多表查询练习 --用户表 --表名tb_user --用户编号uid int 主键自增长 --用户姓名uname varchar(32) not null --用户密码upass varchar(32) not null
--角色表 --表名tb_role --角色编号rid int 主键自增长 --角色名称rname varchar not null
--权限表 -- 表名tb_qx -- 权限编号qid int 主键自增长 -- 权限名称 qname varchar not null
--用户角色表 --表名tb_user_role --用户角色编号urid int 主键自增长 --用户编号 uid int 外键关联用户表的主键 --角色编号 rid int 外键关联角色表的主键
--角色权限表 --表名tb_role_qx --角色权限编号rqid int 主键自增长 --角色编号rid int 外键关联角色表的主键 --权限编号qid int 外键关联权限表的主键
create table tb_user( uid int identity(1,1), uname varchar(32) not null, upass varchar(32) not null, primary key(uid) )
create table tb_role( rid int identity(1,1), rname varchar(32) not null, primary key(rid) )
create table tb_qx( qid int identity(1,1), qname varchar(32) not null, primary key(qid) )
create table tb_user_role( urid int identity(1,1), uid int , rid int, primary key(urid), foreign key(uid) references tb_user, foreign key(rid) references tb_role, )
create table tb_role_qx( rqid int identity(1,1), rid int , qid int , primary key(rqid), foreign key(rid) references tb_role, foreign key(qid) references tb_qx,
)
--往用户表中插入数据 insert into tb_user values('admin','admin'); insert into tb_user values('es','es'); insert into tb_user values('pom','pom'); insert into tb_user values('pa','pa'); insert into tb_user values('ts','ts');
--往角色表中插入数据 insert into tb_role values('管理员'); insert into tb_role values('boss'); insert into tb_role values('farmer');
--往权限表中插入数据 insert into tb_qx values('add权限'); insert into tb_qx values('delete权限'); insert into tb_qx values('select权限'); insert into tb_qx values('update权限'); insert into tb_qx values('login权限');
select * from tb_user; select * from tb_role; select * from tb_qx; select * from tb_user_role; select * from tb_role_qx --给admin赋一个管理员权限
declare @uid int, @rid int begin select @uid=uid from tb_user where uname='admin'; select @rid=rid from tb_role where rname='管理员'; insert into tb_user_role (uid,rid) values(@uid,@rid); end;
--给管理员赋权限表中所有功能 insert into tb_role_qx(rid,qid)values(1,1); insert into tb_role_qx(rid,qid)values(1,2); insert into tb_role_qx(rid,qid)values(1,3); insert into tb_role_qx(rid,qid)values(1,4); insert into tb_role_qx(rid,qid)values(1,5);
--如果一个用户是管理员角色那么此用户有没有增删改查登陆权限 --查询admin用户拥有的角色 select rname from tb_role where rid in( select rid from tb_user_role where uid in( select uid from tb_user where uname='admin' ) )
--查找角色为管理员的所有用户 select uname from tb_user where uid in(select uid from tb_user_role where rid in (select rid from tb_role where rname='管理员')) --查找用户为admin的所有权限 select qid from tb_role_qx where rid =1 select uid from tb_user_role where rid=1 select uname from tb_user where uid=1 select qname from tb_qx where qid in(select qid from tb_role_qx where rid in (select rid from tb_user_role where uid in (select uid from tb_user where uname='admin'))) --查找拥有增加权限的所有用户 select uname from tb_user where uid in(select uid from tb_user_role where rid in (select rid from tb_role_qx where qid in (select qid from tb_qx where qname='add权限'))) --查找拥有增加权限的所有角色 select rname from tb_role where rid in (select rid from tb_role_qx where qid in (select qid from tb_qx where qname='add权限'))
--为用户admin增加一个权限
--为用户admin增加一个角色 declare @uid int, @rid int begin select @uid=uid from tb_user where uname='admin'; select @rid=rid from tb_role where rname='boss'; insert into tb_user_role (uid,rid) values(@uid,@rid); end; --为管理员角色增加一个权限
--增加一个用户,并赋给它所有的权限 insert into tb_user(uname,upass) values ('mm','mm'); declare @uid int, @rid int begin select @uid=uid from tb_user where uid=6; select @rid=rid from tb_role where rid=1; insert into tb_user_role (uid,rid) values (@uid,@rid); end;
--移除一个用户的某一项权限 declare @uid int, @rid int begin select @uid=uid from tb_user where uid=5; select @rid=rid from tb_role where rid=3; insert into tb_user_role (uid,rid) values (@uid,@rid); end;
declare @rid int , @qid int begin select @rid=rid from tb_role where rid=3; select @qid=qid from tb_qx where qid=1; insert into tb_role_qx (rid,qid) values (@rid,@qid); end;
--移除用户的角色 declare @uid int begin select @uid=uid from tb_user where uname='ts'; delete from tb_user_role where uid=@uid; end;
--移除一个角色的所有权限 declare @rid int begin select @rid=rid from tb_role where rname='farmer'; delete from tb_role_qx where rid=@rid; end; --移除用户的所有权限 declare @uid int begin select @uid=uid from tb_user where uname ='admin' delete from tb_user_role where uid=@uid; end
select * from tb_user; select * from tb_role; select * from tb_qx; select * from tb_user_role; select * from tb_role_qx delete from tb_user where uid=8 |
相关推荐
笔记是本人学习SQLServer一段时间后重新整理出来的,适合有一些入门基础的人学习。 ├─01 安装及使用 │ SQLServer2005安装及使用.txt │ ├─02 常用函数 │ function.sql │ ├─03 建表、建库 │ create.sql ...
sql server 2005学习笔记之触发器 很全 说的很详细
SQL_Server_2005_学习笔记【经典】80是很经典的SQL_Server_2005_学习笔记噢
SQL_Server_2000学习笔记
oracle知识点笔记,语法,触发器,存储过程,存储函数,流程控制,游标,异常处理,记录类型,视图,控制用户权限,高级子查询,set运算符,基本的sql_Select语句等等
Java基础 java学习笔记 Java Java基础Markdown学习笔记,可转换成PDF、Word等格式
关于SQL学习的笔记,可以在有SQL基础之上,平时学习或者工作开发时当做查阅资料
资源名称:Java零基础教程视频_从入门到精通资源目录:【】000_Java零基础视频教程__讲义【】000_Java零基础视频教程__课堂笔记【】001_Java零基础视频教程__安装EditPlus【】002_Java零基础视频教程__计算机概述...
java学习笔记 从零开始的老师教程 jave笔记,喜欢学校jave的人
SQL SERVER 存储过程学习笔记(自己写的希望能帮助到你们,如果有地方写的不好,请大家见谅。)
sqlserver2005学习笔记之触发器简介.pdf
本人的SQL server2017笔记。 笔记内容:基础增删改查、视图操作、索引操作、索引种类、多表、ml查询、Transact-SQL、存储过程、触发器、SQL server自动化服务、SQL server集成服务…
sqlserver个人学习笔记
学习sql存储过程,一则关于存储过程的学习笔记
<br>系统设计<br> 1 jive设计思路 <br> 2 jive的工作内幕 <br> 3 Jive源代码研究 <br> 4 Jive中的设计模式 <br> 5 jive学习笔记 <br><br><br> <br>设计模式<br> 1 大道至简-Java之23种模式一点就通 <br> 2 设计模式...
SQLServer 学习精华,知识笔记,供大家交流。
资源名称:SQL_Server2008数据库笔记资源截图: 资源太大,传百度网盘了,链接在附件中,有需要的同学自取。
已整理好基础版和高级版笔记 导出为pdf 和html 格式 界面清晰美观,均有详细目录
1.FORALL 用法小结 2.如何使用批挷定提高性能 3.FORALL 如何影响回滚 4.用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数 ,用%BULK_ROWCOUNT 属性计算FORALL...8.SQL优化学习笔记 9.给Oracle存储过程传入数组(这是自己的)