数据库——SQL Sever和MySQL各种函数的区别

以你之姓@ 2024-03-25 23:21 64阅读 0赞

**目录**

**一、标量函数（内置函数 & 自定义函数）**

**1、SQL Server 的标量函数基本语法如下：**

**2、MySQL 的标量函数则采用以下语法：**

**3、区别**

**二、存储过程**

**三、存储过程与标量函数的区别**

**四、分区函数（分区表）**

**五、触发器**

**六、表值函数**

**七、数据库中存储过程、标量函数、表值函数的区别**

--------------------

## **一、标量函数（内置函数 & 自定义函数）** ##

1.  **内置函数：**SQL Server预先定义了一些标量函数，这些可以直接在SQL查询中使用，例如LEN和LOWER。
2.  **用户定义函数：**SQL Server允许用户自定义标量函数，这些函数可以满足特定的需求，例如将日期格式化，将字符串转为大写等。

SQL Server：
    SELECT LEN('Hello World')  -- 内置函数
    MySQL：
    SELECT length('Hello World'); -- 内置函数

--------------------

### 1、SQL Server 的标量函数基本语法如下： ###

CREATE FUNCTION 函数名 (@参数名 数据类型)
    RETURNS 返回值数据类型
    AS
    BEGIN
        -- 函数体
        RETURN 返回值
    END

> 其中，`@``参数名` 表示函数的参数，多个参数用逗号隔开。`RETURNS` 表示函数的返回值类型。`RETURN` 用于返回函数结果。
> 
> 以下是一个 SQL Server 标量函数的例子，该函数实现计算圆的面积：

CREATE FUNCTION GetCircleArea (@r FLOAT)
    RETURNS FLOAT
    AS
    BEGIN
        DECLARE @PI FLOAT
        SET @PI = 3.14159265358979
        RETURN @PI * @r * @r
    END

--------------------

### 2、MySQL 的标量函数则采用以下语法： ###

CREATE FUNCTION 函数名 (参数名 数据类型)
    RETURNS返回值数据类型
    BEGIN
        -- 函数体
        RETURN 返回值
    END

> 类似 SQL Server 的语法，`参数名` 表示函数的参数，多个参数用逗号隔开。`RETURNS` 表示函数的返回值类型。`RETURN` 用于返回函数结果。
> 
> 以下是一个 MySQL 标量函数的例子，该函数实现计算圆的面积：

CREATE FUNCTION GetCircleArea (r FLOAT)
    RETURNS FLOAT
    BEGIN
        DECLARE PI FLOAT;
        SET PI = 3.14159265358979;
        RETURN PI * r * r;
    END

> ### 3、区别 ###
> 
> 这两者的区别在于它们创建函数的语法和一些函数语法上的细节。
> 
> 例如，在 SQL Server 中，DECLARE 语句必须在 BEGIN 语句之前，而在 MySQL 中，则可以将 DECLARE 语句放在 BEGIN 语句之中。此外，SQL Server 和 MySQL 在数据类型、函数返回类型等方面也会略有不同。

## 二、存储过程 ##

> SQL Server 存储过程用 T-SQL 语言编写，它可以接受参数、执行查询并返回结果集、例如：

CREATE PROCEDURE GetProducts
        @name nvarchar(50)
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT * FROM Products WHERE ProductName LIKE '%' + @name + '%';
    END;

> 上面的存储过程接受一个参数 @name，并根据参数查询 Products 表中的记录，并返回查询结果。
> 
> MySQL 存储过程用 SQL 语句和流程控制语句编写，例如：

DELIMITER $$
    
    CREATE PROCEDURE GetCustomers(IN country VARCHAR(50))
    BEGIN
        SELECT * FROM Customers WHERE Country = country;
    END $$
    
    DELIMITER ;

> 上面的存储过程接受一个参数 country，根据参数查询 Customers 表中的记录，并返回查询结果。
> 
> SQL Server 和 MySQL 存储过程的差异：
> 
> 1.  语法差异。SQL Server 存储过程使用 T-SQL 编写，MySQL 存储过程使用 SQL 语句和流程控制语句。
> 2.  数据类型差异。SQL Server 存储过程支持更多的数据类型，[如.NET][.NET]的数据类型等。
> 3.  存储过程调用方式。MySQL 存储过程的调用方式相对简单，只需要使用 CALL 存储过程名称 即可调用，而 SQL Server 则需要使用 EXECUTE 存储过程名称 或者 存储过程名称 参数名=参数值 的方式调用。
> 4.  错误处理差异。MySQL 存储过程使用 SIGNAL 语句抛出异常，而 SQL Server 则使用 RAISERROR 语句。
> 
> **总之，虽语法上有差异，但两者的目的是相同的，都是为了提高数据库的性能和安全性。**

