Menu
homeiOS

What are VIEWS views in databases? And why are they needed? Views in SQL

Last update: 08/14/2017

Views or Views represent virtual tables. But unlike regular standard tables in a database, views contain queries that dynamically retrieve the data being used.

Views give us a number of advantages. They simplify complex SQL operations. They protect data because views can give access to part of a table rather than the entire table. Views also allow you to return formatted values ​​from tables in a useful and useful way.

To create a view, use the CREATE VIEW command, which has the following form:

CREATE VIEW view_name [(column_1, column_2, ....)] AS SELECT_expression

For example, let's say we have three related tables:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL); CREATE TABLE Customers (Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(30) NOT NULL); CREATE TABLE Orders (Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL );

Now let's add the following view to the database that contains the table data:

CREATE VIEW OrdersProductsCustomers AS SELECT Orders.CreatedAt AS OrderDate, Customers.FirstName AS Customer, Products.ProductName As Product FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id

That is, this view will actually return summary data from three tables. And after it is created, we can see it in the Views node of the selected database in SQL Server Management Studio:

Now we use the view created above to get the data:

SELECT * FROM OrdersProductsCustomers

When creating views, keep in mind that views, like tables, must have unique names within the same database.

Views can have a maximum of 1024 columns and can access a maximum of 256 tables.

You can also create views based on other views. Such views are also called nested views. However, the nesting level cannot be greater than 32.

A SELECT command used in a view cannot include INTO or ORDER BY clauses (unless a TOP or OFFSET clause is also used). If you need to sort the data in the view, then the ORDER BY clause is used in the SELECT command, which retrieves the data from the view.

Also, when creating a view, you can define a set of its columns:

CREATE VIEW OrdersProductsCustomers2 (OrderDate, Customer,Product) AS SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id

Changing the View

To change the view, use the ALTER VIEW command. This command has almost the same syntax as CREATE VIEW :

ALTER VIEW view_name [(column_1, column_2, ....)] AS SELECT_expression

For example, let's change the OrdersProductsCustomers view created above:

ALTER VIEW OrdersProductsCustomers AS SELECT Orders.CreatedAt AS OrderDate, Customers.FirstName AS Customer, Products.ProductName AS Product, Products.Manufacturer AS Manufacturer FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers .Id

Delete a view

To remove a view, use the DROP VIEW command:

DROP VIEW OrdersProductsCustomers

It's also worth noting that when you delete tables, you should also delete the views that use those tables.

1) Concept of representation
Views are database objects that do not contain their own tables, but their content is taken from other tables or views by executing a query.

2) Creating views
CREATE VIEW
[()]
AS
CHECK OPTION]

Notes:
In SQL Server, the text of a view can be encrypted using the WITH ENCRYPTION option after the view name.

3) Removing views
DROP VIEW CASDADE|RESTRICT

Note:
RESTRICT– There must not be any reference to the view being deleted in the view and constraints, otherwise the deletion will be denied.
CASADE– means deleting all objects that reference this view.

4) Keywords
a) RECURSIVE
A representation is created that receives values ​​from itself.
b) WITH CHECK OPTION
Prevents table updates based on views if the data that is changed or added is not reflected in the view.
The prohibition only applies to values ​​that do not meet the conditions specified in the WHERE clause.
c) LOCAL
Controls that changes to base tables are reflected only in the current view.
d) CASCADED
Controls whether changes are reflected in all views defined on a given view.

5) Limitations and features
1. Column names are usually specified when some columns are calculated and therefore not named, and when two or more columns have the same name in the corresponding tables in the query. Always in InterBase.
2. In some DBMSs, you cannot use the ORDER BY section, which provides sorting.
3. Views can be connected to both base tables and other views using queries on both objects.

6) Criteria for updating views
1. It should be based on only one table. It is desirable that it includes the primary key of the table.
2. It should not contain columns resulting from aggregation functions.
3. It cannot contain a DISTINCT specification in its definition.
4. It cannot use GROUP BY or HAVING in its definition.
5. It should not contain subqueries.
6. If it is defined on another view, then it must be updatable.
7. It cannot include constants, strings or expressions in the list of output fields. Rearranging and renaming fields is not allowed.
8. For an INSERT statement, it must include any fields from the underlying views of the base table that have NOT NULL constraints, but the default value may be set to another value.

