Pelajari Cara Menggunakan Beberapa Fungsi MySQL dan MariaDB - Bagian 2


Ini adalah bagian kedua dari seri 2 artikel tentang esensi perintah MariaDB/MySQL. Silakan merujuk ke artikel kami sebelumnya tentang topik ini sebelum melanjutkan.

  1. Pelajari Dasar-dasar MySQL/MariaDB untuk Pemula – Bagian 1

Pada bagian kedua dari seri pemula MySQL/MariaDB ini, kami akan menjelaskan cara membatasi jumlah baris yang dikembalikan oleh kueri SELECT, dan cara mengurutkan kumpulan hasil berdasarkan kondisi tertentu.

Selain itu, kita akan mempelajari cara mengelompokkan catatan dan melakukan manipulasi matematika dasar pada bidang numerik. Semua ini akan membantu kita membuat skrip SQL yang dapat kita gunakan untuk menghasilkan laporan yang bermanfaat.

Prasyarat

Untuk memulai, silakan ikuti langkah-langkah berikut:

1. Unduh contoh database karyawan, yang mencakup enam tabel yang terdiri dari total 4 juta catatan.

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

2. Masukkan perintah MariaDB dan buat database bernama karyawan:

mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE employees;
Query OK, 1 row affected (0.00 sec)

3. Impor ke server MariaDB Anda sebagai berikut:

MariaDB [(none)]> source employees.sql

Tunggu 1-2 menit hingga database sampel dimuat (ingatlah bahwa kita sedang membicarakan 4 juta data di sini!).

4. Verifikasi bahwa database telah diimpor dengan benar dengan membuat daftar tabelnya:

MariaDB [employees]> USE employees;
Database changed
MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.02 sec)

5. Buat akun khusus untuk digunakan dengan database karyawan (silakan pilih nama akun dan kata sandi lain):

MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)

MariaDB [employees]> GRANT ALL PRIVILEGES ON  employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.02 sec)

MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> exit
Bye

Sekarang login sebagai pengguna empadmin ke prompt Mariadb.

mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Pastikan semua langkah yang diuraikan pada gambar di atas telah diselesaikan sebelum melanjutkan.

Mengurutkan dan Membatasi Jumlah Baris dalam Kumpulan Hasil

Tabel gaji berisi semua pendapatan setiap karyawan dengan tanggal mulai dan berakhir. Kami mungkin ingin melihat gaji emp_no=10001 dari waktu ke waktu. Ini akan membantu menjawab pertanyaan-pertanyaan berikut:

  1. Apakah dia mendapat kenaikan gaji?
  2. Jika ya, kapan?

Jalankan kueri berikut untuk mencari tahu:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;
+--------+--------+------------+------------+
| 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 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
+--------+--------+------------+------------+
17 rows in set (0.03 sec)

Sekarang bagaimana jika kita perlu melihat 5 kenaikan gaji terbaru? Kami dapat melakukan ORDER BY from_date DESC. Kata kunci DESC menunjukkan bahwa kita ingin mengurutkan kumpulan hasil dalam urutan menurun.

Selain itu, LIMIT 5 memungkinkan kita mengembalikan hanya 5 baris teratas dalam kumpulan hasil:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)

Anda juga dapat menggunakan ORDER BY dengan beberapa kolom. Misalnya, kueri berikut akan mengurutkan kumpulan hasil berdasarkan tanggal lahir karyawan dalam bentuk menaik (default) dan kemudian berdasarkan nama belakang dalam bentuk menurun berdasarkan abjad:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, gender AS Gender,  hire_date AS "Hire date" FROM employees ORDER BY birth_date, last_name DESC LIMIT 10;
+--------------------+--------+------------+
| Name               | Gender | Hire date  |
+--------------------+--------+------------+
| Whitcomb, Kiyokazu | M      | 1988-07-26 |
| Schaad, Ronghao    | M      | 1988-07-10 |
| Remmele, Supot     | M      | 1989-01-27 |
| Pocchiola, Jouni   | M      | 1985-03-10 |
| Kuzuoka, Eishiro   | M      | 1992-02-12 |
| Decaestecker, Moni | M      | 1986-10-06 |
| Wiegley, Mircea    | M      | 1985-07-18 |
| Vendrig, Sachar    | M      | 1985-11-04 |
| Tsukuda, Cedric    | F      | 1993-12-12 |
| Tischendorf, Percy | M      | 1986-11-10 |
+--------------------+--------+------------+
10 rows in set (0.31 sec)

