DBMS :Database Management System
數據模型:概念數據模型 ----設計
???????? 邏輯數據模型（關系數據模型，網狀數據模型，層次數據模型，）----實現
???????? 物理數據模型
數據模式：描述
聯系，約束并發控制：多用戶訪問共享數據的沖突問題
實體—聯系：er模型
rdbms (關系數據庫)???? :? sql server ,oracle ,db/2,sybase,informix
Non-rdbms (file-based) :? foxpro,access
客戶端網絡實用工具(Client Network Utility)：用來配置客戶端的網絡連接，管理和測定客戶端的數據庫。
事件探察器：監視事件處理日志，并對日志進行分析和重播。
服務端網絡實用工具(Server Network Utility):配置服務器端網絡連接和設置相關參數。
導入和導出數據(Imput and Export Data):采用DTC(data transformation services)向導來完成，實現其他數據和SQL 2000數據的轉換。
企業版:win2000 advanced server
標準版:win2000 server
首要數據文件 .mdf
次要數據文件 .ndf
日志文件???? .ldf
windows身份驗證模式 比 混合模式具有更高的安全性，windows身份驗證模式下必須是本地用戶和域用戶才能使用SQL Server 2000
升級SQL SERVER:備份(包括系統庫在內的)所有數據庫，備份注冊表
日志文件：
sqlstp.log?? 在\windows或\winnt下
setup.log??? 在\windows或\winnt下
searchsetup.log? 在\winnt\temp下
errorlog???? 在mssql\log下
系統數據庫：
master:
model:? 是用戶數據庫和tempdb數據庫的摸板數據庫，是創建新數據庫的摸板
msdb:?? 是代理服務數據庫
tempdb: 臨時數據庫，默認大小8mb，為臨時操作提供存儲空間
pubs:?? 示范學習
系統表：
(
sysobjects表
syscolumns表
sysindexes表
sysusers表
sysdatabases表
sysdepends表
sysconstraints表
)
(主關鍵字(主鍵)：
候選關鍵字：
公共關鍵字：
外關鍵字：確定主表和從表)
數據庫對象：
(
表
索引
視圖：其實是虛表，是查詢表產生的
圖表：是表之間的關系示意圖
確省值：
規則：
觸發器：
存儲過程：
用戶：
)
范式：構造數據庫必須遵守的規則，目前關系數據庫有6種范式，第一范式(1NF)....，一般需要滿足3NF
SQL(structured query language)結構化查詢語言
9種語句
(
數據查詢：select
數據定義：create,drop(刪除),alter(修改)
數據操作：insert,update,delete
數據控制：grant,revoke
)
SQL語言分類：
(
數據定義語言(dll),用于定義數據結構
數據控制語言(dml)，用于檢索和修改數據結構
數據控制語言(dcl),用于規定數據庫用戶的權限
數據庫事務處理，用來保證數據庫的完整性
)
常用dll語句：
(
create table
create index
drop table??? 刪除數據庫表
drop index
truncate????? 刪除表中所有的行
alter table?? 增加表列，重定義表列，更改存儲分配
alter table add constraint?? 在已有的表上增加約束
)
常用dml語句:
(
insert
delete
update
selete
)
常用dcl語句:
(
grant? 將權限或角色授予用戶或其他角色
revoke 從用戶或數據庫角色回收權限
set role? 禁止或允許角色
)
事務處理語句：
(
commit work? 把當前事務所作的更改永久化
rollback???? 作廢上次提交以來的所有更改
)
數據表名稱 as 數據表別名，如果已經給表定了別名，在T-SQL語句中應引用別名，而不能用數據表名
select e.id,e.name from 用戶表 as e
select [all | distinct] [top n] selectlist [into[NewtAbleName]]? //distinct:無重復的,selectlist:可以是表達式
? from {TableName | ViewName,....}
where? conditions
[group by group_by_list]? // 分組?
[having conditions]?????? // 分組條件
[order by order_by_list [asc | desc]]
select cost*quantity as sum
select 數據表 join on (員工數據表.員工編號=項目數據表.負責人)???? //聯接條件
嵌套select:
select table1.a,tmptable.b
from table1,
(select table2.c,table2.d
from table2
where table2.id>2) as tmptable
where table1.id=tmptable.id
where:
比較：<,<=,>,>=,=,!=,!<,!>
范圍：between a and b , not between a and b
可選擇：in,not in
模式匹配：like，not like
是否空值：is null,is not null
邏輯：and,or,not
like通配符：
%? ： like 'wang%'??? like '%yi'??
-? ： like '_angyi'???
[]? ：like '[a-z]angyi'
[~] ：like '[~w]angyi'
in的用例：
select name
? from table
where id in ('4','20')
escape的用例：
select *
? from table
like 'wang#_yi' escape '#'
select id,count(*)
? from table
where wage>=6000
group by id
//group by 對查詢結果分組
select id,name
? from table
where level='2'
group by id,name???? //先按id分，在按name分
with cube??????????? //對group產生的組再按組統計
with rollup????????? //只返回第一個分組條件下的再按組統計
having 指定分組搜索條件，通常與group by一起用
having count(*)>1
select *
? from table
where col like '%50[%]%'? 返回包含50%的字符串
asc:升續
//compute計算? avg,sum,min,max,count (都會忽律null)
select id,wage
?? from table
order by 部門
compute sum(wage) by 部門
count(distinct 列名)
union
子查詢中一般不用Order by子句，Order by子句用于最終的結果
>all,<all,
<>all等價于not in
>some,<some
=some 等價于in? <>some等價于 not in
不要把If..exists和聚合函數一起使用
數據完整性：
實體完整性---表中數據的唯一性
區域完整性---字段數據范圍的完整性
參考完整性----表與表之間關聯的完整性
使用者定義的完整性----用戶自己定義的完整性。如：客戶欠6個月的錢，下次再下定單的時候就不賣給他。
數據庫分離：使用SQL語句分離為EXEC sp_detach_db ‘銷售數據庫’?指明欲分離的數據庫名稱即可。分離后想再用該數據庫，則需要附加該數據庫，具體SQL語句為：
CREATE? DATABASE 銷售數據庫 ON PRIMARY（FILENAME=’C:\SQLTEST\銷售數據文件_1。MDF’）
FOR? ATTACH 或者使用sp_attach_db系統存儲過程來附加數據庫：
sp_attach_db? 銷售數據庫
‘C:\SQLTEST\銷售數據文件_1.MDF’
?UNIQUE 和Primary key的差異
1、? UNIQUE允許輸入NULL值（最多為一個），而Primary key不允許；
2、? 一個數據表中可以定義多個UNIQUE條件約束，但只能定義一個Primary key條件約束。
CHECK約束可用來限制字段值是否在所允許的范圍內，例如：
CREATE TABLE 客戶02
(
客戶編號 int IDENTITY? NULL? UNIQUE,
身份證號 char[10] NOT NULL? UNIQUE,
年齡? int? CHECK (年齡>0) DEFAULT 2
)
建立計算列：
CREATE TABLE 估價
(
編號? int? IDENTITY,?????????????? //自動編號
單價? numeric(5,1),
數量? int,
總價? AS 單價*數量
)
insert 估價 values(21.5,8)
insert 估價 values(12,3)
delete from 估價 where 編號=5 or 編號=6
drop table 估價
創建約束字段：
Create table 客戶
{
客戶編號? int? IDENTITY? Primary Key,------------------------IDENTITY自動產生編號
身份證號? char 18? NOT NULL UNIQUE,
地址????? char(50),
電話????? char (12),
雜志編號? int? REFERENCES 雜志種類（雜志編號）,
訂單編號? int? NOT NULL,FOREIGN Key(雜志編號，訂戶編號)
REFERENCES? 雜志訂戶（雜志編號，訂戶編號），
Check(地址 is NOT NULL? OR 電話? is? NOT NULL)
}
修改數據表：
ALTER TABLE 客戶A
ADD
類別編號 int
DEFAULT 1 WITH VALUES
CONSTRAINT? FK_類型編號
FOREIGN KEY
REFERENCES? 客戶類別（類別編號）
刪除約束字段：
ALTER TABAL訂購項目A
DROP CONSTRAINT PK_訂購項目A
下面刪除訂購項目A的兩個字段
ALTER TABLE 訂購項目A
DROP COLUMN 定單編號，項目編號
激活/關閉約束
ALTER TABLE table
{ CHECK | NOCHECK} CONSTRAINT
{ALL | constrant_name{,…….n}}
臨時數據表以#或##開頭，用戶斷開連接后表被自動刪除
數據表中字段的種類：
Primary key,? Foreign Key,? NULL,? NOT NULL,? DEFAULT,? UNIQUE,? CHECK
用查詢結果建立新數據表
Select * into? newtablename? from table_source where 條件
重復記錄采用DISTINCT來獲取單獨的數據。
TOP? N 為顯示前N個數據，TOP? N? 30? PERSENT * FROM 表名
表示顯示N的前30%的數據
?ORDER BY 按什么方式排序顯示結果，ASC升DESC降
?JOIN ：SELECT? A,B,C? FROM? T1? JOIN? T2? ON? T1.NO=T2.NO
JOIN 的類型有多種：INNER（默認）LEFT（以左邊為條件，如果右邊沒有符合的條件，則以NULL）
??????? SELECT 旗.產品名稱AS旗旗公司產品名稱，旗.價格，標.成品名稱AS標標公司產品名稱，標.價格 FROM 旗旗公司 AS 旗 LEFT JOIN 標標公司AS標 ON旗.產品名稱=標產品名稱 RIGHT（以右邊為條件，如果左邊沒有符合的條件，則以NULL）FULL（左邊右邊都顯示，如果沒有則以NULL） CROSS（將兩個表的數據進行比較，全部顯示出來）
有時需要自己JOIN自己
SELECT 員工.姓名，員工.職位，長官.姓名AS主管
FROM 員工 LEFT JOIN 員工AS 長官
ON 員工.主管編號=主管.編號 姓名 職位 主管
?1
?a
?b
?c
?2
?d?
e?
NULL
GROUP BY 按什么分組顯示
UNION把兩個查詢結果合并到一個表中顯示出來查詢時Group的使用，?
Select name? AS名稱,Sum(Quantity)數量? Form? table? Group? By? name
GROUP BY后面如果有WITH CUBE則把各個組進行匯總計算后顯示結果?
查詢：select name? AS出版社名稱,bookname AS 書名,sum(quantity) AS 數量 from table group by name,bookname with cube
查詢時改變要顯示的數據：（1代表“男”，0代表“女”）要求查詢時如果是1則顯示“男”，否則顯示“女”
Select name? AS 姓名,case? sex? when? 0? then “女” else? “男”? End? AS 性別? From? table
?比較清單有：IN，ALL，ANY（SOME）結果為TRUE或FALSE
測試存在用EXISTS結果返回TRUE，FALSE
?索引分為聚集索引和非聚集索引
聚焦索引數據的存放會依照該索引的順序來存放
非聚焦索引數據的存放不會依照索引的順序來存放
因為非聚焦索引不影響數據排列順序，則可以設置多個非聚焦索引，而聚焦索引則最多只能設置一個，因為實際數據只能有一種排列順序。
(Unique index)索引值是否唯一：如果索引值為唯一，則成為唯一索引
多個字段做為索引稱為復合索引（Composite index）
Create? [UNIQUE]? -----指定唯一
CREATE? TABLE? t
(
ProductID? smallint? not null? primary key,
ProductName?? char(30),
Price?? smallmoney,
Manufacturer?? char(30)
)?????????
//創建索引
CREATE??UNIQUE???NONCLUSTERED????INDEX????indexname
ON???t?(Price?? DESC)
WITH???? PAD_INDEX, FILLFACTOR=30,IGNORE_DUP_KEY
//刪除索引
DROP? INDEX?? table.index_name
//修改索引
使用DROP_EXISTING修改索引,如果要修改索引，只要在CREATE??? INDEX語句的最后加上DROP_EXISTING即可（如果存在該索引，則修改之，否則會發生錯誤）
使用DBCC? DBREINDEX重建索引
如果只想重建索引（而不修改索引的相關設置），那么使用DBCC? DBREINDEX語句比較方便，而且使用時也可指定重建的填充因子。
DBCC DBREINDEX（’database_name.owner.table_name’,? index_name,?? fillfactor）數據庫? 數據庫所有者 表名? 重建的索引名 設置新的填充因子[WITH? NO_INFOMSGS]?? -------有此項，則重建索引不會顯示出信息
?存儲過程
系統存儲過程都是以 sp_開頭
擴展存儲過程通常以xp_開頭
EXECUTE 執行存儲過程或SQL字符串（EXEC）
EXEC? 存儲過程名?? 參數1?? 參數2?? 參數3……
?變量的聲明：
DECLARE? @A? int -------------- 生命了一個為int 類型的A 變量。
建立存儲過程
使用CREATE? PROC或者CREATE? PROCEDURE + 過程名 + AS +SQL語句
CREATE? PROC? MyProcl
AS? SELECT * FROM 標標公司? WHERE 價格 >5
GO
EXEC MyProcl
EXEC? lookup? ‘楊小雄’，@地址 OUTPUT---OUTPUT表示參數的值是可以返回的
一個存儲過程為一個批，因此在查詢分析器中遇到GO時，表示存儲過程的定義已結束.在存儲過程中,有些創建對象的語句不可使用
CREATE? DEFAULT?????? CREATE? TRIGGER
CREATE? PROCEDURE??? CREATE? VIEW
CREATE? RULE
臨時存儲過程分為局部和全局
局部要以#開頭
全局要以##開頭
修改存儲過程ALTER PROC
刪除存儲過程DROP PROC
建立函數CREATE? FUNCTION????????????????????
修改函數?ALTER? FUNCTION?????????????
刪除函數DROP? FUNCTION
返回單個數據的函數和一般函數一樣
返回大量數據集的函數如下例：
CREATE FUNCTION 依售價查詢書籍
（@由 money,? @到money）
RETURNS TABLE
RETURN? （SELECT 書籍編號，書籍名稱，單價 FROM 書籍 WHERE 單價>=@由AND單價<=@到）
GO
SELECT * FROM依售價查詢書籍（400，500）
ORDER? BY? 單價
在調用系統內置的函數時，需要加上?? ：：如：SELECT * FROM ：：fn_helpcollations()在創建表時可以使用這些函數。
?函數分為確定性函數和非確定性函數
確定性函數為當傳入的參數不變時，返回值不變，而非確定性函數為傳入參數不變，但每次返回的值會改變（如：GETDATE（）函數）
?觸發器（Trigger）是一種與數據表緊密結合的存儲過程，當該數據表有新建（INSERT）更改（UPDATE）或刪除（DELETE）事件發生時，所設置的觸發器會自動被執行，以進行數據完整性，或其他一些特殊的數據處理工作。
觸發器是針對單一數據表所撰寫的特殊存儲過程。觸發器的種類與操作：觸發器分為2類
AFTER觸發器：這類觸發器要在數據已變動完成之后（AFTER），才會被激活并進行必要的善后處理或檢查。若發現錯誤，則可用ROLLBACK? TRANSATION回滾全部數據。
INSTEAD? OF觸發器：INSTEAD? OF 是取代的意思，就是這類觸發器會取代原本要進行的操作（例如新建或更改數據庫操作），因此會在數據變動前就發生，而且數據要如何變動也完全取決于觸發器。（該觸發器能應用與數據表與視圖）
建立觸發器
CREATE? TRIGGER? trigger_name
ON? {table|view}
[with? encryption]??????? ------加密
{FOR|AFTER|INSTEAD OF}? ------FOR和AFITER都是要等到數據完全操作完后才激活觸發器，INSTEAD OF則表示要建立INSTEAD OF觸發器，此時觸發器將取代原來要執行的數據操作。
{[DELETE][,][INSERT][,][UPDATE]}-----指定觸發器的觸發操作，必須指定一個，多個時以逗號隔開，在同一數據表中，AFTER觸發器數目沒有限制；但對INSTEAD OF 觸發器來說則限制DELETE、INSERT、UPDATE每項最多只能有一個。
AS
Sql_statements??? ------用來定義觸發器的內容。
例如：下面對一個表建立2個觸發器：
CREATE? TRIGGER? 訂單修改通知
ON? 訂單
AFTER? INSERT ，UPDATE
AS
PRINT? ‘又有訂單被修改了！’
GO
CREATE TRGGER 訂單刪除通知
ON? 訂單
AFTER? DELETE
AS
PRINT? ‘又有訂單被刪除了！’
GO
INSERT 訂單(日期，客戶編號)
Values(‘2000/1/1’,3)
Delete訂單
Where 日期=‘2000/1/1’
?刪除觸發器：DROP? TRIGGER? trigger_name
?事務通常是由BEGIN? TRAN 語句開始，一直執行到COMMIT? TRAN或ROLLBACK? TRAN語句時才結束。
當執行完事務中的最后一項數據庫的操作后，若沒有任何錯誤，我們可以用COMMIT TRAN提交事務。反之，若發現在事務中有任何的錯誤，則執行ROLLBACK? TRAN取消事務，并回滾至事務執行前的狀態。
每執行完一項數據庫的操作后，要立即檢查@@ERROR和@@ROWCOUNT，否則再執行下一項數據庫操作時，這2個系統變量將會被新的值代替。在更改或刪除數據時，若因指定條件不符或其他原因而沒有更改到任何一條記錄，由于這種情況并不是錯誤（@@ERROR仍為0），因此我們還要檢查@@ROWCOUNT的更改條數，以確定數據已正確更改。
?無論是COMMIT TRAN還是ROLLBACK TRAN而結束的事務后，如果后面還有其他未執行的語句，則還會繼續執行這些語句，直到批結束。不過已COMMIT的事務就無法再ROLLBACK了，同理，已ROLLBACK的事務也無法再COMMIT了。
?在事務中，把事務中所有的語句看成是一個數據處理單元，因此單元必須全部做完或全部不做。
?事務的4大特性：
1，? 原子性：整個事務被看成一個執行單元，要么全部成功，要么全部取消。
2，? 一致性：當事務完成后，數據庫的內容必須全部更新妥當，而且仍然具備正確性及完整性。
3，? 隔離性：在事務中所使用到的數據，必須與其他同時在進行的事務適度隔離（使用鎖定數據的方法來隔離事務）
4，? 永久性：事務一旦提交后，其所作的數據修改將被視為永久性的，無法再用ROLLBACK回滾了。
事務是以連接為單位，每個連接都可以有自己的事務。
事務的執行有3種模式：
1，? 外顯事務2，? 自動提交事務3，? 隱含事務
嵌套事務是以最外層的事務為提交或回滾對象的。
雖然嵌套事務是以最外層為提交對象的，但其中的每個事務都有自己的BEGIN TRAN 和COMMIT
其實事務是為存儲過程而設計的，因為這樣我們就可以在存儲過程中撰寫事務程序，而不用擔心該程序被調用時是否已在另一個事務之中。
?@@TRANCOUNT事務計數
無論是那里調用ROLLBACK，都會跳轉到最外層的BEGIN TRAN處，@@TRANCOUNT都會變為0。‘
有時在事務中發生錯誤時，我們希望只要回滾一小部分就可以了
則用SAVE TRANSACTION來設置“事務保存點”，然后在必要時使用ROLLBACK來回滾到所保存的位置，而不會中斷事務。
ROLLBACK 后的事務名稱只能是由SAVATRAN或最外層的BEGIN TRAN所聲明的事務名稱。
游標的狀態：
1
?Cursor已打開，其內有0，1或多條記錄
?0
?Cursor已打開，但確定其內沒有查詢到任何一條信息記錄
?-1
?Cursor已關閉
?-2
?Cursor變量名未參照到時間Cursor或參照的Cursor已被DeAllocale
?-3
?指定的游標變量名不存在
?
Cursor主要是使用于SQL批、存儲過程和出發器中。
游標的格式：
DECLARE? 游標名? CURSOR? -------聲明游標
FOR? SELECT 姓名? FROM 通訊薄? WHERE 地址=臺北??? -------------游標的數據來源
OPEN 游標名?? -----------打開游標
DECLARE? @name? varchar(20)
FETCH? NEXT? FROM? 游標名???? ------------將第一條數據存入@name
INTO? @name
WHILE(@@FETCH_STATUS=0)????? -------------判斷是否讀到數據，0表示讀到數據
BEGIN
???FETCH? NEXT? FROM 游標名
?? INTO? @name
END
CLOSE? 游標名?? ----------------關閉游標與數據的關聯
DEALLOCATE? 游標名? ---------------將游標對象刪除
除了Cursor中FETCH數據外，可以通過Cursor來UPDATE或DELETE一條目前所指定的記錄
UPDATE 標標公司?? SET 價格=100? WHERE CURRENT? OF? 游標名
DELETE 標標公司?? WHERE CURRENT? OF? 游標名
范例：
DECLARE? MyCursor? CURSORLOCAL?? SCROLL_LOCKS
FOR? SELECT 價格FROM? 標標公司
FOR? UPDATE
OPEN? MyCursor
DECLARE? @money?? money
FETCH? MyCursor? INTO @money
WHILE(@@FETCH_STATUS=0)
BEGIN
????IF? @money<=10
??????BEGIN
?????????SET @money=money*1.1
???????????????UPDATE? 標標公司
???????????????SET 價格=@money?????????????? -----------------更新價格信息
???????????????WHERE?? CURRENT OF MyCursor
???????END
FETCH? MyCursor? INTO @money
END
游標變量，使用SET將已經聲明好的游標給游標變量。在使用上，游標變量和游標是一樣使用的
全局游標和本地游標有相同的變量時，以本地游標為優先調用
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?