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

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

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

How to update a MySQL view

How to drop a MySQL view

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:

[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:

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:

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):

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.

Now we can query data from this view:

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.

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

How to drop a MySQL view

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

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 trial, spend a couple of minutes installing it, and let’s get started.

Once you are connected to your MySQL database, 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.

Query formatting in a SQL document

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.

Visual query design with JOINs between tables

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.