Application of tables and views in Oracle databases

　Oracle DatabaseIt is a relational database management system supported by many people. It can process large volumes of data and has many applications in the network.Oracle DataThe most basic objects are tables and views. A table consists of records (row) and fields (column). It is the structure of data stored in the database. To store and manage data, you must first create a table in the database, that is, the field (column) Structure of the table. With the correct structure, you can use data operation commands to insert or delete records in tables or modify records. Other types include constraints, sequences, functions, stored procedures, packages, and triggers. Database Operations can basically be attributed to operations on Data Objects. Understanding and understanding Oracle database objects is a shortcut to learning Oracle.

I. Table Creation

1. Create Table syntax

TABLE creation requires the create table system permission. The basic TABLE creation syntax is as follows:

Create table Name

COLUMN Name Data Type [DEFAULT expression] [column constraint],

[Table constraint]

[TABLE_PARTITION_CLAUSE]

);

It can be seen that the most important thing to create a table is to describe the table name, column name, column data type and width. Multiple columns are separated by commas. You can use Chinese or English as the table name and column name. The maximum table name length is 30 characters. In the same user, the table name cannot be duplicated, but the names of different user tables can be duplicated. In addition, the table name cannot use the reserved words of Oracle. A table can contain a maximum of 2000 columns. The other parts of the syntax are added as needed. The functions are as follows:

DEFAULT expression: used to define the DEFAULT value of a column.

Column constraint: used to define COLUMN-level constraints.

Table constraint: used to define TABLE-level constraints.

TABLE_PARTITION_CLAUSE: defines the partition clause of a table.

2. Create a table through subquery

To create a table with the same or partially identical structure as an existing table, use the following syntax:

Create table Name (column name) as SQL query statement;

This syntax can be used to copy the table structure or the table content, and name a new column name for the new table. The new column name is given in parentheses after the table name. If omitted, the original table column name will be used. The copied content is determined by the WHERE condition of the query statement.

3. Delete the created table

The syntax for deleting a table is as follows:

Drop table name [cascade constraints];

The TABLE owner must be the TABLE creator or have the permission to drop any table. Cascade constraints: when the table to be deleted is referenced by another table, delete the CONSTRAINTS that reference this table.

View Application

A view is a logical table based on one or more tables or another view. Unlike tables, a view does not contain any data. A table is an entity that exists independently. It is the basic structure used to store data. The view is just a definition that corresponds to a query statement. View data comes from some tables, which are called base tables. You can view a table in a view, as if you are observing one or more tables from different perspectives.

Ii. Create a view

The creae view system permission is required to create a VIEW. The VIEW creation syntax is as follows:

CREATE [or replace] [FORCE | NOFORCE] VIEW name [(alias 1, alias 2...)]

AS subquery

[With check option [CONSTRAINT name]

[With read only]

Where:

Or replace indicates replacing an existing view.

FORCE: Creates a view regardless of whether the base table exists.

NOFORCE indicates that a view is created only when the base table exists. It is the default value.

The alias is the new name defined for the selected columns in the subquery. It replaces the original column names in the query table.

A subquery is a SELECT query statement used to define a view. It can contain connections, groups, and subqueries.

With check option indicates that the view insertion or modification must meet the constraints of the subquery. The constraint name is the name of the constraint.

With read only indicates that the view is READ-ONLY.

The syntax for deleting a view is as follows:

Drop view name;

To delete a VIEW, you must be the creator of the VIEW or have the permission to drop any view. Deleting a view does not affect the base table or data loss.

This is the application of tables and views in Oracle databases that I will introduce to you. The application of tables and views in Oracle databases is two important applications in Oracle database applications, I hope this article will help you.