Mariadb 简明教程

MariaDB - Quick Guide

MariaDB - Introduction

数据库应用程序独立于主应用程序而存在,并存储数据集合。每个数据库都使用一个或多个 API 来创建、访问、管理、搜索和复制它包含的数据。

A database application exists separate from the main application and stores data collections. Every database employs one or multiple APIs for the creation, access, management, search, and replication of the data it contains.

数据库还使用非关系数据源,如对象或文件。但是,对于大型数据集而言,数据库被证明是最佳选择,而使用其他数据源会使检索和写入变得缓慢。

Databases also use non-relational data sources such as objects or files. However, databases prove the best option for large datasets, which would suffer from slow retrieval and writing with other data sources.

关系数据库管理系统或 RDBMS 将数据存储在不同的表中。通过使用主键和外键建立这些表之间的关系。

Relational database management systems, or RDBMS, store data in various tables.Relationships between these tables are established using primary keys and foreign keys.

RDBMS 提供以下功能

RDBMS offers the following features −

  1. They enable you to implement a data source with tables, columns, and indices.

  2. They ensure the integrity of references across rows of multiple tables.

  3. They automatically update indices.

  4. They interpret SQL queries and operations in manipulating or sourcing data from tables.

RDBMS Terminology

在我们开始讨论 MariaDB 之前,让我们回顾一下与数据库相关的一些术语。

Before we begin our discussion of MariaDB, let us review a few terms related to databases.

  1. Database − A database is a data source consisting of tables holding related data.

  2. Table − A table, meaning a spreadsheet, is a matrix containing data.

  3. Column − A column, meaning data element, is a structure holding data of one type; for example, shipping dates.

  4. Row − A row is a structure grouping related data; for example, data for a customer. It is also known as a tuple, entry, or record.

  5. Redundancy − This term refers to storing data twice in order to accelerate the system.

  6. Primary Key − This refers to a unique, identifying value. This value cannot appear twice within a table, and there is only one row associated with it.

  7. Foreign Key − A foreign key serves as a link between two tables.

  8. Compound Key − A compound key, or composite key, is a key that refers to multiple columns. It refers to multiple columns due to a column lacking a unique quality.

  9. Index − An index is virtually identical to the index of a book.

  10. Referential Integrity − This term refers to ensuring all foreign key values point to existing rows.

MariaDB Database

MariaDB 是由 MySQL 的原开发者创建的 MySQL 一个流行的分支。它源自对甲骨文收购 MySQL 的担忧。它为小型数据处理任务和企业需求提供支持。它旨在作为 MySQL 的模块化替代品,只需卸载 MySQL 并安装 MariaDB。MariaDB 提供了与 MySQL 相同的功能,并提供了更多功能。

MariaDB is a popular fork of MySQL created by MySQL’s original developers. It grew out of concerns related to MySQL’s acquisition by Oracle. It offers support for both small data processing tasks and enterprise needs. It aims to be a drop-in replacement for MySQL requiring only a simple uninstall of MySQL and an install of MariaDB. MariaDB offers the same features of MySQL and much more.

Key Features of MariaDB

MariaDB 的重要功能包括:

The important features of MariaDB are −

  1. All of MariaDB is under GPL, LGPL, or BSD.

  2. MariaDB includes a wide selection of storage engines, including high-performance storage engines, for working with other RDBMS data sources.

  3. MariaDB uses a standard and popular querying language.

  4. MariaDB runs on a number of operating systems and supports a wide variety of programming languages.

  5. MariaDB offers support for PHP, one of the most popular web development languages.

  6. MariaDB offers Galera cluster technology.

  7. MariaDB also offers many operations and commands unavailable in MySQL, and eliminates/replaces features impacting performance negatively.

Getting Started

在开始本教程之前,请确保你已经具备了 PHP 和 HTML 的一些基本知识,特别是我们 PHP 和 HTML 教程中讨论的内容。

Before you begin this tutorial, make sure you have some basic knowledge of PHP and HTML, specifically material discussed in our PHP and HTML tutorials.

本指南重点介绍了在 PHP 环境中使用 MariaDB,因此我们的示例将对 PHP 开发人员最有用。

This guide focuses on use of MariaDB in a PHP environment, so our examples will be most useful for PHP developers.

如果你不熟悉或需要查看,我们强烈建议你查看我们的 PHP 教程。

We strongly recommend reviewing our PHP Tutorial if you lack familiarity or need to review.

MariaDB - Installation

MariaDB 的所有下载都位于官方 MariaDB 基金会网站的 Download 部分。单击你想要的版本的链接,然后会显示一个可用于多个操作系统、体系结构和安装文件类型的下载列表。

All downloads for MariaDB are located in the Download section of the official MariaDB foundation website. Click the link to the version you would like, and a list of downloads for multiple operating systems, architectures, and installation file types is displayed.

Installing on LINUX/UNIX

如果你对 Linux/Unix 系统有深入的了解,只需下载源来构建安装。我们建议的安装方式是使用发行版包。MariaDB 为以下 Linux/Unix 发行版提供包:

If you have intimate knowledge of Linux/Unix systems, simply download source to build your install. Our recommended way of installing is to utilize distribution packages. MariaDB offers packages for the following Linux/Unix distributions −

  1. RedHat/CentOS/Fedora

  2. Debian/Ubuntu

以下发行版在其存储库中包含 MariaDB 包:

The following distributions include a MariaDB package in their repositories −

  1. openSUSE

  2. Arch Linux

  3. Mageia

  4. Mint

  5. Slackware

要在 Ubuntu 环境中安装,请按照以下步骤操作 -

Follow these steps to install in an Ubuntu environment −

Step 1 - 以 root 用户身份登录。

Step 1 − Login as a root user.

Step 2 - 导航到包含 MariaDB 包的目录。

Step 2 − Navigate to the directory containing the MariaDB package.

Step 3 - 使用以下代码导入 GnuPG 签名密钥 -

Step 3 − Import the GnuPG signing key with the following code −

sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

Step 4 - 将 MariaDB 添加到 sources.list 文件中。打开该文件,并添加以下代码 -

Step 4 − Add MariaDB to the sources.list file. Open the file, and add the following code −

sudo add-apt-repository 'deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntuprecise main'

Step 5 - 使用以下内容刷新系统 -

Step 5 − Refresh the system with the following −

sudo apt-get update

Step 6 - 使用以下内容安装 MariaDB -

Step 6 − Install MariaDB with the following −

sudo apt-get install mariadb-server

Installing on Windows

在找到并下载自动安装文件 (MSI) 后,只需双击该文件即可开始安装。安装向导将引导您完成安装的每一步和任何必需的设置。

After locating and downloading an automated install file (MSI), simply double click the file to start the installation. The installation wizard will walk you through every step of installation and any necessary settings.

通过从命令提示符启动安装来测试安装。导航到安装位置,通常在该目录中,并在提示符处键入以下内容 -

Test the installation by starting it from the command prompt. Navigate to the location of the installation, typically in the directory, and type the following at the prompt −

mysqld.exe --console

如果安装成功,您将看到与启动相关的消息。如果未显示此消息,您可能存在权限问题。确保您的用户帐户可以访问该应用程序。在 Windows 环境中可用于 MariaDB 管理的图形客户端。如果您觉得命令行不方便或繁琐,请务必尝试使用它们。

If the installation is successful, you will see messages related to startup. If this does not appear, you may have permission issues. Ensure that your user account can access the application. Graphical clients are available for MariaDB administration in the Windows environment. If you find the command line uncomfortable or cumbersome, be sure to experiment with them.

Testing the Installation

执行一些简单任务来确认 MariaDB 的功能和安装。

Perform a few simple tasks to confirm the functioning and installation of MariaDB.

Use the Admin Utility to Get Server Status

Use the Admin Utility to Get Server Status

使用 mysqladmin 二进制文件查看服务器版本。

View the server version with the mysqladmin binary.

[root@host]# mysqladmin --version

它应该显示版本、发行版、操作系统和架构。如果您没有看到该类型的输出,请检查您的安装是否存在问题。

It should display the version, distribution, operating system, and architecture. If you do not see the output of that type, examine your installation for issues.

Execute Simple Commands with a Client

Execute Simple Commands with a Client

调出 MariaDB 的命令提示符。这应该使您连接到 MariaDB 并允许执行命令。输入以下简单命令 -

Bring up the command prompt for MariaDB. This should connect you to MariaDB and allow execution of commands. Enter a simple command as follows −

mysql> SHOW DATABASES;

Post- Installation

成功安装 MariaDB 后,设置 root 密码。新安装的密码将为空。输入以下内容设置新密码 -

After successful installation of MariaDB, set a root password. A fresh install will have a blank password. Enter the following to set the new password −

mysqladmin -u root password "[enter your password here]";

输入以下内容以使用您的新凭据连接到服务器 -

Enter the following to connect to the server with your new credentials −

mysql -u root -p
Enter password:*******

Upgrading on Windows

如果您已经在 Windows 系统上安装了 MySQL,并且想要升级到 MariaDB,请不要卸载 MySQL 并安装 MariaDB。这将与现有数据库产生冲突。相反,您必须安装 MariaDB,然后使用 Windows 安装文件中的升级向导。

If you already have MySQL installed on your Windows system, and want to upgrade to MariaDB; do not uninstall MySQL and install MariaDB. This will cause a conflict with the existing database. You must instead install MariaDB, and then use the upgrade wizard in the Windows installation file.

你的 MySQL my.cnf 文件的选项应与 MariaDB 兼容。但是,MariaDB 有许多 MySQL 中没有的功能。

The options of your MySQL my.cnf file should work with MariaDB. However, MariaDB has many features, which are not found in MySQL.

考虑 my.cnf 文件中的以下冲突——

Consider the following conflicts in your my.cnf file −

  1. MariaDB uses Aria storage engine by default for temporary files. If you have a lot of temporary files, modify key buffer size if you do not use MyISAM tables.

  2. If your applications connect/disconnect frequently, alter the thread cache size.

  3. If you use over 100 connections, use the thread pool.

Compatibility

MySQL 和 MariaDB 基本上是相同的。但是,两者之间有足够的差异会导致升级问题。请参阅 MariaDB Knowledge Base. 中的更多关键差异

MySQL and MariaDB are essentially identical. However, there are enough differences to create issues in upgradation. Review more of these key differences in the MariaDB Knowledge Base.

MariaDB - Administration

在尝试运行 MariaDB 之前,先确定其当前状态,正在运行还是关闭。有三种启动和停止 MariaDB 的方法——

Before attempting to run MariaDB, first determine its current state, running or shutdown. There are three options for starting and stopping MariaDB −

  1. Run mysqld (the MariaDB binary).

  2. Run the mysqld_safe startup script.

  3. Run the mysql.server startup script.

如果你在非标准位置安装了 MariaDB,你可能需要编辑脚本文件中的位置信息。通过使用脚本来简单地添加一个“stop”参数来停止 MariaDB。

If you installed MariaDB in a non-standard location, you may have to edit location information in the script files. Stop MariaDB by simply adding a “stop” parameter with the script.

如果你想在 Linux 下自动启动它,将启动脚本添加到你的 init 系统。每个发行版都有不同的程序。请参阅你的系统文档。

If you would like to start it automatically under Linux, add startup scripts to your init system. Each distribution has a different procedure. Refer to your system documentation.

Creating a User Account

使用以下代码创建新的用户帐户——

Create a new user account with the following code −

CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'userpassword';

此代码将一行添加到用户表中,没有任何权限。你也可以选择使用密码的哈希值。使用以下代码授予用户权限——

This code adds a row to the user table with no privileges. You also have the option to use a hash value for the password. Grant the user privileges with the following code −

GRANT SELECT, INSERT, UPDATE, DELETE ON database1 TO 'newusername'@'localhost';

其他权限包括 MariaDB 中几乎所有可能的命令或操作。在创建用户后,执行“FLUSH PRIVILEGES”命令以刷新授权表。这允许用户帐户被使用。

Other privileges include just about every command or operation possible in MariaDB. After creating a user, execute a “FLUSH PRIVILEGES” command in order to refresh grant tables. This allows the user account to be used.

The Configuration File

在 Unix/Linux 上构建后,配置文件“/etc/mysql/my.cnf”应被编辑为如下所示——

After a build on Unix/Linux, the configuration file “/etc/mysql/my.cnf” should be edited to appear as follow −

# Example mysql config file.
# You can copy this to one of:
# /etc/my.cnf to set global options,
# /mysql-data-dir/my.cnf to get server specific options or
# ~/my.cnf for user specific options.

#

# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# This will be passed to all mysql clients
[client]
#password = my_password
#port = 3306
#socket = /tmp/mysql.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
#port = 3306
#socket = /tmp/mysql.sock
temp-pool

# The following three entries caused mysqld 10.0.1-MariaDB (and possibly other
   versions) to abort...
# skip-locking
# set-variable = key_buffer = 16M
# set-variable = thread_cache = 4

loose-innodb_data_file_path = ibdata1:1000M
loose-mutex-deadlock-detector
gdb

######### Fix the two following paths

# Where you want to have your database
data = /path/to/data/dir

# Where you have your mysql/MariaDB source + sql/share/english
language = /path/to/src/dir/sql/share/english

[mysqldump]
quick
MariaDB
8
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash

[myisamchk]
set-variable = key_buffer = 128M

编辑行“data= ”和“language= ”以匹配你的环境。

Edit the lines “data= ” and “language= ” to match your environment.

在修改文件后,导航到源目录并执行以下操作——

After file modification, navigate to the source directory and execute the following −

./scripts/mysql_install_db --srcdir = $PWD --datadir = /path/to/data/dir --
   user = $LOGNAME

如果你已将数据目录添加到配置文件中,省略“$PWD”变量。确保在运行 MariaDB 版本 10.0.1 时使用“$LOGNAME”。

Omit the “$PWD” variable if you added datadir to the configuration file. Ensure “$LOGNAME” is used when running version 10.0.1 of MariaDB.

Administration Commands

使用 MariaDB 时会定期使用的重要命令列表:

Review the following list of important commands you will regularly use when working with MariaDB −

  1. USE [database name] − Sets the current default database.

  2. SHOW DATABASES − Lists the databases currently on the server.

  3. SHOW TABLES − Lists all non-temporary tables.

  4. SHOW COLUMNS FROM [table name] − Provides column information pertaining to the specified table.

  5. SHOW INDEX FROM TABLENAME [table name] − Provides table index information relating to the specified table.

  6. SHOW TABLE STATUS LIKE [table name]\G – − Provides tables with information about non-temporary tables, and the pattern that appears after the LIKE clause is used to fetch table names.

MariaDB - PHP Syntax

MariaDB 与广泛的编程语言和框架(例如 PHP、C#、JavaScript、Ruby on Rails、Django 等)合作良好。PHP 由于其简单性和历史足迹,仍然是所有可用语言中最流行的语言。本指南将重点关注与 MariaDB 合作的 PHP。

MariaDB partners well with a wide variety of programming languages and frameworks such as PHP, C#, JavaScript, Ruby on Rails, Django, and more. PHP remains the most popular of all available languages due to its simplicity and historical footprint. This guide will focus on PHP partnered with MariaDB.

PHP 提供了一组用于处理 MySQL 数据库的功能。这些功能执行诸如访问数据库或执行操作之类的任务,并且它们与 MariaDB 完全兼容。只需像调用任何其他 PHP 函数一样调用这些函数。

PHP provides a selection of functions for working with the MySQL database. These functions perform tasks like accessing it or performing operations, and they are fully compatible with MariaDB. Simply call these functions as you would call any other PHP function.

您用于 MariaDB 的 PHP 函数遵循以下格式:

The PHP functions you will use for MariaDB conform to the following format −

mysql_function(value,value,...);

函数的第二部分指定其动作。本指南中使用的两个函数如下:

The second part of the function specifies its action. Two of the functions used in this guide are as follows −

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

以下示例演示了对 MariaDB 函数的 PHP 调用的常规语法:

The following example demonstrates the general syntax of a PHP call to a MariaDB function −

<html>
   <head>
      <title>PHP and MariaDB</title>
   </head>

   <body>
      <?php
         $retval = mysql_function(value, [value,...]);

         if( !$retval ) {
            die ( "Error: Error message here" );
         }
         // MariaDB or PHP Statements
      ?>
   </body>
</html>

在下一节中,我们将使用 PHP 函数检查基本的 MariaDB 任务。

In the next section, we will examine essential MariaDB tasks, using PHP functions.

MariaDB - Connection

与 MariaDB 建立连接的一种方法是使用命令提示符中的 mysql 二进制文件。

One way to establish a connection with MariaDB consists of using the mysql binary at the command prompt.

MYSQL Binary

查看下面给出的示例。

Review an example given below.

[root@host]# mysql -u root -p

Enter password:******

上面给出的代码连接到 MariaDB 并提供用于执行 SQL 命令的命令提示符。输入代码后,会显示一条欢迎消息,表明连接成功,并显示版本号。

The code given above connects to MariaDB and provides a command prompt for executing SQL commands. After entering the code, a welcome message should appear indicating a successful connection, with the version number displayed.

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 122323232
Server version: 5.5.40-MariaDB-log

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

该示例使用 root 访问,但具有权限的任何用户当然都可以访问 MariaDB 提示并执行操作。

The example uses root access, but any user with privileges can of course access the MariaDB prompt and perform operations.

通过 exit 命令断开与 MariaDB 的连接,如下所示:

Disconnect from MariaDB through the exit command as follows −

mysql> exit

PHP Connection Script

连接到 MariaDB 并断开连接的另一种方法是使用 PHP 脚本。PHP 提供 mysql_connect() 函数用于打开数据库连接。它使用五个可选参数,并在连接成功后返回 MariaDB 链接标识符,或在连接不成功时返回 false。它还提供了 mysql_close() 函数用于关闭数据库连接,该函数使用单个参数。

Another way to connect to and disconnect from MariaDB consists of employing a PHP script. PHP provides the mysql_connect() function for opening a database connection. It uses five optional parameters, and returns a MariaDB link identifier after a successful connection, or a false on unsuccessful connection. It also provides the mysql_close() function for closing database connections, which uses a single parameter.

Syntax

审阅以下 PHP 连接脚本语法 −

Review the following PHP connection script syntax −

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

参数说明如下 −

The description of the parameters is given below −

Sr.No

Parameter & Description

1

server This optional parameter specifies the host name running the database server. Its default value is “localhost:.3036.”

2

user This optional parameter specifies the username accessing the database. Its default value is the owner of the server.

3

passwd This optional parameter specifies the user’s password. Its default value is blank.

4

new_link This optional parameter specifies that on a second call to mysql_connect() with identical arguments, rather than a new connection, the identifier of the current connection will be returned.

5

client flags This optional parameter uses a combination of the following constant values − MYSQL_CLIENT_SSL − It uses ssl encryption. MYSQL_CLIENT_COMPRESS − It uses compression protocol. MYSQL_CLIENT_IGNORE_SPACE − It permits space after function names. MYSQL_CLIENT_INTERACTIVE − It permits interactive timeout seconds of inactivity prior to closing the connection.

审阅下面给出的 PHP 断开连接脚本语法 −

Review the PHP disconnection script syntax given below −

bool mysql_close ( resource $link_identifier );

如果您省略资源,最近打开的资源将关闭。它在成功关闭时返回 true 值,或者在失败时返回 false 值。

If you omit the resource, the most recent opened resource will close. It returns a value of true on a successful close, or false.

尝试以下示例代码以连接至 MariaDB 服务器 −

Try the following example code to connect with a MariaDB server −

<html>
   <head>
      <title>Connect to MariaDB Server</title>
   </head>

   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'guest1';
         $dbpass = 'guest1a';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);

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

         echo 'Connected successfully';
         mysql_close($conn);
      ?>
   </body>
</html>

成功连接后,您将看到以下输出 −

On successful connection, you will see the following output −

mysql> Connected successfully

MariaDB - Create Database

在 MariaDB 中创建或删除数据库通常需要特权,这些特权通常只授予 root 用户或管理员。在这些帐户下,您有两种创建数据库的选项 − mysqladmin 二进制文件和 PHP 脚本。

Creation or deletion of databases in MariaDB requires privileges typically only given to root users or admins. Under these accounts, you have two options for creating a database − the mysqladmin binary and a PHP script.

mysqladmin binary

以下示例演示了使用 mysqladmin 二进制文件创建名为 Products 的数据库 −

The following example demonstrates the use of the mysqladmin binary in creating a database with the name Products

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

PHP Create Database Script

PHP 在创建 MariaDB 数据库时采用 mysql_query 函数。该函数使用两个参数,一个可选,成功时返回“true”值,失败时返回“false”值。

PHP employs the mysql_query function in creating a MariaDB database. The function uses two parameters, one optional, and returns either a value of “true” when successful, or “false” when not.

Syntax

审阅以下 create database script 语法 −

Review the following create database script syntax −

bool mysql_query( sql, connection );

参数说明如下 −

The description of the parameters is given below −

S.No

Parameter & Description

1

sql This required parameter consists of the SQL query needed to perform the operation.

2

connection When not specified, this optional parameter uses the most recent connection used.

尝试以下示例代码以创建数据库 −

Try the following example code for creating a database −

<html>
   <head>
      <title>Create a MariaDB 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 PRODUCTS';
         $retval = mysql_query( $sql, $conn );

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

         echo "Database PRODUCTS created successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

成功删除后,您将看到以下输出 −

On successful deletion, you will see the following output −

mysql> Database PRODUCTS created successfully
mysql> SHOW DATABASES;
+-----------------------+
| Database              |
+-----------------------+
| PRODUCTS              |
+-----------------------+

MariaDB - Drop Database

在 MariaDB 中创建或删除数据库需要拥有权限,通常只授予 root 用户或管理员。 在这些帐户下,您可以使用两种方法来删除数据库:mysqladmin 二进制文件和 PHP 脚本。

Creation or deletion of databases in MariaDB requires privileges, typically, only given to root users or admins. Under these accounts, you have two options for deleting a database: the mysqladmin binary and a PHP script.

请注意,已删除的数据库无法恢复,所以在执行此操作时要小心。此外,用于删除的 PHP 脚本在删除前会 not 要求您确认。

Note that deleted databases are irrecoverable, so exercise care in performing this operation. Furthermore, PHP scripts used for deletion do not prompt you with a confirmation before the deletion.

mysqladmin binary

以下示例演示如何使用 mysqladmin 二进制文件删除现有数据库 −

The following example demonstrates how to use the mysqladmin binary to delete an existing database −

[root@host]# mysqladmin -u root -p drop PRODUCTS
Enter password:******
mysql> DROP PRODUCTS
ERROR 1008 (HY000): Can't drop database 'PRODUCTS'; database doesn't exist

PHP Drop Database Script

PHP 使用 mysql_query 函数删除 MariaDB 数据库。该函数使用两个参数,一个可选参数,并在成功时返回 “true” 值,在不成功时返回 “false” 值。

PHP employs the mysql_query function in deleting MariaDB databases. The function uses two parameters, one optional, and returns either a value of “true” when successful, or “false” when not.

Syntax

查看以下删除数据库脚本语法 −

Review the following drop database script syntax −

bool mysql_query( sql, connection );

参数说明如下 −

The description of the parameters is given below −

Sr.No

Parameter & Description

1

sql This required parameter consists of the SQL query needed to perform the operation.

2

connection When not specified, this optional parameter uses the most recent connection used.

尝试以下示例代码来删除数据库 −

Try the following example code for deleting a database −

<html>
   <head>
      <title>Delete a MariaDB 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 PRODUCTS';
         $retval = mysql_query( $sql, $conn );

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

         echo "Database PRODUCTS deleted successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

成功删除后,您将看到以下输出 −

On successful deletion, you will see the following output −

mysql> Database PRODUCTS deleted successfully

MariaDB - Select Database

连接到 MariaDB 后,您必须选择一个数据库以供使用,因为可能存在多个数据库。有两种方法可以执行此任务:从命令提示符或通过 PHP 脚本。

After connecting to MariaDB, you must select a database to work with because many databases may exist. There are two ways to perform this task: from the command prompt or through a PHP script.

The Command Prompt

要从命令提示符选择一个数据库,只需使用 SQL 命令 ‘use’

In choosing a database at the command prompt, simply utilize the SQL command ‘use’

[root@host]# mysql -u root -p

Enter password:******

mysql> use PRODUCTS;

Database changed

mysql> SELECT database();
+-------------------------+
| Database                |
+-------------------------+
| PRODUCTS                |
+-------------------------+

选择数据库后,所有后续命令都将在所选数据库上操作。

Once you select a database, all subsequent commands will operate on the chosen database.

Note − 所有名称(例如,数据库、表、字段)都区分大小写。确保命令符合正确的格式。

Note − All names (e.g., database, table, fields) are case sensitive. Ensure commands conform to the proper case.

PHP Select Database Script

PHP 提供 mysql_select_db 函数来进行数据库选择。该函数使用两个参数,一个可选参数,并在成功选择时返回 “true” 值,在失败时返回 “false” 值。

PHP provides the mysql_select_db function for database selection. The function uses two parameters, one optional, and returns a value of “true” on successful selection, or false on failure.

Syntax

查看以下选择数据库脚本语法。

Review the following select database script syntax.

bool mysql_select_db( db_name, connection );

参数说明如下 −

The description of the parameters is given below −

S.No

Parameter & Description

1

db_name This required parameter specifies the name of the database to use.

2

connection When not specified, this optional parameter uses the most recent connection used.

尝试以下示例代码来选择一个数据库 −

Try the following example code for selecting a database −

<html>
   <head>
      <title>Select a MariaDB Database</title>
   </head>

   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'guest1';
         $dbpass = 'guest1a';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);

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

         mysql_select_db( 'PRODUCTS' );
         mysql_close($conn);
      ?>
   </body>
</html>

成功选择后,您将看到以下输出 −

On successful selection, you will see the following output −

mysql> Connected successfully

MariaDB - Data Types

良好的字段定义对于优化数据库至关重要。理想的方法要求您仅使用所需类型和大小的字段。例如,如果您只使用一个字段,宽度为五个字符,则不要定义一个宽度为 20 个字符的字段。字段(或列)类型也称为数据类型,因为数据类型存储在字段中。

