<수업 내용>

데이터 베이스

• 데이터를 체계적으로 저장, 관리 및 검색하기 위한 시스템
• SQL(Structured Query Language)는 가장 널리 사용되는 쿼리언어. 데이터 베이스에 저장된 데이터를 검색, 삽입, 수정, 삭제하는 데 사용. 필요한 정보를 효율적으로 추출하거나 조작 할 수 있다.
• 관계형 데이터 베이스는 테이블 이라고 불리는 구조화된 데이터의 집합으로 구성된다
  • 행과 열로 구성
  • 행은 개별 데이터 레코드를 나타내며, 열은 데이터 필드(속성)를 정의

• 종류
  • 정형 데이터 : 형태 O, 연산O (RDB, Excel, CSV 등)
  • 반정형 데이터 : 형태 O, 연산X (XML,JSON,HTML 등)
  • 비정형 데이터 : 음성, 이미지 등

• SQL vs NoSQL

  • SQL : 관계형 데이터 베이스 조작언어
  • NoSQL : 비정형 데이터를 다루는 적합. 데이터는 테이블 형태가 아닌, 컬렉션, 문서, 키-값 쌍 등의 형태. 수평적으로 확장 가능한 아키텍처를 가지면, 대용량의 분산 데이터 처리에 적합
    • 대용량 데이터 처리
    • 분산 환경 지원
    • 유연성과 확장성

• 로컬DB vs 클라우드DB

• 기본키(primary key) vs 외래키(foreign key)

  • 기본키 : 각 레코드를 고유하게 식별하는 사용되는 필드
    • NULL값 없어야 하고 중복값 없어야 한다
  • 외래키 : 다른 테이블과의 참조관계를 나타내며, 한 테이블의 데이터와 다른 테이블의 데이터 간의 관계를 정의
    • DBDiagram.io 참고

SQL

SELECT

• 원하는 데이터를 조회하는 명령어
• 테이블의 컬럼에서 데이터를 선택하고 결과로 반환할 데이터 지정
• $*$를 표기하면 모든 컬럼을 의미

EX)

select * from albums;

# albums 테이블에서 모든 열 불러오기

• 산술연산자 활용 : +,-,/,*,%
• 문자열 연결 : ||
• 중복 제거 : SELECT DISTINCT 컬럼명 FROM 테이블명;

실습

1. gengres 테이블의 Name컬럼에서 "음악"(문자열)을 추가하여 출력하기

SELECT Name || "음악" from genres;

2. customers 테이블에서 Country 컬럼을 중복없이 출력하기

SELECT DISTINCT Country from customers ;

3. tracks테이블에서 Milliseconds칼럼에 1000을 나누어 Seconds 칼럼명으로 출력하기

SELECT Milliseconds /1000 as Seconds from tracks ;

• 함수

  • 입력 X값에 대한 결과 Y값을 반환하는 기능을 하나의 묶음으로 선언

  • 문자함수

    • Select Length : 문자열의 길이 반환
    • Select Upper : 문자열을 모두 대문자로 변환
    • Select Lower : 문자열을 모두 소문자로 변환
    • Select Concat : 두 개 이상의 문자열을 합쳐서 하나의 문자열로 반환
    • Select Trim : 문자열의 앞 도는 뒤에 있는 공백을 제거
    • Select Instr : 문자열에서 특정 문자열이 처음으로 나타나는 위치를 반환
    • Select Substr(대상문자열, 시작위치, 반환할 문자열의 길이) : 문자열의 일부분을 반환
    • Select Replace(대상문자열, 바꿀 문자, 바뀔 문자) : 문자열에서 특정 문자열을 다른 문자열로 변경
    • Select Ltrim
    • Select Rtrim

  • 숫자함수

    • Select Abs
    • Select Round
    • Select Ceiling
    • Select Floor
    • Select Power
    • Select Sqrt
    • Select Mod
    • Select Rand
    • Select Sign : 수의 부호를 반환
    • Select Trunc(수, 남길 자리수): 주어진 숫자를 지정된 소수 자릿수로 잘라내고, 남은 소수 자릿수를 버린다.
    • Select Sum
    • Select Avg

  • CASE 함수 : 조건부로 값을 반환하거나 특정 작업을 수행하기 위해 사용되는 함수

    CASE
        WHEN condition 1 THEN result 1
        WHEN condition 2 THEN result 2
        ...
    
        ELSE RESULT
    END    

실습

1.tracks 테이블에서 곡의 길이(Millisecinds)를 반올림 한 다음 분 단위로 곡의 길이를 표시

SELECT ROUND(Milliseconds)/60000 from tracks ;

2. invoices 테이블에서 청구서의 총 금액(Total)에 따라서 금액 범위를 분류

Select Total,
	Case 
		when Total <=10 Then "낮음"
		When (Total >10 and Total <=50) Then "보통"
		When Total >50 Then "높음"
	End as "분류"
from invoices	

WHERE

• 쿼리에서 특정 조건을 기반으로 데이터를 필터링 하는 데 사용
• SELECT, UPDATE, DELETE 등의 쿼리에서 사용될 수 있다.
• 조건식을 지정하여 조건이 참인 행만 반환하거 조작한다.
• 다양한 연산자와 함께 활용된다.
• 비교연산자(=,<,>,!= 등), 논리연산자(AND,OR,NOT 등), 기타 연산자 (IN, LIKE, BETWEEN, IS NULL 등)과 사용

