数据库实验报告.docx

1、数据库实验报告实验1.1 (1) 原因是percent好像是系统的关键字。忘记了加主键网上查有这个，如果没设NOT null ，先设好同样方法设置PRODUENTS表却有问题，提示已经有PRODUENTS对象，无法添加完整性约束。后上网发现原来，constraint后是完整性约束的名字，上面的成功因为打错字。2）（3）（4）（5）1.2实验1.21.2（1）select * from COURSES（2）select sid from CHOICESwhere cid is not null(3) select CID from COURSESwhere hour400select score

2、 from CHOICES where sid=822863200(5)select count(DISTINCT CID) from COURSES(6) select CID,count(CID) from CHOICESGROUP BY CID(7) select sid from CHOICESwhere score60group by sidhaving count(*)2(8) select sid,count(cid),avg(score) from CHOICESgroup by sid (9)SELECT SNAME,STUDENTS.SIDFROM COURSES,STUD

3、ENTS,CHOICESWHERE COURSES.CID=CHOICES.CID AND CHOICES.SID=STUDENTS.SID AND CNAME=Java结果：共6110条记录(10)使用等值连接：SELECT CID,SCOREFROM STUDENTS,CHOICESWHERE CHOICES.SID=STUDENTS.SID AND SNAME=sssht使用谓词IN:SELECT CID,SCOREFROM CHOICESWHERE SID IN(SELECT SIDFROM STUDENTSWHERE SNAME=sssht)结果：共5条记录(11)SELECT CN

4、AMEFROM COURSESWHERE HOUR (SELECT HOURFROM COURSESWHERE CNAME=C+)结果：共5条记录(12)SELECT CHOICES.SID,SNAMEFROM COURSES,STUDENTS,CHOICESWHERE CHOICES.SID=STUDENTS.SID AND CHOICES.CID=COURSES.CID AND CNAME=C+ AND SCORE (SELECT SCORE FROM COURSES,STUDENTS,CHOICES WHERE CHOICES.SID=STUDENTS.SID AND CHOICES.C

5、ID=COURSES.CID AND CNAME=C+ AND SNAME=znkoo)结果：共2065条记录(13)SELECT SNAMEFROM STUDENTSWHERE GRADE IN(SELECT GRADEFROM STUDENTSWHERE SID=883794999 OR SID=850955252)结果：共13372条记录(14)SELECT DISTINCT SNAMEFROM STUDENTS,COURSES,CHOICESWHERE CHOICES.SID=STUDENTS.SID AND CHOICES.CID=COURSES.CID AND CNAME NOT

