mysql学习三之外键、联合查询、子查询、视图、数据备份还原

连接查询
将多张表（可以大于2张表）进行记录的连接（按照某个指定的条件进行数据拼接）
最终结果是：记录数有可能变化，字段数一定会增加（至少两张表的合并）

连接查询的意义：在用户查看数据的时候，需要显示的数据来自多张表。

连接查询：join 使用方式 左表 join 右表
左表：在join左边的表
右表：在join右边的表

连接查询分类
SQL中将连接查询分为4类：内连接、外连接、自然连接和交叉连接


交叉连接
从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配；
匹配一定保留（没有条件匹配），而连接本身字段就会增加（保留），最终
形成的结果是笛卡尔积。

基本语法： 左表 cross join 右表;  等价于 from 左表,右表

--交叉连接
select * from my_student cross join my_class;
没有意义，应该尽量避免
存在的价值：保证连接这种结构的完整性。


内连接
inner join  其中inner关键字可以省略，从左表中取出每一条记录，去
右表中与所有的记录进行匹配；匹配必须是某个条件在左表中与右表中相同
最终才会保留结果，否则不保留。

基本语法： 左表 [inner] join 右表 on 左表.字段 = 右表.字段;
on表示连接条件；条件字段就是代表相同的业务含义

select * from my_student inner join my_class on my_student.c_id = my_class.id;

字段别名以及表别名的使用：在查询数据的时候，不同表有同名字段，这个时候需要别名
才能区分，表名太长可用别名

select * from my_student as s inner join my_class as c on s.c_id = c.id;

内连接可以没有连接条件；没有on之后的内容，这个时候系统会保留所有结果，为笛卡尔积

select * from my_student as s inner join my_class as c where s.c_id = c.id;
内连接可能用where代替on（where没有on效率高）；


外连接
以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上
条件，最终都会保留；能匹配，正确保留；不能匹配，其他表的字段都置空NULL。

外连接分为两种：是以某张表为主：有主表
left join:左外连接（称为左连接），以左表为主表
right join:右外连接（称为右连接），以右表为主表
基本语法： 左表 left/right join 右表 on 左表.字段 = 右表.字段

--左连接
select s.*,c.name as c_name,c.room from my_student as s left join my_class as c 
	on s.c_id=c.id;


--右连接
select s.*,c.id as c_id,c.name as c_name,c.room from my_student as s right join my_class as c 
	on s.c_id=c.id;

虽然左连接和右连接有主表差异，但是显示的结果：左表的数据在左边，右表的数据在右边


自然连接
就是自动匹配连接条件；系统以字段名字作为匹配模式，同名字段作为条件，多个同名字段，
都作为条件。

自然连接：可以分为自然内连接和自然外连接

自然内连接 左表 natural join 右表;
--自然内连接
select * from my_student natural join my_class ;
自然连接自动使用同名字段作为连接条件；连接之后合并同名字段

--自然左内连接
select * from my_student natural left join my_class;



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

增加外键
外键可以在创建表的时候或者创建表之后增加（但是要考虑数据的问题）
一张表可以有多个外键

方案一：在创建表的时候增加外键：在所有的表字段之后；使用
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;
外键要求字段本身必须先是一个索引（普通索引），如果字段本身没有索引，外键会先创建一个索引，
然后才会创建外键本身。


方案二：在新增表之后增加外键：修改表结构
alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段)
create table my_foreign2(
id int primary key auto_increment,
name varchar(20) not null comment '学生名称',
c_id int comment '班级id'
)charset utf8;

alter table my_foreign2 add constraint student_class_1 foreign key(c_id) references my_class(id);

      
修改外键&删除外键
外键不可修改，只能先删除后新增

删除外键
alter table 表名 drop foreign key 外键名; --一张表中可以有多个外键，名字不能相同

--删除外键
alter table my_foreign1 drop foreign key my_foreign1_ibfk_1;
外键删除不能通过查看表结构体现，应该通过查看创建语句体现。


外键的作用

外键默认的作用有两点：一个对父表，一个对子表（外键字段所在的表）
对子表约束：子表数据进行写操作的时候，如果对应的外键字段在父表找不到对应的匹配，那
么操作会失败（约束了子表的数据操作）。

