MySQL day6（2019.4.27）

一、外键 foreign key：

如果说有一张表中的某个字段（非主键），它是另外一张表的主键，我们称这个字段位外键。

1．增加外键

外键可以在创建表的时候添加，同样的也可以在创建表之后添加。

1)在创建表的时候添加

在所有的字段申明之后，添加外键（foreign key）。

foreign key(字段) references 外表(主键);

实例：

image.png

看图说话：
外键使用desc语法无法查看。只能看到在外键的位置有多个键。
在建立外键的时候，外键的字段必须是一个键（索引），所以在创建外键的时候，数据库系统会自动的给这个字段先添加一个键。然后再添加外键，所以能够看到有MUL多个键的属性。

image.png
外键具有自己的名字，外键可以通过建表语句查看。

2)在建好表之后修改表结构

alter table 表名 add 【constraint 外键名】foreign key(外键字段) references 外部表表（主键字段）；

image.png

外键可以有多个。

1．修改&删除外键

外键不能修改，只能删除外键，再添加外键。

alter table table_name drop foreign key 外键名；

image.png

删除外键之后会将外键的约束删除掉，但是在外键之前建立的建（一般索引）还是会保存的。

2．外键的作用

子表：外键所在的表。
父表：就是外键指定字段引用的表。
外键的默认约束有两个：

• 外键对子表的约束：
  外键会对子表的数据进行约束。如果在外键的父表中没有某个主键的值，而在子表中添加数据的时候，使用了这个值作为子表的外键字段的值。子表数据不能插入。比如说my_1表具有主键（ID）为1,2,3的数据，但是在学生表my_3表中添加数据的时候使用classid为4值得。那么不允许插入。

  
  image.png

• 外键对父表的约束：
  如果要对父表中的数据进行写操作（增删改），子表中具有与之对应的值，那么修改父表失败。

  
  image.png

3．外键的条件

• 外键存储数据要求数据库的存储引擎是innoDB，如果不是innoDB，那么外键也是可以添加的，但是外键的约束作用不生效。
• 外键的字段类型必须与指定主键的字段类型一致。
• 一张表中外间的名字不能重复，因为要是用外键的名称删除外键，但是一张表可以有多个外键。
• 在增加外键字段的时候，如果表中已经具有数据，那么数据的格式必须是符合外键约束条件的。这有点类似与主键。

4．外键约束

外键可以定制符合自己要求的约束。外键的约束有三种，都是针对父表的。
1.严格模式（district）（默认的）父表不能删除或者更新一个已经被子表添加引用的记录。
2.级联模式（cascade）父表的操作，对应子表的数据也会跟着变化。
3.置空模式（set null）父表操作之后，子表的外键字段被置空。（外键允许为空）
通常的设置是：删除的时候置空，修改的时候级联操作。

指定语法：

foreign key (字段) references 外表（主键） on delete 模式 on update 模式；

级联模式：

image.png

置空模式：

image.png
注意：
直控模式下，子表外键的字段必须可以是空的。
image.png

外键虽然很强大，但是降低程序员对数据的可控性，所以通常不使用外键约束，但是外键约束的逻辑是正确的，那么我们可以使用代码去模拟外键的约束。

二、视图

视图：视图是一种有结构没结果的虚拟表结构。表的结构来源于查询，表的数据也是来源查询。
有结构：表的结构（行，列）是通过数据查询语句建立的。
没结果：不能有真实的数据，他的数据全部是通过查询从其他表获取的。

1．创建视图

基本语法：

create view 视图名称 as select语句；

2)创建单表视图

create view vie1 as select name from  my_1;

image.png

2)创建多表视图

create view vie2 as select * my_1 ,my_3 where my_1.id=my_3.classid;

image.png

看图说话：
创建多表视图的时候，如果两张表中具有相同的字段，并且这个两个字段都会包含在视图中，那么视图建立语句不成立。

image.png

注意：可以使用show tables查看到视图以及数据表。但是没有show views的命令、

2．查看视图

1)查看视图的结构

desc  视图名称；

image.png

show create table|view 视图名;

