MySQL多表查询全面解析实例【汇总】

经常遇到mysql多表查询的问题，特整理关于MySQL多表查询全面解析实例分享记录。一步一步实战MySQL多表查询。

 

创建虚拟数据

-- [创建公司职员表] --

DROP TABLE IF EXISTS `company_staff`;  -- 如果company_staff表存在则先删除重建
CREATE TABLE `company_staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) DEFAULT '0',
  `sex` enum('男','女','保密') NOT NULL DEFAULT '保密',
  `salary` decimal(10,3) NOT NULL DEFAULT '10000.000',
  `hire_date` date NOT NULL,
  `company_department_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- [添加职员数据] --

-- [软件开发部]  --
INSERT INTO `company_staff` VALUES ('1', 'Tom', '25', '男', '27000.000', '2019-04-21', '1');
INSERT INTO `company_staff` VALUES ('2', 'Roudo', '27', '男', '38000.000', '2020-02-21', '1');
INSERT INTO `company_staff` VALUES ('3', 'Aaron', '30', '男', '26500.000', '2017-03-11', '1');
INSERT INTO `company_staff` VALUES ('4', 'Lance', '48', '男', '46680.000', '2003-05-20', '1');

-- [国际事务]  --
INSERT INTO `company_staff` VALUES ('5', 'Leo', '23', '男', '13600.000', '2017-06-21', '2');
INSERT INTO `company_staff` VALUES ('6', 'Lee', '24', '男', '12400.000', '2013-06-30', '2');
INSERT INTO `company_staff` VALUES ('7', 'lvan', '26', '女', '12500.000', '2014-03-27', '2');
INSERT INTO `company_staff` VALUES ('8', 'Jack', '29', '女', '22300.000', '2003-07-21', '2');

-- [法律部] --
INSERT INTO `company_staff` VALUES ('9', '张飞', '41', '女', '24000.000', '2003-05-21', '3');
INSERT INTO `company_staff` VALUES ('10', '李逵', '42', '女', '34000.000', '2005-04-15', '3');
INSERT INTO `company_staff` VALUES ('10', '罗翔', '45', '男', '54000.000', '2005-03-15', '3');

-- [销售部] --
INSERT INTO `company_staff` VALUES ('11', '孙权', '37', '女', '65000.000', '2000-06-21', '4');


-- [创建部门表]  --

CREATE TABLE `company_department` (
    id int not null auto_increment PRIMARY KEY,
    name VARCHAR(125) not null COMMENT '部门名称'
)ENGINE=INNODB DEFAULT charset utf8;


-- [添加部门数据]  --

INSERT INTO `company_department` VALUES ('1', '软件开发部');
INSERT INTO `company_department` VALUES ('2', '国际事务');
INSERT INTO `company_department` VALUES ('3', '法律部');
INSERT INTO `company_department` VALUES ('4', '销售部');

多表查询语法

select  字段1,字段2... from 表1,表2... [where 条件]

PS: 如果不加条件直接进行查询的话, 则会出现笛卡尔乘积这种现象

#查询职员和部门所有信息
select * from company_staff, company_department　

笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积

笛卡尔乘积示例

#查询职员和部门所有信息
select * from company_staff, company_department where company_department.id = company_staff.dept_id;
#注意: 多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用

查询结果

 

多表联合查询方式

#多表连接查询语法(重点)
SELECT 字段列表
    FROM 表1  INNER|LEFT|RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

内连接查询方式 (只显示符合条件的数据)

#查询公司职员和部门所有信息
select * from company_staff inner join company_department  on company_staff.dept_id = company_department.id;

查询公司职员和部门所有信息

 

Mysql多表查询

 

左外连接查询 (左边表中的数据优先全部显示)

#查询职员员和部门所有信息
select * from company_staff left join  company_department  on  company_staff.dept_id = company_department.id;

左连接查询示例

左连接查询图

 

右外连接查询 (右边表中的数据优先全部显示)

#查询职员和部门所有信息
select * from company_staff right join  company_department  on  company_staff.dept_id = company_department.id;

右链接插叙示例

 

有链接查询图

 

全连接查询(显示左右表中全部数据)

全连接查询：是在内连接的基础上增加 左右两边没有显示的数据
注意: mysql并不支持全连接 full JOIN 关键字
注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能

#查询职员和部门的所有数据
SELECT * FROM company_staff LEFT JOIN company_department ON company_staff.dept_id = company_department.id
UNION
SELECT * FROM company_staff RIGHT JOIN company_department ON company_staff.dept_id = company_department.id;

全连接查询示例

 

全连接查询图

 

以上就是针对MySQL多表查询全面解析实例【汇总】。转自：必学智库