數(shù)據(jù)庫系統(tǒng)原理實(shí)驗(yàn)報(bào)告

1、數(shù)據(jù)庫系統(tǒng)原理實(shí)驗(yàn)報(bào)告班級(jí)學(xué)號(hào)信息與電子工程學(xué)院實(shí)驗(yàn)1 sql server熟悉和數(shù)據(jù)庫創(chuàng)建一、實(shí)驗(yàn)?zāi)康氖煜ql server 2005提供的服務(wù)管理器、企業(yè)管理器、查詢分析器、客戶端和服務(wù)器端網(wǎng)絡(luò)實(shí)用工具等常用管理工具的使用。理解客戶/服務(wù)器模式，理解面向連接與非面向連接的差別。理解交互式sql的工作機(jī)制。能夠理解命名管道協(xié)議與tcp/ip 協(xié)議的差別。能夠登陸上sql server數(shù)據(jù)庫服務(wù)器。二、實(shí)驗(yàn)容1、啟動(dòng)sql server 服務(wù)。2、 打開sql server的企業(yè)管理器，連接上sql server服務(wù)器。展開左邊樹狀窗口的各級(jí)結(jié)點(diǎn)，觀察右邊容窗口的變化。3、 打開sql se

2、rver的查詢分析器，用 use命令打開樣例數(shù)據(jù)庫 pubs。4、在查詢窗口輸入 exec sp_help ，運(yùn)行后察看結(jié)果。5、 在查詢窗口輸入 select * from authors，運(yùn)行后察看結(jié)果。三、實(shí)驗(yàn)結(jié)果3、當(dāng)不確定當(dāng)前所操作的是哪個(gè)數(shù)據(jù)庫，可使用 use來定位到某數(shù)據(jù)庫。4、MarneOwnerObject_type12 M See plication ociioins2juser table2spt_hlib3ck_dbdbousr laNe3set fallback devdbouser LabJeI 4spt Jallb3ck_usgdbouser laNe5spt _

3、monitordbouser tabh6Spt_V3lUftSdbouser 也 bb7qp_MSdea nupmerge pubishefdbostoned pracedunEBspJdSnepltartuDdbostoned procedure丨fl匚 L I _J丈！kl廠JL.EUser_type Storsgejype Length Prec Scale Nullable5、查詢某表的所有列。實(shí)驗(yàn)2簡(jiǎn)單查詢一、實(shí)驗(yàn)?zāi)康模菏煜ql server的企業(yè)管理器和查詢分析器的用戶界面，掌握用企業(yè)管理器和查詢分析器創(chuàng)建數(shù)據(jù)庫，修改數(shù)據(jù)庫和刪除數(shù)據(jù)庫的方法。二、實(shí)驗(yàn)容分別使用sql serve

4、r 2005 企業(yè)管理器和t sql語句，按下列要求創(chuàng)建、修改和刪除用 戶數(shù)據(jù)庫。1、創(chuàng)建名稱為company的數(shù)據(jù)庫，數(shù)據(jù)庫中包含一個(gè)數(shù)據(jù)文件，邏輯文件名為company_data ,磁盤文件名為 company_data.mdf，文件初始容量為 5mb,最大容量為15mb,文件容量遞增值為 1mb事務(wù)日志文件的邏輯文件名為 company_log，磁盤文 件名為company_log.ldf ，文件初始容量為 5mb最大容量為10mb,文件容量遞增值 為1mb2 、對(duì)該數(shù)據(jù)庫進(jìn)行修改：添加一個(gè)數(shù)據(jù)文件，邏輯文件名為company2_data，磁盤文件名為company2_data.ndf，

