search

keyfall / xuexibiji

3 stars 0 forks source link

MySql #26

Open keyfall opened 5 years ago

keyfall commented 5 years ago

安装mysql

sudo apt-get install mysql-server 出现下列错误 图片 这是由于之前apt报错，删除掉就行 图片 上面是搜索出来，下面是删除

mysql常用

进入mysql默认 sudo mysql -u root -p

显示所有的数据库 show databases

进入数据库 use 数据库名字

创建数据库 create database 数据库名字;

显示数据库所有表 show tables;

创建pet表 图片

查看表详情 describe pet;

插入数据 图片

数据类型 图片

增删改查 删除数据 图片

修改数据 图片

约束

主键约束 能够唯一确定一张表中的一条记录，使字段不重复且不为空 图片

联合主键 如果这么写主键，那么就是联合主键，主要这两个中有一个不相同，那么就可以执行写入 图片

建表忘记添加主键了，后续alter add添加主键方法 alter table user4 add primary key(id);

删除主键 alter table user4 drop primary key;

使用modify修改字段，这里相当于直接替换掉之前的对应字段设置 alter table user4 modify id int primary key;

自增约束 图片

外键约束 这里涉及两个表

主表
create table classes(
  id int primary key,
  name varchar(20)
)
副表
create table students(
  id int primary key,
  name varchar(20),
  class_id int,
  foreign key(class_id) references class(id)
)

主表中的id没有的数据，副表中class_id不能添加 主表中的数据被副表使用，那么对应的数据不能删除

唯一约束 约束修饰的字段值不可以重复

create table user5(
   id int,
   name varchar(28) unique
);

删除唯一约束 alter table user5 drop index name;

非空约束 修饰的字段不能为空 create table user6( id int, name varchar(28) not null ); 默认约束 插入字段时如果没有传值，就会使用默认值 create table user7( id int, name varchar(28) default 'sss' );

keyfall commented 5 years ago

三范式

第一范式 数据表中的字段都是不可分割的 比如地址字段里面有省市详细地址，这里就要把省，市，详细地址分开，建成3个字段

第二范式 满足第一范式，除主键外每一列都必须完全依赖主键

第三范式 满足第二范式，除主键列的其他列之间不能有传递依赖关系

mysql其他关键字

分组 group by 和having group by 是用列排序，比如用学生班级排列，having是对group by选的列进行限制查找，比如学生班级需要大于2班 图片

keyfall commented 5 years ago

三范式

第一范式 数据表中的字段都是不可分割的 比如地址字段里面有省市详细地址，这里就要把省，市，详细地址分开，建成3个字段

第二范式 满足第一范式，除主键外每一列都必须完全依赖主键

第三范式 满足第二范式，除主键列的其他列之间不能有传递依赖关系

mysql其他关键字

分组 group by 和having group by 是用列排序，比如用学生班级排列，having是对group by选的列进行限制查找，比如学生班级需要大于2班 图片

keyfall commented 5 years ago

三范式

第一范式 数据表中的字段都是不可分割的 比如地址字段里面有省市详细地址，这里就要把省，市，详细地址分开，建成3个字段

第二范式 满足第一范式，除主键外每一列都必须完全依赖主键

第三范式 满足第二范式，除主键列的其他列之间不能有传递依赖关系

mysql其他关键字

分组 group by 和having group by 是用列排序，比如用学生班级排列，having是对group by选的列进行限制查找，比如学生班级需要大于2班 图片

模糊查询 like 查询3开头后面任意一个字符的 like ‘3%’ 图片

查询3开头后面任意字符的 like '3*'

范围查询 between 图片

通过year关键字查询年份 图片

union和not in关键字 not in不在后面括号范围里 连接两条sql语句结果 图片

any关键字 后面内容中至少有一个满足就可 图片

all关键字 满足后面所有的内容 图片

复制表数据查询 实际就是对数据库用2个别名，进行两表查询 图片

now函数和year函数相减得岁数 图片

内连接和外连接 内连接，inner join或join 两个表交集 图片

外连接

左连接，left jonin或left outer join 两个表中左边的表数据都取出来，右边的表如果有对应就显示，没对应就显示null 图片

右连接，right jonin或right outer join 跟左连接相反，把右边表的数据都取出来，左边表如果对应就显示，不对应就显示null 图片

keyfall commented 5 years ago

事务

事务是不可分割的工作单元，保证一个业务的完整性 mysql默认开启自动提交 select @@autocommit; 显示1就是开启 默认提交就是执行一个sql语句的时候，会自动提交，不可回滚。

事务回滚 rollback;

使语句可以回滚 设置自动提交为0 set autocommit=0 这时提交一个sql语句后 再进行rollback，之前的语句就没有实现 另外如果在rollback之前输入commit，那么回滚也没用了

sql语句流程 图片

手动开启事务 begin或者start transaction 在输入sql语句前.进行begin;或者start transaction;就可以回滚 即使开启了自动提交也会回滚的。

事务的四大特性： A 原子性：事务是最小的单位，不可以分割 C 一致性：事务要求，同一事务中的sql语句，必须保证同时成功或者同时失败 I 隔离性： 事务1 和 事务2 之间是有隔离性的 D 持久性： 事务一旦结束(commit,rollback),就不可以返回 图片

keyfall commented 5 years ago

事务隔离性详解

1.read uncommitted 读未提交的 2.read committed 读已经提交的 3.repeatable read 可以重复读 4.serializable 串行化

查看数据库的隔离级别 图片

