MySql（六）常见单行函数

文章目录

单行函数
    字符函数
    数学函数
    日期函数
    其他函数
    流程控制函数

常见函数分类:

1.单行函数：（主要做数据的处理）：concat、length、ifnull…
2.分组函数：（做统计使用；因此又称为统计函数）

 

单行函数

字符函数

1. LENGTH(‘字符’);

作用：返回字符的字节长度；一个英文字符为一个字节；一个汉字按照不同的编码所占据的字节也是不用，例如在utf-8中一个汉字占三个字节，在gbk中一个汉字占两个字节

/**返回4个字节**/
LENGTH('join');
/**返回5个字节(这里的汉字为utf=8编码)**/
LENGTH('哈hi')

2. CONCAT(’’,’’);

作用：连接字符

/**姓和名之间用下划线连接**/
SELECT CONCAT('last_name','_','first_name');

3. UPPER()和LOWER()

作用：字母的大小写转换

/**将姓变大写，将名变为小写**/
SELECT CONCAT( UPPER(last_name),LOWER(first_name)) 姓名
FROM employees;

4. SUBSTR()

作用：用于截取字符

注意：在mysql中索引是从1开始

/**返回的结果为“陆展元”**/
SELECT SUBSTR('李莫愁爱上了陆展元',6) AS result;
/**返回的结果为“李莫愁” **/
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS result;
/**将姓名中首字符大写，其他字符小写然后用_进行拼接 **/
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1),'_',LOWER(SUBSTR(first_name,2)))) result;

5. INSTR

作用：用于返回子串在父串的起始索引，如果找不到返回0

/**返回结果：4**/
SELECT INSTR('老鼠爱大米','大米')；
/**返回结果：4**/
SELECT INSTR('老鼠爱大米，非常爱大米','大米')；
/**返回结果：0**/
SELECT INSTR('老鼠爱大米','小米')；

6. TRIM()

作用：取出字符串中的空格或指定字符

/**此时返回的长度是“张三丰”的字节长度**/
SELECT LENGTH(TRIM('   张三丰    ')) result;
/**删除指定的首字符 'aa'；返回结果：'a张aaa三aaa丰aa'**/
SELECT TRIM(LEADING'aa' FROM 'aaa张aaa三aaa丰aa');
/**删除指定的尾字符 'aa；返回结果：‘aaa张aaa三aaa丰'**/
SELECT TRIM(TRAILING'aa' FROM 'aaa张aaa三aaa丰aa');
/**删除指定的首尾字符 'aa'；返回结果：'a张aaa三aaa丰'**/
SELECT TRIM(BOTH'aa' FROM 'aaa张aaa三aaa丰aa');
/**默认是删除指定的首尾字符  即去除字符串中的a:返回结果:张aaa三aaaaa丰**/
SELECT TRIM('a' FROM 'aaa张aaa三aaaaa丰') result;
/**去除字符串中的aa:返回结果:a张aaa三aaaaa丰**/
SELECT TRIM('aa' FROM 'aaa张aaa三aaaaa丰') result;

7. LPAD()

作用：用指定字符实现指定长度的左填充
注意：下面的数字 5和2分别指的是字符的长度，比如‘西虹市’长度为3；'red’的长度是3;'red西虹市’长度是6；

/**输出结果：“**西虹市”**/
SELECT LPAD('西虹市',5,'*') result;
/**输出结果：“西虹”**/
SELECT LPAD('西虹市',2,'*') result;

同理：RPAD()为右填充

8.REPLACE()

作用：指定字符之间的替换

/**输出结果：老鼠爱小米**/
SELECT REPLACE('老鼠爱大米','大米','小米') result;

数学函数

1. ROUND()

作用：四舍五入

/**2**/
SELECT ROUNND(1.95);
/**-2**/
SELECT ROUNND(-1.95);
/**1.57**/
SELECT ROUND(1.567,2);

2.CEIL()和FLOOR()

作用：CEIL(参数)：向上取整(返回大于等于该参数的最小整数)

FLOOR(参数)：向下取整，返回小于等于该参数苏的最大整数

/**2**/
SELECT CEIL(1.47);
/**-1**/
SELECT CEIL(-1.47);
/**1**/
SELECT FLOOR(1.47);
/**-2**/
SELECT FLOOR(-1.47);

3.TRUNCATE()

作用：截断

/**1.6(保留小数点后1位开始截断)**/
SELECT TRUNCATE(1.69999,1);
/**1.699(保留小数点后1位开始截断)**/
SELECT TRUNCATE(1.69999,3);

4.MOD()

作用：取余

/**1**/
SELECT MOD(10,3);
/**1**/
SELECT 10%3;
/**-1**/
SELECT MOD(-10,3);
/**1**/
SELECT MOD(10,-3);
/**-1**/
SELECT MOD(-10,-3);

日期函数

1.NOW()

作用：返回当前系统日期和时间

2. CURDATE()

作用：返回当前系统日期不包含时间

3. CURTIME()

作用：返回系统当亲时间不包含日期

