提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

mysql中经典50道题目

mysql
2023-03-15 23:29:33 时间

1、四表之间的关联关系及字段说明

2、建表语句和插入数据

1）student表

-- student表
create table student(
    sid varchar(20),
    sname varchar(20),
    sage datetime,
    ssex varchar(10)
)charset=utf8;
-- student表中插入数据
insert into student values
('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-12-20' , '男'),
('04' , '李云' , '1990-12-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-01-01' , '女'),
('07' , '郑竹' , '1989-01-01' , '女'),
('09' , '张三' , '2017-12-20' , '女'),
('10' , '李四' , '2017-12-25' , '女'),
('11' , '李四' , '2012-06-06' , '女'),
('12' , '赵六' , '2013-06-13' , '女'),
('13' , '孙七' , '2014-06-01' , '女');

2）score表(sc表)

-- score表
create table sc(
    sid varchar(20),
    cid varchar(20),
    score int
)charset=utf8;
-- score表中插入数据
insert into sc values
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);

3）course表

-- course表
create table course(
    cid varchar(20),
    cname varchar(20),
    tid varchar(20)
)charset=utf8;
-- course表中插入数据
insert into course values
('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');

4）teacher表

-- teacher表
create table teacher(
    tid varchar(20),
    tname varchar(20)
)charset=utf8;
-- teacher表中插入数据
insert into teacher values
('01' , '张三'),
('02' , '李四'),
('03' , '王五');

3、50道题目

1）查询"01"课程比"02"课程成绩高的学生的信息及课程分数。

select 
	s.*,
	a.score score01,
	b.score score02
from 
	student s
join 
	sc a on s.sid=a.sid and a.cid="01"
join 
	sc b on s.sid=b.sid and b.cid="02"
where 
	a.score > b.score;

2）查询"01"课程比"02"课程成绩低的学生的信息及课程分数。

select 
	s.*,
	a.score score01,
	b.score score02
from 
	student s
join 
	sc a on s.sid=a.sid and a.cid="01"
join 
	sc b on s.sid=b.sid and b.cid="02"
where 
	a.score < b.score;

3）查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。

select 
	s.sid,
	s.sname,
	avg(score) avg_score
from 
	student s
left join 
	sc on s.sid= sc.sid
group by 
	s.sid,s.sname
having 
	avg_score > 60;

4）查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩。

select 
	s.sid,
	s.sname,
	avg(score) avg_score
from 
	student s
left join 
	sc on s.sid= sc.sid
group by 
	s.sid,s.sname
having 
	avg_score < 60;

5）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。

select 
	s.sid,
	s.sname,
	count(cid) counts,
	sum(score) sum_score
from 
	student s
left join 
	sc on s.sid= sc.sid
group by 
	s.sid,s.sname;

6）查询"李"姓老师的数量。

select 
	count(*) counts
from 
	teacher
where 
	tname like "李%";

7）查询学过"张三"老师授课的同学的信息。

select 
	s.*
from 
	student s
join 
	sc on s.sid=sc.sid
where 
	sc.cid=(select cid from course where tid=(select tid from teacher where tname="张三"));

8）查询没学过"张三"老师授课的同学的信息。

select student.*
from student
where sid not in
(
select 
	s.sid
from 
	student s
join 
	sc on s.sid=sc.sid
where 
	sc.cid=(select cid from course where tid=(select tid from teacher where tname="张三"))
);

9）查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。

select 
	s.*
from 
	student s
join 
	sc a on s.sid=a.sid and a.cid="01"
join 
	sc b on s.sid=b.sid and b.cid="02";

10）查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。(这一题要好好理解一下)

select 
	s.*
from 
	student s
left join 
	sc a on s.sid = a.sid and a.cid = '01'
left join 
	sc b on s.sid = b.sid and b.cid = '02'
where 
	a.sid is not null and b.sid is  null;

11）查询没有学全所有课程的同学的信息。

select 
	c.*
from (
	select s.sid sid,count(cid) counts
	from student s
	left join sc a on s.sid=a.sid
	group by s.sid
	having counts<(select count(*) from course)
)b
join 
	student c
on 
	b.sid=c.sid;

12）查询至少有一门课与学号为"01"的同学所学相同的同学的信息。

select 
	distinct s.*
from 
	student s
join 
	sc on s.sid=sc.sid
where 
	sc.cid in (select cid from sc where sid="01");

13）查询和"01"号的同学学习的课程完全相同的其他同学的信息。(这一题有点难)

