数据库上机报告分析解析.docx

1、数据库上机报告分析解析数据库原理上机实验指导书专 业： 自动化、测控、信控指导老师： 杨彪 昆明理工大学信息工程与自动化学院2016年9月一、实验目的与要求： 熟练使用SQL定义子语言、操纵子语言命令语句 掌握关系模型上的完整性约束机制 掌握一定的数据库管理技术 能完成简单的数据库应用开发二、实验内容及学时安排（总学时：8）（一）数据定义子语言实验（2学时）实验1：利用SQL语句创建Employee数据库 CREATE DATABASE Employee实验2：利用SQL语句在Employee数据库中创建人员表person、月薪表salary及部门表dept。要求：按表1、表2、表3中的字段说

2、明创建表1 person表结构字段名数据类型字段长度允许空否字段说明及备注备注P_noChar6Not Null工号，主键P_nameVarchar10Not Null姓名SexChar2Not Null性别BirthdateDatetime8Null出生日期ProfVarchar10Null职称DeptnoChar4Not Null部门代码实验12以后作为外键（参照dept表）表2 salary表结构字段名数据类型字段长度允许空否字段说明及备注P_noChar6Not Null工号，主键 实验12以后作为外键（参照person表）BaseDec5Null基本工资BonusDec5Null奖金

3、，要求50FactDec5Null实发工资=基本工资+奖金MonthInt2Not Null月份表3 dept表结构字段名数据类型字段长度允许空否字段说明DeptnoChar4Not Null部门代码，主键，DnameVarchar10Not Null部门名称CREATE TABLE dept( Deptno CHAR(4) NOT NULL PRIMARY KEY, Dname VARCHAR(10) NOT NULL);CREATE TABLE person (P_no CHAR(6) NOT NULL PRIMARY KEY, P_name VARCHAR(10) NOT NULL, S

4、ex CHAR(2) NOT NULL, Birthday Datetime, Prof VARCHAR(10), Deptno CHAR(4) NOT NULL);CREATE TABLE salary (P_no CHAR(6) NOT NULL PRIMARY KEY, Base Dec(5), Bonus Dec(5) CHECK(Bonus 50), Fact AS Base + Bonus, Mon int, );（二）数据操纵子语言实验（4学时）实验3：利用SQL语句向表person、salary和dept中插入数据。要求：按表4、表5、表6中的数据插入。表4 表person中的

5、数据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答案：INSERT INTO person (P_no, P_name, Sex, BirthDay, Prof, Deptno) VALUES(000001, 王云, 男, 1973-4

6、-7, 中级, 0001);INSERT INTO person VALUES(000002, 谢志文,男, 1975-2-17, 中级, 0001);INSERT INTO person (P_no, P_name, Sex, BirthDay, Prof, Deptno) VALUES(000003, 李浩然, 男, 1970-8-25, 高级, 0002);INSERT INTO person (P_no, P_name, Sex, BirthDay, Prof, Deptno) VALUES(000004, 廖小玲, 女, 1970-8-25, 初级, 0002);INSERT INT

7、O person (P_no, P_name, Sex, BirthDay, Prof, Deptno) VALUES(000005, 梁玉琼, 女, 1970-5-11, 中级, 0003);INSERT INTO person (P_no, P_name, Sex, BirthDay, Prof, Deptno) VALUES(000006, 罗想动, 男, 1979-5-11, 初级, 0003);INSERT INTO person (P_no, P_name, Sex, BirthDay, Prof, Deptno) VALUES(000007, 肖家庆, 男, 1963-7-14,

8、 高级, 0003); 表5 表salary中的数据P_noBaseBonusFactS_month00000121003001000002180030010000032800280100000425002501000005230027510000061750130100000724002101答案：INSERT INTO salary (P_no, Base, Bonus, Mon) VALUES(000001, 2100, 300, 1);INSERT INTO salary (P_no, Base, Bonus, Mon) VALUES(000002, 1800, 300, 1);INS

