MySQL 自关联 & 外键

数据库优化 学习笔记

在开始之前，先导入数据。导入时，注意修改 sql 文件中的数据库。

一、自关联

        简单来说，自关联就是表与表自身进行比较查询，即 表A inner join 表A on...

1.1、不用自关联

        假设有三组数据（省，市，区），想要实现由省查市、由市查区，那么一般会想到用三种表分别存储三组数据，然后做连接查询。

	-- 黑龙江省下面的市
	select * from cities c inner join provinces p 
		on c.provinceid=p.provinceid having p.province='黑 龙江省';

问题： 观察两个表，不难发现，它们存储的都是同一类信息（都是地区信息）。那么，能不能把它们合并为一个表，从而减少开销呢 ？

解决： 当然可以，观察两张表发现，citys 表比 provinces 表多一个列 proid，其它列的类型都是一样的。所以可创建表 areas。

• 表 areas 结果：id 、pid 、name 、type
• 表 areas 分为省、市、区三个等级，三个等级分别用 type 表示为 1，2，3
• pid 表示该条记录的上一级的 id ，如 “哈尔滨市” 的上一级为 “黑龙江省” ，“黑龙江省” 的 id 为 12，所以 ”哈尔滨市“ 的 pid 为12.
  
  

1.2、使用自关联查询

	# 查询黑龙江省下面的市
	select * from areas a inner join areas b on a.id=b.pid having a.name='黑龙江';

	# 查询哈尔滨市下面的区
	select * from areas a inner join areas b on a.id=b.pid having a.name='哈尔滨';

---

二、外键

2.1、外键的作用

        引入外键是为了更好的保证数据的参照完整性。

如：
有学生信息表、成绩表两个表
学生表中有 "张三" 的信息, 成绩表中有 "张三" 的成绩信息.

若学生表中, "张三" 的信息记录被删除, 
那么成绩表中有关 "张三" 的信息就成为了 "垃圾数据" 或者 "错误数据".

        因此，为了保证数据的完整性，将两张表之间的数据建立关系，因此就需要在成绩表中添加外键约束。

2.2、添加外键

        引入外键之后，外键列只能插入参照列存在的值，参照列被参照的值不能被删除，这就保证了数据的参照完整性。

• 语法： alert table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名)
  
  

• 验证外键的作用

	insert into student (sid, sname, gid) values(1000, 'juran', 123);

• 删除外键约束

	alter table 表名 drop foreign key 外键名;