MySQL基础
SQL语言
DDL:
描述：data definition language 数据操纵语言,主要负责对数据库的操作、对表操作的查询、创建表、修改表和删除表
数据库操作
查询所有数据库
show databases;查询当前数据库
select databse();创建数据库
create databases [IF NOT EXISTS] 数据库名字 [DEFAULT CHARSET 字符集][COLLATE 排序规则];[DEFAULT CHARSET 字符集]:
MySQL 8.0 之前：默认字符集为
latin1，utf8字符集指向的是utf8mb3。MySQL 8.0 及以后：默认字符集改为
utf8mb4，以避免乱码问题
utf8_general_ci：不区分大小写
utf8_bin：区分大小写，按照字符的二进制值进行排序
utf8_unicode_ci：基于 Unicode 的排序规则，不区分大小写，支持更广泛的语言267[COLLATE 排序规则]:
**
utf8_general_ci**基于 UTF-8 的排序规则，不区分大小写**
utf8_bin**基于二进制的排序规则，区分大小写**
utf8_unicode_ci**基于 Unicode 的排序规则，不区分大小写，支持更广泛的语言**
utf8mb4_general_ci**默认的排序规则 for utf8mb4 字符集，不区分大小写**
utf8mb4_unicode_ci**基于 Unicode Collation Algorithm (UCA) 的排序规则，不区分大小写**
utf8mb4_bin**基于二进制的排序规则，区分大小写
删除数据库
drop database [IF EXISTS] 数据库名称;使用数据库
use 数据库名称;表查询操作
查询当前数据库中的所有表
show tables;查询表结构
desc 表名;查询指定的建表语句
show create table 表名;创建表
创建表语句
create table 表名(
字段1 字段1类型 comment'对字段1的注释' 约束1... 约束n,#comment'对表的注释';
字段2 字段2类型 comment'对字段2的注释' 约束1... 约束n,
........ ,
字段n 字段n类型 comment'对字段n的注释' 约束1... 约束n,
primary key(字段i)
foreign key(字段i) references 主表(主表字段名)
) engine=InnoDB#引擎选择
#注释：设置字段i为主键和外键时可以同时设置多个字段，也可以不设置约束注释:约束类型： 1.非空约束 not null 2.唯一约束 unique 3.主键约束(非空且唯一) primary key 4.默认约束(不设置值给默认值) default 5.检查约束(满足某一条件) check 6.外键约束(保证数据的完整一致性) foreign key
修改表
添加字段
alter table 表名 add 字段名 字段类型 comment'注释' 约束;修改数据类型
alter table 表名 modify 字段名 新字段类型;修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 字段类型 comment'注释' 约束;删除字段
alter table 表名 drop 字段名;修改表名
alter table 表名 rename to 新表名;修改现有数据库的排序规则
alter database mydatabase collate utf8_general_ci;修改表排序规则
alter table mytable convert to character set utf8 collate utf8_general_ci;删除表
删除表
drop table [IF EXISTS] 表名;删除指定表并重新创建该表
truncate table 表名;DML
描述:data manipulation language 数据操纵语言,主要负责对数据的增加、删除、修改
插入语句
insert into 表名（表字段名1,表字段名2,...） values (数据1.1,数据1.2,...),(数据2.1,数据2.2,...);更新语句
update table set 字段名1=值1,字段名2=值2....[where 条件]删除语句
delete from 表名 [where 条件]DQL
描述:data quary language 数据查询语言，主要负责对数据的查询（最被频繁使用的一个）
查询语句
select (表字段名1,表字段名2,....)(*代表查询全部)
from 表名
where 条件
group by 分组字段列表
having 条件2
order by 表字段名i [asc(升序,也是默认排序)|desc(降序)]
limit 数字DCL
描述:data control language 数据控制语音，主要负责用户权限控制
查询用户
use mysql;
select * from user;创建用户
create user '用户名'@'主机名' identified by '密码';修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';删除用户
drop user '用户名'@'主机名';注意：
主机名可以使用%通配 这类SQL开发人员操作的比较少，主要是DBA(Database Administrator 数据库管理员) 使用。
函数
字符串函数:
将多个字符串拼接为一个字符串
concat(str1,str2,...,strn)将全部字符串转换为小写
lower(str)将全部字符串转换为大写
upper(str)左填充,用字符串pad对str的左边进行填充，达到n个字符的长度
lpad(str,n,pad)右填充,用字符串pad对str的右边进行填充，达到n个字符的长度
rpad(str,n,pad)去掉字符串头部和尾部的空格
trim(str)返回字符串str从start位置到len个长度单位的字符
substring(str,start,len)数值函数
向上取整
ceil(x)向下取整
floor(x)返回x/y的模
mod(x/y)返回0~1内的随机数
rand()求参数x四舍五入的值，保留y位小数
round(x,y)日期函数
返回当前日期
curdate()返回当前时间
curtime()返回当前的日期和时间
now()获取指定date的年份
year(date)获取指定date的月份
month(date)获取指定date的日期
day(date)返回一个日期/时间值加上一个时间间隔expr后的时间值
date_add(date,interval expr type)返回起始时间date1和结束时间date2之间的天数
datediff(date1,date2)流程函数
如果value为true，则返回t否则返回f
if(value,t,f)如果value1不为空,返回value1,否则返回value2
ifnull(value1,value2)如果val1为true，返回res1，...否则返回default默认值
case with [val1] then [res1] ... else [default] end如果expr的值等于val1，返回res1，...否则返回default默认值
case [expr] when [val1] then [res1] ...else [default] end约束
约束概念：
约束是作用与表中字段上的规则,用于限制存储在表中的数据(保证数据库中数据的正确、有效性和完整性)
约束语法:
添加外键
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名)
);alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名)删除外键
alter table 表名 drop foreign key 外键名称删除/更新行为
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名) on update [cascade...] on delete [cascade] [restrict] [set null] [set default];行为:
no action当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与restrict一致)
restrict当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与no action一致)
cascade当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
set null当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键的值为null(外键允许null)
set default父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)
约束分类
1.not null 限制该字段的数据不能为null
2.unique 保证该字段的所有数据都是唯一、不重复的
3.primary key 主键一行数据的唯一标识，要求非空且唯一
4.default 保存数据时，如果未指定该字段的值，则采用默认值
5.check 保证字段值满足某个条件
6.foreign key 用来让两张表的数据之间建立连接，保证数据的一致性和完整性
多表查询
多表关系
一对多:在多的一方设置外键，关联一方的主键
多对多:建立中间表，中间表包含两个外键，关联两张表的主键
一对一：用于表结构的拆分，在其中任何一方设置外键(unique)，关联另一方的主键
多表查询
显示内连接
select ... from 表A inner join 表B on 条件显示外连接
select ... from 表A,表B where 条件左外连接
select ... from 表A left join 表B on 条件相当于表A的所有数据包含二者交集
右外连接
select ... from 表A right join 表B on 条件相当于表B的所有数据包含二者交集
自连接
select ... from 表A 别名1,表A 别名2 where 条件联合查询
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B...;注意：
联合查询就是把多次查询结果直接合并(上下拼接)，形成一个新的查询结果集
联合查询的多张表的列数必须保持一致，字段类型也必须保持一致
子查询
标量子查询 ：子查询返回的结果是单个值 常用操作符：= <> > >= < <=
列子查询：子查询返回的结果是一列(可以是多行) 常用操作符：in not in some all
行子查询：子查询返回的结果是一行(可以是多列) 常用操作符：= <> in not in
表子查询：子查询返回的结果是多行多列 常用操作符：in
事务
事务描述：
一组同时成功或失败的操作
注意:默认的MySQL的事务是自动提交的，也就是说执行一条DML语句，MySQL会立刻隐形的提交事务
事务操作:
查看事务提交方式
select @@autocommit;设置事务提交方式
set @@autocommit=0;#0默认不提交事务,需要手动提交开启事务
start transaction | begin提交事务
commit;回滚事务
rollback;查看事务隔离级别
select @@TRANSACTION_ISOLATION;设置事务隔离级别
set [session|global] transaction isolation level [read uncommited | read commited | repeatable read | serializable]事务四大特性:
原子性(Automicity) : 事务是不可分割的最小操作单元，要么全部操作成功,要么全部失效
一致性(Consistency) : 事务完成时，必须使所有的数据都保持一致状态(分布式事务就是为了解决这个问题)
隔离性(Isolation) : 数据库提供的隔离机制，保证事务在不受外部并发操作的影响的环境下独立运行
持久性(Durability) : 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
注意:事务的四大特性也是事务的基本特性，任何事务都需要满足
事务并发问题:
脏读 : 一个事务读到另一个事务还没有提交的事务(读已提交-Read commited-时解决)
不可重复读 : 一个事务先后读取同一条记录，单两次读取的数据不同 (可重复读-Repeatable Read-时解决)
幻读 : 一个事务按条件查询数据的时候，没有对应的数据行，但是在插入数据的时候，又发现这行数据存在了，如同“幻影”(可序列化-Serializable-时解决)
事务隔离级别
1.读未提交 --Read Uncommited
2.读已提交 --Read Commited --解决了脏读
3.可重复读(默认级别) --Repeatable Read --解决了脏读、不可重复读
4.可序列化 --Serilizable --解决了脏读、不可重复读、幻读
存储引擎
Mysql体系结构
连接层
最上层是一些客户端的链接服务，主要完成一些类似于处理链接、授权认证以及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。
引擎层
存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API的存储和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需求，来选取合适的存储引擎。
存储层
主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
各个存储引擎的特点
InnoDB
介绍
InnoDB是一种兼顾高可用和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是MySQL的默认存储引擎
特点
DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能；
支持外键foreign key约束，保证数据的完整性和准确性；
文件
xxx.ibd: xxx代表的是表名，InnoDB引擎的每张表都会对应这样的一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。
参数：innodb_file_per_table
frm：MySQL数据库中用于存储表结构的二进制文件，每个表都会生成一个frm文件，该文件包含了表的结构定义，如列名、列类型、索引等信息，frm文件是MySQL数据库文件格式的一种，与MYD（存储数据）和MYI（存储索引）文件一起，构成了MySQL数据库的存储体系
MyISAM
介绍
MyISAM是MySQL早期的默认存储引擎
特点
不支持事务；
不支持外键；
支持表锁，不支持行锁；
访问速度快；
文件
xxx.sdi：存储表结构信息
xxx.MYD：存储数据
xxx.MYI：存储索引
Memory
介绍
Memory引擎的表数据存储在内存中，由于受到硬件问题、断电问题的影响，只能即将这些表作为临时表或缓存使用。
特点
内存存放；
hash索引(默认);
文件
xxx.sdi：存储表结构信息
索引
索引描述
索引是帮助MySQL高效获取数据的数据结构
索引优缺点
优点：提高数据的检索效率，降低数据库IO的成本；通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗
缺点：索引也占用空间；索引降低了更新表的速度，如果对表进行insert、update、delete的时候效率会降低
索引语法
创建索引
create [ unique | fulltext | spatial ] index 'index_name' on 'table_name'('index_col_name',...);注释:
fulltext的底层是倒排索引，在MySQL的FULLTEXT索引中，当对文本数据进行索引时，会进行分词处理，将文本分解成一系列的单词或短语，并为每个单词或短语建立索引，此时可以进行全文检索。
查看索引
show index from 'table_name';删除索引
drop index 'index_name' on 'table_name';索引结构
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| full-text(全文索引) | 是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucence，Solr，ES |
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 5.7版本之后支持 | 不支持 | 支持 |
| R-tree(空间索引) | 不支持 | 支持 | 不支持 |
| full-text(全文索引) | 5.6版本之后支持 | 支持 | 不支持 |
B+Tree索引特点：高效的数据检索能力、优化的磁盘I/O操作、支持多种索引类型、叶子节点形成有序链表等...
Hash索引特点：只能用于对等比较(=，in)、无法利用索引完成排序操作、查询效率通常高于B+Tree索引
为什么InnoDB存储引擎选择B+树？
1.相较于二叉树，层级更少，搜索效率高；
2.对于B树，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要保存同样的数据，B树的高度比B+树更高，导致IO次数增加，性能降低；
3.相对于Hash索引，B+树支持范围匹配及排序操作；
示例图：
索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | primary |
| 唯一索引 | 避免同一个表中某个数据列中的值重复 | 可以有多个 | unique |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | fulltext |
在InnoDB中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引（Clustered Index） | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引（Secondary Index） | 将数据与索引索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则：
如果存在主键，主键就是聚集索引
如果不存在主键索引，将使用第一个唯一索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个
rowid作为隐藏的聚集索引
注意：一页中可以存储16行大小为1KB的行数据，而一行数据可以存储大约1171个指针，所以对于高度为n的B+树可以存储
1171* . . .*1171(共计n个1171)*16行数据