MySQL讲义第40讲——select 查询之函数（3）：数学函数

使用函数可在查询时构造更加灵活的查询条件。MySQL 提供了处理数值型数据的函数，下面对数学函数进行详细的介绍并举例说明其用法。

一、数据准备

在当前数据库中创建一个保存上市公司信息的 listed_company 表，表结构及表中的数据如下：

CREATE TABLE listed_company(
    company_id CHAR(6) PRIMARY KEY, 
    company_abbreviation CHAR(20), 
    company_fullname CHAR(200), 
    English_name CHAR(200), 
    registered_address CHAR(200),
    listing_date DATETIME,
    total_share_capital DECIMAL(16,2), 
    circulating_share_capital DECIMAL(16,2),
    industry CHAR(100), 
    company_website CHAR(200)
);    
mysql> DESC listed_company;
+---------------------------+---------------+------+-----+---------+-------+
| Field                     | Type          | Null | Key | Default | Extra |
+---------------------------+---------------+------+-----+---------+-------+
| company_id                | char(6)       | NO   | PRI | NULL    |       |
| company_abbreviation      | char(20)      | YES  |     | NULL    |       |
| company_fullname          | char(200)     | YES  |     | NULL    |       |
| English_name              | char(200)     | YES  |     | NULL    |       |
| registered_address        | char(200)     | YES  |     | NULL    |       |
| listing_date              | datetime      | YES  |     | NULL    |       |
| total_share_capital       | decimal(16,2) | YES  |     | NULL    |       |
| circulating_share_capital | decimal(16,2) | YES  |     | NULL    |       |
| industry                  | char(100)     | YES  |     | NULL    |       |
| company_website           | char(200)     | YES  |     | NULL    |       |
+---------------------------+---------------+------+-----+---------+-------+
10 rows in set (0.00 sec)

SELECT * 
FROM listed_company 
WHERE company_id = '000004'\G

*************************** 1. row ***************************
               company_id: 000004
     company_abbreviation: 国农科技
         company_fullname: 深圳中国农大科技股份有限公司
             English_name: SHENZHEN CAU TECHNOLOGY CO., LTD
       registered_address: 广东省深圳市南山区中心路（深圳湾段）3333号中铁南方总部大厦503室
             listing_date: 1990-12-01 00:00:00
      total_share_capital: 83976684.00
circulating_share_capital: 82905273.00
                 industry: C 制造业
          company_website: www.sz000004.cn
1 row in set (0.00 sec)

二、MySQL 数学函数介绍

1、ABS() 函数

ABS() 函数返回一个数值的绝对值。语法格式如下：

ABS(n);

举例：查询【工商银行】和【农业银行】两支股票的总股本差异

SET @count_gsyh = 0;
SET @count_nyyh = 0;

SELECT 
    total_share_capital
INTO 
    @count_gsyh
FROM
    listed_company
WHERE
    company_abbreviation = '工商银行';
    
SELECT 
    total_share_capital
INTO 
    @count_nyyh
FROM
    listed_company
WHERE
    company_abbreviation = '农业银行';
    
SELECT
    @count_gsyh,
    @count_nyyh,
    ABS(@count_gsyh - @count_nyyh) AS count_interval;
+-------------+-------------+----------------------------------------+
| @count_gsyh | @count_nyyh | count_interval                         |
+-------------+-------------+----------------------------------------+
| 26961221.25 | 29405529.39 | 2444308.140000000000000000000000000000 |
+-------------+-------------+----------------------------------------+
1 row in set (0.01 sec)

2、FORMAT() 函数

FORMAT() 函数可以对一个数值进行格式化操作，返回值类型为字符串。语法格式如下：

FORMAT(X,n);

--说明：
（1）返回值的类型为字符串。
（2）对 X 进行四舍五入，保留 n 位小数，并以 ##,###,###.### 格式显示。

举例：显示股票的总股本和流通股本，并进行格式化

SELECT
    company_id,
    company_abbreviation,
    FORMAT(total_share_capital,4) AS total_share_capital,
    FORMAT(circulating_share_capital,4) AS circulating_share_capital
FROM 
    listed_company
WHERE 
    company_id < '000010';
