MySQL 入门

发表于 2019-12-09 更新于 2024-01-23 分类于 Python > Python教程 > Python基础本文字数： 34k 阅读时长 ≈ 56 分钟

MySQL 简介

MySQL 是一个流行的开源关系型数据库管理系统（RDBMS），它使用结构化查询语言（SQL）进行数据库管理。MySQL 是基于客户端 / 服务器模型的数据库解决方案，其中多个客户端可以同时连接到服务器上的数据库。

MySQL 特点

开源：MySQL 遵循 GNU 通用公共许可证（GPL），这意味着它可以免费使用和修改，只要所有衍生工作也是开源的。
跨平台：MySQL 可以在多种不同的操作系统上运行，包括但不限于 Linux、Windows 和 MacOS。
可扩展性与高性能：MySQL 被设计用来处理大量数据。它提供了多种存储引擎，允许用户根据需要选择最合适的一种。
安全性：提供了多层安全措施，包括基于主机的验证、密码加密和 SSL 支持。
复制和分区：MySQL 支持一主多从的复制配置，可以用于提高数据的可用性和读取性能。同时，它也支持分区表，有助于管理大型数据库。
大社区支持：作为一个受欢迎的数据库系统，MySQL 拥有一个庞大的开发者和用户社区。这为用户提供了丰富的学习资源和第三方工具。
兼容性：MySQL 支持大量的编程语言，如 PHP、Python、Java、C++ 等，并且可以集成到各种应用程序中。

MySQL 分类

版本分类
- 社区版（MySQL Community Server）: 这是 MySQL 的免费版，适用于开发者和技术爱好者，它是完全开源的。
- 企业版（MySQL Enterprise Edition）: 这是 MySQL 的商业版，包含了额外的高级功能、管理工具和技术支持。
存储引擎分类
- InnoDB: 提供事务安全（ACID 兼容）、行级锁定和外键约束，是 MySQL 默认的存储引擎。
- MyISAM: 支持全文搜索，但不支持事务安全。
- Memory: 将所有数据存储在内存中，用于临时表，速度非常快，但数据在数据库服务器重新启动时会丢失。
- Archive: 适用于只插入操作的表，支持高效的数据压缩。
- 其他: 如 Federated、CSV、Blackhole 等。
按照部署方式分类
- 本地部署: 在用户自己的服务器上安装和运行 MySQL。
- 云部署: 在云服务提供商的平台上作为服务运行，如 Amazon RDS for MySQL、Google Cloud SQL for MySQL 或 Azure Database for MySQL。
按照使用方式分类
- 作为独立数据库: 用于各种应用程序，负责数据存储和查询。
- 嵌入式数据库: 可以嵌入到其他软件中，MySQL 也提供了一个嵌入式库（libmysqld）。
按照许可证分类
- 开源许可证: 在 GPL 许可下，允许用户免费使用和修改源代码。
- 商业许可证: 适用于需要企业级支持的用户，通常需要支付费用。

SQL 分类

SQL（Structured Query Language）是一种用于管理关系型数据库管理系统的标准编程语言。它可以用来执行各种操作，包括数据查询、更新数据、管理数据库架构以及控制数据访问。SQL 语言的分类通常是根据其命令的功能来划分的，主要可以分为以下几类：

数据查询语言（DQL）
- SELECT: 用于查询数据库中的数据。
数据定义语言（DDL）
- CREATE: 用于创建新的数据库、表格或视图。
- ALTER: 用于修改数据库中已存在的结构，如添加或删除表格中的列。
- DROP: 用于删除数据库、表格、索引或视图。
- TRUNCATE: 用于删除表中的所有行，但不删除表本身。
数据操纵语言（DML）
- INSERT: 用于向表格中添加新的数据行。
- UPDATE: 用于修改表中的数据。
- DELETE: 用于从表中删除数据。
数据控制语言（DCL）
- GRANT: 用于给用户分配访问权限。
- REVOKE: 用于移除用户的访问权限。
事务控制语言（TCL）
- COMMIT: 用于将所有自上一个 COMMIT 或 ROLLBACK 命令以来进行的更改永久保存到数据库。
- ROLLBACK: 用于撤销到上一个 COMMIT 或 ROLLBACK 点的所有更改。
- SAVEPOINT: 用于在事务内设置一个保存点，可以回退到这个点而不是整个事务的起点。
- SET TRANSACTION: 用于指定事务的特性。

MySQL 安装

下载
- 访问 MySQL 官方网站下载页面。
- 选择 Windows 平台的 MySQL 安装程序。
安装
- 运行下载的 .msi 安装程序。
- 选择安装类型（通常有全功能安装、自定义安装等）。
- 安装过程中，设置根用户密码，并配置相关的选项，比如服务器类型、网络配置等。
- （可选）安装 MySQL Workbench 作为图形界面管理工具。
配置
- 启动 MySQL 配置向导，进行必要的设置，比如开机启动等。
- 设置环境变量，将 MySQL 的 bin 目录添加到系统的 PATH 变量中，以便可以在命令行中直接调用 MySQL 命令。
验证安装
- 打开命令提示符（CMD）。
- 输入 mysql -u root -p，然后输入你在安装时设置的密码。
- 如果能够进入 MySQL 命令行，说明安装成功。

下载
- 访问 MySQL 官方网站下载页面。
- 选择适合 macOS 的安装包。
安装
- 打开下载的 .dmg 安装程序包。
- 按照提示进行安装。
配置
- 安装完成后，通常需要在系统偏好设置中找到 MySQL，启动 MySQL 服务。
- （可选）在终端中设置环境变量。
验证安装
- 打开终端。
- 输入 mysql -u root -p，然后输入你在安装时设置的密码。
- 如果能够进入 MySQL 命令行，说明安装成功。

安装
- 打开终端。
- 更新包管理器的包索引列表：sudo apt update
- 安装 MySQL 服务器：sudo apt install mysql-server
配置
- 运行安全脚本：sudo mysql_secure_installation。
- 按照提示设置密码策略和 root 用户密码。
启动 MySQL 服务
- 启动 MySQL 服务：sudo systemctl start mysql
- 设置 MySQL 服务开机自启：sudo systemctl enable mysql
验证安装
- 输入 mysql -u root -p，然后输入你在配置过程中设置的密码。
- 如果能够进入 MySQL 命令行，说明安装成功。

数据类型

数字类型

数据类型	大小	用途	有符号范围	无符号范围
TINYINT	1 字节	小整数值	-128 到 127	0 到 255
SMALLINT	2 字节	较小的整数	-32,768 到 32,767	0 到 65,535
MEDIUMINT	3 字节	中等大小的整数	-8,388,608 到 8,388,607	0 到 16,777,215
INT/INTEGER	4 字节	标准的整数	-2,147,483,648 到 2,147,483,647	0 到 4,294,967,295
BIGINT	8 字节	较大的整数	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807	0 到 18,446,744,073,709,551,615
FLOAT	4 字节	单精度浮点数	-3.402823466E+38 到 -1.175494351E-38, 0 以及 1.175494351E-38 到 3.402823466E+38	同有符号
DOUBLE	8 字节	双精度浮点数	双于 FLOAT	同有符号
DECIMAL/NUMERIC	可变（最多 65 字节）	定点数，用于存储精确的数值，常用于金融领域	-10^38 +1 到 10^38 -1	0 到 10^38

FLOAT 和 DOUBLE 是浮点类型，用于存储近似值。它们可能因为舍入误差而不存储精确的数值。
DECIMAL 类型用于存储精确的小数，适用于需要高精度计算的场景，如财务计算。
对于 FLOAT 和 DOUBLE，精度可以指定，例如 DOUBLE(10,2) 可以存储最多 10 位数字的小数，其中 2 位为小数部分。
DECIMAL 的精度和标度可以指定，例如 DECIMAL(5,2) 用于存储最多 5 位数（包括 2 位小数）的数字。
无符号（UNSIGNED）类型的数值范围是从 0 开始的，即不包括负数。
有符号类型可以存储负数和正数。

日期和时间类型

数据类型	格式	用途	范围
DATE	`YYYY-MM-DD`	用于表示日期	`1000-01-01` 到 `9999-12-31`
TIME	`HH:MM:SS`	用于表示一天中的时间或持续时间	`-838:59:59` 到 `838:59:59`
DATETIME	`YYYY-MM-DD HH:MM:SS`	用于表示日期和时间，不包括时区	`1000-01-01 00:00:00` 到 `9999-12-31 23:59:59`
TIMESTAMP	`YYYY-MM-DD HH:MM:SS`	用于表示日期和时间，包括时区，通常用于记录事件发生的时间点	`1970-01-01 00:00:01` UTC 到 `2038-01-19 03:14:07` UTC
YEAR	`YYYY`	用于表示年份	`1901` 到 `2155`

