PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
what is mysql 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;