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

一. 问题背景

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;

查询的结果：

解释： 虽然查出来的最大年龄是对的，但是对应的name却是错的。dept为101时，年龄最大那个人的name是li4

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

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

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

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

3.3 设置sql_mode

首先查一下：

show variables like 'sql_mode';

然后设置值为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 总体概览

4.2 查看sql的执行周期

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

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

使用之前先查询show variables like 'profiling';，如下：

如果它的值为OFF而不是ON，那么使用set profiling = 1;即可。

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

然后执行show profiles;，如下：

如上，可以查看到执行的时间用了多久。

4.2.2 查看详细的执行周期

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

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

然后使用show profiles;查看id号，如下：

最后使用show profile cpu, block io from query 205;查看详细的执行周期，如下:

五. 命中缓存的条件

条件就是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语句，但是不影响结果。