目录
一、数值函数
1、ROUND
- ?ROUND(x,y)：求参数 x 的四舍五入的值，并保留 y 位小数
-
SELECT ROUND(2.345, 2);
2、abs()
-
返回绝对值函数，例：
-
mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32
-
3、 ceil（x）
????????返回不小于x的最小整数
4、floor(x)
? ? ? ? 返回不大于x的最大整数
mysql> select ceil(10.5),floor(10.1);
+------------+-------------+
| ceil(10.5) | floor(10.1) |
+------------+-------------+
| 11 | 10 |
+------------+-------------+
1 row in set (0.00 sec)5、RAND（）
????????获取随机数，函数产生的数在0-1之间?
mysql> select rand(); # 0到1之间随机的小数
+---------------------+
| rand() |
+---------------------+
| 0.01271833014927649 |
+---------------------+
1 row in set (0.00 sec)
# 获取0~100 之间的随机整数
select ceil(rand()*100);?6、truncate(m,n)
???????? 返回数字m 被截断为n的为小数的值
mysql> SELECT TRUNCATE(1.223,1);
-> 1.2
mysql> SELECT TRUNCATE(1.999,1);
-> 1.9
mysql> SELECT TRUNCATE(1.999,0);
-> 1
mysql> SELECT TRUNCATE(-1.999,1);
-> -1.9
mysql> SELECT TRUNCATE(122,-2);
-> 100
mysql> SELECT TRUNCATE(10.28*100,0);
-> 1028?二、字符串函数
?1、concat
????????字符串拼接，将 s1, s2, ..... , sn 拼接成一个字符串
select concat('abc','def') as title;
select concat(name,'birth is',birthday) from employee;
+----------------------------------+
| concat(name,'birth is',birthday) |
+----------------------------------+
| 张三birth is1975-01-01 |
| 李四birth is1985-01-01 |
| 王五birth is1978-11-11 |
| 赵六birth is1979-01-01 |
+----------------------------------+2、insert
????????替换字符串的函数
????????nsert(str,m,n,new_str) 将字符串str 从下表m位置开始 替换n个 替换成new_str
????????mysql 下表从1 开始
mysql> select insert('linux is so good',1,5,'python');
+-----------------------------------------+
| insert('linux is so good',1,5,'python') |
+-----------------------------------------+
| python is so good |
+-----------------------------------------+3、upper
????????LOWER(str)：将字符串 str 全部转为小写
????????UPPER(str)：将字符串 str 全部转为大写
mysql> select lower('XIAO'),upper('hello');
+---------------+----------------+
| lower('XIAO') | upper('hello') |
+---------------+----------------+
| xiao | HELLO |
+---------------+----------------+
1 row in set (0.00 sec)
select * from 表名 where upper() = ; # 也可以这么用4、left&&right
????????获取指定长度的字符串left（s,n）,right();
????????若n>s的长度则返回s,若n为负数则返回空；
mysql> select left('愿你与我立黄昏',3),right('愿你与我粥可温',3),left()'python is yyds',null);
+---------------------------------+----------------------------------+-----------------------------+
| left('愿你与我立黄昏',3) | right('愿你与我粥可温',3) | left('python is yyds',null) |
+---------------------------------+----------------------------------+-----------------------------+
| 愿你与 | 粥可温 | NULL |
+---------------------------------+----------------------------------+-----------------------------+
1 row in set (0.00 sec)5、lpad&&rpad
????????填充字符串函数lpad(s1,len,s2),rpad(s1,len,s2);
????????如果s1的长度大于len 则返回len长度的s1;l和r的区别为在左和在右；
6、ltrim&&rtrim
????????删除空格函数ltrim(s),rtrim(s),trim(s)即删除字符串左边、右边、两边的空格；
?
?7、replace
????????替换函数replace（s,s1,s2）?
mysql> select replace('linux is so good','linux','python');
+----------------------------------------------+
| replace('linux is so good','linux','python') |
+----------------------------------------------+
| python is so good |
+----------------------------------------------+
1 row in set (0.00 sec)?8、substring (str,m,n）
????????从下标m开始 截取n个 长度的字符串类似于python 字符串的切片
????????如果有头无尾 代表 代表截取到最后
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'三、日期函数
1、curdate()
????????当前的日期
?2、curtime()
????????当前的时间
3、now()
? ? ? ? 当前的日期加时间
4、year()
? ? ? ? 获取年份
5、month()
? ? ? ? 获取月份
6、week()
? ? ? ? 获取第几周
7、hour()
? ? ? ? 获取小时
8、minute()
? ? ? ? 获取分钟
mysql> select year(@test);
+-------------+
| year(@test) |
+-------------+
| 2022 |
+-------------+
1 row in set (0.00 sec)
mysql> select month(@test);
+--------------+
| month(@test) |
+--------------+
| 5 |
+--------------+
1 row in set (0.00 sec)
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
| 21 |
+-------------+
1 row in set (0.00 sec)
mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
| 15 |
+-------------+
1 row in set (0.00 sec)
mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 23 |
+---------------+
1 row in set (0.00 sec)9、date_add
????????DATE_ADD(date, INTERVAL expr type)：返回一个日期/时间值加上一个时间间隔expr后的时间值?
mysql> set @test = now();
Query OK, 0 rows affected (0.00 sec)
mysql> select date_add(@test,interval 3 day);
+--------------------------------+
| date_add(@test,interval 3 day) |
+--------------------------------+
| 2022-05-30 15:11:52 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select date_add(@test,interval -5 day);
+---------------------------------+
| date_add(@test,interval -5 day) |
+---------------------------------+
| 2022-05-22 15:11:52 |
+---------------------------------+
1 row in set (0.00 sec)
select date_add(@test,interval -5 hour);
select date_add(@test,interval -5 minute);
select date_add(@test,interval -5 second);
select date_add(@test,interval -5 microsecond);
select date_add(@test,interval -5 week);
select date_add(@test,interval -5 month);
select date_add(@test,interval -5 quarter);
select date_add(@test,interval -5 year);
mysql> select adddate(curdate(),7);
+----------------------+
| adddate(curdate(),7) |
+----------------------+
| 2022-06-03 |
+----------------------+
1 row in set (0.00 sec)
mysql> select adddate(now(),7);
+---------------------+
| adddate(now(),7) |
+---------------------+
| 2022-06-03 15:15:18 |
+---------------------+
1 row in set (0.00 sec)
mysql> select adddate(now(),-5);
+---------------------+
| adddate(now(),-5) |
+---------------------+
| 2022-05-22 15:15:47 |
+---------------------+
1 row in set (0.00 sec)10、datediff
????????DATEDIFF(date1, date2)：返回起始时间 date1 和结束时间 date2 之间的天数
SELECT DATEDIFF("2022-5-7","2022-7-9");
11、date_format()
? ? ? ? 日期格式化，等同python的 strftime()
mysql> select date_format(now(),'%Y-%m-%d %h:%m:%s');
+----------------------------------------+
| date_format(now(),'%Y-%m-%d %h:%m:%s') |
+----------------------------------------+
| 2022-05-27 03:05:07 |
+----------------------------------------+?四、流程函数
?1、IF?
????????IF(value, t, f)：如果表达式 value?成立，返回结果 t；否则，返回结果 f。
SELECT IF(TRUE,'OK','ERROR'), IF(FALSE,'OK','ERROR');
2、IFNULL
????????IFNULL(value1, value2)：如果 value1?的值不为 NULL，则返回 value1，否则返回 value2。?
SELECT IFNULL('OK','ERROR'), IFNULL(NULL,'ERROR');?
3、CASE WHEN THEN ELSE END
CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE result
END- CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了
- 下面以一个例子说明这个函数：
? ? ? ? 查询 emp 表的员工姓名和员工地址，员工地址在北京和上海的显示为一线城市，除此之外的都显示为二线城市：? -
SELECT name, (CASE workaddress WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END) as '工作地址' FROM emp;
五、窗口函数?
1、基本语法
<窗口函数> OVER ([PARTITION BY <列清单>]
ORDER BY <排序用列清单>)
①partition by子句
????????窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行
②order by子句
????????按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号
?2、排序
? ? ? ? ①rank
? ? ? ? ②row_number
? ? ? ? ③dense_rank
ROW_NUMBER():顺序排序——1、2、3
RANK():并列排序，跳过重复序号——1、1、3
DENSE_RANK():并列排序，不跳过重复序号——1、1、2
select id,classname,score,RANK() over (ORDER BY score desc ) as 排名1, DENSE_RANK() over (ORDER BY score desc ) as 排名2,ROW_NUMBER() over (ORDER BY score desc ) as 排名3 from classroom;
rank dense row
0003 2 90 1 1 1
0002 1 87 2 2 2
0001 1 85 3 3 3
0005 2 85 3 3 4
0007 3 85 3 3 5
0004 1 74 6 4 6
0008 1 66 7 5 7
0006 3 59 8 6 8