《数据库实验.docx》由会员分享，可在线阅读，更多相关《数据库实验.docx（8页珍藏版）》请在三一办公上搜索。
1、数据库实验实验三 建表、修改表、删除表 1.建立数据库jxgl 在jxgl数据库中建立学生表student,课程表course,选修表sc，建表过程有如下方式 1）以图形界面操作 2）以SQL操作 Create Table Student (Sno CHAR(5) NOT NULL PRIMARY KEY(Sno), Sname VARCHAR(20), Sage SMALLINT CHECK(Sage=15 AND Sage23 AND Ssex=男; 检索至少选修一门课程的女学生的姓名； select Sname from Student,SC where Ssex=女 AND Stude
2、nt.Sno=SC.Sno group by Student.Sname having count(*)=1; 或者 Select Sname From Student Where Ssex=女 AND Sno in (select sno from SC group by sno having count(*)=1); 检索王同学不学的课程的课程号； select Cno from Course where Course.Cno not in (select Cno from SC,Student where SC.Sno=Student.Sno AND Sname LIKE 王%); 检索
3、至少选修两门课程的学生学号； select DISTINCT Student.Sno from Student,SC 3 WHERE Student.Sno=SC.Sno GROUP BY Student.Sno HAVING COUNT(*)=2; 检索全部学生都选修的课程的课程号与课程名； SELECT Cno,Cname from Course where not exists (select * from student where not exists (select * from SC where SC.sno=Student.Sno AND SC.Cno=Course.Cno)
4、) 或者假设所有学生只有两人 SELECT Cno,Cname from Course WHERE Course.Cno in (select Cno from SC group by SC.Cno having count(Sno)= (select count(*) from Student); SELECT Cno,Cname from Course WHERE Course.Cno in (select Cno from SC group by SC.Cno having count(Sno)=2); 4 检索选修了所有3学分课程的学生学号。 select distinct Stude
5、nt.Sno from Student,SC where exists (select * from Course where Ccredit =3 AND Student.Sno=SC.Sno AND SC.Cno=Course.Cno); (2)基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询： 统计有学生选修的课程门数； select count(distinct SC.Cno) FROM SC; 求选修4号课程的学生的平均年龄； SELECT avg(Student.Sage) from Student,SC where Student.Sno=SC.Sno AND C
6、no=4; SELECT avg(Student.Sage) as 平均年龄 from Student,SC where Student.Sno=SC.Sno AND Cno=3; 求学分为3的每门课程的学生平均成绩； 5 SELECT avg(SC.Grade) from Course,SC,Student where Student.Sno=SC.Sno AND Course.Ccredit=3 group by SC.Cno 用group by 语句以课程号分组 注意，如果程序是这样的： SELECT avg(SC.Grade) from Course,SC,Student where
7、Student.Sno=SC.Sno AND Course.Ccredit=3AND Course.Cno=SC.Cno; 只显示一门课程的成绩！ 统计每门课程的学生选修人数，要求超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列； SELECT Cno,count(Sno) from SC GROUP BY Cno HAVING Count(Sno)3 order by count(sno)DESC,Cno; SELECT Cno,count(Sno) as 选修人数 from SC GROUP BY Cno HAVING Count(Sno
8、)1 order by count(sno)DESC,Cno; 检索学号比“王林”同学大而年龄比他小的学生姓名； SELECT Sname FROM Student where Sno (select Sno from Student where Sname=王林) 6 AND SageY.Sno AND X.Sage(select avg(Sage) from student where Ssex=女) AND Ssex=男; 求年龄大于所有女学生年龄的男学生姓名和年龄； SELECT Sname,Sage from Student where Sage(SELECT MAX(Sage) 7
9、 from Student where Ssex=女) AND Ssex=男; 检索所有比“王林”年龄大的学生姓名、年龄和性别； SELECT Sname,Sage,Ssex from Student where Sage(select Sage from Student where Sname=王林) 检索选修“2”课程的学生中成绩最高的学生的学号； SELECT Sno,Grade from SC where Grade=(select MAX(Grade) from SC where Cno=2); 注意：不能写成Grade=MAX(Grade)的形式，因为不存在！Count,min,a
10、vg也是 检索学生姓名以及所选修课程的课程号和成绩； select Sname,Cno,Grade from SC,Student where Student.Sno=SC.Sno; 检索选修4门以上课程的学生总成绩，并要求按总成绩的降序排列出来。 where grade=60 选择4门以上 select sno,sum(Grade) sum AND Sno in from SC (select Sno 8 from SC group by Sno having count(Sno)4) group by sno order by sum(grade) desc 注意：选择2门课 select
11、 sno,sum(Grade) from SC 再注意： Select sno,sum(grade) From SC Where grade=60 Group by sno having count(sno)1 Order by sum(grade) desc where grade=60 AND Sno in (select Sno from SC group by Sno having count(Sno)=2) group by sno order by sum(grade) desc 因为输入的数据分数都大于60，所以结果与上面的例子相同。但是，当分数小于60时，有课不及格的同学的学号会被删除，统计不完整。 9