Anda dapat melihat informasi selengkapnya tentang LIMIT di sini.

Pengelompokan Record/MAX, MIN, AVG, dan ROUND

Seperti yang kami sebutkan sebelumnya, tabel gaji berisi pendapatan setiap karyawan dari waktu ke waktu. Selain LIMIT, kita dapat menggunakan kata kunci MAX dan MIN untuk menentukan kapan jumlah maksimum dan minimum karyawan dipekerjakan:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Max. salary |
+-----------------+-------------+
| Facello, Georgi |       88958 |
| Simmel, Bezalel |       72527 |
| Bamford, Parto  |       43699 |
+-----------------+-------------+
3 rows in set (0.02 sec)

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Min. salary |
+-----------------+-------------+
| Facello, Georgi |       60117 |
| Simmel, Bezalel |       65828 |
| Bamford, Parto  |       40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)

Berdasarkan kumpulan hasil di atas, dapatkah Anda menebak apa yang akan dihasilkan oleh kueri di bawah ini?

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Avg. salary |
+-----------------+-------------+
| Facello, Georgi |    75388.94 |
| Simmel, Bezalel |    68854.50 |
| Bamford, Parto  |    43030.29 |
+-----------------+-------------+
3 rows in set (0.01 sec)

Jika Anda setuju bahwa ini akan mengembalikan gaji rata-rata (seperti yang ditentukan oleh AVG) dari waktu ke waktu yang dibulatkan menjadi 2 desimal (seperti yang ditunjukkan oleh ROUND), Anda benar.

Jika kita ingin melihat jumlah gaji yang dikelompokkan berdasarkan karyawan dan mengembalikan 5 teratas, kita dapat menggunakan kueri berikut:

MariaDB [employees]> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;
+--------+---------+
| emp_no | Salary  |
+--------+---------+
| 109334 | 2553036 |
|  43624 | 2492873 |
|  66793 | 2383923 |
| 237542 | 2381119 |
|  47978 | 2374024 |
+--------+---------+
5 rows in set (2.22 sec)

Dalam query di atas, gaji dikelompokkan berdasarkan karyawan dan kemudian dilakukan penjumlahan.

Menyatukan semuanya

Untungnya, kita tidak perlu menjalankan kueri demi kueri untuk menghasilkan laporan. Sebagai gantinya, kita dapat membuat skrip dengan serangkaian perintah SQL untuk mengembalikan semua rangkaian hasil yang diperlukan.

Setelah kami menjalankan skrip, skrip akan mengembalikan informasi yang diperlukan tanpa intervensi lebih lanjut dari pihak kami. Misalnya, mari kita buat file bernama maxminavg.sql di direktori kerja saat ini dengan konten berikut:

--Select database
USE employees;
--Calculate maximum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate minimum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate averages, round to 2 decimal places
SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;

Baris yang dimulai dengan dua tanda hubung akan diabaikan, dan masing-masing kueri dijalankan satu demi satu. Kita dapat menjalankan skrip ini baik dari baris perintah Linux:

mysql -u empadmin -p < maxminavg.sql
Enter password: 
Name	Max. salary
Facello, Georgi	88958
Simmel, Bezalel	72527
Bamford, Parto	43699
Name	Min. salary
Facello, Georgi	60117
Simmel, Bezalel	65828
Bamford, Parto	40006
Name	Avg. salary
Facello, Georgi	75388.94
Simmel, Bezalel	68854.50
Bamford, Parto	43030.29

atau dari perintah MariaDB:

mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> source maxminavg.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Ringkasan

Dalam artikel ini kami telah menjelaskan cara menggunakan beberapa fungsi MariaDB untuk menyempurnakan rangkaian hasil yang dikembalikan oleh pernyataan SELECT. Setelah ditentukan, beberapa kueri individual dapat dimasukkan ke dalam skrip untuk mengeksekusinya dengan lebih mudah dan mengurangi risiko kesalahan manusia.

Apakah Anda memiliki pertanyaan atau saran tentang artikel ini? Jangan ragu untuk mengirimi kami catatan menggunakan formulir komentar di bawah. Kami menantikan kabar dari Anda!