您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 常用 SQL 語句大全：基礎 DDL/DML、進階查詢、報表實戰複雜查詢

編輯：MySQL 綜合教學

MySQL 常用 SQL 語句大全：基礎 DDL/DML、進階查詢、報表實戰複雜查詢

整理專案中高頻使用的 MySQL 語句，分為基礎、進階、實戰三層，方便快速複製調用。

一、基礎 DDL/DML

1. 資料庫操作

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
DROP DATABASE db_name;

2. 資料表操作

-- 建表 (含主鍵、索引、註解、引擎、字元集)
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(60) NOT NULL DEFAULT '' COMMENT '登入名',
    password CHAR(32) NOT NULL DEFAULT '' COMMENT '密碼雜湊',
    PRIMARY KEY (id),
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

-- 修改表名
ALTER TABLE old_name RENAME TO new_name;

-- 新增/刪除欄位
ALTER TABLE tbl ADD COLUMN col_name VARCHAR(30);
ALTER TABLE tbl DROP COLUMN col_name;

-- 基本 CRUD
SELECT * FROM tbl WHERE condition;
INSERT INTO tbl(col1, col2) VALUES(val1, val2);
DELETE FROM tbl WHERE condition;
UPDATE tbl SET col1=val1 WHERE condition;

-- 模糊查詢
SELECT * FROM tbl WHERE col LIKE '%keyword%';

-- 排序
SELECT * FROM tbl ORDER BY col1, col2 DESC;

-- 聚合
SELECT COUNT(*) AS total FROM tbl;
SELECT SUM(col) AS sum_val FROM tbl;
SELECT AVG(col) AS avg_val FROM tbl;
SELECT MAX(col) AS max_val FROM tbl;
SELECT MIN(col) AS min_val FROM tbl;

-- 權限授予
CREATE USER 'app'@'%' IDENTIFIED BY 'pass';
GRANT ALL PRIVILEGES ON db.* TO 'app'@'%';
FLUSH PRIVILEGES;

3. 索引/視圖

CREATE INDEX idx_name ON tbl(col);
DROP INDEX idx_name ON tbl;
CREATE VIEW v_name AS SELECT ...;
DROP VIEW v_name;

4. 聯合查詢

SELECT SUM(col1) FROM tbl GROUP BY col2;
SELECT * FROM tbl LIMIT 0, 10;  -- 第 1 頁 10 筆

二、進階技巧

去重

SELECT DISTINCT * FROM tbl;
SELECT DISTINCT(col) FROM tbl;

復制表結構+資料

CREATE TABLE tbl2 AS SELECT * FROM tbl1;

執行計畫分析

EXPLAIN SELECT * FROM tbl;
DESC SELECT * FROM tbl;

三、報表實戰複雜查詢 (脫敏範例)

1. 多條件篩選專案 ID

SELECT DISTINCT xp.Id 
FROM finance_plan xfp 
LEFT JOIN project xp ON xfp.proId = xp.Id 
WHERE xp.proCategory IN ('ITER973') AND xp.Id IN (0,1,2);

2. 專案預算科目彙總 (子查詢 + 多表 JOIN)

SELECT t.Pid, xsb2.subjectName,
       SUM(t.planTotalAmount) planTotalAmount,
       SUM(t.planCurrentAmount) planCurrentAmount,
       SUM(t.total_amount) total_amount,
       SUM(t.payedMoney) payedMoney,
       SUM(t.finalPayment) finalPayment,
       SUM(t.CurYearPayedMoney) CurYearPayedMoney
FROM (
    SELECT xsb.Id, xsb.subjectName, xsb.Pid,
           SUM(xfp.planTotalAmount) planTotalAmount,
           SUM(xfp.planCurrentAmount) planCurrentAmount,
           SUM(xfp.total_amount) total_amount,
           SUM(xfp.payedMoney) payedMoney,
           SUM(xfp.finalPayment) finalPayment,
           SUM(xfp.CurYearPayedMoney) CurYearPayedMoney
    FROM subject_budget xsb
    LEFT JOIN project xp ON xsb.type = xp.subjectType
    LEFT JOIN finance_plan xfp ON xsb.Id = xfp.subjectId
    WHERE xp.Id = 1
    GROUP BY xfp.subjectId, xsb.Id
    ORDER BY xsb.Id
) t
LEFT JOIN subject_budget xsb2 ON t.Pid = xsb2.Id
WHERE t.Pid <> 0
GROUP BY t.Pid;

3. 部門科目控制額度與支出 (優化版)

SELECT DISTINCT xcs1.dept, xsc.subjectName, xcs2.amount,
       COALESCE(SUM(xfa.amount),0) payedMoney,
       xcs2.amount - SUM(xfa.amount) leftAmount
FROM control_subject xcs1
LEFT JOIN control_subject xcs2 ON xcs1.dept = xcs2.dept AND xcs2.detailId = 3
LEFT JOIN subject_control xsc ON xcs2.detailId = xsc.Id
LEFT JOIN finance_activity xfa ON xcs2.dept = xfa.department AND xfa.detailId IN ('22','23')
GROUP BY xcs1.dept, xcs1.detailId;

4. 類 QQ 空間訊息流 (多來源 UNION ALL)

-- 贊了我
SELECT tf.id, 1 AS typeId, c.avatar, c.name, ct.catalog, ct.id AS messageId, ct.content AS trendsContent, NULL AS replyContent, tf.create_time AS createTime, tf.read AS isRead
FROM trends_fork tf
LEFT JOIN cust_trends ct ON tf.trends_id = ct.id
LEFT JOIN customer c ON c.id = tf.customer_id
WHERE tf.master_id = 1

UNION ALL

-- 給我留言
SELECT cb.id, 2, c.avatar, c.name, 3, cb.id, NULL, cb.content, cb.create_time, cb.read
FROM cust_board cb
LEFT JOIN customer c ON cb.customer_id = c.id
WHERE cb.master_id = 1

UNION ALL

-- 評論我的動態
SELECT tr.id, 3, c.avatar, c.name, ct.catalog, ct.id, ct.content, tr.content, tr.create_time, tr.read
FROM trends_reply tr
LEFT JOIN cust_trends ct ON tr.trends_id = ct.id
LEFT JOIN customer c ON c.id = tr.customer_id
WHERE tr.master_id = 1

UNION ALL

-- 回復我的評論/At 我
SELECT tra.id, 4, c.avatar, c.name, ct.catalog, tra.trends_id, ct.content, tra.content, tra.create_time, tra.read
FROM trends_replyat tra
LEFT JOIN trends_reply tr ON tra.reply_id = tr.id
LEFT JOIN customer c ON c.id = tra.customer_id
LEFT JOIN cust_trends ct ON ct.id = tr.trends_id
WHERE tra.at = 1 OR (tra.master_id = 1 AND tra.customer_id != 1)

UNION ALL

-- 留言板回復我
SELECT cbr.id, 5, c.avatar, c.name, 3, cb.id, cb.content, cbr.content, cbr.create_time, cbr.read
FROM cust_board_reply cbr
LEFT JOIN cust_board cb ON cbr.board_id = cb.id
LEFT JOIN customer c ON cbr.customer_id = c.id
WHERE (cbr.board_customer_id = 1 AND cbr.customer_id != 1) OR (cbr.master_id = 1 AND cbr.customer_id != 1)

ORDER BY createTime DESC LIMIT 2, 10;