數(shù)據(jù)庫及表的基本操作實驗報告

1、-. z.數(shù)據(jù)庫與表的根本操作實驗報告工程名稱數(shù)據(jù)庫與表的根本操作實驗成績：批閱教師：2015年5 月 11 日實驗4存儲過程實驗學(xué)時： 4 每組人數(shù)： 1 實驗類型： 2 1：根底性 2：綜合性 3：設(shè)計性 4：研究性實驗要求： 1 1：必修 2：選修 3：其它實驗類別： 3 1：根底2：專業(yè)根底 3：專業(yè) 4：其它一、實驗?zāi)康睦斫獯鎯^程的概念、建立和調(diào)用方法。進一步熟悉SQL語句對數(shù)據(jù)庫進展完整性控制的方法。二、實驗內(nèi)容1、建立一個新的銷售數(shù)據(jù)庫，包含五*表，每*表至少需要10條記錄。1/*員工人事表employee */emp_nochar(5)Not nullprimary key員

2、工編號emp_namechar(10)員工*se*char(1)性別deptchar(4)所屬部門titlechar(6)職稱date_hireddatetime到職日birthdaydatetime生日salaryint薪水a(chǎn)ddrchar(50)null住址2/*客戶表customer */cust_idchar(5)Not nullprimary key客戶號cust_namechar(20)客戶名稱addrchar(40)客戶住址tel_nochar(10)客戶zipchar(6)郵政編碼3/*銷售主表sales */order_nointNot nullprimary key訂單編號

3、cust_idchar(5)客戶號sale_idchar(5)業(yè)務(wù)員編號tot_amtnumeric(9,2)訂單金額order_datedatetime訂貨日期ship_datedatetime出貨日期invoice_nochar(10)發(fā)票4/*銷貨明細表sale_item */order_nointNot null,primary key訂單編號prod_idchar(5)Not null,primary key產(chǎn)品編號qtyint銷售數(shù)量unit_pricenumeric(7,2)單價order_datedatetimenull訂單日期5/*產(chǎn)品名稱表product */pro_idc

4、har(5)Not nullprimary key產(chǎn)品編號prod_namechar(20)Not null產(chǎn)品名稱2、建立表的同時創(chuàng)立表的約束。為每*表建立主鍵約束。通過拖放操作參加外鍵。在表employee參加CHECK約束：輸入的員工編號必須以E開頭的5位數(shù)編號，性別只能為M/F。為銷售主表sales中的發(fā)票編號字段建立UNIQUE約束。3、利用存儲過程，給employee表添加一條業(yè)務(wù)部門員工的信息。4、利用存儲過程從employee、sales、customer表的連接中返回所有業(yè)務(wù)員的*、客戶*、銷售金額。5、利用存儲過程查找*德華的員工編號、訂單編號、銷售金額。6、利用存儲過程查

5、找姓李并且職稱為職員的員工的員工編號、訂單編號、銷售金額。7、利用存儲過程計算出訂單編號為10003的訂單的銷售金額。三、實驗要求：1熟悉SQL SERVER 工作環(huán)境；2建立銷售數(shù)據(jù)庫3復(fù)習(xí)有關(guān)約束與存儲過程的SQL語言命令。4備份數(shù)據(jù)庫，作為實驗5 的操作數(shù)據(jù)庫。四、實驗步驟1創(chuàng)立銷售數(shù)據(jù)庫，并建表、修改，要求將自己的信息包含其中；2、利用存儲過程，給employee表添加一條業(yè)務(wù)部門員工的信息。3、利用存儲過程從employee、sales、customer表的連接中返回所有業(yè)務(wù)員的*、客戶*、銷售金額。4、利用存儲過程查找*德華的員工編號、訂單編號、銷售金額。5、利用存儲過程查找姓李并

6、且職稱為職員的員工的員工編號、訂單編號、銷售金。五、實驗結(jié)果1、建立一個新的銷售數(shù)據(jù)庫，包含五*表，每*表至少需要10條記錄。1/*員工人事表employee */emp_nochar(5)Not nullprimary key員工編號emp_namechar(10)員工*se*char(1)性別deptchar(4)所屬部門titlechar(6)職稱date_hireddatetime到職日birthdaydatetime生日salaryint薪水a(chǎn)ddrchar(50)null住址2/*客戶表customer */cust_idchar(5)Not nullprimary key客戶號c

