《MySQL》

简介

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

RDBMS 术语

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

数据库操作

创建数据库

创建数据库的语法是：

create database 数据库名;

通常情况下，我们在创建数据库的时候会额外加上 if not exists，否则当数据库已经存在的时候，会报 database exists 的错误。

切换数据库

切换数据库的语法是：

use 数据库名;

删除数据库

drop database 数据库名;

删除数据库的时候，也可以带上 if exists，这样当数据库不存在的时候，就不会报错了。

drop database if exists test;

表操作

查表

查看当前数据库中的所有表，可以使用 show tables; 命令。

假如不指定数据库的话，我们可以通过 show tables from database 的方式，来指定要操作的表。

建表

创建表的语法如下：

create table 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    ...
    列名n 数据类型n
);

我们创建一张文章表，表中包含文章的标题、内容、作者、发布时间、阅读量等信息，那么可以这样创建：

create table article(
    id int primary key auto_increment,
    title varchar(100) not null,
    content text not null,
    author varchar(20) not null,
    create_time datetime not null,
    read_count int default 0
);

article 是表名；
id 是主键，类型为 int，自增长；
title 是标题，类型为 varchar，长度为 100，不允许为空；
content 是内容，类型为 text，不允许为空；
author 是作者，类型为 varchar，长度为 20，不允许为空；
create_time 是发布时间，类型为 datetime，不允许为空；
read_count 是阅读量，类型为 int，默认值为 0。

执行上述语句后，可以使用 show tables; 命令查看当前数据库中的所有表。

给表添加注释

在建表的时候，我们可以给表添加注释，语法如下：

create table 表名(
    列名1 数据类型1 comment '注释1',
    列名2 数据类型2 comment '注释2',
    ...
    列名n 数据类型n comment '注释n'
) comment '表注释';

方便我们在后期维护的时候，能够更好的理解表的含义。

在之前的基础上增加了一些字段的注释和表注释：

create table article(
    id int primary key auto_increment comment '主键',
    title varchar(100) not null comment '标题',
    content text not null comment '内容',
    author varchar(20) not null comment '作者',
    create_time datetime not null comment '发布时间',
    read_count int default 0 comment '阅读量'
) comment '文章表';

假如表已存在

由于之前 article 表已经创建了，这时候再执行上述语句，就会报错 Table 'article' already exists

为了避免这种情况，我们可以在建表的时候，先判断表是否存在，如果不存在，再创建表，语法如下：

create table if not exists 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    ...
    列名n 数据类型n
);

删表

删除表的语法如下：

drop table 表名;

在删除表的时候也可以加上 if exists，语法如下：

drop table if exists 表名;

这样可以防止表不存在的时候，报错。

查看表结构

有时候，我们想知道表的结构是什么样的，也就是 create table 的时候包含了哪些列、列有哪些属性，那这时候我们可以使用以下这些命令查看：

desc 表名;
describe 表名;
explain 表名;
show columns from 表名;
show fields from 表名;

还有一个命令 show create table 表名;，可以查看建表语句。

假如没有使用 use 数据库名; 命令指定要操作的数据库，那我们可以通过 show columns from 数据库名.表名; 的方式，来查看表的结构，例如：

show create table itwanger.article;

可以通过 \G 来格式化输出，例如：

show create table itwanger.article \G;

数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
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的值	小数值

smallint 和 mediuint 这两种类型很少用到，一般我们用的是 tinyint、int、bigint 这三种类型。

对于主键的数据类型选择，不同的业务场景有不同的需求，如果需要确保跨多个数据库或者系统唯一性，那么 UUID 或者 雪花算法 生成的 ID 会更合适。

UUID 不依赖于数据库的自增特性，非常适合分布式系统，但是 UUID 会占用更多的存储空间（CHAR(36) 或 VARCHAR(36)），而且不是递增的，会导致索引的性能下降。

有无符号

数值数据类型还可以选择有符号和无符号，有符号就是可以存储正数和负数，无符号就是只能存储正数。默认为有符号，也就是不用指定。