使用show tables能够看到视图，那么说明视图具有类似于表一样的结构。每一个视图都会创建。frm的文件用来保存表的结构。

image.png

2)查看视图内容

select 字段  from  视图名 条件；

本质上操作视图费类似于操作表，区别在于，表的数据是保存在表中的，而视图的数据是在操作之前查询出来的。所以说本质上视图是对查询语句的封装。
对视图的操作可以使用任何形式的select。

3．视图的修改

视图本身不能进行修改，只能删除之后在创建。但是视图后面的select语句是可以修改的。

alter  view  视图的名称 as  select 语句；

image.png

4．删除视图

drop  view 视图的名称；

image.png

5．视图的意义

视图可以节省SQL语句。可以将一条复杂的SQL语句使用视图存储起来。当需要对这个语句进行查询的时候，可以直接对视图进行操作。
视图可以保证数据的安全，对视图操作数据（删除），原表的数据是不会删除的。修改也不会。
视图很容易实现字段权限的控制。在大型系统中，能够做到需要什么给什么。也可以隐藏安全性比较高的数据。

6．视图的数据操作

数据直接在视图上操作，无法操作基表。
基表：数据的来源表。

1)添加数据

多表视图不能进行数据的插入，单表视图是可以进行数据插入的。
多表视图插入错误：

image.png

单表视图：

image.png
但是：如果单表视图中不包含某个数据基表中默认不为空，并且没有指定默认值的数据字段，那么在数据插入的时候会报错。如果某个字段没有默认值也不为空的话，在数据插入的时候是必须要填入数据的。但是如果数据的视图中不包含这个字段，那么我们插入数据的时候就不能穿值过去。
image.png

2)删除数据

多表视图不能进行数据删除，单表视图是可以进行数据删除的。

delete from 视图名 where 条件；

image.png

3)修改数据

理论上单表视图与多表视图都可以进行数据的修改。

update view_name set 字段=值 where 条件；
更新限制：with check option

如果说视图对某个字段做了更新限制，那么在视图进行数据更新的时候，系统会对数据进行验证，要保证在数据更新之后，还能够被视图查询出来。否则更新不能进行。

create view  v4 as select * from my_1 where id>5 with check option;

这句话限制更新了字段ID。如果修改之后的ID号小于5了，那么不允许修改。

image.png
image.png
image.png

4)视图算法

系统对于视图select以及外部查询select之间先后顺序的解析规则。
视图算法分为三种：
undefined ：未定义（磨人的算法）这不是一种实际的算法，只是告诉系统，视图没有算法，自己看着办。
temptable:临时表算法：系统应该先对视图的select进行查询，然后再对视图结果使用select查询。
merge：合并算法：系统应该先将视图的select 查询与外部的select查询进行合并，然后进行执行（效率高：常态）。

create algorithm=temptable view  view_name as  select * from table where条件；

算法选择：
如果最后的查询条件是在视图查询的结果上进行查询就选择temptable，其他情况可以不指定算法。
实例：

image.png

三、事务安全

事务：是一连串连续的数据库操作。
举例：比如说张三给李四转500块钱。
1.把张三账户-500块钱。
2.把李四的账户+500块钱。
事务安全：保证连续的数据操作能够正常成功执行，一旦某个环节出现错误，之前的所有操作全部回滚。撤销。
数据的操作具有不可逆性。那么我们就需要一种机制保证事务的安全。

1．事务操作：

事务操作分为两种：自动事务，手动事务。
手动事务的流程：
1.开启事务。
告诉系统下面我的所有操作都是一连串相关的操作，只要有其中某个出现错误，那就全部撤销（回滚），如果说能够执行成功，那就全部执行成功。

start transaction

image.png

2. 事务操作：

   
   image.png
   image.png

update my_1 set id=6 where id=5;
update my_1 set id=5 where id=1;
update my_1 set id=1 where id=6;
update my_1 set num=4 where id=1;

image.png

在事务关闭之前，当前操作的客户端数据结果已经发生了变化，但是其他的客户端还没有发生改变，也就是说事务只有在结束之后才会真实生效。

3. 关闭事务：
   将日志文件中的操作保存到数据表。或者直接叫清空事务。

