How to Create a View in MySQL

This article will show you how to create and manage views in MySQL. A view is a virtual table that does not store its own data but rather displays data that is stored in other tables. Essentially, a view is a result of SQL query execution, which returns the required rows of data from one or multiple tables.

Contents

1. How to create a simple MySQL view with the CREATE VIEW statement
2. How to create a view with JOINs to combine data from multiple tables
3. How to update a MySQL view
4. How to drop a MySQL view
5. How to create a view in dbForge Studio for MySQL

How to create a simple MySQL view with the CREATE VIEW statement

The basic syntax for creating a view in MySQL is as follows:

CREATE VIEW [db_name.]view_name [(column_list)]
AS
  select-statement;

• [db_name.] is the name of the database where your view will be created; if not specified, the view will be created in the current database
• view_name is a unique name of the view you are creating
• [(column_list)] defines the required list of columns that can be indicated in parentheses after the view name; by default, the list of columns is retrieved from the select list of the SELECT statement
• select-statement is a specified SELECT statement that can query data from tables or views

Here is the simplest example. If we have a table called customers in our current database, and we would like to request a list of customers with the transaction dates of their orders, the script may look as follows:

CREATE VIEW transactions AS
    SELECT 
        id_number,
        name,
        transaction_date
    FROM
        customers;

After we execute this statement, the transactions object will be available in Views. Now we can move on and execute a statement that selects all the fields in this view:

SELECT * FROM transactions;

The output will constitute a table containing three columns: id_number, name, and transaction_date.

How to create a view with JOINs to combine data from multiple tables

Our next example is somewhat more complicated since it involves multiple tables (there will be three in our case):

CREATE VIEW order_incomes AS
SELECT
    order_id,
    customer_name,
    SUM(ordered_quantity * product_price) total
FROM
    order_details
INNER JOIN orders USING (order_id)
INNER JOIN customers USING (customer_name)
GROUP BY order_id;

This view gives us information on order income per customer, grouped by order ID. For that purpose, we calculate the total income using the order_details table data and use the INNER JOIN clause to retrieve order IDs from the orders table and customer names from the customers table.

How to update a MySQL view

If you need to update tables through views, you can use the INSERT, UPDATE, and DELETE statements to perform the corresponding operations with the rows of the underlying table. However, please note that in order to be updatable, your view must not include any of the following:

• Aggregate functions, e.g. MIN, MAX, COUNT, AVG, or SUM
• Such clauses as GROUP BY, DISTINCT, HAVING, UNION or UNION ALL
• Left or outer JOINs
• Multiple references to any column of the base table
• Subqueries in the SELECT or WHERE clause referring to the table appearing in the FROM clause
• References to non-updatable views in the FROM clause
• References to non-literal values

Now let’s create an updatable view called warehouse_details based on the warehouses table.

CREATE VIEW warehouse_details AS
   SELECT warehouse_id, phone, city
   FROM warehouses;

Now we can query data from this view:

SELECT * FROM warehouse_details;

Let’s say we want to change the phone number of the warehouse with the warehouse_id ’55’ through the warehouse_details view using the following UPDATE statement.

UPDATE warehouse_details 
SET 
    phone = '(555) 555-1234'
WHERE
    warehouse_id = 55;

Finally, we can check whether the change has been applied using the following query:

SELECT * FROM warehouse_details
WHERE
    warehouse_id = 55;

How to drop a MySQL view

If we no longer need a certain view, we can delete it with a simple DROP statement:

DROP VIEW warehouse_details;

How to create a view in dbForge Studio for MySQL

Now that we know the basic syntax, we need to find a tool that will help us manage our databases and views most effectively. We suggest you try dbForge Studio for MySQL, a toolset that covers nearly any operation with MySQL databases you can think of. Download a free trial, spend a couple of minutes installing it, and let’s get started.

Once you are connected to your MySQL database (look here to see how it is done), you can create a view using one of the two following ways.

The first way is writing and executing a query in a SQL document. Here dbForge Studio for MySQL delivers context-sensitive code completion, automatic syntax check, code snippets, quick navigation through large scripts, and customizable formatting profiles. In other words, you get every feature you might need in a single convenient IDE.

If you want to master this functionality with easy step-by-step guides, feel free to check the Writing and Executing SQL Statements section of our documentation. You will find everything there, from the creation of a new SQL document to the automated execution of your queries via the command-line interface.

The second way is one of the most notable tools of dbForge Studio — Query Builder. It presents your queries visually as diagrams, generates the abovementioned JOINs, and enables the interactive building of the INSERT, UPDATE, and DELETE statements to update your views.

Again, describing the workflow is more than this article can handle, but we have a special Query Builder section in our documentation, where you can get detailed guides to building and managing visual diagrams.

Conclusion

dbForge Studio for MySQL is an all-encompassing IDE for database development, management, and administration. It offers the easiest ways of building queries, comparing and analyzing data, developing and debugging stored procedures, comparing and syncing database schemas, and much more.

Get a 30-day free trial of dbForge Studio for MySQL today and see how irreplaceable it can become for your daily operations.