【MySQL视频笔记5】mysql函数

版权声明:本文为ywcmoon原创文章,未经允许不得转载。 https://blog.csdn.net/qq_39251267/article/details/80322781

一、数学函数

  • ABS(x) 返回绝对值

  • ACOS() 返回X的反余弦值,即余弦值为X的值。如果X不在-1到1的范围内,则返回NULL。

mysql> SELECT ACOS(1);
        -> 0
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.5707963267949
  • ASIN(X) 返回X的反正弦值,即正弦为X的值。如果X不在-1到1的范围内,则返回NULL。
mysql> SELECT ASIN(0.2);
        -> 0.20135792079033
mysql> SELECT ASIN('foo');

+-------------+
| ASIN('foo') |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+
  • ATAN(X) 返回X的反正切值,即正切值为X的值。
mysql> SELECT ATAN(2);
        -> 1.1071487177941
mysql> SELECT ATAN(-2);
        -> -1.1071487177941
  • ATAN(Y,X), ATAN2(Y,X) 返回Y / X的反正切值,两个参数的符号用于确定结果的象限。
mysql> SELECT ATAN(-2,2);
        -> -0.78539816339745
mysql> SELECT ATAN2(PI(),0);
        -> 1.5707963267949
  • CEIL(X),CEILING(x) 返回大于x的最小整数
mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1

对于精确值数字参数,返回值具有精确值数字类型。
对于字符串或浮点参数,返回值具有浮点类型。

  • CONV(N,from_base,to_base)
    在不同的数字基础之间转换数字。
    返回数字N的字符串表示形式,从基本from_base转换为基本to_base
    如果任何参数为NULL,则返回NULL。
    参数N被解释为整数,但可以指定为整数或字符串。
    最小基数为2,最大基数为36.
    如果from_base为负数,则N被视为有符号数。 否则,N被视为未签名。
    CONV()以64位精度工作。
mysql> SELECT CONV('a',16,2);
        -> '1010'
mysql> SELECT CONV('6E',18,8);
        -> '172'
mysql> SELECT CONV(-17,10,-18);
        -> '-H'
mysql> SELECT CONV(10+'10'+'10'+X'0a',10,10);
        -> '40'
  • COS(x) 返回X的余弦,其中X以弧度给出
mysql> SELECT COS(PI());
        -> -1
  • COT(x)返回X的余切值。
mysql> SELECT COT(12);
        -> -1.5726734063977
mysql> SELECT COT(0);
        -> out-of-range error
  • CRC32(expr)
    计算循环冗余校验值并返回一个32位无符号值。
    如果参数为NULL,则结果为NULL。
    该参数预计是一个字符串,并且(可能的话)如果它不是字符串将会被视为字符串
mysql> SELECT CRC32('MySQL');
        -> 1651825290
mysql> SELECT CRC32('mysql');
        -> 2501908538 
mysql> SELECT CRC32('null')
        -> NULL
  • DEGREES(X) 返回参数X,从弧度转换为度数。
mysql> SELECT DEGREES(PI());
        -> 180
mysql> SELECT DEGREES(PI() / 2);
        -> 90
  • EXP(X)
    返回e的值(自然对数的底数),其值为X的幂
    该函数的反函数是 LOG() (仅使用单个参数)或 LN()
    (e,自然对数函数的底数 lnx, 约2.718281828459045…)
mysql> SELECT EXP(1);
        -> 2.718281828459045
mysql> SELECT EXP(-1);
        ->  0.36787944117144233
mysql> SELECT EXP(0);
        -> 1
  • floor(x) 返回不大于X的最大整数值。
mysql> SELECT FLOOR(1.23), FLOOR(-1.23);
        -> 1, -2

对于精确值数字参数,返回值具有精确值数字类型
对于字符串或浮点参数,返回值具有浮点类型

  • FORMAT(X,D)
    将数字X格式化为像’#,###,###。##’这样的格式,四舍五入为D小数位,并将结果作为字符串返回。
mysql> select format(1.715,2);
+-----------------+
| format(1.715,2) |
+-----------------+
| 1.72            |
+-----------------+

