mysql语句练习题 mysql例题

建表语句 

#########创建学生表
create table t_student(
	sid int primary key,
	sname varchar(10),
	sage date,
	ssex char(10)
)charset=utf8;

INSERT INTO t_student VALUES(01,'赵雷','1990-01-01','男');
INSERT INTO t_student VALUES(02,'钱电','1990-12-21','男');
INSERT INTO t_student VALUES(03,'孙凤','1990-05-20','男');
INSERT INTO t_student VALUES(04,'李云','1990-08-16','男');
INSERT INTO t_student VALUES(05,'周梅','1991-12-01','女');
INSERT INTO t_student VALUES(06,'吴兰','1992-03-01','女');
INSERT INTO t_student VALUES(07,'郑竹','1989-07-01','女');
INSERT INTO t_student VALUES(08,'王菊','1990-01-20','女');


#########创建课程表
create table  t_course(
	cou_id int primary key,
	cou_name VARCHAR(10),
	tea_id int
)charset=utf8;

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


#########创建教师表

create table t_teacher(
	tea_id int primary key,
	tea_name VARCHAR(10)
)charset=utf8;

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


#########创建成绩表
create table t_grade(
	stu_id int ,
	cou_id int,
	score int
)charset=utf8;

insert into t_grade values(01,01,80);
insert into t_grade values(01,02,90);
insert into t_grade values(01,03,99);
insert into t_grade values(02,01,70);
insert into t_grade values(02,02,60);
insert into t_grade values(02,03,80);
insert into t_grade values(03,01,80);
insert into t_grade values(03,02,80);
insert into t_grade values(03,03,80);
insert into t_grade values(04,01,50);
insert into t_grade values(04,02,30);
insert into t_grade values(04,03,20);
insert into t_grade values(05,01,76);
insert into t_grade values(05,02,87);
insert into t_grade values(06,01,31);
insert into t_grade values(06,02,34);
insert into t_grade values(07,01,89);
insert into t_grade values(07,03,98);
insert into t_grade values(08,02,100);


select * from t_student;
select * from t_course;
select * from t_teacher;
select * from t_grade;

查询开始

2019-05-19

#1. 查询" 01 "课程比" 02 "课程成绩高的学生的id及课程分数
方法1：
select a.*,b.cou_id,b.score from (select * from t_grade where cou_id=1)a
	inner join (select * from t_grade where cou_id=2)b
	on a.stu_id=b.stu_id
	where a.score>b.score;
	#总结：
		#一张表的同一个字段进行比较，则必须先对原始表进行查询形成两张子表，两张子表join后再对该字段比较
方法2：
select a.*,b.cou_id,b.score from t_grade a
	inner join t_grade b
	on a.stu_id=b.stu_id and a.cou_id=1 and b.cou_id=2
	where a.score>b.score;
	#总结：
		#由于子查询性能低于连接查询，方法2比方法1好，之后的习题尽量避免子查询，使用连接查询
        ##多重join的话，带where条件的join应该放在最后。

方法3：
select a.*,b.cou_id,b.score from t_grade a
	inner join t_grade b
	on a.stu_id=b.stu_id 
	where a.score>b.score and a.cou_id=1 and b.cou_id=2;
	#总结：
		#SQL92语法可将SQL99的连接条件全部移入where子句中，不过还是建议使用SQL99


#2.查询" 01 "课程比" 02 "课程成绩高的学生的详细信息及课程分数

select c.*,a.score 科目1分数,b.score 科目2分数 from t_student c
	inner join t_grade a 
	on a.stu_id=c.sid and a.cou_id=1
	inner join t_grade b
	on b.stu_id=c.sid and b.cou_id=2
	where a.score>b.score;
	#总结：
        #多重join的话，带where条件的join应该放在最后。


#3.查询同时存在" 01 "课程和" 02 "课程的情况

select t1.* from t_grade t1
inner join t_grade t2
on t1.stu_id=t2.stu_id
inner join t_grade t3
on t2.stu_id=t3.stu_id and t2.cou_id='01' and t3.cou_id='02'



#4.查询存在" 01 "课程但,存在也可能不存在" 02 "课程的情况(不存在时显示为 null )

select t1.* from t_grade t1 
inner join t_grade t2
on t1.stu_id=t2.stu_id 
left join t_grade t3
on t2.stu_id=t3.stu_id and t3.cou_id='02'
where t2.cou_id='01'; 
	#总结：
		#在左外连接中，不能在on中对左表进行过滤，且必须对右表过滤（其他外连接类似）。
        #任何连接查询，on中都不能过滤空值。




