Day1——权限、sql_mode、逻辑架构

傷城~ 2022-11-26 07:39 93阅读 0赞

### 文章目录 ###

*  一. 问题背景
 *  二. 权限
 *   *  2.1 创建用户
     *  2.2 授权命令
 *  三. sql\_mode
 *   *  3.1 问题背景
     *  3.2 为什么上面的查询语句不正确仍能执行呢？
     *  3.3 设置sql\_mode
 *  四. 数据库的逻辑结构
 *   *  4.1 总体概览
     *  4.2 查看sql的执行周期
     *   *  4.2.1 查看最近几次的sql执行周期
         *  4.2.2 查看详细的执行周期
 *  五. 命中缓存的条件
 *  六. 查询顺序
 *   *  6.1 手写语句的顺序
     *  6.2 机器读的顺序

# 一. 问题背景 #

Linux：CentOS  
MySQL：5.5.54

情况：学习尚硅谷教程的MySQL高级，讲师是夏磊

# 二. 权限 #

## 2.1 创建用户 ##

**语法：**

create user 用户名 identified by '密码';

## 2.2 授权命令 ##

**语法：**

grant 权限1, 权限2,...,权限n on 数据库名.表名 to 用户名@'用户地址' identified by '密码';

**例子1：**

grant select, update, insert, delete on atguigudb.mytbl to lisi@'localhost';

给lisi用户在本地命令行方式下，授予atguigudb数据库下的mytbl表的查询、更新、插入、删除的权限。

**例子2：**

grant all privileges on *.* to zhangsan@'%' identified by '123456';

授予通过网络方式登录的zhangsan用户，对所有库所有表的全部权限，密码为123456

# 三. sql\_mode #

## 3.1 问题背景 ##

有一个表，有如下数据：

create table mytbl2(
      id int,
    	name varchar(200),
    	age int,
    	dept int
    );
    
    insert into mytbl2 values(1, 'zhang3', 33, 101);
    insert into mytbl2 values(2, 'li4', 34, 101);
    insert into mytbl2 values(3, 'wang5', 34, 102);
    insert into mytbl2 values(4, 'zhao6', 34, 102);
    insert into mytbl2 values(5, 'tian7', 36, 102);

**需求：** 查询出每个机构年龄最大的人  
**新人常错点：** `select name, dept, max(age) from mytbl2 group by dept;`

**查询的结果：**  
![在这里插入图片描述][20200810213151239.jpg]  
**解释：** 虽然查出来的最大年龄是对的，但是对应的name却是错的。dept为101时，年龄最大那个人的name是li4

**原因：group的使用原则是：select后面只能跟 函数以及 `group by`后面的字段**

## 3.2 为什么上面的查询语句不正确仍能执行呢？ ##

答案是sql\_mode变量，它的默认值是空的。如果是空的，它将会允许某些非法操作执行。

**总结：因此在生产环境中必须将sql\_mode设置为严格模式，因此开发阶段也设置它为严格模式。**

## 3.3 设置sql\_mode ##

首先查一下：

show variables like 'sql_mode';

![在这里插入图片描述][20200810214322166.jpg]  
然后设置值为ONLY\_FULL\_GROUP\_BY，如下：

set sql_mode = 'ONLY_FULL_GROUP_BY';

设置完后，再执行`select name, dept, max(age) from mytbl2 group by dept;`就会报错。

修改后要怎么 **查询出每个机构年龄最大的人** 呢？如下：

select * from mytbl2 m inner join (
    select dept, max(age)maxage from mytbl2 group by dept 
    )ab on ab.dept = m.dept and m.age = ab.maxage

# 四. 数据库的逻辑结构 #

## 4.1 总体概览 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70]

## 4.2 查看sql的执行周期 ##

### 4.2.1 查看最近几次的sql执行周期 ###

用什么证明上图阐述的逻辑结构执行顺序确实如此呢？答：**使用`show profiles`**

使用之前先查询`show variables like 'profiling';`，如下：  
![在这里插入图片描述][20200810222222822.jpg]  
如果它的值为OFF而不是ON，那么使用`set profiling = 1;`即可。

show profiles的作用是显示最近几次的查询。  
首先我们来执行一下随意的查询，比如：`select * from mytbl2 where id = 2;`

然后执行`show profiles;`，如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 1]  
如上，可以查看到执行的时间用了多久。

### 4.2.2 查看详细的执行周期 ###

**如果要查看详细信息，可以使用`show profile cpu, block io for query id号;`**

为了避免MySQL的缓存命中，我们先执行一下随意查询：`select * from mytbl2 where id = 3;`

然后使用`show profiles;`查看id号，如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 2]  
最后使用`show profile cpu, block io from query 205;`查看详细的执行周期，如下:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 3]

# 五. 命中缓存的条件 #

**条件就是sql语句一模一样**

# 六. 查询顺序 #

## 6.1 手写语句的顺序 ##

select distinct
        <select_list>
    from
        <left_table> <join_type>
        join <right_table> on <join_condition>
    where
        <where_condition>
    group by
        <group_by_list>
    having
        <having_condition>
    order by
        <order_by_condition>
    limit <limit_number>

## 6.2 机器读的顺序 ##

**每次都会不一样，因为它会优化sql语句，但是不影响结果。**

[20200810213151239.jpg]: /images/20221124/990e23fc5492477484eb1d985bb3961a.png
[20200810214322166.jpg]: /images/20221124/b56d0317eb44422c8c2d393ff09b73c2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70]: /images/20221124/2dcb2d920afb4f87941cdea868cf134d.png
[20200810222222822.jpg]: /images/20221124/262fc79dfac5495da47708a4a5a7f736.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 1]: /images/20221124/8f099b77c88442099b003258a7727d03.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 2]: /images/20221124/ed08fdb3b3ad422c9388e11e44bc8719.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjM0ODQ2_size_16_color_FFFFFF_t_70 3]: /images/20221124/72a02cd9e07d455e95c550b8c80231a0.png