比如说 int 类型，如果是有符号的，那么范围是 -2147483648 到 2147483647，如果是无符号的，那么范围是 0 到 4294967295。

无符号的情况下，要特别注意和 Java 数据类型的对应关系。

Java 中的 int 范围 是 -2147483648 到 2147483647。如果 MySQL 选择的 int 类型是无符号的，范围就超出了 Java 的 int 类型范围了。

这时候，为了避免出现不兼容的情况，Java 的数据类型要选择 long 类型。

日期和时间类型

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

类型	大小 ( 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	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 类型，可存储的最大长度不同，可根据实际情况选择。

枚举与集合类型

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

空间数据类型

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

字段属性

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

插入数据

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

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

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

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

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

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

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

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

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

查询数据

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

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

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 是一个可选的子句，用于限制返回的行数。

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

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

WHERE 子句

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

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

语法

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。

简单实例

组合条件（AND、OR）:

SELECT * FROM products WHERE category = 'Electronics' AND price > 100.00;

SELECT * FROM orders WHERE order_date >= '2023-01-01' OR total_amount > 1000.00;

模糊匹配条件（LIKE）:

SELECT * FROM customers WHERE first_name LIKE 'J%';

IN 条件:

SELECT * FROM countries WHERE country_code IN ('US', 'CA', 'MX');

NOT 条件:

SELECT * FROM products WHERE NOT category = 'Clothing';

BETWEEN 条件:

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

IS NULL 条件

SELECT * FROM employees WHERE department IS NULL;

IS NOT NULL 条件:

SELECT * FROM customers WHERE email IS NOT NULL;

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

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

修改数据

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

语法

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

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

参数说明：

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

更多说明：

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

实例

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 列中。

删除数据

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

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

语法

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

DELETE FROM table_name
WHERE condition;

参数说明：

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

更多说明：

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

实例

DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date < '2023-01-01'
);

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

模糊查询

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

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

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

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

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

语法

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 子句来指定条件。

实例

百分号通配符 %：

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

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

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

下划线通配符 _：

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

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

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

组合使用 % 和 _：

SELECT * FROM users WHERE username LIKE 'a%o_';

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

不区分大小写的匹配：

SELECT * FROM employees WHERE last_name LIKE 'smi%' COLLATE utf8mb4_general_ci;

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

UNION

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

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

语法

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 子句是一个可选的子句，用于指定合并后的结果集的排序顺序。

实例

基本的 UNION 操作：

SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;

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

使用过滤条件的 UNION：

SELECT product_name FROM products WHERE category = 'Electronics'
UNION
SELECT product_name FROM products WHERE category = 'Clothing'
ORDER BY product_name;

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

UNION 操作中的列数和数据类型必须相同：

SELECT first_name, last_name FROM employees
UNION
SELECT department_name, NULL FROM departments
ORDER BY first_name;

返回的结果包括了 employees 表中的 first_name 和 last_name，以及 departments 表中的 department_name 和 NULL，所有的结果都按照 first_name 列排序。

使用 UNION ALL 不去除重复行：

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
ORDER BY city;

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

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

排序语句

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

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

语法

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 子句来设置条件。

实例

单列排序：

SELECT * FROM products
ORDER BY product_name ASC;

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

多列排序：

SELECT * FROM employees
ORDER BY department_id ASC, hire_date DESC;

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

使用数字表示列的位置：

SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 1 ASC;

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

使用表达式排序：

SELECT product_name, price * discount_rate AS discounted_price
FROM products
ORDER BY discounted_price DESC;

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

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

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

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

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

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

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

分组语句

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

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

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

语法

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 语句如下：

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

注意事项：

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

SELECT column1, column2, aggregate_function(column3)
FROM TABLE_NAME
WHERE condition
GROUP BY column1, column2;

连接语句

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

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

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

INNER JOIN

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

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：

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、客户名称和产品名称，连接了这些表的关联列。

使用 WHERE 子句进行过滤：

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 语句的基本语法：

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

多表 LEFT JOIN：

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 中没有匹配的行，仍然会返回客户和订单的信息。

