MySQLi – 快速指南
MySQLi – 快速指南
MySQLi – 介绍
MySQLi 是 PHP 中可用的 MySQL API 的扩展，从 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。如果你对 MySQL 和 PERL 感兴趣，那么你可以考虑阅读PERL教程。
PHP 提供了各种函数来访问 MySQL 数据库和操作 MySQL 数据库内部的数据记录。您需要以与调用任何其他 PHP 函数相同的方式调用 PHP 函数。
用于 MySQL 的 PHP 函数具有以下通用格式 –
mysqli function(value,value,...);
函数名的第二部分是特定于函数的，通常是描述函数做什么的词。以下是我们将在教程中使用的两个函数 –
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); mysqli→query(,"SQL statement");
以下示例显示了调用任何 MySQL 函数的 PHP 通用语法。
<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);
| Sr.No. | 参数及说明 |
|---|---|
| 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)
| Sr.No. | 参数及说明 |
|---|---|
| 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)
| Sr.No. | 参数及说明 |
|---|---|
| 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 命令用于选择数据库。
例子
这是一个选择名为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
| Sr.No. | 参数及说明 |
|---|---|
| 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)
| Sr.No. | 参数及说明 |
|---|---|
| 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)
| Sr.No. | 参数及说明 |
|---|---|
| 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 表，您需要使用 SQL INSERT INTO命令。您可以使用 mysql> 提示符或使用任何脚本（如 PHP）将数据插入到 MySQL 表中。
句法
这是将数据插入 MySQL 表的 INSERT INTO 命令的通用 SQL 语法 –
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
要插入字符串数据类型，需要将所有值保留在双引号或单引号中。例如“价值”。
从命令提示符插入数据
要从命令提示符插入数据，我们将使用 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 提示自动创建的，同时按下 Enter 键，在命令的每一行末尾没有分号。
在上面的例子中，我们没有提供tutorial_id，因为在创建表时，我们已经为这个字段提供了 AUTO_INCREMENT 选项。因此 MySQL 会自动插入这些 ID。这里，NOW()是一个 MySQL 函数，它返回当前日期和时间。
使用 PHP 脚本插入数据
PHP 使用mysqli query()或mysql_query()函数将记录插入到 MySQL 表中。此函数采用两个参数，成功时返回 TRUE，失败时返回 FALSE。
句法
$mysqli→query($sql,$resultmode)
| Sr.No. | 参数及说明 |
|---|---|
| 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 网络服务器上的 mysql_example.php，输入详细信息并验证提交表单时的输出。
Record inserted successfully.
在进行数据插入时，最好使用函数get_magic_quotes_gpc()来检查是否设置了魔术引号的当前配置。如果此函数返回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)
| Sr.No. | 参数及说明 |
|---|---|
| 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 并验证输出。在这里，我们在运行选择脚本之前在表中输入了多条记录。
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 子句
我们已经看到了从 MySQL 表中获取数据的 SQL SELECT命令。我们可以使用名为WHERE子句的条件子句来过滤结果。使用这个 WHERE 子句，我们可以指定一个选择标准来从表中选择所需的记录。
句法
以下代码块具有 SELECT 命令的通用 SQL 语法，带有 WHERE 子句以从 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，然后 –
| Operator | 描述 | 例子 |
|---|---|---|
| = | 检查两个操作数的值是否相等，如果相等，则条件成立。 | (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)
| Sr.No. | 参数及说明 |
|---|---|
| 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 并验证输出。在这里，我们在运行选择脚本之前在表中输入了多条记录。
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)
| Sr.No. | 参数及说明 |
|---|---|
| 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 并验证输出。在这里，我们在运行选择脚本之前在表中输入了多条记录。
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)
| Sr.No. | 参数及说明 |
|---|---|
| 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 并验证输出。在这里，我们在运行选择脚本之前在表中输入了多条记录。
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 子句
我们已经看到了从 MySQL 表中获取数据的 SQL SELECT命令。我们还可以使用称为WHERE子句的条件子句来选择所需的记录。
带有“等于”符号 (=) 的 WHERE 子句适用于我们想要进行精确匹配的情况。就像“tutorial_author = ‘Sanjay’”一样。但是可能需要过滤掉所有tutorial_author 名称应包含“jay”的结果。这可以使用SQL LIKE 子句和 WHERE 子句来处理。
如果 SQL LIKE 子句与 % 字符一起使用，那么它将像 UNIX 中的元字符 (*) 一样工作，同时在命令提示符下列出所有文件或目录。如果没有 % 字符，LIKE 子句与等号以及 WHERE 子句非常相似。
句法
以下代码块具有 SELECT 命令的通用 SQL 语法以及用于从 MySQL 表中获取数据的 LIKE 子句。
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)
| Sr.No. | 参数及说明 |
|---|---|
| 1 |
$sql 必需 – SQL 查询以使用 Like 子句选择 MySQL 表中的记录。 |
| 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 并验证输出。在这里，我们在运行选择脚本之前在表中输入了多条记录。
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 命令的通用 SQL 语法以及 ORDER BY 子句，用于对 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)
| Sr.No. | 参数及说明 |
|---|---|
| 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 语句中使用 JOINS 来连接 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)
| Sr.No. | 参数及说明 |
|---|---|
| 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);
例子
尝试以下示例以使用 Join 从两个表中获取记录。–
将以下示例复制并粘贴为 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 左连接
MySQL 左连接与简单连接不同。MySQL LEFT JOIN 对左侧的表给予了一些额外的考虑。
如果我执行LEFT JOIN，我会以相同的方式获得所有匹配的记录，此外，我会为连接的左表中的每个未匹配记录获得一条额外记录：从而确保（在我的示例中）每个 AUTHOR 获得一个提到。
例子
尝试以下示例以了解 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)
您需要做更多的练习才能熟悉 JOINS。这是 MySQL/SQL 中稍微有点复杂的概念，在做实际示例时会变得更加清晰。
MySQLi – 处理 NULL 值
我们已经看到 SQL SELECT命令和WHERE子句从 MySQL 表中获取数据，但是当我们尝试给出一个条件，将字段或列值与NULL进行比较时，它无法正常工作。
为了处理这种情况，MySQL 提供了三个运算符 –
-
IS NULL – 如果列值为 NULL，则此运算符返回 true。
-
IS NOT NULL – 如果列值不为 NULL，则此运算符返回 true。
-
<=> – 此运算符比较值，即使对于两个 NULL 值也是如此（与 = 运算符不同）。
涉及 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");
列出表和数据库
列出数据库服务器可用的所有数据库和表非常容易。如果您没有足够的权限，您的结果可能为空。
除了以下代码块中显示的方法外，您还可以使用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）来获取有关数据库服务器的各种重要信息。
| Sr.No. | 命令和描述 |
|---|---|
| 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 下载。选择您想要的MySQL 社区服务器的最新版本号，并尽可能准确地选择您想要的平台。
在 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
-
这是可选但推荐的步骤，以相同的方式安装剩余的 RPM –
以上命令负责安装 MySQL 服务器，创建 MySQL 用户，创建必要的配置并自动启动 MySQL 服务器。
您可以在 /usr/bin 和 /usr/sbin 中找到所有与 MySQL 相关的二进制文件。所有的表和数据库都将在 /var/lib/mysql 目录中创建。
[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 二进制文件。
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 中的用户表中添加一个新条目。
下面的程序是添加一个具有SELECT、INSERT和UPDATE权限的新用户guest，密码为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 查询更新它们。
- 选择_priv
- 插入_priv
- 更新_priv
- 删除_priv
- 创建_priv
- drop_priv
- 重新加载_priv
- Shutdown_priv
- process_priv
- 文件_priv
- Grant_priv
- References_priv
- 索引_priv
- 改变_priv
添加用户帐户的另一种方法是使用 GRANT SQL 命令。以下示例将为名为TUTORIALS的特定数据库添加密码为zara123 的用户zara。
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的条目。
注意– MySQL 不会终止命令，直到您在 SQL 命令的末尾给出一个分号 (;)。
/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 – 正则表达式
您已经看到 MySQL 模式与LIKE …%匹配。MySQL 支持另一种基于正则表达式和REGEXP运算符的模式匹配操作。如果您了解 PHP 或 PERL，那么您很容易理解，因为这种匹配与那些脚本正则表达式非常相似。
以下是模式表，可与REGEXP运算符一起使用。
| Pattern | 模式匹配什么 |
|---|---|
| ^ | 字符串的开头 |
| $ | 字符串结束 |
| . | 任意单个字符 |
| […] | 方括号之间列出的任何字符 |
| [^…] | 方括号之间未列出的任何字符 |
| 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 语句结束。开始和结束语句之间的 SQLi 命令构成了事务的主体。
提交和回滚
这两个关键字Commit和Rollback主要用于 MySQL Transactions。
-
成功完成事务后，应发出 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。
从源代码编译 MySQL 时，对 InnoDB 表的支持需要特定的编译参数。如果您的 MySQL 版本不支持 InnoDB，请要求您的 Internet 服务提供商构建一个支持 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 将包含与您第一次创建表时相同的两列，但不会具有完全相同的结构。这是因为新列默认添加到表的末尾。所以即使我最初是 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 文件上创建和删除 INDEX。我们将在下一章中看到这个特性。
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);
添加和删除 INDEX 的 ALTER 命令
有四种类型的语句用于向表添加索引 –
-
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) – 该语句添加了一个 PRIMARY KEY，这意味着索引值必须是唯一的，不能为 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 命令删除任何 INDEX。试试下面的例子来删除上面创建的索引。
mysql> ALTER TABLE tutorials_inf DROP INDEX (c);
您可以使用 DROP 子句和 ALTER 命令删除任何 INDEX。试试下面的例子来删除上面创建的索引。
添加和删除 PRIMARY KEY 的 ALTER 命令
您也可以以相同的方式添加主键。但请确保主键适用于非空的列。
这是在现有表中添加主键的示例。这将首先使列 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;
要删除不是 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 中使用 Sequences 的最简单方法是将列定义为 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 列重新排序是不可避免的，那么这样做的方法是从表中删除该列，然后再次添加它。以下示例显示如何使用此技术对昆虫表中的 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) );
要防止在此表中创建多个具有相同名字和姓氏值的记录，请将 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 子句，该子句通过要求组计数大于 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;
从表中删除重复记录的一种简单方法是向该表添加 INDEX 或 PRIMAY KEY。即使此表已经可用，您也可以使用此技术删除重复记录，并且将来也很安全。
mysql> ALTER IGNORE TABLE person_tbl → ADD PRIMARY KEY (last_name, first_name);
