MSSQL

SQL 分類：

DDL - 定義 (CREATE，ALTER，DROP，DECLARE)

DML - 操作 (SELECT，DELETE，UPDATE，INSERT)

DCL - 控制 (GRANT，REVOKE，COMMIT，ROLLABACK)

基礎語法：

  1. 建立資料庫

  2. CREATE DATABASE database_name

  3. 刪除資料庫

  4. DROP DATABASE database_name

  5. 備份 sql server

        • 建立 備份資料的 device

      1. USE master

      2. EXEC sp_addumpdevice 'disk', 'testBack', 'C:\mssal7backup\MyNwind_1.dat'

        • 開始 備份

      3. BACKUP DATABASE pubs TO table_name

  1. 建立資料表

  2. CREATE TABLE table_name ( column1 type1 [ not null ] [ primary key ] , column2 type 2 [ not null ] , ... )

      1. 根據已有的資料表建立新的資料表

      2. A：CREATE TABLE table_name_new LIKE table_name_old (使用舊表建立新表)

      3. B：CREATE TABLE table_name_new AS SELECT column1, column2, ... FROM table_name_old DEFINITION ONLY (使用舊表部份欄位建立新表，不加 DEFINITION ONLY 會連資料一起複製)

  1. 刪除 新的 資料表

  2. DROP TABLE table_name

  3. 增加一列

  4. ALTER TABLE table_name ADD COLUMN column_name type

      1. ps.列增加後不能刪除。DB2中增加列後不能改變資料型態，唯一能改變的是增加 varchar 型態的長度。

  1. 增加主鍵

  2. ALTER TABLE table_name ADD PRIMARY KEY ( column_name )

  3. 刪除主鍵

  4. ALTER TABLE table_name DROP PRIMARY KEY ( column_name )

  5. 建立索引

  6. CREATE [ UNIQUE ] INDEX index_name ON table_name (column_name1, column_name2, ... )

      1. ps.UNIQUE 唯一，資料不重覆

  1. 刪除索引

  2. DROP INDEX index_name

      1. ps.索引是不可更改的，想更改必須刪除重新建立。

  1. 建立視界

  2. CREATE VIEW view_name AS select_statement

  3. 刪除視界

  4. DROP VIEW view_name

簡單常用語法：

  1. 選擇

  2. SELECT * FROM table_name WHERE 範圍

  3. 新增

  4. INSERT INTO table_name (column1, column2, ... ) VALUES (value1, value2, ... )

  5. 刪除

  6. DELETE FROM table_name WHERE 範圍

  7. 更新

  8. UPDATE table_name SET column1 = value1, column2 = value 2, ... WHERE 範圍

  9. 模糊選擇

  10. SELECT * FROM table_name WHERE column LIKE '%value%'

  11. 排序

  12. SELECT * FROM table_name ORDER BY column1, column2, ... [ DESC ]

    1. ps.DESC 是由大到小排序，不設是由小到大排序

  13. 總筆數

  14. SELECT COUNT * AS count_name FROM table_name

  15. 加總

  16. SELECT SUM ( column_name ) AS sum_name FROM table_name

  17. 平均

  18. SELECT AVG ( column_name ) AS avg_name FROM table_name

  19. 最大

  20. SELECT MAX ( column_name ) AS max_name FROM table_name

  21. 最小

  22. SELECT MIN ( column_name ) AS min_name FROM table_name

其他查詢函式：

  1. UNION

  2. UNION 通過組合其他兩個結果資料表，並消去資料表中任何重覆的資料，而產生的結果資料表。

  3. 當 ALL 和 UNION 一起使用時 ( 即 UNION ALL )，不消除重覆的資料。

    1. 兩種情況下，所產生的資料表，每一行不是來 結果資料表1 就是來自 結果資料表2。

  4. EXCEPT

  5. EXCEPT 將所有在 table1 中但不在 table2 中的行，並消除所有動覆的資料，而產生的結果資料表。

    1. 當 ALL 和 EXCEPT 一起使用時 ( 即 EXCEPT ALL )，不消除重覆的資料。

  1. INTERSECT

  2. INTERSECT 將包括 table1 和 table2 中都有的行，並消除所有重覆的資料，而產生的結果資料表。

  3. 當 ALL 和 INTERSECT 一起使用時 ( 即 INTERSECT ALL )，不消除重覆的資料。

  4. ps. 查詢時，結果資料表的欄位必需相同。

