251230 [ Day 2 ] - SQL (1)

TaeHyun·2025년 12월 30일
sqlstudy

TIL

목록 보기
124/182

시작하며

오늘부터 본격적인 수업이 시작되었다. 오늘과 내일은 SQL에 대한 내용을 다루고 그다음에 Python을 배울 예정이다.

데이터베이스의 이해

데이터베이스

  • 데이터베이스(DB)는 다양한 형태의 데이터를 체계적으로 저장하고 관리하는 데이터의 집합
    • 데이터의 형태에는 문자열, 수치형(정수, 실수), 날짜시간 데이터 등이 포함
    • 데이터를 파일 형태(엑셀)로 저장하면 여러 사람과 공유할 수 있지만, 여러 사용자가 동시에 접근하고 수정하는 데 한계가 있음

데이터베이스 관리 시스템(DBMS)

  • DBMS는 대량의 데이터를 효율적으로 저장, 조회, 수정, 삭제하고 안정적으로 운영하기 위해 도입된 소프트웨어 프로그램
    • SQL(씨퀄)이라고도 부름
    • 대표적인 DBMS는 MySQL, MariDB, Oracle, SQL Server, SQLite 등

DBMS의 종류

  • 저장 구조에 따라 계층형 DB, 네트워크형 DB, 관계형 DB 등으로 구분되며, 관계형 DB(RDBMS)가 가장 많이 사용됨
    • RDBMS + Hadoop → Data Lake

관계형 데이터베이스

  • RDBMS는 Key와 Value의 관계를 확장하여, 데이터를 테이블 형태로 저장하는 구조
    • 데이터는 행과 열로 구성된 표 형태로 관리되며, 관계(병합, Merge, Join)의 개념을 기반
  • 관계형 데이터베이스의 테이블은 행(Row)과 열(Column)로 이루어진 2차원 구조
    • 행은 고객, 상품, 주문과 같이 서로 구분되는 하나의 엔티티(Entity)를 의미
    • 열은 해당 엔티티의 특성을 여러 차원에서 설명하는 속성
    • 테이블에서 각 행을 유일하게 식별할 수 있는 열을 기본키(Primary Key = PK)라고 함
    • 기본키는 중복이 불가하며, NULL 값을 가질 수 없음
  • 관계형 데이터베이스는 데이터 중복을 줄이고 저장 공간을 효율적으로 사용하기 위해 하나의 데이터를 여러 테이블로 분리하여 저장하는 정규화 구조를 사용
    • 여러 테이블을 연결할 때 기준이 되는 열을 외래키(Foreign Key = FK)라고 함

Data Warehouse

  • Metadata : 데이터의 데이터
  • Summary Data : 데이터 요약
  • Raw Data : 원형 데이터

SQL 기초

SQL

  • 구조화된 질의 언어(Structured Query Language, SQL)는 DBMS에서 데이터를 정의하고, 관리하며, 활용하기 위해 사용하는 특수 목적의 프로그래밍 언어
    • 소프트웨어를 개발한 회사에 따라 작은 차이가 있지만, 표준 SQL을 준수함

용도에 따른 구분

  • 데이터 정의어(DDL) : 데이터를 저장하는 테이블과 테이블 간의 관계로 이루어진 스키마를 정의하는 언어
    • CREATE : 데이터베이스 및 테이블 등 객체를 생성
    • DROP : 데이터베이스 및 테이블 등 객체를 삭제
    • ALTER : 테이블 등의 구조를 변경
  • 데이터 조작어(DML) : 테이블에 데이터를 삽입, 삭제, 수정, 조회하는 데 사용하는 언어
    • INSERT : 테이블에 행을 삽입
    • DELETE : 테이블의 행을 삭제
    • UPDATE : 테이블의 내용을 수정
    • SELECT : 테이블에서 데이터를 조회
  • 데이터 제어어(DCL) : 데이터베이스 사용자에 대한 권한을 제어(부여, 회수)하는 언어
    • GRANT : 테이블 사용 권한을 부여
    • REVOKE : 테이블 사용 권한을 회수

