黑马MySQL数据库学习day04 MySQL变量 存储过程 用户和授权管理
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 MySQL中的变量局部变量,用户变量,会话变量和全局变量。 3 用户变量不用定义,直接使用。 4 1.用户变量赋值 5 set @xxx = 值; 6 2.查询 7 select @xxx; 8 语法帮助: 9 过程保存在information_schema.routines表中 10 help create PROCEDURE 11 help drop PROCEDURE 12 help SET 13 help select 14 help WHILE 15 help if statement 16 help declare 17 bug点: 18 不同数据库可以存在同名的存储过程。 19 */ 20 drop table if exists product; 21 create table product( 22 id int primary key auto_increment, 23 name varchar(50) unique not null, 24 price double(10, 2) not null 25 ); 26 insert into product(name, price) 27 values 28 ('白菜', 5), 29 ('豆角', 5), 30 ('香菇', 10), 31 ('土豆', 4); 32 33 -- 定义一个求平均价格,最高价格,最低价格的存储过程 34 35 SELECT avg(price) FROM product INTO @avgprice; 36 SELECT @avgprice; 37 38 -- 查询存储过程,保存在information_schema.routines表中 39 SELECT routine_schema, routine_name, definer, routine_body, routine_definition from information_schema.routines; 40 41 -- 定义存储过程 42 delimiter // 43 CREATE PROCEDURE proc_db1.productprice( 44 OUT avg INT, 45 OUT max INT, 46 OUT min INT) 47 BEGIN 48 SELECT avg(price) INTO avg FROM product; 49 SELECT max(price) INTO max FROM product; 50 SELECT min(price) INTO min FROM product; 51 END // 52 delimiter ; 53 54 -- 调用存储过程 55 -- CALL sp_name([parameter[,...]]); 56 CALL productprice(@avgprice, @maxprice, @minprice); 57 58 -- 使用用户变量,保存 59 SELECT @avgprice, @maxprice, @minprice; 60 61 -- 删除存储过程 62 -- DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name; 63 DROP PROCEDURE IF EXISTS proc_db1.productprice;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 MySQL手册中,变量分为:系统变量和用户变量。 3 实际开发中,认为分为4类: 4 1.局部变量 5 2.用户变量 6 3.会话变量 7 4.全局变量 8 语法帮助: 9 help set 10 help declare 11 help show 12 13 1.局部变量 14 概念:局部变量一般用在sql语句块中,其作用域局限于语句块。 15 语法:DECLARE var_name [, var_name] ... type [DEFAULT value] 16 17 2.用户变量 18 概念: 19 用户变量作用域是全局,生命周期是当前连接。 20 语法:@xxx 用户变量无需定义,直接使用。 21 赋值: 22 SET @xxx = 值; 23 查询: 24 SELECT @xxx; 25 查询+赋值: 26 SELECT @xxx := 值; 因为select语句中的=号是关系运算符,用:=表示赋值 27 28 3.会话变量 29 概念: 30 服务器为每个连接的客户端维护一系列会话变量。在客户端连接时使用相应的全局变量 31 的当前值对客户端的会话变量进行初始化。 32 设置会话变量不需要特殊的权限,但客户端只能更改自己的会话变量,不能更改其他客户端的会话变量。 33 会话变量的作用域是全局的,生命周期是当前连接。连接断开,则失效。 34 35 语法: 36 设置会话变量: 37 set 变量名 = 值; 38 set session 变量名 = 值; 39 set @@session.变量名 = 值; 40 查询会话变量: 41 select @@变量名; 42 select @@session.变量名; 43 show session variables [like '%变量名%']; 44 45 4.全局变量 46 概念: 47 全局变量影响服务器。当服务器启动时,它将所有全局变量初始化为默认值, 48 这些默认值可以在配置文件或启动参数中修改。全局变量作用于服务器的一次启动和关闭。 49 特点: 50 全局变量不能跨重启,服务重启后所有在mysql客户端中设置的全局变量将失效。 51 如果想让全局变量重启后继续生效,需更改配置文件。 52 53 语法: 54 设置全局变量: 55 set global 变量名 = 值; 56 set @@global.变量名 = 值; 57 查询全局变量: 58 select @@global.变量名; 59 show global variables [like '%变量名%']; 60 */
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 语法帮助: 3 1. help create user 4 2. help drop user 5 3. help alter user 6 4. help set password 7 */ 8 9 -- mysql.user表中查询用户和其密码验证插件 10 SELECT user, host, plugin from mysql.user; 11 12 -- 查看验证插件 13 SHOW PLUGINS; 14 15 -- 查看系统变量,服务器默认的密码验证插件 16 SHOW VARIABLES LIKE '%auth%'; 17 18 -- 添加用户 19 CREATE USER '用户名'@'主机名' IDENTIFIED BY '新密码'; 20 CREATE USER 'caocao'@'localhost' IDENTIFIED BY 'cao'; 21 22 -- 删除用户 23 DROP USER '用户名'@'主机名'; 24 DROP USER 'caocao'@'localhost'; 25 26 -- 修改密码 27 /* 28 MySQL5.5中 29 */ 30 -- update mysql.user表 + password()函数设置密码 31 UPDATE mysql.user SET password=password('新密码') where user = '用户名';-- MySQL8不支持 32 UPDATE mysql.user set password=password('root') where user='caocao';-- MySQL8不支持 33 -- 刷新下,使其立即生效 34 FLUSH PRIVILEGES; 35 36 -- set password + password()函数设置密码 37 SET PASSWORD FOR '用户名'@'主机名' = password('新密码');-- MySQL8.0.11中被移除 38 SET password for 'caocao'@'localhost' = password('root'); 39 40 /* 41 MySQL8 42 */ 43 -- 空串表示没有密码,不指定验证插件使用的是当时的默认插件。 44 -- 使用help alter user查看是否能够获取到帮助信息,获取不到不支持。 45 ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码'; -- MySQL8中 46 ALTER USER 'caocao'@'localhost' IDENTIFIED BY 'root'; 47 48 -- 指定验证插件 49 ALTER USER 'caocao'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; 50 ALTER USER 'caocao'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'root'; 51 ALTER USER 'caocao'@'localhost' IDENTIFIED WITH sha256_password BY 'root'; 52 53 -- 查看验证插件 54 SHOW PLUGINS; 55 56 -- 查看系统变量,服务器默认的密码验证插件 57 SHOW VARIABLES LIKE '%auth%';
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 /* 2 语法帮助: 3 help grant 4 help revoke 5 */ 6 -- 查询有哪些权限 7 SHOW PRIVILEGES 8 9 -- 查询用户表,里面有所有用户的权限信息 10 SELECT * FROM mysql.user; 11 12 -- 查询对某个用户的授权 13 SHOW GRANTS FOR 'liubei'@'localhost'; 14 15 -- 创建一个新用户 16 CREATE USER 'liubei'@'localhost' IDENTIFIED BY 'liu'; 17 18 -- 添加权限,ALL表示所有权限 *.* 所有库所有表,数据库.* 某个库所有表 19 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; 20 GRANT SELECT, INSERT ON db1.student TO 'liubei'@'localhost'; 21 GRANT ALL ON *.* TO 'liubei'@'localhost'; 22 23 -- 删除权限 24 REVOKE SELECT ON *.* FROM 'liubei'@'localhost'; 25 REVOKE ALL ON *.* FROM 'liubei'@'localhost';