[TIL] 데이터분석 데브코스 11일차 - SQL/RDBMS/No-SQL/SELECT/FROM/WHERE/DDL/DML

📌 학습 주제

1. 데이터 조회 구문 : SELET/FROM/WHERE
2. SQL 연산자 : 비교연산자/논리연산자
3. SQL 함수 : 정렬함수/집계함수/문자열함수/숫자함수
4. DDL/DML

 

 

 

---

 

데이터 조회 : SELECT, FROM, WHERE

SQL문 = SQL 쿼리

(** 쿼리 : 질의하다.)

 

 

 

SELECT 컬럼명 : 무엇을 가져올 지 지정해주는 표현

- 여러 개의 컬럼을 가져오고 싶을 때 콤마(,) 을 활용해서 구분해준다. 컬럼은 쿼리를 작성한 순서대로 결과에 나타난다.

- 모든 컬럼을 가져오고 싶을 때, * 을 사용하면 된다.

※ 단, 행과 열 많아 테이블의 규모가 크다면, *을 사용할 경우 불필요하게 많은 리소스를 사용할 수 있으므로 주의하자!

따라서 실무에서는 *을 사용하기 전에 테이블 스키마를 미리 확인한 뒤(어떤 컬럼이 있으며 타입은 무엇인지), 필요한 컬럼만 지정해서 사용하는 것을 권장한다.

 

 

FROM 테이블명 : 어디에서 가져올지 지정해주는 표현

SELECT name, category
FROM products

 

 

WHERE 조건 : 어떤 조건으로 가져올지 지정해주는 표현

- 조건에 일치하는 경우만 필터링해서 해당하는 행을 가져온다.)

항상 참인 조건을 사용하는 이유는 코드 작성에 잇어서 편의성을 가져가기 위함이다. 

SELECT product_id, category
FROM products
WHERE 1=1
-- AND category = '주방용폼'
AND price > 5000

 

 

-- : 주석처리 (코드 실행시에는 무시되는, 프로그래머가 참고용으로만 작성해둔 코멘트)

 

 

LIMIT 행개수 : 결과물을 몇개까지 반환할지 개수를 제한하는 표현 (상위 몇개 행만 필터링할지 정해준다.)

MySQL 기준

 

MSSQL에서는 TOP, Oracle에서는 Lownum이라는 키워드로 대체된다.

 

※ 같은 관계형 데이터베이스라도 구체적으로 사용하는 DB 언어 종류에 따라 세부적인 키워드가 다를 수있다.

 

 

 

 

 

 

 

비교 연산자

WHERE절에서 사용된다.

 

등호 연산자 =  : 양쪽의 값이 같은지를 비교한다.

 

부등호 연산자 >, <, >=, <=  : 왼쪽 값이 오른쪽 값보다 큰지, 작은지, 크거나 같은지, 작거나 같은지를 비교한다.

 

부등호 연산자 <>, !=  : 왼쪽 값과 오른쪽 값이 서로 다른지를 비교한다.

 

 

 

 

 

 

논리 연산자

AND : 2개 이상의 조건이 모두 참일 때만 참을 반환한다.

 

 

OR : 여러 조건 중 한 가지만 참이어도 참을 반환한다.

 

※ 쿼리에서의 괄호 ( )는 수학적 개념과 동일하게 괄호 안에 있는 조건들끼리 묶어서 본다는 의미이다.

괄호 안의 연산을 먼저 수행하고, 그 다음 나머지 괄호 밖의 연산을 수행하면 된다.

 

SELECT name, price
FROM products
WHERE 1=1
AND price < 12000
AND (category = '주방용품' OR name = '손목보호대')

→ 'products' 테이블에서

가격이 '12000원 미만'이면서

품목이 '주방용품'이거나 이름이 '손목보호대'인

'name'과 'price'를 반환한다.

 

 

NOT : 참/거짓을 뒤집어서 반환한다. (참 → 거짓, 거짓 → 참)

 

IN : 왼쪽 값이 우측 괄호 안에 포함될때만 '참'을 반환한다.

SELECT product_id, name
FROM products
WHERE 1=1
AND category NOT IN ('스포츠', '디지털', '식품')

