MySQL 常用的操作整理汇总

Focus on

于 2023-08-30 09:26:29 发布

阅读量76

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/hahahahazmr/article/details/132575511

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL

1、常用的MySQL数据库操作

1、建库

创建数据库

CREATE DATABASE `QSMAS`;

创建表

#数据表
CREATE TABLE `SYS_TABLE`(
`SYS_TABLE_ID` INT(10) NOT NULL AUTO_INCREMENT COMMENT '数据表ID' PRIMARY KEY,
`SYS_TABLE_NAME` NVARCHAR(50) NOT NULL UNIQUE COMMENT '数据表名',
`SYS_TABLE_DESC` VARCHAR(200)  COMMENT '数据表中文描述',
`UPDATE_TIME` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间' 
) ENGINE=INNODB DEFAULT CHARSET=utf8

2、Mysql引擎

1、InnoDB引擎

特点：

为MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

2、MyISAM存储引擎

特点：

被大文件系统和操作系统支持
当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，若下一个块被删除，就扩展到下一块自动完成
每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
BLOB和TEXT列可以被索引
NULL被允许在索引的列中，这个值占每个键的0~1个字节
所有数字键值以高字节优先被存储以允许一个更高的索引压缩
每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
可以把数据文件和索引文件放在不同目录
每个字符列可以有不同的字符集
有VARCHAR的表可以固定或动态记录长度
VARCHAR和CHAR列可以多达64KB

3、MEMORY存储引擎

特点：

MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
MEMORY存储引擎执行HASH和BTREE缩影
可以在一个MEMORY表中有非唯一键值
MEMORY表使用一个固定的记录长度格式
MEMORY不支持BLOB或TEXT列
MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

在这里插入图片描述

InnoDB： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MEMORY： 所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

4、InnoDB和MyISAM的区别

事务 InnoDB支持事务、回滚、事务安全和奔溃恢复。而MyISAM不支持，但查询的速度要比InnoDB更快
主键 InnoDB规定，如果没有设置主键，就自动的生成一个6字节的主键，而MyISAM允许没有任何索引和主键的存在，索引就是行的地址
外键 InnoDB支持外键，而MyISAM不支持
表锁 InnoDB支持行锁和表锁，而MyISAM只支持表锁
全文索引 InnoDB不支持全文索引，但是可以用插件来实现相应的功能，而MyISAM是本身就支持全本索引
行数 InnoDB获取行数时，需要扫全表。而MyISAM保存了当前表的总行数，直接读取即可。

所以，简单总结一下，MyISAM只适用于查询大于更新的场景，如果你的系统查询的情况占绝大多数（例如报表系统）就可以使用MyISAM来存储，除此之外，都建议使用InnoDB。

5、InnoDB底层原理

1、组成

InnoDB的内存架构主要分为三大块，缓冲池（Buffer Pool）、重做缓冲池（Redo Log Buffer）和额外内存池

缓冲池:

InnoDB为了做数据的持久化，会将数据存储到磁盘上。面对大量的请求时，CPU的处理速度和磁盘的IO速度之间差距太大，为了提高整体的效率， InnoDB引入了缓冲池。

缓冲池采用了LRU算法(最近最久未使用法) ==>可能会导致缓冲池污染

缓冲池污染:

当你在进行批量扫描甚至全表扫描时，可能会将缓冲池中的热点页全部替换出去。这样以来可能会导致MySQL的性能断崖式下降。

缓冲池中存储了 索引页、Undo页、插入缓冲、自适应哈希索引、InnoDB锁信息和数据字典

两次写：

与插入缓冲相辅相成

插入缓冲提高Mysql性能，两次写提高数据的可靠性

当机器宕机了，发生了写失效，有Redo Log来进行恢复。但是如果是在从缓冲池中将数据刷回磁盘的时候宕机了呢？

共享表空间中存储的有副本和主文件，所以实例恢复时可以找到副本进行覆盖

2、日志

MySQL层面&&InnoDB层面

Mysql日志:

错误日志
二进制文件：即Binlog，用来记录对数据库的所有更改
查询日志：记录所有来自客户端的语句
慢查询日志：记录所有响应时间超过阈值的SQL语句，这个阈值我们可以自己设置，参数为long_query_time，默认10秒，且默认关闭，需要手动打开

InnoDB日志：

Redo Log和Undo Log，

Redo Log 重做日志：用于记录事务操作的变化，且记录的是修改之后的值。不论是否失败

Undo Log ：记录的是记录的事务开始之前的一个版本，可用于事务失败之后发生的回滚。

BinLog:

记录了所有对数据库的修改, 记录日志有三种格式。分别是Statement、Row和MixedLevel。

Statement

记录所有会修改数据的SQL，其只会记录SQL，并不需要记录下这个SQL影响的所有行，减少了日志量，提高了性能

Row

只保存被修改的记录, Row会产生大量的日志。但是Row不用记录上下文信息了，只需要关注被改成啥样就行。

MixedLevel

Statement和Row混合使用的结果

3、主从复制（了解）

I/O thread（实际上就是一个主服务器的客户端进程）

Binary log 二进制日志

4、Mysql主从复制的过程

Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。
Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置。
Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从何处开始读取日志。
Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

5、基本操作语句 main

1、建表语句基本了解

1、编码格式

