一、Mysql外键约束foreign key
（1）外键约束概述
- 外键约束用来在两个表的数据之间建立连接，它可以是一列或者多列，一个表可以有一个或者多个外键。
- 在设置外键的时候，要有两张表，其中一个是主表，另外一个是从表
- 外键的主要作用就是保持数据的一致性、完整性，能够实验级联删除和级联更新
主表：对于两个具有关联关系的表而言，相关联字段中的主键所在的表就是主表
从表：对于两个具有关联关系的表而言，相关联字段中的外键所在的表就是从表
主表和从表都可以有主键，但是从表的主键不能作为主表的外键
定义外键是由从表定义的，定义自己的某个项去关联某个表的某个项，从而定义外键与主表的主键进行关联
主表删除、修改数据时，从表会进行同步，而主表加数据从表是不会同步的
（2）选取设置Mysql外键约束的字段
定义一个外键时，需要遵守下列规则：
- 主表必须存在于数据库中，或者是当前正在创建的表，如果是第二种情况，则主表和从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性
- 必须为主表定义主键
- 主键不允许出现空值，但是允许在外键出现空值，也就是说只要外键的每个非空值出现在指定的主键中，那么这个外键的内容就是正确的
- 在主表的表名后面指定列名或列名的组合，这个列或列的组合必须是主表的主键或候选主键
- 外键中的列的数目必须和主表中的主键中的列的数目相同
- 外键中的列的数据类型必须和主表中的主键中的列的数据类型相同
- 存储引擎必须是innoDB，使用mysql5.0以上版本
（3）使用外键约束foreign key
-在创建表时设置外键约束
******（1）进入数据库，创建库和进入库 [root@rzy ~]# mysql -u root -p123123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.12 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database aaa; Query OK, 1 row affected (0.00 sec) mysql> use aaa; Database changed ******（2）创建主表和从表，aaa为主表，bbb为从表，bbb从表的uuid项为aaa主表的外键 mysql> create table aaa(id int primary key,name char(10) not null,age int); Query OK, 0 rows affected (0.00 sec) mysql> create table bbb(id int primary key,name char(10),uuid int,constraint waijian foreign key(uuid) references aaa(id)); Query OK, 0 rows affected (0.01 sec) #constraint ：定义外键名称，这里定义的是waijian #foreign key（uuid） references aaa（id） ：定义本表的uuid项为aaa表的外键并且与id项关联 ******（3）可以查看两个表的信息，key列中pri为主键，mul为外键 mysql> desc aaa; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(10) | NO | | NULL | | | age | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> desc bbb; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(10) | YES | | NULL | | | uuid | int(11) | YES | MUL | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
-在修改表时添加外键约束
mysql> alter table bbb add constraint waijian foreign key(uuid) references aaa(id); #添加外键约束 Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table bbb; #查看bbb表的信息 +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bbb | CREATE TABLE `bbb` ( `id` int(11) NOT NULL, `name` char(10) DEFAULT NULL, `uuid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `waijian` (`uuid`), CONSTRAINT `waijian` FOREIGN KEY (`uuid`) REFERENCES `aaa` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
-删除外键约束
mysql> alter table bbb drop foreign key waijian; #删除外键约束，在从表上面删除 Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table bbb; #查看bbb表的信息 +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bbb | CREATE TABLE `bbb` ( `id` int(11) NOT NULL, `name` char(10) DEFAULT NULL, `uuid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `waijian` (`uuid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
二、内外连接查询
（1）inner join内连接查询
内连接是通过在查询数据时设置连接条件的方式，来移除查询后结果的数据行的交叉连接，利用条件表达式来消除交叉连接的某些数据行
在Mysql from子句中使用关键字inner join连接两张表，并且使用on子句来设置连接条件，如果没有任何条件，inner join和cross join 在语法上是等同的，两者可以互换
内连接是系统默认的表连接，所有在from子句后面可以省略inner关键字，只用关键字join，使用内连接后，from子句中的on子句可以用来设置连接表的条件
在from子句中可以在多个表之间连续使用inner join或join，如此可以同时实现多个表的内连接
mysql> create table aaa(id int,name char (10),age int); #创建两个表 Query OK, 0 rows affected (0.00 sec) mysql> create table bbb(id int,name char (10),sex char(10)); Query OK, 0 rows affected (0.00 sec) mysql> insert into aaa values(1,"zhangsan",18); #给两个表插入数据 Query OK, 1 row affected (0.00 sec) mysql> insert into aaa values(2,"lisi",20); Query OK, 1 row affected (0.00 sec) mysql> insert into bbb values(1,"zhangsan","man"); Query OK, 1 row affected (0.00 sec) mysql> insert into bbb values(2,"lisi","woman"); Query OK, 1 row affected (0.00 sec) mysql> select * from aaa; #查看两个表的数据 +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | zhangsan | 18 | | 2 | lisi | 20 | +------+----------+------+ 2 rows in set (0.00 sec) mysql> select * from bbb; +------+----------+-------+ | id | name | sex | +------+----------+-------+ | 1 | zhangsan | man | | 2 | lisi | woman | +------+----------+-------+ 2 rows in set (0.00 sec) mysql> alter table bbb drop name; #先删除bbb表的name项，两个表只要有一个相同项就可以，如果有多个就没必要进行内连接查询了 Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from bbb; +------+-------+ | id | sex | +------+-------+ | 1 | man | | 2 | woman | +------+-------+ 2 rows in set (0.00 sec) mysql> select name,sex from aaa,bbb where aaa.id=bbb.id; #查看aaa和bbb表中id项相同的name和sex项的数据 +----------+-------+ | name | sex | +----------+-------+ | zhangsan | man | | lisi | woman | +----------+-------+ 2 rows in set (0.00 sec) mysql> select name,sex from aaa inner join bbb on aaa.id=bbb.id; #和上面查询结果相同，这个使用了inner join内连接 +----------+-------+ | name | sex | +----------+-------+ | zhangsan | man | | lisi | woman | +----------+-------+ 2 rows in set (0.00 sec)
（2）left\right join外连接查询（左连接和右连接）
内连接是交叉显示两个表的数据，而左右连接是将左边或右边的表作为参照表来显示数据
左外链接以左表为主，右外链接以右表为主
mysql> insert into aaa values(3,"hehe",99); #给aaa表再次插入一条数据 Query OK, 1 row affected (0.00 sec) mysql> select * from aaa; #查看aaa表和bbb表的数据 +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | zhangsan | 18 | | 2 | lisi | 20 | | 3 | hehe | 99 | +------+----------+------+ 3 rows in set (0.00 sec) mysql> select * from bbb; +------+-------+ | id | sex | +------+-------+ | 1 | man | | 2 | woman | +------+-------+ 2 rows in set (0.00 sec) mysql> select name,sex from aaa a left outer join bbb b on a.id=b.id; #使用左连接，以左表为参照表，可以看到aaa表中的hehe就算在bbb表中没有，也可以显示，只不过是null的，aaa a表示别名为a，bbb的别名为b +----------+-------+ | name | sex | +----------+-------+ | zhangsan | man | | lisi | woman | | hehe | NULL | +----------+-------+ 3 rows in set (0.00 sec) mysql> select name,sex from aaa a right outer join bbb b on a.id=b.id; #和上面大同小异，使用右连接，以右表为参照表，可以看到aaa表的hehe没有了，这是因为bbb表中没有，而bbb表又是参照表所以不会显示 +----------+-------+ | name | sex | +----------+-------+ | zhangsan | man | | lisi | woman | +----------+-------+ 2 rows in set (0.00 sec)
