Uso de vistas MySQL en Debian 7

• Introducción
• Prerrequisitos
• Paso uno: instale el servidor MySQL
• Paso dos: instale una base de datos de muestra
• Paso tres: creación, uso y eliminación de vistas

Introducción

MySQL tiene una gran característica conocida como "vistas". Las vistas son consultas almacenadas. Piense en ellos como un alias para una consulta larga. En esta guía, le mostraré cómo usar las vistas para organizar los datos de manera más eficiente.

Prerrequisitos

• Un servidor en la nube Vultr SSD cargado con Debian 7.
• Usuario con privilegios de administrador (root).
• Conocimientos básicos de Linux, la línea de comandos y SSH.

Paso uno: instale el servidor MySQL

Instalar MySQL en Debian 7.x es muy sencillo. Primero, debemos asegurarnos de que nuestras fuentes se actualicen ejecutando:

sudo apt-get update

A continuación, podemos instalar el servidor MySQL:

sudo apt-get install -y mysql-server

Aparecerá un cuadro de diálogo que le solicitará que cree una contraseña para el usuario "root". Asegúrese de recordar esta contraseña.

Fortalezcamos la seguridad de nuestra instalación ejecutando:

sudo mysql_secure_installation

Después de la ejecución, se le presentará una serie de indicaciones. Cada una de las respuestas que debe seleccionar se muestran a continuación.

...
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...

Paso dos: instale una base de datos de muestra

En este punto, no tenemos datos sobre el servidor para experimentar. Para este tutorial, utilizaremos la base de datos de empleados , ya que es fácil de trabajar y está disponible gratuitamente en el sitio web de MySQL.

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

Necesitamos instalar bzip2para poder extraer el archivo.

sudo apt-get install bzip2

Extraer la base de datos. El archivo es bastante grande, por lo que puede demorar unos minutos.

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

Una vez que se haya extraído el archivo, tendrá una carpeta titulada employees_db. Necesitamos navegar en este directorio para instalar la base de datos.

cd employees_db 
ls -l

La salida se verá así:

-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

Ejecute el siguiente comando para conectarse al servidor MySQL, crear la base de datos e importar los datos:

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

Aparecerá un mensaje pidiéndole su contraseña de root. Esta es la contraseña que configuró en el paso uno.

Dado que la base de datos es bastante grande, probablemente tomará entre 1 y 3 minutos importar completamente los datos. Si todo se hizo correctamente, verá el siguiente resultado.

+-----------------------------+
| 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 |
+------------------+

Ahora, podemos iniciar sesión en MySQL y ver los datos importados.

sudo mysql -h localhost -u root -p

Ingrese la contraseña de root que configuró en la sección anterior.

Consulte la lista de bases de datos de nuestra base de datos de empleados recién creada .

show databases;

La salida se verá así:

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

Usemos la base de datos de empleados .

use employees;

Revisa las tablas que contiene.

show tables;

Esto generará:

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

Paso tres: creación, uso y eliminación de vistas

En este paso, aprenderá a crear y usar vistas. He dividido este paso en secciones más pequeñas para hacer coincidir los datos y combinar los datos para la organización. Es hora de comenzar a interactuar con nuestros datos de prueba.

Fusionar / unir datos

A continuación, tengo una consulta que muestra todos los empleados que tienen un salario anual igual o superior a $ 50,000.

select * from salaries where salary >= 50000;

Salida (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 puede ver, esto solo muestra los números de los empleados. Podría ser una molestia cuando se trata de identificar a un empleado rápidamente. Afortunadamente, podemos crear una vista que almacene una consulta bastante larga que pueda hacer coincidir los números de los empleados con los nombres de los empleados extrayendo y haciendo coincidir los datos de varias tablas. La consulta se muestra a continuación.

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 cómo he omitido >= 50000de la consulta. Utilizaremos este valor después de que se haya creado nuestra vista.

Para crear la vista, simplemente agregamos create view view_name asa la consulta. En este caso, crearé una vista llamada 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;

Mostramos datos de una vista de la misma manera que mostramos datos de una tabla.

select * from named_salaries

Si la vista se ha creado correctamente, verá el siguiente resultado (los datos se han truncado):

+------------+-----------+--------+--------+------------+------------+
| 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 interactuar con las vistas de la misma manera que podemos interactuar con una tabla, es posible tomar la >= 50000consulta original y aplicarla a la vista.

select * from named_salaries where salary >= 50000;

Salida (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 puede ver, la consulta ha tratado la vista como una tabla tradicional.

Usemos una vista en otro ejemplo. A continuación, tengo una consulta bastante larga que enumera los gerentes de departamento, sus nombres / apellidos, números de empleados, sus nombres de departamento y los números de departamento. La consulta reúne datos de varias tablas 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;

Salida (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 puede ver, sería un poco incómodo escribir esa consulta cada vez que necesite buscar una lista de gerentes de departamento. Creemos una vista para que sea más fácil. Voy a llamar a la vista "gestión".

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;

Ahora, simplemente podemos escribir select * from management;para recuperar los mismos datos. Por supuesto, también podemos aplicar parámetros adicionales a eso, al igual que una tabla tradicional. Por ejemplo, supongamos que solo queríamos mostrar a los gerentes de departamento el "Servicio al cliente".

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

Salida:

+------------+-------------+--------+------------+------------+------------------+---------+
| 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    |
+------------+-------------+--------+------------+------------+------------------+---------+

O tal vez queremos "Servicio al cliente" y "Recursos humanos":

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

Salida:

+------------+--------------+--------+------------+------------+------------------+---------+
| 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    |
+------------+--------------+--------+------------+------------+------------------+---------+

Eliminar una vista

Eliminar una vista es muy sencillo. Similar a eliminar una tabla, escribiría drop view view_name;. Por ejemplo, si quisiéramos eliminar la named_salaries vista, el comando sería: drop view named_salaries;.