Oracle Database entry DDL and database objects

Database objects

Common: tables: basic database objects for storing data, composed of rows (records) and columns (fields)

Constraints: series of rules for performing data verification to ensure data integrity

View: Logical display of data in a table

Index: an index is created based on the specified fields in the table to improve query performance.

Sequence: a set of regular integer values

Synonym: alias of an object

Name: it must start with a letter. Can contain letters, Data, underscores, $ ,#

Objects in the same solution (User) cannot be renamed. Oracle reserved words cannot be used

Supplement: tables in Oracle databases are divided into user-defined tables and data dictionary tables.

User-Defined tables: A group of tables created and maintained by the user, including the information required by the user

Data dictionary table: A group of tables automatically created and maintained by the Oracle database, including database information.

Create a table

Overview: two prerequisites are required for creating a table: Permission to create a table and available storage space.

When creating a table, you must specify the table name, field name, and field type. Create table is a DDL statement and cannot be undone once executed

Syntax: create table [schema.] table (column datatype [default expr] [,...]); -- creates a table under the current solution (User) by default.

Example: create table scott. test1 (name varchar2 (20), hiredata date default sysdate, salary number (8, 2) default 0 );

When a new record is inserted into test1, if the hiredate value is not specified, the current system time is used by default. The default value of salsry is also set to 0.

If no eid value is specified during record insertion, the default value of eid is null. Note that the default value of the numeric type is not 0, but null.

Supplement: You can also use subqueries to create tables. This is another way to create a table, but it is not very common. That is, when creating a table, insert the results of the subquery directly into the table.

The field list of the new table and the subquery result must match. The field list of the new table can be set to default. The field name is the same as the field name of the subquery result.

Syntax: create table [schema.] table (column [,...]) as subquery; -- field types cannot be defined in parentheses

Example: create table myemp2 as select empno, ename, sal * 12 from emp; -- invalid. The expression cannot act as a field and an alias must be specified.

Create table myemp2 as select empno, ename, sal * 12 annsal from emp; -- valid

Create table myemp (number, name, annual salary) as select empno, ename, sal * 12 from emp; -- specify the field name of the new table

Modify Table Structure

Overview: Use the alter table statement to modify the table structure. Including adding, modifying, and deleting fields. The alter statement is a DDL statement. Once executed, it cannot be undone.

Add: Use the add clause in the alter table statement to add a new field. The new field can only be added to the end of the entire table.

Alter table add (column datatype [default expr] [column datatype]...);

Alter table test1 add (grade number (3), phone varchar2 (20) default 'None ');

Modify: Use the modify clause in the alter table statement to modify an existing field. Including data type, size, and default value. However, the field name cannot be modified.

Alter table modify (column datatype [default expr] [column datatype]...);

Alter table test1 modify (grade number (2), phone varchar2 (15) default '010-12345678 ');

The modified default value is only valid for the newly inserted records. The modification operation will be affected by the existing data in the current table.

When the corresponding fields of an existing record only contain null values, the type and size can be modified. If the field already contains a value, the modification may fail.

Delete: Use the drop clause in the alter table statement to delete a field. Delete the length and data occupied by this field from each row and release the storage space occupied by the data block.

Alter table drop (column [, column]...);

Alter table test1 drop (grade, phone );

Clear table data

Overview: You can use truncate table to clear table data. Clears all records in the table and releases the table's storage space. It is a DDL statement and cannot be undone once executed.

It is very different from the delete statement in DML. Delete can be used for conditional deletion or transaction rollback or cancellation.

Syntax: truncate table; -- it does not clear the table itself. The table structure still exists, but becomes an empty table.

Delete table

Overview: Use the drop table statement to delete a table. It is a DDL statement and cannot be undone once executed.

All data in the table will be deleted, unfinished transactions will be committed, and all related indexes will be deleted.

Syntax: drop table;

Rename a table

Overview: the rename statement can be used to change the name of an existing table. It is a DDL statement and cannot be undone once executed.

