本文最后更新于：2024年10月22日 下午

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。本文记录 MySQL 的基本知识。

概述

数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

  1. 数据以表格的形式出现

  2. 每行为各种记录名称

  3. 每列为记录名称所对应的数据域

  4. 许多的行和列组成一张表单

  5. 若干的表单组成database

RDBMS 术语

先了解下RDBMS的一些术语：

  • 数据库: 数据库是一些关联表的集合。
  • 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  • 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
  • **行：**一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
  • 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
  • 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  • **外键：**外键用于关联两个表。
  • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
  • **索引：**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:

  • 表头(header): 每一列的名称;
  • 列(col): 具有相同数据类型的数据的集合;
  • 行(row): 每一行用来描述某条记录的具体信息;
  • 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
  • 键(key): 键的值在当前列中具有唯一性。

MySQL数据库

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

  • MySQL 是开源的，目前隶属于 Oracle 旗下产品。
  • MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
  • MySQL 使用标准的 SQL 数据语言形式。
  • MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
  • MySQL 对 PHP 有很好的支持，PHP 是很适合用于 Web 程序开发。
  • MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
  • MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

MySQL 管理

Windows 系统下

启动 MySQL 服务器:

1、通过 “服务” 管理工具： 打开"运行"对话框（Win + R），输入 services.msc，找到"MySQL"服务，右击选择"启动"。

2、通过命令提示符： 打开命令提示符（以管理员身份），输入以下命令：

1
net start mysql

关闭MySQL服务器:

1、**通过 “服务” 管理工具：**同样打开"运行"对话框，输入 services.msc，找到 “MySQL” 服务，右击选择"停止"。

2、通过命令提示符： 打开命令提示符（以管理员身份），输入以下命令：

1
net stop mysql

Linux 系统下

1、启动 MySQL 服务：

使用 systemd命令（适用于大多数现代 Linux 发行版，如 Ubuntu、CentOS 等）：

1
sudo systemctl start mysql

使用 service 命令（在一些较旧的发行版中）：

1
sudo service mysql start

2、关闭 MySQL 服务：

使用 systemd：

1
sudo systemctl stop mysql

使用 service 命令：

1
sudo service mysql stop

3、重启 MySQL 服务：

使用 systemd：

1
sudo systemctl restart mysql

使用 service 命令：

1
sudo service mysql restart

4、检查 MySQL 服务状态：

使用 systemd命令：

1
sudo systemctl status mysql

使用 service 命令：

1
sudo service mysql status

Mac OS 系统

启动 MySQL 服务：

使用命令行：

1
sudo /usr/local/mysql/support-files/mysql.server start

关闭 MySQL 服务：

使用命令行：

1
sudo /usr/local/mysql/support-files/mysql.server stop

重启 MySQL 服务：

使用命令行：

1
sudo /usr/local/mysql/support-files/mysql.server restart

检查 MySQL 服务状态：

使用命令行：

1
sudo /usr/local/mysql/support-files/mysql.server status

以上命令中，mysql 可能会因为安装路径或版本的不同而有所变化。

在 Mac OS 中，MySQL 的安装路径通常是 /usr/local/mysql/，因此启动和关闭 MySQL 服务需要使用这个路径下的 mysql.server 脚本。

MySQL 用户设置

在 MySQL 中，用户设置包括创建用户、设置权限、管理用户等操作。以下是一些常用的 MySQL 用户设置操作，包括创建用户、设置权限、查看和删除用户等。

创建用户

要创建一个新用户，你可以使用以下 SQL 命令：

1
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  • username：用户名。
  • host：指定用户可以从哪些主机连接。例如，localhost 仅允许本地连接，% 允许从任何主机连接。
  • password：用户的密码。

实例

1
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123';

授权权限

创建用户后，你需要授予他们访问权限，使用 GRANT 命令来授予权限：

1
GRANT privileges ON database_name.* TO 'username'@'host';
  • privileges：所需的权限，如 ALL PRIVILEGES、SELECT、INSERT、UPDATE、DELETE 等。
  • database_name.*：表示对某个数据库或表授予权限。database_name.* 表示对整个数据库的所有表授予权限，database_name.table_name 表示对指定的表授予权限。
  • TO 'username'@'host'：指定授予权限的用户和主机。

实例

1
GRANT ALL PRIVILEGES ON test_db.* TO 'john'@'localhost';

刷新权限

授予或撤销权限后，需要刷新权限使更改生效：

1
FLUSH PRIVILEGES;
  • 举例：在数据库 test 上新建一位只读用户 reader
1
2
3
4
5
6
7
8
# root 用户登录
mysql -u root -p
# 创建用户
CREATE USER 'reader'@'%' IDENTIFIED BY '123456';
# 授权 SELECT 查询权限
GRANT SELECT ON test.* TO 'reader'@'%';
# 刷新权限
FLUSH PRIVILEGES;

查看用户权限

要查看特定用户的权限，可以使用以下命令：

1
SHOW GRANTS FOR 'username'@'host';

实例

1
SHOW GRANTS FOR 'john'@'localhost';

撤销权限

要撤销用户的权限，使用 REVOKE 命令：

1
REVOKE privileges ON database_name.* FROM 'username'@'host';

实例

1
REVOKE ALL PRIVILEGES ON test_db.* FROM 'john'@'localhost';

删除用户

如果需要删除用户，可以使用以下命令：

1
DROP USER 'username'@'host';

实例

1
DROP USER 'john'@'localhost';

修改用户密码

要修改用户的密码，可以使用 ALTER USER 命令：

1
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

实例

1
ALTER USER 'john'@'localhost' IDENTIFIED BY 'newpassword456';

修改用户主机

要更改用户的主机（即允许从哪些主机连接），可以先删除用户，再重新创建一个新的用户。

实例

  • 删除旧用户
1
DROP USER 'john'@'localhost';
  • 重新创建用户并指定新的主机
1
CREATE USER 'john'@'%' IDENTIFIED BY 'password123';

创建用户时指定权限

在创建用户时，也可以同时授予权限（在 MySQL 8.0.16 及更高版本）：

实例

1
2
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON test_db.* TO 'john'@'localhost';

/etc/my.cnf 文件配置

/etc/my.cnf 文件是 MySQL 配置文件，用于配置 MySQL 服务器的各种参数和选项。

一般情况下，不需要修改该配置文件，该文件默认配置如下：

1
2
3
4
5
6
7
8
9
10
11
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

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

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

在配置文件中，你可以指定不同的错误日志文件存放的目录，一般你不需要改动这些配置。

/etc/my.cnf 文件在不同的系统和 MySQL 版本中可能有所不同，但是一般包含以下几个部分：

基本设置

  • basedir: MySQL 服务器的基本安装目录。
  • datadir: 存储 MySQL 数据文件的位置。
  • socket: MySQL 服务器的 Unix 套接字文件路径。
  • pid-file: 存储当前运行的 MySQL 服务器进程 ID 的文件路径。
  • port: MySQL 服务器监听的端口号，默认是 3306。

服务器选项

  • bind-address: 指定 MySQL 服务器监听的 IP 地址，可以是 IP 地址或主机名。
  • server-id: 在复制配置中，为每个 MySQL 服务器设置一个唯一的标识符。
  • default-storage-engine: 默认的存储引擎，例如 InnoDB 或 MyISAM。
  • max_connections: 服务器可以同时维持的最大连接数。
  • thread_cache_size: 线程缓存的大小，用于提高新连接的启动速度。
  • query_cache_size: 查询缓存的大小，用于提高相同查询的效率。
  • default-character-set: 默认的字符集。
  • collation-server: 服务器的默认排序规则。

性能调优

  • innodb_buffer_pool_size: InnoDB 存储引擎的缓冲池大小，这是 InnoDB 性能调优中最重要的参数之一。
  • key_buffer_size: MyISAM 存储引擎的键缓冲区大小。
  • table_open_cache: 可以同时打开的表的缓存数量。
  • thread_concurrency: 允许同时运行的线程数。

安全设置

  • skip-networking: 禁止 MySQL 服务器监听网络连接，仅允许本地连接。
  • skip-grant-tables: 以无需密码的方式启动 MySQL 服务器，通常用于恢复忘记的 root 密码，但这是一个安全风险。
  • auth_native_password=1: 启用 MySQL 5.7 及以上版本的原生密码认证。

日志设置

  • log_error: 错误日志文件的路径。
  • general_log: 记录所有客户端连接和查询的日志。
  • slow_query_log: 记录执行时间超过特定阈值的慢查询。
  • log_queries_not_using_indexes: 记录未使用索引的查询。

复制设置

  • master_host 和 master_user: 主服务器的地址和复制用户。
  • master_password: 复制用户的密码。
  • master_log_file 和 master_log_pos: 用于复制的日志文件和位置。

管理MySQL的命令

以下列出了使用Mysql数据库过程中常用的命令：

  • USE 数据库名 :
    选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

    1
    2
    mysql> use RUNOOB;
    Database changed

  • SHOW DATABASES:
    列出 MySQL 数据库管理系统的数据库列表。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    mysql> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | RUNOOB             |
    | cdcol              |
    | mysql              |
    | onethink           |
    | performance_schema |
    | phpmyadmin         |
    | test               |
    | wecenter           |
    | wordpress          |
    +--------------------+
    10 rows in set (0.02 sec)

  • SHOW TABLES:
    显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    mysql> use RUNOOB;
    Database changed
    mysql> SHOW TABLES;
    +------------------+
    | Tables_in_runoob |
    +------------------+
    | employee_tbl     |
    | runoob_tbl       |
    | tcount_tbl       |
    +------------------+
    3 rows in set (0.00 sec)

  • SHOW COLUMNS FROM *数据表*:
    显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> SHOW COLUMNS FROM runoob_tbl;
    +-----------------+--------------+------+-----+---------+-------+
    | Field           | Type         | Null | Key | Default | Extra |
    +-----------------+--------------+------+-----+---------+-------+
    | runoob_id       | int(11)      | NO   | PRI | NULL    |       |
    | runoob_title    | varchar(255) | YES  |     | NULL    |       |
    | runoob_author   | varchar(255) | YES  |     | NULL    |       |
    | submission_date | date         | YES  |     | NULL    |       |
    +-----------------+--------------+------+-----+---------+-------+
    4 rows in set (0.01 sec)

  • SHOW INDEX FROM *数据表*:
    显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

    1
    2
    3
    4
    5
    6
    7
    mysql> SHOW INDEX FROM runoob_tbl;
    +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | runoob_tbl |          0 | PRIMARY  |            1 | runoob_id   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
    +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    1 row in set (0.00 sec)

  • SHOW TABLE STATUS [FROM db_name] [LIKE ‘pattern’] \G:
    该命令将输出Mysql数据库管理系统的性能及统计信息。

    1
    2
    3
    4
    mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的信息

    mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob开头的表的信息
    mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查询结果按列打印

MySQL 连接

您可以使用 MySQL 二进制方式进入到 mysql 命令提示符下来连接 MySQL 数据库，格式如下：

1
mysql -u your_username -p

参数说明：

  • -u 参数用于指定用户名。
  • -p 参数表示需要输入密码。

以下是从命令行中连接 mysql 服务器的简单实例：

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

按照提示输入密码，并按下 Enter 键。

在登录成功后会出现 mysql> 命令提示窗口，你可以在上面执行任何 SQL 语句。

数据库操作

创建数据库

我们可以在登陆 MySQL 服务后，使用 create 命令创建数据库，语法如下:

1
CREATE DATABASE 数据库名;

以下命令简单的演示了创建数据库的过程，数据名为 RUNOOB:

1
2
3
4
[root@host]# mysql -u root -p   
Enter password:******  # 登录后进入终端

mysql> create DATABASE RUNOOB;

建数据库的基本语法如下：

1
2
3
CREATE DATABASE [IF NOT EXISTS] database_name
  [CHARACTER SET charset_name]
  [COLLATE collation_name];

如果你希望在创建数据库时指定一些选项，可以使用 CREATE DATABASE 语句的其他参数，例如，你可以指定字符集和排序规则：

1
2
3
CREATE DATABASE mydatabase
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

如果数据库已经存在，执行 CREATE DATABASE 将导致错误。

为了避免这种情况，你可以在 CREATE DATABASE 语句中添加 IF NOT EXISTS 子句：

1
CREATE DATABASE IF NOT EXISTS mydatabase;

删除数据库

使用普通用户登陆 MySQL 服务器，你可能需要特定的权限来创建或者删除 MySQL 数据库，所以我们这边使用 root 用户登录，root 用户拥有最高权限。

在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。

drop 命令删除数据库

drop 命令格式：

1
2
3
DROP DATABASE <database_name>;        -- 直接删除数据库，不检查是否存在
或
DROP DATABASE [IF EXISTS] <database_name>;