7) Examples

1. CREATE VIEW LondonStaff
AS SELECT * FROM SalesPeople WHERE City=’London’

2. CREATE VIEW SalesOwn
AS SELECT SNum, SName, City FROM SalesPeople

3. CREATE VIEW NameOrders
AS SELECT ONum, Amt, A.SNum, SName, CName
FROM Orders A, Customer B, SalesPeople C
WHERE A.CNum=B.CNum AND A.SNUM=C.SNum

Examples for blocking updates:

1. CREATE VIEW HighRating AS SELECT CNum, Rating

2. Add a line that the view does not see:
INSERT INTO HighRating VALUES(2018, 200)
3. We prohibit adding lines outside of visibility:
CREATE VIEW HighRating AS SELECT CNum, Rating
FROM Customer WHERE Rating=300
WITH CHECK OPTION
4. Create a new one that allows you to add again:
CREATE VIEW MyRating AS SELECT * FROM HighRating

Performance is a virtual table. In reality, a view is just the result of a SELECT statement, which is stored in a memory structure that resembles SQL table, for example, Oracle. For those working with a view, manipulating its data is no different from manipulating table data. In some cases, the user can enter data into the view as if it were a table. When working with a presentation you need to remember that:

  • Views add a layer of data security (for example, you can create a view on a table where the user who performs a SELECT on the view only sees the salary information)
  • Views can hide data complexity by combining relevant information from multiple tables
  • Views can hide the actual column names, which are sometimes difficult to understand, and show simpler names.

The view is created using the command. Once created, views become part of the schema of the user who created them. You can reassign them to another user when he has the system privilege CREATE ANY VIEW.

CREATE VIEW Oracle Command Syntax

Create View Command Syntax

Basic Keywords and Parameters CREATE VIEW Oracle :
OR REPLACE, FORCE, NOFORCE, Schema, View, Alias, AS subquery, WITH CHECK OPTION, Constraint

OR REPLACE- recreates the view if it already exists. You can use this option to change the definition of a view without having to delete it, recreate it, and reassign all object privileges that were assigned to that view;

FORCE- creates a view regardless of whether the view's underlying tables exist and whether the owner of the schema containing the view has privileges on those tables. It is necessary that both of these conditions be satisfied before any proposals can be issued on this representation. SELECT, INSERT, UPDATE or DELETE. The default setting is NOFORCE;

NOFORCE- creates a view only if the base tables of this view exist, and the owner of the schema containing the view has privileges on these tables;

Schema- the schema in which the representation is created. If SCHEME is omitted, then ORACLE creates a view in the user's schema;

View- keyword view this is the name of the view to create;

Alias— specifies names for the expressions selected by the view query. The number of aliases must match the number of expressions selected by the subquery. Aliases must comply with the naming rules for schema objects. Aliases must be unique within a view. If aliases are omitted, then ORACLE identifies them by column names or aliases in the view query. Therefore, you need to use aliases if the view query contains expressions in addition to column names;

AS subquery— identifies the columns and rows of the tables on which the view is based. A submission request can be any proposal SELECT, not containing phrases ORDER BY or FOR UPDATE. Its selection list can contain up to 254 expressions;

WITH CHECK OPTION- indicates that inserts and updates that will be performed through this query should result only in rows that can be selected by a query of the same view. Option CHECK OPTION cannot guarantee this if there is a subquery in the request for this view or any view on which this view is based. In other words, when specifying the parameter WITH CHECK OPTION the user cannot enter, delete or update information from a table from which he cannot read information through a simple view (created from data in a single table). An updatable view that uses multiple related tables cannot be created with this option;

Constraint— the name that is assigned to the constraint CHECK OPTION. If this identifier is omitted, then ORACLE automatically assigns this constraint a name like this:

SYS_Cn where n is an integer that makes the constraint name unique within the database.