DATE 类型只包含日期，不含时间。
TIME 类型可以表示一天之内的时间或时间间隔，它的范围允许表示超过 24 小时的值。
DATETIME 类型用于表示没有时区信息的日期和时间，适合需要存储时间点但不涉及时区转换的场景。
TIMESTAMP 类型的值是基于 UTC 的，并且在插入和检索时会自动根据系统时区进行转换。TIMESTAMP 的范围较小，但特别适合记录创建、更新时间戳。
YEAR 类型可以单独存储年份信息，它的存储空间小，适合存储只需要年份的日期值。

字符串类型

数据类型	大小	用途和特征
CHAR	0 到 255 字符	固定长度字符串。存储长度固定的字符串时效率较高。
VARCHAR	0 到 65535 字符	变长字符串。用于存储长度可变的字符串。长度范围取决于最大行大小和字符集。
TINYTEXT	最大 255 字符	小文本字符串。对于非常短的文本或状态代码，优于 VARCHAR。
TEXT	最大 65,535 字符	长文本数据。适用于存储大量文本，如文章或评论。
MEDIUMTEXT	最大 16,777,215 字符	更长的文本数据。用于需要比 TEXT 更大容量的场景。
LONGTEXT	最大 4,294,967,295 字符	极大文本数据。适用于需要存储大量文本的情况，如书籍内容。
BINARY	0 到 255 字节	类似 CHAR，存储二进制字符串。
VARBINARY	0 到 65535 字节	类似 VARCHAR，存储可变长度的二进制字符串。
TINYBLOB	最大 255 字节	小块二进制数据。用于存储非常小的二进制数据。
BLOB	最大 65,535 字节	二进制大对象。用于存储二进制数据，如图片或音频文件。
MEDIUMBLOB	最大 16,777,215 字节	更大块的二进制数据。用于存储比 BLOB 更大的二进制文件。
LONGBLOB	最大 4,294,967,295 字节	极大块二进制数据。适用于需要存储很大的二进制文件，如视频数据。
ENUM	1 到 65535 个值	枚举类型。用于存储少量预定义的字符串值。
SET	1 到 64 个值	集合类型。允许存储零个或多个预定义的字符串值。

对于 CHAR 和 VARCHAR，实际长度限制还依赖于字符集的最大字节长度。例如，使用 utf8mb4 字符集时，最大长度比上述提到的要小。
CHAR 类型是长度固定的，会用空格填充到指定长度。当检索时，末尾的空格会被移除。
VARCHAR 类型是长度可变的，只存储必要的字符并使用额外的字节记录长度。
文本类型 (TEXT) 和二进制大对象类型 (BLOB) 是用于存储大量数据的，它们的检索和更新可能比 CHAR 和 VARCHAR 类型慢。
ENUM 类型限制数据必须是列出的值之一，SET 类型允许组合列出的值。
二进制类型 (BINARY, VARBINARY, BLOB 等) 的数据会按照字节值存储，不同于文本类型，它们不依赖字符集。

空间类型

数据类型	描述
GEOMETRY	任意类型的几何数据的超类型。
POINT	表示一个单独的点。
LINESTRING	表示一系列点的线条，点之间按顺序连接。
POLYGON	表示一个封闭的区域。
MULTIPOINT	表示多个点。
MULTILINESTRING	表示多个线条。
MULTIPOLYGON	表示多个多边形。
GEOMETRYCOLLECTION	表示一个几何对象的集合，集合内可以是任意类型的几何对象。

空间数据类型被用于存储和处理空间特征，如地图中的位置、区域、线路等。
这些类型通常与 GIS 软件和服务一起使用，以提供地理空间分析和数据管理的功能。
MySQL 也提供了一系列空间相关的函数，用于对空间数据进行操作和查询，例如计算两点之间的距离、确定一个点是否在多边形内等。
使用空间数据需要 MySQL 表支持空间数据类型，这意味着表必须使用支持空间列的存储引擎，如 InnoDB。
空间数据类型的存储基于 WKB（Well-Known Binary）格式，而其文本表现形式基于 WKT（Well-Known Text）格式。

JSON 类型

数据类型	存储需求	用途和特性
JSON	变长存储	用于存储 JSON 文档。JSON 列以优化的格式存储，使其快速读取。支持自动验证 JSON 文档，确保存储的数据是有效的 JSON 格式。

JSON 数据类型是一个二进制的格式，它使得 JSON 文档的读写更加高效。
MySQL 提供了一系列函数来创建、解析和修改 JSON 文档，并且可以直接在 SQL 查询中使用。
JSON 类型的索引利用虚拟列技术，这意味着可以对 JSON 列中的属性创建索引以提高查询性能。
通过使用 JSON 路径表达式，可以直接在 JSON 文档内部寻址和修改数据。

字段修饰

NOT NULL：此修饰符指定字段的值不能为 NULL，每个记录必须包含该字段的值。
DEFAULT：为字段定义一个默认值。如果插入记录时没有指定该字段的值，MySQL 将自动插入默认值。
AUTO_INCREMENT：对于整数字段，该修饰符用于在新记录插入表时自动生成一个唯一的数字。它通常用于主键字段。
PRIMARY KEY：此修饰符用于定义表的主键。一个表只能有一个主键，主键列的值必须唯一，不能为 NULL。
UNIQUE：确保字段值在整个表中是唯一的。
INDEX：为字段创建一个索引，以加速查询和排序操作。
FULLTEXT：为了进行全文搜索，可以对 TEXT 类型的列指定 FULLTEXT 索引。
COMMENT：为字段添加注释，以说明字段的内容或用途。
CHARACTER SET：指定字段使用的字符集，如 utf8, latin1 等。
COLLATE：指定字段的字符集排序规则。
FOREIGN KEY：用于创建外键关联，指定当前字段引用另一个表的主键字段，用于保持数据的引用完整性。
UNSIGNED：通常情况下，整数字段在 MySQL 中可以存储正值和负值。标记为 UNSIGNED 的字段属性只能存储正整数和零。这意味着该字段的存储范围向正数方向扩大了。
ZEROFILL：当字段被标记为 ZEROFILL，MySQL 会自动为该字段添加 UNSIGNED 属性，并用零填充字段宽度的剩余空间。

示例

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(50) NOT NULL COMMENT '用户密码',
    email VARCHAR(100) DEFAULT 'user@example.com' COMMENT '用户邮箱',
    signup_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
);

在这个 users 表中，id 字段被设置为自动递增的主键，username 和 password 字段被设置为必填项，username 还必须是唯一的。email 字段有一个默认值，而 signup_date 字段的默认值是当前的时间戳。

字符集及存储引擎

字符集（Character Set）

MySQL 中的字符集决定了数据库如何存储文本数据。字符集是一组符号和编码。每个字符集都支持一个或多个语言。举例来说，latin1 字符集支持西欧语言，而 utf8 字符集（或其扩展 utf8mb4）支持所有 Unicode 字符，包括中文、日文、表情符号等。
在创建数据库或表的时候，可以指定所使用的字符集。如果没有明确指定，将使用服务器级或数据库级的默认设置。

以下是如何为数据库和表指定字符集的示例：

