MySQL从入门到精通实操

第一章 数据库基础

1.  数据库概念

1.1.  关系型数据库

关系型数据库（Relational Database Management System，RDBMS）是一种以表格形式存储数据的数据库管理系统。它使用结构化查询语言（Structured Query Language，SQL）作为查询和操作数据的主要工具。关系型数据库的核心思想是将数据组织成一个或多个表，每个表都由行（记录）和列（字段）组成，并使用键（如主键、外键）来建立表与表之间的关系。

关系型数据库具有以下特点：

1. 结构化：数据以表格形式存储，表格中的数据具有清晰的结构和关系。

2. 规范化：关系型数据库遵循规范化原则，以减少数据冗余和提高数据一致性。

3. ACID属性：关系型数据库支持事务（Transaction）处理，具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）的特性。

4. SQL语言：关系型数据库使用SQL作为查询和操作数据的标准语言，具有较高的灵活性和可扩展性。

5. 成熟稳定的技术：关系型数据库技术经过数十年的发展，已经非常成熟和稳定，广泛应用于各种场景。

常见的关系型数据库管理系统有：

●  MySQL

●  Oracle Database

●  Microsoft SQL Server

●  PostgreSQL

●  IBM DB2

关系型数据库广泛应用于企业信息管理系统、电子商务平台、数据分析等领域。

1.2.  MySQL简介

MySQL是一个开源的关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，后被Sun Microsystems公司收购，最后被Oracle公司收购。MySQL遵循开源许可协议，可以免费使用、修改和分发。它是一个跨平台的数据库系统，支持多种操作系统，如Linux、Windows、Mac OS等。

MySQL具有以下特点：

1. 开源免费：MySQL是一个开源软件，可以免费使用、修改和分发，降低了企业的成本。

2. 性能卓越：MySQL具有高性能、高可用性和高可扩展性，可以支持大量的数据存储和访问。

3. 易于使用：MySQL使用SQL作为查询和操作数据的标准语言，易于学习和使用。同时，它提供了丰富的命令行工具和图形化管理工具，方便用户进行数据库管理和维护。

4. 社区支持：MySQL拥有庞大的开发者社区，提供了大量的文档、教程和第三方库，方便用户学习和解决问题。

5. 广泛的兼容性：MySQL支持多种硬件平台、操作系统和编程语言，可以与其他软件无缝集成。

MySQL广泛应用于Web开发、企业信息管理系统、数据仓库、云计算等领域。许多知名的网站和应用，如Facebook、Google、Amazon等，都使用MySQL作为其数据库系统。

2.  数据库安装

2.1.  Linux安装数据库

2.1.1.  选择要安装的版本

访问 https://downloads.mysql.com/archives/community/，选择版本，如下图：

这里选择Red Hat Enterprise Linux 7/Oracle Linux 7 (x86,64-bit) / 8.2.0的版本。

2.1.2.  下载安装包（RPM包）

[root@localhost mysql]# cd /usr/local && mkdir -p mysql
[root@localhost mysql]# cd /mysql/
[root@localhost mysql]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.2.0-1.el7.x86_64.rpm-bundle.tar

注意：如果没有安装wget，请先进行安装

[root@localhost mysql]# yum -y install wget

2.1.3.  解压安装包（RPM包）

[root@localhost mysql]# tar -xvf mysql-8.2.0-1.el7.x86_64.rpm-bundle.tar
[root@localhost mysql]# ll
总用量 2058652
-rw-r--r--. 1 root root  1054023680 10月 13 15:54 mysql-8.2.0-1.el7.x86_64.rpm-bundle.tar
-rw-r--r--. 1 7155 31415   16860000 10月 13 15:47 mysql-community-client-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    3617000 10月 13 15:47 mysql-community-client-plugins-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415     687048 10月 13 15:47 mysql-community-common-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  549736120 10月 13 15:47 mysql-community-debuginfo-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    1963108 10月 13 15:47 mysql-community-devel-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    4217572 10月 13 15:47 mysql-community-embedded-compat-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    2344832 10月 13 15:47 mysql-community-icu-data-files-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    1583436 10月 13 15:47 mysql-community-libs-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415     685776 10月 13 15:47 mysql-community-libs-compat-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   67735456 10月 13 15:48 mysql-community-server-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   26028560 10月 13 15:48 mysql-community-server-debug-8.2.0-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  378554428 10月 13 15:49 mysql-community-test-8.2.0-1.el7.x86_64.rpm

2.1.4.  安装MySQL

● 安装前检查

[root@localhost mysql]# rpm -qa | grep mysql
[root@localhost mysql]# rpm -qa | grep mariadb

● 如果有安装过其他版本，需要先进行卸载

[root@localhost mysql]#  rpm -e --nodeps    mysql包名
[root@localhost mysql]#  rpm -e --nodeps    mariadb包名

● 安装需要按以下顺序进行：

rpm -ivh mysql-community-common-8.2.0-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.2.0-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.2.0-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.2.0-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.2.0-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-8.2.0-1.el7.x86_64.rpm

2.1.5.  启动MySQL

#初始化
mysqld --initialize --user=mysql

#启动服务
systemctl start mysqld

#查看初始密码，kJTfmi#k8e&7 即为初始密码
[root@localhost mysql]# cat /var/log/mysqld.log  | grep password
2024-03-18T08:14:13.697262Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: kJTfmi#k8e&7

2.1.6.  设置远程访问

● 添加防火墙端口

MySQL默认使用3306端口，需要在防火墙中进行添加。

#添加端口
[root@localhost mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
#重启防火墙
[root@localhost mysql]# firewall-cmd --reload
#查看端口
[root@localhost mysql]# firewall-cmd --list-ports
3306/tcp

● 设置用户远程访问

使用root账号进行登录（密码为前面获取的密码：kJTfmi#k8e&7，该密码由MySQL随机生成）

[root@localhost mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.2.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use mysql;
mysql> update user set host = '%' where user ='root';
mysql> flush privileges;

● 修改MySQL的默认密码

mysql> alter user 'root'@'%' identified by '123456';

2.2.  Windows安装数据库

在 Windows 上安装 MySQL，可以从MySQL(https://dev.mysql.com/downloads/) 官网下载安装包，然后按照提示进行安装。

2.2.1.  下载MySQL

下图中，点击MySQL Installer for Windows，然后选择离线安装的版本（最新版本：8.0.36）。

2.2.2.  安装MySQL

1)  双击安装包，并下一步

2)  经多次下一步后，出现下图时，选择Server only，并下一步

3)  下一步

4)  配置信息

5)  认证方式

6)  设置登录密码

我设置的密码是：Root123@456，需记住此密码，后续登录要用。

7)  设置Windows服务方式启动

8)  开始安装

至此，MySQL安装完成。

2.2.3.  命令行登录MySQL

1)  开始菜单，找到命令行客户端

2)  输入登录密码

成功登录后，能正常执行SQL，如上图。

3.  数据库连接

3.1.  命令行连接

使用以下命令连接 MySQL 数据库：

mysql -u 用户名 -p 密码 -h 主机名

例如：

mysql -u root -p 123456 -h localhost

3.2.  Navicat 连接

打开 Navicat，点击 “连接”，输入主机名、用户名、密码，即可连接数据库。具体如下：

保存后，可用打开连接，并进行相应的操作。

3.3.  Java 连接

使用 JDBC 连接 MySQL，需要在 Java 代码中加载驱动，然后使用 DriverManager.getConnection() 方法连接数据库。具体的连接根据Java框架，视具体情况而定。

3.4.  Python 连接

使用 pymysql 库连接 MySQL，需要在 Python 代码中导入库，然后使用 pymysql.connect() 方法连接数据库，具体连接可查看相应的资料。

4.  数据类型

MySQL 数据类型非常丰富，涵盖各种数值类型、字符串类型、日期时间类型、二进制类型以及其他特殊类型。下面是一些主要的数据类型的分类和描述：

1.  数值类型：

a.  整数类型：TINYINT, SMALLINT, MEDIUMINT, INT 或 INTEGER（分别对应1字节、2字节、3字节、4字节整数，用于存储较小到较大的整数值）

i.  BIGINT（8字节整数，用于存储非常大的整数值）

ii.  这些类型都可以使用 UNSIGNED 关键字表示无符号类型，也可以指定显示宽度，例如 INT(5)，但显示宽度并不影响存储的实际数值大小。

b.  浮点数类型：FLOAT 和 DOUBLE（用于存储浮点数，可以指定精度，例如 FLOAT(10,2) 表示总共10位数，其中2位小数）

i.  DECIMAL 或 NUMERIC（用于存储高精度的小数，必须指定精度和小数位数，例如 DECIMAL(10,2)）

2.  字符串类型：

a.  定长字符串：CHAR(size)，固定长度的字符串，剩余空间会被填充空格。

b.  变长字符串：VARCHAR(size)，可变长度的字符串，节省空间。

c.  文本类型：TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT，用于存储较长的文本内容。

d.  二进制类型：BINARY(size) 和 VARBINARY(size)，用于存储二进制数据，类似于字符类型，但是存储的是字节而不是字符。

e.  Blob类型：TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB，用于存储大块的二进制数据，如图像、文件等。

3.  日期和时间类型：

a.  DATE：仅存储日期，格式为 'YYYY-MM-DD'。

b.  TIME：仅存储时间，格式为 'HH:MM:SS'。

c.  DATETIME 或 TIMESTAMP：存储日期和时间的组合，格式为 'YYYY-MM-DD HH:MM:SS'。

d.  YEAR：存储年份。

4.  枚举和集合类型：

a.  ENUM(value1, value2, ...): 存储从预定义枚举值中选择的一个值。

b.  SET('value1', 'value2', ...): 存储一组预定义值的集合，允许在一个字段中选择多个值。

5.  其他类型：

a.  BOOLEAN 或 BOOL：在MySQL中实际上是一个 TINYINT(1) 的别名，用于存储布尔值（0或1）。

b.  JSON：从MySQL 5.7开始支持，用于存储JSON格式的数据。

c.  BIT：存储二进制位值。

每种数据类型在存储空间、可接受的值范围和操作特性上都有所不同，选择合适的数据类型有助于优化存储和查询性能。

5.  SQL

5.1.  SQL语法

SQL（Structured Query Language，结构化查询语言）是一种用于管理关系数据库的标准语言，它包括一系列用于查询、操作、定义和控制数据的指令。

1)  SQL语句可以单行或多行书写，以分号结尾。

2)  SQL语句可以使用空格/缩进来增强语句的可读性。

3)  MYSQL数据库的SQL语句不区分大小写，关键字建议使用大写。

4)  注释：

单行注释：-- 注释内容或#注释内容(MySQL特有)

多行注释：/* 注释内容 */

5.2.  SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

6.  数据库版本

使用以下命令查看 MySQL 版本：

[root@localhost ~]# mysql --version
mysql  Ver 8.2.0 for Linux on x86_64 (MySQL Community Server - GPL)

第二章 基本DDL操作

DDL是Data Definition Language（数据定义语言）的缩写，在SQL中主要用于创建、修改和删除数据库对象，如表、视图、索引、存储过程等结构定义相关的操作。

7.  数据库操作

7.1.  查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

7.2.  创建数据库

使用以下命令创建数据库（db_learn）：

mysql> create database db_learn;
Query OK, 1 row affected (0.01 sec)

7.3.  切换数据库

使用以下命令切换数据库：

mysql> use db_learn
Database changed

7.4.  删除数据库

使用以下命令删除数据库（数据结构和数据都会被删除）：

mysql> drop database db_learn

8.  表操作

后续的操作将在数据库db_learn中进行，默认在navicat中进行操作，主要讲解SQL语法，navicat工具的使用请查阅对应的资料。下面我们将模拟设计一个订单系统，进行逐步的语法讲解和案例演示。

8.1.  创建表

【语法】在 MySQL 中，创建表的语法如下：

CREATE TABLE table_name (  
    column1 datatype constraint,  
    column2 datatype constraint,  
    column3 datatype constraint,  
    ...  
);

其中：

● table_name 是你想要创建的表的名称。

● column1, column2, column3, ... 是表中的列名称。

● datatype 是每列的数据类型，如 INT, VARCHAR, DATE 等。

● constraint 是可选的列约束，如 NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK 等。

【DEMO】创建系统用户表：

CREATE TABLE sys_user (
  id INT COMMENT'ID',
  user_no varchar(30) COMMENT '用户编号',
  user_name VARCHAR(50) COMMENT '用户名称',
  pwd varchar(50) COMMENT '密码',
  create_time date COMMENT '创建时间',
  update_time date COMMENT'修改时间'
) COMMENT '系统用户表';

在navicat中执行的结果如下：

为方便后续演示，同时创建下表：

--创建订单主表
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT COMMENT '订单ID',
    order_no VARCHAR(30) COMMENT '订单编号',
    order_date DATETIME COMMENT '订单日期',
    customer_id BIGINT COMMENT '客户ID',
    guide_id BIGINT COMMENT '导购ID',
    remark VARCHAR(500) COMMENT '订单备注',
    PRIMARY KEY (id)
) COMMENT '订单主表';                    

--创建订单明细表
CREATE TABLE orders_detail (
    id BIGINT AUTO_INCREMENT COMMENT '订单明细ID',          
    orders_id BIGINT COMMENT '订单ID',                        
    goods_id BIGINT COMMENT '商品ID',                         
    qty DECIMAL(12, 2) COMMENT '数量',                        
    unit_price DECIMAL(12, 2) COMMENT '单价',                   
    amount DECIMAL(12, 2) COMMENT '金额',                       
    remark VARCHAR(500) COMMENT '订单明细备注',               
    PRIMARY KEY (id)                                           
) COMMENT '订单明细表';  

--客户表
CREATE TABLE `customer` (
    `id` BIGINT AUTO_INCREMENT COMMENT '客户ID',
    `cust_no` VARCHAR(30) COMMENT '客户编号',
    `cust_name` VARCHAR(30) COMMENT '客户名称',
    `nick_name` VARCHAR(30) COMMENT '昵称',
    `age` INT COMMENT '年龄',
    `gender` CHAR(1) COMMENT '性别',
    `mobile` VARCHAR(50) COMMENT '手机',
    `e_mail` VARCHAR(50) COMMENT '邮箱',
    `address` VARCHAR(300) COMMENT '地址',
    `remark` VARCHAR(500) COMMENT '备注',
    PRIMARY KEY (`id`)
) COMMENT='客户表';

-- 导购表
CREATE TABLE `guide` (
    `id` BIGINT AUTO_INCREMENT COMMENT '导购ID',
    `guide_no` VARCHAR(30) COMMENT '导购编号',
    `guide_name` VARCHAR(30) COMMENT '导购名称',  
    `remark` VARCHAR(500) COMMENT '备注',
    PRIMARY KEY (`id`)
) COMMENT='导购表';

