数据库应用（MySQL）---实验2-子查询

**1.查找同名学生名单，显示同名学生学生的学号、姓名、班级༶**
select *
from xsxxb
where xm in(
						select xm
						from xsxxb
						group by xm
						having count(*)>1)
order by xm

**2.查找同班同名学生名单，显示同名学生学生的学号、姓名、班级**
select *
from xsxxb
where concat(bjmc,'-',xm) in(
						select concat(bjmc,'-',xm)
						from xsxxb
						group by bjmc,xm
						having count(*)>1)
order by xm

3.显示xsxxb里在cjb里没有记录的学生名单
select *
from xsxxb
where xh not in(
						select distinct xh
						from cjb)
order by xm

4.统计xsxxb里每个学生所修课程门次、不及格课程门次、及格课程门次
//第一种
select xh,count(*),
			count(if(cj<60,1,null)),
			count(if(cj>=60,1,null))
from cjb
//第二种
select xh,count(*),
			sum(if(cj<60,1,0)),
			sum(if(cj>=60,1,0))
from cjb
//第三种
select xh,count(*),
			sum(case when cj<60 then 1 else 0 end),
			sum(case when cj>=60 then 1 else 0 end)
from cjb
//第四种
select xh,xm,csrq,bjmc,sfzh,rxcj,
	(select count(*) from cjb where xh=xsxxb.xh)mc,
	(select count(*) from cjb where xh=xsxxb.xh and cj>=60)jgmc,
	(select count(*) from cjb where xh=xsxxb.xh and cj<60)bjgmc
from xsxxb

5.统计bjxxb里的每个班级的学生数
select bjmc,xymc,nj,zymc,
(select count(*) from xsxxb where bjmc=bjxxb.bjmc) 学生数
from bjxxb

6.显示bjxxb里班级人数为0的班级名称、专业名称、年级
//第一种
select bjmc,xymc,nj,zymc,
(select count(*) from xsxxb where bjmc=bjxxb.bjmc) 学生数
from bjxxb
where (select count(*) from xsxxb where bjmc=bjxxb.bjmc)=0
//第二种
select * from bjxxb
where bjmc not in(
							select distinct bjmc
							from xsxxb)

7.统计bjxxb里的每个班级的学生数、入学成绩最大值
select bjmc,xymc,nj,zymc,
(select count(*) from xsxxb where bjmc=bjxxb.bjmc )学生数
(select max(rxcj) from xsxxb where bjmc=bjxxb.bjmc) 入学成绩最大值
from bjxxb

8.统计bjxxb里的每个班级的学生数、男生数、女生数、入学成绩最大值、入学成绩最小大值
select bjmc,xymc,nj,zymc,
(select count(*) from xsxxb where bjmc=bjxxb.bjmc )学生数
(select count(*) from xsxxb
		where bjmc=bjxxb.bjmc and substr(sfzh,17,1)%2=1) 男生数
(select count(*) from xsxxb
		where bjmc=bjxxb.bjmc and substr(sfzh,17,1)%2=0) 女生数

(select max(rxcj) from xsxxb where bjmc=bjxxb.bjmc) 入学成绩最大值
(select min(rxcj) from xsxxb where bjmc=bjxxb.bjmc) 入学成绩最小值
from bjxxb

9.统计xsxxb里每个学生的平均成绩，要求显示每个学生的姓名、班级、平均成绩
select xh,xm,csrq,bjmc,sfzh,rxcj,
(select avg(cj) from cjb where xh=xsxxb.xh)平均成绩
from xsxxb
10.统计bjxxb里的每个班级的平均成绩
select bjmc,xymc,nj,zymc,
(select avg(cj) from cjb
		where xh in (select xh from xsxxb where bjmc=bjxxb.bjmc))平均成绩
from bjxxb

11.统计bjxxb里的每个班级的平均年龄
select bjmc,xymc,nj,zymc,
(select avg(timestampdiff(year,substr(sfzh,7,8),now()))
	from xsxxb where bjmc=bjxxb.bjmc)平均年龄

from bjxxb

12.统计bjxxb里的每个班级的不及格率
select bjmc,xymc,nj,zymc,
(select count(cj) from cjb
		where xh in (select xh from xsxxb where bjmc=bjxxb.bjmc))cjs,
(select count(if(cj<60,1,null)) from cjb
		where xh in (select xh from xsxxb where bjmc=bjxxb.bjmc))bjgs,
(select count(if(cj<60,1,null)) from cjb
		where xh in (select xh from xsxxb where bjmc=bjxxb.bjmc))/
(select count(cj) from cjb
		where xh in (select xh from xsxxb where bjmc=bjxxb.bjmc))
from bjxxb

13.列出所有个人平均成绩大于全校平均成绩的学生名单，只需显示学生学号
select xh,avg(cj)
from cjb
group by xh
having avg(cj)>(select avg(cj) from cjb)

14.列出所有个人平均成绩大于全校平均成绩的学生名单，显示学生的学号、姓名、班级
//第一种
select xh,
(select xm from xsxxb where xh=cjb.xh) new_xm,
(select bjmc from xsxxb where xh=cjb.xh) new_bj,
avg(cj)
from cjb
group by xh
having avg(cj)>(select avg(cj) from cjb)
//第二种
select xh,xm,csrq,bjmc,sfzh,rxcj
from xsxxb
where(select avg(cj) from cjb where xh=xsxxb.xh)

>(select avg(cj) from cjb)