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

one。 View, sequence, index

View

1. What is a view?

A VIEW, also known as a virtual table, is a logical representation of a set of data

The view corresponds to a SELECT statement, and the result set is given a name, that is, the view name.

The view itself does not contain any data, it only contains a query that maps to the base table, and when the data-based changes, the view data page changes accordingly

CREATE [OR REPLACE] VIEW view_name [alias [, alias...]] AS subquery

After the view is created, you can manipulate the view like an operation table, mainly a query

SUBQUERY is a SELECT query statement, and the corresponding table is called the base table

The-SELECT statement is based on a single table and does not contain any function operations, expressions or grouping functions, called a simple view, in which case the view is a subset of the base table

The-SELECT statement is based on a single table, but contains a single-line function, expression, grouping function, or GROUP BY clause called a complex view

The-SELECT statement is based on multiple tables, called join views

two。 The role of views

Simplify complex queries: if you often need to execute a complex query, you can create a view based on the complex query, and then query the secondary view

Restrict data access: when accessing the view, you can only access the columns involved in the corresponding SELECT statement, which plays a secure and confidential role for other columns in the base table.

3. Authorize the creation of a simple view (single table)

The statement to create a view is: CREATE VIEWER

The user must have CREATE VIEWER system permissions to create a view

The administrator grants the user permission to create a view through the DCL statement: GRANT CREATE VIEWER TO user

Eg: CREATE VIEWER v_emp_10 AS SELECT empno,name,sal,deptno FROM emp WHERE deptno = 10

4. Query view

Desc v_emp_10

5. INSERT the view

The view itself does not contain data, but only a logical mapping of base table data.

When performing a DML operation on a view, it is actually a DML operation on the base table

Basic principles for performing DML operations on views:

-A simple view can perform DML operations, except when a non-empty column is defined in the base table, but the SELECT statement corresponding to the simple view does not contain this non-empty column, so that the non-empty queue view is not visible, and the INSERT operation cannot be performed on the view.

-if the view definition contains functions, expressions, grouping statements, DISTINCT keywords or ROWNUM pseudo columns, DML operations are not allowed

-DML operation cannot violate the constraints of the base table

-simple views can affect base table data through DML operations

-during the DELETE operation, the view can only delete the data that can be seen in the view in the base table, not the data that exists in the base table but cannot be seen in the view.

6. Create a view with CHECK OPTION constraints

CREATE [OR REPLACE] VIEW view_name [(alias [, alias...])] AS subquery [WITH CHECK OPTION]

The WITH CHECK OPTION phrase means that changes made through the view must be within the visible range of the view

-assuming INSERT, the new records can still be viewed in the view.

-assuming UPDATE, the modified result must be viewable through the view.

7. Create a view with READ ONLY constraints

CREATE [OR REPLACE] VIEW view_name [(alias [, alias...])] AS subquery [WITH READ ONLY]

If it is not necessary to perform DML operations on the view, declare it read-only when creating the view to avoid this situation and ensure that the base table data corresponding to the view will not be illegally modified

8. Obtain relevant information by querying user_viewers

View-related data dictionaries:

-USER_OBJECTS

-USER_VIEWS

-USER_UPDATE_COLUMNS (check which columns are allowed to be added, deleted or modified)

Eg: query all view names in the data dictionary USER_OBJECTS

SELECT object_name FROM user_objects WHERE object_type = 'VIEW'

9. Create a complex view (multi-table association)

Complex view: a view that contains expressions, one-line functions, or grouped functions in a subquery

An alias must be defined for an expression or function in a subquery

Complex views do not allow DML operations (that is, INSERT,UPDATE,DELETE)

10. Delete View

When the definition of the view is no longer needed, you can use the DROP VIEW statement to delete the view

Eg:DROP VIEW view_name

Although the view is a separate object stored in the data dictionary, the view is only a query definition based on the table, so the deletion of the view will not lead to the loss of the base table data and will not affect the base table data.

SELECT sys_guid () FROM DUAL;// can get 32-bit uuid

In JAVA:

String uuid = UUID.randomUUID () .toString ()

Sequence

1. What is a sequence?

SEQUENCE is a database object used to generate unique numeric values.

The value of the sequence is automatically generated by the Oracle program in increasing or decreasing order, which is usually used to automatically generate the primary key value of the table, which is an efficient way to obtain the unique key value.

The sequence is an independent database object, and the table is the opposite object, and the sequence is not attached to the table.

Usually a sequence provides primary key values for one table, but a sequence can also provide primary keys for multiple tables.

two。 Create a sequence

CREATE SEQUENCE [schema.] sequence_name

[START WITH i] [INCREMENT BY j]

[MAXVALUE m | NOMAXVALUE]

[MINVALUE n | NOMINVALUE]

[CYCLE | NOCYCLE] [CACHE p | NOCACHE]

