MySQL 视图、函数和存储过程

红太狼 2023-10-09 19:52 4阅读 0赞

MySQL 是一种流行的关系型数据库管理系统，其具有强大的功能和灵活性，使其成为了许多企业和个人喜爱的数据库选择。在 MySQL 中，视图、函数和存储过程是常见的数据库对象，它们都有助于提高数据的处理效率和可重用性。

![在这里插入图片描述][a7eeaf9ca2404b34a4748fd63503033f.png]

## 一、视图 ##

视图是一种虚拟表，它是根据 SQL 语句检索出的结果集，与物理表不同的是，它并不在数据库中实际存在。视图的作用是为了简化复杂的查询，将多表关联和过滤操作集中到一个视图中，然后通过查询该视图来获取需要的结果。视图具有以下几个特点：

视图不存储数据，而是根据 SELECT 语句的结果动态生成的；  
视图只能读取，不能写入；  
视图可以基于一个或多个表创建。  
下面是一个视图的创建示例：

CREATE VIEW vw_employee AS SELECT
    e.emp_no,
    e.first_name,
    e.last_name,
    d.dept_name 
    FROM
    	employees e
    	JOIN departments d ON e.dept_no = d.dept_no;

该语句创建了一个名为 vw\_employee 的视图，它包含了 employees 和 departments 两个表中的数据，可以用以下语句查询该视图：

SELECT * FROM vw_employee;

## 二、函数 ##

函数是一种可重用的程序单元，它封装了一段特定的逻辑，可以用于完成特定的任务。在 MySQL 中，函数分为两类：标量函数和聚合函数。标量函数返回单个值，而聚合函数返回一个聚合值，如 COUNT、SUM、AVG 等。函数具有以下几个特点：

函数具有输入和输出，可以接收参数并返回结果；  
函数可以调用其他函数；  
函数可以嵌套调用。  
下面是一个标量函数的创建示例：

CREATE FUNCTION get_employee_name ( emp_no INT ) RETURNS VARCHAR ( 50 ) BEGIN
    	DECLARE
    		emp_name VARCHAR ( 50 );
    	SELECT
    		CONCAT_WS( ' ', first_name, last_name ) INTO emp_name 
    	FROM
    		employees 
    	WHERE
    		emp_no = emp_no;
    RETURN emp_name;
    END;

该语句创建了一个名为 get\_employee\_name 的标量函数，它接收一个员工编号，返回该员工的姓名。  
调用该函数：

SELECT get_employee_name (100001);

## 三、存储过程 ##

存储过程是一组预定义的 SQL 语句集合，它们被封装在一个单元内，可以被重复调用。存储过程可以接收输入参数和输出参数，它们具有以下几个特点：

存储过程可以包含多条 SQL 语句，可以完成复杂的任务；  
存储过程可以在服务器端执行，减少网络传输的开销；  
存储过程可以被多个应用程序共享。  
下面是一个使用存储过程的示例，假设我们有以下三个表：

employees 表：存储员工的基本信息

CREATE TABLE employees (
      emp_no INT PRIMARY KEY,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      gender ENUM('M', 'F'),
      birth_date DATE,
      hire_date DATE
    );

插入一些数据：

INSERT INTO employees VALUES
    (10001, 'Georgi', 'Facello', 'M', '1953-09-02', '1986-06-26'),
    (10002, 'Bezalel', 'Simmel', 'F', '1964-06-02', '1985-11-21'),
    (10003, 'Parto', 'Bamford', 'M', '1959-12-03', '1986-08-28'),
    (10004, 'Chirstian', 'Koblick', 'M', '1955-01-05', '1986-12-01'),
    (10005, 'Kyoichi', 'Maliniak', 'M', '1959-09-12', '1989-09-12'),
    (10006, 'Anneke', 'Preusig', 'F', '1953-04-20', '1989-06-02'),
    (10007, 'Tzvetan', 'Zielinski', 'F', '1957-05-23', '1989-02-10');

departments 表：存储部门的基本信息

CREATE TABLE departments (
      dept_no CHAR(4) PRIMARY KEY,
      dept_name VARCHAR(50)
    );

插入一些数据：

INSERT INTO departments VALUES
    ('d001', 'Marketing'),
    ('d002', 'Finance'),
    ('d003', 'Human Resources'),
    ('d004', 'Production'),
    ('d005', 'Development'),
    ('d006', 'Quality Management'),
    ('d007', 'Sales'),
    ('d008', 'Research');

dept\_emp 表：存储员工与部门的关系

CREATE TABLE dept_emp (
      emp_no INT,
      dept_no CHAR(4),
      from_date DATE,
      to_date DATE,
      PRIMARY KEY (emp_no, dept_no)
    );

插入一些数据：

INSERT INTO dept_emp VALUES
    (10001, 'd001', '1986-06-26', '9999-01-01'),
    (10002, 'd001', '1985-11-21', '9999-01-01'),
    (10003, 'd002', '1986-08-28', '9999-01-01'),
    (10004, 'd005', '1986-12-01', '9999-01-01'),
    (10005, 'd005', '1989-09-12', '9999-01-01'),
    (10006, 'd006', '1989-06-02', '9999-01-01'),
    (10007, 'd007', '1989-02-10', '9999-01-01');

现在，我们可以创建一个存储过程来查询某个部门中的员工数量和员工详细信息：

CREATE PROCEDURE get_employee_by_dept ( IN dept_name VARCHAR ( 50 ), OUT employee_count INT ) BEGIN
    	SELECT
    		COUNT(*) INTO employee_count 
    	FROM
    		employees e
    		JOIN dept_emp de ON e.emp_no = de.emp_no
    		JOIN departments d ON de.dept_no = d.dept_no 
    	WHERE
    		d.dept_name = dept_name;
    	SELECT
    		e.emp_no,
    		e.first_name,
    		e.last_name,
    		e.gender,
    		e.birth_date,
    		e.hire_date 
    	FROM
    		employees e
    		JOIN dept_emp de ON e.emp_no = de.emp_no
    		JOIN departments d ON de.dept_no = d.dept_no 
    	WHERE
    	d.dept_name = dept_name;
    END;

该语句创建了一个名为 get\_employee\_by\_dept 的存储过程，它接收一个部门名称作为输入参数，并返回该部门中的员工数量和员工详细信息。

调用该函数

CALL get_employee_by_dept('Development', @employee_count);
    SELECT @employee_count;

在实际应用中，视图、函数和存储过程都可以发挥重要的作用。例如，在一个复杂的企业应用中，可能需要从多个表中获取数据，并对其进行过滤和聚合操作，这时可以使用视图来简化复杂的查询。另外，如果有一些常用的业务逻辑需要重复使用，可以将其封装为函数或存储过程，以提高代码的重用性和可维护性。

总之，视图、函数和存储过程都是 MySQL 中非常重要的数据库对象，它们可以提高数据库的效率和可重用性，使开发人员更加高效地处理数据。在实际应用中，可以根据具体的业务需求，灵活地使用这些对象，以提高数据处理的效率和质量。

[a7eeaf9ca2404b34a4748fd63503033f.png]: https://img-blog.csdnimg.cn/a7eeaf9ca2404b34a4748fd63503033f.png