mysql用户

一、数据库用户管理

DCL：数据控制语言，用于设置或者更改数据库用户或角色权限

1.新建用户

create user '用户名'@'源地址'   identified by '密码';

'用户名'
指定将创建的用户名

'来源地址'
指定新创建的用户可在哪些主机上登录
可使用IP地址、网段、主机名的形式
本地用户可用localhost
允许任意主机登录可用通配符%

'密码'
若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；

若使用加密密码，需要先使用select password('密码'); 获取密文，再在语句中添加 password '密文';

若省略“identified by”部分，则用户的密码将为空（不建议使用）

create user 'user1'@'localhost' identified by '123456';

select password('abc123');

create user 'user2'@'localhost' identified by PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

2.查看用户信息

#创建后的用户保存在 mysql 数据库的 user 表里
use mysql;
select user,authentication_string,Host from user;
#authentication_string在mariadb中为password

#查看当前登录用户
select user();

3.重命名用户

rename user 'hx'@'192.168.100.%' to 'hx'@'192.168.%.%'

4.删除用户

drop user 'lisi'@'localhost';

5.修改当前登录用户密码

#修改当前用户的密码
set password = password('abc123');　
#修改其他用户密码
set password for '用户' = password('abc'123);

在MariaDB 10.4及更高版本中，默认情况下使用了 validate_password 插件来强制密码策略，包括密码长度和复杂性要求。如果想使用简单密码（如只有数字或字母），需要修改相应的全局变量来放宽密码策略要求。

set global validate_password_policy=0;
set global validate_password_length=1;
#修改密码策略

7.忘记 root 密码的解决办法

1）修改 mysql配置文件/etc/my.cnf ，不使用密码直接登录到 mysql

vim /etc/my.cnf

[mysqld]
skip-grant-tables                    #添加，使登录mysql不使用授权表

systemctl restart mysqld

mysql                                #直接登录

2）使用 update 修改 root 密码，刷新数据库

use mysql;

update `user` set `Password`=password('123') where user='root';

FLUSH PRIVILEGES;
quit

mysql -u root -pabc123

#注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除，并重启 mysql 服务。

二、数据库用户授权

1.授予权限

grant语句：专门用来设置数据库用户的访问权限。
当指定的用户名不存在时，grant语句将会创建新的用户；
当指定的用户名存在时， grant 语句用于修改用户信息。

grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' [indentified by '密码'];

#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“kgc.*”表示授权操作的对象为 kgc数据库中的所有表。
#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。
#来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.byyb.com”、“192.168.80.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。

#允许用户 zhangsan 在本地查询 hx 数据库中 所有表的数据记录，但禁止查询其他数据库中的表的记录。
grant select ON hx.* TO 'zhangsan'@'localhost' IDENTIFIED BY '123';

#允许用户 lisi 在所有终端远程连接 mysql ，并拥有所有权限。
GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%' IDENTIFIED BY '123';

flush privileges;
quit

mysql -u zhangsan -p123;
use hx;
show tables;
select * from tb1;

2.查看权限

show grants for 用户名@来源地址;

#举个例子
show grants for root;

3.撤销权限

revoke 权限列表 on 数据库名.表名 from 用户名@来源地址;

#举个例子
#移除用户所有权限
revoke all on *.* from 'hx'@'192.168.100.%';

show grants for'hx'@'192.168.100.%';
#USAGE权限只能用于数据库登陆，不能执行任何操作；
#USAGE权限不能被回收，即 REVOKE 不能删除用户.

flush privileges;

三、命令总结

use 库名;
show tables;      show tables from 库名;
desc [库名.]表名;

create database 库名;
use 库名;
create table [库名.]表名 (字段1 数据类型, 字段2 数据类型, ....[, primary key (字段)]);

drop table [库名.]表名;
drop database 库名;

修改表结构
改表名    alter table 旧表名 rename 新表名
增加字段  alter table 表名 add 新字段 数据类型 [字段属性];
修改字段  alter table 表名 change 旧字段 新字段 数据类型 [字段属性];
删除字段  alter table 表名 drop 字段;

DML：用于管理表数据
insert into 表名 (字段1, 字段2, ...) values (字段1的值, 字段2的值, ...);
insert into 表名 values (字段1的值, 字段2的值, ...);                      #要按照表结构的字段顺序设置值

update 表名 set 字段1=值, ... where 条件表达式;

delete from 表名 where 条件表达式;

DQL：用于根据条件查询表数据
select * from 表名 [where 条件表达式];
select 字段1,字段2,... from 表名 [where 条件表达式];

select * from 表名\G      #纵向查看每行记录字段的值

select * from 表名 limit N;      #查看表的前N行记录
select * from 表名 limit N,M;    #查看表的前N行之后的连续M行记录（不包含第N行）


MySQL 的约束特性：
primary key      主键约束    字段的值不能重复，且不能为null，一个自建的表只能有一个主键
unique key       唯一性约束  字段的值不能重复，能为null，一个表可以有多个唯一键
not null         非空约束    字段的值不能为null
default          默认值约束  字段的值如果没有设置则使用默认值自动填充
auto_increment   自增约束    字段的值如果没有设置默认会从1开始每次自动递增1，要求自增字段必须设置主键
foreign key      外键约束    保证相关联的表数据的完整性和一致性

int(N) zerofill  零填充


克隆表
create table 新表 like 旧表;                    #克隆表结构
insert into 新表 (select * from 旧表);          #克隆表数据        可实现表结构和表数据与旧表都一样

create table 新表 (select * from 旧表);         表数据与旧表一样，表结构与旧表可能不一样

清空表
delete from 表名;        一条一条的删除记录，效率较慢；自增字段仍然会按照清空前的最大记录继续自增

truncate table 表名;     直接重建表，清空表效率更快；自增字段会重新从1开始自增

临时表
create temporary table 表名 (....);   临时表可以跟普通的表一样增删改查表中的数据，但是show tables是查看不到的，
                                      临时表只能在当前会话中有效，在其它会话中或者退出当前会话连接，临时都会失效

外键约束
主键表： alter table 表名 add primary key (主键字段);
外键表： alter table 表名 add foreign key (外键字段) references 主键表名 (主键字段);
        插入新数据时，需要先在主键表插入数据再在外键表插入对应数据；删除数据时，需要先在外键表删除数据再在主键表删除对应数据


DCL：用于管理用户与权限
用户管理
select user();        查看当前登录的用户

create user '用户名'@'源地址'   identified by '密码';
                      localhost/IP/网段/主机名/%
                      
rename user '旧用户名'@'源地址' to '新用户名'@'源地址';
                      
drop user '用户名'@'源地址';

select user,host,authentication_string from mysql.user;                      

set password = password('密码');
set password for '用户名'@'源地址' = password('密码');
alter user '用户名'@'源地址' identified by '密码';

找回 root 密码？
1）修改mysql配置文件，在 [mysqld] 下面添加 skip-grant-tables 配置项
2）重启mysqld服务，使用 mysql 密码直接登录 mysql
3）执行 update user set authentication_string=password('密码') where user='root'; 命令修改 root 用户的密码
4）还原mysql配置文件，重启mysqld服务，使用 mysql -u 用户名 -p密码 [-h mysql地址 -P mysql端口] 命令来验证登录

权限管理
grant 权限1,权限2,....  on 库名.表名   to  '用户名'@'源地址'  [identified by '密码'];     #5.7版本支持创建用户和权限授权，8.0版本只能用于权限授权
      all                     *.*

show grants for '用户名'@'源地址';

revoke 权限1,权限2,....  on 库名.表名  from  '用户名'@'源地址';
       all