MySQL 快速指南

什麼是資料庫？

資料庫是一個獨立的應用程式，用於儲存資料的集合。每個資料庫都擁有一個或多個用於建立、訪問、管理、搜尋和複製其所儲存資料的 distinct API。

也可以使用其他型別的資料庫，例如檔案系統上的檔案或記憶體中大型雜湊表，但使用這些型別的系統進行資料獲取和寫入將不會那麼快速和容易。

如今，我們使用關係資料庫管理系統 (RDBMS) 來儲存和管理海量資料。這被稱為關係資料庫，因為所有資料都儲存在不同的表中，並且使用主鍵或其他稱為外部索引鍵的鍵來建立關係。

關係資料庫管理系統 (RDBMS)是一種軟體，它：

使您能夠實現具有表、列和索引的資料庫。
保證各個錶行之間的參照完整性。
自動更新索引。
解釋 SQL 查詢並組合來自各個表的資訊。

RDBMS 術語

在我們開始解釋 MySQL 資料庫系統之前，讓我們回顧一些與資料庫相關的定義。

資料庫 - 資料庫是相關資料的表的集合。
表 - 表是一個包含資料的矩陣。資料庫中的表看起來像一個簡單的電子表格。
列 - 一列（資料元素）包含一種相同型別的資料，例如郵政編碼列。
行 - 行（= 元組、條目或記錄）是一組相關資料，例如一個訂閱的資料。
冗餘 - 為了使系統更快，重複儲存資料。
主鍵 - 主鍵是唯一的。一個鍵值在一個表中不能出現兩次。使用鍵，您只能找到一行。
外部索引鍵 - 外部索引鍵是連線兩個表的連結。
複合鍵 - 複合鍵（組合鍵）是一個由多列組成的鍵，因為一列不足以唯一。
索引 - 資料庫中的索引類似於書後面的索引。
參照完整性 - 參照完整性確保外部索引鍵值始終指向現有行。

MySQL 資料庫

MySQL 是一種快速、易於使用的 RDBMS，被許多大小企業使用。MySQL 由瑞典公司 MySQL AB 開發、銷售和支援。MySQL 變得如此流行是有許多原因的：

MySQL 在開源許可下發布。因此，您無需付費即可使用它。
MySQL 本身就是一個非常強大的程式。它處理了最昂貴和最強大的資料庫包的大部分功能。
MySQL 使用眾所周知的標準 SQL 資料語言。
MySQL 可在許多作業系統和許多語言（包括 PHP、PERL、C、C++、JAVA 等）上執行。
MySQL 執行速度非常快，即使在大型資料集上也能很好地執行。
MySQL 對 PHP（最受推崇的 Web 開發語言）非常友好。
MySQL 支援大型資料庫，一張表最多可容納 5000 萬行或更多。表的預設檔案大小限制為 4GB，但您可以將其（如果您的作業系統可以處理）增加到理論上的 800 萬 TB（TB）的限制。
MySQL 可定製。開源 GPL 許可證允許程式設計師修改 MySQL 軟體以適應他們自己的特定環境。

開始之前

在開始本教程之前，您應該具備我們 PHP 和 HTML 教程中介紹的基本知識。

本教程主要關注在 PHP 環境中使用 MySQL。本教程中提供的許多示例對 PHP 程式設計師都非常有用。

我們建議您參考我們的PHP 教程。

MySQL - 安裝

所有 MySQL 下載檔案都位於 MySQL 下載。選擇您需要的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 不會將自身新增到開始選單，也沒有特別好的 GUI 方法來停止伺服器。因此，如果您傾向於透過雙擊 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 二進位制檔案。

MySQL - 管理

執行和關閉 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 查詢時將以下列的值設定為“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';

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

注意 − 在您在 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 資料庫名 − 這將用於在 MySQL 工作區中選擇資料庫。
SHOW DATABASES − 列出 MySQL DBMS 可訪問的資料庫。
SHOW TABLES − 選擇資料庫後（使用 use 命令），顯示資料庫中的表。
SHOW COLUMNS FROM 表名: 顯示錶的屬性、屬性型別、鍵資訊、是否允許 NULL、預設值以及表的其他資訊。
SHOW INDEX FROM 表名 − 顯示錶上所有索引的詳細資訊，包括 PRIMARY KEY。
SHOW TABLE STATUS LIKE 表名\G − 報告 MySQL DBMS 效能和統計詳細資訊。

在下一章中，我們將討論如何在 MySQL 中使用 PHP 語法。

MySQL - PHP 語法

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

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

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

與 MySQL 一起使用的 PHP 函式具有以下通用格式：

mysql_function(value,value,...);

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

mysqli_connect($connect);
mysqli_query($connect,"SQL statement");

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

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

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

MySQL - 連線

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

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

示例

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

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

這將為您提供 mysql> 命令提示符，您可以在其中執行任何 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 提供mysql_connect()函式來開啟資料庫連線。此函式接受五個引數，並在成功時返回 MySQL 連結識別符號，在失敗時返回 FALSE。

語法

connection mysql_connect(server,user,passwd,new_link,client_flag);

