SQLServer数据库上机实验报告

1、数据库系统原理上机实验报告学号：1120131743姓名：谈兆年班级：07111301、实验目的与要求：熟练使用SQL®句掌握关系模型上的完整性约束机制二、实验内容1:利用SQL语句创建Employee数据库CREATE DATABASE Employee;结果：2：利用SQL®句在Employee数据库中创建人员表 person、月薪表salary及部门表dept。做法：按表1、表2、表3中的字段说明创建表1 person 表结构字段名数据类型字段长度允许空否字段说明P_noChar6Not Null工号，主键P_nameVarchar10Not Null姓名SexCha

2、r2Not Null性别BirthdateDatetimeNull出生日期ProfVarchar10Null职称DeptnoChar4Not Null部门代码，外键（参照 dept表）表2 salary 表结构字段名数据类型字段长度允许空否字段说明P_noChar6Not Null工号，主键，外键（参照person表）BaseDec5Null基本工资BonusDec5Null奖金，要求50FactDec5Null实发工资=基本工资+奖金MonthInt2Not Null月份表3 dept 表结构字段名数据类型字段长度允许空否字段说明DeptnoChar4Not Null部门代码，主键，Dnam

3、eVarchar10Not Null部门名称程序为：CREATE TABLE dept(deptno CHAR(4) PRIMARY KEY NOT NULL,dname VARCHAR(10) NOT NULL)CREATE TABLE Person(P_no CHAR(6) PRIMARY KEY Not Null,P_name VARCHAR(10) Not Null,Sex CHAR(2) Not Null,Birthdate Datetime Null,Prof VARCHAR(10) Null,Deptno CHAR(4) Not Null,FOREIGN KEY(Deptno)

4、REFERENCES dept(Deptno);CREATE TAB国ary(P_no Char(6) PRIMARY KEY Not Null,Base Dec(5),Bonus Dec(5) CHECK (Bonus>50),Fact as Base+Bonus,Month Int Not Null,FOREIGN KEY (P_no) REFERENCES person(P_no);结果：蒜校方台根目录,"uos口ft SQL Servers'iSQiL Server ®ZDH-37 (Wlndkms MTMJR餐阵NjEmpto总片,衰镇ft®

5、; Ha ukp Ij不，|由画|南其眼| g | * |4|日量ra«|事31个项目竽艮日荻-rj muvsoft sqtservetsR 口/L5WVW"组H 此；DH'37(WntfcwiPJT)l-j SBJtB j EmpJp/H 应关系国 S兼 而'视图 四用捌强 量用户 律角色 弱则 口式认 g用户定义的数费 总用户定义的国圈 片全主目录由U皿5的E model 由 mdb 由.North崎 nd 由g 岫5 由 U tflinpclbI-*1SWA M- Mb. M二啊<flba用尸21DI3-12-13 1D:0D:562 Perso

6、ndba2013-12-1310:01 ：Z8二dbg2000*$ 1：言12"Isyscommerts心2000-3-6 l：zg：12因 systfcpendsdbaSOOO-fi-6 1:2942sysHe group tdbaZfltn-fl-e 1:29:123 sy5f4esdtoaJDO-O-6 1：Z9；lZsysHsstdba母5 1.：29：t2二3零货谢曰*ikewdba20m 中-6i；zg：iz3七炉tw8s葡B野61二以n fysfultaxEnalrf#dbci3000-3-6 1:23:L2Z 5ygmcl&xiMdbs3000-6 1:29：

7、L2与那bdeke产dba2000-6 1:29:12HsysmemberEdtoZODO-a-6 1:35123 FyxfejpG匕dbg2000-6 1;29：123号配的而内酊里孙2000-6 1:23-12百期即fopertiesdba20001:29:1221iyspfrtirtidba2flD0-fi-6 1:£g：L2二J sreferencesdto3000*0-6 1:29512国W呻ps工dba田皿舟 6 1:翁二12臼 sy Queersdb?zoDoasi：2gz3:利用SQL语句向表person、salary 和dept中插入数据。做法：按表4、表5、表6中

8、的数据插入。表4 表person中的数据P_noP_nameSexBirthDateProfDeptno000001王云男1973-4-7中级0001000002谢志文男1975-2-14中级0001000003李浩然男1970-8-25高级0002000004廖小玲女1979-8-6初级0002000005梁玉琼女1970-8-25中级0003000006罗向东男1979-5-11初级0003000007肖家庆男1963-7-14高级0003表5 表salary 中的数据P_noBaseBonusFactS_month0000012100300100000218003001000003280

9、0280100000425002501000005230027510000061750130100000724002101表6 表dept中的数据DeptnoDname0001人事部0002财务部0003市场部程序为：INSERT INTO dept(Deptno,Dname)values('0001',' 人事部');INSERT INTO dept(Deptno,Dname)values('0002','财务部')；INSERT INTO dept(Deptno,Dname)values('0003','

10、;市场部');呼的数据i，"employee囱 &g G 50? _0l! 一斗并；DeptnoDn，n±t 0001人事部 0002财务部0003市场部程序为：INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno)VALUES('000001','王云','男','1979-4-7',' 中级','0001');INSERT INTO person(P_no,P_name,Sex,Birthdate,P

11、rof,Deptno)VALUES('000002','谢志文,男','1975-2-14','中级','0001');INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno)VALUES('000003','李浩然,男','1970-8-25','高级','0002');VALUES('000004','廖小玲,女','1979-8-6&

12、#39;,' 初级,'0002');INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno)VALUES('000005','梁玉琼,女','1970-8-25',' 中级','0003');INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno)VALUES('000006','罗向东,男','1979-5-11',初级'

13、;,'0003');INSERT INTO person(P_no,P_name,Sex,Birthdate,Prof,Deptno)VALUES('000007',肖家庆,男','1963-7-14','高级','0003');2:君per5un中的数据,&Semploye- wZDH-38t图三直现豆皆上？行取|西加下依| T noFnameI AxFiirthdatt FrofI Dept no,OWHJ1_ 000002-000003000004000005000006000007王谢李廖梁

14、罗肖云志浩小玉向凉文然静琼东庆mn m J 05 El- 目耳.rl nF- m_z耳一1973-4-719TT5-2-14 mm咨 19TT9-8-6 团0-日召 igrrs-s-u 1963-7-14中中高初平初高00010001箕Q20002000300030003程序为：INSERT INTO salary(P_no,Base,Bonus,Month)VALUES('000001','2100','300','1');INSERT INTO salary(P_no,Base,Bonus,Month)VALUES('

15、000002','1800','300','1');INSERT INTO salary(P_no,Base,Bonus,Month)VALUES('000003','2800','280','1');INSERT INTO salary(P_no,Base,Bonus,Month)VALUES('000004','2500','250','1');INSERT INTO salary(P_no,Base,Bon

16、us,Month)VALUES('000005','2300','275','1');INSERT INTO salary(P_no,Base,Bonus,Month)VALUES('000006','1750','130','1');INSERT INTO salary(P no,Base,Bonus,Month)VALUES('000007','2400','210','1');再亘面画直呼| 畸十|

17、却"备鉴| %Bas4BotiueFactMonth00000121003002400100000218003002100100000323002803060100000425002502750100000523002T5257510000051T501301S601OOOOOT24002102B101米4: (1)利用SQL语句修改表中的数据做法：将salary 表中工号为000006的员工工资增加为1800元,奖金增加为160元。修改的代码为：UPDATE salarySET Base=1800,Bonus=160WHERE P_no='000006'修改后的结果

18、为:kJP_HQBaseBom?FactMonth100000 J21003CD240C1200000218003002 DO1300000323002S0303014000004260D25 0275015000005230021525751OOOOOfi1800160196017JQ0J07即QU21 口261Q1(2)利用SQL语句删除表中的数据。要求：删除 person表中工号为000007的员工数据。删除的代码为：DELETEFROM personWHERE P_no='000007'修改后的结果为：冲突出错。不能够删除，是因为前面各表间都建立了联系，定义了外键，所以

19、在此不能够删除，如果要删除，前面各表之间必须解除相互间的关系，把定义的外键删除。查询语句为：select *from person查询结果为：臼 select -frcm结果_j消息P_noP_nameSexBiithdateProfDeptna1| 000001 I王云里1979-04-07 00:00:00 000中级000120D0W2谢志文曼1975-02-14 00:00:00 000中金00013000003李浩然里197CW8-25 00:00:00, m高级00024(MXXXM廛小玲女19708-06 00:00:00.初级00025OT0W5梁玉琼女197CWE-25 00

20、:00:00,000中泰00036000006罗向东弱19705-11 00:00:00,000初级0DD37肖家底里19607-14 DO:0Q:O0.(X»曷级0003g宣宜三成功执行.此处也可以看出“删除 person表中工号为000007的员工数据”这条命令没有成功，000007的信息还在5:条件查询做法：(1)查询person表中所有不重复的职称查询代码为：SELECT DISTINCT profFROM person;查询结果为:0 SELECT DISTINCT trcf-FROM cursor;t.叟级rN初二曷中(2)查询person表中职称为中级的所有员工数据查询

21、代码：SELECT *FROM personWHERE prof='中级'查询结果为:日 SELECT " FROM cerscn-WHERE prof=l 中夕5 1 ;4HIJ结果消息P_noP_nameSeaBirthdateProfDeptno1i OTQO01 :王云男1979-04-07 00:00:00,000中税00012000002谢志文里1975-02-14 W:00:K.OM中级DM13000005梁玉琼女19708 25 00:00:00,000中级DM3(3)查询person表中具有高级职称的男员工信息查询代码：SELECT *FROM pe

22、rsonWHERE prof='高级'AND Sex='男'查询结果为： SELECT 三FROM person-where四或j高级，AND 级，男1S结果l'j消息P_noP_narneSexBvthdateProfDeptno1回面T；李浩然男197CHJB-2500:00:00.000高级00022瀛而肖家庆里1W3-D7-1400:00:00 (XK)高级0003(4)查询person表中姓名为王云、谢志文、罗向东的员工数据查询代码：SELECT*FROM personWHERE P_name IN ('王云',谢志文'

23、,罗向东');查询结果为:日 SELECT'FROM personWHERE会工汽1,王云'J谢志文罗向东”6:使用 ORDER BYE序做法：利用SQL语句将工号在000003和000006之间的员工的月收入按 实发工资升序排序。代码：SELECT *FROM salary8:利用SQL语句查询人事部所有员工信息WHERE P_no BETWEEN 000003 AND 000006ORDER BY Fact ASC查询的结果为:日 SELECT 'FROM salaryWHERE F nc BETWEEN 000003 AtJD 000006 kORDER

24、BY Face ASC-1结果消息7:利用SQL语句查询各部门的实发工资总数代码：SELECT Dname, SUM (Fact)FROM person,salary,deptWHERE dept.Deptno=person.Deptno AND person.P_no=salary.P_noGROUP BY Dname查询结果为: SELECT Dnair.e, SUM iFactFROM persion., salaryWHERE dept.DeEt"G-peTson.Deptno AND person.P no=3alary.P no-GROUP BY Dnair.e中查询代码

25、:SELECTFROM person,deptWHERE dept.Deptno=person.Deptno AND Dname='人事部查询结果为：El SELECT ”AND Dname-人事部，FROM perscn. dept-WHERE dept i Deptno=r：er3cr；.r口结果3消息P_noP_nameSecBirthdateProfDeptnodeptnodname1HIHIIBIIBIIBIUII.II fOOODDl ；王云弱197W4J7 00:00.00 ODO中泰0M10001人事部2OODOD2谢志文里137)2 14 MW 00.000中级0W1

26、0001人事部9:表的内连接查询：做法：利用SQL语句查询person表中职称为中级的员工信息。查询代码：SELECT *FROM person,salary,deptWHERE person.P_no=salary.P_no AND person.Deptno=dept. Deptno AND prof='中 级查询结果为：L SELECTFROM 版四为 恕惠初，聪四WHE底噫魏姐四=恕朋取凡四丁口噫四IUJ四破四=悲*口段藻='中级'in口结果_J消息P_no PjiarrwSaxB«thda(ePrdDepin 口P-。BaseBonusFadMort

27、hdeptnodrame1| DW001彳王云再19加处07 00册 MHJ00中担0001QOffiMI210030024001(XXJ1人事部2D0W02 谢志文同1 磬 XJ2-M 00如8.000中级00010000021800300210010001人事部3QWQ05 梁玉琼女197D-OS-25 00:0(?.DOT中缴000300OT052300275257510003市场部10:表的外连接查询：做法：利用SQL®句查询每个员工1月份的工资和奖金查询代码：select salary.base,Bonus from salary left outer join perso

28、n on (salary.P_no = person.P_no);查询结果为:-select salary.kase,Sonus您野）已成功执阮-fxaro salary left cter join person on (salarVjP no = person,P no);baseBonus1J 2100 300218003DD3280C28042500250523002756ieoo16012400210口结果消息(local) (10.0' RTM) Ryan-PCF11:子查询：做法：利用SQL语句查询比工号为000005的员工实发工资高的所有员 工信息。查询代码为：SELE

29、CT DISTINCT person. * ,salary. *,dept.*FROM person,salary,deptWHERE Fact>(SELECT DISTINCT FactFROM salaryWHERE P_no='000005')AND person.P_no=salary.p_no ANDperson.Deptno=Dept.Deptno查询结果为： SELECT DISTINCT person. * fsalary. fdept.*1 FRQMwhere麻总2 iSELECT DISTINCT FactFROM salary/wwwwwfrWHER

30、E F no=, 000005 ' ")A2ID person. P no=salaxy .C no A2ID person. Deptno=Dept. Deptnc阳口结果消息P_noP_nameSexBirthdateProfDeptnoP_naBaseBonusFactMonthdeptnodname1i。皿皿3,李浩然里197。阳25 00:曲的 000高级WD2(MMMM323W2朝蝌1TO02财箝部2QQGGW廖小玲女1979486 00:00:00,000初级0002OQQOM250025027&01OTO?财势部3WWWM7肖家庆里1%W-14 M：(

31、W:OT.W0高级WD1WD0724OT210261D1WOS市场部12:定义外键约束做法：(1) 创建表时将person表的deptno列定义为外键，并参考 dept表 的歹!J deptno。代码为：FOREIGN KEY Deptno REFERENCES dept(Deptno) /* 在表级定义参照完整性 */(2)将salary表中的P_no设为外键，并使其参照person表中的列P_noo代码为：FOREIGN KEY P_no REFERENCES person(P_no) /* 在表级定义参照完整性 */其依赖关系为又寸象依赖关系-person区眦本的帮助依赖于person的

32、对象Q)person依赖的对象遭)依赖关系E1" 3 person3 salary对象依赖关系 person总脚本T 13驾助O依赖于parson的对象9) person依赖的对象也)依赖关系EI1 J personL一 3 dep t13:测试对主表进行插入、更新及删除操作时的影响。做法：(1)向表dept中插入一行数据(0004',研发部)，测试是否影响从表。代码为：INSERT INTO dept (Deptno, Dname)VALUES('0004','研发部')；测试结果：影响从表原因：由于插入的元祖的 Deptno属性的值在表pe

33、rson中找不到一个元祖，其Deptno属性的值与之相等，破坏了被参照表person的参照完整性。(2)将表dept中的部门号0003改为0006,测试是否影响从表。代码为：UPDATE deptSET Deptno='0003'WHERE Deptno='0006'测试结果： UPDATE 恭歌SET蕊蕊砒伸后 WHERE DeptnQ=QClQ3;I附j*消息,J窥别"，状卷口,转研二UPDATE语句与PEFESEWCE约束"FK_Ps r b en_Dspt no_0 5 ”鹏&丁；中突口该冲突发生于额据库/"1口理&

34、#39;，表”曲口，Mrw口g. eqXu 语句3备止。影响从表 原因：修改了 dept表中的一个属性，使得在 person表中找不到一个元祖，其 Deptno属性 的值与之相等。破坏了参照完整性(3)删除表dept中部门号为0001的员工的数据，测试是否影响从表。DELETEFROM deptWHERE Deptno='0001'测试结果：日 DELETEFRQH豌应WHERE Dptnc：二,仆口口二；I/I»r RTSS-消息E - J辎l T4状态刑第1口后行XLSHW 语句与 2EFSMNCE 约束"FK_Eefaan_Deptno_0 515C 6触*冲突。该冲突发生于数据库一包3口 丫-。. Person * r coiu 语句已终止，影响从表原因：删除了 dept表中的一个员工的数据，使得在 person表中定义的外键无法参照dept表，破坏了参照完整性。14:测试对从表进行插入、更新及删除