[1/9] SQL 사용하여 데이터베이스 다루기

 

1. 데이터베이스와 테이블

데이터베이스

• 데이터와 데이터베이스 개념-데이터베이스 : 데이터가 모여진 공간 (구조적 방식으로 관리되는 데이터의 집합)
• 권한에 따라 수정(DBA), 검색 등 다룰 수 있는 범위가 다름
• -데이터 : 모든 기록들
• 데이터베이스 특징1)실시간 접근성2)지속적인 변화3)동시 공유4)내용에 대한 참조 (중요!!)
• 데이터베이스의 데이터 참조 시, 데이터 행 주소나 위치 아니라, 데이터 내용으로 데이터를 찾음
• 다수 사용자가 동시에 같은 내용의 데이터 사용
• 새로운 데이터 삽입,삭제,갱신으로 항상 최신의 데이터 유지
• 사용자 질의에 즉시 처리 및 응답
• -중복 데이터 제거, 자료 구조화하여 효율적 처리 가능하도록 관리한다
• 관계형 데이터베이스 (RDB, Relational Database)구조화된 데이터는 SQL 통해 조회 가능
• 일관성, 안정적, 신뢰성
• 2차원 테이블(행,열)로 구성

테이블

• 테이블-행/열의 형태로 구성 및 관리 가능
• -Key를 지정하여, 원하는 데이터를 빠르고 쉽게 찾아낼 수 있습니다.
• -관계형 데이터베이스에서 자료의 구조를 2차원의 표로 나타낸 것
• 행(Row), 열(Column) 개념
• 

스키마와 데이터 타입

• 스키마-테이블에 대한 정보가 들어있다
• -테이블 정의할 때, 컬럼별로 저장할 수 있는 타입까지 명시해 주어야 함
• -테이블의 뼈대
• 데이터 타입-날짜 : 날짜 함수 따로 써줘야 함조건을 부여할 때, 숫자는 그대로 작성. 문자, 날짜는 ‘ ‘ 따옴표 표시!
• -숫자 : 소문자변환 함수(lower) 사용 불가
• -문자 : 사칙연산 불가

 

---

2. 데이터 다루기

쿼리문 작성과 데이터 필터링

• 쿼리문 작성하기(SELECT, FROM)

#전체 컬럼 가져오기
SELECT * (혹은 특정 컬럼도 가능)
FROM 테이블명

• 전체 데이터 필터링하기(WHERE)

-WHERE는 FROM절 다음에 위치

-WHERE절에는 연산자를 같이 사용

SELECT 원하는 컬럼 (*)
FROM 테이블명 (bike)
WHERE 조건절 (yr = 2012)

비교 연산자 사용하기

= , != (<>) (포함, 제외)

, <, >=, <= (숫자타입의 금액, 날짜타입의 날짜)

논리 연산자 사용하기

여러가지 조건 주고싶을 때 사용

AND(둘 다 만족), OR(하나라도 만족), IN, NOT(부정)

mysql("""  

SELECT dteday, season, weathersit
FROM bike
WHERE yr = 2012
AND season != 'spring'
AND hr >= 23

LIMIT 100   #전체 데이터 중 특정 숫자만 확인할 때  

""")

mysql("""

SELECT *
FROM bike
WHERE hr = 10 OR hr = 11
AND mnth IN ('Jan', 'Mar', 'May') #1월, 3월, 5월 중 하나라도 만족
AND weekday NOT IN ('Tuesday', 'Wednesday', 'Sunday') #반대의 의미

""")

컬럼이 어떤 값들의 집합에 속할 경우 IN을 사용 (조회하고자 하는 데이터 값이 여러 개일 때)

(= 은 하나의 조건만 지정 가능, or은 비효율적)

NULL : 값 지정되지 않아 알 수 없는 값 (≠ 0은 숫자, 공백은 문자열)

SELECT *
FROM bike
WHERE weathersit IS NULL

Between A and B : A와 B 사이에 값이 존재할 때 사용하며, A와 B를 포함

number Between 3 AND 5  == number>=3 AND number<=5

