MySQL – Create Views

MySQL - Create Views

Using MySQL, a view is a virtual table that enables us to access the data in a table without preventing other programs from accessing it, thus preventing deadlocks.

The views are created on top of one or more tables that contain data. We are using MySQL version 8.0 for this exercise.

Syntax:

CREATE [REPLACE] VIEW view_name AS SELECT col1, col2, ... 
FROM table_name WHERE condition;

The syntax begins with the keyword “CREATE VIEW”, which informs the MySQL Server of the type of operation to be performed. It is a mandatory keyword that cannot be omitted.
There is an optional parameter called REPLACE. In order to work on an existing view, we can use this tool.
The next step is to identify the view by its name. It should be unique, just as table and column names are.
Then, select the columns in the table. It is possible for a view to contain all of the columns in the underlying table or only a limited number of columns.
The next step is to identify the table on which the view will be run. This is a mandatory requirement that cannot be omitted.
Finally, there is the WHERE condition, which determines which rows in the target table should be included in the CREATE VIEW action. There is also a keyword called WHERE, but it is not required.

Example

Below is an example of how to create a view that displays all employees with salaries greater than 10,000 in the “employees” table:

CREATE VIEW high_salary_employees AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 10000;

The SELECT clause can be with specific column names or we can use “*” to get all the columns. Once a view has been created, columns cannot be added or deleted. To add or delete columns, we must either create a new view or replace the existing view.

According to the output statement, the CREATE VIEW SQL statements have been successfully executed. It also states that no rows have been affected. The data in the underlying table is not affected when a new row is created.

SELECT * FROM high_salary_employees;;