hopeless-dream

导航

MySQL内置函数-单行函数（字符函数）

字符函数  

length 获取字节量

## 查看英文的字节长度
mysql> select length('haha');
+----------------+
| length('haha') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

## 查看中文的字节长度
mysql> select length('哈哈');
+------------------+
| length('哈哈')   |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)


## 查看表中某一列的字节长度
mysql> select length(first_name) as len from employees group by len ;
+-----+
| len |
+-----+
|   6 |
|   7 |
|   5 |
|   9 |
|   4 |
|   8 |
|  10 |
|   3 |
|  11 |
|  12 |
|  13 |
|  14 |
+-----+
12 rows in set (1.65 sec)

查看emoji表情的字节长度

concat函数  拼接字符串

mysql> select concat("我是","中国","人");
+---------------------------------+
| concat("我是","中国","人")      |
+---------------------------------+
| 我是中国人                      |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select concat(first_name ," 的生日是： ",birth_date) from employees limit 10;
+----------------------------------------------------+
| concat(first_name ," 的生日是： ",birth_date)      |
+----------------------------------------------------+
| Georgi 的生日是： 1953-09-02                       |
| Bezalel 的生日是： 1964-06-02                      |
| Parto 的生日是： 1959-12-03                        |
| Chirstian 的生日是： 1954-05-01                    |
| Kyoichi 的生日是： 1955-01-21                      |
| Anneke 的生日是： 1953-04-20                       |
| Tzvetan 的生日是： 1957-05-23                      |
| Saniya 的生日是： 1958-02-19                       |
| Sumant 的生日是： 1952-04-19                       |
| Duangkaew 的生日是： 1963-06-01                    |
+----------------------------------------------------+
10 rows in set (0.00 sec)

备份数据库语句的拼接

mysql> select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," > /bak/",table_schema,"_",table_name,".sql") from informatiion_schema.tables where table_schema='world';
+-------------------------------------------------------------------------------------------------------------+
| concat("mysqldump -uroot -p123 ",table_schema," ",table_name," > /bak/",table_schema,"_",table_name,".sql") |
+-------------------------------------------------------------------------------------------------------------+
| mysqldump -uroot -p123 world City > /bak/world_City.sql                                                     |
| mysqldump -uroot -p123 world Country > /bak/world_Country.sql                                               |
| mysqldump -uroot -p123 world CountryLanguage > /bak/world_CountryLanguage.sql                               |
+-------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

upper && lower 大小写转换

mysql> select first_name from employees limit 10;
+------------+
| first_name |
+------------+
| Georgi     |
| Bezalel    |
| Parto      |
| Chirstian  |
| Kyoichi    |
| Anneke     |
| Tzvetan    |
| Saniya     |
| Sumant     |
| Duangkaew  |
+------------+
10 rows in set (0.00 sec)

mysql> select upper(first_name) from employees limit 10;
+-------------------+
| upper(first_name) |
+-------------------+
| GEORGI            |
| BEZALEL           |
| PARTO             |
| CHIRSTIAN         |
| KYOICHI           |
| ANNEKE            |
| TZVETAN           |
| SANIYA            |
| SUMANT            |
| DUANGKAEW         |
+-------------------+
10 rows in set (0.11 sec)

mysql> select lower(first_name) from employees limit 10;
+-------------------+
| lower(first_name) |
+-------------------+
| georgi            |
| bezalel           |
| parto             |
| chirstian         |
| kyoichi           |
| anneke            |
| tzvetan           |
| saniya            |
| sumant            |
| duangkaew         |
+-------------------+
10 rows in set (0.00 sec)

substr 截取字符串

mysql> select substr(birth_date,1,4) year from employees limit 10;
+------+
| year |
+------+
| 1953 |
| 1964 |
| 1959 |
| 1954 |
| 1955 |
| 1953 |
| 1957 |
| 1958 |
| 1952 |
| 1963 |
+------+
10 rows in set (0.00 sec)

mysql> select substr(birth_date,6) date from employees limit 10;
+-------+
| date  |
+-------+
| 09-02 |
| 06-02 |
| 12-03 |
| 05-01 |
| 01-21 |
| 04-20 |
| 05-23 |
| 02-19 |
| 04-19 |
| 06-01 |
+-------+
10 rows in set (0.00 sec)

instr 返回字符串首次出现的索引，没有找到就返回0

mysql> select instr(birth_date,'80') from employees limit 10;
+------------------------+
| instr(birth_date,'80') |
+------------------------+
|                      0 |
|                      0 |
|                      0 |
|                      0 |
|                      0 |
|                      0 |
|                      0 |
|                      0 |
|                      0 |
|                      0 |
+------------------------+
10 rows in set (0.00 sec)

