---

一、常用的系统函数

1.1 数学函数

当然可以，下面是MySQL中一些常用的数学函数，按照不同的类别进行分类，并提供了简单的案例。每个函数的介绍和用法都在表格中，而示例则单独列出并换行。

函数名称	描述	用法
ABS(x)	返回x的绝对值	SELECT ABS(x);
CEILING(x)	返回大于或等于x的最小整数	SELECT CEILING(x);
FLOOR(x)	返回小于或等于x的最大整数	SELECT FLOOR(x);
MOD(x, y)	返回x除以y的余数	SELECT MOD(x, y);
ROUND(x, y)	返回x四舍五入到小数点后y位的结果	SELECT ROUND(x, y);
RAND()	返回一个0到1之间的随机浮点数	SELECT RAND();
POW(x, y)	返回x的y次幂	SELECT POW(x, y);

示例：

SELECT ABS(-10); -- 结果为10
SELECT CEILING(3.14); -- 结果为4
SELECT FLOOR(3.14); -- 结果为3
SELECT MOD(10, 3); -- 结果为1
SELECT ROUND(3.145, 2); -- 结果为3.15
SELECT RAND(); -- 结果可能是0.12345
SELECT POW(2, 3); -- 结果为8

1.2 字符串函数

1.2.1 计算字符串字符数的函数和计算字符串长度的函数

• CHAR_LENGTH(str) 或 CHARACTER_LENGTH(str): 返回字符串str的字符数。
• LENGTH(str): 返回字符串str的长度，以字节为单位。

示例：

SELECT CHAR_LENGTH('Hello World');  -- 返回11
SELECT LENGTH('Hello World');  -- 返回11（如果字符编码为单字节）

1.2.2 字符串合并函数

• CONCAT(str1, str2, ...): 将多个字符串参数首尾相连。

示例：

SELECT CONCAT('Hello', ' ', 'World');  -- 返回Hello World

1.2.3 字符串大小写转换函数

• LOWER(str) 或 LCASE(str): 将字符串str转换为小写。
• UPPER(str) 或 UCASE(str): 将字符串str转换为大写。

示例：

SELECT LOWER('Hello World');  -- 返回hello world
SELECT UPPER('Hello World');  -- 返回HELLO WORLD

1.2.4 删除空格函数

• TRIM(str): 去除字符串str两端的空格。
• LTRIM(str): 去除字符串str左端的空格。
• RTRIM(str): 去除字符串str右端的空格。

示例：

SELECT TRIM('   Hello World   ');  -- 返回Hello World
SELECT LTRIM('   Hello World   ');  -- 返回Hello World   
SELECT RTRIM('   Hello World   ');  -- 返回   Hello World

1.2.5 取子串函数

• SUBSTRING(str, pos) 或 SUBSTR(str, pos): 从字符串str中提取从位置pos开始到字符串末尾的子串。
• SUBSTRING(str, pos, len): 从字符串str中提取从位置pos开始长度为len的子串。

示例：

SELECT SUBSTRING('Hello World', 1);  -- 返回Hello World（从第一个字符开始）
SELECT SUBSTRING('Hello World', 1, 5);  -- 返回Hello（从第一个字符开始提取5个字符）

1.2.6 其他字符串函数

• REPLACE(str, search_str, replace_str): 在字符串str中替换search_str为replace_str。
• POSITION(search_str IN str) 或 INSTR(str, search_str): 返回search_str在str中的位置。
• LTRIM(str, char_set): 去除字符串str左端的指定字符集char_set中的字符。
• RTRIM(str, char_set): 去除字符串str右端的指定字符集char_set中的字符。
• REVERSE(str): 返回字符串str的反转字符串。

示例：

SELECT REPLACE('Hello World', 'World', 'MySQL');  -- 返回Hello MySQL
SELECT POSITION('World' IN 'Hello World');  -- 返回7（World在Hello World中的位置）
SELECT LTRIM('xxxHello World', 'x');  -- 返回Hello World
SELECT RTRIM('Hello Worldxxx', 'x');  -- 返回Hello World
SELECT REVERSE('Hello World');  -- 返回dlroW olleH

