Mysql内置函数

1.数学函数

　　ABS(x) ----> 返回x的绝对值

mysql> select abs(1024),abs(-1024);
+-----------+------------+
| abs(1024) | abs(-1024) |
+-----------+------------+
|      1024 |       1024 |
+-----------+------------+
1 row in set (0.00 sec)

　　FLOOR(x)---->返回小于或者等于x的最大整数

mysql> select floor(-2.3),floor(9);
+-------------+----------+
| floor(-2.3) | floor(9) |
+-------------+----------+
|          -3 |        9 |
+-------------+----------+
1 row in set (0.00 sec)

　　RAND()---->返回0~1的随机数

mysql> select rand(), rand();
+--------------------+----------------------+
| rand()             | rand()               |
+--------------------+----------------------+
| 0.2691672390971028 | 0.006026514811465996 |
+--------------------+----------------------+
1 row in set (0.00 sec)

　　PI()---->返回圆周率

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

　　TRUNCATE(x,y)---->返回x保留到小数点后y位的值

mysql> select truncate (pi(),2) as evilxr;
+--------+
| evilxr |
+--------+
|   3.14 |
+--------+
1 row in set (0.00 sec)

　　ROUND(X)函数和ROUND(X,Y)---->前者返回离x最近的整数(四舍五入)，后者返回x保留到小数点y位的值

mysql> select round(pi(),3),round(1024.3389);
+---------------+------------------+
| round(pi(),3) | round(1024.3389) |
+---------------+------------------+
|         3.142 |             1024 |
+---------------+------------------+
1 row in set (0.00 sec)

　　SQRT(x)---->返回x的平方根

mysql> select sqrt(225),sqrt(625),sqrt(1024) as Evilxr;
+-----------+-----------+--------+
| sqrt(225) | sqrt(625) | Evilxr |
+-----------+-----------+--------+
|        15 |        25 |     32 |
+-----------+-----------+--------+
1 row in set (0.00 sec)

此外内置了sin(x) cos(x) asin(x) acos(x) 等数学函数

2.字符串函数

　　UPPER(S)和函数USCASE(S) 将S中所有小写字母变为大写字母

mysql> select upper('evilxr'),ucase('is good Boy!');
+-----------------+-----------------------+
| upper('evilxr') | ucase('is good Boy!') |
+-----------------+-----------------------+
| EVILXR          | IS GOOD BOY!          |
+-----------------+-----------------------+
1 row in set (0.00 sec)

　　LEFT(s,n)----> 返回字符串s前n个字符

mysql> select left('Evilxr',4);
+------------------+
| left('Evilxr',4) |
+------------------+
| Evil             |
+------------------+
1 row in set (0.00 sec)

　　REVERSE(s) ----> 翻转字符串s的顺序

mysql> select reverse('cow') as evilxr;
+--------+
| evilxr |
+--------+
| woc    |
+--------+
1 row in set (0.00 sec)

3.日期和时间函数

　　CURDATE（），CURRENT——DATE（）返回系统当前日期

mysql> select curdate(),current_date();
+------------+----------------+
| curdate()  | current_date() |
+------------+----------------+
| 2014-10-10 | 2014-10-10     |
+------------+----------------+
1 row in set (0.00 sec)

　　curtime(),current_time() ----> 返回系统当前时间

mysql> select curtime(),current_time();
+-----------+----------------+
| curtime() | current_time() |
+-----------+----------------+
| 19:11:45  | 19:11:45       |
+-----------+----------------+
1 row in set (0.00 sec)

　　返回系统当前日期和时间的有如下5中方法：

mysql> select now(),current_timestamp(),localtime(),sysdate(),localtimestamp();
+---------------------+---------------------+---------------------+---------------------+---------------------+
| now()               | current_timestamp() | localtime()         | sysdate()           | localtimestamp()    |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2014-10-10 19:14:21 | 2014-10-10 19:14:21 | 2014-10-10 19:14:21 | 2014-10-10 19:14:21 | 2014-10-10 19:14:21 |
+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

　　返回日期中月份的值名称

mysql> select month(curdate()),monthname(curdate());
+------------------+----------------------+
| month(curdate()) | monthname(curdate()) |
+------------------+----------------------+
|               10 | October              |
+------------------+----------------------+
1 row in set (0.00 sec)

　　日期的复合查询

mysql> select curdate() as 系统日期,dayofweek(curdate()),dayname(curdate()),week(curdate()) as 第几个星期,dayofyear(curdate()) as 多少天,quarter(curdate()) as 季度;
+--------------+----------------------+--------------------+-----------------+-----------+--------+
| 系统日期      | dayofweek(curdate()) | dayname(curdate())  | 第几个星期       | 多少天    | 季度    |
+--------------+----------------------+--------------------+-----------------+-----------+--------+
| 2014-10-28   |                    3 | Tuesday            |              43 |       301 |      4 |
+--------------+----------------------+--------------------+-----------------+-----------+--------+

　　分别返回时间的小时分钟秒的值

mysql> select curtime(),hour(curtime()),minute(curtime()),second(curtime());
+-----------+-----------------+-------------------+-------------------+
| curtime() | hour(curtime()) | minute(curtime()) | second(curtime()) |
+-----------+-----------------+-------------------+-------------------+
| 19:44:52  |              19 |                44 |                52 |
+-----------+-----------------+-------------------+-------------------+
1 row in set (0.00 sec)

4.系统信息函数

　　系统信息函数用来查询Mysql数据库的系统信息，例如数据库的版本，数据库的当前用户等；

　　获取数据库的版本号

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.5.40-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.00 sec)

　　获取服务器的链接数

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              36 |
+-----------------+
1 row in set (0.00 sec)

　　获取当前的数据库名的两种方法

mysql> select database(),schema();
+------------+----------+
| database() | schema() |
+------------+----------+
| mysql      | mysql    |
+------------+----------+
1 row in set (0.00 sec)

　　获取系统用户的三种方法

mysql> select user(),system_user(),session_user();
+----------------+----------------+----------------+
| user()         | system_user()  | session_user() |
+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+
1 row in set (0.00 sec)

　　获取当前用户

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

5.加密函数

　　加密函数是Mysql中用来对数据进行加密的函数。因为数据库中有很多很敏感的信息不希望被他人看到，所以就可以通过加密的方式使这些数据变成看似乱码的数据

　　PASSWORD(str)---->对字符串进行加密经此函数加密后的数据使不可逆的，其经常用于对用户注册的密码进行加密处理。

mysql> select password ('Evilxr');
+-------------------------------------------+
| password ('Evilxr')                       |
+-------------------------------------------+
| *5142D0DB3649F9884798F7285BF68E1125C7A3F8 |
+-------------------------------------------+
1 row in set (0.00 sec)

　　MD5(str)---->对字符串str进行加密。通常用于对普通数据进行加密

mysql> select md5('Evilxr');
+----------------------------------+
| md5('Evilxr')                    |
+----------------------------------+
| ad24189018ed1cc7588f6f75e47f7bc8 |
+----------------------------------+
1 row in set (0.00 sec)

　　encode(str,pswd_str)---->使用字符串pswd_str来加密字符串str。加密的结果使一个二进制数，必须使用Blob类型的字段来保存它。

发表于 2014-10-10 22:49 西戎阅读(440) 评论(0) 编辑收藏举报

公告