5、文件初始容量為 1mb,最大容量為 5mb文件容量遞增值為 1mb;將日志文件 company_log的最大容量增加為 15mb，文件容量遞增值為 2mb3 、在company數(shù)據(jù)庫中添加一個(gè)文件組tempgroup，并向該文件組中添加一個(gè)容量為3mb最大容量為10mb,遞增量為1mb的數(shù)據(jù)文件，該數(shù)據(jù)文件的邏輯文件名為 company3_data，磁盤文件名為 company3_data.ndf。4 、在company數(shù)據(jù)庫中刪除數(shù)據(jù)文件company2_data。5、刪除數(shù)據(jù)庫compa ny。6、 采用默認(rèn)設(shè)置創(chuàng)建數(shù)據(jù)庫compa ny。三、實(shí)驗(yàn)結(jié)果1、create database c

6、ompanyon ( n ame=compa ny_data,file name=d:microsoftsqlservermssql10_50.sqlexpressmssqldatacompa ny_data.mdf,size=5mb,maxsize=15mb,filegrowth=1mb )log on (n ame=compa ny_log,file name=d:microsoftsqlservermssql10_50.sqlexpressmssqldatacompa ny_log .l df,size=5mb,maxsize=10mb,filegrowth=1mb );2、alter

7、database companyadd file(n ame=compa ny2_data,file name=d:microsoftsqlservermssql10_50.sqlexpressmssqldatacompa ny 2_data.mdf,size=1mb,maxsize=5mb,filegrowth=1mb)alter database compa nymodify file(n ame=compa ny_log,maxsize=15mb,filegrowth=2mb);3、alter database companyadd filegroup tempgroupalter da

8、tabase compa nyadd file(n ame=compa ny 3_data,file name=d:microsoftsqlservermssql10_50.sqlexpressmssqldatacompa ny 3_data .n df,size=3mb,maxsize=10mb,filegrowth=1mb)to filegroup tempgroup;4、alter database company remove file company2_data;5、drop database company;6、create database company;實(shí)驗(yàn)3創(chuàng)建和修改數(shù)據(jù)表

9、、實(shí)驗(yàn)?zāi)康模菏煜び嘘P(guān)數(shù)據(jù)表的創(chuàng)建和修改等工作，理解數(shù)據(jù)庫模式的概念，了解主鍵約束、外鍵約束、unique約束和check約束的創(chuàng)建和應(yīng)用。要求學(xué)生熟練掌握使用企業(yè)管理器和t sql語句create table 、alter table 及drop table語句對(duì)數(shù)據(jù)表進(jìn)行管理。、實(shí)驗(yàn)容分別在sql server 2005企業(yè)管理器和在查詢分析分析器中使用 t sql語句完成以下操作：?jiǎn)T工人事表employeeemp_ nochar(5)not n ullprimary key員工編號(hào)emp_ namevarchar(10)not n ull員工sexchar not n ull性別deptv

10、archar(10)not n ull所屬部門titlevarchar(10)not n ull職稱date_hireddatetimenot n ull雇傭日birthdaydatetimen ull生日salaryintnot n ull薪水teleph onevarchar(20)n ulladdrvarchar(50)n ull住址客戶表customercust_idchar(5)not nullprimary key客戶號(hào)cust_ namevarchar(20)not null客戶名稱addrvarchar(40)not null客戶住址tel_ novarchar(20)not

11、null客戶zipchar(6)n ull郵政編碼銷售主表salesorder nointnot n ullprimary key訂單編號(hào)cust idchar(5)not n ull客戶號(hào)sale_idchar(5)not n ull業(yè)務(wù)員編號(hào)tot_amtnu meric(9,2)not n ull訂單金額order_datedatetimenot n ull訂貨日期銷貨明細(xì)表sale_itemorder_ nointnot n ullprimary key訂單編號(hào)proddchar(5)not n ullprimary key產(chǎn)口口編號(hào)qtyintnot n ull銷售數(shù)量uni t_p

