1. 基础知识

---

1.1 一条数据的存储过程

存储数据确实是处理数据的基石, 只有确保数据被准确无误且有条理地存储, 我们才能对其进行深入的处理和细致的分析.
否则, 这些数据就像是一团毫无章法的乱麻, 让我们难以捉摸其内在的逻辑和价值.

那么, 如何才能够将用户那些与经营紧密相关, 错综复杂的数据, 以有序且高效的方式存储起来呢?
在MySQL中, 一个完整的数据存储过程总共有4步, 分别是: 
* 1. 创建数据库: 在MySQL中, 数据库是数据的容器.
     创建数据库就像是开辟一个新的存储空间, 这个存储空间将用来存放各种数据表.
     数据库名称在整个数据库系统中必须是唯一的.
     
* 2. 确认字段: 在创建数据表之前, 需要确定表中需要哪些字段(列), 每个字段的数据类型是什么, 
     以及是否需要对字段进行约束(如唯一性, 非空等).
     
* 3. 创建数据表: 数据表是数据库中实际存储数据的结构.
     它定义了数据的组织方式, 包括字段名, 数据类型, 约束等.
     
* 4. 插入数据: 当数据表创建好之后, 就可以向表中插入数据了.
     插入数据是向数据表中添加实际记录的过程.

为什么需要先创建数据库再创建数据表呢?
* 1. 层次结构: 数据库是包含数据表的更高层次的容器.
     数据库可以包含多个数据表, 每个数据表存储着不同类型的数据.
     这种层次结构使得数据的管理和组织更加清晰和有序.

* 2. 权限管理: 在MySQL中, 权限是基于数据库来设置的.
     可以为不同的数据库设置不同的用户权限, 以控制哪些用户可以访问哪些数据库中的数据.

* 3. 数据隔离: 不同的数据库可以存储不同的数据, 它们之间是相互独立的.
     这种隔离性有助于保护数据的完整性和安全性.

* 4. 维护方便: 当需要备份, 恢复或迁移数据时, 以数据库为单位进行操作通常更为方便和高效.

因此, 在MySQL中, 创建数据库是存储数据的第一步, 它为后续的数据表创建和数据插入提供了基础.

1.2 数据库系统的层次结构

从系统架构的层次角度来看, MySQL数据库系统的组织结构呈现出一种从大到小, 层层递进的排列方式.

这个层次结构如下:
* 1. 数据库服务器(Database Server): 数据库服务器是整个系统的核心.
     它负责提供数据库服务, 管理数据库文件的存储, 读取和写入, 以及处理客户端的连接请求和数据查询等.
     MySQL数据库服务器就是运行MySQL数据库管理系统的计算机或服务器, 它负责执行所有与数据库相关的操作.

* 2. 数据库(Database): 数据库是存储数据的逻辑容器, 它包含了多个数据表.
     在MySQL中, 每个数据库都是独立的, 拥有自己的数据表, 视图, 存储过程和函数等对象.
     创建数据库是为了在逻辑上将数据划分为不同的集合, 便于管理和访问.

* 3. 数据表(Table): 数据表是数据库中实际存储数据的对象, 它由行和列组成.
     每一列定义了一种数据类型, 而每一行则包含了一组具体的数据.
     数据表是数据库中最基本的存储单位, 用于存储特定类型的数据记录.

* 4. 行与列(Rows and Columns): 行和列是数据表的基本结构.
     列(也称为字段)定义了数据表中的数据属性, 每一列都有一个数据类型, 用于限制该列中数据的类型.
     行(也称为记录)则是数据表中实际的数据, 每一行包含了多个列的值, 表示一个完整的数据记录.

前面说过: 从系统架构的层次角度来看, 应先创建数据库再创建数据表.
这是因为在数据库系统中, 数据库作为数据的高级组织单位, 充当着存储和管理多个数据表的容器.
在逻辑上, 数据库是包含数据表的更高级别的结构, 而数据表则是实际存储数据的单元.

创建数据库的过程实际上是在数据库系统中开辟一个新的存储空间, 并为这个空间分配一个唯一的名称.
这个名称将用于标识和引用该数据库, 确保在系统中能够准确找到并操作该数据库中的数据.

而数据表是具体的数据存储单位, 用于存放与特定业务逻辑相关的数据.
每个数据表都有其特定的结构和约束, 这些结构和约束定义了表中数据的组织方式和操作规则.
在创建数据表之前, 必须先确定好表的结构和字段, 以便在数据库中正确创建和定义表.

因此, 从系统架构的层次角度来看, 先创建数据库再创建数据表是符合逻辑和规范的.
从数据库服务器到数据表的行与列, 每一层都负责处理不同的数据管理和操作任务, 从而构建了一个完整, 高效的数据存储和处理系统.

1.3 标识符命名规则

在MySQL数据库中, 为了确保数据的一致性和管理的便利性, 需要遵循一定的标识符命名规则.
以下是关于数据库名, 表名, 字段名等对象命名时需要遵守的规则:
* 1. 长度限制: 数据库名, 表名的长度不得超过30个字符.
     变量名的长度限制为29个字符(考虑到某些情境下可能需要为变量名添加前缀或后缀).

* 2. 字符集: 标识符(包括数据库名, 表名, 字段名等)必须只能包含字母(A–Z, a–z), 数字(0–9)和下划线(_), 共63个字符.
     避免使用特殊字符, 因为它们可能会在不同的数据库管理系统中引起冲突或解析错误.
     
* 3. 空格问题: 数据库名, 表名, 字段名等对象名中间不得包含空格, 空格可能导致解析错误或混淆.

* 4. 唯一性: 在同一个MySQL软件中, 数据库名称必须唯一, 不能存在同名的数据库.
     在同一个数据库中, 表名必须唯一, 不能存在同名的表.
     在同一个表中, 字段名必须唯一, 不能存在同名的字段.

