MySQL数据库多表操作-tutorial mysql-php.cn

一、外键

1. 什么是外键：外键是指引用另一个表中的一列或多列，被引用的列应具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的连接。

Student表中的gid是学生所在班级id，是引入了grade表中的主键id。那么gid就可以作为表student的外键。被引用的表，即表grade是主表；引用外键的表，即表student是从表，两个表是主从关系。表student用gid可以连接表grade中的信息，从而建立了两个表数据之间的连接。

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

2. 为表添加外键约束

想要真正连接两个表的数据，就需要为表添加外键约束。为表添加外键约束的语法格式如下：alter table 表名 add constraint FK_ID foreign key(外键字段名) references 外键表名（主键字段名）

在为表添加外键约束时，有些需要注意的地方，如下所示：

（1）建立外键的表必须是InnoDB型，不能是临时表。因为在MySQL中只有InnoDB类型的表才支持外键；

（2）定义外键名时，不能加引号，如constraint ‘FK_ID’或constraint “FK_ID”都是错误的。

在主表中数据被删除时，从表的数据也应被删除，否则会参数很多无意义的垃圾数据。Mysql可以在建立外键时添加on delete或on update子句来告诉数据库，怎样避免垃圾数据的产生。具体语法格式如下：

Alter table 表名 addconstraint FK_ID foreign key（外键字段名）references外表表名（主键字段名）；

[on delete{cascade | set null | no action | restrict}]

[on update{cascade | set null | no action | restrict}]

语句中各参数的具体说明如下表所示：

参数名称	功能描述
Cascade	删除包含与已删除键值有参照关系的所有记录
Set null	修改包含与已删除键值有参照关系的所有记录，使用null值替换（不能用于已标记为not null的字段）
No action	不进行任何操作
Restrict	拒绝主表删除或修改外键关联列。（在不定义on delete和on update子句时，这是默认设置，也是最安全的设置）

3. 删除外键约束: alter table 表名 drop foreign key 外键名；

二、操作关联表

1. 关联关系

（1）多对一：在多对一的表关系中，应该将外键建在多的一方，否则会造成数据的冗余。

（2）多对多：如学生表和课程表。通常情况下，为了实现这种关系需要定义一张中间表（称为连接表），该表会存在两个外键，分别参照课程表和学生表。在多对多的关系中，需注意的是，连接表的两个外键都是可重复的，但是两个外键之间的关系是不能重复的，所以这两个外键又是表的联合主键。

（3）一对一：首先要分清主从关系，从表需要主表的存在才有意义，如人为主表，身份证为从表，外键建立在从表。需注意的时，这种关系在数据库中并不常见，因为以这种方式存储的信息通常会放在一个表中。在实际开发中，一对一关联关系可以应用于以下几个方面。

Ø 分割具有很多列的表；

Ø 由于安全原因而隔离表的一部分；

Ø 保存临时的数据，并且可以毫不费力地通过删除该表而删除这些数据。

2. 添加数据

上述语句执行成功后，两个表之间的数据就具有关联性。假如要查询软件一班有哪些学生，首先要查询软件一班的id，然后根据这个id在student表中查询该班级有哪些学生。

3. 删除数据：由于grade表和student表之间具有关联关系，参照列被参照的值是不能被删除的。因此，在删除软件一班时，一定要先删除该班级的所有学生，然后再删除班级。

（1）将软件一班的所有学生删除

（2）在grade表中，将软件一班删除

若直接删除软件二班会出错：

需注意的是，实际情况中，想要删除’软件一班’并不需要删除’软件一班的学生’，可以将表student中gid=1地方改成gid为null只要主表中该列没有被从表参照就可以删除。但是在建表时，gid字段有非空约束，所以这个例子中只能删除学生。

三、连接查询：当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。

1. 交叉连接：返回的结果是被连接的两个表中所有数据行的笛卡儿积，也就是返回第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。语法格式如下：

Select * from 表1 cross join 表2；

从上面的结果可以看出，交叉连接的结果就是两个表中所有数据的组合，需要注意的是，在实际研发中，这种需求是很少见的，一般不会使用，而是使用具体的条件对数据进行有目的的查询。

2. 内连接：又称简单连接或自然连接。使用比较运算符对两个表的数据进行比较，并列出与连接条件匹配的数据行，组合成新的记录。语法格式如下：

Select 查询字段 from 表1 [inner] join 表2 on 表1.关系字段=表2.关系字段

还可以使用where条件语句来实现同样的功能。

虽然这两种方式的查询结果是一样的，但是inner join是内连接语句，where是条件判断语句，在where后面可以直接添加其他条件，而inner join语句不可以。

#如果在一个连接查询中，涉及两个表是同一个表，这种查询称为自连接查询。自连接是一种特殊的连接，它是指相互连接的表在物理上为同一个表，但逻辑上分为两个表，例如要查询王红所在部门有哪些员工，就可以使用自连接查询。

3. 外连接：关键字左边的表被称为坐标，右边的字被称为右表

Select 所查字段 from 表1 left|right [outer] join 表2

On 表1.关系字段=表2.关系字段 where条件

（1） left join(左连接)：返回包括左表中的所有记录和右表中符合连接条件的记录。

如果左表的某条记录在右表中不存在，则在右表中显示为空。

（2） Right join(右连接)：返回包括右表中的所有记录和左表中符合连接条件的记录。

4. 复合条件连接查询

四、子查询：是指一个查询语句嵌套在另一个查询语句内部的查询。它可以嵌套在一个select、select … into语句、insert…into等语句中。在执行查询语句时，首先会执行子查询中的语句，然后将返回结果作为外层查询的过滤条件，在子查询中通常可以使用in、exists、any、all操作符

（1）带in关键字的子查询：内层查询语句仅返回一个数据列，这个数据列中的值将供外层查询语句进行比较操作。

例如：查询存在年龄为20岁的员工部门。

（2）带exists关键字的子查询：exists关键字后面的参数可以是任意一个子查询，这个子查询的作用相当于测试，它不会产生任何数据，只返回True或False，当返回值为True时，外层查询才会执行。

下例中，子查询的返回结果为True，所以外层查询语句会执行，即查询出所有部门信息。需要注意的是，exists关键字比in关键字的运行效率高，所以在实际开发中，特别是大数据量时，推荐使用exists关键字。

（3）带any关键字的子查询：any表示满足其中任意一个条件，它允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。

（4）带all关键字的子查询：和any有些类似，只不过带all关键字的子查询返回的结果需同时满足所有内层查询条件。

（5）带比较运算符的子查询

本文讲解了MySQL数据库多表操作，更多相关内容请关注php中文网。