目录
SQL语句
/*
Navicat Premium Data Transfer
Source Server : mico
Source Server Type : MySQL
Source Server Version : 50733
Source Host : 114.117.194.125:3306
Source Schema : mico
Target Server Type : MySQL
Target Server Version : 50733
File Encoding : 65001
Date: 19/11/2021 17:27:49
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for Teacher
-- ----------------------------
DROP TABLE IF EXISTS `Teacher`;
CREATE TABLE `Teacher` (
`TId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '老师ID',
`Tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '老师名称'
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of Teacher
-- ----------------------------
INSERT INTO `Teacher` VALUES ('01', '张三');
INSERT INTO `Teacher` VALUES ('02', '李四');
INSERT INTO `Teacher` VALUES ('03', '王五');
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for Student
-- ----------------------------
DROP TABLE IF EXISTS `Student`;
CREATE TABLE `Student` (
`SId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'ID',
`Sname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
`Sage` datetime(0) NULL DEFAULT NULL COMMENT '年龄',
`Ssex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别'
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of Student
-- ----------------------------
INSERT INTO `Student` VALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `Student` VALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `Student` VALUES ('03', '孙风', '1990-12-20 00:00:00', '男');
INSERT INTO `Student` VALUES ('04', '李云', '1990-12-06 00:00:00', '男');
INSERT INTO `Student` VALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `Student` VALUES ('06', '吴兰', '1992-01-01 00:00:00', '女');
INSERT INTO `Student` VALUES ('07', '郑竹', '1989-01-01 00:00:00', '女');
INSERT INTO `Student` VALUES ('09', '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `Student` VALUES ('10', '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `Student` VALUES ('11', '李四', '2012-06-06 00:00:00', '女');
INSERT INTO `Student` VALUES ('12', '赵六', '2013-06-13 00:00:00', '女');
INSERT INTO `Student` VALUES ('13', '孙七', '2014-06-01 00:00:00', '女');
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for SC
-- ----------------------------
DROP TABLE IF EXISTS `SC`;
CREATE TABLE `SC` (
`SId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`CId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`score` decimal(18, 1) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of SC
-- ----------------------------
INSERT INTO `SC` VALUES ('01', '01', 80.0);
INSERT INTO `SC` VALUES ('01', '02', 90.0);
INSERT INTO `SC` VALUES ('01', '03', 99.0);
INSERT INTO `SC` VALUES ('02', '01', 70.0);
INSERT INTO `SC` VALUES ('02', '02', 90.0);
INSERT INTO `SC` VALUES ('02', '03', 80.0);
INSERT INTO `SC` VALUES ('03', '01', 80.0);
INSERT INTO `SC` VALUES ('03', '02', 80.0);
INSERT INTO `SC` VALUES ('03', '03', 80.0);
INSERT INTO `SC` VALUES ('04', '01', 50.0);
INSERT INTO `SC` VALUES ('04', '02', 30.0);
INSERT INTO `SC` VALUES ('04', '03', 20.0);
INSERT INTO `SC` VALUES ('05', '01', 76.0);
INSERT INTO `SC` VALUES ('05', '02', 87.0);
INSERT INTO `SC` VALUES ('06', '01', 31.0);
INSERT INTO `SC` VALUES ('06', '03', 34.0);
INSERT INTO `SC` VALUES ('07', '02', 89.0);
INSERT INTO `SC` VALUES ('07', '03', 98.0);
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for Course
-- ----------------------------
DROP TABLE IF EXISTS `Course`;
CREATE TABLE `Course` (
`CId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目ID',
`Cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '科目名称',
`TId` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '老师id'
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of Course
-- ----------------------------
INSERT INTO `Course` VALUES ('01', '语文', '02');
INSERT INTO `Course` VALUES ('02', '数学', '01');
INSERT INTO `Course` VALUES ('03', '英语', '03');
SET FOREIGN_KEY_CHECKS = 1;
表结构说明
1.学生表:Student(SId,Sname,Sage,Ssex)
SId 学生编号
Sname 学生姓名
Sage 出生年月
Ssex 学生性别
2.课程表:Course(CId,Cname,TId)
CId 课程编号
Cname 课程名称
TId 教师编号
3. 教师表:Teacher(TId,Tname)
TId 教师编号
Tname 教师姓名
4.成绩表:SC(SId,CId,score)
SId 学生编号
CId 课程编号
score 分数
练习题
- 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT a.*,b.score as 语文,c.score as 数学
from Student a
LEFT JOIN SC b on a.SId=b.SId and b.CId='01'
LEFT JOIN SC c on a.SId=c.SId and c.CId='02'
WHERE b.score>c.score
- 查询同时存在" 01 "课程和" 02 "课程的情况
SELECT a.SId,a.score 01_score,b.score 02_score
FROM (SELECT * FROM SC WHERE CId = 01) AS a,(SELECT * FROM SC WHERE CId = 02) AS b
WHERE a.SId = b.SId;
- 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT S1.SId,S1.score 01_score,S2.score 02_score
from (SELECT * from SC WHERE SC.CId='01')as S1 LEFT JOIN (SELECT * from SC WHERE SC.CId='02')as S2
ON S1.SId=S2.SId
- 查询不存在" 01 "课程但存在" 02 "课程的情况
select a.*
from Student a
where a.SId in (select b.SId from SC b where b.CId = '01')
and a.SId not in (select c.SId from SC c where c.CId = '02')
- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select a.SId,a.Sname,avg(b.score) as 平均成绩
from Student a
left join SC b on a.SId = b.SId
group by a.SId
having avg(b.score) >= 60
- 查询在 SC 表存在成绩的学生信息
select a.SId,a.Sname,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄,a.Ssex as 性别
from Student a
RIGHT join SC b on a.SId = b.SId
group by a.SId
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT a.SId as 学生编号 ,a.Sname as 学生姓名 , COUNT(b.CId) as 选课总数,SUM(b.score) as 总成绩
FROM Student a
LEFT JOIN SC b on a.SId=b.SId
GROUP BY a.SId
- 查有成绩的学生信息
select a.SId,a.Sname,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄,a.Ssex as 性别,b.CId as 课程号,b.score as 成绩
from Student a
left join SC b on a.SId = b.SId
group by a.SId,b.CId
- 查询「李」姓老师的数量
select COUNT(TId) as 数量
FROM Teacher WHERE Tname like"李%";
- 查询学过「张三」老师授课的同学的信息
select d.SId,d.Sname,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄,d.Ssex as 性别
from Student d
where d.SId in (select c.SId
from SC c
where c.CId in (select b.CId
from Course b
where b.TId in (select a.TId
from Teacher a
where a.Tname = '张三')))
- 查询没有学全所有课程的同学的信息
select a.SId as 学号,a.Sname as 姓名,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄,a.Ssex as 性别,count(b.CId) as 已选课程总数,3 as 课程总数
FROM Student a
left JOIN SC b on a.SId=b.SId
GROUP BY a.SId
having count(b.CId) < (select count(CId) from Course)
- 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT a.SId,a.Sname,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄,a.Ssex as 性别
FROM
Student a
WHERE a.SId IN(
SELECT
DISTINCT b.SId
FROM SC b
WHERE
b.SId IN (
SELECT b.CId
FROM SC sc
WHERE sc.SId = '01'))
- 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select a.SId as 学号,a.Sname as 姓名,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄,a.Ssex as 性别
from Student a where a.SId in(
select SId from SC where SId != '01' and CId in
(select CId from SC where SId = '01')
group by SId
having count(CId)=(select count(CId) from SC where SId = '01'));
- 查询没学过"张三"老师讲授的任一门课程的学生姓名
select d.SId as 学号,d.Sname as 姓名,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄,d.Ssex as 性别
from Student d
where d.SId not in (select c.SId
from SC c
where c.CId in (select b.CId
from Course b
where b.TId in (select a.TId
from Teacher a
where a.Tname = '张三')))
- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select a.SId as 学号,a.Sname as 姓名,avg(b.score) 平均成绩
from Student a
left join SC b on a.SId = b.SId and b.score < 60
group by a.SId
having count(score) >= 2
- 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
select d.SId as 学号,d.Sname as 姓名,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄,d.Ssex as 性别,b.*
from Student d
LEFT JOIN SC b on d.SId=b.SId and b.CId='01' and b.score<60
order by b.score desc
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.SId,b.score as 语文,c.score as 数学,d.score as 英语,avg(a.score)as 平均成绩
from SC a
left join SC b on a.SId = b.SId and b.CId = '01'
left join SC c on a.SId = c.SId and c.CId = '02'
left join SC d on a.SId = d.SId and d.CId = '03'
group by a.SId
order by 平均成绩 desc
- 查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率。
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90。
select a.CId as 课程ID,b.Cname as 课程name,MAX(a.score) as 最高分,MIN(a.score) as 最低分,avg(a.score) as 平均分,
(1-(sum(case when a.score < 60 then 1 else 0 end)/sum(case when a.score then 1 else 0 end))) as 及格率,
(sum(case when a.score >= 70 and a. score < 80 then 1 else 0 end)/sum(case when a.score then 1 else 0 end)) as 中等率,
(sum(case when a.score >= 80 and a. score < 90 then 1 else 0 end)/sum(case when a.score then 1 else 0 end)) as 优良率,
(sum(case when a.score >= 90 then 1 else 0 end)/sum(case when a.score then 1 else 0 end)) as 优秀率
from SC a
left join Course b on a.CId = b.CId
group by a.CId
- 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT a.CId,COUNT(c.CId )
FROM SC a
LEFT JOIN Course c ON a.CId=c.CId
GROUP BY c.CId
ORDER BY COUNT(c.CId) DESC,a.CId ASC
- 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
select a.SId 学号,a.SId 课程号,c.Cname 课程名, a.score 分数, count(b.score)+1 as 排名
from SC a
left join SC b on a.score<b.score and a.CId = b.CId
LEFT JOIN Course c on c.CId=a.CId
group by a.CId ,a.SId,a.score
order by a.CId, 排名 ASC;
- 按各科成绩进行排序,并显示排名, Score 重复时合并名次
select a.sid 学号,a.cid 课程号,c.Cname 课程名, a.score 分数, count(b.score)+1 as 排名
from SC a
left join SC b on a.score<b.score and a.cid = b.cid
LEFT JOIN Course c on c.CId=a.CId
group by a.cid, a.sid,a.score
order by a.cid, 排名 ASC;
- 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
SELECT *,
(SELECT count(DISTINCT score) FROM SC AS b WHERE a.score<b.score)+1 AS 排名
FROM SC AS a ORDER BY 排名;
- 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
select a.sid, a.score, count(b.score)+1 as rank
from (
select sc.sid, sum(sc.score)score from SC sc group by sc.sid
) as a
left join (
select sid, sum(score)score from SC sc group by sc.sid
) as b
on a.score<b.score
group by a.sid,a.score
order by a.sid ,rank ASC;
- 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
select a.CId as 课程ID,b.Cname as 课程name,MAX(a.score) as 最高分,MIN(a.score) as 最低分,avg(a.score) as 平均分,
(1-(sum(case when a.score < 60 then 1 else 0 end)/sum(case when a.score then 1 else 0 end))) as 及格率,
(sum(case when a.score >= 60 and a. score < 70 then 1 else 0 end)/sum(case when a.score then 1 else 0 end)) as 中等率,
(sum(case when a.score >= 70 and a. score < 85 then 1 else 0 end)/sum(case when a.score then 1 else 0 end)) as 优良率,
(sum(case when a.score >= 85 and a. score < 100 then 1 else 0 end)/sum(case when a.score then 1 else 0 end)) as 优秀率
from SC a
left join Course b on a.CId = b.CId
group by a.CId
- 查询各科成绩前三名的记录
(SELECT * FROM SC a WHERE a.CId='01'ORDER BY a.score DESC LIMIT 3)
union all
(SELECT * FROM SC a WHERE a.CId='02'ORDER BY a.score DESC LIMIT 3)
union all
(SELECT * FROM SC a WHERE a.CId='03'ORDER BY a.score DESC LIMIT 3)
- 查询每门课程被选修的学生数
select a.CId AS 课程号,count(a.SId) AS 被选修的学生数
from SC a
group by a.CId
- 查询出只选修两门课程的学生学号和姓名
select b.SId as 学号,b.Sname as 姓名
from Student b
left join (select a.SId,count(*) as number
from SC a
group by a.SId) c
on b.SId = c.SId
where c.number = 2
- 查询男生、女生人数
select a.Ssex as 性别,count(a.Ssex) as 人数
from Student a
group by a.Ssex
- 查询名字中含有「风」字的学生信息
select d.SId as 学号,d.Sname as 姓名,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄,d.Ssex as 性别
from Student d
WHERE d.Sname like "%风%"
- 查询同名同性学生名单,并统计同名人数
select a.SId AS 学号,count(*) as 同名人数
from Student a
left join Student b on a.SId = b.SId and a.Sname = b.Sname and a.Ssex = b.Ssex
group by a.SId
- 查询 1990 年出生的学生名单
SELECT * FROM Student
WHERE YEAR(Sage)='1990'
- 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select a.CId as 课程号,avg(a.Score) as 平均分
from SC a
group by a.CId
order by avg(a.Score) desc,a.CId asc
- 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT a.SId 学号,a.Sname 姓名,AVG(b.score) 平均成绩
FROM Student a
left JOIN SC b on a.SId=b.SId
group by a.SId
having avg(b.score) >= 85
- 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
SELECT a.SId 学号,a.Sname 姓名,b.score as 分数
FROM Student a
left JOIN SC b on a.SId=b.SId
where b.CId in (select c.CId
from Course c
where c.Cname = '数学') and b.score < 60
- 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
select a.Sname as 姓名,b.score as 语文,c.score as 数学,d.score as 英语
from Student a
left join SC b on a.SId = b.SId and b.CId = '01'
left join SC c on a.SId = c.SId and c.CId = '02'
left join SC d on a.SId = d.SId and d.CId = '03'
- 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select a.Sname 姓名,c.Cname 课程名,b.score 成绩
from Student a
join SC b on a.SId = b.SId and b.score >= 70
join Course c on b.CId = c.CId
- 查询不及格的课程
SELECT a.SId 学生号,CId AS 未及格课程号
FROM Student a
LEFT JOIN SC b on a.SId=b.SId AND b.score<60
- 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT a.SId 学号,a.Sname 学生姓名,b.score as 成绩,c.CId 课程号,c.Cname 课程名 FROM
Student as a
INNER JOIN
SC as b on a.SId=b.SId
INNER JOIN
Course as c on b.CId=c.CId
WHERE c.CId='01' AND b.score>=80
- 求每门课程的学生人数
SELECT CId as 课程号,count(CId) as 学生人数
FROM SC
GROUP BY CId
- 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT Student.*,SC.score
FROM Course,SC, Teacher,Student
WHERE
Teacher.Tid = Course.Tid
AND Teacher.Tname= '张三'
AND Course.CId = SC.CId
AND Student.SId = SC.SId
AND SC.score IN (
SELECT
MAX( score )
FROM
Course,SC,Teacher,Student
WHERE
Teacher.TId = Course.TId
AND Teacher.Tname = '张三'
AND Course.CId = SC.CId
AND Student.SId = SC.SId
)
- 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select d.SId,d.Sname,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄,d.Ssex as 性别,max(e.score) as 最高成绩
from Student d,SC e
where d.SId in (select c.SId
from SC c
where c.CId in (select b.CId
from Course b
where b.TId in (select a.TId
from Teacher a
where a.Tname = '张三')))
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT st.SId,st.Sname,sc.CId,sc.score
FROM Student st
LEFT JOIN SC sc ON sc.SId=st.SId
LEFT JOIN Course c ON c.CId=sc.CId
WHERE (
SELECT
COUNT(1)
FROM Student st2
LEFT JOIN SC sc2 ON sc2.SId=st2.SId
LEFT JOIN SC c2 ON c2.CId=sc2.CId
WHERE sc.score=sc2.score AND c.CId!=c2.CId
)>1
- 查询每门功成绩最好的前两名
SELECT s.CId 课程号,s.score 成绩
FROM SC s WHERE (
SELECT COUNT(*) FROM SC b WHERE s.CId = b.CId AND s.score<b.score
)<2 ORDER BY s.CId ASC,s.score DESC;
- 统计每门课程的学生选修人数(超过 5 人的课程才统计)。
SELECT sc.CId AS 课程号,COUNT(c.CId)
FROM SC sc
LEFT JOIN Course c ON c.CId=sc.CId
GROUP BY c.CId
HAVING COUNT(c.CId)>5
ORDER BY COUNT(c.CId) DESC,sc.CId ASC
- 检索至少选修两门课程的学生学号
select a.SId
from SC a
group by a.SId
having count(a.CId) >= 2
- 查询选修了全部课程的学生信息
select a.*
from Student a
LEFT JOIN SC b on a.SId=b.SId
group by a.SId
having count(*) = (select count(*)from Course b)
- 查询各学生的年龄,只按年份来算
SELECT Sname,Sage AS 出生日期,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(Sage,'%Y') -
(case when DATE_FORMAT(NOW(),'%m%d') < DATE_FORMAT(Sage,'%m%d') then 0 else 0 end)) as age
from Student
- 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
SELECT Sname,Sage AS 出生日期,TIMESTAMPDIFF(YEAR,Sage,CURDATE()) as 年龄
from Student
- 查询本周过生日的学生
select *
from Student
where week(Sage) = week(date_format(now(),'%Y%m%d'))
- 查询下周过生日的学生
select *
from Student
where week(Sage) = week(date_format(now(),'%Y%m%d'))+1
- 查询本月过生日的学生
select *
from Student
where month(Sage) = month(date_format(now(),'%Y%m%d'))
- 查询下月过生日的学生
select *
from Student
where month(Sage ) = month(date_format(now(),'%Y%m%d'))+1