Introdução

O MySQL possui um ótimo recurso conhecido como “views”. Visualizações são consultas armazenadas. Pense neles como um alias para uma consulta longa. Neste guia, mostrarei como usar modos de exibição para organizar os dados com mais eficiência.

Pré-requisitos

  • Um servidor de nuvem Site & Site SSD carregado com o Debian 7.
  • Usuário com privilégios de administrador (root).
  • Conhecimento básico de Linux, linha de comando e SSH.

Etapa 1 – Instalar o servidor MySQL

Instalar o MySQL no Debian 7.x é muito simples. Primeiro, precisamos garantir que nossas fontes sejam atualizadas executando:

sudo apt-get update

Em seguida, podemos instalar o servidor MySQL:

sudo apt-get install -y mysql-server

Uma caixa de diálogo será exibida solicitando que você crie uma senha para o usuário “root”. Lembre-se de lembrar essa senha.

Vamos endurecer a segurança da nossa instalação executando:

sudo mysql_secure_installation

Após a execução, você será presenteado com uma série de prompts. Cada uma das respostas que você deve selecionar é exibida abaixo.

...
Enter current password for root (enter for none):
OK, successfully used password, moving on...
...
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
...
Remove anonymous users? [Y/n] y
 ... Success!
...
Disallow root login remotely? [Y/n] y
 ... Success!
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
...
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...

Etapa 2 – Instalar o banco de dados de amostra

Neste momento, não temos dados no servidor para experimentar. Para este tutorial, usaremos o banco de dados de funcionários , pois é fácil de trabalhar e disponível gratuitamente no site da MySQL.

sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2

Precisamos instalar bzip2para que possamos extrair o arquivo.

sudo apt-get install bzip2

Extraia o banco de dados. O arquivo é bem grande, então pode demorar alguns instantes.

sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2
sudo tar -xf employees_db-full-1.0.6.tar

Uma vez que o arquivo foi extraído, você terá uma pasta intitulada employees_db. Precisamos navegar para esse diretório para instalar o banco de dados.

cd employees_db 
ls -l

A saída ficará assim:

-rw-r--r--. 1 501 games       752 Mar 30  2009 Changelog
-rw-r--r--. 1 501 games      6460 Oct  9  2008 employees_partitioned2.sql
-rw-r--r--. 1 501 games      7624 Feb  6  2009 employees_partitioned3.sql
-rw-r--r--. 1 501 games      5660 Feb  6  2009 employees_partitioned.sql
-rw-r--r--. 1 501 games      3861 Nov 28  2008 employees.sql
-rw-r--r--. 1 501 games       241 Jul 30  2008 load_departments.dump
-rw-r--r--. 1 501 games  13828291 Mar 30  2009 load_dept_emp.dump
-rw-r--r--. 1 501 games      1043 Jul 30  2008 load_dept_manager.dump
-rw-r--r--. 1 501 games  17422825 Jul 30  2008 load_employees.dump
-rw-r--r--. 1 501 games 115848997 Jul 30  2008 load_salaries.dump
-rw-r--r--. 1 501 games  21265449 Jul 30  2008 load_titles.dump
-rw-r--r--. 1 501 games      3889 Mar 30  2009 objects.sql
-rw-r--r--. 1 501 games      2211 Jul 30  2008 README
-rw-r--r--. 1 501 games      4455 Mar 30  2009 test_employees_md5.sql
-rw-r--r--. 1 501 games      4450 Mar 30  2009 test_employees_sha.sql

Execute o seguinte comando para conectar ao servidor MySQL, criar o banco de dados e importar os dados:

sudo mysql -h localhost -u root -p -t < employees.sql

Um aviso aparecerá pedindo sua senha de root. Essa é a senha que você definiu na etapa um.

Sendo que o banco de dados é bastante grande, provavelmente levará de 1 a 3 minutos para importar totalmente os dados. Se tudo foi feito corretamente, você verá a seguinte saída.

+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+

Agora, podemos efetuar login no MySQL e visualizar os dados importados.

sudo mysql -h localhost -u root -p

Digite a senha do root que você definiu na seção anterior.

Verifique a lista de bancos de dados do nosso banco de dados de funcionários recém-criado .

show databases;

