-- 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.asphttp://zhidao.baidu.com/question/187757031.htmlMySQL的视图不支持CHECK，我个人是很希望它能支持的。但如果你很需要在表中使用这样的功能，我建议大家使用触发器来实现。CREATE TABLE t25(s1 INT, s2 CHAR(5),PRIMARY KEY (s1))ENGINE=INNODB//CREATE TRIGGER t25_biBEFORE INSERT ON t25FOR EACH ROWIF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//CREATE TRIGGER t25_buBEFORE UPDATE ON t25FOR EACH ROWIF 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;
