您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 基礎操作語句大全與 PHP 連接實戰

編輯：MySQL 綜合教學

MySQL 是 Web 開發中最常用的關係型數據庫。掌握核心 SQL 語句與 PHP 連接操作，是後端開發的基本功。本文系統梳理查詢、插入、更新、刪除等核心操作，並提供 PHP 連接、查詢、事務封裝的完整示例。

核心 SQL 語句速查

1. 查詢 (SELECT)

-- 基礎查詢
SELECT * FROM `test` WHERE 1;
SELECT `id`, `uid` FROM `test` WHERE `id` = 2;

-- 條件運算符
SELECT * FROM `test` WHERE `id` IN (1,2,4);
SELECT * FROM `test` WHERE `uid` LIKE '%王%';
SELECT * FROM `test` WHERE `id` BETWEEN 1 AND 3;
SELECT * FROM `test` WHERE `id` = 2 AND `remark` = '學生';

-- 排序、分組、分頁
SELECT * FROM `test` GROUP BY `remark`;
SELECT * FROM `test` ORDER BY `regdate` ASC, `id` DESC;
SELECT * FROM `test` LIMIT 0, 10;  -- 分頁核心

-- 聚合函數
SELECT COUNT(*) FROM `test`;
SELECT MAX(`id`), MIN(`regdate`) FROM `test`;

2. 插入 (INSERT)

INSERT INTO `test` (`id`, `uid`, `regdate`, `remark`) 
VALUES (NULL, '小方', NOW(), '學生');  -- 自增 ID 傳 NULL

3. 更新 (UPDATE)

UPDATE `test` SET `uid` = '小方' WHERE `id` = 4 LIMIT 1;

4. 刪除 (DELETE)

DELETE FROM `test` WHERE `id` = 3 LIMIT 1;  -- 建議加 LIMIT 防誤刪

PHP + MySQL 連接與操作

1. 建立連接

<?php
// 方式 A：基本連接
$conn = @mysql_connect('localhost', 'root', 'root');
if ($conn) echo '連接成功';

// 方式 B：帶錯誤處理
$conn = @mysql_connect('localhost', 'root', 'root') or die('連接失敗: ' . mysql_error());

// 選擇數據庫
mysql_select_db('newdb', $conn) or die('數據庫選擇失敗');

// 設置字符集（關鍵，防亂碼）
mysql_query("SET NAMES 'utf8'", $conn);
?>

2. 執行 SQL 與結果處理

<?php
$sql = "SELECT * FROM `test`";
$result = mysql_query($sql, $conn) or die(mysql_error());

// mysql_fetch_row：返回數字索引數組
while ($row = mysql_fetch_row($result)) {
    echo $row[0] . ' - ' . $row[1] . '<br>';
}

// mysql_fetch_array：返回雙索引數組（數字+關聯）
while ($row = mysql_fetch_array($result)) {
    echo $row['id'] . ' - ' . $row['uid'] . '<br>';
}

// 獲取影響行數 / 插入 ID
$count = mysql_num_rows($result);
$lastId = mysql_insert_id($conn);
mysql_close($conn);
?>

3. 常用函數速查

mysql_num_rows()：查詢結果行數
mysql_insert_id()：最後插入的自增 ID
mysql_error()：錯誤信息
mysql_close()：關閉連接

面向對象封裝：MySQL 操作類

將常用操作封裝為類，提升代碼復用性與維護性：

<?php
class MySQL_Operating {
    private $host, $user, $pass, $db, $charset, $link;

    public function __construct($host, $user, $pass, $db, $charset = 'utf8') {
        $this->host = $host;
        $this->user = $user;
        $this->pass = $pass;
        $this->db = $db;
        $this->charset = $charset;
        $this->connect();
    }

    private function connect() {
        $this->link = mysql_connect($this->host, $this->user, $this->pass) or die(mysql_error());
        mysql_select_db($this->db, $this->link) or die('數據庫選擇失敗');
        mysql_query("SET NAMES '{$this->charset}'", $this->link);
    }

    public function query($sql) {
        return mysql_query($sql, $this->link);
    }

    public function fetchAll($sql) {
        $result = $this->query($sql);
        $data = [];
        while ($row = mysql_fetch_assoc($result)) {
            $data[] = $row;
        }
        return $data;
    }

    public function insert($table, $data) {
        $fields = implode(',', array_map(fn($k) => "`$k`", array_keys($data)));
        $values = implode(',', array_map(fn($v) => "'" . mysql_real_escape_string($v, $this->link) . "'", $data));
        $sql = "INSERT INTO `$table` ($fields) VALUES ($values)";
        $this->query($sql);
        return mysql_insert_id($this->link);
    }

    public function update($table, $data, $where) {
        $sets = implode(',', array_map(fn($k, $v) => "`$k` = '" . mysql_real_escape_string($v, $this->link) . "'", array_keys($data), $data));
        $sql = "UPDATE `$table` SET $sets WHERE $where";
        return $this->query($sql);
    }

    public function delete($table, $where) {
        $sql = "DELETE FROM `$table` WHERE $where";
        return $this->query($sql);
    }

    public function close() {
        mysql_close($this->link);
    }
}

// 使用示例
$db = new MySQL_Operating('localhost', 'root', 'root', 'test_db', 'utf8');
$db->insert('users', ['name' => '張三', 'email' => 'zhangsan@example.com']);
$users = $db->fetchAll("SELECT * FROM users WHERE name LIKE '%張%'");
$db->close();
?>