【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 1

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 1

🍀1、delimiter

概念:

delimiter是用来指定mysql分隔符,在mysql客户端中分隔符默认是分号(;)。如果一次输入的语句较多,并且语句中间有分号,这时需要新指定一个特殊的分隔符。比如我们指定SQL以(//)结尾而不使用默认的(;)那需要执行下面的语句

delimiter //
记得使用完要改回默认的,因为要防止其他人使用;结尾而发生错误
delimiter ;

🍀2、union 与 union all

union与union all都可以实现上下连表,但是不同的是union 会对新加入的数据进行去重,而union all不会

比如我有一张class表如下:

72d5d29012464257b30d1839f689df54.png

执行如下:

SELECT cid, caption FROM class 
UNION 
SELECT cid, caption FROM class
输出结果去重:
cid caption
1 三年二班
2 三年三班
3 一年二班
4 二年九班
SELECT cid, caption FROM class 
UNION 
SELECT cid, caption FROM class
输出结果不去重:
cid caption
1 三年二班
2 三年三班
3 一年二班
4 二年九班
1 三年二班
2 三年三班
3 一年二班
4 二年九班

🍀3、MySQL视图

什么是视图?

答:先不说概念,首先假设我们在MySQL操作中不断会使用到 SELECT * FROM class WHERE cid>2这一条语句,那每当我们要使用这条语句的时候,

就要重复书写,这样是十分麻烦的,那么我们可不可以将这一句会重复使用到的语句取个别名比如叫**“v666”**,每当我们要使用这条语句的时候只要用它的别名来代替那么就会十分省事,我们将给MySQL语句取别名的过程就叫做创建视图相当于基于存在的物理表而创建了一张虚拟表

视图的创建方法:

CREATE VIEW 视图名称 AS 需要取别名的SQL语句
列如:
CREATE VIEW v1 AS SELECT * FROM class WHERE cid>2

efad5f21a4d1443dac2e3897508e92b7.png

问题一:如果我们在class表中增加一条数据,基于class表产生的视图中的数据会发生改变吗?

答案:视图中会发生动态的改变

问题二:我们可以向创建出来的视图中(也就是所说的虚拟表)中插入数据吗?例如执行以下的语句

INSERT INTO v1(xx, xx) VALUES(xx,xx)吗?

答案:都说了视图是创建出来的方便查询的虚拟表,那当然是不可以的如果执行上面的语句是百分百会报错的

🍀4、MySQL触发器

🍁4.1、创建触发器

什么是触发器?

答:触发器(TRIGGER)是由事件来触发某个操作。这些事件包括insert语句、update语句和delete语句查询是不会引发的。当数据库系统执行这些事件时,就会激活触发器执行相应的操作。比如我有两张表分别为【class】【teacher】比如我只要在teacher表中插入一条数据,对应的class会触发在表中插入相应的数据,要实现这种关系我们就要使用到触发器

触发器的实现:

1、在MySQL中,创建只有一个执行语句的触发器的基本形式如下:
create trigger 触发器名 before | after 触发事件
on 表名 for each row 执行语句
1、MySQL中,触发器触发的执行语句可能有多个。创建有多个执行语
句的触发器的基本形式如下:
create  trigger 触发器名 before | after 触发事件
on 表名 for each row
begin
  执行语句列表;
end

实例:

-- 创建触发器
-- 方法1
-- delimiter //
-- CREATE TRIGGER t1 BEFORE INSERT ON teacher FOR EACH ROW
-- BEGIN 
--  INSERT INTO class(caption) values("触发产生");
-- END//
-- delimiter ;
-- 方法2
-- CREATE TRIGGER t1 BEFORE INSERT ON 
-- teacher FOR EACH ROW INSERT INTO class(caption) values("触发产生");
-- 执行触发事件
INSERT into teacher(tname) VALUES("引发触发器");
如果执行下面的语句触发器将会执行两次,因为向teacher表中插入了两行数据
INSERT into teacher(tname) VALUES("引发触发器1"),("引发触发器2");

5174c8727db14911ba0513d3aa66fb77.png

🍁4.2、触发器之 NEW与OLD

mysql触发器中, NEW关键字,和 MS SQL Server 中的 INSERTED 和 DELETED 类似,MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据。

具体地:

在 INSERT 型触发器中,NEW 用来表示将要(BEFORE)或已经(AFTER)插入的新数据;

在 UPDATE 型触发器中,OLD 用来表示将要或已经被修改的原数据,NEW 用来表示将要或已经修改为的新数据;

在 DELETE 型触发器中,OLD 用来表示将要或已经被删除的原数据;

使用方法: NEW.columnName (columnName 为相应数据表某一列名)

另外,OLD 是只读的,而 NEW 则可以在触发器中使用 SET 赋值,这样不会再次触发触发器,造成循环调用。

栗子:

CREATE TRIGGER t1 BEFORE INSERT ON 
teacher FOR EACH ROW INSERT INTO class(caption) values(NEW.tname);//触发器插入的数据为teacher新插入数据的sname

🍁4.3、查看触发器

方式1:查看当前数据库的所有触发器的定义

SHOW TRIGGERS;

方法2: 查看当前数据库中某个触发器的定义

SHOW CREATE TRIGGER 触发器名

方式3:从系统库information_schema的TRIGGERS表中查询触发器的信息

SELECT * FROM information_schema.TRIGGERS;

🍁4.4、删除触发器

DROP TRIGGER  IF EXISTS 触发器名称;

🍀5、MySQL函数

🍁5.1、执行函数

可以用以下语句来执行mysql的内置函数,以及自定义函数

select 函数

栗子:

-- 内置的求字符串长度的函数
SELECT CHAR_LENGTH('abcdefg') as len 
返回结果:
----
len
----
7
----

🍁5.2、创建函数

mysql创建函数可能会报的错误以及解决的方法:

mysql函数报错:This function has none of DETERMINISTIC, 
NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

这是因为创建函数、存储过程、触发器这类操作会写入二进制日志,当有主丛模式的时候这些功能也会进入从服务器,然而这些操作的重复执行会导致主从数据不一致,因此MySQL要求:此时在主服务器上,子程序必须声明为确定性的或者不更改数据,否则创建或者替换子程序就会被拒绝。这就是上面报错的原因,


报错中已经提级可用的参数:DETERMINISTIC, NO SQL, or READS SQL DATA 三者之一,实际有以下5个参数,但在function里面,只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如下:

1 DETERMINISTIC 确定的

2 NO SQL 没有SQl语句,不修改数据

3 READS SQL DATA 只读取数据,不修改数据

4 MODIFIES SQL DATA 修改数据

5 CONTAINS SQL 包含SQL语句

原文链接附上非常详细:mysql函数报错解决方法

从上面的栗子我们可以得知函数体内不能够存在类似于select等修改数据的语句

方法如下:

CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )
RETURNS <返回类型>
<函数主体>

