补12.关于mysql的多表查询

8870188

　　首先先准备两张表。
　　首先是员工信息表，表名为employee。
　　create table employee( emp_id int primary key auto_increment not null, emp_name varchar(50), age int, dept_id int );
　　然后在员工信息表中插入6条记录：
　　insert into employee(emp_name,age,dept_id)values ('A',19,200), ('B',26,201), ('C',30,201), ('D',24,202), ('E',20,200), ('F',38,204);
　　#创建了6个员工。
　　然后是部门信息表。
　　create table department( dept_id int, dept_name varchar(100) );
　　#部门信息表中包含了部门ID以及部门名称。
　　insert into department values(200,'人事部'),(201,'技术部'),(202,'销售部'),(203,'财政部');
　　#在部门表中添加了4个部门。
　　employee表：
　　+--------+----------+------+---------+
　　| emp_id | emp_name | age  | dept_id |
　　+--------+----------+------+---------+
　　|      1 | A        |   19 |     200 |
　　|      2 | B        |   26 |     201 |
　　|      3 | C        |   30 |     201 |
　　|      4 | D        |   24 |     202 |
　　|      5 | E        |   20 |     200 |
　　|      6 | F        |   38 |     204 |
　　+--------+----------+------+---------+
　　department表：
　　+---------+-----------+
　　| dept_id | dept_name |
　　+---------+-----------+
　　|     200 | 人事部    |
　　|     201 | 技术部    |
　　|     202 | 销售部    |
　　|     203 | 财政部    |
　　+---------+-----------+
　　一、笛卡尔积查询：
　　select * from employee,department;
　　结果：
　　+--------+----------+------+---------+---------+-----------+
　　| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
　　+--------+----------+------+---------+---------+-----------+
　　|      1 | A        |   19 |     200 |     200 | 人事部    |
　　|      1 | A        |   19 |     200 |     201 | 技术部    |
　　|      1 | A        |   19 |     200 |     202 | 销售部    |
　　|      1 | A        |   19 |     200 |     203 | 财政部    |
　　|      2 | B        |   26 |     201 |     200 | 人事部    |
　　|      2 | B        |   26 |     201 |     201 | 技术部    |
　　|      2 | B        |   26 |     201 |     202 | 销售部    |
　　|      2 | B        |   26 |     201 |     203 | 财政部    |
　　|      3 | C        |   30 |     201 |     200 | 人事部    |
　　|      3 | C        |   30 |     201 |     201 | 技术部    |
　　|      3 | C        |   30 |     201 |     202 | 销售部    |
　　|      3 | C        |   30 |     201 |     203 | 财政部    |
　　|      4 | D        |   24 |     202 |     200 | 人事部    |
　　|      4 | D        |   24 |     202 |     201 | 技术部    |
　　|      4 | D        |   24 |     202 |     202 | 销售部    |
　　|      4 | D        |   24 |     202 |     203 | 财政部    |
　　|      5 | E        |   20 |     200 |     200 | 人事部    |
　　|      5 | E        |   20 |     200 |     201 | 技术部    |
　　|      5 | E        |   20 |     200 |     202 | 销售部    |
　　|      5 | E        |   20 |     200 |     203 | 财政部    |
　　|      6 | F        |   38 |     204 |     200 | 人事部    |
　　|      6 | F        |   38 |     204 |     201 | 技术部    |
　　|      6 | F        |   38 |     204 |     202 | 销售部    |
　　|      6 | F        |   38 |     204 |     203 | 财政部    |
　　+--------+----------+------+---------+---------+-----------+
　　显然这种结果没有任何的意义。
　　二、内连接查询。
　　查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
　　下面是一个内连接查询的例子：
　　还拿前面的两张表举例，现在需要列出所有员工的姓名，以及职位名称。
　　select emp_name,dept_name from employee,department where employee.dept_id = department.dept_id;
　　+----------+-----------+
　　| emp_name | dept_name |
　　+----------+-----------+
　　| A        | 人事部    |
　　| B        | 技术部    |
　　| C        | 技术部    |
　　| D        | 销售部    |
　　| E        | 人事部    |
　　+----------+-----------+
　　三、外连接查询。

• 　　左外链接：在内连接的基础上增加左边有右边没有的结果。
  　　select * from employee left join department on employee.dept_id = department.dept_id;
  　　#就是以左边的表为基准
  

　　+--------+----------+------+---------+---------+-----------+
　　| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
　　+--------+----------+------+---------+---------+-----------+
　　|      1 | A        |   19 |     200 |     200 | 人事部    |
　　|      5 | E        |   20 |     200 |     200 | 人事部    |
　　|      2 | B        |   26 |     201 |     201 | 技术部    |
　　|      3 | C        |   30 |     201 |     201 | 技术部    |
　　|      4 | D        |   24 |     202 |     202 | 销售部    |
　　|      6 | F        |   38 |     204 |    NULL | NULL      |
　　+--------+----------+------+---------+---------+-----------+
　　2.右外链接：在内连接的基础上增加右边有左边没有的结果。
　　select * from employee right join department on employee.dept_id = department.dept_id;
　　+--------+----------+------+---------+---------+-----------+
　　| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
　　+--------+----------+------+---------+---------+-----------+
　　|      1 | A        |   19 |     200 |     200 | 人事部    |
　　|      2 | B        |   26 |     201 |     201 | 技术部    |
　　|      3 | C        |   30 |     201 |     201 | 技术部    |
　　|      4 | D        |   24 |     202 |     202 | 销售部    |
　　|      5 | E        |   20 |     200 |     200 | 人事部    |
　　|   NULL | NULL     | NULL |    NULL |     203 | 财政部    |
　　+--------+----------+------+---------+---------+-----------+
　　3.全外链接：
　　全外连接是将左外链接和右外链接做一个结合。
　　select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id UNION select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;
　　使用union关键字将左链接和有链接的结果做个一个拼接。
　　union ：去除所有重复的结果。
　　union all : 没有去重功能。
　　4.多表复合条件查询。
　　比如说现在想要查询，公司年龄大于25岁的员工都在哪个部门中。
　　select distinct department.dept_name from employee,department where employee.dept_id = department.dept_id and age > 25;
　　+-----------+
　　| dept_name |
　　+-----------+
　　| 技术部    |
　　+-----------+
　　以公司所有员工的年龄以从小到大的方式排序。
　　select employee.emp_id,employee.emp_name,employee.age,department.dept_name from employee,department where employee.dept_id = department.dept_id order by age asc;
　　5.多表子查询。
　　子查询，就是将一个查询的sql语句，嵌套在另一个查询的sql语句中。
　　内层查询语句的查询结果，可以为外层查询语句提供查询条件。
　　子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字。
　　IN关键字：这个关键字大概是包含的意思。
　　比如说，查询employee表，但dept_id必须在department表中出现过。
　　包含在department表中dept_id字段中，才会显示。
　　select * from employee where dept_id in (select dept_id from department);
　　+--------+----------+------+---------+
　　| emp_id | emp_name | age  | dept_id |
　　+--------+----------+------+---------+
　　|      1 | A        |   19 |     200 |
　　|      2 | B        |   26 |     201 |
　　|      3 | C        |   30 |     201 |
　　|      4 | D        |   24 |     202 |
　　|      5 | E        |   20 |     200 |
　　+--------+----------+------+---------+
　　使用比较运算符：
　　=、!=、>、>=、