-- 创建数据库时指定默认字符集
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建表时指定字符集
CREATE TABLE mytable (
    id INT PRIMARY KEY,
    text VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

存储引擎（Storage Engine）
- InnoDB：提供了事务安全（ACID 兼容）、行级锁定和外键约束。它是 MySQL 默认的存储引擎，适合大多数应用场景。
- MyISAM：早期的默认存储引擎，它提供了全文搜索索引和较好的读性能，但不支持事务处理。
- Memory：使用内存存储数据，提供快速的访问速度，但数据在数据库重启后不会被保留。
- Archive：适用于只插入（INSERT）操作的大量数据存储和归档。
- 以下是如何为表指定存储引擎的示例：
  1
  2
  3
  4
  5
  -- 创建表时指定存储引擎为InnoDB
  CREATE TABLE mytable (
  id INT PRIMARY KEY,
  value VARCHAR(100)
  ) ENGINE = InnoDB;
  选择合适的字符集可以有效地支持国际化，避免乱码的发生。而合适的存储引擎则可以根据应用的需求优化性能和功能。通常，InnoDB 是一个均衡的选择，因为它既支持事务也有很好的性能表现。在特定情况下，比如需要高速读取且数据安全性要求不高时，可以选择 MyISAM 等其他存储引擎。

索引

简介
MySQL 中的索引是一个能够加快数据检索速度的数据库结构。索引可以看作是数据库表的目录，它帮助 MySQL 快速定位和检索数据，而不需要逐行扫描整个表。
分类
- INDEX（普通索引）
  - 最基本的索引，没有任何约束，其主要目的是提高查询效率。
  - 同一列可以有多条记录有相同的索引值。
  - 可以在一个表中创建多个普通索引。
- UNIQUE（唯一索引）
  - 与普通索引类似，但它要求索引列的值必须唯一，即同一列不允许有重复的值。
  - 如果尝试插入重复值，数据库会返回错误。
  - 唯一索引可以保证数据的唯一性，同时也加快数据检索的速度。
  - NULL 值在唯一索引列中通常被视为不相等，所以你可以有多个 NULL 值。
- PRIMARY KEY（主键索引）
  - 是一种特殊的唯一索引，一张表只能有一个主键。
  - 主键索引不仅保证其值的唯一性，还标识了数据表中的每一行。
  - 主键列不能有 NULL 值。
  - 通常，主键是在创建表时定义的，也可以在表创建后添加。
- FULLTEXT（全文索引）
  - 用于进行全文搜索，可以在 CHAR、VARCHAR 或 TEXT 类型的列上创建。
  - 主要由 MyISAM 和 InnoDB 支持。
- B-Tree 索引
  - 是最常见的索引类型，适用于全值匹配、匹配列的最左前缀、匹配范围值以及 ORDER BY 操作。
  - 几乎所有 MySQL 存储引擎都支持 B-Tree 索引。
- HASH 索引
  - 基于哈希表实现，它提供了快速的索引查找性能，但只支持精确匹配查找，不支持基于范围的查询（例如：>、<、BETWEEN、LIKE 等）。
  - 主要由 MEMORY 存储引擎支持。
- R-Tree（空间）索引
  - 用于空间数据类型，例如，用于存储地理数据的 GEOMETRY 字段。
  - 主要由 MyISAM 支持。
- Composite（复合）索引
  - 包含多个列，适用于查询条件涉及多个列时。

示例一

CREATE TABLE mytable (
    id INT AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (id), -- 设置主键索引
    UNIQUE INDEX uq_username (username), -- 设置唯一索引
    INDEX idx_email (email) -- 设置普通索引
);

在这个例子中：

id 列被定义为主键，它会自动创建一个唯一索引，并且是自动递增的；表中的每一行都可以通过唯一的 id 来区分。
username 列有一个唯一索引，保证所有用户名都是独一无二的。
email 列有一个普通索引，可以加快查询邮箱地址的速度，但不要求邮箱地址唯一。

示例二

-- 创建单列索引
CREATE INDEX idx_column ON table_name (column_name);

-- 创建复合索引
CREATE INDEX idx_composite ON table_name (column1, column2);

-- 创建全文索引
CREATE FULLTEXT INDEX ft_idx ON table_name (text_column);

-- 创建时指定索引类型，如使用B-Tree
CREATE INDEX idx_column USING BTREE ON table_name (column_name);

注意事项
- 创建索引会占用额外的磁盘空间。
- 索引可以加快查询速度，但是它们会稍微降低插入（INSERT）、更新（UPDATE）和删除（DELETE）操作的速度，因为索引也需要在这些操作后进行更新。
- 在设计索引时，应当考虑查询模式、表的大小以及数据的变化频率。理想情况下，应该只对经常用于搜索条件的列、排序的列或连接的列创建索引。过度索引和选择不恰当的索引都可能导致性能下降。

数据定义语言 (DDL)

简介
- 数据定义语言（DDL）是 SQL 语言的一个子集，用于定义和修改数据库结构。
- 请注意，以下示例是在假设你使用的数据库系统遵循标准 SQL 语法的情况下提供的。不同的数据库系统（如 MySQL、PostgreSQL、Oracle、SQL Server 等）在创建存储过程和视图时可能会有自己特定的语法要求。特别是 SQL 的结构化查询语言可能因数据库系统而异，例如，在 Oracle 中拼接字符串可能需要使用 || 而在 SQL Server 中使用 +。对于复杂对象如存储过程和视图，确保检查你所使用的数据库系统的官方文档，以获取正确的语法指导。

CREATE

用于创建数据库中的新对象，如数据库、表格、索引、视图、存储过程等。
创建数据库
1
CREATE DATABASE ExampleDatabase;

创建表格

USE ExampleDatabase;

-- 首先选择数据库
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE,
    Position VARCHAR(50)
);

创建索引

1 2	-- 在Employees表的LastName字段上创建一个索引 CREATE INDEX idx_lastname ON Employees (LastName);

创建视图

1
2
3

-- 创建一个视图，用于简化对员工全名的查询
CREATE VIEW FullNames AS
SELECT EmployeeID, FirstName + ' ' + LastName AS FullName FROM Employees;

创建存储过程

-- 创建一个存储过程，用于插入新员工记录
CREATE PROCEDURE AddEmployee (
    IN p_FirstName VARCHAR(50),
    IN p_LastName VARCHAR(50),
    IN p_BirthDate DATE,
    IN p_Position VARCHAR(50)
) BEGIN
INSERT INTO
    Employees (FirstName, LastName, BirthDate, Position)
VALUES
    (p_FirstName, p_LastName, p_BirthDate, p_Position);

END;

ALTER

用于修改数据库中现有对象的结构，如添加、删除或修改表格中的列，或修改其它对象属性。
添加列
- 在末尾添加列（这是大多数数据库系统的默认行为）
  1
  2
  3
  -- 在Employees表的最后添加一个名为Email的新列。
  ALTER TABLE Employees
  ADD Email VARCHAR(255);
- 在开头添加列（以 MySQL 为例）
  1
  2
  3
  -- 在Employees表的最开始添加一个名为Email的新列。
  ALTER TABLE Employees
  ADD Email VARCHAR(255) FIRST;
- 在指定位置添加列（以 MySQL 为例，在 FirstName 列后添加）
  1
  2
  3
  -- 在FirstName列之后添加一个名为Email的新列。
  ALTER TABLE Employees
  ADD Email VARCHAR(255) AFTER FirstName;
- 请注意，FIRST 和 AFTER 关键字是 MySQL 特有的，不是 SQL 标准的一部分，因此这些关键字可能不适用于其他数据库系统。例如，在 SQL Server 中，要改变列的顺序，通常需要创建一个新表，将数据复制到新表中，然后重命名新表。因此，在使用这些特定的关键字时，你应该根据所使用的数据库管理系统来调整 SQL 语句。在进行结构更改之前，请确保备份你的数据。
删除列
1
2
ALTER TABLE Employees
DROP COLUMN Email;
这个命令会从 Employees 表中删除名为 Email 的列。
修改列
- 修改列的数据类型
  1
  2
  ALTER TABLE Employees
  MODIFY COLUMN BirthDate DATETIME;
  这个命令将 Employees 表中的 BirthDate 列的数据类型从 DATE 修改为 DATETIME。
- 重命名列（这个命令在不同的数据库系统中可能有所不同）
  1
  2
  ALTER TABLE Employees
  CHANGE COLUMN FirstName GivenName VARCHAR(50);
  这个命令将 Employees 表中的 FirstName 列重命名为 GivenName。

修改其他对象属性

修改索引（通常是删除后重新创建，因为大多数数据库不允许直接修改索引）
1
2
3
4
5
-- 删除索引
DROP INDEX idx_lastname ON Employees;

-- 重新创建索引，假设我们现在要在FirstName上建立索引
CREATE INDEX idx_firstname ON Employees (FirstName);
首先删除 Employees 表上的 idx_lastname 索引，然后在 FirstName 列上创建一个新的索引。
修改视图（如果想要修改视图的结构，一般需要重新创建视图）
1
2
3
-- MySQL和PostgreSQL
CREATE OR REPLACE VIEW FullNames AS
SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
这个命令删除并重新创建了 FullNames 视图，以包含拼接了 FirstName 和 LastName 的 FullName 列。

修改存储过程（修改存储过程通常需要用到特定的语法或者是通过删除旧的再创建新的）

-- 在MySQL中修改存储过程
DROP PROCEDURE IF EXISTS AddEmployee;
DELIMITER //
CREATE PROCEDURE AddEmployee (
    IN p_FirstName VARCHAR(50),
    IN p_LastName VARCHAR(50),
    IN p_BirthDate DATE,
    IN p_Position VARCHAR(50),
    IN p_Email VARCHAR(255)
)
BEGIN
    INSERT INTO Employees (FirstName, LastName, BirthDate, Position, Email)
    VALUES (p_FirstName, p_LastName, p_BirthDate, p_Position, p_Email);