序號	引數和說明
1	伺服器可選 − 執行資料庫伺服器的主機名。如果未指定，則預設值為localhost:3306。
2	使用者可選 − 訪問資料庫的使用者名稱。如果未指定，則預設為擁有伺服器程序的使用者名稱稱。
3	密碼可選 − 訪問資料庫的使用者的密碼。如果未指定，則預設為空密碼。
4	new_link 可選 − 如果使用相同的引數對 mysql_connect() 進行第二次呼叫，則不會建立新的連線；相反，將返回已開啟連線的識別符號。
5	client_flags 可選 − 以下常量的組合： MYSQL_CLIENT_SSL − 使用 SSL 加密。 MYSQL_CLIENT_COMPRESS − 使用壓縮協議。 MYSQL_CLIENT_IGNORE_SPACE − 允許函式名後有空格。 MYSQL_CLIENT_INTERACTIVE − 允許互動式超時，在關閉連線之前等待幾秒鐘的空閒時間。

您可以隨時使用另一個 PHP 函式mysql_close()斷開與 MySQL 資料庫的連線。此函式接受一個引數，該引數是mysql_connect()函式返回的連線。

語法

bool mysql_close ( resource $link_identifier );

如果未指定資源，則關閉上次開啟的資料庫。如果成功關閉連線，則此函式返回 true，否則返回 false。

示例

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

<html>
   <head>
      <title>Connecting MySQL Server</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost:3306';
         $dbuser = 'guest';
         $dbpass = 'guest123';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully';
         mysql_close($conn);
      ?>
   </body>
</html>

MySQL - 建立資料庫

使用 mysqladmin 建立資料庫

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

示例

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

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

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

使用 PHP 指令碼建立資料庫

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

語法

bool mysql_query( sql, connection );

序號	引數和說明
1	sql 必需 - 建立或刪除 MySQL 資料庫的 SQL 查詢
2	連線可選 - 如果未指定，則使用 mysql_connect 最後開啟的連線。

序號

引數和說明

sql

必需 - 建立或刪除 MySQL 資料庫的 SQL 查詢

連線

可選 - 如果未指定，則使用 mysql_connect 最後開啟的連線。

示例

以下是如何建立資料庫的示例：

<html>
   <head>
      <title>Creating MySQL Database</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'root';
         $dbpass = 'rootpassword';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully<br />';
         $sql = 'CREATE DATABASE TUTORIALS';
         $retval = mysql_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not create database: ' . mysql_error());
         }
         echo "Database TUTORIALS created successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

刪除 MySQL 資料庫

使用 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 使用 **mysql_query** 函式來建立或刪除 MySQL 資料庫。此函式接受兩個引數，成功時返回 TRUE，失敗時返回 FALSE。

語法

bool mysql_query( sql, connection );

序號	引數和說明
1	sql 必需 - 建立或刪除 MySQL 資料庫的 SQL 查詢
2	連線可選 - 如果未指定，則使用 mysql_connect 最後開啟的連線。

序號

引數和說明

sql

必需 - 建立或刪除 MySQL 資料庫的 SQL 查詢

連線

可選 - 如果未指定，則使用 mysql_connect 最後開啟的連線。

示例

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

<html>
   <head>
      <title>Deleting MySQL Database</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'root';
         $dbpass = 'rootpassword';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully<br />';
         $sql = 'DROP DATABASE TUTORIALS';
         $retval = mysql_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not delete database: ' . mysql_error());
         }
         echo "Database TUTORIALS deleted successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

**警告** - 使用 PHP 指令碼刪除資料庫時，不會提示您進行任何確認。因此，刪除 MySQL 資料庫時要小心。

選擇 MySQL 資料庫

連線到 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 提供函式 **mysql_select_db** 來選擇資料庫。成功時返回 TRUE，失敗時返回 FALSE。

語法

bool mysql_select_db( db_name, connection );

序號	引數和說明
1	db_name 必需 - 要選擇的 MySQL 資料庫名稱
2	連線可選 - 如果未指定，則使用 mysql_connect 最後開啟的連線。

序號

引數和說明

db_name

必需 - 要選擇的 MySQL 資料庫名稱

連線

可選 - 如果未指定，則使用 mysql_connect 最後開啟的連線。

示例

以下是一個顯示如何選擇資料庫的示例。

<html>
   <head>
      <title>Selecting MySQL Database</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'guest';
         $dbpass = 'guest123';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully';
         mysql_select_db( 'TUTORIALS' );
         
         mysql_close($conn);
      ?>
   </body>
</html>

MySQL - 資料型別

正確定義表中的欄位對於資料庫的整體最佳化非常重要。您應該只使用真正需要的欄位型別和大小。例如，如果您知道只需要使用 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 年某個時間之間的timestamp。這看起來像之前的 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。

字串型別

儘管數值和日期型別很有趣，但您儲存的大多數資料都將採用字串格式。此列表描述了 MySQL 中常見的字串資料型別。

**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）才能填充該欄位。

在下一章中，我們將討論如何在 MySQL 中建立表。

建立 MySQL 表

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

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

語法

以下是如何建立 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 函式 **mysql_query()**。您將使用正確的 SQL 命令將其作為第二個引數傳遞以建立表。

示例

