MySQL50道典型案例

最新推荐文章于 2024-03-27 21:53:42 发布
最新推荐文章于 2024-03-27 21:53:42 发布
阅读量1.7k 收藏 28
点赞数 1
分类专栏： 笔记
原文链接：https://blog.csdn.net/weixin_41099058/article/details/115015691
版权

MySQL经典题50道

建表前准备工作 解决this is incompatible with sql_mode=only_full_group_by问题
找到MySQL配置文件 将下列代码加入其中 保存后重启MySQL服务 即可解决
代码 	sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

学生表建表

create table Student(
SId varchar(10),
Sname varchar(10),
Sage datetime,
Ssex varchar(10)
);

课程表建表

create table Course(
CId varchar(10),
Cname nvarchar(10),
TId varchar(10)
);

教师表建表

create table Teacher(
TId varchar(10),
Tname varchar(10)
);

成绩表建表

create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)
);

四表添加数据

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
1 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select * from Student RIGHT JOIN (
	select t1.SId, cl1, cl2 from
		(select SId, score as cl1 from sc where sc.CId = '01')as t1, 
		(select SId, score as cl2 from sc where sc.CId = '02')as t2
where t1.SId = t2.SId AND t1.cl1 > t2.cl2
)c on Student.SId = c.SId;

在这里插入图片描述

1.1查询同时存在" 01 “课程和” 02 "课程的情况
select * from 
(select * from sc where sc.CId = '01') as t1, 
(select * from sc where sc.CId = '02') as t2
where t1.SId = t2.SId;

在这里插入图片描述

1.2查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
select * from 
	(select * from sc where sc.CId = '02') as t2
right join 
	(select * from sc where sc.CId = '01') as t1
on t1.SId = t2.SId;

在这里插入图片描述

1.3查询不存在" 01 “课程但存在” 02 "课程的情况
select * from sc
where sc.SId not in 
	(select SId from sc where sc.CId = '01') 
AND sc.CId= '02';

在这里插入图片描述

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.sid,s.sname,p.avg from 
Student s,(select sid,avg(score) avg from SC GROUP BY sid) p
where s.sid=p.sid and p.avg>=60;

在这里插入图片描述

3. 查询在 SC 表存在成绩的学生信息
SELECT DISTINCT student.*
	FROM student,sc WHERE student.SID = sc.SID;

在这里插入图片描述

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select student.sid, student.sname,c.coursenumber,c.scoresum
from student,
(select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc 
group by sc.sid)c
where student.sid = c.sid;

在这里插入图片描述

4.1查有成绩的学生信息
select * from student 
where exists (select sc.sid from sc where student.sid = sc.sid);

在这里插入图片描述

5. 查询「李」姓老师的数量
select count(*) from teacher where tname like '李%';

在这里插入图片描述

6. 查询学过「张三」老师授课的同学的信息
select student.* from student,teacher,course,sc
where student.sid = sc.sid and course.cid=sc.cid and course.tid = teacher.tid and tname = '张三';

在这里插入图片描述

7. 查询没有学全所有课程的同学的信息
select * from student
where student.sid not in (
select sc.sid from sc
group by sc.sid 
having count(sc.cid)= (select count(cid) from course)
);

在这里插入图片描述

8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from student 
	where student.sid in (
			select sc.sid from sc 
				where sc.cid in(
					select sc.cid from sc 
						where sc.sid = '01'
    )
);

在这里插入图片描述

9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
	group_concat() 函数作用：将组中的字符串连接成为具有各种选项的单个字符串。

select * from student
where sid in (
    select sid from sc
			group by sid
				having group_concat(cid ORDER BY cid) = (
					select group_concat(cid ORDER BY cid)
						from sc where sid = '01') and sid != '01');

在这里插入图片描述

10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student
where student.sid not in(
    select sc.sid from sc,course,teacher 
    where sc.cid = course.cid
        and course.tid = teacher.tid
        and teacher.tname= "张三"
);

在这里插入图片描述

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.sid, student.sname, AVG(sc.score) from student,sc
where student.sid = sc.sid and sc.score<60
group by sc.sid 
having count(*)>1;

