MySQL – 快速指南

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 Community Server版本号以及您将在其上运行的平台。

在 Linux/UNIX 上安装 MySQL

在 Linux 系统上安装 MySQL 的推荐方法是通过 RPM。MySQL AB 在其网站上提供以下 RPM 可供下载 –

  • MySQL – MySQL 数据库服务器管理数据库和表，控制用户访问并处理 SQL 查询。

  • MySQL-client – MySQL 客户端程序，可以连接到服务器并与之交互。

  • MySQL-devel – 在编译其他使用 MySQL 的程序时派上用场的库和头文件。

  • MySQL-shared – MySQL 客户端的共享库。

  • MySQL-be​​nch – MySQL 数据库服务器的基准测试和性能测试工具。

这里列出的 MySQL RPM 都是建立在SuSE Linux 系统上的，但它们通常可以毫无困难地在其他 Linux 变体上运行。

现在，您需要遵循下面给出的步骤，继续安装 –

  • 使用root用户登录系统。

  • 切换到包含 RPM 的目录。

  • 通过执行以下命令安装 MySQL 数据库服务器。请记住将斜体文件名替换为 RPM 的文件名。

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

上面的命令负责安装 MySQL 服务器，创建 MySQL 用户，创建必要的配置并自动启动 MySQL 服务器。

您可以在 /usr/bin 和 /usr/sbin 中找到所有与 MySQL 相关的二进制文件。所有的表和数据库都将在 /var/lib/mysql 目录中创建。

以下代码框有一个可选但推荐的步骤，以相同的方式安装剩余的 RPM –

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

在 Windows 上安装 MySQL

现在，任何版本的 Windows 上的默认安装都比以前容易得多，因为 MySQL 现在与安装程序一起打包。只需下载安装程序包，将其解压到任意位置并运行 setup.exe 文件。

默认安装程序 setup.exe 将引导您完成微不足道的过程，默认情况下将在 C:\mysql 下安装所有内容。

通过第一次从命令提示符启动它来测试服务器。转到mysqld 服务器的位置，可能是 C:\mysql\bin，然后键入 –

mysqld.exe --console

注意– 如果您在 NT 上，则必须使用 mysqld-nt.exe 而不是 mysqld.exe

如果一切顺利，您将看到一些有关启动和InnoDB 的消息。如果没有，您可能有权限问题。确保数据库进程在其下运行的任何用户（可能是 MySQL）都可以访问保存数据的目录。

MySQL 不会将自己添加到开始菜单中，并且也没有特别好的 GUI 方式来停止服务器。因此，如果您倾向于通过双击 mysqld 可执行文件来启动服务器，您应该记住使用 mysqladmin、任务列表、任务管理器或其他特定于 Windows 的方法手动停止该进程。

验证 MySQL 安装

成功安装 MySQL 后，基表已初始化，服务器已启动：您可以通过一些简单的测试来验证一切正常。

使用 mysqladmin 实用程序获取服务器状态

使用mysqladmin二进制文件检查服务器版本。这个二进制文件在 Linux 上的 /usr/bin 和 Windows 上的 C:\mysql\bin 中可用。

[root@host]# mysqladmin --version

它将在 Linux 上产生以下结果。它可能因您的安装而异 –

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

如果您没有收到这样的消息，那么您的安装可能存在一些问题，您需要一些帮助来修复它。

使用 MySQL 客户端执行简单的 SQL 命令

您可以通过 MySQL 客户端和使用mysql命令连接到您的 MySQL 服务器。此时，您不需要提供任何密码，因为默认情况下它会被设置为空白。

您可以使用以下命令 –

[root@host]# mysql

它应该得到一个 mysql> 提示。现在，您已连接到 MySQL 服务器，您可以在 mysql> 提示符下执行所有 SQL 命令，如下所示 –

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

安装后步骤

MySQL 为 root MySQL 用户提供了一个空白密码。成功安装数据库和客户端后，您需要按照以下代码块中给出的方式设置 root 密码 –

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

现在要连接到您的 MySQL 服务器，您必须使用以下命令 –

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

UNIX 用户还希望将您的 MySQL 目录放在您的 PATH 中，这样您就不必在每次要使用命令行客户端时都输入完整路径。

对于 bash，它类似于 –

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

在启动时运行 MySQL

如果要在启动时运行 MySQL 服务器，请确保 /etc/rc.local 文件中有以下条目。

/etc/init.d/mysqld start

此外，您应该在 /etc/init.d/ 目录中有 mysqld 二进制文件。

MySQL – 管理

运行和关闭 MySQL 服务器