A saída ficará assim:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

Vamos usar o banco de dados de funcionários .

use employees;

Verifique as tabelas dentro dele.

show tables;

Isto irá produzir:

+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.01 sec)

Etapa 3 – Criando, usando e removendo visualizações

Nesta etapa, você aprenderá a criar e usar visualizações. Eu dividi este passo em seções menores para dados correspondentes e combinando dados para organização. É hora de começar a interagir com nossos dados de teste.

Mesclando / combinando dados

Abaixo, tenho uma consulta que exibe todos os funcionários que têm um salário anual igual ou superior a US $ 50.000.

select * from salaries where salary >= 50000;

Saída (truncada):

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
(...)

Como você pode ver, isso exibe apenas números de funcionários. Pode ser um incômodo ao tentar identificar um funcionário rapidamente. Felizmente, podemos criar uma visualização que armazene uma consulta razoavelmente longa que possa corresponder os números de funcionários aos nomes dos funcionários, puxando e combinando dados de várias tabelas. A consulta é mostrada abaixo.

select employees.first_name,employees.last_name,employees.emp_no,salaries.salary,salaries.to_date,salaries.from_date from employees, salaries where employees.emp_no = salaries.emp_no;

Observe como eu omiti >= 50000a consulta. Nós estaremos usando este valor após nossa visão ter sido criada.

Para criar a visualização, basta acrescentar create view view_name asà consulta. Neste caso, estarei criando uma visão chamada named_salaries .

create view named_salaries as select employees.first_name,employees.last_name,employees.emp_no,salaries.salary,salaries.to_date,salaries.from_date from employees, salaries where employees.emp_no = salaries.emp_no;

Exibimos dados de uma visão da mesma maneira que exibimos dados de uma tabela.

select * from named_salaries

Se a visualização foi criada corretamente, você verá a seguinte saída (os dados foram truncados):

+------------+-----------+--------+--------+------------+------------+
| first_name | last_name | emp_no | salary | to_date    | from_date  |
+------------+-----------+--------+--------+------------+------------+
| Georgi     | Facello   |  10001 |  60117 | 1987-06-26 | 1986-06-26 |
| Georgi     | Facello   |  10001 |  62102 | 1988-06-25 | 1987-06-26 |
| Georgi     | Facello   |  10001 |  66074 | 1989-06-25 | 1988-06-25 |
| Georgi     | Facello   |  10001 |  66596 | 1990-06-25 | 1989-06-25 |
| Georgi     | Facello   |  10001 |  66961 | 1991-06-25 | 1990-06-25 |
| Georgi     | Facello   |  10001 |  71046 | 8  1992-06-24 | 1991-06-25 |
(...)

Como podemos interagir com as visualizações da mesma forma que podemos interagir com uma tabela, é possível pegar a >= 50000da consulta original e aplicá-la à exibição.

select * from named_salaries where salary >= 50000;

Saída (truncada):

+------------+-----------+--------+--------+------------+------------+
| first_name | last_name | emp_no | salary | to_date    | from_date  |
+------------+-----------+--------+--------+------------+------------+
| Georgi     | Facello   |  10001 |  60117 | 1987-06-26 | 1986-06-26 |
(...)
| Bezalel    | Simmel    |  10002 |  65828 | 1997-08-03 | 1996-08-03 |
(...)
| Chirstian  | Koblick   |  10004 |  50594 | 1992-11-29 | 1991-11-30 |
(...)
| Kyoichi    | Maliniak  |  10005 |  78228 | 1990-09-12 | 1989-09-12 |
(...)
| Anneke     | Preusig   |  10006 |  53747 | 1998-08-03 | 1997-08-03 |
(...)
+------------+-----------+--------+--------+------------+------------+

Como você pode ver, a consulta tratou a exibição como uma tabela tradicional.

Vamos usar uma visão em outro exemplo. Abaixo, tenho uma consulta bastante longa que lista os gerentes de departamento, seus primeiros / últimos nomes, números de funcionários, seus nomes de departamento e os números de departamento. A consulta reúne dados de várias tabelas diferentes.

select employees.first_name,employees.last_name,employees.emp_no,dept_manager.to_date,dept_manager.from_date,departments.dept_name,departments.dept_no from employees, dept_manager, departments where employees.emp_no = dept_manager.emp_no AND departments.dept_no = dept_manager.dept_no;

