Mysql基础权限命令

用户及权限管理

Mysql 权限分类角度

权限角度

1. 管理员用户:root,具有最高权限,具有创建用户的权限也可以为其他用户授权

2. 普通用户:是由 root 用户创建来的,他的权限是由 root 用户来分配的

访问主机角度

1. 本地用户:只能在安装 mysql 的机器上访问 mysql 数据库的用户,在创建用户时,使用的地址是 localhost

2. 远程访问用户:在安装 mysql 的机器以外的机器上访问 mysql 数据库的相关用户

(1) 只能在指定 IP 对应的机器上远程访问 mysql 数据库的用户,创建用户时,只能使用指定的具体 ip 地址比如:192.168.x.xxx 所在的机器远程访问

(2) 在除安装 mysql 数据库的机器以外的任何一台机器上都可以远程访问 mysql 数据库,在创建用户时,ip 地址写%在 mysql 中,添加一个可用的新用户一共分为三步:
第一步:添加用户
第二步:为用户授权
第三步:使授权生效

1. 用户的创建
语法:insert into mysql.user (HOST,USER,PASSWORD) values (‘IP 地址’, ’用户名’, PASSWORD(‘密码’)); — 创建用户
PASSWORD() — 加密函数

2. 为创建的用户授权
语法:grant all privileges on . to 用户名@IP 地址 identified by ‘密码’; — 为新创建的用户授权

3. 使授权生效
语法:flush privileges; — 刷新授权表,使被授权用户具有对应的权限

权限相关

有什么权限?mysql 权限分类

1. create:创建库,创建表,创建视图,创建存储过程等
2. drop:删除库,删除表,删除视图,删除存储过程,删除列等
3. alter:修改库,修改表等
4. insert:往表中插入数据
5. select:查询表中数据
6. update:修改表中数据
7. delete:删除表中数据
8. 特殊权限一:all privileges --> 所有权限
9. 特殊权限二:usage -->没有权限

授权语法

grant 权限列表 on 库名.表名 to 用户名@主机 IP identified by‘密码’;
grant -->授权的意思
on 库名.表名 -->在哪些库上授权
to 用户名@主机 IP -->为指定主机 IP 对应的用户授权
identified by ‘密码’ —> 通过什么密码鉴定用户身份(用什么密码登录)
identified 单词,它是 identify 单词的过去式,标志标识的意思

查看用户权限

基本语法
Show grants for 用户名@主机 IP

查看当前登录用户
Show grants;

–查看结果中的 with grant option , 意思是该用户具有超级用户权限,可以创建用户也可以为其他用户授权

查看本地用户
show grants for ‘用户名’@’localhost’;

查看远程可以在任意一台主机上访问数据库的用户权限
show grants for ’用户名’@’%’;

查看远程指定 IP 的主机上访问数据的用户的权限
Show grants for ‘用户名’@’指定的 IP’
Show grants for ‘ertai’@’192.168.1.104’;

用户密码修改
说明:root 用户可以修改任何用户的密码
语法: update 库名.表名
Set PASSWORD = PASSWORD(‘新密码’)
Where user = ‘用户名’ and host = ‘localhost’;

注意:改密码需要做两件事
一:就是运行上面的 update 语句
二:需要刷新授权列表,使新密码生效 —>flush privileges;

查看当前用户身份
语法:select user();

用户的删除
语法:drop user 用户名@主机 IP 地址;

删除主机 IP 是%的用户
drop user 用户名

删除指定 IP 地址的远程用户
drop user 用户名@’IP 地址’;

删除本地用户
drop user 用户名%’localhost’;

部分函数补全(非重点)
字符大小写转换函数
upper() --> 将函数内的小写变成大写
Lower() --> 将函数内的大写变成小写

字符控制函数