9、ERT INTO salary (P_no, Base, Bonus, Mon) VALUES(000003, 2800, 280, 1);INSERT INTO salary (P_no, Base, Bonus, Mon) VALUES(000004, 2500, 250, 1);INSERT INTO salary (P_no, Base, Bonus, Mon) VALUES(000005, 2300, 275, 1);INSERT INTO salary (P_no, Base, Bonus, Mon) VALUES(000006, 1750, 130, 1);INSERT INTO

10、 salary (P_no, Base, Bonus, Mon) VALUES(000007, 2400, 210, 1);表6 表dept中的数据DeptnoDname0001人事部0002财务部0003市场部答案：INSERT INTO dept (Deptno, Dname) VALUES(001, 人事部);INSERT INTO dept VALUES(002, 财务部);INSERT INTO dept VALUES(003, 市场部);实验4：（1）利用SQL语句修改表中的数据。要求：将salary表中工号为000006的员工工资增加为1800元，奖金增加为160元。UPDATE

11、 salary SET Base = 1800, Bonus = 160 WHERE P_no = 000006;（2）利用SQL语句删除表中的数据。要求：删除person表中工号为000007的员工数据。DELETE FROM person WHERE P_no = 000007;（3）利用SQL语句查询person表中的所有数据。SELECT *FROM person 实验5：（1）更新视图要求：将员工视图PersonView中姓名为王云的员工职称改为高职CREATE VIEW PView AS SELECT * FROM person UPDATE PViewSET prof =高职WH

12、ERE P_name = 王云（2）向视图中插入数据 要求：向视图PersonView中插入一行数据（000008，刘美萍，中级，0002）INSERT INTO PView(P_no, P_name, prof, Deptno)VALUES(000008, 刘美萍, 中级, 0002);（3）删除视图 要求：将视图PersonView删除DROP VIEW PView;实验6：条件查询 要求：（1） 查询person表中所有不重复的职称。SELECT DISTINCT prof FROM Person;（2） 查询person表中职称为中级的所有员工数据。SELECT * FROM pers

13、on WHERE Prof = 中级;（3） 查询person表中具有高级职称的男员工信息。SELECT * FROM person WHERE Prof = 高级 AND Sex = 男;（4） 查询person表中姓名为王云、谢志文、罗向东的员工数据。SELECT * FROM person WHERE P_name= 王云 OR P_name= 谢志文 OR P_name= 罗想动;或者SELECT * FROM person WHERE P_name IN(王云,谢志文,罗想动)实验7：使用ORDER BY排序 要求：利用SQL语句将工号在000003和000006之间的员工的月收入按

14、实发工资升序排序。SELECT * FROM Salary WHERE P_no BETWEEN 000003 AND 000006ORDER BY Fact ASC;实验8：利用SQL语句查询各部门的实发工资总数。SELECT dept.Dname, sum(Fact) AS 部门实发工资总数 FROM dept, salary, personWHERE person.P_no = salary.P_no AND person.Deptno = dept.DeptnoGROUP BY dept.Dname;实验9：利用SQL语句查询人事部所有员工信息。SELECT * FROM Person

15、 WHERE Deptno = 0001实验10：表的内连接查询：要求：利用SQL语句查询person表中职称为中级的员工信息。SELECT * FROM Person WHERE prof = 中级;实验11：表的外连接查询：要求：利用SQL语句查询每个员工1 月份的工资和奖金SELECT Salary.P_no , Salary.Base, Salary.Bonus FROM Person JOIN Salary ON Person.P_no = Salary.P_no;实验12：子查询：要求：利用SQL语句查询比工号为000005的员工实发工资高的所有员工信息。SELECT * FROM

