1. 外键

定义: foreign key, 外面的键(键不在自己表中): 如果一张表中有一个字段(非主键)指向另外一张表的主键,那么将该字段称之为外键.

1.1 增加外键

  • 创建表的时候增加外键: 在所有的表字段之后,使用：
foreign key(外键字段) references 外部表(主键字段)
  • 在新增表之后增加外键: 修改表结构
Alter table 表名 add [constraint 外键名字] 
foreign key(外键字段) references 父表(主键字段);

1.2 修改外键&删除外键

外键不可修改: 只能先删除后新增.

删除外键语法

Alter table 表名 drop foreign key 外键名; 
-- 一张表中可以有多个外键,但是名字不能相同

1.3 外键作用

外键默认的作用有两点:

  • 对子表约束: 子表数据进行写操作(增和改)的时候, 如果对应的外键字段在父表找不到对应的匹配: 那么操作会失败.(约束子表数据操作)
  • 对父表约束: 父表数据进行写操作(删和改: 都必须涉及到主键本身), 如果对应的主键在子表中已经被数据所引用, 那么就不允许操作

1.4 外键条件

  1. 外键要存在: 首先必须保证表的存储引擎是innodb(默认的存储引擎): 如果不是innodb存储引擎,那么外键可以创建成功,但是没有约束效果.
  2. 外键字段的字段类型(列类型)必须与父表的主键类型完全一致.
  3. 一张表中的外键名字不能重复.
  4. 增加外键的字段(数据已经存在),必须保证数据与父表主键要求对应.

1.5 外键约束

外键约束有三种约束模式: 都是针对父表的约束

  • District: 严格模式(默认的), 父表不能删除或者更新一个已经被子表数据引用的记录
  • Cascade: 级联模式: 父表的操作, 对应子表关联的数据也跟着被删除
  • Set null: 置空模式: 父表的操作之后,子表对应的数据(外键字段)被置空

通常的一个合理的做法(约束模式): 删除的时候子表置空, 更新的时候子表级联操作
指定模式的语法

Foreign key(外键字段) references 父表(主键字段) 
on delete set null on update cascade;

2. 联合查询

联合查询: 将多次查询(多条select语句), 在记录上进行拼接(字段不会增加)

2.1 基本语法

多条select语句构成: 每一条select语句获取的字段数必须严格一致(但是字段类型无关)

Select 语句1
Union [union选项]
Select语句2...

Union选项: 与select选项一样有两个

  • All: 保留所有(不管重复)
  • Distinct: 去重(整个重复): 默认的

2.2 意义

联合查询的意义分为两种:

  1. 查询同一张表,但是需求不同: 如查询学生信息, 男生身高升序, 女生身高降序.
  2. 多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的.

2.3 Order by使用

在联合查询中: order by不能直接使用,需要对查询语句使用括号才行
若要order by生效: 必须搭配limit: limit使用限定的最大数即可.

3. 子查询

子查询: sub query, 查询是在某个查询结果之上进行的.(一条select语句内部包含了另外一条select语句).

3.1 子查询分类

子查询有两种分类方式:
（1）按位置分类: 子查询(select语句)在外部查询(select语句)中出现的位置

  • From子查询: 子查询跟在from之后
  • Where子查询: 子查询出现where条件中
  • Exists子查询: 子查询出现在exists里面

（2）按结果分类: 根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结果都可以理解为二维表)

  • 标量子查询: 子查询得到的结果是一行一列
  • 列子查询: 子查询得到的结果是一列多行
  • 行子查询: 子查询得到的结果是多列一行(多行多列)。上面几个出现的位置都是在where之后
  • 表子查询: 子查询得到的结果是多行多列(出现的位置是在from之后)

4. 视图

视图: view, 是一种有结构(有行有列)但是没结果(结构中不真实存放数据)的虚拟表, 虚拟表的结构来源不是自己定义, 而是从对应的基表中产生(视图的数据来源).在这里插入代码片

4.1 创建视图

基本语法

Create view 视图名字 as select语句; 
-- select语句可以是普通查询;可以是连接查询; 可以是联合查询; 可以是子查询.

视图是一张虚拟表: 表, 表的所有查看方式都适用于视图:

show tables [like];
desc 视图名字;
show create table 视图名;

4.2 查看视图

查看”表(视图)”的创建语句的时候可以使用view关键字

show create view 视图名;

视图一旦创建: 系统会在视图对应的数据库文件夹下创建一个对应的结构文件: frm文件

4.3 使用视图

使用视图主要是为了查询: 将视图当做表一样查询即可.

4.4 修改视图

视图本身不可修改, 但是视图的来源是可以修改的.

Alter view 视图名字 as 新的select语句;

4.5 删除视图

Drop view 视图名字;

