資料庫基本觀念

日期：

一、基本觀念

1、表(資料表)

(1)、索引(索引鍵)

  •     聚集索引(主索引)：只有一個
  •     非聚集索引(次索引)：可能多個

(2)、觸發器(觸發程序)：存在表架構下，做為資料表操作時(新、更新、刪除)自動觸發動作。

(3)、約束(條件約序)：只限於表中存在，當滿足條件時自動執行約束限制，例如性別欄位，只能輸入男或女。

2、數據庫關係圖(資料庫圖表)：表與表之間關係圖

3、視圖(檢視) ：視圖不存儲數據，而是預先寫好的查詢語句，目的控制用戶顯示數據及安全。

4、可編程性(可程式性)→存儲過程(預存程序)用途：

  1. 可減少存儲過程執行代碼之網路存取訊息流量
  2. 因有經過優化，減少存取每次運行時間
  3. 考量安全性，如穩藏數據庫訊息
  4. 存儲過程之間可以彼此調用

5、用戶與角色(登入與伺服器角色)

6、SQL Server 中的對象名

命令：　[ServerName.[DataBaseName.[SchemaName.]]] ObjectName
  • SchemaName：預設為dbo，參考資料庫名稱>安全性>結構描述
  • ObjectName ：可以是 Table、View、Function...等名稱

二、資料表連接

1、內部連接：

  • 顯示兩個表有匹配(相交)記錄的資料
  • select * from A inner join B on A.id = B.id

2、外部連接：分成

  • 左外連接 left join，顯示兩個表有(相交)記錄並顯示左邊的表沒匹配的記錄
  • 右外連接 right join，顯示兩個表有(相交)記錄並顯示右邊的表沒匹配的記錄
  • 全連接 full join，顯示兩個表有(相交)記錄並顯示左右邊的表沒匹配的記錄
PS：

  • 所謂左右邊，指語法設指定時前後關係，如 Form A left join B ，其中A就是左邊，B就是右邊。
  • 在使用連結，盡量使用內部連接，避免使用外部連結，可以避免代碼間的錯誤。
  • 當連結多個表，先執行第一段連結，連結結果再和第二段做連結，以此類推

3、交叉連結：

  • 就是兩個表相乘的和，如A表10筆記錄，B表20筆記錄，算10*20 = 200筆記錄
  • 語法：select * from A cross join B

三、聯合

  1. 將第二查詢結果附加在第一個查詢結果未尾，稱為聯合查詢
  2. JOIN 功能將垂直增加，增加更多列
  3. UNION 功能將水平增加，增加更多行
  4. 語法：
    SELECT row1,row2  FROM table1
    UNION [ALL]
    SELECT row1,row2  FROM table2
  5. 使用UNION 處理查詢時，要注意以下機個關鍵點：

  • 所有UNION查詢必須在SELECT清單中有相同的欄位數。
  • UNION 傳回的結果集的標題只能從第一個查詢中取得。
  • 查詢中對應列的資料類型必須隱式一致，當上下兩個欄位型態不同時，必須進行轉換，例CASE(ID AS varchar(10))。
  • 與其他非 UNION 查詢不同,UNION 查詢的預設回傳選項為DISTINCT,而非 ALL。

四、建立與修改資料表

CREATE TABLE

CREATE TABLE [database_n ame.[owner] Jtable_name)

