2016年1月11日 星期一

資料庫概念隨手筆記

Transaction
     commit
     全部sql執行成功將結果交給資料庫
     rollback
     其中一個sql執行失敗就必須還原全部動作

ACID
     完成姓A
     異動執行若非全部認可就是全部失敗
     一致性C
     遵守資料庫強制的規定，否則錯誤
     隔離性I
     異動執行不允許查詢
     耐久性D
     異動完成資料無法馬上變更

B-tree
     多欄位索引
定義除了繼承二元樹的定義外，二元搜索樹本身也有額外的定義，但可能會看到幾種不同的說法，而較多數人使用的定義如下：
  1. 左子樹不為空，則左子樹的所有節點的鍵值(Key)小於根節點的鍵值。
  2. 右子樹不為空，則右子樹的所有節點的鍵值(Key)大於根節點的鍵值。
  3. 左右子樹也都是二元搜索樹。
  4. 節點不會有重複的鍵值。
這個定義是樹中的節點都具有Key-value pair情況，有時候可能會其他變化：
  1. 沒有鍵值，而用值(Value)來比較。
  2. 允許重複的資料，此時會出現等於的情況，則將定義1.改成小於等於或者定義2.改成大於等於。

unique
不允許重複值，也就是唯一值
primary
     不能為空值，也就是主鍵值

正規化

1.  第一 : 同一欄位不能有一筆以上的資料(單一值)    
錯誤:
交易
顧客日期數量
PeteMonday19.00
-28.20
PeteWednesday-84.00
SarahFriday100.00
150.00
-40.00

正確:
 
交易
顧客日期數量
PeteMonday19.00
PeteMonday-28.20
PeteWednesday-84.00
SarahFriday100.00
SarahFriday150.00
SarahFriday-40.00
 

2.  第二 : 同一個table應該是要相關的資料
錯誤:
元件來源
元件 ID (主鍵)價格供應商ID (主鍵)供應商名稱供應商住址
6559.991Stylized PartsVA
7320.001Stylized PartsVA
6569.992ACME IndustriesCA
正確:
供應商
供應商 ID (主鍵)名稱住址
2ACME IndustriesCA
1Stylized PartsVA

元件來源
元件 ID (主鍵)價格供應商 ID（主鍵、外來鍵）
6559.991
7320.001
6569.992

3.  第三 : 非主鍵欄位不能有依賴關係 ex 數量 單價 小計(x)

錯誤:

價格供應商名稱供應商住址
59.99ACME IndustriesCA
20.00ACME IndustriesCA
69.99Stylized PartsVA



Primary Key
主鍵 (Primary Key) 中的每一筆資料都是表格中的唯一值。換言之，它是用來獨一無二地確認一個表格中的每一行資料。主鍵可以是原本資料內的一個欄位，或是一個人造欄位 (與原本資料沒有關係的欄位)。主鍵可以包含一或多個欄位。當主鍵包含多個欄位時，稱為組合鍵 (Composite Key)。

主鍵可以在建置新表格時設定 (運用 CREATE TABLE 語句)，或是以改變現有的表格架構方式設定 (運用 ALTER TABLE 語句)

MySQL:
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));
Oracle:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
SQL Server:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
以下則是以改變現有表格架構來設定主鍵的方式：
MySQL: 
ALTER TABLE Customer ADD PRIMARY KEY (SID);
Oracle: 
ALTER TABLE Customer ADD PRIMARY KEY (SID);
SQL Server: 
ALTER TABLE Customer ADD PRIMARY KEY (SID);

請注意，在用 ALTER TABLE 語句來添加主鍵之前，我們需要確認被用來當做主鍵的欄位是設定為 『NOT NULL』 ；也就是說，那個欄位一定不能沒有資料