CREATE TABLE `goods` (
    `id` BIGINT AUTO_INCREMENT COMMENT '商品ID',
    `goods_no` VARCHAR(30) COMMENT '商品编号',
    `goods_name` VARCHAR(30) COMMENT '商品名称', 
	`unit_price` DECIMAL(12,2) COMMENT '商品单价', 
    `remark` VARCHAR(500) COMMENT '备注',
    PRIMARY KEY (`id`)
) COMMENT='商品表';

8.2.  自增字段

【语法】使用以下命令为表添加自增字段：

ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;

【DEMO】对sys_user表的id字段设置为自增

ALTER TABLE sys_user MODIFY id INT PRIMARY KEY AUTO_INCREMENT;

【注意】MySQL要求每个表中只能有一个自增字段，并且这个字段必须是索引的一部分，通常是主键（PRIMARY KEY）。如下图不能直接执行“ALTER TABLE sys_user MODIFY id INT AUTO_INCREMENT；”，而应该设置为主键，再做自增。

8.3.  字段默认值

【语法】使用以下命令为表添加字段默认值：

ALTER TABLE 表名 MODIFY 列名 数据类型 DEFAULT 默认值;

【DEMO】把create_time字段设置系统日期

ALTER TABLE sys_user MODIFY create_time DATE DEFAULT (CURRENT_DATE);

设置默认值时，同时修改字段类型(date→datetime)：

ALTER TABLE sys_user MODIFY update_time DATETIME DEFAULT (SYSDATE());

--或者
ALTER TABLE sys_user MODIFY update_time DATETIME DEFAULT NOW();

8.4.  重命名表

【语法】使用以下命令重命名表：

RENAME TABLE 原表名 TO 新表名;

【DEMO】把sys_user改为sys_users

RENAME TABLE sys_user TO sys_users;

8.5.  增加字段

【语法】使用以下命令为表添加字段：

ALTER TABLE 表名 ADD COLUMN 列名 数据类型;

【DEMO】对表sys_users增加字段：

ALTER TABLE sys_users ADD COLUMN e_mail VARCHAR ( 100 ) COMMENT '电子邮箱',
ADD COLUMN remark VARCHAR ( 100 ) COMMENT '备注';

通过ADD COLUMN关键词可以同时增加多个不同数据类型的字段，COMMENT关键词对字段进行描述。

8.6.  删除字段

【语法】使用以下命令为表删除字段：

ALTER TABLE 表名 DROP COLUMN 列名;

【DEMO】表sys_users删除e_mail字段

ALTER TABLE sys_users DROP COLUMN e_mail;

8.7.  修改字段类型

【语法】使用以下命令为表修改字段类型：

ALTER TABLE 表名 MODIFY 列名 数据类型;

【DEMO】把create_time和update_time改为datetime类型

ALTER TABLE sys_users MODIFY create_time DATETIME,
MODIFY update_time DATETIME;

8.8.  复制表

【语法】使用以下命令复制表：

● 方法1：

-- 创建一个与原始表结构相同的新表  
CREATE TABLE new_table LIKE original_table;  
  
-- 复制数据到新表  
INSERT INTO new_table SELECT * FROM original_table;

● 方法2：

-- 复制表结构和数据，索引和约束一般需要手工添加
CREATE TABLE new_table AS
SELECT * FROM original_table；

【DEMO】复制表和数据

-- 创建一个与原始表结构相同的新表  
CREATE TABLE sys_users_bak1 LIKE sys_users;  
  
-- 复制数据到新表  
INSERT INTO sys_users_bak1 SELECT * FROM sys_users;

-- 复制表结构和数据
CREATE TABLE sys_users_bak2 AS SELECT * FROM sys_users;

【注意】

● 使用 LIKE 创建的新表会复制原始表的所有索引、主键、外键等约束。

● 使用 AS SELECT 创建的新表通常只会根据查询结果中的数据类型自动创建列，并不会复制原始表的索引和约束（在某些情况下，例如MySQL 5.7及更高版本，主键和唯一索引可能会被隐式地复制）。如果需要在新表上创建与原始表相同的索引和约束，必须手动添加。

● LIKE 语句只能复制整个表的结构，不能选择性地复制某些列。

● AS SELECT 语句提供了更大的灵活性，因为它允许你通过修改 SELECT 子句来选择性地复制原始表中的某些列，或者对数据进行转换和处理后再复制到新表中。

8.9.  删除表

【语法】使用以下命令删除表：

-- 删除单个表
DROP TABLE table_name;

-- 如果不确定表是否存在，可以加上条件判断避免出错
DROP TABLE IF EXISTS table_name;

-- 删除多个表
DROP TABLE table1, table2, ... , table_n;

-- 删除表的同时还可给出数据库名称，尤其当不在当前选定数据库时
DROP TABLE db_name.table_name;

【DEMO】删除表sys_users_bak2

DROP TABLE sys_users_bak2

8.10.  主键约束

【语法】使用以下命令为表添加主键约束：

ALTER TABLE 表名 ADD PRIMARY KEY (列名);

【DEMO】：把表sys_users_bak1的id设置为主键

ALTER TABLE sys_users_bak1 ADD PRIMARY KEY (id);

【注意】

主键可以单个字段，也可用使用多个字段（称为复合主键），主键值必须唯一。

【主键特点】

1.  唯一性（Uniqueness）：

主键的值必须在表的范围内唯一，即每一行的主键值都不能与表中其他行的主键值相同。

2.  非空性（Non-nullability）：

主键列的值不能为NULL。这意味着每一行在主键列上都必须有一个具体的值，不允许存在NULL值作为主键。

3.  稳定性：

主键一旦设定并有数据写入后，其值通常不应再做更改，尤其是在涉及外键约束和其他表的参照完整性时。这是因为主键是数据行的稳定标识符，更改主键可能导致数据一致性问题。

4.  选择性：

主键应当有足够的选择性，也就是说，不同行之间的主键值差异越大越好，这样可以提高查询效率。

5.  简单性：

主键尽可能简洁，例如，单字段主键比多字段复合主键更容易理解和使用。

6.  持久性：

主键的值应该是稳定的，不会随时间或其他因素变化而变化。

在设计主键时，除了上述原则外，还要考虑实际业务需求和数据库性能等因素。常见的主键策略包括自然主键（业务本身存在的唯一标识，如身份证号）、代理主键（系统生成的唯一标识，如自增序列）和复合主键（由多个字段组合而成的主键）。

8.11.  唯一约束

【语法】使用以下命令为表添加唯一约束：

ALTER TABLE 表名 ADD UNIQUE (列名);

【DEMO】：对订单表的订单编号做唯一约束，防止编号重复

ALTER TABLE orders ADD UNIQUE (order_no);

8.12.  CHECK约束

【语法】使用以下命令为表添加 CHECK约束：

ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (条件);

【DEMO】限制数量不能为0

ALTER TABLE orders_detail ADD CONSTRAINT chk_qty  CHECK (qty!=0);

8.13.  外键约束

【语法】使用以下命令为表添加外键约束：

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (列名) 
REFERENCES 参考表名(列名);

【DEMO】删除外键约束

ALTER TABLE orders_detail ADD CONSTRAINT frk_order_id
 FOREIGN KEY ( orders_id ) REFERENCES orders ( id );

8.14.  删除约束

【语法】使用以下命令为表删除约束：

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

【DEMO】

--删除外键
ALTER TABLE orders_detail DROP CONSTRAINT frk_order_id;
--或者
ALTER TABLE orders_detail DROP FOREIGN KEY  frk_order_id;


--删除数量约束
ALTER TABLE orders_detail DROP CONSTRAINT chk_qty;

经过上面的操作后，navicat中生成的表创建语句如下：