SELECT *
FROM bike
WHERE dteday Between '2011-03-30' and '2011-03-31'

산술 연산자 사용하기

데이터값 계산에 사용

• 우선 순위: () > *, / > +,-

날짜, 문자에는 산술연산자 사용 X

숫자열에 산술 연산자 적용시, 적용된 열이 생성된다

 

 

---

3. 다양한 데이터 활용법

특정 데이터 조건과 별칭

• 특정 데이터 조건주기(LIKE, Wildcard)

LIKE : 조건 값 명확하지 않을 때 특정 값 찾을 수 있다 (wildcard랑 같이 사용)

Wildcard : %(조건을 포함하는 모든 문자), _(한 글자, 갯수가 중요)

SELECT *
FROM bike
WHERE weekday LIKE '%ur%'(ur이 그냥 포함)/ 'Cl%' (Cl로 시작)/ '%ur' (ur로 끝)
AND mnth LIKE 'ma_' (ma로 시작, 뒤에 한 글자만 온다)

• 별칭 사용하기(Alias)

컬럼명과 별칭 사이는 AS를 작성 (생략 가능)

SELECT b.temp, b.hum AS 'humidity'
FROM bike AS b
LIMIT 5

데이터 정렬과 중복 제거

• 데이터 정렬하기(ORDER BY; 오름차순, 내림차순)

SELECT
FROM
WHERE
ORDER BY
LIMIT

SELECT에 작성한 첫번째 컬럼 기준으로 내림차순으로 정렬, 같은 값이 있다면 두번째 컬럼 기준 정렬

SELECT dteday, temp, cnt
FROM bike
ORDER BY cnt DESC, temp  #cnt 기준 내림차순, 동일한 값 있으면 temp 오름차순
/ ORDER BY 3 DESC, 2
LIMIT 100

• 중복제거하기(DISTINCT)

고유한 값을 반환 (COUNT, SUM, AVG, MAX, MIN 등 집계함수에도 사용 가능)

SELECT DISTINCT 옆 모든 컬럼 고려하여 중복 제거!

SELECT DISTINCT weekday, weathersit
FROM bike

조건문 활용하기(IF, CASE WHEN ~ THEN ~)

• 단일 조건문(IF) (엑셀이랑 똑같은 문법)SELECT, WHERE에 사용
• 함수와 함께 사용 가능 (pandassql에서는 iif로 표현)

SELECT customerName AS '고객명', birthday AS '생년월일',
    iif(birthday >= '1990-01-01','이후','이전') AS '1990년 기준'
FROM customers

• 다중 조건문(CASE WHEN ~ THEN ~ ELSE ~ END)

  SELECT customerName, creditLimit,
    CASE WHEN creditLimit<=50000 THEN '5만원이하'
          WHEN creditLimit<=100000 THEN '10만원이하'
          ELSE '10만원초과'
          END AS '고객 신용한도'
  FROM customers
  

• WHEN 조건 THEN 반환값

CASE WHEN THEN

WHEN THEN

ELSE

END

데이터 타입 변환하기(CAST)

CAST(컬럼 AS 변환할 타입)

변환할 타입 : int, float, varchar(문자열), datetime(날짜) 등

예) SELECT CAST(14.58 AS int)

14(결과값)

SELECT CAST(yr AS varchar) AS yr
FROM bike
WHERE CAST(yr AS varchar)= '2011'
LIMIT 5

 

---

4. 데이터 집계하기

집계함수(COUNT, SUM, AVG, MAX, MIN) - group by와 함께

• COUNT

COUNT(*) : 모든 행의 갯수 카운트, NULL포함, NULL 유무 체크 불가

COUNT(컬럼명) : NULL값 제외한 해당컬럼의 갯수 카운트

SELECT COUNT(*) AS cnt  
    , COUNT(weathersit) AS weather_cnt   #
FROM bike

• SUM, AVG, MAX, MIN(컬럼명)

SUM : 해당 컬럼 값의 합계

SELECT SUM(casual) AS casual_sume
    , SUM(registered) AS registered_sum
FROM bike

SELECT MAX(casual) AS casual_max
    , MIN(registered) AS registered_min
