MySQL - 快速指南
MySQL - 简介
什么是数据库?
数据库是一个独立的应用程序，用于存储数据集合。每个数据库都有一个或多个不同的 API，用于创建、访问、管理、搜索和复制其存储的数据。
也可以使用其他类型的数据存储，例如文件系统上的文件或内存中的大型哈希表，但使用这些类型的系统，数据读取和写入速度会不那么快，也不太容易。
如今，我们使用关系数据库管理系统 (RDBMS) 来存储和管理海量数据。之所以称为关系数据库，是因为所有数据都存储在不同的表中，并且使用主键或其他称为外键的键建立关系。
关系数据库管理系统 (RDBMS) 是一种软件，它能够:-
使您能够实现包含表、列和索引的数据库。
保证各个表的行之间的引用完整性。
自动更新索引。
解释 SQL 查询并组合来自各个表的信息。
RDBMS 术语
在继续解释 MySQL 数据库系统之前，让我们先复习一下与数据库相关的一些定义。
数据库 − 数据库是包含相关数据的表的集合。
表格 − 表格是包含数据的矩阵。数据库中的表格看起来像一个简单的电子表格。
列 − 一列(数据元素)包含同一种类型的数据，例如该列的邮政编码。
行 − 一行(= 元组、条目或记录)是一组相关数据，例如一个订阅的数据。
冗余 − 将数据冗余存储两次，以提高系统速度。
主键 − 主键是唯一的。一个键值不能在一个表中出现两次。使用一个键，您只能找到一行。
外键 − 外键是两个表之间的连接点。
复合键 − 复合键是由多个列组成的键，因为一个列不够唯一。
索引 − 数据库中的索引类似于书末的索引。
参照完整性 − 参照完整性确保外键值始终指向现有行。
MySQL 数据库
MySQL 是一款快速、易于使用的关系型数据库管理系统 (RDBMS)，广泛应用于众多小型企业。MySQL 由瑞典公司 MySQL AB 开发、营销和支持。MySQL 之所以如此受欢迎，有很多原因 -
MySQL 采用开源许可证发布。因此您无需付费即可使用。
MySQL 本身就是一款非常强大的程序。它能够处理大多数昂贵且功能强大的数据库软件包的大部分功能。
MySQL 使用众所周知的 SQL 数据语言的标准形式。
MySQL 可在多种操作系统上运行，并支持多种语言，包括 PHP、PERL、C、C++、JAVA 等。
MySQL 运行速度非常快，即使处理大型数据集也能表现出色。
MySQL 与 PHP 非常友好，而 PHP 是最受欢迎的 Web 开发语言。
MySQL 支持大型数据库，单表最多可容纳 5000 万行或更多行。表的默认文件大小限制为 4GB，但您可以将其增加(如果您的操作系统可以处理)到 800 万 TB 的理论上限。
MySQL 是可定制的。开源 GPL 许可证允许程序员修改 MySQL 软件以适应他们自己的特定环境。
开始之前
在开始本教程之前，您应该对我们的 PHP 和 HTML 教程中涵盖的信息有基本的了解。
本教程主要侧重于在 PHP 环境中使用 MySQL。本教程中提供的许多示例对 PHP 程序员来说非常有用。
我们建议您查看我们的 PHP 教程 以供参考。
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
上述命令负责安装 MySQL 服务器、创建 MySQL 用户、创建必要的配置并自动启动 MySQL 服务器。
您可以在 /usr/bin 和 /usr/sbin 中找到所有与 MySQL 相关的二进制文件。所有表和数据库都将在 /var/lib/mysql 目录中创建。
以下代码框包含一个可选但推荐的步骤，以相同的方式安装其余的 RPM 包 -
[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm [root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm [root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm [root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm
在 Windows 上安装 MySQL
现在，任何版本的 Windows 上的默认安装都比以前容易得多，因为 MySQL 现在已集成到安装程序中。只需下载安装程序包，将其解压到任意位置并运行 setup.exe 文件即可。
默认安装程序 setup.exe 将引导您完成简单的安装过程，默认情况下，所有安装程序都会安装在 C:\mysql 目录下。
首次启动服务器时，请先从命令提示符启动服务器进行测试。转到 mysqld 服务器 的位置(可能是 C:\mysql\bin)，然后输入 -
mysqld.exe --console
注意 - 如果您使用的是 NT 系统，则必须使用 mysqld-nt.exe 而不是 mysqld.exe
如果一切顺利，您将看到一些关于启动和 InnoDB 的消息。如果没有看到，则可能是权限问题。确保数据库进程运行的任何用户(可能是 MySQL)都可以访问保存数据的目录。
MySQL 不会自动添加到开始菜单中，也没有特别好用的 GUI 方法来停止服务器。因此，如果您倾向于通过双击 mysqld 可执行文件来启动服务器，请记住使用 mysqladmin、任务列表、任务管理器或其他 Windows 专用工具手动停止该进程。
验证 MySQL 安装
MySQL 成功安装后，基表已初始化，服务器已启动:您可以通过一些简单的测试来验证一切是否正常工作。
使用 mysqladmin 实用程序获取服务器状态
使用 mysqladmin 二进制文件检查服务器版本。此二进制文件在 Linux 上位于 /usr/bin，在 Windows 上位于 C:\mysql\bin。
[root@host]# mysqladmin --version
在 Linux 上，它将产生以下结果。具体结果可能因您的安装而异 -
mysqladmin Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386
如果您没有收到这样的消息，则您的安装可能存在问题，您需要一些帮助来修复它。
使用 MySQL 客户端执行简单的 SQL 命令
您可以通过 MySQL 客户端并使用 mysql 命令连接到 MySQL 服务器。此时，您无需输入任何密码，因为默认情况下密码为空。
您只需使用以下命令即可 -
[root@host]# mysql
此时应出现 mysql> 提示符。现在，您已连接到 MySQL 服务器，您可以在 mysql> 提示符下执行所有 SQL 命令，如下所示 -
mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | +----------+ 2 rows in set (0.13 sec)
安装后步骤
MySQL 默认为 root 用户设置了一个空密码。成功安装数据库和客户端后，您需要按照以下代码块中的说明设置 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启动
此外，你的 /etc/init.d/ 目录中应该有 mysqld 二进制文件。
MySQL - 管理
运行和关闭 MySQL 服务器
首先检查你的 MySQL 服务器是否正在运行。你可以使用以下命令进行检查 -
ps -ef | grep mysqld
如果你的 MySQL 正在运行，你将在结果中看到 mysqld 进程。如果服务器未运行，你可以使用以下命令启动它 -
root@host# cd /usr/bin ./safe_mysqld &
现在，如果你想关闭一个正在运行的 MySQL 服务器，你可以使用以下命令 -
root@host# cd /usr/bin ./mysqladmin -u root -p shutdown Enter password: ******
设置 MySQL 用户帐户
要向 MySQL 添加新用户，您只需在数据库 mysql 中的 user 表中添加一条新条目即可。
以下程序示例添加了一个新用户 guest，该用户拥有 SELECT、INSERT 和 UPDATE 权限，密码为 guest123;SQL 查询语句为 -
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed
mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host | user | password |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)
添加新用户时，请务必使用 MySQL 提供的 PASSWORD() 函数加密新密码。如上例所示，密码 mypass 被加密为 6f8c114b58f2ce9e。
请注意 FLUSH PRIVILEGES 语句。该语句指示服务器重新加载授权表。如果不使用该语句，则至少在服务器重启之前，您将无法使用新用户帐户连接到 MySQL。
您还可以通过在执行 INSERT 查询时将用户表中以下各列的值设置为"Y"来为新用户指定其他权限，或者您可以稍后使用 UPDATE 查询更新这些权限。
- Select_priv
- Insert_priv
- Update_priv
- Delete_priv
- Create_priv
- Drop_priv
- Reload_priv
- Shutdown_priv
- Process_priv
- File_priv
- Grant_priv
- References_priv
- Index_priv
- Alter_priv
添加用户帐户的另一种方法是使用 GRANT SQL 命令。以下示例将为特定数据库(名为 TUTORIALS)添加用户 zara 和密码 zara123。
root@host# mysql -u root -p password; Enter password:******* mysql> use mysql; Database changed mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP -> ON TUTORIALS.* -> TO 'zara'@'localhost' -> IDENTIFIED BY 'zara123';
这还会在 MySQL 数据库表中创建一个名为 user 的条目。
注意 − 除非您在 SQL 命令末尾添加分号 (;)，否则 MySQL 不会终止命令。
/etc/my.cnf 文件配置
大多数情况下，您无需修改此文件。默认情况下，该文件包含以下条目 −
[mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql8/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 − 显示表中所有索引的详细信息，包括主键。
SHOW TABLE STATUS LIKE tablename\G − 报告 MySQL DBMS 性能和统计信息的详细信息。
下一章，我们将讨论如何在 MySQL 中使用 PHP 语法。
MySQL - PHP 语法
MySQL 可以与 PERL、C、C++、JAVA 和 PHP 等各种编程语言完美结合使用。在这些语言中，PHP 因其强大的 Web 应用程序开发功能而成为最受欢迎的语言。
本教程重点介绍如何在 PHP 环境中使用 MySQL。如果您对使用 PERL 访问 MySQL 感兴趣，可以考虑阅读 PERL 教程。
PHP 提供了各种函数来访问 MySQL 数据库并操作其中的数据记录。您需要像调用其他 PHP 函数一样调用这些 PHP 函数。
用于 MySQL 的 PHP 函数具有以下通用格式 -
mysql_function(value,value,...);
函数名称的第二部分特定于函数，通常是一个描述函数功能的单词。以下是我们将在本教程中使用的两个函数 -
mysqli_connect($connect); mysqli_query($connect,"SQL 语句");
以下示例展示了 PHP 调用任何 MySQL 函数的通用语法。
<html>
<head>
<title>PHP with MySQL</title>
</head>
<body>
<?php
$retval = mysql_function(value, [value,...]);
if( !$retval ) {
die ( "Error: a related error message" );
}
// Otherwise MySQL or PHP Statements
?>
</body>
</html>
从下一章开始，我们将学习所有重要的 MySQL 功能以及 PHP。
MySQL - 连接
使用 MySQL 二进制文件建立 MySQL 连接
您可以在命令提示符下使用 mysql 二进制文件建立 MySQL 数据库。
示例
这是一个从命令提示符连接到 MySQL 服务器的简单示例 -
[root@host]# mysql -u root -p Enter password:******
这将打开 mysql> 命令提示符，您可以在其中执行任何 SQL 命令。以下是上述命令的执行结果 -
以下代码块显示了上述代码的执行结果 -
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2854760 to server version: 5.0.9 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
在上面的例子中，我们使用了 root 作为用户，但您也可以使用任何其他用户。任何用户都可以执行允许该用户执行的所有 SQL 操作。
您可以随时在 mysql> 提示符下使用 exit 命令断开与 MySQL 数据库的连接。
mysql> exit Bye
使用 PHP 脚本连接 MySQL
PHP 提供了 mysql_connect() 函数来打开数据库连接。此函数接受五个参数，成功时返回 MySQL 链接标识符，失败时返回 FALSE。
语法
connection mysql_connect(server,user,passwd,new_link,client_flag);
| Sr.No. | 参数和说明 |
|---|---|
| 1 |
server 可选 - 运行数据库服务器的主机名。如果未指定，则默认值为 localhost:3306。 |
| 2 |
user 可选 - 访问数据库的用户名。如果未指定，则默认为拥有服务器进程的用户的名称。 |
| 3 |
passwd 可选 - 访问数据库的用户的密码。如果未指定，则默认密码为空。 |
| 4 |
new_link 可选 - 如果使用相同参数再次调用 mysql_connect()，则不会建立新的连接;而是返回已打开连接的标识符。 |
| 5 |
client_flags 可选 - 以下常量的组合 -
|
您可以随时使用其他 PHP 函数 mysql_close() 断开与 MySQL 数据库的连接。此函数接受一个参数，即 mysql_connect() 函数返回的连接。
语法
bool mysql_close ( resource $link_identifier );
如果未指定资源，则关闭上次打开的数据库。如果成功关闭连接，此函数返回 true，否则返回 false。
示例
尝试以下示例连接到 MySQL 服务器 -
<html>
<head>
<title>Connecting MySQL Server</title>
</head>
<body>
<?php
$dbhost = 'localhost:3306';
$dbuser = 'guest';
$dbpass = 'guest123';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($conn);
?>
</body>
</html>
MySQL - 创建数据库
使用 mysqladmin 创建数据库
您需要特殊权限才能创建或删除 MySQL 数据库。因此，假设您拥有 root 用户权限，则可以使用 mysql mysqladmin 二进制文件创建任何数据库。
示例
以下是创建名为 TUTORIALS 的数据库的简单示例 -
[root@host]# mysqladmin -u root -p create TUTORIALS Enter password:******
这将创建一个名为 TUTORIALS 的 MySQL 数据库。
使用 PHP 脚本创建数据库
PHP 使用 mysql_query 函数来创建或删除 MySQL 数据库。此函数接受两个参数，成功时返回 TRUE，失败时返回 FALSE。
语法
bool mysql_query( sql, connection );
| Sr.No. | 参数和说明 |
|---|---|
| 1 |
sql 必需 - 用于创建或删除 MySQL 数据库的 SQL 查询 |
| 2 |
connection 可选 - 如果未指定，则将使用 mysql_connect 最后打开的连接。 |
示例
以下示例用于创建数据库 -
<html>
<head>
<title>Creating MySQL Database</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = 'CREATE DATABASE TUTORIALS';
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not create database: ' . mysql_error());
}
echo "Database TUTORIALS created successfully
";
mysql_close($conn);
?>
</body>
</html>
删除 MySQL 数据库
使用 mysqladmin 删除数据库
您需要特殊权限才能创建或删除 MySQL 数据库。因此，假设您拥有 root 用户权限，则可以使用 mysql mysqladmin 二进制文件创建任何数据库。
删除任何数据库时请务必小心，因为您将丢失数据库中的所有可用数据。
以下是删除上一章中创建的数据库(教程)的示例 -
[root@host]# mysqladmin -u root -p drop TUTORIALS Enter password:******
这将向您发出警告，并确认您是否真的要删除该数据库。
Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the 'TUTORIALS' database [y/N] y Database "TUTORIALS" dropped
使用 PHP 脚本删除数据库
PHP 使用 mysql_query 函数创建或删除 MySQL 数据库。此函数接受两个参数，成功时返回 TRUE，失败时返回 FALSE。
语法
bool mysql_query( sql, connection );
| Sr.No | 参数和说明 |
|---|---|
| 1 |
sql 必需 - 用于创建或删除 MySQL 数据库的 SQL 查询 |
| 2 |
connection 可选 - 如果未指定，则将使用 mysql_connect 最后打开的连接。 |
示例
尝试以下示例删除数据库 -
<html>
<head>
<title>Deleting MySQL Database</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = 'DROP DATABASE TUTORIALS';
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not delete database: ' . mysql_error());
}
echo "Database TUTORIALS deleted successfully
";
mysql_close($conn);
?>
</body>
</html>
警告 − 使用 PHP 脚本删除数据库时，不会提示您进行任何确认。因此，删除 MySQL 数据库时请务必小心。
选择 MySQL 数据库
连接到 MySQL 服务器后，需要选择要使用的数据库。这是因为 MySQL 服务器可能有多个可用的数据库。
从命令提示符中选择 MySQL 数据库
从 mysql> 提示符中选择数据库非常简单。您可以使用 SQL 命令 use 来选择数据库。
示例
以下示例显示了如何选择名为 TUTORIALS 的数据库 −
[root@host]# mysql -u root -p Enter password:****** mysql> use TUTORIALS; Database changed mysql>
现在，您已选择 TUTORIALS 数据库，所有后续操作都将在 TUTORIALS 数据库上执行。
注意 − 所有数据库名称、表名称和表字段名称均区分大小写。因此，在输入任何 SQL 命令时，您必须使用正确的名称。
使用 PHP 脚本选择 MySQL 数据库
PHP 提供了函数 mysql_select_db 来选择数据库。该函数在成功时返回 TRUE，在失败时返回 FALSE。
语法
bool mysql_select_db( db_name, connection );
| 序号 | 参数 &描述 |
|---|---|
| 1 |
db_name 必填 - 待选择的 MySQL 数据库名称 |
| 2 |
connection 可选 - 如果未指定，则将使用 mysql_connect 最后打开的连接。 |
示例
以下示例向您展示如何选择数据库。
<html>
<head>
<title>Selecting MySQL Database</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'guest';
$dbpass = 'guest123';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_select_db( 'TUTORIALS' );
mysql_close($conn);
?>
</body>
</html>
MySQL - 数据类型
正确定义表中的字段对于数据库的整体优化至关重要。您应该只使用真正需要的字段类型和大小。例如，如果您知道只使用 2 个字符，就不要定义 10 个字符宽的字段。这些类型的字段(或列)也称为数据类型，以您将存储在这些字段中的数据类型命名。
MySQL 使用许多不同的数据类型，分为三类 -
- 数字
- 日期和时间
- 字符串类型
现在让我们详细讨论一下它们。
数字数据类型
MySQL 使用所有标准的 ANSI SQL 数字数据类型，因此如果您从其他数据库系统转到 MySQL，这些定义对您来说应该很熟悉。
以下列表显示了常见的数字数据类型及其描述 -
INT - 一个正常大小的整数，可以是有符号的，也可以是无符号的。如果有符号，允许范围为 -2147483648 到 2147483647。如果无符号，允许范围为 0 到 4294967295。您最多可以指定 11 位数字的宽度。
TINYINT − 一个非常小的整数，可以有符号或无符号。如果有符号，允许范围为 -128 到 127。如果无符号，允许范围为 0 到 255。您最多可以指定 4 位数字的宽度。
SMALLINT − 一个可以有符号或无符号的小整数。如果有符号，允许范围为 -32768 至 32767。如果无符号，允许范围为 0 至 65535。您最多可以指定 5 位数字的宽度。
MEDIUMINT − 中等大小的整数，可以有符号或无符号。如果有符号，允许范围为 -8388608 至 8388607。如果无符号，允许范围为 0 至 16777215。您最多可以指定 9 位数字的宽度。
BIGINT − 大整数，可以有符号或无符号。如果有符号，允许范围为 -9223372036854775808 至 9223372036854775807。如果没有符号，允许范围为 0 至 18446744073709551615。您可以指定最多 20 位数字的宽度。
FLOAT(M,D) − 不能为无符号的浮点数。您可以定义显示长度 (M) 和小数位数 (D)。这不是必需的，默认为 10,2，其中 2 表示小数位数，10 表示总位数(包括小数)。对于 FLOAT，小数精度可达 24 位。
DOUBLE(M,D) − 双精度浮点数，不能为无符号数。您可以定义显示长度 (M) 和小数位数 (D)。这不是必需的，默认为 16.4，其中 4 为小数位数。对于 DOUBLE，小数精度可达 53 位。REAL 是 DOUBLE 的同义词。
DECIMAL(M,D) − 非压缩浮点数，不能为无符号数。在非压缩小数中，每个小数对应一个字节。必须定义显示长度 (M) 和小数位数 (D)。NUMERIC 是 DECIMAL 的同义词。
日期和时间类型
MySQL 日期和时间数据类型如下:
DATE - YYYY-MM-DD 格式的日期，介于 1000-01-01 和 9999-12-31 之间。例如，1973 年 12 月 30 日将存储为 1973-12-30。
DATETIME - YYYY-MM-DD HH:MM:SS 格式的日期和时间组合，介于 1000-01-01 00:00:00 和 9999-12-31 23:59:59 之间。例如，1973 年 12 月 30 日下午 3:30 将存储为 1973-12-30 15:30:00。
TIMESTAMP − 1970 年 1 月 1 日午夜至 2037 年某个时间之间的时间戳。这看起来像以前的 DATETIME 格式，只是没有数字之间的连字符; 1973 年 12 月 30 日下午 3:30 将被存储为 19731230153000 ( YYYYMMDDHHMMSS )。
TIME − 以 HH:MM:SS 格式存储时间。
YEAR(M) − 以 2 位或 4 位格式存储年份。如果长度指定为 2(例如 YEAR(2))，则 YEAR 可以介于 1970 至 2069(70 至 69)之间。如果长度指定为 4，则 YEAR 可以是 1901 到 2155。默认长度为 4。
字符串类型
虽然数字和日期类型很有趣，但您存储的大多数数据都将采用字符串格式。此列表介绍了 MySQL 中常见的字符串数据类型。
CHAR(M) − 长度在 1 到 255 个字符之间的固定长度字符串(例如 CHAR(5))，存储时会在右侧用空格填充至指定长度。无需定义长度，但默认值为 1。
VARCHAR(M) − 长度在 1 到 255 个字符之间的可变长度字符串。例如，VARCHAR(25)。创建 VARCHAR 字段时，必须定义长度。
BLOB 或 TEXT − 最大长度为 65535 个字符的字段。BLOB 是"二进制大对象"，用于存储大量二进制数据，例如图像或其他类型的文件。定义为 TEXT 的字段也可以存储大量数据。两者的区别在于，BLOB 存储数据的排序和比较区分大小写，而 TEXT 字段不区分大小写。BLOB 或 TEXT 字段无需指定长度。
TINYBLOB 或 TINYTEXT − BLOB 或 TEXT 列的最大长度为 255 个字符。您未使用 TINYBLOB 或 TINYTEXT 指定长度。
MEDIUMBLOB 或 MEDIUMTEXT − BLOB 或 TEXT 列的最大长度为 16777215 个字符。您未使用 MEDIUMBLOB 或 MEDIUMTEXT 指定长度。
LONGBLOB 或 LONGTEXT − BLOB 或 TEXT 列的最大长度为 4294967295 个字符。您未使用 LONGBLOB 或 LONGTEXT 指定长度。
ENUM − 枚举，是列表的别称。定义 ENUM 时，您将创建一个项目列表，必须从中选择值(否则可以为 NULL)。例如，如果您希望字段包含"A"或"B"或"C"，则可以将 ENUM 定义为 ENUM ('A', 'B', 'C')，并且只有这些值(或 NULL)才能填充该字段。
在下一章中，我们将讨论如何在 MySQL 中创建表。
创建 MySQL 表
首先，表创建命令需要以下详细信息 -
- 表名称
- 字段名称
- 每个字段的定义
语法
以下是创建 MySQL 表的通用 SQL 语法 -
CREATE TABLE table_name (column_name column_type);
现在，我们将在 TUTORIALS 数据库中创建下表。
create table tutorials_tbl( tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE, PRIMARY KEY ( tutorial_id ) );
这里需要解释几点 −
使用字段属性 NOT NULL 是因为我们不希望此字段为 NULL。因此，如果用户尝试创建包含 NULL 值的记录，MySQL 将抛出错误。
字段属性 AUTO_INCREMENT 指示 MySQL 继续并将下一个可用数字添加到 id 字段。
关键字 PRIMARY KEY 用于将列定义为主键。您可以使用逗号分隔的多个列来定义主键。
从命令提示符创建表
从 mysql> 提示符创建 MySQL 表非常简单。您将使用 SQL 命令 CREATE TABLE 来创建表。
示例
以下示例将创建 tutorials_tbl −
root@host# mysql -u root -p Enter password:******* mysql> use TUTORIALS; Database changed mysql> CREATE TABLE tutorials_tbl( -> tutorial_id INT NOT NULL AUTO_INCREMENT, -> tutorial_title VARCHAR(100) NOT NULL, -> tutorial_author VARCHAR(40) NOT NULL, -> submission_date DATE, -> PRIMARY KEY ( tutorial_id ) -> ); Query OK, 0 rows affected (0.16 sec) mysql>
注意 - 除非您在 SQL 命令末尾添加分号 (;)，否则 MySQL 不会终止命令。
使用 PHP 脚本创建表
要在任何现有数据库中创建新表，您需要使用 PHP 函数 mysql_query()。您需要将其第二个参数与正确的 SQL 命令一起传递才能创建表。
示例
以下程序是使用 PHP 脚本创建表的示例 -
<html>
<head>
<title>Creating MySQL Tables</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = "CREATE TABLE tutorials_tbl( ".
"tutorial_id INT NOT NULL AUTO_INCREMENT, ".
"tutorial_title VARCHAR(100) NOT NULL, ".
"tutorial_author VARCHAR(40) NOT NULL, ".
"submission_date DATE, ".
"PRIMARY KEY ( tutorial_id )); ";
mysql_select_db( 'TUTORIALS' );
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not create table: ' . mysql_error());
}
echo "Table created successfully
";
mysql_close($conn);
?>
</body>
</html>
删除 MySQL 表
删除现有的 MySQL 表非常简单，但在删除任何现有表时都需要非常小心，因为删除表后丢失的数据将无法恢复。
语法
以下是删除 MySQL 表的通用 SQL 语法 -
DROP TABLE table_name ;
从命令提示符中删除表
要从命令提示符中删除表，我们需要在 mysql> 提示符下执行 DROP TABLE SQL 命令。
示例
以下程序是一个删除 tutorials_tbl 的示例 -
root@host# mysql -u root -p Enter password:******* mysql> use TUTORIALS; Database changed mysql> DROP TABLE tutorials_tbl Query OK, 0 rows affected (0.8 sec) mysql>
使用 PHP 脚本删除表
要删除任何数据库中的现有表，您需要使用 PHP 函数 mysql_query()。您需要将其第二个参数与正确的 SQL 命令一起传递才能删除表。
示例
<html>
<head>
<title>Creating MySQL Tables</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = "DROP TABLE tutorials_tbl";
mysql_select_db( 'TUTORIALS' );
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not delete table: ' . mysql_error());
}
echo "Table deleted successfully
";
mysql_close($conn);
?>
</body>
</html>
MySQL - 插入查询
要将数据插入 MySQL 表，您需要使用 SQL INSERT INTO 命令。您可以使用 mysql> 提示符或任何脚本(例如 PHP)将数据插入 MySQL 表。
语法
以下是 INSERT INTO 命令的通用 SQL 语法，用于将数据插入 MySQL 表 -
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
要插入字符串数据类型，需要将所有值括在双引号或单引号中。例如 "value"。
从命令提示符插入数据
要从命令提示符插入数据，我们将使用 SQL INSERT INTO 命令将数据插入 MySQL 表 tutorials_tbl。
示例
以下示例将在 tutorials_tbl 表中创建 3 条记录 -
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> INSERT INTO tutorials_tbl
->(tutorial_title, tutorial_author, submission_date)
->VALUES
->("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tutorials_tbl
->(tutorial_title, tutorial_author, submission_date)
->VALUES
->("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tutorials_tbl
->(tutorial_title, tutorial_author, submission_date)
->VALUES
->("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)
mysql>
注意 - 请注意，所有箭头符号 (->) 均不属于 SQL 命令。它们表示换行，并且由 MySQL 提示符在按下 Enter 键时自动创建，且在命令的每一行末尾不添加分号。
在上面的示例中，我们没有提供 tutorial_id，因为在创建表时，我们已为该字段指定了 AUTO_INCREMENT 选项。因此，MySQL 会自动插入这些 ID。这里，NOW() 是一个 MySQL 函数，它返回当前日期和时间。
使用 PHP 脚本插入数据
您可以将相同的 SQL INSERT INTO 命令用于 PHP 函数 mysql_query()，以将数据插入 MySQL 表。
示例
此示例将从用户那里获取三个参数，并将它们插入 MySQL 表 -
<html>
<head>
<title>Add New Record in MySQL Database</title>
</head>
<body>
<?php
if(isset($_POST['add'])) {
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
if(! get_magic_quotes_gpc() ) {
$tutorial_title = addslashes ($_POST['tutorial_title']);
$tutorial_author = addslashes ($_POST['tutorial_author']);
} else {
$tutorial_title = $_POST['tutorial_title'];
$tutorial_author = $_POST['tutorial_author'];
}
$submission_date = $_POST['submission_date'];
$sql = "INSERT INTO tutorials_tbl ".
"(tutorial_title,tutorial_author, submission_date) "."VALUES ".
"('$tutorial_title','$tutorial_author','$submission_date')";
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully
";
mysql_close($conn);
} else {
?>
<form method = "post" action = "<?php $_PHP_SELF ?>">
<table width = "600" border = "0" cellspacing = "1" cellpadding = "2">
<tr>
<td width = "250">Tutorial Title</td>
<td>
<input name = "tutorial_title" type = "text" id = "tutorial_title">
</td>
</tr>
<tr>
<td width = "250">Tutorial Author</td>
<td>
<input name = "tutorial_author" type = "text" id = "tutorial_author">
</td>
</tr>
<tr>
<td width = "250">Submission Date [ yyyy-mm-dd ]</td>
<td>
<input name = "submission_date" type = "text" id = "submission_date">
</td>
</tr>
<tr>
<td width = "250"> </td>
<td> </td>
</tr>
<tr>
<td width = "250"> </td>
<td>
<input name = "add" type = "submit" id = "add" value = "Add Tutorial">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>
执行数据插入时，最好使用函数 get_magic_quotes_gpc() 检查当前的魔术引号配置是否已设置。如果此函数返回 false，则使用函数 addslashes() 在引号前添加斜线。
您可以设置多种验证方式来检查输入的数据是否正确，并采取相应的措施。
MySQL - 选择查询
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 脚本获取数据
您可以将相同的 SQL SELECT 命令添加到 PHP 函数 mysql_query() 中。此函数用于执行 SQL 命令，之后可以使用另一个 PHP 函数 mysql_fetch_array() 获取所有选定的数据。此函数将以关联数组、数值数组或两者的形式返回行。如果没有更多行，此函数将返回 FALSE。
以下程序是一个简单的示例，它将展示如何从 tutorials_tbl 表中获取/显示记录。
示例
以下代码块将显示 tutorials_tbl 表中的所有记录。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully
";
mysql_close($conn);
?>
行的内容被赋值给变量 $row，然后打印该行的值。
注意 - 如果要将数组值直接插入字符串，请务必记住使用大括号。
在上面的例子中，常量 MYSQL_ASSOC 用作 PHP 函数 mysql_fetch_array() 的第二个参数，因此它将行作为关联数组返回。使用关联数组，您可以使用字段名称访问字段，而无需使用索引。
PHP 提供了另一个名为 mysql_fetch_assoc() 的函数，它也将行作为关联数组返回。
示例
以下示例使用 mysql_fetch_assoc() 函数显示 tutorial_tbl 表中的所有记录。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_assoc($retval)) {
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully
";
mysql_close($conn);
?>
您还可以使用常量 MYSQL_NUM 作为 PHP 函数 mysql_fetch_array() 的第二个参数。这将使该函数返回一个包含数字索引的数组。
示例
尝试以下示例，使用 MYSQL_NUM 参数显示 tutorials_tbl 表中的所有记录。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
echo "Tutorial ID :{$row[0]} <br> ".
"Title: {$row[1]} <br> ".
"Author: {$row[2]} <br> ".
"Submission Date : {$row[3]} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully
";
mysql_close($conn);
?>
以上三个示例的结果相同。
释放内存
在每个 SELECT 语句结束时释放游标内存是一个好习惯。您可以使用 PHP 函数 mysql_free_result() 来完成此操作。以下程序示例展示了如何使用该函数。
示例
尝试以下示例 -
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
echo "Tutorial ID :{$row[0]} <br> ".
"Title: {$row[1]} <br> ".
"Author: {$row[2]} <br> ".
"Submission Date : {$row[3]} <br> ".
"--------------------------------<br>";
}
mysql_free_result($retval);
echo "Fetched data successfully
";
mysql_close($conn);
?>
在获取数据时，您可以编写任意复杂的代码，但流程与上述相同。
MySQL - WHERE 子句
我们已经了解了如何使用 SQL SELECT 命令从 MySQL 表中获取数据。我们可以使用名为 WHERE 子句 的条件子句来筛选结果。使用 WHERE 子句，我们可以指定选择条件，从表中选择所需的记录。
语法
以下代码块包含 SELECT 命令的通用 SQL 语法，其中包含用于从 MySQL 表中获取数据的 WHERE 子句 -
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 脚本获取数据
您可以将相同的 SQL SELECT 命令与 WHERE 子句结合使用，并将其添加到 PHP 函数 mysql_query() 中。此函数用于执行 SQL 命令，之后可以使用另一个 PHP 函数 mysql_fetch_array() 获取所有选定的数据。此函数以关联数组、数值数组或两者的形式返回一行。如果没有其他行，此函数将返回 FALSE。
示例
以下示例将返回 tutorials_tbl 表中作者姓名为 Sanjay 的所有记录 -
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title,
tutorial_author, submission_date
FROM tutorials_tbl
WHERE tutorial_author = "Sanjay"';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully
";
mysql_close($conn);
?>
MySQL - UPDATE 查询
您可能需要修改 MySQL 表中的现有数据。您可以使用 SQL UPDATE 命令来执行此操作。这将修改任何 MySQL 表中的任何字段值。
语法
以下代码块包含 UPDATE 命令的通用 SQL 语法，用于修改 MySQL 表中的数据 -
UPDATE table_name SET field1 = new-value1, field2 = new-value2 [WHERE 子句]
- 您可以同时更新一个或多个字段。
- 您可以使用 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 函数 mysql_query() 中使用带或不带 WHERE 子句的 SQL UPDATE 命令。此函数将以类似于在 mysql> 提示符下执行的方式执行 SQL 命令。
示例
以下示例更新 tutorial_id 为 3 的记录的 tutorial_title 字段。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'UPDATE tutorials_tbl
SET tutorial_title="Learning JAVA"
WHERE tutorial_id=3';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully
";
mysql_close($conn);
?>
MySQL - DELETE 查询
如果您想从任何 MySQL 表中删除一条记录，可以使用 SQL 命令 DELETE FROM。您可以在 mysql> 中使用此命令。提示符以及任何 PHP 脚本中都可以使用。
语法
以下代码块包含 DELETE 命令的通用 SQL 语法，用于从 MySQL 表中删除数据。
DELETE FROM table_name [WHERE 子句]
如果未指定 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 函数 mysql_query() 中使用带或不带 WHERE 子句的 SQL DELETE 命令。此函数将以与在 mysql> 提示符下执行相同的方式执行 SQL 命令。
示例
尝试以下示例，从 tutorial_tbl 表中删除 tutorial_id 为 3 的记录。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'DELETE FROM tutorials_tbl WHERE tutorial_id = 3';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully
";
mysql_close($conn);
?>
MySQL - LIKE 子句
我们已经了解了如何使用 SQL SELECT 命令从 MySQL 表中获取数据。我们还可以使用称为 WHERE 子句的条件子句来选择所需的记录。
当我们想要进行精确匹配时，带有等号 (=) 的 WHERE 子句效果很好。例如，if "tutorial_author = 'Sanjay'"。但我们可能需要过滤掉所有 tutorial_author 名称中包含"jay"的结果。这可以使用 SQL LIKE 子句 和 WHERE 子句来实现。
如果 SQL LIKE 子句与 % 字符一起使用，那么它将像 UNIX 中的元字符 (*) 一样工作，同时在命令提示符下列出所有文件或目录。如果没有 % 字符，LIKE 子句与 WHERE 子句中的 等于 符号完全相同。
语法
以下代码块包含 SELECT 命令的通用 SQL 语法以及 LIKE 子句，用于从 MySQL 表中获取数据。
SELECT field1, field2,...fieldN table_name1, table_name2... WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
您可以使用 WHERE 子句指定任何条件。
您可以将 LIKE 子句与 WHERE 子句一起使用。
您可以使用 LIKE 子句代替 等于 符号。
何时使用 LIKE以及 % 符号，则其工作方式类似于元字符搜索。
您可以使用 AND 或 OR 运算符指定多个条件。
WHERE...LIKE 子句可以与 DELETE 或 UPDATE SQL 命令一起使用，以指定条件。
在命令提示符中使用 LIKE 子句
这将使用带有 WHERE...LIKE 子句的 SQL SELECT 命令从 MySQL 表 tutorials_tbl 中获取选定的数据。
示例
以下示例将返回 tutorials_tbl 表中作者姓名以 jay 结尾的所有记录 -
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> SELECT * from tutorials_tbl -> WHERE tutorial_author LIKE '%jay'; +-------------+----------------+-----------------+-----------------+ | tutorial_id | tutorial_title | tutorial_author | submission_date | +-------------+----------------+-----------------+-----------------+ | 3 | JAVA Tutorial | Sanjay | 2007-05-21 | +-------------+----------------+-----------------+-----------------+ 1 rows in set (0.01 sec) mysql>
在 PHP 脚本中使用 LIKE 子句
您可以在 PHP 函数 mysql_query() 中使用与 WHERE...LIKE 子句类似的语法。此函数用于执行 SQL 命令，之后如果 WHERE...LIKE 子句与 SELECT 命令一起使用，则可以使用另一个 PHP 函数 mysql_fetch_array() 获取所有选定的数据。
但是，如果 WHERE...LIKE 子句与 DELETE 或 UPDATE 命令一起使用，则无需进一步调用 PHP 函数。
示例
尝试以下示例，返回 tutorials_tbl 表中作者姓名包含 jay 的所有记录 -
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title,
tutorial_author, submission_date
FROM tutorials_tbl
WHERE tutorial_author LIKE "%jay%"';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully
";
mysql_close($conn);
?>
MySQL - 排序结果
我们已经了解了如何使用 SQL SELECT 命令从 MySQL 表中获取数据。当您选择行时，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 函数 mysql_query() 中使用类似的 ORDER BY 子句语法。此函数用于执行 SQL 命令，之后可以使用另一个 PHP 函数 mysql_fetch_array() 来获取所有选定的数据。
示例
尝试以下示例，它将按教程作者的降序排列返回结果。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT tutorial_id, tutorial_title,
tutorial_author, submission_date
FROM tutorials_tbl
ORDER BY tutorial_author DESC';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully
";
mysql_close($conn);
?>
使用 MySQL 连接
在前面的章节中，我们一次从一个表中获取数据。这对于简单的操作来说已经足够了，但在大多数实际的 MySQL 应用中，您通常需要在单个查询中从多个表中获取数据。
您可以在单个 SQL 查询中使用多个表。MySQL 中的连接操作是指将两个或多个表合并为一个表。
您可以在 SELECT、UPDATE 和 DELETE 语句中使用连接来连接 MySQL 表。我们还将看到一个 LEFT JOIN 的示例，它与简单的 MySQL JOIN 不同。
在命令提示符下使用连接
假设我们在教程中有两个表 tcount_tbl 和 tutorials_tbl。现在来看看下面给出的例子 -
示例
以下示例 -
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> SELECT * FROM tcount_tbl; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahran | 20 | | mahnaz | NULL | | Jen | NULL | | Gill | 20 | | John Poul | 1 | | Sanjay | 1 | +-----------------+----------------+ 6 rows in set (0.01 sec) mysql> SELECT * from tutorials_tbl; +-------------+----------------+-----------------+-----------------+ | tutorial_id | tutorial_title | tutorial_author | submission_date | +-------------+----------------+-----------------+-----------------+ | 1 | Learn PHP | John Poul | 2007-05-24 | | 2 | Learn MySQL | Abdul S | 2007-05-24 | | 3 | JAVA Tutorial | Sanjay | 2007-05-06 | +-------------+----------------+-----------------+-----------------+ 3 rows in set (0.00 sec) mysql>
现在我们可以编写一个 SQL 查询来连接这两个表。此查询将从表 tutorials_tbl 中选择所有作者，并从 tcount_tbl 中提取相应数量的教程。
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count -> FROM tutorials_tbl a, tcount_tbl b -> WHERE a.tutorial_author = b.tutorial_author; +-------------+-----------------+----------------+ | tutorial_id | tutorial_author | tutorial_count | +-------------+-----------------+----------------+ | 1 | John Poul | 1 | | 3 | Sanjay | 1 | +-------------+-----------------+----------------+ 2 rows in set (0.01 sec) mysql>
在 PHP 脚本中使用连接
您可以在 PHP 脚本中使用上述任何 SQL 查询。只需将 SQL 查询传递给 PHP 函数 mysql_query()，即可按常规方式获取结果。
示例
以下示例 -
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a, tcount_tbl b
WHERE a.tutorial_author = b.tutorial_author';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Author:{$row['tutorial_author']} <br> ".
"Count: {$row['tutorial_count']} <br> ".
"Tutorial ID: {$row['tutorial_id']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully
";
mysql_close($conn);
?>
MySQL LEFT JOIN
MySQL 左连接不同于简单的连接。MySQL LEFT JOIN 会额外考虑左侧的表。
如果我执行 LEFT JOIN，我会以相同的方式获取所有匹配的记录，此外，对于连接的左表中每个不匹配的记录，我还会获得一条额外的记录:从而确保(在我的示例中)每个作者都会被提及。
示例
尝试以下示例来理解 LEFT JOIN。
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count -> FROM tutorials_tbl a LEFT JOIN tcount_tbl b -> ON a.tutorial_author = b.tutorial_author; +-------------+-----------------+----------------+ | tutorial_id | tutorial_author | tutorial_count | +-------------+-----------------+----------------+ | 1 | John Poul | 1 | | 2 | Abdul S | NULL | | 3 | Sanjay | 1 | +-------------+-----------------+----------------+ 3 rows in set (0.02 sec)
您需要做更多的练习才能熟悉 JOINS。这在 MySQL/SQL 中是一个稍微复杂的概念，在实际示例中会变得更加清晰。
处理 MySQL NULL 值
我们已经了解了 SQL SELECT 命令以及 WHERE 子句如何从 MySQL 表中获取数据，但是当我们尝试给出一个条件，将字段或列值与 NULL 进行比较时，它无法正常工作。
为了处理这种情况，MySQL 提供了三个运算符 -
IS NULL - 如果列值为 NULL，则此运算符返回 true。
IS NOT NULL - 如果列值不为 NULL，则此运算符返回 true。
<=> - 此运算符比较值，与 = 运算符不同，即使两个 NULL 值也为真。
涉及 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 或不为 NULL 的记录，查询应按照以下程序所示编写。
mysql> SELECT * FROM tcount_tbl -> WHERE tutorial_count IS NULL; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahnaz | NULL | | Jen | NULL | +-----------------+----------------+ 2 rows in set (0.00 sec) mysql> SELECT * from tcount_tbl -> WHERE tutorial_count IS NOT NULL; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahran | 20 | | Gill | 20 | +-----------------+----------------+ 2 rows in set (0.00 sec)
在 PHP 脚本中处理 NULL 值
您可以使用 if...else 条件来准备基于 NULL 值的查询。
示例
以下示例从外部获取 tutorial_count，然后将其与表中可用的值进行比较。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
if( isset($tutorial_count )) {
$sql = 'SELECT tutorial_author, tutorial_count
FROM tcount_tbl
WHERE tutorial_count = $tutorial_count';
} else {
$sql = 'SELECT tutorial_author, tutorial_count
FROM tcount_tbl
WHERE tutorial_count IS $tutorial_count';
}
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Author:{$row['tutorial_author']} <br> ".
"Count: {$row['tutorial_count']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully
";
mysql_close($conn);
?>
MySQL - 正则表达式
您已经了解了使用 LIKE ...% 进行 MySQL 模式匹配。MySQL 支持另一种基于正则表达式和 REGEXP 运算符的模式匹配操作。如果您了解 PHP 或 PERL，那么您很容易理解，因为这种匹配与编写正则表达式的脚本相同。
以下是模式表，可与 REGEXP 运算符一起使用。
| 模式 | 模式匹配的内容 |
|---|---|
| ^ | 字符串开头 |
| $ | 字符串结尾 |
| . | 任意单个字符 |
| [...] | 方括号内列出的任意字符 |
| [^...] | 方括号内未列出的任意字符 |
| p1|p2|p3 | 交替;匹配模式 p1、p2 或 p3 中的任意一个 |
| * | 零个或多个前导元素实例 |
| + | 一个或多个前导元素实例 |
| {n | n 个前导元素实例 |
| {m,n | m 到 n 个前导元素实例 |
示例
现在，基于上表，您可以设计各种类型的 SQL 查询来满足您的需求。这里，我列出一些以便您理解。
假设我们有一个名为 person_tbl 的表，它有一个名为 name 的字段 -
查询所有以 'st' 开头的姓名 -
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查询所有以 'ok' 结尾的姓名 -
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
查询所有包含 'mar' 的姓名 -
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
查询所有以元音字母开头且以 'ok' 结尾的姓名 -
mysql> SELECT FirstName FROM intque.person_tbl WHERE FirstName REGEXP '^[aeiou].*ok$';
MySQL - 事务
事务是一组连续的数据库操作，其执行方式如同单个工作单元。换句话说，除非组内的每个操作都成功，否则事务永远不会完成。如果事务中的任何操作失败，则整个事务都会失败。
实际上，您会将多个 SQL 查询组合成一个组，然后将它们作为事务的一部分一起执行。
事务的属性
事务具有以下四个标准属性，通常缩写为 ACID -
原子性 - 这确保工作单元内的所有操作都成功完成;否则，事务将在故障点中止，之前的操作将回滚到之前的状态。
一致性 − 确保数据库在成功提交事务后正确地更改状态。
隔离性 − 使事务能够独立运行，并且彼此透明。
持久性 − 确保已提交事务的结果或效果在系统发生故障时能够持久保存。
在 MySQL 中，事务以 BEGIN WORK 语句开始，以 COMMIT 或 ROLLBACK 语句结束。开始和结束语句之间的 SQL 命令构成了事务的主体。
COMMIT 和 ROLLBACK
这两个关键字 Commit 和 Rollback 主要用于 MySQL 事务。
事务成功完成后，应发出 COMMIT 命令，以便对所有相关表的更改生效。
如果发生故障，应发出 ROLLBACK 命令，将事务中引用的每个表恢复到其先前的状态。
您可以通过设置名为 AUTOCOMMIT 的会话变量来控制事务的行为。如果 AUTOCOMMIT 设置为 1(默认值)，则每个 SQL 语句(无论是否在事务内)都将被视为一个完整的事务，并在完成后默认提交。
当 AUTOCOMMIT 设置为 0 时，通过发出 SET AUTOCOMMIT = 0 命令，后续一系列语句将像事务一样运行，在发出显式 COMMIT 语句之前不会提交任何活动。
您可以使用 mysql_query() 函数在 PHP 中执行这些 SQL 命令。
事务的通用示例
此事件序列与所使用的编程语言无关。无论您使用哪种语言创建应用程序，都可以创建逻辑路径。
您可以使用 mysql_query() 函数在 PHP 中执行这些 SQL 命令。
通过执行 SQL 命令 BEGIN WORK 开始事务。
执行一个或多个 SQL 命令，例如 SELECT、INSERT、UPDATE 或 DELETE。
检查是否没有错误，并且一切符合您的要求。
如果有任何错误，则执行 ROLLBACK 命令，否则执行 COMMIT 命令。
MySQL 中的事务安全表类型
您不能直接使用事务，但在某些异常情况下可以使用。然而，它们并不安全且无法保证其有效性。如果您计划在 MySQL 编程中使用事务，则需要以特殊的方式创建表。许多类型的表都支持事务，但最流行的是 InnoDB。
要支持 InnoDB 表，需要在从源代码编译 MySQL 时使用特定的编译参数。如果您的 MySQL 版本不支持 InnoDB，请要求您的互联网服务提供商构建一个支持 InnoDB 表类型的 MySQL 版本，或者下载并安装适用于 Windows 或 Linux/UNIX 的 MySQL-Max 二进制发行版，并在开发环境中使用该表类型。
如果您的 MySQL 安装支持 InnoDB 表，只需在表创建语句中添加 TYPE = InnoDB 定义即可。
例如，以下代码创建了一个名为 tcount_tbl 的 InnoDB 表 -
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> create table tcount_tbl -> ( -> tutorial_author varchar(40) NOT NULL, -> tutorial_count INT -> ) TYPE = InnoDB; Query OK, 0 rows affected (0.05 sec)
有关 InnoDB 的更多详细信息，您可以点击以下链接:InnoDB
您可以使用其他表类型，例如 GEMINI 或 BDB，但这取决于您的安装是否支持这两种表类型。
MySQL - ALTER 命令
当您想要更改表名、任何表字段，或者想要在表中添加或删除现有列时，MySQL ALTER 命令非常有用。
让我们从创建一个名为 testalter_tbl 的表开始。
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> create table testalter_tbl -> ( -> i INT, -> c CHAR(1) -> ); Query OK, 0 rows affected (0.05 sec) mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | i | int(11) | YES | | NULL | | | c | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
删除、添加或重新定位列
如果您想从上述 MySQL 表中删除现有列 i，则需要使用 DROP 子句和 ALTER 命令，如下所示 -
mysql> ALTER TABLE testalter_tbl DROP i;
如果表中只剩下该列，则 DROP 子句将不起作用。
要添加列，请使用 ADD 命令并指定列定义。以下语句将 i 列恢复到 testalter_tbl -
mysql> ALTER TABLE testalter_tbl ADD i INT;
执行此语句后，testalter 将包含与首次创建表时相同的两列，但结构会有所不同。这是因为默认情况下，新列会被添加到表的末尾。因此，即使 i 最初是 mytbl 中的第一列，现在它却是最后一列。
mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
要指示您希望将某列放置在表中的特定位置，请使用 FIRST 将其设为第一列，或使用 AFTER col_name 指示新列应放置在 col_name 之后。
尝试以下 ALTER TABLE 语句，并在每个语句后使用 SHOW COLUMNS 来查看每个语句的效果 -
ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT FIRST; ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT AFTER c;
FIRST 和 AFTER 说明符仅适用于 ADD 子句。这意味着，如果要重新定位表中的现有列，必须先DROP该列，然后在新位置ADD添加该列。
修改列定义或名称
要更改列定义，请将MODIFY或CHANGE子句与 ALTER 命令一起使用。
例如，要将列c从 CHAR(1) 更改为 CHAR(10)，可以使用以下命令 -
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
使用CHANGE时，语法略有不同。在 CHANGE 关键字之后，您可以命名要更改的列，然后指定包含新名称的新定义。
尝试以下示例 -
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
如果您现在使用 CHANGE 将 j 从 BIGINT 转换回 INT 而不更改列名，则语句将如下所示 -
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
ALTER TABLE 对 Null 和默认值属性的影响 - 当您修改或更改列时，您还可以指定该列是否可以包含 NULL 值以及其默认值是什么。事实上，如果您不这样做，MySQL 会自动为这些属性赋值。
以下代码块是一个示例，其中 NOT NULL 列的默认值为 100。
mysql> ALTER TABLE testalter_tbl -> MODIFY j BIGINT NOT NULL DEFAULT 100;
如果不使用上述命令，MySQL 将在所有列中填充 NULL 值。
修改列的默认值
您可以使用 ALTER 命令更改任何列的默认值。
尝试以下示例。
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | 1000 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
您可以使用 DROP 子句和 ALTER 命令从任何列中删除默认约束。
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
更改表类型
您可以使用 TYPE 子句和 ALTER 命令来更改表类型。请尝试以下示例，将 testalter_tbl 更改为 MYISAM 表类型。
要查看表的当前类型，请使用 SHOW TABLE STATUS 语句。
mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;
mysql> SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
Name: testalter_tbl
Type: MyISAM
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 25769803775
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2007-06-03 08:04:36
Update_time: 2007-06-03 08:04:36
Check_time: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
重命名(修改)表
要重命名表，请使用 ALTER TABLE 语句的 RENAME 选项。
尝试以下示例，将 testalter_tbl 重命名为 alter_tbl。
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
您可以使用 ALTER 命令在 MySQL 文件上创建和删除 INDEX 命令。我们将在下一章详细讨论此命令。
MySQL - 索引
数据库索引是一种可以提高表操作速度的数据结构。索引可以使用一列或多列创建，为快速随机查找和高效的记录访问排序提供基础。
创建索引时，应考虑哪些列将用于执行 SQL 查询，并在这些列上创建一个或多个索引。
实际上，索引也是一种表，它将主键或索引字段以及指向每条记录的指针保存到实际表中。
用户无法看到索引，它们仅用于加快查询速度，数据库搜索引擎将使用它们来快速定位记录。
在具有索引的表上，INSERT 和 UPDATE 语句会花费更多时间，而 SELECT 语句在这些表上会更快。原因是在执行插入或更新操作时，数据库也需要插入或更新索引值。
简单且唯一的索引
您可以在表上创建唯一索引。唯一索引意味着两行不能具有相同的索引值。以下是在表上创建索引的语法。
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);
您可以使用一列或多列来创建索引。
例如，我们可以使用 tutorial_author 在 tutorials_tbl 上创建索引。
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)
您可以在表上创建简单索引。只需从查询中省略 UNIQUE 关键字即可创建简单索引。简单索引允许表中存在重复值。
如果要按降序对列中的值进行索引，可以在列名后添加保留字 DESC。
mysql> CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC)
ALTER 命令用于添加和删除索引
向表添加索引的语句有四种类型 -
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) - 此语句添加一个PRIMARY KEY，这意味着索引值必须唯一且不能为 NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) - 此语句创建一个索引，其值必须唯一(NULL 值除外，NULL 值可以多次出现)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list) - 这将添加一个普通索引，其中任何值都可以多次出现一次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) − 这将创建一个特殊的 FULLTEXT 索引，用于文本搜索。
以下代码块是在现有表中添加索引的示例。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
您可以使用 DROP 子句和 ALTER 命令删除任何索引。
尝试以下示例删除上面创建的索引。
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);
您可以使用 DROP 子句和 ALTER 命令删除任何索引。
ALTER 命令添加和删除主键
您也可以用同样的方式添加主键。但请确保主键作用于 NOT NULL 的列。
以下代码块是在现有表中添加主键的示例。这将首先使列 NOT NULL，然后将其添加为主键。
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
您可以使用 ALTER 命令删除主键，如下所示 -
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
要删除非 PRIMARY KEY 的索引，必须指定索引名称。
显示索引信息
您可以使用 SHOW INDEX 命令列出与表关联的所有索引。此语句通常使用垂直格式输出(由 \G 指定)，以避免长行环绕 -
尝试以下示例 -
mysql> SHOW INDEX FROM table_name\G ........
MySQL - 临时表
临时表在某些情况下非常有用，可以用来保存临时数据。关于临时表，需要注意的是，它们会在当前客户端会话终止时被删除。
什么是临时表?
临时表是在 MySQL 3.23 版本中添加的。如果您使用的 MySQL 版本低于 3.23，则无法使用临时表，但可以使用堆表。
如前所述，临时表仅在会话期间有效。如果您在 PHP 脚本中运行代码，则临时表将在脚本执行完成后自动销毁。如果您通过 MySQL 客户端程序连接到 MySQL 数据库服务器，则临时表将一直存在，直到您关闭客户端或手动销毁该表。
示例
以下程序是一个示例，向您展示了临时表的用法。相同的代码也可以在 PHP 脚本中使用 mysql_query() 函数。
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
当您执行 SHOW TABLES 命令时，您的临时表不会列在列表中。现在，如果您退出 MySQL 会话，然后执行 SELECT 命令，您将发现数据库中没有任何可用数据。甚至您的临时表也不存在。
删除临时表
默认情况下，当您的数据库连接终止时，MySQL 会删除所有临时表。但是，如果您想在连接终止期间删除它们，则可以通过执行 DROP TABLE 命令来实现。
以下程序是删除临时表的示例 -
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql> SELECT * FROM SalesSummary;
ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist
MySQL - 克隆表
有时您需要一个表的精确副本，而 CREATE TABLE ... SELECT 语句无法满足您的需求，因为副本必须包含相同的索引、默认值等。
您可以按照以下步骤处理这种情况 -
使用 SHOW CREATE TABLE 命令获取一个指定源表结构、索引等信息的 CREATE TABLE 语句。
修改该语句，将表名更改为克隆表的名称，然后执行该语句。这样，您将获得完全相同的克隆表。
如果您还需要复制表内容，也可以执行 INSERT INTO ... SELECT 语句。
示例
尝试以下示例为 tutorials_tbl 创建克隆表。
步骤 1 - 获取表的完整结构。
mysql> SHOW CREATE TABLE tutorials_tbl \G;
*************************** 1. row ***************************
Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
`tutorial_id` int(11) NOT NULL auto_increment,
`tutorial_title` varchar(100) NOT NULL default '',
`tutorial_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`tutorial_id`),
UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE = MyISAM
1 row in set (0.00 sec)
ERROR:
No query specified
步骤 2 − 重命名此表并创建另一个表。
mysql> CREATE TABLE clone_tbl ( -> tutorial_id int(11) NOT NULL auto_increment, -> tutorial_title varchar(100) NOT NULL default '', -> tutorial_author varchar(40) NOT NULL default '', -> submission_date date default NULL, -> PRIMARY KEY (tutorial_id), -> UNIQUE KEY AUTHOR_INDEX (tutorial_author) -> ) TYPE = MyISAM; Query OK, 0 rows affected (1.80 sec)
步骤 3 - 执行步骤 2 后，您将在数据库中创建一个克隆表。如果您想从旧表中复制数据，可以使用 INSERT INTO... SELECT 语句来实现。
mysql> INSERT INTO clone_tbl (tutorial_id, -> tutorial_title, -> tutorial_author, -> submission_date) -> SELECT tutorial_id,tutorial_title, -> tutorial_author,submission_date -> FROM tutorials_tbl; Query OK, 3 rows affected (0.07 sec) Records: 3 Duplicates: 0 Warnings: 0
最终，您将获得一个与您想要的完全相同的克隆表。
MySQL - 数据库信息
获取和使用 MySQL 元数据
您希望从 MySQL 获取三种类型的信息。
查询结果信息 - 包括受 SELECT、UPDATE 或 DELETE 语句影响的记录数。
表和数据库信息 - 包括与表和数据库结构相关的信息。
MySQL 服务器信息 - 包括数据库服务器的状态、版本号等。
在 MySQL 提示符下获取所有这些信息非常容易，但在使用 PERL 或 PHP 时API，我们需要显式调用各种 API 来获取所有这些信息。
获取查询影响的行数
现在让我们看看如何获取这些信息。
PERL 示例
在 DBI 脚本中，受影响的行数由 do() 或 execute() 命令返回，具体取决于您执行查询的方式。
# 方法 1 # 使用 do() 执行 $query my $count = $dbh->do ($query); # 如果发生错误，则报告 0 行 printf "%d rows were affected ", (defined ($count) ? $count : 0); # 方法 2 # 使用 prepare() 和 execute() 执行查询 my $sth = $dbh->prepare ($query); my $count = $sth->execute ( ); printf "%d rows were affected ", (defined ($count) ? $count : 0);
PHP 示例
在 PHP 中，调用 mysql_affected_rows() 函数来查找查询更改了多少行。
$result_id = mysql_query ($query, $conn_id);
# 如果查询失败，则报告 0 行
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected
");
列出表和数据库
列出数据库服务器中所有可用的数据库和表非常容易。如果您没有足够的权限，结果可能为 null。
除了以下代码块中显示的方法外，您还可以使用 SHOW TABLES 或 SHOW DATABASES 查询来获取 PHP 或 PERL 中的表或数据库列表。
PERL 示例
# 获取当前数据库中所有可用的表。
my @tables = $dbh->tables ( );
foreach $table (@tables ){
print "Table Name $table
";
}
PHP 示例
<?php
$con = mysql_connect("localhost", "userid", "password");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
$db_list = mysql_list_dbs($con);
while ($db = mysql_fetch_object($db_list)) {
echo $db->Database . "<br />";
}
mysql_close($con);
?>
获取服务器元数据
MySQL 中有一些重要的命令，可以在 MySQL 提示符下执行，也可以使用 PHP 等脚本执行，以获取有关数据库服务器的各种重要信息。
| Sr.No. | Command &描述 |
|---|---|
| 1 |
SELECT VERSION() 服务器版本字符串 |
| 2 |
SELECT DATABASE() 当前数据库名称(如果没有则为空) |
| 3 |
SELECT USER() 当前用户名 |
| 4 |
SHOW STATUS 服务器状态指标 |
| 5 |
SHOW VARIABLES 服务器配置变量 |
使用 MySQL 序列
序列是一组整数 1、2、3……，它们根据特定需求按顺序生成。序列在数据库中经常使用，因为许多应用程序要求表中的每一行都包含一个唯一的值，而序列提供了一种生成序列的简便方法。
本章介绍如何在 MySQL 中使用序列。
使用 AUTO_INCREMENT 列
在 MySQL 中使用序列最简单的方法是将列定义为 AUTO_INCREMENT，其余的事情交给 MySQL 处理。
示例
尝试以下示例。这将创建表，然后在该表中插入几行，由于 MySQL 会自动增加记录 ID，因此无需提供记录 ID。
mysql> CREATE TABLE insect -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, # date collected -> origin VARCHAR(30) NOT NULL # where collected ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO insect (id,name,date,origin) VALUES -> (NULL,'housefly','2001-09-10','kitchen'), -> (NULL,'millipede','2001-09-10','driveway'), -> (NULL,'grasshopper','2001-09-10','front yard'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM insect ORDER BY id; +----+-------------+------------+------------+ | id | name | date | origin | +----+-------------+------------+------------+ | 1 | housefly | 2001-09-10 | kitchen | | 2 | millipede | 2001-09-10 | driveway | | 3 | grasshopper | 2001-09-10 | front yard | +----+-------------+------------+------------+ 3 rows in set (0.00 sec)
获取 AUTO_INCREMENT 值
LAST_INSERT_ID() 是一个 SQL 函数，因此您可以在任何了解如何发出 SQL 语句的客户端中使用它。此外，PERL 和 PHP 脚本提供了专用函数来检索最后一条记录的自动递增值。
PERL 示例
使用 mysql_insertid 属性获取查询生成的 AUTO_INCREMENT 值。此属性可以通过数据库句柄或语句句柄访问，具体取决于您发出查询的方式。
以下示例通过数据库句柄引用它。
$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};
PHP 示例
执行生成 AUTO_INCREMENT 值的查询后，通过调用 mysql_insert_id() 命令检索该值。
mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);
对现有序列重新编号
有时，您可能已经从表中删除了许多记录，并且想要对所有记录重新排序。这可以通过一个简单的技巧来实现，但如果您的表与另一个表有连接，则您应该非常小心地这样做。
如果您确定 AUTO_INCREMENT 列的重新排序不可避免，则执行此操作的方法是从表中删除该列，然后重新添加它。
以下示例显示如何使用此技术对表中的 id 值 重新编号。
mysql> ALTER TABLE insect DROP id; mysql> ALTER TABLE insect -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, -> ADD PRIMARY KEY (id);
从特定值开始序列
默认情况下，MySQL 序列从 1 开始，但您也可以在创建表时指定任何其他数字。
以下程序是一个示例，展示了 MySQL 如何从 100 开始序列。
mysql> CREATE TABLE insect -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, # date collected -> origin VARCHAR(30) NOT NULL # where collected );
或者，您可以创建表，然后使用 ALTER TABLE 命令设置初始序列值。
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
MySQL - 处理重复项
通常，表或结果集有时会包含重复记录。大多数情况下，这是允许的，但有时需要阻止重复记录。需要识别重复记录并将其从表中删除。本章将介绍如何防止表中出现重复记录以及如何删除已经存在的重复记录。
防止表中出现重复记录
您可以在表上使用相应字段的PRIMARY KEY或UNIQUE索引来阻止重复记录。
举个例子，下表不包含这样的索引或主键，因此它允许first_name和last_name出现重复记录。
CREATE TABLE person_tbl ( first_name CHAR(20), last_name CHAR(20), sex CHAR(10) );
为防止此表中创建多个具有相同姓氏和名字值的记录，请在其定义中添加 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;
从表中删除重复记录的一个简单方法是向该表添加索引或主键。即使该表已经存在，您也可以使用此方法删除重复记录，并且将来也能保证安全。
mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);
MySQL 和 SQL 注入
如果您通过网页获取用户输入并将其插入 MySQL 数据库，则可能会面临称为 SQL 注入 的安全问题。本章将教您如何防止此类情况发生，并帮助您保护脚本和 MySQL 语句的安全。
SQL 注入通常发生在您要求用户输入(例如其姓名)时，而他们提供的不是姓名，而是一条 MySQL 语句，而您会在不知情的情况下在数据库上运行该语句。
永远不要相信用户提供的数据，仅在验证后处理这些数据;通常，验证是通过模式匹配完成的。在以下示例中，用户名限制为字母数字字符加下划线，长度在 8 到 20 个字符之间(请根据需要修改这些规则)。
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) {
$result = mysql_query("SELECT * FROM users WHERE username = $matches[0]");
} else {
echo "username not accepted";
}
为了演示此问题，请参考以下摘录。
// 假设输入
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name = '{$name}'");
该函数调用旨在从 users 表中检索一条记录，其中 name 列与用户指定的名称匹配。正常情况下，$name 只包含字母数字字符，也可能包含空格。但在这里，由于向 $name 附加了一个全新的查询，对数据库的调用变成了一场灾难。注入的 DELETE 查询会删除 users 的所有记录。
幸运的是，如果您使用 MySQL，mysql_query() 函数不允许查询堆叠或在单个函数调用中执行多个查询。如果您尝试堆叠查询，调用将失败。
然而，其他 PHP 数据库扩展，例如 SQLite 和 PostgreSQL，却乐于执行堆叠查询，将所有查询放在一个字符串中执行，从而造成严重的安全问题。
防止 SQL 注入
您可以在脚本语言(如 PERL 和 PHP)中巧妙地处理所有转义字符。PHP 的 MySQL 扩展提供了函数 mysql_real_escape_string() 来转义 MySQL 特有的输入字符。
if (get_magic_quotes_gpc()) {
$name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name = '{$name}'");
LIKE 难题
为了解决 LIKE 难题，自定义转义机制必须将用户提供的 % 和 _ 字符转换为字面量。请使用 addcslashes() 函数，该函数允许您指定要转义的字符范围。
$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == \%something\_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");
MySQL - 数据库导出
将表数据导出到文本文件的最简单方法是使用 SELECT...INTO OUTFILE 语句，该语句将查询结果直接导出到服务器主机上的文件中。
使用 SELECT ... INTO OUTFILE 语句导出数据
此语句的语法将常规 SELECT 命令与末尾的 INTO OUTFILE filename 组合在一起。默认输出格式与 LOAD DATA 命令相同。因此，以下语句将 tutorials_tbl 表导出到 /tmp/tutorials.txt 中，并以制表符分隔、换行符结尾的文件形式保存。
mysql> SELECT * FROM tutorials_tbl -> INTO OUTFILE '/tmp/tutorials.txt';
您可以使用各种选项更改输出格式，以指示如何引用和分隔列和记录。要将 tutorial_tbl 表导出为包含 CRLF 结尾行的 CSV 格式，请使用以下代码。
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt' -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY ' ';
SELECT ... INTO OUTFILE 具有以下属性 -
输出文件由 MySQL 服务器直接创建，因此文件名应指示您希望文件在服务器主机上的写入位置。该语句没有类似于 LOAD DATA 的 LOCAL 版本的 LOCAL 版本。
您必须拥有 MySQL FILE 权限才能执行 SELECT ... INTO 语句。
输出文件必须不存在。这可以防止 MySQL 破坏可能重要的文件。
您应该在服务器主机上拥有登录帐户，或者有某种方式从该主机检索文件。否则，SELECT ... INTO OUTFILE 命令很可能对您毫无用处。
在 UNIX 下，该文件创建为全局可读，并归 MySQL 服务器所有。这意味着虽然您可以读取该文件，但可能无法删除它。
将表导出为原始数据
mysqldump 程序用于复制或备份表和数据库。它可以将表输出写为原始数据文件，也可以写为一组INSERT语句(用于重新创建表中的记录)。
要将表转储为数据文件，必须指定--tab选项，该选项指示 MySQL 服务器写入文件的目录。
例如，要将tutorials_tbl表从TUTORIALS数据库转储到/tmp目录中的文件中，请使用如下所示的命令。
$ mysqldump -u root -p --no-create-info \ --tab=/tmp tutorials tutorials_tbl password ******
以 SQL 格式导出表内容或定义
要将表以 SQL 格式导出到文件，请使用以下命令。
$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt password ******
这将创建一个包含如下所示内容的文件。
-- MySQL dump 8.23 -- -- Host: localhost Database: TUTORIALS --------------------------------------------------------- -- Server version 3.23.58 -- -- Table structure for table `tutorials_tbl` -- CREATE TABLE tutorials_tbl ( tutorial_id int(11) NOT NULL auto_increment, tutorial_title varchar(100) NOT NULL default '', tutorial_author varchar(40) NOT NULL default '', submission_date date default NULL, PRIMARY KEY (tutorial_id), UNIQUE KEY AUTHOR_INDEX (tutorial_author) ) TYPE = MyISAM; -- -- Dumping data for table `tutorials_tbl` -- INSERT INTO tutorials_tbl VALUES (1,'Learn PHP','John Poul','2007-05-24'); INSERT INTO tutorials_tbl VALUES (2,'Learn MySQL','Abdul S','2007-05-24'); INSERT INTO tutorials_tbl VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');
要转储多个表，请依次命名所有表，后跟数据库名称参数。要转储整个数据库，请勿在数据库后命名任何表，如以下代码块所示。
$ mysqldump -u root -p TUTORIALS > database_dump.txt password ******
要备份主机上所有可用的数据库，请使用以下代码。
$ mysqldump -u root -p --all-databases > database_dump.txt password ******
--all-databases 选项在 MySQL 3.23.12 版本中可用。此方法可用于实施数据库备份策略。
将表或数据库复制到另一台主机
如果您想将表或数据库从一台 MySQL 服务器复制到另一台 MySQL 服务器，请使用 mysqldump 命令，并指定数据库名称和表名称。
在源主机上运行以下命令。这将把完整的数据库转储到 dump.txt 文件中。
$ mysqldump -u root -p database_name table_name > dump.txt password *****
您可以复制完整的数据库，而无需使用如上所述的特定表名。
现在，通过 ftp 将 dump.txt 文件传输到另一台主机并使用以下命令。运行此命令之前，请确保您已在目标服务器上创建了 database_name。
$ mysql -u root -p database_name < dump.txt password *****
另一种无需使用中间文件即可实现此目的的方法是将 mysqldump 的输出直接通过网络发送到远程 MySQL 服务器。如果您可以从源数据库所在的主机连接到这两台服务器，请使用以下命令(确保您在这两台服务器上均具有访问权限)。
$ mysqldump -u root -p database_name \ | mysql -h other-host.com database_name
在 mysqldump 中，一半命令连接到本地服务器，并将转储输出写入管道。另一半命令连接到 other-host.com 上的远程 MySQL 服务器。它读取管道中的输入，并将每个语句发送到 other-host.com 服务器。
MySQL - 数据库导入 - 恢复方法
在 MySQL 中，有两种简单的方法可以将数据从先前备份的文件加载到 MySQL 数据库中。
使用 LOAD DATA 导入数据
MySQL 提供了一个 LOAD DATA 语句，可充当批量数据加载器。以下示例语句从当前目录读取文件 dump.txt，并将其加载到当前数据库的表 mytbl 中。
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
如果没有指定 LOCAL 关键字，MySQL 会使用查找绝对路径的方式在服务器主机上查找数据文件，该路径指定了文件的完整位置，从文件系统的根目录开始。MySQL 会从指定位置读取文件。
默认情况下，LOAD DATA 假定数据文件包含以换行符(新行符)结尾的行，并且行内的数据值以制表符分隔。
要明确指定文件格式，请使用 FIELDS 子句描述行内字段的特征，并使用 LINES 子句指定行尾顺序。以下 LOAD DATA 语句指定数据文件包含以冒号分隔的值以及以回车符和换行符结尾的行。
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl -> FIELDS TERMINATED BY ':' -> LINES TERMINATED BY ' ';
LOAD DATA 命令假定数据文件中的列与表中的列顺序相同。如果不是这样，您可以指定一个列表来指示应将数据文件中的列加载到哪些表列中。假设您的表包含列 a、b 和 c，但数据文件中的连续列分别对应于列 b、c 和 a。
您可以按照以下代码块所示加载文件。
mysql> LOAD DATA LOCAL INFILE 'dump.txt' -> INTO TABLE mytbl (b, c, a);
使用 mysqlimport 导入数据
MySQL 还包含一个名为 mysqlimport 的实用程序，它充当 LOAD DATA 的包装器，以便您可以直接从命令行加载输入文件。
要将数据从 dump.txt 加载到 mytbl，请在 UNIX 提示符下使用以下命令。
$ mysqlimport -u root -p --local database_name dump.txt password *****
如果您使用 mysqlimport，命令行选项会提供格式说明符。与前两个 LOAD DATA 语句对应的 mysqlimport 命令如下所示。
$ mysqlimport -u root -p --local --fields-terminated-by = ":" \ --lines-terminated-by = " " database_name dump.txt password *****
对于 mysqlimport 来说，指定选项的顺序并不重要，但它们都必须位于数据库名称之前。
mysqlimport 语句使用 --columns 选项指定列顺序 -
$ mysqlimport -u root -p --local --columns=b,c,a \ database_name dump.txt password *****
处理引号和特殊字符
除 TERMINATED BY 外，FIELDS 子句还可以指定其他格式选项。默认情况下，LOAD DATA 假定值不带引号，并将反斜杠 (\) 解释为特殊字符的转义符。要明确指示值的引号字符，请使用 ENCLOSED BY 命令。MySQL 会在输入处理过程中从数据值的末尾删除该字符。要更改默认转义符，请使用 ESCAPED BY。
当您指定 ENCLOSED BY 来指示应从数据值中删除引号字符时，可以通过将引号字符重复或在引号字符前面加上转义符，将引号字符直接包含在数据值中。
例如，如果引号和转义符分别为 " 和 \，则输入值 "a""b\"c" 将被解释为 a"b"c。
对于 mysqlimport，用于指定引号和转义值的相应命令行选项为 --fields-enclosed-by 和 --fields-escaped-by。
