简单子查询

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

子查询分类：
1、 相关子查询
      执行依赖于外部查询的数据
      外部查询返回一行，子查询就执行一次。
2、非相关子查询
      独立于外部查询的子查询
      子查询总共执行一次，执行完毕后将值传递给外部查询
 相关子查询通常要消耗更长的时间，当数据量增加时，执行时间会急剧增加

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)