mysql 数据表关系结构

-- 检查外键值是否存在
SELECT * FROM related_table WHERE primary_key_column = foreign_key_value;

-- 插入前确保外键值存在
INSERT INTO table_name (column1, column2, foreign_key_column)
SELECT value1, value2, primary_key_value
FROM related_table
WHERE some_condition;

-- 创建索引
CREATE INDEX index_name ON table_name (column_name);

-- 优化查询语句
SELECT column1, column2
FROM table1
JOIN table2 ON table1.foreign_key = table2.primary_key
WHERE some_condition;

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
INSERT INTO orders (user_id, order_date, total_amount) VALUES (LAST_INSERT_ID(), '2023-10-01', 100.50);

SELECT u.username, o.order_date, o.total_amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE u.username = 'john_doe';