* 5. 避免保留字: 必须确保你的字段名没有与MySQL的保留字, 数据库系统或常用方法冲突.
     如果由于某种原因确实需要使用保留字作为字段名, 那么在SQL语句中必须使用反引号(`)将其引起来.

* 6. 字段名和类型的一致性: 在设计数据库结构时, 应保持字段名和类型的一致性.
     如果某个字段在一个表中被定义为整数类型, 那么在其它相关的表中, 该字段也应该被定义为整数类型, 而不是字符型或其它类型.
     遵循这些规则有助于确保数据库结构的清晰性, 一致性和可维护性, 同时, 它们也有助于减少因命名冲突或混淆而导致的错误.

1.4 MySQL字段命名

阿里巴巴<<Java开发手册>>之MySQL字段命名:
* 1. [强制]表名, 字段名必须使用小写字母或数字, 禁止出现数字开头, 禁止两个下划线中间只出现数字.
     数据库字段名的修改代价很大, 因为无法进行预发布, 所以字段名称需要慎重考虑.
     正例: aliyun_admin, rdc_config, level3_name
     反例: AliyunAdmin, rdcConfig, level_3_name
* 2. [强制]禁用保留字, 如: desc, range, match, delayed等, 请参考MySQL官方保留字.
* 3. [强制]表必备三字段id, gmt_create, gmt_modified.
     说明: 其中id必为主键, 类型为BIGINT UNSIGNED, 单表时自增, 步长为1.
     gmt_create, gmt_modified的类型均为DATETIME类型, 前者现在时表示主动式创建, 后者过去分词表示被动式更新.
* 4. [推荐]表的命名最好是遵循'业务名称_表的作用'.
     正例: alipay_task, force_project, trade_config.
* 5. [推荐]库名与应用名称尽量一致.
* 6. [参考]合适的字符存储长度, 不但节约数据库表空间, 节约索引存储, 更重要的是提升检索速度.
     正例: 无符号值可以避免误存负数, 且扩大了表示范围.

对象	年龄区间	数据类型	字节	表示范围
人	0 到 150 岁	tinyint unsigned	1	无符号值: 0 到 255
龟	不可确定	smallint unsigned	2	无符号值: 0 到 65535
恐龙化石	数千万年	int unsigned	4	无符号值: 0 到约 43 亿
太阳	约 50 亿年	bigint unsigned	8	无符号值: 0 到约 10 的 19 次方

2. 数据类型

---

在实际使用中, 根据具体需求选择合适的数据类型是非常重要的, 因为它们直接影响到数据的存储, 查询效率和准确性.
下面是MySQL中提到的各种数据类型的表格概述.

2.1 整数类型

在MySQL中, 整形数据通常用于存储没有小数部分的数值.

整数类型	描述	范围
TINYINT	非常小的整数	有符号范围: -128 到 127.
		无符号范围: 0 到 255.
SMALLINT	小的整数	有符号范围: -32,768 到 32,767.
		无符号范围: 0 到 65,535.
MEDIUMINT	中等大小的整数	有符号范围: -8,388,608 到 8,388,607.
		无符号范围: 0 到 16,777,215.
INT 或 INTEGER	标准整数	有符号范围: -2,147,483,648 到 2,147,483,647.
		无符号范围: 0 到 4,294,967,295.
BIGINT	大整数	有符号范围: -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807.
		无符号范围: 0 到 18,446,744,073,709,551,615.

2.2 浮点型

在MySQL中, 浮点型数据主要用于存储带有小数部分的数值.

浮点型数据	描述	范围
FLOAT	单精度浮点数	取值范围大约为: -3.4E38 到 3.4E38.
		有效数字位数大约为6~7位.
DOUBLE	双精度浮点数	取值范围大约为: -1.8E308 到 1.8E308.
		有效数字位数大约为15位.
DECIMAL	精确浮点数	取值范围由用户定义的精度和小数位数决定.
		提供精确的数值表示, 适用于需要高精度计算的场景, 如财务计算.

科学计数法中, 字母"e"或"E"通常用来表示10的幂次.
例: 数字"-3.4E38"中, "E38"表示的是10的38次方 = -3.4 × (10^38) = -3400000000000000000000000000000000000000000000

需要注意的是, FLOAT和DOUBLE类型的范围是指数的范围, 实际的数值范围会受到尾数的影响.
另外, 由于FLOAT和DOUBLE是近似值表示, 它们可能会存在舍入误差, 
因此在需要精确计算的场合(如金融计算), 通常推荐使用DECIMAL类型.

DECIMAL类型允许用户指定数值的精度和小数位数, 从而确保数值的精确性.
用户可以根据具体需求来定义DECIMAL类型的精度和小数位数, 以满足不同的精度要求.

2.3 字符串类型

在MySQL中, 字符型数据用于存储文本和字符串.

二进制字符串类型	描述	范围
CHAR	定长字符串	长度为0到255个字符. 存储时, 如果字符串长度小于定义的长度, 会使用空格填充到指定长度.
VARCHAR	可变长度字符串	长度为0到65,535个字节(具体取决于最大行大小和其他列).
		存储时, 只使用必要的长度来存储字符串, 不会添加额外的空格.
TINYTEXT	非常小的文本字符串	最大长度为255个字符.
TEXT	文本字符串	最大长度为65,535个字符.
MEDIUMTEXT	中等大小的文本字符串	最大长度为16,777,215个字符.
LONGTEXT	大文本字符串	最大长度为4,294,967,295个字符.
二进制字符串类型	描述	范围
BINARY	定长二进制字符串	类似于CHAR, 但存储二进制数据. 长度为0到255个字节.
VARBINARY	可变长度二进制字符串	类似于VARCHAR, 但存储二进制数据. 长度为0到65,535个字节.
TINYBLOB	非常小的二进制大对象	最大长度为255个字节.
BLOB	二进制大对象	最大长度为65,535个字节.
MEDIUMBLOB	中等大小的二进制大对象	最大长度为16,777,215个字节.
LONGBLOB	大二进制大对象	最大长度为4,294,967,295个字节.

需要注意的是, VARCHAR, TEXT和BLOB类型的实际存储需求取决于存储的字符串或二进制数据的长度.
此外, VARCHAR和TEXT类型在存储时不会添加末尾的空格, 而CHAR类型则会用空格填充到指定长度.

在选择字符型数据类型时. 应根据实际的数据需求和存储限制来进行选择.
例如, 如果字符串的长度是固定的, 并且长度较短, 可以选择CHAR类型;
如果字符串的长度是可变的, 可以选择VARCHAR类型;
如果需要存储大量的文本数据, 可以选择TEXT或BLOB类型.

2.4 枚举类型

在MySQL中, 枚举(ENUM)类型是一种特殊的数据类型, 它允许你在表中定义一个列, 其中只能存储预定义的值.

枚举类型	描述	范围
ENUM	允许在列中插入预定义的值列表中的一个值.	范围: 最多可以有65535个不同的值.
	示例: ENUM('男', '女'); 只能存储'男'或'女'.	如果值列表在255个以内, 使用一个字节存储;
	实际上不存储字符串本身, 而是存储值在列表中的位置(整数).	如果超过255但小于65535, 使用两个字节存储.

需要注意的是, 虽然上述表格只列出了一种类型(ENUM), 但实际上枚举类型的'范围'是由定义它时指定的值列表决定的.
每个枚举字段都可以有自己的值列表和范围.

此外, 枚举类型有一些特点和限制:
* 1. 枚举值在物理存储时实际上是按照定义的顺序存储的整数索引, 而不是实际的字符串值.
* 2. 枚举字段在查询时返回的是字符串值, 而不是整数索引.
* 3. 如果尝试插入不在值列表中的值, MySQL会报错(除非该列允许NULL值, 并且插入了NULL).
* 4. 枚举类型的值列表中的值必须是唯一的, 不能重复.
* 5. 修改枚举类型的值列表(例如添加, 删除或重新排序值)可能需要使用'ALTER TABLE'语句, 这可能会影响到已有的数据和应用逻辑.
* 6. 由于枚举类型的这些特点, 它最适合用于那些值的集合是固定的, 不会经常变化的字段, 例如性别, 星期几等.
     对于可能经常变化的字段, 使用其他数据类型(如VARCHAR)可能更为合适.

2.5 集合类型

在MySQL中, 集合(SET)类型是一种字符串对象, 用于存储一个预定义的字符串集合中的零个或多个值.
集合类型允许在单个列中存储多个值, 每个值都是集合定义时指定的一个元素.

集合类型确实对元素的顺序有一定的敏感性, 但这种敏感性主要体现在集合元素的定义上, 而不是在存储和检索时.
当你在创建表并定义SET类型的列时, 需要按照特定的顺序列出集合中的元素. 这个顺序在定义后是不可更改的.

集合类型	描述	范围
SET	允许在单个列中存储多个预定义的字符串值(定义时指定).	存储时, 每个集合元素使用多个位来表示,
	每个集合元素之间用逗号分隔, 集合成员本身不能包含逗号.	因此存储需求取决于集合的大小和元素数量.

需要注意的是, 集合类型的范围是由定义集合时指定的字符串元素集合决定的.
每个集合元素都是唯一的, 并且集合中元素的数量是有限制的(最多64个元素).

使用集合类型可以方便地在一个列中存储多个相关的值, 而无需使用多个列或进行复杂的字符串处理.

然而, 集合类型也有一些限制, 比如元素的数量限制以及存储和检索时的性能考虑.
因此, 在选择是否使用集合类型时, 需要根据具体的应用场景和需求进行权衡.

此外, 与集合类型相关的MySQL函数(如: FIND_IN_SET())可以用于查询和操作集合数据, 提供了灵活的集合运算能力.

2.6 JSON类型

在MySQL中, JSON类型是一种用于存储JSON格式数据的数据类型. 
它允许你在MySQL数据库的表中直接存储, 查询和操作JSON数据.

JSON类型	描述	范围
JSON	用于存储JSON格式的数据.	可以存储任何有效的JSON数据, 包括对象, 数组, 字符串, 数字, 布尔值和NULL.
		JSON数据在MySQL中以二进制字符串的形式存储, 但可以使用专门的JSON函数进行查询和操作.
		JSON类型列可以接受空值(NULL).
JSON_OBJECT	返回一个JSON对象.	在查询时用于构建JSON对象.
		可以使用列值或常量作为对象的键和值.
		返回的对象是一个JSON字符串.
JSON_ARRAY	返回一个JSON数组.	在查询时用于构建JSON数组.
		可以使用列值或常量作为数组的元素.
		返回的数组是一个JSON字符串.

需要注意的是, 虽然上述表格中列出了JSON_OBJECT和JSON_ARRAY, 但它们实际上是用于构建JSON数据的函数, 而不是数据类型本身.
在创建表时, 会使用JSON数据类型来定义列, 然后使用这些函数在查询时构建和操作JSON数据.

关于范围, JSON类型在MySQL中没有固定的长度限制, 它取决于存储的JSON数据的大小.
然而, 需要注意的是, 过大的JSON数据可能会对性能产生影响, 因此在设计数据库和查询时需要谨慎考虑.

MySQL提供了一系列专门的JSON函数, 用于在查询时验证, 提取和修改JSON数据.
这些函数使得在MySQL中处理JSON数据变得相对简单和高效.
可以使用这些函数来查询JSON对象中的特定字段, 修改JSON数据, 数组操作等.

MySQL的JSON类型提供了一种灵活的方式来存储和查询JSON格式的数据, 使得在数据库中直接处理结构化数据变得更加方便.

2.7 空间数据类型

MySQL的空间数据类型是用于存储和处理地理空间信息的.
主要包括GEOMETRY, POINT, LINESTRING, POLYGON以及集合类型如MULTIPOINT、MULTILINESTRING、MULTIPOLYGON等.
这些数据类型允许用户在数据库中直接存储和操作地理空间数据, 如点, 线, 多边形等几何对象.

空间数据类型(单值)	描述	范围
GEOMETRY	所有空间数据类型的基类.	表示任意的几何对象, 可以是点, 线, 多边形等.
POINT	表示二维空间中的一个点.	包含一个坐标值, 如(x, y).
LINESTRING	表示一系列有序的点, 形成一条线.	由至少两个点组成, 表示一条连续的线段.
POLYGON	表示一个多边形.	由一系列有序的点组成, 第一个和最后一个点必须相同, 表示闭合的多边形.
空间数据类型(多值)	描述	范围
MULTIPOINT	表示多个点的集合.	包含多个POINT对象.
MULTILINESTRING	表示多条线的集合.	包含多个LINESTRING对象.
MULTIPOLYGON	表示多个多边形的集合.	包含多个POLYGON对象.
GEOMETRYCOLLECTION	表示几何对象的集合, 可以是点, 线, 多边形等的任意组合.	包含上述任意类型的几何对象.

在范围方面, 这些空间数据类型没有固定的长度或大小限制, 它们根据实际存储的几何对象的复杂性和大小来确定.
例如, 一个简单的POINT类型可能只占用几个字节, 而一个包含大量顶点的POLYGON类型则可能占用更多的存储空间.

需要注意的是, 这些空间数据类型在MySQL中是与空间索引和空间函数一起使用的, 以实现高效的空间数据查询和操作.
通过使用这些数据类型和相关功能, 用户可以在MySQL数据库中执行复杂的空间分析和地理信息处理任务.

此外, MySQL还支持将空间数据以BLOB类型的二进制数据形式进行存储, 这为那些不支持直接空间数据类型存储的系统提供了灵活性.
然而, 使用BLOB类型存储空间数据通常需要额外的解析和处理步骤, 以便在查询和分析时能够提取和使用空间信息.

2.8 常用类型

数据类型	描述
INT	从-2^31到2^31-1的整型数据. 存储大小为4个字节.
CHAR(size)	定长字符数据. 若未指定, 默认为1个字符, 最大长度255.
VARCHAR(size)	可变长字符数据. 根据字符串实际长度保存, 必须指定长度.
FLOAT(M,D)	单精度, 占用4个字节. M=整数位+小数位, D=小数位. D<=M<=255, 0<=D<=30. 默认M+D<=6.
DOUBLE(M,D)	双精度, 占用8个字节. D<=M<=255, 0<=D<=30. 默认M+D<=15.
DECIMAL(M,D)	高精度小数, 占用M+2个字节. D<=M<=65, 0<=D<=30. 最大取值范围与DOUBLE相同.
ENUM	枚举类型, 用于存储预定义的值列表中的一个值. 常用于存储如性别(男/女), 状态(开启/关闭)等固定选项的数据.
DATE	日期型数据, 格式'YYYY-MM-DD.'
BLOB	二进制形式的长文本数据, 最大可达4G.
TEXT	长文本数据, 最大可达4G.

3. 数据库操作

注意: 
- 在实际操作中, 确保你具有足够的权限来执行这些命令, 并且替换命令中的数据库名和表名为实际的数据库和表名称.
- 在修改任何数据库级别的设置之前, 请确保您了解这些更改可能带来的影响, 并始终在修改之前备份你的数据库.

3.1 创建数据库

---

在MySQL中, 可以使用CREATE DATABASE语句来创建一个新的数据库.
以下是一些创建数据库的示例和注意事项:
* 1. 方式1: 创建数据库: CREATE DATABASE 数据库名;
     这条命令将创建一个新的数据库, 其中'数据库名'是你想要创建的数据库的名称.
     如果不指定字符集, 数据库将使用MySQL服务器的默认字符集.

* 2. 方式2: 创建数据库并指定字符集: CREATE DATABASE 数据库名 CHARACTER SET 字符集;
     这条命令不仅创建一个新的数据库, 还指定了该数据库的字符集.
     '字符集'是你希望数据库使用的字符编码, 例如'utf8'或'utf8mb4'.
     指定字符集可以帮助确保正确存储和检索包含特殊字符的数据.

* 3. 方式3: 判断数据库是否已经存在, 不存在则创建数据库: CREATE DATABASE IF NOT EXISTS 数据库名;
     使用'IF NOT EXISTS'子句是一种安全的做法, 因为它可以防止因尝试创建已存在的数据库而产生的错误.
     如果数据库已经存在, MySQL将忽略该命令并继续执行后续语句.

注意事项:
- DATABASE(数据库)一旦创建后, MySQL本身并不提供直接重命名数据库的命令.
  如果需要重命名数据库, 通常的做法是创建一个新的数据库, 然后将旧数据库中的表和数据复制到新数据库中, 最后删除旧数据库.
  这个过程需要谨慎操作, 以避免数据丢失或损坏.
- 使用可视化工具进行数据库重命名时, 工具内部通常也是执行了上述步骤, 只是这个过程对用户来说是透明的.

- 在实际应用中, 建议使用方式3来创建数据库, 因为它更加健壮, 可以防止因重复创建数据库而导致的错误.
  同时, 在创建数据库时, 如果有可能的话, 最好也指定字符集, 以确保数据的正确存储和检索.

-- 方式1创建数据库:
mysql> CREATE DATABASE db0;
Query OK, 1 row affected (0.01 sec)
-- Query OK: 表示执行成功.
-- 1 row affected: 通常创建数据库时不会直接涉及到'行'的概念, 这里可能是MySQL的一个通用消息.
-- (0.01 sec): 表示执行花费的时间.

-- 方式2创建数据库:
mysql> CREATE DATABASE db1 CHARACTER SET 'utf8';
Query OK, 1 row affected, 1 warning (0.01 sec)  -- 警告的意思是: 创建时不推荐使用utf8而是写utf8mb4.
-- 1 warning: 表示执行时产生了1条警告信息.

-- 方式3创建数据库:
mysql> CREATE DATABASE IF NOT EXISTS db2 CHARACTER SET 'utf8mb4';
Query OK, 1 row affected (0.01 sec)

3.2 使用数据库

以下是使用MySQL数据库的一些基本语句:
* 1. 查看当前所有的数据库: SHOW DATABASES;  -- 有一个S, 代表多个数据库.
     这条命令将列出MySQL服务器上所有的数据库.

* 2. 查看数据库的创建信息: SHOW CREATE DATABASE 数据库名;
     这条命令都用于显示指定数据库的创建语句.
     
* 3. 使用/切换数据库: USE 数据库名;
     这条命令用于选择或切换到指定的数据库, 以便后续的SQL语句都在该数据库上执行.
     在切换数据库之后, 你可以直接对表进行操作, 而不需要在表名前加上前缀'数据库名.'.
     
* 4. 查看当前正在使用的数据库: SELECT DATABASE();  -- DATABASE()是一个全局函数.
     这条命令返回当前会话中选择的数据库名.
     如果没有选择任何数据库, 它将返回一个NULL值.

* 5. 查看指定库下所有的表: SHOW TABLES FROM 数据库名;
     这条命令列出指定数据库中的所有表. 
     需要将数据库名替换为实际的数据库名称.

3.2.1 查看所有的数据库

-- 查看所有的数据库:
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| atguigudb          |  -- 练习使用的数据库
| db0                |  -- 前面创建的数据库
| db1                |  
| db2                |  
| information_schema |  -- 系统自动创建
| mysql              | 
| performance_schema |
| sys                |
+--------------------+
8 rows in set (0.01 sec)  -- 意味着查询结果集包含了8行记录.

以下数据库是MySQL服务器安装时自动创建的, 并且通常不建议用户直接修改它们.
* 1. information_schema: 是MySQL中的一个特殊的数据库, 它包含了关于所有其他数据库的信息.
    这个数据库不能被直接修改, 它提供了一个只读的方式来访问数据库元数据(metadata).
    通过查询information_schema数据库, 你可以获取关于所有数据库, 表, 列, 索引, 键, 约束, 分区等的信息.
* 2. mysql: 是MySQL服务器的系统数据库, 包含了服务器的系统表.
     这些表存储了服务器的用户账号, 权限, 系统变量, 插件, 事件调度器等信息.
* 3. performance_schema: 这个数据库提供了对服务器执行过程中低级别的统计信息, 帮助用户监控和分析服务器的性能.
* 4. sys: sys数据库是一个视图(view)和函数(function)的集合, 
     这些视图和函数基于performance_schema来提供一个更加友好和直观的方式来查看和分析服务器的性能数据.
     这个数据库的目的是简化性能监控和诊断.

3.2.2 查看数据库的创建信息

-- 查看数据库的创建信息:
mysql> SHOW CREATE DATABASE db0;

-- 按使用\G作为命令的结尾将以垂直格式显示结果, 这在某些情况下可能更容易阅读:
SHOW CREATE DATABASE db0\G
*************************** 1. row ***************************
       Database: db0
Create Database: CREATE DATABASE `db0` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ 
                 /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

CREATE DATABASE的信息介绍:

* 1. CREATE DATABASE `db0`: 这是创建新数据库的基本SQL语句.
     db0是数据库的名称, 反引号(`)用于包围数据库名称, 以确保即使名称与MySQL的保留关键字冲突, 也能正确解析.