a)提交事务：

commit;

image.png

事务提交之后,会同步到各个客户端。其实本质上是服务器内部已经发生改变，客户端只是获取的服务器的内容。

b) 事务回滚：

rollback;

当事务中某一个环节出现了问题，可以使用回滚返回到事务开始之前的状态。

image.png

2．事务原理

事务开启之后，所有数据库的操作都会保存临时事务日志中。只有在得到commit命令或者rollback命令之后，日志才会清空。如果提交的命令是commit的话，我们会将日志清空的同时，将所有数据的操作结果写入到数据库中。如果提交的是rollback命令，那么只是情况事务日志。
在哪些情况会发生rollback（自动的rollback）：
断电，断网，失去连接，一些错误。宕机。

image.png

3．事务的回滚点
回滚点：在事务中的某一次操作成功之后，后续的操作不管是成功还是失败。都不会对之前的所有操作造成影响。我们就可以在这个位置设置一个回滚点，让事务没有必要完全回滚。为了防止事务失败完全回滚，可以设置后续事务出现错误的时候回滚到这个地方。
基本语法：

savepoint 回滚点的名称;

回滚到某个回滚点的位置：

rollback to 回滚点名称;

实例：
将ID为1的和ID为5的做替换，然后将ID为2 的删除。

image.png

update my_1 set id=6 where id=5;
update my_1 set id=5 where id=1;
update my_1 set id=1 where id=6;
savepoint stp1;
delete from my_1 where id=2;
rollback to stp1;

image.png image.png

4．自动事务

在MySQL中，所有的操作默认的都是自动事务操作。用户操作完成后立即同步到数据库中。
自动事物通过系统的autocommit变量控制。默认是开启的。

show variables  like ‘%autocommit%’;

image.png

修改自动事务不提交

set autocommit = off;

image.png

尝试删除表中的一条数据：

image.png
image.png

5．事务特性

事务有四大特性：ACID
A:atomit 原子性：事务的所有操作是个整体。要么全部成功，要么全部失败。
C:consistency一致性：事务操作的前后数据库表的数据不会发生变化，之后再事务处理完成之后统一提交或者回滚才会改变。
I：isolation 隔离性：事务操作是相互隔离不受影响的。两个相同的或者不同的数据库事务不会相互之间产生影响。每一个事务都有一个单独的事务日志。
D:durability 持久性：事务一旦提交，不可改变。数据库操作不可逆。

6．数据库锁机制

表锁机制：当数据表进行操作的时候，对整个表进行锁定，任何其他操作都允许。
行锁机制：当数据行进行数据操作的时候，对整个行进行锁定，不能进行任何其他操作。
如果数据表没有任何索引，那么行锁会自动变为表锁，第二个事务会产生等待。
innoDB默认的是行锁机制，myisam的话是表锁机制。innoDB支持事务，myisam支持事务。
实例：
创建一张没有索引的表：

image.png
image.png

对整个表锁定，修改表结构也不允许

image.png
行锁机制：
image.png
image.png

四、数据库管理

数据备份与还原
备份：将当前的数据保存下来。
还原：将保存下来的数据恢复到数据库中。
数据库的备份有以下这几种：
数据表备份，单表数据备份，SQL备份，增量备份。

1)数据表备份

不需要通过数据库操作，直接进入到数据的安装目录，去找这样的文件。
每一个数据库在安装目录的数据目录里面都对应的是一个文件夹。

image.png
image.png

在innoDB中数据表只有一个叫.frm的文件用于存储表的结构。表的数据是统一保存ibdata1文件中。而且这个文件是所有的数据库共享的。

image.png
在myisam引擎中，没创建一个表，都会产生三个文件。
image.png
FRM：数据表的结构。
MYD：数据表的数据文件。

MYI：数据表的索引文件。
其实数据表备份用来备份存储引擎是myisam 的数据库，数据库的存储引擎是innoDB 的时候，表备份在拷贝文件之后能够显示，但是不能使用，而myisam能够正常使用。

数据表备份就是将创建数据库的时候建立三个文件复制到要使用的地方。就可以在新的数据库中使用。

