北京大學(xué)青鳥(niǎo)S2sql數(shù)據(jù)庫(kù)設(shè)計(jì)和高級(jí)查詢(xún)總結(jié)

1、第一章 數(shù)據(jù)庫(kù)的設(shè)計(jì)良好的數(shù)據(jù)庫(kù)設(shè)計(jì)1、節(jié)省數(shù)據(jù)庫(kù)的存儲(chǔ)空間2、能夠保證數(shù)據(jù)的完整性3、方便進(jìn)行數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的開(kāi)發(fā)糟糕的數(shù)據(jù)庫(kù)設(shè)計(jì)1、效率低下2、更新和檢索數(shù)據(jù)時(shí)會(huì)出現(xiàn)許多問(wèn)題設(shè)計(jì)數(shù)據(jù)庫(kù)的步驟1、需求分析階段 分析客戶(hù)的業(yè)務(wù)和數(shù)據(jù)處理需求 收集信息 標(biāo)識(shí)對(duì)象(實(shí)體) 標(biāo)識(shí)每個(gè)對(duì)象需要存儲(chǔ)的詳細(xì)信息 標(biāo)識(shí)對(duì)象之間的關(guān)系2、概要設(shè)計(jì)階段 繪制數(shù)據(jù)庫(kù)的E-R模型圖(實(shí)體關(guān)系圖)3、詳細(xì)設(shè)計(jì)階段 將E-R圖轉(zhuǎn)換為多表,進(jìn)行邏輯設(shè)計(jì),并應(yīng)用數(shù)據(jù)庫(kù)設(shè)計(jì)的三大規(guī)進(jìn)行設(shè)計(jì)4、代碼編寫(xiě)階段映射基數(shù)1一對(duì)一2一對(duì)多3多對(duì)一4多對(duì)多實(shí)體關(guān)系圖矩形表示實(shí)體集橢圓形表示屬性菱形表示關(guān)系集直線(xiàn)用來(lái)連接設(shè)計(jì)數(shù)據(jù)庫(kù)問(wèn)題1

2、信息重復(fù)2 更新異常3 插入異常(無(wú)法表示某些信息)4 刪除異常(丟失有用的信息)數(shù)據(jù)庫(kù)設(shè)計(jì)的三大規(guī)理論一 第一式的目標(biāo)是確保 每列的原子性二 第二式的目標(biāo)是確保 表中的每列都和主鍵相關(guān)三 第三式的目標(biāo)是確保 每列都和主鍵直接相關(guān),而不是間接相關(guān)(不能傳遞依賴(lài))規(guī)化和性能的關(guān)系在必要的情況下 允許適當(dāng)?shù)臄?shù)據(jù)冗余第二章 數(shù)據(jù)庫(kù)的實(shí)現(xiàn)一 建庫(kù)ues master goif exists (select * from sysdatabases where name =數(shù)據(jù)庫(kù)名字)drop database 數(shù)據(jù)庫(kù)名 -判斷這個(gè)數(shù)據(jù)庫(kù)名字是否存在,如果存在 刪除create database 數(shù)據(jù)庫(kù)名

3、on primary( name=, filename=, size =, 建立數(shù)據(jù)庫(kù)的主文件 如果需要建立次要文件(ndf) 用逗號(hào)隔開(kāi) maxsize =, filegrowth =)log on ( name =, filename=, size =, 建立日志文件 如果建立多個(gè)日志文件 在后面用逗號(hào)隔開(kāi) maxsize =, filegrowth =)go - 批處理 二 建表ues 數(shù)據(jù)庫(kù)名字go if exists (select * from sysobjects where name =表名)drop table 表名 -判斷是否有這個(gè)名字的表create table 表名(

4、字段名 數(shù)據(jù)類(lèi)型 列的特征如 stuid int identity(1,1) not null -identity 表示是否為自動(dòng)增長(zhǎng))go三 加約束1 主鍵約束(primary key constraint) -constraint 約束 alter table 表名 add constraint 約束名(PK_*) primary key(約束的字段)2 唯一約束(unique constraint) alter table 表名 add constraint 約束名(uq_*) unique(約束的字段)3 檢查約束(check constraint) alter table 表名 ad

