MySQL是后端的重要工具，之前一直想学习来着，但是一直没有机会，现在在掘金写一篇记录自己学习过程中的问题及想法。
MySQL
启动： systemctl start mysqld
查询状态： systemctl status mysqld
查看版本： mysql -V
查看是否启动： ps -ef | grep mysqld
停止： systemctl stop mysql.service
进入命令行： mysql -u root -p
数据库
数据库（Database）是数据有组织的存储的仓库
MySQL是数据库管理系统
SQL是操作关系型数据库的编程语言
关系型数据库：建立在关系模型上的，由多张相互连接的二维表组成的数据库
SQL
1.单行或多行书写，以分号结尾
2.可以使用空格，缩进增强可读性
3.不区分大小写，建议关键字使用大写
4.单行注释--或# 多行注释/* */
通用语法
SQL分类
DDL
Data Definition language 定义数据库，表，字段等
数据库操作
查询所有数据库: SHOW DATABASES;
查询当前数据库: SELECT DATABASE();
创建数据库: CREATE DATABASE 数据库名;
删除数据库： DROP DATABASE 数据库名;
使用数据库: USE 数据库名;
表结构操作
查询当前数据库所有表： show tables;
查看表结构: DESC 表名;
查看建表语句: show create table 表名;
创建表: create table name(字段一 字段一类型[注释]， 字段二 字段二类型)；
数据类型
数型
TINYINT 1byte
SMALLINT 2byte
MEDIUMINT 3byte
INT 4byte
BIGINT 8byte
FLOAT 4byte
DOUBLE 4byte eg. score double(4,1)最长四位，小数一位
DECIMAL (依赖于精度和标度)123.45 精度5标度2
字符串
char （定长）char(10)性能好
varchar（变长）varchar(10)
TINYBLOB(二进制)
TINYTEXT
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT
日期类型
DATE 年月日
TIME 时分秒
YEAR 年
DATETIME 年月日 时分秒
TIMESTAMP
修改表结构
添加字段: ALTER TABLE 表名 ADD 字段名 类型 comment '';
修改数据类型： ALTER TABLE 表名 MODIFY 字段名 新数据类型;
修改字段名和数据类型： ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型;
删除字段: ALTER TABLE 表名 DROP 字段名;
修改表名: ALTER TABLE RENAME TO 新表名;
删除表: DROP TABLE IF_EXIST 表名;
删除指定表并重新创建: TRUNCATE TABLE 表名;
DML
Data Manipulation language 表中数据进行增删改
添加数据：
给指定字段添加数据： INSERT INTO 表名(字段名1,字段名2) VALUES（值1,值2）;
给全部字段添加数据: INSERT INTO 表名 VALUES(值1,值2,...);
批量添加数据: INSERT INTO 表名(字段1,字段2,...) VALUES(值1,值2,...),(值1,值2),（值1,值2）;
INSERT INTO 表名 VALUES(值1,值2),(值1,值2),(值1,值2,..)；
修改数据
UPDATE 表名 SET 字段名1=值1,字段名2=值2,字段名3=值3,... [where 条件];
删除数据
DELETE FROM 表名 [where 条件]
DQL
Data Query Language 查询数据库中表的记录
基本查询
查询多个字段: SELECT 字段1 字段2 字段3... FROM 表名; SELECT * FROM 表名;
设置别名: SELECT 字段1 [ as 别名1] 字段2[ as 别名2] ... FROM 表名.
去除重复记录 SELECT DISTINCT 字段列表 FROM 表名;
SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT; 顺序不能变
执行顺序 FROM WHERE GROUP BY SLECT ORDER BY LIMIT
条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表
条件 > >= < <= = != between..and.. in(..) like IS NULL IS NOT NULL && || !
ps. in中只要有一个满足就可以 between and 是左闭右闭. LIKE是占位符 _ 占单位 %占任意位
聚合函数
聚合函数：将一列数据作为一个整体，进行纵向计算
常见聚合函数： count max min avg sum
用法: select 聚合函数（字段）FROM 表名;
分组查询
SELECT 字段列表 FROM 表名 [where 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]
排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2； 排序方式（ASC升序 DESC降序）
分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引 查询记录数;
Data Control Language(DCL)
创建用户及控制权限
在mysql库user表里
创建用户: CREATE USER '用户名'@'主机名' IDENTIFIED BY ‘密码’; %可以代表任意主机
修改密码:ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户: DROP USER '用户名'@'主机名';
权限控制
查询权限: SHOW GRANTS FOR ‘user‘@’host’;
授予权限: GRANT 权限列表 ON 数据库名 表名 TO 'user'@'host';
撤销权限：REVOKE 权限列表 ON 数据库名 表名 FROM 'user'@'host';
函数
一段可以直接被里一段程序调用的程序或代码。
字符串函数
CONCAT(s1,..,sn)拼接
LOWER(str)
UPPER(str)
LPAD(str,n,pad)左填充到n
RPAD(str,n,pad)
TRIM(str)去除开头结尾的空格
SUBSTRING(str,start,len)取子串
数值函数
CEIL(x)
FLOOR(x)
MOD(x,y)
RAND()
ROUND(x,y)四舍五入保留Y位小数
日期函数
CURDATE() CURTIME() NOW() YEAR(date) MONTH(date) DAY(date) DATE_ADD(date,interval expr type) DATEDIFF(date1,date2)
流程函数
IF(value，t，f) IFNULL(value1,value2) CASE WHEN [val1] THEN [res1]END CASE
约束
作用于表中字段上的规则，用来限制存储在表中的数据
目的： 保证数据库中数据的正确性，有效性，完整性
分类：
1.非空约束 NOT NULL
2.唯一约束 UNIQUE
3.主键约束(非空且唯一) PRIMARY KEY
4.默认约束(未指定则采用默认值) DEFAULT
5.检查约束（字段值满足某一个条件）CHECK
6.外键约束（两张表的数据之间建立连接）
7.自动增长 AUTO_INCREMENT
create table user 约束
(
id int primary key auto_increment comment 'primary key',
name varchar(10) not null unique comment 'name',
age int check (age > 0 and age <= 120) comment 'age',
status char(1) default '1' comment 'status',
gender char(1) comment 'gender'
) comment '用户表';
外键约束
连接两张表之间的数据，具有外键的表称为子表，关联的表称为父表
constraint 外键名称 foreign key (外键字段名) references 主表（主表列名）
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY（外键字段名）REFERENCES 主表（主表列名） ON UPDATE CASCADE ON DELETE CASCADE;
删除外键: ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
删除/更新行为
NO ACTION
RESTRICT
CASCADE 父表更新时子表也会更新
SETNULL
SET DEFAULT
多表查询
多表关系
1.一对多(多的一方建立外键）
eg.部门-员工
2.多对多（建立中间表，至少包含两个外键，分别关联两方主键）
eg.学生与课程的关系
3.一对一（外键UNIQUE）
eg.用户与用户详情
多表查询概述
select * from emp,dept where em.dept_id == dept.id;
内连接
查询A,B表交集部分
外连接
左外连接：查询左表所有数据，以及两张表交集部分
left join
右外连接：查询右表所有数据，以及两张表交集部分 right join
自连接
当前表与自身连接查询
同一个表访问两次，起两个别名
select a.name b.name from emp a, emp b where a.managerid = b.id;
多表查询
UNION
字段的个数要一样才能合并，并且类型也要一致
select from union all/union select from 多次查询合并起来形成一个新的查询结果集
union合并后去重 union all不去重
子查询
嵌套查询
select * from t1 where column1 = {select column1 from t2};
标量子查询
返回结果是单个值
select id from dept where name='销售';
select * from emp where dept_id=4;
合并后 select * from emp where dept_id=（select id from dept where name='销售'）;
列子查询
返回结果是一列
常用操作符(in not in any some all) any/some(任意一个满足即可) all全部都要满足
select id from dept where name ='销售部' or name = '市场部';
select * from emp where dept_id in (2,4);
合并后 select * from emp where dept_id in (select id from dept where name ='销售部' or name = '市场部');
行子查询
子查询返回的结果是一行
常用操作符(= <> in not in)
select salary,manager_id from emp where name='张无忌';
select * from emp where (salary,manager_id) = (select salary,manager_id from emp where name='张无忌');
表子查询
返回的结果是多行多列
常用符号in
select job,salary from emp where name='鹿杖客'or name='宋远桥';
select * from emp where (job,salary) in (select job,salary from emp where name='鹿杖客'or name='宋远桥');
select * from emp where entry_date>'2006-01-01';
select e.* d.* from (select * from emp where entry_date>'2006-01-01') e left join dept d on e.dept.id=d.id;
事务
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作视为一个整体一起向系统提交或撤销操作请求，即要么同时成功，要么同时失败。
事务操作
select @@autocommit; 1为自动提交0为手动提交
set @@autocommit = 0; 设置为手动提交
手动提交需要添加一行命令
COMMIT;提交事务
ROLLBACK;回滚事务
START TRANSACTION; 或 BEGIN; 开启事务
事务四大特性
ACID
原子性： 事务是一组不可分割的最小操作单元
一致性：事务完成时，必须是所有的数据都保持一致状态
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下
持久性：事务一旦提交或回滚，对数据库中数据的改变是永久的
并发事务问题
脏读：一个事务读取到另一个事务还未提交的数据
不可重复读：一个事物先后读取同一条记录，但两次读取的数据不同，称为不可重复读
幻读：一个事务按照条件查询数据时，没有对应数据行，但是在插入数据时，又发现这行数据已经存在了。
事务隔离级别
解决并发事务引发的问题
脏读 不可重复读 幻读
READ uncommitted √ √ √
READ committed × √ √
Repeatable Read（Mysql 默认级别） × × √
Serializable × × ×
select @@TRANSACTION_ISOLATION;查看隔离级别
SET [session|GLOBAL] TRANSACTION ISOLATION LEVEL {}
进阶篇
存储引擎
简介
存储引擎是存储数据，建立索引，更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的。
默认innoDB
show engines;当前数据库支持的存储引擎
create table my_myisam(
id int,
name varchar(10)
)engine = Myisam; 创建指定存储引擎
特点
InnoDB
兼顾高可靠性和高性能的通用存储引擎
特点： DML遵循ACID，支持事务
行级锁，提高并发访问性能
支持外键约束
xxx.idb存储该表的表结构，数据和索引
show variables like 'innodb_file_per_table';
idb2sdi account.ibd;查看idb文件
MyISAM
早期默认引擎
不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快
xxx.sdi:存储表结构信息
xxx.MYD:存储数据
xxx.MYI:存储索引
Memory
存储在内存中，只能用作缓存表
访问速度快
支持hash索引
xxx.sdi 存储表结构信息
选择存储引擎
索引
索引概述
帮助MySQL高效获取数据的数据结构（有序）
优点：提高检索效率，降低IO成本；降低排序成本，降低CPU消耗
缺点: 占用空间；提高了查询效率，降低更新表的速度，如ISNERT，UPDATE，DELETE
索引结构
MySQL在存储引擎层实现的，不同的存储引擎有不同结构
二叉树:顺序插入时，会形成一个链表，查询性能大大降低，大数据时，层级较深，检索慢
红黑树（自平衡的二叉树):大数据时层级较深
B-TREE(多路平衡查找树):最大度数为n称为 n阶b-tree
中间节点向上分裂
B+-TREE：所有元素都出现在叶子结点，根节点只是起索引作用，同时叶子结点会形成单向链表
MySQL B+ TREE
HASH
底层结构用hash表，不支持范围查询
只能用于等值比较，不支持范围查询
无法完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常高于B+TREE索引
R-TREE（空间索引）
主要用于地理空间数据类型，使用较少
FULL-text（全文索引）
倒排索引，快速匹配文档的方式
索引分类
常规分类
主键索引：针对表中主键创建的索引，只能有一个 PRIMARY
唯一索引：避免同一个表中某数据列的值重复UNIQUE
常规索引：快速定位特定数据
全文索引：查找文本关键词 FULLTEXT
只包含一个字段的索引叫做单列索引
包含两个或以上字段的索引叫做复合索引（或组合索引）
InnoDB引擎中的存储形式
聚集索引：数据存储与索引放到了一块儿，叶子结点放一行的数据（只有一个）
如果存在主键，主键索引就是聚集索引；如果不存在主键，将使用第一个唯一索引作为聚集索引；如果没有唯一索引，则创建一个隐含的唯一索引
二级索引：数据索引分开存储，叶子结点存放聚集索引的对应值，再去聚集索引查找
索引语法
create [UNIQUE|FULLTEXT] index index_name on table_name (index_col_name,...);创建索引(默认为常规索引)
SHOW index from table_name;查看索引
DROP INDEX index_name on table_name;删除索引
SQL性能分析
show [session|global] status;当前服务器状态信息，当前数据库的insert,update,delete,select的访问频次
eg.show global status like Com____；
慢查询日志：记录了所有执行时间超过指定参数的所有SQL语句的日志；默认没有开启
show variable like 'slow_query_log';查看是否开启
/etc/my.cnf 文件
slow_query_log = 1
long_query_time = 2
show profile 能够在做SQL优化时帮助我们了解到时间都耗费到哪里了。
set profiling =1; 开启
show profile for query query_id;该命令的详细执行时间
explain 任意字句
eg. explain select * from tb_user where id=1;
索引使用
索引可以大幅提高查询效率
最左前缀法则：查询时从索引中最左侧列开始,不跳过索引中的列，如果最左侧列不存在，则索引失效。如果中间跳过，则后面部分失效
范围查询：联合索引中，出现范围查询（><），范围查询右侧的列索引失效
规则一：不要在索引列上进行运算操作，索引将失效
规则二：字符串不加引号，索引将失效
规则三：如果头部（‘软件%’）进行模糊匹配则不失效，若尾部（‘%工程’）进行模糊匹配则失效
规则四： 用or分开的条件，如果只有一侧有索引，那么索引失效
规则五：数据分布影响：如果MySQL评估索引比全表更慢，则不使用索引
SQL提示：在SQL语句中加入认为的提示来达到优化操作的目的
use index;(建议)
ignore index;（忽略）
force index;（强制）
eg. select * from user_table use index(idx_user_pro) where profession=5;
覆盖索引：尽量使用覆盖索引（查询使用了索引并且需要返回的列在该索引中已经全部能找到），减少select*;
using where;using index(查询使用了索引，需要的数据在该索引列都能找到，不需要回表) 性能高于using index condition(查询使用了索引，但是需要回表查询)
前缀索引：当字段类型为字符串时，有时候需要很长的字符串，会让索引变得很大。查询时浪费大量磁盘IO。此时可以只将字符串的一部分前缀，建立索引。
eg. create index idx xxx on table name(column(n));
select count(distinct email) / count(*) from tb_user;
select count(distinct sbstring(email,1,5)) / count(*) from tb_user;
单列索引如果有多个条件，只根据一个索引查询，因此会回表 eg. select id,phone,name from tb_user where phone='' and name='';
联合索引: 在查询时 使用use index 可以覆盖索引
索引设计原则
1.针对数据量较大，且查询频繁的表建立索引
2.针对于常作为查询条件，排序，分组操作的字段建立索引
3.尽量选择区分度高的列作为索引，尽量建立唯一索引
4.如果时字符串类型且长度较长，可以建立前缀索引
5.尽量使用联合索引
6.控制索引数量，维护索引结构的代价很大会影响增删改的效率
7.如果索引列不能存储NULL值，在创建表时用NOT NULL约束它