END;
//
DELIMITER ;

这个例子首先删除 AddEmployee 存储过程（如果它存在的话），然后重新创建它并添加了一个新的参数 p_Email。

DROP
- 概述
  - 用于从数据库中完全删除对象，如表格、索引、视图等。
  - 使用 DROP 语句时要特别小心，因为一旦执行，被删除的对象和所有相关数据将无法恢复。因此，通常在执行 DROP 操作之前，建议先进行数据备份。此外，DROP 语句在不同的数据库系统中可能存在语法差异，下述使用 IF EXISTS 的语法是为了避免在对象不存在时执行语句产生错误，但并不是所有的数据库系统都支持这一语法。在执行 DROP 语句之前，请确保查询你所使用的数据库系统的文档。
- 删除表格
  1
  DROP TABLE IF EXISTS Employees;
  这个命令将删除名为 Employees 的表。IF EXISTS 是一个通用的 SQL 扩展，它会先检查表是否存在，存在则删除，避免了因为表不存在而导致的错误。
- 删除视图
  1
  DROP VIEW IF EXISTS FullNames;
  这个命令会删除名为 FullNames 的视图（如果存在的话）。
- 删除索引
  1
  ALTER TABLE Employees DROP INDEX idx_lastname;
  或者
  1
  DROP INDEX idx_lastname ON Employees;
  这两个命令都会删除 Employees 表上名为 idx_lastname 的索引。
- 删除数据库
  1
  DROP DATABASE IF EXISTS ExampleDatabase;
  这个命令将删除名为 ExampleDatabase 的数据库（如果存在的话）。
- 删除存储过程
  1
  DROP PROCEDURE IF EXISTS AddEmployee;
  这个命令会删除名为 AddEmployee 的存储过程（如果存在的话）。
TRUNCATE
- 用于删除表内的所有数据行，但保留表结构本身。与 DELETE 语句相比，TRUNCATE 通常更快，因为它不逐行删除数据，也不记录每条记录的删除动作。TRUNCATE 命令还会重置表的任何自增计数器。
- 以下是使用 TRUNCATE 语句的示例：
  1
  TRUNCATE TABLE Employees;
- 执行这个命令后，Employees 表中的所有数据会被删除，但表结构、其索引、约束和表本身仍然存在。如果 Employees 表有一个自增的主键，那么这个主键的计数也会被重置（在大多数数据库系统中）。
- 需要注意的是，TRUNCATE 是不可逆的，一旦执行，被删除的数据不可恢复。另外，在某些数据库系统中，如 MySQL，TRUNCATE 命令对于有外键引用的表是不允许的，因为它可能会违反引用完整性。在这种情况下，你可能需要先删除或修改引用该表的外键约束，或者使用 DELETE 语句代替。
- 在执行 TRUNCATE 之前，请确保你有足够的权限来对数据库中的表进行这样的操作，并且确认你不需要表中的数据，或者已经做好了数据备份。
COMMENT
- 这允许你在创建或修改表及其列时添加注释。这些注释通常用于说明表或列的用途，为其他开发者或数据库维护者提供信息。
- 创建表时添加注释
  1
  2
  3
  4
  5
  6
  7
  CREATE TABLE Employees (
  ID INT AUTO_INCREMENT PRIMARY KEY,
  FirstName VARCHAR(100),
  LastName VARCHAR(100),
  DepartmentID INT,
  COMMENT 'A table to store employee data'
  );
  这个例子在创建 Employees 表时添加了一个注释，说明这个表用于存储员工数据。
- 给新列添加注释
  1
  2
  ALTER TABLE Employees
  ADD COLUMN BirthDate DATE COMMENT 'The birth date of the employee';
  这个例子在 Employees 表中添加了一个新的列 BirthDate，并添加了解释这个列意义的注释。
- 修改现有列的注释
  1
  2
  ALTER TABLE Employees
  CHANGE COLUMN DepartmentID DepartmentID INT COMMENT 'Reference to the department table';
  这个例子修改了 Employees 表中 DepartmentID 列的注释。
- 创建带注释的索引（注释是 MySQL 5.5 及更高版本的功能）
  1
  CREATE INDEX idx_lastname ON Employees (LastName) COMMENT 'Index to improve search performance on last name';
  这个例子创建了一个名为 idx_lastname 的索引，并添加了一个注释来说明其用途。
RENAME
- 概述
  - 用于更改数据库对象的名称，例如表名或列名（不同数据库系统中可能略有不同）。
  - 使用 RENAME TABLE 语句时，需要注意的是，该操作需要对旧表拥有 ALTER 权限和对新表拥有 CREATE 权限。此外，RENAME 操作不能在有锁定或活动事务的表上执行。在对生产数据库执行 RENAME 操作之前，建议先在测试环境验证，并确保选择了合适的时机，以避免对正常服务造成干扰。
- 重命名单个表
  1
  RENAME TABLE old_table_name TO new_table_name;
  这个命令将 old_table_name 重命名为 new_table_name。
- 同时重命名多个表
  1
  2
  RENAME TABLE old_table_name1 TO new_table_name1,
  old_table_name2 TO new_table_name2;
  这个命令会将 old_table_name1 改名为 new_table_name1 ，同时将 old_table_name2 改名为 new_table_name2。
- 重命名表并更改库
  1
  RENAME TABLE current_database.old_table_name TO new_database.new_table_name;
  如果想要将表 old_table_name 从 current_database 移动到 new_database，同时重命名为 new_table_name，可以使用上述命令。
- 重命名带前缀的表
  1
  RENAME TABLE `old_prefix_%` TO `new_prefix_%`;
  请注意这不会自动应用于所有带有 "old_prefix_" 的表。这个命令显示了如何书写一个通配符模式，但实际上 MySQL 不支持在 RENAME 操作中直接使用通配符。如果需要批量重命名带有特定前缀的表，你可能需要编写一个脚本来生成对应的 RENAME 语句。

数据操作语言 (DML)

INSERT

准备工作
- 假设我们有一个 students 表，该表有四个列：id（主键）、name、age 和 grade。
- 首先，这是 students 表的一个可能的结构：
  1
  2
  3
  4
  5
  6
  CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  grade VARCHAR(50)
  );

插入一行数据，指定所有列（除了自增主键 id ）

1 2	INSERT INTO students (name, age, grade) VALUES ('Alice Smith', 20, 'A');

插入一行数据，但是只指定部分列（比如，假设 grade 有一个默认值）
1
2
INSERT INTO students (name, age)
VALUES ('Bob Johnson', 23);

一次性插入多行数据

INSERT INTO
    students (name, age, grade)
VALUES
    ('Chris Green', 22, 'B'),
    ('Diana Red', 19, 'A'),
    ('Evan Brown', 21, 'C');

插入 SELECT 语句的结果，假设我们还有另一个表 transfer_students，我们想把其中符合条件的学生信息插入到 students 表中
1
2
3
4
INSERT INTO students (name, age, grade)
SELECT name, age, grade
FROM transfer_students
WHERE transfer_date > '2023-01-01';
插入默认值，假设所有列都有默认值
1
INSERT INTO students DEFAULT VALUES;

UPDATE
- 准备工作
  假设有一个名为 employees 的表，它有 id（主键）、name、position 和 salary 列。
- 更新单个记录：如果我们知道要更新记录的 id，下面的语句将特定 id 的 employee 的 salary 更新为 70000。
  1
  2
  3
  UPDATE employees
  SET salary = 70000
  WHERE id = 4;
- 更新多个记录：如果我们需要给所有的 Software Developer 加薪 10%，可以这样写
  1
  2
  3
  UPDATE employees
  SET salary = salary * 1.1
  WHERE position = 'Software Developer';
- 更新多个列：如果我们要同时更新员工的职位和工资，可以这样写
  1
  2
  3
  UPDATE employees
  SET position = 'Senior Software Developer', salary = 90000
  WHERE id = 4;
- 有条件的更新：如果我们只想更新工资低于某个特定金额的员工记录，可以这样写
  1
  2
  3
  UPDATE employees
  SET salary = 75000
  WHERE salary < 50000;
- 更新并使用表达式：我们可以在 SET 子句中使用表达式和函数来计算新值
  1
  2
  3
  UPDATE employees
  SET salary = salary + (salary * 0.05)
  WHERE position = 'Sales Manager';
- 限制更新数量：我们可以使用 LIMIT 来限制更新的记录数，例如，我们只想更新 2 个 Intern 的工资
  1
  2
  3
  4
  UPDATE employees
  SET salary = 30000
  WHERE position = 'Intern'
  LIMIT 2;