FROM bike

• 집계함수의 특징

집계함수 내부에 조건문을 사용하여, 컬럼값을 변경 후 한번에 결과값끼리 계산 가능

SUM(컬럼명) / COUNT(컬럼명)

SELECT COUNT(season) AS '봄기간데이터개수'
FROM bike
WHERE season = 'spring'    #봄일 경우의 데이터 갯수만 센다

SELECT COUNT(CASE WHEN weekday = 'Sunday' THEN '휴일'
            WHEN weekday = 'Saturday' THEN '휴일'
            ELSE NULL     #NULL을 넣으면 얘네는 뺴고 값 있는 애만 count하니까
            END) AS '휴일기간'
FROM bike

데이터 그룹화하기(GROUP BY) : 집계함수랑 같이 써야 효과적임

SELECT
FROM
WHERE
GROUP BY
ORDER BY
LIMIT

SELECT 컬럼1, SUM(컬럼)
FROM 테이블명
GROUP BY 컬럼1

*** GROUP BY 기준 컬럼을 꼭!! SELECT 옆에 넣어줘야 한다 (SELECT 옆의 컬럼 기준으로 정렬)

SELECT dteday AS "일자", SUM(cnt) AS "이용건수합계"
FROM bike
GROUP BY 1

그룹화한 데이터 조건주기(HAVING)

• HAVING

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

그룹함수 결과 중 원하는 조건에 부합하는 것만 필터링

• WHERE와 HAVING의 차이

WHERE : 집계함수 사용 불가, 개별행에 적용

HAVING : GROUP BY 후에 적용, 그룹에 대해 적용

SELECT season, AVG(temp) AS "계절별 평균온도"
FROM bike
GROUP BY 1
HAVING AVG(temp) >= 0.3

 

---

5. 여러 개 테이블 사용하기

기본키(PK), 외래키(FK)

• 기본키(PK : Primary Key)

NULL값 허용 X, 테이블은 각각 하나의 기본키를 가진다, 기본치로 하나의 컬럼이 지정되어 있다, 유일성을 보장

• 외래키(FK : Foreign Key)

JOIN으로 다른 테이블과 관계를 맺을 경우, 다른 테이블의 기본키를 참조하는 컬럼

(외래키의 값은 참조하는 기본키값과 동일 or NULL)

기본키와 동일한 값의 종류와 범위를 가진다

다중 테이블 사용하기(JOIN)

• JOIN

한 개 이상의 테이블과 테이블을 서로 연결하여 사용하는 기법

조인 유형을 지정하지 않으면 서버는 기본적으로 내부 조인(INNER JOIN)을 실행

• 두 테이블을 조인할 때 사용한 열의 이름이 동일할 경우, ON 대신 **USING**을 사용 가능

2개 테이블

SELECT 컬럼명
FROM 테이블명 AS A
JOIN 테이블명 AS B
ON A.id = B.id     #두 테이블을 합치는 기준

SELECT orderId, orderDate, c.customerId, country, city (c. 뒤는 customer 테이블의 열)
FROM orders AS o
JOIN customers AS c
ON o.customerId = c.customerId  #orders 테이블 표출 (customerId 기준으로 customer 테이블을 합친다)
혹은
USING (customerId)              #이렇게 표현 가능하다

3개 테이블

SELECT 컬럼명
FROM 테이블명 AS A
JOIN 테이블명 AS B
ON A.id = B.id
JOIN 테이블명 AS C
ON A.id = C.id   #먼저 두개 합치고서 마지막거 합치기

• JOIN의 종류

INNER JOIN : 공통된 부분 기준 (일반적인 JOIN) : 일치하는 행만 가져온다(교집합)

별칭 사용의 장점 : 긴 이름 축약, 가독성 증가, 효율적

OUTER JOIN

— LEFT JOIN : 왼쪽 테이블 기준 (왼쪽 테이블 데이터 유지한 채 오른쪽 테이블 데이터 가져옴, 없으면 NULL)

— RIGHT JOIN : 오른쪽 테이블 기준 (그 반대)

데이터 이어붙이기(UNION)

