SQL Foundation Creation Other scenario object (15)

View Overview:

– Create, modify, and retrieve data from a view

– Data Manipulation Language (DML) operations on the view

– Delete View

Database objects

Object objects	Description description
Table tables	A basic collection of data stores, consisting of rows and columns.
View views	Logically related data collection drawn from one table or multiple tables
Sequence sequence	The numerical value of the generated law
Index indexes	Improve query performance
Synonym synonyms	Alias to an Object

What is a view? My understanding is to create a custom association virtual table from one table or multiple tables

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M00/8F/28/wKioL1jVJ1CCh7WzAAC_8VYSGpA461.jpg "title=" Qq20170324220342.jpg "width=" 720 "height=" 398 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:720PX;HEIGHT:398PX; "alt = "Wkiol1jvj1cch7wzaac_8vysgpa461.jpg"/>

There are several advantages to the view:

• Restricting data access

• Simplify queries

• Data independence

• Avoid repeated access to the same data

Simple and complex views

Characteristics	Simple view	Complex views
Number of tables	One	One or more
Include function	No	Yes
Include grouped data	No	Yes
Doing DML operations with views	Yes	Not alway

Create a View

To 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];

• A subquery can contain complex SELECT syntax

• With CHECK option: Prevents the insertion of invisible rows and prevents the updates from being lost from view.

1, create the view emp80, including the Department of 80 employee details:

CREATE VIEW EMP80

As select employee_id, last_name, salary

From Employees

where department_id = 80;

Describe the view structure using the Sql*plus DESCRIBE command

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;

You should use aliases when selecting columns in a view

Modify a View

Use the Create OR REPLACE view clause to modify the EMPVU80 view. Add aliases for each column:

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

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;

Rules for performing DML operations on a view--DELETE

DML operations can be performed on a simple view

The row cannot be deleted when the view definition contains the following elements

– Group functions

–group BY clause

–distinct keywords

–rownum Pseudo-Column

Rules for performing DML operations on a view--UPDATE

The data cannot be modified when the view definition contains the following elements:

– Group functions

–group BY clause

–distinct keywords

–rownum Pseudo-Column

– The columns defined by an expression

Rules for performing DML operations on a view--INSERT

The data cannot be inserted when the view definition contains the following elements:

– Group functions

–group BY clause

–distinct keywords

–rownum Pseudo-Column

– The 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 executes only 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

Masking DML Operations

• You can use the WITH READ only option to mask DML operations on a 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 a view

Deleting a view simply deletes the definition of the view and does not delete the data from the base table

Drop View emp80;

Drop View Slav;

Sequence

• Automatically provide a unique value

• Shared objects

• Primarily used to provide primary key values

• can be used instead of the application generation sequence

• Cache sequence values in memory for increased access efficiency

CREATE SEQUENCE Syntax

Define a sequence to automatically generate consecutive numbers:

Create sequence sequence

[Increment by n]

[Start with N]

[{MaxValue n | nomaxvalue}]

[{MinValue n | nominvalue}]

[{cycle | nocycle}]

[{Cache n | nocache}];

• Create sequence DEPT_DEPTID_SEQ provides a primary key for table departments.

• Not with the CYCLE option

Create sequence Dept_deptid_seq

Increment by 10

Start with 120

MaxValue 9999

NoCache

Nocycle;

Nextval and Currval pseudo-columns

• Nextval returns the next available sequence value. It returns a unique value each time it is referenced, any user can reference it

• Currval get the current sequence value

• Nextval must be issued before using Currval

Using sequences

Insert a new department for the "support" Location ID of 2500

INSERT INTO departments (DEPARTMENT_ID,

Department_name, location_id)

VALUES (Dept_deptid_seq.nextval,

' Support ', 2500);

Sequence DEPT_DEPTID_SEQ display the current value

Select Dept_deptid_seq.currval from dual;

Cache Sequence Values

• Cache sequence values in memory for increased access efficiency

• The sequence occurs with a "break number" in the following situations:

– rollback occurs

– System crashes

– The sequence is used for other tables

You can modify the increment, maximum, minimum, loop options, or cache for a sequence:

Alter sequence DEPT_DEPTID_SEQ

Increment by 20

MaxValue 999999

NoCache

Nocycle;

Considerations for Modifying a sequence

• Must be the owner of a sequence or have ALTER permission on a sequence

• Only future sequence values will be changed.

• Changing the initial value of a sequence can only be achieved by removing the sequence after the series is reconstructed

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

To delete a sequence using the drop statement:

Drop sequence dept_deptid_seq;

Index:

is a Scenario object

Accelerate query speed for Oracle servers with pointers

You can reduce disk I/O by using the fast path access method to quickly locate data

Indexes and tables are independent of each other

Oracle server automatically uses and maintains indexes

To create an index:

Auto-Create: The system automatically creates a unique index on the corresponding column after the PRIMARY KEY or UNIQUE constraint is defined.

Create manually: Users can create non-unique indexes on other columns to speed up the query.

To create an index syntax on one or more columns:

Create [Unique][bitmap]index Index

On table (column[, column] ...);

2. Create an index on the Last_Name field in table employees to increase query access speed:

Create index EMP_LAST_NAME_IDX on employees (last_name);

CREATE INDEX considerations

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/8F/2A/wKiom1jVOsWT0YlNAADT2s81x7U911.jpg "title=" Qq20170324220342.jpg "width=" 720 "height=" 421 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:720PX;HEIGHT:421PX; "alt = "Wkiom1jvoswt0ylnaadt2s81x7u911.jpg"/>

Delete Index

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

Drop index Index;

To delete an index from a 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.

Synonyms

Create synonyms for objects

Simplifying object access by creating synonyms (another name for an object), using synonyms you can:

Easy access to other users ' objects

Shorten the length of the object name

To create a synonym syntax:

Create [public] synonym synonym for object;

Creating and deleting synonyms examples

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;

This article is from the "record a bit of learning life" blog, please make sure to keep this source http://ureysky.blog.51cto.com/2893832/1910214

SQL Foundation Creation Other scenario object (15)