MySQLi 快速指南

上一頁
下一頁

MySQLi - 簡介

MySQLi 是 MySQL API 在 PHP 中的擴充套件，從 PHP 5.0 開始引入。它也被稱為 MySQL 改進擴充套件。MySQLi 背後的動機是利用從 MySQL 4.1.3 開始提供的新的特性。它提供了比 MySQL 擴充套件更多的優勢。

  • MySQL 提供了一個面向物件的介面。它提供了面向物件和過程兩種方法來處理資料庫操作。

面向物件介面

<?php
   $mysqli = mysqli_connect("localhost", "user", "password", "database-name");

   $result = mysqli_query($mysqli, "SELECT 'Welcome to MySQLi' AS _msg FROM DUAL");
   $row = mysqli_fetch_assoc($result);
   echo $row['_msg'];
?>

過程方法

<?php
   $mysqli = new mysqli("localhost", "user", "password", "database-name");

   $result = $mysqli→query("SELECT 'Welcome to MySQLi' AS _msg FROM DUAL");
   $row = $result→fetch_assoc();
   echo $row['_msg'];
?>

  • MySQLi 支援預處理語句。

  • MySQLi 支援多條語句。

  • MySQLi 支援事務。

  • MySQLi 提供增強的除錯功能。

MySQLi - PHP 語法

MySQL 可以很好地與各種程式語言結合使用，例如 PERL、C、C++、JAVA 和 PHP。在這些語言中，PHP 由於其 Web 應用程式開發能力而成為最流行的一種。

本教程重點關注在 PHP 環境中使用 MySQL。如果您對使用 PERL 的 MySQL 感興趣，則可以考慮閱讀PERL 教程。

PHP 提供了各種函式來訪問 MySQL 資料庫並在 MySQL 資料庫中操作資料記錄。您需要像呼叫任何其他 PHP 函式一樣呼叫 PHP 函式。

用於 MySQL 的 PHP 函式具有以下通用格式：

mysqli function(value,value,...);

函式名稱的第二部分特定於該函式，通常是一個描述該函式作用的詞。以下是我們將在教程中使用的兩個函式：

$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
mysqli→query(,"SQL statement");

以下示例顯示了 PHP 呼叫任何 MySQL 函式的通用語法。

<html>
   <head>
      <title>PHP with MySQL</title>
   </head>
   <body>
      <?php
         $retval = mysqli - > <i>function</i>(value, [value,...]);
         if( !$retval ) {
            die ( "Error: a related error message" );
         }
         // Otherwise MySQL  or PHP Statements
      ?>
   </body>
</html>

從下一章開始，我們將看到所有重要的 MySQL 功能以及 PHP。

MySQLi - 連線

使用 MySQL 二進位制檔案連線 MySQL

您可以在命令提示符下使用mysql二進位制檔案建立 MySQL 資料庫連線。

示例

這是一個從命令提示符連線到 MySQL 伺服器的簡單示例：

[root@host]# mysql -u root -p
Enter password:******

這將為您提供 mysqli 命令提示符，您可以在其中執行任何 SQL 命令。以下是上述命令的結果：

以下程式碼塊顯示了上述程式碼的結果：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2854760 to server version: 5.0.9

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

在上面的示例中，我們使用root作為使用者，但您也可以使用任何其他使用者。任何使用者都可以執行允許該使用者執行的所有 SQL 操作。

您可以隨時使用mysql>提示符下的exit命令斷開與 MySQL 資料庫的連線。

mysql> exit
Bye

使用 PHP 指令碼連線 MySQL

PHP 提供了mysqli建構函式或mysqli_connect()函式來開啟資料庫連線。此函式接受六個引數，並在成功時返回 MySQL 連結識別符號，在失敗時返回 FALSE。

語法

$mysqli = new mysqli($host, $username, $passwd, $dbName, $port, $socket);

序號 引數及描述
1

$host

可選 - 執行資料庫伺服器的主機名。如果未指定，則預設值為localhost:3306。
2

$username

可選 - 訪問資料庫的使用者名稱。如果未指定，則預設為擁有伺服器程序的使用者名稱稱。
3

$passwd

可選 - 訪問資料庫的使用者密碼。如果未指定，則預設為空密碼。
4

$dbName

可選 - 要在其上執行查詢的資料庫名稱。
5

$port

可選 - 要嘗試連線到的 MySQL 伺服器的埠號。
6

$socket

可選 - 應使用的套接字或命名管道。

您可以隨時使用另一個 PHP 函式close()斷開與 MySQL 資料庫的連線。

語法

$mysqli→close();

示例

嘗試以下示例以連線到 MySQL 伺服器：

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Connecting MySQL Server</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache Web 伺服器上的 mysql_example.php 並驗證輸出。

Connected successfully.

MySQLi - 建立資料庫

使用 mysqladmin 建立資料庫

您需要特殊的許可權才能建立或刪除 MySQL 資料庫。因此，假設您可以訪問 root 使用者，則可以使用 mysql mysqladmin 二進位制檔案建立任何資料庫。

示例

這是一個建立名為TUTORIALS的資料庫的簡單示例：

[root@host]# mysqladmin -u root -p create TUTORIALS
Enter password:******

這將建立一個名為 TUTORIALS 的 MySQL 資料庫。

使用 PHP 指令碼建立資料庫

PHP 使用mysqli query()或mysql_query()函式來建立或刪除 MySQL 資料庫。此函式接受兩個引數，並在成功時返回 TRUE，在失敗時返回 FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必需 - 建立 MySQL 資料庫的 SQL 查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

嘗試以下示例以建立資料庫：

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head><title>Creating MySQL Database</title></head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');

         if ($mysqli→query("CREATE DATABASE TUTORIALS")) {
            printf("Database TUTORIALS created successfully.<br />");
         }
         if ($mysqli→errno) {
            printf("Could not create database: %s<br />", $mysqli→error);
         }

         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache Web 伺服器上的 mysql_example.php 並驗證輸出。

Connected successfully.
Database TUTORIALS created successfully.

MySQLi - 刪除資料庫

使用 mysqladmin 刪除資料庫

您需要特殊的許可權才能建立或刪除 MySQL 資料庫。因此，假設您可以訪問 root 使用者，則可以使用 mysql mysqladmin 二進位制檔案建立任何資料庫。

刪除任何資料庫時請小心，因為刪除資料庫後將丟失資料庫中的所有資料。

這是一個刪除上一章中建立的資料庫（TUTORIALS）的示例：

[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******

這將向您發出警告，並確認您是否真的要刪除此資料庫。

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'TUTORIALS' database [y/N] y
Database "TUTORIALS" dropped

使用 PHP 指令碼刪除資料庫

PHP 使用mysqli query()或mysql_query()函式來刪除 MySQL 資料庫。此函式接受兩個引數，並在成功時返回 TRUE，在失敗時返回 FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必需 - 刪除 MySQL 資料庫的 SQL 查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

嘗試以下示例以刪除資料庫：

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head><title>Dropping MySQL Database</title></head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');

         if ($mysqli->query("Drop DATABASE TUTORIALS")) {
            printf("Database TUTORIALS dropped successfully.<br />");
         }
         if ($mysqli->errno) {
            printf("Could not drop database: %s<br />", $mysqli->error);
         }
         $mysqli->close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache Web 伺服器上的 mysql_example.php 並驗證輸出。

Connected successfully.
Database TUTORIALS dropped successfully.

MySQLi - 選擇資料庫

一旦連線到 MySQL 伺服器，就需要選擇一個數據庫來使用。這是因為 MySQL 伺服器上可能存在多個數據庫。

從命令提示符選擇 MySQL 資料庫

從 mysql> 提示符選擇資料庫非常簡單。您可以使用 SQL 命令use來選擇資料庫。

示例

這是一個選擇名為TUTORIALS的資料庫的示例：

[root@host]# mysql -u root -p
Enter password:******
mysql> use TUTORIALS;
Database changed
mysql>

現在，您已選擇 TUTORIALS 資料庫，所有後續操作都將在 TUTORIALS 資料庫上執行。

注意 - 所有資料庫名稱、表名稱、表字段名稱都區分大小寫。因此，在發出任何 SQL 命令時，您必須使用正確的名稱。

使用 PHP 指令碼選擇 MySQL 資料庫

PHP 使用mysqli_select_db函式來選擇要對其執行查詢的資料庫。此函式接受兩個引數，並在成功時返回 TRUE，在失敗時返回 FALSE。

語法

mysqli_select_db ( mysqli $link , string $dbname ) : bool

序號 引數及描述
1

$link

必需 - 由 mysqli_connect() 或 mysqli_init() 返回的連結識別符號。
2

$dbname

必需 - 要連線的資料庫名稱。

示例

嘗試以下示例以選擇資料庫：

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Selecting MySQL Database</title>
   </head>
   <body>
   <?php
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'root@123';
      $conn = mysqli_connect($dbhost, $dbuser, $dbpass);

      if(! $conn ) {
         die('Could not connect: ' . mysqli_error($conn));
      }
      echo 'Connected successfully<br />';
      
      $retval = mysqli_select_db( $conn, 'TUTORIALS' );

      if(! $retval ) {
         die('Could not select database: ' . mysqli_error($conn));
      }
      echo "Database TUTORIALS selected successfully\n";
      mysqli_close($conn);
   ?>
   </body>
</html>

輸出

訪問部署在 apache Web 伺服器上的 mysql_example.php 並驗證輸出。

Database TUTORIALS selected successfully

MySQLi - 建立表

首先，建立表命令需要以下詳細資訊：

  • 表的名稱
  • 欄位的名稱
  • 每個欄位的定義

語法

這是一個建立 MySQL 表的通用 SQL 語法：

CREATE TABLE table_name (column_name column_type);

現在，我們將在TUTORIALS資料庫中建立以下表。

create table tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);