Good field definitions are essential for the optimization of your database. The ideal approach requires that you exclusively use a field of the type and size needed. For example, if you will only use a field, five-characters wide, do not define a field, 20-characters wide. Field (or column) types are also known as data types given the data types stored within the field.

MariaDB 数据类型可以分为数值、日期和时间以及字符串值。

MariaDB data types can be categorized as numeric, date and time, and string values.

Numeric Data Types

MariaDB 支持的数值数据类型如下 −

The numeric data types supported by MariaDB are as follows −

  1. TINYINT − This data type represents small integers falling within the signed range of -128 to 127, and the unsigned range of 0 to 255.

  2. BOOLEAN − This data type associates a value 0 with “false,” and a value 1 with “true.”

  3. SMALLINT − This data type represents integers within the signed range of -32768 to 32768, and the unsigned range of 0 to 65535.

  4. MEDIUMINT − This data type represents integers in the signed range of -8388608 to 8388607, and the unsigned range of 0 to 16777215.

  5. INT(also INTEGER) − This data type represents an integer of normal size. When marked as unsigned, the range spans 0 to 4294967295. When signed (the default setting), the range spans -2147483648 to 2147483647. When a column is set to ZEROFILL( an unsigned state), all its values are prepended by zeros to place M digits in the INT value.

  6. BIGINT − This data type represents integers within the signed range of 9223372036854775808 to 9223372036854775807, and the unsigned range of 0 to 18446744073709551615.

  7. DECIMAL( also DEC, NUMERIC, FIXED)− This data type represents precise fixed-point numbers, with M specifying its digits and D specifying the digits after the decimal. The M value does not add “-” or the decimal point. If D is set to 0, no decimal or fraction part appears and the value will be rounded to the nearest DECIMAL on INSERT. The maximum permitted digits is 65, and the maximum for decimals is 30. Default value for M on omission is 10, and 0 for D on omission.

  8. FLOAT − This data type represents a small, floating-point number of the value 0 or a number within the following ranges − -3.402823466E+38 to -1.175494351E-38 1.175494351E-38 to 3.402823466E+38

  9. DOUBLE (also REAL and DOUBLE PRECISION) − This data type represents normal-size, floating-point numbers of the value 0 or within the following ranges − -1.7976931348623157E+308 to -2.2250738585072014E-308 2.2250738585072014E-308 to 1.7976931348623157E+308

  10. BIT − This data type represents bit fields with M specifying the number of bits per value. On omission of M, the default is 1. Bit values can be applied with “ b’[value]’” in which value represents bit value in 0s and 1s. Zero-padding occurs automatically from the left for full length; for example, “10” becomes “0010.”

Date and Time Data Types

MariaDB 支持的日期和时间数据类型如下 −

The date and time data types supported by MariaDB are as follows −

  1. DATE − This data type represents a date range of “1000-01-01” to “9999-12-31,” and uses the “YYYY-MM-DD” date format.

  2. TIME − This data type represents a time range of “-838:59:59.999999” to “838:59:59.999999.”

  3. DATETIME − This data type represents the range “1000-01-01 00:00:00.000000” to “9999-12-31 23:59:59.999999.” It uses the “YYYY-MM-DD HH:MM:SS” format.

  4. TIMESTAMP − This data type represents a timestamp of the “YYYY-MM-DD HH:MM:DD” format. It mainly finds use in detailing the time of database modifications, e.g., insertion or update.

  5. YEAR − This data type represents a year in 4-digit format. The four-digit format allows values in the range of 1901 to 2155, and 0000.

String DataTypes

MariaDB 支持的字符串类型值如下 −

The string type values supported by MariaDB are as follows −

  1. String literals − This data type represents character sequences enclosed by quotes.

  2. CHAR − This data type represents a right-padded, fixed-length string containing spaces of specified length. M represents column length of characters in a range of 0 to 255, its default value is 1.

  3. VARCHAR − This data type represents a variable-length string, with an M range (maximum column length) of 0 to 65535.

  4. BINARY − This data type represents binary byte strings, with M as the column length in bytes.

  5. VARBINARY − This data type represents binary byte strings of variable length, with M as column length.

  6. TINYBLOB − This data type represents a blob column with a maximum length of 255 (28 - 1) bytes. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.

  7. BLOB − This data type represents a blob column with a maximum length of 65,535 (216 - 1) bytes. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.

  8. MEDIUMBLOB − This data type represents a blob column with a maximum length of 16,777,215 (224 - 1) bytes. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.

  9. LONGBLOB − This data type represents a blob column with a maximum length of 4,294,967,295(232 - 1) bytes. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.

  10. TINYTEXT − This data type represents a text column with a maximum length of 255 (28 - 1) characters. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.

  11. TEXT − This data type represents a text column with a maximum length of 65,535 (216 - 1) characters. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.

  12. MEDIUMTEXT − This data type represents a text column with a maximum length of 16,777,215 (224 - 1) characters. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.

  13. LONGTEXT − This data type represents a text column with a maximum length of 4,294,967,295 or 4GB (232 - 1) characters. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.

  14. ENUM − This data type represents a string object having only a single value from a list.

  15. SET − This data type represents a string object having zero or more values from a list, with a maximum of 64 members. SET values present internally as integer values.

MariaDB - Create Tables

在本教程中,我们将学习如何创建表。在创建表之前,首先要确定其名称、字段名和字段定义。

In this chapter, we will learn how to create tables. Before creating a table, first determine its name, field names, and field definitions.

以下是一般创建表语法:

Following is the general syntax for table creation −

CREATE TABLE table_name (column_name column_type);

查看用于在 PRODUCTS 数据库中创建表的命令:

Review the command applied to creating a table in the PRODUCTS database −

databaseproducts_ tbl(
   product_id INT NOT NULL AUTO_INCREMENT,
   product_name VARCHAR(100) NOT NULL,
   product_manufacturer VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( product_id )
);

上述示例使用“NOT NULL”作为字段属性,以避免因空值引起的错误。属性“AUTO_INCREMENT”指示 MariaDB 将下一个可用值添加到 ID 字段。关键字 primary key 定义列为 primary key 。多个以逗号分隔的列可以定义一个主键。

The above example uses “NOT NULL” as a field attribute to avoid errors caused by a null value. The attribute “AUTO_INCREMENT” instructs MariaDB to add the next available value to the ID field. The keyword primary key defines a column as the primary key. Multiple columns separated by commas can define a primary key.

创建表的两种主要方法是使用命令提示符和 PHP 脚本。

The two main methods for creating tables are using the command prompt and a PHP script.

The Command Prompt

使用 CREATE TABLE 命令执行任务,如下所示:

Utilize the CREATE TABLE command to perform the task as shown below −

root@host# mysql -u root -p
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> CREATE TABLE products_tbl(
   -> product_id INT NOT NULL AUTO_INCREMENT,
   -> product_name VARCHAR(100) NOT NULL,
   -> product_manufacturer VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( product_id )
   -> );
mysql> SHOW TABLES;
+------------------------+
| PRODUCTS               |
+------------------------+
| products_tbl           |
+------------------------+

确保用分号终止所有命令。

Ensure all commands are terminated with a semicolon.

PHP Create Table Script

PHP 提供了 mysql_query() 用于创建表。其第二个参数包含必要的 SQL 命令 −

PHP provides mysql_query() for table creation. Its second argument contains the necessary SQL command −

<html>
   <head>
      <title>Create a MariaDB Table</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 products_tbl( ".
            "product_id INT NOT NULL AUTO_INCREMENT, ".
            "product_name VARCHAR(100) NOT NULL, ".
            "product_manufacturer VARCHAR(40) NOT NULL, ".
            "submission_date DATE, ".
            "PRIMARY KEY ( product_id )); ";

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

         if(! $retval ) {
            die('Could not create table: ' . mysql_error());
         }
         echo "Table created successfully\n";

         mysql_close($conn);
      ?>
   </body>
</html>

成功创建表后,您将看到以下输出 −

On successful table creation, you will see the following output −

mysql> Table created successfully

MariaDB - Drop Tables

在本章中,我们将学习如何删除表。

In this chapter, we will learn to delete tables.

删除表非常容易,但请记住,所有已删除的表是不可恢复的。表删除的一般语法如下 −

Table deletion is very easy, but remember all deleted tables are irrecoverable. The general syntax for table deletion is as follows −

DROP TABLE table_name ;

有两种执行表删除操作的方法:使用命令提示符或 PHP 脚本。

Two options exist for performing a table drop: use the command prompt or a PHP script.

The Command Prompt

在命令提示符下,只需使用 DROP TABLE SQL 命令 −

At the command prompt, simply use the DROP TABLE SQL command −

root@host# mysql -u root -p
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> DROP TABLE products_tbl

mysql> SELECT * from products_tbl
ERROR 1146 (42S02): Table 'products_tbl' doesn't exist

PHP Drop Table Script

PHP 提供了 mysql_query() 用于删除表。仅需将其第二个参数传递给相应的 SQL 命令 −

PHP provides mysql_query() for dropping tables. Simply pass its second argument the appropriate SQL command −

<html>
   <head>
      <title>Create a MariaDB Table</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 products_tbl";
         mysql_select_db( 'PRODUCTS' );
         $retval = mysql_query( $sql, $conn );

         if(! $retval ) {
            die('Could not delete table: ' . mysql_error());
         }
         echo "Table deleted successfully\n";

         mysql_close($conn);
      ?>
   </body>
</html>

成功删除表后,您将看到以下输出 −

On successful table deletion, you will see the following output −

mysql> Table deleted successfully

MariaDB - Insert Query

在本章中,我们将学习如何在表中插入数据。

In this chapter, we will learn how to insert data in a table.

在表中插入数据需要使用 INSERT 命令。该命令的一般语法是 INSERT 后跟表名、字段和值。

Inserting data into a table requires the INSERT command. The general syntax of the command is INSERT followed by the table name, fields, and values.

查看其给出的以下一般语法 −

Review its general syntax given below −

INSERT INTO tablename (field,field2,...) VALUES (value, value2,...);

该语句需要使用单引号或双引号来表示字符串值。该语句的其他选项包括 “INSERT…​SET” 语句、“INSERT…​SELECT” 语句以及其他几个选项。

The statement requires the use of single or double quotes for string values. Other options for the statement include “INSERT…​SET” statements, “INSERT…​SELECT” statements, and several other options.

Note − 仅适用于 INSERT 语句并且在其他地方使用时返回 NULL 的 VALUES() 函数出现在该语句中。

Note − The VALUES() function that appears within the statement, only applies to INSERT statements and returns NULL if used elsewhere.

有两种执行该操作的方法:使用命令行或使用 PHP 脚本。

Two options exist for performing the operation: use the command line or use a PHP script.

The Command Prompt

在提示符下,有许多执行选择操作的方法。下面给出了一个标准语句 −

At the prompt, there are many ways to perform a select operation. A standard statement is given below −

belowmysql>
INSERT INTO products_tbl (ID_number, Nomenclature) VALUES (12345,“Orbitron 4000”);
mysql> SHOW COLUMNS FROM products_tbl;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ID_number   | int(5)      |      |     |         |       |
| Nomenclature| char(13)    |      |     |         |       |
+-------------+-------------+------+-----+---------+-------+

您可以插入多行 −

You can insert multiple rows −

INSERT INTO products VALUES (1, “first row”), (2, “second row”);

您还可以使用 SET 子句 −

You can also employ the SET clause −

INSERT INTO products SELECT * FROM inventory WHERE status = 'available';

PHP Insertion Script

在 PHP 函数中使用相同的 “INSERT INTO…​” 语句来执行该操作。您将再次使用 mysql_query() 函数。

Employ the same “INSERT INTO…​” statement within a PHP function to perform the operation. You will use the mysql_query() function once again.

查看下面给出的示例 −

Review the example given below −

<?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() ) {
         $product_name = addslashes ($_POST['product_name']);
         $product_manufacturer = addslashes ($_POST['product_name']);
      } else {
         $product_name = $_POST['product_name'];
         $product_manufacturer = $_POST['product_manufacturer'];
      }
      $ship_date = $_POST['ship_date'];
      $sql = "INSERT INTO products_tbl ".
         "(product_name,product_manufacturer, ship_date) ".
         "VALUES"."('$product_name','$product_manufacturer','$ship_date')";

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

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

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

当数据插入成功时,您将看到以下输出 −

On successful data insertion, you will see the following output −

mysql> Entered data successfully

您还可以将验证语句与插入语句配合使用,例如检查以确保数据输入正确。MariaDB 为此目的包含许多选项,其中一些是自动的。

You will also collaborate validation statements with insert statements such as checking to ensure correct data entry. MariaDB includes a number of options for this purpose, some of which are automatic.

MariaDB - Select Query

在本章中,我们将学习如何从表中选择数据。

In this chapter, we will learn how to select data from a table.

SELECT 语句检索选定的行。它们可以包括 UNION 语句、排序子句、LIMIT 子句、WHERE 子句、GROUP BY…​HAVING 子句和子查询。

SELECT statements retrieve selected rows. They can include UNION statements, an ordering clause, a LIMIT clause, a WHERE clause, a GROUP BY…​HAVING clause, and subqueries.

复习以下一般语法 −

Review the following general syntax −

SELECT field, field2,... FROM table_name, table_name2,... WHERE...

SELECT 语句提供了多种指定所用表的选择 −