--系统用户表
CREATE TABLE `sys_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_no` varchar(30) DEFAULT NULL COMMENT '用户编号',
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户名称',
  `pwd` varchar(50) DEFAULT NULL COMMENT '密码',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `remark` varchar(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统用户表';

--订单主表
CREATE TABLE `orders` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `order_no` varchar(30) DEFAULT NULL COMMENT '订单编号',
  `order_date` datetime DEFAULT NULL COMMENT '订单日期',
  `customer_id` bigint DEFAULT NULL COMMENT '客户ID',
  `guide_id` bigint DEFAULT NULL COMMENT '导购ID',
  `remark` varchar(500) DEFAULT NULL COMMENT '订单备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_no` (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单主表';

--订单明细表
CREATE TABLE `orders_detail` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单明细ID',
  `orders_id` bigint DEFAULT NULL COMMENT '订单ID',
  `goods_id` bigint DEFAULT NULL COMMENT '商品ID',
  `qty` decimal(12,2) DEFAULT NULL COMMENT '数量',
  `unit_price` decimal(12,2) DEFAULT NULL COMMENT '单价',
  `amount` decimal(12,2) DEFAULT NULL COMMENT '金额',
  `remark` varchar(500) DEFAULT NULL COMMENT '订单明细备注',
  PRIMARY KEY (`id`),
  KEY `frk_order_id` (`orders_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单明细表';

9.  视图操作

MySQL中的视图（View）是一种虚拟表，它的内容由SELECT查询定义，而非实际存储数据。视图简化了复杂查询，并且可以根据需要提供基表中数据的不同视角。视图可以包含来自一个或多个实际表的数据，这些表被称为基表或基础表。视图的定义被保存在数据库中，当用户查询视图时，MySQL会根据视图的定义执行对应的SELECT查询，并返回结果。

视图的特点和优势包括：

1.  提高数据安全性：可以通过视图限制用户访问特定列或行，从而控制数据访问权限。

2.  数据抽象和简化：视图可以隐藏底层表的复杂结构，将复杂查询的结果封装成一个易于理解的形式。

3.  数据重用：对于常用或复杂的查询，可以创建视图以便多次使用。

4.  数据独立性：当基表结构发生改变时，只要视图定义中的查询仍然有效，就可以通过视图继续工作，不影响应用程序对视图的使用。

对视图进行增删改查操作时，需要注意的是并非所有的DML操作都能应用于所有类型的视图，特别是涉及到UPDATE和DELETE操作时，它们受限于视图的定义以及是否有足够的信息来定位到基表中的对应行。

9.1.  创建视图

【语法】使用以下命令创建视图

CREATE VIEW 视图名 AS SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件;

【DEMO】创建客户订单明细视图

CREATE VIEW v_cust_order_detail AS SELECT
c.cust_no,
c.cust_name,
a.order_date,
a.order_no,
b.goods_id,
b.qty,
b.amount 
FROM
	orders a
	INNER JOIN orders_detail b ON b.orders_id = a.id
	INNER JOIN customer c ON c.id = a.customer_id

从视图中查询数据：

select * from  v_cust_order_detail 

9.2.  修改视图

【语法】有两种方法修改视图

--方法1：使用ALTER VIEW
ALTER VIEW old_view AS
SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.id = table2.foreign_id
WHERE condition;

--方法2：CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW view_name [(column_list)]
AS SELECT ...
FROM ...
[WHERE conditions];

【DEMO】修改客户订单明细视图，增加单价字段

--方法1
ALTER VIEW v_cust_order_detail AS SELECT
c.cust_no,
c.cust_name,
a.order_date,
a.order_no,
b.goods_id,
b.unit_price, --增加单价
b.qty,
b.amount 
FROM
	orders a
	INNER JOIN orders_detail b ON b.orders_id = a.id
	INNER JOIN customer c ON c.id = a.customer_id;


-- 方法2
CREATE OR REPLACE VIEW v_cust_order_detail AS SELECT
c.cust_no,
c.cust_name,
c.mobile, -- 增加手机号
a.order_date,
a.order_no,
b.goods_id,
d.goods_no, -- 增加商品编码
d.goods_name, -- 增加商品名称
b.unit_price,
b.qty,
b.amount 
FROM
	orders a
	INNER JOIN orders_detail b ON b.orders_id = a.id
	INNER JOIN customer c ON c.id = a.customer_id
	INNER JOIN goods d on b.goods_id=d.id;

9.3.  删除视图

【语法】使用以下命令删除视图

DROP VIEW [IF EXISTS] view_name [, view_name ...];

这里的IF EXISTS是可选的，用于防止因试图删除不存在的视图而导致错误。如果你确定视图一定存在，可以直接写视图名；若想安全删除，可以在视图名前加上IF EXISTS关键字，这样即使视图不存在也不会报错。

【DEMO】删除v_cust_order_detail视图

DROP VIEW IF EXISTS v_cust_order_detail；
-- or 
DROP VIEW v_cust_order_detail；

10.  存储过程

MySQL 存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字和参数（如果有的话）来调用并执行它。存储过程可以看作是对一系列SQL操作的封装和重用，它允许用户通过简单的调用来执行复杂的数据库操作。存储过程可以接收输入参数、返回输出参数，还可以设置局部变量和条件逻辑，实现较为复杂的业务逻辑。存储过程的优点包括：

1.  代码重用：存储过程可以被多个客户端调用，减少了重复编写SQL语句的需求，提高了代码的可重用性。

2.  性能提升：由于存储过程在数据库内部执行，能够减少网络通信开销，同时数据库引擎可以对存储过程进行优化，使得执行效率相对较高。

3.  安全性：存储过程可以隐藏复杂的SQL逻辑，通过控制用户对存储过程的权限，增强了数据库的安全性。

4.  事务管理：存储过程中可以包含事务处理，确保一系列操作要么全部成功，要么全部失败。

5.  模块化：存储过程有助于将复杂的应用逻辑划分为可管理和维护的模块。

10.1.  创建存储过程

【语法】使用以下命令创建存储过程

CREATE PROCEDURE procedure_name ([IN|OUT|INOUT parameter_type parameter_name], ...)
BEGIN
    -- 存储过程的SQL语句和控制流语句
    DECLARE local_variable datatype;
    SET variable = expression;
    SELECT ... INTO variable;
    IF condition THEN
        statements;
    ELSEIF ... THEN
        ...
    ELSE
        ...
    END IF;
    WHILE condition DO
        statements;
    END WHILE;
    REPEAT
        statements;
    UNTIL condition END REPEAT;
    CASE case_value
        WHEN comparison_value THEN
            statements;
        ELSE
            statements;
    END CASE;
    CALL other_procedure(...);
    ...
END;

【DEMO】创建存储过程并调用

-- 定义存储过程，根据id修改客户名称
CREATE PROCEDURE update_customer_info (
	IN p_cust_id INT,
	IN p_new_name VARCHAR ( 50 )) 
BEGIN
	UPDATE customer 
	SET cust_name = p_new_name 
	WHERE
		id = p_cust_id;
END;

--调用存储过程
CALL update_customer_info(1, '张三');

10.2.  修改存储过程

【语法】

ALTER PROCEDURE procedure_name
[characteristic ...]
[PROCEDURE ANALYSE([MAX_CHARACTERS_PER_LITERAL], [MAX_MEMORY_ROWS])]
[SQL SECURITY { DEFINER | INVOKER }]
BEGIN
    -- 新的存储过程体（SQL语句和控制流）
END;

【DEMO】对update_customer增加地址参数（注意：MySQL8.0后不允许使用ALTER PROCEDURE，故需先删除旧的存储过程，然后重新创建存储过程）

-- 如果存在，则删除
DROP PROCEDURE IF EXISTS update_customer_info;
-- 重新创建
DELIMITER //  
CREATE PROCEDURE update_customer_info(  
    IN p_id BIGINT,  
    IN p_name VARCHAR(50),  
    IN p_address VARCHAR(100)  
)  
BEGIN  
    UPDATE customer  
    SET cust_name = p_name,  
       address = p_address  
    WHERE id = p_id;  
END //  
DELIMITER ;

-- 调用
CALL update_customer_info(1,'张三','深圳市深南大道')

10.3.  删除存储过程

在MySQL中，删除存储过程的操作可以通过执行DROP PROCEDURE语句来完成。此命令允许你从数据库中移除不再需要的存储过程。

【语法】

DROP PROCEDURE [IF EXISTS] 存储过程名称;

[IF EXISTS]为可选项，当不存在该存储过程时，删除语句不执行（不报错）。

注意事项：

● 权限：确保你有足够的权限来删除存储过程。通常，这需要ALTER ROUTINE权限。

● 依赖性检查：在删除存储过程之前，最好确认没有其他过程、触发器或者应用程序依赖于它。如果存在依赖，删除操作可能导致相关功能失效。

● 确认删除：使用IF EXISTS可以安全地尝试删除，即使存储过程不存在，也不会产生错误。

● 事务管理：虽然删除存储过程本身不是一个事务性的操作，但在包含这一操作的脚本中，可能需要考虑事务的使用，以确保数据一致性。

【DEMO】

DROP PROCEDURE IF EXISTS update_customer_info

11.  函数

MySQL提供了丰富的内置函数，用于处理各种数据类型，包括字符串、数字、日期时间等。以下是一些主要类别及其示例函数：

1)  字符串函数

用于处理文本数据，例如：

●  `LENGTH(str)`: 返回字符串str的长度。

●  `CONCAT(str1, str2,...)`: 连接两个或更多字符串。

●  `SUBSTR(str, start, length)`: 从字符串str的start位置开始，返回长度为length的子串。

●  `UPPER(str)`, `LOWER(str)`: 将字符串转换为大写或小写。

●  `TRIM(str)`: 移除字符串两侧的空格。

●  `REPLACE(str, from_str, to_str)`: 在字符串str中用to_str替换所有from_str。

2)  数学函数

用于执行数学运算，例如：

●  `ABS(x)`: 返回x的绝对值。

●  `ROUND(x, decimals)`: 把x四舍五入到decimals位小数。

●  `FLOOR(x)`, `CEIL(x)`: 返回不大于x的最大整数（向下取整），或不小于x的最小整数（向上取整）。

●  `RAND()`: 返回0到1之间的随机数。

3)  日期时间函数

用于处理日期和时间数据，例如：

●  `NOW()`: 返回当前的日期和时间。

●  `CURDATE()`: 返回当前的日期。

●  `CURTIME()`: 返回当前的时间。

●  `YEAR(date)`, `MONTH(date)`, `DAY(date)`: 分别从日期中提取年、月、日。

●  `DATE_ADD(date, INTERVAL expr unit)`: 给日期添加指定的时间间隔。

●  `TIMESTAMPDIFF(unit, datetime1, datetime2)`: 计算两个日期/时间之间的差，单位由unit指定。

4)  控制函数

用于逻辑控制，例如：

●  `IF(condition, value_if_true, value_if_false)`: 如果condition为真，则返回value_if_true，否则返回value_if_false。

●  `CASE...WHEN...THEN...ELSE...END`: 多分支条件语句，根据不同的条件返回不同的结果。

5)  聚合函数

用于从多行数据中计算单一值，例如：

●  `COUNT(column)`: 计算列中非NULL值的数量。

●  `SUM(column)`: 计算列的总和。

●  `AVG(column)`: 计算列的平均值。

●  `MAX(column)`, `MIN(column)`: 找出列中的最大值和最小值。

6)  系统函数

提供有关服务器的信息，例如：

●  `USER()`: 返回当前用户的名称。

●  `DATABASE()`: 返回当前数据库的名称。

●  `VERSION()`: 返回MySQL服务器的版本号。

这些函数可以在查询中直接调用，以实现复杂的数据处理和分析任务。在实际应用中，根据具体需求选择合适的函数并正确使用它们的参数是非常重要的。

MySQL除了以上的系统内置函数，还可以实际需要，进行自定义函数。

11.1.  创建自定义函数

在MySQL中，自定义函数（User-Defined Function，UDF）允许用户根据特定需求创建自己的函数，以扩展MySQL的功能。自定义函数与内置函数类似，可以接受输入参数，执行一系列操作，并返回一个结果。下面概述了创建和使用自定义函数的基本步骤和规则：

【语法】使用以下命令创建函数：

CREATE FUNCTION function_name RETURNS return_type
BEGIN
   -- 函数体，包含SQL语句和流程控制
   DECLARE variable_name variable_type;
   ... -- 可以声明多个变量
   ... -- 函数逻辑
   RETURN result_value;
END;

● function_name：你自定义的函数名称，需遵循MySQL的标识符命名规则。

● RETURNS return_type：指定函数返回值的类型，如INT, VARCHAR(255), DECIMAL, 等。

● BEGIN ... END：包裹函数体的代码块，其中包含变量声明、流程控制和实际的逻辑处理。

● RETURN result_value;：函数结束时必须有一条RETURN语句，用于返回计算结果。

【DEMO】创建一个函数，该函数接受一个字符串参数，返回该字符串的长度加上5

-- 创建函数
CREATE FUNCTION AddFiveToLength(str VARCHAR(255))
RETURNS INT
BEGIN
   DECLARE len INT DEFAULT LENGTH(str);
   RETURN len + 5;
END;

/* 默认用户不得创建或修改存储函数，执行将报错：
1418 - This function has none of DETERMINISTIC, NO SQL,
 or READS SQL DATA in its declaration and binary logging
  is enabled (you *might* want to use the less safe 
  log_bin_trust_function_creators variable) */
  
/*解决方法：修改全局参数，该值默认为0*/
set global log_bin_trust_function_creators=1;

--调用函数
SELECT AddFiveToLength('Hello');

【特点】自定义函数有如下特点：

● 确定性(Deterministic vs. Non-deterministic)：自定义函数在定义时可以声明为确定性或非确定性。确定性函数对于相同的输入总是返回相同的结果，而非确定性函数可能对于相同的输入返回不同的结果（例如，包含随机数生成）。这影响到函数是否能在某些上下文中被使用，比如在索引或复制环境中。

● 安全性: 在启用二进制日志的情况下，自定义函数需要明确声明其是否读取（READS SQL DATA）、修改（MODIFIES SQL DATA）或不涉及（NO SQL）SQL数据，以保证数据的一致性和复制的正确性。

● 参数和变量：函数可以有零个或多个参数，参数类型需明确指定。在函数体内，可以声明局部变量来辅助计算。

● 调用方式：自定义函数可以直接在SQL查询中像内置函数那样被调用，无需使用CALL语句。

11.2.  修改自定义函数

在MySQL中，直接修改一个已存在的自定义函数（UDF）的行为（如更改其逻辑或返回类型）并不直接支持。如果你想改变函数的行为，你需要经历一个两步的过程：

-- 1.删除已经存在的自定义函数
DROP FUNCTION IF EXISTS function_name;

--2.重新创建函数
CREATE FUNCTION function_name ...

11.3.  删除函数

【语法】

DROP FUNCTION IF EXISTS function_name;

确保替换function_name为你要删除的函数名称。使用IF EXISTS可以避免因函数不存在而引发错误。

【DEMO】删除自定义函数AddFiveToLength

 DROP FUNCTION IF EXISTS AddFiveToLength

12.  触发器

MySQL触发器（Trigger）是一种特殊类型的存储过程，它会在指定表上发生特定事件（如INSERT、UPDATE或DELETE）时自动执行。触发器可以用于检查或修改数据，保持数据的完整性，防止无效数据进入数据库，或者执行其他与数据表相关的操作。

【特征】

(1)  自动性：触发器是由数据库系统自动激活的，无需外部调用。

(2)  事件驱动：触发器与特定的数据库表操作关联，如INSERT、UPDATE、DELETE。

(3)  时机：触发器可以在事件发生之前（BEFORE）或之后（AFTER）执行。

(4)  作用范围：通过FOR EACH ROW指定，触发器对受操作影响的每一行数据分别执行一次。

(5)  逻辑执行：触发器中可以包含复杂的SQL语句，用于实现业务规则或数据验证

【应用场景】

● 数据完整性检查：在数据插入或更新前，检查数据是否满足特定的业务规则。

● 自动更新相关表：当一个表的数据发生变化时，自动更新另一个表的相应数据，以维护参照完整性。

● 审计日志：自动记录数据变更的历史，便于追踪和审计。

● 自动计算字段值：例如，自动计算订单总金额或更新库存量。

● 安全控制：根据用户权限或数据状态决定是否允许执行某项操作。

【注意事项】

● 触发器的执行是隐式的，可能会对数据库性能产生影响，特别是在高并发环境下。

● 应谨慎使用触发器，避免造成循环触发或逻辑错误。

● 确保触发器的逻辑正确且高效，避免对数据库性能造成不必要的负担。

通过合理设计和使用触发器，可以增强数据库的自动化管理能力，但同时要求开发者具备良好的数据库设计和维护技能。

12.1.  创建触发器

【语法】使用以下命令创建触发器：

CREATE TRIGGER trigger_name
BEFORE | AFTER  INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
   -- 触发器内部的SQL语句
END;

● trigger_name：触发器的名称。

● trigger_time（BEFORE|AFTER）：触发时间，可以是BEFORE或AFTER。

● trigger_event：触发事件，可以是INSERT、UPDATE或DELETE。

● table_name：触发器关联的表名。

● FOR EACH ROW：表示触发器会对受影响的每一行都执行一次。

【DEMO】

 /* ----------------------------------------
	CreateDate：20240413
	Author：HillMan
	Description：检查订单数量，限制不能为0
------------------------------------------*/
CREATE TRIGGER tr_check_order_qty
BEFORE INSERT ON orders_detail
FOR EACH ROW
BEGIN
  IF NEW.qty =0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单数量不能等于0';
  END IF;
END 

向orders_detail表中插入数据：

12.2.  修改触发器

在MySQL中，如果你需要修改一个已存在的触发器（比如更改其逻辑或触发时机），不能直接进行修改操作，而是需要先删除旧的触发器，然后重新创建一个新的触发器来替代它。这是因为MySQL不直接支持ALTER TRIGGER命令来修改触发器的定

义。

-- 1.删除存在的触发器
DROP TRIGGER [IF EXISTS] trigger_name;

--2.重新创建触发器
CREATE TRIGGER trigger_name
BEFORE | AFTER  INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
   -- 触发器内部的SQL语句
END;

12.3.  删除触发器

在MySQL中，若要删除一个已创建的触发器，可以使用DROP TRIGGER语句。此操作是不可逆的，因此在执行删除操作前请确保不再需要该触发器。以下是删除触发器的基本语法，包括使用IF EXISTS来避免在触发器不存在时出现错误：

【语法】

DROP TRIGGER [IF EXISTS] trigger_name;

【DEMO】删除触发器tr_check_order_qty

DROP TRIGGER IF EXISTS tr_check_order_qty;

第三章 基本DML操作

DML是Data Manipulation Language（数据操作语言）的缩写，在SQL中主要用于对数据库表中的数据进行增删改查等操作。对于MySQL中的唯一列索引，虽然我们在执行DML语句时不会直接涉及到索引的创建或删除，但索引的存在会影响到这些DML操作的结果，尤其是当尝试插入或更新数据时，如果违反了唯一索引约束，则会引发错误

13.  新增数据

13.1.  插入单条数据

在 MySQL 中，插入数据主要有以下几种方式：

1.  INSERT INTO 语句：这是最基本且常用的插入数据方式。它允许你在指定的数据表中插入一条新记录。

【语法】

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

【DEMO】新增单个用户

insert into sys_users(user_no,user_name,pwd)
VALUES('admin','管理员','123456')

-- 插入订单
INSERT INTO orders(order_no,order_date,customer_id,guide_id)
VALUES('SO0001',NOW(),1,1);

在上面的示例中，主键（ID）字段已经设置为自增，所以在插入时，由数据库自动赋值，所以不需要包括该字段。

13.2.  插入多条数据

在MySQL中，如果你想一次性插入多条数据到一个表中，可以使用INSERT INTO语句的特殊格式，即在一个SQL语句中包含多个值列表（“，”英文逗号分隔）。

【语法】

INSERT INTO 表名 (字段1, 字段2, ...)
VALUES
    (值1, 值2, ...),
    (值1, 值2, ...),
    ...
    (值1, 值2, ...);

【DEMO】

INSERT INTO customer(cust_no,cust_name,nick_name,age,gender,mobile,e_mail,address)
VALUES('C0001','妲己','千年老妖',1000,0,'13800000000','888@163.com','商朝'),
('C0002','鲁班','能工巧匠',1000,1,'13800000001','8888@163.com','春秋'),
('C0003','诸葛亮','神算子',1000,1,'13800000002','88888@163.com','三国');

-- 新增商品
INSERT INTO goods(goods_no,goods_name,unit_price)
VALUES('10001','手机',6188),
('10002','电脑',5100),
('10003','打印机',2899);

-- 新增导购
INSERT INTO guide(guide_no,guide_name)
VALUES('S0001','张三'),
('S0002','李四'),
('S0003','王五');

13.3.  从其他表中插入数据

INSERT INTO … SELECT 语句：这种方式允许你从一个或多个表中查询数据并将其插入到另一个表中。语法如下：

【语法】

INSERT INTO table_name (column1, column2, ..., columnN)
SELECT column1, column2, ..., columnN
FROM source_table
WHERE some_condition;

【DEMO】在前面的订单基础上，插入订单明细

-- 插入id为1，2的商品到订单明细（订单id为1）
INSERT INTO orders_detail(orders_id,goods_id,qty,unit_price,amount)
select 1,id,2,unit_price,2*unit_price from  goods WHERE id in(1,2);

【注意】

此示例中使用了常量：orders_id写死为1，qty写死为2。另外amount的值使用了表达式2*unit_price。

-- 从视图查询订单信息
select * from v_cust_order_detail;

除了可以从实际表中查询数据并插入到目标表，如果数据都为常量，还可以从dual表插入数据（当然此方法可以直接使用insert into ……values的方式），如：

INSERT INTO sys_users(user_no,user_name,pwd,create_time)
select 'U001','销冠','123456',NOW() from  DUAL;

14.  数据查询

14.1.  基础查询

14.1.1.  单表查询

14.1.1.1.  单表全量查询

【语法】

SELECT 列名1, 列名2, ... FROM 表名

【DEMO】查询全表信息

-- 查询全表所有字段
SELECT * FROM sys_users；

-- 查询全部的指定字段
SELECT user_no,user_name FROM sys_users

14.1.1.2.  单表条件查询

【语法】

SELECT column1, column2, ...
FROM table_name
WHERE condition;

【DEMO】

-- 查询女性客户
SELECT * FROM customer WHERE gender=0;

-- 查询客户名称包含"亮"的客户
SELECT * FROM customer WHERE cust_name LIKE '%亮%';

14.1.1.3.  排序查询

【语法】

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
ORDER BY column [ASC | DESC], ...;

【DEMO】

-- 年龄升序（默认为ASC，可省略）
SELECT * FROM customer ORDER BY age;

-- 年龄降序
SELECT * FROM customer ORDER BY age DESC;

14.1.2.  多表查询

MySQL的多表查询是指在同一查询语句中涉及两个或更多表的数据操作，以关联和整合来自不同数据源的信息。

在查询之前进行如下数据准备：

-- 导购
INSERT INTO guide(guide_no,guide_name)
VALUES('S0004','赵六');

-- 插入id为1，2的商品到订单明细（订单id为1）
INSERT INTO orders_detail(orders_id,goods_id,qty,unit_price,amount)
select 1,id,2,unit_price,2*unit_price from  goods WHERE id in(1,2);

-- 订单
INSERT INTO orders(order_no,order_date,customer_id,guide_id)
VALUES('SO0002',NOW(),2,2),('SO0003',NOW(),3,3),('SO0004',NOW(),2,3),('SO0005',NOW(),3,2);

INSERT INTO orders_detail(orders_id,goods_id,qty,unit_price,amount)
select 2,id,2,unit_price,2*unit_price from  goods WHERE id in(1,2);

INSERT INTO orders_detail(orders_id,goods_id,qty,unit_price,amount)
select 3,id,3,unit_price,3*unit_price from  goods WHERE id in(3,2);

INSERT INTO orders_detail(orders_id,goods_id,qty,unit_price,amount)
select 4,id,4,unit_price,4*unit_price from  goods WHERE id in(4,2);

INSERT INTO orders_detail(orders_id,goods_id,qty,unit_price,amount)
select 5,id,5,unit_price,5*unit_price from  goods WHERE id in(3,2);

14.1.2.1.  左连接

左外连接返回左表（第一个表）的所有记录，即使在右表中没有匹配。对于右表中无匹配的记录，结果中对应的列显示为NULL。

【语法】

SELECT column_list
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

【DEMO】

-- 查询导购的订单情况
SELECT
	a.guide_no,
	a.guide_name,
	b.order_no,
	b.order_date 
FROM
	guide a
	LEFT JOIN orders b ON b.guide_id = a.id

在这个例子中，orders 表中的 guide_id_id 与 guide 表中的 id 相匹配。结果集会包含 guide 表（左表）中的所有导购，以及他们对应的订单信息（如果有的话）。如果某个导购没有下过订单，那么 orders 表中的列将会是NULL值。

14.1.2.2.  右连接

在MySQL中，右连接（RIGHT JOIN）会返回右表的所有行，即使左表中没有匹配的行。如果左表中没有匹配的行，那么左表的列会以NULL值返回。右连接通常与ON子句一起使用，以指定连接条件。

【语法】

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

【DEMO】

-- 查询导购的订单情况
SELECT
	b.order_no,
	b.order_date,
	a.guide_no,
	a.guide_name 
FROM
	orders b
	RIGHT JOIN guide a ON b.guide_id = a.id;

在这个例子中，orders 表中的 guide_id_id 与 guide 表中的 id 相匹配。结果集会包含 guide 表（右表）中的所有导购，以及他们对应的订单信息（如果有的话）。如果某个导购没有下过订单，那么 orders 表中的列将会是NULL值。

14.1.2.3.  内连接

在MySQL中，内连接（INNER JOIN）是最常见的连接类型，它用于连接两个或多个表，并且只返回两个表中连接条件相匹配的行。如果连接条件没有找到匹配项，那么相关的行将不会出现在结果集中。

内连接是默认的连接类型，所以即使不使用 INNER 关键字，MySQL 也会执行内连接。

【语法】

SELECT column_list
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

SELECT column_list
FROM table1
JOIN table2 -- 省略INNER关键词
ON table1.column_name = table2.column_name;

【DEMO】

-- 查询客户订单
SELECT
	a.cust_no,
	a.cust_name,
	b.order_no,
	b.order_date 
FROM
	customer a
	INNER JOIN orders b ON b.customer_id = a.id;

在这个例子中，customer 表中的 id 与 orders 表中的 customer 相匹配。结果集会包含所有成功匹配的客户信息和订单信息。如果某个客户没有下过订单，或者某个订单没有录入客户，那么这些信息不会出现在结果集中。

14.1.2.4.  全连接

在SQL中，全连接（FULL OUTER JOIN）的目的是合并两个表，返回左表和右表中所有的行。如果某一侧的连接条件没有匹配，那么结果集中该侧的列会以NULL值填充。

全连接会包括：

● 左表中存在匹配的行。

● 右表中存在匹配的行。

● 左表或右表中不满足连接条件的行，这些行在结果集中会以NULL填充非匹配侧的列。

【注意】

MySQL不支持FULL OUTER JOIN的语法，只能变通实现。

【语法】

-- SQL 语法
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.common_field = table2.common_field;

-- MySQL变通实现 ，通过左连接和右连接查询后进行结果合并
SELECT *
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field
UNION
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

【DEMO】

-- 为了演示全外连接，先清空一个导购
update orders set guide_id=null WHERE id=4

-- 查询全部导购和订单信息
SELECT
	a.guide_no,
	a.guide_name,
	b.order_no,
	b.order_date 
FROM
	guide a
	LEFT JOIN orders b ON a.id = b.guide_id 
UNION
SELECT
	a.guide_no,
	a.guide_name,
	b.order_no,
	b.order_date 
FROM
	guide a
	RIGHT JOIN orders b ON a.id = b.guide_id

这个查询首先通过左连接返回guide表中所有导购及其对应的订单信息，然后将没有匹配的订单信息以NULL填充。然后，通过右连接返回orders表中所有订单及其对应的导购信息，并将没有匹配的导购信息以NULL填充。最后，使用UNION将两个结果集合并，去除重复的行，从而得到全连接的结果。

14.1.2.5.  UNION查询

在MySQL中，UNION查询用于合并两个或多个SELECT语句的结果集，形成单个结果集。使用UNION时，需要注意以下几点：

● 列数匹配：参与UNION的每个SELECT语句中的列数必须相同。

● 类型兼容：每个相应位置的列数据类型必须兼容。例如，如果第一个查询的第一列是整数类型，那么第二个查询的第一列也必须是整数类型。

● 去除重复：UNION默认会去除结果集中的重复行，只保留唯一的行。

● 排序：ORDER BY子句应该位于UNION的外部，并且仅能使用一次，以对最终合并后的结果集进行排序。

● 性能：使用UNION ALL可以提高查询性能，因为它不会去除重复行，所以不需要排序，这通常比UNION快。

【语法】

SELECT column_list
FROM table1
UNION
SELECT column_list
FROM table2;

【DEMO】使用UNION，用ORDER BY排序时需要使用括号

-- 查询全部导购和订单信息(UNION)
(SELECT
	a.guide_no,
	a.guide_name,
	b.order_no,
	b.order_date 
FROM
	guide a
	LEFT JOIN orders b ON a.id = b.guide_id )
UNION 
(SELECT
	a.guide_no,
	a.guide_name,
	b.order_no,
	b.order_date 
FROM
	guide a
	RIGHT JOIN orders b ON a.id = b.guide_id)
	ORDER BY guide_no

【DEMO】使用UNION ALL

-- 查询全部导购和订单信息（UNION ALL）
SELECT
	a.guide_no,
	a.guide_name,
	b.order_no,
	b.order_date 
FROM
	guide a
	LEFT JOIN orders b ON a.id = b.guide_id 
UNION ALL
SELECT
	a.guide_no,
	a.guide_name,
	b.order_no,
	b.order_date 
FROM
	guide a
	RIGHT JOIN orders b ON a.id = b.guide_id

在以上的示例中，使用UNION查询去除了重复的行，且使用了ORDER BY进行排序（NULL排序在前）。使用UNION ALL时返回了两个连接的所有行。

【注意】

● 当使用UNION或UNION ALL时，每个SELECT语句的列名不需要相同，但是在最终结果集中列名会与第一个SELECT语句的列名相匹配。

● 如果列名不同，可以使用别名来使它们一致。

● 在执行UNION或UNION ALL之前，每个SELECT语句都可以有自己的WHERE子句和ORDER BY子句，但最终结果集的排序只能通过在UNION或UNION ALL之后使用一个ORDER BY子句来实现。

14.1.2.6.  INTERSECT查询

在MySQL中，INTERSECT 是一个用于返回两个查询结果集中都存在的行的操作符。它类似于数学中的交集概念，返回的结果集是两个结果集共有的部分。

【语法】

SELECT column_list
FROM table1
INTERSECT
SELECT column_list
FROM table2;

【DEMO】

-- 查询导购和订单信息(INTERSECT)
SELECT
	a.guide_no,
	a.guide_name,
	b.order_no,
	b.order_date 
FROM
	guide a
	LEFT JOIN orders b ON a.id = b.guide_id 
INTERSECT
SELECT
	a.guide_no,
	a.guide_name,
	b.order_no,
	b.order_date 
FROM
	guide a
	RIGHT JOIN orders b ON a.id = b.guide_id

在以上的示例中，取了两个查询中都存在的数据（共有数据），类似于数学交集的概念。当然，上面的查询也可以使用INNER JOIN实现。

14.1.2.7.  EXCEPT查询

在MySQL的早期版本中，不直接支持EXCEPT集合操作符来找出两个查询结果之间的差集。不过，你能够通过LEFT JOIN结合IS NULL条件或者使用NOT EXISTS子句等方式来模拟实现EXCEPT的功能。

自MySQL 8.0版本起，MySQL已经正式引入了EXCEPT关键字，现在可以直接用来找出存在于第一个查询结果中但不在第二个查询结果中的行。

通常，使用LEFT JOIN、IS NULL、NOT EXISTS等语句的性能会优于EXCEPT。

【语法】

(SELECT column1, column2 FROM table1)
EXCEPT
(SELECT column1, column2 FROM table2);

这个查询将返回那些在table1中存在的、而不在table2中的column1和column2的记录集合。同样，对应列的数量和数据类型需要一致。

【DEMO】

-- 查询订单信息，排除导购为空的行(EXCEPT)
SELECT
	* 
FROM
	orders a 
EXCEPT
SELECT
	* 
FROM
	orders b 
WHERE
	b.guide_id IS NULL

14.1.2.8.  子查询

在MySQL中，子查询（也称为嵌套查询或内部查询）是嵌套在另一个查询（外部查询）内部的SQL查询。子查询可以出现在SELECT、INSERT、UPDATE或DELETE语句中，并且通常用在WHERE或HAVING子句中，以返回一个值或一组值供外部查询使用。

【注意】

● 子查询限制：在某些上下文中，如FROM子句中，子查询可能会有限制，比如它们必须返回唯一的列集合。

● 性能考虑：子查询可能会影响查询性能，尤其是当它们包含复杂的逻辑或处理大量数据时。在某些情况下，使用连接（JOIN）或其他策略可能更有效率。

● 标量子查询：如果子查询返回多个行，那么外部查询中使用它的比较运算符必须允许多行比较，如IN。

● 相关子查询：子查询可以是相关的，即它的结果取决于外部查询中的行。这种子查询经常用在WHERE子句中，并且行对行地与外部查询进行比较。

● 非相关子查询：如果子查询的结果不依赖于外部查询的任何值，那么它是非相关的，可以独立执行。

● 子查询优化：有时可以通过将子查询转换为连接或使用派生表、物化子查询或临时表来优化查询性能。

● 使用EXISTS：当子查询用于存在性检查时，使用EXISTS通常比使用IN更高效，尤其是当子查询返回大量行时。

● 避免大数据集：尽量避免让子查询返回大量数据，因为这可能导致外部查询的性能问题。

● 子查询和事务：子查询通常在外部查询的事务上下文中执行，因此它们的执行会遵循相同的事务规则。

● 正确使用子查询可以提高查询的灵活性和表达能力，但也要注意它们可能带来的性能影响

【DEMO】

-- where 中使用的子查询，查询金额大于平均金额的订单明细
SELECT
	a.* 
FROM
	orders_detail a 
WHERE
	a.amount >(
	SELECT
		avg( b.amount ) 
	FROM
	orders_detail b 
	)

-- 作为计算字段，如total_qty
SELECT a.*,( SELECT sum( qty ) FROM orders_detail ) AS total_qty 
FROM
	orders_detail a

-- 作为from子句的子查询
SELECT b.goods_no, b.goods_name, a.qty, a.unit_price, a.amount 
FROM orders_detail a,( SELECT * FROM goods ) b 
WHERE
	a.goods_id = b.id;

-- 作为HAVING子句的子查询,平均单价大于总平均单价
SELECT
	a.goods_id,
	AVG( a.unit_price ) AS unit_price 
FROM
	orders_detail a 
GROUP BY
	a.goods_id 
HAVING
	AVG( unit_price ) > ( SELECT AVG( unit_price ) FROM orders_detail );

-- 新增客户
INSERT INTO customer(cust_no,cust_name,nick_name,age,gender,mobile,e_mail,address)
VALUES('C0004','后羿','神射手',4030,1,'13800000000','888@163.com','夏代')

-- 在EXISTS中使用，查询没有购买过任何商品的客户
SELECT * FROM customer c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

14.1.2.9.  Exists/Not Exists查询

在MySQL中，EXISTS是一个SQL操作符，用于检查子查询是否返回任何行。它不返回数据列，只返回两个可能的结果值：TRUE或FALSE。EXISTS通常用在WHERE子句中，以基于另一个查询的结果来过滤主查询的行。NOT EXISTS的用法与EXISTS一致，只是值取反，即不存在子查询中的条件。

【语法】

SELECT column_list
FROM table1
WHERE EXISTS (
    SELECT *
    FROM table2
    WHERE condition
);

【DEMO】

-- 查询有购买过下过订单的客户
SELECT
	* 
FROM
	customer c 
WHERE
	EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id );

-- 查询没有购买过下过订单的客户
SELECT
	* 
FROM
	customer c 
WHERE
	NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id );

【注意】

● 性能优化：使用EXISTS可以提高查询性能，因为它允许数据库查询优化器在找到第一个匹配的行后停止子查询的执行。

● 相关子查询：EXISTS经常与相关子查询一起使用，相关子查询是指子查询中引用了外部查询中的表或别名。

● 子查询结果：EXISTS不关心子查询返回的具体数据，只关心子查询是否返回了至少一行。

● 非相关子查询：如果子查询不依赖于外部查询的任何值，那么它是非相关的，可以独立于外部查询执行。

● 使用ANY或ALL：在某些情况下，可以使用EXISTS与ANY或ALL一起使用，以提供更明确的语义。

● 避免大数据集：如果子查询返回大量数据，可能会影响查询性能。

● 事务和锁定：EXISTS查询在事务上下文中执行，并遵循相同的事务规则和锁定行为。

● 与IN比较：当子查询返回多个值时，EXISTS通常比IN更高效，因为EXISTS在找到第一个匹配的行后就可以停止执行。

● 避免子查询：在某些情况下，可以通过使用JOIN操作来避免使用子查询，这可能会提高查询性能。

● 在EXISTS的子查询中，为了提高性能，一般不查询具体的字段，而只有使用select 1 代替。

正确使用EXISTS可以提高查询的灵活性和性能，特别是在需要基于另一个查询的存在性来过滤数据时。

14.2.  高级查询

14.2.1.  分组查询

分组查询是数据库操作中的一种常见查询方式，它允许你根据一个或多个列的值将数据分成不同的组，并为每个组应用聚合函数（如SUM、COUNT、MAX、MIN、AVG等）。分组查询在数据分析中非常有用，可以帮助你快速获取汇总信息。

【特点】

(1)  使用GROUP BY子句：在SQL查询中，GROUP BY子句用于指定分组的列或列的组合。

(2)  聚合函数：在分组查询中，你可以使用聚合函数对每个分组进行计算。

(3)  选择列：在SELECT语句中，除了分组的列之外，其他列通常是聚合函数的结果。

(4)  HAVING子句：HAVING子句用于对分组后的结果进行过滤，类似于WHERE子句，但专门用于过滤分组后的结果。

(5)  空值处理：在分组查询中，如果分组列中含有空值，空值会被作为一个分组处理。

(6)  分组列的选择：分组列的选择对查询的性能有影响，选择合适的列可以提高查询效率。

(7)  分区内分组：在某些数据库系统中，还可以在分区表上执行分组查询，这可能会进一步优化查询性能。

【DEMO】

-- 查询商品的总销售数量和金额
SELECT
	b.goods_no,
	b.goods_name,
	sum( a.qty ) AS qty,
	sum( a.amount ) AS amount 
FROM
	orders_detail a
	INNER JOIN goods b ON b.id = a.goods_id 
GROUP BY
	b.goods_no,
	b.goods_name

-- 查询客户的总销售数量和金额
SELECT
	c.cust_no,
	c.cust_name,
	sum( b.qty ) AS qty,
	sum( b.amount ) AS amount 
FROM
	orders a
	INNER JOIN orders_detail b ON b.orders_id = a.id
	INNER JOIN customer c ON c.id = a.customer_id 
GROUP BY
	c.cust_no,
	c.cust_name

14.2.2.  窗口函数查询

MySQL窗口函数（也称为“分析函数”或“OLAP函数”）是SQL中用于执行窗口聚合计算的函数。它们允许你对一组行执行聚合计算，这些行与当前行有某种关系，而不是像传统聚合函数那样对整个表进行聚合。窗口函数在处理数据排名、比较、移动平均等方面非常有用。

窗口函数可以极大地提高处理复杂数据集的效率，并且使得SQL查询更加强大和灵活。在使用窗口函数时，重要的是要仔细考虑 PARTITION BY 和 ORDER BY 子句，因为它们定义了窗口的逻辑和行为。

【语法】

FUNCTION (column) OVER (
    [ PARTITION BY column, ... ]
    [ ORDER BY column [ ASC | DESC ], ... ]
    [ ROWS | RANGE frame_clause ]
)

● FUNCTION 是一个聚合函数，如 SUM()、AVG()、COUNT() 等。

● PARTITION BY 是可选的，用于将数据分成不同的分区，每个分区内部独立进行聚合计算。

● ORDER BY 是可选的，用于定义窗口中行的排序方式。

● frame_clause 是可选的，用于定义窗口的行范围，可以是 ROWS 或 RANGE。

【常用窗口函数】

● ROW_NUMBER()：为每个窗口内的行分配一个唯一的序号。

● RANK()：为每个窗口内的行分配一个排名，相同值的行会有相同的排名，排名之间会有间隔。

● DENSE_RANK()：与 RANK() 类似，但排名之间不会有间隔。

● LEAD()：返回当前行的下一行的值。

● LAG()：返回当前行的上一行的值。

● SUM()：计算窗口内所有行的总和。

● AVG()：计算窗口内所有行的平均值。

● MIN()：返回窗口内所有行的最小值。

● MAX()：返回窗口内所有行的最大值。

【DEMO】

-- 计算商品的销售额排名
SELECT
	b.goods_no,
	b.goods_name,
	sum( a.amount ) amount,
	RANK() OVER ( ORDER BY sum( a.amount ) DESC ) AS amount_rank 
FROM
	orders_detail a
	INNER JOIN goods b ON b.id = a.goods_id 
GROUP BY
	b.goods_no,
	b.goods_name;

-- 计算与前一行的差
SELECT id,  
    amount,
    amount - LAG(amount, 1, 0) OVER (ORDER BY id) AS amount_difference
FROM
    orders_detail;

-- 计算每行的累计值
SELECT
    id,
    amount,
    SUM(amount) OVER (ORDER BY id) AS cumulative_amount
FROM
    orders_detail;

-- PARTITION BY 分区统计
SELECT
	goods_id,
	orders_id,
	amount,
	-- 计算每个产品的总销售额
	SUM( amount ) OVER ( PARTITION BY goods_id ) AS order_total,
	-- 计算每个产品在总销售额排名
	RANK() OVER ( ORDER BY SUM( amount ) DESC ) AS overall_rank ,
	-- 排名无间隔
	DENSE_RANK() OVER ( ORDER BY SUM( amount ) DESC ) AS overall_rank1 
FROM
	orders_detail 
GROUP BY
	goods_id,
	orders_id,
	amount;

14.2.3.  行转列查询

在MySQL中，将行转换为列是一种常见的数据重构操作，通常称为“行转列”或“PIVOT”操作。这种转换在数据展示时特别有用，尤其是当你需要将数据从长格式转换为宽格式以便于分析和报告时。

【DEMO】

-- 计算每天的销售额，按商品分列(使用case when 方式）
SELECT
	x.order_date,
	sum( x.computer_amount ),
	sum( x.mobile_amount ),
	sum( x.printer_amount ) 
FROM
	( SELECT date( a.order_date ) order_date, sum( CASE WHEN b.goods_id = 1 THEN b.amount ELSE 0 END ) AS mobile_amount, sum( CASE WHEN b.goods_id = 2 THEN b.amount ELSE 0 END ) AS computer_amount, sum( CASE WHEN b.goods_id = 3 THEN b.amount ELSE 0 END ) AS printer_amount FROM orders a INNER JOIN orders_detail b ON b.orders_id = a.id GROUP BY date( a.order_date ), b.goods_id ) x 
GROUP BY
	x.order_date;

-- 计算每天的销售额，按商品分列(sum if 方式)
SELECT
	x.order_date,
	sum( x.computer_amount ),
	sum( x.mobile_amount ),
	sum( x.printer_amount ) 
FROM
	( SELECT date( a.order_date ) order_date, sum( IF ( b.goods_id = 1, b.amount, 0 ) ) AS mobile_amount, sum( IF ( b.goods_id = 2, b.amount, 0 )) AS computer_amount, sum( IF ( b.goods_id = 3, b.amount, 0 ) ) AS printer_amount FROM orders a INNER JOIN orders_detail b ON b.orders_id = a.id GROUP BY date( a.order_date ), b.goods_id ) x 
GROUP BY
	x.order_date;

14.2.4.  多行合并查询

在MySQL中，将多行数据合并为一行通常是通过使用聚合函数和GROUP BY子句来实现的。这种类型的查询通常用于生成汇总信息，其中每一组的多行数据根据某个键（或多个键）合并为一行，并且其他列通过聚合函数（如SUM(), MAX(), MIN(), COUNT(), GROUP_CONCAT()等）进行合并。

【DEMO】

-- 使用GROUP_CONCAT取每个客户的订单号，合并到一个字段中
SELECT
	customer_id,
	GROUP_CONCAT( order_no ORDER BY order_date ASC SEPARATOR ', ' ) AS order_nos 
FROM
	orders 
GROUP BY
	customer_id;

在这个查询中，GROUP_CONCAT()函数将每个客户的订单号连接成一个由逗号分隔的字符串，并且通过ORDER BY order_date ASC确保了订单号是按照订单日期升序排列的。

【注意】

● GROUP_CONCAT()函数有一个默认的长度限制（1024字符），如果预计连接的字符串会超过这个限制，可能需要增加该函数的系统变量group_concat_max_len的值。

● 当使用GROUP_CONCAT()时，如果分组内的某个字符串列为NULL，该NULL值将被忽略，不会包含在结果字符串中。

15.  修改数据

在MySQL中，修改数据通常使用UPDATE语句。UPDATE语句用于更新表中的现有记录。使用该语句时，你需要指定要更新的表名，以及要更新的列和新的值。此外，你需要使用WHERE子句来指定哪些行需要被更新（特别要注意更新的条件）。

15.1.  条件修改

【语法】

UPDATE table_name
SET column = value, [column2 = value2, ...]
WHERE condition;

【DEMO】

-- 把男客户的年龄加1
UPDATE customer 
SET age = age + 1 
WHERE
	gender = 1；

-- 把女客户的年龄加1，并备注为VIP
UPDATE customer 
SET age = age + 1,remark='VIP' 
WHERE
	gender = 0;

-- 更新商品价格为9折
UPDATE goods set unit_price=unit_price*0.9

【注意】

● 如果省略WHERE子句，UPDATE语句将会更新表中所有行的指定列。这通常是一个错误，除非你确实想要更新所有行。

● 在执行UPDATE语句之前，建议使用SELECT语句和相同的WHERE条件来检查哪些行将被影响，以确保你不会意外更新错误的记录。

● 有些数据库配置可能会要求你在执行UPDATE语句后不能立即看到更改，因为它们使用了事务日志。确保你提交了事务（通过执行COMMIT;），或者你的数据库设置是自动提交的。

● 在更新重要数据前，建议对数据表先进行备份，以防数据被错误修改。

15.2.  联表修改

在MySQL中，联表更新（也称为跨表更新）是一种涉及两个或多个表的更新操作，其中一个表的数据被用来更新另一个表中的数据。这种操作通常使用子查询或JOIN来实现，将两个表中的数据关联起来。

【DEMO】使用子查询进行更新

-- 客户增加默认导购字段（下面的DEMO用）
ALTER TABLE customer add guide_id BIGINT；
	
-- 把最新订单中的导购更新为默认导购
UPDATE customer a SET a.guide_id =( SELECT b.guide_id FROM orders b WHERE b.customer_id = a.id AND b.guide_id IS NOT NULL ORDER BY b.order_date DESC LIMIT 1 );

【DEMO】使用JOIN进行更新

-- 把最新单价更新到订单明细的备注中
UPDATE orders_detail a
INNER JOIN goods b ON b.id = a.goods_id 
SET a.remark = CONCAT( '最新商品单价:', b.unit_price )；

-- 既更新a表，也更新b表
UPDATE orders_detail a
INNER JOIN goods b ON b.id = a.goods_id 
SET a.remark = CONCAT( '最新商品单价:', b.unit_price ),b.remark='已销售过'

【注意】

● 在执行联表更新之前，确保你完全理解两个表之间的关系，以及更新操作的影响。

● 使用子查询时，确保子查询返回的列数与外部查询中的列数相匹配。

● 在执行任何更新操作之前，建议先使用SELECT语句和相同的条件来检查哪些行将被影响，以确保你不会意外更新错误的记录。

● 确保你有适当的备份，以防万一需要恢复数据。

16.  删除数据

在MySQL中，删除数据通常使用DELETE或DELETE FROM语句。使用这些语句时，你需要指定要删除数据的表名，并且通常需要使用WHERE子句来指定哪些行需要被删除。如果不使用WHERE子句，将会删除表中的所有数据，这通常是一个严重的错误。在进行数据删除时，需要注意以下事项：

1)  使用WHERE子句：始终使用WHERE子句来限制要删除的行，除非你确实要删除表中的所有记录。

2)  检查删除条件：在执行删除操作之前，建议先使用SELECT语句和相同的WHERE条件来检查哪些行将被影响，以确保你不会意外删除错误的记录。

3)  备份数据：在执行删除操作之前，请确保你有适当的备份，以防万一需要恢复数据。

4)  使用事务：如果你在一个事务环境中工作，确保在执行删除后提交事务（通过执行COMMIT;），或者考虑使用事务的回滚功能来撤销删除操作。

5)  权限：执行删除操作需要对表有适当的权限。

6)  外键约束：如果表中的数据被其他表通过外键引用，直接删除可能会违反外键约束，导致删除操作失败。在这种情况下，你需要先删除或更新引用这些数据的外键表中的记录。

7)  性能：对于大型表，删除大量数据可能会影响性能。在这种情况下，可能需要考虑性能优化技术，如使用分区表或批量删除。

8)  删除全部数据：如果你确实需要删除表中的所有数据，可以使用TRUNCATE TABLE语句，它比DELETE无WHERE子句更快，因为它不记录每行的删除操作。

16.1.  条件删除

【语法】

DELETE FROM table_name
WHERE condition;

【DEMO】

-- 删除编号为U001的用户
DELETE 
FROM
	sys_users 
WHERE
	user_no = 'U001';

16.2.  联表删除

在MySQL中，联表删除操作涉及到使用多表之间的关系来删除一个表中满足特定条件的行。这种操作通常使用子查询或者JOIN来实现，但需要注意的是，MySQL不支持使用JOIN来直接执行删除操作。相反，通常的做法是使用子查询来指定删除条件。

【DEMO】使用子查询进行删除

-- 删除没有明细的订单头
DELETE 
FROM
	orders 
WHERE
	id NOT IN ( SELECT orders_id FROM orders_detail );

注意：

● 确保子查询正确：子查询必须返回与外部查询中使用的字段相匹配的列。在上面的例子中，子查询返回了orders_id，这必须与外部DELETE语句中的id字段相对应。

● 使用WHERE子句：始终使用WHERE子句来限制要删除的行，以避免删除错误的记录。

● 检查删除条件：在执行删除操作之前，建议先使用SELECT语句和相同的WHERE子句来检查哪些行将被影响。

● 备份数据：在执行删除操作之前，请确保你有适当的备份，以防万一需要恢复数据。

● 外键约束：如果表中的数据被其他表通过外键引用，直接删除可能会违反外键约束，导致删除操作失败。在这种情况下，你需要先删除或更新引用这些数据的外键表中的记录。

● 性能：对于大型表，删除大量数据可能会影响性能。在这种情况下，可能需要考虑性能优化技术。

● 事务：如果你在一个事务环境中工作，确保在执行删除后提交事务（通过执行COMMIT;），或者考虑使用事务的回滚功能来撤销删除操作。

● 权限：执行删除操作需要对表有适当的权限。

【DEMO】使用JIOIN进行删除

-- 删除没有录入客户的订单头
DELETE o 
FROM
	orders AS o
	LEFT JOIN customer AS c ON o.customer_id = c.id 
WHERE
	c.id IS NULL;

【DEMO】使用exists进行判断

-- 删除没有下过单的客户
DELETE customer 
WHERE
	NOT EXISTS ( SELECT 1 FROM orders WHERE orders.customer_id = customer.id );

16.3.  整表删除

整表删除指的是删除表中的所有数据，但保留表结构。这可以通过几种不同的方法实现：

● TRUNCATE TABLE语句：这是删除整表数据最快的方法，因为它不记录每行的删除操作，而是直接重置表，删除所有行，并且速度非常快。请注意，TRUNCATE TABLE只能用于没有外键约束的MYISAM或INNODB表。

● DELETE语句：与TRUNCATE TABLE相比，这种方法会慢得多，尤其是对于大表，因为它会记录每一行的删除操作。使用DELETE语句的好处是，如果你有外键约束，它仍然可以工作，而TRUNCATE TABLE则不能。

【语法】

-- 清空所有记录，无日志，速度非常快
TRUNCATE TABLE table_name;
-- 清空所有记录，有日志
DELETE FROM table_name;

重置ID：如果你使用的是自增主键，并且你想要重置自增计数器（例如，从1开始），你可以使用以下方法：

ALTER TABLE table_name ORDER BY primary_key_column DESC LIMIT 1 NO WRITE TO BINLOG;

【注意】

● 在执行整表删除之前，请确保你确实希望删除所有数据，因为这将是一个不可逆的操作。

● 确保你有足够的权限来执行删除操作。

● 如果表中的数据量很大，那么删除操作可能需要一些时间来完成。

● 如果你计划删除表中的所有数据并用新数据重新填充，使用TRUNCATE TABLE是最快的方法。

● 在执行整表删除操作之前，请确保你已经对重要数据进行了备份，以防意外删除导致数据丢失。

17.  MySQL的日期

17.1.  日期格式

MySQL 支持多种日期格式，可以根据需要灵活地进行日期和时间操作。以下是一些常用的日期格式：

1.  ‘YYYY-MM-DD’：这种格式表示年-月-日，例如 ‘2022-09-15’。在 MySQL 中，这种格式可以直接用于日期类型的字段，例如 DATE、DATETIME 和 TIMESTAMP。

2.  ‘YYYY-MM-DD HH:MI:SS’：这种格式表示年-月-日 时:分:秒，例如 ‘2022-09-15 14:30:00’。在 MySQL 中，这种格式可以直接用于 DATETIME 和 TIMESTAMP 字段。

3.  ‘YYYY-MM-DD HH:MI:SS.sss’：这种格式表示年-月-日 时:分:秒.毫秒，例如 ‘2022-09-15 14:30:00.123’。在 MySQL 中，这种格式可以用于 DATETIME 和 TIMESTAMP 字段，但需要注意的是，MySQL 只保留前 6 位小数，多余的小数部分将被截断。

4.  ‘YYYY-MM-DDTHH:MI:SS’：这种格式表示年-月-日 时:分:秒，例如 ‘2022-09-15T14:30:00’。在 MySQL 中，这种格式可以用于 DATETIME 和 TIMESTAMP 字段，且不受时区影响。

5.  ‘YYYY-MM-DDTHH:MI:SS.sss’：这种格式表示年-月-日 时:分:秒.毫秒，例如 ‘2022-09-15T14:30:00.123’。在 MySQL 中，这种格式可以用于 DATETIME 和 TIMESTAMP 字段，但需要注意的是，MySQL 只保留前 6 位小数，多余的小数部分将被截断。

6.  时间戳格式：MySQL 支持将时间戳（从 1970 年 1 月 1 日 00:00:00 UTC 开始的秒数）直接转换为日期和时间。例如，将时间戳 1663300400 转换为日期和时间：

SELECT FROM_UNIXTIME(1663300400);

这将返回 ‘2022-09-16 03:53:20’。

在 MySQL 中，可以使用 DATE()、TIME()、DATETIME() 和 TIMESTAMP() 函数将不同的日期格式转换为其他格式。同时，也可以使用 STR_TO_DATE() 和 DATE_FORMAT() 函数将字符串转换为日期格式。

17.2.  当前日期

在 MySQL 中，可以使用多种方法获取当前日期。以下是一些常用的方法：

1.  使用 NOW() 函数：NOW() 函数返回当前的日期和时间。如果你只需要日期部分，可以使用 DATE() 函数将 NOW() 函数返回的结果转换为日期格式。例如：

SELECT DATE(NOW());

这将返回当前日期，例如 ‘2022-09-15’。

2.  使用 CURRENT_DATE 关键字：CURRENT_DATE 关键字返回当前的日期。与 NOW() 函数类似，如果你只需要日期部分，可以使用 DATE() 函数将 CURRENT_DATE 返回的结果转换为日期格式。例如：

SELECT DATE(CURRENT_DATE);

这将返回当前日期，例如 ‘2022-09-15’。

3.  使用 CURRENT_TIMESTAMP 关键字：CURRENT_TIMESTAMP 关键字返回当前的日期和时间。如果你只需要日期部分，可以使用 DATE() 函数将 CURRENT_TIMESTAMP 返回的结果转换为日期格式。例如：

SELECT DATE(CURRENT_TIMESTAMP);

这将返回当前日期，例如 ‘2022-09-15’。

4.  使用 SELECT 语句：你可以直接使用 SELECT 语句获取当前日期。例如：

SELECT '2022-09-15';

这将返回当前日期，例如 ‘2022-09-15’。

请注意，这些方法返回的日期格式可能与你的应用程序或表的日期格式不同。因此，在将结果插入到数据库或显示给用户之前，可能需要使用 DATE_FORMAT() 函数将日期格式化为你需要的格式。例如，将日期格式化为 ‘YYYY-MM-DD’ 格式：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

这将返回当前日期，例如 ‘2022-09-15’。

17.3.  日期计算

SELECT DATE_ADD(日期, INTERVAL 值 INTERVAL 类型);
SELECT DATE_SUB(日期, INTERVAL 值 INTERVAL 类型);

【DEMO】

SELECT DATE_ADD('2024-05-09', INTERVAL 1 DAY)
UNION
SELECT DATE_SUB('2024-05-09', INTERVAL 1 MONTH);

17.4.  常用日期函数

● 获取当前日期和时间：

■ CURDATE()：返回当前日期。

■ CURTIME()：返回当前时间。

■ NOW()：返回当前日期和时间。

● 日期和时间的格式化：

■ DATE_FORMAT(date, format)：将日期按照指定的格式返回字符串。

● 日期和时间的计算：

■ DATE_ADD(date, INTERVAL value unit)：在日期上添加指定的时间量。

■ DATE_SUB(date, INTERVAL value unit)：在日期上减去指定的时间量。

● 日期和时间的比较：

■ 使用比较运算符（=, <, >, <=, >=, != 或 <>）来比较日期和时间。

● 日期和时间的提取：

■ YEAR(date)：从日期中提取年份。

■ MONTH(date)：从日期中提取月份。

■ DAYOFMONTH(date)：从日期中提取月份中的天数。

■ HOUR(time)：从时间中提取小时。

■ MINUTE(time)：从时间中提取分钟。

■ SECOND(time)：从时间中提取秒。

● 日期和时间的转换：

■ STR_TO_DATE(string, format)：将字符串转换为日期。

■ DATEDIFF(date1, date2)：返回两个日期之间的天数差。

■ TIMEDIFF(time1, time2)：返回两个时间之间的时间差。

● 日期范围和周期：

■ LAST_DAY(date)：返回包含日期的月份的最后一天。

■ DAYOFWEEK(date)：返回日期是星期几（2 = 星期一，...，1 = 星期日）。

● 时间戳：

■ UNIX_TIMESTAMP()：返回一个 UNIX 时间戳（自 1970-01-01 00:00:00 UTC 以来的秒数）。

■ FROM_UNIXTIME(unix_timestamp)：将 UNIX 时间戳转换为日期。

● 周期性日期：

■ WEEKDAY(date)：返回日期是星期几（0 = 星期一，...，6 = 星期日）。

第四章 数据库进阶操作

DCL是Data Control Language（数据控制语言）的缩写，在SQL中主要用于管理数据库的访问权限和事务处理。虽然DCL并不直接影响MySQL中唯一列索引的创建或使用，但数据库权限的设置可能会影响谁可以执行涉及唯一列索引的DDL或DML操作。

18.  数据库排序规则

数据库排序规则（Collation）主要指的是数据库系统中对字符串数据进行比较、排序和存储的规则。排序规则不仅决定了字符的排序顺序，而且还影响到字符串的相等性和相似性测试，包括但不限于以下方面：

1.  字符集（Character Set）：排序规则基于特定的字符集，字符集定义了数据库能识别和存储的所有字符的集合。

2.  大小写敏感性：某些排序规则区分大小写，另一些则不区分。例如，有的规则会认为"A"和"a"是相同的，而在区分大小写的规则下则认为两者不同。

3.  重音（ Accent）敏感性：有些排序规则区分重音符号，即同一个字母带重音和不带重音会被视为不同的字符。

4.  特殊字符处理：排序规则还规定了特殊字符（如标点符号、空白字符等）的处理方式。

5.  排序顺序：确定字符间的排序顺序，如拉丁字母表顺序、西里尔字母表顺序、汉字笔画顺序等。

在SQL查询中，排序规则直接影响到ORDER BY子句的执行结果。例如，在创建表、索引或者执行查询时，可以指定排序规则来确保数据的正确排序和比较。MySQL允许用户在服务器、数据库、表或列级别指定排序规则，以适应不同的应用场景和需求。在查询中，也可以临时覆盖列的默认排序规则。

MySQL 提供了众多的排序规则（Collations），它们与字符集（Character Sets）一起使用，共同决定了数据库中字符串数据的比较、排序和存储方式。这里列出一些常见的MySQL排序规则类别和实例：

1.  ASCII 字符集排序规则：

i.  ascii_bin

ii.  ascii_general_ci

2.  UTF-8 字符集排序规则：

i.  utf8_bin：二进制排序，区分大小写，且对每个字符逐字节比较。

ii.  utf8_general_ci：一般用途，不区分大小写，对于大多数拉丁字符族的字母和数字提供合理的排序。

iii.  utf8_unicode_ci：基于Unicode Collation Algorithm（UCA），提供更广泛的国际化排序，同样不区分大小写。

iv.  utf8mb4 系列的排序规则，用于支持4字节的Unicode字符（例如：表情符号）。

3.  Latin系列字符集排序规则：

i.  latin1_swedish_ci

ii.  latin1_general_ci

iii.  latin1_bin

4.  其他国际字符集排序规则：

i.  utf8mb4_unicode_ci

ii.  utf8mb4_0900_ai_ci（MySQL 8.0起的推荐排序规则，AI代表人工智能优化）

iii.  utf8_unicode_520_ci

iv.  ucs2_general_ci

v.  gbk_chinese_ci（用于简体中文）

vi.  big5_chinese_ci（用于繁体中文）

vii.  utf8mb4_japanese_ci（用于日语）

viii.  utf8mb4_debian_ci（Debian项目定制的排序规则）

每种排序规则都具有独特的特性和适用场景，例如，那些以 _ci 结尾的通常是大小写不敏感（case-insensitive），而以 _cs 结尾的则是大小写敏感（case-sensitive）。根据实际需求选择合适的排序规则非常重要，尤其是当涉及到多语言环境和精确字符串比较时。您可以通过 SHOW COLLATION SQL命令查看MySQL服务器支持的所有排序规则及其详细信息。

19.  索引

MySQL索引是一种数据结构，用于加快数据库表中数据的检索速度。索引类似于书籍的目录，可以帮助数据库管理系统快速定位到特定的数据行。索引虽然可以显著提高查询效率，但也会增加插入、删除和更新操作的开销，因为索引本身也需要维护。

19.1.  MySQL索引类型

主要包括：

● 普通索引 (Basic Index)：最基本的索引类型，没有任何限制，允许重复值。

● 唯一索引 (Unique Index)：索引列的值必须是唯一的，除了NULL外，不能有两个相同的值。插入新记录时，如果有重复的索引值，则会导致错误。

● 主键索引 (Primary Key Index)：一种特殊的唯一索引，用于标识表中每条记录的唯一性。每个表只能有一个主键，主键列不允许有NULL值。

● 组合索引 (Composite/Index/Multi-column Index)：对多个列创建一个索引，MySQL会按照索引定义的顺序考虑这些列。在查询中，如果查询条件匹配了索引的第一列并且可能也匹配了后面的列，则可以充分利用这个索引。

● 全文索引 (Full-text Index)：适用于大型文本字段，允许对文本中的单词进行全文搜索。在MySQL 5.6及以后的InnoDB存储引擎中，也开始支持全文索引。

● 哈希索引 (Hash Index)：MySQL InnoDB引擎支持自适应哈希索引（自动生成并管理），用户也可在MEMORY存储引擎中手动创建哈希索引，哈希索引主要用于等值查询，但无法支持范围查询或排序。

● B-Tree索引 (B-Tree Index)：MySQL中最常用的一种索引结构，它是一个平衡多路查找树，在大部分情况下都能提供高效的检索性能。

● R-Tree索引 (R-Tree Index)：主要应用于GIS数据类型，适合用于地理空间数据的索引。

19.2.  索引的工作原理

B-Tree索引通常将数据以有序的方式存储在磁盘上，这样在查找、排序或范围查询时，无需扫描整个表，而是通过索引直接定位到所需数据的位置。

19.3.  索引的注意事项

● 创建索引可以提高查询速度，但会增加存储空间并可能影响插入、删除和更新操作的速度（因为需要维护索引结构）。

● 不是所有的查询都适合建立索引，尤其是那些涉及少量数据或频繁更新的表，应谨慎选择索引策略。

● 索引的选择应根据实际的查询模式和数据分布来决定。

● 索引并不能解决所有的性能问题，合理的数据库设计和查询优化同样重要。

19.4.  如何使用索引

● 在WHERE子句中经常被作为条件的列创建索引。

● 对于JOIN操作，应该在联接字段上创建索引。

● 避免在具有高基数（唯一值数量很多）的列上创建索引，尤其是在小型表上，效果可能并不明显。

● 查询语句中，尽量遵循“左前缀原则”使用组合索引，即查询条件应从索引左侧开始。

● 总之，合理使用索引能够显著提升数据库系统的整体性能，但在实际应用中，需要结合具体场景权衡利弊。

19.5.  指定索引

在某些情况下，如果你认为优化器没有选择最佳的索引，可以使用FORCE INDEX来强制查询使用一个特定的索引。FORCE INDEX是对于SELECT、DELETE和UPDATE语句的提示。

【语法】

SELECT *
FROM your_table
USE INDEX (index_name1, index_name2)
WHERE condition;

【注意】

● 使用FORCE INDEX或USE INDEX可能在某些情况下提高性能，但也可能降低性能。它们应该谨慎使用，并且最好在充分测试和基准测试之后使用。

● 强制使用索引可能会绕过优化器的正常决策过程，如果不正确使用，可能会导致性能下降。

● 优化器通常非常擅长选择最佳的索引，所以除非你有充分的理由相信优化器的选择不是最优的，否则最好让优化器自己做决定。

● 在某些存储引擎（如InnoDB）中，DELETE和UPDATE操作可能会自动使用到主键索引，即使没有明确指定。

● 如果你经常需要使用FORCE INDEX或USE INDEX，这可能是一个信号，表明数据库设计 或查询逻辑需要重新评估。

● 确保统计信息是最新的：MySQL优化器依赖于统计信息来做出决策。运行ANALYZE TABLE可以更新这些统计信息。

● 调整查询逻辑：有时候，通过改变查询逻辑，可以使优化器更容易选择正确的索引。

● 使用合适的数据类型：确保列的数据类型与索引的数据类型一致，以避免类型转换影响索引的使用。

● 考虑索引的选择性：索引的选择性是指索引中唯一值的数量与表中行的总数的比率。高选择性的索引通常更有效。

● 避免使用复杂的表达式：在WHERE子句中使用索引列的复杂表达式或函数可能会阻止索引的使用。

19.6.  索引的维护和最佳实践

为了保持索引的有效性和性能，需要定期维护索引。这包括重建损坏的索引、删除不再需要的索引以及调整索引以适应数据分布的变化。

最佳实践包括：

● 在高选择性的列上创建索引，即那些返回结果集较小的列。

● 避免在低选择性的列上创建索引，因为它们可能不会显著提高查询性能。

● 考虑使用复合索引，特别是当查询条件包含多个列时。

● 避免在经常变动的列上创建索引，因为这会增加写操作的成本。

● 使用EXPLAIN命令来分析查询计划，以确定是否有效地使用了索引。

● 请注意，索引的设计和使用应该根据具体的应用场景和数据模式来定制。在实际使用中，可能需要进行多次尝试和调整，以达到最优的性能。

20.  动态SQL

在MySQL中，动态SQL指的是在运行时构建和执行SQL语句的过程，而不是执行静态的、预先定义好的SQL语句。动态SQL常用于编程中，尤其是当SQL查询的某些方面依赖于用户输入或程序逻辑时。

预处理语句是一种执行动态SQL的常用方法，它允许你使用参数化查询来避免SQL注入攻击。在MySQL中，你可以使用PREPARE, EXECUTE, DEALLOCATE PREPARE语句来使用预处理语句。

【使用场景】

● 多条件查询：根据用户输入的不同条件动态构造 WHERE 子句。

● 批量操作：比如根据一个 ID 列表执行批量的更新或删除操作。

● 存储过程：在存储过程中根据传入参数构建不同的 SQL 语句。

● 循环和条件判断：在存储过程中根据循环或条件判断的结果动态执行不同的 SQL。

【语法】

SET @sql = 'SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

【DEMO】

SET @sql = CONCAT('SELECT * FROM users WHERE name = ?', 'John');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

在这个例子中，CONCAT 函数用来构造 SQL 字符串，其中 ? 是预处理语句的参数占位符，随后通过 PREPARE 准备语句，用实际值通过 EXECUTE 执行。

【注意】

● SQL注入：直接拼接字符串构建SQL语句可能导致SQL注入攻击。始终使用预处理语句或参数化查询来避免这种风险。

● 性能：预处理语句可以提高性能，因为它们允许MySQL缓存查询计划。

● 灵活性：动态SQL提供了灵活性，允许你根据不同的条件构建不同的查询。

21.  事务

MySQL中的事务是数据库操作的一个基本单位，它包含一条或多条SQL语句，这些语句要么全部执行成功，要么全部不执行。事务的这种特性称为原子性（Atomicity）。此外，事务还具有一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这四个特性统称为ACID特性。

MySQL事务的基本操作：

● 开启事务：使用START TRANSACTION;或BEGIN;命令开启一个新的事务。

● 执行事务中的SQL语句：在事务中，你可以执行多个SQL语句，如INSERT、UPDATE、DELETE等。

● 提交事务：如果所有语句都执行成功，使用COMMIT;命令来提交事务，这将使事务中的所有更改永久生效。

● 回滚事务：如果在事务中的某个操作失败，你可以使用ROLLBACK;命令来回滚事务，撤销事务中的所有更改。

MySQL事务的隔离级别：

MySQL支持不同的事务隔离级别，用于解决事务并发执行时可能出现的问题，如脏读、不可重复读和幻读。隔离级别可以通过以下命令设置：

SET SESSION TRANSACTION ISOLATION LEVEL [隔离级别];

常见的隔离级别包括：

● READ UNCOMMITTED：最低级别，允许脏读。

● READ COMMITTED：允许不可重复读。

● REPEATABLE READ：默认级别，防止不可重复读，但允许幻读。

● SERIALIZABLE：最高级别，完全串行化执行事务，性能较低。

注意事项：

(1)  在使用事务之前，需要确保数据库支持事务，如使用InnoDB存储引擎。

(2)  事务只在当前会话中有效，其他会话不受影响。

示例：

-- 1.开启事务
START TRANSACTION;
-- 2.执行SQL语句
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 3.提交事务
COMMIT;
-- 4.如果有错误发生，可以回滚
ROLLBACK;

在上述示例中，两个UPDATE操作被视为一个事务，如果第一个操作成功而第二个操作失败，则可以使用ROLLBACK来撤销整个事务。

22.  MySQL定时任务

MySQL的定时任务通常指的是使用MySQL的事件调度器（Event Scheduler）来创建的周期性任务，这些任务可以自动执行特定的SQL语句。以下是关于MySQL定时任务的一些关键信息和操作步骤：

1.  事件调度器的开启与关闭： 在开始使用事件之前，需要确保事件调度器是开启的。可以通过以下命令来检查和开启事件调度器：

SHOW VARIABLES LIKE 'event_scheduler';

SET GLOBAL event_scheduler = ON;

```【4】

2.  创建定时任务（事件）： 使用CREATE EVENT语句来创建一个新的事件。以下是创建事件的基本语法：

CREATE EVENT event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT 'comment']

DO event_body;

其中schedule可以是一次性的（使用AT）或者是周期性的（使用EVERY），event_body是要执行的SQL语句或存储过程。3

3.  查看当前系统有的事件： 你可以通过以下命令来查看当前系统中已经定义的事件：

SELECT * FROM mysql.event;

SELECT * FROM information_schema.events;

```【3】

4.  修改和删除事件： 使用ALTER EVENT来修改现有事件的属性，如调度计划或事件主体。使用DROP EVENT来删除一个事件：

ALTER EVENT event_name ON SCHEDULE schedule;

DROP EVENT event_name;

```【3】

5.  事件的调度时间设置： 调度时间可以非常灵活，包括单次执行（AT）和周期性执行（EVERY），并可以指定开始和结束时间。时间单位可以是YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, WEEK, SECOND等。1

6.  持久化配置： 为了让事件在MySQL重启后依然能够运行，需要将event_scheduler=1写入到MySQL的配置文件中，如my.cnf。5

7.  应用案例： 事件可以用于多种应用场景，例如数据备份、清理过期数据、定期更新统计信息等。2

8.  注意事项：

a.  事件的创建和修改需要具有相应的权限。

b.  在设置周期性任务时，需要注意时间的准确性和周期性，避免出现不必要的资源消耗。

c.  对于不再需要的事件，应及时删除以保持数据库的整洁。

以上信息汇总了MySQL定时任务的创建、查看、修改和删除的基本操作，以及一些使用时的注意事项。在实际应用中，应根据具体的业务需求来设计和调整定时任务。

23.  临时表

在MySQL中，临时表是一种特殊的表，它在当前会话中存在，并且在会话结束时自动删除。临时表可以用于存储中间结果集，以便在查询过程中进行进一步的数据处理。以下是一些关于MySQL中临时表的重要概念和使用场景：

【用途】

● 数据清洗：在进行数据分析之前，您可以使用临时表来存储清洗后的数据。

● 复杂的JOIN操作：在执行多个表的复杂连接操作时，可以先将部分连接结果存储在临时表中，以简化查询。

● 存储中间结果：在执行一系列操作时，可以使用临时表来存储中间结果，以便后续查询使用。

【限制】

● 临时表不支持FULLTEXT类型的索引。

● 临时表不支持外键约束。

【特点】

● 生命周期短暂：临时表仅在创建它的会话（session）或连接期间存在。一旦会话结束，无论是正常关闭还是意外断开，MySQL 都会自动删除临时表及其数据，无需手动清理，从而释放占用的空间。

● 会话隔离性：每个会话（即数据库连接）都有自己的临时表空间，因此即使多个会话创建了同名的临时表，这些表之间也是互不可见的，确保了数据的隔离性。

● 自动管理：除了手动创建外，MySQL 优化器在处理复杂查询时也可能自动生成临时表以辅助操作，如执行排序、分组、去重等操作，这些由系统生成的临时表同样遵循会话结束即删除的原则。

● 创建方式：可以通过 CREATE TEMPORARY TABLE 语句来显式创建一个临时表，这种方式允许用户定义表结构、选择存储引擎等。此外，还可以通过 SELECT ... INTO OUTFILE 创建临时表来保存查询结果。

● 显式删除：尽管MySQL会在会话结束时自动删除临时表，但在某些情况下，如使用线程池或者需要在会话结束前提前释放资源时，显式地使用 DROP TABLE 语句来删除临时表是一个好习惯。

● 存储引擎：与永久表一样，临时表可以选择不同的存储引擎，但通常使用内存表（MEMORY 存储引擎）以提高处理速度，尤其是在处理大量数据的临时运算时。

23.1.  创建临时表

-- 从现有表进行创建
CREATE TEMPORARY TABLE temp_table_name
 LIKE existing_table_name;
    
--通过sql查询进行创建 
 CREATE TEMPORARY TABLE temp_table_name
 SELECT * FROM existing_table_name
 WHERE condition;
 
 -- 通过sql创建
 CREATE TEMPORARY TABLE temp_table_name(
     id int ,
     name varchar(100),
     age int
 );

23.2.  使用临时表

【DEMO】

CREATE TEMPORARY TABLE temp_avg_sales
    SELECT AVG(sales) AS average_sales FROM sales_data;

SELECT *
FROM sales_data
JOIN temp_avg_sales
ON sales_data.sales > temp_avg_sales.average_sales;

在上述示例中，temp_avg_sales 临时表用于存储平均销售额，然后在主查询中与 sales_data 表进行连接，以筛选出销售额超过平均值的记录。

24.  分区表

MySQL 分区表是一种将大型表的数据分散存储在不同的分区中的技术，这样可以提高查询性能和管理的便捷性。分区表对于处理大型数据集特别有用，因为它们可以减少查询所需的I/O操作，从而加快查询速度。

【分区类型】

● RANGE分区：基于连续的值范围进行分区。

● LIST分区：基于离散的值列表进行分区。

● HASH分区：基于HASH函数将数据分配到分区。

● KEY分区：类似于HASH分区，但使用一个索引列的HASH值。

【语法】

CREATE TABLE table_name (
    column_list
) ENGINE = engine_type
PARTITION BY partition_type (
    partition_expression
) (
    PARTITION partition_name VALUES IN (value_list) ENGINE = engine_type,
    ...
);

【DEMO】

CREATE TABLE sales_data (
    sale_id INT AUTO_INCREMENT,
    product_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (sale_id, sale_date)
) ENGINE = INNODB
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

在这个例子中，我们创建了一个名为sales_data的表，并且根据sale_date列的年份进行了RANGE分区。

【限制】

● 分区表的所有分区必须使用相同的存储引擎。

● 分区键（用于分区的列）必须是表中索引的一部分。

● 不能在分区表上创建外键约束。

【操作】

● 查询：对分区表的查询操作与普通表相同，MySQL会在内部优化查询以仅扫描相关的分区。

● 维护：可以使用ALTER TABLE语句对分区表进行维护操作，如添加、删除或重命名分区。

【性能】

(1)  查询优化：分区可以显著提高查询性能，特别是当查询条件与分区键相关时。

(2)  写入性能：对于写入密集型的应用，分区可能不会带来太大的性能提升，因为数据需要写入多个分区。

(3)  维护成本：分区表可能会增加维护的复杂性，因为需要考虑分区的策略和分区表的维护。

【分区因素】

(1)  数据量：对于非常大的表，分区可以提高查询性能。

(2)  查询模式：如果查询经常针对表的特定范围或值进行，使用RANGE或LIST分区可能会很有帮助。

(3)  维护资源：确保有足够的资源来维护分区表，包括时间、技能和工具。

分区表是MySQL中一个强大的特性，可以显著提高大型表的性能。然而，它们也带来了额外的复杂性，因此在决定使用分区表之前，需要仔细评估其优势和成本。分区并不总是提高性能，对于小表或者不恰当的分区设计可能反而降低效率。分区表的维护和管理需要额外的规划和监控。

25.  性能优化

25.1.  优化查询

● 使用索引

● 减少 JOIN 操作

● 避免使用 SELECT *

● 分页查询

25.2.  优化表结构

● 使用合适的数据类型

● 避免使用 NULL 值

● 使用外键约束

● 拆分大表

25.3.  优化存储引擎

● 选择合适的存储引擎

● 调整 InnoDB 参数

第五章 数据库运维

26.  启动数据库

1.  在 Linux 上使用以下命令启动 MySQL：

sudo systemctl start mysql

2.  在 Windows 上启动MySQL（mysql80 为安装时设置的服务名称）

sc start mysql80

在 Windows中，也可以在服务管理器中启动 MySQL数据。

27.  停止数据库

(1)  在 Linux 上使用以下命令停止 MySQL：

sudo systemctl stop mysql

(2)  在 Windows 上停止数据库（mysql80 为安装时设置的服务名称）

sc stop mysql80

在 Windows中，也可以在服务管理器中停止 MySQL 数据库。

28.  备份数据库

【语法】使用以下命令备份数据库(bash中操作)：

mysqldump -u 用户名 -p  -h 主机名 数据库名 > 备份文件名.sql

其中 用户名是你的MySQL用户名，主机名 是数据库服务器的地址，数据库名 是你想要备份的数据库名称，备份文件名.sql 是将生成的备份文件。

【DEMO】

mysqldump -u root -p -h localhost db_learn > mydb_backup.sql

上面的命令录入后会出现密码的录入提示，录入密码（无显示），备份完成后形成mydb_backup.sql文件。

29.  还原数据库

【语法】使用以下命令还原数据库：

mysql -u 用户名 -p 密码 -h 主机名 数据库名 < 备份文件名.sql

【DEMO】

mysql -u root -p  -h localhost db_learn < mydb_backup.sql

当提示输入密码时，输入密码（密码不会显示）。

【注意】

● 确保MySQL服务正在运行，还需要确认备份的数据是需要的。

● 替换 用户名、数据库名 和 mydb_backup.sql 为实际的用户名、数据库名和备份文件路径。

● 如果备份文件中包含了创建数据库的语句，你不需要事先手动创建数据库，但要确保在导入时指定正确的数据库名或使用 CREATE DATABASE IF NOT EXISTS 以防万一。

● 检查备份文件的权限，确保MySQL用户有权限读取该文件。

● 根据实际情况，可能需要调整命令中的数据库字符集等设置。

30.  权限管理

MySQL的权限管理是数据库安全的重要组成部分，它允许你控制不同用户对数据库和数据的访问权限。

【类型】

● 全局权限：主要用于管理系统模块，这些权限涵盖了对MySQL服务器整体的操作和管理，与具体的数据库或对象无关，因此在授权时需要指定为*.*。

● 数据库权限：是用于管理数据库，这些权限针对特定数据库，允许用户执行与该数据库相关的操作，例如创建、修改、删除数据库等。在授权时需指定database_name.*。

● 对象权限：是用于管理数据库对象，涉及对具体数据库对象（如表、字段）的权限管理，允许用户对特定对象执行特定操作，例如对表进行查询、更新或删除操作。授权时需指定为database_name.table_name或database_name.table_name.column_name。

权限名称	授权范围	描述
ALL	全局权限	实例所有权限
CREATE ROLE	全局权限	创建数据库角色
CREATE TABLESPACE	全局权限	创建表空间
CREATE USER	全局权限	创建数据库用户
DROP ROLE	全局权限	删除数据库角色
PROCESS	全局权限	查看实例中的所有session(show processlist)
		默认只能看登录用户的session
PROXY	全局权限
RELOAD	全局权限	执行下面的操作需要reload权限
		flush privileges
		flush logs
REPLICATION CLIENT	全局权限	查看复制信息的权限(show slave status)
REOLICATION SLAVE	全局权限	复制权限（从主库复制数据）
SHUTDOWN	全局权限	关闭实例
SUPER	全局权限	超级权限
		kill任何用户的session
		修改参数
		管理复制(start slave,change master等）
USAGE	全局权限	无任何权限
SHOW DATABASES	全局权限	查看数据库列表
		show databases
CREATE	数据库权限	创建数据库、表
INDEX	对象权限	创建索引（create index)
CREATE VIEW	对象权限	创建视图
CREATE ROUTINE	数据库权限	创建存储过程、函数
DROP	数据库权限	删除表、视图、存储过程、触发器、定时任务
ALTER	对象权限	修改表结构和表的索引
		即使没有index权限，也可以使用alter语句添加或删除索引
		修改表名称时，同时需要drop权限
ALTER ROUTINE	对象权限	修改存储过程、函数
EVENT	全局权限	创建、删除调度任务
TRIGGER	对象权限	创建触发器的权限
SELECT	对象权限	查询数据
INSERT	对象权限	插入数据
UPDATE	对象权限	更新数据
DELETE	对象权限	删除数据
EXECUTE	对象权限	执行存储过程的权限
REFERENCE	数据库权限	外键引用权限
LOCK TABLES	数据库权限	执行lock tables权限，需要同时对表有select权限
SHOW VIEW	对象权限	查看视图定义（show create view)

