-- 创建数据库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; -- 检查总行数
-- 创建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; -- 检查总行数
-- 创建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; -- 检查总行数
-- 创建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; -- 检查总行数
练习题
-- ----------------查询提高------------------ -- 1、查询考试成绩不及格的学生信息 select s_id from sc where score <60; select * from stu where s_id in (select s_id from sc where score <60); select distinct stu.* from sc left join stu on stu.s_id=sc.s_id where score < 60; #笛卡儿积 不能有非匹配项 select distinct stu.* from stu , sc where stu.s_id = sc.s_id and score <60; -- 2、查询姓‘李’的老师的数量 ##模糊查询 select count(t_id) from te where t_name like '李%'; -- 3、查询没有出现过考试成绩不及格的学生：s_id,c_name,score ##1、考试成绩大于60 的学生 select distinct s_id from sc where score<60; select stu.s_id,stu.s_name,co.c_name,score from stu left join sc on stu.s_id = sc.s_id left join co on sc.c_id = co.c_id where stu.s_id not in ( select distinct s_id from sc where score<60); -- 4、查询平均成绩大于70分的课程 #第一种方法 select c_name, avg(score) ,co.c_id from sc left join co on sc.c_id = co.c_id group by sc.c_id having avg(score)>70; #第二种方法 select c_id from sc group by c_id having avg(score)>70; select c_name ,c_id from co where c_id in ( select c_id from sc group by c_id having avg(score)>70); -- 5、查询平均成绩大于60分的学生:s_id,s_name,平均成绩 select stu.s_id , s_name , avg(score) from sc left join stu on stu.s_id = sc.s_id group by sc.s_id having avg(score)>60; ##第二种方法 select s_id,avg(score) from sc group by s_id having avg(score)>60; select s_id , s_name from stu where s_id in (select s_id from sc group by s_id having avg(score)>60); -- 6、查询至少选修了两门课程的学生：s_id,s_name,选课门数 select sc.s_id,s_name,count(sc.s_id) from sc left join stu on sc.s_id = stu.s_id group by sc.s_id having count(sc.s_id) >=2; ##第二种方法 select s_id ,count(c_id) from sc group by s_id having count(c_id) >= 2; select s.*,s_name from (select s_id ,count(c_id) from sc group by s_id having count(c_id) >= 2) s left join stu on s.s_id=stu.s_id; -- 7、查询选修了全部课程的学生信息 ##思路：先查询出总共的课程门数有多少，然后连接学生表和成绩表，通过学生id分组，当成绩表中的选修门数等于所有的课程数时输出 #查找课程门数 select count(c_id) from co; select stu.*,count(sc.c_id) 选修门数 from sc left join stu on sc.s_id = stu.s_id group by s_id having 选修门数=(select count(c_id) from co); -- 8、查询每门课程被选修的学生数 ##课程表中的 select co.c_id,c_name,count(*) 选修人数 from co left join sc on co.c_id = sc.c_id group by co.c_id; -- 9、查询90后学生的男女人数 ##第一种方法 select sum(datediff(s_birth,'1989-12-12')>0) 90后人数,s_sex #90后的人数 from stu group by s_sex; ##第二种方法 select s_sex,count(s_id) 人数 from stu where year(s_birth)>=1990 group by s_sex; -- 10、查询至少有3门课程及格的学生 select sc.s_id ,s_name,count(score>60) from sc left join stu on sc.s_id = stu.s_id where score >=60 group by sc.s_id having count(score>60)>=3; ##查询每位同学的及格门数 select s_id , count(c_id) 及格门数 from sc where score >= 60 group by s_id having 及格门数>=3; #子查询 select s.s_id,s_name ,及格门数 from ( select s_id , count(c_id) 及格门数 from sc where score >= 60 group by s_id having 及格门数>=3) s left join stu on s.s_id = stu.s_id; -- 11、查询所有学生的s_id,s_name,选课门数,所选课程总成绩 select stu.s_id,s_name,count(c_id),sum(score) from stu left join sc on stu.s_id = sc.s_id group by stu.s_id; -- 12、查询至少有两门成绩不及格的学生：s_id,s_name,不及格门数,平均成绩 #第一种方法 select sc.s_id,s_name,count(score<60),avg(score) from sc left join stu on sc.s_id= stu.s_id where score <60 group by sc.s_id having count(score<60)>=2; #第二种方法 select sc.s_id,s_name,count(c_id) 不及格门数,avg(score) from sc left join stu on stu.s_id= sc.s_id group by sc.s_id having sum(score<60)>=2; select sum(score <60) from sc; -- 13、学过"张三"老师课程的学生信息 #四表连接顺序， 教师，课程，成绩，学生 #方法一 select * from te left join co on te.t_id = co.t_id left join sc on co.c_id = sc.c_id left join stu on sc.s_id = stu.s_id where t_name = '张三'; #方法二 子查询 select * from stu where s_id in (select s_id from te left join co on te.t_id = co.t_id left join sc on co.c_id = sc.c_id where t_name = '张三'); -- 14、查询所有学生不同课程的成绩及平均成绩：s_id,语文,数学,英语,平均成绩 #第一种方法 select s_id,sum(case when c_id='01' then score else 0 end) 语文, sum(case when c_id='02' then score else 0 end) 数学, sum(case when c_id='03' then score else 0 end) 英语, avg(score) 平均成绩 from sc group by s_id; select stu.s_id,sum(case when c_id='01' then score else 0 end) 语文, sum(case when c_id='02' then score else 0 end) 数学, sum(case when c_id='03' then score else 0 end) 英语, avg(score) 平均成绩 from stu left join sc on stu.s_id=sc.s_id group by stu.s_id; ##第二种方法 select s_id,sum(if( c_id='01',score ,0)) 语文, sum(if( c_id='02',score ,0)) 数学, sum(if( c_id='03',score ,0)) 英语, avg(score) 平均成绩 from sc group by s_id; -- 15、查询不同老师所授不同课程的平均成绩 select t_name,avg(score),c_name from te left join co on te.t_id = co.t_id left join sc on co.c_id = sc.c_id group by te.t_id,co.c_id; -- 16、查询和01号同学所学课程完全一样的学生信息 select group_concat(c_id) from sc group by s_id having s_id = '01'; #找出01所选修的课程 select c_id from sc where s_id='01'; select stu.s_id,group_concat(c_id order by c_id) 选修课程 from stu left join sc on sc.s_id= stu.s_id group by stu.s_id having 选修课程 = (select group_concat(c_id order by c_id) from sc group by s_id having s_id = '01'); -- 17、查询至少有一门课与01号同学所学课程一样的学生信息 select c_id from sc where s_id='01'; select distinct stu.* from stu left join sc on sc.s_id= stu.s_id where c_id in (select c_id from sc where s_id='01'); -- 18、查询选修了01号课程并且也选修了02号课程的学生信息 ##第一种方法 select s_id,count(c_id) from sc where c_id in (01,02) group by s_id having count(c_id)=2; select stu.* from sc left join stu on sc.s_id= stu.s_id where c_id in (01,02) group by sc.s_id having count(c_id)=2; ##第二种方法 select distinct stu.* from sc left join stu on sc.s_id= stu.s_id where stu.s_id in (select s_id from sc where c_id='01') and stu.s_id in(select s_id from sc where c_id='02'); ##第三种方法 select stu.*,group_concat(c_id order by c_id) 选修课程 from sc left join stu on sc.s_id= stu.s_id group by stu.s_id having 选修课程 like '01,02%'; #第四种方法 select stu.* from sc left join stu on sc.s_id= stu.s_id group by stu.s_id having sum(c_id=01)+sum(c_id=02)=2; having sum(c_id =(01,02))=2; -- 19、查询01号课程比02号课程分数高的学生信息及成绩 #第一步 select s_id,score from sc where c_id = '01'; select s_id,score from sc where c_id = '02'; #第二步 select * from stu left join (select s_id,score from sc where c_id = '01') s1 on stu.s_id=s1.s_id left join (select s_id,score from sc where c_id = '02') s2 on stu.s_id=s2.s_id where s1.score>s2.score; -- 20、查询选修了01号课程但是没有选修02号课程的学生信息 select distinct stu.* from sc left join stu on sc.s_id= stu.s_id where stu.s_id in (select s_id from sc where c_id='01') and stu.s_id not in(select s_id from sc where c_id='02'); #第二种方法 select stu.* from sc left join stu on sc.s_id= stu.s_id group by stu.s_id having sum(c_id=01)=1 and sum(c_id=02)=0; -- 21、查询选修'张三'老师所授课程的学生中，分数最高的学生信息及成绩 select stu.*,score from te left join co on te.t_id = co.t_id left join sc on co.c_id = sc.c_id left join stu on sc.s_id = stu.s_id where t_name = '张三' order by score desc limit 1; select stu.*,max(score) from te left join co on te.t_id = co.t_id left join sc on co.c_id = sc.c_id left join stu on sc.s_id = stu.s_id where t_name = '张三'; -- 22、查询各科成绩最高分,最低分,平均分,合格率:cname select c_name ,max(score),min(score),avg(score),sum(score>=60)/count(co.c_id) 合格率 from co left join sc on sc.c_id = co.c_id group by co.c_id; ##考试成绩合格的人数 select sum(score >=60) from sc; -- 23、统计各科成绩各分数段人数 select co.c_id ,c_name, sum(score >=85) '[100,85]', sum(score >=70 and score <85) '[70,85]', sum(score >=60 and score <70 ) '[60,70]', sum( score <60 ) '[0,60]' from co left join sc on co.c_id = sc.c_id group by co.c_id; select c_id , sum(case when score >=85 then 1 else 0 end) '[100,85]', sum(case when score between 70 and 85 then 1 else 0 end) '[70,85]', sum(case when score between 60 and 70 then 1 else 0 end) '[60,70]', sum(case when score <60 then 1 else 0 end) '[0,60]' from sc group by c_id; -- 24、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； select * from sc group by s_id having max(score)<70; select s_name,c_name,score from stu left join sc on sc.s_id= stu.s_id left join co on sc.c_id = co.c_id where stu.s_id not in ( select s_id from sc group by s_id having max(score)<70); /* select s_name,c_name,score from sc left join stu on sc.s_id= stu.s_id left join co on sc.c_id = co.c_id where score>70 group by score;*/ -- 25、按各科成绩进行排序，并显示排名；s_id,c_id,score,排名 use school; set @ss:=0; select a.*,@ss:=@ss+1 as 排名 from (select c_id,s_id,score from sc group by c_id,s_id order by c_id,score desc) a; select c_id,s_id,score from sc group by c_id,s_id order by c_id,score desc; select @ss;