管理
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] --Clearinsert 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 columnalter table [tb_UserInfo] drop column Column_testselect * 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_nameupdate [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] -- disablecreate 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 updateopen cursor_namedeclare @a [nvarchar](50)fetch cursor_name into @awhile(@@fetch_status = 0)begin print @a fetch cursor_name into @aendclose cursor_namedeallocate cursor_name --scroll/insensitive 否则只读向前/在临时表中创建不影响原有数据--for read only/updatecreate procedure [dbo].[sp_test] ( @UserName [nvarchar](50), @PassWord [nvarchar](50) output )as select @PassWord = [PassWord] from [tb_UserInfo] where [UserName] = @UserNameexecute sp_test 'd','f'drop procedure sp_test