* 2. /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */: 
     这是一个特定于MySQL的注释, 用于设置新数据库的默认字符集和排序规则.

     DEFAULT CHARACTER SET utf8mb4: 这指定了数据库的默认字符集为utf8mb4.
     utf8mb4字符集支持所有Unicode字符, 包括emoji表情符号和其他一些扩展的Unicode字符.

     COLLATE utf8mb4_0900_ai_ci: 这指定了数据库的默认排序规则为utf8mb4_0900_ai_ci.
     排序规则决定了数据库中字符的排序和比较方式.
     _ai_ci表示排序是'不区分大小写'(case-insensitive)和'不区分重音'(accent-insensitive)的.

* 3. /*!80016 DEFAULT ENCRYPTION='N' */: 这是另一个特定于MySQL的注释, 用于设置新数据库的默认加密选项/
     DEFAULT ENCRYPTION='N': 这表示数据库的默认加密设置为'N', 即不启用加密.
     这个选项在MySQL 8.0及更高版本中可用, 允许管理员设置数据库的透明数据加密(Transparent Data Encryption, TDE).
     通过将DEFAULT ENCRYPTION设置为'Y', 可以启用数据库的加密. 而设置为'N'则意味着数据库不会默认加密存储的数据.
     
总结一下, 这条命令创建了一个名为db0的新数据库, 
设置了其默认字符集为utf8mb4, 默认排序规则为utf8mb4_0900_ai_ci, 并禁用了默认加密.
这些设置对于确保数据库正确存储和比较字符数据, 以及保护数据的安全性至关重要.

3.2.3 使用数据库

-- 使用数据库:
mysql> USE atguigudb;
Database changed  -- 切换成功

-- 使用另一个数据库直接使用USE切换:
mysql> USE db0;
Database changed  

MySQL并没有提供直接的命令或方法来'返回上层'或退出当前选择的数据库.
一旦你选择了某个数据库, 你只需再次使用USE命令来选择另一个数据库, 或者在执行查询时指定数据库名.

3.2.4 查看使用的数据库

-- 先退出:
-- mysql> exit
Bye

-- 再重新登入:
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

-- 查看使用的数据库:
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |  -- 如果没有选择任何数据库, 它将返回一个NULL值.
+------------+
1 row in set (0.00 sec)

-- 使用数据库:
mysql> USE db0;
Database changed

-- 查看数据的数据库:
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db0        |
+------------+
1 row in set (0.00 sec)

-- 切换数据库:
mysql> USE db1;
Database changed

-- 查看数据的数据库:
+------------+
| DATABASE() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

3.2.5 查看指定库下所有的表

-- 查看数据库下所有的表格:
mysql> SHOW TABLES FROM atguigudb;
+---------------------+
| Tables_in_atguigudb |  -- 位置
+---------------------+
| countries           |
| departments         |
| emp_details_view    |
| employees           |
| job_grades          |
| job_history         |
| jobs                |
| locations           |
| order               |  -- 关键字在sql编辑器中高亮显示
| regions             |
+---------------------+
10 rows in set (0.01 sec)

-- 查看空的数据库:
mysql> SHOW TABLES FROM db0;
Empty set (0.00 sec)

3.2.6 嵌套问题

在MySQL中, 不能在一个已经存在的数据库下再创建另一个数据库.
数据库是独立的, 它们之间并没有嵌套关系.
所以, 在数据库中创建数据库时, 实际上是在一个数据库管理系统中创建新的数据库.

成功连接到数据库后, 实际上已经处于数据库管理系统的环境中.
在这个环境中, 可以执行各种SQL命令来管理数据库和其中的对象.

当使用USE db_name; 命令后, 则指定了当前会话要使用的数据库.
这意味着后续的SQL命令将默认在这个数据库db0中执行, 除非再次使用USE命令切换到另一个数据库.
通过在表名前加上数据库名前缀, 可以在不改变默认数据库的情况下对任何数据库中的表执行操作.
* 操作示例在 4.1.1 创建方式1 中.

3.3 修改数据库

在MySQL中, 可以ALTER DATABASE 语句修改数据库的一些属性. 

以下是一些常见的修改数据库的操作:
* 1. 修改数据库的字符集和校对规则: ALTER DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 校对规则;
     例如, 将数据库mydatabase的字符集修改为utf8mb4并设置相应的校对规则:
     ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
     
