mysql的多表查询

一道面试题

--tableA  class

--id 班级id

--class_name  班级名称

 

--tableB  student

--id  学生id

--studnet_name  学生名字

--class_id  班级id

--gender  学生性别

--score   分数

 

create table t_class(

  id int primary key auto_increment,

  class_name varchar(12) not null

);

 

create table t_student(

 id int primary key auto_increment,

 student_name varchar(12) not null,

 class_id  int not null,

 gender varchar(6) not null,

 score int not null

);

 

insert into t_class(class_name)

  values("classA"),

  ("classB"),

  ("classC");

 

insert into t_student(student_name,class_id,gender,score) 

          values("kitty",1,"female",86),

                ("lucy",2,"male",96),

                ("momo",3,"female",46),

                ("Tom",1,"female",98),

                ("sch",2,"male",59),

                ("pitty",2,"female",61),

                ("lily",1,"female",76),

                ("stylr",2,"female",43),

                ("john",2,"male",78),

                ("vae",3,"female",68),

                ("jolin",2,"female",81),

                ("panpan",2,"female",49),

                ("looser",2,"male",89);

 

 

--问题1 找出某个班级女生多于3人的班级名称；

 

--问题2 把班级名称为 classA 班级的所有女生性别改为男生;

 

 

 

--问题3 求出班级名称为 classA 的人的平均分;

 

答案： select class_name from t_class where id=(select class_id from t_student where gender='female' group by class_id having count(class_id)=4 );

 

update t_student set gender='male' where class_id=(select id from t_class where t_class.class_name='classA');

 

select avg(score) from t_student studnt where class_id=(select id from t_class where t_class.class_name='classA');