连接查询
连接查询,多表查询,查询字段涉及多个表。
没有有效连接条件就会产生笛卡尔乘积现象。
笛卡尔乘积现象:表1有m行,表2有n行,结果m*n行
分类
按年代分类
sql92标准:仅支持内连接
sql99标准:推荐,不支持全外连接
按功能分类
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全外连接
- 交叉连接
sql92标准
等值连接
- 多表等值连接的结果为多表的交集部分。
- 如果有字段重名,用表名限定字段; 如果表名太长,给表起别名;如果起了别名,对字段进行限制时只能用别名限制
- FROM后面的表名不分顺序
- 支持分组、排序、筛选
- 支持多表连接
- n表连接至少需要n-1个连接条件
查询rating是R的电影名和演员id
SELECT title,actor_id,rating
FROM film,film_actor
WHERE rating = 'R'and film.film_id=film_actor.film_id
查询电影名和演员名
SELECT title,last_name
FROM film f,film_actor fa,actor a
WHERE f.film_id = fa.film_id
AND a.actor_id= fa.actor_id;
查询地址、城市、国家名,筛选地址名长度大于10的,按城市降序排序
SELECT address,city,country
FROM address a,city c,country cc
WHERE a.city_id = c.city_id
AND c.country_id = cc.country_id
AND LENGTH(address)>10
ORDER BY city desc;
查询为R级的电影数量大于10部的类型和电影数,按类别分组,按数量降序
SELECT COUNT(title) 电影数,name 类别,rating 级别
FROM film_category fc,film f,category c
WHERE f.film_id=fc.film_id
AND fc.category_id = c.category_id
AND rating = 'R'
GROUP BY `name`
HAVING COUNT(title)>10
ORDER BY COUNT(title);
非等值连接
查找replacement_cost级别为A的电影名
SELECT title,grade_level
FROM film,grade
WHERE film.replacement_cost
BETWEEN grade.moneyL and grade.moneyH
AND grade_level = 'A';
自连接
一个表取不同别名,实现相同表之间的连接
SELECT a.last_name
FROM staff a,staff b
WHERE a.staff_id = b.store_id
sql99标准
语法
SELECT 查询列表
FROM 表1 别名 【连接类型】
JOIN 表2 别名
on 连接条件
WHERE 筛选条件
连接类型
- 内连接 inner
- 外连接
- 左外连接 LEFT (OUTER)
- 右外连接 RIGHT (OUTER)
- 全外连接 full (OUTER)
- 交叉连接
内连接
语法
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
on 连接条件
WHERE 筛选条件
特点
- 支持排序分组筛选
- INNER 可以省略
- WHERE后是筛选条件
- ON 后是连接条件
- 与92的效果一样
等值连接
查询rating是R的电影名和演员id
SELECT title,actor_id,rating
FROM film f
INNER JOIN film_actor fa
ON f.film_id = fa.film_id
WHERE rating='R'
查询电影名和演员名
SELECT title,last_name
FROM film f
INNER JOIN film_actor fa
ON f.film_id = fa.film_id
INNER JOIN actor a
ON fa.actor_id = a.actor_id;
查询地址、城市、国家名,筛选地址名长度大于10的,按城市降序排序
SELECT address,city,country
FROM address a
INNER JOIN city c ON a.city_id = c.city_id
INNER JOIN country cc ON cc.country_id = c.country_id
WHERE LENGTH(address)>10
ORDER BY city desc
查询为R级的电影数量大于10部的类型和电影数,按类别分组,按数量降序
SELECT COUNT(title),name,rating
FROM film f
INNER JOIN film_category fc ON f.film_id=fc.film_id
INNER JOIN category c ON fc.category_id = c.category_id
WHERE rating='R'
GROUP BY name
HAVING COUNT(title)>10
ORDER BY COUNT(title)
非等值连接
查找replacement_cost级别为A的电影名
SELECT replacement_cost,grade_level
FROM film f
INNER JOIN grade g
ON replacement_cost BETWEEN moneyL AND moneyH
WHERE grade_level = 'A'
自连接
SELECT a.last_name
FROM staff a
INNER JOIN staff b ON a.staff_id = b.store_id
WHERE a.last_name LIKE '%p%';
外连接
外连接,用于查询一个表有,另一个没有的值
- 外连接的查询结果是主表中的所有记录
- 如果从表中有和他匹配的,显示值
- 如果没有,显示null
- 外连接的查询结果=内连接的结果+主表中有而从表没有的记录
- 左外连接 LEFT JOIN 左边是主表
- 右外连接 RIGHT JOIN 右边是主表
- 如果交换左外右外两个表的顺序可以获得同样的结果
- 全外连接=内连接结果+表1中表2没有的+表2中表1没有的 FULL JOIN
#二者效果相同
SELECT ms,m.mname,aperson
FROM material m
LEFT OUTER JOIN attention a ON a.mname= m.mname;
SELECT ms,m.mname,aperson
FROM attention a
right OUTER JOIN material m ON a.mname= m.mname;
查找语言名是空的电影
SELECT title,`name`
FROM film m
LEFT JOIN `language` l ON l.language_id=m.language_id
WHERE `name` is NULL
查找演员fn名是ED的电影名
SELECT title,first_name,rating
FROM film m
left JOIN film_actor fa ON m.film_id=fa.film_id
left JOIN actor a ON fa.actor_id = a.actor_id
WHERE first_name ='ED'
交叉连接
交叉连接CROSS JOIN,显示笛卡尔乘积
SELECT a.*,fa.*
FROM actor a
CROSS JOIN film_actor fa ON fa.actor_id = a.actor_id;