维勒斯.txt

来自「关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品」· 文本 代码 · 共 32 行

1、建表
 学生 create table stu_choose (id int,name varchar2(10),sex varchar2(2),age integer,lid integer,score number(3,1));
 老师 create table lesson (id int,name varchar2(10),credit number(1,1));

2、添加约束
 主键 alter table stu_choose add constraint pk_stu_choose primary key(id);
  alter table lesson add constraint pk_lesson primary key(id);

 外键  alter table stu_choose add constraint fk_stu_choose foreign key(lid) references lesson (id);

 check约束  alter table stu_choose add constraint ck_stu_choose check(sex in ('男','女') and age>18 and age<60 and score>0);

 不为空约束 alter table lesson modify name not null;
alter table lesson modify credit not null;

savepoint p;

插入数据
 insert into stu_choose (id,score) values (1,90);
 insert into stu_choose (id,score) values (2,85.5);
 insert into stu_choose (id,score) values (3,80.5);
 insert into stu_choose (id,score) values (4,70);
 insert into stu_choose (id,score) values (5,63.5);  
 insert into stu_choose (id,name,score) values (6,'张三',47);
 insert into stu_choose (id,score) values (7,55);
 

查询加分仍不及格学生
 select name from stu_choose where score = (select score from stu_choose group by score having (score+10)<60);

commit;