A SELECT statement provides multiple options for specifying the table used −

  1. database_name.table_name

  2. table_name.column_name

  3. database_name.table_name.column_name

所有选择语句都必须包含一个或多个 select expressions 。选择表达式由以下选项之一组成 −

All select statements must contain one or more select expressions. Select expressions consist of one of the following options −

  1. A column name.

  2. An expression employing operators and functions.

  3. The specification “table_name.*” to select all columns within the given table.

  4. The character “*” to select all columns from all tables specified in the FROM clause.

可以在执行选择语句时使用命令提示符或 PHP 脚本。

The command prompt or a PHP script can be employed in executing a select statement.

The Command Prompt

在命令提示符处,如下执行语句 −

At the command prompt, execute statements as follows −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> SELECT * from products_tbl
+-------------+---------------+
| ID_number   | Nomenclature  |
+-------------+---------------+
| 12345       | Orbitron 4000 |
+-------------+---------------+

PHP Select Script

在 PHP 函数中使用相同的 SELECT 语句来执行操作。您将再次使用 mysql_query() 函数。查看以下给出的示例 −

Employ the same SELECT statement(s) within a PHP function to perform the operation. You will use the mysql_query() function once again. Review an example given below −

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

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

   $sql = 'SELECT product_id, product_name,product_manufacturer, ship_date FROM products_tbl';
   mysql_select_db('PRODUCTS');
   $retval = mysql_query( $sql, $conn );

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

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Product ID :{$row['product_id']} <br> ".
         "Name: {$row['product_name']} <br> ".
         "Manufacturer: {$row['product_manufacturer']} <br> ".
         "Ship Date : {$row['ship_date']} <br>".
         "--------------------------------<br>";
   }

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

当数据检索成功时,您将看到以下输出 −

On successful data retrieval, you will see the following output −

Product ID: 12345
Nomenclature: Orbitron 4000
Manufacturer: XYZ Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 12346
Nomenclature: Orbitron 3000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
mysql> Fetched data successfully

最佳实践建议在每次 SELECT 语句之后释放游标内存。PHP 提供了 mysql_free_result() 函数用于此目的。查看其用法,如下所示 −

Best practices suggest releasing cursor memory after every SELECT statement. PHP provides the mysql_free_result() function for this purpose. Review its use as shown below −

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

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

   $sql = 'SELECT product_id, product_name, product_manufacturer, ship_date FROM products_tbl';
   mysql_select_db('PRODUCTS');
   $retval = mysql_query( $sql, $conn );

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

   while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
      echo "Product ID :{$row[0]} <br> ".
         "Name: {$row[1]} <br> ".
         "Manufacturer: {$row[2]} <br> ".
         "Ship Date : {$row[3]} <br> ".
         "--------------------------------<br>";
   }

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

MariaDB - Where Clause

WHERE 子句过滤各种语句,例如 SELECT、UPDATE、DELETE 和 INSERT。它们提供了用于指定动作的条件。它们通常出现在语句中的表名之后,其条件随之而来。WHERE 子句本质上就像一个 if 语句。

WHERE clauses filter various statements such as SELECT, UPDATE, DELETE, and INSERT. They present criteria used to specify action. They typically appear after a table name in a statement, and their condition follows. The WHERE clause essentially functions like an if statement.

查看下面给出的 WHERE 子句的一般语法 −

Review the general syntax of WHERE clause given below −

[COMMAND] field,field2,... FROM table_name,table_name2,... WHERE [CONDITION]

注意 WHERE 子句的以下特性 −

Note the following qualities of the WHERE clause −

  1. It is optional.

  2. It allows any condition to be specified.

  3. It allows for the specification of multiple conditions through using an AND or OR operator.

  4. Case sensitivity only applies to statements using LIKE comparisons.

WHERE 子句允许使用以下运算符 −

The WHERE clause permits the use of the following operators −

Operator

= !=

> <

>= ⇐

WHERE 子句可以在命令提示符或 PHP 脚本中使用。

WHERE clauses can be utilized at the command prompt or within a PHP script.

The Command Prompt

在命令提示符下,只需使用标准命令 −

At the command prompt, simply use a standard command −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> SELECT * from products_tbl WHERE product_manufacturer = 'XYZ Corp';
+-------------+----------------+----------------------+
| ID_number   | Nomenclature   | product_manufacturer |
+-------------+----------------+----------------------+
| 12345       | Orbitron 4000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12346       | Orbitron 3000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12347       | Orbitron 1000  | XYZ Corp             |
+-------------+----------------+----------------------+

使用 AND 条件查看示例 −

Review an example using the AND condition −

SELECT *
FROM products_tbl
WHERE product_name = 'Bun Janshu 3000';
AND product_id <= 344;

此示例结合 AND 和 OR 条件

This example combines both AND and OR conditions

SELECT *
FROM products_tbl
WHERE (product_name = 'Bun Janshu 3000' AND product_id < 344)
OR (product_name = 'Bun Janshu 3000');

PHP Scripts Using Where Clause

在使用 WHERE 子句的操作中使用 mysql_query() 函数 −

Employ the mysql_query() function in operations using a WHERE clause −

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

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

   $sql = 'SELECT product_id, product_name, product_manufacturer, ship_date
      FROM products_tbl
      WHERE product_manufacturer = "XYZ Corp"';

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

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

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Product ID :{$row['product_id']} <br> ".
         "Name: {$row['product_name']} <br> ".
         "Manufacturer: {$row['product_manufacturer']} <br> ".
         "Ship Date: {$row['ship_date']} <br> ".
         "--------------------------------<br>";
   }

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

当数据检索成功时,您将看到以下输出 −

On successful data retrieval, you will see the following output −

Product ID: 12345
Nomenclature: Orbitron 4000
Manufacturer: XYZ Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 12346
Nomenclature: Orbitron 3000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
Product ID: 12347
Nomenclature: Orbitron 1000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
mysql> Fetched data successfully

MariaDB - Update Query

UPDATE 命令通过更改值来修改现有字段。它使用 SET 子句来指定用于修改的列,并指定分配的新值。这些值可以是表达式或字段的默认值。设置默认值需要使用 DEFAULT 关键字。该命令还可以使用 WHERE 子句为更新指定条件和/或 ORDER BY 子句以按特定顺序更新。

The UPDATE command modifies existing fields by changing values. It uses the SET clause to specify columns for modification, and to specify the new values assigned. These values can be either an expression or the default value of the field. Setting a default value requires using the DEFAULT keyword. The command can also employ a WHERE clause to specify conditions for an update and/or an ORDER BY clause to update in a certain order.

复习以下一般语法 −

Review the following general syntax −

UPDATE table_name SET field=new_value, field2=new_value2,...
[WHERE ...]

从命令提示符或使用 PHP 脚本执行 UPDATE 命令。

Execute an UPDATE command from either the command prompt or using a PHP script.

The Command Prompt

在命令提示符下,只需使用标准命令根 −

At the command prompt, simply use a standard commandroot −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> UPDATE products_tbl
   SET nomenclature = 'Fiber Blaster 300Z' WHERE ID_number = 112;
mysql> SELECT * from products_tbl WHERE ID_number='112';
+-------------+---------------------+----------------------+
| ID_number   | Nomenclature        | product_manufacturer |
+-------------+---------------------+----------------------+
| 112         | Fiber Blaster 300Z  | XYZ Corp             |
+-------------+---------------------+----------------------+

PHP Update Query Script

在 UPDATE 命令语句中使用 mysql_query() 函数 −

Employ the mysql_query() function in UPDATE command statements −

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

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

   $sql = ‘UPDATE products_tbl
      SET product_name = ”Fiber Blaster 300z”
      WHERE product_id = 112’;

   mysql_select_db(‘PRODUCTS’);
   $retval = mysql_query( $sql, $conn );

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

   echo “Updated data successfully\n”;
   mysql_close($conn);
?>

在成功更新数据后,您将看到以下输出 −

On successful data update, you will see the following output −

mysql> Updated data successfully

MariaDB - Delete Query

DELETE 命令从指定表中删除表行,并返回已删除的数量。使用 ROW_COUNT() 函数访问已删除的数量。WHERE 子句指定行,并且在没有它的时候,所有行都将被删除。LIMIT 子句控制已删除的行数。

The DELETE command deletes table rows from the specified table, and returns the quantity deleted. Access the quantity deleted with the ROW_COUNT() function. A WHERE clause specifies rows, and in its absence, all rows are deleted. A LIMIT clause controls the number of rows deleted.

在针对多行的 DELETE 语句中,它只删除满足条件的行;并且不允许 LIMIT 和 WHERE 子句。DELETE 语句允许从不同数据库中的表中删除行,但不允许从表中删除,然后在子查询中从同一表中选择。

In a DELETE statement for multiple rows, it deletes only those rows satisfying a condition; and LIMIT and WHERE clauses are not permitted. DELETE statements allow deleting rows from tables in different databases, but do not allow deleting from a table and then selecting from the same table within a subquery.

查看以下 DELETE 语法 −

Review the following DELETE syntax −

DELETE FROM table_name [WHERE …]

从命令提示符或使用 PHP 脚本执行 DELETE 命令。

Execute a DELETE command from either the command prompt or using a PHP script.

The Command Prompt

在命令提示符下,只需使用标准命令 −

At the command prompt, simply use a standard command −

root@host# mysql –u root –p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed
mysql> DELETE FROM products_tbl WHERE product_id=133;
mysql> SELECT * from products_tbl WHERE ID_number='133';
ERROR 1032 (HY000): Can't find record in 'products_tbl'

PHP Delete Query Script

在 DELETE 命令语句中使用 mysql_query() 函数 −

Use the mysql_query() function in DELETE command statements −

<?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 products_tbl WHERE product_id = 261';
   mysql_select_db('PRODUCTS');
   $retval = mysql_query( $sql, $conn );

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

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

在成功删除数据后,您将看到以下输出 −

On successful data deletion, you will see the following output −

mysql> Deleted data successfully
mysql> SELECT * from products_tbl WHERE ID_number='261';
ERROR 1032 (HY000): Can't find record in 'products_tbl'

MariaDB - Like Clause

WHERE 子句提供了一种在操作使用完全匹配时检索数据的方式。在需要具有共享特征的多条结果的情况下, LIKE 子句可容纳广义模式匹配。

The WHERE clause provides a way to retrieve data when an operation uses an exact match. In situations requiring multiple results with shared characteristics, the LIKE clause accommodates broad pattern matching.

LIKE 子句测试模式匹配,返回真或假。用于比较的模式接受以下通配符:“%”,它与数字字符(0 或更多)匹配;而 “ ”, which matches a single character. The “ ” 通配符仅与它自己集合内的字符匹配,这意味着在使用另一个集合时它将忽略拉丁字符。默认情况不区分大小写,需要附加设置来区分大小写。

A LIKE clause tests for a pattern match, returning a true or false. The patterns used for comparison accept the following wildcard characters: “%”, which matches numbers of characters (0 or more); and “”, which matches a single character. The “” wildcard character only matches characters within its set, meaning it will ignore latin characters when using another set. The matches are case-insensitive by default requiring additional settings for case sensitivity.

NOT LIKE 子句允许测试相反条件,很像 not 运算符。

A NOT LIKE clause allows for testing the opposite condition, much like the not operator.

如果语句表达式或模式评估为 NULL,则结果为 NULL。

If the statement expression or pattern evaluate to NULL, the result is NULL.

复习以下给出的 LIKE 子句一般语法 −

Review the general LIKE clause syntax given below −

SELECT field, field2,... FROM table_name, table_name2,...
WHERE field LIKE condition

在命令提示符处或 PHP 脚本中使用 LIKE 子句。

Employ a LIKE clause either at the command prompt or within a PHP script.

The Command Prompt

在命令提示符下,只需使用标准命令 −

At the command prompt, simply use a standard command −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from products_tbl
   WHERE product_manufacturer LIKE 'XYZ%';
+-------------+----------------+----------------------+
| ID_number   | Nomenclature   | product_manufacturer |
+-------------+----------------+----------------------+
| 12345       | Orbitron 4000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12346       | Orbitron 3000  | XYZ Corp             |
+-------------+----------------+----------------------+
| 12347       | Orbitron 1000  | XYZ Corp             |
+-------------+----------------+----------------------+

PHP Script Using Like Clause

在使用 LIKE 子句的语句中使用 mysql_query() 函数

Use the mysql_query() function in statements employing the LIKE clause

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

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

   $sql = 'SELECT product_id, product_name, product_manufacturer, ship_date
      FROM products_tbl WHERE product_manufacturer LIKE "xyz%"';

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

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

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Product ID:{$row['product_id']} <br> ".
         "Name: {$row['product_name']} <br> ".
         "Manufacturer: {$row['product_manufacturer']} <br> ".
         "Ship Date: {$row['ship_date']} <br> ".
         "--------------------------------<br>";
   }

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

当数据检索成功时,您将看到以下输出 −

On successful data retrieval, you will see the following output −

Product ID: 12345
Nomenclature: Orbitron 4000
Manufacturer: XYZ Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 12346
Nomenclature: Orbitron 3000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
Product ID: 12347
Nomenclature: Orbitron 1000
Manufacturer: XYZ Corp
Ship Date: 01/02/17
----------------------------------------------
mysql> Fetched data successfully

