四川师范大学 数据库实验报告.docx
《四川师范大学 数据库实验报告.docx》由会员分享，可在线阅读，更多相关《四川师范大学 数据库实验报告.docx（29页珍藏版）》请在冰点文库上搜索。
四川师范大学数据库实验报告
实验一熟悉SQLSERVER的环境
（验证型实验2学时）
1.目的要求：
1）了解SQLServermanagementstudio的使用
2.实验内容：
回答下面每一个问题,写出实验步骤
1）在“已注册服务器窗口”中注册sqlserver数据库服务器
展开【数据库引擎】节点，右击LocalServerGroup节点，选择【新建服务器注册】命令，设置相应属性，单击【保存】即可。
2）在“对象资源管理器”中创建名字为sc的数据库
展开节点，右击【数据库】节点，选择【新建数据库】命令，在【数据库名称】后填写sc,设置相应属性，单击【确定】即可。
3）在sc数据库中创建一个名字为student的基本表
展开【数据库】节点，选择数据库并展开，右击表节点，选择【新建表】命令，设置行列信息，保存即可。
4）在查询窗口中里创建名为S_C的数据库
CREATEDATABASES_C
5）在查询窗口中使用sql语言创建名字为course的基本表
CREATETABLEcourse
3.主要设备及软件
1）PC
2）MicrosoftSQLServer2005
实验二建立表格，并插入若干记录
（验证型实验2学时）
1.目的要求：
1）学会使用CreateTable和Insert语句
2.实验内容
1）使用sql语言建立student,course和sc共三张表格（包括主键,外码的指定）,分析具体情况适当给出一些用户自定义的约束.
CREATETABLEStudent
（Snochar（9）PRIMARYKEY,
Snamechar（20）NOTNULL,
SsexCHAR
（2）,
SageSMALLINT,
Sdeptchar（20）
）;
CREATETABLECourse
（Cnochar（4）PRIMARYKEY,
Cnamechar（14）,
Cpnochar（4）,
Ccreditsmallint,
FOREIGNKEY（Cpno）REFERENCESCourse（Cno）,
）;
CREATETABLESC
（Snochar（9）,
Cnochar（4）,
Gradesmallint,
PRIMARYKEY（Sno,Cno）,
FOREIGNKEY（Sno）REFERENCESStudent（Sno）,
FOREIGNKEY（Cno）REFERENCESCourse（Cno）,
）;
2）使用Insert语句向这三张表格里添加至少10条记录（数据如教材56页所示）,如果出现错误,分析错误原因
INSERTINTOStudentVALUES（'95001','李勇','男',20,'CS'）;
INSERTINTOStudentVALUES（'95002','刘晨','女',20,'IS'）;
INSERTINTOStudentVALUES（'95003','王敏','女',20,'MA'）;
INSERTINTOStudentVALUES（'95004','张立','男',20,'IS'）;
SELECT*FROMStudent;
INSERTINTOCourse（Cno,Cname,Ccredit）VALUES（'1','数据库',4）;
INSERTINTOCourse（Cno,Cname,Ccredit）VALUES（'2','数学',2）;
INSERTINTOCourse（Cno,Cname,Ccredit）VALUES（'3','信息系统',4）;
INSERTINTOCourse（Cno,Cname,Ccredit）VALUES（'4','操作系统',3）;
INSERTINTOCourse（Cno,Cname,Ccredit）VALUES（'5','数据结构',4）;
INSERTINTOCourse（Cno,Cname,Ccredit）VALUES（'6','数据处理',2）;
INSERTINTOCourse（Cno,Cname,Ccredit）VALUES（'7','PASCAL语言',4）;
UPDATECourseSETCpno='5'WHERECno='1';
UPDATECourseSETCpno='1'WHERECno='3';
UPDATECourseSETCpno='6'WHERECno='4';
UPDATECourseSETCpno='7'WHERECno='5';
UPDATECourseSETCpno='6'WHERECno='7';
SELECT*FROMCourse;
INSERTINTOSCVALUES（'95001','1',92）;
INSERTINTOSCVALUES（'95001','2',85）;
INSERTINTOSCVALUES（'95001','3',88）;
INSERTINTOSCVALUES（'95002','2',90）;
INSERTINTOSCVALUES（'95002','3',80）;
SELECT*FROMSC;
3）在“对象资源管理器”中实现
（1）题中的三张表
展开【数据库】节点，单击【表】节点，选择【新建表】，填入如下数据
保存的时候保存为Student就行了
展开【数据库】节点，单击【表】节点，选择【新建表】，填入如下数据
保存的时候保存为Course
设置外键如图所示
展开【数据库】节点，单击【表】节点，选择【新建表】，填入如下数据
保存的时候保存为Course
设置外键如图所示
4）在“对象资源管理器”中向这
（1）题中的三张表添加至少10条记录（数据如教材56页所示）,如果出现错误,分析错误原因
第一张表中插入数据
第二张表中插入数据
第三张表插入数据
3.主要设备及软件
1）PC
2）MicrosoftSQLServer2005
实验三修改表格结构，修改和删除表格中的数据
（验证型实验4学时）
1.目的要求：
1）用ALTER语句修改表结构：
添加列，修改列定义，删除列。
使用UPDATE和DELETE语句修改和删除Student,sc,course表格中的数据。
2.实验内容
如下所示，创建表s，并完成随后的操作
Createtables（snochar
（2）primarykey,snamechar（10））;
1）向表中添加属性列status,数据类型为int
ALTERTABLEsADDstatusint;
2）向表中添加属性列city,数据类型为varchar（20）,并限定其取值范围为“上海”，“北京”，“天津”之一
ALTERtablesADDcityvarchar（20）;
AltertablesaddconstraintCK_citycheck（city='上海'orcity='北京'orcity='天津'）;
3）修改属性列status的数据类型为smallint
ALTERtablesALTERCOLUMNstatussmallint;
4）删除（3）题中取值范围的约束
ALTERtablesDROPCK_city;
5）删除属性列status
ALTERtablesDROPCK_city;
如教材85页所示，建立student,sc,course等表,并插入若干数据，完成如下操作：
列出没有成绩的学生的学号和课程号
SELECTSno,CnoFROMscWHEREGradeISNULL;
列出2号课程成绩在70分到80分学生的学号
SELECTSnoFROMscWHEREGrade=2ANDGradebetween70AND80;
查询所有2005级的学生的姓名，性别和所在系
SELECTSname,Ssex,SdeptFROMstudentWHERESnoLIKE'2005%';
查询计科系2004级全体学生的所有信息
SELECT*FROMstudentWHERESdept='js'ANDSnoLIKE'2004%';
查询计科系2006级3班和4班学生的姓名和性别
SELECTSname,SsexFROMstudentWHERESdept='js'AND（SnoLIKE'2006_3%'ORSnoLIKE'2006_4%'）;
查询数学系所有学生的姓名，性别和出生年份|
SELECTSname,Ssex,2012-SageFROMstudentWHERESdept='math';
将course表中名为PASCAL语言的课程更名为“C语言”
UPDATECourseSETCname='C语言'WHERECname='PASCAL'；
将所有课程的学分增加1分
UPDATECourseSETCcredit=Ccredit+1；
删除没有选课成绩的选课记录
DELETEFROMscWHEREGradeISNULL;
删除“IS”系的所有学生信息
DELETEFROMstudentWHERESdept='IS';
删除所有的课程信息
DELETEFROMCourse;
3.主要设备及软件
1）PC
2）MicrosoftSQLServer2005
实验四查询（多表查询，嵌套查询，分组查询）
（验证型实验12学时）
1.目的要求：
1）实现单表和多表的普通查询和嵌套查询。
包括返回单值的子查询和返回多值的子查询。
使用5个聚合函数以及GROUPBY子句和HAVING子句实现分组查询.
2.实验内容
有如下关系模式，分析每个关系模式的主码，外码，完成后面的查询
职员表：
Emp（eid:
integer;ename:
string,salary:
real）
部门表：
Dept（did:
integer,dname:
string,managerid:
integer,floornum:
integer）
职员与部分的关系表：
Works（eid:
integer,did:
integer）；
Works表表示：
一个职员可以在多个部门工作，一个部门有多个职员
Dept表中managerid可以取值null，表示尚未任命部门经理，floornum可以取值null，表示尚未分配工作地点
用单表查询完成如下操作：
1）输出所有员工的姓名和工资
SELECTename,salaryFROMEmp;
2）输出薪水少于10000或者大于100000的雇员的名字
SELECTenameFROMEmpWHEREsalary<10000orsalary>100000;
3）输出所有姓“欧阳”，且全名为四个字的雇员的姓名和工资
SELECTename,salaryFROMEmpWHEREenameLIKE'欧阳__';
4）输出薪水在20000和50000之间的雇员的名字
SELECTenameFROMEmpWHEREsalaryBETWEEN20000AND50000;
5）输出部门名字中含有“_”的所有部门的名字和楼层号
SELECTdname,floornumFROMDeptWHEREdnameLIKE'%\_%'ESCAPE'\';
6）查询公司的员工数
SELECTCOUNT（*）FROMEmp;
7）查询所有还没有部门经理的部门的名字和编号
SELECTdname,didFROMDeptWHEREmanageridisNULL;
8）查询所有已分配楼层的部门的所有信息
SELECT*FROMDeptWHEREfloornumisNOTNULL;
用连接查询完成如下操作：
1）查询“电视”部门的职工人数
SELECTCOUNT（did）FROMWorksWHEREWorks.did=（SELECTdidFROMDeptWHEREdname='电视'）;
2）输出每个部门的名字和平均工资
selectdname,avg（salary）fromemp,dept,workswhereemp.eid=works.eidanddept.did=works.didgroupbydname;
3）查询每个部门的部门编号，及其拥有的雇员的人数
selectDept.did,Count（eid）fromDept,WorksWHEREDept.did=Works.didGROUPBYDept.did;
4）查询在第10层工作，同时薪水少于￥50000的所有雇员的名字
SELECTDISTINCTenameFROMEmp,Dept,WorksWHEREEmp.eid=Works.eidANDWorks.did=Dept.didANDDept.floornum=10ANDsalary>50000;
5）输出同时管理三个或者更多部门的管理者的名字
selectenamefromdept,empwheredept.managerid=emp.eidgroupbymanagerid,enamehavingcount（did）>=3;
6）输出管理在同一层上10个以上部门的所有管理者的名字
SELECTenameFROMDept,EmpWHEREEmp.eid=Dept.manageridgroupbymanagerid,floornum,enamehavingCOUNT（did）>10;
7）输出雇员“刘丽”工作的部门的名字
SELECTdnameFROMEmp,Dept,WorksWHEREEmp.ename='刘丽'ANDEmp.eid=Works.eidANDWorks.did=Dept.did;
用嵌套查询完成如下操作：
1）查询工资最高的雇员的名字
SELECTenameFROMEmpWHEREsalary=（SELECTMAX（salary）FROMEmp）;
2）查询工资最低的雇员的名字及其所在部门的编号和名字
SELECTename,Dept.did,dnameFROMEmp,Dept,WorksWHEREsalary=（SELECTMIN（salary）FROMEmp）ANDEmp.eid=Works.eidANDWorks.did=Dept.did;
3）输出与Santa工作部门相同的所有雇员的所有信息
SELECTEmp.eid,ename,salaryFROMEmp,Dept,WorksWHEREEmp.eid=Works.eidANDWorks.did=Dept.didANDDept.didin（SELECTdidFROMWorksWHEREeid=（SELECTeidFROMEmpWHEREename='Santa'））;
4）找出薪水在20000以上，并且在电视部门或者玩具部门工作的雇员的名字
SELECTDISTINCTenameFROMEmp,Dept,WorksWHEREEmp.eid=Works.eidANDWorks.did=Dept.didANDsalary>20000ANDdnamein（SELECTdnameFROMDeptWHEREdname='电视'ordname='玩具'）;
5）输出与刘丽在同一层工作的雇员的名字
SELECTDISTINCTenameFROMEmp,Dept,WorksWHEREEmp.eid=Works.eidANDWorks.did=Dept.didANDfloornumin（SELECTDISTINCTfloornumFROMDept,Emp,WorksWHEREDept.did=Works.didANDWorks.eid=Emp.eidANDEmp.ename='刘丽'）;
6）输出比所在部门的经理挣的还要多的雇员的名字
SELECTDISTINCTenameFROMEmp,Dept,WorksWHEREsalary>All（SELECTsalaryFROMEmp,DeptWHEREEmp.eid=Dept.manageridANDEmp.eid=Works.eidANDWorks.did=Dept.didANDenamein（SELECTenameFROMEmp））;
7）输出满足如下条件的各个部门的名字：
经理的姓为张，同时他的薪水既不是本部门最高也不是最低
SELECTDISTINCTdnameFROMWorks,Dept,empwhereDept.managerid=emp.eidandenamelike'张%'ANDsalary<（selectmax（salary）FROMempWHEREeidIN（SELECTeidFROMworks,DeptWHEREworks.did=Dept.did））ANDsalary>（SELECTmin（salary）fromempwhereeidin（selecteidfromworkswhereworks.did=dept.did））;
8）输出比“玩具”部门所有职工工资都高的雇员的姓名
SELECTenameFROMEmpWHEREsalary>（SELECTmax（salary）FROMEmp,Works,DeptWHEREEmp.eid=Works.eidANDWorks.did=Dept.didANDDept.dname='玩具'）;
9）输出比“电视”部门职工平均工资高的雇员的姓名
SELECTenameFROMEmpWHEREsalary>（SELEcTAVG（salary）FROMEmp,Works,DeptWHEREEmp.eid=Works.eidANDWorks.did=Dept.didANDDept.dname='电视'GROUPBYDept.did）;
10）找出所有有职工的部门的名字和楼层号
SELECTdname,floornumFROMDeptWHEREdidin（SELECTDISTINCTdidFROMWorks）;
11）查询所有没有职工的部门编号和名字
SELECTdid,dnameFROMDeptWHEREdidnotin（SELECTDISTINCTdidFROMWorks）;
12）输出同时在玩具部门和糖果部门工作的雇员的名字和薪水
SELECTDISTINCTename,salaryFROMEmp,Works,DeptWHEREEmp.eid=Works.eidANDWorks.eidin（SELECTeidFROMWorks,DeptWHEREWorks.did=Dept.didANDDept.dname='玩具'）ANDWorks.eidin（SELECTeidFROMWorks,DeptWHEREWorks.did=Dept.didANDDept.dname='糖果'）;
3.主要设备及软件
1）PC
2）MicrosoftSQLServer2005
实验五为表格建立约束，修改约束和查询约束
（验证型实验4学时）
1.目的要求：
1）使用ALTER语句和CREATE语句建立、修改、删除和查询约束
2.实验内容：
执行以下SQL语句，完成随后的操作，若有错误，分析错误原因并改正错误：
createtablestudent（snochar（9）notnull,snamechar（10）,ssexchar
（2）,sagetinyint,sdeptvarchar（40））;
createtablecourse（cnochar（4）notnull,cnamevarchar（30）,cpnochar（4）,credittinyint）
createtablesc（snochar（9）,char（5）,gradenumeric（3,1））;
出错
第三句改为
createtablesc（snochar（9）,cnochar（5）,gradenumeric（3,1））
1）在student表中，使sdept只能取值“计算机科学学院”，“数软学院”，“电子工程学院”，“化学与材料科学学院”
AltertablestudentaddconstraintCK_sdeptcheck（sdept='计算机科学学院'orsdept='数软学院'orsdept='电子工程学院'orsdept='化学与材料科学学院'）;
2）在student表中sage有默认值18
ALTERTABLEstudentADDDEFAULT（'18'）FORsage;
3）为student表建立主键
ALTERTABLEstudentADDPRIMARYKEY（Sno）；
4）为course表建立主键和外键，其中外键约束名为C_FK_CPNO
ALTERTABLEcourseADDprimarykey（cno）,C_FK_CPNOCHAR（4）FOREIGNKEY（cpno）REFERENCEScourse（cno）;
5）为course表建立检查约束，限定credit的取值只能取3，2，4，5；
ALTERTABLEcourseADDCHECK（creditin（'3','2','4','5'））;
6）为course表建立唯一约束，确保每们课程名字唯一
ALTERTABLEcourseADDUNIQUE（cname）;
7）为sc表建立主键和外键，并给出相应的约束名
ALTERTABLEscADDconstraintPK_sno_cnoPRIMARYKEY（sno,cno）,FK_snoCHAR（9）FOREIGNKEY（sno）REFERENCESstudent（sno）,FK_cnoCHAR（4）FOREIGNKEY（cno）REFERENCEScourse（cno）;
8）在course表中插入元组（1,数据库，5，4）和（2,数学，null,2）,若不能正确插入，分析原因，给出解决办法
不能正确插入，因为数据类型不对，更改方案
INSERTINTOcourse（cno,cname,cpno,credit）VALUES（'1','数据库','5',4）;
INSERTINTOCourse（cno,cname,cpno,credit）VALUES（'2','数学',null,2）;
若前面的题中建立了约束关系，那删掉
9）在sc表中插入元组（95001,1,92）和
升级会员 