T-SQL 语句实战：学生成绩管理系统示例

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

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

SELECT * FROM JSJSTUDENT ORDER BY 年龄 DESC;

-- 使用显式内连接 SELECT S.学号, S.姓名, S.性别, C.选修课程名, C.成绩 FROM STUDENT AS S JOIN COURSE AS C ON S.学号 = C.学号 WHERE S.系别 = '计算机系';

-- 使用派生表查询 SELECT S.学号, S.姓名, S.性别, C.选修课程名, C.成绩 FROM STUDENT AS S, (SELECT 学号, 选修课程名, 成绩 FROM COURSE) AS C WHERE S.学号 = C.学号 AND S.系别 = '计算机系';

SELECT S.学号, S.姓名, S.系别 FROM STUDENT AS S LEFT JOIN COURSE AS C ON S.学号 = C.学号 WHERE C.学号 IS NULL;

-- 使用外连接 SELECT C.课程号, C.课程名, C.学分 FROM COURSE AS C LEFT JOIN (SELECT 学号 FROM COURSE AS C WHERE C.学号 IS NOT NULL) AS S ON C.课程号 = S.课程号 WHERE S.学号 IS NULL;

-- 使用嵌套查询 SELECT 课程号, 课程名, 学分 FROM COURSE WHERE 课程号 NOT IN (SELECT DISTINCT 课程号 FROM COURSE);

SELECT TOP 3 S.学号, S.姓名, S.系别, COUNT(C.学号) AS 选课门数, AVG(C.成绩) AS 课程平均分 FROM STUDENT AS S JOIN COURSE AS C ON S.学号 = C.学号 GROUP BY S.学号, S.姓名, S.系别 ORDER BY COUNT(C.学号) DESC;

SELECT C.课程号, C.课程名, C.学分, COUNT(C.学号) AS 选课人数, AVG(C.成绩) AS 平均分 FROM COURSE AS C LEFT JOIN COURSE AS C2 ON C.课程号 = C2.课程号 WHERE C.课程号 = 2 OR C2.学号 IS NOT NULL GROUP BY C.课程号, C.课程名, C.学分;

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

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

SELECT 学号, 姓名, 性别, 年龄 FROM STUDENT COMPUTE COUNT(学号), AVG(年龄);

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

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

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

-- 使用子查询 UPDATE COURSE SET 成绩 = (SELECT 成绩 - 5 FROM COURSE WHERE 学号 IN (SELECT 学号 FROM STUDENT WHERE 系别 = '计算机系'));

-- 使用FROM子句 UPDATE C SET C.成绩 = C.成绩 - 5 FROM COURSE AS C JOIN STUDENT AS S ON C.学号 = S.学号 WHERE S.系别 = '计算机系';

-- 使用子查询 DELETE FROM COURSE WHERE 学号 IN (SELECT 学号 FROM COURSE WHERE 成绩 < 60);

-- 使用FROM子句 DELETE C FROM COURSE AS C JOIN (SELECT 学号 FROM COURSE WHERE 成绩 < 60) AS S ON C.学号 = S.学号;