-- 1. 用戶表（存儲(chǔ)用戶基本信息）
CREATE TABLE `user` (
    user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用戶ID（主鍵）',
    user_name VARCHAR(50) NOT NULL COMMENT '用戶名',
    user_phone VARCHAR(20) COMMENT '用戶手機(jī)號(hào)',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 商品表（存儲(chǔ)商品信息）
CREATE TABLE `product` (
    product_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID（主鍵）',
    product_name VARCHAR(100) NOT NULL COMMENT '商品名稱',
    price DECIMAL(10,2) NOT NULL COMMENT '商品價(jià)格',
    stock INT DEFAULT 0 COMMENT '商品庫存'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 訂單表（存儲(chǔ)用戶訂單，關(guān)聯(lián)用戶表和商品表）
CREATE TABLE `order` (
    order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '訂單ID（主鍵）',
    user_id INT NOT NULL COMMENT '用戶ID（關(guān)聯(lián)user表的user_id）',
    product_id INT NOT NULL COMMENT '商品ID（關(guān)聯(lián)product表的product_id）',
    order_amount DECIMAL(10,2) NOT NULL COMMENT '訂單金額',
    order_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '下單時(shí)間',
    -- 外鍵約束（可選，用于強(qiáng)制數(shù)據(jù)完整性）
    FOREIGN KEY (user_id) REFERENCES `user`(user_id),
    FOREIGN KEY (product_id) REFERENCES `product`(product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入用戶數(shù)據(jù)（3個(gè)用戶：張三、李四、王五，其中王五暫無訂單）
INSERT INTO `user` (user_name, user_phone) VALUES
('張三', '13800138000'),
('李四', '13900139000'),
('王五', '13700137000');

-- 插入商品數(shù)據(jù)（3個(gè)商品：手機(jī)、電腦、耳機(jī)）
INSERT INTO `product` (product_name, price, stock) VALUES
('華為Mate60', 6999.00, 100),
('蘋果MacBook Pro', 12999.00, 50),
('索尼WH-1000XM5', 2499.00, 200);

-- 插入訂單數(shù)據(jù)（4個(gè)訂單：張三買了手機(jī)和耳機(jī)，李四買了電腦，無王五的訂單，且有一個(gè)訂單關(guān)聯(lián)的商品ID為4（不存在的商品））
INSERT INTO `order` (user_id, product_id, order_amount) VALUES
(1, 1, 6999.00),  -- 張三買華為Mate60
(1, 3, 2499.00),  -- 張三買索尼耳機(jī)
(2, 2, 12999.00), -- 李四買蘋果電腦
(2, 4, 0.00);     -- 李四買了一個(gè)不存在的商品（product_id=4）

-- 顯式交叉連接（用戶表 × 商品表）
SELECT u.user_name, p.product_name 
FROM `user` u
CROSS JOIN `product` p;

-- 顯式內(nèi)連接（推薦，可讀性高）
SELECT 
    u.user_name,
    o.order_id,
    p.product_name,
    o.order_amount
FROM `user` u
INNER JOIN `order` o ON u.user_id = o.user_id
INNER JOIN `product` p ON o.product_id = p.product_id;

-- 隱式內(nèi)連接（省略INNER JOIN，用WHERE指定條件，功能一致）
SELECT 
    u.user_name,
    o.order_id,
    p.product_name,
    o.order_amount
FROM `user` u, `order` o, `product` p
WHERE u.user_id = o.user_id AND o.product_id = p.product_id;

-- 左外連接：保留用戶表的所有行
SELECT 
    u.user_name,
    o.order_id,
    p.product_name,
    o.order_amount
FROM `user` u
LEFT JOIN `order` o ON u.user_id = o.user_id
LEFT JOIN `product` p ON o.product_id = p.product_id;

-- 左表：user（u），右表：order（o）
SELECT u.user_name, o.order_id
FROM `user` u
LEFT JOIN `order` o ON u.user_id = o.user_id;

-- 左表：第一步得到的臨時(shí)表（user+order），右表：product（p）
SELECT ...
FROM (user u LEFT JOIN order o ON ...)  -- 這是新的左表
LEFT JOIN `product` p ON o.product_id = p.product_id;

-- 右外連接：保留商品表的所有行
SELECT 
    u.user_name,
    o.order_id,
    p.product_name,
    o.order_amount
FROM `user` u
INNER JOIN `order` o ON u.user_id = o.user_id
RIGHT JOIN `product` p ON o.product_id = p.product_id;

SELECT 
    u.user_name,
    o.order_id,
    p.product_name,
    o.order_amount
FROM `product` p
RIGHT JOIN `order` o ON p.product_id = o.product_id
LEFT JOIN `user` u ON o.user_id = u.user_id;
-- 結(jié)果會(huì)包含product_id=4的無效訂單，以及所有商品（若有商品無訂單則顯示NULL）

-- 左外連接部分：所有用戶 + 匹配的訂單/商品
SELECT 
    u.user_name,
    o.order_id,
    p.product_name,
    o.order_amount
FROM `user` u
LEFT JOIN `order` o ON u.user_id = o.user_id
LEFT JOIN `product` p ON o.product_id = p.product_id

UNION  -- UNION去重，UNION ALL保留重復(fù)行

-- 右外連接部分：所有商品 + 匹配的訂單/用戶
SELECT 
    u.user_name,
    o.order_id,
    p.product_name,
    o.order_amount
FROM `user` u
RIGHT JOIN `order` o ON u.user_id = o.user_id
RIGHT JOIN `product` p ON o.product_id = p.product_id;

-- 自然內(nèi)連接（自動(dòng)匹配user_id字段）
SELECT 
    user_name,
    order_id,
    order_amount
FROM `user` u
NATURAL JOIN `order` o;

-- 創(chuàng)建員工表（包含員工ID和上級(jí)ID，上級(jí)ID關(guān)聯(lián)自身的員工ID）
CREATE TABLE `employee` (
    emp_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '員工ID',
    emp_name VARCHAR(50) NOT NULL COMMENT '員工姓名',
    manager_id INT COMMENT '上級(jí)ID（關(guān)聯(lián)emp_id）',
    department VARCHAR(50) COMMENT '部門'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入測(cè)試數(shù)據(jù)
INSERT INTO `employee` (emp_name, manager_id, department) VALUES
('馬云', NULL, '董事會(huì)'),       -- 馬云無上級(jí)
('張勇', 1, 'CEO辦公室'),      -- 張勇的上級(jí)是馬云
('王堅(jiān)', 2, '阿里云'),         -- 王堅(jiān)的上級(jí)是張勇
('蔣芳', 2, '廉政部');         -- 蔣芳的上級(jí)是張勇

-- 自連接查詢：每個(gè)員工的姓名及對(duì)應(yīng)的上級(jí)姓名
SELECT 
    e.emp_name AS 員工姓名,
    m.emp_name AS 上級(jí)姓名,
    e.department AS 部門
FROM `employee` e
LEFT JOIN `employee` m ON e.manager_id = m.emp_id;