- 注意事项
  在使用 UPDATE 语句时要小心，特别是不要忘记 WHERE 子句，否则你可能会不经意间更新到整个表。在对生产环境的数据库进行操作前，建议在备份或非生产环境中测试你的 UPDATE 语句，以确保它们执行的动作正是你所期望的。
DELETE
- 删除指定记录：如果我们要删除 id 为 5 的学生，可以使用以下语句
  1
  2
  DELETE FROM students
  WHERE id = 5;
- 基于条件删除多个记录：如果我们希望删除所有未达到合格成绩 C 的学生，我们可以这样做
  1
  2
  DELETE FROM students
  WHERE grade NOT IN ('A', 'B', 'C');
- 删除所有记录：如果我们要删除 students 表中的所有记录，可以这样写（请谨慎使用，因为这会删除所有数据）
  1
  DELETE FROM students;
  或者使用更安全的操作，仅当表中确实存在记录时
  1
  2
  DELETE FROM students
  WHERE 1=1;
  注意：在没有 WHERE 子句的情况下，DELETE 语句会删除表中的所有数据。这是一个危险的操作，因为所有数据都将丢失。在一些情况下，如果你只是想清空表中的数据，而不想删除表本身，使用 TRUNCATE TABLE students 语句可能是更好的选择，因为它通常更快，并且会重置任何自动增加的计数器。
- 限制删除数量：如果我们只想删除两个年龄小于 20 岁的学生，可以使用 LIMIT
  1
  2
  3
  DELETE FROM students
  WHERE age < 20
  LIMIT 2;
- 注意：
  - DELETE 语句用于删除表中的记录。它可以删除一行、多行或表中的所有行。使用 DELETE 时必须谨慎，尤其要注意 WHERE 子句，以避免不小心删除了过多数据。
  - 使用 DELETE 操作时，一定要确保 WHERE 子句的条件是准确的，以避免不必要的数据丢失。在对生产环境中的数据执行删除操作之前，强烈建议在测试环境中验证你的 DELETE 语句，并确保有数据备份。

数据查询语言 (DQL)

基础查询
查询 students 表中所有学生的所有信息
1
SELECT * FROM students;
选择特定列
仅查询 students 表中学生的姓名和年龄
1
SELECT name, age FROM students;
排除重复记录
查询 students 表中所有不重复的名字
1
SELECT DISTINCT name FROM students;

条件查询

查询 students 表中年龄大于 20 岁学生的信息

1	SELECT * FROM students WHERE age > 20;

运算符	描述	示例
=	等于	`SELECT * FROM students WHERE age = 18;`
<> 或！=	不等于	`SELECT * FROM students WHERE age <> 18;`
>	大于	`SELECT * FROM students WHERE age > 18;`
<	小于	`SELECT * FROM students WHERE age < 18;`
>=	大于等于	`SELECT * FROM students WHERE age >= 18;`
<=	小于等于	`SELECT * FROM students WHERE age <= 18;`
BETWEEN	在某个范围之内	`SELECT * FROM students WHERE age BETWEEN 18 AND 22;`
LIKE	模糊匹配	`SELECT * FROM students WHERE name LIKE 'A%';`
IN	在指定的选项中	`SELECT * FROM students WHERE class IN ('A', 'B', 'C');`
IS NULL	是 NULL 值	`SELECT * FROM students WHERE address IS NULL;`
AND	多个条件同时成立	`SELECT * FROM students WHERE age > 18 AND gender = 'Female';`
OR	至少一个条件成立	`SELECT * FROM students WHERE age < 18 OR age > 22;`
NOT	条件不成立	`SELECT * FROM students WHERE NOT age < 18;`

排序结果

按照年龄对 students 表中的学生进行排序

1 2	SELECT * FROM students ORDER BY age ASC; -- 升序排列 SELECT * FROM students ORDER BY age DESC; -- 降序排列

限制返回的结果数量
仅返回前 5 个查询结果
1
SELECT * FROM students LIMIT 5;

计算列

使用 AS 为计算结果指定别名

1	SELECT name, age * 2 AS double_age FROM students;

聚合函数

计算 students 表中学生的平均年龄

1	SELECT AVG(age) FROM students;

聚合函数	描述	示例
`COUNT()`	计数列中的行数	`SELECT COUNT(*) FROM students;`
`SUM()`	计算列中数值的总和	`SELECT SUM(score) FROM students;`
`AVG()`	计算列中数值的平均值	`SELECT AVG(score) FROM students;`
`MAX()`	返回列中的最大值	`SELECT MAX(score) FROM students;`
`MIN()`	返回列中的最小值	`SELECT MIN(score) FROM students;`
`GROUP_CONCAT()`	将列值连接为一个字符串	`SELECT GROUP_CONCAT(name) FROM students;`

分组结果

按年龄分组学生，并计算每组的人数

1	SELECT age, COUNT(*) AS number_of_students FROM students GROUP BY age;

使用 HAVING 子句过滤分组

选择年龄大于 20 岁的学生分组

1	SELECT age, COUNT(*) AS number_of_students FROM students GROUP BY age HAVING age > 20;

连接查询

结合 students 表和 courses 表，查询所有学生及其所选课程的信息

1
2
3

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

子查询

查询至少选了一门课程的所有学生的信息

1
2
3

SELECT *
FROM students
WHERE id IN (SELECT student_id FROM courses);

复合条件查询
查询 students 表中名字含有 'a' 并且年龄大于 18 岁的学生
1
SELECT * FROM students WHERE name LIKE '%a%' AND age > 18;

使用别名

为表和列指定别名以简化查询

1
2
3

SELECT s.name AS student_name, c.course_name AS course
FROM students AS s
INNER JOIN courses AS c ON s.id = c.student_id;

数据控制语言 (DCL)

创建用户
创建用户通常是由数据库管理员进行的操作，它不是 DCL 的一部分，而是属于数据库安全管理的范畴。创建用户可以通过 CREATE USER 语句来实现。下面是一个简单的例子：
1
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
在这个例子中：
- 'newuser' 是你想要创建的用户名。
- 'localhost' 表明这个用户只能从本地主机连接到 MySQL 服务器。如果你想允许用户从任何主机连接，可以使用通配符 '%' 替换 'localhost'。
- IDENTIFIED BY 'password' 设置登录数据库的密码（请将 'password' 替换为一个强密码）。
授权用户 (GRANT)
- 赋予用户名为 user1 的用户对 students 表的 SELECT（查询）权限：
  1
  GRANT SELECT ON database_name.students TO 'user1'@'localhost';
- 赋予用户名为 user2 的用户对 database_name 数据库中所有表的所有权限（SELECT, INSERT, UPDATE 等）：
  1
  GRANT ALL PRIVILEGES ON database_name.* TO 'user2'@'localhost';
查看授权
- 查看当前登录用户的权限：
  1
  SHOW GRANTS;
- 查看指定用户的权限，需要将 'username'@'host' 替换为实际的用户名和主机名：
  1
  SHOW GRANTS FOR 'username'@'host';
- 这里 'username' 是你想要查看权限的用户名，'host' 定义了用户可以从哪里连接到数据库服务器。例如，如果有一个用户名为 john，允许从本地主机连接，你会使用：
  1
  SHOW GRANTS FOR 'john'@'localhost';
- 如果你想要查看所有用户的权限，可能需要查询 mysql 数据库中的 user 表，可以使用如下查询：
  1
  SELECT * FROM mysql.user;
  这将返回一个包含所有用户及其权限的列表，但请注意，这将显示所有的权限设置，格式并不如 SHOW GRANTS 那样清晰易读。
刷新权限
- 当你使用 GRANT、REVOKE 或 CREATE USER 等语句更改了用户的权限之后，你可能需要执行 FLUSH PRIVILEGES 语句。这个语句会清空 MySQL 的权限缓存，并重新从权限表中加载最新的权限设置。
- 以下是 FLUSH PRIVILEGES 语句的标准用法：
  1
  FLUSH PRIVILEGES;
- 在大多数情况下，当使用 GRANT 或 REVOKE 语句时，你不需要手动执行 FLUSH PRIVILEGES，因为这些语句会自动刷新权限。但如果你直接修改了权限相关的表，如通过对 mysql.user 或其他相关权限表进行 INSERT、UPDATE 或 DELETE 操作，那么你需要执行 FLUSH PRIVILEGES 来确保这些更改生效。
- 请注意，FLUSH PRIVILEGES 是一个强大的语句，它会中断所有当前的连接，并重新加载权限。因此，在高负载的生产环境中，你需要谨慎使用它，以免影响正在运行的服务。通常，直接使用 GRANT 和 REVOKE 语句是更好的选择，因为它们在修改权限时会自动处理权限的重新加载。
取消授权 (REVOKE)
- 撤销用户名为 user1 的用户对 students 表的 SELECT 权限：
  1
  REVOKE SELECT ON database_name.students FROM 'user1'@'localhost';
