SQL 기초 정리 by postgres

쿼리는 postgres를 기준으로 작성되었습니다.  DBMS마다 다른 부분이 존재하나 기본적인 골격을 이해하면 적응하는데 크게 이슈가 없습니다.  시간이 되면 maria db, sqlLite 버전도 만들어 볼 예정입니다.

 

전체적으로 직원, 부서, 고객, 매출 테이블을 기반으로 구성되어 있고, 꾸준히 샘플데이터를 넣어서 테스트해보면서 쿼리를 심화해서 이 포스팅을 갱신할 예정입니다. 

 

포스트그레스 SQL 설치

https://itcamp24.tistory.com/190

POSTGRESQL 11 다운로드 및 설치

 

사용자 생성 create user Account

CREATE USER test WITH PASSWORD 'xptmxm123!';

 

데이터베이스 생성 create database DB명

CREATE DATABASE testdb OWNER test ENCODING 'UTF8' TEMPLATE template0;

 

데이터베이스에 사용자 권한 할당 grant

GRANT ALL PRIVILEGES ON DATABASE testdb TO test;

 

스키마 생성 create schema 스키마명

CREATE SCHEMA test AUTHORIZATION test;

 

 테이블 생성 create table 테이블명

postgresql 서버의 test 스키마 아래에  "employees", "departments", "sales" and "customers"  테이블을 생성

CREATE TABLE test.departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    location VARCHAR(255)
);

CREATE TABLE test.employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    salary NUMERIC(10, 2) NOT NULL,
    start_date DATE NOT NULL,
    department_id INTEGER REFERENCES test.departments(id)
);

CREATE TABLE test.customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    phone VARCHAR(14) NOT NULL
);

CREATE TABLE test.sales (
    id SERIAL PRIMARY KEY,
    date DATE NOT NULL,
    product VARCHAR(255) NOT NULL,
    quantity INTEGER NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    customer_id INTEGER REFERENCES test.customers(id),
    salesperson_id INTEGER REFERENCES test.employees(id)
);

CREATE TABLE test.samples (
    id SERIAL PRIMARY KEY,
    test VARCHAR(255) NOT NULL
);

 

필드타입

• INTEGER: 정수형
• BIGINT: 대정수
• NUMERIC(precision, scale): <전체자리수, 소수점자리수,> NUMERIC(15,2) 소수점 2자리를 포함한 총 15자리
• REAL: 실수
• DOUBLE PRECISION: 배정도 실수
• SERIAL : auto-incrementing 자동증가형 필드
• BOOLEAN: 불린
• CHAR(n): characters 고정자리 문자열로 입력문자가 지정된 수보다 작을 경우 " "스페이스가 추가됨
• VARCHAR(n): 가변형 문자열
• TEXT: 텍스트타입으로 오라클의 CLOB와 유사하나 사용성이 용이함
• DATE: 날짜 YYYY-MM-DD
• TIMESTAMP: 날짜 시분초 YYYY-MM-DD HH:MI:SS.
• JSON and JSONB: JSON data.

 

테이블명 변경

alter table 테이블명 rename to 수정테이블명

ALTER TABLE test.samples
RENAME TO sample;

samples테이블명을 sample로 바꿀 수 있다.

 

테이블 비우기(자르기)

TRUNCATE TABLE test.sample;

samples테이블을 비운다(자른다).  테이블 전체를 삭제할 경우 delete * from `table`보다 유용한다. 

delete시 데이터가 많을 경우 건 단위로 삭제되므로 긴시간이 소요된다.

 

테이블 삭제 drop table 테이블명

DROP TABLE test.sample;

sample테이블을 삭제한다.

 

테이블 삭제 drop table if exists테이블명(테이블 존재유무를 확인 후)

DROP TABLE IF EXISTS test.sample;

sample테이블이 존재할 경우 삭제한다.

 

 테이블에 유닉키(고유키) 추가하기