使用 WHERE 子句进行过滤：

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 并不经常使用，因为它可以用 LEFT 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 运算符。

注意：
select * , columnName1+ifnull(columnName2,0) from tableName;
columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。

常见注意事项和技巧

检查是否为 NULL：

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

SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE department_id IS NOT NULL;

使用 COALESCE 函数处理 NULL：

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

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

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

使用 IFNULL 函数处理 NULL：

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

SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity
FROM products;

NULL 排序：

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

SELECT product_name, price
FROM products
ORDER BY price ASC NULLS FIRST;

使用 <=> 操作符进行 NULL 比较：

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

SELECT * FROM employees WHERE commission <=> NULL;

注意聚合函数对 NULL 的处理：

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

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

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

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

正则表达式

我们了解过 MySQL 可以通过 LIKE …% 来进行模糊匹配。

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

下表中的正则模式可应用于 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 运算符的基本语法：

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

参数说明：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

使用 RLIKE 进行模式匹配

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

事务

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 – 用于开始一个事务：

BEGIN; -- 或者使用 START TRANSACTION;

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

COMMIT;

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

ROLLBACK;

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

SAVEPOINT savepoint_name;

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

ROLLBACK TO SAVEPOINT savepoint_name;

举例：

-- 开始事务
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;

实例：

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 命令的常见用法和实例：

添加列

ALTER TABLE table_name
ADD COLUMN new_column_name datatype;

修改列的数据类型

ALTER TABLE TABLE_NAME
MODIFY COLUMN column_name new_datatype;

修改列名

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;

删除列

ALTER TABLE table_name
DROP COLUMN column_name;

添加 PRIMARY KEY

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

添加 FOREIGN KEY

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES parent_table (column_name);

修改表名

ALTER TABLE old_table_name
RENAME TO new_table_name;

修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 对 Null 值和默认值的影响

当你修改字段时，你可以指定是否包含值或者是否设置默认值。

以下实例，指定字段 j 为 NOT NULL 且默认值为 100。

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

修改字段默认值

你可以使用 ALTER 来修改字段的默认值，尝试以下实例：

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Changing a Table Type:

索引

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

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

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

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

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

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

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

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

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

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

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

普通索引

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

创建索引

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

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

CREATE INDEX 的语法：

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 列上创建一个普通索引。

CREATE INDEX idx_name ON students (name);

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

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

修改表结构(添加索引)

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

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

ALTER TABLE 创建索引的语法：

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 的表上创建一个普通索引:

ALTER TABLE employees
ADD INDEX idx_age (age);

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

创建表的时候直接指定

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

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 列上创建一个普通索引。

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX idx_age (age)
);

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

删除索引

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

DROP INDEX 的语法：

DROP INDEX index_name ON table_name;

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

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

ALTER TABLE table_name
DROP INDEX index_name;

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

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

DROP INDEX idx_age ON employees;

或使用 ALTER TABLE 语句：

ALTER TABLE employees
DROP INDEX idx_age;

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

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

唯一索引

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

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

创建索引

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列上创建一个唯一索引，以确保每个员工的电子邮件地址都是唯一的。

CREATE UNIQUE INDEX idx_email ON employees (email);

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

修改表结构添加索引

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

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

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 列上创建一个唯一索引，以确保每个员工的电子邮件地址都是唯一的。

ALTER TABLE employees
ADD CONSTRAINT idx_email UNIQUE (email);

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

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

创建表的时候直接指定

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

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

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

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 列的值是唯一的，因此我们要在创建表时定义唯一索引。

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 ，用于全文索引。

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

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

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

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

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

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

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

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

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

显示索引信息

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

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

SHOW INDEX 语句：:

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 客户端程序连接 MySQL 数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

创建临时表

CREATE TEMPORARY TABLE temp_table_name (
  column1 datatype,
  column2 datatype,
  ...
);

或者简写为：

CREATE TEMPORARY TABLE temp_table_name AS
SELECT column1, column2, ...
FROM source_table
WHERE condition;

插入数据

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

