六天带你玩转Mysql笔记--第五天
1.外键
（1）外键（foreign key）：外面的键（键不在自己表中），如果一张表中有一个字段（非主键）指向另外一张表的主键，那么将该字段称为外键。
1.1增加外键
外键可以在创建表的时候或者创建表之后增加（但是要考虑数据问题）。
一张表可以有多个外键
1）创建表的时候增加外键：在所有的表字段语句之后，使用foreign key（外键字段） references外部表（主键字段）
--创建外键
create table my_foreign1(
id int primary key auto_increment,
name varchar(20) not null comment'学生姓名',
c_id int comment '班级id',
--增加外键
foreign key(c_id) references my_class(id)
)charset utf8;
2）在新增表之后增加外键，修改表结构。
Alter table表名 add [constraint外键名字] foreign key（外键字段）references 父表（主键字段）
--增加外键
alter table my_foreign2 add
--指定外键名
constraint student_class_1
--指定外键字段
foreign key(c_id)
--引用父表
references my_class(id);
1.2修改外键&删除外键
外键不可修改，只能先删除后新增。
删除外键的语法：
Alter table表名 drop foreign key 外键名;
--查看表结构
desc my_foreign1;
--删除外键
alter table my_foreign1 drop foreign key my_foreign1_ibfk_1;
--查看表结构
desc my_foreign1;
--查看表创建语句
show create table my_foreign1;
1.3外键作用
外键默认作用：（1）一个对父表约束（2）一个对字表约束（外键字段所在的表）
（1）对字表的约束：子表数据进行写操作（增和改）的时候，如果对应的外键字段在父表中找不到对应的匹配，那么操作会失败（约束子表数据操作）。
insert into my_foreign2 values(null,'张飞',5);
（2）对父表的约束：父表数据进行写操作（删除和修改），如果对应的主键在子表中已经被数据引用，那就不允许操作。
1.4外键条件
（1）外键要存在，首先必须保证表的存储引擎是innodb（默认存储引擎）
如果不是innodb存储引擎，那么外键可以创建成功，但是没有约束效果。
（2）外键字段的字段类型（列类型）与父表的主键类型完全一致。
（3）一张表中外键名字不能重复。
（4）增加外键的字段（数据已经存在），必须保证数据
1.5外键约束
（1）所谓外键约束：就是指外键的作用
（2）之前所讲的外键作用：是默认的作用，其实可以通过对外键的需求，进行定制。
（3）外键约束有三种约束模式，都是针对父表的约束。
1）district：严格模式（默认的），父表不能删除或者更新一个已经被字表数据引用的记录
2）cascade：级联模式，父表的操作对字表关联的数据也跟着被删除。
3）setnull：置空模式，父表的操作之后，字表对应的数据（外键字段）被置空。
通常的一个合理做法（约束模式）：删除的时候字表置空，更新的时候子表级联操作指定模式的语法。
指定模式的语法：
Foreign key(外键字段) references父表（主键字段）on delete set null on update cascade;
--创建外键：指定模式（删除置空，更新级联）
create table my_foreign3(
id int primary key auto_increment,
name varchar(20) not null,
c_id int,
--增加外键
foreign key(c_id)
--引用表
references my_class(id)
--指定删除默认，更新级联
on delete set null
on update cascade
)charset utf8;
（4）删除置空的前提条件：外键字段允许为空（如果不满足条件，外键无法创建）
2联合查询
（1）联合查询：将多次查询（多条select语句），在记录上进行拼接（字段不会增加）。
2.1基本语法
（1）多条select语句构成，每一条select语句获取的字段数必须严格一致（但是字段类型无关）
Select语句1
Union[union选项]
Select语句2 .........
| Union选项 | |
| all | 保留所有 |
| distinct | 去除（整个重复），默认的。 |
（2）联合查询只要求字段一样，跟数据类型无关。
2.2意义
联合查询的意义分为两种：
（1）查询同一张表，但是需求不同：如查询学生信息，男生身高升序，女生身高降序。
（2）多表查询：多张表的结构时完全一样的，保存的数据（结构）也是一样的。
2.3 order by使用
（1）在联合查询中：order by不能直接使用，需要对查询语句使用括号才行。
(select * from my_friend where sex = '男' order by age asc)
union
(select * from my_friend where sex = '女' order by age desc);
（2）若要order by生效，必须搭配limit。Limit使用限定的最大数即可。
(select * from my_friend where sex = '男' order by age asc limit 9999999)
union
(select * from my_friend where sex = '女' order by age desc limit 9999999);
3.子查询
（1）子查询：sub query，查询时在某个查询结果之上进行（一条select语句内包含另外一条select语句）。
3.1子查询分类
（1）按位置分类：子查询（select语句）在外部查询（select语句）中出现的位置。
| From子查询 | 子查询跟在from之后 |
| Where子查询 | 子查询出现在where条件中 |
| Exists子查询 | 子查询出现在exists里面 |
（2）按结果分类：根据子查询得到的数据进行分类（理论上讲任何一个查询得到的结果都可以理解为二维表）。
| 标量子查询 | 子查询得到的结果是一行一列。（在where之后） |
| 列子查询 | 子查询得到的结果是一列多行。（在where之后） |
| 行子查询 | 子查询得到的结果是一行多列。（在where之后） |
| 表子查询 | 子查询得到的结果是多行多列。（在from之后） |
3.2标量子查询
需求：知道班级名字为PHP0810后，获取该表所有学生。
分析：
（1）确定数据源：获取符合条件的所有学生
Select * from my_student wherec_id = ?;
（2）获取PHP0810班级的id：可以通过班级名字确定
Select id from my_class where c_name =‘PHP0810’;
代码：
Select * from my_student
where c_id = (Select id from my_class where c_name = 'PHP0810');
3.3列子查询
需求：查询所有在读班级的学生（班级表中存在的班级）
（1）确定数据源：学生
Select * from my_student wherec_id in(?);
（2）确定有效班级的id：所有班级的id
Select id from my_class;
（3）列子查询返回的结果比较：一列多行，需要使用in作为条件匹配。其中在mysql中还有几个类似的条件：all，some，any
1）=any等价于in（其中一个满足即可）。
2）Any与some是一样的。
3）=all即为全部。
陈述语句代码：
Select * from my_studentwhere c_id=any(Select id from my_class);
Select * from my_studentwhere c_id=some(Select id from my_class);
Select * from my_studentwhere c_id=all(Select id from my_class);
否定语句代码：
Select * from my_studentwhere c_id!=any(Select id from my_class); -- 所有结果（NULL除外）
Select * from my_studentwhere c_id!=some(Select id from my_class);-- 所有结果（NULL除外）
Select * from my_studentwhere c_id !=all(Select id from my_class); -- my_class表中不存在的id，包括null。
3.4行子查询
行子查询：返回的结果是多行多列（一行多列）
需求:要求查询整个学生中，年龄最大且身高是最高的学生。
（1）确定数据源：
Select * from my_student where age =? And heigh = ?;
（2）确定最大的年龄和最高的身高
Select max(age) from my_student;
Select max(height) from my_student;
（3）行子查询：需要构造行元素，行元素由多个字段构成。
3.5表子查询
表子查询：子查询返回的结果是多行多列的二维表，子查询返回的结果是当做二维表来使用的。
需求：找出每一个班最高的一个学生。
（1）确定数据源
select * from my_studnet oder by height desc;
（2）从每个班选出第一个学生
Select * from my_student group by c_id;
代码：
Select * from (select * from my_student order by height desc) as student group by c_id;
3.6 exists子查询
Exists：是否存在的意思，exists子查询就是用来判断某些条件是否满足（跨表），exists是接在where之后，exists返回的结果只有0和1。
4.视图（view）
视图是一种有结构（有行有列）但是没有结果（结构中不真实存放数据）的虚拟表，虚拟表的结构来源不是自己定义，而是从对应的基表中产生（视图的数据来源）。
4.1创建视图
（1）基本语法：
Create view视图名字 as select语句 -- select语句可以是普通查询，连接查询，联合查询或者子查询。
（2）创建单表视图：基表只有一个
创建多表视图：基表有多个
代码：
--视图：单表
create view my_v1 as
select * from my_student;
create view my_v2 as
select * from my_class;
--视图：多表
create view my_v3 as
select s.*,c.c_name,c.room from my_student as s
left join my_class as c
on s.c_id = c.id;
效果：
4.2查看视图
（1）视图是一张虚拟表，表的所有查看方式都适用于视图：
show tables;
Desc视图名;
Show create table视图名;
（2）视图比表还是有一个关键字的区别：view
查看视图的创建语句时可以使用Show create view视图名;
（3）视图一旦创建：系统会在视图对应的数据库文件夹下创建一个对应的结构文件：frm文件。
4.3使用视图
视图的执行其实本质就是执行封装的select语句。
4.4修改视图
视图本身不可修改，但是视图来源来源是可以修改的。
Alter view视图名 as 新的select语句;
4.5删除视图
Drop view视图名;
4.6视图的意义
（1）视图可以接受sql语句，将一条复制的查询语句是在视图进行保存，以后可以直接对视图进行操作。
（2）数据安全：视图操作主要是针对查询的，如果对视图结构进行处理（删除），不会影响基表数据（相对安全）。
（3）视图往往是在大项目中使用，而且是多系统使用。可以对外提供有用的数据，但是隐藏关键（或无用）的数据，保证数据安全。
（4）视图可以对外提供友好型：不同的视图提供不同的数据，对外专门设计。
（5）视图可以更好（容易）的进行权限控制。
4.7视图数据操作
视图的确可以进行数据写操作但是有很多限制
4.7.1新增数据
数据新增就是直接对视图进行数据新增。
（1）多表视图不能新增数据。
（2）可以向单表视图插入数据：但是视图中的字段必须包含”基表中所有不能为空（或没有默认值）的字段”。
（3）单表视图是可以向基表插入数据的。
select * from my_v2;
select * from my_class;
insert into my_v2 values(6,'PHP8888','D306');
4.7.2删除数据
（1）多表视图不能删除数据
（2）单表视图可以删除
4.7.3更新数据
（1）理论上单表视图和多表视图都可更新数据。
（2）更新限制：with check option
如果对视图进行新增的时候，限定了某个字段有限制。那么视图进行数据更新操作时，系统会进行验证：保证更新后，数据依然可以被实体查询出来，否则不让更新。
4.7.4视图算法
（1）视图算法：系统对视图以及外部查询视图的select语句的一种解析方式。
（2）视图算法分为三种：
| Undefined（未定义） | 这不是一种实际使用的算法，是一种推卸责任的算法，告诉系统，视图没有定义算法，系统自己看着办。 |
| Temptable（临时表算法） | 系统应该先执行视图的select语句，后执行外部查询语句 |
| Merge（合并算法） | 系统应该先将视图对应的select语句与外部查询视图的select语句进行合并，然后执行（效率高） |
（4）算法指定：在创建视图的时候
Create algorithm =指定算法 view 视图名字 as select语句;
（5）视图算法选择：如果视图的select语句中包含一个查询子句（五子句），而且很可能顺序比外部的查询语句要靠后一定要使用算法temptable，其他情况可以不使用指定（默认）。
5. 数据备份与还原
（1）备份：将当前已有的数据或记录保留。
还原：将已经保留的数据恢复到对应的表。
（2）为什么要做备份还原？
1）防止数据丢失：被盗，误操作
2）保护数据记录
（3）数据备份还原的方式有很多种：数据表备份，单表数据备份，sql备份，增量备份。
5.1数据表备份
（1）不需要通过sql来备份，直接进入到数据库文件夹复制对应的表结构以及数据文件，以后还原的时候，直接将备份的内容放进去即可。
（2）数据表备份的前提条件：根据不同的存储引擎有不同的区别。
存储引擎：mysq进行数据存储的方式：innodb和myisam（免费）
（3）对比myisam和innodb的数据存储方式
1）innodb：只有表结构，数据全部存储到ibdata1文件中
2）myisam：表，数据和索引分成单独存储
5.2单表数据备份
（1）每次只能备份一张表，只能备份数据（表结构不能备份）
（2）通常的使用：将表中的数据进行导出到文件
（3）备份：从表中选出一部分数据保存到外部的文件中（outfile）
Select */字段列表into outfile ‘文件所在的路径’from 数据源; --前提：外部文件不存在。
（4）高级备份：自己制定字段和行的处理方式
Select */字段列表into outfile ‘文件所在的路径’fields字段处理 lines 行处理 from数据源;
| Fields：字段处理 | |
| Enclosed by | 字段使用什么内容包裹，默认是’’（空字符串） |
| Terminated by | 字段以什么结束，默认是”\t”（tab键） |
| Escaped by | 特殊符号用什么方式处理，默认是‘\\’（反斜杠转义） |
| Starting by | 每行以什么开始，默认是’’（空字符串） |
| Terminated by | 每行以什么结束，默认是”\r\n”（换行符） |
（5）数据还原：将一个在外部的数据重新恢复到表中（如果表结构部不存在，那么sorry）。
Load data infile文件所在路径 into table表名[字段列表] fields字段处理 lines行处理; --怎么备份的怎么还原
5.3 sql备份
（1）备份的是sql语句：系统会对表结构以及数据进行处理，变成对应的sql语句，然后进行备份。还原的时候只有执行sql指令即可（主要是针对表结构）。
（2）备份：mysql没有提供备份指令，需要利用mysql提供的软件：mysqldump.exe。Mysqldump.exe也是一种客户端，需要操作服务器，必须连接认证
（3）基本语法：
Mysqldump/mysqldump.exe -hpup数据库名字 [数据表名字1 [数据表2......]]
代码：表备份
mysqldump -uroot -p mydatabase my_student > D:\server\student.sql
代码：库备份
mysqldump -uroot -p mydatabase > D:\server\mydatabase.sql
（4）sql还原数据：两种方式还原
方案1：使用mysql.exe客户端还原
Mysql.exe/mysql -hPup数据库名字 < 备份目录、
代码：
mysql -uroot -p mydatabase < D:\server\mydatabase.sql
方案2：使用sql指令还原
Source备份所在的目录
代码：
source D:\server\student.sql;
（5）sql备份优缺点：
1.优点：可以备份结构
2.缺点：会浪费空间（额外增加sql指令）
5.4增量备份
指定时间段进行备份，备份数据不会重复，而且所有的操作都会备份（大项目都用增量备份）
扫一扫
342
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
