MySQL Views

MySQL Views

view is a named SQL query statement, stored in the database and considered as a virtual table.

It is a subset of a database, based on a query that runs from one or more database tables.

It can be used to save frequently used, complex queries.

It contains select rows and columns fields from one or more tables in the database.

 

Creating view

To create a view, use the CREATE VIEW statement.

Syntax:-

CREATE [OR REPLACE] VIEW view_name [(column_list)]

AS

  select-statement;

 

1.      Specify the unique name of the view after the CREATE VIEW keywords in a database.

views and tables share the same namespace in the same database, so the view name cannot the same as the existing table name.

2.      REPLACE option use for if an existing view already exists. If the view does not exist, then the OR REPLACE has not work.

3.      Specify a columns list for the view. By default, the view columns are derived from the SELECT statement. But, can explicitly specify the column list in parentheses following with view name.

4.      Specify a SELECT statement for the view.

 

The SELECT  statement use for query data from tables or views.

By default, the CREATE VIEW statement creates a view in the current database. If want to explicitly create a view in a given database, then use view name with the database name.

 

example: -

Two table payments and customers.

 

 

 

CREATE VIEW customerPayments

AS

SELECT   customerName, checkNumber, paymentDate, amount FROM customers INNER JOIN payments USING (customerNumber);

Call view;

SELECT * FROM customerPayments;

 

Note:-

·         View does not physically store the data.

·         When use the select statement against the view, mysql executes the original query specified in the view’s definition and returns the result set. So, this reason, view is referred as a virtual table.

 

 

Example: -

create a view called daysofweek that return 7 days of a week by executing the following query:

CREATE VIEW daysofweek (day) AS

    SELECT 'Mon' UNION  SELECT 'Tue'  UNION  SELECT 'Web' UNION SELECT 'Thu'

    UNION SELECT 'Fri' UNION SELECT 'Sat' UNION SELECT 'Sun';

 

Call the daysofweek view:

SELECT * FROM daysofweek;

Output:

 

Types of views:

Read-only View : Allows only SELECT operations.

Updateable View : Allows SELECT as well as INSERT , UPDATE and DELETE operations.

 

Advantages of MySQL Views

MySQL views provide following advantages.

1) Simplify complex query

Any frequently used complex query, create a view which based on reference by using a simple SELECT statement instead of typing the query all over again.

2) Make the business logic consistent

To repeatedly write the same formula in every query or a query has complex business logic. To which use consistent logic across queries, then use a view to store the calculation and hide the complexity.

3) Add extra security layers

A table has a lot of sensitive data such as personal and banking information. For extra security using views and privileges.

Example: - the table employees may contain SSN and address information, which accessible by the HR department only. If want to expose general information such as first name, last name, and gender to the General Administration (GA) department, can create a view based on these columns and grant the users of the GA department to the view, not the entire table employees.

4) Enable backward compatibility

In inheritance systems, views can enable backward compatibility.

Example: - To normalize a big table into many smaller ones and don’t want to impact the current applications that reference the table. In this case, create a view whose name is the same as the table based on the new tables so that all applications can reference the view as if it were a table.

 

Note: - a view and table cannot have the same name so need to drop the table first before creating a view whose name is the same as the deleted table.

 

Uses of a View :
A good database should contain views due to the given reasons:

1.      Restricting data access –
provide an extra level table security by restricting access.

2.      Hiding data complexity –
hide the complexity in a multiple table join.

3.      Simplify commands for the user –
allows the user to select information from multiple tables without requiring knowledge –      

“ how to perform a join”.

4.      Store complex queries –
used to store complex queries.

5.      Rename Columns –
also used to rename the columns without affecting the base tables specified in select statement.

6.      Multiple view facility –
Different views can be created on the same table for different users.

 

Examples:-

CREATE VIEW salesperorder represents total sales per order from orderDetails table which details are:-

 