查询临时表

SELECT * FROM temp_table_name;

修改临时表

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

ALTER TABLE temp_table_name
ADD COLUMN new_column datatype;

删除临时表

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

DROP TEMPORARY TABLE IF EXISTS temp_table_name;

临时表对于需要在某个会话中存储中间结果集或进行复杂查询时非常有用。

临时表的作用范围仅限于创建它的会话。其他会话无法直接访问或引用该临时表。在多个会话之间共享数据时，可以考虑使用普通表而不是临时表。

请注意，临时表在会话结束时会被自动删除，但也可以使用 DROP TEMPORARY TABLE 明确删除它，这样可以更早地释放资源。

复制表

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

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

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

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

步骤一：

获取数据表的完整结构。

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 语句。

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 语句来实现。

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

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

使用 mtsqldump 命令

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

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

以下是一个简单的实例。

备份表数据：

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

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

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

还原到新的数据库：

mysql -u username -p new_dbname < old_table_dump.sql

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

元数据

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

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

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

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

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

查看所有数据库：

SHOW DATABASES;

选择数据库：

USE database_name;

查看数据库中的所有表：

SHOW TABLES;

查看表的结构：

DESC table_name;

查看表的索引：

SHOW INDEX FROM table_name;

查看表的创建语句：

SHOW CREATE TABLE table_name;

查看表的行数：

SELECT COUNT(*) FROM table_name;

查看列的信息：

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’ 分别是你的数据库名和表名。

查看外键信息：

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 表

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

SELECT * FROM information_schema.SCHEMATA;

TABLES 表

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

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';

COLUMNS 表

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

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

STATISTICS 表

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

SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

KEY_COLUMN_USAGE 表

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

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

REFERENTIAL_CONSTRAINTS 表

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

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

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

序列

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

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

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

CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

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

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

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

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

SELECT LAST_INSERT_ID();

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

SHOW TABLE STATUS LIKE 'example_table';

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

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

使用 AUTO_INCREMENT

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

获取 AUTO_INCREMENT 值

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

重置序列

如果你删除了数据表中的多条记录，并希望对剩下数据的 AUTO_INCREMENT 列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：

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

设置序列的开始值

一般情况下序列的开始值为 1，但如果你需要指定一个开始值 100，那我们可以通过以下语句来实现：

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

或者你也可以在表创建成功后，通过以下语句来实现：

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

处理重复数据

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

防止表中出现重复数据

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

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

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

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

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

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

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

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

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 索引，如下所示：

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

统计重复数据

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

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

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

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

过滤重复数据

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

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

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

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

删除重复数据

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

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（主键）这种简单的方法来删除表中的重复记录。方法如下：

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

SQL 注入

如果您通过网页获取用户输入的数据并将其插入一个 MySQL 数据库，那么就有可能发生 SQL 注入安全的问题。

所谓 SQL 注入，就是通过把 SQL 命令插入到 Web 表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的 SQL 命令。

MySQL 注入是指攻击者通过恶意构造的输入，成功地执行恶意的 SQL 查询，这通常发生在用户输入未经适当验证或转义的情况下，攻击者试图在输入中插入 SQL 代码，以执行意外的查询或破坏数据库。

我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

假设有一个登录系统，用户通过输入用户名和密码进行身份验证：

SELECT * FROM users WHERE username = 'input_username' AND password = 'input_password';

如果没有正确的输入验证和防范措施，攻击者可以输入类似于以下内容的用户名：

' OR '1'='1'; --

在这种情况下，SQL 查询会变成：

SELECT * FROM users WHERE username = '' OR '1'='1'; --' AND password = 'input_password';

这会使查询返回所有用户，因为 1=1 总是为真，注释符号 – 用于注释掉原始查询的其余部分，以确保语法正确。

防范 SQL 注入：

