sql1

create database scmanage;
use scmanage;


create table admin(
adminname varchar(20) primary key,
adminpwd varchar(15)
);
insert into admin values('admin','1234');
insert into admin values('1','1');


--教师信息
create table teacher(
Tno char(8) not null primary key,
Tna varchar(10)not null ,
T_sex char(2) default'男',
T_birth varchar(20) not null,
Degree varchar(10) not null,
T_polit char(4) default'群众',
T_room char(4) ,
Dept char(20) not null,
Ph_no varchar(11)
);



--学生信息
create table students(
Sno char(8) not null primary key,
Sna varchar(10) not null ,
S_sex char(2) default'男',
S_birth varchar(20) not null,
Ph_no varchar(11),
Clno char(7) not null
);

--课程信息
create table courses(
Cno char(8) not null primary key,
Cna varchar(10) not null,
Cdate int not null check(Cdate>50 and Cdate<100)
);

--开课信息
create table open_courses(
Cno char(8) not null primary key,
Cna varchar(10) not null,
Tdate char(1) not null check(Tdate>0),
Tno char(8) not null,
Tna varchar(10)not null
);

--选课信息
create table s_oc(
Sno char(8) not null primary key ,
Sna varchar(10) not null ,
Clno char(7) not null,

Cno char(8) not null,
Cna varchar(10) not null,

Tno char(8) not null,
Tna varchar(10)not null,

Tdate char(1) not null check(Tdate>0),
Grade int check(Grade>=0 and Grade<=100),

Sco char(3)
);


delimiter //
create trigger t_teacher1
before delete on teacher
for each row
begin
delete from phone
where phone.Tno=old.tno ;
end//


delimiter //
create trigger t_student1
before delete on students
for each row
begin
delete from s_oc
where s_oc.Sno=old.Sno ;
end//