SQLServer數(shù)據(jù)庫設(shè)計(jì)和高級(jí)查詢2

1、第1章數(shù)據(jù)庫的設(shè)計(jì)1. 設(shè)計(jì)數(shù)據(jù)庫時(shí)的步驟：1. 需求分析。2. 概耍設(shè)計(jì)。3. 詳細(xì)設(shè)計(jì)。4. 代碼編寫。5. 運(yùn)行測(cè)試。6. 打包發(fā)行。設(shè)計(jì)數(shù)據(jù)庫時(shí)要符合三大范式：1不允許有套表出現(xiàn)。即：確保每列的原子性。例如：“地址”可以分為國家、省、市、區(qū) 等。有的程序把“姓名”分為“姓”和“名”。2. 每一行只能有唯一的單位來決定。即：除了主鍵外的其他列都依賴于該鍵。3. 確保每列都和主鍵列直接相關(guān)，而不是間接相關(guān)。第2章數(shù)據(jù)庫的實(shí)現(xiàn)1.使用sql語句創(chuàng)建和刪除數(shù)據(jù)庫數(shù)據(jù)庫文件由3部分組成：(1) 主數(shù)據(jù)文件：*.mdfo(2) 次數(shù)據(jù)文件：*.ndfo(3) 日志文件：*.ldfo2 創(chuàng)建數(shù)據(jù)庫c

2、reate database 數(shù)據(jù)庫名創(chuàng)建數(shù)據(jù)庫on primary /primary可以省略，代表指定的主文件組中的文件此文件為數(shù)據(jù)文件(name二'邏輯文件名；-主數(shù)據(jù)文件的邏輯名稱后的單引號(hào)可以去掉有黃色地板的表示是必須有的filename物理文件名'，-主數(shù)據(jù)文件的物理名稱后的單引號(hào)必須有引號(hào)內(nèi)必須有盤符 例如d:文件名.mdfsize二大小，主數(shù)據(jù)文件的初始大小maxsize=最大容量，主數(shù)據(jù)文件的增長的最大值 filegrowth=增長量-主數(shù)據(jù)文件的增長率)log on 指明id志文件的明確定義(所需要的參數(shù)同上)3.刪除數(shù)據(jù)庫drop database數(shù)據(jù)庫名例

3、：use master /設(shè)置當(dāng)前數(shù)據(jù)庫為master,以便訪問sysdatabases表 goif exists(select name from sysdatabases where name=,studb,)drop database studbgocreate database studbon( )log on)go4 創(chuàng)建表create table 表名（字段1數(shù)據(jù)類型列的特征，字段2數(shù)據(jù)類型列的特征 ）“列的特征”包扌舌是否為空（null）、是否是標(biāo)示列（自動(dòng)編號(hào)）、是否有默認(rèn)值、是否為主 鍵等。列屬性“identity （起始值，遞增量）”表示列為自動(dòng)編號(hào)，也稱為標(biāo)識(shí)列。4 刪除

4、表drop table 表名例：use studbgoif exists(select name from sysobjects where name='stulnfo')drop table stulnfo刪除表gocreate table stulnfo一創(chuàng)建表(stuname varchar(20) not null, 學(xué)員姓名，非空(必填)stuno char(6) not null,學(xué)號(hào)，非空stuage int not null,年齡stuid numeric(18,0),-身份證號(hào)，numeric。&0)代表數(shù)字，小數(shù)位數(shù)為()stuseat smalli

5、nt identity(l,l),座位號(hào)，自動(dòng)編號(hào)(標(biāo)識(shí)列),從1開始增 stuaddress text-住址)go添加列alter table 表名 列名 col type5.刪除表使用sql語句創(chuàng)建和刪除約束a. 主鍵約束(primary key constraint)：要求主鍵列數(shù)據(jù)唯一，并且不允許為空。b. 唯一約束(uniqueconstniint):要求該列唯一，允許為空，但只能出現(xiàn)一個(gè)空值。c. 檢查約朿(check constraint) 列収值范圍限制、格式限制等，如有關(guān)年齡約束。d. 默認(rèn)約束(defaultconstraint):某列的默認(rèn)值，如我們的男性學(xué)員較多，性別默

6、認(rèn)為“男”。e. 外鍵約束(foreign key constraint)：用于在兩表之間建立關(guān)系，需要指定引用主表的哪一 列。6 添加約束alter table表名add constraint約束名 約束類型 具體的約束說明 約束名的命名規(guī)則采用“約束類型一約束字段”這樣的形式。例：alter table stulnfo add constraint pk_stuno primary key(stuno)添加主鍵約束-添加唯一約束alter table stulnfo add constraint uq_stuid unique(stuid)alter table stulnfo add c