使用参数化查询或预编译语句：使用参数化查询（Prepared Statements）可以有效防止 SQL 注入，因为它们在执行查询之前将输入数据与查询语句分离。
输入验证和转义：对用户输入进行适当的验证，并使用合适的转义函数（如mysqli_real_escape_string）来处理输入，以防止恶意注入。
最小权限原则：给予数据库用户最小的权限，确保它们只能执行必要的操作，以降低潜在的损害。
使用ORM框架：使用对象关系映射（ORM）框架（如Hibernate、Sequelize）可以帮助抽象 SQL 查询，从而降低 SQL 注入的风险。
禁用错误消息显示：在生产环境中，禁用显示详细的错误消息，以防止攻击者获取有关数据库结构的敏感信息。

防止SQL注入：

1. 永远不要信任用户的输入 – 对用户的输入进行校验，可以通过正则表达式，或限制长度，对单引号和双等进行转义等。
2. 永远不要使用动态拼装 SQL – 可以使用参数化的 SQL 或者直接使用存储过程进行数据查询存取。
3. 永远不要使用管理员权限的数据库连接 – 为每个应用使用单独的权限有限的数据库连接。
4. 不要把机密信息直接存放 – 使用 hash 加密密码和敏感的信息。
5. 应用的异常信息应该给出尽可能少的提示 – 最好使用自定义的错误信息对原始错误信息进行包装。
6. SQL 注入的检测方法一般采取辅助软件或网站平台来检测 – 使用专门的漏洞扫描工具（如 sqlmap、Acunetix、Netsparker）对应用程序进行自动化的 SQL 注入检测。

like 语句中的注入

like 查询时，如果用户输入的值有 _ 和 %，则会出现这种情况：用户本来只是想查询 abcd_，查询结果中却有" abcd_"、“abcde”、“abcdf” 等等；用户要查询 “30%”（注：百分之三十）时也会出现问题。

导出数据

MySQL 中你可以使用 SELECT…INTO OUTFILE 语句来简单的导出数据到文本文件上。

使用 SELECT … INTO OUTFILE 语句导出数据

SELECT…INTO OUTFILE 是 MySQL 中用于将查询结果导出到文件的语法。

SELECT…INTO OUTFILE 允许你将查询的结果写入一个文本文件，基本的使用方法：

SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;

参数说明：

column1, column2, ...: 要选择的列。
'file_path': 指定输出文件的路径和名称。
your_table: 要查询的表。
your_conditions: 查询条件。

以下是一个简单的实例：

SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;

以上 SQL 语句中，我们从 users 表中选择了 id、name 和 email 列，并将结果写入了 /tmp/user_data.csv 文件。FIELDS TERMINATED BY ‘,’ 指定了列之间的分隔符（逗号），LINES TERMINATED BY ‘\n’ 指定了行之间的分隔符（换行符）。

需要注意的是，执行 SELECT…INTO OUTFILE 需要相应的权限，并且输出文件的目录需要是 MySQL 服务器可以写入的地方。

SELECT … INTO OUTFILE 语句有以下属性:

LOAD DATA INFILE是SELECT … INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT … INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。
SELECT…INTO OUTFILE 'file_name’形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
输出不能是一个已存在的文件。防止文件数据被篡改。
你需要有一个登陆服务器的账号来检索文件。否则 SELECT … INTO OUTFILE 不会起任何作用。
在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

mysqldump 导出表作为原始数据

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

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

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

mysqldump 基本的用法：

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 目录中：

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp RUNOOB runoob_tbl
password ******

mysqldump 实例

1. 导出整个数据库

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

mysqldump -u root -p mydatabase > mydatabase_backup.sql

2. 导出特定表

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

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

或：

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

3. 导出数据库结构

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

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

4. 导出压缩文件

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

mysqldump -u username -p password -h hostname database_name | gzip > output_file.sql.gz

导出 SQL 格式的数据

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

导入数据

mysql 命令导入

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

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

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

实例：

# mysql -uroot -p123456 < runoob.sql

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

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

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

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

source 命令导入

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

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

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

使用 LOAD DATA 导入数据

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

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) 也是可选的，不过，用户必须至少指定它们中的一个。

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

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

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

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

使用 mysqlimport 导入数据

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

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

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

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

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

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

