sql语句大全|sql语句列表
【娱乐休闲】
【生活服务】
【电脑网络】
【文体教育】
【行业部门】
文章浏览→→→sql语句大全|sql语句列表sql语句大全|sql语句列表
----sql server 2005 远程连接问题------1.sqlcmd 或者 osql 等二进制工具，如果想远程连接服务器--在服务器端要设置 配置工具--〉外围应用配置器--〉TCP/IP和pipes--默认状态下面 不允许远程连接。--2. 默认状态下没有办法注册服务器端的实例，要想注册服务器端实例--配置工具--〉外围应用配置器--〉TCP/IP和pipes--另外需要在服务器端启动：sql server browser 的 服务。--数据库的创建和修改----1.创建--主文件必须至少是 3 MB 才能容纳模型数据库的副本。&create database teston primary(name=test_data,filename='C:\sql 2005\database\test_data.mdf',size=3,--maxsize=50,maxsize=unlimited,filegrowth=10%),(name=test_data2,filename='C:\sql 2005\database\test_data2.ndf',size=2,maxsize=40,filegrowth=5%),filegroup mygroup(name=test_data3,filename='C:\sql 2005\database\test_data3.ndf',size=1,maxsize=unlimited,filegrowth=5%),(name=test_data4,filename='C:\sql 2005\database\test_data4.ndf',size=1,maxsize=20,filegrowth=10%)log on(name=test_log,filename='C:\sql 2005\database\test_log.ldf',size=2,maxsize=unlimited,filegrowth=10%)go--2.更改alter database testmodify file (name=test_data2,size=10,filegrowth=10%)go--3.收缩文件use testgodbcc shrinkfile(test_data2,1)go--4.数据类型select * from systypes--自定义数据类型exec sp_addtype dt,'varchar(20)','not null'goexec sp_addtype dts,'varchar(20)','NOT NULL'goexec sp_droptype dtsgo---表的创建和修改-----1.创建create table deptment(dept_id varchar(2),dept_name varchar(10))go--插入数据insert into deptment(dept_id,dept_name) values('01','企管部')goinsert into deptment values('02','市场部')insert into deptment(dept_id) values('03')--查询select * from deptmentselect dept_id from deptment where dept_name='市场部'select * from deptment order by dept_id ascselect top 1 * from deptment order by dept_id descselect top 30 percent * from deptment order by dept_id desc--修改update deptment set dept_name='研发部' where dept_id='03'--删除delete from deptmentgodelete from deptment where dept_id='01' or dept_id='02'delete from dpetment where dpet_id&='02'--2.修改--修改数据类型alter table deptment alter column dept_id nvarchar(4) notnullgo--增加字段alter table deptment& add dept_addressvarchar(40)goselect * from deptment--删除字段alter table deptment drop column dept_addressgo--3.修改对象名称--sp_rename--修改字段名称exec sp_rename 'deptment.dept_names','dept_name','column'goselect * from deptmentgo--修改表的名称exec sp_rename 'deptment','dept'go--修改数据库名称exec sp_rename 't','t_new','database'gocreate table emp(emp_id varchar(4),emp_name varchar(10),sex char(2),birth datetime,dept_id nvarchar(4))goinsert into emp values('001','张三','男','','01')insert into emp values('002','李四','女','','02')goselect * from empselect * from dept--T-SQL 语句----DDL 数据定义语言--1.变量--1.1 局部变量declare @s varchar(20)--select @s='hello world!'--set @s='hello world!'set @s=(select emp_name from emp where emp_id='001')print @sgo--1.2 全局变量declare @@quanju varchar(50)set @@quanju='quanju'print @@quanjugo--2.注释符;运算符;统配符--2.1 注释符--注释一行--2.2 运算符declare @i1 int,@i2 intset @i1=100set @i2=201print @i1%@i2go--2.3 通配符--%;_;[0-9];[a-z]select * from sselect * from s where s1 like 'a%'select * from s where s1 like '%a%'select * from s where s1 like '%a%b%'select * from s where s1 like '%a_b%'select * from s where s1 like '[0-9]'select * from s where s1 like '张_'select * from s where s1 like '张%'--3. 流程控制语句--3.1 if (begin end)declare @i intset @i=110if @i=110print elseprint godeclare @id varchar(4),@name varchar(10),@s varchar(40)set @id='001'set @name=(select emp_name from emp where )set @s='编号为
的员工的姓名是：if @name is not null&begin&&print @s+@name&&print 'ok'&endelse&print '员工信息表中没有这个编号!'go--3.2 select casecreate table stu_s(stu_name varchar(10),stu_score float)goselect * from stu_sdeclare @name varchar(10),@s floatset @name='孙起'set @s=(select stu_score from stu_s where )if @s&=90 print '评定级别为:优'elseif @s&=80 print '评定级别为:良'elseif @s&=70 print '评定级别为:中'elseif @s&=60 print '评定级别为:可'elseprint '评定级别为：差'godeclare @name varchar(10),@s float,@r varchar(2)set @name='孙起'set @s=(select stu_score from stu_s where )select @r=casewhen @s&=60 and @s&70 then '可'when @s&=70 and @s&80 then '中'when @s&=80 and @s&90 then '良'when @s&=90 and @s&=100 then'优'else '差'endprint @name+' 的评定级别是：go--3.3 goto--改变程序执行的流程，跳转到标示位（标示位必须以：结尾）declare @i intset @i=1here:print @iset @i=@i+1while @i&=5 goto herego--DML 数据操作语言--DCL 数据控制语言-----------------------------------------------------3.4 while continue breakdeclare @n intset @n=0while @n&5begin&& set @n=@n+1&& if @n=3&&&&&--break&&&&&continue&& else&&&&&print @nendgo--3.5 wait for--delay(最多24个小时)waitfor delay '00:00:10'print '等待了10秒'go--timewaitfor time '08:42:00'--print 'ok'select getdate()go--4.常用函数--4.1 聚合函数select * from stu_sselect avg(stu_score) as 平均值 from stu_sselect sum(stu_score) as 和 from stu_sselect max(stu_score) as 最大值 from stu_sselect min(stu_score) as 最小值 from stu_sselect count(*) as 记录数& from stu_sselect @@versionselect @@servername--4.2 算数函数select ceiling(98.2)go--向前进一select floor(98.7)go--去掉小数点select round(98.58,1)--4.3 字符串select ascii('A')select char(97)select lower('HELLO')select upper('hello')--4.4 去空格--ltrim rtrimdeclare @s varchar(20)set @s='&&&hello&&& '--set ltrim(@s)print print 'no'+ltrim(rtrim(@s))+'yes'go--4.5 取子串函数select left('abcd',3)goselect right('abcd',2)select substring('abcd',1,2)go--4.6 字符串比较select charindex('b','abcd')goselect charindex('j','abcd')go--4.7 数据类型转换--convert/castdeclare @i int ,@s varchar(10)set @i=100set @s='abc'print convert(varchar(10),@i)+@s--print @i+cast(@s as int)go--DML----1.IN 关键字select * from emp where emp_id='001' or emp_id='002'goselect * from emp where emp_id in('001','002')go--2.group by 分组create table books(books_id varchar(10),books_name varchar(20),price smallmoney,qty int,pub varchar(20)constraint pk_books primary key(books_id))goselect * from booksgoselect pub,sum(price*qty) as 小计 from books group by pubgoselect pub,sum(price*qty) as 小计 from books whereprice&30 group by pubgoselect pub,avg(price) as 平均价 from books group by pubgo--computeselect pub,sum(price*qty) as 小计 from books group by pub havingavg(price)&40goselect * from books order by pub asc compute avg(price) bypubgoselect * from books order by pub asc compute sum(price*qty) bypubgo--报错--havingselect pub,avg(price) as 平均值 from books whereavg(price)&20 group by pubgoselect pub,avg(price) as 平均值 from books group by pub havingavg(price)&20go--练习：create table prod(pid varchar(10),pname varchar(10),pricesmallmoney,qty int,type varchar(10),fac varchar(10))goselect * from prodgo--根据type分组查询出每一组产品总共花费多少；分别查出每一组中具体产品总花费--根据type排序，利用compute 统计出所有产品的单价的平均值；统计出每一组产品单价的平均值--分别统计每一组中具体产品的总花费和此组产品一共的总花费select [type],sum(price*qty) as 小计 from prod group by[type]goselect pname,[type],sum(price*qty) as 小计 from prod group by[type],pnamegoselect * from prod order by [type] compute avg(price)goselect * from prod order by [type] compute avg(price) by typegoselect *,price*qty as 小计 from prod order by [type] computesum(price*qty) by typego---连接查询-----1.交叉连接（笛卡尔乘积）--cross joinselect * from empselect * from deptselect * from emp cross join deptselect * from emp,dept--2.内连接--inner join onselect emp_id,emp_name,dept_name from emp inner join dept onemp.dept_id=dept.dept_idgo--简写select emp_id,emp_name,dept_name from emp e,dept d wheree.dept_id=d.dept_idgocreate table sal(emp_id varchar(4),emp_sal smallmoney)goinsert into sal values('001',3000)insert into sal values('002',2500)--打印出员工姓名、所在部门、工资select emp_name,dept_name,emp_sal from emp,dept,salwhere emp.dept_id=dept.dept_id and emp.emp_id=sal.emp_idgo--3.外连接--3.1 左外连接select * from empinsert into emp(emp_id,emp_name,sex,birth)values('003','王五','女','')go--left outer join onselect emp_id,emp_name,dept_name from emp left outer join dept onemp.dept_id=dept.dept_idgo--简写select emp_id,emp_name,dept_name from emp,dept whereemp.dept_id*=dept.dept_idgo--sql server 2005 不在支持--3.2 又外连接select * from deptinsert into dept values('04','教学部')go--right outer join onselect emp_id,emp_name,dept_name from emp right outer join dept onemp.dept_id=dept.dept_idgo--简写select emp_id,emp_name,dept_name from emp,dept whereemp.dept_id=*dept.dept_idgo--sql server 2005 不在支持--3.3 全外连接--full outer join onselect emp_id,emp_name,dept_name from emp full outer join dept onemp.dept_id=dept.dept_idgo--4.自连接select * from emp,empselect * from emp e1,emp e2--存储查询结果（批量插入）--insert into emp values('004','孙刘','男','','03')select emp_id,emp_name into emp_self from empgoselect * from emp_selfinsert into emp_self select emp_id,emp_name from empgo--结束--alter table emp_self add pos varchar(10),leader varchar(10)goselect * from emp_selfgo--尝试：把leader字段的编号替换成人名(自连接)select * from emp_self e1,emp_self e2--内连接select e1.emp_id,e1.emp_name,e1.pos,e2.emp_name from emp_selfe1,emp_self e2where e1.leader=e2.emp_id--左外连接select e1.emp_id,e1.emp_name,e1.pos,e2.emp_namefrom emp_self e1 left outer join emp_self e2on e1.leader=e2.emp_idselect e1.emp_id,e1.emp_name,e1.pos,isnull(e2.emp_name,'总负责人')as leaderfrom emp_self e1 left outer join emp_self e2on e1.leader=e2.emp_idgo--5.子查询select * from (select * from emp) e--5.1where 条件中--1.inselect * from emp where emp_id in('002','003')select * from emp where emp_id in(select emp_id from emp)--2.exists(存在)--select * from emp where emp_id in(select * from emp)select * from emp where exists(select * from emp)gocreate table t(t1 int,t2 varchar(10))goif exists(select * from sysobjects where name='t')drop table tcreate table t(t1 int,t2 varchar(10))goselect * from sysobjects where name='t'select object_id('t')use mastergoselect * from syscomments where id=object_id('books')--5.2字段位置select emp_id,emp_name,dept_name from emp,dept whereemp.dept_id=dept.dept_idgo--内连接select emp_id,emp_name,(select dept_name from dept wheredept_id=emp.dept_id) from empgo--子查询（实现左外连接功能）--5.3表的位置use testgoselect emp_id,emp_name from emp order by emp_idselect emp_id as id,emp_name name,'test' tt from emp order byttselect id=emp_id,emp_name name,'test' tt,getdate() cd from emporder by idselect id=emp_id,emp_name name,'test' tt,getdate() cd from empwhere id='001'select * from (select id=emp_id,emp_name name,'test' tt,getdate()cd from emp) e where id='001'go--总结：如果用连接查询和子查询都可以实现某个功能时，优先使用连接查询（效率远远高于子查旬）--尝试与提到：（子查询）--1.为表加行号select * from prodselect sn=8,* from prodselect sn=(select count(*) from prod p1 wherep1.pid&=p.pid),* from prod pgo--2.断号的查询drop table tcreate table t(t1 int)goselect * from tselect t1+1 from tselect * from t where t1 not in(select t1+1 from t)go--3.删除重复行create table ss(s1 varchar(10),s2 varchar(10))goselect * from ssselect distinct * from ss--3.1 临时表select distinct * into #t from ssgodelete from ssinsert into ss select * from #tgoselect * from ssgo--3.2 子查询alter table ss add s3 int identitygodelete from ss where s1 in(select s1 from ss ds where ds.s1=ss.s1 and ds.s2=ss.s2 andds.s3&ss.s3)goalter table ss drop column s3goselect * from ss---索引-----为了加快检索速度；类似于书的目录，主要针对表；建立主键（默认生成一个聚集索引）--建立索引后会降低增删改的速度，占空间。--分类：聚集索引（簇索引）：和数据混为一体，速度快，一个表上只能有一个索引；非聚集索引（非簇索引）--1.创建create index pk_prod on prod(pid)go--默认　非聚集，不唯一create nonclustered index pk_prod on prod(pid)--指明创建非聚集create clustered index pk_prod on prod(pid)--指明创建聚集create unique index pk_prod on prod(pid)--唯一值索引create unique clustered index pk_prod on prod(pid)--2.察看索引exec sp_helpindex prodgo--3.编辑--3.1 修改sp_rename 'prod.[pk_prod]','pk_prod_new','index'go--3.2 删除drop index prod.pk_prodgo--建议：以主键的方式建立索引，效率高，符合范式要求。--4.全文索引--4.1 给某个表建立索引（主键）--4.2 新建全文目录--4.3 在表上定义全文索引--4.4 启用全文填充--4.5 全文查询select * from v where contains(v1,'hello')go--路径：C:\WINDOWS\system32\noise.chsuse adventureworksgoselect * from production.productdescriptiongoselect * from production.productdescription wherecontains(description,'自行车')go------------------------------------------------------约束完整性-----1.分类--1.1 实体完整性--保证表中所有行唯一--实现：唯一索引；primary keycreate table aa(a1 varchar(10) primary key,a2 varchar(10))goinsert into aa values('abc','b2')select * from aa--1.2 域完整性--指定列的取值范围--实现：check 约束create table bb(b1 int,b2 int check(b2&10))goinsert into bb values(1,12)--1.3 参照完整性（引用完整性）-- 两个或者以上的表的主键和外键的数据一致性--实现：主/外键create table cc(c1 varchar(10) foreign key references aa(a1) on updatecascade,c2 varchar(10))gocreate table c(c1 varchar(10) foreign key references aa(a1) on update noaction,c2 varchar(10))goinsert into c values('xyz','dsf')goinsert into cc(c2) values('dsf')goselect * from ccselect * from aaselect * from cupdate aa set a1='abc' where a1='xyz'go--2.实现--2.1 主键--定义了主键的列在表中唯一，而且一个表只能有一个主键，不允许重复也不允许空，产生一个唯一的聚集索引create table b(b1 int ,b2 int,constraint pk_b primary key(b1))create table b(b1 int not null primary key,b2 int)--2.2 外键insert into sal values('800',8000)insert into emp values('003','王五','女','','03')select * from salselect * from empselect * from dept--增加主键alter table dept add constraint pk_dept primary key (dept_id)goalter table emp add constraint pk_emp_dept foreignkey(dept_id)references dept(dept_id) on update cascadego--不检查表中的原有数据 with nocheckalter table emp with nocheck add constraint pk_emp_dept foreignkey(dept_id)references dept(dept_id) on update cascadegodelete from emp where emp_id='003'goalter table emp alter column emp_id varchar(4) not nullgoalter table emp add constraint pk_emp primary key (emp_id)goalter table sal with nocheck add constraint pk_sal_empforeign key (emp_id) references emp(emp_id)on update cascadego--2.3 checkselect * from empinsert into emp values('006','hello','no','','02')goalter table emp with nocheck add constraint pk_emp_sexcheck(sex='男' or sex='女')go--练习：判定手机号create table con(cid varchar(10) primary key,cname varchar(20),cphone varchar(11))goselect * from conalter table con add constraintpk_cphone check(cphone like'13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or cphone like'15[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')goinsert into con values('001','user1','135689')--2.4 ruleselect * from bbcreate rule myruleas@n&10 and @n&50goexec sp_bindrule myrule,'bb.b1'goexec sp_unbindrule 'bb.b1'goinsert into bb values(15,30)create table m(username varchar(10),mon money)goselect * from mdelete from minsert into m values('a',30)insert into m values('b',30)begin tranupdate m set mon=mon-25 where username='a'update m set mon=mon+25 where username='b'if @@error=0commit tranelserollback trangocreate rule m_ruleas@m&0 and @m&50sp_bindrule m_rule,'m.mon'go--2.5 默认create table def(d int default 8,e int)goinsert into def(d,e) values(10,8)select * from defcreate default def_sex as '男'goexec sp_bindefault def_sex,'emp.sex'goexec sp_unbindefault 'emp.sex'goselect * from empinsert into emp(emp_id,emp_name,birth,dept_id)values('006','hello','','01')--2.6 unique 唯一约束create table uu(u1 int primary key,u2 int unique)goinsert into uu values(1,null)goselect * from uu--2.7 察看约束关系exec sp_helpconstraint empgo--2.8 整型字段的自增--identitycreate table tt(t1 int identity(10,2),t2 varchar(10))goinsert into tt values('a')insert into tt values('c')select *from tt---视图---create view emp_deptasselect emp_name,dept_name from emp,dept whereemp.dept_id=dept.dept_idgoselect * from emp_deptgoinsert into emp_dept values('ok','88')go--1.概述:一个或者多个物理表的查询结果(虚表)--2.优点: 简化操作;安全性--3.创建create view v_booksasselect * from booksgoselect * from v_booksinsert into v_books(books_id) values('0006')goupdate v_books set books_name='test' where books_id='0006'go--4.修改create view v_1asselect * from uugoselect * from v_1goalter view v_1asselect u1 from uugo--5.察看视图--5.1 系统存储过程exec sp_helptext v_booksgo--5.2 系统表select * from syscommentsselect text from syscomments where id=''select * from sysobjectsselect * from sysobjects where name='v_books'select text from syscomments where id=(select id from sysobjectswhere name='v_books')goselect text from syscomments where id=object_id('emp_dept')go--6.视图加密alter view v_bookswith encryption--设置视图加密asselect * from booksgo---存储过程-----1.概述:一组为了完成特定功能的T-SQL语句的集合--2.分类: 系统存储过程(sp开头400个左右);用户自定义--3.优点:通用性和可移植性;安全性;速度快create table users(userid varchar(4),username varchar(10),userpwd varchar(10))goinsert into users values('001','admin','adminpwd')insert into users values('002','user1','userpwd')select * from usersselect * from users where username='admin' anduserpwd='adminpwd'goselect * from users where username='' or 1=1 --' anduserpwd='adminpwd'go--4.过程：检索代码；编译；优化；执行；保存在cache--5.系统存储过程--5.1 用于管理--5.1.1 用户管理exec sp_addlogin 'username','pwd','database'goexec sp_droplogin 'username'exec sp_password 'oldpwd','newpwd','username'gosp_whogo--5.1.2 数据库管理--分离数据库exec sp_detach_db tgo--附加数据库sp_attach_db 't','C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\t.mdf','C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\t_log.ldf'go--更改数据库选项exec sp_dboption t,'read only','true'goexec sp_dboption t,'read only','false'go--5.2 用于收集相关信息exec sp_helptext emp_deptgosp_helpdb 'test'gosp_helpuserexec sp_helpservergo--5.3 用于系统配置sp_addtypesp_droptypeexec sp_configurego--5.4 用于监控sp_spaceusedgoexec sp_monitorgo--6.用户自定义存储过程create procedure p_prod@id varchar(10)='001'asselect * from prod where goexec p_prodgo--6.1 嵌套alter proc p1asprint 'p1 level is:'+cast(@@nestlevel as varchar(10))goexec p1gocreate proc p2asprint 'p2 level is:'+cast(@@nestlevel as varchar(10))exec p1goexec p2gocreate proc p_selfasprint 'p_self level is:'+cast(@@nestlevel as varchar(10))exec p_selfgoexec p_selfgo--6.2 参数--6.2.1 输入参数alter proc proc_b@id intasdelete from bb where goselect * from bbexec proc_b 1goexec proc_b @id=1godeclare @inid int--set @inid=1set @inid=(select b1 from bb where b2=4)--exec proc_b @inidexec proc_b @id=@inidgo--6.2.2 输出参数select * from booksgoselect sum(price*qty) from books where pub='清华'gocreate proc p_books_out@pub varchar(10),@sum money outputasselect @sum=sum(price*qty) from books where goexec p_books_out '清华'godeclare @sum_out moneyexec p_books_out '清华',@sum_out outputprint @sum_outgodeclare @pub_in varchar(10),@sum_out money--set @pub_in='清华'set @pub_in=(select pub from books where books_name='数据结构')exec p_books_out @pub_in,@sum_out outputprint '出版社为:'+@pub_in+'的书一共卖了：'+cast(@sum_out asvarchar(10))+'元'godeclare @pub_in varchar(10),@sum_out moneyset @pub_in='机械'--set @pub_in=(select pub from books where books_name='数据结构')exec p_books_out @pub=@pub_in,@sum=@sum_out outputprint '出版社为:'+@pub_in+'的书一共卖了：'+cast(@sum_out asvarchar(10))+'元'go--6.2.3 加密exec sp_helptext p_books_outgoalter proc p_books_out@pub varchar(10),@sum money outputwith encryption--加密asselect @sum=sum(price*qty) from books where goselect text from syscomments whereid=object_id('p_books_out')go--6.3 创建sp开头的存储过程use mastergocreate proc sp_testasprint 'this is in test db'gouse testgoexec sp_testgo--6.4 默认值参数create proc p_def@i int=110,@s varchar(20)='hello'asprint cast(@i as char(10))+@sgoexec p_def @i=999,@s='ok'go--7. 重命名和删除sp_rename p_def,p_def_newgodrop proc p_defgocreate table orders(s_name& varchar(10),s_course varchar(20),s_classid varchar(10),s_price smallmoney)goselect * from ordersinsert into orders values('张三','asp','a1',1500)insert into orders values('李四','asp','a1',1400)insert into orders values('王五','asp','a2',1600)insert into orders values('孙六','jsp','j1',1800)insert into orders values('赵起','jsp','j1',1900)insert into orders values('胡八','jsp','j1',2000)go--根据 s_course 和 s_classid查询出学习asp(jsp)这门课程在a1(j1)班里的有几个学员;交费总金额是多少alter proc selectorder@course varchar(20),@classid varchar(10),@count int output,@sumsmallmoney outputasselect @count=count(*) from orders where
and select @sum=sum(s_price) from orders where
and godeclare @count_out int,@sum_out smallmoneyexec selectorder 'jsp','j1',@count_out output,@sum_out outputprint @count_outprint @sum_outgo---使用对称密钥加密列---USE AdventureWGO--If there is no master key, create one nowIF NOT EXISTS&&& (SELECT *FROM sys.symmetric_keys WHERE symmetric_key_id = 101)&&& CREATEMASTER KEY ENCRYPTION BY&&& PASSWORD ='23987hxJKL95QYV4369#ghf0&#GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'GOCREATE CERTIFICATE HumanResources037&& WITH SUBJECT = 'EmployeeSocial Security Numbers';GOCREATE SYMMETRIC KEY SSN_Key_01&&& WITHALGORITHM = AES_256&&& ENCRYPTIONBY CERTIFICATE HumanResources037;GOUSE [AdventureWorks];GOselect * from HumanResources.Employee-- Create a column in which to store the encrypted dataALTER TABLE HumanResources.Employee&&& ADDEncryptedNationalIDNumber varbinary(128);GO-- Open the symmetric key with which to encrypt the dataOPEN SYMMETRIC KEY SSN_Key_01&& DECRYPTION BY CERTIFICATEHumanResources037;-- Encrypt the value in column NationalIDNumber withsymmetric-- key SSN_Key_01. Save the result in columnEncryptedNationalIDNumber.UPDATE HumanResources.EmployeeSET EncryptedNationalIDNumber =EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);GO-- Verify the encryption.-- First, open the symmetric key with which to decrypt thedataOPEN SYMMETRIC KEY SSN_Key_01&& DECRYPTION BY CERTIFICATEHumanResources037;GO-- Now list the original ID, the encrypted ID, and the-- decrypted ciphertext. If the decryption worked, theoriginal-- and the decrypted ID will match.SELECT NationalIDNumber, EncryptedNationalIDNumber&&& AS"Encrypted ID Number",&&&CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))&&& AS"Decrypted ID Number"&&& FROMHumanResources.EGO------------------------------------------------------------自定义函数-----1.函数 过程--返回值不同，存储过程返回整型（0 成功/1 失败）；函数可以返回任何数据类型--函数没有output类型的参数返回数据--函数不可以更改数据库的状态，只能读取；过程可以更改数据库状态。--2. 分类：标量；表值型；多语句表--2.1 标量--返回一个确定数据类型的标量值use testgocreate function adds(@a int,@b int)returns intasbegin&& return @a+@bendgoselect dbo.adds(2,3)goselect getdate()gocreate function getdates()returns varchar(10)asbegin&& return (selectconvert(varchar(10),getdate(),120))endgoselect dbo.getdates()gocreate function gettoday(@now datetime)returns varchar(10)asbegin& return convert(varchar(10),@now,120)endgoselect dbo.gettoday(' 08:00:05')goselect dbo.gettoday(getdate())go--练习：利用标量函数实现字段的自动增加create table nn(n int)goselect * from nninsert into nn values(dbo.f_auto())gocreate function f_auto()returns intasbegin& declare @a int& select @a=max(n) from nn& if @a is null&&begin&&&&&& set@a=0&&& set@a=@a+1&&& return@a&& end& else&&& set@a=@a+1&&& return@aendgo--2.2 表值(执行时把函数当表来处理)--2.2.1 表值create function f_emp(@name varchar(10))returns tableasreturn (select * from emp where )goselect * from dbo.f_emp('张三')godeclare @s varchar(10)set @s='李四'select * from dbo.f_emp(@s)go--2.2.2 多语句表create function ft()returns @tab table(tid varchar(6),tname varchar(10))asbegin&& insert into @tabvalues('001','hello')&& insert into @tab selectpid,pname from prod&& returnendgoselect * from dbo.ft()go--补充：return 返回存储过程select * from emp where emp_id='5623'select @@rowcountgoalter proc p_empasdeclare @n intupdate emp set emp_name='胡八' where emp_id='05632'set @n=@@rowcountif @n=0& return 0else& return @ngoexec p_empgodeclare @a intexec @a=p_empif @a=0& print 'no data'else& print 'the rowcount is :'+cast(@a asvarchar(10))go---触发器-----分类：DDL触发器（新增加） 只有after 触发器/DML触发器（原来sql server 2000 有的）--sql server 2005新增加了DDL触发器（create/alter/drop/grant/deny/revoke）--原来的触发器都是作用在表或者视图；sql server 2005 触发器可以作用在数据库上。CREATE TRIGGER safetyON DATABASEFOR DROP_TABLE, ALTER_TABLEAS&& PRINT 'You must disableTrigger "safety" to drop or alter tables!'&& ROLLBACK;CREATE TRIGGER ddl_trig_loginON ALL SERVERFOR DDL_LOGIN_EVENTSAS&&& PRINT 'LoginEvent Issued.'&&& SELECTEVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')GODROP TRIGGER ddl_trig_loginON ALL SERVERGO;Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATESTATISTICS statement (DDL Trigger)CREATE TRIGGER trigger_nameON { ALL SERVER | DATABASE }[ WITH &ddl_trigger_option& [ ,...n ]]{ FOR | AFTER } { event_type | event_group } [ ,...n ]AS { sql_statement& [ ; ] [ ...n ] | EXTERNAL NAME& method specifier&& [ ; ] }&ddl_trigger_option& ::=&&& [ ENCRYPTION]&&& [ EXECUTE ASClause ]&method_specifier& ::=&&&assembly_name.class_name.method_namecreate trigger t_createon databasefor create_table--afterasprint 'create table event'gocreate table trig(t1 int ,t2 int)go--1.概述：触发器是一种特殊的存储过程，靠触发才能执行，而存储过程必须调用--2.作用：完成 主/外键所不能保证的，复杂的参照完整性和数据一致性；--完成比check约束更复杂的数据完整性；级联更新/删除--3.如何触发：insert update delete--4.作用对象：主要应用在表上；还可以应用在视图--5.分类--5.1 after (for)--执行完原来的操作后才触发，只能定义在表上（sql server 2000）一个表上可以定义多个触发器;--sql server 2005 中 可以定义在数据库/服务器上(DDL触发器)--5.2 instead of--执行某个操作时触发，这时原来的操作不执行，转而执行触发器本身；可以定义在表或者视图上，只能定义一个。--6.创建触发器--6.1 for/afterselect * from bb--创建create trigger tr_bbon bbfor insert--after insertasprint '有数据插入表bb'goinsert into bb values(20,50)go--察看exec sp_helptext 'tr_bb'go--加密alter trigger tr_bbon bbwith encryption--加密--for insertafter insertas--print '有数据插入表bb'raiserror('有数据插入表bb',16,1)goinsert into bb values(40,60)go--练习1.不能删除多条记录select * from bbgocreate trigger tr_bb_del on bbfor deleteasif (select count(*) from deleted)&1begin& raiserror('不能同时删除多条记录',16,1)& rollback tranendgodelete from bbgodelete from bb where b1=15go--rollback tran 回滚事务--练习2：不能插入多条记录create table aaa(a1 int,a2 int)goinsert into aaa values(10,11)goselect * from aaacreate table bbb(b1 int,b2 int)goinsert into bbb values(1,1)insert into bbb values(2,2)insert into bbb values(3,3)create trigger t_aaa on aaafor insertasif (select count(*) from inserted)&1begin&&raiserror('不允许同时插入多条记录！',16,1)&& rollback tranendgoselect * from aaainsert into aaa values(20,21)insert into aaa select * from bbbgo--练习3：当表中只有一条记录时不允许删除select * from bbbcreate trigger t_bbb_del on bbbfor deleteasif (select count(*) from bbb)=0begin& raiserror('表中只有一条记录时不允许删除',16,1)& rollback tranendgodelete from bbb where b1=3go--练习4：不允许删除/修改表中的某一条记录select * from usersgocreate trigger t_users on usersfor delete,updateasif exists(select username from deleted whereusername='admin')begin&&raiserror('不允许删除/修改admin!',16,1)&& rollback tranendgoupdate users set username='hello' where userid='002'update users set username='ok' where userid='001'delete from users where userid='001'--6.2 instead ofcreate trigger t_bbb_insert on bbbinstead of insertasprint '看看数据插入表中了没有！'goinsert into bbb values(101,202)goselect * from bbb--表的级联删除/更新create table mm(m1 varchar(10),m2 varchar(10))godrop table nngocreate table nn(n1 varchar(10),n2 varchar(10))goselect * from mmselect * from nnselect * from mm,nn where mm.m1=nn.n1go--级联删除create trigger t_mm_del on mmfor deleteasdelete from nn where n1 in(select m1 from deleted)godelete from mm where m1='abc'go--级联更新create trigger t_mm_update on mmfor updateasupdate nn set n1=(select m1 from inserted) where n1=(select m1 fromdeleted)--update nn set n1=i.m1 from inserted i, deleted d wheren1=d.m1goupdate mm set m1='as' where m1='sa'goselect * from mmselect * from nn--视图的多表更新create view e_dasselect emp_id,emp_name,dept.dept_id,dept_name from emp,dept whereemp.dept_id=dept.dept_idgoselect * from e_dinsert into e_d values('007','test','08','haha')goselect * from empselect * from deptcreate trigger tr_view on e_dinstead of insertasinsert into dept select dept_id,dept_name from insertedinsert into emp(emp_id,emp_name,dept_id) selectemp_id,emp_name,dept_id from insertedif @@error!=0begin&& raiserror('find someproblem',16,1)&& rollback tranendgo--游标--select * from emp where emp_id='001'--类型：静态游标；动态游标；键集驱动游标；只进游标--使用：close--declaredeclare emp_cursor cursor forselect emp_id,emp_name from empgo--openopen emp_cursorgo--fetchfetch next from emp_cursor--closeclose emp_cursorgo--提取所有行fetch next from emp_cursorwhile @@fetch_status=0begin&& fetch next fromemp_cursorendgoclose emp_cursor--只读游标declare emp_cursor_readonly cursor forselect emp_id,emp_name from empfor read onlygoopen emp_cursor_readonlyfetch next from emp_cursor_readonlyupdate emp set emp_name='ok' where current ofemp_cursor_readonlygodelete emp where current of emp_cursor_readonlygo--可更新游标declare emp_cursor_update cursor forselect emp_id,emp_name from empfor updategoopen emp_cursor_updatefetch next from emp_cursor_updateupdate emp set emp_name='ok' where current ofemp_cursor_updategoselect * from emp--删除游标deallocate emp_cursor_updategofetch last from emp_cursor_update--SCROLLdeclare emp_scroll scroll cursor forselect emp_id,emp_name from empgoopen& emp_scrollfetch next from emp_scrollfetch PRIOR from emp_scrollfetch last from emp_scrollfetch first from emp_scroll--RELATIVEfetch relative 2 from emp_scrollfetch ABSOLUTE -3 from emp_scroll--事务----1.什么是事务：由一些列语句构成的逻辑工作单元--2.将SQL语句组合成一个事务，目标是保证这一组语句能够得到可靠的执行。--如果阻止了其中的某条语句的执行，则返回原来的状态--3.依靠日志工作--4.事务的特性：原子性；一致性；隔离性；持久性--5.控制事务--5.1 启动事务：begin transaction/tran(ADO trans)--5.2 结束事务：commit transaction/tran/rollbacktransaction/tran/work--6.事务的三种模式--6.1 显示事务select * from empgobegin transaction emp_tranupdate emp set emp_name='aaa' where emp_id='007'commit transaction--rollback transactiongobegin trandelete from emp where emp_id='007'rollback tran--commit trangoselect * from mupdate m set mon=mon-25 where username='a'update m set mon=mon+25 where username='b'delete from minsert into m values('a',30)insert into m values('b',30)begin tranupdate m set mon=mon-25 where username='a'update m set mon=mon+25 where username='b'if @@error=0& commit tranelse& rollback trango--6.2 自动提交事务create table trans(t1 int primary key,t2 int)goinsert into trans values(1,1)insert into trans values(1,2)select * from trans--SSIS----sql server intergration service--包装DTS(sql server 2000)--比DTS快7倍--大量数据的导入/导出--bcp-------------------------------------------------------数据库的备份和恢复-----1.备份--1.1 物理备份--数据库分离sp_detach_db--1.2 逻辑备份--1.2.1 完全备份--和SQL SERVER 2000 不同，报告错误：未备份数据库的日志尾部--还原时 选中 覆盖原来数据库backup database test to test_backgo--1.2.2 差异备份backup database test to test_back with differentialgo--完全+差异备份，只能通过增加备份次数，尽量减少数据的丢失，不能恢复到时间点。--1.2.3 事务日志backup log test to test_back--完全+事务日志 可以恢复到失败的点。（推荐使用）--1.2.4 文件和文件组--2.恢复--2.1 物理--数据库附加sp_attach_db--2.2 逻辑use testgorestore database master from master_backgo--3.创建备份设备--3.1 向导--服务器对象--〉备份设备--3.2 T-SQL--exec sp_addumpdevice 'disk/tape','逻辑名称','物理路径名称'--创建exec sp_addumpdevice 'disk','testbackup','c:\sql2005\backup\testbackup.bak'go--删除exec sp_dropdevice 'testbackup'go--数据库快照--创建CREATE DATABASE AdventureWorks_dbss1800 ON( NAME = AdventureWorks_Data, FILENAME ='C:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )AS SNAPSHOT OF AdventureWGO--RESTORE DATABASE &database_name&FROM DATABASE_SNAPSHOT =&database_snapshot_name&--其中，&database_name&是源数据库的名称，&database_snapshot_name&是要将数据库恢复到的快照的名称。--请注意，必须在此语句中指定快照名称而非备份设备。--恢复 AdventureWorks 数据库的快照USE-- Reverting AdventureWorks to AdventureWorks_dbss1800RESTORE DATABASE AdventureWorks fromDATABASE_SNAPSHOT = 'AdventureWorks_dbss1800';GO---凭据---select * from sys.credentials--创建CREATE CREDENTIAL xyz WITH IDENTITY = 'zhoujie\userb', SECRET ='123456'GOselect * from sys.credentials---将该凭据映射到 SQL Server 登录名alter login ok with credential=xyzgocreate login hello with go--删除DROP CREDENTIAL credential_namego--非对称密钥CREATE ASYMMETRIC KEY PacificSales09&&& WITHALGORITHM = RSA_2048&&& ENCRYPTIONBY PASSWORD = 'bmsA$dk7i82bv55foajsd9764';GOcreate asymmetric key feimiyaowith algorithm=RSA_1024encryption by goselect * from ss--创建主密钥CREATE MASTER KEY ENCRYPTION BY PASSWORD = go--创建证书CREATE CERTIFICATE bb_c01&& WITH SUBJECT = 'use inbb'GO--创建对称密钥CREATE SYMMETRIC KEY bb_s01&&& WITHALGORITHM = AES_256&&& ENCRYPTIONBY CERTIFICATE bb_c01GO--打开OPEN SYMMETRIC KEY bb_s01DECRYPTION BY CERTIFICATE bb_c01--加密UPDATE ss SET s3 = EncryptByKey(Key_GUID('bb_s01'), s1) wheres1='fdg'GOalter table ss add s3 varbinary(128)go--EncryptByKey& 使用对称密钥加密数据select * From ss--解密select convert(varchar(10),DecryptByKey(s3)) from ss wheres1='fdg'go---加密create table users_jiami(userid varchar(10),username varchar(20),userpwd varbinary(128))go--创建主密钥CREATE MASTER KEY ENCRYPTION BY PASSWORD = go--创建证书CREATE CERTIFICATE bb_c01&& WITH SUBJECT = 'use inbb'GO--创建对称密钥CREATE SYMMETRIC KEY bb_s01&&& WITHALGORITHM = AES_256&&& ENCRYPTIONBY CERTIFICATE bb_c01GO--打开OPEN SYMMETRIC KEY bb_s01DECRYPTION BY CERTIFICATE bb_c01insert into users_jiamivalues('001','zhou',EncryptByKey(Key_GUID('bb_s01'),'zhou_pwd'))goselect * from users_jiami&&文章出处：北大青鸟航天桥田老师所属分类：→&&&&作者：新浪博客&&&&时间： 0:00:00
All Right Reserved}