1.显示的字段要有老师name, age 每个老师所带的学生人数,2 只列出老师age为40以下学生age为12以上的记录

drop table if exists tea_stu;drop table if exists teacher;drop table if exists student;create table teacher(teaID int primary key,name varchar(50),age int);create table student(stuID int primary key,name varchar(50),age int);create table tea_stu(teaID int references teacher(teaID),stuID int references student(stuID));delete from teacher;delete from student;delete from tea_stu;insert into teacher(teaID, name, age) values(1,'zxx',45), (2,'lhm',25), (3,'wzg',26) , (4,'tg',27);insert into student(stuID, name, age) values(1,'wy',11), (2,'dh',25) , (3,'ysq',26) , (4,'mxc',27);insert into tea_stu(teaID, stuID) values(1,1), (1,2), (1,3);insert into tea_stu(teaID, stuID) values(2,2), (2,3), (2,4);insert into tea_stu(teaID, stuID) values(3,3), (3,4), (3,1);insert into tea_stu(teaID, stuID) values(4,4), (4,1), (4,2) , (4,3);select * from teacher;select * from student;select * from tea_stu;/*1.显示的字段要有老师name, age 每个老师所带的学生人数  *2.只列出老师age为40以下学生age为12以上的记录 */ /*要会统计分组信息*/select teaid, count(*) from tea_stu group by teaid;+-------+----------+| teaid | count(*) |+-------+----------+|     1 |        3 ||     2 |        3 ||     3 |        3 ||     4 |        4 |+-------+----------+select * from tea_stu,teacher where tea_stu.teaID=teacher.teaID;+-------+-------+-------+------+------+| teaID | stuID | teaID | name | age  |+-------+-------+-------+------+------+|     1 |     1 |     1 | zxx  |   45 ||     1 |     2 |     1 | zxx  |   45 ||     1 |     3 |     1 | zxx  |   45 ||     2 |     2 |     2 | lhm  |   25 ||     2 |     3 |     2 | lhm  |   25 ||     2 |     4 |     2 | lhm  |   25 ||     3 |     3 |     3 | wzg  |   26 ||     3 |     4 |     3 | wzg  |   26 ||     3 |     1 |     3 | wzg  |   26 ||     4 |     4 |     4 | tg   |   27 ||     4 |     1 |     4 | tg   |   27 ||     4 |     2 |     4 | tg   |   27 ||     4 |     3 |     4 | tg   |   27 |+-------+-------+-------+------+------+/*再接着去掉大于40的老师：*/select * from tea_stu,teacher where tea_stu.teaID=teacher.teaID and teacher.age<=40;+-------+-------+-------+------+------+| teaID | stuID | teaID | name | age  |+-------+-------+-------+------+------+|     2 |     2 |     2 | lhm  |   25 ||     2 |     3 |     2 | lhm  |   25 ||     2 |     4 |     2 | lhm  |   25 ||     3 |     3 |     3 | wzg  |   26 ||     3 |     4 |     3 | wzg  |   26 ||     3 |     1 |     3 | wzg  |   26 ||     4 |     4 |     4 | tg   |   27 ||     4 |     1 |     4 | tg   |   27 ||     4 |     2 |     4 | tg   |   27 ||     4 |     3 |     4 | tg   |   27 |+-------+-------+-------+------+------+/*再对上面的结果去掉小于12的学生*/select * from (select tea_stu.teaID, tea_stu.stuID, teacher.name, teacher.agefrom tea_stu,teacher where tea_stu.teaID=teacher.teaID and teacher.age<40) as t,student where t.stuid=student.stuid and student.age>12;+-------+-------+------+------+-------+------+------+| teaID | stuID | name | age  | stuID | name | age  |+-------+-------+------+------+-------+------+------+|     2 |     2 | lhm  |   25 |     2 | dh   |   25 ||     4 |     2 | tg   |   27 |     2 | dh   |   25 ||     2 |     3 | lhm  |   25 |     3 | ysq  |   26 ||     3 |     3 | wzg  |   26 |     3 | ysq  |   26 ||     4 |     3 | tg   |   27 |     3 | ysq  |   26 ||     2 |     4 | lhm  |   25 |     4 | mxc  |   27 ||     3 |     4 | wzg  |   26 |     4 | mxc  |   27 ||     4 |     4 | tg   |   27 |     4 | mxc  |   27 |+-------+-------+------+------+-------+------+------+/*再对上面的结果进行统计，显示的是老师的id和组信息*/select t.teaID,count(*) from (select tea_stu.teaID, tea_stu.stuID, teacher.name, teacher.agefrom tea_stu,teacher where tea_stu.teaID=teacher.teaID and teacher.age<40) as t,student where t.stuid=student.stuid and student.age>12group by t.teaID;+-------+----------+| teaID | count(*) |+-------+----------+|     2 |        3 ||     3 |        2 ||     4 |        3 |+-------+----------+/*然后对上面的东西进行改写，改写成显示老师的名字*/select teacher.name,t2.c from(select t.teaID,count(*) c from (select tea_stu.teaID, tea_stu.stuID, teacher.name, teacher.age from tea_stu,teacher where tea_stu.teaID=teacher.teaID and teacher.age<40) as t,student where t.stuid=student.stuid and student.age>12group by t.teaID) as t2,teacherwhere teacher.teaID=t2.teaID;+------+---+| name | c |+------+---+| lhm  | 3 || wzg  | 2 || tg   | 3 |+------+---+/*第二种写法：*/select teacher.teaID, teacher.name, t1.total from teacher,(select teaID,count(tea_stu.stuID) total from tea_stu, student where tea_stu.stuID = student.stuID and student.age>12group by teaID ) as t1where teacher.teaID = t1.teaID and teacher.age<40 ;