數(shù)據(jù)庫(kù)編程實(shí)驗(yàn)指導(dǎo)

1、實(shí)驗(yàn)一 t-sql語(yǔ)言基礎(chǔ)(一)實(shí)驗(yàn)h的：回顧基本sql語(yǔ)句的使用練習(xí)使用sql server編寫(xiě) 基本t-sql語(yǔ)句掌握t-sql中局部變量利全局變量的使用實(shí)驗(yàn)內(nèi)容：一、熟悉sql server management studio 2005的環(huán)境，口行練習(xí)新建查詢及査詢的保存、 打開(kāi)、運(yùn)行等操作。二、數(shù)據(jù)庫(kù)library,仔細(xì)觀察該數(shù)據(jù)丿牟表結(jié)構(gòu)和表間關(guān)系三、掌握t-sql中局部變量和全局變量的使用練習(xí)一：局部變量的作用域觀察運(yùn)行結(jié)果并分析出錯(cuò)原因，在實(shí)驗(yàn)報(bào)告中寫(xiě)出糾錯(cuò)代碼declare myvariable charset ©myvariable = 3010306go一批處理結(jié)

2、束.select *from stuwhere 學(xué)號(hào)=©myvariable練習(xí)二：用局部變量做計(jì)數(shù)器1、完成下列代碼，分析其結(jié)構(gòu)，在實(shí)驗(yàn)報(bào)告中寫(xiě)出代碼實(shí)現(xiàn)功能及其中函數(shù)char ()的用 途。create table testtable (cola i nt, colb char(3)godeclare mycountcrintset mycounter = 0while (mycounter < 26)begininsert into testtable values(mycounter, char( ( mycounter + ascii('a')set

3、mycounter = mycounter + 1end2、自己編寫(xiě)一個(gè)能夠利川局部變暈循環(huán)輸出數(shù)字l10的程序段，在實(shí)驗(yàn)報(bào)告屮給出代碼練習(xí)三：在查詢語(yǔ)句中使用局部變量1、利用局部變量作為條件查詢數(shù)據(jù)use scgodeclare namevariable varchar(8),©dnovariable varchar (20)set ©namevariable ='張三'select學(xué)號(hào),姓名,from stuwhere sname = namevariablego2、把杳詢結(jié)果賦值給局部變量use scgodeclare stucount intsele

4、ct stucount二count(*)from stuprint ©stucountgo說(shuō)明：如果select語(yǔ)句返回多行而且變量引用一個(gè)非標(biāo)量表達(dá)式，則變量被設(shè)置為結(jié)杲集 瑕后一行中表達(dá)式的返冋值，請(qǐng)自行設(shè)計(jì)杏詢進(jìn)行驗(yàn)證，并在實(shí)驗(yàn)報(bào)告中給出查詢語(yǔ)句和運(yùn) 行結(jié)果四、全局變量 在t-sql中的使用通過(guò)幫助查看下列全局變量的用途tdentity, error, rowcol*nt, ©©connections 并在査詢窗口進(jìn)行檢索例：select rowcount思考：以上全局變量在何時(shí)需要使用，你能否找出有實(shí)際應(yīng)川價(jià)值的使川示例五、系統(tǒng)函數(shù)的使川練習(xí)一、假定學(xué)生學(xué)

5、號(hào)字段屮的前四位為班級(jí)號(hào)，請(qǐng)使川字符處理函數(shù)len()查詢stu表屮 的學(xué)生所在班級(jí)練習(xí)二、使用時(shí)間日期函數(shù)中的yearo等函數(shù)查詢：1、所有同學(xué)的學(xué)號(hào)、姓名、出生年月2、年齡在20歲以上的同學(xué)的學(xué)號(hào)、姓名實(shí)驗(yàn)二t-sql語(yǔ)言基礎(chǔ)(二)實(shí)驗(yàn)n的：回顧基本sql語(yǔ)句的使用掌握t-sql中流稈控制語(yǔ)句使用實(shí)驗(yàn)內(nèi)容：一、tf-else語(yǔ)句的使用語(yǔ)法：if boolean expressi on sqlstatementstatementblock else sql_statement | statemcnt_block 練習(xí)一：declare x int, y int,c intselect x =

6、 1, y=lwhile x < 3begin一 begin到最后的e¥d之間的多個(gè)語(yǔ)句作為一個(gè)語(yǔ)句塊print x打印變量x的值while y < 3嵌套循環(huán)beginselect c = 100*x + yprint c打卬變量c的值select y二妙+ 1 -改變循環(huán)變量yendselect x = x + 1改變循環(huán)變量xselect y = 1end練習(xí)二：創(chuàng)建兩個(gè)新表tab1(product id int, pruduct_name varchar(20)產(chǎn)品編號(hào)產(chǎn)品名稱tab2(product_id,num,get_date)產(chǎn)品編號(hào)入庫(kù)數(shù)量入庫(kù)時(shí)間用t-

7、sql語(yǔ)句實(shí)現(xiàn)如下功能：向tab2插入一名為"暖風(fēng)機(jī)”的產(chǎn)品的入庫(kù)記錄，如果tab1中有該產(chǎn)品，則插入記錄，如沒(méi)有，則先向tab1插入該產(chǎn)品情況，再把入庫(kù)情況添加至tab20參考語(yǔ)句：declare pr1d intselect prid二prduct_idfrom tab 1where product_name=,暖風(fēng)機(jī)if prid is not nullinsert into tab2 values(prid,1(x),getdate()elsebegininset into tab2(product_name) values(；暖風(fēng)機(jī)')select pr1d=1de

8、ntityinsert intotab2 values(prid,100,getdate()end練習(xí)三、檢索圖書(shū)總?cè)藬?shù)，如果學(xué)牛人數(shù)超過(guò)500,則打印“學(xué)牛人數(shù)鮫多”，500-200打 卬“人數(shù)中等”，100-200打印“人數(shù)偏少”，100以下打印“很少”在實(shí)驗(yàn)報(bào)告中寫(xiě)出相應(yīng)語(yǔ)句。二、循環(huán)while語(yǔ)句的使用while boolean expression sql_statcmcnt | statement_block break sql_statcmcnt | statement_block continue 練習(xí)一：declare x int, y int,c intselect x =

9、 1, y=lwhile x < 3begin一 begin到最后的endz間的多個(gè)語(yǔ)句作為一個(gè)語(yǔ)句塊print x打印變量x的值while y < 3嵌套循環(huán)beginselect c = 100*x + yprint c打印變屋c的值select y二妙+ 1 -改變循環(huán)變量yendselect x = x + 1改變循環(huán)變量xselect y = 1end三、case語(yǔ)句的使用語(yǔ)法一:input_expressionwhen when expression then result expressionelse else_result_expression 語(yǔ)法二：、sewhe

10、n boolean_expression then resuit_expression n else else result expressionend練習(xí)一：利用case語(yǔ)句來(lái)簡(jiǎn)寫(xiě)岀版社use libraryselect press,case presswhen，清華大學(xué)出版社，then，清華，when '北大出版社，then t匕大，when '浙江大學(xué)出版社，then，浙大，when，四川人學(xué)出版社，then ji大，else '其它，endfrom book練習(xí)二：利丿|j case語(yǔ)句對(duì)圖書(shū)價(jià)格進(jìn)行評(píng)價(jià)use libraryselect price,casew

11、hen price >100 then燧貴,when price >60 then1很貴*when price >40 then，較貴,when price >30 thenwhen price >20 then'伸'awhen price >10 then*較便宜*else，很便宜1endfrom book實(shí)驗(yàn)三數(shù)據(jù)創(chuàng)建與數(shù)據(jù)完整性%1. 實(shí)驗(yàn)?zāi)康?. 熟練數(shù)據(jù)創(chuàng)建語(yǔ)句。2. 加深通過(guò)主鍵、外鍵、約束、默認(rèn)值等方式實(shí)現(xiàn)數(shù)據(jù)完整性的理解。%1. 實(shí)驗(yàn)內(nèi)容使用 management studio創(chuàng)建一新數(shù)據(jù)庫(kù) my library1. 使用sq

12、l語(yǔ)句創(chuàng)建教材p77頁(yè)所示表,參考代碼如下定義職員表use my ii brarycreate table dbo. clerk(clerk_id nvarchar (8) primary key not null,name nvarchar (8) null,id card nvarchar(18) null,password nvarchar (30) null,officier nvarchar(30) null)定義圖書(shū)表create table dbo. book(book id nvarchar (8) primary key not null,book_name nvarchar

13、(50) null,author nvarchar(30) null,press nvarchar(50) null,press_date datetime null,flag borrow nvarchar(1) null,price money null)定義讀者表create table dbo. reader(reader_id nvarchar(8) primary key not null,name nvarchar(8) null,idcard nvarchar(18) null,maxnum_can_borrow smallint null ,flag borrow nvarc

14、har(2) null )定義借閱表create table dbo. borrow(id int identity(1, 1) primary key not null,reader id nvarchar (8) null,book_id nvarchar(8) null,borrow_date datetime null,b_clerk_id nvarchar(8) null,r clerk id nvarchar(8) null,return date datetime null,flag return nchar(2) null )定義罰款表create table dbo. fin

15、e(id int identity(1, 1) primary key not null,reader.id nvarchar(8) null, amount int null,days smallint null,fine_date datetime null,clerk_id nvarchar(8) null,ok_date datetime null,flag fine nchar (2) null )3、表的數(shù)據(jù)完整性1) 修改讀者表中的姓名字段不允許為空alter table fdbol，readeralter column name nvarchar(8) not null2) 為

16、職員表的職位字段增加默認(rèn)值約束。alter table clerkadd constraint nl_officer default '管理員for officier3) 創(chuàng)建標(biāo)識(shí)默認(rèn)值對(duì)彖，并綁定到reader> book中的兩列。create default dfo_flagasgoexec sp.bindefault dfo_flaq,reader.flag_borrow,goexec sp.bindefault dfo_flag,book.flag_borrow,4) 創(chuàng)建口期默認(rèn)值對(duì)象，并綁定到borrow屮的兩列。create default dfo_todayas

17、gctdatc()goexec sp.bindefault dfo_today；borrow.borrow_date,goexec sp.bindefault dfo.today/borrow.return.date15) 創(chuàng)建出版日期的檢查約朿。alter table bookadd constraint ck.pressdatecheck (press_date > '1970-1-1' and press_date <getdate()go6) 創(chuàng)建價(jià)格規(guī)則限制價(jià)格范圍為。create rule rl.priceas©price > 0 and

