04.数据库操作

chanchaw大约 13 分钟编程语言数据库mysql

恢复数据库

在服务器数据库上右键 转储SQL文件 -> 结构和数据
本地开发机器上创建新的空白数据库，注意字符编码和排序规则要保持一致
管理员身份打开 cmd 并切换到 mysql 的 bin 目录下，一般在 C:\Program Files\MySQL\MySQL Server 5.7\bin
通过命令 mysql -h localhost -u root -p 登录 mysql ，执行后要求输入登录密码
开始恢复数据

use 新创建的数据库名称
source d:/备份的sql文件.sql

通过 navicat 备份的文件只有100M，通过上面方法备份的文件可达到1G。。。

执行计划 explain

概述

explain 后面接具体的SQL语句，用来查看SQL语句的优化程度，主要用来检查SQL语句的执行效率，通过看速度的登记以及是否使用了索引

案例

EXPLAIN SELECT * FROM plan WHERE date>'2020-10-01 00:00:00';

会返回下列字段：
id // 选择标识符
select_type // 表示查询的类型
table // 输出结果集的表
partitions // 匹配的分区
type // 表示表的连接类型，
possible_keys // 表示查询时，可能使用的索引
key // 表示实际使用的索引
key_len // 索引字段的长度
ref // 列与索引的比较
rows // 扫描出的行数(估算的行数)
filtered // 按表条件过滤的行百分比
Extra // 执行情况的描述和说明

其中 type 列比较关键，会出现下面几种情况

ALL：全表扫描，应当避免该类型
index：索引全局扫描，index与ALL区别为index类型只遍历索引树
range：检索索引一定范围的行
ref：非唯一性索引扫描，返回匹配某个单独值的所有行
eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描
const：表示通过一次索引就找到了结果，常出现于primary key或unique索引
system：system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，是最高的登记

key 表示实际使用到的索引的列， rows 表示预估的扫描表的行数 extra 分为下面几种情况

Using where // 表示不用读取表中所有信息，仅通过索引就可以获取所需数据，即使用列覆盖索引
Using temporary // 表示需要使用临时表来存储结果集，常见于排序和分组查询，如：group by ; order by
Using filesort // 表示无法利用索引完成的排序
Using join buffer // 表示使用了连接缓存，如果出现了这个值，建议根据查询的具体情况可能需要添加索引来改进能。
Impossible where // 表示where语句会一直false，导致没有符合条件的行（通过收集统计信息不可能存在结果）
Select tables optimized away // 这个值意味着sql优化到不能在优化了
No tables used // Query语句中使用from dual 或不含任何from子句

shell中操作

连接数据库

# 连接其他服务器
mysql -hlocalhost -uroot -pchanchaw

# 连接本机
mysql -uroot -p你的密码

DDL

批量设置字符集

# 下面的 femis 是数据库名称，设置该数据库下的所有表的字符集
SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'femis'

新增字段

alter table dye_grey_main add charge_person VARCHAR(100) default '' null comment '经手人';

检测指定字段是否存在

DELIMITER $$
set @dbName='showa2023';
set @fieldName = 'create_user';
select tables.TABLE_NAME,tables.field_create_user,
case when fields.COLUMN_NAME is null then '×' else '' end as is_exists
from (
    SELECT a.*,b.field_create_user FROM INFORMATION_SCHEMA.TABLES a
    CROSS JOIN (select @fieldName as field_create_user) b on 1=1
    WHERE TABLE_SCHEMA=@dbName
) tables
left join (
    select * from information_schema.columns
    where table_schema=@dbName
) fields
on tables.TABLE_NAME = fields.TABLE_NAME
and tables.field_create_user = fields.COLUMN_NAME
;
$$

获取指定表所有字段

select * from information_schema.columns
where table_schema='showa2023'
and table_name='contact'
ORDER BY ORDINAL_POSITION
;

获取所有表名

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='showa2023';

创建数据库

create schema [数据库名称] default character set utf8 collate utf8_general_ci;--创建数据库
采用create schema和create database创建数据库的效果一样。

为数据库创建用户

create user 'root'@'%' identified by 'chanchaw';--创建用户
密码8位以上，包括：大写字母、小写字母、数字、特殊字符
%：匹配所有主机，该地方还可以设置成‘localhost’，代表只能本地访问，例如root账户默认为‘localhost‘

