Role of database view

Role of database view

09:36:42 | category:

Sybase | font size subscription

I wrote my understanding of the view into this post and discussed it with everyone. A view has the following functions:

1. Simplify applications.

As an entity in the database, a view actually exists only its script, and its content does not actually exist separately. Generally, you can analyze complex applications from a functional perspective and separate them from other applications. For this function, different database entities (such as processes) can be made according to the actual situation, and some can be made into views. In this way, the upper-layer application can fetch data from the view.

In addition, you can encapsulate access to the remote database in the view to make it transparent to upper-layer applications.

2. You can sort record sets after union.

It is impossible to sort the results of the following statements directly (at least I don't know how to sort them directly ).

Select a. ID from

Union

Select B. ID from B;

Therefore, you can make the preceding statements into a view. Set the view name to a_ B:

Select ID from a_ B order by ID;

3. You can implement certain permission control.

You can create a view of a part of the table as needed for certain roles. You can create a view (vertical) for some records in a table, or create a view (horizontal) for some fields in a table, or both.

I have thought so much for the time being. I hope you can give me more comments and comments.

--------------------------------------------------------------------

ViewIs a virtual table whose content is defined by the query. Like a real table, a view contains a series of columns and row data with names. However, the view is notDatabaseIn the form of stored data value sets. Rows and columns are used to define tables referenced by View queries and dynamically generate tables when views are referenced.
For the base table referenced in the table, the role of the ViewClassSimilar to filtering. The filtering of the definition view can be from one or more tables of the current or other databases, or other views. Distributed queries can also be used to define the use of multiple heterogeneousSource data. This method is useful if several different servers store data in different regions of the Organization and you need to combine the data with similar structures on these servers.

I. Role of views

* Simplicity. What you see is what you need. A view not only simplifies users' understanding of data, but also simplifies their operations. Frequently Used queries can be defined as views, so that you do not have to specify all the conditions for each subsequent operation.

* Security. Users can only query and modify the data they can see through the view. Other data in the database is neither visible nor accessible. Database authorization commands allow each user to restrict the retrieval of a database to a specific database.ObjectBut cannot be authorized to a specific row or column of the database. Through views, users can be restricted to different subsets of data:

The permission can be restricted to a subset of rows in the base table.

The permission can be restricted to a subset of columns in the base table.

The permission can be restricted to the row and column subsets of the base table.

The permission can be restricted to the rows restricted by the connection of multiple base tables.

The permission can be restricted to the Statistical Summary of data in the base table.

The permission can be restricted to a subset of another view, or a subset of some views and merged base tables.

* Logical Data independence. View helps you avoid the impact of changes in the real table structure.

Ii. Advantages of views

(1) views can simplify user operations

(2) The view mechanism allows users to query the same data in different ways.

(3) view databaseReconstructionProvides a certain degree of logical independence

(4) views can provide security protection for confidential data

Iii. view Security

View security prevents unauthorized users from viewing specific rows or columns. You can only view specific rows in the table as follows:

1. Add a column indicating the user name in the table;

2. Create a view. You can only see rows marked with your username;

3. Authorize the view to other users.

Iv. Logical Data independence

View allows applicationsProgramAnd database tables to a certain extent. If there is no view, the application must be created on the table. With the view, the program can be built on the view, so that the program and the database table are separated by the view. The view can separate the program from the data in the following aspects:

1. If an application is created on a database table, you can create a view on the table when the database table changes. The view shields the changes in the table, so that the application can not move.

2. If an application is created on a database table and the application changes, you can create a view on the table to mask the application changes through the view so that the database table does not move.

3. If the application is built on a view, when the database table changes, you can modify the view on the table and use the view to shield the changes in the table so that the application can remain unchanged.

4. If an application is created on a view, you can modify the view on the table when the application changes. The view shields the application changes, so that the database does not move.

V. View writing format

Create view <VIEW Name> [(column name group)]

AS<Subquery>

Drop view <index Name>

Note: views can be queried like basic tables. However, operations such as adding, deleting, and modifying data using views are limited.

(1) views exported from more than two basic tables

(2) View fields come from field expression Functions

(3) nested queries in view Definitions

(4) view defined on a view that cannot be updated

Vi. create and manage views

(Considerations when creating a view)

(1) create and manage views using the Enterprise Manager

1. Use the Enterprise Manager to create a view:

2. Use the "Wizard" of the Enterprise Manager to create a view:

3. Use the Enterprise Manager's modify View:

Note:Design view:

Add/Delete tables? Add or delete a reference field? Adjust the field order? Set Group

Set filter conditions? Set whether to output referenced fields? Set other view attributes

4. Use the Enterprise Manager's delete View:

(2). UseT-SQLStatement creation and management view

(View the view created by the Enterprise Manager-corresponding T-"project information-SQLStatement) ("view attributes ")

1. create view Syntax: see p130 ~ 135

Example 1: select some fields and records in the 'employee table' and 'Project table' to create a view, and restrict the records in the 'employee table' to a set of records whose departments are "project departments, the view is defined as view_part. The program list is as follows:

Create view view_part

AS

SELECT employee table. No., employee table. Name, employee table. Gender,

Employee table. Department, project table. Project No., project table. Name

FROM project Table inner join employee table ON project table. Owner = employee table. No.

WHERE employee table. Department = 'project'

Example 2: specifying an alias and encrypting the view

Create view Project Information VIEW

(Project name, project customer, project owner, start date, [planned construction period (days)])

With encryption

AS

SELECT project table. Name, customer table. Customer name, employee table. Name, project table. Start date,

DATEDIFF (day, project table. Start Date, project table. End Date)

FROM project Table inner join employee table ON project table. Owner = employee table. No.

Inner join customer table ON project table. Customer = Customer table. Customer ID

WHERE employee table. Department = 'project'

Statement: exec sp_helptext 'Project information view' displayMessageIs:

"The object remarks are encrypted ."

Shows the operation result.

Example 5-3: Use the with check option clause

Question:

If you create a view ygb_view, the program list is as follows:

Create view ygb_view

AS

SELECT * FROM employee table

WHERE employee table. Gender = female execute the following statement to insert a new record:

Insert into ygb_view (name, gender, salary)

Values ('Lili san', 'male', 2300)

The insert operation is successful, but it is unreasonable!

Solution: Use with check option. The program list is as follows:

Create view ygb_view

AS

SELECT * FROM employee table WHERE employee table. Gender = 'femal' With check option

Similarly, insert a new record:

Insert into ygb_view (name, gender, salary) values ('Lili san', 'male', 2300)

Insertion operation will fail!

2. Use alter view to modify VIEW Syntax: see tutorial

3. Delete VIEW DROP VIEW

The syntax for deleting a VIEW using the drop view command is as follows: drop view name 1 [,…]

Example: drop view ygb_view

7. Operate table data using views

(1) adding table data through views

Use the INSERT statement.

Note: A view is a virtual table and does not store data (from its referenced table). The added data is stored in the data table referenced by the view.

Conditional Analysis:

1) You have the permission to insert data into a data table;

2) The view only references some fields in the table. When inserting data, you can only specify the values of the fields to be applied;