→ 'products' 테이블에서

'category' 값이 '스포츠', '디지털', '식품'에 포함되지 않는

'product_id'와 'name'을 반환한다.

 

 

LIKE

: 문자열의 패턴을 검색하는데 사용된다.

 

- case-sensitive 하다 (=대소문자 구별)

 

% , * : 모든 문자열을 뜻한다.

ex. %중앙% 또는 *중앙* 의 의미는 문자열에 '중앙'이 포함된 모든 문자열을 뜻한다.

'중앙'이라는 문자열 앞/뒤로 어떤 문자열이 오든지 관계없이 포함하고 있으면 모두 반환하라는 뜻

 

SELECT product_id, name
FROM products
WHERE 1=1
AND name LIKE '%보호%'

→ 'products' 테이블에서

문자열 타입의 컬럼인 'name'의 값에 '보호'라는 문자열이 포함된

'product_id'와 'name'을 반환한다.

 

SELECT prodcut_id, name
FROM products
WHERE 1=1
AND name LIKE '밥%'

→ 'products' 테이블에서

문자열 타입의 컬럼인 'name'의 값이 '밥' 이라는 문자열로 시작하는

'product_id'와 'name'을 반환한다.

 

 

 

 

ILIKE

- LIKE와 같은 기능

- case-insensitive 하다 (=대소문자 구별X)

 

 

_ (언더바) : 한 개의 문자를 뜻한다.

SELECT prodcut_id, name
FROM products
WHERE 1=1
AND name LIKE '_그릇'

→ 'products' 테이블에서

문자열 타입의 컬럼인 'name'의 값이 '그릇'라는 문자 앞에 한 글자 짜리의 문자만 포함된

'product_id'와 'name'을 반환한다.

 

밥그릇 (O)

개밥그릇 (X)

 

 

 

BETWEEN A and B : (양쪽 끝 값 A, B를 포함하면서) A와 B사이의 모든 값들을 반환한다.

- 숫자 뿐만 아니라 문자열에도 사용된다.

 

IS NULL : 컬럼값이 비어있는지를 검사한다.

IS NOT NULL : 컬럼값이 비어있지 않은지(모두 채워져 있는지)를 검사한다.

 

** (null) : 테이블 내 특정 값이 비어있음 을 의미한다.

 

 

 

 

 

 

 

 

정렬 함수

ORDER BY 컬럼명 ASC/DESC : 특정 열의 값을 기준으로 데이터를 오름차순(ASC) or 내림차순(DESC)으로 정렬한다.

- Default(기본값)은 오름차순(ASC)이므로 생략할 수 있다.

- 숫자 뿐만 아니라 문자열에 대해서도 정렬 가능하다. (한글은 가나다 순, 영어는 알파벳 순)

- 여러 기준으로 정렬하고자 하는 경우 콤마(,)로 구분해서 정렬한다.

 

※ 컬럼명 대신 숫자 표현을 쓸 수도 있는데, 각 숫자의 의미는 최종 결과에서의 컬럼 순서를 나타낸다.

즉 SELECT문에서의 컬럼명에 각각 번호를 부여한 것과 같다.

해당 번호에 맞게 정렬하면 된다!

SELECT price, name
FROM products_v2
WHERE 1=1
ORDER BY 1 DESC, 2

→ 'products_v2' 테이블에서

'price' 컬럼의 값들은 내림차순으로, 'name' 컬럼의 값들은 오름차순으로 정렬한 후

'price'와 'name'을 반환한다.

 

 

 

Ordering by multipel columns

ORDER BY 1 DESC, 2, 3

(SELECT문에서의 컬럼명 순서와 동일)

 

NULL값 순서

• ASC(오름차순)일 경우 : 마지막에 위치
• DESC(내림차순)일 경우 : 처음에 위치
• NULLS FIRST : NULL값을 처음에 위치시킴
• NULLS LAST : NULL값을 마지막에 위치시킴

 

 

 

 

집계 함수

: 여러 행으로부터 하나의 결괏값을 반환하는 함수

열에 대한 연산을 수행한다.

 

SUM(컬럼명) : 컬럼값 전체의 합을 반환한다.

 