给用户授权

grant select,insert,update,delete,create on femis.* to 'root';
--用户授权数据库
*代表整个数据库

取消用户权限

revoke all on *.* from tester;--取消用户所有数据库（表）的所有权限

删除用户

delete from mysql.user where user='tester';--删除用户

删除数据库

drop database [schema名称|数据库名称];--删除数据库

创建表

create table chargeunit(
   iid INT NOT NULL AUTO_INCREMENT,# 整型，主键，自增
   caption VARCHAR(100),#标题，文本
   fieldname VARCHAR(50),#计量单位字段名称
   defaultval INT,#默认值
   # 每次行数据被修改下面字段的时间会被更新
   update_time datetime null on update CURRENT_TIMESTAMP comment '最后一次修改时间',
   PRIMARY KEY ( iid )
)COMMENT='计量单位表';

显示表信息：SHOW TABLE STATUS from femis;#左边的femis是数据库名称显示字段信息：SHOW FULL COLUMNS FROM orderdetail # 列出字段及其详情显示索引信息：SHOW INDEX FROM t_rsdir_app //列出表索引为字段创建注释：alter table chargeunit modify column caption int comment '显示到UI的文本'; 显示字段信息：SHOW FULL COLUMNS FROM chargeunit;#列出字段及详情

获取创建表的脚本

后面的 client 是表名称

show create table client;

注释相关

创建表时写注释

create table test1
(
field_name int comment '字段的注释'
)comment='表的注释';

修改表注释

alter table test1 comment '修改后的表的注释';

修改字段注释

alter table test1 modify column field_name int comment '修改后的字段注释';

修改表字符集和排序规则

第一种方法

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET  utf8 COLLATE utf8_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'databaseName'

第二种方法

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_general_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'databaseName'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_general_ci'
);

线上库增加字段

隐患 1/4

给线上表

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='用户表';

增加字段

ALTER TABLE `user` ADD `age` int NOT NULL DEFAULT '0' COMMENT '年龄';

有可能导致系统宕机。MYSQL在给表增加字段时会锁整个表，并且加的是写锁，同时阻塞读（用户的查询操作）和写（用户的写操作），导致对应功能模块停机。

原因 2/4

用户执行增删改查（insert , delete , update , select - 统称为 DML 操作）操作时，MYSQL会自动添加 MDL（medata lock）写锁，即锁定表的定义，不可修改表结构。当执行 DDL（create , drop , alter , rename , truncate）操作时 MYSQL 会自动添加 MDL 读锁，保证读操作同步进行。锁的互斥关系：

读锁和读锁不互斥 - 可并行进行
读锁和写锁互斥
写锁和写锁互斥

优雅的添加字段 3/4

MYSQL5.6 版本新增了 Online DDL 特性，可以使执行 DDL 操作的同时进行 DML 操作，所以从 5.6 版本开始可以这样新增字段

ALTER TABLE `user` ADD `age` int NOT NULL DEFAULT '0' COMMENT '年龄',
ALGORITHM=Inplace,
LOCK=NONE;

上面代码的 ALGORITHM 有下面几个选项：

Copy：拷贝方式，MySQL5.6 之前 DDL 的执行方式，过程就是先创建新表，修改新表结构，把旧表数据复制到新表，删除旧表，重命名新表。执行过程非常耗时，产生大量的磁盘IO和占用CPU，还有使Buffer poll失效，而且需要锁住旧表，性能较差，现在基本很少使用。 Inplace：原地修改，MySQL5.6开始引入的，优点是不会在Server层发生表数据拷贝，过程中允许并发执行DML操作。过程就是先添加MDL写锁，执行初始化操作，然后降级为MDL读锁，执行DDL操作（比较耗时，允许并发执行DML操作），升级为MDL写锁，完成DDL操作。 Instant：快速修改，MySQL8.0开始引入的，可以实现快速给大表添加字段。

性能由低到高：copy -> inplace -> instant。上面代码的 LOCK 可以指定过程中是否加锁：

