SQL2005 回望

set statistics time on--开启客户端分析 on/offset nocount on--不返回计数 on/offset statistics profile  off--用于分析SQL 语句 on/offuse mastergoif exists(select * from sysdatabases where name = 'msstudy')drop database msstudygocreate database msstudygouse msstudygocreate table t_user(	userid int primary key identity(1,1),	userName varchar(20) not null,	userAge int)create table t_role(	roleid int primary key identity(1,1),	roleName varchar(20) not null)create table t_user_role(	infoid int primary key identity(1,1),	userid int references t_user(userid) not null, 	roleid int references t_role(roleid) not null)insert into t_user values('小胖',16);insert into t_user values('大胖',16);insert into t_user values('胖子',41);insert into t_user values('胖胖',21);select @@identity --查询当前 identity 值insert into t_role values('google');insert into t_role values('JavaEye');insert into t_role values('CSDN');insert into t_role values('isw');insert into t_user_role values(1,1);insert into t_user_role values(1,2);insert into t_user_role values(3,2);insert into t_user_role values(3,3);if exists(select * from sysobjects where name = 'pro_first')drop procedure pro_firstgocreate procedure pro_firstas	declare @count int,@temp int	set @count = 100000;	while(@count > 0)	begin		insert into t_user values ('测试',24)		select @temp = @@identity,@count = @count - 1		insert into t_user_role values(@temp,4)	endgo exec pro_first;select * from t_userselect * from t_roleselect * from t_user_roleexec sp_help t_user --用于分析相关对象select distinct username from t_user -- distinct 用于去除重复项select username from t_user group by username --这里的gourp by 达到了和  distinct 同样的效果--compute 用于汇总查询,注意在与order by 合用时 compute by 要一致select userid ,username from t_user where userid < 10 compute count(username) compute sum(userid) select userid ,username from t_user where userid < 10 order by username compute count(username) by username--row_number(),rank(),dense_rank()  用于分组排序,注意区分它们的不同select row_number() over(partition by username order by username) as num,userid ,username from t_user where userid < 10 order by numselect row_number() over(order by username asc) as num,userid ,username from t_user where userid < 10 order by numselect rank() over(order by username asc) as num,userid ,username from t_user where userid < 10 order by numselect dense_rank() over(partition by username order by username) as num,userid ,username from t_user where userid < 10 order by num--over 用于代替子查询select userid, count(userid) over(partition by username) as user_count,username from t_user where userid < 10select userid, (select count(userid) from t_user where username = tu.username and userid < 10) as user_count,username  from t_user as tu where  userid < 10--unionselect username from t_user where userid < 5unionselect username from t_user where userid >= 5 and userid < 10--union all 注意和 union 的区别select username from t_user where userid < 5union allselect username from t_user where userid >= 5 and userid < 10--intersect 注意它们都有distinct的作用select username from t_user where userid < 8intersect select username from t_user where userid > 3 and userid < 10--except 注意它们都有distinct的作用select username from t_user where userid < 8except select username from t_user where userid > 3 and userid < 10--事务  多用于触发器,存储过程begin transaction update t_user set username = '小米' where userid < 10select * from t_user where userid < 10rollback transaction --用于回滚事务--commit transaction 用于提交事务select * from t_user where userid < 10--建立临时表with temp_table (infoid,roleinfo,userinfo)as(select infoid , tu.username as username, tr.rolename as rolename from t_user_role as tur ,t_user as tu,t_role as tr where infoid < 10 and tur.userid = tu.userid and tur.roleid = tr.roleid)select * from temp_table--定义一个游标declare cursor_info cursor for select * from t_user where userid < 10--打开游标open cursor_info--读取内容declare @userid intdeclare @username varchar(20)declare @userage intfetch next from cursor_info into @userid, @username,@userageselect @userid, @username,@userage--关闭游标close cursor_info--删除游标deallocate cursor_infosp_help t_role--查看表信息--查询identity 值select @@identity--查询所有数据库exec sp_databases--创建索引if exists(select * from sysobjects where name = 'index_role')drop index t_role.index_role gocreate index index_role --unique 唯一索引,clustered 聚集索引(主键包括聚集索引一张表只能有一个聚集索引),noclustered 非聚集索引on t_role(rolename)--创建视图if exists(select * from sysobjects where name ='view_info')drop view view_infogocreate view view_info--添加 with encryption 用于加密视图asselect infoid ,tr.rolename ,tu.username from t_user_role as tur ,t_user as tu ,t_role as tr where infoid < 10 and tur.userid = tu.userid and tur.roleid = tr.roleidgoselect * from view_info--创建存储过程create procedure pro_role--无参as select * from t_rolegoexec pro_roleif exists(select * from sysobjects where name = 'pro_invalue')drop proc pro_invaluego--入参(含默认值)create procedure pro_invalue @inFirst int = 4,@inSecond int = 14as	select * from t_user where userid = @inFirst or userid = @inSecondgoexec pro_invalueexec pro_invalue default,51--后一个参数需指定create proc pro_outvalue @out int outputas select @out = max(userid) from t_user godeclare @num intexec pro_outvalue @num output--注意outputselect @num--触发器create trigger tri_role on t_rolefor insertasbeginselect * from inserted -- delect 时查询 deleted  而update 分别查询 deleted ,inserted endgoinsert into t_role values ('aa')--instead of 触发器  它并不执行操作,而是执行触发器本身create trigger tri_user on t_userinstead of deleteasbegin	select * from deletedendgodelete t_user where userid = 1--这里delete 操作并没有提交select * from t_user where userid < 10