30.1.  创建用户

【语法】

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

其中，username 是用户名，host 指定该用户可以从哪个主机连接（可以是IP地址、域名或%表示任何主机），password 是用户密码。

【DEMO】

-- 创建用户
CREATE USER 'username'@'%' IDENTIFIED BY 'Pwd@123456';

30.2.  修改密码

【语法】

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

【DEMO】

-- 修改密码
ALTER USER 'username'@'%' IDENTIFIED BY 'Pwd@12345';

30.3.  用户授权

【语法】

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'host';

这里SELECT, INSERT, UPDATE是授予的权限，database_name是要授予权限的数据库名，*表示该数据库下的所有表。

【DEMO】

-- 用户授权
GRANT SELECT, INSERT, UPDATE ON *.* TO 'username'@'%';

30.4.  刷新权限

在MySQL中，FLUSH PRIVILEGES命令用于重新加载授权表，这些授权表存储了用户权限信息。当你手动修改了MySQL的授权表（例如，通过直接编辑mysql.user表或使用GRANT、REVOKE等SQL命令）后，你需要执行FLUSH PRIVILEGES来使这些更改生效。

注意，从MySQL 5.7.6开始，如果使用CREATE USER、ALTER USER、DROP USER、RENAME USER、CREATE ROLE、ALTER ROLE、DROP ROLE、GRANT或REVOKE等命令修改了权限，MySQL会自动重新加载授权表，因此通常不需要手动执行FLUSH PRIVILEGES。但是，如果你直接编辑了授权表或使用了不支持自动重新加载的命令，仍然需要手动执行该命令。