ALTER TABLE test.departments
ADD CONSTRAINT unique_name UNIQUE (name);

departments테이블에서 name필드를 고유하도록 유닉키를 설정한다.

 

 인덱스 생성

CREATE INDEX employees_name_idx ON test.employees (name);

"employee" 테이블의 "name" 필드에 "employee_name_idx"라는 인덱스가 생성한다.

인덱스는 데이터베이스가 하나 이상의 열에 있는 값을 기준으로 테이블의 행을 신속하게 찾고 검색할 수 있도록 하는 데이터 구조이다. "employee"  테이블의 "name" 필드에 색인을 작성하여 이름으로 직원을 검색하는 쿼리의 성능을 향상시킬 수 있다.

열에 인덱스를 만들면 해당 열의 데이터가 정렬되므로 쿼리 최적화 도구가 필요한 행을 쉽게 찾을 수 있습니다.

인덱스를 생성하면 Disk 공간이 증가하고 쓰기 부하가 높은 워크로드에 대한 잠재적인 성능 저하와 같은 비용이 든다는 점에 유의해야 한다. 따라서 자주 검색되거나 순서가 지정되는 열에만 인덱스를 생성해야 한다.

 

복합 인덱스 생성

CREATE INDEX employees_name_dept_idx ON test.employees(name, department_id);

이렇게 하면 "employee" 테이블의 "name" 및 "department_id" 필드에 "employee_name_dept_idx"라는 인덱스가 생성된다.

이와 같은 여러 열의 인덱스를 복합 인덱스라고 하며, 여러 열을 기준으로 행을 자주 검색할 때 유용하다.

복합 인덱스를 작성할 때 데이터베이스는 인덱스의 각 열에 대해 별도의 데이터 구조를 작성한다. 이 경우 데이터베이스는 "name" 열에 있는 데이터를 정렬하는 데이터 구조를 만들고 "department_id" 열에 있는 데이터를 정렬하는 다른 데이터 구조를 만든다. "name" 및 "department_id" 열을 기준으로 행을 검색할 때 데이터베이스는 두 데이터 구조를 동시에 검색하여 행을 빠르게 찾을 수 있다.

복합 인덱스는 검색에 참여하는 열을 동시에 함께 자주 검색할 때 유리하다. 열 중 하나만 사용하는 경우라면 굳이 복합인덱스를 만들어서는 안된다.

 

 

 insert 

insert into (field1, field2, ..) values (value1, value2,..) ;

INSERT INTO test.departments (name, location)
VALUES ('IT', '서울');

INSERT INTO test.departments (name, location)
VALUES ('HR', '부산');

INSERT INTO test.departments (name, location)
  SELECT 'Department' || i, 'Location' || i
  FROM generate_series(1, 100) as i;

employees테이블이  departments테이블을 참고하므로 데이터가 먼저 추가되어야 한다.

부서의 name이 고유키므로 중복되서는 않됀다.

 

INSERT INTO test.customers (name, address, phone)
  SELECT 'Customer' || i, '서울' || i, '010-1234-12' || lpad(cast(i as varchar), 2, '0')
  FROM generate_series(1, 99) as i;

customers테이블에  generate_series(1, 99) as i를 사용해서 1부터 99까지 시리즈 인덱스를 생성해서 추가한다.

문자열을 더하기 위해 || 를 사용하고, lpad(문자, 자리수, 대체문자)함수는 2자리로 맞추기 위해서 사용한다. 10보다 작은 숫자는 01, 02, 03.. 이런식으로 문자열이 더해진다.

cast(필드 또는 값 as 필드타입)함수는 원하는 필드타입으로 변환하는 캐스팅함수이다.

 

INSERT INTO test.employees (name, salary, start_date, department_id)
VALUES ('최프로', 50000, '2022-01-01', 1);

INSERT INTO test.employees (name, salary, start_date, department_id)
VALUES ('김프로', 45000, '2022-03-01', 2);

