View MySql

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

Contoh Implemetasi View:
1. Membuat Tabel barang:

mysql> create table barang

   -> (kode_barang char(3) not null primary key default'0',

   -> nama_barang char(30),

   -> harga_barang int);

Query OK, 0 rows affected (0.05 sec)

mysql> insert into barang

    -> value

    -> ('001', 'mobil',5000000);

Query OK, 1 row affected (0.01 sec)

2. Membuat Tabel Jual

mysql> create table jual

    -> (tgl date,

    -> kode char(3), jml_jual int);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into jual

    -> value

    -> ('2007/01/01','001',3);

Query OK, 1 row affected (0.03 sec)

3. Membuat View

mysql> create view jualan_view as select date_format(b.tgl,'%d%M%Y')as tgl, b.ko

de, a.nama_barang, a.harga_barang, b.jml_jual,(a.harga_barang*b.jml_jual) as tot

al from jual b, barang a where b.kode=a.kode_barang;

Query OK, 0 rows affected (0.00 sec)

mysql> select* from jualan_view;

+---------------+------+-------------+--------------+----------+----------+

| tgl           | kode | nama_barang | harga_barang | jml_jual | total    |

+---------------+------+-------------+--------------+----------+----------+

| 01January2007 | 001  | mobil       |      5000000 |        3 | 15000000 |

+---------------+------+-------------+--------------+----------+----------+

1 row in set (0.00 sec)

mysql> select* from jual;

+------------+------+----------+

| tgl        | kode | jml_jual |

+------------+------+----------+

| 2007-01-01 | 001  |        3 |

+------------+------+----------+

1 row in set (0.00 sec)

mysql> select* from jualan_view;