mysql连接查询作业答案_mysql之连接查询小作业

#数据准备

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 "男女人数的差值";