数据库上机实验

1、数据库原理上机实验报告学 号： 201410401226姓 名： 易磊班 级： 自动化142指导教师： 张光辉昆明理工大学信息工程与自动化学院2006年12月一、实验目的与要求：l 熟练使用SQL定义子语言、操纵子语言命令语句l 掌握关系模型上的完整性约束机制l 掌握一定的数据库管理技术l 能完成简单的数据库应用开发二、实验内容及学时安排（总学时：8）（一）数据定义子语言实验（2学时）实验1：利用SQL语句创建Employee数据库CREATE DATABASE Employee;实验2：利用SQL语句在Employee数据库中创建人员表person、月薪表salary及部门表dept。要求：

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

3、onusDec5Null奖金，要求>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(1

4、0) NOT NULL,Sex 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,Month int,);（二）数据操纵子语言实验（4学时）实验3：利用SQL语句向表person、salary和dept中插入数据。要求：按表4、表5、表6中的数据插入。表

5、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/*CREATE DATABASE Employee;*/*CREATE TABLE person(P_no CHAR(6) NOT NULL PRIMARY KEY,

6、P_name VARCHAR(10) NOT NULL,Sex 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,Month int,);CREATE TABLE dept(Deptno CHAR(4) NOT NULL PRIMARY KEY,Dnam

7、e VARCHAR(10) NOT NULL);*/INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('000001','磊少', '男', '1973-4-7', '中级', '0001');INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('000002','谢文东', '男&#

8、39;, '1975-2-14', '中级', '0001');INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('000003','李勇', '男', '1970-8-25', '高级', '0002');INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('0000

9、04','廖小灵', '女', '1979-8-6', '初级', '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

10、, Prof, Deptno)VALUES('000006','罗向冬', '男', '1979-5-11', '初级', '0003');INSERT INTO person (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES('000007','肖家寨', '男', '1963-7-14', '高级', '0003');答案：INSERT INTO p

11、erson (P_no, P_name, Sex, BirthDate, Prof, Deptno)VALUES(000001, 磊少, 男, 1973-4-7, 中级, 0001);答案：INSERT INTO personVALUES(000001, 磊少, 男, 1973-4-7, 中级, 0001);表5 表salary中的数据P_noBaseBonusFactS_month00000121003001000002180030010000032800280100000425002501000005230027510000061750130100000724002101答案：INSERT

12、 INTO salary (P_no, Base, Bonus, Fact, Month)VALUES(000001, 2100, 300, NULL, 1);答案：INSERT INTO salaryVALUES(000001, 2100, 300, NULL, 1);答案3：INSERT INTO salary (P_no, Base, Bonus, Month)VALUES(000001, 2100, 300, 1);表6 表dept中的数据DeptnoDname0001人事部0002财务部0003市场部答案：INSERT INTO dept (Deptno, Dname)VALUES(

13、001, 人事部);答案：INSERT INTO deptVALUES(001, 人事部);实验4：（1）利用SQL语句修改表中的数据。要求：将salary表中工号为000006的员工工资增加为1800元，奖金增加为160元。UPDATE 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

14、* FROM person;实验5：（1）更新视图要求：将员工视图PersonView中姓名为王云的员工职称改为高职CREATE VIEW PersonViewASSELECT *FROM personUPDATE PersonViewSET prof =高职WHERE P_name = 磊少;（2）向视图中插入数据 要求：向视图PersonView中插入一行数据（000008，刘美萍，中级，00002）INSERTINTO PersonView(P_no, P_name, prof, Deptno)VALUES(000008, 女, 中级, 00002);（3）删除视图 要求：将视图Pers

15、onView删除DROP VIEW PersonView;实验6：条件查询 要求：（1） 查询person表中所有不重复的职称。SELECT DISTINCT prof FROM Person;（2） 查询person表中职称为中级的所有员工数据。SELECT * FROM person WHERE Prof = 中级;（3） 查询person表中具有高级职称的男员工信息。SELECT * FROM person WHERE Prof = 高级 AND Sex = 男;（4） 查询person表中姓名为王云、谢志文、罗向东的员工数据。SELECT * FROM person WHERE P_n

16、ame= 磊少 OR P_name= 谢文东 OR P_name= 罗向冬;实验7：使用ORDER BY排序 要求：利用SQL语句将工号在000003和000006之间的员工的月收入按实发工资升序排序。SELECT * FROM Salary WHERE P_no BETWEEN 000003 AND 000006 ORDER BY Fact ASC;实验8：利用SQL语句查询各部门的实发工资总数。SELECT detp.Dname, sum(Fact) AS “部门实发工资总数” FROM dept, salary, personWHERE person.P_no = salary.P_no

17、 AND person.Deptno = dept.DetpnoGROUP BY dept.Dname;实验9：利用SQL语句查询人事部所有员工信息。SELECT * FROM Person WHERE Deptno = 0001;实验10：表的内连接查询：要求：利用SQL语句查询person表中职称为中级的员工信息。SELECT * FROM Person WHERE prof = 中级;实验11：表的外连接查询：要求：利用SQL语句查询每个员工1 月份的工资和奖金SELECT Salary.Base, Salary.Bonus FROM Person JOIN Salary ON Pers

18、on.P_no = Salary.P_no;实验12：子查询：要求：利用SQL语句查询比工号为000005的员工实发工资高的所有员工信息。SELECT * FROM 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 V

19、ARCHAR(10) NOT NULL,Sex CHAR(2) NOT NULL,Birthday Datetime,Prof VARCHAR(10),Deptno CHAR(4) NOT NULL,FOREIGN KEY (Deptno) REFERENCES dept(Deptno);ALTER TABLE person FOREIGN KEY (Deptno) REFERENCES dept(Deptno) ON DELETE NO ACTION ON UPDATE NO ACTION（2） 将salary表中的P_no设为外键，并使其参照person表中的列P_no。CREATE TA

20、BLE 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);实验13：测试对主表进行插入、更新及删除操作时的影响。（请写明原因） 要求：（1） 向表dept中插入一行数据（0004，研发部），测试是否影响从表。INSERT INTO dept(Deptno, Dname) VALUES(0004, 研发部);不影响（2） 将表dept中的部门号0003改为0006，测试是否影响从表。UPDATE dept SET Deptno =0006 WHERE Deptno = 0003;影响（3） 删除表dept中部门号为0001的员工的数据，测试是否影响从表。DELETE FROM dept WHERE Deptno = 0001;影响实验14：测试对从表进行插入、更新及删除操作时的影响。（请写明原因）要求：（1） 向表