Microsoft PowerPoint - 資料庫-08 [相容模式]

Transcription

1 第八單元 建立資料庫 1

2 本章重點 本章介紹在 MySQL 中建立資料庫 修改資料庫屬性 刪除資料庫... 等功能 操作的方法主要有兩種 : 一是利用 Workbench 功能和命令來進行 另一種則是執行 SQL 敘述來達成 如何建立資料庫 各種修改資料庫屬性與設定的方法 如何刪除資料庫 2

3 8-1 使用 workbench 建立資料庫 3

4 SQL 指令的種類 : 資料定義語言 (Data Definition Language, DDL) 用來宣告 建立資料庫物件 ( 即 : 建立 (Create), 刪除 (Drop), 更改 (Alter)) 即 : 針對 Table 或 View 的 Schema 資料處理語言 (Data Manipulation Language, DML) 用來操作資料庫中的資料 ( 即 : 插入 (Insert), 更新 (Update), 刪除 (Delete)) 針對 Table 內的 Data 資料查詢語言 (Data Query Language, DQL) 用來查詢資料庫中的資料, 主要指令是 Select 資料控制語言 (Data Control Language, DCL) 用來從事資料庫的權限控管, 如 :GRANT, REVOKE, ALTER PASSWORD

5 SQL 資料型態 MySQL 的字串型態 :

