SQL_Server__歸納整理11-1(修改)

1、.SQL Server 2005 歸納整理(修改)2011年12月19日 20:26:57首發(fā)-有錯的請?zhí)岢?，本人進(jìn)行及時修改-本文用于SQL Server 2005考試復(fù)習(xí)，選擇題部分參考課本課后選擇題(以老師上課評價的為準(zhǔn))填空部分1、數(shù)據(jù)庫的三種模型(關(guān)系模型、網(wǎng)狀模型、層次模型)2、SQL有幾種語言?（1）數(shù)據(jù)定義語言（DDL）（2）數(shù)據(jù)操作語言（DML）（3）數(shù)據(jù)控制語言（DCL）3、范式的關(guān)系概念？關(guān)系規(guī)范化的目的是為了消除存儲異常、減少數(shù)據(jù)的冗（rong）余(即重復(fù)) 以保證數(shù)據(jù)的完整性和存儲效率，范式分為三等級：范式、范式、范式，一般用到范式就好了 ，如果一個關(guān)系不滿足2NF，

2、則該關(guān)系一定也不滿足 3NF 。 (在1NF、2NF、3NF范圍內(nèi))。 (-此處修改-) 主數(shù)據(jù)文件 *.mdf 主數(shù)據(jù)文件4、數(shù)據(jù)庫文件分類: 輔助數(shù)據(jù)文件 *.ndf 事務(wù)日志文件 *.ldf通常數(shù)據(jù)庫都具有一個主數(shù)據(jù)文件和一個或多個事務(wù)日志文件。(-此處修改-)5、主鍵約束要滿足什么條件？（1）. 已有數(shù)據(jù)的主鍵字段不能為空(NULL)；（2）. 已有數(shù)據(jù)的主鍵字段必須唯一，即在這一字段上沒有重復(fù)的值。6、數(shù)據(jù)庫的完整性：（參考黃皮P171-P190）用 primary key (主鍵)約束、unique 唯一約束或標(biāo)識列保證表數(shù)據(jù)的完整性；用check（檢查）約束、default(默認(rèn)

3、)約束、default(默認(rèn)值)、rule(規(guī)則)保證列數(shù)據(jù)完整性；使用primary key（主鍵）約束、foreign key(外鍵)約束保證表與表之間參照完整性。關(guān)系模式的三類完整性約束條件分別是 域完整性約束 、參照完整性約束和 實體（表）完整性約束 。(-此處修改-)約束類型分為主鍵約束(PRIMARY KEY) 外鍵約束(FOREIGN KEY) 唯一約束(UNIQUE) 檢查約束(CHECK) 默認(rèn)約束(DEFAULT)7、視圖、索引視圖，（參考黃皮P212-P224）用于查看（瀏覽）數(shù)據(jù)庫各表之間的數(shù)據(jù)(或這查看數(shù)據(jù)表的關(guān)系)提高數(shù)據(jù)庫的開發(fā)效率，視圖是一張?zhí)摂M表?？键c：視圖的

4、創(chuàng)建，視圖刪除。索引，（參考黃皮P191-P205）用于提高數(shù)據(jù)庫的查詢速度，索引分為(clustered index)聚集索引和( nonclustered index)非聚集索引，考點：會創(chuàng)建、使用索引，重命名索引，刪除索引；8、使用什么命令可以改變數(shù)據(jù)庫文件的大??？（參考黃皮P140-P143）ALTER DATABASE 數(shù)據(jù)庫名 ADD FILE (若為日志文件：add log fileNAME = 輔助數(shù)據(jù)庫名FILENAME=C: 輔助數(shù)據(jù)庫名.ndf-文件路徑,SIZE=4MB,-大小為MBMAXSIZE=512MB,-最大容量FILEGROWTH=1MB,-每次遞增MB )A

5、lter database 命令可一改變數(shù)據(jù)庫文件大小 (-此處修改-)Size=size -修改文件容量但必需比原先的容量大。9、修改字段類型修改列的定義例如有一列為：Create table student(Lie Varchar(12) not null,)修改Lie Varchar(12) not null,中的字段的類型 ，Int 類型；使用Alter Table 命令:Alter Table student /先修改表 Alter Column Lie Int not null,/再修改列g(shù)o修改列名 將Lie 的名稱改為 StuLie, 使用系統(tǒng)存儲過程Sp_rename:Sp_

6、rename'student.Lie','StuLie','Column' go10、重命名視圖名稱將V_ss 改為 V_studentexec sp_rename 'V_ ss ','V_student' go判斷改錯、編程印刷P94Declare A IntSet A=1Select AgoSet A=A+1 -錯誤-局部變量A的范圍在go 之前有效印刷P94Create Table tblTemp(TempID int not null,TempDate datetime not null,)-(1)請寫出S

