Oracle---Creating and Managing Tables

上一篇 / 下一篇  2010-03-30 21:30:58 / 个人分类:Oracle

1.create table directly
SQL> CREATE TABLE dept
  2     (deptno  NUMBER(2),
  3              dname    VARCHAR2(14),
  4              loc      VARCHAR2(13));
Table created.
 
2.Creating a Table by Using a Subquery
SQL> CREATE TABLE        dept30
  2     AS
 
  3             SELECT     empno, ename, sal*12 ANNSAL, hiredate
  4             FROM       emp
  5             WHERE      deptno = 30;
 
3.The ALTER TABLE Statement
Use the ALTER TABLE statement to:
Add a new column
Modify an existing column
Define a default value for the new column
 
ALTER TABLE dept30
  2  ADD                   (job VARCHAR2(9));
 
Guidelines for Adding a Column
You can add or modify columns, but you cannot drop them from a table.
You cannot specify where the column is to appear. The new column becomes the last column.
ALTER TABLE     dept30
MODIFY          (ename VARCHAR2(15));
 
 
4.Dropping a Table
The DROP TABLE statement removes the definition of an Oracle8 table. When you drop a table, the database loses all the data in the table and all the indexes associated with it.
Guidelines
All data is deleted from the table.
Any views or synonyms will remain but are invalid.
Any pending transactions are committed.
Only the creator of the table or a user with the DROP ANY TABLE privilege can remove a table.
The DROP TABLE statement, once executed, is irreversible. The Oracle Server does not question the action when you issue the DROP TABLE statement. If you own that table or have a high-level privilege, then the table is immediately removed. All DDL statements issue a commit, therefore making the transaction permanent.
 
5 Renaming a Table
Additional DDL statements include the RENAME statement, which is used to rename a table, view, sequence, or a synonym.
SQL> RENAME dept TO department;
Table renamed.
 
6.Truncating a Table
Another DDL statement is the TRUNCATE TABLE statement, which is used to remove all rows from a table and to release the storage space used by that table. When using the TRUNCATE TABLE statement, you cannot rollback row removal.
SQL> TRUNCATE TABLE department;
 
7.Adding a Comment to a Table
You can add a comment of up to 2000 bytes about a column, table, view, or snapshot by using the COMMENT statement. The comment is stored in the data dictionary and can be viewed in one of the following data dictionary views in the COMMENTS column:
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
SQL> COMMENT ON TABLE emp
  2  IS 'Employee Information';
Comment created.

TAG:

 

评分:0

我来说两句

Open Toolbar