25 - SQL Views

An SQL SELECT statement generates data and presents in the form of a table that is a virtual table. VIEW is a type of virtual table. A view can be created using a single table or multiple tables. The view can contain all the rows of the table or selected rows.

Views are used to:

  1. Get data from various tables in specified format and generate reports.
  2. Restrict the access to data to the users. Decide who can see and update what data.
  3. Extract and format database data without physically altering the stored data.

Creating VIEWS:

You can create views by using CREATE VIEW statement. The view can be created from a single table, or from multiple tables based on some condition or with some modified or calculated fields.
Syntax:

CREATE VIEW  view_name AS

SELECT columns

FROM table_name

WHERE conditions;

Consider an example where a manager wants to know the status of the projects under him. The data can be in multiple table.  We can create a view for the manager as below:

      

 

CREATE VIEW MANAGER_PROJECT AS

SELECT MANAGER_ID, MANAGER_NAME, PROJECT_ID, PROJECT_NAME,PROJECT_STS

FROM MANAGER_DETAIL

INNER JOIN PROJECT_DETAIL

ON MANAGER_DETAIL.MANAGER_ID = PROJECT_DETAIL.MANAGER_ID;

Example:

For the below EMPLOYEE and DEPTS table, create a view with following fields, EMP_ID, EMP_NAME, DEPT_ID, DEPT_NAME and SALARY.

EMPLOYEE:

+-------------------+-------------------+----------+------------------------+-------------+-------------+
| EMP_ID     | EMMP_NAME | AGEG | PHONE_NUM     | DEPT_ID | SALARY |
+-------------------+-------------------+----------+------------------------+-------------+-------------+
|      1     | john      |   35 | 100233023     |       2 |  10000 |
|      2     | Linda     |   30 | 100234565     |       1 |  15000 |
|      3     | Max       |   40 | 122222344     |       3 |  22000 |
|      4     | Will      |   40 |  12323424     |       3 |  31000 |
|      5     | Michal    |   45 |  12323434     |       3 |   5000 |
+-------------------+-------------------+----------+------------------------+-------------+-------------+

DEPTS:

+------------------+----------------------------+
| DEPT_ID     | DEPT_NAME     |
+------------------+----------------------------+
|       1     | Accounts      |
|       2     | HR            |
|       3     | Production    |
+------------------+----------------------------+
 

mysql> CREATE VIEW EMP_DEPT AS

    -> SELECT EMP_ID, EMP_NAME, EMPLOYEE.DEPT_ID, DEPT_NAME, SALARY

    -> FROM EMPLOYEE,DEPTS

    -> WHERE EMPLOYEE.DEPT_ID = DEPTS.DEPT_ID;

Query OK, 0 rows affected (0.05 sec)

You can check the contents of the view using SELECT statement.

mysql> SELECT * FROM EMP_DEPT;
+-----------+-----------------+---------------+--------------------+----------------+
| EMP_ID | EMP_NAME | DEPT_ID     | DEPT_NAME  | SALARY     |
+-----------+-----------------+---------------+--------------------+----------------+
|      2 | Linda    |       1     | Accounts   |  15000     |
|      1 | john     |       2     | HR         |  10000     |
|      3 | Max      |       3     | Production |  22000     |
|      4 | Will     |       3     | Production |  31000     |
|      5 | Michal   |       3     | Production |   5000     |
+-----------+-----------------+---------------+--------------------+----------------+
5 rows in set (0.06 sec)

Insert records to views:

Data can be inserted into a view. This data will be inserted into the tables from where the data was pulled up.

Points to remember:

  1. Data will be inserted into the tables only if all the non NULL columns have been included in the column list of the view.
  2. You cannot insert data into view created by joining multiple tables.

Update Views

When you update a view, the underlying table is updated. But not updates will work like the views which get data from one or more tables and a view with expressions in the SELECT statement.

You cannot update a column in a view unless it corresponds to a column in underlying table. Like for example consider a view created as below:

 

CREATE VIEW vendor_view  AS

SELECT VENDOR_ID, VENDOR_PRICE * 0.01 AS VENDOR_CREDIT

FROM VENDOR_DETAILS;

Here even if you update VENDOR_CREDIT using UPDATE statement, it will not change any table as there is no VENDOR_CREDIT column in base VENDOR_DETAILS table.

You can update the underlying tables only when the view has been created using a single table and no special conditions using sub queries have been used.

Example:

Create a view EMP_VIEW with employees with salary > 20000, Then update the age of  employee “MAX”.

mysql> create vieW emp_view as
    -> select * from employee
    -> where salary > 20000;
