MySQL學習小記(二) 混合表

混合表

繼續上次的内容

建立一個表posts,将上次的users表中的id作為posts的外鍵

CREATE TABLE posts(
    id INT auto_increment,
    user_id INT,
    title VARCHAR(100),
    body text,
    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP, #時間戳的預設值
    PRIMARY KEY(ID),
    FOREIGN KEY(user_id) REFERENCES users(id) #★
);      

插入了幾條記錄,

通過主鍵将他們關聯起來

SELECT users.first_name, users.last_name, posts.title, posts.publish_date
FROM users
INNER JOIN posts
ON users.id = posts.user_id
ORDER BY posts.title;      

再建立一個評論表,與使用者和文章聯系起來

CREATE TABLE comments(
    id INT AUTO_INCREMENT,
    post_id INT,
    user_id INT,
    body TEXT,
    publish_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id),
    FOREIGN KEY(post_id) REFERENCES posts(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);      

插入幾條記錄

INSERT INTO comments(post_id, user_id, body)
VALUES (1, 3, "1st comment by user_3"),
(2, 3, "2nd comment by user_3"),
(4, 5, "3rd comment by user_5"),
(3, 4, "4th comment by user_4");      

連接配接多個表

SELECT 
comments.body, posts.title, 
CONCAT(users.first_name,'-',users.last_name) AS 'Name'
FROM comments
LEFT JOIN posts ON posts.id = comments.post_id
INNER JOIN users ON users.id = comments.user_id
ORDER BY posts.title;      

為啥此時在右邊加入會出現下面這種情況捏

SELECT 
comments.body, posts.title, users.first_name
FROM comments
LEFT JOIN posts ON posts.id = comments.post_id
RIGHT JOIN users ON users.id = comments.user_id
ORDER BY posts.title;      

各種JOIN之間的差別

這就需要了解一下各種JOIN之間的差別了, 網上有張圖流傳的較為廣泛

以我建的兩個表舉例

comments表(下面統稱為C表)

users表(下面統稱為U表)

表C左連接配接表U，以左為主，則表示以表C為主，關聯上表U的資料，查出來的結果顯示左邊的所有資料，右邊顯示的是和左邊有交集部分的資料

SELECT 
comments.id,comments.user_id,comments.body, users.last_name
FROM comments
LEFT JOIN users ON users.id = comments.user_id;      

若是右接U表,則會是這樣