VIEW in MySQL

VIEW

Views are stored queries. A view acts as a virtual table. A view consists of rows & columns just like table. the difference between table and view is that view are definitions built on top of other tables (or view).

There are some points to define a View as follows :

• It is used to restrict access to the database.
• Hide data compexity.
• A view is stored as a select statement in the database.
• DML operations on a view like Insert, Update, Delete affects.

There are several rules which SELECT statement has to follows:

• In the SELECT statement Subquery cannot be included.
• Variables such as local, user and session variables cannot be used in the SELECT statement.
•  Prepared statement cannot be used in the view.
• Temporary tables or views cannot be used in the SELECT statements and any tables or views which referred by views must exists.
• View cannot be associated with triggers.

How to create a View

Syntax :

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

We can use the show create View command in the following example :

For Example :

CREATE TABLE Employee(
id int,
first_name VARCHAR(15),
last_name VARCHAR(15),
start_date DATE,
end_date DATE,
salary FLOAT(8,2),
city VARCHAR(10),
description VARCHAR(15)
);

Insert the values in the table :

mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,      


       -> Description) values (1,'Jason',    'Martin',  '19960725',  '20060725', 1234.56, 'Toronto',  'Programmer');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,      
    ->   Description) values(2,'Alison',   'Mathews',  '19760321', '19860221', 6661.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,      
    ->   Description) values(3,'James',    'Smith',    '19781212', '19900315', 6544.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,      
    ->   Description) values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78, 'Vancouver','Manager');
Query OK, 1 row affected (0.02 sec)

mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,      
    ->               values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78, 'Vancouver','Tester');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,     
    ->   Description) values(6,'Linda',    'Green',    '19870730', '19960104', 4322.78,'New York',  'Tester');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,      
    ->   Description) values(7,'David',    'Larry',    '19901231', '19980212', 7897.78,'New York',  'Manager');
Query OK, 1 row affected (0.00 sec)

mysql> insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,      
    ->   Description) values(8,'James',    'Cat',     '19960917',  '20020415', 1232.78,'Vancouver', 'Tester');
Query OK, 1 row affected (0.00 sec)

select * from Employee;

+------+------------+-----------+------------+------------+---------+-----------+-------------+
 | id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
 +------+------------+-----------+------------+------------+---------+-----------+-------------+
 |    1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto   | Programmer  |
 |    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester      |
 |    3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester      |
 |    4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager     |
 |    5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester      |
 |    6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4322.78 | New York  | Tester      |
 |    7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7897.78 | New York  | Manager     |
 |    8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester      |
 +------+------------+-----------+------------+------------+---------+-----------+-------------+

 

Create a view in this command :

CREATE OR REPLACE VIEW myView AS
    SELECT id, first_name, city FROM employee
    WHERE id = 3 WITH LOCAL CHECK OPTION;

View table:-  SELECT * FROM myView;

mysql> CREATE OR REPLACE VIEW myView1 AS
    -> SELECT id, first_name FROM myView;
Query OK, 0 rows affected (0.01 sec)

View table:-  select * from myView1;

mysql> SHOW CREATE VIEW myView\G




How to drop a View :

mysql> drop view myView1;
Query OK, 0 rows affected (0.00 sec)