分析用外键消除数据冗余过程

1，先来创建一个简单的表（代码在Navicat中进行）

create table user_info(
  id char(36) primary key,
  user_name varchar(30) not null,
  password varchar(30) not null,
  real_name varchar(8),
  mobile char(11),
  address varchar(150)
);

insert into user_info (id,user_name,password,real_name,mobile,address) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456','张三','18920120206','河南安阳');
insert into user_info (id,user_name,password,real_name,mobile,address) values ('cc95772b-75a2-4702-bd99-4c3b0322d606','fuliuqingfeng','123456','李四','18617297545','北京海淀');
insert into user_info (id,user_name,password,real_name,mobile,address) values ('c63028fd-cf8d-4dac-a278-b5cc8fd61e3c','fuliuqingfeng','123456','王五','17694976949','山西大同');
#我们看到，上述表中添加的语句password和user_name出现重复，数据冗余，且如果个人不变信息越多，冗余越大（如果用户还有身份证号等其他不变信息也是冗余），怎么消除冗余，先试试下边代码。

2，创建两个表，两个表有联系，是同一个用户的，将用户固定（不可变）信息写到一个表，与用户有关的其他信息写到另一个表。

create table user_info(
  id char(36) primary key,
  user_name varchar(30) not null,
  password varchar(30) not null
)
insert into user_info (id,user_name,password) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456');

#用user_info_id将两个表联系在一起，就可以表示下边信息是同一个用户的信息；

create table address(
  id char(36) primary key,		#此id是与real_name关联的，用id来标示不同的real_name，此id不可省：举个例子；如果用user_info_id买东西，会给三个人都买，用id就可以给不同的人买。
  user_info_id char(36),
  real_name varchar(8) not null,
  mobile char(11) not null,
  address varchar(150) not null
)

#先添加三条数据
insert into address (id,user_info_id,real_name,mobile,address) values ('bfb9472a-7911-4e6f-a479-3b719454ebab','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','张三','18920120206','河南安阳');
insert into address (id,user_info_id,real_name,mobile,address) values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','李四','18617297545','北京海淀');
insert into address (id,user_info_id,real_name,mobile,address) values ('30b8584b-aa6a-4516-a623-03f487058586','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','王五','17694976949','山西大同');

上述所有的操作完成之后我们来执行如下两个代码

1）
delete from user_info where id='51b28fe1-4ebf-41ac-a17b-d5e276861fd0'	

运行代码如下

#得到结果：用这个删除语句之后会删掉user_info中的数据，但是address中的数据依然存在，但这些数据是多余的了，不能保证数据完整性。

接着看第二个代码

2）
insert into address (id,user_info_id,real_name,mobile,address) values ('30b8584b-aa6a-4516-a623-03f487058587','51b28fe1-4ebf-41ac-a17b-d5e276','王五','17694976949','山西111大同');

运行结果如下

#数据会添加成功，但是此数据不是fuliuqingfeng用户的，不能保证数据完整性
#此时只是逻辑上的外键

#怎么解决保证数据完整性，不能添加不是fuliuqingfeng用户的信息，也不能随意删除用户但是用户信息却保留了。

3，添加外键将两个表连接起来，解决了前边两个情况中存在的问题

create table user_info(
  id char(36) primary key,
  user_name varchar(30) not null,
  password varchar(30) not null
)
insert into user_info (id,user_name,password) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456');

create table address(
  id char(36) primary key,
  user_info_id char(36),
  real_name varchar(8) not null,
  mobile char(11) not null,
  address varchar(150) not null,
  foreign key(user_info_id) references user_info(id)	#foreign key是添加外键语句，(user_info_id)表示address中的user_info_id是外键，reference 表示外键出处，user_info(id)，user_info表中的id
)

#先来添加三条数据
insert into address (id,user_info_id,real_name,mobile,address) values ('bfb9472a-7911-4e6f-a479-3b719454ebab','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','张三','18920120206','河南安阳');
insert into address (id,user_info_id,real_name,mobile,address) values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','李四','18617297545','北京海淀');
insert into address (id,user_info_id,real_name,mobile,address) values ('30b8584b-aa6a-4516-a623-03f487058586','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','王五','17694976949','山西大同');

上述所有的操作完成之后我们来执行如下两个代码

1）	
delete from user_info where id='51b28fe1-4ebf-41ac-a17b-d5e276861fd0'	

运行结果如下

#因为在address表中使用了user_info表中的id，在address表中是外键，所以不能按照外键来删除数据。

delete from address where id='30b8584b-aa6a-4516-a623-03f487058586'	
#可以在address表中用id删除数据

接着看第二个代码

2）
insert into address (id,user_info_id,real_name,mobile,address) values ('30b8584b-aa6a-4516-a623-03f487058587','51b28fe1-4ebf-41ac-a17b-d5e276','王五','17694976949','山西111大同');

运行结果如下

#此处user_info_id和fuliuqingfeng不同，此时因为有了外键，所以添加不了，即不可以添加废数据。保证了数据完整性。

说明：第三种方案为user_info_id添加了外键，指向user_info表的主键，该约束起到了保护数据完整性的作用：如果删除的用户信息id已经在address表中使用，则该条数据无法删除；无法向address表中添加用户id不存在的地址信息。

补充：SQL语句按其功能分为4类：
数据定义语言（DDL Data Definition Language） ：创建、修改或删除数据库中表、视图、索引等对象的操作，常用命令为create、alter和drop；
数据查询语言（DQL Data Query Language） ：按照指定的组合、条件表达式或排序检索已存在的数据库中数据，不改变数据库中数据，常用命令为select；
数据操纵语言（DML Data Manipulation Language） ：向表中添加、删除、修改数据操作，常用命令有insert、update和delete；
数据控制语言（DCL Data Control Language） ：用来授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视等操作，常用命令有GRANT、REVOKE、COMMIT、ROLLBACK；