本文共 1893 字，大约阅读时间需要 6 分钟。

随时随地阅读更多技术实战干货，获取项目源码、学习资料，请关注源代码社区公众号(ydmsq666)、QQ技术交流群(183198395)。

子查询分类：

CREATE TABLE lovoStudent(id INT PRIMARY KEY AUTO_INCREMENT,studentName VARCHAR(20),SUBJECT VARCHAR(20),grade INT)DEFAULT CHARSET =utf8;INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('张三','java基础',97);INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('张三','数据库',80);INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('张三','java web',96);INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('李四','数据库',95);INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('李四','java基础',94);INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('王五','java基础',85);-- 查询java基础最高分及其最高分的得主SELECT * FROM lovoStudent t WHERE grade=(SELECT MAX(grade) FROM lovoStudent t1 WHERE SUBJECT='java基础' AND t1.SUBJECT=t.SUBJECT)-- 查询各科目最高分及其得主SELECT *  FROM lovoStudent t  WHERE grade=(SELECT MAX(grade) FROM lovoStudent t1 WHERE t.SUBJECT =t1.SUBJECT )SELECT *  FROM lovoStudent t  WHERE grade IN(SELECT MAX(grade) FROM lovoStudent t1 WHERE t.SUBJECT =t1.SUBJECT GROUP BY SUBJECT)CREATE TABLE t_lovoClass(id INT PRIMARY KEY AUTO_INCREMENT,className VARCHAR(20))DEFAULT CHARSET =utf8;INSERT INTO t_lovoClass(className) VALUES('AT01');INSERT INTO t_lovoClass(className) VALUES('AT02');INSERT INTO t_lovoClass(className) VALUES('AT03');INSERT INTO t_lovoClass(className) VALUES('AT04')ALTER TABLE lovoStudent ADD classId INT;UPDATE lovoStudent SET classId=1 WHERE id<3;UPDATE lovoStudent SET classId=2 WHERE id>5;UPDATE lovoStudent SET classId=3 WHERE id>=3 AND id<=5;-- 查询学生表所有内容并加上对应班级信息SELECT e.*,(SELECT className FROM t_lovoClass c WHERE e.classId=c.id) className FROM lovoStudent e;-- 查询所有没有学生的班级SELECT className FROM t_lovoClass WHERE id NOT IN(SELECT classId FROM lovoStudent)

转载地址：http://nvtei.baihongyu.com/