mysql杂记【牛客刷题学习】

• 顺序：
  FROM
  ON JOIN
  WHERE
  GROUP BY
  UNION
  WITH
  HAVING
  SELECT
  DISTINCT
  ORDER BY
  LIMIT

• IFNULL
  IFNULL( expr1 , expr2 ) expr不为null返回expr1，为null返回expr2

• 笛卡尔积 适合判相等
  select a.name from tableA a , tableB b; 这个from返回的是笛卡尔积

• 子表
  子表需要取别名

• 用 is null is not null 判空

• 日期作差
  DATEDIFF( D1 , D2 )返回D1-D2

• OR使索引失效，建议使用 多查询语句+UNION 代替

• IF 与 CASE WHEN
  sex = IF( sex=‘f’,‘m’,‘f’)
  sex = CASE sex WHEN ‘m’ THEN ‘f’ ELSE ‘m’ END;
  当一个单元格聚合态有多个数据，CASE WHEN只会提取第一个

SELECT CASE WHEN age < 25 OR age IS NULL THEN '25岁以下' 
           WHEN age >= 25 THEN '25岁及以上'
           END age_cut  , COUNT(*)number
FROM user_profile
GROUP BY age_cut ; #这里的group  by是对case when 的结果进行分组

• between a and b 相当于 (a,b]
• 自定义排序规则

select * from tablename order by field(columnname，columnname的枚举值1，columnname的枚举值2，...）；
#自定义排序规则
select * from user order by field(roleId,2,3,4,1,5);

• 对一个字段升序，对另一个字段降序

select   *  from test order by name asc,id desc 
#asc是默认的，不写也可以，但是 不写不代表 name、id都是desc,desc只对 id生效

• 处理date类型字段

year(date) month(date) day(date)
date_format(date, '%Y-%m')='2021-08'

• 笛卡尔积

select * from tab_a , tab_b； #返回的是笛卡尔积，每条数据都会有并集

• 对于多表的级联查询，根据结果倒推，找出包含最多结果信息的“母表”，据此为基础进行级联 映射信息表
• where field1 or field2 中field1和field2是平级的
• union 用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中 union all合并 union distinct合并且去重
• 聚合函数使用后做连接需要重命名

select device_id,university,a.gpa 
from
user_profile as a 
right join 
( select  distinct(min(gpa)) as gpa     
 #如果不加这个 as gpa，会导致  Unknown column 'b.gpa' in 'on clause',这个报错要么重复列没指明，要么列名写错了
from 
user_profile
group by university)  as  b
on a.gpa=b.gpa 
order by university asc;

• 文本处理

# like 模糊查询  _单字符 %任意数量字符
select left('滚滚长江东逝水',4);   # 返回“滚滚长江” RIGHT 
#CONCAT拼接  LOWER UPPER 大小写  LENGTH长度（字节数）  
#LOCATE( pattern,text) 定位首次出现的索引位
#去空格 LTRIM RTRIM TRIM ,TRIM也可以删除指定的pattern
#replace（原字符串，待替换的子串，用于替换的子串）可以在替换字符串作妙用，如空白
#LPAD（字符串，固定长度，需要填充的字符）RPAD同理
# SUBSTRNG(TEXT , START, LENGTH )   
#SUBSTRING_INDEX(  text , delim , count) 

sub相关链接

• case when的列何时生效

select 
case when profile like "%female%" then "female" else "male" end as gender ,
 count(gender) as number  
#此处报错Unknown column 'gender' in 'field list'，说明二者平行执行，故无法使用，可改成count（*），
#此时count（*）就是返回分组后每组内的总条数count，不是全表的总条数，如果某列为null，使用count(field)则计为0

from  user_submit group by gender;  #case when 的 as  可以在groupby 使用

• 自连接使用时机：一个列起到不止一个条件的作用