MySQL每日一练——MySQL多表查询进阶挑战

目录

1、首先创建表

t_dept:

t_emp:

2、插入数据

t_dept表：

t_tmp表:

3、修改表

4、按条件查找

---

1、首先创建表

t_dept:

CREATE TABLE t_dept (
 id INT(11) NOT NULL AUTO_INCREMENT,
 deptName VARCHAR(30) DEFAULT NULL,
 address VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 

t_emp:

CREATE TABLE t_emp (
 id INT(11) NOT NULL AUTO_INCREMENT,
 name VARCHAR(20) DEFAULT NULL,
  age INT(3) DEFAULT NULL,
 dept_id INT(11) DEFAULT NULL,
empno int  not null,
 PRIMARY KEY (id),
 KEY idx_dept_id (dept_id)
 #CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES t_dept (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、插入数据

t_dept表：

INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');

t_tmp表:

INSERT INTO t_emp(NAME,age,dept_id,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,dept_id,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,dept_id,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,dept_id,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,dept_id,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,dept_id,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,dept_id,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,dept_id,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,dept_id,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,dept_id,empno) VALUES('韦小宝',18,null,100010);

3、修改表

ALTER TABLE t_dept
add  CEO  INT(11)  ;
 # CEO=2 值，都应该是t_emp 中id的值。
update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;

4、按条件查找

1 所有有门派的人员信息 

select * from t_emp where dept_id  is not  null;

2 列出所有用户，并显示其机构信息 

select t_emp.*,t_dept.* 
from t_emp 
join t_dept 
on t_dept.id=t_emp.id;

3 列出不入派的人员

select * from t_emp where dept_id is null ;

4 所有没人入的门派

select * 
from t_dept  
left join t_emp 
on t_emp.dept_id=t_dept.id where dept_id is null;

5 列出所有人员和门派的对照关系

select * from t_emp  
left join t_dept 
on t_emp.dept_id=t_dept.id;

6 列出所有没入派的人员和没人入的门派

(select name as 没有部门或者没有人员,deptname 
from t_emp 
left join t_deptept 
on t_emp.dept_id=t_dept.id where dept_id is null) 
union 
(select t_dept.adddress,t_emp.empno 
from  t_emp 
right join t_dept 
on t_emp.dept_id=t_dept.id wheere dept_id is null);

7 求各个门派对应的掌门人名称:

方法1：
select name as 掌门人,deptname 
from t_emp 
right join t_dept 
on t_emp.dept_id=t_dept.id where t_emp.id in(t_dept.CEO);
方法2：
 select name as 掌门人,deptname
from t_emp inner 
join t_dept
on t_emp.dept_id=t_dept.id where t_emp.id in(select  CEO from t_dept);

8 求所有当上掌门人的平均年龄:

SELECT d.deptName, e.name AS ceo_name, avg_age.average_ceo_age 
FROM t_dept d 
JOIN t_emp e  #与t_emp的连接
ON d.CEO = e.id #查找出所有的CEO信息
JOIN (
SELECT AVG(age) AS average_ceo_age     
FROM t_emp    
WHERE id IN (SELECT CEO FROM t_dept) 
) avg_age;#和一个子查询进行连接，连接结果是所有掌门人的平均年龄

9 求所有人物对应的掌门名称:

select e1.id,e1.name,e1.age,e1.dept_id,e1.deptname,e1.address,e1.CEO,e2.掌门  
from (select em.id,em.name,em.age,em.dept_id,de.deptname,de.address,de.CEO  
from t_emp as em  
inner join t_dept asde  
on em.dept_id=de.id) as e1  
inner join(select name as 掌门,id  
from t_emp) as e2  
on e1.dept_id=e2.id;

10 列出自己的掌门比自己年龄小的人员

 第一步：拿出所有掌门的信息：

select em.id,em.name,em.age,em.dept_id,de.CEO 
from t_emp as em  
inner join t_dept as de  
on em.id=de.CEO;

第二步：将所有人员的年龄与掌门的年龄进行比较并且按照在部门内部进行比较：

select * 
from t_emp as e left 
join(select em.id,em.name,em.age,em.dept_id,de.CEO 
from t_emp as em  
inner join t_dept as de  
on em.id=de.CEO) as d 
on e.dept_id=d.dept_id 
where e.age>d.age;

11 列出所有年龄低于自己门派平均年龄的人员

select e.id,e.name,e.age,e.dept_id,e.empno,d.age1  
from t_emp as e  
left join (select dept_id,avg(agee) as age1 
from t_emp group by dept_id ) as d  
on e.dept_id=d.dept_id 
where e.age<d.age1;

12 列出至少有2个年龄大于40岁的成员的门派

select e1.deptname,count(e1.age) 
from (select em.id,name,age,dept_id,deptname 
from t_emp as em  
join t_dept as de 
on de.id=em.dept_id where age>=40) as e1  
group by e1.dept_id 
having count(e1.age)>=2 ;

13 至少有2位非掌门人成员的门派

select deptname,count(t_emp.name) 
from t_emp  
right join t_dept  
on t_emp.dept_id=t_dept.id 
where t_emp.id not in(t_dept.CEO) 
group by deptname  
having count(t_emp.name)>=2;

14 列出全部人员，并增加一列备注“是否为掌门”，如果是掌门人显示是，不是掌门人显示否*

SELECT e.name, e.age, d.deptName,         
CASE WHEN e.empno = (SELECT empno FROM t_emp WHERE dept_id = d.id ORDER BY age DESC LIMIT 1) THEN '是' ELSE '否' END AS 是否为掌门 
FROM t_emp e 
JOIN t_deppt d 
ON e.dept_id = d.id;

 
15 列出全部门派，并增加一列备注“老鸟or菜鸟”，若门派的平均值年龄>50显示“老鸟”，否则显示“菜鸟”*

SELECT d.deptName,        
CASE WHEN AVG(e.age) > 50 THEN '老鸟' ELSE '菜鸟' END AS 备注 
FROM t_dept d 
JOIN t_emp e 
ON d.id = e.dept_id GROUP BY d.deptName;

16 显示每个门派年龄最大的人

select dept_id,max(age) 
from t_emp group by dept_id  
having dept_id is not null;