MySQL - 练习题

最新推荐文章于 2022-09-16 09:49:05 发布

满天繁星_

最新推荐文章于 2022-09-16 09:49:05 发布

阅读量480

点赞数

分类专栏：笔记文章标签： mysql 数据库开发

本文链接：https://blog.csdn.net/weixin_51081062/article/details/124209443

版权

笔记专栏收录该内容

15 篇文章 0 订阅

订阅专栏

本文展示了四个数据库表的设计，包括class、teacher、course和student，并提供了相关的数据插入操作。接着，给出了五个SQL查询，用于获取课程及教师信息、查询平均成绩高于80分的学生、找出未选李平老师课程的学生、筛选未同时选修物理和体育的学生以及查找挂科超过两门的学生。这些查询展示了数据库操作的实用性和灵活性。

摘要由CSDN通过智能技术生成

结构

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3307
 Source Server Type    : MySQL
 Source Server Version : 50733
 Source Host           : localhost:3307
 Source Schema         : day48

 Target Server Type    : MySQL
 Target Server Version : 50733
 File Encoding         : 65001

 Date: 15/04/2022 17:04:52
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `cid` int(11) NOT NULL,
  `caption` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cid` int(11) NOT NULL,
  `cname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `teacher_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE,
  INDEX `teacher_id`(`teacher_id`) USING BTREE,
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `sid` int(11) NOT NULL,
  `student_id` int(11) NULL DEFAULT NULL,
  `course_id` int(11) NULL DEFAULT NULL,
  `num` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `student_id`(`student_id`) USING BTREE,
  INDEX `course_id`(`course_id`) USING BTREE,
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(11) NOT NULL,
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `class_id` int(11) NULL DEFAULT NULL,
  `sname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `class_id`(`class_id`) USING BTREE,
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

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

SET FOREIGN_KEY_CHECKS = 1;

数据

# class
insert into class (cid, caption)
values
('1', '三年二班'),
('2', '三年三班'),
('3', '一年二班'),
('4', '二年九班')
;

# teacher
insert into teacher(tid, tname)
values
('1', '张磊老师'),
('2', '李平老师'),
('3', '刘海燕老师'),
('4', '朱云海老师'),
('5', '李杰老师')
;

# course
insert into course (cid, cname, teacher_id)
values
('1', '生物', '1'),
('2', '物理', '2'),
('3', '体育', '3'),
('4', '美术', '2')
;


# student
insert into student
(sid, gender, class_id, sname)
values
('1', '男', '1', '理解'),
('2', '女', '1', '钢弹'),
('3', '男', '1', '张三'),
('4', '男', '1', '张一'),
('5', '女', '1', '张二'),
('6', '男', '1', '张四'),
('7', '女', '2', '铁锤'),
('8', '男', '2', '李三'),
('9', '男', '2', '李一'),
('10', '女', '2', '李二'),
('11', '男', '2', '李四'),
('12', '女', '3', '如花'),
('13', '男', '3', '刘三'),
('14', '男', '3', '刘一'),
('15', '女', '3', '刘二'),
('16', '男', '3', '刘四')
;

# score
insert into score
(sid, student_id, course_id, num)
values
('1', '1', '1', '10'),
('2', '1', '2', '9'),
('5', '1', '4', '66'),
('6', '2', '1', '8'),
('8', '2', '3', '68'),
('9', '2', '4', '99'),
('10', '3', '1', '77'),
('11', '3', '2', '66'),
('12', '3', '3', '87'),
('13', '3', '4', '99'),
('14', '4', '1', '79'),
('15', '4', '2', '11'),
('16', '4', '3', '67'),
('17', '4', '4', '100'),
('18', '5', '1', '79'),
('19', '5', '2', '11'),
('20', '5', '3', '67')
;

题目

查询所有的课程的名称以及对应的任课老师姓名
查询平均成绩大于八十分的同学的姓名和平均成绩
查询没有报李平老师课的学生姓名
查询没有同时选修物理课程和体育课程的学生姓名
查询挂科超过两门(包括两门)的学生姓名和班级

答案

SELECT
	course.cname,
	teacher.tname 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid;



SELECT
	student.sname,
	t1.avg_num 
 FROM
 	student
 	INNER JOIN (
     	SELECT
   		score.student_id,
     		avg( num ) AS avg_num 
     	FROM
     		score
     		INNER JOIN student ON score.student_id = student.sid 
     	GROUP BY
     		score.student_id 
     	HAVING
     		AVG( num ) > 80 
	 	) AS t1 
 ON student.sid = t1.student_id;






 SELECT
 	student.sname 
 FROM
 	student 
 WHERE
	sid NOT IN (
 	SELECT DISTINCT
		score.student_id 
	FROM
 		score 
	WHERE
		score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' ) 
	);





SELECT
 	student.sname 
FROM
	student 
 WHERE
 	student.sid IN (
 	SELECT
 		score.student_id 
 	FROM
 		score 
 	WHERE
 		score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) ) 
 	GROUP BY
 		score.student_id 
 	HAVING
		COUNT( score.course_id ) = 1 
	);





SELECT
	class.caption,
	student.sname 
FROM
	class
	INNER JOIN student ON class.cid = student.class_id 
WHERE
	student.sid IN (
	SELECT
		score.student_id 
	FROM
		score 
	WHERE
		score.num < 60 GROUP BY score.student_id HAVING COUNT( score.course_id ) >= 2 
	);