資料庫基礎知識匯總__資料庫

最近在複習期末考，就順便把這些筆記儲存下來，也方便以後查看，涉及的都是一些基礎知識，大牛勿噴啊，有錯誤之處歡迎指教~~~~

首先，當然得記住一些常用的詞彙啊。。。下面就把會出現的詞彙都大概的總結出來，方便查看，省得滿文章的去找(英語好的就自行跳過吧。)。當然，不是純純的單詞翻譯，後面跟的文字可能是翻譯也可能是他用到的地方。

-DBCC ShrinkDataBase ：收縮資料庫
- Constraint ：約束 (資料完整性約束條件定義時用到)
- Primary Key ：主鍵約束
- Unique ：唯一性限制式
- Clustered | Nonclustered ：叢集索引或非叢集索引
- Check： 檢查約束
- Default ：預設約束
- Foreign Key：外鍵約束
- On update Cascade On deleteCascade ：外鍵約束中的串聯更新和刪除
- SQL： Structured Query Language 結構化查詢語言 (SQL)
- Having ：篩選組
- Exists：存在
- Union：聯合
- Truncate Table ：清空表（不記錄日誌操作，無法恢複）
- Begin Transaction ：事務開始
- Commit Transaction ：事務結束，執行過程成功
- Rollback Transaction: 交易回復，執行過程出錯
- Set Implicit_Transactions On：隱含交易開始
- Set Implicit_Transactions Off：隱含交易關閉
- @@Trancount：檢測串連交易處理嵌套的層數
- Sysobjects：主要記錄新表的基本資料
- Syscolumns ：主要記錄新表的列資訊
- Sysindexes：主要記錄指向新表鎖使用的儲存空間和主鍵等資訊。
- excute：執行
- @@fetch_status：取得最後一次遊標資料提取操作結果狀態，0表示成功，-1表示失敗，-2表示要取得行不在記錄集內，已從集合中刪除。
- PROCEDURE：預存程序

資料庫基礎和設計 資料庫系統包括資料庫、資料庫管理系統、資料庫的使用者和支撐資料庫管理系統啟動並執行軟硬體。 資料庫五個常用的對象是：表、儲存、視圖、觸發器、索引。 資料庫物件的三種關係：一對一、一對多、多對多。 資訊是有用的資料，資料是資訊的表現形式。資訊的特點是：無限性、共用性、創造性。 模式是資料庫中全體資料的邏輯結構和特徵的描述。資料庫的三級模式：內模式(物理層)、概念模式(邏輯層)、外模式(使用者層)。 資料庫系統的使用者結構：單使用者結構、主從式結構、客戶-伺服器模式結構和分布式結構。 第一範式：在一個關係中消除重複的欄位，且每個欄位都是最小的邏輯儲存單位。 第二範式：所有非主鍵欄位完全依賴於主鍵，不存在非主鍵欄位部分依賴主鍵。 第三範式：去除傳遞依賴(不要包含可以通過計算得到或推導得到的欄位)。

資料庫結構和管理 主要資料檔案(.mdf)：資料庫的起點，可以指向資料庫中檔案的其他部分。 次資料庫檔案(.ndf) 交易記錄檔(.ldf)：包含恢複資料庫所需的所有日誌資訊。 收縮資料庫 DBCC SHRINKDATABASE(資料庫名稱,收縮後的大小)

表的儲存原理及完整性建立管理 資料類型： 數值型：BIGINT , INT , SMALLINT , TINYINT , DECIMAL和NUMERIC , FLOAT 和 REAL 貨幣型：MONEY , SMALLMONEY 字元型：CHAR , VARCHAR , TEXT 日期時間類型：DATETIME , SMALLDATETIME 完整性條件約束條件 空值約束 : null | not null 主鍵約束：Constraint PK_課程表_課號 PRIMARY KEY (課號) 唯一性限制式：Constraint 約束名 unique [ clustered | nonclustered] (列名) 檢查約束：Constraint CK_課程表_課號 check（課號 like ‘s[0-9][0-9][0-9][0-9]’） 預設約束：性別 char(2) Default ‘男’ 外部鍵約束：Constraint FK_開課表_課號 Foreign Key (課號) References 課程表(課號) on update cascade on delete cascade(包含了串聯刪除和修改) 修改表結構：ALETR 跟屬性列有關的約束和索引刪除後，指定的屬性才能刪除。 使用DROP Table 語句不能刪除系統資料表和被Foreign Key 約束所參照的使用者表，必須先刪除引用的外鍵約束或引用的表。
~~~感覺分不清哪張表可以刪除，哪張不可以了。。。。下面就用例子說明吧，如下所示：建立了兩張表，mainTB和foreignTB,foreignTB中的學校欄位受外鍵約束，此時我們不能刪除mainTB，而foreignTB是可以直接刪除的，當然，如果刪除了foreignTB則mainTB也就可以刪除了。