--------------------

## 三、存储过程与标量函数的区别 ##

> **存储过程和标量函数**都是用户自定义的数据库对象。
> 
> 存储过程是一组经过编写、编译和存储在数据库中的SQL语句的集合，它们允许一组应用逻辑作为单个对象处理并存储在数据库服务器上。存储过程可以包含控制流语句和数据操作语句，允许所有用户按照同样的逻辑来访问和操作数据库。存储过程通常会返回值或输出参数，主要用于执行事务处理和批处理操作。
> 
> 标量函数是一种只返回单个值的函数，它接受一组输入参数，并根据输入参数计算结果，并返回计算的结果值。标量函数允许用户自定义一些重复使用的计算逻辑，并把这个逻辑保存在函数中。标量函数可以调用其他函数或使用其他数据库对象，如表、视图、存储过程等。
> 
> 总的来说，存储过程和标量函数的区别在于：
> 
> 存储过程是一组SQL语句的集合，主要用于执行事务处理和批处理操作；
> 
> 而标量函数是一个只返回单个值的函数，主要用于计算和查询操作。
> 
> 存储过程通常比标量函数更复杂，经常涉及到多个表和事务管理，而标量函数通常只包含简单的计算，并且具有更好的性能和可重用性。

## 四、分区函数（分区表） ##

> SQL Server 和 MySQL 都支持分区表，但是分区函数的写法是不同的。
> 
> 在SQL Server 中，分区函数可以通过创建分区方案和分区函数来实现。创建分区方案时需要指定分区函数，例如：

CREATE PARTITION FUNCTION my_partition_function (int)
    AS RANGE LEFT FOR VALUES (100, 200, 300)
    
    Create partition function my_partition(int)
    As range left for values(100,200,300)

> 这个语句创建了一个名为**"my\_partition\_function"** 的分区函数，它将表按照 int 类型的列进行分区，每个分区的**左边界分别为** **0、100、200 和 300。**
> 
> 在 MySQL 中，分区函数可以通过创建分区表时使用 **PARTITION BY** 子句来实现。例如：

CREATE TABLE my_partition_table (
        id INT,
        name VARCHAR(50),
        created_date DATE
    )
    PARTITION BY RANGE (YEAR(created_date)) (
        PARTITION p0 VALUES LESS THAN (2020),
        PARTITION p1 VALUES LESS THAN (2021),
        PARTITION p2 VALUES LESS THAN (2022),
        PARTITION p3 VALUES LESS THAN MAXVALUE
    );

> 这个语句创建了一个名为 "my\_partition\_table" 的分区表，它将表按照 created\_date 列的年份进行分区，分为 4 个分区，每个分区的右边界分别为 2019、2020、2021 和最大值。
> 
> 两者的差别在于语法不同，SQL Server 的分区函数可以指定较为复杂的分区逻辑，例如按照某个数值类型的列取模进行分区；而 MySQL 的分区函数则相对简单，只能按照某个列的值进行分区划分。

## 五、触发器 ##

> SQL Server触发器的写法：
> 
> 触发器用来对表中的数据变化进行捕捉和处理。创建触发器可以使用CREATE TRIGGER语句。
> 
> 创建一个简单的SQL Server触发器示例：

CREATE TRIGGER myTrigger
    ON myTable
    FOR INSERT, UPDATE
    AS
    BEGIN
        PRINT 'Trigger fired.'
        -- 在此处编写其他代码逻辑以进行处理
    END

> MySQL触发器的写法：
> 
> MySQL触发器与SQL Server的写法略微有些不同，MySQL使用CREATE TRIGGER语句来定义一个触发器。CREATE TRIGGER语句中的大多数元素与SQL Server触发器中的语句相同，但是，MySQL触发器还支持"BODIES"语句块，使其更加灵活。
> 
> 以下是创建一个新MySQL触发器的示例：

CREATE TRIGGER myTrigger
    AFTER INSERT ON myTable
    FOR EACH ROW
    BEGIN
        -- 在此处编写代码逻辑以进行处理
    END

> 这两种触发器的主要区别在于语法上的差异，因此它们之间并没有太多的操作和功能差异。然而，值得一提的是，两者的实现方式可能略有不同，因此在具体场景下可能需要注意一些差异，比如在MySQL中的触发器支持新旧行引用，而SQL Server中并不支持

