Node.js<十五>——外键约束和多表查询

Running53
315 阅读8分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第3天，点击查看活动详情

MySQL其他操作及约束

聚合函数

聚合函数是把我们所有的数据当做一组，然后再这一组中进行某些运算或操作

  1. 求所有手机的价格总和
# 起别名的时候可以不使用AS关键字
SELECT SUM(price) totalPrice FROM `products`;
  1. 求华为手机的价格的总和
SELECT SUM(price) FROM `products` WHERE brand = '华为';
  1. 求华为手机的平均价格
SELECT AVG(price) FROM `products` WHERE brand = '华为';
  1. 求所有手机最高的价格
SELECT MAX(price) FROM `products`;
  1. 求所有手机最低的价格
SELECT MIN(price) FROM `products`;
  1. 求华为手机的个数
SELECT COUNT(*) FROM `products` WHERE brand = '华为';
  1. 求有url字段的苹果手机的个数，如果字段值为null则不会被计入进去
SELECT COUNT(url) FROM `products` WHERE brand = '苹果';
  1. 求有price字段且不为空的手机数量
SELECT COUNT(price) FROM `products`;
  1. 求有price字段且不为空的手机数量，而且要求一样的价格不重复计数 sql
SELECT COUNT(DISTINCT price) FROM `products`;

Group By

  1. 查询不同的手机品牌对应的品牌、平均价格、数量、平均评分
* 注意：根据哪个字段分类，前面才能查询到哪个字段，比如这里的brand换成title就不可以
SELECT brand, AVG(price), COUNT(*) , AVG(score) FROM `products` GROUP BY brand;
  1. 查询不同的手机品牌对应的品牌、平均价格、数量、平均评分，并且要求平均价格要大于2000
* 注意：WHERE关键字是针对表的，而HAVING关键字是针对分组的，所以这里不能使用WHERE而要用HAVING
SELECT brand, AVG(price) avgPrice, COUNT(*) , AVG(score) FROM `products` GROUP BY brand HAVING avgPrice > 2000;
  1. 求评分score大于7.5的手机，然后按照品牌进行分类后再求出平均价格
* 注意：因为这里WHERE是作用在表中的，用于选出所有score大于7.5的数据，所以没有问题
SELECT brand, AVG(price) FROM `products` WHERE score > 7.5 GROUP BY brand;

总结： WHERE对应的筛选操作在分组前，HAVING对应的筛选操作在分组后，所以WHERE只能出现在GROUP BY前面，HAVING只能出现在GROUP BY后面

外键

外键相当于是建立了当前表和其他表之间的联系，当一个字段添加了外键约束之后，其设置的值只能是绑定的其他表中指定字段的值，否则就会报错；比如说学生信息表和学校信息表之间就可以给学生信息表中的school_id建立一个外键约束，这样就可以通过school_id去学校信息表中查找到该学生所在学校的信息了

  1. 给user的school_id字段设置school表的id作为外键约束
* 先创建对应的字段
ALTER TABLE `user` ADD school_id INT ; 
* 给对应的字段设置外键约束，引用user2中的id
ALTER TABLE `user` ADD FOREIGN KEY(school_id) REFERENCES school(id);
  1. 在有外键约束的情况下，被依赖的字段是不能随便进行修改的，比如学校信息表中对应的id，因为其已经被其他表中的数据作为外键约束了

如何更新外键中的action？

因为默认情况下，被外键所依赖的字段是不可以随便更新和删除的，因为对应外键的action（on delete 和on update）的值都是RESTRICT

但其不仅仅可以设置这个值而已，其还有很多值可供选择

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

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

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

    • 更新：那么会更新对应的记录，意思就是被依赖字段改成了什么，依赖的字段也要改成什么（开发中常把on update设为这个）
    • 删除：那么关联的记录会被一起删掉，注意这里并不是将对应的字段设为null，而是会直接删除一整条数据，所以我们在开发中on delete一般不设置为这个
  • SET NULL：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为null
  1. 获取到目前的外键的名称
SHOW CREATE TABLE `user`;

打印出来的语句中我们就可以找到对应的外键名称，注意：外键名称并不是字段，而是创建外键时自动创建的，比如我们这里的user_ibfk_1

