数据库实验(共9页).doc
精选优质文档-倾情为你奉上实验三 建表、修改表、删除表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 Sage<=45),Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'),Sdept CHAR(2);Create Table Course(Cno CHAR(2) NOT NULL PRIMARY KEY(Cno),Cname VARCHAR(20),Cpno CHAR(2),Ccredit SMALLINT);Create Table SC(Sno CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno),Cno CHAR(2) NOT NULL,Grade SMALLINT CHECK(Grade IS NULL)OR (Grade BETWEEN 0 AND 100),PRIMARY KEY(Sno,Cno),CONSTRAINT C_F FOREIGN KEY(Cno) REFERENCES Course(Cno);INSERT INTO Student VALUES('98001','钱横',18,'男','CS');INSERT INTO Student VALUES('98002','王林',19,'女','CS');INSERT INTO Student VALUES('98003','李民',20,'男','IS');INSERT INTO Student VALUES('98004','赵三',16,'女','MA');INSERT INTO Course VALUES('1','数据库系统','5',4);INSERT INTO Course VALUES('2','数学分析',null,2);INSERT INTO Course VALUES('3','信息系统导论','1',3);INSERT INTO Course VALUES('4','操作系统原理','6',3);INSERT INTO Course VALUES('5','数据结构','7',4);INSERT INTO Course VALUES('6','数据处理基础',null,4);INSERT INTO Course VALUES('7','C语言','6',3);INSERT INTO SC VALUES('98001','1',87);INSERT INTO SC VALUES('98001','2',67);INSERT INTO SC VALUES('98001','3',90);INSERT INTO SC VALUES('98002','2',95);INSERT INTO SC VALUES('98002','3',88);课程表Course（课程号Cno,课程名Cname,先修课号Cpno,学分Ccredit）学生表Student（学号Sno,姓名Sname,年龄Sage,性别Ssex,所在系Sdept）学生选课表SC（学号Sno,课程号Cno,成绩Grade)实验四 Select查询操作练习（1）基于“教学管理”数据库jxgl，使用SQL的查询语句表示下列查询：检索年龄大于23岁的男学生的学号和姓名；select Sno,Snamefrom Studentwhere Sage>'23' AND Ssex='男'检索至少选修一门课程的女学生的姓名；select Sname from Student,SCwhere Ssex='女' AND Student.Sno=SC.Sno group by Student.Sname having count(*)>=1;或者Select SnameFrom StudentWhere Ssex='女'AND Sno in(select sno from SCgroup by snohaving count(*)>=1);检索王同学不学的课程的课程号；select Cnofrom Coursewhere Course.Cno not in (select Cno from SC,Studentwhere SC.Sno=Student.Sno AND Sname LIKE '王%');检索至少选修两门课程的学生学号；select DISTINCT Student.Snofrom Student,SCWHERE Student.Sno=SC.SnoGROUP BY Student.Sno HAVING COUNT(*)>=2;检索全部学生都选修的课程的课程号与课程名； SELECT Cno,Cnamefrom Course where not exists(select *from studentwhere not exists(select *from SCwhere SC.sno=Student.Sno AND SC.Cno=Course.Cno)或者假设所有学生只有两人SELECT Cno,Cnamefrom Course WHERE Course.Cno in(select Cno from SCgroup by SC.Cnohaving count(Sno)=(select count(*)from Student);SELECT Cno,Cnamefrom Course WHERE Course.Cno in(select Cno from SCgroup by SC.Cnohaving count(Sno)=2);检索选修了所有3学分课程的学生学号。select distinct Student.Snofrom Student,SCwhere exists(select *from Coursewhere 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,SCwhere Student.Sno=SC.Sno AND Cno='4'SELECT avg(Student.Sage) as 平均年龄from Student,SCwhere Student.Sno=SC.Sno AND Cno='3'求学分为3的每门课程的学生平均成绩；SELECT avg(SC.Grade)from Course,SC,Studentwhere Student.Sno=SC.Sno AND Course.Ccredit='3'group by SC.Cno用group by 语句以课程号分组注意，如果程序是这样的：SELECT avg(SC.Grade)from Course,SC,Studentwhere Student.Sno=SC.Sno AND Course.Ccredit='3'AND Course.Cno=SC.Cno;只显示一门课程的成绩！统计每门课程的学生选修人数，要求超过3人的课程才统计，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列；SELECT Cno,count(Sno)from SCGROUP BY Cno HAVING Count(Sno)>3order by count(sno)DESC,Cno;SELECT Cno,count(Sno) as 选修人数from SCGROUP BY Cno HAVING Count(Sno)>1order by count(sno)DESC,Cno;检索学号比“王林”同学大而年龄比他小的学生姓名；SELECT SnameFROM Studentwhere Sno>(select Snofrom Studentwhere Sname='王林')AND Sage<(select Sagefrom Studentwhere Sname='王林');或者也可以select X.sname from Student as X,Student as Ywhere X.Sno>Y.Sno AND X.Sage<Y.Sage AND Y.Sname='王林'检索姓名以“王”开头的所有学生的姓名和年龄；select Sname,Sagefrom Studentwhere Student.Sname like '王%'注意：like那里不能用等号“=”在SC中检索成绩为空值的学生学号和课程号；SELECT Sno,Cnofrom SCwhere Grade is null注意：is null 那里不能用等号“=”求年龄大于女学生平均年龄的男学生姓名和年龄；SELECT Sname,Sagefrom Studentwhere Sage>(select avg(Sage)from studentwhere Ssex='女')AND Ssex='男'求年龄大于所有女学生年龄的男学生姓名和年龄；SELECT Sname,Sagefrom Studentwhere Sage>(SELECT MAX(Sage)from Studentwhere Ssex='女')AND Ssex='男'检索所有比“王林”年龄大的学生姓名、年龄和性别；SELECT Sname,Sage,Ssexfrom Studentwhere Sage>(select Sagefrom Studentwhere Sname='王林')检索选修“2”课程的学生中成绩最高的学生的学号；SELECT Sno,Gradefrom SCwhere Grade=(select MAX(Grade)from SCwhere Cno='2');注意：不能写成Grade=MAX(Grade)的形式，因为不存在！Count,min,avg也是检索学生姓名以及所选修课程的课程号和成绩；select Sname,Cno,Gradefrom SC,Studentwhere Student.Sno=SC.Sno;检索选修4门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。专心-专注-专业选择4门以上select sno,sum(Grade) sumfrom SCwhere grade>=60 AND Sno in(select Sno from SCgroup by Snohaving count(Sno)>4)group by snoorder by sum(grade) desc注意：选择2门课（包括两门）select sno,sum(Grade)from SCwhere grade>=60 AND Sno in(select Sno from SCgroup by Snohaving count(Sno)>=2)group by snoorder by sum(grade) desc再注意：Select sno,sum(grade)From SCWhere grade>=60Group by sno having count(sno)>1Order by sum(grade) desc因为输入的数据分数都大于60，所以结果与上面的例子相同。但是，当分数小于60时，有课不及格的同学的学号会被删除，统计不完整。
