mysql复习第八天：子查询

1、子查询

标量子查询(单行子查询)
列子查询(多行子查询)

• where后面

分数大于张三的

SELECT s_name,score
FROM student
WHERE score > (SELECT
                 s.score
               FROM student s
               WHERE s.s_name = '张三');

• select后面

统计每个班级的人数

SELECT
  c.classname,
  (SELECT
     COUNT(*)
   FROM student s
   WHERE s.class_id = c.classid) AS `sum`
FROM class c;

• from后面

每个班级的平均分

SELECT
  c.classname,
  s1.avgscore
FROM class c,
  (SELECT
     AVG(s.score) AS avgscore,
     s.class_id
   FROM student s
   GROUP BY s.class_id) s1
WHERE s1.class_id = c.classid;

• exists后面

查询存在学生的班级

SELECT
  c.classname
FROM class c
WHERE EXISTS(SELECT *
             FROM student s
             WHERE s.class_id = c.classid);