数据库查询

一. 单表查询

3.
（1）使用
SELECT sc.Sno, sc.Cno AS CourseCount
FROM sc
GROUP BY sc.Sno	
虽然时按Sno进行分组，但是Cno并没有分组是随机的

（2）使用
SELECT sc.Sno, GROUP_CONCAT(sc.Cno) AS CourseCount
FROM sc
GROUP BY sc.Sno
使用聚合函数之后，Coursecount正常被分组

(3)使用
SELECT sc.Sno, COUNT(sc.Cno) AS CourseCount 
FROM sc GROUP BY sc.Sno
对相同的Cno进行计数

SELECT sc.Sno, COUNT(sc.Cno) AS CourseCount
FROM sc
GROUP BY sc.Sno
HAVING COUNT(sc.Cno) >= 3;
在having后使用聚合函数，然后在其结果中进行选择，正确得到第3题答案

SELECT Sdept, COUNT(*) AS StudentCount
FROM student
WHERE Sage > 20 AND (Sdept = '信息管理系' OR Sdept = '数学系')
GROUP BY Sdept;
先进行where的条件判断，然后根据其结果按goup by进行分组

二. 表连接查询

(1) 使用
SELECT * FROM student JOIN course
两个表做笛卡尔积，表一n行，表二m行，连接后m*n行

（2）使用
SELECT * FROM student 
JOIN sc ON student.Sno = sc.Sno 
JOIN course ON sc.Cno = course.Cno
通常来说我们通过外键来连接两个表，而且我们看sc表Sno和Con恰好是连接student表和course表的外键，起到一个承上启下的作用

(3)使用
SELECT student.Sname, course.Cname, sc.Grade
FROM student
JOIN sc ON student.Sno = sc.Sno
JOIN course ON sc.Cno = course.Cno
WHERE student.Sdept = 'aaa'   -- 筛选aaa系
  AND student.Ssex = 'male'          -- 筛选性别为女
  AND course.Cname = 'computer';   -- 筛选课程为computer

三. 子查询

1.

SELECT Sno, Sname
FROM student
WHERE Sno IN (
    SELECT sc.Sno
    FROM sc
    WHERE sc.Cno = (
        SELECT course.Cno
        FROM course
        WHERE course.Cname = 'exercise'
    )
);
和连接查询很像，从内层向外，不断使用外键查询。
内层子查询：
SELECT course.Cno：首先从 course 表中查询课程名称为“exercise”的课程编号（Cno）。
中间子查询：
SELECT sc.Sno：根据内层子查询返回的 Cno，从 sc 表中查询所有选修了该课程的学生学号（Sno）。
外层查询：
SELECT Sno, Sname FROM student：根据中间子查询得到的学号，在 student 表中查询这些学生的学号
和姓名。

2.

SELECT student.Sno, student.Sname,    
       (SELECT COUNT(*)
        FROM sc
        WHERE sc.Sno = student.Sno) AS CourseCount,  -- 关联子查询，加上括号和逗号放到紧跟外层查询
       (SELECT AVG(sc.Grade)
        FROM sc
        WHERE sc.Sno = student.Sno) AS AvgGrade      -- 关联子查询
FROM student
WHERE student.Sno IN (
    SELECT sc.Sno
    FROM sc
    JOIN course ON sc.Cno = course.Cno
    WHERE course.Cname = 'exercise'                       -- 独立子查询
);
这里使用了关联子查询，关联子查询依赖外层查询，比如这里的关联子查询依赖于外层查询的student.Sno, student.Sname，并且结果会和外层查询并列输出。相反，独立子查询通常在外层查询之前执行，外层查询依赖独立子查询。

四. 练习

如果直接使用

SELECT student.Sname, student.Sdept 
FROM student 
WHERE Sno IN 
    (SELECT Sno 
     FROM sc 
     WHERE sc.Cno != '1' --没有选修1的课程
    );
 因为select语句是一行一行检查，所以如果表结构如下

这样因为有人选了1但也选了除了1之外的课程，这样也会被选上。导致错误

改进：

SELECT student.Sname, student.Sdept 
FROM student 
WHERE Sno NOT IN 
    (SELECT Sno 
     FROM sc 
     WHERE sc.Cno = '1' --选修1的课程
    );
    我们找选了1的人，那所有选了1的人都会被找到。然后使用NOT IN 就解决了 

SELECT sc.Sno, sc.Grade 
FROM sc 
WHERE sc.Cno = '1' 
  AND sc.Grade > (
    SELECT AVG(sc.Grade) 
    FROM sc 
    WHERE sc.Cno = '1'
  );

SELECT student.Sno, student.Sname
FROM student 
WHERE student.Sno IN
	(SELECT sc.Sno 
     FROM sc 
     WHERE sc.Cno='1' OR sc.Sno='2'
    )

4. 查询至少选修了学生“02”选修的全部课程的学号