資料庫與表的創建
-- 1. 查看現有資料庫
SHOW DATABASES;
-- 2. 創建資料庫（指定字符集）
CREATE DATABASE menagerie CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 3. 選擇資料庫
USE menagerie;
-- 4. 創建寵物表
CREATE TABLE pet (
name VARCHAR(30) NOT NULL,
owner VARCHAR(30) NOT NULL,
species VARCHAR(20) NOT NULL,
sex ENUM('m','f') NOT NULL,
birth DATE NOT NULL,
death DATE DEFAULT NULL,
PRIMARY KEY (name, owner) -- 複合主鍵示例
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
資料匯入三種方式
方式一：LOAD DATA INFILE（最高效）
-- pet.txt 內容（Tab 分隔，NULL 用 \N）
Fluffy Harold cat f 1993-02-04 \N
Claws Gwen cat m 1994-03-17 \N
...
-- 匯入
LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
方式二：INSERT 批次
INSERT INTO pet (name, owner, species, sex, birth, death) VALUES
('Fluffy', 'Harold', 'cat', 'f', '1993-02-04', NULL),
('Claws', 'Gwen', 'cat', 'm', '1994-03-17', NULL),
...;
方式三：INSERT ... SELECT 複製表
CREATE TABLE pet_archive LIKE pet;
INSERT INTO pet_archive SELECT * FROM pet WHERE death IS NOT NULL;
多表關聯查詢實例
-- 建立事件表
CREATE TABLE event (
name VARCHAR(30) NOT NULL,
date DATE NOT NULL,
type VARCHAR(15) NOT NULL,
remark VARCHAR(255),
PRIMARY KEY (name, date)
);
-- 關聯查詢：寵物及其事件
SELECT p.name, p.species, e.date, e.type, e.remark
FROM pet p
LEFT JOIN event e ON p.name = e.name
ORDER BY p.name, e.date;
常用維護指令
| 操作 | 指令 |
|---|
| 檢視表結構 | DESCRIBE pet; / SHOW CREATE TABLE pet; |
| 修改欄位 | ALTER TABLE pet MODIFY species VARCHAR(30); |
| 新增索引 | ALTER TABLE pet ADD INDEX idx_species (species); |
| 備份結構+資料 | mysqldump -u root -p menagerie > backup.sql |
| 僅備份結構 | mysqldump -u root -p -d menagerie > schema.sql |
日期運算技巧
-- 計算年齡（年）
SELECT name, birth, TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet;
-- 即將過生日（30 天內）
SELECT name, birth FROM pet
WHERE DATE_ADD(birth, INTERVAL YEAR(CURDATE())-YEAR(birth) YEAR)
BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY);
