考前的一些復(fù)習(xí)整理葫笼，雖然考試考的會很淺深啤，但難得有機會認真的復(fù)習(xí)一下數(shù)據(jù)庫內(nèi)容……
游標(biāo) cursor
DECLARE cursor_name CURSOR FOR SELECT ...
OPEN cursor_name
FETCH FROM cursor_name INTO @v_1,@v_2...
WHILE @@FETCH_STATUS=0
失敗返回-1
沒有找到記錄返回100
CLOSE cursor_name
DEALLOCATE cursor_name
存儲過程
CREATE PROCEDURE proc_name
@input ='default',@input...
AS
SELECT ...
RETURN @output ...
DECLARE @in INT ...
EXECUTE @in = proc_name '','',...
EXECUTE @in = proc_name @input = '',...
如果是OUTPUT參數(shù)的話調(diào)用PROC時需要傳入數(shù)據(jù)對象
DECLARE @in INT, @avg INT
EXECUTE @in = proc_name @input = ' ',@avg = @avg OUTPUT
返回游標(biāo)
CREATE PROCDURE proc_name
@cursor_out CURSOR OUTPUT
AS
SET @cursor_out = CURSOR FOR
sql_statement
OPEN cursor_out
觸發(fā)器
CREATE TRIGGER trigger_name ON
table_name
FOR/INSTEAD OF
INSERT/UPDATE/DELETE
sql_statement
動態(tài)SQL
DECLARE cursor_name DYNAMIC CURSOR FOR SQLSA
PREPARE SQLSA FROM @sql_statement --通常為字符串變量
OPEN DYNAMIC cursor_name USING @parameter
數(shù)據(jù)庫安全
訪問控制
- 用戶管理
- 角色管理
- 權(quán)限管理
- 數(shù)據(jù)加密
存取控制
- 定義用戶權(quán)限 被稱為安全規(guī)則或授權(quán)規(guī)則
- 合法權(quán)限檢查 用戶發(fā)出請求后，查找數(shù)據(jù)字典通過安全規(guī)則進行檢查路星，如果超出權(quán)限則拒絕
用戶角色權(quán)限
- 角色成為用戶的成員
- 權(quán)限可以授權(quán)給用戶溯街，也可以授權(quán)給角色
自主存取控制
- 用戶對于不同的數(shù)據(jù)對象（表）有不同的操作權(quán)限
- 不同的用戶對于同一對象也有不同的操作權(quán)限，用戶可以將其存取權(quán)限轉(zhuǎn)授
強制存取控制
每一個數(shù)據(jù)對象被標(biāo)以密級洋丐，每個用戶被許可相應(yīng)的級別
建立登錄用戶
CREATE LOGIN zhang WITH PASSWORD = '....' {[ MUST_CHANGE ]
[,DEFAULT_DATABASE = database ]
[, CHECK_EXPIRATION = { ON | OFF}]}
盡管指定了默認數(shù)據(jù)庫苫幢，但是在成為該數(shù)據(jù)庫用戶前還是無法USE該數(shù)據(jù)庫。
修改用戶
ALTER LOGIN zhang DISABLE/ENABLE
- 修改口令
管理員：ALTER LOGIN zhang WITH PASSWORD='bistu'
用戶自己：ALTER LOGIN zhang WITH PASSWORD='mis' OLD_PASSWORD='bistu'
刪除登錄用戶
DROP LOGIN login_name
數(shù)據(jù)庫用戶
CREATE USER user_name [LOGIN login_name]
[ WITH DEFAULT_SCHEMA = schema_name ]
默認架構(gòu)即訪問數(shù)據(jù)庫對象時可以省略架構(gòu)名
USE 倉儲訂貨
CREATE USER zhang WITH DEFAULT_SCHEMA =倉儲
沒有指定login則自動映射到同名login
在創(chuàng)建前USE數(shù)據(jù)庫垫挨，指定默認架構(gòu)韩肝，此時zhang成為了數(shù)據(jù)庫用戶，但在沒有得到操作授權(quán)前不能進行查詢和操作九榔。
游標(biāo) cursor
DECLARE cursor_name CURSOR FOR SELECT ...
OPEN cursor_name
FETCH FROM cursor_name INTO @v_1,@v_2...
WHILE @@FETCH_STATUS=0
--失敗返回-1
--沒有找到記錄返回100
CLOSE cursor_name
DEALLOCATE cursor_name
存儲過程
CREATE PROCEDURE proc_name
@input ='default',@input...
AS
SELECT ...
RETURN @output ...
DECLARE @in INT ...
EXECUTE @in = proc_name '','',...
EXECUTE @in = proc_name @input = '',...
如果是OUTPUT參數(shù)的話調(diào)用PROC時需要傳入數(shù)據(jù)對象
DECLARE @in INT, @avg INT
EXECUTE @in = proc_name @input = ' ',@avg = @avg OUTPUT
返回游標(biāo)
CREATE PROCDURE proc_name
@cursor_out CURSOR OUTPUT
AS
SET @cursor_out = CURSOR FOR
sql_statement
OPEN cursor_out
觸發(fā)器
CREATE TRIGGER trigger_name ON
table_name
FOR/INSTEAD OF
INSERT/UPDATE/DELETE
sql_statement
動態(tài)SQL
DECLARE cursor_name DYNAMIC CURSOR FOR SQLSA
PREPARE SQLSA FROM @sql_statement --通常為字符串變量
OPEN DYNAMIC cursor_name USING @parameter
數(shù)據(jù)庫安全
訪問控制
- 用戶管理
- 角色管理
- 權(quán)限管理
數(shù)據(jù)加密
存取控制
- 定義用戶權(quán)限 被稱為安全規(guī)則或授權(quán)規(guī)則
- 合法權(quán)限檢查 用戶發(fā)出請求后哀峻，查找數(shù)據(jù)字典通過安全規(guī)則進行檢查涡相，如果超出權(quán)限則拒絕
用戶角色權(quán)限
- 角色成為用戶的成員
- 權(quán)限可以授權(quán)給用戶，也可以授權(quán)給角色
自主存取控制
- 用戶對于不同的數(shù)據(jù)對象（表）有不同的操作權(quán)限
- 不同的用戶對于同一對象也有不同的操作權(quán)限剩蟀，用戶可以將其存取權(quán)限轉(zhuǎn)授
強制存取控制
每一個數(shù)據(jù)對象被標(biāo)以密級催蝗，每個用戶被許可相應(yīng)的級別
建立登錄用戶
CREATE LOGIN zhang WITH PASSWORD = '....' {[ MUST_CHANGE ]
[,DEFAULT_DATABASE = database ]
[, CHECK_EXPIRATION = { ON | OFF}]}
盡管指定了默認數(shù)據(jù)庫，但是在成為該數(shù)據(jù)庫用戶前還是無法USE該數(shù)據(jù)庫育特。
修改用戶
ALTER LOGIN zhang DISABLE/ENABLE
修改口令
- 管理員：
ALTER LOGIN zhang WITH PASSWORD='bistu' - 用戶自己：
ALTER LOGIN zhang WITH PASSWORD='mis' OLD_PASSWORD='bistu'
刪除登錄用戶
DROP LOGIN login_name
數(shù)據(jù)庫用戶
CREATE USER user_name [LOGIN login_name]
[ WITH DEFAULT_SCHEMA = schema_name ]
默認架構(gòu)即訪問數(shù)據(jù)庫對象時可以省略架構(gòu)名
USE 倉儲訂貨
CREATE USER zhang WITH DEFAULT_SCHEMA =倉儲
沒有指定login則自動映射到同名login
在創(chuàng)建前USE數(shù)據(jù)庫丙号，指定默認架構(gòu)，此時zhang成為了數(shù)據(jù)庫用戶缰冤，但在沒有得到操作授權(quán)前不能進行查詢和操作犬缨。
數(shù)據(jù)庫用戶分類
- 系統(tǒng)管理員
- 數(shù)據(jù)庫管理員
- 數(shù)據(jù)庫對象用戶 創(chuàng)建表、視圖等
- 數(shù)據(jù)庫訪問用戶 可以操作被授權(quán)的數(shù)據(jù)庫對象（表棉浸、視圖等）
public角色
- 每個數(shù)據(jù)庫（包括所有系統(tǒng)數(shù)據(jù)庫和所有用戶數(shù)據(jù)庫）都有public角色
每個數(shù)據(jù)庫用戶都自動是public角色的成員
public角色的權(quán)限是數(shù)據(jù)庫中所有用戶的默認權(quán)限
或者說每個用戶都擁有的權(quán)限應(yīng)該來自public角色
系統(tǒng)初始時public角色幾乎沒有任何默認的權(quán)限
管理員應(yīng)該將所有用戶擁有的權(quán)限授予public角色
創(chuàng)建角色
CREATE ROLE role_name [ AUTHORIZATION owner_name ]
以sa登錄后直接執(zhí)行如下命令：
USE 倉儲訂貨
CREATE ROLE manager
指定角色
sp_addrolemember @rolename='manager',@membername='huang'
或
sp_addrolemember 'manager','huang'
取消角色
sp_droprolemember @rolename='manager',@membername='huang'
修改角色名稱命令
ALTER ROLE role_name WITH NAME = new_name
例：將角色manager的名稱修改為orders_manager 怀薛。
ALTER ROLE manager WITH NAME = orders_manager
授予權(quán)限-對象權(quán)限
GRANT SELECT ON 基礎(chǔ).職工(職工號,倉庫號,姓名,班組長) TO public
GRANT INSERT,UPDATE(經(jīng)手人,供貨方,訂購日期,金額),DELETE
ON 訂貨.訂購單 TO order_man
WITH GRANT OPTION
GRANT ... ON A.B TO ... AS ...
REVOKE GRANT OPTION FOR INSERT ON table FROM role
REVOKE INSERT ON table FROM role
架構(gòu)權(quán)限
GRANT INSERT/CONTROL ON SCHEMEA::name TO user
ALTER AUTHORIZATION ON SCHEMEA::name/table_name TO user/SCHEMA OWNER
ALTER schema_to TRANSFER schema_from.table
語句權(quán)限
CREATE TABLE/RULE
BACKUP 等
禁止權(quán)限
DENY X TO A
事務(wù)
在關(guān)系數(shù)據(jù)庫中，一個事務(wù)可以是一條SQL語句迷郑，一個SQL語句序列或整個程序枝恋。
事務(wù)的開始與結(jié)束可以由用戶顯式控制。如果用戶沒有顯式地定義事務(wù)則由DBMS按缺省規(guī)定自動劃分事務(wù)(隱式事務(wù))嗡害。
在SQL語言中焚碌，定義事務(wù)的語句有：
BEGIN TRANSACTION 定義事務(wù)開始
COMMIT 提交事務(wù)
ROLLBACK 回滾事務(wù)
事務(wù)的特性
- 原子性（Atomicity）：事務(wù)是數(shù)據(jù)庫工作的基本邏輯單位
- 事務(wù)中包括的各操作要么全做，要么全不做霸妹；
- 一致性（Consistency）：事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫從一個一致性狀態(tài)轉(zhuǎn)變到另一個一致性狀態(tài)呐能。
- 隔離性（Isolation）：一個事務(wù) 的執(zhí)行不能被其它事務(wù)干擾。
- 持續(xù)性（Durability）：一個事務(wù)一旦提交抑堡，它對數(shù)據(jù)庫數(shù)據(jù)的改變就應(yīng)是持久的摆出，即使系統(tǒng)發(fā)生故障也不影響其結(jié)果。
以上稱為事務(wù)的ACID特性首妖。
可能導(dǎo)致事務(wù)ACID特性被破壞的因素
- 多個事務(wù)并發(fā)運行偎漫，不同事務(wù)的操作交叉執(zhí)行；
- 事務(wù)在運行過程中被強行停止有缆。
數(shù)據(jù)恢復(fù)機制和并發(fā)控制機制的任務(wù)
- 保證多個事務(wù)的交叉運行不影響這些事務(wù)的原子性象踊；
- 保證被強行終止的事務(wù)不影響其它事務(wù)的執(zhí)行。
設(shè)置隱含事務(wù)方式的命令
SET IMPLICIT_TRANSACTIONS ON
取消隱含事務(wù)方式的命令
SET IMPLICIT_TRANSACTIONS OFF
基本的封鎖類型
排它鎖(Exclusive Locks棚壁，簡稱X鎖)杯矩，又稱為寫鎖；
若事務(wù)T對數(shù)據(jù)對象A加X鎖袖外，則只允許T讀取和修改A史隆，其他任何事務(wù)都不能再對A加任何類型的鎖，直到T釋放A上的鎖曼验。
這保證了其他事務(wù)在T釋放A的鎖之前不能再讀取和修改A泌射。共享鎖(Share Locks粘姜，簡稱S鎖)，又稱為讀鎖熔酷。
若事務(wù)T對數(shù)據(jù)對象A加S鎖孤紧，則事務(wù)T可以讀A但不能修改A，其他事務(wù)只能再對A加S鎖拒秘，而不能加X鎖号显，直到T釋放A的S鎖。
這就保證了其他事務(wù)可以讀A躺酒，但在T釋放A的S鎖之前不能對A做任何修改押蚤。
SQL Server的封鎖操作是在相關(guān)語句的“WITH (<table_hint>)”子句中完成的，該短語可以在SELECT阴颖、INSERT、UPDATE和DELETE等語句中指定表級鎖定的方式和范圍丐膝。
TABLOCK：對表施行共享封鎖量愧，在讀完數(shù)據(jù)后立刻釋放封鎖，此類封鎖可以避免讀“臟”數(shù)據(jù)帅矗，但不具有可重復(fù)讀的特性偎肃。
HOLDLOCK：與TABLOCK一起使用，可將共享鎖保留到事務(wù)完成浑此，而不是在讀完數(shù)據(jù)后立即釋放鎖累颂，這樣可以保證數(shù)據(jù)的可重復(fù)讀。
NOLOCK：不進行封鎖凛俱，此關(guān)鍵詞僅應(yīng)用于SELECT語句紊馏，這樣可能會讀取未提交事務(wù)的數(shù)據(jù)，即有可能發(fā)生“臟”讀蒲犬。
TABLOCKX：對表實施獨占封鎖朱监。
UPDLOCK：對表中的指定元組實施更新封鎖；這時其它事務(wù)可以對同一表中的其它元組也實施更新封鎖原叮，但是不允許對表實施共享封鎖和獨占封鎖赫编。
建立冗余數(shù)據(jù)最常用的技術(shù)是數(shù)據(jù)轉(zhuǎn)儲和登錄日志文件。