7、onstraint df_stuaddress default('地址不詳')from stuaddress地址不填默認(rèn)為“地址不填”alter table stulnfo add constraint fk_stuno foreign key(stuno) references stuinfo(stuno)-添加外鍵約朿添加約束時(shí)還可以直接在創(chuàng)建表時(shí)直接添加約束添加外鍵約束列名類型列的特征foreign key表名（主鍵列）7.刪除約束alter table 表名 drop constraint 約束名例：alter table stulnfo drop constraint

8、 df_stuaddress.8使用sql語句創(chuàng)建登錄第一關(guān)：需要登錄到sql server系統(tǒng)，即需要登錄賬戶。第二關(guān)：需要訪問某個(gè)數(shù)據(jù)庫（相當(dāng)于我們的單元樓），即需要成為該數(shù)據(jù)庫的用戶， 第三關(guān)：需要訪問數(shù)據(jù)表屮的表（相當(dāng)于打開我們的房間），即需要數(shù)據(jù)庫管理員dba給 白己授權(quán)，如增添、修改、刪除、查詢等權(quán)限。9創(chuàng)建登錄賬戶a. sql身份驗(yàn)證：適合于非windows平臺(tái)的用戶或internet用戶，需要提供賬 戶和密碼。exec sp_addlogin '賬戶名密碼，例：exec sp_addlogin 'zhangsan',' 1234' 賬戶名

9、為 zhangsan 密碼為 1234b. windows身份驗(yàn)證:適合于windows平臺(tái)用戶，不需要提供密碼，和windows 集成驗(yàn)證。exec sp_grantlogin 'windows 域名域賬戶，例：exec sp_grantlogin 4jbtrainings26301'10 創(chuàng)建數(shù)據(jù)庫用戶創(chuàng)建數(shù)據(jù)庫用戶需要調(diào)用系統(tǒng)存儲(chǔ)過程sp_grantdbaccess調(diào)用語法：exec sp_grantdbaccess 錄賬戶數(shù)據(jù)庫用戶一“數(shù)據(jù)庫用戶”為可選參數(shù)，默認(rèn)為登錄賬戶，即數(shù)據(jù)庫用戶默認(rèn)和登錄賬戶同名。例：use usedbgoexec sp_grantdbacce

10、ss 4jbtrainings26301, /s26301 dbuser5exec sp_grantdbaccess tzhangsan,zhangsandbuser,笛給數(shù)據(jù)庫用戶授權(quán)常用的權(quán)限包括添加數(shù)據(jù)(insert)、刪除數(shù)據(jù)(delete)、更新數(shù)據(jù)(update)、查看數(shù)據(jù)(select)> 創(chuàng)建表(create table)等操作。grant權(quán)限on表名lo數(shù)據(jù)庫用戶例：user studbgogrant select, insert,update,select on stulnfo to zhangsandbuser-stulnfo 代表表名，zhangsandbuser

11、代表數(shù)據(jù)庫用戶。第3章t-sql編程1. 使用變量t-sql 111的變量分為局部變量和全局變量。局部變量的使用也是先聲明，再賦值。而全 局變量由系統(tǒng)定義和維護(hù)，可以直接使用，一般不自定義全局變量。2. 局部變量局部變量的名稱必須以標(biāo)記作為前綴聲明局部變量的語句如下：declare variable_name datetype其中variable_name為局部變量的名稱,datetype為數(shù)據(jù)類型例：declare name varchar(8)-聲明一個(gè)存放學(xué)員姓名的變量name,最多可以存儲(chǔ)8個(gè)字符declare seat int聲明一個(gè)存放學(xué)員座位號(hào)的變量seat局部變量的賦值有兩種方

12、法：使用set語句或select語句。語法：set variable_name=value或select variable_name=value3. 全局變量sql server屮的所有全局變量都使用兩個(gè)標(biāo)志作為前綴。全局變量變量含義error最后一個(gè)t-sql錯(cuò)誤的錯(cuò)誤號(hào)identity最后一次插入的標(biāo)識(shí)值language當(dāng)前使用的語言的名稱max connections可以創(chuàng)建的同時(shí)連接的最大數(shù)目rowcount受上一個(gè)sql語句影響的行數(shù)servername本地服務(wù)器的名稱servicename該計(jì)算機(jī)上的sql服務(wù)的名稱timeticks當(dāng)前計(jì)算機(jī)上每刻度的微秒數(shù) transcount