1)테이블을 아래로, 세로로 붙이는 형태 (행 추가)

2)중복을 포함하여 합친다

-컬럼명 달라도 UNION 사용 가능

-컬럼수, 데이터 타입과 같을 때 UNION 사용 가능

-컬럼수 다르면 or 데이터 타입 다르면 UNION 사용 불가

SELECT *
FROM 테이블1
UNION ALL
SELECT *
FROM 테이블2

중복 제거할 때는 그냥 UNION

SELECT *
FROM 테이블1
UNION
SELECT *
FROM 테이블2

서브쿼리 원리와 방식(Subquery)

• Subquery

1)하나의 쿼리문 안에 포함되어 있는 또 다른 쿼리문

2)새로운 컬럼 필요 없을 때 서브쿼리를 사용

3)완전히 다른 테이블에서 데이터값 조회해서 메인쿼리 조건으로 사용할 때 유용

4)항상 서브쿼리 실행 → 메인쿼리 실행 순서

5)서브쿼리 내에서는 ORDER BY 문법이 지원되지 않는다

6)괄호로 묶어서 사용

7)연산자의 오른쪽에 위치

8)괄호 뒤에 세미콜론(;) 사용 안함

• WHERE절 서브쿼리

SELECT MIN(price)
FROM orders      # 61545

SELECT *
FROM customers
WHERE creditLimit < 61545

• 단일행 서브쿼리

-서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리

-단일행 비교연산자(=, !=, >, < 등)와 함께 사용

SELECT *
FROM customers
WHERE creditLimit < (SELECT MIN(price)
                     FROM orders)  #가장 작은 주문금액보다 작은 한도를 가진 고객 정보

• 다중행 서브쿼리

-서브쿼리의 실행 결과가 여러 건인 서브 쿼리

-다중행 비교연산자(IN, NOT IN 등)와 함께 사용

#USA에 사는 고객의 주문ID 구하고 싶을 때, USA사는 customer SUb쿼리 먼저 작성 후 메인쿼리 작성

SELECT orderID
FROM orders
WHERE customerId IN (SELECT customerId
                    FROM customers
                    WHERE country = 'USA')

• FROM절 서브쿼리

-뷰(view)처럼 작동하여 인라인뷰라고 부름

*뷰(view) : 쿼리문으로 생성하는 가상 테이블

-데이터 추출 결과를 FROM 절에서 하나의 테이블처럼 사용하고 싶을 때 사용

-FROM 절에 서브쿼리를 사용하면 특정 조건식을 갖는 데이터 추출결과를 테이블처럼 사용 가능

SELECT *
FROM (SELECT customerId, Max(orderDate) as max_date
      FROM orders
      GROUP BY 1) AS MAIN

#FROM절 안에 들어간 게 VIEW 임 >> 저 결과를 MAIN이라는 뷰로 만들어 놓은거임

[참고] 날짜 차이를 구할 때 MySQL 등과 같은 DBMS에서는 날짜와 관련된 함수, 예를 들면 DATEDIFF 등의 함수를 사용하지만 pandasql과 SQLite에서는 julianday 함수를 사용합니다. (julianday(’now’) : 오늘)

#고객별 마지막 제품의 구매일자가 현재일을 기준으로 며칠 지났는지 확인

SELECT *, CAST(julianday('now')-julianday(final_order) AS INT) AS date_diff
FROM (SELECT customerId, Max(orderDate) AS final_order
        FROM orders
        GROUP BY 1) AS main

 

---

6. SQL 연습문제

31. 주문(orders) 테이블, 고객(customers) 테이블, 직원 테이블(employees)을 조인해 보세요.

직원(name)별 고객수(c.customerId)와 주문건수(o.orderId)를 확인하세요. 고객수 카운트시 중복을 제거하여 고유개수를 추출하면 됩니다.

SELECT e.name, COUNT(DISTINCT c.customerId) AS '고객수', COUNT(o.orderId) AS '주문건수'
FROM orders AS o
JOIN customers AS c
ON o.customerId = c.customerId
JOIN employees AS e
ON c.employeesId = e.employeesId
GROUP BY name