参数说明：

  • IF EXISTS 是一个可选的子句，表示如果数据库存在才执行删除操作，避免因为数据库不存在而引发错误。
  • database_name 是你要删除的数据库的名称。

例如删除名为 RUNOOB 的数据库：

1
2
3
4
5
-- 直接删除数据库，不检查是否存在
mysql> DROP DATABASE RUNOOB;

-- 删除数据库，如果存在的话
DROP DATABASE IF EXISTS RUNOOB;

注意： 在执行删除数据库操作前，请确保你确实想要删除数据库及其所有数据，因为该操作是不可逆的。为了避免误操作，通常建议在执行删除之前备份数据库。

选择数据库

在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库。

从命令提示窗口中选择 MySQL 数据库

在 mysql> 提示窗口中可以很简单的选择特定的数据库。

在 MySQL 中，要选择要使用的数据库，可以使用 USE 语句，以下是基本的语法：

1
USE database_name;

参数说明：

  • database_name 是你要选择的数据库的名称。

选择来数据库后，你的后续 SQL 查询和操作在指定的数据库 database_name 上执行。

MySQL 数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 Bytes (-128，127) (0，255) 小整数值
SMALLINT 2 Bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 Bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 Bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 Bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 Bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型 大小 ( bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ YYYY-MM-DD hh:mm:ss 混合日期和时间值
TIMESTAMP 4 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYY-MM-DD hh:mm:ss 混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

枚举与集合类型（Enumeration and Set Types）

  • ENUM: 枚举类型，用于存储单一值，可以选择一个预定义的集合。
  • SET: 集合类型，用于存储多个值，可以选择多个预定义的集合。

空间数据类型（Spatial Data Types）

GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: 用于存储空间数据（地理信息、几何图形等）。

数据表操作

创建数据表

创建 MySQL 数据表需要以下信息：

  • 表名
  • 表字段名
  • 定义每个表字段的数据类型

语法

以下为创建 MySQL 数据表的 SQL 通用语法：

1
2
3
4
5
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

参数说明：

  • table_name 是你要创建的表的名称。
  • column1, column2, … 是表中的列名。
  • datatype 是每个列的数据类型。

以下是一个具体的实例，创建一个用户表 users：

1
2
3
4
5
6
7
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    birthdate DATE,
    is_active BOOLEAN DEFAULT TRUE
);

实例解析：

  • id: 用户 id，整数类型，自增长，作为主键。
  • username: 用户名，变长字符串，不允许为空。
  • email: 用户邮箱，变长字符串，不允许为空。
  • birthdate: 用户的生日，日期类型。
  • is_active: 用户是否已经激活，布尔类型，默认值为 true。

以上只是一个简单的实例，用到了一些常见的数据类型包括 INT, VARCHAR, DATE, BOOLEAN，你可以根据实际需要选择不同的数据类型。AUTO_INCREMENT 关键字用于创建一个自增长的列，PRIMARY KEY 用于定义主键。

如果你希望在创建表时指定数据引擎，字符集和排序规则等，可以使用 CHARACTER SET 和 COLLATE 子句：

1
2
3
4
CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

以上代码创建一个使用 utf8mb4 字符集和 utf8mb4_general_ci 排序规则的表。

以下例子中我们将在 RUNOOB 数据库中创建数据表 runoob_tbl：

1
2
3
4
5
6
7
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

实例解析：

  • 如果你不想字段为空可以设置字段的属性为 NOT NULL，如上实例中的 runoob_title 与 runoob_author 字段， 在操作数据库时如果输入该字段的数据为空，就会报错。
  • AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加 1。
  • PRIMARY KEY 关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号 , 分隔。
  • ENGINE 设置存储引擎，CHARSET 设置编码。

删除数据表

MySQL中删除数据表是非常容易操作的，但是你在进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

语法

以下为删除 MySQL 数据表的通用语法：

1
DROP TABLE table_name;     -- 直接删除表，不检查是否存在

或

1
DROP TABLE [IF EXISTS] table_name;  -- 会检查是否存在，如果存在则删除

参数说明：

  • table_name 是要删除的表的名称。
  • IF EXISTS 是一个可选的子句，表示如果表存在才执行删除操作，避免因为表不存在而引发错误。
1
2
3
4
5
-- 删除表，如果存在的话
DROP TABLE IF EXISTS mytable;

-- 直接删除表，不检查是否存在
DROP TABLE mytable;

请替换 mytable 为你要删除的表的名称。

如果你只是想删除表中的所有数据，但保留表的结构，可以使用 TRUNCATE TABLE 语句：

1
TRUNCATE TABLE table_name;

这会清空表中的所有数据，但不会删除表本身。

注意事项：

  • 备份数据：在删除表之前，确保已经备份了数据，如果你需要的话。
  • 外键约束：如果该表与其他表有外键约束，可能需要先删除外键约束，或者确保依赖关系被处理好。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
$dbhost = 'localhost';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功<br />';
$sql = "DROP TABLE runoob_tbl";
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
  die('数据表删除失败: ' . mysqli_error($conn));
}
echo "数据表删除成功\n";
mysqli_close($conn);
?>

插入数据

MySQL 表中使用 INSERT INTO 语句来插入数据。

你可以通过 mysql> 命令提示窗口中向数据表中插入数据，或者通过PHP脚本来插入数据。

语法

以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

1
2
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

参数说明：

  • table_name 是你要插入数据的表的名称。
  • column1, column2, column3, … 是表中的列名。
  • value1, value2, value3, … 是要插入的具体数值。

如果数据是字符型，必须使用单引号 ’ 或者双引号 "，如： ‘value1’, “value1”。

一个简单的实例，插入了一行数据到名为 users 的表中：

1
2
INSERT INTO users (username, email, birthdate, is_active)
VALUES ('test', 'test@runoob.com', '1990-01-01', true);
  • username: 用户名，字符串类型。
  • email: 邮箱地址，字符串类型。
  • birthdate: 用户生日， 日期类型。
  • is_active: 是否已激活，布尔类型。

如果你要插入所有列的数据，可以省略列名：

1
2
INSERT INTO users
VALUES (NULL,'test', 'test@runoob.com', '1990-01-01', true);

这里，NULL 是用于自增长列的占位符，表示系统将为 id 列生成一个唯一的值。

如果你要插入多行数据，可以在 VALUES 子句中指定多组数值：

1
2
3
4
5
INSERT INTO users (username, email, birthdate, is_active)
VALUES
    ('test1', 'test1@runoob.com', '1985-07-10', true),
    ('test2', 'test2@runoob.com', '1988-11-25', false),
    ('test3', 'test3@runoob.com', '1993-05-03', true);

以上代码将在 users 表中插入三行数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
root@host# mysql -u root -p password;
Enter password:*******
mysql> USE RUNOOB;
DATABASE changed
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 ROWS affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 MySQL", "菜鸟教程", NOW());
Query OK, 1 ROWS affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
Query OK, 1 ROWS affected (0.00 sec)
mysql>

查询数据

MySQL 数据库使用 SELECT 语句来查询数据。

你可以通过 mysql> 命令提示窗口中在数据库中查询数据，或者通过 PHP 脚本来查询数据。

语法

以下为在 MySQL 数据库中查询数据通用的 SELECT 语法：

1
2
3
4
5
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];

参数说明：

  • column1, column2, … 是你想要选择的列的名称，如果使用 * 表示选择所有列。
  • table_name 是你要从中查询数据的表的名称。
  • WHERE condition 是一个可选的子句，用于指定过滤条件，只返回符合条件的行。
  • ORDER BY column_name [ASC | DESC] 是一个可选的子句，用于指定结果集的排序顺序，默认是升序（ASC）。
  • LIMIT number 是一个可选的子句，用于限制返回的行数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 选择所有列的所有行
SELECT * FROM users;

-- 选择特定列的所有行
SELECT username, email FROM users;

-- 添加 WHERE 子句，选择满足条件的行
SELECT * FROM users WHERE is_active = TRUE;

-- 添加 ORDER BY 子句，按照某列的升序排序
SELECT * FROM users ORDER BY birthdate;

-- 添加 ORDER BY 子句，按照某列的降序排序
SELECT * FROM users ORDER BY birthdate DESC;

-- 添加 LIMIT 子句，限制返回的行数
SELECT * FROM users LIMIT 10;

SELECT 语句可以是灵活的，我们可以根据实际需求组合和使用这些子句，比如同时使用 WHERE 和 ORDER BY 子句，或者使用 LIMIT 控制返回的行数。

在 WHERE 子句中，你可以使用各种条件运算符（如 =, <, >, <=, >=, !=），逻辑运算符（如 AND, OR, NOT），以及通配符（如 %）等。

以下是一些进阶的 SELECT 语句实例：

1
2
3
4
5
6
7
8
-- 使用 AND 运算符和通配符
SELECT * FROM users WHERE username LIKE 'j%' AND is_active = TRUE;

-- 使用 OR 运算符
SELECT * FROM users WHERE is_active = TRUE OR birthdate < '1990-01-01';

-- 使用 IN 子句
SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');

WHERE 子句

我们知道从 MySQL 表中使用 SELECT 语句来读取数据。

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

WHERE 子句用于在 MySQL 中过滤查询结果，只返回满足特定条件的行。

语法

以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE condition;

参数说明：

  • column1, column2, … 是你要选择的列的名称，如果使用 * 表示选择所有列。
  • table_name 是你要从中查询数据的表的名称。
  • WHERE condition 是用于指定过滤条件的子句。

更多说明：

  • 查询语句中你可以使用一个或者多个表，表之间使用逗号**,** 分割，并使用WHERE语句来设定查询条件。
  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以使用 AND 或者 OR 指定一个或多个条件。
  • WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
  • WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

以下为操作符列表，可用于 WHERE 子句中。

下表中实例假定 A 为 10, B 为 20

操作符 描述 实例
= 等号，检测两个值是否相等，如果相等返回true (A = B) 返回false。
<>, != 不等于，检测两个值是否相等，如果不相等返回true (A != B) 返回 true。
> 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true (A > B) 返回false。
< 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true (A < B) 返回 true。
>= 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true (A >= B) 返回false。
<= 小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true (A <= B) 返回 true。

简单实例

  1. 等于条件：
1
SELECT * FROM users WHERE username = 'test';
  1. 不等于条件：
1
SELECT * FROM users WHERE username != 'runoob';
  1. 大于条件:
1
SELECT * FROM products WHERE price > 50.00;
  1. 小于条件:
1
SELECT * FROM orders WHERE order_date < '2023-01-01';
  1. 大于等于条件:
1
SELECT * FROM employees WHERE salary >= 50000;
  1. 小于等于条件:
1
SELECT * FROM students WHERE age <= 21;
  1. 组合条件（AND、OR）:
1
2
3
SELECT * FROM products WHERE category = 'Electronics' AND price > 100.00;

SELECT * FROM orders WHERE order_date >= '2023-01-01' OR total_amount > 1000.00;
  1. 模糊匹配条件（LIKE）:
1
SELECT * FROM customers WHERE first_name LIKE 'J%';
  1. IN 条件:
1
SELECT * FROM countries WHERE country_code IN ('US', 'CA', 'MX');
  1. NOT 条件:
1
SELECT * FROM products WHERE NOT category = 'Clothing';
  1. BETWEEN 条件:
1
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
  1. IS NULL 条件
1
SELECT * FROM employees WHERE department IS NULL;
  1. IS NOT NULL 条件:
1
SELECT * FROM customers WHERE email IS NOT NULL;

如果我们想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。

使用主键来作为 WHERE 子句的条件查询是非常快速的。

如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

UPDATE 更新

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 UPDATE 命令来操作。

语法

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

1
2
3
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

参数说明：

  • table_name 是你要更新数据的表的名称。
  • column1, column2, … 是你要更新的列的名称。
  • value1, value2, … 是新的值，用于替换旧的值。
  • WHERE condition 是一个可选的子句，用于指定更新的行。如果省略 WHERE 子句，将更新表中的所有行。

更多说明：

  • 你可以同时更新一个或多个字段。
  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以在一个单独表中同时更新数据。

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

实例

以下实例演示了如何使用 UPDATE 语句。

  1. 更新单个列的值：
1
2
3
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;
  1. 更新多个列的值：
1
2
3
UPDATE orders
SET status = 'Shipped', ship_date = '2023-03-01'
WHERE order_id = 1001;
  1. 使用表达式更新值：
1
2
3
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';

以上 SQL 语句将每个属于 ‘Electronics’ 类别的产品的价格都增加了 10%。

  1. 更新符合条件的所有行：
1
2
UPDATE students
SET status = 'Graduated';

以上 SQL 语句将所有学生的状态更新为 ‘Graduated’。

  1. 更新使用子查询的值：
