备份和恢复命令

备份库

直接在cmd窗口中直接输入，结束不需要输入；

mysqldump -h端口号 -u用户名 -p密码 数据库名>备份地址

恢复库

在cmd窗口中进行
1、连接数据库
mysql -u用户名 -p密码
2、创建数据库
create database 库名
3、切换到可用数据库
use 库名
4、进行恢复
source 备份文件地址

授权：

新用户信息增改

1.创建用户:
# 指定ip：192.118.1.1的用户登录
create user '用户名'@'192.118.1.1' identified by '密码';
# 指定ip：192.118.1.开头的用户登录
create user '用户名'@'192.118.1.%' identified by '密码';
# 指定任何ip的用户登录
create user '用户名'@'%' identified by '密码';
2.删除用户
drop user '用户名'@'IP地址';
3.修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
4.修改密码
set password for '用户名'@'IP地址'=Password('新密码');

用户权限管理

#查看用户权限
show grants for '用户名'@'IP地址'
1、授权
#授权用户仅对某文件有查询、插入和更新的操作
grant select,insert,update on 文件名 to '用户名'@'IP地址';
#授权所有的权限，除了grant这个命令，这个命令是root才有的。用户对db1下的t1文件有任意操作
grant all privileges  on db1.t1 to '用户名'@'IP地址';
#授权用户可以对db1数据库中的所有文件执行任何操作
grant all privileges  on db1.* to '用户名'@'IP地址';
#授权用户可以对所有数据库中文件有任何操作
grant all privileges  on *.*  to '用户名'@'IP地址';
 
2、取消权限
# 取消用户对db1的t1文件的任意操作
revoke all on db1.t1 from '用户名'@'IP地址';  
# 取消来自远程服务器的mjj用户对数据库db1的所有表的所有权限
revoke all on db1.* from '用户名'@'IP地址';  
# 取消来自远程服务器的mjj用户所有数据库的所有的表的权限
revoke all privileges on *.* from '用户名'@'IP地址';

为什么使用视图

多表的联合查询，最多也才3张表，如果面临更多的表，为了简化连表操作，可以使用MySQL中的视图

好处

1、简化sql语句

2、提高了sql的重用性

3、保护基表的数据，提高了安全性

创建视图

create view 视图名
as
查询语句;

修改视图

方式一

create or replace view 视图名
as
查询语句;

方式二

alter view 视图名
as
查询语句

删除视图

drop view 视图1，视图2,...;

查看视图

desc 视图名;
show create view 视图名;

视图和表的对比

	关键字	是否占用物理空间	使用
视图	view	占用较小，只保存sql逻辑	一般用于查询
表	table	保存实际的数据	增删改查

什么是索引

==索引用于快速找出在某个列中有一特定值的行，避免全表扫描==

MySQL索引，默认是B+树索引

查询表中的索引

show index from 表名

索引的优缺点

优点：

1、所有的MySQL列类型（字段）都可以被索引，也就是可以给任意字段设置索引

2、大大加快数据的查询速度

缺点：

1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

2、索引也需要占空间，如果有大量的索引，索引文件可能会比数据文件更快达到上限值

3、当对表中的数据进行增、删、改时，索引也需要动态的维护，降低了数据的维护速度

索引的分类

单列索引（普通索引、唯一索引、主键索引）、组合索引、全文索引、空间索引

创建索引

普通索引(b+树索引)

alter table table_name add index index_name(column_name) using btree;
create unique index index_name on table_name(column_name);

组合索引

唯一索引

全文索引

 

什么是事务

事务是作为单个逻辑单元执行的一系列操作

多个操作作为一个整体向系统提交，要么执行、要么都不执行，事务是一个不可分割的工作工作逻辑单元

四大特性（ACID）

原子性（A -- Atomicity）：原子是参与化学反应中最小的粒子，不能再分割了，也就是说事务就是最小的，不能再分割了，如果把事务都分割了，就会出现问题

一致性（C -- Consistency）：在事务执行前数据库的数据处于正确的状态，而事务执行完成后数据库的数据还是处于正确的状态，即数据完整性约束没有被破坏

隔离性（I -- Isolation）：事务与事务之间互不干扰

持久性（D -- Durability）：通过事务进行操作后的数据，是永久保存的

