Views e Tabelas Virtuais do MySQL

Introdução

Neste laboratório, você aprenderá a trabalhar com views do MySQL. Uma view é uma tabela virtual baseada no conjunto de resultados de uma instrução SQL. Views são úteis para simplificar consultas complexas, ocultar a complexidade dos dados e fornecer um nível de abstração para o acesso aos dados. Você aprenderá a criar, atualizar, consultar e remover views do MySQL.

Você começará conectando-se a um servidor MySQL, criando um banco de dados chamado company e populando-o com uma tabela employees. Em seguida, criará uma view chamada sales_employees que filtra a tabela employees para mostrar apenas funcionários do departamento 'Sales'. Você aprenderá a verificar a estrutura da view e a consultá-la como uma tabela regular.

Conectar ao MySQL e Criar Banco de Dados/Tabela

Nesta etapa, você se conectará ao servidor MySQL e configurará o banco de dados e a tabela necessários para o laboratório. Isso envolve a criação de um banco de dados chamado company e uma tabela employees dentro dele, populada com alguns dados de exemplo.

Primeiro, abra um terminal na VM LabEx. Você já deve estar no diretório ~/project.

Conecte-se ao servidor MySQL como usuário root. Como você tem privilégios sudo e o usuário root no MySQL está configurado para usar o plugin auth_socket, você pode se conectar sem senha usando sudo.

sudo mysql -u root

Você está agora no shell do MySQL. O prompt mudará para mysql>.

Dentro do shell do MySQL, crie um banco de dados chamado company:

CREATE DATABASE company;

Mude para o banco de dados company para que os comandos subsequentes operem dentro deste banco de dados:

USE company;

Agora, crie uma tabela chamada employees para armazenar informações de funcionários:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department VARCHAR(50)
);

Insira alguns dados de exemplo na tabela employees:

INSERT INTO employees (id, first_name, last_name, salary, department) VALUES
(1, 'John', 'Doe', 60000.00, 'Sales'),
(2, 'Jane', 'Smith', 75000.00, 'Marketing'),
(3, 'Robert', 'Jones', 50000.00, 'Sales'),
(4, 'Emily', 'Brown', 80000.00, 'Engineering'),
(5, 'Michael', 'Davis', 65000.00, 'Marketing');

Você pode verificar os dados na tabela employees selecionando todas as linhas:

SELECT * FROM employees;

A saída deve mostrar os dados dos funcionários inseridos:

+----+------------+-----------+----------+-------------+
| id | first_name | last_name | last_name | department  |
+----+------------+-----------+----------+-------------+
|  1 | John       | Doe       | 60000.00 | Sales       |
|  2 | Jane       | Smith     | 75000.00 | Marketing   |
|  3 | Robert     | Jones     | 50000.00 | Sales       |
|  4 | Emily      | Brown     | 80000.00 | Engineering |
|  5 | Michael    | Davis     | 65000.00 | Marketing   |
+----+------------+-----------+----------+-------------+
5 rows in set (0.00 sec)

Você se conectou com sucesso ao MySQL, criou um banco de dados e populou uma tabela com dados.

Criar uma View

Nesta etapa, você criará uma view baseada na tabela employees. Uma view é uma consulta SQL armazenada que funciona como uma tabela virtual. Ela não armazena dados em si, mas apresenta dados de uma ou mais tabelas subjacentes.

Você ainda deve estar no shell do MySQL, conectado ao banco de dados company. Caso contrário, reconecte-se usando sudo mysql -u root e depois USE company;.

Criaremos uma view chamada sales_employees que mostrará apenas os funcionários que trabalham no departamento 'Sales'. Esta view selecionará colunas específicas da tabela employees.

CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

Esta instrução define uma view chamada sales_employees. A definição da view é a consulta SELECT que segue a palavra-chave AS. Esta consulta seleciona as colunas id, first_name, last_name e salary da tabela employees, mas apenas para as linhas onde a coluna department é igual a 'Sales'.

Para confirmar que a view foi criada, você pode listar as tabelas e views no banco de dados atual:

SHOW TABLES;

Você deverá ver employees e sales_employees listadas.

+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
| sales_employees   |
+-------------------+
2 rows in set (0.00 sec)

Você também pode descrever a estrutura da view, assim como faria com uma tabela:

DESCRIBE sales_employees;

Isso mostrará as colunas incluídas na view:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Você criou com sucesso uma view chamada sales_employees.

Consultar Dados Usando a View

