发布时间：2023-09-27 09:00
create table [if not exists]表名(
字段名1 类型 [(宽度)] [约束条件] [comment \'字段说明\'],
字段名2 类型 [(宽度)] [约束条件] [comment \'字段说明\'],
字段名3 类型 [(宽度)] [约束条件] [comment \'字段说明\'],
)[表的一些设置];
创建表是构建一张块表，指定这个表的名字，这个表有几列，每一列叫什么名字，以及每一列存储的数据类型。
语法格式
alter table 表名 add 列名 类似(长度) [约束];
例子：
为student表添加一个新的字段为：系别 dept 类型为 varchar(20)
alter table student add \'dept\' varchar(20);
语法格式
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
例子：为student表的dept字段更换为department varchar(30)
alter table student change \'dept\' department varchar(30);
语法格式：
alter table 表名 drop 列名;
例如：删除student表中的department这列
alter table student drop department;
语法格式：
rename table 表名 to 新表名;
例子： 将表student改名为stu
rename table \'student\' to stu
DML是指数据操作语言，英文全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新。
语法格式：
// 向表中插入某些列
insert into 表名(列名1, 列名2, 列名3...) values (值1, 值2, 值3);
// 向表中插入所有列
insert into 表名(值1, 值2, 值3);
例子：
insert into student(sid,name,gender,age,birth,adderss) values(1001,\'man\',18,\'1996-12-23\',\'beijing\');
insert into student values(1001,\'男\', 18,\'1996-12-23\',\'北京\');
语法格式：
update 表名 set 字段名=值, 字段名=值...;
update 表名 set 字段名=值, 字段名=值... where 条件;
例子：
# 将所有学生的地址修改为曹县
update student set address = \'曹县\';
# 将id为1004的学生的地址修改为青岛
update student set address = \'青岛\' where id = 1004;
# 将id为1001的学生的地址修改为北京，成绩修改为100
update student set address = \'北京\',score = 100 where id = 1001;
语法格式：
delete from 表名 [where 条件];
truncate table 表名 或者 truncate 表名;
例子：
# 删除sid为1004的学生数据
delete from stduent where sid = 1004;
# 删除表所有数据
delete from student;
# 清空表数据
truncate table student;
truncate student;
注意：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table，可以理解为是将整个表删除，然后再创建该表。
from - on - join -where -group by -having -select - distinct order by - limit
概念
约束英文：constraint
约束实际上就是表中数据的限制条件
作用
表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户列表的值（手机号）不能为空，有些列的值（身份证号）不能重复。
分类
概念
创建单列主键有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键
方法1 - 语法：
# 在 create table 语句中，通过 primary key 关键字来指定主键
# 在定义字段的同时指定主键，语法格式如下：
create table 表名(
...
<字段名><数据类型> primary key
...
);
方法1-实现：
create table emp1(
eid int primary key,
name varchar(20),
salary double
);
方法2-语法：
#在定义字段之后再指定主键，语法格式如下：
create table 表名 (
...
[constraint<约束名>] primary key [字段名]
);
方法2-实现：
mysql> create table emp2(
-> eid int,
-> name varchar(20),
-> salary double,
-> constraint pk1 primary key(eid)
-> );
主键重复报错：
ERROR 1062 (23000): Duplicate entry \'1\' for key \'emp1.PRIMARY\'
主键不能为NULL报错：
ERROR 1048 (23000): Column \'eid\' cannot be null
所谓的联合主键，就是这个主键是由一张表中多个字段组成的。
注意：
语法：
create table 表名(
...
primary key(字段1, 字段2, ... ,字段n)
);
实现：
create table emp3(
name varchar(20),
deptId int,
salary double,
primary key(name,deptId)
);
联合主键中联合的列，只要有一列不相同，其他列相同旧认为是不同的。
语法：
create table 表名(
...
);
alter table 表名 add primary key (字段列表);
实现：
create table if not exists emp1(
eid int,
name varchar(20),
depId int,
salary double,
);
alter table emp1 add primary key(eid);
如果一个表已经有primary key，则不能再则不能在使用
ERROR 1068 (42000): Multiple primary key defined
一个表中不需要主键约束，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。
语法：
alter table 表名 drop primary key
实现：
# 删除主键（单列、多列）
alter table emp1 drop primary key;
概念
在MySQL中，当主键定义为自增长后，这个主键的值就不需要用户输入数据了，而由数据库根据定义自动赋值，每增加一条记录，主键会自动以相同的步长进行增长。
通过给字段添加auto-increment属性来实现主键自增长。
语法：
字段名 数据类型 auto_increment
实现：
create table emp1(
id int primary key auto_increment,
name varchar(20)
);
自增到int的最大范围时，无法继续添加数据：
ERROR 1062 (23000): Duplicate entry \'2147483647\' for key \'emp1.PRIMARY\'
特点
默认情况下，auto_increment的初始值是1，每新增一条记录，字段值自动加1
一个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键的一部分）。
auto_increment约束的字段必须具备NOT NULL属性。
auto_increment约束的字段只能是整数类型（tinyint，samllint，int，bigint）。
auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，就无法继续添加数据。
指定自增字段初始值
如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条件记录的id值设置为5，那么再插入记录时，id值就会从5开始往上增加。
# 方式1 创建表时指定
create table emp1(
id int primary key auto_increment,
name varchar(20)
)auto_increment=100;
# 方式2 创建表之后指定
create table emp1(
id int primary key auto_incremetn,
name varchar(20)
);
alter table 表名 auto_increment=100;
delete和truncate在删除后自增列的变化
概念
MySQL非空约束（not null）指字段的值不能为空。对于使用非空约束的字段，如果用户在添加数据时没有指定值，数据库就会报错。
语法
# 方式1
<字段名><数据类型> not null
# 方式2
alter table 表名 modify 字段 类型 not null;
实现
# 创建表时添加
create table emp1(
id int,
name varchar(20) not null,
address varchar(20) not null
);
alter table emp1 modify id int not null;
删除非空约束
alter table 表名 modify 字段 类型
概念
唯一约束（Unique key）是指所有记录中字段的值不能重复出现。例如，为id字段加上唯一性约束后，每条记录的id值都是唯一的，不能出现重复的情况。
语法
# 方式1
<字段名><数据类型> unique;
# 方式2
alter table 表名 add constraint 约束名 unique (列);
实现
create table emp1(
id int,
name varchar(20),
phone_number varchar(20) unique --指定唯一约束
);
在MySQL中 NULL和任何值都不相同 甚至和自己都不相同
删除唯一约束
语法
alter table 表名 drop index <唯一约束名>;
没有指定约束名，默认就为列名。
概念
MySQL默认值约束用来指定某列的默认值。
语法
# 方式1
<字段名><数据类型> default <默认值>
# 方法2
alter table 表名 modify 列名 类型 default 默认值;
实现
create table emp1(
id int,
name varchar(20),
address varchar(20) default \'beijing\' -- 指定默认约束
);
概念
操作
create table person(
id int zerofill, -- 零填充约束
name varchar(20)
);
删除
alter table person modify id int;
概念
语法
select
[all|distinct]
<目标列的表达式1>[别名],
<目标列的表达式2>[别名]...
from <表名或视图名>[别名],<表名或视图名>[别名]...
[where<条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];
简化版语法
select *| 列名 from 表 where 条件
简介
数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。
例如，学生表中存在一个birth字段，这个字段表示学生的出生年份。而运用MySQL的算数运算符用当年的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。
MySQL支持4中运算符
算数运算符
| 算数运算符 | 说明 |
|---|---|
| + | 加法运算 |
| - | 减法运算 |
| * | 乘法运算 |
| / 或 DIV | 除法运算，返回商 |
| % 或 MOD | 求余运算，返回余数 |
比较运算符
| 比较运算符 | 说明 |
|---|---|
| = | 等于 |
| < 和 <= | 小于和小于等于 |
| > 和 >= | 大于和大于等于 |
| <=> | 安全的等于，两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0 |
| <> 或 != | 不等于 |
| IS NULL | 判断一个值是否为NULL |
| IS NOT NULL | 判断一个值是否不为NULL |
| LEAST | 当有两个或多个参数时，返回最小值 |
| GREATEST | 当有两个或多个参数时，返回最大值 |
| BETWEEN AND | 判断一个值是落在两个值之间 |
| IN | 判断一个值是IN列表中的任意一个值 |
| NOT IN | 判断一个值不是IN列表中的任意一个值 |
| LIKE | 通配符匹配 |
| REGEXP | 正则表达式 |
# %用来匹配任意字符
select * from gooder where pname like \'%ku%\';
# _用来匹配一个字符
select * from gooder where pname like \'_kou\';
# 如果求最小值时，有个值为null，则不会进行比较，结果直接为null,
# greatest同理
select least(10, null, 20);
位运算符
位运算是二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。
然后再将计算结果从二进制数变回十进制数。
select 3&5; -- 位与
select 3|5; -- 位或
select 3^5; -- 位异或
select 3>>1; -- 位左移
介绍
如果我们需要对读取的数据进行排序，我们就可以使用MySQL的 order by子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
select
字段名1, 字段名2, ...
from 表名
order by 字段名1 [asc|desc], 字段名2[asc|desc];
特点
操作
# 使用价格排序（降序）
select * from gooder order by price desc;
# 在价格排序（降序）的基础上，以分类排序（降序）
# 价格相同 才会按分类进行排序
select * from gooder order by price desc, category_id asc;
# 显示商品的价格（去重复），并排序（降序）
select distinct price from product order by price desc;
简介
之前我们做的查询都是横向查询，它们都是根据条件一行一列的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
| 聚合函数 | 作用 |
|---|---|
| count() | 统计指定列不为NULL的记录行数 |
| sum() | 计算指定列的数值和，如果指定类型不是数值类型，那么计算结果为0 |
| max() | 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算 |
| min() | 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算 |
| avg() | 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0 |
聚合函数对-NULL值的处理
简介
分组查询是指使用group by子句对查询信息进行分组
格式
select 字段1, 字段2... from 表名 group by 分组字段 having 分组条件
操作
# 统计各个分类商品得个数
select category_id, count(*) from product group by category_id;
如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他字段不能出现。
分组之后的条件筛选-having
操作
# 统计各个分类商品的个数，且只显示个数大于4的信息
# SQL执行顺序 from -> group by -> count(pid) -> select -> having -> order by
select category_id, count(*) from product group by category_id having count(*) > 1;
简介
分页查询在项目开发中很常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示和1-5条，第二页显示6-10条。
格式
# 方法1 显示前n条
select 字段1，字段2... from 表名 limit n;
# 方法2 分页显示
select 字段1，字段2... from 表名 limit m,n;
# m: 整数，表示从第几条索引开始，计算方式（当前页 -1）*每页显示条数
# n: 整数，表示查询多少条数据
操作
# 查询product表的前5条记录
select * from product limit 5;
# 从第4条开始显示，显示5条
select * from product limit 3,5
简介
将一张表的数据导入到另一张表中，可以使用insert into select语句。
格式
# 1.
insert into table2(field1,field2,...) select value1,value2,...from Table1;
# 2.
insert into table2 select * from table1;
要求目标表Table2必须存在。
# ifnull(salary, 0)
# 如果salary的值为null,则当做0；不为null，则为salary
介绍
正则表达式（regular expression）描述了一种字符串匹配的规则，正则表达式本省就是一个字符串，使用这个字符串来描述、用于定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常用来检索、替换那些符合某个规则的文本。
MySQL通过REGEXP关键之支持正则表达式进行字符串匹配。
格式
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置 |
| $ | 匹配输入字符串的结束位置 |
| . | 匹配除“\\n”之外的任何单个字符 |
| […] | 字符串集合。匹配所包含的任意一个字符。例如’[abc]‘可以匹配’plain’中的’a’ |
| [^…] | 负值字符集合。匹配未包含的任意字符。例如，[ ^ abc ]可以匹配\"plain\"中的 ‘p’ |
| p1|p2|p3 | 匹配p1或p2或p3。例如，\'z|food’能匹配“z”或“food”。“（z|f）ood”能匹配“zood”或“food” |
| * | 匹配前面的子表达式零次或多次。例如，zo*能匹配“z”以及“zoo”。 *等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，‘zo+’能匹配“zo”以及“zoo”，但不能匹配“z”。+等价于{1，}。 |
| {n} | n是一个非负整数。匹配确定的n次。例如，“o{2}”不能匹配“Bob”中的“o”，但是能匹配“food”中的两个o。 |
| {n,m} | m和n均为非负整数，其中n<=m。最少匹配n次且最多匹配m次。 |
多表关系
MySQL多表之间的关系可以概况为：一对一、一对多、多对多关系。
一对一关系
一对多/多对一关系
部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门
实现原则：在多的一方建立外键，指向一的一方的主键。
多对多关系
学生和课程
分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
原则：多对多关系实现需要借助第三方中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键。
简介
MySQL外键约束（foreign key）是表的一个特殊字段，经常与主键约束以一起使用。对于两个具有关联关系的表而言，相关字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果、桃子、梨子、西瓜4种水果，那么你来到水果摊要买水果就只能选择苹果、桃子、梨子和西瓜，其他水果是不能购买的。
特点
定义一个外键时，需要遵守下列规则：
操作–创建外键约束
方式1：在创建表时设置外键约束
在create table语句中，通过foreign key关键字来指定外键
语法：
[constraint <外键名>] foreign key 字段名 [, 字段名2, ...] references <主表名> 主键列1 [, 主键2，...]
实现：
# 主表
create table if not exists dept(
detpno varchar(20) primary key, --部门号
name varchar(20) -- 部门名字
);
# 从表
create table if not exists employee(
eid varchar(20) primary key, -- 员工编号
ename varchar(20), -- 员工名字
age int,
dept_id varchar(20),
constraint emp_fk foreign key (dept_id) references department (detpno)
);
方式2 在创建表时设置外键约束
外键约束也可以在修改表时添加，但是添加外键外外键约束的前提是：从表中中外键列中的数据必须与主键列中的数据一致或者没有数据。
alter table <表名> add constraint <外键名> foreign key(列名) references <主表名>(<列名>);
删除外键约束
当一个表不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系
格式
alter table <表名> drop foreign key <外键约束名>;
实现
alter table employee drop foreign key dept_fk;
多对多关系
在多对多关系中，A表中的一行对于B的多行，B表的一行对应A表的多行，我们要新增加一个中间表，来建立多对多关系。
格式
select * from 表1, 表2, 表3...;
实现
select * from department,employee;
内连接查询求多张表的交集
格式
# 隐式内连接（SQL92标准）
select * from A,B where 条件;
# 显示内连接（SQL99标准）
selecr * from A inner join B on 条件;
操作
—— 查询每个部门的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
外连接分为左外连接（left outer join）、右外连接（right outer join）、满外连接（full outer join）。
注意：oralce里面有full join，可是1在MySQL对full join支持的不好。我们可以使用union来达到目的。
格式
# 左外连接
select * from A left outer join B on 条件;
# 右外连接
select * from A right outer join B on 条件;
# 满外连接
select * from A full outer join B on 条件;
介绍
子查询就是指的一个完整的查询语句中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是select嵌套的查询。
特点
子查询可以返回的数据类型一共分为四种：
子查询关键字
在子查询中，有一些常用的逻辑关键字，这些关键字可以给我们提供更丰富的查询功能。
ALL
格式
select * from 表名 where c > all(查询语句);
# 等价于
select ... from ... where c > result1 and c > result2 and c > result 3 ...
特点
操作
—— 查询年龄大于\'1003\'部门所有年龄的员工信息
select * from employee where age > all (select age from employee where dept_id = \'1003\')；
—— 查询不属于任何一个部门的员工信息
select * from employee where dept_id != all(select deptno from department);
ANY
格式
select ... from ... where > any(查询语句);
-- 等价于
select ... from ... where c > result1 or c > result2...
特点
操作
select * from employee where age > any(select age from dept_id = \'1003\');
IN
格式
select ... from where c in(查询语句);
-- 等价于:
select ... from ... where c = result1 or c = result or c=result2;
特点
操作
—— 查询研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename,t.name from employee where dept_id in(select deptno from department name = \'研发部\' or name = \'小说部\');
EXISTS
格式
select .. from ... where exists(查询语句);
特点:
操作
select * from employee a where exists(select * from employee b where a.age > 60);
select * from department a where exists (select * from employee b where a.deptno = b.dept_id)；
概念
MySQL有时在信息查询需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。
注意自关联时表必须起别名。
格式
select 字段列表 from 表1 a, 表1 b where 条件;
select 字段列表 from 表1 a join 表1 b on 条件;
格式
create trigger 触发器名 before | after 触发事件
on 表名 for each row
执行语句;
create trigger 触发器 before|after 触发事件
on 表名 for each row
begin
执行语句列表
end；
NEW与OLD
格式
MySQL中定义了NEW和OLD，用来表示触发器的所在表中，触发了触发器的那行一行数据，来引用触发器中发生变化的记录内容
| 触发器类型 | 触发器类型NEW和OLD的使用 |
|---|---|
| INSERT型触发器 | NEW表示将要或者已经新增的数据 |
| UPDATE型触发器 | OLD表示修改之前的数据，NEW表示将要或已经修改后的数据 |
| DELETE型数据 | OLD表示将要或者已经删除的数据 |
索引是存储引擎用来快速查找记录的一种数据结构，按照实现的方式分类，主要有Hash索引和B+Tree索引
一个索引只包含单个列，但一个表中可以有多个单列索引
普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯碎为了查询数据更快一点。
create index index_card_id on student(card_id);
alter table student add index index_age(age);
删除索引
-- 删除索引
drop index index_name on student;
alter table student drop index index_age;
查看索引
select * from mysql.`innodb_index_stats` a where a.`database_name` = \'mydatabase\';
show index from student;
select * from mysql.`innodb_index_stats` a where a.`database_name` = \'mydatabase\' and a.table_name like \'%student%\';
唯一索引
唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
-- 创建表的时候直接指定
create table if not exists student(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(11),
score double,
unique index_card_id(card_id) -- 给card_id列创建索引
);
-- 直接创建
create unique index index_phone_num on student(phone_num);
-- 修改表结构
alter table student add unique index_sid(sid);
主键索引
每张表一般都会有自己的主键，当我们在创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引。主键索引具有唯一性并且不允许为NULL，所以他是一种特殊的唯一索引。
create index indexname on student(phone_num,name);
-- 组合索引
-- 普通索引
create index indexname on student(phone_num,name);
drop index indexname on student;
-- 组合索引 唯一索引
create unique index index_phone_name on student(phone_num,name);
最左匹配原则
select * from student where name = \'张三\';
select * from student where phone_num = \'15100046637\';
select * from student where phone_num = \'15100046637\' and name = \'张三\';
select * from student where name = \'张三\' and phone_num = \'15100046637\';
/*
四条sql只有2、3、4能使用的到索引index_phone_num，因为条件里面必须包含索引前面的字段才能进行匹配
而3和4相比where条件的实现不一样，为什么4可以用到索引呢？是因为MySQL本身就有一层sql优化，会根据sql来识别出来该用哪个索引，我们可以理解为3和4在MySQL眼中是等价的。
*/
全文索引的版本、存储引擎、数据类型的支持情况：
MySQL中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不被索引。通俗点就是说，想对一个词语全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。
这两个的默认值可以使用以下命令查看：
show variables like \'%ft%\';
-- 修改表结构添加全文索引
alter table t_article add fulltext index_content(content);
drop index index_content on t_article;
-- 添加全文索引
create fulltext index index_content on t_article(content);
使用全文索引
和常用的模糊匹配使用 like+% 不同，全文索引有自己的语法格式，使用match和against关键字。
-- 使用全文索引
select * from t_article where match(content) against (\'yo\'); -- 没有结果，最小搜索长度为3
select * from t_article where match(content) against (\'yoo\'); -- 有结果
Hash算法
优点：通过字段的值计算的hash值，定位数据非常快。
缺点：不能进行范围查找，因为散列表中的值是无序的，无法进行大小的比较。
二叉树搜索树
特性：分为左子树、右子树和根节点，左子树比根结点要小，右子树比根节点值要大。
缺点：有可能产生不平衡，产生类似于链表的结构。
平衡二叉树
特点：
缺点：
BTREE树
目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，BTree结构可以有效的解决目前的相关算法遇到的问题。
B-Tree
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sMpqVR8g-1641373580479)(C:\\Users\\DELL\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220104103640579.png)]
B+Tree
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xO6aa39v-1641373580480)(C:\\Users\\DELL\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220104104307975.png)]
MyISAM引擎使用B+Tree
MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。
InnoDB引擎使用B+Tree
InnoDB的叶子节点的data域存放的是数据，相比于MyISAM效率要高一些，但是比较占硬盘内存大小。
优点
缺点
创建索引的原则
概念
分类
MyISAM：MySQL5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务。
InnoDB：事务性速记的首选引擎，支持ACID事务，支持行级锁，MySQL5.5成为默认数据库引擎
memory：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数量成正比的内存空间。并且其内容会在MySQL重启启动后丢失。
Archive：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差。
Federated：将不同的MySQL服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用。
| 功能 | MyISAM | MEMORY | InnoDB |
|---|---|---|---|
| 存储限制 | 256TB | RAM | 64T |
| 支持事务 | NO | NO | YES |
| 支持全文索引 | YES | NO | NO |
| 支持B树索引 | YES | YES | YES |
| 支持哈希索引 | NO | YES | NO |
| 支持集群索引 | NO | NO | YES |
| 支持数据索引 | NO | YES | YES |
| 支持数据压缩 | YES | NO | NO |
| 空间使用率 | 低 | N/A | 高 |
| 支持外键 | NO | NO | YES |
-- 查询当前数据库支持的存储引擎
show engines;
-- 查看当前的默认存储引擎
show variables like \'%storage_engine%\';
-- 查看某个表使用了什么存储引擎
show create table student;
drop table if exists student;
-- 创建表时，指定存储引擎
create table if not exists student(id int, name varchar(20), gender varchar(10)) engine = MyISAM;
-- 通过alter命令来修改表的引擎
alter table student engine = INNODB;
什么是事务
MySQL的事务操作主要有以下三种：
之前的所有SQL操作其实也有事务，只是MySQL自动帮我们完成的，每执行一条SQL时MySQL就帮我们自动提交事务，因此如果想要手动控制事务，则必须关闭MySQL的事务自动提交。
在MySQL中直接用set来改变MySQL的自动提交模式
set autocommit = 0; -- 禁止自动提交
set autocommit - 1; -- 开启自动提交
事务的特性
事务的隔离级别
lsolate，顾名思义就是将事务与另一个事务隔离开，为什么要隔离呢？如果一个事务正在操作的数据被另一个事务修改或删除了，最后的执行结果可能无法达到预期。如果没有隔离性还会导致其他的问题。
读未提交 read uncomitted
一个事务可以读取另一个未提交的数据，最低级别，任何情况下都无法保证，会造成脏读。
读提交 read commited
一个事务要等另一个事务提交后才能读取数据，可避免脏读，会造成不可重复读。
repeateable read 可重复度（MySQL默认）
就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生。
serializale串行化
是最高的事务隔离级别，会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
| 事务隔离级别（往下隔离级别越强） | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交read-uncommitted | yes | yes | yes |
| 读提交 read-committed | no | yes | yes |
| 可重复读 repeatable-read | no | no | yes |
| 串行化 serializable | no | no | no |
脏读
如果一个事务读到了另一个未提交事务修改过的数据，就意味着发生了脏读现象。
不可重复读
在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了不可重复度现象。
脏读
在一个事务内多次查询某个符合查询条件的记录数量，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了幻读现象。
概述
锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争夺）。
在数据库中，除传统的计算机资源（如CPU、RAM、I/O等）的争抢以外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
从对数据操作的粒度分：
从对数据操作的类型分：
| 存储引擎 | 表级锁 | 行级锁 |
|---|---|---|
| MyISAM | 支持 | 不支持 |
| InnoDB | 支持 | 支持 |
| MEMORY | 支持 | 不支持 |
| BDB | 支持 | 不支持 |
表级锁
偏向MyISAM存储引擎，开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率最高，并发度最低。
行级锁
偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁，锁粒度最小哦啊，发生锁冲突的概率最低并发度也最高。
MyISAM表锁
MyISAM在执行查询语句（select）前，会自动给涉及的所有表叫读锁，在执行更新操作（update、delete、insert等）前，会自动给涉及的表加写锁，这个过程并不需要用户的干预，因此，用户一般不需要直接用lock table命令给MyISAM表显式加锁。
-- 加读锁:
lock table table_name read;
-- 加写锁:
lock table table_name write;
-- 释放锁
unlock tables;
InnoDB行锁
行锁特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁，锁粒度最小哦啊，发生锁冲突的概率最低并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。
行锁模式
InnoDB实现了以下两种类型的行锁。
共享锁（S）：又称读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁（X）：又称为写锁，简称X锁，排他锁就是不能和其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获得改行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务可以对数据行读取和修改。
对于UPDATE、delete和insert语句，InnoDB会自动给涉及数据加排他锁（X）
对于普通的SELECT语句，InnoDB不会加如何锁。
错误日志
错误日志是MySQL中最重要的日志之一，它记录了当前MySQL启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。
该日志是默认开启的，默认存放目录为MySQL的数据目录，默认的日志文件名为hostname.err（hostname是主机名）。
查看日志位置指令：
show variables like \'log_error%\';
二进制日志-binlog
二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操作语言）语句。但是不包括数据查询语句。此日志对于灾难性时的数据恢复起着极其重要的作用，MySQL的主从放置，就是通过binlog实现的。
二进制日志，MySQL默认已经开启，低版本的MySQL需要通过配置文件开启，并配置MySQL日志的格式。
日志格式
STATEMENT
该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件中，通过MySQL提供的MySQLbinlog工具，可以清楚的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。
ROW
该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句，比如执行SQL语句；update tb_book set status = ‘1’，如果是statement日志格式，在日志中会记录一行SQL文件；如果是row，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW格式的日志中记录每一行的数据变更。
MIXED
混合了STATEMENT和ROW两种格式。
-- 查看所有日志
show binlog events;
-- 查看最新的日志
show master status;
-- 查询指定的binlog日志
show binlog events in \'binlog.000003\' ;
-- 清空所有的binlog日志文件
reset master;
查询日志
-- 该选项用来开启查询日志，可选项：0代表开启，1代表关闭
general_log = 1;
-- 设置日志的文件名，如果没有指定，默认的文件名为host_name.log
general_log_file=file_name;
慢查询日志
慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志。long_query_time默认为110秒，最小为0，精度可以到微秒。
-- 慢查询日志查询
-- 查看慢查询日志是否开启
show variables like \'slow_query_log%\';
-- 开启慢查询日志
SET global slow_query_log = 1;
-- 查看慢查询的超时时间
show variables like \'long_query_time%\';
MySQL的优化方式有很多，大致我们可以从以下几点来优化MySQL：
查看SQL执行的频率
-- 查看当前会话SQL执行类型的统计信息
show session status like \'Com_______\';
-- 查看全局（自从上次MySQL服务器启动至今）执行类型的统计信息
show global status like \'Com_______\';
-- 查看针对innoDB引擎的统计信息
show status like \'InnoDB_rows_%\';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3VyjKD9O-1641373580481)(C:\\Users\\DELL\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220104223231458.png)]
定位低效率执行SQL
可以通过以下两种方式定位执行效率较低的SQL语句
-- 查看慢日志配置信息
show variables like \'%slow_query_log%\';
-- 开启慢日志查询
set global show_query_log = 1;
-- 查看慢日志记录SQL的最低阈值时间
show variables like \'%long_query_time%\';
-- 修改慢查询日志记录SQL的最低阈值时间
set global long_query_time = 5;
-- 动态定位查询效率低的SQL
show processlist;
通过以上步骤查询到效率低的SQL语句后，可以通过EXPLAIN命令获取MySQL如何执行select语句的信息，包括在select语句在执行过程中表如何连接和连接的顺序。
explain select * from user where uid = 1;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LHPzcqVH-1641373580481)(C:\\Users\\DELL\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220105121918335.png)]
| 字段 | 含义 |
|---|---|
| id | select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。 |
| select_type | 表示select的类型，常见的取值有simple（简单表，既不使用表连接或者子查询）、Primary(主查询，即外层的查询)、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等 |
| table | 输出结果集的表 |
| type | 表示表的连接类型，性能有好到差的连接类型为（system----> const-----> eq_ref ----> ref ----> ref_or_null -----> index_merge —> index_subquery —> range —> index ----> all） |
| possible_keys | 表示查询时，可能使用的索引 |
| key | 表示实际使用的索引 |
| key_len | 索引字段的长度 |
| rows | 扫描行的数量 |
| extra | 执行情况的说明和描述 |
| select_type | 含义 |
|---|---|
| simple | 简单的select查询，查询中不包含子查询或者union |
| primary | 查询中若包含任何复杂的子查询，最外层查询标记为该查询 |
| subquery | 在select或where列表中包含了子查询 |
| derived | 在from列表中包含的子查询，被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表中 |
| union | 若第二个select出现在union之后，则标记为union；若union包含在from子句的子查询中，外层select将被标记为：derived |
| union result | 从union表获取结果的select |
type显示的是访问类型，是叫为重要的一个指标。
| type | 含义 |
|---|---|
| NULL | MySQL不访问任何表，索引直接返回结果 |
| system | 系统表，少量数据，往往不需要进行磁盘IO；如果是5.7以上版本的化就不是system了，而是all，即使只有一条记录。 |
| const | 命中主键（primary key）或者唯一（unique）索引；被连接的部分是一个常量（const）值 |
| eq_ref | 对于前表的每一行，后表只有一行被扫描。（1）join查询；（2）命中主键（primary key）或者非空唯一（unique not null）索引；（3）等值连接； |
| ref | 非唯一性索引扫描，返回匹配某个单独值的所有行。对于前表得每一行（row），后表可能有多于一行的数据被扫描 |
| range | 只检索给定返回行，使用一个索引来选择行，where之后出现between，<，>, in等操作 |
| index | 需要扫描索引上的全部数据 |
| all | 全表扫描，此时id上无索引 |
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
-- 查看当前的MySQL是否支持profile
select @@have_profiling;
-- 如果不支持，则需要设置打开
set profiling = 1;
-- 查看大致耗时
show profiles;
-- 查看详细耗时
show profile for query 61;
-- 查看CPU详细耗时
show profile cpu for query 80;
| 字段 | 含义 |
|---|---|
| Status | sql语句执行的状态 |
| Duration | sql执行过程中每一步骤的耗时 |
| CPU_user | 当前用户占用的cpu |
| CPU_system | 系统占有的CPU |
MySQL5.6提供了SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是B计划。
-- 全值匹配和字段匹配成功即可，和字段的先后顺序无关。
explain select * from ta_seller where name = \'Yokoyama Mai\' and status = \'1\' and address = \'270 Diplomacy Drive\';
explain select * from ta_seller where status = \'1\' and address = \'270 Diplomacy Drive\' AND name = \'Yokoyama Mai\';
create index idx_seller_name_sta_addr on ta_seller(name,status,address);
-- 最左前缀法则
-- 索引失效
explain select * from ta_seller where status = \'1\' and address = \'270 Diplomacy Drive\' ;
-- 用到了索引
explain select * from ta_seller where name = \'Yokoyama Mai\';
-- 用到了索引，只有name生效，address没有生效
explain select * from ta_seller where address = \'270 Diplomacy Drive\' AND name = \'Yokoyama Mai\';
-- 范围查询右边的列，不能使用索引
-- 根据前面的两个字段name，status，查询时走索引的，但是最后一个条件address没有用到索引。
explain select * from ta_seller where name = \'Yokoyama Mai\' and status > \'1\' and address = \'270 Diplomacy Drive\';
-- 不要在索引列上进行运算操作，索引将失效
explain select * from ta_seller where SUBSTRING(name,3,2)=\'Mai\';
-- 字符串不加单引号，造成索引失效
explain select * from ta_seller where name = \'Yokoyama Mai\' and status = 1;
-- 尽量使用覆盖索引，避免select *
-- 需要从原表及磁盘上读取数据
explain select * from ta_seller where name = \'Yokoyama Mai\' and address= \'270 Diplomacy Drive\';
-- 从索引树上就可以查询到所有的数据
explain select name from ta_seller where name = \'Yokoyama Mai\' and address= \'270 Diplomacy Drive\';
/*
Extral
using index : 使用覆盖索引的时候就会出现
using where : 在查找使用索引的情况下，需要回表去查询所需的数据
using index condition : 查找使用了索引，但是需要会表查询数据
using index;using where : 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
-- 用or分割开的条件，那么涉及的索引都不会被用到
explain select * from ta_seller where sellerid = 200 or status = \'1\';
-- 以%开头的like模糊查询，索引失效
-- 索引有效
explain select * from ta_seller where name like \'Yo%\';
-- 索引失效
explain select * from ta_seller where name like \'%Yo\';
-- 弥补不足，不用*，使用索引列
explain select name from ta_seller where name like \'%Yo%\';
-- 如果MySQL评估使用索引比全表慢，则不使用索引
-- 这种情况是由数据本身的特点来决定的
create index index_address on ta_seller(address);
explain select * from ta_seller where address = \'186 Narborough Rd\';
-- is NULL, is not null 有时有效，有时索引失效
-- 这种情况是由数据本身的特点来决定的
explain select * from ta_seller where address is null;
explain select * from ta_seller where address is not null;
-- in走索引， not in 索引失效
explain select * from ta_seller where address in(\'186 Narborough Rd\',\'V\');
explain select * from ta_seller where address not in(\'186 Narborough Rd\',\'V\');
-- 单列索引和符合索引，尽量使用符合索引
create index idx_seller_name_sta_addr on ta_seller(name,status,address);
/*
等价于：
NAME
name + STATUS
name + status + address
*/
-- 如果一张表有多个单列索引，即使where中都使用了这些索引列，则只有一个最优索引生效
-- 加载数据
-- 结论通关load向表中加载数据时，尽量保证文件中的主键有序，这样可以提高运行执行效率
load data local infile \'D:\\\\sql_data\\\\sql1.log\' into table tb_user fields terminated by \',\' lines terminated by \'\\n\';
-- 关闭唯一性效验
show global variables like \'unique_checks\';
set unique_checks = 0;
-- 减少客户端与服务器连接的次数
-- 原始方式
insert into user values(1,\'lisi\',\'123456\');
insert into user values(2,\'zhangsan\',\'123456\');
insert into user values(3,\'wangwu\',\'123456\');
-- 优化后的方案
insert into user values(1,\'lisi\',\'123456\'),(2,\'zhangsan\',\'123456\'),(3,\'wangwu\',\'123456\');
-- 在事务中进行数据插入
begin
insert into user values(1,\'lisi\',\'123456\');
insert into user values(2,\'zhangsan\',\'123456\');
insert into user values(3,\'wangwu\',\'123456\');
commit;
-- 数据插入有序
-- 原始方式
insert into user values(4,\'laowa\',\'123456\');
insert into user values(1,\'lisi\',\'123456\');
insert into user values(2,\'zhangsan\',\'123456\');
insert into user values(3,\'wangwu\',\'123456\');
-- 优化后
insert into user values(1,\'lisi\',\'123456\');
insert into user values(2,\'zhangsan\',\'123456\');
insert into user values(3,\'wangwu\',\'123456\');
insert into user values(4,\'laowa\',\'123456\');
两种排序方式
第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫filesort排序。
第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
-- 排序 oreder by
explain select * from employee order by age; -- using filesort
explain select * from employee order by age,salary; -- using filesort
explain select id from employee order by age; -- using INDEX
explain select id, salary FROM employee order by age; -- using index
explain select id,age from employee order by age desc,salary desc; -- Backward index scan; Using index
-- order by 后边的多个排序字段顺序尽量和组合索引字段顺序一致
explain select id,age from employee order by salary ,age; -- Using index; Using filesort
FileSort的优化
MySQL通过比较系统变量max_length_for_sort_data的大小和query语句取出字段总大小，来判定是否选择那种排序方式，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率。
使用子查询可以一次性的完成很大逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表死锁，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（join）替代。
连接（join）查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
一把分页查询时，通过创建索引能够比较好地提高性能。一个常见又头疼地问题就是limit 90000，10，此时需要MySQL排序前90010条记录，其他记录丢弃，查询排序地代价非常大。
优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要地其他列内容。
select * from tab_user limit 0,10;
explain select * from ta_user limit 900000,10;
explain select * from ta_user as a,(select id from tb_user order by id limit 90000,10) as b where a.id = id;
优化思路二
explain select * from tb_user where id > 900000 limit 10;
insert into user values(3,‘wangwu’,‘123456’);
– 优化后
insert into user values(1,‘lisi’,‘123456’);
insert into user values(2,‘zhangsan’,‘123456’);
insert into user values(3,‘wangwu’,‘123456’);
insert into user values(4,‘laowa’,‘123456’);
### 优化order by语句
两种排序方式
第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫filesort排序。
第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
```sql
-- 排序 oreder by
explain select * from employee order by age; -- using filesort
explain select * from employee order by age,salary; -- using filesort
explain select id from employee order by age; -- using INDEX
explain select id, salary FROM employee order by age; -- using index
explain select id,age from employee order by age desc,salary desc; -- Backward index scan; Using index
-- order by 后边的多个排序字段顺序尽量和组合索引字段顺序一致
explain select id,age from employee order by salary ,age; -- Using index; Using filesort
FileSort的优化
MySQL通过比较系统变量max_length_for_sort_data的大小和query语句取出字段总大小，来判定是否选择那种排序方式，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率。
使用子查询可以一次性的完成很大逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表死锁，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（join）替代。
连接（join）查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
一把分页查询时，通过创建索引能够比较好地提高性能。一个常见又头疼地问题就是limit 90000，10，此时需要MySQL排序前90010条记录，其他记录丢弃，查询排序地代价非常大。
优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要地其他列内容。
select * from tab_user limit 0,10;
explain select * from ta_user limit 900000,10;
explain select * from ta_user as a,(select id from tb_user order by id limit 90000,10) as b where a.id = id;
优化思路二
explain select * from tb_user where id > 900000 limit 10;