MySQL ve MariaDB'nin Çeşitli İşlevlerinin Nasıl Kullanılacağını Öğrenin - Bölüm 2
Bu, MariaDB/MySQL komutlarının temellerini anlatan 2 makalelik serinin ikinci bölümüdür. Devam etmeden önce lütfen bu konuyla ilgili önceki makalemize bakın.
- Yeni Başlayanlar İçin MySQL/MariaDB Temellerini Öğrenin – Bölüm 1
MySQL/MariaDB başlangıç serisinin bu ikinci bölümünde, SELECT sorgusunun döndürdüğü satır sayısının nasıl sınırlanacağını ve sonuç kümesinin belirli bir koşula göre nasıl sıralanacağını açıklayacağız.
Ayrıca kayıtları nasıl gruplandıracağımızı ve sayısal alanlarda temel matematiksel işlemleri nasıl yapacağımızı öğreneceğiz. Bütün bunlar, yararlı raporlar üretmek için kullanabileceğimiz bir SQL betiği oluşturmamıza yardımcı olacaktır.
Önkoşullar
Başlamak için lütfen şu adımları izleyin:
1. Toplamda 4 milyon kayıttan oluşan altı tablo içeren çalışanlar örnek veritabanını indirin.
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. MariaDB istemine girin ve çalışanlar adında bir veritabanı oluşturun:
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. Bunu MariaDB sunucunuza aşağıdaki şekilde aktarın:
MariaDB [(none)]> source employees.sql
Örnek veritabanı yüklenene kadar 1-2 dakika bekleyin (burada 4 milyon kayıttan bahsettiğimizi unutmayın!).
4. Tablolarını listeleyerek veritabanının doğru şekilde içe aktarıldığını doğrulayın:
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. Çalışanlar veritabanıyla kullanılacak özel bir hesap oluşturun (başka bir hesap adı ve şifre seçmekten çekinmeyin):
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
Şimdi Mariadb istemine empadmin kullanıcısı olarak giriş yapın.
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
Devam etmeden önce yukarıdaki resimde özetlenen tüm adımların tamamlandığından emin olun.
Sonuç Kümesindeki Satır Sayısını Sıralama ve Sınırlama
Maaş tablosu her çalışanın tüm gelirlerini başlangıç ve bitiş tarihleriyle birlikte içerir. Zaman içinde emp_no=10001 maaşlarını görüntülemek isteyebiliriz. Bu, aşağıdaki soruların yanıtlanmasına yardımcı olacaktır:
- Herhangi bir zam aldı mı?
- Eğer öyleyse, ne zaman?
Bunu öğrenmek için aşağıdaki sorguyu yürütün:
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)
Peki ya son 5 zammı görmemiz gerekirse? ORDER BY from_date DESC işlemini yapabiliriz. DESC anahtar sözcüğü, sonuç kümesini azalan düzende sıralamak istediğimizi belirtir.
Ayrıca LIMIT 5, sonuç kümesinde yalnızca en üstteki 5 satırı döndürmemize olanak tanır:
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)
ORDER BY seçeneğini birden fazla alanla da kullanabilirsiniz. Örneğin, aşağıdaki sorgu, sonuç kümesini çalışanın doğum tarihine göre artan biçimde (varsayılan) ve ardından alfabetik olarak azalan biçimde soyadlarına göre sıralayacaktır:
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)
LIMIT hakkında daha fazla bilgiyi burada görebilirsiniz.
Kayıtları Gruplandırma/MAX, MIN, AVG ve ROUND
Daha önce de belirttiğimiz gibi maaşlar tablosu her çalışanın zaman içindeki gelirini içerir. Maksimum ve minimum sayıda çalışanın ne zaman işe alındığını belirlemek için LIMIT'in yanı sıra MAX ve MIN anahtar kelimelerini de kullanabiliriz:
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)
Yukarıdaki sonuç kümelerine dayanarak aşağıdaki sorgunun ne getireceğini tahmin edebilir misiniz?
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)
Zaman içindeki ortalama maaşı (AVG ile belirtildiği üzere) 2 ondalığa yuvarlanmış olarak (YUVARLA ile belirtildiği şekilde) döndüreceğini kabul ediyorsanız, haklısınız.
Çalışanlara göre gruplandırılmış maaşların toplamını görüntülemek ve ilk 5'i döndürmek istiyorsak aşağıdaki sorguyu kullanabiliriz:
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)
Yukarıdaki sorguda maaşlar çalışan bazında gruplandırılıp toplama işlemi yapılmaktadır.
Hepsini Bir Araya Getirmek
Neyse ki bir rapor oluşturmak için sorgu üstüne sorgu çalıştırmamıza gerek yok. Bunun yerine, gerekli tüm sonuç kümelerini döndürmek için bir dizi SQL komutu içeren bir komut dosyası oluşturabiliriz.
Komut dosyasını çalıştırdığımızda, bizim tarafımızdan başka bir müdahaleye gerek kalmadan gerekli bilgileri döndürecektir. Örneğin, mevcut çalışma dizininde aşağıdaki içeriğe sahip maxminavg.sql adında bir dosya oluşturalım:
--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;
İki tireyle başlayan satırlar dikkate alınmaz ve bireysel sorgular birbiri ardına yürütülür. Bu betiği Linux komut satırından çalıştırabiliriz:
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
veya MariaDB isteminden:
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
Özet
Bu makalede, SELECT ifadelerinin döndürdüğü sonuç kümelerini hassaslaştırmak için çeşitli MariaDB işlevlerinin nasıl kullanılacağını açıkladık. Tanımlandıktan sonra, daha kolay yürütmek ve insan hatası riskini azaltmak için bir komut dosyasına birden çok ayrı sorgu eklenebilir.
Bu makaleyle ilgili herhangi bir sorunuz veya öneriniz var mı? Aşağıdaki yorum formunu kullanarak bize bir not bırakmaktan çekinmeyin. Sizden haber almak için sabırsızlanıyoruz!