데브코스 11일차 - SQL, RDBMS

kiki·2024년 3월 4일
RDBMSsql데브코스데이터분석
0

데브코스

목록 보기
1/17

키워드

RDBMS, SQL 기초, DDL, DML

SQL 학습 목적

  • 데이터로 데이터 프로덕트(머신러닝 모델, 데이터 리포트)를 만들고자함
  • 데이터 베이스에서 원하는 데이터를 가져올 때 SQL을 사용
  • SQL을 이용하면..
    - 원하는 형태로 데이터를 가져올 수 있다.
    - 효율적으로 데이터를 가져올 수 있다.
    - 간단한 데이터 분석을 수행할 수 있다.
    - 기초 통계량 추출 등이 가능

RDBMS / SQL 개요

  • 데이터베이스 (데이터 적제 및 관리)

    • 데이터 적제 뿐만 아니라 데이터를 관리하는 시스템을 필수로 필요로하기 때문에 관리 시스템(Management System)까지 포함하는 개념으로 설명됨
    • 관계형 데이터베이스 (Relational DataBase)
      • 표 형태(정형 데이터)로 데이터를 쌓는다
      • transaction(DB 상태를 변화시키는 논리적 실행 단위) 처리에 적절 (일관성과 무결성이 중요한 서비스에 사용 → 금융, 이커머스 등에서 사용)
      • ACID 특성을 준수해 데이터 일관성을 보장
      • 고정된 스키마 사용 (데이터의 타입) → 데이터 구조 변경이 어려움
      • 수평 확장이 복잡하고 어려움
      • mysql, postfreSQL, oracle
    • 비-관계형 데이터베이스 NoSQL DB)
      • key-value store, column-family, document, graph 등의 형태로 저장
      • json, xml 등(비정형, 반정형) 다양한 형태 → 빠른 읽기 가능
      • ACID 특성을 보장하지 않는 경우도 있음, 중복이 발생
      • 스키마가 없거나 유연한 스키마를 가져 데이터 구조 변경이 자유로움
      • 수평 확장이 상태적으로 용이
      • 롱고, 카산드라

  • SQL (Structured Query Language): 관계형 데이터베이스를 사용하기 위한 표준 언어
    - 테이블 (행, 열로 이루어진 이차원 표)
    - 행과 열끼리, 그리고 테이블끼리 관계를 맺고있어 관계형 DB라고함

SQL 기초 구문

  • SELECT
    • 무엇을 가져올지 지정
    • 가져올 열을 선택
  • FROM
    • 어디에서 (어느 테이블에서)
    • 여러개의 DB가 있는 경우 명시해줘야함
  • WHERE
    • 필터링 구문
    • 해당 조건에 일치하는 경우만 조회함 (논리/비교 연산자 사용)
    • 가져올 행을 선택
SELECT name, category
FROM products
  • 모든 컬럼을 가져올 땐 SELECT 문에 * 사용
    • 하지만 이는 테이블이 큰 경우 비효율적일 수 있음
    • 데이터 스키마를 미리 확인하고 쿼리를 짜기
SELECT product_id, category
FROM products
WHERE 1=1
AND category = "주방용품"
  • 1=1이라는 무조건 참인 조건을 추가하면 코드 수정이 용이해짐
    • 조건들을 주석 처리로 조건을 수정할 수 있기 때문에
  • 위 쿼리는 category 값이 “주방용품”인 데이터만 조회
SELECT product_id, name
FROM products
WHERE 1=1
AND price < 30000
LIMIT 2
  • LIMIT은 조회 데이터 갯수를 제한함
  • 해당 코드는 상위 2개 데이터를 조회

비교 연산자, 논리 연산자

AND category = "주방용품"
AND price = 2000
  • 등호 연산자는 숫자, 문자에 모두 적용 가능하다
AND price > 5000
  • price 값이 5000원보다 큰 데이터 조회
AND category <> "키즈"
AND category != "키즈"
  • <>는 ‘같지 않음’ 연산자로 테이블에서 카테고리 값이 “키즈”가 아닌 데이터를 조회한다.
  • 파이썬에서와 동일하기 !=도 같지 않음을 의미한다.
