Usando vistas MySQL no Debian 7

• Introdução
• Pré-requisitos
• Etapa 1 - Instale o servidor MySQL
• Etapa 2 - Instalar o banco de dados de amostra
• Etapa 3 - Criando, usando e removendo visualizações

Introdução

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

Pré-requisitos

• Um servidor de nuvem Vultr SSD carregado com o Debian 7.
• Usuário com privilégios de administrador (raiz).
• Conhecimentos básicos de Linux, linha de comando e SSH.

Etapa 1 - Instale o servidor MySQL

A instalação do 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 dessa senha.

Vamos reforçar a segurança de nossa instalação executando:

sudo mysql_secure_installation

Após a execução, você verá uma série de prompts. Cada uma das respostas que você deve selecionar são exibidas 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 ponto, não temos dados no servidor para experimentar. Para este tutorial, usaremos o banco de dados dos funcionários , pois é fácil trabalhar e disponibilizar gratuitamente no site do 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 é bastante grande, por isso pode levar alguns instantes.

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

Depois que o arquivo for extraído, você terá uma pasta intitulada employees_db. Precisamos navegar neste diretório para instalar o banco de dados.

cd employees_db 
ls -l

A saída será 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-se ao servidor MySQL, criar o banco de dados e importar os dados:

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

Um prompt aparecerá solicitando sua senha root. Essa é a senha que você definiu na etapa um.

Como o banco de dados é bastante grande, provavelmente levará de 1 a 3 minutos para importar completamente 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 fazer login no MySQL e visualizar os dados importados.

sudo mysql -h localhost -u root -p

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

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

show databases;

A saída será assim:

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

Vamos usar o banco de dados dos funcionários .

use employees;

Verifique as tabelas dentro dele.

show tables;

Isso 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. Dividi esta etapa em seções menores para correspondência de dados e combinação de 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 com 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 os números dos funcionários. Pode ser um incômodo ao tentar identificar um funcionário rapidamente. Felizmente, podemos criar uma exibição que armazena uma consulta bastante longa que pode corresponder números de funcionários a nomes de 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. Usaremos esse valor após a criação da nossa visão.

Para criar a visualização, basta anexar create view view_name asà consulta. Nesse caso, vou criar uma exibiçã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 exibiçã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 visualizações da mesma maneira que podemos interagir com uma tabela, é possível pegar a >= 50000consulta original e aplicá-la à visualizaçã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 nomes / sobrenomes, números de funcionários, nomes de departamentos e números de departamentos. 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 você 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. Obviamente, também podemos aplicar parâmetros adicionais a isso - como uma tabela tradicional. Por exemplo, digamos que queremos mostrar apenas aos gerentes de departamento o "Atendimento ao Cliente".

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

Resultado:

+------------+-------------+--------+------------+------------+------------------+---------+
| 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 desejemos "Atendimento ao cliente" e "Recursos humanos":

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

Resultado:

+------------+--------------+--------+------------+------------+------------------+---------+
| 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 Visualização

A exclusão de uma visualização é muito direta. Semelhante à remoção de 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;.