MySQL经典例题

MySQL经典例题

• 学生表
  Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
• 课程表
  Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
• 教师表
  Teacher(t_id,t_name) –教师编号,教师姓名
• 成绩表
  Score(s_id,c_id,s_score) –学生编号,课程编号,分数

# 自己创建数据库

-- 学生表
CREATE TABLE Student(
    s_id VARCHAR(20) COMMENT '学生编号',
    s_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
    s_birth VARCHAR(20) NOT NULL DEFAULT '' COMMENT '出生年月',
    s_sex VARCHAR(10) NOT NULL DEFAULT '' COMMENT '学生性别',
    PRIMARY KEY(s_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '学生表';
-- 课程表
CREATE TABLE Course(
    c_id  VARCHAR(20) COMMENT '课程编号',
    c_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '课程名称',
    t_id VARCHAR(20) NOT NULL COMMENT '教师编号',
    PRIMARY KEY(c_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '课程表';
-- 教师表
CREATE TABLE Teacher(
    t_id VARCHAR(20) COMMENT '教师编号',
    t_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师姓名',
    PRIMARY KEY(t_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '教师表';
-- 成绩表
CREATE TABLE Score(
    s_id VARCHAR(20) COMMENT '学生编号',
    c_id  VARCHAR(20) COMMENT '课程编号',
    s_score INT(3) COMMENT '分数',
    PRIMARY KEY(s_id,c_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '成绩表';
 
-- 插入学生表测试数据
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);


---------------------------------------------------------------------------------------------


*** 查询"01"课程比"02"课程成绩高的学生的信息及课程分数  
select a.*,b.s_score as score1,c.s_score as score2
from Student a
left join Score b on a.s_id=b.s_id and b.c_id='01'
left join Score c on a.s_id=c.s_id and c.c_id='02'
where b.s_score>c.s_score;


*** 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select stu.*,s1.s_score as score1,s2.s_score as score2
from Student stu
left join Score s1 on stu.s_id=s1.s_id and s1.c_id='01'
left join Score s2 on stu.s_id=s2.s_id and s2.c_id='02'
where s1.s_score<s2.s_score;

 
*** 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select stu.s_id,stu.s_name,round(avg(s.s_score),1)
from Student stu
left join Score s on stu.s_id=s.s_id
group by stu.s_id
having avg(s.s_score)>=60;


*** 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
select stu.s_id,stu.s_name,avg(s.s_score)
from Student stu
left join Score s on stu.s_id=s.s_id
group by stu.s_id
having avg(s.s_score)<60
or stu.s_id not in(select stu.s_id from Student stu join Score s where stu.s_id=s.s_id); 

*** 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩并从高到低排序
select stu.s_id,stu.s_name,count(s.c_id) as 选课总数,sum(s.s_score) as 总成绩
from Student stu
left join Score s on stu.s_id=s.s_id
group by stu.s_id
order by sum(s.s_score) desc;

*** 查询"李"姓老师的数量 
select count(t_name) from Teacher where t_name like "李%";

*** 查询学过"张三"老师授课的同学的信息 
# 张三编号
select t_id from Teacher where t_name = '张三';
# 张三所教的课程编号
select c_id from Course where t_id= (select t_id from Teacher where t_name = '张三'); 
# 学张三课程的学生编号
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 * 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 stu.* from Student stu,Score s,Course c
where stu.s_id=s.s_id 
and s.c_id=c.c_id 
and c.t_id=(select t_id from Teacher where t_name='张三');

*** 查询没学过"张三"老师授课的同学的信息(把7改为not in 即可)
select * from Student
where
s_id not 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 = '张三')));

 
*** 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息,及两门课程成绩
select stu.*,s1.s_score as score1,s2.s_score as score2
from Student stu,Score s1,Score s2
where
	stu.s_id = s1.s_id
and stu.s_id = s2.s_id
and s1.c_id = '01'
and s2.c_id = '02';

*** 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
# 学过01课程的学生编号
select s_id from Score where c_id='01';
# 学过02可能的学生编号
select s_id from Score where c_id='02';

# 综上
select * from Student
where
s_id in (select s_id from Score where c_id='01')
and s_id not in (select s_id from Score where c_id='02');


*** 查询没有学全所有课程的同学的信息 
# 查询成绩表中没有学完3们课程大的学生
select s_id from Score group by s_id having count(c_id) != 3;
# 查询没有成绩的学生（在成绩表中没有的）
select s_id from Student where s_id not in (select s_id from Score);
# 综上
select * from Student
where
s_id in (select s_id from Score group by s_id having count(c_id) != 3)
or s_id not in (select s_id from Score);

*** 查询至少有一门课与学号为"01"的同学所学相同 的同学的信息
# 学号为01的同学所学的所有课程
select c_id from Score where s_id='01';
# 至少一门课程相同的学生编号
select distinct s_id from Score where c_id in (select c_id from Score where s_id='01') and s_id != '01';
# 综上
select * from Student
where
s_id in (select distinct s_id from Score where c_id in (select c_id from Score where s_id='01') and s_id != '01')


*** 查询和"01"号的同学学习的课程完全相同的其他同学的信息
# 学号为01的同学所学的所有课程
select c_id from Score where s_id='01';
# 与01同学课程完全相同的学生编号
select distinct s_id from Score 
where s_id != '01' and c_id in (select c_id from Score where s_id='01')
group by s_id
having count(1)=(select count(1) from Score where s_id='01')# 判断分组后的行数是否等于01学生所学课程的行数(学了几门课程就有几行)
# 综上
select * from Student
where
s_id in (
	select distinct s_id from Score 
	where s_id != '01' and c_id in (select c_id from Score where s_id='01')
	group by s_id
	having count(1)=(select count(1) from Score where s_id='01')
);



*** 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
# 查询两门及以上不及格的学生编号
select s_id from Score where s_score<60 group by s_id having count(1) >=2;


select 
	stu.s_id,stu.s_name,round(avg(s.s_score),1) as 平均成绩
from 
	Student stu
left join Score s on stu.s_id=s.s_id
group by 
	stu.s_id
having
	stu.s_id in (select s_id from Score where s_score<60 group by s_id having count(1) >=2);
	


*** 检索"01"课程分数小于60，按分数降序排列的学生信息及01分数
# 方法一
select 
	stu.*,s.s_score
from 
	Student stu,Score s
where 
	stu.s_id=s.s_id and s.c_id='01' and s.s_score<60
order by
	s.s_score desc;


# 方法二
select stu.*,s_score
from Student stu
left join Score s on stu.s_id=s.s_id
where s.c_id='01' and s_score<60
order by s.s_score desc;


*** 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
	s.s_id,
	s1.s_score as 课程1,
	s2.s_score as 课程2,
	s3.s_score as 课程3,
	round(avg(s.s_score),2) as 平均分
from
	Score s
left join Score s1 on s.s_id=s1.s_id and s1.c_id='01'
left join Score s2 on s.s_id=s2.s_id and s2.c_id='02'
left join Score s3 on s.s_id=s3.s_id and s3.c_id='03'
group by s.s_id
order by 平均分 desc;


*** 查询不同老师所教不同课程 平均分从高到低显示 
select
	t.t_name,
	c.c_id,
	c.c_name,
	round(avg(s.s_score),2) as 平均分
from
	Teacher t
left join Course c on t.t_id=c.t_id
left join Score s on c.c_id=s.c_id
group by s.c_id
order by avg(s.s_score) desc;



*** 查询每门课程被选修的学生数 
select c_id,count(s_id) from Score group by c_id; 


*** 查询出只有两门课程的全部学生的学号和姓名
# 方法一
select stu.s_id,stu.s_name
from Student stu
left join Score s on stu.s_id=s.s_id
group by stu.s_id
having count(s.c_id)=2;

# 方法二
select s_id,s_name
from Student
where s_id in (select s_id from Score group by s_id having count(c_id)=2);


*** 查询男生、女生人数
select s_sex,count(s_id) from Student group by s_sex;

*** 查询名字中含有"风"字的学生信息
select * from Student where s_name like '%风%';


*** 查询同名同性学生名单，并统计同名人数 
select
	stu.s_name,
	stu.s_sex,
	count(stu.s_name) as 同名人数
from
	Student stu
left join Student stu2 on stu.s_name=stu2.s_name and stu.s_sex=stu2.s_sex and stu.s_id!=stu2.s_id
group by stu.s_name,stu.s_sex;



*** 查询1990年出生的学生名单
# 方法一
select s_name from Student where s_birth like '1990%';
# 方法二
select s_name from Student where year(s_birth)='1990';


*** 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id,round(avg(s_score),2)
from Score
group by c_id
order by avg(s_score) desc,c_id;


*** 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select stu.s_id,stu.s_name,round(avg(s.s_score),2) as 平均成绩
from Student stu
left join Score s on stu.s_id=s.s_id
group by stu.s_id
having avg(s.s_score)>=85;


*** 查询课程名称为"数学"，且分数低于60的学生姓名和分数
# 查出课程名称数学的课程编号
select c_id from Course where c_name='数学';
# 综上
select stu.s_name,s.s_score
from Student stu
left join Score s on stu.s_id=s.s_id
where
	s.c_id=(select c_id from Course where c_name='数学')
and
	s.s_score<60;



*** 查询所有学生的课程及分数情况； 
select stu.s_id,stu.s_name,s1.s_score,s2.s_score,s2.s_score
from Student stu
left join Score s1 on stu.s_id=s1.s_id and s1.c_id='01'
left join Score s2 on stu.s_id=s2.s_id and s2.c_id='02'
left join Score s3 on stu.s_id=s3.s_id and s3.c_id='03';


*** 查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select stu.s_name,c.c_name,s.s_score
from Student stu
left join Score s on stu.s_id=s.s_id
left join Course c on s.c_id=c.c_id
having s.s_score>70;

*** 查询不及格的学生id,姓名，及其课程名称，分数
select stu.s_id,stu.s_name,c.c_name,s.s_score
from Student stu
left join Score s on stu.s_id=s.s_id
left join Course c on s.c_id=c.c_id
where s.s_score<60;


*** 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select stu.s_id,stu.s_name
from Score s
left join Student stu on s.s_id=stu.s_id
where s.c_id='01' seland s.s_score>80;

*** 求每门课程的学生人数 
select c_id,count(s_id) from Score group by c_id;

*** 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
# 张三老师的教师编号
select t_id from Teacher where t_name='张三';
# 张三老师所授课的课程编号
select c_id from Course where t_id=(select t_id from Teacher where t_name='张三');
# 综上
select stu.*,s.s_score
from Student stu
left join Score s on stu.s_id=s.s_id and s.c_id = 
	(select c_id from Course where t_id=(select t_id from Teacher where t_name='张三'))
having max(s.s_score);


*** 查询不同课程 成绩相同的学生的学生编号、课程编号、学生成绩
select distinct s1.s_id,s1.c_id,s1.s_score
from Score s1,Score s2
where s1.s_score=s2.s_score and s1.c_id != s2.c_id;


*** 查询每门功课成绩最好的前两名 
select s.s_id,s.c_id,s.s_score
from Score s
where 
(select count(1) from Score s1 where s1.c_id=s.c_id and s1.s_score>=s.s_score)<=2
order by s.c_id



*** 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 

select c_id as 课程号,count(1) as 选修人数
from Score
group by c_id
having count(1)>5
order by count(1) desc,c_id;


*** 检索至少选修两门课程的学生学号
select s_id from Score group by s_id having count(c_id)>=2;

*** 查询选修了全部课程的学生信息
select stu.* from Score s
left join Student stu on s.s_id=stu.s_id
group by s.s_id
having count(1) = (select count(1) from Course)

*** 查询各学生的年龄
# 用今年的年份-出生的年份
# 考虑：如果今天的月日小于出生的月日,说明今年还不满一岁，结果-1.否则不变为0
select s_id,s_birth,(DATE_FORMAT(NOW(),'%Y') - DATE_FORMAT(s_birth,'%Y')) - 
(case when DATE_FORMAT(NOW(),'%m%d')<DATE_FORMAT(s_birth,'%m%d') then 1 else 0 end) as age
from Student;

*** 查询本周过生日的学生(判断出生的周是否是当下时间的周)
# 方法一
select * from Student where week(current_date)=week(s_birth);
# 方法二
select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth);

*** 查询下周过生日的学生
select * from Student where week(date_format(now(),'%Y%m%d'))+1 = week(s_birth);

*** 查询本月过生日的学生
select * from Student where month(now()) = month(s_birth);

*** 查询下月过生日的学生
select * from Student where month(now())+1 = month(s_birth);

本文为转载，参考原作者进行了相应的修改

原文链接：
原作者链接https://blog.csdn.net/csdnluolei/article/details/83507312