Mysql数据处理函数/单行处理函数

1、数据/单行处理函数

mysql> select user_name,over,age,lower(English_name) from user1;-- lower()转换小写
+-----------+----------+-----+---------------------+
| user_name | over     | age | lower(English_name) |
+-----------+----------+-----+---------------------+
| 孙悟空    | 齐天大圣 | 100 | hi                  |
| 沙僧      | 金身罗汉 | 900 | hi                  |
| 唐僧      | 功德佛   | 900 | hi                  |
| 猪八戒    | 净坛使者 | 900 | hi                  |
+-----------+----------+-----+---------------------+
4 rows in set (0.01 sec)

mysql> select user_name,over,age,upper(English_name) from user1;-- upper()转换小写
+-----------+----------+-----+---------------------+
| user_name | over     | age | upper(English_name) |
+-----------+----------+-----+---------------------+
| 孙悟空    | 齐天大圣 | 100 | HI                  |
| 沙僧      | 金身罗汉 | 900 | HI                  |
| 唐僧      | 功德佛   | 900 | HI                  |
| 猪八戒    | 净坛使者 | 900 | HI                  |
+-----------+----------+-----+---------------------+
4 rows in set (0.01 sec)

mysql> select user_name,over,age,substr(English_name,1,1) as `截取的字符串` from user1;-- substr()截取的，下标从1开始
+-----------+----------+-----+--------------+
| user_name | over     | age | 截取的字符串 |
+-----------+----------+-----+--------------+
| 孙悟空    | 齐天大圣 | 100 | H            |
| 沙僧      | 金身罗汉 | 900 | H            |
| 唐僧      | 功德佛   | 900 | H            |
| 猪八戒    | 净坛使者 | 900 | H            |
+-----------+----------+-----+--------------+
4 rows in set (0.00 sec)

mysql> select user_name,over,age,lower(substr(English_name,1,length(English_name))) as `首字母小写` from user1;-- 首字母小写
+-----------+----------+-----+------------+
| user_name | over     | age | 首字母小写 |
+-----------+----------+-----+------------+
| 孙悟空    | 齐天大圣 | 100 | hi         |
| 沙僧      | 金身罗汉 | 900 | hi         |
| 唐僧      | 功德佛   | 900 | hi         |
| 猪八戒    | 净坛使者 | 900 | hi         |
+-----------+----------+-----+------------+
4 rows in set (0.00 sec)

mysql> select length(English_name) as `字符串长度` from user1;-- 获取字符串长度
+------------+
| 字符串长度 |
+------------+
|          2 |
|          2 |
|          2 |
|          2 |
+------------+
4 rows in set (0.00 sec)

mysql> select * from user1 where over=trim("      齐天大圣");-- trim()去空格
+----+-----------+----------+-----+--------------+
| id | user_name | over     | age | English_name |
+----+-----------+----------+-----+--------------+
|  1 | 孙悟空    | 齐天大圣 | 100 | Hi           |
+----+-----------+----------+-----+--------------+
1 row in set (0.01 sec)

mysql> select round(1234.567,0) as num from user1;-- select 字面值（数据），后面的0代表保留整数位
+------+
| num  |
+------+
| 1235 |
| 1235 |
| 1235 |
| 1235 |
+------+
4 rows in set (0.00 sec)

mysql> select rand() from user1;-- 生成随机数
+---------------------+
| rand()              |
+---------------------+
|   0.489278456651865 |
| 0.11995117936278803 |
| 0.13192055759199015 |
| 0.29974928060523165 |
+---------------------+
4 rows in set (0.01 sec)

mysql> select user_name,age+salary as sum from user1;-- 有null值参与的运算结果都是null
+-----------+-------+
| user_name | sum   |
+-----------+-------+
| 孙悟空    |  NULL |
| 沙僧      |  NULL |
| 唐僧      | 19900 |
| 猪八戒    |  NULL |
+-----------+-------+
4 rows in set (0.00 sec)

mysql> select user_name,age+salary as sum from user1;-- 有null值参与的运算结果都是null
+-----------+-------+
| user_name | sum   |
+-----------+-------+
| 孙悟空    |  NULL |
| 沙僧      |  NULL |
| 唐僧      | 19900 |
| 猪八戒    |  NULL |
+-----------+-------+
4 rows in set (0.00 sec)

mysql> select user_name,age+ifnull(salary,0) as sum from user1;-- ifnull()函数用于避免结果为null的情况出现，指定如果salary的值为null，则将其值规定为0
+-----------+-------+
| user_name | sum   |
+-----------+-------+
| 孙悟空    |   100 |
| 沙僧      |   900 |
| 唐僧      | 19900 |
| 猪八戒    |   900 |
+-----------+-------+
4 rows in set (0.01 sec)

mysql> select user_name, (case when user_name="唐僧" then age*1.5 when user_name="孙悟空" then age*2 else age end) newage from user1;
+-----------+--------+
| user_name | newage |
+-----------+--------+
| 孙悟空    |    200 |
| 沙僧      |    900 |
| 唐僧      | 1350.0 |
| 猪八戒    |    900 |
+-----------+--------+
4 rows in set (0.01 sec)

mysql> -- case when...then...when...then...else...end 当出现什么情况的时候，规定什么，否则按照常规值执行，case可以模拟if语句来进行执行