14）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。

select 
	s.sid,s.sname,count(cid) counts,avg(score) avg_score
from 
	student s
join 
	sc on s.sid=sc.sid
where 
	sc.score < 60
group by 
	s.sid,s.sname
having 
	counts >=2;

15）检索"01"课程分数小于60，按分数降序排列的学生信息。

select 
	s.*
from 
	student s
join 
	sc on s.sid=sc.sid
where 
	sc.cid="01" and sc.score<60
order by 
	sc.score desc;

16）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。

select 
	a.sname,b.score,c.avg_score avg_score
from 
	student a
left join 
	sc b on a.sid=b.sid
left join
	(
	 select s.sid sid,avg(score) avg_score
	 from student s
  	 left join sc on s.sid=sc.sid
	 group by sid
	)c 
	on a.sid=c.sid
order by 
	avg_score desc;

17）查询各科成绩最高分、最低分和平均分。

  • 以如下形式显示：
  • 课程id，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
  • 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select 
	cid,
	max(score) 最高分,
	min(score) 最低分,
	avg(score) 平均分,
	sum(case when score>=60 then 1 else 0 end)/count(*) 及格率,
	sum(case when score>=70 and score<80 then 1 else 0 end)/count(*) 中等率,
	sum(case when score>=80 and score<90 then 1 else 0 end)/count(*) 优良率,
	sum(case when score>=90 then 1 else 0 end)/count(*) 优秀率
from 
	sc
group by 
	cid;

18）按各科成绩进行排序，并显示排名。(有一点难度)

19）查询学生的总成绩并进行排名。

-- 设置一个全局变量；
set @num=0;
select 
	a.sname,
	a.sum_score,
	@num:=@num+1 as ranks
from
(
	select s.sname sname,sum(sc.score) sum_score
	from student s
	join sc 
	on s.sid=sc.sid
	group by s.sid,s.sname
	order by sum_score desc
)a;

20）查询不同老师所教不同课程平均分从高到低显示。

select 
	c.tid,
	avg(sc.score) avg_score
from 
	sc
join 
	course c
on 
	sc.cid=c.cid
group by 
	c.tid;

21）查询1990年1月份出生的学生名单。

select 
	*
from 
	student
where 
	year(sage)="1990" and month(sage)="1";

22）统计各科成绩各分数段人数。

  • 课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select 
	sc.cid,
	c.cname,
	sum(case when score>=0 and score<60 then 1 else 0 end) '[0-60]',
	sum(case when score>=60 and score<70 then 1 else 0 end) '[60-70]',
	sum(case when score>=70 and score<85 then 1 else 0 end) '[70-85]',
	sum(case when score>=85 and score<=100 then 1 else 0 end) '[85-100]',
	sum(case when score>=0 and score<60 then 1 else 0 end)/count(*) '[0-60]占比',
	sum(case when score>=60 and score<70 then 1 else 0 end)/count(*) '[60-70]占比',
	sum(case when score>=70 and score<85 then 1 else 0 end)/count(*) '[70-85]占比',
	sum(case when score>=85 and score<=100 then 1 else 0 end)/count(*) '[85-100]占比'
from 
	sc
join 
	course c on sc.cid=c.cid
group by 
	sc.cid,c.cname;

23）查询学生平均成绩及其名次。

set @num=0;
select 
	a.sid,
	a.avg_score,
	@num:=@num+1 rank
from
(
	select sid,avg(score) avg_score
	from sc 
	group by sid
	order by avg_score desc
)a;

24）查询各科成绩前三名的记录。

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

25）查询每门课程被选修的学生数。

select 
	cid,
	count(*) counts
from 
	sc
group by 
	cid;

26）查询出只有两门课程的全部学生的学号和姓名。

select 
	s.sid,
	s.sname
from 
	student s
join 
	sc 
on 
	s.sid=sc.sid
group by 
	s.sid,s.sname
having 
	count(*)=2;

27）查询男生、女生人数。

select 
	ssex,
		count(*) counts
from 
	student
group by 
	ssex;

28）查询名字中含有"风"字的学生信息。

select 
	*
from 
	student
where 
	sname like "%风%";

29）查询同名同性学生名单，并统计同名人数。

select 
	sname,
	ssex,
	count(*) counts
from 
	student
group by 
	sname,ssex
having 
	counts>=2;

30）查询1990年出生的学生名单。

select 
	*
from 
	student
