建筑网官方网站,网页设计服务,布展设计收费标准,网站描述在哪里写文章目录 前言一、SQL脚本二、实操以及实现思路 前言
续上篇博主MySQL之视图索引执行计划这篇给大家讲解MySQL之子查询、连接查询(内外)以及分页查询
一、SQL脚本
/*Navicat Premium Data TransferSource Server : localhostSource Server Type :… 文章目录 前言一、SQL脚本二、实操以及实现思路 前言
续上篇博主MySQL之视图索引执行计划这篇给大家讲解MySQL之子查询、连接查询(内外)以及分页查询
一、SQL脚本
/*Navicat Premium Data TransferSource Server : localhostSource Server Type : MySQLSource Server Version : 80018Source Host : localhost:3306Source Schema : mybatis_ssmTarget Server Type : MySQLTarget Server Version : 80018File Encoding : 65001Date: 04/07/2023 23:53:33
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS 0;-- ----------------------------
-- Table structure for t_mysql_course
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_course;
CREATE TABLE t_mysql_course (cid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 课程编号,cname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 课程名称,tid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 教师编号,PRIMARY KEY (cid) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 课程信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_course
-- ----------------------------
INSERT INTO t_mysql_course VALUES (01, 语文, 02);
INSERT INTO t_mysql_course VALUES (02, 数学, 01);
INSERT INTO t_mysql_course VALUES (03, 英语, 03);-- ----------------------------
-- Table structure for t_mysql_score
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_score;
CREATE TABLE t_mysql_score (sid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生编号外键,cid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 课程编号外键,score float NULL DEFAULT 0 COMMENT 成绩,INDEX sid(sid) USING BTREE,INDEX cid(cid) USING BTREE,CONSTRAINT t_mysql_score_ibfk_1 FOREIGN KEY (sid) REFERENCES t_mysql_student (sid) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT t_mysql_score_ibfk_2 FOREIGN KEY (cid) REFERENCES t_mysql_course (cid) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 成绩信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_score
-- ----------------------------
INSERT INTO t_mysql_score VALUES (01, 01, 80);
INSERT INTO t_mysql_score VALUES (01, 02, 90);
INSERT INTO t_mysql_score VALUES (01, 03, 99);
INSERT INTO t_mysql_score VALUES (02, 01, 70);
INSERT INTO t_mysql_score VALUES (02, 02, 60);
INSERT INTO t_mysql_score VALUES (02, 03, 80);
INSERT INTO t_mysql_score VALUES (03, 01, 80);
INSERT INTO t_mysql_score VALUES (03, 02, 80);
INSERT INTO t_mysql_score VALUES (03, 03, 80);
INSERT INTO t_mysql_score VALUES (04, 01, 50);
INSERT INTO t_mysql_score VALUES (04, 02, 30);
INSERT INTO t_mysql_score VALUES (04, 03, 20);
INSERT INTO t_mysql_score VALUES (05, 01, 76);
INSERT INTO t_mysql_score VALUES (05, 02, 87);
INSERT INTO t_mysql_score VALUES (06, 01, 31);
INSERT INTO t_mysql_score VALUES (06, 03, 34);
INSERT INTO t_mysql_score VALUES (07, 02, 89);
INSERT INTO t_mysql_score VALUES (07, 03, 98);-- ----------------------------
-- Table structure for t_mysql_student
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_student;
CREATE TABLE t_mysql_student (sid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生编号,sname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生名称,sage varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生年龄,ssex varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 学生性别,PRIMARY KEY (sid) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 学生信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_student
-- ----------------------------
INSERT INTO t_mysql_student VALUES (01, 赵雷, 1990-01-01, 男);
INSERT INTO t_mysql_student VALUES (02, 钱电, 1990-12-21, 男);
INSERT INTO t_mysql_student VALUES (03, 孙风, 1990-12-20, 男);
INSERT INTO t_mysql_student VALUES (04, 李云, 1990-12-06, 男);
INSERT INTO t_mysql_student VALUES (05, 周梅, 1991-12-01, 女);
INSERT INTO t_mysql_student VALUES (06, 吴兰, 1992-01-01, 女);
INSERT INTO t_mysql_student VALUES (07, 郑竹, 1989-01-01, 女);
INSERT INTO t_mysql_student VALUES (09, 张三, 2017-12-20, 女);
INSERT INTO t_mysql_student VALUES (10, 李四, 2017-12-25, 女);
INSERT INTO t_mysql_student VALUES (11, 李四, 2012-06-06, 女);
INSERT INTO t_mysql_student VALUES (12, 赵六, 2013-06-13, 女);
INSERT INTO t_mysql_student VALUES (13, 孙七, 2014-06-01, 女);-- ----------------------------
-- Table structure for t_mysql_teacher
-- ----------------------------
DROP TABLE IF EXISTS t_mysql_teacher;
CREATE TABLE t_mysql_teacher (tid varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 教师编号,tname varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 教师名称,PRIMARY KEY (tid) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 教师信息表 ROW_FORMAT Dynamic;-- ----------------------------
-- Records of t_mysql_teacher
-- ----------------------------
INSERT INTO t_mysql_teacher VALUES (01, 张三);
INSERT INTO t_mysql_teacher VALUES (02, 李四);
INSERT INTO t_mysql_teacher VALUES (03, 王五);SET FOREIGN_KEY_CHECKS 1;二、实操以及实现思路 09查询学过「张三」老师授课的同学的信息 SELECTs.*,c.cname,t.tname,sc.score
FROMt_mysql_teacher t,t_mysql_course c,t_mysql_student s,t_mysql_score sc
WHEREt.tid c.tid AND c.cid sc.cid AND sc.sid s.sid AND t.tname 张三10查询没有学全所有课程的同学的信息 分析没有学全学全了有多少门统计一共有多少门学科 统计每一个学生学了多少门 SELECTs.sid,s.sname,count( sc.score ) n
FROMt_mysql_student sLEFT JOIN t_mysql_score sc ON s.sid sc.sid
GROUP BYs.sid,s.sname
HAVINGn (SELECTcount( 1 ) FROMt_mysql_course) 11查询没学过张三老师讲授的任一门课程的学生姓名 分析子查询 张三教了哪些课程 找学过张三的课程有哪些人 SELECTs.sid,s.sname
FROMt_mysql_score sc,t_mysql_student s
WHEREs.sid sc.sid AND sc.cid NOT IN ( SELECT cid FROM t_mysql_course c, t_mysql_teacher t WHERE c.tid t.tid AND t.tname 张三 )
GROUP BYs.sid,s.sname12查询两门及其以上不及格课程的同学的学号姓名及其平均成绩 分析聚合函数 内连接 要先筛选再分组 SELECTs.sid,s.sname,avg( sc.score )
FROMt_mysql_student s,t_mysql_score sc
WHEREs.sid sc.sid AND sc.score 60
GROUP BYs.sid,s.sname13检索 01 课程分数小于 60按分数降序排列的学生信息 SELECTs.*,sc.score
FROMt_mysql_student s,t_mysql_score sc
WHEREs.sid sc.sid AND sc.cid 01 AND sc.score 60
ORDER BYsc.score DESC14按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 分析聚合函数 GROUP BY 从高到低 ORDER BY DESC 所有学生的所有课程的成绩 姓名 语文 数学 英语 平均成绩 SELECTs.sid,s.sname,sum((case when sc.cid01 then sc.score end))语文,sum((case when sc.cid02 then sc.score end))数学,sum((case when sc.cid03 then sc.score END))英语,round(avg(sc.score),2) 平均分FROMt_mysql_score scRIGHT JOIN t_mysql_student s ON sc.sid s.sid
GROUP BYs.sid,s.snameSELECTs.sid,s.sname,sum(if(sc.cid01,sc.score,0))语文,sum(if(sc.cid02,sc.score,0))数学,sum(if(sc.cid03,sc.score,0))英语,round(avg(sc.score),2) 平均分FROMt_mysql_score scRIGHT JOIN t_mysql_student s ON sc.sid s.sid
GROUP BYs.sid,s.sname15查询各科成绩最高分、最低分和平均分 以如下形式显示课程 ID课程 name最高分最低分平均分及格率中等率优良率优秀率及格为60中等为70-80优良为80-90优秀为90 要求输出课程号和选修人数查询结果按人数降序排列若人数相同按课程号升序排列 分析聚合函数 GROUP BY 大量使用函数 SELECTc.cid,c.cname,count(sc.score) 人数,max(sc.score) 最高分,min(sc.score) 最低分,round(avg(sc.score),2) 平均分,concat(round(sum(if(sc.score60,1,0))/(select count(1) from t_mysql_student)*100,2),%) 及格率,concat(round(sum(if(sc.score70 and sc.score80,1,0))/(select count(1) from t_mysql_student)*100,2),%) 中等率,concat(round(sum(if(sc.score80 and sc.score90,1,0))/(select count(1) from t_mysql_student)*100,2),%) 优良率,concat(round(sum(if(sc.score90,1,0))/(select count(1) from t_mysql_student)*100,2),%) 优秀率
FROMt_mysql_score scLEFT JOIN t_mysql_course c ON sc.cid c.cid
group by
c.cid,c.cname
