Mysql数据库基础第二章：（六）连接查询

Mysql数据库基础系列

软件下载地址
提取码：7v7u
数据下载地址
提取码：e6p9
mysql数据库基础第一章：（一）数据库基本概念
mysql数据库基础第一章：（二）mysql环境搭建
mysql数据库基础第二章：（一）基础查询
mysql数据库基础第二章：（二）条件查询
mysql数据库基础第二章：（三）排序查询
mysql数据库基础第二章：（四）常见函数
mysql数据库基础第二章：（五）分组查询
mysql数据库基础第二章：（六）连接查询
mysql数据库基础第二章：（七）子查询
mysql数据库基础第二章：（八）子查询经典案例
mysql数据库基础第二章：（九）分页查询
mysql数据库基础第二章：（十）连接查询
mysql数据库基础第三章：DML语言
mysql数据库基础第四章：DDL（数据定义语言）：库表的管理、数据类型与约束条件
mysql数据库基础第五章：（一）事务
mysql数据库基础第五章：（二）视图
mysql数据库基础第六章：变量、存储过程与函数
mysql数据库基础第七章：流程控制结构
mysql数据库基础第八章：窗口函数和公用表达式（CTE）

---

文章目录

• Mysql数据库基础系列
• • 一、基本语法
  • • 1.1sql99语法主要如下：
    • • 1.2主要分类：
  • 二、内连接
  • • • 2.1基本语法
      • 2.2 分类：
  • 三、 外连接
  • • 3.1 左外连接
    • 3.2 右外连接
    • 四、练习

---

一、基本语法

又称多表查询，当查询的字段分别来自于不同表时，就会用到连接查询。连接查询有sql92和sql99语法，sql92标准仅仅支持内连接，因此本文主要介绍sql99语法。

1.1sql99语法主要如下：

select coloun
from table1 连接类型
join table 2
on 连接条件

1.2主要分类：

内连接：inner
外连接：
	左外：left outer
	右外：right outer
	全外（用的较少）
交叉连接：cross（用的较少）相当于做了一个笛卡尔乘积

图1 内连接

图2 右外连接

图3 左外连接
图4 全外连接

二、内连接

内连接可以添加排序、分组、筛选等
筛选条件

2.1基本语法

select column
from table 1
inner join table 2
on 连接条件

2.2 分类：

• 等值连接

# 1 查询员工名、部门名
# 其中员工名在employees表，部门名在departments表
SELECT last_name,department_name
FROM departments d
JOIN  employees e
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 部门个数 > 3;
#4.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

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;

• 非等值连接

#1. 查询工资级别的个数>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 DESC;

• 自连接

#1.查询员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`;

#2.查询姓名中包含字符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%';

三、 外连接

用于查询一个表中有，另一个表没有的记录
外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

3.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;

3.2 右外连接

 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;

上述两种方法得到结果一致，部门信息在departments表中，departments表没有employee_id，这时我们想要的结果是部门信息＋员工id，因此，如果使用左外连接，则departments为主表，右外连接则employees为主表。

四、练习

#1.显示所有员工的姓名，部门号和部门名称。

SELECT last_name,d.department_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;


#2.查询90号部门员工的job_id和90号部门的location_id

SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;



#3.	选择所有有奖金的员工的 last_name , department_name , location_id , city

SELECT last_name , department_name , l.location_id , city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;


#4.选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name 

SELECT last_name , job_id , d.department_id , department_name 
FROM employees e,departments d ,locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND city = 'Toronto';



#5.查询每个工种、每个部门的部门名、工种名和最低工资


SELECT department_name,job_title,MIN(salary) 最低工资
FROM employees e,departments d,jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;



#6.查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING 部门个数>2;



#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees	Emp#	manager	Mgr#
kochhar		101	    king	100

SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM employees e,employees m
WHERE e.manager_id = m.employee_id
AND e.last_name='kochhar';