creating and manipulation table

1 Table in the Oracle Database
1) User Tables:
  a Are a collection of tables created and maintained by the user
  b Contain user information
 2) Data Dictionary
  a is a collection of table created and maintained by the Oracle Server
  b Contain database information
2 Querying the Data Dictionary
 1)see the names of the table owned by the user
   select table_name from user_tables;
 2) view distinct object types ownered by the user
   select distinct object_type from user_object;
 3) view tables ,view ,synonyms and sequences owned by the user
   select * from user_catalog
3 Creating a Table by Ussing a Subquery Syntax
 create table tt3
 as
 select * from authors
4 Teh alter table Statement
 1) Add a new column
  alter table tt2
  add(fname varchar2(20) default 'unkonown',
      address varchar2(30) null);
 2)Modigying a Column's data type size and default value
  alter table dept80
  modigy (last_name varchr2(30))
  A change to thee default value affects onlly subsequent insertion to the table
  3) drop a column
  alter table dept80
  drop column job_id;
  The set unseed Option
   a you use the set unused optoin to mark one or more columns as unused
   b you use the drop unused colimns options to remove the columns that are marked as
   as unused
   alter table tt2
   set unused colun fnamel;
   alter table table
   drop unused columns
5 Dropping a Table
 1) All data and structure in the table is deleted
 2) Any pending transaction are committed
 3) All indexes are dropped
 4) You cannot roll back the drop table statement
6 Changing the Name of an Object
  rename dept to detail_dept;
  you must be the owner of the object
7 Truncate a Table
  Remove all rows from the table
  release the storage space used by that table
  you cannot rollback row when using truncate
  alternatly ,you can remove row by using delete statement

posted on 2006-09-25 17:59 康文 阅读(195) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2006年9月>
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

导航

统计

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

搜索

最新评论

阅读排行榜

评论排行榜