mysql基础---常见函数

# 4.常见函数

概念：将一组逻辑语句封装在方法体外，对外暴露方法名

优点：隐藏实现细节；提高代码的重用性

调用：select 函数名 （） 【from 表】；

特点：函数名，函数功能

分类：
    单行函数：处理
    
    分组函数：做统计使用，又称统计函数
 

#一，字符函数

#---length   获取参数值的字节个数
USE myemployees;
SELECT LENGTH('john');                       #结果为4
SELECT LENGTH('张三丰john');                 #结果为13
SHOW VARIABLES LIKE '%char%';                #查询数据库编码                         

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

#---upper,lower
SELECT UPPER('john');
SELECT LOWER('jhon');
#实例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(`last_name`),LOWER(`first_name`)) 姓名 FROM employees;

#---substr,sudstring   索引从1开始

#截取从指定索引处后面所有字符
SELECT SUBSTR('年少轻狂',2) out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('吾心安处即吾家',1,3) out_put;

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

#---instr   返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('吾心安处是吾家','安') output;

#---trim   去字符两边的
SELECT TRIM('   wu   ') AS output;
SELECT TRIM('a' FROM 'aaaaaa肖aaaaaa战aaaaaaa') output;

#---lpad   用指定的字符左填充指定填充长度
SELECT LPAD('王胖子',10,'*') output;

#---rpad   用指定的字符实现右填充指定长度
SELECT RPAD('王胖子',10,'h') output;

 

#---replace  替换
SELECT REPLACE('妈妈爱爸爸','爸爸','我') output;

#二：数学函数

#---round   四舍五入
SELECT ROUND('1.45');
SELECT ROUND(1.567,2);     #小数点后保留2位

#---ceil    向上取整，返回>=该参数的最小整数
SELECT CEIL (-1.02);

#---floor   向下取整<=该参数的最大整数
SELECT FLOOR (-10.97);

#---truncate   截断
SELECT TRUNCATE(1.789,2);

#---mod 取余
SELECT MOD(-902,3);

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

#---curdate  返回当前系统日期，不包含日期
SELECT CURDATE();

#---可以获取指定的部分，年，月，日，小时，分钟，秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1999-1-1') 年;
SELECT YEAR(`hiredate`) 年 FROM employees;
SELECT MONTHNAME(NOW()) 月;

#---str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') output;
    #查询入职日期为1992-4-3的员工信息
    SELECT * FROM employees WHERE hiredate='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;
#查询有奖金的员工名和入职名
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 常量值1 then 要显示的值1或语句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 新工资
FROM employees;

#case函数的使用二：类似于多重if

/*
case 
when 条件1 then 要显示的值1或语句1；
when 条件2 then 要显示的值2或语句2；
。。。
else 要显示的值或语句
end
*/

#案例：查询员工的工资情况
/*
如果工资>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 工资级别
FROM employees;