北京大學(xué)青鳥S2sql數(shù)據(jù)庫設(shè)計和高級查詢總結(jié)

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

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

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

4、字段名 數(shù)據(jù)類型 列的特征如 stuid int identity(1,1) not null -identity 表示是否為自動增長)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 默認約束(default constraint) alter table 表名 add constraint 約束名(df_*) default(默認值) for 約束的字段5 外建約束(foreign key constraint) alter table 表名 add constraint 約束名(fk_*) foreign key(約束的字段) references 主表的表名(主表的字段)總結(jié)create database 建庫create table 建表add constraint 加約束drop datab

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

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

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

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

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

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

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

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

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

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

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

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