zheld.doden

이번 시간에는 수학적 함수들을 공부할 것이다.

Mathematical Function

아래 공식 문서를 참고하면 수학적인 함수 및 연산자들을 확인할 수 있다.
https://www.postgresql.org/docs/current/functions-math.html

생각할 수 있는 거의 모든 수학 연산자는 PostgreSQL 에서 사용할 수 있다.
절대값, 반올림, 로그, 파이, 삼각함수 등 다양한 수학 연산이 가능하다. 위 사이트를 참고해서 사용하면 된다.

String Function

String을 다루기 위한 Function 들도 있다.
이또한 문서에서 확인할 수 있다.

https://www.postgresql.org/docs/9.1/functions-string.html

### Timestamps and Extract

시간과 날짜 정보를 보고하는 명령어와 함수를 살펴볼 예정이다.
데이터베이스를 쿼리할 떄보다 자체 데이터베이스를 만들 때 이러한 함수가 유리해진다.

Postgre는 다음과 같은 날짜 시간 정보를 가질 수 있다. 
- TIME(시간정보), DATE(날짜정보), TIMESTAMP(시간과날짜정보), TIMESTAMPTZ(시간, 날짜, 시간대 정보)

시간 데이터 유형을 선택할 때 신중하게 고려해야한다. 상황에 따라 날짜, 시간 및 시간대가 필요할 수도 필요하지 않을 수도 있기 때문이다. 직원이 근무한 시간을 계싼할 때는 시간이 필요하지만 시간대는 필요없을 것이다. 그러면 TIMESTAMPTZ는 필요하지 않다.

이번에는 이러한 데이터 유형과 관련된 함수를 배울 것이다.
- TIMEZONE, NOW, TIMEOFDAY, CURRENT_TIME, CURRENT_DATE


### NOW

NOW() 를 사용하면 현재 시간대, 날짜, 시간 정보를 볼 수 있다. 현재 내가 있는 시간을 timestamp 형식으로 표시한다. 


### TIMEOFDAY
TIMEOFDAY() 는 text 의 형태로 날짜, 시간을 가져온다.


### CURRENT_DATE

CURRENT_DATE()는 현재 날짜를 date 형식으로 가져온다.



### EXTRACT()
날짜 값의 하위 구성요소 출력하거나 할 때 사용한다. YEAR, MONTH, DAY, WEEK, QUARTER 과 같은 데이터값들을 갖는다.
다음 문법으로 사용할 수 있다.
```sql
EXTRACT(YEAR FROM date_col)
```

### AGE()
타임스탬프가 지정된 현재 age 를 계산후 리턴한다.

```sql
AGE(date_col)
```
로 사용하고 

다음과 같은 형식의 결과를 리턴할 것이다.
```sql
13 year 1 mon 5 days 01:34:13.003423
```
### TO_CHAR()

TO_CHAR() 은 데이터 타입을 텍스트로 변환한다. timestamp 를 포매팅할 때 유용하다.

```sql
TO_CHAR(date_col, 'mm-dd-yyyy')
```



## 실습
payment 테이블 속 payment_date 칼럼은 timestamp 형식으로 되어있다. 여기서 연도를 추출해보자.

```sql
SELECT EXTRACT(YEAR FROM payment_date) FROM payment
```