1
2
3
4
5
6
7
UPDATE customers
SET total_purchases = (
    SELECT SUM(amount)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';

以上 SQL 语句通过子查询计算每个 ‘Premium’ 类型客户的总购买金额，并将该值更新到 total_purchases 列中。

注意： 在使用 UPDATE 语句时，请确保你提供了足够的条件来确保只有你想要更新的行被修改。如果不提供 WHERE 子句，将更新表中的所有行，可能导致不可预测的结果。

DELETE 语句

你可以使用 DELETE FROM 命令来删除 MySQL 数据表中的记录。

你可以在 mysql> 命令提示符或 PHP 脚本中执行该命令。

语法

以下是 DELETE 语句从 MySQL 数据表中删除数据的通用语法：

1
2
DELETE FROM table_name
WHERE condition;

参数说明：

  • table_name 是你要删除数据的表的名称。
  • WHERE condition 是一个可选的子句，用于指定删除的行。如果省略 WHERE 子句，将删除表中的所有行。

更多说明：

  • 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
  • 你可以在 WHERE 子句中指定任何条件
  • 您可以在单个表中一次性删除记录。

当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

实例

以下实例演示了如何使用 DELETE 语句。

  1. 删除符合条件的行：
1
2
DELETE FROM students
WHERE graduation_year = 2021;

以上 SQL 语句删除了 students 表中所有 graduation_year 为 2021 的学生的记录。

  1. 删除所有行：
1
DELETE FROM orders;

以上 SQL 语句删除了 orders 表中的所有记录，但表结构保持不变。

  1. 使用子查询删除符合条件的行：
1
2
3
4
5
6
DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date < '2023-01-01'
);

以上 SQL 语句通过子查询删除了 orders 表中在 ‘2023-01-01’ 之前下的订单对应的客户。

注意： 在使用 DELETE 语句时，请确保你提供了足够的条件来确保只有你想要删除的行被删除。如果不提供 WHERE 子句，将删除表中的所有行，可能导致不可预测的结果。

LIKE 子句

我们知道在 MySQL 中使用 SELECT 命令来读取数据， 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。

WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 “runoob_author = ‘RUNOOB.COM’”。

但是有时候我们需要获取 runoob_author 字段含有 “COM” 字符的所有记录，这时我们就需要在 WHERE 子句中使用 LIKE 子句。

LIKE 子句是在 MySQL 中用于在 WHERE 子句中进行模糊匹配的关键字。它通常与通配符一起使用，用于搜索符合某种模式的字符串。

LIKE 子句中使用百分号 **%**字符来表示任意字符，类似于UNIX或正则表达式中的星号 *****。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

语法

以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

参数说明：

  • column1, column2, … 是你要选择的列的名称，如果使用 * 表示选择所有列。
  • table_name 是你要从中查询数据的表的名称。
  • column_name 是你要应用 LIKE 子句的列的名称。
  • pattern 是用于匹配的模式，可以包含通配符。

更多说明：

  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以在 WHERE 子句中使用LIKE子句。
  • 你可以使用LIKE子句代替等号 =。
  • LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
  • 你可以使用 AND 或者 OR 指定一个或多个条件。
  • 你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

实例

以下是一些 LIKE 子句的使用实例。

  1. 百分号通配符 %：

% 通配符表示零个或多个字符。例如，‘a%’ 匹配以字母 ‘a’ 开头的任何字符串。

1
SELECT * FROM customers WHERE last_name LIKE 'S%';

以上 SQL 语句将选择所有姓氏以 ‘S’ 开头的客户。

  1. 下划线通配符 _：

_ 通配符表示一个字符。例如，‘_r%’ 匹配第二个字母为 ‘r’ 的任何字符串。

1
SELECT * FROM products WHERE product_name LIKE '_a%';

以上 SQL 语句将选择产品名称的第二个字符为 ‘a’ 的所有产品。

  1. 组合使用 % 和 _：
1
SELECT * FROM users WHERE username LIKE 'a%o_';

以上 SQL 语句将匹配以字母 ‘a’ 开头，然后是零个或多个字符，接着是 ‘o’，最后是一个任意字符的字符串，如 ‘aaron’、‘apol’。

  1. 不区分大小写的匹配：
1
SELECT * FROM employees WHERE last_name LIKE 'smi%' COLLATE utf8mb4_general_ci;

以上 SQL 语句将选择姓氏以 ‘smi’ 开头的所有员工，不区分大小写。

LIKE 子句提供了强大的模糊搜索能力，可以根据不同的模式和需求进行定制。在使用时，请确保理解通配符的含义，并根据实际情况进行匹配。

UNION 操作符

本教程为大家介绍 MySQL UNION 操作符的语法和实例。

描述

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行。

UNION 操作符必须由两个或多个 SELECT 语句组成，每个 SELECT 语句的列数和对应位置的数据类型必须相同。

语法

MySQL UNION 操作符语法格式：

1
2
3
4
5
6
7
8
SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition2
[ORDER BY column1, column2, ...];

参数说明：

  • column1, column2, … 是你要选择的列的名称，如果使用 * 表示选择所有列。
  • table1, table2, … 是你要从中查询数据的表的名称。
  • condition1, condition2, … 是每个 SELECT 语句的过滤条件，是可选的。
  • ORDER BY 子句是一个可选的子句，用于指定合并后的结果集的排序顺序。

实例

  1. 基本的 UNION 操作：
1
2
3
4
SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;

以上 SQL 语句将选择客户表和供应商表中所有城市的唯一值，并按城市名称升序排序。

  1. 使用过滤条件的 UNION：
1
2
3
4
SELECT product_name FROM products WHERE category = 'Electronics'
UNION
SELECT product_name FROM products WHERE category = 'Clothing'
ORDER BY product_name;

以上 SQL 语句将选择电子产品和服装类别的产品名称，并按产品名称升序排序。

  1. UNION 操作中的列数和数据类型必须相同：
1
2
3
4
SELECT first_name, last_name FROM employees
UNION
SELECT department_name, NULL FROM departments
ORDER BY first_name;

以上 SQL 语句中，departments 表中的 department_name 列被映射到了 employees 表中的 last_name 列，但是列数和数据类型必须相同。

  1. 使用 UNION ALL 不去除重复行：
1
2
3
4
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
ORDER BY city;

以上 SQL 语句使用 UNION ALL 将客户表和供应商表中的所有城市合并在一起，不去除重复行。

UNION 操作符在合并结果集时会去除重复行，而 UNION ALL 不会去除重复行，因此 UNION ALL 的性能可能更好，但如果你确实希望去除重复行，可以使用 UNION。

示例

演示数据库

在本教程中，我们将使用 RUNOOB 样本数据库。

下面是选自 “Websites” 表的数据：

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

下面是 “apps” APP 的数据：

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | http://im.qq.com/       | CN      |
|  2 | 微博 APP | http://weibo.com/       | CN      |
|  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)
UNION 实例
  • 下面的 SQL 语句从 “Websites” 和 “apps” 表中选取所有不同的country（只有不同的值）：
1
2
3
4
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

执行以上 SQL 输出结果如下：

**注释：**UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

UNION ALL 实例

下面的 SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的country（也有重复的值）：

1
2
3
4
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

执行以上 SQL 输出结果如下：

带有 WHERE 的 SQL UNION ALL

下面的 SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的中国(CN)的数据（也有重复的值）：

1
2
3
4
5
6
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

执行以上 SQL 输出结果如下：

ORDER BY(排序) 语句

我们知道从 MySQL 表中使用 SELECT 语句来读取数据。

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

MySQL ORDER BY(排序) 语句可以按照一个或多个列的值进行升序（ASC）或降序（DESC）排序。

语法

以下是 SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

1
2
3
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

参数说明：

  • column1, column2, … 是你要选择的列的名称，如果使用 * 表示选择所有列。
  • table_name 是你要从中查询数据的表的名称。
  • ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ... 是用于指定排序顺序的子句。ASC 表示升序（默认），DESC 表示降序。

更多说明：

  • 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
  • 你可以设定多个字段来排序。
  • 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
  • 你可以添加 WHERE…LIKE 子句来设置条件。

实例

以下是一些 ORDER BY 子句的使用实例。

  1. 单列排序：
1
2
SELECT * FROM products
ORDER BY product_name ASC;

以上 SQL 语句将选择产品表 products 中的所有产品，并按产品名称升序 ASC 排序。

  1. 多列排序：
1
2
SELECT * FROM employees
ORDER BY department_id ASC, hire_date DESC;

以上 SQL 语句将选择员工表 employees 中的所有员工，并先按部门 ID 升序 ASC 排序，然后在相同部门中按雇佣日期降序 DESC 排序。

  1. 使用数字表示列的位置：
1
2
3
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 1 ASC;

以上 SQL 语句将选择员工表 employees 中的名字和工资列，并按第三列（salary）降序 DESC 排序，然后按第一列（first_name）升序 ASC 排序。

  1. 使用表达式排序：
1
2
3
SELECT product_name, price * discount_rate AS discounted_price
FROM products
ORDER BY discounted_price DESC;

以上 SQL 语句将选择产品表 products 中的产品名称和根据折扣率计算的折扣后价格，并按折扣后价格降序 DESC 排序。

5.从 MySQL 8.0.16 版本开始，可以使用 NULLS FIRST 或 NULLS LAST 处理 NULL 值：

1
2
3
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS LAST;

以上 SQL 语句将选择产品表 products 中的产品名称和价格，并按价格降序 DESC 排序，将 NULL 值排在最后。

相反，如果你想让 NULL 值排在前面，可以这样写：

1
2
3
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS FIRST;

ORDER BY 子句是一个强大的工具，可以根据不同的业务需求对查询结果进行排序。在实际应用中，注意选择适当的列和排序顺序，以获得符合期望的排序效果。

MySQL GROUP BY 语句

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具，尤其在处理大量数据时，它能够提供有用的汇总信息。

GROUP BY 语法

1
2
3
4
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;
  • column1：指定分组的列。
  • aggregate_function(column2)：对分组后的每个组执行的聚合函数。
  • table_name：要查询的表名。
  • condition：可选，用于筛选结果的条件。

假设有一个名为 orders 的表，包含以下列：order_id、customer_id、order_date 和 order_amount。

我们想要按照 customer_id 进行分组，并计算每个客户的订单总金额，SQL 语句如下：

实例

1
2
3
SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;

以上实例中，我们使用 GROUP BY customer_id 将结果按 customer_id 列分组，然后使用 SUM(order_amount) 计算每个组中 order_amount 列的总和。

AS total_amount 是为了给计算结果取一个别名，使查询结果更易读。

注意事项：

  • GROUP BY 子句通常与聚合函数一起使用，因为分组后需要对每个组进行聚合操作。
  • SELECT 子句中的列通常要么是分组列，要么是聚合函数的参数。
  • 可以使用多个列进行分组，只需在 GROUP BY 子句中用逗号分隔列名即可。

实例

1
2
3
4
SELECT column1, column2, aggregate_function(column3)
FROM TABLE_NAME
WHERE condition
GROUP BY column1, column2;

连接的使用

在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

你可以在 SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

  • INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
  • **LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。
  • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

本章节使用的数据库结构及数据下载：runoob-mysql-join-test.sql。

INNER JOIN

INNER JOIN 返回两个表中满足连接条件的匹配行，以下是 INNER JOIN 语句的基本语法：

1
2
3
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

参数说明：

  • column1, column2, … 是你要选择的列的名称，如果使用 * 表示选择所有列。
  • table1, table2 是要连接的两个表的名称。
  • table1.column_name = table2.column_name 是连接条件，指定了两个表中用于匹配的列。

简单的 INNER JOIN：

1
2
3
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

以上 SQL 语句将选择 orders 表和 customers 表中满足连接条件的订单 ID 和客户名称。

  1. 使用表别名：
1
2
3
SELECT o.order_id, c.customer_name
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id;

以上 SQL 语句使用表别名 o 和 c 作为 orders 和 customers 表的别名。

  1. 多表 INNER JOIN：
1
2
3
4
5
SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;

以上 SQL 语句涉及了 orders、customers、order_items 和 products 四个表的连接。它选择了订单 ID、客户名称和产品名称，连接了这些表的关联列。

  1. 使用 WHERE 子句进行过滤：
1
2
3
4
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date >= '2023-01-01';

以上 SQL 语句在 INNER JOIN 后使用 WHERE 子句，过滤了订单日期在 ‘2023-01-01’ 及以后的订单。

LEFT JOIN

LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值，以下是 LEFT JOIN 语句的基本语法：

1
2
3
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
  1. 简单的 LEFT JOIN：
1
2
3
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

以上 SQL 语句将选择客户表中的客户 ID 和客户名称，并包括左表 customers 中的所有行，以及匹配的订单 ID（如果有的话）。

  1. 使用表别名：
1
2
3
SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;

以上 SQL 语句使用表别名 c 和 o 分别代替 customers 和 orders 表的名称。

  1. 多表 LEFT JOIN：
1
2
3
4
5
SELECT customers.customer_id, customers.customer_name, orders.order_id, products.product_name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN order_items ON orders.order_id = order_items.order_id
LEFT JOIN products ON order_items.product_id = products.product_id;