mysql> select format(1.14,1);
+----------------+
| format(1.14,1) |
+----------------+
| 1.1            |
+----------------+
  • HEX(N_or_S)
    该函数可用于获取十进制数或字符串的十六进制表示形式;
    它的表现方式因论据的类型而异。
mysql> select hex(9);
+--------+
| hex(9) |
+--------+
| 9      |
+--------+

mysql> select hex(10);
+---------+
| hex(10) |
+---------+
| A       |
+---------+

mysql> select hex(99999);
+------------+
| hex(99999) |
+------------+
| 1869F      |
+------------+
  • LN(X)
    返回X的自然对数; 即X的基数e对数。
    如果X小于或等于0.0E0,则函数返回NULL,并报告“对于对数无效的参数”警告。
mysql> SELECT LN(1);
        -> 0
mysql> SELECT LN(2);
        -> 0.69314718055995
mysql> SELECT LN(-2);
        -> NULL

该功能与LOG(X)同义。 这个函数的反函数是EXP()函数。

  • LOG(X), LOG(B,X)
    如果用一个参数调用,则此函数返回X的自然对数。
    如果X小于或等于0.0E0,则函数返回NULL,并报告“对于对数无效的参数”警告。

这个函数的反函数(当用一个参数调用时)是EXP()函数。

mysql> select log(1);
+--------+
| log(1) |
+--------+
|      0 |
+--------+

mysql> select log(-1);
+---------+
| log(-1) |
+---------+
|    NULL |
+---------+

如果使用两个参数调用,则此函数将X的对数返回给基数B.(以X为底,B的对数)
如果X小于或等于0,或者B小于或等于1,则返回NULL。

mysql> SELECT LOG(2,65536);
        -> 16
mysql> SELECT LOG(10,100);
        -> 2
mysql> SELECT LOG(1,100);
        -> NULL

LOG(B,X) 相当于 LOG(X) / LOG(B).

  • LOG2(X)
    返回X的基数为2的对数。
    如果X小于或等于0.0E0,则该函数返回NULL,并报告“对于对数无效的参数”警告。
mysql> SELECT LOG2(8);
        -> 3
mysql> SELECT LOG2(-100);
        -> NULL
  • LOG10(X)
    同 log2()
    LOG10(X) 相当于 LOG(10,X)

  • MOD(N,M), N % M, N MOD M 模数操作。 返回N除以M的余数

mysql> SELECT MOD(2, 4);
        -> 2
mysql> SELECT 4 % 2;
        -> 0
mysql> SELECT 21 MOD 4;
        -> 1

该功能可以安全地用于BIGINT值。
MOD()也适用于具有小数部分的值,并返回除法后的精确余数:

mysql> SELECT MOD(34.5,3);
        -> 1.5

MOD(N,0) returns NULL.

  • POW(X,Y),POWER(X,Y) 返回X的值增加到Y的幂。
mysql> SELECT POW(2,2);
        -> 4
mysql> SELECT POW(2,-2);
        -> 0.25
mysql> SELECT POWER(3,3);
        -> 9
  • RADIANS(X)
    返回从度数转换为弧度的参数X. (注意π弧度等于180度。)
mysql> SELECT RADIANS(180);
        -> 3.141592653589793
  • RAND([N]) 返回0 <= v <1.0范围内的随机浮点值v。

要获得范围为i <= R <j的随机整数R,请使用表达式FLOOR(i + RAND()*(j-i))

//获取1 <= R < 10范围内的随机整数:
mysql> SELECT FLOOR(1 + (RAND() * 9));
+-------------------------+
| FLOOR(1 + (RAND() * 9)) |
+-------------------------+
|                       4 |
+-------------------------+

如果指定了整数参数N,则将其用作种子值:
在初始化参数不变的情况下,当语句准备好时,种子会在执行前初始化一次。
使用非常量初始化参数(如列名称),种子将使用每次调用RAND()的值进行初始化。

在ORDER BY或GROUP BY子句中使用带有RAND()值的列可能会产生意外结果,因为对于任一子句,RAND()表达式可以针对同一行进行多次评估,每次都返回不同的结果。

