Using MySQL Views on Debian 7

Introduction

MySQL has a great feature known as "views". Views are stored queries. Think of them as an alias for an otherwise long query. In this guide, I will be showing you how to use views to organize data more efficiently.

Prerequisites

• A Vultr SSD cloud server loaded with Debian 7.
• User with administrator (root) privileges.
• Basic knowledge of Linux, the command line, and SSH.

Step one - Install MySQL server

Installing MySQL on Debian 7.x is very straightforward. First, we need to ensure that our sources are updated by executing:

sudo apt-get update

Next, we can install the MySQL server:

sudo apt-get install -y mysql-server

A dialog box will be displayed prompting you to create a password for the "root" user. Be sure that you remember this password.

Let's harden the security of our installation by executing:

sudo mysql_secure_installation

After execution, you will be presented with a series of prompts. Each of the answers that you should select are displayed below.

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

Step two - Install sample database

At this point, we have no data on the server to experiment with. For this tutorial, we will be using the employees database as it is easy to work with and freely available from MySQL's website.

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

We need to install bzip2 so that we can extract the file.

sudo apt-get install bzip2

Extract the database. The file is fairly large, so it may take a few moments.

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

Once the file has been extracted, you will have a folder titled employees_db. We need to navigate into this directory to install the database.

cd employees_db 
ls -l

The output will look like this:

-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 the following command to connect to the MySQL server, create the database, and import the data:

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

Apareixerà un missatge que us demanarà la vostra contrasenya d'arrel. Aquesta és la contrasenya que heu establert al primer pas.

Com que la base de dades és bastant gran, probablement trigarà entre 1 i 3 minuts a importar completament les dades. Si tot s'ha fet correctament, veureu la següent sortida.

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

Ara, podem iniciar sessió a MySQL i veure les dades importades.

sudo mysql -h localhost -u root -p

Introduïu la contrasenya d' arrel que heu establert a la secció anterior.

Consulteu la llista de bases de dades de la nostra base de dades d' empleats de nova creació .

show databases;

La sortida tindrà aquest aspecte:

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

Utilitzem la base de dades dels empleats .

use employees;

Comproveu les taules que hi ha dins.

show tables;

Això sortirà:

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

Pas tres: crear, utilitzar i eliminar vistes

In this step, you will learn to create and use views. I have broken up this step into smaller sections for matching data, and combining data for organization. It's time to start interacting with our test data.

Merging/matching data

Below, I have a query which displays all employees who have a yearly salary equal to, or greater than $50,000.

select * from salaries where salary >= 50000;

Output (truncated):

+--------+--------+------------+------------+
| 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 |
(...)

As you can see, this only displays employee numbers. It could be a nuisance when trying to identify an employee quickly. Luckily, we can create a view that will store a fairly lengthy query that can match employee numbers to employee names by pulling and matching data from multiple tables. The query is shown below.

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;

Notice how I have omitted >= 50000 from the query. We will be using this value after our view has been created.

To create the view, we simply append create view view_name as to the query. In this case, I will be creating a view called 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;

We display data from a view in the same fashion that we display data from a table.

select * from named_salaries

If the view has been created properly, then you will see the following output (data has been truncated):

+------------+-----------+--------+--------+------------+------------+
| 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 |
(...)

Since we can interact with views in the same way that we can interact with a table, it is possible to take the >= 50000 from the original query and apply it to the view.

select * from named_salaries where salary >= 50000;

Output (truncated):

+------------+-----------+--------+--------+------------+------------+
| 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 |
(...)
+------------+-----------+--------+--------+------------+------------+

As you can see, the query has treated the view just like a traditional table.

Let's use a view in another example. Below, I have a fairly lengthy query that lists the department managers, their first/last names, employee numbers, their department names, and the department numbers. The query pulls together data from several different tables.

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;

Output (truncated):

+-------------+--------------+--------+------------+------------+--------------------+---------+
| 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    |
(...)

As you can see, it would be somewhat inconvenient to type in that query every time you need to fetch a list of department managers. Let's create a view to make it easier. I'm going to call the view "management".

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;

Now, we can simply type select * from management; to retrieve the same data. Of course, we can also apply additional parameters to that - just like a traditional table. For example, say we wanted to only show the departments managers for "Customer Service".

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

Output:

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

Or maybe we want "Customer Service" and "Human Resources":

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

Output:

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

Removing a view

Deleting a view is very straightforward. Similar to removing a table, you would type drop view view_name;. For example, if we wanted to delete the named_salaries view, the command would be: drop view named_salaries;.