mysql

mysql数据库的存储引擎：

5.5之间：MYISAM 不支持事物和外键的存储引擎。适用于读的多。写的好

5.6之后默认的存储引擎：lnnodb,可以支持事务，外键，行级锁定的存储引擎

支持高并发性能的应用

事务：在数据当中，一个或者多个操作组成的数据操作的序列

这些操作要么全部成功，失败一个就全部不执行。确保数据的一致性和完整性

事务的特点：原子性。是数据库的最小工作单位，要么全部执行成功，要么全部不执行，只要有一个操作失败，整个执行的序列都会被回滚。但怕是完成的操作也会被撤销

一致性：事务执行前后，数据库的完整性约束不能被破坏。只有满足所有的约束条件情况下，事务才能被提交

隔离性：事务的执行是相互隔离。一个事务的执行不能受到其他执行事务的干扰。并发事务之间互相隔离。防止数据不一致的情况发生 。

持久性：事务一旦提交，他所做的所有修改会被永久的保存在数据库当中。即使系统崩溃，提交的数据也不会丢失。

确保数据库始终处于一致性的状态。确保数据库的完整和一致。

mysql的名词：
1、数据库：database

2、表：table 行：row 列：column

3、索引：index

4、视图：view

5、存储过程：procedure

6、触发器：tirgger

7、用户：user

8、权限privilege

mysql语句的规范

在数据库系统中，sql不区分大小写，但是建议大写。语句不区分，但是表名是严格区分大小写

sql语句可以单行也可以多行书写，但是默认都以；结尾

关键词不能跨写或者简写

子语句通常位于度路航，便于编辑，提高可读性

数据库的命名规则：

必须已字母开头，后面可以包含数据，特殊字符：# _ $

不要使用musql的保留字：table select

数据库名 表名 用户名严格区分大小写

数据库的字符类型：

int:4个字节，用来存储整数

char:固定长度的字符串，用来存储定长的字符串

varchat:可变长度的字符类型，存储可变长度的字符串。（无限制的可以随便写，也要根据类型的长度）

flocat(m,d):单精度浮点 存储浮点数 m表示总位数， d表示小数位

double（m,d）:双精度浮点数，存储浮点数，M也是总位置，d表示小数位

text:用于存储大文本数据，文档，或者长字符串。

image:二进制存储图像，图片。多媒体

decimal(5.2):用于存储固定精度的小数，其中5，表示总位数，2表示小数位

date:存储日期：yyyy-mm-dd

datetime:存储日志 yyyy-mm-dd hh:mm:ss

timestamp:和datetime格式一致，可以自动的更新为当前的时间戳

重点：

char varchar

''char 4个字节 ''1个字节

‘ab’ ‘ab’ 4个字节 'ab’3个字节

‘abcd’ ‘abcd’ 4个字节 ‘abcd’ 5个字节

char无论你是否定义了值。都会占用固定长度的字节大小

varchar:在保存时，varchar 14 实际长度是5 就占用5个 加一个隐藏符占用6个

varchar比char节省磁盘空间

但是varchaer读写速度比char慢

都是字符串类型：一定要用单引导

sql语句的分类

DDL：数据定义语言，用于创建数据的对象，创建库 表 索引 。

CREATE 创建

DROP 删除

ALTER 修改

mysql基本操作

mysql> show global variables like 'port';
查看使用的端口 
-----------------------
mysql> show databases;
查看当前数据库有几个库 
-----------------------
mysql> use mysql;
进入mysql的库 
-----------------------
mysql> show tables;
查看当前数据库中有多少表 
-----------------------
mysql> describe user;
查看表的结构  可以在后面加上大写的G 纵向查看 
mysql> describe user \G;
------------------------
mysql> create database czy;
创建一个czy库名的库 
----------------------------
mysql> create table czy (id int(4) not null,name varchar(10) not null,sroce decimal(5,2),passwd char(48),primary key(id));
----------------------------
mysql> drop table czy;    
删除表 指定表名
----------------------------
mysql> insert into czy (id,name,sroce,passwd) values(1,'test',90,'123');
添加数据 
mysql> select * from czy;
+----+------+-------+--------+
| id | name | sroce | passwd |
+----+------+-------+--------+
|  1 | test | 90.00 | 123    |
+----+------+-------+--------+
1 row in set (0.00 sec)