如果目标是以随机顺序检索行,则可以使用如下所示的语句:

SELECT * FROM tbl_name ORDER BY RAND();

要从一组行中选择一个随机样本,请将ORDER BY RAND()与LIMIT结合使用:

SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000;
  • ROUND(X), ROUND(X,D)
    将参数X舍入到D小数位。
    舍入算法取决于X的数据类型。
    如果未指定,则D默认为0。
    D可能是负数,导致值X的小数点左边的D数字变为零。
mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2
mysql> SELECT ROUND(1.298, 1);
        -> 1.3
mysql> SELECT ROUND(1.298, 0);
        -> 1
mysql> SELECT ROUND(23.298, -1);
        -> 20
mysql> select round(233.298,-2);
        -> 200

返回值与第一个参数具有相同的类型(假设它是整数,双精度或十进制)。
这意味着对于整数参数,结果是一个整数(无小数位):

mysql> SELECT ROUND(150.000,2), ROUND(150,2);
+------------------+--------------+
| ROUND(150.000,2) | ROUND(150,2) |
+------------------+--------------+
|           150.00 |          150 |
+------------------+--------------+

根据第一个参数的类型,ROUND()使用以下规则:

对于精确值数字,ROUND()使用“从零开始的半个圆”或“向最近”旋转规则:
小数部分为.5或更大的值向上取整为下一个整数,如果为正或向下 下一个整数如果为负数 (换句话说,它是从零开始舍去的。)如果小数部分小于.5,则数值向下取整为下一个整数,如果是负数,则向下取整。

对于近似值数字,结果取决于C库。 在许多系统上,这意味着ROUND()使用“round to nearest even”规则:将任何小数部分的值四舍五入到最接近的偶数整数。

以下示例显示了精确值和近似值的舍入方式不同:

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+

//25E-1 = 2.5
  • SIGN(X) 根据X是负数,零还是正数,将参数的符号返回为-1,0或1。
mysql> SELECT SIGN(-32);
        -> -1
mysql> SELECT SIGN(0);
        -> 0
mysql> SELECT SIGN(234);
        -> 1
mysql> SELECT SIGN('a');
        -> 0
  • SIN(X) 返回X的正弦值,其中X以弧度给出。
mysql> SELECT SIN(PI());
        -> 1.2246063538224e-16
mysql> SELECT ROUND(SIN(PI()));
        -> 0
  • SQRT(X) 返回非负数X的平方根。
mysql> SELECT SQRT(4);
        -> 2
mysql> SELECT SQRT(20);
        -> 4.4721359549996
mysql> SELECT SQRT(-16);
        -> NULL
  • TAN(X) 返回X的正切值,其中X以弧度给出
mysql> SELECT TAN(PI());
        -> -1.2246063538224e-16
mysql> SELECT TAN(PI()+1);
        -> 1.5574077246549
  • TRUNCATE(X,D)
    返回数字X,截断到D小数位
    如果D为0,结果没有小数点或小数部分
    D可能是负数,导致值X的小数点左边的D数字变为零
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

  • bin(x) 返回x二进制(oct 八进制,hex 十六进制)
mysql> select bin(7);
+--------+
| bin(7) |
+--------+
| 111    |
+--------+
  • LOCATE(substr,str) , LOCATE(substr,str,pos)
    第一个语法返回字符串 str中子字符串substr的第一个出现位置。
    第二个语法返回字符串 str中子字符串substr的第一个出现位置, 起始位置在pos。
    如若substr 不在str中,则返回值为0。
mysql> select locate('世','你好 世界');
+--------------------------+
| locate('世','你好 世界')           |
+--------------------------+
|                        4 |
+--------------------------+

mysql> select locate('a','hello world');
+---------------------------+
| locate('a','hello world') |
+---------------------------+
|                         0 |
+---------------------------+
  • POSITION(substr IN str) 返回字符串第一次出现的位置
mysql> select position('@' in 'ywc@2018.cmo');
+---------------------------------+
| position('@' in 'ywc@2018.cmo') |
+---------------------------------+
|                               4 |
+---------------------------------+
  • right(str,len) 从字符串 str 右边开始,返回 len 个字符。
