数据璐SQL零基础入门教程学习第九天主知识点八：常用函数

本文详细介绍了MySQL中的条件函数如CASE WHEN和IF，数据类型转换函数CAST和DATE_FORMAT，日期函数如YEAR、MONTH、DAY，以及字符处理函数ROUND、CONCAT、LEFT、RIGHT和SUBSTRING。通过实例展示了如何在SQL查询中使用这些函数，帮助读者理解并掌握MySQL中的常用函数，提升数据处理能力。

【知识点引入】
- 接着我们再来补充一些mysql中常用函数
  - 【条件函数语法】
    - case when
      - case 字段名 when 值1 then 值2 else 值3 end
      - 【解释】
        创建新的一列字段，当某字段等于值1在新列中就等于值2，除此之外的数据在新列中就等于值3
      - 或
      - case when 字段名运算符值1 then 值2 else 值3 end
      - 【解释】
        创建新的一列字段，字段名运算符值1这一部分可以看成是条件代码，当某字段符合条件时，在新列中就等于值2，除此之外的数据在新列中就等于值3
    - if
      - if(字段名运算符值1,值2,值3)
      - 【解释】
        创建新的一列字段，如果字段满足条件，在新列中就等于值2，否则就等于值3
  - 【数据类型转换函数语法】
    - cast
      - cast(字段名 as type)
      - type:date或int等
      - 【解释】
        将字段从原来的数据类型转换成指定的数据类型，比如日期date，整数型int等，在type的位置填写指定的参数即可
    - date_formate
      - date_formate(date,formate)
      - 格式
      - 常用日期格式
      - 【解释】
        date_formate用于以不同格式显示日期/时间数据。date 指合法的日期字段，format 规定日期/时间的输出格式，记得格式外加单引号
        
        例如：date_formate('2021-06-01','%m/%d/%Y')返回数据06/01/2021
  - 【日期函数语法】
    - year、month、day
      - year(date)
      - month(date)
      - day(date)
      - 【解释】
        year函数提取数据格式为日期或时间字段的年份
        
        month提取月份，day提取几号
        
        hour, minute, second等函数同理
        
        例如：year('2021-06-01')返回数据2021
    - now
      - now()
      - 【解释】没有其他参数，返回当前的日期和时间
    - date_add
      - date_add(date,interval 值 type)
      - type
      - 【解释】
        向日期添加指定的时间间隔
        
        例如：date_add('2021-06-01',interval 2 day)，向日期函数2021-06-01加了2天，返回值2021-06-03
    - date_sub
      - date_sub(date,interval 值 type)
      - 【解释】
        从日期减去指定的时间间隔
        
        type参数与date_add相同，使用方法也相同
    - datediff
      - datediff(date1,date2)
      - 【解释】
        返回两个日期之间的天数，date1-date2
        
        date1 和 date2 代表是日期或日期加时间的表达式
        
        注意只有值的日期部分参与计算
        
        例如：datediff('2021-06-08','2021-06-01')返回7，datediff('2021-06-01','2021-06-08')返回-7
  - 【字符处理函数语法】
    - round
      - round(字段名,小数指定位数)
      - 【解释】
        round函数将数值字段舍入到指定的小数位数
        
        例如：round(3.15,1)返回数据3.2
    - concat
      - concat(字段名1,字段名2,'字符串')
      - 【解释】
        concat是字符串拼接函数，用于将多个字符串连接在一起
        
        例如：concat('123',' ','ABC')返回数据123 ABC
    - left、right
      - left(字符串,n)
      - right(字符串,n)
      - 【解释】
        left函数返回字符串从左到右n个字符
        
        right函数返回字符串从右到左n个字符
        
        例如：left('123456',2)返回12，right('123456',3)返回456
    - substring
      - substring(字符串,x,y)
      - 【解释】
        substring函数用来截取字符串从x位开始取y位，如果没有y则取从x位开始到最后一位
        
        例如：substring('New York',3,4)返回w Yo，substring('New York',3)返回w York
