SQL2005 回顾

set statistics time on--开启客户端分析 on/off
set nocount on--不返回计数 on/off
set statistics profile  off--用于分析SQL 语句 on/off

use master
go
if exists(select * from sysdatabases where name = 'msstudy')
drop database msstudy
go
create database msstudy
go
use msstudy
go
create 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_first
go
create procedure pro_first
as
	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)
	end
go 
exec pro_first;

select * from t_user
select * from t_role
select * from t_user_role

exec 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 num
select row_number() over(order by username asc) as num,userid ,username from t_user where userid < 10 order by num
select rank() over(order by username asc) as num,userid ,username from t_user where userid < 10 order by num
select 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 < 10
select 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

--union
select username from t_user where userid < 5
union
select username from t_user where userid >= 5 and userid < 10
--union all 注意和 union 的区别
select username from t_user where userid < 5
union all
select username from t_user where userid >= 5 and userid < 10
--intersect 注意它们都有distinct的作用
select username from t_user where userid < 8
intersect 
select username from t_user where userid > 3 and userid < 10
--except 注意它们都有distinct的作用
select username from t_user where userid < 8
except 
select username from t_user where userid > 3 and userid < 10

--事务  多用于触发器,存储过程
begin transaction 
update t_user set username = '小米' where userid < 10
select * from t_user where userid < 10
rollback 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 int
declare @username varchar(20)
declare @userage int
fetch next from cursor_info into @userid, @username,@userage
select @userid, @username,@userage
--关闭游标
close cursor_info
--删除游标
deallocate cursor_info

sp_help t_role--查看表信息

--查询identity 值
select @@identity
--查询所有数据库
exec sp_databases



--创建索引
if exists(select * from sysobjects where name = 'index_role')
drop index t_role.index_role 
go
create index index_role --unique 唯一索引,clustered 聚集索引(主键包括聚集索引一张表只能有一个聚集索引),noclustered 非聚集索引
on t_role(rolename)
--创建视图
if exists(select * from sysobjects where name ='view_info')
drop view view_info
go
create view view_info
--添加 with encryption 用于加密视图
as
select 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.roleid
go
select * from view_info

--创建存储过程
create procedure pro_role
--无参
as
 select * from t_role
go
exec pro_role
if exists(select * from sysobjects where name = 'pro_invalue')
drop proc pro_invalue
go
--入参(含默认值)
create procedure pro_invalue @inFirst int = 4,@inSecond int = 14
as
	select * from t_user where userid = @inFirst or userid = @inSecond
go
exec pro_invalue
exec pro_invalue default,51--后一个参数需指定
create proc pro_outvalue @out int output
as
 select @out = max(userid) from t_user 
go
declare @num int
exec pro_outvalue @num output--注意output
select @num

--触发器
create trigger tri_role on t_role
for insert
as
begin
select * from inserted 
-- delect 时查询 deleted  而update 分别查询 deleted ,inserted 
end
go
insert into t_role values ('aa')
--instead of 触发器  它并不执行操作,而是执行触发器本身
create trigger tri_user on t_user
instead of delete
as
begin
	select * from deleted
end
go
delete t_user where userid = 1--这里delete 操作并没有提交
select * from t_user where userid < 10