SQL经典案例（学生表，课程表，选课表，教师表） 练习

一·创建表

课程表

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `CNO` int(10) NOT NULL COMMENT '课程号',
  `CNAME` char(30) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '课程名称',
  `TNO` int(10) NOT NULL COMMENT '授课教师编号',
  `CREDIT` float(10, 1) NOT NULL COMMENT '学分',
  PRIMARY KEY (`CNO`, `TNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

INSERT INTO `course` VALUES (1101, 'Java程序设计', 1, 3.0);
INSERT INTO `course` VALUES (1102, '人工智能概述', 1, 3.0);
INSERT INTO `course` VALUES (1103, 'MySQL数据库', 3, 1.0);
INSERT INTO `course` VALUES (2201, '多元统计分析', 3, 3.5);
INSERT INTO `course` VALUES (2202, '经济计量分析', 2, 2.5);
INSERT INTO `course` VALUES (2203, '宏观经济统计分析', 5, 2.0);
INSERT INTO `course` VALUES (3301, '大学语文', 4, 2.0);
INSERT INTO `course` VALUES (3302, '四进四信', 6, 1.0);
INSERT INTO `course` VALUES (3303, '形式与政策', 7, 0.2);

教师表

DROP TABLE IF EXISTS `newteacher`;
CREATE TABLE `newteacher`  (
  `TNO` int(10) NOT NULL COMMENT '教师编号',
  `TNAME` char(8) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL COMMENT '教师姓名',
  PRIMARY KEY (`TNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

INSERT INTO `newteacher` VALUES (1, '张卓');
INSERT INTO `newteacher` VALUES (2, '范英兵');
INSERT INTO `newteacher` VALUES (3, '孙丽男');
INSERT INTO `newteacher` VALUES (4, '闫晶');
INSERT INTO `newteacher` VALUES (5, '罗晓媛');
INSERT INTO `newteacher` VALUES (6, '王静');
INSERT INTO `newteacher` VALUES (7, '李红红');

选课表

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `SNO` int(10) NOT NULL COMMENT '学号',
  `CNO` int(10) NOT NULL COMMENT '课程号',
  `GRADE` int(10) NOT NULL,
  PRIMARY KEY (`SNO`, `CNO`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

学生表

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `SNO` int(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `SNAME` varchar(30) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `SEX` char(2) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '性别',
  `age` int(20) NOT NULL,
  PRIMARY KEY (`SNO`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

操作题目

将Student 表中的第一条记录的AGE数据修改成比原来大两岁；

UPDATE student SET age=20+2 WHERE SNO=1;

将学生选课表SC中的成绩 GRADE属性名改为：NEWGRADE；

ALTER TABLE sc CHANGE COLUMN GRADE NEWGRADE int(10);
desc sc;

删除STUDENT 表中的最后一条记录。

DELETE FROM student WHERE SNO=15;

查询成绩在60 到 89 之间的所有学生选课记录，查询结果按成绩的降序排列，并为课程号起别名。

SELECT SNO,CNO AS '课程号',NEWGRADE FROM sc WHERE NEWGRADE BETWEEN 60 AND 89 ORDER BY NEWGRADE DESC;

左连接查询所有学生的选课信息。

SELECT c.SNO,stu.SNAME,c.CNO,c.NEWGRADE FROM sc c LEFT JOIN student stu ON c.SNO=stu.SNO;

右连接查询选课的学生的基本信息。

SELECT stu.SNO, stu.SNAME, stu.SEX, stu.age, c.CNO, c.NEWGRADE  FROM sc c RIGHT JOIN student stu ON c.SNO=stu.SNO;

查询选修课成绩在60分以下的学生姓名、性别、年龄。

SELECT SNAME,SEX,age FROM student WHERE SNO IN (SELECT SNO FROM sc WHERE NEWGRADE>60);

查询各门课程取得最高成绩的学生姓名及其成绩。

SELECT s.SNAME,c.CNO,c.NEWGRADE FROM (SELECT CNO,max(NEWGRADE) maxg FROM sc GROUP BY CNO) a,student s,sc c WHERE a.CNO=c.CNO AND a.maxg=c.NEWGRADE AND c.SNO=s.SNO;

查询选修了某门课程的学生的基本信息。

SELECT s.SNO,s.SNAME,s.SEX,s.age,c.CNO FROM student s,sc c WHERE c.CNO IN(SELECT CNO FROM sc WHERE CNO=课程号) AND s.SNO=c.SNO;

查询选修了某位老师开设的课程的学生的信息以及相应的成绩。

SELECT s.SNAME,s.SEX,s.age,c.NEWGRADE,e.CNAME FROM (SELECT TNO,TNAME FROM newteacher WHERE TNAME='老师' GROUP BY TNO) n,student s,sc c,course e WHERE n.TNO=e.TNO AND e.CNO=c.CNO AND c.SNO=s.SNO;

当某一门课程的平均成绩大于85分时，查询选择该门课程学生的基本信息，否则不查询

SELECT s.SNAME,s.SEX,s.age FROM (SELECT SNO,avg(NEWGRADE) avgg FROM sc GROUP BY SNO) a,student s,sc c,course e WHERE a.SNO=c.SNO AND a.avgg=c.NEWGRADE AND c.SNO=s.SNO AND c.CNO=e.CNO;