Creation and management of Oracle tables

In fact, for a database, each table represents a database object, and the database object refers to all the operations of the DDL definition, for example: tables, views, indexes, sequences, constraints, and so on, all belong to the object, so the establishment of the table is the establishment of objects, and the operation of the object is divided into the following three types of syntax:

• Creating object: Create Object Name ... ；
• Delete object: Drop Object Name ... ；
• Modify object: ALTER Object Name ... ；

First, the commonly used data fields

Each data table is actually composed of several fields, each of which has its corresponding data type, and in Oracle, there are several common data types:

№	Data type	Key words	Describe
1	String	VARCHAR2 (N)	where n represents the maximum length that a string can hold, essentially saving 200 or so of the content
2	Integer	Number (n)	Represents an integer of up to n bits, and sometimes an int can be used instead
3	Decimal	Number (N,M)	where M is the decimal place, the n-m is the integer digit, and sometimes float can be used instead of
4	Date	DATE	Storage Date-time
5	Large text	Clob	Can store a large amount of text (4G), such as storage "Three Kingdoms Performing Arts", "dream of Red Mansions"
6	Large Object	Blob	Store binary data such as: movies, MP3, pictures, text

Generally used in the development of the most: VARCHAR2 (), number, DATE, CLOB, and for the BLOB field is generally less used, the first blob can be stored in 4G binary data, but after the storage, one is the database is too large, the second is to read inconvenient;

Second, the creation of the table

If you want to create a table now, you can use the following syntax:

CREATE Table Table name (    field 1 data type [default defaults],    field 2 data type [default defaults],    ...,    field n data type [default defaults]);

Create a member table below (member) with the following saved information: Name, age, birthday, personal profile.

CREATE TABLE member (     name VARCHAR2 () default ' anonymous ', age number     (3),     birthday DATE default sysdate,     Content CLOB);

After the table is created successfully, the following starts adding data to the table:

INSERT into member (name,age,birthday,content) VALUES (' Zhang San ', 20,to_date (' 1990-08-12 ', ' yyyy-mm-dd '), ' a good person ');

INSERT into member (age,content) VALUES (20, ' a good person ');

Be sure to remember again that the creation of the table is the creation of the database object, so the create syntax is used.

Iii. Duplication of tables

Before you learned the copy operation of a table, the following is the complete syntax for the operation:

CREATE table Copy table name as subquery;

Example: copy a table with 20-door employee information

CREATE TABLE Emp20 as SELECT * from EMP WHERE deptno=20;

Example: It is now required to copy the table structure of the EMP table, not the data-write a condition that can never be satisfied.

CREATE TABLE Empnull as SELECT * from EMP WHERE 1=2;

But the syntax above is only the operations supported by the Oracle database, and there are some differences in the syntax of the other databases.

Iv. Renaming a table

In Oracle database, all data is actually saved through a data dictionary, for example, a query that was previously used as follows:

SELECT * from Tab;

The above is a data dictionary, and in Oracle, there are three types of data dictionary, most commonly used is: Dba_, User_, so the following query a User_tables data dictionary:

SELECT * from User_tables;

That is, all of the data in Oracle is saved according to the file, then all the content is registered in the data dictionary, in which case the so-called modification table name is actually equivalent to modifying a single piece of data for Oracle, and the method of modifying the table name is as follows:

RENAME the old table name to the new table name;

Example: renaming a member table to a person table

RENAME member to person;

But this kind of operation is a unique feature of Oracle database, so we can understand it without having to grasp it deeply.

V. Truncation of tables

Previously explained a delete table data operation, using a delete operation, but this delete operation itself has a feature, that is, the transaction can be rolled back, that is, after the deletion does not immediately release the data resources, if you now want to completely release a table of all the resources (table space, Index, and so on) you can use the syntax for truncating the table, which is as follows:

TRUNCATE table name;

Example: truncating the person table

TRUNCATE TABLE person;

But this syntax itself is all Oracle, so just understand.

Vi. deletion of the table

The delete operation of a table refers to the deletion of a database object, and since it is deleted, the syntax for dropping the table is as follows:

