mysql常用函数

mysql问题记录一、窗口函数2. 窗口函数 排序全场景表格总结ORDER BYPARTITION BY双维度3. 位移函数二、常用函数1. 类型转换2. 字符串处理3.数值函数三、MySQL 常用语句1. 插入语句2. 更新语句3. 分组统计新增四、常用问题记录1、mysql自动生成当前时间CURRENT_TIMESTAMP2、mysql锁表问题3、mysql去重保留首行方法3用子查询定位“非第一条”重复行适用于所有 MySQL 版本注意事项4、无主键所有字段相同保留首行4.1、纯 MySQL 写法无需Python更高效4.1.1. 极简版MySQL 8.0推荐4.1.2. 兼容低版本 MySQL5.7及以下4.2、Python MySQL 写法灵活可控5、sql语句别名使用注意点(mysql的group by和having后也可以接其余sql不行)一、窗口函数# SQL 常用语法与函数指南 ## 一、窗口函数 ### 1. 窗口函数 聚合函数 sql SUM() OVER( [PARTITION BY column] [ORDER BY column] [ROWS BETWEEN frame_start AND frame_end] )框架范围选项CURRENT ROW当前行n PRECEDING往前 n 行数据n FOLLOWING往后 n 行数据UNBOUNDED PRECEDING分区起点UNBOUNDED FOLLOWING分区终点新增内容COALESCE(driver_id,总体)将NULL替换为总体添加到SELECT语句示例中GROUP BY driver_id WITH ROLLUP生成分组小计和总计添加到聚合函数部分2. 窗口函数 排序RANK()OVER([PARTITIONBYcolumn][ORDERBYcolumn])-- 排名有间隔DENSE_RANK()OVER([PARTITIONBYcolumn][ORDERBYcolumn])-- 密集排名无间隔ROW_NUMBER()OVER([PARTITIONBYcolumn][ORDERBYcolumn])-- 行号全场景表格总结ORDER BYPARTITION BY双维度窗口函数类别是否必须加ORDER BY省略ORDER BY的行为是否必须加PARTITION BY省略PARTITION BY的行为典型示例分桶函数ntile必须否则报错/无意义PostgreSQL/Oracle直接报错MySQL随机分桶结果不可控否窗口范围为整个结果集全表分桶ntile(5) over(partition by city order by recency asc) → 按城市分组、按近度分桶排序函数row_number/rank/dense_rank必须否则报错/无意义多数数据库报错少数随机排序序号无意义否窗口范围为整个结果集全表排序row_number() over(partition by city order by amount desc) → 按城市分组、金额排序聚合函数sum/avg/count/max/min可选无ORDER BY全局聚合有ORDER BY可形成滑动窗口否无PARTITION BY全表窗口有PARTITION BY分组窗口avg(r_score) over(partition by city) → 按城市分组算平均分组窗口avg(r_score) over() → 全表平均全局窗口sum(x) over(order by dt rows between 6 preceding and current row) → 全局滑窗全表滚动取值函数lag/lead/nth_value可选业务必加按物理存储顺序取值结果无业务意义否窗口范围为整个结果集全表取值lag(recency) over(partition by user_id order by dt) → 按用户分组、按时间取上一次消费时间百分位函数percent_rank/cume_dist必须报错或结果无意义否窗口范围为整个结果集全表算百分位percent_rank() over(order by frequency) → 全表按频次算百分位3. 位移函数LEAD(column,n)OVER([PARTITIONBYcolumn][ORDERBYcolumn])-- 获取后n行数据LAG(column,n)OVER([PARTITIONBYcolumn][ORDERBYcolumn])-- 获取前n行数据FIRST_VALUE(column)OVER([PARTITIONBYcolumn][ORDERBYcolumn])-- 分区第一个值LAST_VALUE(column)OVER([PARTITIONBYcolumn][ORDERBYcolumn])-- 分区最后一个值新增内容FIELD(driver_id,总体)返回字符串在列表中的位置添加到排序函数部分二、常用函数1. 类型转换CAST(exprAStype)-- 类型转换2. 字符串处理SUBSTRING(str,start[,length])-- 提取子串LOCATE(substr,str[,pos])-- 查找子串位置3.数值函数FLOOR(x)-- 向下取整最接近且 x 的整数CEIL(x)-- 向上取整最接近且 x 的整数ROUND(x,d)-- 四舍五入保留 d 位小数ABS(x)-- 绝对值MOD(x,y)-- 取模余数三、MySQL 常用语句1. 插入语句INSERTINTO表名(列1,列2,...)SELECT(列1值,列2值,...)WHERENOTEXISTS(SELECT列名FROM表名WHERE列名值)2. 更新语句UPDATE表名SET列名值WHERE列名值3. 分组统计新增SELECTCOALESCE(driver_id,总体)ASdriver,COUNT(*)astrip_countFROMtripsGROUPBYdriver_idWITH ROLLUPORDERBYFIELD(driver_id,总体),trip_countDESC说明将COALESCE添加到SELECT语句示例处理NULL值将WITH ROLLUP添加到GROUP BY子句生成汇总行将FIELD()函数添加到ORDER BY子句控制总体行的排序位置四、常用问题记录1、mysql自动生成当前时间CURRENT_TIMESTAMP2、mysql锁表问题-- SHOW FULL PROCESSLIST;SELECTID,USER,HOST,DB,COMMAND,TIME,STATE,LEFT(INFO,300)ASINFO_SNIPPET-- 截取部分SQL语句便于查看FROMinformation_schema.PROCESSLISTWHERESTATEISNOTNULLANDSTATE!-- 确保排除空字符串ORDERBYTIMEDESC;-- 按执行时间降序排列快速定位长耗时操作SELECTCONCAT(KILL ,ID,;)ASKill_CommandFROMinformation_schema.PROCESSLISTWHERESTATElikeWaiting%ORDERBYTIMEDESC;--KILL226038771;3、mysql去重保留首行在 MySQL 中如果表中没有唯一标识列如自增 ID要删除重复行并只保留第一条记录可以使用以下方法假设我们有一张表your_table其中col1, col2, col3这几列组合起来判断是否为重复行可以按以下步骤操作首先创建一个临时表用来存储需要保留的记录CREATETABLEtemp_tableASSELECT*FROMyour_tableWHERE(col1,col2,col3)IN(SELECTcol1,col2,col3FROMyour_tableGROUPBYcol1,col2,col3HAVINGCOUNT(*)1)GROUPBYcol1,col2,col3;-- 同时添加只出现一次的记录INSERTINTOtemp_tableSELECT*FROMyour_tableWHERE(col1,col2,col3)IN(SELECTcol1,col2,col3FROMyour_tableGROUPBYcol1,col2,col3HAVINGCOUNT(*)1);清空原表并将临时表数据导回-- 清空原表TRUNCATETABLEyour_table;-- 将去重后的数据导回原表INSERTINTOyour_tableSELECT*FROMtemp_table;-- 删除临时表DROPTABLEtemp_table;另一种更简洁的方法是使用 MySQL 的行号功能适用于 MySQL 8.0DELETEt1FROMyour_table t1JOIN(SELECTcol1,col2,col3,ROW_NUMBER()OVER(PARTITIONBYcol1,col2,col3ORDERBY(SELECTNULL))ASrnFROMyour_table)t2ONt1.col1t2.col1ANDt1.col2t2.col2ANDt1.col3t2.col3WHEREt2.rn1;这里的ORDER BY (SELECT NULL)表示保留物理顺序上的第一条记录你也可以根据实际情况指定其他排序字段。除了临时表还可以通过以下几种方式删除重复行并保留第一条记录核心思路是利用 MySQL 的行标识或子查询定位需要删除的重复行方法3用子查询定位“非第一条”重复行适用于所有 MySQL 版本通过NOT IN或LEFT JOIN排除每组的第一条记录删除剩余重复行。示例按col1, col2去重DELETEFROMyour_tableWHERE(col1,col2,col3)NOTIN(-- 替换为表中所有列SELECT*FROM(-- 子查询获取每组第一条记录全字段SELECTcol1,col2,col3FROMyour_tableGROUPBYcol1,col2-- 按重复列分组HAVINGCOUNT(*)0-- 确保包含所有组包括只出现一次的)AStemp);内层子查询temp提取每组需要保留的第一条记录GROUP BY会默认保留组内第一条外层NOT IN表示删除不在“保留列表”中的行即重复行注意必须嵌套一层子查询temp否则 MySQL 会禁止直接删除子查询中引用的表注意事项备份数据删除操作前务必备份表避免误删。全字段匹配因无唯一ID关联或判断时需用表中所有列确保精准定位行。性能问题大表操作可能耗时建议先在测试环境验证或分批次执行。优先推荐方法1窗口函数简洁且效率较高需 MySQL 8.0 支持。注意操作前请务必备份数据以防意外情况发生。4、无主键所有字段相同保留首行有两种核心方案适配“无主键所有字段相同保留首行”的需求直接能用4.1、纯 MySQL 写法无需Python更高效4.1.1. 极简版MySQL 8.0推荐用窗口函数给重复行编号直接删除编号1的行不用手动列字段DELETEt1FROM表名 t1JOIN(SELECT-- 用所有字段的哈希值分组自动匹配所有列无需罗列MD5(CONCAT_WS(,,*))ASrow_hash,-- 给每组重复行编序号按数据存储顺序保留首行rn1ROW_NUMBER()OVER(PARTITIONBYMD5(CONCAT_WS(,,*))ORDERBY(SELECTNULL))ASrnFROM表名)t2ONMD5(CONCAT_WS(,,t1.*))t2.row_hashANDt2.rn1;原理CONCAT_WS(,, *)把一行所有字段拼接成字符串MD5()转成唯一哈希值相同行哈希值一致再按哈希分组编号删除编号1的行。4.1.2. 兼容低版本 MySQL5.7及以下用临时表DISTINCT *去重保留首行后覆盖原表-- 1. 备份去重后的数据DISTINCT * 自动保留所有字段相同的首行CREATETEMPORARYTABLEtemp_uniqueASSELECTDISTINCT*FROM表名;-- 2. 清空原表注意若有自增列自增序列会重置无影响DELETEFROM表名;-- 3. 把去重数据写回原表INSERTINTO表名SELECT*FROMtemp_unique;-- 4. 删临时表可选DROPTEMPORARYTABLEIFEXISTStemp_unique;4.2、Python MySQL 写法灵活可控用 Pandas 读取数据去重后覆盖原表无需写复杂SQLimportpandasaspdimportpymysql# 1. 连接 MySQL 数据库替换为你的数据库信息connpymysql.connect(host你的主机,user用户名,password密码,database数据库名,charsetutf8mb4)# 2. 读取数据到 DataFramedfpd.read_sql(SELECT * FROM 表名,conn)# 3. 去重所有字段相同则保留首行keepfirstdf_cleandf.drop_duplicates(keepfirst)# 4. 覆盖原表先清空再写入去重后的数据withconn.cursor()ascursor:cursor.execute(DELETE FROM 表名)# 清空原表conn.commit()# 5. 写入去重后的数据indexFalse 避免写入多余索引列df_clean.to_sql(表名,conn,if_existsappend,indexFalse)# 6. 关闭连接conn.close()原理Pandas 的drop_duplicates()默认按所有列去重keepfirst保留首次出现的行再通过 SQL 覆盖原表。5、sql语句别名使用注意点(mysql的group by和having后也可以接其余sql不行)标准能用别名只有ORDER BY执行最后以及外层查询对子查询/CTE的别名引用不能用别名FROM/JOIN、WHERE、GROUP BY、HAVING均执行在SELECT之前以及SELECT内部的聚合函数参数特殊情况MySQL的HAVING支持别名非标准ORDER BY支持数字序号不推荐生产环境优先用别名ORDER BY和原始表达式其他子句。能否用别名的唯一判断依据记住这个执行顺序所有场景都能自己判断FROM/JOIN → 2. WHERE → 3. GROUP BY → 4. HAVING → 5. SELECT解析别名 → 6. ORDER BY仅在 MySQL 环境运行如公司内部业务库GROUP BY/HAVING/ORDER BY都可以直接用 SELECT 的列别名代码更简洁比如你原 SQL 的GROUP BY dt需要跨数据库兼容如多引擎迁移、通用报表仅ORDER BY用别名通用支持GROUP BY/HAVING必须写原始表达式如GROUP BY DATE(event_time)彻底遵守标准。参考链接1、SQL176 每个城市中评分最高的司机信息https://www.doubao.com/thread/wdbe7eba92a64a6832、SQL177 国庆期间近7日日均取消订单量https://www.doubao.com/thread/we3e854cf2e70c11e3、使用Navicat 导出 MySQL 数据库表结构、表数据到Excel表格中https://blog.csdn.net/Mrqiang9001/article/details/1082427594、利用Navicat Premium导出数据库表结构信息至Excelhttps://blog.csdn.net/xiaohanshasha/article/details/1179673345、根据Excel快速生成表的创建以及新增数据记录的sqlhttps://blog.csdn.net/weixin_46935703/article/details/146184504