查詢處理和表資料編輯 起別名：當別名有空格時要加上”，eg: ‘new name’。 Distinct關鍵字作用的範圍是整個查詢列表,而不是單個的列，因此distinct要放在select後面。 涉及空值的查詢，要用exp is [not] null，而不能用”=“ 或者 ”。=“ 或者 ” <>“代替。 如果在select中用了計算資料行，並且要求按這個計算資料行進行排序，則在order by子句中有三種方式表示:
  1.計算資料行的順序編號 2.計算資料行的運算式 3.計算資料行的別名

select 學號,成績,成績+10 as 新成績from 學生表order by 3        --第二種方式：計算資料行的順序編號order by 成績 +10 --第二種方式：計算資料行的運算式order by 新成績   --第三種方式：計算資料行的別名

分組查詢
  Group by 子句可以將查詢結果集按一列或多列取值相等的原則進行分組。

--查詢個門課程的課程名級相應的選課人數select 開課號,count(學號) From 選課表 group by 開課號

group by 子句中的列名只能是From子句所列表的列名，而不能是別名。
select目標列運算式所涉及的列必須滿足，要麼在group by 子句中，要麼在某個統計函數中。

--查詢學號前5位為"s0601"且選修了兩門以上課程的學生的學號select 學號 from 選課表 where 學號 like 's0606%' group by 學號 having count(*) >=2

where 和 having的區別：
1.作用對象不同(where -> 表；having -> 組)；
2.選擇條件的構成有差異,where 條件不能直接包含統計函數，而having條件所涉及的列必須要麼在group by子句中，要麼在某個統計函數中。 串連查詢
  普通串連
  join on 串連 （Inner join）
  外串連(左串連left outer join，右串連 right outer join，全串連 full outer join) 子查詢
 對子查詢結果集的檢查包括：
  檢查給定值是否在結果集中(用in串連子查詢和父查詢)；
  檢查給定值和結果值中元素的大小比較(單值比較；多值比較)；
  檢查結果集是否為空白。

--查詢選修了開課計劃編號為010101的課程的學生姓名select 姓名from 學生表 as swhere exists (                select * from 選課表 as s                where e.學號=s.學號 and 開課號='010101'             )--*****   這類查詢的特點：--*****   1.子查詢的條件往往用到父查詢所涉及的表；--*****   2.子查詢的select 子句一般寫成select * 即可，無需給出具體列名；

//擷取前8條記錄  desc降序  asc升序select top 8 * from newsTab where type='通知通告' order by zhiding desc,id desc;

插入子查詢結果
  1.使用insert select 子句 要自己建立表
  2.使用select into 子句 系統自己建立表(當目標列是計算資料行時必須取別名)

刪除資料
  可以使用delete from 表名 或者 Truncate Table 語句來清空目標表（比delete快 不記錄日誌操作，他的刪除無法恢複）

索引 索引是對資料庫中一個或多個列的值進行排序的結構。

作用：
  通過建立唯一索引，可以保證資料記錄的唯一性。
  可以大大加快資料檢索的速度。
  可以加速表與表之間的串連。
  使用order by子句和group by子句進行檢索資料時，可以顯著減少查詢中分組和排序的時間。
  索引可以在檢索的過程中使用查詢最佳化工具，提高系統效能。

索引分類
  叢集索引：邏輯有序，物理也有序，一個表只能有1個；
  非叢集索引：邏輯有序，物理無序，一個表只能有249個；

索引的建立和刪除

--索引的建立create [unique] [clustered|nonclustered]index 索引名on 資料表|視圖(欄位 asc|desc)--索引的刪除drop index 表名.索引名

事務 事務的特性：原子性、一致性、隔離性、永久性。 事務時恢複和並發的基本單位。 事務並發的資料問題：丟失修改或被覆蓋、讀髒資料、不能重複讀、幻影讀。 事務分類：顯示事務、隱含交易、自動事務模式(SQL SERVER預設的)。 顯示事務：

begin transaction                      --語句開始commit transaction / commit work       --事務結束 執行成功rollback transaction / rollback work   --事務結束 執行失敗

隱含交易
所有的create語句，alert table ，所有的drop語句，Truncate table，grant，revork，insert，delete，update，select，open，fetch都會重新啟動一個事務。

set Implicit_Transactions on --隱含交易開啟set Implicit_Transactions off --隱含交易關閉

