MySQL 및 MariaDB의 여러 기능을 사용하는 방법 알아보기 - 2부
이 글은 MariaDB/MySQL 명령의 필수 사항을 다룬 2개의 기사로 구성된 시리즈 중 두 번째 부분입니다. 계속 진행하기 전에 이 주제에 대한 이전 기사를 참조하세요.
- 초보자를 위한 MySQL/MariaDB 기본 사항 알아보기 - 1부
MySQL/MariaDB 초보자 시리즈의 두 번째 부분에서는 SELECT 쿼리에서 반환되는 행 수를 제한하는 방법과 주어진 조건에 따라 결과 집합을 정렬하는 방법을 설명합니다.
또한 레코드를 그룹화하고 숫자 필드에 대한 기본적인 수학적 조작을 수행하는 방법을 배웁니다. 이 모든 것은 유용한 보고서를 생성하는 데 사용할 수 있는 SQL 스크립트를 만드는 데 도움이 될 것입니다.
전제 조건
시작하려면 다음 단계를 따르세요.
1. 총 4백만 개의 레코드로 구성된 6개의 테이블이 포함된 employees 샘플 데이터베이스를 다운로드합니다.
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 프롬프트를 입력하고 employees라는 데이터베이스를 만듭니다.
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. 다음과 같이 MariaDB 서버로 가져옵니다.
MariaDB [(none)]> source employees.sql
샘플 데이터베이스가 로드될 때까지 1~2분 정도 기다립니다(여기서는 4M 레코드에 대해 이야기하고 있다는 점을 명심하세요!).
4. 테이블을 나열하여 데이터베이스를 올바르게 가져왔는지 확인합니다.
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. 직원 데이터베이스에 사용할 특별 계정을 만듭니다(다른 계정 이름과 비밀번호를 자유롭게 선택 가능).
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
이제 Mariadb 프롬프트에 empadmin 사용자로 로그인하십시오.
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
계속하기 전에 위 이미지에 설명된 모든 단계가 완료되었는지 확인하세요.
결과 집합의 행 수 정렬 및 제한
급여 테이블에는 시작 날짜와 종료 날짜와 함께 각 직원의 모든 소득이 포함되어 있습니다. 시간 경과에 따른 emp_no=10001의 급여를 확인하고 싶을 수도 있습니다. 이는 다음 질문에 답하는 데 도움이 됩니다.
- 그 사람/그녀는 급여 인상을 받았나요?
- 그렇다면 언제?
다음 쿼리를 실행하여 알아보세요.
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)
이제 최근 5번의 인상을 확인해야 한다면 어떻게 될까요? ORDER BY from_date DESC를 수행할 수 있습니다. DESC 키워드는 결과 집합을 내림차순으로 정렬하려고 함을 나타냅니다.
또한 LIMIT 5를 사용하면 결과 집합에서 상위 5 행만 반환할 수 있습니다.
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를 사용할 수도 있습니다. 예를 들어, 다음 쿼리는 직원의 생년월일을 기준으로 오름차순(기본값)으로 결과 집합을 정렬한 다음 알파벳 내림차순으로 성을 기준으로 정렬합니다.
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에 대한 자세한 정보를 볼 수 있습니다.
레코드 그룹화/MAX, MIN, AVG 및 ROUND
앞서 언급했듯이 salaries 테이블에는 시간 경과에 따른 각 직원의 소득이 포함됩니다. LIMIT 외에도 MAX 및 MIN 키워드를 사용하여 최대 및 최소 직원 수를 고용한 시기를 결정할 수 있습니다.
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)
위의 결과 세트를 기반으로 아래 쿼리가 무엇을 반환할지 추측할 수 있습니까?
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)
시간 경과에 따른 평균(AVG로 지정) 급여를 소수점 이하 두 자리까지 반올림(ROUND로 지정)하여 반환하는 데 동의하신다면 귀하의 말이 맞습니다.
직원별로 그룹화된 급여 합계를 보고 상위 5를 반환하려면 다음 쿼리를 사용할 수 있습니다.
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)
위 쿼리에서는 급여를 직원별로 그룹화한 후 합계를 계산합니다.
모든 것을 하나로 모으기
다행히 보고서를 생성하기 위해 쿼리를 계속 실행할 필요는 없습니다. 대신 일련의 SQL 명령이 포함된 스크립트를 생성하여 필요한 모든 결과 집합을 반환할 수 있습니다.
스크립트를 실행하면 추가 개입 없이 필요한 정보가 반환됩니다. 예를 들어, 현재 작업 디렉토리에 다음 내용을 포함하는 maxminavg.sql이라는 파일을 생성해 보겠습니다.
--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;
두 개의 대시로 시작하는 줄은 무시되고 개별 쿼리가 차례로 실행됩니다. 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
또는 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
요약
이 문서에서는 SELECT 문에서 반환된 결과 집합을 구체화하기 위해 여러 MariaDB 함수를 사용하는 방법을 설명했습니다. 정의한 후에는 여러 개별 쿼리를 스크립트에 삽입하여 보다 쉽게 실행하고 인적 오류의 위험을 줄일 수 있습니다.
이 기사에 대해 질문이나 제안 사항이 있습니까? 아래 의견 양식을 사용하여 자유롭게 메모를 남겨주세요. 여러분의 의견을 기다리겠습니다!