1.3 日期和时间函数

1.3.1 获取当前系统日期及指定日期年、月、日的函数

• CURDATE() 或 CURRENT_DATE(): 返回当前的日期，格式为YYYY-MM-DD。
• YEAR(date): 返回指定日期的年份。
• MONTH(date): 返回指定日期的月份（1到12）。
• DAY(date) 或 DAYOFMONTH(date): 返回指定日期的天数（1到31）。

示例：

SELECT CURDATE();  -- 返回当前日期，例如2024-10-28
SELECT YEAR('2024-10-28');  -- 返回2024
SELECT MONTH('2024-10-28');  -- 返回10
SELECT DAY('2024-10-28');  -- 返回28

1.3.2 获取当前系统日期时间的函数

• NOW(): 返回当前的日期和时间，格式为YYYY-MM-DD HH:MM:SS。
• CURRENT_TIME(): 返回当前的时间，格式为HH:MM:SS。
• UTC_TIMESTAMP(): 返回当前的UTC日期和时间。

示例：

SELECT NOW();  -- 返回当前日期和时间，例如2024-10-28 14:30:00
SELECT CURRENT_TIME();  -- 返回当前时间，例如14:30:00
SELECT UTC_TIMESTAMP();  -- 返回当前UTC时间，例如2024-10-28 06:30:00

1.3.3 其他日期和时间函数

• DATEDIFF(date1, date2): 返回两个日期之间的天数差。
• DATE_ADD(date, INTERVAL expr type): 在指定日期上加上一个时间间隔。
• DATE_SUB(date, INTERVAL expr type): 从指定日期减去一个时间间隔。
• LAST_DAY(date): 返回指定日期所在月份的最后一天。
• WEEKDAY(date): 返回指定日期是星期几（0表示星期一，6表示星期日）。

示例：

SELECT DATEDIFF('2024-10-28', '2024-10-01');  -- 返回27（两个日期之间的天数差）
SELECT DATE_ADD('2024-10-28', INTERVAL 10 DAY);  -- 返回2024-11-07（在当前日期上加10天）
SELECT DATE_SUB('2024-10-28', INTERVAL 10 DAY);  -- 返回2024-10-18（从当前日期减去10天）
SELECT LAST_DAY('2024-10-15');  -- 返回2024-10-31（2024年10月的最后一天）
SELECT WEEKDAY('2024-10-28');  -- 返回0（表示2024年10月28日是星期一）

1.4 系统信息函数

1.4.1 获取MySQL服务器版本号、用户名和数据库名的函数

• VERSION() 或 VERSION(): 返回MySQL服务器的版本号。

  SELECT VERSION();  -- 返回版本号，如8.0.32
  

• CONNECTION_ID(): 返回当前连接的唯一ID。

  SELECT CONNECTION_ID();  -- 返回连接ID，如487032
  

• DATABASE() 或 SCHEMA(): 返回当前选择的数据库名。

  SELECT DATABASE();  -- 返回当前数据库名
  

• USER(), CURRENT_USER(), SYSTEM_USER(), SESSION_USER(): 返回当前用户名称。

  SELECT USER();  -- 返回当前用户名称，如'username'@'host'
  

1.4.2 其他系统信息函数

• CHARSET(str) 或 CHARACTER_SET(str): 返回字符串str的字符集。

  SELECT CHARSET('text');  -- 返回字符串的字符集
  

• COLLATION(str): 返回字符串str的排序规则。

  SELECT COLLATION('text');  -- 返回字符串的排序规则
  

• FOUND_ROWS(): 返回上一条SELECT语句符合条件的行数（受LIMIT语句影响）。

  SELECT FOUND_ROWS();  -- 返回上一条查询符合条件的行数
  

