T-SQL 语句实战：学生信息管理系统案例

本教程通过一系列 T-SQL 语句案例，演示如何使用 T-SQL 操作学生信息数据库，涵盖数据插入、查询、更新和删除等操作，并涉及内连接、外连接、派生表、嵌套查询、分组统计、条件语句、子查询等常用技巧。

数据库结构

STUDENT 表
- 学号 (sno)
- 姓名 (sname)
- 性别 (ssex)
- 年龄 (sage)
- 所在系 (sdept)
COURSE 表
- 课程号 (cno)
- 课程名 (cname)
- 学分 (ccredit)
SC 表
- 学号 (sno)
- 课程号 (cno)
- 成绩 (grade)

案例演示

将计算机系学生信息放入新表

SELECT 学号, 姓名, 性别, 年龄 INTO JSJSTUDENT
FROM STUDENT
WHERE 所在系 = '计算机系';

查询年龄最大的 3 位学生信息

SELECT TOP 3 学号, 姓名, 性别, 年龄 INTO JSJSTUDENT
FROM STUDENT
ORDER BY 年龄 DESC;

查询计算机系学生选修课程信息（两种方式）

显式内连接

SELECT S.学号, S.姓名, S.性别, C.课程名, SC.成绩
FROM STUDENT S
JOIN SC ON S.学号 = SC.学号
JOIN COURSE C ON SC.课程号 = C.课程号
WHERE S.所在系 = '计算机系';

派生表查询

SELECT S.学号, S.姓名, S.性别, C.课程名, SC.成绩
FROM STUDENT S, SC, COURSE C
WHERE S.学号 = SC.学号
AND SC.课程号 = C.课程号
AND S.所在系 = '计算机系';

查询没有选修课程的学生信息

SELECT S.学号, S.姓名, S.所在系
FROM STUDENT S
LEFT JOIN SC ON S.学号 = SC.学号
WHERE SC.学号 IS NULL;

查询没有人选修的课程信息（两种方式）

外连接

SELECT C.课程号, C.课程名, C.学分
FROM COURSE C
LEFT JOIN SC ON C.课程号 = SC.课程号
WHERE SC.课程号 IS NULL;

嵌套查询

SELECT 课程号, 课程名, 学分
FROM COURSE
WHERE 课程号 NOT IN (SELECT 课程号 FROM SC);

统计每名学生选修课程门数和平均分

SELECT S.学号, S.姓名, S.所在系, COUNT(SC.课程号) AS 选课门数, AVG(SC.成绩) AS 课程平均分
FROM STUDENT S
LEFT JOIN SC ON S.学号 = SC.学号
GROUP BY S.学号, S.姓名, S.所在系
ORDER BY 选课门数 DESC
LIMIT 3;

统计除 2 号课程外的课程选课人数和平均分

SELECT C.课程号, C.课程名, C.学分, COUNT(SC.学号) AS 选课人数, AVG(SC.成绩) AS 平均分
FROM COURSE C
LEFT JOIN SC ON C.课程号 = SC.课程号
WHERE C.课程号 <> '2号课程'
GROUP BY C.课程号, C.课程名, C.学分
UNION ALL
SELECT C.课程号, C.课程名, C.学分, COUNT(SC.学号) AS 选课人数, AVG(SC.成绩) AS 平均分
FROM COURSE C
JOIN SC ON C.课程号 = SC.课程号
WHERE C.课程号 = '2号课程'
GROUP BY C.课程号, C.课程名, C.学分;

查询每门课程的男女生选课人数和平均分（使用 WITH ROLLUP 和 WITH CUBE）

WITH ROLLUP

SELECT SC.课程号, S.性别, COUNT(S.学号) AS 选课人数, AVG(SC.成绩) AS 平均分
FROM STUDENT S
JOIN SC ON S.学号 = SC.学号
GROUP BY SC.课程号, S.性别 WITH ROLLUP;

WITH CUBE

SELECT SC.课程号, S.性别, COUNT(S.学号) AS 选课人数, AVG(SC.成绩) AS 平均分
FROM STUDENT S
JOIN SC ON S.学号 = SC.学号
GROUP BY SC.课程号, S.性别 WITH CUBE;

查询所有学生信息，统计总人数和平均年龄（两种方式）

COMPUTE 子句

SELECT COUNT(*) AS 总人数, AVG(年龄) AS 平均年龄
FROM STUDENT
COMPUTE SUM(COUNT(*)), AVG(年龄);

批处理

SELECT COUNT(*) AS 总人数, AVG(年龄) AS 平均年龄
FROM STUDENT;
SELECT SUM(COUNT(*)), AVG(年龄)
FROM STUDENT;

查询每个学生选修课程信息，并统计课程数和平均分（两种方式）

COMPUTE BY 子句

SELECT 学号, 姓名, 课程号, 成绩
FROM SC
ORDER BY 学号
COMPUTE COUNT(课程号), AVG(成绩) BY 学号;

批处理

SELECT 学号, 姓名, 课程号, 成绩
FROM SC
ORDER BY 学号;
SELECT COUNT(课程号), AVG(成绩)
FROM SC
GROUP BY 学号;

查询选修了 '数据库' 课程的计算机系学生信息，成绩以等级制显示

SELECT S.学号, S.姓名, 
CASE 
    WHEN SC.成绩 >= 90 THEN '优秀'
    WHEN SC.成绩 >= 80 THEN '良好'
    WHEN SC.成绩 >= 70 THEN '中等'
    WHEN SC.成绩 >= 60 THEN '及格'
    ELSE '不及格'
END AS 成绩等级
FROM STUDENT S
JOIN SC ON S.学号 = SC.学号
JOIN COURSE C ON SC.课程号 = C.课程号
WHERE S.所在系 = '计算机系'
AND C.课程名 = '数据库';

将计算机系学生所有成绩降低 5 分（两种方式）

子查询

UPDATE SC
SET 成绩 = (SELECT 成绩 - 5
            FROM SC
            WHERE 学号 IN (SELECT 学号 FROM STUDENT WHERE 所在系 = '计算机系'))
WHERE 学号 IN (SELECT 学号 FROM STUDENT WHERE 所在系 = '计算机系');

FROM 子句

UPDATE SC
SET 成绩 = SC.成绩 - 5
FROM SC
JOIN STUDENT S ON SC.学号 = S.学号
WHERE S.所在系 = '计算机系';

删除计算机系成绩不及格学生的选课记录（两种方式）

子查询

DELETE FROM SC
WHERE 学号 IN (SELECT 学号
                FROM STUDENT
                WHERE 所在系 = '计算机系'
                AND 学号 IN (SELECT 学号
                            FROM SC
                            WHERE 成绩 < 60));

FROM 子句

DELETE SC
FROM SC
JOIN STUDENT S ON SC.学号 = S.学号
WHERE S.所在系 = '计算机系'
AND SC.成绩 < 60;

总结

本教程演示了 T-SQL 在学生信息管理系统中的应用，涵盖了数据库操作的基本技巧。通过学习这些案例，你可以更好地理解 T-SQL 语法，并能够独立完成更复杂的操作。

注意：

以上代码示例中的数据表名和字段名仅供参考，请根据实际情况进行修改。
实际操作中，请根据数据库的安全策略进行操作，避免出现数据丢失或安全问题。
建议在正式环境中使用数据库备份和恢复功能，确保数据的安全。