--插入数据：外键字段在父表中不存在
insert into my_foreign2 values(null,'外键',5); --不能新增或者更新一个子行

对父表约束：父表数据进行写操作（删和改：都必须涉及到主键本身），如果对应的主键在
子表中已经被数据所引用，那么就不允许操作

insert into my_foreign2 values(null,'外键1',1);
insert into my_foreign2 values(null,'外键2',2);
insert into my_foreign2 values(null,'外键3',3);

--更新父表记录
update my_class set id=5 where id=1;  --更新失败

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

--插入数据
insert into my_foreign1 values(null,'无外键',5);
--数据已经存在，在新增外键的时候，无法改变不能匹配数据事实，添加外键失败
alter table my_foreign1 add foreign key(c_id) references my_class(id);

外键约束
所就是指外键的作用：之前所讲的外键作用：是默认的作用；其实可以通过对外键的需求，进行定制
操作。

外键约束有三种约束模式：都是针对父表的约束
	district:严格模式（默认的），父表不能删除或者更新一个已经被子表数据引用的记录
	cascade:级联模式：父表的操作，对应子表关联的数据也跟着操作
	set null:置空模式：父表的操作之后，子表对应的数据（外键字段）被置空。

通常的一个合理的做法（约束模式）：删除的时候子表置空，更新的时候子表级联操作
指定模式的语法
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;
	

--插入数据
insert into my_foreign3 values(null,'齐备',1),(null,'苏打',1),(null,'阿哥',1),
(null,'疑心',2),(null,'导游',2);

--更新父表主键,子表会被级联更新
update my_class set id=6 where id=1;
--删除父表主键
delete from my_class where id=2;

更新操作是级联更新，删除操作是置空
删除置空的前提条件：外键字段允许为空。



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

基本语法：
多条select语句构成：每一条select语句获取的字段数必须严格一致（但是字段类型无关）

select 语句1
	union [union 选项]
	select 语句2...

union 选项 与select 选项一样有两个：
	all：保留所有（不管重复）
	distinct: 去重（整个重复）：默认的


--联合查询，默认去重
select * from my_class 
		union 
		select * from my_class;
--联合查询，不去重
select * from my_class 
		union all
		select * from my_class;
	
联合查询只要求字段一样，跟数据类型无关。
select id,name,room from my_class 
		union all
		select name,id,number from my_student;

意义：
联合查询的意义分为两咱：
1、查询同一张表，但是需求不同：如查询学生信息，男生身高升序，女生身高降序

2、多表查询：多张表的结构是完全一样的，保存的数据（结构）也是一样的。

--错误
select * from my_student where sex='男'  order by height asc
	union
	select * from my_student where sex='女' order by height desc ;
在联合查询中order by不能直接使用，需要要()，括起来
(select * from my_student where sex='男'  order by height asc)
	union
	(select * from my_student where sex='女' order by height desc );
若要order by 生效：必须搭配limit.limit使用限定

(select * from my_student where sex='男'  order by height asc limit 9999)
	union
	(select * from my_student where sex='女' order by height desc limit 9999);
	

子查询
查询是在某个查询结果之上进行的（一条select语句包含另一条select语句）

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

按位置分类：子查询（select语句）在外部查询（select语句）中出现的位置
	from 子查询：子查询跟在from之后
	where子查询：子查询跟在where条件中
	exists子查询：子查询出现在exists里面

按结果分类：根据子查询得到的数据进行分类（理论上任何一个查询得到的结果为二维表）
	标量子查询：子查询得到的结果是一行一列
	列子查询：子查询得到的结果是一列多行
	行子查询：子查询得到的结果是多列一行（可以多行多列）
		上面几个出现的位置都是在where之后
	表子查询：子查询得到的结果是多行多列（出现的位置是在from之后）

标量子查询

需求：知道班级名字为php0812的学生
select * from my_student where c_id= (select id from my_class where name='PHP0812');


列子查询
需求：查询所有在读班级的学生