Sequence_name is the sequence name, which will be created under the schema scheme. Schema is the user name, which can be left unwritten for your own users.

The first sequence value of the sequence is I and the step (that is, step size) is j

If j is an integer, it is increasing, if it is negative, it is decreasing.

The maximum value that a sequence can generate is m, and the minimum value n

If no optional parameters are set, the first value of the sequence is 1 and the step is 1

CYCLE indicates whether the sequence is reused after incrementing to the maximum or decreasing to the minimum. If it is decreasing and has a maximum value, start with the maximum value. If it is incremented and has a minimum value, start with the minimum value. If it does not start with the value specified by START WITH. Default is NOCYCLE

CACHE is used to specify that p pieces of data are prefetched in the cache first to improve the efficiency of generating sequence values. The default is 20.

3. Use sequenc

Eg: sequence start data: 100, step 10, then the sequence number is 100110120130.

CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 10

There are 2 pseudo columns in the sequence:

-NEXTVAL: gets the next value of the sequence

-CURRENT: gets the current value of the sequence

After the sequence is created, NEXTVAL must be performed once before you can use CURRENT

Gets the first value of the sequence and inserts a new record for the EMP table using the sequence value

SELECT emp_seq.NEXTVAL FROM DUAL;// first execution: 100; second execution: 110

INSERT INTO emp (epmno, ename) VALUES (emp_seq.NEXTVAL, 'DONNA')

4. Delete sequence

DROP SEQUENCE sequence_name

Indexes

1. The principle of index

An index is a tree structure that allows values to access a data row in a data table. It is introduced to improve query efficiency. It is an object independent of the table and can be stored in a different table space (TABLESPACE).

Index records contain index keywords and pointers to data in the table (address)

There are much less Istroke O operations on indexes than on tables.

Once the index is established, it will be automatically maintained by the Oracle system. There is no need to specify which index to use in the query statement.

Index is a mechanism to improve query efficiency.

two。 Create an index

CREATE [UNIQUE] INDEX index_name ON table (column [, column...])

-index_name represents the index name

-table indicates that

-column represents the column name, and you can create a single-column index or a composite index

-UNIQUE represents a unique index

-the column specified by the index is a common column in the WHERE clause when querying

A composite index, also known as a multi-column index, is an index based on multiple columns

If you often use job,sal as a sorting basis in the ORDER BY clause, you can build a composite index:

CREATE INDEX idx_emp_job_sal ON emp (job, sal)

The index [idx_emp_job_sal] is automatically applied as the following query

SELECT empno, name, sal, job FROM amp ORDER BY job,sal

3. Create a function-based index

You can build an index on the column based on the UPPER function:

CREATE INDEX emp_name_upper_idx ON emp (UPPER (name))

The index [emp_name_upper_idx] is automatically applied as the following query

SELECT empno FROM emp WHERE UPPER (ename) = 'KING'

4. Modify and delete indexes

If you often perform DML operations on index columns, you need to rebuild the index periodically to improve the space utilization of the index: ALTER INDEX index_name REBUILD

When there is an unreasonable index on a table, it will lead to a decline in operational performance. Delete index: DROP INDEX index_name

5. Rational use of index to improve query efficiency

Create an index for columns that appear frequently in the WHERE clause

Index the fields that often appear after the ORDER BY,DISTINCT. If a composite index is established, the order of the fields in the index should be the same as the order of the fields after these keywords

Create an index on a column that is often a join condition for a table

Do not build indexes on tables that often do DML operations

Do not index on small tables

Limit the number of indexes on the table. The more indexes, the better.

Delete rarely used, unreasonable indexes

two。 Constraint

Overview of constraints

1. The role of constraints

The full name of CONSTRAINT is constraint condition, also known as integrity constraint condition.

Constraints can ensure the integrity of the data in the table and the business logic between the data.

two。 Type of constraint

-non-null constraint (Not Null), referred to as NN

-uniqueness constraint (Unique), UK for short

-Primary key constraint (Primary Key), or competition for short

-Foreign key constraint (Foreign Key), abbreviated as FK

-check constraints (Check), referred to as CK

Non-empty constraint

1. Add a non-empty constraint when creating a table

Column-level constraint: a constraint declared at the same time as the specified column in the table is created

CREATE TABLE employees (id NUMBER (6)

Name VARCHAR2 (30) NOT NULL,// column level constraint 1

Salary NUMBER (7 dint 2)

Hiredate DATE CONSTRAINT employees_hiredate_nn NOT NULL// column level constraint 2

);

NOT NULL can only be a column-level constraint

two。 Add a non-empty constraint when modifying a table

ALTER TABLE employees MODIFY (id NUMBER (6) NOT NULL)

3. Cancel a non-empty constraint

ALTER TABLE employees MODIFY (id NUMBER (6) null)

Uniqueness constraint

1. What is a uniqueness constraint

