mysql知识点概览_mysql概览

InnoDB具有行级锁。具有外键。支持事务。支持mvcc。

mvcc有点类似于CAS，是一个多版本高并发控制，是一个乐观锁。实现的时候结合了排他锁。由于不用悲观地上锁让他完成了高并发。

我们先理解mvcc，指的是多版本高并发控制，对于InnoDB来说，每一行有两个隐藏的数据，分别是系统版本即当前事务ID和删除标识，事务ID指的是你的语句所在的事务的ID，比如你的语句在第七个事务那就是7，删除标识指的是你要删除行的所在事务的事务ID，update会将所在事务ID作为原行的删除标识，原行不会删除，更新后的行作为insert的行，该行的系统版本为当前事务ID，我们在select的时候，select所在的事务ID要小于行的删除标识，要大于行的系统版本，也就是说删除标识和系统版本都是事务ID，我要查询得到那我查询所在事务ID肯定要大于你的系统版本也就是创建时间呀，也肯定要小于你的删除标识呀。对于RR来说，每个select事务都只会使用select的第一次快照，也就是说不管你的select是第几个事务，都只用那一个快照，也就是说你的select的事务ID很老即很小。其他事务做出的增删改等都不会影响到我的select，因为只要增删改的行的系统版本和删除标识都大于select所在的事务ID，都不会影响到select。而如果是RC，则每个select都新建一个快照。

mvcc无法实现未提交读和序列化读，因为是根据事务ID和系统版本、删除标识比较来的，因此肯定有未提交读不满足的情况，比如说你先开启查询事务，再开启修改事务，查询事务ID是一定小于行的系统版本和删除标志的，因此即使未提交，我也只能查询到修改前的数据。由于mvcc是模仿行级锁，但是并未上锁，也就是说不同的事务的读写操作是非阻塞的，也就说我开启事务增删查改的时候其他事务也可以开启增删查改，但是具体的实现上肯定有个先后顺序，只不过由于没有上锁，因此可以并发事务。假设现在先开启了A事务，然后开启B事务，那么A的事务ID为1，B的为2，但是B先执行查询操作，查到的记录为0行数据，然后在A里执行插入操作，该插入行的系统版本为1，删除标识为undifined，然后A里再执行查询操作，由于A的事务ID2大于行的系统版本1且小于行的删除标识或未定义，则查询成功，注意要等到A提交，不然系统版本和删除标识是不会改变的，这也解释了为什么不支持未提交读，由上可知也不支持序列化读。

快照的意义在于是否每次依照select所在的事务ID还是依照第一次select所在的事务ID来和经由其他事务修改后的行的删除标识、系统版本进行比较。

gbk类似于ASCII的编码方式，windows默认gbk，Linux默认utf-8，在insert等操作时，客户端会按照系统方式进行编码，然后MySQL再根据MySQL的字符集(默认为utf8)将字符转换，而select等操作则是服务端到客户端的字符转换。

这个MySQL客户端和服务端之间呢是字符流，因此客户端不用管是什么类型编码的，因为字符串到了我这边后是用我这边的来解码，因此我就可以存储该字符或操作。

而如果是文件的话就是I/O流可能是字节，字节就是我这边按照我的字符集解码为字节给你，你再按照你的字符集编码，肯定会出错(如果不一致的话)。

事务的隔离性，即事务之间互相不影响。

mvcc，虽然没有上锁，但是事务满足一定隔离级别在某种程度上也是一个锁，因此隔离级别必须为已提交读或可重复读。这样能保证数据的一个真实性。然后再进行版本的比较，也就是说并发的事务随便你begin，最后是否commit还得我版本是否相等来决定。

InnoDB默认是可重复读，是用next-key Lock锁实现的，但是和其他数据库不同，在MySQL里它同样实现了幻读的级别，因此完全足够。虽然大部分的数据库隔离级别为已提交读，但是和MySQL的可重复读相比无任何性能损失。