## 六、表值函数 ##

> **SQL Server的表值函数：**
> 
> SQL Server的表值函数（Table-Valued Function）是一种返回表的函数，它可以与其他表和视图一样在查询中使用。**表值函数可以接受参数**，通常返回的表结构是根据参数的不同而有所变化。创建表值函数的语法如下：

CREATE FUNCTION function_name(@parameter1 datatype, @parameter2 datatype)
    RETURNS TABLE
    AS
    RETURN
    SELECT ...

> 例如，以下是一种返回某个部门所有成员的表值函数示例：

CREATE FUNCTION GetDepartmentEmployees (@DepartmentID int)
    RETURNS TABLE
    AS
    RETURN
    SELECT * FROM Employees WHERE DepartmentID = @DepartmentID

> 调用此函数的方法是：

SELECT * FROM GetDepartmentEmployees(3)

> 这里的 @DepartmentID 是 GetDepartmentEmployees 函数的参数，函数返回 DepartmentID 为 3 的所有 Employee。
> 
> MySQL的表值函数：
> 
> MySQL的表值函数（Table-Valued Function）是一种用户自定义的函数，返回虚拟表，可以被其他查询引用。**MySQL****没有直接支持表值函数，它使用的是临时表来模拟表值函数**的效果，如下所示：

CREATE TEMPORARY TABLE temp_table AS
    SELECT ...

> 临时表创建成功后，将它和 SELECT 语句的结果进行 JOIN 或 UNION，就可以在查询中使用。以下是一个返回部门所有成员的表值函数示例：

CREATE TEMPORARY TABLE DepartmentEmployees AS
    SELECT * FROM Employees WHERE DepartmentID = @DepartmentID
    
    SELECT * FROM DepartmentEmployees

> 调用此函数的方法是：

SET @DepartmentID = 3;
    CALL GetDepartmentEmployees(@DepartmentID);

> **这里的 @DepartmentID 是 GetDepartmentEmployees 函数的参数，函数返回 DepartmentID 为 3 的所有 Employee。**
> 
> 区别：
> 
> SQL Server 的表值函数和 MySQL 的表值函数作用类似，但它们有一些区别：
> 
> 1.  语法不同：SQL Server 的创建表值函数的语法是 CREATE FUNCTION，而 MySQL 使用 CREATE TEMPORARY TABLE。
> 2.  调用方式不同：SQL Server 在查询中通过函数名和参数直接调用，而 MySQL 是用符合 SQL 规范的 CALL 语法调用。
> 3.  返回值类型不同：SQL Server 的表值函数返回表，而 MySQL 的表值函数使用临时表模拟返回表。
> 4.  传递参数的方式不同：SQL Server 可以像其他函数一样使用参数，MySQL 的表值函数需要使用 CALL 语法传递参数。
> 
> **总之，表值函数可以大大简化编写复杂查询的工作。**
> 
> **虽然每种 DBMS 的语法和用法不同，但它们都能够达到相同的效果。**

#### **七、数据库中存储过程、标量函数、表值函数的区别** ####

> **存储过程、标量函数和表值函数是数据库中的三种常见的可复用代码体**
> 
> 它们的主要区别如下：
> 
> 1.  存储过程（Stored Procedure）：是存储在数据库中的一些 SQL 语句的集合，可以在任何时候被调用执行，是一种可被重复调用的封装。
> 2.  标量函数（Scalar Function）：是一个可以接受一个或多个输入参数，并根据这些参数返回一个标量值（如一个字符串、一个数字等）的 SQL 工具。标量函数可在 SELECT 语句、WHERE 子句和 HAVING 子句中使用。
> 3.  表值函数（Table Valued Function）：是一个能返回一个表格（类似于数据库表格）的 SQL 工具，类似于标量函数，表值函数也可以接受一个或多个输入参数。表值函数分为两种类型：内联表值函数和多语句表值函数。前者返回一组用于 SELECT 语句的结果行，而后者则是针对大量数据执行一些操作后返回结果集。
> 
> 总体而言，存储过程比标量函数和表值函数复杂，但对于一些需求较为复杂的业务逻辑，存储过程的优势更明显；而标量函数和表值函数又十分适合需要以常规方式多次查询数据（比如数据的分类、排序、统计等）的场景，其可以大大提高查询和处理数据的效率和速度。

[.NET]: http://xn--bvs.net/