Duke përdorur MySQL Views në Debian 7

Prezantimi

MySQL ka një veçori të shkëlqyer të njohur si "views". Pamjet janë të ruajtura pyetje. Mendoni për to si një pseudonim për një pyetje ndryshe të gjatë. Në këtë udhëzues, unë do t'ju tregoj se si të përdorni pamjet për të organizuar të dhënat në mënyrë më efikase.

Parakushtet

• Një server cloud SSD Vultr i ngarkuar me Debian 7.
• Përdorues me privilegje administratori (root).
• Njohuri bazë për Linux, linjën e komandës dhe SSH.

Hapi i parë - Instaloni serverin MySQL

Instalimi i MySQL në Debian 7.x është shumë i thjeshtë. Së pari, duhet të sigurohemi që burimet tona të përditësohen duke ekzekutuar:

sudo apt-get update

Më pas, ne mund të instalojmë serverin MySQL:

sudo apt-get install -y mysql-server

Do të shfaqet një kuti dialogu që ju kërkon të krijoni një fjalëkalim për përdoruesin "root". Sigurohuni që ta mbani mend këtë fjalëkalim.

Le të forcojmë sigurinë e instalimit tonë duke ekzekutuar:

sudo mysql_secure_installation

Pas ekzekutimit, do t'ju paraqitet një sërë kërkesash. Secila prej përgjigjeve që duhet të zgjidhni shfaqen më poshtë.

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

Hapi i dytë - Instaloni bazën e të dhënave të mostrës

Në këtë pikë, ne nuk kemi të dhëna në server për të eksperimentuar. Për këtë tutorial, ne do të përdorim bazën e të dhënave të punonjësve pasi është e lehtë për t'u punuar dhe e disponueshme falas nga faqja e internetit e MySQL.

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

Duhet të instalojmë në bzip2mënyrë që të mund ta nxjerrim skedarin.

sudo apt-get install bzip2

Ekstraktoni bazën e të dhënave. Skedari është mjaft i madh, kështu që mund të duhen disa momente.

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

Pasi të jetë nxjerrë skedari, do të keni një dosje të titulluar employees_db. Ne duhet të lundrojmë në këtë direktori për të instaluar bazën e të dhënave.

cd employees_db 
ls -l

Prodhimi do të duket si ky:

-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

Ekzekutoni komandën e mëposhtme për t'u lidhur me serverin MySQL, për të krijuar bazën e të dhënave dhe për të importuar të dhënat:

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

Do të shfaqet një kërkesë që kërkon fjalëkalimin tuaj root. Ky është fjalëkalimi që keni vendosur në hapin e parë.

Duke qenë se baza e të dhënave është mjaft e madhe, ndoshta do të duhen diku nga 1-3 minuta për të importuar plotësisht të dhënat. Nëse gjithçka është bërë si duhet, do të shihni daljen e mëposhtme.

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

Tani, ne mund të hyjmë në MySQL dhe të shohim të dhënat e importuara.

sudo mysql -h localhost -u root -p

Futni fjalëkalimin rrënjë që keni vendosur në seksionin e mëparshëm.

Kontrolloni listën e bazave të të dhënave për bazën e të dhënave të punonjësve tanë të krijuar rishtazi .

show databases;

Prodhimi do të duket si ky:

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

Le të përdorim bazën e të dhënave të punonjësve .

use employees;

Kontrolloni tabelat brenda tij.

show tables;

Kjo do të nxjerrë:

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

Hapi i tretë - Krijimi, përdorimi dhe heqja e pamjeve

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.

Për të krijuar pamjen, ne thjesht i bashkojmë create view view_name aspyetjes. Në këtë rast, unë do të krijoj një pamje të quajtur 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;

Ne shfaqim të dhëna nga një pamje në të njëjtën mënyrë që shfaqim të dhënat nga një tabelë.

select * from named_salaries

Nëse pamja është krijuar siç duhet, atëherë do të shihni daljen e mëposhtme (të dhënat janë shkurtuar):

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

Meqenëse ne mund të ndërveprojmë me pamjet në të njëjtën mënyrë që mund të ndërveprojmë me një tabelë, është e mundur të marrim >= 50000pyetjen nga pyetja origjinale dhe ta zbatojmë atë në pamje.

select * from named_salaries where salary >= 50000;

Prodhimi (i shkurtuar):

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

Siç mund ta shihni, pyetja e ka trajtuar pamjen ashtu si një tryezë tradicionale.

Le të përdorim një pamje në një shembull tjetër. Më poshtë, kam një pyetje mjaft të gjatë që liston menaxherët e departamenteve, emrat/mbiemrat e tyre, numrat e punonjësve, emrat e departamenteve të tyre dhe numrat e departamenteve. Kërkesa mbledh të dhëna nga disa tabela të ndryshme.

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;

Prodhimi (i shkurtuar):

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

Siç mund ta shihni, do të ishte disi e papërshtatshme të shkruani atë pyetje sa herë që ju duhet të merrni një listë të menaxherëve të departamenteve. Le të krijojmë një pamje për ta bërë më të lehtë. Unë do ta quaj pamjen "menaxhimi".

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;

Tani, ne thjesht mund select * from management;të shkruajmë për të tërhequr të njëjtat të dhëna. Sigurisht, ne mund të aplikojmë edhe parametra shtesë për këtë - ashtu si një tabelë tradicionale. Për shembull, le të themi se ne donim të tregonim vetëm menaxherët e departamenteve për "Shërbimin e Klientit".

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

Prodhimi:

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

Ose mbase duam "Shërbimin e Klientit" dhe "Burimet Njerëzore":

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

Prodhimi:

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

Heqja e një pamjeje

Fshirja e një pamjeje është shumë e thjeshtë. Ngjashëm me heqjen e një tabele, ju do të shkruani drop view view_name;. Për shembull, nëse do të donim të fshinim pamjen named_salaries , komanda do të ishte: drop view named_salaries;.