TO_CHAR() 은 포매팅할 때 유용하게 사용할 수 있다. 
TIMESTAMP 를 원하는 형식으로 출력할 수 있다.
포매팅 방법은 아래 사이트를 참고하면된다.
[documentation](https://www.postgresql.org/docs/current/functions-formatting.html)

```sql
SELECT TO_CHAR(payment_date, 'MONTH-YYYY')
FROM payment
```


payment 테이블에서 월요일에 결제된 주문 수를 계산하려면 어떻게 할까?
Timestamp 에서 EXTRACT 를 통해 원하는 field 를 추출할 수 있다. 요일이 궁금하므로 "dow" 를 사용하면 일요일부터 토요일까지 요일을 0-6의 숫자로 나타낸다. 따라서 payment_date 라는 timestamp 칼럼에서 요일이 1(=월요일)일 때의 행의 개수를 COUNT 하면 된다. 

```sql
SELECT COUNT(payment_date)
FROM payment
WHERE EXTRACT(dow FROM payment_date) = 1
```

JOIN

JOIN 은 여러 테이블에서 정보를 결합할 수 있게 해준다.

Inner Joins, Full Outer Joins, Left Outer Joins, Right Joins 등이 있다.

AS statement

AS 는 출력에 표시할 새로운 이름을 정할 떄 사용된다.

SELECT column AS new_name FROM table;

주의할 점은 AS 문은 가장 마지막에 칼럼의 별칭으로 바꾸는 것이므로 WHERE 이나 HAVING 문에서 별칭으로 적으면 안된다. 아래 사진은 정상적으로 작동하나, 두번째 사진에서는 오류가 발생함을 확인할 수 있다.

Inner Join

inner join 은 가장 간단한 join 유형이다.

inner join 은 두 테이블 내에 일치하는 걸 찾을 떄 사용된다.

예를 들어 아래 처럼 Registration 테이블과 logins 테이블이 있다고 할 때, INNER JOIN 은 둘 다에서 매치되는 애들을 결과로 출력한다.

문법

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.col_match = TableB.col_match

위 예시에서는 다음처럼 사용한다.

SELECT * FROM Registrations
INNER JOIN Logins
ON Registrations.name = Logins.name

INNER JOIN 이기 때문에 SELECT * FROM Registrations 와 SELECT * FROM LOGINS 이 똑같이 되게 된다.

실습은 payment 테이블과 customer 테이블에서 customer_id 가 같으므로 해당 칼럼으로 inner join 하였다.

OUTER JOIN

OUTER JOIN 은 두 테이블에 동시에 있는 행만 가져오지만 OUTER JOIN 은 조금 더 복잡하다.

FULL OUTER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN 으로 나눌 수 있다.

FULL OUTER JOIN

FULL OUTER JOIN 은 모든 행을 가져온다고 보면 된다.

아래와 같은 예시에서는 Andrew, Bob, Charlie, David, Xavier, Yolanda 를 가져온다.

벤다이어그램으로 표현하면 이렇다.

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.col_match = TableB.col_match

WHERE 을 쓰면 겹치는 애들을 제외할 수도 있다.

id가 null 인 애들만 출력할 수 있기 때문이다.

SELECT * FROM Registrations FULL OUTER JOIN Logins
ON Registrations.name = Logins.name
WHERE Registrations.reg_id IS null OR
Logins.log_id IS null

LEFT OUTER JOIN

LEFT OUTER JOIN 은 TableA 만 리턴하게 된다. 아래 사진처럼 말이다. 만약 우리가 A에는 있지만 B에는 없는 데이터를 보려고 할 때 LEFT OUTER JOIN 을 사용할 수 있다.

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match

벤 다이어그램으로 표현하면 이렇다. FULL OUTER JOIN과 INNER JOIN 에서는 순서가 중요하지 않았지만 LEFT OUTER JOIN 은 FROM 뒤에 써진 테이블이 기준이 되므로 테이블의 순서가 중요해진다.

WHERE 을 쓰면 TableA 에는 있으면서 TableB 인 애들을 거를 수 있다.

위에서 봤던 표를 보면, A에 있고 B에 없는 애들은 B의 id 가 null 이 됨을 확인할 수 있었다. 이를 이용하면 unique 한 Table A 행들을 추출할 수 있다.

SELECT * FROM Registrations
LEFT OUTER JOIN Logins
ON Registrations.bane = Logins.name
WHERE Logins.log_id IS null

실습을 해보면, film 테이블에는 있지만 inventory 테이블에는 없는 행들을 다음처럼 뽑아낼 수 있다. 재고에 없는 영화들을 추출할 수 있다.

SELECT film.film_id, film.title, inventory_id, store_id
FROM film 
LEFT OUTER JOIN inventory
ON film.film_id = inventory.film_id
WHERE inventory.film_id IS null

RIGHT OUTER JOIN

다음은 RIGHT JOIN 이다. RIGHT JOIN 은 LEFT JOIN 의 반대격이다.
아래 문장에서 Table A가 아닌 Table B 의 전체 행을 추출한다.

SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match

LEFT JOIN 과 마찬가지로 WHERE 을 사용하면 Table B 이면서 Table A 인 애들을 제외할 수 있다.

SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.col_match = TableB.col_match
WHERE TableA.id IS null

UNION

UNION 은 결과를 그냥 결합할 때 사용한다.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

예를 들어 아래와 같은 내용에서 David 는 처음에는 100 다음에는 200을 판매했을 때, 이 둘을 합치기 위해 두번째 사진처럼 합칠 수 있다. 첫번째 쿼리 이후에 두번째 쿼리를 그냥 넣는 것이다.

Aggregate functions

Aggregate function 은 여러 입력을 받아 단일 출력을 반환하기 위한 함수이다.

Aggregate functions 에는 다음과 같은 종류가 있다.
AVG(), COUNT() , MAX(), MIN(), SUM()
하나씩 살펴보자.

MIN(), MAX()

MIN, MAX 함수를 사용하면 전체 데이터 중 해당하는 칼럼의 최소값, 최대값을 알 수 있다.

예를 들어 film 테이블에서 대체 비용의 최소값을 출력하려면 다음처럼 작성한다.

SELECT MIN(replacement_cost) FROM film;

만약, film 테이블에서 대체 비용의 최대값을 출력하려면 MAX() 로 적는다.

SELECT MAX(replacement_cost) FROM film;

둘 다 같이 할 수도 있다. 이떄는 film 테이블에서 대체 비용의 최소값과 최대값 칼럼 두개가 출력된다.

AVG(), ROUND()

AVG 를 사용하면 평균도 알 수 있다. film 테이블에서 대체 비용의 평균을 알 수 있다.

SELECT AVG(replacement_cost) FROM film;

이 때 너무 소수점이 길어지므로 원하는 자리수까지만 출력하려면 ROUND() 로 감싸서 소수점 자리수와 같이 적어주면 된다. 아래처럼 작성한다면 소수점 둘째자리까지만 표현할 수 있다.

SELECT ROUND(AVG(replacement_cost),2) FROM film;

SUM()

SUM 을 사용하면 전체 합을 얻을 수 있다. 아래 예시에서는 film 테이블에서 대체 비용의 총합을 알 수 있다.

SELECT SUM(replacement_cost) FROM film;

GROUP BY

GROUP BY 는 여러 행을 기준에 따라 그룹화하고 각 그룹에 대해 데이터가 어떻게 분포되어있는지 확인하기 위해 사용된다.

예를 들어 아래와 같은 테이블이 있다고 하자. 카테고리는 A, B, C 가 있는데 이들은 숫자가 아니라 범주형 자료이다. 이때 A는 A끼리 B는 B끼리 C는 C끼리 나눌 수 있을 것이다. 그룹별로 나눠서 집계함수를 실행하기 위해 GROUP BY 를 사용할 수 있다.

만약 각각에 대해 집계함수 COUNT() 를 적용한다면 A의 개수는 2개 B도 2개, C도 2개로 아래와 같은 결과가 나올 것이다.

SELECT category_col, AGG(data_col) FROM table GROUP BY category_col;

payment 테이블에서 어떤 고객이 가장 많은 돈을 썼는지 알아본다고 하자. payment 테이블에는 구매 고객 id 와 결제 금액이 있게 된다(+ 다른 칼럼들) 이때 고객 id 로 그루핑을 한 뒤 amount 의 합을 구하면 된다.
그리고 이것을 결제 금액의 합을 내림차순(DESC)으로 ORDER BY 하면 된다.

SELECT customer_id, SUM(amount) FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

만약 날짜별로 매출을 조회하려면 다음처럼 작성할 수 있다.

HAVING

SELECT company, SUM(sales)
FROM finance_table
WHERE company != 'Google'
GROUP BY company

결과에 대해 추가 필터링을 하고 싶을 때 having 을 통해 필터 추가가 가능하다.

SELECT company, SUM(sales)
FROM finance_table
WHERE company != 'Google'
GROUP BY company
HAVING SUM(sales) > 1000

을 실행하면 구글이 아닌 회사들로 필터링된 후 SUM(sales) 가 1000이상인 애들만 볼 수 있다.

아래 예시를 보면 customer id 로 GROUP BY 를 한 후에 합이 100이상인 것만 필터링을 했을 때 잘 실행된다.

<span style='color: grey'>해당 포스트는 Udemy 'The Complete SQL Bootcamp: Go from Zero to Hero' 를 보고 작성한 내용입니다.</span>


## SQL Statement

### SELECT Statement

Select 는 가장 일반적으로 사용되는 구문이다. 
이는 데이터베이스 내에 존재하는 테이블에서 정보를 검색할 때 사용된다.
아래 사용 예시를 보자. 

**사용 예시**
ex) SELECT column_name FROM table_name