EX)

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary>=50000;

# 실행 순서 : 1. From 2.Where 3.Select 순

• LIKE : 특정 문자열을 검색하는 데 사용되는 연산자
  • 와일드 카드 문자를 사용하여 패턴을 정의할 수 있다
    • '%' : 임의의 문자열(0개 이상의 문자)
    • '_': 임의의 단일 문자

실습

1. customers 테이블에서 나라가 "USA"인 고객들의 정보

Select *
from customers 
where Country ="USA"
	

2. employees 테이블에서 고용일이 2003년 이전인 직원들의 정보

select *
from employees
where HireDate < "2003-01-01"

3. albums테이블 에서 앨범 제목에 "Love"라는 단어가 포함된 앨범들의 정보

Select *
from albums
Where Title like '%LOVE%'

GROUP BY & HAVING

• GROUP BY : 특정 열의 값에 따라 그룹화한다. 집계함수(SUM, COUNT, AVG 등)과 함께 쓰인다.

• HAVING : GROUP BY와 함께 조건을 지정하는 데 사용

  EX)

  SELECT CustomerId, AVG(Total)
  FROM invoices
  WHERE CustomerId >= 10
  
  
  GROUP BY CustomerId
  HAVING SUM(Total) >= 30
    
  # 실행 순서 : 1. FROM 2. WHERE 3. GROUP BY 4. HAVING 5. SELECT 순

cf) 집계 함수 :집계 함수는 여러 행으로부터 하나의 결괏값을 반환하는 함수이다. SELECT 구문에서 사용된다. WHERE에서는 집계 함수를 사용할 수 없는 반면 HAVING은 집계 함수와 함께 사용 가능하다는 장점이 있다.

• COUNT : 각 그룹에서의 비어있지 않은 값의 개수를 반환합니다.
• SUM : 각 그룹에서의 합을 계산합니다.
• AVG 또는 MEAN : 각 그룹에서의 평균을 계산합니다.
• MEDIAN : 각 그룹에서의 중앙값을 계산합니다.
• MIN : 각 그룹에서의 최소값을 찾습니다.
• MAX : 각 그룹에서의 최대값을 찾습니다.
• STDDEV 또는 STD : 각 그룹에서의 표준 편차를 계산합니다.
• VAR 또는 VARIANCE: 각 그룹에서의 분산을 계산합니다.
• FIRST : 각 그룹에서 첫 번째 값을 반환합니다.
• LAST : 각 그룹에서 마지막 값을 반환합니다

실습

1. tracks 테이블에서 곡 장르별로 곡 수가 100개 이상인 장르를 선택

SELECT GenreId , COUNT(TrackId) 
FROM tracks 
group by GenreId 
HAVING COUNT(TrackId)>=100   

2. invoices 테이블에서 각 고객별로 총 구매 금액이 40달러 이상인 고객을 선택

SELECT CustomerId, sum(Total)  
FROM invoices 
group by CustomerId 
HAVING Sum(Total)>=40

ORDER BY

• 특정 열을 기준으로 정렬하는 사용
• ASC(오름차순) , DESC(내림차순)
• 여러개 열 지정 가능
  EX)

1. 		SELECT employee_id, first_name, last_name
   		FROM employees
   		ORDER BY last_name ASC, first_name ASC;

	SELECT CustomerId, AVG(Total)
	FROM invoices
	WHERE CustomerId >= 10
	GROUP BY CustomerId
	HAVING SUM(Total) >= 30
	ORDER BY 2;
    
    # 실행 순서 : 1. from 2. where 3. group by 4. having 5. select 6. order by

cf) 순서 정리
실행 순서 :
FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
작성 순서 :
SELECT → FROM → JOIN → ON → WHERE → GROUP BY → HAVING → ORDER BY

실습

1. artists 테이블에서 아티스트 이름을 알파벳 순으로 정렬

SELECT  Name 
FROM artists
ORDER BY Name 

2. tracks 테이블에서 곡 길이를 오름차순으로 정렬하여 선택

SELECT  Milliseconds, Name 
FROM tracks 
order by Milliseconds  

3. invoices 테이블에서 청구서의 총 금액을 내림차순으로 정렬하여 선택

SELECT  Total, InvoiceId 
FROM invoices
order by Total DESC 

JOIN

• 둘 이상의 테이블을 연결하여 연관된 데이터를 함께 조회하는 데 사용

EX)

SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition;

• INNER JOIN(JOIN) : 두 테이블 사이의 일치하는 행만 반환(교집합)
• LEFT JOIN(LEFT OUTER JOIN) : 왼쪽테이블의 모든 행을 반환하고, 오른쪽테이블과 일치하는 행이 있는
  경우 일치하는 행도 반환
• RIGHT JOIN (RIGHT OUTER JOIN) : 오른쪽테이블의 모든 행을 반환하고, 왼쪽테이블과 일치하는
  행이 있는 경우 일치하는 행도 반환
• FULL JOIN(FULL OUTER JOIN) : 두 테이블 사이의 모든 행을 반환(합집합)

실습

1. customers와 invoices을 테이블을 조인하여 각 고객의 이름과 구매한 청구서의 총 금액을 선택

SELECT  customers.FirstName ,customers.LastName ,invoices.total
FROM customers
	full outer join invoices
	on customers.CustomerId = invoices.CustomerId