Skip to content

Create a view

What is a view

A view is a visual, read-only virtual table based on the result set of SQL statements, and its content is defined by a query. Unlike ordinary tables (tables that store data), the view does not contain data, but is just a formatted display based on the query results of the base table (the table being queried). You can think of a view as a window of a table, and the data in this window is reflected on other tables. When querying a view, the database applies the SQL query of that view to its underlying table.

Advantages of ## View

  • Simplified query: For complex queries, you can create views to hide the complexity of the query, just select data from the view without remembering complex query statements.

  • Add an additional security layer: Views can restrict users from accessing certain database fields, showing only the fields they need to see, which can protect the data from security.

  • Maintain data consistency: If multiple queries require the same query clause, then creating a view can maintain data consistency.

  • Logical Abstraction: Views can represent useful parts of the base table data, or summarize, and information combined from several tables.

But views also have disadvantages:

  • Performance: Querying data from a database view can be slow, especially if the view is created based on other views.

  • Depend on other tables: A view will be created based on the underlying tables of the database. Not all views support updating data, which mainly depends on the definition of the view and its underlying table.

Prepare before starting

Before reading this page, you need to prepare the following:

How to use views

The syntax for creating a view is as follows:

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

After creating a view, you can query the view as you would other tables:

SELECT column1, column2, ...
FROM view_name;

Example

-- Create a table called 'orders'
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    order_amount DOUBLE,
    PRIMARY KEY (order_id)
);

-- Insert some data into the 'orders' table
INSERT INTO orders (customer_id, order_date, order_amount)
VALUES (1, '2023-01-01', 99.99),
       (1, '2023-01-03', 29.99),
       (2, '2023-01-03', 49.99),
       (3, '2023-01-05', 89.99),
       (1, '2023-01-07', 59.99),
       (2, '2023-01-07', 19.99);

-- Create a view called 'order_summary' that shows the total order quantity and total order amount for each customer
CREATE VIEW order_summary AS
SELECT customer_id, COUNT(*) as order_count, SUM(order_amount) as total_amount
FROM orders
GROUP BY customer_id;

-- Query View
mysql> SELECT *
FROM order_summary;
+--------------------------------------------------------+
| customer_id | order_count | total_amount |
+--------------------------------------------------------+
| 1 | 3 | 189.97 |
| 2 | 2 | 69.98 |
| 3 | 1 | 89.99 |
+--------------------------------------------------------+
3 rows in set (0.01 sec)