AVG(컬럼명) : 컬럼값 전체의 평균을 반환한다.

 

컬럼명 AS 별칭 : 해당 컬럼명이 지정해준 새로운 컬럼명(=별칭; Alias)으로 반환된다.

 

SELECT SUM(price) AS sum_price, AVG(price) AS avg_price
FROM products_v2
WHERE 1=1

→ 'products_v2' 테이블에서

'price' 컬럼값 전체 합을 'sum_price'라는 새로운 컬럼명으로,

'price'컬럼값 전체의 평균을 'avg_price'라는 새로운 컬럼명으로 반환한다.

 

 

 count()

: 주어진 인자의 값이 NULL이면 0, NULL이 아니면 1씩 더하는 방식으로 조건에 맞는 행 개수를 반환한다.

 

• count(1) : 컬럼에 null값이 포함되어 있는지의 여부와 관계없이 모든 행 개수를 반환한다.
                  (∵ 특정 컬럼을 지정한 것이 아니기 때문!)
• count(0) : 모든 행의 개수를 반환 (count(1)과 동일)
• count(*) : 모든 행의 개수를 반환 (count(1)과 동일)
• count(컬럼명) : 컬럼에 있는 null값은 제외한 행의 개수를 반환
• count(NULL) : 0을 반환

SELECT count(1) as cnt
FROM products_v2
WHERE 1=1

(1=1이라는 항상 참인 조건밖에 없으므로)

→ 'products_v2' 테이블의 전체 행 개수를 반환한다.

 

 

컬럼값에 중복이 있는지 여부는 고려하지 않는다.

- count(distinct 컬럼명) : 해당 컬럼의 unique 한 값(=중복이 없는 값)들의 행 개수를 반환한다.

SELECT count(distinct price) as unique_price_cnt
FROM products_v2
WHERE 1=1

→ 'products_v2' 테이블에서

'unique_price_cnt'라는 새로운 컬럼명으로

'price' 컬럼의 중복이 없는 전체 행 개수를 반환한다.

 

 

집계 함수는 필터링이 적용된 후에 집계 연산을 수행한다.

즉, WHERE 이하의 조건절이 있다면 해당 조건을 모두 만족하는 행들만 필터링한 후에 집계함수를 특정 컬럼에 적용시킨다.

SELECT count(1) as some_cnt
FROM products_v2
WHERE 1=1
AND category like '%용품'

→ 'products_v2' 테이블에서

'category' 컬럼값이 '용품'으로 끝나는 문자열이 있는 행에 대해서만 필터링하여

'some_cnt'라는 새로운 컬럼명으로 전체 행 개수를 반환한다.

 

 

 

GROUP BY : 

- SELECT문에 그룹으로 묶을 기준이 되는 컬럼명을 먼저 써준다.

SELECT category, count(1) as sales_cnt
FROM products_v3
WHERE 1=1
AND sale_yn = 'yes'
GROUP BY 1
GROUP BY 2 DESC, 1

→ 'products_v3' 테이블에서

'sale_yn' 컬럼값이 'yes'인 행에 대해서만 필터링하여

1번째에 해당하는 'category'별로

'category'값과 'sales_cnt'라는 새로운 컬럼명으로 각 'category'별 전체 행 개수를 반환하되,

결과에서 2번째에 해당하는 'sales_cnt'를 내림차순으로, 1번째에 해당하는 'category'를 오름차순으로 정렬하여 반환한다.

 

 

HAVING : 각 그룹에 대해서 필터링한다.

SELECT category, sale_yn, avg(price) as avg_price
FROM products_v3
WHERE 1=1
GROUP BY 1, 2
HAVING avg_price > 3000

→ 'products_v3' 테이블에서

1번째에 해당하는 'category'와 2번째에 해당하는 'sale_yn'에 대해 그룹화 하되

'avg_price'가 3000보다 큰 행에 대해서만

'category', 'sale_yn' 그리고 'price' 컬럼값 전체의 평균을 'avg_price'란 새로운 컬럼명으로 반환한다.

 

 

WHERE vs. HAVING