18、 price < 300goexec sp_bindrulc rlpricc/book.pricc'go取消規(guī)則對(duì)象rl_price對(duì)price列的綁定sp_unbindrule 'book.price'說(shuō)明：分別在規(guī)則和默認(rèn)值創(chuàng)建和刪除前后錄入相應(yīng)數(shù)據(jù)，檢查規(guī)則與默認(rèn)值是否起到約束 作用7) 定義借閱信息衣中的readerjd外鍵約束use mylibrarygoif exists (select * from sys.foreign_keyswhere object_id = object_id(n'dbo.fk_borrow_reader')

19、and parent_object_id = object_id(n'dbo.borrow')goalter table dbo.borrowdrop constraint fk_borrow_readergoalter table borrowadd constraint flc_borrow_readerforeign key (readerjd)references reader (reader_id)8) 定義借閱信息表屮的book_id外鍵約束use my librarygoif exists (select * from sys.foreign_keyswhere

20、objectjd = object_id(n'dbo.fk_borrow_book')and parent_object_id = object_id(n'dbo.borrow')alter table dboj.borrowconstraint fk_borrow_bookgoalter table borrowadd constraint fk_borrow_bookforeign key (book_id)references book (bookjd)9) 定義借閱信息表中的clerk j d外鍵約朿use mylibrarygoif exists (s

21、elect * from sys.foreign_keyswhere objectjd = object_id(n'dbo.fk_borrow_clerk')and parent_object_id = objectjd(n'dbo. borrow1)alter table dbo.borrowdrop constraint fk_borrow_clerkgoalter table borrowadd constraint tk.borrow.clerk foreign key (b_clerk_id,r_clerk_id) references clerk (cler

22、k_id)實(shí)驗(yàn)四索引.視圖與游標(biāo)實(shí)驗(yàn)h的：回顧基本sql語(yǔ)句的使用練習(xí)使用sql server編寫(xiě) 基本t-sql語(yǔ)句掌握t-sql中局部變量利全局變量的使用實(shí)驗(yàn)內(nèi)容：練習(xí)一、視圖1、創(chuàng)建視圖create view vw_bookasselect * from bookgo2、通過(guò)視圖向基表中插入數(shù)據(jù)insert into vw_book( book_id book_namez author, press, press_datez price)values('10301022',，計(jì)算機(jī)組成原理一'羅克露'j電子工業(yè)出版社，,遼003-10-，33)gosele

23、ct * from book where book_id = ' 10301022'goselect * from vw_book where book_id = '10301022 *練習(xí)二、游標(biāo)1、聲明只讀游標(biāo)，查詢并并逐行顯示圖書(shū)信息。-聲明只讀游標(biāo)declare cur_book cursorforselect top 10 book_name,author,press,price from bookfor read onlygoopen cur_book-打開(kāi)游標(biāo)fetch next from cur_book -讀取游標(biāo)-循環(huán)顯示每一行記錄while fet

24、ch_status = 0beginfetch next from cur_bookendclose cur_book -關(guān)閉游標(biāo)-刪除游標(biāo)deallocate cur_book2、實(shí)現(xiàn)如下游標(biāo)功能已知如下數(shù)據(jù)：idnameidnum戶主張三老婆nullnull張三女兒nullnull李四434311null李四老婆nullnull李四兒子nullnull通過(guò)游標(biāo)實(shí)現(xiàn)idnum和戶主的一樣，張三，李四是戶主，請(qǐng)問(wèn)怎么實(shí)現(xiàn)idnameidnum戶主1張三434300是2張三老婆4343000null3張三女兒434000null4李四434311是5李四老婆434311null6李四兒子4343

25、11null實(shí)現(xiàn)代碼參考如f：declare id int,idnum int, idnum2 int; declare cur cursor fast_forward forselect idjdnum from tb;open cur;fetch next from cur into id,idnum;while fetch一status"besinif idnum is not nullbeginset idnum2 = idnumupdate tb set 八主 二'是1 where id = id實(shí)驗(yàn)五存儲(chǔ)過(guò)程與函數(shù)實(shí)驗(yàn)h的：回顧基本sql語(yǔ)句的使用練習(xí)使用sql

26、server編寫(xiě) 基本t-sql語(yǔ)句掌握t-sql中局部變量利全局變量的使用實(shí)驗(yàn)內(nèi)容：一、存儲(chǔ)過(guò)程練習(xí)1、建立簡(jiǎn)單存儲(chǔ)過(guò)程查詢書(shū)價(jià)最低的十本書(shū)create procedure sp_bookasselect top 10 book_namea author, pressa price from book order by pricegoexec sp_book2、建立帶輸入?yún)?shù)存儲(chǔ)過(guò)程，查詢指定出版社的圖書(shū)create procedure proc_book_presspress char (30)=，清華人學(xué)版社，asselect book_name,authorr press,price

