mysql表结构的完整描述（详细教程）

---基于一个列给他加上功能 

mysql表结构的完整描述图示 --用于显示表的结构，涵盖了每个字段的属性（这里字段描述5个）。

表结构描述操作

创建 create table();

显示show 

删除drop

修改表结构 alter table---

一、desc（describe）命令的输出标签---属于修改表结构内容

1、Field（字段名）

• 描述: 列的名称。字段名用于标识表中的数据。

• 用途: 字段名是访问表数据的关键，操作 SQL 查询时引用字段名来选择、更新或删除数据。
• 命名规则: 避免使用 SQL 保留字（如 SELECT、WHERE），字段名应具有描述性，以便代码的可读性和维护性。例如，employee_name 比 name 更具描述性。
• 常见问题: 避免使用特殊字符或空格，字段名应该遵循一致的命名约定，例如使用下划线分隔单词（first_name）。

 2、Type (数据类型)

• 描述: 列的数据类型，定义了数据的存储方式和格式。

• 基本数据类型

1.1、数值类型

• 整型：

  • bigint
  • int（或 integer）
  • mediumint
  • smallint
  • tinyint

• 浮点型：

  • decimal（或 numeric）
  • double
  • float

1.2、字符串类型

• 字符类型：

  • varchar(n) （可变长度）
  • char(n) （固定长度）

• 文本类型：

  • longtext
  • mediumtext
  • text
  • tinytext

• 二进制类型：

  • longblob
  • mediumblob
  • blob
  • tinyblob
  • varbinary(n)
  • binary(n)

1.3、日期和时间类型

• 日期类型：

  • year
  • time
  • timestamp
  • datetime
  • date

1.4、枚举和集合类型

• enum：一个字符串对象，可以取预定义的值。

• set：一个字符串对象，可以包含零个或多个预定义值。

1.5、son类型

• json：用于存储 json 格式的数据。

长度和精度: 数值类型可以设置最大长度（例如 varchar(255)），浮点数可以设置总位数和小数位数（例如 decimal(10,2)）

3、Null (是否允许 NULL)

• 描述: 列是否可以包含 null值，默认不写是允许空值
• 允许：null允许空值
• 不允许：not null不允许为空值
• 插入数据: 当字段允许 null时，你可以在插入数据时选择不提供该字段的值，MySQL 将自动将其设置为 null。
• 查询数据: 查询中可以使用 is null 或 is not null 来筛选包含 null 或不包含 null 的记录。
• 比较: 在 SQL 中，null 不等于任何值（包括另一个 null），比较时需要使用 is null 进行检查。

4、Key (键、索引)---基于某个列

自动生成索引减少了开发人员的负担，避免了在每次创建键时都需要手动创建索引的麻烦。

• 描述: 列是否有索引，以及索引的类型。---show index from employeetable;(显示所有索引)
• 注意：在 MySQL 中，外键必须引用一个存在的主键或唯一键（unique key）列。

外键与索引的关系

外键自动生成索引解释：

当你定义外键时，MySQL 通常会自动为外键列创建索引。如果你后来删除了外键约束，外键的索引不会被自动删除，除非你手动删除它。---创建的是普通的索引简称：mul

手动索引独立存在解释：

如果你手动创建了索引（如在你的例子中），那么删除外键不会影响这个索引，因为它是独立于外键约束存在的。

key键的类型：

主键（primary key）:

自动生成索引: 创建主键时，MySQL 会自动为主键列生成一个唯一索引，以确保主键的唯一性和非空性。

• 用于唯一标识表中的每一行，不能有重复值且不能为空。---要给一个外键约束名称
• #创建name表时写入（主键）：
  create table name(
  id int(9) not null primary key comment'序号',
  name varchar(8) comment'名字',
  sex enum('男','女')comment'性别', 
  school varchar(30) default'柳州大学' comment'学校'
  )comment ='信息登记表';

