A view is a virtual table. This chapter shows how to create, update, and delete a view.
CREATE VIEW
In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.
UPDATE VIEW
You can update a view by using the following syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
DELETE VIEW
You can delete a view with the DROP VIEW command:
DROP VIEW view_name
MY EXERCISE
Create Table BARANG:
mysql> CREATE table barang (
-> kd_brg varchar(3) not null primary key,
-> nama_barang varchar(50),
-> harga_barang int)
-> engine=INNODB;
Query OK, 0 rows affected (0.09 sec)
Create Table JUAL:
mysql> create table jual (
-> tgl date,
-> kode varchar(3),
-> jml_jual int)
-> engine=INNODB;
Query OK, 0 rows affected (0.02 sec)
Insert Value Into BARANG:
mysql> insert into barang values
-> ('b01','Sepatu','50000'),
-> ('b02','Sandal','10000'),
-> ('b03','Handuk','25000'),
-> ('b04','Ember','5000'),
-> ('b05','Celana','20000');
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
Insert Value Into JUAL:
mysql> insert into jual values
-> ('09-08-23','b01','5'),
-> ('09-10-21','b02','10'),
-> ('09-11-14','b03','3'),
-> ('09-12-01','b04','7'),
-> ('09-12-01','b05','20');
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
Create Table View VIEW_JUAL:
mysql> CREATE VIEW VIEW_JUALAN as
-> SELECT
-> DATE_FORMAT(b.tgl,'%d %M %Y') as tgl,
-> b.kode,
-> a.nama_barang,
-> a.harga_barang,
-> b.jml_jual,
-> (a.harga_barang*b.jml_jual) as total
-> FROM jual b, barang a
-> WHERE b.kode=a.kd_brg;
Query OK, 0 rows affected (0.05 sec)
Select It :)
mysql> SELECT * FROM VIEW_JUALAN;
+------------------+------+-------------+--------------+----------+--------+
| tgl | kode | nama_barang | harga_barang | jml_jual | total |
+------------------+------+-------------+--------------+----------+--------+
| 23 August 2009 | b01 | Sepatu | 50000 | 5 | 250000 |
| 21 October 2009 | b02 | Sandal | 10000 | 10 | 100000 |
| 14 November 2009 | b03 | Handuk | 25000 | 3 | 75000 |
| 01 December 2009 | b04 | Ember | 5000 | 7 | 35000 |
| 01 December 2009 | b05 | Celana | 20000 | 20 | 400000 |
+------------------+------+-------------+--------------+----------+--------+
5 rows in set (0.00 sec)
No Comment.
Add Your Comment