WHERE	HAVING
그룹화를 하기 전에 필터링한다.	그룹화 이후, 그룹화된 결과에 대해서 필터링한다.

SELECT category, sale_yn, avg(price) as avg_price
FROM products_v3
WHERE 1=1
AND price > 3000
GROUP BY 1, 2

→ 'products_v3' 테이블에서

'price'가 3000보다 큰 행에 대해서만 필터링한 후

1번째에 해당하는 'category'와 2번째에 해당하는 'sale_yn'에 대해 그룹화 하되

'category', 'sale_yn' 그리고 'price' 컬럼값 전체의 평균을 'avg_price'란 새로운 컬럼명으로 반환한다.

 

 

 

 

 

 

 

문자열 함수

CONCAT(컬럼명1, 구분자, 컬럼명2) : 여러 컬럼의 문자열 값들을 하나의 컬럼으로 합쳐준다. (각 컬럼의 데이터 형태는 무관하다.)

SELECT CONCAT(category, '-', name) as comb_name
FROM products
WHERE 1=1
LIMIT 3

→ 'products'테이블에서

'category'컬럼값과 'name'컬럼값을 대쉬(-)로 이어서 합쳐준 값을

'comb_name'이라는 새로운 컬럼명으로

상위 3개 데이터만 반환한다.

 

REPLACE(문자열, 문자열a, 문자열b)

: 주어진 문자열에서 문자열a를 찾아서 문자열b로 대체시키는 함수

 

 

 

SUBSTRING(컬럼명, 시작인덱스, 추출길이)

 

MySQL에서는 아래와 같이 사용한다.

SUBSTR(컬럼명, 시작인덱스, 추출길이) : 문자열을 잘라서 일부분만 변환하는 함수로 3개의 인자를 받는다.

LEFT(컬럼명, 추출길이) : 왼쪽 끝에서부터 추출 길이만큼 문자열을 잘라서 추출한다.

RIGHT(컬럼명, 추출길이) : 오른쪽 끝에서부터 추출 길이만큼 문자열을 잘라서 추출한다.

SELECT SUBSTR(name, 1, 2) as sub_string
FROM products
WHERE 1=1

→ 'products' 테이블에서

'name'컬럼값의 1번째 인덱스에 해당하는 값부터 길이 2에 해당하는 값까지(인덱스 2까지)만

'sub_string'이라는 새로운 컬럼명으로 반환한다.

 

SELECT LEFT(name, 3) as left_sub_string
FROM products
WHERE 1=1

→ 'products' 테이블에서

'name'컬럼값의 왼쪽 끝에서부터 길이 3에 해당하는 값까지(인덱스 3까지)만

'left_sub_string'이라는 새로운 컬럼명으로 반환한다.

 

 

UPPER(문자열) : 영어 문자열에서 각 알파벳을 대문자로 통일하여 반환한다.

LOWER(문자열) : 영어 문자열에서 각 알파벳을 소문자로 통일하여 반환한다.

SELECT UPPER('John') as up, LOWER('AMY') as low

→ 'John'의 각 알파벳을 모두 대문자로 통일하여 'up'이라는 새로운 컬럼명으로,

'Amy'의 각 알파벳을 모두 소문자로 통일하여 'low'라는 새로운 컬럼명으로 반환한다.

 

 

LEN(문자열), CHAR_LENGTH(컬럼명) : 문자열의 길이를 반환한다.

 

LPAD : 문자열의 왼쪽에 문자열을 패딩(붙여줌)해준다.

RPAD : 문자열의 오른쪽에 문자열을 패딩(붙여줌)해준다.

 

 

숫자 함수

ROUND(컬럼명, 자릿수) : 괄호 안의 값을 반올림하여 반환한다.

※ 자릿수는 0이 기본으로, 생략가능하다. 이 경우 정수값이 반환된다.

※ 자릿수는 소수점 아래 해당 자릿수만큼 남기겠다는 의미로,

예를 들어 2를 입력했다면 소숫점 아래 3번째 수에서 반올림을 한 값이 반환된다.

 

CEIL() : 괄호 안의 값을 올림하여 반환한다.

FLOOR() : 괄호 안의 값을 내림하여 반환한다.

 

