数据库原理上机实验-代码及截图

《数据库原理》上机实验报告2017年11月1一、实验目的与要求：熟练使用SQL定义子语言、操纵子语言命令语句掌握关系模型上的完整性约束机制掌握一定的数据库管理技术能完成简单的数据库应用开发二、实验内容1、实验一到实验十七（一）数据定义子语言实验（2学时）实验1：利用SQL语句创建Employee数据库代码如下：createdatabaseEmployee;运行结果：实验2：利用SQL语句在Employee数据库中创建人员表person、月薪表salary及部门表dept,暂不定义外键约束。要求：按表1、表达、表3中的字段说明创建表1person表结构字段名数据类型字段长度允许空否字段说明2P_noChar6NotNull工号，主键P_nameVarchar10NotNull姓名SexChar2NotNull性别BirthdateDatetime8Null出生日期ProfVarchar10Null职称DeptnoChar4NotNull部门代码，外键（参照dept表）表2salary表结构字段名数据类型字段长度允许空否字段说明P_noChar6NotNull工号，主键，外键（参照person表）BaseDec5Null基本工资BonusDec5Null奖金，要求50FactDec5Null实发工资=基本工资+奖金MonthInt2NotNull月份表3dept表结构字段名数据类型字段长度允许空否字段说明DeptnoChar4NotNull部门代码，主键，DnameVarchar10NotNull部门名称代码如下：createtableperson(P_nochar(6)notnullprimarykey,P_namevarchar(10)notnull,Sexchar(2)notnull,Birthdatedatetimenull,Profvarchar(10)null,Deptnochar(4)notnull);createtablesalary(P_nochar(6)notnullprimarykey,Basedec(5)null,Bonusdec(5)null,Factdec(5)null,Monthintnotnull);createtabledept(Deptnochar(4)notnullprimarykey,Dnamevarchar(10)notnull);3运行结果：4（二）数据操纵子语言实验（4学时）实验3：利用SQL语句向表person、salary和dept中插入数据。要求：按表4、表5、表6中的数据插入。表4表person中的数据P_noP_nameSexBirthDateProfDeptno000001王云男1973-4-7中级0001000002谢志文男1975-2-14中级0001000003李浩然男1970-8-25高级00025000004廖小玲女1979-8-6初级0002000005梁玉琼女1970-8-25中级0003000006罗向东男1979-5-11初级0003000007肖家庆男1963-7-14高级0003000007肖家庆男1963-7-14高级0003代码如下：insertintoperson(P_no,P_name,Sex,Birthdate,Prof,Deptno)values('000001','王云','男','1973-4-7','中级','0001')insertintoperson(P_no,P_name,Sex,Birthdate,Prof,Deptno)values('000002','谢志文','男','1975-2-14','中级','0001')insertintoperson(P_no,P_name,Sex,Birthdate,Prof,Deptno)values('000003','李浩然','男','1970-8-25','高级','0002')insertintoperson(P_no,P_name,Sex,Birthdate,Prof,Deptno)values('000004','廖小玲','女','1979-8-6','初级','0002')insertintoperson(P_no,P_name,Sex,Birthdate,Prof,Deptno)values('000005','梁玉琼','女','1970-8-25','中级','0003')insertintoperson(P_no,P_name,Sex,Birthdate,Prof,Deptno)values('000006','罗向东','男','1979-5-11','初级','0003')insertintoperson(P_no,P_name,Sex,Birthdate,Prof,Deptno)values('000007','尚家庆','男','1963-7-14','高级','0003')运行结果：6表5表salary中的数据P_noBaseBonusFactS_month00000121003001000002180030010000032800280100000425002501000005230027510000061750130100000724002101代码如下：insertintosalary(P_no,Base,Bonus,Fact,Month)values('000001',2100,300,2100+300,1)insertintosalary(P_no,Base,Bonus,Fact,Month)values('000002',1800,300,1800+300,1)insertintosalary(P_no,Base,Bonus,Fact,Month)values('000003',2800,280,2800+280,1)insertintosalary(P_no,Base,Bonus,Fact,Month)values('000004',2500,250,2500+500,1)insertintosalary(P_no,Base,Bonus,Fact,Month)values('000005',2300,275,2300+275,1)insertintosalary(P_no,Base,Bonus,Fact,Month)values('000006',1750,130,1750+130,1)insertintosalary(P_no,Base,Bonus,Fact,Month)values('000007',2400,210,2400+210,1)运行结果：7表6表dept中的数据DeptnoDname0001人事部0002财务部0003市场部代码如下：insertintodept(Deptno,Dname)values('0001','人事部')insertintodept(Deptno,Dname)values('0002','财务部')insertintodept(Deptno,Dname)values('0003','市场部')运行结果：8实验4：（1）利用SQL语句修改表中的数据。要求：将salary表中工号为000006的员工工资增加为1800元，奖金增加为160元。代码如下：updatesalarysetBase=1800,Bonus=160,Fact=1800+160whereP_no='000006'运行结果：9（2）利用SQL语句删除表中的数据。要求：删除salary表中工号为000007的员工数据。代码如下：deletefromsalarywhereP_no='000007'10（3）利用SQL语句查询person表中的所有数据。代码如下：select*fromperson运行结果：实验5：（1）创建视图要求：创建员工视图PersonView，包含员工的所有信息，并调用视图代码如下：createviewPersonViewasselectperson.P_no,P_name,Sex,Birthdate,Prof,person.Deptno,Base,Bonus,Fact,Month,Dnamefromperson,salary,deptwhereperson.Deptno=dept.Deptnoandsalary.P_no=person.P_no11select*fromPersonView运行结果：（2）删除视图12要求：将视图PersonView删除代码如下：dropviewPersonView运行结果：实验6：条件查询要求：（1）查询person表中所有不重复的职称。（2）查询person表中职称为中级的所有员工数据。（3）查询person表中具有高级职称的男员工信息。（4）查询person表中姓名为王云、谢志文、罗向东的员工数据。代码及运行结果如下：（1）selectdistinctProffromperson13（2）select*frompersonwhereProf='中级'（3）select*frompersonwhereProf='高级'andSex='男'14（4）select*frompersonwhereP_namein('王云','谢志文','罗向东')15实验7：使用ORDERBY排序要求：利用SQL语句将工号在000003和000006之间的员工的月收入按实发工资升序排序。代码如下：select*fromsalarywhereP_nobetween'000003'and'000006'orderbyFactasc运行结果：实验8：利用SQL语句查询各部门的实发工资总数。代码如下：selectdept.Dname,sum(Fact)as部门实发工资总数fromperson,salary,deptwhereperson.Deptno=dept.Deptnoandsalary.P_no=person.P_nogroupbydept.Dname运行结果：16实验9：利用SQL语句查询人事部所有员工信息。代码如下：select*fromperson,salary,deptwhereperson.Deptno=dept.Deptnoandsalary.P_no=person.P_noanddept.Dname=’人事部’运行结果：17实验10：表的内连接查询：要求：利用SQL语句查询person表中职称为中级的员工信息。代码如下：select*frompersonwhereprof='中级'运行结果：实验11：表的外连接查询：要求：利用SQL语句查询每个员工1月份的工资和奖金代码如下：selectperson.P_no,person.P_name,salary.Base,salary.Bonusfrompersonleftouterjoinsalaryon(person.P_no=salary.P_no)运行结果：18实验12：子查询：要求：利用SQL语句查询比工号为000005的员工实发工资高的所有员工信息。代码如下：select*fromperson,salarywhereFact(selectFactfromsalarywhereP_no='000005')andperson.P_no=salary.P_no19（三）数据完整性实验（1学时）实验13：定义外键约束要求：（1）创建表时将person表的deptno列定义为外键，并参考dept表的列deptno。代码如下：altertablepersonaddconstraintpersonkeyforeignkey(Deptno)referencesdept(Deptno)（2）将salary表中的P_no设为外键，并使其参照person表中的列P_no。代码如下：altertablesalaryaddconstraintsalarykeyforeignkey(P_no)referencesperson(P_no)20