12、ricenu meric(7,2)not n ull單價(jià)order_datedatetimen ull訂單日期產(chǎn)品名稱表productproddchar(5)not n ullprimary key產(chǎn)口口編號(hào)prod_ namevarchar(20)not n ull產(chǎn)品名稱1、在數(shù)據(jù)庫compa ny中創(chuàng)建以上五表，并設(shè)置各表的主鍵。2、在銷售主表 sales 中添加字段發(fā)票”invoice_no ，char(10) ， not null 。3 、添加外鍵約束：a. 在銷售主表sales的業(yè)務(wù)員編號(hào)字段 sale_id上添加外鍵約束，參照字段為員工表 employee中的字段員工編號(hào) emp

13、_no,約束名為fk_sale_id 。b. 在銷售主表 sales的客戶號(hào)字段 cust_id上添加外鍵約束，參照字段為客戶表customer中的字段客戶號(hào) cust_id，約束名為fk_cust_id 。c. 在銷售明細(xì)表sale_item的訂單編號(hào)字段order_no上添加外鍵約束，參照字段為銷售主表sales中的字段訂單編號(hào) order_no，約束名為fk_order_no 。d. 在銷售明細(xì)表sale_item的產(chǎn)品編號(hào)字段prod_id上添加外鍵約束，參照字段為產(chǎn)品名稱表product中的產(chǎn)品編號(hào)字段prodd，約束名為fk_prodd 。4 、添加核查約束：a. 將員工表empl

14、oyee中的薪水字段salary的值限定在1000至10000間，約束名為 ck_salary 。b. 將員工表employee中的員工編號(hào)字段 emp_no設(shè)定為以e”字母開頭，后面跟5位數(shù)的編號(hào)，約束名為ck_emp_no。c. 將員工表employee中的性別字段設(shè)定這取值只能是男”和女”。約束名為ck_sex。d. 將銷售主表sales中的發(fā)票字段in voice_ no設(shè)定為以“ i”字母開頭，后面跟 9位數(shù)的編號(hào)，約束名為ck_inno。5、為銷售主表sales中的字段發(fā)票invoice_no 設(shè)置為唯一約束，約束名為unnno。三、實(shí)驗(yàn)結(jié)果1、create table emplo

