题目
question.png
要求：
查出“计算机系”的所有学生信息。
查出“韩顺平”所在的院系信息。
查出在“行政楼”办公的院系名称。
查出男生女生各多少人。
查出人数最多的院系信息。
查出人数最多的院系的男女生各多少人。
查出跟“罗弟华”同籍贯的所有人。
查出有“河北”人就读的院系信息。
查出跟“河北女生”同院系的所有学生的信息。
php_stu.png
t_col.png
1. select php_stu.* from php_stu join t_col on t_col.id = php_stu.c_id where t_col.id = 1;
2. select * from t_col where id = (select c_id from php_stu where s_name = '韩顺平');
3. select * from t_col where c_add like '行政楼%'
4. select count(*),gender from php_stu GROUP BY gender;
5. select t_col.* ,COUNT(c_id)from t_col join php_stu on c_id = t_col.id
GROUP BY c_id HAVING COUNT(c_id) order by COUNT(c_id) desc limit 1;
select * from t_col where id = (select c_id from php_stu GROUP BY c_id limit 1);
6. select gender,COUNT(*) from php_stu where c_id = (select c_id from php_stu GROUP BY c_id limit 1) GROUP BY gender;
7. select * from php_stu where addr = (select addr from php_stu where s_name = '罗弟华') and s_name != '罗弟华';
select a.* from php_stu as a join php_stu as b on a.addr = b.addr and b.s_name = '罗弟华';
8. select * from t_col where id in (select c_id from php_stu where addr = '河北')
9. select b.* from php_stu as a join php_stu as b on a.c_id = b.c_id and a.addr = '河北' and a.gender = '女 '