栗子:

delimiter \\
create function f1(
  var1 int,
  var2 int
)
returns int 
-- 此处加上声明没有SQL语句。才能成功创建函数
NO SQL
begin
  -- 将int变量num的默认值为0
  declare num int default 0;
  set num = var1 + var2;
  -- 设置返回值
  return(num);
end \\
delimiter ;
执行函数:
SELECT f1(1, 2)
输出结果:
--------+
f1(1, 2)
--------+
3
--------+

🍀6.存储过程

🍁6.0、概念

什么是存储过程?

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用
或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来,这个过程经编译和
优化后存储在数据库服务器中,因此称为存储过程。当以后需要数据库提供与已定义
好的存储过程的功能相同的服务时,只需调用“CALL存储过程名字”即可自动完成。

🍁6.1、参数分类

存储过程的参数类型可以是in、out和inout

下面我们来具体介绍这些吗参数的作用:

1、没有参数(无参数无返回)
2、仅仅带 IN 类型(有参数无返回)
3、仅仅带 OUT 类型(无参数有返回)
4、既带 IN 又带 OUT(有参数有返回)
5、带 INOUT(有参数有返回)
注意: IN、OUT、INOUT 都可以在一个存储过程中带多个。
说明:
IN:当前参数为输入参数,也就是表示入参;
存储过程只是读取这个参数的值。如果没有定义参数种类,默认就是 IN,表示输入参数。
OUT:当前参数为输出参数,也就是表示出参;
执行完成之后,调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT:当前参数既可以为输入参数,也可以为输出参数。

