Month: March 2019

Mysql – Views

Posted on March 21, 2019 in Feature Questions

Mysql database view is known as a “virtual table”. virtual table created by a query by joining one or more tables. By Virtual, we mean, the tables do not store any data of their own but display data of other tables. In other simple words, VIEWS are nothing but SELECT Queries.

Create View Syntax:

CREATE VIEW[OR REPLACE] my_view_name AS
SELECT columnname1, columnname2, ...
FROM table_name
WHERE condition;

Example

Sample example of create and call view 
Ex : CREATE VIEW employee_view AS
SELECT EmployeeID, LastName, FirstName
FROM Tbl_Employee
WHERE Gender = "Female";
Try It Now

Views are nothing but SELECT Queries.

CREATE VIEW employee_view AS
SELECT EmployeeID, LastName, FirstName
FROM Tbl_Employee
WHERE Gender = "Female";

SAME AS

SELECT * FROM (
 SELECT EmployeeID, LastName, FirstName
 FROM Tbl_Employee
 WHERE Gender = "Female"
) x;

Drop view using-

DROP VIEW IF EXISTS <view_name>;

How to display the contents of a view?

SHOW CREATE TRIGGER <trigger_name>; 
Ex : SHOW CREATE TRIGGER after_insert_trigger; 
Try It Now

Show list of views in Database-

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
Try It Now

Advantages of views:

  • View the data without storing the data into the object.
  • You can restrict users to the view instead of the underlying table(s), thereby enhancing security.
  • Join two or more tables and show it as one object to user.
  • Restict the access of a table so that nobody can insert the rows into the table.
  • Simply If we are having complex queries with joins etc you can make and put in view and use that view where need of reusing it.
Tags: ,