FOREIGN KEY
外來鍵是一個(或數個)指向另外一個表格主鍵的欄位。外來鍵的目的是確定資料的參考完整性 (Referential Integrity)。換言之，只有被准許的資料值才會被存入資料庫內。
舉例來說，假設我們有兩個表格：一個 CUSTOMER 表格，裡面記錄了所有顧客的資料；另一個 ORDERS 表格，裡面記錄了所有顧客訂購的資料。在這裡的一個限制，就是所有的訂購資料中的顧客，都一定是要跟在 CUSTOMER 表格中存在。在這裡，我們就會在 ORDERS 表格中設定一個外來鍵，而這個外來鍵是指向 CUSTOMER 表格中的主鍵。這樣一來，我們就可以確定所有在 ORDERS 表格中的顧客都存在 CUSTOMER 表格中。換句話說，ORDERS 表格之中，不能有任何顧客是不存在於 CUSTOMER 表格中的資料。
這兩個表格的結構將會是如下：
CUSTOMER 表格
欄位名性質
SID主鍵
Last_Name
First_Name
ORDERS 表格
欄位名性質
Order_ID主鍵
Order_Date
Customer_SID外來鍵
Amount
在以上的例子中，ORDERS 表格中的 Customer_SID 欄位是一個指向 CUSTOMER 表格中 SID 欄位的外來鍵。
以下列出幾個在建置 ORDERS 表格時指定外來鍵的方式：
MySQL:
CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
PRIMARY KEY (Order_ID),
FOREIGN KEY (Customer_SID) REFERENCES CUSTOMER (SID));
Oracle:
CREATE TABLE ORDERS
(Order_ID integer PRIMARY KEY,
Order_Date date,
Customer_SID integer REFERENCES CUSTOMER (SID),
Amount double);
SQL Server:
CREATE TABLE ORDERS
(Order_ID integer PRIMARY KEY,
Order_Date datetime,
Customer_SID integer REFERENCES CUSTOMER (SID),
Amount double);
以下的例子則是藉著改變表格架構來指定外來鍵。這裡假設 ORDERS 表格已經被建置，而外來鍵尚未被指定：
MySQL:
ALTER TABLE ORDERS
ADD FOREIGN KEY (Customer_SID) REFERENCES CUSTOMER (SID);
Oracle:
ALTER TABLE ORDERS
ADD (CONSTRAINT fk_orders1) FOREIGN KEY (Customer_SID) REFERENCES CUSTOMER (SID);

SQL Server:
ALTER TABLE ORDERS 
ADD FOREIGN KEY (Customer_SID) REFERENCES CUSTOMER (SID);




ALTER TABLE

在表格被建立在資料庫中後，我們常常會發現，這個表格的結構需要有所改變。常見的改變如下：
  • 加一個欄位
  • 刪去一個欄位
  • 改變欄位名稱
  • 改變欄位的資料種類
以上列出的改變並不是所有可能的改變。ALTER TABLE 也可以被用來作其他的改變，例如改變主鍵定義。
ALTER TABLE 的語法如下：
ALTER TABLE "table_name"
[改變方式];
[改變方式] 的詳細寫法會依我們想要達到的目標而有所不同。再以上列出的改變中，[改變方式] 如下：
  • 加一個欄位: ADD "欄位 1" "欄位 1 資料種類"
  • 刪去一個欄位: DROP "欄位 1"
  • 改變欄位名稱: CHANGE "原本欄位名" "新欄位名" "新欄位名資料種類"
  • 改變欄位的資料種類: MODIFY "欄位 1" "新資料種類"
以下我們用在CREATE TABLE 一頁建出的 Customer 表格來當作例子：
Customer 表格
欄位名稱資料種類
First_Namechar(50)
Last_Namechar(50)
Addresschar(50)
Citychar(50)
Countrychar(25)
Birth_Datedatetime
第一，我們要加入一個叫做 "Gender" 的欄位。這可以用以下的指令達成：
ALTER TABLE Customer ADD Gender char(1);
這個指令執行後的表格架構是：
Customer 表格
欄位名稱資料種類
First_Namechar(50)
Last_Namechar(50)
Addresschar(50)
Citychar(50)
Countrychar(25)
Birth_Datedatetime
Genderchar(1)
接下來，我們要把 "Address" 欄位改名為 "Addr"。這可以用以下的指令達成：
ALTER TABLE Customer CHANGE Address Addr char(50);
這個指令執行後的表格架構是：
Customer 表格
欄位名稱資料種類
First_Namechar(50)
Last_Namechar(50)
Addrchar(50)
Citychar(50)
Countrychar(25)
Birth_Datedatetime
Genderchar(1)
再來，我們要將 "Addr" 欄位的資料種類改為 char(30)。這可以用以下的指令達成：
ALTER TABLE Customer MODIFY Addr char(30);
這個指令執行後的表格架構是：
Customer 表格
欄位名稱資料種類
First_Namechar(50)
Last_Namechar(50)
Addrchar(30)
Citychar(50)
Countrychar(25)
Birth_Datedatetime
Genderchar(1)
最後，我們要刪除 "Gender" 欄位。這可以用以下的指令達成：
ALTER TABLE Customer DROP Gender;
這個指令執行後的表格架構是：
Customer 表格
欄位名稱資料種類
First_Namechar(50)
Last_Namechar(50)
Addrchar(30)
Citychar(50)
Countrychar(25)
Birth_Datedatetime


