子查询,查询出现在其他语句中的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