+------------+----------------------+---------------------+---------------------------+
| company_id | company_abbreviation | total_share_capital | circulating_share_capital |
+------------+----------------------+---------------------+---------------------------+
| 000001     | 平安银行             | 17,170,411,366.0000 | 14,623,200,091.0000       |
| 000002     | 万 科Ａ              | 9,724,196,533.0000  | 9,705,462,185.0000        |
| 000004     | 国农科技             | 83,976,684.0000     | 82,905,273.0000           |
| 000005     | 世纪星源             | 1,058,536,842.0000  | 912,332,164.0000          |
| 000006     | 深振业Ａ             | 1,349,995,046.0000  | 1,343,618,405.0000        |
| 000007     | 全新好               | 230,965,363.0000    | 205,776,701.0000          |
| 000008     | 神州高铁             | 2,757,709,279.0000  | 1,494,764,599.0000        |
| 000009     | 中国宝安             | 2,149,344,971.0000  | 2,118,885,157.0000        |
+------------+----------------------+---------------------+---------------------------+
8 rows in set (0.00 sec)

3、ROUND() 函数

ROUND() 函数可以对一个数值进行四舍五入操作。语法格式如下：

ROUND(x);
ROUND(x,n);
--说明：
（1）对 x 进行四舍五入，保留 n 位小数。
（2）如果省略 n，则保留 0 位小数。
（3）n 如果小于 0，则对整数位四舍五入。比如 n = -2，对百位数四舍五入。

举例：

（1）查询所有银行股的总股本并四舍五入保留到千位数。

SELECT
    company_id,
    company_abbreviation,
    FORMAT(ROUND(total_share_capital,-3),0) AS total_share_capital
FROM 
    listed_company
WHERE 
    company_abbreviation LIKE '%银行%';
+------------+----------------------+---------------------+
| company_id | company_abbreviation | total_share_capital |
+------------+----------------------+---------------------+
| 000001     | 平安银行             | 17,170,411,000      |
| 002142     | 宁波银行             | 3,899,794,000       |
| 002807     | 江阴银行             | 1,767,354,000       |
| 600000     | 浦发银行             | 2,162,000           |
| 600015     | 华夏银行             | 1,069,000           |
| 600016     | 民生银行             | 2,955,000           |
| 600036     | 招商银行             | 2,063,000           |
| 600908     | 无锡银行             | 185,000             |
| 600919     | 江苏银行             | 1,154,000           |
| 600926     | 杭州银行             | 262,000             |
| 601009     | 南京银行             | 606,000             |
| 601128     | 常熟银行             | 222,000             |
| 601166     | 兴业银行             | 1,905,000           |
| 601169     | 北京银行             | 1,521,000           |
| 601229     | 上海银行             | 600,000             |
| 601288     | 农业银行             | 29,406,000          |
| 601328     | 交通银行             | 3,925,000           |
| 601398     | 工商银行             | 26,961,000          |
| 601818     | 光大银行             | 3,981,000           |
| 601939     | 建设银行             | 959,000             |
| 601988     | 中国银行             | 21,077,000          |
| 601997     | 贵阳银行             | 230,000             |
| 601998     | 中信银行             | 3,405,000           |
| 603323     | 吴江银行             | 111,000             |
+------------+----------------------+---------------------+
24 rows in set (0.00 sec)

（2）查询股票简称包含【石油】的股票的总股本并四舍五入保留1位小数

SELECT
    company_id,
    company_abbreviation,
    total_share_capital,
    FORMAT(ROUND(total_share_capital,1),1) AS total_share_capital_002
FROM 
    listed_company
WHERE 
    company_abbreviation LIKE '%石油%';
+------------+----------------------+---------------------+-------------------------+
| company_id | company_abbreviation | total_share_capital | total_share_capital_002 |
+------------+----------------------+---------------------+-------------------------+
| 000554     | 泰山石油             |        480793318.00 | 480,793,318.0           |
| 300164     | 通源石油             |        440432159.00 | 440,432,159.0           |
| 601857     | 中国石油             |         16192207.78 | 16,192,207.8            |
+------------+----------------------+---------------------+-------------------------+
3 rows in set (0.00 sec)

4、TRUNCATE() 函数

TRUNCATE() 函数把一个数值截取 n 位小数，并且不进行四舍五入。语法格式如下：

TRUNCATE(x,n);

--说明：把数值 x 的小数点后第 n 位之后的数据直接舍去（不四舍五入），保留 n 为小数。

举例：

