Oracle表管理以及约束笔记

表管理

*!常用的数据类型!*
varchar/varchar2 表示一个字符串
number/ number(n): 表示一个整数，数字长度是n，可以使用INT
      \ number(m,n): 表示一个小数，数字小数位长度是n，整数长度是m-n，可以使用FLOAT代替
date 表示日期类型，日期要按照标准日期格式进行存放。
clob 大对象，表示大文本数据，一般可以存放4G的文本。
blob 大对象，表示二进制数据，最大可以存放4G，例如：电影、歌曲、图片

·表的建立。
 1·复制一张表
  create table 表名 as select * from emp;   ----复制emp表中的结构和内容
  create table 表名 as select * from emp where 1=2;----复制结构不复制内容
 2·创建一张表。create 命令
  ________________范例____________________
  create table person
  (
          pid varchar2(18),
          name varchar2(200),
          age number(3),
          birthday date,
          sex varchar2(2)default '男'
  );
  ________________________________________
·删除表
drop 删除表。

·修改表（了解） 一般很少修改表 在IBM 的DB2中根本没有alter指令
 *!表中存在大量记录，删除的话 是不是很麻烦？!*  有命令：
 ·增加列：alter指令+table+表名称+add
  __________________________范例__________________________________
  alter table person add(address varchar(200) default '暂无地址');
  ________________________________________________________________
 ·修改列。alter指令+table+表名称+modify+(列信息); 
 
·表重命名(Oracle独有的命令)
 rename … to …  只能在Oracle中使用

·截断表
 清空一张表 不能回滚，
 需要截断表语法：
 truncate +表明

 

   ############约束############
主要分为五种：
 ·主键约束：表示唯一的表示，本身不能为空。 *例如*：身份证编号、
 ·唯一约束：在以个表中只允许建立以个主键约束而其他不想出现重复的值的话，则可以使用唯一约束。
 ·检查约束：检查一个列的内容是否合法。  *例如*：性别只能是男、女、中性。
 ·非空约束，就好像姓名这样的字段就不能为空。
 ·外间约束，在两张表中间进行约束操作。

1·主键约束（primary key）
 ·一般在ID上使用，而且本身已经默认内容不能为空。可以在建立表的时候指定。
 _________________________范例__________________________________
 create table person
  (
          pid varchar2(18) primary key,
          name varchar2(200),
          age number(3),
          birthday date,
          sex varchar2(2)default '男'
  );
 ________________________________________________________________

 ·可以用constraint来指定约束的名称
 _________________________范例__________________________________
 create table person
  (
          pid varchar2(18) ,
          name varchar2(200),
          age number(3),
          birthday date,
          sex varchar2(2)default '男',
          constraint person_pid_pk primary key (pid)
  );
 ________________________________________________________________

2·非空约束（not null）
 
 _________________________范例__________________________________
 create table person
  (
          pid varchar2(18) ,
          name varchar2(200) not null ,
          age number(3) not null ,
          birthday date,
          sex varchar2(2)default '男',
          constraint person_pid_pk primary key (pid)
  );
 ________________________________________________________________
3·唯一约束（unique）
 表示一个字段中的内容是唯一的，其他列不允许重复。
 _________________________范例__________________________________
 create table person
  (
          pid varchar2(18) ,
          name varchar2(200)  not null ,
          age number(3)   not null ,
          birthday date,
          sex varchar2(2)  default '男',
          constraint person_pid_pk primary key (pid)
  );
 ________________________________________________________________

4·检查约束（check）
 使用检查约束来判断一个列中插入的内容是否合法、例如年龄的取值范围

 _________________________范例__________________________________
 create table person
  (
          pid varchar2(18) ,
          name varchar2(200)  not null ,
          age number(3)   not null  check(age between 0 and 150),
          birthday date,
          sex varchar2(2)  default '男' check(sex in ('男','女','中性')),
          constraint person_pid_pk primary key (pid)
  );
 ________________________________________________________________
  上面语句 优化自定义 约束名称
  create table person
  (
          pid varchar2(18) ,
          name varchar2(200)  not null ,
          age number(3)   not null  ,
          birthday date,
          sex varchar2(2)  default '男' ,
          constraint person_pid_pk primary key (pid),
          constraint person_name_uk  unique(name),
          constraint person_age_ck check(age between 0 and 150),
          constraint person_sex_ck check(sex in ('男','女','中性'))
  );_________________________________________________