------------------------------
mysql> update czy set passwd=password('000') where id = 3;
对密码进行加密
------------------------------
mysql> alter table czy add birth date;
添加新的一列 
插入数据 生日日期 
mysql> update czy set birth=date('2020-1-1') where id = 4 ;
mysql> select * from czy;
+----+------+-------+-------------------------------------------+------------+
| id | name | sroce | passwd                                    | birth      |
+----+------+-------+-------------------------------------------+------------+
|  1 | test | 90.00 | 123                                       | NULL       |
|  2 | aest | 95.00 | *B66FEB4CF5216D971C066EB920EC03B27A5A3F40 | NULL       |
|  3 | west | 59.00 | 123                                       | NULL       |
|  4 | rest | 59.00 | 444                                       | 2020-01-01 |
+----+------+-------+-------------------------------------------+------------+
4 rows in set (0.00 sec)

null和空格的区别：
null是什么都没有，对象没有任何的描述信息

空格：也是字符

DML：数据操作语言，对表中的数据进行管理。

select

update 更新

insert 添加

delete 删除 对表进行删除操作

DQL：查询数据记录

select

DCL:数据控制语言，设置或者更改数据库用户或者用户的权限

GRANT 赋予权限

REVOKE 取消权限

TCL：事务控制语句，管理数据库当中的事务。

commit:确认提交事务。

ROLLBACK：事务提交之后无法回滚

savepoint保存点，可以回滚

---

create table test2 like test;
复制，通过like这个语法，可以直接复制test的表结构。只是复制表结构，不能复制表里面的数据 
insert into test2 select * FROM test;
CREATE table test1 (select * from test);
创建一张表，test1,数据从ky32来，表结构也是ky32;

mysql的约束方式

6种常用的约束：

1.主键约束，用于唯一标识表中的每一行，主键列的值必须是唯一而且不能为空，一个表只能有一个主键

2.外键约束，用于建立表与表之间的关系，一般是和另一张表的主键关联。确保了数据的引用完整性 。一个表可以有多个外键

3.非空约束，not null 必须要有一个值。

4.唯一性约束：unique,确保列中的所有值都是唯一的，类似于主键，但是可以为空，而且一个表可以有多个唯一约束

5.默认值约束：default,在插入表数据时，如果没有定义值，会提供一个默认值。

6.自增约束：每行自动生成一个唯一标识，通常和主键在一起使用。

DDL：CTEATE DROP ALTER

dml:对数据进行管理

update insert into delete truncate

dql:查询语句

dcl:权限控制语句 grant revoke

数据库用户管理：

创建用户

修改用户的权限

删除用户

grant 要在终端执行。

---

mysql用户管理

CREATE user ‘czy’@‘localhost’ identified by ‘123456’;
CREATE user 这是创建用户的开头
‘czy’@‘localhost’
‘czy’@‘192.168.211.10’
‘czy’@‘192.168.211.0/24’
‘czy’@‘%’ 任意，所有
czy 表示的是用户名
localhost:新建的用户可以在哪些主机上登录，既可以使用ip地址，网段，主机名都可以

如何以加密的形式创建用户
CREATE user ‘czy’@‘localhost’ IDENTIFIED by ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’;
SELECT PASSWORD (‘123456’) 这个命令会帮助你生成加密

实例
grant all privileges on . to ‘czy’@‘localhost’ identified by ‘123456’;
grant 赋权语句
all privileges 所有，所有权限
on . 所有库，可以对所有的库进行操作
on test.* 对指定的库进行操作。
to ‘czy’@‘localhost’ 赋权给这个用户
identified by ‘123456’;所使用的登录密码 ，创建用户的时候可以不写，默认密码为空。

mysql> grant all privileges on . to ‘czy’@‘localhost’ identified by ‘123456’;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