concat() --> 拼接字符串函数,多参数用逗号隔开
substr(str,m,n) --> 在 str 中从第 m 个开始连续截取 n 个
length(str) – > 返回参数字符串的长度
instr(str1,str2) --> 给定一个大的字符串 str1,一个小的字符串str2,返回 str2 的第一个字母在 str1 中出现的位置数,从 1 开始
select instr(‘HelloWord’,‘o’);
Lpad(str1,int,str2 或 int2) --> 按指定的 int 长度显示数据,长度不够则在左侧使用 str2 或 int2 进行补齐
select LPAD(‘Hello’,10,0);
Rpad(str1,int,str2 或 int2) --> 按指定的 int 长度显示数据,长度不够则在右侧使用 str2 或 int2 进行补齐
select RPAD(‘Hello’,10,0);
Trim(str) --> 在没有给定其他关键字参数时,默认是去除 str两侧的空格
select trim(’ AA ');
Trim(leading str1 from str2) --> 去除 str2 左侧的 str1
select trim(leading ‘c’ from ‘cccccAAAAccccc’);
Trim(trailing str1 from str2) – >去除 str2 右侧 str1
select trim(trailing ‘c’ from ‘cccccAAAAccccc’);
Trim(BOTH str1 from str2) --> 去除 str2 左右两侧的 str1
select trim(both ‘c’ from ‘cccccAAAAccccc’);

数字处理函数

Round(小数, 数字) – > 四舍五入小数,精确到数字位
select round(3.1465,2) --> 3.15
Truncate(小数, 数字) – >直接将小数截取到数字位数
select truncate(3.1465,2) --> 3.14
Mod(被除数, 除数) – >整除的情况下取模(求余数)
Mod (15 , 2) – > 1
Mod (30 , 2) – > 0
Mod (14, 5) --> 4

日期处理函数

sysdate() – > 获取系统当前时间 YYYY-mm-dd HH:ii:ss
now() – > 获取系统当前时间 YYYY-mm-dd HH:ii:ss
Weekday(sysdate()) – >判断给到的日期是周几(从 0 开始)
Monthname(now()) --> 判断给到的日期是几月(返回的是月份名称)
Date_format(‘日期’, ’显示格式’) – > 按照指定的格式显示给顶的日期时间
Date_add(‘日期’, interval n second) --> 将给定时间增加 n 秒
Date_add(‘日期’, interval n day) – > 将给定时间增加 n 天
Date_add(‘日期’, interval n month) --> 将给定时间增加 n 月
Date_add(‘日期’, interval -n month) --> 将给定时间减少 n 月
Timestampdiff(单位, 日期 1, 日期 2) – >按照给定的单位显示日期 1 和日期 2 相差了多少其中单位包括
Microsecond --> 微秒
Second --> 秒
Minute --> 分
Hour – > 时
day – > 天
Week --> 周
Month --> 月
Quarter --> 季
Year – >年

存储过程

概念
存储过程是一组可编程的函数,是为了完成特定的功能的 sql 语句.经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定相关的参数(如果有)来调用执行,定义好的存储过程经过预编译,可以提高 sql 语句的执行速度.

存储过程的优点

(1) 将重复性很高的一些操作,封装到一个存储过程中,简化了对这些 sql 的调用
(2) 批量处理:sql+循环 ,减少流量,也就是常说的’跑批’
(3) 统一结构,确保数据的安全

特殊说明:相对于 Oracle 来说,mysql 的存储过程功能性相对较弱,使用较少存储过程的参数说明
定义存储过程时,存储过程可以有 0 个或多个参数,存储过程有

三种参数类型:

(1) in 输入参数:表示调用者向存储过程传入值
(2) Out 输出参数:表示存储过程向调用者传出参数(可以返回多个值)
(3) Inout 输入输出参数:表示调用者向存储过程传入值,又表示存储过程向调用者传出值

创建存储过程（建议熟悉）

(1) In 输入参数
语法:
Delimiter // (-- > 将语句的结束符号从分号临时改为 // 可以自定义)
Create procedure 库名.存储过程名([参数])Begin
Sql 语句;
End // (–> 将语句的结束符号恢复成分号)Delimiter;调用该存储过程:
Call 库名.存储过程名([参数])
– 查询给定人员工号的薪资
delimiter //
create procedure test.looklook1(in empno1 integer)
begin
select sal from test.emp where empno = empno1;
end //
delimiter;
call test.looklook1(7902)
说明:默认情况下,存储过程和默认数据库相关,如果下指定存储过程创建在指定的数据库中,那么需要用库名点出来在存储过程中设置了需要传入的变量 empno1,调用存储过程的时候,通过传参 7902,赋值给 empno1,作为存储过程中参数来使用.

(2) out 输出参数

创建存储过程统计员工人数
delimiter //
create procedure cp (out p1 int)
begin
select count(*) from emp;
end //
delimiter;
call cp(@p1)
select @p1
注意:用户变量名一般用@开头
滥用用户变量会导致程序难以理解及管理

(3)inout 输入输出参数

创建存储过程,向存储过程传入值,将给定的值输出
delimiter //
create procedure inout_param(inout p_inout int)
begin
select p_inout;
set p_inout = 2;
select p_inout;
end //
delimiter;

调用存储过程
set @p_inout = 10;
call inout_param(@p_inout)

无参存储过程
查询 SMITH 的所有信息包括部门名字和城市
delimiter //
create procedure seesmith()
begin
select a.*,dname,loc
from emp a,dept b
where a.deptno = b.deptno
and ename = ‘SMITH’;
end //
delimiter;
call seesmith()

向 xxxx 表内插入 10000 条数据且数据不能重复
delimiter //
create procedure insert_1w()
begin
declare i int default 0;
while i < 10000
do
insert into xxxx values (i,concat(‘张三’,i));
set i = i + 1;
end while;
end //
delimiter;

说明:其中的 i 是属于局部变量,局部变量声明一定要放在存储过程体开的位置,declare 声明While 进入循环, 当… 表达式返回为 true 时,执行循环内的语句.表达式返回为 false 时,执行循环外的语句.call insert_1w()

查询当前库中所有的存储过程
show procedure status like ‘%’

删除指定的存储过程
Drop procedure 存储过程名;

视图(view)
说明:

1. 视图是一个’虚拟表’.
2. 数据库中只存在视图的定义
3. 视图可以提高数据库中数据的安全性,提供数据库访问的便利
4. 访问视图时,可以像访问数据库的真是表一样,从中查询数据
5. 可以从不同角度看数据库中的数据
6. 视图中不存放数据,视图中的数据来源于基础表创建视图
   语法:create view 库名.视图名 as 查询语句;
   说明:select 语句可以是普通查询,也可以是连表查询,联合查询,子查询等创建视图,查询在纽约上班的有哪些人,显示名字和工资和城市名
   create view seesee
   as
   select ename,sal,loc
   from dept a, emp b
   where a.deptno = b.deptno
   and loc = ‘NEW YORK’;
   调用视图:
   select * from seesee;
   删除视图
   语法:drop view [库名.]视图名;