TSQL笔记

case 关键字的使用：

语法：

Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END

示例：

select id, name,
case 
when id = 1 then '111'
else 'ee'
end as 'qi'
from test2

while关键字的使用

语法：

WHILE Boolean_expression 
     { sql_statement | statement_block } 
     [ BREAK ] 
     { sql_statement | statement_block } 
     [ CONTINUE ] 
     { sql_statement | statement_block } 
示例：
Declare @ii int
Set @ii = 3
while @ii<5
begin
insert into Test2 values(@ii, @ii);
Set @ii = @ii + 1
end
Create关键字的使用
语法：
CREATE TABLE 
    [ database_name . [ schema_name ] . | schema_name . ] table_name 
        ( { <column_definition> | <computed_column_definition> }
        [ <table_constraint> ] [ ,...n ] ) 
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
        | "default" } ] 
    [ { TEXTIMAGE_ON { filegroup | "default" } ] 
[ ; ]

示例：
Create Table Test2(
id nvarchar(50) not null primary key,
name nvarchar(50) not null,
PID int identity(1,1)
)
一个表的数据插入更新到另一个表中：

UPDATE YuDean SET Order1 = (SELECT Widght FROM YuDean2 WHERE YuDean.id = YuDean2.ID)

if关键字的使用

if @i % 2 = 0
begin
end
else
begin
end

删除主键

select * from sysobjects where xtype='PK'
alter table Student drop PK__Student__3214EC277F60ED59