show GRANTS for ‘czy’@‘localhost’; 查询指定用户权限

创建一个用户 test1
只允许该用户从192.168.211.10这个终端登录
第三个，只能对country这个库有权限，其他库一律不行
CREATE user ‘test1’@‘192.168.211.10’ identified by ‘123456’;
grant all PRIVILEGES on country.* to ‘test1’@‘192.168.211.10’ IDENTIFIED by ‘123456’;
flush privileges 刷新权限
mysql -h 192.168.211.10 -u test1 -p 终端登录test1

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| country |
±-------------------+
2 rows in set (0.00 sec)

结束

删除用户的权限：revoke
REVOKE all PRIVILEGES on country.* from ‘test1’@‘192.168.211.10’;
flush privileges;
这个地方要注意，要删除用户的权限的话，要精确定位，之前给的是什么权限，现在就给他删除那个权限，否则无效

---

如何对权限进行控制：
grant SELECT on country.* to ‘test1’@‘192.168.211.10’ IDENTIFIED by ‘123456’;
flush privileges
show GRANTS for ‘test1’@‘192.168.211.10’;

REVOKE SELECT on country.* FROM ‘test1’@‘192.168.211.10’;

---

对用户进行多个权限的赋权；
GRANT SELECT,INSERT,DROP on country.* to ‘test1’@‘192.168.211.10’ IDENTIFIED by ‘123456’;
flush privileges

移除用户权限
REVOKE drop on country.* from ‘test1’@‘192.168.211.10’;

---

移除用户的部分权限
REVOKE drop on country.* from ‘test1’@‘192.168.211.10’;
如要移除所有权限 可以使用 revoke all on country.* from ‘test1’@‘192.168.211.10’;

用户重命名：
rename user ‘test1’@‘192.168.211.10’ to ‘test’@‘192.168.211.10’;

删除用户：
drop user ‘test’@‘192.168.211.10’;

修改用户的密码
set PASSWORD for ‘czy’@‘192.168.211.10’=PASSWORD(‘abc123’);
修改当前用户密码
set password = PASSWORD(‘abc123’)

---

mysql 索引

索引：索引是一个排序的表，列表当中存储的是索引的值和包含这个值的数据所在行的物理地址。

索引的作用就是加快查询速度

索引的作用

1. 利用索引，数据库可以快速定位，大大加快查询速度
2. 当表的数据很多时，查询需要关联多个表，这个时候使用索引也可以提高查询速度
3. 加快表与表之间的连接速度
4. 使用分组和排序时，可以大大减少时间
5. 可以提高数据库恢复数据时的速度

索引创建的原则

1. 有索引的话，数据库会先进行索引查询，然后再定位数据，索引如果使用不当，反而会增加数据库的负担
2. 主键，外键必须有索引（创建好的主键和外键自动就是索引，不需要额外的声明）
3. 一个表超过了300行的记录，那么必须要有索引，否则数据库查询的时候会遍历表的所有数据，影响查询速度
4. 互相之间有关联的表，在这个关联字段上应该设置索引
5. 唯一性太差的字段，不适合创建索引
6. 更新太频繁的字段，不适合做索引
7. 经常被where条件匹配的字段，尤其是表的数据比较多的，应该创建索引
8. 经常进行group by（分组） order by (排序) 的字段上要建立索引。
9. 索引的列的字段越小越好，长文本的字段，不适合建立索引。

索引的类型 ：

B-树索引 BTREE

如何查看表的索引

show index from 表名;   创建了主键就是创建了索引 
树形结构的一个索引 ，也是大部分数据库的默认索引类型。
根节点：树的最顶端的分支节点 
分支节点：指向索引里其他的分支节点，也可以是叶子节点
叶子节点：直接指向表里的数据行

哈希索引:散列索引 把任意长度的输入，通过散列算法换成固定长度的输出。散列值–分别对应数据里的列和行

mysql的默认引擎：INNODB 默认的索引类型就是btree

MEMORY引擎可以支持HASH，也是他的默认索引