這裡，一些專案需要解釋：

  • 欄位屬性NOT NULL正在使用，因為我們不希望此欄位為 NULL。因此，如果使用者嘗試建立具有 NULL 值的記錄，則 MySQL 將引發錯誤。

  • 欄位屬性AUTO_INCREMENT告訴 MySQL 繼續並將下一個可用數字新增到 id 欄位。

  • 關鍵字PRIMARY KEY用於將列定義為主鍵。您可以使用逗號分隔的多個列來定義主鍵。

從命令提示符建立表

從 mysql> 提示符建立 MySQL 表很容易。您將使用 SQL 命令CREATE TABLE來建立表。

示例

這是一個示例，它將建立tutorials_tbl：

root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> CREATE TABLE tutorials_tbl(
   → tutorial_id INT NOT NULL AUTO_INCREMENT,
   → tutorial_title VARCHAR(100) NOT NULL,
   → tutorial_author VARCHAR(40) NOT NULL,
   → submission_date DATE,
   → PRIMARY KEY ( tutorial_id )
   → );
Query OK, 0 rows affected (0.16 sec)
mysql>

注意 - 在 SQL 命令末尾給出分號（;）之前，MySQL 不會終止命令。

使用 PHP 指令碼建立表

PHP 使用mysqli query()或mysql_query()函式來建立 MySQL 表。此函式接受兩個引數，並在成功時返回 TRUE，在失敗時返回 FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必需 - 建立 MySQL 表的 SQL 查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

嘗試以下示例以建立表：

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Creating MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = "CREATE TABLE tutorials_tbl( ".
            "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
            "tutorial_title VARCHAR(100) NOT NULL, ".
            "tutorial_author VARCHAR(40) NOT NULL, ".
            "submission_date DATE, ".
            "PRIMARY KEY ( tutorial_id )); ";
         if ($mysqli→query($sql)) {
            printf("Table tutorials_tbl created successfully.<br />");
         }
         if ($mysqli→errno) {
            printf("Could not create table: %s<br />", $mysqli→error);
         }
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache Web 伺服器上的 mysql_example.php 並驗證輸出。

Connected successfully.
Table tutorials_tbl created successfully.

MySQLi - 刪除表

刪除現有的 MySQL 表非常容易，但在刪除任何現有表時需要非常小心，因為刪除表後將無法恢復丟失的資料。

語法

這是一個刪除 MySQL 表的通用 SQL 語法：

DROP TABLE table_name ;

從命令提示符刪除表

要從命令提示符刪除表，我們需要在 mysql> 提示符下執行 DROP TABLE SQL 命令。

示例

以下程式是一個刪除tutorials_tbl的示例：

root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> DROP TABLE tutorials_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

使用 PHP 指令碼刪除表

PHP 使用mysqli query()或mysql_query()函式來刪除 MySQL 表。此函式接受兩個引數，並在成功時返回 TRUE，在失敗時返回 FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必需 - 刪除表的 SQL 查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

嘗試以下示例以刪除表：

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Dropping MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($mysqli→query("Drop Table tutorials_tbl")) {
            printf("Table tutorials_tbl dropped successfully.<br />");
         }
         if ($mysqli→errno) {
            printf("Could not drop table: %s<br />", $mysqli→error);
         }
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache Web 伺服器上的 mysql_example.php 並驗證輸出。

Connected successfully.
Table tutorials_tbl dropped successfully.

MySQLi - 插入查詢

要將資料插入 MySQL 表，您需要使用 SQLINSERT INTO命令。您可以使用 mysql> 提示符或使用任何指令碼（如 PHP）將資料插入 MySQL 表。

語法

這是一個將資料插入 MySQL 表的 INSERT INTO 命令的通用 SQL 語法：

INSERT INTO table_name ( field1, field2,...fieldN )
   VALUES
   ( value1, value2,...valueN );

要插入字串資料型別，需要將所有值保留在雙引號或單引號中。例如"value"。

從命令提示符插入資料

要從命令提示符插入資料，我們將使用 SQL INSERT INTO 命令將資料插入 MySQL 表 tutorials_tbl。

示例

以下示例將在tutorials_tbl表中建立 3 條記錄：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed

mysql> INSERT INTO tutorials_tbl 
   →(tutorial_title, tutorial_author, submission_date)
   →VALUES
   →("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl
   →(tutorial_title, tutorial_author, submission_date)
   →VALUES
   →("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl
   →(tutorial_title, tutorial_author, submission_date)
   →VALUES
   →("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)
mysql>

注意 - 請注意，所有箭頭符號（→）都不是 SQL 命令的一部分。它們表示新行，並且在按 Enter 鍵而不給每行命令末尾新增分號時，由 MySQL 提示符自動建立。

在上面的例子中，我們沒有提供 tutorial_id，因為在建立表的時候，我們已經為這個欄位設定了 AUTO_INCREMENT 選項。因此，MySQL 會自動處理這些 ID 的插入。這裡，NOW() 是一個 MySQL 函式，它返回當前日期和時間。

使用 PHP 指令碼插入資料

PHP 使用 mysqli_query() 或 mysql_query() 函式將記錄插入 MySQL 表中。此函式接受兩個引數，成功時返回 TRUE，失敗時返回 FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必填 - 將記錄插入表的 SQL 查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

此示例將從使用者處獲取三個引數，並將它們插入 MySQL 表中 - -

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Add New Record in MySQL Database</title>
   </head>
   <body>
      <?php
         if(isset($_POST['add'])) {
            $dbhost = 'localhost';
            $dbuser = 'root';
            $dbpass = 'root@123';
            $dbname = 'TUTORIALS';
            $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
            if($mysqli→connect_errno ) {
               printf("Connect failed: %s<br />", $mysqli→connect_error);
               exit();
            }
            printf('Connected successfully.<br />');

            if(! get_magic_quotes_gpc() ) {
               $tutorial_title = addslashes ($_POST['tutorial_title']);
               $tutorial_author = addslashes ($_POST['tutorial_author']);
            } else {
               $tutorial_title = $_POST['tutorial_title'];
               $tutorial_author = $_POST['tutorial_author'];
            }
            $submission_date = $_POST['submission_date'];
            $sql = "INSERT INTO tutorials_tbl ".
               "(tutorial_title,tutorial_author, submission_date) "."VALUES ".
               "('$tutorial_title','$tutorial_author','$submission_date')";
           
            if ($mysqli→query($sql)) {
               printf("Record inserted successfully.<br />");
            }
            if ($mysqli→errno) {
               printf("Could not insert record into table: %s<br />", $mysqli→error);
            }
            $mysqli→close();
         } else {
      ?>  
      <form method = "post" action = "<?php $_PHP_SELF ?>">
         <table width = "600" border = "0" cellspacing = "1" cellpadding = "2">
            <tr>
               <td width = "250">Tutorial Title</td>
               <td><input name = "tutorial_title" type = "text" id = "tutorial_title"></td>
            </tr>         
            <tr>
               <td width = "250">Tutorial Author</td>
               <td><input name = "tutorial_author" type = "text" id = "tutorial_author"></td>
            </tr>         
            <tr>
               <td width = "250">Submission Date [   yyyy-mm-dd ]</td>
               <td><input name = "submission_date" type = "text" id = "submission_date"></td>
            </tr>      
            <tr>
               <td width = "250"> </td>
               <td></td>
            </tr>         
            <tr>
               <td width = "250"> </td>
               <td><input name = "add" type = "submit" id = "add"  value = "Add Tutorial"></td>
            </tr>
         </table>
      </form>
   <?php
      }
   ?>
   </body>
</html>

輸出

訪問部署在 apache web 伺服器上的 mysql_example.php，輸入詳細資訊並在提交表單後驗證輸出。

Record inserted successfully.

在進行資料插入時，最好使用函式 get_magic_quotes_gpc() 來檢查是否設定了 magic quote 的當前配置。如果此函式返回 false，則使用函式 addslashes() 在引號前新增斜槓。

您可以圍繞它進行許多驗證，以檢查輸入的資料是否正確，並可以採取相應的措施。

MySQLi - 選擇查詢

SQL SELECT 命令用於從 MySQL 資料庫中獲取資料。您可以在 mysql> 提示符下以及在任何指令碼（如 PHP）中使用此命令。

語法

以下是從 MySQL 表中獲取資料的 SELECT 命令的通用 SQL 語法 -

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

  • 您可以使用逗號分隔一個或多個表以包含使用 WHERE 子句的各種條件，但 WHERE 子句是 SELECT 命令的可選部分。

  • 您可以在單個 SELECT 命令中獲取一個或多個欄位。

  • 您可以用星號 (*) 代替欄位。在這種情況下，SELECT 將返回所有欄位。

  • 您可以使用 WHERE 子句指定任何條件。

  • 您可以使用 OFFSET 指定偏移量，SELECT 將從該偏移量開始返回記錄。預設情況下，偏移量從零開始。

  • 您可以使用 LIMIT 屬性限制返回的數量。

從命令提示符獲取資料

這將使用 SQL SELECT 命令從 MySQL 表 tutorials_tbl 中獲取資料。

示例

以下示例將返回 tutorials_tbl 表中的所有記錄 -

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl 
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | John Poul       | 2007-05-21      |
|           2 | Learn MySQL    | Abdul S         | 2007-05-21      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.01 sec)

mysql>

使用 PHP 指令碼獲取資料

PHP 使用 mysqli_query() 或 mysql_query() 函式從 MySQL 表中選擇記錄。此函式接受兩個引數，成功時返回 TRUE，失敗時返回 FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必填 - 從 MySQL 表中選擇記錄的 SQL 查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

嘗試以下示例以從表中選擇記錄 -

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Creating MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
		 
         $result = $mysqli->query($sql);
           
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache web 伺服器上的 mysql_example.php 並驗證輸出。在執行 select 指令碼之前，我們在表中輸入了多條記錄。

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 4, Title: Java Tutorial, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

MySQLi - WHERE 子句

我們已經看到了 SQL SELECT 命令從 MySQL 表中獲取資料。我們可以使用稱為 WHERE 子句 的條件子句來過濾結果。使用此 WHERE 子句，我們可以指定選擇條件以從表中選擇所需的記錄。

語法

以下程式碼塊包含了帶有 WHERE 子句的 SELECT 命令的通用 SQL 語法，用於從 MySQL 表中獲取資料 -

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

  • 您可以使用逗號分隔一個或多個表以包含使用 WHERE 子句的各種條件，但 WHERE 子句是 SELECT 命令的可選部分。

  • 您可以使用 WHERE 子句指定任何條件。

  • 您可以使用 AND 或 OR 運算子指定多個條件。

  • WHERE 子句也可以與 DELETE 或 UPDATE SQL 命令一起使用來指定條件。

WHERE 子句在任何程式語言中都類似於 if 條件。此子句用於將給定值與 MySQL 表中可用的欄位值進行比較。如果來自外部的給定值等於 MySQL 表中可用的欄位值，則它將返回該行。

以下是可與 WHERE 子句一起使用的運算子列表。

假設欄位 A 包含 10，欄位 B 包含 20，則 -

運算子 描述 示例
= 檢查兩個運算元的值是否相等，如果相等，則條件變為真。 (A = B) 不為真。
!= 檢查兩個運算元的值是否相等，如果不相等，則條件變為真。 (A != B) 為真。
> 檢查左側運算元的值是否大於右側運算元的值，如果是，則條件變為真。 (A > B) 不為真。
< 檢查左側運算元的值是否小於右側運算元的值，如果是，則條件變為真。 (A < B) 為真。
>= 檢查左側運算元的值是否大於或等於右側運算元的值，如果是，則條件變為真。 (A >= B) 不為真。
<= 檢查左側運算元的值是否小於或等於右側運算元的值，如果是，則條件變為真。 (A <= B) 為真。

當您想要從表中獲取選定的行時，WHERE 子句非常有用，尤其是在使用 MySQL Join 時。聯接在另一章中討論。

使用 主鍵 搜尋記錄是一種常見做法，可以使搜尋更快。

如果給定的條件與表中的任何記錄都不匹配，則查詢將不返回任何行。

從命令提示符獲取資料

這將使用帶有 WHERE 子句的 SQL SELECT 命令從 MySQL 表 – tutorials_tbl 中獲取選定的資料。

示例

以下示例將返回 tutorials_tbl 表中作者名為 Sanjay 的所有記錄。

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl WHERE tutorial_author = 'Sanjay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      | JAVA Tutorial  |      Sanjay     |    2007-05-21   |      
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

除非對字串執行 LIKE 比較，否則比較不區分大小寫。您可以透過如下使用 BINARY 關鍵字使搜尋區分大小寫 -

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl \
   WHERE BINARY tutorial_author = 'sanjay';
Empty set (0.02 sec)

mysql>

使用 PHP 指令碼獲取資料

PHP 使用 mysqli_query() 或 mysql_query() 函式使用 where 子句從 MySQL 表中選擇記錄。此函式接受兩個引數，成功時返回 TRUE，失敗時返回 FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必填 - 使用 Where 子句從 MySQL 表中選擇記錄的 SQL 查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

嘗試以下示例以使用 where 子句從表中選擇記錄 -

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Using Where Clause</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author = "Mahesh"';
		 
         $result = $mysqli→query($sql);
           
         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache web 伺服器上的 mysql_example.php 並驗證輸出。在執行 select 指令碼之前，我們在表中輸入了多條記錄。

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021

MySQLi - 更新查詢

可能需要修改 MySQL 表中現有的資料。您可以使用 SQL UPDATE 命令來做到這一點。這將修改任何 MySQL 表的任何欄位值。

語法

以下程式碼塊包含了 UPDATE 命令的通用 SQL 語法，用於修改 MySQL 表中的資料 -

UPDATE table_name SET field1 = new-value1, field2 = new-value2
[WHERE Clause]
  • 您可以同時更新一個或多個欄位。
  • 您可以使用 WHERE 子句指定任何條件。
  • 您可以一次更新單個表中的值。

當您想要更新表中的選定行時，WHERE 子句非常有用。

從命令提示符更新資料

這將使用帶有 WHERE 子句的 SQL UPDATE 命令更新 MySQL 表 tutorials_tbl 中選定的資料。

示例

以下示例將更新 tutorial_id 為 3 的記錄的 tutorial_title 欄位。

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> UPDATE tutorials_tbl 
   → SET tutorial_title = 'Learning JAVA' 
   → WHERE tutorial_id = 3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

使用 PHP 指令碼更新資料

PHP 使用 mysqli_query() 或 mysql_query() 函式更新 MySQL 表中的記錄。此函式接受兩個引數，成功時返回 TRUE，失敗時返回 FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必填 - 更新 MySQL 表中記錄的 SQL 查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

嘗試以下示例以更新表中的記錄 -

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Updating MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($mysqli→query('UPDATE tutorials_tbl set tutorial_title = "Learning Java" where tutorial_id = 4')) {
            printf("Table tutorials_tbl updated successfully.<br />");
         }
         if ($mysqli→errno) {
            printf("Could not update table: %s<br />", $mysqli→error);
         }
         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
		 
         $result = $mysqli→query($sql);
           
         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache web 伺服器上的 mysql_example.php 並驗證輸出。在執行 select 指令碼之前，我們在表中輸入了多條記錄。

Connected successfully.
Table tutorials_tbl updated successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 4, Title: Learning Java, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

MySQLi - 刪除查詢

如果要從任何 MySQL 表中刪除記錄，則可以使用 SQL 命令 DELETE FROM。您可以在 mysql> 提示符下以及在任何指令碼（如 PHP）中使用此命令。

語法

以下程式碼塊包含了 DELETE 命令的通用 SQL 語法，用於從 MySQL 表中刪除資料。

DELETE FROM table_name [WHERE Clause]

  • 如果未指定 WHERE 子句，則將從給定的 MySQL 表中刪除所有記錄。

  • 您可以使用 WHERE 子句指定任何條件。

  • 您可以一次刪除單個表中的記錄。

當您想要刪除表中的選定行時，WHERE 子句非常有用。

從命令提示符刪除資料

這將使用帶有 WHERE 子句的 SQL DELETE 命令將選定的資料刪除到 MySQL 表 – tutorials_tbl 中。

示例

以下示例將刪除 tutorial_tbl 中 tutorial_id 為 3 的記錄。

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>

使用 PHP 指令碼刪除資料

PHP 使用 mysqli_query() 或 mysql_query() 函式刪除 MySQL 表中的記錄。此函式接受兩個引數，成功時返回 TRUE，失敗時返回 FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必填 - 刪除 MySQL 表中記錄的 SQL 查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

嘗試以下示例以刪除表中的記錄 -

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Deleting MySQL Table record</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($mysqli→query('DELETE FROM tutorials_tbl where tutorial_id = 4')) {
            printf("Table tutorials_tbl record deleted successfully.<br />");
         }
         if ($mysqli→errno) {
            printf("Could not delete record from table: %s<br />", $mysqli→error);
         }
   
         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
		 
         $result = $mysqli→query($sql);
           
         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache web 伺服器上的 mysql_example.php 並驗證輸出。在執行 select 指令碼之前，我們在表中輸入了多條記錄。

Connected successfully.
Table tutorials_tbl record deleted successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

MySQLi - LIKE 子句

我們已經看到了 SQL SELECT 命令從 MySQL 表中獲取資料。我們還可以使用稱為 WHERE 子句的條件子句來選擇所需的記錄。

帶有“等於”符號（=）的 WHERE 子句在我們要進行精確匹配時效果很好。例如，如果“tutorial_author = 'Sanjay'”。但可能需要過濾所有 tutorial_author 名稱應包含“jay”的結果。這可以使用 SQL LIKE 子句 以及 WHERE 子句來處理。

如果 SQL LIKE 子句與 % 字元一起使用，則它將類似於 UNIX 中的元字元 (*)，同時在命令提示符下列出所有檔案或目錄。在沒有 % 字元的情況下，LIKE 子句與帶有 WHERE 子句的 等於 符號完全相同。

語法

以下程式碼塊包含了帶有 LIKE 子句的 SELECT 命令的通用 SQL 語法，用於從 MySQL 表中獲取資料。

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

  • 您可以使用 WHERE 子句指定任何條件。

  • 您可以將 LIKE 子句與 WHERE 子句一起使用。

  • 您可以將 LIKE 子句替換為 等於 符號。

  • 當 LIKE 與 % 符號一起使用時，它將像元字元搜尋一樣工作。

  • 您可以使用AND或OR運算子指定多個條件。

  • WHERE...LIKE子句也可以與DELETE或UPDATE SQL命令一起使用來指定條件。

在命令提示符下使用LIKE子句

這將使用帶有WHERE...LIKE子句的SQL SELECT命令從MySQL表tutorials_tbl中獲取選定的資料。

示例

以下示例將返回tutorials_tbl表中所有作者姓名以jay結尾的記錄 -

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl 
   → WHERE tutorial_author LIKE '%jay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      |  JAVA Tutorial |     Sanjay      |    2007-05-21   |   
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

在PHP指令碼中使用LIKE子句

PHP使用mysqli query()或mysql_query()函式使用Like子句在MySQL表中選擇記錄。此函式接受兩個引數，成功時返回TRUE，失敗時返回FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必需 - 使用Like子句在MySQL表中選擇記錄的SQL查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

嘗試以下示例以使用表中的like子句選擇記錄 -

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Using Like Clause</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author like "Mah%"';
		 
         $result = $mysqli→query($sql);
           
         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache web 伺服器上的 mysql_example.php 並驗證輸出。在執行 select 指令碼之前，我們在表中輸入了多條記錄。

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021

MySQLi - 排序結果

我們已經瞭解了用於從MySQL表中獲取資料的SQL SELECT命令。當您選擇行時，MySQL伺服器可以自由地以任何順序返回它們，除非您透過說明如何排序結果來指示它。但是，您可以透過新增一個ORDER BY子句來對結果集進行排序，該子句指定要排序的列或列。

語法

以下程式碼塊是SELECT命令以及ORDER BY子句的通用SQL語法，用於對MySQL表中的資料進行排序。

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

  • 如果該欄位已列出，則可以對任何欄位對返回的結果進行排序。

  • 您可以對多個欄位對結果進行排序。

  • 您可以使用關鍵字ASC或DESC以升序或降序獲取結果。預設情況下，它是升序。

  • 您可以像往常一樣使用WHERE...LIKE子句來設定條件。

在命令提示符下使用ORDER BY子句

這將使用帶有ORDER BY子句的SQL SELECT命令從MySQL表tutorials_tbl中獲取資料。

示例

嘗試以下示例，該示例以升序返回結果。

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl ORDER BY tutorial_author ASC
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      2      |  Learn MySQL   |     Abdul S     |    2007-05-24   |   
|      1      |   Learn PHP    |    John Poul    |    2007-05-24   |   
|      3      | JAVA Tutorial  |     Sanjay      |    2007-05-06   |   
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.42 sec)

mysql>

驗證以升序列出的所有作者姓名。

在PHP指令碼中使用ORDER BY子句

PHP使用mysqli query()或mysql_query()函式從MySQL表中獲取排序後的記錄。此函式接受兩個引數，成功時返回TRUE，失敗時返回FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必需 - 從表中獲取排序後的記錄的SQL查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

示例

嘗試以下示例以從表中獲取排序後的記錄 -

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Sorting MySQL Table records</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl order by tutorial_title asc";
         $result = $mysqli→query($sql);
           
         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache Web 伺服器上的 mysql_example.php 並驗證輸出。

Connected successfully.
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021

MySQLi - 使用連線

在前面的章節中，我們一次從一個表中獲取資料。這對於簡單的操作來說已經足夠了，但在大多數現實世界的MySQL用法中，您通常需要在單個查詢中從多個表中獲取資料。

您可以在單個SQL查詢中使用多個表。在MySQL中連線的行為是指將兩個或多個表合併成一個表。

您可以在SELECT、UPDATE和DELETE語句中使用JOIN來連線MySQL表。我們還將看到一個LEFT JOIN的示例，它與簡單的MySQL JOIN不同。

在命令提示符下使用連線

假設我們在TUTORIALS中有兩個表tcount_tbl和tutorials_tbl。現在看看下面給出的例子 -

示例

以下示例 -

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * FROM tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
|      mahran     |       20       |     
|      mahnaz     |      NULL      |        
|       Jen       |      NULL      |          
|      Gill       |       20       |          
|    John Poul    |        1       |      
|     Sanjay      |        1       |        
+-----------------+----------------+
6 rows in set (0.01 sec)
mysql> SELECT * from tutorials_tbl;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      1      |  Learn PHP     |     John Poul   |    2007-05-24   |   
|      2      |  Learn MySQL   |      Abdul S    |    2007-05-24   |   
|      3      | JAVA Tutorial  |      Sanjay     |    2007-05-06   |   
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql>

現在我們可以編寫一個SQL查詢來連線這兩個表。此查詢將從表tutorials_tbl中選擇所有作者，並從tcount_tbl中獲取相應的教程數量。

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
   → FROM tutorials_tbl a, tcount_tbl b
   → WHERE a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|      1      |    John Poul    |        1       |
|      3      |     Sanjay      |        1       |
+-------------+-----------------+----------------+
2 rows in set (0.01 sec)
mysql>

在PHP指令碼中使用連線

PHP使用mysqli query()或mysql_query()函式使用連線從MySQL表中獲取記錄。此函式接受兩個引數，成功時返回TRUE，失敗時返回FALSE。

語法

$mysqli→query($sql,$resultmode)

序號 引數及描述
1

$sql

必需 - 使用連線從多個表中獲取記錄的SQL查詢。
2

$resultmode

可選 - 根據所需的行為使用常量 MYSQLI_USE_RESULT 或 MYSQLI_STORE_RESULT。預設情況下，使用 MYSQLI_STORE_RESULT。

首先使用以下指令碼在MySQL中建立一個表並插入兩條記錄。

create table tcount_tbl(
   tutorial_author VARCHAR(40) NOT NULL,
   tutorial_count int
);
insert into tcount_tbl values('Mahesh', 3);
insert into tcount_tbl values('Suresh', 1);

示例

嘗試以下示例以使用連線從兩個表中獲取記錄。 -

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Using joins on MySQL Tables</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
				FROM tutorials_tbl a, tcount_tbl b
				WHERE a.tutorial_author = b.tutorial_author';
		 
         $result = $mysqli→query($sql);
           
         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Id: %s, Author: %s, Count: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_author"], 
                  $row["tutorial_count"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache Web 伺服器上的 mysql_example.php 並驗證輸出。

Connected successfully.
Id: 1, Author: Mahesh, Count: 3
Id: 2, Author: Mahesh, Count: 3
Id: 3, Author: Mahesh, Count: 3
Id: 5, Author: Suresh, Count: 1

MySQL LEFT JOIN

MySQL左連線與簡單連線不同。MySQL LEFT JOIN 對左側的表給予了一些額外的考慮。

如果我執行LEFT JOIN，我會得到所有匹配的記錄，此外，我還為左表中每個不匹配的記錄獲得一條額外的記錄：從而確保（在我的示例中）每個作者都被提及。

示例

嘗試以下示例以瞭解LEFT JOIN。

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
   → FROM tutorials_tbl a LEFT JOIN tcount_tbl b
   → ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|      1      |    John Poul    |       1        |
|      2      |     Abdul S     |      NULL      |
|      3      |     Sanjay      |       1        |
+-------------+-----------------+----------------+
3 rows in set (0.02 sec)

您需要進行更多練習才能熟悉JOIN。這是MySQL/SQL中稍微有點複雜的概念，在做實際示例時會變得更加清晰。

MySQLi - 處理 NULL 值

我們已經瞭解了SQL SELECT命令以及WHERE子句，用於從MySQL表中獲取資料，但是當我們嘗試給出比較欄位或列值與NULL的條件時，它不能正常工作。

為了處理這種情況，MySQL提供了三個運算子 -

  • IS NULL - 如果列值為NULL，則此運算子返回true。

  • IS NOT NULL - 如果列值不為NULL，則此運算子返回true。

  • <=> - 此運算子比較值，（與=運算子不同）即使對於兩個NULL值也是true。

涉及NULL的條件是特殊的。您不能使用= NULL或!= NULL在列中查詢NULL值。此類比較始終失敗，因為無法判斷它們是真還是假。有時，即使NULL = NULL也會失敗。

要查詢是或不是NULL的列，請使用IS NULL或IS NOT NULL。

在命令提示符下使用NULL值

假設在TUTORIALS資料庫中有一個名為tcount_tbl的表，它包含兩個列，即tutorial_author和tutorial_count，其中NULL tutorial_count表示該值為未知。

示例

嘗試以下示例 -

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> create table tcount_tbl
   → (
   → tutorial_author varchar(40) NOT NULL,
   → tutorial_count  INT
   → );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tcount_tbl
   → (tutorial_author, tutorial_count) values ('mahran', 20);

mysql> INSERT INTO tcount_tbl
   → (tutorial_author, tutorial_count) values ('mahnaz', NULL);

mysql> INSERT INTO tcount_tbl
   → (tutorial_author, tutorial_count) values ('Jen', NULL);

mysql> INSERT INTO tcount_tbl
   → (tutorial_author, tutorial_count) values ('Gill', 20);

mysql> SELECT * from tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
|     mahran      |       20       |
|     mahnaz      |      NULL      |
|      Jen        |      NULL      |
|     Gill        |       20       |
+-----------------+----------------+
4 rows in set (0.00 sec)

mysql>

您可以看到=和!=不適用於NULL值，如下所示 -

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;
Empty set (0.01 sec)

要查詢tutorial_count列是或不是NULL的記錄，查詢應按以下程式中所示編寫。

mysql> SELECT * FROM tcount_tbl 
   → WHERE tutorial_count IS NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
|     mahnaz      |      NULL      |
|      Jen        |      NULL      |
+-----------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * from tcount_tbl 
   → WHERE tutorial_count IS NOT NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
|     mahran      |       20       |
|     Gill        |       20       |
+-----------------+----------------+
2 rows in set (0.00 sec)

在PHP指令碼中處理NULL值

您可以使用if...else條件根據NULL值準備查詢。

以下示例從外部獲取tutorial_count，然後將其與表中可用的值進行比較。

示例

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Handling NULL</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         $tutorial_count = null;
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         if( isset($tutorial_count )) {
            $sql = 'SELECT tutorial_author, tutorial_count
               FROM  tcount_tbl
               WHERE tutorial_count = ' + $tutorial_count;
         } else {
            $sql = 'SELECT tutorial_author, tutorial_count
               FROM  tcount_tbl
               WHERE tutorial_count IS NULL';
         }
         $result = $mysqli→query($sql);
           
         if ($result→num_rows > 0) {
            while($row = $result→fetch_assoc()) {
               printf("Author: %s, Count: %d <br />",
                  $row["tutorial_author"], 
                  $row["tutorial_count"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache Web 伺服器上的 mysql_example.php 並驗證輸出。

Connected successfully.
No record found.

MySQLi - 資料庫資訊

獲取和使用MySQL元資料

您希望從MySQL中獲取三種類型的資訊。

  • 查詢結果資訊 - 這包括任何SELECT、UPDATE或DELETE語句影響的記錄數。

  • 關於表和資料庫的資訊 - 這包括與表和資料庫結構相關的資訊。

  • 關於MySQL伺服器的資訊 - 這包括資料庫伺服器的狀態、版本號等。

在MySQL提示符下獲取所有這些資訊非常容易，但在使用PERL或PHP API時，我們需要顯式呼叫各種API來獲取所有這些資訊。

獲取查詢影響的行數

現在讓我們看看如何獲取此資訊。

PERL示例

在DBI指令碼中，受影響的行數由do( )或execute( )命令返回，具體取決於您如何執行查詢。

# Method 1
# execute $query using do( )
my $count = $dbh→do ($query);
# report 0 rows if an error occurred
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

# Method 2
# execute query using prepare( ) plus execute( )
my $sth = $dbh→prepare ($query);
my $count = $sth→execute ( );
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

PHP示例

在PHP中，呼叫mysql_affected_rows( )函式以找出查詢更改了多少行。

$result_id = mysql_query ($query, $conn_id);
# report 0 rows if the query failed
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected\n");

列出表和資料庫

列出資料庫伺服器上所有可用的資料庫和表非常容易。如果您沒有足夠的許可權，您的結果可能為null。

除了以下程式碼塊中顯示的方法外，您還可以使用SHOW TABLES或SHOW DATABASES查詢在PHP或PERL中獲取表或資料庫列表。

PERL示例

# Get all the tables available in current database.
my @tables = $dbh→tables ( );

foreach $table (@tables ){
   print "Table Name $table\n";
}

PHP示例

嘗試以下示例以獲取資料庫資訊 -

複製並貼上以下示例作為 mysql_example.php：

<html>
   <head>
      <title>Getting MySQL Database Info</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         $tutorial_count = null;
         
         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($result = mysqli_query($mysqli, "SELECT DATABASE()")) {
            $row = mysqli_fetch_row($result);
            printf("Default database is %s<br />", $row[0]);
            mysqli_free_result($result);
         }
         $mysqli→close();
      ?>
   </body>
</html>

輸出

訪問部署在 apache Web 伺服器上的 mysql_example.php 並驗證輸出。

Connected successfully.
Default database is tutorials

獲取伺服器元資料

MySQL中有一些重要的命令，可以在MySQL提示符下或使用PHP等任何指令碼執行，以獲取有關資料庫伺服器的各種重要資訊。

序號 命令和描述
1

SELECT VERSION( )

伺服器版本字串
2

SELECT DATABASE( )

當前資料庫名稱（如果無則為空）
3

SELECT USER( )

當前使用者名稱
4

SHOW STATUS

伺服器狀態指示器
5

SHOW VARIABLES

伺服器配置變數

MySQLi - 安裝

下載MySQL

MySQLi擴充套件旨在與MySQL 4.1.13或更高版本一起使用，因此必須下載MySQL。所有MySQL下載都位於MySQL Downloads。選擇您想要的MySQL Community Server的最新版本號，以及儘可能準確的平臺。

在Linux/UNIX上安裝MySQL

在Linux系統上安裝MySQL的推薦方法是透過RPM。MySQL AB在其網站上提供以下RPM供下載 -

  • MySQL - MySQL資料庫伺服器，它管理資料庫和表，控制使用者訪問並處理SQL查詢。

  • MySQL-client - MySQL客戶端程式，使連線到伺服器並與伺服器互動成為可能。

  • MySQL-devel - 在編譯使用MySQL的其他程式時派上用場的庫和標頭檔案。

  • MySQL-shared - MySQL客戶端的共享庫。

  • MySQL-bench - MySQL資料庫伺服器的基準測試和效能測試工具。

此處列出的MySQL RPM均在SuSE Linux系統上構建，但通常在其他Linux變體上也能毫無困難地工作。

現在，按照以下步驟繼續進行安裝 -

  • 使用root使用者登入系統。

  • 切換到包含RPM的目錄 -

  • 透過執行以下命令安裝MySQL資料庫伺服器。請記住用RPM的檔名替換斜體中的檔名。

[root@host]# rpm -i MySQL-5.0.9-0.i386.rpm

    上述命令負責安裝MySQL伺服器、建立MySQL使用者、建立必要的配置並自動啟動MySQL伺服器。

    您可以在/usr/bin和/usr/sbin中找到所有與MySQL相關的二進位制檔案。所有表和資料庫都將在/var/lib/mysql目錄中建立。

  • 這是可選但推薦的步驟，以相同的方式安裝其餘的RPM -

[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm

在Windows上安裝MySQL

現在，任何版本的Windows上的預設安裝都比以前容易得多，因為MySQL現在與安裝程式完美地打包在一起。只需下載安裝程式包，將其解壓縮到任何位置，然後執行setup.exe。

預設安裝程式setup.exe將引導您完成簡單的過程，並且預設情況下將在C:\mysql下安裝所有內容。

第一次從命令提示符啟動它來測試伺服器。轉到mysqld伺服器的位置，可能是C:\mysql\bin，然後輸入 -

mysqld.exe --console

注意 − 如果你使用的是 NT 系統，則需要使用 mysqld-nt.exe 而不是 mysqld.exe

如果一切順利，你會看到一些關於啟動和 InnoDB 的訊息。如果沒有，你可能存在許可權問題。確保儲存資料的目錄對資料庫程序執行的使用者（可能是 mysql）是可訪問的。

MySQL 不會自動新增到開始選單，也沒有特別好的圖形介面方式來停止伺服器。因此，如果你傾向於透過雙擊 mysqld 可執行檔案來啟動伺服器，則應該記住手動使用 mysqladmin、任務列表、任務管理器或其他 Windows 特定的方法來停止程序。

驗證 MySQL 安裝

在 MySQL 成功安裝、基礎表初始化並伺服器啟動後，你可以透過一些簡單的測試來驗證一切是否正常工作。

使用 mysqladmin 實用程式獲取伺服器狀態

使用mysqladmin二進位制檔案檢查伺服器版本。此二進位制檔案在 Linux 上位於 /usr/bin，在 Windows 上位於 C:\mysql\bin。

[root@host]# mysqladmin --version

它將在 Linux 上產生以下結果。它可能因你的安裝而異：

mysqladmin  Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386

如果你沒有收到這樣的訊息，那麼你的安裝可能存在一些問題，你需要一些幫助來解決它。

使用 MySQL 客戶端執行簡單的 SQL 命令

你可以使用 MySQL 客戶端透過mysql命令連線到你的 MySQL 伺服器。此時，你不需要提供任何密碼，因為預設情況下它將設定為空白。

只需使用以下命令

[root@host]# mysql

你應該會看到 mysql> 提示符。現在，你已連線到 MySQL 伺服器，並且可以在 mysql> 提示符下執行所有 SQL 命令，如下所示：

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.13 sec)

安裝後步驟

MySQL 為 root MySQL 使用者提供了一個空白密碼。一旦你成功安裝了資料庫和客戶端，你需要設定 root 密碼，如下所示：

[root@host]# mysqladmin -u root password "new_password";

現在要連線到你的 MySQL 伺服器，你必須使用以下命令：

[root@host]# mysql -u root -p
Enter password:*******

UNIX 使用者還希望將你的 MySQL 目錄放入你的 PATH 中，這樣你就不必每次使用命令列客戶端時都鍵入完整路徑。對於 bash，它將類似於：

export PATH = $PATH:/usr/bin:/usr/sbin

在啟動時執行 MySQL

如果你想在啟動時執行 MySQL 伺服器，請確保在 /etc/rc.local 檔案中包含以下條目。

/etc/init.d/mysqld start

此外，你應該在 /etc/init.d/ 目錄中擁有 mysqld 二進位制檔案。

MySQLi - 管理

執行和關閉 MySQL 伺服器

首先檢查你的 MySQL 伺服器是否正在執行。你可以使用以下命令來檢查它：

ps -ef | grep mysqld

如果你的 MySQL 正在執行，那麼你會看到結果中列出了mysqld程序。如果伺服器未執行，則可以使用以下命令啟動它：

root@host# cd /usr/bin
./safe_mysqld &

現在，如果你想關閉正在執行的 MySQL 伺服器，則可以使用以下命令：

root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

設定 MySQL 使用者帳戶

要向 MySQL 新增新使用者，你只需要在mysql資料庫的user表中新增一個新條目。

以下程式是新增新使用者guest的示例，該使用者具有 SELECT、INSERT 和 UPDATE 許可權，密碼為guest123；SQL 查詢為：

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
   (host, user, password, 
   select_priv, insert_priv, update_priv) 
   VALUES ('localhost', 'guest', 
   PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
|    host   |   user  |     password     |    
+-----------+---------+------------------+
| localhost |  guest  | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

新增新使用者時，請記住使用 MySQL 提供的 PASSWORD() 函式加密新密碼。如上例所示，密碼 mypass 被加密為 6f8c114b58f2ce9e。

注意 FLUSH PRIVILEGES 語句。這告訴伺服器重新載入授權表。如果你不使用它，那麼你將無法使用新使用者帳戶連線到 MySQL，至少在伺服器重新啟動之前是這樣。

你還可以透過在執行 INSERT 查詢時將user表中以下列的值設定為 'Y' 來為新使用者指定其他許可權，或者你以後可以使用 UPDATE 查詢更新它們。

  • Select_priv
  • Insert_priv
  • Update_priv
  • Delete_priv
  • Create_priv
  • Drop_priv
  • Reload_priv
  • Shutdown_priv
  • Process_priv
  • File_priv
  • Grant_priv
  • References_priv
  • Index_priv
  • Alter_priv

另一種新增使用者帳戶的方法是使用 GRANT SQL 命令。以下示例將為名為TUTORIALS的特定資料庫新增使用者zara，密碼為zara123。

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
   → ON TUTORIALS.*
   → TO 'zara'@'localhost'
   → IDENTIFIED BY 'zara123';

這還將在 MySQL 資料庫表中建立一個名為user的條目。

注意 − 在 SQL 命令末尾新增分號 (;) 之前，MySQL 不會終止命令。

/etc/my.cnf 檔案配置

在大多數情況下，你不應該修改此檔案。預設情況下，它將包含以下條目：

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

[mysql.server]
user = mysql
basedir = /var/lib

[safe_mysqld]
err-log = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

在這裡，你可以為錯誤日誌指定不同的目錄，否則你不應該更改此表中的任何條目。

管理 MySQL 命令

以下是重要的 MySQL 命令列表，你將不時使用這些命令來處理 MySQL 資料庫：

  • USE Databasename − 這將用於在 MySQL 工作區中選擇資料庫。

  • SHOW DATABASES − 列出 MySQL DBMS 可訪問的資料庫。

  • SHOW TABLES − 在使用 use 命令選擇資料庫後，顯示資料庫中的表。

  • SHOW COLUMNS FROM tablename: 顯示錶的屬性、屬性型別、鍵資訊、是否允許 NULL、預設值以及其他資訊。

  • SHOW INDEX FROM tablename − 顯示錶上所有索引的詳細資訊，包括 PRIMARY KEY。

  • SHOW TABLE STATUS LIKE tablename\G − 報告 MySQL DBMS 效能和統計資訊。

MySQLi - 資料型別

正確定義表中的欄位對於資料庫的整體最佳化非常重要。你應該只使用真正需要的欄位型別和大小；如果你知道只需要使用 2 個字元，則不要將欄位定義為 10 個字元寬。這些型別的欄位（或列）也稱為資料型別，以你將儲存在這些欄位中的資料型別命名。

MySQL 使用許多不同的資料型別，分為三類：數字型別、日期和時間型別以及字串型別。

數字資料型別

MySQL 使用所有標準的 ANSI SQL 數字資料型別，因此，如果你從其他資料庫系統遷移到 MySQL，這些定義對你來說會很熟悉。以下列表顯示了常見的數字資料型別及其描述：

  • INT − 一個正常大小的整數，可以是有符號的或無符號的。如果是帶符號的，則允許的範圍是 -2147483648 到 2147483647。如果是無符號的，則允許的範圍是 0 到 4294967295。你可以指定最多 11 位的寬度。

  • TINYINT − 一個非常小的整數，可以是有符號的或無符號的。如果是帶符號的，則允許的範圍是 -128 到 127。如果是無符號的，則允許的範圍是 0 到 255。你可以指定最多 4 位的寬度。

  • SMALLINT − 一個小的整數，可以是有符號的或無符號的。如果是帶符號的，則允許的範圍是 -32768 到 32767。如果是無符號的，則允許的範圍是 0 到 65535。你可以指定最多 5 位的寬度。

  • MEDIUMINT − 一箇中等大小的整數，可以是有符號的或無符號的。如果是帶符號的，則允許的範圍是 -8388608 到 8388607。如果是無符號的，則允許的範圍是 0 到 16777215。你可以指定最多 9 位的寬度。

  • BIGINT − 一個大的整數，可以是有符號的或無符號的。如果是帶符號的，則允許的範圍是 -9223372036854775808 到 9223372036854775807。如果是無符號的，則允許的範圍是 0 到 18446744073709551615。你可以指定最多 20 位的寬度。

  • FLOAT(M,D) − 一個浮點數，不能是無符號的。你可以定義顯示長度 (M) 和小數位數 (D)。這不是必需的，並且預設為 10,2，其中 2 是小數位數，10 是總位數（包括小數位數）。FLOAT 的小數精度可以達到 24 位。

  • DOUBLE(M,D) − 一個雙精度浮點數，不能是無符號的。你可以定義顯示長度 (M) 和小數位數 (D)。這不是必需的，並且預設為 16,4，其中 4 是小數位數。DOUBLE 的小數精度可以達到 53 位。REAL 是 DOUBLE 的同義詞。

  • DECIMAL(M,D) − 一個未打包的浮點數，不能是無符號的。在未打包的小數中，每個小數對應一個位元組。定義顯示長度 (M) 和小數位數 (D) 是必需的。NUMERIC 是 DECIMAL 的同義詞。

日期和時間型別

MySQL 日期和時間資料型別為：

  • DATE − 格式為 YYYY-MM-DD 的日期，介於 1000-01-01 和 9999-12-31 之間。例如，1973 年 12 月 30 日將儲存為 1973-12-30。

  • DATETIME − 格式為 YYYY-MM-DD HH:MM:SS 的日期和時間組合，介於 1000-01-01 00:00:00 和 9999-12-31 23:59:59 之間。例如，1973 年 12 月 30 日下午 3:30 將儲存為 1973-12-30 15:30:00。

  • TIMESTAMP − 1970 年 1 月 1 日午夜到 2037 年某個時間之間的時間戳。這看起來像之前的 DATETIME 格式，只是數字之間沒有連字元；1973 年 12 月 30 日下午 3:30 將儲存為 19731230153000（YYYYMMDDHHMMSS）。

  • TIME − 以 HH:MM:SS 格式儲存時間。

  • YEAR(M) − 以 2 位或 4 位格式儲存年份。如果長度指定為 2（例如 YEAR(2)），則 YEAR 可以是 1970 到 2069（70 到 69）。如果長度指定為 4，則 YEAR 可以是 1901 到 2155。預設長度為 4。

字串型別

雖然數字和日期型別很有趣，但你儲存的大多數資料將以字串格式儲存。此列表描述了 MySQLi 中常見的字串資料型別。

  • CHAR(M) − 長度在 1 到 255 個字元之間的固定長度字串（例如 CHAR(5)），儲存時用空格填充到指定的長度。定義長度不是必需的，但預設為 1。

  • VARCHAR(M) − 可變長度字串，長度在 1 到 255 個字元之間；例如 VARCHAR(25)。建立 VARCHAR 欄位時必須定義長度。

  • BLOB 或 TEXT − 欄位的最大長度為 65535 個字元。BLOB 是“二進位制大物件”，用於儲存大量二進位制資料，例如影像或其他型別的檔案。定義為 TEXT 的欄位也儲存大量資料；這兩者之間的區別在於，儲存資料的排序和比較在 BLOB 中區分大小寫，而在 TEXT 欄位中不區分大小寫。您無需為 BLOB 或 TEXT 指定長度。

  • TINYBLOB 或 TINYTEXT − 最大長度為 255 個字元的 BLOB 或 TEXT 列。您無需為 TINYBLOB 或 TINYTEXT 指定長度。

  • MEDIUMBLOB 或 MEDIUMTEXT − 最大長度為 16777215 個字元的 BLOB 或 TEXT 列。您無需為 MEDIUMBLOB 或 MEDIUMTEXT 指定長度。

  • LONGBLOB 或 LONGTEXT − 最大長度為 4294967295 個字元的 BLOB 或 TEXT 列。您無需為 LONGBLOB 或 LONGTEXT 指定長度。

  • ENUM − 列舉，它是列表的另一種說法。定義 ENUM 時，您將建立一個專案列表，從中必須選擇值（或可以為 NULL）。例如，如果您希望您的欄位包含“A”或“B”或“C”，則會將您的 ENUM 定義為 ENUM('A', 'B', 'C')，並且只有這些值（或 NULL）才能填充該欄位。

MySQLi - 正則表示式

您已經瞭解了使用LIKE ...%的 MySQL 模式匹配。MySQL 支援另一種基於正則表示式和REGEXP運算子的模式匹配操作。如果您熟悉 PHP 或 PERL，那麼您很容易理解，因為這種匹配與這些指令碼正則表示式非常相似。

以下是可與REGEXP運算子一起使用的模式表。

模式 模式匹配的內容
^ 字串開頭
$ 字串結尾
. 任何單個字元
[...] 方括號之間列出的任何字元
[^...] 方括號之間未列出的任何字元
p1|p2|p3 替換；匹配模式 p1、p2 或 p3 中的任何一個
* 前一個元素的零個或多個例項
+ 前一個元素的一個或多個例項
{n} 前一個元素的 n 個例項
{m,n} 前一個元素的 m 到 n 個例項

示例

現在，根據上表，您可以設計各種型別的 SQL 查詢來滿足您的需求。在這裡，我列出了一些供您理解。假設我們有一個名為 tutorials_inf 的表，它有一個名為 name 的欄位 -

查詢所有以“sa”開頭的名稱的查詢

mysql>  SELECT * FROM tutorials_inf WHERE name REGEXP '^sa';

示例輸出應如下所示 -

+----+------+
| id | name |
+----+------+
|  1 | sai  |
+----+------+
1 row in set (0.00 sec)

查詢所有以“ai”結尾的名稱的查詢

mysql> SELECT * FROM tutorials_inf WHERE name REGEXP 'ai$';

示例輸出應如下所示 -

+----+------+
| id | name |
+----+------+
|  1 | sai  |
+----+------+
1 row in set (0.00 sec)

查詢包含“a”的所有名稱的查詢

mysql> SELECT * FROM tutorials_inf WHERE name REGEXP 'a';

示例輸出應如下所示 -

+----+-------+
| id | name  |
+----+-------+
|  1 | sai   |
|  3 | ram   |
|  4 | johar |
+----+-------+
3 rows in set (0.00 sec)

查詢所有以母音開頭的名稱的查詢

mysql>  SELECT * FROM tutorials_inf WHERE name REGEXP '^[aeiou]';

MySQLi - 事務

事務是一組按順序執行的資料庫操作，這些操作被視為一個工作單元。換句話說，除非組中的每個操作都成功，否則事務永遠不會完成。如果事務中的任何操作失敗，則整個事務將失敗。

實際上，您會將許多 SQL 查詢組合成一個組，並將它們一起作為事務的一部分執行。

事務的屬性

事務具有以下四個標準屬性，通常用首字母縮略詞 ACID 來表示 -

  • 原子性 − 確保工作單元中的所有操作都成功完成；否則，事務將在發生故障時中止，並且先前操作將回滾到其以前的狀態。

  • 一致性 − 確保資料庫在成功提交的事務後正確更改狀態。

  • 隔離性 − 使事務能夠獨立於彼此並對其透明地執行。

  • 永續性 − 確保已提交事務的結果或效果在系統故障的情況下仍然存在。

在 MySQL 中，事務以語句 BEGIN WORK 開始，並以 COMMIT 或 ROLLBACK 語句結束。BEGIN 和 END 語句之間的 SQLi 命令構成了事務的主體。

COMMIT 和 ROLLBACK

這兩個關鍵字Commit 和Rollback 主要用於 MySQL 事務。

  • 當成功完成事務時，應發出 COMMIT 命令，以便對所有相關表所做的更改生效。

  • 如果發生故障，應發出 ROLLBACK 命令以將事務中引用的每個表恢復到其先前狀態。

您可以透過設定名為AUTOCOMMIT的會話變數來控制事務的行為。如果 AUTOCOMMIT 設定為 1（預設值），則每個 SQL 語句（在事務中或不在事務中）都被視為一個完整的事務，並在完成時預設提交。當 AUTOCOMMIT 設定為 0 時，透過發出 SET AUTOCOMMIT=0 命令，後續的一系列語句將充當事務，並且在發出顯式 COMMIT 語句之前不會提交任何活動。

您可以使用mysqli_query()函式在 PHP 中執行這些 SQL 命令。

事務的通用示例

此事件序列獨立於所使用的程式語言；可以在您用於建立應用程式的任何語言中建立邏輯路徑。

您可以使用mysqli_query()函式在 PHP 中執行這些 SQL 命令。

  • 透過發出 SQL 命令BEGIN WORK開始事務。

  • 發出一個或多個 SQL 命令，例如 SELECT、INSERT、UPDATE 或 DELETE。

  • 檢查是否存在錯誤以及是否一切符合您的要求。

  • 如果存在任何錯誤，則發出 ROLLBACK 命令，否則發出 COMMIT 命令。

MySQL 中的事務安全表型別

您不能直接使用事務，您可以使用，但它們不會是安全且有保證的。如果您計劃在 MySQL 程式設計中使用事務，則需要以特殊方式建立表。有許多型別的表支援事務，但最流行的是InnoDB。

對 InnoDB 表的支援需要在從原始碼編譯 MySQL 時使用特定的編譯引數。如果您的 MySQL 版本不支援 InnoDB，請要求您的網際網路服務提供商構建一個支援 InnoDB 表型別的 MySQL 版本，或下載並安裝適用於 Windows 或 Linux/UNIX 的 MySQL-Max 二進位制發行版，並在開發環境中使用該表型別。

如果您的 MySQL 安裝支援 InnoDB 表，只需在表建立語句中新增TYPE = InnoDB定義即可。例如，以下程式碼建立了一個名為 tutorials_innodb 的 InnoDB 表 -

root@host# mysql -u root -p;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> create table tutorials_innodb
   → (
   → tutorial_author varchar(40) NOT NULL,
   → tutorial_count  INT
   → ) TYPE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

檢視以下連結以瞭解更多資訊 - InnoDB

您可以使用其他表型別，如GEMINI或BDB，但這取決於您的安裝是否支援這兩種型別。

MySQLi - ALTER 命令

當您想要更改表名、任何表字段或想要在表中新增或刪除現有列時，MySQL 的ALTER命令非常有用。

讓我們從建立一個名為tutorials_alter的表開始。

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> create table tutorials_alter
   → (
   → i INT,
   → c CHAR(1)
   → );
Query OK, 0 rows affected (0.27 sec)

mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)

刪除、新增或重新定位列

假設您想從上面的 MySQL 表中刪除現有列i，那麼您將使用DROP子句以及ALTER命令，如下所示 -

mysql> ALTER TABLE tutorials_alter  DROP i;

如果列是表中剩下的唯一列，則DROP將不起作用。

要新增列，請使用 ADD 並指定列定義。以下語句將i列恢復到 tutorials_alter -

mysql> ALTER TABLE tutorials_alter ADD i INT;

發出此語句後，testalter 將包含與您首次建立表時相同的兩列，但結構不會完全相同。這是因為新列預設新增到表的末尾。因此，即使i最初是 mytbl 中的第一列，現在它也是最後一列。

mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

要指示您希望列在表中的特定位置，請使用 FIRST 將其設為第一列，或使用 AFTER col_name 指示新列應放在 col_name 之後。嘗試以下 ALTER TABLE 語句，在每個語句之後使用 SHOW COLUMNS 來檢視每個語句的效果 -

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 說明符僅適用於 ADD 子句。這意味著，如果您想在表中重新定位現有列，則必須先將其刪除，然後在新的位置新增它。

更改列定義或名稱

要更改列的定義，請使用MODIFY或CHANGE子句以及 ALTER 命令。例如，要將列c從 CHAR(1) 更改為 CHAR(10)，請執行以下操作 -

mysql> ALTER TABLE tutorials_alter MODIFY c CHAR(10);

使用 CHANGE，語法略有不同。在 CHANGE 關鍵字之後，您命名要更改的列，然後指定新定義，其中包括新名稱。試試以下示例

mysql> ALTER TABLE tutorials_alter CHANGE i j BIGINT;

如果您現在使用 CHANGE 將 j 從 BIGINT 轉換回 INT 而不更改列名，則該語句將按預期執行 -

mysql> ALTER TABLE tutorials_alter CHANGE j j INT;

ALTER TABLE 對 NULL 和預設值屬性的影響 -

當您修改或更改列時，您還可以指定列是否可以包含 NULL 值以及其預設值是什麼。實際上，如果您不這樣做，MySQL 會自動為這些屬性分配值。

這是一個示例，其中 NOT NULL 列的預設值為 100。

mysql> ALTER TABLE tutorials_alter 
   → MODIFY j BIGINT NOT NULL DEFAULT 100;

如果您不使用上述命令，則 MySQL 將在所有列中填充 NULL 值。

更改列的預設值

您可以使用 ALTER 命令更改任何列的預設值。試試以下示例。

mysql> ALTER TABLE tutorials_alter ALTER j SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | NULL    |       |
| j     | bigint(20) | NO   |     | 1000    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

您可以使用 DROP 子句以及 ALTER 命令從任何列中刪除預設約束。

mysql> ALTER TABLE tutorials_alter ALTER j DROP DEFAULT;
mysql> SHOW COLUMNS FROM tutorials_alter;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c     | char(10)   | YES  |     | NULL    |       |
| j     | bigint(20) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

更改表型別

您可以使用TYPE子句以及 ALTER 命令使用表型別。

要找出表的當前型別，請使用 SHOW TABLE STATUS 語句。

mysql>  SHOW TABLE STATUS LIKE 'tutorials_alter'\G
*************************** 1. row ***************************
           Name: tutorials_alter
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-02-17 11:30:29
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

重命名錶

要重命名錶，請使用 ALTER TABLE 語句的RENAME選項。嘗試以下示例將 tutorials_alter 重新命名為 tutorials_bks。

mysql> ALTER TABLE tutorials_alter RENAME TO tutorials_bks;

您可以使用 ALTER 命令在 MySQL 檔案上建立和刪除索引。我們將在下一章中看到此功能。

MySQLi - 索引

資料庫索引是一種資料結構，可以提高表中操作的速度。可以使用一個或多個列建立索引，為快速隨機查詢和有效排序對記錄的訪問提供基礎。

建立索引時，應考慮哪些列將用於執行 SQL 查詢，並在這些列上建立一個或多個索引。

實際上，索引也是一種表，它保留主鍵或索引欄位以及指向實際表中每個記錄的指標。

使用者看不到索引，它們僅用於加速查詢，並將由資料庫搜尋引擎用於非常快地定位記錄。

INSERT 和 UPDATE 語句在具有索引的表上花費更多時間，而 SELECT 語句在這些表上變得更快。原因是在執行插入或更新時，資料庫也需要插入或更新索引值。

簡單索引和唯一索引

您可以在表上建立唯一索引。唯一索引表示兩行不能具有相同的索引值。以下是建立表索引的語法。

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

您可以使用一個或多個列來建立索引。例如，我們可以使用 NAME_INDEX 在 tutorials_inf 上建立索引。

CREATE UNIQUE INDEX NAME_INDEX ON tutorials_inf(name);

您可以為表建立簡單的索引。只需從查詢中省略 UNIQUE 關鍵字即可建立簡單索引。簡單索引允許表中存在重複值。

如果要按降序對列中的值建立索引，可以在列名後新增保留字 DESC。

mysql> CREATE UNIQUE INDEX NAME_INDEX ON tutorials_inf (name DESC);

用於新增和刪除索引的 ALTER 命令

有四種用於向表新增索引的語句：

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) - 此語句新增主鍵，這意味著索引值必須唯一且不能為 NULL。

  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) - 此語句建立一個索引，其值必須唯一（除了 NULL 值，它可能出現多次）。

  • ALTER TABLE tbl_name ADD INDEX index_name (column_list) - 這將新增一個普通索引，其中任何值都可能出現多次。

  • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) - 這將建立一個用於文字搜尋目的的特殊 FULLTEXT 索引。

以下是在現有表中新增索引的示例。

mysql> ALTER TABLE tutorials_inf ADD INDEX (id);

您可以使用 DROP 子句以及 ALTER 命令刪除任何索引。嘗試以下示例以刪除上面建立的索引。

mysql> ALTER TABLE tutorials_inf DROP INDEX (c);

您可以使用 DROP 子句以及 ALTER 命令刪除任何索引。嘗試以下示例以刪除上面建立的索引。

用於新增和刪除主鍵的 ALTER 命令

您也可以以相同的方式新增主鍵。但請確保主鍵作用於非 NULL 列。

以下是在現有表中新增主鍵的示例。這將首先使列變為 NOT NULL，然後將其新增為主鍵。

mysql>  ALTER TABLE tutorials_inf MODIFY id INT NOT NULL;
mysql> ALTER TABLE tutorials_inf ADD PRIMARY KEY (id);

您可以使用 ALTER 命令刪除主鍵，如下所示

mysql> ALTER TABLE tutorials_inf DROP PRIMARY KEY;

要刪除不是主鍵的索引，必須指定索引名稱。

顯示索引資訊

您可以使用 SHOW INDEX 命令列出與表關聯的所有索引。垂直格式輸出（由 \G 指定）通常與此語句一起使用，以避免長行換行：

嘗試以下示例

mysql> SHOW INDEX FROM table_name\G
........

MySQLi - 臨時表

臨時表在某些情況下可能非常有用，可以儲存臨時資料。對於臨時表，應該知道的最重要的事情是，它們將在當前客戶端會話終止時被刪除。

如前所述，臨時表僅在會話處於活動狀態時才會存在。如果在 PHP 指令碼中執行程式碼，則指令碼執行完成後，臨時表將自動銷燬。如果透過 MySQL 客戶端程式連線到 MySQL 資料庫伺服器，則臨時表將一直存在，直到您關閉客戶端或手動銷燬表。

示例

以下是一個顯示臨時表用法的示例。相同的程式碼可以使用 mysqli_query() 函式在 PHP 指令碼中使用。

mysql> CREATE TEMPORARY TABLE SalesSummary (
   → product_name VARCHAR(50) NOT NULL
   → , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
   → , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
   → , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
   → );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
   → (product_name, total_sales, avg_unit_price, total_units_sold)
   → VALUES
   → ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

當您發出 SHOW TABLES 命令時，您的臨時表將不會列在列表中。現在，如果您登出 MySQL 會話，然後發出 SELECT 命令，則您將發現數據庫中沒有可用資料。即使您的臨時表也將不存在。

刪除臨時表

預設情況下，當您的資料庫連線終止時，所有臨時表都將被 MySQL 刪除。如果您想在兩者之間刪除它們，則可以透過發出 DROP TABLE 命令來實現。

以下是刪除臨時表的示例：

mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist

MySQLi - 克隆表

可能存在您需要表的精確副本的情況，而 CREATE TABLE ... SELECT 不適合您的目的，因為副本必須包含相同的索引、預設值等。

您可以按照以下步驟處理這種情況：

  • 使用 SHOW CREATE TABLE 獲取指定源表結構、索引等的 CREATE TABLE 語句。

  • 修改語句以將表名更改為克隆表的表名並執行該語句。這樣，您將擁有精確的克隆表。

  • 可選地，如果您還需要複製表內容，也請發出 INSERT INTO ... SELECT 語句。

示例

嘗試以下示例為 tutorials_inf 建立一個克隆表。

步驟 1

獲取有關表的完整結構。

mysql> SHOW CREATE TABLE tutorials_inf \G;
*************************** 1. row ***************************
       Table: tutorials_inf
Create Table: CREATE TABLE `tutorials_inf` (
   `id` int(11) NOT NULL,
   `name` varchar(20) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `AUTHOR_INDEX` (`name`),
   UNIQUE KEY `NAME_INDEX` (`name`),
   KEY `id` (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set (0.05 sec)

ERROR: No query specified

步驟 2

重新命名此表並建立另一個表。

mysql> CREATE TABLE tutorials_clone(
   → id int(11) NOT NULL,
   → name varchar(20) NOT NULL,
   → PRIMARY KEY (id),
   → UNIQUE KEY AUTHOR_INDEX (name),
   → UNIQUE KEY NAME_INDEX (name),
   →  KEY id (id));
Query OK, 0 rows affected (1.80 sec)

步驟 3

執行步驟 2 後，您將在資料庫中建立一個克隆表。如果要從舊錶複製資料，則可以使用 INSERT INTO... SELECT 語句。

mysql> INSERT INTO tutorials_clone(id,name) SELECT id,name from tutorials_inf;
Query OK, 4 rows affected (0.19 sec)
Records: 4  Duplicates: 0  Warnings: 0

最後，您將擁有您想要的精確克隆表。

MySQLi - 使用序列

序列是一組按需按順序生成的整數 1、2、3、...。序列在資料庫中經常使用，因為許多應用程式要求表中的每一行都包含一個唯一值，而序列提供了一種簡單的方法來生成它們。本章介紹如何在 MySQL 中使用序列。

使用 AUTO_INCREMENT 列

在 MySQL 中使用序列的最簡單方法是將列定義為 AUTO_INCREMENT，並將其餘事情留給 MySQL 來處理。

示例

嘗試以下示例。這將建立表，然後在此表中插入幾行，其中不需要提供記錄 ID，因為它由 MySQL 自動遞增。

mysql>CREATE TABLE tutorials_auto(
   id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL,PRIMARY KEY(id));
Query OK, 0 rows affected (0.28 sec)

mysql>INSERT INTO tutorials_auto(id,name) VALUES(NULL,'sai'),(NULL,'ram');
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM insect ORDER BY id;
+----+------+
| id | name |
+----+------+
|  1 | sai  |
|  2 | ram  |
+----+------+
2 rows in set (0.05 sec)

獲取 AUTO_INCREMENT 值

LAST_INSERT_ID( ) 是一個 SQL 函式，因此您可以從任何瞭解如何發出 SQL 語句的客戶端中使用它。否則，PERL 和 PHP 指令碼提供獨有的函式來檢索最後一條記錄的自動遞增值。

PERL示例

使用 mysql_insertid 屬性獲取查詢生成的 AUTO_INCREMENT 值。此屬性可以透過資料庫控制代碼或語句控制代碼訪問，具體取決於您如何發出查詢。以下示例透過資料庫控制代碼引用它

$dbh→do ("INSERT INTO tutorials_auto (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh→{mysqli_insertid};

PHP示例

發出生成 AUTO_INCREMENT 值的查詢後，透過呼叫 mysql_insert_id( ) 檢索該值：

mysql_query ("INSERT INTO tutorials_auto (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysqli_insert_id ($conn_id);

重新編號現有序列

可能存在您已從表中刪除了許多記錄並且您想要重新排序所有記錄的情況。這可以透過使用一個簡單的技巧來完成，但是如果您的表與其他表具有連線，則您應該非常小心地這樣做。

如果您確定重新排序 AUTO_INCREMENT 列是不可避免的，則執行此操作的方法是從表中刪除該列，然後重新新增它。以下示例顯示瞭如何使用此技術重新編號 insect 表中的 id 值：

mysql> ALTER TABLE tutorials_auto DROP id;
mysql> ALTER TABLE tutorials_auto
   → ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
   → ADD PRIMARY KEY (id);

從特定值開始序列

預設情況下，MySQLi 將從 1 開始序列，但您也可以在建立表時指定任何其他數字。以下是在 MySQL 將從 100 開始序列的示例。

mysql> CREATE TABLE tutorials_auto
   → (
   → id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
   → PRIMARY KEY (id),
   → name VARCHAR(30) NOT NULL, 
   → );

或者，您可以建立表，然後使用 ALTER TABLE 設定初始序列值。

mysql> ALTER TABLE tutorials_auto AUTO_INCREMENT = 100;

MySQLi - 處理重複項

表或結果集有時包含重複記錄。有時，這是允許的，但有時需要停止重複記錄。有時，需要識別重複記錄並將其從表中刪除。本章將介紹如何防止表中出現重複記錄以及如何刪除已存在的重複記錄。

防止表中出現重複項

您可以使用具有適當欄位的表上的 PRIMARY KEY 或 UNIQUE 索引來停止重複記錄。讓我們舉一個例子：下表不包含此類索引或主鍵，因此它將允許 first_name 和 last_name 的重複記錄。

CREATE TABLE person_tbl (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

為了防止在此表中建立具有相同 first 和 last name 值的多條記錄，請將其定義新增為主鍵。執行此操作時，還需要將索引列宣告為 NOT NULL，因為主鍵不允許 NULL 值：

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

表中存在唯一索引通常會導致錯誤發生，如果您向表中插入一條記錄，該記錄複製了定義索引的列或列中的現有記錄。

使用 INSERT IGNORE 而不是 INSERT。如果記錄不重複現有記錄，則 MySQL 會照常插入它。如果記錄是重複的，則 IGNORE 關鍵字告訴 MySQL 靜默地丟棄它，而不會生成錯誤。

以下示例不會出錯，同時也不會插入重複記錄。

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   → VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   → VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

使用 REPLACE 而不是 INSERT。如果記錄是新的，則會像使用 INSERT 一樣插入它。如果它是重複的，則新記錄將替換舊記錄：

mysql> REPLACE INTO person_tbl (last_name, first_name)
   → VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   → VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

應根據您想要實現的重複處理行為選擇 INSERT IGNORE 和 REPLACE。INSERT IGNORE 保留一組重複記錄中的第一個，並丟棄其餘記錄。REPLACE 保留一組重複項中的最後一個，並擦除任何較早的重複項。

另一種強制唯一性的方法是向表新增唯一索引而不是主鍵。

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

計算和識別重複項

以下是在表中計算具有 first_name 和 last_name 的重複記錄的查詢。

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
   → FROM person_tbl
   → GROUP BY last_name, first_name
   → HAVING repetitions > 1;

此查詢將返回 person_tbl 表中所有重複記錄的列表。一般來說，要識別重複的值集，請執行以下操作：

  • 確定哪些列包含可能重複的值。

  • 在列選擇列表中列出這些列，以及 COUNT(*)。

  • 還在 GROUP BY 子句中列出這些列。

  • 新增一個 HAVING 子句，透過要求組計數大於 1 來消除唯一值。

從查詢結果中消除重複項

您可以將 DISTINCT 與 SELECT 語句一起使用以查詢表中可用的唯一記錄。

mysql> SELECT DISTINCT last_name, first_name
   → FROM person_tbl
   → ORDER BY last_name;

DISTINCT 的替代方法是新增一個 GROUP BY 子句，其中命名您正在選擇的列。這將刪除重複項並僅選擇指定列中唯一值的組合：

mysql> SELECT last_name, first_name
   → FROM person_tbl
   → GROUP BY (last_name, first_name);

使用表替換刪除重複項

如果表中存在重複記錄，並且您想從該表中刪除所有重複記錄，則以下是步驟：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   → FROM person_tbl;
   → GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

從表中刪除重複記錄的一種簡單方法是在該表中新增索引或主鍵。即使此表已經可用，您也可以使用此技術刪除重複記錄，並且將來也會安全。

mysql> ALTER IGNORE TABLE person_tbl 
   → ADD PRIMARY KEY (last_name, first_name);
列印頁面

上一頁
下一頁
廣告