mysql题_经典MySQL50道练习题及答案写完以后感觉⾃⼰对⼦查询和⾃查询的理解不够，收获颇多。
另外，⾃⼰使⽤的数据库是MySQL，⼤家也可以⽤别的数据库练习，有些语句还会简单些。
欢迎⼤家和我交流~
CREATE table Student
(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10))
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' , '⼥');
js特效字体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' , '⼥');
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语⽂' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
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,class1,class2 from
(select SId, score as class1 from sc where sc.CId = '01')as t1, (select SId, score as class2 from sc where sc.CId = '02')as t2 where t1.SId = t2.SId AND t1.class1 > t2.class2 )r on Student.SId = r.SId
******2.1、查询同时存在" 01 "课程和" 02 "课程的情况
select t1.sid,class1,class2 from
(select sid,score class1 from sc where cid='01') t1
join
(select sid,score class2 from sc where cid='02') t2
on t1.sid=t2.sid
2.2、查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显⽰为 null )
select t1.sid,class1,class2 from
(select sid,score class1 from sc where cid='01') t1
left join
(select sid,score class2 from sc where cid='02') t2
on t1.sid=t2.sid
2.3、查询不存在" 01 "课程但存在" 02 "课程的情况
select t2.sid,class1,class2 from
(select sid,score class1 from sc where cid='01') t1
right join
(select sid,score class2 from sc where cid='02') t2
on t1.sid=t2.sid
3、查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
select distinct a.sid,sname,a.avg from
(select sid,avg(score) avg from sc GROUP BY sid) a
join student on a.sid=student.sid
where avg>=60
select distinct sc.sid,sname,avg(score) avg
from sc join student s on sc.sid=s.sid
group by sc.SId
having avg(score)>=60
4、查询在 SC 表存在成绩的学⽣信息
SELECT DISTINCT * from student
c语言乘法表程序怎么写
where sid in (select sid from sc)
*****5、查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显⽰为 null ) SELECT s.sid,s.sname,count(cid),sum(score)
from student s left join sc
on s.sid=sc.sid
group by s.sid
mysql面试题34道经典6、查有成绩的学⽣信息
select DISTINCT s.* from student s join sc
python加密文件夹where s.sid in (select DISTINCT sc.sid from sc)
7、查询「李」姓⽼师的数量
select count(*) from teacher where tname like "李%"
8、查询学过「张三」⽼师授课的同学的信息
SELECT DISTINCT s.* from student s join sc on s.sid=sc.sid
join course c on sc.cid=c.cid
join teacher t on t.tid=c.tid
where tname='张三'
******9、查询没有学全所有课程的同学的信息
select DISTINCT s.* from student s LEFT JOIN sc on s.sid=sc.sid
left join course c on c.cid=sc.cid
where s.sid not in
(select sc.sid from sc GROUP BY sc.sid having count(1) =(select count(*) from course)) 10、查询⾄少有⼀门课与学号为" 01 "的同学所学相同的同学的信息
select DISTINCT s.* from student s JOIN sc on s.sid=sc.sid
where s.sid in (SELECT sc.sid from sc where sc.cid
in (select sc.cid FROM sc where sc.sid='01'))
*********11、查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
(1、GROUP_CONCAT()函数 2、⽤法和聚合函数⼀样，select后或having后)
select DISTINCT s.* from student s JOIN sc on s.sid=sc.sid
where s.sid in (SELECT sc.sid FROM sc GROUP BY sc.sid
having GROUP_CONCAT(sc.cid ORDER BY cid) = (select GROUP_CONCAT(sc.cid ORDER BY cid) FROM sc where sc.sid='01' GROUP BY sc.sid))
12、查询没学过"张三"⽼师讲授的任⼀门课程的学⽣姓名
select s.sid,s.sname from student s
where s.sid not in (SELECT DISTINCT s.sid from student s join sc on s.sid=sc.sid
join course c on sc.cid=c.cid
join teacher t on t.tid=c.tid
where tname='张三')
13、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select sc.sid,sname,avg(score) from student s join sc on s.sid=sc.sid
where score<60 GROUP BY sc.sid having count(1)>=2
14、检索" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息
select s.* ,score from student s join sc on s.sid=sc.sid
where cid='01' and score<60
order by score desc
****15、按平均成绩从⾼到低显⽰所有学⽣的所有课程的成绩以及平均成绩(avg聚合函数想要多⾏输出，⼦查询)
select sc.sid,cid,score,a.avg
from sc join (select sid,avg(score) avg from sc group by sid) a on sc.sid=a.sid
ORDER BY a.avg desc
16、查询各科成绩最⾼分、最低分和平均分:
以如下形式显⽰：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为:70-80，优良为:80-90，优秀为:>=90
要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列
select c.cid,cname,count(1) 选修⼈数,max(score) 最⾼分,min(score) 最低分,avg(score) 平均分,
sum(case when score>=60 then 1 else 0 end)/count(1) 及格率,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(1) 中等率,
sum(case when score>=80 and score<90 then 1 else 0 end)/count(1) 优良率,
sum(case when score>=90 then 1 else 0 end)/count(1) 优秀率
from sc join course c on sc.cid=c.cid
GROUP BY sc.cid
order by 选修⼈数 desc,c.cid
************(排序题)
17.1、按各科成绩进⾏排序，并显⽰排名， Score 重复时保留名次空缺
select a.*,count(1) rank from sc a left join sc b on a.cid=b.cid
where a.score<=b.score
group by a.cid,a.sid,a.score
order by a.cid,a.score desc
17.2、按各科成绩进⾏排序，并显⽰排名， Score 重复时合并名次
17.3、查询学⽣的总成绩，并进⾏排名，总分重复时保留名次空缺
17.4、 查询学⽣的总成绩，并进⾏排名，总分重复时不保留名次空缺
-
-学习变量
--row_number函数功能，未分组
set @r:=0;
select *,@r:=@r+1 rank from sc
西门子plc视频ORDER BY cid,score desc
--row_number函数功能，分组，判断cid是否相等。
另⼀种定义变量的写法--SELECT @p:=NULL ,@r:=0
SELECT sc.*,IF (@p=cid ,@r:=@r+1,@r:=1) AS rank,@p:=cid
FROM sc,(SELECT @p:=NULL ,@r:=0) s输入ascii码输出字符c语言
ORDER BY sc.cid,sc.score DESC
--dense_rank函数功能，未分组(=表⽰判断是否相等，返回0,1；：=表⽰赋值，先计算右边，赋值给左边) set @r:=0;
set @p:=null;
select *,
case when @p=score then @r
when @p:=score then @r:=@r+1
END rank
from sc
ORDER BY cid,score desc
--dense_rank函数功能，分组
select sc.*,
if(@p=cid,
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论