在这里插入图片描述

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select student.*, sc.score from student, sc
where student.sid = sc.sid and sc.score < 60 and cid = "01"
ORDER BY sc.score DESC;

在这里插入图片描述

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select *  from sc 
left join (
    select sid,avg(score) as avscore from sc 
    group by sid
    )c 
on sc.sid = c.sid
order by avscore desc;

在这里插入图片描述

14.查询各科成绩最高分、最低分和平均分：
#以如下形式显示：课程 ID，课程 name，最高分，最低分，平均	分，及格率，中等率，优良率，优秀率
#及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
#要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select sc.CId ,max(sc.score)as 最高分,min(sc.score)as 最低分,AVG(sc.score)as 平均分,count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率 
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC;

在这里插入图片描述

15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select a.cid, a.sid, a.score, count(b.score)+1 as rank1
from sc as a 
left join sc as b 
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank1 ASC;

在这里插入图片描述

#自定义变量： 申明变量：SET @crank =0;  对变量进行赋值：@crank := @crank +1，赋值操作符   =或:=  使用：查找，比较  运算等
15.1按各科成绩进行排序，并显示排名， Score 重复时合并名次
select a.* ,count(b.score)+1 rank1 from sc a left join sc b
on a.cid = b.cid and (a.score < b.score or (a.score = b.score and a.sid > b.sid))
group by a.cid,a.sid
order by a.cid,count(b.score)

在这里插入图片描述

16查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SET @crank =0;
SELECT b.studentid, b.a, @crank := @crank +1 AS rank FROM
 (SELECT studentid,SUM(score) AS a FROM student_score GROUP BY studentid ORDER BY a DESC)b ;
17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
分数段所占百分比=分数段人数/总人数

select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from sc left join course
on sc.cid = course.cid
group by sc.cid;

在这里插入图片描述

18.查询各科成绩前三名的记录
统计比自身小的行数有多少行
< 3 也就是取前三

select * from sc where (
select count(*) from sc as a 
where sc.cid = a.cid and sc.score<a.score 
)< 3
order by cid asc, sc.score desc

在这里插入图片描述

19.查询每门课程被选修的学生数
select cid, count(sid) from sc group by cid;

在这里插入图片描述

20.查询出只选修两门课程的学生学号和姓名
通过sid锁定学生信息
通过sc表进行sid分组查询限制选课科数位2

select student.sid, student.sname from student
where student.sid in
(select sc.sid from sc
group by sc.sid having count(sc.cid)=2
);

在这里插入图片描述

21.查询男生、女生人数
select ssex, count(*) from student group by ssex;

在这里插入图片描述

22.查询名字中含有「风」字的学生信息
select * from student where student.Sname like '%风%';

在这里插入图片描述

23.查询同名同性学生名单，并统计同名人数
查找相同姓名，相同性别的学生，只需要按照姓名，性别进行分组，把相同的人数筛选统计出来

select sname, count(*) from student
group by sname having count(*)>1;

在这里插入图片描述

24.查询 1990 年出生的学生名单
select * from student where YEAR(student.Sage)=1990;

在这里插入图片描述

25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select sc.cid, course.cname, AVG(SC.SCORE) as average from sc, course
where sc.cid = course.cid group by sc.cid 
order by average desc,cid asc;

在这里插入图片描述

26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select student.sid, student.sname, AVG(sc.score) as aver from student, sc
where student.sid = sc.sid
group by sc.sid
having aver > 85;

在这里插入图片描述

27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select student.sname, sc.score from student, sc, course
where student.sid = sc.sid and course.cid = sc.cid and course.cname = "数学" and sc.score < 60;

在这里插入图片描述

28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select student.sname, cid, score from student
left join sc on student.sid = sc.sid;

在这里插入图片描述

29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select student.sname, course.cname,sc.score from student,course,sc
where sc.score>70 and student.sid = sc.sid and sc.cid = course.cid;

在这里插入图片描述

30.查询不及格的课程
select cid from sc where score< 60
group by cid;
select DISTINCT sc.CId from sc where sc.score <60;

在这里插入图片描述

31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select student.sid,student.sname from student,sc
where cid="01" and score>=80 and student.sid = sc.sid;

