SQL基础（廿三）--- 数学函数

求绝对值：

mysql> select fweight-50, abs(fweight-50), abs(-3.6) from t_person;
+------------+-----------------+-----------+
| fweight-50 | abs(fweight-50) | abs(-3.6) |
+------------+-----------------+-----------+
| 6.67       | 6.67            | 3.6       |
| -13.83     | 13.83           | 3.6       |
| -9.67      | 9.67            | 3.6       |
| -3.77      | 3.77            | 3.6       |
| -1.32      | 1.32            | 3.6       |
| 16.67      | 16.67           | 3.6       |
| 1.28       | 1.28            | 3.6       |
| 10.32      | 10.32           | 3.6       |
+------------+-----------------+-----------+
8 rows in set

求指数：

mysql> select fweight,power(fweight, 0.5), power(fweight,2),power(fweight,3), power(fweight,4) from t_person;
+---------+---------------------+--------------------+--------------------+--------------------+
| fweight | power(fweight, 0.5) | power(fweight,2)   | power(fweight,3)   | power(fweight,4)   |
+---------+---------------------+--------------------+--------------------+--------------------+
| 56.67   |   7.527947927556354 | 3211.4889000000003 |      181995.075963 |  10313660.95482321 |
| 36.17   |   6.014149981501958 |          1308.2689 | 47320.086113000005 | 1711567.5147072102 |
| 40.33   |   6.350590523722971 | 1626.5088999999998 |  65597.10393699999 | 2645531.2017792095 |
| 46.23   |    6.79926466612383 | 2137.2128999999995 |  98803.35236699998 |  4567678.979926409 |
| 48.68   |   6.977105417004963 |          2369.7424 |      115359.060032 |   5615679.04235776 |
| 66.67   |   8.165169930871004 |          4444.8889 |      296340.742963 |  19757037.33334321 |
| 51.28   |   7.161005515987263 | 2629.6384000000003 |      134847.857152 |   6914998.11475456 |
| 60.32   |   7.766595135579039 |          3638.5024 |      219474.464768 |  13238699.71480576 |
+---------+---------------------+--------------------+--------------------+--------------------+
8 rows in set

求平方根：

mysql> select fweight, sqrt(fweight) from t_person;
+---------+-------------------+
| fweight | sqrt(fweight)     |
+---------+-------------------+
| 56.67   | 7.527947927556354 |
| 36.17   | 6.014149981501958 |
| 40.33   | 6.350590523722971 |
| 46.23   |  6.79926466612383 |
| 48.68   | 6.977105417004963 |
| 66.67   | 8.165169930871004 |
| 51.28   | 7.161005515987263 |
| 60.32   | 7.766595135579039 |
+---------+-------------------+
8 rows in set

求随机数：

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.8065719788955263 |
+--------------------+
1 row in set

舍入到最小整数：

mysql> select fname, fweight, floor(fweight), floor(fweight*-1)
    -> from t_person;
+-----------+---------+----------------+-------------------+
| fname     | fweight | floor(fweight) | floor(fweight*-1) |
+-----------+---------+----------------+-------------------+
| Tom       | 56.67   |             56 |               -57 |
| Jim       | 36.17   |             36 |               -37 |
| Lily      | 40.33   |             40 |               -41 |
| Kelly     | 46.23   |             46 |               -47 |
| Sam       | 48.68   |             48 |               -49 |
| Kerry     | 66.67   |             66 |               -67 |
| Smith     | 51.28   |             51 |               -52 |
| BillGates | 60.32   |             60 |               -61 |
+-----------+---------+----------------+-------------------+
8 rows in set

四舍五入：

mysql> select fname, fweight, round(fweight), round(fweight*-1) from t_person;
+-----------+---------+----------------+-------------------+
| fname     | fweight | round(fweight) | round(fweight*-1) |
+-----------+---------+----------------+-------------------+
| Tom       | 56.67   | 57             | -57               |
| Jim       | 36.17   | 36             | -36               |
| Lily      | 40.33   | 40             | -40               |
| Kelly     | 46.23   | 46             | -46               |
| Sam       | 48.68   | 49             | -49               |
| Kerry     | 66.67   | 67             | -67               |
| Smith     | 51.28   | 51             | -51               |
| BillGates | 60.32   | 60             | -60               |
+-----------+---------+----------------+-------------------+
8 rows in set