数据库引擎InnoDB就四种锁，行级锁record lock(非常消耗资源，但是锁的粒度最小，会发生死锁)，间隙锁gap lock(不包括记录行)、record+gap包括行记录的范围，表级锁(消耗资源少，但是锁的粒度最大，不会发生死锁)。而事务的隔离级别我们是直接可以设置的(基于session设置)。现在的MySQL的隔离级别是基于mvcc实现的，例如已提交读，每次提交都会创建一个新的readview，当你的事务id号在readview里去找，因此每次都是新的。而可重复读一直都用你第一次读的时候创建的readview，所以你后面的所有事务的id号去readview里找的时候都是原来那一个，结果就是找不到咯。

可重复读和已提交读都可以依靠mvcc实现，注意这里不需要结合上述提到的锁来实现，假设现在有两个session为a和b，a里面先查后改某行记录信息，a可以正确执行，b里面的select在a提交修改事务之前执行的话那么b后续所有的select事务都是该结果。而如果在a提交修改事务之后执行select，那么b后续的所有select都是修改后的结果，也就是说可重复读是在session里第一次执行select事务的时候就确定好了readview，即使其他session的修改事务(注意这里会回滚，就是说只要我b开始select了，a做的事情都是不会提交的，就相当于比如b查询了某范围的记录，那么就会利用gap+record加锁，这就是典型的乐观锁，即b里面的事务开启并不会给资源上锁，而是在执行select的时候才上相应的锁)，我还是只能查询到我第一次查询的东西，这期间如果我修改的话是起作用的。只要间隙够大，就可以达到可串行化读防止幻读。

而已提交读则无锁，是指我查询的时候不上锁，谁改了我查到的都会变。

注意上面的思想，修改和删除不上锁，一到查询的时候就上锁。严格上来说不叫上锁，因为只有查询的时候才会产生readview即快照，这个快照的作用相当于锁。

show processlist展示子线程例如select、insert等。

insert into user(class) values ('22班'),('33班')同时插入两条数据。

数据库里没有{}和[]，表示内容统统用()。

有一种更简单的添加约束alter table yue add index suoyin(yue_age)适用于其他任何约束，这里的suoyin(yue_age)类似于添加数据时的表名(列名)。

在修改表来添加一些约束的时候统统用add constraint 约束名的方式，后面跟约束(字段)，注意括号里一定要跟上列的字段。

修改表添加约束要加列名字段，放到()里，例如alter table user add constraint primary key(id)、...add unique(id)、alter table user add constraint ee unique(class)、alter table user add constraint fr check(class<>'ds')，而加不加constraint 字段名(不能用''，直接写例如dwa)都可以。

而在创建表时则不需要加列字段，直接写约束关键字就可以了。check要写字段。

上述两种方式添加约束(例如主键、外键、unique、check)时加与不加constraint关键字都可以。

而在撤销约束时，其实也就是修改表的时候，不能使用alter table user drop constraint dd的方式，因为constraint只是一个笼统的概念，具体就不知道是删除该列上的哪一个约束了(注意这里我们讨论的是列上的不同约束，这是因为不可能有两列上的constraint字段重名，这和数据库的第一范式有关，第一范式规定数据库不允许有两个相同的列，也就是不允许两个列字段重名，这样就能保证每一行由且仅由一系列独特的字段确定。)。

所以删除约束直接drop index ee、drop check fr、 drop primary key、alter table yue drop foreign key ss。对于default和null这种没法用()跟上列字段的约束，我们在修改表这种方式下添加该类型约束的时候要修改列，例如alter table user alter column set default='sd' not null。删除则alter table user alter column uname drop default，和primary ley在一个表里只有一个一样，指定表后不用指定名字。这里指定列后，一列里只有一个默认值，可以不加default约束名字。

注意，在修改表添加约束的时候要在约束字段后面的()里加上列字段名，删除约束不需要列字段名，只需要constraint提供的约束字段名就ok了，因为约束字段和列字段一样，能确定一个列(通俗一点就是不允许有两个列的相同约束有相同名字)。而primary和default都是处于已确定好列或唯一性的条件下。因此只有在修改表+添加约束的条件下才需要列字段名。