# 然后我们就可以在控制台中看到创建时的指令，有一些是自动帮我们添加上去的
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `TelPhone` varchar(30) DEFAULT NULL,
  `birthday` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `school_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`TelPhone`),
  KEY `school_id` (`school_id`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`school_id`) REFERENCES `school` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=154 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  1. 根据外键名称将外键删除掉
ALTER TABLE `user` DROP FOREIGN KEY user_ibfk_1;
  1. 重新添加外键约束

多表查询

默认多表查询的结果

表1现在有108条数据，表2现在有6条数据，如果我们先在执行下面的语句，会得到多少条数据呢？

SELECT * FROM `user`, `school`;

我们会发现一共有648条数据，这个数据量是如何得到的呢？

  • 第一张表的108条数据 * 第二张表的6条数据
  • 也就是说一张表中的每一条数据，都会和第二张表中的每一条数据结合一次
  • 这个结果我们称之为笛卡尔乘积，也称之为直积，表示为X * Y

但是事实上很多的数据是没有意义的，比如华为和苹果，小米的品牌结合起来的数据就是没有意义的，我们可以不可以进行筛选呢？

  • 使用WHERE来进行筛选
  • 这个表示在查询到笛卡尔乘积后的结果中，把符合user.school_id = school.id条件的数据过滤出来
SELECT * FROM `user`, `user2` WHERE user.school_id = user2.id;;

多表之间的连接

事实上我们想要的效果并不是这样的，而且表中的某些特定的数据，这个时候我们可以使用SQL JOIN

  • 左连接
  • 右连接
  • 内连接
  • 全连接

左连接

  1. 查询所有的学生（包括没有对应学校信息的学生）以及对应的学校信息 sql
SELECT * FROM `user` LEFT JOIN `school` ON user.school_id = school.id;;

1

可以看到查询的结果中包含两个表的数据，有没有学校信息的学生都被查询了出来

  1. 查询没有对应学校信息的学生
SELECT * FROM `user` LEFT JOIN `school` ON user.school_id = user2.id WHERE school.id IS NULL;

可以看到查询到的结果依然是包含两个表中的数据的，只不过查询到的是两个表中没有关联的学生

右连接

  1. 查询所有的学校信息（包括没有对应的学生信息）以及对应的学生信息
SELECT * FROM `user` RIGHT JOIN `school` ON user.school_id = school.id;

可以看到查询的结果中包含两个表的数据，有没有学生信息的学校都被查询了出来

  1. 查询没有对应学生信息的学校
SELECT * FROM `user` RIGHT JOIN `school` ON user.school_id = school.id WHERE user.school_id IS NULL;

可以看到查询到的结果依然是包含两个表中的数据的，只不过查询到的是两个表中没有关联的学校

内连接

  1. 查询绑定了学校信息的学生
SELECT * FROM `user` JOIN `school` ON user.school_id = school.id;、
# 后面也可以加上WHERE关键字查询指定的数据
SELECT * FROM `user` JOIN `school` ON user.school_id = school.id where user.name = '2';

其和笛卡尔乘积查询到的结果一样，但他们的过程是不一样的，笛卡尔乘积是先查询到了所有的情况然后再做了一层筛选，而内连接是在连接的时候就已经做了条件限制了

可以看到查询的结果中包含两个表的数据，只有具有连接的信息才被查询了出来:

全连接

  1. 查询所有的学生和学校信息（无论相互之间有没有联系）
# 相当于是我们的左连接1和右连接1做了一个联合，重复的部分会被删除掉的
(SELECT * FROM `user` LEFT JOIN `school` ON user.school_id = school.id)
UNION
(SELECT * FROM `user` RIGHT JOIN `school` ON user.school_id = school.id);

可以看到两张表内无论是有没有联系的数据都被查询了出来

  1. 查询所有的学生和学校信息（相互之间没有联系的）
# 相当于是我们的左连接2和右连接2做了一个联合
(SELECT * FROM `user` LEFT JOIN `school` ON user.school_id = school.id WHERE school.id IS NULL) 
UNION
(SELECT * FROM `user` RIGHT JOIN `school` ON user.school_id = school.id WHERE user.school_id IS NULL);

将两个表中没有联系的部分都查询了出来

avatar
文章
阅读
粉丝