MySQL資料庫教學

SQL-99: 綱要定義、基本限制與查詢 (1)
國立聯合大學資訊管理學系
陳士杰老師
▊ Outlines






Database Languages
SQL資料型態
Data Query Language, DQL (資料查詢語言)
Data Definition Language, DDL (資料定義語言)
Data Manipulation Language, DML (資料處理語言)
Data Control Language, DCL (資料控制語言)
(see Chapter 8, Chapter 9; 實作講義Ch. 5~Ch. 7)
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
▊ Database Languages

SQL (Structured Query Language, 結構化查詢語言) 是一種標準的
關連式資料庫語言，由IBM於1970年代所研發出來的。


SQL為用來與關連式資料庫系統對話而使用的語言

幾乎目前所有的資料庫管理系統都支援SQL
目前版本為1999年所提出的SQL/99或稱SQL/3，對物件導向DB
與分散式DB有提供支援，並加入了程式設計的功能預存程
式(stored procedure)
國立聯合大學資訊管理學系
(早期版本：SQL/87或稱SQL/1, SQL/92或稱SQL/2)
資料庫系統課程 (陳士杰)

SQL指令的種類：

資料定義語言 (Data Definition Language, DDL)



資料處理語言 (Data Manipulation Language, DML)



用來從事資料庫的權限控管，如：GRANT, REVOKE, ALTER PASSWORD
資料管理指令 (Data Administration Commands)


用來查詢資料庫中的資料，主要指令是Select
資料控制語言 (Data Control Language, DCL)


用來操作資料庫中的資料 (即：插入(Insert)，更新(Update)，刪除(Delete))
針對Table內的Data
資料查詢語言 (Data Query Language, DQL)


用來宣告、建立資料庫物件 (即：建立(Create), 刪除(Drop), 更改(Alter))
即：針對Table或View的Schema
用來從事資料庫的稽核與分析
交易控制指令 (Transactional Control Commands)

用來管理資料庫的交易動作
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
各家的資料庫系統對於絕大多數的國際標準
SQL語法 (ANSI SQL) 皆有支援。然而，在各資
料庫系統中還是留有一些專屬的功能。因
此，了解各家資料庫系統SQL語法與ANSI SQL
之差異點至為重要。
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
▊ SQL資料型態


ANSI SQL的字串 (Character Strings)

CHAR(n)：固定長度字元串 (n為字元個數)

VARCHAR(n)：變動長度字元串

BIT(n)：固定長度位元串 (n為位元個數)

BIT VARING(n)：變動長度位元串
MySQL的字串型態：
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
何謂列舉型態與集合型態？

列舉型態 ENUM：(多選一)



這種型態的欄位，其存放的值只能是列舉清單中的其中一項。
列舉清單中最多可有65535個項目，也可以有 NULL 值。
範例：



Field1 ENUM('Am','Pm') DEFAULT ‘Am’
Field2 ENUM(‘經理’,‘工程師’,‘技術員’) DEFAULT ‘工程師’
集合型態 SET：(多選多)


SET 型態欄位可以存放清單中的多個項目。它適合應用於網頁表單
中，選擇多個項目儲存的場合。
範例：


Field1 SET(‘工程師’,‘技術員’,‘專案經理’)。
若Field1這個欄位表示 “職位”，且公司中有一位工程師同時身兼某專案
的專案經理，則在資料庫中插入此工程師之職位時之指令為：
INSERT INTO Field1 VALUES (‘工程師’,‘專案經理’)
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

ANSI SQL數值串(Numeric Strings)


INT, INTEGER：整數 (4bytes; 0~ 0-4294967295 )
DEC(m,n), DECIMAL(m,n), NUMERIC(m,n)：格式化數值 (m：總位數或精確度，
n：小數位數)

SMALLINT：短整數 (2bytes; 0~65535)

FLOAT：浮點數 (4bytes)

REAL：單精度實數 (4bytes)

DOUBLE PRECISION：雙精度實數 (8bytes)
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

MySQL的數值串型態：
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

ANSI SQL日期/時間 (Date/Time)資料型態

DATE：一般格式為 YYYY-MM-DD

TIME：一般格式為 HH:MM:SS