5、d constraint 約束名(ck_*) check(約束的條件)4 默認(rèn)約束(default constraint) alter table 表名 add constraint 約束名(df_*) default(默認(rèn)值) for 約束的字段5 外建約束(foreign key constraint) alter table 表名 add constraint 約束名(fk_*) foreign key(約束的字段) references 主表的表名(主表的字段)總結(jié)create database 建庫(kù)create table 建表add constraint 加約束drop datab

6、ase 刪庫(kù)drop table 刪表drop constraint 刪約束 use master select * from sysdatabases 判斷是否有庫(kù)名select * from sysobjects 判斷是否有表名完整性1 實(shí)體完整性(保證數(shù)據(jù)是唯一的如主鍵唯一鍵標(biāo)示列)2 引用完整性(保證兩表數(shù)據(jù)一致如外鍵)3 域完整性(保證數(shù)據(jù)的準(zhǔn)確性如檢查約束默認(rèn)約束非空)三層安全模型1 登陸- 決定登陸服務(wù) windows 身份驗(yàn)證 exec sp_grantlogin windows域名域 sql 身份驗(yàn)證 exec sp_addlogin 名,密碼2 數(shù)據(jù)庫(kù)用戶(hù)-訪(fǎng)問(wèn)數(shù)據(jù)庫(kù) us

7、e 數(shù)據(jù)庫(kù)名 go exec sp_grantdbaccess 登陸,(數(shù)據(jù)庫(kù)用戶(hù)) 如果不寫(xiě)數(shù)據(jù)庫(kù)用戶(hù)默認(rèn)為登陸名3 權(quán)限 -在數(shù)據(jù)庫(kù)里的操作 use 數(shù)據(jù)庫(kù)名 go grant 權(quán)限(增,刪,改,查,建表(create table) on 表名 to 用戶(hù)名 第三章 T-SQL編成使用變量一局部變量 declare 變量名 類(lèi)型 賦值: 1、 set 變量名 = 值 2、 select 變量名 =值二全局變量 error 最后一個(gè)T-SQL錯(cuò)誤的錯(cuò)誤號(hào) identity 最后一次插入的標(biāo)示值 language 當(dāng)前使用的語(yǔ)言的名稱(chēng) max_connections 可以創(chuàng)建的同時(shí)連接的最大數(shù)

8、目 rowcount 受上一個(gè)sql語(yǔ)句影響的行數(shù) servername 本地服務(wù)器的名稱(chēng) servicename 該計(jì)算機(jī)上的sql服務(wù)的名稱(chēng) timeticks 當(dāng)前計(jì)算機(jī)上每刻度的微秒數(shù) transcount 當(dāng)前連接打開(kāi)的事物數(shù) version sql server 的版本信息輸出語(yǔ)句 1、print 局部變量或字符串 2、select 局部變量 as 自定義列名(查詢(xún)語(yǔ)句的特殊應(yīng)用) 邏輯控制語(yǔ)句1、if(條件) begin - 開(kāi)頭 語(yǔ)句 end -結(jié)束else -為可選 begin - 開(kāi)頭 語(yǔ)句 end -結(jié)束2、while (條件) 語(yǔ)句 break3、case when 條

9、件1 then 結(jié)果1 when 條件2 then 結(jié)果2 else 其他結(jié)果end 批處理語(yǔ)句 以一條命令的方式來(lái)處理一組命令的過(guò)程稱(chēng)為批處理批處理的好處就是能夠簡(jiǎn)化數(shù)據(jù)庫(kù)的管理第四章 高級(jí)查詢(xún)一 簡(jiǎn)單子查詢(xún)select * from 表1 where 字段1 (子查詢(xún)) 它等于一個(gè)等值連接將子查詢(xún)和比較運(yùn)算符聯(lián)合使用，必須保證子查詢(xún)返回的值不能多與一個(gè)二 in 和 not in 子查詢(xún)in 后面的子查詢(xún)可以返回多條記錄select * from 表1 where 字段1 in(not in)(子查詢(xún))三 exists not exists子查詢(xún) if exists (子查詢(xún)) 語(yǔ)句 如果子

10、查詢(xún)的結(jié)果非空，則exists(子查詢(xún)) 將返回真(true) ,否則返回假(false)第五章 事務(wù)、索引和試圖一 事務(wù)：是一個(gè)整體，要么都成功，要么都失敗事務(wù)時(shí)作為單個(gè)邏輯工作單元執(zhí)行的一系列操作。一個(gè)邏輯工作單元必須有4個(gè)屬性1 原子性(atomicity) 事務(wù)是一個(gè)完整的操作。事務(wù)的各元素是不可分得2 一致性(consistency) 當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致?tīng)顟B(tài)3 隔離性(isolation) 對(duì)數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，這表明事務(wù)必須是獨(dú)立的， 它不應(yīng)以任何方式依賴(lài)于或影響其它事務(wù)4 持久性(durability) 事務(wù)完成之后，它對(duì)系統(tǒng)的影響是永久的簡(jiǎn)稱(chēng) A

