MySQL、SQL SERVER、Oracle、PostgreSQL常用内置函数

╰+哭是因爲堅強的太久メ 2023-10-12 09:34 45阅读 0赞

#### 一、MySQL常用内置函数 ####

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>用法</th> 
   <th>举例</th> 
   <th>注意事项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONCAT</td> 
   <td>CONCAT(str1,str2,…)</td> 
   <td>SELECT CONCAT(“Hello”, “World”);</td> 
   <td>参数必须为字符串类型</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING</td> 
   <td>SUBSTRING(str, start, length)</td> 
   <td>SELECT SUBSTRING(“Hello World”, 1, 5);</td> 
   <td>start从1开始计算</td> 
  </tr> 
  <tr> 
   <td>IFNULL</td> 
   <td>IFNULL(expr1, expr2)</td> 
   <td>SELECT IFNULL(NULL, “Hello”);</td> 
   <td>只有第一个参数可能为NULL</td> 
  </tr> 
  <tr> 
   <td>COUNT</td> 
   <td>COUNT(expr)</td> 
   <td>SELECT COUNT(*) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>SUM</td> 
   <td>SUM(expr)</td> 
   <td>SELECT SUM(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>AVG</td> 
   <td>AVG(expr)</td> 
   <td>SELECT AVG(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>MAX</td> 
   <td>MAX(expr)</td> 
   <td>SELECT MAX(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>MIN</td> 
   <td>MIN(expr)</td> 
   <td>SELECT MIN(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>RAND</td> 
   <td>RAND()</td> 
   <td>SELECT RAND();</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>NOW</td> 
   <td>NOW()</td> 
   <td>SELECT NOW();</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

官方文档地址：[MySQL 8.0 Reference Manual][]

#### 二、SQL Server常用内置函数 ####

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>用法</th> 
   <th>举例</th> 
   <th>注意事项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>LEN</td> 
   <td>LEN(string_expression)</td> 
   <td>SELECT LEN(“Hello World”);</td> 
   <td>返回字符数而不是字节数</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING</td> 
   <td>SUBSTRING(string_expression, start, length)</td> 
   <td>SELECT SUBSTRING(“Hello World”, 1, 5);</td> 
   <td>start从1开始计算</td> 
  </tr> 
  <tr> 
   <td>COALESCE</td> 
   <td>COALESCE (expression1, expression2, expression_n)</td> 
   <td>SELECT COALESCE(NULL, “Hello”);</td> 
   <td>只有第一个参数可能为NULL</td> 
  </tr> 
  <tr> 
   <td>COUNT</td> 
   <td>COUNT(expr)</td> 
   <td>SELECT COUNT(*) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>SUM</td> 
   <td>SUM(expr)</td> 
   <td>SELECT SUM(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>AVG</td> 
   <td>AVG(expr)</td> 
   <td>SELECT AVG(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>MAX</td> 
   <td>MAX(expr)</td> 
   <td>SELECT MAX(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>MIN</td> 
   <td>MIN(expr)</td> 
   <td>SELECT MIN(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>RAND</td> 
   <td>RAND()</td> 
   <td>SELECT RAND();</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>GETDATE</td> 
   <td>GETDATE()</td> 
   <td>SELECT GETDATE();</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

官方文档地址：[SQL Server Functions][]

#### 三、Oracle常用内置函数 ####

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>用法</th> 
   <th>举例</th> 
   <th>注意事项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONCAT</td> 
   <td>CONCAT(string1, string2)</td> 
   <td>SELECT CONCAT(‘Hello’, ‘World’);</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>SUBSTR</td> 
   <td>SUBSTR(string, start_position, length)</td> 
   <td>SELECT SUBSTR(“Hello World”, 1, 5);</td> 
   <td>start_position从1开始计算</td> 
  </tr> 
  <tr> 
   <td>NVL</td> 
   <td>NVL(expr1, expr2)</td> 
   <td>SELECT NVL(NULL, “Hello”);</td> 
   <td>只有第一个参数可能为NULL</td> 
  </tr> 
  <tr> 
   <td>COUNT</td> 
   <td>COUNT(expr)</td> 
   <td>SELECT COUNT(*) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>SUM</td> 
   <td>SUM(expr)</td> 
   <td>SELECT SUM(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>AVG</td> 
   <td>AVG(expr)</td> 
   <td>SELECT AVG(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>MAX</td> 
   <td>MAX(expr)</td> 
   <td>SELECT MAX(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>MIN</td> 
   <td>MIN(expr)</td> 
   <td>SELECT MIN(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>ROUND</td> 
   <td>ROUND(expr, n)</td> 
   <td>SELECT ROUND(3.1415926, 2);</td> 
   <td>n表示小数点后保留的位数</td> 
  </tr> 
 </tbody> 
</table>

官方文档地址：[Oracle Database SQL Language Reference][]

#### 四、 PostgreSQL常用内置函数 ####

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>用法</th> 
   <th>举例</th> 
   <th>注意事项</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONCAT</td> 
   <td>CONCAT(string1, string2)</td> 
   <td>SELECT CONCAT(‘Hello’, ‘World’);</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>SUBSTRING</td> 
   <td>SUBSTRING(string FROM start FOR length)</td> 
   <td>SELECT SUBSTRING(“Hello World” FROM 1 FOR 5);</td> 
   <td>start从1开始计算</td> 
  </tr> 
  <tr> 
   <td>COALESCE</td> 
   <td>COALESCE (expression1, expression2, expression_n)</td> 
   <td>SELECT COALESCE(NULL, “Hello”);</td> 
   <td>只有第一个参数可能为NULL</td> 
  </tr> 
  <tr> 
   <td>COUNT</td> 
   <td>COUNT(expr)</td> 
   <td>SELECT COUNT(*) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>SUM</td> 
   <td>SUM(expr)</td> 
   <td>SELECT SUM(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>AVG</td> 
   <td>AVG(expr)</td> 
   <td>SELECT AVG(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>MAX</td> 
   <td>MAX(expr)</td> 
   <td>SELECT MAX(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>MIN</td> 
   <td>MIN(expr)</td> 
   <td>SELECT MIN(score) FROM student;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>ROUND</td> 
   <td>ROUND(expr, n)</td> 
   <td>SELECT ROUND(3.1415926, 2);</td> 
   <td>n表示小数点后保留的位数</td> 
  </tr> 
 </tbody> 
</table>

官方文档地址：[PostgreSQL 13.3 Documentation][]

注意事项：

*  每个数据库的内置函数都有特定的使用方式和参数要求，需要根据具体情况选择合适的函数。建议在使用时参考官方文档，以避免出现错误。
 *  数据库内置函数的名称和用法可能在不同的数据库版本中有所不同，需要根据具体版本选择相应的文档。

[MySQL 8.0 Reference Manual]: https://dev.mysql.com/doc/refman/8.0/en/functions.html
[SQL Server Functions]: https://docs.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-ver15
[Oracle Database SQL Language Reference]: https://docs.oracle.com/database/121/SQLRF/functions.htm#SQLRF006
[PostgreSQL 13.3 Documentation]: https://www.postgresql.org/docs/13/functions.html