27、from bookwhere press = qpressgovexec proc_book_press '科學(xué);il版社' exec proc_book_press spress = '科學(xué)出版社'3創(chuàng)建一個(gè)帶輸入輸岀參數(shù)的存儲(chǔ)過(guò)程proc_book_price,返111指定圖書(shū)工d 的圖書(shū)名稱和價(jià)格。use libraryif exists(select name from sysobjectswhere name= 1 proc_book_price1 and type = 1p1)drop procedure proc_book_pricegocreat

28、e procedure proc_book_price book_id char (30 ),-圖書(shū)idbook_name char (30) output,輸出i參數(shù)，圖書(shū)名稱book_price money output -輸參數(shù)'圖書(shū)價(jià)榕asselect book_name =book_name, book_price = price from book where book_id = book_idgo4、存儲(chǔ)過(guò)程參數(shù)中使用通配符use libraryif exists (select name from sysobjects where name=1proc_author *

29、 and type = 'p *)drop procedure proc_authorgocreate procedure proc_author author char (30)='陳 'asselect book_name,authorr press,price from bookwhere muthor like authorgoexec proc_authorexec proc_author '張 '5、建立帶輸入輸出參數(shù)存儲(chǔ)過(guò)程，返冋指定圖書(shū)id的書(shū)名與價(jià)格create procedure proc_book_price book_id cha

