유용한 MariaDB 쿼리

이 페이지는 MariaDB에서 자주 사용되거나 유용한 쿼리들에 대한 빠른 참고를 목적으로 합니다.

테이블 생성

CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );
CREATE TABLE student_tests (
 name CHAR(10), test CHAR(10), 
 score TINYINT, test_date DATE
);

자세한 내용은 CREATE TABLE을 참조하세요.

레코드 삽입

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (2), (4);

INSERT INTO student_tests 
 (name, test, score, test_date) VALUES
 ('Chun', 'SQL', 75, '2012-11-05'), 
 ('Chun', 'Tuning', 73, '2013-06-14'),
 ('Esben', 'SQL', 43, '2014-02-11'), 
 ('Esben', 'Tuning', 31, '2014-02-09'), 
 ('Kaolin', 'SQL', 56, '2014-01-01'),
 ('Kaolin', 'Tuning', 88, '2013-12-29'), 
 ('Tatiana', 'SQL', 87, '2012-04-28'), 
 ('Tatiana', 'Tuning', 83, '2013-09-30');

자세한 내용은 INSERT를 참조하세요.

AUTO_INCREMENT 사용

AUTO_INCREMENT 속성은 새로운 행에 대해 고유한 식별자를 자동으로 생성하는 데 사용됩니다.

CREATE TABLE student_details (
 id INT NOT NULL AUTO_INCREMENT, name CHAR(10), 
 date_of_birth DATE, PRIMARY KEY (id)
);

값을 삽입할 때 id 필드는 생략할 수 있으며 자동으로 생성됩니다.

INSERT INTO student_details (name,date_of_birth) VALUES 
 ('Chun', '1993-12-31'), 
 ('Esben','1946-01-01'),
 ('Kaolin','1996-07-16'),
 ('Tatiana', '1988-04-13');

SELECT * FROM student_details;
+----+---------+---------------+
| id | name    | date_of_birth |
+----+---------+---------------+
|  1 | Chun    | 1993-12-31    |
|  2 | Esben   | 1946-01-01    |
|  3 | Kaolin  | 1996-07-16    |
|  4 | Tatiana | 1988-04-13    |
+----+---------+---------------+

자세한 내용은 AUTO_INCREMENT를 참조하세요.

두 테이블에서 공통 값에 따라 조회

SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;

이러한 종류의 쿼리를 조인이라고 합니다. 자세한 내용은 JOINS를 참조하세요.

최댓값 찾기

SELECT MAX(a) FROM t1;
+--------+
| MAX(a) |
+--------+
|      3 |
+--------+

자세한 내용은 MAX() 함수를 참조하고, 아래 최댓값 찾기 및 결과 그룹화하기에서 보다 실용적인 예시를 확인하세요.

최솟값 찾기

SELECT MIN(a) FROM t1;
+--------+
| MIN(a) |
+--------+
|      1 |
+--------+

자세한 내용은 MIN() 함수를 참조하세요.

평균값 찾기

SELECT AVG(a) FROM t1;
+--------+
| AVG(a) |
+--------+
| 2.0000 |
+--------+

자세한 내용은 AVG() 함수를 참조하세요.

최댓값 찾기 및 결과 그룹화하기

SELECT name, MAX(score) FROM student_tests GROUP BY name;
+---------+------------+
| name    | MAX(score) |
+---------+------------+
| Chun    |         75 |
| Esben   |         43 |
| Kaolin  |         88 |
| Tatiana |         87 |
+---------+------------+

자세한 내용은 MAX() 함수를 참조하세요.

결과 정렬

SELECT name, test, score FROM student_tests ORDER BY score DESC;
+---------+--------+-------+
| name    | test   | score |
+---------+--------+-------+
| Kaolin  | Tuning |    88 |
| Tatiana | SQL    |    87 |
| Tatiana | Tuning |    83 |
| Chun    | SQL    |    75 |
| Chun    | Tuning |    73 |
| Kaolin  | SQL    |    56 |
| Esben   | SQL    |    43 |
| Esben   | Tuning |    31 |
+---------+--------+-------+

자세한 내용은 ORDER BY를 참조하세요.

특정 열의 최솟값을 가진 행 찾기

이 예제에서는 가장 낮은 시험 점수를 받은 학생을 찾습니다.

SELECT name,test, score FROM student_tests WHERE score=(SELECT MIN(score) FROM student);
+-------+--------+-------+
| name  | test   | score |
+-------+--------+-------+
| Esben | Tuning |    31 |
+-------+--------+-------+

그룹별로 열의 최대값이 있는 행 찾기

이 예제에서는 각 학생의 가장 높은 시험 결과를 반환합니다.

