黑马MySQL数据库学习day04 MySQL变量 存储过程 用户和授权管理

/*
MySQL中的变量局部变量，用户变量，会话变量和全局变量。
    用户变量不用定义，直接使用。
    1.用户变量赋值
        set @xxx = 值;
    2.查询
        select @xxx;
语法帮助：
    过程保存在information_schema.routines表中
    help create PROCEDURE
    help drop PROCEDURE
    help SET
    help select
    help WHILE
    help if statement
    help declare
bug点：
    不同数据库可以存在同名的存储过程。
*/
drop table if exists product;
create table product(
    id int primary key auto_increment,
    name varchar(50) unique not null,
    price double(10, 2) not null
);
insert into product(name, price)
values
('白菜', 5),
('豆角', 5),
('香菇', 10),
('土豆', 4);

-- 定义一个求平均价格，最高价格，最低价格的存储过程

SELECT avg(price) FROM product INTO @avgprice;
SELECT @avgprice;

-- 查询存储过程，保存在information_schema.routines表中
SELECT routine_schema, routine_name, definer, routine_body, routine_definition from information_schema.routines;

-- 定义存储过程
delimiter //
CREATE PROCEDURE proc_db1.productprice(
OUT avg INT,
OUT max INT,
OUT min INT)
BEGIN
    SELECT avg(price) INTO avg FROM product;
    SELECT max(price) INTO max FROM product;
    SELECT min(price) INTO min FROM product;
END //
delimiter ;

-- 调用存储过程
-- CALL sp_name([parameter[,...]]);
CALL productprice(@avgprice, @maxprice, @minprice);

-- 使用用户变量，保存
SELECT @avgprice, @maxprice, @minprice;

-- 删除存储过程
-- DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;
DROP PROCEDURE IF EXISTS proc_db1.productprice;

/*
MySQL手册中，变量分为：系统变量和用户变量。
实际开发中，认为分为4类：
    1.局部变量
    2.用户变量
    3.会话变量
    4.全局变量
语法帮助：
    help set
    help declare
    help show

1.局部变量
    概念：局部变量一般用在sql语句块中，其作用域局限于语句块。
    语法：DECLARE var_name [, var_name] ... type [DEFAULT value]

2.用户变量
概念：
    用户变量作用域是全局，生命周期是当前连接。
语法：@xxx 用户变量无需定义，直接使用。
    赋值:
        SET @xxx = 值;
    查询：
        SELECT @xxx;
    查询+赋值：
        SELECT @xxx := 值; 因为select语句中的=号是关系运算符，用:=表示赋值

3.会话变量
概念：
    服务器为每个连接的客户端维护一系列会话变量。在客户端连接时使用相应的全局变量
    的当前值对客户端的会话变量进行初始化。
    设置会话变量不需要特殊的权限，但客户端只能更改自己的会话变量，不能更改其他客户端的会话变量。
    会话变量的作用域是全局的，生命周期是当前连接。连接断开，则失效。

语法：
    设置会话变量：
        set 变量名 = 值;
        set session 变量名 = 值;
        set @@session.变量名 = 值;
    查询会话变量：
        select @@变量名;
        select @@session.变量名;
        show session variables [like '%变量名%'];

4.全局变量
概念：
    全局变量影响服务器。当服务器启动时，它将所有全局变量初始化为默认值，
    这些默认值可以在配置文件或启动参数中修改。全局变量作用于服务器的一次启动和关闭。
特点：
    全局变量不能跨重启，服务重启后所有在mysql客户端中设置的全局变量将失效。
    如果想让全局变量重启后继续生效，需更改配置文件。

语法：
    设置全局变量：
        set global 变量名 = 值;
        set @@global.变量名 = 值;
    查询全局变量：
        select @@global.变量名;
        show global variables [like '%变量名%'];
*/

/*
    语法帮助：
        1. help create user 
        2. help drop user 
        3. help alter user 
        4. help set password 
*/

-- mysql.user表中查询用户和其密码验证插件
SELECT user, host, plugin from mysql.user;

-- 查看验证插件
SHOW PLUGINS;

-- 查看系统变量，服务器默认的密码验证插件
SHOW VARIABLES LIKE '%auth%';

-- 添加用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '新密码';
CREATE USER 'caocao'@'localhost' IDENTIFIED BY 'cao';

-- 删除用户
DROP USER '用户名'@'主机名';
DROP USER 'caocao'@'localhost';

-- 修改密码
/*
    MySQL5.5中
*/
-- update mysql.user表 + password()函数设置密码
UPDATE mysql.user SET password=password('新密码') where user = '用户名';-- MySQL8不支持
UPDATE mysql.user set password=password('root') where user='caocao';-- MySQL8不支持
-- 刷新下，使其立即生效
FLUSH PRIVILEGES;

-- set password + password()函数设置密码
SET PASSWORD FOR '用户名'@'主机名' = password('新密码');-- MySQL8.0.11中被移除
SET password for 'caocao'@'localhost' = password('root');

/*
    MySQL8
*/
-- 空串表示没有密码，不指定验证插件使用的是当时的默认插件。
-- 使用help alter user查看是否能够获取到帮助信息，获取不到不支持。
ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码'; -- MySQL8中
ALTER USER 'caocao'@'localhost' IDENTIFIED BY 'root';

-- 指定验证插件
ALTER USER 'caocao'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'caocao'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'root';
ALTER USER 'caocao'@'localhost' IDENTIFIED WITH sha256_password BY 'root';

-- 查看验证插件
SHOW PLUGINS;

-- 查看系统变量，服务器默认的密码验证插件
SHOW VARIABLES LIKE '%auth%';

/*
    语法帮助：
        help grant
        help revoke
*/
-- 查询有哪些权限
SHOW PRIVILEGES

-- 查询用户表，里面有所有用户的权限信息
SELECT * FROM mysql.user;

-- 查询对某个用户的授权
SHOW GRANTS FOR 'liubei'@'localhost';

-- 创建一个新用户
CREATE USER 'liubei'@'localhost' IDENTIFIED BY 'liu';

-- 添加权限，ALL表示所有权限 *.* 所有库所有表，数据库.* 某个库所有表
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
GRANT SELECT, INSERT ON db1.student TO 'liubei'@'localhost';
GRANT ALL ON *.* TO 'liubei'@'localhost';

-- 删除权限
REVOKE SELECT ON *.* FROM 'liubei'@'localhost';
REVOKE ALL ON *.* FROM 'liubei'@'localhost';