Nesta etapa, você aprenderá a consultar dados usando a view sales_employees que você criou. Views podem ser consultadas como tabelas regulares, fornecendo uma maneira simplificada de acessar subconjuntos específicos de dados.

Você ainda deve estar no shell do MySQL, conectado ao banco de dados company. Caso contrário, reconecte-se usando sudo mysql -u root e depois USE company;.

Para recuperar todos os dados da view sales_employees, use uma instrução SELECT padrão:

SELECT * FROM sales_employees;

Esta consulta executa a instrução SELECT subjacente definida na view e retorna o resultado. Você deverá ver apenas os funcionários do departamento 'Sales':

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
|    3 | Robert     | Jones     | 50000.00 |
+------+------------+-----------+----------+
2 rows in set (0.00 sec)

Você também pode aplicar filtros ou ordenações adicionais aos dados recuperados da view. Por exemplo, para encontrar o funcionário de vendas com um salário superior a 55000:

SELECT * FROM sales_employees WHERE salary > 55000;

Isso retornará apenas as linhas da view onde o salário é superior a 55000:

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
+------+------------+-----------+----------+
1 row in set (0.00 sec)

Você também pode selecionar colunas específicas da view:

SELECT first_name, last_name FROM sales_employees;

Isso exibirá apenas os nomes e sobrenomes dos funcionários de vendas:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
| Robert     | Jones     |
+------------+-----------+
2 rows in set (0.00 sec)

Consultar uma view é essencialmente o mesmo que consultar uma tabela, mas a view fornece um filtro predefinido e uma seleção de colunas da fonte de dados subjacente.

Atualizar a Definição da View com ALTER VIEW

Nesta etapa, você aprenderá a modificar a definição de uma view existente usando a instrução ALTER VIEW. Isso é útil quando você precisa alterar as colunas incluídas na view ou os critérios de filtragem.

Você ainda deve estar no shell do MySQL, conectado ao banco de dados company. Caso contrário, reconecte-se usando sudo mysql -u root e depois USE company;.

Atualmente, a view sales_employees inclui id, first_name, last_name e salary. Vamos modificar a view para incluir também a coluna department.

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';

Esta instrução usa ALTER VIEW seguido pelo nome da view e a nova consulta SELECT que define a view. A nova consulta agora inclui a coluna department.

Para verificar se a view foi atualizada, descreva-a novamente:

DESCRIBE sales_employees;

Você deverá ver agora a coluna department na saída:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
| department | varchar(50)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Consulte a view atualizada para ver a nova coluna:

SELECT * FROM sales_employees;

A saída agora incluirá a coluna department:

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
|    3 | Robert     | Jones     | 50000.00 | Sales      |
+------+------------+-----------+----------+------------+
2 rows in set (0.00 sec)

Você atualizou com sucesso a definição da view sales_employees.

Remover a View e Limpar

Nesta etapa final, você aprenderá a remover (excluir) a view e limpar o banco de dados e a tabela criados durante este laboratório.

Você ainda deve estar no shell do MySQL, conectado ao banco de dados company. Caso contrário, reconecte-se usando sudo mysql -u root e depois USE company;.

Para remover a view sales_employees, use a instrução DROP VIEW:

DROP VIEW sales_employees;

Este comando remove permanentemente a view sales_employees do banco de dados company.

Para confirmar que a view foi removida, você pode tentar descrevê-la:

DESCRIBE sales_employees;

Isso deverá resultar em uma mensagem de erro indicando que a view não existe:

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

Agora, vamos limpar a tabela employees e o banco de dados company.

Remova a tabela employees:

DROP TABLE employees;

Remova o banco de dados company:

DROP DATABASE company;

Você pode sair do shell do MySQL digitando:

exit

Você removeu com sucesso a view, a tabela e o banco de dados, limpando os recursos utilizados neste laboratório.

Resumo

Neste laboratório, você aprendeu a trabalhar com views do MySQL. Você começou conectando-se ao servidor MySQL e configurando um banco de dados e uma tabela. Em seguida, criou uma view chamada sales_employees para fornecer uma visualização filtrada da tabela employees.

Você praticou a consulta da view usando instruções SELECT, demonstrando como as views simplificam o acesso aos dados. Você também aprendeu a modificar a definição de uma view existente usando a instrução ALTER VIEW para incluir colunas adicionais. Finalmente, você aprendeu a remover uma view usando a instrução DROP VIEW e limpou o banco de dados e a tabela.

Agora você entende os conceitos básicos e as operações para trabalhar com views do MySQL, que são ferramentas valiosas para gerenciar e acessar dados em um banco de dados.