30、r (30 ),-圖書(shū)工dbook_name char (30) output, -輸出參數(shù)'圖書(shū)名稱 book_price money output -輸出參數(shù)/圖書(shū)價(jià)*各asselect book_name =book_nam6 book_price = price from book here book_id = book_idgodeclare bookid varchar (30),0bookname varchar(30)declare qbookprice moneyexec proc_book_price ' 10301210',booknbme out

31、put,bookprice outputselect sbookname,sbookprice二、自定義函數(shù)1、自定義標(biāo)量函數(shù)，實(shí)現(xiàn)對(duì)圖書(shū)價(jià)格的高與低的評(píng)價(jià)。use libraryif exists (select name from sysobjectswhere name= 1fc_price 1 and xtype = * fn *)drop function fc_pricegocreate function fc_price(priceinput money)returns nvarchar(10)begindeclare sreturnstr nvarchar(10)if pric

32、einput > 40set sreturnstr ='較貴圖書(shū)'else,、set sreturnstr ='便宜圖書(shū)'return greturnstrend2、use librarygocreate function fc_press(press varchar (30)returns tableasreturn(select book_namez author,pressz price from bookwhere press = spressselect * from dbo. fc_press清華大學(xué)出版社 3、口定義多語(yǔ)句表值函數(shù)use li

33、brarygocreate function fc_press_l(press varchar (50)returns tb_press table(book_name varchar(50) null, authorvarchar(30) null,pressvarchar(50) null,price money)asbegininsert 吐b_pressselect book_name,authorz pressa price from bookwhere press = spressreturnendgoselect * from dbo. f c_press_l清華人學(xué)出版社

34、9;)go、select * from dbo fc_press_l ('禾斗學(xué)出版社')go實(shí)驗(yàn)六觸發(fā)器的使用實(shí)驗(yàn)d的：回顧基本sql語(yǔ)句的使用練習(xí)使用sql server編寫(xiě) 基本t-sql語(yǔ)句掌握t-sql中局部變量和全局變量的使用實(shí)驗(yàn)內(nèi)容：1、創(chuàng)建insert觸發(fā)器，在插入數(shù)據(jù)時(shí)檢查出版日期use libraryif exists(select name from sysobjectswhere name= 1 tri_book_insert * and type = 1tr1)drop trigger tri_book_insertgocreate trigger t

35、ri_book_inserton bookfor insertasdeclare press_date datetimeselect press_date = book.press_date from book,insertedwhere book.book_id = inserted.book_idif press_date > getdate()beginraiserror ( 1出版日期錯(cuò)誤，請(qǐng)刪除該行數(shù)據(jù)！ 1 , 16, 10)endgo-插入數(shù)據(jù)insert into book(book_id fbook_name, author,press,press_date,price

36、)values(1 10037974 1 , ! sql server數(shù)據(jù)庫(kù)應(yīng)用教程-1關(guān)敬嫩j巧青華大學(xué)出 版社一 ,2007-10-1, 22.00)-檢查該數(shù)據(jù)是否插入成功select * from book where book_id = 110037974 f2、創(chuàng)建更新觸發(fā)器，以檢查對(duì)價(jià)格的修改use libraryif exists (select name from sysobjectswhere name= 1tri_book_update' and type = 'tr!)drop trigger tri_book_updatecreate trigger

37、tri_book_updateon bookfor updateasif update(price)beginselect insertedbook_n3me, inserted.price as new_price, deleted.price as old_pricefrom inserted,deletedwhere insertedbook_id = deletedbook_idendgoupdate book set price = 2000 where book_id =* 10301022 *3、創(chuàng)建更新觸發(fā)器，用column_update ()函數(shù)檢查對(duì)價(jià)格列的修改use li

38、braryif exists (select name from sysobjectswhere name= ' tri_book_upda' and type = 1tr1)drop triggerok_updm*2gocreate trigger tri_book_update2on bookfor updateas一檢查笫列(price列)是否有更新if columns_updated( ) & 00000010 = 2beginselect inserted bo ok_name, inserted .price as new_price, deleted.pr

39、ice as old_pricefrom inserted,deletedwhere insertedbook_id = deletedbook_idendgoupdate book set price = 25 where book_id =11111087414、創(chuàng)建刪除觸發(fā)器，以記錄刪除的圖書(shū)冊(cè)數(shù)及刪除圖書(shū)價(jià)格之和create trigger tri_book_deletedon bookfor deleteasselect count(deletedbook_n3me) as delete_count, sum(deleted-price) as total_pricefrom del

40、etedgodelete book where left(book_id,4) = 19810 1select top 20* from book order by book_id 5、創(chuàng)建ddl觸發(fā)器，實(shí)現(xiàn)刪除和修改表時(shí)顯示警告信息create trigger safetyon databasefor drop_tablez alter_tableasprint，要?jiǎng)h除或修改表z前，你須先禁用觸發(fā)器safety! *rollbackgodrop table new table實(shí)驗(yàn)七在sql server中使用xml實(shí)驗(yàn)d的：回顧基本sql語(yǔ)句的使用練習(xí)使用sql server編寫(xiě) 基本t-s

41、ql語(yǔ)句 掌握t-sql中局部變量和全局變量的使用實(shí)驗(yàn)內(nèi)容：1、創(chuàng)建xml架構(gòu)并査詢create xml schema collection xmlbookschema asn 1 <?xml version=n 1 0 h encoding=lf utf-1 6n ?><xsd:schema targetnamespace=nhttp:/myxmlscheman elementformdefault = h qualified11attributeformdefault = nunqualif iednxmlns:xsd="http:/www w3 org/2 0

42、01/xmlschema” ><xsd:element name=nbook”<xsd:complextype mixed=ntruen> <xsd:sequence><xsd:element=lf xsd: st ring11 / ><xsd:element=nxsd:stringn/><xsd:element<xsd:element</xsd:sequence></xsd:complextype></xsd:element></xsd:schema>1 ;goname=h

43、booknainen typename=nauthor" typename=npressn type ="xsd:string"/>name=npricen type =nxsd:float"/>-查詢數(shù)據(jù)庫(kù)中的架構(gòu)集.select *from sysxml_schema_collections 查詢數(shù)據(jù)庫(kù)屮的命名空間.select namefrom sysxml_schema_namespaces2、創(chuàng)建xml數(shù)據(jù)類型以及相關(guān)的表并插入數(shù)據(jù)創(chuàng)建xml類型變msbook并將架構(gòu)集xmlbookschema與其關(guān)聯(lián)。 declare book

44、xml (xmlbookschema)-chap 10-3-創(chuàng)建xml列的表create table xmlbook(id int,xmlbook xml (xmlbookschema)-chap 10-4-向xml列中插入xml類型數(shù)據(jù)create table xml_book(id int primary key, content xml) godeclare s varchar(200)set s = '<book><book_nbm0>計(jì)算機(jī)組成原理</1300)<_11且111 xauthoi: 3m</author></

45、book> 'insert into xml_book values (3, s)-隱式轉(zhuǎn)換insert into xml_book values (4, cast (s as xml) -一cast 顯示轉(zhuǎn)換insert into xml_book values (5, convert (xml, s) ) 一一convert 自動(dòng)轉(zhuǎn)換3、使用query方法查詢xml實(shí)例xmldoc屮結(jié)點(diǎn)book的內(nèi)容。 declare xmldoc xmlset xmldoc = (select book_namez price from bookwhere press=巧青華大學(xué)出版社，f

46、or xml auto,type)select xmldoc query(，book，)4、對(duì)xml數(shù)據(jù)進(jìn)行操作1) 查詢xml變量屮是否存在/ book節(jié)點(diǎn)。declare xmldoc xmlset xmldoc = (select book_name, price from bookwhere press=，清華大學(xué)岀版社，for xml auto,type)select xmldoc exist('/book')2) 將xmldoc中的第一行數(shù)據(jù)的書(shū)名更新為''sql server 2005編程基礎(chǔ)。declare xmldoc xmlset xmldo