1、确定数据源：学生
select * from my_student where c_id in(?) ;
2、确定有效班级的id，所有班级id
select id from my_class;

列子查询
select * from my_student where c_id in(select id from my_class) ;

列子查询返回的结果会比较：一列多行，需要使用in作为匹配条件
select * from my_student where c_id =any(select id from my_class) ;
select * from my_student where c_id =some(select id from my_class) ;
select * from my_student where c_id =all(select id from my_class) ;

行子查询 
行子查询：返回的结果可以是多行多列

需求：要求查询整个学生中，年龄最大且身高是最高的学生。
1、确定数据源 
select * from my_student where age=? and height=?;
2、确定最大的年龄和最高的身高
select max(age),max(height) from my_student;

select * from my_student where 
	age =(select min(age) from my_student)
and 
	height=(select max(height) from my_student);
--(age,height) 称为行元素
select * from my_student where (age,height) = (select min(age),max(height) from my_student);


表子查询
子查询返回的结果是多行多列的二维表；子查询返回的结果是当做二维表来使用

需求：找出每个班中最高的一个学生
1、确定数据源 ；先将学生按照身高进行降序排序
select * from my_student order by height desc;

2、从每个班选出第一个学生
select * from my_student group by c_id; --每个班选出第一个学生

表子查询：from子查询；得到的结果作为from的数据源
select * from (select * from my_student order by height desc) as stu
	group by c_id;

exists子查询 
是否存在的意思，exists子查询就是用来判断某些条件是否满足（跨表），
exists是接在where之后；exists返回的结果只有0和1
--返回100
select exists(select * from my_student);
--返回0
select exists(select * from my_student where id=0);

需求：返回所有的学生；前提条件是班级存在
1、确定数据源 
select * from my_student where ?;
2、确定条件是否满足
exists (select * from my_class);  --是否成功

select * from my_student where exists(select * from my_class where id=1);

select * from my_student where exists(select * from my_class where id=9);


视图
是一种有结构（有行有列）但是没有结果（结构中不真实存放数据）的虚拟表。虚拟表的
结构来源不是自已定义，而是从对应的基表中产生。

创建视图
基本语法：
create view 视图名字 as select 语句; --select语句可以是普通查询；可以是连接查询，可以是联合查询等

--创建单表视图
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.id cid,c.name cname,c.room from my_student as s left join my_class c on s.c_id=c.id;

注意：创建视图时不能有同名字段

查看视图
视图是一张虚拟表： 表的所有查看方式都适用 show tables /desc , 

--查看视图创建语句
show create view my_v3\G;

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


使用视图
使用视图主要是为了查询：将视图当做表一样使用
视图的执行：其实本质就是执行封装的select语句

select * from my_v1;

修改视图
视图本身不可修改，但是视图的来源是可以修改的。
修改视图：修改视图本身的来源语句（select语句）

alter veiw 视图名字 as 新的名字 select 语句;

alter view my_v1 as 
select id,name,age,sex,height,c_id from my_student;


删除视图
drop view 视图名字;

create view my_v4 as select * from my_student;

drop view my_v4;

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


视图数据操作

数据新增
直接对视图进行数据新增

1、多表视图不能新增数据
2、可以向单表视图插入数据；但是视图中包含的字段必须包含所有不允许为空的字段
3、视图可以向基表插入数据


删除数据
1、多表视图不能删除数据
2、单表视图可以删除数据


更新数据
理论上单表视图和多表视图都可以

更新限制：with check option; 如果对视图在新增的时候，限定了某个字段有限制；那么在
对视图进行数据更新操作时，系统会进行验证：要保证更新之后，数据依然可以被实体查询出
来，否则不让更新。

--视图 ：age字段限制更新
--表示视图的数据来源都是年龄大于30岁；where age>30决定
--with check option：决定通过视图更新的时候，不能将已经得到的 数据age>30的改成小于30的
create view my_v4 as 
select * from my_student where age >30 with check option;


--将视图可以查到的数据改成<30
update my_v4 set age=23 where id=1;

--可以修改数据让视图可以查到
update my_v4 set age=33 where id=1;


