Χρήση MySQL Views στο Debian 7

Εισαγωγή

Η MySQL έχει ένα εξαιρετικό χαρακτηριστικό γνωστό ως "προβολές". Οι προβολές είναι αποθηκευμένα ερωτήματα. Σκεφτείτε τα ως ψευδώνυμο για μια κατά τα άλλα μεγάλη ερώτηση. Σε αυτόν τον οδηγό, θα σας δείξω πώς να χρησιμοποιείτε τις προβολές για να οργανώνετε τα δεδομένα πιο αποτελεσματικά.

Προαπαιτούμενα

• Ένας διακομιστής νέφους Vultr SSD φορτωμένος με Debian 7.
• Χρήστης με δικαιώματα διαχειριστή (root).
• Βασικές γνώσεις Linux, γραμμής εντολών και SSH.

Βήμα πρώτο - Εγκατάσταση διακομιστή MySQL

Η εγκατάσταση της MySQL στο Debian 7.x είναι πολύ απλή. Αρχικά, πρέπει να διασφαλίσουμε ότι οι πηγές μας ενημερώνονται εκτελώντας:

sudo apt-get update

Στη συνέχεια, μπορούμε να εγκαταστήσουμε τον διακομιστή MySQL:

sudo apt-get install -y mysql-server

Θα εμφανιστεί ένα παράθυρο διαλόγου που θα σας ζητήσει να δημιουργήσετε έναν κωδικό πρόσβασης για τον χρήστη "root". Βεβαιωθείτε ότι θυμάστε αυτόν τον κωδικό πρόσβασης.

Ας σκληρύνουμε την ασφάλεια της εγκατάστασής μας εκτελώντας:

sudo mysql_secure_installation

Μετά την εκτέλεση, θα εμφανιστεί μια σειρά προτροπών. Κάθε μία από τις απαντήσεις που πρέπει να επιλέξετε εμφανίζεται παρακάτω.

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

Βήμα δεύτερο - Εγκαταστήστε το δείγμα βάσης δεδομένων

Σε αυτό το σημείο, δεν έχουμε δεδομένα στον διακομιστή για να πειραματιστούμε. Για αυτό το σεμινάριο, θα χρησιμοποιήσουμε τη βάση δεδομένων των εργαζομένων, καθώς είναι εύκολη η εργασία και δωρεάν διαθέσιμη από τον ιστότοπο της MySQL.

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

Πρέπει να bzip2κάνουμε εγκατάσταση για να μπορέσουμε να εξαγάγουμε το αρχείο.

sudo apt-get install bzip2

Εξαγωγή της βάσης δεδομένων. Το αρχείο είναι αρκετά μεγάλο, επομένως μπορεί να χρειαστούν μερικά λεπτά.

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

Μόλις εξαχθεί το αρχείο, θα έχετε έναν φάκελο με τίτλο employees_db. Πρέπει να πλοηγηθούμε σε αυτόν τον κατάλογο για να εγκαταστήσουμε τη βάση δεδομένων.

cd employees_db 
ls -l

Η έξοδος θα μοιάζει με αυτό:

-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

Εκτελέστε την ακόλουθη εντολή για να συνδεθείτε στον διακομιστή MySQL, να δημιουργήσετε τη βάση δεδομένων και να εισαγάγετε τα δεδομένα:

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

Θα εμφανιστεί ένα μήνυμα που θα σας ζητά τον κωδικό πρόσβασης root. Αυτός είναι ο κωδικός πρόσβασης που ορίσατε στο πρώτο βήμα.

Δεδομένου ότι η βάση δεδομένων είναι αρκετά μεγάλη, θα χρειαστούν πιθανώς από 1-3 λεπτά για την πλήρη εισαγωγή των δεδομένων. Εάν όλα έγιναν σωστά, θα δείτε την ακόλουθη έξοδο.

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

Τώρα, μπορούμε να συνδεθούμε στη MySQL και να προβάλουμε τα εισαγόμενα δεδομένα.

sudo mysql -h localhost -u root -p

Εισαγάγετε τον κωδικό πρόσβασης root που ορίσατε στην προηγούμενη ενότητα.

Ελέγξτε τη λίστα των βάσεων δεδομένων για τη βάση δεδομένων εργαζομένων που δημιουργήθηκε πρόσφατα .

show databases;

Η έξοδος θα μοιάζει με αυτό:

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

Ας χρησιμοποιήσουμε τη βάση δεδομένων των εργαζομένων .

use employees;

Ελέγξτε τους πίνακες μέσα σε αυτό.

show tables;

Αυτό θα παράγει:

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

Βήμα τρίτο - Δημιουργία, χρήση και κατάργηση προβολών