Query OK, 0 rows affected (0.22 sec)
mysql> select * from emp_view;
+-------------------+-------------------+----------+------------------------+-------------+-------------+
| EMP_ID     | EMMP_NAME | AGEG | PHONE_NUM     | DEPT_ID | SALARY |
+-------------------+------------------+----------+------------------------+-------------+-------------+
|      3     | Max       |   40 | 122222344     |       3 |  22000 |
|      4     | Will      |   40 |  12323424     |       3 |  31000 |
+-------------------+-------------------+----------+------------------------+-------------+-------------+
mysql> UPDATE EMP_VIEW
    -> SET AGE = 35
    -> WHERE EMP_NAME = "MAX";
Query OK, 1 row affected (0.32 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM EMP_VIEW;
+-------------------+-------------------+----------+------------------------+-------------+-------------+
| EMP_ID     | EMMP_NAME | AGEG | PHONE_NUM     | DEPT_ID | SALARY |
+-------------------+-------------------+----------+------------------------+-------------+-------------+
|      3     | Max       |   35 | 122222344     |       3 |  22000 |
|      4     | Will      |   40 |  12323424     |       3 |  31000 |
+-------------------+-------------------+----------+------------------------+-------------+-------------+

Now let us see if the change has been done to the underlying base table that is EMPLOYEE table.

mysql> SELECT * FROM EMPLOYEE;
+-------------------+-------------------+----------+------------------------+-------------+-------------+
| EMP_ID     | EMMP_NAME | AGEG | PHONE_NUM     | DEPT_ID | SALARY |
+-------------------+-------------------+----------+------------------------+-------------+-------------+
|      1     | john      |   35 | 100233023     |       2 |  10000 |
|      2     | Linda     |   30 | 100234565     |       1 |  15000 |
|      3     | Max       |   35 | 122222344     |       3 |  22000 |
|      4     | Will      |   40 |  12323424     |       3 |  31000 |
|      5     | Michal    |   45 |  12323434     |       3 |   5000 |
+-------------------+-------------------+----------+------------------------+-------------+-------------+

Delete records from views.

You can delete records from a view. Same rules are applicable for DELETE like UPDATE and INSERT. The data has to map to corresponding column in base table. Only then the rows will be deleted from the base table.

Syntax:

     DELETE FROM view_name

     WHERE conditions;

Example:

Delete employee detail of employee with salary = 22000.

mysql> DELETE FROM EMP_VIEW
    -> WHERE SALARY = 22000;
Query OK, 1 row affected (0.18 sec)


mysql> SELECT * FROM EMP_VIEW;
+-------------------+-------------------+----------+------------------------+-------------+-------------+
| EMP_ID     | EMMP_NAME | AGEG | PHONE_NUM     | DEPT_ID | SALARY |
+-------------------+-------------------+----------+------------------------+-------------+-------------+
|      4     | Will      |   40 |  12323424     |       3 |  31000 |
+-------------------+-------------------+----------+------------------------+-------------+-------------+

Now check the base table employee;

mysql> SELECT * FROM EMPLOYEE;
+-------------------+-------------------+----------+------------------------+-------------+-------------+
| EMP_ID     | EMMP_NAME | AGEG | PHONE_NUM     | DEPT_ID | SALARY |
+-------------------+-------------------+----------+------------------------+-------------+-------------+
|      1     | john      |   35 | 100233023     |       2 |  10000 | 
|      2     | Linda     |   30 | 100234565     |       1 |  15000 |
|      4     | Will      |   40 |  12323424     |       3 |  31000 |
|      5     | Michal    |   45 |  12323434     |       3 |   5000 |
+-------------------+-------------------+----------+------------------------+-------------+-------------+

Drop Views

The view can be deleted or dropped using DROP statement.

Syntax:

     DROP VIEW viewname;

Example:

Drop the view EMP_VIEW created.

First check the list of tables and views in the database using SHOW tables command.

mysql> SHOW TABLES;
+-------------------------------------+
| Tables_in_employeedb  |
+-------------------------------------+
| depts                 |
| emp_email             |
| emp_view              |
| employee              |
+-------------------------------------+
4 rows in set (0.02 sec)

Then DROP the view and then again check the tables list.

mysql> DROP VIEW EMP_VIEW;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+-------------------------------------+
| Tables_in_employeedb     |
+-------------------------------------+
| depts                    |
| emp_email                |
| employee                |
+-------------------------------------+
3 rows in set (0.00 sec)

Question:

What will happen to the view EMP_EMAIL which uses table EMPLOYEE, if the underlying base table EMPLOYEE is dropped?

mysql> SELECT * FROM EMP_VIEW;

ERROR 1356 (HY000): View 'employeedb.emp_email' references invalid table(s) or c

olumn(s) or function(s) or definer/invoker of view lack rights to use them

 

 

Like us on Facebook