mysql> select right('hello world',3);
+------------------------+
| right('hello world',3) |
+------------------------+
| rld                    |
+------------------------+
//取出@后缀
mysql> select id,email,right(email,length(email)-position('@' in `email`)) as hz
 from email;
+------+-----------------+-----------+
| id   | email           | hz        |
+------+-----------------+-----------+
|    1 | ywc@qq.com      | qq.com    |
|    2 | moon@sina.cn    | sina.cn   |
|    3 | ymoon@gmail.com | gmail.com |
+------+-----------------+-----------+

所以存储的时候可以把email以@为界,分别存储在两个列中,在想查询后缀的时候可省去以上,优化了数据库查询

二、聚合函数

  • group_concat(col) 返回属于一组的列值连接组合而成的结果
mysql> select goods_id,cat_id from goods where cat_id=24;
+----------+--------+
| goods_id | cat_id |
+----------+--------+
|       69 |     24 |
|       70 |     24 |
|       72 |     24 |
|       64 |     24 |
|       63 |     24 |
|       61 |     24 |
|       62 |     24 |
|       68 |     24 |
+----------+--------+

mysql> select group_concat(goods_id,'') from goods where cat_id=24;
+---------------------------+
| group_concat(goods_id,'') |
+---------------------------+
| 69,70,72,64,63,61,62,68   |
+---------------------------+

三、字符串函数

  • ascii(char) 返回字符串的ASCII码值
mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
|         97 |
+------------+
  • length(str)返回字符串str的字节数
mysql> select length('hello world');
+-----------------------+
| length('hello world') |
+-----------------------+
|                    11 |
+-----------------------+

mysql> select length('你好 世界'); //gbk编码的字符,空格占两个字节
+---------------------+
| length('你好 世界')  |
+---------------------+
|                   9 |
+---------------------+

mysql> select tid,tname,length(tname) from t; //utf8编码的字符占三个字节
+------+----------+---------------+
| tid  | tname    | length(tname) |
+------+----------+---------------+
|    1 | 国安      |             6 |
|    2 | 甲花      |             6 |
|    3 | 公益联队   |            12 |
+------+----------+---------------+
  • char_length(str)返回字符串str的字符数
mysql> select tid,tname,length(tname),char_length(tname) from t;
+------+----------+---------------+--------------------+
| tid  | tname    | length(tname) | char_length(tname) |
+------+----------+---------------+--------------------+
|    1 | 国安         |             6 |                  2 |
|    2 | 甲花        |             6 |                  2 |
|    3 | 公益联队         |            12 |                  4 |
+------+----------+---------------+--------------------+
  • reverse(str) 反转字符串str
mysql> select tid,tname,reverse(tname) from t;
+------+----------+----------------+
| tid  | tname    | reverse(tname) |
+------+----------+----------------+
|    1 | 国安         | 安国               |
|    2 | 甲花        | 花甲               |
|    3 | 公益联队         | 队联益公             |
+------+----------+----------------+

四、日期函数

  • now()获取当前日期和时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-05-16 13:47:51 |
+---------------------+
  • curdate(),current_date()返回当前日期
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2018-05-16 |
+------------+
  • curdate(),current_date()返回当前时间
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 13:50:14  |
+-----------+
  • dayofweek(date) 返回 date 在那周的第几天
mysql> select dayofweek('2018-05-16');
+----------------------+
| dayofweek(curdate()) |
+----------------------+
|                    4 |
+----------------------+

//注意:周日是第一天
  • week(date) 返回日期 date 在当年的第几周
mysql> select week(curdate());
+-----------------+
| week(curdate()) |
+-----------------+
|              19 |
+-----------------+

问:按周统计加班时间

如下表,1~5 的 num 相加,6~12 的 num 相加

mysql> select * from overtime;
+------+------------+
|num   |  date      |
+------+------------+
|    5 | 2018-05-01 |
|    6 | 2018-05-02 |
|    7 | 2018-05-03 |
|    8 | 2018-05-04 |
|    9 | 2018-05-05 |
|   10 | 2018-05-06 |
|   11 | 2018-05-07 |
|   12 | 2018-05-08 |
|   13 | 2018-05-09 |
|   14 | 2018-05-10 |
|   15 | 2018-05-11 |
|   16 | 2018-05-12 |
+------+------------+

