Simplest SQL tutorial

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,
)
-- Single table operation
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 %/<>/use () to form a complex condition
Select count (distinct [username]) from [tb_userinfo]
Select [username] from [tb_userinfo] group by [username] Having [username] <> 'A' -- having group by is not allowed, whereas having is used as the limit.
-- Multi-table join query
(Select [userid] from [tb_userinfo]) Union all (select [userid] from [tb_message]) -- merge the results of two statements (only union will eliminate duplicates) /intersection t/intersect
Select * from [tb_userinfo], [tb_message] Where [tb_userinfo]. [userid] <> [tb_message]. [userid] -- equivalent union and unequal Union
Select * from [tb_userinfo] inner join [tb_message] on [tb_userinfo]. [userid] = [tb_message]. [userid] -- Internal union and external Union/left and right are dominated by the corresponding ones, while null is not supported on the other side.
--
Begin transaction trans_name
Update [tb_userinfo] Set [username] = 'D', [Password] = 'D' where [username] = 'B'
Delete from [tb_userinfo] Where [username] = 'C'
Commit -- execute all executable tasks/rollback all-around execution.

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' -- view the trigger
Alter table [tb_userinfo] Enable trigger [tri_name] -- disable
Create view [view_userinfo] as select * from [tb_userinfo] -- View is only used to simplify complex queries.
Create unique clustered index [index_userinfo] on [tb_userinfo] ([username], [Password]) -- Unique = distinct/clustered Cluster
Create Table # temp (a int) -- put it in tempdb/It is similar to session, so the concurrent operation is safe. // # global temporary table for sharing with different sessions
-- Cursor and simple programming
Declare cursor_name scroll cursor for select [userid] from [tb_userinfo] for update
Open cursor_name
Declare @ A [nvarchar] (50)
Fetch cursor_name into @
While (@ fetch_status = 0)
Begin
Print @
Fetch cursor_name into @
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

Note: Reference from http://www.cnblogs.com/hbreset/archive/2008/09/11/1289351.html