mysql——单行函数

#进阶4：常见函数
/*
好处：1.隐藏了实现细节	2.提高了代码的重用性
调用：select 函数名（实参列表） 【from 表】
分类：
	1.单行函数 如 concat length
	2.分组函数 功能：做统计使用，又称为统计函数，聚合函数，组函数
常见函数
	字符函数：length,concat,substr,instr,trim,upper,lower,lpad,rpad,replace
	数学函数：round,ceil,floor,truncate,mod
	日期函数：mow,curdate,curtime,year,month,monthname,day,hour,minute,second,str_to_date,date_format
	其他函数：user,version,datebase
	控制函数：if,case
*/


#一，字符函数

#1.length 获取参数值得字节个数
SELECT LENGTH('john');

SELECT LENGTH('你好john');

SHOW VARIABLES LIKE '%char%';	#显示字符编码


#2.concat 拼接字符串
SELECT CONCAT(`last_name`,'_',`first_name`) 姓名
FROM employees;


#3.upper,lower 更改大小写
SELECT UPPER('john');

SELECT LOWER('JOhn');

#案列：将姓改成大写，名改成小写，然后拼接
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name))
FROM employees;


#4.substr,substring（重载函数） 截取字符，注意索引从1开始

#截取指定索引处后面所有字符
SELECT SUBSTR('阿珍爱上了阿强',6) 输出;

#截取指定长度的字符
SELECT SUBSTR('阿珍爱上了阿强',1,2) 输出；

#案例：姓名中首字母大写，其他字母小写，然后用_拼接
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) 输出
FROM employees;


#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('阿珍爱上了阿强阿强','阿强') 输出；


#6.trim 去掉前后的空格或者字符
SELECT TRIM('         aaa     ') 输出;

SELECT LENGTH(TRIM('         aaa     ')) 输出;

SELECT TRIM('a' FROM 'aaaaaaaaaabbaaaaaaabbbaaaa') 输出;



#7.lpad rpad 用指定字符实现左/右填充指定长度
SELECT LPAD('哈哈', 10, '*') output;

SELECT RPAD('哈哈', 10, 'abc') output;


#8.replace 替换
SELECT REPLACE('阿珍爱上了阿强', '阿强', '阿丽') output;



#二.数学函数

#round 四舍五入
SELECT ROUND(1.65);

SELECT ROUND(1.345,2);

#ceil 向上取整，返回大于等于该参数的最小整数
SELECT CEIL(1.02);

#floor 向下取整
SELECT FLOOR(9.99);

#truncate 截断
SELECT TRUNCATE(1.69999, 1);

#mod 取余
SELECT MOD(10,3);
SELECT 10%3;



#三。日期函数
#now 返回当前的系统日期+时间
SELECT NOW(); 

#curdate 返回当前的日期，不包括时间
SELECT CURDATE();

#curtime 返回当前的时间，不包括日期
SELECT CURTIME();

#可以获取指定的部分
SELECT YEAR(NOW()) 年;

SELECT YEAR('1998-1-1') 年;

SELECT YEAR(`hiredate`) 年
FROM employees;

SELECT MONTH(NOW()) 月;

SELECT MONTHNAME(NOW()) 月;	#英文的月份

SELECT MINUTE(NOW()) 分;


#str_to_date 将字符通过指定格式转化成日期
SELECT STR_TO_DATE('1995-07-22', '%Y-%m-%d') output;

#案列：查询入职日期为1992-4-3的员工信息
SELECT *
FROM employees
WHERE `hiredate` = STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format 将日期转化成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') output;

#案例：查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT last_name 姓名,DATE_FORMAT(`hiredate`,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE `commission_pct` IS NOT NULL;



#其他函数

SELECT VERSION();
SELECT DATABASE();	#查看数据库
SELECT USER();		#查看用户



#五，流程控制函数
#if函数：if else效果
SELECT IF('10>5','大','小') 输出;

SELECT `last_name`,`commission_pct`,IF(`commission_pct` IS NULL,'无','有') 备注；
FROM employees;



#case函数
/*
使用一： switch case 效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1(注意值后没有分号) 或 语句1；
when 常量2 then 要显示的值2(注意值后没有分号) 或 语句2；
.....
else 要显示的值n 或 语句n；
end
*/


/*案例：查询员工的工资，要求，
部门号=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;


/*
使用二： 类似于多重if

case
when 常量1 then 要显示的值1(注意值后没有分号) 或 语句1；
when 常量2 then 要显示的值2(注意值后没有分号) 或 语句2；
.....
else 要显示的值n 或 语句n；
end
*/

/*
#案例：查询员工工资
如果工资>20000，显示为A级别
如果工资>15000，显示为B级别
如果工资>10000，显示为C级别
否则，显示为D级别
*/

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