以上 SQL 语句连接了 customers、orders、order_items 和 products 四个表，并选择了客户 ID、客户名称、订单 ID 和产品名称。左连接保证了即使在 order_items 或 products 中没有匹配的行，仍然会返回客户和订单的信息。

  1. 使用 WHERE 子句进行过滤：
1
2
3
4
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_date >= '2023-01-01' OR orders.order_id IS NULL;

以上 SQL 语句在 LEFT JOIN 后使用 WHERE 子句，过滤了订单日期在 ‘2023-01-01’ 及以后的订单，以及没有匹配订单的客户。

LEFT JOIN 是一种常用的连接类型，尤其在需要返回左表中所有行的情况下。当右表中没有匹配的行时，相关列将显示为 NULL。在使用 LEFT JOIN 时，请确保理解连接条件并根据需求过滤结果。

RIGHT JOIN

RIGHT JOIN 返回右表的所有行，并包括左表中匹配的行，如果左表中没有匹配的行，将返回 NULL 值，以下是 RIGHT JOIN 语句的基本语法：：

1
2
3
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

以下是一个简单的 RIGHT JOIN 实例：

1
2
3
SELECT customers.customer_id, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

以上 SQL 语句将选择右表 orders 中的所有订单 ID，并包括左表 customers 中匹配的客户 ID。如果在 customers 表中没有匹配的客户 ID，相关列将显示为 NULL。

在开发过程中中，RIGHT JOIN 并不经常使用，因为它可以用 LEFT JOIN 和表的顺序交换来实现相同的效果。例如，上面的查询可以通过使用 LEFT JOIN 改写为：

1
2
3
SELECT customers.customer_id, orders.order_id
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

以上 SQL 语句返回相同的结果，因为 LEFT JOIN 与 RIGHT JOIN 是对称的。在实际使用中，你可以根据个人偏好或组织规范选择使用哪种形式。

NULL 值处理

我们已经知道 MySQL 使用 SELECT 命令及 WHERE 子句来读取数据表中的数据，但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

在 MySQL 中，NULL 用于表示缺失的或未知的数据，处理 NULL 值需要特别小心，因为在数据库中它可能会导致不同于预期的结果。

为了处理这种情况，MySQL提供了三大运算符:

  • IS NULL: 当列的值是 NULL,此运算符返回 true。
  • IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
  • <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

注意：

1
select * , columnName1+ifnull(columnName2,0) from tableName;

columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。

MySQL 中处理 NULL 值的常见注意事项和技巧

  1. 检查是否为 NULL：

要检查某列是否为 NULL，可以使用 IS NULL 或 IS NOT NULL 条件。

1
2
SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE department_id IS NOT NULL;
  1. 使用 COALESCE 函数处理 NULL：

COALESCE 函数可以用于替换为 NULL 的值，它接受多个参数，返回参数列表中的第一个非 NULL 值：

1
2
SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity
FROM products;

以上 SQL 语句中，如果 stock_quantity 列为 NULL，则 COALESCE 将返回 0。

  1. 使用 IFNULL 函数处理 NULL：

IFNULL 函数是 COALESCE 的 MySQL 特定版本，它接受两个参数，如果第一个参数为 NULL，则返回第二个参数。

1
2
SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity
FROM products;
  1. NULL 排序：

在使用 ORDER BY 子句进行排序时，NULL 值默认会被放在排序的最后。如果希望将 NULL 值放在最前面，可以使用 ORDER BY column_name ASC NULLS FIRST，反之使用 ORDER BY column_name DESC NULLS LAST。

1
2
3
SELECT product_name, price
FROM products
ORDER BY price ASC NULLS FIRST;
  1. 使用 <=> 操作符进行 NULL 比较：

<=> 操作符是 MySQL 中用于比较两个表达式是否相等的特殊操作符，对于 NULL 值的比较也会返回 TRUE。它可以用于处理 NULL 值的等值比较。

1
SELECT * FROM employees WHERE commission <=> NULL;
  1. 注意聚合函数对 NULL 的处理：

在使用聚合函数（如 COUNT, SUM, AVG）时，它们会忽略 NULL 值，因此可能会得到不同于预期的结果。如果希望将 NULL 视为 0，可以使用 COALESCE 或 IFNULL。

1
SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;

这样即使 salary 为 NULL，聚合函数也会将其视为 0。

处理 NULL 值时，要特别小心确保查询和操作的语义符合预期。在设计表结构时，也需要考虑 NULL 值的使用场景和合理性。

正则表达式

在前面的章节我们已经了解到 MySQL 可以通过 LIKE …% 来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配， MySQL 中使用 REGEXP 和 RLIKE操作符来进行正则表达式匹配。

如果您了解 PHP 或 Perl，那么操作起来就非常简单，因为 MySQL 的正则表达式匹配与这些脚本的类似。

下表中的正则模式可应用于 REGEXP 操作符中。

模式 描述
^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
. 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用像 ‘[.\n]’ 的模式。
[…] 字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…] 负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如，‘z|food’ 能匹配 “z” 或 “food”。‘(z|f)ood’ 则匹配 “zood” 或 “food”。
* 匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+ 匹配前面的子表达式一次或多次。例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n} n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

正则表达式匹配的字符类

  • .：匹配任意单个字符。
  • ^：匹配字符串的开始。
  • $：匹配字符串的结束。
  • *：匹配零个或多个前面的元素。
  • +：匹配一个或多个前面的元素。
  • ?：匹配零个或一个前面的元素。
  • [abc]：匹配字符集中的任意一个字符。
  • [^abc]：匹配除了字符集中的任意一个字符以外的字符。
  • [a-z]：匹配范围内的任意一个小写字母。
  • [0-9]：匹配一个数字字符。
  • \w：匹配一个字母数字字符（包括下划线）。
  • \s：匹配一个空白字符。

使用 REGEXP 进行模式匹配

REGEXP 是用于进行正则表达式匹配的运算符。

REGEXP 用于检查一个字符串是否匹配指定的正则表达式模式，以下是 REGEXP 运算符的基本语法：

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE column_name REGEXP 'pattern';

参数说明：

  • column1, column2, … 是你要选择的列的名称，如果使用 * 表示选择所有列。
  • table_name 是你要从中查询数据的表的名称。
  • column_name 是你要进行正则表达式匹配的列的名称。
  • 'pattern' 是一个正则表达式模式。

查找 name 字段中以 ‘st’ 为开头的所有数据：

1
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找 name 字段中以 ‘ok’ 为结尾的所有数据：

1
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找 name 字段中包含 ‘mar’ 字符串的所有数据：

1
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找 name 字段中以元音字符开头或以 ‘ok’ 字符串结尾的所有数据：

1
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

选择订单表中描述中包含 “item” 后跟一个或多个数字的记录。

1
SELECT * FROM orders WHERE order_description REGEXP 'item[0-9]+';

使用 BINARY 关键字，使得匹配区分大小写：

1
SELECT * FROM products WHERE product_name REGEXP BINARY 'apple';

使用 OR 进行多个匹配条件，以下将选择姓氏为 “Smith” 或 “Johnson” 的员工记录：

1
SELECT * FROM employees WHERE last_name REGEXP 'Smith|Johnson';

使用 RLIKE 进行模式匹配

RLIKE 是 MySQL 中用于进行正则表达式匹配的运算符，与 REGEXP 是一样的，RLIKE 和 REGEXP 可以互换使用，没有区别。

以下是使用 RLIKE 进行正则表达式匹配的基本语法：

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE column_name RLIKE 'pattern';

参数说明：

  • column1, column2, … 是你要选择的列的名称，如果使用 * 表示选择所有列。
  • table_name 是你要从中查询数据的表的名称。
  • column_name 是你要进行正则表达式匹配的列的名称。
  • 'pattern' 是一个正则表达式模式。
1
SELECT * FROM products WHERE product_name RLIKE '^[0-9]';

以上 SQL 语句选择产品名称以数字开头的所有产品。

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中，事务是一组SQL语句的执行，它们被视为一个单独的工作单元。

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert、update、delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句

  • BEGIN 或 START TRANSACTION 显式地开启一个事务；
  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
  • ROLLBACK TO identifier 把事务回滚到标记点；
  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT 来实现

  • BEGIN 或 START TRANSACTION：开用于开始一个事务。
  • ROLLBACK 事务回滚，取消之前的更改。
  • COMMIT：事务确认，提交事务，使更改永久生效。

2、直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

BEGIN 或 START TRANSACTION – 用于开始一个事务：

1
BEGIN; -- 或者使用 START TRANSACTION;

COMMIT – 用于提交事务，将所有的修改永久保存到数据库：

1
COMMIT;

ROLLBACK – 用于回滚事务，撤销自上次提交以来所做的所有更改：

1
ROLLBACK;

SAVEPOINT – 用于在事务中设置保存点，以便稍后能够回滚到该点：

1
SAVEPOINT savepoint_name;

ROLLBACK TO SAVEPOINT – 用于回滚到之前设置的保存点：

1
ROLLBACK TO SAVEPOINT savepoint_name;

下面是一个简单的 MySQL 事务的例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 开始事务
START TRANSACTION;

-- 执行一些SQL语句
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 判断是否要提交还是回滚
IF (条件) THEN
    COMMIT; -- 提交事务
ELSE
    ROLLBACK; -- 回滚事务
END IF;

事务测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql>

ALTER 命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到 MySQL ALTER 命令。

MySQL 的 ALTER 命令用于修改数据库、表和索引等对象的结构。

ALTER 命令允许你添加、修改或删除数据库对象，并且可以用于更改表的列定义、添加约束、创建和删除索引等操作。

ALTER 命令非常强大，可以在数据库结构发生变化时进行灵活的修改和调整。

以下是 ALTER 命令的常见用法和实例：

  1. 添加列
1
2
ALTER TABLE table_name
ADD COLUMN new_column_name datatype;

以下 SQL 语句在 employees 表中添加了一个名为 birth_date 的日期列：

1
2
ALTER TABLE employees
ADD COLUMN birth_date DATE;
  1. 修改列的数据类型
1
2
ALTER TABLE TABLE_NAME
MODIFY COLUMN column_name new_datatype;

以下 SQL 语句将 employees 表中的 salary 列的数据类型修改为 DECIMAL(10,2)：

1
2
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10,2);
  1. 修改列名
1
2
ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;

以下 SQL 语句将 employees 表中的某个列的名字由 old_column_name 修改为 new_column_name，并且可以同时修改数据类型：

1
2
ALTER TABLE employees
CHANGE COLUMN old_column_name new_column_name VARCHAR(255);
  1. 删除列
1
2
ALTER TABLE table_name
DROP COLUMN column_name;

以下 SQL 语句将 employees 表中的 birth_date 列删除：

1
2
ALTER TABLE employees
DROP COLUMN birth_date;
  1. 添加 PRIMARY KEY
1
2
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

以下 SQL 语句在 employees 表中添加了一个主键：

1
2
ALTER TABLE employees
ADD PRIMARY KEY (employee_id);
  1. 添加 FOREIGN KEY
1
2
3
4
ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES parent_table (column_name);

以下 SQL 语句在 orders 表中添加了一个外键，关联到 customers 表的 customer_id 列：

1
2
3
4
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);
  1. 修改表名
1
2
ALTER TABLE old_table_name
RENAME TO new_table_name;

以下 SQL 语句将表名由 employees 修改为 staff：

1
2
ALTER TABLE employees
RENAME TO staff;

**注意：**在使用 ALTER 命令时要格外小心，因为一些操作可能需要重建表或索引，这可能会影响数据库的性能和运行时间。

在进行重要的结构修改时，建议先备份数据，并在生产环境中谨慎操作。

索引

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。

打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引：

  • 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
  • 组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引虽然能够提高查询性能，但也需要注意以下几点：

  • 索引需要占用额外的存储空间。
  • 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。
  • 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。

普通索引

索引能够显著提高查询的速度，尤其是在大型表中进行搜索时。通过使用索引，MySQL 可以直接定位到满足查询条件的数据行，而无需逐行扫描整个表。

创建索引

使用 CREATE INDEX 语句可以创建普通索引。

普通索引是最常见的索引类型，用于加速对表中数据的查询。

CREATE INDEX 的语法：