DROP TABLE 
有時候我們會決定我們需要從資料庫中清除一個表格。事實上，如果我們不能這樣做的話，那將會是一個 很大的問題，因為資料庫管理師 (Database Administrator - DBA) 勢必無法對資料庫做有效率的管理。 還好，SQL 有提供一個 DROP TABLE 的語法來讓我們清除表格。 DROP TABLE 的語法是：
DROP TABLE "表格名";
我們如果要清除在上一頁中建立的顧客表格，我們就鍵入：
DROP TABLE Customer;



TRUNCATE TABLE
有時候我們會需要清除一個表格中的所有資料。要達到者個目的，一種方式是我們在 上一頁看到 的 DROP TABLE 指令。不過這樣整個表格就消失，而無法再被用了。另一種方式就是運 用 TRUNCATE TABLE的指令。在這個指令之下，表格中的資料會完全消失， 可是表格本身會繼續存在。 TRUNCATE TABLE 的語法為下：
TRUNCATE TABLE "表格名";
所以，我們如果要清除在 SQL Create Table 那一頁建立的顧客表格之內的資料，我們就鍵入：
TRUNCATE TABLE Customer;




UPDATE 
我們有時候可能會需要修改表格中的資料。在這個時候，我們就需要用到UPDATE 指令。這個指令的語法是：
UPDATE "表格名"
SET "欄位1" = [新值]
WHERE "條件";
最容易瞭解這個語法的方式是透過一個例子。假設我們有以下的表格：
Store_Information 表格
Store_NameSalesTxn_Date
Los Angeles150005-Jan-1999
San Diego25007-Jan-1999
Los Angeles30008-Jan-1999
Boston70008-Jan-1999
我們發現說 Los Angeles 在 1999 年 1 月 8 號的營業額實際上是 $500，而不是表格中所儲存的 $300，因此我們用以下的 SQL 來修改那一筆資料：
UPDATE Store_Information
SET Sales = 500
WHERE Store_Name = 'Los Angeles'
AND Txn_Date = 'Jan-08-1999';
現在表格的內容變成：
Store_Information 表格
Store_NameSalesTxn_Date
Los Angeles150005-Jan-1999
San Diego25007-Jan-1999
Los Angeles50008-Jan-1999
Boston70008-Jan-1999
在這個例子中，只有一筆資料符合 WHERE 子句中的條件。如果有多筆資料符合條件的話，每一筆符合條件的資料都會被修改的。
我們也可以同時修改好幾個欄位。這語法如下：
UPDATE "表格"
SET "欄位1" = [值1], "欄位2" = [值2]
WHERE "條件";



DELETE FROM
在某些情況下，我們會需要直接由資料庫中去除一些資料。這可以藉由 DELETE FROM 指令來達成。它的語法是：
DELETE FROM "表格名"
WHERE "條件";
以下我們用個實例說明。假設我們有以下這個表格：
Store_Information 表格
Store_NameSalesTxn_Date
Los Angeles150005-Jan-1999
San Diego25007-Jan-1999
Los Angeles30008-Jan-1999
Boston70008-Jan-1999
而我們需要將有關 Los Angeles 的資料全部刪除。在這裡我們可以用以下的 SQL 來達到這個目的:
DELETE FROM Store_Information
WHERE Store_Name = 'Los Angeles';
現在表格的內容變成：
Store_Information 表格
Store_NameSalesTxn_Date
San Diego250Jan-07-1999
Boston700Jan-08-1999
張貼者： 於 沒有留言:
標籤：
訂閱： 文章 (Atom)