谢妮娜.txt

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

建一个学生选课的关系
学生表（学生号，姓名，性别，年龄，课程编号，成绩）
sQL> create table xnn_student (sid int,name varchar2(20),sex char(2),courseno int, grade number);

Table created

SQL> desc xnn_student
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
SID      INTEGER      Y                         
NAME     VARCHAR2(20) Y                         
SEX      CHAR(2)      Y                         
COURSENO INTEGER      Y                         
GRADE    NUMBER       Y           
              
SQL> alter table xnn_student add age number(3);

Table altered


建课程表（课程编号，课程名字，课程学分）
SQL> create table xnn_course (coureno int,cname varchar2(20),cmark number(3));

Table created

SQL> desc xnn_course
Name    Type         Nullable Default Comments 
------- ------------ -------- ------- -------- 
COURENO INTEGER      Y                         
CNAME   VARCHAR2(20) Y                         
CMARK   NUMBER(3)    Y               

约束： 主键约束-----学生号；
SQL> alter table xnn_student add constraint pri_xstu_sid primary key(sid);

Table altered

约束： 主键约束----课程编号

SQL> alter table xnn_course add constraint pri_xc_courseno primary key(courseno);

Table altered

外键约束-----课程编号，外键到课程表的课程编号
SQL> alter table xnn_student add constraint fk__courseno foreign key(courseno) references xnn_course(courseno);

Table altered


check约束----性别:值为‘男’，‘女’
       
SQL> alter table xnn_student add constraint check_xsex check (sex in('男','女'));

Table altered

check约束----   年龄：18<age<60  

SQL> alter table xnn_student add constraint check_xage check (age between 18 and 60);

Table altered

check约束---- 成绩: score> 0
SQL> alter table XNN_STUDENT
  2    add constraint CHECK_XGRADE
  3    check (grade>0);

Table altered

不为空约束---课程名字，
SQL> alter table xnn_course modify cname not null;

Table altered

不为空约束---课程学分
SQL> alter table xnn_course modify cmark not null;

Table altered

给学生表 插入10行记录
SQL> insert into xnn_student values(1,'李虎','男',1001,30,22);

1 row inserted

SQL> insert into xnn_student values(2,'黄娟','女',1001,62,19);

1 row inserted

SQL> insert into xnn_student values(3,'张鹏','男',1003,55,21);

1 row inserted

SQL> insert into xnn_student values(4,'黄亚非','男',1002,90,21);

1 row inserted

SQL> insert into xnn_student values(5,'蒋怀玉','女',1005,45,25);

1 row inserted

SQL> insert into xnn_student values(6,'刘华','女',1004,78,23);

1 row inserted

SQL> insert into xnn_student values(7,'谢靖康','男',1002,49,28);

1 row inserted

SQL> insert into xnn_student values(8,'李小红','女',1004,10,30);

1 row inserted

SQL> insert into xnn_student values(9,'王晓','女',1005,100,20);

1 row inserted

SQL> insert into xnn_student values(10,'曾科','男',1004,88,26);

1 row inserted


给课程表插入5行记录

SQL> insert into xnn_course values(1001,'物理',30);

1 row inserted

SQL> insert into xnn_course values(1002,'数学',40);

1 row inserted

SQL> insert into xnn_course values(1003,'语文',40);

1 row inserted

SQL> insert into xnn_course values(1004,'地理',20);

1 row inserted

SQL> insert into xnn_course values(1005,'生物',20);

1 row inserted


把成绩〈 60分的学生成绩 + 10分，然后查下谁不及格

SQL> update xnn_student set grade=grade+10 where grade<60;

5 rows updated

SQL> select name,grade from xnn_student where grade<60;

NAME                      GRADE
-------------------- ----------
李虎                         40
蒋怀玉                       55
谢靖康                       59
李小红                       20