데이터베이스는 있다고 가정하고, 해당 데이터내부에 몇 개의 테이블이 있다고 하자. 각 테이블은 칼럼과 데이터 행으로 구성될 것이다. 우리가 어떤 칼럼을 고를때 SELECT column_name FROM table_name 을 통해 어떠한 테이블 table_name 에서 해당 column_name 을 검색할 수 있다. 여러개의 칼럼도 다음처럼 적을 수 있다.

```sql
SELECT c1, c2 FROM table1
```

만약 모든 칼럼을 선택한다면 *를 사용한다.
```sql
SELECT * FROM table1 
```

다만, 일반적으로 * 을 사용하는 것은 트래픽을 증가시킬 수 있기 때문에 필요할 떄가 아니라면 권장되지 않는다.


### SELECT 실습

1. PGadmin 을 연다.


2. dvd 렌탈을 우클릭하여 쿼리 툴을 연다.
이에 대한 설명은 아래 게시글에 설명되어 있다.
https://velog.io/@aengzu/SQL-Database-%EB%9E%80-%EB%AC%B4%EC%97%87%EC%9D%B8%EA%B0%80-PostgreSQL-%EC%84%A4%EC%B9%98%ED%95%98%EA%B8%B0

3. dvd 렌탈의 schemas 의 table 을 보면 어떠한 tablel 이 있는지 확인할 수 있다. 그 밑에 칼럼까지 누르면 확인 가능


4. SELECT * FROM actor; 을 통해 배우 테이블의 모든 칼럼 검색하기