以下程式是使用 PHP 指令碼建立表的示例：

<html>
   <head>
      <title>Creating MySQL Tables</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'root';
         $dbpass = 'rootpassword';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo '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 )); ";
         mysql_select_db( 'TUTORIALS' );
         $retval = mysql_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not create table: ' . mysql_error());
         }
         echo "Table created successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

刪除 MySQL 表

刪除現有的 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函式mysql_query()。你需要將正確的SQL命令作為其第二個引數傳入，以刪除表。

示例

<html>
   <head>
      <title>Creating MySQL Tables</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'root';
         $dbpass = 'rootpassword';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully<br />';
         $sql = "DROP TABLE tutorials_tbl";
         mysql_select_db( 'TUTORIALS' );
         $retval = mysql_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not delete table: ' . mysql_error());
         }
         echo "Table deleted successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

MySQL - INSERT 查詢

要將資料插入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命令的一部分。它們指示新行，並且在按下回車鍵時，如果沒有在命令的每一行末尾給出分號，MySQL提示符會自動建立它們。

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

使用PHP指令碼插入資料

你可以將相同的SQL INSERT INTO命令放入PHP函式mysql_query()中，以將資料插入MySQL表。

示例

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

<html>

   <head>
      <title>Add New Record in MySQL Database</title>
   </head>

   <body>
      <?php
         if(isset($_POST['add'])) {
            $dbhost = 'localhost:3036';
            $dbuser = 'root';
            $dbpass = 'rootpassword';
            $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
            if(! $conn ) {
               die('Could not connect: ' . mysql_error());
            }

            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')";
               mysql_select_db('TUTORIALS');
            $retval = mysql_query( $sql, $conn );
         
            if(! $retval ) {
               die('Could not enter data: ' . mysql_error());
            }
         
            echo "Entered data successfully\n";
            mysql_close($conn);
         } 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>

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

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

MySQL - SELECT 查詢

SQLSELECT命令用於從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指令碼提取資料

你可以將相同的SQL SELECT命令放入PHP函式mysql_query()中。此函式用於執行SQL命令，然後另一個PHP函式mysql_fetch_array()可以用來提取所有選定的資料。此函式將行作為關聯陣列、數字陣列或兩者都返回。如果沒有更多行，則此函式返回FALSE。

以下程式是一個簡單的示例，它將演示如何從tutorials_tbl表中提取/顯示記錄。

示例

以下程式碼塊將顯示tutorials_tbl表中的所有記錄。

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

行的內容被賦值給變數$row，然後列印該行中的值。

注意 - 記住，當你想要直接將陣列值插入字串時，一定要使用花括號。

在上面的示例中，常量MYSQL_ASSOC用作PHP函式mysql_fetch_array()的第二個引數，以便它將行作為關聯陣列返回。使用關聯陣列，你可以透過使用欄位名稱而不是索引來訪問欄位。

PHP提供了另一個名為mysql_fetch_assoc()的函式，它也返回行作為關聯陣列。

示例

以下示例使用mysql_fetch_assoc()函式顯示tutorial_tbl表中的所有記錄。

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
      FROM tutorials_tbl';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_assoc($retval)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

你也可以使用常量MYSQL_NUM作為PHP函式mysql_fetch_array()的第二個引數。這將導致函式返回一個帶有數字索引的陣列。

示例

嘗試以下示例，使用MYSQL_NUM引數顯示tutorials_tbl表中的所有記錄。

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
      FROM tutorials_tbl';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
      echo "Tutorial ID :{$row[0]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
   }
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

以上三個示例將產生相同的結果。

釋放記憶體

最好在每個SELECT語句結束時釋放遊標記憶體。這可以透過使用PHP函式mysql_free_result()來完成。以下程式是演示如何使用它的示例。

示例

嘗試以下示例：

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
      FROM tutorials_tbl';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
      echo "Tutorial ID :{$row[0]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
   }
   mysql_free_result($retval);
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

在提取資料時，你可以編寫任意複雜的程式碼，但過程將與上述相同。

MySQL - WHERE子句

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

語法

以下程式碼塊包含使用WHERE子句從MySQL表中提取資料的SELECT命令的通用SQL語法：

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連線時。連線在另一章中討論。

通常的做法是使用主鍵搜尋記錄以加快搜索速度。

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

從命令提示符提取資料

這將使用帶有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指令碼提取資料

你可以將帶有WHERE CLAUSE的相同SQL SELECT命令放入PHP函式mysql_query()中。此函式用於執行SQL命令，然後另一個PHP函式mysql_fetch_array()可以用來提取所有選定的資料。此函式將行作為關聯陣列、數字陣列或兩者都返回。如果沒有更多行，則此函式返回FALSE。

示例

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

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = 'SELECT tutorial_id, tutorial_title, 
      tutorial_author, submission_date
      FROM tutorials_tbl
      WHERE tutorial_author = "Sanjay"';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
      "Title: {$row['tutorial_title']} <br> ".
      "Author: {$row['tutorial_author']} <br> ".
      "Submission Date : {$row['submission_date']} <br> ".
      "--------------------------------<br>";
   } 

   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

MySQL - UPDATE查詢

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

語法

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

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指令碼更新資料

