PHP 資料庫互動與 SQL 基礎

PHP 網站程式設計與資料庫基礎班 DAY 6

講師:張凱迪(KD.Chang)

Copyright © 2014 All rights reserved

Outline

資料庫基礎

MySQL 資料庫基本使用

SQL 結構化查詢語言基礎

PHP 與 MySQL 資料庫互動

資料庫基礎

為何要學資料庫?

一般學習 PHP 程式主要是希望建立 Web-Based 系統和應用程式(學校的課程網站、訂便當系統、人事管理系統等),而大部分的系統多半需要和資料庫互動,提供系統存取資料的能力,所以如何讓 PHP 和資料庫互動就是非常重要的一個主題

在這邊我們不談

EM-Model、正規化、交易管理等議題,

有興趣的朋友可以參考學校資料庫管理的課程

我們的重點會放在:

PHP 如何和資料庫互動

資料庫基礎

資料庫 (Database) 是一種資料儲存的單位,亦即一群相關資料的集合 (A collection of related data

 

1. 將相關的資料以系統化、有效率的方式儲存在一起

2. 一個資料庫通常針對特定需求設計

3. 透過資料庫管理系統 (DBMS) 進行管理

4. 資料庫系統 = 資料庫管理系統 + 資料庫

參考文件:資料庫系統概論

資料庫 vs. 檔案系統

由於比起檔案系統,資料庫具備以下優點,所以適合用於資料處理和管理:

 

1. 避免資料重覆

2. 資料一致性

3. 資料整合與共享性較高

4. 資料較為獨立及安全

 

參考文件:資料庫學習實務

RDB vs. NoSQL 

目前市面上大部分的資料庫管理系統為關聯式資料庫(Relational Database),例如:MySQL、PostgreSQL、SQL Server、Oracle等。但由於為了處理 Big Data 等非結構化資料問題,目前也有許多資料庫管理系統是使用 NoSQL (Not Only SQL,同常具備不使用SQL作為查詢語言,一般不用固定 Schema,常會避免使用SQL的JOIN操作等特性) 的方式(例如:MongoDB、CouchDB、Cassandra)

我們這堂課只討論 RDB  !

http://images.40era.com/201207/site/mysql.gif

關聯式資料庫

http://www.gcsecomputing.org.uk/

主鍵 / 外來鍵

http://allenbrowne.com/

PHP 與資料庫運作

http://www.hosting.vt.edu/tutorials/phpmysql/figure1.png

練習一

請拿出一張白紙,試著畫出學校課程網站資料庫管理的資料表關聯圖(至少含 3 個 table,教授、課程、學生。每個 table 至少要有四個欄位並寫上可能的資料型別和長度)

MySQL 資料庫基本使用

MySQL 資料庫系統基礎

MySQL 是一套 Open Source 關聯式資料庫

 

由 C/C++語言所開發,常用於和 PHP 搭配,支援多種平台

 

最初由 MySQL AB 公司負責,現已被 Oracle 併購

 

提供多執行緒、多使用者並使用標準(ANSI) SQL 

使用 phpMyAdmin

phpMyAdmin 是一套用PHP寫出來的MySQL管理程式,可以透過圖形化介面的方式管理資料庫,也可以下 SQL 指令

參考文件:認識phpMyAdmin

設定 config.inc.php

phpMyAdmin 安裝完成後通常需要將 libraries 資料夾中的 config.default.php (或config.sample.php) 更改檔名為 config.inc.php 後放入 phpMyAdmin 根目錄

設定權限和帳號密碼

一般預設帳號:root,密碼:空值 或 admin

設定權限和帳號密碼

建立新的管理帳號

不要直接用root

資料庫建立與設計

資料庫建立與設計

1. 啟動伺服器,登入 phpMyAdmin  

2. 選擇資料庫標籤 

3. 輸入資料庫名稱(MySQL 不區分大小寫) 

4. 選擇編碼校對(utf8_general_ci)

5. 點選建立資料庫按鈕

6. 成功後會看到左欄出現建立的資料庫名稱

建立資料表

建立資料表

1. 點選進入欲建立資料表的資料庫

2. 輸入資料表名稱和欄位數

3. 輸入欄位名稱、資料型態、資料長度、預設值、是否為主鍵、是否唯一、是否為索引、自動遞增等資料庫結構資訊

(資料型態分為:數值、日期/時間、字串三大類)

4. 確認後點選儲存按鈕

5. 完成後可以新增幾筆資料測試

建立資料表結構

新增資料

練習二

請開啟 phpMyAdmin 更改 root 密碼,並新增新的管理帳戶。重新登入後試著建立一資料庫,並將練習一所製作的資料表建立於資料庫中

資料讀取

資料修改

刪除資料

點選操作等於下 SQL 指令

直接下 SQL 指令也行

建立關聯性

1. 建立各 table 的主鍵(primary key)

2. 在欲關聯的 table 建立相同名稱欄位並將關聯者欄位設為 index,形成關聯的外來鍵(foreign key)

order_id

order_name

user_id

user_id

user_name

user_email

主鍵

主鍵

外來鍵 index

匯入資料庫

匯出資料庫

重設 root 密碼

練習三

請試著在建立好的資料表中完成新增、修改、刪除的操作

SQL 結構化查詢語言基礎

SQL 語言基礎

SQL (Structured Query Language) 全稱為結構化查詢語言

屬於 ANSI 標準的資料庫語言,MySQL、SQL Server、Oracle等都支援

可以對資料庫進行新增、查詢、刪除、修改等操作

在 PHP 程式中可以下達 SQL 指令來存取資料庫

SQL 指令種類

2. 資料操作語言(Data Manipulation Language)

資料表新增、查詢、修改、刪除等操作指令

3. 資料控制語言(Data Control Language)

資料庫安全設定和權限管理等相關指令

1. 資料定義語言(Data Definition Language)

建立資料表、索引和檢視表(Views)等,主要定義資料表欄位

SQL 建立 : CREATE 

CREATE DATABASE IF NOT EXISTS 資料庫名稱;

CREATE 指令可以新增一資料庫:

SQL 建立 : CREATE 

CREATE TABLE "表格名稱"
("欄位 1" "欄位 1 的資料型態",
"欄位 2" "欄位 2 的資料型態",
... );

CREATE 指令可以新增一筆記錄到資料表中:

(資料型態分為:數值、日期/時間、字串三大類)

常用 SQL 操作指令

透過PHP 中的 mysqli_query() 函數執行常用 SQL 指令:

1. INSERT

在資料表中插入一筆新記錄

2. SELECT

查詢資料表的資料,可以設定條件篩選

3. UPDATE

更新資料表已存資料

4. DELETE

刪除資料記錄

 

參考文件:SQL語法教學

SQL 新增 : INSERT 

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 指令可以查詢資料表中的資料,基本用法為:

SQL 查詢 : SELECT 

SELECT column_1, column_2 
    FROM `資料表名稱` WHERE 篩選條件;

// 從資料表中依據篩選條件取出 column_1, column_2 資料

SQL 條件查詢 

// 顯示資料表部分欄位
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-';

WHERE 條件查詢 

WHERE 條件查詢 

// 條件是數值
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 來連接多個條件

WHERE 條件查詢 

// 由小到大排序
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 聚合函數

使用聚合函數可以進行資料表欄位的比數、平均、最大和最小加總的運算,提供 SQL 查詢結果的進一步資訊

1. COUNT(Column) 計算記錄筆數, * 參數統計所有記錄數

 

2. AVG(Column) 計算欄位平均值

 

3. MAX(Column) 取得記錄欄位的最大值

 

4. MIN(Column) 取得記錄欄位的最小值

 

5. SUM(Column) 取得記錄欄位的總和

SQL 聚合函數

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`;

SQL 子查詢

若在 WHERE 子句使用另一個 SELECT 指令查詢其他資料表的紀錄稱為子查詢(subquery),其目的通常是為了取得所需條件值


SELECT COUNT(*) FROM order_info
WHERE user_id = 
(SELECT user_id FROM `member` WHERE user_name="KD.Chang");

SQL 合併查詢

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 內部合併查詢

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;

INNER JOIN 內部合併查詢

查詢三個資料表,將原來 FROM 子句後的 INNER JOIN 使用括號括起當成查詢結果的資料表即可以進一步查詢 meal_info 資料表的所有欄位

OUTER JOIN 外部合併查詢

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 可以取回左邊資料表的所有記錄

LEFT JOIN

SELECT member.*, order_info.order_id FROM `member`
LEFT JOIN order_info ON member.user_id=order_info.user_id;

RIGHT JOIN

RIGHT JOIN 可以取回右邊資料表的所有記錄

SELECT member.user_id, order_info.* FROM `order_info`
RIGHT JOIN `member` ON order_info.user_id=member.user_id; 

SQL 修改 : UPDATE 

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'; 

SQL 刪除 : DELETE 

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 擴充程式當做 PHP 與 MySQL 資料庫的連結橋樑。後來因為安全性和相容性等問題目前主流連接資料庫方法有:PDO(PHP Data Obeject) 和 MySQLi(MySQL Improved Extension)

使用 PDO / MySQLi 連接資料庫

// 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 

執行 SQL 查詢(PDO)  

// 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();
}

執行 SQL 查詢(MySQLi)  

執行 SQL 查詢(MySQLi)  

$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);

執行 SQL 查詢(MySQLi)  

練習六

請使用 PDO 和 MySQLi 方法成功連接到資料庫,並搭配使用 SQL 語法進行新增、讀取、修改、刪除等資料庫資料操作 

PDO vs. MySQLi

Database Support

http://code.tutsplus.com/tutorials/

<?php
var_dump(PDO::getAvailableDrivers());
?>

Named Parameters(PDO)

$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);

Named Parameters(MySQLi)

$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

Object Mapping

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";
}

Object Mapping

使用 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";
    }
}

Object Mapping

使用 MySQLi 進行 Object Mapping

Security

$_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();

Security

使用預處理語句(prepared statement) 可以將變數和 SQL 語法分離,並使用佔位符(placeholder)將傳到此處的值事先轉義(escape)

參考文件:mysqli_stmt::bind_param

Stored procedures

預存程式,類似函數,但儲存在資料庫層別。SQL敘述的特點是一次只能執行一件工作,視需要在資料庫中建立許多不同用途的 Stored procedure,一次執行數個SQL 敘述,使用「call」來呼叫

delimiter $$
CREATE PROCEDURE get_order_info();
BEGIN
    SELECT user_name
    FROM `member`
    ORDER BY created_time DESC;
END $$
delimiter;  

Stored procedures

$db_conn = new PDO('mysql:host=localhost;dbname:launchbox', 
'myadmin', 'secret');
$stmt = $db_conn->query('call get_recipes()');
$result = $stmt->fetchAll();

使用 call 呼叫 stored procedures

Performance

效能表現:

MySQL > MySQLi > PDO

 

除非非常重視效能表現,事實上三者差異不大(MySQLi 和 PDO 約差 2.5%,non-prepared statements 約 6.5%),但 PDO 相較之下更安全以及應用範圍更廣

練習七

請搭配 PDO 或 MySQLi 製作一簡單的留言板系統,包含登入、發文、修文、刪除

製作 pagination 分頁效果

當查詢結果很多筆時,我們會希望結果使用分頁的方式顯示,並使用超連結進行頁面切換

// 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>";
}
?>

製作 pagination 分頁效果

練習八

請運用之前講授的方法製作分頁效果

練習九

使用 PHP 和資料庫製作簡易訂便當系統(填寫訂購表單、後台管理)

Q & A

PHP 資料庫互動與 SQL 基礎

By 張凱迪(KD Chang)

PHP 資料庫互動與 SQL 基礎

本課程分為 前端網頁程式設計基礎班 和 PHP 網站程式設計與資料庫基礎班 兩個班,希望藉由這次課程能讓對於網頁程式設計有興趣的同學可以有一個入門的管道,在上完課程後可以有足夠的自學能力能夠持續學習進修,進而將自己的點子付諸實踐。課程網站:http://kdchang.cc/web-programming-course/ Email:web.programming.course@gmail.com FB 粉絲專頁:https://fb.com/Web.Programming.Course

  • 2,259
Loading comments...

More from 張凱迪(KD Chang)