5. SQL回顾

5.1 Join

在这里插入图片描述

5.2 SQL回顾

#准备工作
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;
 
CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 empno int  not null,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
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('少林','少林寺');
 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
 
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);
 

ALTER TABLE `t_dept` 
add  CEO  INT(11)  ;
 
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;
# 查询哪个门派没有人
select d.* from t_dept d
LEFT JOIN t_emp e on  d.id=e.deptId
WHERE e.id is null;

# 查询各门派的人数
select d.deptName,count(*) from t_emp e
inner JOIN t_dept d on  d.id=e.deptId
GROUP BY e.deptId;

# 列出所有人员和门派的对照关系,如果确定上下的结果不会有重复的时候,最好使用union all
select e.*,d.* from t_emp e left join t_dept d on  d.id=e.deptId
union all
select e.*,d.* from t_dept d left join t_emp e on  d.id=e.deptId;

# 查询没入门派的人员以及哪个门派没有人
select d.*,e.* from t_dept d
LEFT JOIN t_emp e on  d.id=e.deptId
WHERE e.id is null
union all
select d.*,e.* from t_emp e
LEFT JOIN t_dept d on e.deptId=d.id
where e.deptId is null;

# 求各个门派对应的掌门人名称
SELECT d.deptName,e.name from t_dept d
inner JOIN t_emp e on d.CEO = e.`id`;

# 求当上掌门人的平均年龄
SELECT AVG(e.age) from t_dept d
inner JOIN t_emp e on d.CEO = e.`id`;

# 求所有人物对应的掌门名称
# 1. 直接连两个表查询
select e.`name`,d.CEO,e1.name from t_emp e
inner join t_dept d on e.deptId = d.id
inner join t_emp e1 on d.CEO=e1.id;

# 2. select后面的子查询,先查出所有人,再查出所有有门派的人,最后查出ceo对应的名字
select e.`name`,d.CEO,(select e1.`name` from t_emp e1 where e1.id=d.CEO) from t_emp e
inner join t_dept d on e.deptId = d.id;

# 3. from后面的子查询,先查出所有有门派的人,再关联查出这些人的名字
select e.name,ed.CEO,ed.`name` from 
(select e.name,d.CEO from t_emp e inner join t_dept d on e.deptId=d.id) ed
inner join t_emp e on ed.CEO=e.id;

# 4. join后面的子查询,先查出所有人,再查出所有门派的掌门人名称,然后再关联查出结果
select e.name,ed.CEO,ed.`name` from t_emp e
inner join(select d.id,e.name,d.CEO from t_dept d inner join t_emp e on d.CEO=e.id) ed
on e.deptId=ed.id;
# 1.列出自己的掌门比自己年龄小的人
select e.`name`,e.age from t_emp e 
inner join (
	# 查出各门派掌门的年龄
	select d.deptName,e.`name`,e.age,d.id from t_emp e 
	inner join t_dept d on e.id=d.CEO
) ed on e.deptId = ed.id
where e.age > ed.age;


# 2.列出所有年龄低于自己门派平均年龄的人
select ed.deptName,e.`name`,age from (
	# 各门派的平均年龄
	select d.id,d.deptName,avg(age) avgAge from t_emp e
	inner join t_dept d on d.id = e.deptId
	group by e.deptId
) ed
inner join t_emp e on ed.id = e.deptId
where e.age < ed.avgAge;

# 3.列出至少有2个年龄大于40岁的成员的门派
select count(*) num,deptName from t_dept d
inner join t_emp e on e.deptId = d.id
where age>40
group by deptId
having num > 1;

# 4.至少有2位非掌门人成员的门派
select count(*) num,deptName from t_dept d
inner join t_emp e on e.deptId = d.id
where e.id not in (
	select CEO from t_dept d
 inner join t_emp e on e.id = d.CEO
)
group by deptName
having num > 1;


# 5.列出全部人员,并增加一列备注“是否为掌门”,如果是掌门人则显示是,不是掌门人则显示不是
select e.name,if(e.id=d.ceo,'是','不是') 是否为掌门 from t_emp e
inner join t_dept d on e.deptId = d.id;

# 6.列出全部门派,并增加一列“老鸟or菜鸟”,若门派的平均年龄>50则显示“老鸟”,否则显示“菜鸟”
select deptName, if(avg(age)>50,'老鸟','菜鸟') 老鸟or菜鸟 from t_emp e
inner join t_dept d on e.deptId = d.id
group by deptName;

# 7.显示每个门派年龄最大的人
select ed.deptName,e1.name,e1.age from (
	select d.id,d.deptName,max(age) mx from t_emp e
	inner join t_dept d on d.id = e.deptId
	group by deptId	
) ed
inner join t_emp e1 on e1.deptId = ed.id
where e1.age=ed.mx;


# 8.显示每个门派年龄第二大的人



©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页