编辑代码

CREATE DATABASE gradem;
use gradem;
CREATE TABLE student(
	SNO CHAR(10)NOT FULL PRIMARY KEY,
	SNAME varchar(8),
	SSEX CHAR(2),
	SBIRTHDAY varchar(50),
    SADDRESS VARCHAR(50),
    SDEPT CHAR(16),
    SPECIALITY VARCHAR(20)
);

CREATE TABLE COURSE (
    CNO CHAR(5) NOT FULL PRIMARY KEY,
    CNAME VARCHAR(20) NOT FULL
);
CREATE TABLE SC (
    SNO CHAR(10) NOT FULL,
    CNO CHAR(5) NOT FULL,
    DEGREE DECIMAL(4,1),
    PRIMARY KEY(SNO,CNO),
    FOREIGN KEY(SNO) REFRENCES student(SNO),
    FOREIGN KEY(cno) REFRENCES COURSE(cno)
);
CREATE TABLE teachar (
    tno char(3) not full PRIMARY KEY,
    tname varchar(8),
    tsex char(2),
    tbirthday date,
    tdept char(16)
);

CREATE TABLE teaching (
    cno char(5) not full,
    tno char(3) not full,
    cterm tinyint(1),
    PRIMARY KEY(cno,tno),
    FOREIGN KEY(cno) REFRENCES sc(cno),
    FOREIGN KEY(tno) REFRENCES teaching(tno)
);

INSERT INTO student(sno,sname,ssex,sbirthday,saddress,sdept,speciality) 
VALUES ('20050101','李勇','男','1987-01-12','山东济南','计算机工程系','计算机应用')，
('20050201','刘晨','女','1988-06-04','山东青岛','信息工程系','电子商务')，
('20050301','王敏','女','1989-12-23','江苏苏州','数学系','数学')，
('20050202','张立','男','1988-08-25','河北唐山','信息工程系','电子商务');
SELECT * FROM student;
INSERT INTO course(cno,cname,cno,cname) 
VALUES('C01','数据库','C03','信息系统'),
('C02','数学','C04','操作系统');

INSERT INTO sc(sno,cno,degree)
VALUES('20050101','C01','92'),
('20050101','C02','85'),
('20050101','C03','88'),
('20050201','C02','90'),
('20050201','C03','80');

INSERT INTO teacher(tno,tname,tsex,tbirthday,tdept,)
VALUES('101','李新','男','1977-01-12','计算机工程系'),
('102','钱军','女','1968-06-04','计算机工程系'),
('201','王小花','女','1979-12-23','信息工程系'),
('202','张小青','男','1968-08-25','信息工程系');

INSERT INTO teaching(cno,tno,cterm)
VALUES('C01','101','2'),
('C02','102','1'),
('C03','201','3'),
('C04','202','4');

create table student_copy1 like student;
INSERT INTO student_copy1 SELECT*FROM student;

alter table student_copy1 add column 入学时间 date;
alter table student_copy1 modify column sdept char(20);
alter table student_copy1 drop column speciality;