mysql的语法

一、功能函数1.char_length() 查看字符串长度select tweet_id from tweets where char_length(content)152.计算日期类型的数据(1)datediff()计算的天数select distinct w2.id Id from Weather w1 left join Weather w2 on datediff(w2.recordDate,w1.recordDate)1 where w2.Temperature w1.Temperature;2细化day,month,year差-- 计算两个日期相差的天数 SELECT TIMESTAMPDIFF(DAY, 2023-01-01, 2023-01-31); -- 返回30 select timestampdiff(DAY, 2023-01-01, 2023-01-31); -- 计算两个日期相差的月数 SELECT TIMESTAMPDIFF(MONTH, 2023-01-15, 2023-03-20); -- 返回2 -- 计算两个日期相差的年数 SELECT TIMESTAMPDIFF(YEAR, 2020-05-01, 2023-05-01); -- 返回33. 对数据分配处理(cross join)select name from Employee where id in (select managerId from Employee group by managerId having count(managerId)5 );4.条件语句控制where处理的是单个元数据在分组之间。having处理在分组之后。所以where 后面不能跟count*和group by。select name from Employee where id in (select managerId from Employee group by managerId having count(managerId)5 );5.round处理后余小数和sum累加avg平均数 ifnull判空0select product_id , ifnull(round((sum(sale)/sum(units)),2),0) average_price from ( select p.product_id,p.price*u.units sale,u.units from Prices p left join UnitsSold u on p.product_idu.product_id and(u.purchase_date between p.start_date and p.end_date) ) t group by product_id;6.对于日期数据的处理分类 date_format ()常用格式说明符说明符描述示例结果%Y四位年份2023%y两位年份23%m月份(01-12)05%c月份(1-12)5%d日(01-31)07%e日(1-31)7%H小时(00-23)14%h小时(01-12)02%i分钟(00-59)05%s秒(00-59)30%pAM或PMPM%W星期名称Monday%a缩写的星期名Mon%M月份名称May%b缩写的月份名Maydate_format(trans_date,%Y-%m)SELECT DATE_FORMAT(date_column, 格式字符串) FROM table_name;7.sum()增加事件语句sum(case when stateapproved then 1 else 0 end) approved_count, sum(amount) trans_total_amount, sum(case when stateapproved then amount else 0 end) approved_total_amount8.over(),对于滑动窗口可以做数据的详细处理select visited_on, amount, average_amount from ( select distinct visited_on, sum(amount) over(order by visited_on range interval 6 day preceding) amount, round(sum(amount) over(order by visited_on range interval 6 day preceding)/7, 2) average_amount from Customer) t where datediff(visited_on, (select min(visited_on) from Customer))6 order by visited_on;select visited_on,amount,average_amount from ( select visited_on, sum(amount) over(order by visited_on rows between 6 preceding and current row) amount, round(avg(amount) over(order by visited_on rows between 6 preceding and current row),2) average_amount, count(*) over(order by visited_on rows between 6 preceding and current row) cunt from (select visited_on,sum(amount) amount from Customer group by visited_on) a ) b where cunt69.partition by 相较于group by 可保留原表数据不会减少10查询数据第二行通过offset跳过数据select (select distinct salary from Employee order by salary desc limit 1 offset 1) SecondHighestSalary;二、细节处理1.对null值的处理可以用is null查询select customer_id,count(*) count_no_trans from Visits v left join Transactions t on v.visit_id t.visit_id where t.transaction_id is null group by customer_id;