Linux 第68天 procedure,trigger,DCL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Linux 第68天 procedure,trigger,DCL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7942字，纯文字阅读大概需要12分钟。

内容图文

Linux 第68天 procedure,trigger,DCL

时间: 20181009

        个人博客地址: www.winthcloud.top

目录

存储过程简介

触发器

DCL (Data Countrol Language)

存储过程简介

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的

SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储

过程带有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在

不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据

库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

存储过程的优点：

(1).增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以

完成复杂的判断和较复杂的运算。

(2).标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存

储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫

无影响。

(3).较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，

那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存

储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。

而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及

的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，

网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

(5).作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现

对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

存储过程与自定义函数的区别

存储过程实现的过程要复杂一些,而函数的针对性较强

存储过程可以有多个返回值,而自定义函数只有一个返回值

存储过程一般独立的来执行,而函数往往是作为其他SQL语句的一部分来使用

流程控制

存储过程和函数中可以使用流程控制来控制语句的执行

流程控制：

IF：用来进行条件判断。根据是否满足条件，执行不同语句

CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断

LOOP：重复执行特定的语句，实现一个简单的循环

LEAVE：用于跳出循环控制

ITERATE：跳出本次循环，然后直接进入下一次循环

REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句

WHILE：有条件控制的循环语句

存储过程: 存储过程保存在mysql.proc表中

创建存储过程

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 ...]])

CALL sp_name

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

存储过程修改

ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体

所以要修改存储过程,方法就是删除重建

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name;

存储过程示

创建无参存储过程

DELIMITER //

CREATE PROCEDURE showTime()

BEGIN

SELECT now();

END //

DELIMITER ;

CALL showTime;

创建含参存储过程：只有一个IN参数

DELIMITER //

CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)

BEGIN

SELECT * FROM students WHERE stuid = uid;

END //

DELIMITER ;

CALL selectById(2);

只有一个IN参数(带变量)

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 uid SMALLINT UNSIGNED,

OUT num SMALLINT UNSIGNED)

BEGIN

DELETE FROM students WHERE stuid = uid;

SELECT row_count() INTO num;

END //

DELIMITER ;

CALL deleteById(2,@Line);

SELECT @Line;

说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和

保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数

触发器

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

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

触发器的特性：

　　有begin end体，begin end;之间的语句可以写的简单或者复杂

　　触发条件：I、D、U

　　触发机制：在增删改前或者后

　　触发频率：针对每一行执行

　　触发器定义附着在表上。

也就是由事件来触发某个操作，事件包括INSERT语句，UPDATE语句和DELETE语句；可以协助

应用在数据库端确保数据的完整性。

注意：cannot associate a trigger with a TEMPORARY table or a view.

创建触发器

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：该触发器作用在表名

查看触发器

SHOW TRIGGERS

查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。

mysql> USE information_schema;

mysql> SELECT * FROM triggers WHERE 

trigger_name='trigger_student_count_insert';

删除触发器

DROP TRIGGER trigger_name;

触发器示例

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);

示例：创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少

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_delete

AFTER DELETE

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count-1;

DCL (Data Countrol Language)

GRANT REVOKE

用户和权限管理

元数据数据库：mysql

系统授权表：

db, host, user

columns_priv, tables_priv, procs_priv, proxies_priv

创建用户：CREATE USER

CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；

默认权限：USAGE

用户重命名：RENAME USER

RENAME USER old_user_name TO new_user_name

删除用户：

DROP USER 'USERNAME'@'HOST‘

示例：删除默认的空用户

DROP USER ''@'localhost';

修改密码：

mysql>SET PASSWORD FOR 'user'@'host' = PASSWORD('password');

mysql>UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;

此方法需要执行下面指令才能生效：

mysql> FLUSH PRIVILEGES;

mysqladmin -u root -poldpass password 'newpass'

忘记管理员密码的解决办法：

启动mysqld进程时，为其使用如下选项：

--skip-grant-tables 表示跳过权限认证

--skip-networking 表示不启用网络接口

使用UPDATE命令修改管理员密码

关闭mysqld进程，移除上述两个选项，重启mysqld

权限类别:

管理类：

CREATE TEMPORARY TABLES

CREATE USER

FILE

SUPER

SHOW DATABASES

RELOAD

SHUTDOWN

REPLICATION SLAVE

REPLICATION CLIENT

LOCK TABLES

PROCES

程序类： FUNCTION、PROCEDURE、TRIGGER

CREATE

ALTER

DROP

EXCUTE

库和表级别：DATABASE、TABLE

ALTER

CREATE

CREATE VIEW

DROP

INDEX

SHOW VIEW

GRANT OPTION：能将自己获得的权限转赠给其他用户

数据操作：

SELECT

INSERT

DELETE

UPDATE

字段级别：

SELECT(col1,col2,...)

UPDATE(col1,col2,...)

INSERT(col1,col2,...)

所有权限：ALL PRIVILEGES 或 ALL

授权

参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html

GRANT priv_type [(column_list)],... 

ON [object_type] priv_level 

TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

priv_type: ALL [PRIVILEGES]

object_type:TABLE | FUNCTION | PROCEDURE

priv_level: *(所有库) | *.* | db_name.* | db_name.tbl_name | 

tbl_name(当前库的表) | db_name.routine_name(指定库的函数,存储过程,触发器)

with_option: GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

回收授权

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... 

ON [object_type] priv_level FROM user [, user] ...

示例：

REVOKE DELETE ON testdb.* FROM 'testuser'@'%‘

查看指定用户获得的授权：

Help SHOW GRANTS

SHOW GRANTS FOR 'user'@'host';

SHOW GRANTS FOR CURRENT_USER[()];

注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存

(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自

动重读授权表，使之生效

(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表

mysql> FLUSH PRIVILEGES;

内容总结

以上是互联网集市为您收集整理的Linux 第68天 procedure,trigger,DCL全部内容，希望文章能够帮你解决Linux 第68天 procedure,trigger,DCL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---