外键

设置外键

• 如果是创建表添加外键约束，我们需要在创建表的()最后添加如下语句

   FOREIGN KEY (brand_id) REFERENCES brand(id)
  

• 如果是表已经创建好，额外添加外键:

  # 将products表中的brand_id字段设置为外键 该外键引用自brand表中的id字段
  ALTER TABLE products ADD FOREIGN KEY(brand_id) REFERENCES brand(id);
  

外键存在时更新和删除数据

设置外键之后，设置/修改外键的值时必须在引用表中对应字段的取值范围内。

比如将brand_id的值设置为10000：

UPDATE products SET brand_id = 10000 WHERE brand = '华为';

此时就会报错：

1452 - Cannot add or update a child row: a foreign key constraint fails

因为brand表中的id值并没有10000的。

此时修改brand表中的id字段的值也是报错：因为被其他表引用为外键了

UPDATE `brand` SET `id` = 100 WHERE `id` = 1;

1451 - Cannot delete or update a parent row: a foreign key constraint fails

如果希望可以修改呢？此时需要修改on delete(删除时)或者on update(更新时)的值;

action

可以给删除时/更新时设置对应的ACTION：

• RESTRICT（默认属性）当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错的， 不允许更新或删除;

• NO ACTION：和RESTRICT是一致的，是在SQL标准中定义的;

• CASCADE：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话:

• 更新：那么会更新对应的记录;

• 删除: 那么关联的记录会被一起删除掉;

• SET NULL：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为NULL；

修改外键的更新时的ACTION大致分为三个步骤：

1. 查看表结构,获取外键名

   SHOW CREATE TABLE products;
   

   打印信息为：

   CREATE TABLE products (
   id int NOT NULL AUTO_INCREMENT,
   brand varchar(20) DEFAULT NULL,
   title varchar(100) NOT NULL,
   price double NOT NULL,
   score decimal(2,1) DEFAULT NULL,
   voteCnt int DEFAULT NULL,
   url varchar(100) DEFAULT NULL,
   pid int DEFAULT NULL,
   brand_id int DEFAULT NULL,
   PRIMARY KEY (id),
   KEY brand_id (brand_id),

   // products_ibfk_1就是外键名

   CONSTRAINT products_ibfk_1 FOREIGN KEY (brand_id) REFERENCES brand (id)
   ) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

2. 删除之前的外键

   ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;
   

3. 添加新的外键，并设置新的action

   ALTER TABLE products ADD FOREIGN KEY(brand_id) 
                                         REFERENCES brand(id) 
                                         # 更新时的action设置为CASCADE
                                         ON UPDATE CASCADE
                                         # 修改时的action设置为RESTRICT（也可以不设置，默认就是）
                                         ON DELETE RESTRICT;
   

此时再更新主键时就不会报错了。

多表查询

什么是多表查询

如果我们希望查询到产品的同时，显示对应的品牌相关的信息，因为数据是存放在两张表中，所以这个时候就需要 进行多表查询。

如果我们直接通过查询语句希望在多张表中查询到数据，这个时候是什么效果呢?

SELECT * FROM `products`, `brand`;

但是这样很多数据是没有意义的，比如华为手机数据和小米品牌的数据结合在一起是没有意义的。所以需要进行筛选。

可以使用WHERE筛选

# 进行筛选 
SELECT * FROM products, brand WHERE products.brand_id = brand.id;

可以看到这时只有81条数据了（因为有些手机是没有品牌信息的）

多表之间的连接

使用WHERE是能查询到数据，但其实际上还是先进行了笛卡尔乘积，然后对其结果再进行筛选。

事实上我们想要的可能是表中的某些特定的数据，这时可以使用 SQL JOIN 操作：

多表操作

1. 左连接
2. 右连接
3. 内连接
4. 全连接

左连接

如果我们希望获取到的是左边所有的数据(以左表为主):

• 这个时候就表示无论左边的表是否有对应的brand_id的值对应右边表的id，左边的数据都会被查询出来;
• 这个也是开发中使用最多的情况，它的完整写法是LEFT [OUTER] JOIN，但是OUTER可以省略的;