13、當(dāng)前連接打開的事務(wù)數(shù) versionsql server的版本信息4. 輸出語句常用的輸出語句有兩種：print局部變量或字符串select局部變量as自定義列名例：print '服務(wù)器的名稱：'+ servername select servername as '服務(wù)器名稱，5. if-else條件語句語法：if（條件）語句或語句塊else語句或語句塊同java語言一樣,else為可選。如果有多條語句，需要使用語句塊，語句塊使用begin.end表示，其作用類似于java 語言的“”符號(hào)。if（條件）begin語句1語句2endelse6. while循環(huán)語句語法：w

14、hile （條件）語句或語句塊break使用break關(guān)鍵字從最內(nèi)層while循環(huán)中退出。7. case多分支語句語法casewhen條件1 then結(jié)果1when條件2 then結(jié)果2else其他結(jié)果end 注意的是 在case分支語句中后面要加endcase語句表示如果“條件1”成立,則執(zhí)行“結(jié)果1”，其余類推。8.批處理語句在sqlserver “go”就是批處理的標(biāo)志。批處理的主要好處是簡化數(shù)據(jù)庫的管理。 go關(guān)鍵字標(biāo)志著批處理的結(jié)束。第4章高級(jí)查詢1. 簡單子查詢例：select .from 表 1 where 字段 1 > （子查詢）注意：將子查詢和比較運(yùn)算符聯(lián)合使用，必須保

15、證子查詢返回的值不能多于一個(gè)。在用select進(jìn)行查詢時(shí)where條件后盡量不要用“”，前面加“”則不通過索引進(jìn)行查詢。可以將多表間的數(shù)據(jù)組合在一起，替換連接（join）查詢。多表連接查詢都可以用子查詢替換，但反過來說卻不一定,有的子查詢不能用表連接來替換, 子查詢比較靈活、方便、形式多樣，適合于作為查詢的篩選條件，而表連接更適合于查看 多表的數(shù)據(jù)。2. in和not in子查詢使用in和not in進(jìn)行子查詢后而的子查詢可以返回多條記錄。3 exists 和 not exists 子查詢exists可以作為where語句的子查詢，但一般用于訐語句的存在檢測(cè)。基本語法如下: if exists

16、（子查詢）語句如果子查詢的結(jié)果非空,則exists（子查詢）將返冋真（true）,否則返冋假（false）。第5章事務(wù)、索引和視圖1.事務(wù)事務(wù)（transaction）是單個(gè)的工作單元。如果某個(gè)事務(wù)成功，則在該事務(wù)中進(jìn)行的所有數(shù)據(jù) 更改均會(huì)提交，成為數(shù)據(jù)庫中的永久組成部分。如果事務(wù)遇到錯(cuò)誤且必須取消或回滾，則所 有數(shù)據(jù)更改均被清除。transact-sql使用下列語句來管理事務(wù)。開始事務(wù)：begin transactiono提交事務(wù):commit transactiono回滾（撤銷）事務(wù):rollback transaction<>事務(wù)的分類有以下3種：顯式事務(wù)：用begin tr

17、ansaction明確指定事務(wù)的開始（2）隱式事務(wù) （3）自動(dòng)提交事務(wù)例：（銀行轉(zhuǎn)賬系統(tǒng)）begin transaction開始事務(wù)declare errorsum int-泄義變量，用于累訃爭務(wù)執(zhí)彳亍過程中的錯(cuò)誤set errorsum = 0-初始化為0,即無錯(cuò)誤轉(zhuǎn)賬：張三的賬戶少1000元，李四的賬戶多1000元update bank set currentmoney=currentmoney-1000 where customcrnamc二，張三，set errorsum = errorsum+error一累計(jì)是否有錯(cuò)誤update bank set currentmoney = cu

18、rrentmoney+1000 where customername二，李四'set errorsum = error "累計(jì)是否有錯(cuò)誤print，查看轉(zhuǎn)賬事務(wù)過程中的余額if errorsum <>0beginprint'交易失敗，回滾事務(wù)rollback transactionendelsebeginpin廣交易成功，提交事務(wù)，寫入硬盤，永久保存commit transactionend2.索引索引提供指針以指向存儲(chǔ)在表屮指定列的數(shù)據(jù)值，然后根據(jù)指定的排列次序排列這些指 針。索引相當(dāng)于字典中按拼咅或筆畫排序的目錄。索引可以分為3類：（1）唯一索引：不允

