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