mysql> select fname, fweight, round(fweight,1), round(fweight*-1,0),round(fweight,-1) from t_person;
+-----------+---------+------------------+---------------------+-------------------+
| fname     | fweight | round(fweight,1) | round(fweight*-1,0) | round(fweight,-1) |
+-----------+---------+------------------+---------------------+-------------------+
| Tom       | 56.67   | 56.7             | -57                 | 60                |
| Jim       | 36.17   | 36.2             | -36                 | 40                |
| Lily      | 40.33   | 40.3             | -40                 | 40                |
| Kelly     | 46.23   | 46.2             | -46                 | 50                |
| Sam       | 48.68   | 48.7             | -49                 | 50                |
| Kerry     | 66.67   | 66.7             | -67                 | 70                |
| Smith     | 51.28   | 51.3             | -51                 | 50                |
| BillGates | 60.32   | 60.3             | -60                 | 60                |
+-----------+---------+------------------+---------------------+-------------------+
8 rows in set

求正弦值：

mysql> select fname,fweight, sin(fweight) from t_person;
+-----------+---------+---------------------+
| fname     | fweight | sin(fweight)        |
+-----------+---------+---------------------+
| Tom       | 56.67   | 0.12103475545596079 |
| Jim       | 36.17   | -0.9991313277030088 |
| Lily      | 40.33   |  0.4887919712841254 |
| Kelly     | 46.23   |  0.7795141519117128 |
| Sam       | 48.68   | -0.9998921607216601 |
| Kerry     | 66.67   | -0.6415784319099757 |
| Smith     | 51.28   |  0.8492258987538737 |
| BillGates | 60.32   |  -0.588934312893273 |
+-----------+---------+---------------------+
8 rows in set

求余弦值：

mysql> select fname, fweight,cos(fweight) from t_person;
+-----------+---------+-----------------------+
| fname     | fweight | cos(fweight)          |
+-----------+---------+-----------------------+
| Tom       | 56.67   |    0.9926482700190011 |
| Jim       | 36.17   |   0.04167241296616697 |
| Lily      | 40.33   |   -0.8724003718523845 |
| Kelly     | 46.23   |   -0.6263846158466563 |
| Sam       | 48.68   | -0.014685602724090054 |
| Kerry     | 66.67   |   -0.7670574396405635 |
| Smith     | 51.28   |    0.5280297083362597 |
| BillGates | 60.32   |   -0.8081809049321373 |
+-----------+---------+-----------------------+
8 rows in set

求反正弦值：

mysql> select fname,fweight,asin(1/fweight) from t_person;
+-----------+---------+----------------------+
| fname     | fweight | asin(1/fweight)      |
+-----------+---------+----------------------+
| Tom       | 56.67   |  0.01764693590390818 |
| Jim       | 36.17   |  0.02765074432436187 |
| Lily      | 40.33   | 0.024797978465578074 |
| Kelly     | 46.23   |  0.02163266220748844 |
| Sam       | 48.68   |  0.02054376203881499 |
| Kerry     | 66.67   | 0.014999812472575733 |
| Smith     | 51.28   |  0.01950201617235658 |
| BillGates | 60.32   | 0.016579008483673555 |
+-----------+---------+----------------------+
8 rows in set

求反余弦值：

mysql> select fname,fweight,acos(1/fweight) from t_person;
+-----------+---------+--------------------+
| fname     | fweight | acos(1/fweight)    |
+-----------+---------+--------------------+
| Tom       | 56.67   | 1.5531493908909884 |
| Jim       | 36.17   | 1.5431455824705347 |
| Lily      | 40.33   | 1.5459983483293185 |
| Kelly     | 46.23   | 1.5491636645874083 |
| Sam       | 48.68   | 1.5502525647560816 |
| Kerry     | 66.67   |  1.555796514322321 |
| Smith     | 51.28   |   1.55129431062254 |
| BillGates | 60.32   | 1.5542173183112231 |
+-----------+---------+--------------------+
8 rows in set