# 创建name4表时写入（复合主键）：
create table name4 (
    id int(9) not null comment '序号',
    name varchar(9) not null comment '姓名',
    sex enum('男', '女') comment '性别',
    school varchar(99) default '柳州大学',
    primary key (id, name) comment'定义复合主键' 
   ) comment = '信息登记表'; 

# 向name2表添加一个（主键）：
alter table name2 add  primary key(id ); 

# 向name3表添加（复合主键）
alter table name3 add primary key(id,name);

外键（foreign key）： 

自动生成索引: 当创建外键时，MySQL 不一定会自动生成索引。一般来说，如果外键列没有已有的索引，MySQL 会为其创建一个索引；如果已有索引，则会使用现有索引。

• 用于在两个表之间建立关系，确保引用完整性，子表参照父表数值要一致。

• 在 MySQL 中，外键必须引用一个存在的主键或唯一键（UNIQUE key）列。如果你尝试创建一个外键来引用一个没有主键或唯一键的列，MySQL 会返回错误。因此，在创建外键约束之前，你需要确保被引用的表中的列具有主键或唯一约束。

# 创建wai表是写入外键---（constraint约束）

• create table wai (
  id int(9) not null comment'序号',
  name varchar(9) not null comment'名字',
  sex enum('男','女') comment'性别',
  school varchar(9) default'柳州大学' comment'柳州大学',
  foreign key(id) references name(id) # 外键参照name表的id列
  )comment ='登记信息表';

 向wai2表添加外键

alter table wai2
add constraint fk_wai2
foreign key(id) references wai(id);

显示wai2表的创建完整结构

show create table wai2;

删除外键使用（修改表结构）alter table 删除外键（drop）---要先知道外键名称
alter table wai3  drop foreign key ky_wi3;

# 查看显示wai2的表结构，下面即是
show create table wai2;

创建外键表后删除外键的表显示示例（导出变大写）：---写外键加constraint的约束名叫啥索引就同名否者索引默认列名为索引名称

