Sybase基礎知識(zt)
第一講 Sybase基礎知識
一、客戶/伺服器體系結構
Sybase是一種建立在客戶/伺服器體系結構上的資料庫管理系統。
●什麼是客戶/伺服器體系結構？
從硬體角度看，客戶/伺服器體系結構是指將某項任務在兩臺或多臺機器之間進行分配，其中客戶機（Client）用來執行提供使用者介面和前端處理的應用程式，伺服器機（Server）提供客戶機使用的各種資源和服務。
從軟體角度看，客戶/伺服器體系結構是把某項應用或軟體系統按邏輯功能劃分為客戶軟體部分和伺服器軟體部分。客戶軟體部分一般負責資料的表示和應用，處理使用者介面，用以接收使用者的資料處理請求並將之轉換為對伺服器的請求，要求伺服器為其提供資料的儲存和檢索服務；伺服器端軟體負責接收客戶端軟體發來的請求並提供相應服務。
客戶/伺服器融合了大型機的強大功能和中央控制以及PC機的低成本和較好的處理平衡。客戶/伺服器為任務的集中/區域性分佈提供了一種新的方法，這種體系能夠使使用者對資料完整性、管理和安全性進行集中控制。在緩解網路交通和主機負荷以及滿足使用者需要方面，客戶/伺服器體系提供了良好的解決方案。
總之，客戶/伺服器的工作模式是：客戶與伺服器之間採用網路協議（如TCP/IP、IPX/SPX）進行連線和通訊，由客戶端向伺服器發出請求，伺服器端響應請求，並進行相應服務。
資料庫應用的客戶/伺服器模式如下圖所示：
二、訪問Sybase伺服器的基本過程
1．建立客戶與伺服器之間的連線，包括網路連線、客戶程式與伺服器程式之間的連線；
2．客戶端通過網路傳送SQL語句給伺服器，用來查詢或操作伺服器中的資料或資料庫物件；
3．伺服器接收到SQL語句後，對其進行語法分析、優化和編譯後執行；
4．如果執行的語句產生一個結果集，伺服器通過網路把結果集返回給客戶；
5．客戶端對收到的結果作相應的處理。
在客戶/伺服器體系，有兩種資料庫引擎結構，即多程式資料庫引擎和單程式、多執行緒引擎結構。
多程式結構即多個可執行程式同時執行。每當使用者登入到資料庫系統時實際上都啟動了資料庫引擎的一個獨立例項。存在起協調作用的程式協調進行之間的通訊以保證當多使用者訪問相同資料時資料的完整性。多程式資料庫引擎一般用在大型機資料庫上。
單程式多執行緒資料庫引擎在原理上和多程式資料庫引擎類似，不同的是多執行緒資料庫引擎自己負責排程各應用程式佔用CPU的時間，而不依賴於作業系統。這樣，多執行緒資料庫引擎自我保護的能力更強。
Oracle Server是一個真正的多程式資料庫引擎，Sybase資料庫管理系統採用的是單程式多執行緒的引擎結構。
三、Sybase產品概述
1．Sybase軟體的組成
Sybase軟體可劃分為三個部分：一是進行資料管理與維護的聯機關聯式資料庫管理系統Sybase SQL Server；二是支援資料庫應用系統的建立和開發的一組前端工具軟體Sybase SQL Tools；三是可把異構環境下其他廠商的應用軟體和任何型別的資料連線在一起的介面軟體Open Client/Open Server。
SQL Server是個可程式設計的資料庫管理系統（DBMS），它是整個Sybase產品的核心軟體，起著資料管理、高速緩衝區管理、事務管理的作用。
2．SQL Server的基本特徵
SQL Server是個關聯式資料庫管理系統，它具有如下一些基本特徵：
a.SQL Server可以放在若干個磁碟裝置上，初始安裝時所需的磁碟空間至少要17MB。
b.SQL Server支援多庫結構，也就是說Sybase系統中可以有多個資料庫。Sybase可以管理多個資料庫。
c.SQL Server可以編譯和執行T-SQL語句，並可返回客戶程式所要求的結果。T-SQL語句是標準SQL的擴充，它除了有資料定義語句、資料操縱語句和資料控制語句之外，主要增加了流程控制語句。
d.SQL Server可以管理多個使用者並具有較高的事務吞吐量和較低的事務響應時間。
客戶的應用程式可以存取Server中某一個或幾個資料庫的資料。
四、SQL Server的主要內容
Sybase SQL Server是一個多庫結構的RDBMS，體系結構大致如下：
1．資料庫
伺服器自身所使用的資料庫，也可以說是管理伺服器和使用者資料庫的資料庫。Sybase在安裝時，自動建立了四個系統資料庫：
master、model、tempdb、sybsystemprocs
(1) master資料庫
它是管理和控制使用者資料庫以及維護伺服器正常執行的核心資料庫，它儲存了大量的系統資訊，如伺服器配置、使用者、裝置等。
在master資料庫中不允許普通使用者在其中建立資料庫物件，否則會使得master資料庫的事務日誌很快變滿。如果事務日誌用盡，就無法使用dump transaction命令釋放master資料庫中的空間。
（2）model資料庫
它是為建立使用者資料庫而提供的模板。每當建立新的資料庫時，SQL Server自動建立model資料庫的一份拷貝，並把它擴充到使用者所要求的大小，以此作為新使用者資料庫。
Model資料庫中包含每個使用者資料庫所要求的系統表。Model資料庫可以被修改以便定製新建立的。
（3）tempdb資料庫
它是個臨時資料庫，為伺服器執行與處理提供一個共享的儲存區域，如group by和order by的中間結果就存放在這裡。Tempdb的空間為伺服器中所有資料庫的所有使用者所共享。
每次重啟SQL Server，伺服器的一個自動程式都拷貝model資料庫到tempdb資料庫，並清除tempdb中原來的內容。因此tempdb中的使用者表都是臨時的。臨時表分為兩類：可共享的和不可共享的。不可共享的臨時表在由create table中將符號#置於表名之前創立；可共享的臨時表通過create table中指定表名字首tempdb..而創立。不可共享的臨時表SQL Server自動為其新增數字字尾名，且它只存在於當前會話中。
（4）sybsystemprocs資料庫master資料庫
它是專門用來儲存系統命令（儲存過程）的資料庫，如sp_help、sp_configure、sp_helpdevice等。當任一資料庫使用者執行以sp_開頭的儲存過程時，SQL Server按照以下順序查詢：當前資料庫、sybsystemprocs資料庫、master資料庫。
2．使用者資料庫
使用者資料庫是我們使用Sybase伺服器的真正目的。要管理使用者資料，必須在Sybase中建立自己的資料庫，它是指用create database命令建立的資料庫。不能存取master資料庫的使用者是無權建立新的資料庫的。
資料庫中的主要內容——資料庫物件：
表、檢視、臨時表
索引、主鍵、外來鍵
預設值、規則
儲存過程、觸發器等
五、Sybase的安裝與配置
1．伺服器端的安裝
安裝建立SQL Server以後，要建立放置資料庫、日誌和索引的邏輯磁碟裝置。資料庫、日誌和索引的配置應注意以下原則；
a.不要把任何使用者物件安裝在master資料庫中。
b.日誌應該儲存在與資料庫分離的磁碟上。
c.可以通過跨越多個裝置分配工作優化I/O效能。
2．客戶端的安裝
3．需要立即更改的內容
（1）更改sa的登入口令；（2）命名伺服器；（3）修改檔名（保證Sybase Central正常啟動）；（4）更改預設裝置；（5）增加tempdb的空間。
4．建立使用者資料庫
以下通過一個建立資料庫的指令碼說明建立資料庫的過程：
//建立資料庫裝置，裝置大小以頁（2K）為單位
disk init
name=”test_dbdev”,
physname=”c:testtest_dbdev.dat”,
vdevno=10,
size=10240
go
disk init
name=”test_logdev”,
phyname=”c:testtest_logdev.dat”,
vdevno=11,
size=5120
go
//建立資料庫TEST_DB，其大小為20M，日誌大小為10M
create database TEST_DB
on test_dbdev=20
log on test_logdev=10
go
//開啟資料庫
use TEST_DB
go
5．系統管理的主要內容
a.物理資源的管理
b.使用者及其許可權管理
c.資料庫的備份與恢復
6．SQL Server的配置引數
伺服器配置是系統管理員的職責，正確的配置對系統效能有重大的影響。有兩個系統表儲存配置資訊：sysconfigures和syscurconfigs；sysconfigures是永久性的，一旦系統執行，sysconfigures的資訊就拷貝到syscurconfigs中。顯示配置和改變配置使用系統過程sp_configure。
有兩種配置值：動態的和靜態的，動態值一旦改變立即生效，靜態值要在系統重啟動後才起作用。
第二講 資料庫裝置與儲存空間管理
一、概述
1．安裝初始化
初始安裝SQL Server時，安裝程式和指令碼初始化主裝置，並建立master、model、tempdb和sybsystemprocs資料庫。系統資料庫、預定義裝置和段按下列預設方式組織：
a.master、model、tempdb資料庫安裝在主裝置master上；
b.sybsystemprocs資料庫安裝在安裝時選擇的裝置上（sysprocsdev）；
c.為每個資料庫建立三個預定義段：system、default和logsegment；
d.所有使用者建立資料庫的預設裝置是master裝置；
e.如果選擇安裝了審計資料庫sybsecurity，它位於自己的裝置上。
2．裝置與儲存管理考慮的主要問題
（1）恢復
物理磁碟崩潰時，磁碟映象或在單獨的物理裝置上儲存日誌為資料庫恢復提供了兩種機制。
（2）效能
磁碟讀寫速度是I/O操作的瓶頸，正確地把資料庫物件放置到物理裝置上有利於改進效能；
把日誌和資料庫物件置於單獨的裝置上可以提高系統效能；
把表放在一個硬碟上而把索引放在另一個硬碟上，由於把工作分置於兩個硬碟驅動器上，所以可以確保物理讀寫速度加快；
磁碟映象會降低磁碟寫的速度。
二、裝置（Device）
Sybase將資料庫中的所有資料存放在裝置上。
1．裝置的概念
裝置是Sybase預先配置的專門存放資料庫的一塊連續的磁碟空間，並且它被對映到一作業系統檔案或一原始磁碟分割槽上。它有兩個對應的名稱：邏輯名和物理名。NT僅支援裝置對映到檔案。
裝置與資料庫之間的關係：多對多關係。一個資料庫可以被建立或擴充到多個裝置上，一個裝置也可以被用來存放多個資料庫。不同的裝置作業系統可以對其並行地讀寫，因此我們可以人為地將一個資料庫放置到多個資料庫裝置上。
裝置的分類：Database Device和Dump Device。資料庫裝置存放資料庫和事務日誌，轉儲裝置用來存放資料庫或日誌的備份。
2．裝置的建立
命令語法：
DISK INIT
Name=’device_name’,
Physname=’physical_name’,
Vdevno=virtual_device_number
Size=number_of_pages
[…….]
舉例說明：
DISK INIT
Name=’My_Device’,
Physname=’D:databaseMy_device.dat’,
Vdevno=3
Size=5000
註釋：邏輯名、物理名、裝置虛擬號、裝置大小
建立轉儲裝置：
sp_addumpdevice{‘disk’|’tape’}
Logical_Name,
Physical_Name,
TapeSize
3．預設裝置
在沒有指定裝置的情況下，使用者建立的任何資料物件自動存放在預設裝置上。
初始安裝後，系統的主裝置master被預指定為預設裝置，因此要儘快建立自己的預設裝置。確保以下裝置不是預設裝置：系統主裝置、指定僅被日誌使用的裝置。
Sp_diskdefault 裝置名[,DefaultOn|DefaultOff]
4．磁碟映象
磁碟映象是出於資料庫安全性的考慮，當介質失敗時，磁碟映象能提供不間斷恢復。磁碟映象是磁碟上的資料的絕對拷貝。如果某一硬碟事故發生，則該被損壞的拷貝就自動變成離線狀態，因而所有的讀寫都被引向未被損壞的拷貝。
Sybase的磁碟映象是在裝置級上進行的，因此磁碟映象實質上是裝置映象。當對某一裝置進行映象操作時，Sybase自動建立一個附加裝置，由映象處理程式將原裝置上的所有資料拷貝到映象裝置上。
如果要使某一資料庫成為映象，被分配給該資料庫的每一裝置都必須成映象。Master裝置在伺服器中地位特殊，如果它損壞了，SQL Server將崩潰。因此，如果有可能總是要映象Master裝置（在另一磁碟上）。
映象命令語法：
Disk Mirror
Name=’device_name’,//被映象的裝置
Mirror=’physical_name’
[,writes=serial|noserial]
5．刪除裝置
sp_dropdevice logical_name[,delfile]
含有資料庫的裝置不允許刪除。
三、建立與使用段
段（Segment）是資料庫裝置上磁碟空間的邏輯組合，它可以看作是指向一個或多個資料庫裝置的標籤。利用段可以控制資料庫物件的存放位置，可以將資料庫物件分類存放到不同的段上。
裝置與段之間的關係：多對多關係。一個裝置上可以建立多個段，一個段也可以覆蓋多個裝置。
1．使用段的優點
a. 控制空間的使用：放在一個段上的資料庫物件不會在段外增長；
b. 提高效能：處於不同磁碟裝置上的段可以並行地讀寫；
d.處理大表：利用段，可以將一個大表分段放在獨立的物理裝置上，如將一個表的文字或圖象資料儲存另外的一個段上。
2．建立段
sp_addsegment 段名，資料庫名，裝置名
說明：在指定裝置上為某個資料庫建立一個段。
●擴充套件段的範圍
sp_exetendsegment 段名，資料庫名，裝置名
說明：裝置在資料庫中必須可用，否則需要擴充套件資料庫到新的裝置上；指定的段、資料庫、裝置必須存在。
●縮小段的範圍：
sp_dropsegment 段名，資料庫名，裝置名
說明：帶第三個引數時，該命令並不刪除段，只是段的範圍縮小了。若某個段包含了別的段要獨佔的裝置，就需要縮小該段的範圍。
3．使用段
●兩個資料庫放在同一裝置的不同段上，它們不會相互影響；
●當資料庫增加空間時，增加的空間會自動分配到它的每一個段上；
例如：alter database my_db
on data_dev=50
在data_dev裝置上為my_db增加50M空間，這50M空間被自動分配到資料庫的每一個段中。注意：如果data_dev對於資料庫是新的，system和default段會自動擴充套件到該裝置上。
可以使用alter database命令的log on選項分配附加的日誌空間。
（1）在段中建立新物件
create table 表名（列名 資料型別）[on 段名]
create [clusterd|non clusterd]index 索引名 on 表名（列名）[on 段名]
注意：按照定義，聚集索引總是與表放在同一段上。
（2）在段上放置現有物件
sp_placeobject 段名，物件名
注意：該命令並不把物件從一個資料庫裝置移動到另一裝置上，它隻影響未來的空間分配。
可以將某個大表的text欄位或image欄位放置到一個單獨的裝置段上。
Sp_placeobject 段名，“表名．欄位名”
（3）在段上建立聚集索引
按照定義，聚集索引總是與表放在同一段上。如果在一個段上建立表，而在另一個段上建立聚集索引，則其表與其索引一起移動，整個表將離開建立表的段而遷移到建立聚集索引的段中。通過這種方法，可以快速而方便地把指定表移動到指定的裝置上。
（4）系統預定義段
當使用者建立一個資料庫時，Sybase自動建立三個預定義的段：
system段：存放系統表（包括所有使用者物件的定義）
default段：存放使用者建立的各種物件，除非它們明顯地指定到不同的段上。
Logsegment段：存放資料庫的事務日誌。
（5）刪除段
刪除段是縮小段範圍的一個特例：
sp_dropsegment 段名，資料庫名
4．使用閾值管理
閾值（Threshold）管理是一種自動監控資料庫自由空間的機制，Sybase的閾值管理允許使用者為資料庫的某個段上的自由空間設定閾值並定義相應的儲存過程。當該段上的自由空間低於所置頭閾值時，Sybase自動執行相應的儲存過程。
在一個實際執行的資料庫中，日誌的增長速度一般要高於資料的增長，一旦日誌段的自由空間用盡，SQL Server在預設情況下會掛起所有資料操縱事務，客戶端應用程式停止執行。
在每個分離的段上儲存其事務日誌的資料庫均自動設定有最後機會閾值（Last Chance Threshold），其閾值是備份事務日誌所需的自由空間的估計值。當該段上的自由空間低於所置閾值時，Sybase自動執行名為sp_thresholdaction的儲存過程。該過程的名稱及引數由系統預定義，內容由使用者編寫。下面是一個簡單示例。
CREATE PROCEDURE dbo.sp_thresholdaction
/*本過程引數通過位置傳遞，名稱可以改變，但其定義及順序不能變*/
@db_name varchar(30),/*資料庫名*/
@seg_name varchar(30),/*段名*/
@space_lefe int,/*剩餘自由空間*/
@status int/*最後機會閾值，其值為1，其它閾值，其值為0*/
AS
BEGIN
/*使用者編寫過程內容*/
dump transaction @db_name
with truncate_only
END
第三講 資料庫與事務日誌
一、建立使用者資料庫
Create Database 資料庫名
On 裝置_1=Size_1,//單位：M
裝置_2=Size_2,
……
log on 日誌裝置=Log_Size
[With Override]//在同一裝置上建立資料庫和事務日誌時使用該選項
[For Load] //禁止使用者訪問直到資料庫的裝入或恢復操作完成為止
舉例：
Create Database test_db
On data_dev=100,//單位：M
Index_dev=50
Log on log_dev=30
說明：
（1）將日誌放在單獨的裝置上，有利於資料庫效能的提高；
（2）圖示：
Data_dev Index_dev log_dev
（3）如果將資料庫和日誌放在同一裝置上，就不能實現增量備份；
（4）通常將System和Default段縮減範圍到一個裝置上，如刪除裝置Index_dev上的System段和Default段，建立新的段，用來存放專門的資料庫物件。
二、更改資料庫
1．改變資料庫屬主
通常使用者資料庫有系統管理員建立，它的預設屬主是dbo。系統過程sp_changeddbowner可改變資料庫的屬主關係，它必須由資料庫管理員在要改變屬主關係的資料庫中執行。語法如下：
sp_changeddbowner login_name[,True]
其中引數True用於將許可權一半傳遞給新屬主。
2．擴充套件資料庫
（1）擴充套件資料庫空間
alter database 資料庫名
on 裝置名=擴充套件空間 //單位：M
如果擴充套件的裝置對於資料庫是新的，System和Default段會自動擴充套件到該裝置上。
（2）擴充套件事務日誌到新的裝置上
sp_logdevice 資料庫名，裝置名
舉例：將資料庫另外擴充5M，用於儲存日誌
alter database my_db
on my_dev=5
go
sp_logdevice my_db,my_dev
go
3.刪除資料庫
drop database 資料庫名
刪除裝置前必須刪除其上的所有資料庫，刪除裝置的命令是：sp_dropdevice
三、事務日誌
日誌檔案是用來記錄資料庫每一次修改活動的檔案。SQL Server中的每一個資料庫都有自己的日誌檔案，即系統表syslogs，也稱為事務日誌。事務日誌是撤消事務和出現故障時恢復事務的依據。
在某些情況下，事務日誌比資料本身更為重要。
什麼是事務？
資料庫的修改是以事務為單位進行的。一個事務就是一個操作序列，這些操作要麼全做，要麼全不做，它是一個不可分割的工作單位。任何一個事務具備如下特徵。
（1）執行的原子性（Atomic）；
（2）保持資料的一致性（Consistency）；
（3）彼此的隔離性（Isolation）；
（4）作用的永續性（Durability）。
上述事務的四個特徵被稱為事務的ACID準則。
事務在執行過程中，SQL Server把事務開始、事務結束以及對資料庫的插入、刪除和更新等每一個操作作為一個日誌記錄存放到事務日誌中。事務中的更新操作首先在資料庫緩衝區（記憶體）中進行，緩衝區分別有用來記錄操作活動的資料頁（data page）和日誌頁（log page）。當執行到commit tran時，日誌頁首先從緩衝區寫到磁碟上，而後資料頁從緩衝區寫到磁碟上，即遵循“先與日誌（write_ahead log）”的原則，這樣保證出現故障的情況下，通過日誌能夠得到最大限度的恢復。恢復必須撤消發生故障時還未提交的事務，已完成的事務若仍有未從緩衝區寫到資料庫裝置中，還要重新執行該事務。
附：建立與裝載資料庫例項
use master
decl are @vedvno int
select @vdevno=max(convert(tinyint,substring(convert(binary(4),d.low),v.low,1)))+1
from master.dbo.sysdevices d,master.dbo.spt_values v
where v.type=’E’and v.number=3
declare @v_str char(2)
select @v_str=convert(char(2),@vdevno)
print @v_str
disk init
name=”YDDATA”,
physname=”D:Syb_DataYDDATA.dat”,
vdevno=@vdevno+1,
size=153600
disk init
name=”YDINDEX”,
physname=”D:Syb_DataYDINDEX.dat”,
vdevno=@vdevno+2,
size=102400
disk init
name=”YDLOG”,
physname=”D:Syb_DataYDLOG.dat”,
vdevno=@vdevno+3,
size=76800
create database YDMISDB
on YDDATA=300,YDINDEX=200
log on YDLOG=150
use YDMISDB
execute sp_addsegment indexdev,YTMISDB,YTINDEX
execute sp_dropsegment “default”,YTMISDB,YTINDEX
execute sp_dropsegment system,YTMISDB,YTINDEX
use master
load database YDMISDB from ‘d:yd_dumpydmis_backup.dmp’
online database YDMISDB
第四講 資料庫安全性與使用者管理
一、安全管理概述
資料庫的安全性是指保護資料庫以防止不合法的使用所造成的資料洩露、更改或破壞。SQL Server的安全管理是一種基於角色（role）的管理方法，位於不同許可權層次（或角色）的使用者具有不同的使用者許可權。
規定使用者許可權有三個因素：使用者、資料物件和操作，即什麼使用者在哪些資料物件上可以執行什麼操作。
訪問SQL Server中資料的四道屏障：
（1）作業系統——作業系統登入
（2）Sybase伺服器——伺服器登入
（3）Sybase資料庫——資料庫使用者登入
（4）資料庫物件——物件授權
二、理解資料庫角色
角色是許可權的集合。
1．系統預定義角色
一個真正的資料庫管理員應該是整個伺服器及其資料庫的擁有者，具有管理伺服器及其資料庫的所有許可權。在一個大的系統中，資料庫管理員往往不只由一人承擔，而是將管理任務進行功能劃分，每人提當不同的責任。系統預定義角色就是為適應這種要求而設計的。
●系統管理員（sa_role）
sa_role角色擁有執行與資料庫具體應用無關的管理許可權：
◇安裝與更新SQL Server
◇管理伺服器的物理儲存
◇配置系統設定引數；
◇建立使用者資料庫；
◇授予SQL Server使用者許可權
◇┅┅
●系統安全管理員（sso_role）
sso_role用來實施安全上敏感的操作，執行有關安全性的任務：
◇建立伺服器登入帳戶；
◇管理口令；
◇授予除sa_role之外的任何角色；
◇管理審計系統；
◇┅┅
●操作員（oper_role）
oper_role執行全伺服器範圍的資料庫操作，如備份與恢復任意資料庫等：
◇轉儲資料庫與日誌
◇轉載資料庫與日誌
◇┅┅
2．建立與使用自定義角色
（1）建立新的角色：create role
（2）賦予角色許可權：grant
（3）設定登入帳戶的角色許可權：sp_role
格式：sp_role “grant”|”revoke”,角色名，登入帳號名
這裡的“grant”|”revoke”是指對角色許可權是授予還是撤消。
三、伺服器的帳戶管理
要想成為Sybase系統的一個使用者，首先必須擁有自己的登入帳戶。通過這一帳戶連同自己的口令向伺服器註冊後，才能使用Sybase系統中的一些資源，每一個這樣的登入帳戶在系統中被賦予Server級的標識SUID。
1．新增登入
sp_addlogin 登入名，口令[，預設的資料庫]
如果不指定預設的資料庫，則該帳戶的預設資料庫為master庫。
2．更改登入屬性
◇更改口令；
◇鎖定帳戶；
◇期限設定；
◇賦予角色。Sybase
四、資料庫使用者管理
通過某一個帳戶註冊到伺服器後，要想使用某一個資料庫，必須成為該資料庫的一個使用者。通過建立登入與使用者之間的聯絡來管理對資料庫的訪問。
1．組的概念
組（group）是資料庫使用者的集合。屬於某個組的使用者，他自動獲得該組所擁有的許可權。
Sp_addgroup 組名
◇將一個使用者加入某個組：（1）改變使用者屬性；（2）建立新使用者時指定。
◇所有使用者自動屬於public組，即使這個使用者已經屬於其他組了。
2．建立新的使用者
sp_adduser 登入名，使用者名稱[，組名]
3．別名使用者
Sybase的別名機制可以使得多個帳戶對應於同一個資料庫使用者，這樣使不同的登入在資料庫中擁有同樣的許可權。這在審計系統中是一種控制責任的一種手段。
Sp_addalias 登入名，資料庫中的使用者名稱
別名——相當於給某個使用者配一把別人的鑰匙（登入）。
五、使用者的許可權管理
控制使用者對資料庫物件的訪問，有兩類許可權：命令許可權和物件許可權。
1．命令許可權
Create Database
Create Table
Create View
Create Procedure
Create Rule
Create default
命令許可權授權與收權：
Grant 命令許可權組合 to 組名|使用者名稱|角色
Revoke 命令許可權組合 from 組名|使用者名稱|角色
2．物件許可權
Select
Update
Insert
Delete
Reference
Execute
物件許可權授權與收權：
Grant 物件許可權組合 on 資料庫物件 to public|組名|使用者名稱|角色
[With Grant Option]
Revoke 物件許可權組合 on 資料庫物件 to public|組名|使用者名稱|角色
From public public|組名|使用者名稱|角色
[Cascade]
授權與收權舉例：
◇Grant Insert,Delete on Employee
to user_1,Group_1
◇Grant Execute on Pro_culculate
to public
◇Grant Select on Employee(emp_id,emp_name)
to user_3
◇Grant All on Employee
to user_4
◇Revoke update on Employee(emp_id,emp_name)
from user_5
◇Revoke Create Table,Create Rule
from user_6
第五講 資料庫的備份與恢復
資料庫的備份與恢復是SQL Server保障資料安全的一種重要手段，為防止意外，資料庫管理員必須定期和經常製作資料庫的備份。一旦系統出現故障，資料能夠得到及時的恢復。
一、基本概念
1．事務處理及其日誌
SQL Server使用事務來跟蹤所有資料庫變化。事務是SQL Server的工作單元。一個事務包含一條或多條作為整體成功或失敗的T_SQL語句。每個資料庫都有自己的事務日誌，即系統表syslogs，事務日誌自動記錄每個使用者發出的每個事務，它飲食了每個事務足夠多的資訊，以確保資料能夠被恢復。
2．檢查點（CheckPoint）
伺服器在何時更新資料？
——在檢查點。在伺服器發出一個檢查點時：（1）更新資料；（2）在日誌中記錄下執行檢查點的標記。
檢查點可把所有“髒頁”寫到資料庫裝置上，“髒頁”是指從上一次檢查點以來，在記憶體中修改、但沒有在磁碟上修改的頁。SQL Server的自動檢查點機制保證了被完成的事務修改的資料頁有規律地從記憶體中的緩衝區寫到資料庫裝置上。
二、資料庫備份
若硬體介質出現故障（如磁碟損壞），當且僅當事先已對資料庫及其事務日誌作了備份，才能恢復資料庫。
注意：絕對不要使用作業系統的拷貝資料庫裝置，把這樣一個拷貝裝入SQL Server將導致大量資料庫受損。
備份的型別：
完全備份（）
增量備份——備份事務處理日誌
說明：
（1）只有把事務日誌放在單獨的裝置上，才能進行增量備份；
（2）備份事務日誌會截斷日誌，因此備份的內容是自上次備份以來的事務處理。
（3）備份之前要啟動備份伺服器，並最好建立轉儲裝置。
命令語法：
dump database 資料庫名
to 轉儲裝置名/物理檔名
dump transaction 資料庫名
{with {truncate_only|no_log}
to 轉儲裝置名/物理檔名
[with No_truncate]
Truncate_only與no_log選項用於刪除事務處理而不作拷貝。Truncate_only截斷日誌；在事務處理日誌完全滿時用no_log，它不為資料庫建立檢查點。兩個選項都會丟掉日誌。當使用了這兩個引數後，應及時備份整個資料庫。
No_truncate拷貝日誌但不截斷日誌，在出現介質錯誤時使用該選項。
圖形介面的選項與命令引數的對應關係：
（1）dump transaction （2）dump transaction…… with no_truncate
（3）dump transaction…… with truncate_only
（4）dump transaction…… with no_log
三、資料庫的恢復
使用load database載入備份到現有資料庫，資料庫可以是用於建立轉儲的資料庫，也可以不是。語法為：
load database 資料庫名 from 轉儲裝置名/物理檔名
load transaction資料庫名 from 轉儲裝置名/物理檔名
●利用備份恢復資料庫舉例：
某資料庫資料和日誌分別儲存在兩個獨立的磁碟上，正常運轉時的執行的備份計劃如下，每天的17:00執行整個資料庫的備份，每天的10:00、12:00、14:00、16:00點執行增量備份：
週一17:00磁帶1（100M）週二10:00磁帶2（30M）週二12:00磁帶3（30M）週二14:00磁帶4（30M）週二16:00磁帶5（30M）週二17:00磁帶6（30M）
DumpdatabaseDumptransactionDumptransactionDumptransactionDumptransactionDumpdatabase
若資料磁碟在週二的下午六點損壞，可以採用如下步驟恢復資料庫：
（1）使用dump transaction with no_truncate獲得當前的事務日誌轉儲，磁帶7；
（2）使用load database轉載最新的資料庫轉儲，磁帶6；（offline）
（3）使用load transaction提交最新的事務日誌轉儲，磁帶7；
（4）使用online database把資料庫狀態設定為online。
若資料磁碟在週二的下午4:50損壞，恢復過程如下：
（1）使用dump transaction with no_truncate獲得當前的事務日誌轉儲，磁帶7；
（2）使用load database轉載最新的資料庫轉儲，磁帶6；（offline）
（3）使用load transaction依次裝載磁帶2、3、4、5上的事務日誌；
（4）使用load transaction提交最新的事務日誌轉儲，磁帶7；
（5）使用online database把資料庫狀態設定為online。
四、制定備份與恢復的策略
由於事務日誌在恢復資料庫中的特殊作用，應定期備份資料庫及其事務日誌，而且事務日誌的備份要更頻繁一些。如：資料庫每週備份一次，事務日誌每天備份一次。
第六講 資料庫與T-SQL語言
一、關係模型的基本概念
關聯式資料庫以關係模型為基礎，它有以下三部分組成：
●資料結構——模型所操作的物件、型別的集合
●完整性規則——保證資料有效、正確的約束條件
●資料操作——對模型物件所允許執行的操作方式
關係（Relation）是一個由行和列組成的二維表格，表中的每一行是一條記錄（Record），每一列是記錄的一個欄位（Field）。表中的每一條記錄必須是互斥的，欄位的值必須具有原子性。
二、SQL語言概述
SQL（結構化查詢語言）是關聯式資料庫語言的一種國際標準，它是一種非過程化的語言。通過編寫SQL，我們可以實現對關聯式資料庫的全部操作。
●資料定義語言（DDL）——建立和管理資料庫物件
●資料操縱語言（DML）——用來查詢與更新資料
●資料控制語言（DCL）——控制資料的安全性
T-SQL語言是Sybase對SQL92標準的一種擴充套件，主要在它的基礎上增加了三個方面的功能：自己的資料型別/特有的SQL函式/流程控制功能
T-SQL中的識別符號使用說明：
（1）識別符號由1-30個字元或數字構成，但首字元必須為字母。臨時表的表名以#開頭，長度不能超過13個字元。
（2）資料庫物件的標識方法舉例
database.owner.tablename.columnname
執行遠端儲存過程：
EXEC server.db.owner.proc_name
當執行語句在批處理的句首時，EXEC可以省略。
三、Sybase的資料型別
在建立表或宣告區域性變數時，必須使用Sybase系統預定義型別。
1．字元型別
Char(n) VarChar(n)
2．數值型別
整數型別——Integer SmallInt TinyInt
浮點型別——Real Float Number[P,S] Decimal[P,S]
貨幣型別——Money SmallMoney
3．日期/時間型別
Datetime SmallDatetime
兩者時間部分的精度不同，前者精確到分，後者精確到1/30秒。
4．文字和影像型別
Text Image
5．二進位制資料型別
Binary(n) VarBinary(n)
四、資料定義語言
用來定義資料庫物件。資料庫物件是Sybase用來儲存資料的邏輯實體，主要有：
表（Table）、檢視（View）、臨時表（Temp Table）；
主鍵（Primary Key）、外來鍵（Foreign Key）、索引（Index）、規則（Rule）、預設值（Default）；
儲存過程（Stored Procedure）、觸發器（Trigger）
●基本語法
下面給出建立主要資料庫物件的語法：
1．表
建立表的基本語法是：
Create table[database.[owner].]table_name
(column_name datatype [default {constant_expression|user|null}]
{[{identity|null|not null}]|[[constraint constraint_name]
{{unique|primary key}[clustered|nonclustered]
[with{fillfactor|max_rows_per_page}=x]
[on segment_name]
|references[[database.]owner.]ref_table
[(ref_column)]
|check(search_condition)}]}…
在建立大型的資料庫時，可以考慮將建立表乃至其它資料庫物件的過程寫到一個文字里，當資料庫系統出現問題時，在最壞的情況下，重建過程可以得到簡化，也能比較好的對資料庫的建設過程進行監視。
建立表的過程完成下列活動：
·定義表的每一列；
·定義列名和列的資料型別並指定列是否處理空值；
·指定列是否具有IDENTITY屬性；
·定義列級的完整性約束和表級的完整性約束
上述過程可見，建立表的過程可以設定填充因子，將列置於段上，設計索引，外來鍵等等。
2．索引
索引對查詢效能的影響很大，要引起重視。
索引加速了資料檢索，Adaptive Server有三類索引：
·複合索引——索引包含多列；當兩列或多列由於它們的邏輯關係而作為整體被查詢時可建立這種索引；
·唯一索引——索引列的值不允許重複；
·簇聚索引和非簇聚索引——簇聚索引強迫Server不斷地對錶中資料排序或重排序以保證表中資料的物理順序和邏輯順序的一致性，簇聚索引對範圍查詢效能影響極大；非簇索引沒有這樣的要求，非簇聚索引對修改操作有利。
何時建索引？
·如果手動插入identity列，則建立唯一索引以保證不插入已經存在的值；
·經常被排序訪問的列，即被列在order by子句中的列，最好對其建立索引以便Adaptive Server能充分利用索引順序的優點；
·如果列經常用手連線，則可對列建立索引，這樣系統能更快地執行連線；
·包含主鍵的列一般都有簇聚索引，尤其是當它頻繁地和其它表的列相關聯；
·經常被範圍查詢的列最好為其建立簇聚索引，一旦查詢範圍內的第一個值被發現，則隨後的值在物理上一定相近。簇聚索引對單值查詢並沒有什麼優點。
建立索引的基本語法：
Create [unique][clustered|nonclustered]index index_name
On [[database.]owner.]table_name
(column_name[,column_name]…)
[on segment_name][with consumers=x]
上述語法包含了這樣的暗示：將簇聚索引和它的基表分離在不同的段上；段是邏輯概念，但段可以位於不同的物理裝置上，也即將簇聚索引和基表物理上分開。 這是不允許的，我們將在後面討論裝置、資料庫、段、表分割槽時作詳細討論。
3．鍵（key）
理解鍵是理解關聯的關鍵。
鍵和索引往往是一回事。鍵的意義在概念上，鍵用於參照完整性約束。
主鍵是表的單值列的集合，主鍵通過在放置它們的表上建立一個單值索引來實現其單值性的。實際上主鍵是作為標誌表的標誌符而存在的，一旦主鍵確定，則由該主鍵就確定了的表也就確定了。
外來鍵是和其它表中的主鍵相關的列，主鍵和外來鍵的關係確定了外來鍵的值域，該值域即為相應主鍵的取值範圍。這樣就從理論上強制實現了表與表之間的參照完整性。
前面建立表的語法裡包含了建立鍵的成分。也可以通過其它途徑建立主鍵和外來鍵。
◇Unique約束和Primary key約束的區別
Unique約束和Primary key約束用來保證同一表中指定的列上沒有重複值，這兩個約束都產生唯一索引確保資料一致性，預設情況下，Unique約束產生唯一的非聚集索引，Primary key約束產生唯一的聚集索引。Primary key約束比Unique約束嚴格：Primary key列不允許有空值，Unique列允許有空值。
4．檢視
檢視是檢視多表中資料的方法，檢視從基表派生，它並非物理存在，而是邏輯表；檢視也系統提供管理表的一種安全機制。檢視使得使用者集中精力在感興趣的資料集上。
建立檢視的語法：
create view [[database.]owner.]view_name
[(column_name[,column_name]…)]
as select [distinct] select_statement
[with check option]
有distinct關鍵字的檢視不能更新。當檢視涉及關聯時，定義檢視要小心，這時是對多表操作，完整性顯得很重要。
五、資料操縱語言
1．Select語句
基本語法：
SELECT[all|distinct]欄位列表
[into表名]
[from表名]
[where條件表示式]
[group by [all]欄位列表]
[having篩選表示式]
[order by 欄位列表[asc|desc]]
[compute聚集函式列表[by欄位列表]]
注意：Select語句中的子句必須按照上述順序使用。也就是說，若該語句包括一個group by子句和一個order by子句where，group by子句必須放在order by子句之前。
Having子句類似於where子句，不同之處有兩點：（1）Having子句必須結合group by子句使用；（2）where子句不能用聚集函式，而Having子句可以。
下面通過例項來對Select的通常用法加以介紹。
例1：選擇所有的列，語法為select * from table_list
如：select * from publishers
例2：選擇指定的列，語法為
select column_name[,column_name]…
from table_name
如：select pub_id,pub_name from publishers
例3：重新命名查詢結果中的列，語法為
select column_heading= column_name
from table_name
如：select Publisher=pub_name,pub_id
from publishers
例4：select列表中的計算值，可以對select列表中的數值資料進行計算，下面列出了算術運算子。
符號運算
+加
-減
/除
*乘
%取模
如select title_id,total_sales,total_sales*2 from titles
例5：使用distinct消除重複的查詢結果
可選的關鍵詞消除select語句的結果中的重複行。若不指定distinct，預設值為all，將檢索出包含重複行的所有行資料。
如：select distinct au_id from titleauthor
例6：選擇行——where語句
select語句中的確切指定要檢索哪些行的準則，其一般格式為：
select select_list from table_list where search_conditions
where子句中的搜尋條件（或稱限制）包括：
·比較運算子（＝，＜，＞，!=等＝
如：where advance*2＞total_sales*price
·範圍（between和not between）
如：where total_sales between 5000 and 10000
·列表（in和not in）
如：where state in(“CA”,”IN”,”MD”)
·匹配字元（like和not like）
如：where phone like “0535%”
·未知值（is null和is not null）
如：where advance is null
·以上各項的組合（and, or）
如：where advance＜5000 or total_sales between 500 and 1000
例7：用集合函式小結查詢結果
集合函式用特定列的資料來計算小結值。
集合函式結 果
Sum([all|distinct]expression)數值列中（不重複）值的總和
Avg([all|distinct]expression)數值列中（不重複）值的平均
count([all|distinct]expression)列中（不重複）非空值的數目
Count(*)選定的行數
Max(expression)Expression的最大值
Min(expression)Expression的最小值
如：select avg(advance),sum(total_sales)
from titles
where type=”as”
select count(*) from titles
select avg(distinct price) from titles
select max(price) from books
例8：分組組織查詢結果——group by 子句
group by 子句用在select語句中將一張表分成若干組。
如：select type, advance from titles group by type
例9：選擇分組資料——having子句
having為group by 子句設定條件，與where為select語句設定條件一樣。Having搜尋條件與where相同，但having可包括集合函式，而where不能包括。
下列語句使用帶集合函式having子句的例子。它把title表中的行按型別分組，但去掉了那隻包含一本書的分組。
Select type from titles group by type having count(*)>;1
下面是一個不帶集合函式的having子句的例子。它把title表中的行按型別分組，但去掉了那些不以字母“p”開頭的型別。
Select type from titles group by type having type like “p%”
例10：查詢結果排序——order by子句
Order by子句允許按一列或多列對查詢結果排序。每個排序可以是升序的（asc）或降序的(desc)。若不特別指明，則按升序進行。下列查詢返回按pub_id排序的結果：
Select pub_id,type,title_id from titles order by pub_id
例11：連線——從多張表中檢索資料
連線兩張或兩張以上的表是這樣一個過程：比較指定欄位中的資料，根據比較結果用符合條件的行組成一張新表。
舉例：
select publishers.pub_id,publishers.pub_name,authors.*
from publishers,authors
where publishers.city=authors.city
例12：分組計運算元句
Compute是Sybase對SQL標準中Group子句的擴充，可以將其看作帶聚集計算的Group子句。例如：
Select type,price,advance
From titles
Order by type
Compute sum(price),sum(advance) by type
2．Insert語句
用Insert命令向資料庫中新增行有兩種方法：使用關鍵詞values或使用select語句。
Insert語句的基本語法為：
Insert[into]表名[（欄位列表）]
{values（值列表）|select_statement}
舉例：insert into publishers
values(‘1622’,’Jardin,Inc.’,’Camden’,’NJ’)
Insert into publishers(pub_id,pub_name)
values(‘1756’,’The Health Center’)
Insert authors select * from newauthors
Insert authors(au_id,address,au_lname,au_fname)
Select * from newauthors
3．Delect語句
Delect可以對一行或多行進行操作。
Delect語句的基本語法為：
Delect 表名
[from 表名列表]
[where條件表示式]
舉例：Delect publishers
where pub_name=”Jardin,Inc.”
Delect titles
From authors, titles
Where titles.title_id=authors.title_id
4．Update語句
可以使用Update命令來改動表中的單個行、一組行或所有行。
Update語句的基本語法為：
Update表名
Set column_name1={expression1|null|(select_statement)}
[,column_name2={expression2|null|(select_statement)}]
[……]
[from 表名列表]
[where 條件表示式]
舉例：
update authors set_au_lname=”Health”,aufname=”Goodbody”
where au_lname=”Bloth”
update titles
set total_sales=total_sales + qty
from titles,sales
where titles.title_id=sales.title_id
六、Sybase預定義函式
1．聚集函式
sum([all|distinct]表示式)
avg([all|distinct]表示式)
count([all|distinct]表示式)
count(*)
max(表示式)
min(表示式)
2．字串函式
upper(字元表示式)
lower(字元表示式)
char(整型表示式)
char_length(字元表示式)
ltrim(字元表示式)
rtrim(字元表示式)
……
3．數學函式
abs(精確小數型表示式)
floor(精確小數型表示式)求小於或等於給定表示式值的最大整數（取底）
rand([整數型]
round(精確小數型表示式,整數)
sign(精確小數型表示式)
power(精確小數型表示式,整數冪)
……
4．日期函式
getdate()
datepart(日期部分，日期)
datediff(日期部分，日期1,日期2)
dateadd(日期部分，數值表示式，日期)
5．型別轉換函式
convert(資料型別，表示式[，格式])
6．系統函式
db_name([資料庫ID])
host_name()
isnull(表示式1,表示式2)
……
七、資料控制語言
用來控制資料的安全性，如許可權控制語句GRANT和REVOKE等。
第七講 資料庫程式設計基礎
一、批處理
SQL Server可以處理作為一批而提交的多個SQL語句，既可以是互動式的，也可以是一個檔案。批處理SQL語句由批結束標誌終止，該標誌指示SQL Server從前面開始執行該批處理語句，對於獨立的SQL實用程式isql而言，其批結束標誌為單獨佔一行的“go”。
舉例：選擇表title及表authors的行數
select count(*) from titles
select count(*) from authors
go
二、流程控制語言
1．變數宣告與賦值
全域性變數由系統預定義，以符號@@打頭。
區域性變數宣告使用Declare語句，這個變數必須以符號@開頭，後跟一個識別符號。
Declare @變數名 資料型別[，@變數名 資料型別，……]
變數賦值使用Select語句，未賦值的變數其值為Null。
舉例：
Declare @msg char(50)
Select @msg=’How are you?’
Select @msg=emp_name from employee
Where emp_id=12345678
2．SQL語句塊
Begin
Statement Block/*多個順序執行的SQL 語句*/
End
3．條件語句
If 條件表示式
語句（塊）
Else
語句（塊）
舉例：
if(select max(id) from sysobjects)<50
print ‘資料庫裡沒有使用者建立的物件‘
else
select name,type,id from sysobjects where id>;50
4．迴圈語句
While 條件表示式
語句（塊）
●兩個特殊的迴圈控制語句：
Continue 執行下一次迴圈
Break 退出當前迴圈
舉例：
While(select avg(price) from titles)>;$20
Begin
Update titles set price=price/2
If(select avg(price) from titles) Break
Else
Continue
End
5．其它控制語句
◇Return語句——無條件結束當前過程，並可返回給呼叫者的一個狀態值：Return[整數表示式]
◇Print語句
◇RaiseError語句
◇Waitfor語句
三、儲存過程
儲存過程是儲存在伺服器端的一類資料庫物件，它實質上是一段用SQL語言編寫的程式，它在伺服器端預先經過編譯，並確定出執行計劃，因此與同樣功能的批處理語句相比，它的執行速度較快。
基本語法：
Create Procedure[owner.]過程名
[@引數名 資料型別[=預設值][Output]]
[，@引數名 資料型別[=預設值][Output]]
[……]
AS
Begin
SQL語句（塊）
End
儲存過程是資料庫物件，和表、索引是一個級別的；是SQL語句和控制流語言的集合，儲存過程在首次執行時被編譯，並駐留在過程快取記憶體的記憶體中，所以儲存過程的招待非常快。儲存過程可以帶引數，可以呼叫其他過程，返回狀態值，返回引數值，並且可以在遠端SQL Server執行。可以在遠端SQL Server執行對資料庫設計有特別重要的意義。SQL Server提供的儲存過程稱為系統過程。
儲存過程大大增強了SQL的能力、效率和靈活性，經過編譯的儲存過程極大地改善SQL語句和批處理的效能。
儲存過程有很多優點：
●儲存過程在第一次執行時編譯，並儲存在過程快取記憶體的記憶體中。編譯時系統對其進行優化，以選擇最佳的路徑來訪問資料集中的資料，這種優化考慮了資料集的實際資料結構。因此儲存過程大大提高了系統的效能。
●儲存過程可以跨伺服器執行。這一點是通過觸發器來實現的，當然，首先儲存過程要能登入到該遠端伺服器。
●應用程式也能執行儲存過程，從而實現伺服器和客戶之間的協同作業。
●儲存過程減少了網路的交通。這是因為儲存過程的文字儲存在資料庫裡，呼叫儲存過程時通過網路的只是儲存過程的過程名。
●利用儲存過程可以提供一個附加的安全層。
如（該例子取自pubs2資料庫）：
Create proc titleid_proc(@title_id varchar(80))
As
Begin
Select @title_id=lower(@title_id)+”%”
Select title,title_id,price
Form titles
Where lower(title_id) like @title_id
Return @@rowcount
End
注意例子中的黑體部分，這實際上是一條賦值語句。該儲存過程有返回值。
儲存過程可以變得非常複雜。我們認為，建立儲存過程還是要遵循“最簡單就是最好”的原則。建議在建立儲存過程時採用縮排風格，否則建立的儲存過程三天之後連自己都看不懂。
需要對儲存過程作些說明：
●Create procedure 語句不能和其他語句在同一個批命令裡。
●Create procedure 語句不能包括下列語句：
use
Create View
Create default
Create rule
Create trigger
Create procedure
不能使用use語句好理解，儲存過程是針對資料庫的，不能在一個資料庫裡訪問另外的資料庫。如果在儲存過程裡訪問另外的資料庫，則資料庫表的參照完整性難於得到保障。
從另外幾條語句看，在儲存過程裡一般不能建立新的資料庫物件。但可以建立表和索引，以及和表相關聯的鍵，表是臨時表，在儲存過程結束後不能看見建立的臨時表；否則的話每執行一次儲存過程就建立一個表，結果可想而知。
儲存過程裡不能建立一個物件，刪除它；然後又在同一儲存過程裡用相同的名字建立新的物件。實際上，SQL Server在儲存過程執行時而不是在編譯時建立物件的。
●如果儲存過程呼叫另外的儲存過程，則第二個儲存過程可以呼叫在第一個儲存過程裡建立的物件。
●儲存過程包含的最多引數為255個，對儲存過程裡的區域性和全域性變數沒有限制。
最後討論一下系統儲存過程。系統儲存過程以sp_開頭，當然使用者建立的儲存過程也可以以sp_開頭；系統過程儲存在sybsystemprocs資料庫裡。系統過程的使用有許可權，如果打入系統過程名但沒有出現預期的結果，要麼是命令名錯，要麼是使用者沒有該過程的許可權。一般可通過系統管理員或資料庫所有者對系統過程的execute授權。
系統過程繁多，大致有幾類：
a. 使用者標誌和授權。這一類的過程主要由於：增加、刪除或報告在SQL Server上的登入，增加、刪除或報告某資料庫的使用者、分組或別名等。這類過程有sp_addlogin,sp_adduser,sp_helpgroup,sp_dropuser等。
b. 遠端過程的呼叫。這類過程用於：增加、刪除或報告能存取本SQL Server的遠端伺服器；增加能從遠端伺服器上存取本SQL Server的使用者名稱。這類過程有：sp_addremotelogin,sp_addserver,sp_dropserver等。
c. 資料定義和資料庫物件。這類儲存過程用於：連線和定義規則和預設值，增加、刪除或報告主碼、外碼和公共碼；增加、刪除或報告使用者定義的資料型別。這類儲存過程有：sp_bindfault, sp_bindrule, sp_help, sp_helpdb, sp_foreignkey, sp_helptext等。
d. 系統管理。這類儲存過程用於：增加、刪除或報告資料庫及轉儲裝置；報告鎖；設定的資料庫選擇及使用者正進行的程式；修改及報告配置變數；監控SQL Server的活動。這類過程有：sp_addumpdevice，sp_dropdevice, sp_helpdevice等。
四、觸發器
觸發器是一種用來保障參照完整性的特殊的儲存過程，它維護不同表中資料間關係的有關規則。當對指定的表進行某種特定操作（如：Insert,Delete或Update）時，觸發器產生作用。觸發器可以呼叫儲存過程。
建立觸發器的語法：
Create Trigger[owner.]觸發器名
On [owner.]表名
For {insert,update,delete}
As
Begin
SQL語句（塊）
End
定義一個好的觸發器對簡化資料的管理，保證資料庫的安全都有重要的影響。觸發器是針對表一級的，這就意味著，只有表的所有者有權建立表的觸發器。
舉例：
插入一個新行，必須保證外來鍵與主鍵相匹配，觸發器應該首先檢查被插入行與主鍵表的連線。
以下的觸發器對inserted表和titles表的title_id進行比較，這裡假設正在給外來鍵輸入資料，沒有插入空值，若連線失敗，事務被回退。insert,update,delete
Create trigger forinsertrigl
On salesdetail
For insert
As
If(select count(*)
From title,inserted
Where titles.title_id=inserted.title_id)!=@@rowcount
Begin
Rollback transaction
Print “No,some title_id does not exist in titles.”
End
Else
Print “Added! All the title_id is exist in titles.”
在本例中，@@rowcount代表新增到salesdetail表的行數，這也是新增到inserted表中的行數。通過連線表titles和表inserted來檢測所有新增到salesdetail的title_id是否在titles中存在。若所連線的行數（count(*)）與@@rowcount不同，由有一個或多個插入不正確，整個事務被取消。
觸發器的限制：
●一個表最多隻能有三個觸發器，insert,update,delete
●每個觸發器只能用於一個表
●不能對檢視、臨時表建立觸發器
●Truncate table能刪除表，但不能觸發觸發器
●不能將觸發器用於系統表
合理地使用觸發器對效能的影響是正面的。在設計和使用觸發器時，經常地用sp_depends命令瞭解物件所關聯的觸發器是有好處的，該命令能列出觸發器影響的所有物件、表和視等。
在定義幾類資料庫物件的時候，對儲存過程、索引和觸發器要給予特別的注意，尤其儲存過程，它設計的好壞對資料庫效能的影響很大。
說明：Sybase觸發器使用的兩個測試表：Deleted表和Inserted表，它們都是臨時表，其結構與觸發器的基表結構相同，用來存放與修改相關的資料行。
五、遊標
1．遊標的概念
遊標是指向查詢結果集的一個指標，它是一個通過定義語句與一條Select語句相關聯的一組SQL語句。遊標包含兩方面的內容：
●遊標結果集：執行其中的Select語句所得到的結果集；
●遊標位置：一個指向遊標結果集內的某一條記錄的指標
利用遊標可以單獨操縱結果集中的每一行。遊標在定義以後存在兩種狀態：關閉和開啟。當遊標關閉時，其查詢結果集不存在；只有當遊標開啟時，才能按行讀取或修改結果集中的資料。
2．使用遊標
一個應用程式可以使用兩種型別的遊標：前端（客戶）遊標和後端（伺服器）遊標，它們是兩個不同的概念。
無論使用哪一種遊標，都需要經過如下幾個步驟：
●定義遊標
●開啟遊標
●從遊標中運算元據
●關閉遊標
下面講述的是後端（伺服器）遊標。
（1）定義遊標
在使用遊標之前必須宣告它。宣告指定定義遊標結果集的查詢。通過使用for update或for read only關鍵詞將遊標顯式定義成可更新的或只讀的。
Declare cursor語法為：
For select_statement
[for{read only|update[of colum_name_list]}]
舉例：
declare pubs_crsr cursor
for select pub_name,city,state
from publishers
for update of city,state
（2）開啟遊標
open的語法為：
open 遊標名
在宣告遊標後，必須開啟它以便用fetch,update,delete讀取、修改、刪除行。在開啟一個遊標後，它將被放在遊標結果集的首行前，必須用fetch語句訪問該首行。
（3）從遊標中讀取資料
在宣告並開啟一個遊標後，可用fetch命令從遊標結果集中獲取資料行。
Fetch的語法為：
Fetch 遊標名[into 變數列表]
舉例：fetch pub_crsr into @name,@city,@state
SQL Server在每次讀取後返回一個狀態值。可用@@sqlstatus訪問該值，下表給出了可能的@@sqlstatus值及其意義。
值意 義
0Fetch語句成功
1Fetch語句導致一錯誤
2結果集沒有更多的資料，當前位置位於結果集最後一行，而客戶對該遊標仍發出Fetch語句時。
若遊標是可更新的，可用update和delete語句來更新和刪除行。
刪除遊標當前行的語法為：
delete[from]表名
where current of 遊標名
舉例：delete from authors where current of authors_crsr
當遊標刪除一行後，SQL Server將遊標置於被刪除行的前一行上。
更新遊標當前行的語法為：
update 表名
set column_name1={expression1|NULL|(select_statement)}
[,column_name2={expression2|NULL|(select_statement)}
[……]
where current of 遊標名
舉例：
update publishers
set city=”Pasadena”,state=”CA”
where current of pubs_crsr
（4）關閉遊標
當結束一個遊標結果集時，可用close關閉。該語法為：
close 遊標名
關閉遊標並不改變其定義，可用open再次開啟。若想放棄遊標，必須使用deallocate釋放它，deallocater的語法為：
deallocater cursor 遊標名
deallocater語句通知SQL Server釋放Declare語句使用的共享記憶體，不再允許另一程式在其上執行Open操作。
六、事務——維持資料一致性和恢復
1．事務的定義
事務提供了一種將T-SQL語句分組的方法，從而使它們能夠被當成一個單元來處理：組中所有語句或都執行，或都不執行。
事務是確保一個或多個SQL語句的集合不被當成單一工作單元處理的機制，SQL Server自動將所有資料修改命令，包括單步改變請求，作為事務處理，預設時，每個insert、update、delete語句被當成一個事務處理。
2．事務的作用
事務使SQL Server能保證：
·一致性——同時發生的查詢或改變請求彼此不衝突，使用者不能對正處於改變過程的資料進行檢視或操作。
·恢復——在系統失效時，資料庫的恢復是完全的和自動的。
3．使用事務
a. 開始和確認事務
begin transaction和commit transaction可將任意數目的SQL語句封裝起來，這兩名的簡單語法為：
begin transaction
commit transaction
b. 回退事務
在commit transaction命令提交前任何時候可取消或回退事務，該命令的簡單語法為：
rollback transaction
4．檢查事務狀態
全域性變數@@transtate記錄了事務當前的狀態。在執行一個語句後SQL Server通過記錄所有事務變化來確定返回何種狀態。
@@transtate可包含下列值：
值意 義
0事務進行中：一個顯式或隱式事務有效；上一語句執行成功
1事務完成：事務完成並提交其變化
2語句異常中止：上一語句異常終止；對事務無影響
3事務異常中止：事務異常中止並回退所有變化
舉例：在事務中，可在一個語句（如insert）後使用@@transtate確定該語句成功或失敗對事務的影響。commit transaction
Begin transaction
Insert into publishers(pub_id)values(‘9999’)
(1 row affected)
select @@transtate
…………………
0
commit transaction
select @@transtate
…………………
0
(1 row affected)
一、客戶/伺服器體系結構
Sybase是一種建立在客戶/伺服器體系結構上的資料庫管理系統。
●什麼是客戶/伺服器體系結構？
從硬體角度看，客戶/伺服器體系結構是指將某項任務在兩臺或多臺機器之間進行分配，其中客戶機（Client）用來執行提供使用者介面和前端處理的應用程式，伺服器機（Server）提供客戶機使用的各種資源和服務。
從軟體角度看，客戶/伺服器體系結構是把某項應用或軟體系統按邏輯功能劃分為客戶軟體部分和伺服器軟體部分。客戶軟體部分一般負責資料的表示和應用，處理使用者介面，用以接收使用者的資料處理請求並將之轉換為對伺服器的請求，要求伺服器為其提供資料的儲存和檢索服務；伺服器端軟體負責接收客戶端軟體發來的請求並提供相應服務。
客戶/伺服器融合了大型機的強大功能和中央控制以及PC機的低成本和較好的處理平衡。客戶/伺服器為任務的集中/區域性分佈提供了一種新的方法，這種體系能夠使使用者對資料完整性、管理和安全性進行集中控制。在緩解網路交通和主機負荷以及滿足使用者需要方面，客戶/伺服器體系提供了良好的解決方案。
總之，客戶/伺服器的工作模式是：客戶與伺服器之間採用網路協議（如TCP/IP、IPX/SPX）進行連線和通訊，由客戶端向伺服器發出請求，伺服器端響應請求，並進行相應服務。
資料庫應用的客戶/伺服器模式如下圖所示：
二、訪問Sybase伺服器的基本過程
1．建立客戶與伺服器之間的連線，包括網路連線、客戶程式與伺服器程式之間的連線；
2．客戶端通過網路傳送SQL語句給伺服器，用來查詢或操作伺服器中的資料或資料庫物件；
3．伺服器接收到SQL語句後，對其進行語法分析、優化和編譯後執行；
4．如果執行的語句產生一個結果集，伺服器通過網路把結果集返回給客戶；
5．客戶端對收到的結果作相應的處理。
在客戶/伺服器體系，有兩種資料庫引擎結構，即多程式資料庫引擎和單程式、多執行緒引擎結構。
多程式結構即多個可執行程式同時執行。每當使用者登入到資料庫系統時實際上都啟動了資料庫引擎的一個獨立例項。存在起協調作用的程式協調進行之間的通訊以保證當多使用者訪問相同資料時資料的完整性。多程式資料庫引擎一般用在大型機資料庫上。
單程式多執行緒資料庫引擎在原理上和多程式資料庫引擎類似，不同的是多執行緒資料庫引擎自己負責排程各應用程式佔用CPU的時間，而不依賴於作業系統。這樣，多執行緒資料庫引擎自我保護的能力更強。
Oracle Server是一個真正的多程式資料庫引擎，Sybase資料庫管理系統採用的是單程式多執行緒的引擎結構。
三、Sybase產品概述
1．Sybase軟體的組成
Sybase軟體可劃分為三個部分：一是進行資料管理與維護的聯機關聯式資料庫管理系統Sybase SQL Server；二是支援資料庫應用系統的建立和開發的一組前端工具軟體Sybase SQL Tools；三是可把異構環境下其他廠商的應用軟體和任何型別的資料連線在一起的介面軟體Open Client/Open Server。
SQL Server是個可程式設計的資料庫管理系統（DBMS），它是整個Sybase產品的核心軟體，起著資料管理、高速緩衝區管理、事務管理的作用。
2．SQL Server的基本特徵
SQL Server是個關聯式資料庫管理系統，它具有如下一些基本特徵：
a.SQL Server可以放在若干個磁碟裝置上，初始安裝時所需的磁碟空間至少要17MB。
b.SQL Server支援多庫結構，也就是說Sybase系統中可以有多個資料庫。Sybase可以管理多個資料庫。
c.SQL Server可以編譯和執行T-SQL語句，並可返回客戶程式所要求的結果。T-SQL語句是標準SQL的擴充，它除了有資料定義語句、資料操縱語句和資料控制語句之外，主要增加了流程控制語句。
d.SQL Server可以管理多個使用者並具有較高的事務吞吐量和較低的事務響應時間。
客戶的應用程式可以存取Server中某一個或幾個資料庫的資料。
四、SQL Server的主要內容
Sybase SQL Server是一個多庫結構的RDBMS，體系結構大致如下：
1．資料庫
伺服器自身所使用的資料庫，也可以說是管理伺服器和使用者資料庫的資料庫。Sybase在安裝時，自動建立了四個系統資料庫：
master、model、tempdb、sybsystemprocs
(1) master資料庫
它是管理和控制使用者資料庫以及維護伺服器正常執行的核心資料庫，它儲存了大量的系統資訊，如伺服器配置、使用者、裝置等。
在master資料庫中不允許普通使用者在其中建立資料庫物件，否則會使得master資料庫的事務日誌很快變滿。如果事務日誌用盡，就無法使用dump transaction命令釋放master資料庫中的空間。
（2）model資料庫
它是為建立使用者資料庫而提供的模板。每當建立新的資料庫時，SQL Server自動建立model資料庫的一份拷貝，並把它擴充到使用者所要求的大小，以此作為新使用者資料庫。
Model資料庫中包含每個使用者資料庫所要求的系統表。Model資料庫可以被修改以便定製新建立的。
（3）tempdb資料庫
它是個臨時資料庫，為伺服器執行與處理提供一個共享的儲存區域，如group by和order by的中間結果就存放在這裡。Tempdb的空間為伺服器中所有資料庫的所有使用者所共享。
每次重啟SQL Server，伺服器的一個自動程式都拷貝model資料庫到tempdb資料庫，並清除tempdb中原來的內容。因此tempdb中的使用者表都是臨時的。臨時表分為兩類：可共享的和不可共享的。不可共享的臨時表在由create table中將符號#置於表名之前創立；可共享的臨時表通過create table中指定表名字首tempdb..而創立。不可共享的臨時表SQL Server自動為其新增數字字尾名，且它只存在於當前會話中。
（4）sybsystemprocs資料庫master資料庫
它是專門用來儲存系統命令（儲存過程）的資料庫，如sp_help、sp_configure、sp_helpdevice等。當任一資料庫使用者執行以sp_開頭的儲存過程時，SQL Server按照以下順序查詢：當前資料庫、sybsystemprocs資料庫、master資料庫。
2．使用者資料庫
使用者資料庫是我們使用Sybase伺服器的真正目的。要管理使用者資料，必須在Sybase中建立自己的資料庫，它是指用create database命令建立的資料庫。不能存取master資料庫的使用者是無權建立新的資料庫的。
資料庫中的主要內容——資料庫物件：
表、檢視、臨時表
索引、主鍵、外來鍵
預設值、規則
儲存過程、觸發器等
五、Sybase的安裝與配置
1．伺服器端的安裝
安裝建立SQL Server以後，要建立放置資料庫、日誌和索引的邏輯磁碟裝置。資料庫、日誌和索引的配置應注意以下原則；
a.不要把任何使用者物件安裝在master資料庫中。
b.日誌應該儲存在與資料庫分離的磁碟上。
c.可以通過跨越多個裝置分配工作優化I/O效能。
2．客戶端的安裝
3．需要立即更改的內容
（1）更改sa的登入口令；（2）命名伺服器；（3）修改檔名（保證Sybase Central正常啟動）；（4）更改預設裝置；（5）增加tempdb的空間。
4．建立使用者資料庫
以下通過一個建立資料庫的指令碼說明建立資料庫的過程：
//建立資料庫裝置，裝置大小以頁（2K）為單位
disk init
name=”test_dbdev”,
physname=”c:testtest_dbdev.dat”,
vdevno=10,
size=10240
go
disk init
name=”test_logdev”,
phyname=”c:testtest_logdev.dat”,
vdevno=11,
size=5120
go
//建立資料庫TEST_DB，其大小為20M，日誌大小為10M
create database TEST_DB
on test_dbdev=20
log on test_logdev=10
go
//開啟資料庫
use TEST_DB
go
5．系統管理的主要內容
a.物理資源的管理
b.使用者及其許可權管理
c.資料庫的備份與恢復
6．SQL Server的配置引數
伺服器配置是系統管理員的職責，正確的配置對系統效能有重大的影響。有兩個系統表儲存配置資訊：sysconfigures和syscurconfigs；sysconfigures是永久性的，一旦系統執行，sysconfigures的資訊就拷貝到syscurconfigs中。顯示配置和改變配置使用系統過程sp_configure。
有兩種配置值：動態的和靜態的，動態值一旦改變立即生效，靜態值要在系統重啟動後才起作用。
第二講 資料庫裝置與儲存空間管理
一、概述
1．安裝初始化
初始安裝SQL Server時，安裝程式和指令碼初始化主裝置，並建立master、model、tempdb和sybsystemprocs資料庫。系統資料庫、預定義裝置和段按下列預設方式組織：
a.master、model、tempdb資料庫安裝在主裝置master上；
b.sybsystemprocs資料庫安裝在安裝時選擇的裝置上（sysprocsdev）；
c.為每個資料庫建立三個預定義段：system、default和logsegment；
d.所有使用者建立資料庫的預設裝置是master裝置；
e.如果選擇安裝了審計資料庫sybsecurity，它位於自己的裝置上。
2．裝置與儲存管理考慮的主要問題
（1）恢復
物理磁碟崩潰時，磁碟映象或在單獨的物理裝置上儲存日誌為資料庫恢復提供了兩種機制。
（2）效能
磁碟讀寫速度是I/O操作的瓶頸，正確地把資料庫物件放置到物理裝置上有利於改進效能；
把日誌和資料庫物件置於單獨的裝置上可以提高系統效能；
把表放在一個硬碟上而把索引放在另一個硬碟上，由於把工作分置於兩個硬碟驅動器上，所以可以確保物理讀寫速度加快；
磁碟映象會降低磁碟寫的速度。
二、裝置（Device）
Sybase將資料庫中的所有資料存放在裝置上。
1．裝置的概念
裝置是Sybase預先配置的專門存放資料庫的一塊連續的磁碟空間，並且它被對映到一作業系統檔案或一原始磁碟分割槽上。它有兩個對應的名稱：邏輯名和物理名。NT僅支援裝置對映到檔案。
裝置與資料庫之間的關係：多對多關係。一個資料庫可以被建立或擴充到多個裝置上，一個裝置也可以被用來存放多個資料庫。不同的裝置作業系統可以對其並行地讀寫，因此我們可以人為地將一個資料庫放置到多個資料庫裝置上。
裝置的分類：Database Device和Dump Device。資料庫裝置存放資料庫和事務日誌，轉儲裝置用來存放資料庫或日誌的備份。
2．裝置的建立
命令語法：
DISK INIT
Name=’device_name’,
Physname=’physical_name’,
Vdevno=virtual_device_number
Size=number_of_pages
[…….]
舉例說明：
DISK INIT
Name=’My_Device’,
Physname=’D:databaseMy_device.dat’,
Vdevno=3
Size=5000
註釋：邏輯名、物理名、裝置虛擬號、裝置大小
建立轉儲裝置：
sp_addumpdevice{‘disk’|’tape’}
Logical_Name,
Physical_Name,
TapeSize
3．預設裝置
在沒有指定裝置的情況下，使用者建立的任何資料物件自動存放在預設裝置上。
初始安裝後，系統的主裝置master被預指定為預設裝置，因此要儘快建立自己的預設裝置。確保以下裝置不是預設裝置：系統主裝置、指定僅被日誌使用的裝置。
Sp_diskdefault 裝置名[,DefaultOn|DefaultOff]
4．磁碟映象
磁碟映象是出於資料庫安全性的考慮，當介質失敗時，磁碟映象能提供不間斷恢復。磁碟映象是磁碟上的資料的絕對拷貝。如果某一硬碟事故發生，則該被損壞的拷貝就自動變成離線狀態，因而所有的讀寫都被引向未被損壞的拷貝。
Sybase的磁碟映象是在裝置級上進行的，因此磁碟映象實質上是裝置映象。當對某一裝置進行映象操作時，Sybase自動建立一個附加裝置，由映象處理程式將原裝置上的所有資料拷貝到映象裝置上。
如果要使某一資料庫成為映象，被分配給該資料庫的每一裝置都必須成映象。Master裝置在伺服器中地位特殊，如果它損壞了，SQL Server將崩潰。因此，如果有可能總是要映象Master裝置（在另一磁碟上）。
映象命令語法：
Disk Mirror
Name=’device_name’,//被映象的裝置
Mirror=’physical_name’
[,writes=serial|noserial]
5．刪除裝置
sp_dropdevice logical_name[,delfile]
含有資料庫的裝置不允許刪除。
三、建立與使用段
段（Segment）是資料庫裝置上磁碟空間的邏輯組合，它可以看作是指向一個或多個資料庫裝置的標籤。利用段可以控制資料庫物件的存放位置，可以將資料庫物件分類存放到不同的段上。
裝置與段之間的關係：多對多關係。一個裝置上可以建立多個段，一個段也可以覆蓋多個裝置。
1．使用段的優點
a. 控制空間的使用：放在一個段上的資料庫物件不會在段外增長；
b. 提高效能：處於不同磁碟裝置上的段可以並行地讀寫；
d.處理大表：利用段，可以將一個大表分段放在獨立的物理裝置上，如將一個表的文字或圖象資料儲存另外的一個段上。
2．建立段
sp_addsegment 段名，資料庫名，裝置名
說明：在指定裝置上為某個資料庫建立一個段。
●擴充套件段的範圍
sp_exetendsegment 段名，資料庫名，裝置名
說明：裝置在資料庫中必須可用，否則需要擴充套件資料庫到新的裝置上；指定的段、資料庫、裝置必須存在。
●縮小段的範圍：
sp_dropsegment 段名，資料庫名，裝置名
說明：帶第三個引數時，該命令並不刪除段，只是段的範圍縮小了。若某個段包含了別的段要獨佔的裝置，就需要縮小該段的範圍。
3．使用段
●兩個資料庫放在同一裝置的不同段上，它們不會相互影響；
●當資料庫增加空間時，增加的空間會自動分配到它的每一個段上；
例如：alter database my_db
on data_dev=50
在data_dev裝置上為my_db增加50M空間，這50M空間被自動分配到資料庫的每一個段中。注意：如果data_dev對於資料庫是新的，system和default段會自動擴充套件到該裝置上。
可以使用alter database命令的log on選項分配附加的日誌空間。
（1）在段中建立新物件
create table 表名（列名 資料型別）[on 段名]
create [clusterd|non clusterd]index 索引名 on 表名（列名）[on 段名]
注意：按照定義，聚集索引總是與表放在同一段上。
（2）在段上放置現有物件
sp_placeobject 段名，物件名
注意：該命令並不把物件從一個資料庫裝置移動到另一裝置上，它隻影響未來的空間分配。
可以將某個大表的text欄位或image欄位放置到一個單獨的裝置段上。
Sp_placeobject 段名，“表名．欄位名”
（3）在段上建立聚集索引
按照定義，聚集索引總是與表放在同一段上。如果在一個段上建立表，而在另一個段上建立聚集索引，則其表與其索引一起移動，整個表將離開建立表的段而遷移到建立聚集索引的段中。通過這種方法，可以快速而方便地把指定表移動到指定的裝置上。
（4）系統預定義段
當使用者建立一個資料庫時，Sybase自動建立三個預定義的段：
system段：存放系統表（包括所有使用者物件的定義）
default段：存放使用者建立的各種物件，除非它們明顯地指定到不同的段上。
Logsegment段：存放資料庫的事務日誌。
（5）刪除段
刪除段是縮小段範圍的一個特例：
sp_dropsegment 段名，資料庫名
4．使用閾值管理
閾值（Threshold）管理是一種自動監控資料庫自由空間的機制，Sybase的閾值管理允許使用者為資料庫的某個段上的自由空間設定閾值並定義相應的儲存過程。當該段上的自由空間低於所置頭閾值時，Sybase自動執行相應的儲存過程。
在一個實際執行的資料庫中，日誌的增長速度一般要高於資料的增長，一旦日誌段的自由空間用盡，SQL Server在預設情況下會掛起所有資料操縱事務，客戶端應用程式停止執行。
在每個分離的段上儲存其事務日誌的資料庫均自動設定有最後機會閾值（Last Chance Threshold），其閾值是備份事務日誌所需的自由空間的估計值。當該段上的自由空間低於所置閾值時，Sybase自動執行名為sp_thresholdaction的儲存過程。該過程的名稱及引數由系統預定義，內容由使用者編寫。下面是一個簡單示例。
CREATE PROCEDURE dbo.sp_thresholdaction
/*本過程引數通過位置傳遞，名稱可以改變，但其定義及順序不能變*/
@db_name varchar(30),/*資料庫名*/
@seg_name varchar(30),/*段名*/
@space_lefe int,/*剩餘自由空間*/
@status int/*最後機會閾值，其值為1，其它閾值，其值為0*/
AS
BEGIN
/*使用者編寫過程內容*/
dump transaction @db_name
with truncate_only
END
第三講 資料庫與事務日誌
一、建立使用者資料庫
Create Database 資料庫名
On 裝置_1=Size_1,//單位：M
裝置_2=Size_2,
……
log on 日誌裝置=Log_Size
[With Override]//在同一裝置上建立資料庫和事務日誌時使用該選項
[For Load] //禁止使用者訪問直到資料庫的裝入或恢復操作完成為止
舉例：
Create Database test_db
On data_dev=100,//單位：M
Index_dev=50
Log on log_dev=30
說明：
（1）將日誌放在單獨的裝置上，有利於資料庫效能的提高；
（2）圖示：
Data_dev Index_dev log_dev
（3）如果將資料庫和日誌放在同一裝置上，就不能實現增量備份；
（4）通常將System和Default段縮減範圍到一個裝置上，如刪除裝置Index_dev上的System段和Default段，建立新的段，用來存放專門的資料庫物件。
二、更改資料庫
1．改變資料庫屬主
通常使用者資料庫有系統管理員建立，它的預設屬主是dbo。系統過程sp_changeddbowner可改變資料庫的屬主關係，它必須由資料庫管理員在要改變屬主關係的資料庫中執行。語法如下：
sp_changeddbowner login_name[,True]
其中引數True用於將許可權一半傳遞給新屬主。
2．擴充套件資料庫
（1）擴充套件資料庫空間
alter database 資料庫名
on 裝置名=擴充套件空間 //單位：M
如果擴充套件的裝置對於資料庫是新的，System和Default段會自動擴充套件到該裝置上。
（2）擴充套件事務日誌到新的裝置上
sp_logdevice 資料庫名，裝置名
舉例：將資料庫另外擴充5M，用於儲存日誌
alter database my_db
on my_dev=5
go
sp_logdevice my_db,my_dev
go
3.刪除資料庫
drop database 資料庫名
刪除裝置前必須刪除其上的所有資料庫，刪除裝置的命令是：sp_dropdevice
三、事務日誌
日誌檔案是用來記錄資料庫每一次修改活動的檔案。SQL Server中的每一個資料庫都有自己的日誌檔案，即系統表syslogs，也稱為事務日誌。事務日誌是撤消事務和出現故障時恢復事務的依據。
在某些情況下，事務日誌比資料本身更為重要。
什麼是事務？
資料庫的修改是以事務為單位進行的。一個事務就是一個操作序列，這些操作要麼全做，要麼全不做，它是一個不可分割的工作單位。任何一個事務具備如下特徵。
（1）執行的原子性（Atomic）；
（2）保持資料的一致性（Consistency）；
（3）彼此的隔離性（Isolation）；
（4）作用的永續性（Durability）。
上述事務的四個特徵被稱為事務的ACID準則。
事務在執行過程中，SQL Server把事務開始、事務結束以及對資料庫的插入、刪除和更新等每一個操作作為一個日誌記錄存放到事務日誌中。事務中的更新操作首先在資料庫緩衝區（記憶體）中進行，緩衝區分別有用來記錄操作活動的資料頁（data page）和日誌頁（log page）。當執行到commit tran時，日誌頁首先從緩衝區寫到磁碟上，而後資料頁從緩衝區寫到磁碟上，即遵循“先與日誌（write_ahead log）”的原則，這樣保證出現故障的情況下，通過日誌能夠得到最大限度的恢復。恢復必須撤消發生故障時還未提交的事務，已完成的事務若仍有未從緩衝區寫到資料庫裝置中，還要重新執行該事務。
附：建立與裝載資料庫例項
use master
decl are @vedvno int
select @vdevno=max(convert(tinyint,substring(convert(binary(4),d.low),v.low,1)))+1
from master.dbo.sysdevices d,master.dbo.spt_values v
where v.type=’E’and v.number=3
declare @v_str char(2)
select @v_str=convert(char(2),@vdevno)
print @v_str
disk init
name=”YDDATA”,
physname=”D:Syb_DataYDDATA.dat”,
vdevno=@vdevno+1,
size=153600
disk init
name=”YDINDEX”,
physname=”D:Syb_DataYDINDEX.dat”,
vdevno=@vdevno+2,
size=102400
disk init
name=”YDLOG”,
physname=”D:Syb_DataYDLOG.dat”,
vdevno=@vdevno+3,
size=76800
create database YDMISDB
on YDDATA=300,YDINDEX=200
log on YDLOG=150
use YDMISDB
execute sp_addsegment indexdev,YTMISDB,YTINDEX
execute sp_dropsegment “default”,YTMISDB,YTINDEX
execute sp_dropsegment system,YTMISDB,YTINDEX
use master
load database YDMISDB from ‘d:yd_dumpydmis_backup.dmp’
online database YDMISDB
第四講 資料庫安全性與使用者管理
一、安全管理概述
資料庫的安全性是指保護資料庫以防止不合法的使用所造成的資料洩露、更改或破壞。SQL Server的安全管理是一種基於角色（role）的管理方法，位於不同許可權層次（或角色）的使用者具有不同的使用者許可權。
規定使用者許可權有三個因素：使用者、資料物件和操作，即什麼使用者在哪些資料物件上可以執行什麼操作。
訪問SQL Server中資料的四道屏障：
（1）作業系統——作業系統登入
（2）Sybase伺服器——伺服器登入
（3）Sybase資料庫——資料庫使用者登入
（4）資料庫物件——物件授權
二、理解資料庫角色
角色是許可權的集合。
1．系統預定義角色
一個真正的資料庫管理員應該是整個伺服器及其資料庫的擁有者，具有管理伺服器及其資料庫的所有許可權。在一個大的系統中，資料庫管理員往往不只由一人承擔，而是將管理任務進行功能劃分，每人提當不同的責任。系統預定義角色就是為適應這種要求而設計的。
●系統管理員（sa_role）
sa_role角色擁有執行與資料庫具體應用無關的管理許可權：
◇安裝與更新SQL Server
◇管理伺服器的物理儲存
◇配置系統設定引數；
◇建立使用者資料庫；
◇授予SQL Server使用者許可權
◇┅┅
●系統安全管理員（sso_role）
sso_role用來實施安全上敏感的操作，執行有關安全性的任務：
◇建立伺服器登入帳戶；
◇管理口令；
◇授予除sa_role之外的任何角色；
◇管理審計系統；
◇┅┅
●操作員（oper_role）
oper_role執行全伺服器範圍的資料庫操作，如備份與恢復任意資料庫等：
◇轉儲資料庫與日誌
◇轉載資料庫與日誌
◇┅┅
2．建立與使用自定義角色
（1）建立新的角色：create role
（2）賦予角色許可權：grant
（3）設定登入帳戶的角色許可權：sp_role
格式：sp_role “grant”|”revoke”,角色名，登入帳號名
這裡的“grant”|”revoke”是指對角色許可權是授予還是撤消。
三、伺服器的帳戶管理
要想成為Sybase系統的一個使用者，首先必須擁有自己的登入帳戶。通過這一帳戶連同自己的口令向伺服器註冊後，才能使用Sybase系統中的一些資源，每一個這樣的登入帳戶在系統中被賦予Server級的標識SUID。
1．新增登入
sp_addlogin 登入名，口令[，預設的資料庫]
如果不指定預設的資料庫，則該帳戶的預設資料庫為master庫。
2．更改登入屬性
◇更改口令；
◇鎖定帳戶；
◇期限設定；
◇賦予角色。Sybase
四、資料庫使用者管理
通過某一個帳戶註冊到伺服器後，要想使用某一個資料庫，必須成為該資料庫的一個使用者。通過建立登入與使用者之間的聯絡來管理對資料庫的訪問。
1．組的概念
組（group）是資料庫使用者的集合。屬於某個組的使用者，他自動獲得該組所擁有的許可權。
Sp_addgroup 組名
◇將一個使用者加入某個組：（1）改變使用者屬性；（2）建立新使用者時指定。
◇所有使用者自動屬於public組，即使這個使用者已經屬於其他組了。
2．建立新的使用者
sp_adduser 登入名，使用者名稱[，組名]
3．別名使用者
Sybase的別名機制可以使得多個帳戶對應於同一個資料庫使用者，這樣使不同的登入在資料庫中擁有同樣的許可權。這在審計系統中是一種控制責任的一種手段。
Sp_addalias 登入名，資料庫中的使用者名稱
別名——相當於給某個使用者配一把別人的鑰匙（登入）。
五、使用者的許可權管理
控制使用者對資料庫物件的訪問，有兩類許可權：命令許可權和物件許可權。
1．命令許可權
Create Database
Create Table
Create View
Create Procedure
Create Rule
Create default
命令許可權授權與收權：
Grant 命令許可權組合 to 組名|使用者名稱|角色
Revoke 命令許可權組合 from 組名|使用者名稱|角色
2．物件許可權
Select
Update
Insert
Delete
Reference
Execute
物件許可權授權與收權：
Grant 物件許可權組合 on 資料庫物件 to public|組名|使用者名稱|角色
[With Grant Option]
Revoke 物件許可權組合 on 資料庫物件 to public|組名|使用者名稱|角色
From public public|組名|使用者名稱|角色
[Cascade]
授權與收權舉例：
◇Grant Insert,Delete on Employee
to user_1,Group_1
◇Grant Execute on Pro_culculate
to public
◇Grant Select on Employee(emp_id,emp_name)
to user_3
◇Grant All on Employee
to user_4
◇Revoke update on Employee(emp_id,emp_name)
from user_5
◇Revoke Create Table,Create Rule
from user_6
第五講 資料庫的備份與恢復
資料庫的備份與恢復是SQL Server保障資料安全的一種重要手段，為防止意外，資料庫管理員必須定期和經常製作資料庫的備份。一旦系統出現故障，資料能夠得到及時的恢復。
一、基本概念
1．事務處理及其日誌
SQL Server使用事務來跟蹤所有資料庫變化。事務是SQL Server的工作單元。一個事務包含一條或多條作為整體成功或失敗的T_SQL語句。每個資料庫都有自己的事務日誌，即系統表syslogs，事務日誌自動記錄每個使用者發出的每個事務，它飲食了每個事務足夠多的資訊，以確保資料能夠被恢復。
2．檢查點（CheckPoint）
伺服器在何時更新資料？
——在檢查點。在伺服器發出一個檢查點時：（1）更新資料；（2）在日誌中記錄下執行檢查點的標記。
檢查點可把所有“髒頁”寫到資料庫裝置上，“髒頁”是指從上一次檢查點以來，在記憶體中修改、但沒有在磁碟上修改的頁。SQL Server的自動檢查點機制保證了被完成的事務修改的資料頁有規律地從記憶體中的緩衝區寫到資料庫裝置上。
二、資料庫備份
若硬體介質出現故障（如磁碟損壞），當且僅當事先已對資料庫及其事務日誌作了備份，才能恢復資料庫。
注意：絕對不要使用作業系統的拷貝資料庫裝置，把這樣一個拷貝裝入SQL Server將導致大量資料庫受損。
備份的型別：
完全備份（）
增量備份——備份事務處理日誌
說明：
（1）只有把事務日誌放在單獨的裝置上，才能進行增量備份；
（2）備份事務日誌會截斷日誌，因此備份的內容是自上次備份以來的事務處理。
（3）備份之前要啟動備份伺服器，並最好建立轉儲裝置。
命令語法：
dump database 資料庫名
to 轉儲裝置名/物理檔名
dump transaction 資料庫名
{with {truncate_only|no_log}
to 轉儲裝置名/物理檔名
[with No_truncate]
Truncate_only與no_log選項用於刪除事務處理而不作拷貝。Truncate_only截斷日誌；在事務處理日誌完全滿時用no_log，它不為資料庫建立檢查點。兩個選項都會丟掉日誌。當使用了這兩個引數後，應及時備份整個資料庫。
No_truncate拷貝日誌但不截斷日誌，在出現介質錯誤時使用該選項。
圖形介面的選項與命令引數的對應關係：
（1）dump transaction （2）dump transaction…… with no_truncate
（3）dump transaction…… with truncate_only
（4）dump transaction…… with no_log
三、資料庫的恢復
使用load database載入備份到現有資料庫，資料庫可以是用於建立轉儲的資料庫，也可以不是。語法為：
load database 資料庫名 from 轉儲裝置名/物理檔名
load transaction資料庫名 from 轉儲裝置名/物理檔名
●利用備份恢復資料庫舉例：
某資料庫資料和日誌分別儲存在兩個獨立的磁碟上，正常運轉時的執行的備份計劃如下，每天的17:00執行整個資料庫的備份，每天的10:00、12:00、14:00、16:00點執行增量備份：
週一17:00磁帶1（100M）週二10:00磁帶2（30M）週二12:00磁帶3（30M）週二14:00磁帶4（30M）週二16:00磁帶5（30M）週二17:00磁帶6（30M）
DumpdatabaseDumptransactionDumptransactionDumptransactionDumptransactionDumpdatabase
若資料磁碟在週二的下午六點損壞，可以採用如下步驟恢復資料庫：
（1）使用dump transaction with no_truncate獲得當前的事務日誌轉儲，磁帶7；
（2）使用load database轉載最新的資料庫轉儲，磁帶6；（offline）
（3）使用load transaction提交最新的事務日誌轉儲，磁帶7；
（4）使用online database把資料庫狀態設定為online。
若資料磁碟在週二的下午4:50損壞，恢復過程如下：
（1）使用dump transaction with no_truncate獲得當前的事務日誌轉儲，磁帶7；
（2）使用load database轉載最新的資料庫轉儲，磁帶6；（offline）
（3）使用load transaction依次裝載磁帶2、3、4、5上的事務日誌；
（4）使用load transaction提交最新的事務日誌轉儲，磁帶7；
（5）使用online database把資料庫狀態設定為online。
四、制定備份與恢復的策略
由於事務日誌在恢復資料庫中的特殊作用，應定期備份資料庫及其事務日誌，而且事務日誌的備份要更頻繁一些。如：資料庫每週備份一次，事務日誌每天備份一次。
第六講 資料庫與T-SQL語言
一、關係模型的基本概念
關聯式資料庫以關係模型為基礎，它有以下三部分組成：
●資料結構——模型所操作的物件、型別的集合
●完整性規則——保證資料有效、正確的約束條件
●資料操作——對模型物件所允許執行的操作方式
關係（Relation）是一個由行和列組成的二維表格，表中的每一行是一條記錄（Record），每一列是記錄的一個欄位（Field）。表中的每一條記錄必須是互斥的，欄位的值必須具有原子性。
二、SQL語言概述
SQL（結構化查詢語言）是關聯式資料庫語言的一種國際標準，它是一種非過程化的語言。通過編寫SQL，我們可以實現對關聯式資料庫的全部操作。
●資料定義語言（DDL）——建立和管理資料庫物件
●資料操縱語言（DML）——用來查詢與更新資料
●資料控制語言（DCL）——控制資料的安全性
T-SQL語言是Sybase對SQL92標準的一種擴充套件，主要在它的基礎上增加了三個方面的功能：自己的資料型別/特有的SQL函式/流程控制功能
T-SQL中的識別符號使用說明：
（1）識別符號由1-30個字元或數字構成，但首字元必須為字母。臨時表的表名以#開頭，長度不能超過13個字元。
（2）資料庫物件的標識方法舉例
database.owner.tablename.columnname
執行遠端儲存過程：
EXEC server.db.owner.proc_name
當執行語句在批處理的句首時，EXEC可以省略。
三、Sybase的資料型別
在建立表或宣告區域性變數時，必須使用Sybase系統預定義型別。
1．字元型別
Char(n) VarChar(n)
2．數值型別
整數型別——Integer SmallInt TinyInt
浮點型別——Real Float Number[P,S] Decimal[P,S]
貨幣型別——Money SmallMoney
3．日期/時間型別
Datetime SmallDatetime
兩者時間部分的精度不同，前者精確到分，後者精確到1/30秒。
4．文字和影像型別
Text Image
5．二進位制資料型別
Binary(n) VarBinary(n)
四、資料定義語言
用來定義資料庫物件。資料庫物件是Sybase用來儲存資料的邏輯實體，主要有：
表（Table）、檢視（View）、臨時表（Temp Table）；
主鍵（Primary Key）、外來鍵（Foreign Key）、索引（Index）、規則（Rule）、預設值（Default）；
儲存過程（Stored Procedure）、觸發器（Trigger）
●基本語法
下面給出建立主要資料庫物件的語法：
1．表
建立表的基本語法是：
Create table[database.[owner].]table_name
(column_name datatype [default {constant_expression|user|null}]
{[{identity|null|not null}]|[[constraint constraint_name]
{{unique|primary key}[clustered|nonclustered]
[with{fillfactor|max_rows_per_page}=x]
[on segment_name]
|references[[database.]owner.]ref_table
[(ref_column)]
|check(search_condition)}]}…
在建立大型的資料庫時，可以考慮將建立表乃至其它資料庫物件的過程寫到一個文字里，當資料庫系統出現問題時，在最壞的情況下，重建過程可以得到簡化，也能比較好的對資料庫的建設過程進行監視。
建立表的過程完成下列活動：
·定義表的每一列；
·定義列名和列的資料型別並指定列是否處理空值；
·指定列是否具有IDENTITY屬性；
·定義列級的完整性約束和表級的完整性約束
上述過程可見，建立表的過程可以設定填充因子，將列置於段上，設計索引，外來鍵等等。
2．索引
索引對查詢效能的影響很大，要引起重視。
索引加速了資料檢索，Adaptive Server有三類索引：
·複合索引——索引包含多列；當兩列或多列由於它們的邏輯關係而作為整體被查詢時可建立這種索引；
·唯一索引——索引列的值不允許重複；
·簇聚索引和非簇聚索引——簇聚索引強迫Server不斷地對錶中資料排序或重排序以保證表中資料的物理順序和邏輯順序的一致性，簇聚索引對範圍查詢效能影響極大；非簇索引沒有這樣的要求，非簇聚索引對修改操作有利。
何時建索引？
·如果手動插入identity列，則建立唯一索引以保證不插入已經存在的值；
·經常被排序訪問的列，即被列在order by子句中的列，最好對其建立索引以便Adaptive Server能充分利用索引順序的優點；
·如果列經常用手連線，則可對列建立索引，這樣系統能更快地執行連線；
·包含主鍵的列一般都有簇聚索引，尤其是當它頻繁地和其它表的列相關聯；
·經常被範圍查詢的列最好為其建立簇聚索引，一旦查詢範圍內的第一個值被發現，則隨後的值在物理上一定相近。簇聚索引對單值查詢並沒有什麼優點。
建立索引的基本語法：
Create [unique][clustered|nonclustered]index index_name
On [[database.]owner.]table_name
(column_name[,column_name]…)
[on segment_name][with consumers=x]
上述語法包含了這樣的暗示：將簇聚索引和它的基表分離在不同的段上；段是邏輯概念，但段可以位於不同的物理裝置上，也即將簇聚索引和基表物理上分開。 這是不允許的，我們將在後面討論裝置、資料庫、段、表分割槽時作詳細討論。
3．鍵（key）
理解鍵是理解關聯的關鍵。
鍵和索引往往是一回事。鍵的意義在概念上，鍵用於參照完整性約束。
主鍵是表的單值列的集合，主鍵通過在放置它們的表上建立一個單值索引來實現其單值性的。實際上主鍵是作為標誌表的標誌符而存在的，一旦主鍵確定，則由該主鍵就確定了的表也就確定了。
外來鍵是和其它表中的主鍵相關的列，主鍵和外來鍵的關係確定了外來鍵的值域，該值域即為相應主鍵的取值範圍。這樣就從理論上強制實現了表與表之間的參照完整性。
前面建立表的語法裡包含了建立鍵的成分。也可以通過其它途徑建立主鍵和外來鍵。
◇Unique約束和Primary key約束的區別
Unique約束和Primary key約束用來保證同一表中指定的列上沒有重複值，這兩個約束都產生唯一索引確保資料一致性，預設情況下，Unique約束產生唯一的非聚集索引，Primary key約束產生唯一的聚集索引。Primary key約束比Unique約束嚴格：Primary key列不允許有空值，Unique列允許有空值。
4．檢視
檢視是檢視多表中資料的方法，檢視從基表派生，它並非物理存在，而是邏輯表；檢視也系統提供管理表的一種安全機制。檢視使得使用者集中精力在感興趣的資料集上。
建立檢視的語法：
create view [[database.]owner.]view_name
[(column_name[,column_name]…)]
as select [distinct] select_statement
[with check option]
有distinct關鍵字的檢視不能更新。當檢視涉及關聯時，定義檢視要小心，這時是對多表操作，完整性顯得很重要。
五、資料操縱語言
1．Select語句
基本語法：
SELECT[all|distinct]欄位列表
[into表名]
[from表名]
[where條件表示式]
[group by [all]欄位列表]
[having篩選表示式]
[order by 欄位列表[asc|desc]]
[compute聚集函式列表[by欄位列表]]
注意：Select語句中的子句必須按照上述順序使用。也就是說，若該語句包括一個group by子句和一個order by子句where，group by子句必須放在order by子句之前。
Having子句類似於where子句，不同之處有兩點：（1）Having子句必須結合group by子句使用；（2）where子句不能用聚集函式，而Having子句可以。
下面通過例項來對Select的通常用法加以介紹。
例1：選擇所有的列，語法為select * from table_list
如：select * from publishers
例2：選擇指定的列，語法為
select column_name[,column_name]…
from table_name
如：select pub_id,pub_name from publishers
例3：重新命名查詢結果中的列，語法為
select column_heading= column_name
from table_name
如：select Publisher=pub_name,pub_id
from publishers
例4：select列表中的計算值，可以對select列表中的數值資料進行計算，下面列出了算術運算子。
符號運算
+加
-減
/除
*乘
%取模
如select title_id,total_sales,total_sales*2 from titles
例5：使用distinct消除重複的查詢結果
可選的關鍵詞消除select語句的結果中的重複行。若不指定distinct，預設值為all，將檢索出包含重複行的所有行資料。
如：select distinct au_id from titleauthor
例6：選擇行——where語句
select語句中的確切指定要檢索哪些行的準則，其一般格式為：
select select_list from table_list where search_conditions
where子句中的搜尋條件（或稱限制）包括：
·比較運算子（＝，＜，＞，!=等＝
如：where advance*2＞total_sales*price
·範圍（between和not between）
如：where total_sales between 5000 and 10000
·列表（in和not in）
如：where state in(“CA”,”IN”,”MD”)
·匹配字元（like和not like）
如：where phone like “0535%”
·未知值（is null和is not null）
如：where advance is null
·以上各項的組合（and, or）
如：where advance＜5000 or total_sales between 500 and 1000
例7：用集合函式小結查詢結果
集合函式用特定列的資料來計算小結值。
集合函式結 果
Sum([all|distinct]expression)數值列中（不重複）值的總和
Avg([all|distinct]expression)數值列中（不重複）值的平均
count([all|distinct]expression)列中（不重複）非空值的數目
Count(*)選定的行數
Max(expression)Expression的最大值
Min(expression)Expression的最小值
如：select avg(advance),sum(total_sales)
from titles
where type=”as”
select count(*) from titles
select avg(distinct price) from titles
select max(price) from books
例8：分組組織查詢結果——group by 子句
group by 子句用在select語句中將一張表分成若干組。
如：select type, advance from titles group by type
例9：選擇分組資料——having子句
having為group by 子句設定條件，與where為select語句設定條件一樣。Having搜尋條件與where相同，但having可包括集合函式，而where不能包括。
下列語句使用帶集合函式having子句的例子。它把title表中的行按型別分組，但去掉了那隻包含一本書的分組。
Select type from titles group by type having count(*)>;1
下面是一個不帶集合函式的having子句的例子。它把title表中的行按型別分組，但去掉了那些不以字母“p”開頭的型別。
Select type from titles group by type having type like “p%”
例10：查詢結果排序——order by子句
Order by子句允許按一列或多列對查詢結果排序。每個排序可以是升序的（asc）或降序的(desc)。若不特別指明，則按升序進行。下列查詢返回按pub_id排序的結果：
Select pub_id,type,title_id from titles order by pub_id
例11：連線——從多張表中檢索資料
連線兩張或兩張以上的表是這樣一個過程：比較指定欄位中的資料，根據比較結果用符合條件的行組成一張新表。
舉例：
select publishers.pub_id,publishers.pub_name,authors.*
from publishers,authors
where publishers.city=authors.city
例12：分組計運算元句
Compute是Sybase對SQL標準中Group子句的擴充，可以將其看作帶聚集計算的Group子句。例如：
Select type,price,advance
From titles
Order by type
Compute sum(price),sum(advance) by type
2．Insert語句
用Insert命令向資料庫中新增行有兩種方法：使用關鍵詞values或使用select語句。
Insert語句的基本語法為：
Insert[into]表名[（欄位列表）]
{values（值列表）|select_statement}
舉例：insert into publishers
values(‘1622’,’Jardin,Inc.’,’Camden’,’NJ’)
Insert into publishers(pub_id,pub_name)
values(‘1756’,’The Health Center’)
Insert authors select * from newauthors
Insert authors(au_id,address,au_lname,au_fname)
Select * from newauthors
3．Delect語句
Delect可以對一行或多行進行操作。
Delect語句的基本語法為：
Delect 表名
[from 表名列表]
[where條件表示式]
舉例：Delect publishers
where pub_name=”Jardin,Inc.”
Delect titles
From authors, titles
Where titles.title_id=authors.title_id
4．Update語句
可以使用Update命令來改動表中的單個行、一組行或所有行。
Update語句的基本語法為：
Update表名
Set column_name1={expression1|null|(select_statement)}
[,column_name2={expression2|null|(select_statement)}]
[……]
[from 表名列表]
[where 條件表示式]
舉例：
update authors set_au_lname=”Health”,aufname=”Goodbody”
where au_lname=”Bloth”
update titles
set total_sales=total_sales + qty
from titles,sales
where titles.title_id=sales.title_id
六、Sybase預定義函式
1．聚集函式
sum([all|distinct]表示式)
avg([all|distinct]表示式)
count([all|distinct]表示式)
count(*)
max(表示式)
min(表示式)
2．字串函式
upper(字元表示式)
lower(字元表示式)
char(整型表示式)
char_length(字元表示式)
ltrim(字元表示式)
rtrim(字元表示式)
……
3．數學函式
abs(精確小數型表示式)
floor(精確小數型表示式)求小於或等於給定表示式值的最大整數（取底）
rand([整數型]
round(精確小數型表示式,整數)
sign(精確小數型表示式)
power(精確小數型表示式,整數冪)
……
4．日期函式
getdate()
datepart(日期部分，日期)
datediff(日期部分，日期1,日期2)
dateadd(日期部分，數值表示式，日期)
5．型別轉換函式
convert(資料型別，表示式[，格式])
6．系統函式
db_name([資料庫ID])
host_name()
isnull(表示式1,表示式2)
……
七、資料控制語言
用來控制資料的安全性，如許可權控制語句GRANT和REVOKE等。
第七講 資料庫程式設計基礎
一、批處理
SQL Server可以處理作為一批而提交的多個SQL語句，既可以是互動式的，也可以是一個檔案。批處理SQL語句由批結束標誌終止，該標誌指示SQL Server從前面開始執行該批處理語句，對於獨立的SQL實用程式isql而言，其批結束標誌為單獨佔一行的“go”。
舉例：選擇表title及表authors的行數
select count(*) from titles
select count(*) from authors
go
二、流程控制語言
1．變數宣告與賦值
全域性變數由系統預定義，以符號@@打頭。
區域性變數宣告使用Declare語句，這個變數必須以符號@開頭，後跟一個識別符號。
Declare @變數名 資料型別[，@變數名 資料型別，……]
變數賦值使用Select語句，未賦值的變數其值為Null。
舉例：
Declare @msg char(50)
Select @msg=’How are you?’
Select @msg=emp_name from employee
Where emp_id=12345678
2．SQL語句塊
Begin
Statement Block/*多個順序執行的SQL 語句*/
End
3．條件語句
If 條件表示式
語句（塊）
Else
語句（塊）
舉例：
if(select max(id) from sysobjects)<50
print ‘資料庫裡沒有使用者建立的物件‘
else
select name,type,id from sysobjects where id>;50
4．迴圈語句
While 條件表示式
語句（塊）
●兩個特殊的迴圈控制語句：
Continue 執行下一次迴圈
Break 退出當前迴圈
舉例：
While(select avg(price) from titles)>;$20
Begin
Update titles set price=price/2
If(select avg(price) from titles) Break
Else
Continue
End
5．其它控制語句
◇Return語句——無條件結束當前過程，並可返回給呼叫者的一個狀態值：Return[整數表示式]
◇Print語句
◇RaiseError語句
◇Waitfor語句
三、儲存過程
儲存過程是儲存在伺服器端的一類資料庫物件，它實質上是一段用SQL語言編寫的程式，它在伺服器端預先經過編譯，並確定出執行計劃，因此與同樣功能的批處理語句相比，它的執行速度較快。
基本語法：
Create Procedure[owner.]過程名
[@引數名 資料型別[=預設值][Output]]
[，@引數名 資料型別[=預設值][Output]]
[……]
AS
Begin
SQL語句（塊）
End
儲存過程是資料庫物件，和表、索引是一個級別的；是SQL語句和控制流語言的集合，儲存過程在首次執行時被編譯，並駐留在過程快取記憶體的記憶體中，所以儲存過程的招待非常快。儲存過程可以帶引數，可以呼叫其他過程，返回狀態值，返回引數值，並且可以在遠端SQL Server執行。可以在遠端SQL Server執行對資料庫設計有特別重要的意義。SQL Server提供的儲存過程稱為系統過程。
儲存過程大大增強了SQL的能力、效率和靈活性，經過編譯的儲存過程極大地改善SQL語句和批處理的效能。
儲存過程有很多優點：
●儲存過程在第一次執行時編譯，並儲存在過程快取記憶體的記憶體中。編譯時系統對其進行優化，以選擇最佳的路徑來訪問資料集中的資料，這種優化考慮了資料集的實際資料結構。因此儲存過程大大提高了系統的效能。
●儲存過程可以跨伺服器執行。這一點是通過觸發器來實現的，當然，首先儲存過程要能登入到該遠端伺服器。
●應用程式也能執行儲存過程，從而實現伺服器和客戶之間的協同作業。
●儲存過程減少了網路的交通。這是因為儲存過程的文字儲存在資料庫裡，呼叫儲存過程時通過網路的只是儲存過程的過程名。
●利用儲存過程可以提供一個附加的安全層。
如（該例子取自pubs2資料庫）：
Create proc titleid_proc(@title_id varchar(80))
As
Begin
Select @title_id=lower(@title_id)+”%”
Select title,title_id,price
Form titles
Where lower(title_id) like @title_id
Return @@rowcount
End
注意例子中的黑體部分，這實際上是一條賦值語句。該儲存過程有返回值。
儲存過程可以變得非常複雜。我們認為，建立儲存過程還是要遵循“最簡單就是最好”的原則。建議在建立儲存過程時採用縮排風格，否則建立的儲存過程三天之後連自己都看不懂。
需要對儲存過程作些說明：
●Create procedure 語句不能和其他語句在同一個批命令裡。
●Create procedure 語句不能包括下列語句：
use
Create View
Create default
Create rule
Create trigger
Create procedure
不能使用use語句好理解，儲存過程是針對資料庫的，不能在一個資料庫裡訪問另外的資料庫。如果在儲存過程裡訪問另外的資料庫，則資料庫表的參照完整性難於得到保障。
從另外幾條語句看，在儲存過程裡一般不能建立新的資料庫物件。但可以建立表和索引，以及和表相關聯的鍵，表是臨時表，在儲存過程結束後不能看見建立的臨時表；否則的話每執行一次儲存過程就建立一個表，結果可想而知。
儲存過程裡不能建立一個物件，刪除它；然後又在同一儲存過程裡用相同的名字建立新的物件。實際上，SQL Server在儲存過程執行時而不是在編譯時建立物件的。
●如果儲存過程呼叫另外的儲存過程，則第二個儲存過程可以呼叫在第一個儲存過程裡建立的物件。
●儲存過程包含的最多引數為255個，對儲存過程裡的區域性和全域性變數沒有限制。
最後討論一下系統儲存過程。系統儲存過程以sp_開頭，當然使用者建立的儲存過程也可以以sp_開頭；系統過程儲存在sybsystemprocs資料庫裡。系統過程的使用有許可權，如果打入系統過程名但沒有出現預期的結果，要麼是命令名錯，要麼是使用者沒有該過程的許可權。一般可通過系統管理員或資料庫所有者對系統過程的execute授權。
系統過程繁多，大致有幾類：
a. 使用者標誌和授權。這一類的過程主要由於：增加、刪除或報告在SQL Server上的登入，增加、刪除或報告某資料庫的使用者、分組或別名等。這類過程有sp_addlogin,sp_adduser,sp_helpgroup,sp_dropuser等。
b. 遠端過程的呼叫。這類過程用於：增加、刪除或報告能存取本SQL Server的遠端伺服器；增加能從遠端伺服器上存取本SQL Server的使用者名稱。這類過程有：sp_addremotelogin,sp_addserver,sp_dropserver等。
c. 資料定義和資料庫物件。這類儲存過程用於：連線和定義規則和預設值，增加、刪除或報告主碼、外碼和公共碼；增加、刪除或報告使用者定義的資料型別。這類儲存過程有：sp_bindfault, sp_bindrule, sp_help, sp_helpdb, sp_foreignkey, sp_helptext等。
d. 系統管理。這類儲存過程用於：增加、刪除或報告資料庫及轉儲裝置；報告鎖；設定的資料庫選擇及使用者正進行的程式；修改及報告配置變數；監控SQL Server的活動。這類過程有：sp_addumpdevice，sp_dropdevice, sp_helpdevice等。
四、觸發器
觸發器是一種用來保障參照完整性的特殊的儲存過程，它維護不同表中資料間關係的有關規則。當對指定的表進行某種特定操作（如：Insert,Delete或Update）時，觸發器產生作用。觸發器可以呼叫儲存過程。
建立觸發器的語法：
Create Trigger[owner.]觸發器名
On [owner.]表名
For {insert,update,delete}
As
Begin
SQL語句（塊）
End
定義一個好的觸發器對簡化資料的管理，保證資料庫的安全都有重要的影響。觸發器是針對表一級的，這就意味著，只有表的所有者有權建立表的觸發器。
舉例：
插入一個新行，必須保證外來鍵與主鍵相匹配，觸發器應該首先檢查被插入行與主鍵表的連線。
以下的觸發器對inserted表和titles表的title_id進行比較，這裡假設正在給外來鍵輸入資料，沒有插入空值，若連線失敗，事務被回退。insert,update,delete
Create trigger forinsertrigl
On salesdetail
For insert
As
If(select count(*)
From title,inserted
Where titles.title_id=inserted.title_id)!=@@rowcount
Begin
Rollback transaction
Print “No,some title_id does not exist in titles.”
End
Else
Print “Added! All the title_id is exist in titles.”
在本例中，@@rowcount代表新增到salesdetail表的行數，這也是新增到inserted表中的行數。通過連線表titles和表inserted來檢測所有新增到salesdetail的title_id是否在titles中存在。若所連線的行數（count(*)）與@@rowcount不同，由有一個或多個插入不正確，整個事務被取消。
觸發器的限制：
●一個表最多隻能有三個觸發器，insert,update,delete
●每個觸發器只能用於一個表
●不能對檢視、臨時表建立觸發器
●Truncate table能刪除表，但不能觸發觸發器
●不能將觸發器用於系統表
合理地使用觸發器對效能的影響是正面的。在設計和使用觸發器時，經常地用sp_depends命令瞭解物件所關聯的觸發器是有好處的，該命令能列出觸發器影響的所有物件、表和視等。
在定義幾類資料庫物件的時候，對儲存過程、索引和觸發器要給予特別的注意，尤其儲存過程，它設計的好壞對資料庫效能的影響很大。
說明：Sybase觸發器使用的兩個測試表：Deleted表和Inserted表，它們都是臨時表，其結構與觸發器的基表結構相同，用來存放與修改相關的資料行。
五、遊標
1．遊標的概念
遊標是指向查詢結果集的一個指標，它是一個通過定義語句與一條Select語句相關聯的一組SQL語句。遊標包含兩方面的內容：
●遊標結果集：執行其中的Select語句所得到的結果集；
●遊標位置：一個指向遊標結果集內的某一條記錄的指標
利用遊標可以單獨操縱結果集中的每一行。遊標在定義以後存在兩種狀態：關閉和開啟。當遊標關閉時，其查詢結果集不存在；只有當遊標開啟時，才能按行讀取或修改結果集中的資料。
2．使用遊標
一個應用程式可以使用兩種型別的遊標：前端（客戶）遊標和後端（伺服器）遊標，它們是兩個不同的概念。
無論使用哪一種遊標，都需要經過如下幾個步驟：
●定義遊標
●開啟遊標
●從遊標中運算元據
●關閉遊標
下面講述的是後端（伺服器）遊標。
（1）定義遊標
在使用遊標之前必須宣告它。宣告指定定義遊標結果集的查詢。通過使用for update或for read only關鍵詞將遊標顯式定義成可更新的或只讀的。
Declare cursor語法為：
For select_statement
[for{read only|update[of colum_name_list]}]
舉例：
declare pubs_crsr cursor
for select pub_name,city,state
from publishers
for update of city,state
（2）開啟遊標
open的語法為：
open 遊標名
在宣告遊標後，必須開啟它以便用fetch,update,delete讀取、修改、刪除行。在開啟一個遊標後，它將被放在遊標結果集的首行前，必須用fetch語句訪問該首行。
（3）從遊標中讀取資料
在宣告並開啟一個遊標後，可用fetch命令從遊標結果集中獲取資料行。
Fetch的語法為：
Fetch 遊標名[into 變數列表]
舉例：fetch pub_crsr into @name,@city,@state
SQL Server在每次讀取後返回一個狀態值。可用@@sqlstatus訪問該值，下表給出了可能的@@sqlstatus值及其意義。
值意 義
0Fetch語句成功
1Fetch語句導致一錯誤
2結果集沒有更多的資料，當前位置位於結果集最後一行，而客戶對該遊標仍發出Fetch語句時。
若遊標是可更新的，可用update和delete語句來更新和刪除行。
刪除遊標當前行的語法為：
delete[from]表名
where current of 遊標名
舉例：delete from authors where current of authors_crsr
當遊標刪除一行後，SQL Server將遊標置於被刪除行的前一行上。
更新遊標當前行的語法為：
update 表名
set column_name1={expression1|NULL|(select_statement)}
[,column_name2={expression2|NULL|(select_statement)}
[……]
where current of 遊標名
舉例：
update publishers
set city=”Pasadena”,state=”CA”
where current of pubs_crsr
（4）關閉遊標
當結束一個遊標結果集時，可用close關閉。該語法為：
close 遊標名
關閉遊標並不改變其定義，可用open再次開啟。若想放棄遊標，必須使用deallocate釋放它，deallocater的語法為：
deallocater cursor 遊標名
deallocater語句通知SQL Server釋放Declare語句使用的共享記憶體，不再允許另一程式在其上執行Open操作。
六、事務——維持資料一致性和恢復
1．事務的定義
事務提供了一種將T-SQL語句分組的方法，從而使它們能夠被當成一個單元來處理：組中所有語句或都執行，或都不執行。
事務是確保一個或多個SQL語句的集合不被當成單一工作單元處理的機制，SQL Server自動將所有資料修改命令，包括單步改變請求，作為事務處理，預設時，每個insert、update、delete語句被當成一個事務處理。
2．事務的作用
事務使SQL Server能保證：
·一致性——同時發生的查詢或改變請求彼此不衝突，使用者不能對正處於改變過程的資料進行檢視或操作。
·恢復——在系統失效時，資料庫的恢復是完全的和自動的。
3．使用事務
a. 開始和確認事務
begin transaction和commit transaction可將任意數目的SQL語句封裝起來，這兩名的簡單語法為：
begin transaction
commit transaction
b. 回退事務
在commit transaction命令提交前任何時候可取消或回退事務，該命令的簡單語法為：
rollback transaction
4．檢查事務狀態
全域性變數@@transtate記錄了事務當前的狀態。在執行一個語句後SQL Server通過記錄所有事務變化來確定返回何種狀態。
@@transtate可包含下列值：
值意 義
0事務進行中：一個顯式或隱式事務有效；上一語句執行成功
1事務完成：事務完成並提交其變化
2語句異常中止：上一語句異常終止；對事務無影響
3事務異常中止：事務異常中止並回退所有變化
舉例：在事務中，可在一個語句（如insert）後使用@@transtate確定該語句成功或失敗對事務的影響。commit transaction
Begin transaction
Insert into publishers(pub_id)values(‘9999’)
(1 row affected)
select @@transtate
…………………
0
commit transaction
select @@transtate
…………………
0
(1 row affected)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242405/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 最基本財務基礎知識,財務知識基礎(ZT)
- SYBASE IQ 基本知識
- 基礎知識
- AI 基礎知識AI
- Webpack 基礎知識Web
- Dart基礎知識Dart
- RabbitMQ基礎知識MQ
- webpack基礎知識Web
- javascript基礎知識JavaScript
- ThinkPHP基礎知識PHP
- Laravel基礎知識Laravel
- Redis基礎知識Redis
- Docker基礎知識Docker
- 程式基礎知識
- Envoy基礎知識
- DockerFile基礎知識Docker
- Nginx基礎知識Nginx
- CSS基礎知識CSS
- Java基礎知識Java
- PRML 基礎知識
- BGP基礎知識
- PHP基礎知識PHP
- React基礎知識React
- httpclient基礎知識HTTPclient
- HBase基礎知識
- Jquery基礎知識jQuery
- js基礎知識JS
- PGA基礎知識
- Javascript 基礎知識JavaScript
- ASM基礎知識ASM
- html基礎知識HTML
- 黑客基礎知識黑客
- Mysqli基礎知識MySql
- oracle 基礎知識Oracle
- SEO基礎知識
- CMMI 基礎知識
- Kafka 基礎知識Kafka
- 1、基礎知識