SQL 数据库操作：学生、课程和成绩表实例

本教程将演示如何使用 SQL 创建学生、课程和成绩表，并进行各种数据操作。

1. 创建表并添加数据

首先，我们将创建三个表：student、course 和 score，并添加一些示例数据。

学生表 (student)

CREATE TABLE student (
    sno INT PRIMARY KEY,
    sname VARCHAR(20),
    sex VARCHAR(2),
    birthday DATE,
    depart VARCHAR(20)
);

INSERT INTO student VALUES
(1001, '李明', '男', '2002-03-15', '计算机系'),
(1002, '张然', '女', '2003-11-05', '经管系'),
(1003, '许晴', '女', '2002-08-11', '电子系');

课程表 (course)

CREATE TABLE course (
    cno VARCHAR(3) PRIMARY KEY,
    cname VARCHAR(20),
    credit INT
);

INSERT INTO course VALUES
('001', 'MySQL 数据库', 4),
('002', '线性代数', 3),
('003', '专业英语', 2);

成绩表 (score)

CREATE TABLE score (
    sno INT,
    cno VARCHAR(3),
    grade INT,
    PRIMARY KEY (sno, cno),
    FOREIGN KEY (sno) REFERENCES student(sno),
    FOREIGN KEY (cno) REFERENCES course(cno)
);

INSERT INTO score VALUES
(1001, '001', 90),
(1002, '001', 85),
(1002, '002', 76),
(1003, '003', 70);

2. SQL 查询操作

以下是一些常用的 SQL 查询操作示例：

(1) 查询 MySQL 数据库成绩为良好（80~89 分）的学生。

SELECT s.sno, s.sname
FROM student s
INNER JOIN score sc ON s.sno = sc.sno
WHERE sc.grade >= 80 AND sc.grade <= 89;

(2) 把 course 表中课程号为 002 的课程的学分修改为 4。

UPDATE course
SET credit = 4
WHERE cno = '002';

(3) 在 student 表中查询 2002 年出生的学生的所有信息，并按学号降序排列。

SELECT *
FROM student
WHERE YEAR(birthday) = 2002
ORDER BY sno DESC;

(4) 查询年龄最大的 2 名学生的学号、姓名和出生日期。

SELECT sno, sname, birthday
FROM student
ORDER BY birthday DESC
LIMIT 2;

(5) 查询选修了“专业英语”课程的学生的学号、姓名及成绩。

SELECT s.sno, s.sname, sc.grade
FROM student s
INNER JOIN score sc ON s.sno = sc.sno
INNER JOIN course c ON c.cno = sc.cno
WHERE c.cname = '专业英语';

(6) 统计每个学生的平均分。

SELECT s.sno, s.sname, AVG(sc.grade) AS average_grade
FROM student s
INNER JOIN score sc ON s.sno = sc.sno
GROUP BY s.sno, s.sname;

3. 添加成绩等级列并分类

(1) 在 score 表后增加一列成绩等级 grade。

ALTER TABLE score
ADD grade VARCHAR(10);

(2) 将成绩分类为“优秀”、“良好”、“中等”、“及格”和“不及格”。

UPDATE score
SET grade = CASE
    WHEN grade >= 90 THEN '优秀'
    WHEN grade >= 80 AND grade <= 89 THEN '良好'
    WHEN grade >= 70 AND grade <= 79 THEN '中等'
    WHEN grade >= 60 AND grade <= 69 THEN '及格'
    ELSE '不及格'
    END;

总结

本教程展示了如何创建和管理 SQL 数据库表，以及如何使用各种 SQL 语句进行查询、更新、修改和统计。通过学习本教程，您将能够更有效地使用 SQL 进行数据处理。