MySQL 环境、关系模型与基本增删改查

sudo apt update
sudo apt install -y mysql-server mysql-client
sudo systemctl status mysql
sudo mysql

CREATE DATABASE engineering_notes
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE engineering_notes;

CREATE TABLE projects (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tasks (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    project_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    status ENUM('todo', 'doing', 'done') NOT NULL DEFAULT 'todo',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tasks_project
      FOREIGN KEY (project_id) REFERENCES projects(id)
      ON DELETE CASCADE
);

SHOW TABLES;
DESCRIBE projects;
SHOW CREATE TABLE tasks;

INSERT INTO projects (name) VALUES ('engineering-basics');
INSERT INTO tasks (project_id, title, status)
VALUES
  (1, 'configure Linux environment', 'done'),
  (1, 'connect MySQL from Python', 'todo');

SELECT id, title, status FROM tasks ORDER BY id;

UPDATE tasks SET status = 'doing' WHERE id = 2;
DELETE FROM tasks WHERE id = 2 AND status = 'done';

ALTER TABLE tasks ADD COLUMN due_at DATETIME NULL AFTER status;

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'replace-with-strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE
ON engineering_notes.* TO 'app_user'@'localhost';
SHOW GRANTS FOR 'app_user'@'localhost';