{"id":13791,"date":"2023-12-29T01:39:24","date_gmt":"2023-12-29T08:39:24","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/mysql-views\/mysql-create-view\/"},"modified":"2023-12-29T01:39:59","modified_gmt":"2023-12-29T08:39:59","slug":"mysql-create-view","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-views\/mysql-create-view\/","title":{"rendered":"MySQL CREATE VIEW"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL CREATE VIEW<\/code>  statement to create a new view in the database.<\/p>\n\n\n\n

Introduction to MySQL CREATE VIEW statement<\/h2>\n\n\n\n

The CREATE VIEW<\/code> statement creates a new view in the database. Here is the basic syntax of the CREATE VIEW<\/code> statement:<\/p>\n\n\n

CREATE<\/span> [OR<\/span> REPLACE<\/span>] VIEW<\/span> [db_name.]view_name [(column_list)]\nAS<\/span>\n  select<\/span>-statement<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, specify the name of the view that you want to create after the CREATE VIEW<\/code> keywords. The name of the view is unique in a database. Because views and tables in the same database share the same namespace, the name a view cannot the same as the name of an existing table.<\/p>\n\n\n\n
Second, use the OR REPLACE<\/code> option if you want to replace an existing view if the view already exists. If the view does not exist, the OR REPLACE<\/code> has no effect.<\/p>\n\n\n\n
Third, specify a list of columns for the view. By default, the columns of the view are derived from the select list of the SELECT<\/code> statement. However, you can explicitly specify the column list for the view by listing them in parentheses following the view name.<\/p>\n\n\n\n
Finally,  specify a SELECT<\/a><\/code> statement that defines the view. The SELECT<\/code>  statement can query data from tables or views. MySQL allows you to use the ORDER BY<\/a><\/code> clause in the SELECT<\/code> statement but ignores it if you select from the view with a query that has its own ORDER BY<\/code> clause.<\/p>\n\n\n\n
By default, the CREATE VIEW<\/code> statement creates a view in the current database. If you want to explicitly create a view in a given database, you can qualify the view name with the database name.<\/p>\n\n\n\n
MySQL CREATE VIEW examples<\/h2>\n\n\n\n
Let’s take some example of using the CREATE VIEW<\/code> statement to create new views.<\/p>\n\n\n\n
1) Creating a simple view example<\/h3>\n\n\n\n
Let’s take a look at the orderDetails<\/code> table from the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
This statement uses the CREATE VIEW<\/code> statement to create a view that represents total sales per order.<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> salePerOrder AS<\/span>\n    SELECT<\/span> \n        orderNumber, \n        SUM<\/span>(quantityOrdered * priceEach) total\n    FROM<\/span>\n        orderDetails\n    GROUP<\/span> by<\/span> orderNumber\n    ORDER<\/span> BY<\/span> total DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you use the SHOW TABLE<\/code> command to view all tables in the classicmodels<\/code> database, you will see the viewsalesPerOrder<\/code> is showing up in the list.<\/p>\n\n\n
SHOW<\/span> TABLES<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"show<\/figure>\n\n\n\n
This is because the views and tables share the same namespace as mentioned earlier.<\/p>\n\n\n\n
To know which object is a view or table, you use the SHOW FULL TABLES<\/a><\/code> command as follows:<\/p>\n\n\n
SHOW<\/span> FULL<\/span> TABLES<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"show<\/figure>\n\n\n\n
The table_type<\/code> column in the result set specifies the type of the object: view or table (base table).<\/p>\n\n\n\n
If you want to query total sales for each sales order, you just need to execute a simple SELECT<\/code>  statement against the SalePerOrder<\/code>  view as follows:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> salePerOrder;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mysql<\/figure>\n\n\n\n
2) Creating a view based on another view example<\/h3>\n\n\n\n
MySQL allows you to create a view based on another view.<\/p>\n\n\n\n
For example, you can create a view called bigSalesOrder<\/code> based on the salesPerOrder<\/code> view to show every sales order whose total is greater than 60,000<\/code> as follows:<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> bigSalesOrder AS<\/span>\n    SELECT<\/span> \n        orderNumber, \n        ROUND<\/span>(total,2<\/span>) as<\/span> total\n    FROM<\/span>\n        salePerOrder\n    WHERE<\/span>\n        total > 60000<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Now, you can query the data from the bigSalesOrder<\/code> view as follows:<\/p>\n\n\n
SELECT<\/span> \n    orderNumber, \n    total\nFROM<\/span>\n    bigSalesOrder;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"create<\/figure>\n\n\n\n
3) Creating a view with join example<\/h3>\n\n\n\n
The following example uses the CREATE VIEW<\/code> statement to create a view based on multiple tables. It uses the INNER JOIN<\/a><\/code> clauses to join tables.<\/p>\n\n\n
CREATE<\/span> OR<\/span> REPLACE<\/span> VIEW<\/span> customerOrders AS<\/span>\nSELECT<\/span> \n    orderNumber,\n    customerName,\n    SUM<\/span>(quantityOrdered * priceEach) total\nFROM<\/span>\n    orderDetails\nINNER<\/span> JOIN<\/span> orders o USING<\/span> (orderNumber)\nINNER<\/span> JOIN<\/span> customers USING<\/span> (customerNumber)\nGROUP<\/span> BY<\/span> orderNumber;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement selects data from the customerOrders<\/code> view:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> customerOrders \nORDER<\/span> BY<\/span> total DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This picture shows the partial output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
4) Creating a view with a subquery example<\/h3>\n\n\n\n
The following example uses the CREATE VIEW<\/code> statement to create a view whose SELECT<\/code> statement uses a subquery<\/a>. The view contains products whose buy prices are higher than the average price of all products.<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> aboveAvgProducts AS<\/span>\n    SELECT<\/span> \n        productCode, \n        productName, \n        buyPrice\n    FROM<\/span>\n        products\n    WHERE<\/span>\n        buyPrice > (\n            SELECT<\/span> \n                AVG<\/span>(buyPrice)\n            FROM<\/span>\n                products)\n    ORDER<\/span> BY<\/span> buyPrice DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This query data from the aboveAvgProducts<\/code> is simple as follows:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> aboveAvgProducts;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mysql<\/figure>\n\n\n\n
5) Creating a view with explicit view columns example<\/h3>\n\n\n\n
This statement uses the CREATE VIEW<\/code> statement to create a new view based on the customers and orders tables with explicit view columns:<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> customerOrderStats (\n   customerName , \n   orderCount\n) \nAS<\/span>\n    SELECT<\/span> \n        customerName, \n        COUNT<\/span>(orderNumber)\n    FROM<\/span>\n        customers\n            INNER<\/span> JOIN<\/span>\n        orders USING<\/span> (customerNumber)\n    GROUP<\/span> BY<\/span> customerName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This query returns data from the customerOrderStats<\/code> view:<\/p>\n\n\n
SELECT<\/span> \n    customerName,\n    orderCount\nFROM<\/span>\n    customerOrderStats\nORDER<\/span> BY<\/span> \n\torderCount, \n    customerName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
In this tutorial, we have shown you how to use the MySQL CREATE VIEW<\/code> statement to create views in the database.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t