Database Management - DML SELECT

 DML의 가장 기초인 SELECT 문을 살펴보자. Projection(결과가 표와 튜플들의 형태로 보여주는 연산)을 수행하는 가장 근본적인 쿼리이다.

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY] [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
        [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
    | LOCK IN SHARE MODE]
    [into_option]

또한, 일반적으로 다음과 같은 쿼리의 우선순위로 실행됨을 기억해두고 가자. 모든 쿼리들이 다음과 같이 실행되지는 않는다.

FROM → WHERE [→ GROUPBY → HAVING] → SELECT [ → ORDER BY]

 

1. SFW query basic form

SELECT <expr> [, <expr>, <expr>, ...]
FROM <one or more relations>
WHERE <conditions>

exprs : 적어도 하나의 expr가 들어가야하며, 이때 expr는 수식을 의미한다. FROM에서 참조된 relation(=table)에 대한 수식이 들어가야 한다.

-- 임시 데이터 생성

-- create table t1 (
--     Id	INT	NOT NULL,
--     Name	VARCHAR(10)	NOT NULL,
--     Age	INT,
-- 	Weight INT,
--     Height INT
-- );

-- INSERT INTO t1 VALUES (1, 'BOB', 10, 45, 155);
-- INSERT INTO t1 VALUES (2, 'John', 11, 43, 160);
-- INSERT INTO t1 VALUES (3, 'Key', 9, 50, 150);

SELECT * -- 모든 열 선택
FROM t1 -- t1에서

여기서 어떤 조건을 추가하고 싶다면 다음과 같이 입력한다.

SELECT Name, Age
FROM t1
WHERE Age >= 10; -- 피연산자와 연산자끼리 띄어쓰기 무상관

 

2. SELECT Clause

 다음은 수식에 Arithmetic Operators를 넣어 결과를 보여주는 쿼리이다.

SELECT Name, (WEIGHT/(HEIGHT*HEIGHT/10000) )
FROM t1
WHERE age >= 10;

 해당 쿼리의 열 이름이 굉장히 보기 힘들어 다음과 같이 별칭(nickname)을 지어주자.

SELECT Name, (WEIGHT/(HEIGHT*HEIGHT/10000) ) AS BMI
FROM t1
WHERE age >= 10;

HEIGHT와 WEIGHT만 보여주게 할 수도 있다.

SELECT WEIGHT || ', ' || HEIGHT AS "W_H"
FROM t1;

가끔씩 중복된 값을 지운 튜플들을 보고 싶을 수도 있다.(Set 집합 형태로) 그럴 때는 다음과 같이 Distinct 옵션을 사용한다.

-- CREATE TABLE DuplicatedTable (
-- 	ID	INT	NOT NULL,
--     Name	VARCHAR(10)	NOT NULL
-- );

-- INSERT INTO DuplicatedTable 
-- VALUES (1, 'example');

-- INSERT INTO DuplicatedTable 
-- VALUES (1, 'example');

-- INSERT INTO DuplicatedTable 
-- VALUES (2, 'example');

SELECT * FROM duplicated_table;

SELECT DISTINCT * FROM duplicated_table;

 

3. WHERE clauses

 종종 글자의 어떤 패턴, 규칙이 있는지 검색하고 싶을 때가 있다. 다음과 같이 LIKE 연산을 사용하여 WHERE의 조건문에 사용할 수 있다.

SELECT *
FROM t1
WHERE Name LIKE '%o_' OR Name LIKE '%O_';
-- t1에서
-- Name에 뒤에서 두 번째로 o나 O가 들어가는 튜플 검색
-- 모든 열 선택

%는 어느 문자열(하나 이상의 문자)을 나타내며, _는 한 개의 문자를 나타낸다.

 

4. ORDER BY

 데이터들을 조회할 때, 오름차순 내림차순으로 보여주게 하고 싶을 때도 있다. 가령 올림픽 대회에서나 어떤 점수를 기준으로 가져오고 싶은 경우가 있는데 이는 ORDER BY 옵션을 통해 처리를 할 수 있다. 여기서 mysql은 기본적으로 stable sort를 따른다.

-- create table Scores (
--     kor int not null,
--     math int not null,
--     eng int not null,
--     sci int not null
-- );

-- insert into Scores values (85, 90, 100, 70);
-- insert into Scores values (90, 91, 100, 70);
-- insert into Scores values (83, 90, 100, 70);
-- insert into Scores values (70, 90, 100, 70);
-- insert into Scores values (91, 90, 100, 70);
-- insert into Scores values (90, 90, 99, 70);
-- insert into Scores values (80, 90, 100, 70);

select *
    from Scores
    order by kor, math;

기본적으로 order by kor, math 뒤에 ASC(ascending)이 생략되어 있다. 내림차순으로 정렬하고 싶다면 DESC를 입력해주면 된다.

select *
    from Scores
    order by kor desc, math asc;

다음은 kor column이 80점 이상 90점 이하 인 tuple에 대해 평균을 계산한 projection이다.

select (kor+math+eng+sci)/4 as avg
from Scores
where kor between 80 and 90
order by avg desc;

별칭에 대해서도 예약어로 사용된다. 따라서 위처럼 작성이 가능하다.

 

5. GROUP BY

 GROUP BY는 같은 값을 가지는 튜플들끼리 묶는 것이다. 이때 주의할 점은 GROUP BY 절에 나열된 속성들만 SELECT 에 넣을 수 있다. 예제를 보자.

-- MySQL

CREATE TABLE sales (
  id INT NOT NULL AUTO_INCREMENT,
  customer_name VARCHAR(50) NOT NULL,
  product_name VARCHAR(50) NOT NULL,
  sale_date DATE NOT NULL,
  sale_amount DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO sales (customer_name, product_name, sale_date, sale_amount)
VALUES ('John', 'Shoes', '2022-03-01', 100.00),
       ('John', 'Shoes', '2022-03-05', 50.00),
       ('John', 'Shirt', '2022-03-05', 75.00),
       ('Mary', 'Shoes', '2022-03-06', 200.00),
       ('Mary', 'Shirt', '2022-03-07', 100.00),
       ('Mary', 'Pants', '2022-03-08', 150.00),
       ('Bob', 'Shoes', '2022-03-10', 125.00),
       ('Bob', 'Shirt', '2022-03-11', 80.00),
       ('Bob', 'Pants', '2022-03-11', 110.00),
       ('Bob', 'Shoes', '2022-03-12', 75.00),
       ('Bob', 'Shirt', '2022-03-13', 90.00);

-- Oracle DB

CREATE TABLE sales (
  id NUMBER(10) NOT NULL,
  customer_name VARCHAR2(50) NOT NULL,
  product_name VARCHAR2(50) NOT NULL,
  sale_date DATE NOT NULL,
  sale_amount NUMBER(10,2) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (1, 'John', 'Shoes', TO_DATE('2022-03-01', 'YYYY-MM-DD'), 100.00);
INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (2, 'John', 'Shoes', TO_DATE('2022-03-05', 'YYYY-MM-DD'), 50.00);
INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (3, 'John', 'Shirt', TO_DATE('2022-03-05', 'YYYY-MM-DD'), 75.00);
INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (4, 'Mary', 'Shoes', TO_DATE('2022-03-06', 'YYYY-MM-DD'), 200.00);
INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (5, 'Mary', 'Shirt', TO_DATE('2022-03-07', 'YYYY-MM-DD'), 100.00);
INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (6, 'Mary', 'Pants', TO_DATE('2022-03-08', 'YYYY-MM-DD'), 150.00);
INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (7, 'Bob', 'Shoes', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 125.00);
INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (8, 'Bob', 'Shirt', TO_DATE('2022-03-11', 'YYYY-MM-DD'), 80.00);
INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (9, 'Bob', 'Pants', TO_DATE('2022-03-11', 'YYYY-MM-DD'), 110.00);
INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (10, 'Bob', 'Shoes', TO_DATE('2022-03-12', 'YYYY-MM-DD'), 75.00);
INSERT INTO sales (id, customer_name, product_name, sale_date, sale_amount) VALUES (11, 'Bob', 'Shirt', TO_DATE('2022-03-13', 'YYYY-MM-DD'), 90.00);

데이터들을 통해 다음 GROUP BY를 실행시켜보자.

select product_name
from sales
group by product_name;

 sales 테이블의 product_name 기준으로 묶고, product_name을 테이블 형태로 보여주는 쿼리이다. 묶어서는 DISTINCT와 별 다를게 없다. 이는 다음과 같이 Aggregation 함수와 사용하여 더 실용성 있게 쓸 수 있도록 고안된 쿼리이다.

select product_name, avg(sale_amount)
from sales
group by product_name;

 

6. HAVING

 GROUP BY와 무조건 같이 사용해야한다. grouping을 하는 동안 조건을 넣어 사용할 수 있게 된다. 이때 Nested Query(서브 쿼리를 사용한 쿼리)는 성능이 O(n^2)이지만, Group-by having을 통해서는 O(n log n)의 성능을 보이게 된다. 따라서 Nested Query를 GROUP BY 쿼리로 고칠 수 있다면, GROUP BY 쿼리를 사용하는게 더 효율적이다.

select S
from R1, R2, ..., Rn
where C1
group by a1, a2, ..., an
having C2;

예제를 살펴보자.

SELECT customer_name, SUM(sale_amount) as total_sales
FROM sales
GROUP BY customer_name
HAVING total_sales > 200;

sales 테이블의 customer_name을 그룹화하여 각각의 그룹이 total_sales 가 200초과인 그룹만 보여주는 쿼리이다.

 

7. LIMIT(MySQL), ROWNUM(Oracle DB)

 행이 엄청 많은 테이블에 대해서는 출력하는 것이 꽤 오래걸리고, 방대한 양을 굳이 다 출력할 필요도 없다. 이때, 쓸 수 있는 것이 LIMIT = ROWNUM이다.

LIMIT [offset,] row_count

위는 LIMIT clause의 syntax이다. 아래 예제를 보자.

SELECT * FROM sales LIMIT 10;
-- 출력 행을 10개로 제한한다.

SELECT * FROM sales LIMIT 10, 100;
-- 11번째 행부터 100번째 행까지 제한한다.

LIMIT는 맨 위의 syntax에서 보다시피 맨 마지막에 붙여주면 된다.

SELECT * FROM (
  SELECT * FROM sales
) WHERE ROWNUM <= 10;
-- 출력 행을 10개로 제한한다.