朱志明.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

创建学生表 
 create table MYSTUDENT
(
  STUID   INTEGER not null,
  STUNAME VARCHAR2(10),
  SEX     CHAR(2),
  AGE     INTEGER,
  OBJID   INTEGER,
  GRADE   INTEGER
);

添加主建约束

  alter table MYSTUDENT add constraint PK_STU_STUID primary key (STUID); 

添加外建约束

  alter table MYSTUDENT add constraint FK_OBJ_OBJID foreign key (OBJID) references OBJECT (OBJID);

添加检查约束

  alter table MYSTUDENT add constraint CHECK_AGE check (age>18 and age<60);

  alter table MYSTUDENT add constraint CHECK_GRADE check (grade>0);

  alter table MYSTUDENT add constraint CHECK_STU_SEX  check (sex in ('男','女'));

为学生表添加记录

 1.insert into mystudent values(1001,'a','男',20,1001,86);

 2.insert into mystudent values(1002,'aa','女',20,1001,85);

 3.insert into mystudent values(1003,'aaa','女',20,1002,75);

 4.insert into mystudent values(1004,'aaaa','男',21,1002,65);

 5.insert into mystudent values(1005,'aaaaa','男',22,1003,57);

 6.insert into mystudent values(1006,'b','女',22,1003,56);

 7.insert into mystudent values(1007,'bb','男',22,1004,87);

 8.insert into mystudent values(1008,'bbb','男',23,1004,81);

 9.insert into mystudent values(1009,'bbbb','男',23,1005,81);

 10.insert into mystudent values(1010,'bbbbb','女',21,1005,80);

创建课程表
create table OBJECT
(
  OBJID    INTEGER not null,
  OBJNAME  VARCHAR2(10) not null,
  OBJGRADE INTEGER not null
);

添加主建约束

  alter table OBJECT add constraint PK_OBJ_OBJID primary key (OBJID);

为课程表添加记录

 1. insert into object values(1001,'数学',20);

 2. insert into object values(1002,'语文',21);

 3. insert into object values(1003,'英语',21);

 4. insert into object values(1004,'数据库',22);

 5. insert into object values(1005,'程序开发',23);


SQL> select * from object;

                                  OBJID OBJNAME                                   OBJGRADE
--------------------------------------- ---------- ---------------------------------------
                                   1001 数学                                            20
                                   1002 语文                                            21
                                   1003 英语                                            21
                                   1004 数据库                                          22
                                   1005 程序开发                                        23

把成绩〈 60分的学生成绩 + 10分

  update mystudent set grade=grade+10 where grade>60;

查询成绩低于60分的人
   select stuname ,grade
   from mystudent
   where grade<60;

STUNAME                                      GRADE
---------- ---------------------------------------
aaaaa                                           57
b                                               56