DEFAULT CHARSET=utf8

表示设置数据库的默认字符集为utf8

2、自增长

AUTO_INCREMENT 表示采用默认自增长，增长值为1

AUTO_INCREMENT=10 表示自动增长的起始值为10

2、键的定义

1、外键

FOREIGN KEY(SYS_MODEL_PARENTID) REFERENCES SYS_MODEL(SYS_MODEL_ID)

FOREIGN KEY(当前表中字段名) REFERENCES 被引用的表名(被引用表中的字段名)

2、复合主键

#联合主键
CONSTRAINT USER_ID_MOBILE PRIMARY KEY(USER_ID,USER_MOBILE)

#联合主键
CONSTRAINT 复合主键名（自己取名） PRIMARY KEY(主键字段名1,主键字段名2,...)

3、常用修改语句

1、修改自增长规则

1、修改自增长的粒度

ALTER TABLE SYS_ROLE AUTO_INCREMENT=10001;

ALTER TABLE 表名 AUTO_INCREMENT=自增长起始值;

2、给一个数据库表格添加一个自增长主键（原本没有）

ALTER TABLE `hr_user_base` ADD `user_id` BIGINT NOT NULL COMMENT '用户ID' PRIMARY KEY AUTO_INCREMENT; # 添加自增主键

3、给一个数据库表格的主键添加自增长属性（原本没有自增长）

ALTER TABLE `hr_user_base` MODIFY `user_id` BIGINT NOT NULL COMMENT '用户ID'  AUTO_INCREMENT;

2、修改某张表的某个字段

修改的其实是建表语句

ALTER TABLE `CO_COMPANY` CHANGE COMPANY_ADRRESS COMPANY_ADDRESS NVARCHAR(200) COMMENT '公司地址'

ALTER TABLE 表名 CHANGE 字段名 修改后字段具体描述

3、修改字段为Not Null

原本的SQL语句

alter table `SYS_USER_PRJ` modify PRJ_ID int COMMENT '项目ID';

执行转换为not null的SQL语句

alter table `SYS_USER_PRJ` modify PRJ_ID int not null;

alter table `表名` modify 字段名 数据类型（一般和原本的数据类型一致） not null;

4、修改字段从Not Null 到 Null

原本的SQL语句

alter table `SYS_USER_PRJ` modify PRJ_ID int not null COMMENT '项目ID';

执行转换为null的SQL语句

alter table `SYS_USER_PRJ` modify PRJ_ID int;

alter table `表名` modify 字段名 数据类型（一般和原本的数据类型一致）;

5、修改Enum取值范围

原本SQL语句

`STATUS` ENUM('D','N','A') DEFAULT 'A' COMMENT '状态',

执行转换SQL语句

Alter Table `CO_PRJ` MODIFY status ENUM('D','N','M','W','A') DEFAULT 'A' COMMENT '状态';

Alter Table `表名` MODIFY status ENUM(所有状态码) DEFAULT 默认状态码 COMMENT 字段名称（别名）;

other、常用的修改关键字

Rename、Change、Modify

rename:

修改的是表名称

alter table table_name rename [to] new table_name ;

change:

修改的是表中的字段名称

alter table table_name change old_name new_name datatype...; # --新字段需要完整定义

modify:

用于修改表中字段的数据长度，数据类型以及字段的约束条件的

alter table table_name modify field datatype...;

4、常用添加语句

1、添加外键

alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);

2、添加字段

alter table 表名 add column 字段名 字段类型 default 默认值;
# 或者不设置默认值
alter table 表名 add column 字段名 字段类型;

样例：

ALTER TABLE `num_test` ADD COLUMN `VALUE1` INT(10) COMMENT '值1';

5、常用删除语句

1、删除外键

ALTER TABLE `daily_task_info` DROP FOREIGN KEY `daily_task_info_ibfk_2`

ALTER TABLE `表名` DROP FOREIGN KEY `约束编号`

2、删除某张表的某一个字段

ALTER TABLE `daily_task_info` DROP COLUMN `INSP_ITEM_ID`

ALTER TABLE `表名` DROP COLUMN `字段名`

3、删除表中的所有数据

方式1：

truncate table 表名 # 清除所有数据，主键从1开始

方式2：

delete from 表名 # 删除所有数据，主键继续增长

Other、其他常用语句

1、枚举

`STATUS` ENUM('D','N','A') DEFAULT 'A' COMMENT '状态',

`STATUS` ENUM('枚举对象1','枚举对象2','枚举对象3') DEFAULT '默认枚举对象' COMMENT '字段名称',

2、TIMESTAMP 时间戳

`UPDATE_TIME` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间'

该方法用于生成当前系统时间

在java中定义updateTime时

import java.sql.Timestamp;
private Timestamp updateTime;

3、DateTime 日期（main）

`update_time` DATETIME DEFAULT NULL COMMENT '更新时间'

6、根据一个字段或者多个字段查询重复数据

1、根据一个字段查找重复数据

SELECT userName from User GROUP BY userName HAVING count(*) > 1;

2、根据多个字段查找重复数据

SELECT * FROM User GROUP BY userName,loginType HAVING count( * ) > 1;

参考链接

1、MySQL5.7下载、安装和配置

2、【mysql】MySQL 数据库根据一个或多个字段查询重复数据