The advantage of using a view instead of a base table is that it updates automatically when the tables that form it change. The contents of the view are not frozen, but are recalculated whenever you reference the view in a command.

If the update commands DML (INSERT, UPDATE, DELETE) can be applied to a view, the view is said to be updatable; otherwise it is read-only. A view is updatable if the following criteria were taken into account when creating it:

  • the view must include the table's primary key
  • should not contain fields obtained as a result of applying aggregation functions
  • must not contain DISTINCT, GROUP BY, HAVING in its definition
  • can be defined on another view, but that view must be updatable
  • cannot contain constants, strings, or expressions (for example, comm*100) in the list of selectable output fields

Example 1.
CREATE VIEW Oracle.
A simple view that is created from data from a single table:

London_view AS SELECT * FROM Salespeople WHERE city ​​= 'London';

Example 2.
CREATE VIEW Oracle.
When creating a view, you can specify new field names:

Rating_view(rating,number) AS SELECT rating, COUNT(*) FROM Customers GROUP BY rating;

Example 3.
CREATE VIEW Oracle.
Views can receive information from any number of base tables:

Nameorders AS SELECT onum, amt, a.snum, sname, cname FROM Orders a, Customers b, Salespeople C WHERE a.cnum = b.cnum AND a.snum = c.snum;

Example 4.
CREATE VIEW Oracle.
When creating views, you can use subqueries, including related subqueries:

Sales_view AS SELECT b.odate, a.snum, a.sname, FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = ( SELECT MAX(amt) FROM Orders from WHERE c.odate = b.odate);

Example 5.
CREATE VIEW Oracle.

Empl_v04 AS SELECT e.eid, e.sname, e.fname, e.otch, p.pname, d.dname FROM posts p, departments d, employees e WHERE e.did = d.did AND e.pid = p.pid;

Representation

Removal

Renaming

Change

Managing Stored Procedures

To change an existing procedure, use the ALTER PROC statement; the parameters of this command are similar to the parameters of the command to create a procedure.

To do this, you need to use a special system stored procedure:

sp_rename 'ObjectName' 'NewObjectName'.

To delete a stored procedure, use the Transact‑SQL command:

DROP PROC ProcedureName.

Performance(View) is another object that makes up the logical structure of any database. The end-user view looks like a table, but does not contain data, but only represents it. The physically represented data is located in various database tables.

The view is implemented in the form of a saved query, on the basis of which a selection is made from various database tables.

Views have the following benefits:

· ensure the confidentiality of information, because allow you to display only the necessary information, hiding certain fields;

· simplify the presentation of data, because the user works with the view as a single table, which is created based on data selection from several tables;

· manage data access rights, for example, instead of granting permissions to execute queries on certain table fields, it is easier to allow queries to be executed through a view.

MS SQL Server provides various ways to create views: using Transact-SQL tools and in the administration utility Management Studio.

To create a view, use the CREATE VIEW command, and role members have the right to execute it sysadmin, db_owner, db_dlladmin:

CREATE VIEW ViewName [(field [,...n])]

QuerySamples

When specified ViewName it is necessary to adhere to the previously defined rules for naming objects; also, this name should not coincide with the name of an already existing table in the database. Parameter WITH ENCRYPTION defines the encryption of the request code and ensures that users cannot view or use it.

QuerySamples is a SELECT statement whose parameters determine the contents of the view. View field names are specified either using aliases in the select statement or specified in a parameter field.

For example, let's create a view containing only the following information about the company's employees AdventureWorks , such as: employee’s position and login, date of birth.

CREATE VIEW InfoEmployees ([Number], [Last Name], [Date of Birth]) AS

SELECT BusinessEntityID, JobTitle + "(" + LoginID + ")",

CONVERT (char(10), BirthDate, 104)

FROM HumanResources.Employee

To view the contents of the projection, the following query is executed:

SELECT * FROM InfoEmployees

Using this view, access to some fields of the source table is limited Employee, in this case they say that the table is superimposed vertical filter, i.e. Access to some table fields is limited without column-level protection.

