Oracle SQL Foundation (iv): Data definition language (create and manage tables, views)

The Data definition language (Language, DDL) is a language in the SQL language set responsible for data structure definitions and database object definitions, consisting of Create, alter, and drop three grammars. database objects include tables, views, indexes, synonyms, sequences, and so on (table/view/index/syn/sequence). Let's start with an introduction to creating and managing tables and views.

I. Creating and Managing Tables

A data table is a very important object in a database and is the basis for other objects. Can be divided into user tables and data dictionary two categories.

1. Creating tables (CREATE TABLE)

CREATE TABLE statement Creating tables

CREATE TABLE Table                 --table: Table name:   Column name            ( column datatype       -- data type and length for columns            [DEFAULT expr]         -- Specify default values to prevent input of            null values into columns when inserting [,... ]);

The most common data types are three, character, numeric, and date-based.

Data type	Description
VARCHAR2 (size)	Variable-length character data (the maximum number of characters must be specified: the minimum number of characters is 1; The maximum number of characters is 4000)
CHAR [(size)]	Fixed-length character data, the size of the length in bytes (default and minimum number of characters is 1; Maximum character count is 2000)
Number [(P,s)]	Number, precision p, fractional s (p is the total length of the number, S is the number of digits to the right of the decimal point, and P ranges from 1 to 38,s from 84 to 127)
DATE	Date and time values, from 4712.1.1 BC to A.D. 9999.12.31

Create a table with subquery syntax (CTAS)

CREATE TABLE Table              [(column, column ...) ]as           subquery              -- use sub-query

If the specified column is given, the number of columns must be equal to the number of columns of the subquery's select list. If the specified column is not given, the column name of the table should be the same as the column name in the subquery. Integrity rules are not passed to the new table, only the data type of the column is defined.

2, modify the table (ALTER TABLE)

ALTER TABLE ADD [DEFAULT expr] [, column datatype]...);    -- Adding Columns