NONE：不加锁，允许DML操作。 SHARED：加读锁，允许读操作，禁止DML操作。 DEFAULT：默认锁模式，在满足DDL操作前提下，默认锁模式会允许尽可能多的读操作和DML操作。 EXCLUSIVE：加写锁，禁止读操作和DML操作。

Online DDL 支持的 DDL 操作 4/4

操作	Instant	Inplace	Rebuilds Table	允许并发DML	仅修改元数据
添加列	Yes	Yes	No	Yes	No
删除列	No	Yes	Yes	Yes	No
重命名列	No	Yes	No	Yes	Yes
更改列顺序	No	Yes	Yes	Yes	No
设置列默认值	Yes	Yes	No	Yes	Yes
更改列数据类型	No	No	Yes	No	No
设置VARCHAR列大小	No	Yes	No	Yes	Yes
删除列默认值	Yes	Yes	No	Yes	Yes
更改自动增量值	No	Yes	No	Yes	No
设置列为null	No	Yes	Yes	Yes	No
设置列not null	No	Yes	Yes	Yes	No

DML

删除特殊符号

# 删除回车符、换行符
update `LOL` set hero_title = REPLACE(hero_title, char(10), '');
update `LOL` set hero_title = REPLACE(hero_title, char(13), '');

查看表详细信息

desc 表名相当于：explain cfgbillcode 相当于：show columns from cfgbillcode

显示错误信息

show warnings

表相关

获取建表语句：show create table cfgbillcode; 查看表状态：show table status from femis where name='cfgbillcode'; 修改表引擎：alter table table_name engine=innodb;

引擎相关

显示已安装的数据库支持的所有引擎：show engines; 查看当前数据库使用的引擎：show variables like '%storage_engine%'; 查看指定表的引擎：show create table 表名;

DQL

游标案例

下面案例在 showa 中同名存储过程

create definer = root@`%` procedure usp_rebuildMaint()
BEGIN
    declare found bool default true;  #循环控制变量

    DECLARE vMainId INT;
    DECLARE vBillCode VARCHAR(100);
    DECLARE vBillDate DATETIME;
    DECLARE vDevSid VARCHAR(100);
    DECLARE vRemark VARCHAR(100);
    DECLARE vState INT;
    DECLARE vCreateTime DATETIME;
    DECLARE vCreateUser VARCHAR(100);
    DECLARE vLastUpdate DATETIME;
    DECLARE vLastUser VARCHAR(100);

    DECLARE newId INT;

    # 1月15日 重建设备维保任务
    DECLARE maintMain CURSOR FOR
    select id,concat('DM24',substring(bill_code,5,7)) as bill_code,
    date_add(bill_date,INTERVAL 1 YEAR) as bill_date,
    dev_sid, remark, state, create_time, create_user, last_update, last_user
    from maint_main
    where year(bill_date)='2023'
    ORDER BY id asc;

    declare continue handler for not found set found=false;

    OPEN maintMain;

    FETCH  maintMain INTO vMainId,vBillCode,vBillDate,vDevSid, vRemark, vState, vCreateTime, vCreateUser, vLastUpdate, vLastUser;
    while found do
        # 写入主表
        insert into maint_main
        (bill_code,bill_date,dev_sid, remark, state, create_time, create_user, last_update, last_user)
        select vBillCode,vBillDate,vDevSid, vRemark, vState, vCreateTime, vCreateUser, vLastUpdate, vLastUser;

        # 获取主键
        SELECT LAST_INSERT_ID() into newId;

        # 写入明细表
        insert into maint_detail
        (parent_id, area, way, qualified, rate, type,
        plan_date,plan_employee,remark, state, seq, maint_content, current_state, next_state, create_time, create_user, last_update, last_user)
        select newId, area, way, qualified, rate, type,
        date_add(plan_date,INTERVAL 1 YEAR) as plan_date,
        plan_employee,remark, state, seq, maint_content, current_state, next_state, create_time, create_user, last_update, last_user
        from maint_detail where parent_id=vMainId;

        FETCH  maintMain INTO vMainId,vBillCode,vBillDate,vDevSid, vRemark, vState, vCreateTime, vCreateUser, vLastUpdate, vLastUser;
    end while;

    CLOSE maintMain;
END;

exists用法

