第一章 數(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、需

第一章 數(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 信息重復(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

go

if exists (select * from sysdatabases where name ='數(shù)據(jù)庫名字')

drop database 數(shù)據(jù)庫名 --判斷這個數(shù)據(jù)庫名字是否存在, 如果存在 刪除 create database 數(shù)據(jù)庫名

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 表名

(

字段名 數(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 表名

add 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 database 刪庫

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ù)庫

use 數(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ù)目 @@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 (條件)

語句

[break]

3、

case

when 條件1 then 結(jié)果1

when 條件2 then 結(jié)果2

[else 其他結(jié)果]

end

批處理語句

以一條命令的方式來處理一組命令的過程稱為批處理

批處理的好處就是能夠簡化數(shù)據(jù)庫的管理

第四章 高級查詢

一 簡單子查詢

select *** from 表1 where 字段1 >(子查詢) 它等于一個等值內(nèi)連接

將子查詢和比較運算符聯(lián)合使用，必須保證子查詢返回的值不能多與一個

二 in 和 not in 子查詢

in 后面的子查詢可以返回多條記錄

select *** from 表1 where 字段1 in(not in)(子查詢)

三 exists not exists子查詢

if exists (子查詢)

語句

如果子查詢的結(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)的影響是永久的

簡稱 ACID

二 如何創(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í)行, 則自動提交. 如果錯誤, 則自動回滾.

在這里用的了全局變量@@error

declare @errorsum int

set @errorsum =0

語 句

set @errorsum =@errorsum @@error

三 什么是索引

索引, 它是sql server編排數(shù)據(jù)的內(nèi)部方法, 相當于字典中的目錄

索引頁, 數(shù)據(jù)庫中存儲索引的數(shù)據(jù)頁

通過索引可以大大提高數(shù)據(jù)庫的檢索速度, 改善數(shù)據(jù)庫性能

加快查詢的速度(通過平衡二叉樹)

增刪改 速度慢, 所需要的空間大

索引可分為3類

1 唯一索引: 唯一索引不允許兩行具有相同的索引值.

創(chuàng)建了唯一約束, 將自動創(chuàng)建唯一索引, 為了最佳性能, 建議使用主鍵的約束

2 主鍵索引: 在數(shù)據(jù)庫關(guān)系圖中為表定義一個主鍵將自動創(chuàng)建主鍵索引, 主鍵索引是唯一索引的特殊類型.

3 聚集索引: 在聚集索引中, 表中各行的物理順序與健值的邏輯(索引) 順序相同.

非聚集索引: 表中各行的物理順序與鍵值的邏輯順序不匹配.

在sql server 中, 一個表只能 創(chuàng)建一個聚集索引, 但可以有多個非聚集索引, 設(shè)置某列為主鍵, 該列就默認為聚集索引

四 建立索引

create [unique][clustered][nonclustered] index ix_(name)

on 表名 (字段名)

with fillfactor =30

1 unique 指定唯一索引 可選

2 clustered nonclustered 指定是聚集索引還是非聚集索引, 可選

3 fillfactor 表示填充因子, 指定0--100的值, 該值指示索引頁填滿的空間所占的比例 (百分比越小, 所留得空白空間越大)

什么情況下可以建立索引

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 語句)

第六章 存儲過程

一 什么是存儲過程

存儲過程(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 (返回當前環(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 proc[edure] 存儲過程名

[

@參數(shù)1 數(shù)據(jù)類型 [默認值] [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,state}[with option[,...n]])

例 raiserror('及格線錯誤，請指定0-100之間的分數(shù)，統(tǒng)計中斷退出',16,1)

msg_id 在sysmessages 系統(tǒng)表中指定的用戶定義錯誤信息

msg_str 用戶定義的特定信息，最長255個字符

severity 與特定信息相關(guān)聯(lián)，表示用戶定義的嚴重性級別。用戶可使用的級別為0-18級(一般下為16)

19-25級是為sysadmin 固定角色的成員預(yù)留的，并需要指定with log選項 20-25

為致命錯誤

state 表示錯誤的狀態(tài)，是1-127的值

option 指示是否將錯誤記錄到服務(wù)器錯誤日至中