mysql高级（尚硅谷-夏磊）

1、Linux下MySQL的安装与使用

2、逻辑架构

3、sql预热

1、docker安装docker run -d \-p 3309:3306 \-v /atguigu/mysql/mysql8/conf:/etc/mysql/conf.d \-v /atguigu/mysql/mysql8/data:/var/lib/mysql \-e MYSQL_ROOT_PASSWORD=123456 \--name atguigu-mysql8 \--restart=always \mysql:8.0.29

*查看容器

2、远程连接问题

（1）问题

（2）解决方案

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码

docker exec -it atguigu-mysql8 env LANG=C.UTF-8 /bin/bash

#进入容器内的mysql命令行

mysql -uroot -p

#修改默认密码校验方式

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

3、字符集

MySQL 8版本之前，默认字符集为 latin1（ISO-8859-1） ，不支持中文，使用前必须设置字符集为utf8（utf8mb3）或utf8mb4。从MySQL 8开始，数据库的默认字符集为 utf8mb4 ，从而避免中文乱码的问题。

4、sql_mode

（1）是什么

Mysql提供的sql语法规范

（2）实例

CREATE DATABASE atguigudb;

USE atguigudb;

CREATE TABLE employee(id INT, `name` VARCHAR(16),age INT,dept INT);

INSERT INTO employee VALUES(1,'zhang3',33,101);

INSERT INTO employee VALUES(2,'li4',34,101);

INSERT INTO employee VALUES(3,'wang5',34,102);

INSERT INTO employee VALUES(4,'zhao6',34,102);

INSERT INTO employee VALUES(5,'tian7',36,102);

需求：查询每个部门年龄最大的人

#查询每个部门年龄最大的人(错误写法)

SELECT e.`dept`,MAX(e.`age`),e.`name` FROM employee e

GROUP BY e.`dept`;

#查询每个部门年龄最大           的人

SELECT e.`dept`,MAX(e.`age`)maxage FROM employee e

GROUP BY e.`dept`;

SELECT ee.*,e.`name` FROM employee e

INNER JOIN (

SELECT e.`dept`,MAX(e.`age`)maxage FROM employee e

GROUP BY e.`dept`

)ee ON e.`dept` =ee.dept AND e.`age`= ee.maxage;

• ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，SELECT子句中只能包含函数和 GROUP BY 中出现的字段。

1、逻辑架构图

下面是MySQL5.7使用的经典架构图，MySQL 8中去掉了Caches&Buffers部分：

2、客户端

MySQL服务器之外的客户端程序，与具体的语言相关，例如Java中的JDBC，图形用户界面SQLyog等。本质上都是在TCP连接上通过MySQL协议和MySQL服务器进行通信。

3、服务层

（1）连接层

第一件事就是建立 TCP 连接、身份认证、权限获取

（2）服务层

Management Serveices & Utilities： 系统管理和控制工具

SQL Interface：SQL接口：接收用户的SQL命令，并且返回用户需要查询的结果。

Parser：解析器：解析器中SQL 语句进行`词法分析、语法分析、语义分析`，并为其创建`语法树`。

Optimizer：查询优化器：

 不改变查询结果前提下，调整sql顺序，生成执行计划

Caches & Buffers： 查询缓存组件：在MySQL 8之后就抛弃了这个功能。

（3）引擎层

负责MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信

4、存储层

所有的数据、数据库、表的定义、表的每一行的内容、索引，都是存在文件系统 上，以文件的方式存在，并完成与存储引擎的交互。

5、执行顺序

6、SQL执行流程（MySQL8）

（1）.开启profiling

SET profiling = 1;

（2）显示查询`

*执行sql

SELECT * FROM atguigudb.employee;

SELECT * FROM atguigudb.employee WHERE id = 5;

*查看计划

SHOW PROFILES;

（3）查看某个查询计划流程

SHOW PROFILE cpu,block io FOR QUERY 3;

1、MyISAM和InnoDB的区别

1. 创建测试数据

CREATE TABLE `t_dept` (`id` INT NOT NULL AUTO_INCREMENT,`deptName` VARCHAR(30) DEFAULT NULL,`address` VARCHAR(40) DEFAULT NULL,PRIMARY KEY (`id`));CREATE TABLE `t_emp` (`id` INT NOT NULL AUTO_INCREMENT,`name` VARCHAR(20) DEFAULT NULL,`age` INT DEFAULT NULL,`deptId` INT DEFAULT NULL,`empno` INT NOT NULL,PRIMARY KEY (`id`),KEY `idx_dept_id` (`deptId`)#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`));INSERT INTO t_dept(id,deptName,address) VALUES(1,'华山','华山');INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐帮','洛阳');INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');INSERT INTO t_dept(id,deptName,address) VALUES(4,'武当','武当山');INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明顶');INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);