16、 Person, Salary WHERE Fact (SELECT Fact FROM Salary WHERE P_no = 000005);（三） 数据完整性实验（1学时）实验12：定义外键约束 要求：（1） 创建表时将person表的deptno列定义为外键，并参考dept表的列deptno。 CREATE TABLE person(P_no CHAR(6) NOT NULL PRIMARY KEY,P_name VARCHAR(10) NOT NULL,Sex CHAR(2) NOT NULL,Birthday Datetime,Prof VARCHAR(10),Deptno CHA

17、R(4) NOT NULL,FOREIGN KEY (Deptno) REFERENCES dept(Deptno);ALTER TABLE person ADD FOREIGN KEY (Deptno) REFERENCES dept(Deptno) （2） 将salary表中的P_no设为外键，并使其参照person表中的列P_no。CREATE TABLE salary(P_no CHAR(6) NOT NULL PRIMARY KEY,Base Dec(5),Bonus Dec(5) CHECK(Bonus 50),Fact AS Base + Bonus,Mon int,FOREIG

18、N KEY (P_no) REFERENCES person(P_no);ALTER TABLE salary ADD FOREIGN KEY (P_no) REFERENCES person (P_no)实验13：测试对主表进行插入、更新及删除操作时的影响。（请写明原因） 要求：（1） 向表dept中插入一行数据（0004，研发部），测试是否影响从表。INSERT INTO dept(Deptno, Dname) VALUES(0004, 研发部);不影响（2） 将表dept中的部门号0003改为0006，测试是否影响从表。UPDATE dept SET Deptno =0006 WHERE

19、 Deptno = 0003;-影响:UPDATE 语句与 REFERENCE 约束FK_person_Deptno_239E4DCF冲突。该冲突发生于数据库Employee，表dbo.person, column Deptno。-语句已终止。（3） 删除表dept中部门号为0001的员工的数据，测试是否影响从表。DELETE FROM dept WHERE Deptno = 0001;-影响:DELETE 语句与 REFERENCE 约束FK_person_Deptno_239E4DCF冲突。该冲突发生于数据库Employee，表dbo.person, column Deptno。-语句已终

20、止。实验14：测试对从表进行插入、更新及删除操作时的影响。（请写明原因）要求：（1） 向表person中插入一行数据（000012，宋全礼，男、1980-7-17，初级，0005），测试是否违背参照完整性。INSERT INTO Person VALUES(000012,宋全礼,男,1980-7-17,初级,0005);-影响:INSERT 语句与 FOREIGN KEY 约束FK_person_Deptno_239E4DCF冲突。该冲突发生于数据库Employee，表dbo.dept, column Deptno。（2） 删除表中工号为000005的员工数据，测试是否违背参照完整性。DELE

21、TE FROM person WHERE P_no = 000005; -不影响（四） SQL Server安全设置实验（1学时） 实验 15：创建登录账号（1） 创建Windows登录账号 要求：基于Windows组成员或用户账号创建登录账号Market000005（2） 建SQL Server登录账号要求：基于SQL Server创建新的登录账号000005（3） 删除登录账号 要求：删除000005及Market000005登录账号 实验 16：数据库用户设置 （1）基于Windows登录的设置要求：授权Market域的000005用户访问数据库Employee（2）基于SQL Serv

22、er登录的设置 要求：授权000005登录账号以000005用户身份访问数据库Employee（4） 特殊的数据库用户账号guest的使用要求：将guest用户账号添加到数据库Employee中（5）删除数据库用户账号要求：从数据库Employee中删除000005用户三、实验报告撰写要求：1、 制作实验报告封面（见附录）。2、 内容顺序包括：一、 实验目的与要求：二、 实验内容：（1） 实验1至实验14：要标明是第几个实验、实验内容及实验要求，然后把程序代码及运行结果写出，应写成：实验1：（内容、要求）代码如下：create table AAA ()运行结果为：（运行结果）.（2） 设计的“Employee数据库系统”要求把设计思路及相应的界面打印出来，并作说明。三、 总结与体会附录：数据库原理上机实验报告封面数据库原理上机实验报告学 号： 姓 名： 班 级： 指导教师：昆明理工大学信息工程与自动化学院2006年11月