schema.sql

CREATE DATABASE IF NOT EXISTS `course_system` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

USE `course_system`;

CREATE TABLE IF NOT EXISTS `student` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `sno` VARCHAR(20) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL DEFAULT '123456',
  `major` VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS `teacher` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `tno` VARCHAR(20) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL DEFAULT '123456',
  `department` VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS `admin` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL DEFAULT '123456'
);

CREATE TABLE IF NOT EXISTS `course` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `teacher` VARCHAR(50) NOT NULL,
  `credit` INT NOT NULL,
  `capacity` INT NOT NULL,
  `selected` INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS `selection` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `course_id` INT NOT NULL,
  `score` INT DEFAULT NULL,
  UNIQUE KEY `uk_student_course` (`student_id`, `course_id`)
);

INSERT IGNORE INTO `student` (`name`, `sno`, `password`, `major`) VALUES
('张三', '20230001', '123456', '计算机科学与技术'),
('李四', '20230002', '123456', '软件工程'),
('王五', '20230003', '123456', '人工智能');

INSERT IGNORE INTO `teacher` (`name`, `tno`, `password`, `department`) VALUES
('张老师', 't001', '123456', '计算机系'),
('王老师', 't002', '123456', '软件系'),
('李老师', 't003', '123456', '网络系');

INSERT IGNORE INTO `admin` (`username`, `password`) VALUES
('admin', '123456');

INSERT IGNORE INTO `course` (`name`, `teacher`, `credit`, `capacity`, `selected`) VALUES
('高等数学', '数学老师A', 4, 100, 0),
('Java程序设计', '张老师', 3, 50, 0),
('Spring Boot实战', '王老师', 3, 30, 0),
('数据库系统概念', '李老师', 4, 80, 0),
('计算机网络', '赵老师', 3, 60, 0),
('编译原理', '钱老师', 4, 40, 0);