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

1、數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)報(bào)告 信息與電子工程學(xué)院數(shù)據(jù)庫(kù)系統(tǒng)原理實(shí)驗(yàn)報(bào)告班級(jí)_姓名_學(xué)號(hào)_信息與電子工程學(xué)院13實(shí)驗(yàn)1 sql server 熟悉和數(shù)據(jù)庫(kù)創(chuàng)建1、 實(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ù)庫(kù)服務(wù)器。2、 實(shí)驗(yàn)內(nèi)容1、啟動(dòng)sql server 服務(wù)。2、打開(kāi)sql server的企業(yè)管理器，連接上sql server服務(wù)器。展開(kāi)左邊樹(shù)狀窗口

2、的各級(jí)結(jié)點(diǎn)，觀察右邊內(nèi)容窗口的變化。3、打開(kāi)sql server的查詢分析器，用use命令打開(kāi)樣例數(shù)據(jù)庫(kù)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ù)庫(kù)，可使用use來(lái)定位到某數(shù)據(jù)庫(kù)。4、5、 查詢某張表的所有列。實(shí)驗(yàn)2 簡(jiǎn)單查詢1、 實(shí)驗(yàn)?zāi)康模?熟悉sql server的企業(yè)管理器和查詢分析器的用戶界面，掌握用企業(yè)管理器和查詢分析器創(chuàng)建數(shù)據(jù)庫(kù)，修改數(shù)據(jù)庫(kù)和刪除數(shù)據(jù)庫(kù)的方法。2、 實(shí)驗(yàn)內(nèi)容分別使用sql server 2005企業(yè)管理器和tsql

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

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

5、 filename='d:microsoft sql servermssql10_50.sqlexpressmssqldatacompany_data.mdf',size=5mb,maxsize=15mb,filegrowth=1mb )log on (name=company_log, filename='d:microsoft sql servermssql10_50.sqlexpressmssqldatacompany_log.ldf',size=5mb,maxsize=10mb,filegrowth=1mb );2、alter database comp

6、anyadd file (name=company2_data,filename='d:microsoft sql servermssql10_50.sqlexpressmssqldatacompany2_data.mdf',size=1mb,maxsize=5mb,filegrowth=1mb)alter database companymodify file (name='company_log',maxsize=15mb,filegrowth=2mb);3、alter database companyadd filegroup tempgroupalter

7、 database companyadd file(name='company3_data', filename='d:microsoft sql servermssql10_50.sqlexpressmssqldatacompany3_data.ndf', size=3mb, maxsize=10mb, filegrowth=1mb)to filegroup tempgroup;4、alter database company remove file company2_data;5、 drop database company;6、 create databa

8、se company;實(shí)驗(yàn)3 創(chuàng)建和修改數(shù)據(jù)表一、實(shí)驗(yàn)?zāi)康模?熟悉有關(guān)數(shù)據(jù)表的創(chuàng)建和修改等工作，理解數(shù)據(jù)庫(kù)模式的概念，了解主鍵約束、外鍵約束、unique約束和check約束的創(chuàng)建和應(yīng)用。要求學(xué)生熟練掌握使用企業(yè)管理器和tsql語(yǔ)句create table、alter table及drop table語(yǔ)句對(duì)數(shù)據(jù)表進(jìn)行管理。2、 實(shí)驗(yàn)內(nèi)容分別在sql server 2005企業(yè)管理器和在查詢分析分析器中使用tsql語(yǔ)句完成以下操作：?jiǎn)T工人事表employeeemp_nochar(5)not nullprimary key員工編號(hào)emp_namevarchar(10)not null員工姓名sex

9、char(2)not null性別deptvarchar(10)not null所屬部門titlevarchar(10)not null職稱date_hireddatetimenot null雇傭日birthdaydatetimenull生日salaryintnot null薪水telephonevarchar(20)null電話addrvarchar(50)null住址客戶表customercust_idchar(5)not nullprimary key客戶號(hào)cust_namevarchar(20)not null客戶名稱addrvarchar(40)not null客戶住址tel_nov

10、archar(20)not null客戶電話zipchar(6)null郵政編碼銷售主表salesorder_nointnot nullprimary key訂單編號(hào)cust_idchar(5)not null客戶號(hào)sale_idchar(5)not null業(yè)務(wù)員編號(hào)tot_amtnumeric(9,2)not null訂單金額order_datedatetimenot null訂貨日期銷貨明細(xì)表sale_itemorder_nointnot nullprimary key訂單編號(hào)prod_idchar(5)not nullprimary key產(chǎn)品編號(hào)qtyintnot null銷售數(shù)量u

11、nit_pricenumeric(7,2)not null單價(jià)order_datedatetimenull訂單日期產(chǎn)品名稱表productprod_idchar(5)not nullprimary key產(chǎn)品編號(hào)prod_namevarchar(20)not null產(chǎn)品名稱 1、在數(shù)據(jù)庫(kù)company中創(chuàng)建以上五張表，并設(shè)置各表的主鍵。2、在銷售主表sales中添加字段“發(fā)票號(hào)碼” invoice_no，char(10)，not null。 3、 添加外鍵約束：a. 在銷售主表sales的業(yè)務(wù)員編號(hào)字段sale_id上添加外鍵約束，參照字段為員工表employee中的字段員工編號(hào)emp_no

12、，約束名為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)字段prod_id，約束名為fk_prod_id。 4、添加核查約束：a. 將員工表employee中的薪

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

14、mp_no char(5) not null primary key,emp_name varchar(10) not null,sexchar(2)not null,dept varchar(10) not null,title varchar(10) not null,date_hired datetime not null,birthday datetime null,salary intnot null,telephone varchar(20) null,addr varchar(50) null);create table customer(cust_id char(5) not

15、null primary key,cust_name varchar(20) not null,addr varchar(40) not null,tel_novarchar(20)not null,zipchar(6)null);create table sales(order_no intnot null primary key,cust_idchar(5)not null,sale_idchar(5)not null,tot_amtnumeric(9,2) not null,order_date datetime not null);create table sale_item(orde

16、r_no int not null,prod_idchar(5)not null,qtyintnot null,unit_price numeric(7,2) not null,order_date datetime null,primary key(order_no,prod_id);create table product(prod_id char(5) not null primary key,prod_name varchar(20) not null);2、 alter table sales add invoice_no char(10) not null;3、 a、alter t

17、able salesadd constraint fk_sale_id foreign key (sale_id) references employee(emp_no);b、alter table salesadd constraint fk_cust_idforeign key (cust_id) references customer(cust_id);c、alter table sale_itemadd constraint fk_order_noforeign key (order_no) references sales(order_no);d、alter table sale_i

18、temadd constraint fk_prod_id foreign key (prod_id) references product(prod_id);4、a、alter table employeeadd constraint ck_salary check (salary between 1000 and 10000);b、alter table employee add constraint ck_emp_no check(emp_no like 'e0-90-90-90-90-9');c、alter table employeeadd constraint ck_

19、sex check(sex like('男''女');d、alter table sales add constraint ck_inno check(invoice_no like 'i0-90-90-90-90-90-90-90-90-9');5、alter table sales add constraint un_inno unique(invoice_no);實(shí)驗(yàn)4 簡(jiǎn)單的單表查詢一、實(shí)驗(yàn)?zāi)康模?熟練掌握用select語(yǔ)句實(shí)現(xiàn)簡(jiǎn)單的單表查詢。掌握select子句、from子句、where子句及order by 子句的用法。2、 實(shí)驗(yàn)內(nèi)容

20、運(yùn)行查詢文件company.sql，生成上機(jī)必要的數(shù)據(jù)，然后完成以下操作。 1、查找所有經(jīng)理的姓名、職稱、薪水。 2、在銷售主表sales中查找銷售金額大于等于10000元的訂單。 3、在員工表employee中查找薪水在4000至8000元之間的員工。 4、在員工表employee中查找住址為上海、北京、天津這三個(gè)城市的員工。5、 在客戶表customer中查找住址不在上海、北京、天津這三個(gè)城市的客戶。6、 在員工表employee中查找姓“王”用姓名最后一個(gè)字為“功”的員工。7、 在客戶表customer中查找姓“劉”的客戶名稱、電話。8、 查找出職稱為“經(jīng)理”或“職員”的女工的信息。9、

21、 查找薪水最高的前三條員工記錄。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_amt>10000;3、select * from employee where salary between 4000 and 8000;4、select * from employee whe

22、re addr in('上海','北京','天津');5、select * 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 titl

23、e in ('經(jīng)理','職員') and sex='女'9、select top 3 * from employee order by salary desc;10、select top 10 percent * from sales 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查詢語(yǔ)句中的group by 子句、having子句的用法

24、，以及匯總函數(shù)的使用。二、實(shí)驗(yàn)內(nèi)容1、在員工表employee中統(tǒng)計(jì)員工人數(shù)。2、統(tǒng)計(jì)各部門員工的員工人數(shù)及平均薪水。3、查詢銷售業(yè)績(jī)超過(guò)10000元的員工編號(hào)。4、計(jì)算每一產(chǎn)品銷售數(shù)量總和與平均銷售單價(jià)。5、統(tǒng)計(jì)各部門不同性別、或各部門、或不同性別或所有員工的平均薪水（在group by 子句中使用cube關(guān)鍵字）。6、統(tǒng)計(jì)各部門不同性別、或各部門或所有員工的平均薪水（在group by 子句中使用rollup關(guān)鍵字）。7、計(jì)算出一共銷售了幾種產(chǎn)品。8、顯示sale_item表中每種產(chǎn)品的訂購(gòu)金額總和，并且依據(jù)銷售金額由大到小排列來(lái)顯示出每一種產(chǎn)品的排行榜。9、計(jì)算每一產(chǎn)品每月的銷售金額總和

25、，并將結(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) 員工人數(shù),avg(salary) 平均薪水 from employee group by dept;3、select sale_id,tot_amt from dbo.sales where tot_amt>10000;4、select prod_id,count(prod_id) 銷售總量,avg(unit_price) 平均單價(jià) fr

26、om dbo.sale_item group by prod_id;5、select sex,title,avg(salary) 平均薪水 from dbo.employee group by sex,title with cube;6、select case when(grouping(sex)=1)then 'all'else isnull(sex,'unknown')end as sex,case when(grouping(title)=1) then 'all'else isnull(title,'unknown')en

27、d 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 sale_itemgroup by prod_idorder by sum(unit_price) desc;9、 select prod_id,month(order_date),sum(unit_price)from sale_itemgroup by

28、 prod_id,month(order_date)order by prod_id,month(order_date) desc;10、 select sale_id,month(order_date), sum(tot_amt) from sales group by sale_id,month(order_date)order by sale_id,month(order_date) desc;實(shí)驗(yàn)6 連接查詢一、實(shí)驗(yàn)?zāi)康恼莆帐褂眠B接的方法從多個(gè)表中查詢數(shù)據(jù)。理解內(nèi)連接、外連接（包括左外連接、右外連接和全外連接）、自身連接的概念和使用。要求學(xué)生熟練掌握在from子句和在where子句中指

