新手入门MySQL——篇5-CSDN博客

MySQL函数

1——聚合函数

group_concat( [distinct] 字段名 [order by 排序字段 asc/desc] [separator ‘分隔符’] )

select group_concat(name) from laoban;
select group_concat(name separator ';') from laoban;
select dept_id, group_concat(ename separator ';') from emp3  group by dept_id;
select dept_id, group_concat(ename  order by age separator ';') from emp3  group by dept_id;

2——数学函数

select abs(-10);
select floor(1.3);
select greatest(1,3,6);
select mod(5,2);
select power(2,4);
select rand() * 10;
select round(52.68276,3);
select truncate(34.27664,3);

3——字符串函数

select char_length('euy788');   -- 字符个数
select character_length('hello python');
select length('hello world');   -- 取长度
select concat('hello', '2022'); -- 连接
select concat_ws('-', 'hello', '2022_04_08');  -- 指定连接形式
select field('aa', 'ttk', 'bb', 'aac', 'aa');  -- 查找第一个在后面首次出现位置,1开始;没有返回0
select ltrim(' shjggh');                       -- 去除左边空格
select rtrim('hgg  ');
select trim('  www ');
select mid('hello2022', 3, 2);             -- 第三个开始,截取2个
select position('amp' in 'ahtjampheamp');  -- 出现位置
select replace('abcdaas', 'aa', 'pp');
select reverse('hello2022');
select mid('hello2022', 3, 2);             -- 第三个开始,截取2个
select position('amp' in 'ahtjampheamp');  -- 出现位置
select replace('abcdaas', 'aa', 'pp');
select reverse('hello2022');
select right('hamburgers', 3);    			-- 最后3个字符
select strcmp('book', 'blood');   			-- 比较大小,字段顺序
select substr('hello-python3', 2, 3);		-- 截取,ell
select substring('hello-python3', 2, 3);
select ucase('hello1');               -- 变大写
select upper('bananas');              -- 变大写
select lower('So FAR so Good');       -- 变小写
select lcase('WHat ArE You doing');	  -- 变小写

4——日期函数

select unix_timestamp();                      -- 返回毫秒数1649430187
select unix_timestamp('2022-04-08 23:03:21');	-- 转为毫秒数
select from_unixtime(1602109037, '%Y-%m-%d %h:%i:%s');
select curdate();      -- 年月日
select current_date(); -- 年月日
select current_time(); -- 时分秒
select curtime();      -- 时分秒
select current_timestamp();  -- 年月日 时分秒
select date('2022-09-20');   -- 字符串中获取年月日
select datediff('2022-09-01', '2018-09-01'); -- 1461
select timediff('19:19:30', '16:26:51');     -- 02:52:39
select date_format('2022-02-19 2:26:59', '%Y-%m-%d %H:%i:%s');  -- 2022-02-19 02:26:59
select str_to_date('2022-02-19 2:26:59', '%Y-%m-%d %H:%i:%s');  -- 2022-02-19 02:26:59
select date_sub('2022-01-10', interval 2 month) -- 日期退后
select date_add('2022-03-10', interval 3 day)   -- 日期超前
select extract(day  from '2021-09-18'); -- 18
select extract(year from '2021-09-18'); -- 2021
select last_day('2022-10-23');      -- 所在月的最后一天
select makedate('2022-09-10', 66);  -- 所在年的第66天
select year('2022-08-08 12:25:48');
select month('2022-08-08 12:25:48');
select minute('2022-08-08 12:25:48');
select quarter('2022-08-08 12:25:48');
select monthname('2022-09-09 18:09:29');  -- September
select dayname('2022-09-09 18:09:29');    -- Friday
select dayofmonth('2022-09-09 18:09:29'); -- 9
select dayofweek('2022-09-09 18:09:29');  -- 6表示星期五
select dayofyear('2022-09-09 18:09:29');  -- 252
select week('2017-09-03 18:09:29');       -- 36
select week('2032-07-09 18:09:29');       -- 27
select week('2012-09-09 18:09:29');       -- 37
select yearweek('2022-08-10');            -- 202232
select now();                             -- 2022-04-08 23:36:30

5——控制流函数

if(表达式, value1, value2)
ifnull(value1, value2)
isnull(表达式)
nullif(表达式1, 表达式2)

select *, if(age>38, 'yes', 'no') flag  from emp3;
select ename, ifnull(age,0) age_flag from emp3;
select isnull(7);     -- 0
select isnull(null);  -- 1
select isnull(null);  -- 1
select nullif(12, 12); -- null表示相等
select nullif(12, 13); -- 12不等,返回第一个值

6——case when 函数

select  -- [简单使用1]
	case 5 
		when 1 then 'hello'
		when 2 then 'hai'
		else 'no'
end as info;

select   -- [简单使用2]
	case  
		when 1 > 2 then 'hello'
		when 2 > 1 then 'hai'
		else 'no'
end as info;

select name,  -- [案例使用3]
case manager_id
	when 1 then 'good'
	when 2 then 'ok'
	when 3 then 'wa'
	else 'nonono'
end as 'label'
from laoban;

7——窗口函数

窗口函数总览

窗口函数名 (参数) over(
partition by ...
order by     ...
)

select ename,  -- [按照部门分组,年龄排序]
row_number() over(partition by dept_id order by age) as cn1,
rank()       over(partition by dept_id order by age) as cn2,
dense_rank() over(partition by dept_id order by age rows between current and unbounded following ) as cn3  -- 当前行——最后
from emp3;

cume_dist()  查询小于等于当前薪资的比例
percent_rank()  rank()-1 / rows-1 rows表示总行数

select ename,
cume_dist() over(order by salary) as cn1,
cume_dist() over(partiton by dept order by salary) as cn2
from employee;

selece ename, hiredate, salary
lag(hiredate, 1, '2022-01-01') over(partition by dname order by hiredate) as time1,  前1行的数据
lag(hiredate, 2) 			   over(partition by dname order by hiredate) as time2   前2行的数据
lead(hiredate, 2) 			   over(partition by dname order by hiredate) as time2   后2行的数据
from employee;

select enmae, hiredate,salary,
first_value(salary) over(partition by hiredate dname order by hiredate ) as first,  最早入职
last_value(salary) over(partition by hiredate dname order by hiredate ) as last    最晚入职
from employee;

select ename,hiredate,salary,
nth_value(salary, 2) over(partition by dname order by hiredate) as second,  -- 目前薪资排名第2的那个员工的信息
nth_value(salary, 3) over(partition by dname order by hiredate) as third    -- 目前薪资排名第3的那个员工的信息
from employee;

select * from  -- 取出每个部门的第一组员工
(
select ename, hiredate,salary,
ntile(3) over(partition by dname order by hiredate) as rn from elmployee) t
where t.rn = 1;