首先检查您的 MySQL 服务器是否正在运行。您可以使用以下命令进行检查 –

ps -ef | grep mysqld

如果您的 MySql 正在运行，那么您将看到结果中列出了mysqld进程。如果服务器未运行，则可以使用以下命令启动它 –

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

现在，如果你想关闭已经运行的 MySQL 服务器，那么你可以使用以下命令来完成 –

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

设置 MySQL 用户帐户

要将新用户添加到 MySQL，您只需要在数据库mysql 中的用户表中添加一个新条目。

下面的程序是添加一个具有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的条目。

注意– 在 SQL 命令的末尾给出分号 (;) 之前，MySQL 不会终止命令。

/etc/my.cnf 文件配置

在大多数情况下，您不应触摸此文件。默认情况下，它将具有以下条目 –

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

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

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

在这里，您可以为错误日志指定不同的目录，否则不应更改此表中的任何条目。

管理 MySQL 命令

以下是重要的 MySQL 命令列表，您将不时使用它们来处理 MySQL 数据库 –

  • USE Databasename – 这将用于在 MySQL 工作区中选择一个数据库。

  • SHOW DATABASES – 列出 MySQL DBMS 可访问的数据库。

  • SHOW TABLES – 使用 use 命令选择数据库后，显示数据库中的表。

  • SHOW COLUMNS FROM tablename：显示表的属性、属性类型、键信息、是否允许NULL、默认值和其他信息。

  • SHOW INDEX FROM tablename –显示表上所有索引的详细信息，包括 PRIMARY KEY。

  • SHOW TABLE STATUS LIKE tablename\G – 报告 MySQL DBMS 性能和统计信息的详细信息。

在下一章中，我们将讨论如何在 MySQL 中使用 PHP 语法。

MySQL – PHP 语法

MySQL 可以很好地结合各种编程语言，如 PERL、C、C++、JAVA 和 PHP。在这些语言中，PHP 是最受欢迎的一种，因为它具有 Web 应用程序开发能力。

本教程主要关注在 PHP 环境中使用 MySQL。如果你对 MySQL 和 PERL 感兴趣，那么你可以考虑阅读PERL 教程。

PHP 提供了各种函数来访问 MySQL 数据库和操作 MySQL 数据库内部的数据记录。您需要以与调用任何其他 PHP 函数相同的方式调用 PHP 函数。

用于 MySQL 的 PHP 函数具有以下通用格式 –

mysql_function(value,value,...);

函数名的第二部分是特定于函数的，通常是描述函数做什么的词。以下是我们将在教程中使用的两个函数 –

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

以下示例显示了调用任何 MySQL 函数的 PHP 通用语法。

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

从下一章开始，我们将看到所有重要的 MySQL 功能以及 PHP。

MySQL – 连接

使用 MySQL 二进制文件的 MySQL 连接

您可以在命令提示符下使用mysql二进制文件建立 MySQL 数据库。

例子

这是从命令提示符连接到 MySQL 服务器的简单示例 –

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

这将为您提供 mysql> 命令提示符，您可以在其中执行任何 SQL 命令。以下是上述命令的结果 –

以下代码块显示了上述代码的结果 –

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

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

在上面的示例中，我们使用root作为用户，但您也可以使用任何其他用户。任何用户都可以执行该用户允许的所有 SQL 操作。

您可以随时在 mysql> 提示符下使用exit命令断开与 MySQL 数据库的连接。

mysql> exit
Bye

使用 PHP 脚本的 MySQL 连接

PHP 提供了mysql_connect()函数来打开数据库连接。该函数接受五个参数，成功时返回 MySQL 链接标识符，失败时返回 FALSE。

句法

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

Sr.No. 参数及说明
1

server

可选 – 运行数据库服务器的主机名。如果未指定，则默认值为localhost:3306。
2

user

可选 – 访问数据库的用户名。如果未指定，则默认值为拥有服务器进程的用户名。
3

passwd

可选 – 访问数据库的用户的密码。如果未指定，则默认为空密码。
4

new_link

可选 – 如果使用相同的参数对 mysql_connect() 进行第二次调用，则不会建立新的连接；相反，将返回已打开连接的标识符。
5

client_flags

可选 – 以下常量的组合 –

  • MYSQL_CLIENT_SSL – 使用 SSL 加密。

  • MYSQL_CLIENT_COMPRESS – 使用压缩协议。

  • MYSQL_CLIENT_IGNORE_SPACE – 在函数名称后允许空格。

  • MYSQL_CLIENT_INTERACTIVE – 在关闭连接之前允许交互式超时秒不活动。