29、定連接條件的這兩種方法。二、實(shí)驗(yàn)內(nèi)容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、查詢?cè)诿繌堄唵沃杏嗁?gòu)金額超過(guò)24000元的客戶名及其地址。5、查找有銷售記錄的客戶編號(hào)、名稱和訂單總額。6、每位客戶訂購(gòu)的每種產(chǎn)品的總數(shù)量及平均單價(jià)，并按客戶號(hào)，產(chǎn)品號(hào)從小到大排列。7、查找在1997年中有銷售記錄的客戶編號(hào)、名稱和訂單總額。8、分別使用左向外連接、右

30、向外連接、完整外部連接檢索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.addr from employee a,employee b where a.dept=b.dept and a.addr=b.addr and a.sex='女'2、 select d_id,prod_name,qty,unit_pricefrom sale_item,product where sal

31、e_d_id=d_id;3、 select d_id,prod_name,qty,unit_price from sale_item,product where unit_price>2400;4、 select cust_name,addr from customer where cust_id in (select cust_id from sales where tot_amt>24000);5、select a.cust_id,cust_name,tot_amt from customer a,sales b wh

32、ere a.cust_id=b.cust_id;6、select prod_id,cust_id,qty,unit_price from sale_item,sales order by prod_id,cust_id;7、select a.cust_id,cust_name,tot_amtfrom customer a,sales b where a.cust_id=b.cust_id and convert(char(4),order_date,120)='1997'8、 左外連接 select d_id,d_name,b.qty,b.unit_pric

33、e from product a left outer join sale_item b on(d_id=d_id) and b.unit_price>2400; 右外連接 select d_id,d_name,b.qty,b.unit_price from product a right outer join sale_item b on d_id=d_id and b.unit_price>2400; 完整外部連接 select d_id,d_name,b.qty,b.unit_price from

34、 product a full join sale_item b on(d_id=d_id)and b.unit_price>2400;實(shí)驗(yàn)7 嵌套查詢3、 實(shí)驗(yàn)?zāi)康恼莆誷elect語(yǔ)句的嵌套使用，實(shí)現(xiàn)多表的復(fù)雜查詢，進(jìn)一步理解select語(yǔ)句的高級(jí)使用方法。4、 實(shí)驗(yàn)內(nèi)容1、由sales表中查找出銷售金額最高的訂單。2、由sales表中查找出訂單金額大于“e0013業(yè)務(wù)員在1996/10/15這天所接任一張訂單的金額”的所有訂單，并顯示承接這些訂單的業(yè)務(wù)員和該條訂單的金額。3、找出公司女業(yè)務(wù)員所接的訂單。4、找出目前業(yè)績(jī)未超過(guò)200000元的員工。5、在銷售主表sales中查詢銷售業(yè)績(jī)最高的業(yè)務(wù)員編號(hào)及銷售業(yè)績(jī)。6、找出目前業(yè)績(jī)超過(guò)232000元的員工編號(hào)和姓名。7、查詢訂購(gòu)的產(chǎn)品至少包含了訂單10003