數(shù)據(jù)庫原理上機實驗 代碼及截圖

1、數(shù)據(jù)庫原理上機實驗報告2017年11月一、實驗目的與要求：l 熟練使用SQL定義子語言、操縱子語言命令語句l 掌握關(guān)系模型上的完整性約束機制l 掌握一定的數(shù)據(jù)庫管理技術(shù)l 能完成簡單的數(shù)據(jù)庫應用開發(fā)二、實驗內(nèi)容1、實驗一到實驗十七（一）數(shù)據(jù)定義子語言實驗（2學時）實驗1：利用SQL語句創(chuàng)建Employee數(shù)據(jù)庫代碼如下：create database Employee;運行結(jié)果：實驗2：利用SQL語句在Employee數(shù)據(jù)庫中創(chuàng)建人員表person、月薪表salary及部門表dept, 暫不定義外鍵約束。要求：按表1、表達、表3中的字段說明創(chuàng)建表1 person表結(jié)構(gòu)字段名數(shù)據(jù)類型字段長度允許

2、空否字段說明P_noChar6Not Null工號，主鍵P_nameVarchar10Not Null姓名SexChar2Not Null性別BirthdateDatetime8Null出生日期ProfVarchar10Null職稱DeptnoChar4Not Null部門代碼，外鍵（參照dept表）表2 salary表結(jié)構(gòu)字段名數(shù)據(jù)類型字段長度允許空否字段說明P_noChar6Not Null工號，主鍵，外鍵（參照person表）BaseDec5Null基本工資BonusDec5Null獎金，要求>50FactDec5Null實發(fā)工資=基本工資+獎金MonthInt2Not Null月

3、份表3 dept表結(jié)構(gòu)字段名數(shù)據(jù)類型字段長度允許空否字段說明DeptnoChar4Not Null部門代碼，主鍵，DnameVarchar10Not Null部門名稱代碼如下：create table person( P_no char(6) not null primary key, P_name varchar(10) not null, Sex char(2) not null, Birthdate datetime null, Prof varchar(10) null, Deptno char(4) not null);create table salary( P_no char(6

4、) not null primary key, Base dec(5) null, Bonus dec(5) null, Fact dec(5) null, Month int not null);create table dept( Deptno char(4) not null primary key, Dname varchar(10) not null); 運行結(jié)果：（二）數(shù)據(jù)操縱子語言實驗（4學時）實驗3：利用SQL語句向表person、salary和dept中插入數(shù)據(jù)。要求：按表4、表5、表6中的數(shù)據(jù)插入。表4 表person中的數(shù)據(jù)P_noP_nameSexBirthDatePr

5、ofDeptno000001王云男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高級0003000007肖家慶男1963-7-14高級0003代碼如下：insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000001','王

6、云','男','1973-4-7','中級','0001')insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000002','謝志文','男','1975-2-14','中級','0001')insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('0000

7、03','李浩然','男','1970-8-25','高級','0002')insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000004','廖小玲','女','1979-8-6','初級','0002')insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) va

8、lues ('000005','梁玉瓊','女','1970-8-25','中級','0003')insert into person (P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000006','羅向東','男','1979-5-11','初級','0003')insert into person (P_no,P_name,Sex,Birthdate

9、,Prof,Deptno) values ('000007','尚家慶','男','1963-7-14','高級','0003') 運行結(jié)果：表5 表salary中的數(shù)據(jù)P_noBaseBonusFactS_month00000121003001000002180030010000032800280100000425002501000005230027510000061750130100000724002101代碼如下：insert into salary (P_no,Base,Bonus,Fact

10、,Month) values ('000001',2100,300,2100+300,1)insert into salary (P_no,Base,Bonus,Fact,Month) values ('000002',1800,300,1800+300,1)insert into salary (P_no,Base,Bonus,Fact,Month) values ('000003',2800,280,2800+280,1)insert into salary (P_no,Base,Bonus,Fact,Month) values ('

11、000004',2500,250,2500+500,1)insert into salary (P_no,Base,Bonus,Fact,Month) values ('000005',2300,275,2300+275,1)insert into salary (P_no,Base,Bonus,Fact,Month) values ('000006',1750,130,1750+130,1)insert into salary (P_no,Base,Bonus,Fact,Month) values ('000007',2400,210,

12、2400+210,1)運行結(jié)果：表6 表dept中的數(shù)據(jù)DeptnoDname0001人事部0002財務部0003市場部代碼如下：insert into dept (Deptno,Dname) values ('0001','人事部')insert into dept (Deptno,Dname) values ('0002','財務部')insert into dept (Deptno,Dname) values ('0003','市場部')運行結(jié)果：實驗4：（1）利用SQL語句修改表中的數(shù)據(jù)。要

13、求：將salary表中工號為000006的員工工資增加為1800元，獎金增加為160元。代碼如下：update salaryset Base=1800,Bonus=160,Fact=1800+160where P_no='000006'運行結(jié)果：（2）利用SQL語句刪除表中的數(shù)據(jù)。要求：刪除 salary表中工號為000007的員工數(shù)據(jù)。代碼如下：delete from salary where P_no='000007'（3）利用SQL語句查詢person表中的所有數(shù)據(jù)。代碼如下：select * from person運行結(jié)果：實驗5：（1）創(chuàng)建視