先算散列值，然后在对应，速度比较慢，比btree慢

hash的索引匹配：= in() <=>

创建表的时候需要考虑的因素：

1、关联程度 3张表 选好关联字段

2、每个字段的长度，也要考虑

3、设计合理的索引列

4、表数据，要控制在合理的范围之内，可以在牺牲一定性能的条件下，满足需求。5秒以上就要考虑优化了。10秒以上一般是出问题了（缓存失效，缓存击穿，缓存血崩）

创建索引：

普通索引：

索引：普通索引 主键索引 唯一索引 全文索引 联合索引

创建索引时，注意索引失效的情况

explain 加在查询语句前面，可以查看索引的使用情况

---

mysql事务

事务是一种机制，一个操作序列。包含了一组数据库的操作命令，所有命令都是一个整体，向系统提交或者撤销的操作，要么都执行，要么都不执行

不可分割的单位

事务的特点ACID：

A-原子性 最小单位 事务里的所有条件都是一个整体，不可分割。要么都成功，要么都失败

C-一致性 事务开始之前和事务结束之后，数据库的完整性约束没有被破坏

事务完成时，数据必须属于一致状态

事务开始前，数据库中的存储数据要处于一致状态。

进行中的事务，数据可能处于不一致的状态

当事务最终完成时，必须再次回到已知的一致状态。

I-隔离性 只在并发环境中，不同事务同时操纵相同的数据时，每个事务都有各自的完整数据空间

对数据进行修改的所有并发事务是彼此隔离的，表面事务必须是独立的

修改数据的事务可以在另一个使用相同数据的事务开始之间访问这些数据。或者在另一个使用相同的事务结束之后访问这些数据

mysql支持四种隔离级别：

1、未提交读，允许脏读，允许一个事务可以看到其他事务未提交的修改

2、提交读 事务只能查看已经提交的修改，未提交的修改是不可见的 。防止脏读orcale sql server

3、可重复读 也是mysql的默认隔离级别。确保如果在一个事务中，执行两次相同的select 语句时，都能得到相同的结果，不管其他事务是否提交修改，可以防止脏读

4、串行读 锁表 完全串行化 每一个事务都隔离，读写都堵塞

---

不可重复读：一个事务内，多次读同一数据。
前一个事务还没有结束，另一个事务也访问该数据。
在一个事务之内，两次查询到的结果不一致。读不到相同的数据内容

set global transaction ISOLATION LEVEL READ committed;
set session transaction isolation level read committed;

FLUSH PRIVILEGES;

begin;
update test set money=money-200 where id=1;

select * from test;
COMMIT

---

持久性：数据提交。事务的效果将会被永久的保留在数据库中。而且不会被回滚。

主从复制

高可用

备份

权限控制

总结：在事务管理中，原子性是基础，隔离性是手段，一致性是目的 持久性是最终的结果

事务的控制语句：

BEGIN：开始

start transaction

这两个都是显示的开启事务

commit commit work 这两个都可以作为提交事务

rollback

rollback work

这两个都是回滚 能撤销正在进行的所有未提交的修改

savepoint （自定义名称） 创建回滚点 一个事务可以有多个回滚点

rollback to (回滚点名称) 回滚到还原点

1、多点还原 s1和s2

如果还原到s1,s2将消失

如果提交事务，所有还原点全部消失。

---

存储引擎：
在mysql当中数据用各种不同的技术存储在文件中，每一种技术都使用的是不同的存储机制，索引技巧，锁定水平
以及最终提供的不同的功能和能力，这些就是我们说的存储引擎。
功能：
1、mysql将数据存储在文件系统中的一种方式和格式
2、存储引擎负责执行实际的数据I/O操作
3、存储引擎介于数据和文件系统之间，数据会先保存到存储引擎，再按照存储引擎的格式保存到文件系统。

