【MySQL 保姆级教学】表结构的操作（4）

ops/2024/10/24 4:14:02/

表结构的操作

  • 1. 定义和语法
  • 2. 创建表 CREATE
    • 2.1 创建表的本质
    • 2.2 表的存储引擎
    • 2.3 表的字符集和校验规则
    • 2.4 创建表实例
  • 3. 查看表结构 DESC
    • 3.1 作用
    • 3.2 示例
  • 4. 修改表结构 ALTER
    • 4.1 添加列 ADD
    • 4.2 修改列 MODIFY
    • 4.3 删除列 DROP
    • 4.4 更改列名 CHANGE
  • 5. 修改表名 RENAME
  • 6. 删除表 DROP

1. 定义和语法

在数据库中，表是用于存储和组织数据的基本结构。表由行（记录）和列（字段）组成，每一行代表一个数据记录，每一列代表一个特定的数据属性。表的定义包括表的名称、列的定义、数据类型、约束和其他属性。

表的定义要素：

  • 表名：表的唯一标识符，用于在数据库中引用该表。
  • 列名：表中每一列的名称，用于标识该列存储的数据。
  • 数据类型：每列的数据类型，定义了该列可以存储的数据种类。
  • 约束：用于确保数据的完整性和一致性，常见的约束包括主键、外键、唯一性、非空等。
  • 索引：用于加速数据检索的特殊数据结构。
  • 其他属性：如默认值、注释等。

语法：

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

说明：

table： 表示列名。
datatype ：表示列的类型。
character set ：字符集，如果没有指定字符集，则以所在数据库的字符集为准。
collate： 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。

2. 创建表 CREATE

2.1 创建表的本质

创建一个study表
命令：create table study ( score int);
这个命令中，study是表名，score是列名，int是类型。
在这里插入图片描述

那么，创建表的本质是什么呢？
我是在Linux中，打开存储数据库test的目录：
命令：ls /var/lib/mysql/test
在这里插入图片描述
同学们可以看到，有两个文件名和我创建的表名一样，所以，创建表的本质就是创建一个文件。

说明：
不同的存储引擎，创建表的文件不一样。
study 表存储引擎是 Innodb ，在数据目中有二个不同的文件，分别是：
study..frm：表的结构信息。
study.ibd：表的数据和索引。

2.2 表的存储引擎

Innodb 存储引擎：

  • .frm 文件：是 Form 文件的缩写，用于存储表的元数据（即表结构信息）。每个表都有一个对应的 .frm 文件，无论使用哪种存储引擎。
  • .ibd 文件：是 InnoDB Data 文件的缩写，用于存储 InnoDB 存储引擎表的数据和索引。每个使用 InnoDB 存储引擎的表都有一个对应的 .ibd 文件。

MyISAM 存储引擎：

  • .frm 文件：存储了表的结构信息，包括列的名称、数据类型、索引定义、约束等。这些信息用于描述表的元数据。
  • .MYD 文件：存储了表的实际数据行。数据按照行的顺序存储，每行数据按照列的定义进行排列。
  • .MYI 文件：
    存储了表的索引信息。索引用于加速数据的检索，包括主键索引、唯一索引和普通索引。

2.3 表的字符集和校验规则

表的时候，有字符集和校验集的设置：

  1. 字符集：定义了数据库中可以存储的字符及其编码方式。字符集决定了每个字符在计算机中如何表示，即每个字符占用多少字节以及具体的二进制编码。

  2. 校验集：定义了字符集中的字符如何进行比较和排序。同一个字符集可以有不同的校验集，不同的校验集决定了字符的大小写敏感性、重音敏感性等。

数字存储的时会使用字符集，数据查找或者匹配，这样会进行数据比较，想要进行比较就需要先读取出来，读取使用校验集，支持数据库进行比较使用的校验集，本质也是一种读取数据库中数据的编码格式

举个简单的例子，如果你存储数据使用浮点类型，而取数据用字符串类型，这样取出来的数据肯定会乱套的。

总之，数据库无论对数据做任何操作，都必须保证操作和编码必须是一致的。

