编辑代码

CREATE DATABASE EDUC;

USE EDUC;

CREATE TABLE student
(
    sno char(8) NOT NULL,
    sname char(8) NOT NULL,
    sex char(2),
    native char(20),
    birthday date,
    dno char(6),
    spno char(4),
    classno char(4),
    entime date,
    PRIMARY KEY (sno)
);

CREATE TABLE course
(
    cno char(10) NOT NULL,
    PRIMARY KEY (cno),
    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),
    FOREIGN KEY (sno) REFERENCES student(sno)
);

CREATE TABLE teacher
(
    tno char(8) NOT NULL,
    tname char(8) NOT NULL,
    sex char(2),
    birthday date,
    dno char(6),
    pno tinyint(1),
    home varchar(40),
    zipcode char(6),
    tel varchar(40),
    email varchar(40),
    PRIMARY KEY (tno)
);

CREATE TABLE teacher_course
(
    tcid smallint(2) NOT NULL,
    PRIMARY KEY (tcid),
    tno char(8),
    spno char(4),
    classno char(4),
    cno char(10) NOT NULL,
    semester char(6),
    schoolyear char(10),
    classtime varchar(40),
    classroom varchar(40),
    weektime tinyint(1)
);
drop table teacher_course;
drop table teacher;

alter table student drop constraint PK column sno;
alter table student modify column sno varchar(20) NOT NULL;
alter table student add constraint PK_student primary key (sno)


alter table course add year varchar（4）CONSTRAINT year 
CHECK(year =’第一’ or year=’第二’ or year=’第三’ or year=’第四’);

ALTER TABLE course DROP column year;