Create and manage an Oracle note (9) Table

For a database, each table actually represents a database object, and the database object refers to all the operations defined by DDL, for example: tables, views, indexes, sequences, constraints, and so on all belong to object operations. Therefore, the creation of tables is the creation of objects, and object operations are mainly divided into the following three types of syntax:

• Create object: Create object name ...;
• Delete object: Drop object name ...;
• Modify Object: Alter object name ...;

I. Common data fields

Each data table is actually composed of several fields, and each field has its own data type. In Oracle, common data types include:

Bytes	Data Type	Keywords	Description
1	String	Varchar2 (N)	N indicates the maximum length that a string can store. Basically, about 200 pieces of content are saved.
2	Integer	Number (N)	Represents an integer up to N bits. Sometimes it can be replaced by an int.
3	Decimal	Number (n, m)	Where m is decimal, N-M is an integer, and sometimes float can be used
4	Date	Date	Storage date-time
5	Large text	Clob	Massive volumes of text (4 GB) can be stored, for example, three kingdoms performing arts and A Dream of Red Mansions
6	Large Object	Blob	Stores binary data, such as movies, MP3 files, images, and text.

Generally, varchar2 (), number, date, and clob are the most widely used in development, while BLOB fields are generally used less. First, blob can store 4 GB of binary data, however, the database is too large, and the reading is inconvenient;

Ii. Table Creation

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

Create TableTable Name (Field 1 Data Type[Default], Field 2 Data Type[Default],... ..., Field n Data Type[Default]);
 

Create a member table (member) with the following information: name, age, birthday, and personal profile.

Create TableMember (nameVarchar2(50)Default 'Anonymous', AgeNumber(3), Birthday dateDefaultSysdate, content clob );
 

After the table is created successfully, add data to the table as follows:

  insert   into  member (name, age, birthday, content)  values  ( '  JOHN   ',  20 , to_date ( '  1990-08-12   ',  '  yyyy-mm-DD   '), '   good guy   '); 

Insert IntoMember (age, content)Values(20,'Good guy');
 

Remember that the table is created as a database object, so the create syntax is used.

Iii. Table Replication

After learning the copy operation of a table, the complete operation syntax is provided below:

 
Create TableCopy table nameAsSubquery;
 

Example:Copy a table that only contains information about employees in 20 departments.

Create TableEmp20As Select * FromEMPWhereDeptno=20;
 

Example:Now we need to copy the table structure of the EMP table and do not want data-just write a condition that will never be satisfied.

 
Create TableEmpnullAs Select * FromEMPWhere 1=2;
 

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

4. rename a table

In Oracle databases, all data is actually saved using data dictionaries. For example, you have used the following query before:

 
Select * FromTab;
 

The above is a data dictionary. in Oracle, three types of data dictionaries are provided. The most common types are DBA _ and user _. Therefore, we can query a user_tables data dictionary as follows:

 
Select * FromUser_tables;
 

That is to say, all data in Oracle is saved by file, so all the content will be registered in the data dictionary. In this case, the so-called modification of the table name is actually equivalent to modifying a piece of data for Oracle, and the method for modifying the table name is as follows:

 
Rename old table nameToThe new table name;
 

Example:Rename a member table to a person table

Rename MemberToPerson;
 

However, this operation is a unique feature of Oracle databases, so you can understand it without further understanding.

V. Table Truncation

I have previously explained how to delete table data using the delete operation. However, this delete operation has its own characteristic: Transaction rollback can be performed, that is to say, the deleted data resources will not be released immediately. If you want to completely release all the resources occupied by a table (tablespace, index, etc.), you can use the truncation table syntax, the syntax is as follows:

 
Truncate TableTable Name;
 

Example:Truncation person table

 
Truncate TablePerson;
 

However, this syntax is only applicable to Oracle.

Vi. Table Deletion

The delete operation of a table refers to the deletion of database objects. The drop statement is used to delete a table. The syntax for deleting a table is as follows:

Drop TableTable Name;
 

Example:Delete the person table

 
Drop TablePerson;
 