7、QL語句來顯示列TempID是偶數(shù)的記錄中列TempDate 的年份Select TempDate from tblTemp where TempID%2=0-(2)請寫出SQL語句來顯示列TempID是該類的最大值的所有記錄Select * from tblTemp where TempID=(Select max(TempID) from tblTemp)印刷P115有家企業(yè)要用表tblCustomerInfo 來存儲用戶信息 ，客戶的信息包括：代號 int 類型，名稱（最長20字），電話（20字符），傳真（20字符），備注(1000個漢字)。電話號碼和傳真要用統(tǒng)一的自定義類型，名稱為ty

8、pe_TelphoneNum。-跟據(jù)題意創(chuàng)建一個type_TelphoneNum 類型的個字符Create type type_TelphoneNum from varchar(20) null-創(chuàng)建表Create table tblCustomerInfo(代號int identity not null,名稱 varchar(80) null,電話type_TelphoneNumm null,備注varchar(2000) null,)-因為是漢字,一個漢字等于2個字符，所以字符數(shù)為2*40=80 和2*1000=2000go-更具需要，添加“手機”字段Alter table tblCust

9、omerInfoAdd 手機 type_TelphoneNum nullGo印刷P186某班級的學(xué)生成績表包括“學(xué)號”，“課程代號”，“成績”，“學(xué)期號”，請統(tǒng)計學(xué)期號為“第三學(xué)期”的每個學(xué)生所有科目的平均成績Select 學(xué)號,avg(成績) from 成績表where 學(xué)期號='第三學(xué)期' group by 學(xué)號印刷P186題目8.9Create table 部門表(部門號char(2) not null primary key,部門名稱varchar(30) not null,)Create table 雇員表(雇員代號 char(4) not null primary

10、key,雇員名稱 varchar(20) not null,)Create table 任職表(雇員代號 char(4) not null,部門號char(2) not null,開始時間datetime not null,備注varchar(1000) null, constraint pl_works primary key(雇員代號,部門號,開始時間),constraint Fk_Employees Foreign key(雇員代號)references 雇員表(雇員代號) on delete cascade,constraint Fk_Deparements Foreign key(部

11、門號)references 部門表(部門號)答案參考：select 雇員名稱 from 雇員表 as A where not exists (select B.部門名稱from 部門表as B where B.部門號 not in (select C.部門號 from 任職表 as C where A.雇員代號 = C.雇員代號)印刷P254假設(shè)有創(chuàng)建如下兩張表，并有許多數(shù)據(jù)：Create Table tblDeptinfo(DeptID char (2) not null primary key ,DeptName varchar(20) not null,Remarks varchar(2

12、55) null,)Create Table tblStudinfo(StudID char(8) not null primary key,Name varchar(20) not null,Brithday datetime null,DeptID char(2) not null,)-學(xué)生信息表tblStudinfo 的列DeptID 用來存放學(xué)生所在系部代號.該列引用信息表tblDeptinfo的列DeptID,但是沒有約束,要求創(chuàng)建約束：Alter Table tblStudinfoadd constraint C_depforeign key (DeptID) references

13、 tblDeptinfo(DeptID)以下錯誤在哪里？create view productsviewas select productID , price ,companyfrom supplier join products on suppliers.ID = products.suppllierIDorder by productID -錯誤，原因:不能在定義視圖的查詢中包含order by 語句-錯在最后一行附綜合項目操作：（包含了數(shù)據(jù)庫的創(chuàng)建、表的創(chuàng)建、數(shù)據(jù)的查詢、數(shù)據(jù)的完整性、視圖、存儲過程、觸發(fā)器、函數(shù)、備份數(shù)據(jù)庫）銷售數(shù)據(jù)庫Sales中存在這樣的三張表：1.客戶表（Custo

14、mer） 客戶編號(CusNo)姓名(CusName)地址(Address)電話(Tel)C001楊婷北002李和平上003葉新成004馮辰誠上海021-872359652.產(chǎn)品表(Product)產(chǎn)品編號(ProNo)品名(ProName)單價(price)庫存數(shù)量(Stocks)P0001液晶電視5600.00800P0002空調(diào)2390.00460P0003洗衣機3700.00600P0004電熱水器890.001203.銷售表(ProOut)銷售日期(SaleDate)客戶編號(CusNo)產(chǎn)品編號(Pro

15、No)銷售數(shù)量(Quantity)2007-10-27C001P000132007-11-06C004P0003402007-12-27C001P000352008-3-15C002P0002122008-05-02C003P0002212008-05-02C003P000192008-09-21C004P0001302008-11-21C004P000173按要求完成下列題目：（要求以下各小題均使用Transact-SQL語句完成?。ǎ?建立銷售數(shù)據(jù)庫Sales的以上三個表。（） 實現(xiàn)各表的實體完整性要求。（） 在Customer上基于CusName列創(chuàng)建非惟一索引；在Product上基于

16、ProName列創(chuàng)建惟一索引；在ProOut上基于SaleDate列創(chuàng)建聚集的、惟一索引。（） 在ProOut表上設(shè)置外鍵，保證錄入時的產(chǎn)品代碼都在產(chǎn)品表中存在的。（） 創(chuàng)建一規(guī)則限定只能錄入大于的值，然后將其綁定到ProOut表上Quantity列。（） 查詢銷售數(shù)量超過100的產(chǎn)品號。（） 創(chuàng)建一自定義函數(shù)sumMoney，要求能夠利用該函數(shù)計算出銷售金額，并進(jìn)行測試，利用該函數(shù)計算出每種產(chǎn)品(ProNo)的銷售金額。（） 查詢購買了產(chǎn)品號“P0002”的客戶編號、客戶名和電話，查詢結(jié)果按客戶名降序排列。（） 在查詢的基礎(chǔ)上創(chuàng)建一張新表Cus，要求顯示客戶“C004”在2008年購買的產(chǎn)品

17、號、數(shù)量。（） 創(chuàng)建視圖viewPro，要求顯示每種產(chǎn)品的銷售量和銷售金額（） 創(chuàng)建存儲過程p_Pro, 要求能夠根據(jù)指定的客戶編號，統(tǒng)計該客戶購買每種產(chǎn)品的產(chǎn)品號、數(shù)量。（） 創(chuàng)建一個觸發(fā)器t_Stocks，要求當(dāng)插入、更新、刪除銷售表(ProOut)的銷售記錄時，根據(jù)銷售數(shù)量(Quantity)的變化，能更新產(chǎn)品表(Product)中相應(yīng)的庫存數(shù)量(Stocks) （） 備份該數(shù)據(jù)庫Stu_info。create database Sale-（）建立銷售數(shù)據(jù)庫Sales的以上三個表。gouse Sale-（）實現(xiàn)各表的實體完整性要求。create table Customer(CusNo

18、int PRIMARY KEY not null,CusName varchar(10) not null,Address varchar(25) null,Tel varchar(13) null)create table Product(ProNo varchar(10) primary key not null,ProName varchar(10) not null,Price money null,Stocks varchar(10) not null)create table ProOut(SaleDate datetime null,CusNo int not null,ProN

19、o varchar(10) not null,Quantuty int null,check(Quantuty>0)-（）創(chuàng)建一規(guī)則限定只能錄入大于的值，然后將其綁定到ProOut表上Quantity列。)gocreate index pk_Customer on Customer(CusName)-（）在Customer上基于CusName列創(chuàng)建非惟一索引；CREATE UNIQUE INDEX IX_Product ON Product (ProName)-(3)在Product上基于ProName列創(chuàng)建惟一索引；CREATE UNIQUE CLUSTERED INDEX IX_Pr

20、oOut ON ProOut (SaleDate)-(3)在ProOut上基于SaleDate列創(chuàng)建聚集的、惟一索引。Alter Table ProOut Add Constraint pk_ProOut_Product ForEign Key (ProNo) References Product(ProNo)-（）在ProOut表上設(shè)置外鍵，保證錄入時的產(chǎn)品代碼都在產(chǎn)品表中存在的。use Saleselect * from ProOut where(Quantuty>100)-（）查詢銷售數(shù)量超過的產(chǎn)品號。-（）創(chuàng)建一自定義函數(shù)sumMoney，要求能夠利用該函數(shù)計算出銷售金額，并進(jìn)行

21、測試，利用該函數(shù)計算出每種產(chǎn)品(ProNo)的銷售金額。create function sumMoney (x money,y int)returns floatasbegin return(x*y)endgo-測試select PO.ProNo,ProName,dbo.sumMoney (price,sum(Quantuty) as '銷售金額' from Product as P,ProOut as PO where P.ProNo=PO.ProNoselect P.CusNo,CusName,Tel from ProOut as p,Customer as C where

22、 P.ProNo='P0002' and P.CusNo = C.CusNo order by CusName desc-（）查詢購買了產(chǎn)品號“P0002”的客戶編號、客戶名和電話，查詢結(jié)果按客戶名降序排列。select CusNo,sum(Quantuty)from ProOut where CusNo ='C004' and SaleDate>'2007-12-31'-（）在查詢的基礎(chǔ)上創(chuàng)建一張新表Cus，要求顯示客戶“C004”在年購買的產(chǎn)品號、數(shù)量。create view viewPro-（）創(chuàng)建視圖viewPro，要求顯示每種產(chǎn)品的銷售量和銷售金額asselect PO.ProNo,sum(Quantity) as '銷售量',sum(Quantuty * price) as &#