• LAST_INSERT_ID(): 返回最后生成的AUTO_INCREMENT值。

  SELECT LAST_INSERT_ID();  -- 返回最后插入行的自增ID
  

1.5 条件判断函数

1.5.1 IF函数

IF(expr, val1, val2): 如果expr为真（非0，非NULL，非FALSE），则返回val1，否则返回val2。

示例：

SELECT IF(10 > 5, '真', '假');  -- 返回真

1.5.2 IFNULL()函数

IFNULL(val1, val2): 如果val1不为NULL，则返回val1，否则返回val2。

示例：

SELECT IFNULL(NULL, '默认值');  -- 返回默认值
SELECT IFNULL('非NULL值', '默认值');  -- 返回非NULL值

1.5.3 CASE函数

CASE expr WHEN condition THEN result [WHEN ... THEN ...] [ELSE result] END: 根据expr的值和condition的条件，返回相应的result。如果没有匹配的条件，且存在ELSE子句，则返回ELSE子句的结果。

示例：

SELECT CASEWHEN age < 18 THEN '儿童'WHEN age BETWEEN 18 AND 64 THEN '成人'ELSE '老年'
END
FROM users;  -- 根据年龄返回儿童、成人或老年

1.6 加密函数

要创建一个用户表并对其密码字段进行加密，你可以按照以下步骤操作：

• 创建用户表（user）：

CREATE TABLE user (id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL,u_pass VARCHAR(255) NOT NULL
);

• 插入数据时，对密码进行加密。这里我们可以使用SHA2()函数来进行加密，因为它提供了较强的安全性。以下是插入数据的示例：

-- 插入新用户
INSERT INTO user (username, u_pass)
VALUES ('newuser', SHA2('plainTextPassword', 256));

在这个例子中，'plainTextPassword'是你想要存储的明文密码，SHA2('plainTextPassword', 256)会生成一个SHA-256散列值，然后将这个散列值存储在u_pass字段中。

如果你想要使用MySQL的PASSWORD()函数来生成一个随机的、加盐的密码散列值，你需要确保你的MySQL版本支持这个函数（MySQL 5.7.6及以后版本）。以下是使用PASSWORD()函数的示例：

-- 插入新用户
INSERT INTO user (username, u_pass)
VALUES ('newuser', PASSWORD('plainTextPassword'));

请注意，PASSWORD()函数在不同版本的MySQL中可能有不同的行为，因此在实际应用中，你可能需要根据你的MySQL版本选择合适的加密方法。

在实际应用中，你还可能需要在用户登录时验证密码。这通常涉及到对用户输入的密码进行相同的散列处理，然后与数据库中存储的散列值进行比较。例如：

-- 验证用户登录
SELECT * FROM user
WHERE username = 'newuser' AND u_pass = SHA2('inputPassword', 256);

在这个例子中，'inputPassword'是用户输入的密码，SHA2('inputPassword', 256)会生成一个SHA-256散列值，然后与数据库中存储的散列值进行比较。

二、常量与变量

2.1 常量与变量

1. 常量（Constant）：

   • 在数据库中，常量通常指的是固定值，它们在查询或操作过程中不会改变。
   • 常量可以直接在SQL语句中使用，如数字、字符串等。

2. 变量（Variable）：

   • 数据库中的变量用于存储临时数据，它们可以在SQL语句中被声明和赋值。
   • 在不同的数据库系统中，变量的声明和使用方式可能有所不同。例如，在MySQL中，可以使用 SET @variable = value; 来声明和赋值一个变量。

2.2 存储函数的操作

1. 创建（Creation）：

   • 存储过程和函数需要被创建并存储在数据库中。创建时，需要定义输入参数、输出参数和返回类型（对于函数）。

2. 调用（Invocation）：

   • 一旦存储过程或函数被创建，它们可以通过特定的SQL命令被调用。调用时，可以传递参数给这些存储函数。

