07-mysql入门基础03-视图函数事件

VIEW 视图

视图：虚拟表，保存有实表的查询结果，相当于别名
利用视图,可以隐藏表的真实结构,在程序中利用视图进行查询,可以避免表结构的变化,而修改程序,降低程序和数据库之间的耦合度

#创建方法：
CREATE   VIEW view_name [(column_list)]
     AS select_statement
     [WITH [CASCADED | LOCAL] CHECK OPTION]

#查看视图定义：
SHOW CREATE VIEW view_name #只能看视图定义
SHOW CREATE TABLE view_name # 可以查看表和视图

#删除视图：
DROP VIEW [IF EXISTS]
   view_name [, view_name] ...
    [RESTRICT | CASCADE]

注意：视图中的数据事实上存储于"基表"中，因此，其修改操作也会针对基表实现；其修改操作受基表限制

create view v_st_co_sc as select st.name,co.Course,sc.score 
from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.CourseID;
SHOW TABLE STATUS LIKE 'v_st_co_sc'\G;
select * from v_st_co_sc;

FUNCTION 函数

函数：分为系统内置函数和自定义函数
系统内置函数参考：

https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html
https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

自定义函数：user-defined function UDF，保存在mysql.proc (MySQL8.0 中已经取消此表)表中

创建UDF语法

CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name 
type,...])
   RETURNS {STRING|INTEGER|REAL}
 runtime_body

说明：
- 参数可以有多个,也可以没有参数
- 无论有无参数，小括号（）是必须的
- 必须有且只有一个返回值

#查看函数列表：
SHOW FUNCTION STATUS;

#查看函数定义
SHOW CREATE FUNCTION function_name

#删除UDF
DROP FUNCTION function_name

#调用自定义函数语法
SELECT function_name(parameter_value,...)

#范例
#无参UDF
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";

#有参数UDF
DELIMITER //
CREATE FUNCTION deleteById(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
BEGIN
 DELETE FROM students WHERE stuid = id;
 RETURN (SELECT COUNT(*) FROM students);
END//
DELIMITER ;

范例: MySQL8.0 默认开启二进制不允许创建函数

#默认MySQL8.0开启二进制日志,而不允许创建函数
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";
select @@log_bin;
show variables like '%log_bin%';
show variables like 'log_bin_trust_function_creators';
#打开此变量允许二进制日志信息函数创建
set global log_bin_trust_function_creators=ON;
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World";
SHOW FUNCTION STATUS like 'simple%'\G;

MySQL中的变量

两种变量：系统内置变量和用户自定义变量
系统变量：MySQL数据库中内置的变量，可用@@var_name引用
用户自定义变量分为以下两种
- 普通变量：在当前会话中有效，可用@var_name引用
- 局部变量：在函数或存储过程内才有效，需要用DECLARE 声明，之后直接用 var_name引用
自定义函数中定义局部变量语法

DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]

说明：局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义

#为变量赋值语法
SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name

#范例1
DELIMITER //
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, y SMALLINT UNSIGNED)
RETURNS SMALLINT
BEGIN
 DECLARE a, b SMALLINT UNSIGNED;
 SET a = x, b = y;
 RETURN a+b;
END//
DELIMITER;

#范例2
DECLARE x int;
SELECT COUNT(*) FROM tdb_name INTO x;
RETURN x;
END//

#范例：自定义的普通变量
#方法1
select count(*) from students into @num ;
#方法2
select count(*) into @num from students;
#查看变量
select @num;

PROCEDURE 存储过程

存储过程：多表SQL的语句的集合，可以独立执行，存储过程保存在mysql.proc表中
存储过程优势
- 存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程，提高了运行速度，同时降低网络数据传输量
存储过程与自定义函数的区别
- 存储过程实现的过程要复杂一些,而函数的针对性较强
- 存储过程可以有多个返回值,而自定义函数只有一个返回值
- 存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用
- 无参数的存储过程执行过程中可以不加(),函数必须加 ( )

创建存储过程

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
routime_body 
proc_parameter : [IN|OUT|INOUT] parameter_name type
#说明：
其中IN表示输入参数
OUT表示输出参数;
INOUT表示既可以输入也可以输出;
param_name表示参数名称;
type表示参数的类型.

#查看存储过程列表
SHOW PROCEDURE  STATUS;

