Mysql常用函数集合

拼搏现实的明天。 2023-09-30 08:41 81阅读 0赞

# 1、数值函数 #

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
   <th>样例</th> 
   <th>结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>CEIL(n)</code></td> 
   <td>大于或等于数值n的最小整数</td> 
   <td>SELECT CEIL(10.6);</td> 
   <td>11</td> 
  </tr> 
  <tr> 
   <td><code>FLOOR(n)</code></td> 
   <td>小于等于数值n的最大整数</td> 
   <td>SELECT FLOOR (10.6);</td> 
   <td>10</td> 
  </tr> 
  <tr> 
   <td><code>MOD(m,n)</code></td> 
   <td>m除以n的余数,若n=0,则返回m</td> 
   <td>SELECT MOD (7,5);</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td><code>POWER(m,n)</code></td> 
   <td>m的n次方</td> 
   <td>SELECT POWER (3,2);</td> 
   <td>9</td> 
  </tr> 
  <tr> 
   <td><code>ROUND(n,m)</code></td> 
   <td>将n四舍五入,保留小数点后m位</td> 
   <td>SELECT ROUND (1234.5678,2);</td> 
   <td>1234.57</td> 
  </tr> 
  <tr> 
   <td><code>SIGN(n)</code></td> 
   <td>若n=0,则返回0,否则,n&gt;0,则返回1,n&lt;0,则返回-1</td> 
   <td>SELECT SIGN (12);</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td><code>SQRT(n)</code></td> 
   <td>n的平方根</td> 
   <td>SELECT SQRT (9);</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

2、常用字符函数

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
   <th>样例</th> 
   <th>结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>LOWER(char)</code></td> 
   <td>整个字符串换成小写</td> 
   <td>SELECT LOWER (‘MR.ecop’)</td> 
   <td>mr.ecop</td> 
  </tr> 
  <tr> 
   <td><code>REPLACE(char,str1,str2)</code></td> 
   <td>字符串中所有str1换成str2</td> 
   <td>SELECT REPLACE (‘Scott’,‘S’,‘Boy’)</td> 
   <td>Boycott</td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
   <th>样例</th> 
   <th>结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>LOWER(char)</code></td> 
   <td>整个字符串换成小写</td> 
   <td>SELECT LOWER (‘MR.ecop’)</td> 
   <td>mr.ecop</td> 
  </tr> 
  <tr> 
   <td><code>LENGTH(char)</code></td> 
   <td>求字符串的长度</td> 
   <td>SELECT LENGTH (‘ACD’)</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>``</td> 
   <td>====</td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>