SQL 大全

新增
不加欄位名稱必須填入所有值可用NULL或default
INSERT [ad].[dbo].[trade] VALUES(3,'susan',default)

正常都加欄位名比較方便
INSERT [ad].[dbo].[trade]
(id,name)
VALUES(4,'eric')

另外是插入別的資料表的值
INSERT [ad].[dbo].[trade]
(id,name)
select id,name
from trade
where id < 3
order by id
不喜歡用...因為欄位順序和類型要一樣

修改
UPDATE [ad].[dbo].[trade]
SET [id] +=1
WHERE name='jack'

修改特定範圍
UPDATE [ad].[dbo].[trade]
SET [id] +=1
from trade c inner join class p on
c.id = p.age
WHERE c.id = 4

刪除
DELETE FROM [ad].[dbo].[trade]
WHERE id NOT IN (select id from trade)
也可以特定範圍或是用子查詢

查詢
select count(*) AS count from trade
where 1=0 false
group by ALL id 全部分組
HAVING count(*) > 5 事後篩選
order by id DESC

select distinct name 移除重複
into #class 插入暫存資料表
from trade
where id = 1

TOP
declare @per float
set @per = 1
select top (@per) PERCENT *
from ad.dbo.trade

資料正規化
SELECT *
from trade
pivot (count(type) for type in ([girl])) as b
把欄位內容轉成欄位名稱 UNPIVOT則相反
unpivot (typevalue for type in ([girl])) as b

找出存在於trade卻沒有再class
select name from trade
EXCEPT
select name from class
欄位數目要相同

select name from trade
INTERSECT
select name from class
同時符合2個資料表


SELECT grouping(name),SUM(id),name
FROM [ad].[dbo].[trade]
group by name
with cube 統計總數

SELECT name,SUM(id)
FROM [ad].[dbo].[trade]
group by name
with rollup 階層查詢依據group by的順序


declare @error int
begin tran 顯性交易開始

if(@error <> 0) goto error

commit tran 釋放資源
error: 副程式寫法
if @error <> 0
begin
rollback transaction
end

轉型

convert(char(4),2005)
cast(name as char(4))

次序函數

row_number() over (order by name)

當科系不同時會重新計數
row_number() over (partition by 科系 order by name)

rank() over (order by name)
欄位內容若相同則排名一樣且會跳過下一號
dense_rank() over (order by name)
這個不會跳過下一號

系統函數
@@rowcount 傳回影響資料筆數

create table #t4 創造一個暫存表
newid() 產生一組識別值

條件處理

case 欄位名稱
when 'boy' then 'roomA'
when 'girl' then 'roomB'
else 'roomC'
end 會議室

if @temp = 1
begin

end
else
begin

end

if not exists
(select * from trade where ccolor = 'pink')
真表示沒有粉紅色

while (select count(*) from trade) > 0
begin

end

waitfor delay '00:00:10'
begin
等10秒後才執行
end

waitfor time '19:05:00'
等到晚上7點5分才執行

指標的用法
declare @fi int
declare sp cursor 宣告
for select id from ad.dbo.trade where id = 3

open sp 開啟

fetch next
from sp
into @fi

print @fi

close sp 關閉
deallocate sp 釋放

@床位1 nvarchar(10),
@學號1 nvarchar(10)

錯誤處理
begin try
end try
begin catch
rollback
end catch

SELECT INTO跟INSERT INTO的差別

SELECT INTO可以into進去暫存的table也可以into實體table，
INSERT INTO只能into實體的table。
實體：SELECT ～ INTO table2 FROM table1
虛擬：SELECT ～ INTO #table2 FROM table1

也就是說，如果使用SELECT INTO
後面的table2如果本來就有，他就直接塞資料；
如果本來就沒有table2，他會新建一個table2，再塞資料。

加了#的意義就是，into進去後，
把Query關掉，重新開啟一個Query，table2就不見了，等於是暫存性的。
當然假如不加#的話，table2就會一直保留著。

另外SELECT INTO如果是實體用法，可以等同於：
INSERT INTO table2 (.....)
SELECT ～ FROM table1

這兩個都可以下WHERE條件。

還有一點，以SELECT 'a' id, 100 cost INTO table3這段而言，
它會自動新建兩個欄位，一個叫id、一個叫cost，
還會自動判別出，id欄位是字串、cost欄位是數值。

附註：以上測試環境是Microsoft SQL Server Service Manager 8.0