20240719数据库关联查询、条件查询

mysql关联外键查询

商品表和图片表是分开的，用一张商品图片表关联起来了。

查询商品表所有字段和图片信息。其余的，商人id、区域id、分类id都是直接关联，没有中间表

SELECT
    p.id,
    p.name,
    p.price,
    p.unit,
    f.file,
    p.description,
    p.is_on_sale,
    p.is_reviewed,
    a.name AS area_name,
    u.name AS user_name,
    pc.name AS category_name,
    p.created_at,
    p.updated_at
FROM
    product_product p
LEFT JOIN product_product_image pi ON p.id = pi.product_id
LEFT JOIN backstage_file f ON pi.file_id = f.id
JOIN location_area a ON p.area_id = a.id
JOIN user_user u ON p.merchant_id = u.id
JOIN product_productcategory pc ON p.category_id = pc.id;

join默认是内连接（INNER JOIN），只有左右两个表都有这个数据时，才会被查出来，由于有一些商品没有图片，所以图片相关的两个表用左连接，不然就会出现没有图片就查不出来的情况。

连接方式总结：

1. INNER JOIN（内连接）：如果使用 INNER JOIN，只有当两个表中都存在匹配时，结果集才会包含这些记录。如果任一表中没有匹配的记录，那么这些记录就不会出现在结果集中。
2. LEFT JOIN（左连接） 或 LEFT OUTER JOIN：当你使用 LEFT JOIN 时，结果集会包含左表（JOIN 语句中指定的第一个表）的所有记录，即使右表中没有匹配的记录。对于右表中没有匹配的记录，结果集中这些列的值将会是 NULL。
3. RIGHT JOIN（右连接） 或 RIGHT OUTER JOIN：与 LEFT JOIN 相反，RIGHT JOIN 会包含右表（JOIN 语句中指定的第二个表）的所有记录，即使左表中没有匹配的记录。对于左表中没有匹配的记录，结果集中这些列的值将会是 NULL。
4. FULL OUTER JOIN（全外连接）：FULL OUTER JOIN 会返回左表和右表中所有的记录。当某侧的表中没有匹配时，另一侧表中的列将以 NULL 填充。但是，需要注意的是，并非所有的数据库系统都支持 FULL OUTER JOIN。
5. CROSS JOIN（交叉连接）：CROSS JOIN 会返回两个表的笛卡尔积，即第一个表中的每一行都会与第二个表中的每一行组合。

关于左表右表：

1. 在 SQL 的 JOIN 语句中，左表（left table）和右表（right table）的概念是相对于你写的 JOIN 语句的顺序而言的。第一个指定的表（即在 JOIN 关键字之前的表）被视为左表，而随后通过 JOIN 关键字连接的表则被视为右表。
2. 当你进行多表连接时，这个规则仍然适用，但需要注意的是，每个新的 JOIN 操作都会重新定义“左表”和“右表”的上下文。对于嵌套的 JOIN 或多个连续的 JOIN，你可以将每个 JOIN 看作是一个独立的操作，其中左侧的表是前一个 JOIN 的结果（或最初的表，如果是第一个 JOIN），而右侧的表是你正在连接的新表。

条件查询：

现在实现多表关联查询了，下边需要做个判断：商家只能查询自己的商品，管理员可以查询所有的：

SELECT
    p.id,
    p.name,
    p.price,
    p.unit,
    f.file,
    p.description,
    p.is_on_sale,
    p.is_reviewed,
    a.name AS area_name,
    u.name AS user_name,
    pc.name AS category_name,
    p.created_at,
    p.updated_at,
    su.USER_ID
FROM
    product_product p
LEFT JOIN product_product_image pi ON p.id = pi.product_id
LEFT JOIN backstage_file f ON pi.file_id = f.id
JOIN location_area a ON p.area_id = a.id
JOIN user_user u ON p.merchant_id = u.id
JOIN product_productcategory pc ON p.category_id = pc.id
JOIN sys_user su ON u.phone = su.TEL
WHERE
    ${USER_ID} IN (SELECT user_id FROM sys_user_role WHERE role_id = 'root') OR su.USER_ID = ${USER_ID};