编辑代码

CREATE DATABASE EDUC;
USE EDUC;
CREATE TABLE student(
  sno CHAR(8) NOT NULL PRIMARY KEY,
  sname CHAR(8) NOT NULL,
  sex CHAR(2),
  native char(20),
  birthday DATE,
  dno CHAR(6),
  spno CHAR(4),
  clssno CHAR(4),
  entime date
);
CREATE TABLE course(
  cno CHAR(6) NOT NULL PRIMARY KEY,
  spno CHAR(4),
  cname CHAR(20) NOT NULL,
  ctno tinyint(1),
  experiment tinyint(1),
  lecture tinyint(1),
  semester tinyint(1),
  credit tinyint(1)
  
);
CREATE TABLE student_course(
  sno CHAR(8) NOT NULL, 
  tcid smallint(2) NOT NULL, 
  score tinyint(1),
  PRIMARY KEY (sno, tcid)
);
CREATE TABLE teacher(
  tno CHAR(8) NOT NULL PRIMARY KEY,
  tname CHAR(8), 
  sex CHAR(2), 
  birthday date, 
  dno CHAR(6),
  pno tinyint(1),
  home VARCHAR(40),
  zipcode CHAR(6),
  tel VARCHAR(40),
  email VARCHAR(40)
);
CREATE TABLE teacher_course(
  tcid smallint NOT NULL PRIMARY KEY,
  tno CHAR(5),
  spno CHAR(5),
  classno CHAR(4),
  cno CHAR(6) NOT NULL,
  semester CHAR(6),
  schoolyear CHAR(10),
  classtime VARCHAR(40),
  classroom VARCHAR(40),
  weektime tinyint(1),
  CONSTRAINT fk_1 FOREIGN KEY (tno) REFERENCES teacher(tno),
  CONSTRAINT fk_2 FOREIGN KEY (cno) REFERENCES course(cno)
);
ALTER TABLE student MODIFY COLUMN birthday DATE NOT NULL;
-- 删除 teacher_course 表
DROP TABLE IF EXISTS teacher_course;

-- 删除 teacher 表
DROP TABLE IF EXISTS teacher;
ALTER TABLE student MODIFY COLUMN sno VARCHAR(20);
ALTER TABLE course
ADD COLUMN year VARCHAR(4);

-- 设置 year 字段的值范围约束
ALTER TABLE course
ADD CONSTRAINT chk_year CHECK (year IN ('第一', '第二', '第三', '第四'));
ALTER TABLE course
DROP COLUMN year;