INSERT INTO test.employees (name, salary, start_date, department_id)
VALUES ('강프로', 35000, '2022-03-01', 2);

INSERT INTO test.employees (name, salary, start_date, department_id)
  SELECT 'Employee' || i, 10000 + i * 1000, '2022-01-01', i % 2 + 1
  FROM generate_series(1, 100) as i;

employees테이블에 데이터를 추가한다. generate_series를 사용해서 샘플데이터를 추가한다.

 

CREATE TABLE test.temp_employees AS SELECT name, salary, start_date FROM test.employees limit 0;

INSERT INTO test.temp_employees (name, salary, start_date)
SELECT '이프로', 35000, cast('2022-01-01' as date)
UNION ALL
SELECT '장프로', 45000, cast('2022-03-01' as date);

• test.employees 테이블을 이용해 temp_employees테이블 생성하고 데이터를 추가한다.
• insert select 로 추가할 수 있다.
• start_date필드가 날짜타입이므로 cast함수를 사용해서 cast('2022-01-01' as date)와 같이 해서 날짜로 캐스팅한다.
• cast함수는 cast(값 as 필드타입)의 형태로 사용된다.

 

INSERT INTO test.employees (name, salary, start_date)
SELECT name, salary, start_date
FROM test.temp_employees;

• insert select from 절로 추가할 수 있으며 대용량 데이터 작업 시 많이 사용한다.
• temp_employees테이블 읽어서 employees테이블에 데이터를 추가한다.

 

INSERT INTO test.sales (date, product, quantity, price, customer_id, salesperson_id)
VALUES ('2022-01-01', 'Product 1', 10, 100.00, 1, 1);

INSERT INTO test.sales (date, product, quantity, price, customer_id, salesperson_id)
VALUES ('2022-02-01', 'Product 2', 5, 200.00, 2, 2);

INSERT INTO test.sales (date, product, quantity, price, customer_id, salesperson_id)
  SELECT '2022-03-01', 'Product 1', 1, 100.00, i, 1
  FROM generate_series(1, 99) as i;

sales테이블에 데이터를 추가한다.

샘플데이터 생성을 위해 generate_series를 사용하여 salesperson_id에게 추가적으로 99건의 판매건을 등록하였다.

 

update(데이터 수정)

update  테이블명 set 필드명1 = 값,1, 필드명2 = 값2 where 조건;

UPDATE test.departments
SET location = '부산'
WHERE name = 'IT';

departments테이블의 name = 'IT'인 location필드의 값을 '부산''으로 수정한다. 

 

UPDATE test.employees
SET salary = 55000
WHERE name = '최프로';

employees테이블에서 name = '최프로'의 salary를 55000으로 수정한다.

 

select(데이터 조회)

SELECT * FROM test.departments;

부서 전체를 조회한다.

 

UPDATE test.departments
SET location = '서울'
WHERE name = 'IT';

SELECT * FROM test.departments LIMIT 10;

name이 IT인 부서의 위치를 서울로 바꾸고 부서 데이터 10개를 조회한다.

 

SELECT * FROM test.departments
WHERE name = 'IT';

departments테이블에서 name = 'IT'인 데이터를 조회한다.

 

SELECT * FROM test.departments
WHERE name LIKE '%IT%';

departments테이블에서 'IT'를 포함한 데이터를 조회한다.

 

SELECT * FROM test.departments
WHERE name LIKE 'IT%';

%가 뒤에만 붙어있을 경우 departments테이블에서 'IT'로 시작하는 데이터를 조회한다.

 

delete(데이터 삭제)

DELETE FROM test.employees
WHERE name = '장프로';

employees테이블에서 name = '장프로'를 삭제한다.

 

DELETE FROM test.employees
WHERE name LIKE '강%';

employees테이블에서 name에서 '강'으로 시작하는 전체를 삭제한다.

 

조회문 예제

sales테이블에서 2022년 총판매액을 구하라.