7、ust_namechar(20)客戶名稱addrchar(40)客戶住址tel_nochar(10)客戶zipchar(6)郵政編碼3/*銷售主表sales */order_nointNot nullprimary key訂單編號cust_idchar(5)客戶號sale_idchar(5)業(yè)務(wù)員編號tot_amtnumeric(9,2)訂單金額order_datedatetime訂貨日期ship_datedatetime出貨日期invoice_nochar(10)發(fā)票4/*銷貨明細表sale_item */order_nointNot null,primary key訂單編號prod_idc

8、har(5)Not null,primary key產(chǎn)品編號qtyint銷售數(shù)量unit_pricenumeric(7,2)單價order_datedatetimenull訂單日期5/*產(chǎn)品名稱表product */pro_idchar(5)Not nullprimary key產(chǎn)品編號prod_namechar(20)Not null產(chǎn)品名稱解：首先寫出每一個表的創(chuàng)立語句CREATE TABLE employee(emp_no char(5)not null, emp_name char(10),se* char(1), dept char(4),title char(6), date_hi

9、red datetime, birthday datetime,salary int, addr CHAR (50), primary key (emp_no)CREATE TABLEcustomer(cust_id char(5)not null,cust_name char(20), addr char(40), tel_nochar(10),zipchar(6), primary key (cust_id)CREATE TABLE sales(order_no int not null,cust_id char(5),sale_id char(5), tot_amt numeric(9,

10、2),order_datedatetime,ship_datedatetime,invoice_nochar(10), primary key (order_no)CREATE TABLE sale_item (order_no int not null, prod_id char(5) not null,qty int,unit_price numeric(7,2),order_datedatetime not null, primary key (order_no,prod_id)CREATE TABLE product(pro_idchar(5) NOT NULL,prod_namech

11、ar(20) NOT NULL, primary key (pro_id)以上建表結(jié)果：為每一個表輸入數(shù)據(jù)：2、建立表的同時創(chuàng)立表的約束。為每*表建立主鍵約束。已建立主鍵約束通過拖放操作參加外鍵。步驟如下：首先點擊數(shù)據(jù)庫，可以看見，數(shù)據(jù)庫下方有數(shù)據(jù)庫關(guān)系表：右鍵其，創(chuàng)立一個數(shù)據(jù)庫關(guān)系圖接著可以看見選擇添加以上五個數(shù)據(jù)庫，拖動鍵后便可以設(shè)立建立在表employee參加CHECK約束：輸入的員工編號必須以E開頭的5位數(shù)編號，性別只能為M/F。步驟：第一步右鍵第二步：又按著鼠標右鍵：第三步：點擊添加接著再表達式里面寫著約束條件為銷售主表sales中的發(fā)票編號字段建立UNIQUE約束。alter ta

12、ble sales add constraint order_nounique unique (order_no);3、利用存儲過程，給employee表添加一條業(yè)務(wù)部門員工的信息。create procedure proAddEmployee(emp_no char(5),emp_name char(10),se* char(1),dept char(10),title char(6),date_hired datetime,birthday datetime,salary int,addr char(50)asinsert into employee values(emp_no,emp_n

13、ame,se*,dept,title,date_hired,birthday,salary,addr)goe*ec proAddEmployeeE0022,羅剛,M,業(yè)務(wù),經(jīng)理,2009-07-08,1988-02-03,13000,都勻市執(zhí)行后：刷新表格后，查看表格，可以發(fā)現(xiàn)，表中寫入了信息結(jié)果為：4、利用存儲過程從employee、sales、customer表的連接中返回所有業(yè)務(wù)員的*、客戶*、銷售金額。create procedure findasselect employee.emp_name,customer.cust_name,sales.tot_amtfrom employee

14、,customer,saleswhere sales.sale_id = employee.emp_no and sales.cust_id = customer.cust_idgoe*ec find執(zhí)行后結(jié)果為：刷新后，旁邊的存儲過程的顯示為：5、利用存儲過程查找*德華的員工編號、訂單編號、銷售金額。create procedure findasselect employee.emp_name,customer.cust_name,sales.tot_amtfrom employee,customer,saleswhere sales.sale_id = employee.emp_no an

15、d sales.cust_id = customer.cust_idgoe*ec find執(zhí)行結(jié)果：刷新后，可看見：此結(jié)果采用的數(shù)據(jù)為：Employee表：Sales表：6、利用存儲過程查找姓李并且職稱為職員的員工的員工編號、訂單編號、銷售金額。create procedure findLiemp_name varchar(10)asselect employee.emp_no,sales.order_no,sales.tot_amtfrom employee,saleswhere employee.emp_no = sales.sale_id and employee.title = 職員

16、and (employee.emp_name like emp_name)goe*ec findLi李%結(jié)果：刷新后，在旁邊可看見：結(jié)果的數(shù)據(jù)中所查詢的表的內(nèi)容為：Employee表：Sales表：7、利用存儲過程計算出訂單編號為10003的訂單的銷售金額。CREATE PROCEDURE PRO_ORDERorder_no varchar(6)asselect sales.tot_amtfrom saleswhere sales.order_no = order_nogoe*ec PRO_ORDER10003實驗結(jié)果：刷新后發(fā)現(xiàn)左邊更新：附錄：實驗例如1、模糊查詢create procedu

17、re sp_empname E_name varchar(10) asselect a.emp_name,a.dept,b.tot_amtfrom employee a inner join sales bon a.emp_no=b.sale_id where a.emp_name like E_namegoe*ec sp_empname 陳%利用存儲過程計算出E0014業(yè)務(wù)員的銷售總金額。create procedure sp_saletot E_no char(5),p_tot int output asselect p_tot=sum(tot_amt)from saleswhere sa

18、le_id=E_no godeclare tot_amt inte*ec sp_saletot E0014, tot_amt outputselect tot_amt六、實驗結(jié)論存儲過程Stored Procedure是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL 語句集，存儲在數(shù)據(jù)庫中經(jīng)過第一次編譯后再次調(diào)用不需要再次編譯，用戶通過指定存儲過程的名字并給出參數(shù)如果該存儲過程帶有參數(shù)來執(zhí)行它。存儲過程是數(shù)據(jù)庫中的一個重要對象，任何一個設(shè)計良好的數(shù)據(jù)庫應(yīng)用程序都應(yīng)該用到存儲過程。sql中的存儲過程：CREATE PROCEDURE 擁有者.存儲過程名;程序編號(參數(shù)#1,參數(shù)#1024)WI

19、THREPILE | ENCRYPTION | REPILE, ENCRYPTIONFOR REPLICATIONAS 程序行其中存儲過程名不能超過128個字。每個存儲過程中最多設(shè)定1024個參數(shù)(SQL Server 7.0以上版本),參數(shù)的使用方法如下：參數(shù)名數(shù)據(jù)類型VARYING =內(nèi)定值 OUTPUT每個參數(shù)名前要有一個符號,每一個存儲過程的參數(shù)僅為該程序內(nèi)部使用,參數(shù)的類型除了IMAGE外，其他SQL Server所支持的數(shù)據(jù)類型都可使用。內(nèi)定值相當于我們在建立數(shù)據(jù)庫時設(shè)定一個字段的默認值，這里是為這個參數(shù)設(shè)定默認值。OUTPUT是用來指定該參數(shù)是既有輸入又有輸出值的，也就是在調(diào)用了

20、這個存儲過程時，如果所指定的參數(shù)值是我們需要輸入的參數(shù)，同時也需要在結(jié)果中輸出的，則該項必須為OUTPUT，而如果只是做輸出參數(shù)用，可以用CURSOR，同時在使用該參數(shù)時，必須指定VARYING和OUTPUT這兩個語句。分類：1系統(tǒng)存儲過程以sp_開頭,用來進展系統(tǒng)的各項設(shè)定.取得信息.相關(guān)管理工作。2本地存儲過程用戶創(chuàng)立的存儲過程是由用戶創(chuàng)立并完成*一特定功能的存儲過程，事實上一般所說的存儲過程就是指本地存儲過程。3臨時存儲過程分為兩種存儲過程：一是本地臨時存儲過程，以井字號(#)作為其名稱的第一個字符，則該存儲過程將成為一個存放在tempdb數(shù)據(jù)庫中的本地臨時存儲過程，且只有創(chuàng)立它的用戶才

21、能執(zhí)行它;二是全局臨時存儲過程，以兩個井字號(#)號開場，則該存儲過程將成為一個存儲在tempdb數(shù)據(jù)庫中的全局臨時存儲過程，全局臨時存儲過程一旦創(chuàng)立，以后連接到效勞器的任意用戶都可以執(zhí)行它，而且不需要特定的權(quán)限。4遠程存儲過程在SQL Server2005中，遠程存儲過程(Remote Stored Procedures)是位于遠程效勞器上的存儲過程，通?？梢允褂梅植际讲樵兒虴*ECUTE命令執(zhí)行一個遠程存儲過程。5擴展存儲過程擴展存儲過程(E*tended Stored Procedures)是用戶可以使用外部程序語言編寫的存儲過程，而且擴展存儲過程的名稱通常以*p_開頭。格式：創(chuàng)立存儲過

22、程create procedure sp_name參數(shù)名 類型,參數(shù)名 類型asbegin.end以上格式還可以簡寫成：create proc sp_name參數(shù)名 類型,參數(shù)名 類型asbegin.end/*注：sp_name為需要創(chuàng)立的存儲過程的名字，該名字不可以以阿拉伯數(shù)字開頭*/調(diào)用存儲過程1.根本語法：e*ec sp_name 參數(shù)名刪除存儲過程1.根本語法：drop procedure sp_name2.考前須知(1)不能在一個存儲過程中刪除另一個存儲過程，只能調(diào)用另一個存儲過程其他常用命令1.show procedure status顯示數(shù)據(jù)庫中所有存儲的存儲過程根本信息，包括所

23、屬數(shù)據(jù)庫，存儲過程名稱，創(chuàng)立時間等2.show create procedure sp_name顯示*一個mysql存儲過程的詳細信息3、e*ec sp_helpte*t sp_name顯示你這個sp_name這個對象創(chuàng)立文本七、實驗小結(jié)在本次實驗中，我學(xué)會了存儲過程的參數(shù)的傳遞，以及參數(shù)的輸入和參數(shù)的創(chuàng)立，以及使用存儲過程去實現(xiàn)功能的查詢，和打印輸出*些東西。實驗5觸發(fā)器與游標實驗學(xué)時： 4 每組人數(shù)： 1 實驗類型： 2 1：根底性 2：綜合性 3：設(shè)計性 4：研究性實驗要求： 1 1：必修 2：選修 3：其它實驗類別： 3 1：根底 2：專業(yè)根底 3：專業(yè) 4：其它一、實驗?zāi)康倪M一步熟悉

24、SQL語句對數(shù)據(jù)庫進展完整性控制的方法；理解觸發(fā)器的概念、定義方法和觸發(fā)條件。理解游標的定義、翻開、使用、關(guān)閉與釋放的方法。二、實驗內(nèi)容針對實驗4所建銷售數(shù)據(jù)庫：設(shè)置一個觸發(fā)器，該觸發(fā)器僅允許dbo用戶可以刪除employee表內(nèi)數(shù)據(jù)，否則出錯。針對employee表寫一個DELETE觸發(fā)器。針對employee表寫一個UPDATE觸發(fā)器。統(tǒng)計employee表中員工的平均薪水，輸出低于平均薪水的員工的人數(shù)以及這些員工的*與薪水使用游標。三、實驗要求：1熟悉SQL SERVER 工作環(huán)境；2恢復(fù)實驗4所建銷售數(shù)據(jù)庫3復(fù)習(xí)有關(guān)SQL語句對數(shù)據(jù)庫進展完整性控制的方法；復(fù)習(xí)觸發(fā)器的概念、定義方法和觸

25、發(fā)條件。復(fù)習(xí)游標的定義、翻開、使用、關(guān)閉與釋放的方法約束與存儲過程的SQL語言命令。四、實驗步驟1.設(shè)置一個觸發(fā)器，該觸發(fā)器僅允許dbo用戶可以刪除employee表內(nèi)數(shù)據(jù)，否則出錯。2.針對employee表寫一個DELETE觸發(fā)器。3.針對employee表寫一個UPDATE觸發(fā)器。4.統(tǒng)計employee表中員工的平均薪水，輸出低于平均薪水的員工的人數(shù)以及這些員工的*與薪水使用游標。五、實驗結(jié)果設(shè)置一個觸發(fā)器，該觸發(fā)器僅允許dbo用戶可以刪除employee表內(nèi)數(shù)據(jù)，否則出錯。創(chuàng)立命令為：create trigger EmploteeDelete on employeefor delet

26、e asif e*ists (select * from deleted)-要刪除的是有數(shù)據(jù)的beginif user !=dbo/*如果不是dbo用戶*/rollback transactionend結(jié)果：針對employee表寫一個DELETE觸發(fā)器。創(chuàng)立命令：create trigger delete_disployon employeefor delete/*默認after*/as /*操作*/SELECT * from employeeBEGINPRINT 已觸發(fā)觸發(fā)器了END結(jié)果：測試：發(fā)現(xiàn)輸出來的表已無E00001，說明刪除成功再看輸出來的信息：說明觸發(fā)器成功執(zhí)行針對employ

27、ee表寫一個UPDATE觸發(fā)器。命令:create trigger UPDATE_DISPLOYON EMPLOYEEFOR UPDATEAS /*操作*/select * from employeeBEGINPRINT 觸發(fā)了更新操作的觸發(fā)器END結(jié)果：更新前的數(shù)據(jù)為：更新后：消息處顯示為：統(tǒng)計employee表中員工的平均薪水，輸出低于平均薪水的員工的人數(shù)以及這些員工的*與薪水使用游標。原先備份過數(shù)據(jù)庫，所以我先把數(shù)據(jù)后復(fù)原復(fù)原后，employee表的數(shù)據(jù)為:命令為：DECLARE CUR_EMPLOEE_NAME_SALARY SCROLL CURSOR FOR -定義游標名為 CUR_

28、EMPLOEE_NAME_SALARY SELECT EMPLOYEE.emp_no,EMPLOYEE.salaryFROM EMPLOYEE WHERE EMPLOYEE.salary (SELECT AVG(EMPLOYEE.salary) FROM EMPLOYEE)OPEN CUR_EMPLOEE_NAME_SALARY -翻開游標declare emp_no CHAR(5) , salary INT -定義選取出來的局部變量DECLARE COUNT INT,AVG_SALARY INTset COUNT = 0SET AVG_SALARY = (SELECT AVG(EMPLOYE

29、E.salary) FROM EMPLOYEE)PRINT 平均工資為：+convert(char(6),AVG_SALARY)-打印平均工資fetch ne*t from CUR_EMPLOEE_NAME_SALARY into emp_no , salary -讀數(shù)據(jù)到局部變量WHILE(FETCH_STATUS = 0)BEGINSELECT emp_no , salaryfetch ne*t from CUR_EMPLOEE_NAME_SALARY into emp_no , salaryset COUNT = COUNT+1END-select COUNT-print str(COU

30、NT)print 總共+convert(char(2),COUNT)+人CLOSE CUR_EMPLOEE_NAME_SALARY -關(guān)閉游標DEALLOCATE CUR_EMPLOEE_NAME_SALARY -釋放游標結(jié)果：六、實驗結(jié)論觸發(fā)器trigger是SQL server 提供應(yīng)程序員和數(shù)據(jù)分析員來保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動，而是由事件來觸發(fā)，比方當對一個表進展操作 insert，delete， update時就會激活它執(zhí)行。觸發(fā)器與存儲過程的唯一區(qū)別是觸發(fā)器不能執(zhí)行E*ECUTE語句調(diào)用，而是在用戶執(zhí)行Transact-SQL語句時自動觸發(fā)執(zhí)行語法：DELIMITER |CREATE TRIGGER . ON dbo /dbo代表該表的所有者FOR EACH ROWBEGIN-do somethingEND |七、實驗小結(jié)學(xué)會了觸發(fā)器的使用，創(chuàng)立和對于更新時候的做的操作的設(shè)定和對于觸發(fā)器然后顯示出已觸犯的效果，進展了學(xué)習(xí)。學(xué)會了創(chuàng)立和聲明游標，學(xué)會使用游標去讀取一行行數(shù)據(jù)，學(xué)會了如何把varchar類型用函數(shù)轉(zhuǎn)換成char型，使得整型變量可以打印出來。受益匪淺。附錄：實驗例如