MySql练习题

准备工作:建表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cno` int(11) NOT NULL,
  `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tno` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, 'Java基础', 1);
INSERT INTO `course` VALUES (2, 'Java高级', 1);
INSERT INTO `course` VALUES (3, 'C#', 2);
INSERT INTO `course` VALUES (4, '计算机网络', 2);
INSERT INTO `course` VALUES (5, '算法与结构', 4);
INSERT INTO `course` VALUES (6, '数字模拟电子', 3);

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `sno` int(11) NOT NULL,
  `cno` int(11) NULL DEFAULT NULL,
  `score` int(255) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (1001, 1, 90);
INSERT INTO `sc` VALUES (1001, 2, 80);
INSERT INTO `sc` VALUES (1001, 3, 60);
INSERT INTO `sc` VALUES (1002, 1, 56);
INSERT INTO `sc` VALUES (1002, 2, 90);
INSERT INTO `sc` VALUES (1002, 4, 66);
INSERT INTO `sc` VALUES (1003, 1, 90);
INSERT INTO `sc` VALUES (1003, 2, 70);
INSERT INTO `sc` VALUES (1003, 4, 60);
INSERT INTO `sc` VALUES (1003, 3, 80);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `course` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `score` int(255) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('张三', '语文', 81);
INSERT INTO `score` VALUES ('张三', '数学', 80);
INSERT INTO `score` VALUES ('李四', '语文', 76);
INSERT INTO `score` VALUES ('王五', '语文', 81);
INSERT INTO `score` VALUES ('王五', '数学', 100);
INSERT INTO `score` VALUES ('王五', '英语', 90);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sno` int(11) NOT NULL,
  `sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sage` int(255) NULL DEFAULT NULL,
  `ssex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1001, 'tom', 22, '男');
INSERT INTO `student` VALUES (1002, 'jack', 23, '男');
INSERT INTO `student` VALUES (1003, 'rose', 24, '女');

-- ----------------------------
-- Table structure for student1
-- ----------------------------
DROP TABLE IF EXISTS `student1`;
CREATE TABLE `student1`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of student1
-- ----------------------------
INSERT INTO `student1` VALUES (1, '张三');
INSERT INTO `student1` VALUES (2, '张三');
INSERT INTO `student1` VALUES (3, '李四');
INSERT INTO `student1` VALUES (4, '王五');
INSERT INTO `student1` VALUES (5, '王五');
INSERT INTO `student1` VALUES (6, '王五');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tno` int(11) NOT NULL,
  `tname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '张力');
INSERT INTO `teacher` VALUES (2, '李四');
INSERT INTO `teacher` VALUES (3, '王强');
INSERT INTO `teacher` VALUES (4, '李晓燕');

SET FOREIGN_KEY_CHECKS = 1;

练习题:

1.查询课程1的成绩比课程2的成绩高的所有学生的信息

考点:表的自链接查询

思路：将sc表当成2份，一份视为课程1的成绩表，一份视为课程2的成绩表，两表连接，连接条件为学号相等，且cno=1表中的成绩大于cno=2表中的成绩，得到的学号即为查询的学号，最后和学生表联查，获取对应的学生信息即可。

SELECT s.*,sc1.score '课程1',sc2.score '课程2' FROM sc sc1 JOIN sc sc2 JOIN student s ON sc1.sno=sc2.sno AND 
sc1.score>sc2.score AND sc1.sno=s.sno WHERE sc1.cno=1 AND sc2.cno=2

2.查询平均成绩大于60分的同学的学号和平均成绩

select sno,avg(score ) av from sc group by sno having av>60

3.查询学过'李四'老师所教课程的所有同学的学号丶姓名

注意: 需要对结果集去重操作

SELECT DISTINCT s.sno '学号',s.sname '姓名' FROM sc JOIN  student s ON sc.sno=s.sno  WHERE
 sc.cno 
IN (
SELECT c.cno 
FROM course c JOIN teacher t ON c.tno=t.tno WHERE t.tname='李四'
)

4.查询每门课程的选修人数(课程名称,学生数量)---存在没有人选的课程

SELECT c.cno,cname,COUNT(sno) FROM course 
c LEFT JOIN sc ON c.cno=sc.cno GROUP BY c.cno

5.删除学号为"1002"的同学的'1'课程的成绩

delete from sc where sno='1002' and cno=1

6.查询选修人数最多的课程(课程id,课程名称,学生数量)--考虑有多门课程都是选修最多的情况

分析:统计每门课程的选秀人数,选秀人数最多的是多少

        获取选修人数=以上查询结果人数的课程

SELECT c.cno,cname,COUNT(sno) cou FROM sc JOIN course c ON 
sc.cno=c.cno GROUP BY sc.cno HAVING
cou=(SELECT COUNT(sno) cou FROM sc GROUP BY cno ORDER BY cou DESC LIMIT 1)