20240719資料庫關聯查詢、條件查詢

marverdol發表於2024-07-19
資料庫
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};

相關文章