mysql数据库实验报告三

实验目的：

1.熟练掌握各种连接查询及其连接条件。
2.掌握各种嵌套查询的使用。
3.掌握复杂的集合查询。

内容和主要步骤：

第2题：
1>

select student.sno,sname,ssex,sage,sdept,cno,grade
from a.student,a.sc
where a.student.sno=a.sc.sno

2>

select first.cno,second.cpno
from a.course first,a.course second
where first.cpno=second.cno 

3>

select a.student.sno,sname,ssex,sage,sdept,cno,grade
from a.student right outer join a.sc on a.student.sno=sc.sno

4>

select sname,sdept
from a.student 
where sno in(select sno from a.sc group by sno having min(grade)<60)

5>

select sname
from a.student 
where sno in(select sno from a.sc group by sno having min(grade)>90)

6>

select a.student.sno,sname
from a.student inner join a.sc on a.student.sno=sc.sno
where cno='3' and sc.sno in
(select sno
from a.sc
where cno='2')

7>

select a.student.sno,sname
from a.student
where sname!='刘晨' and sage=
(select sage 
from a.student
where sname='刘晨')

8>

select sname,sage
from a.student
where sno in
(select sno
from a.sc
where cno in
(select cno
from a.course 
where cname='数据库'))

9>

select student.sno,sname
from a.student
where sdept<>'IS' and
sage<any
(select sage 
from a.student
where sdept='IS')

10>

select student.sno,sname
from a.student
where sdept<>'IS' and 
sage<all
(select sage 
from a.student 
where sdept='IS')

11>

select sname
from a.student
where Sno in
(select Sno from a.sc
group by Sno
having count(*) = (select count(*) from a.course ))

12>

select a.student.sno,sname
from a.student
where sdept='IS' and ssex='男'

13>

select *
from a.sc where cno='001'and sno not in(select sno from a.sc where cno='002')

14>

select cno
from a.course
where cno not in
(select cno
from a.sc
where sno in
(select sno
from a.student
where sname='李丽'))

15>

select AVG(sage) as avgsage
from a.student inner join a.sc on student.sno=sc.sno
where cno='3'

16>

select cno,AVG(grade) as avggrade
from a.sc
group by cno

17>

select a.course.cno '课程号', count(sc.sno) '人数'
from a.course,a.sc 
where a.course.cno=a.sc.cno 
group by a.course.cno having count(sc.sno)>3 order by COUNT(sc.sno) desc,a.course.cno asc

18>

select sname
from a.student
where sno>
(select sno from a.student where sname='刘晨')and
sage<(select sage from a.student where sname='刘晨')

19>

select sname,sage from  a.student
where sage>(select avg(sage) from a.student where ssex='女') 
and ssex='男'

20>

select sname,sage
from a.student
where ssex='男'and sage>
(select MAX(sage) from a.student where ssex='女')

21>

select a.Student.Sno,Sname
from a.Student,a.SC
where a.Student.Sno=a.SC.Sno 
and Cno =
(select Cno from a.SC
where Sno='95002')   

22>

select * 
from a.course where cno in (select cno from a.sc where sno='95001')
and cno in (select cno from a.sc where sno='95002')	

遇到的问题及体会：

1.头晕眼花，三个实验报告同时交
2.总觉得一直再win+shift+s+ctrl+a+ctrl+c+ctrl+v
3.知识点：
1>求总数可以用COUNT（）函数
2>分组group by 要用having来限制条件
3>order by是排序要求 desc是降序 ，asc是升序
4>any（）函数是任意的意思，all（）是所有
4.建表要合适否则很多查询出来是空的