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);
打印页面

上一页
下一页
广告
© . All rights reserved.