SQL

매일 공부(ML)·2021년 11월 15일
CS
0

CS 

목록 보기
26/33

SQL

  • 정의: DB에서 데이터 조회할 때 필요한 컴퓨터 언어

  • 예시:

    • 데이터 조회 및 조작파트 담당
    • SELECT * FROM 도서대출내역

  • 기본 구조:
- Like:  해당 문자를 포함 혹은 시작 또는 종료하는 것을 불러온다

- "문%" : 문으로 시작하는 모든 문자열 가져오기

- '%문%' : 문이 존재하는 모든 문자열 가져오기

- '%문' : 문으로 끝나는 모든 문자열 가져오기

- SELECT ~ : 조회할 컬럼명 선택

- FROM~: 조회할 테이블명 지정(위치, 테이블명 입력)

- WHERE ~ :질의할 때 필요한 조건, 특정 조건

- GROUP BY ~: 특정 걸럼을 기준으로 그룹 지어 출력, 데이터 그룹화, 집계성 함수와 함께 사용, 집계성 함수(COUNT, MAX, MIN, AVG)

  - EX: SELECT A, MAX(B) FROM 테이블명 GROUP BY A DESC

- DISTINCT: 특정 컬럼들이 갖고 있는 값의 중복 제거, COUNT함수 사용

  - EX: SELECT DISTINCT A FROM 테이블명

- ORDER BY ~ : SELECT다음에 오는 컬럼 중 정렬이 필요한 부분 정렬(기본: 오름차순)

- LIMIT 숫자: Display하고자 하는 행 수 설정