可以使用SQL UPDATE命令（帶或不帶WHERE子句）在PHP函式mysql_query()中。此函式將以類似於在mysql>提示符下執行的方式執行SQL命令。

示例

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

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = 'UPDATE tutorials_tbl
      SET tutorial_title="Learning JAVA"
      WHERE tutorial_id=3';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not update data: ' . mysql_error());
   }
   echo "Updated data successfully\n";
   mysql_close($conn);
?>

MySQL - DELETE 查詢

如果要從任何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指令碼刪除資料

可以使用SQL DELETE命令（帶或不帶WHERE子句）在PHP函式mysql_query()中。此函式將以與在mysql>提示符下執行相同的方式執行SQL命令。

示例

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

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = 'DELETE FROM tutorials_tbl WHERE tutorial_id = 3';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not delete data: ' . mysql_error());
   }
   echo "Deleted data successfully\n";
   mysql_close($conn);
?>

MySQL - LIKE子句

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

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

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

語法

以下程式碼塊包含SELECT命令以及LIKE子句的通用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函式mysql_query()中使用WHERE...LIKE子句的類似語法。此函式用於執行SQL命令，之後可以使用另一個PHP函式mysql_fetch_array()來提取所有選定的資料（如果WHERE...LIKE子句與SELECT命令一起使用）。

但是，如果WHERE...LIKE子句與DELETE或UPDATE命令一起使用，則不需要進一步的PHP函式呼叫。

示例

嘗試以下示例以返回tutorials_tbl表中作者姓名包含jay的所有記錄：

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $sql = 'SELECT tutorial_id, tutorial_title, 
      tutorial_author, submission_date
      FROM tutorials_tbl
      WHERE tutorial_author LIKE "%jay%"';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

MySQL - 排序結果

我們已經瞭解了用於從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函式mysql_query()中使用ORDER BY子句的類似語法。此函式用於執行SQL命令，之後可以使用另一個PHP函式mysql_fetch_array()來提取所有選定的資料。

示例

嘗試以下示例，該示例以教程作者的降序返回結果。

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $sql = 'SELECT tutorial_id, tutorial_title, 
      tutorial_author, submission_date
      FROM tutorials_tbl
      ORDER BY  tutorial_author DESC';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

使用MySQL連線

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

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

可以在SELECT、UPDATE和DELETE語句中使用連線來連線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指令碼中使用任何上述SQL查詢。只需要將SQL查詢傳遞到PHP函式mysql_query()中，然後就可以照常提取結果。

示例

以下示例：

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

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

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Author:{$row['tutorial_author']}  <br> ".
         "Count: {$row['tutorial_count']} <br> ".
         "Tutorial ID: {$row['tutorial_id']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

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)

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

處理MySQL NULL值

我們已經瞭解了帶有WHERE子句的SQL SELECT命令，用於從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，然後將其與表中可用的值進行比較。

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   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 $tutorial_count';
   }

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Author:{$row['tutorial_author']}  <br> ".
         "Count: {$row['tutorial_count']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

MySQL - 正則表示式

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

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

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

示例

現在，根據上表，您可以設計各種型別的SQL查詢來滿足您的需求。在這裡，我列出了一些供您理解。

假設我們有一個名為person_tbl的表，它有一個名為name的欄位：

查詢所有以'st'開頭的名稱的查詢：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查詢所有以'ok'結尾的名稱的查詢：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查詢所有包含'mar'的名稱的查詢：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查詢所有以母音開頭並以'ok'結尾的名稱的查詢：

mysql> SELECT FirstName FROM intque.person_tbl WHERE FirstName REGEXP '^[aeiou].*ok$';

MySQL - 事務

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

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

事務的屬性

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

原子性 (Atomicity) − 確保工作單元中的所有操作都成功完成；否則，事務將在發生故障的點中止，之前的操作將回滾到其以前的狀態。
一致性 (Consistency) − 確保資料庫在成功提交事務後正確更改狀態。
隔離性 (Isolation) − 使事務能夠獨立執行，並且彼此透明。
永續性 (Durability) − 確保已提交事務的結果或影響在系統故障的情況下仍然存在。

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

COMMIT 和 ROLLBACK

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

成功完成事務後，應發出 COMMIT 命令，以便對所有相關表所做的更改生效。
如果發生故障，應發出 ROLLBACK 命令，以將事務中引用的每個表恢復到其先前狀態。

您可以透過設定名為AUTOCOMMIT 的會話變數來控制事務的行為。如果將 AUTOCOMMIT 設定為 1（預設值），則每個 SQL 語句（無論是否在事務中）都被視為一個完整的事務，並在完成時預設提交。

當 AUTOCOMMIT 設定為 0 時，透過發出SET AUTOCOMMIT = 0 命令，後續的語句序列將像事務一樣，在發出顯式的 COMMIT 語句之前不會提交任何活動。

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

事務的通用示例

此事件序列與使用的程式語言無關。可以使用任何語言建立邏輯路徑來建立應用程式。

您可以使用mysql_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 定義即可。

例如，以下程式碼建立一個名為tcount_tbl 的 InnoDB 表：

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
   -> ) TYPE = InnoDB;
