數(shù)據(jù)庫原理課程實(shí)驗(yàn)報告

1、實(shí)驗(yàn)一：表及約束的創(chuàng)實(shí)驗(yàn)?zāi)康呐c內(nèi)實(shí)驗(yàn)一：表及約束的創(chuàng)實(shí)驗(yàn)?zāi)康呐c內(nèi)實(shí)驗(yàn)代碼及結(jié)實(shí)驗(yàn)代實(shí)驗(yàn)結(jié)實(shí)驗(yàn)二：SQL 更新語實(shí)驗(yàn)?zāi)康呐c內(nèi)實(shí)驗(yàn)代碼及結(jié)實(shí)驗(yàn)代實(shí)驗(yàn)結(jié)實(shí)驗(yàn)三：SQL 查詢語實(shí)驗(yàn)?zāi)康呐c內(nèi)實(shí)驗(yàn)代碼及結(jié)實(shí)驗(yàn)代實(shí)驗(yàn)結(jié)實(shí)驗(yàn)四：視圖及索引的建立實(shí)驗(yàn)?zāi)康呐c內(nèi)實(shí)驗(yàn)代碼及結(jié)實(shí)驗(yàn)代實(shí)驗(yàn)結(jié)實(shí)驗(yàn)五過程的.實(shí)驗(yàn)?zāi)康呐c內(nèi)實(shí)驗(yàn)代碼及結(jié)實(shí)驗(yàn)代實(shí)驗(yàn)結(jié)實(shí)驗(yàn)六：觸發(fā)器的建立.實(shí)驗(yàn)?zāi)康呐c內(nèi)實(shí)驗(yàn)代碼及結(jié)實(shí)驗(yàn)代實(shí)驗(yàn)結(jié)1.1 PRIMARY LIKEA-ZA-Z0-90-NOT NULL, NOT NULL, NOTNULL,1.1 PRIMARY LIKEA-ZA-Z0-90-NOT NULL, NOT NULL, NOTNULL,FORE

2、IGNKEy20112819ONDELETEON UPDATE FOREIGNKEyONDELETEON UPDATE )CREATE TABLE PRIMARY FOREIGNKEyONDELETEON UPDATE Base+Bonus,1.2.2 實(shí)驗(yàn)1.2.2 實(shí)驗(yàn)2.1 實(shí)驗(yàn)?zāi)康呐c內(nèi)目的：update、delete、insert語句的練習(xí)2.1 實(shí)驗(yàn)?zāi)康呐c內(nèi)目的：update、delete、insert語句的練習(xí)。實(shí)驗(yàn)代碼及結(jié)實(shí)驗(yàn)代 UPDATE salary20112819 WHERENOT*FROM2.2.1.211-8實(shí)驗(yàn)代Delete 2.2.2 實(shí)驗(yàn)結(jié)20112819 WH

3、ERE 2.2.2.111-7實(shí)驗(yàn)結(jié)2.2.2.2 11-8 實(shí)驗(yàn)結(jié)3.1 實(shí)驗(yàn)?zāi)康呐c內(nèi)。實(shí)驗(yàn)代碼及結(jié)實(shí)驗(yàn)代3.2.1.1 11-13 實(shí)驗(yàn)代3.1 實(shí)驗(yàn)?zāi)康呐c內(nèi)。實(shí)驗(yàn)代碼及結(jié)實(shí)驗(yàn)代3.2.1.1 11-13 實(shí)驗(yàn)代（1）selectdistinctdeptnameselectselect*20112819wheres=nullandsex=女20112819wherep_name,羅向東（4）select*fromsalary20112819wherep_non000003and000008orderby（5）selectp_noas 工號,2*base+1.5*bonusas fromsa

4、lary20112819where3.2.1.2 11-14 實(shí)驗(yàn)代select deptname as 部門,avg(bonus)as 平均獎金 from salary20112819 a join group by deptname having avg(bonus)200 order by avg(bonus) b selectcount(*) 訂單總數(shù),sum(order_total) 訂單總額fromwhereorderdetail20112819.cust_no=customer20112819.cust_noand3.2.2 實(shí)驗(yàn)3.2.2.1 11-13 實(shí)驗(yàn)結(jié)3.2.2.2

5、11-14 實(shí)驗(yàn)結(jié)3.2.2.2 11-14 實(shí)驗(yàn)結(jié)實(shí)驗(yàn)?zāi)康?實(shí)驗(yàn)?zāi)康?from customer20112806where （2）createviewTrainingView sorder_date=getdate()-4.2.1.2 11-4 實(shí)驗(yàn)代create index name_sort on createindexbirth_name4.2.1.2 11-4 實(shí)驗(yàn)代create index name_sort on createindexbirth_namecreateuniqueindexu_name_sortupdateCustomerView set discount=0.8

6、5where cust_name= 實(shí)驗(yàn)結(jié)11-3 實(shí)驗(yàn)結(jié)4.2.2.2 11-4 4.2.2.2 11-4 實(shí)驗(yàn)結(jié)4.2.2.3 11-9 實(shí)驗(yàn)結(jié)為的4.2.2.3 11-9 實(shí)驗(yàn)結(jié)為的5.1 。create proc 5.1 。create proc declarefor select*where p_no=p_no select add=0fetch cur_addbonus_checks close cur_addbonus_checks set nocount on set add=add+20elsesetadd=add+order_total/100000*30 fetch cu

7、r_addbonus_checkso order_total 5.2.2 實(shí)驗(yàn)5.2.2 實(shí)驗(yàn)6.1 。（1）createafterdelete Ifrowcount=0return delete salary20112819where s.p_no=d.p_no6.1 。（1）createafterdelete Ifrowcount=0return delete salary20112819where s.p_no=d.p_noon salary20112819declareif num_rows = 0 returnif(select count(*) 20112819p,insertedwherep.p_no=raiserror53334 rollback tranon salary20112819forasdeclareif num_rows = 0 returnif(select count(*) 20112819p,insertedwherep.p_no=raiserror 53334 rollback6.2.2 實(shí)驗(yàn) 6.2.