-- 建庫/刪庫
CREATE DATABASE db_name;
DROP DATABASE db_name;
-- 建表/刪表
CREATE TABLE tab (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
DROP TABLE tab;
-- 根據舊表建新表
CREATE TABLE tab_new LIKE tab_old; -- 僅結構
CREATE TABLE tab_new AS SELECT * FROM tab_old; -- 結構+數據
-- 增刪改查
INSERT INTO tab (id, name) VALUES (1, 'a');
DELETE FROM tab WHERE id = 1;
UPDATE tab SET name = 'b' WHERE id = 1;
SELECT * FROM tab WHERE name LIKE '%a%';
-- UNION:合併去重
SELECT * FROM t1 UNION SELECT * FROM t2;
-- UNION ALL:合併不去重
SELECT * FROM t1 UNION ALL SELECT * FROM t2;
-- EXCEPT:差集(t1 有、t2 無)
SELECT * FROM t1 EXCEPT SELECT * FROM t2;
-- INTERSECT:交集
SELECT * FROM t1 INTERSECT SELECT * FROM t2;
-- LEFT JOIN:左表全保留
SELECT a.*, b.name FROM a LEFT JOIN b ON a.id = b.a_id;
-- RIGHT JOIN:右表全保留
SELECT a.*, b.name FROM a RIGHT JOIN b ON a.id = b.a_id;
-- FULL JOIN:MySQL 不直接支持,用 UNION 模擬
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;
SELECT dept, COUNT(*), AVG(salary), MAX(salary), MIN(salary)
FROM emp
GROUP BY dept
HAVING COUNT(*) > 5;
注意:SELECT 列表只能包含分組列或聚合函數;TEXT/NTEXT/IMAGE 不能分組。
-- IN 子查詢
SELECT * FROM a WHERE id IN (SELECT a_id FROM b WHERE type=1);
-- 關聯子查詢:每組最大值
SELECT a.* FROM tab a
WHERE a.val = (SELECT MAX(val) FROM tab b WHERE b.grp = a.grp);
-- 通用模板:每頁 10 條,第 3 頁
DECLARE @start INT = 21, @end INT = 30;
SELECT * FROM (
SELECT TOP (@end - @start + 1) * FROM (
SELECT TOP (@end) * FROM tab ORDER BY id DESC
) t ORDER BY id ASC
) t2 ORDER BY id DESC;
MySQL 直接用 LIMIT 20, 10(偏移 20,取 10)。
SELECT TOP 10 * FROM tab (SQL Server) / LIMIT 10 (MySQL)DELETE FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.id=t2.id)BACKUP DATABASE ... TO DISK / RESTORE DATABASE ... FROM DISKsp_renamedb 'old', 'new'