AND (category = "주방용품" OR name = "손목보호대")
  • OR 연산자를 이용해 두 조건에 맞는 데이터를 모두 조회 가능하다.
  • 위의 경우 IN을 사용할 수도 있음
AND NOT category = "키즈"
  • NOT은 true와 false를 뒤집는 연산자이다.
  • 이 쿼리에선 category가 키즈가 아닌 데이터만 필터링된다.
AND category IN ("스포츠","디지털","식품")
  • IN은 좌측의 컬럼의 값이 우측 집합에 포함되어있는 데이터만 필터링 된다.
  • 이 쿼리에선 category가 "스포츠","디지털","식품"에 해당하는 데이터만 필터링된다.
AND name LIKE "%보호%"
  • %는 모든 문자열을 의미
  • LIKE는 문자열에 특정 문자의 포함 여부를 확인하는 연산자이다.
  • 위 쿼리는 name에 ‘보호’가 포함된 모든 데이터를 조회한다.
    • 앞 뒤로 어떤 문자가 있던지 상관 없음
AND name LIKE "_그릇"
  • _(언더바)는 한 글자를 의미한다.
  • 즉 ‘밥그릇’ 같은 ‘그릇’ 앞 한 글자를 더 갖는 데이터만 조회되고 ‘고양이밥그릇’을 name으로 갖는 데이터는 조회되지 않는다.
AND price BETWEEN 5000 and 15000
AND purchase_date between '20231001' and '20231003'
  • BETWEEN은 특정 범위에 포함되는 데이터만 조회하는 연산자
  • 끝 두 값을 모두 포함함
  • 날짜 데이터도 between으로 필터링할 수 있음
    • 날짜 데이터는문자열인가 숫자인가 아님 날짜 타입이 따로 있나?
AND purchase_date IS NULL
AND product_name IS NOT NULL
  • IS NULL로 특정 컬럼이 비어있는(NULL)인 데이터를 필터링할 수 있음
    • ISNULL(컬럼명) 이라는 함수도 사용 가능하다.
  • IS NOT NULL은 is null과 반대로 특정 컬럼이 NULL이 아닌 데이터를 필터링할 수 있다.

정렬과 집계

ORDER BY price
ORDER BY price DESC
  • 특정 열의 값을 기준으로 데이터를 오름차순, 내림차순으로 정렬한다.
  • 디폴트는 오름차순(ASC)이다.
    • DESC(내림차순)을 써주면 내림차순으로 정렬된다.
  • 숫자뿐만 아니라 문자열에도 적용 가능 (한글-가나다 순, 영어-알파벳 순)
ORDER BY price DESC, name
ORDER BY 1 DESC, 2
  • 두 개 이상의 열 값 기준으로 데이터 정렬이 가능하다.
  • 위의 경우 price 기준으로 내림차순으로 1차 정렬 후, 2차로(price가 같은 경우) name에 대해 정렬한다.
  • 아래와 같이 숫자 표현을 사용할 수 있다.
    • 최종 결과의(SELECT의) 첫번째, 두번째 결과로 1,2를 이용할 수 있다.
  • LIMIT과 조합해 상위 N개, 하위 N개를 뽑을 수 있다.
SUM(price) as sum_price, avg(price) as avg_price
  • sum, avg와 같은 집계 함수를 이용해 특정 컬럼 값의 합, 평균을 구할 수 있다.
SELECT name as name_v2, 1 as new_col
SELECT name as name_v2, 'tmp' as new_col
  • 1 as new_col과 같이 작성함으로써 모든 데이터가 해당 컬럼의 값으로 1을 갖는 새로운 컬럼을 만들 수 있다.
  • 1 대신 문자열 등으로도 값을 채울 수 있음
count(1) as cnt
count(name)
count(distinct price) as unique_price_cnt
  • count(1)은 테이블의 행 수를 의미
    • count(*)도 동일한 의미를 가짐
  • 아래와 같이 count(컬럼)으로 작성하면 null 값을 갖는 데이터는 제외되고 count된다.
  • 컬럼 앞에 distinct를 붙이면 고유한 값만을 count함
SELECT category, count(1) as sales_cnt
FROM products_v3
WHERE 1=1
AND sale_yn = 'yes'
GROUP BY 1
ORDER BY 2, 1 DESC
  • category로 그룹화 후 각 그룹의 데이터 갯수를 count