3) fields not referenced must meet one of the following conditions:

NULL values are allowed; default values are set; fields are identified; DataTypeIs timestamp or uniqueidentifer;

4) A view cannot contain a combination of multiple fields.

5) The view cannot contain the result of using statistical functions;

6) A view cannot contain DISTINCT or group by clauses;

7) when the definition view uses the with check option, the inserted data should meet the corresponding conditions;

8) if the view references multiple tables, one INSERT statement can only contain data in the same base table;

Example:

First, a new view is created:

Create view ygb_view

AS

SELECT * FROM employee table WHERE employee table. Gender = 'femal' with check option

Then, add a new data record to the base table by executing the following statement:

Insert into ygb_view (name, gender, salary) values ('Lili ping', 'female, 2300)

(2). Update data records

You can use the view to update the data records of the base table (note that the restrictions when using INSERT are also applicable ).

Example 3:

(1) update project_view

Set project owner = 'wang Dali 'where project owner = 'wang Libing'

(2) update project_view

Set end date = DATEADD (day, 50, end date) where customer name = 'cch company'

(3) deleting data records

You can use the DELETE statement and view to DELETE records in any base table.

Note: You must specify fields defined in the view to delete records;

When a view references multiple tables, data cannot be deleted using the DELETE command.

Example 4: delete ygb_view where employee salary <1500

6. Views can be viewed as virtual tables or storage queries. Data that can be accessed through views is not stored in the database as unique objects. The SELECT statement is stored in the database. The result set of the SELECT statement constitutes the virtual table returned by the view. You can use the method used to reference a table to use a virtual table in a Transact-SQL statement by referencing the view name. You can use a view to implement any or all of the following functions:

Limit the user to a specific row in the table.
For example, only employees are allowed to see the rows that record their work in the work tracking table.

Restrict users to specific columns.
For example, employees who are not responsible for payroll processing can only see the name column, office column, work phone column, and department column in the employee table, you cannot see any columns that contain salary information or personal information.

Join the columns in multiple tables to make them look like a table.

7.

 

A view is a table exported from one or more basic tables or other views. You can add, delete, or modify a view.

Create View

The syntax for creating a view is:

Create view view_name

(Column_1,..., column_n)

As select_statements

 

For example, define a view in the authors and publishers tables as follows:

Create view cities (authorname, acity, publishername, pcity)

As select au_lname, authors. City, pub_name, publishers. City

From authors, publishers

Undo a view by using the drop view command

Drop view view_name

VIII. 1. Single-source table View
The data of a view can only be taken from some rows and columns of a basic table. Such a view column corresponds to the row and column of the basic table. The defined view can be used to query and modify data.
2. Multi-source table View
The data of a view can come from multiple tables. The defined view is generally used only for queries but not for data modification.
3. New views defined on existing views
You can create a view on the view. The view as the data source must have been created.
4. Views with expressions
When defining a basic table, to reduce redundant data in the database, the table only stores basic data, and the data derived from the basic data after various computations is generally not stored. However, because the data in the view is not actually stored, you can set some derived attribute columns as needed and save the calculated values in these derived attribute columns. Because these derived attributes do not actually exist in the basic table, they are also called virtual columns. A view that contains virtual columns is also called a view with expressions.

5. View with group statistics
A view with GROUP statistics indicates that a query statement defining a view contains a group by clause. Such a view can only be used for query and cannot be used to modify data.