mysql之多表查询
1.新建的一个数据库db3
create database db3 charset utf8;
2.为db3数据库创建两张表和数据
（1）创建person表
create table peron ( id int(11) not null auto_increment, name varchar(50) not null, age tinyint(4) default \'0\', sex enum(\'男\',\'女\',\'人妖\') not null default \'人妖\', salary decimal(10, 2) not null DEFAULT \'250.00\', hire_date date not null, dept_id int(11) default null, PRIMARY KEY (id) ) ENGINE=INNODB auto_increment = 13 DEFAULT CHARSET = utf8;
（2）为person表创建数据
# 教学部 insert into peron values(\'1\', \'alex\', \'28\', \'人妖\', \'53000.00\', \'2010-06-21\', \'1\'); insert into peron values(\'2\', \'wupeiqi\', \'23\', \'男\', \'8000.00\', \'2011-02-21\', \'1\'); insert into peron values(\'3\', \'egon\', \'30\', \'男\', \'6500.00\', \'2015-06-21\', \'1\'); insert into peron values(\'4\', \'jingnvshen\', \'28\', \'女\', \'6680.00\', \'2014-06-21\', \'1\'); # 销售部 insert into peron values(\'5\', \'歪歪\', \'20\', \'女\', \'3000.00\', \'2015-02-21\', \'2\'); insert into peron values(\'6\', \'星星\', \'20\', \'女\', \'2000.00\', \'2018-01-30\', \'2\'); insert into peron values(\'7\', \'格格\', \'20\', \'女\', \'2000.00\', \'2018-02-27\', \'2\'); insert into peron values(\'8\', \'周周\', \'20\', \'女\', \'2000.00\', \'2015-06-21\', \'2\'); #市场部 insert into peron values(\'9\', \'月月\', \'21\', \'女\', \'4000.00\', \'2014-07-21\', \'3\'); insert into peron values(\'10\', \'安琪\', \'22\', \'女\', \'4000.00\', \'2015-07-15\', \'3\'); # 人事部 insert into peron values(\'11\', \'周明月\', \'17\', \'女\', \'5000.00\', \'2014-06-21\', \'4\'); # 鼓励部 insert into peron values(\'12\', \'苍老师\', \'33\', \'女\', \'1000000.00\', \'2018-02-21\', null);
（3）创建一个部门表dept。 COMMENT 表示为字段添加注释
create table dept( did int not null COMMENT \'部门id记录\' auto_increment PRIMARY KEY, dname VARCHAR(50) not null COMMENT \'部门名称\' ) ENGINE=INNODB DEFAULT CHARSET utf8;
（4）为dept部门表，创建部门数据
# 添加部门数据 INSERT INTO dept VALUES(\'1\', \'教学部\'); INSERT INTO dept VALUES(\'2\', \'销售部\'); INSERT INTO dept VALUES(\'3\', \'市场部\'); INSERT INTO dept VALUES(\'4\', \'人事部\'); INSERT INTO dept VALUES(\'5\', \'鼓励部\');
（5）此时部门表与人员表为
3.多表联合查询
3.1 笛卡尔乘积（多个表的结果集相乘）。查询人员表和dept部门表，结果却查询出来了60条，重复的记录出现，这个笛卡尔乘积现象。两个表数据的结果集相乘。这是在多表联合查询时不加条件调导致的
select * from person, dept; -- 查询人员表和dept表
3.2 多表联合查询使用where条件限定。将person表的部门id和dept表的id记录相等的记录查询出来。注意：多表查询时，一定要找到多个表的关联字段，并且将这个关联字段作为条件使用
select * from person as p, dept as d where p.dept_id = d.did; -- person表和dept表进行多表联合查询，条件是人员表的部门dept_id必须和部门表的did相同。
4.多表连接查询。
4.1左连接查询（左侧表为基准表，基准表去匹配右侧的表，不管匹配没匹配上，基准表的数据都会显示出来）
这种方式查询会将person表中的dept_id和dept表中的did字段相等的记录都查询出来，同时还会将person表中没匹配上dept表中的记录查询出来。如人员表中小溢这个记录没有dept_id部门字段值，但是有了左连接所以会将person人员表中dept_id字段没匹配上dept部门表中的did字段的记录也查询出来，左连接，左侧表中没匹配上的记录也会查询出来。
select * from person LEFT JOIN dept ON person.dept_id = dept.did; -- left join关键字，连接关键字左连接
4.2右连接查询（右侧表为基准表，基准表去匹配左侧的表，不管匹配没匹配上，基准表的数据都会显示出来）
这种方式查询会将person表中的dept_id和dept表中的did字段相等的记录都查询出来，同时还会将dept表中没匹配上person表中的记录查询出来。如dept部门表did中开车部没有被person人员表中的dept_id部门id匹配上，但是有了右连接所以会将dept部门表中did字段没匹配上的记录也查询出来，没配上就会显示空值，右连接，右侧表中没匹配上的记录也会查询出来。
select * from person RIGHT JOIN dept ON person.dept_id = dept.did; -- right join关键字，左连接关键字
4.3内连接查询（与多表联合查询的效果一样），两表匹配上的才会被查询出来
select * from person INNER JOIN dept ON person.dept_id = dept.did; -- inner为内连接关键字
4.4全连接查询（显示左右表的全部数据）使用union 可以将左右表的数据去重查询出来，union all 不会讲左右表的数据去重
（1）union，全连接查询关键字，并将左右两表数据去重
select * from person LEFT JOIN dept ON person.dept_id = dept.did UNION select * from person RIGHT JOIN dept ON person.dept_id = dept.did;
（2）union all，全连接查询关键字，不会将左右两表的数据去重
-- union all 关键不会将两表重复的数据去重 select * from person LEFT JOIN dept ON person.dept_id = dept.did UNION all select * from person RIGHT JOIN dept ON person.dept_id = dept.did;
5.复杂条件多表查询
5.1要求查询出教学部年龄大于20岁，并且工资小于40000的员工，按工资时间倒序排序
（1）使用子语句的方法查询
select * from person where age > 20 and dept_id = (select did from dept where dname = \'教学部\') and salary < 40000 ORDER BY salary desc;
（2）使用多表联合查询的方法查询
select * from person as p, dept as d where p.dept_id = d.did and d.dname= \'教学部\' and p.age > 20 and p.salary < 40000 ORDER BY p.salary desc;
5.2查询每个部门中最高工资和最低工资是多少，显示部门名称
（1）使用多表连接查询的方法
SELECT MAX(salary), MIN(salary),dname from person LEFT JOIN dept ON person.dept_id = dept.did GROUP BY dept_id;
（2）使用多表联合查询的方法
select MAX(salary), MIN(salary),dname from person as p, dept as d where p.dept_id = d.did GROUP BY p.dept_id;