SQL Server資料庫程式設計知識總結

        SQL Server是由Microsoft開發和推廣的關聯式資料庫管理系統（DBMS）,它最初是由Microsoft、Sybase和Ashton-Tate三家公司共同開發的，並於1988年推出了第一個OS/2版本。它只是眾多關聯式資料庫管理系統的一種，其它的關聯式資料庫管理系統還有Server，Oracle，DB/2，Sybase，Informix；關聯式資料庫：Access，FoxPro，Dbase等。如今，資料庫已經變成學習軟體開發的核心課程之一，幾乎絕大部分軟體都涉及到資料庫，很多資料必須存在資料庫中。所以為了更好的掌握和操作資料庫，對資料庫知識的封裝就顯得尤為重要。

 

●  關係和非關聯式資料庫

 

       關聯式資料庫是建立在關聯式模式基礎上的資料庫，由一個或多個二維關係資料表組成，通過建立表之間的關係來定義資料庫的結構。在關聯式資料庫中最重要的是資料表，資料表把相關聯的資料按行和列排列到一起來描述一個實物。一個二維表稱為一個關係，每一個關係又可以包含多個屬性。

      和關係型資料庫相比，非關係型資料庫特別適合以SNS為代表web 2.0應用，這些應用需要極高速的並發讀寫操作，而對數值一致性要求卻不甚高。關聯式資料庫為了維護事務的一致性付出了重大代價導致了其讀寫能力下降，隨著現在網路SNS的應用對並發讀寫能力要求極高，關係型資料庫已經無法應付，因此，必須用新的一種資料結構化儲存來來代替關聯式資料庫。關聯式資料庫的另一個特點就是其具有固定的表結構，因此，其擴充性極差，而在SNS中，系統的升級，功能的增加，往往意味著資料結構巨大改動，這一點關係型資料庫也難以應付，需要新的結構化資料存放區。於是，非關聯式資料庫（NoSQL）應運而生。非關聯式資料庫通常沒有固定的表結構，嚴格上說不是一種資料庫，應該是一種資料結構化儲存方法的集合。

 

●  SQL Server資料庫基礎

 

首先我們看他們之間的聯絡。

      一、T-SQL和SQL的比較

           SQL全稱為Structured Query Language，是關聯式資料庫的標準語言，它主要包括三種語言：  資料定義語言 (Data Definition Language)、資料操縱語言、資料控制語言。T-SQL是SQL語言的增強版。

        也就是說，SQL三種語言適用於所有關係型資料庫，但是它本身具有局限性，因為它只提供了對資料庫資料基本的增刪改查等命令，在開發擁有複雜結構的資料庫程式時，只有SQL語言是遠遠不夠的，於是各大公司在SQL基礎上對它進行了增強。T-SQL是MS和Sybase在
SQL的DDL和 DML基礎上，增加了延伸的函數、系統預存程式以及程式設計結構(例如 IF和
WHILE)讓程式設計更有彈性。和T-SQL類似的增強版的SQL語言還有Oracle對SQL的擴充PL/SQL。

     二、資料庫物件

       資料庫物件定義了資料庫內容的結構。它們包含在資料庫專案中，資料庫專案還可以包含資料產生計劃和指令碼。 在學習SQL Server時，最快的方法是先學習資料庫物件。在研究了它們之間的異同點後，我把資料庫物件分為三類。

                  

      這三類資料庫物件在功能上可能有重疊。

               1、基於表的對象

                    表，資料儲存在表中。

                   基於表的對象我把它分為三類：

                視圖，用來篩選出使用者想要的資訊，它是虛擬表，只封裝語句，一定程度上保證表中資料的安全；

                   索引，對資料庫表中一個或多個列的值進行排序的結構，提供指標以指向儲存在表中指定列的資料值，然後根據指定的排序次序排列這些指標。資料庫使用索引的方式與使用書的目錄很相似；

                   約束、預設值、規則、觸發器確保了資料完整性。

             2、資料庫安全性：使用者、角色、許可權與資料庫安全性中的登陸聯合應用可以限制登陸者可進行的操作來保證資料庫的安全性。

            3、塊：預存程序和函數有點相似，但是預存程序可以返回Recordset；觸發器是一種特殊的預存程序。

     三、DTS資料轉換服務

                     DTS的主要作用：1、匯入與匯出資料

                                                       2、轉換資料（檢驗、淨化、重整資料）

                                                       3、轉換資料庫物件

     四、管理和維護

             
在這部分中主要是對資料庫檔案的保護和管理。

               1、資料庫檔案

 

                                           

         資料庫檔案是存放資料庫資料和資料庫物件的檔案。一個資料庫可以有一個或多個資料庫檔案，一個資料庫檔案只屬於一個資料庫。當有多個資料庫檔案時，有一個檔案被定義為主要資料庫檔案，副檔名為.mdf，它用來儲存資料庫的啟動資訊和部分或全部資料。一個資料庫只能有一個主要資料庫檔案，其它資料庫檔案被稱為次要資料庫檔案，副檔名為.ndf，用來儲存主檔案沒儲存的其它資料。

            多個資料庫檔案組合到一塊就成為檔案組。主檔案組是由主檔案組合成，此檔案組是由此檔案組合成，但是交易記錄檔不屬於任何一個檔案組。

       2、事務和鎖

          Ø  事務

           
