关于MySql基础学习的链接,如下:
-- 创建数据库school
create database school;
-- 选择进入school数据库
use school;
-- ------------建表导数-------------
-- 创建stu
create table stu(
s_id varchar(10) primary key,
s_name varchar(10) not null,
s_birth date,
s_sex varchar(10));
-- 导入数据
insert into stu values
('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1992-04-21' , '女'),
('08' , '王菊' , '1990-01-20' , '女');
select * from stu; -- 检查数据
select count(*) from stu; -- 检查总行数8
-- 创建co
create table co(
c_id varchar(10) primary key,
c_name varchar(10),
t_id varchar(10));
-- 导入数据
insert into co values
('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');
select * from co; -- 检查数据
select count(*) from co; -- 检查总行数3
-- 创建te
create table te(
t_id varchar(10) primary key,
t_name varchar(10));
-- 导入数据
insert into te values
('01' , '张三'),
('02' , '李四'),
('03' , '王五');
select * from te; -- 检查数据
select count(*) from te; -- 检查总行数3
-- 创建sc
create table sc(
s_id varchar(10),
c_id varchar(10),
score int);
-- 导入数据
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);
select * from sc; -- 检查数据
select count(*) from sc; -- 检查总行数18
上述操作,我们在所创建的数据库中,分别创建了stu学生表,co课程表,sc成绩表,教师表te。
-- ----------------------------------------------------------------------------------
-- 1、查询"01"课程比"02"课程成绩高的学生信息及课程分数
# 涉及到了两张表,一个stu,一个sc
# 在sc表中分别筛选出01和02的课程成绩,分别作为t1和t2表,对这两个表进行表连接,得到的结果与stu表进行连接
select stu.*,sc.c_id,sc.score
from
(select * from sc where c_id = '01') as t1
inner join (select *from sc where c_id = '02') as t2
on t1.s_id=t2.s_id
inner join stu on t1.s_id=stu.s_id
inner join sc on stu.s_id = sc.s_id
where t1.score > t2.score;
-- ----------------------------------------------------------------------------------
-- 2、练习:查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select stu.*,sc.c_id,sc.score
from
(select * from sc where c_id = '01') as t1
inner join (select *from sc where c_id = '02') as t2
on t1.s_id=t2.s_id
inner join stu on t1.s_id=stu.s_id
inner join sc on stu.s_id = sc.s_id
where t1.score < t2.score;
-- ----------------------------------------------------------------------------------
-- 3、查询平均成绩大于等于60分的同学的学生编号、学生姓名和平均成绩
# 找出每一个学生的信息
select stu.s_id,s_name,avg(score)
from stu left join sc on stu.s_id = sc.s_id
# 按照学生表里面的编号进行分组
group by stu.s_id
having avg(score) >= 60;
-- ----------------------------------------------------------------------------------
-- 4、练习:查询平均成绩小于60分的同学的学生编号、学生姓名和平均成绩
select stu.s_id,s_name,avg(score)
from stu left join sc on stu.s_id = sc.s_id
# 按照学生表里面的编号进行分组
group by stu.s_id
having avg(score) < 60;
-- ----------------------------------------------------------------------------------
-- 5、查询"李"姓老师的教授的课程数量
select count(c_id)
from te left join co on te.t_id = co.t_id
where t_name like '李%';
-- ----------------------------------------------------------------------------------
-- 6、练习:查询名字中含有"风"字的学生信息
select *
from stu
where s_name like '%风%';
-- ----------------------------------------------------------------------------------
-- 7、查询学过"张三"老师授课的同学的信息
# 学生信息在stu ,"张三"老师在te表,两个表需要连接,两个表没有相同的字段,需要中间表进行连接
select *
from stu
where s_id in( select s_id
from (select t_id,s_id from sc left join co on sc.c_id = co.c_id) as t1 left join te on t1.t_id = te.t_id
where t_name = '张三');
-- ----------------------------------------------------------------------------------
-- 练习:查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
select stu.* ,score
from stu
left join sc on stu.s_id = sc.s_id
left join co on sc.c_id = co.c_id
left join te on co.t_id = te.t_id
where t_name = '张三'
order by score desc
limit 1;
# 或者可以这样写
select stu.*,score
from stu
left join sc on stu.s_id = sc.s_id
left join co on sc.c_id = co.c_id
left join te on co.t_id = te.t_id
where t_name = '张三' and score = (select max(score)
from stu
left join sc on stu.s_id = sc.s_id
left join co on sc.c_id = co.c_id
left join te on co.t_id = te.t_id
where t_name = '张三' );
-- ----------------------------------------------------------------------------------
-- 8、练习:查询没学过"张三"老师授课的同学的信息
select *
from stu
where s_id not in( select s_id
from (select t_id,s_id from sc left join co on sc.c_id = co.c_id) as t1 left join te on t1.t_id = te.t_id
where t_name = '张三');
-- ----------------------------------------------------------------------------------
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select stu.*,group_concat(c_id order by c_id)
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having group_concat(c_id order by c_id) like '01,02%';
-- ----------------------------------------------------------------------------------
-- 练习:查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select stu.*
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having group_concat(c_id order by c_id) like '%01%' and group_concat(c_id order by c_id) not like '%02%';
# 或者
select *
from stu
where s_id in (select s_id from sc where c_id = 01) and s_id not in (select s_id from sc where c_id = 02);
#或者
select stu.*
from stu left join sc on stu.s_id = sc.s_id
where c_id in ('01','02')
group by stu.s_id
having group_concat(c_id) = '01';
# 或者
select stu.*
from stu left join sc on stu.s_id = sc.s_id
where c_id = '01' and stu.s_id not in (select s_id from sc where c_id = '02');
-- ----------------------------------------------------------------------------------
-- 10、查询选修了全部课程的学生信息
select stu.*,group_concat(c_id order by c_id)
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having group_concat(c_id order by c_id) like '01,02,03';
#或者
select *
from stu
where s_id in (select s_id from sc group by s_id having count(c_id)= (select count(c_id) from co ));
# 或者
select stu.*,count(c_id) as 选课门数
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having count(c_id) = (select count(c_id) from co);
-- ----------------------------------------------------------------------------------
-- 11、练习:查询没有学全所有课程的同学的信息
select stu.*,count(c_id) as 选课门数
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having count(c_id) < (select count(c_id) from co);
-- ----------------------------------------------------------------------------------
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct stu.*
from stu left join sc on stu.s_id = sc.s_id
where c_id in (select c_id from sc where s_id ='01')and stu.s_id != '01';
-- ----------------------------------------------------------------------------------
-- 13、练习:查询和"01"号的同学学习的课程完全相同的其他同学的信息
select s_id,group_concat(c_id)
from sc
group by s_id
having group_concat(c_id) = (select group_concat(c_id)
from stu left join sc on stu.s_id = sc.s_id
where sc.s_id ='01');
-- ----------------------------------------------------------------------------------
-- 14、查询所有学生的课程及分数情况(一维转二维)
select stu.s_id,
sum(if(c_id='01',score, 0)) as '01',
sum(if(c_id='02',score, 0)) as '02',
sum(if(c_id='03',score, 0)) as '03'
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id;
# 或者
select stu.s_id,
sum(case when c_id= '01' then score else 0 end )as '01',
sum( case when c_id= '02' then score else 0 end) as '02',
sum(case when c_id= '03' then score else 0 end) as '03'
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id;
# 或者
select stu.s_id,
ifnull(sum((c_id='01') * score),0) as '01', # (c_id='01') 是一个判断表达式,sql语句中,结果为真,返回1,结果为假,返回0
ifnull(sum((c_id='02') * score),0) as '02',
ifnull(sum((c_id='03') * score),0) as '03'
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id;
-- ----------------------------------------------------------------------------------
-- 15、练习:按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select stu.s_id,
sum(if(c_id='01',score, 0)) as '01',
sum(if(c_id='02',score, 0)) as '02',
sum(if(c_id='03',score, 0)) as '03',
ifnull(avg(score),0) as 平均成绩
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
order by avg(score) desc;
#或者
select stu.s_id,
ifnull(sum((c_id='01') * score),0) as '01', # (c_id='01') 是一个判断表达式,sql语句中,结果为真,返回1,结果为假,返回0
ifnull(sum((c_id='02') * score),0) as '02',
ifnull(sum((c_id='03') * score),0) as '03',
ifnull(avg(score),0) as 平均成绩
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
order by avg(score) desc;
-- ----------------------------------------------------------------------------------
-- 16、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
#及格为:>=60,中等为:70-80,优良为:80-90,优秀为:>=90
select co.c_id,c_name,max(score),min(score),avg(score),
avg(score>=60) as 及格率, # 我们知道(score>=60)是返回1或者0,avg()就是先求和然后除以个数,求和就是多个1和多个0相加。
avg(score >= 70 and score < 80) as 中等率 ,
avg(score >=80 and score <90) as 优良率 ,
avg(score>=90) as 优秀率
from co left join sc on co.c_id=sc.c_id
group by co.c_id;
# 其次 及格率,还可以这么写,其他的类似。
sum(score>=60)/count(score)
-- ----------------------------------------------------------------------------------
-- 17、练习:统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select co.c_id,c_name,
sum((score>=85)) as '[100-85]',
sum((score >= 70 and score < 85) ) as '[85-70]',
sum((score >= 60 and score < 70) ) as '[60-70]',
sum((score <60 ) ) as '[0-60]',
concat(avg(score>=85) * 100,'%') as '[100-85]百分比',
concat(avg(score >= 70 and score < 85) * 100,'%') as '[85-70]百分比',
concat(avg(score >= 60 and score < 70) * 100,'%') as '[60-70]百分比',
concat(avg(score <60 ) * 100,'%') as '[0-60]百分比'
from co left join sc on co.c_id=sc.c_id
group by co.c_id;
-- ----------------------------------------------------------------------------------
-- 18、查询学生的总成绩并进行排名]
select s_id,sum(score) as 总成绩, row_number() over(order by sum(score) desc) as 排名
from sc
group by s_id;
-- ----------------------------------------------------------------------------------
-- 19、练习:查询每个学生平均成绩及其名次
select s_id,avg(score) as 平均成绩, row_number() over(order by avg(score) desc) as 排名
from sc
group by s_id;
-- ----------------------------------------------------------------------------------
-- 20、按各科成绩进行排序,并显示排名
select * ,rank() over(partition by c_id order by score desc ) as 排名
from sc;
-- ----------------------------------------------------------------------------------
-- 21、查询各科成绩前三名的记录
select*
from (select * ,rank() over(partition by c_id order by score desc ) as 排名 from sc) as t
where 排名<= 3;
-- ----------------------------------------------------------------------------------
-- 22、练习:查询每门功成绩最好的前两名
select*
from (select * ,rank() over(partition by c_id order by score desc ) as 排名 from sc) as t
where 排名<= 2;
-- ----------------------------------------------------------------------------------
-- 23、练习:查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select*
from (select * ,rank() over(partition by c_id order by score desc ) as 排名 from sc) as t
where 排名>= 2 and 排名 <=3;
-- ----------------------------------------------------------------------------------
-- 24、查询每门课程被选修的学生数
select c_id,count(c_id)
from sc
group by c_id;
-- ----------------------------------------------------------------------------------
-- 25、练习:统计每门课程的学生选修人数(超过5人的课程才统计)
#要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select c_id,count(c_id)
from sc
group by c_id
having count(c_id) >= 5;
-- ----------------------------------------------------------------------------------
-- 26、查询同名同姓学生名单,并统计同名人数
select s_name,count(s_name)-1
from stu
group by s_name;
-- ----------------------------------------------------------------------------------
-- 27、查询课程成绩在70分以上的姓名、课程名称和分数
select s_name,c_name,score
from stu inner join sc on stu.s_id = sc.s_id inner join co on sc.c_id = co.c_id
where score >70 ;
-- ----------------------------------------------------------------------------------
--28、查询任一门课程成绩在70分以上的姓名、课程名称和分数
select s_name,c_name,score
from stu inner join sc on stu.s_id = sc.s_id inner join co on sc.c_id = co.c_id
where stu.s_id in (select distinct s_id
from sc
where score >70);
-- ----------------------------------------------------------------------------------
-- 29、练习:查询出现过学生考试不及格的课程
select c_name,count(s_name)
from stu inner join sc on stu.s_id = sc.s_id inner join co on sc.c_id = co.c_id
where score < 60
group by c_name;
-- ----------------------------------------------------------------------------------
-- 30、查询课程不同、成绩相同的学生的学生编号、课程编号、学生成绩
select distinct t1.*
from sc as t1 join sc as t2 on t1.c_id != t2.c_id and t1.score =t2.score;
-- ----------------------------------------------------------------------------------
-- 31、查询本周过生日的学生
select *
from stu
where week(s_birth) = week(curdate());
-- ----------------------------------------------------------------------------------
-- 32、练习:查询下周过生日的学生
select *
from stu
where week(s_birth) = if(week(curdate()) = 54, 1, week(curdate())+1);
-- ----------------------------------------------------------------------------------
-- 33、查询本月过生日的学生
select *
from stu
where month(s_birth)= month(curdate());
-- ----------------------------------------------------------------------------------
-- 34、练习:查询下月过生日的学生
select *
from stu
where month(s_birth) = if(month(curdate()) = 12, 1, month(curdate())+1);