You can also modify the names of other database objects (Views, sequences, synonyms, and so on. The object owner must perform the RENAME operation.

Syntax: rename old_name to new_name;

Example: rename test1 to test88;

Data Dictionary

Overview: data dictionary is the core of Oracle databases and is used to describe databases and all objects. A data dictionary consists of a series of read-only tables and views.

These tables and views are owned by SYS users and maintained by Oracle Server. Users can access these tables and views through select statements.

Content: physical and logical structure of the database. Object definition and space allocation. Integrity constraints. User. Role. Permission. Audit records

Views: the views in the data dictionary are read-only and can be divided into the following three types:

Dba (object information contained in all schemes), all (object information accessible to users), and user (Object Information of user Schemes)

Example: select table_name from user_tables; -- view the names of all tables owned by the current user

Select table_name from all_tables; -- view the names of all tables accessible to the current user

Select distinct object_type from user_objects; -- view the types of all objects owned by the current user

Select distinct object_type from all_objects; -- returns the types of all objects that the current user can view.

Select table_name frome dba_tables; -- view the names of all tables owned by all users

Select * from user_constraints; -- View All constraints of the current solution (User)

Select * from user_constraints where table_name = 'student '; -- view the constraint information in the student table under the current solution (User)

Constraint (Constraint)

Overview: constraints are data validation rules enforced on tables to protect data integrity.

There are five types, namely, not null (not empty), unique key (unique key), primary key (primary key), foreign key (foreign key), and check (check)

Category: domain integrity constraints: not null, check. Entity integrity constraints: unique and primary key. Integrity constraints: foreign key

Note: constraints are also a database object. If you do not specify a name when creating a constraint, the system automatically name it.

In Oracle, you can use the SYS_Cn format naming constraints. You can also view constraints in the data dictionary view.

You can add constraints when creating a table or separately after creating a table. You can define constraints at the table or column level.

It is generally not recommended that you add constraints after creating a table or modify the table structure after creating a table.

View: query the user dictionary view user_constraints to obtain all the constraints of the current user. Select * from user_constraints;

Query the user dictionary view user_cons_columns to find the fields where the constraints are created. Select * from user_cons_columns;

Create: create table [schema.] table (column datatype [default expr] [column_constraint],... [table_constraint]);

Alter table add [constraint constraint_name] constraint_type (column); -- this is the constraint added after the table is created.

For example, alter table stu add constraint stu_sid_pk primary key (sid); -- the original stu table has two fields: sid and name.

Equivalent alter table stu add primary key (sid); -- the constraint name is automatically set by the system.

Special case: when adding constraints after creating a table, non-empty constraints must be added using the modify clause. Actually, it is equivalent to redefining a field.

For example, alter table stu modify (name not null); or alter table stu modify (name char (8) default 'n'/A' not null );

You can also modify multiple fields, such as alter table stu modify (sid not null, name default 'stone 'not null );

Delete: The syntax is alter table drop constraint constraint_name;

Alter table drop primary key; -- another way to delete a primary key. Only the primary key can be deleted in this way.

Because only one primary key can be defined in a table, it does not have any ambiguity. Other constraints may define multiple

Continued 1: When deleting a constraint, if there are other constraints associated with the constraint, the delete operation will fail. The cascade clause can be used to delete other association constraints.

Syntax: alter table drop constraint constraint_name cascade;

At this time, two constraints are deleted. One is the primary key in the master table and the other is the foreign key in the child table.

Continued 2: When deleting a field in the table, if the field is in a multi-field joint constraint (joint primary key, joint unique key, and foreign key with reference to the current field ),

The deletion fails. In this case, the cascade constraints clause can be used to delete the constraints related to this field.

Syntax: alter table drop (column [, column]...) cascade constraints;

Disable: In alter table, you can use the disable constraint clause to disable existing constraints. The cascade option can also be used to delete the associated constraints.

Syntax: alter table disable constraint constraint_name [cascade];

It can also be enabled if it is disabled or deleted. In addition, the specific content or definition of the constraint still exists, but temporarily does not work.

Enable: In alter table, enable the disabled constraint by using the enable constraint clause.

Syntax: alter table enable constraint constraint_name;

When constraints are enabled, you cannot use the cascade option to enable other associated constraints. If you still want to use other constraints, you can only recreate other constraints.

Non-null constraint (not null)

Feature: it can only be defined at the field level. Make sure that the field value cannot be blank. Multiple non-empty constraints can be defined in a table.

Example: create table strudent (sid number (3) not null, name varchar2 (20), birth date constraint nn not null );

Note: constraint nn not null is also a non-null constraint, except that the name of the birth constraint is named by the user, and the sid constraint is named by the system.

Constraint is a reserved word, indicating that a constraint must be added here. nn is the name of the constraint, and not null is the type of the constraint.

By convention, the constraint name is named "Table name_field name_type abbreviation of constraint". It should be student_birth_nn.

Unique Constraint)

Feature: It can be defined either at the field level or at the table level. Used to ensure that the field (or field combination) does not contain repeated values

A null value is allowed for a field with a uniqueness constraint. Oracle automatically creates a unique index for the uniqueness constraint. A table can define multiple unique keys.

Example: create table student (sid number (3) unique, name varchar2 (20); -- field-level definition

Create table student (sid number (3), name varchar2 (20), constraint strudent_sid_un unique (sid); -- table-level constraints

Note: When you restrict the uniqueness of a field, the two effects are the same. To define uniqueness constraints for multiple fields, you can only define table-level constraints.

For example, create table fenshu (a number (3), B varchar2 (20), c number (4), constraint fenshu_a_ B _un unique (a, B ));

That is to say, the values of fields a and B cannot be duplicated. You can think of it as a student Score Table. a and B are equivalent to student numbers and subjects.

Primary key)

Features: a primary key can be defined either at the field level or at the table level. A primary key uniquely identifies a row of records in a table.

Only one primary key is allowed in a table. A primary key can be a combination of a single field or multiple fields. Oracle automatically creates a unique index for the primary key field.

Example: create table student (sid number (3) primary key, name varchar2 (20 ));

Create table student (sid number (3), name varchr2 (20), constraint student_sid_pk primary key (sid ));

Union: a primary key composed of multiple fields is also called the Union primary key. Each field in the federated primary key cannot be blank.

The value of the Union primary key field combination cannot be repeated. A joint primary key can only be defined as a table-level constraint.

Create table fenshu (a number (3), B varchar2 (20), c number (3), constraint fenshu_a_ B _pk primary_key (a, B ));

It is also a student Score Table. In fact, the student ID and subject combination should be set to a primary key instead of a unique key, because their values should not be empty.

Foreign key constraint (foreign key)

Feature: The foreign key is used to ensure the reference relationship between the two related fields, so as to implement the constraints of reference integrity. The foreign key must be the primary key or unique key of the primary table.

Foreign key constraints are usually built between two fields from different tables. The value of the foreign key column of the sub-table must be within the value range of the reference column of the master table, or be empty.

When the primary key or unique key of the master table is referenced by the quilt table, the corresponding records of the master table cannot be deleted.

Reference: the so-called integrity constraints, such as employee information table and payroll, are connected by employee numbers.

At this time, the employee number in the payroll is restricted, that is, it must appear in the employee information table. This relationship is called a reference relationship.

Example: create table info (id number (3) primary key, name varchar2 (20), job varchar2 (20), birth date );

Create table sal (a number (3) references info (id), B number (); -- defines the foreign key constraint as a field-level constraint

Create table sal (a number (3), B number (8, 2), constraint sal_a_fk foreign key (a) references info (id ));

Description: field a in the sub-Table sal is defined as a foreign key. It refers to the id field in the info table. The foreign key constraint is defined as a table-level constraint.

After the two statements are executed, no data exists in both tables. If insert into sal (a, B) values () is executed, an error occurs.

Because the system does not find the value of the field id 1 on a record in the master table referenced by the sub-table

You can execute insert into info values (1, 'Tom ', 'adv', sysdate) before the insert statement.

If insert into sal (null, 8) is executed again, the record is inserted successfully. After all, the foreign key is different from the primary key, which does not violate the reference rules.

Then execute insert into sal () to record successful insertion. This method of inserting null and repeated records is unreasonable.

In order to rationalize the data, other restrictions can also be imposed. That is, when field a acts as a foreign key, it can also limit the primary key.

Create table sal (a number (3) primary key references info (id), B number (8, 2 ));

Or create table sal (a number (3) primary key, B number (8, 2), constraint sal_a_fk foreign key (a) references info (id ));

Check Constraints)

Feature: it can only be defined at the field level. It defines the conditions that must be met for each row (specified field. Conditions to be met for data are given in the form of conditional expressions

Pseudo columns such as currval, nextval, level, rownum, or functions such as sysdate, uid, user, userenv, or reference to other field values are not allowed in the condition.

Currval is the current value of the sequence. Nextval is the next Sequence Value of the sequence generator. Level indicates the number of layers in the hierarchical Query Process.

Sysdate is the current system time. Uid indicates the ID of the current user. User is the current user name. Userenv is used to query the client environment, including the language or user name.

Example: create table test1 (name varchar2 (20) check (length (name)> = 6), age number (3) check (age> = 0 and age <= 120 ));

Create table test1 (name varchar2 (20) check (name is not null), age number (3); -- this is equivalent to a non-null constraint.

View)

Overview: A view is composed of one or more tables (or views) that extract data. A view is a virtual table. Once a view is created, it can be used as a table.

Using views can simplify complex data queries. It can improve the running efficiency. The database table structure can be shielded to implement data logic independence.

You can also restrict database access. You can also provide different views on the same data to facilitate data sharing. Views can also have primary keys.

You can create a view by embedding a subquery in the create view statement.

Create: create [or replace] view [schema.] view [(alias [, aliasx]...)] as subquery;

Example: create or replace view v1 (number, name, salary) as select empno, ename, sal from emp where deptno = 20;

We recommend that you add or replace when creating a view, that is, if there is a duplicate view, replace the duplicate view.

Field Types cannot be listed in parentheses, because the specific field types are determined by the results of the subquery.

Stress: You can use the force option to forcibly create a view. That is, the view must be created forcibly regardless of whether the expected field or base table exists.

That is, a view definition is provided first. However, you cannot query a view that does not exist in the base table. The reason is self-evident.

Syntax: create [or replace] [force | noforce] view [schema.] view [(alias [, aliasx]...)] as subquery;

For example, create or replace force view v2 as select empno, ename, job, sal from emp2 where deptno = 20;

Assume that the emp2 table does not exist at this time. Of course, if these fields do not exist, you cannot create a view without the force option.

By default, a view is not created forcibly (noforce. In fact, it is not recommended to create a view forcibly.

Query: Like querying table data, you can use select * from v1; to query data in the view.

The implementation details are as follows: when the database server receives the query view Command sent by the client software such as the application or SQLPlus

First, we will find the view definition named v1 in the current scheme, that is, the create statement that defines the view, that is, create or replace view v1...

Find the definition of v1, execute the subquery in the definition, and then return the query result to the client.

This means that each time a view is referenced or accessed, A subquery is executed and the underlying physical table is checked.

Therefore, any data update in the physical table will be immediately reflected in the View query.

Continued 1: You can use desc v1 to view the view structure. You can use drop view v1 to delete a view.

A temporary table is a temporary table created as needed during the running of the program. Create table is usually used to create a temporary table.

The temporary table is only used temporarily and will be deleted when it is used up. Similar to a temporary file, a temporary table stores real data, which is different from a virtual table.

Continued 2: when creating a view, you can also leave the field name unspecified. The field attributes in the default view are the same as those in the subquery results.

If an alias is used in a subquery, the view uses the alias as its field name. In addition, expressions or functions in subqueries cannot be used as field names of views.

Continued 3: in fact, the definition of views is the same as that of tables. During application development, the number of views should be determined during the database design phase.

Create a certain number of tables to save the underlying data. Set the data to be used by specific program modules in the form of views.

Complex: You can also create complex views. That is to say, subqueries can be more complex.

If a service needs to query statistics frequently, you only need to create a view like this, instead of executing a subquery every time to obtain data.

For example, create or replace v_emp (employee ID, name, position, annual salary, length of service (month), department ID, Department name ,)

As select empno, ename, job, sal * 12, month_between (sysdate, hiredate), emp. deptno, dname

From emp, dept where emp. deptno = dept. deptno;

Update: Perform DML operations on the updatable view to modify the data in the base table. The syntax is the same as that for table operations, including insert, update, and delete statements.

The group function, group by clause, distinct keyword, and rownum pseudo Columns cannot be used in the updatable view definition, and the field definition cannot be an expression.

Views exported from more than two base tables cannot be updated. If a non-empty column in the base table is not included in the view definition, the insert operation cannot be performed on The View.

This operation is very error-prone. In fact, this is rarely done in real development, and it is not recommended to update underlying data through views.

Read-only: when creating a view, you can use the with read only option to set it to read-only.

Syntax: create [or replace] view [schema.] view [(alias [, aliasx]...)] as subquery [with read only];

Temporary: subqueries embedded in SQL statements are temporary views. For example, the subqueries involved in Table creation or TopN Analysis Using subqueries all belong to the temporary view.

The temporary view is not a database object and its definition is not stored in the database for a long time. It is cleared after this operation. It is similar to the anonymous class created in Java.

Index)

Overview: it is a database object used to improve query efficiency. The index information is stored independently of the table. It can reduce disk I/O operations by quickly Locating data

There are two types of indexes: unique index and non-unique index. Automatic Use and Maintenance of indexes in Oracle databases

Create: Automatic Creation: when defining the primary key or unique key constraint, the system automatically creates a unique index on the corresponding field

Manual creation: You can create non-unique indexes on other columns to accelerate queries.

Syntax: create index [schema.] index on table (column [, column]...);

For example, create index myindex on emp (ename); -- When querying a table, if ename is used as the query condition, it is easy to locate a row of records.

Delete: Use the drop index statement to delete an index, for example, drop index myindex. The operator must be the index owner or have the permission to drop the index.

When a table is deleted, related indexes (and constraints) are automatically deleted, but views and sequences are retained.

Principle: Suitable for index creation: the Field Values are distributed in a wide range. Fields contain a large number of null values. Fields often appear in the where clause or join condition.

Tables are frequently accessed, or tables have a large amount of data. Generally, the amount of data accessed each time is less than 2% of the total number of records ~ 4%

Not suitable for index creation: the table is small. Fields do not often appear in the where clause. The amount of data accessed each time exceeds 2% of the total number of records ~ 4%

Tables are updated frequently. The indexed field is referenced as part of the expression.

View: query the user dictionary view user_indexes to obtain all your indexes. For example, select * from user_indexes;

Query the user dictionary view user_ind_columns to find out which fields the index is created on. For example, select * from user_ind_columns;

Function: expression-based indexes are collectively referred to as function-based indexes. The index expression is constructed by fields, constants, SQL functions, and user-defined functions in the table.

The syntax for creating a function index is create index [schema.] index on table (function (column ));

Create index myindex on emp (lower (ename ));

Use the function index select * from emp where lower (ename) = 'King ';

During use, the fields in the condition must be the same as those specified during index creation. That is, the lower (ename) must be consistent

Sequence)

Overview: an integer that is automatically generated by the system and does not repeat. A sequence is a database object that can be shared by multiple users. Serial numbers can replace Application Numbers

The most typical purpose of a sequence is to act as the primary key value, which must be unique for each row. Sequential values can be buffered to improve access efficiency.

However, if the number is generated in the application, the performance bottleneck will occur. That is to say, it is troublesome to ensure its uniqueness and efficiency.

The so-called buffer storage means that, before using a sequence, the value of multiple specified sequences can be used as a backup, which can improve the access efficiency.

From the perspective of practical application, the importance of sequences far exceeds that of views and indexes, which is equivalent to the importance of constraints and tables.

View: query the data dictionary view user_sequences to obtain user sequence information.

Create: create sequence [schema.] sequence [increment by n] [start with n]

[{Maxvalue n | nomaxvalue}] [{minvalue n} | nominvalue] [{cycle | nocycle}] [{cache n | nocache}] [{order | noorder}];

Example: create sequence mysequence2;

Create sequence mysequence1 increment by 1 start with 1 nomaxvalue nocycle;

Release 1: increment indicates the incremental value of the current serial number. The default value is 1. Start with specifies the start value of the current serial number. The default value is 1.

Maxvlaue sets the maximum value of the serial number. The default value is nomaxvalue, that is, no maximum value. Similarly, minvalue is used to set the minimum value of the serial number.

After the sequence reaches the maximum value, cycle is set to whether to start from 1 again. The default value is nocycle, that is, no cycle.

NOTE 2: in fact, nomaxvalue has a maximum value in the implementation process, which is about 27 to the power of 10. Similarly, nominvalue is 10 to the power of 27.

In addition, when using a sequence, it is generally not allowed to loop. Avoid duplication of unexpected data

Release 3: Set whether to store cached data. Its default value is not nocache, but cache 20.

That is to say, the cache will generate 20 serial numbers for backup. When a serial number is used up, it will add another

In this way, the program runs more efficiently. However, the cache serial number may be lost. It is not an error, but not continuous.

Note 4: order ensures that the generated serial number is in the Request order. In fact, there is no difference between a single process or a single-thread Program (that is, non-concurrent access ).

If multiple requests request the same serial number at the same time in the concurrent (also called parallel) working mode, the serial number assigned to them may not guarantee the order.

The sequence is not guaranteed, but the value is unique and will not be repeated. In fact, it has little impact on us. For example, two requests arrive at the same time.

If order is used, the serial number requested by the first request will be 101, and then 102 will be reached.

However, if nooder appears, the first request may get 102, and the second request may arrive at 101, because there is an alternate process in the middle.

The default value is noorder, indicating that the serial number is generated in the Request order.

Use: select mysequence1.nextval from dual; -- in fact, it generates a serial number and returns an integer.

Select mysequence1.currval from dual; -- retrieves the current serial number. This direct query is of little significance.

Insert into test1 values (mysequence1.nextval, 'Tom '); -- this is the real sequence used.

The nextval pseudo column is used to extract the next value from the specified sequence value. The currval pseudo column references the "current value" of the specified sequence"

Cache n can improve access efficiency. Setting nocache and order will reduce the running efficiency.

When the same sequence is used for rollback, system exception, and multiple tables at the same time, the sequence may be discontinuous.

Modify: alter sequence [schema.] sequence [increment by n] [{maxvalue n | nomaxvalue}]

[{Minvalue n} | nominvalue] [{cycle | nocycle}] [{cache n | nocache}] [{order | noorder}];

The operator must be the sequence owner or have the alter sequence permission. Only the number of sequences generated in the future will be affected.

Some verification will be performed during the modification. For example, if the new maxvalue is smaller than the current sequence value, an error will be reported. The initial values of the sequence cannot be changed.

In order to avoid duplication with the existing serial number, the initial value of the sequence cannot be changed.

Delete: Use the drop sequence statement to delete a sequence. The operator must be the sequence owner or have the permission to drop the sequence.

Synonym

Overview: equivalent to an object alias. synonyms can be used to easily access objects of other users and shorten the Object Name Length.

Create: create [public] synonym fro object;

Create synonym gt1 for emp;

Use: select * from gt1;

Delete: drop synonym gt1;

NOTE: If public is not added, the defined synonym can only be used in the current solution (User) environment. Other users cannot use this synonym.

After public is added, other schemes (users) can use this synonym.

However, only users with the corresponding permissions (such as DBA) are eligible to define synonyms as public.