SQL Server 2008技術內幕：T-SQL語言基礎 筆記

1.1 SQL Server 2008例項

    SQL Server例項是指安裝的一個SQL Server資料庫引擎/服務。在同一臺計算機上可以安裝SQL Server的多個例項，從安全性，例項管理的資料以及其他方面來說，每個例項之間是彼此獨立的。

    可以將計算機上安裝的例項之一設定為預設例項，而其他例項則必須為命名例項。

    在安裝期間中可以決定是將一個例項安裝為預設例項，還是命名例項，但安裝好後就不能對此進行修改了！

    客戶端應用程式連線預設例項：   計算機名(IP地址)

    客戶端應用程式連線命名例項：   計算機名(IP地址) \ 例項名稱(安裝期間提供)

1.2資料庫

    可以認為資料庫是各種物件的容器，這些物件可以是：表，檢視，儲存過程等等。每個SQL Server例項可以包含多個資料庫。

    安裝SQL Server時安裝程式會建立幾個系統資料庫，用於儲存系統資料和服務於內部目的。

1.2.1系統資料庫

    master : 儲存例項範圍內的後設資料資訊，伺服器配置，例項中所有資料庫的資訊，以及初始化資訊。

    resource ： 是SQL Server 2005新增的，用於儲存所有系統物件。當查詢資料庫中的後設資料資訊時，這種資訊表面上是位於資料庫中的，但實際上是儲存在resource資料庫中的。

    model : 是新資料庫的模板，每個新建立的資料庫最初都是model的一個副本（Copy）。

    tempdb : 是儲存臨時資料的地方。每次重新啟動SQL Server例項時，會刪除這個資料庫 的內容，並將其建立為model 的一個副本。 

    msdb : 是稱為SQL Server Agent的一種服務儲存其資料的地方。

1.2.2登陸驗證

    從安全性方面，為了能連線到SQL Server例項，必須讓DBA位使用者建立一個登陸賬號。登陸賬號可以關聯到Windows憑據（credentials），在這種情況下，它會呼叫Windows憑據進行身份驗證。

    當使用SQL Server驗證登陸來連線SQL Server例項時，就必須提供登陸的使用者名稱和密碼。

1.2.3物理佈局

    資料庫在物理上是由資料檔案和事務日誌檔案組成。

    每個資料庫必須至少有一個資料檔案和一個日誌檔案（SQL Server的預設情況），資料檔案用於儲存資料庫物件資料，事務日誌檔案則儲存SQL Server為了維護事務所需的資訊

    .mdf 代表Master Data File（主要資料檔案）。

    .ldf 代表Log Data File（日誌資料檔案）

    .ndf 代表Not Master Data File（輔助資料檔案）

 

1.2.4 架構和物件

一個資料庫包含多個架構，而每個架構則又包含多個物件（表，檢視，儲存過程等）。

架構一個名稱空間，用作物件名稱的字首。例如，在架構Sales中有一個Order表，架構限定（schema-qualified）的物件名稱是Sales.Order。如果在引用物件時忽略了架構名稱，SQL Server將按照一定的策略分析出架構的名稱是什麼。

如果不顯式的指定架構，那麼在解析物件名稱時就會付出一定的沒有意義的額外代價。所以在程式碼中引用物件時，總是推薦使用這種架構限定的物件名稱（兩部分物件名稱）。

 

1.3 建立表和定義資料完整性

示例1：建立資料庫

IF DB_ID ( 'testdb' ) IS NULL

CREATE DATABASE testdb ;

（1）檢查資料庫是否存在?

DB_ID函式接受一個資料庫名稱作為輸入引數，返回它的內部資料庫ID。如果指定名稱的資料庫還不存在，則返回NULL

（2）架構？

這個例子使用的架構是dbo（預設架構），在每個資料庫中都會自動建立這個架構。

 

1.3.1建立表

示例2：建立表

USE testdb ; 

IF  OBJECT_ID( 'dbo.Employees' ,'U' )  IS NULL

    DROP  TABLE  dbo.Employees ;

