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

-- 2. 商品表（存儲商品信息）
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 '商品價格',
    stock INT DEFAULT 0 COMMENT '商品庫存'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 訂單表（存儲用戶訂單，關聯(lián)用戶表和商品表）
CREATE TABLE `order` (
    order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '訂單ID（主鍵）',
    user_id INT NOT NULL COMMENT '用戶ID（關聯(lián)user表的user_id）',
    product_id INT NOT NULL COMMENT '商品ID（關聯(lián)product表的product_id）',
    order_amount DECIMAL(10,2) NOT NULL COMMENT '訂單金額',
    order_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '下單時間',
    -- 外鍵約束（可選，用于強制數(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個用戶：張三、李四、王五，其中王五暫無訂單）
INSERT INTO `user` (user_name, user_phone) VALUES
('張三', '13800138000'),
('李四', '13900139000'),
('王五', '13700137000');

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

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

-- 顯式內連接（推薦，可讀性高）
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;

-- 隱式內連接（省略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;

-- 左表：第一步得到的臨時表（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;
-- 結果會包含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保留重復行

-- 右外連接部分：所有商品 + 匹配的訂單/用戶
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;

-- 自然內連接（自動匹配user_id字段）
SELECT 
    user_name,
    order_id,
    order_amount
FROM `user` u
NATURAL JOIN `order` o;

-- 創(chuàng)建員工表（包含員工ID和上級ID，上級ID關聯(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 '上級ID（關聯(lián)emp_id）',
    department VARCHAR(50) COMMENT '部門'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- 自連接查詢：每個員工的姓名及對應的上級姓名
SELECT 
    e.emp_name AS 員工姓名,
    m.emp_name AS 上級姓名,
    e.department AS 部門
FROM `employee` e
LEFT JOIN `employee` m ON e.manager_id = m.emp_id;