数据库上机实验报告.docx
- 文档编号:7829296
- 上传时间:2023-01-26
- 格式:DOCX
- 页数:25
- 大小:325.61KB
数据库上机实验报告.docx
《数据库上机实验报告.docx》由会员分享,可在线阅读,更多相关《数据库上机实验报告.docx(25页珍藏版)》请在冰豆网上搜索。
数据库上机实验报告
数据库实验报告(所有实验)
院系:
信息科学与工程学院
专业:
网络工程电子商务
班级:
姓名:
学号:
指导老师:
年月日
一、实验目的
熟练掌握SQL语句的插入、修改、删除、查询等相关语法。
能够使用Microsoft SQL Server和MySQL软件进行相关的数据库操作。
二、实验内容
1数据库的创建和使用;
2表的创建和使用;
3数据的插入、删除和修改;
4数据的查询;
5.视图的创建和使用。
实验一、
创建学生成绩管理数据库
在学生成绩管理数据库中,包括基本的三个关系:
student,course,sc。
(1)student(sno,sname,ssex,sage,sdept),表示学号,姓名,性别,年龄,所在系。
主键学号,姓名唯一,系默认cs。
(2)Course(cno,cname,cpno,ccredit),表示课程号,课程名,先行课程号,学分。
主键课程号
(3)SC(sno,cno,grade),表示学号,课程号,成绩。
外键,学号和课程号。
创建该数据库代码及其最后结果图如下:
createtablestudent(
snochar(20)primarykey,
snamechar(10)unique,
ssexchar
(2),
sagesmallint,
sdeptchar(12)default'cs'
)
createtablecourse(
cnochar(10)primarykey,
cnamechar(20),
cpnochar(10),
ccreditchar(6)
)
createtablesc(
snochar(20),
cnochar(10),
gradesmallint,
primarykey(sno,cno),
foreignkey(sno)referencesstudent(sno),
foreignkey(cno)referencescourse(cno)
)
实验二、
在实验一的结果上进行各种操作练习及其代码
一修改表结构,对学生表增加,电话号码一列,删除年龄一列。
altertablestudent
addpnumberchar(22)
altertablestudent
dropcolumnsage
二简单查询
例1.查询全体学生详细记录
select*
fromstudent
例2.查询信息系所有男生的学号、姓名、出生年份
selectsno,sname,sage
fromstudent
wheressex='男'
例3.查询选修过课的学生的学号
selectsno
fromsc
例4.查询年龄在25-30之间的学生姓名及性别。
selectsname,ssex
fromstudent
wheresagebetween25and30
例5.查询姓“欧阳”的学生。
selectsname
fromstudent
wheresnamelike'欧阳%'
例6.查询信息系IS,数学系MA和计算机系CS的学生。
selectsname
fromstudent
wheresdept='is'orsdept='ma'orsdept='cs'
例7查询所有学生的成绩及姓名
selectgrade,sname
fromstudent,sc
wheresc.sno=student.sno
例8查询‘95001‘学生的所选的课程,成绩和专业
selectcname,grade,sdept
fromstudent,sc,course
wherestudent.sno=sc.snoando=oandstudent.sno='95001'
实验三、四
关系模式如下:
数据内容如下:
创建数据库并使用数据库
createdatabaseEnterprise;
useEnterprise;
创建表EMPLOYEE并插入数据
createtableEMPLOYEE(
FNAMEchar(20),
MINITchar
(1),
LNAMEchar(20),
SSNchar(9)primarykey,
BDATEdatetime,
ADDRESSchar(50),
SEXchar
(1)check(SEX='M'orSEX='F'),
SALARYint,
SUPERSSNchar(9),
DNOsmallint,
foreignkey(SUPERSSN)referencesEMPLOYEE(SSN)
);
insertintoEMPLOYEEvalues('James','E','Bong','888665555',1937-11-10,'450Stone,Houston,TX','M',55000,null,1);
insertintoEMPLOYEEvalues('Franklin','T','Wong','333445555',1955-12-08,'638Voss,Houston,Tx','M',40000,'888665555',5);
insertintoEMPLOYEEvalues('Jennifer','S','Wallace','987654321',1941-06-20,'291Berry,Bellaire,TX','F',43000,'888665555',4);
insertintoEMPLOYEEvalues('Ramesh','K','Narayan','666884444',1962-09-15,'975FireOak,Humble,TX','M',38000,'333445555',5);
insertintoEMPLOYEEvalues('Joyce','A','English','453453453',1972-07-31,'5631Rice,Houston,TX','F',25000,'333445555',5);
insertintoEMPLOYEEvalues('John','B','Smith','123456789',1965-01-09,'731Fondren,Houston,TX','M',30000,'333445555',5);
insertintoEMPLOYEEvalues('Alicia','J','Zelaya','999887777',1968-07-19,'3321Castle,Spring,TX','F',25000,'987654321',4);
insertintoEMPLOYEEvalues('Ahmad','V','Jabbar','987987987',1969-03-29,'980Dallas,Houston,TX','M',25000,'987654321',4);
创建表DEPARTMENT并插入数据
createtableDEPARTMENT(
DNAMEchar(20),
DNUMBERsmallintprimarykey,
MGRSSNchar(9),
MGRSTARTDATEdatetime
);
insertintoDEPARTMENTvalues('Research',5,'333445555',1988-05-22);
insertintoDEPARTMENTvalues('Administration',4,'987654321',1995-01-01);
insertintoDEPARTMENTvalues('Headquarters',1,'888665555',1981-06-19);
创建表PROJECT并插入数据
createtablePROJECT(
PNAMEchar(20),
PNUMBERsmallintprimarykey,
PLOCATIONchar(20),
DNUMsmallint,
foreignkey(DNUM)referencesDEPARTMENT(DNUMBER)
);
insertintoPROJECTvalues('ProductX',1,'Bellaire',5);
insertintoPROJECTvalues('ProductY',2,'Sugarland',5);
insertintoPROJECTvalues('ProductZ',3,'Houston',5);
insertintoPROJECTvalues('Computerization',10,'Stafford',4);
insertintoPROJECTvalues('Reorganization',20,'Houston',1);
insertintoPROJECTvalues('Newbenefits',30,'Stafford',4);
创建表WORKS_ON并插入数据
createtableWORKS_ON(
ESSNchar(9),
PNOsmallint,
HOURSfloat
(1),
primarykey(ESSN,PNO),
foreignkey(ESSN)referencesEMPLOYEE(SSN),
foreignkey(PNO)referencesPROJECT(PNUMBER)
);
insertintoWORKS_ONvalues('123456789',1,32.5);
insertintoWORKS_ONvalues('123456789',2,7.5);
insertintoWORKS_ONvalues('666884444',3,40.0);
insertintoWORKS_ONvalues('453453453',1,20.0);
insertintoWORKS_ONvalues('453453453',2,20.0);
insertintoWORKS_ONvalues('333445555',2,10.0);
insertintoWORKS_ONvalues('333445555',3,10.0);
insertintoWORKS_ONvalues('333445555',10,10.0);
insertintoWORKS_ONvalues('333445555',20,10.0);
insertintoWORKS_ONvalues('999887777',30,30.0);
insertintoWORKS_ONvalues('999887777',10,10.0);
insertintoWORKS_ONvalues('987987987',10,35.0);
insertintoWORKS_ONvalues('987987987',30,5.0);
insertintoWORKS_ONvalues('987654321',30,20.0);
insertintoWORKS_ONvalues('987654321',20,15.0);
insertintoWORKS_ONvalues('888665555',20,null);
创建表DEPT_LOACTION并插入数据
createtableDEPT_LOCATION(
DNUMBERsmallint,
DLOCATIONchar(20),
primarykey(DNUMBER,DLOCATION),
foreignkey(DNUMBER)referencesDEPARTMENT(DNUMBER)
);
insertintoDEPT_LOCATIONvalues(1,'Houston');
insertintoDEPT_LOCATIONvalues(4,'Stafford');
insertintoDEPT_LOCATIONvalues(5,'Bellaire');
insertintoDEPT_LOCATIONvalues(5,'Sugarland');
insertintoDEPT_LOCATIONvalues(5,'Houston');
创建表DEPENDENT并插入数据
createtableDEPENDENT(
ESSNchar(9),
DEPENDENT_NAMEchar(20),
SEXchar
(1),
BDATEdatetime,
RELATIONSHIPchar(10),
primarykey(ESSN,DEPENDENT_NAME),
foreignkey(ESSN)referencesEMPLOYEE(SSN)
);
insertintoDEPENDENTvalues('333445555','Alice','F',1986-04-05,'DAUGHTER');
insertintoDEPENDENTvalues('333445555','Theodore','M',1983-10-25,'SON');
insertintoDEPENDENTvalues('333445555','Joy','F',1958-05-03,'SPOUSE');
insertintoDEPENDENTvalues('987654321','Abner','M',1942-02-28,'SPOUSE');
insertintoDEPENDENTvalues('123456789','Michael','M',1988-01-04,'SON');
insertintoDEPENDENTvalues('123456789','Alice','F',1988-12-30,'DAUGHTER');
insertintoDEPENDENTvalues('123456789','Elizabeth','F',1967-05-05,'SPOUSE');
查询操作
Retrievethebirthdateandaddressoftheemployeewhosenameis'JohnB.Smith'.
selectBDATE,ADDRESSfromEMPLOYEE
whereFNAME='John'andMINIT='B'andLNAME='Smith';
Retrievethenameandaddressofallemployeeswhoworkforthe'Research'department.
selectFNAME,ADDRESSfromEMPLOYEE,DEPARTMENT
whereDEPARTMENT.MGRSSN=EMPLOYEE.SSN
andDEPARTMENT.DNAME='Research';
Foreveryprojectlocatedin'Stafford',listtheprojectnumber,thecontrollingdepartmentnumber,andthedepartmentmanager'slastname,address,andbirthdate.
selectPNUMBER,DNAME,LNAME,BDATE,ADDRESS
fromPROJECT,DEPARTMENT,DEPT_LOCATION,EMPLOYEE
whereDEPT_LOCATION.DLOCATION='Stafford'
andDEPT_LOCATION.DNUMBER=PROJECT.DNUM
andDEPT_LOCATION.DNUMBER=DEPARTMENT.DNUMBER
andDEPARTMENT.MGRSSN=EMPLOYEE.SSN;
Foreachemployee,retrievetheemployee'sname,andthenameofhisorherimmediatesupervisor.
selectEP.FNAME,EP.MINIT,EP.LNAME,ES.FNAME,ES.MINIT,ES.LNAME
fromEMPLOYEEEP,EMPLOYEEES
whereEP.SUPERSSN=ES.SSN;
RetrievetheSSNvaluesforallemployees.
selectSSNfromEMPLOYEE;
Retrievethenamesofemployeeswhohavenodependents.
selectSSNfromEMPLOYEE
wherenotexists(
selectESSNfromDEPENDENT
whereEMPLOYEE.SSN=ESSN);
Retrievethenameofeachemployeewhoworksonalltheprojectscontrolledbydepartmentnumber5.
selectdistinctFNAME,LNAME
fromEMPLOYEE,PROJECT,DEPARTMENT,WORKS_ON
whereDEPARTMENT.DNUMBER=5
andPROJECT.DNUM=DEPARTMENT.DNUMBER
andPROJECT.PNUMBER=WORKS_ON.PNO
andEMPLOYEE.SSN=WORKS_ON.ESSN;
Makealistofallprojectnumbersforprojectsthatinvolveanemployeewhoselastnameis'Smith'asaworkerorasamanagerofthedepartmentthatcontrolstheproject.
selectdistinctWORKS_ON.PNO
fromEMPLOYEE,WORKS_ON,PROJECT,DEPARTMENT
whereEMPLOYEE.LNAME='Smith'
and(EMPLOYEE.SSN=WORKS_ON.ESSN
orPROJECT.DNUM=DEPARTMENT.DNUMBER
andDEPARTMENT.MGRSSN=EMPLOYEE.SSN);
Retrievethesocialsecuritynumbersofallemployeeswhoworkonprojectnumber1,2,or3.
selectdistinctESSNfromWORKS_ON,PROJECT
whereWORKS_ON.PNO=PROJECT.PNUMBER
and(PROJECT.PNUMBER=1orPROJECT.PNUMBER=2orPROJECT.PNUMBER=3)
Retrievethenamesofallemployeeswhodonothavesupervisors
selectFNAMEfromEMPLOYEE
whereSUPERSSNisnull
Findthemaximumsalary,theminimumsalary,andtheaveragesalaryamongallemployees.
selectmax(SALARY)'maximumsalary',
min(SALARY)'minimumsalary',
avg(SALARY)'averagesalary'
fromEMPLOYEE
Findthemaximumsalary,theminimumsalary,andtheaveragesalaryamongemployeeswhoworkforthe'Research'department.
selectmax(SALARY)'maximumsalary',
min(SALARY)'minimumsalary',
avg(SALARY)'averagesalary'
fromEMPLOYEE,DEPARTMENT
whereEMPLOYEE.DNO=DEPARTMENT.DNUMBER
andDEPARTMENT.DNAME='Research';
Retrievethetotalnumberofemployeesinthecompany
selectcount(FNAME)'totalnumber'fromEMPLOYEE;
Retrievethenumberofemployeesinthe'Research'department
selectcount(FNAME)'totalnumber'fromEMPLOYEE,DEPARTMENT
whereEMPLOYEE.DNO=DEPARTMENT.DNUMBER
andDEPARTMENT.DNAME='Research';
Foreachdepartment,retrievethedepartmentnumber,thenumberofemployeesinthedepartment,andtheiraveragesalary.
selectDEPARTMENT.DNUMBER,count(EMPLOYEE.DNO)'count',avg(EMPLOYEE.SALARY)'averagesalary'
fromDEPARTMENT,EMPLOYEE
whereDEPARTMENT.DNUMBER=EMPLOYEE.DNO
groupbyDEPARTMENT.DNUMBER
Foreachproject,retrievetheprojectnumber,projectname,andthenumberofemployeeswhoworkonthatproject.
selectPROJECT.PNAME,count(PROJECT.PNUMBER)'count'
fromPROJECT,WORKS_ON
wherePROJECT.PNUMBER=WORKS_ON.PNO
groupbyPROJECT.PNAME
Fore
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 上机 实验 报告
![提示](https://static.bdocx.com/images/bang_tan.gif)