当创建数据库时不设置字符集和校验集，会默认使用mysql配置的字符集和校验集

示例：

创建一个表，同时设置字符集和校验集。
命令：

create table study(
id int primary key,
name varchar(10),
score int
) charset utf8 collate utf8_general_ci;

同学们想更加深入的了解字符集和校验集，请看 数据库的操作(第三章)

2.4 创建表实例

  1. 创建一个study表
    命令：create table study ( score int);
    这个命令中，study是表名，score是列名，int是类型。

  2. 创建一个表，每列加上注释
    命令：

    create table test(
    id int comment ‘学号’,
    name varchar(8) comment ‘姓名’,
    score int comment ‘分数’);

    在这里插入图片描述

  3. 创建一个表，id 设置为主键，同时设置字符集和校验集。
    命令：

    create table study(
    id int primary key,
    name varchar(10),
    score int
    ) charset utf8 collate utf8_general_ci;

    在这里插入图片描述

3. 查看表结构 DESC

3.1 作用

  1. 了解表的定义
  • 列信息：查看表中的所有列及其数据类型、长度、是否允许为空等。
  • 约束信息：查看表中的主键、外键、唯一性约束、检查约束等。
  • 索引信息：查看表中的索引，包括主键索引、唯一索引、普通索引等。
  1. 确保数据模型的正确性
  • 验证列定义：确保表中的列定义符合业务需求，没有遗漏或多余列。
  • 验证约束：确保表中的约束（如主键、外键、唯一性约束）正确无误，以保证数据的完整性和一致性。
  1. 优化查询性能
  • 索引分析：了解表中的索引情况，帮助你优化查询性能。索引可以显著提高查询速度，但过多的索引会增加写操作的开销。
  • 数据类型选择：确保选择了合适的数据类型，以优化存储和查询性能。
  1. 进行数据迁移和备份
  • 备份和恢复：在备份和恢复表时，了解表结构可以帮助你确保备份文件的完整性和正确性。
  • 数据迁移：在将数据从一个数据库迁移到另一个数据库时，了解表结构可以确保数据的一致性和兼容性。
  1. 调试和问题排查
  • 调试查询：在调试复杂的查询时，了解表结构可以帮助你更好地理解查询的执行计划和性能瓶颈。
  • 问题排查：在遇到数据问题时，查看表结构可以帮助你快速定位问题所在。

3.2 示例

语法：desc 表名;

示例：
命令：desc test;

在这里插入图片描述字段其实就是表

4. 修改表结构 ALTER

4.1 添加列 ADD

语法：

ALTER TABLE table_name
ADD column_name datatype [constraints];

示例：

  1. 在表中添加一个 sex 的字段
    命令：

    ALTER TABLE table_name
    ADD column_name datatype [constraints];

    在这里插入图片描述

  2. 添加一个 age 的字段，在name 的后面
    命令：

    alter table test
    add age int comment ‘年龄’ after name;

    在这里插入图片描述

4.2 修改列 MODIFY

语法：

ALTER TABLE table_name
MODIFY column_name new_datatype [new_constraints];

注意：modify 是把原来的字段属性覆盖，而不是想改什么属性就只改变什么属性。在修改表的时候需要查看创建表show create table 表名;

示例：

  1. 查看创建表
    命令：show create table test ;
    在这里插入图片描述
    我们可以看到表的具体结构，方便对表的进行修改。

  2. 修改表的 age ，字段类型改为 tiny，放在 name 后
    命令：

    alter table test
    modify age tinyint comment ‘年龄’ after name;

    在这里插入图片描述

4.3 删除列 DROP

语法：

ALTER TABLE table_name
DROP COLUMN column_name;

注意：删除字段一定要小心，删除字段及其对应的列数据都没了

示例：

  1. 删除表的 score 列（省略 column）
    命令：alter table test drop score;
    在这里插入图片描述
  2. 删除表的 sex 列（省略 column）
    命令：alter table test drop column sex;

4.4 更改列名 CHANGE

语法：

ALTER TABLE table_name
CHANGE old_column_name new_column_name new_datatype [new_constraints];

