mysql 函数
访问量:1336
创建时间:2021-04-19
数学函数
| 数学函数 | 作用 |
|---|---|
| abs() | 绝对值 |
| PI() | π |
| PAND() | 返回0-1内的随机值，可以通过提供一个参数(种子) 使RAND()随机数生成器生成一个指定的值。 |
| ROUND(x,y) | 返回参数x的四舍五入的有y位小数的值； |
| MOD(x,y) | 返回x/y的模(余数) |
| ceil(x) | 返回大于等于x的最小整数，同义词ceiling() |
| floor() | 向下取整 |
| truncate(1.999,1) | 截取，不四舍五入 |
mysql> select rand();
+---------------------+
| rand() |
+---------------------+
| 0.17276123368438448 |
+---------------------+
1 row in set (0.20 sec)
mysql> select abs(-0.1),abs(0.1);
+-----------+----------+
| abs(-0.1) | abs(0.1) |
+-----------+----------+
| 0.1 | 0.1 |
+-----------+----------+
1 row in set (0.16 sec)
mysql> select PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.08 sec)
mysql> select ROUND(1.23343,3),round(1.5),round(1.298,-1),round(2.58,0);
+------------------+------------+-----------------+---------------+
| ROUND(1.23343,3) | round(1.5) | round(1.298,-1) | round(2.58,0) |
+------------------+------------+-----------------+---------------+
| 1.233 | 2 | 0 | 3 |
+------------------+------------+-----------------+---------------+
1 row in set (0.06 sec)
mysql> select mod(10,3),5%3,22 mod 5;
+-----------+------+----------+
| mod(10,3) | 5%3 | 22 mod 5 |
+-----------+------+----------+
| 1 | 2 | 2 |
+-----------+------+----------+
1 row in set (0.34 sec)
mysql> select ceil(2);
+---------+
| ceil(2) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
mysql> select ceil(2.4);
+-----------+
| ceil(2.4) |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)
mysql> select ceiling(-2.2);
+---------------+
| ceiling(-2.2) |
+---------------+
| -2 |
+---------------+
1 row in set (0.00 sec)
mysql> select floor(-2.4),floor(1),floor(1.5);
+-------------+----------+------------+
| floor(-2.4) | floor(1) | floor(1.5) |
+-------------+----------+------------+
| -3 | 1 | 1 |
+-------------+----------+------------+
1 row in set (0.38 sec)
mysql> select truncate(1.999,1),truncate(123,-2);
+-------------------+------------------+
| truncate(1.999,1) | truncate(123,-2) |
+-------------------+------------------+
| 1.9 | 100 |
+-------------------+------------------+
1 row in set (0.00 sec)
##获取指定范围的随机值
mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
| 88 | 33 |
+------------------+------------------+
##产生指定范围随机值7到57
mysql> select floor(7+(rand()*50));
+----------------------+
| floor(7+(rand()*50)) |
+----------------------+
| 30 |
+----------------------+
1 row in set (0.00 sec)
聚合函数
每个组函数接收一个参数，默认情况组函数忽略值为null的行，不参与计算，有时会用关键字distinct剔除字段值重复的条数。
| 聚合函数 | 作用 |
|---|---|
| avg(col) | 求平均值 |
| count(col) | 指定列中非NULL值得个数 |
| sum(col) | 指定列的所有值之和 |
| max() | 指定列最大值 |
| min() | 指定列最小值 |
| group_concat() | 函数的值等于属于一个组的指定列的所有制，以逗号隔开，并以字符串表示。 |
案例：
###创建数据
create table salary_tab(
userId int primary key auto_increment,
salary DECIMAL(6,2)
);
insert into salary_tab (salary) values(1000),(2000),(3000),(NULL),(1000);
mysql> select sum(salary) from salary_tab;
+-------------+
| sum(salary) |
+-------------+
| 7000.00 |
+-------------+
1 row in set (0.07 sec)
mysql> select avg(salary) 平均薪资 from salary_tab;
+--------------+
| 平均薪资 |
+--------------+
| 1750.000000 |
+--------------+
1 row in set (0.06 sec)
mysql> select max(salary) 最高薪资 ,min(salary) 最低薪资 from salary_tab;
+--------------+--------------+
| 最高薪资 | 最低薪资 |
+--------------+--------------+
| 3000.00 | 1000.00 |
+--------------+--------------+
mysql> select count(userid) 总记录数 from salary_tab;
+--------------+
| 总记录数 |
+--------------+
| 5 |
+--------------+
1 row in set (0.49 sec)
##统计去重后的数据量
mysql> select count(distinct salary) 总记录数 from salary_tab;
+--------------+
| 总记录数 |
+--------------+
| 3 |
+--------------+
1 row in set (0.06 sec)
mysql> select group_concat(salary) from salary_tab;
+---------------------------------+
| group_concat(salary) |
+---------------------------------+
| 1000.00,2000.00,3000.00,1000.00 |
+---------------------------------+
1 row in set (0.07 sec)
mysql> select group_concat(salary) from salary_tab group by salary;
+----------------------+
| group_concat(salary) |
+----------------------+
| NULL |
| 1000.00,1000.00 |
| 2000.00 |
| 3000.00 |
+----------------------+
4 rows in set (0.05 sec)
###having 字句在group by 之后进行过滤（where条件过滤在group by 之前）
mysql> select salary,count(*) 当前薪资大于2人 from salary_tab group by salary having count(*)>1;
+---------+------------------------+
| salary | 当前薪资大于2人 |
+---------+------------------------+
| 1000.00 | 2 |
+---------+------------------------+
1 row in set (0.07 sec)
mysql> select salary,count(*) from salary_tab where salary>=2000 group by salary having count(*)>=1;
+---------+----------+
| salary | count(*) |
+---------+----------+
| 2000.00 | 1 |
| 3000.00 | 1 |
+---------+----------+
2 rows in set (0.00 sec)
字符串函数
| 函数 | 描述 | 实例 |
|---|---|---|
| ASCII(s) | 返回字符串 s 的第一个字符的 ASCII 码。 | 返回 CustomerName 字段第一个字母的 ASCII 码：SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers; |
| CHAR_LENGTH(s) | 返回字符串 s 的字符数 | 返回字符串 RUNOOB 的字符数 SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString; |
| CHARACTER_LENGTH(s) | 返回字符串 s 的字符数 | 返回字符串 RUNOOB 的字符数 SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString; |
| CONCAT(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 | 合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString; |
| CONCAT_WS(x, s1,s2...sn) | 同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 | 合并多个字符串，并添加分隔符：SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString; |
| FIELD(s,s1,s2...) | 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置 | 返回字符串 c 在列表值中的位置：SELECT FIELD("c", "a", "b", "c", "d", "e"); |
| FIND_IN_SET(s1,s2) | 返回在字符串s2中与s1匹配的字符串的位置 | 返回字符串 c 在指定字符串中的位置：SELECT FIND_IN_SET("c", "a,b,c,d,e"); |
| FORMAT(x,n) | 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。 | 格式化数字 "#,###.##" 形式：SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56 |
| INSERT(s1,x,len,s2) | 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 | 从字符串第一个位置开始的 6 个字符替换为 runoob：SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com |
| LOCATE(s1,s) | 从字符串 s 中获取 s1 的开始位置 | 获取 b 在字符串 abc 中的位置：SELECT LOCATE('st','myteststring'); -- 5返回字符串 abc 中 b 的位置：SELECT LOCATE('b', 'abc') -- 2 |
| LCASE(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：SELECT LCASE('RUNOOB') -- runoob |
| LEFT(s,n) | 返回字符串 s 的前 n 个字符 | 返回字符串 runoob 中的前两个字符：SELECT LEFT('runoob',2) -- ru |
| LOWER(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：SELECT LOWER('RUNOOB') -- runoob |
| LPAD(s1,len,s2) | 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len | 将字符串 xx 填充到 abc 字符串的开始处：SELECT LPAD('abc',5,'xx') -- xxabc |
| LTRIM(s) | 去掉字符串 s 开始处的空格 | 去掉字符串 RUNOOB开始处的空格：SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB |
| MID(s,n,len) | 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len) | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| POSITION(s1 IN s) | 从字符串 s 中获取 s1 的开始位置 | 返回字符串 abc 中 b 的位置：SELECT POSITION('b' in 'abc') -- 2 |
| REPEAT(s,n) | 将字符串 s 重复 n 次 | 将字符串 runoob 重复三次：SELECT REPEAT('runoob',3) -- runoobrunoobrunoob |
| REPLACE(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 | 将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE('abc','a','x') --xbc |
| REVERSE(s) | 将字符串s的顺序反过来 | 将字符串 abc 的顺序反过来：SELECT REVERSE('abc') -- cba |
| RIGHT(s,n) | 返回字符串 s 的后 n 个字符 | 返回字符串 runoob 的后两个字符：SELECT RIGHT('runoob',2) -- ob |
| RPAD(s1,len,s2) | 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len | 将字符串 xx 填充到 abc 字符串的结尾处：SELECT RPAD('abc',5,'xx') -- abcxx |
| RTRIM(s) | 去掉字符串 s 结尾处的空格 | 去掉字符串 RUNOOB 的末尾空格：SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB |
| SPACE(n) | 返回 n 个空格 | 返回 10 个空格：SELECT SPACE(10); |
| STRCMP(s1,s2) | 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 | 比较字符串：SELECT STRCMP("runoob", "runoob"); -- 0 |
| SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| SUBSTRING_INDEX(s, delimiter, number) | 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数，返回第 number 个字符左边的字符串。如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。 | SELECT SUBSTRING_INDEX('ab','',1) -- a SELECT SUBSTRING_INDEX('ab','',-1) -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('abcde','',3),'',-1) -- c |
| TRIM(s) | 去掉字符串 s 开始和结尾处的空格 | 去掉字符串 RUNOOB 的首尾空格：SELECT TRIM(' RUNOOB ') AS TrimmedString; |
| UCASE(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：SELECT UCASE("runoob"); -- RUNOOB |
| UPPER(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：SELECT UPPER("runoob"); -- RUNOOB |
日期和时间函数
| 函数 | 作用 |
|---|---|
| curdate()或current_date() | 返回当前日期 |
| curtime() 或current_time() | 返回当前时间 |
| from_unixtime(时间戳) | 时间戳转日期 |
| unix_timestamp() | 获取当前系统的时间戳 |
| now() | 返回当前日期和时间 |
| week(date) | 返回date是一年第几周 |
| YEAR(date) | 返回日期的年份 |
| HOUR(time) | 返回time的小时 |
| MINUTE(time) | 返回time的分钟值 |
| MONTHNAME(date) | 返回date的月份名 |
| date_formate(date,format) | 返回按fmt格式的date值 |
| DATE_add(date,interval expr type) | 返回一个日期或时间加上一个时间间隔的时间值 |
| datediff(expr,expr2) | 返回起始时间expr和结束时间expr2之间的天数 |
| ### 加解密函数 | 案例 |
|---|---|
| 加解密函数 | 案例 |
|---|---|
| MD5(str) | select md5('hello'); |
| sha(str) | select sha('hello'); |
| sha1(str) | select sha1('hello'); |
| encode decode | mysql8弃用 |
流程控制函数
select case value then result else value end;
select if(1>2,2,3);
mysql> select case 1 when 1 then 'one' when 2 then 'two' else 'more' end;
+------------------------------------------------------------+
| case 1 when 1 then 'one' when 2 then 'two' else 'more' end |
+------------------------------------------------------------+
| one |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select case 2 when 1 then 'one' when 2 then 'two' else 'more' end;
+------------------------------------------------------------+
| case 2 when 1 then 'one' when 2 then 'two' else 'more' end |
+------------------------------------------------------------+
| two |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select case 3 when 1 then 'one' when 2 then 'two' else 'more' end;
+------------------------------------------------------------+
| case 3 when 1 then 'one' when 2 then 'two' else 'more' end |
+------------------------------------------------------------+
| more |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select if(1>2,2,3);
+-------------+
| if(1>2,2,3) |
+-------------+
| 3 |
+-------------+
1 row in set (0.06 sec)
mysql> select if(3>2,2,3);
+-------------+
| if(3>2,2,3) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
###ifnull(expr1,expr2) 如果表达式1 不为null ,返回xepr1,否则返回expr2
mysql> select ifnull(1,0);
+-------------+
| ifnull(1,0) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select ifnull(null,10);
+-----------------+
| ifnull(null,10) |
+-----------------+
| 10 |
+-----------------+
1 row in set (0.00 sec)
mysql> select ifnull(1/0,10);
+----------------+
| ifnull(1/0,10) |
+----------------+
| 10.0000 |
+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> select ifnull(1/0,'yes');
+-------------------+
| ifnull(1/0,'yes') |
+-------------------+
| yes |
+-------------------+
1 row in set, 1 warning (0.00 sec)
格式化函数
| 格式化函数 | 用处 |
|---|---|
| format(x,y) | 把x格式化为以逗号隔开的数字序列，y是结果的小数位数 |
| INET_ATON(ip) | 返回IP 地址的数字表示 |
| INET_NTON(num) | 返回数字所代表的IP地址 |
| TIME_format(time,fmt) | 依照字符串fmt格式化事件time值 |
类型转换函数
- cast(value as type) 就是 cast(xxx as 类型)
- convert(value ,type) 就是convert(xxx,类型)
系统函数
| 系统函数 |
|---|
| select version()查看版本号 |
| select connection_id(); 查看当前用户连接号 |
| show processlist; 查看所有用户连接信息 |
| select database(),schema(); 查看当前使用的数据库 |
| select user(),current_user(),system_user();查看当前登录用户 |
| select charset('abc'),charset(convert('abc' using latin1)),charset(version());查看字符串的字符集 |
| select collation('abc'),collation(convert('abc' using utf8));查看排序方式 |
| select last_insert_id(); 查看最后一个自动生成的列值 |
登陆评论:
使用GITHUB登陆