SQL注意事项

这里只介绍一些SQL中的操作事项，并不是所有都介绍一遍，常识非介绍。

1、SQL衔接

1.1、内连接

• left join：左边可能为空，右边全需要
• right join：左边可能为空，右边全需要
• inner join：显式内连接，where 隐式内连接

1.2、常用

• where：不能使用聚合函数判断，如果要获取聚合函数值，可通过子查询获取数据
• order by：默认升序ASC，否则也可显式降序DESC
• group by：根据某个列分组
• HAVING：需要配合 group by使用，用于对分组后数据的聚合函数进行再次筛选
• distinct：只能放在第一个位置去重，且只能在select中使用

2、SQL函数

2.1、简单函数

• ISFULL(a)：若a为空，则返回1，否则返回0
• IFNULL（a，b）：当a非空时，取a值，否则取b值
• DATE_ADD(date, interval 1 day)：对date这天增加一天，date可为负数
• DATE_SUB(date, interval 1 day)：对date这天减少一天，date可为负数
• DATEDIFF（date1，date2）：相差天数，DATEDIFF（减数，被减数）
• TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)：日时间差转日以外的其他单位，如月或年
• time(date1)：取date1的时分秒
• year(date1)：取date1的年
• month(date1)：取date1的月
• day(date1)：取date1的日
• now(number)：当a为空时取当前时间，否则精确到当前时间number位小数点后
• CONCAT(str, "%")：相当于str + "%" = str%
• IF(expr, v1, v2)：当expr为真是返回v1的值，否则返回v2
• CASE：case table.a when ‘b’ then ‘c’ end
• ASCII()：可用异或获取值
• LEFT(str, number)：对str字符串从左边截取number个数字
• RIGHT(str, number)：对str字符串从右边截取number个数字
• ROUND(number1, number2)：对number1保留number2个有效小数
• COALESCE(expression_1, expression_2, ...,expression_n)：遇到非null值即停止并返回该值，若所有表达式都是null值，最终将返回一个null值

2.2、复杂函数

2.2.1、DATE_FORMAT(date,format)

描述：date参数是合法的日期。format规定日期/时间的输出格式

%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值（00-31）
%e 月的天，数值（0-31）
%f 微秒
%H 小时（00-23）
%h 小时（01-12）
%I 小时（01-12）
%i 分钟，数值（00-59）
%j 年的天（001-366）
%k 小时（0-23）
%l 小时（1-12）
%M 月名
%m 月，数值（00-12）
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒（00-59）
%s 秒（00-59）
%T 时间, 24-小时（hh:mm:ss）
%U 周（00-53）星期日是一周的第一天
%u 周（00-53）星期一是一周的第一天
%V 周（01-53）星期日是一周的第一天，与 %X 使用
%v 周（01-53）星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天（0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位

2.2.2、开窗函数

语法：over(partition by 列名1 order by 列名2 )，括号中的两个关键词partition by和order by可以只出现一个，over()前面是一个函数，这里主要介绍聚合函数的sum()，其它聚合函数也可作为开窗函数，这里就不做主要介绍，除了集合函数，此处还将介绍排名开窗函数的使用(row_number、dense_rank、rank)以及开窗函数的取值。
ps：在排名开窗函数中必须使用order by语句，partition by用于将结果集进行分组，开窗函数应用于每一组

1. sum(score) over(partition by name)
   • over：对作用的每行数据进行函数的开窗，里面主要写开窗的定义
   • partition by name：对name进行分组
   • 若整个表为：user(id, name, score)，则这里主要是对name进行分组后计算其sum(score)值
   • 和直接使用gropu by 不同的是窗口函数会生成多行
2. sum(score) over (order by id)
   • 这里主要是根据id进行排序，然后对socre逐层累加求和
   • 适合于按顺累加的总数的计算
3. first_value(score) over( partition by name)：根据name分组，然后再获取score列第一个值
4. last_value(score) over(partition by name)：根据name分组，然后再获取score列最后一个值值
5. lead(score,1,0) over(partition by name)：根据name分组，然后再获取score列后N（代表所有）条数据，偏移量为1，默认值为0
6. lag(score) over(partition by name)：根据name分组，然后再获取score列前N条数据，偏移量为1，默认值为0
7. row_number() over( partition by name order by score asc)：根据name分组，根据score排序，按顺序赋值递增数字1~N，如：123456
8. rank() over(partition by name order by score asc)：根据name分组，根据score排序，按顺序赋值递增数字1~N，但是如果有相同的值会生成相同的序号，如：113446
9. dense_rank() over(partition by name order by score asc)：根据name分组，根据score排序，按顺序赋值递增数字1~N，但是如果有相同的值会生成相同的序号，接下来的序号不会间断，如：112334