SELECT 
    TRUNCATE(12.258,2),
    TRUNCATE(1285.0128,-2),
    TRUNCATE(12.7258,2);
+--------------------+------------------------+---------------------+
| TRUNCATE(12.258,2) | TRUNCATE(1285.0128,-2) | TRUNCATE(12.7258,2) |
+--------------------+------------------------+---------------------+
|              12.25 |                   1200 |               12.72 |
+--------------------+------------------------+---------------------+
1 row in set (0.01 sec)

5、CEILING() 和 FLOOR() 函数

CEILING() 和 FLOOR() 函数可以对一个数值进行取整操作。语法格式如下：

CEILING(X);   --返回大于或等于 X 的最小整数
FLOOR(X);     --返回小于或等于 X 的最大整数

举例：

SELECT 
    CEILING(4.9),
    CEILING(-2.8);
+--------------+---------------+
| CEILING(4.9) | CEILING(-2.8) |
+--------------+---------------+
|            5 |            -2 |
+--------------+---------------+
1 row in set (0.01 sec)

SELECT 
    FLOOR(3.9),
    FLOOR(-2.7);
+------------+-------------+
| FLOOR(3.9) | FLOOR(-2.7) |
+------------+-------------+
|          3 |          -3 |
+------------+-------------+
1 row in set (0.00 sec)

6、RAND() 函数

RAND() 函数用于生成一个随机数。语法格式如下：

RAND(n);
RAND();

--说明：
（1）RAND() 函数可以产生一个0到1之间的随机数。
（2）如果无参数，每次运行都会产生一个不同的随机数。
（3）如果指定了种子 n，则每次运行时产生的随机数相同。

举例：

（1）基本用法

SELECT 
    RAND(22),
    RAND(22),
    RAND(22);
+--------------------+--------------------+--------------------+
| RAND(22)           | RAND(22)           | RAND(22)           |
+--------------------+--------------------+--------------------+
| 0.6592488313645579 | 0.6592488313645579 | 0.6592488313645579 |
+--------------------+--------------------+--------------------+
1 row in set (0.01 sec)

SELECT 
    RAND(),
    RAND(),
    RAND();
+--------------------+------------------------+---------------------+
| RAND()             | RAND()                 | RAND()              |
+--------------------+------------------------+---------------------+
| 0.1869446245831853 | 0.00047542341097763126 | 0.44154327403897725 |
+--------------------+------------------------+---------------------+
1 row in set (0.00 sec)

（2）创建一张表 t1，为 num 列插入 100 到 200 之间的随机整数

-- 1、创建表 t1
CREATE TABLE t1(
    id int primary key auto_increment,
    num int
);
--2、创建存储过程，向表中添加若干记录
DELIMITER //
CREATE PROCEDURE sp_insert(IN cnt_insert INT)
BEGIN
    DECLARE n INT;
    SET n = 1;
    WHILE (n < cnt_insert) DO
        INSERT INTO t1(num)
        VALUES(100 + RAND() * 100);
        SET n = n +1;
    END WHILE;
END //
DELIMITER ;
--3、执行存储过程，向 t1 表添加 20 条记录
CALL sp_insert(20);
--4、查看 t1 表中的数据
SELECT
    *
FROM
    t1;
mysql> select * from t1;
+----+------+
| id | num  |
+----+------+
|  1 |  179 |
|  2 |  135 |
|  3 |  139 |
|  4 |  189 |
|  5 |  127 |
|  6 |  170 |
|  7 |  169 |
|  8 |  136 |
|  9 |  173 |
| 10 |  154 |
| 11 |  153 |
| 12 |  104 |
| 13 |  160 |
| 14 |  186 |
| 15 |  153 |
| 16 |  105 |
| 17 |  167 |
| 18 |  122 |
| 19 |  106 |
+----+------+
19 rows in set (0.00 sec)

7、SQRT() 函数

SQRT() 函数可以求一个数的平方根。语法格式如下：

SQRT(x);

举例：

SELECT
    SQRT(16),
    SQRT(10);
+----------+--------------------+
| SQRT(16) | SQRT(10)           |
+----------+--------------------+
|        4 | 3.1622776601683795 |
+----------+--------------------+
1 row in set (0.01 sec)

8、SIGN() 函数

SIGN() 函数用于判断一个数是大于 0、小于 0 或者等于 0。语法格式如下：

SIGN(x);