您可以随时使用另一个 PHP 函数mysql_close()断开与 MySQL 数据库的连接。此函数采用单个参数，即mysql_connect()函数返回的连接。

句法

bool mysql_close ( resource $link_identifier );

如果未指定资源，则关闭最后打开的数据库。如果成功关闭连接，则此函数返回真，否则返回假。

例子

尝试以下示例连接到 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\n";
         mysql_close($conn);
      ?>
   </body>
</html>

删除 MySQL 数据库

使用 mysqladmin 删除数据库

您需要特殊权限才能创建或删除 MySQL 数据库。因此，假设您有权访问 root 用户，您可以使用 mysql mysqladmin二进制文件创建任何数据库。

删除任何数据库时要小心，因为您将丢失数据库中的所有可用数据。

这是删除在上一章中创建的数据库（TUTORIALS）的示例 –

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

这会给你一个警告，它会确认你是否真的想删除这个数据库。

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

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

使用 PHP 脚本删除数据库

PHP 使用mysql_query函数来创建或删除 MySQL 数据库。此函数采用两个参数，成功时返回 TRUE，失败时返回 FALSE。

句法

bool mysql_query( sql, connection );

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\n";
         mysql_close($conn);
      ?>
   </body>
</html>

警告– 使用 PHP 脚本删除数据库时，它不会提示您进行任何确认。所以在删除 MySQL 数据库时要小心。

选择 MySQL 数据库

与 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 提供了函数mysql_select_db来选择数据库。成功时返回 TRUE，失败时返回 FALSE。

句法

bool mysql_select_db( db_name, connection );

Sr.No. 参数及说明
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 之间。例如，12 月 30 日th, 1973 将存储为 1973-12-30。

  • DATETIME – YYYY-MM-DD HH:MM:SS 格式的日期和时间组合，介于 1000-01-01 00:00:00 和 9999-12-31 23:59:59 之间。例如12月30日下午3:30th, 1973 将存储为 1973-12-30 15:30:00。

  • TIMESTAMP – 1 月 1 日午夜之间的时间戳st, 1970 和 2037 年的某个时间。这看起来像以前的 DATETIME 格式，只是数字之间没有连字符；12月30日下午3:30th, 1973 将存储为 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和是不区分大小写的文本字段。您没有用 BLOB 或 TEXT 指定长度。

  • TINYBLOB 或 TINYTEXT – 最大长度为 255 个字符的 BLOB 或 TEXT 列。您没有使用 TINYBLOB 或 TINYTEXT 指定长度。

  • MEDIUMBLOB 或 MEDIUMTEXT – 最大长度为 16777215 个字符的 BLOB 或 TEXT 列。您没有使用 MEDIUMBLOB 或 MEDIUMTEXT 指定长度。

  • LONGBLOB 或 LONGTEXT – 最大长度为 4294967295 个字符的 BLOB 或 TEXT 列。您没有使用 LONGBLOB 或 LONGTEXT 指定长度。

  • ENUM – 枚举，这是列表的一个奇特术语。定义 ENUM 时，您正在创建一个必须从中选择值（或可以为 NULL）的项目列表。例如，如果您希望您的字段包含“A”或“B”或“C”，您可以将 ENUM 定义为 ENUM (‘A’, ‘B’, ‘C’) 并且只有那些值（或 NULL）可以填充该字段。

在下一章中，我们将讨论如何在 MySQL 中创建表。

创建 MySQL 表

首先，表创建命令需要以下详细信息 –

  • 表名
  • 字段名称
  • 每个字段的定义

句法

这是创建 MySQL 表的通用 SQL 语法 –

CREATE TABLE table_name (column_name column_type);

现在，我们将在TUTORIALS数据库中创建下表。

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

在这里，一些项目需要解释 –

  • 正在使用字段属性NOT NULL，因为我们不希望此字段为 NULL。因此，如果用户尝试使用 NULL 值创建记录，则 MySQL 将引发错误。

  • 字段属性AUTO_INCREMENT告诉 MySQL 继续将下一个可用数字添加到 id 字段。

  • 关键字PRIMARY KEY用于将列定义为主键。您可以使用逗号分隔的多列来定义主键。

从命令提示符创建表

从 mysql> 提示符创建 MySQL 表很容易。您将使用 SQL 命令CREATE TABLE创建一个表。

例子

这是一个示例，它将创建tutorials_tbl –

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

注意– MySQL 不会终止命令，直到您在 SQL 命令的末尾给出分号 (;)。

使用 PHP 脚本创建表