14、圖要求：創(chuàng)建員工視圖PersonView，包含員工的所有信息 ，并調(diào)用視圖代碼如下：create view PersonView as select person.P_no,P_name,Sex,Birthdate,Prof,person.Deptno,Base,Bonus,Fact,Month,Dname from person,salary,dept where person.Deptno=dept.Deptno and salary.P_no=person.P_noselect * from PersonView運行結(jié)果：（2）刪除視圖 要求：將視圖PersonView刪除代碼如下：dr

15、op view PersonView運行結(jié)果：實驗6：條件查詢 要求：（1） 查詢person表中所有不重復的職稱。（2） 查詢person表中職稱為中級的所有員工數(shù)據(jù)。（3） 查詢person表中具有高級職稱的男員工信息。（4）查詢person表中姓名為王云、謝志文、羅向東的員工數(shù)據(jù)。代碼及運行結(jié)果如下：（1）select distinct Prof from person（2）select * from person where Prof='中級'（3）select * from person where Prof='高級' and Sex='男&#

16、39;（4）select * from person where P_name in ('王云','謝志文','羅向東')實驗7：使用ORDER BY排序 要求：利用SQL語句將工號在000003和000006之間的員工的月收入按實發(fā)工資升序排序。代碼如下：select * from salary where P_no between '000003' and '000006' order by Fact asc運行結(jié)果：實驗8：利用SQL語句查詢各部門的實發(fā)工資總數(shù)。代碼如下：select dept.Dname,

17、sum(Fact) as "部門實發(fā)工資總數(shù)" from person,salary,dept where person.Deptno=dept.Deptno and salary.P_no=person.P_no group by dept.Dname運行結(jié)果：實驗9：利用SQL語句查詢?nèi)耸虏克袉T工信息。代碼如下：select * from person,salary,dept where person.Deptno=dept.Deptno and salary.P_no=person.P_no and dept.Dname=人事部運行結(jié)果：實驗10：表的內(nèi)連接查詢：要

18、求：利用SQL語句查詢person表中職稱為中級的員工信息。代碼如下：select * from person where prof='中級'運行結(jié)果：實驗11：表的外連接查詢：要求：利用SQL語句查詢每個員工1 月份的工資和獎金代碼如下：select person.P_no,person.P_name,salary.Base,salary.Bonusfrom person left outer join salary on (person.P_no=salary.P_no)運行結(jié)果：實驗12：子查詢：要求：利用SQL語句查詢比工號為000005的員工實發(fā)工資高的所有員工信息。

19、代碼如下：select * from person,salarywhere Fact>(select Fact from salary where P_no='000005') and person.P_no=salary.P_no（三） 數(shù)據(jù)完整性實驗（1學時）實驗13：定義外鍵約束 要求：（1） 創(chuàng)建表時將person表的deptno列定義為外鍵，并參考dept表的列deptno。代碼如下：alter table person add constraint personkey foreign key (Deptno) references dept(Deptno)（2

20、）將salary表中的P_no設(shè)為外鍵，并使其參照person表中的列P_no。代碼如下：alter table salaryadd constraint salarykey foreign key (P_no) references person(P_no)運行結(jié)果：實驗14：測試對主表進行插入、更新及刪除操作時的影響。（請寫明原因） 要求：（1） 向表dept中插入一行數(shù)據(jù)（0004，研發(fā)部），測試是否影響從表。代碼如下：insert into dept(Deptno,Dname) values ('0004','研發(fā)部')運行結(jié)果：不影響從表，插入的數(shù)據(jù)與

21、外鍵約束的數(shù)據(jù)沒有聯(lián)系。（2） 將表dept中的部門號0003改為0006，測試是否影響從表。代碼及運行結(jié)果如下：update dept set Deptno='0006' where Deptno='0003'分析：影響從表，0006為原本不存在的數(shù)據(jù)，修改時受外鍵約束無法實現(xiàn)。（3） 刪除表dept中部門號為0001的員工的數(shù)據(jù)，測試是否影響從表。代碼及運行結(jié)果如下：delete from dept where Deptno='0001'分析：影響從表，刪除表dept中部門號為0001的員工的數(shù)據(jù)，修改時受外鍵約束無法實現(xiàn)。實驗15：測試對從

22、表進行插入、更新及刪除操作時的影響。（請寫明原因）要求：（1） 向表person中插入一行數(shù)據(jù)（000012，宋全禮，男、1980-7-17，初級，0005），測試是否違背參照完整性。代碼及運行結(jié)果如下：insert into person(P_no,P_name,Sex,Birthdate,Prof,Deptno) values ('000012','宋全禮','男','1980-7-17','初級','0005');分析：違背參照完整性，更新數(shù)據(jù)的操作受到約束無法實現(xiàn)。（2） 刪除表中工號為00

23、0005的員工數(shù)據(jù)，測試是否違背參照完整性。代碼及運行結(jié)果如下：delete from person where P_no='000005'分析：違背參照完整性，刪除數(shù)據(jù)的操作受到約束無法實現(xiàn)。（四） SQL Server安全設(shè)置實驗（1學時） 實驗 16：創(chuàng)建登錄賬號（1） 創(chuàng)建SQL Server登錄賬號 要求：基于SQL Server創(chuàng)建登錄賬號kmust、student、testdeletesp_addlogin loginame=kmust,passwd='123456',defdb=N'Employee',deflanguage=N'Simplified Chinese',sid=null,encryptopt=nullsp_addlogin loginame=student,passwd='123456',defdb=N'Employee',deflang