外部連接查詢：

  1. LEFT OUTER JOIN

  2. 左連接：結果集合包含連接表的設定欄位，也包括左連接資料表的所有欄位

  3. SELECT table1.column1, table1.column2, table1.column3, table2.column3, table2.column5 FROM table1 LEFT OUT JOIN table2 ON table1.column1 = table2.column3

  4. RIGHT OUTER JOIN

  5. 右連接：結果集即包含連接的設定欄位，也包括右連接資料表的所有欄位

  6. FULL OUTER JOIN

  7. 全連接：不只是包括摙接資料表所設定欄位，也包括兩個資料表中的所有欄位

常用語法

  1. 複製資料表 ( 只複製 結構，欄位名稱 ) ( Access 可用)

  2. 方法一：SELECT * INTO table_name_new FROM table_name_old WHERE 1<>1

  3. 方法二：SELECT TOP 0 * INTO table_name_new FROM table_name_old

  4. 複製資料表 ( 包含 資料，欄位名稱，結構) ( Access 可用)

  5. INSERT INTO table_name_new ( column1, column2, column3 ) SELECT column4, column5, column6 FROM table_name_old

  6. 跨資料庫的複製資料表 ( Access 可用)

  7. INSERT INTO table_name_new ( column1, column2, column3 ) SELECT column4, column5, column6 FROM table_name_new IN '資料庫的絕對路徑' WHERE 條件

  8. 子查詢

  9. SELECT column1, column2, column3 FROM table1 WHERE table1 IN ( SELECT column4 FROM table2)

  10. SELECT column1, column2, column3 FROM table1 WHERE table2 IN (1, 2, 3)

  11. 視界查詢

  12. SELECT * FROM ( SELECT column1, column2, column3 FROM table1 ) table_name_new WHERE table_name_new.column1 > 1

  13. between 的用法

  14. SELECT * FROM table_name WHERE column BETWEEN value1 AND value2

  15. in 的用法

  16. SELECT * FROM table_name WHERE column [ NOT ] IN ( value1, value2, value3 )

  17. 兩張關聯表，刪除主表中已經在副表中沒有的訊息

  18. DELETE FROM table1 WHERE NOT EXISTS ( SELECT * FROM table2 WHERE table1.column1 = table2.column1 )

  19. 前10筆資料

  20. SELECT TOP 10 * FROM table_name WHERE 範圍

  21. 選擇在每一組 column2 值相同的資料中，對應 column1 值最大記錄的所有資料

  22. SELECT column1, column2 FROM table_name ta WHERE column1 = ( SELECT MAX ( column1 ) FROM table_name tb WHERE tb.column2 = ta.column2 )

  23. 包含所有在 table1 中，但不在 table2 和 table3 中，並消除所有重覆的資料，產生的結果資料表

  24. ( SELECT column FROM table1 ) EXCEPT ( SELECT column FROM table2 ) EXCEPT ( SELECT column FROM table3 )

  25. 隨機取出 10 筆資料

  26. SELECT TOP 10 * FROM table_name ORDER BY NEWID()

  27. 隨機選擇資料

  28. SELECT NEWID()

  29. 刪除重覆資料

  30. DELETE FROM table_name WHERE column NOT IN ( SELECT MAX ( column ) FROM table_name GROUP BY column2, column3, .... )

  31. case 的用法

  32. SELECT

  33. CASE column WHER value THEN data1 ELSE data2 END

    1. FROM table_name

  34. 初始化資料表

  35. TRUNCATE TABLE table_name

  36. 選擇 10 到 15 的資料

  37. SELECT TOP 5 * FROM ( SELECT TOP 15 * FROM table_name ORDER BY column ASC ) table_name_new ORDER BY column DESC