SELECT SUM(quantity * price) as total_sales
FROM test.sales
WHERE date_part('year',date) = 2022;

 

employees테이블에서 2022년도에 입사한 사람 중 김프로라는 이름을 포함하는 데이터를 찾아라

SELECT *
FROM test.employees
WHERE start_date >= '2022-01-01' AND start_date <= '2022-12-31'
AND name ILIKE '%김프로%';

like  연산자는 유사한 데이터를 찾을 때 %등을 사용하여 찾는다.

 

employees테이블에서 2022년도에 입사한 사람 중 김프로라는 이름을 포함하는 고유한 이름 목록을 가져와라.

SELECT DISTINCT name
FROM test.employees
WHERE start_date >= '2022-01-01' AND start_date <= '2022-12-31'
AND name ILIKE '%김프로%';

distinct키워드는 중복되지 않는 고유한 필드값을 구할 때 사용한다.

 

custmoers 테이블의 전화번호를 5번째 자리에서 4개의 문자만 가져와라

SELECT phone, SUBSTR(phone, 5, 4) as phone_substring
FROM test.customers;

substr(필드명, 시작인덱스, 갯수) : 시작인덱스는 1부터 시작한다.  갯수는 가져오고자 하는 문자 수이다.

 

employees 테이블에서 급여가 많은 순으로 데이터 5개만 가져와라.

SELECT * FROM test.employees
ORDER BY salary DESC
LIMIT 5;

LIMIT 5를 사용하여 갯수를 제한한다.

 

employees 테이블에서 급여가 많은 순으로 6번째 데이터부터 데이터 5개만 가져와라.

SELECT * FROM test.employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

LIMIT 5를 사용하여 갯수를 제한한다. OFFSET은 시작이 0으므로 6번째 위치는 5이다. LIMIT n OFFSET page*n을 사용해 게시판 페이징기능을 구현할 수 있다.

 

테이블 조인

INNER JOIN 

from 테이블1 inner join  테이블2 on  테이블1.키 = 테이블2.키

SELECT employees.name, departments.name, salary
FROM test.employees
INNER JOIN test.departments
ON employees.department_id = departments.id
WHERE 
salary > 47000
;

두개의 테이블에서 공통적인 것만 선택되며 교집합으로 이해하면 된다. where 조건을 사용하여 데이터를 필터하여 조인문을 사용할 수 있다. 

 

부서별 직원수를 직원수가 많은 순으로 정렬해서 구하라. 

SELECT departments.name, COUNT(employees.id) as employee_count
FROM test.employees
INNER JOIN test.departments
ON employees.department_id = departments.id
GROUP BY departments.name
ORDER BY employee_count DESC;

inner join 후 group by를 통해 부서명별로 직원수를 sum()집계함수로 구한다. 정렬기준은 직원수가 많은 순이다.

 

다중 조인

SELECT sales.product, customers.name, employees.name
FROM test.sales
INNER JOIN test.customers
ON sales.customer_id = customers.id
INNER JOIN test.employees
ON sales.salesperson_id = employees.id;

sales테이블과 customers테이블과 employees 삼중조인 예

 

LEFT JOIN

from 테이블1 left join  테이블2 on  테이블1.키 = 테이블2.키

join문으로 테이블을 결합할 때 왼쪽과 오른쪽 조인이되는 결합데이터에 조인조건에 해당되지 않는 왼쪽데이터를 포함해서 결과값을 가져온다. 왼쪽 기준 부분집합으로 이해하면 된다. RIGHT JOIN도 있으나 동일한 개념으로 이해하면 되며 LEFT JOIN으로 처리하는 것이 일반적이다.

SELECT employees.name, departments.name
FROM test.employees
LEFT JOIN test.departments
ON employees.department_id = departments.id;

LEFT조인의 결과는 오른쪽 조인되는 테이블에 데이터가 존재하지 않을  경우 null필드값으로 결과가 리턴되어 왼쪽 테이블의 갯수의 동일한 건수의 결과가 나온다.

