Mysql基礎教程 mysql進階

Mysql數學函數庫



MySQL中的函數


得到當前版本號(系統函數)


mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)


查看當前時間和日期(系統函數)


mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-01-03 15:37:54 |
+---------------------+
1 row in set (0.06 sec)



數學函數


名稱

描述

CEIL()

進一取整

FLOOR()

舍一取整

MOD

取余數(區模)

POWER()

冪運算

ROUND()

四舍五入

TRUNCATE()

數字截取

ABS()

取絕對值

PI()

圓周率

RAND()和RAND(x)

返回0~1之間隨機數,RAND(X)X相同時返回的隨機數相同。

SIGN(X)

返回X的符號,X為負數、0、正數分別返回-1和0和1

EXP(X)

計算e的幾次方


數學函數的測試,進一取整CEIL(1.2),CEILING(1.2)


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


(結果一樣)


查看用戶表的記錄


mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password | email  | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三  | zhangsan| [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  2 | 張三豐 | zhangsanfeng | [email protected] | 1419812708 | user.jpg | 2 |   31 | 女     |
|  3 | 章子怡| zhangsan | [email protected] | 1419813708 | user.jpg |     3 |   43 | 男     |
|  4 | long      | long | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring      | ring  | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen | queen | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek    | blek   | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose   | rose  | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john      | john | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2     | TEST2 | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
| 14 | lll       | lll  | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 15 | ttt       | lll   | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 16 | ooo  | lll    | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
15 rows in set (0.00 sec)


查找maizi數據庫并查看數據庫中的表


mysql> USE maizi;
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate        |
| cms_news        |
| course          |
| test1           |
| test10          |
| test11          |
| test12          |
| test13          |
| test14          |
| test2           |
| test3           |
| test4           |
| test5           |
| test6           |
| test7           |
| test8           |
| test9           |
| testuser        |
| user            |
| user1           |
| user2           |
| user3           |
+-----------------+
22 rows in set (0.00 sec)


查看user表


mysql> select * from user;
Empty set (0.10 sec)


查看user表的記錄


mysql> select * from user1;
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
| 13 | QUEEN    |
+----+----------+
2 rows in set (0.08 sec)


查看user2表的記錄


mysql> select * from user2;
+----+----------+------+
| id | username | card |
+----+----------+------+
|  1 | king     | 111  |
|  1 | queen    | 112  |
+----+----------+------+
2 rows in set (0.07 sec)


查看text2表的記錄


mysql> SELECT *FROM test2;
+------+------+
| num1 | num2 |
+------+------+
|    0 |  -12 |
+------+------+
1 row in set (0.07 sec)


查看text3表的記錄


mysql> SELECT *FROM test3;
+------+-------+----------+------------+----------------------+
| num1 | num2  | num3     | num4       | num5                 |
+------+-------+----------+------------+----------------------+
|  001 | 00001 | 00000001 | 0000000001 | 00000000000000000001 |
|  123 | 00001 | 00000001 | 0000000001 | 00000000000000000001 |
+------+-------+----------+------------+----------------------+
2 rows in set (0.06 sec)


查看text4表的記錄


mysql> SELECT *FROM test4;
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 3.14 | 3.14 | 3.14 |
| 3.25 | 3.25 | 3.25 |
+------+------+------+
2 rows in set (0.07 sec)


對text4表的num1,CEIL(num2),CEILING(num3)浮點數進行取整


mysql> SELECT num1,CEIL(num2),CEILING(num3) FROM test4;
+------+------------+---------------+
| num1 | CEIL(num2) | CEILING(num3) |
+------+------------+---------------+
| 3.14 |          4 |             4 |
| 3.25 |          4 |             4 |
+------+------------+---------------+
2 rows in set (0.00 sec)


對數據直接取整


mysql> SELECT FLOOR(3.14);
+-------------+
| FLOOR(3.14) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)


對數據直接取余數


mysql> SELECT MOD(3,8);
+----------+
| MOD(3,8) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)


對數據冪運算


mysql> SELECT POW(2,3),POWER(3,3);
+----------+------------+
| POW(2,3) | POWER(3,3) |
+----------+------------+
|        8 |         27 |
+----------+------------+
1 row in set (0.06 sec)


四舍五入(3.14567,2)取兩位


mysql> SELECT ROUND(3.14567,2);
+------------------+
| ROUND(3.14567,2) |
+------------------+
|             3.15 |
+------------------+
1 row in set (0.02 sec)


TRUNCATE截斷數字,不進行四舍五入


mysql> SELECT TRUNCATE(3.14567,2);
+---------------------+
| TRUNCATE(3.14567,2) |
+---------------------+
|                3.14 |
+---------------------+
1 row in set (0.00 sec)


