CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mydb;
DROP DATABASE mydb;
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用戶名',
email VARCHAR(100) NOT NULL UNIQUE COMMENT '信箱',
password_hash CHAR(60) NOT NULL COMMENT 'bcrypt 雜湊',
status TINYINT NOT NULL DEFAULT 1 COMMENT '1=啟用 0=停用',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_status (status),
INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';
-- 新增欄位
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
-- 修改欄位型別/屬性
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30) NULL;
-- 重命名欄位
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30) NULL;
-- 刪除欄位
ALTER TABLE users DROP COLUMN mobile;
-- 新增索引
ALTER TABLE users ADD INDEX idx_email (email);
-- 刪除索引
ALTER TABLE users DROP INDEX idx_email;
DESC users;
SHOW CREATE TABLE users;
SHOW COLUMNS FROM users;
-- 單筆
INSERT INTO users (username, email, password_hash)
VALUES ('john', 'john@example.com', '$2y$10$...');
-- 多筆批次(效能最佳)
INSERT INTO users (username, email, password_hash) VALUES
('alice', 'alice@test.com', '$2y$10$...'),
('bob', 'bob@test.com', '$2y$10$...');
-- 從其他表複製
INSERT INTO archive_users SELECT * FROM users WHERE status = 0;
-- 全欄位
SELECT * FROM users;
-- 指定欄位、別名、條件、排序、分頁
SELECT id, username AS name, email
FROM users
WHERE status = 1
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
UPDATE users
SET status = 0, updated_at = NOW()
WHERE id = 123;
-- 條件刪除
DELETE FROM users WHERE status = 0 AND created_at < '2020-01-01';
-- 清空表(重置自增 ID,不可回滾,速度快)
TRUNCATE TABLE users;
-- 內連接:雙方都有匹配
SELECT u.username, o.order_no, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 左連接:保留左表所有行
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
-- CTE 遞迴查詢分類樹
WITH RECURSIVE cat_tree AS (
SELECT id, name, parent_id, 1 AS level FROM categories WHERE parent_id = 0
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.level + 1
FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT * FROM cat_tree;
SELECT
id, amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn,
LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amount
FROM transactions;
-- 建立使用者
CREATE USER 'app_user'@'%' IDENTIFIED BY 'strong_password';
-- 授權
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'%';
FLUSH PRIVILEGES;
-- 撤銷
REVOKE DELETE ON mydb.* FROM 'app_user'@'%';
-- 版本
SELECT VERSION();
-- 當前連線數
SHOW STATUS LIKE 'Threads_connected';
-- 表大小統計
SELECT
TABLE_NAME,
ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb,
ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb,
TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;
熟練掌握 DDL/DML/DCL 基礎語法,善用索引、EXPLAIN 分析執行計畫、窗口函數簡化複雜邏輯,是後端開發與資料分析的核心基本功。