Shulou(Shulou.com)06/01 Report--

-create, modify, and retrieve data from the view

-data manipulation language (DML) operations on views

-Delete the view

Database object

The Object object Description describes the basic data store collection of the Table table, consisting of rows and columns. View View A logically related collection of data extracted from one or more tables. Sequence sequences generate regular numerical Index indexes to improve query performance. Synonym synonyms aliases objects.

What is a view? My understanding is to create a custom associated virtual table from one or more tables.

Simplify query

Data independence

Avoid repeatedly accessing the same data

Features simple view number of complex view charts OneOne or more contains functions NoYes contains grouped data NoYes does DML operations through views YesNot alway

Create a view

Embed a subquery in the CREATE VIEW clause:

Create [or replace] [force | noforce] view myview

[(alias [, alias]...)]

As subquery

[with check option [constraint constraint]]

[with read only [constraint constraint]]

Subqueries can contain complex SELECT syntax

With check option: prevents invisible rows from being inserted and updates that are lost from the view.

1. Create a view emp80, which contains the details of the employee with department 80:

Create view emp80

As select employee_id, last_name, salary

From employees

Where department_id = 80

Use the DESCRIBE command of SQL*Plus to describe the view structure

Desc emp80

2. Create a view using column aliases in a subquery:

Create view salv50

As select employee_id id_number, last_name name

Salary*12 ann_salary

From employees

Where department_id = 50

Aliases should be used when selecting columns in a view

Modify the view

Create or replace view emp80

(id_number, name, sal, department_id)

As select employee_id, first_name | |''

| | last_name, salary, department_id |

From employees

Where department_id = 80

The aliases listed in the CREATE OR REPLACE VIEW clause correspond to the columns in the subquery

Create a complex view that contains group functions that display data from two tables:

Create or replace view dept_sum_vu

(name, minsal, maxsal, avgsal)

As select d.department_name, min (e.salary)

Max (e.salary), avg (e.salary)

From employees e join departments d

On (e.department_id = d.department_id)

Group by d.department_name

The rule for performing DML operations on the view-- DELETE

You can perform DML operations on a simple view

Rows cannot be deleted when the view definition contains one of the following elements

-group function

-GROUP BY clause

-DISTINCT keyword

The rule for performing DML operations on the view-- UPDATE

Data cannot be modified when the view definition contains one of the following elements:

-group function

-GROUP BY clause

-DISTINCT keyword

-ROWNUM pseudo column

The rule for performing DML operations on the view-- INSERT

Data cannot be inserted when the view definition contains one of the following elements:

-group function

-GROUP BY clause

-ROWNUM pseudo column

-columns defined by an expression

-non-empty columns in the table are not included in the view definition

WITH CHECK OPTION constraint

Use the WITH CHECK OPTION clause to ensure that DML can only be executed within a specific scope:

Create or replace view empvu20

As select *

From employees

Where department_id = 20

With check option constraint empvu20_ck

Any request that violates the WITH CHECK OPTION constraint will fail

Mask DML operation

You can use the WITH READ ONLY option to mask DML operations on the view

Any DML operation will return an Oracle server error

Create or replace view empvu10

(employee_number, employee_name, job_title)

As select employee_id, last_name, job_id

From employees

Where department_id = 10

With read only

Delete View

Deleting a view only deletes the definition of the view, not the data of the base table.

Drop view emp80

Drop view slav

Sequence

Provide a unique value automatically

Shared object

Mainly used to provide primary key values

It can generate serial number instead of application.

Caching sequence values in memory can improve access efficiency

CREATE SEQUENCE syntax

Define a sequence that automatically generates consecutive numbers:

Create sequence sequence

[increment by n]

[start with n]

[{maxvalue n | nomaxvalue}]

[{cycle | nocycle}]

[{cache n | nocache}]

Create the sequence DEPT_DEPTID_SEQ to provide the primary key for the table DEPARTMENTS.

Create sequence dept_deptid_seq

Increment by 10

Start with 120

Maxvalue 9999

Nocache

Nocycle

NEXTVAL returns the next available sequence value. It returns a unique value every time it is referenced, any user can reference it

CURRVAL gets the current sequence value

NEXTVAL must be issued before using CURRVAL

Use sequenc

Insert a new department for "Support" location ID is 2500

Insert into departments (department_id

Department_name, location_id)

Values (dept_deptid_seq.nextval

'support', 2500)

Sequence DEPT_DEPTID_SEQ displays the current value

Select dept_deptid_seq.currval from dual

Caching sequence values in memory can improve access efficiency

The sequence has a "dash" in the following cases:

-rollback occurs

-system crash

-sequence is used for other tables

You can modify the increments, maximum, minimum, loop options, or cache of a sequence:

Alter sequence dept_deptid_seq

Increment by 20

Maxvalue 999999

Nocache

Nocycle

Considerations for modifying a sequence

Must be the owner of the sequence or have ALTER permission to the sequence

Only future sequence values will be changed

Changing the initial value of a sequence can only be achieved by reconstructing the sequence after deleting the sequence.

Perform some validation (for example, the new MAXVALUE is less than the current serial number)

Use the DROP statement to delete the sequence:

Drop sequence dept_deptid_seq

Index:

It is a scheme object.

Accelerate the query speed of the Oracle server through pointers

By using the fast path access method to quickly locate data, the disk Icano can be reduced.

Indexes and tables are independent of each other.

The Oracle server automatically uses and maintains indexes

Create an index:

Automatic creation: the system automatically creates a unique index on the corresponding column after the PRIMARY KEY or UNIQUE constraint is defined.

Manual creation: users can create non-unique indexes on other columns to speed up the query.

Create an index syntax on one or more columns:

Create [unique] [bitmap] index index

2. Create an index on the LAST_NAME field of table EMPLOYEES to improve query access speed:

Create index emp_last_name_idx on employees (last_name)

Considerations for creating an index

Delete index

Use the DROP INDEX command to remove the index from the data dictionary:

Drop index index

Remove the index from the data dictionary: emp_last_name_idx

Drop index emp_last_name_idx

To delete an index, you must be the owner of the index or have DROP ANY INDEX permission.

Synonym

Create synonyms for objects

By creating synonyms to simplify object access (another name of an object), using synonyms you can:

Facilitate access to other users' objects

Shorten the length of object names

Create synonym syntax:

Create [public] synonym synonym for object

1. Create a synonym for a shorter name for the view DEPT_SUM_VU:

Create synonym d_sum for dept_sum_vu

2. Delete synonyms

Drop synonym d_sum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.