要在任何现有数据库中创建新表，您需要使用 PHP 函数mysql_query()。您将使用适当的 SQL 命令传递其第二个参数以创建表。

例子

以下程序是使用 PHP 脚本创建表的示例 –

<html>
   <head>
      <title>Creating MySQL Tables</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'root';
         $dbpass = 'rootpassword';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully<br />';
         $sql = "CREATE TABLE tutorials_tbl( ".
            "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
            "tutorial_title VARCHAR(100) NOT NULL, ".
            "tutorial_author VARCHAR(40) NOT NULL, ".
            "submission_date DATE, ".
            "PRIMARY KEY ( tutorial_id )); ";
         mysql_select_db( 'TUTORIALS' );
         $retval = mysql_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not create table: ' . mysql_error());
         }
         echo "Table created successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

删除 MySQL 表

删除现有的 MySQL 表非常容易，但是删除任何现有表时需要非常小心，因为删除表后丢失的数据将无法恢复。

句法

这是删除 MySQL 表的通用 SQL 语法 –

DROP TABLE table_name ;

从命令提示符删除表

要从命令提示符删除表，我们需要在 mysql> 提示符下执行 DROP TABLE SQL 命令。

例子

以下程序是删除tutorials_tbl的示例–

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

使用 PHP 脚本删除表

要删除任何数据库中的现有表，您需要使用 PHP 函数mysql_query()。您将使用适当的 SQL 命令传递它的第二个参数以删除表。

例子

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

MySQL – 插入查询

要将数据插入 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>

NOTE – 请注意，所有箭头符号 (->) 都不是 SQL 命令的一部分。它们表示一个新行，它们是由 MySQL 提示自动创建的，同时按下 Enter 键，在命令的每一行末尾没有分号。

在上面的例子中，我们没有提供tutorial_id，因为在创建表时，我们已经为这个字段提供了 AUTO_INCREMENT 选项。因此 MySQL 会自动插入这些 ID。这里，NOW()是一个 MySQL 函数，它返回当前日期和时间。

使用 PHP 脚本插入数据

您可以在 PHP 函数mysql_query() 中使用相同的 SQL INSERT INTO 命令将数据插入到 MySQL 表中。

例子

这个例子将从用户那里获取三个参数，并将它们插入到 MySQL 表中 –

<html>

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

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

            if(! get_magic_quotes_gpc() ) {
               $tutorial_title = addslashes ($_POST['tutorial_title']);
               $tutorial_author = addslashes ($_POST['tutorial_author']);
            } else {
               $tutorial_title = $_POST['tutorial_title'];
               $tutorial_author = $_POST['tutorial_author'];
            }

            $submission_date = $_POST['submission_date'];
   
            $sql = "INSERT INTO tutorials_tbl ".
               "(tutorial_title,tutorial_author, submission_date) "."VALUES ".
               "('$tutorial_title','$tutorial_author','$submission_date')";
               mysql_select_db('TUTORIALS');
            $retval = mysql_query( $sql, $conn );
         
            if(! $retval ) {
               die('Could not enter data: ' . mysql_error());
            }
         
            echo "Entered data successfully\n";
            mysql_close($conn);
         } else {
      ?>
   
      <form method = "post" action = "<?php $_PHP_SELF ?>">
         <table width = "600" border = "0" cellspacing = "1" cellpadding = "2">
            <tr>
               <td width = "250">Tutorial Title</td>
               <td>
                  <input name = "tutorial_title" type = "text" id = "tutorial_title">
               </td>
            </tr>
         
            <tr>
               <td width = "250">Tutorial Author</td>
               <td>
                  <input name = "tutorial_author" type = "text" id = "tutorial_author">
               </td>
            </tr>
         
            <tr>
               <td width = "250">Submission Date [   yyyy-mm-dd ]</td>
               <td>
                  <input name = "submission_date" type = "text" id = "submission_date">
               </td>
            </tr>
      
            <tr>
               <td width = "250"> </td>
               <td> </td>
            </tr>
         
            <tr>
               <td width = "250"> </td>
               <td>
                  <input name = "add" type = "submit" id = "add"  value = "Add Tutorial">
               </td>
            </tr>
         </table>
      </form>
   <?php
      }
   ?>
   </body>
</html>

在进行数据插入时，最好使用函数get_magic_quotes_gpc()来检查是否设置了魔术引号的当前配置。如果此函数返回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 脚本获取数据

您可以在 PHP 函数mysql_query() 中使用相同的 SQL SELECT 命令。该函数用于执行 SQL 命令，然后另一个 PHP 函数mysql_fetch_array()可用于获取所有选定的数据。此函数以关联数组、数值数组或两者的形式返回行。如果没有更多行，此函数返回 FALSE。