#5.查询不存在" 01 "课程但存在" 02 "课程的情况

select a.*,b.cou_id,b.score from t_grade a
	left join t_grade b
	on a.stu_id=b.stu_id  and b.cou_id=1
	where a.cou_id=2 and b.cou_id is null;
	#总结
		#所谓的不存在，因该理解成"可能不存在+该字段为null"，而可能不存在即为左右外连接
        #on子句不要做控制过滤。


#6.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

#第一种方式
select a.sid,a.sname,b.平均成绩 from t_student a
	inner join (select stu_id,avg(score)平均成绩 from t_grade group by stu_id) b
	on a.sid=b.stu_id
	where b.平均成绩>=60;

#第二种方式：不含子查询
select t1.sid '学生编号',t1.sname '学生姓名',avg(t2.score) '平均成绩' from t_student t1
inner join t_grade t2
on t1.sid=t2.stu_id 
group by  t1.sid ,t1.sname
having avg(t2.score)>=60

	#总结:
        #having中的过滤不能移到on中，因为on执行时group by还未执行。
		#子查询和连接查询互转过程中group by 字段可能会改变，在spark/hive/调优可以借助这一点



#7.查询在 grade表存在成绩的学生信息
	select t1.* from t_student t1
	inner join t_grade t2
	on t1.sid=t2.stu_id
	group by t2.stu_id
	#对某个字段去重可以对它执行distinct也可以group by
	
#8.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
	
select 
t1.sid '学生编号',t1.sname '学生姓名',count(t2.cou_id) '选课总数',sum(t2.score) '所有课程总成绩' from t_student t1
left join t_grade t2
on t1.sid=t2.stu_id 
group by t1.sid,t1.sname




#9查询「李」姓老师的数量
 
select count(tea_name) from t_teacher where tea_name like '李%';
#总结：
        #%：通配任意个字符  _:通配一个字符




#10.查询学过「李四」老师授课的同学的信息
select t4.* from t_teacher t1
inner join t_course t2
on t1.tea_id=t2.tea_id and t1.tea_name='李四'
inner join t_grade t3
on t2.cou_id=t3.cou_id
inner join t_student t4 
on t3.stu_id=t4.sid ;

#11.查询没有学全所有课程的同学的信息 
select t1.sid,t1.sname,t1.sage,t1.ssex from t_student t1
inner join t_grade t2 
on t1.sid=t2.stu_id
group by t1.sid,t1.sname,t1.sage,t1.ssex 
having count(t2.cou_id)<(select count(cou_id) from t_course)

#返回一个值或多个值的子查询无法转成连接查询。



#12.查询至少有一门课与学号为" 08 "的同学所学相同的同学的信息 
select t1.* from t_student t1
inner join t_grade t2
on t1.sid=t2.stu_id 
where t1.sid!='08' and t2.cou_id in (select cou_id from t_grade where stu_id='08');

	#distinct必须位于select子句的最前端 
	#in和=any可以完成一样的功能
	#<>  !=  都表示不等于  

	

#13.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select t1.* from t_student t1
inner join t_grade t2
on t1.sid=t2.stu_id and t1.sid!='01'
group by t1.sid,t1.sname,t1.sage,t1.ssex 
having group_concat(t2.cou_id order by t2.cou_id asc)=(select group_concat(cou_id order by cou_id asc) from t_grade where stu_id='01')

#group_concat函数可以将在组内将某个字段的值按要求全部连接起来
select stu_id,group_concat(cou_id order by cou_id separator '-')  from t_grade group by stu_id;



#14.查询没学过"张三"老师讲授的任一门课程的学生姓名

select t1.sname from t_student t1
left join
(
select stu_id from t_grade t3
inner join t_course t4 on t3.cou_id=t4.cou_id
inner join t_teacher t5 on t4.tea_id=t5.tea_id and t5.tea_name='张三'
)t2
on t1.sid=t2.stu_id  where t2.stu_id is null;



#15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 

select t1.sid,t1.sname,avg(t2.score)'平均成绩' from t_student t1
inner join t_grade t2
on t1.sid=t2.stu_id 
group by t1.sid,t1.sname
having sum(if(t2.score>=60,0,1))>=2;


#16.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

select t1.*,t2.score from t_student t1
inner join t_grade t2 
on t1.sid=t2.stu_id 
where t2.cou_id='01' and t2.score<60
order by t2.score desc

