# Create a View

●

Published 2022-12-03

This tutorial explains to you how to create a view into MySQL database (schema).

In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.

When you create a view into a MySQL schema, you can consider that you create a view into a specific database.

In my case I will create a view into the "X" database (schema).

If you want to create the view using the MySQL Workbench, here are the steps:

Connect to the MySQL Workbench, and right click on the schema where you want to create that view:

Choose the "Crete View ..." and you will see the following screen:

Enter the select on which the view will be based, click on "Apply" and you will see the following screen:

You see the SQL Script for creating that view. Click on "Apply" in order to create that view.

SQL script was successfully applied to MySQL database. You can click on "Finish" to exit that window.

In the picture above you can see how you can create the SQL statement for creating the script for that view.

Here the SQL command for creating that view:

CREATE
ALGORITHM=UNDEFINED 
DEFINER=`root`@`localhost` 
SQL SECURITY DEFINER 
VIEW `x`.`view1` 
  AS select `x`.`table1`.`Column1` AS `Column1`,
            `x`.`table1`.`Column2` AS `Column2` 
     from `x`.`table1`;

The optional ALGORITHM clause for CREATE VIEW or ALTER VIEW is a MySQL extension to standard SQL. It affects how MySQL processes the view.

ALGORITHM takes three values:

MERGE : the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.
TEMPTABLE : the results from the view are retrieved into a temporary table, which then is used to execute the statement.
UNDEFINED : MySQL chooses which algorithm to use.

It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used.

DEFINER = who create the view

Views can have a SQL SECURITY characteristic with a value of DEFINER or INVOKER to specify whether the object executes in definer or invoker context. If the SQL SECURITY characteristic is omitted, the default is definer context.