mysql> select instr(birth_date,'19') from employees limit 10;
+------------------------+
| instr(birth_date,'19') |
+------------------------+
|                      1 |
|                      1 |
|                      1 |
|                      1 |
|                      1 |
|                      1 |
|                      1 |
|                      1 |
|                      1 |
|                      1 |
+------------------------+
10 rows in set (0.00 sec)
mysql> select id,instr(name,"qingdao") as a from City where CountryCode ='CHN' having a>0;
+------+---+
| id   | a |
+------+---+
| 1903 | 1 |
+------+---+
1 row in set (0.01 sec)

trim 去掉行首和行尾的指定字符，默认为空格

mysql> select trim('hello' from 'hello world') as test;
+--------+
| test   |
+--------+
|  world |
+--------+
1 row in set (0.00 sec)

mysql> select trim('hello' from ' hello world') as test;
+--------------+
| test         |
+--------------+
|  hello world |
+--------------+
1 row in set (0.00 sec)

mysql> select trim('world' from ' hello world') as test;
+---------+
| test    |
+---------+
|  hello  |
+---------+
1 row in set (0.00 sec)

mysql> select trim('world' from ' hello world ') as test;
+---------------+
| test          |
+---------------+
|  hello world  |
+---------------+
1 row in set (0.00 sec)

Lpad 左填充

mysql> select concat(lpad(floor(rand()*24),2,0),':',lpad(floor(rand()*60),2,0),':',lpad(floor(rand()*60),2,0))d ;
+----------+
| d        |
+----------+
| 05:00:23 |
+----------+
1 row in set (0.00 sec)

mysql> select concat(lpad(floor(rand()*24),2,0),':',lpad(floor(rand()*60),2,0),':',lpad(floor(rand()*60),2,0))d ;
+----------+
| d        |
+----------+
| 22:33:54 |
+----------+
1 row in set (0.00 sec)

rpad 右侧填充

replace 替换字符串

mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 5a87e51c-aac4-11ea-b4fc-000c295e277d |
+--------------------------------------+
1 row in set (0.01 sec)

mysql> select replace(uuid(),'-','');
+----------------------------------+
| replace(uuid(),'-','')           |
+----------------------------------+
| bc8c03aeaac411eab4fc000c295e277d |
+----------------------------------+
1 row in set (0.00 sec)

数学函数

round 四舍五入

mysql> select round(10.105);
+---------------+
| round(10.105) |
+---------------+
|            10 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(10.10569,3);
+-------------------+
| round(10.10569,3) |
+-------------------+
|            10.106 |
+-------------------+
1 row in set (0.00 sec)

ceil 向上取整

mysql> select ceil(-3.12);
+-------------+
| ceil(-3.12) |
+-------------+
|          -3 |
+-------------+
1 row in set (0.00 sec)

mysql> select ceil(3.12);
+------------+
| ceil(3.12) |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql> select ceil(3.00);
+------------+
| ceil(3.00) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)

floor 向下取整

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

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

mysql> select floor(-3.12);
+--------------+
| floor(-3.12) |
+--------------+
|           -4 |
+--------------+
1 row in set (0.00 sec)

truncate 截取浮点数小数点后的位数

mysql> select truncate(3.1415,3);
+--------------------+
| truncate(3.1415,3) |
+--------------------+
|              3.141 |
+--------------------+
1 row in set (0.00 sec)

mod 取模

mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select mod(-10,3);
+------------+
| mod(-10,3) |
+------------+
|         -1 |
+------------+
1 row in set (0.00 sec)

rand 取随机数

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

mysql> select rand()*10;
+-------------------+
| rand()*10         |
+-------------------+
| 8.334071122421019 |
+-------------------+
1 row in set (0.00 sec)

mysql> select floor(rand()*10);
+------------------+
| floor(rand()*10) |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

coalesce 转换null值

Null 不会等于或不等于任何值，甚至不能与其自身进行比较，使用如 COALESCE 这样的函数把 Null 转换为一个具体的、可以用于标准评估的值。COALESCE 函数会返回参数列表里的第一个非 Null 值
mysql> create table if not exists  t2 (id int(10),comm int(10));
Query OK, 0 rows affected, 3 warnings (0.00 sec)

mysql> insert into t2 values(1,null),(2,15),(3,18),(4,25),(5,null);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+------+
| id   | comm |
+------+------+
|    1 | NULL |
|    2 |   15 |
|    3 |   18 |
|    4 |   25 |
|    5 | NULL |
+------+------+
5 rows in set (0.00 sec)

mysql> select id,coalesce(comm,0) as a from t2 having a<1;
+------+---+
| id   | a |
+------+---+
|    1 | 0 |
|    5 | 0 |
+------+---+
2 rows in set (0.00 sec)

 

posted on 2020-06-10 11:41  hopeless-dream  阅读(375)  评论(0)  编辑  收藏  举报