The Unique constraint is used to ensure that fields or combinations of fields do not have duplicate values.

When a unique constraint is defined for a column of a table, the value of that column is not allowed to repeat, but it is allowed to be NULL

two。 Add uniqueness constraint

CREATE TABLE employees (id NUMBER (6) UNIQUE,// column level constraint

Name VARCHAR2 (30)

Email VARCHAR2 (50)

Salary NUMBER (7 dint 2)

Hiredate DATE

CONSTRAINT employees_email_uk UNIQUE (email) / / Table-level constraints

);

The column-level constraint is to specify the constraint condition when the table declares a column, the table-level constraint is to add the constraint separately after all the columns are declared, and which column the constraint object is specified in parentheses.

Uniqueness constraints can be either column-level or table-level constraints

Add the uniqueness constraint after the table is created: ALTER TABLE employees ADD CONSTRAINT employees_name_uk UNIQUE (name); / / the new uniqueness constraint will only work on the data after the increase.

Primary key constraint

1. The meaning of the primary key

Primary key (Primary Key) constraints are functionally equivalent to a non-empty and unique combination.

Primary key fields can be a single field or a combination of multiple fields

The primary key can be used to determine the only row of data in the table

Only one primary key is allowed in a table, while there is no explicit limit on the number of other constraints.

two。 The principle of selecting primary key

The primary key should be data that is meaningless to the system.

Never update the primary key so that the primary key is of no use other than to uniquely identify a row

The primary key should not contain dynamically changing data, such as timestamps

The primary key should be generated automatically, and there should be no human intervention, lest it have a meaning other than a unique identification.

The primary key is based on a single column as much as possible.

3. Add primary key constraint

Add primary key constraints when creating a table:

CREATE TABLE employees (

Id NUMBER (6) PRIMARY KEY

Name VARCHAR2 (30)

Email VARCHAR2 (50)

Salary NUMBER (7 dint 2)

Hiredate DATE

);

Create a primary key constraint after the table is created, and customize the constraint name:

CREATE TABLE employees (

Id NUMBER (6)

Name VARCHAR2 (30)

Email VARCHAR2 (50)

Salary NUMBER (7 dint 2)

Hiredate DATE

);

ALTER TABLE employees ADD CONSTRAINT employees ADD CONSTRAINT employees_id_pk PRIMARY KEY (id)

Foreign key constraint

1. The meaning of Foreign key constraint

Foreign key constraints are defined on the fields of two tables or two fields of a table, which are used to ensure the relationship between the two related fields.

Dept table: primary or parent table

Emp: from tables or child tables

Deptno (competitive) nameloc10 Research and Development Department Beijing 20 sales Department Shanghai

Empno (Competition) namedeptno (FK) 1001 Liu Xin 101002 Li Suhai 10

1. Add a foreign key constraint

Create the table first, and then establish the foreign key constraint after the table is built.

CREATE TABLE employees (

Id NUMBER (6)

Name VARCHAR2 (30)

Email VARCHAR2 (50)

Salary NUMBER (7 dint 2)

Deptno NUMBER (4)

);

ALTER TABLE employees ADD CONSTRAINT employees_deptno_fk FOREIGN KEY (deptno) REFERENCES dept (deptno)

two。 Maintenance of consistency by foreign key constraints

Foreign key constraints include two aspects of data constraints:

-the column values of the foreign keys defined on the table must be selected from the column values referenced in the main table, or NULL

When the value of the reference column of the master table is referenced by the slave table, the row record of the master table is not allowed to be deleted

3. Performance degradation caused by foreign key constraints

If a foreign key is established on a table with frequent DML operations, each DML operation will cause the database to automatically check the corresponding table associated with the foreign key, resulting in overhead. If the logic has been controlled in the program, these judgments will add additional burden and can be saved.

Foreign keys determine the order of master-slave table generation, which sometimes affects business logic.

4. Association does not necessarily require foreign key constraints

Ensure that the complete row of data can be controlled by a program or trigger

Simplify development and maintain data without considering foreign key constraints

There is no need to consider the time-consuming of foreign keys in DML operations with large amounts of data.

Check constraint

1. What is a check constraint

Check constraints are used to force each value on a field to meet the conditions defined in Check

When data is added or modified to a column that defines a Check constraint, the data must meet the conditions defined in the Check constraint

two。 Add check constraint

Eg: employee salary must be greater than 2000

ALTER TABLE employees4 ADD CONSTRAINT employees4_salary_check CHECK (salary > 2000)

3. Delete a constraint

ALTER TABLE employees DROP CONSTRAINT employees_salary_check

View the constrained data dictionary

SELECT constraint_name,constraint_type FROM user_constraints WHERE table_name = 'EMPLOYEES'

-constraint_type: constraint type

-constraint_name: constraint name

SELECT * FROM user_constraints WHERE table_name = 'EMPLOYEES'

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.