Oracle Basic Statement (chapter seventh, database logical object Management)

indexes, manifested views, clusters, hash clusters, sequences, synonyms

1. Create a table

CREATE table < table name > (< column name 1> < data type >,......);

CREATE GLOBAL Temporary table < name > (< column name 1> < data type >,......) On COMMIT Delete ROWS tablespace < temp tablespace name >;--Create a transaction-level staging table, delete the data in the staging table after the transaction commits

CREATE GLOBAL Temporary Table < name > (< column name 1> < data type >,......) On COMMIT PRESERVE ROWS ;--Create session-level staging tables to delete data from temporary tables after the session ends

2. DESC < table name >;--view table structure

3, modify the table

ALTER table < table name > add (< column name > < data type >);--Add column

ALTER table < table names > RENAME column < original column name > to < new column name >;

ALTER table < table name > drop Column < column name >;--Delete columns

ALTER table < table name > set UNUSED(< column name >);--setting column is not available

ALTER table < table name > MODIFY (< column name 1> < data type >,......); --Modifying column types

4. Table renaming

RENAME < table name > to < new table name >;

5. Delete data from the table

DELETE Form < table name > WHERE < condition >;

6. Truncate the table

TRUNCATE table< Table name >;

7. Delete a table

DROP Table < name >;

8. Create and delete views

CREATE OR REPLACE View < view name > as <select clause >;

Drop View < view name >;--Delete

9. Create an index

CREATE Index < index name > on < table name > (< column name >);

DROP Index < index name >;

10. Modify the Index

ALTER Index < index name > unusable;--Set Index not available

ALTER Index < index name > rebuild;--rewrite using index

ALTER index< Index name > RENAME to < new name >;

11. Create, modify, and delete a solid view

CREATE materialized View < manifested view name > as <select statement >;

ALTER materialized View < manifested view name >......;

DROP materialized View < manifested view name >;

12, cluster (slightly)

13. Create a sequence

CREATE SEQUENCE < sequence name >

MINVALUE n| nominvalue--Minimum value is n| no minimum limit

MAXVALUE n| nomaxvalue-- maximum value is n| No maximum value limit

Start with n--initial value n

INCREMENT by n--interval n

cycle| nocycle-- Cycle | No loops

CACHE n| nocache--Cache size is n| no cache

order| Noorder;--whether orderly

14. Modify the sequence

ALTER SEQUENCE < sequence name > MAXVALUE 1000;--parameter is the same as previously created parameter　　　

15. Deleting a sequence

DROP SEQUENCE < sequence name >;

16. Use sequence

Using the sequence value, two pseudo-columns of Oracle Nextval and Currval are used, where the nextval pseudo-column is used to remove the next value from the specified sequence number, which is most commonly used, whereas the Currval pseudo-column refers to the current value of the specified sequence. Use the "< sequence name >.< pseudo-column name >" format when using both pseudo-columns, for example:

17. Create synonyms (that is, aliases for objects)

CREATE public synonym < synonym name > for < object name >;--object name can be table name, etc.

DROP public synonym < synonyms >;