下面的程序是一个简单的例子，它将展示如何从tutorials_tbl表中获取/显示记录。

例子

以下代码块将显示 tutorials_tbl 表中的所有记录。

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

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

行的内容分配给变量 $row，然后打印该行中的值。

注意– 当您想将数组值直接插入字符串时，请务必记住放置大括号。

在上面的示例中，常量MYSQL_ASSOC用作 PHP 函数mysql_fetch_array()的第二个参数，以便它以关联数组的形式返回行。使用关联数组，您可以使用名称而不是索引来访问字段。

PHP 提供了另一个名为mysql_fetch_assoc() 的函数，它也将行作为关联数组返回。

例子

以下示例使用 mysql_fetch_assoc() 函数显示 tutorial_tbl 表中的所有记录。

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

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

您还可以使用常量MYSQL_NUM作为 PHP 函数 mysql_fetch_array() 的第二个参数。这将导致函数返回一个带有数字索引的数组。

例子

尝试以下示例以使用 MYSQL_NUM 参数显示 tutorials_tbl 表中的所有记录。

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

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

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

以上三个例子都会产生相同的结果。

释放记忆

在每个 SELECT 语句结束时释放游标内存是一个很好的做法。这可以通过使用 PHP 函数mysql_free_result()来完成。下面的程序是展示它应该如何使用的示例。

例子

试试下面的例子 –

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

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

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

在获取数据时，您可以编写任意复杂的代码，但过程将与上述相同。

MySQL – WHERE 子句

我们已经看到了从 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 函数mysql_query() 中使用带有 WHERE CLAUSE 的相同 SQL SELECT 命令。此函数用于执行 SQL 命令，稍后可以使用另一个 PHP 函数mysql_fetch_array()来获取所有选定的数据。此函数以关联数组、数值数组或两者的形式返回一行。如果没有更多行，此函数返回 FALSE。

例子

以下示例将返回tutorials_tbl表中作者名为Sanjay 的所有记录–

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

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

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

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

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

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

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

MySQL – 更新查询

可能需要修改 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 函数mysql_query() 中使用带或不带 WHERE CLAUSE 的 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\n";
   mysql_close($conn);
?>

MySQL – 删除查询

如果要从任何 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 函数mysql_query() 中使用带或不带 WHERE CLAUSE 的 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\n";
   mysql_close($conn);
?>

MySQL – LIKE 子句

我们已经看到了从 MySQL 表中获取数据的 SQL SELECT命令。我们还可以使用称为WHERE子句的条件子句来选择所需的记录。

带有“等于”符号 (=) 的 WHERE 子句适用于我们想要进行精确匹配的情况。就像“tutorial_author = ‘Sanjay’”一样。但是可能需要过滤掉所有tutorial_author 名称应包含“jay”的结果。这可以使用SQL LIKE 子句和 WHERE 子句来处理。

如果 SQL LIKE 子句与 % 字符一起使用，那么它将像 UNIX 中的元字符 (*) 一样工作，同时在命令提示符下列出所有文件或目录。如果没有 % 字符，LIKE 子句与等号以及 WHERE 子句非常相似。

句法

以下代码块具有 SELECT 命令的通用 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 子句

您可以将 WHERE…LIKE 子句的类似语法用于 PHP 函数 – mysql_query()。此函数用于执行 SQL 命令，然后另一个 PHP 函数 – mysql_fetch_array()可用于获取所有选定的数据，如果 WHERE…LIKE 子句与 SELECT 命令一起使用。

但是如果 WHERE…LIKE 子句与 DELETE 或 UPDATE 命令一起使用，则不需要进一步的 PHP 函数调用。

例子

尝试以下示例以从tutorials_tbl表中返回作者姓名包含jay 的所有记录–

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

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

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

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

MySQL – 排序结果

我们已经看到了从 MySQL 表中获取数据的 SQL SELECT命令。当您选择行时，MySQL 服务器可以自由地以任何顺序返回它们，除非您通过说明如何对结果进行排序来指示它。但是，您可以通过添加一个ORDER BY子句来对结果集进行排序，该子句命名要排序的一列或多列。

句法

以下代码块是 SELECT 命令的通用 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 子句

您可以将 ORDER BY 子句的类似语法用于 PHP 函数 – mysql_query()。此函数用于执行 SQL 命令，稍后可以使用另一个 PHP 函数mysql_fetch_array()来获取所有选定的数据。

例子

尝试以下示例，该示例以教程作者的降序返回结果。

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

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

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

