Home Mysql Mysql5 Views
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Views

Advertisement
VIEW is a virtual table, which acts like a table but actually it contains no data. That is based on the result set of a SELECT statement. A VIEW consists rows and columns from one or more than one tables.

Views

     

VIEW is a virtual table, which acts like a table but actually it contains no data. That is based on the result set of a SELECT statement. A VIEW consists rows and columns from one or more than one tables. A VIEW is a query that?s stored as an object. A VIEW is nothing more than a way to select a subset of table?s columns.
When you defined a view then you can reference it like any other table in a database. A VIEW provides as a security mechanism also. VIEWS ensures that users are able to modify and retrieve only that data which seen by them. 

By using Views you can ensure about the security of data by restricting access to the following data:

  • Specific columns of the tables. 
  • Specific rows of the tables. 
  • Specific rows and columns of the tables. 
  • Subsets of another view or a subset of views and tables
  • Rows fetched by using joins. 
  • Statistical summary of data in a given tables. 

CREATE VIEW Statement
 

CREATE VIEW Statement is used to create a new database view. The general syntax of CREATE VIEW Statement is:
  CREATE VIEW view_name [(column_list)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION]

View_name specifies the name for the new view. column_list specifies the name of the columns to be used in view. column_list must have the same number of columns that specified in select_statement. If column_list option is not available then view is created with the same columns that specified in select_statement.
WITH ENCRYPTION option encrypts the text to the view in the syscomments table.
AS option specifies the action that is performed by the view. select_statement is used to specify the SELECT statement that defines a view. The optional WITH CHECK OPTION clause applies to the data modification statement like INSERT and UPDATE statements to fulfill the criteria given in the select_statement defining the view. This option also ensures that the data can visible after the modifications are made permanent.

Some restrictions imposed on views are given below :

  • A view can be created only in the current database. 
  • The view name must follow the rules for identifiers and 
  • The view name must not be the same as that of the base table
  • A view can be created only that time if there is a SELECT permission on its base table. 
  • A SELECT INTO statement cannot be used in view declaration statement. 
  • A trigger or an index cannot be defined on a view. 
  • The CREATE VIEW statement cannot be combined with other SQL statements in a single batch. 

Example :
In the following example we have two table Client and Products. And if you want to see only those client records that are active in Products table also means right now they are supplying us the products. For this we are creating the view by the name of Supp_Client.

mysql> SELECT * FROM Client;
+------+---------------+----------+
| C_ID | Name          | City     |
+------+---------------+----------+
| 1    | A K Ltd       | Delhi    |
| 2    | V K Associate | Mumbai   |
| 3    | R K India     | Banglore |
| 4    | R S P Ltd     | Kolkata  |
| 5    | A T Ltd       | Delhi    |
| 6    | D T Info      | Delhi    |
+------+---------------+----------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM Products;
+---------+-------------+------+
| Prod_ID | Prod_Detail | C_ID |
+---------+-------------+------+
| 111     | Monitor     | 1    |
| 112     | Processor   | 2    |
| 113     | Keyboard    | 2    |
| 114     | Mouse       | 3    |
| 115     | CPU         | 5    |
+---------+-------------+------+
5 rows in set (0.00 sec)
 
Example : Create View Statement
 
mysql> CREATE VIEW Supp_Client AS
    -> SELECT * FROM Client
    -> WHERE C_ID IN (
    -> SELECT C_ID FROM Products)
    -> WITH CHECK OPTION;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM Supp_Client;
+------+---------------+----------+
| C_ID | Name          | City     |
+------+---------------+----------+
| 1    | A K Ltd       | Delhi    |
| 2    | V K Associate | Mumbai   |
| 3    | R K India     | Banglore |
| 5    | A T Ltd       | Delhi    |
+------+---------------+----------+
4 rows in set (0.03 sec)

In the following example we include the WHERE clause with the select statement of view. Then MySQL adds this condition to the VIEW definition when executing the statement for further restricting the result. Example :

mysql> SELECT * FROM Supp_Client WHERE City='Delhi';
+------+---------+-------+
| C_ID | Name    | City  |
+------+---------+-------+
| 1    | A K Ltd | Delhi |
| 5    | A T Ltd | Delhi |
+------+---------+-------+
2 rows in set (0.04 sec)

ALTER VIEW Statement

By the ALTER VIEW Statement we can change the definition of a view. This statement is useful to modify a view without dropping it. ALTER VIEW statement syntax is similar to CREATE VIEW Statement and effect is same as the CREATE OR REPLACE VIEW. The general syntax of ALTER VIEW Statement is :
    ALTER VIEW view_name [(column_list)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION]

In the following example we are altering the view definition that we have created above. In this we add one more column by the name of Prod_Detail of Products table. Example of Altering the View Statement :

mysql> ALTER VIEW Supp_Client AS
    -> SELECT Client.C_ID, Client.Name, Client.City,
    -> Products.Prod_Detail from Client, Products
    -> WHERE Client.C_ID=Products.C_ID;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM Supp_Client;
+------+---------------+----------+-------------+
| C_ID | Name          | City     | Prod_Detail |
+------+---------------+----------+-------------+
| 1    | A K Ltd       | Delhi    | Monitor     |
| 2    | V K Associate | Mumbai   | Processor   |
| 2    | V K Associate | Mumbai   | Keyboard    |
| 3    | R K India     | Banglore | Mouse       |
| 5    | A T Ltd       | Delhi    | CPU         |
+------+---------------+----------+-------------+
5 rows in set (0.02 sec)

DROP VIEW Statement

For dropping a view you can use the DROP VIEW Statement. When view is dropped but it has no effect on the underlying tables. After dropping a view if you issue any query that reference a dropped view then you get an error message. But dropping a table that reference any view does not drop the view automatically you have to dropt the view explicitly. The general syntax of DROP VIEW Statement is :
  DROP VIEW view_name;

In the following example we are dropping the view that we have created above. Example of Dropping the View Statement :

mysql> DROP VIEW Supp_Client;
Query OK, 0 rows affected (0.00 sec)
  
mysql> SELECT * FROM Supp_Client;
ERROR 1146 (42S02): Table 'employee.supp_client' doesn't exist
Advertisement

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: March 13, 2008

Ask Questions?    Discuss: Views  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
Amrita
June 22, 2011
mysql

nice tutorial to understand datas easily
sugir
January 25, 2012
i want the full commends

usefull
anu
February 6, 2012
nice

nice
Charismatick!
February 6, 2012
About Website

Really a Nice website...Very Useful for Database Learners
heena chodha
February 15, 2012
how alter views in oracle

what is the right method of alter view
A.SUTHAKAR
March 16, 2012
Textile Store Maintenance

Textile Store Maintenance. opening Stock Materials Receiveing Materials Issues Closing Stock. for Packing Materials Spares of all Departments Lubricatione Electrical Items Generals. Thanking You.
DMCA.com