@@Trancount檢測串連交易處理嵌套的層數。 基本鎖
  共用鎖定（S鎖）：用於唯讀操作，它允許多個事務對資源鎖定進行讀取，但禁止其他事務對鎖定資源進行修改。
  排它鎖（X鎖） ：它鎖定資源不能再被其他事務鎖定，所以其他事務不能讀取和修改。

Transact-SQL程式結構

批是一組sql語句的集合，一個批以GO結束，使用批的基本規則：
  1.所有create語句應該單獨做成一個批，不能再批中和其他sql語句一起使用。
  2.使用alter table修改表結構以後， 不能再同一個批中使用新定義的列。
  3.excute語句為批中的第一個語句時，可以省略excute關鍵字。
   4.批命令GO與其他sql語句不能再用一行上。

轉換函式
  cast (<運算式> as <目標資料類型> [(<長度>)])
  convert(<目標資料類型> [(<長度>)]，<運算式>[,style])

Case語句用法

CASE <輸入運算式>    When <當運算式1> Then <結果運算式1>    When <當運算式2> Then <結果運算式2>    ................    When <當運算式n> Then <結果運算式n>    {Else <結果運算式n+1>}ENDCASE    WHEN <條件運算式1> Then <結果運算式1>    WHEN <條件運算式2> Then <結果運算式2>    ................    WHEN <條件運算式n> Then <結果運算式n>    {Else <結果運算式n+1>}END

視圖規劃和操作
視圖對應三級模式中的外模式，視圖中儲存著視圖的定義及其關聯的基本表的資訊，而不存放視圖對應的資料，這些資料仍然存放在匯出視圖的基本表中，因此視圖有稱為虛擬表。
資料庫中使用視圖的主要優點：
- 視圖能簡化使用者的操作；
- 視圖是使用者能以多角度看待同一資料；
- 視圖為資料庫重構提供了一定程度的邏輯獨立性；
- 視圖能夠對機密資料提供安全保護。

--建立視圖的文法格式 create view view_name as select_statement    --定義視圖的select子句 [with check option] --強制視圖上執行的所有資料修改語句都必須符合由select_statement設定的準則 with encryption --表示sql server加密包含create view 語句的文本系統資料表列，可防止將視圖作為sql server複製的一部分發布。 --建立視圖時，視圖的名字存在sysobjects表中，有關視圖所定義的列資訊添加到syscolumns表中，而有關視圖相關性的資訊儲存在 --sysdepends表中，另外，create view 語句的文本添加到syscomments表中。 eg: USE 教學管理 IF exists (select table_name from information_schema.views where table_name='V_視圖名')    drop view V_視圖名 GO CREATE view V_視圖名 WITH ENCPYPTION AS SELECT 課號,課名,教材 FROM 課程表 WHERE 所在院系='資訊學院' WITH CHECK OPTION GO

--修改視圖alert view view_nameasselect_statement[with check option]--使用alert view 更改當前正在使用的視圖，sql server將為他提供一個排他架構鎖。

--視圖重新命名exec sp_rename 'V_oldname','V_newname'

--刪除視圖drop view view_name

當視圖引用多個表時，無法用delete刪除資料，若使用update，則應當與insert一樣，被更新的列必須屬於同一個表。
select_statement在挑選清單中沒有彙總函式，也不包括top,group by,union或distinct子句。
select_statement列表中沒有衍生的資料行。
一個update或insert語句只能修改引用的一個基表中的資料。
當視圖在from子句中只引用一個表時，delete語句才能引用可更新的視圖。

遊標
遊標提供了一種對從表中檢索出資料進行操作的靈活手段，就本質而言，有標識實際上是一種包括多條資料記錄的記過集中每次提取一條記錄的機制。
sql server支援三種類型的遊標：Transact-SQL 遊標、API伺服器資料指標、客戶遊標。

--遊標的申明DECLARE <遊標名>  cursor   [local|global][forward_only|scroll]                --forward_only順向資料指標，[static|keyset|dynamic|fast_forwar]  --static靜態資料指標，不能隨時反應使用者的更改結果 dynamic動態資料指標，能隨時反應使用者的更改結果[read_only|scroll_locks|optimistic]FOR <select 查詢>eg:declare 學生表_cur1 cursorfor select 學號,姓名    from 學生表 where 專業='電腦'

--開啟遊標open 遊標名

--讀取遊標資料fetch [next | prior | first | last |absolute{n|@nvar} | relative {n|@nvar} from ]  --next 返回結果集的第一行                                                                                   --prior | first | last |absolute{n|@nvar} | relative {n|@nvar}只有定義了scroll選項才可以使用遊標名into @變數1,@變數2