左连接1

左连接2

对于上面的图：

# 查询出以products 表为主的 products.brand_id = brand.id的数据 因为brand_id 是以brand表的id设置的 所以是找出手机对应的品牌信息 手机没有品牌信息的 品牌信息会显示NULL 
SELECT * FROM products  LEFT JOIN brand ON products.brand_id = brand.id; // 查询出108条 

第二张图：

# 在products表中查询 brand.id为NULL的数据 
SELECT * FROM products LEFT JOIN brand ON products.brand_id = brand.id WHERE brand.id IS NULL;

ON说明是以什么条件进行连接的

右连接

如果我们希望获取到的是右边所有的数据(以右表为主)：

• 右连接在开发中没有左连接常用，它的完整写法是RIGHT [OUTER] JOIN，但是OUTER可以省略的

右连接1.png

右连接2.png

对于上面的图, 其实代表就是查询所有的品牌（即使没有对应的手机数据，品牌也显示）以及对应的手机数据

# 3.1. 查询所有的品牌（即使没有对应的手机数据，品牌也显示）以及对应的手机数据 
SELECT * FROM products RIGHT JOIN brand ON products.brand_id = brand.id;

下面的图：查询所有没有对应手机信息的品牌数据

# 3.2. 查询所有没有对应手机信息的品牌数据 
SELECT * FROM products RIGHT JOIN brand ON products.brand_id = brand.id WHERE products.brand_id IS NULL;

内连接

内连接表示左边的表和右边的表都有对应的数据关联

• 内连接的写法:INNER JOIN, CROSS JOIN或者 JOIN都可以;
  
  内连接

SELECT * FROM products JOIN brand ON products.brand_id = brand.id;

可以发现，查询的结果跟笛卡尔乘积的结果一致：

SELECT * FROM products, brand WHERE products.brand_id = brand.id;

但它们是有区别的：笛卡尔乘积是将两张表的所有数据都查询出来，然后对查询结果进行筛选，而内连接是在两张表进行连接时就使用条件products.brand_id = brand.id进行了约束。

全连接

全连接的示意图：

全连接

    SELECT * FROM products FULL JOIN brand ON products.brand_id = brand.id;

然后发现会报一个语法错误：

1054 - Unknown column 'products.brand_id' in 'on clause

因为mysql是不支持全连接的。

但我们可以通过其他方式来实现，

对于左图，我们可以把左连接的结果和右连接额的结果进行联合(UNION),联合会去除重复的数据。

也就是对下面的两张图就行联合：

左连接

右连接

(SELECT * FROM products LEFT JOIN brand ON products.brand_id = brand.id)
UNION
(SELECT * FROM products RIGHT JOIN brand ON products.brand_id = brand.id);

对于全连接的右图， 同理可以：

(SELECT * FROM products LEFT JOIN brand ON products.brand_id = brand.id WHERE brand.id IS NULL)
UNION
(SELECT * FROM products RIGHT JOIN brand ON products.brand_id = brand.id WHERE products.brand_id IS NULL);

查询数据转为对象

之前的查询语句的结果通常是一张表，但实际开发中查询的结果应该是一个对象，这时候可以使用JSON_OBJECT;

# 将联合查询到的数据转成对象 （一对多使用的比较多）
SELECT 
    products.id id, products.title title, products.price price,
    JSON_OBJECT('id', brand.id, 'name', brand.`name`, 'website', brand.website) brand 
FROM products 
LEFT JOIN brand ON products.brand_id = brand.id;

或者将查询结果转成对象，并存放到一个数组中：

# 将查询到的多条数据 组成对象 并放到数组中(多对多用的比较多)
SELECT 
    stu.id id, stu.name name,stu.age age,
    JSON_ARRAYAGG(JSON_OBJECT('id',cs.id, 'name',cs.`name`, 'price',cs.price)) 
FROM students stu 
JOIN students_select_courses ssc ON stu.id = ssc.student_id
JOIN courses cs ON cs.id = ssc.course_id
GROUP BY stu.id;