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

叁歲伎倆 2023-02-23 07:50 15阅读 0赞

经常遇到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表中数据条数 = 笛卡尔乘积

![笛卡尔乘积示例][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70] 笛卡尔乘积示例

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

![查询结果][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 1] 查询结果

# 多表联合查询方式 #

#多表连接查询语法(重点)
    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;

![查询公司职员和部门所有信息][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 2] 查询公司职员和部门所有信息

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 3][] Mysql多表查询

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

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

![左连接查询示例][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 4] 左连接查询示例

![左连接查询图][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 5] 左连接查询图

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

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

![右链接插叙示例][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 6] 右链接插叙示例

![有链接查询图][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 7] 有链接查询图

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

全连接查询：是在内连接的基础上增加 左右两边没有显示的数据  
注意: 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;

![全连接查询示例][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 8] 全连接查询示例

![全连接查询图][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 9] 全连接查询图

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200705230324661.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200705230407916.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200705230504229.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200705230539400.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/2020070523064343.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200705230726323.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200705230818114.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200705230925786.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200705231025245.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200705231112638.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l3ODg4NjQ4NA==,size_16,color_FFFFFF,t_70
[Link 1]: http://www.ibixue.com