what is Views?

VIEWS are virtual tables that do not store any data of its own. but display data stored in other tables. In other words, VIEWS are nothing but some SQL Queries. A view can contain all or a few rows from a table. A MySQL view can show data from one table or many tables.

In MySQL, the View is a virtual table created by a query by joining one or more tables.

the main difference between views and Tabel that the views are definitions built on top of other tables. If any changes occur in the main table, the same changes reflected in the View table also.

Syntax :

CREATE [OR REPLACE] VIEW view_name AS SELECT columns_name FROM tables WHERE conditions;

view_name: It specifies the name of the VIEW that you want to create in MySQL database.

OR REPLACE: It is optional. It is used when a VIEW already exists. If you do not specify this clause and the VIEW already exists, the CREATE VIEW statement will return an error.

How to create views in mysql?

CREATE VIEW trainer AS SELECT course_name, course_fee FROM ittutorial;

How to show created view

SELECT * FROM trainer; // here trainer is the view

Note :It is essential to know that a view does not store the data physically. When we execute the SELECT statement for the view, MySQL uses the query specified in the view's definition and produces the output.

View Example :-

CREATE TABLE products (product_title varchar(32),qty INT(11), price INT(11));
INSERT INTO products VALUES('car',5, 50000), ('mabile',10, 25000);

CREATE VIEW product_view AS SELECT qty, price, qty*price AS cost FROM products;

select * from product_view;

SELECT * FROM product_view WHERE qty = 10;

Example

CREATE TABLE `products` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`products_title` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
`qty` INT(10) NULL DEFAULT NULL,
`price` DOUBLE NULL DEFAULT NULL,
`size` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
PRIMARY KEY (`id`) USING BTREE
)

products table:

products view table:

MySQL Update VIEW :

ALTER VIEW statement is used to modify or update the already created VIEW

ALTER VIEW view_name AS SELECT columns FROM table WHERE conditions;

example

ALTER VIEW trainer AS SELECT id, course_name, trainer FROM ittutorial;

Drop VIEW

DROP VIEW [IF EXISTS] view_name;

DROP VIEW trainer;

Create View with JOIN Clause

CREATE VIEW Trainer
AS SELECT c.course_name, c.trainer, t.email
FROM courses c, contact t
WHERE c.id = t.id;

Create View with inner JOIN Clause

CREATE VIEW view_Emp_Department AS
SELECT emp.ID, emp.Name, emp.Salary, CAST(emp.DOB AS Date) AS DOB, emp.Gender, dept.Name AS DepartmentName
FROM Employee emp
INNER JOIN Department dept
ON emp.DepartmentID = dept.ID;

Previous Next

Review & Rating

0.0 / 5

0 Review

5

(0)

4

(0)

3

(0)

2

(0)

1

(0)