MODIFY ([DEFAULT expr] [, column datatype--Modify column     

Altertable TABLE drop (column); --delete  column 
 alter table TABLE SET unused (column);                                            --hidden column 
 alter table TABLE drop unused  Columns; --delete hidden columns            

When you add a column, you cannot specify the newly added listed location, the new column becomes the last column, the data type size and the default value can be modified when the column is modified, the width of one column and the data type are reduced when the column contains only null values, and changes to the default values change only the columns that are inserted after the column is deleted. You must keep at least one column after the table has been modified. Once a column is deleted, it can no longer be restored; When a column is deleted from the table, any other set unused option tag columns in the table are also deleted, and the function of setting unused is to make the CPU, memory and other resources insufficient, Do the UNUSED tag and wait until the database resource is idle with the drop UNUSED columns Delete.

3. Delete tables (drop table)

DROP table table [PURGE];

When you delete a table, the database loses all of the data in the table, and all its associated indexes are deleted, and any views and synonyms are retained but not valid. Because of the introduction of the Recycle Bin in ORACLE 10g, it is necessary to add purge keywords to completely remove a table.

4. Truncated table (TRUNCATE tables)

TRUNCATE table table;

The truncation table is used to remove all rows from the table and to free the storage space used by the table. The DELETE statement can also delete all rows from the table, but it cannot free up storage space. The truncate command is faster for the following reasons: The TRUNCATE statement is a data definition (DDL) statement, does not produce rollback information, and truncates a table without touching the published delete trigger.

Ii. Defining and managing constraints

Constraints (constraints) are used to prevent invalid data from being entered into a table. You can use constraints to do the following: Force the data in the table to follow the rules when inserting, updating, or deleting rows from a table; For successful operations, the constraints must be met, if there is a dependency between tables, to prevent the deletion of tables, and for Oracle tools, such as Oracle Developer.

Constraints can be defined when a table is created, or after a table is created. You can use the user_constraints and User_cons_columns data dictionary tables to view the definition of a table constraint.

1. Defining constraints

CREATE TABLE [schema.]Table             (columnDataType[DEFAULT Expr]                                       --Data Type             [column [CONSTRAINT constraint_name]Constraint_type],...--column-level constraints             [column,... [CONSTRAINT constraint_name]Constraint_type] (column,...) --table-level constraints             [,...]);

Constraints are typically created at the same time that a table is created. The constraint can be added after the table is created, and the constraint may be temporarily disabled. Constraints can be defined at two levels: column-level constraints, which involve only a single column, any type that defines an integrity constraint for that column, a table-level constraint that involves one or more columns, and the columns in the table are defined separately, except for not NULL, to define arbitrary constraints.

2. Constraint type

Constraint name	Description
Not NULL constraint	Make sure the column contains no null values. By default, columns do not have a NOT NULL constraint and can contain null values.
UNIQUE constraint	Integrity constraints, which require that each value of a column or a combination of columns (keys) be unique, and that no two rows have the same value in the column or combination of columns specified in the table. The column (or combination of columns) that defines a unique key constraint is called a unique key. Because nothing is equal to anything. Null in a column (or all columns in a composite unique key) always satisfies the unique constraint.
PRIMARY KEY Constraint	Create a primary key for the table. Only one primary key can be created per table. The PRIMARY key constraint is a combination of a column or columns that uniquely identifies a row in a table, which enforces uniqueness of the column or column combination, and ensures that the column that is part of the primary key cannot contain null values. The PRIMARY key constraint can be defined either at the column level or at the table level. Create a composite primary KEY with the table-level definition. A table can have only one primary KEY constraint, but there may be multiple unique constraints.
FOREIGN KEY Constraint	Referential integrity constraints, which indicate the combination of a column or column as a foreign key, and establish a relationship between the primary key or unique key and foreign key of the same table or different table. The precondition is that the referenced foreign key must be the primary key of the other table.
Check Constraint	Defines a condition that each row must satisfy, which can be used in the same structure as the query criteria.

3. Management constraints

ALTER TABLE Table ADD [CONSTRAINT CONSTRAINT]Type (column);--Add constraintALTER TABLE Table DROP PRIMARY KEY|UNIQUE(column)|CONSTRAINT constraint [CASCADE];--Delete ConstraintALTER TABLE TableDISABLECONSTRAINT constraint [CASCADE];--Disabling ConstraintsALTER TABLE TableENABLECONSTRAINT constraint;--Enable Constraints

You can add, remove, or disable a constraint, but you cannot modify its structure. You can add a NOT NULL constraint to a column that already exists by using the MODIFY clause of the ALTER TABLE statement. You can define a NOT NULL column only if the table is empty or if the column for each row has a non-null value.
To remove a constraint, you can first determine the name of the constraint from the user_constraints and user_cons_columns data dictionary views, and then use the ALTER TABLE statement with the DROP clause. The cascade option of the DROP clause causes any constraints that depend on it to be deleted.
You can disable a constraint without deleting it, or recreate it with the ALTER TABLE statement with the Disable clause. We can also use the DISABLE clause in the ALTER TABLE statement in the CREATE TABLE statement. The CASCADE clause disables dependency integrity constraints. Disabling a unique or PRIMARY KEY constraint removes the uniqueness index.
You can enable a disabled constraint with an ALTER TABLE statement with an enable clause without recreating it. If you enable a constraint, the constraint is applied to all the data in the table, and all data in the table must be appropriate for that constraint. If you enable a unique key or PRIMARY KEY constraint, a unique or primary key index will be created automatically. You can also use the ENABLE clause in the ALTER TABLE statement with the CREATE TABLE statement. Enabling a disabled primary KEY constraint with the CASCADE option does not take any foreign keys that depend on the primary key.

Iii. Creating and Managing views

A view is a logical representation of a data subset of data from one or more tables. A view is a logical table that is based on a table or another view, and a view does not contain its own data, which is like a window through which you can view or change the data in a table. A view is based on a table on which it is called a base table.
Views restrict access to data because the view can selectively display the columns in the table, which is used to form a simple query to retrieve the results of a complex query, which provides data independence for special users and applications, a view that retrieves data from several tables, and a view that provides user groups with access to data according to their specific criteria.

1. Create a View

There are two categories of views: a simple view and a complex view. Simple view: Data comes from only one table, does not contain functions or data groupings, and can perform DML operations through a view. Complex views: Data comes from multiple tables, including functions or data groupings, and only a few DML operations, except queries. The following is the syntax for creating a view.

CREATE [OR REPLACE] [force| Noforce] VIEW View [(alias[, alias]...)]  assubquery[With CHECK OPTION [CONSTRAINT CONSTRAINT]]                         [With READ only [CONSTRAINT CONSTRAINT]];----or REPLACE If the view already exists re-create it----force Create a view, regardless of whether the base table exists----noforce Create a view only if the base table exists (this is the default value)----view The name of the view----alias Specifies a name for the expression selected by the view query (the number of aliases must match the number of expressions selected by the view)----subquery is a complete SELECT statement (you can use aliases for fields in the select list)----with CHECK OPTION Specifies that only accessible rows can be inserted or modified in the view----constraint The name specified for the CHECK option constraint----with READ only ensures that no DML operations are performed in this view

The principle of creating a view: A subquery that defines a view can contain complex select syntax, including even grouping and multiple word queries; a subquery that defines a view cannot contain an ORDER BY clause, and you can specify an ORDER BY clause when you retrieve data from a view; The option option creates a view that specifies a constraint name, and the system assigns a default name in the SYS_CN format.

2. Management view

DROP VIEW view;      --view is the name of the view

We can delete the view using the Drop VIEW statement. The statement removes the view definition from the database. Deleting a view does not affect the base table used to establish the view. Other views or applications that are based on the deleted view will be invalid.

With the or Replace option, a view that has the same name as the view is created is already present so that the old version of the view is replaced, which means that the view can be modified without being deleted, recreated, and again granted permission to the object. When you specify a column alias in the CREATE VIEW clause, do not look at the list of aliases in the same order as the column order of the subquery.

Oracle SQL Foundation (iv): Data definition language (create and manage tables, views)