1、数据库原理上机实验指导书专 业: 自动化、测控、信控指导老师: 杨彪 昆明理工大学信息工程与自动化学院2016年9月一、实验目的与要求:l 熟练使用SQL定义子语言、操纵子语言命令语句l 掌握关系模型上的完整性约束机制l 掌握一定的数据库管理技术l 能完成简单的数据库应用开发二、实验内容及学时安排(总学时:8)(一)数据定义子语言实验(2学时)实验1:利用SQL语句创建Employee数据库 CREATE DATABASE Employee实验2:利用SQL语句在Employee数据库中创建人员表person、月薪表salary及部门表dept。要求:按表1、表2、表3中的字段说明创建表1 p
2、erson表结构字段名数据类型字段长度允许空否字段说明及备注备注P_noChar6Not Null工号,主键P_nameVarchar10Not Null姓名SexChar2Not Null性别BirthdateDatetime8Null出生日期ProfVarchar10Null职称DeptnoChar4Not Null部门代码实验12以后作为外键(参照dept表)表2 salary表结构字段名数据类型字段长度允许空否字段说明及备注P_noChar6Not Null工号,主键 实验12以后作为外键(参照person表)BaseDec5Null基本工资BonusDec5Null奖金,要求50Fa
3、ctDec5Null实发工资=基本工资+奖金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,Sex CHAR(2) N
4、OT 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中的数据P_noP_nameSexBirthD
5、ateProfDeptno000001王云男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-7, 中级, 0001);INSERT I
6、NTO personVALUES(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 INTO person (P_no, P_name, S
7、ex, 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, 高级, 0003); 表5 表salary中的数据P_
8、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);INSERT INTO salary (P_no, Base, B
9、onus, 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 salary (P_no, Base, Bonus, Mon)VA
10、LUES(000007, 2400, 210, 1);表6 表dept中的数据DeptnoDname0001人事部0002财务部0003市场部答案:INSERT INTO dept (Deptno, Dname)VALUES(001, 人事部);INSERT INTO deptVALUES(002, 财务部);INSERT INTO deptVALUES(003, 市场部);实验4:(1)利用SQL语句修改表中的数据。要求:将salary表中工号为000006的员工工资增加为1800元,奖金增加为160元。UPDATE salary SET Base = 1800, Bonus = 160 W
11、HERE 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 =高职WHERE P_name = 王云(2)向视图中插入数据 要求:向视图Perso
12、nView中插入一行数据(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 person WHERE Prof = 中级;(3) 查询person表中具有高级职
13、称的男员工信息。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之间的员工的月收入按实发工资升序排序。SELECT * FROM Salary WHERE P_
14、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 WHERE Deptno = 0001实验10:表的内连接查询:要求:利用
15、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 Person, Salary WHERE Fact (SELECT Fac
16、t 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 CHAR(4) NOT NULL,FOREIGN KEY (Deptno) REF
17、ERENCES 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,FOREIGN KEY (P_no) REFERENCES person(P_no);A
18、LTER 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 Deptno = 0003;-影响:UPDATE 语句与 REFERENC
19、E 约束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。-语句已终止。实验14:测试对从表进行插入、更新及删除操作时的影响。(请写明原因)要求
20、:(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的员工数据,测试是否违背参照完整性。DELETE FROM person WHERE P_no = 000005; -不
21、影响(四) 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 Server登录的设置 要求:授权000005登录账号以000005用户身份访问数据
22、库Employee(4) 特殊的数据库用户账号guest的使用要求:将guest用户账号添加到数据库Employee中(5)删除数据库用户账号要求:从数据库Employee中删除000005用户三、实验报告撰写要求:1、 制作实验报告封面(见附录)。2、 内容顺序包括:一、 实验目的与要求:二、 实验内容:(1) 实验1至实验14:要标明是第几个实验、实验内容及实验要求,然后把程序代码及运行结果写出,应写成:实验1:(内容、要求)代码如下:create table AAA ()运行结果为:(运行结果).(2) 设计的“Employee数据库系统”要求把设计思路及相应的界面打印出来,并作说明。三、 总结与体会附录:数据库原理上机实验报告封面数据库原理上机实验报告学 号: 姓 名: 班 级: 指导教师:昆明理工大学信息工程与自动化学院2006年11月33