MySQL zip版本安装

问题：需要查询1月、1-2月、1-3月…1-12月，分区间的累计数据，在同一个sql语句里面实现。多个分开查询效率不高，并且数据手动合并麻烦。

以下参数分别是换算单位、出生日期、当前日期。以下参数分别是换算单位、出生日期、当前日期。mysql 年龄的计算方法。

mysql 时间格式处理

空值设置 coalesce和ifnull空值处理，两种方法。

# 方法一 transform 单字段 （性能佳）df[df['付款方式']=='支付宝'][['付款人电话','被保险人']].groupby(["付款人电话"]).transform(lambda x:"和".join(x))# 方法二 apply 多字段df[df['付款方式']=='微信'].groupby(["付款人电话"]).apply(lambda x:"和".join(x['被保险人']))...

WITH a AS ( SELECT exam_id, uid, DATE_FORMAT( start_time, "%Y%m" ) AS start_month FROM exam_record ) SELECTexam_id,start_month,month_cnt,sum( month_cnt ) over ( PARTITION BY exam_id ORDER BY start_month ) FROM ( SELECT exam_id, start_month, COUNT( ui

SELECT tag,COUNT(uid) as tag_rank FROM exam_record left join examination_info USING(exam_id) WHERE# month(start_time)=9 and uid in( select uid from exam_record WHERE month(start_time)=9 GROUP BY uid having count(submit_time)>=3)GROUP BY tag #

-- 时间格式转换 ,获取当前的时间SELECT DATE_FORMAT(NOW(), '%Y%m')SELECT IFNULL(submit_month,"2021汇总"),COUNT(ymd),ROUND(COUNT(ymd)/max(ymd),3)FROM(SELECT CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(submit_time,'-',2),'-',1),SUBSTRING_INDEX(SUBSTRING_INDEX(submit_tim

CREATE TABLE IF NOT EXISTS user_info_vip ( id int primary KEY AUTO_INCREMENT COMMENT '自增ID', uid int unique not null COMMENT '用户ID', nick_name varchar(64) COMMENT '昵称', achievement int DEFAULT 0 COMMENT '成就值', level int COMMENT '用户等级', job varchar(

DELETE FROM # SECOND 秒# MINUTE 分钟（返回秒数差除以60的整数部分）# HOUR 小时（返回秒数差除以3600的整数部分）# DAY 天数（返回秒数差除以3600*24的整数部分）# MONTH 月数# YEAR 年数# year ,month ,day,hour,second, minute ,start_time ,submit_timeexam_record WHERE score<60 and TIMESTAMPDIFF(minute,

# UPDATE UPDATE examination_info SET tag="Python" WHERE tag="PYTHON"

SELECT DAY(date) as day_id,COUNT(*) FROM question_practice_detail WHERE MONTH(date)=8 and YEAR(date)=2021 GROUP BY day_id

SELECT age_range,count(*) FROM (SELECT IF(IFNULL(age,0)>=25,"25岁及以上","25岁以下") as age_range,device_id FROM user_profile) AS user_profileGROUP BY age_range

SELECT user_profile.device_id,user_profile.university,IFNULL(B.question,0),IFNULL(B.RIGHT_QUESTION,0) FROM user_profile LEFT JOIN (SELECT device_id, COUNT(*) AS question,sum((case when result='right' then 1 else 0 end)) as RIGHT_QUESTION FROM question_pr

SELECT device_id,university,gpa FROM user_profile WHERE (university,gpa) IN (SELECT university,MIN(gpa) FROM user_profile GROUP BY university) ORDER BY university

# SUBSTRING_INDEX 按，分割SELECT SUBSTRING_INDEX(profile,',',-1) as sex,COUNT(*) FROM user_submit GROUP BY sex

需要安装mysql clienthttps://www.cnblogs.com/buxizhizhoum/p/11725588.html

SELECT 单据号, 医保目录编码, 机构收费项目名称,总金额,超限价金额,IF(自付比例>=1,总金额,超限价金额) as 自费费用 FROM jsmx1参考资料https://blog.youkuaiyun.com/weixin_43064185/article/details/89554393

CREATE VIEW yb as SELECT jshid,zje FROM jsmxWHERE mllb IN(1) and jsxmbh IN(21,22) and ylxmbm NOT IN(*,*) and yyxmmc NOT rlike '一|二' and ylxmbm NOT rlike '^a';-- yyxmmc NOT rlike 是不存在 包含一或者二 -- ylxmbm NOT rlike 是不存在 包含看头包括a 参考资料https://www.cnblog

mysql导致内存不够需要定期清理内存https://www.jianshu.com/p/e4cfbcdb5fc1

# Python操作MySQL# 第一步 pip install mysql-connector# -*- coding: UTF-8 -*-import pymysqldb=pymysql.connect(host="localhost", user="root",# 用户名password="186373", # 密码db="sqlfirst", #链接数据库的名port=3306 # 端口)# Create your views here.cur=db.cursor().

windows+R点开开始，在开始的输入框中输入cmd，这个时候出现cmd的快捷方式，可以采用方法一的方法启用管理员调用，但这里我们不用鼠标，直接按住CTRL+SHIFT+ENTER三个键就可以直接调用管理员权限的cmd了net stop mysql80net start mysql80...

-- 加载数据路径load DATA INFILE 'G:\\js.csv'-- js 为表名into table js -- 分割符fields terminated by '^^' --换行符lines terminated by '\n'--忽略首行IGNORE 1 LINES;windows 下面的my.ini文件中SHOW variables like ‘%secure%’ ;修改secure_file_priv=Incorrect integer value ” fo

官方分区指南https://dev.mysql.com/doc/refman/8.0/en/partitioning-range.htmlhttps://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html1.需要设置进行分区的列为主键不设置主键会出现报错；已经有主键的，设置复合主键可以解决这个问题https://blog.youkuaiyun.com/tiansan/article/details/813888932.

出错主要原因是数据库中有csv分割符号，需要用其他符号来导出，建议用^符号来。#分割符用^df=pd.read_csv('*****.csv',delimiter='^')

空值处理方式--方式一select * from A where B1 != 1 or B1 is null--方式二select * from A where IFNULL(B1,'') != 1参考资料https://blog.youkuaiyun.com/qq_33833327/article/details/81513152