Code create/drop/alter/use/execute create table [tb_UserInfo] ( [UserID] [uniqueidentifier] NOT NULL DEFAULT (newid()) PRIMARY KEY, [UserName] [nvarchar](50) NOT NULL, [PassWord] [nvarchar](50) NOT NULL, ) create table [tb_Message] ( [MessageID] [uniqueidentifier] NOT NULL DEFAULT (newid()) PRIMARY KEY, [UserID] [uniqueidentifier] NOT NULL, [MessageBody] [nvarchar](max) NOT NULL, [PostTime] [datetime] NOT NULL, ) --单表操作 truncate table [tb_UserInfo] --Clear insert into [tb_UserInfo] ([UserName], [PassWord]) values ('a','a') insert into [tb_UserInfo] ([UserName], [PassWord]) values ('b','b') insert into [tb_UserInfo] ([UserName], [PassWord]) values ('c','c') alter table [tb_UserInfo] add Column_test int--drop column alter table [tb_UserInfo] drop column Column_test select * from [tb_UserInfo] where [UserName] not in ('a','b') --between and/like%/<>/用()组成复杂条件 select count(distinct [UserName]) from [tb_UserInfo] select [UserName] from [tb_UserInfo] group by [UserName] having [UserName] <>'a'--有group by就不能有where,而用having作为限制 --多表联查 (select [UserID] from [tb_UserInfo]) union all (select [UserID] from [tb_Message]) --讲2个语句的结果合并(只union会消除重复)/except/intersect取交集 select * from [tb_UserInfo], [tb_Message] where [tb_UserInfo].[UserID] <> [tb_Message].[UserID] --等值联合和不等值联合 select * from [tb_UserInfo] inner join [tb_Message] on [tb_UserInfo].[UserID] = [tb_Message].[UserID] --内部联合和外部联合/left、right以相应的为主,另一边没有则null -- begin transaction trans_name update [tb_UserInfo] set [UserName] ='d', [PassWord] ='d'where [UserName] ='b' delete from [tb_UserInfo] where [UserName] ='c' commit --能执行的都执行/rollback全能执行才执行 Create Trigger [tri_name] On [tb_UserInfo] for Update, insert, delete As if Update([UserName]) begin select [UserName] +','+ [UserName] from [tb_UserInfo] end select * from sysobjects where xtype='TR'--查看触发器 alter table [tb_UserInfo] enable trigger [tri_name] -- disable create view [view_UserInfo] as select * from [tb_UserInfo] --视图仅用于简化复杂查询可增删改但限制很多 create unique clustered index [index_UserInfo] on [tb_UserInfo]([UserName], [PassWord]) -- unique = distinct/clustered群集 create table #temp (a int) --放在tempdb中/与session类似所以并发操作安全/##全局临时表,用于不同session共享 --游标和简单编程 declare cursor_name scroll cursor for select [UserID] from [tb_UserInfo] for update open cursor_name declare @a [nvarchar](50) fetch cursor_name into @a while(@@fetch_status =0) begin print @a fetch cursor_name into @a end close cursor_name deallocate cursor_name --scroll/insensitive 否则只读向前/在临时表中创建不影响原有数据 --for read only/update create procedure [dbo].[sp_test] ( @UserName [nvarchar](50), @PassWord [nvarchar](50) output ) as select @PassWord = [PassWord] from [tb_UserInfo] where [UserName] = @UserName execute sp_test 'd','f' drop procedure sp_test
posted on
2008-09-11 17:33hbfly
阅读(654)
评论(4)
编辑收藏举报