CREATE VIEW salePerOrder AS

    SELECT orderNumber, SUM(quantityOrdered * priceEach) total FROM orderDetails GROUP by orderNumber     ORDER BY total DESC;

 

For execute salePerOrder view use a simple SELECT  statement.

SELECT * FROM salePerOrder;

Output:-

 

Example:-  Creating a view based on another view

create a view called bigSalesOrder based on the salesPerOrder view to show every sales order whose total is greater than 60,000 as follows:

 

CREATE VIEW bigSalesOrder AS

    SELECT orderNumber,  ROUND(total,2) as total FROM salePerOrder WHERE total > 60000;

 

Query the data from the bigSalesOrder view:

SELECT  orderNumber, total FROM bigSalesOrder;

Output :- 

 

In view INSERT, or UPDATE and DELETE statement can be use.

 

Creating View from multiple tables: 

create a View named MarksView from two tables StudentDetails and StudentMarks. 

 

CREATE VIEW MarksView AS

SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS

FROM StudentDetails, StudentMarks WHERE StudentDetails.NAME = StudentMarks.NAME;

 

To display data of View MarksView:

SELECT * FROM MarksView;

 

updatable view in MySQL

some certain conditions needed to be satisfied to update a view. If any one of these conditions is not met, then will not be allowed to update the view.

1.      The SELECT statement which is used to create the view should not include GROUP BY clause or ORDER BY clause.

2.      The SELECT statement should not have the DISTINCT keyword.

3.      The View should have all NOT NULL values.

4.      The view should not be created using nested queries or complex queries.

5.      The view should be created from a single table. If the view is created using multiple tables then we will not be allowed to update the view.

 

example:-

create a view named officeInfo  based on the offices  table.

 

CREATE VIEW officeInfo

 AS

   SELECT officeCode, phone, city FROM offices;

 

query data from the officeInfo view:

SELECT * FROM officeInfo;

 

To change the phone number of the office with officeCode  4 through the officeInfo view using the UPDATE statement.

 

UPDATE officeInfo SET  phone = '+33 14 723 5555' WHERE officeCode = 4;

 

To verify the change, query the data from the officeInfo  view:

SELECT  * FROM officeInfo WHERE officeCode = 4;

Output: 

 

Inserting a row in a view:
Insert a row in a View in a same way as do in a table. use the INSERT INTO statement of SQL to insert a row in a View.

Syntax:

INSERT INTO view_name(column1, column2 , column3,..)VALUES(value1, value2, value3..);

 

Example:-

INSERT INTO DetailsView(NAME, ADDRESS) VALUES("Suresh","Gurgaon");

 

 

Removing rows through the view

-- create a new table named items

CREATE TABLE items ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL,  price DECIMAL(11 , 2 ) NOT NULL );

 

-- insert data into the items table

INSERT INTO items(name,price)  VALUES('Laptop',700.56),('Desktop',699.99),('iPad',700.50) ;

 

-- create a view based on items table

CREATE VIEW LuxuryItems AS SELECT * FROM  items WHERE price > 700;

-- query data from the LuxuryItems view

SELECT  * FROM LuxuryItems;

Output :- 

 
















Use DELETE statement to remove a row with id value 3.

DELETE FROM LuxuryItems WHERE id = 3;

 

Check the data through the view again.

SELECT  * FROM LuxuryItems;

Output :- 

 

MySQL DROP VIEW statement

The DROP VIEW statement deletes a view completely from the database.

Syntax:-

DROP VIEW [IF EXISTS] view_name;

specify the view name that to drop after the DROP VIEW keywords. The optional IF EXISTS option conditionally removes the view only if it exists.

syntax:

DROP VIEW [IF EXISTS] view_name1 [,view_name2]...;

In this syntax:-

specify a list of comma-separated views after the DROP VIEW keywords.

If the list contains a view that doesn’t exist, the DROP VIEW statement will fail and won’t delete any view and, if use the IF EXISTS option, the DROP VIEW statement will generate a NOTE for each non-existing view.

============================================================