WEB：Mysql 备份与多表设计 - 04

 mysql数据库的备份与恢复

备份数据库:

cmd窗口中使用mysqldump 备份数据库

mysqldump -u root -p db_name > c:/xxx.sql

例如：mysqldump -u root -p mydb2 > c:/mydb2.sql

恢复数据库:

方式1:cmd窗口中使用mysql命令 恢复数据库,注意,只能恢复数据库中的数据,不能恢复数据库本身!所以要提前建好数据库.

mysql -u root -p db_name < c:/xxx.sql

例如：mysql -u root -p mydb2 < c:/mydb2.sql

方式2:在mysql命令下,source xxx.sql,这个命令的作用就是,在当前位置执行sql文件中的所有的sql.首先新建出数据库,进入数据库,在source执行备份的sql文件即可。

例如：source c:/xxx.sql

注意：如果在cmd窗口下，执行出现“拒绝访问”提示，用管理员打开cmd窗口即可。

外键约束

外键概念

数据库用表来保存数据，但是现实中，数据和数据之间往往具有一定的关系，在设计表时应该如何来保存这种关系呢？我们来看如下图。

一个部门可以具有多个员工，而一个员工只能属于一个部门。对于这种类型的关系我们称之为1对多的关系。

那么我们该如何保存他们之间的关系呢？

可不可以在dept表中设计一张表记录部门中员工的信息呢？

我们发现这是不可行的，因为一个部门中可能有多个员工，这就导致emp_id中一个字段可能需要保存多个值，这是不可行的。

那么反过来，可否在emp表保存员工所属部门id呢？

这种方式是可行的，因为一个员工只能属于一个部门，dept_id中只需存储一个值即可。

虽然我们设计了这样的字段保存两表之间的关系，但是数据库并不知道这样的关系。我们可以设想，如下的情况。

“公司老总发现科技部不挣钱，决定解散科技部”，对应到数据库时，就是如下的sql

delete from dept where id = 004；

正确的执行后，数据库中的数据变为如下

我们发现“555 郭德纲”人还在，所属部门却没有了，数据之间的对应关系出现了问题，我们称为违反了数据库的“完整性”。

之所以产生这样的问题，是因为虽然我们知道dept_id要参考dept表的id列，但是数据库并不知道，所以在删除数据造成违反“不完整”性的情况下数据库没有任何提示。

为了防止这样的问题产生，可以通过外键明确的通知数据库维系这种关系。

一旦任何操作违反了这种关系，数据库会阻止并报错。

​​​​​​​建表时增加外键：

foreign key(ordersid) references orders(id)

​​​​​​​修改方式增加外键：

alter table book add [constraint FK_BOOK] foreign key(pubid) references pub_com(id) [on delete restrict] [on update restrict];

可以明确指定外键的名称，如果不指定外键的名称,mysql会自动为你创建一个外键名称。

RESTRICT : 只要本表格里面有指向主表的数据， 在主表里面就无法删除相关记录。

CASCADE : 如果在foreign key 所指向的那个表里面删除一条记录，那么在此表里面的跟那个key一样的所有记录都会一同删掉。

​​​​​​​删除外键

alter table 表名 drop foreign key 外键名

​​​​​​​实验外键

略

多表设计

多表设计概念

在讲解外键时，我们提到了表和表之间往往存在着一定的关系，那么除了上述的1对多关系外还有哪些种关系呢？又如何设计外键来保存这些不同的关系呢？下面，我们一起来讨论表和表中的三种关系。

​​​​​​​三种关系及外键设计

一对一表和表之间的关系分为三种，分别是1对1、1对多、多对多，下面就用具体的例子讲解这三种关系

班级表和教室表之间的关系，一个班级只能在一个教室上课，一个教室也只能放下一个班级，这种关系就称为一对一的关系，对于一对一的关系，可以在任意一方保存另一方的主键作为外键保存两表之间的关系。

​​​​​​​一对多

学生表和班级表的关系，一个班级可以有多个学生而一个学生只能属于一个班级，这种关系称为一对多的关系，对于一对多的关系，可以在多的一方设计外键保存一的一方的主键作为外键保存两表之间的关系。

​​​​​​​多对多:

学生表和教师表的关系，一个学生可以由多个老师教授，一个老师也可以教授多个学生，这种关系称为多对多的关系，对于多对多的关系，需要设计一张第三方关系表保存两张表主键之间的对应关系。

多表查询

多表查询概念

表和表之间往往是存在关系的，而有时我们需要的数据在多张表中，如何跨着多张表查询数据呢?这就涉及到多表查询了

准备数据：

create table dept(

id int primary key auto_increment,

name varchar(20)

);

insert into dept values

(null,'财务部'),(null,'人事部'),(null,'科技部'),(null,'销售部');

create table emp(

id int primary key auto_increment,

name varchar(20),

dept_id int

);

insert into emp values

(null,'张飞',1),(null,'关羽',2),(null,'刘备',3),(null,'赵云',5);

需求：查询所有部门名称和对应的员工名称。

多表查询设计

笛卡尔积查询:

这是一种最原始的查询方式，其实就是两张表相乘的结果,如果左表有m条记录,右表有n条记录,则查询出来m*n条记录.虽然查出数据了,但是其中包含了大量错误的数据,所以这种查询我们通常不使用。

select * from dept,emp;

​​​​​​​内连接查询

内连接查询:只查询左边表有且右边表也有的数据,本质上是依据外键关系，在笛卡尔积查询的基础上过滤出正确的数据。通过观察结果，我们发现结果中只有两边都有对应数据的数据才能被查出来。

select * from dept ,emp where dept.id = emp.dept_id;

select * from dept inner join emp on dept.id = emp.dept_id;

​​​​​​​外连接查询:

内连接查询只能查找出两边表都有对应数据的数据，如果两边冗余的数据也需要查询时该怎么办呢？

(1)左外连接查询:在内连接的基础上增加上左边表有而有边表没有的记录

select * from dept left join emp on dept.id = emp.dept_id;

#其中，left join 也可以写成 left outer join;

(2)右外连接查询:在内连接的基础上增加上右边表有而左边表没有的记录

select * from dept right join emp on dept.id = emp.dept_id;

#其中，right join 也可以写成 right outer join;

(3)全外连接查询:在内连接的基础上增加左边表有而右边表没有的记录和右边表有而左边表没有的记录

#mysql 不支持全外连接，但是其他的数据库支持，比如SQL Server

select * from dept full join emp on dept.id = emp.dept_id;

#我们可以在mysql中使用union关键字模拟全外连接

select * from dept left join emp on dept.id = emp.dept_id

union

select * from dept right join emp on dept.id = emp.dept_id;

#其中，full join 也可以写成 full outer join;