【MySQL数据库学习】

MySQL数据库学习：创建和查询学生、课程及成绩表

在这篇博客中，我们将介绍如何在MySQL数据库中创建并操作学生、课程及成绩表。这包括创建表结构、插入数据，以及执行各种查询操作。我们将使用三个表：student（学生表）、course（课程表）和SC（成绩表）。每个表的结构和数据如下：

一、创建表结构

首先，我们创建三个表：student、course 和 SC。
– Student(Sno,Sname,Ssex,Sage,Sdept)
– Course(Cno,Cname,Cpno,Ccredit)
– Sc(Sno,Cno,Grade )

-- 创建学生表
CREATE TABLE student (
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) UNIQUE,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);

-- 创建课程表
CREATE TABLE course (
    Cno CHAR(4) PRIMARY KEY,
    Cname CHAR(40) NOT NULL,
    Cpno CHAR(4),
    Ccredit SMALLINT,
    FOREIGN KEY (Cpno) REFERENCES course(Cno)
);

-- 创建成绩表
CREATE TABLE SC (
    SNO CHAR(9),
    CNO CHAR(4),
    GRADE SMALLINT,
    PRIMARY KEY (SNO, CNO),
    FOREIGN KEY (SNO) REFERENCES student(Sno),
    FOREIGN KEY (CNO) REFERENCES course(Cno)
);

二、插入数据

接下来，我们向这三个表中插入一些数据。

-- 向学生表中插入数据
INSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES
('201215121', '李勇', '男', 20, 'CS'),
('201215122', '刘晨', '女', 19, 'CS'),
('201215123', '王敏', '女', 18, 'MA'),
('201215125', '张立', '男', 19, 'IS');

-- 向课程表中插入数据
INSERT INTO course (Cno, Cname, Cpno, Ccredit) VALUES
('1', '数据库', '5', 4),
('2', '数学', NULL, 2),
('3', '信息系统', '1', 4),
('4', '操作系统', '6', 3),
('5', '数据结构', '7', 4),
('6', '数据处理', NULL, 2),
('7', 'JAVA', '6', 4);

-- 向成绩表中插入数据
INSERT INTO SC (Sno, Cno, Grade) VALUES
('201215121', '1', 92),
('201215121', '2', 85),
('201215121', '3', 88),
('201215122', '2', 90),
('201215122', '3', 59);

三、查询操作

现在我们可以执行各种查询来获取所需的信息。

1. 查询学生总数

-- 查询学生总数
SELECT COUNT(*) FROM student;

2. 查询选修了课程的学生人数

-- 查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) FROM SC;

3. 计算选修了一号课程的学生平均成绩

-- 计算选修了一号课程的学生平均成绩
SELECT AVG(Grade) FROM SC WHERE Cno = '1';

4. 查询选修了一号课程的学生最高分数

-- 查询选修了一号课程的学生最高分数
SELECT MAX(GRADE) FROM SC WHERE Cno = '1';

5. 查询学生201215122的选修课程的总学分

-- 查询学生201215122的选修课程的总学分
SELECT SUM(Ccredit) AS 总学分 
FROM SC, course 
WHERE Sno = '201215122' AND SC.CNO = course.Cno;

6. 求各个课程号以及相应的人数

-- 求各个课程号以及相应的人数
SELECT Cno, COUNT(Sno) 
FROM SC 
GROUP BY Cno;

7. 查询选修了一门以上的学生学号

-- 查询选修了一门以上的学生学号
SELECT Sno 
FROM SC 
GROUP BY Sno 
HAVING COUNT(*) > 1;

8. 查询平均成绩大于80分的学生学号和平均成绩

-- 查询平均成绩大于80分的学生学号和平均成绩
SELECT Sno, AVG(GRADE) 
FROM SC 
GROUP BY Sno 
HAVING AVG(GRADE) > 80;

四、连接查询

1. 查询每个学生以及选修课程的情况

-- 查询每个学生以及选修课程的情况
SELECT SC.*, student.* 
FROM SC, student 
WHERE SC.SNO = student.Sno;

2. 使用左连接：查询学生表和SC表的所有学生信息

-- 使用左连接
SELECT * 
FROM student 
LEFT OUTER JOIN SC 
ON student.sno = SC.SNO;

3. 使用自然连接：查询每个学生以及选修课程的情况

-- 使用自然连接
SELECT student.sno, Sname, Ssex, Sage, Sdept, CNO, GRADE 
FROM student, SC 
WHERE student.Sno = SC.SNO;

4. 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名

-- 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
SELECT student.Sno, Sname 
FROM student, SC  
WHERE student.Sno = SC.SNO AND SC.CNO = '2' AND SC.GRADE > 90;

5. 自己连接：查询每一门课的间接先修课

-- 自己连接：查询每一门课的间接先修课
SELECT FIRST.Cno, SECOND.Cpno 
FROM course FIRST, course SECOND 
WHERE FIRST.Cpno = SECOND.Cno;

6. 多表连接：查询每个学生的学号、姓名、选修的课程名和成绩

-- 多表连接：查询每个学生的学号、姓名、选修的课程名和成绩
SELECT student.Sno, Sname, Cname, GRADE 
FROM student, SC, course 
WHERE student.Sno = SC.SNO AND SC.CNO = course.Cno;

7. 嵌套查询：选修2号课程的学生姓名

-- 嵌套查询：选修2号课程的学生姓名
SELECT Sname 
FROM student 
WHERE SNO IN (SELECT Sno FROM SC WHERE CNO = '2');