VII. New Features of Oracle 10 GB: Flash back Technology

After 10 Gb of Oracle, the recycle bin function is provided to prevent users from accidentally deleting tables. The deleted tables are saved in a recycle bin by default, users can also restore tables through the recycle bin. Therefore, this technology is called flashback );

Example:View Recycle Bin

 
Show recyclebin;
 

At this time, we can find that all the deleted tables are saved in the recycle bin. Then we can use the following syntax to restore the table:

 
FlashbackTableTable NameToBeforeDrop;
 

Example:Restore the myemp table

FlashbackTableMyempToBeforeDrop;
 

Of course, you can also delete some data tables in the recycle bin directly. The syntax is as follows:

 
PurgeTableTable Name;
 

Example:Delete the person table in the recycle bin

 
PurgeTablePerson;
 

Example:Clear Recycle Bin

 
Purge recyclebin;
 

If you want to delete a table but do not want it to be added to the recycle bin, you can add the purge when deleting the table.

 
Drop TableMyemp purge;
 

This technology is only available after Oracle 10 Gb, and Oracle 11g also exists.

Problem:Now a tab table exists in the recycle bin and a tab table is created. Can it be recovered from the recycle bin?

A: it cannot be recovered.

8. Modify the table structure

If a created data table finds that its initial structure does not meet the later usage requirements, you can modify the table, the Modification Operation of the table is actually the modification operation of the database object, which is completed using the alter command. For example, there is now the following table:

 
Create TableMember (MidNumber, NameVarchar2(50));
 

Now you want to add fields to the table, so you can use the following syntax:

Alter TableTable NameAdd(Column Name Data Type[Default], Column Name Data Type[Default],...);
 

Example:Add fields to the member table

 
Alter TableMemberAdd(AgeNumber(3), Birthday dateDefaultSysdate );
 

If no default value is set for the added data column, the content of all existing data columns is null. If the default value is specified for the added column, all existing data columns are the default values.

You can also modify the existing table structure. The syntax is as follows:

Alter TableTable Name modify (column Name Data Type[Default], Column Name Data Type[Default],...);
 

Example:Define the default value of the Name field as anonymous

 
Alter TableMember modify (nameVarchar2(100)Default 'Anonymous');
 

Although the SQL syntax and the Oracle database all provide the Operation to modify the table structure, this operation will not be used if it is not available. For large databases, the database with the highest performance in the world is IBM DB2, but IBM DB2 itself has a platform restriction problem, so if it is a cross-platform database, it is the highest performance of the Oracle database.

In the IBM DB2 database, the table structure cannot be modified after the table is created. Therefore, do not modify the table structure as much as possible during development.

9. Questions

Now we need to create an nation table with a name field, which stores four records: China, the United States, Brazil, and the Netherlands. We need to perform the following operations through queries:

China, USA

China Brazil

Netherlands, China

United States and Brazil

United States and China

Netherlands, USA

The rest are the same. Now we need to create a new table and complete the query operation.

The main purpose of this question is not to compile the query, but to standardize the format of the database creation script. If you encounter similar requirements in the future, you must first compile a database creation script, the requirements for this script are as follows:

1. The file name suffix must be "*. SQL ";

2. Delete the corresponding data table first;

3. Compile the statement for creating a table;

4. Add test data;

5. Submit the transaction;

 --  1. delete a table  Drop   Table  Nation purge;  --  2. Create a table Create   Table  Nation (name  Varchar2 ( 50  ));  --  3. Test Data  Insert   Into Nation (name) Values ( '  China  '  );  Insert  Into Nation (name) Values ( '  USA  '  );  Insert   Into Nation (name) Values ( '  Brazil  '  );  Insert   Into Nation (name) Values ( '  Netherlands  '  );  --  4. Transaction submission  Commit ;

BenProgramIf you want to complete a table, it depends on Cartesian product.

 
SelectN1.name, n2.nameFromNation N1, nation N2WhereN1.name<>N2.name;
 

In addition, if there are some complicated queries during the interview in the future, we recommend that you write all the scripts.