MariaDB - Order By Clause

如前文讨论所述, ORDER BY 子句对语句的结果进行排序。它指定所操作数据的顺序,并包括按升序 (ASC) 或降序 (DESC) 排序的选项。在忽略订单规范的情况下,默认订单为升序。

The ORDER BY clause, as mentioned in previous discussions, sorts the results of a statement. It specifies the order of the data operated on, and includes the option to sort in ascending (ASC) or descending (DESC) order. On omission of order specification, the default order is ascending.

ORDER BY 子句出现在各种语句中,例如 DELETE 和 UPDATE。它们始终出现在语句的末尾,而不在子查询中或在集合函数之前,因为它们对最终结果表进行操作。您也不能使用整数来标识列。

ORDER BY clauses appear in a wide variety of statements such as DELETE and UPDATE. They always appear at the end of a statement, not in a subquery or before a set function, because they operate on the final resulting table. You also cannot use an integer to identify a column.

复习以下给出的 ORDER BY 子句的一般语法 −

Review the general syntax of the ORDER BY clause given below −

SELECT field, field2,... [or column] FROM table_name, table_name2,...
ORDER BY field, field2,... ASC[or DESC]

在命令提示符处或 PHP 脚本中使用 ORDER BY 子句。

Use an ORDER BY clause either at the command prompt or within a PHP script.

The Command Prompt

在命令提示符下,只需使用标准命令 −

At the command prompt, simply use a standard command −

root@ host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed

mysql> SELECT * from products_tbl ORDER BY product_manufacturer ASC
+-------------+----------------+----------------------+
| ID_number   | Nomenclature   | product_manufacturer |
+-------------+----------------+----------------------+
| 56789       | SuperBlast 400 | LMN Corp             |
+-------------+----------------+----------------------+
| 67891       | Zoomzoom 5000  | QFT Corp             |
+-------------+----------------+----------------------+
| 12347       | Orbitron 1000  | XYZ Corp             |
+-------------+----------------+----------------------+

PHP Script Using Order By Clause

在使用 ORDER BY 子句的语句中再次使用 mysql_query() 函数 −

Utilize the mysql_query() function, once again, in statements employing the ORDER BY clause −

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

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

   $sql = 'SELECT product_id, product_name, product_manufacturer, ship_date
      FROM products_tbl ORDER BY product_manufacturer DESC';

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

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

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Product ID :{$row['product_id']} <br> ".
         "Name: {$row['product_name']} <br> ".
         "Manufacturer: {$row['product_manufacturer']} <br> ".
         "Ship Date : {$row['ship_date']} <br> ".
         "--------------------------------<br>";
   }

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

当数据检索成功时,您将看到以下输出 −

On successful data retrieval, you will see the following output −

Product ID: 12347
Nomenclature: Orbitron 1000
Manufacturer: XYZ Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 67891
Nomenclature: Zoomzoom 5000
Manufacturer: QFT Corp
Ship Date: 01/01/17
----------------------------------------------
Product ID: 56789
Nomenclature: SuperBlast 400
Manufacturer: LMN Corp
Ship Date: 01/04/17
----------------------------------------------
mysql> Fetched data successfully

MariaDB - Join

在之前的讨论和示例中,我们从单表中进行检索,或从多源中检索多个值。大多数实际数据操作要复杂得多,需要从多张表中进行聚合、比较和检索。

In previous discussions and examples, we examined retrieving from a single table, or retrieving multiple values from multiple sources. Most real-world data operations are much more complex, requiring aggregation, comparison, and retrieval from multiple tables.

JOINs 允许将两个或多个表合并为单个对象。它们通过 SELECT、UPDATE 和 DELETE 语句使用。

JOINs allow merging of two or more tables into a single object. They are employed through SELECT, UPDATE, and DELETE statements.

复习如下所示使用 JOIN 的语句的一般语法 −

Review the general syntax of a statement employing a JOIN as shown below −

SELECT column
FROM table_name1
INNER JOIN table_name2
ON table_name1.column = table_name2.column;

请注意,用于 JOIN 的旧语法使用隐式连接且没有关键字。可以使用 WHERE 子句实现连接,但关键字最适合可读性、可维护性和最佳实践。

Note the old syntax for JOINS used implicit joins and no keywords. It is possible to use a WHERE clause to achieve a join, but keywords work best for readability, maintenance, and best practices.

JOIN 有多种形式,例如左连接、右连接或内连接。各种联接类型基于共享值或特征提供不同类型的聚合。

JOINs come in many forms such as a left join, right join, or inner join. Various join types offer different types of aggregation based on shared values or characteristics.

在命令提示符处或使用 PHP 脚本使用 JOIN。

Employ a JOIN either at the command prompt or with a PHP script.

The Command Prompt

在命令提示符处,只需使用标准语句 −

At the command prompt, simply use a standard statement −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use PRODUCTS;
Database changed

mysql> SELECT products.ID_number, products.Nomenclature, inventory.inventory_ct
   FROM products
   INNER JOIN inventory
   ON products.ID_numbeer = inventory.ID_number;
+-------------+----------------+-----------------+
| ID_number   | Nomenclature   | Inventory Count |
+-------------+----------------+-----------------+
| 12345       | Orbitron 4000  | 150             |
+-------------+----------------+-----------------+
| 12346       | Orbitron 3000  | 200             |
+-------------+----------------+-----------------+
| 12347       | Orbitron 1000  | 0               |
+-------------+----------------+-----------------+

PHP Script Using JOIN

使用 mysql_query() 函数执行联接操作-

Use the mysql_query() function to perform a join operation −

<?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.product_id, a.product_manufacturer, b.product_count
      FROM products_tbl a, pcount_tbl b
      WHERE a.product_manufacturer = b.product_manufacturer';

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

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

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Manufacturer:{$row['product_manufacturer']} <br> ".
         "Count: {$row['product_count']} <br> ".
         "Product ID: {$row['product_id']} <br> ".
         "--------------------------------<br>";
   }

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

当数据检索成功时,您将看到以下输出 −

On successful data retrieval, you will see the following output −

ID Number: 12345
Nomenclature: Orbitron 4000
Inventory Count: 150
--------------------------------------
ID Number: 12346
Nomenclature: Orbitron 3000
Inventory Count: 200
--------------------------------------
ID Number: 12347
Nomenclature: Orbitron 1000
Inventory Count: 0
--------------------------------------
mysql> Fetched data successfully

MariaDB - Null Values

在处理 NULL 值时,记住它们是未知值。它们不是有效值,即空字符串或零。在表创建中,列规范允许设置它们以接受空值或拒绝空值。只需使用 NULL 或 NOT NULL 子句。这在缺少记录信息(如 ID 编号)的情况下有应用。

When working with NULL values, remember they are unknown values. They are not empty strings or zero, which are valid values. In table creation, column specifications allow for setting them to accept null values, or reject them. Simply utilize a NULL or NOT NULL clause. This has applications in cases of missing record information like an ID number.

用户定义变量在明确赋值之前具有 NULL 值。存储例程参数和局部变量允许设置 NULL 值。当局部变量没有默认值时,它具有 NULL 值。

User-defined variables have a value of NULL until explicit assignment. Stored routine parameters and local variables allow setting a value of NULL. When a local variable has no default value, it has a value of NULL.

NULL 不区分大小写,并且具有以下别名 -

NULL is case-insensitive, and has the following aliases −

  1. UNKNOWN (a boolean value)

  2. \N

NULL Operators

标准比较运算符不能与 NULL 一起使用(例如 =、>、>=、⇐、< 或 !=),因为对 NULL 值的所有比较都返回 NULL,而不是 true 或 false。与 NULL 的比较或可能包含 NULL 的比较必须使用“<⇒”(NULL 安全)运算符。

Standard comparison operators cannot be used with NULL (e.g., =, >, >=, ⇐, <, or !=) because all comparisons with a NULL value return NULL, not true or false. Comparisons with NULL or possibly containing it must use the “<⇒” (NULL-SAFE) operator.

其他可用的运算符有 -

Other available operators are −

  1. IS NULL − It tests for a NULL value.

  2. IS NOT NULL − It confirms the absence of a NULL value.

  3. ISNULL − It returns a value of 1 on discovery of a NULL value, and 0 in its absence.

  4. COALESCE − It returns the first non-NULL value of a list, or it returns a NULL value in the absence of one.

Sorting NULL Values

在排序操作中,NULL 值具有最低的值,因此 DESC 顺序将 NULL 值放在底部。MariaDB 允许为 NULL 值设置更高的值。

In sorting operations, NULL values have the lowest value, so DESC order results in NULL values at the bottom. MariaDB allows for setting a higher value for NULL values.

有两种方法可以做到这一点,如下所示 -

There are two ways to do this as shown below −

SELECT column1 FROM product_tbl ORDER BY ISNULL(column1), column1;

另一种方法 -

The other way −

SELECT column1 FROM product_tbl ORDER BY IF(column1 IS NULL, 0, 1), column1 DESC;

NULL Functions

当任何参数为 NULL 时,函数通常会输出 NULL。但是,有一些函数专门设计用于管理 NULL 值。它们是 -

Functions generally output NULL when any parameters are NULL. However, there are functions specifically designed for managing NULL values. They are −

  1. IFNULL() − If the first expression is not NULL it returns it. When it evaluates to NULL, it returns the second expression.

  2. NULLIF() − It returns NULL when the compared expressions are equal, if not, it returns the first expression.

SUM 和 AVG 等函数会忽略 NULL 值。

Functions like SUM and AVG ignore NULL values.

Inserting NULL Values

在声明为 NOT NULL 的列中插入 NULL 值时,会发生错误。在默认 SQL 模式下,NOT NULL 列将改为基于数据类型插入默认值。

On insertion of a NULL value in a column declared NOT NULL, an error occurs. In default SQL mode, a NOT NULL column will instead insert a default value based on data type.

当字段为 TIMESTAMP、AUTO_INCREMENT 或虚拟列时,MariaDB 会以不同的方式管理 NULL 值。在 AUTO_INCREMENT 列中插入会导致序列中的下一个数字插入到它所在的位置。在 TIMESTAMP 字段中,MariaDB 会为此分配当前时间戳。在本教程的后面部分讨论的虚拟列中,会分配默认值。

When a field is a TIMESTAMP, AUTO_INCREMENT, or virtual column, MariaDB manages NULL values differently. Insertion in an AUTO_INCREMENT column causes the next number in the sequence to insert in its place. In a TIMESTAMP field, MariaDB assigns the current timestamp instead. In virtual columns, a topic discussed later in this tutorial, the default value is assigned.

唯一索引可以保存许多 NULL 值,但主键不能为 NULL。

UNIQUE indices can hold many NULL values, however, primary keys cannot be NULL.

NULL Values and the Alter Command

当您使用 ALTER 命令修改一列时,在没有 NULL 规范的情况下,MariaDB 会自动分配值。

When you use the ALTER command to modify a column, in the absence of NULL specifications, MariaDB automatically assigns values.

MariaDB - Regular Expression

除了 LIKE 子句中可用的模式匹配外,MariaDB 通过 REGEXP 运算符提供基于正则表达式的匹配。该运算符根据给定的模式对字符串表达式执行模式匹配。

Beyond the pattern matching available from LIKE clauses, MariaDB offers regular expression-based matching through the REGEXP operator. The operator performs pattern matching for a string expression based on a given pattern.

MariaDB 10.0.5 引入了 PCRE 正则表达式,这极大地增加了匹配范围,涉及递归模式、前瞻断言等领域。

MariaDB 10.0.5 introduced PCRE Regular Expressions, which dramatically increases the scope of matching into areas like recursive patterns, look-ahead assertions, and more.

查看下面给出的标准 REGEXP 运算符语法的用法 −

Review the use of standard REGEXP operator syntax given below −

SELECT column FROM table_name WHERE column REGEXP '[PATTERN]';

如果模式匹配,REGEXP 返回 1;否则返回 0。

REGEXP returns 1 for a pattern match or 0 in the absence of one.

NOT REGEXP 形式的选项与之相反。MariaDB 还为 REGEXP 和 NOT REGEXP 提供了同义词 RLIKE 和 NOT RLIKE,这些同义词出于兼容性原因创建。

An option for the opposite exists in the form of NOT REGEXP. MariaDB also offers synonyms for REGEXP and NOT REGEXP, RLIKE and NOT RLIKE, which were created for compatibility reasons.

要比较的模式可以是文本字符串或诸如表列的其它内容。在字符串中,它使用 C 转义语法,因此对所有“\”字符进行加倍。REGEXP 也区分大小写,二进制字符串除外。

The pattern compared can be a literal string or something else such as a table column. In strings, it uses C escape syntax, so double any “\” characters. REGEXP is also case-insensitive, with the exception of binary strings.

下面给出了可以使用的一个模式表 −

A table of possible patterns, which can be used are given below −

Sr.No

Pattern & Description

1

^ It matches the start of the string.

2

$ It matches the string’s end.

3

. It matches a single character.

4

[…​] It matches any character in the brackets.

5

[^…​] It matches any character not listed in the brackets.

6

*p1

p2

p3* It matches any of the patterns.

7

* It matches 0 or more instances of the preceding element.

8

+ It matches 1 or more instances of the preceding element.