5·主-外键约束（foreign key）
 之前的约束都是针对一张表的约束，这个主-外键约束是针对两张表的约束。
 
 范例：书这个表中的一本书属于人这个表中的一个人。
 

  (1)·首先插入两张表。
drop table person;
drop table book;
create table person
  (
          pid varchar2(18) ,
          name varchar2(200)  not null ,
          age number(3)   not null  ,
          birthday date,
          sex varchar2(2)  default '男' ,
          constraint person_pid_pk primary key (pid),
          constraint person_name_uk  unique(name),
          constraint person_age_ck check(age between 0 and 150),
          constraint person_sex_ck check(sex in ('男','女','中性'))
  );
 
create table book
(
          bid number primary key not null ,
          bname varchar(30),
          bprice number(5,2),
           pid varchar2(18)
);
  (2)·插入完成两张表之后，下一步插入一些数据。
insert into book(bid ,bname ,bprice,pid) values ( 1,'Java Se' ,'59','111111');
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values ('111111','张三',30,to_date('1987-12-27','yyyy-mm-

dd'),'女');
 
  (3)·插入以下数据
insert into book(bid ,bname ,bprice,pid) values ( 1,'Java Se' ,'59','000000');
*!注意!*此编号的人根本不存在。数据也就不应该插入。
*!注意!*此时应该用到主-外键关联，关联之后 字表 的数据跟随着 父表 的数据内容

 主-外键语法：foreign key(字段) reference 表名(字段)后者表作为父表 后者表明是指定字段作为父表的字

段 book 表的创建有所改变，变成如下
 drop table book;
 create table book
(
          bid number primary key not null ,
          bname varchar(30),
          bprice number(5,2),
          pid varchar2(18) ,
          constraint person_book_pid_fk foreign key(pid) references person(pid)
);
再插入数据
此时就不会出现找不到对应数据的情况。这就是主外键。
 ·在字表中设置的外键在父表中必须是主键。
 ·删除时应该先删除子表，再删除父表。
 drop table book; book表是父表。
 drop table person; person表是字表。
可以使用强制删除手段，但这个做法一般不使用。
 ________范例______________________
 drop table book cascade constraint;不管约束 直接删除。
 __________________________________

 ·级联删除
  建立主外键连接的时候可以加上 on delete  cascade 删的时候连同字表的对应数据一起删除掉。
  这是外键的完整语法。

 
   ############修改约束#######（了解）##

·添加约束
alter  表名称 add constraint 约束名称 约束类型（字段）;

 

 

    *!重点!*   ROWNUM
rownum：表示行号、实际上这此是一个列，但这个列是一个伪列，此列可以在每张表中出现。
  _____________范例______________________
  select rownum ,empno,ename,job from emp
  where rownum <=5;只显示前五条
  _______________________________________

想进行中间的截取，只能使用子查询。
__________________________范例________________显示中间五条。_____________________
select * from
(select rownum rn ,empno,ename,job from emp where rownum <=10)temp where temp.rn>5;
_______________________________________________________________________________

      

 

    学习以上内容实用到的sql语句如下。

--conn scott/tiger@orcl_localhost;
--create table  temp as (select * from emp where 1=2);
--select * from temp;
/*
create table person
(
        pid varchar2(18),
        name varchar2(200),
        age number(3),
        birthday date,
        sex varchar2(2)default '男'
);
*/
--drop table person;
/*
create table person
(
        pid varchar2(18),
        name varchar2(200),
        age number(3),
        birthday date,
        sex varchar2(2)default '男',
        address varchar(200)
);
*/
--drop table person;
create table person
(
        pid varchar2(18),
        name varchar2(200),
        age number(3),
        birthday date,
        sex varchar2(2)default '男'
);
--alter table person add(address varchar(200) default '暂无地址');
/*
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values ('1111111111','张三',30,to_date('1987-12-27','yyyy-mm-dd'),'女');
insert into person (PID,NAME,AGE,BIRTHDAY) values ('2222222222','李四',31,to_date('1987-12-26','yyyy-mm-dd'));
*/
--select* from person;
--alter table person modify(name varchar2(10) default '无名氏');
--insert into person (PID,AGE,BIRTHDAY,SEX) values ('2222222222',31,to_date('1987-12-26','yyyy-mm-dd'),'女');
--select * from person;
--conn scott/tiger@orcl_localhost;
--drop table person;
/*
 create table person
  (
          pid varchar2(18) primary key,
          name varchar2(200),
          age number(3),
          birthday date,
          sex varchar2(2)default '男'
  );
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values ('1111111111','张三',30,to_date('1987-12-27','yyyy-mm-dd'),'女');
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values ('1111111111','李四',31,to_date('1987-12-26','yyyy-mm-dd'),'男');
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values (null,'李四',31,to_date('1987-12-26','yyyy-mm-dd'),'男');
*/
/*
drop table person;
 create table person
  (
          pid varchar2(18) ,
          name varchar2(200),
          age number(3),
          birthday date,
          sex varchar2(2)default '男',
          constraint person_pid_pk primary key (pid)
  );*/
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values ('1111111111','张三',30,to_date('1987-12-27','yyyy-mm-dd'),'女');
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values ('1111111111','李四',31,to_date('1987-12-26','yyyy-mm-dd'),'男');
/*
drop table person;
 create table person
  (
          pid varchar2(18) ,
          name varchar2(200) not null ,
          age number(3) not null ,
          birthday date,
          sex varchar2(2)default '男',
          constraint person_pid_pk primary key (pid)
  );
*/
/*
drop table person;
 create table person
  (
          pid varchar2(18) ,
          name varchar2(200) unique  not null ,
          age number(3) not null ,
          birthday date,
          sex varchar2(2)default '男',
          constraint person_pid_pk primary key (pid)
  );
 
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values ('1111111111','张三',30,to_date('1987-12-27','yyyy-mm-dd'),'女');
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values   ('2222222222','张三',31,to_date('1987-12-26','yyyy-mm-dd'),'男');
*/
drop table person;
create table person
  (
          pid varchar2(18) ,
          name varchar2(200)  not null ,
          age number(3)   not null  check(age between 0 and 150),
          birthday date,
          sex varchar2(2)  default '男' check(sex in ('男','女','中性')),
          constraint person_pid_pk primary key (pid)
  );
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values ('1111111111','张三',300,to_date('1987-12-27','yyyy-mm-dd'),'女');
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values ('1111111111','张三',30,to_date('1987-12-27','yyyy-mm-dd'),'无');
 drop table person;
 create table person
  (
          pid varchar2(18) ,
          name varchar2(200)  not null ,
          age number(3)   not null  ,
          birthday date,
          sex varchar2(2)  default '男' ,
          constraint person_pid_pk primary key (pid),
          constraint person_name_uk  unique(name),
          constraint person_age_ck check(age between 0 and 150),
          constraint person_sex_ck check(sex in ('男','女','中性'))
  );
/*
drop table person;
drop table book;
create table person
  (
          pid varchar2(18) ,
          name varchar2(200)  not null ,
          age number(3)   not null  ,
          birthday date,
          sex varchar2(2)  default '男' ,
          constraint person_pid_pk primary key (pid),
          constraint person_name_uk  unique(name),
          constraint person_age_ck check(age between 0 and 150),
          constraint person_sex_ck check(sex in ('男','女','中性'))
  );
 
create table book
(
          bid number primary key not null ,
          bname varchar(30),
          bprice number(5,2),
           pid varchar2(18)
);
*/
 /*drop table book;
 create table book
(
          bid number primary key not null ,
          bname varchar(30),
          bprice number(5,2),
          pid varchar2(18)
          constraint person_book_pid_fk foreign key(pid) references person(pid)
);
insert into book(bid ,bname ,bprice,pid) values ( 1,'Java Se' ,'59','111111');
insert into person (PID,NAME,AGE,BIRTHDAY,SEX) values ('111111','张三',30,to_date('1987-12-27','yyyy-mm-dd'),'女');
insert into book(bid ,bname ,bprice,pid) values ( 1,'Java Se' ,'59','000000');
*/
drop table book cascade constraint;
/* drop table book;
 create table book
(
          bid number primary key not null ,
          bname varchar(30),
          bprice number(5,2),
          pid varchar2(18) ,
          constraint person_book_pid_fk foreign key(pid) references person(pid) on delete cascade 
);
*/

 --rownum使用 这个是重点。
select * from
(select rownum rn ,empno,ename,job from emp where rownum <=10)temp where temp.rn>5;

引用地址：http://haheihei.blogbus.com/logs/61174559.html
博客地址http://haheihei.blogbus.com