文章目录

(一)环境搭建(二)多表查询
(三)多表查询的分类
(四)内连接查询
(五)外连接查询
(六)子查询

(一)环境搭建

在讲解多表之前先新建如下表：

# 创建部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ("开发部"),("市场部"),("财务部");
# 创建员工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1), -- 性别
	salary DOUBLE, -- 工资
	join_date DATE, -- 入职日期
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("张三","男",7200,"2013-02-24",1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("张四","男",3600,"2010-12-02",2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("张五","男",9000,"2008-08-08",2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("张六","女",5000,"2015-10-07",3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES("张七","女",4500,"2011-03-14",1);

输入SELECT *FROM dept;之后效果如下：

部门组织 mysql设计 mysql创建部门表_sql


输入SELECT *FROM emp;之后效果如下：

部门组织 mysql设计 mysql创建部门表_sql_02


我们一张一张地查很麻烦，我们能不能一次性查多个表呢？

(二)多表查询

我们输入SELECT *FROM emp,dept;，效果如下：

部门组织 mysql设计 mysql创建部门表_部门组织 mysql设计_03


我们得到了一个笛卡尔积，显然不是所有数据都是我们想要的，我们要怎么才能消除无用的数据呢？这个问题待会儿说

(三)多表查询的分类

分为：内连接查询、外连接查询和子查询

(四)内连接查询

(一) 隐式内连接：
使用where条件消除无用的数据

SELECT *FROM emp,dept WHERE emp.`dept_id`=dept.`id`;

效果如下：

部门组织 mysql设计 mysql创建部门表_部门组织 mysql设计_04


可是上面有一些字段我不想要，如果我只想要员工表的名称、性别和部门表的名称呢？

SELECT emp.`name`,emp.`gender`,dept.`name` FROM emp,dept WHERE emp.`dept_id`=dept.`id`;

效果如下：

部门组织 mysql设计 mysql创建部门表_python_05


为了简化操作，我们经常会给每个表起别名

SELECT
	t1.`name`, -- 员工表的姓名
	t1.`gender`, -- 员工表的性别
	t2.`name`  -- 部门表的名称
FROM
	emp t1,   -- 给emp表起别名t1
	dept t2   -- 给dept表起别名t2
WHERE
	t1.`dept_id`=t2.`id`; -- 筛选掉不需要的数据

效果跟上面一样
(二) 显式内连接：

select 字段列表 from 表名1 [inner] join 表名2 on 条件

注意：以后加上[]这种括号的代码都是可选，即可写可不写

SELECT * FROM emp INNER JOIN dept ON emp.`dept_id`=dept.`id`;

效果如下：

部门组织 mysql设计 mysql创建部门表_python_06


当然还可以起别名，这里不一一演示了

内连接查询注意事项：

a:从哪些表中查询数据

b:条件是什么

c:查询哪些字段

(五)外连接查询

  • 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件；

此时如果刚入职一位员工，它还没有部门，如下：

部门组织 mysql设计 mysql创建部门表_sql_07


需求：查询所有员工信息，如果员工有部门，则查询部门的名称，如果没有部门，则不显示部门的名称

由于id为6的员工不满足t1.`dept_id`=t2.`id`;条件，所以用内连接查询是会被筛选掉的，我们要用外连接查询：

SELECT
	t1.*,t2.`name`
FROM
	emp t1
LEFT JOIN
	dept t2
ON
	t1.`dept_id`=t2.`id`;

效果如下：

部门组织 mysql设计 mysql创建部门表_python_08


总结：左外连接查询的是左表所有数据以及其交集部分

  • 右外连接
    右外连接语法只是把left改成right，不做阐述

(六)子查询

概念：查询中嵌套查询，称嵌套查询为子查询
在刚才的表里，有一个需求：
查询工资最高的员工信息
步骤：
a:查询最高的工资是多少

SELECT MAX(emp.`salary`) FROM emp;

b:查询员工信息，并且工资等于9000的员工信息

SELECT * FROM emp WHERE emp.`salary`= 9000;

效果如下：

部门组织 mysql设计 mysql创建部门表_sql_09


但是如果我不想分两步查询，我想一步到位呢？这就要用到子查询了

SELECT * FROM emp WHERE emp.`salary`= (SELECT MAX(emp.`salary`) FROM emp);

子查询有多种情况，下面我们分别去介绍

  • 子查询的结果是单行单列的
    (我们刚才所使用的子查询就符合这种情况)
    子查询可以作为条件，使用运算符去判断
    需求：查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.`salary` < (SELECT AVG(salary) FROM emp);

运算符：> >= < <= =

  • 子查询的结果是多行单列的
    先看看这个需求：查询’财务部’所有的员工信息
    第一步：查询’财务部’的id
    SELECT id FROM dept WHERE NAME = '财务部'; 得出财务部的id为3

    • 部门组织 mysql设计 mysql创建部门表_数据库_10

  • 第二步：查询对应这个id的所有员工的信息
    SELECT * FROM emp WHERE dept_id = 3;

    • 部门组织 mysql设计 mysql创建部门表_python_11

  • 现在需求增加了：查询’财务部’和’市场部’所有的员工信息
    SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2; 使用集合更加方便：
    SELECT * FROM emp WHERE dept_id IN (3,2); 同样这两步操作可以合并为一步：
SELECT * FROM emp WHERE dept_id IN 
(SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

总结：可以使用IN运算符

  • 子查询的结果是多行多列的
    需求：查询员工入职日期是2011-11-11日之后的员工信息和部门信息
    SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11'; 效果如下：

    • 部门组织 mysql设计 mysql创建部门表_数据库_12

  • 但是要输出部门信息还需要跟另一张表做关联：
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
WHERE t1.`id`= t2.dept_id;

部门组织 mysql设计 mysql创建部门表_数据库_13


总结：子查询可以作为一张虚拟表

其实用之前的内链接查询反而更方便：

SELECT * FROM emp t1,dept t2
WHERE t1.`dept_id` = t2.`id`
AND t1.`join_date` > '2011-11-11';