子查询
嵌套在 SQL 语句中的查询语句称为子查询，可以嵌套多层。子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用。子查询通常出现的位置是在 WHERE 或 HAVING 之后作为查询条件的值。也可以写在 from 后面当作一张表。
语句格式：
SELECT 查询字段 FROM 表名,(子查询) 子查询别名 WHERE 条件字段 = (子查询);
使用 MySQLWorkbench 执行实例：
SELECT number,name,r.role_name FROM testrole,(SELECT * FROM role WHERE role_number = 101) r WHERE number = (SELECT role_number FROM role WHERE role_name = "A");
截屏2023-05-22 18.16.44.png
关联查询
同时查询多张表的数据称为关联查询，关联查询包括: 等值链接、内连接和外连接。
内连接
内连接只返回在连接过程中有连接匹配的记录。根据连接条件，两个表中都有对应的数据存在的记录，才会返回。由于mysql默认是内连接，所以 join 等同于 inner join。
语句格式：
SELECT 表别名.查询字段,表别名.查询字段 FROM 表名 AS 表别名 JOIN 表名 AS 表别名 ON 表别名.关联字段 = 表别名.关联字段;
使用 MySQLWorkbench 执行实例：
SELECT c.category_name,g.goods_name FROM category AS c JOIN goods AS g ON c.category_id = g.category_id;
截屏2023-05-24 18.01.30.png
等值连接
内连接与等值连接效果是相同的，执行效率也相同，只是书写方式不一样，内连接是由SQL 1999规则定的书写方式。
语句格式：
SELECT 表别名.查询字段名,表别名.查询字段名 FROM 表名 AS 表别名,表名 AS 表别名 WHERE 表别名.关联字段 = 表别名.关联字段;
使用 MySQLWorkbench 执行实例：
SELECT c.category_name,g.goods_name FROM category AS c,goods AS g WHERE c.category_id = g.category_id;
截屏2023-05-24 18.01.30.png
外连接
外连接分为左、右外连接以在 JOIN 关键字前添加 LEFT、RIGHT 区分，左外连接时，JOIN 关键字左侧的表显示所有数据，右侧的表显示交集数据；右外连接时，JOIN 关键字右侧的表显示所有数据，左侧的表显示交集数据。
左外连接语句格式：
SELECT 表别名.查询字段名,表别名.查询字段名 FROM 表名 AS 表别名 LEFT JOIN 表名 AS 表别名 ON 表别名.关联字段 = 表别名.关联字段;
使用 MySQLWorkbench 执行实例：
SELECT c.category_name,g.goods_name FROM category AS c LEFT JOIN goods AS g ON c.category_id = g.category_id;
截屏2023-05-25 09.43.16.png
右外连接语句格式：
SELECT 表别名.查询字段名,表别名.查询字段名 FROM 表名 AS 表别名 RIGHT JOIN 表名 AS 表别名 ON 表别名.关联字段 = 表别名.关联字段;
使用 MySQLWorkbench 执行实例：
SELECT c.category_name,g.goods_name FROM category AS c RIGHT JOIN goods AS g ON c.category_id = g.category_id;
截屏2023-05-25 09.53.02.png
笛卡尔积
关联查询时，如果不写关联关系，则查询结果为两张表的乘积，这个乘积称为笛卡尔积（注：笛卡尔积本身没有错误，笛卡尔积是一种数学运算）。但如果把这种查询结果当作最终结果则是错误的，因为产生了重复且错误的数据。
表设计的关联关系
表设计的关联关系分为：一对一、一对多、多对多、自关联。
一对一关系
若有 A 表 和 B 表两张表，A 表中一条数据对应 B 表中的一条数据，这种关系称为一对一关系。
建立一对一关系：在从（主从关系）表中添加一个外键字段值指向主表的主键字段值。
一对多关系
若有 A 表 和 B 表两张表，A 表中一条数据对应 B 表中的多条数据，而 B 表中的一条数据只对应 A 表中的一条数据，这种关系称为一对多关系。
建立一对多关系：一对多的两张表，在多的表里添加外键记录另一个表主键值。
多对多关系
若有 A 表 和 B 表两张表，A 表中一条数据对应 B 表中的多条数据，同时 B 表中的一条数据对应 A 表中的多条数据，这种关系称为多对多关系。例如权限管理表设计的经典五张表：用户表、角色表、权限表、用户与角色关系表、角色与权限关系表。
建立对对多关系：需要创建单独的关系表，关系表中两个外键字段保存两表的主键值。
权限管理表设计的经典五张表实例：
截屏2023-05-28 09.14.50.png
SQL 如下：
/*user_role指定外键*/
ALTER TABLE user_role add constraint user_foreign_key_name FOREIGN KEY(user_foreign_key) REFERENCES user(user_id);
ALTER TABLE user_role add constraint role_foreign_key_name_one FOREIGN KEY(role_foreign_key) REFERENCES role(role_id);
/*role_power指定外键*/
ALTER TABLE role_power add constraint role_foreign_key_name_two FOREIGN KEY(role_foreign_key) REFERENCES role(role_id);
ALTER TABLE role_power add constraint power_foreign_key_name FOREIGN KEY(power_foreign_key) REFERENCES power(power_id);
/*子查询SQL*/
SELECT rp.user_name,rp.rolecol_name,pr.power_name FROM
(SELECT re.user_name,re.rolecol_name,rp.power_foreign_key FROM
(SELECT ur1.user_name,re.rolecol_name,re.role_id FROM
(SELECT ur.role_foreign_key,us.user_name FROM user AS us JOIN user_role AS ur ON us.user_id = ur.user_foreign_key WHERE us.user_name = '小王') ur1
JOIN role AS re ON ur1.role_foreign_key = re.role_id) AS re
JOIN role_power AS rp ON re.role_id = rp.role_foreign_key) AS rp JOIN power AS pr ON rp.power_foreign_key = pr.power_id;
/*关联查询SQL*/
SELECT user.user_name,role.rolecol_name,power.power_name FROM user
JOIN user_role ON user.user_id = user_role.user_foreign_key
JOIN role ON user_role.role_foreign_key = role.role_id
JOIN role_power ON role.role_id = role_power.role_foreign_key
JOIN power ON role_power.power_foreign_key = power.power_id
WHERE user.user_name = '小王';
当查询用户名为小王时，小王同时扮演了两种角色，当我们为用户增加角色时，只需要在 用户-角色关系表中加入数据。 SQL 执行结果：
截屏2023-05-28 10.56.30.png
当查询用户名为小张时，小张只扮演了一种角色，但这个角色同时被分配了两种权限，当我们为角色分配权限时，只需要在 角色-权限关系表中加入数据。 SQL 执行结果：
截屏2023-05-28 11.02.32.png
当查询用户名为小高时，小高只扮演了一种角色，这个角色也只有一种权限。SQL 执行结果：
截屏2023-05-28 11.04.40.png
自关联关系
在表中有一个外键字段指向自己表的主键值，这种关系称为自关联。自关联多用于需要保存层级关系，但不确定有多少层时使用。例如在地区表中使用：
截屏2023-05-27 11.21.29.png
网友评论