mysql_2

CREATE TABLE student
(sno CHAR(10) NOT NULL PRIMARY KEY,
 sname VARCHAR(8),
 ssex CHAR(2),
 sbirthday datetime(2),
 saddress VARCHAR(50),
 sdept CHAR(16),
 speciality VARCHAR(20)
);

CREATE TABLE course
(cno CHAR(5) NOT NULL PRIMARY KEY,
 cname VARCHAR(20) NOT NULL
);

CREATE TABLE sc
(sno CHAR(10) NOT NULL,
 cno CHAR(5) NOT NULL,
 degree DECIMAL(4,1),
 CONSTRAINT n1 PRIMARY KEY (sno,cno),
 CONSTRAINT n2 FOREIGN KEY (sno) REFERENCES student(sno),
 CONSTRAINT n2 FOREIGN KEY (cno) REFERENCES course(cno)
);

CREATE TABLE teacher
(tno CHAR(3) NOT NULL PRIMARY KEY,
 tname VARCHAR(8) NOT NULL,
 tsex CHAR(2),
 tbirthday DATE,
 tdept CHAR(16)
);

CREATE TABLE teaching
(cno CHAR(5) NOT NULL,
 tno CHAR(3) NOT NULL,
 cterm TINYINT(1),
 CONSTRAINT n1 PRIMARY KEY(cno,tno),
 CONSTRAINT n2 FOREIGN KEY(cno) REFERENCES course(cno),
 CONSTRAINT n3 FOREIGN KEY(tno) REFERENCES teacher(tno)
);

修改表结构。
向student表中增加“入学时间”列，其数据类型为日期时间型。

ALTER TABLE student ADD sdate DATE

将student表中的sdept字段长度改为20。

ALTER TABLE student MODIFY sdept CHAR(20);

将student表中的speciality字段删除。

ALTER TABLE student DROP speciality;