Create View

What is View

View is a visual, read-only virtual table based on the result set of an SQL statement, whose content is defined by the query. Unlike ordinary tables (tables that store data), views do not contain data but are only formatted displays of query results based on the base table (the table being queried). You can think of a view as a window on a table; the data in this window is reflected on other tables. When a view is queried, the database applies its SQL query to its underlying tables.

Advantages of Views

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

  • Add an extra layer of security: Views can restrict user’s access to specific database fields and only display the fields they need to see, which can protect the security of the data.

  • Maintain data consistency: Creating a view can maintain data consistency if multiple queries need to use the same query clause.

  • Logical abstraction: Views can represent functional parts of base table data, summaries, and information 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.

  • Depends on other tables: A view will be created based on the underlying tables of the database. Not all views support updating data, depending on the definition of the view and its underlying tables.

Before you start

Before reading this document, make sure that the following tasks are completed:

How to use views

The syntax for creating a view is as follows:

  1. CREATE VIEW view_name AS
  2. SELECT column1, column2, ...
  3. FROM table_name
  4. WHERE condition;

After creating a view, you can query it like any other table:

  1. SELECT column1, column2, ...
  2. FROM view_name;

Example

  1. -- Create a table called 'orders'
  2. CREATE TABLE orders (
  3.     order_id INT AUTO_INCREMENT,
  4.     customer_id INT,
  5.     order_date DATE,
  6.     order_amount DOUBLE,
  7.     PRIMARY KEY (order_id)
  8. );
  10. -- Insert some data into the 'orders' table
  11. INSERT INTO orders (customer_id, order_date, order_amount)
  12. VALUES (1, '2023-01-01', 99.99),
  13.        (1, '2023-01-03', 29.99),
  14.        (2, '2023-01-03', 49.99),
  15.        (3, '2023-01-05', 89.99),
  16.        (1, '2023-01-07', 59.99),
  17.        (2, '2023-01-07', 19.99);
  19. -- Create a view called 'order_summary' that shows the total order quantity and total order amount for each customer
  20. CREATE VIEW order_summary AS
  21. SELECT customer_id, COUNT(*) as order_count, SUM(order_amount) as total_amount
  22. FROM orders
  23. GROUP BY customer_id;
  25. --  Query view
  26. mysql> SELECT *
  27. FROM order_summary;
  28. +-------------+-------------+--------------+
  29. | customer_id | order_count | total_amount |
  30. +-------------+-------------+--------------+
  31. |           1 |           3 |       189.97 |
  32. |           2 |           2 |        69.98 |
  33. |           3 |           1 |        89.99 |
  34. +-------------+-------------+--------------+
  35. 3 rows in set (0.01 sec)