19、許兩行具有相同的索引值。（一般情況下大多數(shù)據(jù)庫不允許創(chuàng)建唯一索 引）創(chuàng)建了唯一約朿，將自動(dòng)創(chuàng)建唯一索引，盡管唯一索引有助于找到信息，但為了獲得最 佳性能，最好使用主鍵約束。（2）主鍵約束：在數(shù)據(jù)庫關(guān)系圖屮為表定義一個(gè)主鍵將自動(dòng)創(chuàng)建主鍵索引，主鍵索引是唯 一索引的特殊類型。主鍵索引要求主鍵中的每個(gè)值是唯一的。有利于快速訪問數(shù)據(jù)。（3）聚集索引：在聚集索引屮，表屮各行的物理順序與鍵值的邏輯（索引）順序相同。表 只能包含一個(gè)聚集索引。聚集索引比非聚集索引有更快的數(shù)據(jù)訪問速度。在sql server中，一個(gè)表只能創(chuàng)建一個(gè)聚集索引，但可以有多個(gè)非聚集索引，設(shè)置某列為 主鍵，該列就默認(rèn)為聚集索引。uniq

20、ue （唯一索引）、clustered （聚集索引）使用t-sql語句創(chuàng)建索引語法：create unique clustered | nonclusteredl index index_nameon table_name （column_name,coumn_namel.）withfillfactor 二 x其中：unique指定唯一索引,可選。clusterednonclustered指定是聚集索引還是非聚集索引，可選。fillfactor表示填充因子，指定一個(gè)0100的值，該值指示索引頁填滿的空間所占的百分比。例：/*筆試列創(chuàng)建非聚集索引*/create non clustered in

21、 dex ix_stumarks_writtenexamon stumarks（writtenexam）with fill factor = 30運(yùn)用索引進(jìn)行查詢select * from stumarks （index=ix_stumarks_writtenexam） where writtenexam between 1 and 33 使用索引可以加快數(shù)據(jù)檢索速度。創(chuàng)建索引要用在（1）該列頻繁搜索（2）該列用于對(duì)數(shù)據(jù)進(jìn)行排序3.視圖視圖是保存在數(shù)據(jù)庫屮的select查詢。是一張?zhí)摂M的表。對(duì)查詢執(zhí)行的大多數(shù)操作也可在視圖上進(jìn)行。使用視圖的原因有2：（1）出于安全考慮。用戶不必看到整個(gè)數(shù)據(jù)庫結(jié)

22、構(gòu)，而隱藏部分?jǐn)?shù)據(jù)；（2）符合用戶日常業(yè)務(wù)邏輯，使他們對(duì)數(shù)據(jù)更容易理解。使用t-sql語句創(chuàng)建視圖語法如下：create view view_nameas<select 語句例：if exists（select name from sysobjects where name=iew_stuinfo_stumarks,）drop view view_stuinfo_stumarksgocreate view view_stuinfo_stumarksasselect 姓名=sluname,學(xué)號(hào)=stuinfo.stuno,筆試成績=wrillenexam,機(jī)試成績=labexam, 平均分

23、=（writtenexam+labexam）/2 from stulnfoleft join stumarks on stulnfo.stuno = stumarks.stunogo嚴(yán)使用視圖：視圖是一個(gè)虛擬表，可以像物理表一樣打開*/ select * from view stulnfo stumarks第6章存儲(chǔ)過程1. 存儲(chǔ)過程存儲(chǔ)過程類似于java語言中的方法。存儲(chǔ)過程可包含邏輯控制語句和數(shù)據(jù)操作語句，它可以接收參數(shù)、輸出參數(shù)、返回單個(gè) 或多個(gè)結(jié)果集以及返回值。sql server中的存儲(chǔ)過程有：（1）接收輸入?yún)?shù)，并向調(diào)用過程或語句返回值（2）包含在數(shù)據(jù)庫中執(zhí)行操作或調(diào)用其他存儲(chǔ)過程

24、的編程語句。（3）向調(diào)用過程返回狀態(tài)值，指示執(zhí)行過程是否成功。存儲(chǔ)過程的優(yōu)點(diǎn)（1）允許模塊化程序設(shè)計(jì)只需創(chuàng)建一次存儲(chǔ)過程并將其存儲(chǔ)在數(shù)據(jù)庫中，以后即可在程序中調(diào)用該過程任意次。（2）允許更快的執(zhí)行（3）減少網(wǎng)絡(luò)流量（4）可作為安全機(jī)制使用2. 常用的系統(tǒng)存儲(chǔ)過程所有系統(tǒng)存儲(chǔ)過程的名稱都以“sp_”開頭，并存放在master數(shù)據(jù)庫中。常用的系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程說明sp databases列出服務(wù)器上的所有數(shù)據(jù)庫sp.helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp renamedb更改數(shù)據(jù)庫的名稱sp tables返冋當(dāng)前環(huán)境下可查詢的對(duì)象的列表sp colu runs返回某個(gè)表列的信息