🍁6.2、创建存储过程

方法:

create procedure 存储过程名(
  IN|OUT|INOUT 参数名 参数类型,...
)
[characteristics ...]
BEGIN
  存储过程体
END

栗子:

create procedure p1()
begin 
  INSERT INTO class(caption) VALUES ("调用了存储过程");
  SELECT * from class;
end

🍁6.3、调用存储过程

方法:

call 存储过程名(实参列表)

调用存储过程

call p1()

输出结果:


875c56eebe6c485f9877efcb3964b4f0.png

格式:

1、调用in模式的参数:

CALL p1('值');

2、调用out模式的参数:

特别的由于存储过程中没有一个类似于return的可返回返回值的关键字,所以我们有结果集以及使用out伪造一个类似于返回值的效果。@加变量名,相当Python中声明了一个全局变量,存储过程中out变量的改变可以改变外部的@变量名的值,从而得到类似于返回值的效果

SET @name;
CALL p1(@name);
SELECT @name;

栗子下面这个栗子在6.8还要用到:

创建存储过程

delimiter //
create procedure p2(
  in n1 int,
  out n2 char(15)
)
begin 
  set n2 = "在存储过程中改变";
  SELECT * FROM class WHERE cid > n1;
end //
delimiter ;

执行下面的语句:

set @v1 = "开始值";
call p2(3, @v1);
select @v1;

9e16bc9090134dd790cd7cdd439002e5.png

3、调用inout模式的参数:

SET @name=值;
CALL p1(@name);
SELECT @name;


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
26天前
|
SQL 数据采集 关系型数据库
实现MySQL与SQL Server之间数据迁移的有效方法
总的来说,从MySQL到SQL Server的数据迁移是一个涉及到很多步骤的过程,可能会遇到各种问题和挑战。但只要精心规划、仔细执行,这个任务是完全可以完成的。
90 18
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL】SQL分析的几种方法
以上就是SQL分析的几种方法。需要注意的是,这些方法并不是孤立的,而是相互关联的。在实际的SQL分析中,我们通常需要结合使用这些方法,才能找出最佳的优化策略。同时,SQL分析也需要对数据库管理系统,数据,业务需求有深入的理解,这需要时间和经验的积累。
91 12
|
2月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
2月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
SQL 存储 安全
SQL Server——触发器
触发器,是一种特殊类型的存储过程,它在指定的表中的数据发生变化时自动生效。经常通过触发器来强制实现不同表中的逻辑相关数据的引用完整性或一致性。触发器主要是通过事件进行触发而被执行的,而存储过程可以通过过程名字而直接调用。当对某一个表进行诸如UPDATE、INSERT、DELETE这些操作时,SQL Server就会自动执行触发器所定义的SQL语句,从而确保对数据的处理必须符合这些SQL语句所定义的规则
|
SQL 存储 安全
SQL Server 触发器
SQL Server 触发器
214 0
|
SQL 存储 程序员
SQL Server——SQL Server触发器及事务和锁
SQL Server——SQL Server触发器及事务和锁
529 1
|
SQL 存储 程序员
SQL Server触发器总结
触发器的简介: 触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。 触发器可以查询其他表,而且可以包含复杂的sql语句。它们主要用于强制服从复杂的业务规则或要求。例如:您可以根
267 0
|
SQL 存储 Go
SQL SERVER TRIGGER 触发器
1.触发器简介 触发器是一种特殊的存储过程,它的执行不是由程序调用,也不是手动执行,而是由事件来触发。触发器是当对某一个表进行操作。例如:update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。
2054 0

推荐镜像

更多