[MariaDB] 9. DML - SELECT

1. SELECT
   1. 기본
      1. 예제 1. WHERE, ORDER BY, LIMIT
      2. 예제 2. LIMIT
   2. JOIN
      1. 예제 1. LEFT JOIN
      2. 예제 2. INNER JOIN
   3. FROM DUAL
   4. USE
   5. DISTNCT
2. UNION

---

1. SELECT

DML(Data Manipulation Language) 데이터 조작어
데이터 조작어는 읽기, 쓰기, 수정, 삭제 와같은 CRUD에 이용되는 명령어가 이에 해당됩니다.

그 중 가장 많이 사용되는 명령어인 SELECT에 대해 먼저 알아봅시다.
SELECT는 테이블의 row를 검색하는데에 이용하는 명령어로, UNION절이나 subqueries를 포함하여 이용할 수 있습니다.

※ subquery 관련 부분은 추후 상세히 다루도록 하겠습니다.

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [ FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset  [ROWS EXAMINED rows_limit] } |
        [OFFSET start { ROW | ROWS }]
        [FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }] ]
      procedure|[PROCEDURE procedure_name(argument_list)]
      [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options] |
        INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ]
      [FOR UPDATE lock_option | LOCK IN SHARE MODE lock_option]


export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]


lock_option:
    [WAIT n | NOWAIT | SKIP LOCKED]

select_expr: 조회할 컬럼
table_references: 조회할 데이터가 들어있는 테이블들

1.1. 기본

• SELECT
  • 조회할 컬럼을 설정하고, 조회하고 싶은 컬럼이 많다면 , 로 구분지어 나열하면 됩니다.
• FROM
  • 조회하고자하는 rows가 들어있는 테이블이나 테이블들이 들어갑니다.
• WHERE
  • 조회할 조건을 설정합니다.
  • AND 와 OR같은 연산자를 이용하여 조건절을 설정할 수 있습니다.
• ORDER BY col_name [ASC|DESC] [,col_name [ASC|DESC]]
  • 정렬기준을 설정합니다. (기본값은 오름차순(ASC))
  • 내림차순으로 하고 싶다면 DESC를 쓰면 됩니다.
  • 2번째 정렬기준이 있다면 쉼표로 구분지어 나열합니다.
• LIMIT
  • 한개만 설정할 경우에는 출력할 row 수
  • 쉼표로 두개를 설정할 경우에는 첫번째 수는 offset(패스할 row수), 두번째는 출력할 row 수 입니다.
    • 예를들어 LIMIT 10, 5 의 경우, 11번째 row부터 5개를 출력합니다.

간단한 DML 문법을 예제를 통해 알아봅시다.

예제 1. WHERE, ORDER BY, LIMIT

• employees 테이블에서
• gender 가 F 면서 first_name이 A로 시작되거나 B로 시작되는 값을
• last_name 내림차순으로 정렬하여
• 5개의 row를
• emp_no, first_name, last_name 컬럼만 조회하기

SELECT emp_no, first_name, last_name
FROM employees
WHERE gender = 'F' AND (first_name LIKE 'A%' OR first_name LIKE 'B%')
ORDER BY last_name DESC
LIMIT 5;

+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
|  75486 | Anestis    | Zykh      |
|  73684 | Badri      | Zykh      |
|  83473 | Adhemar    | Zykh      |
|  81062 | Berry      | Zykh      |
| 204150 | Akemi      | Zykh      |
+--------+------------+-----------+

예제 2. LIMIT

• employees 테이블에서
• 5개 row 출력하기

SELECT *
FROM employees
LIMIT 5;

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+

• employees 테이블에서
• 4개 제외하고 5개 row 출력하기

SELECT *
FROM employees
LIMIT 4, 5;

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
+--------+------------+------------+-----------+--------+------------+

1.2. JOIN

여러 테이블을 JOIN하여 조회하고 싶다면 원하는 JOIN 형태에 맞춰 JOIN을 하면 됩니다.
JOIN 조건은 ON에 설정합니다.