事務（Transaction）是並發控制的單位，是使用者定義的一個操作序列。這些操作要麼都做，要麼都不做，是一個不可分割的工作單位。通過事務，SQL Server能將邏輯相關的一組操作綁定在一起，以便伺服器保持資料的完整性。 

                                                   

          事務通常是以BEGIN TRANSACTION開始，以COMMIT或ROLLBACK結束。 
             COMMIT表示提交，即提交事務的所有操作。具體地說就是將事務中所有對資料庫的更新寫回到磁碟上的物理資料庫中去，事務正常結束。 
             ROLLBACK表示復原，即在事務啟動並執行過程中發生了某種故障，事務不能繼續進行，系統將事務中對資料庫的所有以完成的操作全部撤消，復原到事務開始的狀態。 

         Ø  鎖

            資料庫就是通過鎖機制來解決並發問題的。。主要就是兩種鎖，共用鎖定和獨佔鎖定（也叫獨佔鎖）。

            從程式員的角度看鎖分為以下兩種類型：

                  樂觀鎖（Optimistic Lock）：樂觀鎖假定在處理資料時，不需要在應用程式的代碼中做任何事情就可以直接在記錄上加鎖、即完全依靠資料庫來管理鎖的工作。一般情況下，當執行交易處理時SQL
Server會自動對交易處理範圍內更新到的表做鎖定。

                  悲觀鎖（Pessimistic Lock）：悲觀鎖對資料庫系統的自動管理不感冒，需要程式員直接管理資料或對象上的加鎖處理，並負責擷取、共用和放棄正在使用的資料上的任何鎖。

 

●  應用執行個體

/*--下面我們建立一個名為company的資料庫，建立三個表priduct、project、tblsales，並在其中建立預設值、規則、觸發器、預存程序，並利用使用者、角色、許可權等確保資料庫安全。*/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_priduct_project]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[priduct] DROP CONSTRAINT FK_priduct_projectGOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getavgpbiaodi]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[getavgpbiaodi]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pinfo5000]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[pinfo5000]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[pro]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysconstraints]') and OBJECTPROPERTY(id, N'IsView') = 1)drop view [dbo].[sysconstraints]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[priduct]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[priduct]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[project]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[project]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblsales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[tblsales]GOif exists (select * from dbo.systypes where name = N'tele')exec sp_droptype N'tele'GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rule_position]') and OBJECTPROPERTY(id, N'IsRule') = 1)drop rule [dbo].[rule_position]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@position]') and OBJECTPROPERTY(id, N'IsDefault') = 1)drop default [dbo].[@position]GOcreate default [@position] as '其它'GOcreate rule [rule_position] as @postion in('專案經理','秘書','會計','職員','其它')GOsetuserGOEXEC sp_addtype N'tele', N'smallint', N'not null'GOsetuserGOCREATE TABLE [dbo].[priduct] ([產品ID] [int] NULL ,[產品名稱] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[project] ([項目編號] [int] NOT NULL ,[項目名稱] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,[開始日期] [datetime] NULL ,[預計日期] [int] NULL ,[客戶編碼] [int] NULL ,[負責人編號] [int] NULL ,[項目標的] [int] NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[tblsales] ([number] [int] NOT NULL ,[name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,[sex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,[birthday] [datetime] NULL ,[salary] [money] NULL ) ON [PRIMARY]GOsetuserGOEXEC sp_bindefault N'[dbo].[@position]', N'[project].[項目標的]'GOEXEC sp_bindrule N'[dbo].[rule_position]', N'[project].[項目名稱]'GOsetuserGOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO--/****** Encrypted object is not transferable, and script can not be generated. ******/GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate procedure getavgpbiaodi@name varchar(10),@avgpbiaodi int outputas declare @errorsave intset @errorsave=0select @avgpbiaodi=avg(項目標的)from project as p inner join pmanager as pm on p.負責人ID=pm.負責人IDwhere pm.姓名=@nameif(@@error<>0)set @errorsave=@@errorreturn @errorsaveGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate procedure pinfo5000as select * from project where 項目標的>=5000order by 項目標的 descGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate procedure pro@n1 int,@n2 int,@n3 int,@avreage int outputas select@avreage=(@n1+@n2+@n3)/3declare @avgscore intexec pro1 1,2,3,@avgscore outputselect 'The score is:',@avgscoreGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

SQL Server的基礎架構知識，我現在只能總結這麼多，更多的總結還是在以後一點一滴的學習中。