mysql基础篇(函数)

单行函数

1. 字符串函数 （String StringBuilder）

函数	描述
CONCAT(str1,str2,…,strn)	将str1,str2-strn拼接成一个新的字符串
INSERT(str,index,n,newstr)	将字符串str从第index位置开始的n个字符串替换成字符串newstr
LENGTH(str)	获取字符串str的长度
LOWER(str)	将字符串str中的每个字符转换为小写
UPPER(str)	将字符串str中的每个字符转换成大写
LEFT(str,n)	获取字符串str最左边的n个字符
RIGHT(str,n)	获取字符串str最右边的n个字符
LRAD(str,n,pad)	使用字符串pad在str的最左边进行填充,直到长度为n个字符为止
RPAD(str,n,pad)	使用字符串pad在str的最右边进行填充,直到长度为n个字符为止
LTRIM(str)	去除字符串str左侧的空格
RTRIM(str)	去除字符串str右侧的空格
TRIM(str)	去除字符串str左右两侧的空格
REPLACE(str,oldstr,newstr)	用字符串newstr替换字符串str中所有的子字符串oldstr
REVERSE(str)	将字符串str中的字符逆序
STRCMP(str1, str2)	比较字符串str1和str2的大小
SUBSTRING(str,index,n)	获取从字符串str的index位置开始的n个字符

2. 数值函数 （Math）

函数	描述
ABS(num)	返回num的绝对值
CEIL(num)	返回大于num的最小整数（向上取整）
FLOOR(num)	返回小于num的最大整数（向下取整）
MOD(num1, num2)	返回num1/num2的余数（取模）
PI()	返回圆周率的值
POW(num,n)/POWER(num, n)	返回num的n次方
RAND(num)	返回0~1之间的随机数
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值

3. 日期与时间函数

函数	描述
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
SYSDATE()	返回该函数执行时的日期和时间
DAYOFYEAR(date)	返回日期date为一年中的第几天
WEEK(date)/WEEKOFYEAR(date)	返回日期date为一年中的第几周
DATE_FORMAT(date, format)	返回按字符串format格式化后的日期date
DATE_ADD(date, INTERVAL expr unit)	返回date加上一个时间间隔后的新时间值
/ADDDATE(date, INTERVAL expr unit)	返回date加上一个时间间隔后的新时间值
DATE_SUB(date, INTERVAL expr unit)	返回date减去一个时间间隔后的新时间值
/SUBDATE(date, INTERVAL expr unit)	返回date减去一个时间间隔后的新时间值
DATEDIFF(date1, date2)	返回起始日期date1与结束日期date2之间的间隔天数

1.-- 单行函数包含：
2.-- 1.字符串函数
3.select ename,length(ename),substring(ename,2,3) from emp;
4.-- substring字符串截取，2:从字符下标为2开始，3：截取长度3    （下标从1开始）
5.-- 2.数值函数
6.select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; -- dual实际就是一个伪表 
7.select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入;  -- 如果没有where条件的话，from dual可以省略不写
8.select ceil(sal) from emp;
9.select 10/3,10%3,mod(10,3) ;
10.-- 3.日期与时间函数 
11.select * from emp;
12.select curdate(),curtime() ; -- curdate()年月日 curtime()时分秒
13.select now(),sysdate(),sleep(3),now(),sysdate() from dual; -- now(),sysdate() 年月日时分秒
14.
15.insert into emp values (9999,'lili','SALASMAN',7698,now(),1000,null,30);
16.-- now()可以表示年月日时分秒，但是插入数据的时候还是要参照表的结构的

4. 流程函数（ IF ?SWITCH）

间隔类型	描述
IF(condition,t,f)	如果条件condition为真，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为null，则返回value1，否则返回value2
NULLIF(value1, value2)	如果value1等于value2，则返回null，否则返回value1
CASE value WHEN [value1] THEN result1 [WHEN [value2] THEN result2 …] [ELSE result] END	如果value等于value1，则返回result1，···，否则返回result
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 …] [ELSE result] END	如果条件
–	–
condition1为真，则返回result1，···，否则返回result

5.JSON函数

函数	描述
JSON_APPEND()	在JSON文档中追加数据
JSON_INSERT ()	在JSON文档中插入数据
JSON_REPLACE ()	替换JSON文档中的数据
JSON_REMOVE ()	从JSON文档的指定位置移除数据
JSON_CONTAINS()	判断JSON文档中是否包含某个数据
JSON_SEARCH()	查找JSON文档中给定字符串的路径

6.其他函数

函数	描述
DATABASE()	返回当前数据库名
VERSION()	返回当前MySQL的版本号
USER()	返回当前登录的用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA	返回数字代表的IP地址
PASSWORD(str)	实现对字符串str的加密操作
PASSWORD(str)	实现对字符串str的加密操作
FORMAT(num, n)	实现对数字num的格式化操作，保留n位小数
CONVERT(data, type)	实现将数据data转换成type类型的操作

1.-- 4.流程函数
2.-- if相关
3.select empno,ename,sal,if(sal>=2500,'高薪','底薪') as '薪资等级' from emp; -- if-else 双分支结构
4.select empno,ename,sal,comm,sal+ifnull(comm,0) from emp; -- 如果comm是null，那么取值为0 -- 单分支
5.select nullif(1,1),nullif(1,2) from dual; --  如果value1等于value2，则返回null，否则返回value1  
6.-- case相关：
7.-- case等值判断
8.select empno,ename,job,
9.case job 
10. when 'CLERK' then '店员'
11. when 'SALESMAN'  then '销售'
12. when 'MANAGER' then '经理'
13. else '其他'
14.end '岗位',
15.sal from emp;
16.
17.-- case区间判断:
18.select empno,ename,sal,
19.case 
20. when sal<=1000 then 'A'
21. when sal<=2000 then 'B'
22. when sal<=3000 then 'C'
23. else 'D'
24.end '工资等级',
25.deptno from emp;
-- 5.JSON函数  
-- 6.其他函数
select database(),user(),version() from dual;

多行函数

对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数

函数	描述
COUNT()	统计表中记录的数目
SUM()	计算指定字段值的总和
AVG()	计算指定字段值的平均值
MAX()	统计指定字段值的最大值
MIN()	统计指定字段值的最小值

2.-- 多行函数：
3.select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;
4.select * from emp;
5.-- 多行函数自动忽略null值
6.select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;
7.-- max(),min(),count()针对所有类型   sum(),avg() 只针对数值型类型有效
8.select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;
9.
10.
11.-- count --计数   
12.-- 统计表的记录数：方式1：
13.select * from emp;
14.select count(ename) from emp;
15.select count(*) from emp;
16.
17.-- 统计表的记录数：方式2
18.select 1 from dual;
19.select 1 from emp;
20.select count(1) from emp;