编辑代码

CREATE DATABASE choose85;

USE choose85;

-- 创建 classes 表
CREATE TABLE classes (
  class_no int NOT NULL AUTO_INCREMENT,
  class_name char(20) NOT NULL,
  department_name char(20) NOT NULL,
  PRIMARY KEY (class_no),
  UNIQUE KEY class_name (class_name)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;

-- 创建 teacher 表
CREATE TABLE teacher (
  teacher_no char(10) NOT NULL,
  teacher_name char(10) NOT NULL,
  teacher_contact char(20) NOT NULL,
  PRIMARY KEY (teacher_no)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- 创建 course 表（依赖 teacher 表）
CREATE TABLE course (
  course_no int NOT NULL AUTO_INCREMENT,
  course_name char(10) NOT NULL,
  up_limit int DEFAULT '60',
  description text NOT NULL,
  status char(6) DEFAULT '未审核',
  teacher_no char(10) NOT NULL,
  PRIMARY KEY (course_no),
  UNIQUE KEY teacher_no (teacher_no),
  CONSTRAINT course_teacher_fk FOREIGN KEY (teacher_no) REFERENCES teacher (teacher_no)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk;

-- 创建 student 表（依赖 classes 表）
CREATE TABLE student (
  student_no char(11) NOT NULL,
  student_name char(10) NOT NULL,
  student_contact char(20) NOT NULL,
  class_no int DEFAULT NULL,
  PRIMARY KEY (student_no),
  KEY student_class_fk (class_no),
  CONSTRAINT student_class_fk FOREIGN KEY (class_no) REFERENCES classes (class_no)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- 创建 choose 表（依赖 student 和 course 表）
CREATE TABLE choose (
  choose_no int NOT NULL AUTO_INCREMENT,
  student_no char(11) NOT NULL,
  course_no int NOT NULL,
  score tinyint unsigned DEFAULT NULL,
  choose_time datetime NOT NULL,
  PRIMARY KEY (choose_no),
  KEY choose_student_fk (student_no),
  KEY choose_course_fk (course_no),
  CONSTRAINT choose_course_fk FOREIGN KEY (course_no) REFERENCES course (course_no),
  CONSTRAINT choose_student_fk FOREIGN KEY (student_no) REFERENCES student (student_no)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=gbk;


-- 插入 classes 表数据
INSERT INTO classes VALUES (1,'2012自动化1班','机电工程'),(2,'2012自动化2班','机电工程'),(3,'2012自动化3班','机电工程');

-- 插入 teacher 表数据
INSERT INTO teacher VALUES ('001','张老师','11000000000'),('002','李老师','12000000000'),('003','王老师','13000000000');

-- 插入 course 表数据
INSERT INTO course VALUES (1,'java语言程序设计',60,'暂无','已审核','001'),(2,'MySQL数据库',150,'暂无','已审核','002'),(3,'C语言程序设计',230,'暂无','已审核','003');

-- 插入 student 表数据
INSERT INTO student VALUES ('2012001','张三','15000000000',1),('2012002','李四','16000000000',1),('2012003','王五','17000000000',3),('2012004','马六','18000000000',2),('2012005','田七','19000000000',2);

-- 插入 choose 表数据
INSERT INTO choose VALUES (1,'2012001',2,40,'2022-04-17 11:33:04'),(2,'2012001',1,50,'2022-04-17 11:33:04'),(3,'2012002',3,60,'2022-04-17 11:33:04'),(4,'2012002',2,70,'2022-04-17 11:33:04'),(5,'2012003',1,80,'2022-04-17 11:33:04'),(6,'2012004',2,90,'2022-04-17 11:33:04'),(7,'2012005',3,NULL,'2022-04-17 11:33:04'),(8,'2012005',1,NULL,'2022-04-17 11:33:04');



-- 查询
SELECT * FROM classes;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM student;
SELECT * FROM choose;

-- 温馨提示
SELECT '图形界面以将表数据导入，但终端启动时恢复默认。' as '温馨提示：'UNION SELECT '所以使用sql命令时先使用choose85数据库再操作表内容！' as '温馨提示：' ;