软件测试修炼之路 A Tester

MySQL-运算符和函数

2016-07-19
i.itest.ren
数据库
 

字符函数

  • CONCAT(): 字符连接
  • CONCAT_WS(): 使用指定的分隔符进行字符连接
  • FORMAT(): 数字格式化
  • LOWER(): 转换成小写字母
  • UPPER(): 转换成大写字母
  • LEFT(): 获取左侧字符
  • RIGHT(): 获取右侧字符
  • LENGTH(): 获取字符串长度
  • LTRIM(): 删除前导空格
  • RTRIM(): 删除后续空格
  • TRIM(): 删除前导和后续空格
  • SUBSTRING(): 字符串获取
  • [NOT] LIKE: 模式匹配
  • REPLACE(): 字符串替换

示例：

SELECT CONCAT('imooc', 'MySQL');

 mysql> SELECT CONCAT('imooc','-', 'MySQL');
+------------------------------+
| CONCAT('imooc','-', 'MySQL') |
+------------------------------+
| imooc-MySQL                  |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS('-', 'imooc', 'MySQL', 'Function');
+----------------------------------------------+
| CONCAT_WS('-', 'imooc', 'MySQL', 'Function') |
+----------------------------------------------+
| imooc-MySQL-Function                         |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(12560.75, 1);
+---------------------+
| FORMAT(12560.75, 1) |
+---------------------+
| 12,560.8            |
+---------------------+
1 row in set (0.02 sec)

mysql> SELECT LOWER('MySQL');
+----------------+
| LOWER('MySQL') |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER('MySQL');
+----------------+
| UPPER('MySQL') |
+----------------+
| MYSQL          |
+----------------+
1 row in set (0.05 sec)

mysql> SELECT LEFT('MySQL', 2);
+------------------+
| LEFT('MySQL', 2) |
+------------------+
| My               |
+------------------+
1 row in set (0.05 sec)

mysql> SELECT LOWER(LEFT('MySQL', 2));
+-------------------------+
| LOWER(LEFT('MySQL', 2)) |
+-------------------------+
| my                      |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT RIGHT('MySQL', 2);
+-------------------+
| RIGHT('MySQL', 2) |
+-------------------+
| QL                |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH('My SQL');
+------------------+
| LENGTH('My SQL') |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(LTRIM('   MySQL   '));
+------------------------------+
| LENGTH(LTRIM('   MySQL   ')) |
+------------------------------+
|                            8 |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(TRIM('   MySQL   '));
+-----------------------------+
| LENGTH(TRIM('   MySQL   ')) |
+-----------------------------+
|                           5 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(LEADING '?' FROM '??MySQL???');
+-------------------------------------+
| TRIM(LEADING '?' FROM '??MySQL???') |
+-------------------------------------+
| MySQL???                            |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(TRAILING '?' FROM '??MySQL???');
+--------------------------------------+
| TRIM(TRAILING '?' FROM '??MySQL???') |
+--------------------------------------+
| ??MySQL                              |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT TRIM(BOTH '?' FROM '??MySQL???');
+----------------------------------+
| TRIM(BOTH '?' FROM '??MySQL???') |
+----------------------------------+
| MySQL                            |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REPLACE('??My??SQL???', '?', '');
+----------------------------------+
| REPLACE('??My??SQL???', '?', '') |
+----------------------------------+
| MySQL                            |
+----------------------------------+
1 row in set (0.02 sec)

mysql> SELECT SUBSTRING('MySQL', 1,2);
+-------------------------+
| SUBSTRING('MySQL', 1,2) |
+-------------------------+
| My                      |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('MySQL', 3);
+-----------------------+
| SUBSTRING('MySQL', 3) |
+-----------------------+
| SQL                   |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('MySQL', -1);
+------------------------+
| SUBSTRING('MySQL', -1) |
+------------------------+
| L                      |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT 'MySQL' LIKE 'M%';
+-------------------+
| 'MySQL' LIKE 'M%' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

注意：

  • %(百分号)： 代表任意个字符
  • _(下划线)： 代表任意一个字符

数值运算符和函数

  • CEIL(): 进一取整
  • DIV(): 整数除法
  • FLOOR(): 舍一取整
  • MOD(): 取余数（取模）
  • POWER(): 幂运算
  • ROUND(): 四舍五入
  • TRUNCATE(): 数字截取