#17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select t1.*,t3.cou_id,t3.score,t2.avg_score from t_student t1
inner join 
(select stu_id,avg(score) avg_score from t_grade group by stu_id) t2 
on t1.sid=t2.stu_id 
inner join t_grade t3
on  t1.sid=t3.stu_id
order by t2.avg_score desc;



#18.查询各科课程ID,课程name,最高分、最低分和平均分,选修人数，及格率,中等率，优良率，优秀率
#先按人数降序，再按课程号升序排列
#及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select cou_id,
max(score) '最高分',
min(score) '最低分',
avg(score) '平均分',
count(stu_id) '选修人数',
(sum(if(score>=60,1,0))/count(stu_id)) '及格率',
(sum(if(score>=70 and score<80,1,0))/count(stu_id)) '中等率',
(sum(if(score>=80 and score<90,1,0))/count(stu_id)) '优良率',
(sum(if(score>=90,1,0))/count(stu_id)) '优秀率'
from t_grade group by cou_id
order by count(stu_id)  desc,cou_id asc;


#19.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select t1.cou_id,t1.stu_id,t1.score,
(select count(*) from t_grade where cou_id=t1.cou_id and score>t1.score)+1 rank
from t_grade t1 
order by t1.cou_id asc,rank asc;

#20.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SELECT t.stu_id, t.sum_score, (@rank := @rank + 1) rank FROM
(SELECT stu_id, sum(score) sum_score  FROM t_grade GROUP BY stu_id ) t
inner join (select @rank:= 0) t2 on 1=1
order by sum_score desc;

#21.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select t1.cou_id,t2.cou_name,
sum(if(t1.score<=100 and t1.score>85,1,0))/count(stu_id) as'100-85',
sum(if(t1.score<=85 and t1.score>70,1,0))/count(stu_id) '85-70',
sum(if(t1.score<=70 and t1.score>60,1,0))/count(stu_id) '70-60',
sum(if(t1.score<=60,1,0))/count(stu_id) '60-0'
from t_grade t1
inner join t_course t2
on t1.cou_id=t2.cou_id
group by t1.cou_id,t2.cou_name;


#22.查询各科成绩前三名的记录(要考虑排名重复的情况)

select t1.* from t_grade t1
where (select count(*) from t_grade where cou_id=t1.cou_id and score>t1.score)<3
order by t1.cou_id asc,t1.score desc;


#23.查询每门课程被选修的学生数 

select cou_id,count(stu_id) from t_grade group by cou_id;


#24.查询出只选修两门课程的学生学号和姓名
select t1.sid,t1.sname from t_student t1
inner join t_grade t2
on t1.sid=t2.stu_id
group by t1.sid,t1.sname
having count(t2.cou_id)=2;


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


#26.查询名字中含有「累」字的学生信息

select * from t_student where sname like '%雷%';


#27.查询同名学生名单，并统计同名人数
select t1.*,(select count(*) from t_student where sname=t1.sname) from t_student t1
where sid in 
(
select t1.sid from t_student t1 inner join t_student t2 on t1.sname=t2.sname and t1.sid!=t2.sid
);

####子查询已经保证同名不同sid，select子句中查同名人数即可，不用考虑id不同。

#28.查询 1990 年出生的学生名单
select * from t_student where year(sage)='1990'


#29.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cou_id,avg(score) from t_grade group by cou_id order by avg(score) desc,cou_id asc;


#30.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select t1.sid,t1.sname,avg(t2.score) from t_student t1
inner join t_grade t2
on t1.sid=t2.stu_id 
group by t1.sid,t1.sname
having avg(t2.score)>=85;


#31.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

select t3.sname,t2.cou_name,t1.score from t_grade t1
inner join t_course t2
on t1.cou_id=t2.cou_id and t2.cou_name='数学' and t1.score<60
inner join t_student t3
on t1.stu_id=t3.sid;


#32.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select t1.sid,t2.cou_id,t2.score from t_student t1
left join t_grade t2
on t1.sid=t2.stu_id
left join t_course t3
on t2.cou_id=t3.cou_id
		
		#要保证连接字段中的null,第二次连接必须用left join

#33.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select t3.sname,t2.cou_name,t1.score from t_grade t1
inner join t_course t2
on t1.cou_id=t2.cou_id and t1.score>70
inner join t_student t3
on t1.stu_id=t3.sid