* 2. 修改数据库的默认存储引擎(在MySQL中, 这通常在创建数据库时设置, 但之后也可以修改):
     ALTER DATABASE 数据库名 ENGINE = 存储引擎;
     例如, 将数据库mydatabase的默认存储引擎修改为InnoDB:
     ALTER DATABASE mydatabase ENGINE = InnoDB;

对于gbk字符集, MySQL提供了几个默认的校对规则, 比如: gbk_chinese_ci和gbk_bin.
其中, gbk_chinese_ci是大小写不敏感的(ci 表示 case-insensitive), 并且是按照中文的语义进行排序的.
gbk_bin是二进制排序, 它是大小写敏感的, 并且是基于字符的二进制值进行排序的.
如果您在设置gbk字符集时没有明确指定校对规则, MySQL可能会使用一个默认的校对规则, 但这取决于MySQL的具体版本和配置.

-- 查看数据库创建信息:
mysql> SHOW CREATE DATABASE db0;
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ -- 省略显示

-- 修改字符集为gbk:
mysql> ALTER DATABASE db0 CHARACTER SET gbk;
Query OK, 1 row affected (0.01 sec)

-- 查看数据库创建信息(已经被更新, 它并不是最佳的查看方式, 但可以使用):
mysql> SHOW CREATE DATABASE db0;
/*!40100 DEFAULT CHARACTER SET gbk */ -- 省略显示

3.4 删除数据库

在MySQL中, 可以使用DROP DATABASE 语句永久删除数据库及其所有内容.
在执行之前请确保你已经备份了所有重要的数据.

以下是一些删除数据库的格式:
* 1. 方式1: 删除指定的数据库: DROP DATABASE 数据库名;
     这种方式会直接删除指定的数据库. 如果数据库不存在, MySQL 会返回一个错误.
     这种方式在您确定数据库存在并且确实需要删除它时很有用.
     但如果你不确定数据库是否存在, 或者不希望因为数据库不存在而收到错误, 那么这种方式可能会导致问题.

* 2. 方式2: 删除指定的数据库(推荐): DROP DATABASE IF EXISTS 数据库名;
     这种方式在删除数据库之前会先检查数据库是否存在. 
     如果数据库存在, 它会执行删除操作如果数据库不存在, 它不会执行任何操作, 也不会返回错误.
     这种方式更加安全, 因为它可以避免因为尝试删除不存在的数据库而导致的错误. 因此, 这种方式通常更受推荐.

使用建议:
- 在执行DROP DATABASE语句之前, 始终确保你已经备份了所有重要的数据, 以防万一操作失误导致数据丢失.
- 如果你不确定数据库是否存在, 或者想要避免因为数据库不存在而产生的错误, 请使用 IF EXISTS 子句.
- 在执行任何可能对数据产生影响的操作之前, 最好先在一个测试环境中进行验证, 以确保您了解命令的实际效果.
- 无论使用哪种方式, DROP DATABASE都是一个高风险的操作, 因为它会永久删除数据库及其所有内容.
  因此, 在执行此操作之前, 请务必谨慎考虑并确认您的意图.

-- 查看所有库:
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| atguigudb          |
| db0                |
| db1                |
| ...                |  -- 省略
+--------------------+
8 rows in set (0.01 sec)

-- 删除db0库:
mysql> DROP DATABASE db0;
Query OK, 0 rows affected (0.02 sec)

-- 查看所有库:
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| atguigudb          |
| db1                |
| ...                |  -- 省略
7 rows in set (0.00 sec)


-- 删除不存在的数据库:
mysql> DROP DATABASE db0;
ERROR 1008 (HY000): Can't drop database 'db0'; database doesn't exist  -- 报错

-- 使用DROP DATABASE IF EXISTS删除数据库:
mysql> DROP DATABASE IF EXISTS db1;
Query OK, 0 rows affected (0.01 sec)

-- 查看所有库:
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| atguigudb          |
| db2                |
| ...                |  -- 省略
+--------------------+
6 rows in set (0.00 sec)

--  使用DROP DATABASE IF EXISTS删除不存在的数据库:
mysql> DROP DATABASE IF EXISTS db1;
Query OK, 0 rows affected, 1 warning (0.01 sec)  --  0 rows affected, 1 warning 没有操作成功并发出警告

4. 表格操作

---

表格的很多操作是危险的操作, 执行时一定要谨慎!!!
删除表操作将把表的定义和表中的数据一起删除, 并且MySQL在执行删除操作时, 不会有任何的确认信息提示, 因此执行删除操时应当慎重.
在删除表前, 最好对表中的数据进行备份, 这样当操作失误时可以对数据进行恢复, 以免造成无法挽回的后果.
同样的, 在使用ALTER TABLE进行表的基本修改操作时, 在执行操作过程之前, 也应该确保对数据进行完整的备份,
因为数据库的改变是无法撤销的, 如果添加了一个不需要的字段, 可以将其删除;
相同的, 如果删除了一个需要的列, 该列下面的所有数据都将会丢失.

4.1 创建表格

4.1.1 创建方式1

在MySQL中,使用CREATE TABLE语句创建表格.
用户必须具备CREATE TABLE权限与足够的存储空间.

下面是一个简单的表格创建格式:
CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
... 
[表约束条件]
);

以下是一些详细的解释和示例:
* 1. CREATE TABLE: 这是创建新表的命令.
* 2. [IF NOT EXISTS]: 可选子句, 它确保在尝试创建已存在的表时不会引发错误.
     如果当前数据库中不存在要创建的数据表. 则创建数据表;
     如果当前数据库中已经存在要创建的数据表, 则忽略建表语句, 不再创建数据表.
* 3. 表名: 必须子句, 这是你要创建的表的名称.
* 4. 字段名: 必须子句, 这是表中的列的名称.
* 5. 数据类型: 必须子句, 每个字段都需要一个数据类型, 如INT, VARCHAR, DATE等.
* 6. [约束条件]: 可选子句, 如PRIMARY KEY, UNIQUE, NOT NULL等, 用于限制字段中的数据.
* 7. [默认值]: 可选子句, 当插入新记录但没有为某个字段提供值时, 该字段将使用的值.
* 8. [表约束条件]: 可选子句, 这是应用于整个表的约束, 如FOREIGN KEY.

-- 示例: 创建emp表:
-- 1. 先创建数据库:
mysql> CREATE DATABASE db0;
Query OK, 1 row affected (0.01 sec)

-- 2. 使用数据库:
mysql> use db0;
Database changed

-- 3. 在db0下创建emp表:
mysql> CREATE TABLE emp ( 
    emp_id INT,
    emp_name VARCHAR(20),
    salary DOUBLE,
    birthday DATE
);
Query OK, 0 rows affected (0.02 sec)

创建一个名为emp的表, 该表包含四个字段: emp_id, emp_name, salary和birthday.
下面是每个字段的详细解释:
* 1. emp_id: 这是一个整数(INT)类型的字段, 用于存储员工的ID.
     通常, 这样的字段会设置为主键(PRIMARY KEY), 以确保每个员工的ID都是唯一的.

* 2. emp_name: 这是一个可变字符(VARCHAR)类型的字段, 最大长度为20个字符.
     这个字段用于存储员工的名字, 可以包含中英文字符.

* 3. salary: 这是一个双精度浮点数(DOUBLE)类型的字段, 用于存储员工的薪水.
     DOUBLE类型可以存储非常大或非常小的数值, 包括小数.

* 4. birthday: 这是一个日期(DATE)类型的字段, 用于存储员工的生日.

如果你不想切换到特定的数据库内, 而想直接对某个数据库中的表执行操作, 可以在表名前使用数据库名作为前缀.
在MySQL中, 这通常是通过在数据库名和表名之间使用点号(.)来实现的.

格式: CREATE TABLE mydatabase.emp(...); 

-- 查看当前的库:
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db0        |
+------------+
1 row in set (0.00 sec)

-- 创建数据库:
mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)


-- 在db0中通过'库.表名'的方式在db1中创建表格:
mysql> CREATE TABLE db1.emp(
    emp_id INT,
    emp_name VARCHAR(20),
    salary DOUBLE,
    birthday DATE
);
Query OK, 0 rows affected (0.01 sec)

-- 查看db1下的所有表格:
mysql> SHOW TABLES FROM db1;
+---------------+
| Tables_in_db1 |
+---------------+
| emp           |
+---------------+
1 row in set (0.01 sec)

4.1.2 创建方式2

可以使用 CREATE TABLE ... AS SELECT 语法来创建一个新表, 并将另一个查询的结果插入到这个新表中.
它是SQL中的一个非常有用的特性, 它允许你基于一个查询的结果创建一个新表.
这种方法特别适用于当你需要从一个或多个现有的表中提取数据, 并将其存储在一个新的, 具有特定结构的表中时.

以下是使用 CREATE TABLE ... AS SELECT 语法的基本步骤:
* 1. 确定你要从哪个表或哪些表中提取数据.
* 2. 编写一个查询来提取你需要的数据.
* 3. 创建一个新表, 并将查询的结果插入到这个新表中.

注意事项:
查询结果中的列必须与要创建的新表中的列一一对应.
如果你想要为新表的列指定不同的名称或者想要排除某些列, 可以在SELECT语句中明确地列出所需的列, 如有需要可指定别名.

假设我们有一个名为employees表, 它包含员工的个人信息, 如:employee_id, first_name, salary 和 department_id.
现在, 我们想要创建一个新表highsalary_employees, 其中只包含工资大于17000的员工.
我们可以使用以下SQL语句来实现这个目的:

mysql> CREATE TABLE highsalary_employees AS
SELECT employee_id, first_name, salary, department_id
FROM atguigudb.employees
WHERE salary > 17000;
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 1

-- 这个MySQL查询的输出信息表示:
-- Query OK: 查询执行成功.
-- 1 rows affected: 该查询影响了1行记录. 也就是说，它可能已经更新了、删除了或者插入了1条记录.
-- 1 warning: 在执行查询的过程中, MySQL返回了一个警告. 
-- (0.02 sec): 查询执行所花费的时间为0.02秒.
-- Records: 1: 这与'1 rows affected'意义相同, 指的是被影响的记录数量.
-- Duplicates: 0: 表示没有产生任何重复记录.
-- Warnings: 1: 再次提醒, 有一个警告产生.

