数据库原理及应用实验四（MySQL）

#实验4
#1
create database students;
use students;
create table stu(
    学号 char(4) not null primary key,
    姓名 char(8),
    性别 char(2),
    出生日期 date
);
#2
create table sc(
	学号 char(4) not null,
    课号 char(4) not null,
    成绩 decimal(5,2) check(成绩 between 0 and 100),
    primary key(学号,课号),
    constraint fk_sno foreign key(学号) references stu(学号)
);
#3
create table course(
	课号 char(4) not null,
    课名 char(20),
    学分 int,
    constraint uq_cname unique(课名)
);
#4
alter table course
add primary key(课号);
#5
alter table sc
add constraint fk_cno foreign key(课号) references course(课号)
on update cascade;
#6
alter table stu
add constraint up_sname unique(姓名);
#7
alter table course
add constraint ck_xf check(学分>0);
#8
alter table sc
drop foreign key fk_cno;
alter table sc
drop foreign key fk_sno;
#9
alter table stu   
drop primary key;
#10
alter table course
drop index uq_cname;
#11
create table test(
    date_time varchar(50)
);
create trigger test_trg
     after insert
     on stu
     for each row
     insert into test values(sysdate());
insert into stu values('1','mary','f','1995-10-13');
select * from test;

#12
create trigger del_taig
	after delete
    on course
    for each row
    delete from sc where 课号=old.课号;
delete from course where 课号='1';
select *from sc;