这里面仅仅介绍一些常用到的函数,文章中的代码是按照上两篇文章{数据查询语言DQL}和{数据定义DDL与数据操作DML}的基础上继续编写的,下载了资源的同学,可以直接在sql文件直接进行编写。
关于MySql基础学习的链接,如下:
字符串函数
CONCAT(str1,str2,…)
把多个文本字符串合并成一个长字符串(),除NULL外
mysql> select concat('CDA','数据', '分析');
+------------------------------+
| concat('CDA','数据', '分析') |
+------------------------------+
| CDA数据分析 |
+------------------------------+
# 含有空值
mysql> select concat('CDA',null, '分析');
+----------------------------+
| concat('CDA',null, '分析') |
+----------------------------+
| NULL |
+----------------------------+
INSTR(str,substr)
返回子字符串substr在文本字符串str中第一次出现的位置(),找到返回位置(位置从1开始),找不到返回0
mysql> select instr('CDA', 'A');
+-------------------+
| instr('CDA', 'A') |
+-------------------+
| 3 |
+-------------------+
mysql> select instr('数据分析', 'CDA');
+--------------------------+
| instr('数据分析', 'CDA') |
+--------------------------+
| 0 |
+--------------------------+
LEFT(str,len)
返回字符串str的左端len个字符(同样是从1开始)
mysql> select left('CDA数据分析', 3);
+------------------------+
| left('CDA数据分析', 3) |
+------------------------+
| CDA |
+------------------------+
RIGHT(str,len)
返回字符串str的右端len个字符
mysql> select right('CDA数据分析', 4);
+-------------------------+
| right('CDA数据分析', 4)
| +-------------------------+
| 数据分析 |
+-------------------------+
MID(str,pos,len)
返回字符串str的位置pos起len个字符,第三个参数可以省略,代表从 pos位置开始,截取后面的所有。
mysql> select mid('CDA数据分析', 4, 2);
+--------------------------+
| mid('CDA数据分析', 4, 2) |
+--------------------------+
| 数据 |
+--------------------------+
SUBSTRING ( expression, start, length )
截取字符串
- expression:字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。
- start:整数或可以隐式转换为int 的表达式,指定子字符串的开始位置。
- length:整数或可以隐式转换为 int 的表达式,指定子字符串的长度。
mysql> select substring('CDA数据分析',1,3);
+------------------------------+
| substring('CDA数据分析',1,3) |
+------------------------------+
| CDA |
+------------------------------+
LTRIM(str)
返回删除了左空格的字符串str
mysql> select ltrim(' CDA数据分析');
+-------------------------+
| ltrim(' CDA数据分析') |
+-------------------------+
| CDA数据分析 |
+-------------------------+
RTRIM(str)
返回删除了右空格的字符串str
mysql> select rtrim('CDA数据分析 ');
+--------------------------+
| rtrim('CDA数据分析 ') |
+--------------------------+
| CDA数据分析 |
+--------------------------+
TRIM(str)
返回删除了两边空格的字符串str
mysql> select trim(' CDA数据分析 ');
+------------------------------+
| rtrim(' CDA数据分析 ') |
+------------------------------+
| CDA数据分析 |
+------------------------------+
REPLACE(str,from_str,to_str)
用字符串to_str替换字符串str中的子串from_str并返回
mysql> select replace('CDA数据分析', 'CDA', 'cda');
+--------------------------------------+
| replace('CDA数据分析', 'CDA', 'cda') |
+--------------------------------------+
| cda数据分析 |
+--------------------------------------+
REPEAT(str,count)
返回由count个字符串str连成的一个字符串,就是把str重复count次。
mysql> select repeat('CDA', 3);
+------------------+
| repeat('CDA', 3) |
+------------------+
| CDACDACDA |
+------------------+
REVERSE(str)
颠倒字符串str的字符顺序并返回
mysql> select reverse('CDA');
+----------------+
| reverse('CDA') |
+----------------+
| ADC |
+----------------+
UPPER(str)
返回大写的字符串str
mysql> select upper('cda');
+--------------+
| upper('cda') |
+--------------+
| CDA |
+--------------+
LOWER(str)
返回大写的字符串str
mysql> select lower('CDA');
+-----------------+
| lower('CDA') |
+-------------- --+
| cda |
+-----------------+
数学函数
ABS(n)
返回n的绝对值
mysql> select abs(-32);
+----------+
| abs(-32) |
+----------+
| 32 |
+----------+
FLOOR(n)
返回不大于n的最大整数值,相当于下取整
mysql> select floor(1.23);
+-------------+
| floor(1.23) |
+-------------+
| 1 |
+-------------+
CEILING(n)
返回不小于n的最小整数值,相当于上取整
mysql> select ceiling(1.23);
+---------------+
| ceiling(1.23) |
+---------------+
| 2 |
+---------------+
ROUND(n,d)
返回n的四舍五入值,保留d位小数(d的默认值为0)
mysql> select round(1.58);
+-------------+
| round(1.58) |
+-------------+
| 2 |
+-------------+
RAND(n)
返回在范围0到1.0内的随机浮点值(可以使用数字n作为种子,所有n相同的rand函数,返回的值都一样)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.8111234054072978 |
+--------------------+
mysql> select rand(2);
+--------------------+
| rand(2) |
+--------------------+
| 0.6555866465490187 |
+--------------------+
时期时间函数
DATE(date)
返回指定日期/时间表达式的日期部分或将文本转为日期格式
mysql> select date('20200101');
+------------------+
| date('20200101') |
+------------------+
| 2020-01-01 |
+------------------+
WEEK(date)
返回指定日期是一年中的第几周,WEEK(date,n)
表示n为从第n周开始计算。
mysql> select week('2020-01-01');
+--------------------+
| week('2020-01-01') |
+--------------------+
| 0 |
+--------------------+
MONTH(date)
返回指定日期的月份
mysql> select month('2020-01-01');
+---------------------+
| month('2020-01-01') |
+---------------------+
| 1 |
+---------------------+
QUARTER(date)
返回指定日期是一年的第几个季度
mysql> select quarter('2020-01-01');
+-----------------------+
| quarter('2020-01-01') |
+-----------------------+
| 1 |
+-----------------------+
YEAR(date)
返回指定日期的年份(范围在1000到9999)
mysql> select year('20-01-01');
+------------------+
| year('20-01-01') |
+------------------+
| 2020 |
+------------------+
对日期时间进行加减运算
增加 — DATE_ADD(date,interval expr type)
或ADDDATE(date,interval expr type)
减去 — DATE_SUB(date,interval expr type)
或SUBDATE(date,interval expr type)
- date是一个datetime或date值
- expr对date进行加减法的一个表达式字符串
- type指明表达式expr应该如何被解释
mysql> select date_add("2020-01-01",interval 1 day);
+---------------------------------------+
| date_add("2020-01-01",interval 1 day)
| +---------------------------------------+
| 2020-01-02 |
+---------------------------------------+
mysql> select date_sub("2020-01-01", interval 1 day);
+----------------------------------------+
| date_sub("2020-01-01", interval 1 day) |
+----------------------------------------+
| 2019-12-31 |
+----------------------------------------+
DATE_FORMAT(date,format)
根据format字符串格式化date值 ,在format字符串中可用标志符
- %m 月名字(january……december)
- %Y 年, 数字, 4 位
- %y 年, 数字, 2 位
- %a 缩写的星期名字(sun……sat)
- %d 月份中的天数, 数字(00……31)
- %e 月份中的天数, 数字(0……31)
- %m 月, 数字(01……12)
- %c 月, 数字(1……12)
- %b 缩写的月份名字(jan……dec)
- %j 一年中的天数(001……366)
- %h 小时(00……23)
- %k 小时(0……23)
- %i 分钟, 数字(00……59)
- %r 时间,12 小时(hh:mm:ss [ap]m)
- %t 时间,24 小时(hh:mm:ss)
- %s 秒(00……59)
- %p am或pm
- %w 一个星期中的天数(0=sunday ……6=saturday )
- %u 星期(0……52), 这里星期天是星期的第一天
- %% 字符%
ysql> select date_format('20-01-01 12:00:00','%Y-%m-%d');
+---------------------------------------------+
| date_format('20-01-01 12:00:00','%Y-%m-%d') |
+---------------------------------------------+
| 2020-01-01 |
+---------------------------------------------+
DATEDIFF(expr1,expr2)
返回结束日expr1和起始日expr2之间的天数
UNIX_TIMESTAMP()
返回一个unix时间戳(从’1970-01-01 00:00:00’开始的秒数,date默认值为当前时间)
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1564576533 |
+------------------+
mysql> select unix_timestamp('2020-01-01'); +
------------------------------+
| unix_timestamp('2020-01-01') |
+------------------------------+
| 1577808000 | +
------------------------------+
FROM_UNIXTIME(unix_timestamp)
以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回时间戳的值(根据返回值所处上下文是字符
串或数字)
mysql> select from_unixtime(1577808000);
+---------------------------+
| from_unixtime(1577808000) |
+---------------------------+
| 2020-01-01 00:00:00 |
+---------------------------+
mysql> select from_unixtime(1577808000) + 0;
+-------------------------------+
| from_unixtime(1577808000) + 0 |
+-------------------------------+
| 20200101000000 |
+-------------------------------+
分组合并函数
GROUP_CONCAT([distinct] str [order by str asc/desc] [separator])
将group by产生的同一个分组中的值连接起来,返回一个字符串结果。
mysql> select deptno,group_concat(ename)
-> from emp
-> group by deptno;
+--------+--------------------------------------+
| deptno | group_concat(ename) |
+--------+--------------------------------------+
| 10 | clark,king,miller |
| 20 | smith,jones,scott,adams,ford |
| 30 | allen,ward,martin,blake,turner,james |
+--------+--------------------------------------+
逻辑函数
IFNULL(expression, alt_value)
判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值。
mysql> select ifnull(comm,0)
-> from emp;
+----------------+
| ifnull(comm,0) |
+----------------+
| 0 |
| 300 |
| 500 |
| 0 |
| 1400 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+----------------+
IF(expr1,expr2,expr3)
如果expr1的值为true,则返回expr2的值,如果expr1的值为false,则返回expr3的值。
mysql> select ename,sal,if(sal>=3000,'高',if(sal>=1500,'中','低')) 工资级别
-> from emp;
+--------+------+--------------+
| ename | sal | 工资级别 |
+--------+------+--------------+
| smith | 800 | 低 |
| allen | 1600 | 中 |
| ward | 1250 | 低 |
| jones | 2975 | 中 |
| martin | 1250 | 低 |
| blake | 2850 | 中 |
| clark | 2450 | 中 |
| scott | 3000 | 高 |
| king | 5000 | 高 |
| turner | 1500 | 中 |
| adams | 1100 | 低 |
| james | 950 | 低 |
| ford | 3000 | 高 |
| miller | 1300 | 低 |
+--------+------+--------------+
CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4…ELSE expr] END
如果expr1的值为true,则返回expr2的值,如果expr3的值为false,则返回expr4的值…
mysql> select ename,sal,case when sal>=3000 then '高' when sal>=1500 then '中' else '低' end 工资级别
-> from emp;
+--------+------+--------------+
| ename | sal | 工资级别 |
+--------+------+--------------+
| smith | 800 | 低 |
| allen | 1600 | 中 |
| ward | 1250 | 低 |
| jones | 2975 | 中 |
| martin | 1250 | 低 |
| blake | 2850 | 中 |
| clark | 2450 | 中 |
| scott | 3000 | 高 |
| king | 5000 | 高 |
| turner | 1500 | 中 |
| adams | 1100 | 低 |
| james | 950 | 低 |
| ford | 3000 | 高 |
| miller | 1300 | 低 |
+--------+------+--------------+
开窗函数
开窗函数是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,
- 有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口。
- 有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。
开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [< 细分窗口>])
开窗函数的本质还是聚合运算,只不过它更具灵活性,它对数据的每一行,都使用与该行相关的行进行计算并返回计算结果。
开窗函数和普通聚合函数的区别:
- 聚合函数是将多条记录聚合为一条;而开窗函数是每条记录都会执行,有几条记录执行完还是几条。
- 聚合函数也可以用于开窗函数中。
开窗函数的一个概念是当前行,当前行属于某个窗口,窗口由over
关键字来指定函数执行的窗口范围,如果后面括号中什么都不写,则意味着窗口包含满足where条件的所有行,开窗函数基于所有行进行计算;如果不为空,则有三个参数来设置窗口:
- partition by子句:按照指定字段进行分区,两个分区由边界分隔,开窗函数在不同的分区内分别执行,在跨越分区边界时重新初始化。
- order by子句:按照指定字段进行排序,开窗函数将按照排序后的记录顺序进行编号。可以和
partition by
子句配合使用,也可以单独使用。 - frame子句:当前分区的一个子集,用来定义子集的规则,通常用来作为滑动窗口使用。
## 没有参数
mysql> select avg(sal) 所有员工平均工资 from emp;
+------------------+
| 所有员工平均工资 |
+------------------+
| 2073.2143 |
+------------------+
mysql> select avg(sal) over() 所有员工平均工资 from emp;
+-------+
|所有员 工平均工资 |
+-------+ |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
| 2073.2143 |
+-------+
## 第一个参数
ysql> select deptno,avg(sal) 部门平均工资 from emp group by deptno;
+--------+--------------+
| deptno | 部门平均工资 |
+--------+--------------+
| 10 | 2916.6667 |
| 20 | 2175.0000 |
| 30 | 1566.6667 |
+--------+--------------+
mysql> select *,avg(sal) over(partition by deptno) 部门平均工资 from emp;
+-------+--------+-----------+------+------------+------+------+--------+------- -------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | 部门平 均工资 |
+-------+--------+-----------+------+------------+------+------+--------+------- -------+
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450 | NULL | 10 | 2916.6667 |
| 7839 | king | persident | NULL | 1981-11-17 | 5000 | NULL | 10 | 2916.6667 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 | 2916.6667 |
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | NULL | 20 | 2175.0000 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975 | NULL | 20 | 2175.0000 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000 | NULL | 20 | 2175.0000 |
| 7876 | adams | clerk | 7788 | 1987-05-23 | 1100 | NULL | 20 | 2175.0000 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000 | NULL | 20 | 2175.0000 |
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | 1566.6667 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 | 1566.6667 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 1566.6667 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | 1566.6667 |
| 7844 | turner | salesman | 7698 | 1981-09-08 | 1500 | 0 | 30 | 1566.6667 |
| 7900 | james | clerk | 7698 | 1981-12-03 | 950 | NULL | 30 | 1566.6667 |
+-------+--------+-----------+------+------------+------+------+--------+------- -------+
# 带第二个参数
mysql> select *,avg(sal) over(partition by deptno order by hiredate) 部门平均工资 from emp;
+-------+--------+-----------+------+------------+------+------+--------+------- -------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | 部门平 均工资 |
+-------+--------+-----------+------+------------+------+------+--------+------- -------+
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450 | NULL | 10 | 2450.0000 |
| 7839 | king | persident | NULL | 1981-11-17 | 5000 | NULL | 10 | 3725.0000 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 | 2916.6667 |
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | NULL | 20 | 800.0000 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975 | NULL | 20 | 1887.5000 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000 | NULL | 20 | 2258.3333 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000 | NULL | 20 | 2443.7500 |
| 7876 | adams | clerk | 7788 | 1987-05-23 | 1100 | NULL | 20 | 2175.0000 |
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | 1600.0000 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 | 1425.0000 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | 1900.0000 |
| 7844 | turner | salesman | 7698 | 1981-09-08 | 1500 | 0 | 30 | 1800.0000 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 1690.0000 |
| 7900 | james | clerk | 7698 | 1981-12-03 | 950 | NULL | 30 | 1566.6667 |
+-------+--------+-----------+------+------------+------+------+--------+------- -------+
对于滑动窗口的范围指定,通常使用 between frame_start and frame_end
语法来表示行范围,frame_start
和frame_end
可以支持如下关键字,来确定不同的动态行记录:
- current row 边界是当前行,一般和其他范围关键字一起使用
- unbounded preceding 边界是分区中的第一行
- unbounded following 边界是分区中的最后一行
- expr preceding 边界是当前行减去expr的值
- expr following 边界是当前行加上expr的值
比如,下面都是合法的范围:
rows between 1 preceding and 1 following #窗口范围是当前行、前一行、后一行一共三行记录。
rows unbounded preceding # 窗口范围是当前行到分区中的最后一行。
rows between unbounded preceding and unbounded following #窗口范围是当前分区中所有行, 等同于不写。
mysql> select *,avg(sal) over(partition by deptno order by hiredate rows between 1 preceding and 1 following) 部门平均工资 from emp;
+-------+--------+-----------+------+------------+------+------+--------+------- -------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | 部门平 均工资 |
+-------+--------+-----------+------+------------+------+------+--------+------- -------+
| 7782 | clark | manager | 7839 | 1981-06-09 | 2450 | NULL | 10 | 3725.0000 |
| 7839 | king | persident | NULL | 1981-11-17 | 5000 | NULL | 10 | 2916.6667 |
| 7934 | miller | clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 | 3150.0000 |
| 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | NULL | 20 | 1887.5000 |
| 7566 | jones | manager | 7839 | 1981-04-02 | 2975 | NULL | 20 | 2258.3333 |
| 7902 | ford | analyst | 7566 | 1981-12-03 | 3000 | NULL | 20 | 2991.6667 |
| 7788 | scott | analyst | 7566 | 1987-04-19 | 3000 | NULL | 20 | 2366.6667 |
| 7876 | adams | clerk | 7788 | 1987-05-23 | 1100 | NULL | 20 | 2050.0000 |
| 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | 1425.0000 |
| 7521 | ward | salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 | 1900.0000 |
| 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | 1866.6667 |
| 7844 | turner | salesman | 7698 | 1981-09-08 | 1500 | 0 | 30 | 1866.6667 |
| 7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 1233.3333 |
| 7900 | james | clerk | 7698 | 1981-12-03 | 950 | NULL | 30 | 1100.0000 |
+-------+--------+-----------+------+------------+------+------+--------+------- -------+
序号函数
row_number()
显示分区中不重复不间断的序号dense_rank()
显示分区中重复不间断的序号rank()
显示分区中重复间断的序号
select * ,
row_number() over(partition by deptno order by sal desc) as 排名1,
dense_rank() over(partition by deptno order by sal desc) as 排名2,
rank() over(partition by deptno order by sal desc) as 排名3
from emp;
运行结果: