mysql表和表的关联索引 mysql表关联有几种

概述：
Mysql数据库表中两表之间的关系可以分为以下几种：
1、一对一：
人—身份证号 丈夫–妻子 学生–电脑
2、一对多：
商品分类----商品 主人----宠物 房叔----房子 学生----手机
3、多对多：
学生----课程 顾客----商品 商品-----订单

**一对一**
特点：
1、数据库表中存在两张表，其中一张表的主键是另外一张表的外键，通过外键进行关联
2、外键所在的表叫从表，另外一张表叫主表
3、添加数据的时候需要先添加主表数据，后添加从表数据
4、删除数据的时候需要先删除从表数据，后删除主表数据
小结：
主表中数据和从表中数据通过主键值进行关联，也就是从表中数据的id和主表中数据的id是一一对应。
设置外键的语法：

```sql
ALTER TABLE husbands  #修改丈夫表
ADD CONSTRAINT  fk_husband_wife  #增加外键的名称
FOREIGN KEY (hid)  #设置外键的字段
REFERENCES wifes(wid); #外键关联的主表以及主表的关联字段

代码示例：

#创建数据库

CREATE DATABASE mysql0202;

#创建丈夫表

CREATE TABLE husbands(
hid  INT PRIMARY KEY AUTO_INCREMENT,
hname VARCHAR(30)
);

#创建妻子表

CREATE TABLE wifes(
wid INT PRIMARY KEY AUTO_INCREMENT,
wname VARCHAR(30)
);

#设置外键约束

ALTER TABLE husbands  #修改丈夫表
ADD CONSTRAINT  fk_husband_wife  #增加外键的名称
FOREIGN KEY (hid)  #设置外键的字段
REFERENCES wifes(wid); #外键关联的主表以及主表的关联字段

#添加数据：先添加妻子表数据

INSERT INTO wifes (wname) VALUES("李丽1");
INSERT INTO husbands(hname) VALUES("王五");

#删除数据

DELETE FROM husbands WHERE hid=2;
DELETE FROM wifes WHERE wid=2;

一对多
特点：
1、在两个表中，一张是多方表，一张是一方表，通常设置外键在多方表中，需要单独设置一个字段来作为外键，外键和主表的主键进行关联。
2、外键所在的表叫从表，外键关联的表叫主表
3、增加数据的时候需要先增加主表数据，后增加从表数据
4、删除的时候先删除从表数据后删除主表数据（如果删除主表数据，需要确认当前主表下面没有和从表数据有关联）

代码示例：

/*
2、一对多关系
*/
#创建商品分类表

CREATE TABLE sorts(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(50)
);

#创建商品表

CREATE TABLE prods(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(60),
sort_id INT  #外键 和分类的主键关联
);

#设置商品和分类的外键约束

ALTER TABLE prods 
ADD CONSTRAINT fk_prods_sort
FOREIGN KEY (sort_id)
REFERENCES sorts(sid);

#添加商品分类信息

INSERT INTO sorts(sname)VALUES("电脑");

#sort_id==分类表sid值
此时无法执行，因为主表中没有id=5的

INSERT INTO prods(pname,sort_id)VALUES("耐克",5);

#删除数据，需要删除从表中的数据再删主表的，像这一句主表的id中，从表没有数据与之关联，因此可以删

DELETE FROM sorts WHERE sid=2;

2.3多对多
特点：
1、有两张表，还需要一张中间表，中间表用来存其他两张表的外键。多对多关系中，中间表是从表，其他两个表是主表。
2、增加数据的时候需要先增加主表，后增加中间表数据
3、删除先删除从表也就是中间表数据，在删除主表数据。如果要删除主表数据需要先确定中间表中有没有关联的数据存在，如果有不能删除。

代码示例：

/*
3、多对多关系
*/
#创建学生表
CREATE TABLE students(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(30)
);
#创建课程表
CREATE TABLE courses(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(50)
);
#创建一个中间外键表
CREATE TABLE fks(
fid INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT, #关联学生的外键
course_id INT #关联课程的外键
);
#设置外键约束
ALTER TABLE fks
ADD CONSTRAINT fk_fks_stu
FOREIGN KEY (stu_id)
REFERENCES students(sid);
ALTER TABLE fks
ADD CONSTRAINT fk_fks_course
FOREIGN KEY (course_id)
REFERENCES courses(cid);
#添加数据
INSERT INTO students (sname)VALUES(“小黑”);
INSERT INTO courses (cname)VALUES(“web”);
INSERT INTO fks (stu_id,course_id)VALUES(2,3);
#删除数据
DELETE FROM students WHERE sid=1;

小结：
在数据库表关系中可以通过设置外键的形式将表进行关联，这种方式叫外键约束。
但是，在我们进行项目开发的时候，通常不建议使用这种外键的约束。一般我们建议使用java代码的方式来实现表之间的关联关系。

两表关联查询

概述：
在数据库中经常会出现多张表关联查询的问题，通常我们可以将多张表进行分解，最终其实就是两表的关联查询问题，常见的两个表关联查询有两种情况：内连接查询和外连接查询。

3.1内连接查询

内连接查询：相当于获取两个表的交集。

语法：
a.使用where关键字：
Select a.,b. from a ,b where a.外键=b.主键；
b.使用inner…join：
Select a.* ,b.* from a inner join b on a.外键=b.主键
小结：
通过内连接查询的是两个表的交集数据。
代码示例：

#创建商品分类表
CREATE TABLE sorts(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(50)
);
#创建商品表
CREATE TABLE prods(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(60),
sort_id INT #外键 和分类的主键关联
);
#设置商品和分类的外键约束
ALTER TABLE prods
ADD CONSTRAINT fk_prods_sort
FOREIGN KEY (sort_id)
REFERENCES sorts(sid);
#添加商品分类信息
INSERT INTO sorts(sname)VALUES(“电脑”);
#sort_id==分类表sid值
INSERT INTO prods(pname,sort_id)VALUES(“耐克”,4);

#查询分类表和商品表中每个分类信息以及对应的商品信息
#where
SELECT * FROM sorts s,prods p
WHERE s.sid=p.sort_id;
#inner join
SELECT * FROM sorts s
INNER JOIN prods p
ON s.sid=p.sort_id;

3.2外连接查询

外连接：相当于获取两个表数据的交集以及其中一张表的并集。

分类：

a.左外链接：

语法 Select * from a left join b on a.主键=b.外键

特点：left左边的表数据会全部显示无论是否有关联数据

b.右外连接：

语法 Select * from a right join b on a.主键=b.外键

特点：right右边的数据会全部显示无论是否有关联数据

代码示例：

/*
外连接查询测试
*/
#右外连接：查询所有商品分类信息以及分类下面的商品信息

SELECT * FROM prods p 
RIGHT JOIN sorts s 
ON p.`sort_id`=s.`sid`;

#左外连接:查询所有商品分类信息以及分类下面的商品信息

SELECT * FROM sorts s
LEFT JOIN prods p 
ON s.`sid`=p.`sort_id`;

#查询所有商品信息以及商品对应的分类信息
SELECT * FROM prods p
LEFT JOIN sorts s
ON p.`sort_id`=s.`sid`;

SELECT * FROM sorts s
RIGHT JOIN prods p
ON s.`sid`=p.`sort_id`;

最后再说一下关于数据库的备份
备份：将文件或者数据资料进行拷贝保存到一个指定的地方。在mysql数据库备份就是将数据库信息存储到一个外部的本地磁盘文件中。
恢复：
将备份的文件，重新导入到数据库中。
使用可视化界面可以很方便的操作。
当然使用dos命令行操作也是可以的：
备份：

`mysqldump -uroot -proot mysql0202 > C:\JAVAWEB\mysql0202.sql`

lmysqldump是进行备份的命令，然后输入用户名密码，>代表将数据备份到那个位置。

恢复：需要先创建好目标数据库mysqldemo01

mysql -uroot -proot mysqldemo01 < C:\JAVAWEB\mysql0202.sql