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).
The benefits of using Views
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.
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.
The CREATE VIEW Statement
The Create View statement creates a new view in a database. The syntax is following:
Create View view_name As Select * from base_table
- 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.
Create a simple View
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;
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;
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;
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;
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;
The REPLACE VIEW keyword
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
- 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 CREATE or REPLACE view keyword
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
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 DROP View keyword
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';
The query returned zero rows thus indicating that the View has been deleted.
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.