CREATE TABLE `wai2` (
  `id` int NOT NULL COMMENT '序号',
  `name` varchar(8) DEFAULT NULL COMMENT '名字',
  `sex` enum('男','女') DEFAULT NULL COMMENT '性别',
  `school` varchar(30) DEFAULT '柳州大学' COMMENT '学校',
  KEY `fk_wai2` (`id`) # 保留了一个普通索引，需要手动删除
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='信息登记表'

唯一键（Unique Key） 

自动生成索引: 创建唯一键时，MySQL 会为唯一键列生成一个索引，以确保列值的唯一性。

描述：每个值都是唯一的。与主键不同，唯一键可以包含空值。（复合唯一键写法-unique（id，name）

# 创建wai4表写入唯一键（unique key）
create table wai4(
id int(9) unique comment'序号', #定义一个唯一键
name varchar(9) comment'名字',
sex enum('男','女')comment'性别',
phone varchar(20) unique key comment'电话号码', #定义一个唯一键
state enum('在校','离校') comment'状态',
mark_for varchar(99) comment'去向'
) comment = '信息登记';

删除唯一键语句：alter table wai4 drop index id; 

复合键（unique key（id,name））

描述：基于多个列创建的主键、外键、索引---示例：primary key(id,name)

创建主键、外键、唯一键都可以自定义约束名称

索引（index）

手动创建: 普通索引需要手动创建。它不是由主键、唯一键或外键自动生成的。

定义普通索引时，直接使用 index 关键字指定索引名称，而不需要使用 constraint

描述：在某些数据库管理工具中，复合索引的显示可能仅基于第一个列。

注意：已存在的表添加索引的方法

第1：alter table name add index idx_id (id)

第2：create index index_id on name(id)

1、普通索引（index）

---基于一个列给他加上功能（提高一列的查询速度）

描述：最基本的索引类型，可以加速查询操作，但不强制唯一性。

# 创建wai5表定义一个普通单列索引和复合索引
create table wai5 (
id int(9) comment'序号',
name varchar(9) comment'名字',
sex enum('男','女') comment'性别',
age int(7) comment'年龄',
mark_for varchar(50) comment'去向',
school varchar(22) default'柳州大学' comment'学校',
index idx_id (id), #定义了一个普通的单列索引自定义名为idx_id
index idx_name_sex (name,sex) comment'复合索引' #定义了一个普通索引的复合索引
) comment ='信息登记';
 

#删除wai5表的索引idx_id
drop index idx_id on wai5

# 删除wai5表
drop table wai5

# 显示wai5的所有索引
show index from wai5

2、复合索引（Composite Index）

---基于一个列给他加上功能（一次提高多个列的查询速度）

描述：复合索引是一个索引，它由两个或多个列组成，用于加速对表中数据的查询。

主键复合索引（primary key）：constraint ky_id_name_sex primary key (id,name,sex)

外键复合索引(foreign key)：constraint ky_id_name_sex foreign key(id,name,sex)

唯一键复合索引(unique key)：constraint unique_id_name_sex unique key(is,name,sex)

普通索引复合索引(index)：index index_id_name_sex (id,name,sex)

全文索引复合索引（fulltext index）：fulltext index idx_fulltext (address,school)

3、全文索引（fulltext Index）

---基于一个列给他加上功能（提高文本内容查询速度）

描述：用于文本搜索，可以在大文本数据中进行高效的全文检索，适用于char，varchar和 text 类型的列。

# 向已存在wai5表使用alter table添加一个全文索引
alter table wai5 add fulltext index idx_mark_for (mark_for);

# 向已存在wai5表使用create index 添加一个全文索引
create fulltext index idx_school on wai5(school);

# 使用alter table 删除索引 idx_mark_for
alter table wai5 drop index idx_age;

# 使用drop index 删除索引
drop index idx_school on wai5

4、空间索引（spatial Index）

描述：用于地理信息系统（GIS）中的空间数据，支持对 GEOMETRY 类型的列进行快速检索。

空间索引主要用于以下数据类型：

• point：表示一个二维坐标点（X, Y）。
• linestring：表示由一系列点连接而成的线。
• polygon：表示一个多边形，由一个或多个线环定义。
• multipoint：表示多个点的集合。
• multilinestring：表示多个线的集合。
• multipolygon：表示多个多边形的集合。
• geometry：一个通用的空间数据类型，可以表示任意几何形状。

添加索引：alter table name add spatial index idx_spatial (zuobiao)

                  carete spatial index idx_spatial on name(zuobiao)

5、反向索引（Reverse Index）

描述：在 MySQL 中并没有直接的反向索引关键字，但反向索引的概念是将字段的值反转存储，以提高某些查询的性能，通常需要自定义实现。

5、default（默认值）

描述：如果没有为某个字段提供值，则使用数据库表中定义的默认值。---基于一个列给他加上功能（默认写的信息）

# 创建wai5表定义一个默认值名为柳州大学
create table wai5 (
id int(9) comment'序号',
name varchar(9) comment'名字',
sex enum('男','女') comment'性别',
age int(7) comment'年龄',
mark_for varchar(50) comment'去向',
school varchar(22) default'柳州大学' comment'学校',
index idx_id (id), #定义了一个普通的单列索引自定义名为idx_id
index idx_name_sex (name,sex) comment'复合索引' #定义了一个普通索引的复合索引
) comment ='信息登记';

6、extra（额外属性）

描述：不涉及列的自动生成或更新行为，因此它不会出现在额外属性的列表中。auto_increment列必须是主键或唯一键。一个表只允许一个自增列。---基于一个列给他加上功能（自增功能）

#wai5表id 列已经存在且希望将其设为自增
alter table wai5 modify column age int auto_increment;

# 向wai4表型添加一个新的唯一键自增列
alter table wai4 add column age int(30) auto_increment unique key

 

常见属性：

• auto_increment：用于生成唯一标识值的字段（通常用于主键）。
• on update CURRENT_TIMESTAMP：自动更新字段（通常用于记录更新时间）。