5. SELECT first_name, last_name FROM actor; 을 통해 배우 테이블의 특정 칼럼 검색하기(칼럼들 순서 바꾸면 바뀐채로 확인 가능)


6. 만약 SELECT FROM 을 소문자로 써도 작동 가능하다. (다만 대문자가 알아보기 쉽기에 표준화되어있다) 
7. 세미콜론은 쿼리의 끝을 나타낸다. 없어도 작동 가능하지만 있어야 가독성이 좋다. 




### SELECT 과제

1. 상황 : 우리는 기존 고객에게 프로모션 이메일 보내려고 한다. 
SELECT 문을 활용하여 고객의 이름과 이메일 문을 가져오자.

```sql
SELECT first_name, last_name, email FROM customers;
```



### SELECT DISTINCT

경우에 따라 테이블에 중복 값이 있는 열이 포함되어 있으며 우리는 고유한 값만 나열해야하는 상황에 처할 수 있다. 이때 열에서 DISTINCT 키워드를 사용해 고유값을 반환할 수 있다. 

SELECT DISTINCT 를 사용하여 고유한 값이나 고유한 값을 얻을 수 있는 열 바로 앞에 두고 그런 다음 이 열이 있는 테이블을 지칭한다. 

다른 열이 distinct 열로 지정 되는 것 막기 위해선 괄호 사용해도 된다. (괄호 없이도 작동한다.)

```sql
SELECT DISTINCT column FROM table;
```


예를 들어 아래와 같은 테이블이 있을 때, SELECT DISTINCT name FROM color_table 을 하면 name 칼럼 안에 David 인스턴스가 두 개가 있기 떄문에 이 테이블에서 DISTICT name 을 SELECT 하면 이 열에서 리턴하는 것은 중복을 배제한 3개일 것이다.
이렇게 되면 데이터의 손실이 발생할 수 있다. 따라서 이런 경우는 David 가 두개이기 때문에 이럴 때는 SELECT DISTINCT choice FROM color_table 을 진행하여 고유한 색상 옵션으로 하면 중복값을 효과적으로 제거할 수 있다.




DISTINCT 은 열 안의 고유값 알고 싶을 떄 도움이 된다. 예를 들어 film 테이블에서 개봉연도를 DISTINCT 로 SELECT 하면 하나의 데이터만 나온다. 모든 영화들이 2006년에 개봉되었다는 뜻이다. 만약 ()괄호를 쓰면 DISTICT 에 해당하는 하나를 더 명확히 지정해 줄 수 있다. 


DISTINCT 를 사용하면 고유한 값이 얼마나 되는 지 확인할 수 있다. 



### COUNT Statement

다음은 COUNT 문을 알아보자.COOUNT 는 쿼리의 조건에 맞는 행의 개수를 리턴한다. 

아까 예시에서 
```sql
SELECT COUNT(name) FROM table;
``` 
을 실행하면 4개가 리턴될 것이다. 

COUNT 는 단순히 해당하는 행의 개수를 리턴하기 때문에 SELECT COUNT(name) FROM table; 이나 SELECT COUNT(Choice) FROM table; 나 똑같이 4를 리턴한다.

따라서 개수를 세는 조건을 붙여야 유리하게 사용할 수 있다. 예를 들어 해당 테이블에서 고유한 이름이 몇 개인지 알아보려면

SELECT COUNT(DISTINCT name) FROM table; 을 사용하면 3을 리턴하여 고유한 name 의 개수를 확인할 수 있다.



### COUNT Statement 실습

만약 payment 테이블에서 staff_id 의 개수를 센다면 14,596개가 나온다. 

여기서 DISTINCT staff_id 의 개수를 세면 2개가 나온다. ~~2명이서 14,596개의 payment 를 담당했나보다.~~




### SELECT WHERE 구문

다음은 SELECT WHERE 을 알아보자. WHERE 은 조건을 추가할 수 있다.
우선 기본 문법은 다음과 같다.

```sql
SELECT column1, column2
FROM table
WHERE conditions;
```

WEHRE 절은 FROM 다음에 등장한다. 

- 비교 연산
=, >, <, >=, <=, <>, !=
    
- 논리 연산
AND, OR, NOT
    
또 아까봤던 예시로 확인하자.

David 인 사람만 검색하고 싶을 떄는 아래 문법을 사용한다.

```sql
SELECT name, choice FROM table WHERE name='David';
```
그럼 아래와 같은 결과가 나올 것이다.



### SELECT WHERE 실습

customer 테이블에서 고객 정보 필터링 연습을 해보자.
만약 누가 가게에 와서 first name 만 말한다고 가정해보자. 그럼 우리는 first name 으로 검색을 해야할 것이다. 아래 문법처럼 검색하면 된다.

```sql
SELECT * FROM customer
WHERE first_name='Julia';
```