SQL 특징

  • 키워드의 대소문자를 구분하지 않음
    • 하지만 대문자 사용 권장
  • 들여쓰기를 하지 않아도 실행에 문제 없음
  • 컴파일 과정이 필요 없음
  • 선언적인 언어로, 무엇을 가져올지(데이터 조회 O / 분석 X)를 기술하는 데 중점
  • 변수와 자료형을 명시적으로 선언하지 않고도 데이터를 조회, 처리 가능

SQL 기본 문법

  • SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
  • WHERE 절은 FROM 절 다음에 위치
  • HAVING 절은 GROUP BY 절 다음에 위치
  • ORDER BY 절은 WHERE 절 또는 HAVING 절 다음에 위치

주요 필드명 접미사

접미사의미상세 내용
_cdcode코드
_nmname이름 또는 명칭
_nonumber번호(식별값, 연산 X)
_idid아이디
_dtdate날짜, 시간까지 포함하는 경우 _dtm 또는 _ts 사용
_gbgubun구분값
_cntcount개수
_amtamount금액
_sumsum수치형의 합계
_avgaverage수치형의 평균

SQL 실습

현업에서는 주석처리를 간편하게 하기 위해 , 을 앞에 사용

SELECT species
	, COUNT(*) as cnt
    , SUM(sepal_length) as sum
    , AVG(sepal_length) as avg
from IRIS
GROUP by species;
  • 기본 조회
SELECT * FROM IRIS;
select * from IRIS LIMIT 10;
SELECT sepal_length, species FROM IRIS;
  • 필터링
SELECT * FROM IRIS WHERE sepal_length >= 5;

SELECT COUNT(*) AS cnt FROM IRIS WHERE sepal_length >= 5 AND sepal_length <= 7;
SELECT COUNT(*) as cnt FROM IRIS WHERE sepal_length BETWEEN 5 AND 7;

SELECT COUNT(*) as cnt FROM IRIS WHERE species = 'versicolor' or species = 'virginica';
SELECT COUNT(*) as cnt FROM IRIS WHERE species in ('virginica', 'versicolor');
  • 파생변수 생성
    • WHERE 절에서는 파생변수 사용이 기본적으로는 불가능
SELECT *, sepal_length / sepal_width as sepal_ratio From IRIS;

SELECT *, sepal_length / sepal_width as sepal_ratio
FROM IRIS
WHERE (sepal_length / sepal_width) >= 1.5;
  • 정렬
SELECT * FROM IRIS ORDER by sepal_length;
SELECT * from IRIS ORDER by sepal_length DESC;

SELECT * from IRIS ORDER by sepal_length, petal_width;
SELECT * from IRIS ORDER by sepal_length DESC, petal_width;
  • 집계 함수로 요약
SELECT COUNT(*) as cnt FROM IRIS;
SELECT species, COUNT(*) as cnt from IRIS GROUP by species;

SELECT species, SUM(sepal_length) as sum from IRIS GROUP by species;
SELECT species, AVG(sepal_length) as avg from IRIS GROUP by species;

SELECT species
	, AVG(sepal_length) / AVG(sepal_width) as sepal_ratio
from IRIS
group by species
HAVING AVG(sepal_length) / AVG(sepal_width) >= 1.5
ORder by sepal_ratio DESC;
  • 테이블 생성
CREATE TABLE users(
	id INT NOT NULL PRIMARY key,
  	name VARCHAR(10),
  	age INT
);
  • 테이블 조회
select * from users;
  • 행 추가
INSERT INTO users values(1, '김현수', 28);
INSERT INTO users values(2, '이서현', 32);
INSERT INTO users values(3, '박지민', 27);
  • 값 변경
UPDATE users SET name = '최민석' WHERE age < 30;
  • 특정 행 삭제
DELETE from users WHERE age = 27;
  • 테이블 삭제
drop table users;

SQL 연산자

산술 연산자

  • a + b : a와 b를 더하기
  • a - b : a에서 b를 빼기
  • a * b : a와 b를 곱하기
  • a / b : a를 b로 나누기
  • a % b : a를 b로 나눈 나머지 반환
  • 실행 결과 소수점 이하가 0이면 정수로 반환
  • 정수를 정수로 나누면 항상 정수로 몫을 반환
  • 실수를 포함한 나눗셈에서는 나누어 떨어지면 정수, 그렇지 않으면 실수로 반환