答:
mysql> select sum(num) as overtime,week(dt) as wk from overtime group by wk;
+----------+------+
| overtime | wk   |
+----------+------+
|       35 |   17 |
|       91 |   18 |
+----------+------+

五、加密函数

  • md5(str)计算字符串str的md5效验和
mysql> select md5('123');
+----------------------------------+
| md5('123')                       |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+
//该加密算法不可逆

良好的加密:
1.不可逆。 即不可以根据加密后的值逆推原值
2.碰撞性底。 即两个加密后的值重复的可能性底

六、流程控制函数

  • CASE value
    WHEN [compare-value] THEN result
    [WHEN [compare-value] THEN result ...]
    [ELSE result] END

  • CASE WHEN [condition] THEN result
    [WHEN [condition] THEN result ...]
    [ELSE result] END

    在第一个方案的返回结果中, value=compare-value。
    第二个方案的返回结果是第一种情况的真实结果。
    如果没有匹配的结果值,则返回结果为ELSE后的结果,如果没有ELSE 部分,则返回值为 NULL。

mysql> select * from b;
+------+------+
| id   | num  |
+------+------+
| b    |    5 |
| c    |   10 |
| d    |   15 |
| e    | NULL |
+------+------+
4 rows in set (0.00 sec)

mysql> SELECT
    ->  id,
    ->  CASE `num`
    -> WHEN 5 THEN
    ->  '值5'
    -> WHEN 10 THEN
    ->  10
    -> WHEN 15 THEN
    ->  '值15'
    -> ELSE
    ->  '其他值'
    -> END AS nums
    -> FROM
    ->  b;
+------+--------+
| id   | nums   |
+------+--------+
| b    | 值5     |
| c    | 10     |
| d    | 值15    |
| e    | 其他值      |
+------+--------+
4 rows in set, 1 warning (0.01 sec)


mysql> SELECT
    ->  id,
    ->  CASE
    -> WHEN num THEN
    ->  1
    -> ELSE
    ->  0
    -> END AS nums
    -> FROM
    ->  b;
+------+------+
| id   | nums |
+------+------+
| b    |    1 |
| c    |    1 |
| d    |    1 |
| e    |    0 |
+------+------+
4 rows in set (0.00 sec)
  • IF(expr1,expr2,expr3)
    如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。
mysql> SELECT
    ->  id,
    -> IF (num = 5, '值5', '其他') AS num
    -> FROM
    ->  b;
+------+------+
| id   | num  |
+------+------+
| b    | 值5   |
| c    | 其他     |
| d    | 其他     |
| e    | 其他     |
+------+------+
4 rows in set, 1 warning (0.00 sec)
  • IFNULL(expr1,expr2)
    假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。
mysql> SELECT
    ->  id,
    -> IFNULL (num,'expr1为NULL') AS num
    -> FROM
    ->  b;
+------+-------------+
| id   | num         |
+------+-------------+
| b    | 5           |
| c    | 10          |
| d    | 15          |
| e    | expr1为NULL  |
+------+-------------+
4 rows in set (0.00 sec)
  • NULLIF(expr1,expr2)
    如果expr1 = expr2 成立,那么返回值为NULL,否则返回值为 expr1。
mysql> SELECT
    ->  id,
    -> NULLIF (num,5) as num
    -> FROM
    ->  b;
+------+------+
| id   | num  |
+------+------+
| b    | NULL |
| c    |   10 |
| d    |   15 |
| e    | NULL |
+------+------+
4 rows in set (0.00 sec)

七、系统函数

  • user()
    返回用户及所在主机,判断自己身份
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
  • database()
    返回当前正在操作的数据库
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
  • version()
    返回当前数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.53    |
+-----------+
1 row in set (0.03 sec)

使用mysql函数会影响查询速度,应通过表结构等减少函数的使用

阅读更多

没有更多推荐了,返回首页