create table student
(
sno char(20) primary key,
sname char(20) not null,
ssex char(20),
sage int,
sdept char(20)
);
create table course(
cno char(20) primary key,
cname char(20) not null,
cpno char(20),
ccredit int ,
foreign key(cpno) references course(cno)
);
create table sc(
sno char(20) not null,
cno char(20) not null,
grade int,
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);
select * from student;
一、实体完整性
1.插入student表 当id一样时
insert into student values(2,'zhangsan','男',25,'cs');
insert into student values(2,'zhangsan','男',25,'cs');
insert into student(sname) values('zhangsan');
2.更新student表 将名字为lisi的sno修改为(已结有sno唯一的数据)
UPDATE student SET sno = 1 WHERE sname = 'lisi' ;
二、参照完整性
参照表SC 被参照表STUDENT
1.插入sc表
insert into sc values (1,2,60);
插入课程表
insert into course values(1,'数据库原理',null,8);
满足条件
insert into sc values (1,1,60);
select * fromsc;
2.修改sc表中外码值
UPDATE sc SET sno = 9 WHERE grade = 60 ;
3.删除student表中的元组
delete student where sno=1;
4.修改student表中的主码值
UPDATE student SET sno = 9 WHERE sname = 'zhangsan' ;
三、用户定义的完整性
(1)不允许空值
insert into sc values (1,null,60);
(2)列值唯一
create table dept(deptno int,dname char(20) unique not null , loc char(20),primary key (deptno));
insert into dept values (1,'信息','包头青山区');
insert into dept values (2,'信息','包头昆区');
(3)用check短语指定值应该满足的条件
create table stu(sno char(9) primary key,sname char(20) not null , ssex char(2) check (ssex in('男','女')) ,sage int ,sdept char(20) );
insert into stu values ('1','zhangsan','无',25,'CS');
(4)元组上的约束条件
create table stu1(sno char(10),sname char(10) not null , ssex char(20) , sage int , sdept char(20), primary key (sno), check (ssex='女' or sname not like 'Ms.%'));
insert into stu1 values('1','zhangsan','女',25,'CS');
insert into stu1 values('2','zhangsan','男',25,'CS');
insert into stu1 values('3','Ms.zhangsan','女',25,'CS');
insert into stu1 values('3','Ms.zhangsan','男',25,'CS');