数据库学习笔记(三)

create table Student
( Sno char(9) primary key,		# 在列级定义主码
  Sname char(20) not null,
  Ssex char(2)
);

create table Student
( Sno char(9),
  Sname char(20) not null,
  Ssex char(2),
  primary key(Sno)			   # 在表级定义主码
);

# 如果是要将属性组定义为主码，只能在表级定义主码
create table SC
( Sno char(9),
  Cno char(4),
  Grade smallint,
  primary key(Sno, Cno)		# 属性组定义为主码，只能在表级定义主码
);

create table SC
( Sno char(9) not null,
  Cno char(4) not null,
  Grade smallint,
  primary key(Sno, Cno),
  foreign key(Sno) references Student(Sno),
  foreign key(Cno) references Course(Cno)	# 在表级定义参照完整性
);

create table SC
( Sno char(9) not null,
  Cno char(4) not null,
  Grade smallint not null
  primary key(Sno, Cno)		# Sno、Cno、Grade属性不允许取空值
)

create table DEPT
( Deptno numeric(2),
  Dname char(9) unique not null,	# 要求Dname列值唯一，并且不能取空值
  primary key(Deptno)
)

create table Student
( Sno char(9) primary key,
  Sname char(8) not null,
  Ssex char(2) check(Ssex in ('男', '女'))  # Ssex只允许是'男'或'女'
)

create table Student
( Sno char(9),
  Sname char(8) not null,
  Ssex char(2),
  primary key(Sno),
  check(Ssex='女' or Sname not like 'MS.%')		# 当学生的性别是男时，名字不能以MS.开始
)

create table Student
( Sno numeric(6)		# 下面有完整性约束命名子句，所以没有逗号
  constraint C1 check (Sno between 9000 and 9999),	# 要求学号在9000~9999之间
  Sname char(20),
  constraint C2 not null,
  Sage numeric(3)
  constraint C3 check(Sage < 30),
  constraint StudentKey primary key(Sno)		# 主码约束，上面的C1、C2、C3都是列级约束
)

alter table Student
drop constraint C3;		# 删除对年龄的限制

# 修改表的约束条件方法：先删除原来的约束条件，再增加新的约束条件
alter table Student
drop constraint C1;
alter table Student
add constraint C1 check(Sno between 1000 and 9999);

create assertion ASSE_SC_CNUM1
check(60 >= all(select count(*) from SC
                group by Cno)
     );		# 限制每一门课程最多60名学生选修

# 限制每个学期每一门课程最多60名学生选修
# 先增加一个学期属性
alter table SC add Team;

# 定义断言
create assertion ASSE_SC_CNUM2
check(60 >= all(select count(*) from SC
                group by Cno, Team)
     );

# 当对表SC的Grade属性进行修改时，若分数增加了10% 则将这次的操作记录下来
create trigger SC-T
after update of Grade on SC		# 分数发生修改后激活触发器
referencing
	old row as OldTuple			# 把引发事件之前的值改名为OldTuple
	new row as NewTuple			# 把引发事件之后的值改名为NewTuple
for each row		# 行级触发器，即每执行一次Grade的更新，下面的规则就会执行一次
when(NewTuple.Grade >= 1.1 * OldTuple.Grade)
	insert into SC_U(Sno, Cno, OldGrade, NewGrade)	# 满足条件，把这次的操作记录下来
	values(OldTuple.Sno, OldTuple.Cno, OldTuple.Grade, NewTuple.Grade)

# 将每次对表Student的插入操作所增加的学生个数记录下来
create trigger Student_Count
after insert on Student
referencing
	new table as Delta
for each statement
	insert into StudentInsertLog(Numbers)
	select count(*) from Delta

# 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元
create trigger Insert_Or_Update_Sal
before insert or update on Teacher		# 插入工资前，或者更改工资前激活触发器
for each row
begin		# 定义触发动作体，是一个PL/SQL过程块
	if (new.Jog = '教授') and (new.Sal < 4000)
	then new.Sal := 4000;
	end if;
end;		# 触发动作体结束