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;