#查看存储过程定义
SHOW CREATE PROCEDURE sp_name;

#调用存储过程
CALL sp_name ([ proc_parameter [,proc_parameter ...]])

说明:当无参时,可以省略"()",当有参数时,不可省略"()"

#存储过程修改
ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建
DROP PROCEDURE [IF EXISTS] sp_name

删除存储过程

#创建含参存储过程：只有一个IN参数
delimiter //
CREATE PROCEDURE selectById(IN id SMALLINT UNSIGNED)
BEGIN
 SELECT * FROM students WHERE stuid = id;
END//
delimiter ;
call selectById(2);

delimiter //
CREATE PROCEDURE dorepeat(n INT)
BEGIN
 SET @i = 0;
 SET @sum = 0;
 REPEAT SET @sum = @sum+@i; 
 SET @i = @i + 1;
 UNTIL @i > n END REPEAT;
END//
delimiter ;
CALL dorepeat(100);
SELECT @sum;

#创建含参存储过程:包含IN参数和OUT参数
delimiter //
CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM students WHERE stuid >= id;
SELECT row_count() into num;
END//
delimiter ;
call deleteById(20,@Line);
SELECT @Line; #说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行
数值的用户变量@Line,select @Line;输出被影响行数
#row_count() 系统内置函数，用于存放前一条SQL修改过的表的记录数

流程控制

存储过程和函数中可以使用流程控制来控制语句的执行
IF：用来进行条件判断。根据是否满足条件，执行不同语句
CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
LOOP：重复执行特定的语句，实现一个简单的循环
LEAVE：用于跳出循环控制，相当于SHELL中break
ITERATE：跳出本次循环，然后直接进入下一次循环，相当于SHELL中continue
REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
WHILE：有条件控制的循环语

TRIGGER 触发器

触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行

创建触发器