#34.查询存在不及格的课程
select distinct(t1.cou_id),t2.cou_name from t_grade t1
inner join t_course t2
on t1.cou_id=t2.cou_id  and t1.score<60


#35.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

select t1.sid,t1.sname from t_student t1
inner join t_grade t2
on t1.sid=t2.stu_id and t2.cou_id='01' and t2.score >= 80

#36.求每门课程的学生人数 

select cou_id,count(*) from t_grade group by cou_id;


#37.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select t1.*,t2.cou_id,t2.score from t_student t1
inner join t_grade t2
on t1.sid=t2.stu_id
inner join t_course t3
on t2.cou_id=t3.cou_id
inner join t_teacher t4
on t3.tea_id=t4.tea_id 
where t4.tea_name='张三'
order by t2.score desc limit 1
	#最高最低不一定max或min，也可以排序加limit

#38.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select t1.*,t2.score from t_student t1 
inner join t_grade t2
on t1.sid=t2.stu_id and t2.cou_id in
(
select t3.cou_id from t_course t3 inner join t_teacher t4 on t3.tea_id=t4.tea_id and t4.tea_name='张三'
) 
where (select count(*) from t_grade where cou_id=t2.cou_id and score>t2.score)<1;


#39.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 

select t1.stu_id,t1.cou_id,t1.score from t_grade t1
inner join t_grade t2 
on t1.stu_id=t2.stu_id and t1.score=t2.score and t1.cou_id!=t2.cou_id
group by t1.stu_id,t1.cou_id,t1.score


#40.查询每门功成绩最好的前两名 (要考虑到排名重复的情况)

select t1.* from t_grade t1 
where 
(select count(*) from t_grade t where t.cou_id=t1.cou_id and score>t1.score)<2 
order by t1.cou_id asc,t1.score desc ;


#41.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select cou_id,count(stu_id) from t_grade group by cou_id having count(stu_id)>5;

#42.检索至少选修两门课程的学生学号 
select stu_id from t_grade group by stu_id having count(cou_id)>=2;


#43.查询选修了全部课程的学生信息
select t1.* from t_student t1
inner join ( 
select stu_id from t_grade group by stu_id having count(cou_id)=(select count(*) from t_course)
)t2 
on t1.sid=t2.stu_id;

#44.查询各学生的年龄，只按年份来算
select year(now())-year(sage) from t_student;

#45.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select timestampdiff(year,sage,now()) from t_student;


#46.查询本周过生日的学生
select * from t_student 
where UNIX_TIMESTAMP(concat_ws('-',year(now()),month(sage),day(sage))) between
UNIX_TIMESTAMP(date_sub(now(),interval dayofweek(now())-1 day)) and 
UNIX_TIMESTAMP(date_add(now(),interval 7-dayofweek(now()) day));

#47.查询下周过生日的学生
select * from t_student 
where UNIX_TIMESTAMP(concat_ws('-',year(now()),month(sage),day(sage))) between
UNIX_TIMESTAMP(date_sub(now(),interval dayofweek(now())-8 day)) and 
UNIX_TIMESTAMP(date_add(now(),interval 14-dayofweek(now()) day));

#48.查询本月过生日的学生
select * from t_student where month(sage)=month(now());


#49-.查询下月过生日的学生(下个月可能是下一年)
select * from t_student 
where if(month(now())=12,12-month(sage),month(sage))+1=month(now());

行列转换
create table row2col(
	name varchar(22),
	subj varchar(22),
	score int(4)
)charset=utf8

truncate table row2col;
insert into row2col values('张三','yuwen',82);
insert into row2col values('张三','shuxue',90);
insert into row2col values('张三','yingyu',78);
insert into row2col values('李四','yuwen',82);
insert into row2col values('李四','shuxue',90);
insert into row2col values('李四','yingyu',78);
insert into row2col values('王五','yuwen',82);
insert into row2col values('王五','shuxue',90);
insert into row2col values('王五','yingyu',78);

select * from row2col;

行转列

select t1.name,t1.score yuwen,t2.score shuxue,t3.score yingyu  from
(select name,score from row2col where subj='yuwen')t1
inner join
(select name,score from row2col where subj='shuxue')t2
on t1.name=t2.name
inner join 
(select name,score from row2col where subj='yingyu')t3
on t2.name=t3.name;      


列换行
select name,'yuwen' as subj,yuwen from col2row
union all
select name,'shuxue' as subj,shuxue from col2row
union all
select name,'yingyu' as subj,yingyu from col2row
order by name