9

{n} It matches n instances of the preceding element.

10

{m,n} It matches m to n instances of the preceding element.

查看下面提供的模式匹配示例 −

Review the pattern matching examples given below −

以“pr”开头的产品 −

Products starting with “pr” −

SELECT name FROM product_tbl WHERE name REGEXP '^pr';

以“na”结尾的产品 −

Products ending with “na” −

SELECT name FROM product_tbl WHERE name REGEXP 'na$';

以元音开头的产品 −

Products starting with a vowel −

SELECT name FROM product_tbl WHERE name REGEXP '^[aeiou]';

MariaDB - Transactions

事务是连续的组操作。它们作为一个单一单元运行,并且在组内所有操作全部成功执行之前不会终止。组内只要有一个操作失败,整个事务就会失败,并且这不会对数据库产生任何影响。

Transactions are sequential group operations. They function as a single unit, and do not terminate until all operations within the group execute successfully. A single failure in the group causes the entire transaction to fail, and causes it to have no impact on the database.

事务符合 ACID(原子性、一致性、隔离性和耐久性)−

Transactions conform to ACID (Atomicity, Consistency, Isolation, and Durability) −

  1. Atomicity − It ensures the success of all operations by aborting on failures and rolling back changes.

  2. Consistency − It ensures the database applies changes on a successful transaction.

  3. Isolation − It enables independent transactions operation of transactions.

  4. Durability − It ensures the persistence of a successful transaction in the event of system failure.

在事务语句的开头是 START TRANSACTION 语句,后面跟着 COMMIT 和 ROLLBACK 语句 −

At the head of a transaction statement is the START TRANSACTION statement followed by COMMIT and ROLLBACK statements −

  1. START TRANSACTION begins the transaction.

  2. COMMIT saves changes to data.

  3. ROLLBACK ends the transaction, destroying any changes.

在事务成功时,COMMIT 起作用。在失败时,ROLLBACK 起作用。

On a successful transaction, COMMIT acts. On a failure, ROLLBACK acts.

Note − 某些语句会导致隐式提交,当在事务内使用时,它们也会导致错误。此类语句的示例包括但不限于 CREATE、ALTER 和 DROP。

Note − Some statements cause an implicit commit, and they also cause an error when used within transactions. Examples of such statements include, but are not limited to CREATE, ALTER, and DROP.

MariaDB 事务还包括 SAVEPOINT 和 LOCK TABLES 等选项。SAVEPOINT 设置一个还原点以用于 ROLLBACK。LOCK TABLES 可以在会话期间控制对表的访问,以防止在某些时间段内进行修改。

MariaDB transactions also include options like SAVEPOINT and LOCK TABLES. SAVEPOINT sets a restore point to utilize with ROLLBACK. LOCK TABLES allows controlling access to tables during sessions to prevent modifications during certain time periods.

AUTOCOMMIT 变量提供对事务的控制。将其设置为 1 会强制所有操作都被视为成功事务,而将其设置为 0 则会导致只有在显式 COMMIT 语句中才会持久保留更改。

The AUTOCOMMIT variable provides control over transactions. A setting of 1 forces all operations to be considered successful transactions, and a setting of 0 causes persistence of changes to only occur on an explicit COMMIT statement.

Structure of a Transaction

事务语句的一般结构包含从 START TRANSACTION 开始。下一步是插入一个或多个命令/操作,插入检查错误的语句,插入 ROLLBACK 语句以管理发现的任何错误,最后插入 COMMIT 语句以对成功操作应用更改。

The general structure of a transaction statement consists of beginning with START TRANSACTION. The next step is inserting one or more commands/operations, inserting statements that check for errors, inserting ROLLBACK statements to manage any errors discovered and finally inserting a COMMIT statement to apply changes on successful operations.

查看下面给出的示例 −

Review the example given below −

START TRANSACTION;
SELECT name FROM products WHERE manufacturer = 'XYZ Corp';
UPDATE spring_products SET item = name;
COMMIT;

MariaDB - Alter Command

ALTER 命令提供了一种修改现有表结构的方法,它表示移除或添加列、修改索引、更改数据类型或更改名称等修改。当元数据锁处于活动状态时,ALTER 也会等待应用更改。

The ALTER command provides a way to change an existing table’s structure, meaning modifications like removing or adding columns, modifying indices, changing data types, or changing names. ALTER also waits to apply changes when a metadata lock is active.

Using ALTER to Modify Columns

ALTER 与 DROP 结合使用,可以移除现有列。但是,如果该列是唯一剩余列,它将失败。

ALTER paired with DROP removes an existing column. However, it fails if the column is the only remaining column.

查看下面给出的示例 −

Review the example given below −

mysql> ALTER TABLE products_tbl DROP version_num;

使用 ALTER…​ADD 语句添加列 −

Use an ALTER…​ADD statement to add columns −

mysql> ALTER TABLE products_tbl ADD discontinued CHAR(1);

使用关键字 FIRST 和 AFTER 来指定列放置位置 −

Use the keywords FIRST and AFTER to specify placement of the column −

ALTER TABLE products_tbl ADD discontinued CHAR(1) FIRST;
ALTER TABLE products_tbl ADD discontinued CHAR(1) AFTER quantity;

请注意,关键字 FIRST 和 AFTER 仅适用于 ALTER…​ADD 语句。此外,必须删除表,然后再添加表才能重新定位。

Note the FIRST and AFTER keywords only apply to ALTER…​ADD statements. Furthermore, you must drop a table and then add it in order to reposition it.

使用 ALTER 语句中的 MODIFY 或 CHANGE 子句来更改列定义或名称。这些子句具有类似效果,但使用完全不同的语法。

Change a column definition or name by using the MODIFY or CHANGE clause in an ALTER statement. The clauses have similar effects, but utilize substantially different syntax.

请查看以下给出的 CHANGE 示例 −

Review a CHANGE example given below −

mysql> ALTER TABLE products_tbl CHANGE discontinued status CHAR(4);

在使用 CHANGE 的语句中,指定原始列,然后指定将替换它的新列。请查看以下 MODIFY 示例 −

In a statement using CHANGE, specify the original column and then the new column that will replace it. Review a MODIFY example below −

mysql> ALTER TABLE products_tbl MODIFY discontinued CHAR(4);

ALTER 命令还允许更改默认值。请查看示例 −

The ALTER command also allows for changing default values. Review an example −

mysql> ALTER TABLE products_tbl ALTER discontinued SET DEFAULT N;

还可以使用与 DROP 子句配对的方式删除默认约束 −

You can also use it to remove default constraints by pairing it with a DROP clause −

mysql> ALTER TABLE products_tbl ALTER discontinued DROP DEFAULT;

Using ALTER to Modify Tables

使用 TYPE 子句更改表类型 −

Change table type with the TYPE clause −

mysql> ALTER TABLE products_tbl TYPE = INNODB;

使用关键字 RENAME 重命名表 −

Rename a table with the RENAME keyword −

mysql> ALTER TABLE products_tbl RENAME TO products2016_tbl;

MariaDB - Indexes & Statistics Tables

索引是加速记录检索的工具。索引为索引列中的每个值生成一个条目。

Indexes are tools for accelerating record retrieval. An index spawns an entry for each value within an indexed column.

有四种类型的索引 −

There are four types of indexes −

  1. Primary (one record represents all records)

  2. Unique (one record represents multiple records)

  3. Plain

  4. Full-Text (permits many options in text searches).

在这种用法中,术语 “键”和 “索引”是相同的。

The terms “key” and “index” are identical in this usage.

索引与一列或多列关联,并支持快速搜索和高效的记录组织。在创建索引时,考虑在查询中经常使用的列。然后在这些列上创建一个或多个索引。此外,将索引视为主键表。

Indexes associate with one or more columns, and support rapid searches and efficient record organization. When creating an index, consider which columns are frequently used in your queries. Then create one or multiple indexes on them. In addition, view indexes as essentially tables of primary keys.

尽管索引加快搜索或 SELECT 语句,但它们会因为对表和索引执行操作而拖慢插入和更新速度。

Though indexes accelerate searches or SELECT statements, they make insertions and updates drag due to performing the operations on both the tables and the indexes.

Create an Index

可以通过 CREATE TABLE…​INDEX 语句或 CREATE INDEX 语句创建索引。支持可读性、维护性和最佳实践的最佳选项是 CREATE INDEX。

You can create an index through a CREATE TABLE…​INDEX statement or a CREATE INDEX statement. The best option supporting readability, maintenance, and best practices is CREATE INDEX.

查看索引的一般语法,如下所示:

Review the general syntax of Index given below −

CREATE [UNIQUE or FULLTEXT or...] INDEX index_name ON table_name column;

查看其使用示例:

Review an example of its use −

CREATE UNIQUE INDEX top_sellers ON products_tbl product;

Drop an Index

可以使用 DROP INDEX 或 ALTER TABLE…​DROP 删除索引。支持可读性、维护性和最佳实践的最佳选项是 DROP INDEX。

You can drop an index with DROP INDEX or ALTER TABLE…​DROP. The best option supporting readability, maintenance, and best practices is DROP INDEX.

查看删除索引的一般语法,如下所示:

Review the general syntax of Drop Index given below −

DROP INDEX index_name ON table_name;

查看其使用示例:

Review an example of its use −

DROP INDEX top_sellers ON product_tbl;

Rename an Index

使用 ALTER TABLE 语句重命名索引。查看其给出的常用语法:

Rename an index with the ALTER TABLE statement. Review its general syntax given below −

ALTER TABLE table_name DROP INDEX index_name, ADD INDEX new_index_name;

查看其使用示例:

Review an example of its use −

ALTER TABLE products_tbl DROP INDEX top_sellers, ADD INDEX top_2016sellers;

Managing Indexes

您需要检查并跟踪所有索引。使用 SHOW INDEX 列出与给定表关联的所有现有索引。您可以通过使用诸如“\G”之类的选项来设置显示内容的格式,它指定垂直格式。

You will need to examine and track all indexes. Use SHOW INDEX to list all existing indexes associated with a given table. You can set the format of the displayed content by using an option such as “\G”, which specifies a vertical format.

查看以下示例:

Review the following example −

mysql > SHOW INDEX FROM products_tbl\G

Table Statistics

由于提供更快地访问记录和统计信息,索引被大量用于优化查询。然而,许多用户发现索引维护很繁琐。MariaDB 10.0 提供了与存储引擎无关的统计表,该表会计算每个存储引擎中每张表的统计信息,甚至会计算未被索引的列的统计信息。

Indexes are used heavily to optimize queries given the faster access to records, and the statistics provided. However, many users find index maintenance cumbersome. MariaDB 10.0 made storage engine independent statistics tables available, which calculate data statistics for every table in every storage engine, and even statistics for columns that are not indexed.

MariaDB - Temporary Tables

一些操作可能会从临时表中受益,因为速度或可处置数据。无论您是否通过命令提示符、PHP 脚本或客户端程序使用临时表,临时表的生命都将终止于会话终止。它也不会以典型方式显示在系统中。SHOW TABLES 命令不会显示包含临时表的列表。

Some operations can benefit from temporary tables due to speed or disposable data. The life of a temporary table ends at the termination of a session whether you employ them from the command prompt, with a PHP script, or through a client program. It also does not appear in the system in a typical fashion. The SHOW TABLES command will not reveal a list containing temporary tables.

Create a Temporary Table

CREATE TABLE 语句中的 TEMPORARY 关键字生成一个临时表。查看下面给出的示例:

The TEMPORARY keyword within a CREATE TABLE statement spawns a temporary table. Review an example given below −

mysql>CREATE TEMPORARY TABLE order (
   item_name VARCHAR(50) NOT NULL
   , price DECIMAL(7,2) NOT NULL DEFAULT 0.00
   , quantity INT UNSIGNED NOT NULL DEFAULT 0
);

在创建临时表时,您可以克隆现有表,这意味着使用类似 LIKE 子句的所有常规特征。用于生成临时表的 CREATE TABLE 语句不会因 TEMPORARY 关键字而提交事务。

In creating a temporary table, you can clone existing tables, meaning all their general characteristics, with the LIKE clause. The CREATE TABLE statement used to spawn the temporary table will not commit transactions as a result of the TEMPORARY keyword.

尽管临时表独立于非临时表并在会话结束时删除,但它们可能存在某些冲突:

Though temporary tables stand apart from non-temporary and drop at the end of a session, they may have certain conflicts −

  1. They sometimes conflict with ghost temporary tables from expired sessions.

  2. They sometimes conflict with shadow names of non-temporary tables.

Note - 允许临时表与现有的非临时表具有相同的名称,因为 MariaDB 将其视为不同的引用。

Note − Temporary tables are permitted to have the same name as an existing non-temporary table because MariaDB views it as a difference reference.

Administration

MariaDB 要求向用户授予创建临时表的权限。利用 GRANT 语句向非管理员用户授予此权限。

MariaDB requires granting privileges to users for creating temporary tables. Utilize a GRANT statement to give this privilege to non-admin users.

GRANT CREATE TEMPORARY TABLES ON orders TO 'machine122'@'localhost';

Drop a Temporary Table

尽管临时表在会话结束时基本上会被删除,但您可以选择删除它们。删除临时表需要使用 TEMPORARY 关键字,并且最佳实践建议在任何非临时表之前删除临时表。

