一个混错圈儿的小测试

1、下载mysql安装包安装包:mysql5.7-win64提取码：4hmp2、解压mysql-5.7.33-winx64到安装目录3、添加my.ini配置文件配置文件内容：[mysqld]skip-grant-tables=1port = 3306basedir=D:\mysql-5.7.33-winx64datadir=D:\mysql-5.7.33-winx64\datamax_connections=200character-set-serv..

1

你见过一个联表查询需要3300+秒么？今天我们的慢查询日志捕捉到了一只，我们一起来解剖一下看看这到底是何方妖孽。首先，我们查看一下罪魁祸首的执行计划EXPLAINSELECT r.id ,si.set_id ,m.project_id 应添加项目列表FROM report r INNER JOIN application a ON r.app_id=a.id INNER JOIN application...

SELECT b.assign_name,b.introduce_amount FROM daily_assign_bug b WHERE b.introduce_amount IN( SELECT introduce_amount FROM daily_assign_bug GROUP BY introduce_amount ORDER BY introduce_amount DESC LIMIT 10);

SELECT b.assign_name,b.introduce_amount FROM daily_assign_bug b WHERE b.introduce_amount >=( SELECT introduce_amount FROM daily_assign_bug GROUP BY introduce_amount ORDER BY introduce_amount DESC LIMIT 9,1);SELECT b.assign_name,b.introduce_...

SELECT C.project_name,C.antibiotic_dict_name,C.`检出总数`, SUM(CASE C.`drugs_result` WHEN 'D' THEN C.`数量` ELSE 0 END ) AS '剂量依赖性敏感', CONCAT(SUM(CASE C.`drugs_result` WHEN 'D' THEN FORMAT(C.`数量`/C.`检出总数`*100,2) ELSE 0 END),'%') AS '剂量依赖性敏感比率',...

一、问题有一张如下图所示的表，需要我们查出 result 值大于 reference_high值的数据然后我们写了下面的SQL查询语句SELECT i.result,i.reference_high FROM report_item iLEFT JOIN report r ON r.id=i.report_idWHERE r.org_id=54 AND r.report_status=1AND r.add_date BETWEEN '2020-12-01' AND '2020-12-.

示例操作系统：centos，具体信息如下1、官网下载mysql安装包https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar然后用xftp上传到linux服务器2、解压安装包tar -xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar3、卸载系统自带的mariadb-librpm -qa|grep mariadbrpm -

一、语法EXPLAIN/DESC + [查询SQL];二、输出概要id: 执行优先级标识select_type: 查询类型。table: 输出结果集的表partitions: 匹配的分区type: 表的连接类型possible_keys: 查询时可能使用的索引key: 实际使用的索引key_len: 索引字段的长度ref: 列与索引的比较rows: 扫描出的行数(估算的行数)filtered: 按表条件过滤的行百分比Extra: 执行情况的描...

一、函数介绍1、ROUND(X,D)和ROUND(X)round函数用于数据的四舍五入，x指要处理的数，d是指保留几位小数round(x) ，其实就是 round(x,0)d可以是负数，代表指定小数点左边的d位整数位为0,同时小数位均为02、FORMAT(X,D)提供数据内容格式化功能，可以格式化数据为整数或者浮点数，能四舍五入D为负数时，按0处理3、LEFT(str,len)left函数是一个字符串函数，它返回具有指定长度的字符串的左边部分,str为要.

MYSQL慢查询日志可以记录执行缓慢的sql语句以及执行时间，通常用来排查mysql性能问题一、查看慢查询日志是否开启show variables like 'slow_query%'; show variables like 'long_query_time'; -- 查询慢查询设置时间二、开启慢查询的配置方式1、通过命令临时开启set global slow_query_log='ON';set global slow_query_log_file='/va...

# coding=utf-8import pymysqlclass MysqlUntil: def __init__(self, host, user, passwd, db, port, charset): self.host = host self.user = user self.passwd = passwd self.db = db self.port = int(port) self.

# 查看时区show variables like '%time_zone%';#修改时区方法一：show variables like '%time_zone%';set time_zone = '+8:00'; # 修改当前会话失时区set global time_zone = '+8:00'; #修改全局的时区配置flush privileges;方法二：修改配置文件(my.cnf)，重启服务# vim /etc/my.cnf ##在[mysqld]区域...

一、计算时间差SELECTTIMESTAMPDIFF(YEAR,'2020-10-0100:00:00',NOW()); SELECTTIMESTAMPDIFF(QUARTER,'2020-10-0100:00:00',NOW()); SELECTTIMESTAMPDIFF(MONTH,'2020-10-0100:00:00',NOW()); SELECTTIMESTAMPDIFF(WEEK,'2020-10-0100:00:00',NOW());...

SELECT CURDATE() AS 当前日期;SELECT NOW() AS 当前时间;SELECT QUARTER(CURDATE()) AS 当前季度;SELECT DATE_FORMAT(CURDATE(),'%w') AS 当前周几; --SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') AS 本周一;SELECT DATE_FORMAT( SUBDA..