Crie visualizações atualizáveis ​​​​do MySQL

Resumo : neste tutorial, mostraremos como criar uma visualização atualizável e atualizar os dados na tabela subjacente por meio da visualização .

Introdução às visualizações atualizáveis ​​do MySQL

No MySQL, as visualizações não são apenas consultáveis, mas também atualizáveis. Isso implica que você pode usar a instrução INSERT ou  UPDATE para adicionar ou modificar linhas da tabela base por meio da visualização atualizável.

Além disso, você pode usar a instrução DELETE para remover linhas da tabela subjacente por meio da visualização.

Entretanto, para criar uma visualização atualizável, a instrução SELECT que define a visualização não deve conter nenhum dos seguintes elementos:

• Funções agregadas  como MIN , MAX , SUM , AVG e  COUNT .
• DISTINTO
• Cláusula GROUP BY .
• Cláusula HAVING .
• Cláusula UNION ou UNION ALL.
• Junção esquerda ou junção externa.
• Subconsulta  na cláusula SELECT ou na cláusula WHERE que se refere à tabela que aparece na cláusula FROM.
• Faça referência a visualizações não atualizáveis ​​na FROMcláusula.
• Use valores literais.
• Múltiplas referências a qualquer coluna da tabela base.

Se você criar uma visualização com o algoritmo TEMPTABLE, a visualização não poderá ser atualizada.

Observe que é possível criar visualizações atualizáveis ​​baseadas em múltiplas tabelas usando um inner join .

Exemplo de visualização atualizável do MySQL

Vamos criar uma visualização atualizável.

Primeiro, crie uma visualização nomeada officeInfo  com base na offices  tabela do banco de dados de amostra . A visualização refere-se a três colunas da officestabela: officeCode, phone, e city.

CREATE VIEW officeInfo
 AS 
   SELECT officeCode, phone, city
   FROM offices;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, recupere os dados da officeInfovisualização usando a seguinte instrução:

SELECT * FROM officeInfo;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, altere o número de telefone do escritório para officeCode  4 na officeInfovisualização usando a seguinte  instrução UPDATE .

UPDATE officeInfo 
SET 
    phone = '+33 14 723 5555'
WHERE
    officeCode = 4;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Por fim, consulte os dados da officeInfo  visualização para verificar a alteração:

SELECT 
    *
FROM
    officeInfo
WHERE
    officeCode = 4;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Verificando informações de visualização atualizáveis

Você pode verificar se uma visualização em um banco de dados é atualizável consultando a is_updatablecoluna da tabela de visualizações no information_schemabanco de dados.

A consulta a seguir obtém todas as visualizações do banco de dados classicmodels e mostra quais visualizações são atualizáveis.

SELECT 
    table_name, 
    is_updatable
FROM
    information_schema.views
WHERE
    table_schema = 'classicmodels';Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Removendo linhas por meio da visualização

Primeiro, crie uma tabela chamada itens, insira algumas linhas na tabela de itens e crie uma visualização que contenha itens cujos preços sejam superiores a 700.

-- create a new table named items
CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(11 , 2 ) NOT NULL
);

-- insert data into the items table
INSERT INTO items(name,price) 
VALUES('Laptop',700.56),('Desktop',699.99),('iPad',700.50) ;

-- create a view based on items table
CREATE VIEW LuxuryItems AS
    SELECT 
        *
    FROM
        items
    WHERE
        price > 700;
-- query data from the LuxuryItems view
SELECT 
    *
FROM
    LuxuryItems;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, use a DELETE instrução para remover uma linha com valor de id 3.

DELETE FROM LuxuryItems 
WHERE id = 3;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O MySQL retorna uma mensagem dizendo que 1 linha(s) foi(ão) afetada(s).

Terceiro, verifique os dados através da visualização novamente:

SELECT 
    *
FROM
    LuxuryItems;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Por fim, consulte os dados dos itens da tabela base para verificar se a DELETEinstrução excluiu a linha.

SELECT 
    *
FROM
    items;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A saída mostra que a linha com id 3 foi removida da tabela base.

Neste tutorial, você aprendeu como criar uma visualização atualizável e modificar dados na tabela subjacente por meio da visualização.