1
2
CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE INDEX: 用于创建普通索引的关键字。
  • index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
  • table_name: 指定要在哪个表上创建索引。
  • (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
  • ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下实例假设我们有一个名为 students 的表，包含 id、name 和 age 列，我们将在 name 列上创建一个普通索引。

1
CREATE INDEX idx_name ON students (name);

上述语句将在 students 表的 name 列上创建一个名为 idx_name 的普通索引，这将有助于提高通过姓名进行搜索的查询性能。

需要注意的是，如果表中的数据量较大，索引的创建可能会花费一些时间，但一旦创建完成，查询性能将会显著提高。

修改表结构(添加索引)

我们可以使用 ALTER TABLE 命令可以在已有的表中创建索引。

ALTER TABLE 允许你修改表的结构，包括添加、修改或删除索引。

ALTER TABLE 创建索引的语法：

1
2
ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • ALTER TABLE: 用于修改表结构的关键字。
  • table_name: 指定要修改的表的名称。
  • ADD INDEX: 添加索引的子句。ADD INDEX用于创建普通索引。
  • index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
  • (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
  • ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

下面是一个实例，我们将在已存在的名为 employees 的表上创建一个普通索引:

1
2
ALTER TABLE employees
ADD INDEX idx_age (age);

上述语句将在 employees 表的 age 列上创建一个名为 idx_age 的普通索引。

创建表的时候直接指定

我们可以在创建表的时候，你可以在 CREATE TABLE 语句中直接指定索引，以创建表和索引的组合。

1
2
3
4
5
6
CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
  • CREATE TABLE: 用于创建新表的关键字。
  • table_name: 指定要创建的表的名称。
  • (column1 data_type, column2 data_type, ...): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
  • INDEX: 用于创建普通索引的关键字。
  • index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
  • (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
  • ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

下面是一个实例，我们要创建一个名为 students 的表，并在 age 列上创建一个普通索引。

1
2
3
4
5
6
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX idx_age (age)
);

在上述实例中，我们在 students 表的 age 列上创建了一个名为 idx_age 的普通索引。

删除索引的语法

我们可以使用 DROP INDEX 语句来删除索引。

DROP INDEX 的语法：

1
DROP INDEX index_name ON table_name;
  • DROP INDEX: 用于删除索引的关键字。
  • index_name: 指定要删除的索引的名称。
  • ON table_name: 指定要在哪个表上删除索引。

使用 ALTER TABLE 语句删除索引的语法如下：

1
2
ALTER TABLE table_name
DROP INDEX index_name;
  • ALTER TABLE: 用于修改表结构的关键字。
  • table_name: 指定要修改的表的名称。
  • DROP INDEX: 用于删除索引的子句。
  • index_name: 指定要删除的索引的名称。

以下实例假设我们有一个名为 employees 的表，并在 age 列上有一个名为 idx_age 的索引，现在我们要删除这个索引：

1
DROP INDEX idx_age ON employees;

或使用 ALTER TABLE 语句：

1
2
ALTER TABLE employees
DROP INDEX idx_age;

这两个命令都会从 employees 表中删除名为 idx_age 的索引。

如果该索引不存在，执行命令时会产生错误。因此，在删除索引之前最好确认该索引是否存在，或者使用错误处理机制来处理可能的错误情况。

唯一索引

在 MySQL 中，你可以使用 CREATE UNIQUE INDEX 语句来创建唯一索引。

唯一索引确保索引中的值是唯一的，不允许有重复值。

创建索引
1
2
CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE UNIQUE INDEX: 用于创建唯一索引的关键字组合。
  • index_name: 指定要创建的唯一索引的名称。索引名称在表中必须是唯一的。
  • table_name: 指定要在哪个表上创建唯一索引。
  • (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
  • ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下是一个创建唯一索引的实例： 假设我们有一个名为 employees的 表，包含 id 和 email 列，现在我们想在email列上创建一个唯一索引，以确保每个员工的电子邮件地址都是唯一的。

1
CREATE UNIQUE INDEX idx_email ON employees (email);

以上实例将在 employees 表的 email 列上创建一个名为 idx_email 的唯一索引。

修改表结构添加索引

我们可以使用 ALTER TABLE 命令来创建唯一索引。

ALTER TABLE命令允许你修改已经存在的表结构，包括添加新的索引。

1
2
ALTER table table_name 
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);
  • ALTER TABLE: 用于修改表结构的关键字。
  • table_name: 指定要修改的表的名称。
  • ADD CONSTRAINT: 这是用于添加约束（包括唯一索引）的关键字。
  • unique_constraint_name : 指定要创建的唯一索引的名称，约束名称在表中必须是唯一的。
  • UNIQUE (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。

以下是一个使用 ALTER TABLE 命令创建唯一索引的实例：假设我们有一个名为 employees 的表，包含 id 和 email 列，现在我们想在 email 列上创建一个唯一索引，以确保每个员工的电子邮件地址都是唯一的。

1
2
ALTER TABLE employees
ADD CONSTRAINT idx_email UNIQUE (email);

以上实例将在 employees 表的 email 列上创建一个名为 idx_email 的唯一索引。

请注意，如果表中已经有重复的 email 值，那么添加唯一索引将会失败。在创建唯一索引之前，你可能需要确保表中的 email 列没有重复的值。

创建表的时候直接指定

我们也可以在创建表的同时，你可以在 CREATE TABLE 语句中使用 UNIQUE 关键字来创建唯一索引。

这将在表创建时同时定义唯一索引约束。

CREATE TABLE 语句中创建唯一索引的语法：

1
2
3
4
5
6
CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
  • CREATE TABLE: 用于创建新表的关键字。
  • table_name: 指定要创建的表的名称。
  • (column1 data_type, column2 data_type, ...): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
  • CONSTRAINT: 用于添加约束的关键字。
  • index_name: 指定要创建的唯一索引的名称。约束名称在表中必须是唯一的。
  • UNIQUE (column1, column2, ...): 指定要索引的表列名。
  • ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下是一个在创建表时创建唯一索引的实例：假设我们要创建一个名为 employees 的表，其中包含 id、name 和 email 列，我们希望 email 列的值是唯一的，因此我们要在创建表时定义唯一索引。

1
2
3
4
5
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100) UNIQUE
);

在这个例子中，email 列被定义为唯一索引，因为在它的后面加上了 UNIQUE 关键字。

请注意，使用 UNIQUE 关键字后，索引名称将自动生成，你也可以根据需要指定索引名称。

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

  • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

    该语句添加一个主键，主键列中的值必须唯一，主键的列的列表，可以是一个或多个列，不能包含 NULL 值。 。

  • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

  • ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

  • **ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)😗*该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。

1
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

1
mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键作用于列上（可以一个列或多个列联合主键），添加主键时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

1
2
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

1
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定 PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。

可以通过添加 \G 来格式化输出信息。

SHOW INDEX 语句：:

1
2
mysql> SHOW INDEX FROM table_name\G
........
  • SHOW INDEX: 用于显示索引信息的关键字。
  • FROM table_name: 指定要查看索引信息的表的名称。
  • \G: 格式化输出信息。

执行上述命令后，将会显示指定表中所有索引的详细信息，包括索引名称（Key_name）、索引列（Column_name）、是否是唯一索引（Non_unique）、排序方式（Collation）、索引的基数（Cardinality）等。

临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。

临时表只在当前连接可见，当关闭连接时，MySQL 会自动删除表并释放所有空间。

在 MySQL 中，临时表是一种在当前会话中存在的表，它在会话结束时会自动被销毁。

MySQL 临时表只在当前连接可见，如果你使用PHP脚本来创建 MySQL 临时表，那每当 PHP 脚本执行完成后，该临时表也会自动销毁。

如果你使用了其他 MySQL 客户端程序连接 MySQL 数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

创建临时表

1
2
3
4
5
CREATE TEMPORARY TABLE temp_table_name (
  column1 datatype,
  column2 datatype,
  ...
);

或者简写为：

1
2
3
4
CREATE TEMPORARY TABLE temp_table_name AS
SELECT column1, column2, ...
FROM source_table
WHERE condition;

插入数据到临时表

1
2
INSERT INTO temp_table_name (column1, column2, ...)
VALUES (value1, value2, ...);

查询临时表

1
SELECT * FROM temp_table_name;

修改临时表

临时表的修改操作与普通表类似，可以使用 ALTER TABLE 命令。

1
2
ALTER TABLE temp_table_name
ADD COLUMN new_column datatype;

删除临时表

临时表在会话结束时会自动被销毁，但你也可以使用 DROP TABLE 明确删除它。

1
DROP TEMPORARY TABLE IF EXISTS temp_table_name;

实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建临时表
CREATE TEMPORARY TABLE temp_orders AS
SELECT * FROM orders WHERE order_date >= '2023-01-01';

-- 查询临时表
SELECT * FROM temp_orders;

-- 插入数据到临时表
INSERT INTO temp_orders (order_id, customer_id, order_date)
VALUES (1001, 1, '2023-01-05');

-- 查询临时表
SELECT * FROM temp_orders;

-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_orders;

复制表

如果我们需要完全的复制 MySQL 的数据表，包括表的结构，索引，默认值等。

如果仅仅使用 CREATE TABLE … SELECT 命令，是无法实现的。

本章节将为大家介绍如何完整的复制 MySQL 数据表，步骤如下：

  • 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
  • 复制以下命令显示的 SQL 语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
  • 如果你想复制表的内容，你就可以使用 INSERT INTO … SELECT 语句来实现。

实例

尝试以下实例来复制表 runoob_tbl 。

步骤一：

获取数据表的完整结构。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************
       Table: runoob_tbl
Create Table: CREATE TABLE `runoob_tbl` (
  `runoob_id` int(11) NOT NULL auto_increment,
  `runoob_title` varchar(100) NOT NULL default '',
  `runoob_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`runoob_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB 
1 row in set (0.00 sec)

ERROR:
No query specified

步骤二：

修改 SQL 语句的数据表名，并执行 SQL 语句。

1
2
3
4
5
6
7
8
9
mysql> CREATE TABLE `clone_tbl` (
  -> `runoob_id` int(11) NOT NULL auto_increment,
  -> `runoob_title` varchar(100) NOT NULL default '',
  -> `runoob_author` varchar(40) NOT NULL default '',
  -> `submission_date` date default NULL,
  -> PRIMARY KEY  (`runoob_id`),
  -> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)

步骤三：

执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO… SELECT 语句来实现。

1
2
3
4
5
6
7
8
9
mysql> INSERT INTO clone_tbl (runoob_id,
    ->                        runoob_title,
    ->                        runoob_author,
    ->                        submission_date)
    -> SELECT runoob_id,runoob_title,
    ->        runoob_author,submission_date
    -> FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

执行以上步骤后，会完整的复制表的内容，包括表结构及表数据。

元数据

MySQL 元数据是关于数据库和其对象（如表、列、索引等）的信息。

元数据存储在系统表中，这些表位于 MySQL 数据库的 information_schema 数据库中，通过查询这些系统表，你可以获取关于数据库结构、对象和其他相关信息的详细信息。

你可能想知道MySQL以下三种信息：

  • 查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
  • 数据库和数据表的信息： 包含了数据库及数据表的结构信息。
  • MySQL 服务器信息： 包含了数据库服务器的当前状态，版本号等。

在 MySQL 的命令提示符中，我们可以很容易的获取以上服务器信息，但如果使用 Perl 或 PHP 等脚本语言，你就需要调用特定的接口函数来获取，接下来我们会详细介绍。

以下是一些常用的 MySQL 元数据查询：

查看所有数据库：

1
SHOW DATABASES;

选择数据库：

1
USE database_name;

查看数据库中的所有表：

1
SHOW TABLES;

查看表的结构：

1
DESC table_name;

查看表的索引：

1
SHOW INDEX FROM table_name;

查看表的创建语句：

1
SHOW CREATE TABLE table_name;

查看表的行数：

1
SELECT COUNT(*) FROM table_name;

查看列的信息：

1
2
3
4
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

以上SQL 语句中的 ‘your_database_name’ 和 ‘your_table_name’ 分别是你的数据库名和表名。

查看外键信息：

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    TABLE_SCHEMA = 'your_database_name'
    AND TABLE_NAME = 'your_table_name'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

请替换上述 SQL 语句中的 ‘your_database_name’ 和 ‘your_table_name’ 为实际的数据库名和表名。

information_schema 数据库

information_schema 是 MySQL 数据库中的一个系统数据库，它包含有关数据库服务器的元数据信息，这些信息以表的形式存储在 information_schema 数据库中。

  • SCHEMATA 表

存储有关数据库的信息，如数据库名、字符集、排序规则等。

1
SELECT * FROM information_schema.SCHEMATA;
  • TABLES 表

包含有关数据库中所有表的信息，如表名、数据库名、引擎、行数等。

1
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';
  • COLUMNS 表

包含有关表中列的信息，如列名、数据类型、是否允许 NULL 等。

1
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
  • STATISTICS 表

提供有关表索引的统计信息，如索引名、列名、唯一性等。

1
SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
  • KEY_COLUMN_USAGE 表

包含有关表中外键的信息，如外键名、列名、关联表等。

1
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
  • REFERENTIAL_CONSTRAINTS 表

存储有关外键约束的信息，如约束名、关联表等。

1
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

这些表提供了丰富的元数据信息，可以用于查询数据库结构、表信息、列信息、索引信息等。

请注意，你需要将查询中的 ‘your_database_name’ 和 ‘your_table_name’ 替换为实际的数据库名和表名。

获取查询语句影响的记录数

PERL 实例

在 DBI 脚本中， 语句影响的记录数通过函数 do( ) 或 execute( )返回：

1
2
3
4
5
6
7
8
9
10
11
# 方法 1
# 使用do( ) 执行  $query 
my $count = $dbh->do ($query);
# 如果发生错误会输出 0
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);

