[一点笔记]主键与外键-MySQL中的表连接

  关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键。
比如：
  学生表(学号，姓名，性别，班级)
  其中每个学生的学号是唯一的，学号就是一个主键
  课程表(课程编号,课程名,学分)
  其中课程编号是唯一的,课程编号就是一个主键
  成绩表(学号,课程号,成绩)
  成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以 学号和课程号的属性组是一个主键 。

  成绩表中的学号不是成绩表的主键，但它和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键 。

一、定义
  定义主键和外键主要是为了维护关系数据库的完整性。主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键；外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。

  主键：可以用来连接两张数据表，是唯一标识一条记录，不能有重复的，不允许为空，用来保证数据完整性；

  外键：是另一表的主键, 外键可以有重复的, 可以是空值，用来和其他表建立联系用的。所以说，如果谈到了外键，一定是至少涉及到两张表。
例子：

  上面有两张表：部门表（dept）、员工表(emp)。Id=Dept_id，而Dept_id就是员工表中的外键：因为员工表中的员工需要知道自己属于哪个部门，就可以通过外键Dept_id找到对应的部门，然后才能找到部门表里的各种字段信息，从而让二者相关联。所以说，外键一定是在从表中创建，从而找到与主表之间的联系；从表负责维护二者之间的关系。
我们先通过如下命令把部门表和职工表创建好，方便后面的举例：

create table department(            id int primary key auto_increment,            name varchar(20) not null,            description varchar(100));create table employee(            id int primary key auto_increment,            name varchar(10) not null,            gender varchar(2) not null,            salary float(10,2),            age int(2),            gmr int,            dept_id int);

  主键、外键和索引的区别：

     主键 外键 索引 定义 唯一标识一条记录，不能有重复的，不允许为空 表的外键是另一表的主键, 外键可以有重复的, 可以是空值 该字段没有重复值，但可以有一个空值 作用 用来保证数据完整性 用来和其他表建立联系用的 是提高查询排序的速度 个数主键只能有一个一个表可以有多个外键 一个表可以有多个惟一索引

二、使用条件
外键的使用需要满足下列的条件：（这里涉及到了InnoDB的概念）
1. 两张表必须都是InnoDB表，并且它们没有临时表。
注：InnoDB是数据库的引擎。MySQL常见引擎有两种：InnoDB、MyISAM、Memory等，有一些不支持外键，可以通过

show engines;

查看MySQL支出的引擎，默认情况下使用的是InnoDB。
2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。
3. 建立外键关系的对应列必须建立了索引。
4. 假如显式的给出了CONSTRAINT symbol，那symbol在数据库中必须是唯一的。假如没有显式的给出，InnoDB会自动的创建。

MyISAM：不支持外键约束。不支持事务。对数据大批量导入时，它会边插入数据边建索引，所以为了提高执行效率，应该先禁用索引，在完全导入后再开启索引。
InnoDB：支持外键约束，支持事务。对索引都是单独处理的，无需引用索引。

三、添加外键的语法：
有两种方式：
方式一：在创建表的时候进行添加
方式二：表已经创建好了，继续修改表的结构来添加外键

【方式一】在创建表的时候进行添加

[CONSTRAINT symbol] FOREIGN KEY [id] (从表的字段1)REFERENCES tbl_name (主表的字段2)[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}][ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION} 

上面的代码是同一行，中括号里的内容是可选项。

CONSTRAINT symbol：可以给这个外键约束起一个名字，有了名字，以后找到它就很方便了。如果不加此参数的话，系统会自动分配一个名字。
FOREIGN KEY：将从表中的字段1作为外键的字段。
REFERENCES：映射到主表的字段2。
ON DELETE后面的四个参数：代表的是当删除主表的记录时，所做的约定。
RESTRICT(限制)：如果你想删除的那个主表，它的下面有对应从表的记录，此主表将无法删除。
CASCADE（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删掉。
SET NULL：将外键设置为空。
NO ACTION：什么都不做。
注：一般是RESTRICT和CASCADE用的最多。

【方式二】表已经创建好了，继续修改表的结构来添加外键。

ALTER TABLE employee ADD FOREIGN KEY(dept_id) REFERENCES department(id); 

ALTER TABLE employee：在从表employee中进行操作；
ADD FOREIGN KEY(dept_id)：将从表的字段dept_id添加为外键；
REFERENCES department(id)：映射到主表department当中为id的字段。

四、删除外键：（通过sql语句的方式）：

(1)获取外键名：
  如果在命令行中不知道外键的名字，可以通过查看表的定义找出外键的名称：

show create table emp;

  emp为含有外键的表的表名。

（2）删除外键：

alter table emp drop foreign key 外键名; 

五、表连接（join）：

1、内连接：只列出匹配的记录

SELECT … FROM join_table[INNER] JOIN join_table2 [ON join_condition]WHERE where_definition

  只列出这些连接表中与连接条件相匹配的数据行。INNER可以不写，则默认为内连接。[ON join_condition]里面写的是连接的条件。

例子：

select e.name,d.name from employee e inner join department d on e.dept_id=d.id; 

等价于

select e.name,d.name from employee e,department d where e.dept_id=d.id; 

2、外连接：
外连接分类：
  左外连接(LEFT [OUTER] JOIN)
  右外连接(RIGHT [OUTER] JOIN)
  全外连接(FULL [OUTER] JOIN)

SELECT … FROM join_table1(LEFT | RIGHT | FULL) [OUTER] JOIN join_table2ON join_conditionWHERE where_definition 

  不仅列出与连接条件（on）相匹配的行，还列出左表table1(左外连接)、或右表table2(右外连接)、或两个表(全外连接)中所有符合WHERE过滤条件的数据行。一般都是用左连接或者外连接。
其中，[OUTER]部分可以不写，(LEFT | RIGHT | FULL)部分要写其中一个。

左外连接：左表列出全部，右表只列出匹配的记录。
右外连接：右表列出全部，左表只列出匹配的记录。

3、交叉连接：

SELECT … FROM join_table1 CROSS JOIN join_table2; 

  没有ON子句和WHERE子句，它返回的是连接表中所有数据行的笛卡尔积。
  笛卡尔积举例：假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}
其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

等价于：

SELECT … FROM table1, table2; 

例子：

select a.name, b.name from table1 a, table2 b;

4、自连接：参与连接的表都是同一张表。(通过给表取别名虚拟出两张表)

  例子：

select e1.name 员工, e2.name 领导 from table e1 left join table e2 on e1.id=e2.leader;

六、数据库中主键和外键的设计原则：

  主键和外键是把多个表组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。
  必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和外键的结构是这个设计过程的症结所在。一旦将所设计的数据库用于了生产环境，就很难对这些键进行修改，所以在开发阶段就设计好主键和外键就是非常必要和值得的。
主键：
  关系数据库依赖于主键—它是数据库物理模式的基石。主键在物理层面上只有两个用途：
   1. 惟一地标识一行。
   2. 作为一个可以被外键有效引用的对象。
  基于以上这两个用途，下面给出了我在设计物理层面的主键时所遵循的一些原则：
   1. 主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。
   2. 主键应该是单列的，以便提高连接和筛选操作的效率。
  注：使用复合键的人通常有两个理由为自己开脱，而这两个理由都是错误的。其一是主键应当具有实际意义，然而，让主键具有意义只不过是给人为地破坏数据库提供了方便。其二是利用这种方法可以在描述多对多关系的连接表中使用两个外部键来作为主键，我也反对这种做法，理由是：复合主键常常导致不良的外键，即当连接表成为另一个从表的主表，而依据上面的第二种方法成为这个表主键的一部分，然，这个表又有可能再成为其它从表的主表，其主键又有可能成了其它从表主键的一部分，如此传递下去，越靠后的从表，其主键将会包含越多的列了。
   3. 永远也不要更新主键。实际上，因为主键除了惟一地标识一行之外，再没有其他的用途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对用户无意义的原则被违反了。
   注：这项原则对于那些经常需要在数据转换或多数据库合并时进行数据整理的数据并不适用。
   4. 主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
   5. 主键应当有计算机自动生成。如果由人来对主键的创建进行干预，就会使它带有除了惟一标识一行以外的意义。一旦越过这个界限，就可能产生认为修改主键的动机，这样，这种系统用来链接记录行、管理记录行的关键手段就会落入不了解数据库设计的人的手中。

常见的数据库主键选取方式有：

自动增长字段
手动增长字段
UniqueIdentifier
“COMB（Combine）”类型