ABS取絕對值


mysql> SELECT ABS(-12);
+----------+
| ABS(-12) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)


PI得到圓周率


mysql> SELECT PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.01 sec)


RAND返回0到1之間的隨機數(每次返回的數是不同的)


mysql> SELECT RAND();
+--------------------+
| RAND()             |
+--------------------+
| 0.6808652986594153 |
+--------------------+
1 row in set (0.00 sec)


RAND返回0到1之間的隨機數(每次返回的數是不同的)


mysql> SELECT RAND();
+--------------------+
| RAND()             |
+--------------------+
| 0.7984468916416605 |
+--------------------+
1 row in set (0.00 sec)


RAND返回0到1之間的隨機數(每次返回的數是不同的)


mysql> SELECT RAND();
+--------------------+
| RAND()             |
+--------------------+
| 0.9496385929637017 |
+--------------------+
1 row in set (0.00 sec)


PI得到圓周率


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


RAND返回0到1之間的隨機數(每次返回的數是不同的)


mysql> SELECT RAND();
+---------------------+
| RAND()              |
+---------------------+
| 0.35285232062717187 |
+---------------------+
1 row in set (0.00 sec)


RAND取用戶表的隨機記錄


mysql> SELECT * FROM cms.cms_user ORDER BY RAND();
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| 14 | lll       | lll  | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
|  6 | queen  | queen| [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  9 | rose      | rose | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 15 | ttt       | lll | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 11 | john      | john| [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
|  2 | 張三豐    | zhangsanfeng | [email protected] | 1419812708 | user.jpg | 2 |31 | 女     |
| 16 | ooo       | lll   | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
|  4 | long      | long | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
| 13 | TEST2     | TEST2 | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
|  5 | ring      | ring| [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  3 | 章子怡| zhangsan | [email protected] | 1419813708 | user.jpg |  3 |   43 | 男     |
|  8 | blek      | blek | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
| 10 | lily      | lily   | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
|  1 | 張三  | zhangsan  | [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
15 rows in set (0.02 sec)


RAND取用戶表的隨機記錄


mysql> SELECT * FROM cms.cms_user ORDER BY RAND();
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password| email     | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  9 | rose      | rose | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
|  6 | queen | queen  | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
| 15 | ttt       | lll  | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
|  8 | blek      | blek  | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
| 13 | TEST2     | TEST2 | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
|  3 | 章子怡 | zhangsan | [email protected] | 1419813708 | user.jpg | 3 |   43 | 男     |
| 10 | lily      | lily  | [email protected] | 1419831708 | user.jpg |  2 |   39 | 女     |
| 16 | ooo       | lll   | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
|  1 | 張三  | zhangsan | [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  5 | ring      | ring  | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
| 11 | john      | john | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 14 | lll       | lll   | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
|  2 | 張三豐| zhangsanfeng | [email protected] | 1419812708 | user.jpg | 2 |   31 | 女     |
| 12 | test1     | test1   | [email protected] | 1419811708 | user.jpg | 1 | NULL | 保密   |
|  4 | long      | long  | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
15 rows in set (0.00 sec)


RAND返回固定隨機數


mysql> SELECT RAND(1);
+---------------------+
| RAND(1)             |
+---------------------+
| 0.40540353712197724 |
+---------------------+
1 row in set (0.00 sec)


重復看見這個數就可以寫RAND(1)


mysql> SELECT RAND(1);
+---------------------+
| RAND(1)             |
+---------------------+
| 0.40540353712197724 |
+---------------------+
1 row in set (0.00 sec)


SIGN的數字的符號


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


EXP計算e的幾次方


mysql> SELECT EXP(3);
+--------------------+
| EXP(3)             |
+--------------------+
| 20.085536923187668 |
+--------------------+
1 row in set (0.03 sec)



【本文由麥子學院獨家原創,轉載請注明出處并保留原文鏈接】

logo
? 2012-2016 www.jvbprd.live
蜀ICP備13014270號-4 Version 5.0.0 release20160127

免費領取價值1888元求職寶典!

客服熱線 400-862-8862

回到頂部

彩票安徽25选5 修手机可赚钱 怎么下载大鱼号赚钱 逆水寒和剑网三哪个赚钱 微乐免费麻将外挂神器 改手机串号无限赚钱 龙王捕鱼必赢窍门 母婴护理中心赚钱吗 开小吃铺赚钱吗 创盈彩票苹果 买小百货赚钱吗 王者荣耀官网 蜀山传奇手游怎么赚钱 分享赚钱的平台靠谱吗 广西麻将大胡算法 买个车放婚庆到底赚不赚钱 千旺彩票群