만약 film 테이블에서 렌탈 비용과 교체 비용에 조건을 두고 검색을 하면 다음처럼 잘 출력된다. 조건은 얼마든지 추가할 수 있다. 



### ORDER BY

ORDER BY 는 행을 정렬할 때 사용된다. 

```sql
SELECT column_1, column_2
FROM table
ORDER BY column_1 ASC/DESC
```

Order by 는 쿼리 가장 마지막에 있으며, ASC 가 오름차순 / DESC 가 내림차순이라는 의미이다.

예를 들어, 아래 사진과 같은 테이블이 있을 때, 아래 테이블은 Company 에서 알파벳 순서로 먼저나오고 그 다음으로 Name 을 알파벳 순서로 나오고 그 다음으로 Sales 의 오름차순으로 정렬되어 있다. 


실습을 해보면, 아래 customer 테이블에서 first_name 의 알파벳 순서로 정렬하려면 다음처럼
SELECT * FROM customer
ORDER BY first_name; 
을 입력한다. 디폴트는 오름차순(알파벳순)이다.


만약 내림차순(역알파벳순)으로 보고싶다면 뒤에 DESC 라는 옵션을 추가한다.




### LIMIT 

LIMIT 키워드는 반환되는 쿼리의 행 수를 제한할 수 있다.

```sql
SELECT * FROM payment
ORDER BY payment_date DESC
LIMIT 5;
```

예를 들어 아래 처럼 마지막에 LIMIT 5 를 추가하면 전체 행수 중에서 상위 5개만 리턴하게 된다. 




### BETWEEN

BETWEEN 연산자은 값 범위와 값을 대조할 때 사용된다.

value BETWEEN low AND high 처럼 쓰여서 low 와 high 사이인 value 를 가진 애들만 리턴하게 ㅗ딘다. 
만약 해당 범위 아닌 애들만 출력하고 싶다면 NOT BETWEEN 을 사용한다.

아래 예시를 보면 payment 테이블에서 총합이 8에서 9 사이인 애들만 리턴된 것을 확인할 수 있다.
SELECT * FROM payment
WHERE amount BETWEEN 8 AND 9;

만약 COUNT 랑 같이 쓰면 해당 범위에 해당하는 row 의 개수를 알 수 있다. 위 예시에서 총합이 8에서 9 사이인  row 는 총 439개이다. 

이건 date 에도 적용 가능하다.



### IN

IN 을 사용하면 조건을 지정할 수 있다. 
예를 들어 아래와 같은 Statement에서 amount 가 0.99, 1.98, 1.99 인 애들만 반환하게 된다.
```sql
SELECT amount FROM payment
WHERE amount IN (0.99, 1.98,1.99);
```


### LIKE / ILIKE

문자열 패턴매칭과 관한 것이다.
예를 들어 이메일 중에 @gmail.com 으로 끝나는 애들을 출력하고 싶거나 'A'로 시작하는 애들만 출력하고 싶을 때, 
customer 테이블에서 J 로 시작하는 애들만 추출하려면 다음처럼 **LIKE '원하는 글자%'** 로 적으면 된다. 

```sql
SELECT * FROM customer
WHERE first_name LIKE 'J%';
```


만약 문자열 중간에 어떠한 글자가 들어간 애들만 추출하려면 앞 뒤에 %를 붙여서 쓰면 된다.
```sql
SELECT * FROM customer
WHERE first_name LIKE '%her%';
```




% 대신에 _ 를 쓰면 글자 수를 지정할 수 있다. 
예를 들어 
```sql
SELECT * FROM customer
WHERE first_name LIKE '_her%';
```

다음처럼 적는다면 her 앞에는 글자가 1개가 있는 애들만 추출할 수 있다. 

해당 포스트는 Udemy 'The Complete SQL Bootcamp: Go from Zero to Hero' 를 보고 작성한 내용입니다.

데이터베이스?

데이터베이스는 사용자가 데이터를 체계적으로 구성, 저장, 관리할 수 있게 해주는 시스템이다. 대량의 데이터를 효율적으로 처리하고, 다양한 사용자와 애플리케이션의 요구를 충족시키는 데 있어 핵심적인 역할을 한다.

데이터베이스는 다양한 사용자가 있을 수 있다. 마케팅 분석가, 영업 운영 분석가, 데이터 과학자, 웹 개발자, 소프트웨어 엔지니어 등 광범위한 분야의 전문가들이 데이터베이스를 활용함으로써, 복잡한 데이터 관리 작업을 보다 효과적으로 수행할 수 있게 된다.

웹사이트와 애플리케이션에서의 데이터베이스

현대의 웹사이트와 웹 애플리케이션, 모바일 애플리케이션은 대부분 데이터베이스와 연결되어 있다. 사용자의 정보를 저장하고, 콘텐츠를 관리하며, 다양한 서비스를 제공하는 데 데이터베이스가 핵심적으로 사용된다.