WITH sales_for_employees AS (
    SELECT employees.name as employee_name, SUM(sales.quantity * sales.price) as total_sales
    FROM test.employees
    LEFT JOIN test.sales
    ON employees.id = sales.salesperson_id
    GROUP BY employees.name
)
SELECT employee_name, COALESCE(total_sales, 0) as total_sales
FROM sales_for_employees;

employees테이블 과 sales테이블을 left join해서 매출액 총액을 구하는 예제이다. 이경우 판매실적이 없는 직원도 결과 데이터에 나오게 된다. inner join을 하게 되면 판매실적이 있는 직원만 결과로 나온다.

 

FULL OUTER JOIN 

select 필드 from 테이블1 full outer join  테이블2 on  테이블1.키 = 테이블2.키

SELECT employees.name, departments.name
FROM test.employees
FULL OUTER JOIN test.departments
ON employees.department_id = departments.id;

full outer join 결과는 조인되는 데이터가 존재하지 않을  양쪽 모두에 null필드값으로 결과가 리턴된다. 오른쪽과 왼쪽 데이터가 조인되는 것은 조인되는 데로 안되는 것은 안되는 것 데로 좌우에 null값을 넣어서 결합한다. 일종의 합집합이다.

 

CROSS JOIN 은 곱하기(*) 매트릭스 데이터를 만들어낸다. 

SELECT employees.name, departments.name
FROM test.employees
CROSS JOIN test.departments;

개인적으로 사용한 적이 없어서 스킵한다.

 

Group by 집계

group by 절과 함께 사용하는 집계함수는 sum, max, min, avg, rank등이 있다.

rank함수등은 partition by등을 사용해서 부서별등의 랭킹을 구할 때 사용한다. 있다는 것을 이해하고 필요할 때 구글링하면 된다.

group by를 사용할 경우 중요한 것 중 하나가 grouping에 사용되지 않는 필드는 select  필드에 사용할 수 없다. 따라서  group by 에서 사용되지 않는 필드인데 집계함수 sum등을 사용하지 않고 가져올 경우 max(필드)등의 방식으로 가져와야 한다.

추가적인 사용예들은 별도로 찾아보기 바란다.

 

올해 각 제품의 총 판매 가격을 얻기 위해 쿼리

SELECT product, SUM(quantity * price) as total_sales
FROM test.sales
WHERE date >= TO_DATE('2022-01-01','YYYY-MM-DD')
AND date <= TO_DATE('2022-02-31','YYYY-MM-DD')
GROUP BY product
ORDER BY total_sales DESC;

WITH 절을 사용하여 올해의 날짜 범위를 정의하는 this_year라는 공통 테이블을 만들고, sales.date가 year_start와 year_end 사이라는 조건에 해당하는  각 제품의 가격에 수량을 곱한 값을 합산하여 총 매출을 계산한뒤 결과를 제품별로 그룹화하고 total_sales별로 내림차순으로 정렬한다.

 

 올해 가장 높은 수익을 올린 직원 5명과 그들의 판매 수치를 얻기 위한 쿼리

SELECT employees.name, SUM(sales.quantity * sales.price) as total_sales
FROM test.employees
INNER JOIN test.sales
ON employees.id = sales.salesperson_id
WHERE date >= TO_DATE('2022-01-01','YYYY-MM-DD')
AND date <= TO_DATE('2022-02-31','YYYY-MM-DD')
GROUP BY employees.name
ORDER BY total_sales DESC
LIMIT 5;

이 쿼리는 WITH 절을 사용하여 올해의 날짜 범위를 정의하는 this_year라는 공통 테이블 표현식)를 만들고 salesperson_id 열을 사용하여 employee 테이블을 sales 테이블과 사원id로 조인하고, sales.date가 year_start와 year_end 사이라는 조건을 사용하여  필터다. 

