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

连接查询

连接查询，多表查询，查询字段涉及多个表。
没有有效连接条件就会产生笛卡尔乘积现象。
笛卡尔乘积现象：表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;