SELECT name, test, score FROM student_tests st1 WHERE score = (
  SELECT MAX(score) FROM student st2 WHERE st1.name = st2.name
); 
+---------+--------+-------+
| name    | test   | score |
+---------+--------+-------+
| Chun    | SQL    |    75 |
| Esben   | SQL    |    43 |
| Kaolin  | Tuning |    88 |
| Tatiana | SQL    |    87 |
+---------+--------+-------+

나이 계산

TIMESTAMPDIFF 함수를 사용하여 다른 사람의 나이를 계산할 수 있습니다.

SELECT CURDATE() AS today;
+------------+
| today      |
+------------+
| 2014-02-17 |
+------------+

SELECT name, date_of_birth, TIMESTAMPDIFF(YEAR,date_of_birth,'2014-08-02') AS age 
  FROM student_details;
+---------+---------------+------+
| name    | date_of_birth | age  |
+---------+---------------+------+
| Chun    | 1993-12-31    |   20 |
| Esben   | 1946-01-01    |   68 |
| Kaolin  | 1996-07-16    |   18 |
| Tatiana | 1988-04-13    |   26 |
+---------+---------------+------+

자세한 내용은 TIMESTAMPDIFF()를 참조하세요.

사용자 정의 변수 사용

이 예제에서는 사용자 정의 변수를 평균 시험 점수로 설정한 다음, 이후 쿼리에서 평균 이상의 모든 결과를 반환하는 데 사용합니다.

SELECT @avg_score:= AVG(score) FROM student_tests;
+-------------------------+
| @avg_score:= AVG(score) |
+-------------------------+
|            67.000000000 |
+-------------------------+

SELECT * FROM student_tests WHERE score > @avg_score;
+---------+--------+-------+------------+
| name    | test   | score | test_date  |
+---------+--------+-------+------------+
| Chun    | SQL    |    75 | 2012-11-05 |
| Chun    | Tuning |    73 | 2013-06-14 |
| Kaolin  | Tuning |    88 | 2013-12-29 |
| Tatiana | SQL    |    87 | 2012-04-28 |
| Tatiana | Tuning |    83 | 2013-09-30 |
+---------+--------+-------+------------+

사용자 정의 변수를 사용하여 결과 집합에 증분 카운터를 추가할 수도 있습니다.

SET @count = 0;

SELECT @count := @count + 1 AS counter, name, date_of_birth FROM student_details;
+---------+---------+---------------+
| counter | name    | date_of_birth |
+---------+---------+---------------+
|       1 | Chun    | 1993-12-31    |
|       2 | Esben   | 1946-01-01    |
|       3 | Kaolin  | 1996-07-16    |
|       4 | Tatiana | 1988-04-13    |
+---------+---------+---------------+

자세한 내용은 사용자 정의 함수를 참조하세요.

크기 순으로 테이블 보기

데이터베이스의 모든 테이블 목록을 크기 순으로 정렬하여 반환합니다.

SELECT table_schema as `DB`, table_name AS `Table`, 
  ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` 
  FROM information_schema.TABLES 
  ORDER BY (data_length + index_length) DESC;

+--------------------+---------------------------------------+-----------+
| DB                 | Table                                 | Size (MB) |
+--------------------+---------------------------------------+-----------+
| wordpress          | wp_simple_history_contexts            |      7.05 |
| wordpress          | wp_posts                              |      6.59 |
| wordpress          | wp_simple_history                     |      3.05 |
| wordpress          | wp_comments                           |      2.73 |
| wordpress          | wp_commentmeta                        |      2.47 |
| wordpress          | wp_simple_login_log                   |      2.03 |
...

중복 제거

이 예제에서는 고유 ID가 있지만 다른 모든 필드가 동일하다고 가정합니다. 아래 예제에는 4개의 레코드가 있고 그 중 3개가 중복되므로 중복된 3개 중 2개의 레코드를 제거해야 합니다. 중간 SELECT는 필수는 아니지만 반환되는 내용을 보여주기 위한 것입니다.

CREATE TABLE t (id INT, f1 VARCHAR(2));

INSERT INTO t VALUES (1,'a'), (2,'a'), (3,'b'), (4,'a');

SELECT * FROM t t1, t t2 WHERE t1.f1=t2.f1 AND t1.id<>t2.id AND t1.id=(
  SELECT MAX(id) FROM t tab WHERE tab.f1=t1.f1
);
+------+------+------+------+
| id   | f1   | id   | f1   |
+------+------+------+------+
|    4 | a    |    1 | a    |
|    4 | a    |    2 | a    |
+------+------+------+------+

DELETE FROM t WHERE id IN (
  SELECT t2.id FROM t t1, t t2 WHERE t1.f1=t2.f1 AND t1.id<>t2.id AND t1.id=(
    SELECT MAX(id) FROM t tab WHERE tab.f1=t1.f1
  )
);
Query OK, 2 rows affected (0.120 sec)

SELECT * FROM t;
+------+------+
| id   | f1   |
+------+------+
|    3 | b    |
|    4 | a    |
+------+------