在这个例子中:
- CREATE TABLE highsalary_employees AS: 这部分表示我们要创建一个名为highsalary_employees的新表.
- SELECT employee_id, first_name, salary, department_id FROM employees WHERE salary > 17000; 
  这部分是一个查询, 它从employees表中提取工资大于17000的员工的id, 名字, 薪资和部门号.
- CREATE TABLE ... AS SELECT语法将查询的结果存储在新创建的highsalary_employees表中.

执行上述SQL语句后, 会得到一个名为highsalary_employees的新表, 其中只包含工资大于17000的员工的记录.

-- 查看highsalary_employees表的内容:
mysql> SELECT * FROM highsalary_employees;
+-------------+------------+----------+---------------+
| employee_id | first_name | salary   | department_id |
+-------------+------------+----------+---------------+
|         100 | Steven     | 24000.00 |            90 |
+-------------+------------+----------+---------------+
1 row in set (0.00 sec)

4.2 查看数据表结构

在MySQL中创建好数据表之后, 可以使用 DESCRIBE/DESC 或 SHOW CREATE TABLE 语句查看数据表结构.

以下是这两种方法的语法格式和使用说明:
* 1. 使用 DESCRIBE 或 DESC 语句(DESCRIBE和DESC是同义词, 它们都可以用来查看数据表的结构信息).
     语法格式: DESCRIBE table_name;
     或者: DESC table_name;
     使用说明: table_name 是你要查看结构的数据表的名称.
     执行这条命令后, MySQL会返回数据表的字段名, 数据类型, 是否允许NULL值, 键信息, 默认值以及额外信息等.

* 2. 使用 SHOW CREATE TABLE 语句.
     SHOW CREATE TABLE 语句用来查看创建表的SQL语句, 通过这个语句, 你可以了解到表的完整定义,
     包括表名, 字段定义, 索引, 默认值, 字符集等.
     语法格式: SHOW CREATE TABLE table_name;
     使用说明: table_name 是你要查看创建语句的数据表的名称.
     执行这条命令后, MySQL会返回一个包含创建表的完整SQL语句的结果集. 这个语句可以用来重新创建该表.
     
在实际使用中, 你可以根据自己的需求选择合适的方法来查看数据表的结构.
如果你只需要查看字段的基本信息(如字段名, 数据类型等), 那么使用 DESCRIBE 或 DESC 就足够了.
如果你需要查看表的完整定义, 包括字符集, 校对规则以及所有的约束和索引, 那么 SHOW CREATE TABLE 会更合适.

-- 查看表结构
mysql> DESC emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_id   | int         | YES  |     | NULL    |       |
| emp_name | varchar(20) | YES  |     | NULL    |       |
| salary   | double      | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

输出中的列有特定的含义:
* 1. Field: 列名, 表示表中的字段名称.
* 2. Type: 数据类型, 表示该列存储的数据类型, 例如, int 表示整数, varchar(20) 表示可变字符, 最大长度为20个字符.
* 3. Null: 是否允许为NULL. 如果这一列的值是 YES, 那么该列的值可以是NULL; 如果值是NO, 则该列的值不能是NULL.
* 4. Key: 索引类型, 如果这一列是表的某个索引的一部分(如主键, 唯一索引或常规索引), 这里会显示相应的信息.
     常见的值有: PRI(主键), UNI(唯一索引)或空(没有索引).
* 5. Default: 默认值. 如果为某列指定了默认值, 这里会显示出来.
     如果列允许 NULL 并且没有指定默认值, 则这一列通常是NULL.
* 6. Extra: 额外的信息. 这里可能包含一些特殊的信息, 比如 auto_increment(表示该列是自增的),
     或者如果列是最后一个并且数据类型是 timestamp, 
     则可能是 on update current_timestamp(表示当行被更新时, 这个 timestamp 列会被设置为当前时间戳).

-- 查看表创建语句:
mysql> SHOW CREATE TABLE emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `emp_id` int DEFAULT NULL,
  `emp_name` varchar(20) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

4.3 修改表格

修改指的是修改数据库中已经存在的数据表的结构.
MySQL中使用ALTER TABLE语句修改已存在的数据表的结构.
使用 ALTER TABLE 语句可以实现:
* 1. 向已有的表中添加列:  ALTER TABLE table_name ADD column_name datatype [FIRST|AFTER column_name];
* 2. 修改现有表中的列:    ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
     某些MySQL版本中:    ALTER TABLE table_name CHANGE old_column_name new_column_name new_datatype;
                        ALTER TABLE table_name MODIFY COLUMN address TEXT;
                        ALTER TABLE table_name CHANGE address new_address VARCHAR(255);
* 3. 删除现有表中的列:   ALTER TABLE table_name DROP COLUMN column_name;
* 4. 重命名现有表中的列: ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;

4.3.1 添加一个列

在MySQL中, 使用ALTER TABLE语句添加新字段时, 可以指定新字段应该放在哪个现有字段之后, 或者使用FIRST关键字将其放在表的开头.

下面是使用这种语法的示例:
假设您有一个名为my_table的表, 并且想在existing_column字段之后添加一个新字段new_column, 数据类型为VARCHAR(255),
可以这样做: ALTER TABLE my_table ADD new_column VARCHAR(20) AFTER existing_column;
这条语句会在my_table表的existing_column字段之后添加名为new_column的新字段, 数据类型为VARCHAR(20).

如果您想将新字段放在表的开头, 可以使用FIRST关键字:
ALTER TABLE my_table ADD new_column VARCHAR(255) FIRST;
这条语句会在my_table表的开头添加新字段.

请注意, AFTER column_name和FIRST是可选的.
如果省略它们, 新字段将默认添加到表的末尾.

-- 尾部追加字段
mysql> ALTER TABLE emp ADD age INT;  -- 在emp表的末尾追加加一个age字段:
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_id   | int         | YES  |     | NULL    |       |
| emp_name | varchar(20) | YES  |     | NULL    |       |
| salary   | double      | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |  -- 新增的字段 
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- 头部追加字段:
mysql>  ALTER TABLE emp ADD emp_pk INT FIRST;  -- 在emp表的头部追加加一个emp_pk字段:
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_pk   | int         | YES  |     | NULL    |       |  -- 头部追加
| emp_id   | int         | YES  |     | NULL    |       |
| emp_name | varchar(20) | YES  |     | NULL    |       |
| salary   | double      | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       | 
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

-- 在某个字段后追加字段:
mysql> ALTER TABLE emp ADD job_id INT AFTER salary;  -- 在salary字段后追加岗位id
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_pk   | int         | YES  |     | NULL    |       |
| emp_id   | int         | YES  |     | NULL    |       |
| emp_name | varchar(20) | YES  |     | NULL    |       |
| salary   | double      | YES  |     | NULL    |       |
| job_id   | int         | YES  |     | NULL    |       |  -- 指定位置增加
| birthday | date        | YES  |     | NULL    |       |  
| age      | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

4.3.2 修改一个列

可以修改列的数据名称, 类型, 长度, 默认值和位置, 详细的解释和示例:
* 1. 修改列的名称:     ALTER TABLE table_name CHANGE [COLUMN] old_column_name new_column_name datatype;
* 2. 修改列的数据类型: ALTER TABLE table_name MODIFY [COLUMN] column_name new_datatype;
* 3. 修改列的长度:     ALTER TABLE table_name MODIFY [COLUMN] column_name VARCHAR(new_length);
* 4. 修改列的默认值:   ALTER TABLE table_name MODIFY [COLUMN] column_name datatype DEFAULT new_default_value;
* 5. 修改列的位置:     ALTER TABLE table_name MODIFY [COLUMN] column_name datatype AFTER another_column_name;
     或:              ALTER TABLE table_name MODIFY [COLUMN] column_name datatype FIRST;

注意事项:
- COLUMN关键字在SQL语句中不是必需的, 但使用它可以使代码更加清晰和易于理解.
- datatype是必须指定的, 尤其是在你想要改变列的数据类型时.
  即使你不打算改变数据类型, 而只是想要修改其他属性(如默认值或位置), 数据库管理系统仍然要求你显式地包含数据类型.
- 上述操作中, 如果改变了datatype的值, 那么意味着同时修改该列的数据类型.

综合示例, 假设您有一个名为users的表, 并且您想修改username列的数据类型为VARCHAR(100), 为其设置默认值'anonymous',
并将其放在email列之后, 可以这样写:
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) DEFAULT 'anonymous' AFTER email;

请注事项:
当你更改列的数据类型时, 必须确保现有的数据能够适配新的数据类型, 否则操作会失败.
如果数据无法适配, 可能需要先对数据进行清洗或转换, 或者考虑其他解决方案.

4.3.2.1 修改字段名称

-- 修改emp_id字段的名称:
mysql> ALTER TABLE emp CHANGE COLUMN emp_id gender INT;  -- 只修改名称
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查询表结构:
mysql> DESC emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ...      | ...         | ...  |     | ...     |       |  -- 省略
| gender   | int         | YES  |     | NULL    |       |
| ...      | ...         | ...  |     | ...     |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

-- 修改名称时一同修改类型:
mysql> ALTER TABLE emp CHANGE COLUMN gender sex VARCHAR(24);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查询表结构:
mysql> DESC emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ...      | ...         | ...  |     | ...     |       |  -- 省略
| sex      | varchar(24) | YES  |     | NULL    |       |
| ...      | ...         | ...  |     | ...     |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

4.3.2.2 修改字段类型

-- 修改emp表中slary的类型:
mysql> ALTER TABLE emp MODIFY COLUMN salary VARCHAR(32);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ...      | ...         | ...  |     | ...     |       |  -- 省略
| salary   | varchar(32) | YES  |     | NULL    |       |
| ...      | ...         | ...  |     | ...     |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

4.3.2.3 修改字段长度

-- 修改emp表中emp_name的长度:
mysql> ALTER TABLE emp MODIFY COLUMN emp_name VARCHAR(24);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ...      | ...         | ...  |     | ...     |       |  -- 省略
| emp_name | varchar(24) | YES  |     | NULL    |       |
| ...      | ...         | ...  |     | ...     |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

4.3.2.4 修改字段默认值

-- 修改emp表中job_id的默认值:
mysql> ALTER TABLE emp MODIFY COLUMN job_id INT DEFAULT 1;  -- 将job_id的默认值设置为1
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ...      | ...         | ...  |     | ...     |       |  -- 省略
| job_id   | int         | YES  |     | 1       |       |
| ...      | ...         | ...  |     | ...     |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

