MySQL 多表查询

例表：

CREATE DATABASE db1;
USE da1;

#创建部门表
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);

多表查询分为3种：内连接、外连接、子查询

1、内连接：隐式内连接、显式内连接

　　隐式内连接：

　　　　使用where条件消除无用数据

-- 查询所有员工信息和对应部分信息
SELECT
    emp.`id`,
    emp.name,
    gender,
    salary,
    join_date,
    dept.`name`
FROM
    emp,
    dept
WHERE
    emp.`dept_id`=dept.`id`;

　　　　结果：

　　　　

　　显式内连接：

　　　　使用[inner] join 消除无用数据

-- 查询所有员工信息和对应部分信息
SELECT 
    emp.`id`,
    emp.name,
    gender,
    salary,
    join_date,
    dept.`name`
FROM
    emp
JOIN
    dept
ON
    emp.`dept_id`=dept.`id`;

　　*隐式内连接和显式内连接只是写法不同，效果却是一样的

　　*内连接查询注意事项

　　　　1、从哪些表中查询数据

　　　　2、条件什么

　　　　3、查询哪些字段

 

2、外连接：左外连接、右外连接

　　左外连接：

　　　　select 字段列表 from 表1 left [outer] join 表2 on 条件

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

　　右外连接：

　　　　select 字段列表 from 表1 right [outer] join 表2 on 条件

　　　　右外连接查询的是右表所有数据以及其交集部分

 　　*左外连接和右外连接只是一个相对概念，可随意更换

 

3、子查询：查询中嵌套查询称嵌套查询为子查询

　　例子：

-- 查询工资最高的员工信息
SELECT MAX(salary) FROM emp; -- 结果为9000
SELECT *FROM emp WHERE salary = 9000; -- 查询对应员工信息

-- 使用子查询
SELECT 
    * 
FROM 
    emp 
WHERE 
    salary = (SELECT MAX(salary) FROM emp);

　　1、子查询的结果是单行单列的：

　　　　子查询作为条件，可以使用运算符(>、>=、<、<=、=)判断

　　2、子查询的结果是多行单列的：

　　　　子查询作为条件，可以使用运算符（in，or）判断

-- 查询财务部和市场部所有员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';-- 结果为3,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 = '市场部');

　　3、子查询的结果是多行多列的：

　　　　子查询可以作为一张虚拟表参与查询

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 子查询
SELECT 
    *
FROM
    (SELECT * FROM emp WHERE emp.`join_date`>'2011-11-11') t1,
    dept t2
WHERE
    t1.dept_id = t2.`id`;

-- 普通内连接
SELECT 
    * 
FROM 
    emp,
    dept
WHERE
    emp.`dept_id`=dept.`id`
    AND
    emp.`join_date`>'2011-11-11';