郑国@6月6日.txt

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

建一个学生选课的关系
学生表（学生号，姓名，性别，年龄，课程编号，成绩）

create table stu(sno number,sname varchar2(8),age number(2),sex char(2),tno number(2));

Table created

select * from stu;

       SNO SNAME    AGE SEX TNO
---------- -------- --- --- ---


SQL> alter table stu add (grade number(2));

Table altered

SQL> create table topic(tno number(2),tname varchar(10),credit number(4));

Table created

SQL> select * from topic;

TNO TNAME      CREDIT
--- ---------- ------


课程表（课程编号，课程名字，课程学分）

SQL> create table topic(tno number(2),tname varchar(10),credit number(4));

Table created

SQL> select * from topic;

TNO TNAME      CREDIT
--- ---------- ------


主键约束-----学生号

SQL> alter table stu add constraint stu_primary primary key(sno);

Table altered


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

SQL> alter table topic add constraint topic_primary primary key(tno);

Table altered


外键约束-----课程编号，外键到课程表的课程编号
SQL> alter table topic add constraint topic_foreign foreign key(tno) references topic(tno);

Table altered


修改性别的字段长度
SQL> alter table stu modify (sex char(6));

Table altered


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



check约束 成绩: score> 0
SQL> alter table stu add constraint check_age check(age>0);

Table altered


check约束 年龄：18<age<60

SQL> alter table stu add constraint check_age check((18<age) and (age<60));

Table altered

外键约束-----课程编号，外键到课程表的课程编号