11、CID二 如何創(chuàng)建事務(wù)開(kāi)始事務(wù)：begin transaction提交事務(wù)：commit transaction回滾(撤銷(xiāo))事務(wù)： rollback transaction事務(wù)的分類(lèi)有3種1 顯示事務(wù): 用 begin transaction 明確指定事務(wù)的開(kāi)始2 隱式事務(wù): 通過(guò)設(shè)置 set implicit_transaction on 語(yǔ)句,將隱式事務(wù)模式設(shè)置為打開(kāi)3 自動(dòng)提交事務(wù): 這是sql server 的默認(rèn)模式 它將每條單獨(dú)的T-SQL語(yǔ)句視為一個(gè)事務(wù). 如果成功執(zhí)行,則自動(dòng)提交.如果錯(cuò)誤,則自動(dòng)回滾.在這里用的了全局變量errordeclare errorsum int se

12、t errorsum =0 語(yǔ) 句 set errorsum =errorsum+error三 什么是索引索引,它是sql server編排數(shù)據(jù)的部方法,相當(dāng)于字典中的目錄索引頁(yè),數(shù)據(jù)庫(kù)中存儲(chǔ)索引的數(shù)據(jù)頁(yè) 通過(guò)索引可以大大提高數(shù)據(jù)庫(kù)的檢索速度,改善數(shù)據(jù)庫(kù)性能 加快查詢(xún)的速度(通過(guò)平衡二叉樹(shù)) 增刪改 速度慢,所需要的空間大索引可分為3類(lèi)1 唯一索引: 唯一索引不允許兩行具有相同的索引值. 創(chuàng)建了唯一約束,將自動(dòng)創(chuàng)建唯一索引,為了最佳性能,建議使用主鍵的約束2 主鍵索引: 在數(shù)據(jù)庫(kù)關(guān)系圖中為表定義一個(gè)主鍵將自動(dòng)創(chuàng)建主鍵索引,主鍵索引是唯一索引的特殊類(lèi)型.3 聚集索引: 在聚集索引中,表中各行的物

13、理順序與健值的邏輯(索引)順序相同. 非聚集索引: 表中各行的物理順序與鍵值的邏輯順序不匹配.在sql server中,一個(gè)表只能 創(chuàng)建一個(gè)聚集索引,但可以有多個(gè)非聚集索引,設(shè)置某列為主鍵,該列就默認(rèn)為聚集索引四 建立索引create uniqueclusterednonclustered index ix_(name)on 表名 (字段名)with fillfactor =30 1 unique 指定唯一索引 可選2 clustered nonclustered 指定是聚集索引還是非聚集索引,可選3 fillfactor 表示填充因子,指定0-100的值,該值指示索引頁(yè)填滿(mǎn)的空間所占的比例

14、(百分比越小,所留得空白空間越大)什么情況下可以建立索引1 該列用于頻繁搜索2 該列用于對(duì)數(shù)據(jù)進(jìn)行排序 請(qǐng)不要對(duì)下面的列創(chuàng)建索引1 列中僅包含幾個(gè)不同的值2 表中僅包含幾行.五 什么是試圖試圖使另一種查看數(shù)據(jù)庫(kù)中一個(gè)或多個(gè)表中的數(shù)據(jù)的方法.它是一種虛擬表試圖通常用來(lái)進(jìn)行以下三種操作1 篩選表中的行2 防止未經(jīng)許可的用戶(hù)訪(fǎng)問(wèn)敏感數(shù)據(jù)3 將多個(gè)物理數(shù)據(jù)表抽象為一個(gè)邏輯數(shù)據(jù)表 (降低數(shù)據(jù)庫(kù)的復(fù)雜程度)好處1對(duì)最終用戶(hù)的好處 結(jié)果更容易理解 獲得數(shù)據(jù)更容易2對(duì)開(kāi)發(fā)人員的好處 限制數(shù)據(jù)檢索更容易 維護(hù)應(yīng)用程序更方便六 如何創(chuàng)建試圖create view view_(name)as (select 語(yǔ)句)