注意：change 是把原来的字段属性覆盖，而不是想改什么属性就只改变什么属性。在修改表的时候需要查看创建表show create table 表名;

示例：

  1. 查看创建表
    命令：show create table test ;
    在这里插入图片描述

  2. 更改列name 名字
    命令：

    alter table test
    change name student varchar(8) comment ‘姓名’;

    在这里插入图片描述

5. 修改表名 RENAME

语法：
ALTER TABLE old_table_name RENAME TO new_table_name;

示例：

  1. 修改表名
    命令：alter tablee test rename to study;

  2. 修改表名,不使用 to
    命令：alter table test rename study;

6. 删除表 DROP

语法：
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

示例：
命令：drop table test;


http://www.ppmy.cn/ops/128000.html

相关文章

CRMEB标准版Mysql修改sql_mode

CRMEB标准版Mysql修改sql_mode

数据库配置 1.宝塔控制面板-软件商店-MySql-设置 2.点击配置修改，查找sql-mode或sql_mode （可使用CtrlF快捷查找） 3.复制 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 然后替换粘贴，保存 注：MySQL8.0版本的 第三步用…
阅读更多...
记录一个容易混淆的 Spring Boot 项目配置文件问题

记录一个容易混淆的 Spring Boot 项目配置文件问题

记录一个容易混淆的 Spring Boot 项目配置文件问题 去年，我遇到了这样一个问题： 在这个例子中，由于密码 password 以 0 开头，当它被 Spring Boot 的 bean 读取时，前导的 0 被自动去掉了。这导致程序无法正确读取密码。…
阅读更多...
【Flutter】基础入门：自定义Widget

【Flutter】基础入门：自定义Widget

在 Flutter 开发中，除了使用丰富的内置 Widgets 构建界面外，自定义 Widget 是让你的应用更灵活和个性化的重要手段。Flutter 允许你根据需求自定义 StatelessWidget 和 StatefulWidget，以实现复杂的 UI 组件或功能模块。 本教程将通过实例讲…
阅读更多...
Git Push（TODO）

Git Push（TODO）

最近经常碰到GIT push不上去的问题。到处求人解决也真是尴尬，想自己看看，所以刚刚在github上建了一个仓，试了下。结果如下： 暂时可能还不行，因为数据都是加密的，没法看到具体GIT的交互信息。。。 后面再想办…
阅读更多...
CTFHUB技能树之SQL——布尔盲注

CTFHUB技能树之SQL——布尔盲注

开启靶场，打开链接： 输入1： 显示查询成功但没有回显出相关信息，初步判断是布尔盲注入、时间盲注或报错注入 输入1： 还是没有回显 输入1"： 还是没有回显，到这里已经可以确认是布尔盲注了&a…
阅读更多...
深入理解 SQL 中的高级数据处理特性：约束、索引和触发器

深入理解 SQL 中的高级数据处理特性：约束、索引和触发器

在 SQL（Structured Query Language）中，除了基本的查询、插入、更新和删除操作外，还有一些高级的数据处理特性，它们对于确保数据的完整性、提高查询性能以及实现自动化的数据处理起着至关重要的作用。这些特性包括约束、…
阅读更多...
redis过期提醒

redis过期提醒

文章目录 redis过期提醒 redis过期提醒 有一次看redis的配置文件发现一个notify-keyspace-events配置，注释里边长篇大论的，那我得看看这是干啥的，看完注释内容，发现不得了了，redis竟然还有过期提醒的功能 接下来得大…
阅读更多...
elasticsearch中的向量检索，语义检索，RRF，kNN，ANN，HNSW

elasticsearch中的向量检索，语义检索，RRF，kNN，ANN，HNSW

kNN 算法 （k-Nearest Neighbor） KNN可以说是最简单的分类算法之一，同时，它也是最常用的分类算法之一。 kNN算法的核心思想是如果一个样本在特征空间中的k个最相邻的样本中的大多数属于某一个类别，则该样本也属于这个…
阅读更多...
最新文章

Copyright @ 2022~2023