Views in SQL are the virtual tables. They have rows and columns like they are present in the normal database tables. But it fetches selective portion of the data from one or more tables. A view is a named query stored in the database catalog.
To create a new view you use the CREATE VIEW statement. Syntax for creating view is
CREATE VIEW view_name AS SELECT column_list FROM table_name [WHERE condition];
Above statement creates a view having name view_name. A view belongs to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, use db_name.view_name syntax to qualify the view name with the database name.
Now, you can reference the view as a table in SQL statements. For example, to query data from the view_name view use the SELECT statement.
SELECT * FROM view_name;
MySQL executes the underlying query specified in the view’s definition when used with SELECT statement. A view can retrieves data from one or more tables. This picture illustrates a view based on columns of multiple tables:
CREATE VIEW MarksView AS SELECT table1_name.NAME, table1_name.ADDRESS, table2_name.MARKS FROM table1_name, table2_name WHERE table1_name.NAME = table2_name.NAME;
Below example demonstrate how to creating view. A view
cityView is created from employee info table. Later the created view is used with SELECT statement to retrieve record from the table.
# Create Table CREATE TABLE employleinfo(id int, firstName varchar(50), lastName varchar(50), age int, city varchar(50)); # Insert record into the table INSERT into employleinfo values(1, "Rahul", "Bhagwat",20,"Noida"); INSERT into employleinfo values(2, "Stephen", "Fleming", 17, "Delhi"); INSERT into employleinfo values(3, "Sebastian", "Smith", 19, "Patna"); INSERT into employleinfo values(4, "Anjali", "Sharma", 50, "Mumbai"); INSERT into employleinfo values(5, "Erica", "Edwards", 40, "Chennai"); INSERT into employleinfo values(6, "Priya", "Gowda", 50, "Pune"); INSERT into employleinfo values(7, "Shekar", "Chandra", 43, "Delhi"); INSERT into employleinfo values(8, "Shekar", "Sharma", 19, "Patna"); # Create view CREATE VIEW cityView AS SELECT firstName, lastName, city FROM employleinfo; # Query from view SELECT * FROM cityView; # Output: # Rahul|Bhagwat|Noida # Stephen|Fleming|Delhi # Sebastian|Smith|Patna # Anjali|Sharma|Mumbai # Erica|Edwards|Chennai # Priya|Gowda|Pune # Shekar|Chandra|Delhi # Shekar|Sharma|Patna