5. SQL回顧

UnityAlvin發表於2020-10-24
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.顯示每個門派年齡第二大的人

相關文章