文章目录
一. 问题背景
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语句,但是不影响结果。