--说明：如果 x>0，返回 1，如果 x<0，返回 -1，如果 x=0，返回 0。

例如：

SELECT 
    SIGN(10666.98),
    SIGN(-0.00988),
    SIGN(0);
+----------------+----------------+---------+
| SIGN(10666.98) | SIGN(-0.00988) | SIGN(0) |
+----------------+----------------+---------+
|              1 |             -1 |       0 |
+----------------+----------------+---------+
1 row in set (0.02 sec)

9、POWER() 函数

POWER() 函数用于计算一个数的若干次幂。语法格式如下：

POWER(x,y);

--说明：返回x的y次方。

举例：

SELECT
    POWER(2,10),
    POWER(16,-0.5),
    POWER(4,1.2);
+-------------+----------------+-------------------+
| POWER(2,10) | POWER(16,-0.5) | POWER(4,1.2)      |
+-------------+----------------+-------------------+
|        1024 |           0.25 | 5.278031643091577 |
+-------------+----------------+-------------------+
1 row in set (0.00 sec)

10、PI() 函数

PI() 函数返回圆周率。语法格式如下：

PI();

举例：

--求半径为 5 的圆的周长和面积
SET @r = 5;
SELECT 
    PI(),
    @r AS radius,
    2 * PI() * @r AS perimeter,
    PI() * POWER(@r, 2) AS area;
+----------+--------+-----------+-------------------+
| PI()     | radius | perimeter | area              |
+----------+--------+-----------+-------------------+
| 3.141593 |      5 | 31.415927 | 78.53981633974483 |
+----------+--------+-----------+-------------------+
1 row in set (0.00 sec)

11、MOD() 函数

MOD() 函数进行求余操作。语法格式如下：

MOD(m,n);

--说明：返回 m 除以 n 的余数。

举例：

SELECT
    MOD(10,3),
    MOD(10,-3),
    MOD(-10,3),
    MOD(-10,-3);
+-----------+------------+------------+-------------+
| MOD(10,3) | MOD(10,-3) | MOD(-10,3) | MOD(-10,-3) |
+-----------+------------+------------+-------------+
|         1 |          1 |         -1 |          -1 |
+-----------+------------+------------+-------------+
1 row in set (0.01 sec)

12、三角函数

角函数的语法格式如下：

SIN(X);   --正弦函数
COS(X);   --余弦函数
TAN(X);   --正切函数
COT(X);   --余切函数

--说明：X表示弧度

举例：

SELECT
    SIN(pi()/2) as sin,
    COS(pi()/6) as cos,
    TAN(pi()/2) as tan,
    COT(pi()/3) as cot;
+------+--------------------+----------------------+-------------------+
| sin  | cos                | tan                  | cot               |
+------+--------------------+----------------------+-------------------+
|    1 | 0.8660254037844387 | 1.633123935319537e16 | 0.577350269189626 |
+------+--------------------+----------------------+-------------------+
1 row in set (0.05 sec)

13、EXP() 函数

EXP() 函数返回常数 e 的若干次幂。语法格式如下：

EXP(X);

--说明：返回e的X次方。

举例：

SELECT
    EXP(1),
    EXP(2);
+-------------------+------------------+
| EXP(1)            | EXP(2)           |
+-------------------+------------------+
| 2.718281828459045 | 7.38905609893065 |
+-------------------+------------------+
1 row in set (0.04 sec)

14、LN() 函数和 LOG() 函数

LN() 函数返回一个数的自然对数，LOG() 函数返回一个数的常用对数。语法格式如下：

LN(X);      --返回X的自然对数
LOG(X);     --返回X的自然对数
LOG(n,x);   --返回以n为底的对数
LOG2(x);    --返回以2为底的对数
LOG10(x);   --返回以10为底的对数

举例：

SELECT
    LN(10),
    LOG(10);
+-------------------+-------------------+
| LN(10)            | LOG(10)           |
+-------------------+-------------------+
| 2.302585092994046 | 2.302585092994046 |
+-------------------+-------------------+
1 row in set (0.02 sec)

SELECT
    LOG(2,1024),
    LOG2(1024),
    LOG10(1000);
+-------------+------------+-------------+
| LOG(2,1024) | LOG2(1024) | LOG10(1000) |
+-------------+------------+-------------+
|          10 |         10 |           3 |
+-------------+------------+-------------+
1 row in set (0.01 sec)