【语法】

FLUSH PRIVILEGES;

30.5.  查看用户权限

【语法】

SHOW GRANTS FOR 'username'@'host';

【DEMO】

-- 查询用户权限
SHOW GRANTS FOR 'username'@'%';

30.6.  撤销权限

【语法】

REVOKE SELECT, INSERT, UPDATE ON database_name.* FROM 'username'@'host';

【DEMO】

-- 撤销用户权限
REVOKE SELECT ON *.* FROM 'username'@'%';

上面的命令执行后，用户仅剩余SELECT权限。

30.7.  删除用户

【语法】

DROP USER 'username'@'host';

【DEMO】

-- 删除用户username
DROP USER 'username'@'%';

31.  参数设置

在MySQL中，参数设置是通过配置文件或使用SQL命令来完成的。这些参数可以影响数据库服务器的行为和性能。MySQL的参数设置是优化数据库性能和调整行为的关键环节，可以通过多种方式来实现，包括但不限于会话级别、全局级别以及通过配置文件。

● 会话级别的参数设置只影响当前连接的会话，当会话断开时，设置将会失效。

● 全局级别的参数设置会影响所有新建立的连接，但已存在的会话不受影响。 MySQL实例重启后，全局参数设置会恢复到配置文件中指定的值。

● 修改MySQL的配置文件（如my.cnf或my.ini，取决于操作系统）是最持久的方 式，适用于需要永久改变参数值的情况。修改后需要重启MySQL服务使设置生效。