-- 可以在修改默认值的时候修改数据类型:
mysql> ALTER TABLE emp MODIFY COLUMN job_id VARCHAR(20) DEFAULT 'IT';  -- 将job_id的类型设置为字符串默认值设置为IT
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ...      | ...         | ...  |     | ...     |       |  -- 省略
| job_id   | varchar(20) | YES  |     | IT      |       |
| ...      | ...         | ...  |     | ...     |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

4.3.2.5 修改字段位置

-- 修改列的位置:
mysql> ALTER TABLE emp MODIFY COLUMN age INT AFTER emp_name;  -- 修改age字段在emp_name字段后面
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ...      | ...         | ...  |     | ...     |       |  -- 省略
| emp_name | varchar(24) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| ...      | ...         | ...  |     | ...     |       |  
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

4.3.3 删除一个列

在MySQL中, 可以使用 ALTER TABLE 语句配合 DROP COLUMN 子句删除一个列.

下面是一个基本的例子来说明如何操作:
假设你有一个名为my_table的表, 并且你想要删除名为my_column的列, 可以使用以下SQL语句:
ALTER TABLE my_table DROP COLUMN column_name;
这条命令会从my_table表中删除column_name 列.

注意事项:
* 1. 请确保已经备份了数据库或表, 以防万一出现错误或需要回滚操作.
* 2. 该列没有作为其他表的外键约束.
* 3. 该列没有包含在任何视图或触发器中。
* 4. 如果列被其他数据库对象(如视图, 触发器或外键约束)引用, 那么你可能需要先删除或修改这些对象, 然后再尝试删除列.

-- 删除emp表中的sex字段:
mysql> ALTER TABLE emp DROP COLUMN sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
DESC emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| emp_pk   | int         | YES  |     | NULL    |       |
| emp_name | varchar(24) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| salary   | varchar(32) | YES  |     | NULL    |       |
| job_id   | varchar(20) | YES  |     | IT      |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

4.4 表格重命名

在MySQL中, 可以使用RENAME TABLE语句重命名一个表.
还可以使用ALTER TABLE ... RENAME [TO] ... 语句来重命名, 子句中的TO关键字是可选的.

以下是如何使用它的基本示例:
假设你有一个名为old_table_name的表, 并且你想要将其重命名为new_table_name,

可以使用以下SQL语句:
RENAME TABLE old_table_name TO new_table_name;
这条命令会将old_table_name重命名为new_table_name.

或:
ALTER TABLE old_table_name RENAME TO new_table_name;
ALTER TABLE old_table_name RENAME new_table_name;

如果你是在一个包含多个表的数据库中重命名表, 并且你想要同时重命名多个表, 可以这样做:
RENAME TABLE old_table_name1 TO new_table_name1,  
             old_table_name2 TO new_table_name2,  
             ...;
             
注意事项:
- 不允许重命名到一个已存在的表名.
- 不能有其他数据库对象(如视图, 触发器或存储过程)依赖于old_table_name, 否则你可能需要先更新或删除这些依赖项.

-- 方式1: 修改emp表格名称:
mysql> RENAME TABLE emp TO myemp;
Query OK, 0 rows affected (0.01 sec)

-- 查看所有表格:
mysql> SHOW TABLES;
+----------------------+
| Tables_in_db0        |
+----------------------+
| highsalary_employees |
| myemp                |
+----------------------+
2 rows in set (0.00 sec)

-- 方式2: 修改emp表格名称:
mysql> ALTER TABLE myemp RENAME TO emp;
Query OK, 0 rows affected (0.02 sec)

-- 查看所有表格:
mysql> SHOW TABLES;
+----------------------+
| Tables_in_db0        |
+----------------------+
| emp                  |
| highsalary_employees |
+----------------------+
2 rows in set (0.00 sec)

4.5 删除表格

在MySQL中, 可以使用DROP TABLE语法删除表格.

语法格式:
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, ..., 数据表n];

语法解释:
* 1. DROP TABLE 是用来删除数据表的SQL语句.
* 2. [IF EXISTS] 是一个可选的子句.
     如果使用了这个子句, 并且在数据库中不存在指定的数据表, MySQL将不会报错,
     而是简单地忽略该语句并继续执行后续操作(如果有的话).
     如果不使用[IF EXISTS], 而指定的数据表不存在, MySQL会抛出一个错误.
* 3. 数据表1, 数据表2, ..., 数据表n: 是你想要删除的数据表的名称列表.
     你可以在一个DROP TABLE语句中指定多个数据表名称, 用逗号分隔.

删除数据表的影响:
* 1. 数据和结构都被删除: 当你删除一个数据表时, 该表中的所有数据行以及表结构(包括列定义, 约束, 索引等)都会被永久删除.

* 2. 所有正在运行的相关事务被提交: 如果有任何事务正在访问或修改该表, 当执行DROP TABLE语句时, 这些事务会被强制提交.
     这意味着它们所做的任何更改都会被保存, 但之后无法再访问该表.

* 3. 所有相关索引被删除: 与表相关联的所有索引(无论是主键, 唯一索引还是普通索引)都会被删除.

* 4. 不能回滚: DROP TABLE是一个DDL(数据定义语言)操作, 它在MySQL中是不能回滚的.
     一旦执行了DROP TABLE语句, 就无法撤销该操作, 除非你之前做了备份.

注意事项:
* 1. 在执行DROP TABLE语句之前, 请确保已经备份了任何重要的数据, 以防意外删除.
* 2. 确保没有其他数据库对象(如触发器, 视图或外键)依赖于您想要删除的数据表, 否则可能需要先删除或修改这些依赖对象.
* 3. 如果可能的话, 最好在数据库的低峰时段或维护窗口执行此类操作, 以减少对业务的影响.

-- 删除emp表:
mysql> DROP TABLE IF EXISTS emp;
Query OK, 0 rows affected (0.01 sec)

-- 查看所有表格:
mysql> SHOW TABLES;
+----------------------+
| Tables_in_db0        |
+----------------------+
| highsalary_employees |
+----------------------+
1 row in set (0.00 sec)

4.6 清空表格

在MySQL中, 使用TRUNCATE TABLE或SELECT FROM TABLE语句删除表中所有记录的语句.

以下是关于 TRUNCATE TABLE 语句的详细信息:
功能:
* 1. 删除所有记录: TRUNCATE TABLE语句删除表中的所有数据行, 但不删除表本身或其结构.
* 2. 不记录个别行的删除: 由于TRUNCATE操作不记录个别行的删除, 它通常比 DELETE 语句更快,
     因为DELETE会为每一行生成一个日志条目.
* 3. 重置任何自增计数器: 如果表有一个自增字段(如AUTO_INCREMENT), TRUNCATE TABLE会重置该字段的计数器到其初始值.

* 4. 释放空间: 在某些数据库系统中, TRUNCATE TABLE可能会释放表所使用的磁盘空间, 使其可用于数据库的其他部分.
     但是, 不是所有数据库系统都会这样操作; 这取决于特定的数据库实现.

语法: 
TRUNCATE TABLE table_name;
其中table_name是你想要删除所有记录的表的名称.

注意事项:
* 1. 不可回滚: 与DROP TABLE类似, TRUNCATE TABLE也是一个不可回滚的操作.
     一旦执行, 数据将不可恢复, 除非你有备份.

* 2. 触发器不执行: 与DELETE不同, TRUNCATE TABLE不会激活与表相关联的触发器.

* 3. 权限要求: 执行TRUNCATE TABLE通常需要具有对该表的TRUNCATE权限, 或者具有DROP权 
     (因为某些数据库系统可能将TRUNCATE视为一种删除表的特殊形式).

* 4. 锁定表: 在执行TRUNCATE TABLE时, 表通常会被锁定, 以防止其他用户同时访问或修改它.

举例: 假设你有一个名为employees的表, 并且你想要删除该表中的所有记录:
TRUNCATE TABLE employees;
执行这条语句后, employees表中的所有数据行都将被删除, 但表本身及其结构将保持不变.
自增字段(如果有的话)将被重置到其初始值.

对比 DELETE 和 TRUNCATE:
* 1. DELETE 语句可以带有 WHERE 子句来删除特定的行, 而 TRUNCATE TABLE 删除所有行.
* 2. DELETE 是一个可以回滚的事务操作, 而 TRUNCATE TABLE 通常不可回滚.
* 3. DELETE 会激活与表相关的触发器, 而 TRUNCATE TABLE 不会.
* 4. DELETE 会记录每一行的删除操作, 可能生成大量的日志, 而 TRUNCATE TABLE 不记录个别行的删除, 因此通常更快.

在MySQL中, 回滚操作通常用于撤销在事务中所做的更改, 使数据库回到事务开始之前的状态.
以下是执行回滚操作的基本步骤:
* 1. 开始事务: 首先, 需要开始一个新的事务. 这可以通过使用START TRANSACTION或BEGIN语句来完成.
* 2. 执行SQL语句, 在事务中, 可以执行一系列的SQL语句, 如: INSERT, UPDATE, DELETE等.
* 3. 遇到错误或决定回滚: 如果在执行这些语句的过程中遇到错误, 或者你决定撤销这些更改, 可以使用ROLLBACK语句来回滚事务.

-- 0. 创建一个表测试:
mysql> CREATE TABLE emp AS
SELECT employee_id, first_name, salary, department_id  FROM atguigudb.employees;
Query OK, 107 rows affected, 1 warning (0.03 sec)
Records: 107  Duplicates: 0  Warnings: 1

-- 1. 开启事务:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

-- 2. 删除所有数据:
mysql> DELETE FROM emp;
Query OK, 107 rows affected (0.01 sec)  -- 删除107条数据	
mysql> SELECT * FROM emp;
Empty set (0.00 sec)

-- 3. 表结构依旧存在:
mysql> DESC emp;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| employee_id   | int         | NO   |     | 0       |       |
| first_name    | varchar(20) | YES  |     | NULL    |       |
| salary        | double(8,2) | YES  |     | NULL    |       |
| department_id | int         | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 4. 回滚(同时, 事务的状态也会变为非活动状态, 即事务已经关闭):
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