SELECT c.CustomerId,CompanyName FROM Customers c
WHERE EXISTS(
SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID)

查看表容量

select
table_schema as 'db name',# 数据库名称
table_name AS 'table name', # 表名称
sum(table_rows) as 'row count',# 行数
sum(truncate(data_length/1024/1024, 2)) as 'data volume MB',# 表数据容量大小，单位MB
sum(truncate(index_length/1024/1024, 2)) as 'index volume MB'# 表索引容量大小，单位MB
from information_schema.tables
WHERE 1=1
AND table_schema='weixin_cloud'
group by table_schema,table_name
order by table_schema,table_name,sum(data_length) desc, sum(index_length) desc;

查询其他数据库

概述 1/4

介绍两种情况，查询同服务器的其他数据库表，查询其他服务器的表

同服务器其他数据库表 2/4

首先通过命令 show ENGINS 确认项目 FEDERATED 配置是 YES，如下图

系统默认是 no ，windows 系统下找到 my.ini 填写该项目（不用 federated = ture，只要填写：federated 即可）之后要重启 MYSQL服务。linux 系统找到 my.cnf 文件填写同样项目查询同服务器其他数据库表只要在表前面添加数据库名称即可

其他服务器数据库 3/4

在本地服务器数据库中创建和远程服务器表同名的表，创建表的末尾注意填写下面 ENGIN =FEDERATED...，如下图

最后的红色字体 root 是登录账号，后面乱码一样的字符是密码，142.44.107 是远程服务器的IP地址，orderdb 是数据库名称 orders 是表名。创建的表相当于远程服务器表的映射，远程服务器上该表数据变更后本地也一起变更，同样修改该表的数据远程服务器上数据也被修改。

注意点 4/4

查看执行计划

# 常规方式显示结果
EXPLAIN SELECT * FROM contactcompany;

# 使用JSON的格式显示结果
EXPLAIN FORMAT=JSON SELECT * FROM contactcompany;

查看表信息

SHOW TABLE status FROM pademisaccount;

数据文件存储位置

SHOW VARIABLES like 'datadir';

查看所有可用存储引擎

SHOW ENGINES ;

INNODB缓冲池参数

SHOW STATUS LIKE '%innodb_buffer_pool%';

查看表引擎

show table status from pademisaccount where name='billordermain';

获取所有存储过程

# 查询获取指定数据库的所有存储过程
# 按照创建时间倒排序显示
SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA='pademisaccount'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY CREATED DESC ,LAST_ALTERED DESC;

获取所有表名

SELECT table_name FROM information_schema.tables 
WHERE table_schema='femis' AND table_type='base table';

获取表信息

SELECT table_schema,table_name,create_time,update_time,table_collation,table_comment,engine
FROM information_schema.tables
WHERE table_schema='fluxgauge' AND table_type='base table';

获取指定表所有字段

SELECT * FROM information_schema.columns
WHERE table_schema='femis' AND table_name='orderdetail';


SELECT `COLUMN_NAME`, COLUMN_COMMENT,COLUMN_TYPE,COLUMN_DEFAULT, IS_NULLABLE, 
		CHARACTER_SET_NAME,COLUMN_KEY,
       GENERATION_EXPRESSION,CHARACTER_OCTET_LENGTH,
       ORDINAL_POSITION,DATA_TYPE
FROM information_schema.columns
WHERE table_schema='weixin_cloud' AND table_name='bp_pushlist_main'
ORDER BY ORDINAL_POSITION;

获取所有视图

SELECT * FROM information_schema.views;

获取所有数据库

select * from information_schema.`SCHEMATA`;

获取所有数据库的所有表

select * from information_schema.`TABLES`;

获取所有字段

SELECT * FROM information_schema.`COLUMNS`;

表及字段字符集

# 查看表字符集
show table status from femis like 'raw_main';

# 查看表中所有字段的字符集
show full columns from raw_main;

日志

慢查询

慢查询默认不开启，可以使用 show variables like '%slow_query_log%'; 查看开启的状态 windows系统中慢查询日志的存放路径是：c:\ProgramData\MySQL\MySQL Server 5.7\Data