6 何謂列舉型態與集合型態? 列舉型態 ENUM:( 多選一 ) 這型態欄位, 其存放值只能列舉清單中的其中一項 列舉最多可有 個項目, 也可有 NULL 值 範例 : Field1 ENUM('Am','Pm') DEFAULT Am Field2 ENUM( 經理 ', 工程師 ', 技術員 ') DEFAULT 工程師 '

7 何謂列舉型態與集合型態? 集合型態 SET:( 多選多 ) SET 型態欄位可以存放清單中的多個項目 它適合應用於網頁表單中, 選擇多個項目儲存的場合 範例 : Field1 SET( 工程師 ', 技術員 ', 專案經理 ') 若 Field1 這個欄位表示 職位 ", 且公司中有一位工程師同時身兼某專案的專案經理, 則在資料庫中插入此工程師之職位時之指令為 : INSERT INTO Field1 VALUES ( 工程師 ', 專案經理 ')

8 MySQL 的數值串型態 :

9 MySQL 的日期 / 時間型態 :

10 供應商 (Supplier) 專案供應零件 (Project_supp_Component) 專案 (Project) 零件 (Component)

11 Data Definition Language, DDL ( 資料定義語言 ) DDL 主要有 CREATE, DROP, ALTER 三個指令, 並可針對以下三個物件進行操作 : 資料庫 (database) 表格 (Table) 觀點 (View)

12 建立 刪除資料庫 在進行 Create Drop 資料庫的指令操作時, 對資料庫這個關鍵字有時是用 Schema 來表示 CREATE SCHEMA (DATABASE): 建立一個新的資料庫 ( 資料庫綱要 ) CREATE SCHEMA < 資料庫名 >; 或 CREATE DATABASE < 資料庫名 >; 例 :CREATE SCHEMA/DATABASE Jacy_Database;

13 建立 刪除資料庫 DROP SCHEMA (DATABASE): 刪除一個資料庫 ( 資料庫綱要 ) DROP SCHEMA < 資料庫名 >; 或 DROP DATABASE < 資料庫名 >; 例 :DROP SCHEMA/DATABASE Jacy_Database;

14 在 MySQL 中, 建立一個新的資料庫 ( 資料庫綱目 ) CREATE SCHEMA < 資料庫名 > CREATE DATABASE < 資料庫名 > 刪除一個資料庫 ( 資料庫綱目 ) DROP SCHEMA < 資料庫名 > [CASCADE/RESTRICT]; DROP DATABASE < 資料庫名 > [CASCADE/RESTRICT]; 資料庫欄位及名稱有分大小寫,SQL 指令並無區分

15 建立 刪除 更改表格 CREATE TABLE: 建立一個新的關聯 ( 表格 ) CREATE TABLE <table name>( < 欄位名 1> <data type> [Null/Not null] [DEFAULT < 預設值 >], < 欄位名 2> <data type> [Null/Not null] [DEFAULT < 預設值 >], < 欄位名 n> <data type> [Null/Not null] [DEFAULT < 預設值 >], PRIMARY KEY(< 欄位名 >), UNIQUE(< 欄位名 >), FOREIGN KEY(< 欄位名 >) REFERENCES < 表格名欄位名 > [ON DELETE /ON UPDATE ] ); 對具有特定用途之欄位加以設定 指定表格中的欄位 括號內最後一行指令不需要逗號!!

16 CREATE TABLE Department (Dname CHAR(10) NOT NULL, Dno INT NOT NULL, Dadd CHAR(20), PRIMARY KEY(Dno) ); CREATE TABLE Project (Pname CHAR(10) NOT NULL, Pno INT NOT NULL, PRIMARY KEY(Pno) );

17 CREATE TABLE Employee_01 ( Ssn CHAR(10) NOT NULL, Emp_id CHAR(10) NOT NULL, Address VARCHAR(50), Dept_id INT, Proj_id INT, Salary NUMERIC(8,1) NOT NULL DEFAULT 18000, Primary Key(Ssn) );

18 CREATE TABLE Employee_02 ( Ssn CHAR(10) NOT NULL, Emp_id CHAR(10) NOT NULL, Address VARCHAR(50), Dept_id INT, Proj_id INT, Salary NUMERIC(8,1) NOT NULL DEFAULT 18000, PRIMARY KEY(Ssn), UNIQUE(Emp_id), FOREIGN KEY(Proj_id) REFERENCES Project(Pno), FOREIGN KEY(Dept_id) REFERENCES Department(Dno) ON Delete CASCADE );

19 設定外來鍵時, 關於 ON DELETE 與 ON UPDATE 的處理動作有以下幾種 : NO ACTION: 發生違反完整性限制的操作時, 外來鍵內的資料不會有任何動作 RESTRICT: 發生違反完整性限制的操作時, DBMS 不允許該操作執行 CASCADE: 發生違反參考完整性限制的操作時, 外來鍵內的資料連帶更新或刪除 SET NULL: 發生違反參考完整性限制的操作時, 外來鍵內的資料設為空值 SET DEFAULT: 發生違反完整性限制的操作時, 外來鍵內的資料設為預設值

20

21

22

23

24 關於 MySQL 資料表格有兩種常用的類型 : MyISAM 為 MySQL 預設的表格類型 ( 當 ENGINE 那一行指令未填寫的話 ) 穩定 容易管理 若無特殊需求, 應以此類型為主 InnoDB 支援交易 (Transaction) 機制 外來鍵 (Foreign Key) 當機復原 ( 若檔案系統未遭受損壞時使用 ) MyISAM v.s. InnoDB 若想追求使用空間與執行效率, 建議採用 MyISAM 若著重交易工作 安全性考量或是可能有多人同時修改資料的情況, 則建議採用 InnoDB

25 在 MySQL 中, 建立一個新的表格 : CREATE TABLE <table name> ( <attribute name 1> <data type> [<(not) null>] [<default value>], <attribute name n> <data type> [<(not) null>] [<default value>], PRIMARY KEY(<attribute name>), UNIQUE(<attribute name>), FOREIGN KEY(<attribute name>) REFERENCES <table name(attribute name)>, FOREIGN KEY(<attribute name>) REFERENCES <table name(attribute name)> ON DELETE /ON UPDATE, )ENGINE = MyISAM/InnoDB; ( 此行若不打, 則預設為 MyISAM 格式 ) 刪除 更改一個表格可採用後面即將介紹的 SQL 標準語法

26 若表格間有外來鍵存在時之建表順序 假設有三個表 :Department, Project, Employee_02 其中 : Employee_02 有兩個外來鍵, 分別指向 Department 與 Project 建表順序 : 先建立 被參考表格 " ( 即 :Project, Department), 再建立 參考表格 " ( 即 : Employee_02)

27 若表格間有外來鍵存在時之建表順序 在 MySQL 中, 不論是參考表格, 還是被參考表格, 只要與外來鍵設定有關的表格, 皆需設定成 InnoDB 類型的表格 反之, 表格刪除時, 先刪除參考表格, 再刪除被參考表格 若有兩個表格互相參考時, 可先建立這兩個表格但不設定外來鍵, 待兩表格的所有欄位與主鍵皆設定完成後, 再以更改表格 (Alter Table) 的方式加入兩表格之外來鍵

28 CREATE TABLE Department (Dname CHAR(10) NOT NULL, Dno INT NOT NULL, Dadd CHAR(20), PRIMARY KEY(Dno) ) ENGINE=INNODB; CREATE TABLE Project (Pname CHAR(10) NOT NULL, Pno INT NOT NULL, PRIMARY KEY(Pno) ) ENGINE=INNODB;

29 CREATE TABLE Employee_02 ( Ssn CHAR(10) NOT NULL, Emp_id CHAR(10) NOT NULL, Address VARCHAR(50), Dept_id INT, Proj_id INT, Salary NUMERIC(8,1) NOT NULL DEFAULT 18000, PRIMARY KEY(Ssn), UNIQUE(Emp_id), FOREIGN KEY(Proj_id) REFERENCES Project(Pno), FOREIGN KEY(Dept_id) REFERENCES Department(Dno) ON Delete CASCADE ) ENGINE=INNODB;

30 DROP TABLE: 刪除一個關聯 ( 表格 ) DROP TABLE < 表格名 >; 例 : DROP TABLE Employee_01; 有外來鍵之表格在刪除時, 先刪除參考表格, 再刪除被參考表格

31 ALTER TABLE: 更改表格中之某欄位的基本定義與限制 包括 : 增加欄位 刪除欄位 修改欄位定義或條件等 ALTER TABLE < 表格名 > ADD/DROP/ALTER 最重要的幾種用法 : 新增欄位 : ALTER TABLE < 表格名 > ADD < 欄位名 > < 資料型態 > [<(not) null> < 預設值 >] 例 :ALTER TABLE Employee_02 ADD SEX CHAR(1);

32 修改欄位 : ALTER TABLE < 表格名 > ALTER < 欄位名 > < 資料型態 > <(not) null> < 預設值 > 例 :ALTER TABLE Employee_02 ALTER Salary DROP DEFAULT;

33 刪除欄位 : ALTER TABLE < 表格名 > DROP < 欄位名 > 例 :ALTER TABLE Employee_02 DROP Address; 新增主鍵 唯一鍵 外來鍵 : ALTER TABLE < 表格名 > ADD PRIMARY KRY < 欄位名 >; ALTER TABLE < 表格名 > ADD UNIQUE < 欄位名 >; ALTER TABLE < 表格名 > ADD FOREIGN KRY < 欄位名 > REFERENCES < 被參考表格名 > (< 欄位名 >)

34 練習範例 請建立課本的範例表格

35 Data Manipulation Language, DML ( 資料處理語言 ) DML 是針對關聯中的資料部份從事處理, 包含 Insert, Update, Delete 指令 INSERT: 插入一筆新的紀錄到關聯中 DELETE: 根據 WHERE 條件刪除關聯中的紀錄 UPDATE: 根據 WHERE 條件更改關聯中的屬性值

36 INSERT: 插入一筆新的紀錄到關聯中 INSERT INTO <table name>[(attribute1, attribute2, )] VALUES ( ) 例 : 現有關聯表格 EMPLOYEE(Ssn, FName, LName, Bdate, Add, Tel, Salary) INSERT INTO EMPLOYEE(Ssn, FName, LName, Salary) VALUES ( F , Jacy, Chen, 100 ); // 此例為插入部份欄位資料

37 INSERT INTO EMPLOYEE VALUES ( F , Jacy, Chen, , Taipei, NULL, 100 ); // 此例為插入全部欄位資料 若多個表格間有外來鍵時, 先插入被參考表格的資料, 再插入參考表格的資料

38 DELETE: 根據 WHERE 條件刪除關聯中的紀錄 DELETE FROM <table name> WHERE <condition> 現有關聯表格 EMPLOYEE(Ssn, FName, LName, Bdate, Add, Tel, Salary) DELETE FROM EMPLOYEE WHERE BDate < ; DELETE FROM EMPLOYEE WHERE Ssn = A ; DELETE FROM EMPLOYEE;

39 UPDATE: 根據 WHERE 條件更改關聯的屬性值 UPDATE <table name> SET <attribute name>=<new value> WHERE <condition> 現有一關聯表格 EMPLOYEE(Ssn, FName, LName, Bdate, Add, Tel, Salary) UPDATE EMPLOYEE SET Tel = , Salary = 200 WHERE Ssn = A ;

40 練習範例 請用 SQL 語法將課本的範例表格加入新的值

41 建立表格的建議步驟 1. 廢除表格 若不知所欲建立的表格是否已存在於 DB 中, 先執行此步驟, 以免原表格已存在而產生錯誤 2. 建立表格 3. 建立個體完整性限制與定義域完整性限制 4. 建立參考完整性限制 5. 插入資料