###### tags: `必修`

# 資料庫管理與實務

:::spoiler 課前預習

## 規劃與建立索引

### 索引簡介

[參考資料](http://vito-note.blogspot.com/2013/05/blog-post_5510.html)

索引:將資料表的部分欄位資要預先進行排序，此欄位稱為"索引欄位"。目的為預先將資料系統化整理，以便縮小搜尋範圍。

索引資料: 1. 索引欄位

2. 指標(Pointer)

* 主索引(Primary Index): 可以是一個或多個欄位的組合。

* 複合索引(Composite Index)或稱結合索引(Concatenated Index):個別欄未允許重複值，但是整個組合值然需要是唯一值。

* 唯一索引(Unique Index): 唯一索引的欄位值也是唯一的，不同於主索引只能有一個，同一個資料表可以擁有多個唯一索引。

* 一般所引(Regular Index): 一般索引的欄位值並不需要是唯一的，主要目的是加速資料表的搜尋與排序。同一個資料表可擁有多個一般索引。

主索引和外部索引

[索引](https://www.itread01.com/content/1544916258.html)
叢集索引


非叢集索引

### M路搜尋樹與B樹
[參考資料](https://ithelp.ithome.com.tw/articles/10221971)

[參考資料](https://isdaniel.github.io/dbindex-1/)

[參考資料](http://www.sql-datatools.com/2017/02/Heap-nonclustered-index-structure-in-sql-Server.html)

[參考資料](http://vito-note.blogspot.com/2013/05/blog-post_5510.html)

[參考資料](https://chowdera.com/2021/09/20210907103104375t.html)

[參考資料](https://jackyshih.pixnet.net/blog/post/5839238)

[參考資料](https://iter01.com/577500.html)

[參考資料](https://inf.news/technique/d24d963fb48e3c1e400b99b17686cc03.html)


## Transact-SQL

SQL SERVER的系統函數

|系統函數 |說明 |
| -------- | -------- |
| @@IDENTIFY | 傳回伺服器最後產IDENTIFY欄位自動編號的值，如果沒有產生傳回NULL |
| @@ROWCOUNT | 傳回最近執行T-SQL指令敘述所影響的記錄數|
| @@ERROW | 傳回最近執行T-SQL指令敘述所產生的錯誤編號，如果沒有錯誤傳回 0 |
| @@SERVERNAME | 傳回伺服器名稱 |

RETURN: 中斷查詢指令

WAITFOR: 暫停執行
`
延遲3秒執行
WAITFOR DELAY '00:00:03'
SELECT * FROM 學生
`

CHOOSE() 函數 根據參數決定回傳值
`
DECLAE @type int
SET @type = 2
DECLAE @result varchar(10)
SET @result = CHOOSE(@type, '全票', '半票', '敬老票', ''免票)
PRINT @result
`
錯誤處理結構

BEGIN TRY

END TRY
BEGIN CATCH

END CATCH

RAISERROW()函數產生錯誤訊息

THROW

BEGIN CATCH
    THROW
END CATCH

PROCEDUER 預存程序:

CREATE PROCEDURE NAME 
    @parameter1  char(5) = 'value',
    @parameter2  char(5) = 'value' OUTPUT (回傳查詢值)
AS 
BEGIN
    SELECT @parameter2 
    FROM TABEL
END

EXEC NAME

# Transaction

Transaction: 是將一系列資料庫讀取和寫入操作，視為一個無法分割的邏輯單位

* 認可交易(Commit): 交易中的所有資料庫單元操作，真正更改寫入資料庫。

* 回復交易(Rollback): 交易尚未認可，可以取消交易，回復到執行交易前的狀態。

## 交易狀態


交易狀態的種類

◎啟動狀態(Active State)：當交易開始執行，就是進入啟動狀態的初始狀態，依序執行交易的讀取或寫入。

◎部分確認交易狀態(Partially Committed State)：當交易的最後一個單元操作執行完後，也就是交易結束，就進入部分確認交易狀態。

◎確認交易狀態(Committed State)：在成功完成交易進入部分確認交易狀態後，還需要確認系統沒有錯誤，可以真正交資料寫入資料庫。

◎失敗狀態(Failed State)：當發現交易不能繼續執行下去時，交易就進入失敗狀態，準備執行回復交易。

◎放棄或中止狀態(Aborted or Terminated State)：交易需要回復到交易前的狀態，在取消所有寫入資料庫操作單元操作影響的資料後，就進入此狀態。

## 交易四大特性

1. 單元性（Atomicity）


2. 一致性（Consistency）


3. 隔離性（Isolation）


4. 永久性（Durability）


## 交易模式

* 自動認可交易模式(Autocommit Transactions Mode): 預設交易模式，每一個單獨的T-SQL指令敘述自動都視為是一個交易，如果有錯誤，就自動回復交易，否則自動認可交易。

* 明顯交易模式（Explicit Transactions Mode）就是自行使用BEGIN TRAN、COMMIT TRAN和ROLLBACK TRAIN指令組合多個T-SQL指令敘述來建立交易。

* 隱含交易模式（Implicit Transactions Mode）的起動需要指定IMPLICIT_TRANSACTIONS選項，如下所示：
SET IMPLICIT_TRANSACTIONS ON
上述指令設定SQL Server進入隱含交易模式，表示交易開始執行，直到執行COMMIT TRAN或ROLLBACK TRAN為止，就會自動進入下一個交易。


## T-SQL 交易指令

| 交易指令 | 說明 |
| -------- | -------- |
| BEGIN TRAN | 標示開始執行一個交易，它是交易的起點 |
| COMMIT | 標示交易的終點，將交易更改的資料寫入資料庫，以便執行下一個交易|
| ROLLBACK | 放棄交易且將資料庫回復到交易前的狀態|

BEGIN TRY
      BEGIN TRAN; -- 啟動交易

      INSERT INTO [dbo].[Employees] ([Name])
      VALUES ('Otis');

      INSERT INTO [dbo].[Employees] ([Name])
      VALUES (NULL); -- 這筆因為不能 NULL 而失敗，會進到 CATCH 區塊進行 ROLLBACK

      COMMIT TRAN; -- 交易成功
END TRY
BEGIN CATCH
      ROLLBACK TRAN; -- 回復到交易的開頭

      THROW; -- 拋出錯誤訊息
END CATCH

### 巢狀交易

BEGIN TRY
      BEGIN TRAN outterTran;

      -- innerTran
      DECLARE @TranCounter INT = @@TRANCOUNT;

      BEGIN TRY
            -- @TranCounter > 0 代表目前已經是在交易內，改用 SAVE TRAN
            IF (@TranCounter > 0) SAVE TRAN innerTran;
            ELSE BEGIN TRAN innerTran;

            INSERT INTO [dbo].[Employees] ([Name])
            VALUES ('Otis');

            INSERT INTO [dbo].[Employees] ([Name])
            VALUES (NULL);

            -- SAVE TRAN 不做 COMMIT
            IF (@TranCounter = 0) COMMIT TRAN innerTran;
      END TRY
      BEGIN CATCH
            -- SAVE TRAN 局部交易回復
            ROLLBACK TRAN innerTran;

            THROW;
      END CATCH
      -- end innerTran

      COMMIT TRAN outterTran;
END TRY
BEGIN CATCH
      ROLLBACK TRAN outterTran;

      THROW;
END CATCH

Note:
@@TRANCOUNT = 0 代表是最底層的 TRANSACTION

@@TRANCOUNT > 0 則是巢狀內的 TRANSACTION

### 交易儲存點 Save Points

* 類似GOTO指令，可以回復到指定的交易儲存點(局部的 Rollback)

Begin Transaction
	insert dep values ('CC')
	Save Transaction TrnA           -- 透過 Save Transaction 定義一個 Savepoints
		insert dep values ('DD')
	Rollback Transaction TrnA       -- Rollback 到上述 Savepoints
Commit Transaction	

### 並行控制

* 可提高CPU和磁碟讀寫效率

* 減少平均的回應時間

### 並行控制的三個問題

* 遺失更新（Lost Update）：是指交易已經更新的資料被另一個交易覆寫


* 未確認交易相依（Uncommitted Dependency）：是指存取已經被另一個交易更新，但尚未確認交易的中間結果資料。


* 不一致分析(Inconsistent Analysis)：並行執行多個交易，造成其中一個交易讀取到資料庫中不一致的資料。


## 交易的隔離性等級(避免併行控制的三個問題)

隔離性等級語法

SET TRANSACT ISOLATION LEVEL 隔離性等級名稱



## 資料鎖定

* Locking: 是將特定的資料暫時鎖住供使用者使用，以防止資料被其他使用者讀取或修改，確保交易的完整性 (Integrity) 和資料庫的一致性 (Consistency)。

* 鎖定層級(Lock Level): 指鎖定時，鎖定資源的範圍大小


### 鎖定模式


### 鎖定模式的使用

* 讀取資料: 共用鎖定

* 更新資料: 獨佔所定

* 更新鎖定: 使用更新操作後，第二階段更新時，會提升至獨佔鎖定，以避免死結。

* 意圖鎖定: 請求共用或獨佔鎖定前使用的鎖定，主要目的是提升獨佔鎖定的效能。例如: 交易以意圖鎖定時，其它交易請求獨佔鎖定時，因為有意圖鎖定，就不用在一一檢查是否有鎖定。

### 鎖定模式相容性


## 死結問題


系統會自動偵測死結並強迫回復(順序不一定)，可透過指定死結優先序語法，來指定優先強迫回復的交易的順序

SET DEADLOCK_PRIORITY LOW | NORMAL

流水號
select RIGHT(REPLICATE('0', 6) + CAST(ISNULL(MAX(CAST(CourseNo AS INT)),0)+1 AS varchar) , 6) AS CourseNo

Schema

SELECT b.IS_NULLABLE                         ,
	   b.COLUMN_NAME,
       *
 FROM INFORMATION_SCHEMA.TABLES  a   
 LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME   
WHERE a.TABLE_NAME like '%' + 'tCusSemCourseTeacher' + '%'
ORDER BY a.TABLE_NAME , b.ORDINAL_POSITION 


SELECT --a.Table_schema +'.'+a.Table_name       AS 表格名稱,
 a.Table_name,
 RANK() OVER (ORDER BY a.TABLE_NAME, b.ORDINAL_POSITION ASC) AS 項次
 ,b.COLUMN_NAME AS 資料行名稱
 ,(SELECT  VALUE 
   FROM    FN_LISTEXTENDEDPROPERTY(NULL,'schema',a.Table_schema,'table',a.TABLE_NAME,'column',DEFAULT)
   WHERE   name='MS_Description' AND objtype='COLUMN' 
   AND objname COLLATE Chinese_Taiwan_Stroke_CI_AS=b.COLUMN_NAME) AS 描述
 ,REPLACE(b.DATA_TYPE+'('+CONVERT(VARCHAR(20),ISNULL(b.CHARACTER_MAXIMUM_LENGTH,''))+')','(0)','') AS 資料類型
  --,isnull(b.CHARACTER_MAXIMUM_LENGTH,'')  AS 長度
  --,isnull(b.COLUMN_DEFAULT,'')            AS 預設值
 ,(CASE b.IS_NULLABLE 
     WHEN 'NO' THEN '否'
     WHEN 'YES' THEN '是'
   END) AS 可為NULL
FROM    INFORMATION_SCHEMA.TABLES a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME=b.TABLE_NAME
WHERE   TABLE_TYPE='BASE TABLE' 
AND a.Table_name IN (
					SELECT   O.NAME 
					FROM SYS.OBJECTS O INNER JOIN SYS.SCHEMAS S
					ON O.SCHEMA_ID = S.SCHEMA_ID
					INNER JOIN SYS.PARTITIONS P
					ON O.OBJECT_ID = P.OBJECT_ID
					WHERE (O.TYPE = 'U') AND
					(P.INDEX_ID IN (0,1))
					 
                     ) --輸入想要的表格名稱
AND b.COLUMN_NAME = 'peodepno'
ORDER BY a.TABLE_NAME,b.ORDINAL_POSITION

--peodepno、peodepname

--取得PK欄位
SELECT CONSTRAINT_NAME,ORDINAL_POSITION,
name,column_name,index_id,type_desc,is_unique,is_primary_key
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
LEFT JOIN sys.indexes ON OBJECT_NAME(object_id)=TABLE_NAME
WHERE OBJECTPROPERTY(OBJECT_ID, 'IsUserTable') = 1 
--WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 
AND table_name = 'ITFORM_DETIAL' --輸入想要的表格名稱
ORDER BY ORDINAL_POSITION ASC

 

SELECT a.name, b.TABLE_NAME, c.COLUMN_NAME,  *
FROM    master.dbo.sysdatabases  a
LEFT JOIN INFORMATION_SCHEMA.Tables b ON a.NAME=b.TABLE_CATALOG
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON b.TABLE_NAME=c.TABLE_NAME
WHERE   
  a.NAME IN (
			   SELECT name FROM master.dbo.sysdatabases 
			   WHERE name LIKE 'CHACC_' + '%' 
			) --輸入想要的表格名稱
AND b.TABLE_NAME like '%' + 'TBACC' + '%'
ORDER BY a.NAME

 
 
sp_helpindex TBTRSACT

查詢PK
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'tCusAddOtherCategory'

欄位更名變動
--ALTER TABLE tCusAddOtherCategory CHANGE UpadateDate UpdateDate datetime;

 SP_RENAME 'tCusAddOtherCategory.UpadateDate', 'UpdateDate', 'COLUMN'
 
 alter TABLE [dbo].[tStuStateChange] ALTER COLUMN [ClassNo] [nvarchar](10) 
alter TABLE [dbo].[tStuStateChange] ALTER COLUMN [ClassNoOld] [nvarchar](10) 

alter table tCEUStudent alter column EmergencyRelationID int  NULL  

ALTER TABLE customers ALTER COLUMN Discount DECIMAL(18, 2);

ALTER TABLE tCEUStudent ADD EmergencyRelationID int

ALTER TABLE customers DROP COLUMN Discount;

alter TABLE ITFORM_BUYACT ADD greenPurchaseMoney decimal(9,0)
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ºñ¦â±ÄÁʪ÷ÃB' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ITFORM_BUYACT', @level2type=N'COLUMN',@level2name=N'greenPurchaseMoney'

## 預防死結的程式技巧


:::

---

::: spoiler 考試


:::

---

## 章節

### 第一堂

### 第二堂

### 第三堂

### 第四堂

### 第五堂

### 第六堂
---

:::spoiler [第一堂](###第一堂)

使用軟體: Software Ideas Modeler

小型企業:

大型企業:


Database:


* SSMS(SQL Server Management Studio): SQL Server的主管理控制台，建立資料庫物件(如資料庫，表，儲存過程，檢視等)，檢視該資料在資料庫中，組態使用者帳戶，執行備份，複製，資料庫之間的資料傳輸，等等。


* DBMS(Database Management System):操縱和管理數據庫的大型軟件，是用於建立、使用和維護數據庫。它對數據庫進行統一的管理和控制，以保證數據庫的安全性和完整性。


# 建立 course 資料庫


欄位:

1.var，意思是可變動的，因為欄位長度可變動，所以會額外花費2Byte去儲存地址(有多少用多少)

nvarchar：可變動長度的nchar

varchar：可變動長度的char

2.n，支援UNICODE UCS-2字元，因為萬國編碼(支援中文字)，所以1字儲存2Byte

nvarchar：可變動長度的nchar

nchar：固定長度的char

3.實際儲存空間：

char(n)：n Byte

varchar(n)：(n + 2) Byte --2Byte記錄地址

nchar(n)：(2 * n) Byte

nvarchar(n)：(2 * n + 2) Byte

## 建議：

確認一定長度，且只會有英數字，如身分證字號：char

確認一定長度，且可能會用非英數以外的字元：nchar

長度可變動，且只會有英數字：varchar

長度可變動，且可能會用非英數以外的字元：nvarchar

# 指令建Table


* mdf檔:無法復原，記得備份

* ldf檔:可復原


# multi-valued

* 方法1: 開欄位


* 方法2: 開Table


# Relational Keys

* Super Key : 唯一識別



* Candidate Key : 一個以上的Superkey，都要Candidate Key


* Primary Key : 從Candidate Key 挑出實務上常用欄位Primary Key

* Foreign Key : 外鍵/外部鍵 : 關聯中被用來參考到其他表格主鍵的關聯鍵，就是外鍵

:::

::: spoiler [第二堂](###第二堂)

# Sequence diagram : 循序圖就是要清楚地傳遞各物件在時間軸上的互動情境

[UML](https://terryjryeh.blogspot.com/2019/03/uml-sequence-diagrams-8.html)

範例:

# Object-oriented SA to help Table design



# What is an (non-static) Attribute?


# Attribute analyses


# Associations


## Multiplicity (more)


## one-to-one


## one-to-many


## 單一Table查詢速度越快，Table越多越慢(一個Table是一個迴圈，兩個是兩個迴圈，以此類推)

### 為什麼要拆Table: 因ont-to-many，無法整合在一張表內

## Relationship of visiting


## Relationship Between Normal Forms



## 1NF to 2NF


### 部分依靠或完全依靠，如果是部分依靠，可以另外開一個Table


## Third Normal From(3NF)


## 2NF to 3NF


:::

:::spoiler [第三堂](###第三堂)


# Possible way to find stisfied records

1.linear search all the table

2.Binary search the table if the table is ordered by field1

3.Create index for field1 beforehand, the
a.query the index(ex.tree search) to get the record location
b.go to the location to get the content of record

:::

:::spoiler [第四堂](###第四堂)


# Table description

---

# 索引

Index Basic Concepts


Index Types :Sparse Index Files


Secondary index(非必要不建議建立)


:::

:::spoiler [第五堂](###第五堂)

# Run SQLexecuteReader, withougt transaction


* sqlDataReader 一次連線約需耗掉2k記憶體






# Concurrent Executions

# Recoverability：當機回復




:::

:::spoiler [第六堂](###第六堂)

### Lock-Based Protocols

### Locking Protocol

### Pitfalls Of Lock-Based Protocols

### Optimistic Locking


### Main types of locks


### Unique number with blind update

### Trigger 適合用在驗證資料，例如：更改商品標價只能輸入一個區間的值

:::