TRUNCATE(컬럼명, 자릿수) : 특정 자릿수 이하를 잘라서 버린 값을 반환한다. (ROUND()와 사용 방식이 유사하다)

※ 자릿수는 0이 기본으로, 생략가능하다. 이 경우 정수값이 반환된다.

※ 자릿수는 소수점 아래 해당 자릿수만큼 남기겠다는 의미로,

예를 들어 2를 입력했다면 소숫점 아래 3번째 수부터 값을 버린다.

 

ABS() : 괄호 안의 값에 절댓값을 취한 값이 반환된다.

 

MOD(분자, 분모) : 첫번째 인자(분자)를 두번째 인자(분모)로 나눈 나머지를 반환한다.

 

특정 숫자의 배수인지 검사하는 코드가 필요할 경우가 있다.

나눗셈에서는 0으로 나누는 연산이 불가능하다.

 

POW(x, y) : 두 인자 x, y를 받아서 x의 y승 (= x^y) 값을 반환한다.

 

GREATEST() : 함수에 들어온 인자들 중 최댓값을 반환한다.

 

LEAST() : 함수에 들어온 인자들 중 최솟값을 반환한다.

 

COALESCE(컬럼명, 채워넣을 값/컬럼명) : 특정 컬럼의 값이 비어있을 때, 해당 빈칸을 특정 값이나 같은 행의 다른 컬럼의 값으로 채워준다.

-- (1) 특정 값으로 채워넣은 경우
SELECT COALESCE(purchase_date, 'Unknown') as purchase_date
FROM orders

→ 'orders'테이블에서

'purchase_date' 컬럼의 비어있는 값은 'Unknown'이라는 문자열로 채워넣어

'purchase_date'라는 새로운 컬럼명으로 반환한다.

 

-- (2) 같은 행의 다른 컬럼 값으로 채워넣은 경우
SELECT COALESCE(purchase_date, product_name) as purchase_date
FROM orders

→ 'orders'테이블에서

'purchase_date' 컬럼의 비어있는 값은 같은 행의 'product_name'컬럼값으로 채워넣어

'purchase_date'라는 새로운 컬럼명으로 반환한다.

 

 

 

 

 

DDL (Data Definition Language) 데이터 정의어

: 데이터베이스 구조를 정의하고 관리하는 데 사용되는 언어

 

** 테이블(Table) : 표 형태로 저장된 데이터로, 테이블마다 이름을 갖고 있으며 행과 열로 구성되어 있다.

** 뷰(View) : 일종의 가상 테이블로, 기존 테이블의 일부분만 혹은 다른 테이블과의 결합된 형태로 보여주고 싶을 때 사용한다.

** 인덱스(Index) : 데이터베이스에서 원하는 데이터를 빠르게 찾기 위한 색인이다.

 

1. CREATE

: 테이블/뷰/인덱스를 생성한다.

CREATE TABLE IF NOT EXISTS '테이블명'(
'컬럼명' 컬럼타입 NOT NULL
'컬럼명' 컬럼타입 NOT NULL
.
.
)

CREATE TABLE IF NOT EXISTS : 해당 테이블이 없을 때에만 생성하겠다는 의미

 

NOT NULL : 해당 컬럼에 (null)값이 포함되면 안된다는 것을 의미

 

unsigned : 음수를 사용하지 않겠다는 의미

(∵ 음수를 사용하지 않으면, 음수에 할당되어야 할 공간이 양수값에 쓰일 수 있기 때문에 기존 signed 타입으로 표현할 수 있는 최대치의 2배 더 큰 숫자까지 정수타입으로 표현할 수 있다. 계속 증가하기만 하거나 절대 음수가 되지 않는 값들을 표현할 때 사용하면 좋다. 단, 최댓값이 지정되어 있어서 2^32 - 1 까지의 값들만 허용된다. )

 

int : 정수

varchar(받을 문자열의 길이) : 가변 길이의 문자열 (문자열이지만 길이를 조절해서 저장할 수 있는 형태)

char(받을 문자열의 길이) : 문자열 길이에 따라 저장공간을 달리하여 사용하는 것이 불가능한 형태

고정된 공간을 차지함