6、IN(Java)结果：共99548条记录(15)SELECT CID,CNAME,HOURFROM COURSESWHERE HOUR=ALL(SELECT HOURFROM COURSES)结果：共2条记录(16)SELECT TID,CIDFROM CHOICESWHERE TID IN( SELECT TID FROM TEACHERS WHERE SALARY= (SELECT MAX(SALARY) FROM TEACHERS)GROUP BY TID,CID结果：共73条记录,共4名教师（17）SELECT SIDFROM CHOICESWHERE SCORE=(SELECT MA

7、X(SCORE)FROM CHOICESWHERE CID=(SELECT CIDFROM COURSESWHERE CNAME=ERP)结果：共5963条记录(18)SELECT CNAMEFROM COURSESWHERE CID NOT IN(SELECT CIDFROM CHOICES)结果：无数据（19）SELECT DISTINCT CIDFROM CHOICESWHERE TID IN(SELECT TIDFROM CHOICESWHERE CID=(SELECT CIDFROM COURSESWHERE CNAME=UML)结果：共50条记录(20)SELECT SNAME F

8、ROM STUDENTSWHERE NOT EXISTS(SELECT *FROM CHOICES AS C1WHERE NOT EXISTS(SELECT *FROM CHOICES AS C2WHERE C2.SID=STUDENTS.SIDAND C2.CID=C1.CIDAND C2.TID=200102901)结果：无数据(21)SELECT SIDFROM CHOICES,COURSESWHERE CHOICES.CID=COURSES.CIDAND COURSES.CNAME=databaseUNIONSELECT SIDFROM CHOICES,COURSESWHERE CHO

9、ICES.CID=COURSES.CIDAND COURSES.CNAME=UML结果：共11297条记录(22)SELECT X.SIDFROM CHOICES AS X,CHOICES AS YWHERE(X.CID=(SELECT CIDFROM COURSESWHERE CNAME=database)AND Y.CID=(SELECT CIDFROM COURSESWHERE CNAME=UML)AND X.SID=Y.SID结果：共317条记录(23)SELECT X.SIDFROM CHOICES AS X,CHOICES AS YWHERE(X.CID=(SELECT CIDFR

10、OM COURSESWHERE CNAME=database)AND X.SID=Y.SIDAND NOT(Y.CID=(SELECT CIDFROM COURSESWHERE CNAME=UML)结果：共21383条记录实验1.3(1)INSERT INTO STUDENTS(SID,SNAME)VALUES(800022222,WangLan)(2)INSERT INTO TEACHERSVALUES(200001000,LXL,s4zrck,3024)(3)UPDATE TEACHERSSET SALARY=4000WHERE TID=200010493(4)UPDATE TEACHER

11、SSET SALARY=2500WHERE SALARY2500结果：（5）UPDATE CHOICESSET TID=(SELECT TIDFROM TEACHERSWHERE TNAME=rnupx)WHERE TID=200016731结果：（6）UPDATE STUDENTSSET GRADE=2001WHERE SID=800071780(7)DELETE FROM COURSESWHERE CID NOT IN(SELECT CIDFROM CHOICESGROUP BY CID)(8)DELETE FROM STUDENTSWHERE GRADE1998这样执行会提示错误，因为C

12、HOICES表中的SID外键参照STUDENTS表，要先去除外键约束，或者删除CHOICES表中的相关约束。（9）DELETE FROM STUDENTSWHERE SID NOT IN(SELECT SID FROM CHOICES GROUP BY SID)(10)DELETE FROM CHOICES WHERE SCORE1998(4)SELECT * FROM VIEWSWHERE SNAME=uxjof结果：（5）SELECT SID,SCOREFROM VIEWCWHERE CNAME=UML结果：共5253条记录(6)INSERT INTO S1VALUES(60000001,

13、Lily,2001)(7)CREATE VIEW S2(SID,SNAME,GRADE) ASSELECT SID,SNAME,GRADEFROM STUDENTSWHERE GRADE1998WITH CHECK OPTION插入：INSERT INTO S2VALUES(60000001,Lily,1997)结果：DELETE FROM S2WHERE GRADE=1999结果：(8)UPDATE VIEWSSET SCORE=SCORE+5WHERE SNAME=uxjof结果：（9）DROP VIEW VIEWCDROP VIEW VIEWSDROP VIEW S1DROP VIEW

14、S2实验1.5(1)GRANT SELECTON STUDENTSTO PUBLIC(2)GRANT SELECT,UPDATEON COURSESTO PUBLIC(3)GRANT SELECT,UPDATE(SALARY)ON TEACHERSTO USER1WITH GRANT OPTION(4)GRANT SELECT,UPDATE(SCORE)ON CHOICESTO USER2(5)CREATE VIEW TV ASSELECT TID,TNAME,EMAIL,SALARYFROM TEACHERSGRANT SELECTON TVTO USER2(6)GRANT SELECTON

15、 TEACHERSTO USER2WITH GRANT OPTION(7)USER2的操作：GRANT SELECTON TEACHERSTO USER3WITH GRANT OPTIONUSER3的操作：GRANT SELECTON TEACHERSTO USER2WITH GRANT OPTION（8）REVOKE SELECTON TEACHERSFROM USER1 CASCADE(9)REVOKE SELECT,UPDATEON COURSESFROM USER1,USER2实验1.6(1)SELECT CID,HOUR*18FROM COURSES对NULL做算术运算结果为NULL

16、结果：共50条记录（2）选修C+课程的人数为5307人：SELECT COUNT(*)FROM CHOICESWHERE CID=(SELECT CIDFROM COURSESWHERE CNAME=C+)其中，合格人数为4817：SELECT COUNT(*)FROM CHOICESWHERE CID=(SELECT CIDFROM COURSESWHERE CNAME=C+)AND SCORE=60不合格人数为0人：SELECT COUNT(*)FROM CHOICESWHERE CID=(SELECT CIDFROM COURSESWHERE CNAME=C+)AND SCORE=ALL

17、(SELECT GRADEFROM STUDENTS)利用上面的查询语句不能查询出结果,可能因为存在NULL值，因此尝试了下面的语句：SELECT GRADEFROM STUDENTSWHERE GRADE=ALL(SELECT GRADEFROM STUDENTSWHERE GRADE IS NOT NULL)得到结果为2004，果真因为在比较=ALL的时候，因为存在NULL值，不能做比较。(8)SELECT COUNT(*)FROM S,TWHERE T.TID=S.SID实验2.1（1）USE SCHOOLCreate Table Class(Class_id varchar(4),na

18、me varchar(10),Deparment varchar(20)constraint PK_Class Primary key(Class_id)（2）USE SCHOOLBegin Transaction T3insert into Class values(0001,01CSC,CS)Begin Transaction T4insert into Class values(0001,01CSC,CS)Commit Transaction T4Commit Transaction T3实验2.2（1）Alter table SC drop constraint FK_SC_cno_0

19、3F0984C;Alter table SC add constraint FK_SC_cno foreign key (cno) references Course(cno) on delete restrict ;USE SCHOOLdelete from Stu_Union where sno=10001;select * from SC;USE SCHOOL delete from Course where cno=0002; select * from SC;（2）Alter table SC drop constraint FK_SC_cno_03F0984C;Alter tabl

20、e SC add constraint FK_SC_cno foreign key (cno) references Course(cno) on delete set null ;USE SCHOOLdelete from Stu_Union where sno=10001;select * from SC;USE SCHOOL delete from Course where cno=0002; select * from SC;（3）CREATE TABLE help(sid char(8),sname varchar(20),help_id char (8)NOT NULLCONSTR

21、AINT PK_help primary key(sid)alter table help add constraint FK_help foreign key(help_id) references help(sid)（4）CREATE TABLE leader(sid char(9),sname varchar(20),myleader char(9)CONSTRAINT PK_leader primary key(sid)CREATE TABLE monitor(sid char(9),sname varchar(20),mymonitor char(9)constraint PK_mo

22、nitor primary key(sid)constraint FK_monitor foreign key(mymonitor) references leader(sid)alter table leaderadd constraint FK_leader foreign key(myleader) references monitor(sid)实验2.3（1）use schoolalter table worker add constraint U3 check(sage=0);（2）use schoolGocreate rule rule_sage as value between

23、1 and 100Go Exec sp_bindrule rule_sage,worker.sage;实验2.4（1）use school gocreate trigger T4 on workerfor insertasif (select sage from inserted)=(select max(sage) from worker)beginprint The sage of couple must be more than the existed couplessageRollback transactionEnduse schoolinsert into worker values (00003,李红,F,50,开发部)（2）use school gocreate trigger T5 on workerfor updateasif (select sage from inserted)=(select sage from deleted)beginprint The sage of new couple must be more than the sage of old coupleRollback transactionEnduse schoolupdate worker set sage=10 where number=00001