8. 使用EXISTS查询：选修2号课程的学生姓名

-- 使用EXISTS查询：选修2号课程的学生姓名
SELECT Sname 
FROM student 
WHERE EXISTS (SELECT * FROM SC WHERE CNO = '2' AND student.sno = SC.SNO);

9. 不相关子查询：查询与刘晨在同一个系学习的学生

-- 不相关子查询：查询与刘晨在同一个系学习的学生
SELECT *  
FROM student 
WHERE student.Sdept IN (
    SELECT student.Sdept 
    FROM student 
    WHERE student.Sname = '刘晨'
);

10. 查询选修了课程名为“信息系统”的学生学号和姓名

-- 使用嵌套查询实现
SELECT Sname, Sno 
FROM student 
WHERE Sno IN (
    SELECT sno 
    FROM SC 
    WHERE cno IN (
        SELECT CNO  
        FROM course 
        WHERE Cname = '信息系统'
    )
);

-- 使用自然连接实现
SELECT student.Sname, student.SNO 
FROM student, SC, course 
WHERE student.Sno = SC.Sno 
  AND course.Cno = SC.CNO 
  AND course.Cname = '信息系统';

11. 相关子查询：找出每个学生超过他自己选修课程平均成绩的课程号

-- 相关子查询：找出每个学生超过他自己选修课程平均成绩的课程号
SELECT SNO, CNO  
FROM SC 
WHERE GRADE > (
    SELECT AVG(GRADE) 
    FROM SC 
);

12. 查询非计算机科学系中比计算机科学系任意一学生年龄小的学生姓名和年龄

-- 查询非计算机科学系中比计算机科学系任意一学生年龄小的学生姓名和年龄
SELECT Sname, Sage 
FROM student 
WHERE Sage < ANY (
    SELECT Sage 
    FROM student 
    WHERE Sdept = 'cs'
) AND Sdept <> 'cs';

SELECT Sname, Sage 
FROM student 
WHERE Sage < (
    SELECT MAX(Sage) 
    FROM student 
    WHERE Sdept = 'cs'
) AND Sdept != 'cs';

13. 查询非计算机科学系中比计算机科学系所有学生年龄小的学生姓名和年龄

-- 查询非计算机科学系中比计算机科学系所有学生年龄小的学生姓名和年龄
SELECT Sname, Sage 
FROM student 
WHERE Sage < ALL (
    SELECT Sage 
    FROM```sql
    student 
    WHERE Sdept = 'CS'
) AND Sdept <> 'CS';

SELECT Sname, Sage 
FROM student 
WHERE Sage < (
    SELECT MIN(Sage) 
    FROM student 
    WHERE Sdept = 'CS'
) AND Sdept != 'CS';

14. EXISTS 查询所有选修了1号课程的学生姓名

-- EXISTS 查询所有选修了1号课程的学生姓名
SELECT Sname 
FROM student 
WHERE EXISTS (
    SELECT *  
    FROM SC 
    WHERE Sno = student.Sno 
    AND CNO = '1'
);

15. 使用IN查询与201215121学过同一门课的学生学号

-- 使用IN查询与201215121学过同一门课的学生学号
SELECT DISTINCT Sno 
FROM SC s1 
WHERE CNO IN (
    SELECT CNO 
    FROM SC s2 
    WHERE Sno = '201215121'
) AND s1.Sno != '201215121';

-- 使用JOIN查询与201215121学过同一门课的学生学号
SELECT DISTINCT s2.Sno 
FROM SC s1 
JOIN SC s2 ON s1.Cno = s2.Cno 
WHERE s1.Sno = '201215121' 
AND s2.Sno != '201215121';

16. 查询选修了全部课程的学生姓名

-- 查询选修了全部课程的学生姓名
SELECT Sname 
FROM student 
WHERE NOT EXISTS (
    SELECT * 
    FROM course 
    WHERE NOT EXISTS (
        SELECT * 
        FROM SC 
        WHERE student.Sno = SC.Sno 
        AND course.Cno = SC.Cno
    )
);

17. 查询所有学生的’2’号课程成绩[姓名, 课程名, 成绩]，要求结果集包括未选课的学生

-- 查询所有学生的'2'号课程成绩[姓名, 课程名, 成绩]，要求结果集包括未选课的学生
SELECT student.Sname, course.Cname, SC.Grade 
FROM student 
LEFT JOIN SC ON student.Sno = SC.Sno AND SC.Cno = '2' 
LEFT JOIN course ON SC.Cno = course.Cno;

18. 在WHERE中使用子查询：查询201215121学过的课程信息

-- 使用EXISTS查询201215121学过的课程信息
SELECT * 
FROM course 
WHERE EXISTS (
    SELECT * 
    FROM SC 
    WHERE course.Cno = SC.Cno 
    AND SC.Sno = '201215121'
);

-- 查询201215121没学过的课程信息
SELECT * 
FROM course 
WHERE NOT EXISTS (
    SELECT * 
    FROM SC 
    WHERE course.Cno = SC.Cno 
    AND SC.Sno = '201215121'
);

总结

通过以上步骤，我们已经了解了如何在SQL数据库中创建表、插入数据以及进行各种查询操作。这些查询涵盖了从简单的单表查询到复杂的多表连接和嵌套查询，能够帮助我们有效地管理和操作数据库中的数据。

希望这篇博客能帮助你更好地理解SQL数据库的基础操作。如果有任何问题或建议，欢迎在评论区留言。Happy Coding!