# 方法 2
# 使用prepare( ) 及 execute( ) 执行  $query 
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);

序列使用（AUTO_INCREMENT）

在 MySQL 中，序列是一种自增生成数字序列的对象，是一组整数 1、2、3、…，由于一张数据表只能有一个字段自增主键。

尽管 MySQL 本身并没有内建的序列类型，但可以使用 AUTO_INCREMENT 属性来模拟序列的行为，通常 AUTO_INCREMENT 属性用于指定表中某一列的自增性。

一个使用 AUTO_INCREMENT 创建表的例子：

1
2
3
4
CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

以上例子中，id 列被定义为 INT AUTO_INCREMENT，这表示每次插入一行数据时，id 列的值会自动增加。主键约束保证了 id 列的唯一性。

当你插入一行数据时，可以不指定 id 列的值，数据库会自动为其分配一个唯一的、自增的值：

1
INSERT INTO example_table (name) VALUES ('John');

你也可以使用 LAST_INSERT_ID() 函数来获取刚刚插入的行的自增值：

1
SELECT LAST_INSERT_ID();

如果你需要获取表的当前自增值，可以使用以下语句：

1
SHOW TABLE STATUS LIKE 'example_table';

在结果集中，Auto_increment 列的值即为当前表的自增值。

请注意，使用 AUTO_INCREMENT 属性的列只能是整数类型（通常是 INT 或 BIGINT）。此外，如果你删除表中的某一行，其自增值不会被重新使用，而是会继续递增。如果你希望手动设置自增值，可以使用 SET 语句，但这不是一种常规的做法，因为可能引起唯一性冲突。

  • 使用 AUTO_INCREMENT

MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义序列。

实例

以下实例中创建了数据表 insect， insect 表中 id 无需指定值可实现自动增长。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)
  • 获取 AUTO_INCREMENT 值

在 MySQL 的客户端中你可以使用 SQL 中的 LAST_INSERT_ID() 函数来获取最后的插入表中的自增列的值。

在 PHP 或 PERL 脚本中也提供了相应的函数来获取最后的插入表中的自增列的值。

处理重复数据

有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。

本章节我们将为大家介绍如何防止数据表出现重复数据及如何删除数据表中的重复数据。

  • 防止表中出现重复数据

你可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。

让我们尝试一个实例：下表中无索引及主键，所以该表允许出现多条重复记录。

1
2
3
4
5
6
CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

如果你想设置表中字段 first_name，last_name 数据不能重复，你可以设置双主键模式来设置数据的唯一性， 如果你设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL。如下所示：

1
2
3
4
5
6
7
CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：

1
2
3
4
5
6
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示：

1
2
3
4
5
6
7
CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);
  • 统计重复数据

以下我们将统计表中 first_name 和 last_name的重复记录数：

1
2
3
4
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：

  • 确定哪一列包含的值可能会重复。
  • 在列选择列表使用COUNT(*)列出的那些列。
  • 在GROUP BY子句中列出的列。
  • HAVING子句设置重复数大于1。
  • 过滤重复数据

如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

1
2
mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;

你也可以使用 GROUP BY 来读取数据表中不重复的数据：

1
2
3
mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);
  • 删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

1
2
3
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

当然你也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

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

导出数据

mysqldump 命令可以用于备份和还原 MySQL 数据库。

如果你只想复制一张表，你可以使用 mysqldump 导出该表的结构和数据，然后再导入到新的数据库或新的表中。

以下是一个简单的实例。

备份表数据：

1
mysqldump -u username -p dbname old_table > old_table_dump.sql

这将导出名为 old_table 的表的结构和数据到一个名为 old_table_dump.sql 的 SQL 文件。

你需要提供 MySQL 用户名和密码，并替换 username、dbname 和 old_table 为实际的值。

还原到新的数据库：

1
mysql -u username -p new_dbname < old_table_dump.sql

这将在新的数据库（new_dbname）中创建一个名为 old_table 的表，并将之前导出的结构和数据导入到新表中。

mysqldump 是 MySQL 提供的用于备份和导出数据库的命令行工具。

mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 导出数据需要使用 –tab 选项来指定导出文件指定的目录，该目标必须是可写的。

mysqldump 基本的用法：

1
mysqldump -u username -p password -h hostname database_name > output_file.sql

参数说明：

  • -u: 指定 MySQL 用户名。
  • -p: 提示输入密码。
  • -h: 指定 MySQL 主机名。
  • database_name: 要导出的数据库名称。
  • output_file.sql: 导出数据保存到的文件。

以下实例将数据表 runoob_tbl 导出到 /tmp 目录中：

1
2
3
$ mysqldump -u root -p --no-create-info \
            --tab=/tmp RUNOOB runoob_tbl
password ******
  • mysqldump 实例

1. 导出整个数据库

导出 mydatabase 数据库到 mydatabase_backup.sql 文件：

1
mysqldump -u root -p mydatabase > mydatabase_backup.sql

2. 导出特定表

如果你只想导出数据库中的某个表，可以使用以下命令：

1
mysqldump -u username -p password -h hostname database_name table_name > output_file.sql

或：

1
mysqldump -u root -p mydatabase mytable > mytable_backup.sql

3. 导出数据库结构

如果只想导出数据库结构而不包括数据，可以使用 –no-data 选项：

1
mysqldump -u username -p password -h hostname --no-data database_name > output_file.sql

4. 导出压缩文件

你可以将导出的数据进行压缩，以减小文件大小。例如，使用 gzip：

1
mysqldump -u username -p password -h hostname database_name | gzip > output_file.sql.gz
  • 导出 SQL 格式的数据

导出 SQL 格式的数据到指定文件，如下所示：

1
2
$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******

以上命令创建的文件内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- MySQL dump 8.23
--
-- Host: localhost    Database: RUNOOB
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `runoob_tbl`
--

CREATE TABLE runoob_tbl (
  runoob_id int(11) NOT NULL auto_increment,
  runoob_title varchar(100) NOT NULL default '',
  runoob_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (runoob_id),
  UNIQUE KEY AUTHOR_INDEX (runoob_author)
) TYPE=MyISAM;

--
-- Dumping data for table `runoob_tbl`
--

INSERT INTO runoob_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO runoob_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO runoob_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

如果你需要导出整个数据库的数据，可以使用以下命令：

1
2
$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******

如果需要备份所有数据库，可以使用以下命令：

1
2
$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

–all-databases 选项在 MySQL 3.23.12 及以后版本加入。

该方法可用于实现数据库的备份策略。

  • 将数据表及数据库拷贝至其他主机

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。

在源主机上执行以下命令，将数据备份至 dump.txt 文件中:

1
2
$ mysqldump -u root -p database_name table_name > dump.txt
password *****

如果完整备份数据库，则无需使用特定的表名称。

如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：

1
2
$ mysql -u root -p database_name < dump.txt
password *****

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：

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

以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

导入数据

本章节我们为大家介绍几种简单的 MySQL 导入数据命令。

mysql -u your_username -p -h your_host -P your_port -D your_database

  1. mysql 命令导入

使用 mysql 命令导入语法格式为：

1
mysql -u your_username -p -h your_host -P your_port -D your_database

your_username、your_host、your_port、your_database 分别为你的 MySQL 用户名、主机、端口和数据库。

实例：

1
# mysql -uroot -p123456 < runoob.sql

以上命令将将备份的整个数据库 runoob.sql 导入。

执行上述命令后，系统将要求输入 MySQL 用户的密码。输入密码并按Enter键。

这样，MySQL 将执行 SQL 文件中的语句，将数据导入到指定的数据库中。

请注意，如果 SQL 文件包含创建数据库的语句，确保在执行导入之前数据库已经存在。如果文件包含创建表的语句，确保表不存在或者是空的，以免导入数据时发生冲突。

  1. source 命令导入

source 命令导入数据库需要先登录到数库终端：

1
2
3
4
mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

使用 source 命令的好处是，你可以在 MySQL 命令行中直接执行，而无需退出 MySQL 并使用其他命令。

  1. 使用 LOAD DATA 导入数据

MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

1
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。

如果用户指定一个 FIELDS 子句，它的子句 （TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。

1
2
3
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

1
2
mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
    -> INTO TABLE mytbl (b, c, a);
  1. 使用 mysqlimport 导入数据

mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

1
2
$ mysqlimport -u root -p --local mytbl dump.txt
password *****

mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：

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

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

1
2
3
$ mysqlimport -u root -p --local --columns=b,c,a \
    mytbl dump.txt
password *****
  • mysqlimport的常用选项介绍
选项 功能
-d or --delete 新数据导入数据表中之前删除数据数据表中的所有信息
-f or --force 不管是否遇到错误，mysqlimport将强制继续插入数据
-i or --ignore mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。
-l or -lock-tables 数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。
-r or -replace 这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。
–fields-enclosed- by= char 指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。
–fields-terminated- by=char 指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（Tab）
–lines-terminated- by=str 此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。

mysqlimport 命令常用的选项还有 -v 显示版本（version）， -p 提示输入密码（password）等。

函数

MySQL 有很多内置的函数，以下列出了这些函数的说明。

MySQL 字符串函数

