Menggunakan Tampilan MySQL pada Debian 7

• pengantar
• Prasyarat
• Langkah satu - Instal server MySQL
• Langkah dua - Instal basis data sampel
• Langkah tiga - Membuat, menggunakan, dan menghapus tampilan

pengantar

MySQL memiliki fitur hebat yang dikenal sebagai "tampilan". Tampilan adalah kueri yang disimpan. Pikirkan mereka sebagai alias untuk permintaan yang panjang. Dalam panduan ini, saya akan menunjukkan kepada Anda cara menggunakan tampilan untuk mengatur data lebih efisien.

Prasyarat

• Server cloud Vultr SSD dimuat dengan Debian 7.
• Pengguna dengan hak administrator (root).
• Pengetahuan dasar tentang Linux, baris perintah, dan SSH.

Langkah satu - Instal server MySQL

Menginstal MySQL pada Debian 7.x sangat mudah. Pertama, kami perlu memastikan bahwa sumber kami diperbarui dengan menjalankan:

sudo apt-get update

Selanjutnya, kita dapat menginstal server MySQL:

sudo apt-get install -y mysql-server

Kotak dialog akan ditampilkan meminta Anda untuk membuat kata sandi untuk pengguna "root". Pastikan Anda mengingat kata sandi ini.

Mari kita tegaskan keamanan instalasi kami dengan mengeksekusi:

sudo mysql_secure_installation

Setelah eksekusi, Anda akan diberi serangkaian konfirmasi. Setiap jawaban yang harus Anda pilih ditampilkan di bawah.

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

Langkah dua - Instal basis data sampel

Pada titik ini, kami tidak memiliki data di server untuk percobaan. Untuk tutorial ini, kita akan menggunakan database karyawan karena mudah digunakan dan tersedia secara bebas dari situs web MySQL.

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

Kita perlu menginstal bzip2agar kita dapat mengekstrak file.

sudo apt-get install bzip2

Ekstrak basis data. File ini cukup besar, jadi mungkin perlu beberapa saat.

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

Setelah file diekstraksi, Anda akan memiliki folder berjudul employees_db. Kita perlu menavigasi ke direktori ini untuk menginstal database.

cd employees_db 
ls -l

Outputnya akan terlihat seperti ini:

-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

Jalankan perintah berikut untuk menyambung ke server MySQL, membuat database, dan mengimpor data:

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

Sebuah prompt akan muncul menanyakan kata sandi root Anda. Ini adalah kata sandi yang Anda tentukan di langkah pertama.

Karena datanya cukup besar, mungkin perlu 1-3 menit untuk mengimpor data sepenuhnya. Jika semuanya dilakukan dengan benar, Anda akan melihat output berikut.

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

Sekarang, kita bisa masuk ke MySQL dan melihat data yang diimpor.

sudo mysql -h localhost -u root -p

Masukkan kata sandi root yang Anda atur di bagian sebelumnya.

Periksa daftar database untuk database karyawan kami yang baru dibuat .

show databases;

Outputnya akan terlihat seperti ini:

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

Mari kita gunakan database karyawan .

use employees;

Periksa tabel di dalamnya.

show tables;

Ini akan menampilkan:

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

Langkah tiga - Membuat, menggunakan, dan menghapus tampilan

Pada langkah ini, Anda akan belajar membuat dan menggunakan tampilan. Saya telah memecah langkah ini menjadi bagian yang lebih kecil untuk mencocokkan data, dan menggabungkan data untuk organisasi. Sudah waktunya untuk mulai berinteraksi dengan data pengujian kami.

Menggabungkan / mencocokkan data

Di bawah, saya memiliki permintaan yang menampilkan semua karyawan yang memiliki gaji tahunan sama dengan, atau lebih dari $ 50.000.

select * from salaries where salary >= 50000;

Output (terpotong):

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

Seperti yang Anda lihat, ini hanya menampilkan nomor karyawan. Ini bisa menjadi gangguan ketika mencoba mengidentifikasi karyawan dengan cepat. Untungnya, kita dapat membuat tampilan yang akan menyimpan kueri yang cukup panjang yang dapat mencocokkan nomor karyawan dengan nama karyawan dengan menarik dan mencocokkan data dari beberapa tabel. Kueri ditampilkan di bawah ini.

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;

Perhatikan bagaimana saya menghilangkan >= 50000dari kueri. Kami akan menggunakan nilai ini setelah tampilan kami dibuat.

Untuk membuat tampilan, kita cukup menambahkan create view view_name askueri. Dalam hal ini, saya akan membuat tampilan bernama 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;

Kami menampilkan data dari tampilan dengan cara yang sama seperti kami menampilkan data dari tabel.

select * from named_salaries

Jika tampilan telah dibuat dengan benar, maka Anda akan melihat output berikut (data telah terpotong):

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

Karena kita dapat berinteraksi dengan tampilan dengan cara yang sama seperti kita dapat berinteraksi dengan sebuah tabel, dimungkinkan untuk mengambil >= 50000dari kueri asli dan menerapkannya ke tampilan.

select * from named_salaries where salary >= 50000;

Output (terpotong):

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

Seperti yang Anda lihat, kueri telah memperlakukan tampilan seperti tabel tradisional.

Mari kita gunakan tampilan dalam contoh lain. Di bawah ini, saya memiliki pertanyaan yang cukup panjang yang mencantumkan manajer departemen, nama depan / belakang, nomor karyawan, nama departemen mereka, dan nomor departemen. Kueri mengumpulkan data dari beberapa tabel yang berbeda.

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 (terpotong):

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

Seperti yang Anda lihat, akan agak merepotkan untuk mengetik kueri itu setiap kali Anda perlu mengambil daftar manajer departemen. Mari kita buat tampilan untuk membuatnya lebih mudah. Saya akan memanggil tampilan "manajemen".

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;

Sekarang, kita cukup mengetik select * from management;untuk mengambil data yang sama. Tentu saja, kita juga dapat menerapkan parameter tambahan untuk itu - seperti tabel tradisional. Misalnya, kami hanya ingin menunjukkan manajer departemen untuk "Layanan Pelanggan".

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

Keluaran:

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

Atau mungkin kita ingin "Layanan Pelanggan" dan "Sumber Daya Manusia":

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

Keluaran:

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

Menghapus tampilan

Menghapus tampilan sangat mudah. Mirip dengan menghapus tabel, Anda akan mengetik drop view view_name;. Sebagai contoh, jika kita ingin menghapus named_salaries pandangan, perintah akan: drop view named_salaries;.