3. 参数传递（Parameter Passing）：

   • 存储过程和函数可以接收参数，这些参数在调用时传递给它们，用于控制存储函数的行为。

4. 执行（Execution）：

   • 存储函数中的SQL代码在数据库内部执行，可以包括数据查询、更新、插入和删除等操作。

5. 返回结果（Returning Results）：

   • 对于存储函数，它们可以返回一个值给调用者。对于存储过程，它们可能不返回值，或者通过输出参数返回值。

6. 错误处理（Error Handling）：

   • 存储函数可以包含错误处理逻辑，以处理执行过程中可能出现的错误。

7. 事务管理（Transaction Management）：

   • 存储过程可以包含事务控制语句，如 BEGIN TRANSACTION、COMMIT 和 ROLLBACK，以确保数据的一致性和完整性。

8. 权限和安全（Permissions and Security）：

   • 存储函数的创建和执行可能需要特定的权限，数据库管理员可以控制谁可以创建和执行这些存储函数。

9. 优化（Optimization）：

   • 存储函数可以被优化以提高性能，包括索引的使用、查询优化等。

10. 维护和调试（Maintenance and Debugging）：

    • 存储函数可能需要定期维护和调试，以确保它们按预期工作。

三、存储过程

3.1 存储过程概述

存储过程是一组为了完成特定功能的SQL语句集合，它被保存在数据库中，可以被多次调用执行。存储过程可以接收输入参数、输出参数，甚至可以不接收任何参数，也不返回任何值。它们的主要优点包括代码重用、减少网络流量、提高执行效率以及增强安全性。

3.2 创建存储过程 CREATE PROCEDURE 语句

以下是创建一个名为 apple_proc 的存储过程的SQL语句，该存储过程用于在 fruits 表中查询供应苹果（apple）的供应商编号 s_id：

DELIMITER $$CREATE PROCEDURE apple_proc()
BEGINSELECT s_id FROM fruits WHERE fruit_name = 'apple';
END $$DELIMITER ;

这里的 DELIMITER 关键字用于改变命令的结束符，因为存储过程内部可能包含多个语句，需要用不同的结束符来区分存储过程的结束。在本例中，我们将结束符临时更改为 $$，以便存储过程可以包含分号 ; 作为语句的结束符。存储过程结束后，我们将结束符改回默认的分号 ;。

3.3 调用存储过程（使用 CALL 语句）

一旦存储过程被创建，你可以使用 CALL 语句来执行它。以下是调用 apple_proc 存储过程的SQL语句：

CALL apple_proc();

这条语句会执行 apple_proc 存储过程，查询并返回 fruits 表中所有供应苹果的供应商编号 s_id。

3.4 存储过程的参数

存储过程中的参数用于传递数据给存储过程。参数可以是输入参数（IN）、输出参数（OUT）或者输入输出参数（INOUT）。在本例中，我们将创建一个带有IN参数的存储过程，用于向suppliers表中插入新记录。

3.4.1 IN参数和存储过程 s_in_proc()

以下是创建一个名为s_in_proc()的存储过程的SQL语句，该存储过程接受供应商的名称、地址和城市作为输入参数，并将新记录插入到suppliers表中：

DELIMITER $$CREATE PROCEDURE s_in_proc(IN supplier_name VARCHAR(255), IN supplier_address VARCHAR(255), IN supplier_city VARCHAR(255))
BEGININSERT INTO suppliers (name, address, city) VALUES (supplier_name, supplier_address, supplier_city);
END $$DELIMITER ;

在这个存储过程中，我们使用了IN参数来传递供应商的名称、地址和城市。VARCHAR(255)是参数的数据类型，表示字符串类型，最大长度为255个字符。

1. 调用存储过程 s_in_proc()

以下是调用s_in_proc()存储过程的SQL语句，假设我们要插入一个名为"New Supplier"的供应商，地址为"123 Main St"，城市为"New City"：