【例题讲解】
- 点击链接Window LAG - SQLZOO 第一题
  - 查看本题数据库表covid
    - 观察本表数据，记录了国家（name）截至时间（whn）的累计确诊（confirmed），累计死亡（deaths），累计治愈（recovered）
  - 我们以本题为例试验一下我们刚学习完的函数
  - case when和if函数
    - 【运行代码】
      - select
      - recovered 累计治愈人数
      - ,case when recovered = 1 then 'one' when recovered > 1 then 'more' else '0' end
      - from covid
      - where recovered > 0
    - 【运行结果】
  - year、month、day函数
    - 【运行代码】
      - select
      - whn 更新时间
      - ,year(whn) 年
      - ,month(whn) 月
      - ,day(whn) 日
      - from covid
      - where recovered > 0
    - 【运行结果】
  - date_add
    - 【运行代码】
      - select
      - whn 更新时间
      - ,date_add(whn,interval 2 day) 加2天
      - from covid
      - where recovered > 0
    - 【运行结果】
  - round和concat嵌套得到百分比数据
    - 【运行代码】
      - select
      - confirmed
      - ,deaths
      - ,recovered
      - ,recovered/confirmed
      - ,concat(round((recovered/confirmed)*100,2),'%') 治愈率
      - from covid
      - where recovered/confirmed > 0.3
    - 【运行结果】
  - substring
    - 【运行代码】
      - select distinct
      - name
      - ,substring(name,2,3)
      - ,substring(name,2)
      - from covid
      - where recovered/confirmed > 0.3
    - 【运行结果】
【总结】
- 【条件函数语法】
  - case when
    - case 字段名 when 值1 then 值2 else 值3 end
    - 或
    - case when 字段名运算符值1 then 值2 else 值3 end
  - if
    - if(字段名运算符值1,值2,值3)
- 【数据类型转换函数语法】
  - cast
    - cast(字段名 as type)
    - type:date或int等
  - date_formate
    - date_formate(date,formate)
    - 格式
    - 常用日期格式
- 【日期函数语法】
  - year、month、day
    - year(date)
    - month(date)
    - day(date)
  - now
    - now()
  - date_add
    - date_add(date,interval 值 type)
    - type
  - date_sub
    - date_sub(date,interval 值 type)
  - datediff
    - datediff(date1,date2)
- 【字符处理函数语法】
  - round
    - round(字段名,小数指定位数)
  - concat
    - concat(字段名1,字段名2,'字符串')
    - 字符串和字段都支持哦~
  - left、right
    - left(字符串,n)
    - right(字符串,n)
  - substring
    - substring(字符串,x,y)
【练习题】
- 【1】SELECT from WORLD Tutorial - SQLZOO第十二题
  - 【题目】
    - 查询国家名称及其首都名称都以相同的字母开头的国家名称及其首都，且不能包括国家名称和首都名称完全相同的情况
  - 【参考答案】
    - select
    - name
    - ,capital
    - from world
    - where left(name, 1) = left(capital, 1)
    - and name != capital;
- 【2】SELECT names - SQLZOO第十四题
  - 【题目】
    - 查询首都和名称，其中首都需是国家名称的扩展
    - 例如：答案中应该包括墨西哥城（Mexico City），因为它比墨西哥（Mexico）更长，而不应该将卢森堡（Luxembourg）包括在内，因为首都名与国家名相同
  - 【参考答案】
    - select
    - capital
    - ,name
    - from world
    - where capital like concat('%',name,'%')
    - and capital != name;
- 【3】The JOIN operation - SQLZOO第十三题
  - 【题目】
    - 查询每场比赛，每个球队的得分情况，按照以下格式显示
    - 最后按照举办时间（mdate）、赛事编号（matchid）、队伍1（team1）和队伍2（team2）排序
  - 【参考答案】
    - select
    - ga.mdate
    - ,ga.team1
    - ,sum(case when ga.team1=go.teamid then 1 else 0 end) score1
    - ,ga.team2
    - ,sum(case when ga.team2=go.teamid then 1 else 0 end) score2
    - from game ga
    - left join goal go
    - on ga.id = go.matchid
    - group by ga.mdate,ga.team1,ga.team2
    - order by ga.mdate, go.matchid, ga.team1, ga.team2
- 【4】NSS Tutorial - SQLZOO第六题
  - 【题目】
    - 查询来自'(8) Computer Science' 和'(H) Creative Arts and Design' 专业的学生，在回答问题22（Q22）时，表示强烈同意（A_STRONGLY_AGREE）的各专业人数占比
    - 精确到个位数，不需要百分号
    - 自行阅读题目涉及的表格哦~
  - 【参考答案】
    - select
    - subject
    - , round(sum(A_STRONGLY_AGREE * response) / sum(response),0)
    - from nss
    - WHERE (question='Q22' and subject='(8) Computer Science' )
    - or (question='Q22' and subject = '(H) Creative Arts and Design')
    - group by subject