Create and manage Oracle tables

Oracle create and manage tables/* ----------------------- create and manage tables --------------------------- */1. Data Types in Orcale: 1. NUMBER, DATE, VARCAHR, CLOB, BLOB 2, and table Creation

Create table Name (field name 1 field type [DEFAULT value], field name 2 Field Type [DEFAULT value],... field name N field type [DEFAULT value]); create table name AS (subquery) · if the subquery writes SELECT * FROM emp, table Structure and table content are copied together. If the subquery writes SELECT * from where 1 = 2 and adds a condition that never works, only the table structure is copied, do not copy content!

 

3. delete a form

Drop table Name

 

Iv. Table Modification

1. ADD a column: · alter table name ADD (DEFAULT value of the column name type ,...); alter table person ADD (address VARCHAR (200) DEFAULT 'no address'); 2. MODIFY a column: alter table name MODIFY (DEFAULT value of the column type)

 

· However, when modifying the table structure, if there is a long data in the corresponding field in the data, the table length cannot be reduced. 5. Rename the table [Orcale-specific]

· RENAME command: RENAME old table name TO new table name;

 

6. truncate the table and clear the table data, but you do not need to clear the data immediately, such as rollback or commit.

Truncate table name;

 

VII. Table constraint [Key]-primary key constraint: a primary key represents a unique identifier and cannot be empty or duplicated. unique constraint: Only one primary key constraint can be created in a table, if you do not want repeated values in other tables, you can use the unique constraint-check constraints: Check whether the content of a column is legal NUMBER (5)-non-empty constraints: not null-foreign key constraint: An Example of the operation to restrict two tables:

Drop table person; -- delete create table person (-- CREATE pid VARCHAR (18) primary key, name VARCHAR (200), age NUMBER (3) not null check (sex in ('male', 'female '), -- non-NULL constraints CHECK constraints birthday DATE, sex VARCHAR (2) DEFAULT 'male' CONSTRAINT person_pid_key primary key (pid); -- primary key constraint person_name_unique UNIQUE (name); -- unique constraint person_age_ck CHECK (age BETWEEN 0 AND 150) -- CHECK constraints ); 1. primary key constraint (primary key). You can add it when creating a table. By default, it cannot be blank and cannot be repeated! · You can also add the following command CONSTRAINT to the table after creating the table: the alias CONSTRAINT person_pid_pk primary key (pid) 2. non-null CONSTRAINT: like the primary key, it can be added during table creation, you can also add NOT NULL3 and UNIQUE constraint (UNIQUE): to indicate the UNIQUE constraint. You can also add the constraint when creating the table. 4. CHECK the constraint to determine whether the inserted content is valid. Example: age CHECK (sex in ('male', 'female ') age CHECK (age BETWEEN 0 AND 150) 5. Foreign KEY constraint (foregin key ): CONSTRAINT person_book_pid_fk foregin key (pid) REFERENCES person (pid) ondelete cascade; indicates cascading Deletion

 

7. Modify constraints:

· If a table has been created, you can add constraints to it. Create table person (pid VARCHAR (18), name VARCHAR (200), age NUMBER (3), birthday DATE, sex VARCHAR (2) DEFAULT 'male '); alter table name add constraint name CONSTRAINT type (CONSTRAINT field); naming convention: · primary key: primary key field _ PL · UNIQUE: primary key _ UK · CHECK: primary KEY _ CK · foreign key: parent field _ child field _ fkalter table person add constraint person_age CHECK (age BETWEEN 0 AND 150); alter table book add constraint person_book_pid_fk foreign key (pid) REFERENCES person (pid) ONDELETECASDE;

 

8. Modify ADD on the delete constraint to DROP.

ALTER TABLE person DROP CONSTRAINT person_age CHECK(age BETWEEN 0 AND 150);

 

9. ROWNUM (important) ROWNUM indicates the number given after the query. In fact, it is a column and a pseudo column, which exists in any table.

SELECT ROWNUM,ename FROM emp;    ROWNUM ENAME---------- ----------         1 ALLEN         2 WARD         3 JONES         4 MARTIN         5 BLAKE         6 CLARK         7 SCOTT         8 KING         9 TURNER        10 ADAMS        11 JAMES        12 FORD        13 MILLER

 

ROWNUM cannot use BETWEEN n AND m for interception. If you want to intercept data, you can only use subqueries.

SELECT * FROM (SELECT ROWNUM rn,e.* FROM emp e WHERE ROWNUM<=10) tempWHERE temp.rn>5;

 

In this way, data of 6 to 10 can be displayed.