求正切值：

mysql> select fname, fweight,tan(fweight) from t_person;
+-----------+---------+---------------------+
| fname     | fweight | tan(fweight)        |
+-----------+---------+---------------------+
| Tom       | 56.67   | 0.12193116042366543 |
| Jim       | 36.17   | -23.975845327559608 |
| Lily      | 40.33   | -0.5602840015373491 |
| Kelly     | 46.23   | -1.2444656720345504 |
| Sam       | 48.68   |   68.08655929943217 |
| Kerry     | 66.67   |  0.8364151089005978 |
| Smith     | 51.28   |  1.6082918921923042 |
| BillGates | 60.32   |  0.7287159462679035 |
+-----------+---------+---------------------+
8 rows in set

求反正切值：

mysql> select fname,fweight,atan(fweight) from t_person;
+-----------+---------+--------------------+
| fname     | fweight | atan(fweight)      |
+-----------+---------+--------------------+
| Tom       | 56.67   | 1.5531521371818653 |
| Jim       | 36.17   | 1.5431561463367782 |
| Lily      | 40.33   | 1.5460059688068788 |
| Kelly     | 46.23   |  1.549168723995774 |
| Sam       | 48.68   |  1.550256898418995 |
| Kerry     | 66.67   | 1.5557982014368528 |
| Smith     | 51.28   | 1.5512980181214533 |
| BillGates | 60.32   |  1.554219595987195 |
+-----------+---------+--------------------+
8 rows in set

求2个变量的反正切：

mysql> select fname, fweight,atan2(fweight,2) from t_person;
+-----------+---------+--------------------+
| fname     | fweight | atan2(fweight,2)   |
+-----------+---------+--------------------+
| Tom       | 56.67   | 1.5355189266211002 |
| Jim       | 36.17   | 1.5155581345313869 |
| Lily      | 40.33   |   1.52124604383783 |
| Kelly     | 46.23   |   1.52756133505066 |
| Sam       | 48.68   | 1.5297347852958334 |
| Kerry     | 66.67   | 1.5408068205143712 |
| Smith     | 51.28   | 1.5318145240765815 |
| BillGates | 60.32   | 1.5376519703495661 |
+-----------+---------+--------------------+
8 rows in set

求余切：

mysql> select fname, fweight,cot(fweight) from t_person;
+-----------+---------+-----------------------+
| fname     | fweight | cot(fweight)          |
+-----------+---------+-----------------------+
| Tom       | 56.67   |      8.20134899500154 |
| Jim       | 36.17   | -0.041708644109850264 |
| Lily      | 40.33   |   -1.7848091276140765 |
| Kelly     | 46.23   |   -0.8035577215762981 |
| Sam       | 48.68   |  0.014687186579691652 |
| Kerry     | 66.67   |    1.1955785941198764 |
| Smith     | 51.28   |    0.6217776790734636 |
| BillGates | 60.32   |    1.3722768180406502 |
+-----------+---------+-----------------------+
8 rows in set

求圆周率π值：

mysql> select PI(), PI() * 5;
+----------+-----------+
| PI()     | PI() * 5  |
+----------+-----------+
| 3.141593 | 15.707963 |
+----------+-----------+
1 row in set

弧度制转换为角度制：

mysql> select fname,fweight,degrees(fweight) from t_person;
+-----------+---------+--------------------+
| fname     | fweight | degrees(fweight)   |
+-----------+---------+--------------------+
| Tom       | 56.67   |  3246.951825006375 |
| Jim       | 36.17   |  2072.388344988188 |
| Lily      | 40.33   |   2310.73878776261 |
| Kelly     | 46.23   | 2648.7838868897957 |
| Sam       | 48.68   | 2789.1585466968477 |
| Kerry     | 66.67   | 3819.9096201371985 |
| Smith     | 51.28   | 2938.1275734308615 |
| BillGates | 60.32   |  3456.081420229126 |
+-----------+---------+--------------------+
8 rows in set

角度值转换为弧度制：