注意：在实际开发中，经常会打破隔离性，当多个事务共同操作同一张表的时候，一旦打破了隔离性，就会出现安全问题

存储引擎

存储引擎：RDBMS中，决定了数据如何存储，如何获取，如何控制事务，如何控制外键等一系列功能的一套程序

**常用引擎：**InnoDB，MyIsam

InnoDB与MyIsam的区别

1. InnoDB支持事务，支持外键；而MyIsam不支持事务，不支持外键
2. InnoDB由于受到事务和外键的影响，所以对数据的存储以及查询效率偏低；MyIsam相反偏高
3. InnoDB在存储时，表文件是2个：frm，ibd；而MyIsam是3个文件，分别存储frm，MYD，MYI
4. InnoDB是MYSQL 5.5之后的默认存储引擎；而MyIsam是5.5之前的默认存储引擎

事务操作

方式一

开启事务：start transaction;

提交事务：commit;

==注意==：当事务开启之后，只有执行了commit，数据才会真的改变，如果没有执行commit，数据还原

回滚事务：rollback;

方式二

修改默认的提交方式，默认是自动提交，我们要改成手动提交

show variables like '%autocommit%'

set @@autocommit=0（默认为1，自动提交；0，手动提交）

隔离级别

隔离级别	读数据一致性及允许的并发副作用	备注
读未提交（Read uncommitted）	最低级别，只能保证不读取物理上损坏的数据，事务可以看到其他事务没有被提交的数据（脏数据）
读已提交（Read commited）	语句级，事务可以看到其他事务已经提交的数据	Oracle数据库默认
可重复读（Repeatable read）	事务级，事务中两次查询的结果相同	MySql数据库默认
可串行读（序列化Serializable）	最高级别，事务级。顺序执行

隔离等级越高，数据库事务并发执行能力越差，能处理的操作越少。因此在实际项目开发中为了考虑并发性能一般使用读已提交隔离级别，他能避免丢失更新和脏读，尽管不可重复读和幻读不能避免，但可以在可能出现的场合使用悲观锁或乐观锁来解决这些问题。

什么是幻读、不可重复度、脏读

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据，读取到未提交的数据。

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读

隔离级别与更新丢失的情况

• 第一类更新丢失
  • 事务A撤销时，把已经提交的事务B的更新数据覆盖了
• 第二类更新丢失
  • 事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失

隔离级别	脏读	不可重复读	幻读	第一类丢失更新	第二类丢失更新
Read uncommitted	√	√	√	×	√
Read commited	×	√	√	×	√
Repeatable read	×	×	√	×	×
Serializable	×	×	×	×	×

设置隔离级别

set session transaction isolation level 隔离级别

查看隔离级别(当前客户端)

select @@tx_isolation

锁

乐观锁

==默认存在==，什么都不做，就是乐观锁。总是乐观的认为，在维护这个数据的时候，没有其他人来维护。

insert update delete 在执行SQL的同时，才给数据加上了乐观锁。

可能会导致的问题

age:12
A用户:update table set age=32 where id=1
B用户:update table set age=12 where id=1
A用户:select * from table where id=1
这个时候，A用户查看的数据为B修改过的，自己的修改已经被覆盖

悲观锁

总是担心，在自己修改数据的时候，有其他人，将这个数据修改，所以在修改数据之前，提前锁住数据

==默认没有开启，需要自己开启==

开启命令

#在查询语句后加for update就可以开启（悲观排他锁）
select * from book for update

优缺点

优点：准确性高，更加安全

缺点：效率太低，一旦一个用户执行悲观锁，那么其他用户都无法查看这个表的数据，也无法进行修改，除非执行悲观锁的用户commit

行锁

只会锁住一行数据

select * from book where id=1 for update

表锁

会锁住整个的一个表

select * from book for update

排他锁

A用户在给表加排他锁以后，那么其他用户都无法对表进行操作、查看、加锁

//表级排他锁
lock table book write
//解锁
unlock table

共享锁

所有的用户都可以对表进行加锁，但是，所有的用户都无法对表进行操作，包括上锁的用户

//表级共享锁
lock table book read
//解锁
unlock table

死锁

两个线程自己各自持有自己数据的锁，但互相都想锁住被对方锁住的数据，就产生了死锁

系统检索到死锁，会自动释放一个锁