25、sp help查看某個(gè)表的所有信息sp.helpconstraint查看某個(gè)表的約束sp helpindex查看某個(gè)表的索引sp stored procedures列出當(dāng)前環(huán)境中的所有存儲(chǔ)過程sp.password添加或修改登錄賬戶的密碼sp_helptext顯示默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義的存儲(chǔ)過程、觸發(fā)器或視圖的實(shí) 際文本還有一個(gè)常用的擴(kuò)展存儲(chǔ)過程：xp_cmdshellc它可以完成dos命令下的一些操作,語法如下:exec xp_cmdshell dos 命令no_oulputl3創(chuàng)建不帶參數(shù)的存儲(chǔ)過程使用t-sql語句創(chuàng)建存儲(chǔ)過程的語法：create procedure存儲(chǔ)過程名

26、參數(shù)1數(shù)據(jù)類型二默認(rèn)值output,參數(shù)n數(shù)據(jù)類型二默認(rèn)值oulpulassql語句例：if exists(select * from sysobjects where name=,proc_stif)drop procedure proc_stugocreate procedure proc.stuasdeclare writtenavg float,labavg float-筆試平均風(fēng)和機(jī)試平均分變量select writtenavg=avg(writtenexam), labavg=avg(labexam) from stumarks print '筆試平均分：,+convert

27、(varchar(5), writtenavg)print，機(jī)試平均分：,+convert(varchar(5), labavg)if(writtenavg>70 and labavg >70)print<本班考試成績：優(yōu)秀elseprint，本班考試成績：較差，print''print，參加考試沒有通過的學(xué)員：select stuname.stuinfo.stunowrittenexamjabexam from stulnfoinner join stumarks on stuinfo.stuno = stumarks.stunowhere writtene

28、xam<60 or labexam <60go/咅調(diào)用執(zhí)行存儲(chǔ)過程*/exec proc_stu4.創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程存儲(chǔ)過程的參數(shù)有兩種：(1) 輸入?yún)?shù)：可以在調(diào)用時(shí)向存儲(chǔ)過程傳遞參數(shù)，此類參數(shù)可用來在存儲(chǔ)過程屮傳入值。(2) 輸出參數(shù)：如果希望返回值，則可以使用輸出參數(shù)，輸11!參數(shù)后有“output”標(biāo)記， 執(zhí)行存儲(chǔ)過程后，將把返回值存放在輸出參數(shù)中，可供其他t-sql語句讀収訪問。語法：create procfedure存儲(chǔ)過程名參數(shù)1數(shù)據(jù)類型二默認(rèn)值output, 9參數(shù)n數(shù)據(jù)類型二默認(rèn)值output1assql語句如果參數(shù)后面有“output”關(guān)鍵字，表示此參數(shù)

29、為輸出參數(shù)，否則視為普通輸入?yún)?shù), 輸入?yún)?shù)還可以設(shè)置默認(rèn)值。例：if exists(selecl * from sysobjects where name=,proc_stu,)drop procedure proc_stugocreate procedure proc_stuwrittenpass int = 60,labpass int = 60as-注意聲明的變量要放在as前print '筆試及格線：+conveil(varchar(5), writtenpass) print '機(jī)試及格線：'+convert(varchar(5), labpass) prin

30、t4'prin參加考試沒有通過的學(xué)員：，select stuname,stuinfo.stiino,writtenexam,labexam from stulnfo inner join stumarks on stulnfo.stuno = stumarks.stuno where writtenexam<writtenpass or labexam <labpass go/*調(diào)用哪個(gè)存儲(chǔ)過程*/exec proc_stu -都采用默認(rèn)值，筆試和機(jī)試及格線都為60exec proc_stu 64機(jī)試采用默認(rèn)：筆試及格線64分，機(jī)試及格線55分exec proc_stu 6

31、0,55都不采用默認(rèn)：筆試及格線60分，機(jī)試及格線55分5. 創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程需要使用輸出(oulpul)參數(shù)。例：if exists(select * from sysobjects where name=,proc_stu,)drop procedure proc_stugocreate procedure proc_stunotpasssum int output, output關(guān)鍵字，否則視為輸入?yún)?shù) writtenpass int = 60,labpass int = 60as-注意聲明的變暈要放在as前print '筆試及格線：,+convert(varchar(5), writtenpass)print '機(jī)試及格線：5 +co