注意上图中的路径是隐藏的。可以通过控制台 set global slow_query_log='ON'; 开启该日志，不过是临时的，重启MYSQL服务器后失效如果要永久打开配置文件 C:\ProgramData\MySQL\MySQL Server 5.7 下的文件 my.ini 设置 slow-query-log=1 设置慢查询时间长度的阈值 set global long_query_time=1 ，当然可以通过 show variables like '%long_query_time%'; 先查看该阈值是多少。

# 定义为慢SQL的时间
SHOW VARIABLES LIKE 'long_query_time'; # 计量单位：秒

# 慢查询日志是否开启
SHOW VARIABLES  LIKE 'slow_query%';

# 查看慢查询配置
SHOW VARIABLES LIKE '%slow%';

#打开慢查询日志
set global slow_query_log=on;

#设置记录查询超过多长时间的sql
set global long_query_time=1;

#设置mysql慢查询日志路径，此路径需要有写权限
set global slow_query_log_file='/tmp/slow_query.log';

#设置没有使用索引的sql记录下来
set global log_queries_not_using_indexes=ON;

错误日志

查看错误日志

show VARIABLES LIKE '%log_err%';

通用查询日志

show VARIABLES LIKE '%general%';

二进制日志

# 记录了对MYSQL进行更改的操作，不记录查询操作，用于数据库恢复，主从复制
# 下面命令是查看是否开启二进制日志
show VARIABLES LIKE '%log_bin%';
# 下面的查看二进制日志的参数
show VARIABLES LIKE '%binlog%';

慢查询日志

# 慢查询日志默认超过10秒认为是慢查询
# 查看是否开启
show VARIABLES LIKE '%slow_query%';
# 查看慢查询的阈值
SHOW VARIABLES LIKE '%long_query_time%';
# 设置慢查询阈值为5秒钟
set long_query_time=5;

04.数据库操作

# 恢复数据库

# 执行计划 explain

# 概述

# 案例

# shell中操作

# 连接数据库

# DDL

# 批量设置字符集

# 新增字段

# 检测指定字段是否存在

# 获取指定表所有字段

# 获取所有表名

# 创建数据库

# 为数据库创建用户

# 给用户授权

# 取消用户权限

# 删除用户

# 删除数据库

# 创建表

# 获取创建表的脚本

# 注释相关

# 创建表时写注释

# 修改表注释

# 修改字段注释

# 修改表字符集和排序规则

# 线上库增加字段

隐患 1/4

原因 2/4

优雅的添加字段 3/4

Online DDL 支持的 DDL 操作 4/4

# DML

# 删除特殊符号

# 查看表详细信息

# 显示错误信息

# 表相关

# 引擎相关

# DQL

# 游标案例

# exists用法

# 查看表容量

# 查询其他数据库

#

概述 1/4

#

同服务器其他数据库表 2/4

#

其他服务器数据库 3/4

#

注意点 4/4

# 查看执行计划

# 查看表信息

# 数据文件存储位置

# 查看所有可用存储引擎

# INNODB缓冲池参数

# 查看表引擎

# 获取所有存储过程

# 获取所有表名

# 获取表信息

# 获取指定表所有字段

# 获取所有视图

# 获取所有数据库

# 获取所有数据库的所有表

# 获取所有字段

# 表及字段字符集

# 日志

# 慢查询

# 错误日志

恢复数据库

执行计划 explain

概述

案例

shell中操作

连接数据库

DDL

批量设置字符集

新增字段

检测指定字段是否存在

获取指定表所有字段

获取所有表名

创建数据库

为数据库创建用户

给用户授权

取消用户权限

删除用户

删除数据库

创建表

获取创建表的脚本

注释相关

创建表时写注释

修改表注释

修改字段注释

修改表字符集和排序规则

线上库增加字段

DML

删除特殊符号

查看表详细信息

显示错误信息

表相关

引擎相关

DQL

游标案例

exists用法

查看表容量

查询其他数据库

查看执行计划

查看表信息

数据文件存储位置

查看所有可用存储引擎

INNODB缓冲池参数

查看表引擎

获取所有存储过程

获取所有表名

获取表信息

获取指定表所有字段

获取所有视图

获取所有数据库

获取所有数据库的所有表

获取所有字段

表及字段字符集

日志

慢查询

错误日志