mysql> select fname,fweight,radians(fweight) from t_person;
+-----------+---------+--------------------+
| fname     | fweight | radians(fweight)   |
+-----------+---------+--------------------+
| Tom       | 56.67   | 0.9890780871051866 |
| Jim       | 36.17   |  0.631285590446349 |
| Lily      | 40.33   |  0.703891287329313 |
| Kelly     | 46.23   | 0.8068657131969785 |
| Sam       | 48.68   | 0.8496262798708396 |
| Kerry     | 66.67   | 1.1636110123046195 |
| Smith     | 51.28   | 0.8950048404226922 |
| BillGates | 60.32   | 1.0527826048029796 |
+-----------+---------+--------------------+
8 rows in set

求符号：

mysql> select fname,fweight-48.68,sign(fweight-48.68) from t_person;
+-----------+---------------+---------------------+
| fname     | fweight-48.68 | sign(fweight-48.68) |
+-----------+---------------+---------------------+
| Tom       | 7.99          |                   1 |
| Jim       | -12.51        |                  -1 |
| Lily      | -8.35         |                  -1 |
| Kelly     | -2.45         |                  -1 |
| Sam       | 0             |                   0 |
| Kerry     | 17.99         |                   1 |
| Smith     | 2.6           |                   1 |
| BillGates | 11.64         |                   1 |
+-----------+---------------+---------------------+
8 rows in set

求余数：

mysql> select fname,fweight,mod(fweight,5) from t_person;
+-----------+---------+----------------+
| fname     | fweight | mod(fweight,5) |
+-----------+---------+----------------+
| Tom       | 56.67   | 1.67           |
| Jim       | 36.17   | 1.17           |
| Lily      | 40.33   | 0.33           |
| Kelly     | 46.23   | 1.23           |
| Sam       | 48.68   | 3.68           |
| Kerry     | 66.67   | 1.67           |
| Smith     | 51.28   | 1.28           |
| BillGates | 60.32   | 0.32           |
+-----------+---------+----------------+
8 rows in set

求自然对数：

mysql> select fname,fweight,log(fweight) from t_person;
+-----------+---------+--------------------+
| fname     | fweight | log(fweight)       |
+-----------+---------+--------------------+
| Tom       | 56.67   |  4.037244970181528 |
| Jim       | 36.17   | 3.5882300459640066 |
| Lily      | 40.33   |  3.697095608885277 |
| Kelly     | 46.23   |  3.833628938000134 |
| Sam       | 48.68   | 3.8852682681193262 |
| Kerry     | 66.67   |  4.199755076629969 |
| Smith     | 51.28   | 3.9373008126124147 |
| BillGates | 60.32   |  4.099663723699701 |
+-----------+---------+--------------------+
8 rows in set

求以10为底的对数：

mysql> select fname,fweight,log10(fweight) from t_person;
+-----------+---------+--------------------+
| fname     | fweight | log10(fweight)     |
+-----------+---------+--------------------+
| Tom       | 56.67   | 1.7533532126414961 |
| Jim       | 36.17   | 1.5583485087616198 |
| Lily      | 40.33   | 1.6056282220076186 |
| Kelly     | 46.23   | 1.6649238934380817 |
| Sam       | 48.68   | 1.6873505695580273 |
| Kerry     | 66.67   |  1.823930455125564 |
| Smith     | 51.28   |  1.709948016510761 |
| BillGates | 60.32   | 1.7804613328617176 |
+-----------+---------+--------------------+
8 rows in set

求幂：

mysql> select fname,fweight,power(1.18, fweight) from t_person;
+-----------+---------+----------------------+
| fname     | fweight | power(1.18, fweight) |
+-----------+---------+----------------------+
| Tom       | 56.67   |   11845.498826328208 |
| Jim       | 36.17   |   398.08169169354545 |
| Lily      | 40.33   |    792.5038024630314 |
| Kelly     | 46.23   |   2104.2880930070924 |
| Sam       | 48.68   |   3156.5750223127166 |
| Kerry     | 66.67   |    61997.39290971575 |
| Smith     | 51.28   |    4854.106880642623 |
| BillGates | 60.32   |   21673.182135863717 |
+-----------+---------+----------------------+
8 rows in set