DROP table name;

Example: Delete Person table

DROP TABLE person;

Vii. new features of Oracle 10g: Flash Back technology

After Oracle 10g, in order to prevent the user's error delete table operation, specifically provides the function of the Recycle Bin, the user's deleted table is saved in one Recycle Bin by default, and the user can recover the table through Recycle Bin, so this technique is called flashback (FLASHBACK);

Example: Viewing the Recycle Bin

SHOW RecycleBin;

This time you can find that all the deleted tables are saved in the Recycle Bin, then the following syntax can be used to restore the table:

FLASHBACK table name to before DROP;

Example: Recovering a myemp table

FLASHBACK TABLE myemp to before DROP;

Of course, you can now delete some of the data tables in the Recycle Bin directly, the syntax is as follows:

PURGE table name;

Example: Deleting a person table in the Recycle Bin

PURGE TABLE person;

Example: emptying the Recycle Bin

PURGE RecycleBin;

If you want to delete a table now and do not want it to go into the Recycle Bin, you can add purge when you delete it.

DROP TABLE myemp PURGE;

This technique is only available after Oracle 10g, and Oracle 11g is present.

problem: Now that there is a tab table in the Recycle Bin, and then a tab table is created, can you recover from the Recycle Bin?

A: cannot be recovered.

Viii. Modifying table structure

If a well-established data table, found that its initial structure is not satisfied with the late use requirements, you can make the table modification operation, and the table modification operation is actually the database object modification operation, using the ALTER command to complete, for example, now has the following table:

CREATE TABLE member (     mid number,     name VARCHAR2 (50));

Now you want to add the fields to the table, so you can do this at this point in the following syntax:

ALTER table name ADD (column name data type [default defaults], column name data type [default value],...);

Example: Adding a field to a member table

ALTER TABLE member ADD (age number (3), birthday DATE DEFAULT sysdate);

If the added data column does not have a default value, the contents of all columns of the existing data are null, and if the added column specifies default defaults, all existing data columns are the default values for the settings.

Now you can also modify an existing table structure with the following syntax:

ALTER table name MODIFY (column name data type [default defaults], column name data type [default defaults],...);

Example: define the default value for the Name field as anonymous

ALTER TABLE member MODIFY (name VARCHAR2 (+) DEFAULT ' anonymous ');

Although the operation of modifying the table structure is given in the SQL syntax as well as in the Oracle database, this operation can be used without using it, and from a large database, the world's highest performance database is IBM DB2, but the IBM DB2 itself has a platform limitation problem, So if it's a cross-platform database, the Oracle database has the highest performance.

In the IBM DB2 database is not allowed to modify the table structure, that is: after the completion of the table can not be modified, so later in the development as far as possible do not modify the table structure.

Nine, study questions

Now requires the establishment of a nation table, the table has a Name field, which holds four records: China, the United States, Brazil, the Netherlands, the request through the query to achieve the following operation effect:

China USA

China Brazil

China Netherlands

United States Brazil

United States China

United States Netherlands

The rest, and so on, now requires that a new table be created and the operation of this query completed.

The main purpose of this topic is not to write the query, but to standardize the format of the database creation script, in the future, as long as it meets the similar requirements, you must first write a database creation script, the requirements of this script are as follows:

1, the file name suffix of this document must be "*.sql";

2, first delete the corresponding data sheet;

3, write the statement to create the table;

4, increase the test data;

5, the submission of services;

--1, delete table drop tables Nation purge;--2, CREATE Table Nation (    name VARCHAR2 (50)),--3, test data insert into nation (name) VALUES (' China '); insert into nation (name) values (' us '); insert into nation (name) VALUES (' Brazil '); insert into nation (name) VALUES (' Holland ');--4, transaction commit commit;

If the program is to be completed relying on Cartesian product, it belongs to the self-correlation of the table.

SELECT N1.name,n2.namefrom Nation n1,nation n2where n1.name<>n2.name;

And later if the interview, there are some complex queries, the proposal to write all the script.

Creation and management of Oracle tables