create database xskc character set=utf8;
use xskc;
create table s(sno char(2) primary key,sname char(10),ssex char(2),sage smallint,ssdept char(4));
insert into s values('01','AAA','女',17,'IS');
insert into s values('02','BBB','男',18,'IS');
insert into s values('03','CCC','女',17,'CS');
insert into s values('04','DDD','男',19,'CS');
insert into s values('05','EEE','男',18,'CS');
insert into s values('06','FFF','女',17,'CS');
create table c(cno char(3) primary key,cname char(12),cpno char(3),credit smallint,maxnum smallint default 50,remainum smallint);
insert into c(cno,cname,cpno,credit) values('c1','程序设计','c2',2);
insert into c(cno,cname,cpno,credit) values('c2','高等数学','c2',3);
insert into c(cno,cname,cpno,credit) values('c3','数据结构','c1',3);
insert into c(cno,cname,cpno,credit) values('c4','离散数学','',2);
insert into c(cno,cname,cpno,credit) values('c5','人工智能','c4',2);
create table sc(sno char(2) not null,cno char(3) not null,grade smallint,primary key(sno,cno));
insert into sc values('01','c1',90);
insert into sc values('01','c2',80);
insert into sc values('01','c3',60);
insert into sc values('02','c1',80);
insert into sc values('02','c2',70);
insert into sc values('02','c3',80);
insert into sc values('03','c1',80);
insert into sc values('03','c3',70);

use xskc

delimiter $$

#select * from c$$

update c set remainum = maxnum - (select count(*) from sc where cno = c.cno);$$  #重置剩余课程数

#1、为sc表建立外键约束使得当向sc表插入数据时需要参照s表的学号和c表的课程号
alter table sc add constraint c1 foreign key(sno) references s(sno);
alter table sc add constraint c2 foreign key(cno) references c(cno);

#2、为s表建立约束使得插入记录时学生的年龄只能在[16-30]之间（插入前触发器）
create trigger t1 before insert on s for each row
  begin
    if(new.sage<16 or new.sage>30) then
       insert into nul values(0);
    end if;
  end$$

#3、为s表建立约束使得性别只能为男或女
alter table s modify ssex enum('男','女');$$

#4、建立触发器当向sc表插入新的选课记录时c表remainum值自动更新
create trigger t2 after insert on sc for each row
  update c set remainum = remainum -1 where cno = new.cno;$$

#5、建立触发器当删除sc表某条记录时c表remainum值自动更新
create trigger t3 after delete on sc for each row
  update c set remainum = remainum +1 where cno = old.cno;$$

#6、建立触发器当修改sc表的某条选课记录时c表对应记录的remainum值自动更新
#一个表具有相同操作时间和事件的多个触发器，可以先建立这个触发器
create trigger t4 after update on sc for each row
    #不确定是否正确暂留