【MySql】第八节 MySql常用内置函数

• MySql数据库中的函数与Java中的函数类似，都是完成一定功能的代码的集合。根据函数操作数据行数可将SQL函数分为单行函数和多行函数：
  • 单行函数：单行函数仅对单条数据中的列进行操作并且返回一个结果；
  • 多行函数：多行函数可以操作成组的多条数据，每组返回一个结果，所以多行函数又称之为组函数；
• 以下表为例：
  

常用聚合函数

• COUNT()：统计指定列不为NULL的记录行数，例：

  #统计月薪与绩效之和大于5000元的人数：
  SELECT COUNT(id) FROM employee WHERE (salary+performance)>6000;
  

• MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
• MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算，例：

  #查询最高工资和最低工资：
  SELECT MAX(salary),MIN(salary) FROM employee; 
  

• SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0，例：

  #统计研发部职员总工资
  SELECT SUM(salary) FROM employee WHERE department='研发部';
  

• AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0，例：

  #计算所有员工的平均工资
  SELECT AVG(salary) FROM employee;
  

常用单行字符串函数

• LENGTH(column_name|str)：返回字符串存储长度；
• CHAR_LENGTH(column_name|str)：返回字符串中字符个数，例：

  #计算字符串“String”的存储长度和字符个数
  SELECT LENGTH('字符串'),CHAR_LENGTH('字符串');
  

  结果为：
  
• CONCAT(column_name1|str1, column_name2|str2,…)：将多个字符串首尾相连后返回；
• CONCAT_WS(separator,column_name1|str1, column_name2|str2,…)：将多个字符串按照执行separator进行首尾相连，例：

  #用两种方式将研发部的员工的姓名、性别和月薪打印在同一个字段中，中间用“-”相接
  SELECT CONCAT(name,'-',gender,'-',salary) planA,CONCAT_WS('-',name,gender,salary) planB FROM employee;
  

  结果为：
  
• TRIM([{both | leading | trailing} [remstr] from] str)：返回去掉str源字符串两端、前缀或后缀字符串remstr；不指定both、leading、trailing ，则默认为 both，此时不能使用from关键字；不指定remstr，则去掉str两端的空格，例：

  #去掉“  str  ”两端的空格、去掉“aaastr”左端的“a”、去掉“strabcabc”右边的“abc”
  SELECT TRIM('  str  ') a,TRIM(LEADING 'a' FROM 'aaastr') b,TRIM(TRAILING 'abc' FROM 'strabcabc') c;
  

  结果为：
  
• SUBSTR(str,pos[,len])：从源字符串str中的指定位置pos开始取一个长度为len的字符串并返回，与SUBSTRING相同，例：

  #从“Hello World”中截取出“Hello”放在字段a,截取“World”放在字段b
  SELECT SUBSTR('Hello World',1,5) a,SUBSTRING('Hello World',7) b;
  

  结果为：
  
• REVERSE(str)：将字符串str反转，与Java中StringBuffer中的reverse功能类似，例：

  #将字符串“abcdefg”反转
  SELECT REVERSE('abcdefg');
  

  结果为：
  
• STRCMP(expr1,expr2)：不区分大小写区分两个字符串，相同则返回0；第一个小于第二个返回-1，否则返回1，例：

  #比较"abc"和"ABC"、比较"abc"和"abb"、比较"abc"和"acc"
  SELECT STRCMP('abc','ABC') a,STRCMP('abc','abb') b,STRCMP('abc','acc') c;
  

  结果为：
  
• REPLACE(str, from_str, to_str)：将源字符串str中所有子串form_str(区分大小写)替代成字符串to_str并返回替换后的字符串；

  #将"www.jpc.JPC.com"中的"jpc"替换成"JING"
  SELECT REPLACE('www.jpc.JPC.com','jpc','JING');
  

  结果为：
  

常用单行数值函数

• MOD(x,y)：取x与y的余数，y为除数，x为被除数，例：

  #求3除以10的余数
  SELECT MOD(3,10);
  

  结果为：
  
• ROUND(x[,y])：返回参数x的四舍五入值，保留y位小数；不指定第二个参数，则默认为0，例：

  #对0.618四舍五入保留两位小数，对0.618四舍五入取整
  SELECT ROUND(0.618,2),ROUND(0.618);
  

  结果为：
  
• TRUNCATE(x,y)：返回数字x截断后的结果，保留y位小数，后面的小数不四舍五入而是直接省略，例：

  #将1.618保留两位小数，对1.618保留一位小数
  SELECT TRUNCATE(1.618,2),TRUNCATE(1.618,1);
  

  结果为：
  

常用单行日期函数

• 获取当前的精确时间

  SELECT now();
  

• 获取指定格式的时间

  SELECT DATE_FORMAT(now(),'%Y-%m-%d %h:%i:%s');
  

  注：Y:年、m:月、d:日、h&H:时、i:分、s:秒

单行函数-转换函数

convert(value,type)：将value转换为type类型，type可以是char（字符型）、date（日期型）、time（时间型）、datetime（日期时间型）、 signed（整型） 和decimal（浮点型）类型中的一个;

单行函数-IF/IFNULL

• IF(expr1,expr2,expr3)： expr1为TRUE，返回expr2，否则返回expr3；
• IFNULL(expr1,expr2)：expr1不是NULL，返回expr1，否则返回expr2，例：

  #查询所有员工的姓名、性别、月薪、管理工资(如果为空就打印0)、部门
  #方式一：
  SELECT name,gender,salary,IF(manage IS NULL,0,manage) manage,department FROM employee;
  #方式二：
  SELECT name,gender,salary,IFNULL(manage,0) manage,department FROM employee;
  

  结果为：