萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL Server T-SQL 完整語法大全:DDL/DML/DCL、建表/索引/視圖、進階查詢、外連接、分頁、去重

SQL Server (T-SQL) 核心語法速查手冊,涵蓋三大語言類別、物件管理、進階查詢運算子、外連接、實用技巧 (分頁、去重、條件彙總、隨機抽樣)。

語言分類

類別指令
DDLCREATE, ALTER, DROP, DECLARE
DMLSELECT, INSERT, UPDATE, DELETE
DCLGRANT, REVOKE, COMMIT, ROLLBACK

物件管理基礎語句

-- 資料庫
CREATE DATABASE dbname;
DROP DATABASE dbname;

-- 資料表
CREATE TABLE tabname (
    col1 type1 [NOT NULL] [PRIMARY KEY],
    col2 type2 [NOT NULL], ...
);
-- 依現有表建表 (結構)
SELECT * INTO tab_new FROM tab_old WHERE 1=0;  -- 空表
SELECT TOP 0 * INTO tab_new FROM tab_old;      -- 同效

-- 刪表
DROP TABLE tabname;

-- 欄位增刪改
ALTER TABLE tabname ADD col type;
-- 注: SQL Server 不支援 DROP COLUMN 直接刪除 (需重建表)
-- 主鍵
ALTER TABLE tabname ADD PRIMARY KEY (col);
ALTER TABLE tabname DROP CONSTRAINT pk_name;

-- 索引
CREATE [UNIQUE] INDEX idxname ON tabname(col...);
DROP INDEX tabname.idxname;  -- SQL Server 語法

-- 視圖
CREATE VIEW viewname AS SELECT ...;
DROP VIEW viewname;

核心 DML

SELECT * FROM table1 WHERE range;
INSERT INTO table1(f1,f2) VALUES(v1,v2);
DELETE FROM table1 WHERE range;
UPDATE table1 SET f1=v1 WHERE range;
SELECT * FROM table1 WHERE f1 LIKE '%value%';  -- 模糊查詢
SELECT * FROM table1 ORDER BY f1, f2 [DESC];
SELECT COUNT(*) AS total FROM table1;
SELECT SUM(f1), AVG(f1), MAX(f1), MIN(f1) FROM table1;

進階查詢運算子

運算子說明去重
UNION合併結果是
UNION ALL合併結果否
EXCEPT差集 (左有右無)是
INTERSECT交集是

參與查詢欄位數、類型、順序必須一致。

外部連接

-- LEFT JOIN: 左表全保留
SELECT a.a, a.b, a.c, b.c, b.d, b.f
FROM a LEFT OUTER JOIN b ON a.a = b.c;

-- RIGHT JOIN: 右表全保留
SELECT ... FROM a RIGHT OUTER JOIN b ON a.a = b.c;

-- FULL JOIN: 雙表全保留
SELECT ... FROM a FULL OUTER JOIN b ON a.a = b.c;

實用技巧精選

1. 子查詢 (IN / EXISTS)

SELECT a,b,c FROM a WHERE a IN (SELECT d FROM b);
SELECT a,b,c FROM a WHERE a IN (1,2,3);

2. 顯示文章、作者、最新回覆時間

SELECT a.title, a.username, b.adddate
FROM table a,
     (SELECT MAX(adddate) adddate FROM table WHERE table.title = a.title) b;

3. 線上檢視 (內聯視圖)

SELECT * FROM (SELECT a,b,c FROM a) T WHERE t.a > 1;

4. BETWEEN / IN

SELECT * FROM t WHERE time BETWEEN t1 AND t2;  -- 含邊界
SELECT * FROM t WHERE a NOT BETWEEN 1 AND 10;
SELECT * FROM t WHERE a IN ('v1','v2','v4');

5. 刪除主表中子表無對應資料

DELETE FROM table1 
WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.f1 = table2.f1);

6. 四表聯查

SELECT * FROM a 
LEFT JOIN b ON a.a = b.b 
RIGHT JOIN c ON a.a = c.c 
INNER JOIN d ON a.a = d.d WHERE ...;

7. 排程提前 5 分鐘提醒

SELECT * FROM schedule 
WHERE DATEDIFF(minute, start_time, GETDATE()) > 5;

8. 單條 SQL 分頁 (SQL 2000 風格)

SELECT TOP 10 b.* 
FROM (SELECT TOP 20 pk, sort_col FROM tbl ORDER BY sort_col DESC) a
JOIN tbl b ON b.pk = a.pk
ORDER BY a.sort_col;

9. 取前 10 筆

SELECT TOP 10 * FROM table1 WHERE range;

10. 每組最大值記錄 (論壇月排行/熱銷分析)

SELECT a,b,c FROM tablename ta 
WHERE a = (SELECT MAX(a) FROM tablename tb WHERE tb.b = ta.b);

11. 多重 EXCEPT

(SELECT a FROM A) EXCEPT (SELECT a FROM B) EXCEPT (SELECT a FROM C);

12. 隨機 10 筆

SELECT TOP 10 * FROM tablename ORDER BY NEWID();

13. 刪除重複 (保留最大 ID)

DELETE FROM tablename 
WHERE id NOT IN (SELECT MAX(id) FROM tablename GROUP BY col1, col2, ...);

14. 列出所有使用者表

SELECT name FROM sysobjects WHERE type = 'U';

15. 列出表所有欄位

SELECT name FROM syscolumns WHERE id = OBJECT_ID('TableName');

16. 條件彙總 (CASE)

SELECT type,
    SUM(CASE vender WHEN 'A' THEN pcs ELSE 0 END) AS A_pcs,
    SUM(CASE vender WHEN 'C' THEN pcs ELSE 0 END) AS C_pcs,
    SUM(CASE vender WHEN 'B' THEN pcs ELSE 0 END) AS B_pcs
FROM tablename GROUP BY type;

17. 清空表 (保留結構、重置身分值)

TRUNCATE TABLE table1;

18. 取區間記錄 (第 11-15 筆)

SELECT TOP 5 * FROM (SELECT TOP 15 * FROM table ORDER BY id ASC) t2 ORDER BY id DESC;

掌握此語法集,應對 T-SQL 開發、維護、遷移無虞。

copyright © 萬盛學電腦網 all rights reserved