mysql 表之间关系显示

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    user_id INT UNIQUE,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- 错误示例
INSERT INTO orders (user_id, order_date) VALUES (99, '2023-04-01'); -- user_id 99 在 users 表中不存在

-- 正确示例
INSERT INTO users (id, username) VALUES (99, 'new_user');
INSERT INTO orders (user_id, order_date) VALUES (99, '2023-04-01');

-- 添加索引
CREATE INDEX idx_user_id ON orders(user_id);

CREATE VIEW user_orders AS
SELECT users.username, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id;