MYSQL中的函数

数学函数：
-- 1.abs(x);-- 返回x的绝对值
-- 2.ceil(x); -- 返回大于或等于x的最小整数
-- 3.floor(x); -- 返回小于或等于x的最大整数
-- 4.greatest(expr1,expr2,expr3```);-- 返回列表中的最大值
-- 5.least(expr1,expr2,expr3```);--  返回列表中的最小值
-- 6.max(expression); -- 返回字段expression中的最大值
-- 7.min(expression); -- 返回字段expression中的最小值
-- 8.mod(x,y); -- 返回x除以y以后的余数
-- 9.pi(); -- 返回圆周率
-- 10，pow()/power();-- 返回x的y次方
-- 11.rand();//返回0-1的随机数
-- 12.round(x);//返回离x最近的整数(四舍五入)
-- 13.round(x,y);//返回指定位数的小数；(四舍五入)
-- 14.truncate(x,y);//返回数值x保留小数后y位的值



字符串函数：
-- 1.获取字符串字符个数
SELECT char_length('hello');-- 5
SELECT char_length('你好吗');-- 3

-- length取长度，返回的单位是字节
SELECT length('hello'); -- 5
SELECT length('你好吗'); -- 9

-- 2.字符串合并
SELECT concat('hello','world');
SELECT concat(c1,c2) from table_name;

-- 3.指定分隔符进行字符串合并
SELECT concat_ws('-','hello','world');-- hello-world
SELECT concat_ws('-','hello','wang','zi','xuan');-- hello-wang-zi-xuan

-- 4.返回字符串在列表中的位置-- 返回第一次出现的位置
-- 第一个字符是要查找的字符，查找第一个字符在后面字段中的位置，从1开始
SELECT field ('aaa','aaa','bbb','ccc');-- 1
SELECT field ('bbb','aaa','bbb','ccc');-- 2

-- 5.去除字符串左边空格
SELECT ltrim('     aaaa');-- 去除左边的空格
SELECT rtrim('     aaaa     ');-- 去除右边的空格
SELECT trim('      aaaa     ');-- 去除两端空格

-- 6.字符串截取
SELECT mid("helloworld",2,3);-- ell，从第二个字符串开始截取，截取长度为3

-- 7.获取字符串a在字符串中出现的位置
SELECT POSITION('abc'in'helloabcworld');-- 6

-- 8.字符串替换
SELECT REPLACE('helloaaaworld','aaa','bbb');-- hellobbbworld

-- 9.字符串翻转
SELECT reverse('hello');

-- 7.获取字符串a在字符串中出现的位置
SELECT POSITION('abc'in'helloabcworld');-- 6

-- 8.字符串替换
SELECT REPLACE('helloaaaworld','aaa','bbb');-- hellobbbworld

-- 9.字符串翻转
SELECT reverse('hello');

-- 10.返回字符串的后几个字符
SELECT right('hello',3);-- llo,返回最后的三个字符

-- 11.字符串比较
SELECT strcmp('hello','world');

-- 12.字符串截取
SELECT substr('hello',2,3); -- 从第二个字符开始截取3个。-- ell

-- 13.将小写转大写
SELECT ucase('helloWOrld');
SELECT upper('helloWOrld');

-- 14.将大写转为小写
SELECT lcase('HELLOWOELD');
SELECT lower('HELLOWORLD');

-- 窗口函数
-- 1.开窗聚合函数
SELECT 
	dname,
	ename,
	hiredate,
	salary,
	sum(salary) over(partition by dname 
	ORDER BY hiredate rows 
	between unbounded preceding and current row) as c1 # 从开始加到当前行
from employee;

SELECT 
	dname,
	ename,
	hiredate,
	salary,
	sum(salary) over(partition by dname 
	ORDER BY hiredate rows 
	between 3 preceding and current row) as c1 -- 从向上三行(没有3行就不加)加到当前行
from employee;

SELECT 
	dname,
	ename,
	hiredate,
	salary,
	sum(salary) over(partition by dname 
	ORDER BY hiredate rows 
	between 3 preceding and 1 following row) as c1 -- 从向上三行(没有3行就不加)+当前行+向下1行
from employee;

SELECT 
	dname,
	ename,
	hiredate,
	salary,
	sum(salary) over(partition by dname 
	ORDER BY hiredate rows 
	between current row and unbounded following) as c1 -- 从向上三行(没有3行就不加)+当前行+向下1行
from employee;

-- 2.分布函数
SELECT 
	dname,
	ename,
	salary,
	cume_dist() over(order by salary) as rn1,
	cume_dist() over(partition by dept order by salary) as rn1
from employee;


-- 3.前后函数
SELECT 
	dname,
	ename,
	salary,
	hiredate,
	lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as time1,
	lag(hiredate,2) over(partition by dname order by hirdate) as time2
from employee;

SELECT 
	dname,
	ename,
	salary,
	hiredate,
	lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as time1,
	lead(hiredate,2) over(partition by dname order by hirdate) as time2
from employee;

-- 4.头尾函数
SELECT 
	dname,
	ename,
	salary,
	hiredate,
	first_value(salary) over(partition by dname order by hiredate) as first,-- 到目前为止第一个人的薪资
	last_value(salary) over(partition by dname order by hirdate) as last -- 到目前为止最后一个人的薪资
from employee;

-- 5.分组函数
-- 平均分为n组
SELECT 
	dname,
	ename,
	salary,
	hiredate,
	nth_value(salary,2) over(partition by dname order by hiredate) as second_salary,-- 截至到当前排第二
	nth_value(salary,3) over(partition by dname order by hiredate) as third_salary -- 截止到当前排第三
from employee;


-- ，平均分组后，去除每一个部门的第一组员工
SELECT
* 
FROM(
	SELECT 
	dname,
	ename,
	salary,
	hiredate,
	NTILE(3) over(partition by dname order by hiredate) as nt
from employee
)t
where t.nt=1;