Though temporary tables are essentially removed at the end of sessions, you have the option to delete them. Dropping a temporary table requires the use of the TEMPORARY keyword, and best practices suggest dropping temporary tables before any non-temporary.

mysql> DROP TABLE order;

MariaDB - Table Cloning

有些情况下需要生成现有表的精确副本。CREATE…​SELECT 语句无法生成此输出,因为它忽略了诸如索引和默认值之类的内容。

Some situations require producing an exact copy of an existing table. The CREATE…​SELECT statement cannot produce this output because it neglects things like indexes and default values.

复制表的步骤如下 −

The procedure for a duplicating a table is as follows −

  1. Utilize SHOW CREATE TABLE to produce a CREATE TABLE statement that details the entire structure of the source table.

  2. Edit the statement to give the table a new name, and execute it.

  3. Use an INSERT INTO…​SELECT statement if you also need the table data copied.

mysql> INSERT INTO inventory_copy_tbl (
   product_id,product_name,product_manufacturer,ship_date)

   SELECT product_id,product_name,product_manufacturer,ship_date,
   FROM inventory_tbl;

创建副本的另一种方法是使用 CREATE TABLE AS 语句。该语句复制所有列、列定义,并利用源表的数据填充副本。

Another method for creating a duplicate uses a CREATE TABLE AS statement. The statement copies all columns, column definitions, and populates the copy with the source table’s data.

查看其在下面提供的语法 −

Review its syntax given below −

CREATE TABLE clone_tbl AS
   SELECT columns
   FROM original_tbl
   WHERE conditions];

查看其在下面提供的示例用法 −

Review an example of its use below −

CREATE TABLE products_copy_tbl AS
   SELECT *
   FROM products_tbl;

MariaDB - Sequences

在版本 10.0.3 中,MariaDB 引入了一种名为序列的存储引擎。它的临时解决方案为操作生成一个整数序列,然后终止它。序列包含按降序或升序排列的正整数,并使用起始、结束和增量值。

In version 10.0.3, MariaDB introduced a storage engine known as sequence. Its ad hoc generates an integer sequence for operations, and then it terminates. The sequence contains positive integers in descending or ascending order, and uses a starting, ending, and increment value.

由于其虚拟(未写入磁盘)性质,它不允许在多个查询中使用,只允许在其原始查询中使用。但是,可以通过 ALTER 命令将序列表转换为标准表。如果删除转换后的表,则序列表仍然存在。序列也不能产生负数或在最小值/最大值处旋转。

It does not allow use in multiple queries, only in its original query because of its virtual (not written to disk) nature. However, sequence tables can be converted to standard tables through an ALTER command. If a converted table is deleted, the sequence table still exists. Sequences also cannot produce negative numbers or rotate at the minimum/maximum.

Installing the Sequence Engine

使用序列需要安装序列引擎,MariaDB 将其作为插件而不是二进制文件进行分发。使用以下命令安装它 −

Using sequences requires installing the sequence engine, which MariaDB distributes as a plugin rather than binary. Install it with the following command −

INSTALL SONAME "ha_sequence";

安装后,验证它 −

After installation, verify it −

SHOW ENGINES\G

请记住,在安装引擎后,您无法使用序列语法创建名称为标准表的表,但您可以创建带有序列语法名称的临时表。

Remember that after engine installation, you cannot create a standard table with a name that uses sequence syntax, but you can create a temporary table with a sequence-syntax name.

Creating Sequence

有两种序列创建方法 −

There are two methods of sequence creation −

  1. Create a table and use the AUTO_INCREMENT attribute to define a column as auto-increment.

  2. Use an existing database and use a sequence SELECT query to produce a sequence. The query uses seq_ [FROM] to[TO] or seq_[FROM]to[TO]_step_STEP syntax.

最佳做法更偏好使用第二种方法。查看下面提供的一个序列创建示例 −

Best practices prefer the use of the second method. Review an example of a sequence creation given below −

SELECT * FROM seq_77_to_99;

序列有许多用途 −

Sequences have many uses −

  1. Locate missing values within a column to protect against related issues in operations −

SELECT myseq.seq FROM seq_22_to_28 myseq LEFT JOIN table1 t ON myseq.seq
   = x.y WHERE x.y IS NULL;
  1. Construct a combination of values −

SELECT x1.seq, x2.seq FROM seq_5_to_9 x1 JOIN seq_5_to_9 x2 ORDER BY 5, 6;
  1. Find multiples of a number −

SELECT seq FROM seq_3_to_100_step_4;
  1. Construct a date sequence for use in applications like booking systems.

  2. Construct a time sequence.

MariaDB - Managing Duplicates

如前文所讨论的,MariaDB 在某些情况下允许重复记录和表。由于不同的数据或对象类型,或者由于操作对象的独特寿命或存储,其中一些重复实际上并不是重复的。这些重复通常也不会有问题。

MariaDB, as discussed in earlier lessons, allows duplicate records and tables in some situations. Some of these duplicates are not in fact duplicates due to distinct data or object types, or as a result of unique lifespan or storage of the operation object. These duplicates also typically pose no problems.

在某些情况下,重复确实会造成问题,并且它们经常是由隐式操作或 MariaDB 命令的宽松策略引起的。有方法可以控制此问题,查找重复项,删除重复项并阻止创建重复项。

In some situations, duplicates do cause problems, and they often appear due to implicit actions or the lenient policy of a MariaDB command. There are ways to control this issue, find duplicates, delete duplicates, and prevent duplicate creation.

Strategies and Tools

有四种关键方法可以管理重复项 -

There are four key ways to manage duplicates −

  1. Fish for them with JOIN, and delete them with a temporary table.

  2. Use INSERT…​ON DUPLICATE KEY UPDATE to update on discovery of a duplicate.

  3. Use DISTINCT to prune the results of a SELECT statement and remove duplicates.

  4. Use INSERT IGNORE to stop insertion of duplicates.

Using Join with a Temporary Table

只需执行类似于内联连接的半连接,然后使用临时表删除找到的重复项。

Simply perform a semi-join like an inner join, and then remove the duplicates found with a temporary table.

Using INSERT

当 INSERT…​ON DUPLICATE KEY UPDATE 发现重复唯一或主键时,它会执行更新。当发现多个唯一键时,它只会更新第一个键。因此,不要在具有多个唯一索引的表上使用它。

When INSERT…​ON DUPLICATE KEY UPDATE discovers a duplicate unique or primary key, it performs an update. On discovery of multiple unique keys, it updates only the first. Hence, do not use it on tables with multiple unique indices.

复习以下示例,它揭示了在向已填入字段中插入索引值时表中发生的情况 -

Review the following example, which reveals what happens in a table containing indexed values on insertion into a populated field −

INSERT INTO add_dupl VALUES (1,'Apple');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Note - 如果它找不到键,则 INSERT…​ON DUPLICATE KEY UPDATE 语句将像普通插入语句一样执行。

Note − If it finds no key, an INSERT…​ON DUPLICATE KEY UPDATE statement executes like a normal insert statement.

Using DISTINCT

DISTINCT 子句从结果中删除重复项。DISTINCT 子句的一般语法如下 -

DISTINCT clauses remove duplicates from results. The general syntax for a DISTINCT clause is as follows −

SELECT DISTINCT fields
FROM table
[WHERE conditions];

Note - 带有 DISTINCT 子句的语句的结果 -

Note − The results of a statement with a DISTINCT clause −

  1. When using one expression, it returns unique values for it.

  2. When using multiple expressions, it returns unique combinations.

  3. It does not ignore NULL values; thus, results also contain NULLs as unique values.

使用 DISTINT 从句对单一表达式查看以下语句 -

Review the following statement using a DISTINCT clause for a single expression −

SELECT DISTINCT product_id
FROM products
WHERE product_name = 'DustBlaster 5000';

使用多个表达式查看以下示例 -

Review the following example using multiple expressions −

SELECT DISTINCT product_name, product_id
FROM products
WHERE product_id < 30

Using INSERT IGNORE

ON DUPLICATE KEY IGNORE 语句指示 MariaDB 在发现重复记录时取消插入。查看下面给出的示例 -

An INSERT IGNORE statement instructs MariaDB to cancel insertion on discovery of a duplicate record. Review an example of its use given below −

mysql> INSERT IGNORE INTO customer_tbl (LN, FN)
   VALUES( 'Lex', 'Luther');

同样,注意重复背后的逻辑。某些表基于该表数据的特性需要重复记录。在管理重复记录的策略中满足该需求。

Also, note the logic behind duplicates. Some tables require duplicates based on the nature of that table data. Accommodate that need in your strategy for managing duplicate records.

MariaDB - SQL Injection Protection

接受用户输入这一简单的行为为利用者打开了方便之门。该问题主要源于对数据的逻辑管理,但幸运的是,避免这些主要缺陷相当容易。

The simple act of accepting user input opens the door to exploits. The problem stems primarily from the logical management of data, but luckily, it is fairly easy to avoid these major flaws.

SQL 注入的机会通常出现在用户输入名称等数据时,而代码逻辑无法分析此输入时。代码允许攻击者插入 MariaDB 语句,该语句将在数据库上运行。

Opportunities for SQL injection typically occur on users entering data like a name, and the code logic failing to analyze this input. The Code, instead, allows an attacker to insert a MariaDB statement, which will run on the database.

总是考虑由用户输入的数据,它们可能是可疑的,并且在任何处理之前都需要进行强有力的验证。通过模式匹配执行此验证。例如,如果期望的输入是用户名,请将输入的字符限制为字母数字字符和下划线以及一定的长度。查看下面给出的示例 -

Always consider data entered by users, suspect and are in need of strong validation prior to any processing. Perform this validation through pattern matching. For example, if the expected input is a username, restrict entered characters to alphanumeric chars and underscores, and to a certain length. Review an example given below −

if(check_match("/^\w{8,20}$/", $_GET['user_name'], $matches)) {
   $result = mysql_query("SELECT * FROM system_users WHERE user_name = $matches[0]");
} else {
   echo "Invalid username";
}

同样,在创建输入约束时利用 REGEXP 运算符和 LIKE 从句。

Also, utilize the REGEXP operator and LIKE clauses in creating input constraints.

考虑所有类型对输入的必要显性控制,例如 -

Consider all types of necessary explicit control of input such as −

  1. Control the escape characters used.

  2. Control the specific appropriate data types for input. Limit input to the necessary data type and size.

  3. Control the syntax of entered data. Do not allow anything outside of the needed pattern.

  4. Control the terms permitted. Blacklist SQL keywords.

您可能不知道注入攻击的危险,或者认为它们无关紧要,但它们位居安全问题之首。此外,考虑以下两个条目的效果 -

You may not know the dangers of injection attacks, or may consider them insignificant, but they top the list of security concerns. Furthermore, consider the effect of these two entries −

1=1
-or-
*

允许其中任何一个与正确命令一起输入的代码可能会导致揭示数据库上的所有用户数据或删除数据库上的所有数据,并且两种注入都不是特别聪明。在某些情况下,攻击者甚至不需要花时间检查漏洞;他们只需使用简单的输入执行盲目攻击。

Code allowing either of those to be entered along with the right command may result in revealing all user data on the database or deleting all data on the database, and neither injection is particularly clever. In some cases, attackers do not even spend time examining holes; they perform blind attacks with simple input.

同样,考虑任何编程/脚本语言(与 MariaDB 配对)提供的模式匹配和正则表达式工具,它们提供了更多的控制,有时会提供更好的控制。

Also, consider the pattern matching and regular expression tools provided by any programming/scripting language paired with MariaDB, which provide more control, and sometimes better control.

MariaDB - Backup Methods

数据是业务和运营的基础,由于存在各种可能的威胁(例如攻击者、系统故障、不良升级和维护错误),因此备份仍然至关重要。这些备份形式多样,并且有许多选项可以创建它们,并在这些过程中有更广泛的选项集。需要记住的重要事项是数据库类型、关键信息和所涉及的结构。这些信息决定了您的最佳选择。

Data serves as the foundation of business and operations, and with various possible threats (e.g., attackers, system failures, bad upgrades, and maintenance errors) out there, backups remain critical. These backups take many forms, and many options exist for creating them with an even wider set of options within those processes. The important things to remember are the database type, the critical information, and the structure involved. This information determines your best option.

OPTIONS

备份的主要选项包括逻辑备份和物理备份。逻辑备份保存 SQL 语句以恢复数据。物理备份包含数据的副本。

The main options for backups include logical backups and physical backups. Logical backups hold SQL statements for restoring data. Physical backups contain copies of data.

  1. Logical backups offer the flexibility of restoring data on another machine with a different configuration in contrast to physical backups, which are often limited to the same machine and database type. Logical backups occur at database and table level, and physical occur at directory and file level.

  2. Physical backups are smaller in size than logical, and also take less time to perform and restore. Physical backups also include log and configuration files, but logical backups do not.

Backup Tools

用于 MariaDB 备份的主要工具是 mysqldump 。它提供逻辑备份和灵活性。它还被证明是小型数据库的绝佳选择。 Mysqldump 将数据转储到 SQL、CSV、XML 和其他许多格式中。在没有明确指令的情况下,其输出不会保留存储过程、视图和事件。

