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, 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, 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, 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, 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, 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, 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, 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, row affected (0.00 
sec)


select * from Employee;

+------+------------+-----------+------------+------------+---------+-----------+-------------+
 | id   | first_name | last_name | start_date | end_date   | salary  | city      | description |
 +------+------------+-----------+------------+------------+---------+-----------+-------------+
 |    | Jason      | Martin    | 1996-07-25 2006-07-25 1234.56 | Toronto   | Programmer  |
 |    | Alison     | Mathews   | 1976-03-21 1986-02-21 6661.78 | Vancouver | Tester      |
 |    | James      | Smith     | 1978-12-12 1990-03-15 6544.78 | Vancouver | Tester      |
 |    | Celia      | Rice      | 1982-10-24 1999-04-21 2344.78 | Vancouver | Manager     |
 |    | Robert     | Black     | 1984-01-15 1998-08-08 2334.78 | Vancouver | Tester      |
 |    | Linda      | Green     | 1987-07-30 1996-01-04 4322.78 | New York  | Tester      |
 |    | David      | Larry     | 1990-12-31 1998-02-12 7897.78 | New York  | Manager     |
 |    | 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 = 
WITH LOCAL CHECK OPTION;

View table:-  SELECT FROM myView;

create-view.gif

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

View table:-  select from myView1;

replace.gif

mysql> SHOW CREATE VIEW myView\G

show.gif

How to drop a View :

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

Up Next
    Ebook Download
    View all
    Learn
    View all