基礎查詢與別名
SELECT * FROM pet;
SELECT name, owner FROM pet;
SELECT SIN(1+2) AS sin_val; -- 算術運算與別名
WHERE 條件過濾
-- 複合條件
SELECT * FROM pet WHERE (birth > '1980' AND species='dog') OR species='bird';
-- NULL 判斷
SELECT * FROM pet WHERE sex IS NOT NULL;
-- 模糊匹配（_ 單字符，% 任意長度）
SELECT * FROM pet WHERE owner LIKE '___n%';
-- 集合匹配
SELECT * FROM pet WHERE owner IN ('gwen', 'benny');
-- 範圍匹配
SELECT * FROM pet WHERE birth BETWEEN '1990' AND '1999';
排序與去重
SELECT * FROM pet ORDER BY owner, name DESC;
SELECT * FROM pet WHERE sex='m' ORDER BY birth DESC;
SELECT DISTINCT owner FROM pet WHERE species='dog';
字符串與日期函數
SELECT owner, LENGTH(owner), CHAR_LENGTH(owner) FROM pet;
-- 兩種方式提取年月
SELECT name, LEFT(birth,4) AS yr, MID(birth,6,2) AS mo FROM pet WHERE species IN ('dog','cat');
SELECT name, YEAR(birth) AS yr, MONTH(birth) AS mo FROM pet WHERE species IN ('dog','cat');
數值與日期運算
SELECT ROUND(2.345,2), TRUNCATE(2.345,2), MOD(323,5);
SELECT NOW(), CURDATE(), CURTIME();
SELECT ADDDATE('2007-02-02', INTERVAL 31 DAY);
計算寵物年齡（兩種寫法）
SELECT name, birth,
TRUNCATE(DATEDIFF(NOW(), birth)/365, 0) AS age1,
YEAR(NOW()) - YEAR(birth) - (DAYOFYEAR(birth) > DAYOFYEAR(NOW())) AS age2
FROM pet;
聚合函數與 GROUP BY
-- 全表聚合
SELECT MIN(birth), MAX(birth), AVG(birth), COUNT(*), COUNT(sex), SUM(birth) FROM pet;
-- 分組統計
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT YEAR(birth), COUNT(*) FROM pet GROUP BY YEAR(birth);
-- 多欄位分組
SELECT species, sex, COUNT(*) FROM pet WHERE species IN ('cat','bird') GROUP BY species, sex;
-- 篩選分組
SELECT species, COUNT(*) AS c FROM pet GROUP BY species HAVING c >= 2;
多表關聯查詢
-- 內連接（隱式/顯式）
SELECT a.name, a.species, a.sex, b.date, b.type, b.remark
FROM pet a, event b WHERE a.name = b.name;
SELECT a.name, a.species, TRUNCATE(DATEDIFF(b.date, a.birth)/365, 0) AS age
FROM pet a INNER JOIN event b ON a.name = b.name
WHERE b.type = 'litter';
-- 90 年代出生狗的事件
SELECT a.name, a.birth, a.species, a.sex, b.date, b.type, b.remark
FROM pet a INNER JOIN event b ON a.name = b.name
WHERE a.birth BETWEEN '1990' AND '1999' AND a.species = 'dog';
-- 活著寵物的事件類型統計
SELECT b.type, COUNT(*)
FROM pet a INNER JOIN event b ON a.name = b.name
WHERE a.death IS NULL
GROUP BY b.type;
-- 事件超過 1 條的寵物
SELECT a.name, a.species, a.sex, COUNT(*)
FROM pet a INNER JOIN event b ON a.name = b.name
GROUP BY a.name HAVING COUNT(*) >= 2;
-- 子查詢：恰好 2 條事件的寵物詳細記錄
SELECT a.name, a.type, a.date, a.remark, b.species, b.sex, b.owner
FROM event a INNER JOIN pet b ON a.name = b.name
WHERE b.name IN (
SELECT name FROM event GROUP BY name HAVING COUNT(*) = 2
);
DML 寫操作
-- 插入
INSERT INTO pet (name, species, birth) VALUES ('KKK', 'snake', '2007-01-01');
INSERT INTO pet VALUES ('KK', 'Diane', 'cat', 'f', NULL, NULL);
INSERT INTO pet SET name='k', owner='Benny';
-- 更新
UPDATE pet SET species='snake', sex='f', birth=NOW() WHERE name='k';
-- 子查詢更新：同步 event 表生日到 pet
UPDATE pet a
SET birth = (SELECT date FROM event b WHERE a.name=b.name AND b.type='birthday')
WHERE a.name IN (SELECT name FROM event WHERE type='birthday');
-- 刪除
DELETE FROM pet WHERE name LIKE 'k%';
通用查詢模板速查
SELECT * FROM `t` WHERE 1;
SELECT id, uid FROM `t` WHERE 1;
SELECT remark AS r FROM `t` WHERE 1;
SELECT * FROM `t` WHERE id IN (1,3);
SELECT * FROM `t` WHERE id NOT IN (2,3);
SELECT * FROM `t` WHERE uid LIKE '%王%';
SELECT * FROM `t` WHERE id BETWEEN 1 AND 3;
SELECT * FROM `t` WHERE id=1 AND remark='學生';
SELECT * FROM `t` GROUP BY remark;
SELECT * FROM `t` ORDER BY regdate ASC, id DESC;
SELECT * FROM `t` LIMIT 0, 3;
SELECT COUNT(*) FROM `t`;
SELECT MAX(id), MIN(id), AVG(score), SUM(score) FROM `t`;
基礎寫操作模板
INSERT INTO `t` (`id`,`uid`,`regdate`,`remark`) VALUES ('', 'PHP100', '2008-07-26', '工人');
UPDATE `t` SET uid='php200' WHERE id=6;
DELETE FROM `t` WHERE id=3;