CREATE [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body


说明：
trigger_name：触发器的名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
tbl_name：该触发器作用在表名

范例

#创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少
CREATE TABLE student_info (
stu_id INT(11) NOT NULL AUTO_INCREMENT,
stu_name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (stu_id));

CREATE TABLE student_count (student_count  INT(11) DEFAULT 0 );
INSERT INTO student_count VALUES(0);
CREATE TRIGGER trigger_student_count_insert
AFTER INSERT ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count+1;

CREATE TRIGGER trigger_student_count_delete
AFTER DELETE ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;

[root@c8-client01 ~]# cat /data/mysql/hellodb/trigger_student_count_delete.TRN 
TYPE=TRIGGERNAME
trigger_table=student_info
[root@c8-client01 ~]# cat /data/mysql/hellodb/trigger_student_count_insert.TRN 
TYPE=TRIGGERNAME
trigger_table=student_info
[root@c8-client01 ~]# cat /data/mysql/hellodb/student_info.TRG 
TYPE=TRIGGERS
triggers='CREATE DEFINER=`root`@`localhost` TRIGGER trigger_student_count_insert AFTER INSERT ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count+1' 'CREATE DEFINER=`root`@`localhost` TRIGGER trigger_student_count_delete AFTER DELETE ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count-1'
sql_modes=1411383296 1411383296
definers='root@localhost' 'root@localhost'
client_cs_names='utf8' 'utf8'
connection_cl_names='utf8_general_ci' 'utf8_general_ci'
db_cl_names='utf8_general_ci' 'utf8_general_ci'
created=165692587395 165692590691

查看触发器
#在当前数据库对应的目录下，可以查看到新生成的相关文件：trigger_name.TRN,table_name.TRG
SHOW TRIGGERS; #查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。
USE information_schema;
SELECT  * FROM triggers WHERE trigger_name='trigger_student_count_insert';
删除触发器
DROP TRIGGER trigger_name;

Event 事件

事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的"事件调度器"。
事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于它们彼此相似，所以事件也称为临时性触发器。
事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。
事件的优缺点
- 优点：一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能，可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用
- 缺点：定时触发，不可以直接调用
MySQL事件调度器event_scheduler负责调用事件，它默认是关闭的。这个调度器不断地监视一个事件是否要调用，要创建事件，必须打开调度器
服务器系统变量和服务器选项：event_scheduler：默认值为OFF，设置为ON才支持Event，并且系统自动打开专用的线程

开启和关闭event_scheduler

#默认事件调度功能是关闭的
select @@event_scheduler;
show variables like '%event_scheduler%';

#临时开启事件调度功能
set global event_scheduler=1;

#开启事件调度功能后,自启动一个event_scheduler线程
show processlist;

#临时关闭事件调度功能
set global event_scheduler=0;

#持久开启事件调度
[root@c8-client01 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
event_scheduler=ON
[root@c8-client01 ~]#systemctl restart mariadb

管理事件

create event 语句创建一个事件。每个事件由两个主要部分组成:
- 第一部分是事件调度（event schedule），表示事件何时启动以及按什么频率启动
- 第二部分是事件动作（event action ），这是事件启动时执行的代码，事件的动作包含一条SQL语句，它可能是一个简单地insert或者update语句，也可以使一个存储过程或者 benin...end语句块，这两种情况允许我们执行多条SQL
一个事件可以是活动（打开）的或停止（关闭）的，活动意味着事件调度器检查事件动作是否必须调用，停止意味着事件的声明存储在目录中，但调度器不会检查它是否应该调用。在一个事件创建之后，它立即变为活动的，一个活动的事件可以执行一次或者多次

创建Event

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
   event_name 
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body; 
schedule: 
    AT timestamp [+ INTERVAL interval] ... 
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
interval: 
   quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
             WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

说明：
event_name ：创建的event名字，必须是唯一确定的
ON SCHEDULE：计划任务
schedule: 决定event的执行时间和频率（注意时间一定要是将来的时间，过去的时间会出错），有两种形式 AT和EVERY
[ON COMPLETION [NOT] PRESERVE]： 可选项，默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件；ON COMPLETION PRESERVE则不会drop掉
[COMMENT 'comment'] ：可选项，comment 用来描述event；相当注释，最大长度64个字节
[ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该事情，可以用alter修改
DO event_body: 需要执行的sql语句，可以是复合语句
提示：event事件是存放在mysql.event表中

查看Event

SHOW EVENTS [{FROM | IN} schema_name]
    [LIKE 'pattern' | WHERE expr]

注意：事件执行完即释放，如立即执行事件，执行完后，事件便自动删除，多次调用事件或等待执行事件,才可以用上述命令查看到。

修改Event

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO event_body]

注意：alter event语句可以修改事件的定义和属性。可以让一个事件成为停止的或者再次让它活动，也可以修改一个事件的名字或者整个调度。然而当一个使用 ON COMPLETION NOT PRESERVE 属性定义的事件最后一次执行后，事件直接就不存在了，不能修改

删除Event

DROP EVENT [IF EXISTS] event_name

范例：创建立即启动事件,创建一个表记录每次事件调度的名字和事件戳

create database testdb;
use testdb;
create table events_list(event_name varchar(20) not null,event_started timestamp not null);
#临时关闭事件调度功能
set global event_scheduler=0;
show variables like 'event_scheduler';

#创建一次性事件
create event event_now on schedule at now() do insert into events_list values('event_now', now());
#因为事件调度功能禁用，所有表中无记录
select * from events_list;
#查看事件
show events\G;
#任务计划存放在mysql.event表中
select * from mysql.event\G;
#开启事件调度功能
set global event_scheduler=1;
#事件立即执行，每秒插入一条记录
select * from events_list;
#事件执行完成后自动删除
show events;

范例：创建每秒启动的事件

CREATE EVENT event_every_second ON SCHEDULE EVERY 1 SECOND DO INSERT INTO events_list VALUES('event_now',now());
SHOW EVENTS\G;
#事件是存放在mysql.event表中
select * from mysql.event\G
select *from events_list;

#删除时间event
drop event event_every_second;
SHOW EVENTS\G;
select * from mysql.event\G;

posted @ 2022-07-05 01:43 西瓜的春天阅读(155) 评论(0) 收藏举报

刷新页面返回顶部

西瓜的春天

斯人若彩虹，遇上方知有

07-mysql入门基础03-视图函数事件

VIEW 视图

FUNCTION 函数

创建UDF语法

MySQL中的变量

PROCEDURE 存储过程

创建存储过程

删除存储过程

流程控制

TRIGGER 触发器

创建触发器

Event 事件

管理事件

创建Event

查看Event

修改Event

删除Event