MySQL Create View, Replace View and Drop View Statements with Examples

An SQL view is a virtual table or a result-set generated by the SELECT query. Unlike physical tables, views do not store data in a database. When we run the SELECT query to populate the data, it executes a query that creates the view (View definition).

What is View in MySQL

Suppose you have a complex SQL query using that uses multiple joins and complex business logic. You use this query in the application code frequently. However, due to business requirement, you must change that query logic. The changes made in a query must reflect in the entire application.

In such cases, it is preferable to create an SQL view and encapsulate the business logic within it. Moreover, when we want to change the query definition, we can change the view’s definition instead of making changes in the application.

The views also apply to hide the actual name and details of the table. If we store any customer’s sensitive information in the table and want to show only the primary details, we can create a view.

This article will explain the Create View, Replace View and Drop View statements usage in the MySQL server.

Sample Database Setup

For the demonstration, I have installed MySQL Server and created a dummy database named EltechDB. In that database, I have created a table named tblemployee.

The following query creates the EltechDB database:

Create database EltechDB;

The following query creates a tblemployee table:

CREATE TABLE tblemployees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

I have inserted some dummy records into the tblemployee table using the following query:

INSERT INTO `EltechDB`.`tblemployees` 
VALUES 
(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'),
(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'),
(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10'),
(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'),
(10009,'1952-04-19','Sumant','Peac','F','1985-02-18'),
(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');

Now, let us clarify the statements to manage the MySQL views.

How to Сreate View in MySQL

The Create View statement creates a new view in a database. The syntax is following:

Create View view_name
As
Select * from base_table

Here:

• view_name is the desired name of the View.
• The select * from is the query that generates the View using base_table. The query is specified after the AS keyword.

MySQL Create View Syntax Example

Suppose we want to create a view that populates the list of employees. The definition of the View is following:

Create view vwEmployees
as
select * from tblemployees;

To access the data of the View, we can use the SELECT query. To view the data of vwEmployees, run the following query:

mysql> select * from vwEmployees;

Output:

Filter the Data of the View Using the WHERE Clause

We can filter the View output using the WHERE clause. In our case, we want to retrieve the list of female employees from the vwEmployees. The query is as follows:

mysql> select * from vwEmployees where gender='F';

The Query output is:

Let’s take another example. We want to retrieve the list of the employees whose employee number is less than 10005. The query is as follows:

mysql> select * from vwemployees where emp_no<10005;

Query Output:

Use the Aggregate Function on the View

We can use the aggregate functions on the View.

Here we want to populate the count of the male and female employees from the vwEmployees View. First, we use the COUNT function to get the count of employees. Then, we use the GROUP BY clause to group the output based on the gender column. The query is as follows:

mysql> select gender, count(emp_no)as 'Total Employees' from vwEmployees group by gender;

Query Output:

Use the Date-Time Function on the View

We can use the date-time function on the View output. Let’s retrieve the list of the employees whose joining year is greater or equal to 1990. The query is as follows:

mysql> select * from vwemployees where Year(hire_date)>=1990;

Query output:

Use the Control-Flow Function on the View

We can use the CASE or IF function on the output of the View.

We print the value of the gender in some meaningful format. The output of the gender column returned by the View is either M or F. Instead of printing M, the query must return Male. Similarly, instead of the printing F, the query must return Female. The query is as follows:

mysql> Select first_name, last_name, case when gender='M' then 'Male' when gender ='F' then 'Female' end as 'Gender', hire_date from vwEmployees;

Query output:

Read more how to create view in SQL.

Replace View Statement

The REPLACE VIEW is used to change the definition of a view. The CREATE VIEW and REPLACE VIEW keywords are used together. The syntax is following:

Create or replace View view_name
As
Select * from base_table

Here:

• Create or replace View: These keywords serve to create or replace the existing View. When we run the create or the replace view statement, MySQL checks whether it exists in the database. If the View exists, it changes the View definition using the query specified after the AS keyword. If the View does not exist, it creates a new view using the query specified after the AS keyword.
• view_name is the desired name of the View.
• The select * from is the query that is used to generate the View using base_table. The query is specified after the AS keyword.

Note: Create or Replace view keywords create a new view or change the existing View definition.

Write the View name in the ‘database_name’.’view_name’ format. If you do not specify the database, you might end up creating the View in a different database.

Example of MySQL CREATE View or MySQL REPLACE View Statements

We want to show only the first name, last name, and gender of employees. Also, we want to provide appropriate names to the columns. For example, the first_name and last_name column values should be combined, and the column name must be the employee’s name. The SQL query should be as follows:

mysql> create or replace view vwEmployees(EmployeeName,gender) as select concat(first_name, " ",last_name),gender from tblEmployees; 

Run the following SELECT query to view the data from vwEmployees:

mysql> select * from vwEmployees

Query output:

As you can see, the first_name and last_name values are combined in the EmployeeName column indicating that the View definition has been updated.

The MySQL DROP View Statement

The DROP View keyword serves to drop the View from the database. The syntax is following:

Drop View ‘database_name’.’view_name’

In the syntax, view_name is the name of the View that you want to drop.

Let us drop the vwEmployees View using the DROP View statement. Execute the following query:

mysql> DROP VIEW `eltechdb`.`vwemployees`;

To verify that the View has been dropped, run the following query:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE TABLE_TYPE LIKE 'VIEW' and table_schema='EltechDB';

Output:

The query returned zero rows thus indicating that the View has been deleted.

Summary

This article explained and demonstrated the MySQL views and the ways of creating and managing those views. It also clarified the usage of the CREATE VIEW, REPLACE View, and DROP View statements. CREATE View is used to create a view, REPLACE View changes the View’s definition, and DROP View deletes the View.

Tags: mysql, mysql view Last modified: March 30, 2023