-- 5. 查看数据
mysql> SELECT * FROM emp;
+-------------+-------------+----------+---------------+
| employee_id | first_name  | salary   | department_id |
+-------------+-------------+----------+---------------+
|         100 | Steven      | 24000.00 |            90 |
|         ... | ...         |      ... |           ... |  -- 省略
|         206 | William     |  8300.00 |           110 | 
+-------------+-------------+----------+---------------+
107 rows in set (0.00 sec)

5. 数据回滚

---

5.1 DML与DDL介绍

DML和DDL是数据库管理系统中的两种重要语言:
* 1. DML(Data Manipulation Language, 数据操纵语言): 用于对数据库中的数据进行增删改操作.
     常见的DML命令包括(DML的主要操作对象是表里的数据(记录)): 
     1. INSERT(用于增加数据到数据库).
     2. UPDATE(用于从数据库中修改现存的数据).
     3. DELETE(用于从数据库中删除数据)等语句.

* 2. DDL(Data Definition Language, 数据库定义语言): 用于对数据库内部的对象进行创建, 删除, 修改等操作.
     这些对象包括表, 视图, 索引, 序列等. DDL的主要目标是定义或改变数据库结构, 而不是处理数据本身.
     常见的DDL命令包括:
     1. CREATE(用于创建数据库或数据库中的对象, 如表, 视图, 索引等). 
     2. ALTER(用于修改数据库或数据库中的对象, 如表结构, 视图定义等).
     3. DROP(用于删除数据库或数据库中的对象, 如表, 视图等).
     DDL的主要作用是对数据库的整体结构进行操作, 确保数据库的结构与业务需求相匹配.
     与DML(数据操纵语言)不同, DML主要用于对数据库中的数据进行增删改操作, 而DDL则更侧重于数据库结构的定义和修改.

5.2 COMMIT与ROLLBACK介绍

COMMIT 和 ROLLBACK 是 SQL 语言中的两个关键命令, 它们与数据库事务的处理密切相关.
事务是数据库管理系统(DBMS)中执行的一个工作单元, 它可以包含多个SQL语句.
事务的主要目的是确保数据的完整性和一致性, 即使在多个操作失败或中断的情况下也能做到这一点.

* 1. COMMIT命令: 用于提交事务, 即将事务中所做的所有更改永久保存到数据库中(意味着数据无法回滚).
     当你执行一个事务, 并对数据库进行了更改(例如, 插入, 更新或删除记录), 这些更改在事务完成之前不会永久生效.
     只有在执行COMMIT命令后, 这些更改才会被保存到数据库中, 并且对其他数据库用户可见.

* 2. ROLLBACK: 命令用于撤销事务中所做的所有更改(回到最近的一次COMMIT之后).
     如果在执行事务的过程中发生错误, 或者由于某种原因你决定不保存所做的更改, 你可以使用ROLLBACK命令来撤销事务中的所有操作.
     这会将数据库恢复到事务开始之前的状态.

5.3 关于回滚的问题

关于回滚的问题:
* 1. DDL操作一旦执行, 确实无法回滚.
     这是因为DDL操作在执行前后会自动执行commit, 所以不能使用rollback来回滚DDL操作的结果.
     然而, 如果在DDL操作执行过程中由于某种原因失败, 系统会自动将其回滚, 这属于隐式回滚, 用户无法控制.

* 2. DML操作, 在默认情况下, 一旦执行也是不可回滚的.
     但是, 如果在执行DML操作之前, 执行了SET autocommit = FALSE, 则执行的DML操作就可以实现回滚.
     这是因为当autocommit设置为FALSE时, DML操作不会立即提交, 而是等待后续的commit或rollback命令.
     
详细说明:
当设置 SET autocommit = FALSE 之后, 数据库管理系统的行为会发生变化, 特别是针对DML(数据操纵语言)操作.

在默认情况下, 大多数数据库系统都设置为自动提交模式(autocommit = TRUE).
这意味着每次执行DML操作时(如INSERT, UPDATE或DELETE), 更改会立即被提交到数据库, 成为永久性的更改.

但是, 当你执行 SET autocommit = FALSE 后, 数据库进入手动提交模式(临时性).
这意味着后续的DML操作不会立即提交到数据库.
相反, 它们会等待一个显式的 COMMIT 命令来提交更改, 或者如果出现问题, 可以使用 ROLLBACK 命令撤销这些更改.

这种手动提交模式在处理多个相关的DML操作时非常有用, 因为它允许你将它们组合成一个单一的事务.
通过这样做, 你可以确保所有操作要么全部成功, 要么在出现问题时全部回滚, 从而保持数据的一致性.

然而, 值得注意的是, SET autocommit = FALSE 对DDL(数据定义语言)操作没有影响.
DDL操作(如CREATE, ALTER和DROP)在执行时总是自动提交, 因此无法回滚, 即使autocommit被设置为FALSE.

-- 0. 创建一个表测试:
mysql> CREATE TABLE emp1 AS
SELECT employee_id, first_name, salary, department_id  FROM atguigudb.employees;
Query OK, 107 rows affected, 1 warning (0.03 sec)
Records: 107  Duplicates: 0  Warnings: 1

-- 1. 临时关闭自动提交:
mysql> SET autocommit = FALSE;
Query OK, 0 rows affected (0.00 sec)

-- 2. 查看数据:
mysql> SELECT * FROM emp1;
...
107 rows in set (0.00 sec)  -- 107条数据	

-- 3. 删除所有数据:
mysql> DELETE FROM emp1;
Query OK, 107 rows affected (0.01 sec)  -- 删除107条数据	

-- 4. 查看数据:
mysql> SELECT * FROM emp1;
Empty set (0.00 sec)

-- 5. 回滚
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

-- 6. 查看数据:
mysql> SELECT * FROM emp1;
...
107 rows in set (0.00 sec)  -- 107条数据	

-- 7. 再次删除数据:
mysql> DELETE FROM emp1;
Query OK, 107 rows affected (0.00 sec)

-- 8. 查看数据:
mysql> SELECT * FROM emp1;
Empty set (0.00 sec)

-- 9. 提交事务:
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
                           
-- 10. 回滚(回滚到最近一次COMMIT之后):
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

-- 11. 查看数据:
mysql> SELECT * FROM emp1;
Empty set (0.00 sec)

5.4 事务的完整性

在MySQL 8.0版本中, InnoDB表的DDL支持事务完整性, 即DDL操作要么成功要么回滚.
DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log中, 
该表是隐藏的表, 通过show tables无法看到中, 用于回滚操作.
通过设置参数, 可将DDL操作日志打印输出到MySQL错误日志中.

-- 分别在MySQL 5.7版本和MySQL 8.0版本中创建数据库和数据表, 结果如下:
mysql> CREATE DATABASE mytest;
Query OK, 1 row affected (0.00 sec)

mysql> USE mytest;
Database changed

mysql> CREATE TABLE book1(
    book_id INT,
    book_name VARCHAR(255)
);
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_mytest |
+------------------+
| book1            |
+------------------+
1 row in set (0.01 sec)

-- (1) 在MySQL 5.7版本中, 测试步骤如下:
-- 1. 删除数据表book1和数据表book2, 结果如下:
mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

-- 2. 再次查询数据库中的数据表名称，结果如下:
mysql> SHOW TABLES;
Empty set (0.00 sec)

-- 3. 从结果可以看出, 虽然删除操作时报错了, 但是仍然删除了数据表book1.

-- (2)在MySQL 8.0版本中, 测试步骤如下:
-- 1. 删除数据表book1和数据表book2, 结果如下:
mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

-- 2. 再次查询数据库中的数据表名称, 结果如下:
mysql> show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| book1      |
+------------------+
1 row in set (0.00 sec)
-- 3. 从结果可以看出, 数据表book1并没有被删除.

6. 练习

---

6.1 练习1

-- 1. 创建数据库test01_office, 指明字符集为utf8.
mysql> CREATE DATABASE test01_office CHARACTER SET 'utf8';
Query OK, 1 row affected, 1 warning (0.01 sec)

-- 2. 使用test01_office数据库:
mysql> USE test01_office;
Database changed