Saída (truncada):

+-------------+--------------+--------+------------+------------+--------------------+---------+
| first_name  | last_name    | emp_no | to_date    | from_date  | dept_name          | dept_no |
+-------------+--------------+--------+------------+------------+--------------------+---------+
| Tonny       | Butterworth  | 111692 | 1988-10-17 | 1985-01-01 | Customer Service   | d009    |
| Marjo       | Giarratana   | 111784 | 1992-09-08 | 1988-10-17 | Customer Service   | d009    |
| Xiaobin     | Spinelli     | 111877 | 1996-01-03 | 1992-09-08 | Customer Service   | d009    |
| Yuchang     | Weedman      | 111939 | 9999-01-01 | 1996-01-03 | Customer Service   | d009    |
| DeForest    | Hagimont     | 110511 | 1992-04-25 | 1985-01-01 | Development        | d005    |
| Leon        | DasSarma     | 110567 | 9999-01-01 | 1992-04-25 | Development        | d005    |
(...)

Como você pode ver, seria um pouco inconveniente digitar essa consulta toda vez que precisar buscar uma lista de gerentes de departamento. Vamos criar uma visão para facilitar. Vou chamar a visão de “gerenciamento”.

create view management as select employees.first_name,employees.last_name,employees.emp_no,dept_manager.to_date,dept_manager.from_date,departments.dept_name,departments.dept_no from employees, dept_manager, departments where employees.emp_no = dept_manager.emp_no AND departments.dept_no = dept_manager.dept_no;

Agora, podemos simplesmente digitar select * from management;para recuperar os mesmos dados. É claro que também podemos aplicar parâmetros adicionais a isso – assim como uma tabela tradicional. Por exemplo, digamos que queremos mostrar apenas os gerentes de departamento para “Atendimento ao cliente”.

select * from management where dept_name = 'Customer Service';

Saída:

+------------+-------------+--------+------------+------------+------------------+---------+
| first_name | last_name   | emp_no | to_date    | from_date  | dept_name        | dept_no |
+------------+-------------+--------+------------+------------+------------------+---------+
| Tonny      | Butterworth | 111692 | 1988-10-17 | 1985-01-01 | Customer Service | d009    |
| Marjo      | Giarratana  | 111784 | 1992-09-08 | 1988-10-17 | Customer Service | d009    |
| Xiaobin    | Spinelli    | 111877 | 1996-01-03 | 1992-09-08 | Customer Service | d009    |
| Yuchang    | Weedman     | 111939 | 9999-01-01 | 1996-01-03 | Customer Service | d009    |
+------------+-------------+--------+------------+------------+------------------+---------+

Ou talvez nós queremos “Atendimento ao Cliente” e “Recursos Humanos”:

select * from management where dept_name = 'Customer Service' OR dept_name = 'Human Resources';

Saída:

+------------+--------------+--------+------------+------------+------------------+---------+
| first_name | last_name    | emp_no | to_date    | from_date  | dept_name        | dept_no |
+------------+--------------+--------+------------+------------+------------------+---------+
| Tonny      | Butterworth  | 111692 | 1988-10-17 | 1985-01-01 | Customer Service | d009    |
| Marjo      | Giarratana   | 111784 | 1992-09-08 | 1988-10-17 | Customer Service | d009    |
| Xiaobin    | Spinelli     | 111877 | 1996-01-03 | 1992-09-08 | Customer Service | d009    |
| Yuchang    | Weedman      | 111939 | 9999-01-01 | 1996-01-03 | Customer Service | d009    |
| Shirish    | Ossenbruggen | 110183 | 1992-03-21 | 1985-01-01 | Human Resources  | d003    |
| Karsten    | Sigstam      | 110228 | 9999-01-01 | 1992-03-21 | Human Resources  | d003    |
+------------+--------------+--------+------------+------------+------------------+---------+

Removendo uma visão

Excluir uma visão é muito simples. Semelhante a remover uma tabela, você digitaria drop view view_name;. Por exemplo, se quiséssemos excluir o named_salaries vista, o comando seria: drop view named_salaries;.