create table(id int)comment="表的备注" data directory=“数据目录” index directory="索引目录"。

drop table if exists test3；if exists少用，很多情况下无效会报错。

create table test3 like yue；

if exists使用范围很窄。

create table test3 as select * from user

delete from test3 where righ is null删除空值的行用is

alter table test3 rename test4重命名

datetime、timestamp都是年月日时分秒的形式，time是时分秒、date是年月日。也就是说Java的Date类型可以被时间戳类型保存。

MySQL的不区分大小写是指不区分关键字和字段名的大小写，而字段的值是区分的。表名列名都叫做字段名例如alter table test3 rename test4;select current_Time from tesT4正确。

集合和枚举类型，和int等类型一样，是一个字段的值类型，表示该值只能为集合或枚举里面的元素的组成。

create table test7(name set('B','A','D'))

insert into test7 values('B,A')

select * from test7使用enum或set的好处是有效节省空间，因为虽然表现为字符串形式，但存储为字节。

alter table test7 engine=InnoDB charset=utf8修改引擎和字符集。

foreign key (外键字段) references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]

此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下，可以设置为null.前提是该外键列，没有not null。

可以不指定主表记录更改或更新时的动作，那么此时主表的操作被拒绝。

如果指定了 on update 或 on delete：在删除或更新时，有如下几个操作可以选择：

1. cascade，级联操作。主表数据被更新(主键值更新)，从表也被更新(外键值更新)。主表记录被删除，从表相关记录也被删

2. set null，设置为null。主表数据被更新(主键值更新)，从表的外键被设置为null。主表记录被删除，从表相关记录外键被设置成null。但注意，要求该外键列，没有not null属性约束。

3. restrict，拒绝父表删除和更新。

注意，外键只被InnoDB存储引擎所支持。其他引擎是不支持的。

select now() from user、select current_time from user。

truncate是删除表后再创建，delete是逐条删除，delete知道删除到了第几行，前者不知道。

select返回的其实是一个表。

视图有啥作用呢，因为视图是靠as实现的，而as后面的select语句往往复杂，那我们得到视图后是对视图查询而且客户端也看不到表的结构，就保证了业务逻辑性和安全性。

临时表temporary table。视图没有数据文件，只有数据结构。

触发器。

是基于表的某些操作，不可以给视图和临时表弄触发器。

create user gou@localhost identified by '123456'创建用户。

非超级用户(root)可以创建用户，但不能分配权限grant select on user to gou无效，没权利。

撤销权限revoke from。

可重复读。

我这个线程先利用record+gap获得了修改、查询、添加、删除的机会，我就开始操作了，比如说我insert、delete等的时候会争取并获得锁，然后如果之后其他事务开启了读，其他事务就会利用mvcc使我的后续修改无效，它读的也是那一个数据。可以结合我交卷老师读卷的例子。而幻读只不过把间隙锁也就是gap的范围扩大的很大。

如果建表时列名和关键字重名，用tab上面的那个点号括起来。

数据库里的时间。

time_stamp比date_time好，因为前者会随着时区的改变而自动改变时间，后者不会。

create table info_time(

`date_time` datetime default null,注意这里default的值不可以是current_Timestamp或current_time。类型不符。但可以是now()。

`time_stamp` timestamp default current_Timestamp

)engine=InnoDB default charset=utf8;

insert into info_time values(now(),now());插入时间后数据库里就一直是这个时间数据，只不过类型为时间类型，也就是说该事件不会随着你不断select而不断更新。

select * from info_time;一直查询到的都是插入时的时间，不管你后续有没有执行更新操作都是查询到这个时间。

set time_zone='-8:00';我测试的是查询到的时间比现在的时间要晚八个小时，且设置时区后无任何变化。

创建表千万不要用单引号或双引号圈住字段名或表名或约束名，要用tab键上的点号。