补12.关于mysql的多表查询
首先先准备两张表。
首先是员工信息表，表名为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 |
+--------+----------+------+---------+
使用比较运算符：
=、!=、>、>=、<、<=、<>
查询包含年龄大于等于25岁的员工，都在哪些部门：
select dept_id,dept_name from department where dept_id in ( select distinct dept_id from employee where age >= 25);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 201 | 技术部 |
+---------+-----------+
exists关键字：
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。Ture或False，当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。
例：
select * from employeeWHERE EXISTS(SELECT dept_name from department where dept_id=203);
#department表中存在dept_id=203，Ture，返回了True后select * from employeeWHERE EXISTS这条语句就可以执行了，主要就是做一个条件判断。
select * from employee WHERE EXISTS（SELECT dept_name from department where dept_id=205)