MySQL 速查表
一份可搜尋、可列印的 MySQL 參考——查詢、聯結、索引、函式、資料類型和管理指令。免費。
Querying
10SELECT * FROM users;
Select all columns and rows
SELECT id, name FROM users;
Select specific columns
SELECT DISTINCT city FROM users;
Unique values only
WHERE age > 18 AND active = 1
Filter rows by conditions
WHERE name LIKE 'A%'
Pattern match (% = any chars)
WHERE id IN (1, 2, 3)
Match any value in a set
WHERE age BETWEEN 18 AND 30
Inclusive range filter
ORDER BY created_at DESC
Sort descending
LIMIT 10 OFFSET 20
Paginate results
WHERE email IS NULL
Match null values
Joins
8INNER JOIN orders ON orders.user_id = users.id
Rows matching in both tables
LEFT JOIN orders ON ...
All left rows + matches
RIGHT JOIN orders ON ...
All right rows + matches
CROSS JOIN colors
Cartesian product
SELF JOIN: FROM emp e JOIN emp m ON e.mgr = m.id
Join a table to itself
USING (user_id)
Join on a same-named column
UNION / UNION ALL
Combine result sets
SELECT u.*, o.total FROM users u JOIN orders o
Alias tables for clarity
Aggregation
9COUNT(*)
Count rows
SUM(amount)
Total of a numeric column
AVG(price)
Average value
MIN(price), MAX(price)
Smallest and largest
GROUP BY country
Group rows for aggregation
HAVING COUNT(*) > 5
Filter on aggregated groups
GROUP_CONCAT(name)
Concatenate grouped values
COUNT(DISTINCT city)
Count unique values
ROW_NUMBER() OVER (ORDER BY id)
Window function ranking
Modifying data
8INSERT INTO users (name) VALUES ('Sam');
Insert a single row
INSERT INTO users (name) VALUES ('A'),('B');
Insert multiple rows
UPDATE users SET active = 1 WHERE id = 5;
Update matching rows
DELETE FROM users WHERE id = 5;
Delete matching rows
TRUNCATE TABLE logs;
Remove all rows quickly
INSERT ... ON DUPLICATE KEY UPDATE
Upsert on a unique key
REPLACE INTO users ...
Delete + insert on conflict
INSERT IGNORE INTO ...
Skip rows that would error
Schema / DDL
9CREATE TABLE users (id INT PRIMARY KEY);
Create a new table
AUTO_INCREMENT
Auto-numbering column
ALTER TABLE users ADD COLUMN age INT;
Add a column
ALTER TABLE users DROP COLUMN age;
Remove a column
ALTER TABLE users MODIFY name VARCHAR(100);
Change a column type
DROP TABLE IF EXISTS users;
Delete a table
FOREIGN KEY (user_id) REFERENCES users(id)
Reference another table
ON DELETE CASCADE
Cascade deletes to children
CREATE TABLE t2 LIKE t1;
Copy a table structure
Indexes & keys
8PRIMARY KEY (id)
Unique row identifier
UNIQUE (email)
Enforce unique values
CREATE INDEX idx_name ON users (name);
Speed up lookups on a column
CREATE INDEX idx_ab ON t (a, b);
Composite (multi-column) index
DROP INDEX idx_name ON users;
Remove an index
FULLTEXT (body)
Full-text search index
EXPLAIN SELECT ...;
Show the query plan
SHOW INDEX FROM users;
List indexes on a table
Data types
9INT, BIGINT, TINYINT
Integer types by size
DECIMAL(10, 2)
Exact fixed-point numbers
VARCHAR(255)
Variable-length string
TEXT, LONGTEXT
Large text blobs
DATE, DATETIME, TIMESTAMP
Date and time types
BOOLEAN (TINYINT(1))
True/false stored as 0/1
JSON
Native JSON document column
ENUM('a', 'b')
One of a fixed set of values
UNSIGNED
Non-negative numeric modifier
Type sizes & limits
20TINYINT
1 byte: -128..127 (0..255 UNSIGNED)
SMALLINT
2 bytes: -32,768..32,767 (0..65,535)
MEDIUMINT
3 bytes: -8.39M..8.39M (0..16.78M)
INT
4 bytes: -2.15B..2.15B (0..4.29B)
BIGINT
8 bytes: ±9.22×10^18 (0..1.84×10^19)
DECIMAL(M, D)
Exact: M up to 65 digits, D up to 30
FLOAT / DOUBLE
4 / 8 bytes approximate floating point
CHAR(M)
Fixed length, 0..255 characters
VARCHAR(M)
0..65,535 bytes (shared 64KB row limit)
TINYTEXT
Up to 255 bytes (~255 chars)
TEXT
Up to 65,535 bytes (64 KB)
MEDIUMTEXT
Up to 16,777,215 bytes (16 MB)
LONGTEXT
Up to 4,294,967,295 bytes (4 GB)
BLOB types
TINY/—/MEDIUM/LONG: 255B..4GB (binary)
JSON
Stored as LONGBLOB, up to ~4 GB
ENUM / SET
ENUM: 65,535 members; SET: 64 members
DATE
1000-01-01 to 9999-12-31 (3 bytes)
DATETIME
1000 to 9999, microseconds (5–8 bytes)
TIMESTAMP
1970-01-01 to 2038-01-19 UTC (4 bytes)
TIME / YEAR
±838:59:59 / 1901..2155
Functions
9NOW(), CURDATE()
Current datetime / date
DATE_FORMAT(d, '%Y-%m-%d')
Format a date
DATEDIFF(a, b)
Days between two dates
CONCAT(a, ' ', b)
Join strings
COALESCE(a, b, 'n/a')
First non-null value
IFNULL(x, 0)
Replace null with a default
CASE WHEN x > 0 THEN '+' ELSE '-' END
Conditional expression
CAST(x AS CHAR)
Convert a value type
ROUND(x, 2)
Round to decimals
Transactions & admin
9START TRANSACTION;
Begin a transaction
COMMIT;
Persist the transaction
ROLLBACK;
Undo the transaction
SAVEPOINT sp1;
Set a rollback point
SHOW TABLES;
List tables in the database
DESCRIBE users;
Show a table structure
SHOW PROCESSLIST;
List running connections
GRANT ALL ON db.* TO 'u'@'%';
Grant user privileges
mysqldump -u root db > db.sql
Back up a database (CLI)
沒有條目符合「:q」。
關於 MySQL 速查表
這份 MySQL 速查表將您實際會用到的 SQL 語法整理成一個可搜尋的參考：查詢、聯結、彙總、資料修改、結構描述與 DDL 陳述式、索引與鍵、資料型別及其大小與限制、內建函式，以及交易與管理命令。
它既能作為語法提醒——例如 LEFT JOIN、UPSERT 或 ALTER TABLE 的確切寫法——也能查找那些沒人會記住的細節，例如整數範圍、VARCHAR 限制，以及哪種索引類型適合哪種查詢。
和這個工具裡的每一份速查表一樣，它免費且完全在用戶端執行：用搜尋框即時篩選各行、透過固定式目錄在各區段間跳轉、一鍵複製任何陳述式，並可列印整頁作為桌邊參考。
如何使用 MySQL 速查表
- 打開速查表，瀏覽各區段，從「查詢與聯結」到「結構描述／DDL」再到「交易與管理」。
- 搜尋像 JOIN、INDEX 或 GROUP BY 這樣的關鍵字，即時篩選所有程式碼片段。
- 需要儲存細節而非語法時，跳到「資料型別」或「型別大小與限制」。
- 點擊某條陳述式或其複製圖示，將 SQL 複製到剪貼簿。
- 使用「列印」取得完整 MySQL 參考的紙本版本。
常見問題
十個區段，涵蓋查詢、聯結、彙總、資料修改、結構描述 DDL、索引與鍵、資料型別、型別大小與限制、函式，以及交易加上管理命令——每一項都是可直接複製的 SQL 程式碼片段。
有的。有一個專門的區段列出型別大小與限制——也就是您原本得查手冊才知道的整數範圍與字串長度上限——就在資料型別區段旁邊。
可以。點擊 SQL 程式碼片段或該行的複製圖示，就會立即複製到您的剪貼簿。
兩者都可以。搜尋框會隨著您輸入即時篩選每一行，並顯示符合數量，而「列印」按鈕會輸出去除頁面外框的乾淨版本。
是的，完全免費，在您的瀏覽器中執行，無需登入。
熱門搜尋
mysql cheat sheet
mysql commands list
mysql data types
mysql joins syntax
mysql alter table syntax
mysql index syntax
mysql aggregate functions
mysql sql reference
需要協助？
使用此工具時遇到問題？請告訴我們的團隊。