Query OK, 0 rows affected (0.05 sec)

有關 InnoDB 的更多詳細資訊，您可以點選以下連結：InnoDB

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

MySQL - ALTER 命令

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

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

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

mysql> use TUTORIALS;
Database changed

mysql> create table testalter_tbl
   -> (
   -> i INT,
   -> c CHAR(1)
   -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   i   | int(11) | YES  |     |   NULL  |       |
|   c   | char(1) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

刪除、新增或重新定位列

如果要從上面的 MySQL 表中刪除現有列 i，則可以使用DROP 子句以及ALTER 命令，如下所示：

mysql> ALTER TABLE testalter_tbl  DROP i;

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

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

mysql> ALTER TABLE testalter_tbl ADD i INT;

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

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 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 子句。這意味著，如果您想重新定位表中的現有列，則必須先DROP 它，然後在新的位置ADD 它。

更改列定義或名稱

要更改列的定義，請使用MODIFY 或CHANGE 子句以及 ALTER 命令。

例如，要將列c 從 CHAR(1) 更改為 CHAR(10)，可以使用以下命令：

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

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

嘗試以下示例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

如果您現在使用 CHANGE 將j 從BIGINT 轉換回INT 而不更改列名，則語句將如下所示：

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 對 NULL 和預設值屬性的影響 − 當您修改或更改列時，您還可以指定列是否可以包含 NULL 值以及其預設值是什麼。事實上，如果您不這樣做，MySQL 會自動為這些屬性賦值。

以下程式碼塊是一個示例，其中NOT NULL 列的預設值為 100。

mysql> ALTER TABLE testalter_tbl 
   -> MODIFY j BIGINT NOT NULL DEFAULT 100;

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

更改列的預設值

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

試試下面的例子。

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   1000  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

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

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

更改表型別

您可以使用TYPE 子句以及 ALTER 命令使用表型別。嘗試以下示例將testalter_tbl 更改為MYISAM 表型別。

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

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

重命名錶

要重命名錶，請使用ALTER TABLE 語句的RENAME 選項。

嘗試以下示例將testalter_tbl 重新命名為alter_tbl。

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

您可以使用 ALTER 命令在 MySQL 檔案上建立和刪除 INDEX 命令。我們將在下一章詳細討論此命令。

MySQL - 索引

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

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

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

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

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

簡單索引和唯一索引

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

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

您可以使用一個或多個列來建立索引。

例如，我們可以使用tutorial_author 在tutorials_tbl 上建立索引。

CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)

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

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

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC)

使用 ALTER 命令新增和刪除索引

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

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) − 此語句新增PRIMARY KEY，這意味著索引值必須唯一且不能為 NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) − 此語句建立一個索引，其值必須唯一（NULL 值除外，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 testalter_tbl ADD INDEX (c);

您可以使用DROP 子句以及 ALTER 命令刪除任何索引。

嘗試以下示例以刪除上面建立的索引。

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

您可以使用 DROP 子句以及 ALTER 命令刪除任何索引。

使用 ALTER 命令新增和刪除主鍵

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

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

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

您可以按如下方式使用 ALTER 命令刪除主鍵：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

顯示索引資訊

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

嘗試以下示例：

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

MySQL - 臨時表

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

什麼是臨時表？

臨時表是在 MySQL 3.23 版本中新增的。如果您使用的 MySQL 版本早於 3.23，則無法使用臨時表，但可以使用堆表 (Heap Tables)。

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

示例

以下程式是一個示例，向您展示了臨時表的使用方法。可以使用mysql_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> 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)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist

MySQL - 克隆表

你可能需要一個表的精確副本，而CREATE TABLE ... SELECT語句不適合你的目的，因為副本必須包含相同的索引、預設值等等。

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

使用SHOW CREATE TABLE 獲取指定源表結構、索引等的 CREATE TABLE 語句。
修改該語句，將表名更改為克隆表的表名，然後執行該語句。這樣，你將擁有精確的克隆表。
可選地，如果你也需要複製表內容，則也要發出 INSERT INTO ... SELECT 語句。

示例

嘗試以下示例，為tutorials_tbl建立克隆表。

步驟 1 - 獲取有關表的完整結構。

mysql> SHOW CREATE TABLE tutorials_tbl \G;
*************************** 1. row ***************************
      Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
   `tutorial_id` int(11) NOT NULL auto_increment,
   `tutorial_title` varchar(100) NOT NULL default '',
   `tutorial_author` varchar(40) NOT NULL default '',
   `submission_date` date default NULL,
   PRIMARY KEY  (`tutorial_id`),
   UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE = MyISAM
1 row in set (0.00 sec)

ERROR:
No query specified

步驟 2 - 重新命名此表並建立另一個表。

mysql> CREATE TABLE clone_tbl (
   -> tutorial_id int(11) NOT NULL auto_increment,
   -> tutorial_title varchar(100) NOT NULL default '',
   -> tutorial_author varchar(40) NOT NULL default '',
   -> submission_date date default NULL,
   -> PRIMARY KEY  (tutorial_id),
   -> UNIQUE KEY AUTHOR_INDEX (tutorial_author)
-> ) TYPE = MyISAM;
Query OK, 0 rows affected (1.80 sec)

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