TIMESTAMP：時間戳記，由DATE+TIME+六位以上小數秒數



用以記錄交易進入系統的時間順序
INTERVAL：時間區間
MySQL的日期/時間型態：
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
供應商(Supplier)
專案供應零件(Project_supp_Component)
專案(Project)
零件(Component)
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
▊ Data Definition Language, DDL (資料定義語言) - - (1)

DDL主要有CREATE, DROP, ALTER三個指令，並可針對以下三個物
件進行操作：

資料庫 (database)

表格 (Table)

觀點 (View)
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
建立、刪除資料庫

在進行Create、Drop資料庫的指令操作時，對 “資料庫” 這個
關鍵字有時是用 “Schema” 來表示。


CREATE SCHEMA (DATABASE): 建立一個新的資料庫 (資料庫綱要)

CREATE SCHEMA <資料庫名>; 或 CREATE DATABASE <資料庫名>;

例：CREATE SCHEMA/DATABASE Jacy_Database;
DROP SCHEMA (DATABASE): 刪除一個資料庫 (資料庫綱要)

DROP SCHEMA <資料庫名>; 或 DROP DATABASE <資料庫名>;

例：DROP SCHEMA/DATABASE Jacy_Database;
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

在MySQL中，



建立一個新的資料庫 (資料庫綱目)

CREATE SCHEMA <資料庫名>

CREATE DATABASE <資料庫名>
刪除一個資料庫 (資料庫綱目)

DROP SCHEMA <資料庫名> [CASCADE/RESTRICT];

DROP DATABASE <資料庫名> [CASCADE/RESTRICT];
資料庫欄位及名稱有分大小寫，SQL指令並無區分。
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
建立、刪除、更改表格

指定表格
中的欄位
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…]
);
括號內最後一行指令不需要逗號!!
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
對具有特
定用途之
欄位加以
設定

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)
);
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

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)
);
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

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
);
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

註：設定外來鍵時，關於ON DELETE與ON UPDATE的處理動作有
以下幾種：





NO ACTION：發生違反完整性限制的操作時，外來鍵內的資料不會有
任何動作
RESTRICT：發生違反完整性限制的操作時，DBMS不允許該操作執行
CASCADE：發生違反參考完整性限制的操作時，外來鍵內的資料連帶
更新或刪除
SET NULL：發生違反參考完整性限制的操作時，外來鍵內的資料設為
空值
SET DEFAULT：發生違反完整性限制的操作時，外來鍵內的資料設為預
設值
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

關於MySQL資料表格有兩種常用的類型：


MyISAM

為MySQL預設的表格類型 (當ENGINE那一行指令未填寫的話)

成熟、穩定、容易管理。若無特殊需求，應以此類型為主。
InnoDB


支援交易(Transaction)機制、外來鍵(Foreign Key)、當機復原(若檔案系統未遭
受損壞時使用)
MyISAM v.s. InnoDB

若想追求使用空間與執行效率，建議採用MyISAM

若著重交易工作、安全性考量或是可能有多人同時修改資料的情
況，則建議採用InnoDB
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

在MySQL中，建立一個新的表格：