47、c = (select book_name, price from bookwhere press=，清華大學(xué)出版社，for xml auto,type)set xmldoc.modify(replace value of (/book/book_name) 1 with nsql server 2005編程基礎(chǔ)h 1 )select xmldoc3) 將xml類型變量分解成book表。declare sxmldoc xmlset xmldoc = (select book_name, price from book where press=，清華人學(xué)出版社，for xml autoz typ

48、e)fromselect bookbook_name.query(1.1) as namexmldocnodes(1/book 1) book(book_name)4) 使用openxml檢索xml中的數(shù)據(jù)declare qxmldoc xmldeclare ixmldoc intset sxmldoc = 1<book><book_n3me> 操作系統(tǒng)原理 </book_name <price>2 9.0000</price></book>1exec sp_xml_preparedocument sixmldoc output

49、, xmldocselect*fromopenxml (qixmldoc, 1/book 1,10)with (book_name varchar(30)zprice float)exec sp_xml_removedocument sixmldoc實(shí)驗(yàn)八實(shí)例開(kāi)發(fā)練習(xí)%1. 實(shí)驗(yàn)?zāi)康氖煜?shù)據(jù)庫(kù)設(shè)計(jì)的棊本方法和步驟，搞淸數(shù)據(jù)庫(kù)設(shè)計(jì)各個(gè)階段所耍完成的任務(wù)。編碼并實(shí) 現(xiàn)系統(tǒng)中至少一模塊功能，要求使用到存儲(chǔ)過(guò)程、觸發(fā)器等數(shù)據(jù)庫(kù)對(duì)象。%1. 實(shí)驗(yàn)內(nèi)容分析教學(xué)用數(shù)據(jù)庫(kù)library的結(jié)構(gòu)，利用編程語(yǔ)言（不限）實(shí)現(xiàn)系統(tǒng)川戶管理圖書(shū)信息管理讀者信息管理借書(shū)還書(shū)查詢等功能要求1、有對(duì)表結(jié)構(gòu)的分析說(shuō)明2、有功能模

