[大数据]数据库练习001：SQL函数总结

max()

• 取最大值，忽略NULL

min()

• 取最小值，忽略NULL

count()

• 统计总数，加distinct可以计算去重总数；
• count(1)和count(*)不忽略NULL，count（列名）忽略NULL

avg()

• 计算平均值，忽略NULL

sum()

• 求总和，忽略NULL

round()

• 指定保留多少位小数，round(m, n)数值m保留n位小数

case…when…then…else…end

• 用法：https://www.cnblogs.com/chenduzizhong/p/9590741.html
• 简单函数
  CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
• 搜索函数
  CASE WHEN [expr] THEN [result1]…ELSE [default] END
• 可以有多个判断，各判断之间不需要逗号

if(条件，为真的取值，为假的取值）

• if(x,n,m)如果条件x为真则取n，反之取m

date()

• 提取合法的日期时间表达式中的日期部分

day()

• 提取合法的日期时间表达式中的day

month()

• 提取合法的日期时间表达式中的month

year()

• 提取合法的日期时间表达式中的year

date_format(日期，‘格式’)

• 按指定格式提取对应时间日期中日期
• 如date_format(date, ‘%Y%m’)

last_day(日期)

• 获取当月的最后一天的日期

date_add(n, interval m day)

• 在日期n上增加指定的时间间隔m天，可以更换其他单位，如second、year

datediff(时间单位，开始日期，结束日期）

• 获取开始时间到结束时间之间的天数，单位可以是时分秒年月日周等等，但最终获取到的数值单位是天

timestampdiff(时间单位，开始时间，结束时间）

• 获取开始时间到结束时间之间的时间差，单位可以是时分秒年月日周等等，最终获取到的数值单位就是指定的时间单位

cast(表达式 as 数据类型)

• 将某种数据类型的表达式显式转换为另一种数据类型
• 数据类型包含整数(SIGNED)、浮点数 (DECIMAL)、日期时间(DATETIME)、日期(DATE)、时间(TIME)、字符型(CHAR())等等
• 例如：CAST(exam_cnt_rank_21 AS SIGNED)，CAST(‘9.0’ AS DECIMAL)，SELECT CAST(NOW() AS DATE)

char_length(str)

• 求字符串的长度，不管汉字、数字、字母都算是一个字符

length(str)

• 求字符串的长度，汉字算3个字符，数字、字母都算是一个字符

UCASE(str)

• 把字段的值（字符串所有字符）转换为大写

LCASE(str)

• 把字段的值（字符串所有字符）转换为小写

upper(str)

• 把字段的值（字符串所有字符）转换为大写

lower(str)

• 把字段的值（字符串所有字符）转换为小写

substring_index(str, 分隔符,n)

• 对字符串str进行截取，使用分隔符将字符串分割成多个字段，截取至第n个字段
• 如1，是截取第1个字段，3是截取1、2、3共3个字段（含分隔符），-1是截取倒数第1个字段，-2是从倒1开始截取至倒2字段

substr(str, i, n)

• 从字符串str指定位置i往后截取n个字符，i是正数时从开头开始算第i个字符，负数时则从末尾开始算（i等于0或1都是从第一个开始）
• 不指定n时，截取后面所有字符
• https://www.cnblogs.com/dshore123/p/7805050.html

concat(str1,str2,str3…)

• 将多个字符串连接成一个字符串（通常是一个元组中的多个属性值），任一个值为null，就输出null

concat_ws(分隔符，str1,str2,str3…）

• 使用分隔符，将多个字符串连接成一个字符串，默认分隔符是逗号‘，’

group_concat()

• 将分组中的值连接成一个字符串，可以使用distinct去重，使用order排序，最后用separator指定分隔符，默认是‘，’

row_number() over (partition by 列名1 order by 列名2）

• 先按列名1进行分组，再在组内按列名2进行排序，会得到序号，相同的值也会因为顺序而得到不同的序号，序号只可能是1，2，3，4，5…

rank() over (partition by 列名1 order by 列名2）

• 先按列名1进行分组，再在组内按列名2进行排序，会得到排名号，相同的值有相同的序号，但会按人数排名跳过序号，序号可能是1，2，2，4，5…

dense_rank() over (partition by 列名1 order by 列名2）

• 先按列名1进行分组，再在组内按列名2进行排序，会得到排名号，相同的值有相同的序号，不会跳过序号，序号可能是1，2，2，3，4…

percent_rank() over (partition by 列名1 order by 列名2）

• 先按列名1进行分组，再在组内按列名2进行排序，会得到0-1之间的数的百分比排名序号（先按rank()规则得出序号，然后计算(序号-1)/(总数-1)得出）

lag(列名1,n,默认值) over (partition by 列名 order by 列名）

• 分组排序后，取当前行往上第n行（默认为1）的值（列名1指定的那列），如果值为null，则取默认值，如果没有默认值则取null

lead(列名1,n,默认值) over (partition by 列名 order by 列名）

• 分组排序后，取当前行往下第n行（默认为1）的值（列名1指定的那列），如果值为null，则取默认值，如果没有默认值则取null

first_value(列名1) over (partition by 列名 order by 列名）

• 分组排序后，取当前分组中的第一个值(列名1指定的那列）

last_value(列名1) over (partition by 列名 order by 列名）

• 分组排序后，取当前分组中的第一个值(列名1指定的那列）

max(列名1) over (partition by 列名2)

• 按列名2分组后，求出列名1的最大值，如max(score) over (partition by exam_id)

min(列名1) over (partition by 列名2)

• 按列名2分组后，求出列名1的最小值，如min(score) over (partition by exam_id)

count() over (partition by … [order by…])

• count(start_time) over (partition by exam_id, date_month)：得到每份试卷每月作答数
• count(start_time) over (partition by exam_id order by date_month)：得到每份试卷截止当月的作答总数

特殊的更新和删除

• replace into tablename values：先检查是否有主键对应的数据，如果有就先删掉旧数据再插入新数据，否则就之间插入新数据。
• truncate table tablename：删除表中的数据的同时重置自增主键。