31.1.  查看参数

【语法】

-- 查看所有参数
SHOW VARIABLES;

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

31.2.  内存参数

MySQL中的内存参数对于数据库的性能和稳定性至关重要。以下是一些常见的内存参数及其作用：

(1)  innodb_buffer_pool_size

这是InnoDB存储引擎用来缓存数据和索引的缓冲池大小。它是最重要的内存参数之一。默认情况下，这个值是数据库服务器启动时可用内存的一半。

(2)  innodb_log_file_size

指定InnoDB重做日志文件的大小。重做日志用于确保事务的持久性和崩溃恢复。

(3)  innodb_flush_log_at_trx_commit

控制何时将InnoDB的重做日志从缓冲池刷新到磁盘上。它可以设置为0、1或2，影响性能和数据的安全性。

(4)  query_cache_size

在MySQL 5.7及更早版本中，这个参数控制查询缓存的大小。查询缓存可以在某些情况下提高性能，但在5.7.20及以后版本中默认被禁用，并且在MySQL 8.0中已被完全移除。

(5)  max_connections

定义了数据库可以同时处理的最大客户端连接数。

(6)  thread_cache_size

定义了线程缓存的大小，用于缓存线程，减少创建和销毁线程的开销。

(7)  table_open_cache

定义了服务器可以同时打开的表的数量。这个值越大，可以同时打开的表就越多。

