第 3 章 · 条件、运算符与模糊查询

SELECT title, dynasty, views
FROM article
WHERE dynasty = '唐' AND views > 250;

SELECT title, dynasty, views
FROM article
WHERE dynasty = '唐' OR views > 400;

SELECT id, title, dynasty
FROM article
WHERE NOT dynasty = '唐';

-- 想表达：(宋 或 唐) 且 推荐
-- 实际含义：宋 或 (唐 且 推荐)   ← AND 先算，OR 后算
SELECT title, dynasty, is_recommend
FROM article
WHERE dynasty = '宋' OR dynasty = '唐' AND is_recommend = 1;

-- 加上括号，明确告诉数据库先算哪一半
SELECT title, dynasty, is_recommend
FROM article
WHERE (dynasty = '宋' OR dynasty = '唐') AND is_recommend = 1;

-- 标题里只要出现「思」就算命中
SELECT id, title, author FROM article WHERE title LIKE '%思%';

-- 以「念」开头（右边不加 % 就是「到此结束」）
SELECT id, title FROM article WHERE title LIKE '念%';

-- 「声声」后面恰好还有 1 个字
SELECT id, title FROM article WHERE title LIKE '声声_';

-- 「静」和「思」中间恰好夹 1 个字
SELECT id, title FROM article WHERE title LIKE '静_思';

-- 标题或摘要里命中「月」
SELECT id, title, summary
FROM article
WHERE title LIKE '%月%' OR summary LIKE '%月%';

SELECT id, title, dynasty
FROM article
WHERE dynasty IN ('唐', '宋');

SELECT id, title, dynasty
FROM article
WHERE dynasty NOT IN ('唐', '宋');

SELECT title, views
FROM article
WHERE views BETWEEN 200 AND 400
ORDER BY views DESC;

-- <> 和 != 完全等价，推荐写 <>（SQL 标准写法）
SELECT title, views
FROM article
WHERE views <> 328 AND views >= 200
ORDER BY views DESC;

SELECT title, views,
       views + 100 AS 加权浏览量,
       views - 100 AS 修正浏览量,
       views % 100 AS 尾数
FROM article
WHERE id <= 3;

-- 先乘 100 再除，避免小数精度损失；ROUND 保留 2 位小数
SELECT title, likes, views,
       ROUND(likes * 100 / views, 2) AS 点赞率百分比
FROM article;

-- 顶层书评（没有父评论）
SELECT id, article_id, parent_id, content
FROM comment
WHERE parent_id IS NULL;

-- 回复别人的评论（楼中楼）
SELECT id, article_id, parent_id, content
FROM comment
WHERE parent_id IS NOT NULL;

-- 错误写法：WHERE 在 SELECT 之前执行，此刻「双倍浏览量」这个别名还不存在
SELECT title, views * 2 AS 双倍浏览量
FROM article
WHERE 双倍浏览量 > 600;

SELECT title, views * 2 AS 双倍浏览量
FROM article
WHERE views * 2 > 600;

SELECT title, views * 2 AS 双倍浏览量
FROM article
ORDER BY 双倍浏览量 DESC
LIMIT 3;