mysql> INSERT INTO clone_tbl (tutorial_id,
   -> tutorial_title,
   -> tutorial_author,
   -> submission_date)
   
   -> SELECT tutorial_id,tutorial_title,
   -> tutorial_author,submission_date
   -> FROM tutorials_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

最終，你將擁有你想要的精確克隆表。

MySQL - 資料庫資訊

獲取和使用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示例

<?php
   $con = mysql_connect("localhost", "userid", "password");
   
   if (!$con) {
      die('Could not connect: ' . mysql_error());
   }
   $db_list = mysql_list_dbs($con);

   while ($db = mysql_fetch_object($db_list)) {
      echo $db->Database . "<br />";
   }
   mysql_close($con);
?>

獲取伺服器元資料

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

序號	命令與描述
1	SELECT VERSION( ) 伺服器版本字串
2	SELECT DATABASE( ) 當前資料庫名稱（如果沒有則為空）
3	SELECT USER( ) 當前使用者名稱
4	SHOW STATUS 伺服器狀態指示器
5	SHOW VARIABLES 伺服器配置變數

使用MySQL序列

序列是一組按特定需求生成的整數1、2、3……序列在資料庫中經常使用，因為許多應用程式要求表中的每一行都包含唯一值，而序列提供了一種生成它們簡單的方法。

本章介紹如何在MySQL中使用序列。

使用AUTO_INCREMENT列

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

示例

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

mysql> CREATE TABLE insect
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
   -> (NULL,'housefly','2001-09-10','kitchen'),
   -> (NULL,'millipede','2001-09-10','driveway'),
   -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id |    name     |    date    |   origin   |
+----+-------------+------------+------------+
|  1 |  housefly   | 2001-09-10 |   kitchen  |
|  2 |  millipede  | 2001-09-10 |  driveway  |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

獲取AUTO_INCREMENT值

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

PERL示例

使用mysql_insertid屬性獲取查詢生成的AUTO_INCREMENT值。根據你發出查詢的方式，可以透過資料庫控制代碼或語句控制代碼訪問此屬性。

以下示例透過資料庫控制代碼引用它。

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

PHP示例

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

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

重新編號現有序列

你可能刪除了表中的許多記錄，並且想要重新排序所有記錄。這可以透過一個簡單的技巧來完成，但是如果你的表與其他表有連線，則應非常小心地執行此操作。

如果你確定必須重新排序AUTO_INCREMENT列，那麼方法是從表中刪除該列，然後重新新增它。

以下示例演示如何使用此技術重新編號表中的id值。

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

從特定值開始序列

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

下面的程式是一個示例，它顯示了MySQL如何從100開始序列。

mysql> CREATE TABLE insect
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);

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

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

MySQL - 處理重複資料

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

防止表中出現重複項

你可以使用具有適當欄位的表上的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值的多個記錄，請向其定義新增PRIMARY KEY。當你這樣做時，還需要宣告索引列為NOT NULL，因為PRIMARY KEY不允許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命令保留最後一組重複項並擦除任何較早的重複項。

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

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子句，透過要求組計數大於一來消除唯一值。

從查詢結果中消除重複項

你可以將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;

從表中刪除重複記錄的一種簡單方法是向該表新增INDEX或PRIMARY KEY。即使此表已經可用，你也可以使用此技術來刪除重複記錄，並且將來也能安全無虞。

mysql> ALTER IGNORE TABLE person_tbl
   -> ADD PRIMARY KEY (last_name, first_name);

MySQL和SQL注入

如果你透過網頁獲取使用者輸入並將其插入到MySQL資料庫中，則你可能讓自己容易受到稱為SQL注入的安全問題的影響。本章將教你如何幫助防止這種情況發生，並幫助你保護你的指令碼和MySQL語句。

SQL注入通常發生在你向用戶索取輸入（例如他們的姓名）時，他們提供的不是姓名，而是你將在不知情的情況下在資料庫上執行的MySQL語句。

永遠不要信任使用者提供的資料，只有在驗證後才能處理此資料；通常，這是透過模式匹配完成的。在下面的示例中，使用者名稱僅限於字母數字字元加下劃線，長度在8到20個字元之間 - 根據需要修改這些規則。

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) {
   $result = mysql_query("SELECT * FROM users WHERE username = $matches[0]");
} else  {
   echo "username not accepted";
}

為了演示這個問題，請考慮以下摘錄。

// supposed input
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name = '{$name}'");

函式呼叫應該從users表中檢索一條記錄，其中name列與使用者指定的name匹配。在正常情況下，$name只包含字母數字字元和空格。但是在這裡，透過將全新的查詢附加到$name，對資料庫的呼叫變成了災難。注入的DELETE查詢刪除了users中的所有記錄。

幸運的是，如果你使用MySQL，mysql_query()函式不允許查詢堆疊或在一個函式呼叫中執行多個查詢。如果你嘗試堆疊查詢，則呼叫會失敗。

但是，其他PHP資料庫擴充套件（例如SQLite和PostgreSQL）會愉快地執行堆疊查詢，執行在一個字串中提供的所有查詢，並建立一個嚴重的安全問題。

