一、資料查詢 (SELECT)
基礎查詢語法
-- 全欄查詢
SELECT * FROM table_name;
-- 多表查詢
SELECT * FROM table1, table2, ...;
-- 指定欄位
SELECT field1, field2 FROM table1, table2, ...;
-- 條件過濾
SELECT ... FROM ... WHERE condition;
-- 分組聚合
SELECT ... FROM ... WHERE condition GROUP BY field;
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;
-- 排序
SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;
-- 限制筆數
SELECT ... FROM ... WHERE condition LIMIT 10;
-- 去重
SELECT DISTINCT field1 FROM ...;
SELECT DISTINCT field1, field2 FROM ...;
-- 連接查詢
SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...;
常用條件運算子
| 運算子 | 說明 | 範例 |
= | 等於 | field = 'value' |
<> / != | 不等於 | field <> 'value' |
LIKE | 模糊匹配 | field LIKE 'val_%' |
IS NULL | 為空 | field IS NULL |
IS NOT NULL | 不為空 | field IS NOT NULL |
IN | 在列表中 | field IN (v1, v2) |
NOT IN | 不在列表中 | field NOT IN (v1, v2) |
AND | 邏輯與 | cond1 AND cond2 |
OR | 邏輯或 | cond1 OR cond2 |
BETWEEN | 範圍之間 | field BETWEEN 1 AND 10 |
二、資料操作 (DML)
新增資料 (INSERT)
INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...);
刪除資料 (DELETE / TRUNCATE)
-- 刪除所有資料(保留結構,可回滾,觸發器生效)
DELETE FROM table1;
-- 刪除所有資料(重置自增ID,不可回滾,速度快)
TRUNCATE TABLE table1;
-- 條件刪除
DELETE FROM table1 WHERE condition;
-- 多表關聯刪除
DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition;
更新資料 (UPDATE)
UPDATE table1 SET field1 = new_value1 WHERE condition;
-- 多表關聯更新
UPDATE table1, table2
SET table1.field1 = new_value1, table2.field2 = new_value2
WHERE table1.id1 = table2.id2 AND condition;
三、結構資訊查看
SHOW DATABASES; -- 所有資料庫
SHOW TABLES; -- 當前資料庫所有表
SHOW FIELDS FROM table; -- 表結構 (等同 DESCRIBE table)
SHOW CREATE TABLE table; -- 建表 SQL
SHOW PROCESSLIST; -- 當前執行緒
KILL process_id; -- 終止進程
四、資料庫與資料表管理 (DDL)
資料庫操作
CREATE DATABASE dbname;
CREATE DATABASE dbname CHARACTER SET utf8mb4;
DROP DATABASE dbname;
ALTER DATABASE dbname CHARACTER SET utf8mb4;
資料表操作
-- 建表
CREATE TABLE table (field1 type1, field2 type2, ...);
CREATE TABLE table (field1 type1, ..., INDEX (field));
CREATE TABLE table (field1 type1, ..., PRIMARY KEY (field1));
CREATE TABLE table (..., PRIMARY KEY (field1, field2)); -- 複合主鍵
-- 外鍵
CREATE TABLE table1 (
fk_field1 type1, ...,
FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA)
[ON UPDATE|ON DELETE] [CASCADE|SET NULL]
);
-- 條件建表/臨時表
CREATE TABLE IF NOT EXISTS table (...);
CREATE TEMPORARY TABLE table (...);
-- 刪表
DROP TABLE table;
DROP TABLE IF EXISTS table;
DROP TABLE table1, table2, ...;
-- 修改欄位
ALTER TABLE table MODIFY field1 type1;
ALTER TABLE table MODIFY field1 type1 NOT NULL;
ALTER TABLE table CHANGE old_name new_name type1;
ALTER TABLE table CHANGE old_name new_name type1 NOT NULL;
-- 預設值
ALTER TABLE table ALTER field1 SET DEFAULT 'value';
ALTER TABLE table ALTER field1 DROP DEFAULT;
-- 增刪欄位
ALTER TABLE table ADD new_field type1;
ALTER TABLE table ADD new_field type1 FIRST;
ALTER TABLE table ADD new_field type1 AFTER another_field;
ALTER TABLE table DROP field1;
-- 索引
ALTER TABLE table ADD INDEX (field);
-- 調整欄位順序
ALTER TABLE table MODIFY field1 type1 FIRST;
ALTER TABLE table MODIFY field1 type1 AFTER another_field;
-- 重命名表
ALTER TABLE old_name RENAME new_name;
五、權限管理
-- 授權
GRANT ALL PRIVILEGES ON db.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON db.* TO 'user'@'localhost' IDENTIFIED BY 'password';
-- 回收權限
REVOKE ALL PRIVILEGES ON db.* FROM 'user'@'host'; -- 單一權限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- 所有權限
-- 修改密碼
SET PASSWORD = PASSWORD('new_pass');
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');
-- 刪除使用者
DROP USER 'user'@'host';
六、常用資料類型速查
| 類型 | 大小/範圍 | 說明 |
| TINYINT | 1 Byte (-128~127 / 0~255) | 小整數、布林 |
| SMALLINT | 2 Bytes (±65K) | 短整數 |
| MEDIUMINT | 3 Bytes (±16M) | 中整數 |
| INT | 4 Bytes (±2B) | 標準整數 |
| BIGINT | 8 Bytes (±9×10¹⁸) | 長整數 |
| FLOAT | 4 Bytes | 單精度浮點 |
| DOUBLE | 8 Bytes | 雙精度浮點 |
| DECIMAL(M,D) | M+2 Bytes | 精確十進位(金額) |
| DATE | 3 Bytes | 1000-01-01 ~ 9999-12-31 |
| DATETIME | 8 Bytes | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
| TIMESTAMP | 4 Bytes | 1970-01-01 ~ 2038-01-19,時區感知 |
| VARCHAR(M) | L+1/2 Bytes | 變長字串 (M≤65535) |
| TEXT | L+2 Bytes | 長文本 (64KB) |
| BLOB | L+2 Bytes | 二進制長資料 (64KB) |
| ENUM | 1-2 Bytes | 單選枚舉 (≤65535值) |
| SET | 1-8 Bytes | 多選集合 (≤64成員) |
七、遺忘 Root 密碼重置
# 1. 停止服務
/etc/init.d/mysql stop
# 2. 跳過授權表啟動
mysqld_safe --skip-grant-tables &
# 3. 另開終端機登入
mysql
UPDATE mysql.user SET authentication_string = PASSWORD('newpass') WHERE User = 'root';
FLUSH PRIVILEGES;
# 4. 結束 mysqld_safe (Ctrl+C)
# 5. 正常啟動
/etc/init.d/mysql start
八、損壞表修復
# 檢查所有資料庫
mysqlcheck --all-databases
# 快速檢查
mysqlcheck --all-databases --fast
# 自動修復
mysqlcheck --all-databases --auto-repair
九、常用管理工具
- 命令列:
mysql, mysqldump, mysqladmin, mysqlcheck, myisamchk
- 圖形介面:phpMyAdmin, MySQL Workbench, DBeaver, DataGrip, Navicat
- 監控:
SHOW PROCESSLIST, SHOW ENGINE INNODB STATUS, sys schema, Prometheus + Grafana