MySQL管理与优化(4)_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL管理与优化(4)_MySQL,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7564字,纯文字阅读大概需要11分钟。
内容图文
![MySQL管理与优化(4)_MySQL](/upload/InfoBanner/zyjiaocheng/579/b9f8ee0ca40342f6a72b4feeeed4568f.jpg)
常用函数
字符串函数:
- 常用的字符串函数
- CONCAT(s1, s2, ...):依次连接字符串。
mysql> SELECT CONCAT('aa', 'bb'), CONCAT('abc', 'de', 'fgh');+--------------------+----------------------------+| CONCAT('aa', 'bb') | CONCAT('abc', 'de', 'fgh') |+--------------------+----------------------------+| aabb | abcdefgh |+--------------------+----------------------------+
- INSERT(str, x, y, instr):将str从x位置开始的y个字符串替换为instr,索引从1开始。
mysql> SELECT INSERT('ilike2014', 2, 4, 'enjoy');+------------------------------------+| INSERT('ilike2014', 2, 4, 'enjoy') |+------------------------------------+| ienjoy2014 |+------------------------------------+
- LPAD(str, n, pad):str长度不足n,则左填充pad。
mysql> SELECT LPAD('XXX', 12, '01');+-----------------------+| LPAD('XXX', 12, '01') |+-----------------------+| 010101010XXX |+-----------------------+
- SUBSTRING(str, x, y): 求子串str[x..y],索引从1开始。
mysql> SELECT SUBSTRING('abcdefg', 1, 3);+----------------------------+| SUBSTRING('abcdefg', 1, 3) |+----------------------------+| abc |+----------------------------+
数值函数:
- MySQL常用的数值函数:
范例:
mysql> SELECT ABS(0.8), ABS(-0.8), CEIL(-0.8), CEIL(0.8), FLOOR(-0.8), FLOOR(0.8), MOD(15,10), MOD(1,11), MOD(NULL,10);+----------+-----------+------------+-----------+-------------+------------+------------+-----------+--------------+| ABS(0.8) | ABS(-0.8) | CEIL(-0.8) | CEIL(0.8) | FLOOR(-0.8) | FLOOR(0.8) | MOD(15,10) | MOD(1,11) | MOD(NULL,10) |+----------+-----------+------------+-----------+-------------+------------+------------+-----------+--------------+| 0.8 | 0.8 | 0 | 1 | -1 | 0 | 5 | 1 | NULL |+----------+-----------+------------+-----------+-------------+------------+------------+-----------+--------------+mysql> SELECT ROUND(1.235, 2), TRUNCATE(1.235, 2);+-----------------+--------------------+| ROUND(1.235, 2) | TRUNCATE(1.235, 2) |+-----------------+--------------------+| 1.24 | 1.23 |+-----------------+--------------------+
日期和时间函数:
- 常用的日期时间函数:
范例:
-- 列举当前时间mysql> SELECT CURDATE(), CURTIME(), NOW();+------------+-----------+---------------------+| CURDATE() | CURTIME() | NOW() |+------------+-----------+---------------------+| 2014-06-12 | 11:54:22 | 2014-06-12 11:54:22 |+------------+-----------+---------------------+-- 返回Unix时间戳mysql> SELECT UNIX_TIMESTAMP(now());+-----------------------+| UNIX_TIMESTAMP(now()) |+-----------------------+| 1402545326 |+-----------------------+-- 返回Unix时间戳对应的日期mysql> SELECT UNIX_TIMESTAMP(now());+-----------------------+| UNIX_TIMESTAMP(now()) |+-----------------------+| 1402545326 |+-----------------------+-- 现在是哪周,哪年mysql> SELECT WEEK(now()), YEAR(now());+-------------+-------------+| WEEK(now()) | YEAR(now()) |+-------------+-------------+| 23 | 2014 |+-------------+-------------+
- MySQL中的日期时间格式化:
如:
mysql> SELECT DATE_FORMAT(now(), '%Y-%m-%d %H:%m:%s');+-----------------------------------------+| DATE_FORMAT(now(), '%Y-%m-%d %H:%m:%s') |+-----------------------------------------+| 2014-06-12 12:06:13 |+-----------------------------------------+
- 有关时间计算
MySQL中的日期时间间隔类型有:
范例:
mysql> SELECT now() current, DATE_ADD(now(), INTERVAL 31 DAY) after31days, DATE_ADD(now(), INTERVAL -7 DAY) before7days, DATE_ADD(now(), INTERVAL '1_2' YEAR_MONTH) after_1year_2month;+---------------------+---------------------+---------------------+---------------------+| current | after31days | before7days | after_1year_2month |+---------------------+---------------------+---------------------+---------------------+| 2014-06-12 22:48:49 | 2014-07-13 22:48:49 | 2014-06-05 22:48:49 | 2015-08-12 22:48:49 |+---------------------+---------------------+---------------------+---------------------+
流程函数:
- MySQL中的流程函数:
范例:
-- 初始化数据mysql> CREATE TABLE salary (userid int, salary decimal(9, 2));Query OK, 0 rows affected (0.06 sec)mysql> INSERT INTO salary VALUES(1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (1, NULL);Query OK, 6 rows affected (0.02 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> SELECT * FROM salary;+--------+---------+| userid | salary |+--------+---------+| 1 | 1000.00 || 2 | 2000.00 || 3 | 3000.00 || 4 | 4000.00 || 5 | 5000.00 || 1 | NULL |+--------+---------+-- 根据薪水高低判断薪水mysql> SELECT IF(salary>2000, 'high', 'low') FROM salary;+--------------------------------+| IF(salary>2000, 'high', 'low') |+--------------------------------+| low || low || high || high || high || low |+--------------------------------+-- IFNULL使用mysql> SELECT IFNULL(salary, 0) FROM salary;+-------------------+| IFNULL(salary, 0) |+-------------------+| 1000.00 || 2000.00 || 3000.00 || 4000.00 || 5000.00 || 0.00 |+-------------------+-- CASE WHEN使用mysql> SELECT CASE salary WHEN 1000 THEN 'low' WHEN 2000 THEN 'mid' ELSE 'high' end FROM salary;+-----------------------------------------------------------------------+| CASE salary WHEN 1000 THEN 'low' WHEN 2000 THEN 'mid' ELSE 'high' end |+-----------------------------------------------------------------------+| low || mid || high || high || high || high |+-----------------------------------------------------------------------+
其他常用函数:
- MySQL中其他常用函数:
范例:
mysql> SELECT DATABASE(), VERSION(), USER();+------------+-----------+----------------+| DATABASE() | VERSION() | USER() |+------------+-----------+----------------+| test | 5.6.14 | root@localhost |+------------+-----------+----------------+mysql> SELECT DATABASE(), VERSION(), USER();+------------+-----------+----------------+| DATABASE() | VERSION() | USER() |+------------+-----------+----------------+| test | 5.6.14 | root@localhost |+------------+-----------+----------------+mysql> SELECT INET_ATON('192.168.141.129');+------------------------------+| INET_ATON('192.168.141.129') |+------------------------------+| 3232271745 |+------------------------------+-- 41位加密密码mysql> SELECT PASSWORD('111111');+-------------------------------------------+| PASSWORD('111111') |+-------------------------------------------+| *FD571203974BA9AFE270FE62151AE967ECA5E0AA |+-------------------------------------------+-- MD5值计算mysql> SELECT MD5('111111');+----------------------------------+| MD5('111111') |+----------------------------------+| 96e79218965eb72c92a549dd5a330112 |+----------------------------------+MySQL更多函数可参考:
http://dev.mysql.com/doc/refman/5.7/en/functions.html
不吝指正。
内容总结
以上是互联网集市为您收集整理的MySQL管理与优化(4)_MySQL全部内容,希望文章能够帮你解决MySQL管理与优化(4)_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。