MySQL学习笔记（四）

目录

 

外键

联合查询

子查询

视图

视图的数据操作

数据库的备份与还原

单表数据备份与还原

SQL备份与还原

增量备份与还原

---

外键

外键（foreign key）：如果一张表中有一个字段（非主键）指向另外一张表的主键，那么该字段被称为外键

其中，外键所在的表称为子表，外键所引用的表称为父表

增加外键

一、在创建表时创建一个外键；在所有表字段后，添加foreign key(外键字段）references 外部表(主键字段);

在创建外键时，要求字段本身必须是一个索引(普通索引)，如果字段本身不是一个索引，那么系统会先创建一个索引，然后再创建外键

二、在表格创建完成之后新增一个外键；

alter table 表名 add constraint 外键名 foreign key(外键字段) references 表名(主键字段);

constraint关键字可以指定外键名

删除外键

一张表可以有多个外键，外键不可修改，必须先删除再新增

alter table 表名 drop foreign key 外键名；

外键在删除之后，使用desc查看数据表的结构不能显示外键是否删除，应该使用show create table查看

外键的作用

外键的默认作用：

对子表的约束：在字表在进行写操作时，如果在父表中无法匹配对应数据，则操作失败；

对父表的约束：在父表对其主键进行删除和修改时，如果对应的子表对数据进行了引用，则不允许操作

外键的存在条件

• 存储引擎必须是innodb（默认的存储引擎），如果非此存储引擎，虽然外键能够创建成功，但是无法产生相应约束
• 外键字段的数据类型，必须和父表的主键数据类型完全一致
• 外键名字不能相同
• 增加外键的字段(数据已经存在)，必须保证数据与父表主键相对应，例如，子表外键字段中包含父表主键中不包含的数据，则新增外键失败

外键约束

外键约束即外键的作用，除了默认作用外，外键约束的功能支持自定义

外键约束分三个模式，主要是针对父表的约束

• 严格模式：district（默认），父表不能对字表引用数据的记录进行操作
• 级联模式：cascade，父表对相关记录操作的同时，字表数据也跟着进行操作
• 置空模式：set null，父表对记录进行操作之后，子表中的数据被系统置空，前提是该字段允许为空

合理的操作模式如下：删除操作时，使用置空约束；更新操作时，使用级联约束，设置语句如下：
foreign key(外键字段) references 父表(主键字段) on delete 模式(set null) on update 模式(set null)

联合查询

将多条select语句查询结果在记录上进行拼接，但是每条select语句获取的字段数必须严格一致

例如:select 语句1 union [union] 选项 select 语句2.....

union选项和select 选项一样：分为all（保留所有）和distinct（去重）---默认

在联合使用中，如果要使用order by语句，必须使用括号将查询语句选中，并且需要使用limit子句限制结果

  由上图可以看出，当没有使用limit子句限制查询结果时，结果并没有按照预期的升序排序

子查询

查询是建立在另外一条查询结果之上进行的，即一条查询语句中包含了另一条查询语句

子查询有两种分类方式：按位置分类、按结果分类

按位置分类

• form子查询：子查询在from之后
• where子查询：子查询在where子句之后
• exits子查询：子查询在exits里面

exits的作用是判断某些条件是否成立，其返回值有两个：0和1

 

按结果分类（理论上来说，任何一个查询语句的结果都是一个二维表）

 

• 标量子查询：子查询结果为一行一列
• 列子查询：子查询结果为一列多行
• 行子查询：子查询结果为一行多列(多行多列)

以上三种查询位置为where之后

• 表子查询：子查询得到的结果为多行多列

上面子查询出现的位置为from之后

在使用行子查询时，如果一个行中需要多个多个字段约束一个记录(例如：需要查询身高最高且年龄最大的学生)，就需要构造行元素，例如:select * from student where (height,age) = (select max(height),max(age) from student);

在使用表子查询时，因为其子查询语句是跟在from之后的，但是from后只能跟表名，因此需要使用as将子查询结果作为表别名跟在from之后

视图

视图：存储的查询语句，当调用的时候，产生结果集，视图充当了虚拟表的角色

创建视图：create view 视图名 as select 语句；

查看视图：视图是虚拟表，因此查看表的所有方式也同样适用于视图

视图和表一样，在系统完成view的创建之后，也会在数据库的存储位置创建一个.frm结构文件

使用视图 ：视图的本质就是执行封装的select语句，但是视图不会占用物理空间，它保存了查询语句，只会在自身被调用的时候产生结果

修改视图：视图的本身是不能修改的，但是可以通过修改其select语句实现对视图的修改

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

删除视图：drop view 视图名

 

视图的作用

• 视图可以提高SQL语句的复用性；
• 数据安全，视图的操作基本是针对查询，如果对视图进行处理（删除）并不会影响基表数据
• 数据保密，视图可以自定义查询语句，可以在不泄露其它信息的基础上提供所需数据
• 视图可以更方便的进行权限控制

视图的数据操作

视图可以进行数据的操作，但是受到了许多的限制

对视图进行插入数据可以影响到基表（前提是必须有相应权限）

数据的新增

• 多表视图不能插入数据
• 单表视图在插入数据时应当包含所有不为空的字段（默认值为空）

数据的删除

• 多表视图不能删除

数据的更新

• 单表数据和多表数据都能进行数据的更新
• 更新限制：with check option，数据在更新时会受到更新限制的约束，如果在对视图数据进行更新时，系统会进行验证，要保证更新数据之后数据依然能够被查询，否则拒绝更新操作

例如：create view 视图名 where age > 30 with check option;

在受到此约束（age > 30）之后，如果将视图中的age更新为小于30的值，则该更新请求被拒绝

视图算法

视图算法：系统对视图以及 外部查询视图的select语句的一种解析方式

视图算法分为三种：

• undefined：未定义（默认）
• temptable：临时表算法，系统先执行视图的select语句，后执行外部的select语句
• merge：合并算法，系统先将视图的select和外部的select合并，之后再执行(效率高)，且如果视图算法为undefined，系统经常会选择此类算法

在创建视图的时候指定视图算法

create algorithm = 视图算法 view 视图名 as select 语句；

数据库的备份与还原

数据备份：将已有的数据或记录保留

数据还原：将已经备份的数据恢复到相应的数据表中

数据备份还原的方式：数据表备份、单表数据备份、SQL备份、增量备份

数据表备份 直接进入数据库文件夹将对应文件复制到指定位置，在还原时直接将备份的数据放回相应位置

数据库的数据表备份根据存储引擎的不同略有区别

存储引擎 即为MySQL进行数据存储的方式，分为innodb和myisam

两种存储引擎数据存储方式：

• innodb：只有表结构，所有的数据文件都在ibdata1中存储
• myisam：表，数据和索引全部分开存储

创建一个新表，将数据库引擎指定为myisam，查看数据库文件

因此，数据表备份十分适合存储引擎为myisam的数据库，将数据表产生的三个文件复制到指定数据库下即可，但是innodb引擎不支持这种备份方式

单表数据备份与还原

单表数据备份

每次只能对一张表进行备份，且只能对数据进行备份而不能对表结构进行备份

备份方式：从表中选取一部分数据保存到外部文件(outfile)

select */字段列表 into outfile 文件路径 from 数据源;          #前提是外部文件不存在（必须在备份时新建）

高级备份：自定义字段和记录的处理方式

select */字段列表 into outfile 文件路径 fileds 字段处理 lines 行处理 from 数据源;

fileds字段处理方式：

enclosed by:字段以指定符号包裹，默认为空字符串
terminated by：字段以指定符号结束，默认为'\t'，tab键
escaped by：特殊符号的处理方式，默认为以'\\'转义

lines行处理方式：

starting by:行以指定内容开始，默认为空字符串
terminated by:行以指定内容结束，默认为"\r\n"，换行符

单表数据还原

将保存正外部的数据重新恢复到数据表中称为数据还原，但是如果表结构不存在，则无法还原

load data infile 文件路径 into table 表名[字段列表] fileds 字段处理 lines 行处理 from 数据源；        #还原的字段处理和行处理必须和字段备份的方式保持一致

SQL备份与还原

SQL备份：备份的数据为SQL语句，是系统对数据进行处理所转化的SQL语句，还原时只需执行SQL语句即可

MySQL中没有备份相关的SQL指令，如果需要进行SQL备份，需要使用mysqldump.exe

mysqldump -uroot -p 数据库名 表名1,[表名2..] > 指定文件路径.sql（建议使用.sql文件后缀名，因为导出的文件为SQL语句）

如果不指定表名， 则对整个数据库进行备份

右键选择student.sql的打开方式，选择以MySQL Workbench打开，文件内容如下：

SQL还原数据

1.使用mysql.exe客户端进行数据还原

        mysql -uroot -p 数据库名(不需要指定表名) < 备份文件目录;

2.使用SQL指令还原

        source 备份文件存储路径;

SQL语句备份可以备份表结构

增量备份与还原

增量备份是针对MySQL服务器的日志文件进行的备份操作

增量备份是指定时间段进行的备份(适合大项目备份)