SQL Views

Views are visual tables.

This chapter explains how to create, update, and delete views.

SQL CREATE VIEW Statement

In SQL, a view is a visual table based on the result set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view come from real tables in one or more databases.

You can add SQL functions, WHERE, and JOIN statements to a view, and you can present data as if it came from a single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Parameter description:

  • CREATE VIEW:Declares that you want to create a view.
  • view_name:Specifies the name of the view.
  • AS:Specifies the keyword indicating the start of the view definition.
  • SELECT column1, column2, ...:Specifies the columns contained in the view, which can be columns from tables or computed columns.
  • FROM table_name:Specifies the table from which the view gets data.
  • WHERE condition:Optional part, used to specify filter conditions to restrict the rows in the view.

Note:A view always displays the latest data! Whenever a user queries a view, the database engine rebuilds the data using the view's SQL statement.

SQL CREATE VIEW Example

Suppose you have a table called employees that contains employee information, including the following columns: employee_id, first_name, last_name, salary, and department_id. Now, we will create a view that shows information about employees whose salary is higher than a certain threshold.

The example is as follows:

-- 创建包含高工资员工信息的视图
CREATE VIEW high_salary_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 50000;

In this example, we created a view named high_salary_employees, which contains information about employees whose salary is higher than 50000.

Now, you can use this view just like querying a normal table:

-- 查询高工资员工视图
SELECT *
FROM high_salary_employees;

This will return the detailed information of all employees with a salary higher than 50000, without having to write the same filter condition every time.

It is worth noting that a view is essentially a virtual table. It does not store data, but is generated based on the query results of the underlying tables. Therefore, if the data in the underlying tables changes, the content of the view will also be updated accordingly.

SQL Updating Views

In SQL, you cannot directly use the UPDATE statement to update a view, because a view is a virtual table generated based on query results, not a table that actually stores data.

The essence of updating a view is to update the data in the tables on which the view is based, and then the view will reflect these changes.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Here, table_name is the name of the base table, column1, column2, ... are the columns to be updated, value1, value2, ... are the new values, and condition is the update condition.

Now, we want to add a "Category" column to the "Current Product List" view. We will update the view with the following SQL:

For example, if you have a view named high_salary_employees that displays information about employees with a salary higher than 50000, and this view is based on the query results of the employees table, you can update the data by following these steps:

-- 步骤 1: 更新 employees 表中的数据
UPDATE employees
SET salary = 60000
WHERE employee_id = 1001;

-- 步骤 2: 查询更新后的高工资员工视图
SELECT *
FROM high_salary_employees;

In this way, you have updated the data in the employees table, and the view high_salary_employees will reflect these changes.


SQL Dropping Views

In SQL, dropping (or deleting) a view is accomplished by using the DROP VIEW statement.

The DROP VIEW statement is used to drop an existing view from the database. The syntax is as follows:

DROP VIEW [IF EXISTS] view_name;

Parameter description:

  • DROP VIEW:Indicates that you want to drop a view.

  • IF EXISTS:Optional part, used to check whether the view exists. If it exists, the drop operation is performed; if it does not exist, no error will occur. In some database systems, this is optional.

  • view_name:Specifies the name of the view to be dropped.

After executing the following statement, the view high_salary_employees will be dropped from the database.

-- 删除名为 high_salary_employees 的视图
DROP VIEW IF EXISTS high_salary_employees;

Please note that this does not affect the data in the underlying tables; it only removes the definition of the view.

If you need to drop or delete the data in a table, you should use the DROP TABLE statement.

When using the DROP VIEW statement, make sure you really want to drop the view, because once dropped, the definition of the view cannot be recovered.

Other Extensions