데이터베이스와 스프레드시트

일반 사용자는 대부분 엑셀이나 스프레드시트를 통해 데이터를 다루는 경험이 있을 것이다. 이러한 도구들은 비전문가도 데이터를 쉽게 처리할 수 있게 해주는 장점이 있다. 하지만 데이터의 규모가 커지고, 데이터 무결성이 중요한 상황에서는 데이터베이스가 훨씬 적합하다. 데이터베이스는 무결성을 유지하며, 허가된 사용자만 데이터를 변경할 수 있도록 관리할 수 있기 때문이다.

데이터베이스 플랫폼 선택

여러 데이터베이스 플랫폼 중에서 PostgreSQL, MySQL, MariaDB, MSSQL Server Express, Microsoft Access, SQLite 등이 널리 사용된다. PostgreSQL은 무료(오픈 소스)이며, 멀티 플랫폼에서 널리 사용되는 경우에 주로 선택된다. 이번 강의에서도 PostgreSql 을 사용할 예정이다.

SQL

SQL(Structured Query Language)은 데이터베이스와 소통하는 데 사용되는 프로그래밍 언어이다. 데이터를 조회, 수정, 삭제 등의 작업을 수행할 수 있게 해주며, 데이터베이스 관리의 기본이 된다.

SQL 예시

이 SQL 쿼리는 sales 테이블에서 고객의 ID, 이름, 성을 선택하고, 이름 순으로 정렬한다.

SELECT customer_id, first_name, last_name
FROM sales
ORDER BY first_name;

Mac 에서 PostgreSQL 설치하기

PostgreSQL 과 PgAdmin 을 설치할 것이다. PostgreSQL 은 SQL 엔진이다. 데이터를 보관하고 쿼리를 읽는 그리고 정보를 반환하는

PgAdmin 은 PostgreSQL 백엔드와 연결하기 위한 GUI 이다.

설치 진행 시 다음 요소들을 기억하자

• 절대 dvdrental.tar 파일을 직접 열지 말자
• PostgreSQL 패스워드를 지정할 것인데 잊지 말고 잘 적어두자.

1.PostgreSQL 다운받기
구글 크롬 브라우저에서 PostgreSQL 입력후 공식 웹사이트에 들어간다.

PostegreSQL 다운로드 버튼을 누른다.

다양한 패키지가 있을텐데 각자의 운영체제에 맞는 것을 설치한다. 나는 macOS 를 클릭하였다.

다음과 같은 패키지, 버전이 있는데 맥 OS 플랫폼이 가지고 있는 PostgreSQL 버전과 일치하는 지 확인해야한다. 내 mac 컴퓨터가 오래된 것이라면 최신의 PostgreSQl 을 다운해도 잘 작동하지 않을 수 있다!

download the installer 클릭 후 버전을 선택하면 된다. 나는 최신버전이라 최신 버전의 PostgreSQL 을 설치하였다.
그러면 압축 파일이 설치될 것인데 이를 풀어준다. 이런 화면이 뜰 것이다.

계속해서 다음을 누른다. 아래와 같은 화면에서는 모두 체크된 상태 그대로 다음을 누른다.

다음으로 암호 입력 창이 뜰 것이다. 무조건 기억할 수 있는 암호를 입력해야한다.
까먹으면 골치 아파질 수도 있다.

포트 번호를 선택해야한다. 이전에 PostgreSQL 을 설치했는지 여부에 따라 다를 수 있는데 일반적인건 5433 으로 나타난다. 이건 포트가 열려있고 서버가 이를 수신할 수 있음을 의미한다. 이 숫자는 따로 수정할 필요가 없고 next 를 누르면 된다.

다음은 Location 인데 바로 next 를 누르고 계속해서 next 를 누르면 설치가 될 것이다.
설치가 되었다면 Stack Builder 는 클릭 해제 후 Finish 를 누르면 된다.

2. PgAdmin 설치하기

PostgreSQL 이 설치되었다면 다음으로는 PGAdmin 을 설치할 것이다. 우선 브라우저로 이동하여 pgadmin 을 구글에 서치한다.
공식사이트에 들어가서 Download 를 클릭한다. 여러 패키지가 있을 텐데 나는 macOS 를 클릭하였다.

나는 최신 버전의 pgAdmin 4 v8.2 를 클릭하였다.

그 후, Files 에 보면 dmg 파일이 있을 건데 자신의 mac 이 arm64인지 x86인지 잘 확인 후 누르면 된다. 나는 arm64를 클릭하였다.

설치된 파일을 클릭한 후, 다음 창이 뜨면 Application 으로 옮겨준다.

이러면 전체 설치 프로세스는 끝이다.

3. dvdrental.tar 파일 다운하기(직접적으로 open 하지 않기 주의)