CREATE TABLE <table name>
( <attribute name 1> <data type> [<(not) null>] [<default value>],
<attribute name 2> <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標準語法。
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

CREATE TABLE Employee_01
( Ssn CHAR(10) NOT NULL,
Emp_id CHAR(10) NOT NULL,
Address VARCHAR(50),
Dept_id INT not null,
Proj_id INT not null,
Salary NUMERIC(8,1) NOT NULL DEFAULT 18000,
Primary Key(Ssn)
);
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
若表格間有外來鍵存在時之建表順序

假設有三個表：Department, Project, Employee_02。其中：

Employee_02有兩個外來鍵，分別指向Department與Project

建表順序：先建立 “被參考表格” (即：Project, Department)，再建立 “參
考表格” (即：Employee_02)

在MySQL中，不論是參考表格，還是被參考表格，只要與外來鍵設定
有關的表格，皆需設定成InnoDB類型的表格。

反之，表格刪除時，先刪除參考表格，再刪除被參考表格。

若有兩個表格互相參考時，可先建立這兩個表格但不設定
外來鍵，待兩表格的所有欄位與主鍵皆設定完成後，再以
更改表格 (Alter Table) 的方式加入兩表格之外來鍵。
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

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;
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

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;
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

DROP TABLE: 刪除一個關聯 (表格)
DROP TABLE <表格名>;

例：


DROP TABLE Employee_01;
有外來鍵之表格在刪除時，先刪除參考表格，再刪除被參考
表格。
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

ALTER TABLE: 更改表格中之某欄位的基本定義與限制。包括：
增加欄位、刪除欄位、修改欄位定義或條件等。
ALTER TABLE <表格名> ADD/DROP/ALTER

最重要的幾種用法：


新增欄位：

ALTER TABLE <表格名> ADD <欄位名> <資料型態> [<(not) null> <預設值>]

例：ALTER TABLE Employee_02 ADD SEX CHAR(1);
修改欄位：

ALTER TABLE <表格名> ALTER <欄位名> <資料型態> <(not) null> <預設值>

例：ALTER TABLE Employee_02 ALTER Salary DROP DEFAULT;
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)


刪除欄位：

ALTER TABLE <表格名> DROP <欄位名>

例：ALTER TABLE Employee_02 DROP Address;
新增主鍵、唯一鍵、外來鍵：

ALTER TABLE <表格名> ADD PRIMARY KRY <欄位名>;

ALTER TABLE <表格名> ADD UNIQUE <欄位名>;

ALTER TABLE <表格名> ADD FOREIGN KRY <欄位名> REFERENCES <被參考表格
名> (<欄位名>)
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
練習範例

請建立slide 9的四個表格。
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
▊ Data Manipulation Language, DML (資料處理語言)

DML是針對關聯中的資料部份從事處理，包含Insert, Update,
Delete指令。

INSERT: 插入一筆新的紀錄到關聯中。

DELETE:根據WHERE條件刪除關聯中的紀錄。

UPDATE: 根據WHERE條件更改關聯中的屬性值。
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

INSERT: 插入一筆新的紀錄到關聯中。
INSERT INTO <table name>[(attribute1, attribute2,…)] VALUES (…)
 例：假設現有一關聯表格EMPLOYEE(Ssn,

FName, LName, Bdate, Add, Tel, Salary)
INSERT INTO EMPLOYEE(Ssn, FName, LName, Salary) VALUES (‘F111111111’, ‘Jacy’, ‘Chen’, ‘100’);
//此例為插入部份欄位資料

INSERT INTO EMPLOYEE VALUES (‘F111111111’, ‘Jacy’, ‘Chen’, 1977-01-01, ‘Taipei’, NULL, ‘100’);
//此例為插入全部欄位資料
 若多個表格間有外來鍵時，先插入
“被參考表格” 的資料，再插入
“參考表格” 的資料
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

DELETE:根據WHERE條件刪除關聯中的紀錄。
DELETE FROM <table name> WHERE <condition>

例：假設現有一關聯表格EMPLOYEE(Ssn, FName, LName, Bdate, Add, Tel, Salary)

DELETE FROM EMPLOYEE WHERE BDate < 1975-02-02;

DELETE FROM EMPLOYEE WHERE Ssn = ‘A1234567’;

DELETE FROM EMPLOYEE;
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

UPDATE: 根據WHERE條件更改關聯中的屬性值。
UPDATE <table name> SET <attribute name>=<new value> WHERE <condition>

例：假設現有一關聯表格EMPLOYEE(Ssn, FName, LName, Bdate, Add, Tel, Salary)

UPDATE EMPLOYEE
SET Tel = ‘12365649’, Salary = ‘200’
WHERE Ssn = ‘A9876543’;
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
練習範例

請將為slide 9所建立的四個空表格插入其應有的資料。
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)
 建立表格的建議步驟
1.
廢除表格。若不知所欲建立的表格是否已存在於DB中，先
執行此步驟，以免原表格已存在而產生錯誤。
2.
建立表格。
3.
建立個體完整性限制與定義域完整性限制。
4.
建立參考完整性限制。
5.
插入資料。
國立聯合大學資訊管理學系
資料庫系統課程 (陳士杰)

Download Report