sql实战-1

sql实战-1

一.按照要求写SQL

1.1.前期准备

首先我们根据表结构创建表，并向其添加一些数据。如下所示

/*
 Navicat Premium Data Transfer

 Source Server         : 本地数据库
 Source Server Type    : MySQL
 Source Server Version : 80033
 Source Host           : localhost:3306
 Source Schema         : sql_test

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

 Date: 24/07/2023 21:39:04
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `cid` int(0) NOT NULL COMMENT '班号',
  `caption` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '班级名称',
  PRIMARY KEY (`cid`) USING BTREE,
  INDEX `cid`(`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '班级表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '三年二班');
INSERT INTO `class` VALUES (2, '一年三班');
INSERT INTO `class` VALUES (3, '三年一班');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cid` int(0) NOT NULL COMMENT '课程编号',
  `cname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '课程名称',
  `teacher_id` int(0) NULL DEFAULT NULL COMMENT '师号',
  PRIMARY KEY (`cid`) USING BTREE,
  INDEX `course_ibfk_1`(`teacher_id`) USING BTREE,
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '课程表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '生物', 1);
INSERT INTO `course` VALUES (2, '体育', 2);
INSERT INTO `course` VALUES (3, '物理', 2);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `sid` int(0) NOT NULL COMMENT '成绩主键',
  `student_id` int(0) NULL DEFAULT NULL COMMENT '学号',
  `course_id` int(0) NULL DEFAULT NULL COMMENT '课程编号',
  `number` decimal(4, 1) NULL DEFAULT NULL COMMENT '分数',
  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 RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '成绩表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 3, 1, 58.0);
INSERT INTO `score` VALUES (2, 2, 3, 32.0);
INSERT INTO `score` VALUES (3, 3, 2, 89.0);
INSERT INTO `score` VALUES (4, 2, 2, 100.0);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(0) NOT NULL COMMENT '学号',
  `sname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学生姓名',
  `gender` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学生性别',
  `class_id` int(0) NULL DEFAULT NULL COMMENT '班号',
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `student_ibfk_1`(`class_id`) USING BTREE,
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '学生表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', '女', 1);
INSERT INTO `student` VALUES (2, '李四', '女', 1);
INSERT INTO `student` VALUES (3, '王五', '男', 2);

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tid` int(0) NOT NULL COMMENT '师号',
  `tname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '老师姓名',
  PRIMARY KEY (`tid`) USING BTREE,
  INDEX `tid`(`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '老师表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '叶平');
INSERT INTO `teacher` VALUES (2, '马化腾');
INSERT INTO `teacher` VALUES (3, '俞敏洪');

SET FOREIGN_KEY_CHECKS = 1;

注意：因为有些表会有外键约束，可能会导致对数据更改有些麻烦，我们可以通过下面指令来设置全局范围不检查外键。

SET GLOBAL FOREIGN_KEY_CHECKS = 0;

1.2.题目

1.2.1.查询平均成绩大于60分的同学的学号、姓名和平均成绩

select
	st.sid sid,
	st.sname sname,
	avg(sc.`number`) avgNumber
from
	student st
left join score sc 
on
	st.sid = sc.student_id
group by
	st.sid
having
	avg(sc.`number`) > 60;

1.2.2.查询所有同学的学号、姓名、选课数、总成绩

select
	st.sid sid,
	st.sname sname,
	count(sc.course_id) courseCount,
	ifnull(sum(sc.`number`), 0) numberSum
from
	student st
left join score sc
on
	st.sid = sc.student_id
group by
	st.sid;

1.2.3.查询姓“李”的老师的个数

select count(1) nameCount
from teacher t 
where t.tname like '李%';

1.2.4.查询没学过“叶平”老师课的同学的学号、姓名

select
	st.sid sid,
	st.sname sname
from
	student st
where
	st.sid not in (
	select
		st.sid sid
	from
		student st
	left join score sc
on
		st.sid = sc.student_id
	where
		sc.course_id in (
		select
			co.cid cid
		from
			course co
		left join teacher te
on
			co.teacher_id = te.tid
		where
			te.tname = '叶平'
)
	group by
		st.sid);

1.2.5.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

select
	a.sid sid,
	b.sname sname
from
	(
	select
		st.sid sid,
		st.sname sname
	from
		student st
	left join score sc
on
		st.sid = sc.student_id
	where
		sc.course_id = 001) a
join (
	select
		st.sid sid ,
		st.sname sname
	from
		student st
	left join score sc
on
		st.sid = sc.student_id
	where
		sc.course_id = 002) b
on
	a.sid = b.sid;