GROUP BY 1, 2
  • 2개 이상의 컬럼을 기준으로도 그룹화가 가능
  • 각각의 조합으로 그룹화됨
GROUP BY 1,2
HAVING avg_price > 3000
  • HAVING은 집계(그룹화) 이후 조건절로 보면 됨.
  • 즉 그룹화 후 avg_price가 3000원이 넘는 데이터만 필터링할 수 있음
  • HAVING avg_price > 3000은 where price>3000 과는 다름

기초 SQL 함수

SELECT CONCAT(category, '-', name) as comb_name
SELECT CONCAT(category, ' ', name) as comb_name
SELECT CONCAT(category, 5000, name) as comb_name
SELECT CONCAT(name, '의 가격은', price,'입니다.') as comb_name
  • CONCAT은 여러 컬럼의 문자열을 합치고자 할 때 사용
  • 각 데이터의 category, name 값을 - 으로 잇는다.
  • 숫자 타입의 컬럼도, 이음값도 모두 문자열로 변환되어 concat된다.
SUBSTRING(name, 1, 2) as sub_string
LEFT(name, 3) as sub_string
RIGHT(name, 3) as sub_string
  • SUBSTRING 특정 컬럼의 값을 시작, 길이를 입력 받아 추출하는 함수이다.
  • 위 쿼리에선 name 컬럼의 값을 1번째 문자부터 길이 2만큼 추출한다.
  • LEFT는 왼쪽부터, RIGHT는 오른쪽부터 n개만큼 추출하는 함수이다.
  • 파이썬의 슬라이싱으로 보면 되겠다.
SELECT UPPER('John') AS up, LOWER('JOHN') AS low
  • UPPER는 모두 대문자로, LOWER는 모두 소문자로 변환한다.
  • 영문 대소문자로 인한 오류를 없애기 위해 사용함
    • 만약 programmers라는 값을 갖는 데이터가 있을 때, programmers라는 값을 갖는 데이터를 count하기 전에 Programmers, PROGRAMMERS와 같이 대소문자만 다른 데이터 모두 lower를 통해 소문자로 변환 후 count해야 원하는 결과가 나올 것이다.
CHAR_LENGTH(name) AS name_length
  • CHAR_LENGTH는 특정 컬럼 값의 길이를 반환한다
ROUND(avg(price)) as avg_price
ROUND(avg(price), 2) as avg_price
CEIL(avg(price)) as avg_price
FLOOR(avg(price)) as avg_price
TRUNCATE(avg(price), 2) as avg_price
  • ROUND는 반올림, CEIL은 올림, FLOOR는 내림, TRUNCATE는 특정 자릿수 이하를 버림
  • ROUND(avg(price),1)과 같이 사용해 반올림 해 줄 자릿수를 정해줄 수 있음
SELECT ABS(price) as res
  • ABS는 절댓값을 반환
SELECT MOD(price, product_id) as mods
POW(2, 3)
GREATEST(2,5,1,7,14)
LEAST(2,5,1,7,14)
  • MOD는 price를 product_id로 나눈 나머지 값을 반환
    • 실무에선 특정 값이 n의 배수인지 확인하기 위해 사용한다.
    • product_id는 2의 배수여야한다던지… 등을 확인하기 위해!
  • POW는 x의 y승을 반환, GREATEST는 최댓값을 반환, LEAST는 최솟값을 반환한다.
COALESCE(purchase_date, 'Unknown') as purchase_date
  • COALESCE는 null 값을 대체하는 함수이다.
    • IFNULL(컬럼명, 대체값)을 이용해 NULL 값을 대체할 수도 있음
  • 두번째 인자로 컬럼명이 올 수 있음 → null 값을 해당 컬럼의 값으로 채움