修改隔离级别 set global transaction isolation level read uncommitted; 图片

read uncommitted脏读 有a事务，和事务b a 事务对数据进行操作，操作过程中，事务没有被提交，但是b可以看见a操作的结果 例子：小明买小刚的东西，转账过去了（执行sql语句小明减100，小刚加100，但是还没有commit）,这时俩人都可以看见自己账号资金变动，后来小明rollback了，这时小刚的钱又减回去了

read committed 读提交了的 事务a和事务b a事务对数据进行查询，a事务结束前，b事务提交了数据，a再进行查询就跟之前查的不一样了，看到了b提交的数据。

keyfall commented 5 years ago

repeatable read 幻读

事务a和事务b都开启，然后事务a查询有5条数据，这时b插入一条数据，提交了，a再查询还是5条，a再进行插入，就会报错，a没有读到b提交的数据

串行化

事务a和事务b都在进行修改数据（增加，修改，删除），如果a先进行，那么b就要等着，等到a事务commit后，b才能提交（可能会超时，超时就报错）

隔离级别越高，性能越差，出现问题最少 按隔离界别从小到大排序 read-uncommitted read-committed repeatable-read serializable

mysql默认隔离级别是 repeatable-read

keyfall commented 3 years ago

mysql事务的实现原理

事务4大特性：原子性，一致性，隔离性，持久性

事务的目的：

实现事务功能的三个技术：

redo log

重做日志，用来实现事务的持久性，该日志由两部分组成：重做日志缓冲(redo log buffer)和重做日志文件(redo log),缓冲在内存中，文件在磁盘中 事务提交之后把所有修改信息存到重做日志中 image image

redo log作用：mysql把修改先存到buffer pool(缓冲池)，把这个当做缓存来用，再使用后台线程去做缓冲池和磁盘之间的同步，如果进入缓存，同步时断电会丢失部分修改信息，所以引入redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据. 事务开始之后就产生redo log,redo log的内容不是事务提交才写入，在事务执行过程中，便开始写入redo log文件中.

redo log也需要存储，涉及磁盘IO，为啥使用？ redo log的存储是顺序存储,而缓存同步是随机操作 缓存同步是以数据页为单位的，每次传输的数据大小大于redo log

redo log是用来恢复数据，保障已提交事务的持久性

undo log

undo log是回滚日志，记录数据被修改前的信息，重做日志记录数据被修改后的信息. undo log作用：假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态 undo log是用来回滚数据保障未提交事务的原子性.

mysql锁技术

就是多个请求读取表中的数据可以不采取任何操作，但是如果有修改请求时需要一种措施进行并发控制，否则可能会造成读请求不一致

读写锁： 共享锁，又叫做读锁，读锁是可以共享的，或者说多个读请求可以共享一把锁读数据，不会造成阻塞 排他锁，又叫做写锁，写锁会排斥其他所有获取锁的请求，一直阻塞，直到写入完成释放锁

MVCC基础

MVCC(MultiVersion Concurrency Control)叫做多版本并发控制 InnoDB的MVCC，是通过在每行记录的后面保存两个隐藏的列来实现的.这两个列，一个保存了行的创建时间，一个保存了行的过期时间，存储的不是时间值，是系统版本号 通过数据多版本来进行读写分离，实现不加锁而做到读写并行

MVCC在mysql中的实现是靠undo log和read view undo log:undo log中记录某行数据的多个版本的数据 read view:用来判断当前版本数据的可见性

image

事务的实现

事务原子性是通过undo log实现 事务持久性是通过redo log实现 事务隔离性是通过(读写锁+MVCC)实现 事务的一致性是通过原子性，持久性和隔离性实现

隔离性实现： sql标准里定义了四种隔离级别,每一种级别都规定一个事务中的修改，哪些是事务之间可见的，哪些是不可见的. 级别越低的隔离级别可以实现越高的并发,但同时实现复杂度以及开销也越大

4种隔离级别(级别由低到高)：

READ UNCOMMITED隔离级别下，事务中的修改即使还没提交，对其他事务是可见的。事务可以杜宇未提交的数据，造成脏读 因为读不加任何锁，所以写操作在读的过程中修改数据，所以会造成脏读，好处是可以提升并发处理性能,能做到读写并行 优点：读写并行,性能高 缺点：造成脏读

READ COMMITTED隔离级别下，修改提交前，对其他事务是不可见的.其他事务能读到已提交的修改变化。在很多场景下可以使用这种逻辑

InnoDB在READ COMMITTED，使用了MVCC机制 该级别会产生不可重读以及幻读问题 不可重读是一个事务内多次读取的结果不一样 产生不可重复读是因为MVCC机制有关系，每次select都会新生成一个版本号，所以每次select时读的不是一个副本而是不同的副本 在每次select之间有其他事务更新了我们读取的数据并提交了,就出现了不可重复读 image

REPEATABLE READ(Mysql默认隔离级别) 在一个事务内的多次duqu读取的结果是一样的，这种级别下可以避免脏读，不可重复读等查询问题，mysql可以使用读写锁或者MVCC实现这种效果 读写锁实现 image 不管是读还是写，每次都加上锁，没有释放锁，其他就不能操作，比如读请求加上读锁，读读共享，不释放锁，写请求进不来，就会一直读第一次读的数据 优点：实现简单 缺点：无法做到读写并行

MVCC实现 image 多次读取只生成一个版本，读到的自然是相同数据 优点：读写并行 缺点：实现的复杂度高

SERIALIZABLE image

image