散亂格部落

SELECT * FROM [TABLE]
選擇在[TABLE]內的所有資料
SELECT [COLUMN1], [COLUMN2] FROM [TABLE]
選擇在[TABLE]內的資料及只顯示[COLUMN1]及[COLUMN2]
SELECT ... FROM [TABLE] WHERE ([COLUMN1] = 1)
只顯示[TABLE]內[COLUMN1]數值是1的資料
SELECT ... FROM [TABLE] WHERE ([COLUMN1] = 1 AND [COLUMN2] = 2)
只顯示[TABLE]內[COLUMN1]數值是1及[COLUMN2]數值是2的資料
SELECT ... FROM [TABLE] WHERE ([COLUMN1] = 1 OR [COLUMN2] = 2)
只顯示[TABLE]內[COLUMN1]數值是1或[COLUMN2]數值是2的資料
SELECT ... FROM [TABLE] WHERE ([COLUMN1] = 1 OR ([COLUMN2] = 2 AND [COLUMN3] <> 3)
只顯示[TABLE]內[COLUMN1]數值是1或當[COLUMN2]數值是2及[COLUMN3]數值不是3的資料
SELECT ... FROM [TABLE] WHERE ([COLUMN1] IS NOT NULL)
只顯示[TABLE]內[COLUMN1]數值不是NULL的資料(有數值)
SELECT ... FROM [TABLE] ORDER BY [COLUMN1]
顯示[TABLE]內的資料並根據[COLUMN1]排序
SELECT ... FROM [TABLE] ORDER BY [COLUMN1] ASC
顯示[TABLE]內的資料並根據[COLUMN1]由小至大排序(Ascending)
SELECT ... FROM [TABLE] ORDER BY [COLUMN1] DESC
顯示[TABLE]內的資料並根據[COLUMN1]由大至小排序(Descending)
SELECT ... FROM [TABLE] ORDER BY [COLUMN1] ASC, [COLUMN2] DESC
顯示[TABLE]內的資料並先根據[COLUMN1]由小至大排序再根據[COLUMN2]由大至小再排序
([COLUMN2]的排序只會在[COLUMN1]的數值相同時再排序相同數值的資料)
SELECT TOP 50 * FROM [TABLE]
顯示[TABLE]內的頭50條資料
SELECT TOP 50 PERCENT * FROM [TABLE]
顯示[TABLE]內的頭50%條資料
 

SELECT MIN([COLUMN1]) FROM [TABLE]
取得[TABLE]內[COLUMN1]最小的數值(只顯示[COLUMN1])
SELECT MAX([COLUMN1]) FROM [TABLE]
取得[TABLE]內[COLUMN1]最大的數值(只顯示[COLUMN1])
SELECT AVG([COLUMN1]) FROM [TABLE]
取得[TABLE]內[COLUMN1]的平均數(只顯示[COLUMN1])
SELECT SUM([COLUMN1]) FROM [TABLE]
取得[TABLE]內[COLUMN1]的總和數(只顯示[COLUMN1])
SELECT COUNT([COLUMN1]) FROM [TABLE]
取得[TABLE]內的資料數(即有多少行資料), 通常與WHERE一同使用

SELECT [COLUMN1] AS MAX_COL1 FROM [TABLE]
顯示[TABLE]內[COLUMN1]的數值及在結果的Header顯示MAX_COL1(而非COLUMN1)
SELECT ... FROM [TABLE] WHERE ([COLUMN1] LIKE '%AA%')
只顯示[TABLE]內[COLUMN1]數值中包括AA的資料(%代表在該位置可有數值, _代表一個單位的數值)
    以'%AA%'為例, 'BAAC', 'AAC', 'BAA', 'AA', 'aa'都會被選取顯示
    以'_A'為例, '1A', '2A', 'CA'都會被選取顯示
    以'_A%'為例, '1ACBA', '2AC', 'AAD'都會被選取顯示

SELECT ... FROM [TABLE] WHERE ([COLUMN1] IN (1, 2, 3))
只顯示[TABLE]內[COLUMN1]數值是1/2/3的資料
SELECT ... FROM [TABLE] WHERE ([COLUMN1] NOT IN (1, 2, 3))
只顯示[TABLE]內[COLUMN1]數值不是1/2/3的資料
SELECT ... FROM [TABLE] WHERE ([COLUMN1] IN (SELECT [COLUMN1] FROM [TABLE2]))
只顯示[TABLE]內[COLUMN1]數值是等於(SELECT [COLUMN1] FROM [TABLE2])結果內的數值(IN後的SELECT只選取了[TABLE2]內的[COLUMN1])
SELECT ... FROM [TABLE] WHERE ([COLUMN1] BETWEEN 1 AND 3)
只顯示[TABLE]內[COLUMN1]數值落於1及3的範圍內(即1-3)

SELECT [COLUMN1] + ',' + [COLUMN2] FROM [TABLE2]
將[COLUMN1]與[COLUMN2]放於同一個Column中顯示, 並在兩個Column中加入','
SELECT CONCAT([COLUMN1],',',[COLUMN2]) FROM [TABLE]
與上句同一效果
SELECT ISNULL([COLUMN1], 0) FROM [TABLE]
檢查[COLUMN1]是否為NULL, 是的話返回0
*在組合/Concat多個Column時, 如有NULL的話, 結果會為NULL

INSERT INTO [TABLE]([COLUMN1], [COLUMN2], [COLUMN3])      
VALUES ([VALUE1], [VALUE2], [VALUE3])
插入一條[COLUMN1]=[VALUE1], [COLUMN2]=[VALUE2], [COLUMN3]=[VALUE3]的資料到[TABLE]中([TABLE]後COLUMN的排序與VALUES後的VALUE排序是對應的)

UPDATE [TABLE] SET [COLUMN1]=[VALUE1], [COLUMN2]=[VALUE2] WHERE [COLUMN3]=3
更新[TABLE]內[COLUMN3]等於3的資料的[COLUMN1]為[VALUE1]及[COLUMN2]為[VALUE2]
    (WHERE並非必入, 但大多情況下都會使用)

DELETE FROM [TABLE] WHERE [COLUMN1]=1
刪除在[TABLE]內所有[COLUMN1]的數值是1的資料