CALL s_in_proc('New Supplier', '123 Main St', 'New City');

2. 查看suppliers表验证存储过程s_in_proc()是否执行成功

为了验证存储过程是否成功执行，我们可以查询suppliers表，检查新插入的记录是否正确：

SELECT * FROM suppliers;

这条SQL语句将返回suppliers表中的所有记录，你可以通过检查结果来确认新供应商的记录是否已经成功插入。

3.4.2 OUT参数和存储过程 s_out_proc()

以下是创建一个名为s_out_proc()的存储过程的SQL语句，该存储过程接受一个供应商编号作为输入参数，并返回对应的供应商名称和电话作为输出参数：

DELIMITER $$CREATE PROCEDURE s_out_proc(IN supplier_id INT, OUT supplier_name VARCHAR(255), OUT supplier_phone VARCHAR(255))
BEGINSELECT name, phone INTO supplier_name, supplier_phone FROM suppliers WHERE s_id = supplier_id;
END $$DELIMITER ;

在这个存储过程中，supplier_id是输入参数，supplier_name和supplier_phone是输出参数。INT和VARCHAR(255)分别是参数的数据类型。SELECT ... INTO语句用于从suppliers表中检索供应商的名称和电话，并将它们存储在输出参数中。

调用存储过程 s_out_proc()

以下是调用s_out_proc()存储过程的SQL语句，假设我们要查询供应商编号为1的供应商的名称和电话：

CALL s_out_proc(1, @name, @phone);
SELECT @name, @phone;

在这里，我们使用@name和@phone作为会话级别的用户定义变量来接收输出参数的值，并使用SELECT语句来查看这些值。

3.4.3 INOUT参数和存储过程

以下是使用INOUT参数实现两个数交换的存储过程的SQL语句：

DELIMITER $$CREATE PROCEDURE swap_numbers(INOUT num1 INT, INOUT num2 INT)
BEGINSET @temp = num1;SET num1 = num2;SET num2 = @temp;
END $$DELIMITER ;

在这个存储过程中，num1和num2是INOUT参数，这意味着它们既可以作为输入也可以作为输出。我们使用了一个会话级别的用户定义变量@temp来临时存储num1的值，以便在交换过程中使用。

调用存储过程以交换两个数

以下是调用上述存储过程以交换两个数的SQL语句：

SET @a = 10;
SET @b = 20;
CALL swap_numbers(@a, @b);
SELECT @a, @b;

在这里，我们首先设置了两个会话级别的用户定义变量@a和@b，然后调用swap_numbers存储过程来交换它们的值，最后使用SELECT语句来查看交换后的结果。

3.5 删除存储过程 DROP PROCEDURE 的语法

在数据库中，如果你需要删除一个已经存在的存储过程，可以使用 DROP PROCEDURE 语句。以下是基本的语法：

DROP PROCEDURE IF EXISTS procedure_name;

这里的 procedure_name 是你想要删除的存储过程的名称。使用 IF EXISTS 选项可以避免在存储过程不存在时出现错误。

• 示例

假设你想要删除之前创建的 s_in_proc 存储过程，你可以使用以下语句：

DROP PROCEDURE IF EXISTS s_in_proc;

这条语句会安全地删除 s_in_proc 存储过程，如果它不存在，则不会引发错误。

3.6 存储过程与存储函数的区别

存储过程和存储函数都是数据库中预先编写好的SQL代码集合，但它们之间存在一些关键区别：

1. 返回值：

   • 存储过程：不返回值，或者可以通过输出参数返回多个值。
   • 存储函数：必须返回一个单一的值，这个值是函数的结果。

2. 调用方式：

   • 存储过程：可以通过 CALL 语句直接调用。
   • 存储函数：可以在SQL语句中被调用，像调用内置函数一样，例如在 SELECT 语句中。

3. 用途：

   • 存储过程：更适合执行需要一系列步骤的操作，如数据的批量更新、复杂的业务逻辑等。
   • 存储函数：更适合执行计算或返回单个值的操作，如计算字段值、数据转换等。