where 
	year(sage)="1990";

31）查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号。

select 
	cid,
	avg(score) avg_score
from 
	sc
group by 
	cid
order by 
	avg_score desc,cid asc;

32）查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 。

select 
	s.sid,
	s.sname,
	avg(score) avg_score
from 
	student s
join 
	sc 
on 
	s.sid=sc.sid
group by 
	s.sid,s.sname
having 
	avg_score>=85;

33）查询课程名称为"数学"，且分数低于60的学生姓名和分数。

select 
	s.sname,
	sc.score
from 
	student s
join 
	sc 
on 
	s.sid=sc.sid
join 
	course c
on 
	sc.cid=c.cid
where 
	c.cname="数学" and sc.score<60;

34）查询所有学生的课程及分数情况。

select 
	* 
from 
	student s
left join 
	sc
on 
	s.sid=sc.sid
left join 
	course c
on 
	sc.cid=c.cid;

35）查询任何一门课程成绩在70分以上的姓名、课程名称和分数。

select 
	s.sname,
	c.cname,
	sc.score
from 
	student s
left join 
	sc on s.sid = sc.sid
left join 
	course c on sc.cid = c.cid
where 
	score > 70;

36）查询不及格的课程名称。

select 
	distinct c.cname
from 
	sc
join 
	course c
on 
	sc.cid=c.cid
where 
	sc.score<60;

37）查询课程编号为01且课程成绩在80分以上(包含80分)的学生的学号和姓名。

select 
	s.sid,
	s.sname
from 
	student s
join 
	sc 
on 
	s.sid=sc.sid
where 
	sc.cid="01" and sc.score>=80;

38）求每门课程的学生人数 。

select 
	cid,
	count(*) counts
from 
	sc
group by 
	cid;

39）查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。

select 
	s.*,
	sc.score
from 
	student s
join 
	sc on s.sid=sc.sid
join 
	course c on c.cid=c.cid
join 
	teacher t on c.tid=t.tid
where 
	t.tname="张三"
order by 
	sc.score desc
limit 1;

40）查询每个学生中，不同课程但成绩相同的学生的学生编号、课程编号、学生成绩。

select 
	a.sid,a.cid,a.score,b.cid,b.score
from 
	sc a
join 
	sc b 
on 
	a.sid=b.sid 
where 
	a.cid != b.cid and a.score = b.score;

41）查询每门功课成绩最好的前两名。

select 
	a.cid,
	a.score
from 
	sc a
where 
	(select count(*) from sc b where a.cid=b.cid and a.score<b.score)<2
order by 
	a.cid,a.score desc;

42）查询选修了全部课程的学生信息。

select 
	s.*
from 
	student s
join 
(
	select sid
	from sc 
	group by sid 
	having count(*)=3
) a
on 
	s.sid=a.sid;

43）查询各学生的年龄。

select 
	sname,
	(year(now())-year(sage)) age
from 
	student;

44）查询本周过生日的学生。

-- 本周实际为周4，但是返回结果是3，但是不影响。
select weekday(now()); 
-- 返回结果是2019-12-31 23:59:59。
select date_add('1999-12-31 23:59:59',interval (year(now())-year('1999-12-31 23:59:59')) year);
-- 返回结果是本周周一的日期：2019-12-09 10:04:02
select date_add(now(),interval -weekday(now()) day);   
-- 返回结果是本周周日的日期：2019-12-15 10:04:37
select date_add(now(),interval 6-weekday(now()) day); 
-- 完整代码如下：
select 
	sname,sage
from 
	student
where 
	date_add(sage,interval (year(now())-year(sage)) year)
between 
	date_add(now(),interval -weekday(now()) day)
and 
	date_add(now(),interval 6-weekday(now()) day);

45）查询下周过生日的学生。

select 
	sname,sage
from 
	student
where 
	date_add(sage,interval (year(now())-year(sage)) year)
between 
	date_add(now(),interval 7-weekday(now()) day)
and 
	date_add(now(),interval 7-weekday(now())+6 day);

46）查询本月过生日的学生。

select 
	sname
from 
	student
where 
	month(now())=month(sage);

47）查询下月过生日的学生。

-- 这里不能直接使用month(now)+1表示下一个月，当月份为12的时候，加1就表示13了；
--因此，使用date_add()函数，可以在原来日期基础上，增加年/月/日/时/分；
select 
	sname
from 
	student
where 
	month(date_add(now(),interval 1 month))=month(sage);

相关文章