函数 描述 实例
ASCII(s) 返回字符串 s 的第一个字符的 ASCII 码。 返回 CustomerName 字段第一个字母的 ASCII 码：SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;
CHAR_LENGTH(s) 返回字符串 s 的字符数 返回字符串 RUNOOB 的字符数SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
CHARACTER_LENGTH(s) 返回字符串 s 的字符数，等同于 CHAR_LENGTH(s) 返回字符串 RUNOOB 的字符数SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
CONCAT(s1,s2…sn) 字符串 s1,s2 等多个字符串合并为一个字符串 合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2…sn) 同 CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 合并多个字符串，并添加分隔符：SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
FIELD(s,s1,s2…) 返回第一个字符串 s 在字符串列表(s1,s2…)中的位置 返回字符串 c 在列表值中的位置：SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置 返回字符串 c 在指定字符串中的位置：SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n) 函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入。 格式化数字 “#,###.##” 形式：SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56
INSERT(s1,x,len,s2) 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 从字符串第一个位置开始的 6 个字符替换为 runoob：SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com
LOCATE(s1,s) 从字符串 s 中获取 s1 的开始位置 获取 b 在字符串 abc 中的位置：SELECT LOCATE('st','myteststring'); -- 5返回字符串 abc 中 b 的位置：SELECT LOCATE('b', 'abc') -- 2
LCASE(s) 将字符串 s 的所有字母变成小写字母 字符串 RUNOOB 转换为小写：SELECT LCASE('RUNOOB') -- runoob
LEFT(s,n) 返回字符串 s 的前 n 个字符 返回字符串 runoob 中的前两个字符：SELECT LEFT('runoob',2) -- ru
LOWER(s) 将字符串 s 的所有字母变成小写字母 字符串 RUNOOB 转换为小写：SELECT LOWER('RUNOOB') -- runoob
LPAD(s1,len,s2) 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len 将字符串 xx 填充到 abc 字符串的开始处：SELECT LPAD('abc',5,'xx') -- xxabc
LTRIM(s) 去掉字符串 s 开始处的空格 去掉字符串 RUNOOB开始处的空格：SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB
MID(s,n,len) 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len) 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO
POSITION(s1 IN s) 从字符串 s 中获取 s1 的开始位置 返回字符串 abc 中 b 的位置：SELECT POSITION('b' in 'abc') -- 2
REPEAT(s,n) 将字符串 s 重复 n 次 将字符串 runoob 重复三次：SELECT REPEAT('runoob',3) -- runoobrunoobrunoob
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1 将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE('abc','a','x') --xbc
REVERSE(s) 将字符串s的顺序反过来 将字符串 abc 的顺序反过来：SELECT REVERSE('abc') -- cba
RIGHT(s,n) 返回字符串 s 的后 n 个字符 返回字符串 runoob 的后两个字符：SELECT RIGHT('runoob',2) -- ob
RPAD(s1,len,s2) 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len 将字符串 xx 填充到 abc 字符串的结尾处：SELECT RPAD('abc',5,'xx') -- abcxx
RTRIM(s) 去掉字符串 s 结尾处的空格 去掉字符串 RUNOOB 的末尾空格：SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB
SPACE(n) 返回 n 个空格 返回 10 个空格：SELECT SPACE(10);
STRCMP(s1,s2) 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 比较字符串：SELECT STRCMP("runoob", "runoob"); -- 0
SUBSTR(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串，等同于 SUBSTR(s, start, length) 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING_INDEX(s, delimiter, number) 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。 SELECT SUBSTRING_INDEX('a*b','*',1) -- a SELECT SUBSTRING_INDEX('a*b','*',-1) -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c
TRIM(s) 去掉字符串 s 开始和结尾处的空格 去掉字符串 RUNOOB 的首尾空格：SELECT TRIM(' RUNOOB ') AS TrimmedString;
UCASE(s) 将字符串转换为大写 将字符串 runoob 转换为大写：SELECT UCASE("runoob"); -- RUNOOB
UPPER(s) 将字符串转换为大写 将字符串 runoob 转换为大写：SELECT UPPER("runoob"); -- RUNOOB

MySQL 数字函数

函数名 描述 实例
ABS(x) 返回 x 的绝对值 返回 -1 的绝对值：SELECT ABS(-1) -- 返回1
ACOS(x) 求 x 的反余弦值（单位为弧度），x 为一个数值 SELECT ACOS(0.25);
ASIN(x) 求反正弦值（单位为弧度），x 为一个数值 SELECT ASIN(0.25);
ATAN(x) 求反正切值（单位为弧度），x 为一个数值 SELECT ATAN(2.5);
ATAN2(n, m) 求反正切值（单位为弧度） SELECT ATAN2(-0.8, 2);
AVG(expression) 返回一个表达式的平均值，expression 是一个字段 返回 Products 表中Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x) 返回大于或等于 x 的最小整数 SELECT CEIL(1.5) -- 返回2
CEILING(x) 返回大于或等于 x 的最小整数 SELECT CEILING(1.5); -- 返回2
COS(x) 求余弦值(参数是弧度) SELECT COS(2);
COT(x) 求余切值(参数是弧度) SELECT COT(6);
COUNT(expression) 返回查询的记录总数，expression 参数是一个字段或者 * 号 返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x) 将弧度转换为角度 SELECT DEGREES(3.1415926535898) -- 180
n DIV m 整除，n 为被除数，m 为除数 计算 10 除于 5：SELECT 10 DIV 5; -- 2
EXP(x) 返回 e 的 x 次方 计算 e 的三次方：SELECT EXP(3) -- 20.085536923188
FLOOR(x) 返回小于或等于 x 的最大整数 小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值 返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字符串列表中的最大值：SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob
LEAST(expr1, expr2, expr3, …) 返回列表中的最小值 返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串列表中的最小值：SELECT LEAST("Google", "Runoob", "Apple"); -- Apple
LN 返回数字的自然对数，以 e 为底。 返回 2 的自然对数：SELECT LN(2); -- 0.6931471805599453
LOG(x) 或 LOG(base, x) 返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。 SELECT LOG(20.085536923188) -- 3 SELECT LOG(2, 4); -- 2
LOG10(x) 返回以 10 为底的对数 SELECT LOG10(100) -- 2
LOG2(x) 返回以 2 为底的对数 返回以 2 为底 6 的对数：SELECT LOG2(6); -- 2.584962500721156
MAX(expression) 返回字段 expression 中的最大值 返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression) 返回字段 expression 中的最小值 返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y) 返回 x 除以 y 以后的余数 5 除于 2 的余数：SELECT MOD(5,2) -- 1
PI() 返回圆周率(3.141593） SELECT PI() --3.141593
POW(x,y) 返回 x 的 y 次方 2 的 3 次方：SELECT POW(2,3) -- 8
POWER(x,y) 返回 x 的 y 次方 2 的 3 次方：SELECT POWER(2,3) -- 8
RADIANS(x) 将角度转换为弧度 180 度转换为弧度：SELECT RADIANS(180) -- 3.1415926535898
RAND() 返回 0 到 1 的随机数 SELECT RAND() --0.93099315644334
ROUND(x [,y]) 返回离 x 最近的整数，可选参数 y 表示要四舍五入的小数位数，如果省略，则返回整数。 SELECT ROUND(1.23456) --1 SELECT ROUND(345.156, 2) -- 345.16
SIGN(x) 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1 SELECT SIGN(-10) -- (-1)
SIN(x) 求正弦值(参数是弧度) SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x) 返回x的平方根 25 的平方根：SELECT SQRT(25) -- 5
SUM(expression) 返回指定字段的总和 计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x) 求正切值(参数是弧度) SELECT TAN(1.75); -- -5.52037992250933
TRUNCATE(x,y) 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） SELECT TRUNCATE(1.23456,3) -- 1.234

MySQL 日期函数

函数名 描述 实例
ADDDATE(d,n) 计算起始日期 d 加上 n 天的日期 SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25
ADDTIME(t,n) n 是一个时间表达式，时间 t 加上时间表达式 n 加 5 秒：SELECT ADDTIME('2011-11-11 11:11:11', 5); ->2011-11-11 11:11:16 (秒)添加 2 小时, 10 分钟, 5 秒:SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26
CURDATE() 返回当前日期 SELECT CURDATE(); -> 2018-09-19
CURRENT_DATE() 返回当前日期 SELECT CURRENT_DATE(); -> 2018-09-19
CURRENT_TIME 返回当前时间 SELECT CURRENT_TIME(); -> 19:59:02
CURRENT_TIMESTAMP() 返回当前日期和时间 SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43
CURTIME() 返回当前时间 SELECT CURTIME(); -> 19:59:02
DATE() 从日期或日期时间表达式中提取日期值 SELECT DATE("2017-06-15"); -> 2017-06-15
DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数 SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32
DATE_ADD(d，INTERVAL expr type) 计算起始日期 d 加上一个时间段后的日期，type 值可以是：MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); -> 2017-06-25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE); -> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR); ->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 MONTH); ->2017-03-15 09:34:21
DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。 Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
DAY(d) 返回日期值 d 的日期部分 SELECT DAY("2017-06-15"); -> 15
DAYNAME(d) 返回日期 d 是星期几，如 Monday,Tuesday SELECT DAYNAME('2011-11-11 11:11:11') ->Friday
DAYOFMONTH(d) 计算日期 d 是本月的第几天 SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11
DAYOFWEEK(d) 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6
DAYOFYEAR(d) 计算日期 d 是本年的第几天 SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315
EXTRACT(type FROM d) 从日期 d 中获取指定的值，type 指定返回的值。 type可取值为： MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') -> 11
FROM_DAYS(n) 计算从 0000 年 1 月 1 日开始 n 天后的日期 SELECT FROM_DAYS(1111) -> 0003-01-16
HOUR(t) 返回 t 中的小时值 SELECT HOUR('1:2:3') -> 1
LAST_DAY(d) 返回给给定日期的那一月份的最后一天 SELECT LAST_DAY("2017-06-20"); -> 2017-06-30
LOCALTIME() 返回当前日期和时间 SELECT LOCALTIME() -> 2018-09-19 20:57:43
LOCALTIMESTAMP() 返回当前日期和时间 SELECT LOCALTIMESTAMP() -> 2018-09-19 20:57:43
MAKEDATE(year, day-of-year) 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 SELECT MAKEDATE(2017, 3); -> 2017-01-03
MAKETIME(hour, minute, second) 组合时间，参数分别为小时、分钟、秒 SELECT MAKETIME(11, 35, 4); -> 11:35:04
MICROSECOND(date) 返回日期参数所对应的微秒数 SELECT MICROSECOND("2017-06-20 09:34:00.000023"); -> 23
MINUTE(t) 返回 t 中的分钟值 SELECT MINUTE('1:2:3') -> 2
MONTHNAME(d) 返回日期当中的月份名称，如 November SELECT MONTHNAME('2011-11-11 11:11:11') -> November
MONTH(d) 返回日期d中的月份值，1 到 12 SELECT MONTH('2011-11-11 11:11:11') ->11
NOW() 返回当前日期和时间 SELECT NOW() -> 2018-09-19 20:57:43
PERIOD_ADD(period, number) 为 年-月 组合日期添加一个时段 SELECT PERIOD_ADD(201703, 5); -> 201708
PERIOD_DIFF(period1, period2) 返回两个时段之间的月份差值 SELECT PERIOD_DIFF(201710, 201703); -> 7
QUARTER(d) 返回日期d是第几季节，返回 1 到 4 SELECT QUARTER('2011-11-11 11:11:11') -> 4
SECOND(t) 返回 t 中的秒钟值 SELECT SECOND('1:2:3') -> 3
SEC_TO_TIME(s) 将以秒为单位的时间 s 转换为时分秒的格式 SELECT SEC_TO_TIME(4320) -> 01:12:00
STR_TO_DATE(string, format_mask) 将字符串转变为日期 SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10
SUBDATE(d,n) 日期 d 减去 n 天后的日期 SELECT SUBDATE('2011-11-11 11:11:11', 1) ->2011-11-10 11:11:11 (默认是天)
SUBTIME(t,n) 时间 t 减去 n 秒的时间 SELECT SUBTIME('2011-11-11 11:11:11', 5) ->2011-11-11 11:11:06 (秒)
SYSDATE() 返回当前日期和时间 SELECT SYSDATE() -> 2018-09-19 20:57:43
TIME(expression) 提取传入表达式的时间部分 SELECT TIME("19:30:10"); -> 19:30:10
TIME_FORMAT(t,f) 按表达式 f 的要求显示时间 t SELECT TIME_FORMAT('11:11:11','%r') 11:11:11 AM
TIME_TO_SEC(t) 将时间 t 转换为秒 SELECT TIME_TO_SEC('1:12:00') -> 4320
TIMEDIFF(time1, time2) 计算时间差值 mysql> SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01 mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', -> '2000:01:01 00:00:00.000001'); -> '-00:00:00.000001' mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001', -> '2008-12-30 01:01:01.000002'); -> '46:58:57.999999'
TIMESTAMP(expression, interval) 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 mysql> SELECT TIMESTAMP("2017-07-23", "13:10:11"); -> 2017-07-23 13:10:11 mysql> SELECT TIMESTAMP('2003-12-31'); -> '2003-12-31 00:00:00' mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00'); -> '2004-01-01 00:00:00'
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差 mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少天 -> 89 mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少月 -> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); // 计算两个时间相隔多少年 -> -1 mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); // 计算两个时间相隔多少分钟 -> 128885
TO_DAYS(d) 计算日期 d 距离 0000 年 1 月 1 日的天数 SELECT TO_DAYS('0001-01-01 01:01:01') -> 366
WEEK(d) 计算日期 d 是本年的第几个星期，范围是 0 到 53 SELECT WEEK('2011-11-11 11:11:11') -> 45
WEEKDAY(d) 日期 d 是星期几，0 表示星期一，1 表示星期二 SELECT WEEKDAY("2017-06-15"); -> 3
WEEKOFYEAR(d) 计算日期 d 是本年的第几个星期，范围是 0 到 53 SELECT WEEKOFYEAR('2011-11-11 11:11:11') -> 45
YEAR(d) 返回年份 SELECT YEAR("2017-06-15"); -> 2017
YEARWEEK(date, mode) 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 SELECT YEARWEEK("2017-06-15"); -> 201724

MySQL 高级函数

函数名 描述 实例
BIN(x) 返回 x 的二进制编码，x 为十进制数 15 的 2 进制编码:SELECT BIN(15); -- 1111
BINARY(s) 将字符串 s 转换为二进制字符串 SELECT BINARY "RUNOOB"; -> RUNOOB
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 SELECT CASE 　WHEN 1 > 0 　THEN '1 > 0' 　WHEN 2 > 0 　THEN '2 > 0' 　ELSE '3 > 0' 　END ->1 > 0
CAST(x AS type) 转换数据类型 字符串日期转换为日期：SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29
COALESCE(expr1, expr2, …, expr_n) 返回参数中的第一个非空表达式（从左向右） SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com'); -> runoob.com
CONNECTION_ID() 返回唯一的连接 ID SELECT CONNECTION_ID(); -> 4292835
CONV(x,f1,f2) 返回 f1 进制数变成 f2 进制数 SELECT CONV(15, 10, 2); -> 1111
CONVERT(s USING cs) 函数将字符串 s 的字符集变成 cs SELECT CHARSET('ABC') ->utf-8 SELECT CHARSET(CONVERT('ABC' USING gbk)) ->gbk
CURRENT_USER() 返回当前用户 SELECT CURRENT_USER(); -> guest@%
DATABASE() 返回当前数据库名 SELECT DATABASE(); -> runoob
IF(expr,v1,v2) 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 SELECT IF(1 > 0,'正确','错误') ->正确
IFNULL(v1,v2) 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 SELECT IFNULL(null,'Hello Word') ->Hello Word
ISNULL(expression) 判断表达式是否为 NULL SELECT ISNULL(NULL); ->1
LAST_INSERT_ID() 返回最近生成的 AUTO_INCREMENT 值 SELECT LAST_INSERT_ID(); ->6
NULLIF(expr1, expr2) 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 SELECT NULLIF(25, 25); ->
SESSION_USER() 返回当前用户 SELECT SESSION_USER(); -> guest@%
SYSTEM_USER() 返回当前用户 SELECT SYSTEM_USER(); -> guest@%
USER() 返回当前用户 SELECT USER(); -> guest@%
VERSION() 返回数据库的版本号 SELECT VERSION() -> 5.6.34