(8)  binlog_cache_size

二进制日志缓存的大小，用于缓存二进制日志在发送到磁盘之前的数据。

(9)  join_buffer_size

用于JOIN操作的缓冲区大小，特别是对于嵌套循环连接算法。

(10)  sort_buffer_size

用于排序操作的缓冲区大小。

(11)  read_buffer_size

用于读取操作的缓冲区大小。

(12)  read_rnd_buffer_size

用于RANDOM访问读取操作的缓冲区大小。

(13)  key_buffer_size

对于MyISAM存储引擎，这个参数控制了索引缓存的大小。对于InnoDB，它已经被innodb_buffer_pool_size取代。

(14)  tmp_table_size

用户会话级别的临时表可以分配的最大内存大小。

(15)  max_heap_table_size

创建的内存或磁盘上的临时表的最大大小。

调整这些参数时，需要根据服务器的硬件资源、工作负载和性能要求来综合考虑。不恰当的参数设置可能会导致性能下降或内存溢出等问题。在调整参数后，通常需要重启MySQL服务来使更改生效，除非是使用SET GLOBAL命令修改的动态参数。

31.3.  连接数参数

在MySQL中，控制连接数的参数主要有以下几个：

1.  max_connections

这个参数指定了MySQL服务器可以同时处理的客户端连接数。增加这个值可以允许更多的用户同时连接到数据库，但也可能增加服务器的内存使用和上下文切换开销。

