Add by 2024-12-17
Mysql中时间判断处理
CONVERT(SUBSTRING(start_time,1,10),datetime) -- 当前月的年月日 DATE_SUB(CONVERT(SUBSTRING(start_time,1,10),datetime), INTERVAL 1 MONTH) -- 当前时间 上个月 的年月日
注：DATE_ADD 是日期增加
End by 2024-12-17
DELETE、Truncate、Drop差异
DELETE：适合做删除数据表中的某些记录。如果清空表(DELETE FROM 表名)，建议使用Truncate，因为delete无法清除自增字段记录，事务可回滚。
Truncate：清空数据表数据，但是表结构不变动，事务不可回滚。
Drop：删除数据表,包括结构、约束、索引等，事务不可回滚。
关于EXISTS和IN的差异
EXISTS:优先查询Exists左边的内容，如果左边内容相对较少，建议使用Exists。
IN：优先查询in右边的内容，如果右边的内容相对较少，建议使用IN。
关于Having和Where
SELECT * FROM lgzx_baseinfos WHERE auto_id=1; SELECT key_name FROM lgzx_baseinfos GROUP BY key_name HAVING COUNT(*)>2; --以key_name分组，并且查找超过两条key_name相同的数据
分组拼接字符串函数：group_concat
select qcode,gcode,group_concat(rid order by tversion desc separator ',') as a from qresource group by qcode,gcode
# 注：以qcode,gcode进行分组查询qcode,gcode和rid。rid通过tversion降序排序并使用','符合拼接成字符串，以a字段显示。
qcode gcode a
A 33 dt-948014bfb976f,dt-389263a2faef8d038
C 62 dt-ab19ccb656d56,dt-7800538cd8eb4ef0a,...
ON DUPLICATE KEY UPDATE
执行规则：先执行INSERT INTO语句，如果插入执行成功，结束；如果发生唯一键/主键约束冲突，则执行更新操作。
表结构：
DROP TABLE IF EXISTS `studyWord`;
CREATE TABLE `studyWord` (
`aid` int(11) NOT NULL AUTO_INCREMENT,
`ecode` varchar(10) NOT NULL,
`wid` varchar(64) NULL DEFAULT NULL,
`word` varchar(100) NULL DEFAULT NULL,
`ption` varchar(29) NOT NULL DEFAULT '',
`dstr` varchar(29) NOT NULL DEFAULT '',
PRIMARY KEY (`aid`) USING BTREE,
UNIQUE INDEX `ecode`(`ecode`, `wid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
插入内容：
INSERT INTO studyWord(ecode,wid,word,ption,dstr)
VALUE('A','dss','测试重复插入','C','2024-07-24 10:00:00')
ON DUPLICATE KEY UPDATE word='测试重复插入112',ption='CC',dstr='2024-06-24 10:00:00';
批量查询数据表
-- 删除数据库名为lgdb_fscmodule'数据库的以_s_176结尾的所以表 -- SELECT CONCAT('drop table ',t.`TABLE_NAME`,'; ') dropsql FROM information_schema.`TABLES` t WHERE t.`TABLE_NAME` LIKE '%\_s\_%' AND t.table_schema='表名';
关于数据库日志处理
-- 查看日志是否开启 SHOW VARIABLES LIKE 'general_log'; -- 开启日志功能 SET GLOBAL general_log='ON'; -- 关闭日志功能 SET GLOBAL general_log='OFF'; -- 看看日志文件保存位置 SHOW VARIABLES LIKE 'general_log_file'; -- 设置日志文件保存位置 C:\ProgramData\MySQL\MySQL Server 5.5\Data\DESKTOP-NUR8UC7.log SET GLOBAL general_log_file='C:\\tmp.log'; -- 看看日志输出类型 TABLE 或 FILE SHOW VARIABLES LIKE 'log_output'; -- 设置输出类型为 TABLE SET GLOBAL log_output='TABLE'; -- 设置输出类型为FILE SET GLOBAL log_output='FILE';
-- 查看数据库 这个命令会以数据库为单位，计算出每个数据库的大小，并按照大小从大到小排列。
SELECT table_schema "Database", sum(data_length + index_length)/1024/1024 "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
-- 这个命令会显示数据库中所有表格的详细信息，包括每个表格的名称、大小、行数、创建时间等。其中，Size字段显示的是表格所占用的存储空间大小，单位为字节。
SHOW TABLE STATUS FROM lgdb_manualquestionsbank;
-- 适用于查看数据库执行命令(快速操作) -- log_output=’FILE’ 表示将日志存入文件,默认值是FILE -- log_output=’TABLE’表示将日志存入数据库,这样日志信息就会被写入到 mysql.slow_log 表中. -- 将日志写入表中 SET GLOBAL log_output = 'TABLE'; -- 开启日志 SET GLOBAL general_log = 'ON'; -- 查看数据库执行命令 SELECT * FROM mysql.general_log ORDER BY event_time DESC;
慢查询
查看是否已经开始日志：show variables like '%slow_query_log';
查询慢查询个数：SHOW STATUS LIKE 'Slow_queries';
slow_query_log：是MySQL用来记录执行时间超过指定阈值的查询的日志文件。通过启用这个日志，你可以找到并优化那些执行效率低下的查询，从而提高数据库的整体性能。
全局开启设置：SET GLOBAL slow_query_log = 'ON';
会话开启级别 (只对当前会话有效)：SET slow_query_log = 'ON';
配置文件(在MySQL的配置文件（通常是my.cnf或my.ini）中设置)：
[mysqld]
slow_query_log = 1 # 长期关闭将其设置为"OFF",如:show_query_log=OFF，或者直接注释掉这两行(因为mysql默认是关闭的)
slow_query_log_file = /path/to/your/logfile.log # 保存文件路径
long_query_time：一个阈值，用于定义哪些查询应该被记录在slow_query_log中。默认情况下，这个值是10秒，意味着所有执行时间超过10秒的查询都会被记录。
全局设置：SET GLOBAL long_query_time = 2; -- 例如，设置为2秒
会话级别 (只对当前会话有效)：SET long_query_time = 2;
配置文件(在MySQL的配置文件（通常是my.cnf或my.ini）中设置)：
[mysqld]
long_query_time = 2
注：慢查询日志可以通过mysqldumpslow（注：mysql5.7中，使用的是mysqldump而不是mysqldumpshow）工具查看，具体可以参照博客：MySQL高级篇——性能分析工具_mysqldumpslow-CSDN博客 中的"3.5 慢查询日志分析工具：mysqldumpslow"，下面查看计划表笔记同博客地址。
EXPLAIN 查看执行计划表
EXPLAIN SELECT * FROM lgzx_identity_info WHERE auto_id=1;
| id | 每个SELECT子句或者join操作都会被分配一个唯一的编号，编号越小优先级越高，id相同的语句可以被认为是一组。id为NULL表示独立的子查询，子查询优先级都比主查询高。 |
| select_type | 查询的类型。主查询(primary)、普通查询(simple)、联合查询、子查询(subquery)、derived(from表临时子查询)、union(union后查询)、union result() |
| table | 表名。显示当前这行的数据是哪个表的。 |
| partitions | 匹配的分区信息。如果表未分区则为NULL。 |
| type | 访问类型，根据索引、全表扫描等方法来执行查询的优化策略。all（全表扫描），ref（命中非唯一索引），index(没命中索引，扫描索引树再回表)、const（命中主键/唯一索引）、range(范围索引查询)、index_merge(使用多个索引)、 system(一行记录时,快速查询)。 |
| possible_keys |
可能用到的索引。列出MySQL能够使用哪些索引来查询。 如果该列只有一个possible_keys，通常意味着这个查询是高效的。 如果这个列有多个possible_keys，并且MySQL只使用了其中一个，则需要考虑是否需要在该列上增加一个联合索引。 |
| key | 实际上使用的索引。如果没有明确的指定KEY，MySQL会根据查询条件自动选择最优的索引。 |
| key_len | 实际使用到索引的字节数长度。越短表示越快，一般表示索引字段越小越好。 |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息。常量等值查询const, 表达式/函数使用到时func,关联查询显示关联字段名 |
| rows | 预估的需要读取的记录条数。数值越小越好，表示结果集越小，查询越高效。 |
| filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比。这个值越小越好，说明可通过索引直接返回数据。 |
| Extra | 额外信息。看有没有走索引，还是全表扫描了。一般搭配type字段看。Using index(使用到覆盖索引)、Using where(未完全命中索引)、Using temporary(临时表存储结果集.排序/分组会使用)、Using filesort(排序操作未用索引)、Using join buffer(连接条件未用索引)、Impossible where(where约束语句可能有问题导致没有结果集) |
创建数据库
-- 删除数据库
DROP DATABASE IF EXISTS 数据库名;
-- 创建数据库
-- CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin';
CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 'utf8' COLLATE 'utf8_bin';
-- 使用数据库
use 数据库名;
-- ----------------------------
-- 保存包结构信息
-- ----------------------------
-- 删除有结构
DROP TABLE IF EXISTS `表名`;
-- 创建表
CREATE TABLE `表名` (
`auto_id` int(11) NOT NULL AUTO_INCREMENT,
`paper_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`bag_structure` longtext CHARACTER SET utf8 COLLATE utf8_bin NULL,
PRIMARY KEY (`auto_id`) USING BTREE, -- 主键
UNIQUE INDEX `paper_id`(`paper_id`) USING BTREE -- 索引
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
用户创建和权限分配
-- 创建用户并分配权限 CREATE USER if not EXISTS '用户名'@'%' IDENTIFIED WITH mysql_native_password BY '密码'; CREATE USER if not EXISTS '用户名'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码'; GRANT ALL ON 表名.* TO '用户名'@'%' with grant option; GRANT ALL ON 表名.* TO '用户名'@'localhost'; flush privileges;
查看用户权限：show grants for 用户名@连接方式;
示例：show grants for namejr@localhost;
DBeaver出现禁止修改：set sql_safe_updates = 0;
浙公网安备 33010602011771号