4.6 视图意义

  1. 视图可以节省SQL语句: 将一条复杂的查询语句使用视图进行保存: 以后可以直接对视图进行操作
  2. 数据安全: 视图操作是主要针对查询的, 如果对视图结构进行处理(删除), 不会影响基表数据(相对安全).
  3. 视图往往是在大项目中使用, 而且是多系统使用: 可以对外提供有用的数据, 但是隐藏关键(无用)的数据: 数据安全
  4. 视图可以对外提供友好型: 不同的视图提供不同的数据, 对外好像专门设计
  5. 视图可以更好(容易)的进行权限控制

4.7 视图数据操作

视图是的确可以进行数据写操作的: 但是有很多限制
将数据直接在视图上进行操作.

（1）新增数据

  1. 多表视图不能新增数据
  2. 可以向单表视图插入数据: 但是视图中包含的字段必须有基表中所有不能为空(或者没有默认值)字段
  3. 视图是可以向基表插入数据的.

（2）删除数据
多表视图不能删除数据
单表视图可以删除数据

（3）更新数据
理论上不能单表视图还是多表示视图都可以更新数据.

4.8 视图算法

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

视图算法分为三种：

  • Undefined: 未定义(默认的), 这不是一种实际使用算法, 是一种推卸责任的算法: 告诉系统,视图没有定义算法, 系统自己看着办
  • Temptable: 临时表算法: 系统应该先执行视图的select语句,后执行外部查询语句
  • Merge: 合并算法: 系统应该先将视图对应的select语句与外部查询视图的select语句进行合并,然后执行(效率高: 常态)

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

Create algorithm = 指定算法 view 视图名字 as select语句;

视图算法选择: 如果视图的select语句中会包含一个查询子句(五子句), 而且很有可能顺序比外部的查询语句要靠后, 一定要使用算法temptable,其他情况可以不用指定(默认即可).

5. 数据备份与还原

数据备份还原的方式有很多种: 数据表备份, 单表数据备份, SQL备份, 增量备份.

5.1数据表备份

直接进入到数据库文件夹复制对应的表结构以及数据文件, 以后还原的时候,直接将备份的内容放进去即可.

前提条件: 根据不同的存储引擎有不同的区别.
存储引擎: mysql进行数据存储的方式.
主要是两种:

  • Innodb: 只有表结构,数据全部存储到ibdata1文件中
  • Myisam(免费): 表,数据和索引全部单独分开存储

这种文件备份通常适用于myisam存储引擎: 直接复制三个文件即可, 然后直接放到对应的数据库下即可以使用.

5.2 单表数据备份

每次只能备份一张表; 只能备份数据(表结构不能备份)。通常的将表中的数据进行导出到文件。
基本备份：

Select */字段列表 into outfile 文件所在路径 from 数据源; 
-- 前提: 外部文件不存在

高级备份: 自己制定字段和行的处理方式

Select */字段列表 into outfile 文件所在路径 fields 字段处理 lines 行处理 from 数据源;
  • Fields: 字段处理
  • Enclosed by: 字段使用什么内容包裹, 默认是’’,空字符串
  • Terminated by: 字段以什么结束, 默认是”\t”, tab键
  • Escaped by: 特殊符号用什么方式处理,默认是’\’, 使用反斜杠转义
  • Lines: 行处理
  • Starting by: 每行以什么开始, 默认是’’,空字符串
  • Terminated by: 每行以什么结束,默认是”\r\n”,换行符

数据还原: 将一个在外部保存的数据重新恢复到表中(如果表结构不存在,那么sorry)

Load data infile 文件所在路径 into table 表名[(字段列表)] fields 字段处理 lines 行处理; 
-- 怎么备份的怎么还原

5.3 SQL备份

系统会对表结构以及数据进行处理,变成对应的SQL语句, 然后进行备份: 还原的时候只要执行SQL指令即可.(主要就是针对表结构)

备份: mysql没有提供备份指令: 需要利用mysql提供的软件: mysqldump.exe
Mysqldump.exe也是一种客户端,需要操作服务器: 必须连接认证

Mysqldump/mysqldump.exe -hPup 数据库名字 [数据表名字1[ 数据表名字2...]] > 外部文件目录(建议使用.sql)

SQL还原数据:
方案1: 使用mysql.exe客户端还原

Mysql.exe/mysql -hPup 数据库名字 < 备份文件目录

方案2: 使用SQL指令还原

Source 备份文件所在路径;

SQL备份优缺点

  1. 优点: 可以备份结构
  2. 缺点: 会浪费空间(额外的增加SQL指令)

5.4 增量备份

不是针对数据或者SQL指令进行备份: 是针对mysql服务器的日志文件进行备份

增量备份: 指定时间段开始进行备份., 备份数据不会重复, 而且所有的操作都会备份(大项目都用增量备份)