What is the Mysql CREATE VIEW command for?

Asked

Viewed 10,727 times

14

I wonder what the command is for CREATE VIEW mysql?

mysql

4 answers

20


CREATE VIEW creates an object of the type VIEW in your database.

One View is a transformation on top of one or more tables that behaves like a table. It is usually read-only (it is not possible to insert, change, or delete lines from it).

Example:

CREATE VIEW MinhaView AS
SELECT COLUNA2, COLUNA4, COLUNA6 
FROM TABELA 
WHERE COLUNA1 = 1;

You can select data directly on the View after the creation:

SELECT * FROM MinhaView;

The result shall be equivalent to SELECT that is part of the body of View.

EDIT

Example asked for comment:

CREATE VIEW MinhaView2 AS
SELECT CONCAT(NOME, ' ', SOBRENOME) AS NOME_COMPLETO, COLUNA2, COLUNA3, COLUNA4
FROM TABELA_COM_NOME;

  • I can create a column and set a value for it? example, take the value of a column name and the value of a column name and set these two values in a single column?

  • 1

    Yes, you can. I’ll set an example for you.

  • This CREATE VIEW I use it every time before making the query to create the correct view?

  • No. It is used only once to create the object. Then the select on top of View to get the results.

  • But if I add a new name this view will not be updated, ie I use it after the Insert?

  • 1

    @Leandrocosta In this case, you will need to change the View. This can be done or using CREATE OR REPLACE VIEW, or using ALTER VIEW. The syntax is the same as CREATE VIEW.

Show 1 more comment

10

Serves to create a 'read-only table/virtual table that is based on queries. Can act as a shortcut to long queries.

Instead of putting this query in several places in the code

SELECT p.nome, c.categoria_descricao, t.tipo_descricao FROM produtos as p
INNER JOIN categorias as c ON  p.id_categoria = c.id_categoria
INNER JOIN tipo as t ON  p.id_tipo = t.id_tipo

You could create a view to bring the description and product type, simplifying your call.

CREATE view produto_simplificado as
SELECT p.nome, c.categoria_descricao, t.tipo_descricao FROM produtos as p
INNER JOIN categorias as c ON  p.id_categoria = c.id_categoria
INNER JOIN tipo as t ON  p.id_tipo = t.id_tipo

then just call it that way

SELECT * FROM produto_simplificado

4

A quote taken from the Imaster post (Working with VIEWS in MYSQL)

A View is an object that belongs to a database, defined based on SELECT statements, returning a given data visualization of one or more tables. These objects are sometimes called "virtual Tables", formed from other tables which in turn are called "based Tables" or other Views. And some cases, Views are upgradable and may be targets of INSERT, UPDATE and DELETE declaration, which actually modify your "based Tables".

The benefits of using Views, in addition to those already pointed out, are:

A View can be used, for example, to return a value based on a record identifier; can be used to promote data restrictions to increase data security, and define access policies at table and column level. They can be configured to display different columns for different users database; can be used with a set of tables that may be attached to other sets of tables using JOIN s or UNION.

  • 1

    It is preferable to make examples and explanations instead of citations and external links.

3

A View is a SELECT query that is saved in the database to be used later. When you need the View data in any context, just use the View name, which will be as a sub-SELECT at the point where the View name is used.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.