MySQL数据库管理与索引优化全攻略
一、表管理
1.建库
语法：
create database if not exists 数据库名;命名规则：
仅可使用数字、字母、下划线、不能纯数字；区分字母大小写；具有唯一性；不可使用MySQL命令或特殊字符。
相关命令：
show databases; #查看库
use 库名; #使用库
select database(); #查看所在的库
drop database if exists 库名; #删除库2.建表
建表语法：
create table 库名.表名(表头名1 数据类型,表头名2 数据类型...);相关命令：
show tables; #显示已有的表
desc 库名.表名; #查看表头
select * from 库名.表名; #查看表记录
drop table 库名.表名; #删除表3.修改表
语法格式：
alter table 库.表 操作命令;修改表名
alter table 库名.表名 rename 库名.新表名;删除表头
alter table 库名.表名 drop 表头名;添加表头
alter table 库名.表名 add 表头名 数据类型; #默认添加在末尾
alter table 库名.表名 add 表头名 数据类型 first; #添加在首位
alter table 库名.表名 add 表头名 数据类型 after 表头名; #添加在指定表头名下方修改表头数据类型
alter table 库名.表名 modify 表头名 新数据类型;修改表头名
alter table 库名.表名 change 表头名 新表头名 数据类型;4.复制表
语法格式：
create table 库名.表名 select 列名 from 库名.表名 [where 条件]; #复制表结构及数据
create table 库名.表名 like 库名.表名; #仅复制表结构二、数据类型
MySQL提供了丰富的数据类型，主要分为三大类：数值类型、枚举类型、日期类型和字符串类型。
1.数值类型
整数类型：tinyint、smallint、mediumint、int、bigint。
示例：
create table users (id int ,age tinyint,views bigint
);浮点数类型：float、double、decimal。
示例：
create table products(price decimal(6,2),weight float,ratio double
);2.枚举类型
enum类型，字段值仅能在范围内选1个值；set类型，字段值能在范围内选取1个或多个值。
示例：
create table 库名.表名(字段名 enum(选项1,选项2,选项3...),字段名 set(选项1,选项2,选项3...)
);3.日期时间类型
date仅日期、time仅时间、datetime日期时间、timestamp时间戳、year年份。
示例：
create table events (evnet_date date,start_time time,created_at timestamp,full_datetime datetime
);4.字符串类型
char定长字符串、varchar变长字符串、tinytext短文本、text长文本内容、mediumtext中等长度文本、longtext超长文本。
示例：
create table articles (title varchar(50),content text,image mediumblob,hash char(32)
);三、数据批量处理
1.数据导入
语法格式：
load data infile "/目录名/文件名" info table 库名.表名 fields terminated by "分隔符" lines terminated by "\n";2.数据导出
select命令 into outfile "/目录名/文件名" fields terminated by "分隔符" lines terminated by "\n";四、表头约束
表头约束是数据库管理系统中的关键机制，用于在列级别确保数据完整性和一致性，通过定义数据必须满足的条件和规则来实现。
1.约束分类
not null：非空约束，确保该字段值不能为空。
default：默认值设置，当未指定该字段值时自动填充默认值。
unique：唯一约束，保证该字段值在整个表中具有唯一性，允许为空值。
primary key：主键约束，兼有唯一性和非空性，作为表的唯一标识。
foreign key：外键约束，用于建立表间关联关系，确保从表字段值必须引用主表对应字段值。
2.基本约束
not null：不允许赋空值。
create table test.stu(name char(10) not null,class char(7) null
);default：不给表头赋值时，使用默认赋值。
create table test.stu(name char(10) not null,class char(10) null default "sql"
);unique：表头的值不允许重复
create table test.stu(name char(10) not null,class char(10) null default "sql",phone char(11),unique(phone)
); 3.高级约束
（1）主键（primary key）
主键字段必须满足非空且唯一的要求。每个表只能定义一个主键，但可以由多个字段组成复合主键。复合主键需要同时创建和删除，在系统中会显示为PRI标志。通常建议将主键字段设置为自增属性，并选择能够唯一标识表中记录的字段作为主键。
主键创建语法格式：
#格式一
create table 库.表(
表头名 数据类型 primary key ,
表头名 数据类型 ,
..... );
#格式二
create table 库.表(
表头名 数据类型 ,
.....
primary key（字段名）
);删除主键语法格式：
alter table 库名.表名 drop primary key;添加主键语法格式：
alter table 库名.表名 add primary key(表头名);复合主键语法格式：
create table 库.表(
表头名 数据类型 ,
.....
primary key（字段名列表）
);与auto_increment连用
create table 库.表(
表头名 数据类型 primary key auto_increment,
表头名 数据类型 ,
..... );（2）外键（foreign key）
表存储引擎必须使用InnoDB，各列的数据类型需保持一致，被参照的列必须是索引类型之一（如主键primary key）。
创建外键语法格式：
create table 库.表(
表头列表 ,
foreign key(表头名) #指定外键
references 库.表(表头名) #指定参考的表头名
on update cascade #同步更新
on delete cascade #同步删除
)engine=innodb;删除外键语法格式：
alter table 库名.表名 drop FOREIGN KEY 外键名;添加外键语法格式：
alter table db1.gz add foreign key(字段名) references 库名.表名(字段名) on update cascade on delete cascade ;五、MySQL索引
MySQL 索引是一种用于提升查询效率的关键数据库机制，其作用类似于书籍的目录，能够帮助数据库快速检索数据。在单个数据表中可以创建多个索引。索引可以应用于任何数据类型的字段，允许字段值重复或为NULL值。通常建议在WHERE子句查询条件涉及的字段上创建索引，在MySQL中这些索引会被标记为MUL。
1.索引分类
普通索引：适用于任何数据类型的索引，无附加条件限制，仅用于提升查询效率，允许重复值和NULL值。
唯一索引：通过unique参数创建的索引，要求索引值必须唯一，但允许存在NULL值。
全文索引：专为文本搜索设计的索引类型，支持自然语言搜索和布尔搜索功能。
单列索引：基于单个字段构建的索引结构。
多列索引：在表的多个列上创建的复合索引，查询时可通过这些字段组合来提高检索效率。
2.索引的优点
优化查询性能，降低数据扫描量；提升连接效率，实现高效 JOIN 操作；确保数据完整性，维护唯一性约束。
3.索引缺点
- 需要额外存储空间，增加了存储开销
- DML操作成本上升，导致写入性能下降
- 需定期维护，维护成本随之增加
- 索引选择难度较大
4.普通索引
建表时创建索引：
create table 库名.表名(
字段列表,
index(表头名),
index(表头名),
);添加索引：
create index 索引名 on 库名.表名(字段名);删除索引：
drop index 索引名 on 库名.表名;验证查询是否使用索引：
explain select 查询语句;