使用 MySQl 连接

在前面的章节中，我们一次从一个表中获取数据。这对于简单的应用来说已经足够了，但在大多数真实世界的 MySQL 使用中，您经常需要在单个查询中从多个表中获取数据。

您可以在单个 SQL 查询中使用多个表。在 MySQL 中加入的行为是指将两个或多个表粉碎成一个表。

您可以在 SELECT、UPDATE 和 DELETE 语句中使用 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 脚本中使用上述任何 SQL 查询。您只需要将 SQL 查询传递到 PHP 函数mysql_query() 中，然后您就会以通常的方式获取结果。

例子

以下示例 –

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

   $sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
      FROM tutorials_tbl a, tcount_tbl b
      WHERE a.tutorial_author = b.tutorial_author';

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

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

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

MySQL 左连接

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 中稍微有点复杂的概念，在做实际示例时会变得更加清晰。

处理 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 的列，请使用IS NULL或IS NOT NULL。

在命令提示符下使用 NULL 值

假设在 TUTORIALS 数据库中有一个名为tcount_tbl的表，它包含两列，即tutorial_author和tutorial_count，其中 NULL tutorial_count 表示该值未知。

例子

尝试以下示例 –

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

mysql> use TUTORIALS;
Database changed

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

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

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

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

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

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

mysql>

您可以看到 = 和!=不适用于 NULL 值，如下所示 –

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

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

要查找tutorial_count 列是否为NULL 的记录，应按以下程序编写查询。

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

在 PHP 脚本中处理 NULL 值

您可以使用if…else条件根据 NULL 值准备查询。

例子

以下示例从外部获取tutorial_count，然后将其与表中可用的值进行比较。

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

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

   if( isset($tutorial_count )) {
      $sql = 'SELECT tutorial_author, tutorial_count
         FROM  tcount_tbl
         WHERE tutorial_count = $tutorial_count';
   } else {
      $sql = 'SELECT tutorial_author, tutorial_count
         FROM  tcount_tbl
         WHERE tutorial_count IS $tutorial_count';
   }

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

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

MySQL – 正则表达式

您已经看到 MySQL 模式与LIKE …%匹配。MySQL 支持另一种基于正则表达式和REGEXP运算符的模式匹配操作。如果您了解 PHP 或 PERL，那么您很容易理解，因为这种匹配与编写正则表达式的脚本相同。

以下是模式表，可与REGEXP运算符一起使用。

Pattern 模式匹配什么
^ 字符串的开头
$ 字符串结束
. 任意单个字符
[…] 方括号之间列出的任何字符
[^…] 方括号之间未列出的任何字符
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主要用于 MySQL Transactions。

  • 成功完成事务后，应发出 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。

从源编译 MySQL 时，对 InnoDB 表的支持需要特定的编译参数。如果您的 MySQL 版本不支持 InnoDB，请要求您的 Internet 服务提供商构建支持 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 将包含与您第一次创建表时相同的两列，但不会具有相同的结构。这是因为默认情况下有新列添加到表的末尾。所以即使我最初是 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 子句。这意味着如果要重新定位表中的现有列，必须先将其删除，然后再将其添加到新位置。

更改（更改）列定义或名称

要更改列的定义，请将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)

添加和删​​除 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 testalter_tbl ADD INDEX (c);

您可以使用DROP子句和 ALTER 命令删除任何 INDEX 。

试试下面的例子来删除上面创建的索引。

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

您可以使用 DROP 子句和 ALTER 命令删除任何 INDEX。

添加和删​​除 PRIMARY KEY 的 ALTER 命令

您也可以以相同的方式添加主键。但请确保主键适用于非空的列。

以下代码块是在现有表中添加主键的示例。这将首先使列 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数据库服务器，那么临时表将一直存在，直到您关闭客户端或手动销毁该表。

例子

以下程序是一个示例，向您展示了临时表的用法。可以使用mysql_query()函数在 PHP 脚本中使用相同的代码。

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

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

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

当您发出SHOW TABLES命令时，您的临时表将不会在列表中列出。现在，如果您退出 MySQL 会话，然后发出SELECT命令，那么您将在数据库中找不到可用数据。甚至你的临时表也不存在。

删除临时表

默认情况下，当您的数据库连接终止时，MySQL 会删除所有临时表。如果您想在两者之间删除它们，那么您可以通过发出DROP TABLE命令来实现。

以下程序是删除临时表的示例 –

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

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

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
|   cucumber   |   100.25    |     90.00      |         2        |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist

MySQL – 克隆表

