最近有些时间 所以就复习了下数据库 sqlserver 呵呵 希望对大家有所帮助
建表
CREATE TABLE [dbo].[USERS] (
[id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[password] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[USERS_BACK] (
[id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[password] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
存储过程
CREATE proc insert_user
@id varchar(50),@username varchar(50),@password varchar(50) output
with recompile
as
insert users(id,username,password) Values(@id,@username,@password)
insert users(id,username,password) Values(@id+1,@username,@password)
insert users(id,username,password) Values(@id+2,@username,@password)
GO
触发器
例子1
Create Trigger t_d_user
On USERS--在Student表中创建触发器 for Update--为什么事件触发As--事件触发后所要做的事情
for Delete
As
insert into USERS_BACK(id,username,password)
select d.id,d.username,d.password from deleted d
例子2
Create Trigger t_i_user
On USERS--在Student表中创建触发器 for Update--为什么事件触发As--事件触发后所要做的事情
for Insert
As
insert into USERS_BACK(id,username,password)
select d.id,d.username,d.password from inserted d
drop Trigger t_d_user
select * from users
select * from users_back
Delete from users where id='2'
insert into USERS_BACK(id,username,password)
select id,username,password from users where id =2
Delete from USERS_BACK where id='1' or id='2' or id='3'
--交集 结果相同 内连接
select ub.* from users u,users_back ub where u.id=ub.id
select * from users u inner join users_back ub on u.id=ub.id
select * from users u where u.id in(select ub.id from users_back ub)
--左外连接 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
select * from users u left outer join users_back ub on u.id=ub.id
select * from users_back ub left outer join users u on u.id=ub.id
--右外连接 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
select * from users u right outer join users_back ub on u.id=ub.id
select * from users_back ub right outer join users u on u.id=ub.id
--全外连接
select * from users u full outer join users_back ub on u.id=ub.id
select * from users_back ub full outer join users u on u.id=ub.id
--交叉连接（笛卡尔乘积）
select * from users u cross join users_back ub where u.id=ub.id
--not in
select * from users u where u.id not in (select ub.id from users_back ub)
--用左外连接代替not in
select u.* from users u left outer join users_back ub on u.id=ub.id where ub.id is null
--exists and in
select * from users u where EXISTS (select * from users_back ub where u.id=ub.id)
select * from users u where u.id in (select ub.id from users_back ub)
select u.* from users u,users_back ub where u.id=ub.id
select * from users u where not EXISTS (select * from users_back ub where u.id=ub.id)
select * from users u where u.id not in (select ub.id from users_back ub)
select u.* from users u left outer join users_back ub on u.id=ub.id where ub.id is null
select * from users u
union
select * from users_back ub
select * from users u
intersect
select * from users_back ub anb
select * from users u full outer join users_back ub on u.id=ub.id
循环插入
declare @i int [email protected]
set @i = 1 [email protected]
while @i < 100 [email protected],这里改成100万即是插入100万条记录
begin
insert into users (id,username,password) values(@i,'name'+convert(varchar,@i),@i/3)
set @i = @i + 1
end