mysql员工类 mysql 员工表部门表

转载

mob64ca140ac564 2024-04-11 13:53:22

文章标签 mysql员工类外连接内连接右外连接 文章分类 MySQL 数据库

前言

这里有两种表，用来测试连接方式

# 部门表
CREATE TABLE `departments` (
	`department_id` INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`department_name` VARCHAR(3) DEFAULT NULL
) ENGINE=INNODB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312

# 员工表
CREATE TABLE employee(
	`employee_id` INT(6) PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`employee_name` varchar(30) NOT NULL,
	`manager_id` INT(6) DEFAULT NULL,	# 领导的编号
	`department_id` INT(4) DEFAULT NULL
) ENGINE=INNODB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312

# 数据
insert into `departments` (`department_id`, `department_name`) values('271','人事部');
insert into `departments` (`department_id`, `department_name`) values('272','销售部');
insert into `departments` (`department_id`, `department_name`) values('273','研发部');
insert into `departments` (`department_id`, `department_name`) values('274','管理部');
insert into `employee` (`employee_id`, `manager_id`, `department_id`, `employee_name`) values('207',NULL,'274','老板');
insert into `employee` (`employee_id`, `manager_id`, `department_id`, `employee_name`) values('208','207','271','老王');
insert into `employee` (`employee_id`, `manager_id`, `department_id`, `employee_name`) values('209','207','273','老李');
insert into `employee` (`employee_id`, `manager_id`, `department_id`, `employee_name`) values('210','209','273','小李');

正文

连接查询主要分为三种：

内连接
外连接
交叉连接

内连接

内连接是用比较运算符比较要连接的列的值的连接，不匹配的行不会被显示。
根据所使用的比较方式不同，内连接分为等值连接、自然连接和自连接三种。
关键字：INNER JOIN 或 JOIN（通常我们使用：JOIN）

等值连接

使用”=”关系将表连接起来的查询，其查询结果中列出被连接表中的所有列，包括其中的重复列

SELECT 
	e.*,
	d.*
FROM employee e
INNER JOIN departments d ON e.department_id = d.department_id

mysql员工类 mysql 员工表部门表_右外连接

自然连接

等值连接中去掉重复的列，形成的连接。

SELECT 
	e.employee_id,
	e.manager_id,
	d.department_id,
	d.department_name
FROM employee e
INNER JOIN departments d ON e.department_id = d.department_id

mysql员工类 mysql 员工表部门表_右外连接_02

自连接

情景：查询每个员工的上级领导（根据 manager_id）

# emp 、mag逻辑上是两张表，物理上是一张表
SELECT 
	emp.*,
	mag.employee_id AS manager_id,
	mag.employee_name AS manager_name
FROM employee emp # 员工表
INNER JOIN employee mag # 领导表
ON emp.manager_id = mag.employee_id

mysql员工类 mysql 员工表部门表_右外连接_03

内连接简单点来将就是：不匹配的行不会显示，只会显示连接条件的行（和接下来的外连接对比就很明显了）

外连接

外连接包括三种,分别是左外连接、右外连接、全外连接。
在左、右外连接中都会以一种表为基表，基表的所有行、列都会显示，外表如果和条件不匹配则所有的外表列值都为NULL。
全外连接则所有表的行、列都会显示，条件不匹配的值皆为NULL。

左外连接

以左表（即LEFT 左边的边）为基表，基表的所有行、列都会显示，外表（右表）如果和条件不匹配则所有的外表列值都为NULL。
关键字：LEFT OUTER JOIN 或 LEFT JOIN

# 给员工表增加一条记录
insert into `employee` (`employee_name`) values('老板娘');

左连接

SELECT
	e.*,
	d.*
FROM employee e
LEFT JOIN departments d ON e.department_id = d.department_id

你会发现，老板娘没有属于任何一个部门，但是她也显示出来了。
这就是外连接的含义：在左、右外连接中都会以一种表为基表，基表的所有行、列都会显示，外表如果和条件不匹配则所有的外表列值都为NULL。
如果是用内连接，“老板娘” 是不会显示出来，因为部门表没有和她匹配的数据

mysql员工类 mysql 员工表部门表_内连接_04

右连接

以右表（即 RIGIN 右边的边）为基表，基表的所有行、列都会显示，外表（左表）如果和条件不匹配则所有的外表列值都为NULL。
关键字：RIGHT OUTER JOIN 或 RIGHTJOIN

SELECT
	e.*,
	d.*
FROM employee e
RIGHT JOIN departments d ON e.department_id = d.department_id

mysql员工类 mysql 员工表部门表_右外连接_05

全外连接

全外连接结合了左外连接和右外连接的特点，即左右两边不匹配的数据，相对外表都为null，都会显示出来

关键字：FULL OUTER JOIN 或 FULL JOIN
注意：

MySQL不支持 FULL JOIN，但可通过左外连接(left join)+union+右外连接(right join)来实现
Oracle 支持 FULL JOIN
MySQL实现全外连接，例如：

select persons.last_name, persons.first_name,orders.order_no
from persons
left join orders
on persons.id_p = orders.id_p
union
select persons.last_name, persons.first_name,orders.order_no
from persons
right join orders
on persons.id_p = orders.id_p;