MySQL从小白到中级DB 用户的权限分配

查询数据库

select database();

展示所有表
show tables;
-- 1 数据语言DDL
create table m_student(
id int PRIMARY KEY auto_increment
);

2、约束的分类
2.1 非空约束
    关键词：not null

-- 例如 在创建 姓名 字段时
create table user(
    name varchar(10) not null comment'姓名'
)
2.2 唯一约束
     关键词： unique

-- 例如 在创建 id 字段时
create table emp(
    id int primary key auto_increment unique comment'主键ID'
);
--------auto_increment------>自动增加
2.3 主键约束
 关键词： primary key

上示例中已展示

2.4 默认约束
关键词：default '  默认值  ' 

在保存数据时，若未指定该字段的值，则采用默认值。

-- 例如 在创建 状态 字段时
create table emp(
    status char(1) default '1' comment '状态'
);

-- 添加字段
alter table m_student add name varchar(30) COMMENT '测试名称';
alter table m_student add level varchar(30) comment '测试名称';
alter table m_student add sex varchar(30) COMMENT '测试性别';
alter table m_student add remark varchar(30) COMMENT '备注';
select * from m_student;
-- 修改数据库类型
alter table m_student modify name varchar(30) comment '测试名称';
-- 修改字段和数据库类型
alter table m_student change sexs sex int COMMENT '测试性别';
-- 删除字段：
alter table m_student drop remark;
-- 数据清空 删除表后重新创建表：
truncate table m_student;

-- 2 数据操作语言DML (对表进行操作: insert/update/delete)

-- 指定字段添加 
insert into 表名(字段,字段,字段)  values(值1,值2,...);
insert into m_student(name,sex,level) values('测试',66,'二级');
-- 批量添加数据 
-- insert into 表名(字段,字段,字段) values(值1,值2,...),(值1,值2,...),(值1,值2,...);
insert into m_student(name,sex,level) values('测试2',55,'三级'),('测试3',77,'四级'),('测试4',22,'五级'),('测试5',11,'一级');

-- 更新数据
update 表名 set 字段1=值1,字段2=值2,......[where 条件];

update m_student set name='测试1' where id=1;

-- 删除数据 
delete from 表名 [where 条件];

delete m_student where id=1;

-- 3 数据查询语言DQL (查询数据库中的记录)
select 字段名 from 表名 where 条件 group by 分组字段列表  having 分组后条件列表  order by 排序字段列表  limit  分页参数 ;
select name,sex from m_student where name='测试1' group by sex  having sex>10  order by sex  limit  10;
select distinct name from m_student ;

-- 比较运算  >、>=、<、<=、=、!=、between...and...(包含边界值)

                in()------->在条件中，字段符合in中的一个值时就会被返回。

                like------->占位符，用于模糊匹配（  _  匹配单个字符，%匹配任意个字符。）

                is null----->表示为空值

-- 逻辑运算       and/&&、or/||、not/！

-- 3.3 聚合函数查询
聚合函数： count()、sum()、avg()、max() 、min()

select 聚合函数（字段列表）from 表名;

----------------    注意：null值不参与所有运算

-- 3.4 分组查询
select 字段列表 from 表名 group by 分组字段名[having 分组后过滤条件];

select * from m_student group by name;

-- 3.5 排序查询
select 字段列表 from 表名 order by 字段1  排序方式,字段2  排序方式;

asc-------->升序（默认值）
desc------->降序 

-- 3.6 分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数;

  如果查询记录数为10，查询第一页数据，此时，起始索引可以省略，直接简写为limit10. 

    起始索引从0开始。

    起始索引=（查询页码  -  1）* 每页显示记录数。

select * from m_student limit 0,3;

 -- 3.7  DQL总结
                1、在写所有的SQL语句时，首先把基础语句写出来，之后在根据题意进行补充。

                2、在通配时，用  like 作为关键字。

                3、编写顺序：
                                select  字段列表 from 表名  where 条件  group by  分组字段  having   分组后条件列表 
                                order by  字段1  排序方式  limit  分页参数

                4、执行顺序：
                                  （1）from  表名   
                                    （2）where 条件   
                                    （3）group  by  分组字段
                  （4）select  字段列表    
                                    （5）order  by  排序字段    
                                    （6）limit   分页参数

