/*
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 '%变量名%'];
*/
MySQL变量
/*
语法帮助：
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';
权限管理