DDL, DML

  • 테이블: 표 형태로 저장된 데이터 (행과 열로 구성)

  • 뷰: 가상의 테이블 (일부분만 본다던지, 다른 테이블과 결합한다던지 할 때 사용)

    • SELECT, JOIN 등을 해서 보는 테이블이 뷰인건가?

  • 인덱스: 원하는 데이터를 빠르게 찾기위한 목적으로 만드는 색인

  • DDL(Data Definition Language): 데이터베이스 구조를 정의하고 관리

    • CREATE: 테이블/뷰/인덱스 생성

    • ALTER: 테이블/뷰/인덱스 구조 변경

    • DROP: 테이블/뷰/인덱스 삭제

    • IF NOT EXIST를 이용해 ‘products’ 이름의 테이블이 없을 때만 create한다.

    • 절대 음수가 입력되지 않는 값은 unsigned로 지정

    • NOT NULL은 해당 컬럼의 값이 NULL 값이 될 수 없음을 의미한다.

    • varchar,char는 최대 길이를 인자로 받는다.

    • primary key는 테이블 내에 모든 행에 적용되는 고유한(unique) 아이디이다.

    • CHARSET을 이용해 테이블의 기본 문자 세트(문자 인코딩 방식)를 지정

    • sql 구문을 콘솔 하나에서 여러개 사용할 떈 세미콜론을 이용해 다른 ddl, dml 구문과 구별한다.
      - 왜 어떨 땐 세미콜론을 쓰고 어떨땐 안쓰는지 궁금했는데 이해됐다!

      ALTER TABLE product drop column price;
ALTER TABLE product add price int NULL;
ALTER TABLE products modify column price int NULL;
ALTER TABLE products CHANGE price price_2 int;

    • ALTER을 이용해 컬럼을 없애고(drop) 생성하고(add), 수정하고(modify) 컬럼 이름을 변경(CHANGE)할 수 있다.

      DROP TABLE products;

    • products 테이블을 삭제한다

  • DML(Data Manipulation Language): 데이터를 쿼리하거나 조작

    • SELECT: 질의

    • INSERT: 삽입

    • UPDATE: 수정

    • DELETE: 삭제

    • 테이블에 데이터를 추가

    • 특정 조건의 데이터의 값을 변경

    • 테이블에서 특정 레코드를 삭제

❗char과 varchar의 차이점

  • char는 고정된 공간을 차지
  • varchar는 입력되는 데이터에 따라 유동적으로 공간을 차지
    • 문자열보다 1 바이트를 더 차지함 (문자열 길이가 함께 저장됨)
    • 글자 길이를 계산하는 시간이 있어 데이터 삽입 시 시간이 조금 더 오래 걸릴 수 있다.
      • 데이터가 적을 땐 크게 문제되지 않을 수도 있지만, 데이터가 많으면 이 시간이 문제가됨
  • 길이가 고정된 문자열의 경우 char 사용, 길이가 유동적일 땐 varchar 이용

궁금한 점

  • datetime의 type은 string인가?
    • datetime 데이터 유형(DATETIME)이 있다고한다.
    • datetime과 문자열의 비교가 가능한가?
      • 대부분의 데이터베이스(시스템)가 자동으로 문자열을 데이트타임 타입으로 변환해 비교한다고 함
    • DATE_FORMAT의 반환값은 문자열이고 이를 숫자와 비교하기보단 문자열과 비교하는 게 일반적이라고 함
      • 몇몇 데이터베이스 시스템에서는 문자열과 숫자 간의 자동 형변환이 발생할 수 있다고 함
  • 뷰가 select, join등을 통해 조회한 테이블을 의미하는건가?
    CREATE VIEW employee_view AS
-- 아래 SELECT문이 view 조회할 때마다 실행된다.
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 1;
    • select는 단순 조회이고, view는 create view를 이용해 만들 수 있는 임시 테이블(?)인 것 같다.
    • 뷰를 조회할 때는 그냥 SELECT 문을 사용하는 것처럼 사용한다.
      -- view 조회
SELECT * FROM employee_view;
    • view를 조회할 때마다 위의 select문이 실행되는 것이라고 한다. 그렇기 때문에 저장 공간을 차지하지 않는다.
    • view를 조회할 때마다 select문이 실행되니 최신 데이터를 반영한다.
    • 그럼 단순히 쿼리(위의 SELECT문)를 저장하는 형식인건가?
      • 시스템 카탈로그(데이터 사전이라고도 함)에 저장된다고 한다.
profile
kiki
다음 포스트

데브코스 12일차 - SQL 문법

0개의 댓글