-- 4.数据控制语言DCL
  DCL是数据控制语言，用来创建数据库用户、控制数据库的访问权限

use mysql;
select database();
show tables;

-- 1. 查看用户表结构
desc user;

2.权限列
Grant_priv
表示是否拥有GRANT权限

Shutdown_priv
表示是否拥有停止MySQL服务的权限

Super_priv
表示是否拥有超级权限

Execute_priv
表示是否拥有EXECUTE权限。拥有EXECUTE权限，可以执行存储过程和函数

Select_priv , Insert_priv等
为该用户所拥有的权限

1.1.资源控制列
资源控制列的字段用来 限制用户使用的资源 ，包含4个字段，分别为：

 max_questions ，用户每小时允许执行的查询操作次数；
 max_updates ，用户每小时允许执行的更新 操作次数；
 max_connections ，用户每小时允许执行的连接操作次数；
 max_user_connections ，用户 允许同时建立的连接次数。

1. 查询用户
select * from user;

查询特定字段：

SELECT host,user,authentication_string,select_priv,insert_priv,drop_priv
FROM mysql.user;

使用DESCRIBE查看db表的基本结构：

DESC mysql.db;

-- 2. 创建用户
create user '用户名'@'主机名' identfied by '密码';

create user 'zj001'@'host' IDENTIFIED by 'zj123456';
create user 'mch001'@'host' IDENTIFIED by 'mch123456';

-- 3. 修改用户密码：
alter user '用户名'@'主机名' identfied with mysql_native_password by '新密码';

alter user 'zj001'@'host' IDENTIFIED with mysql_native_password by 'zj123';
alter user 'mch001'@'host' IDENTIFIED with mysql_native_password by 'zj123';

-- 4. 删除用户：
drop user '用户名'@'主机名';

drop user 'zj001'@'host';

-- 注意： 主机名可以用  %  进行通配，表示任意主机均可访问！

-- 4.2  权限控制
 --  查询权限
show grants for '用户名'@'主机名';
        
show grants for 'zj001'@'host';

show grants for 'mch001'@'host';

-- 设置成管理员权限
GRANT ALL PRIVILEGES ON *.* TO 'mch001'@'host' WITH GRANT OPTION;

-- 查询、插入、更新、删除 数据库中所有表数据的权利。
grant select on testdb.* to common_user@'%';

grant insert on testdb.* to common_user@'%';

grant update on testdb.* to common_user@'%';

grant delete on testdb.* to common_user@'%';

grant select, insert, update, delete on testdb.* to common_user@'%'

grant select, insert, update on test.m_student to 'zj001'@'host'

GRANT select on test.m_student TO 'zj001'@'host' WITH GRANT OPTION;

 -- 授予权限
grant 权限列表 on 数据库.数据表 to '用户名'@'主机名';

revoke PROCESS ON *.* FROM admin@'localhost';

-- 撤销权限：
-- revoke 权限列表 on 数据库.数据表 from '用户名'@'主机名';

ALL/all  privileges    所有权限    select     查数据
insert                            插入数据    update     修改数据
delete                            删除数据    alter       修改数据表
drop                              删除库/表/视图    create    创建库/表

1. 用户列
db表用户列有3个字段，分别是 Host 、 User 、 Db 。这3个字段分别表示主机名、用户名和数据库 名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。

2. 权限列
 Create_routine_priv 和 Alter_routine_priv 这两个字段决定用户是否具有创建和修改存储过程的权限

3. tables_priv表和columns_priv表
 tables_priv 表用来对表设置操作权限 ， columns_priv 表用来对表的 某一列设置权限 。

desc mysql.tables_priv;

select * from user where user='root' and host='localhost';  #所有权限都是Y ，就是什么权限都有
select * from db where user='root' and host='localhost';  # 没有此条记录
select * from tables_priv where user='root' and host='localhost';  # 没有此条记录
select * from columns_priv where user='root' and host='localhost'; # 没有此条记录
select * from procs_priv where user='root' and host='localhost'; # 没有此条记录

修改用户密码的方式包括：

ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
mysqladmin -u user_name -h host_name password "new_password"
　　

