萬盛學電腦網

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

SQL 基礎語句大全:建庫建表、增刪改查、結構變更與進階技巧完整手冊

資料庫與資料表管理

建立/切換/刪除資料庫

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)

-- 新增欄位
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;

資料操作 (DML)

插入資料

-- 單筆
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;

進階查詢技巧

連接查詢 (JOIN)

-- 內連接:雙方都有匹配
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 (MySQL 8.0+)

-- 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;

窗口函數 (MySQL 8.0+)

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 分析執行計畫、窗口函數簡化複雜邏輯,是後端開發與資料分析的核心基本功。

copyright © 萬盛學電腦網 all rights reserved