目录
1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
3.查询存在" 01 “课程但可能不存在” 02 (不存在时显示为 null )
5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
12. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
13. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
14. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
15. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
前言:MySQL在面试中主要是以题目的方式出现,为此我精选了15题来进行拿捏它!!!
一,delete、drop和truncate区别
delete、DROP和truncate,详解如下:
1.delete:
功能:delete 用于从表中删除一条或多条数据。
影响:delete 操作是 DML(数据操作语言)语句,它会触发触发器、产生回滚日志,且可以使用 WHERE 子句对要删除的数据进行筛选。
回滚:delete 操作可以通过事务回滚来撤消已删除的行。
返回值:delete 操作在执行时会返回删除的行数。
2.drop:功能:drop 用于删除整个数据库对象(如表、视图、索引等)。
影响:drop 操作是 DDL(数据定义语言)语句,它会永久性地从数据库中删除对象。
回滚:drop 操作不可回滚,一旦执行成功,对象将被永久性删除。
返回值:drop 操作不返回任何值。
3.truncate:功能:truncate 用于删除表中的所有数据。
影响:truncate 操作是 DDL 语句,它会将表完全清空,并且比 DELETE 操作更快,因为它不会触发触发器、不写回滚日志,并且不使用 WHERE 子句进行筛选。
回滚:truncate 操作不可回滚,一旦执行成功,数据将被永久性删除。
返回值:truncate 操作在执行时不返回删除的行数。
总的来说:效率问题是drop>truncate>delete
二,关于行转列的知识
行转列是一种重塑或重新组织数据的操作,将原先以行的形式存储的数据转换为以列的形式存储。行转列的技术可以使用在各种领域,如数据分析、报表生成等。
三.面试题题目+详解
1.建立数据库
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80018
Source Host : localhost:3306
Source Schema : mybatis_ssm
Target Server Type : MySQL
Target Server Version : 80018
File Encoding : 65001
Date: 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;
2.按要求建立数据表
-- 1.学生表-t_mysql_student
-- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别-- 2.教师表-t_mysql_teacher
-- tid 教师编号,tname 教师名称-- 3.课程表-t_mysql_course
-- cid 课程编号,cname 课程名称,tid 教师名称-- 4.成绩表-t_mysql_score
-- sid 学生编号,cid 课程编号,score 成绩
学生表:
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;
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' , '女');
成绩表:
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);
教师表:
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', '王五');
课程表:
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');
1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
分析:分别获取01课程(t1)和02的课程(t2)的成绩表的信息
运行结果:
代码:
SELECT
st.*,
s1.score '01课程',
s2.score '02课程'
FROM
( SELECT * FROM t_mysql_score WHERE cid = "01" ) s1,
( SELECT * FROM t_mysql_score WHERE cid = "02" ) s2,
t_mysql_student st
WHERE
s1.sid = s2.sid
AND s1.sid = st.sid
AND s1.score > s2.score
2.查询同时存在" 01 "课程和" 02 "课程的情况
分析:获取01课程(t1)和02的课程(t2)的成绩表的信息
运行结果:
代码:
SELECT
t3.* ,
(CASE WHEN t1.cid='01' THEN t1.score END) 语文,
(CASE WHEN t1.cid='01' THEN t1.score END) 数学
FROM
( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1,-- 有01的sid'
( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2, -- 有02的sid'
t_mysql_student t3
WHERE
t1.sid = t2.sid
AND t1.sid = t3.sid
3.查询存在" 01 “课程但可能不存在” 02 (不存在时显示为 null )
分析:先得使用左外连接把01课程保存,在将有02课的保存下来。
运行结果:
代码:
SELECT
t1.*,
t2.cid 数学 ,
t2.score 02成绩
FROM
( SELECT * FROM t_mysql_score WHERE cid = '01' ) t1
LEFT JOIN ( SELECT * FROM t_mysql_score WHERE cid = '02' ) t2 ON t1.sid = t2.sid
4.查询不存在" 01 “课程但存在” 02 "课程的情况
分析:先获取到01课程的学生的sdi,再查询02课程的学生信息,使用not in并且不包含01课程的sid
运行结果:
代码:
SELECT
*
FROM
t_mysql_score
WHERE
sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' )
AND cid = '02'
5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
分析:计算各个学生的平均成绩,即AVG(sc.score),在筛选出大于等于 60 分的同学,即HAVING AVG(sc.score)>=60
运行结果:
代码:
SELECT
t3.sid,
t3.sname,
ROUND( AVG( t1.score ), 2 )
FROM
( SELECT * FROM t_mysql_score sc ) t1,
t_mysql_student t3
WHERE
t1.sid = t3.sid
GROUP BY
t3.sid,
t3.sname
6.查询在t_mysql_score表存在成绩的学生信息
分析:根据成绩表中有sid查询学生表sid
运行结果:
代码:
SELECT
t3.*
FROM
( SELECT * FROM t_mysql_score sc) t1,
t_mysql_student t3
where t1.sid = t3.sid
GROUP BY t3.sid ,t3.sname
7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
分析:通过左外连接连接学生表,成绩表,再将连表查询后的作为一个新表来查询,再根据sid、sname分组(GROUP BY t1.sid,t1.sname)
运行结果:
代码:
SELECT
t1.sid,
t1.sname,
COUNT( t2.cid ),
sum( t2.score )
FROM
( SELECT * FROM t_mysql_student ) t1
LEFT JOIN ( SELECT * FROM t_mysql_score ) t2 ON t1.sid = t2.sid
GROUP BY
t1.sid,
t1.sname
8..查询「李」姓老师的数量
分析:查询t_mysql_teacher表,筛选name姓李的老师,即(WHERE tname like ‘李%’)使用聚合count(*)
运行结果:
代码:
SELECT * FROM t_mysql_teacher sc WHERE sc.tname like '李%';
9.查询学过「张三」老师授课的同学的信息
分析:先根据「张三」老师的编号获取到教的相应课程,即(t.tid=c.tid)再通过课程的编号获取对应课程的成绩信息,即(c.cid=sc.cid) 再通过对应课程的成绩的学生编号获取对应学生的信息
运行结果:
代码:
SELECT
s.*,c.cid,c.cname,t.tname
FROM
t_mysql_teacher t,
t_mysql_course c,
t_mysql_student s,
t_mysql_score sc
WHERE
t.tid = c.tid AND
c.cid = sc.cid AND
sc.sid = s.sid AND
t.tname = '张三'
GROUP BY
s.sid,
s.sname,c.cid,c.cname,t.tname
10. 查询没有学全所有课程的同学的信息
分析:根据学生姓名和学生编号分组(s.sid、s.sname),再筛选出选课总数少于课程总数的学生,即(HAVING COUNT( sc.score ) < ( SELECT count( 1 ) FROM t_mysql_course ))
运行结果:
代码:
SELECT
s.sid,
s.sname,
COUNT( sc.score )
FROM
t_mysql_score sc,
t_mysql_student s
WHERE
sc.sid = s.sid
GROUP BY
s.sid,
s.sname
HAVING
COUNT( sc.score ) < ( SELECT count( 1 ) FROM t_mysql_course )
11.查询没学过"张三"老师讲授的任一门课程的学生姓名
分析:先根据张三老师的编号获取到所教课程的编号,再根据所教课程的课程编号在成绩表中获取到所教学生编号,在学生信息表中筛选出不是张三老师的学生
运行结果:
代码:
SELECT
s.sname
FROM
t_mysql_student s
WHERE
sid NOT IN (
SELECT
sc.sid
FROM
t_mysql_score sc,
t_mysql_teacher t,
t_mysql_course c
WHERE
t.tid = c.tid
AND c.cid = sc.cid
AND t.tname = '张三'
GROUP BY
sc.sid
)
12. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
分析:先筛选出各学生不及格的记录,再分组得到存在科目不及格的学生对应科目数,最后得出不及格课程数大于等于二的学生信息即平均成绩
运行结果:
代码:
SELECT
s.sid,s.sname,count(sc.score) n,ROUND(AVG(sc.score),2)
FROM
t_mysql_score sc,
t_mysql_student s
WHERE
sc.sid=s.sid
and sc.score<60
GROUP BY
s.sid,s.sname
HAVING
n>=2
13. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
分析:先查询出01课程成绩小于60分的学生信息,再通过desc进行降序排序
运行结果:
代码:
SELECT
s.*,sc.score
FROM
t_mysql_score sc,
t_mysql_student s
WHERE
sc.sid=s.sid
and sc.score<60
and sc.cid='01'
ORDER BY
sc.score desc
14. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
分析:计算出所有学生的平均成绩和查询出所有学生所有课程的成绩,通过平均成绩从高到低排序
运行结果:
代码:
SELECT
s.sid,s.sname,ROUND(AVG(sc.score),2) avgNum,
max( CASE WHEN sc.cid = '01' THEN sc.score END ) 语文,
max( CASE WHEN sc.cid = '02' THEN sc.score END ) 数学,
max( CASE WHEN sc.cid = '03' THEN sc.score END ) 英语
FROM
t_mysql_score sc,
t_mysql_student s
WHERE
s.sid=sc.sid
GROUP BY
s.sid,s.sname
ORDER BY avgNum desc
15. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
分析:使用课程表成绩表,按分数进行分组,算及格人数,中等人数,优良人数,优秀人数
运行结果:
代码:
SELECT
c.cid,c.cname,MAX(sc.score) 最高分,min(sc.score) 最低分,ROUND(avg(sc.score),2) 平均分,
ROUND(sum(if(sc.score >=60,1,0))/count(sc.score)*100,2) 及格率,
ROUND(sum(if(sc.score >=70 and sc.score<80,1,0))/count(sc.score)*100,2) 中等率,
ROUND(sum(if(sc.score >=80 and sc.score<90,1,0))/count(sc.score)*100,2) 优良率,
ROUND(sum(if(sc.score >=90 and sc.score<=100,1,0))/count(sc.score)*100,2) 优秀率
from
t_mysql_score sc,
t_mysql_course c,
t_mysql_student s
WHERE
sc.cid=c.cid and sc.sid=s.sid
GROUP BY c.cid,c.cname
希望这篇博客能够帮助到正在学习工作的你!!!