Examples of MySQL CREATE VIEW Statement
Example 1: CREATE VIEW without using the WHERE clause
Let’s CREATE a VIEW without using the WHERE clause
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, . . . . column_n
FROM table_name
WHERE condition1, condition2, . . . . , condition_n;
Let’s CREATE a VIEW from EMPLOYEE table where the view should have only EMP_ID and SALARY of Employees.
Query:
CREATE VIEW view1 AS
SELECT EMP_ID, SALARY
FROM EMPLOYEE;
Output:
Explanation: Here we are creating a VIEW named view1 from the EMPLOYEE table which will have EMP_ID and SALARY as columns. The VIEW will have all the rows present as we have not specified the WHERE condition. As there are 10 rows in the EMPLOYEE table the VIEW will also have 10 rows present in it.
Example 2: CREATE VIEW using WHERE Clause
Let’s CREATE a VIEW using WHERE clause
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, . . . . column_n
FROM table_name
WHERE condition1, condition2, . . . . , condition_n;
Let’s CREATE a VIEW from EMPLOYEE table where the VIEW should have only EMP_ID, AGE, and SALARY of Employees whose SALARY=14000
Query:
CREATE VIEW view2 AS
SELECT EMP_ID, AGE, SALARY
FROM EMPLOYEE
WHERE SALARY=14000;
Output:
Explanation: Here we are creating a VIEW named view2 from the EMPLOYEE table which will have EMP_ID, AGE, and SALARY of Employees whose SALARY=14000. The VIEW will have only 3 rows present as there are only 3 Employees with SALARY=14000.
Example 3: CREATE VIEW without using WHERE clause having columns of 2 TABLES
Let’s CREATE a VIEW without using WHERE clause having columns of 2 TABLES
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, . . . . column_n
FROM table_name
WHERE condition1, condition2, . . . . , condition_n;
Let’s CREATE a VIEW that will have EMP_ID and NAME from EMPLOYEE table and PHONE and CITY from the EMPLOYEE1 table without using WHERE clause.
Query:
CREATE VIEW view3 AS
SELECT a.EMP_ID, a.NAME, b.PHONE, b.CITY
FROM EMPLOYEE a
JOIN EMPLOYEE1 b ON a.EMP_ID=b.EMP_ID;
Output:
Explanation: Here we are creating a VIEW named view3 which is having EMP_ID and NAME from the EMPLOYEE table and PHONE and CITY from the EMPLOYEE1 table. Here it will have only those rows which match EMP_ID from EMPLOYEE table and EMP_ID from the EMPLOYEE1 table. As there are 10 rows from both the tables which have EMP_ID same Let’sthe view3 will have 10 rows present in it.
Example 4: CREATE VIEW using WHERE clause having columns of 2 TABLES
Let’s CREATE a VIEW using the SyntaxWHERE clause having columns of 2 TABLES
SyntaxLet’s:
CREATE VIEW view_name AS
SELECT column1, column2, . . . . column_n
FROM table_name
WHERE condition1, condition2, . . . . , condition_n;
Let’s CREATE a VIEW which will have EMP_ID and NAME from EMPLOYEE table and PHONE and CITY from EMPLOYEE1 table WHERE the CITY of the Employee is ‘Pune‘.
Query:
CREATE VIEW view4 AS
SELECT a.EMP_ID, a.NAME, b.PHONE, b.CITY
FROM EMPLOYEE a
JOIN EMPLOYEE1 b ON a.EMP_ID=b.EMP_ID
WHERE b.CITY='Pune';
Output:
Explanation: Here we are creating a VIEW named view4 which has EMP_ID and NAME from the EMPLOYEE table and PHONE and CITY from the EMPLOYEE1 table WHERE the CITY of the Employee is ‘Pune‘. Here it will have only those rows which match EMP_ID from EMPLOYEE table and EMP_ID from EMPLOYEE1 table. Then once the EMP_ID is matched it will check for the Employees whose CITY = ‘Pune‘. As there are 10 rows from both the tables which have EMP_ID same but there are only 4 Employees whose CITY= ‘Pune‘. So the view will have 4 rows present in it.
MySQL CREATE VIEW Statement
MySQL is a popular open-source relational database management system (RDBMS) that is usually used for developing scalable and high-performance databases. MySQL was developed by MySQL AB (currently owned by Oracle Corporation) in 1995.
MySQL is known for its robust, easy, and reliable features with quick processing speeds. MySQL is generally used by dynamic web applications and is commonly used by languages such as PHP, Python, and other server-side programming languages.
In this article, you will learn about how to CREATE a VIEW in MySQL. You will learn how the CREATE VIEW Statement works along with some examples.
Contact Us