50、塊功能說(shuō)明3、后臺(tái)數(shù)據(jù)庫(kù)中根據(jù)具體分析，創(chuàng)建相應(yīng)視圖、存儲(chǔ)過(guò)程與觸發(fā)器4、實(shí)現(xiàn)至少兩個(gè)功能模塊實(shí)驗(yàn)九嵌入式數(shù)據(jù)庫(kù)%1. 實(shí)驗(yàn)?zāi)康氖煜qlite的api函數(shù)在c語(yǔ)言中的編程應(yīng)用。二、實(shí)驗(yàn)內(nèi)容1、創(chuàng)建數(shù)據(jù)庫(kù)編寫(xiě)c語(yǔ)言程序創(chuàng)建數(shù)據(jù)庫(kù)及數(shù)表，并給表中添加數(shù)據(jù)。設(shè)數(shù)據(jù)庫(kù)名為“student.db”, 其中有一個(gè)學(xué)生基本信息數(shù)據(jù)表，衣名為“baseinfo",有5個(gè)字段及若干個(gè)記錄，如表21 所示。表2-1學(xué)生基本信息數(shù)據(jù)表numnamesexageclassadd res1zhangsanman22076411bcijing2guihuawoman21076412shanghai3lisim

51、an21076411tianjin4hehuawoman20076412chongqing5zhaowuman22076411guangzhou(1)程序清單文件名為creatdb.c#include <stdio.h>#includc <sqlitc3.h>int main( void ) sqlite3 *db=null;char *errmsg = 0, *sql; int rc;rc = sqlite3_open(mstudent.dbh5 &db); 打開(kāi)指定的數(shù)據(jù)丿牟文件，若不存在則創(chuàng)建 if( rc) printf(ncan,t open data