The main tool used for MariaDB backups is mysqldump. It offers logical backups and flexibility. It also proves an excellent option for small databases. Mysqldump dumps data into SQL, CSV, XML, and many other formats. Its output does not retain stored procedures, views, and events without explicit instruction.

有三个用于 mysqldump 备份的选项 −

There are three options for mysqldump backups −

  1. Raw data − Dump a table as a raw data file through the --tab option, which also specifies the destination of the file −

$ mysqldump -u root -p --no-create-info \
   --tab=/tmp PRODUCTS products_tbl
  1. Data/Definitions export − This option allows a single or multiple tables to be exported to a file, and supports backing up all existing databases on the host machine. Examine an example of exporting contents or definitions to a file

$ mysqldump -u root -p PRODUCTS products_tbl > export_file.txt
  1. Transfer − You can also output databases and tables to another host

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

Using THE SELECT…​INTO OUTFILE Statement

导出数据的另一个选项采用 SELECT…​INTO OUTFILE 语句。此简单选项将表输出到一个简单的格式化文本文件中 −

Another option for exporting data employs the SELECT…​INTO OUTFILE statement. This simple option outputs the table into a simple formatted text file −

mysql> SELECT * FROM products_tbl
   -> INTO OUTFILE '/tmp/products.txt';

其属性允许您根据自己的首选规范设置文件格式。

Its attributes allow formatting the file to your preferred specifications.

请注意此语句的以下特性 −

Note the following qualities of this statement −

  1. The file name must specify your desired location for the output.

  2. You need MariaDB file privileges to execute the statement.

  3. The output file name must be unique.

  4. You need login credentials on the host.

  5. In a UNIX environment, the output file is world readable, but its server ownership affects your ability to delete it. Ensure you have privileges.

Using CONNECT in Backups

CONNECT 处理程序允许导出数据。当 SELECT…​INTO OUTFILE 操作不支持文件格式时,这主要在情况下很有用。

The CONNECT handler allows exporting of data. This proves useful primarily in situations when the SELECT…​INTO OUTFILE operation does not support the file format.

查看以下示例:

Review the following example −

create table products
engine = CONNECT table_type = XML file_name = 'products.htm' header = yes
option_list = 'name = TABLE,coltype = HTML,attribute = border = 1;cellpadding = 5'

select plugin_name handler, plugin_version version, plugin_author
author, plugin_description description, plugin_maturity maturity
from information_schema.plugins where plugin_type = 'STORAGE ENGINE';

Other Tools

其他备份选项如下 −

Other options for backups are as follows −

  1. XtraBackup − This option targets XtraDB/InnoDB databases and works with any storage engine. Learn more about this tool from Percona’s official site.

  2. Snapshots − Some filesystems allow snapshots. The process consists of flushing the tables with read lock, mounting the snapshot, unlocking the tables, copying the snapshot, and then unmounting the snapshot.

  3. LVM − This popular method employs a Perl script. It gets a read lock on every table and flushes caches to disk. Then it gets a snapshot and unlocks the tables. Consult the official mylvmbackup website for more information.

  4. TokuBackup − This solution provided by Percona provides hot backups taking into account the problems and limitations of InnoDB backup options. It produces a transactional sound copy of files while applications continue to manipulate them.Consult the Percona website for more information..

INNODB Considerations

InnoDB 使用缓冲池来提高性能。在备份中,配置 InnoDB 以避免将整个表复制到缓冲池,因为逻辑备份通常执行全表扫描。

InnoDB uses a buffer pool for performance enhancement. In a backup, configure InnoDB to avoid copying an entire table into the buffer pool because logical backups typically perform full table scans.

MariaDB - Backup Loading Methods

在本章中,我们将了解各种备份加载方法。从备份还原数据库是一个简单但有时非常漫长的过程。

In this chapter, we will learn about various backup loading methods. Restoring a database from a backup is a simple and sometimes terribly long process.

加载数据有三种选择:LOAD DATA 语句、mysqlimport 和简单的 mysqldump 还原。

There are three options in loading data: the LOAD DATA statement, mysqlimport, and a simple mysqldump restore.

Using LOAD DATA

LOAD DATA 语句用作批量加载器。查看其中加载文本文件的用例示例 −

The LOAD DATA statement functions as a bulk loader. Review an example of its use that loads a text file −

mysql> LOAD DATA LOCAL INFILE 'products_copy.txt' INTO TABLE empty_tbl;

注意 LOAD DATA 语句的以下特点 −

Note the following qualities of a LOAD DATA statement −

  1. Use the LOCAL keyword to prevent MariaDB from performing a deep search of the host, and use a very specific path.

  2. The statement assumes a format consisting of lines terminated by linefeeds (newlines) and data values separated by tabs.

  3. Use the FIELDS clause to explicitly specify formatting of fields on a line. Use the LINES clause to specify line ending. Review an example below.

mysql> LOAD DATA LOCAL INFILE 'products_copy.txt' INTO TABLE empty_tbl
   FIELDS TERMINATED BY '|'
   LINES TERMINATED BY '\n';
  1. The statement assumes columns within the datafile use the same order of the table. If you need to set a different order, you can load the file as follows −

mysql> LOAD DATA LOCAL INFILE 'products_copy.txt' INTO TABLE empty_tbl (c, b, a);

Using MYSQLIMPORT

mysqlimport 工具充当一个 LOAD DATA 封装器,允许从命令行执行相同的操作。

The mysqlimport tool acts as a LOAD DATA wrapper allowing the same operations from the command line.

按如下方式加载数据 −

Load data as follows −

$ mysqlimport -u root -p --local database_name source_file.txt

按如下方式指定格式 −

Specify formatting as follows −

$ mysqlimport -u root -p --local --fields-terminated-by="|" \
   --lines-terminated-by="\n" database_name source_file.txt

使用 — columns 选项指定列顺序 −

Use the --columns option to specify column order −

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

Using MYSQLDUMP

使用 mysqldump 还原需要此简单语句将转储文件加载回主机 −

Restoring with mysqldump requires this simple statement for loading the dump file back into the host −

shell> mysql database_name < source_file.sql

SPECIAL CHARACTERS AND QUOTES

在 LOAD DATA 语句中,可能无法正确解释引号和特殊字符。该语句假定未加引号的值,并将反斜杠视为转义字符。使用 FIELDS 子句指定格式。用“ENCLOSED BY”指向引号,这会导致从数据值中剥离引号。用“ESCAPED BY”更改转义。

In a LOAD DATA statement, quotes and special characters may not be interpreted correctly. The statement assumes unquoted values and treats backslashes as escape characters. Use the FIELDS clause to specify formatting. Point to quotes with “ENCLOSED BY,” which causes the stripping of quotes from data values. Change escapes with “ESCAPED BY.”

MariaDB - Useful Functions

本章包含一个最常用函数列表,提供了定义、说明和示例。

This chapter contains a list of the most frequently used functions, offering definitions, explanations, and examples.

MariaDB Aggregate Functions

最常用的聚合函数如下 −

Most frequently used aggregate functions are given below −

Sr.No

Name & Description

1

COUNT It counts the number of records. Example − SELECT COUNT(*) FROM customer_table;

2

MIN It reveals the minimum value of a set of records. Example − SELECT organization, MIN(account) FROM contracts GROUP BY organization;

3

MAX It reveals the maximum value of a set of records. Example − SELECT organization, MAX(account_size) FROM contracts GROUP BY organization;

4

AVG It calculates the average value of a set of records. Example − SELECT AVG(account_size) FROM contracts;

5

SUM It calculates the sum of a set of records. Example − SELECT SUM(account_size) FROM contracts;

MariaDB Age Calculation

TIMESTAMPDIFF 函数可用来计算年龄 −

The TIMESTAMPDIFF function provides a way to calculate age −

SELECT CURDATE() AS today;
SELECT ID, DOB, TIMESTAMPDIFF(YEAR,DOB,'2015-07-01') AS age FROM officer_info;

MariaDB String Concatenation

CONCAT 函数连接操作后的返回结果字符串。您可以利用一个或多个参数。查看其给出的语法 −

The CONCAT function returns the resulting string after a concatenation operation. You can utilize one or more arguments. Review its syntax given below −

SELECT CONCAT(item, item,...);

查看以下示例:

Review the following example −

SELECT CONCAT('Ram', 'bu', 'tan');
Output:Rambutan

MariaDB Date/Time Functions

以下是重要的日期函数 −

Given below are important date functions −

Sr.No

Name & Description

1

CURDATE() It returns the date in yyyy-mm-dd or yyyymmdd format. Example − SELECT CURDATE();

2

DATE() It returns the date in multiple formats. Example −CREATE TABLE product_release_tbl (x DATE);

3

CURTIME() It returns the time in HH:MM:SS or HHMMSS.uuuuuu format. Example − SELECT CURTIME();

4

DATE_SUB() It adds or subtracts a number of days from the specified date. Example − SELECT DATE_SUB('2016-02-08', INTERVAL 60 DAY);

5

DATEDIFF() It determines the days between two dates. Example − SELECT DATEDIFF('2016-01-01 23:59:59','2016-01-03');

6

DATE ADD() It adds or subtracts any unit of time to/from the date and time. Example − SELECT DATE_ADD('2016-01-04 23:59:59', INTERVAL 22 SECOND);

7

EXTRACT() It extracts a unit from the date. Example − SELECT EXTRACT(YEAR FROM '2016-01-08');

8

NOW() It returns the current date and time in either yyyy-mm-dd hh:mm:ss or yyyymmddhhmmss.uuuuuu format. Example − SELECT NOW();

9

DATE FORMAT() It formats the date in accordance with the specified format string. Example − SELECT DATE_FORMAT('2016-01-09 20:20:00', '%W %M %Y');

以下是部分重要的时间函数 −

Following are some important time functions −

Sr.No

Name & Description

1

HOUR() It returns the hour of the time, or the hours elapsed. Example − SELECT HOUR('19:17:09');

2

LOCALTIME() It functions exactly like NOW().

3

MICROSECOND() It returns the microseconds of the time. Example − SELECT MICROSECOND('16:30:00.543876');

4

MINUTE() It returns the minutes of the time. Example − SELECT MINUTE('2016-05-22 17:22:01');

5

SECOND() It returns the seconds of the date. Example − SELECT SECOND('2016-03-12 16:30:04.000001');

6

TIME_FORMAT() It formats the time in accordance with the specified format string. Example − SELECT TIME_FORMAT('22:02:20', '%H %k %h %I %l');

7

TIMESTAMP() It provides a timestamp for an activity in the format yyyy-mm-dd hh:mm:dd. Example − CREATE TABLE orders_ (ID INT, tmst TIMESTAMP);

MariaDB Numeric Functions

下面是 MariaDB 中一些重要的数值函数−

Given below are some important numeric functions in MariaDB −

Sr.No

Name & Description

1

TRUNCATE() It returns a truncated number to decimal place specification. Example − SELECT TRUNCATE(101.222, 1);

2

COS() It returns the cosine of x radians. Example − SELECT COS(PI());

3

CEILING() It returns the smallest integer not below x. Example − SELECT CEILING(2.11);

4

DEGREES() It converts radians to degrees. Example − SELECT DEGREES(PI());

5

DIV() It performs integer division. Example − SELECT 100 DIV 4;

6

EXP() It returns e to the power of x. Example − SELECT EXP(2);

7

FLOOR() It returns the largest integer not above x. Example − SELECT FLOOR(2.01);

8

LN() It returns the natural logarithm of x. Example − SELECT LN(3);

9

LOG() It returns the natural logarithm or the logarithm to a given base. Example − SELECT LOG(3);

10

SQRT() It returns the square root. Example − SELECT SQRT(16);

MariaDB String Functions

下面列出一些重要的字符串函数−

Important string functions are given below −

Sr.No

Name & Description

1

INSTR() It returns the position of the first instance of a substring. Example − SELECT INSTR('rambutan', 'tan');

2

RIGHT() It returns the rightmost string characters. Example − SELECT RIGHT('rambutan', 3);

3

LENGTH() It returns the byte length of a string. Example − SELECT LENGTH('rambutan');

4

LOCATE() It returns the position of the first instance of a substring. Example − SELECT LOCATE('tan', 'rambutan');

5

INSERT() It returns a string, with a specified substring at a certain position, that was modified. Example − SELECT INSERT('ramputan', 4, 1, 'b');

6

LEFT() It returns the leftmost characters. Example − SELECT LEFT('rambutan', 3);

7

UPPER() It changes characters to uppercase. Example − SELECT UPPER(lastname);

8

LOWER() It changes characters to lowercase. Example − SELECT LOWER(lastname);

9

STRCMP() It compares strings and returns 0 when they are equal. Example − SELECT STRCMP('egg', 'cheese');

10

REPLACE() It returns a string after replacing characters. Example − SELECT REPLACE('sully', 'l', 'n');

11

REVERSE() It reverses characters in a string. Example − SELECT REVERSE('racecar');

12

REPEAT() It returns a string repeating given characters x times. Example − SELECT REPEAT('ha ', 10);

13

SUBSTRING() It returns a substring from a string, starting at position x. Example − SELECT SUBSTRING('rambutan',3);

14

TRIM() It removes trailing/leading characters from a string. Example − SELECT TRIM(LEADING '_' FROM '_rambutan');