【page】mysql笔记（四）常用函数--单行函数

进阶4：常见函数

概念：类似于Java中的方法，将一组逻辑语句封装在方法体内，对外暴露方法

好处：1、隐藏了实现细节 2、提高了代码的重用性

调用：select 函数名(实参列表)
【from 表】；

特点：
1：叫什么（函数名）
2：干什么（函数功能）

分类：
1：单行函数
如concat,length,ifnull
包括：字符函数，数学函数，日期函数，其他函数，流程控制函数
2：分组函数
（1）功能：做统计使用

常见函数：
一、单行函数
字符函数
length----获取参数值的字节个数
concat----拼接字符串
substr----截取字符
instr-----返回子串第一次出现的索引，如果找不到，返回0
trim
upper-----字符串大写
lower-----字符串小写
lpad------用指定字符实现左填充指定长度
rpad------用指定字符实现右填充指定长度
replace—替换
数学函数
round-----四舍五入,或保留几位小数
ceil------向上取整
floor-----向下取整
truncate–截断
mod-------取余
日期函数
now
curdate—返回当前系统日期，不包括时间
curtime—返回当前系统时间，不包括日期
year
month
monthname–返回英文的月份
day
hour
minute
second
str_to_date
date_format

其他函数
version
datebase
user

流程控制函数
if
case

一、字符函数

1.length 获取参数值的字节个数

SELECT LENGTH('joey');
SELECT LENGTH('张沛祺hahaha');

2.concat 拼接字符串

SELECT 
	CONCAT(`last_name`,' ',`first_name`) AS 姓名
FROM 
	employees;

3.upper,lower

SELECT UPPER('joey');
SELECT LOWER('ROSS');

实例：将姓变大写，名变小写，然后拼接

SELECT
	CONCAT(LOWER(`first_name`),' ',UPPER(`last_name`)) AS 姓名
FROM
	employees;

4.substr,substring【截取字符】

注意：mysql中索引从1开始

截取从指定索引处后面的所有字符[>=4]

SELECT SUBSTR('李莫愁爱上陆展元',4) AS out_put;

截取从指定索引处指定字符长度的字符[>=1&&<=3]

SELECT SUBSTRING('李莫愁爱上陆展元',1,3);

案例：将名的首字符大写，其他字符小写，然后用_拼接

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

5.instr

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

SELECT
	INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put
FROM
	employees;

6.trim

SELECT
	TRIM('   张翠山    ') AS out_put;

#--------------------------------------

SELECT 
	TRIM('aa' FROM 'aaaaaaa张翠山aaaa') AS out_put;
	

7.lpad,rpad

#用指定字符实现左填充指定长度

SELECT
	LPAD('张',5,'*') AS out_put;

#用指定字符实现右填充指定长度

SELECT
	RPAD('张',10,'*') AS out_put;

8.replace 替换

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

二、数学函数

1.round—四舍五入

SELECT ROUND(1.2);
SELECT ROUND(1.65,1);#第二个参数是保留几位小数

2.ceil—向上取整

返回>=该参数的最大整数

SELECT CEIL(1.2);

3.floor—向下取整

SELECT FLOOR(1.2);

4.truncate–截断

SELECT TRUNCATE(1.2222,2);#第二个参数指的是保留几位小数

5.mod—取余

SELECT MOD(10,3);

三、日期函数

1.now ：返回当前的日期+时间

SELECT NOW();

2.curdate ：返回当前系统日期，不包括时间

SELECT CURDATE();

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

SELECT CURTIME();

4.可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR('2018-9-12');
SELECT MONTHNAME(NOW()) AS 年;#英文月份
SELECT YEAR(`hiredate`) AS 年 FROM employees;

5.str_to_date ：将合法日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y') AS out_put;

6.date_format:将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS 日期;

四、其他函数

1.查看当前版本

SELECT VERSION();

2.查看当前数据库

SELECT DATABASE();

3.查看用户

SELECT USER();

五、流程控制函数

1.if函数：Java中的if else的效果

SELECT IF(10>5,'da','xiao') AS if函数;
#-----------------------------------------
SELECT 
	IF(`commission_pct`IS NULL,'没奖金，呵呵','有奖金，嘿嘿') AS 备注
FROM
	employees;

2.case函数的使用一：Java中的switch case的效果

Java中
switch(变量或表达式){
case 常量:语句1;break;
…
default:语句n;break;
}

MySQL中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或（语句1;）
when 常量2 then 要显示的值2或（语句2;）
when 常量3 then 要显示的值3或（语句3;）
…
else 要显示的值n或（语句n;）
end

SELECT  
	salary AS 源工资,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的使用二：类似于Java中的 多重if

Java中：
if(条件){
语句1;
}else if(条件2){
语句2；
}else{
语句n;
}

MySQL中：
case
when 条件1 then 要显示的值1或(语句1;)
when 条件2 then 要显示的值2或(语句2;)
when 条件3 then 要显示的值1或(语句3;)
…
else 要显示的值n或(语句n;)
end

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