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 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 COLUMN new_column VARCHAR(20) AFTER existing_column;
这条语句会在my_table表的existing_column字段之后添加名为new_column的新字段, 数据类型为VARCHAR(20).
如果您想将新字段放在表的开头, 可以使用FIRST关键字:
ALTER TABLE my_table ADD COLUMN new_column VARCHAR(255) FIRST;
这条语句会在my_table表的开头添加新字段.
请注意, AFTER column_name和FIRST是可选的.
如果省略它们, 新字段将默认添加到表的末尾.
-- 尾部追加字段
mysql> ALTER TABLE emp ADD COLUMN 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 COLUMN 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 COLUMN 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 COLUMN 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 COLUMN 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 COLUMN 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 COLUMN 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)
本文标签:
操作数据库mysql
发表评论