ON 절에는 JOIN할 조건을 정의합니다.
WHERE 절에는 그 밖의 조회 조건을 정의합니다.

JOIN은 크게 2가지가 있는데, 두 테이블에 모두 존재할 경우에만 조회되는 INNER JOIN
둘 중 하나에만 있어도 조회되는 OUTER JOIN이 있습니다.

LEFT OUTER JOIN은 왼쪽 테이블을 JOIN 조건과 관계없이 조회하고, 오른쪽 테이블은 데이터가 없을 경우 null로 표현합니다.
RIGHT OUTER JOIN은 오른쪽 테이블을 JOIN 조건과 관계없이 조회하고, 왼쪽 테이블은 데이터가 없을 경우 null로 표현합니다.

OUTER JOIN에서 OUTER 키워드는 생략이 가능하여, LEFT JOIN, RIGHT JOIN 과 같이 간편히 표기할 수 있습니다.

예제 출력을 위해 아래와 같은 쿼리를 먼저 실행해봅니다.

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` varchar(10) NOT NULL DEFAULT 'BASIC',
  `name` varchar(50) NOT NULL,
  `sex` varchar(1) NOT NULL DEFAULT 'M',
  `phone_number` varchar(20) NOT NULL,
  `birth_date` date DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(150) NOT NULL,
  `active` bit(1) NOT NULL DEFAULT b'1',
  `created_at` datetime(6) NOT NULL DEFAULT current_timestamp(6),
  `updated_at` datetime(6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_USER_EMAIL` (`email`)
) ;
CREATE TABLE `store` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `business` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_USER_STORE` (`user_id`),
  CONSTRAINT `FK_USER_STORE` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);

INSERT INTO `user` (`id`, `type`, `name`, `sex`, `phone_number`, `birth_date`, `email`, `password`, `created_at`) VALUES
('1','BASIC','지니','F','01011112222','1992-02-01','jini@jiniworld.me','1','2022-04-11 16:59:07.148000'),
('2','OWNER','이한','M','01088887777','1988-07-02','2han@appleboxy.xyz','1','2022-04-13 12:21:30.542000'),
('3','OWNER','코코','F','01044447777','1995-07-05','coco@jiniworld.me','1','2022-04-13 16:14:01.098665');

INSERT INTO `store` (`id`, `user_id`, `name`, `business`) VALUES
('1','1','우리의 시간','카페'),
('2','1','에머이','베트남음식'),
('3','2','한잔의 추억','술집'),
('4',NULL,'할리스','카페');

예제 1. LEFT JOIN

ON 절에는 JOIN할 조건을 정의합니다.

SELECT type, user.name, email, store.name store_name, business
FROM user LEFT JOIN store
ON user.id = store.user_id
WHERE user.type = 'OWNER';

+-------+--------+--------------------+------------------+----------+
| type  | name   | email              | store_name       | business |
+-------+--------+--------------------+------------------+----------+
| OWNER | 이한   | 2han@appleboxy.xyz | 한잔의 추억      | 술집     |
| OWNER | 코코   | coco@jiniworld.me  | NULL             | NULL     |
+-------+--------+--------------------+------------------+----------+

예제 2. INNER JOIN

SELECT type, user.name, email, store.name store_name, business
FROM user RIGHT OUTER JOIN store
ON user.id = store.user_id;

+-------+--------+--------------------+------------------+-----------------+
| type  | name   | email              | store_name       | business        |
+-------+--------+--------------------+------------------+-----------------+
| BASIC | 지니   | jini@jiniworld.me  | 우리의 시간      | 카페            |
| BASIC | 지니   | jini@jiniworld.me  | 에머이           | 베트남음식      |
| OWNER | 이한   | 2han@appleboxy.xyz | 한잔의 추억      | 술집            |
| NULL  | NULL   | NULL               | 할리스           | 카페            |
+-------+--------+--------------------+------------------+-----------------+

INNER JOIN은 , 로 대체할 수 있습니다.
이 경우에는 JOIN 조건을 ON이 아닌 WHERE로 설정하면 됩니다.

SELECT type, user.name, email, store.name store_name, business
FROM user, store
WHERE user.id = store.user_id;

1.3. FROM DUAL

만일 테이블을 선택하고 싶지않다면 FROM DUAL을 이용하면 됩니다.

SELECT now()
FROM DUAL;

+---------------------+
| now()               |
+---------------------+
| 2022-04-22 01:08:42 |
+---------------------+

1.4. USE

USE db_name

USE를 이용하여 기본 database를 선택할 경우, 테이블 앞에 database를 생략할 수 있습니다.

1.5. DISTINCT

중복되는 값을 제외하고 싶다면 DISTINCT 키워드를 사용하면 됩니다.

SELECT dept_no FROM dept_manager;

+---------+
| dept_no |
+---------+
| d001    |
| d001    |
| d002    |
| d002    |
| d003    |
| d003    |
| d004    |
| d004    |
| d004    |
| d004    |
| d005    |
| d005    |
| d006    |
| d006    |
| d006    |
| d006    |
| d007    |
| d007    |
| d008    |
| d008    |
| d009    |
| d009    |
| d009    |
| d009    |
+---------+

SELECT DISTINCT dept_no FROM dept_manager;

+---------+
| dept_no |
+---------+
| d001    |
| d002    |
| d003    |
| d004    |
| d005    |
| d006    |
| d007    |
| d008    |
| d009    |
+---------+

DISTINCT 대신 GROUP BY로도 동일한 결과를 출력할 수 있습니다.

SELECT dept_no FROM dept_manager GROUP BY dept_no;

+---------+
| dept_no |
+---------+
| d001    |
| d002    |
| d003    |
| d004    |
| d005    |
| d006    |
| d007    |
| d008    |
| d009    |
+---------+

---

2. UNION

UNION은 두 쿼리의 출력값을 합칩니다.
합치기 위해서는 각 쿼리의 출력하고자하는 컬럼명이 일치해야합니다.

UNION ALL, UNION DISTINCT 두가지가 있으며, 기본값은 UNION DISTINCT로, 중복되는 row는 하나만 출력합니다.

UNION DISTINCT는 UNION이라는 축약 형태로 사용할 수 있습니다.

2.1. UNION

SELECT 'dept_emp' table_name, emp_no, dept_no FROM dept_emp WHERE emp_no in (10001, 10002, 110511)
UNION
SELECT 'dept_manager' table_name, emp_no, dept_no FROM dept_manager WHERE dept_no='d005';

+--------------+--------+---------+
| table_name   | emp_no | dept_no |
+--------------+--------+---------+
| dept_emp     |  10001 | d005    |
| dept_emp     |  10002 | d007    |
| dept_emp     | 110511 | d005    |
| dept_manager | 110511 | d005    |
| dept_manager | 110567 | d005    |
+--------------+--------+---------+

2.1. UNION ALL

기본적으로, UNION은 중복되는 값을 제거합니다.

SELECT emp_no, dept_no FROM dept_emp WHERE emp_no in (10001, 10002, 110511)
UNION
SELECT emp_no, dept_no FROM dept_manager WHERE dept_no='d005';

+--------+---------+
| emp_no | dept_no |
+--------+---------+
|  10001 | d005    |
|  10002 | d007    |
| 110511 | d005    |
| 110567 | d005    |
+--------+---------+

만일, 중복되는 row도 그대로 출력하고 싶다면 UNION ALL을 사용하면 됩니다.

SELECT emp_no, dept_no FROM dept_emp WHERE emp_no in (10001, 10002, 110511)
UNION ALL
SELECT emp_no, dept_no FROM dept_manager WHERE dept_no='d005';

+--------+---------+
| emp_no | dept_no |
+--------+---------+
|  10001 | d005    |
|  10002 | d007    |
| 110511 | d005    |
| 110511 | d005    |
| 110567 | d005    |
+--------+---------+