다음 단계는 dvdrental.tar 파일을 다운로드하는 것이다. 주의할 것은 직접 오픈해서는 안된다. 파일 압축을 풀거나 더블클릭하거나 파일을 열거나 이런 상호작용을 하지 않아야 한다. 그저 다운만 하면 된다. dvdrental.tar 파일은 샘플 데이터파일이다. 아래 사이트에서 다운할 수 있다.

dvdrentar.tar 다운받기

4. 재부팅하기
   제대로 깔렸는지 확인을 위해 컴퓨터를 재부팅한다.

5. 데이터베이스를 저장하기(실패한 종료 코드가 표시되면 무시한다.)
   재부팅이 되었다면 pgamdin 을 설치한다. 아마 Application 에 있을 것이다.

왼쪽에 있는 Server 를 열고 설치한 PostgreSQL 16을 확장하려고 하면 다음같은 비밀번호 입력 팝업창이 뜬다. 이때 아까 지정했던 PostgreSQL 비밀번호를 입력하면 된다.

비밀번호를 입력하면 다음과 같은 화면이 뜰 것이다.

데이터베이스를 만들기 위해 왼쪽 탭의 Databases 클릭 후 우클릭을 하여 Create-> Database 를 선택한다. Database 에는 dvdrental 을 입력한다. 그 후 save 를 누른다.

입력됐다면 우리가 아까 다운받은 dvdrental.tar 파일, dvd rental 정보를 실제로 입력할 것이다. 왼쪽의 dvdrental 을 우클릭하여 restore 을 클릭한다.
그 후 나온 팝업창에서
dvdrental.tar 파일의 위치를 적고, Data Option 에서 다음 세 개를 활성화 한 뒤 Restore 을 클릭하면된다.
그런 다음 적용을 위해 dvdrental 우클릭 후 Refresh 를 한 번 눌러주자.

이제 잘 되는 지 확인해볼 것이다. 왼쪽 dvdrental 우클릭 후 Query Tool 을 클릭하면 다음과 같은 Query pad 가 뜰 것이다. 여기서 SELECT * FROM film; 을 입력해보자. 그럼 영화 데이터들을 확인할 수 있다.

SELECT * FROM film;

결론

데이터베이스는 현대 디지털 환경에서 필수적인 요소로, 대량의 데이터를 체계적으로 관리하고 활용할 수 있게 해줍니다. 다양한 분야의 전문가들이 데이터베이스를 사용하여 복잡한 데이터 관리 작업을 수행할 수 있으며, 이는 마케팅 분석부터 소프트웨어 개발까지 광범위한 응용을 가능하게 합니다. 데이터베이스와 스프레드시트는 모두 데이터를 다루는 도구지만, 데이터의 규모가 커지고 무결성이 중요한 상황에서 데이터베이스가 더욱 적합한 솔루션을 제공합니다.

이번 포스트에서는 데이터베이스 플랫폼 중 하나인 PostgreSQL의 설치 과정을 통해 데이터베이스의 기본적인 활용 방법을 살펴보았습니다. PostgreSQL은 무료이며 멀티 플랫폼에서 널리 사용되는 오픈 소스 데이터베이스 시스템으로, SQL(Structured Query Language)을 사용하여 데이터베이스와 소통합니다. SQL은 데이터 조회, 수정, 삭제 등의 작업을 수행할 수 있게 해주며, 데이터베이스 관리의 기본입니다.

SQL

데이터 유형 

VARCHAR 유형은 가변 길이 이므로 필요한 영역은 실제 데이터 크기 뿐이다. 따라서 길이가 다양한 칼럼과, 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼에 적합하다. 저장 측면에서도 CHAR 유형보다 작은 영역에 저장할 수 있으므로 장점이 있다.

CHAR 예서는 문자열을 비교할 때 공백을 채워서 비교하는 방법을 사용한다. 

 ex) CHAR 유형 'AA' = 'AA ' // VARCHAR 유형 'AA' != 'AA '

테이블 생성 규칙

1. 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다.가능한 단수형을 권고한다.

2. 테이블명은 다른 테이블의 이름과 중복되지 않아야한다.

3. 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.

4. 테이블 이름을 지정하고, 각 칼럼들은 괄호 "()" 로 묶어 지정한다.

5. 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.

6. 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.

7. 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.

8. 벤더에서 사전에 정의한 예약어는 쓸 수 없다.

9. A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

DDL

: 데이터 정의어

- 데이터베이스를 정의하는 언어를 말하며, 데이터를 생성하거나 수정, 삭제 등 데이터의 전체 골격을 결정하는 역할의 언어

- Create : 데이터베이스, 테이블 등을 생성

- alter : 테이블을 수정

- drop : 데이터베이스 , 테이블을 삭제

-trucate : 테이블을 초기화

선수 테이블 생성 DDL 예시

데이터와 정보

데이터

• 비트들의 모음
• 현실 세계로부터 단순한 관찰이나 측정을 통해 수집된 사실이나 값