15、yee(emp_ no char(5) not n ull primary key,emp_ name varchar(10) not n ull,sex char(2) not n ull,dept varchar(10) not n ull,title varchar(10) not null,date_hired datetime not n ull,birthday datetime n ull,salary int not n ull,telepho ne varchar(20) n ull,addr varchar(50) null);create table customer(c

16、ust_id char(5) not n ull primary key,cust_ name varchar(20) not n ull,addr varchar(40) not n ull,tel_ novarchar(20) not nu II,zip char(6) n ull);create table sales(order_ no intnot n ull primary key,cust_id char(5) not n ull,sale_id char(5) not n ull,tot_amt nu meric(9,2) not n ull,order_date dateti

17、me not n ull)；create table sale_item(order_ no int not n ull,prod_id char(5) not nu II,qty int not nu II,unit_price numeric(7,2) not null,order_date datetime n ull,primary key(order_ no ,prod_id);create table product(prod_id char(5) not n ull primary key,prod_ name varchar(20) not n ull);2、alter tab

18、le sales add invoice_no char(10) not nuII;3、a、alter table salesadd con stra int fk_sale_idforeig n key (sale_id) refere nces employee(emp_ no);b、alter table salesadd con stra int fk_cust_idforeig n key (cust_id) references customer(cust_id);c、alter table sale_itemadd con stra int fk_order_ noforeig

19、n key (order_ no) referen ces sales(order_ no);d、alter table sale_itemadd con stra int fk_prod_idforeig n key (prod_id) references product(prod_id);4、a、alter table employeeadd con strai nt ck_salary check (salary between 1000 and 10000);b、alter table employeeadd con strai nt ck_emp_ no check(emp_ no

20、 like e0-90-90-90-90-9);c、alter table employeeadd constraint ck_sex check(sex like(男” 女);d、alter table salesadd con stra int ck_innocheck(i nvoice_no like i0-90-90-90-90-90-90-90-90-9);5、alter table salesadd con stra int un_inno uniq ue(i nvoice_ no);實(shí)驗(yàn)4簡(jiǎn)單的單表查詢、實(shí)驗(yàn)?zāi)康模菏炀氄莆沼胹elect語句實(shí)現(xiàn)簡(jiǎn)單的單表查詢。掌握select子句

21、、from子句、where子句及order by 子句的用法。實(shí)驗(yàn)容運(yùn)行查詢文件company.sql，生成上機(jī)必要的數(shù)據(jù)，然后完成以下操作。1查找所有經(jīng)理的、職稱、薪水。在銷售主表sales中查找銷售金額大于等于10000元的訂單。在員工表employee中查找薪水在 4000至8000元之間的員工。在員工表employee中查找住址為、這三個(gè)城市的員工。5、在客戶表customer中查找住址不在、這三個(gè)城市的客戶。6、在員工表employee中查找姓“王”用最后一個(gè)字為“功”的員工。7、在客戶表customer中查找姓“”的客戶名稱、。查找出職稱為“經(jīng)理”或“職員”的女工的信息。9、查找薪

22、水最高的前三條員工記錄。10、查找訂單金額最高的前 10%勺訂單記錄。11、 查找員工表中所屬部門（去掉重復(fù)記錄）。12、查找員工表中的所有記錄，并按薪水由低到高進(jìn)行排序。三、實(shí)驗(yàn)結(jié)果1、 select emp_name,title,salary from employee where title =經(jīng)理;2、select * from sales where tot_amt10000;3、select * from employee where salary between 4000 and 8000;4、select * from employee where addr in(,);5、s

23、elect * from customer where addr not in(,);6、 select * from employee where emp_name like 王%五;7、 select cust_name,tel_no from customer where cust_name like %;8、select * from employee where title in (經(jīng)理,職員)and sex= 女;9、select top 3 * from employee order by salary desc;10、select top 10 percent * from s

24、ales order by tot_amt desc;11、select distinct dept from employee;12、select * from employee order by salary;實(shí)驗(yàn)5復(fù)雜的單表查詢、實(shí)驗(yàn)?zāi)康氖炀氄莆誷elect查詢語句中的group by 子句、having子句的用法，以及匯總函數(shù)的使 用。、實(shí)驗(yàn)容1、在員工表 employee中統(tǒng)計(jì)員工人數(shù)。2、統(tǒng)計(jì)各部門員工的員工人數(shù)及平均薪水。3、查詢銷售業(yè)績(jī)超過 10000元的員工編號(hào)。4、計(jì)算每一產(chǎn)品銷售數(shù)量總和與平均銷售單價(jià)。5、 統(tǒng)計(jì)各部門不同性別、 或各部門、或不同性別或所有員工的平均薪水（

25、在group by 子 句中使用cube關(guān)鍵字）。6、 統(tǒng)計(jì)各部門不同性別、或各部門或所有員工的平均薪水（在group by 子句中使用 rollup 關(guān)鍵字）。7、計(jì)算出一共銷售了幾種產(chǎn)品。8、顯示sale_item 表中每種產(chǎn)品的訂購金額總和，并且依據(jù)銷售金額由大到小排列來顯 示出每一種產(chǎn)品的排行榜。9、計(jì)算每一產(chǎn)品每月的銷售金額總和，并將結(jié)果按銷售(月份，產(chǎn)品編號(hào))排序。10、查詢每位業(yè)務(wù)員各個(gè)月的業(yè)績(jī)，并按業(yè)務(wù)員編號(hào)、月份降序排序。三、實(shí)驗(yàn)結(jié)果1、 select count(emp_no)總?cè)藬?shù) from employee;2、 select dept,count(emp_no)員工人

