mysql之函数,存储过程,触发器以及用户管理

函数

创建函数

create FUNCTION simpleFun() RETURNS VARCHAR(20) return "Hello World";  --创建一个无参数函数simpleFun,返回值为Hello World

函数保存在mysql.proc表中

查看函数列表

SHOW FUNCTION STATUS;  

查看函数定义

 SHOW CREATE FUNCTION function_name

删除函数

DROP FUNCTION function_name

调用自定义函数语法

SELECT function_name(parameter_value,...)

自定义函数

DELIMITER //  --用//代替;作为语句结束的符号
CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20) 
BEGIN
DELETE FROM students WHERE stuid = uid;
RETURN (SELECT COUNT(stuid) FROM students);
END//
DELIMITER ;
--定义函数deleteById，里面的变量UID是数值且为正数，删除学生表当学生id等于UID的值，然后返回剩余的学生ID总数

局部变量

DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在
BEGIN...END的第一行定义

变量赋值语法

SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name

存储过程

创建存储过程

delimiter //
CREATE PROCEDURE showTime()
BEGIN
 SELECT now();
END//
delimiter ;
CALL showTime;

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

查看存储过程列表

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 selectById(IN uid SMALLINT UNSIGNED)
BEGIN
SELECT * FROM students WHERE stuid = uid;
END//
delimiter ;
call selectById(2);
--创建存储过程selectById，定义变量UID为输入变量，且为整数且为正数，条件为查询学生表的学生ID和UID相等的值输出。

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;
--逻辑类似于shell里的循环，执行的结果就是1+2+3...+100的结果，其中@代表全局变量，存储过程执行结束后值依然保留。REPEAT是循环，UNTIL是直到的意思

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，输入变量是UID，输出变量是num。然后执行判断学生表ID大于等于UID的进行删除，用row_count()函数赋值给num变量之前修改的数据总数。最后在执行存储过程的时候把num赋值给line变量

触发器

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;
--创建两个触发器，一个是触发器的条件是当学生信息表新增一条记录的时候，会自动在学生数量表里count里+1，另一个则是删除时count-1

触发器记录表information_schema.triggers

查看触发器

SHOW TRIGGERS;

删除触发器

DROP TRIGGER trigger_name;

用户管理

创建用户

create user test@'192.168.160.%' identified by 'centos';  --创建一个用户名为test，允许192.168.160网段的设备访问，密码为centos的用户

重命名

RENAME USER old_user_name TO new_user_name;

删除用户

DROP USER 'USERNAME'@''HOST'

修改密码

set password for root@'localhost'=password('centos');
update mysql.user set password=password('') where host='localhost';
FLUSH PRIVILEGES;
--两种修改密码方法，第二种需要配合FLUSH PRIVILEGES;使用

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

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

vim /etc/my.cnf
--skip-grant-tables 
--skip-networking

使用UPDATE命令修改管理员密码
关闭mysqld进程，移除上述两个选项，重启mysqld