2、常见七种JOIN查询

（1）查询所有有部门的员工信息以及他所在的部门信息SELECT * FROM t_emp a INNER JOIN  t_dept b ON a.`deptId` = b.`id`;（2）需求2：查询所有用户，并显示其部门信息（如果员工没有所在部门，也会被列出） => 查询A的全集SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.`deptId`= b.`id`;（3）需求3：列出所有部门，并显示其部门的员工信息（如果部门没有员工，也会被列出）=> 查询B的全集SELECT * FROM t_dept b LEFT JOIN t_emp a ON a.`deptId`= b.`id`;（4）**需求4：**查询`没有加入任何部门的员工`SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.`deptId`= b.`id` WHERE b.`id` IS NULL;（5）查询没有任何员工的部门SELECT * FROM t_dept b LEFT JOIN t_emp a ON a.`deptId`= b.`id`WHERE a.`id` IS NULL;（6）查询所有员工和所有部门 => AB全有SELECT a.*,b.* FROM t_emp a LEFT JOIN t_dept b ON a.`deptId`= b.`id`UNION ALLSELECT a.*,b.* FROM t_dept b LEFT JOIN t_emp a ON a.`deptId`= b.`id`WHERE a.`id` IS NULL;（7）查询没有加入任何部门的员工，以及查询出部门下没有任何员工的部门SELECT a.*,b.* FROM t_emp a LEFT JOIN t_dept b ON a.`deptId`= b.`id`WHERE b.`id` IS NULLUNION ALLSELECT a.*,b.* FROM t_dept b LEFT JOIN t_emp a ON a.`deptId`= b.`id`WHERE a.`id` IS NULL;

3、修改表，增加难度

（1）增加掌门人字段ALTER TABLE t_dept ADD CEO INT(11);UPDATE t_dept SET CEO=2 WHERE id=1;UPDATE t_dept SET CEO=4 WHERE id=2;UPDATE t_dept SET CEO=6 WHERE id=3;UPDATE t_dept SET CEO=8 WHERE id=4;UPDATE t_dept SET CEO=9 WHERE id=5;（2）求各个门派对应的掌门人SELECT * FROM t_emp a INNER JOIN t_dept bON b.`CEO` = a.`id`;（3）求所有掌门人的平均年龄SELECT AVG(a.`age`) FROM t_emp a INNER JOIN t_dept bON b.`CEO` = a.`id`;（4）求所有人物对应的掌门名称（4种写法分析）#1 NO3SELECT ab.name,c.`name` ceoname FROM(SELECT a.`name`,b.`CEO` FROM t_emp aLEFT JOIN t_dept b ON a.`deptId`=b.`id`)abLEFT JOIN t_emp c ON ab.ceo=c.`id`;#2 NO2SELECT c.name,ab.name ceoname FROM t_emp c LEFT JOIN(SELECT a.`name`,b.`id` FROM t_emp aINNER JOIN t_dept b ON b.`CEO` = a.`id`)abON c.`deptId`= ab.id;#3  NO1SELECT a.`name`,c.`name` ceoname FROM t_emp aLEFT JOIN t_dept b  ON a.`deptId`= b.idLEFT JOIN t_emp c ON b.`CEO`= c.`id`;#4  NO4SELECT a.`name`,(SELECT c.name FROM t_emp c WHERE c.id =b.`CEO`)ceonameFROM t_emp aLEFT JOIN t_dept b ON a.`deptId`=b.`id`;

1、是什么

索引（Index）是帮助MySQL高效获取数据的数据结构。

排好序的快速查找数据结构

2、索引优缺点

（1）优点：查询快、排序快

（2）缺点：所有写操作变慢

                 占用大量磁盘空间

3、索引分类

• 从功能逻辑上划分，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
• 按照作用字段个数划分，索引可以分为单列索引和联合索引。
• 按照物理实现方式划分 ，索引可以分为 2 种，分别是聚簇索引和非聚簇索引。

4、树

（1）二叉树

对于二叉排序树的任何一个非叶子节点，要求左子节点的值比当前节点的值小，右子节点的值比当前节点的值大。

*最好情况

*最坏情况

（2）平衡二叉树（AVL）

具有以下特点：

• 它是一棵空树或它的左右两个子树的高度差的绝对值不超过1
• 并且左右两个子树都是一棵平衡二叉树。

缺点

那么磁盘的IO次数和索引树的高度是相关的。平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下。

解决问题，可以使用平衡三叉树