mysql常见函数之-单行函数

布满荆棘的人生 2023-03-02 13:18 31阅读 0赞

### 一、常见函数 ###

mysql中的常见函数类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露的方法名  
**好处**：

1、隐藏了实现细节  
2、提高代码的重用性  
**语法**：select 函数名（实参列表）【from表】  
**特点**：  
函数名（叫什么）  
函数功能（干什么）  
功能：做统计使用、又称为统计函数、聚合函数、组函数  
**分类：**  
1、单行函数  
如：concat 、length、ifnull等  
2、分组函数  
如：max min avg sum count

## 单行函数 ##

**字符函数**  
1、length 获取参数值的字节个数

SELECT LENGTH ('emily'); 5

SELECT LENGTH ('张三hahahha'); 13
    utf-8下，一个中文占3个字符，一个英文占1个字符
    SHOW VARIABLES LIKE '%char%';

2、concat 拼接字符串

SELECT CONCAT (last_name,'_',first_name) FROM employees;

3、upper lower  
upper：将字符变大写，lower：将字符变小写

SELECT CONCAT(UPPER('haha'),'_',LOWER('HeLLo')) ;

4、substr 截取从指定索引处后面所有字符  
注：索引是从1开始的

eg:截取从指定索引处后的所有字符

select substr('刘备字玄德',4) output;  玄德

eg:截取从指定索引处开始指定字符长度的字符

SELECT SUBSTR('刘备字玄德',1,2) output;  刘备

eg:姓名中首字符大写，其他字符小写然后用\_拼接显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) output
    FROM employees;

5、instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('我是一只小小小鸟','小') output; 5

6、trim 去除前后空格或者字符

SELECT LENGTH(TRIM ('   张三   ')) AS output;  张三
    SELECT TRIM('a',FROM 'aaaaaa张aaa三aaaa')AS output;    张aaa三
    SELECT TRIM('aa',FROM 'aaaaaaa张aaa三aaaaa')AS output;    a张aaa三a

7、lpad 用指定的字符实现左填充指定长度

select lpad ('诸葛亮',16,'孔明');
    孔明孔明孔明孔明孔明孔明孔诸葛亮

8、rpad用指定的字符实现右填充指定长度

SELECT RPAD ('诸葛亮',16,'孔明');
    诸葛亮孔明孔明孔明孔明孔明孔明孔

9、replace 替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏')AS out_put;
    张无忌爱上了赵敏

**数学函数**  
1、round 四舍五入

SELECT ROUND(1.65);  2 
    SELECT ROUND(1.45);  1
    SELECT ROUND(1.65,1); 1.7
    SELECT ROUND(-1.65);  -2 
    SELECT ROUND(-1.45); -1
    SELECT ROUND(1.55);  -2

2、ceil 向上取整，返回>=该参数的最小整数

select ceil (1.52);     2
    SELECT CEIL (1.002);  2
    SELECT CEIL (1.00);  1
    SELECT CEIL (-1.52); -1
    SELECT CEIL (-1.02);  -1

3、floor向下取整，返回<=该参数的最大整数

SELECT FLOOR (9.99);    9
    SELECT FLOOR (-9.99);  -10

4、truncate 截断

SELECT TRUNCATE(1.659,1);    1.6

5、mod 取余  
mod(a,b) : a-a/b*b  
mod(-10/-3): -10-(10)/(-3)*(-3)

SELECT MOD(-10,-3);  -1
    SELECT MOD(-10,3);  -1
    SELECT MOD(10,-3);  1
    SELECT MOD(10,3);  1

6、rand:获取随机数，返回0-1之间的小数

select  rand();

**日期函数**  
1、now 返回当前系统时间

SELECT NOW();    2020-07-29  17：32:30

2、curdate 返回当前系统日期，不包含时间

SELECT CURDATE();  2020-07-29

3、curtime 返回当前系统时间，不包含日期

select curtime();   17:35:28

4、获取指定的年、月、日、时、分、秒  
YEAR :年  
MONTH:月  
DAY：日  
HOUR：时  
MINUTE:分  
SECOND:秒  
monthname: 以英文形式返回月

select year(now()) 年;  2020年

5、str\_to\_date 将字符通过指定的格式换成日期

SELECT STR_TO_DATE('2020-7-6','%Y-%c-%d')  2020-07-06

eg:查询入职日期为1992-4-3的员工信息

SELECT * FROM employees
    WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');

6、date\_formate 将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');  02年07月29日

eg：查询有奖金的员工名和入职日期为（xx年xx月xx日）

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
    FROM employees
    WHERE commission_pct IS NOT NULL;

7、datediff 返回两个日期相差的天数

SELECT DATEDIFF(NOW(),'1994-7-6');  算一下自己距离今天活了多少天了：9521

**其他函数**  
1、select version(); 查看数据库版本  
2、select database();查看当前数据名  
3、select user(); 查看当前用户 root  
4、password(‘aaa’);返回字符的密码形式  
5、md5(‘字符’); 返回该字符的md5加密形式

**流程控制函数** case  
1、类似java中的switch case  
**case函数情况1**

case:要判断的变量、字段或表达式
    when 常量1 then 要显示的值1或语句
    when 常量2 then 要显示的值2或语句
    else 要显示的值n或语句n
    end

eg:查询员工的工资，要求  
部门号=30，显示的工资为原工资的1.1倍  
部门号=40，要显示的工资为原工资的1.2倍  
部门号=50，要显示的工资为原工资的1.3倍  
其他部门，显示的工资为原工资

SELECT salary 原始工资，department_id,
    CASE department_id
    WHEN 30 THEN salary*1.1
    WHEN 40 THEN salary*1.2
    WHEN 50 THEN salary*1.3
    ELSE salary 
    END AS 新工资
    FROM employees;

**case 函数情况2**  
类似java中的if else if else

case
    when 条件1 then 要显示的值1或者语句
    when 条件2 then 要显示的值2或者语句
    ....
    else 要显示的值n或语句n
    end

eg:查询员工的工资情况  
如果工资>20000,显示A级别  
如果工资>15000,显示B级别  
如果工资>10000,显示C级别  
否则，显示D级别

SELECT salary 
    CASE 
    WHEN salary > 20000 THEN 'A'
    WHEN salary > 15000 THEN 'B'
    WHEN salary > 10000 THEN 'C'
    ELSE 'D'
    END AS 工资级别
    FROM employees;