示例：

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

mysql> SELECT CEIL(3.01);
+------------+
| CEIL(3.01) |
+------------+
|          4 |
+------------+
1 row in set (0.05 sec)

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

mysql> SELECT 3/4;
+--------+
| 3/4    |
+--------+
| 0.7500 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 3 DIV 4;
+---------+
| 3 DIV 4 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT 5 % 3;
+-------+
| 5 % 3 |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

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

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

mysql> SELECT POWER(3,2);
+------------+
| POWER(3,2) |
+------------+
|          9 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(3.625, 2);
+-----------------+
| ROUND(3.625, 2) |
+-----------------+
|            3.63 |
+-----------------+
1 row in set (0.00 sec)

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

mysql> SELECT TRUNCATE(125.89, 1);
+---------------------+
| TRUNCATE(125.89, 1) |
+---------------------+
|               125.8 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(125.89, 0);
+---------------------+
| TRUNCATE(125.89, 0) |
+---------------------+
|                 125 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(125.89, -1);
+----------------------+
| TRUNCATE(125.89, -1) |
+----------------------+
|                  120 |
+----------------------+
1 row in set (0.00 sec)

比较运算符和函数

  • [NOT] BETWEEN…AND…: [不]在范围之内
  • [NOT] IN(): [不]在列出值范围内
  • IS [NOT] NULL: [不]为空

日期时间函数

  • NOW(): 当前日期和时间
  • CURDATE(): 当前日期
  • CURTIME(): 当前时间
  • DATE_ADD(): 日期变化
  • DATEDIFF(): 日期差值
  • DATE_FORMAT(): 日期格式化

示例：

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2016-07-26 |
+------------+
1 row in set (0.03 sec)

mysql> SELECT DATE_ADD('2016-07-26', INTERVAL 365 DAY);
+------------------------------------------+
| DATE_ADD('2016-07-26', INTERVAL 365 DAY) |
+------------------------------------------+
| 2017-07-26                               |
+------------------------------------------+
1 row in set (0.10 sec)


mysql> SELECT DATE_ADD('2016-07-26', INTERVAL -365 DAY);
+-------------------------------------------+
| DATE_ADD('2016-07-26', INTERVAL -365 DAY) |
+-------------------------------------------+
| 2015-07-27                                |
+-------------------------------------------+
1 row in set (0.05 sec)

mysql> SELECT DATE_ADD('2016-07-26', INTERVAL 1 YEAR);
+-----------------------------------------+
| DATE_ADD('2016-07-26', INTERVAL 1 YEAR) |
+-----------------------------------------+
| 2017-07-26                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2016-07-26', INTERVAL 3 WEEK);
+-----------------------------------------+
| DATE_ADD('2016-07-26', INTERVAL 3 WEEK) |
+-----------------------------------------+
| 2016-08-16                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF('2015-07-26', '2016-07-26');
+--------------------------------------+
| DATEDIFF('2015-07-26', '2016-07-26') |
+--------------------------------------+
|                                 -366 |
+--------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT DATE_FORMAT('2016-07-26', '%m/%d/%Y');
+---------------------------------------+
| DATE_FORMAT('2016-07-26', '%m/%d/%Y') |
+---------------------------------------+
| 07/26/2016                            |
+---------------------------------------+
1 row in set (0.06 sec)

信息函数

  • CONNECTION_ID(): 连接ID
  • DATEBASE(): 当前数据库
  • LAST_INSERT_ID(): 最后插入记录的ID号
  • USER(): 当前用户
  • VERSION(): 版本信息

示例：

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

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

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.13    |
+-----------+
1 row in set (0.02 sec)

聚合函数

  • AVG(): 平均值
  • COUNT(): 计数
  • MAX(): 最大值
  • MIN(): 最小值
  • SUM(): 求和

加密函数

  • MD5(): 信息摘要算法
  • PASSWORD(): 密码算法

示例：

mysql> SELECT MD5('123456');
+----------------------------------+
| MD5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT PASSWORD('123456');
+-------------------------------------------+
| PASSWORD('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

上一篇 MySQL-子查询和连接

下一篇 MySQL-自定义函数

Comments

Content