实验二 数据描述、定义实验

-- create schema StudentDB ; 
/*
select *from Student;
select *from Course;
select *from SC;
*/

use StudentDB;

drop table SC;
drop table student;
drop table course; 

create table Student(
Sno char(10) primary key,
Sname varchar(30) not null,
Ssex char(9),
Sage smallint,
Sdept varchar(50),
CONSTRAINT ck1 check( Ssex in('男','女') )
);
/*
check子句的用法
http://www.w3school.com.cn/sql/sql_check.asp
http://zhidao.baidu.com/question/187757031.html

MySQL的视图不支持CHECK，我个人是很希望它能支持的。但如果你很需要在表中使用这样的功能，我建议大家使用触发器来实现。
CREATE TABLE t25
(s1 INT, s2 CHAR(5),
PRIMARY KEY (s1))
ENGINE=INNODB//

CREATE TRIGGER t25_bi
BEFORE INSERT ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

CREATE TRIGGER t25_bu
BEFORE UPDATE ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//
我只需要使用BEFORE INSERT和BEFORE UPDATE语句就行了，删除了触发器不会对表有影响，
同时AFTER的触发器也不能修改NEW的过程变量（transition variables）。为了激活触发器，
我执行了向表中的行插入s1＝0的数据，之后只要执行符合LEFT(s2,1) <> 'A'条件的动作都会失败：

*/

create table Course(
Cno char(12) primary key,
Cname varchar(50),
Cpno char(12),
Ccredit smallint ,
constraint fk1 foreign key(Cpno) references Course(Cno)
);

create table SC (
Sno char(10),
Cno char(12),
Grade smallint,
primary key(Sno,Cno),
constraint fk2 foreign key(Sno) references Student(Sno),
constraint fk3 foreign key(Cno) references Course(Cno)
);

alter table SC drop foreign key fk2 ;
alter table SC drop foreign key fk3 ;
alter table SC add constraint fk2 foreign key(Sno) references Student(Sno) on delete cascade on update cascade ;
alter table SC add constraint fk3 foreign key(Cno) references Course(Cno) on delete cascade on update cascade ;

-- Student表数据
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215121','李勇','男',20,'CS');
insert into Student values('200215122','刘晨','女',19,'CS');
-- insert into Student values('200215123','王敏','女',18,'MA'),('200215124','张立','男',19,'IS');
-- 注意上面的语句在MSSQL中不支持
insert into Student values('200215123','王敏','女',18,'MA');
insert into Student values('200215124','赵云','男',20,'IS');
insert into Student values('200215125','张立','男',19,'IS');
select *from Student;
-- delete from Student;

-- Course 表数据
insert into Course(Cno,Cname,Cpno,Ccredit) values('1','数据库' ,null,4);
insert into Course(Cno,Cname,Ccredit) values('2','数学',2);
insert into Course values('3','信息系统',null,4);
insert into Course values('4','操作系统',null,3);
insert into Course values('5','数据结构',null,4);
insert into Course values('6','数据处理',null,2);
insert into Course values('7','Pascal语言',null,4);
update Course set Cpno='5' where Cno='1' ;
update Course set Cpno='1' where Cno='3' ;
update Course set Cpno='6' where Cno='4' ;
update Course set Cpno='7' where Cno='5' ;
update Course set Cpno='6' where Cno='7' ;
select *from Course ;
-- delete from Course;

-- SC表数据
insert into SC values('200215121','1',92) ;
insert into SC values('200215121','2',85) ;
insert into SC values('200215121','3',88) ;
insert into SC values('200215122','2',90) ;
insert into SC values('200215122','3',80) ;
insert into SC values('200215123','1',85);
insert into SC values('200215123','5',89);
insert into SC values('200215124','2',90);
select *from SC;
-- delete from SC;