SELECT sepal_length, sepal_length + 2 from IRIS LIMIT 10;
SELECT sepal_length, sepal_length - 2 from IRIS LIMIT 10;
SELECT sepal_length, sepal_length * 2 from IRIS LIMIT 10;
SELECT sepal_length, sepal_length / 2 from IRIS LIMIT 10;
SELECT sepal_length, sepal_length % 2 from IRIS LIMIT 10;

데이터 타입 변환 함수 : CAST()

  • 정수를 실수로 변환할 때 REAL(또는 FLOAT) 지정
SELECT 5 / CAST(2 AS REAL);
  • 실수를 정수로 변환할 때 INTEGER(또는 INT)를 지정
SELECT 5.0 / CAST(2.0 AS INTEGER);

비교 연산자

  • NULL은 값이 없는 상태를 의미
SELECT 5 >= 2;
SELECT 5 > 2;
SELECT 5 <= 2;
SELECT 5 < 2;
SELECT 5 = 2;
SELECT 5 != 2;
SELECT 5 is NULL; -- <> 사용 가능
SELECT 5 is NOT NULL;

SELECT COUNT(*) from IRIS where sepal_length >= 5;
SELECT COUNT(*) from IRIS_NULL where sepal_length >= 5;
SELECT COUNT(*) from IRIS_NULL WHERE sepal_length IS NULL;

논리 연산자

  • ‘A’ LIKE ‘B’ : 문자열 ‘A’와 ‘B’가 정확하게 일치하면 1, 아니면 0을 반환(와일드 카드 사용 가능)
  • ‘A’ GLOB ’B’ : LIKE와 비슷하지만 대소문자를 구분하며 와일드 카드 사용 불가능
SELECT 1 = 1 and 2 != 2;
SELECT 1 = 1 or 2 != 2;

SELECT 'ABC' like 'ABC'; -- 1
SELECT 'ABC' like 'Abc'; -- 1
SELECT 'ABC' glob 'Abc'; -- 0

SELECT 'ABC' like 'A'; -- 0
SELECT 'ABC' like 'A%'; -- 1
SELECT 'ABC' like 'A_'; -- 0
SELECT 'ABC' like 'A__'; -- 1

집합 연산자

  • UNION : 두 쿼리 결과에서 중복을 제거하고 하나로 결합(합집합)
  • UNION ALL : 두 쿼리 결과에서 중복을 제거하지 않고 하나로 결합
  • INTERSECT : 두 쿼리 결과에서 중복인 행을 선택(교집합)
  • EXCEPT : 첫 번째 쿼리 결과에서 두 번째 쿼리 결과를 제외(차집합)
SELECT * from IRIS WHERE sepal_length <= 4.4
UNION
SELECT * from IRIS_NULL WHERE sepal_length <= 4.4;

SELECT * from IRIS WHERE sepal_length <= 4.4
UNION ALL
SELECT * from IRIS_NULL WHERE sepal_length <= 4.4;

SQL 함수

수학 함수

  • ABS(필드명) : 절대값 반환
  • CEIL(필드명) : 소수점 올림 값 반환
  • FLOOR(필드명) : 소수점 내림 값 반환
  • ROUND(필드명, 소수점 자리수) : 지정한 소수점 자리수에서 반올림한 값 반환
  • RANDOM() : 매우 큰 범위의 정수를 무작위로 반환
  • POWER(필드명, 거듭제곱) : 거듭제곱한 값 반환
  • SQRT(필드명) : 양의 제곱근을 반환
  • EXP(필드명) : 네이피어 상수(e)를 필드 원소로 거듭제곱한 값을 반환(지수 함수)
  • LOG(필드명) : 네이피어 상수(e)를 밑으로 하는 필드 원소의 로그 값을 반환
SELECT sepal_length, CEIL(sepal_length) FROM IRIS LIMIT 10;
SELECT sepal_length, FLOOR(sepal_length) FROM IRIS LIMIT 10;
SELECT sepal_length, ROUND(sepal_length, 0) FROM IRIS LIMIT 10;
SELECT sepal_length, POWER(sepal_length, 2) FROM IRIS LIMIT 10;
SELECT sepal_length, ROUND(POWER(sepal_length, 2), 1) as sepal_length_squared FROM IRIS LIMIT 10;