存储引擎：
MySQL进行数据存储的方式主要有两种：innoDB 与 myIsam。

image.png

myisam与innoDB的区别：
存储形式不一样：
myisam存储的是由三个文件。innoDB存储的是两个文件。
锁机制不同：
myisam是表锁机制。innoDB是行锁机制。innoDB的性能更高一点。
事务支持：
innoDB支持事务，myisam不支持事务。
2)单表数据备份
每次只备份一张数据表，只备份数据，不备份数据表结构。
通常使用的是将表中的数据备份到外部的文件中。

select */字段 into outfile ‘文件位置’ from 数据源；

image.png
image.png

高级备份处理：

select */字段 into outfile ‘文件位置’ fields 字段处理  lines 行处理 from 数据源；

fields处理：
enclosed by: 字段使用什么包裹，默认的是空字符串
terminated by :字段是以什么结束的，默认的是\t，制表符
escaped by :特殊符号使用什么处理，默认的是’\’,使用反斜杠转义。
lines处理：
starting by:以什么开始每行数据，默认的是空字符串。
terminated by：以什么结束，默认的是\r\n;回车换行符。

image.png

数据还原：

load data infile ‘文件位置’ into table  table_name fields 字段处理规则 lines 行处理规则;

image.png

3)SQL备份

SQL备份备份的SQL语句。系统会自动的将表结果以及表的数据组成SQL语句。备份的就是最后的SQL语句。
还原的时候只需要直接运行ＳＱＬ语句就可以了。表结构备份称建表语句，表的数据备份称插入语句。
数据本身没有提供ＳＱＬ备份的命令。但是数据库集成了一个备份的客户端。ＭｙＳＱＬｄｕｍｐ。

image.png

mysqldump -h -P -u -p 数据库 [数据表] >  外部文件

image.png

可以备份一个数据表，也可以备份多个数据表。
备份还原：两种方案：
方案一：
使用MySQL客户端还原。

mysql -hpup 数据库名 < SQL文件

-hpuh就是主机端口号 用户名 密码的意思。
注意在导入数据的时候，必须先创建数据库。
一般数据还原的时候都是创建一个新的数据库，然后将数据的备份文件在这个数据库中执行。
先创建数据库：

image.png

退出使用MySQL导入：

image.png
导入成功：
image.png
方案二：

MySQL命令还原。
source 文件位置

image.png
image.png

SQL备份的优缺点：
有点：能够备份数据表结构
缺点：会产生非常多无用的SQL。
4)增量备份
不是针对表结构的，也不是针对数据的，它是针对整个数据库某个时间段的日志文件。
增量备份会将数据库某个时间段所有的操作都保存下来。
增量备份会广泛的使用在实际项目中。

五、用户管理

在用户登录数据库的时候需要使用用户名还有密码进行验证。在用户操作数据库的时候也需要对用户的权限进行验证。

image.png

1.查看用户

查看用户的命令：

select *[字段] from mysql.user;

image.png

2.创建用户

基本语法：

create user ‘用户名’@’主机名’ [identified by ‘用户密码’];

image.png
image.png
image.png

3.删除用户

基本语法：

drop user ‘用户名’@’主机’；

image.png

4.修改用户

修改用户密码：

set password for ‘用户名’@’主机’ = password（密码）;

image.png

5.用户授权

分配用户对数据库的操作权限。

image.png

权限分配可以做到指定用户对指定数据库里面的指定表的指定数据操作。
语法：

grant 权限名称 on 数据库.数据表 to ‘用户名’@’主机’ [with grant option];

image.png
image.png
image.png
with grant option：
当时用这条命令的时候，新的用户就具有了向其他用户赋值权限的功能，所以一般不会使用。
注意事项：
权限可以小写，全部的权限可以使用all替换。
数据库与数据表如果是全部赋值的话可以使用.
with grant option 使用之后授权的用户也具有了权限分配的能力，所以一般不会使用。
如果有多个权限使用逗号隔开。

6.权限回收

与grant操作相反的操作。

revoke 权限on db.tb from ‘用户名’@’主机’;

image.png
image.png

7.找回root密码（了解）

image.png