数据库实验报告.docx
- 文档编号:27653155
- 上传时间:2023-07-03
- 格式:DOCX
- 页数:55
- 大小:1.87MB
数据库实验报告.docx
《数据库实验报告.docx》由会员分享,可在线阅读,更多相关《数据库实验报告.docx(55页珍藏版)》请在冰豆网上搜索。
数据库实验报告
数据库实验报告
实验1.1
(1)
CREATETABLECUSTOMER
(CIDCHAR(8)UNIQUE,CNAMECHAR(20),CITYCHAR(8),
DISCNTINT,
PRIMARYKEY(CID))
CREATETABLEAGENTS
(AIDCHAR(8)UNIQUE,ANAMECHAR(20),CITYCHAR(8),
PERCENTSFLOAT,
PRIMARYKEY(AID))
CREATETABLEPRODUCTS
(PIDCHAR(8)UNIQUE,PNAMECHAR(20),PRIMARYKEY(PID))
(2)
CREATETABLEORDERS
(ORDNACHAR(8)UNIQUE,MONTHINT,CIDCHAR(8),AIDCHAR(8),
PIDCHAR(8),QTYINT,DOLLARSFLOAT,PRIMARYKEY(ORDNA),
FOREIGNKEY(CID)REFERENCESCUSTOMER,
FOREIGNKEY(AID)REFERENCESAGENTS,
FOREIGNKEY(PID)REFERENCESPRODUCTS)
(3)
ALTERTABLEPRODUCTSADDCITYCHAR(8)
ALTERTABLEPRODUCTSADDQUANTITYINT
ALTERTABLEPRODUCTSADDPRICEFLOAT
(4)
CREATEINDEXXSNOONCUSTOMER(CID)
CREATEINDEXXSNOONAGENTS(AID)
CREATEINDEXXSNOONPRODUCTS(PID)
CREATEINDEXXSNOONORDERS(ORDNA)
(5)
DROPINDEXCUSTOMER.XSNO
DROPINDEXAGENTS.XSNO
DROPINDEXPRODUCTS.XSNO
DROPINDEXORDERS.XSNO
实验1.2
(1)SELECT*FROMCOURSES
(2)SELECTSIDFROMCHOICES
(3)SELECTCIDFROMCOURSESWHEREHOUR<88
(4)SELECTSIDFROMCHOICESGROUPBYSIDHAVINGSUM(SCORE)>400
(5)SELECTCOUNT(CID)FROMCOURSES
(6)SELECTCID,COUNT(SID)FROMCHOICESGROUPBYcid
(7)SELECTSIDFROMCHOICESWHEREscore>60GROUPBYsidHAVINGCOUNT(CID)>2
(8)SELECTSID,COUNT(CID),AVG(SCORE)FROMCHOICESGROUPBYsid
(9)
SELECTSTUDENTS.sid,SNAMEFROMSTUDENTS,CHOICES,COURSES
WHERESTUDENTS.sid=CHOICES.sidANDCHOICES.cid=COURSES.cidANDCOURSES.cname='Java'
(10)
SELECTCHOICES.sid,CHOICES.scoreFROMCHOICES,STUDENTS
WHEREsname='sssht'ANDCHOICES.sid=STUDENTS.sid
SELECTCID,SCOREFROMCHOICESWHEREsidIN(
SELECTSTUDENTS.sidFROMSTUDENTSWHERESNAME='sssht')
(11)
SELECTC1.CNAMEFROMCOURSESASC1,COURSESASC2
WHEREC1.hour>C2.hourANDCame='C++'
(12)
SELECTSID,SNAMEFROMSTUDENTSWHEREsidIN(
SELECTC1.SIDFROMCHOICESASC1,CHOICESASC2
WHEREC1.score>C2.scoreANDC1.cid=C2.cid
ANDC2.sid=(SELECTsidFROMSTUDENTSWHEREsname='znkoo')
ANDC1.cid=(SELECTcidFROMCOURSESWHEREcname='C++'))
(13)
SELECTSNAMEFROMSTUDENTSWHEREgradeIN(
SELECTgradeFROMCHOICESWHEREsidIN('883794999','850955252'))
(14)
SELECTSNAMEFROMSTUDENTSWHEREsidNOTIN
(SELECTSIDFROMCHOICESWHEREcid=(SELECTcidFROM
COURSESWHEREcname='Java'))
(15)SELECT*FROMCOURSESWHEREhour<=ALL(SELECThourFROMCOURSES)
(16)
SELECTCHOICES.tid,CIDFROMCHOICESWHERENOTEXISTS(
SELECT*FROMTEACHERSWHERETEACHERS.salary>=
(SELECTsalaryFROMTEACHERSWHERETEACHERS.tid=CHOICES.tid))
(17)
SELECTSIDFROMCHOICESWHEREscore=
(SELECTMAX(SCORE)FROMCHOICESWHEREcid=(
SELECTcidFROMCOURSESWHEREcname='ERP'))
(18)
SELECTCNAMEFROMCOURSESWHEREcidNOTIN(
SELECTcidFROMCHOICES)
(19)
SELECTCNAMEFROMCOURSESWHEREcid=SOME(
SELECTcidFROMCHOICESWHERETID=SOME(
SELECTTIDFROMCOURSES,CHOICESWHEREcname='UML'AND
COURSES.cid=CHOICES.cid))
(20)
SELECTSNAMEFROMSTUDENTSWHERENOTEXISTS(
SELECT*FROMCHOICESASC1WHERENOTEXISTS(
SELECT*FROMCHOICESASC2WHEREC2.sid=STUDENTS.sid
ANDC2.cid=C1.cidANDC2.tid='200102901'))
(21)
SELECTSIDFROMCHOICES,COURSESWHERECOURSES.CID=
CHOICES.cid
ANDCOURSES.cname='database'
UNION
SELECTSIDFROMCHOICES,COURSESWHERECOURSES.cid=
CHOICES.cidANDCOURSES.cname='UML'
(22)
SELECTX.SIDFROMCHOICESASX,CHOICESASY
WHERE(X.cid=(SELECTcidFROMCOURSESWHEREcname=
'database')
ANDY.cid=(SELECTcidFROMCOURSESWHEREcname='UML'))
ANDX.sid=Y.sid
(23)
SELECTX.SIDFROMCHOICESASX,CHOICESASY
WHERE(X.cid=(SELECTcidFROMCOURSESWHEREcname=
'database'))
ANDX.sid=Y.sid
ANDNOT(Y.cid=(SELECTcidFROMCOURSESWHEREcname=
'UML'))
实验1.3
(1)
INSERTINTOSTUDENTS(sid,sname)
VALUES('800022222','WangLan')
(2)
INSERTINTOTEACHERS
VALUES('200001000','LXL','s4zrch@','3024')
(3)
UPDATETEACHERS
SETsalary=4000
WHEREtid='200010493'
(4)
UPDATETEACHERS
SETsalary=2500
WHEREsalary<2500
(5)
UPDATECHOICES
SETtid=
(SELECTtidFROMTEACHERSWHEREtname='rnupx')
WHEREtid='200016731'
(6)
UPDATESTUDENTS
SETgrade=2001
WHEREsid='800071780'
(7)
DELETEFROMCOURSES
WHEREcidNOTIN
(SELECTcidFROMCHOICESGROUPBYcid)
(8)
DELETEFROMSTUDENTS
WHEREgrade<1998
(9)
DELETEFROMSTUDENTSWHEREsidNOTIN
(SELECTsidFROMCHOICESGROUPBYsid)
(10)
DELETEFROMCHOICESWHEREscore<60
实验1.4
(1)
CREATEVIEWVIEWCAS(
SELECTCHOICES.no,CHOICES.sid,CHOICES.tid,COURSES.cname,
CHOICES.scoreFROMCHOICES,COURSES
WHERECHOICES.cid=COURSES.cid)
(2)
CREATEVIEWVIEWSAS
SELECTCHOICES.no,STUDENTS.sname,CHOICES.tid,CHOICES.cid,
CHOICES.scoreFROMCHOICES,STUDENTS
WHERECHOICES.sid=STUDENTS.sid
(3)
CREATEVIEWS1(SID,SNAME,GRAGE)AS
SELECTSTUDENTS.sid,STUDENTS.sname,STUDENTS.grade
FROMSTUDENTS
WHEREgrade>1998
(4)SELECT*FROMVIEWSWHERESNAME='uxjof'
(5)SELECTSID,SCOREFROMVIEWCWHERECNAME='UML'
(6)
INSERTINTOS1
VALUES('60000001','Lily',2001)
(7)
CREATEVIEWS2(SID,SNAME,GRADE)AS
SELECTSID,SNAME,GRADEFROMSTUDENTS
WHEREGRADE>1998
WITHCHECKOPTION
INSERTINTOS2
VALUES('60000001','Lily',1997)
DELETEFROMS2
WHEREGRADE=1999
(8)
UPDATEVIEWS
SETSCORE=SCORE+5
WHERESNAME='uxjof'
(9)
DROPVIEWVIEWC
DROPVIEWVIEWS
DROPVIEWS1
实验1.5
(1)GRANTSELECTONSTUDENTSTOPUBLIC
(2)GRANTSELECT,UPDATEONCOURSESTOPUBLIC
(3)
GRANTSELECT,UPDATE(SALARY)ONTEACHERSTOUSER1
WITHGRANTOPTION
(4)
GRANTSELECT,UPDATE(SCORE)ONCHOICESTOUSER2
(5)
CREATEVIEWTVASSELECTTID,TNAME,EMAIL,SALARYFROMTEACHERS
GRANTSELECTONTVTOUSER2
(6)GRANTSELECTONTEACHERSTOUSER2WITHGRANTOPTION
(7)GRANTSELECTONTEACHERSTOUSER3WITHGRANTOPTION
GRANTSELECTONTEACHERSTOUSR2WITHGRANTOPTION
(8)REVOKESELECTONTEACHERSFROMUSER1CASCADE
(9)REVOKESELECT,UPDATEONCOURSESFROMUSER1,USER2
实验1.6
(1)SELECTCID,HOUR*18FROMCOURSES
(2)
选修C++的人数:
SELECTCOUNT(*)
FROMCHOICES
WHEREcid='10005'
不及格人数:
SELECTCOUNT(*)
FROMCHOICES
WHEREscore<60ANDcid='10005'
及格人数:
SELECTCOUNT(*)
FROMCHOICES
WHEREscore>=60ANDcid='10005'
(3)NULL:
SELECTCOUNT(*)
FROMCHOICES
WHEREscoreISNULLANDcid='10005'
(4)
SELECTDISTINCTSCOREFROMCHOICESWHEREcid=
(SELECTcidFROMCOURSESWHEREcname='C++')ORDERBYscore
(5)SELECTDISTINCTGRADEFROMSTUDENTSGROUPBYgrade
(6)
SELECTAVG(SCORE),COUNT(*),MAX(SCORE),MIN(SCORE)
FROMCHOICESGROUPBYcid
(7)
SELECTGRADEFROMSTUDENTSWHEREgrade>=ALL(SELECT
gradeFROMSTUDENTS)
(8)SELECTCOUNT(*)FROMS,TWHERET.TID=S.SID
实验2.1
(1)
USESchool
CreateTableClass
(
Class_idvarchar(4),namevarchar(10),Departmentvarchar(20)
constraintPK_ClassPrimarykey(Class_id)
)
(2)
USESchool
BeginTransactionT3
insertintoClassvalues('00001','01CSC','CS')
BeginTransactionT4
insertintoClassvalues('00001','01CSC','CS')
CommitTransactionT4
CommitTransactionT3
实验2.2
(1)
USESchool
createtableStu_Union(snochar(5)NOTNULLUNIQUE,
snamechar(8),
ssexchar
(1),
sageint,
sdeptchar(20),
constraintPK_Stu_UnionPRIMARYKEY(sno));
insertintoStu_Unionvalues('10000','王敏','1',23,'CS');
updateStu_Unionsetsno=''wheresdept='CS';
UPDATEStu_UnionSETsno='95002'WHEREsname='王敏';
select*fromStu_Union;
USESchool
setxact_aborton
begintransactionT1
insertintoStu_Unionvalues('95009','李勇','M',25,'EE');
insertintoStu_Unionvalues('95003','王浩','0',25,'EE');
insertintoStu_Unionvalues('95005','李勇','0',25,'EE');
select*fromStu_Union
committransactionT1
useSchool
insertintoStu_Unionvalues('10001','李勇','0',24,'EE');
select*fromStu_Union;
createtableCourse(
cnochar(4)NOTNULLUNIQUE,
cnamevarchar(50)notnull,
cpointsint,
constraintPKPRIMARYKEY(cno));
insertintoCoursevalues('0001','Computernetworks',2);
insertintoCoursevalues('0002','Database',3);
createtableSC
(
snochar(5)referencesStu_Union(sno)ondeletecascade,
cnochar(4)referencesCourse(cno)ondeletecascade,
gradeint,
constraintPK_SCPRIMARYKEY(sno,cno)
);
(2)
(3)
①
createtablehelp
(
sidchar(8),snamevarchar(20),help_idchar(8)NOTNULL
constraintPK_helpprimarykey(sid)
)
②
altertablehelp
addconstraintFK_helpforeignkey(help_id)referenceshelp(help_id)
(4)
①
createtableleader
(
sidchar(9),snamevarchar(20),myleaderchar(9)
constraintPK_leaderprimarykey(sid)
)
②
createtablemonitor
(
sidchar(9),snamevarchar(20),mymonitorchar(9)
constraintPK_monitorprimarykey(sid)
constraintFK_monitorforeignkey(mymonitor)referencesLeader(sid)
)
altertableleader
addconstraintFK_leaderforeignkey(myleader)references
Monitor(sid)
实验2.3
(1)
USESchool
Altertableworker
AddconstraintU3check(sage>=0);
(2)
USESchool
Go
createrulerule_sageas@valuebetween1and100
Go
Execsp_bindrulerule_sage,'worker.[sage]';
实验2.4
(1)
USESchool
go
createtriggerT4onworker
forinsert
as
if(selectsagefrominserted)<=(selectmax(sage)fromworker)
begin
print'Thesageofcouplemustbemorethantheexistedcouples`sage!
'
rollbacktransaction
End
(2)
USEschool
Go
CreatetriggerT5onworker
Forupdate
as
if(selectsagefrominserted)<=(selectsagefromdeleted)
Begin
print'Thesageofnewcouplemustbemorethanthesageofoldcouple!
'
rollbacktransaction
End
实验3.1
(1)
Execsp_addlogin'王二','123','school'
Go
useSchool
Go
Execsp_grantdbaccess'王二'
(2)
useSchool
execsp_revokedbaccess'王二'
execsp_droplogin'王二'
实验3.3
(1)
useSchool
go
createviewgrade2000as
select*fromSTUDENTSwheregrade='2000';
(2)
use
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 报告
![提示](https://static.bdocx.com/images/bang_tan.gif)