講師:張凱迪(KD.Chang)
Copyright © 2014 All rights reserved
資料庫基礎
MySQL 資料庫基本使用
SQL 結構化查詢語言基礎
PHP 與 MySQL 資料庫互動
一般學習 PHP 程式主要是希望建立 Web-Based 系統和應用程式(學校的課程網站、訂便當系統、人事管理系統等),而大部分的系統多半需要和資料庫互動,提供系統存取資料的能力,所以如何讓 PHP 和資料庫互動就是非常重要的一個主題
在這邊我們不談
EM-Model、正規化、交易管理等議題,
有興趣的朋友可以參考學校資料庫管理的課程
我們的重點會放在:
PHP 如何和資料庫互動
資料庫 (Database) 是一種資料儲存的單位,亦即一群相關資料的集合 (A collection of related data)
1. 將相關的資料以系統化、有效率的方式儲存在一起
2. 一個資料庫通常針對特定需求設計
3. 透過資料庫管理系統 (DBMS) 進行管理
4. 資料庫系統 = 資料庫管理系統 + 資料庫
參考文件:資料庫系統概論
由於比起檔案系統,資料庫具備以下優點,所以適合用於資料處理和管理:
1. 避免資料重覆
2. 資料一致性
3. 資料整合與共享性較高
4. 資料較為獨立及安全
參考文件:資料庫學習實務
目前市面上大部分的資料庫管理系統為關聯式資料庫(Relational Database),例如:MySQL、PostgreSQL、SQL Server、Oracle等。但由於為了處理 Big Data 等非結構化資料問題,目前也有許多資料庫管理系統是使用 NoSQL (Not Only SQL,同常具備不使用SQL作為查詢語言,一般不用固定 Schema,常會避免使用SQL的JOIN操作等特性) 的方式(例如:MongoDB、CouchDB、Cassandra)
參考文件:NoSQL 是資料庫觀念的復興運動
我們這堂課只討論 RDB !
http://images.40era.com/201207/site/mysql.gif
http://www.gcsecomputing.org.uk/
http://allenbrowne.com/
http://www.hosting.vt.edu/tutorials/phpmysql/figure1.png
請拿出一張白紙,試著畫出學校課程網站資料庫管理的資料表關聯圖(至少含 3 個 table,教授、課程、學生。每個 table 至少要有四個欄位並寫上可能的資料型別和長度)
MySQL 是一套 Open Source 關聯式資料庫
由 C/C++語言所開發,常用於和 PHP 搭配,支援多種平台
最初由 MySQL AB 公司負責,現已被 Oracle 併購
提供多執行緒、多使用者並使用標準(ANSI) SQL
phpMyAdmin 是一套用PHP寫出來的MySQL管理程式,可以透過圖形化介面的方式管理資料庫,也可以下 SQL 指令
參考文件:認識phpMyAdmin
phpMyAdmin 安裝完成後通常需要將 libraries 資料夾中的 config.default.php (或config.sample.php) 更改檔名為 config.inc.php 後放入 phpMyAdmin 根目錄
一般預設帳號:root,密碼:空值 或 admin
參考文件:phpMyAdmin 安裝與設定
建立新的管理帳號
不要直接用root
1. 啟動伺服器,登入 phpMyAdmin
2. 選擇資料庫標籤
3. 輸入資料庫名稱(MySQL 不區分大小寫)
4. 選擇編碼校對(utf8_general_ci)
5. 點選建立資料庫按鈕
6. 成功後會看到左欄出現建立的資料庫名稱
1. 點選進入欲建立資料表的資料庫
2. 輸入資料表名稱和欄位數
3. 輸入欄位名稱、資料型態、資料長度、預設值、是否為主鍵、是否唯一、是否為索引、自動遞增等資料庫結構資訊
(資料型態分為:數值、日期/時間、字串三大類)
4. 確認後點選儲存按鈕
5. 完成後可以新增幾筆資料測試
請開啟 phpMyAdmin 更改 root 密碼,並新增新的管理帳戶。重新登入後試著建立一資料庫,並將練習一所製作的資料表建立於資料庫中
參考文件:如何在 MySQL 刪除關聯性的資料呢?
1. 建立各 table 的主鍵(primary key)
2. 在欲關聯的 table 建立相同名稱欄位並將關聯者欄位設為 index,形成關聯的外來鍵(foreign key)
order_id
order_name
user_id
user_id
user_name
user_email
主鍵
主鍵
外來鍵 index
請試著在建立好的資料表中完成新增、修改、刪除的操作
SQL (Structured Query Language) 全稱為結構化查詢語言
屬於 ANSI 標準的資料庫語言,MySQL、SQL Server、Oracle等都支援
可以對資料庫進行新增、查詢、刪除、修改等操作
在 PHP 程式中可以下達 SQL 指令來存取資料庫
2. 資料操作語言(Data Manipulation Language)
資料表新增、查詢、修改、刪除等操作指令
3. 資料控制語言(Data Control Language)
資料庫安全設定和權限管理等相關指令
1. 資料定義語言(Data Definition Language)
建立資料表、索引和檢視表(Views)等,主要定義資料表欄位
CREATE DATABASE IF NOT EXISTS 資料庫名稱;
CREATE 指令可以新增一資料庫:
CREATE TABLE "表格名稱"
("欄位 1" "欄位 1 的資料型態",
"欄位 2" "欄位 2 的資料型態",
... );
CREATE 指令可以新增一筆記錄到資料表中:
(資料型態分為:數值、日期/時間、字串三大類)
透過PHP 中的 mysqli_query() 函數執行常用 SQL 指令:
1. INSERT
在資料表中插入一筆新記錄
2. SELECT
查詢資料表的資料,可以設定條件篩選
3. UPDATE
更新資料表已存資料
4. DELETE
刪除資料記錄
參考文件:SQL語法教學
INSERT 指令可以新增一筆記錄到資料表中:
INSERT INTO table (column1, column2, ...) VALUES ('$value_1', '$value_2', ...);
以上指令代表將 $value_1 等變數值,依序插入到資料表對應欄位中。
注意:插入順序不用依照資料表建立欄位順序
VALUES() 中變數和字串、時間需要用 ' 或 " 括起
請練習使用 SQL 語法在 phpMyAdmin 建立一資料庫和資料表(至少含 user_id, user_name, user_email, create_time),並新增一筆資料到已建立好的資料表中
INSERT INTO `member` (user_name, user_email, create_time)
VALUES ('KD', 'kdchang@gmail.com', date('Y-m-d H:i:s'));
使用 SELECT 指令可以查詢資料表中的資料,基本用法為:
SELECT column_1, column_2
FROM `資料表名稱` WHERE 篩選條件;
// 從資料表中依據篩選條件取出 column_1, column_2 資料
// 顯示資料表部分欄位
SELECT user_id, user_name, user_email FROM `member`;
// 顯示資料表所有欄位
SELECT * FROM `students`;
// 欄位值不重複
SELECT DISTINCT user_name FROM `member`;
// 欄位別名
SELECT user_name, AS name user_email AS email FROM `member`;
// WHERE 條件子句,條件為字串
SELECT * FROM `member` WHERE user_name='KD';
// 包含字串,使用 LIKE 運算子 match,% 代表任何子字串
SELECT * FROM `member` WHERE user_email LIKE '%gmail%';
// - 代表任何字元
SELECT * FROM `member` WHERE user_birthday LIKE '1991092-';
// 條件是數值
SELECT * FROM `member` WHERE user_age <= 20;
// 條件是日期/時間
SELECT * FROM `member` WHERE user_birthday='1990-01-01';
日期/時間資料型態格式:
1. DATA:日期格式,格式為 YYYY-MM-DD
2. TIME:時間格式,格式為 HH:MM:SS.nn
3. TIMESTAMP:日期時間格式
格式為 YYYY-MM-DD HH:MM:SS.nn
// AND 且運算子
SELECT * FROM `member` WHERE user_name LIKE '張%'
AND user_age < 20;
// OR 或運算子
SELECT * FROM `member` WHERE user_name LIKE '張%'
OR user_age < 20;
// 複雜的 WHERE 條件子句
SELECT * FROM `member` WHERE user_name LIKE '張%'
AND user_age < 20
OR user_birthday='19990101';
// 擁有括號的 WHERE 條件子句
SELECT * FROM `member` WHERE user_name LIKE '張%'
AND (user_age < 20 OR user_birthday='19990101');
如果條件子句不只一個條件,我們可以使用邏輯運算子 AND 和 OR 來連接多個條件
// 由小到大排序
SELECT * FROM `member` WHERE user_age > 20 ORDER BY user_name BY ASC
// 由大到小排序
SELECT * FROM `member` WHERE user_age > 20 ORDER BY user_name BY DESC
// BETWEEN / AND 資料範圍運算子
SELECT * FROM `member` WHERE user_birthday BETWEEN '1991-01-01' AND '1992-01-01';
// IN 和 NOT 運算子
SELECT * FROM `member` WHERE user_id IN (1, 4, 5);
SELECT * FROM `member` WHERE user_id NOT IN (1, 4, 5);
// LIMIT 限制記錄數子句(index 0是第一筆)
SELECT * FROM `member` WHERE user_id limit 3; // 取符合條件三筆
SELECT * FROM `member` WHERE user_id limit 1, 3; // 從 index 1 開始取符合三筆
使用聚合函數可以進行資料表欄位的比數、平均、最大和最小加總的運算,提供 SQL 查詢結果的進一步資訊
1. COUNT(Column) 計算記錄筆數, * 參數統計所有記錄數
2. AVG(Column) 計算欄位平均值
3. MAX(Column) 取得記錄欄位的最大值
4. MIN(Column) 取得記錄欄位的最小值
5. SUM(Column) 取得記錄欄位的總和
SELECT COUNT(*) FROM `member`;
SELECT AVG(user_age) FROM `member`;
SELECT MAX(user_age) FROM `member`;
SELECT MIN(user_age) FROM `member`;
SELECT SUM(user_age) FROM `member`;
若在 WHERE 子句使用另一個 SELECT 指令查詢其他資料表的紀錄稱為子查詢(subquery),其目的通常是為了取得所需條件值
SELECT COUNT(*) FROM order_info
WHERE user_id =
(SELECT user_id FROM `member` WHERE user_name="KD.Chang");
SQL 使用 JOIN 指令可以進行合併查詢,可以將關聯式資料庫分割的資料表合併成位分割前狀態,可分為:INNER JOIN 和 OUTER JOIN 指令
// 從 member 和 order_info 中有相同的 order_id 記錄,
// 取出 user_id, user_name, order_price 欄位
// 亦即查詢所有使用者購買每筆訂單的總價格
SELECT member.user_id, member.user_name, order_info.order_price
FROM `member` INNER JOIN `order_info`
ON member.user_id = order.user_id;
INNER JOIN 可以取回 2 個資料表都存在的紀錄
SELECT member.user_id, member.user_name, meal_info.*
FROM `meal_info` INNER JOIN (`member`
INNER JOIN `order_info` ON order_info.user_id=member.user_id)
ON member.user_id = order_info.user_id;
查詢三個資料表,將原來 FROM 子句後的 INNER JOIN 使用括號括起當成查詢結果的資料表即可以進一步查詢 meal_info 資料表的所有欄位
OUTER JOIN 指令可以取回任何一資料的所有記錄,不論是否兩資料表都存在,可以分為:LEFT JOIN和 RIGHT JOIN
// 從 member 和 order_info 中有相同的 order_id 記錄,
// 取出 user_id, user_name, order_price 欄位
SELECT member.user_id, member.user_name, order_info.order_price
FROM `member` INNER JOIN `order_info`
ON member.user_id = order.user_id;
LEFT JOIN 可以取回左邊資料表的所有記錄
SELECT member.*, order_info.order_id FROM `member`
LEFT JOIN order_info ON member.user_id=order_info.user_id;
RIGHT JOIN 可以取回右邊資料表的所有記錄
SELECT member.user_id, order_info.* FROM `order_info`
RIGHT JOIN `member` ON order_info.user_id=member.user_id;
UPDATE 指令是將資料表內符合條件的紀錄,更新指定欄位值,其語法如下:
// 一定要有 WHERE 條件,不然所有資料表中記錄欄位都會更新
UPDATE 資料表名 SET column='value' WHERE 符合條件;
// 更改 user_id 為 1 的使用者名稱和 email 紀錄
UPDATE FROM `member`
SET user_name='XD.Chang', user_email='xd@gmail.com'
WHERE user_id='1';
DELETE 指令是將資料表內符合條件的紀錄刪除掉,其語法如下:
// 一定要有 WHERE 條件,如果沒有資料表內所有記錄都會被刪除
// 條件內可以使用 =, <>, >, <=, >=, AND, OR 等運算子
DELETE FROM 資料表名 WHERE 符合條件;
// 刪除使用者名稱為 KD 的資料欄位
DELETE FROM `member` WHERE user_name='KD';
1. 點選進入欲建立資料表的資料庫
2. 輸入資料表名稱和欄位數
3. 輸入欄位名稱、資料型態、資料長度、預設值、是否為主鍵、是否唯一、是否為索引、自動遞增等資料庫結構資訊
4. 確認後點選儲存按鈕
5. 完成後可以使用 SQL 新增幾筆資料並讀取資料
6. 試著使用 SQL 更新、刪除已建立的資料
過去一段時間 PHP 主要使用 MySQL 擴充程式當做 PHP 與 MySQL 資料庫的連結橋樑。後來因為安全性和相容性等問題目前主流連接資料庫方法有:PDO(PHP Data Obeject) 和 MySQLi(MySQL Improved Extension)
// PDO
$pdo = new PDO("mysql:host=localhost;dbname=database", 'username', 'password');
// mysqli, procedural way
$mysqli = mysqli_connect('localhost','username','password','database');
// mysqli, object oriented way
$mysqli = new mysqli('localhost','username','password','database');
一般使用 PDO 連接資料方法為物件導向(object oriented)的方式,而 MySQLi 則有程序性(procedural)方法和物件導向
參考文件:PHP Data Objects
// PDO
$pdo = new PDO("mysql:host=localhost;dbname=database", 'username', 'password');
$query = "SELECT * FROM `member`;";
$result = $pdo->query($query)->fetchAll();
print_r($result);
連接上資料庫後即可進行 SQL 查詢,以下先用基本方法示範
// mysqli, procedural way
$query = "SELECT * FROM `member`;";
$mysqli = mysqli_connect('localhost','username','password','database');
$result = mysqli_query($link, $query);
while ($row = mysqli_fetch_assoc($result)) {
echo $row['user_email'] . '<br>';
}
mysqli_free_result($result);
mysqli_close($mysqli);
// mysqli, object oriented way
$query = "SELECT * FROM `member`;";
$mysqli = new mysqli('localhost','username','password','database');
if ($result = $mysqli->query($query)) {
printf("Select returned %d rows.\n", $result->num_rows);
print_r($result->fetch_array());
/* free result set */
$result->close();
}
$result = mysqli_query($link, $query);
// 開啟指定資料庫
mysqli_select_db($link, $dbname);
// 取得 MySQL伺服器資料
mysqli_get_host_info($link);
// 設定編碼
mysqli_query($link, 'SET NAMES utf8');
// 取的物件結果和欄位值(結合陣列) mysqli_fetch_object (物件)
while($row = mysqli_fetch_assoc($result)) {
echo $row['user_name'] . '<br>';
}
// 釋放物件佔用記憶體
mysqli_free_result($result);
$result = mysqli_query($link, $query);
// 取得欄位和記錄數
mysqli_num_fields($result);
mysqli_num_rows($result);
// 取得欄位資訊
mysqli_fetch_field($result);
// 讀取單筆記錄的索引陣列
mysqli_fetch_row($result)
// 移動指標
mysqli_data_seek($result, 2);
// 將記錄存入陣列(MYSQLI_NUM, MYSQLI_ASSOC, MYSQLI_BOTH)
mysqli_fetch_array($result, MYSQLI_NUM);
// 受前一操作影響筆數
mysqli_affected_rows($link);
// MySQL 錯誤處理
mysqli_errno($link);
mysqli_error($link);
請使用 PDO 和 MySQLi 方法成功連接到資料庫,並搭配使用 SQL 語法進行新增、讀取、修改、刪除等資料庫資料操作
http://code.tutsplus.com/tutorials/
<?php
var_dump(PDO::getAvailableDrivers());
?>
$params = array(':username' => 'test',
':email' => $user_email,
':last_login_time' => time() - 3600);
$pdo->prepare('
SELECT * FROM `member`
WHERE username = :username
AND email = :email
AND last_login_time > :last_login_time');
$pdo->execute($params);
$user_email = 'kdchang@gmail.com';
$query = $mysqli->prepare('
SELECT * FROM `member`
WHERE username = ?
AND user_email = ?
AND last_login > ?');
$query->bind_param('kd', 'test', $user_email, time() - 3600);
$query->execute();
MySQLi 不能使用 Named Parameters
class User {
public $id;
public $first_name;
public $last_name;
public function info()
{
return '#'.$this->id.': '.$this->first_name.' '.$this->last_name;
}
}
PDO 和 MySQLI 都可以藉由物件屬性對應到資料表的欄位來操作資料庫資料
$query = "SELECT id, first_name, last_name FROM users";
// PDO
$result = $pdo->query($query);
$result->setFetchMode(PDO::FETCH_CLASS, 'User');
while ($user = $result->fetch()) {
echo $user->info()."\n";
}
使用 PDO 進行 Object Mapping
$query = "SELECT id, first_name, last_name FROM users";
// MySQLI, procedural way
if ($result = mysqli_query($mysqli, $query)) {
while ($user = mysqli_fetch_object($result, 'User')) {
echo $user->info()."\n";
}
}
// MySQLi, object oriented way
if ($result = $mysqli->query($query)) {
while ($user = $result->fetch_object('User')) {
echo $user->info()."\n";
}
}
使用 MySQLi 進行 Object Mapping
$_GET['username'] = "'; DELETE FROM users; /*"
// PDO, "manual" escaping
$username = PDO::quote($_GET['username']);
$pdo->query("SELECT * FROM users WHERE username = $username");
// mysqli, "manual" escaping
$username = mysqli_real_escape_string($_GET['username']);
$mysqli->query("SELECT * FROM users WHERE username = '$username'");
為了維護系統的安全我們通常假設使用者輸入的資料為不安全的,務必事先過濾以免造成危險(過濾輸入,轉義輸出)。以下即為典型的 SQL Injection 的攻擊方式:
// PDO, prepared statement
$pdo->prepare('SELECT * FROM users WHERE username = :username');
$pdo->execute(array(':username' => $_GET['username']));
// mysqli, prepared statements
$query = $mysqli->prepare('SELECT * FROM users WHERE username = ?');
$query->bind_param('s', $_GET['username']);
$query->execute();
使用預處理語句(prepared statement) 可以將變數和 SQL 語法分離,並使用佔位符(placeholder)將傳到此處的值事先轉義(escape)
預存程式,類似函數,但儲存在資料庫層別。SQL敘述的特點是一次只能執行一件工作,視需要在資料庫中建立許多不同用途的 Stored procedure,一次執行數個SQL 敘述,使用「call」來呼叫
delimiter $$
CREATE PROCEDURE get_order_info();
BEGIN
SELECT user_name
FROM `member`
ORDER BY created_time DESC;
END $$
delimiter;
$db_conn = new PDO('mysql:host=localhost;dbname:launchbox',
'myadmin', 'secret');
$stmt = $db_conn->query('call get_recipes()');
$result = $stmt->fetchAll();
使用 call 呼叫 stored procedures
效能表現:
MySQL > MySQLi > PDO
除非非常重視效能表現,事實上三者差異不大(MySQLi 和 PDO 約差 2.5%,non-prepared statements 約 6.5%),但 PDO 相較之下更安全以及應用範圍更廣
請搭配 PDO 或 MySQLi 製作一簡單的留言板系統,包含登入、發文、修文、刪除
當查詢結果很多筆時,我們會希望結果使用分頁的方式顯示,並使用超連結進行頁面切換
// http://localhost/PHP/Ch06/pagination.php?pages=1 每頁 3 筆資料
// 計算分頁數
$per_page_records = 3;
if (isset($_GET['pages'])) {
$pages = $_GET['pages'];
} else {
$pages = 1;
}
$total_fields = mysqli_num_fields($result);
$total_records = mysqli_num_rows($result);
$total_pages = ceil($total_records / $per_page_records);
$offset = ($pages - 1) * $per_page_records;
mysqli_data_seek($result, $offset);
// 顯示分頁記錄
$j = 1;
while ($rows = mysqli_fetch_array($result, MYSQLI_NUM) && $j <= $per_page_records) {
for ($i = 0; $i <= $total_fields - 1; $i++) {
echo $rows[$i]
}
$j++;
}
// 顯示上一頁和下一頁
if($pages > 1) {
echo "<a href='paganation.php?pages=" . ($pages - 1) . "'>上一頁</a>";
}
for($i = 1; $i <= $total_pages; $i++) {
if($i != $pages)
echo "<a href='paganation.php?pages='" . ($i) . ">" . $i ."</a>";
else
echo $i;
}
if($pages < $total_pages) {
echo "<a href='paganation.php?pages='" . ($pages + 1) . ">下一頁</a>";
}
?>
請運用之前講授的方法製作分頁效果
使用 PHP 和資料庫製作簡易訂便當系統(填寫訂購表單、後台管理)