萬盛學電腦網

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

SQL 常用指令速查表:從 SELECT 到 JOIN、DDL、DML 完整整理

一張表掌握所有核心 SQL 指令語法,適合開發時快速查閱。

查詢與篩選

指令語法範例
SELECTSELECT col1, col2 FROM table WHERE condition;
SELECT *SELECT * FROM table;
DISTINCTSELECT DISTINCT col FROM table;
WHEREWHERE col = val AND col2 > 10
AND / ORWHERE a=1 AND (b=2 OR c=3)
INWHERE col IN (1,2,3)
BETWEENWHERE col BETWEEN 10 AND 20
LIKEWHERE name LIKE 'A%'
ORDER BYORDER BY col DESC, col2 ASC
GROUP BYGROUP BY col HAVING COUNT(*) > 5
LIMIT / TOPLIMIT 10 / TOP 10

連接查詢

類型語法
INNER JOINSELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id
LEFT JOINSELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id
RIGHT JOINSELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.t1_id
FULL JOINSELECT * FROM t1 FULL JOIN t2 ON t1.id = t2.t1_id

資料操作 (DML)

指令語法
INSERTINSERT INTO t (c1,c2) VALUES (v1,v2);
UPDATEUPDATE t SET c1=v1 WHERE cond;
DELETEDELETE FROM t WHERE cond;
TRUNCATETRUNCATE TABLE t;

結構定義 (DDL)

指令語法
CREATE DATABASECREATE DATABASE db;
CREATE TABLECREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50));
ALTER TABLEALTER TABLE t ADD col INT; DROP COLUMN col;
CREATE INDEXCREATE INDEX idx ON t(col);
CREATE VIEWCREATE VIEW v AS SELECT * FROM t;
DROPDROP TABLE t; DROP DATABASE db; DROP INDEX idx;

集合運算

SELECT * FROM t1 UNION SELECT * FROM t2;      -- 去重合併
SELECT * FROM t1 UNION ALL SELECT * FROM t2;  -- 保留重複

別名 (AS)

SELECT col AS alias FROM table;
SELECT * FROM table AS t;
copyright © 萬盛學電腦網 all rights reserved