mysql的连接查询
-
myemployees数据库
-
girls数据库
-
含义：又称为多表连接，当查询的字段来着于多个表的时候，就会用到连接查询
-
按照年代进行分类：
sql 92 标准 sql 99 标准 （内连接，外连接（左外。右外）和交叉连接） -
按照功能进行分类：
内连接：
等值连接 【*】
非等值连接 【*】
自连接 【*】
外连接：
左外连接 【*】
右外链接 【*】
全外连接
交叉连接 -
等值连接
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名I
⑤可以搭配前面介绍的所有子句 -
笛卡尔乘积现象：
表1有 n行数据，表2有 m行数据 结果会出现 n*m行数据
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
一般连接查询
等值连接
1、查询女神名和对应的男神名
SELECT NAME,boyName FROM beauty,boys
WHERE beauty.`boyfriend_id`=boys.`id`;
2、查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;
3、为表起别名
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
案例1：查询员工名、工种号、工种名（employees和jobs表）
SELECT last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;
案例2：查询城市名中第二个字符为 o（哦） 的部门名和城市名
SELECT department_name,city
FROM departments d, locations l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';
4、查询每个城市的部门个数（部门所在地locations和部门departments）
SELECT COUNT(*) 个数,city
FROM locations l,departments d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;
5、查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT d.department_name 部门名, d.manager_id 领导编号,MIN(salary)
FROM departments d, employees e
WHERE commission_pct IS NOT NULL
AND d.`department_id`=e.`department_id`
GROUP BY d.department_name,d.manager_id;
6、可以加排序，查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM jobs j,employees e
WHERE j.`job_id`=e.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
7、查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e, departments d , locations l
WHERE e.`department_id`=d.`department_id` AND d.`location_id` = l.`location_id`
[AND city LIKE '%s%'
ORDER BY city;]
#根据城市查询员工名、部门名和所在的城市且城市名中含有s
非等值连接
- 常见的就是BETWEEN AND的使用
1、查询员工的工资和工资级别（employees表和job_grades表）
SELECT salary , grade_level
FROM employees,job_grades
WHERE salary BETWEEN lowest_sal AND highest_sal;
自连接
- 可以简单的理解为自己连接自己
1、查询员工名和上级的名称（员工的上级id是employee_id）
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;
内连接【inner join】
-
和数学中的交集一样（重点记住此图）
-
语法：
select 查询列表 from 表1 别名【连接类型】 join 表2 别名 on 连接条件 【where 筛选的条件】 【group by 分组】 【having 筛选条件】 【order by 排序】 -
内连接【*】 inner
-
on作为两个或者多个表之间的关联条件（耦合条件）（仅仅是相连关系）
与一般连接的比较
1、查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`;
#================等价于======================
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
等值连接
1、查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;
2、查询名字中包含e的员工名和工种名（筛选）
SELECT e.`last_name`,j.`job_title`
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE e.`last_name` LIKE '%e%';
3、查询部门个数>3的城市名和部门个数，（添加分组+筛选）
（1）首先要查询的是城市中的部门个数（即要首先按照城市进行分组统计部门数量）
（2）然后根据查询到的部门个数判断是否大于3（是在分组之后得到的结果所以条件应该放在having之后）
（3）如果大于3就打印个数和城市名称
SELECT l.city,COUNT(*)
FROM locations l
INNER JOIN departments d
ON l.`location_id`=d.`location_id`
GROUP BY l.city
HAVING COUNT(department_id)>3;
4、查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
（1）首先要查询的是部门中的员工个数（即首先按照部门进行分组统计数量）
（2）然后根据查询到的员工个数判断是否大于3（是在分组之后得到的结果所以条件应该放在having之后）
（3）如果大于3就打印部门名和员工个数
（4）同时是按照查询到的数量进行排序操作（依次添加条件）
SELECT department_name,COUNT(*)
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`\
GROUP BY d.`department_id`
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`
# GROUP BY department_name
ORDER BY department_name DESC;
非等值连接
1、查询员工的工资和级别（on的后面就是连接条件）字段条件放在where后面
SELECT salary , grade_level
FROM employees,job_grades
WHERE salary BETWEEN lowest_sal AND highest_sal;
#============等价于======================
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades jg
ON e.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;
2、查询工资级别的员工个数>20的个数，并且按工资级别降序
（1）根据员工的工资确定工资级别
（2）根据级别确定每个级别员工的个数
（3）根据员工的个查询出符合条件
（4）更具等级进行排序
SELECT salary,grade_level,COUNT(*)
FROM employees e
INNER JOIN job_grades jg
ON e.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY grade_level DESC;
自连接
1、查询包含字符k的员工名字、上级的名字
（1）连接条件就是manager_id上级领导的编号和employee_id员工的编号
（2）查询包含字符k的员工名（上级领导也是有员工编号的）
SELECT e.last_name,m.`last_name`
FROM employees e
INNER JOIN employees m
ON e.`manager_id` = m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
外连接
-
应用场景：用于查询一个表中有，另外一个表中没有的记录
-
特点:
1、外连接的查询结果为主表的所有记录
- (1)如果从表中有和他匹配的值 则显示匹配的值
- (2)如果从表中没有和他匹配的值 则显示null值
- (3)外连接查询结果=内连接查询结果+主表中没有而从表中没有的记录
- (4)可以通过控制条件来决定是否需要内连接
2、 左外连接：left join左边的表是主表
- 右外连接：right join右边的表是主表
3、左外和右外交换两个表的顺序，也能实现同样的效果
4、全外连接：内连接查询的结果+表1中有但是表2中没有的结果+表2中有但是表1中没有
-
左外连接表示图(A为主表)：
-
右外连接表示图(B为主表)：
- 全外连接表示图（mysql不支持）：
-
全外连接去交集
-
语法：
select 查询列表 from 表1 别名【连接类型】 join 表2 别名 on 连接条件 【where 筛选的条件】 【group by 分组】 【having 筛选条件】 【order by 排序】 -
外连接
左外【*】 left 【outer】
右外【*】 right 【outer】
全外 full 【outer】 -
外连接的使用一定搞清楚那个表是主表（很重要）
1、查询男朋友不在女神表的的女神名
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;
#============上面等价于beauty表去除交集部分beauty是主表========================
#============下面等价于boys表去除交集部分boys是主表========================
SELECT bo.*,b.name
FROM boys bo
LEFT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE b.`boyfriend_id` IS NULL;
右外连接也能实现（beauty是主表）
SELECT b.name,bo.*
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;
2、查询哪个部门没有员工（主表是部门）
（仅仅是记忆的一种方式）
简单记为左连接主表放在FROM后面
右链接主表放在JOIN后面
# 左连接实现
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;
# 右连接实现
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.`employee_id` IS NULL;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
