各种 sql 语句

sql 语句：

SELECT max(val) as level_max_val from (select greatest(level1,level2,level3,level4,level5,level6,level7,level8,level9,level10) as val from kbt_2020cv52_data) k;

select concat('作者','-','Sheenky') as 作者信息;

select upper('sheenky') as 大写;
select lower('SHEENKY') as 小写;

select substr('学SQL就关注博主sheenky',2,8) as out_put;

select instr('学SQL就关注博主sheenky','关注') as 第一次出现;

select trim(' 学SQL就 关注博主 sheenky ') as 空格去除;

select lpad('编程秃头',10,'tu') as out_put;
select rpad('编程秃头',10,'tu') as out_put;

select replace(NAME,'符兴','符强') as 替换之后 from tb_teacher;

#四舍五入； 当对正数进行四舍五入：按照正常的计算方式，四舍五入即可。当对负数进行四舍五入：先把符号丢到一边，对去掉负号后的正数进行四舍五入，完成以后，再把这个负号，补上即可。
select round(1.595658,3) as out_put;

#向上取整，返回>=该参数的最小整数。求的是大于等于这个数字的最小整数
select ceil(1.9) as out_put;
select ceil(1.1) as out_put;

#向下取整，返回<=该参数的最大整数，求的是小于等于这个数字的最大整数。
select floor(1.99) as out_put;
select floor(1.1) as out_put;

#此函数叫截断函数，顾名思义就是就是截取不要的部分，然后删掉（断掉）它。在小数点的D位置处，截取数字直接删去数字，若在左边就是位置取整不使用任何法则。
这个函数理解起来也不难，我们把truncate当作小数点（.）x是要截取的数字。D为正数时是小数点的右侧部分，D为0时则不要小数部分，D为负数时是小数点左边部分，具体使用看例子演示。
select truncate(314159.2673525,5) as 截取之后;
select truncate(314159.2673525,0) as 截取之后;
select truncate(314159.2673525,-4) as 截取之后;

select mod(10,3) as out_put;

select pow(5,2) as 平方运算;

select now() as 当前时间;

select surdate() as 当前日期;

select curtime() as 当前时间;

select date_add(curtime(), interval 1 year) as 一年后;

select date_add(curtime(), interval 1 month) as 一个月后;

select date_add(curtime(), interval 1 day) as 一天后;

select weekofyear() as 当前时间下的周数;

select quarter() as 当前季度;

select str_to_date() as 指定格式的日期;

select last_day(now()) as 当月最后一天的日期;

#计算两个时间相差的天数
select concat('两时间相差', datediff(curtime(), '1992-01-21')) as 天;

#timestampdiff(unit,start_date,end_date)函数
计算两个时间返回的年/月/天数；
unit参数是确定(start_date,end_date)结果的单位，表示为整数，以下是有效单位：
year：年份、month：月份、day：天、hour：小时、minute 分钟、second：秒、microsecond：微秒、week：周数、quarter：季度
select timestampdiff(year, '1992-01-21', concat()) as 相差几年;

select score, if(score<60,'不及格','及格') as 备注 from table_name;

select ifnull(NULL,1),ifnull(NULL,2);

# 离散数值与实际含义的映射
SELECT *,
  CASE is_discount
  WHEN 0 THEN '无折扣'
  WHEN 1 THEN '享受折扣'
  END AS discount_new,
  CASE 
  WHEN pay_type IN (1,3,5,7,10) THEN '微信支付'
  WHEN pay_type IN (6,9,12) THEN '快捷支付'
  WHEN pay_type IN (2,4,8,11) THEN '支付宝支付'
  END AS pay_type_new
FROM orders;

# 连续数值映射为离散区间
SELECT *,
  CASE 
  WHEN YEAR(birthday) BETWEEN 1960 AND 1969 THEN '60后'
  WHEN YEAR(birthday) BETWEEN 1970 AND 1979 THEN '70后'
  WHEN YEAR(birthday) BETWEEN 1980 AND 1989 THEN '80后'
  ELSE '90后' 
  END AS age_group
FROM orders;

SELECT MONTH(order_date) AS `month`,
  SUM(CASE WHEN pay_type IN (1,3,5,7,10) THEN pay_amt END) AS WeChat,
  SUM(CASE WHEN pay_type IN (6,9,12) THEN pay_amt END) AS Bank_Card,
  SUM(CASE WHEN pay_type IN (2,4,8,11) THEN pay_amt END) AS Ali_Pay
FROM orders
WHERE YEAR(order_date) = 2018
GROUP BY MONTH(order_date)
ORDER BY MONTH(order_date);