4. 事务控制：

   • 存储过程：可以在内部使用事务控制语句（如 BEGIN TRANSACTION、COMMIT、ROLLBACK）来管理事务。
   • 存储函数：通常不包含事务控制语句，因为它们通常被用作计算单个值。

5. 参数：

   • 存储过程和存储函数：都可以有输入参数（IN）、输出参数（OUT）和输入输出参数（INOUT）。

6. 性能：

   • 存储过程：通常比存储函数执行更快，因为它们不需要为每个行计算。
   • 存储函数：可能会在查询中为每个行调用，这可能会影响性能。

7. 重用和封装：

   • 存储过程和存储函数：都提供了代码重用和封装的好处，但存储过程更适合复杂的业务逻辑封装。

四、游标（Cursor）

游标是一种数据库对象，用于在存储过程中逐行处理查询结果集。游标允许你逐条检索结果集中的数据，这在处理大量数据时非常有用。

4.1 声明游标、游标的使用 DECLARE 语句

1. 声明游标：
   使用 DECLARE 语句声明游标，并指定查询结果集。游标通常在存储过程中声明。

   DECLARE cursor_name CURSOR FOR SELECT_statement;
   

   例如，假设我们有一个名为 employees 的表，我们想要逐行处理所有员工的信息：

   DECLARE emp_cursor CURSOR FOR SELECT * FROM employees;
   

2. 打开游标：
   使用 OPEN 语句打开游标，以便可以开始从结果集中提取数据。

   OPEN cursor_name;
   

   对于我们的 emp_cursor：

   OPEN emp_cursor;
   

3. 提取数据：
   使用 FETCH 语句从游标中提取数据。通常在一个循环中使用，直到检索完所有的数据。

   FETCH cursor_name INTO variable_list;
   

   例如：

   FETCH emp_cursor INTO @emp_id, @emp_name, @emp_salary;
   

   这里，@emp_id, @emp_name, @emp_salary 是用于存储提取数据的变量。

4. 关闭游标：
   使用 CLOSE 语句关闭游标，释放与游标相关的资源。

   CLOSE cursor_name;
   

   对于我们的 emp_cursor：

   CLOSE emp_cursor;
   

4.2 异常处理

在存储过程中使用游标时，异常处理非常重要，以确保在发生错误时能够正确地关闭游标并处理异常。

1. 声明异常处理器：
   使用 DECLARE 语句声明异常处理器。

   DECLARE CONTINUE HANDLER FOR condition_action;
   

   例如，我们可以声明一个异常处理器来处理没有更多行可提取的情况：

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   

   这里，done 是一个布尔变量，当没有更多行时，将其设置为 TRUE。

2. 完整的游标使用示例：

   DELIMITER $$CREATE PROCEDURE process_employees()
   BEGINDECLARE done INT DEFAULT FALSE;DECLARE emp_id INT;DECLARE emp_name VARCHAR(100);DECLARE emp_salary DECIMAL(10, 2);DECLARE emp_cursor CURSOR FOR SELECT id, name, salary FROM employees;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN emp_cursor;read_loop: LOOPFETCH emp_cursor INTO emp_id, emp_name, emp_salary;IF done THENLEAVE read_loop;END IF;-- 处理每行数据，例如更新或打印-- UPDATE some_table SET some_column = emp_salary WHERE some_id = emp_id;-- SELECT emp_name, emp_salary;END LOOP;CLOSE emp_cursor;
   END $$DELIMITER ;
   

   在这个存储过程中，我声明了一个游标 emp_cursor 来处理 employees 表中的每一行数据。我们使用了一个循环来提取数据，直到 FETCH 语句设置 done 为 TRUE，表示没有更多的行。我们还声明了一个异常处理器来处理 NOT FOUND 条件，这通常发生在 FETCH 操作没有找到更多的行时。