MySQL基础复习与整理---DQL数据查询语言---子查询

子查询，查询出现在其他语句中的select语句
外部的查询语句，称为主查询，外查询

按结果集的行列数不同

• 标量子查询（结果集一行一列)
• 列子查询（结果集一列多行）
• 行子查询（结果集一行多列）
• 表子查询（结果集一般为多行多列）

按子查询出现的位置

• SELECT 后面：只支持标量子查询
• FROM 后面：支持表子查询
• WHERE/HAVING后面：
  • 标量子查询
  • 列子查询
  • 行子查询（较少）
• EXISTS 后面（相关子查询)

SELECT 后面

查找每个演员的电影个数

SELECT a.*,(
	SELECT COUNT(film_id)
	FROM film_actor m
	WHERE a.actor_id = m.actor_id
)
FROM actor a

FROM 后面

FROM 后面 把子查询的结果作为一张表，必须起别名

每个rating的平均cost的等级grade

SELECT g.grade_level, agr.*
FROM (
	SELECT AVG(replacement_cost) ag,rating
	FROM film
	GROUP BY rating
) agr
INNER JOIN grade g 
ON agr.ag BETWEEN moneyL AND moneyH

WHERE/HAVING后面

• 标量子查询（单行子查询）
• 列子查询（多行子查询）
• 行子查询（多列多行）

特点

• 子查询放在小括号内
• 一般放在条件右侧
• 标量子查询一般搭配单行操作符使用（> < >= <= = <>）
• 列子查询 多行操作符（in ANY/SOME ALL）
  • IN 列表中的任意一个
  • ANY/SOME 和子查询返回的某个值比较
  • ALL 和子查询返回的所有值比较

标量子查询

哪个电影的cost比ACADEMY DINOSAUR 高

SELECT title
FROM film
WHERE replacement_cost>(
	sELECT replacement_cost
	FROM film
	WHERE title = 'ACADEMY DINOSAUR')

查找rating和film_id=141相同，replacement_cost>143号电影的电影

SELECT title,rating,replacement_cost
FROM film
WHERE rating=(
	SELECT rating
	FROM film
	WHERE film_id = '141'
)
AND replacement_cost>(
	SELECT replacement_cost
	FROM film
	WHERE film_id = '143'
)

查询cost最少的 电影的 title,rating,cost

SELECT title,rating,replacement_cost
FROM film 
WHERE replacement_cost=(
	SELECT MIN(replacement_cost)
	FROM film
)

查询最低cost>= ‘R’rating的最低cost的 rating 和其最低cost

SELECT MIN(replacement_cost),rating
FROM film
GROUP BY rating
HAVING MIN(replacement_cost)>=(
	SELECT MIN(replacement_cost)
	FROM film
	WHERE rating = 'R'
)

非法使用标量子查询：用单行查多行

SELECT MIN(replacement_cost),rating
FROM film
GROUP BY rating
HAVING MIN(replacement_cost)>=(
	SELECT replacement_cost
	FROM film
	WHERE rating = 'R'
)

多行子查询

查找language_id 是2或6的rating中所有title

SELECT title
FROM film
WHERE rating in ( #in 或 = any
	SELECT rating
	FROM film 
	WHERE language_id IN (2,6)
)

返回其他rating中比 `rating 为 R的任一cost低的 title

SELECT title,rating,replacement_cost
FROM film
WHERE replacement_cost < ANY(
	SELECT replacement_cost
	FROM film
	WHERE rating = 'R' 
)
AND rating!='R'

返回其他rating中比 `rating 为 R的所有cost<=的 title

SELECT title,rating,replacement_cost
FROM film
WHERE replacement_cost <= ALL(#< MIN(replacement_cost)
	SELECT DISTINCT replacement_cost
	FROM film
	WHERE rating = 'R' 
)
AND rating!='R'

查询各rating中比本rating平均cost高的电影名，cost

1 查询各rating的平均cost

SELECT AVG( replacement_cost),rating
	FROM film
	GROUP BY rating;

2 连接1的结果表，并进行筛选

SELECT title,replacement_cost,m.rating,agc.ag
FROM film m
INNER JOIN (
	SELECT AVG( replacement_cost) ag ,rating
	FROM film
	GROUP BY rating
) agc
ON agc.rating = m.rating
WHERE replacement_cost > agc.ag
GROUP BY title
ORDER BY replacement_cost

EXISTS后面

EXISTS后面：相关子查询，查询子查询列表是否有值，返回1或0

查询rating是A的film_id是否有值

SELECT EXISTS(SELECT film_id 
FROM film
WHERE rating = 'A')

查询rating是R的actor_id

#三者效果相同
SELECT actor_id 
FROM film_actor fa
WHERE EXISTS(
	SELECT film_id 
	FROM film f
	WHERE rating = 'R'
  	AND fa.film_id = f.film_id
)


SELECT actor_id 
FROM film_actor fa
WHERE film_id IN(
	SELECT film_id 
	FROM film f
	WHERE rating = 'R'
  AND fa.film_id = f.film_id
)

SELECT actor_id 
FROM film_actor fa
WHERE film_id = ANY(
	SELECT film_id 
	FROM film f
	WHERE rating = 'R'
  AND fa.film_id = f.film_id
)

查询有language名的电影名

SELECT EXISTS(SELECT `name`
FROM `language` l
WHERE language_id = 7
);#如果本身就是null值，返回1；如果数据不存在，返回0

SELECT title
FROM film m 
WHERE EXISTS (
	SELECT *
	FROM `language` l
	WHERE m.language_id = l.language_id 
  AND l.language_id = 8
)

查找没有关注人的material信息

SELECT *
FROM material
WHERE mname not in (
	SELECT mname
	FROM attention
);

SELECT m.*
FROM material m
WHERE not EXISTS (
	SELECT a.mname
	FROM attention a
	WHERE m.mname = a.mname
);

not in 可以直接写
EXISTS 还要写一下链接条件

执行顺序

SELECT 查询列表 7
from 表 1
JOIN… 2
ON 3
WHERE… 4
GROUP BY… 5
HAVING … 6
ORDER BY … 8
LIMIT OFFSET,size; 9