-
顺序:
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)
- 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 使用
- 自连接使用时机:一个列起到不止一个条件的作用