mysql的存储引擎的分类：
1、INNODB:5.5之后mysql的默认存储引擎。又叫事务性速记引擎。支持ACID事务。支持行锁，也可以锁表。写入性能和查询性能比较好。
2、myisam:5.5之前的默认存储引擎。有较高的插入数据的性能较高，查询速度也很优秀。但是不支持事务。
3、memory:所有数据都是保存在内存的存储引擎中。插入数据，更新数据，查询数据都很快。但是占用内存空间比较大，会占用和数据量成正比的内存空间。mysql一旦重启，内容就会丢失
4、csv：由逗号分割数据的存储引擎。他会在数据库子目录里为每一个数据表创建一个叫做.csv的文件。就是一种普通的文本文件，每个数据行占用文本行，csv不支持索引
5、Archive:非常适合存储大量的独立的，历史数据的引擎。不需要被经常读取。插入的速度很快。查询的效率就比较低。
6、blackhole：黑洞引擎，写入的任何数据都会消失。

MYISAM和INNODB分析与对比：
MYISAM:不支持事务，也不支持外键。只支持全文索引，数据文件和索引文件是分开的。访问速度快。
适用场景：适合以查询和插入数据为主的应用。
在磁盘上有三个文件：
文件名和表明相同。但是扩展名不同：
.frm （存储的表结构）
.MYD (数据文件)
.MYI (索引文件)

MYISAM的特点：
1、表级锁定，更新数据时，整个表都将锁定
2、数据库在读写过程中相互阻塞。
支持的存储格式：
1、静态表，固定长度表，静态表是myisam的默认存储格式。静态表中字段都是非可变字段，每个记录都是固定长度。
优点：存储快，方便缓存，有了故障也容易恢复。 缺点：占用的空间较多

2、动态表，动态表可以包含可变字段，记录的长度是不固定的。
优点：占用空间比较少 缺点:频繁更新数据，删除记录，会产生碎片。需要定期清理。myisamchk-r 清楚命令 。因为是碎片化存储，出现故障恢复也比较困难。

3、压缩表 ：myisamchk由工具创建的，占据的空间非常小，每条记录都是单独压缩

INNOB：
1、支持事务，支持四个事务的隔离级别 5.5之后是mysql默认的存储引擎。
读写阻塞和隔离级别相关。
INNODB支持高效的缓存索引以及缓存数据。
表与主键以簇方式存储BTREE
支持外键约束，5.5之后INNODB也可以支持全文索引
对硬件资源的要求比较高。
支持行锁定
也可以支持表锁定（全表扫描）

1、适用like模糊查询，会进行全表扫描，锁定整个表
2、如果对没有创建索引的字段进行查询，也会使用全表扫描。锁定整个表
3、使用索引，进行查询，则是行级锁定。

inoodb的特点
1、不保存表的行数。如果要统计表的行数，会扫描一遍整个表来计算有多少行
2、自增长字段，必须包含只有该字段的索引
3、delete清空表，一行一行删，速度比较快 TRUNCATE

使用场景：
1、业务需要事务的支持
2、论坛，微博，对数据一致性比较高的场景
3、访问量和并发量比较高的场景，innodb支持缓存，减少后台服务器的压力

三个文件：
表名：frm(表结构文件)
表名.idb (即是数据文件，又是索引文件)
dp.opt:表的属性文件。
innodb行锁和索引的关系 以及表锁 排他锁 死锁。

1、发生死锁的时候，数据库会自动选择一个事务作为受害者，然后先解除死锁，再回滚事务。
2、mysql默认的死锁机制，会选择其中一个事务作为死锁的牺牲品直接终止其中一个事务，但是不会自动回滚。

如何尽可能的避免死锁：
1、业务的逻辑要合理，以固定顺序访问表和行
2、如果事务的类型比较负责，要进行拆分，在业务允许的情况下，把大事务拆小
3、在同一事务中，尽可能的一次性锁定所有需要的资源。可以减少死锁的概率
4、隔离级别，如果要避免死锁，可以使用read commit 可以避免死锁
5、添加合理的索引，可以减少死锁的概率

乐观锁：不会有任何提示，只是数据不能写入。数据更新时，进行校验，发生冲突，数据不生效而已，没有其他的报错或者卡停