--關閉遊標close 遊標名--close語句關閉遊標，但不釋放遊標佔用的資料結構，應用程式可以再次執行open開啟和填充遊標

eg:declare 學生表_cur1 cursorfor select 學號,姓名,所在院系    from 學生表     group by 學號declare @snum char(5),        @sname char(10),        @sdepa char(10)--開啟遊標open 學生表_cur1--取遊標第一行資料fetch next from 學生表_cur1 into @snum,@sname,@sdepa--逐行顯示教師資訊，並取下一行資料while @@fetch_status = 0BEGIN        select @snum,@sname,@sdepa        fetch next from 學生表_cur1        into @snum,@sname,@sdepaEND--關閉遊標，此時還可以重新開啟close 學生表_cur1--釋放遊標deallocate 學生表_cur1go

遊標定位修改和刪除操作

--遊標定位修改和刪除操作--文法格式：--遊標定位修改update語句：update 表名    set 子句    where current of 遊標名--遊標定位刪除delete語句：delete from 表名    set 子句    where current of 遊標名--利用where current of進行的修改或刪除隻影響當前行--eg：首先查看學生表中的每一行，將學號="s060109"的記錄的行動電話改為13888320247,並將城市改為天津declare @學號 char(6) ,@姓名 char(10),@電話 char(11),@籍貫 char(10)declare stu_up_cur cursorforselect 學號,姓名,電話,籍貫from 學生表for update of 電話,籍貫open stu_up_cur cursorfetch next from stu_up_cur cursor into  @學號,@姓名,@電話,@籍貫while @@fetch_status=0begin    select @學號,@姓名,@電話,@籍貫    if @學號='s060109'    update 學生表 set 電話='13888320247',籍貫='天津'    where current of stu_up_cur cursor    --利用where current of進行的修改或刪除隻影響當前行    fetch next from stu_up_cur cursor into  @學號,@姓名,@電話,@籍貫end close stu_up_cur cursordeallocate stu_up_cur cursor

使用者自訂函數設計
使用者自訂函數分為三種類型：標量類型函數(返回在returns子句中定義的類型的單個資料)，內嵌表值型函數(以表的類型返回一個傳回值)，多語句表值型函數(返回一個表)
不能在函數中進行的操作有：對資料庫表的修改，對不在函數上的局部遊標進行操作，寄送電子郵件，嘗試修改目錄，以及產生返回至使用者的結果集。

--建立標量值使用者自訂函數--文法格式create function function_namereturn scalar_return_data_typebegin    function body    return scalar_expressionend--eg：建立成績轉換純量值函式，實現百分制與優 良 中 及格 不及格的轉化use 教學管理gocreate function F_分數等級(@成績 float)return char(16)asbegin    declare @等級 char(16)    select @等級= case           when @成績 is null then '還沒有參加考試'           when @成績<60 then '不及格'           ................           end           return  @等級 END--調用方式select 學號,DBO.F_分數等級(成績) as '成績等級' from 學生表 where 學號='s060606'

--建立內聯型使用者自訂函數--文法格式create function function_namereturn tablebegin    return select_stmtend--eg：建立內聯型函數，返回指定學院的學生資訊use 教學管理gocreate function F_學生資訊(@院系 char(20))return tableas           return(select 學號,性別 from 學生表 where 所在院系=@院系)--調用方式select * from DBO.F_學生資訊('資訊學院')

--建立多語句表值型函數--文法格式create function function_namereturn @return_variable tableasbegin    function body    returnend--eg:建立多語句表值型函數，返回執行教師某年的開課資訊use 教學管理gocreate function F_教師課表(@教師姓名 char(20),@開課學年 char(9))return @教師課表 table(        課名 varchar(30),        開課地點 char(6),        已選人數 int)asbegin           insert @教師課表           select 課名,開課地點,已選人數           from 教師表 T,開課表 O           where T.工號=O.工號           AND 開課學年=@開課學年           returnend調用方式select * from DBO.F_教師課表('張三','2015-2016')

使用alert Function 命令相當於重建一個同名的函數
不能用alert Function更改函數的類型，也就是：純量值函式、表值型函數、多語句函數不能相互轉化

刪除函數：drop function function_name