创建用户时指定密码

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

11 设置MySQL用户密码过期策略
设置系统参数default_password_lifetime作用于所有的用户账户

default_password_lifetime=180 设置180天过期

default_password_lifetime=0 设置密码不过期 

如果为每个用户设置了密码过期策略，则会覆盖上述系统参数

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER; 密码不过期
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT; 默认过期策略
　　

手动强制某个用户密码过期

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

mysql 用户 lock
通过执行create user/alter user命令中带account lock/unlock子句设置用户的lock状态

Create user语句默认的用户是unlock状态

# 创建的时候给用户锁定
mysql> create user abc2@localhost identified by 'mysql' account lock;
　　

Alter user语句默认不会修改用户的lock/unlock状态

# 修改用户为unlock
mysql> alter user abc2@'localhost' account unlock;
　　

当客户端使用lock状态的用户登录MySQL时，会收到如此报错 
Access denied for user ‘user_name’@’host_name’. 
Account is locked.

2.3 日期函数
1. curdate()--------->当前日期。

2. curtime()--------->当前时间。

3. now()---------->当前日期和时间。

4. year(date)------> 返回输入日期date的年份。

5. month(date)------->返回输入日期date的月份。

6. day(date)-------->返回输入日期date的天数。

7. date_add(date, interval  推迟数  时间单位)------>得到的是输入日期date 推迟 输入单位个                                                                                   之后的日期。(时间单位：day ，                                                                                                 month，year)

8. datediff(date1 ,  date2)-------->得到两个日期相隔多少天。（date1 - date2）

2、函数的分类
2.1 字符串函数
字符串函数包括有：concat(s1,s2,s3...)、lower(str)、upper（str）、lpad（str，n，pad）、rpad（str，n，pad）、trim（str）、substring（str，start，len）

1. concat(s1,s2,s3)---->字符串拼接，将s1,s2,s3拼接成一个字符串。

2. lower(str)---->将字符串内容全部转为小写。

3. upper(str)---->将字符串内容全部转为大写。

4. lpad(str, n, pad)---->左填充，用pad对字符串str的左面进行填充，达到 n 个字符串长度。

5. rpad(str, n, pad)---->右填充，用pad对字符串str的右面进行填充，达到 n 个字符串长度。

6. trim(str)----->去掉字符串头部和尾部的空格。

7.substring(str, start, len)--->截取字符串str从start（从1开始）位置起len个长度的字符串。

2.2 数值函数
1. ceil(x)----->向上取整。

2. floor(x)----->向下取整。

3. mod(x, y)---->x 与 y 的模，即 x 除以 y 取其余数。

4. rand()-----> 随机数，随机生成一个 0-1 的随机数。

5. round(x, y)----> 求 x 四舍五入的值，保留 y 为小数

2.4 流程函数
1. if(value, t , f)------>若value为true，则返回 t ，否则返回 f。

2. ifnull(value1, value2)------->若value1 不为空，则返回value1，否则返回value2.

3. case  when [val1] then [res1] ... else [default]   end;

        --------> 若 value1 为 true 返回 res1，...... 否则返回default 默认值

4. case [exp1] when [val1] then [res1] ... else [default]  end;

        -------->若exp1的值 = val1 ，返回res1，...... 否则返回default 默认值。

select if (true, 't', 'f');
----------------------------> t
 
select if('true','ok','no');
-------------------------------> no
select ifnull(null, 'nul');
-------------------------------->nul
 
select ifnull('null', 'nul');
-------------------------------->null
 3. case  when [val1] then [res1] ... else [default]   end;           

可以用于城市的划分（一线二线城市）

---工作地址在北京，上海的显示一线城市，其他显示二线城市
select name,
       (case when workadiss = '北京' then '一线城市' when workadiss = '上海' then '一线城市' else '二线城市' end) as '工作地址'
from empeo;
 4. case [exp1] when [val1] then [res1] ... else [default]  end;

                可以用于年龄等级的划分，以及薪资水平等级的划分

select name,
       (case when age >= 45 then '老年人' when age >= 25 then '中年人' when age >= 18 then '成年人' else '未成年人' end) as '年龄'
from empeo;