17. View--sql

First, what is a view

The view is a virtual table

Why use views

• Reuse SQL statements.
• simplifies complex SQL operations. After you write a query, you can easily reuse it without having to know its basic query details.
• Use part of the table instead of the entire table.
• Protect data. You can grant a user permission to access a specific part of a table, rather than an entire table.
• Change the data format and representation. Views can return data that differs from the representation and format of the underlying table.

Warning: Performance issues
Because the view does not contain data, each time you use the view, you must handle all the searches that are required when the query executes. If you create complex views or nesting with multiple joins and filters
The view, performance may drop very badly. Therefore, you should test before deploying an application that uses a large number of views.

Rules and restrictions for views

• As with tables, the view must be uniquely named (You cannot give the view the same name as another view or table).
• There is no limit to the number of views that can be created.
• To create a view, you must have sufficient access rights. These permissions are typically granted by database administrators.
• Views can be nested, that is, you can construct a view with queries that retrieve data from other views. The number of nested layers allowed differs in different DBMS (nested views can severely degrade query performance, so it should be thoroughly tested before being used in a production environment).
• Many DBMS prohibit the use of an ORDER BY clause in a view query.
• Some DBMS require naming all the columns returned, and if the column is a calculated field, you need to use an alias (for more information about column aliases, see Lesson 7th).
• Views cannot be indexed or have associated triggers or default values.
• Some DBMS use a view as a read-only query, which means that data can be retrieved from the view, but the data cannot be written back to the underlying table. For details, refer to the specific DBMS documentation.
• Some DBMS allow the creation of such views, which cannot be inserted or updated to cause rows to no longer be part of the view. For example, there is a view that retrieves only customers with an e-mail address. If you update a customer, deleting his e-mail address will make the customer no longer part of the view. This is the default behavior and is allowed, but some DBMS may prevent this from happening.

Ii. Creating and Using views

Description: View Rename

Delete The view, you can use the drop statement, whose syntax is drop view viewname;.
Overwrite (or update) The view, you must first delete it, and then recreate it.

CREATE VIEW  as SELECT Cust_name, cust_contact, prod_id  from Customers, Orders, OrderItems WHERE = orders.cust_id  and = Orders.order_num;

Analysis
This statement creates a view named Productcustomers that joins three tables and returns a list of all customers who have ordered any product. If you perform a select * from
Productcustomers, customers who have ordered any product will be listed.

SELECT Cust_name, Cust_contact  from productcustomers WHERE = ' RGAN01 ';

Analysis
This statement retrieves specific data from the view through a WHERE clause. When the DBMS processes this query, it adds the specified where clause to the existing WHERE clause in the view query.
To properly filter the data.

Tip: Create a reusable view
Creating a view that is not bound to specific data is a good idea. For example, the view created above returns customers ordering all products, not just RGA N01 (this view is created first). Extended
The scope of a view not only allows it to be reused, but it may be more useful. Doing so does not require creating and maintaining multiple similar views.

17. View--sql