mysql打卡学习9各种连接

二，sq199语法

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

一.内连接

/*
语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点
1.添加排序，分组，筛选
2.inner可以省略
3.筛选条件可以放在where后面，连接条件放在on后面，提高分离性
4.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 e.`last_name` LIKE "%e%";

#(3)部门个数>3的城市名和部门个数
SELECT city,COUNT(*)
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

#(4)查询哪个部门的员工个数>3的部门名和员工个数，个数降序
SELECT department_name,COUNT(*)
FROM departments AS d
INNER JOIN employees AS e
ON d.`department_id`=e.`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 e.`job_id`=j.`job_id`;
ORDER BY department_name DESC;

2.非等值连接

#(1)查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#(2)每个工资级别个数>20,并且按照级别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level;

#(3)自连接
#姓名包含k员工名，上级名
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE "%k%";

2.外连接

/*
应用场景：用于查询一个表中有，另一个表没有的记录
就是俩个表有交集和无交集
特点
1.外连接的查询结果为主表中的所有记录
	如果从表中有和它匹配的，则显示匹配值
	如果从表中没有和它匹配的，则显示null
	外连接查询结果=内链接结果+主表中有而从表没有的记录
2.左外连接，left join左边是主表
  右外连接，right join右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果
4.全外连接=内连接结果+表1中有但是表二中没有+表2中有但是表一中有，就是合集
*/

#查询男朋友不在男神表的女神名
SELECT * FROM beauty;
SELECT * FROM boys;

#左外连接
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.*,bo.*
FROM  boys bo
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
#分组之前
WHERE bo.`id` IS NOT NULL;

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

练习

#1.查询哪个部门么有员工 
#左外
SELECT d.*,e.employee_id
FROM departments d #主表
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

#右外
SELECT d.*,e.employee_id
FROM employees e 
RIGHT OUTER JOIN departments d#主表
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

#全外连接
USE girls;
SELECT b.*,bo.*
FROM beauty b

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

练习

#1.查询编号>3的女神的男朋友的信息，如果有则列出，如果没有，用null填充
SELECT b.id,b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE b.id>3;

#2.查询哪个城市没有部门
SELECT city,d.*
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;

#3.查询部门名为sal或者it的员工信息
SELECT e.*,d.department_name
FROM departments d
LEFT JOIN employees e
ON e.`department_id`=d.`department_id`
WHERE d.`department_name` IN('SAL','IT');

SELECT * FROM departments
WHERE `department_name` IN('SAL','IT');