SELECT sepal_length, SQRT(sepal_length) FROM IRIS LIMIT 10;
SELECT sepal_length, EXP(sepal_length) FROM IRIS LIMIT 10;
SELECT sepal_length, LOG(sepal_length) FROM IRIS LIMIT 10;

문자열 함수

  • LENGTH(필드명) : 글자 개수 반환
  • REPLACE(필드명, 패텅, 대체값) : 패턴을 대체값으로 변경
  • SUBSTR(필드명, 시작, 길이) : 시작위치부터 지정한 길이만큼의 부분 문자열 반환
    • 시작 인덱스는 1이고, 길이 생략시 마지막 글자까지
  • INSTR(필드명, 탐색할 문자열) : 탐색할 문자열의 시작 위치를 반환
  • TRIM(필드명, 삭제할 문자열) : 양 끝에서 삭제할 문자열(생략시 공백)을 삭제
  • LOWER(필드명) : 소문자로 변환
  • UPPER(필드명) : 대문자로 변환
  • CONCAT(필드명, 필드명, …) : 서로 대응하는 원소를 하나의 문자열로 결합
SELECT species, LENGTH(species) from IRIS GROUP by species;
SELECT species, REPLACE(species, 's', 'v') FROM IRIS GROUP by species;
SELECT species, SUBSTR(species, 1, 3) FROM IRIS GROUP by species;
SELECT species, INSTR(species, 's') FROM IRIS GROUP by species;
SELECT species, TRIM(species, 'v'), TRIM(species, 'a') FROM IRIS GROUP by species;
SELECT species, UPPER(species) from IRIS GROUP by species;
SELECT CONCAT(species, ' : ', sepal_length) FROM IRIS GROUP by species;

SUBSTR 에서 INSTR 사용

SELECT species, SUBSTR(species, 1, INSTR(species, 's')-1) FROM IRIS GROUP by species;

제어 함수

  • COALESCE(필드명, 대체값) : 지정한 값 중 결측이 아닌 첫 번째 값 반환
    • COALESCE(필드명1, 필드명2, 대체값)
    • 모두 NULL이면 NULL 반환
  • IFNULL(필드명, 대체값) : 결측이 아니면 원본, 결측이면 대체값 반환
  • NULLIF(필드명, 비교값) : 비교값과 다르면 첫 번째 값, 같으면 NULL 반환
    • NULLIF(필드명1, 필드명2)
    • 모두 NULL이면 NULL 반환
SELECT sepal_length, COALESCE(sepal_length, 0) from IRIS_NULL;
SELECT sepal_length, sepal_width, COALESCE(sepal_length, sepal_width, 0) from IRIS_NULL;
SELECT sepal_length, IFNULL(sepal_length, 0) from IRIS_NULL;
SELECT sepal_length, NULLIF(sepal_length, 5) from IRIS_NULL;
  • IIF(조건, 참값, 거짓값) : 조건을 만족하면 참값, 만족하지 않으면 거짓값 반환(구간화)
    • 구간화 : 연속형데이터를 범주형 데이터로 바꾸는 것
SELECT sepal_width
	, IIF(sepal_width > 3.5, 'L', 'S')
from IRIS;

SELECT sepal_width
	, IIF(sepal_width > 3.5, 'L', IIF(sepal_width > 3, 'M', 'S'))
from IRIS;
  • CASE WHEN : IIF() 를 확장한 구문
SELECT sepal_width
	, CASE WHEN sepal_width > 3.5 THEN 'L' ELSE 'S' END as grade
from IRIS;

SELECT sepal_width
	, CASE WHEN sepal_width > 3.5 THEN 'L'
    	   WHEN sepal_width > 3.0 THEN 'M'
           ELSE 'S' END as grade
from IRIS;

마치며

첫 수업이었는데 현업에서 실제로 사용하는 방식들을 배울 수 있어서 좋았고, 저번 과정보다 수업 속도나 양이 훨씬 빠르게 느껴져서 한 번 배워봤던 내용들이라 정말 다행이었다. 다른 내용들도 복습을 꾸준히 하지 않으면 따라가기 힘들 것 같으니 열심히 해야겠다.

profile
TaeHyun
Hello I'm TaeHyunAn, Currently Studying Data Analysis
이전 포스트

251229 [ Day 1 ] - OT, 제조업 특강

다음 포스트

251231 [ Day 3 ] - SQL (2)

0개의 댓글