52、base: %siv； sqlite3_errmsg(db);sqlite3_close(db);exit(l);printf(hyou have created a sqlite3 database student.db successfully!n h);sql = " create table baseinfo(name, sex, age, class, addres);" sqlite3_exec( db , sql, 0,0 , &errmsg );創(chuàng)建 baseinfo 表sqi="insertinto baseinfo values(

53、9;zhangsan', 'man', 22, '0711', 'bcijing');" sqlite3_exec( db , sql, 0,0 , &ernnsg );插入記錄sql=hinsert into baseinfo values('guihua','woman',21,'0712','shanghai');”; sqlite3_exec( db , sql, 0,0, &errmsg );/插入記錄sql = ''ins

54、ert into baseinfo values('lisi', 'man', 20, '0712', 'tianjin');”;sqlite3_exec( db , sql, 0,0, &errmsg );插入記錄sql=“insert into baseinfo values('hehua','woman',21,'0711','chonhqing');”; sqlite3_exec( db, sql, 0,0, &enmsg );插入記錄sql="insert into baseinfo values(tzhaowutman22；071 l,；guangzhou,);“; sqlite3_exec( db, sql, 0,0, &errmsg );插入記錄sqlite3_close(db);關(guān)閉數(shù)據(jù)庫(kù)return 0;(2) 編譯與運(yùn)行編譯：# gcc -lsqlite3 -l/usr/local/