![](https://velog.velcdn.com/images%2Fqsdcfd%2Fpost%2Ff2265aca-ddd8-4499-8c1e-a327749d2717%2Fimage.png)
 
- DISTINCT와 GROUP BY: 기준 별로 중복 없이 집계할 때 사용하는 것으로, 위의 함수의 집계함수를 활용하여 사용해야 한다.

  • 예시 with 코드

import sqlite3

import os
db_path = os.getenv('HOME')+'/mydb.db'
conn = sqlite3.connect(db_path)  # mydb.db에 연결합니다.
c = conn.cursor()

for row in c.execute('SELECT * FROM 도서대출내역'):
  print(row)

for row in c.execute('SELECT * FROM 도서대출내역'): # 전체 조회
  print(row)
  
  
for row in c.execute('SELECT ID FROM 도서대출내역'): # 특정 컬럼 지정
  print(row)
  
  
for row in c.execute('SELECT * FROM 도서대출내역 WHERE 이름 = "문강태";'): # 조건 입력
  print(row)
  
# GROUP BY로 중복을 제거해 보기 
for row in c.execute('SELECT 이름 FROM 도서대출내역 GROUP BY 이름;'):
  print(row)
  

#  DISTINCT로 중복을 제거해보기 
for row in c.execute('SELECT DISTINCT 이름 FROM 도서대출내역;'):
  print(row)
  
  
# ORDER BY로 정렬해보기
for row in c.execute('SELECT * FROM 도서대출내역 ORDER BY ID ;'):
  print(row)
  
  
# 몇개의 row만 조회하기
for row in c.execute('SELECT * FROM 도서대출내역 LIMIT 5 ;'):
  print(row)

DaTa Type

  • INT: 숫자의 범위(최소,최대)와 크기가 설정

  • Varchar: 문자형

  • 문자형의 특정부분 떼어 내는 함수

    • LEFT: 왼쪽부터 원하는 길이만큼 자른다, LEFT(문자형 컬럼, 길이)
    • RIGHT: 오른쪽부터 원하는 길이 만큼 자른다, RIGHT(문자형 컬럼, 길이)
    • SUBSTRING: 일정 영역만큼 자르기, SUBSTR(문자형 컬럼, 길이)

  • split_part()함수: 마지막 자리 삭제해줌

  • CAST함수: 잘라낸 부분에 더하여 숫자 변환, 형 변환 함수,CAST(형 변환하고 싶은 컬럼명 AS 변환하고 싶은 타입)

  • CODE 

for row in c.execute('SELECT * FROM 도서대출내역2;'):
  print(row)
  
import os
db_path = os.getenv('HOME')+'/mydb.db'
conn = sqlite3.connect(db_path)  # mydb.db에 연결합니다.
c = conn.cursor()

for row in c.execute('pragma table_info(도서대출내역)'):
  print(row)  
 
 
# 수정
for row in c.execute('SELECT *, SUBSTR(대출일수, 1, (length(대출일수)-1)) AS 대출일수_수정 FROM 도서대출내역2;'):
  print(row)
  
  
for row in c.execute('SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정 FROM 도서대출내역2 ;'):
  print(row)
  
for row in c.execute('SELECT ID, 이름, 대출년월, AVG(CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT)) AS 대출일수_평균 FROM 도서대출내역2 GROUP BY 1,2,3;'):
  print(row)

다양한 조건으로 조회하기

  • WHERE에는 조건절 여러 개가 가능하다

SELECT * FROM 도서대출내역2
WHERE
조건1
AND 조건2
AND 조건3
AND (조건 4 OR 조건5);

  • 조건절 안에 쓸 수 있는 것들
  1. 특정 문자를 포함하여 row가져오기

SELECT * FROM 도서대출내역2
WHERE 이름 LIKE "문%" ;

for row in c.execute('SELECT * FROM 도서대출내역2 WHERE 이름 LIKE "문%" ;'):
  print(row)
  1. 특정 기간 혹은 날짜 전후의 row 가져오기
  • 주로 날짜데이터에서 자주 쓰임

SELECT * FROM 도서대출내역
WHERE 대출일 >= "2020-06-01"
AND 대출일 <= "2020-06-07" ;

for row in c.execute('SELECT * FROM 도서대출내역 WHERE 대출일 >= "2020-06-01" AND 대출일 <= "2020-06-07" ;'):
  print(row)
  • Between
for row in c.execute('SELECT * FROM 도서대출내역 WHERE 대출일 BETWEEN "2020-06-01" AND "2020-06-07" ;'):
  print(row)
  1. 특정 숫자 이상 또는 이하의 row 가져오기
  • 부등호 사용
    SELECT *
    , CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수수정
    FROM 도서대출내역2
    WHERE 대출일수    수정 > 5 ;
for row in c.execute('SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정 FROM 도서대출내역2 WHERE 대출일수_수정 > 5 '):
  print(row)

NULL 조건 다루기

  • NULL: 해당 컬럼에 정보가 들어 있지 않은 상태

SELECT * FROM 도서대출내역
WHERE 반납일 IS NOT NULL;

for row in c.execute('SELECT * FROM 도서대출내역 WHERE 반납일 IS NOT NULL;'):
  print(row)

SELECT * FROM 도서대출내역
WHERE 반납일 IS NULL ;


for row in c.execute('SELECT * FROM 도서대출내역 WHERE 반납일 IS NULL;'):
  print(row)

JOIN

  • 핵심: KEY 포착하기

  • KEY: JOIN을 수행할 때 테이블을 연결하는 다리 역할

  • SQLite: RIGHT JOIN과 FULL JOIN은 지원하지 않음

*JOIN의 기본 구문

SELECT 컬럼1, 컬럼2, 컬럼3... FROM A테이블 AS A
{INNER/LEFT/RIGHT/FULL OUTER} JOIN B테이블 AS B
ON A.결합컬럼 = B.결합컬럼
WHERE ~

image.png

*LEFT TABLE:A ,RIGHT TABLE:B

  • INNER JOIN: A와 B의 교집합 조회

SELECT A.*, B.도서명
FROM 대출내역 AS A
INNER JOIN 도서명 AS B
ON A.도서ID = B.도서ID;

query = '''
SELECT A.*, B.도서명 
FROM 대출내역 AS A 
INNER JOIN 도서명 AS B
ON A.도서ID = B.도서ID;
'''
for row in c.execute(query):
  print(row)
  • LEFT JOIN : A테이블기준으로 B테이블의 공통 부분만 조회

SELECT A.*, B.도서명
FROM 대출내역 AS A
LEFT JOIN 도서명 AS B
ON A.도서ID = B.도서ID;

query = '''
SELECT A.*, B.도서명 
FROM 대출내역 AS A 
LEFT JOIN 도서명 AS B
ON A.도서ID = B.도서ID;
'''
for row in c.execute(query):
  print(row)
  • RIGHT JOIN: B테이블기준으로 A테이블의 공통 부분만 조회

SELECT B.*, A.ID, A.이름
FROM 대출내역 AS A
RIGHT JOIN 도서명 AS B
ON A.도서ID = B.도서ID;

query = '''
SELECT B.*, A.ID, A.이름 
FROM 대출내역 AS A 
RIGHT JOIN 도서명 AS B
ON A.도서ID = B.도서ID;
'''
for row in c.execute(query):
  print(row)
  • FULL JOIN: A와 B 모두에서 빠트리는 부분없이 모두 조회

SELECT A.*, B.도서명
FROM 대출내역 AS A
FULL OUTER JOIN 도서명 AS B
ON A.도서ID = B.도서ID
ORDER BY 도서ID;

query = '''
SELECT A.*, B.도서명 
FROM 대출내역 AS A 
FULL OUTER JOIN 도서명 AS B
ON A.도서ID = B.도서ID
ORDER BY 도서ID;
'''
for row in c.execute(query):
  print(row)

중첩질의(Nested Query)

  • LEFT JOIN 사용한 서브쿼리 결과를 C로부터 다시 수행하는 형태로 중첩되어있습니다.

SELECT C.이름, COUNT() 대출건수
FROM (
SELECT A., B.도서명
FROM 대출내역 AS A
LEFT JOIN 도서명 AS B
ON A.도서ID = B.도서ID ) C
GROUP BY C.이름;


query = '''
SELECT C.이름, COUNT(*) 대출건수
FROM (
	SELECT A.*, B.도서명 
	FROM 대출내역 AS A 
	LEFT JOIN 도서명 AS B
	ON A.도서ID = B.도서ID ) C
GROUP BY C.이름;
'''
for row in c.execute(query):
  print(row)

쿼리의 조건절

  1. IFNULL(값1, 값2)

  • NULL값을 다른 값으로 바꿀 때 사용

  • 값1: 항목이 NULL인지 체크

  • 값2: NULL일 경우, 다른 숫자 출력!

    : NULL이 아닌 경우, 그대로 리턴

SELECT A.*, IFNULL(B.도서명, '도서명미상') AS 도서명
FROM 대출내역 AS A
LEFT JOIN 도서명 AS B
ON A.도서ID = B.도서ID;

query = '''
SELECT A.*, IFNULL(B.도서명, '도서명미상') AS 도서명
	FROM 대출내역 AS A 
	LEFT JOIN 도서명 AS B
	ON A.도서ID = B.도서ID;
'''
for row in c.execute(query):
  print(row)
  1. CASE

  • IF문처럼 조건에 따라 다양한 출력 가능케 한다

  • IF문과 같은 기능을 보여준다

  • 문법 사이트: 링크텍스트

SELECT 이름,
CASE WHEN 대출일수수정 > 5
THEN '기간초과'
ELSE '기간내'
END AS 대출기간
FROM (
SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수수정
FROM 도서대출내역2
);

query = '''
SELECT 이름, 
CASE WHEN 대출일수_수정 > 5 
    THEN '기간초과' 
    ELSE '기간내' 
END AS 대출기간
FROM (
    SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정 
    FROM 도서대출내역2
);
'''
for row in c.execute(query):
  print(row)
profile
매일 공부(ML)
성장을 도울 아카이빙 블로그
이전 포스트

Python - DB

다음 포스트

웹 페이지 만들기

0개의 댓글