MySQL筆記之數學函數詳解_MySQL教程
      
編輯Tag賺U幣
      


      教程Tag：函數MySQL添加
      


      

      推薦：MySQL筆記之別名的使用
      在查詢時，可以為表和字段取一個別名。這個別名可以代替其指定的表和字段
      絕對值函數ABS(x)和圓周率函數PI()
      復制代碼 代碼如下:www.wf0088.com

      mysql> SELECT ABS(0.5), ABS(-0.5), PI();
      +----------+-----------+----------+
      | ABS(0.5) | ABS(-0.5) | PI()     |
      +----------+-----------+----------+
      |      0.5 |       0.5 | 3.141593 |
      +----------+-----------+----------+
       row in set (0.00 sec)
      

      平方根函數SQRT(x)和求余函數MOD(x,y)
      復制代碼 代碼如下:www.wf0088.com

      mysql> SELECT SQRT(16), SQRT(3), MOD(13,4);
      +----------+--------------------+-----------+
      | SQRT(16) | SQRT(3)            | MOD(13,4) |
      +----------+--------------------+-----------+
      |        4 | 1.7320508075688772 |         1 |
      +----------+--------------------+-----------+
       row in set (0.00 sec)
      

      取整函數CEIL(x)、CEILING(x)和FLOOR(x)
      復制代碼 代碼如下:www.wf0088.com

      mysql> SELECT CEIL(2.3), CEIL(-2.3), CEILING(2.3), CEILING(-2.3);
      +-----------+------------+--------------+---------------+
      | CEIL(2.3) | CEIL(-2.3) | CEILING(2.3) | CEILING(-2.3) |
      +-----------+------------+--------------+---------------+
      |         3 |         -2 |            3 |            -2 |
      +-----------+------------+--------------+---------------+
       row in set (0.00 sec)
      

      mysql> SELECT FLOOR(2.3), FLOOR(-2.3);
      +------------+-------------+
      | FLOOR(2.3) | FLOOR(-2.3) |
      +------------+-------------+
      |          2 |          -3 |
      +------------+-------------+
       row in set (0.00 sec)

      CEIL(x)和CEILING(x)返回大于或等于x的最小整數
      

      FLOOR(x)返回小于或等于x的最大整數
      

      隨機數函數RAND()和RAND(x)
      復制代碼 代碼如下:www.wf0088.com

      mysql> SELECT RAND(), RAND(2), RAND(2);
      +--------------------+--------------------+--------------------+
      | RAND()             | RAND(2)            | RAND(2)            |
      +--------------------+--------------------+--------------------+
      | 0.8269294489425881 | 0.6555866465490187 | 0.6555866465490187 |
      +--------------------+--------------------+--------------------+
       row in set (0.00 sec)

      RAND()和RAND(x)這兩個函數丟失返回0～1的隨機數
      

      區別在于，RAND()返回的數是完全隨機的，而RAND(x)在x相同時返回的值相同
      

      四舍五入函數ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)
      復制代碼 代碼如下:www.wf0088.com

      mysql> SELECT ROUND(2.3), ROUND(2.5), ROUND(2.53,1), ROUND(2.55,1);
      +------------+------------+---------------+---------------+
      | ROUND(2.3) | ROUND(2.5) | ROUND(2.53,1) | ROUND(2.55,1) |
      +------------+------------+---------------+---------------+
      |          2 |          3 |           2.5 |           2.6 |
      +------------+------------+---------------+---------------+
       row in set (0.00 sec)

      ROUND(x)返回離x最近的整數，也就是對x進行四舍五入處理
      

      ROUND(x,y)返回x保留到小數點后y位的值，在截取時進行四舍五入處理
      

      復制代碼 代碼如下:www.wf0088.com

      mysql> SELECT TRUNCATE(2.53,1), TRUNCATE(2.55,1);
      +------------------+------------------+
      | TRUNCATE(2.53,1) | TRUNCATE(2.55,1) |
      +------------------+------------------+
      |              2.5 |              2.5 |
      +------------------+------------------+
       row in set (0.00 sec)

      TRUNCATE(x,y)返回x保留到小數點后y位的值，不進行四舍五入操作
      

      符號函數SIGN(x)
      復制代碼 代碼如下:www.wf0088.com

      mysql> SELECT SIGN(-2), SIGN(0), SIGN(2);
      +----------+---------+---------+
      | SIGN(-2) | SIGN(0) | SIGN(2) |
      +----------+---------+---------+
      |       -1 |       0 |       1 |
      +----------+---------+---------+
       row in set (0.00 sec)

      SIGN(x)返回x的符號，-1為負數，0不變，1為整數
      


      冪運算函數POW(x,y)、POWER(x,y)
      復制代碼 代碼如下:www.wf0088.com

      mysql> SELECT POW(3,2), POWER(3,2);
      +----------+------------+
      | POW(3,2) | POWER(3,2) |
      +----------+------------+
      |        9 |          9 |
      +----------+------------+
       row in set (0.00 sec)
      分享：MySQL筆記之子查詢使用介紹
      子查詢是將一個查詢語句嵌套在另一個查詢語句中,內層查詢語句的查詢結果，可以為外層查詢語句提供查詢條件
      


      

      


      來源：模板無憂//所屬分類：MySQL教程/更新時間：2013-05-04


      


      



      


      



      相關MySQL教程：