SQL 經典例句大全：從基礎增刪改查到連接查詢、子查詢、分頁、編程

說明

本文匯總 SQL Server (T-SQL) 常用例句，涵蓋 DML、DDL、函數、連接、子查詢、分頁、編程等，**MySQL/PostgreSQL 語法有差異，請參考對應文檔調整**。

一、基礎增刪改查

-- 查詢所有
SELECT * FROM studio;

-- 插入
INSERT INTO studio(st_name, st_sex, st_age, st_add, st_tel) VALUES('黃蘭淇', 0, 36, '南充', '13943943334');

-- 更新（必帶 WHERE）
UPDATE class SET cl_remark='真的是不錯' WHERE cl_id=5;

-- 刪除（必帶 WHERE）
DELETE FROM class WHERE cl_id=7;

-- 列別名
SELECT cl_id AS '班級主鍵', cl_class AS '班級名稱' FROM class;
SELECT 名字=st_name FROM studio; -- T-SQL 特有

二、條件查詢進階

-- 比較/邏輯運算符
SELECT * FROM class WHERE cl_id > 1;
SELECT * FROM class WHERE cl_id <> 10 OR cl_class='百傑一班';
SELECT * FROM class WHERE cl_id <> 10 AND cl_class='百傑一班';

-- LIKE 模糊
SELECT * FROM class WHERE cl_class LIKE '百傑%';
SELECT * FROM class WHERE cl_remark LIKE '%上午%';

-- BETWEEN / IN / IS NULL
SELECT * FROM class WHERE cl_id BETWEEN 3 AND 5;
SELECT * FROM class WHERE cl_id NOT BETWEEN 3 AND 5;
SELECT * FROM class WHERE cl_remark IS NOT NULL;
SELECT * FROM class WHERE cl_class IN('千星一班','百傑二班');

三、數學運算與聚合函數

-- 數學運算
SELECT '結果' = co_num / 5 / 6 FROM course WHERE co_name IN ('Java基礎','Java項目入門');

-- 聚合
SELECT COUNT(*) FROM course WHERE co_num < 50;
SELECT SUM(co_num) FROM course;
SELECT SUM(co_num) * 50 FROM course; -- 假設每節50元
SELECT MIN(co_num), MAX(co_num), AVG(co_num) FROM course;

四、常用數學/日期/字符串/系統函數

-- 數學
SELECT ABS(co_num), PI(), SIN(co_num), EXP(co_num) FROM course;
SELECT ROUND(345.456, -1), ROUND(345.456, 2);

-- 日期 (T-SQL)
SELECT DAY(cl_s_time), MONTH(cl_s_time), YEAR(cl_s_time) FROM class;
SELECT DATEADD(DAY, 10, cl_s_time), DATEDIFF(MONTH, cl_s_time, cl_o_time) FROM class;
SELECT GETDATE(); -- 當前日期時間
SELECT CONVERT(VARCHAR(100), GETDATE(), 120); -- 2006-05-16 10:57:49

-- 字符串
SELECT '班級:' + cl_class + ' 編號:' + cl_coding FROM class;
SELECT SUBSTRING(cl_class, 1, 4), LEFT(cl_class, 3), RIGHT(cl_class, 3), LEN(cl_class) FROM class;
SELECT REPLACE(cl_class, '實訓', '強化') FROM class;

-- 系統
SELECT HOST_ID(), HOST_NAME(), DB_ID(), DB_NAME(), OBJECT_ID('Stu_course_ADD'), OBJECT_NAME(151671588);

五、排序、分組、去重

SELECT * FROM studio ORDER BY st_name DESC, st_age DESC;
SELECT cl_id, COUNT(*) FROM studio GROUP BY cl_id;
SELECT ho_id, AVG(st_age) FROM studio GROUP BY ho_id;
SELECT ho_id, cl_id, AVG(st_age) FROM studio GROUP BY ho_id, cl_id;
SELECT ho_id, AVG(st_age) FROM studio WHERE cl_id=1 GROUP BY ho_id HAVING AVG(st_age) > 35;

六、集合操作：UNION / UNION ALL

SELECT * FROM studio WHERE cl_id=1
UNION ALL
SELECT * FROM studio WHERE ho_id=1
UNION ALL
SELECT * FROM studio WHERE st_age >= 30
ORDER BY st_id;

七、連接查詢全家族

-- 內連接（等值/自然連接）
SELECT st.st_name, cl.cl_class FROM studio st INNER JOIN class cl ON st.cl_id = cl.cl_id;

-- 左外連接
SELECT st.st_name, cl.cl_class FROM studio st LEFT OUTER JOIN class cl ON st.cl_id = cl.cl_id;

-- 右外連接
SELECT st.st_name, cl.cl_class FROM studio st RIGHT OUTER JOIN class cl ON st.cl_id = cl.cl_id;

-- 全外連接
SELECT st.st_name, cl.cl_class FROM studio st FULL OUTER JOIN class cl ON st.cl_id = cl.cl_id;

-- 交叉連接（笛卡爾積）
SELECT st_name, cl_class FROM studio CROSS JOIN class;

-- 自連接（區域層級示例）
SELECT a.z_zone, b.z_zone FROM zone a INNER JOIN zone b ON a.z_id = b.id;
SELECT b.z_zone, COUNT(a.z_zone) FROM zone a INNER JOIN zone b ON a.z_id = b.id GROUP BY b.z_zone;

八、子查詢

-- IN / NOT IN
SELECT * FROM studio WHERE cl_id IN (SELECT cl_id FROM class WHERE cl_id > 2);
SELECT * FROM studio WHERE cl_id NOT IN (SELECT cl_id FROM class WHERE cl_id > 2);

-- ANY / ALL
SELECT * FROM class WHERE cl_id > ANY (SELECT cl_id FROM studio WHERE st_age > 30);
SELECT * FROM class WHERE cl_id > ALL (SELECT cl_id FROM studio WHERE st_age > 30);

-- EXISTS / NOT EXISTS
SELECT * FROM studio WHERE EXISTS (SELECT 1 FROM class WHERE studio.cl_id = class.cl_id AND class.cl_id = 1);
SELECT * FROM studio WHERE NOT EXISTS (SELECT 1 FROM class WHERE studio.cl_id = class.cl_id AND class.cl_id = 1);

九、分頁查詢 (T-SQL 風格)

SELECT TOP 3 * FROM studio
WHERE st_id > ALL (SELECT TOP 3 st_id FROM studio ORDER BY st_id)
ORDER BY st_id;

MySQL 使用 LIMIT offset, count，PostgreSQL/Oracle 12c+ 使用 OFFSET ... FETCH NEXT ...。

十、基於查詢建表與批量插入

SELECT st_name INTO class_3 FROM studio WHERE cl_id=3;
INSERT INTO class_3 SELECT st_name FROM studio WHERE cl_id=4;

十一、SQL 變量與流程控制 (T-SQL)

DECLARE @max INT;
SET @max = 1;
WHILE @max < 10
BEGIN
    SET @max = @max + 1;
    PRINT @max;
END
PRINT '終於循環完了';

結語

熟練掌握上述模式，覆蓋 90% 日常開發場景。跨數據庫移植時，重點關注：分頁語法、日期函數、字符串拼接、自增列定義、系統函數差異。