15、 第六章 存儲(chǔ)過(guò)程一 什么是存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程(procedure)類(lèi)似于java語(yǔ)言中的方法，它是sql語(yǔ)句和控制流語(yǔ)句的預(yù)編譯集合。 存儲(chǔ)過(guò)程的優(yōu)點(diǎn) 1 允許模塊化程序設(shè)計(jì) 2 允許更快地執(zhí)行 3 減少網(wǎng)絡(luò)流量 4 可作為安全機(jī)制使用 存儲(chǔ)過(guò)程分為以下兩類(lèi) 1 系統(tǒng)存儲(chǔ)過(guò)程 2 用戶(hù)自定義的存儲(chǔ)過(guò)程 3 擴(kuò)展存儲(chǔ)過(guò)程(系統(tǒng)定義好的，以xp_開(kāi)頭)二 常用的系統(tǒng)存儲(chǔ)過(guò)程 所有的系統(tǒng)存儲(chǔ)過(guò)程的名稱(chēng)都以sp_開(kāi)頭 sp_databases (列出服務(wù)器上的所有數(shù)據(jù)庫(kù)) sp_helpdb (報(bào)告有關(guān)指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的信息) sp_renamedb (更改數(shù)據(jù)庫(kù)的名稱(chēng)) sp_tables

16、(返回當(dāng)前環(huán)境下可查詢(xún)的對(duì)象的列表) sp_columns (返回某個(gè)表列的信息) sp_help (查看某個(gè)表的所有信息) sp_helpconstraint (查看某個(gè)表的約束) sp_stored_procedures (列出當(dāng)前環(huán)境中的所有存儲(chǔ)過(guò)程) sp_password (添加或修改登陸的密碼) sp_helptext (顯示默認(rèn)值、未加密的存儲(chǔ)過(guò)程、用戶(hù)定義的存儲(chǔ)過(guò)程、觸發(fā)器或試圖的實(shí)際文本) 擴(kuò)展參數(shù) exec xp_cmdshell dos命令 no_output三 用戶(hù)定義的存儲(chǔ)過(guò)程1 創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程 create procedure 存儲(chǔ)過(guò)程名 參數(shù)1 數(shù)據(jù)類(lèi)型

17、默認(rèn)值 output 參數(shù)2 數(shù)據(jù)類(lèi)型 默認(rèn)值 output as sql 語(yǔ)句2 創(chuàng)建步帶參數(shù)的存儲(chǔ)過(guò)程 1 輸入?yún)?shù) 可以在調(diào)用時(shí)向存儲(chǔ)過(guò)程傳遞參數(shù)，此類(lèi)參數(shù)可以用來(lái)在存儲(chǔ)過(guò)程中傳入值 2 輸出參數(shù)(引用傳遞) 如果希望返回值，則可以使用輸出參數(shù)，輸出參數(shù)后有output標(biāo)記，執(zhí)行存儲(chǔ)過(guò)程后， 將把返回值存放在輸出參數(shù)中，可供其它T-SQL語(yǔ)句讀取訪(fǎng)問(wèn)3 創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過(guò)程 如果希望調(diào)用存儲(chǔ)過(guò)程后，返回一個(gè)或多個(gè)值，這時(shí)需要使用輸出(output)參數(shù)。 使用輸出參數(shù)創(chuàng)建存儲(chǔ)過(guò)程時(shí)，在參數(shù)后面需要跟隨output關(guān)鍵字，調(diào)用時(shí)也需要在變量后跟隨output關(guān)鍵字四 處理錯(cuò)誤信息 如果存儲(chǔ)過(guò)程變得越來(lái)越復(fù)雜，則需要在存儲(chǔ)過(guò)程中加入錯(cuò)誤檢查語(yǔ)句 raiserror (msg_id|msg_str,severity,statewith opt