可能存在一种情况，当您需要表的精确副本而CREATE TABLE … SELECT不适合您的目的时，因为副本必须包含相同的索引、默认值等。

您可以按照以下步骤处理这种情况 –

  • 使用 SHOW CREATE TABLE 获取指定源表的结构、索引和所有内容的 CREATE TABLE 语句。

  • 修改语句，将表名更改为克隆表的表名并执行该语句。这样，您将拥有准确的克隆表。

  • 或者，如果您还需要复制表内容，也可以发出 INSERT INTO … SELECT 语句。

例子

尝试以下示例为tutorials_tbl创建克隆表。

步骤 1 – 获取有关表格的完整结构。

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

ERROR:
No query specified

步骤 2 – 重命名此表并创建另一个表。

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

第 3 步– 执行第 2步后，您将在数据库中创建一个克隆表。如果要从旧表复制数据，则可以使用 INSERT INTO… SELECT 语句来完成。

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

最后，您将拥有您想要的精确克隆表。

MySQL – 数据库信息

获取和使用 MySQL 元数据

您希望从 MySQL 获得三种类型的信息。

  • 关于查询结果的信息– 这包括受任何 SELECT、UPDATE 或 DELETE 语句影响的记录数。

  • 关于表和数据库的信息 – 这包括与表和数据库的结构有关的信息。

  • 关于 MySQL 服务器的信息– 这包括数据库服务器的状态、版本号等。

在 MySQL 提示符下很容易获取所有这些信息，但是在使用 PERL 或 PHP API 时，我们需要显式调用各种 API 来获取所有这些信息。

获取查询影响的行数

现在让我们看看如何获​​取这些信息。

PERL 示例

在 DBI 脚本中，受影响的行数由do()或execute()命令返回，具体取决于您如何执行查询。

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

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

PHP 示例

在 PHP 中，调用mysql_affected_rows()函数来找出查询更改了多少行。

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

列出表和数据库

列出数据库服务器可用的所有数据库和表非常容易。如果您没有足够的权限，您的结果可能为空。

除了以下代码块中显示的方法外，您还可以使用SHOW TABLES或SHOW DATABASES查询来获取 PHP 或 PERL 中的表或数据库的列表。

PERL 示例

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

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

PHP 示例

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

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

获取服务器元数据

MySQL 中有一些重要的命令可以在 MySQL 提示符下执行，也可以通过使用任何脚本（如 PHP）来获取有关数据库服务器的各种重要信息。

Sr.No. 命令和描述
1

SELECT VERSION( )

服务器版本字符串
2

SELECT DATABASE( )

当前数据库名称（如果没有则为空）
3

SELECT USER( )

当前用户名
4

SHOW STATUS

服务器状态指示灯
5

SHOW VARIABLES

服务器配置变量

使用 MySQL 序列

序列是一组整数 1, 2, 3, … ，它们是根据特定需求按顺序生成的。序列在数据库中经常使用，因为许多应用程序要求表中的每一行都包含一个唯一值，而序列提供了一种简单的方法来生成它们。

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

使用 AUTO_INCREMENT 列

在 MySQL 中使用 Sequences 的最简单方法是将列定义为AUTO_INCREMENT，其余的事情交给 MySQL 处理。

例子

试试下面的例子。这将创建表，然后它将在此表中插入几行，不需要提供记录 ID，因为它是由 MySQL 自动递增的。

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

获取 AUTO_INCREMENT 值

该LAST_INSERT_ID（）是SQL的功能，让你可以从了解如何发出SQL语句的任何客户端中使用它。否则，PERL 和 PHP 脚本提供专有函数来检索最后一条记录的自动递增值。

PERL 示例

使用mysql_insertid属性获取查询生成的AUTO_INCREMENT值。此属性可通过数据库句柄或语句句柄访问，具体取决于您发出查询的方式。

以下示例通过数据库句柄引用它。

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

PHP 示例

发出生成 AUTO_INCREMENT 值的查询后，通过调用mysql_insert_id()命令检索该值。

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

重新编号现有序列

可能会出现这样的情况：您已从表中删除了许多记录，并且您想对所有记录重新排序。这可以通过使用一个简单的技巧来完成，但是如果您的表与另一个表有连接，您应该非常小心地这样做。

如果您确定 AUTO_INCREMENT 列的重新排序是不可避免的，那么这样做的方法是从表中删除该列，然后再次添加它。

以下示例显示如何使用此技术对表中的id 值重新编号。

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

以特定值启动序列

默认情况下，MySQL 将从 1 开始序列，但您也可以在创建表时指定任何其他数字。

