APP

海量资源，尽在掌握

搜索
当前位置：首页 > IT技术 > 数据库 > 正文

2021-8-5 Mysql个人练习题
2021-10-08 17:33:23

创建学校表格

CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);

CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);

CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);

CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

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 Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98)
View Code

 

SELECT s2.s_name,s2.s_birth,s2.s_sex,s1.s_score,s3.s_score from student s2
left join score s1 on s1.s_id=s2.s_id  and s1.c_id="02"
left join score s3 on s3.s_id=s2.s_id and s3.c_id="01"
where s3.s_score>s1.s_score;#查询课程01比02的课程分数的同学信息

SELECT s1.s_id,(s1.s_score) avgScore,s2.s_name from score s1 
left join student s2 on s1.s_id=s2.s_id
group by s1.s_id HAVING avgScore>=60;#查询平均成绩大于60的同学信息


SELECT s2.s_id,s2.s_name,count(s1.c_id),sum(s1.s_score) from student s2
left join score s1 on s2.s_id=s1.s_id GROUP BY s_id;#查询学生编号、学生姓名、课程数、课程总分

SELECT count(*) from teacher where t_name like '张%';#查询姓张的老师数量

SELECT s2.* from student s2
left join score s1 on s2.s_id=s1.s_id
left join course c1 on c1.c_id=s1.c_id
left join teacher t1 on t1.t_id=c1.t_id
where t1.t_name like "张三";#查询张三教的学生的所有信息

SELECT * from student where s_id not in(
SELECT s2.s_id from student s2
left join score s1 on s2.s_id=s1.s_id
left join course c1 on c1.c_id=s1.c_id
left join teacher t1 on t1.t_id=c1.t_id
where t1.t_name like "张三");#查询不是张三教的学生的所有信息

 

select * from student  where s_id in (select s1.s_id from score s1 inner join score s2 on s1.s_id=s2.s_id where s1.c_id="01" and s2.c_id="02");
#查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select * from student  where s_id in (select s1.s_id from score s1 inner join score s2 on s1.s_id=s2.s_id where s1.c_id="01" and s2.c_id!="02");
#查询学过编号为"01"但没有学过编号为"02"的课程的同学的信息

SELECT * from student where s_id in(SELECT s1.s_id from score s1 GROUP BY s_id HAVING count(s1.c_id)>=(select count(DISTINCT(c_id)) from score));
#查询所有课程都学过的同学信息

SELECT * from student where s_id in (SELECT s_id from score group by s_id HAVING GROUP_CONCAT(c_id)=(SELECT GROUP_CONCAT(c_id) from score s1 where s_id='01' group by s_id));
#查询所有课程都与01同学相同的同学信息

select s_name from student where s_id in(SELECT s_id from score where c_id =(select c_id from course where t_id=(SELECT t_id from teacher where t_name="张三")));
#查询学过张三课程的学生信息

SELECT s1.s_id,s1.s_name,avg(s2.s_score) from student s1
left join score s2 on s1.s_id=s2.s_id 
where s2.s_id in (SELECT s_id from score where (s_score<60 or isnull(s_score)) GROUP BY s_id HAVING count(s_id)>=2) group by s1.s_id,s1.s_name ;
#查询两门或两门以上不及格分数的学生学号，姓名和平均分

SELECT s1.*,s2.s_score from student s1
left join score s2 on s1.s_id=s2.s_id
where s2.c_id='01' and s2.s_score<=60 ORDER BY s2.s_score desc;
#查询01课程成绩小于60分并且按分数降序排列



SELECT s1.s_name,s2.s_score,s3.s_score,s4.s_score,ROUND(sum(s5.s_score)/3,2) as avgs from student s1
left join score s2 on s1.s_id=s2.s_id and s2.c_id='01'
left join score s3 on s1.s_id=s3.s_id and s3.c_id='02'
left join score s4 on s1.s_id=s4.s_id and s4.c_id='03'
left join score s5 on s1.s_id=s5.s_id GROUP BY s1.s_name ORDER BY avgs DESC;
#查询平均分，直接使用avg会不计算空值，导致空值没有计算到


select c1.c_id,c1.c_name,MAX(s1.s_score) AS 最高分,MIN(s1.s_score) AS 最低分,avg(s1.s_score) as 平均分,sum(if(s1.s_score<60,1,0)) as 不及格人数,(sum(if(s1.s_score<60,1,0))/COUNT(s1.s_score))
as 及格率
from course c1
left join score s1 on c1.c_id=s1.c_id
GROUP BY c_id,c_name;
#查询最高分，最低分，平均分，及格率，此处将null值默认不计算

