SQL Create View (CREATE VIEW statement)

In this tutorial, you will learn how to create, update, and delete views using SQL.

Create views to simplify table access

A view is a virtual table whose definition is stored in the database. However, unlike tables, views do not actually contain any data. Instead, they provide a way to store commonly used complex queries in the database. But you can useSQL SELECT statementTo access view data, just like using a regular table or base table.

Views can also be used as a security mechanism by allowing users to access data through views instead of directly granting access to the entire base table.

Syntax

Use the CREATE VIEW statement to create a view.

CREATE VIEW view_name AS select_statement;

To clearly understand this, let's take a look at the followingemployeesanddepartmentsTable.

+--------+--------------+--------+---------+
| emp_id | emp_name | salary | dept_id |
+--------+--------------+--------+---------+
|　　　　　　1　|　Ethan　Hunt　　　|　　　5000 |　　　　　　　4　|
|　　　　　　2　|　Tony　Montana　|　　　6500 |　　　　　　　1　|
|　　　　　　3　|　Sarah　Connor　|　　　8000 |　　　　　　　5　|
|　　　　　　4　|　Rick　Deckard　|　　　7200 |　　　　　　　3　|
|　　　　　　5　|　Martin　Blank　|　　　5600 | NULL |
+--------+--------------+--------+---------+

+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
|　　　　　　　1　| Administration |
|　　　　　　　2　| Customer Service |
|　　　　　　　3　| Finance |
|　　　　　　　4　| Human Resources |
|　　　　　　　5　| Sales |
+---------+------------------+

Suppose you want to retrieve the employee ID and name as well as their department name, then you need to executeLeft joinOperation, as shown below:

SELECT　t1.emp_id,　t1.emp_name,　t2.dept_name
FROM　employees　AS　t1　LEFT　JOIN　departments　AS　t2
ON　t1.dept_id　=　t2.dept_id;

Once the above query is executed, you will get the following output:

+--------+--------------+-----------------+
| emp_id | emp_name | dept_name |
+--------+--------------+-----------------+
|　　　　　　1　| Ethan Hunt | Human Resources |
|　　　　　　2　| Tony Montana | Administration |
|　　　　　　3　| Sarah Connor | Sales |
|　　　　　　4　| Rick Deckard | Finance |
|　　　　　　5　| Martin Blank | NULL |
+--------+--------------+-----------------+

However, whenever you need to access this record, you need to enter the entire query again. If you frequently perform such operations, it will become very inconvenient and annoying.

In this case, you can create a view to make the query results easier to access, as shown below:

CREATE VIEW emp_dept_view AS
SELECT　t1.emp_id,　t1.emp_name,　t2.dept_name
FROM　employees　AS　t1　LEFT　JOIN　departments　AS　t2
ON　t1.dept_id　=　t2.dept_id;

Now, you can use the view emp_dept_view to access the same records as follows:

SELECT　*　FROM emp_dept_view;

As you can see, you can save a lot of time and effort on views.

Replace the existing view

In MySQL, if you need to update or replace an existing view, you can delete the view and create a new one, or simply use the OR REPLACE clause in the CREATE VIEW statement, as shown below:

CREATE OR REPLACE VIEW view_name AS select_statement;

The following SQL statements will replace or change the existing viewemp_dept_viewThe definition of the view, which is done by adding a new column salary.

--　The syntax of MySQL database
CREATE　OR　REPLACE　VIEW　emp_dept_view　AS
SELECT　t1.emp_id,　t1.emp_name,　t1.salary,　t2.dept_name
FROM　employees　AS　t1　LEFT　JOIN　departments　AS　t2
ON　t1.dept_id　=　t2.dept_id;

After updating the view, if you execute the following statement:

SELECT　*　FROM　emp_dept_view　ORDER　BY　emp_id;

You will see another column in the result outputsalaryas shown below:

+--------+--------------+--------+-----------------+
|　emp_id　|　emp_name　　　　　|　salary　|　dept_name　　　　　　　|
+--------+--------------+--------+-----------------+
|　　　　　　1　|　Ethan　Hunt　　　|　　　5000　|　Human　Resources　|
|　　　　　　2　|　Tony　Montana　|　　　6500　|　Administration　　|
|　　　　　　3　|　Sarah　Connor　|　　　8000　|　Sales　　　　　　　　　　　|
|　　　　　　4　|　Rick　Deckard　|　　　7200　|　Finance　　　　　　　　　|
|　　　　　　5　|　Martin　Blank　|　　　5600　|　NULL　　　　　　　　　　　　|
+--------+--------------+--------+-----------------+

for updating data through the view

In theory, exceptSELECTIn addition to statements, you can also performINSERT,UPDATEandDELETEHowever, not all views are updatable, meaning they can modify the data in the underlying source table. There are some limitations on upgradability.

Generally, if the view contains any of the following, it is not updatable:

• DISTINCT, GROUP BY, or HAVING clauses.

• Aggregation functions, such as AVG(), COUNT(), SUM(), MIN(), MAX(), and so on.

• The UNION, UNION ALL, CROSS JOIN, EXCEPT, or INTERSECT operators.

• The subquery in the WHERE clause refers to the table in the FROM clause.

If a view meets these conditions, it can be used to modify the source table.

The following statement will update the salary of the employee with emp_id equal to1The salary of the employee.

UPDATE emp_dept_view SET salary = '6000'　
WHERE emp_id =　1;

Delete View

Similarly, if a view is no longer needed, it can be deleted from the database using the DROP VIEW statement, as shown in the following syntax:

DROP VIEW view_name;

The following command will delete the view from the databaseemp_dept_view.

DROP VIEW emp_dept_view;