Linux:MySQL（六）存储过程触发器用户管理_linux下mysql查看存储过程-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_40001704/article/details/87887986

本文深入探讨了数据库中的存储过程和触发器概念，详细解释了它们的优势、创建及使用方法，包括流程控制语句的应用，并提供了实际示例。同时，文章还涵盖了MySQL的用户管理和权限设置，帮助读者全面理解数据库的安全控制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

存储过程

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

存储过程

存储过程：存储过程保存在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);

查询编号为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 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;输出被影响行数

流程控制

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

触发器一般不再MYSQL中用，用程序代码执行

触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行
创建触发器
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：该触发器作用在表名

触发器示例

准备工作：

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_count_delete
AFTER DELETE
ON student_info FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;

触发器

查看触发器
SHOW TRIGGERS
查询系统表information_schema.triggers（在内存里）的方式指定查询条件，查看指定的触发器信息。

mysql> USE information_schema;
Database changed

mysql> 
SELECT 
	* 
FROM 
	triggers 
WHERE 
	trigger_name='trigger_student_count_insert';

删除触发器
DROP TRIGGER trigger_name;

MySQL用户和权限管理

元数据数据库：mysql
系统授权表：
db, host, user
columns_priv, tables_priv, procs_priv, proxies_priv
用户账号：
‘USERNAME’@‘HOST’
@‘HOST’:
主机名
IP地址或Network
通配符： % _
示例：172.16.%.%

用户管理

创建用户：CREATE USER
CREATE USER ‘USERNAME’@‘HOST’ [IDENTIFIED BY ‘password’]；
默认权限：USAGE（连接权限）

create 
	user dushan@'192.168.31.17' 
identified by 
	'centos';

flush privileges;若为生效可重新加载

用户重命名：RENAME USER
RENAME USER old_user_name TO new_user_name;

rename user 
	liuying@'192.168.31.17' 
to 
	dushan@'192.168.32.17';


在192.168.32.17上测试:
mysql -udushan -pcentos -h192.168.32.7

删除用户：
DROP USER ‘USERNAME’@'HOST‘

DROP USER test@'192.168.31.17';

示例：删除默认的空用户

DROP USER ''@'localhost';

用户管理

修改密码：
SET PASSWORD FOR ‘user’@‘host’ = PASSWORD(‘password’);

set password for 
	dushan@'192.168.32.17'=password('123456');

还可以用下列方法修改，*不推荐*
UPDATE  
	mysql.user 
SET 
	password=PASSWORD('password') 
WHERE 
	clause;
此方法需要执行下面指令才能生效：
FLUSH PRIVILEGES;
客户端验证：	
mysqladmin -u root -poldpass password ‘newpass’

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

测试中可直接删除mysql中的表....当然没有什么意义
systemctl stop mariadb
rm -rf /var/lib/mysql/*

划重点
启动mysqld进程时，为其使用如下选项：
–skip-grant-tables --skip-networking
UPDATE 命令修改
关闭mysqld进程，移除上述两个选项，重启mysqld

systemctl stop mariadb
vim /etc/my.cnf
[mysqld]
skip-grant-tables   mysqld语句块底下添加
skip-networking

systemctl start mariadb
进入后
update mysql.user set password=''

多实例情况下，启动脚本中添加–skip-grant-tables即可

MySQL权限管理

权限类别：
管理类
程序类
数据库级别
表级别
字段级别

管理类：
CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS
程序类： 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

示例：GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO ‘someuser’@'somehost‘;

grant select(stuid,name) on hellodb.students to liuying@'192.168.32.17';
192.168.32.17的liuying只能查询students表中的stuid和name字段

grant all on * to liuying@'192.168.32.17';
对所有表授权，*代表当前库所有表，不代表所有库

show grants for liuying@'192.168.32.17'\G;

授权

回收授权：REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] … ON [object_type] priv_level FROM user [, user] …
- 示例：
  REVOKE DELETE ON testdb.* FROM ‘testuser’@‘172.16.0.%’;

revoke all on *.* from liuying@'192.168.32.17';
revoke delete on hellbd.* from 'liuying'@'192.168.32.17';

查看指定用户获得的授权：
Help SHOW GRANTS
SHOW GRANTS FOR ‘user’@‘host’;
SHOW GRANTS FOR CURRENT_USER[()];

注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存
(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效
(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES;