函数

1. 字符函数

lenth：获取参数值的字节个数

select length('john');
select length('张三丰hahaha');

concat：拼接字符串

#例：查询员工名和姓连接成一个字段，并显示为姓名
select concat(last_name,first_name) as 姓名
from employees;

upper、lower：转大小写

select upper('john');
select lower('joHn');

#例：将姓变大写，名变小写，然后拼接
select concat(upper(last_name),lower(first_name)) 姓名
from employees;

substr、substring：截取字符串

注意：索引从 1 开始。

#截取从指定索引处和后面所有字符，输出：陆展元
select substr('李莫愁爱上了陆展元',7) out_put; 

#截取从指定索引处和指定字符长度的字符，输出：李莫愁
select substr('李莫愁爱上了陆展元',1,3) out_put;

#例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) out_put
from employees;

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

select instr('杨不殷八侠悔爱上了殷六侠','殷八侠') as out_put;

trim：去空格或者去指定字符

select length(trim('    张翠山    ')) AS out_put;

select trim('aa' from 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') as out_put;

lpad、rpad：用指定的字符实现左\右填充指定长度

# 输出：**殷素素
select lpad('殷素素',5,'*') as out_put;

# 输出：殷素素**
select rpad('殷素素',5,'*') as out_put;

replace：替换

select replace('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') as out_put;

2. 数学函数

round：四舍五入

# 输出：-2
select round(-1.55);
# 输出：1.57
select round(1.567,2);

ceil：向上取整,返回>=该参数的最小整数

# 输出：-1
select ceil(-1.02);

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

# 输出：-10
select floor(-9.99);

truncate：截断

# 输出：1.6
select truncate(1.69999,1);

mod：取余

# 输出：1
select mod(10,-3);

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

select rand();

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()) 月;  #以英文的方式返回月

str_to_date：将字符通过指定的格式转换成日期

select str_to_date('1998-3-2','%Y-%m-%d') as out_put;

#例：查询入职日期为 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日') as out_put;

#例：查询有奖金的员工名和入职日期(xx月/xx日 xx年)
select last_name,date_format(hiredate,'%m月/%d日 %y年') 入职日期
from employees
where commission_pct is not null;

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

select datediff('2020-01-01','2019-01-01');

4. 其他函数

version：当前数据库服务器的版本

select version();

database：当前打开的数据库

select database();

user：当前用户

select user();

password：返回该字符的密码形式

select password('root');

md5：返回该字符的 md5 加密形式

select md5('root');

5. 流程控制函数

if 函数

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>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资等级
from employees;

6. 分组函数

特点：

1. sum、avg一般用于处理数值型，max、min、count可以处理任何类型
2. 以上分组函数都忽略null值
3. 可以和 distinct 搭配实现去重的运算
4. 一般使用 count(*) 用作统计行数
5. 和分组函数一同查询的字段要求是group by后的字段

max：最大值

#例：求员工最高工资
select max(salary) from employees;

min：最小值

#例：求员工最低工资
select min(salary) from employees;

sum：和

#例：求所有员工工资总和
select sum(salary) from employees;

avg：平均数

#例：求平均工资
select avg(salary) from employees;

count：计算个数

select count(*) from employees;

7. 自定义函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

1、提高代码的重用性 2、简化操作 3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新 函数：有且仅有1 个返回，适合做处理数据后返回一个结果

7.1. 创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

注意： 1.参数列表 包含两部分： 参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错 如果return语句没有放在函数体的最后也不报错，但不建议

return 值; 3.函数体中仅有一句话，则可以省略begin end 4.使用 delimiter语句设置结束标记

7.2. 调用语法

SELECT 函数名(参数列表)

7.3. 案例演示

#1.无参有返回
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义局部变量
	SELECT COUNT(*) INTO c#赋值
	FROM employees;
	RETURN c;
END $

SELECT myf1()$

#2.有参有返回
#案例1：根据员工名，返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;#定义用户变量 
	SELECT salary INTO @sal   #赋值
	FROM employees
	WHERE last_name = empName;
	RETURN @sal;
END $

SELECT myf2('k_ing') $

#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name=deptName;
	RETURN sal;
END $

SELECT myf3('IT')$

#三、查看函数
SHOW CREATE FUNCTION myf3;

#四、删除函数
DROP FUNCTION myf3;

#案例
#一、创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM=num1+num2;
	RETURN SUM;
END $

SELECT test_fun1(1,2)$