group by 只出一行记录 concat having where
本练习题测试在mysql5.7
问题
已知有如下4张表：
学生表：student(学号,学生姓名,出生年月,性别)
成绩表：score(学号,课程号,成绩)
课程表：course(课程号,课程名称,教师号)
教师表：teacher(教师号,教师姓名)
1、自行完成建表并作数据插入（数据可参考我后面的代码）
2、查询练习
查询名字 中有 ‘小‘ 字的
查询名字中有 ‘小‘ 字的人的个数
查询学号为“20200301”的总成绩
查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分
查询每门课程被选修的学生数
查询男生、女生人数
查询平均成绩大于60分学生的学号和平均成绩
查询至少选修两门课程的学生学号
查询不及格的课程并按课程号从大到小排列
查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
查询两门以上不及格课程的同学的学号及其平均成绩
复杂查询
查询所有课程成绩小于60分学生的学号、姓名
查询没有学全所有课的学生的学号、姓名
查询出只选修了两门课程的全部学生的学号和姓名
1998年出生的学生名单
查询各科成绩前两名的记录
4.多表查询
查询所有学生的学号、姓名、选课数、总成绩
查询学生的选课情况：学号，姓名，课程号，课程名称
使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
代码
准备：
建表
create database school character set utf8;
use school;
CREATE TABLE student(
stu_id int(12) primary key,
stu_name varchar(10) ,
stu_birth date,
stu_gender varchar(1)
) ENGINE= InnoDB DEFAULT CHARSET=UTF8;
CREATE TABLE teacher(
teacher_id int(12) primary key,
teacher_name varchar(10)
)ENGINE= InnoDB DEFAULT CHARSET=UTF8;
CREATE TABLE course(
course_id int(8) primary key,
course_name varchar(20),
teacher_id int(12),
FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id)
)ENGINE= InnoDB DEFAULT CHARSET=UTF8;
CREATE TABLE score(
stu_id int(12),
course_id int(8),
grade double,
primary key(stu_id,course_id),
CONSTRAINT fk_score_stu_sid FOREIGN KEY(stu_id) REFERENCES student(stu_id),
CONSTRAINT fk_score_course_cid FOREIGN KEY(course_id) REFERENCES course(course_id)
)ENGINE= InnoDB DEFAULT CHARSET=UTF8;
以下是一些方法
primary key(stu_id,course_id)// 联合主键
ALTER TABLE score ADD CONSTRAINT primary key(stu_id,course_id);
select * from information_schema.key_column_usage where TABLE_NAME='course';
alter table course drop foreign key course_ibfk_1;
alter table course add CONSTRAINT fk_crouse_teacher_tid FOREIGN KEY(teacher_id)
REFERENCES teacher(teacher_id);
ALTER TABLE STUDENT MODIFY stu_name varchar(10) NOT null;
ALTER TABLE teacher MODIFY teacher_name varchar(10) NOT null;
数据插入
学生表
insert into student values('20200301' , '小猴子' , '1999-04-01' , '男');
insert into student values('20200302' , '小李子' , '1998-12-21' , '男');
insert into student values('20200303' , '貂蝉' , '1991-12-21' , '女');
insert into student values('20200201' , '诸葛亮' , '1997-05-20' , '男');
insert into student values('20200202' , '甄姬' , '1997-05-20' , '女');
insert into student values('20200304' , '吕布' , '2000-05-20' , '男');
insert into student values('20200306' , '鲁班小师' , '1999-04-01' , '男');
教师表里添加数据
insert into teacher values('10001' , '孟扎扎');
insert into teacher values('10002' , '马腾腾');
insert into teacher values('10003' , '刘晓晓');
insert into teacher values('10004' , '铁蛋蛋');
insert into teacher values('10005' , '华鸡鸡');
课程表
insert into course values('20210001' , '语文' , '10002');
insert into course values('20210002' , '数学' , '10001');
insert into course values('20210003' , '英语' , '10003');
insert into course values('20210004' , '数据库' , '10004');
insert into course values('20210005' , '语言学' , '10005');
成绩表
insert into score values('20200301' , '20210001' , 80);
insert into score values('20200301' , '20210002' , 90);
insert into score values('20200301' , '20210003' , 99);
insert into score values('20200302' , '20210001' , 59);
insert into score values('20200302' , '20210002' , 80);
insert into score values('20200302' , '20210003' , 68);
insert into score values('20200303' , '20210001' , 90);
insert into score values('20200303' , '20210004' , 70);
insert into score values('20200201' , '20210002' , 99);
insert into score values('20200201' , '20210001' , 50);
insert into score values('20200201' , '20210004' , 80);
insert into score values('20200202' , '20210002' , 70);
insert into score values('20200304' , '20210001' , 76);
insert into score values('20200304' , '20210003' , 80);
insert into score values('20200301' , '20210005' , 100);
insert into score values('20200304' , '20210005' , 50);
insert into score values('20200306' , '20210001' , 50);
insert into score values('20200306' , '20210002' , 40);
insert into score values('20200306' , '20210003' , 42);
查询名字 中有 ‘小‘ 字的
select * from student where stu_name like ‘%小%’;
查询名字中有 ‘小‘ 字的人的个数
select count(*) from student where stu_name like ‘%小%’;
查询学号为“20200301”的总成绩
select sum(grade) from score where stu_id = ‘20200301’ ;
查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分
select course_id , max(grade) , min(grade) from score group by course_id;
查询每门课程被选修的学生数
select course_id,count(*) from score group by course_id;
查询男生、女生人数
select stu_gender,count(*) from student group by stu_gender;
查询平均成绩大于60分学生的学号和平均成绩
select stu_id,avg(grade) from score group by stu_id having avg(grade)>60;
查询至少选修两门课程的学生学号
select stu_id from score group by stu_id having count(*)>=2 ;
查询不及格的课程并按课程号从大到小排列
select course_id from score where grade<60 order by course_id desc;
查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select course_id ,avg(grade) as avgscore from score group by course_id order by avgscore asc,course_id desc;
查询两门以上不及格课程的同学的学号及其平均成绩
select stu_id,avg(grade) from score where grade<60 group by stu_id having count(*)>=2 ;
复杂查询
查询所有课程成绩小于60分学生的学号、姓名
select stu_id,stu_name from student where stu_id in(select stu_id from score where grade<60);
查询没有学全所有课的学生的学号、姓名
select stu_id,stu_name from student where stu_id in(select stu_id from score group by stu_id having count(*) < (select count(*) from course)) ;
查询出只选修了两门课程的全部学生的学号和姓名
select stu_id,stu_name from student where stu_id in(select stu_id from score group by stu_id having count(*)=2);
1998年出生的学生名单
select stu_id,stu_name from student where year(stu_birth)=1998;
查询各科成绩前两名的记录
(select * from score where course_id= ‘20210001’ order by grade desc limit 2)
union all
(select * from score where course_id= ‘20210002’ order by grade desc limit 2)
union all
(select * from score where course_id = ‘20210003’ order by grade desc limit 2);
4.多表查询
查询所有学生的学号、姓名、选课数、总成绩
select st.stu_id,st.stu_name,count(sc.course_id),sum(sc.grade) from student st,score sc where st.stu_id = sc.stu_id group by sc.stu_id;
select a.学号,a.姓名,count(b.课程号) ,sum(b.成绩)
from student st join score as b
on a.学号 = b.学号
group by a.学号;
查询学生的选课情况：学号，姓名，课程号，课程名称
select st.stu_id,st.stu_name,co.course_id,co.course_name from student st,course co,score sc where st.stu_id = sc.stu_id and sc.course_id = co.course_id ;
使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
-- 考察case表达式
select sc.course_id,co.course_name,
sum(case when grade between 85 and 100
then 1 else 0 end) as '[100-85]',
sum(case when grade >=70 and grade<85
then 1 else 0 end) as '[85-70]',
sum(case when grade>=60 and grade<70
then 1 else 0 end) as '[70-60]',
sum(case when grade<60 then 1 else 0 end) as '[<60]'
from score sc, course co
where sc.course_id=co.course_id
group by sc.course_id,co.course_name;
create table scoreo(
sid int(10),
cid int(10),
grade double
)
insert into scoreo values('10001','20210001',80);
insert into scoreo values('10001','20210002',90);
insert into scoreo values('10001','20210003',99);
insert into scoreo values('10002','20210002',60);
insert into scoreo values('10002','20210003',80);
insert into scoreo values('10003','20210001',80);
insert into scoreo values('10003','20210002',80);
insert into scoreo values('10003','20210003',80);
select sid,
(case cid when '20210001' then grade else 0 end) as '课程号0001',
(case cid when '20210002' then grade else 0 end) as '课程号0002',
(case cid when '20210003' then grade else 0 end) as '课程号0003'
from scoreo;
select sid,
sum(case cid when '20210001' then grade else 0 end) as '课程号0001',
sum(case cid when '20210002' then grade else 0 end) as '课程号0002',
sum(case cid when '20210003' then grade else 0 end) as '课程号0003'
from scoreo
group by sid;
select sid,
max(case cid when '20210001' then grade else 0 end) as '课程号0001',
max(case cid when '20210002' then grade else 0 end) as '课程号0002',
max(case cid when '20210003' then grade else 0 end) as '课程号0003'
from scoreo
group by sid;
2206
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