CREATE  TABLE  dbo.Employees

(

empid   INT   NOT NULL,

firstname  VARCHAR(30)   NOT NULL,

lastname  VARCHAR(30)   NOT NULL,

hiredate   DATE   NOT NULL,

mgrid   INT   NOT NULL,

ssn      VARCHAR(2)   NOT NULL,

salary   MONEY    NOT NULL

) ;

【示例分析】

（1）USE 語句將當前的資料庫上下文切換為dbo.testdb。以確保在正確的資料庫中建立物件。

（2）OBJECT_ID函式接受一個物件名稱和型別作為輸入，這裡，型別 ' U '代表使用者表。如果匹配給定輸入名稱和型別的物件存在，這個函式則返回內部的物件ID，否則返回NULL。

（3）CREATE  TABLE 先指定要建立的表的名稱（架構限定名），然後再在圓括號中定義它的各個屬性（列）。

【ANSI標準】

如果不顯式指定一個列是否允許NULL值，則假設應該是NULL值（允許NULL值）。

【最佳實踐】

將列定義為為NOT NULL

採用分號結束所有語句

 

1.3.2定義完整性約束

（1）Primary Key Constraints

     主鍵約束實施行的唯一約束，同時不允許受約束的屬性（列）取NULL值。

 每個表只能定義一個主鍵.

 ALTER TABLE dbo.Employees

ADD  CONSTRAINT PK_Employees

PRIMARY KEY(empid);

     定義好主鍵約束後，就可以保證所有的empid的值是唯一而確定的。

（2）Unique Constraints

 唯一約束用來保證資料行的一個列（一組列）資料的唯一。

 在同一個表中可以定義多個唯一約束。

ALTER TABLE dbo.Employees

ADD  CONSTRAINT UNQ_Employees_ssn

UNIQUE(ssn);

（3）Foreign Key Constraints

     外來鍵約束用於實施引用完整性。這種約束在引用表(referencing table)的一組屬性上進行定義，並指向被引用表(referenced table)中的一組候選鍵（主鍵或唯一約束）。注意，引用表和被引用表可能是同一個表！

     外來鍵的目的是將外來鍵列允許的值域限制為被引用列中現有的那些值。

示例3：建立表Order，其主鍵定義在orderid上

IF OBJECT_ID('dbo.Orders','U') is NOT NULL

DROP TABLE dbo.Orders;

 

CREATE TABLE dbo.Orders

(

orderid INT NOT NULL,

empid INT NOT NULL,

custid VARCHAR(20) NOT NULL,

orderts DATETIME NOT NULL,

qty INT NOT NULL,

CONSTRAINT PK_Orders

PRIMARY KEY(orderid)

);

如果現在要想實施一個完整性規則，將Orders表的empid列支援的值域限制為現有的Employees表中empid列的取值。為此，要在Orders表的empid上定義一個外來鍵約束，讓他指向Employees表的empid列。

ALTER TABLE dbo.Orders

ADD CONSTRAINT FK_Orders_Employees

FOREIGN KEY(empid) REFERENCES dbo.Employees(empid);

 

類似的，想要限制Employees表中mgrid列支援的值域為同一個表中已經存在的那些empid的值。

ALTER TABLE dbo.Employees

ADD CONSTRAINT FK_Employees_Employees

FOREIGN KEY(mgrid) 

REFERENCES dbo.Employees(empid);

 

注意，既是被引用的候選鍵列不存在NULL值，在外來鍵列中也允許NULL列。

【禁止操作】

當試圖刪除被引用表中的行，或更新被引用的候選鍵時，如果在引用表中存在相關的行，則操作不能執行！

【級聯操作】

    可以在外來鍵定義中將ON DELETE和ON UPDATE選項定義為：

CASCADE : 操作（更新和刪除）將被級聯到引用表中相關的行。

SET DEFAULT : 補償操作將會把相關行的外來鍵屬性設定為列的預設值。

SET NULL : 補償操作將會把相關行的外來鍵屬性設定為NULL值。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-626012/，如需轉載，請註明出處，否則將追究法律責任。