html5单页网站模板,logo设计公司成都,苏州网站营销公司,优秀产品设计案例分析存储过程与函数
MySQL 中的存储过程和存储函数是一种在数据库服务器上存储复杂逻辑的方式#xff0c;允许您封装和重用 SQL 代码。它们在管理复杂的数据库操作和提高性能方面非常有用。以下是对它们的详细介绍#xff1a;
存储过程#xff08;Stored Procedures#xff0…存储过程与函数
MySQL 中的存储过程和存储函数是一种在数据库服务器上存储复杂逻辑的方式允许您封装和重用 SQL 代码。它们在管理复杂的数据库操作和提高性能方面非常有用。以下是对它们的详细介绍
存储过程Stored Procedures
存储过程是一组预先编译好的 SQL 语句。它们可以执行复杂的业务逻辑能够接受参数、执行 SQL 语句并返回结果。 优点: 封装性可以封装复杂的逻辑。性能减少了网络通信量因为不需要多次发送 SQL 语句。安全性能够实现更细粒度的访问控制。维护性在服务器端维护方便修改和维护。 创建存储过程: DELIMITER //
CREATE PROCEDURE procedure_name(parameters)
--注意在这里存储过程的参数可以有in out inout三种类型选择输入输出
BEGIN-- SQL statements
END //
DELIMITER ;调用存储过程: CALL procedure_name(arguments);存储过程示例 假设我们要创建一个存储过程用来插入新的记录到某个表中并打印一个消息。 假设有一个表 students具有列 id主键自增、name 和 age。 首先创建 students 表 CREATE TABLE students (id INT AUTO_INCREMENT,name VARCHAR(100),age INT,PRIMARY KEY (id)
);然后创建存储过程 DELIMITER //
CREATE PROCEDURE add_student(IN student_name VARCHAR(100), IN student_age INT)
BEGININSERT INTO students (name, age) VALUES (student_name, student_age);SELECT Student added successfully! AS message;
END //
DELIMITER ;这个存储过程接受两个参数学生的名字和年龄将它们插入到 students 表中并返回一条消息表示操作成功。 调用该存储过程的示例 CALL add_student(John Doe, 20);这将在 students 表中添加一个名为 ‘John Doe’、年龄为 20 的新记录并返回消息 ‘Student added successfully!’。
存储函数Stored Functions
当然可以。我将提供一个简单的实际例子展示一个存储函数和一个存储过程在 MySQL 中是如何定义和使用的。 优点: 可用于表达式可以在 SQL 查询中直接调用。返回值必须返回一个值可以是标量值或表类型。 创建存储函数: DELIMITER //
CREATE FUNCTION function_name(parameters)
RETURNS data_type --确定返回的类型
BEGIN-- SQL statementsRETURN value;
END //
DELIMITER ;调用存储函数: SELECT function_name(arguments);存储函数示例 假设我们要创建一个存储函数用来计算给定数字的平方。 DELIMITER //
CREATE FUNCTION square_number (num INT)
RETURNS INT
BEGINRETURN num * num;
END //
DELIMITER ;这个函数接受一个整数参数 num然后返回这个数的平方。 使用该函数的示例 SELECT square_number(5);这将返回 25。
区别
返回值存储过程不需要返回值而存储函数必须返回一个值。调用方式存储过程使用 CALL 语句调用存储函数可以直接在 SQL 表达式中调用。用途存储过程更适合执行复杂的业务逻辑存储函数更适合进行计算并返回结果。目的存储过程主要执行并完成某个功能存储函数主要用于计算并返回一个函数值
注释、定界符与语句块
MySQL 支持单行和多行注释 单行注释 以 -- 双破折号后跟一个空格开头直到行尾。以 # 开头直到行尾。 示例 -- 这是一个单行注释
# 这也是一个单行注释多行注释 以 /* 开始以 */ 结束。 示例 /* 这是一个多行注释 */在 MySQL 中默认的命令定界符是分号;。但在编写存储过程或函数时经常需要更改定界符以允许在过程或函数体内使用分号。 更改定界符 使用 DELIMITER 命令更改定界符。常见的做法是使用 // 或 $$ 作为新的定界符。 定界符一般都是默认为分号定界符的作用就是判断是不是要一起执行这体现了事务的原子性要么都执行要么都不执行就如果我们进行复杂的表查询的时候可以在不加分号的基础上直接换行因为系统判断回车的时候有没有遇到定界符分号一旦加了分号系统就会判断这个语句是需要执行但是我们在编写存储过程和函数的时其中的代码语句的分隔符也是分号这会产生冲突比如说我们定义一个 局部变量declare var 这时候有个分号就会判定定界符开始执行 示例 DELIMITER //
CREATE PROCEDURE myProcedure()
BEGIN-- 过程体
END //
DELIMITER ;这里// 被用作创建过程的定界符而过程体内的语句仍然使用分号。
在 MySQL 的存储过程和函数中语句块是由 BEGIN 和 END 关键字包围的一系列语句。语句块允许将多个语句组合在一起作为一个单元执行。语句块在逻辑上被当做一个整体对待因此在程序执行流程中语句块要么被执行要么整体都被执行 使用语句块 通常用于控制流语句如 IF、LOOP、WHILE内部或存储过程和函数的定义中。 示例 BEGIN-- 多个语句SET a b c;IF a 10 THEN-- 更多语句END IF;
END;注释对代码进行说明提高可读性和维护性。 定界符在编写存储过程或函数时用于区分过程或函数体内的语句结束和整个过程或函数定义的结束。 语句块允许在存储过程和函数中组织多个语句用于控制流和逻辑分组。
在 MySQL 中有几种不同类型的变量包括用户会话变量、局部变量和系统变量。它们的作用范围和用途有所不同
变量
用户会话变量User-Defined Session Variables 作用范围用户会话变量在用户会话内有效当会话结束时消失。不同会话之间的变量是隔离的。 设置和使用 使用 SET 变量名 值; 或 SELECT 变量名 : 值; 来赋值。直接使用 变量名 来引用。 示例 SET myVar 100;
SELECT myVar;用途常用于存储查询结果、传递数据等。
局部变量Local Variables 作用范围局部变量仅在定义它们的存储过程或函数内部有效。 定义和使用 在存储过程或函数中使用 DECLARE 变量名 数据类型; 来定义。使用 SET 变量名 值; 或直接在 SQL 语句中引用来赋值和使用。 示例 CREATE PROCEDURE myProcedure()
BEGINDECLARE myVar INT;SET myVar 100;SELECT myVar;
END;用途用于存储过程和函数中的数据处理和控制流。 再来一个实例
#定义局部变total_sale类型为int初值为0
DECLAER total_sale INT DEFAULT 0;#同时定义x,y两个变量类型都是int初始为0
DECLAER X,Y INT DEFAULT 0;#定义局部变量myname类型为VARCHAR(10),没有初始值就为NULL
DECLAER myname VARCHAR(10);DECLAER total_s INT DEFAULT 0;
SELECT COUNT(*) INTO total_s FROM S;
SELECT total_s;系统变量System Variables 作用范围系统变量分为全局变量和会话变量。全局变量对所有会话有效会话变量只对当前会话有效。 设置和使用 使用 SET GLOBAL 变量名 值; 设置全局变量需要管理员权限。使用 SET SESSION 变量名 值; 或 SET 变量名 值; 设置会话变量。使用 SHOW VARIABLES LIKE 变量名; 查看变量值。 示例 SET GLOBAL max_connections 200;#修改最大连接数的全局变量 max_connections
SET sort_buffer_size 1000000;
SHOW VARIABLES LIKE max_connections;#查询全局变量的值
SELECT global.variable_name;用途用于配置和管理 MySQL 服务器的行为。它们包括了许多设置如内存分配大小、连接设置、性能调优参数等。 用户会话变量用于单个用户会话灵活易于使用适用于临时存储和传递数据。 局部变量用于存储过程和函数内部用于模块化编程和封装。 系统变量用于配置 MySQL 服务器影响其整体行为和性能操作需要谨慎。
运算符
算术运算符
在 MySQL 中算术运算符和表达式用于执行基本的数学运算。以下是常见的算术运算符和一些相关函数的使用示例我将它们按照您要求的表格形式呈现
运算符/函数用法运算表达式结果加法SELECT 5 3;8-减法SELECT 5 - 3;2*乘法SELECT 5 * 3;15/除法SELECT 6 / 2;3% 或 MOD求余SELECT 5 % 2; 或 SELECT MOD(5, 2);1ABS()取绝对值SELECT ABS(-5);5CEILING() 或 CEIL()向上取整SELECT CEILING(5.2);6FLOOR()向下取整SELECT FLOOR(5.8);5ROUND()四舍五入SELECT ROUND(5.45);5POW() 或 POWER()求幂SELECT POW(2, 3);8SQRT()平方根SELECT SQRT(9);3
这些运算符和函数可以用于 SQL 查询中的表达式用于进行数据计算和转换。例如它们可以在 SELECT 语句、WHERE 子句、或者任何需要进行数学计算的地方使用。
比较运算符
在 MySQL 中比较运算符用于进行值的比较操作这些操作通常返回布尔值TRUE真、FALSE假或 NULL在比较中涉及 NULL 值时。以下是常见比较运算符的用法、表达式及其结果的表格
运算符用法运算表达式结果示例等于SELECT 5 5;TRUE! 或 不等于SELECT 5 ! 4;TRUE小于SELECT 4 5;TRUE大于SELECT 6 5;TRUE小于或等于SELECT 5 5;TRUE大于或等于SELECT 5 4;TRUEBETWEEN在两值之间SELECT 5 BETWEEN 1 AND 10;TRUEIN在集合中SELECT a IN (a, b, c);TRUEIS NULL是 NULL 值SELECT NULL IS NULL;TRUEIS NOT NULL非 NULL 值SELECT a IS NOT NULL;TRUELIKE字符串匹配SELECT abc LIKE a%;TRUENOT LIKE字符串不匹配SELECT abc NOT LIKE b%;TRUEREGEXP正则表达式匹配SELECT abc REGEXP ^a;TRUENOT REGEXP正则表达式不匹配SELECT abc NOT REGEXP ^b;TRUE
这些比较运算符可以用于 SELECT 语句的条件中如在 WHERE 或 HAVING 子句中以及在 JOIN 条件中。它们是 SQL 查询和数据分析中的基本工具用于过滤和比较数据。
NULL 值的比较在 SQL 中任何与 NULL 的比较都返回 NULL。例如SELECT a NULL; 返回 NULL。要检查 NULL 值应使用 IS NULL 或 IS NOT NULL。LIKE 和 REGEXP这些运算符用于字符串的模式匹配。LIKE 用于简单的模式其中 % 表示任意字符串_ 表示任意单个字符而 REGEXP 用于更复杂的正则表达式匹配。
在 MySQL 中逻辑运算符和位运算符用于执行逻辑和位级运算。下面是这些运算符的用法和示例。
逻辑运算符
逻辑运算符用于组合或修改布尔表达式的结果。常见的逻辑运算符包括
运算符用法运算表达式结果示例AND 或 逻辑与SELECT TRUE AND FALSE;FALSEOR 或 逻辑或NOT 或 !逻辑非SELECT NOT TRUE;FALSEXOR逻辑异或SELECT TRUE XOR FALSE;TRUE
逻辑运算符通常用于 WHERE 和 HAVING 子句中用于组合多个条件。
位运算符
位运算符用于对数值的二进制表示进行操作。常见的位运算符包括
运算符用法运算表达式结果示例位与SELECT 6 3;2位或SELECT 6^位异或SELECT 6 ^ 3;5~位非SELECT ~1;-2左移SELECT 2 1;4右移SELECT 4 1;2
位运算符在处理二进制数据、进行位级操作时非常有用例如在权限管理、标志位处理等场景中。
优先级逻辑运算符和位运算符有不同的优先级可能会影响到复合表达式的结果。在编写包含多个运算符的表达式时最好使用括号明确指定运算顺序。NULL 值的处理在逻辑运算中涉及 NULL 值时结果可能是不直观的。例如TRUE AND NULL 的结果是 NULL。了解如何正确处理 NULL 值是编写有效 SQL 查询的关键。位运算符的应用位运算符直接作用于操作数的二进制表示因此需要对操作数的二进制形式有所了解。在某些特定场景中这些运算符可以非常高效。
优先级
使用数字来表示运算符优先级是一个清晰的方式。以下是 MySQL 中一些常见运算符的优先级列表用数字表示数字越小表示优先级越高
优先级运算符1BINARY, COLLATE2!3- (负号), ~ (位非)4^ (位异或)5*, /, DIV, %, MOD6-, 7, 8910 (赋值), :11IS, IS NOT, IS NULL, IS NOT NULL, IS TRUE, IS FALSE1213, , , , , , !, LIKE, REGEXP, IN14BETWEEN, CASE, WHEN, THEN, ELSE15NOT16AND, 17XOR18OR,
这个表格按照 MySQL 中运算符的优先级顺序排列有助于理解和编写包含多个运算符的复杂 SQL 表达式。在实际应用中如果表达式中包含多个不同优先级的运算符建议使用括号明确指定运算顺序以避免潜在的混淆和错误。
流程控制语句
MySQL 支持多种流程控制语句这些语句主要用于存储过程和函数中允许执行基于条件的操作或重复执行一组操作。以下是 MySQL 中常见的流程控制语句及其使用方法和案例
1. IF 语句
用于基于条件执行不同的代码块。 语法: IF condition THEN-- 代码块 1
ELSEIF condition2 THEN-- 代码块 2
ELSE-- 代码块 3
END IF; #结束的时候用end if示例: IF user_age 18 THENSELECT Adult;
ELSESELECT Minor;
END IF;2. CASE 语句
类似于其他编程语言中的 switch 语句根据条件选择执行不同的代码块。 语法: CASEWHEN condition1 THEN-- 代码块 1WHEN condition2 THEN-- 代码块 2ELSE-- 默认代码块
END CASE;示例: CASE user_statusWHEN active THENSELECT User is active;WHEN inactive THENSELECT User is inactive;ELSESELECT Unknown status;
END CASE;注意在本实例中user_status是可以被选择状态的如果case之后没有东西就会生成一列新的属性
3. LOOP 语句
用于无条件的循环执行一组语句直到遇到 LEAVE 语句。与while语句相反loop语句需要再判断条件为假的时候才会继续一定判断条件为真就会退出循环 语法: label: LOOP-- 代码块IF condition THENLEAVE label;END IF;
END LOOP label;示例: count_loop: LOOPSET counter counter 1;IF counter 10 THENLEAVE count_loop;END IF;
END LOOP count_loop;理解案例假设我们要编写一个存储过程其中包含一个 LOOP 循环该循环将计数器递增并在计数器超过 10 时退出 DELIMITER //CREATE PROCEDURE loop_example()
BEGINDECLARE counter INT DEFAULT 0;count_loop: LOOPSET counter counter 1;-- 当计数器超过 10 时退出循环IF counter 10 THENLEAVE count_loop;END IF;END LOOP count_loop;SELECT counter AS final_count;
END //DELIMITER ; 在这个例子中LOOP 会无条件地执行每次循环将 counter 加 1。当 counter 的值超过 10 时IF 条件成立触发 LEAVE 语句从而退出循环。
4. REPEAT 语句
类似于 do-while 循环至少执行一次代码块直到条件为真。 语法: REPEAT-- 代码块
UNTIL condition
END REPEAT;示例: REPEATSET counter counter 1;
UNTIL counter 10
END REPEAT;5. WHILE 语句
只要条件为真就重复执行代码块。 语法: WHILE condition DO-- 代码块
END WHILE;示例: WHILE counter 10 DOSET counter counter 1;
END WHILE;
