Creating Views in MySQL : How to create mysql views

Summary: in this tutorial, you will learn how to create views in MySQL by using the CREATE VIEW statement.

Introduction to CREATE VIEW statement

The syntax of creating a view in MySQL is as follows:

 

1234

5

  CREATE[ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]VIEW [database_name].[view_name]AS

[SELECT  statement]

Algorithms

The algorithm attribute allows you to control which mechanism is used when creating a view. MySQL provides the MERGETEMPTABLE and UNDEFINED algorithms.

  • MERGE means the input query will be combined with the SELECT statement of the view definition. MySQL will execute the combined query to return the result set. This mechanism is more efficient than TEMPTABLE (temporary table) but MERGE only allowed when the rows in the view represent a one-to-one relationship with the rows in the underlying table. In case theMERGE is not allowed, MySQL will switch the algorithm to UNDEFINED. The combination of input query and query in view definition into one query sometimes refers as view resolution.
  • TEMPTABLE means that MySQL first creates a temporary table based on the SELECTstatement of the view definition, and then it executes the input query against this temporary table. Because MySQL has to create temporary table to store the result set and move the data from the physical tables to the temporary table, the TEMPTABLE algorithm is less efficient than the MERGE algorithm. In addition, a view that uses TEMPTABLE algorithm is not updateable.
  • UNDEFINED is the default algorithm when you create a view without specifying an explicit algorithm. The UNDEFINED algorithm allows MySQL to make a decision whether to use MERGEor TEMPTABLE. MySQL prefers MERGE to TEMPTABLE, which is more efficient.

View name

Each view is associated with a specific database therefore you can have database name prefix with the view name. Names of views share the same domain with tables therefore they cannot be the same names as tables in a database.

SELECT statement

In the SELECT statement, you can query data from any table or view that exists in the database. There are several rules that the SELECT statement must follow:

  • The SELECT statement can contain a subquery in WHERE clause but not in the FROM clause.
  • The SELECT statement cannot refer to any variable including local variable, user variable or session variable.
  • The SELECT statement cannot refer to the parameters of prepared statements.

MySQL create view examples

Create a simple view

Let’s take a look at the orderDetails table. We can create a view that represents total sales per order.

 

1234

5

6

7

CREATE VIEW SalePerOrderASSELECT orderNumber,SUM  (quantityOrdered * priceEach) total

FROM orderDetails

GROUP by orderNumber

ORDER BY total DESC

If you want to query total sales for each sales order, you just need to execute a simple SELECTstatement against the SalePerOrder view as follows:

 

123 SELECT totalFROM salePerOrderWHERE orderNumber = 10102

Create view with JOIN

The following is an example of creating a view with an INNER JOIN statement. The view contains order numbercustomer name and total sales per order.

 

1234

5

6

7

8

9

 CREATE VIEW customerOrders ASSELECT  D.orderNumber,customerName,SUM(quantityOrdered * priceEach) total

FROM orderDetails D

INNER JOIN orders O ON O.orderNumber = D.orderNumber

INNER JOIN customers C ON O.customerNumber =  C.customerNumber

GROUP BY D.orderNumber

ORDER BY total DESC

Create view with subquery

The following illustrates how to create a view with subquery. The view contains products whose buy prices are higher than average price of all products.

 

1234

5

6

7

8

9

10

 CREATE VIEW vwProducts  ASSELECT productCode,productName,buyPrice

FROM products

WHERE buyPrice > (

SELECT AVG  (buyPrice)

FROM  products

)

ORDER BY buyPrice DESC

In this tutorial, we have shown you how to create views by using the CREATE VIEW statement.

 

Related Posts

  • 78
    Summary: in this tutorial, you will learn how to manage views in MySQL including displaying, modifying and removing views. Show view definition in MySQL MySQL provides the SHOW CREATE VIEW statement that helps you show view definition. The following is the syntax of the SHOW CREATE VIEW statement:   1 SHOW CREATE VIEW [database_name].[view_ name]; To…
    Tags: view, create, statement
  • 52
    Summary: in this tutorial, you are going to learn about MySQL View. We will explain how MySQL implements views. MySQL supports database views or views since version 5.X. In MySQL, almost features of views conform to the SQL: 2003 standard. MySQL process queries to the views in two ways: MySQL creates a temporary table…
    Tags: mysql, view
  • 51
    Syntax: IF(expr,if_true_expr,if_false_expr) MySQL IF function is control flow functions that returns a value based on a condition. The IF function is sometimes referred to as IF ELSE or IF THEN ELSE function. If the expr evaluates to TRUE i.e., expr is not NULL and expr is not 0, the IF…
    Tags: select, mysql
  • 46
    Here is an example to select multiple column of a table from select statement in asterisk. exten => h,1,MYSQL(Connect conn localhost username password database) exten => h,n,MYSQL(Query res ${conn} 'SELECT call_id,callerid,calltime FROM todaycall WHERE calltime=curdate() order by calltime desc limit 1') exten => h,n,MYSQL(Fetch fid ${r} call_id callerid calltime) exten…
    Tags: select, statement, mysql
  • 42
      [root@mysqlDB]# mysqladmin -u root -p create eduguru Enter password:****** This will create a MySQL database eduguru. This is a simple example to create database called eduguru. Example: <?php $dbhost = 'localhost:3036'; //If mysql server is on same server $dbuser = 'root'; //mysql user who has the permission to create database $dbpass…
    Tags: create, mysql

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: