數(shù)據(jù)庫系統(tǒng)原理實驗報告班級_姓名_學(xué)號_信息與電子工程學(xué)院實驗1 sql server 熟悉和數(shù)據(jù)庫創(chuàng)建1、 實驗?zāi)康氖煜ql server 2005提供的服務(wù)管理器、企業(yè)管理器、查詢分析器、客戶端和服務(wù)器端網(wǎng)絡(luò)實用工具等常用管理工具的使用。理解客戶/服務(wù)器模式，理解面向連接與非面向連接的差別。理解交互式sql的工作機制。能夠理解命名管道協(xié)議與tcp/ip協(xié)議的差別。能夠登陸上sql server數(shù)據(jù)庫服務(wù)器。2、 實驗內(nèi)容1、啟動sql server 服務(wù)。2、打開sql server的企業(yè)管理器，連接上sql server服務(wù)器。展開左邊樹狀窗口的各級結(jié)點，觀察右邊內(nèi)容窗口的變化。3、打開sql server的查詢分析器，用use命令打開樣例數(shù)據(jù)庫pubs。4、在查詢窗口輸入exec sp_help，運行后察看結(jié)果。5、在查詢窗口輸入select * from authors ，運行后察看結(jié)果。三、實驗結(jié)果3、當(dāng)不確定當(dāng)前所操作的是哪個數(shù)據(jù)庫，可使用use來定位到某數(shù)據(jù)庫。4、5、 查詢某張表的所有列。實驗2 簡單查詢1、 實驗?zāi)康模?熟悉sql server的企業(yè)管理器和查詢分析器的用戶界面，掌握用企業(yè)管理器和查詢分析器創(chuàng)建數(shù)據(jù)庫，修改數(shù)據(jù)庫和刪除數(shù)據(jù)庫的方法。2、 實驗內(nèi)容分別使用sql server 2005企業(yè)管理器和tsql語句，按下列要求創(chuàng)建、修改和刪除用戶數(shù)據(jù)庫。 1、創(chuàng)建名稱為company的數(shù)據(jù)庫，數(shù)據(jù)庫中包含一個數(shù)據(jù)文件，邏輯文件名為company_data，磁盤文件名為company_data.mdf，文件初始容量為5mb，最大容量為15mb，文件容量遞增值為1mb；事務(wù)日志文件的邏輯文件名為company_log，磁盤文件名為company_log.ldf，文件初始容量為5mb，最大容量為10mb，文件容量遞增值為1mb。 2、對該數(shù)據(jù)庫進(jìn)行修改：添加一個數(shù)據(jù)文件，邏輯文件名為company2_data，磁盤文件名為company2_data.ndf，文件初始容量為1mb，最大容量為5mb，文件容量遞增值為1mb；將日志文件company_log的最大容量增加為15mb，文件容量遞增值為2mb。 3、 在company數(shù)據(jù)庫中添加一個文件組tempgroup，并向該文件組中添加一個容量為3mb，最大容量為10mb，遞增量為1mb的數(shù)據(jù)文件，該數(shù)據(jù)文件的邏輯文件名為company3_data，磁盤文件名為company3_data.ndf。 4、在company數(shù)據(jù)庫中刪除數(shù)據(jù)文件company2_data。5、 刪除數(shù)據(jù)庫company。6、 采用默認(rèn)設(shè)置創(chuàng)建數(shù)據(jù)庫company。三、實驗結(jié)果1、create database companyon ( name=company_data, 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 companyadd 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 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 database company;實驗3 創(chuàng)建和修改數(shù)據(jù)表一、實驗?zāi)康模?熟悉有關(guān)數(shù)據(jù)表的創(chuàng)建和修改等工作，理解數(shù)據(jù)庫模式的概念，了解主鍵約束、外鍵約束、unique約束和check約束的創(chuàng)建和應(yīng)用。要求學(xué)生熟練掌握使用企業(yè)管理器和tsql語句create table、alter table及drop table語句對數(shù)據(jù)表進(jìn)行管理。2、 實驗內(nèi)容分別在sql server 2005企業(yè)管理器和在查詢分析分析器中使用tsql語句完成以下操作：員工人事表employeeemp_nochar(5)not nullprimary key員工編號emp_namevarchar(10)not null員工姓名sexchar(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客戶號cust_namevarchar(20)not null客戶名稱addrvarchar(40)not null客戶住址tel_novarchar(20)not null客戶電話zipchar(6)null郵政編碼銷售主表salesorder_nointnot nullprimary key訂單編號cust_idchar(5)not null客戶號sale_idchar(5)not null業(yè)務(wù)員編號tot_amtnumeric(9,2)not null訂單金額order_datedatetimenot null訂貨日期銷貨明細(xì)表sale_itemorder_nointnot nullprimary key訂單編號prod_idchar(5)not nullprimary key產(chǎn)品編號qtyintnot null銷售數(shù)量unit_pricenumeric(7,2)not null單價order_datedatetimenull訂單日期產(chǎn)品名稱表productprod_idchar(5)not nullprimary key產(chǎn)品編號prod_namevarchar(20)not null產(chǎn)品名稱 1、在數(shù)據(jù)庫company中創(chuàng)建以上五張表，并設(shè)置各表的主鍵。2、在銷售主表sales中添加字段“發(fā)票號碼” invoice_no，char(10)，not null。 3、 添加外鍵約束：a. 在銷售主表sales的業(yè)務(wù)員編號字段sale_id上添加外鍵約束，參照字段為員工表employee中的字段員工編號emp_no，約束名為fk_sale_id。b. 在銷售主表sales的客戶號字段cust_id上添加外鍵約束，參照字段為客戶表customer中的字段客戶號cust_id，約束名為fk_cust_id。c. 在銷售明細(xì)表sale_item的訂單編號字段order_no上添加外鍵約束，參照字段為銷售主表sales中的字段訂單編號order_no，約束名為fk_order_no。d. 在銷售明細(xì)表sale_item的產(chǎn)品編號字段prod_id上添加外鍵約束，參照字段為產(chǎn)品名稱表product中的產(chǎn)品編號字段prod_id，約束名為fk_prod_id。 4、添加核查約束：a. 將員工表employee中的薪水字段salary的值限定在1000至10000間，約束名為ck_salary。b. 將員工表employee中的員工編號字段emp_no設(shè)定為以“e”字母開頭, 后面跟5位數(shù)的編號，約束名為ck_emp_no。c. 將員工表employee中的性別字段設(shè)定這取值只能是“男”和“女”。約束名為ck_sex。d. 將銷售主表sales中的發(fā)票號碼字段invoice_no設(shè)定為以“i”字母開頭，后面跟9位數(shù)的編號，約束名為ck_inno。5、為銷售主表sales中的字段發(fā)票號碼invoice_no設(shè)置為唯一約束，約束名為un_inno。三、實驗結(jié)果1、create table employee(emp_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 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(order_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 table 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_itemadd 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_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);實驗4 簡單的單表查詢一、實驗?zāi)康模?熟練掌握用select語句實現(xiàn)簡單的單表查詢。掌握select子句、from子句、where子句及order by 子句的用法。2、 實驗內(nèi)容運行查詢文件company.sql，生成上機必要的數(shù)據(jù)，然后完成以下操作。 1、查找所有經(jīng)理的姓名、職稱、薪水。 2、在銷售主表sales中查找銷售金額大于等于10000元的訂單。 3、在員工表employee中查找薪水在4000至8000元之間的員工。 4、在員工表employee中查找住址為上海、北京、天津這三個城市的員工。5、 在客戶表customer中查找住址不在上海、北京、天津這三個城市的客戶。6、 在員工表employee中查找姓“王”用姓名最后一個字為“功”的員工。7、 在客戶表customer中查找姓“劉”的客戶名稱、電話。8、 查找出職稱為“經(jīng)理”或“職員”的女工的信息。9、 查找薪水最高的前三條員工記錄。10、 查找訂單金額最高的前10%的訂單記錄。11、 查找員工表中所屬部門（去掉重復(fù)記錄）。12、 查找員工表中的所有記錄，并按薪水由低到高進(jì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、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 title 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;實驗5 復(fù)雜的單表查詢一、實驗?zāi)康氖炀氄莆誷elect查詢語句中的group by 子句、having子句的用法，以及匯總函數(shù)的使用。二、實驗內(nèi)容1、在員工表employee中統(tǒng)計員工人數(shù)。2、統(tǒng)計各部門員工的員工人數(shù)及平均薪水。3、查詢銷售業(yè)績超過10000元的員工編號。4、計算每一產(chǎn)品銷售數(shù)量總和與平均銷售單價。5、統(tǒng)計各部門不同性別、或各部門、或不同性別或所有員工的平均薪水（在group by 子句中使用cube關(guān)鍵字）。6、統(tǒng)計各部門不同性別、或各部門或所有員工的平均薪水（在group by 子句中使用rollup關(guān)鍵字）。7、計算出一共銷售了幾種產(chǎn)品。8、顯示sale_item表中每種產(chǎn)品的訂購金額總和，并且依據(jù)銷售金額由大到小排列來顯示出每一種產(chǎn)品的排行榜。9、計算每一產(chǎn)品每月的銷售金額總和，并將結(jié)果按銷售（月份，產(chǎn)品編號）排序。10、查詢每位業(yè)務(wù)員各個月的業(yè)績，并按業(yè)務(wù)員編號、月份降序排序。三、實驗結(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_amt10000;4、select prod_id,count(prod_id) 銷售總量,avg(unit_price) 平均單價 from 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 allelse isnull(sex,unknown)end as sex,case when(grouping(title)=1) then allelse isnull(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 sale_itemgroup by prod_idorder by sum(unit_price) desc;9、 select prod_id,month(order_date),sum(unit_price)from sale_itemgroup by 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;實驗6 連接查詢一、實驗?zāi)康恼莆帐褂眠B接的方法從多個表中查詢數(shù)據(jù)。理解內(nèi)連接、外連接（包括左外連接、右外連接和全外連接）、自身連接的概念和使用。要求學(xué)生熟練掌握在from子句和在where子句中指定連接條件的這兩種方法。二、實驗內(nèi)容1、查找出employee表中部門相同且住址相同的女員工的姓名、性別、職稱、薪水、住址。2、檢索product 表和sale_item表中相同產(chǎn)品的產(chǎn)品編號、產(chǎn)品名稱、數(shù)量、單價。3、檢索product 表和sale_item表中單價高于2400元的相同產(chǎn)品的產(chǎn)品編號、產(chǎn)品名稱、數(shù)量、單價。4、查詢在每張訂單中訂購金額超過24000元的客戶名及其地址。5、查找有銷售記錄的客戶編號、名稱和訂單總額。6、每位客戶訂購的每種產(chǎn)品的總數(shù)量及平均單價，并按客戶號，產(chǎn)品號從小到大排列。7、查找在1997年中有銷售記錄的客戶編號、名稱和訂單總額。8、分別使用左向外連接、右向外連接、完整外部連接檢索product 表和sale_item表中單價高于2400元的相同產(chǎn)品的產(chǎn)品編號、產(chǎn)品名稱、數(shù)量、單價。并分析比較檢索的結(jié)果。三、實驗結(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 sale_d_id=d_id;3、 select d_id,prod_name,qty,unit_price from sale_item,product where unit_price2400;4、 select cust_name,addr from customer where cust_id in (select cust_id from sales where tot_amt24000);5、select a.cust_id,cust_name,tot_amt from customer a,sales b where 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_price from product a left outer join sale_item b on(d_id=d_id) and b.unit_price2400; 右外連接 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_price2400; 完整外部連接 select d_id,d_name,b.qty,b.unit_price from product a full join sale_item b on(d_id=d_id)and b.unit_price2400;實驗7 嵌套查詢3、 實驗?zāi)康恼莆誷elect語句的嵌套使用，實現(xiàn)多表的復(fù)雜查詢，進(jìn)一步理解select語句的高級使用方法。4、 實驗內(nèi)容1、由sales表中查找出銷售金額最高的訂單。2、由sales表中查找