#数据准备
drop table if exists class;
create table class(
class_no int(2) unsigned zerofill primary key auto_increment comment '班级编号',
class_name varchar(30) not null comment '班级名称'
);
insert into class values(1, '培优班');
insert into class values(2, '普通班');
insert into class values(3, '进阶班');
drop table if exists student;
create table student(
stu_no int(2) unsigned zerofill primary key auto_increment comment '学员编号',
stu_name varchar(30) not null comment '学员姓名',
stu_sex varchar(3) not null comment '学员性别',
stu_age tinyint(2) unsigned zerofill comment '学员年代',
grade double(5,2) zerofill comment '成绩',
class_no int(2) unsigned zerofill comment '所在班级编号',
foreign key(class_no) references class(class_no)
);
insert into student values(01, '李白', '男', 18, 60, 01);
insert into student values(02, '杜甫', '男', 20, 76, 01);
insert into student values(03, '张飞', '男', 32, 80, 02);
insert into student values(04, '韩信', '男', 26, 98, 02);
insert into student values(05, '了龙', '男', 27, 56, 02);
insert into student values(06, '大乔', '女', 17, 88, 01);
insert into student values(07, '小乔', '女', 16, 96, 01);
insert into student values(08, '小乔', '女', 16, 90, 01);
insert into student values(09, '关哥', '男', 32, 80, 02);
insert into student values(10, '刘备', '男', 36, 98, null);
alter table student drop foreign key `student_ibfk_1`;
*********************************************************************************************************************************************
1: 查询出‘培优班’的学员
// 子查询
select * from student where class_no = (select class_no from class where class_name = "培优班");
// 内连接
select * from student inner join class on student.class_no = class.class_no and class_name = "培优班";
// 自然连接
select * from student natural join class where class_name = "培优班";
2: 查询出‘普通班’成绩高于85分学员
select * from student where class_no = (select class_no from class where class_name = "普通班") and grade > 85;
select * from student inner join class on student.class_no = class.class_no and class_name = "普通班" and grade > 85;
select * from student natural join class where class_name = "普通班" and grade > 85;
3: 写出一个迪卡尔集的查询结果
select * from student cross join class;
select * from student inner join class;
4: 查询出每一个班级的平均分
// 包含班级号为null的结果
select class_no,avg(grade) from student group by class_no;
// 不包含班级号为null的结果
select class_no,avg(grade) from student inner join class using(class_no) group by class_no; // 不包括class_no为null的结果
5: 查询出每一个学员的姓名和所在的班级名称
select stu_name,class_name from student inner join class using(class_no);
select stu_name,class_name from student inner join class on student.class_no=class.class_no;
select class_name,stu_name from student natural join class;
6: 查询出培优班的最低分是多少
select min(grade) from student where class_no = (select class_no from class where class_name = "培优班");
select min(grade) from student inner join class on class.class_no = student.class_no and class_name = "培优班";
select min(grade) from student natural join class where class_name = "培优班";
7: 查询出培优班成绩最差的学员信息(成绩最差的不一定是一个人)
select * from student where class_no = (select class_no from class where class_name = "培优班") and grade = (select min(grade) from student where class_no = (select class_no from class where class_name = "培优班"));
select * from student where (class_no,grade) = (select class_no,min(grade) from student natural join class where class_name = "培优班");
8: 查询出普通班成绩最好的学员信息
select * from student natural join class where class_name = "普通班" order by grade desc limit 1;
(改下第七题的条件就好)
9: 查询出成绩最好的学员的姓名 以及 他们的班级名称
// 结果为多条记录的查询
select stu_name,class_name from student natural left join class where grade = (select max(grade) from student);
10: 查询出男女学员人数的差值
select (select count(*) from student where stu_sex = "男") - (select count(*) from student where stu_sex = "女") as "男女人数的差值";