2.  max_user_connections

这个参数可以为特定的用户设置最大连接数限制。如果设置了该参数，那么该用户的连接数不能超过这个限制，即使max_connections参数设置的总数还有剩余。

3.  max_connect_errors

这个参数指定了错误连接尝试的最大次数，超过这个次数后，MySQL将暂时阻止该客户端的IP地址。

4.  wait_timeout

这个参数定义了一个连接在空闲多久后会被自动关闭。它有助于释放空闲连接占用的资源。

5.  interactive_timeout

对于交互式客户端，这个参数定义了连接在空闲多久后会被自动关闭。

6.  net_read_timeout 和 net_write_timeout

这两个参数分别定义了读取和写入操作在超时前可以等待的秒数。

7.  thread_cache_size

这个参数定义了线程缓存的大小，用于缓存线程，减少创建和销毁线程的开销。如果max_connections设置得很高，增加线程缓存可以提高性能。

8.  table_open_cache

这个参数定义了服务器可以同时打开的表的数量。如果一个连接打开了大量不同的表，可能需要增加这个值。

31.4.  日志参数

MySQL数据库中，日志参数对于数据的恢复、监控和调试非常重要。以下是一些关键的日志参数及其作用：

1.  general_log_file:

指定了通用日志文件的路径和文件名。通用日志记录了发往MySQL服务器的每个客户端的连接和语句。

2.  general_log:

控制是否启用通用日志。它可以设置为'ON'或'OFF'。

3.  slow_query_log_file:

指定了慢查询日志文件的路径和文件名。慢查询日志记录了执行时间超过指定阈值的查询。

4.  slow_query_log:

控制是否启用慢查询日志。它可以设置为'ON'或'OFF'。

5.  long_query_time:

定义了查询执行时间超过多少秒时，会被记录到慢查询日志中。

6.  log_queries_not_using_indexes:

a.  如果设置为'ON'，则记录所有没有使用索引的查询到慢查询日志。

7.  log_error:

a.  指定了错误日志文件的路径和文件名。

8.  log_warnings:

a.  控制是否记录警告信息到错误日志。

9.  log_bin:

a.  控制是否启用二进制日志（binlog）。二进制日志记录了所有更改数据库状态的语句。

10.  binlog_format:

a.  定义了二进制日志的格式。它可以是'MIXED'、'STATEMENT'或'ROW'。

11.  expire_logs_days:

a.  定义了二进制日志文件在多少天后会被自动删除。

12.  max_binlog_size:

a.  定义了单个二进制日志文件的最大大小。

13.  sync_binlog:

定义了每次事务提交后，二进制日志是否需要同步到磁盘。

14.  innodb_log_file_size:

定义了InnoDB重做日志文件的大小。

15.  innodb_log_buffer_size:

定义了InnoDB日志缓冲区的大小，用于缓存重做日志记录。

16.  innodb_flush_log_at_trx_commit:

控制InnoDB何时将重做日志从缓冲区刷新到日志文件。它可以设置为0、1或2，影响性能和数据的持久性。

17.  innodb_flush_method:

定义了InnoDB如何将数据从缓冲池刷新到磁盘。

18.  innodb_log_group_home_dirs:

定义了InnoDB日志文件存储的目录。

32.  主从复制

● 配置主服务器

● 配置从服务器

● 启动复制

● 监控复制

33.  读写分离

● 配置主服务器

● 配置从服务器

● 客户端连接

34.  查看用户进程

使用以下命令查看用户进程：

SHOW PROCESSLIST;

35.  查看锁表

使用以下命令查看锁表：

SHOW TABLE STATUS;

36.  释放锁表

使用以下命令释放锁表：

KILL 线程ID;

37.  数据导入

使用以下命令导入数据：

LOAD DATA INFILE '文件路径' INTO TABLE 表名 FIELDS TERMINATED BY '分隔符' LINES TERMINATED BY '换行符';

例如：

LOAD DATA INFILE '/path/to/data.txt' INTO TABLE user FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

38.  数据导出

使用以下命令导出数据：

SELECT 列名1, 列名2, ... INTO OUTFILE '文件路径' FIELDS TERMINATED BY '分隔符' LINES TERMINATED BY '换行符' FROM 表名 WHERE 条件;

例如：

SELECT id, name, age INTO OUTFILE '/path/to/data.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM user WHERE age > 18;