(<column name>data type>

[DEFAULT <con stantexpression>]

[IDENTITY[(seed.increment) [NOT FOR REPLICATIONI]

[ROWGUIDCOL]

[COLLATE <Collation namey]

[NULL I NOT NULL ]

<column con straints>]

[column_name AS computed_column_expression]

[ table_con straint [...n])

[ON <filegroup>| DEFAULT)]

[ ColumnName dataType foreign key references TableName(ColumnName) ] //參加外部約束

[TEXTIMAGE_ON.<filegroup-DEFAULT]


ALTER TABLE


添加新列

ALTER TABLE <table name> ADD <column name> <data type>


删除列

ALTER TABLE <table name> DROP COLUMN<column name>


更改列的数据类型

ALTER TABLE <table name> ALTER COLUMN <column name>

<new data type>


添加包含约束的列

ALTER TABLE <table name> ADD <column name> <data type>

CONSTRAINT <constraint name> < constraint type> [check]

CONSTRAINT <constraint name> < constraint type> unique  //唯一約束

增加外部約束

ALTER TABLE <table name> ADD CONSTRAINT <constraint name>  Foreign Key (主要表欄位名稱)  References 被引用表名稱(欄位名稱)


在现有列中添加一个未经验证的CHECK约束

ALTER TABLE <table name> WITH NO CHECK

ADD CONSTRAINT <constraint name>

CHECK (check condition)



五、約束 

  1. 約束：是一種限制，通過表或列確保資料完整性規格的限制規則
  2. 約束的類型：
    • 域約束：針對個別的Col或多個Col做約束，在新增列或插入列發生時，執行約束限制，例如單價不能為負數或庫存量有預設值等約束。
    • 實體約束：每一ROW都有唯一值，都不會重複的資料
    • 參照完整性約束：母表與子表間某個Col之間的約束，例如產品內類別ID與類別內類別ID必須一致(做約束)
  3. 主鍵約束：Col的約束，值是唯一，並且不能null
  4. 唯一約束：Col的約束，值是唯一，但可以一次null 
    例如: 設定studentNO 約束是唯一值約束
    create table students
    (ID int identity not null primary key,
    studentNO char(6) null unique,
    name nvarchar(20))
  5. Check約束、DEFAULT約束、停用與啟用既有約束
    --檢查生日輸入值20~70之間
    --其中 DATEDIFF(單位,被減日期,當前日期)
    alter table people add constraint check_Birthday check (datediff(year, birthday, getdate()) between 20 and 70)
    --查詢性別只能輸入M與F
    alter table people add constraint check_sex check (sex in ('M','F')) 

    --建立資料表指定city欄位預設值為 '珠海'
    create table unit (unitID int not null primary key identity, 
    unitName nvarchar(50),
    city nvarchar(20) default '珠海')

    alter table people add JoiningDate date 

    --設定JoiningDate 預設值為當天
    alter table people add constraint default_JoiningDateForToday default getDate() for JoiningDate

    alter table people add Birthplace nvarchar(20)

    --設定約束，輸入Birthplace 必須含有'區'
    --charIndex(查找字元,被檢查欄位名)
    alter table people add constraint check_Birthplace_keyWord check (CharIndex('區',Birthplace)>0)


    --關閉已設定約束，關閉Birthplace 必須含有'區'約束
    alter table people nocheck constraint check_Birthplace_keyWord
    --恢復既有約束
    alter table people check constraint check_Birthplace_keyWord
  6. 約束與觸發器比較：
約束優點：
快速、可以引用其他列、在命令執行前發生、遵循ANSI標準。
約束缺點：
必須對每張表重新定義、不能引用其他表、不能綁定到資料型別。 

                 觸發器優點：

非常靈活、可以引用其他欄位或其他資料、表可以透過.NET 引用 SQL Server 以外的其他資訊。

                 觸發器缺點：

在執行指令之後發生、系統開銷很大。


六、子查詢

觀念：

子查詢是巢狀或內嵌在另一個查詢之內的 SELECT 陳述式。 巢狀查詢 (即子查詢) 稱為內部查詢。 包住巢狀查詢的查詢稱為外部查詢。


子查詢目的是將結果傳回給外部查詢。 結果的形式將決定子查詢是純量子查詢或多重值子查詢：

  • 純量子查詢傳回單一值。 外部查詢必須處理單一結果。
  • 多重值子查詢傳回的結果很像單一資料行的資料表。 外部查詢必須能夠處理多個值。

除了在純量子查詢和多重值子查詢之間選擇，子查詢可以是自足式子查詢，也可以與外部查詢相互關聯：

  • 自足式子查詢可以撰寫成獨立的查詢，而不相依於外部查詢。 當外部查詢執行時，自足式子查詢只處理一次，並將結果傳給該外部查詢。
  • 相互關聯的子查詢會參考外部查詢的一或多個資料行，因此相依於外部查詢。 相互關聯的子查詢與外部查詢不能分開執行。

若要撰寫純量子查詢，請考慮下列指導方針：

  • 若要表示查詢為子查詢，請以括弧括住。
  • Transact-SQL 支援多個層級的子查詢。 在本課程模組中，我們只考慮兩個層級的查詢 (一個內部查詢在一個外部查詢內)，但最多可支援 32 個層級。
  • 如果子查詢未傳回任何資料列 (空集合)，則子查詢的結果為 NULL。 如果您的情況有可能沒傳回任何資料列，請確保外部查詢除了處理其他預期結果，也能妥善處理 NULL。
  • 內部查詢通常只傳回單一資料行。 在子查詢中選取多個資料行幾乎註定錯誤。 唯一例外是以 EXISTS 關鍵字引進子查詢。

相互關聯的子查詢，請考慮下列指導方針：

  • 有別於自足式子查詢，使用相互關聯的子查詢時有一些特殊考慮：
    • 相互關聯的子查詢與外部查詢不能分開執行。 此限制使測試和偵錯變得複雜。
    • 不同於自足式子查詢只處理一次，相互關聯的子查詢會執行多次。 邏輯上，外部查詢會先執行，然後針對每個傳回的資料列，再處理內部查詢
  • 撰寫外部查詢接受來自內部查詢的適當傳回結果。 如果內部查詢是純量，您可以在 WHERE 子句中使用等式比較運算子，例如 =、<、> 和 <>。 如果內部查詢可能會傳回多個值，請使用 IN 述詞。 規劃處理 NULL 結果。
  • 識別外部查詢中的資料行，此資料行將由相互關聯子的查詢參考。 在外部查詢中，為資料行的來源資料表宣告別名。
  • 識別內部資料表中的資料行，此資料行將與外部資料表中的資料行比較。 建立來源資料表的別名，就像對外部查詢所做的一樣。
  • 撰寫內部查詢，根據外部查詢的輸入值，從來源取得值。 例如，在內部查詢的 WHERE 子句中使用外部資料行。

PS：

  • 您如何決定以 JOIN 或子查詢來撰寫涉及多個資料表的查詢？ 有時只是習慣問題而已。 大部分很容易轉換成 JOIN 的巢狀查詢，實際上會在內部轉換成 JOIN。 針對這類查詢，以何種方式撰寫查詢真的沒什麼差別。
  • 請謹記一項限制，在使用巢狀查詢時，傳回給用戶端的結果只能包含來自外部查詢的資料行。 因此，如果您需要兩個資料表都傳回資料行，則應該使用 JOIN 來撰寫查詢。
  • 最後，在某些情況下，內部查詢需要執行的作業，遠比範例中的簡單擷取複雜得多。 使用 JOIN 來重寫複雜的子查詢可能很困難。 許多 SQL 開發人員發現子查詢最適合複雜的處理，因為可讓您將處理分割成較小的步驟。

資料來源








留言

張貼留言