防止SQL注入

你可以在PERL和PHP等指令碼語言中巧妙地處理所有跳脫字元。PHP的MySQL擴充套件提供函式mysql_real_escape_string()來轉義對MySQL而言特殊的輸入字元。

if (get_magic_quotes_gpc()) {
   $name = stripslashes($name);
}

$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name = '{$name}'");

LIKE難題

為了解決LIKE難題，自定義轉義機制必須將使用者提供的%和_字元轉換為字面量。使用addcslashes()，這是一個允許你指定要轉義的字元範圍的函式。

$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == \%something\_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

MySQL - 資料庫匯出

將表資料匯出到文字檔案的簡單方法是使用SELECT...INTO OUTFILE語句，該語句將查詢結果直接匯出到伺服器主機上的檔案。

使用SELECT ... INTO OUTFILE語句匯出資料

此語句的語法將常規SELECT命令與INTO OUTFILE filename組合在末尾。預設輸出格式與LOAD DATA命令相同。因此，以下語句將tutorials_tbl表匯出到/tmp/tutorials.txt，作為一個製表符分隔、換行符終止的檔案。

mysql> SELECT * FROM tutorials_tbl 
   -> INTO OUTFILE '/tmp/tutorials.txt';

您可以使用各種選項更改輸出格式，以指示如何引用和分隔列和記錄。要以CRLF結尾的行匯出tutorial_tbl表為CSV格式，請使用以下程式碼。

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
   -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
   -> LINES TERMINATED BY '\r\n';

SELECT ... INTO OUTFILE語句具有以下特性：

輸出檔案由MySQL伺服器直接建立，因此檔名應指示您希望將檔案寫入伺服器主機上的哪個位置。沒有類似於LOAD DATA的LOCAL版本的語句。
您必須擁有MySQL FILE許可權才能執行SELECT ... INTO語句。
輸出檔案必須不存在。這可以防止MySQL覆蓋可能重要的檔案。
您應該在伺服器主機上擁有登入帳戶或某種方法可以從該主機檢索檔案。否則，SELECT ... INTO OUTFILE命令很可能對您毫無價值。
在UNIX下，建立的檔案是世界可讀的，並且屬於MySQL伺服器。這意味著雖然您可以讀取該檔案，但您可能無法刪除它。

將表匯出為原始資料

mysqldump程式用於複製或備份表和資料庫。它可以將表輸出寫入原始資料檔案或作為一組INSERT語句，這些語句可以重新建立表中的記錄。

要將表作為資料檔案轉儲，必須指定一個--tab選項，該選項指示MySQL伺服器要寫入檔案的目錄。

例如，要將TUTORIALS資料庫中的tutorials_tbl錶轉儲到/tmp目錄中的檔案，請使用如下所示的命令。

$ mysqldump -u root -p --no-create-info \
   --tab=/tmp tutorials tutorials_tbl
password ******

以SQL格式匯出表內容或定義

要以SQL格式將表匯出到檔案，請使用以下命令。

$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt
password ******

這將建立一個內容如下所示的檔案。

-- MySQL dump 8.23
--
-- Host: localhost    Database: TUTORIALS
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `tutorials_tbl`
--

CREATE TABLE tutorials_tbl (
   tutorial_id int(11) NOT NULL auto_increment,
   tutorial_title varchar(100) NOT NULL default '',
   tutorial_author varchar(40) NOT NULL default '',
   submission_date date default NULL,
   PRIMARY KEY  (tutorial_id),
   UNIQUE KEY AUTHOR_INDEX (tutorial_author)
) TYPE = MyISAM;

--
-- Dumping data for table `tutorials_tbl`
--

INSERT INTO tutorials_tbl 
   VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO tutorials_tbl 
   VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO tutorials_tbl 
   VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

要轉儲多個表，請在資料庫名稱引數後命名所有表。要轉儲整個資料庫，請不要在資料庫後命名任何表，如以下程式碼塊所示。

$ mysqldump -u root -p TUTORIALS > database_dump.txt
password ******

要備份主機上所有可用的資料庫，請使用以下程式碼。

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

MySQL 3.23.12版本中提供了--all-databases選項。此方法可用於實現資料庫備份策略。

將表或資料庫複製到另一個主機

如果要將表或資料庫從一臺MySQL伺服器複製到另一臺MySQL伺服器，請使用帶資料庫名稱和表名稱的mysqldump。

在源主機上執行以下命令。這會將完整的資料庫轉儲到dump.txt檔案中。

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

您可以像上面解釋的那樣，在不使用特定表名稱的情況下複製完整的資料庫。

現在，將dump.txt檔案ftp到另一臺主機，並使用以下命令。在執行此命令之前，請確保您已在目標伺服器上建立了database_name。

$ mysql -u root -p database_name < dump.txt
password *****

另一種無需使用中間檔案即可實現此目的的方法是，將mysqldump的輸出直接透過網路傳送到遠端MySQL伺服器。如果您能夠從源資料庫所在的伺服器主機連線到這兩臺伺服器，請使用以下命令（確保您具有這兩臺伺服器的訪問許可權）。

$ mysqldump -u root -p database_name \
   | mysql -h other-host.com database_name

