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)
相关推荐
苏康申 2020-11-13
vitasfly 2020-11-12
CoderToy 2020-11-16
wangshuangbao 2020-11-13
要啥自行车一把梭 2020-11-12
emmm00 2020-11-17
王艺强 2020-11-17
aydh 2020-11-12
世樹 2020-11-11
zry 2020-11-11
URML 2020-11-11
spurity 2020-11-10
yifangs 2020-10-13
Andrea0 2020-09-18
Ida 2020-09-16
ltd00 2020-09-12
tufeiax 2020-09-03
xjd0 2020-09-10
greatboylc 2020-09-10