接昨天

首先登入帳號

mysql -u root -p

創建新數據庫和表

CREATE DATABASE company;

使用新數據庫

USE company;

創建表格employees和departments，department_id列是外鍵，參照departments表的department_id列。

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(255) NOT NULL
);

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

插入數據

-- 插入部門信息
INSERT INTO departments (department_name) VALUES
    ('HR'),
    ('Finance'),
    ('Engineering');

-- 插入員工信息
INSERT INTO employees (first_name, last_name, department_id) VALUES
    ('John', 'Doe', 1),
    ('Jane', 'Smith', 2),
    ('Robert', 'Johnson', 3);

查詢數據

-- 查詢所有部門信息
SELECT * FROM departments;

-- 查詢所有員工信息及其所屬部門
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;