-- 3. 创建表dept01表:
-- 字段 类型
-- id   INT(7)
-- NAME VARCHAR(25) 
mysql> CREATE TABLE dept01(
    id INT,
    name VARCHAR(25)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC dept01;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(25) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 4. 将表departments中的数据插入新表dept02中:
mysql> CREATE TABLE dept02 AS SELECT * FROM atguigudb.departments;
Query OK, 27 rows affected (0.01 sec)
Records: 27  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC dept02;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| department_id   | int         | NO   |     | 0       |       |
| department_name | varchar(30) | NO   |     | NULL    |       |
| manager_id      | int         | YES  |     | NULL    |       |
| location_id     | int         | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 查看表数据:
mysql> SELECT * FROM dept02;
...
27 rows in set (0.00 sec)

-- 5. 创建表emp01
-- 字段         类型
-- id I         NT(7)
-- first_name   VARCHAR(25)
-- last_name    VARCHAR(25)
-- dept_id      INT(7)

CREATE TABLE emp01(
    id INT,
    first_name VARCHAR(25),
    LAST_name  VARCHAR(25),
    dept_id INT
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC emp01;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | YES  |     | NULL    |       |
| first_name | varchar(25) | YES  |     | NULL    |       |
| LAST_name  | varchar(25) | YES  |     | NULL    |       |
| dept_id    | int         | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 5. 将emp01表的last_name列的长度增加到50:
mysql> ALTER TABLE emp01 MODIFY COLUMN last_name VARCHAR(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp01;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int         | YES  |     | NULL    |       |
| first_name | varchar(25) | YES  |     | NULL    |       |
| last_name  | varchar(50) | YES  |     | NULL    |       |
| dept_id    | int         | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 6. 根据表employees创建emp02:
mysql> CREATE TABLE emp02 AS SELECT * FROM atguigudb.employees;
Query OK, 107 rows affected, 2 warnings (0.02 sec)
Records: 107  Duplicates: 0  Warnings: 2

-- 查看表结构:
mysql> DESC emp02;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_id     | int         | NO   |     | 0       |       |
| first_name      | varchar(20) | YES  |     | NULL    |       |
| last_name       | varchar(25) | NO   |     | NULL    |       |
| email           | varchar(25) | NO   |     | NULL    |       |
| phone_number    | varchar(20) | YES  |     | NULL    |       |
| hire_date       | date        | NO   |     | NULL    |       |
| job_id          | varchar(10) | NO   |     | NULL    |       |
| salary          | double(8,2) | YES  |     | NULL    |       |
| commission_pct  | double(2,2) | YES  |     | NULL    |       |
| manager_id      | int         | YES  |     | NULL    |       |
| department_id   | int         | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

-- 查看表数据:
mysql> SELECT * FROM emp02;
...
107 rows in set (0.00 sec)

-- 7. 删除表emp01:
mysql> DROP TABLE emp01;
Query OK, 0 rows affected (0.01 sec)

-- 查看所有表格:
mysql> SHOW TABLES;
+-------------------------+
| Tables_in_test01_office |
+-------------------------+
| dept01                  |
| dept02                  |
| emp02                   |
+-------------------------+
3 rows in set (0.00 sec)

-- 8. 将表emp02重命名为emp01:
mysql> RENAME TABLE emp02 TO emp01;
Query OK, 0 rows affected (0.02 sec)

-- 查看所有表格:
mysql> SHOW TABLES;
+-------------------------+
| Tables_in_test01_office |
+-------------------------+
| dept01                  |
| dept02                  |
| emp01                   |
+-------------------------+
3 rows in set (0.00 sec)

-- 9. 在表dept02和emp01中添加新列test_column, 并检查所作的操作:
-- 9.1 在dept02表中添加新列test_column, 类型为INT:
mysql> ALTER TABLE dept02 ADD test_column INT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 9.2 查看dep02的表结构:
mysql> DESC dept02;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| department_id   | int         | NO   |     | 0       |       |
| department_name | varchar(30) | NO   |     | NULL    |       |
| manager_id      | int         | YES  |     | NULL    |       |
| location_id     | int         | YES  |     | NULL    |       |
| test_column     | int         | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- 9.3 在emp01表中添加新列test_column, 类型为INT:
mysql> ALTER TABLE emp01 ADD test_column INT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 9.4 查询emp01的表结构:
mysql> DESC emp01;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| ...             | ...         | ...  |     | ...     |       |
| test_column     | int         | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
12 rows in set (0.00 sec)

-- 10. 直接删除表emp01中的列department_id:
mysql> ALTER TABLE emp01 DROP COLUMN department_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC emp01;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| employee_id     | int         | NO   |     | 0       |       |
| first_name      | varchar(20) | YES  |     | NULL    |       |
| last_name       | varchar(25) | NO   |     | NULL    |       |
| email           | varchar(25) | NO   |     | NULL    |       |
| phone_number    | varchar(20) | YES  |     | NULL    |       |
| hire_date       | date        | NO   |     | NULL    |       |
| job_id          | varchar(10) | NO   |     | NULL    |       |
| salary          | double(8,2) | YES  |     | NULL    |       |
| commission_pct  | double(2,2) | YES  |     | NULL    |       |
| manager_id      | int         | YES  |     | NULL    |       |
| test_column     | int         | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

6.2 练习2

-- 1. 创建数据库test02_market:
mysql> CREATE DATABASE test02_market;
Query OK, 1 row affected (0.01 sec)

-- 2. 使用test02_market数据库:
mysql> USE test02_market;
Database changed

-- 3. 创建数据表 customers表:
-- 字段名     数据类型
-- c_num     int
-- c_name    varchar(50)
-- c_contact varchar(50)
-- c_city    varchar(50)
-- c_birth   date
mysql> CREATE TABLE customers(
    c_num INT,
    c_name VARCHAR(50),
    c_contact VARCHAR(50),
    c_city VARCHAR(50),
    c_birth DATE
);
Query OK, 0 rows affected (0.01 sec)

-- 查看表结构:
mysql> DESC customers;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| c_num     | int         | YES  |     | NULL    |       |
| c_name    | varchar(50) | YES  |     | NULL    |       |
| c_contact | varchar(50) | YES  |     | NULL    |       |
| c_city    | varchar(50) | YES  |     | NULL    |       |
| c_birth   | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- 4. 将c_contact字段移动到c_birth字段后面:
mysql> ALTER TABLE customers MODIFY COLUMN c_contact VARCHAR(50) AFTER c_birth;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC customers;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| c_num     | int         | YES  |     | NULL    |       |
| c_name    | varchar(50) | YES  |     | NULL    |       |
| c_city    | varchar(50) | YES  |     | NULL    |       |
| c_birth   | date        | YES  |     | NULL    |       |
| c_contact | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- 5. 将c_name字段数据类型改为varchar(70):
mysql> ALTER TABLE customers MODIFY COLUMN c_name VARCHAR(70);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC customers;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| c_num     | int         | YES  |     | NULL    |       |
| c_name    | varchar(70) | YES  |     | NULL    |       |
| ...       | ...         | ...  |     | ...     |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- 6. 将c_contact字段改名为c_phone:
mysql> ALTER TABLE customers CHANGE COLUMN c_contact c_phone VARCHAR(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC customers;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ...       | ...       | ...  |     | ...     |       |
| c_phone | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- 7. 增加c_gender字段到c_name后面, 数据类型为char(1):
mysql> ALTER TABLE customers ADD c_gender CHAR(1) AFTER c_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC customers;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| c_num    | int         | YES  |     | NULL    |       |
| c_name   | varchar(70) | YES  |     | NULL    |       |
| c_gender | char(1)     | YES  |     | NULL    |       |
| ...       | ...       | ...  |     | ...     |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

-- 8. 将表名改为customers_info:
mysql> ALTER TABLE customers RENAME TO customers_info;
Query OK, 0 rows affected (0.01 sec)

-- 查看所有表格:
mysql> SHOW TABLES;
+-------------------------+
| Tables_in_test02_market |
+-------------------------+
| customers_info          |
+-------------------------+
1 row in set (0.00 sec)

-- 9. 删除字段c_city:
mysql> DESC customers_info;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| c_num    | int         | YES  |     | NULL    |       |
| c_name   | varchar(70) | YES  |     | NULL    |       |
| c_gender | char(1)     | YES  |     | NULL    |       |
| c_birth  | date        | YES  |     | NULL    |       |
| c_phone  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

6.3 练习3

-- 1. 创建数据库test03_company:
mysql> CREATE DATABASE test03_company;
Query OK, 1 row affected (0.01 sec)

-- 2. 使用数据库:
mysql> USE test03_company;
Database changed

-- 3. 创建表offices:
-- 字段名      数据类型
-- officeCode int
-- city       varchar(30)
-- address    varchar(50)
-- country    varchar(50)
-- postalCode varchar(25)
mysql> CREATE TABLE offices(
    officeCode INT,
    city VARCHAR(30),
    address VARCHAR(50),
    country VARCHAR(50),
    POSTALcODE VARCHAR(25)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC offices;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| officeCode | int         | YES  |     | NULL    |       |
| city       | varchar(30) | YES  |     | NULL    |       |
| address    | varchar(50) | YES  |     | NULL    |       |
| country    | varchar(50) | YES  |     | NULL    |       |
| POSTALcODE | varchar(25) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

-- 4. 创建表employees:
-- 字段名    数据类型
-- empNum    int
-- lastName  varchar(50)
-- firstName varchar(50)
-- mobile    varchar(25)
-- code      int
-- jobTitle  varchar(50)
-- birth     date
-- note      varchar(255)
-- sex       varchar(5)
mysql> CREATE TABLE employees(
    empNum INT,
    lastName VARCHAR(50),
    firstNmae VARCHAR(50),
    mobile VARCHAR(25),
    code INT,
    jobTitle VARCHAR(50),
    birth date,
    note VARCHAR(255),
    sex VARCHAR(5)
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC employees;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| empNum    | int          | YES  |     | NULL    |       |
| lastName  | varchar(50)  | YES  |     | NULL    |       |
| firstNmae | varchar(50)  | YES  |     | NULL    |       |
| mobile    | varchar(25)  | YES  |     | NULL    |       |
| code      | int          | YES  |     | NULL    |       |
| jobTitle  | varchar(50)  | YES  |     | NULL    |       |
| birth     | date         | YES  |     | NULL    |       |
| note      | varchar(255) | YES  |     | NULL    |       |
| sex       | varchar(5)   | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

-- 5. 将表employees的mobile字段修改到code字段后面:
mysql> ALTER TABLE employees MODIFY COLUMN mobile VARCHAR(25) AFTER code;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC employees;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ...       | ...          | ...  |     | ...     |       |
| code      | int          | YES  |     | NULL    |       |
| mobile    | varchar(25)  | YES  |     | NULL    |       |
| ...       | ...          | ...  |     | ...     |       |
+-----------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

-- 6. 将表employees的birth字段改名为birthday:
mysql> ALTER TABLE employees CHANGE COLUMN birth birthday DATE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC employees;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ...       | ...          | ...  |     | ...     |       |
| birthday  | date         | YES  |     | NULL    |       |
| ...       | ...          | ...  |     | ...     |       |
+-----------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

-- 7. 修改sex字段, 数据类型为char(1):
mysql> ALTER TABLE employees MODIFY COLUMN sex CHAR(1);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC employees;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ...       | ...          | ...  |     | ...     |       |
| sex       | char(1)      | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

-- 8. 删除字段note:
mysql> ALTER TABLE employees DROP COLUMN note;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC employees;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| empNum    | int         | YES  |     | NULL    |       |
| lastName  | varchar(50) | YES  |     | NULL    |       |
| firstNmae | varchar(50) | YES  |     | NULL    |       |
| code      | int         | YES  |     | NULL    |       |
| mobile    | varchar(25) | YES  |     | NULL    |       |
| jobTitle  | varchar(50) | YES  |     | NULL    |       |
| birthday  | date        | YES  |     | NULL    |       |
| sex       | char(1)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

-- 9. 增加字段名favoriate_activity, 数据类型为varchar(100):
mysql> ALTER TABLE employees ADD favoriate_activity VARCHAR(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表结构:
mysql> DESC employees;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| ...       | ...          | ...  |     | ...     |       |
| favoriate_activity | varchar(100) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

-- 10. 将表employees的名称修改为employees_info:
mysql> ALTER TABLE employees RENAME TO employees_info;
Query OK, 0 rows affected (0.02 sec)

-- 查看所有表格:
mysql> SHOW TABLES;
+--------------------------+
| Tables_in_test03_company |
+--------------------------+
| employees_info           |
| offices                  |
+--------------------------+
2 rows in set (0.00 sec)