- 撤销用户名为 user2 的用户对 database_name 数据库所有表的所有权限：
  1
  REVOKE ALL PRIVILEGES ON database_name.* FROM 'user2'@'localhost';
删除用户
- 以下是删除用户账户的标准语法：
  1
  DROP USER 'username'@'host';
- 这里，你需要将 'username' 替换为要删除的用户名，将 'host' 替换为用户的主机名。例如，如果你想删除用户名为 user1，且从本地主机访问数据库的用户，你应该执行：
  1
  DROP USER 'user1'@'localhost';
- 在执行 DROP USER 命令后，建议运行 FLUSH PRIVILEGES; 命令来立即重新加载权限表，确保所有更改立即生效。
  1
  FLUSH PRIVILEGES;
- 然而，从 MySQL 5.7.2 版本开始，当你使用 DROP USER 语句时，MySQL 会自动执行权限表的刷新操作，所以你不再需要手动执行 FLUSH PRIVILEGES;。
- 请确保在执行 DROP USER 时谨慎操作，并且只有在确信不再需要该用户访问数据库时才执行删除操作。此外，在删除用户前，最好检查该用户是否拥有任何对象，如表或者存储过程，并确定这些对象的所有权或权限不会受到删除操作的影响。
root 用户远程登录
- 在 MySQL 中，默认情况下，root 用户仅设置为从 localhost 登录，这意味着你不能直接从远程机器登录到数据库服务器的 root 账户。如果需要允许 root 用户远程登录，你需要更改用户的主机设置并确保授予了适当的权限。
- 请记住，允许 root 用户或任何用户从远程登录可能引入安全风险，因为它使得数据库更容易受到外部攻击。只有在必要并且采取了适当的安全措施的情况下，才应该配置远程访问。使用 VPN，SSH 隧道或其他安全方法可以帮助减少风险。此外，密钥认证、IP 地址限制和强密码策略也是保护远程数据库连接的重要措施。
- 以下是为 root 用户允许远程登录的步骤：
  1. 登录到 MySQL 服务器的本地 Shell
  2. 使用管理员权限登录到 MySQL
    1
    mysql -u root -p
  3. 一旦登录，你可以创建一个具有远程访问权限的新 root 用户或更改现有 root 用户的主机部分。为了安全起见，建议创建一个新的管理员用户用于远程访问，而不是直接允许 root 用户远程访问。
    如果你确实需要允许 root 远程登录，执行如下命令：
    1
    2
    3
    USE mysql;
    UPDATE user SET Host='%' WHERE User='root' AND Host='localhost';
    FLUSH PRIVILEGES;
    这里 % 代表任何主机，为了安全性，可以将 % 替换为具体的远程 IP 地址或者 IP 范围。
  4. 如果创建新用户，可以这样做：
    1
    2
    3
    CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
    替换 admin 和 password 为你所选择的用户名和密码。
  5. 完成之后，退出 MySQL：
    1
    EXIT;
  6. 如果 MySQL 服务器有防火墙设置，确保防火墙允许远程从你希望连接的主机上的端口 3306（MySQL 的默认端口）访问。
  7. 修改 my.cnf 或 mysqld.cnf（通常位于 /etc/mysql/ ），确保 MySQL 服务绑定的地址不是 127.0.0.1。你可以将其更改为以下内容以允许远程连接：
    1
    2
    [mysqld]
    bind-address = 0.0.0.0
    或者，如果不想允许所有接口，可以绑定到具体的服务器 IP 地址。
  8. 重启 MySQL 服务以使更改生效：
    1
    sudo service mysql restart
    或者
    1
    sudo systemctl restart mysql

事务控制语言 (TCL)

说明
MySQL 中的事务控制语言（Transaction Control Language，TCL）包括用于管理事务的命令，这些命令可以让你控制事务的提交和回滚。事务是一组被视为单个工作单元的 SQL 语句，它们要么全部执行，要么全部不执行，这能保证数据库的一致性。
BEGIN 或 START TRANSACTION
开始一个新的事务。
1
START TRANSACTION; -- 或者 BEGIN;
COMMIT
提交当前事务，使自上次 COMMIT 或 ROLLBACK 以来进行的所有更改成为永久更改。
1
COMMIT;
ROLLBACK
回滚当前事务，撤销自上次 COMMIT 或 ROLLBACK 以来进行的所有更改。
1
ROLLBACK;
SAVEPOINT
在事务中创建一个保存点，允许你将事务回滚到该点而不是完全回滚。
1
SAVEPOINT savepoint_name;
ROLLBACK TO SAVEPOINT
回滚到先前设置的特定保存点。
1
ROLLBACK TO savepoint_name;
RELEASE SAVEPOINT
删除一个事务中的保存点。
1
RELEASE SAVEPOINT savepoint_name;

简单示例

START TRANSACTION; -- 开始事务

INSERT INTO accounts (user_id, amount) VALUES (101, -100.00); -- 假设账户ID为101的用户取款100元
INSERT INTO accounts (user_id, amount) VALUES (102, 100.00); -- 假设账户ID为102的用户存款100元

-- 检查上述操作是否是我们所希望的
-- 如果是，那么提交这个事务，使得更改永久保存
COMMIT;

-- 如果操作出错，我们希望撤销这些更改
ROLLBACK;

在上述例子中，两个 INSERT 语句被视为一个事务。如果两个操作都成功完成，事务将通过 COMMIT 命令提交，改动就会被写入数据库。如果其中一个操作失败或者不是期望的结果，整个事务将被 ROLLBACK 命令回滚，所有的改动都不会影响数据库。这确保了数据库的一致性和数据完整性。在实际应用中，你会根据具体情况在代码中添加必要的错误检查逻辑，来决定何时提交或回滚事务。

禁用自动提交

在 MySQL 中，SET autocommit=0; 是用来禁用自动提交的。在默认情况下，autocommit 选项被设置为 1，意味着每条 SQL 语句都被视作一个单独的事务，它们会在执行后立即被提交。
当你执行 SET autocommit=0; 时，MySQL 将不再自动提交每个独立的事务。这意味着你必须显式地调用 COMMIT; 来提交你的更改，或者调用 ROLLBACK; 来撤销它们，直到你再次设置 autocommit 为 1 或者重新启动连接（会话结束时 autocommit 会恢复默认值）。

以下是如何使用 autocommit 的例子：

-- 禁用自动提交
SET autocommit=0;

-- 执行一系列的更改
INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
UPDATE table_name SET column1 = 'new_value' WHERE column2 = 'some_value';

-- 现在，由于 autocommit 被设置为 0，以上更改不会自动提交
-- 如果你满意更改，手动提交事务
COMMIT;

-- 否则，你可以回滚事务
ROLLBACK;

-- 当完成事务处理后，重新启用自动提交（如果需要的话）
SET autocommit=1;

关闭 autocommit 可以让你有更细粒度的控制，特别是在进行一系列需要作为一个整体提交的操作时。不过，需要注意的是在长时间保持 autocommit=0 而未提交的情况下，可能会导致锁定资源，并影响数据库性能。所以常规操作是在一个事务处理完毕后马上提交或者回滚，并根据需要重置 autocommit 状态。

多表联合查询

假设我们有两个表

students 表

+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | Alice |  22 |
|  2 | Bob   |  23 |
|  3 | Carol |  22 |
+----+-------+-----+

courses 表

+----+-------------+----------+
| id | course_name | student_id |
+----+-------------+----------+
|  1 | Math        |        1 |
|  2 | Science     |        2 |
|  3 | Math        |        2 |
|  4 | History     |        3 |
+----+-------------+----------+

在这个例子中，student_id 字段在 courses 表中是外键，它引用 students 表中的 id 字段。