26、數(shù)，avg(salary)平均薪水 from employee groupby dept;3、select sale_id,tot_amt from dbo.sales where tot_amt10000;4、 select prodd,count(prodd)銷售總量,avg(unit_price)平均單價(jià)from dbo.sale_item group by prod_id;5、select sex,title,avg(salary)平均薪水 from dbo.employee group by sex,titlewith cube;6、select case whe n( group

27、in g(sex)=1)the n allelse isnu ll(sex, unknown)end as sex,case whe n( group in g(title)=1) the n allelse isn ull(title, unknown)end as title,avg(salary) as平均薪水from employeegroup by sex,title with rollup;7、 select distinct count(prod_id)種類數(shù) from sale_item;8、 select prod_id,sum(qty * unit_price)總額from

28、 sale_itemgroup by prod_idorder by sum (uni t_price) desc;9、select prod_id,month(order_date),sum(unit_price)from sale_itemgroup by prod_id,m on th(order_date)order by prod_id,m on th(order_date) desc;10、select sale_id,month(order_date), sum(tot_amt)from salesgroup by sale_id,m on th(order_date)order

29、 by sale_id,m on th(order_date) desc;實(shí)驗(yàn)6連接查詢一、實(shí)驗(yàn)?zāi)康恼莆帐褂眠B接的方法從多個(gè)表中查詢數(shù)據(jù)。理解連接、外連接（包括左外連接、右外連接和全外連接）、自身連接的概念和使用。要求學(xué)生熟練掌握在 from子句和在where子句中指定連接條件的這兩種方法。二、實(shí)驗(yàn)容1、查找出employee表中部門相同且住址相同的女員工的、性別、職稱、薪水、住址。2、檢索product表和sale_item 表中相同產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱、數(shù)量、單價(jià)。3、檢索product表和sale_item 表中單價(jià)高于 2400元的相同產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名 稱、數(shù)量、單價(jià)。4、

30、查詢?cè)诿坑唵沃杏嗁徑痤~超過24000元的客戶名及其地址。5、查找有銷售記錄的客戶編號(hào)、名稱和訂單總額。6、每位客戶訂購的每種產(chǎn)品的總數(shù)量及平均單價(jià)，并按客戶號(hào)，產(chǎn)品號(hào)從小到大排列。7、查找在1997年中有銷售記錄的客戶編號(hào)、名稱和訂單總額。8、 分別使用左向外連接、右向外連接、完整外部連接檢索product表和sale_item 表中 單價(jià)高于2400元的相同產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱、數(shù)量、單價(jià)。并分析比較檢索的結(jié)果。三、實(shí)驗(yàn)結(jié)果1、select a.emp_name,a.sex,a.title,a.salary,a.addrfrom employee a,employee b where a

31、.dept=b.dept and a.addr=b.addr and a.sex=女；2、select d_id,prod_name,qty,unit_pricefrom sale_item,product where sale_d_id=d_id;3、select d_id,prod_name,qty,unit_pricefrom sale_item,product where uni t_price2400;4、select cust_name,addr from customerwhere cust_id

32、in (select cust_id from sales where tot_amt24000);5、 selecta.cust_id,cust_name,tot_amtfromcustomer a,salesb wherea. cust_id=b.cust_id;6、 selectprod_id,cust_id,qty,unit_pricefromsaletem,salesorderbyprod_id,cust_id;7、select a.cust_id,cust_name,tot_amtfromcustomera,saleswherea.cust id=b.cust idandcon v

33、ert(char (4), order_date,120)=1997：8、左外連接select d_id,d_ name,b.qty,b. uni t_pricefrom product a left outer join sale_itemon( d_id=d_id)andb. u nit_price2400;右外連接select d_id,d_ name,b.qty,b. uni t_pricefromproduct a right outer join sale_itemb on d_id=d_idandb.u ni t_price2400;完整外部連接select d_id,d_ name,b.qty,b. uni t_pricefromproduct a full join sale_itembon( d_id=d_id)a ndb.u ni t_price2400;實(shí)驗(yàn)7嵌套查詢?nèi)?、?shí)驗(yàn)?zāi)康恼莆誷elect語句的嵌套使用，實(shí)現(xiàn)多表的復(fù)雜查詢，進(jìn)一步理解select語句的高級(jí)使