char(6)일때, 6글자보다 짧은 문자를 넣어도 나머지는 빈공간으로 채워진다. 글자가 넘어간다면 자름

varchar(6)일때,  입력되는 글자의 길이에 따라 다르게 공간을 할당한다. 시작은 1byte이다.

즉, 'ab'를 입력할경우 3byte이다. (어디까지문자열데이터가 들어가있느지 컴퓨터가 파악할 수 있도록 )

데이터양이 많을때는 varchar에서 시간이 더 걸릴 수 있다.

 

PRIMARY KEY() : 테이블 내 모든 행에 적용되는 고유한 아이디

테이블 내에서 정확하게 몇번쨰 행인지를 식별할 수 있다. 각 행에 대한 주미등록번호와 같은 개념

 

행마다 모두 다르 값이어야 해당 컬럼이 PRIMARY KEY가 된다.

 

DEFAULT CHARSET=utf8 : 테이블의 기본 문자 세트를 지정해줌. 

utf8 : 한글을 입력할 것이기 때문에 unicode문자 세트를 사용하겠다는 의미

 

; : 하나의 콘솔에서 여러개의 SQL 구문을 사용하려면 ;으로 구분지어주어야한다.

 

CREATE TABLE IF NOT EXISTS 'products'(
'product_id' int unsigned NOT NULL,
'category' varchar(40) NOT NULL,
'name' varchar(50) NOT NULL,
'price' int unsigned NOT NULL,
PRIMARY KEY ('product_id')
)DEFAULT CHARSET=utf8;

 

 

 

 

 

 

2. ALTER

: 테이블/뷰/인덱스에 컬럼을 추가하거나 삭제, 컬럼명 변경 등 데이터베이스 내의 객체들을 변경한다.

 

ALTER TABLE products drop column price;

→ 'products' 테이블에서 'price'라는 컬럼을 삭제한다.

 

ALTER TABLE products add price int NULL;

→ 'products' 테이블에서 NULL값도 올 수 있도록 int 타입의 데이터를 받는 'price'컬럼을 추가한다.

 

 

ALTER TABLE products CHANGE price price_2 int;

→ 'products' 테이블에서 int 타입의 데이터를 받는 'price'라는 컬럼을 'price_2'라는 새로운 컬럼명으로 바꿔준다.

 

3. DROP

: 테이블/뷰/인덱스 객체를 삭제한다.

DROP TABLE products;

→ 'products' 테이블 자체를 삭제한다.

 

 

 

 

 

 

DML (Data Manipulation Language) 데이터 조작어

: 데이터를 쿼리에서 가져오거나 조작하는 데 사용되는 언어

 

1. SELECT

: 데이터를 원하는 형태로 가져온다.

 

2. INSERT

: 생성되어있는 테이블에 데이터를 삽입한다.

INSERT INTO '테이블명' (컬럼명1, 컬럼명2, ... ) VALUES
	(값1, 값2, ...),
    (값1, 값2, ...), 
    (값1, 값2, ...),
    .
    .
    .
    (값1, 값2, ...);

 

 

3. UPDATE

: 이미 존재하는 데이터(레코드)를 수정한다.

UPDATE products
SET price = 20000
WHERE price = 15000;

→ 'products' 테이블에서

'price' 컬럼값이 15000인 데이터를 찾아서 해당 값을 20000으로 수정한다.

 

 

4. DELETE

: (테이블 전체를 삭제하는 DROP과 달리,) 테이블 내 특정 데이터(레코드)를 삭제한다.

DELETE FROM products
WHERE price = 15000;

→ 'products' 테이블에서

'price' 컬럼값이 15000인 데이터를 삭제한다.

 

 

 

 

 

---

📌 어려웠던 내용

SQL 함수나 쿼리문은 SQLD자격증 공부를 하면서 많이 다루었던 부분이라 익숙한데
DDL과 DML 파트가 어려웠다.
특히 테이블을 생성하는 CREATE TABLE 안에 포함된 여러가지 함수들/데이터타입/구문 등이 처음 보는 것들이 많아 익히는데 오랜 시간일 걸렸다. 이부분은 추가로 검색해보면서 더 공부해야할 것 같다.