預存程序和使用者預存程序的設計
預存程序是一組完成特定功能的sql語句集，經編譯後儲存在資料庫中。
在sql server中預存程序分為兩類：系統提供的預存程序和使用者自訂的預存程序。
系統過程主要儲存在master資料庫中並以sp_為首碼，系統預存程序主要是從系統資料表中擷取資訊，從而為系統管理員管理sql server提供支援。
使用者自訂預存程序是由使用者建立並能完成某一個特定功能的預存程序。
預存程序的優點： 預存程序允許標準組建式編程。 預存程序能實現較快的執行速度。 預存程序能減少網路流量。 預存程序可作為一種安全機制來充分利用。 自動完成需要預先執行的任務。
預存程序雖然既有參數又有傳回值，但他與函數不同，預存程序的傳回值只是指明執行是否成功，並且他不能像函數那樣直接被調用，在調用預存程序名字前一定要有exec保留字。

預存程序由三部分組成：
- 所有的輸入參數及傳給調用者的輸出參數。
- 執行的針對資料庫的動作陳述式，包括調用其它預存程序的語句。
- 返回給調用者的狀態值，以指明調用是成功還是失敗。

--建立一個帶參數的預存程序，實現對指定的某一專業某門課程學生選課及成績的查詢。use 教學管理goif exists (select * from sysobjects where name='p_學生選課資訊' and type='p')    BEGIN        DROP PROCEDURE P_學生選課資訊    ENDGOCREATE PROCEDURE P_學生選課資訊(@專業 char(20),@課名 char(20))asif  @專業 is null    begin        print '必須指定專業'    endelse    begin        select s.學號,姓名,專業,所在院系,o.課號,o.課名,o.成績        from 學生表 as s,選課表 as E,開課表 as o,課程表 c        where 專業=@專業 and 課名=@課名 and s.學號=e.學號 and e.開課號=o.開課號 and o.課號=c.課號    end--執行預存程序exec  P_學生選課資訊 '電腦','資料結構'

修改預存程序 alter
刪除預存程序 drop procedure 預存程序名

觸發器原理及使用
觸發器可以看成一類特殊的預存程序，他在滿足某個特定條件時自動觸發執行，觸發器是為表上的更新、插入、刪除操作定義的，也就是說，當表上發生更新、插入和刪除操作時觸發器將執行。
觸發器的主要作用就是能夠實現主鍵和外鍵所不能保證的複雜的參照完整性和資料一致性。觸發器還有很多功能： 強化約束。 級聯運行。

預存程序的調用。

觸發器的種類：

AFTER觸發器：該類型觸發器要求只有執行完某一操作[INSERT | UPDATE | DELETE ],並處理過所有約束後，觸發器才能被觸發，且只能在表上定義。

INSTEAD OF 觸發器：該類觸發器表示並不執行所定義的操作[INSERT | UPDATE | DELETE ]，而僅執行觸發器本身。既可以在表上也可以在視圖上。

觸發器原理：
每個觸發器有兩個特殊的表：插入表和刪除表，分別為inserted 和 delete，有下列幾個特點：

這兩個表是邏輯表，並且這兩個表都是由系統管理的，儲存在記憶體，不是儲存在資料庫中，因此不允許使用者直接對其修改。 這兩個表的結構總是與被該觸發器作用的表有相同的表結構。 這兩個表是動態駐留在記憶體中，當觸發器工作完成時，這兩個表也被刪除。

這兩個表是唯讀，且只在觸發器內部可讀，即使用者不能向這兩個表寫入內容，但可以在觸發器中參考資料表的資料。

插入表的功能：
一旦對該表執行了插入操作，那麼對該表插入的所有行來說，都有一個相應的副本存放到插入表（inserted ）中，即插入表格儲存體原表插入的新資料行。
刪除表的功能：
一旦對該表執行了刪除操作，則將所有被刪除的行存放到刪除表（delete）中。

--insert觸發器--在大學資料庫中，當新的學生選課註冊資訊添加到選課表中時，要對開課表中的人數進行更新，且當人數超過最多能容納的人數時，要提示選課人數已滿USE 教學管理GOCREATE trigger T_選課表插入觸發ON 選課表FOR insertASBegin    DECLARE @已選人數 int,@限選人數 int    select @已選人數=已選人數+1,@限選人數=限選人數    from 開課表 o,inserted i    where o.開課號=i.開課號    if(@已選人數 > @限選人數)    begin        print '選修人數已滿'        rollback transaction    end    update 開課表    set 已選人數=@已選人數    from 開課表 o,inserted i    where o.開課號=i.開課號End

--update觸發器--教師表裡的工號和負責人必須有外部索引鍵關聯，當負責人工號修改了，負責人內容也要更著修改，使用觸發器實現當某個負責人工號修改了，級聯修改負責人use 教學管理gocreate trigger T_負責人工號變化on 教師表for updateAS Begin    declare @old_工號 char(