数据库MySQL-连接查询

数据库MySQL-连接查询

含义：又称为多表查询，当查询的字段来自于多个表时，用到连接查询。

笛卡尔乘积现象：表1 有m行，表2有n行， 结果有m*n行。
如：select name, boyName from boys,beauty;

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
	按年代分类：sql92标准：仅仅支持内连接，sql99标准（推荐）：支持内连接+外连接（左外和右外）+交叉连接
	
	按功能分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接
			右外连接
			全外连接
		
		交叉连接
		
**一、sql92标准**
1、sql92标准-等值连接

a、多表等值连接的结果为多表的交集部分
b、n表连接，至少需要n-1个连接条件
c、多表的顺序没有要求
d、一般需要为表起别名
e、可以搭配前面介绍的所有子句使用，比如：排序、分组、筛选。


案例1：查询女神名和对应的男神名
SELECT NAME, boyName FROM boys,beauty
WHERE beauty.`boyfriend_id` = boys.`id`;

案例2：查询员工名和对应的部门名
SELECT last_name,`department_name` 
FROM `departments`,`employees`
WHERE `employees`.`department_id` = `departments`.`department_id`;

案例3、 查询员工名、工种号、工种名(为表起别名:简洁、区分重名字段)
为表起了别名，就不能再用原来的表名。
SELECT last_name,e.`job_id`,job_title
FROM employees e,jobs j
WHERE e.`job_id` = j.`job_id`;

案例4、 两个表的顺序可以调换

案例5、 可以加筛选？查询有奖金的员工名、部门名
SELECT `last_name`,`department_name`
FROM `employees` e,`departments` d
WHERE e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL;

案例6、查询城市名中第二个字符为o的部门名和城市名
SELECT `department_name`,`city`
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';

案例7、可以加分组？查询每个城市的部门个数
SELECT COUNT(*),city
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.city;

案例8、查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT `department_name`,d.`manager_id`,MIN(`salary`)
FROM `departments` d,`employees` e
WHERE d.`department_id` = e.`department_id`
AND e.`commission_pct` IS NOT NULL
ORDER BY `department_name`,d.`manager_id`;

案例9、可以加排序。查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT `job_title`,COUNT(*) 员工个数
FROM `employees` e, `jobs` j
WHERE e.`job_id` = j.`job_id`
GROUP BY `job_title`
ORDER BY 员工个数 DESC;

案例10、可以实现三表连接，查询员工名、部门名和所在的城市
SELECT `last_name`,`department_name`,city
FROM `employees` e,`departments` d,`locations` c
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = c.`location_id`;

2.sql92标准-非等值连接
案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employee e, job_grades g
WHERE salary BETWEEN g.lowest_sal AND highest_sal
AND g.grade_level = 'A'

3.sql92标准-自连接
案列：查询员工名和上级的名称
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM employees e, employees m
WHERE e.`manager_id` = m.`employee_id`;

二、sql99语法

语法：
	select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】
	
内连接（*）：inner
外连接
	左外（*）：left【outer】
	右外（*）：right【outer】
	全外: full【outer】
交叉连接

*/
一）、内连接

语法：
select 查询列表
	from 表1 别名 
	inner join 表2 别名
	on 连接条件
	【】....;
分类：等值连接、非等值连接、自连接

特点：
a、添加排序、分组、筛选
b、inner可以省略
c、筛选条件放在where后边，连接条件放在了on后边，提高了可读性。
d、inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集。
*/
#1、等值连接
#案例1、查询员工名、部门名
SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`;

#案例2、查询名字中包含e的员工名和工种名（添加筛选）
SELECT `last_name`,`job_title`
FROM `employees` e
INNER JOIN `jobs` j
ON e.`job_id` = j.`job_id`
WHERE `last_name` LIKE '%e%';

#案例3、查询部门个数 > 3的城市名和部门个数。（添加分组+筛选）
SELECT `city`,COUNT(*) 部门个数
FROM `locations` l
INNER JOIN `departments` d
ON l.`location_id` = d.`location_id`
GROUP BY `city`
HAVING 部门个数 > 3;

#案例4、查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

SELECT `department_name`,COUNT(*)
FROM `departments` d
INNER JOIN `employees` e
ON e.`department_id` = d.`department_id`
GROUP BY `department_name`
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

#案例5、查询员工名、部门名、工种名，并按部门名降序（三表连接）
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
INNER JOIN `departments` d ON e.`department_id` = d.`department_id`
INNER JOIN `jobs` j ON j.`job_id` = e.`job_id`
ORDER BY `department_name` DESC;

#2、非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employee e
INNER JOIN job_grades g
ON salary BETWEEN g.lowest_sal AND highest_sal
WHERE g.grade_level = 'A'

#3、自连接
#案例：查询员工的名字、上级的名字
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM `employees` e
INNER JOIN `employees` m
ON e.employee_id = m.employee_id;

二）、外连接


应用场景：用于查询一个表中有，另一个表中没有的记录。
特点：
1、外连接的查询结果为主表中的所有记录
	如果从表中有和它匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示null。
	外连接查询结果  = 内连接结果 + 主表中有而从表没有的记录。
2、左外连接：left join左边的是主表
   右外连接：right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接 = 内连接的结果+表1中有但表2中没有的+表1中没有但表2中有的。
*/
#引入：查询男朋友不在男神表的女神名
#左外连接
SELECT b.name,bo.*
FROM `beauty` b
LEFT OUTER JOIN `boys` bo
ON b.`boyfriend_id` = bo.id
WHERE bo.`id` IS NULL;

#右外连接
SELECT b.name,bo.*
FROM `boys` bo 
RIGHT OUTER JOIN `beauty` b
ON b.`boyfriend_id` = bo.id
WHERE bo.`id` IS NULL;

#案例1：查询哪个部门没有员工

SELECT d.`department_id`, `department_name`
FROM `departments` d
LEFT JOIN `employees` e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

#全外连接：查出交集，以及主表在从表中未匹配的部分、从表在主表中未匹配的部分
SELECT d.`department_id`, `department_name`
FROM `departments` d
FULL JOIN `employees` e
ON d.`department_id` = e.`department_id`

交叉连接:笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

#sql92和sql99 pk
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高。