MySQL

SQL语句之查询

2020年12月21日 2023年12月26日

1. join图

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(11) 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(id,deptName,address) VALUES(1,'华山','华山');
INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐帮','洛阳');
INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');
INSERT INTO t_dept(id,deptName,address) VALUES(4,'武当','武当山');
INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明顶');
INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);

3. 7种join

A、B两表共有(查询所有有部门的员工->员工和部门之间必须存在关联的数据)

SELECT * FROM t_emp a INNER JOIN t_dept b ON a.deptid = b.id;

A、B两表共有+A的独有（列出所有用户，并显示其机构信息）A的全集

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id;

A、B两表共有+B的独有（列出所有部门，并显示其部门的员工信息）B的全集

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id;

A的独有 (查询没有加入任何部门的员工)

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id WHERE b.id IS NULL;

B的独有 (查询没有任何员工的部门)

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id WHERE a.id IS NULL;

AB全有 (查询所有员工和所有部门)

# MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
# left join + union(去重)+ right join
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id 
UNION 
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id;

# UNION ALL 不会自动去重
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id 
UNION ALL
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id;

A的独有+B的独有 (查询没有加入任何部门的员工，以及查询出部门下没有任何员工的部门)

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id WHERE b.id IS NULL 
UNION 
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id WHERE a.id IS NULL;

4. 提高

增加掌门人字段

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 b.deptName, a.name from t_emp a RIGHT JOIN t_dept b on a.id = b.CEO;

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

SELECT AVG(a.age) FROM t_emp a INNER JOIN t_dept b ON a.id = b.ceo;

求所有人，对应的掌门是谁(课堂练习，4种写法分析)

#内连接查询
select c.`name`, a.`name` as CEOName
FROM t_emp a 
INNER JOIN t_dept b on a.id = b.CEO
INNER JOIN t_emp c on b.id = c.deptId;

#三表左连接方式
SELECT a.name,c.name AS CEOName 
FROM t_emp a 
LEFT JOIN t_dept b ON a.deptid = b.id 
LEFT JOIN t_emp c ON b.ceo = c.id;

5. 拓展

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

select a.`name`, a.age, c.`name` CEOName, c.age CEOAge
FROM t_emp a 
INNER JOIN t_dept b on a.deptId = b.id
INNER JOIN t_emp c on b.CEO = c.id
WHERE c.age < a.age;

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

SELECT c.`id`,c.`name`,c.`age`,aa.avgage 
FROM t_emp c 
INNER JOIN 
(SELECT a.`deptId`, AVG(a.`age`) avgage 
FROM t_emp a
WHERE a.`deptId` IS NOT NULL
GROUP BY a.`deptId`) aa
ON c.`deptId` = aa.deptId
WHERE c.`age` < aa.avgage;

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

SELECT b.deptName
FROM t_dept b
JOIN t_emp a ON b.id = a.deptId
WHERE a.age > 40
GROUP BY b.deptName
HAVING COUNT(a.id) >= 2;

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

SELECT b.deptName
FROM t_dept b
JOIN t_emp a ON b.id = a.deptId
WHERE a.empno IS NOT NULL
GROUP BY b.deptName
HAVING COUNT(a.id) >= 2;

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

SELECT a.`name`,CASE WHEN b.id IS NULL THEN'否' ELSE'是' END '是否为掌门'
FROM t_emp a 
LEFT JOIN t_dept b ON a.`id`=b.`CEO`;

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

SELECT b.`id`,b.`deptName` ,
IF(AVG(a.`age`)>50, '老鸟', '菜鸟') '老鸟or菜鸟'
FROM t_emp a 
INNER JOIN t_dept b ON a.`deptId`=b.`id`
GROUP BY b.`id`,b.`deptName`;

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

SELECT c.deptName, a.`name`, a.age 
FROM t_emp a INNER JOIN 
(SELECT a.`deptId`, MAX(a.`age`) maxage FROM t_emp a 
WHERE a.`deptId` IS NOT NULL 
GROUP BY a.`deptId`) b ON a.age = b.maxage
INNER JOIN t_dept c ON b.deptId = c.id;

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

SELECT d.deptName, MAX(e.age) AS Second_Max_Age
FROM t_dept d
JOIN t_emp e ON d.id = e.deptId
WHERE e.age < (SELECT MAX(age) FROM t_emp WHERE deptId = d.id)
GROUP BY d.deptName;

# chatgpt给出的答案
SELECT deptName, MAX(age) AS Second_Max_Age
FROM 
(SELECT d.deptName, e.age, ROW_NUMBER() OVER (PARTITION BY e.deptId ORDER BY e.age DESC) AS rnk
FROM t_dept d
JOIN t_emp e ON d.id = e.deptId
) ranked
WHERE rnk = 2
GROUP BY deptName;

发表回复取消回复