以下是 MySQL 8.0 版本新增的一些常用函数：

函数 描述 实例
JSON_OBJECT() 将键值对转换为 JSON 对象 SELECT JSON_OBJECT('key1', 'value1', 'key2', 'value2')
JSON_ARRAY() 将值转换为 JSON 数组 SELECT JSON_ARRAY(1, 2, 'three')
JSON_EXTRACT() 从 JSON 字符串中提取指定的值 SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name')
JSON_CONTAINS() 检查一个 JSON 字符串是否包含指定的值 SELECT JSON_CONTAINS('{"name": "John", "age": 30}', 'John', '$.name')
ROW_NUMBER() 为查询结果中的每一行分配一个唯一的数字 SELECT ROW_NUMBER() OVER(ORDER BY id) AS row_number, name FROM users
RANK() 为查询结果中的每一行分配一个排名 SELECT RANK() OVER(ORDER BY score DESC) AS rank, name, score FROM students

运算符

本章节我们主要介绍 MySQL 的运算符及运算符的优先级。 MySQL 主要有以下几种运算符：

  • 算术运算符
  • 比较运算符
  • 逻辑运算符
  • 位运算符

算术运算符

MySQL 支持的算术运算符包括:

运算符 作用
+ 加法
- 减法
* 乘法
/ 或 DIV 除法
% 或 MOD 取余

在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。

1、加

1
2
3
4
5
6
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+

2、减

1
2
3
4
5
6
mysql> select 1-2;
+-----+
| 1-2 |
+-----+
|  -1 |
+-----+

3、乘

1
2
3
4
5
6
mysql> select 2*3;
+-----+
| 2*3 |
+-----+
|   6 |
+-----+

4、除

1
2
3
4
5
6
mysql> select 2/3;
+--------+
| 2/3    |
+--------+
| 0.6667 |
+--------+

5、商

1
2
3
4
5
6
mysql> select 10 DIV 4;
+----------+
| 10 DIV 4 |
+----------+
|        2 |
+----------+

6、取余

1
2
3
4
5
6
mysql> select 10 MOD 4;
+----------+
| 10 MOD 4 |
+----------+
|        2 |
+----------+

比较运算符

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

符号 描述 备注
= 等于
<>, != 不等于
> 大于
< 小于
<= 小于等于
>= 大于等于
BETWEEN 在两值之间 >=min&&<=max
NOT BETWEEN 不在两值之间
IN 在集合中
NOT IN 不在集合中
<=> 严格比较两个NULL值是否相等 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE 模糊匹配
REGEXP 或 RLIKE 正则式匹配
IS NULL 为空
IS NOT NULL 不为空

1、等于

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select 2=3;
+-----+
| 2=3 |
+-----+
|   0 |
+-----+


mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

2、不等于

1
2
3
4
5
6
mysql> select 2<>3;
+------+
| 2<>3 |
+------+
|    1 |
+------+

3、安全等于

与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+


mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

        
mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+

4、小于

1
2
3
4
5
6
mysql> select 2<3;
+-----+
| 2<3 |
+-----+
|   1 |
+-----+

5、小于等于

1
2
3
4
5
6
mysql> select 2<=3;
+------+
| 2<=3 |
+------+
|    1 |
+------+

6、大于

1
2
3
4
5
6
mysql> select 2>3;
+-----+
| 2>3 |
+-----+
|   0 |
+-----+

7、大于等于

1
2
3
4
5
6
mysql> select 2>=3;
+------+
| 2>=3 |
+------+
|    0 |
+------+

8、BETWEEN

1
2
3
4
5
6
mysql> select 5 between 1 and 10;
+--------------------+
| 5 between 1 and 10 |
+--------------------+
|                  1 |
+--------------------+

9、IN

1
2
3
4
5
6
mysql> select 5 in (1,2,3,4,5);
+------------------+
| 5 in (1,2,3,4,5) |
+------------------+
|                1 |
+------------------+

10、NOT IN

1
2
3
4
5
6
mysql> select 5 not in (1,2,3,4,5);
+----------------------+
| 5 not in (1,2,3,4,5) |
+----------------------+
|                    0 |
+----------------------+

11、IS NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select null is NULL;
+--------------+
| null is NULL |
+--------------+
|            1 |
+--------------+

mysql> select 'a' is NULL;
+-------------+
| 'a' is NULL |
+-------------+
|           0 |
+-------------+

12、IS NOT NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select null IS NOT NULL;
+------------------+
| null IS NOT NULL |
+------------------+
|                0 |
+------------------+

        
mysql> select 'a' IS NOT NULL;
+-----------------+
| 'a' IS NOT NULL |
+-----------------+
|               1 |
+-----------------+

13、LIKE

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select '12345' like '12%';
+--------------------+
| '12345' like '12%' |
+--------------------+
|                  1 |
+--------------------+

mysql> select '12345' like '12_';
+--------------------+
| '12345' like '12_' |
+--------------------+
|                  0 |
+--------------------+

14、REGEXP

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select 'beijing' REGEXP 'jing';
+-------------------------+
| 'beijing' REGEXP 'jing' |
+-------------------------+
|                       1 |
+-------------------------+

mysql> select 'beijing' REGEXP 'xi';
+-----------------------+
| 'beijing' REGEXP 'xi' |
+-----------------------+
|                     0 |
+-----------------------+

逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

运算符号 作用
NOT 或 ! 逻辑非
AND 逻辑与
OR 逻辑或
XOR 逻辑异或

1、与

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select 2 and 0;
+---------+
| 2 and 0 |
+---------+
|       0 |
+---------+

        
mysql> select 2 and 1;   
+---------+     
| 2 and 1 |      
+---------+      
|       1 |      
+---------+

2、或

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select 2 or 0;
+--------+
| 2 or 0 |
+--------+
|      1 |
+--------+

mysql> select 2 or 1;
+--------+
| 2 or 1 |
+--------+
|      1 |
+--------+

mysql> select 0 or 0;
+--------+
| 0 or 0 |
+--------+
|      0 |
+--------+

mysql> select 1 || 0;
+--------+
| 1 || 0 |
+--------+
|      1 |
+--------+

3、非

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select not 1;
+-------+
| not 1 |
+-------+
|     0 |
+-------+

mysql> select !0;
+----+
| !0 |
+----+
|  1 |
+----+

4、异或

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> select 1 xor 1;
+---------+
| 1 xor 1 |
+---------+
|       0 |
+---------+

mysql> select 0 xor 0;
+---------+
| 0 xor 0 |
+---------+
|       0 |
+---------+

mysql> select 1 xor 0;
+---------+
| 1 xor 0 |
+---------+
|       1 |
+---------+

mysql> select null or 1;
+-----------+
| null or 1 |
+-----------+
|         1 |
+-----------+

mysql> select 1 ^ 0;
+-------+
| 1 ^ 0 |
+-------+
|     1 |
+-------+

位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

运算符号 作用
& 按位与
| 按位或
^ 按位异或
! 取反
<< 左移
>> 右移

1、按位与

1
2
3
4
5
6
mysql> select 3&5;
+-----+
| 3&5 |
+-----+
|   1 |
+-----+

2、按位或

1
2
3
4
5
6
mysql> select 3|5;
+-----+
| 3|5 |
+-----+
|   7 |
+-----+

3、按位异或

1
2
3
4
5
6
mysql> select 3^5;
+-----+
| 3^5 |
+-----+
|   6 |
+-----+

4、按位取反

1
2
3
4
5
6
mysql> select ~18446744073709551612;
+-----------------------+
| ~18446744073709551612 |
+-----------------------+
|                     3 |
+-----------------------+

5、按位右移

1
2
3
4
5
6
mysql> select 3>>1;
+------+
| 3>>1 |
+------+
|    1 |
+------+

6、按位左移

1
2
3
4
5
6
mysql> select 3<<1;
+------+
| 3<<1 |
+------+
|    6 |
+------+

运算符优先级

最低优先级为： :=。

命令大全

基础命令

操作 命令
连接到 MySQL 数据库 mysql -u 用户名 -p
查看所有数据库 SHOW DATABASES;
选择一个数据库 USE 数据库名;
查看所有表 SHOW TABLES;
查看表结构 DESCRIBE 表名; 或 SHOW COLUMNS FROM 表名;
创建一个新数据库 CREATE DATABASE 数据库名;
删除一个数据库 DROP DATABASE 数据库名;
创建一个新表 CREATE TABLE 表名 (列名1 数据类型 [约束], 列名2 数据类型 [约束], ...);
删除一个表 DROP TABLE 表名;
插入数据 INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
查询数据 SELECT 列1, 列2, ... FROM 表名 WHERE 条件;
更新数据 UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件;
删除数据 DELETE FROM 表名 WHERE 条件;
创建用户 CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
授权用户 GRANT 权限 ON 数据库名.* TO '用户名'@'主机';
刷新权限 FLUSH PRIVILEGES;
查看当前用户 SELECT USER();
退出 MySQL EXIT;

数据库相关命令

下面是与 MySQL 数据库操作相关的命令，包括创建、删除和修改数据库等操作：

操作 命令
创建数据库 CREATE DATABASE 数据库名;
删除数据库 DROP DATABASE 数据库名;
修改数据库编码格式和排序规则 ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 编码格式 DEFAULT COLLATE 排序规则;
查看所有数据库 SHOW DATABASES;
查看数据库详细信息 SHOW CREATE DATABASE 数据库名;
选择数据库 USE 数据库名;
查看数据库的状态信息 SHOW STATUS;
查看数据库的错误信息 SHOW ERRORS;
查看数据库的警告信息 SHOW WARNINGS;
查看数据库的表 SHOW TABLES;
查看表的结构 DESC 表名; DESCRIBE 表名; SHOW COLUMNS FROM 表名; EXPLAIN 表名;
创建表 CREATE TABLE 表名 (列名1 数据类型 [约束], 列名2 数据类型 [约束], ...);
删除表 DROP TABLE 表名;
修改表结构 ALTER TABLE 表名 ADD 列名 数据类型 [约束]; ALTER TABLE 表名 DROP 列名; ALTER TABLE 表名 MODIFY 列名 数据类型 [约束];
查看表的创建 SQL SHOW CREATE TABLE 表名;

数据表相关命令

以下是与 MySQL 数据表相关的常用命令，包括创建、修改、删除表以及查看表的结构和数据等操作：

操作 命令
创建表 CREATE TABLE 表名 (列名1 数据类型 [约束], 列名2 数据类型 [约束], ...);
删除表 DROP TABLE 表名;
修改表结构 添加列: ALTER TABLE 表名 ADD 列名 数据类型 [约束]; 删除列: ALTER TABLE 表名 DROP 列名; 修改列: ALTER TABLE 表名 MODIFY 列名 数据类型 [约束]; 重命名列: ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [约束];
查看表结构 DESC 表名; DESCRIBE 表名; SHOW COLUMNS FROM 表名; EXPLAIN 表名;
查看表的创建 SQL SHOW CREATE TABLE 表名;
查看表中的所有数据 SELECT * FROM 表名;
插入数据 INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
更新数据 UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件;
删除数据 DELETE FROM 表名 WHERE 条件;
查看表的索引 SHOW INDEX FROM 表名;
创建索引 CREATE INDEX 索引名 ON 表名 (列名);
删除索引 DROP INDEX 索引名 ON 表名;
查看表的约束 SHOW CREATE TABLE 表名; (约束信息会包含在创建表的 SQL 中)
查看表的统计信息 SHOW TABLE STATUS LIKE '表名';

MySQL 事务相关命令

以下是与 MySQL 事务相关的常用命令：

操作 命令
开始事务 START TRANSACTION; 或 BEGIN;
提交事务 COMMIT;
回滚事务 ROLLBACK;
查看当前事务的状态 SHOW ENGINE INNODB STATUS; (可查看 InnoDB 存储引擎的事务状态)
锁定表以进行事务操作 LOCK TABLES 表名 WRITE; 或 LOCK TABLES 表名 READ;
释放锁定的表 UNLOCK TABLES;
设置事务的隔离级别 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

参考资料



文章链接：
https://www.zywvvd.com/notes/coding/dataset/mysql/mysql-tutorial/

“觉得不错的话，给点打赏吧 ୧(๑•̀⌄•́๑)૭”

微信二维码

微信支付

支付宝二维码

支付宝支付

Coding > Database > Mysql
#Coding #Database #Mysql
MySQL 教程
https://www.zywvvd.com/notes/coding/dataset/mysql/mysql-tutorial/
作者
Yiwei Zhang
发布于
2024年8月15日
许可协议