以下程序是一个示例，显示 MySQL 如何从 100 开始序列。

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

或者，您可以创建表，然后使用ALTER TABLE命令设置初始序列值。

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

MySQL – 处理重复

通常，表或结果集有时包含重复记录。大多数情况下是允许的，但有时需要停止重复记录。需要识别重复记录并将其从表中删除。本章将介绍如何防止表中出现重复记录，以及如何删除已经存在的重复记录。

防止表中出现重复项

您可以在具有适当字段的表上使用PRIMARY KEY或UNIQUE索引来停止重复记录。

让我们举个例子——下表不包含这样的索引或主键，所以它会允许first_name和last_name 的重复记录。

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

要防止在此表中创建多个具有相同名字和姓氏值的记录，请将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 或 PRIMARY KEY。即使此表已经可用，您也可以使用此技术删除重复记录，并且将来也很安全。

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

MySQL – 和 SQL 注入

如果您通过网页获取用户输入并将其插入到 MySQL 数据库中，那么您有可能对称为SQL 注入的安全问题敞开心扉。本章将教您如何帮助防止这种情况发生，并帮助您保护脚本和 MySQL 语句。

SQL 注入通常发生在您向用户询问输入时，例如他们的姓名，而不是姓名，他们给您一条 MySQL 语句，您将在不知不觉中在数据库上运行该语句。

永远不要相信用户提供的数据，只有在验证后才处理这些数据；通常，这是通过模式匹配完成的。在下面的示例中，用户名被限制为字母数字字符加下划线和 8 到 20 个字符之间的长度 – 根据需要修改这些规则。

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

为了演示这个问题，请考虑以下摘录。

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

函数调用应该从用户表中检索一条记录，其中名称列与用户指定的名称相匹配。在正常情况下， $name 将只包含字母数字字符和空格。但是在这里，通过向$name附加一个全新的查询，对数据库的调用变成了一场灾难。注入的 DELETE 查询从用户中删除所有记录。

幸运的是，如果您使用 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 文件名组合在一起。默认输出格式与 LOAD DATA 命令的格式相同。因此，以下语句将tutorials_tbl表作为制表符分隔、换行符终止的文件导出到/tmp/tutorials.txt。

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

您可以使用各种选项更改输出格式，以指示如何引用和分隔列和记录。要使用 CRLF 终止的行以 CSV 格式导出 tutorial_tbl 表，请使用以下代码。

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

在SELECT … INTO OUTFILE具有以下属性-

  • 输出文件由 MySQL 服务器直接创建，因此文件名应指示您希望将文件写入服务器主机上的位置。没有类似于LOAD DATA的LOCAL版本的语句的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 服务器复制到另一台，请使用带有数据库名称和表名称的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 '\r\n';

  • LOAD DATA 命令假定数据文件中的列与表中的列具有相同的顺序。如果不是这样，您可以指定一个列表来指示应将数据文件列加载到哪些表列中。假设您的表有 a、b 和 c 列，但数据文件中的连续列对应于 b、c 和 a 列。

您可以按以下代码块所示加载文件。

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

使用 mysqlimport 导入数据

MySQL 还包括一个名为mysqlimport的实用程序，它充当 LOAD DATA 的包装器，以便您可以直接从命令行加载输入文件。

要将dump.txt 中的数据加载到mytbl 中，请在 UNIX 提示符下使用以下命令。

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

如果使用mysqlimport，命令行选项提供格式说明符。与前面两个LOAD DATA语句对应的mysqlimport命令如下面的代码块所示。

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

指定选项的顺序对于 mysqlimport 无关紧要，只是它们都应位于数据库名称之前。

该mysqlimport的语句使用–columns选项来指定列的顺序-

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

处理引号和特殊字符

FIELDS 子句可以指定除TERMINATED BY之外的其他格式选项。默认情况下，LOAD DATA 假定值不带引号，并将反斜杠 (\) 解释为特殊字符的转义字符。要显式指示值引用字符，请使用ENCLOSED BY命令。MySQL 会在输入处理期间从数据值的末尾去除该字符。要更改默认转义字符，请使用ESCAPED BY。

当您指定 ENCLOSED BY 以指示应从数据值中去除引号字符时，可以通过将引号字符加倍或在其前面加上转义字符来将引号字符逐字包含在数据值中。

例如，如果引号和转义字符是 ” 和 \，则输入值“a””b\”c”将被解释为a”b”c。

对于mysqlimport，用于指定引号和转义值的相应命令行选项是–fields-enclosed-by和–fields-escaped-by。

觉得文章有用？

点个广告表达一下你的爱意吧 ！😁