select高级 3 ：子查询

• 子查询是在一个查询的内部包括另一个查询的查询方式
• 创建表1 comment（有外键）

 

---

 

• 表2--news--有外键

---

 

•  表3--topic--无外键

 

1.简单子查询

• 查询比 ‘张东’ 评论日期晚的新闻评论者信息

-- （1）
-- 查询大于该时间的数据
SELECT *
FROM `comment`
WHERE cmtDate>'2018-12-20 00:00:00'

SELECT cmtDate FROM `comment` WHERE cmtAuthor='东东1'

-- 子查询
SELECT *
FROM `comment`
WHERE cmtDate>(SELECT cmtDate FROM `comment` WHERE cmtAuthor='东东1')

 

 2. IN和NOT IN子查询

• IN---查询名字中包含’东‘的作者评论过的所有新闻

SELECT * FROM `comment` WHERE cmtId IN(1,2,3)

-- LIKE为模糊查询
SELECT newsId FROM `comment` WHERE cmtAuthor LIKE '%东%'

-- 子查询
SELECT * 
FROM `comment` 
WHERE cmtId 
IN(SELECT newsId FROM `comment` WHERE cmtAuthor LIKE '%东%')

•  NOT IN--- 查询名字中不包含’东‘的作者评论过的所有新闻

-- NOT IN子查询
SELECT * FROM news
WHERE id 
NOT IN 
(SELECT newsId FROM `comment` WHERE cmtAuthor LIKE '%东%')

 3. ANY / SOME子查询

    SELECT * FROM news
    WHERE id = 
    ANY
    (SELECT newsId FROM `comment` WHERE cmtAuthor LIKE '%东%')

4. ALL子查询

-- ALL子查询
SELECT * FROM news
WHERE id >= 
ALL
(SELECT newsId FROM `comment` WHERE cmtAuthor LIKE '%东%')