python学习笔记 day43 多表联合查询+连接查询

首先创建两张表，person表 和dept表（两者关联的字段是dept_id 就是部门号）

第一张表 person 人员信息表：

create table person(
  id int not null auto_increment primary key,
  name varchar(50) not null,
  age int not null,
  sex char(2) not null,
  salary int not null,
  hire_date datetime default null,    # insert 数据时 可以手动输入员工入职时间
  create_time timestamp not null default current_timestamp,  # 可以记录创建数据的时间（insert数据时可以不用自己添加，会自动生成）   
  update_time timestamp not null default current_timestamp on update current_timestamp,  # 每次修改数据项，会自动记录 update时间
  dept_id int null)

insert into person(id,name,age,sex,salary,hire_date,dept_id) values(1,"璇璇",22,"女",20000,"2017-09-01",1);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(2,"西西",22,"女",16000,"2017-09-01",1);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(3,"楠楠",24,"男",18000,"2017-09-01",1);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(4,"东东",24,"男",22000,"2017-09-01",2);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(5,"哈哈",23,"男",21000,"2017-09-01",2);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(6,"呵呵",24,"女",16000,"2017-09-01",3);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(7,"梅梅",26,"女",15000,"2016-09-01",3);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(8,"浩浩",24,"男",18000,"2018-09-01",4);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(9,"夏夏",27,"女",19000,"2015-09-01",4);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(10,"星星",29,"女",12000,"2016-09-01",5);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(11,"alex",26,"男",30000,"2011-09-01",5);
insert into person(id,name,age,sex,salary,hire_date,dept_id) values(12,"Eva-J",29,"女",29000,"2012-09-01",6);
select * from person;

 

运行结果：

 

 第二张表: dept----记录部门id以及对应的部门名称

create table dept(
  did int not null,
  name varchar(50) not null,
  create_time timestamp not null default current_timestamp,
  update_time timestamp not null default current_timestamp on update current_timestamp 
  )
insert into dept(did,name) values(1,"研发部");
insert into dept(did,name) values(2,"事业部");
insert into dept(did,name) values(3,"销售部");
insert into dept(did,name) values(4,"运营部");
insert into dept(did,name) values(5,"美工部");
insert into dept(did,name) values(6,"产品部");
select * from dept;

运行结果：

哦对了，为了后续讲多表联合查询，连接查询的区别，这里我再往person表 和 dept表各自增加一条信息：

insert into person(id,name,age,sex,salary,hire_date) values(13,"萌萌",25,"女",18000,"2015-02-01");  # 新增加的员工没有部门
insert into dept(did,name) values(7,"国际部")  # dept表有个部门7 person表没有员工在这个部门

运行结果：

 

1. 多表联合查询-----两张表的交集

如果是想查询person表的全部人员信息，而且需要显示各自的部门：需要用到联合查询

 

select * from person,dept where person.dept_id=dept.did;   # 可以查找person表中所有员工信息，并且显示每一位员工的部门名称（不单单是部门id）

运行结果：

 

 

 

其实会发现只显示了12条数据（也就是person表中第十三条数据（没有dept_id的）并没有被显示）因为where条件是按照两张表的dept_id 和did对应相等来联合查询的；

注意：

多表联合查询时一定要找到两个表中相互关联的字段，并且作为条件使用（不加条件显示笛卡尔乘积的条数--有重叠）

 

2.连接查询---左连接，右连接，内连接，全连接

2.1 左连接

如果是想显示person表的全部员工信息，有dept_id的就显示部门名称，没有的也显示该条员工信息，只不过部门名称null即可 这时候就需要用到左连接 :

 

select * from person left join dept on person.dept_id = dept.did;  # 左连接，会显示person表的全部信息，有对应的dept_id就显示名称，没有也显示该条信息，只不过部门名称null即可

运行结果：

 

2.2 右连接

 如果是想显示全部部门信息，即使有的部门没有对应的员工在，也进行显示，就需要用到右连接（其实左连接也可以实现，就是把两个表顺序换了）

select * from person right join dept on person.dept_id= dept.did;

运行结果：

 

 2.3 内链接----跟联合查询的效果是一样的，只会显示两张表重叠的信息（交集）

select * from person inner join dept on person.dept_id =dept.did;  #内连接，显示两张表的重叠部门信息

运行结果：

 

 2.4 全连接 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;  # 全连接，使用UNION关键字，显示两张表的全部信息；

 

 运行结果：

多表联合查询与左右连接的区别：

前者只会找两表关联字段中一一对应的重叠（交集）部门数据，而左右连接查询是不管匹配与否，，只会显示左边表全部信息，能匹配就匹配，匹配不了就不显示（null）

 多表联合查询把符合条件的拿出来，不符合的扔掉，而左右连接查询，一张表作为基准，另一张表去匹配，匹配上的拿过来，匹配不上的用null；

全连接查询是在内连接基础上加左右两边没显示的数据；

注意mysql并不支持full join 关键字，但是mysql提供了union关键字，使用union可以间接实现full join的功能；

 

 

3. 作业

3.1 查询出产品部年龄大于22岁工资小于29000的员工，并且按照薪资倒序排列；

select * from person where age>20 and salary<30000 and dept_id = (select did from dept where name="产品部") order by salary desc;

运行结果：

3.2 查询每个部门中最高工资与最低工资是多少，并且显示部门名称；

其实一看这个题就知道需要用到分组查询（每个部门的，group by dept_id。。。）连接查询（因为显示person表可以看到工资，但是只有部门ID 没有部门名称，而dept表只有部门名称 没有人员，工资等）又需要显示前一张表的所有人员信息，所以是左连接查询（person在前）

 

select max(salary),min(salary),dept.name from person left join dept on person.dept_id =dept.did group by dept_id ;  # 连接查询一定要加两张表的关联条件（关键字on）！！group by需要放在后面 limit 倒数第一，froup by倒数第二，另外当两个表字段一样时，需要加上表名.字段名 这样来区分~

 

运行结果：（person表中分组后每一个小组信息都要显示，尽管第一行 数据没有dept_id 也要显示出来，左连接~）