set @i=0;
SELECT a.*,(@i:=@i+1) i from(
SELECT s1.s_name,s2.s_score as 课程1,s3.s_score as 课程2,s4.s_score as 课程3,sum(s5.s_score) as 总分 from student s1
left join score s2 on s1.s_id=s2.s_id and s2.c_id='01'
left join score s3 on s1.s_id=s3.s_id and s3.c_id='02'
left join score s4 on s1.s_id=s4.s_id and s4.c_id='03'
left join score s5 on s1.s_id=s5.s_id GROUP BY s1.s_name ORDER BY 总分 desc) as a;
#查询分数，由高到低排序,添加序号


SELECT t_name,avg(s_score) as avgScore from score s1
left join course c1 on s1.c_id=c1.c_id
left join teacher t1 on c1.t_id=t1.t_id
group by t1.t_name ORDER BY avgScore desc;
#不同老师教的学生平均分

SELECT s1.*,c1.c_name,s2.s_score from student s1
left join score s2 on s1.s_id=s2.s_id
left join course c1 on s2.c_id=c1.c_id
where (s1.s_id in (SELECT s_id from (SELECT s_id from score s3 inner join course c2 on s3.c_id=c2.c_id and c2.c_id='01' ORDER BY s3.s_score desc LIMIT 1,2) as tt) and c1.c_id='01')
or (s1.s_id in (SELECT s_id from (SELECT s_id from score s3 inner join course c2 on s3.c_id=c2.c_id and c2.c_id='02' ORDER BY s3.s_score desc LIMIT 1,2) as ttt) and c1.c_id='02')
or (s1.s_id in (SELECT s_id from (SELECT s_id from score s3 inner join course c2 on s3.c_id=c2.c_id and c2.c_id='03' ORDER BY s3.s_score desc LIMIT 1,2) as tttt) and c1.c_id='03')
order by c_name,s_score desc;
#查询各科第二名到第三名的学生信息

SELECT c_id as 课程编号,(sum(if(s_score>=85&&s_score<=100,1,0))/count(s_score)) as 优秀,(sum(if(s_score>=70&&s_score<85,1,0))/count(s_score)) as 优良,(sum(if(s_score>=60&&s_score<70,1,0))/
count(s_score)) as 良,(sum(if(s_score>=0&&s_score<60,1,0))/count(s_score)) as 差 from score GROUP BY c_id;
#统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT c_name,COUNT(s_id) from course c1
left join score s1 on s1.c_id=c1.c_id
GROUP BY c_name;#每一门课程选修的学生人数

 

 

select a.s_id,a.c_id,a.s_score from score a
 where (select COUNT(*) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 order by a.c_id;#查询课程前两名
 
 SELECT s1.*,s2.s_score from student s1
 inner join score s2 on s1.s_id=s2.s_id 
 inner join course c1 on s2.c_id=c1.c_id
 inner join teacher t1 on t1.t_id=c1.t_id and t1.t_name="张三"
 order by s2.s_score desc LIMIT 0,1;
 #查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩 
 
 SELECT c_id,count(*) 总人数 from score
 GROUP BY c_id HAVING 总人数>5
 ORDER BY 总人数 desc,c_id;#查询课程id和选修人数超过5人按总人数排序
 
 SELECT s1.* from student s1
 left join score s2 on s1.s_id=s2.s_id
 GROUP BY s1.s_id HAVING count(*)>=(select count(*) from course);#查询选修了全部课程的学生信息
 
 SELECT s1.*,TIMESTAMPDIFF(YEAR,s1.s_birth,NOW()) as age from student s1;#查询年龄

 

 

 

https://www.cnblogs.com/kangxinxin/p/11585935.html

本文摘自 ：https://www.cnblogs.com/

开通会员，享受整站包年服务立即开通 >
推荐内容
IT技术
Web编程 Windows编程 编程语言 数据库 移动平台 系统服务 微信平台 其他技术
科技百科
可穿戴设备 电子工程 电力 智能汽车网 光学 智能硬件 环保 风电 机器人 安防 云计算 物联网 3D打印 传感器 锂电 储能 智能电网 新材料 太阳能光伏 激光 医疗科技 氢能 工控 照明 显示 智慧城市 半导体照明 光通讯 人工智能 新能源汽车 电源 仪器仪表 通信 VR 智能家居