IF ()函数

:条件语句， IF （条件 ，'',''） AS a

case表达式：

CASE

when 条件（字段条件） then “（值，结果） ” （条件判断）

when 条件 （字段条件） then ‘’

else ‘’

END AS 字段

-- 条件表达式举例
关键词

SELECT  case  WHEN  THEN  ELSE  END

SELECT  name, 
  case 
        WHEN  score between  90 AND  100 THEN '优秀'
        WHEN  score between  80 AND  89  THEN  '良好'
        WHEN  score between  60 AND  79  THEN  '一般'
        ELSE '不及格'
  END AS '等级'
FROM  score;

合并字符串：

CONCAT(): concat(字段1," ",字段2) AS 字段3

字段合并的函数

group_concat() 这是将一个字段的多行数据组成成一个字符串

-- LISTAGG 函数的使用,可以把搜索出来指定字段做一个拼接 

  select group_concat(email)
  FROM table
  where status=1

-- 还可以对字段进行排序，和定义分割符(使用separator指定分割符‘；’)
  select group_concat(email  order by email  separator ';')
  FROM table
  where status=1

时间函数：

• now():返回当前的时间

• now（） 返回当前时间 格式为 YYYY-MM-DD HH:MM:SS
• year(now()) 返回当前的年份
• month(now()) 返回当前的月份
• day(now()) 返回当前的日期
• dayofyear(now()) 到今天为止一共过了多少天
• weekofyear(now()) 到今天为止一共过滤多少周
• CURDATE（） 返回系统当前时间 格式尾为YYYY-MM-DD

• 日期的格式化

• date_format

• data_format (now(),'%m%d') => 0318

• 字符串转变为日期

• str_to_date

• str_to_date('19910306',%y%m%d) =>1991-03-06

• 对于date这种格式的字段可以直接使用year(),day(),month(),来取对于的年月日

-- 查询学生的年龄

SELECT a.* ,year(now())-year(a.s_brith) AS age FROM student a;

-- year(now())-year(a.s_brith) 把今年减去s_brith(1990-08-06) 这里只提取年，进行相减然后得到
-- 年龄

数学函数

1. 向上取整 CEIL( )
2. 向下取整 FLOOR()
3. 四舍五入 ROUND(x,d) x:值，d:小数位 例如：round（1.233，2） =>1.23

-- 查询各科的最高，最低和平均分，按照如何的显示显示出来：课程id、课程名、最高分、最低分、平均分、及格率、中等率、不及格率、优良率、来展示
SELECT c.c_id AS '课程id',c.c_name AS '课程名', MAX(sc.s_score) AS '最高分',
        MIN(sc.s_score) AS '最低分',AVG(sc.s_score) AS '平均分',
CONCAT (ROUND(SUM(CASE WHEN  sc.s_score>=60 THEN 1 ELSE 0 END )/COUNT(sc.s_score)*100,2),'%') AS '及格率',
CONCAT (ROUND(SUM(CASE WHEN  sc.s_score BETWEEN 70 AND 80 THEN 1 ELSE 0 END )/COUNT(sc.s_score)*100,2),'%')AS '中等率'
FROM score AS sc
LEFT JOIN course AS c ON c.c_id=sc.c_id
GROUP BY c.c_id,c.c_name

空值处理的函数

1. ifnull( (表达式)，value )

如果判断表达式为null则值为value

2.COALESCE(value1, value2, ..., value_n)

对null值提供默认值

-- coalesce 
SELECT COALESCE(column1, column2, 'Default Value') FROM table_name;
-- 如果字段1，字段2为null,责备替换为'Default Value'

字符串处理的函数

合并字符串：

CONCAT() ： SELECT CONCAT('Hello', ' ', 'World!'); =》Hello World! ，

不能指定拼接字符

CONCAT_WS() ：使用指定分隔符拼接合并字符串，CONCAT_WS("拼接字符",'字符1'，‘字符2’) 可以指定拼接字符

GROUP_CONCAT(): 可以某一列的值利用分组拼接再一块，把对于条件的字段进行整个拼接

字符串切割：

SUBSTRING() ： SELECT SUBSTRING('abcdef', 2, 3); -- 从第2个字符开始，取3个字符

=> bcd

LEFT()： 往左边取字符 SELECT LEFT('abcdef', 3); -- 取左边3个字符

RIGHT(): 往右边取字符 SELECT RIGHT('abcdef', 2); -- 取右边2个字符

替换字符：

REPLACE() ： SELECT REPLACE('I love Java', 'Java', 'MySQL'); => I love MySQL

去除空格：

TRIM() ：

计算字符串长度 ：

LENGTH() ：

-- 关键信息，名字的脱敏处理 
CASE 
    WHEN a.auth_type = '0' THEN 
        CONCAT(
            SUBSTRING(a.auth_name, 1, 1), -- 取姓名的第一个字符
            '*', 
            CASE 
                WHEN LENGTH(a.auth_name) > 1 THEN SUBSTRING(a.auth_name, -1) -- 取姓名的最后一个字符
                ELSE '' 
            END
        )
    ELSE a.auth_name 
END AS display_name

-- substring (a.auth_name,1,1) 取名字中的第一个字符
-- 之后添加*
-- length (字段) >1 则 substring（字段，-1） 如果名字长度大于1，则取最后一个字符