目录
三 使用INET_ATON函数将字符串网络点地址转换为数值网络地址
四 使用INET_NTOA函数将数值网络地址转换为字符串网络点地址
一 使用FORMAT函数格式化数字,保留小数点位数为指定值
mysql> SELECT FORMAT(12332.123456, 4), FORMAT(12332.1,4), FORMAT(12332.2,0);
+-------------------------+-------------------+-------------------+
| FORMAT(12332.123456, 4) | FORMAT(12332.1,4) | FORMAT(12332.2,0) |
+-------------------------+-------------------+-------------------+
| 12,332.1235 | 12,332.1000 | 12,332 |
+-------------------------+-------------------+-------------------+
1 row in set (0.01 sec)
二 使用CONV函数在不同进制数值之间转换
SELECT CONV('a',16,2),
CONV(15,10,2),
CONV(15,10,8),
CONV(15,10,16);
+----------------+---------------+---------------+----------------+
| CONV('a',16,2) | CONV(15,10,2) | CONV(15,10,8) | CONV(15,10,16) |
+----------------+---------------+---------------+----------------+
| 1010 | 1111 | 17 | F |
+----------------+---------------+---------------+----------------+
1 row in set (0.00 sec)
三 使用INET_ATON函数将字符串网络点地址转换为数值网络地址
mysql> SELECT INET_ATON('209.207.224.40');
+-----------------------------+
| INET_ATON('209.207.224.40') |
+-----------------------------+
| 3520061480 |
+-----------------------------+
1 row in set (0.00 sec)
3520061480 = 209*256^3+207*256^2+224*256+40
四 使用INET_NTOA函数将数值网络地址转换为字符串网络点地址
mysql> SELECT INET_NTOA(3520061480);
+-----------------------+
| INET_NTOA(3520061480) |
+-----------------------+
| 209.207.224.40 |
+-----------------------+
1 row in set (0.00 sec)
五 使用加锁、解锁函数
SELECT GET_LOCK('lock1',10) AS GetLock,
IS_USED_LOCK('lock1') AS ISUsedLock,
IS_FREE_LOCK('lock1') AS ISFreeLock,
RELEASE_LOCK('lock1') AS ReleaseLock;
+---------+------------+------------+-------------+
| GetLock | ISUsedLock | ISFreeLock | ReleaseLock |
+---------+------------+------------+-------------+
| 1 | 2 | 0 | 1 |
+---------+------------+------------+-------------+
1 row in set (0.00 sec)
六 使用BENCHMARK重复执行指定函数
首先,使用PASSWORD函数加密密码
mysql> SELECT PASSWORD ( 'newpwd' );
+-------------------------------------------+
| PASSWORD ( 'newpwd' ) |
+-------------------------------------------+
| *1FA85AA204CC12B39B20E8F1E839D11B3F9E6AA4 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
可以看到,PASSWORD执行花费时间为0.00sec,下面使用BENCHMARK函数重复执行PASSWORD操作500000次:
mysql> SELECT BENCHMARK( 500000, PASSWORD ('newpwd') );
+------------------------------------------+
| BENCHMARK( 500000, PASSWORD ('newpwd') ) |
+------------------------------------------+
| 0 |
+------------------------------------------+
1 row in set, 1 warning (0.06 sec)
七 使用CONVERT()函数改变字符串的默认字符集
mysql> SELECT CHARSET('string'), CHARSET(CONVERT('string' USING latin1));
+-------------------+-----------------------------------------+
| CHARSET('string') | CHARSET(CONVERT('string' USING latin1)) |
+-------------------+-----------------------------------------+
| utf8 | latin1 |
+-------------------+-----------------------------------------+
1 row in set (0.01 sec)
八 使用CAST和CONVERT函数进行数据类型的转换
mysql> SELECT CAST(100 AS CHAR(2)), CONVERT('2010-10-01 12:12:12',TIME);
+----------------------+-------------------------------------+
| CAST(100 AS CHAR(2)) | CONVERT('2010-10-01 12:12:12',TIME) |
+----------------------+-------------------------------------+
| 10 | 12:12:12 |
+----------------------+-------------------------------------+
1 row in set, 1 warning (0.01 sec)
九 综合应用
--数据函数
SELECT ABS(-8); /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/
--字符串函数
SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
SELECT CONCAT('我','爱','程序'); /*合并字符串,参数可以有多个*/
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); /*替换字符串,从某个位置开始替换某个长度*/
SELECT LOWER('KuangShen'); /*小写*/
SELECT UPPER('KuangShen'); /*大写*/
SELECT LEFT('hello,world',5); /*从左边截取*/
SELECT RIGHT('hello,world',5); /*从右边截取*/
SELECT REPLACE('狂神说坚持就能成功','坚持','努力'); /*替换字符串*/
SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
SELECT REVERSE('狂神说坚持就能成功'); /*反转
-- 查询姓周的同学,改成邹
SELECT REPLACE(studentname,'周','邹') AS 新名字
FROM student WHERE studentname LIKE '周%';
--日期和时间函数
SELECT CURRENT_DATE(); /*获取当前日期*/
SELECT CURDATE(); /*获取当前日期*/
SELECT NOW(); /*获取当前日期和时间*/
SELECT LOCALTIME(); /*获取当前日期和时间*/
SELECT SYSDATE(); /*获取当前日期和时间*/
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
--系统信息函数
SELECT VERSION(); /*版本*/
SELECT USER(); /*用户*/