--获取所有班级中最高的一个学生
create view my_v5 as
   select * from my_student order by height desc;

select * from my_v5 group by c_id;

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

视图算法分为三种
	undefined :未定义（默认），这不是一种实际使用算法
	temptable:临时表算法；系统先执行视图的select语句，后执行外部查询语句
	merge:合并算法，系统应该先将视图对应的select语句与外部查询视图的select
	语句进行合并，然后执行(效率高:常态);

算法指定：在创建视图的时候
create ALGORITHM=指定算法 view 

create ALGORITHM=temptable view my_v7 as
	select * from my_student order by height desc;
select * from my_v6 group by c_id;


数据备份与还原
备份：将当前已有的数据或者记录保留
还原：将已经保留的数据恢复到对应的表中

为什么要做备份还原？
1、防止数据丢失；被盗；误操作
2、保护数据记录

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

数据表备份 
不需要通过SQL来备份；直接进入到数据库文件夹复制对应的表结构及数据文件，以
后还原的时候，直接将备份的内容放进去即可。
数据表备份有前提条件：根据不同的存储引擎有不同的区别。

存储引擎：mysql进行数据存储的方式：有是innodb和Myisam

对比myisam和innodb：数据存储方式：
	innodb:只有表结构，数据全部存储到ibdata1文件中
	myisam:表、数据、索引全部单独分开存储
	
--创建myisam表
create table my_myisam(
	id int,
	name varchar(20)
)charset utf8 engine=myisam;

这种文件备份通常适用于myisam存储引擎，直接复制三个文件即可，然后放到对应
的数据库下即可以使用。innodb可以复制过来，但是不能使用。

单表数据备份 

每次只能备份一张表，只能备份数据（表结构不能备份）

通常的使用，将表中的数据时行导出到文件

备份： 从表中选出一部分数据存到外部文件中
--前提外部文件不存在
select */字段列表 into outfile 文件所在路径 from 数据源 

--单表数据备份 
select * into outfile 'd:/zdata_student.txt' from my_student ;

高级备份：自己制定字段和行的处理方式
select */字段列表 into outfile 文件所在路径 fields 字段处理 lines 行处理 from 数据源;

fields字段处理：
	enclosed by:字段使用什么内容包裹，默认''，空字符串
	teminated by:字段以什么结束，默认是\t,tab键
	escaped by :特殊符号用什么方式处理，默认是'\\',使用\转义

lines行处理：
	starting by :每行以什么开始，默认是''，空字符串。
	terminated by:每行以什么结束，默认是'\r\n'

--指定备份处理方式
--使用|分隔字段 --字段处理 
select * into outfile 'd:/zdata_student.txt' 
	fields enclosed by '"' 
	terminated by '|'       
	lines 
	starting by 'START:'
FROM MY_STUDENT;


数据还原
将一个在外部保存的数据重新恢复到表中（如果表结构不存在，那么不能恢复）

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

load data  infile 'd:/zdata_student.txt' into table my_student fields enclosed by '"' 
	terminated by '|' lines starting by 'START:';
	
	
SQL备份 
备份的是SQL语句；系统会对表结构以及数据进行处理，变成对应的SQL语句，然后进行备份，
还原的时候只要执行SQL指令即可（主要是针对表结构）

备份： mysql没有提供备份指令；需要利用mysql提供的软件 ：mysqldump.exe

mysqldump.exe也是一种客户端，需要操作服务器，必须要连接认证

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

--SQL备份 
mysqldump -uroot -proot test my_student > d:/zdata_student.sql 

--整库备份
mysqldump -uroot -proot test > d:/zdata_student.sql 

SQL数据还原：两种方式还原
1、使用mysql.exe客户端还原
mysql -hpup 数据库名字 < 备份文件目录

mysql -uroot -proot test < d:/zdata_student.sql 

2、使用SQL指令还原
source 文件所在路径;

source d:/zdata_student.sql ;

SQL备份优缺点
1、优点：可以备份结构
2、缺点：会浪费空间（额外的增加SQL指令）

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

指定时间段开始进行备份、备份数据不会重复，而且所有的操作都会备份（大项目都用增量备份）