Korzystanie z widoków MySQL w Debianie 7

• Wprowadzenie
• Wymagania wstępne
• Krok pierwszy - zainstaluj serwer MySQL
• Krok drugi - Zainstaluj przykładową bazę danych
• Krok trzeci - Tworzenie, używanie i usuwanie widoków

Wprowadzenie

MySQL ma świetną funkcję znaną jako „widoki”. Widoki są przechowywane zapytania. Pomyśl o nich jako o aliasie dla długiego zapytania. W tym przewodniku pokażę Ci, jak korzystać z widoków w celu wydajniejszego organizowania danych.

Wymagania wstępne

• Serwer chmurowy Vultr SSD załadowany Debianem 7.
• Użytkownik z uprawnieniami administratora (root).
• Podstawowa znajomość Linuksa, wiersza poleceń i SSH.

Krok pierwszy - zainstaluj serwer MySQL

Instalacja MySQL na Debianie 7.x jest bardzo prosta. Po pierwsze, musimy upewnić się, że nasze źródła są aktualizowane, wykonując:

sudo apt-get update

Następnie możemy zainstalować serwer MySQL:

sudo apt-get install -y mysql-server

Zostanie wyświetlone okno dialogowe z monitem o utworzenie hasła dla użytkownika „root”. Upewnij się, że pamiętasz to hasło.

Zaostrzmy bezpieczeństwo naszej instalacji, wykonując:

sudo mysql_secure_installation

Po wykonaniu zostanie wyświetlony szereg podpowiedzi. Każda z odpowiedzi, które należy wybrać, jest wyświetlona poniżej.

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

Krok drugi - Zainstaluj przykładową bazę danych

W tym momencie nie mamy danych na serwerze, z którymi można by eksperymentować. W tym samouczku będziemy korzystać z bazy danych pracowników, ponieważ jest ona łatwa w obsłudze i swobodnie dostępna ze strony MySQL.

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

Musimy zainstalować bzip2, abyśmy mogli wyodrębnić plik.

sudo apt-get install bzip2

Wyodrębnij bazę danych. Plik jest dość duży, więc może to chwilę potrwać.

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

Po rozpakowaniu pliku pojawi się folder zatytułowany employees_db. Musimy przejść do tego katalogu, aby zainstalować bazę danych.

cd employees_db 
ls -l

Dane wyjściowe będą wyglądać następująco:

-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

Wykonaj następujące polecenie, aby połączyć się z serwerem MySQL, utworzyć bazę danych i zaimportować dane:

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

Pojawi się monit o podanie hasła roota. To jest hasło ustawione w kroku pierwszym.

Ponieważ baza danych jest dość duża, pełny import danych zajmie od 1 do 3 minut. Jeśli wszystko zostało wykonane poprawnie, zobaczysz następujące dane wyjściowe.

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

Teraz możemy zalogować się do MySQL i wyświetlić zaimportowane dane.

sudo mysql -h localhost -u root -p

Wprowadź hasło roota ustawione w poprzedniej sekcji.

Sprawdź listę baz danych naszej nowo utworzonej bazy danych pracowników .

show databases;

Dane wyjściowe będą wyglądać następująco:

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

Użyjmy bazy danych pracowników .

use employees;

Sprawdź zawarte w nim tabele.

show tables;

Spowoduje to:

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

Krok trzeci - Tworzenie, używanie i usuwanie widoków

W tym kroku nauczysz się tworzyć i korzystać z widoków. Ten krok podzieliłem na mniejsze sekcje w celu dopasowania danych i połączenia danych w celu organizacji. Czas zacząć interakcję z naszymi danymi testowymi.

Scalanie / dopasowanie danych

Poniżej mam zapytanie, które wyświetla wszystkich pracowników, którzy mają roczne wynagrodzenie równe lub wyższe niż 50 000 $.

select * from salaries where salary >= 50000;

Wyjście (obcięte):

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

Jak widać, wyświetla tylko liczby pracowników. Może to być uciążliwe, gdy próbuje się szybko zidentyfikować pracownika. Na szczęście możemy stworzyć widok, który będzie przechowywał dość długie zapytanie, które może dopasować liczby pracowników do nazwisk pracowników, pobierając i dopasowując dane z wielu tabel. Zapytanie pokazano poniżej.

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;

Zauważ, jak pominąłem >= 50000zapytanie. Będziemy używać tej wartości po utworzeniu naszego widoku.

Aby utworzyć widok, po prostu dołączamy create view view_name asdo zapytania. W takim przypadku utworzę widok o nazwie nazwane_salaria .

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;

Wyświetlamy dane z widoku w taki sam sposób, jak wyświetlamy dane z tabeli.

select * from named_salaries

Jeśli widok został poprawnie utworzony, zobaczysz następujące dane wyjściowe (dane zostały obcięte):

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

Ponieważ możemy wchodzić w interakcje z widokami w taki sam sposób, jak możemy wchodzić w interakcje z tabelą, możliwe jest pobranie >= 50000pierwotnego zapytania i zastosowanie go do widoku.

select * from named_salaries where salary >= 50000;

Wyjście (obcięte):

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

Jak widać, zapytanie potraktowało widok tak jak tradycyjny stół.

Użyjmy widoku w innym przykładzie. Poniżej mam dość długie zapytanie, które zawiera listę kierowników działów, ich imiona / nazwiska, numery pracowników, nazwy działów i numery działów. Kwerenda łączy dane z kilku różnych tabel.

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;

Wyjście (obcięte):

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

Jak widać, wpisywanie tego zapytania za każdym razem, gdy trzeba pobrać listę kierowników działów, byłoby nieco niewygodne. Utwórzmy widok, aby to ułatwić. Nazywam widok „zarządzanie”.

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;

Teraz możemy po prostu wpisać, select * from management;aby pobrać te same dane. Oczywiście możemy do tego zastosować dodatkowe parametry - podobnie jak tradycyjny stół. Powiedzmy na przykład, że chcieliśmy pokazywać tylko kierownikom działów w dziale „Obsługa klienta”.

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

Wynik:

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

A może chcemy „Obsługi klienta” i „Zasobów ludzkich”:

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

Wynik:

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

Usuwanie widoku

Usunięcie widoku jest bardzo proste. Podobnie do usuwania tabeli, należy wpisać drop view view_name;. Na przykład, jeśli chcemy usunąć named_salaries pogląd, komenda będzie: drop view named_salaries;.