十二、MySQL基础系列笔记之常用函数-字符串与数值函数

在MySQL数据库中，函数可以用在SELECT语句及其子句（如where、order by、having等）中，也可以用在update、delete语句及其子句中。

字符串函数

字符串函数是最常用的一种函数，如下：

concat(s1,s2...)-拼接字符串

注意：任何字符串与NULL进行连接的结果都为NULL。

insert(str,x,y,instr)-字符串替换-将字符串str中所有字符串变为小写

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span>(<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'ziruchu.com'</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">9</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">3</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'自如初'</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+---------------------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">insert</span>(<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'ziruchu.com'</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">9</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">3</span>,<span style="box-sizing: border-box;color: rgb(152, 195, 121) !important">'自如初'</span>)    |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+---------------------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| ziruchu.自如初                        |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+---------------------------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

数值函数

ceil与floor

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> ceil(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.1</span>),ceil(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.5</span>),floor(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.1</span>),floor(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.5</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+-----------+-----------+------------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| ceil(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.1</span>) | ceil(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.5</span>) | floor(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.1</span>) | floor(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.5</span>) |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+-----------+-----------+------------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">|         <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span> |         <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span> |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> |          <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+-----------+-----------+------------+------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

truncate与round的区别在于truncate是截断，而round进行了四舍五入

<span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">select</span> round(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.244</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>),round(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.245</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>),<span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">truncate</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.245</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>);</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----------------+----------------+-------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">| round(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.244</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>) | round(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.245</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>) | <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">truncate</span>(<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.245</span>,<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">2</span>) |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----------------+----------------+-------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">|           <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.24</span> |           <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.25</span> |              <span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1.24</span> |</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px">+----------------+----------------+-------------------+</span><br></br><span style="box-sizing: border-box;color: rgb(171, 178, 191);padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">1</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">row</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">in</span> <span style="box-sizing: border-box;color: rgb(198, 120, 221) !important">set</span> (<span style="box-sizing: border-box;color: rgb(209, 154, 102) !important">0.00</span> sec)</span>

我是小白，期待和优秀的你一起同行!

小白

2020年10月15日