在这里插入图片描述

32.求每门课程的学生人数
select sc.CId,count(*) as 学生人数
from sc GROUP BY sc.CId;

在这里插入图片描述

33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
得先知道张三教的课程cid，要获取cid则需要通过name进行判断
接下来限制条件便是cid in 张三老师的cid就可以了

select student.*, sc.score, sc.cid from student, teacher, course,sc 
where teacher.tid = course.tid and sc.sid = student.sid and sc.cid = course.cid
	and teacher.tname = "张三" and sc.score = (
    select Max(sc.score)  from sc,student, teacher, course
    where teacher.tid = course.tid and sc.sid = student.sid and sc.cid = course.cid
    and teacher.tname = "张三"
);

在这里插入图片描述

34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
需要改题
UPDATE sc SET score=90
where sid = "05"
and cid ="02";

代码同33题

36.查询每门功成绩最好的前两名
select a.sid,a.cid,a.score from sc as a 
left join sc as b 
on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<2
order by a.cid;

在这里插入图片描述

37.统计每门课程的学生选修人数（超过 5 人的课程才统计）
select sc.cid, count(sid) as sl from sc
group by cid having sl >5;

在这里插入图片描述

38.检索至少选修两门课程的学生学号
select sid, count(cid) as sl from sc
group by sid having sl>=2;

在这里插入图片描述

39.查询选修了全部课程的学生信息
select * from student
where sid in (
    select sid from sc
			group by sid
				having group_concat(cid ORDER BY cid) = (
					select group_concat(cid ORDER BY cid)
						from sc where sid = '01') and sid != '01');

在这里插入图片描述

40.查询各学生的年龄，只按年份来算
select student.* from sc ,student 
where sc.SId=student.SId
GROUP BY sc.SId HAVING count(*) = (select DISTINCT count(*) from course );

在这里插入图片描述

41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select student.SId as 学生编号,student.Sname  as  学生姓名,
TIMESTAMPDIFF(YEAR,student.Sage,NOW()) as 学生年龄 from student;

在这里插入图片描述

42.查询本周过生日的学生
select * from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(NOW());
43.查询下周过生日的学生
select * from student 
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(NOW())+1;
44.查询本月过生日的学生 无4月
select * from student 
where MONTH(student.Sage)=MONTH(NOW());

在这里插入图片描述

45.查询下月过生日的学生 改题 下个月
select * from student 
where MONTH(student.Sage)=MONTH(NOW())+1;

在这里插入图片描述