在mysqldump中，命令的一半連線到本地伺服器並將轉儲輸出寫入管道。命令的另一半連線到other-host.com上的遠端MySQL伺服器。它讀取管道的輸入並將每個語句傳送到other-host.com伺服器。

MySQL - 資料庫匯入 - 恢復方法

在MySQL中，有兩種簡單的方法可以將資料從先前備份的檔案載入到MySQL資料庫中。

使用LOAD DATA匯入資料

MySQL提供了一個LOAD DATA語句，該語句充當批次資料載入器。這是一個示例語句，它從當前目錄讀取檔案dump.txt並將其載入到當前資料庫中的mytbl表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果不存在LOCAL關鍵字，MySQL將使用查詢絕對路徑名在伺服器主機上查詢資料檔案，該路徑名完全指定檔案的位置，從檔案系統的根目錄開始。MySQL從給定位置讀取檔案。
預設情況下，LOAD DATA假設資料檔案包含以換行符（換行符）結尾的行，並且一行中的資料值以製表符分隔。
要顯式指定檔案格式，請使用FIELDS子句來描述一行中欄位的特徵，並使用LINES子句來指定行結束序列。以下LOAD DATA語句指定資料檔案包含以冒號分隔的值，並且行以回車符和換行符結尾。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
   -> FIELDS TERMINATED BY ':'
   -> LINES TERMINATED BY '\r\n';

LOAD DATA命令假設資料檔案中的列順序與表中的列順序相同。如果並非如此，您可以指定一個列表來指示資料檔案列應載入到哪些表列中。假設您的表具有列a、b和c，但資料檔案中的後續列對應於列b、c和a。

您可以按以下程式碼塊所示載入檔案。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
   -> INTO TABLE mytbl (b, c, a);

使用mysqlimport匯入資料

MySQL還包含一個名為mysqlimport的實用程式，它充當LOAD DATA的包裝器，因此您可以直接從命令列載入輸入檔案。

要將資料從dump.txt載入到mytbl，請在UNIX提示符下使用以下命令。

$ mysqlimport -u root -p --local database_name dump.txt
password *****

如果您使用mysqlimport，命令列選項將提供格式說明符。對應於前面兩個LOAD DATA語句的mysqlimport命令如下所示。

$ mysqlimport -u root -p --local --fields-terminated-by = ":" \
   --lines-terminated-by = "\r\n"  database_name dump.txt
password *****

您指定選項的順序對於mysqlimport並不重要，但它們都應該位於資料庫名稱之前。

mysqlimport語句使用--columns選項來指定列順序：

$ mysqlimport -u root -p --local --columns=b,c,a \
   database_name dump.txt
password *****

處理引號和特殊字元

FIELDS子句可以指定除TERMINATED BY之外的其他格式選項。預設情況下，LOAD DATA假設值是不帶引號的，並將反斜槓（\）解釋為特殊字元的跳脫字元。要顯式指示值引號字元，請使用ENCLOSED BY命令。MySQL將在輸入處理過程中從資料值的末尾刪除該字元。要更改預設跳脫字元，請使用ESCAPED BY。

當您指定ENCLOSED BY 來指示應從資料值中刪除引號字元時，可以透過將其加倍或在其前面加上跳脫字元來在資料值中包含引號字元。

例如，如果引號和跳脫字元為“和\，則輸入值"a""b\"c"將被解釋為a"b"c。

對於mysqlimport，用於指定引號和轉義值的相應命令列選項為--fields-enclosed-by和--fields-escaped-by。

列印頁面

MySQL 快速指南

MySQL - 簡介

什麼是資料庫？

RDBMS 術語

MySQL 資料庫

開始之前

MySQL - 安裝

在 Linux/UNIX 上安裝 MySQL

在 Windows 上安裝 MySQL

驗證 MySQL 安裝

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

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

安裝後步驟

在啟動時執行 MySQL

MySQL - 管理

執行和關閉 MySQL 伺服器

設定 MySQL 使用者帳戶

/etc/my.cnf 檔案配置

MySQL 管理命令

MySQL - PHP 語法

MySQL - 連線

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

示例

使用 PHP 指令碼連線 MySQL

語法

語法

示例

MySQL - 建立資料庫

使用 mysqladmin 建立資料庫

示例

使用 PHP 指令碼建立資料庫

語法

示例

刪除 MySQL 資料庫

使用 mysqladmin 刪除資料庫

使用 PHP 指令碼刪除資料庫

語法

示例

選擇 MySQL 資料庫

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

示例

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

語法

示例

MySQL - 資料型別

數值資料型別

日期和時間型別

字串型別

建立 MySQL 表

語法

從命令提示符建立表

示例

使用 PHP 指令碼建立表

示例

刪除 MySQL 表

語法

從命令提示符刪除表

示例

使用 PHP 指令碼刪除表

示例

MySQL - INSERT 查詢

語法

從命令提示符插入資料

示例

使用PHP指令碼插入資料

示例

MySQL - SELECT 查詢

語法

從命令提示符提取資料

示例

使用PHP指令碼提取資料

示例

示例

示例

釋放記憶體

示例

MySQL - WHERE子句

語法

從命令提示符提取資料

示例