CakePHP 數據庫教程 · ZetCode 中文系列教程

# CakePHP 數據庫教程 > 原文： [https://zetcode.com/php/cakephpdatabase/](https://zetcode.com/php/cakephpdatabase/) CakePHP 數據庫教程展示了如何使用 CakePHP 的數據庫包在 PHP 中編程數據庫。 ## CakePHP 數據庫 CakePHP 數據庫是一個靈活而強大的數據庫抽象庫，它具有類似 PDO 的 API。該庫可幫助程序員構建查詢，防止 SQL 注入，檢查和更改模式，以及將調試和配置文件查詢發送到數據庫。 CakePHP 數據庫支持 MySQL/MariaDB，PostgresSQL，SQLite 和 Microsoft SQL Server。 ## MariaDB 數據庫在本教程中，我們將使用 MariaDB 數據庫 `countries_mariadb.sql` ```php DROP TABLE IF EXISTS countries; CREATE TABLE countries(id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), population INT); INSERT INTO countries(name, population) VALUES('China', 1382050000); INSERT INTO countries(name, population) VALUES('India', 1313210000); INSERT INTO countries(name, population) VALUES('USA', 324666000); INSERT INTO countries(name, population) VALUES('Indonesia', 260581000); INSERT INTO countries(name, population) VALUES('Brazil', 207221000); INSERT INTO countries(name, population) VALUES('Pakistan', 196626000); INSERT INTO countries(name, population) VALUES('Nigeria', 186988000); INSERT INTO countries(name, population) VALUES('Bangladesh', 162099000); INSERT INTO countries(name, population) VALUES('Nigeria', 186988000); INSERT INTO countries(name, population) VALUES('Russia', 146838000); INSERT INTO countries(name, population) VALUES('Japan', 126830000); ``` 這些 SQL 命令創建一個`countries`表。 ## 安裝 CakePHP 數據庫 ```php $ composer require cakephp/database ``` CakePHP 數據庫是使用上述命令安裝的。 ## CakePHP 執行 `execute()`執行查詢。 `version.php` ```php <?php require __DIR__ . '/vendor/autoload.php'; use Cake\Database\Connection; use Cake\Database\Driver\Mysql; $driver = new Mysql([ 'database' => 'mydb', 'username' => 'root', 'password' => 's$cret', ]); $conn = new Connection([ 'driver' => $driver, ]); $stm = $conn->execute('SELECT VERSION()'); $version = $stm->fetch()[0]; echo $version . "\n"; ``` 該示例打印 MariaDB 數據庫的版本。 ```php $driver = new Mysql([ 'database' => 'mydb', 'username' => 'root', 'password' => 's$cret', ]); ``` 創建一個新的 MySQL 驅動程序。我們提供數據庫名稱，用戶名和密碼。 ```php $conn = new Connection([ 'driver' => $driver, ]); ``` 創建一個連接對象。 ```php $ php version.php 10.1.36-MariaDB ``` 這是輸出。 ## CakePHP 獲取 `fetch()`方法在執行 SQL 語句后返回結果集的下一行。可以提取行以包含列作為名稱或位置。如果結果集中沒有剩余行，則返回`false`。 `fetch_rows.php` ```php <?php require __DIR__ . '/vendor/autoload.php'; use Cake\Database\Connection; use Cake\Database\Driver\Mysql; $driver = new Mysql([ 'database' => 'mydb', 'username' => 'root', 'password' => 's$cret', ]); $conn = new Connection([ 'driver' => $driver, ]); $stm = $conn->execute('SELECT * FROM countries'); while ($row = $stm->fetch('assoc')) { echo "{$row['id']} {$row['name']} {$row['population']}\n"; } ``` 該示例從`countries`表中提取所有行。 ```php $stm = $conn->execute('SELECT * FROM countries'); ``` 使用`execute()`執行查詢。 ```php while ($row = $stm->fetch('assoc')) { echo "{$row['id']} {$row['name']} {$row['population']}\n"; } ``` 我們在`while`循環中使用`fetch()`獲取所有行。 ```php $ php fetch_rows.php 1 China 1382050000 2 India 1313210000 3 USA 324666000 4 Indonesia 260581000 5 Brazil 207221000 6 Pakistan 196626000 7 Nigeria 186988000 8 Bangladesh 162099000 9 Nigeria 186988000 10 Russia 146838000 11 Japan 126830000 ``` This is the output. ## CakePHP 插入使用`insert()`將新行添加到數據庫。 `insert_row.php` ```php <?php require __DIR__ . '/vendor/autoload.php'; use Cake\Database\Connection; use Cake\Database\Driver\Mysql; $driver = new Mysql([ 'database' => 'mydb', 'username' => 'root', 'password' => 's$cret', ]); $conn = new Connection([ 'driver' => $driver, ]); $conn->insert('countries', ['name' => 'Ethiopia', 'population' => 102403196]); ``` 該示例在`countries`表中插入新行。 ## 參數化查詢參數化查詢可防止 SQL 注入攻擊。 `execute()`方法采用第二個參數中要綁定的值和第三個參數中的值的類型。 `parametrized.php` ```php <?php require __DIR__ . '/vendor/autoload.php'; use Cake\Database\Connection; use Cake\Database\Driver\Mysql; $driver = new Mysql([ 'database' => 'mydb', 'username' => 'root', 'password' => 's$cret', ]); $conn = new Connection([ 'driver' => $driver, ]); $stm = $conn->execute('SELECT * FROM countries WHERE id = :id', ['id' => 1], ['id' => 'integer']); $row = $stm->fetch(); echo "$row[0] $row[1] $row[2]\n"; ``` 該示例使用參數化查詢選擇特定行。 ```php $stm = $conn->execute('SELECT * FROM countries WHERE id = :id', ['id' => 1], ['id' => 'integer']); ``` `:id`是一個占位符，與第二個參數中指定的值綁定。要綁定的參數的類型在第三個參數中給出。 ```php $ php parametrized.php 1 China 1382050000 ``` This is the output. ## CakePHP 查詢構建器查詢構建器為創建和運行數據庫查詢提供了方便，流暢的接口。它是對運行 SQL 語句的低級詳細信息的抽象。它可以使程序員避免過程的復雜性。 `query_builder.php` ```php <?php require __DIR__ . '/vendor/autoload.php'; use Cake\Database\Connection; use Cake\Database\Driver\Mysql; $driver = new Mysql([ 'database' => 'mydb', 'username' => 'root', 'password' => 's$cret', ]); $conn = new Connection([ 'driver' => $driver, ]); $query = $conn->newQuery(); $query->select(['id', 'name']); $query->from('countries'); $query->where(['id >' => 4])->andWhere(['id <' => 10]); $rows = $query->execute(); foreach ($rows as $row) { echo "{$row[0]} {$row[1]}\n"; } ``` 該示例從`countries`表中檢索 ID 大于 4 且小于 10 的行。 ```php $query = $conn->newQuery(); ``` 使用`newQuery()`創建查詢構建器。 ```php $query->select(['id', 'name']); $query->from('countries'); $query->where(['id >' => 4])->andWhere(['id <' => 10]); ``` 該查詢是使用流利的方法調用構建的。 ```php $ php query_builder.php 5 Brazil 6 Pakistan 7 Nigeria 8 Bangladesh 9 Nigeria ``` This is the output. ## 計數行可以使用`func()`方法創建許多常用功能。 `count_rows.php` ```php <?php require __DIR__ . '/vendor/autoload.php'; use Cake\Database\Connection; use Cake\Database\Driver\Mysql; $driver = new Mysql([ 'database' => 'mydb', 'username' => 'root', 'password' => 's$cret', ]); $conn = new Connection([ 'driver' => $driver, ]); $query = $conn->newQuery(); $query->select(['count' => $query->func()->count('*')]); $query->from('countries'); $stm = $query->execute(); $n = $stm->fetch()[0]; echo "There are {$n} countries in the table\n"; ``` 該示例計算表中的行數。 ```php $ php count_rows.php There are 12 countries in the table ``` This is the output. ## CakePHP 的表達方式 `in()`表達式允許創建`IN` SQL 子句。 `in_expr.php` ```php <?php require __DIR__ . '/vendor/autoload.php'; use Cake\Database\Connection; use Cake\Database\Driver\Mysql; $driver = new Mysql([ 'database' => 'mydb', 'username' => 'root', 'password' => 's$cret', ]); $conn = new Connection([ 'driver' => $driver, ]); $query = $conn->newQuery(); $query->select(['id', 'name', 'population']); $query->from('countries'); $query->where(function ($exp) { return $exp ->in('id', [2, 4, 6, 8, 10]); }); $rows = $query->execute(); foreach ($rows as $row) { echo "{$row[0]} {$row[1]} {$row[2]}\n"; } ``` 該示例使用`in()`表達式從給定的 ID 數組中選擇行。 ```php $ php in_expr.php 2 India 1313210000 4 Indonesia 260581000 6 Pakistan 196626000 8 Bangladesh 162099000 10 Russia 146838000 ``` This is the output. 您可能也對以下相關教程感興趣： [PHP PDO 教程](/php/pdo/)， [Symfony 簡介](/symfony/intro/)， [PHP 教程](/lang/php/)或列出[所有 PHP](/all/#php) 教程。在本教程中，我們使用了 Doctrine QueryBuilder 和 PostgreSQL 數據庫。