MySQL — 关联

来自MySQL的学习笔记，写的不对的地方大家多多指教哦

一、外键关联

什么是外键？

假设有 2 个表，分别是表 A 和表 B，它们通过一个公共字段“id”发生关联关系，我们把这个关联关系叫做 R。如果“id”在表 A 中是主键，那么，表 A 就是这个关系 R 中的主表。相应的，表 B 就是这个关系中的从表，表 B 中的“id”，就是表 B 用来引用表 A 中数据的，叫外键。所以，外键就是从表中用来引用主表中数据的那个公共字段。

image.png

1.添加外键约束

语法结构：

[CONSTRAINT <外键约束名称>] FOREIGN KEY 字段名
REFERENCES <主表名> 字段名

在创建表时添加外键约束：

CREATE TABLE 从表名
(
  字段名 类型,
  ...
-- 定义外键约束，指出外键字段和参照的主表字段
CONSTRAINT 外键约束名
FOREIGN KEY (字段名) REFERENCES 主表名 (字段名)
)

在修改表时定义外键约束：

ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY (字段名) REFERENCES 主表名 (字段名);

例子1：创建表时添加外键约束

首先创建主表：importhead

CREATE TABLE test_mysql.importhead (
    listnumber INT PRIMARY KEY,
    supplierid INT,
    stocknumber INT,
    importtype INT,
    importquantity DECIMAL(10 , 3 ),
    importvalue DECIMAL(10 , 2 ),
    recorder INT,
    recordingdate DATETIME
);

创建从表：test_mysql.importdetails

CREATE TABLE test_mysql.importdetails
(
  listnumber INT,
  itemnumber INT,
  quantity DECIMAL(10,3),
  importprice DECIMAL(10,2),
  importvalue DECIMAL(10,2),
  -- 定义外键约束，指出外键字段和参照的主表字段
  CONSTRAINT fk_importdetails_importhead
  FOREIGN KEY (listnumber) REFERENCES importhead (listnumber)
);

查询外键约束的相关信息：

SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name 
FROM information_schema.KEY_COLUMN_USAGE 
WHERE constraint_name = 'fk_importdetails_importhead';

查询结果为：

image.png

例子2：修改表时定义外键约束

修改表时定义从表test_mysql.importdetails的外键约束

ALTER TABLE importdetails ADD CONSTRAINT fk_importdetails_importhead FOREIGN KEY (listnumber) REFERENCES importhead (listnumber);

2.删除外键约束

删除外键约束使用DROP，语法结构为：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

例子：删除从表test_mysql.importdetails的外键约束

ALTER TABLE importdetails DROP FOREIGN KEY fk_importdetails_importhead;

二、连接

在 MySQL 中，有 2 种类型的连接，分别是内连接（INNER JOIN）和外连接（OUTER JOIN）。

• 内连接表示查询结果只返回符合连接条件的记录，这种连接方式比较常用；
• 外连接则不同，表示查询结果返回某一个表中的所有记录，以及另一个表中满足连接条件的记录。

1.内连接：

在 MySQL 里面，关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接。我们可以通过 JOIN 把两个表关联起来，来查询两个表中的数据。

例子：有一张销售表，如下图：

image.png

有一张会员信息表，如下图：

image.png

通过内连接，查询会员的销售记录：

SELECT 
    a.transactionnumber,
    a.Itemnumber,
    a.price,
    a.transdate,
    b.menbername
FROM
    trans as a
JOIN
    membermaster as b
ON
    (a.cardno=b.cardno)

运行语句，结果如下：

image.png

根据上面的结果，其实可以得知：内连接查询到结果集为两个表的交集部分。

2.外连接：

跟内连接只返回符合连接条件的记录不同的是，外连接还可以返回表中的所有记录，它包括两类，分别是左连接和右连接。

• 左连接，一般简写成 LEFT JOIN，返回左边表中的所有记录，以及右表中符合连接条件的记录；
• 右连接，一般简写成 RIGHT JOIN，返回右边表中的所有记录，以及左表中符合连接条件的记录。

例子1：左外连接

如果需要查询所有销售记录，则可以使用左外连接

SELECT 
    a.transactionnumber,
    a.Itemnumber,
    a.price,
    a.transdate,
    b.menbername
FROM
    trans as a
LEFT JOIN
    membermaster as b
ON
    (a.cardno=b.cardno)

运行语句，结果为：

image.png

从上面的结果可以得知，LEFT JOIN左边的表会返回全部记录，而右边的表只返回符合连接条件的记录

例子2：右外连接：

SELECT 
    a.transactionnumber,
    a.Itemnumber,
    a.price,
    a.transdate,
    b.menbername
FROM
    trans as a
RIGHT JOIN
    membermaster as b
ON
    (a.cardno=b.cardno)

运行语句，结果为：

image.png

从上面的结果可以得知，RIGHT JOIN右边的表会返回全部记录，而左边的表只返回符合连接条件的记录