内连接（INNER JOIN）
获取那些既在 students 表中也在 courses 表中登记了课程的学生列表：
1
2
3
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;
这将返回所有有匹配 student_id 的记录。
隐式内连接（Implicit Inner Join）
如果我们想要使用隐式内连接来找出哪些学生选了什么课程，我们可以通过在 WHERE 子句中指定连接条件来实现。
1
2
3
SELECT students.name, courses.course_name
FROM students, courses
WHERE students.id = courses.student_id;
这个查询会返回每个学生及其对应课程的列表。结果可能如下：
1
2
3
4
5
6
7
8
+-------+-------------+
| name | course_name |
+-------+-------------+
| Alice | Math |
| Bob | Science |
| Bob | Math |
| Carol | History |
+-------+-------------+
这种查询方式是传统的 SQL 写法，在现代 SQL 实践中，显式的 JOIN 语法（如 INNER JOIN ）更加推荐，因为它更加清晰，并且当查询变得复杂时，显式连接能提供更好的可读性和维护性。
左外连接（LEFT JOIN）
获取所有学生及其可能注册的任何课程：
1
2
3
SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id;
即使学生没有注册任何课程，也会返回学生的信息，在这种情况下 course_name 将是 NULL。
右外连接（RIGHT JOIN）
获取所有课程及其可能的注册学生：
1
2
3
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.id = courses.student_id;
即使课程没有学生注册，也会返回课程的信息，在这种情况下 name 将是 NULL。

全连接（FULL JOIN）

MySQL 实际上不支持 FULL JOIN，但可以通过合并左连接和右连接的结果来模拟：

SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id
UNION
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.id = courses.student_id;

这将返回 students 和 courses 表中所有的记录。如果没有匹配的学生或课程，相应的列将显示为 NULL。

自连接（SELF JOIN）
有时，你可能需要将表与其自身连接。假设我们想找到具有相同年龄的学生对：
1
2
3
SELECT a.name AS student_name_a, b.name AS student_name_b
FROM students a
INNER JOIN students b ON a.age = b.age AND a.id != b.id;
这将返回一个表，其中包括所有年龄相同但不是同一个人的学生对。

多表联合操作

子查询
- 简介
  - 在 MySQL 中，子查询是嵌套在另一个查询内部的 SQL 查询。子查询可以用在 SELECT、INSERT、UPDATE、DELETE 语句中，以及在 WHERE 和 HAVING 子句中。子查询可以返回单个值、单列、多列甚至多行。
  - 子查询在执行查询时非常强大和灵活，但它们也可能影响查询的性能，特别是在处理大量数据时。因此，在使用子查询前，应当考虑是否有更有效的替代方法来实现相同的结果。
- 单行单列子查询
  这种子查询返回单一的值，可以在 WHERE 子句中使用，比如查找选了数学课程的所有学生的姓名：
  1
  2
  3
  4
  5
  6
  7
  SELECT name
  FROM students
  WHERE id = (
  SELECT student_id
  FROM courses
  WHERE course_name = 'Math'
  );
- 多行单列子查询
  当子查询需要返回多行数据时，可以使用 IN 操作符，比如查找选了任何课程的所有学生的姓名：
  1
  2
  3
  4
  5
  6
  SELECT name
  FROM students
  WHERE id IN (
  SELECT student_id
  FROM courses
  );
- 多行多列子查询
  当子查询返回多个列且可能是多行时，它通常与 EXISTS、IN 或者比较操作符配合使用，比如查找至少选择了一个课程的学生姓名和年龄：
  1
  2
  3
  4
  5
  6
  7
  SELECT name, age
  FROM students
  WHERE EXISTS (
  SELECT 1
  FROM courses
  WHERE students.id = courses.student_id
  );
- 子查询作为计算字段
  你也可以在 SELECT 列表中使用子查询来返回计算字段，比如返回每个学生选了多少门课程：
  1
  2
  3
  4
  5
  6
  SELECT name, (
  SELECT COUNT(*)
  FROM courses
  WHERE students.id = courses.student_id
  ) AS courses_count
  FROM students;
- 子查询用于 FROM 子句
  子查询还可以用于 FROM 子句，以允许你对子查询结果进行进一步的操作，比如计算每个年龄的学生数量：
  1
  2
  3
  4
  5
  6
  SELECT age, COUNT(*) AS students_count
  FROM (
  SELECT age
  FROM students
  ) AS age_groups
  GROUP BY age;
联合更新
- 在 MySQL 中，"联合更新" 指的是将两个或多个表连接起来，并一起更新它们的行。这通常通过 UPDATE 语句结合 JOIN 操作来完成。这样，你可以在一个操作中更新多个表中的相关记录。
- 以下是一个使用 INNER JOIN 来执行联合更新的例子，假设我们要更新 courses 表中的 course_name 为 "Mathematics"，对于那些年龄大于 22 岁的学生：
  1
  2
  3
  4
  UPDATE courses
  INNER JOIN students ON courses.student_id = students.id
  SET courses.course_name = 'Mathematics'
  WHERE students.age > 22;
- 在这个例子中，courses 表和 students 表通过 student_id 和 id 字段联合起来，然后更新 courses 表中的 course_name 字段，但仅限于那些在 students 表中其 age 字段值大于 22 的记录。
- 请注意，在执行这样的操作时，应确保 JOIN 条件正确无误，并且最好在进行重要的更新前备份数据，以免不小心更新了错误的行。联合更新是一个强大的工具，但如果没有谨慎使用，可能会导致意想不到的结果。

清空数据

DELETE 语句
用于从表中删除所有行，但不会删除表本身。这种方式会记录每个被删除的行，并且可以通过事务回滚。如果表很大，这可能是一个非常慢的操作，并且会大量占用日志空间。
1
DELETE FROM table_name;
如果你想清空表并重置自增的 ID，你需要额外执行：
1
ALTER TABLE table_name AUTO_INCREMENT = 1;
TRUNCATE 语句
这是快速清空表的推荐方式，它会删除所有行并重置表的自增计数器，但不会删除表本身。这种方式不会记录每个被删除的行，也就是说，它不能被回滚，而且不会触发 DELETE 触发器。
1
TRUNCATE TABLE table_name;
DROP TABLE 语句
这个语句会删除整个表及其数据。如果你只是想删除数据，这通常不是推荐的做法，因为它还会删除表结构、索引和权限。如果你确定要删除整个表，可以使用：
1
DROP TABLE table_name;
如果之后需要，你还得重新创建这个表。
说明
- 使用 TRUNCATE 通常比 DELETE 要快，因为它不生成任何日志记录，所以在只想清空数据而不是删除表结构的情况下，TRUNCATE 是更好的选择。然而，如果表被引用在外键约束中，TRUNCATE 可能会失败，这时你必须使用 DELETE。
- 在执行这些操作之前，请确保你真的想清除所有数据，因为这些操作通常是不可逆的（尤其是在没有开启事务的情况下）。在正式环境中，执行这些操作前最好先做好数据备份。

外键索引

在 MySQL 中，外键是一种约束，它用于保持表之间的数据完整性和引用关系。一个表中的外键列指向另一个表的主键列。对于外键列建立索引对于数据库性能是非常重要的，特别是当执行联结查询、更新和删除操作时。
例如，假设有两个表：students 和 enrollments。students 表有学生的信息，每个学生都有一个独一无二的 student_id 作为主键。enrollments 表记录了学生的选课信息，其中包含外键 student_id，指向 students 表的主键。

下面是如何定义这两个表及其外键的例子：

CREATE TABLE students (
    student_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (student_id)
);

CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT,
    student_id INT,
    course_name VARCHAR(100),
    PRIMARY KEY (enrollment_id),
    CONSTRAINT fk_student
    FOREIGN KEY (student_id)
    REFERENCES students(student_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

在这个例子中，enrollments 表创建了一个名为 fk_student 的外键，它引用了 students 表的 student_id 字段。ON DELETE CASCADE 和 ON UPDATE CASCADE 选项意味着当对应的 students 表中的记录被删除或更新时，enrollments 表中的相应记录也会自动被删除或更新。

外键索引的创建通常是隐含的，尤其是在 InnoDB 存储引擎中，这意味着当你创建外键约束时，MySQL 会自动为外键列创建索引。在上面的 enrollments 表中，student_id 会自动被索引。如果没有自动创建，或者你使用的是不自动创建外键索引的存储引擎，你可以手动为外键列创建索引，如下：
1
CREATE INDEX idx_student_id ON enrollments(student_id);
这个索引能提高执行涉及外键列的查询的速度，特别是当联结 students 和 enrollments 表时。索引使得数据库能够快速找到 enrollments 表中与 students 表中特定 student_id 相关联的行。如果没有索引，数据库必须扫描整个 enrollments 表来找到匹配的行，这在大型数据库中会非常低效。
约束行为
- ON DELETE CASCADE: 当父表中的记录被删除时，子表中的匹配记录也会被删除。
- ON DELETE SET NULL: 当父表中的记录被删除时，子表中的外键值设置为 NULL（前提是没有设置 NOT NULL 约束）。
- ON DELETE RESTRICT: 阻止删除父表中的记录，如果该记录在子表中被引用。
- ON DELETE NO ACTION: 实际上和 RESTRICT 类似，但是根据 SQL 标准，在检查约束之前那个事件不立即执行。
- ON UPDATE CASCADE: 当父表中的记录被更新时，子表中的匹配记录也会跟着更新。
- ON UPDATE SET NULL: 当父表中的记录被更新时，子表中的外键值设置为 NULL。
- ON UPDATE RESTRICT 和 ON UPDATE NO ACTION: 阻止更新父表中的记录，如果该记录在子表中被引用。

Python 操作 MySQL

简介
在 Python 中操作 MySQL 数据库，你可以使用 mysql-connector-python 库，这是 MySQL 官方的 Python 连接库。另外一个流行的库是 PyMySQL，它是一个纯 Python 写的 MySQL 客户端。

安装

1	pip install mysql-connector-python

或者：

1	pip install PyMySQL

示例

`mysql-connector-python`

import mysql.connector

# 创建数据库连接
db_connection = mysql.connector.connect(
    host="localhost",       # 数据库服务器
    user="yourusername",    # 数据库用户名
    passwd="yourpassword",  # 数据库密码
    database="mydatabase"   # 数据库名
)

# 创建一个cursor对象来执行操作
cursor = db_connection.cursor()

# 创建一个新表
cursor.execute(
    "CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

# 插入数据
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
cursor.execute(sql, val)

# 提交更改，否则无法保存新记录
db_connection.commit()

print(cursor.rowcount, "record inserted.")

# 查询数据
cursor.execute("SELECT * FROM customers")
myresult = cursor.fetchall()  # fetchall() 获取所有记录

for x in myresult:
    print(x)

# 关闭连接
cursor.close()
db_connection.close()

`PyMySQL`

示例 1

import pymysql

# 配置数据库连接参数
host_name = "localhost"
db_user = "yourusername"
db_password = "yourpassword"
db_name = "mydatabase"

# 创建连接
connection = pymysql.connect(
    host=host_name, user=db_user, password=db_password, database=db_name)

# 创建cursor对象
cursor = connection.cursor()

# 创建一个新表
cursor.execute(
    "CREATE TABLE IF NOT EXISTS people (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)")

# 插入数据
insert_query = "INSERT INTO people (name, age) VALUES (%s, %s)"
values = ("John Doe", 30)
cursor.execute(insert_query, values)

# 提交到数据库执行
connection.commit()

print(f"{cursor.rowcount} record(s) inserted.")

# 查询数据
select_query = "SELECT * FROM people"
cursor.execute(select_query)

# 获取所有记录
records = cursor.fetchall()
for record in records:
    print(record)

# 更新数据
update_query = "UPDATE people SET age = %s WHERE name = %s"
new_values = (31, "John Doe")
cursor.execute(update_query, new_values)

# 提交到数据库执行
connection.commit()

print(f"{cursor.rowcount} record(s) updated.")

# 删除数据
delete_query = "DELETE FROM people WHERE name = %s"
delete_name = ("John Doe", )
cursor.execute(delete_query, delete_name)

# 提交到数据库执行
connection.commit()

print(f"{cursor.rowcount} record(s) deleted.")

# 关闭cursor
cursor.close()

# 关闭连接
connection.close()

示例 2

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;

import pymysql.cursors

# 连接数据库
connection = pymysql.connect(host='localhost',  # 数据库服务器地址
                             user='user',       # 数据库登录用户名
                             password='passwd',  # 数据库登录密码
                             database='db',     # 要操作的数据库名
                             charset='utf8mb4',  # 数据库编码
                             cursorclass=pymysql.cursors.DictCursor)  # 使用字典类型游标

# 使用with语句自动管理资源，确保使用完毕后正确关闭连接
with connection:
    # 再次使用with语句获取游标对象
    with connection.cursor() as cursor:
        # SQL 插入语句，向`users`表中插入数据
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))  # 执行SQL语句并传递参数

    # 因为PyMySQL默认不是自动提交，所以需要手动调用commit()保存更改
    connection.commit()

    # 再次使用with语句和cursor()方法来获取游标对象，用于执行查询
    with connection.cursor() as cursor:
        # SQL查询语句，从`users`表中根据电子邮箱读取用户信息
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))  # 执行SQL语句并传递参数
        result = cursor.fetchone()  # 获取查询结果中的第一条记录
        print(result)  # 打印查询结果

数据库的备份与恢复

备份数据库
- 备份整个数据库：
  1
  mysqldump -u username -p mydatabase > mydatabase_backup.sql
  这里的 -u 后面跟的是数据库用户名， -p 表示需要输入密码（执行后会提示输入密码），mydatabase 是要备份的数据库名称。该命令会将 mydatabase 数据库的所有数据导出到一个名为 mydatabase_backup.sql 的文件中。
- 备份数据库中的特定表：
  1
  mysqldump -u username -p mydatabase table1 table2 > tables_backup.sql
- 备份多个数据库：
  1
  mysqldump -u username -p --databases database1 database2 > multiple_databases_backup.sql
- 备份所有数据库：
  1
  mysqldump -u username -p --all-databases > all_databases_backup.sql
- 在执行 mysqldump 命令时，可以添加额外的参数来调整备份，例如 --opt（用于优化备份过程）或者 --no-data（只导出结构而不导出数据）。
恢复数据库
- 恢复数据库：
  1
  mysql -u username -p mydatabase < mydatabase_backup.sql
  这里，你需要提供 MySQL 用户名和密码（会在执行后提示输入），而 mydatabase 是你想要恢复数据到的数据库名称。mydatabase_backup.sql 是包含备份数据的 SQL 文件。
- 恢复多个数据库
  如果备份文件包括了多个数据库的备份，并且希望全部恢复，可以不在 mysql 命令中指定数据库名：
  1
  mysql -u username -p < all_databases_backup.sql
  在这里， all_databases_backup.sql 是包含所有数据库备份的文件。
注意事项
- 确保在备份和恢复时，用户拥有足够的权限来访问指定的数据库和表。
- 对于生产环境，考虑使用 --single-transaction 参数来对备份进行一致性的处理（特别是当备份 InnoDB 类型的表时）。
- 如果备份非常大，可能需要确保有足够的磁盘空间来存储备份文件。
- 对于恢复操作，确保在进行恢复之前，数据库处于预期状态。如有需要，可先创建一个新的数据库再将数据导入到其中。
- 在进行重要的备份和恢复操作之前，建议先在非生产环境进行测试，以确保一切按预期工作。

友情链接

Navicat 16 激活教程

MySQL 简介

MySQL 特点

MySQL 分类

版本分类

存储引擎分类

按照部署方式分类

按照使用方式分类

按照许可证分类

SQL 分类

MySQL 安装

数据类型

数字类型

日期和时间类型

字符串类型

空间类型

JSON 类型

字段修饰

字符集及存储引擎

字符集（Character Set）

存储引擎（Storage Engine）

索引

简介

分类

INDEX（普通索引）

UNIQUE（唯一索引）

PRIMARY KEY（主键索引）

FULLTEXT（全文索引）

B-Tree 索引

HASH 索引

R-Tree（空间）索引

Composite（复合）索引

示例一

示例二

注意事项

数据定义语言 (DDL)

简介

CREATE

ALTER

DROP

TRUNCATE

COMMENT

RENAME

数据操作语言 (DML)

INSERT

UPDATE

DELETE

数据查询语言 (DQL)

基础查询

选择特定列

排除重复记录

条件查询

排序结果

限制返回的结果数量

计算列

聚合函数

分组结果

使用 HAVING 子句过滤分组

连接查询

子查询

复合条件查询

使用别名

数据控制语言 (DCL)

创建用户

授权用户 (GRANT)

查看授权

刷新权限

取消授权 (REVOKE)

删除用户

root 用户远程登录

事务控制语言 (TCL)

说明

BEGIN 或 START TRANSACTION

COMMIT

ROLLBACK

SAVEPOINT

ROLLBACK TO SAVEPOINT

RELEASE SAVEPOINT

简单示例

禁用自动提交

多表联合查询

`BEGIN` 或 `START TRANSACTION`

`COMMIT`

`ROLLBACK`

`SAVEPOINT`

`ROLLBACK TO SAVEPOINT`

`RELEASE SAVEPOINT`

子查询用于 `FROM` 子句

`DELETE` 语句

`TRUNCATE` 语句

`DROP TABLE` 语句

`mysql-connector-python`

`PyMySQL`