$ 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('ab','',1) -- a SELECT SUBSTRING_INDEX('ab','',-1) -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('abcde','',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 主要有以下几种运算符：

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

算术运算符

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

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

1、加

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

2、减

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

3、乘

mysql> select 2*3;
+-----+
| 2*3 |
+-----+
|   6 |
+-----+

4、除

mysql> select 2/3;
+--------+
| 2/3    |
+--------+
| 0.6667 |
+--------+

5、商

mysql> select 10 DIV 4;
+----------+
| 10 DIV 4 |
+----------+
|        2 |
+----------+

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、等于

mysql> select 2=3;
+-----+
| 2=3 |
+-----+
|   0 |
+-----+


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

2、不等于

mysql> select 2<>3;
+------+
| 2<>3 |
+------+
|    1 |
+------+

3、安全等于

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

mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+


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

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

4、小于

mysql> select 2<3;
+-----+
| 2<3 |
+-----+
|   1 |
+-----+

5、小于等于

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

6、大于

mysql> select 2>3;
+-----+
| 2>3 |
+-----+
|   0 |
+-----+

7、大于等于

mysql> select 2>=3;
+------+
| 2>=3 |
+------+
|    0 |
+------+

8、BETWEEN

mysql> select 5 between 1 and 10;
+--------------------+
| 5 between 1 and 10 |
+--------------------+
|                  1 |
+--------------------+

9、IN

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

10、NOT IN

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

11、IS NULL

mysql> select null is NULL;
+--------------+
| null is NULL |
+--------------+
|            1 |
+--------------+

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

12、IS NOT NULL

mysql> select null IS NOT NULL;
+------------------+
| null IS NOT NULL |
+------------------+
|                0 |
+------------------+

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

13、LIKE

mysql> select '12345' like '12%';
+--------------------+
| '12345' like '12%' |
+--------------------+
|                  1 |
+--------------------+

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

14、REGEXP

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、与

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

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

2、或

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、非

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

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

4、异或

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、按位与

mysql> select 3&5;
+-----+
| 3&5 |
+-----+
|   1 |
+-----+

2、按位或

mysql> select 3|5;
+-----+
| 3|5 |
+-----+
|   7 |
+-----+

3、按位异或

mysql> select 3^5;
+-----+
| 3^5 |
+-----+
|   6 |
+-----+

4、按位取反

mysql> select ~18446744073709551612;
+-----------------------+
| ~18446744073709551612 |
+-----------------------+
|                     3 |
+-----------------------+

5、按位右移

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

6、按位左移

mysql> select 3<<1;
+------+
| 3<<1 |
+------+
|    6 |
+------+

运算符优先级

最低优先级为： :=。

最高优先级为： !、BINARY、 COLLATE。

命令大全

基础命令

操作	命令
连接到 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;`

简介

RDBMS 术语

数据库操作

创建数据库

切换数据库

删除数据库

表操作

查表

建表

给表添加注释

假如表已存在

删表

查看表结构

数据类型

数值类型

有无符号

日期和时间类型

字符串类型

枚举与集合类型

空间数据类型

字段属性

插入数据

查询数据

WHERE 子句

语法

简单实例

修改数据

语法

实例

删除数据

语法

实例

模糊查询

语法

实例

UNION

语法

实例

排序语句

语法

实例

分组语句

语法

连接语句

INNER JOIN

实例

LEFT JOIN

RIGHT JOIN

NULL 值处理

常见注意事项和技巧

正则表达式

正则表达式匹配的字符类

使用 REGEXP 进行模式匹配

使用 RLIKE 进行模式匹配

事务

事务控制语句：

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

ALTER 命令

添加列

修改列的数据类型

修改列名

删除列

添加 PRIMARY KEY

添加 FOREIGN KEY

修改表名

修改字段类型及名称

ALTER TABLE 对 Null 值和默认值的影响

修改字段默认值

索引

普通索引

创建索引

修改表结构(添加索引)

创建表的时候直接指定

删除索引

唯一索引

创建索引

修改表结构添加索引

创建表的时候直接指定

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

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