MySQL多表查询

说明：MySQL的语句不区分大小写;本文为了区别语句和我们的变量名，本文SQL语句全部大写。

目录

语法:

准备 数据库文件

多表查询的分类：

一、内连接查询

1、隐式内连接：使用WHERE消除无用数据

2、显式内连接：

内连接注意点：

二、外连接查询

 1、左外连接

2、右外连接

三、子查询

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

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

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

---

语法:

        SELECT

                列名列表

        FROM

                表名列表

        WHERE.... 

准备 数据库文件

#创建部门表

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(唐僧',男'900,'2008-08-08',2);

INSERT INTO emp(NAMEgender,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);

例1：从两张表中查询数据

SELECT  *  FROM  dept,emp;

查询出来的结果称为结果集，多表查询的结果集又名：笛卡尔积，

笛卡尔积：由两个集合A、B组成的集合

笛卡尔积里面有很多重复的无效数据，我们要消除这些无效数据。

多表查询的分类：

一、内连接查询

1、隐式内连接：使用WHERE消除无用数据

例1：查询所有的员工信息和对应的部门信息

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

例2：查询员工表名称、性别和部门表的名称

SELECT  emp.NAME,emp.sex,dept.NAME FROM  emp,dept  WHERE  emp.dept_id = dept.id;

例3：给表名起别名，查询员工表名称、性别和部门表的名称。

SELECT  t1.NAME,t1.sex,t2.NAME 

FROM  emp  t1,dept  t2

WHERE  emp.dept_id = dept.id;

2、显式内连接：

语法：SELECT  字段列表  FROM  表1  [INNER]  JOIN  表2  ON  条件；

[中括号里面的内容可写 、也可不写]

例1：查询所有的员工信息和对应的部门信息

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

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

内连接注意点：

1、从哪些表中查询数据

2、条件是什么

3、查询哪些字段

二、外连接查询

 1、左外连接

语法：SELECT  字段列表  FROM  表1  LEFT  [OUTER]  JOIN  表2  ON  条件;

例1：查询所有的员工信息；如果员工有部门就显示部门名称，没有则不显示

SELECT  t1.*, t2.NAME  FROM  emp t1  INNER  JOIN  dept  t2  ON  t1.dept_id=t2.id;

例2：查询所有的员工信息；不论员工有没有部门，都显示部门名称

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

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

2、右外连接

语法：SELECT  字段列表  FROM  表1  RIGHT  [OUTER]  JOIN  表2  ON  条件;

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

例1：查询部门表所有的信息；以及与员工表交集部分的信息

SELECT  t1.*, t2.NAME  FROM  emp t1  RIGHT  JOIN  dept  t2  ON  t1.dept_id=t2.id;

三、子查询

概念：查询中嵌套查询，称嵌套查询为子查询

例1：查询工资最高的员工信息

其实可以分为两步去做，第一步查询最高的工资是多少？9000 

SELECT  MAX(salary)  FROM  emp;

第二步查询工资等于9000的员工信息

SELECT  *  FROM  emp  WHERE  emp.salary = 9000;

子查询一步到位：

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

子查询的不同情况：

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

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

例1：查询员工工资小于平均工资的人 

SELECT  *  FROM  emp  WHERE  emp.salary < (SELECT  AVG(salary)  FROM  emp);

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

子查询可以作为条件，使用运算符IN来判断 

例1：查询"财务部"所有的员工信息

SELECT  id  FROM  dept  WHERE  NAME='财务部' ;

SELECT  *  FROM  emp  WHERE  dept_id=3;

例2：查询"财务部"和“市场部”所有的员工信息

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=3 IN  (3,2);

子查询可以一行语句实现

SELECT  *  FROM  emp  WHERE  dept_id=3 IN  (SELECT  id  FROM  dept  WHERE  NAME='财务部'  OR  NAME= "市场部");

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

例1：查询入职日期是2018年8月5号之后的员工信息

SELECT  *  FROM  emp  WHERE  emp.join_date  > '2018-08-05';

  

例2：查询入职日期是2018年8月5号之后的员工信息和部门信息

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

SELECT  *  FROM  dept  t1,(SELECT  *  FROM  emp  WHERE  emp.join_date  > '2018-08-05')  t2  WHERE  t1.id=t2.dept_id;

普通查询：SELECT  *  FROM  emp  t1,dept  t2  WHERE  t1.id= t2.dept_id  AND  emp.join_date >  '2018-08-05';