进阶4:常见函数

#进阶4:常见函数
/*
概念：类似于C语言函数，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：
1、隐藏实现细节
2、提高代码重用性
调用：select函数名(实参列表)【from表】；
特点：
①叫什么（功能名）
②干什么（函数功能）
分类：
1、单行函数
如：concat、length、ifnull等
2、分组函数
功能：做统计使用、又称统计函数、聚合函数、组函数
*/
#一、字符函数

#length
SELECT LENGTH('jhon'); 
SELECT LENGTH('张三丰hahaha');
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 SUBSTRING('李莫愁爱上了张若愚' ,6) AS out_put;
SELECT SUBSTRING('李莫愁爱上了张若愚' ,7) AS out_put;
#所应使用两个字符是截取指定数字后的指定长度
SELECT SUBSTR('李莫愁爱上了张若愚',1,3) AS out_put;

#案例：姓名中首字符大写其他字符小写用下划线拼接

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

#5、instr
#返回指定字符串的第一次出现的起始位置

SELECT
	INSTR('杨不悔爱上殷梨亭','殷梨亭') AS out_put
FROM
	employees;

#6、trim 截取字符串前后指定同类型字符

SELECT
	LENGTH(TRIM('   张翠山   ')) AS out_put;
SELECT
	LENGTH(TRIM(' ' FROM '   张翠山   ')) AS out_put;	
SELECT
	TRIM('a' FROM 'aaaaaaaa张翠aaaa山aaaa') AS put_put;

#7、lpad 用指定的字符进行左填充

SELECT LPAD('殷素素',10,'*') AS out_put;
SELECT LPAD('殷素素',2,'*') AS out_put;
#7、rpad 用指定的字符进行右填充
SELECT RPAD('殷素素',10,'*') AS out_put;
SELECT RPAD('殷素素',2,'*') AS out_put;

#8、replace替换

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

#二、数学函数
#1、round 四舍五入
#默认取整

SELECT ROUND(1.43);
#保留小数点后两位
SELECT ROUND(1.6625,2);

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

SELECT CEIL(1.02);
SELECT CEIL(-1.02);

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

SELECT FLOOR(1.02);
SELECT FLOOR(-1.02);
#4、truncate 截断
SELECT TRUNCATE (1.666999,3);

#5、mod 取余
#第一个数为正结果为正，第一个数为符结果为负

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

#三、日期函数
#1、now 返回当前时间

SELECT NOW();

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

SELECT CURDATE();
```sql
#3、curtime 返回当前时间，不包含日期
```sql
SELECT CURTIME();

#4、可以获取指定部分年月日时分秒

SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
#......

#5、str_to_date将字符通过指定格式转换成日期

SELECT STR_TO_DATE('1998-6-9','%Y-%c-%d') out_put;
#案例：查询日期为1994-4-3的员工信息
SELECT 
	*
FROM
	employees
WHERE
	hiredate = '1994-4-3';
SELECT 
	*
FROM
	employees
WHERE
	hiredate = STR_TO_DATE('3-4-1994','%d-%m-%Y');

#6、date_format将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%Y年-%c月-%d日') out_put;
#查询有奖金的员工的员工姓名和入职日期（年月日）
SELECT
	last_name,
	DATE_FORMAT(hiredate,'%Y年%m月%d日')
FROM
	employees
WHERE
	commission_pct IS NOT NULL;

#四、其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

#五、流程控制函数
#1、if函数：if else的效果
#类似于三目运算符

SELECT 
	last_name,
	commission_pct,
	IF(commission_pct IS NULL,'没有奖金','有奖金') 备注
FROM
	employees;

#2、case函数

/*
#①switch case效果
	case 要判断的的字段或表达式
	when 常量1 then 要显示的值1或语句；
	case 要判断的的字段或表达式
	when 常量2 then 要显示的值2或语句；
	....
	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或语句；
case 要判断的的字段或表达式
when 条件2 then 要显示的值2或语句；
....
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 'D级别'
ELSE 'C级别'
END
FROM employees;