津_轻
关注
  • 1
    点赞
  • 踩
  • 28
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql学生表经典案例50题
weixin_39345735的博客
04-03 3243
一、建库、建表 create database alibaba; use alibaba; – 建表 create table student(s_id varchar(10),s_name varchar(10),s_age date,s_sex varchar(10)) engine=InnoDB default charset utf8; insert into student(s_id,s...
MySQL 操作练习
qq_43784516的博客
07-09 1553
MySQL 查询相关练习
数据查询sql习题综合二
Enduranc_e的博客
04-18 1万+
2.统计各门课程选修人数，要求输出课程代号，课程名，有成绩人数（grade不为NULL），最高分，最低分，平均分(取整round函数)，按课程号排序。27.有课程表，学生表，成绩表如下，请完成查询，查询学生成绩单，要求输出有有效成绩的学号，姓名，课程名，成绩，按学号升序课程名降序排列。6.查询学生的选课情况，要求输出学号，姓名，课程门数，课程名列表（按课程名升序排列，用下划线_分隔），按照学号升序排序。14.查询每门课程被选修的情况，输出课程号，课程名，被选修次数，结果按选修次数降序课程号降序排列。
Mysql实用SQL例子
最新发布
qq_38328148的博客
03-27 556
查询一天内登陆两次以上的用户。创建登陆记录表(login)
Mysql综合案例一
尽欢的博客
04-06 950
DDL练习、DML练习、DQL练习
MySQL基础(八)-实战演练经典50题
five小点心的博客
01-10 4383
MySQL经典50题
mysql单表查询案例精选
01-06
MySQL 单表查询是数据库操作的基础，通过案例学习可以更好地理解和掌握其用法。下面将对给定的案例进行详细解读，以便理解 MySQL 单表查询的各种常见操作。 1. **查询特定班级成绩信息**： ```sql SELECT * from ...
php+mysql 经典案例分析
09-30
php+mysql经典案例剖析_最新版，项目实战案例，适合编程入门提高
淘宝大牛传授MySQL十大经典案例
03-02
1.InnoDB表如何设计主键索引 2.字符串索引隐式转换 3.表数据被莫名清空 4.InnoDB表更新锁问题 5.客户端连接被中断 6.核心数据库被同时关闭 7.Slave事件Loop 8.Slave更新操作找不到对应记录 9.备库设置read_only被...
PHP MYSQL DreamWeaver网站建设经典案例.rar
07-09
包含了php与mysql实际应用的12个经典案例
淘宝MySQL十大经典案例 MSQL数据库应用案例 共61页.pdf
06-09
淘宝MySQL十大经典案例 本资源摘要信息将对淘宝MySQL十大经典案例进行详细的知识点汇总，涵盖MySQL数据库应用案例、性能测试、高可用运维、服务器优化、成本节省、数据库设计、SQL相关、客户端相关、Slave相关、...
mysql经典50题
04-01
mysql经典50题。以及注释，经过实战运行及改善，可以直接用。挺有用的，对于sql入门及网申笔试来说。
MySQL 45道基础题
m0_64622390的博客
08-12 188
45道基础MySQL练习题
常用的学生、课程、成绩、教师表的查询
wxg6203的专栏
09-10 795
Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表   create table Student(S# varchar(20),Sname varchar(10),Sage int,Ssex varchar(2)) 前面加一列序号： ...
MySQL-50道经典sql题（完整版）
溺水小鸡_的博客
06-15 1649
mysql经典50题
mysql查询的子级在父级下面排列_MySQL基础篇(01)：经典实用查询案例，总结整理...
weixin_39716105的博客
12-15 323
一、连接查询图解示意图1、建表语句部门和员工关系表：CREATE TABLE `tb_dept` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `deptName` varchar(30) DEFAULT NULL COMMENT '部门名称', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUT...
mysql模拟题三_mysql经典45题（3刷）
weixin_35715235的博客
02-04 2337
Mysql 经典练习题我使用的Mysql版本是5.7.27-log，答案可能会因版本会有少许出入。一、数据源准备及说明：1.1、数据字段说明:1.学生表 Student(SId,Sname,Sage,Ssex)SId ：学生编号Sname：学生姓名Sage ：出生年月Ssex：学生性别2.课程表 Course(CId,Cname,TId)CId ：课程编号Cname ：课程名称TId ：教师编号3...
MySQL经典四表查询(教师，学生，成绩，课程表)多表查询
热门推荐
hank.ll的博客
06-15 3万+
我用的工具：MySQL5.7+SQLyog-11.2.5-0 1、表架构呈现 student(sid,sname,sage,ssex) 学生表 teacher(tid,tname) 教师表 course(cid,cname,tid) 课程表 sc(sid,cid,score) 成绩表 2、建表SQL语句并插入数据 -- 1.学生表 CREATE TABLE student( sid INT PRIMARY KEY AUTO_INCREMENT, sname...
【习题五】【数据库原理】
AXDLMG7的CSDN~
06-12 2938
1.在SQL的SELECT语句中,能实现投影操作的是( )。 A. SELECT B. FROM C. WHERE D. GROUP BY 正确答案: A2.SQL集数据查询、数据操纵、数据定义和数据控制功能于一体,语句ALTER TABLE实现下列哪类功能( )。 A. 数据查询 B. 数据操纵 C. 数据定义 D. 数据控制 正确答案: C3.下列SQL语句中,( )不是数据操纵语句。 A. INSERT B. CREATE C. DELETE D. UPDATE 正确答案: B4.SQL中,下列涉及空
jquery+mysql项目案例
01-30
一个典型的jQuery和MySQL项目案例是一个由用户提交问题并回答的问答网站。用户可以在网站上发表问题，并由其他用户进行回答。通过使用jQuery，可以实现网页的实时刷新和动态更新，使用户能够实时地看到新的问题和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值