If a condition for selecting records is defined in the selection query code, then the table is said to be subject to horizontal filter. For example, the following view provides access to information about manufacturers that have online services for ordering a product:

CREATE VIEW OnlineVendors

FROM Purchasing.Vendor

WHERE PurchasingWebServiceURL IS NOT NULL

A SELECT command of any complexity may be specified in a selection request, but it is prohibited to use sections ORDER BY, which can later be used when retrieving data from the created view. It is also recommended that you only create views based on tables that have an inner join.

For example, let's create a view that displays the total cost of each order, indicating the customer and his number:

CREATE VIEW InfoOrders

SELECT FirstName + " " + LastName as [Company Name],

SalesOrderHeader.SalesOrderID as [Order number],

Convert (money, sum(UnitPrice*OrderQty*(1-UnitPriceDiscount)),0) as [Total]

FROM (Person.Contact INNER JOIN Sales.SalesOrderHeader

ON Contact.ContactID=SalesOrderHeader.ContactID)

INNER JOIN Sales.SalesOrderDetail

ON SalesOrderHeader.SalesOrderID=SalesOrderDetail.SalesOrderID

GROUP BY SalesOrderHeader.SalesOrderID, FirstName + " " + LastName

Keep in mind that using views does not improve performance. Calling a view causes its internal code to be executed, so in the best case, the view NOT will reduce database performance.

Many novice administrators and database programmers, and simply system administrators who maintain a certain database, do not know what a view is or VIEWS, and why they are needed at all. Now we will try to figure out what it is.

Let's start with a little theory.

What is VIEWS?

VIEWS– representation, or for example in PostgreSQL they are called “ Species"(i.e. View), Russian admins often call them views, i.e. one view is a view. It is a stored query to a database, it can also be called a virtual table, but this table does not store data, but only the query itself. But, nevertheless, the view can be accessed as a regular table and data can be retrieved from it.

We are talking about databases that use the SQL language, based on this, we can conclude that VIEWS can be created in this language. This is a very common object in a database, so all DBMSs have the ability to create views in a GUI by clicking the " Create View" or " Create a new view", and also, of course, using the "CREATE VIEW" statement.

But before we learn how to create views, let's talk about why they are needed and what advantages they will give us.

Why are representations needed?

One of the main advantages of views is that they greatly simplify interaction with data in the database. Let's say you need to make a sample that is complex in its structure every time, and as you know, the request for a sample can be, well, just very complex and there is no limit to this. And if there were no views, then you would have to run this request every time, or even modify it, for example, to insert conditions. And since we have objects such as views, we don’t have to do this. We will simply create one view for everything, and then we will access it using simple queries, which can also be made complex if necessary. For example, views can also be joined with other tables or other views.

Views can also be accessed from applications, for example, you need to display some kind of report, the formation of which requires some calculations; this can easily be implemented by writing the necessary query ( in which data will be calculated, for example from different tables) and inserting this request into the view. And then access this view, for example, using such a simple request as:

SELECT * FROM TableName

How to create a VIEWS view?

Now let's talk about how to create these same views. Firstly, I’ll say right away that this requires knowledge of SQL ( for building complex queries). Secondly, you must decide in advance what you need to output as a result of a particular request. We will not consider the process of creating a view by pressing buttons, as it is quite simple. We will look at creating VIEWS using the SQL language ( although this is also simple).

For example, in PostgreSQL, a view creation request would look like this:

CREATE VIEW MyView AS SELECT id, name, org FROM work.TableName

  • CREATE VIEW – command to create a view;
  • MyView – the name of your future view;
  • SELECT id, name, org FROM work.TableName – selection request.

Here we used a simple selection query. You, in turn, can write any query, even combining several tables and conditions for them.

The full syntax for the CREATE VIEW command (in PostgreSQL) is as follows:

CREATE VIEW view_name [(column_list)] AS select_statement CHECK OPTION]

Once you have created a view, you can access it. And the data that the view will display will change depending on changes in the data in the source tables, since the data in the view is formed when this view is accessed. Based on this, we can conclude that the data that the view displays will always be up to date.

That's all, I hope you now have an idea of ​​what VIEWS is, bye!