이결과를 employee.name별로 그룹화하고 total_sales 순으로 내림차순으로 정렬한다. 마지막으로 쿼리는 LIMIT 절을 사용하여 상위 5개 결과만 표시한다.

sales테이블에서 제품별 매출액 순으로 리스틩하는 쿼리

SELECT product, SUM(quantity) as total_sales
FROM test.sales
GROUP BY product
ORDER BY total_sales DESC;

이 쿼리는 제품을 선택하고, 각 제품의 수량을 합산하여 총 매출을 계산하는 것이다. 그런 다음 쿼리는 결과를 제품 이름별로 그룹화한다. 마지막으로 쿼리는 결과를 total_sales 순으로 내림차순으로 정렬한다.

ROW_NUMBER() 함수를 추가하여 각 제품의 순위를 표시하는 열을 쿼리에 추가할 수 있다. 

WITH sales_ranking AS (
    SELECT product, SUM(quantity) as total_sales,
    ROW_NUMBER() OVER (ORDER BY SUM(quantity) DESC) AS ranking
    FROM test.sales
    GROUP BY product
)
SELECT product, total_sales, ranking
FROM sales_ranking;

총 판매량을 기준으로 각 제품의 순위를 계산하는 하위 쿼리를 생성하는 sales_ranking 공통테이블을 만들고  sales_ranking테이블에서  product, total_sales, ranking 열을 가져온다.

2022년 매출액이 가장 높은 부서와 직원을 찾는 쿼리

SELECT departments.name as department_name, employees.name as employee_name,
SUM(sales.quantity * sales.price) as total_sales
FROM test.sales
INNER JOIN test.employees
ON sales.salesperson_id = employees.id
INNER JOIN test.departments
ON employees.department_id = departments.id
WHERE date_part('year',sales.date) = 2022
GROUP BY departments.name, employees.name
ORDER BY total_sales DESC
LIMIT 1;

 salesperson_id 열을 사용하여 "sales" 테이블을 "employee" 테이블을 department_id 열을 사용하여 "employee" 테이블을 "departments" 테이블과 결합한 뒤  2022년의 매출만 포함하도록 판매 테이블을 필터링하고 한다.

 

최종적으로 departments.name, employees.name필드로 그룹핑하고 부서별, 이름별로 매출액을 구한 결과를 리턴한다.

 

서브쿼리 IN

sales테이블에 매출실적이 있는 고객이름을 찾는 쿼리

SELECT name
FROM test.customers
WHERE id IN (
    SELECT customer_id
    FROM test.sales
);

in 절은 키필드 in (서브쿼리)형식으로 서브쿼리와 함께 사용한다.

 

sales테이블에 매출실적이 없는 고객이름을 찾는 쿼리

SELECT name
FROM test.customers
WHERE id NOT IN (
    SELECT customer_id
    FROM test.sales
);

not in절은 아닌 것을 찾는 쿼리로 not in문은 DBMS성능 상 안쓰는게 바람직하며 not exist문으로 대체를 권한다.

 

서브쿼리 EXISTS

EXISTS  서브쿼리는 해당 조건에 해당하는 데이터가 있는 지 상위테이블과 조인해서 결과를 리턴한다.

성능상  IN절보다 EXISTS절이 유리하다.

SELECT name
FROM test.customers
WHERE EXISTS (
    SELECT 1
    FROM test.sales
    WHERE customers.id = sales.customer_id
);

상위 customer테이블과 하위의  sales테이블을 id로 조인해서 서브쿼리를 생성하였다.

customers.id = .customer_id

 

sales테이블에 매출이 없는 customers테이블의 고객명 name을 non exists문으로 구현

대량의 테이블은 not  in으로는 불가능할 정도로 느리다. 반드시 기억해 놓아야 한다.

SELECT name
FROM test.customers
WHERE NOT EXISTS (
    SELECT 1
    FROM test.sales
    WHERE customers.id = sales.customer_id
);