mysql数据库连接两个表

-- 创建用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(50)
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO users (user_id, username, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.com');

INSERT INTO orders (order_id, user_id, order_date, total_amount) VALUES
(101, 1, '2023-01-01', 100.00),
(102, 2, '2023-01-02', 200.00),
(103, 1, '2023-01-03', 150.00);

-- 查询用户及其订单信息（内连接）
SELECT users.username, orders.order_id, orders.order_date, orders.total_amount
FROM users
INNER JOIN orders ON users.user_id = orders.user_id;

-- 查询所有用户及其订单信息（左连接）
SELECT users.username, orders.order_id, orders.order_date, orders.total_amount
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;

-- 查询所有订单及其用户信息（右连接）
SELECT users.username, orders.order_id, orders.order_date, orders.total_amount
FROM users
RIGHT JOIN orders ON users.user_id = orders.user_id;