/**2019-5-17 12:52:23 **/
SELECT NOW();
/**2019-5-17 **/
SELECT CURDATE();
/**12:52:23 **/
SELECT CURTIME();

也可以根据需求获取指定的部分：年（year）、月（month)、日(day)、时(hour)、分(miniute)、秒(second)、

假设当前时间为：2019年5月17号

/**2019**/
SELECT YEAR(NOW()) 年;
/**2019**/
SELECT YEAR('2019-5-17') 年;
/**5**/
SELECT MONTH(NOW()) 月;
/**May**/
SELECT MONTHNAME(NOW()) 月;

4. STR_TO_DATE(‘a’,‘b’)

作用：将日期格式的字符转换成指定的日期类型(a为给定的日期字符；b告诉要解析的日期格式)

大意：首先你得有一个字符，而且这个字符必须是可以能转换成日期的那种（比如：”阿猫阿狗“这个字符串是不可以转换成日期的）；通过给定的日期字符（a）在加上对应的日期格式（b）就可以将字符类型的日期装换成日期类型的数据了

5.DATE_FORMATE()

作用：将日期转换成字符

大意：首先你得有一个日期类型的数据，其次指定你要转换的日期格式的字符串

格式符	功能
%Y	四位的年份
%y	两位的年份
%m	月份（01、02、03、…11、12）
%c	月份（1、2、3、…11、12）
%d	日(01、02、03…)
%H	小时（24小时制）
%h	小时（12小时制）
%i	分钟（00、01、02…59）
%s	秒（00、01、02…59）

   
   
   
    
    
    
    
    
  
  
 

 

 

 

/**将字符串 '1997-4-13'转换成日期类型**/
SELECT STR_TO_DATE('1997-4-13','%Y-%c-%d');
/**将字符串 '4-13 1997'转换成日期类型**/
SELECT STR_TO_DATE('4-13 1997','%c-%d %Y');
/**将当前日期转化成字符串'2019年/4月/13日'的格式显示**/
SELECT DATE_FORMATE(NOW(),'%Y年/%c月/%d日')

其他函数

1.VERSION();

作用：查看版本号

2.DATEBASE()

作用：查看当前数据库

流程控制函数

1. if（表达式1，表达式2，表达式3）

作用：当表达式1满足条件时执行表达式2，否则执行表达式3

/**输出结果大于**/
SELECT IF(3>2,'大于'，'小于');
/

2. case函数

引入：java中switch-case

switch(变量或表达式）{
    case 常量1：语句1；
        break;
    case 常量2：语句2；
        break;
    ...
     default 常量n：语句1；
        break;
}

Mysql中的case使用一：

CASE 要判断的字段或表达式
WHEN 常量1 THEN 要显示的值或者语句（如果要显示的是语句则末尾处要加分号）
WHEN 常量2 THEN 要显示的值或者语句（如果要显示的是语句则末尾处要加分号）
...
ELSE 常量N THEN 要显示的值或者语句（如果要显示的是语句则末尾处要加分号）
END

举个栗子：

/**查询员工的工资    要求：
*    部门编号 = 30 ；显示的工资为1.1倍
*    部门编号 = 40 ；显示的工资为1.2倍
*    部门编号 = 50 ；显示的工资为1.3倍
*    部门编号 = 其他 ；显示的工资为原工资
*/
SELECT department_id,salary 原始工资，
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 40 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

适合等值判断

Mysql中的case使用二：

引入java中的多重if

if(条件1){
    语句1；
}else if(条件2){
    语句2；
}....
 else{
      语句n;
   }

mysql中：

CASE
WHEN 条件1 THEN 要显示的值1或语句（如果要显示的是语句则末尾处要加分号）
WHEN 条件2 THEN 要显示的值2或语句（如果要显示的是语句则末尾处要加分号）
....
ELSE 要显示的值n或语句（如果要显示的是语句则末尾处要加分号）
END

举个栗子：

/**
*查询员工的工资情况    要求：
* 工资级别大于 8000 ;显示 A 级别
* 工资级别大于 5000 ;显示 B 级别
* 工资级别大于 4000 ;显示 C 级别
* 否则显示D级别
*/
SELECT salary,
CASE
WHEN salary > 8000 THEN 'A'
WHEN salary > 5000 THEN 'B'
WHEN salary > 4000 THEN 'C'
ELSE  'D' AS 工资级别
FROM employees;

适合区间判断

进阶练习

/**要求按照员工姓名的首字母排序，并写出姓名的长度**/
SELECT first_name,SUBSTR(first_name,1,1) 首字符,LENGTH(first_name) 姓名长度
FROM employees    
ORDER BY 首字符;
/**做一个查询使之产生的效果：<last_name> earns <salary> monthly but wants <salary*3> Dream Salary
eg: TOM earns 1000 monthly but wants 300 
*/
SELECT CONCAT(last_name,'earns',salary,'monthly but wants',salsary*3) AS Drean Salary
FROM employees;

 

本文原文地址：https://blog.csdn.net/qq_43952245/article/details/90478530