MySQL学习笔记——常见函数

概念

• 类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
• 好处：
  • 隐藏了函数实现的细节
  • 提高了代码的重用性
• 调用格式：select 函数名(参数列表) 【from 表】，其中from 表只有在参数列表中有表中的字段时才会使用
• 分类：
  • 单行函数：如concat、length、ifnull等。
  • 分组函数：做统计使用，又称为统计函数、聚合函数、组函数

单行函数

字符函数

• length(参数)：获取参数值的字节个数（中文在utf-8中占3个字节、gbk中占2个字节）
  
• concat(参数1,参数2,...)：拼接字符串
  
• upper和lower：将字符串中字母全部变为大写或小写（补充：一个函数的返回值可以成为另一个函数的参数）
  
  
• substr或substring：截取 字符串（两个函数的功能一样，只是名字不一样）。注意SQL中的索引是从下标1开始索引的
  • substr(字符串,索引）：截取指定索引处后面的所有字符
    
    
  • substr(字符串,索引,截取长度)：截取从指定索引处开始指定字符长度的字符。
    
• instr(源字符串,目标字符串)：返回目标字符串在源字符串中第一次出现的位置，如果找不到就返回0。
  
• trim(目标字符串 from 源字符串)：去除源字符串中，开头和结尾出现的目标字符串（如果没有填写目标字符串就默认去除空格）
  
• lpad(源字符串, 扩充后字符串的长度, 用于拼接的字符串)：用指定字符串将源字符串填充成指定长度（长度是指填充后字符串的长度），填充时指定字符串添加在源字符串的左侧。如果源字符串的长度就已经超过了扩充后的长度，那么返回的是按扩充后的长度截取的源字符串（截取时抛弃后面多余的字符串）
  
• rpad(源字符串, 扩充后字符串的长度, 用于拼接的字符串)：与lpad类似，只不过是从右侧填充。
  
• replace(源字符串, 目标字符串, 指定字符串)：将源字符串中的目标字符串替换成指定字符串（全部替换）
  

数学函数

• round()：四舍五入
  • round(n)：四舍五入保留整数（先对n取绝对值，四舍五入之后再考虑符号）
    
  • round(n,m)：四舍五入，保留小数点后m位
    
• ceil(n)： 向上取整（返回大于等于n的最小整数）
  
• floor(n)： 向下取整（返回小于等于n的最小整数）
  
• truncate(n,m)：截断（即保留n小数点后m位）
  
• mod(a,b)：取余，相当于a%b。结果的符号与第一个数的符号保持相同（即与a的符号一样）
  

日期函数

• now()：返回当前系统时间
  
  
• curdate()：返回当前日期，不包含时间
  
  
• curtime()：返回当前时间，不包含日期
  
  
• year()：可以获取指定部分的年（参数可以是now()函数，或者日期格式的字符串），同理month()、day()…可以返回指定部分的月、日、小时、分、秒
  
  
  
  
• monthname()：可以返回指定时间月份的名称
  
  
• str_to_date(字符串， 指定格式)：将字符通过指定的格式转换成日期（指定格式是与输入的时间字符串对应的），相当于对输入字符串格式化成标准的日期形式。
  
  
  • 日期格式对照表
    
  • 例如查询入职日期为1992-4-3的员工信息，虽然可以用SELECT * FROM employees WHERE hiredate = '1992-4-3';语句来查询，但是在实际应用中，输入的时间字符串可能不是严格按照时间格式输入的，因此需要对输入字符串进行格式化。
    
    
• date_format(标准日期形式, 指定格式)：将标准日期转换为指定格式的字符串
  
  
  • 查询有奖金的员工的员工名和入职日期（按照xx月/xx日 xx年的格式输出）
    
    

流程控制函数

if函数

• 实现类似于java中"if else"的效果
• 格式：类似于java中的三元运算符，if(条件, 条件成立的表达式, 条件不成立的表达式)
  
  
• 查询所有员工，如果有奖金就输出“有奖金，嘻嘻”，没奖金就输出“没奖金，呵呵”
  
  

case函数

• 使用一：相当于java中“switch case”的效果

  • 格式：

    case 要判断的字段或表达式
    when 常量1 then 要显示的值1或语句1;	#如果是直接是一个值的换，不需要加分号，如果是语句才加分号。
    when 常量2 then 要显示的值2或语句2;
    ...
    else 要显示的值n或语句n;	#相当于java中的defalt，不加的话输出的是null
    end	#结尾一定要记得加end
    

  • 例：查询员工，要求部门号等于30、40、50时，显示的工资分别为1.1、1.2、1.3倍，其他部门则显示原工资。
    
    

  • case语句如果跟在select关键字后面，那么它就是作为一个语句来使用的，比如上面例子中的情况，此时其then后面跟的只能是值，不能是语句。只有当它单独使用的时候，后面才能跟语句。

• 使用二：类似于java中的多重if语句

  • 格式：

    case #case后面不加东西
    when 条件1 then 要显示的值1或语句1;	#同样是值不加引号，语句加引号
    when 条件2 then 要显示的值2或语句2;
    ...
    else 要显示的值n或语句n
    end
    

  • 例：查询员工的工资的情况，如果工资>20000,显示A级别；如果工资>15000,显示B级别；如果工资>10000，显示C级别；否则，显示D级别。
    
    

  • 使用二和使用一的区别就是，使用二中case后面没有东西。使用一更适合等值的判断，而使用二更适合大小的判断。

分组函数

功能和分类

• 功能：用作统计使用（多个值统计过后变为一个值），又称为聚合函数或统计函数或组函数
• 分类：sum求和、avg平均值、max最大值、min最小值、count计算个数

简单的使用

特点

参数支持哪些内容

• sum和avg一般只适用于数值型的参数
• max和min适用于数值型、字符串型（字母顺序）、日期型，即所有类型（遇到null值直接忽略）
• count适用于所有类型，它计算的是值不为null的个数
• 注意：以上分组函数都忽略null值

与distinct搭配使用

• 分组函数都可以与distinct搭配使用
• 计算去重后的工资的和
  
  
• 计算一共有多少种工资
  
  

count函数详细介绍

• 统计表中的行数一般使用SELECT COUNT(*) FROM employees;语句，也可以使用SELECT COUNT(1) FROM employees;来统计行数，其中count中参数可以是任何的常量。
• 效率问题：
  • MYISAM存储引擎下，COUNT(*)的效率高一些。
  • INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，但是比COUNT(字段)要高一些，因为使用COUNT(字段)时会有一个判断过程，来判断字段是否为null。
  • 综上所述，一般使用COUNT(*)来用作统计行数。

和分组函数一同查询的字段有限制

• 和分组函数一同查询的字段要求是group by后的字段