정보

• 데이터를 처리한 결과로 만들어짐
• 어떤 상황에 대한 적절한 결정을 할 수 있게 하는 근거
• 데이터의 유효한 해석이나 문맥을 부여해 주는 것

정보 시스템

한 기관을 위해 데이터를 수집, 조직, 저장하고 필요 시에 처리하여 의사 결정에 유용한 정보를 생성, 분배하는 수단

데이터베이스 시스템

데이터베이스 활용

비즈니스(Business) 분야에서 중요한 비즈니스데이터를 저장하고, 그 데이터의 정확성을 유지하고, 필요할 때 이용이 가능하도록 하는 모든 기능을 데이터베이스에 의존

ex)데이터베이스 : 모든 판매 기록을 저장

회사를 운영하고 판매 전략을 세우기 위해 데이터 분석을 할 때 이용

지식을 창출 : 예) 장바구니 분석

축적된 지혜를 만듦 : 고객의 성향 및 일반적인 행동 분석

대량의 데이터 관리가 필요한 대부분의 응용은 데이터베이스 시스템을 이용

데이터베이스 시스템(Database System) 구성 요소

<구성 요소> = 소프트웨어 + 데이

A. 물리적 데이터베이스 : 데이터 내용을 포함하는 파일들의 집합

B. 스키마(메타데이터) : 물리적 데이터베이스의 내용을 명세

C. 데이터베이스 관리 시스템 : 데이터베이스 안의 내용을 접근하고 변경할 수 있는 소프트웨어

D. 데이터 정의, 관리 언어 : 스키마를 정의하고 데이터베이스에 접근하기 위해 사용하는 언어

A. 데이터베이스 : "한 조직의 여러 응용 시스템들이 공용하기 위해 통합, 저장한 운영 데이터의 집합"

1. 통합된 데이터(최소의 중복, 통제된 중복)

2. 저장 데이터 : 컴퓨터가 접근 가능한 저장 매체에 저장(디스크 등)

3. 운영 데이터: 한 조직의 고유 기능을 수행하기 위해 필요한 데이터

4.공용 데이터 : 한 조직에 있는 여러 응용 프로그램이 공동으로 소유, 유지, 이용하는 데이터

특징

1. 온라인 접근성 : 질의에 대한 실시간 처리 및 응답

2. 계속적인 변화 : 새로운 데이터의 삽입, 기존 데이터의 삭제, 갱신으로 현재의 상태를 유지, 동적 특성

3. 동시 공용 : 서로 다른 목적을 가진 응용들을 여러 사용자가 동시에 사용

4. 내용에 의한 참조 : 위치나 주소 아닌 값에 따라 참조

B. 스키마

- 데이터베이스에 대한 기술

-DBMS 카탈로그에 저장(카탈로그에 저장된 정보를 메타데이터라고 부른다)

-데이터베이스 시스템은 자기 기술적

-DBMS 는 시스템 카탈로그로부터 데이터베이스에 대한 정의를 가져와 데이터베이스를 접근하므로 다양한 데이터베이스들을 접근할 수 있다.

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

- 응용 프로그램과 데이터의 중재자로서 모든 응용 프로그램이 데이터베이스를 공용할 수 있게 관리해주는 소프트웨어 시스템

-기능

정의기능

:하나의 저장 구조로 여러 사용자의 관점을 만족시킬 수 있도록 데이터베이스 구조를 정의할 수 있는 기능

(요건 : 데이터의 논리적 구조 명세, 물리적 저장 구조 명세, 물리적/논리적 사상 명세)

조작기능

:사용자-데이터베이스 인터페이스/ 사용자의 요구에 따라 체계적으로 데이터베이스를 접근하고 조작할 수 있는 기능

(요건: 사용이 쉽고 자연스러워야, 연산의 완전한 명세 기능, 효율적인 데이터 접근)

제어기능

: 공용 목적으로 관리되는 데이터베이스의 내용에 대해 데이터의 정확성과 안전성을 유지하는 기능

(요건 : 데이터의 삽입,삭제, 갱신 작업에 대한 데이터의 무결성 유지, 정당한 사용자가 허가된 데이터만 접근 할 수 있도록 보안 유지 및 권한 검사, 여러 사용자가 데이터베이스를 동시에 접근하여 데이터를 처리할 때 데이터베이스와 처리 결과가 항상 정확성을 유지하도록 병행 수행 제어)

데이터베이스 시스템 사용자

응용 개발자 : 데이터베이스 시스템의 기능을 이용해 응용 프로그램을 설계하고 개발 > 사용자 인터페이스, 데이터 분석 프로그램, 다양한 비즈니스 서비스와 같은 응용 프로그램

데이터베이스 관리자 : 데이터베이스 관리, 관리업무(데이터 시스템 접근 통제, 데이터의 정확성과 무결성 유지, 데이터베이스 성능 감시와 향상)