Σε αυτό το βήμα, θα μάθετε να δημιουργείτε και να χρησιμοποιείτε προβολές. Έχω χωρίσει αυτό το βήμα σε μικρότερες ενότητες για αντιστοίχιση δεδομένων και συνδυασμό δεδομένων για οργάνωση. Ήρθε η ώρα να αρχίσουμε να αλληλεπιδρούμε με τα δεδομένα των δοκιμών μας.

Συγχώνευση/αντιστοιχία δεδομένων

Παρακάτω, έχω ένα ερώτημα που εμφανίζει όλους τους υπαλλήλους που έχουν ετήσιο μισθό ίσο ή μεγαλύτερο από 50.000 $.

select * from salaries where salary >= 50000;

Έξοδος (περικομμένη):

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

Όπως μπορείτε να δείτε, αυτό εμφανίζει μόνο αριθμούς υπαλλήλων. Θα μπορούσε να είναι ενοχλητικό όταν προσπαθείτε να εντοπίσετε γρήγορα έναν υπάλληλο. Ευτυχώς, μπορούμε να δημιουργήσουμε μια προβολή που θα αποθηκεύει ένα αρκετά μεγάλο ερώτημα που μπορεί να αντιστοιχίσει αριθμούς υπαλλήλων με ονόματα υπαλλήλων τραβώντας και αντιστοιχίζοντας δεδομένα από πολλούς πίνακες. Το ερώτημα φαίνεται παρακάτω.

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;

Παρατηρήστε πώς έχω παραλείψει >= 50000από το ερώτημα. Θα χρησιμοποιήσουμε αυτήν την τιμή αφού δημιουργηθεί η προβολή μας.

Για να δημιουργήσουμε την προβολή, απλώς προσαρτούμε create view view_name asστο ερώτημα. Σε αυτήν την περίπτωση, θα δημιουργήσω μια προβολή που ονομάζεται 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;

Εμφανίζουμε δεδομένα από μια προβολή με τον ίδιο τρόπο που εμφανίζουμε δεδομένα από έναν πίνακα.

select * from named_salaries

Εάν η προβολή έχει δημιουργηθεί σωστά, τότε θα δείτε την ακόλουθη έξοδο (τα δεδομένα έχουν περικοπεί):

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

Εφόσον μπορούμε να αλληλεπιδράσουμε με προβολές με τον ίδιο τρόπο που μπορούμε να αλληλεπιδράσουμε με έναν πίνακα, είναι δυνατό να πάρουμε το >= 50000από το αρχικό ερώτημα και να το εφαρμόσουμε στην προβολή.

select * from named_salaries where salary >= 50000;

Έξοδος (περικομμένη):

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

Όπως μπορείτε να δείτε, το ερώτημα αντιμετώπισε την προβολή ακριβώς όπως ένα παραδοσιακό τραπέζι.

Ας χρησιμοποιήσουμε μια προβολή σε άλλο παράδειγμα. Παρακάτω, έχω ένα αρκετά μακροσκελές ερώτημα που παραθέτει τους διευθυντές των τμημάτων, τα ονόματα/επώνυμά τους, τους αριθμούς υπαλλήλων, τα ονόματα των τμημάτων τους και τους αριθμούς τμημάτων. Το ερώτημα συγκεντρώνει δεδομένα από πολλούς διαφορετικούς πίνακες.

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;

Έξοδος (περικομμένη):

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

Όπως μπορείτε να δείτε, θα ήταν κάπως άβολο να πληκτρολογείτε αυτό το ερώτημα κάθε φορά που χρειάζεται να φέρετε μια λίστα με διευθυντές τμημάτων. Ας δημιουργήσουμε μια άποψη για να το κάνουμε πιο εύκολο. Θα ονομάσω την άποψη «διαχείριση».

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;

Τώρα, μπορούμε απλά να πληκτρολογήσουμε select * from management;για να ανακτήσουμε τα ίδια δεδομένα. Φυσικά, μπορούμε επίσης να εφαρμόσουμε πρόσθετες παραμέτρους σε αυτό - ακριβώς όπως ένα παραδοσιακό τραπέζι. Για παράδειγμα, ας πούμε ότι θέλαμε να δείξουμε μόνο τους διαχειριστές τμημάτων για την "Εξυπηρέτηση πελατών".

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

Παραγωγή:

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

Ή ίσως θέλουμε "Εξυπηρέτηση πελατών" και "Ανθρώπινο Δυναμικό":

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

Παραγωγή:

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

Αφαίρεση προβολής

Η διαγραφή μιας προβολής είναι πολύ απλή. Παρόμοια με την αφαίρεση ενός πίνακα, θα πληκτρολογήσετε drop view view_name;. Για παράδειγμα, αν θέλαμε να διαγράψουμε την προβολή named_salaries , η εντολή θα ήταν: drop view named_salaries;.