우아 한의원 | 조재성 원장의 한의학, 의학통계, 프로그래밍

문자열로 입력된 날짜를 임시로 만들고 년/월/일을 짤라놓은 뒤 || 을 이용해 년 월 일을 '-'로 연결한 뒤,다시 DATE타입으로 바꾸어 연산해보는 공부

• 칼럼을 생성하고 싶다면 : SELECT절에서  값 AS 칼럼명
• timestamp 1개로 년/월/일 3개로 나누기 : TOCHAR_( date or timestamp, ' date 기호 ')
• || : SELECT절에서 문자열칼럼 or 생성한문자열 을 concat해준다.
• 둘다 DATE타입일 경우, 마이너스 연산이 가능하다. 연산결과는 n days + 시간 -> int로 캐스팅해야 숫자로 사용( 나누기 365 등)할 수 있다.
• PosgreSQL에서 birthday를 만나이로 계산하는 순서(년/월/일)로 나뉘어있다고 가정 : 
  (1) || 을 통해 YYYY-MM-DD형태로 변환
  (2) DATE타입으로 캐스팅
  (3) NOW()현재날짜와 (-) 연산
  (4) int타입으로 캐스팅
  (5) /365해주기
  
  
• MSSQL에서 birthday를 만나이로 계산하는 순서( YYYY-MM-DD)로 나뉘었다고 가정 :
  (1) DATEDIFF( day, 시작날짜, GETDATE()) / 365 로 자동연산

전처리

1. 기존 테이블
   

2. SELECT에 < 한개의자료 AS 칼럼명 > 으로 새 칼럼을 생성한 뒤, INTO로 테이블 생성

   • '문자열' AS 칼럼명 (text형으로 칼럼생성)
   • NOW() AS 칼럼명 (timestamp형로 칼럼생성) 등등.. 다된다.

     SELECT *, NOW() AS time INTO students_with_birthday FROM students;
     

     

3. time칼럼에 날짜 입력해주기

   UPDATE students_with_birthday SET time = '1987-01-30' WHERE name ='Moe';
   UPDATE students_with_birthday SET time = '1988-03-29' WHERE name ='Larry';
   UPDATE students_with_birthday SET time = '1980-02-28' WHERE name ='Rolf';
   UPDATE students_with_birthday SET time = '1987-06-27' WHERE name ='Anne';
   UPDATE students_with_birthday SET time = '1991-02-26' WHERE name ='Robert';
   UPDATE students_with_birthday SET time = '1958-01-26' WHERE name ='Jose';
   

   

4. 날짜(YYYY-MM-DD)를 문자열 & 년/월/일 로 분리하여 칼럼 만들고, 확인되면 INTO 넣어서 필요한 칼럼들만으로 테이블로 만들기

   SELECT *,
    TO_CHAR(time, 'YYYY') as year_of_birth, 
    TO_CHAR(time, 'MM') as month_of_birth, 
    TO_CHAR(time, 'DD') as day_of_birth 
   FROM students_with_birthday
   

   

   SELECT name, current_mood,
    TO_CHAR(time, 'YYYY') as year_of_birth, 
    TO_CHAR(time, 'MM') as month_of_birth, 
    TO_CHAR(time, 'DD') as day_of_birth 
   INTO students_birthday
   FROM students_with_birthday
   SELECT * FROM students_birthday
   

   

문자열인 년/월/일 칼럼을 || 과 sep '-'로 붙히는 연습해보기

1. 문자열칼럼 || '문자열' or 문자열 칼럼

   SELECT year_of_birth  FROM students_birthday                         -- 2019
   SELECT year_of_birth||'-'  FROM students_birthday                     -- 2019-
   SELECT year_of_birth||'-'||month_of_birth  FROM students_birthday            -- 2019-02 
   SELECT year_of_birth||'-'||month_of_birth||'-'||day_of_birth FROM students_birthday     -- 2019-02-01
   

2. 문자열 YYYY || MM || DD 를 concat(||)으로 붙힌 것을 CAST( AS DATE)으로 타입 변환하기 ( YYYY-MM-DD 형태 아니면 안됨 )
   

   SELECT CAST( year_of_birth||'-'||month_of_birth||'-'||day_of_birth AS DATE),* FROM students_birthday 
   

3. date타입으로 바꿨으면, 특정시간칼럼과 연산이 가능하다.

   SELECT NOW() - CAST( year_of_birth||'-'||month_of_birth||'-'||day_of_birth AS DATE) FROM students_birthday 
   

   

4. 날짜(둘다 DATE여야함.) 차이결과는 days +시간으로 나온다. 이것을 int로 다시 형변환하면, day숫자만 나온다. * NOW()는 date가 아니라 timestamp로서 다른 타입이니 또 캐스팅
   

   SELECT CAST( CAST(NOW() AS DATE) - CAST( year_of_birth||'-'||month_of_birth||'-'||day_of_birth AS DATE)  AS INT) FROM students_birthday 
   

5. day를 /365 로 나누면, 만나이(년수)가 나온다.

   SELECT CAST( CAST(NOW() AS DATE) - CAST( year_of_birth||'-'||month_of_birth||'-'||day_of_birth AS DATE)  AS INT) / 365 FROM students_birthday 
   

   

총 정리

1. SEQUENCE와 달리, CREATE할 필요없이 그냥 테이블 생성시 칼럼 TYPE에 SERIAL 지정해주면 된다.
2. 단, INSERT INTO 시, 테이블명옆에 table( serial칼럼을 제외한 칼럼명들)을 명시해주고 입력해야한다.
   만약, SERIAL 이외 칼럼 명시없이, 제외하고 입력하면 오류난다.

SERIAL

-- SERIAL type 
-- : postgreSQL에 없는 autoincrement를 SEQUENCE로 했었지만, 그럴 필요없이 쉽게할 수있는 type제공
-- (SEQUENCE START n -> SET -> OWNED BY 의 과정이 필요없어진다!)


-- (1) TABLE 생성시 type으로 SERIAL만 주면 된다. 1부터 시작한다.
CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    name character varying(255)
)


-- (2) 입력시 id와 name 중 name만 주면 된다.
INSERT INTO test(name)
VALUES ('chojaeseong');


SELECT * FROM test

총 정리

ENUM

1. ENUM 생성 : CREATE TYPE 이넘명 AS ENUM( '순서범주1', '순서범주2', '순서범주3' ) 형식으로 생성
2. 테이블 생성시, datatype으로 지정해주기
3. INSERT INTO 시 ENUM에 속한 범주만 입력가능
4. WHERE 절에 특정범주에 부등호(< or >)를 통해 여러범주를 필터링할 수 있다.

NESTED SELECT

1. 집계하는 테이블에서는, 집계결과를 따로 칼럼을 보기 위해서는, GROUP BY 에 넣어주고 그 칼럼별 집계밖에 못보지만,
   집계결과를 따로 NESTED SELECT(SubQuery)로 취급하면 -> SELECT문에서 연산에 사용 or WHERE의 조건으로 걸 수 있다.
   
   cf) 서브쿼리는 줄을 바꿔주고 tab하나 넣는게 보통양식
   cf) SELECT 집계연산 FROM 테이블 WHERE 조건 ---> 조건까지 만족하는 테이블 이후 집계가됨
   cf) alias 칼럼명은 "" 쌍따옴표로 만들어주자.

ENUM

-- 1) ENUM 

--     (1) 새로운 dataTYPE을 만드는데 ENUM으로서 만들자. ENUM()안에 limited된 String value들을 정의해주면,
--        ***넣은 순서대로 순서를 가지는*** 순서형 범주(Ordinal <-> Nomial명목형) 데이터 타입이 된다.
 CREATE TYPE mood AS ENUM('extremely unhappy', 'unhappy', 'ok', 'happy', 'extremely happy');

--    (2) 새로 생성한 ENUM type의 mood를 칼럼의 type으로 지정해서 테이블을 생성하자.
 CREATE TABLE students (
name character varying(255),
current_mood mood 
 );

 SELECT * FROM students --mood type의 칼럼이 생김

INSERT INTO students
VALUES ('Moe', 'hapy') -- ENUM 범주에 없는 값을 입력시키면 에러가 난다.

INSERT INTO students
VALUES ('Moe', 'happy')

INSERT INTO students
VALUES ('Larry', 'happy')

INSERT INTO students
VALUES ('Rolf', 'extremely unhappy')

INSERT INTO students
VALUES ('Anne', 'extremely happy')

INSERT INTO students
VALUES ('Robert', 'unhappy')

INSERT INTO students
VALUES ('Jose', 'happy')



SELECT * FROM students



 -- (3) ENUM은 순서형 범주(Ordinal)로서, WHERE 절에서 value로 필터링 할 수 있다.
 -- enum을 이용하면, string형으로 순서를 줄 수 있어서, 회원등급을 메길 수 있고
 -- 설문score도 적용시킬 수 있다. 등등 
 SELECT * FROM students WHERE current_mood > 'ok';

NESTED SELECTD

 -- NESTED SELECT : 집계결과를, 집게테이블에서 WHERE문 or SELECT에 다  쓸 수 있다.
 -- 매우 유용함. 쿼리 안에 쿼리가 들어가는 구조

 -- 예제1) 보다 높은 가격의 item들 목록

 -- 내가 한 것)
 SELECT AVG(price) FROM items        -- 73.16125
 SELECT * FROM items WHERE price > 73.16125

 -- 좋은 것으나 안되는 것)
 SELECT * FROM items WHERE price > AVG(price)

 -- 또다른 조건을 줄때는 HAVING을 쓰나, Group by 된 상태만 된다.

 -- 정답1) NESETED SELECT를 이용해야만, 집계테이블에서, 집계결과를, WHERE문에 사용할 수 있다.
--    (1) 평균(집계)결과값을 사용할 부분을 제외한 WHERE문 작성
SELECT * FROM items WHERE price > 
();
--    (2) 집계결과를 bracket()에 작성
SELECT * FROM items WHERE price > 
(SELECT AVG(items.price) FROM items);


--    my) 이전에 했었던 아이템 목록중 가장 비싼 아이템 골라오기
SELECT * FROM items WHERE price = 
(SELECT MAX(price) FROM items);





-- 예제2 ) 아이템들의 가격이 <아이템 평균가격>보다 얼마나 비싸거나 쌀까?

-- 내가 한 것 )
SELECT * FROM items;
SELECT AVG(price) FROM items;
SELECT items.name, items.price, () FROM itesm;
SELECT items.name, items.price, (SELECT AVG(price) FROM items) FROM items;
SELECT items.name, items.price - (SELECT AVG(price) FROM items) FROM items;


 -- 정답 1)
--    (1) 평균집계값을 () 으로 비워두고, 집계테이블에서 필요한 칼럼들을 SELECT
--        가격의 비교를 위해서는 빼야한다. numeric은 SELECT 란에서 바로 (-)된다.
SELECT items.name, items.price - () FROM items;

--    (2) 서브쿼리의 기본 형식은 라인을 바꿔주고, tab을 넣는 것이다.
SELECT items.name, items.price - (
    SELECT AVG(price) FROM items
) FROM items;





-- 예제3 )  expensive_items아이템들의 가격이
--       과 얼마나 차이나는가?
--        를 VIEW로 만들기

-- 내가 한 것)
SELECT * FROM items;
SELECT * FROM items WHERE price > 80;

SELECT AVG(price) FROM items WHERE price > 80; -- 155.163 : FROM WHERE이후 --> WHERE절로 필터링
SELECT AVG(price) FROM items;    --73.16

SELECT items.name, items.price - () FROM items;

SELECT items.name, items.price - (
    SELECT AVG(price) FROM items WHERE price > 80
) FROM items;

-- 내가 catch못한 것 : 80달러보다 비싼 아이템의 평균값만 구하고, 
-- 그 이후 바깥의테이블에서는 80달러보다 비싸다는 WHERE를 안달아줘서, 모든 items들이 다 나왔음.
-- 문제를 잘못받아적음. 기본적으로 expensive 아이템가격들의 expensive아이템의 평균과의 차이 
-- ***FROM WHERE 필터링 이후--> SELECT의 집계연산이 이루어진다.


-- 정답3 )
--    (1) 80달러보다 비싼 아이템의 평균  = WHERE 절까지 거친 테이블에 집계함수가 일어남
SELECT AVG(items.price) FROM items WHERE price > 80;

--    (2) 다른칼럼들과 함께 + price - (1) 의 차이 SELECT하기 + WHERE 절 안빠트리기
SELECT *, items.price - (

) 
FROM items WHERE price > 80;

--    (3) NESTED SELECT 넣어주기
SELECT *, items.price - (
SELECT AVG(items.price) FROM items WHERE price > 80
) 
FROM items WHERE price > 80;

--    (4) VIEW로 만들기
CREATE VIEW expensive_items_diff AS 
SELECT *, items.price - (
SELECT AVG(items.price) FROM items WHERE price > 80
) 
FROM items WHERE price > 80;

--    (5) VIEW SELECT해보기
SELECT * FROM expensive_items_diff

--    (6) 칼럼이름이 없어서, 다시 DROP후 서브쿼리 ()끝나고 AS로 칼럼명 " "로 alias 정의해주기
DROP VIEW expensive_items_diff;

CREATE VIEW expensive_items_diff AS 
SELECT *, items.price - (
SELECT AVG(items.price) FROM items WHERE price > 80
) AS "average_diff"
FROM items WHERE price > 80;

SELECT * FROM expensive_items_diff;

총 정리

# TO_CHAR() : timestamp -> 문자열 or 영어로표현된 문자열로

1. 현재시간  : MSSQL : GETDATE() / PostgreSQL : NOW()
2. 날짜-> 문자열로 변환 : MSSQL : CONVERT( datatype, 시간(시간칼럼), date type number) / PostgreSQL : TO_CHAR( 시간(시간칼럼) , 'date type' )
3. TO_CHAR(  ,  )에 들어가는 datetype에서,  YYYY=yyyy, MM=mm, DD=dd, HH=hh 대소문자 구분안하고 년/월/일/시이다.
4. 분(minute)는 MI = mi
   
   --위는 TIMESTAMP 형식 : [ YYYY-MM-DD HH:MI:SS ]   
   
   --아래에서는 MM과 DD 대신 --> [  요일(FMDAy) - 일의 th형식   + 월의 영어표현(FMMonth) ] -----
      [ FMDay FMDDth FMMonth, YYYY HH:MI:SS]
   
   
5. 요일은 Day = day = DAY 가 있다. 그러나 공백을 제거하기 위해서는 FM을 붙힌 FMDay = FMDAY = FMday 
6. 일의 th 표현은 DDth = ddth이다.
7. 월의 영어표현은 FMMonth = FMMONTH = FMmonth 이다.

# TO_TIMESTAMP() : 문자열 -> timestamp 로 

1. TO_TIMESTAMP( '문자열로 표현된 시간' , ' 그 문자열의 DATE TYPE 형식' )을 넣어주면, timestamp로 변환

TIMESTAMP

-- DATE
-- date는 많은 문제가 있다. 
-- 1) datetime의 형태가 너무 많다. ex> 2019-01-30  or  01-30-2019 or 01. 30. 2019
-- 2) timezone개념이 붙어있다. ex> GMT, RUSSIAN, UTC 등등 장소에 따라 current time이 달라진다.

-- >> 추천하는 방법 :  SQL에는 TIMEZONE을 입력하지 않고, python 등 어플리케이션 단에서 TIMEZONE을 적용한다.

-- PostgreSQL의 메인 date 타입 4가지 : timestamp, date, time, interval

-- 1) timestamp
--    ISO 8601 standard date 타입 in DB.  YYYY-MM-DD HH:mm:ss 
--    pgSQL의 default 형식으로 많은 문제를 예방할 수 있음.
SELECT timestamp '2019-01-30 13:30:45';



-- 2) timestamp to string
--    (1) NOW()로 현재시간을 가져온다 <--> MSSQL의 GETDATE() or DATEDIFF( 간격, datetime, GETDATE())
--    (2) TO_CHAR( timestamp, 'DD' 등 날짜포맷 ); 으로 문자열로 전환 <--> MSSQL의 CONVERT( type, datetime, style)

SELECT NOW(); --MSSQL에서는 GETDATE() 였다! 

SELECT TO_CHAR( NOW(),'DD' ); -- MSSQL의 CONVERT( type, datetime, style) or DATEDIFF( 간격, datetime, GETDATE())
SELECT TO_CHAR( NOW(),'dd' );
-- text 30

SELECT TO_CHAR( NOW(),'MM' );
SELECT TO_CHAR( NOW(),'mm' );
-- text 01 

SELECT TO_CHAR( NOW(),'YYYY' );
SELECT TO_CHAR( NOW(),'yyyy' );
-- text 2019 

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY')
-- text 30-01-2019  
SELECT TO_CHAR( NOW(), 'DD-MM-YY')
-- text 30-01-19   ==> confusing

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY HH')
-- text 30-01-2019  02 

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY HH:MI'); -- pgSQL에서는 MM,mm month / MI :minute
-- text 30-01-2019  02:28 

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY HH:MI:SS');
-- text 30-01-2019  02:28:08

SELECT TO_CHAR( NOW(), 'Day, DD-MM-YYYY HH:MI:SS');
-- text Wednesday, 30-01-2019  02:28:08
-- 만약 토요일이면, Saturday , 형식으로 빈칸이 하나 발생한다 그걸 제고 하고 싶다면 Day앞에 FM을 쓴다.

SELECT TO_CHAR( NOW(), 'FMDay, DD-MM-YYYY HH:MI:SS');
-- text Wednesday, 30-01-2019  02:28:08


SELECT TO_CHAR( NOW(), 'FMDay Month, DD-MM-YYYY HH:MI:SS');
-- text Wednesday January  , 30-01-2019  02:28:08

SELECT TO_CHAR( NOW(), 'FMDay FMMonth, DD-MM-YYYY HH:MI:SS');
-- text Wednesday January, 30-01-2019  02:28:08


--날짜를 3th 4th 형식으로도 줄 수 있다.
SELECT TO_CHAR( NOW(), 'FMDay DDth FMMonth, DD-MM-YYYY HH:MI:SS');
-- text Wednesday 30th January  , 30-01-2019  02:28:08


-- 이제 DD-MM 을 제거해서 보자.
SELECT TO_CHAR( NOW(), 'FMDay DDth FMMonth, YYYY HH:MI:SS');
-- text Wednesday 30th January, 2019 02:28:08



-- 3) 이제 TO_TIMESTAMP( '마지막에 작성한 양식의 string' , 'string과 똑같은 양식의 날짜포맷' );
--    를 통해 sting을 timestamp로 바꿀 수 있다.

SELECT TO_TIMESTAMP('Wednesday 30th January, 2019 02:28:08', 'FMDay DDth FMMonth, YYYY HH:MI:SS');

-- 4) string양식이 ISO8601 이었으면, 그 string에 맞게 양식을 적어주면, timestamp로 변한다
SELECT TO_TIMESTAMP('2019-01-30 02:28:08', 'YYYY-DD-MM HH:MI:SS');

총정리

1. HAVING은 GROUP BY 끝난 쿼리문에서 조건을 추가적으로 달 수 있다.
2. HAVING은 1차 처리된 칼럼(COUNT()등)을 조건을 걸고 싶을 땐, alias사용이 안된다.

HAVING

-- 내장함수 & HAVING 

-- 내장함수의 종류 : 
-- COUNT(), SUM()
-- 예제 ) 고객별 구매횟수 ( 횟수, 건수 : 해당 테이블의 id를 카운트 해버리면 된다)
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id

-- AVG()
-- 예제 ) 아이템의 가격 평균
SELECT AVG(items.price) FROM items;

-- 예제 ) 구매된(join으로 교집합) 아이템  가격의 평균
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id;

SELECT AVG(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id;



-- 1) 구매된 아이템 중 가격 TOP 1 을 구하는 첫번째 : JOIN 구매된 아이템  -> ORDER BY -> LIMIT
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id;

SELECT items.name, items.price FROM items
INNER JOIN purchases ON items.id = purchases.item_id

SELECT items.name, items.price FROM items
INNER JOIN purchases ON items.id = purchases.item_id
ORDER BY items.price DESC
LIMIT 1;


-- 2) 구매된 아이템 가격 TOP 1을 구하는 두번째 : JOIN 구매된 아이템 -> MAX() ? 
--    : 집계테이블이 되어, 같은 테이블 내 칼럼선택(items.name)을 못함

--    만약, 다른컬럼(items.name)도 같이보려고, 집계함수 앞에 SELECT 시
--        ~별 기준이 되어버려서, 각 items.name 별 최대값들이 여러 row로 나옴.

SELECT MAX(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id;    -- 집계테이블로서 1개 칼럼밖에 못봄

SELECT items.name, MAX(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id    
GROUP BY items.name;-- 하나더보려고 select + groupby에 넣으면 기준테이블이 되어 각 아이템별로 최대값이 되어버림


-- 3) HAVING = GROUP BY + 집계함수 결과에 filter링 (집계함수의 WHERE절)
--    (1) 고객별 구매횟수를 위에서 작성했떤 것 복사해오자.
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id

--    (2) GROUPBY  + 집계함수의 결과에서 HAVING을 이용하여 다시 3개이상의 구매자만 걸러내보자.
--        *** 이때, 집계한칼럼의  alias로는 HAVING에서 조건을 걸 수 없다.
--        *** 1차 작업물이 SELECT 문에 있을시 -> 이후 사용은 그 문장 그대로!
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) AS purchase_count FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id
HAVING purchase_count > 2;     -- alias 사용시 오류

SELECT customers.first_name, customers.last_name, COUNT(purchases.id) AS purchase_count FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id
HAVING COUNT(purchases.id) > 2;

-- 4) 다음에 같은작업(고객별 구매횟수가 가장 많은 사람 뽑기)할일이 있다면, VIEW로 만들어보자.

총 정리

1. DROP TABLE IF EXIST 테이블명
2. 종속관계가 있는 테이블은, 참조하고 있는 테이블부터 지워야 삭제 가능하나, 종속관계를 끊고 삭제하려면 DROP TABLE 테이블명 CASCADE;
   

VIEW정리

1. VIEW 생성 : CREATE VIEW 뷰명 AS  groupby복잡한 쿼리문     을 통해 updatable한 테이블을 생성할 수 있음.
2. VIEW 조회 : SELECT * FROM 뷰명  으로 테이블조회하듯이 조회한다.
3. VIEW 삭제 : DROP VIEW 뷰명
4. VIEW를 조회할 때 WHERE / ORDER BY달고 조회가능 / 
5. VIEW + WHERE 절 달아서 새로운 VIEW 생성가능
   
   
6. VIEW생성시 AS쿼리문에 1) * 모든칼럼 선택한상태 + 2) * group by 등 집계안한 상태 + 3)WHERE문 정도 라면 : INSERT INTO VIEW 로 값 대입 가능, 특별한 옵션이 없다면 WHERE조건 만족못해도 원본테이블에 들어감
7. WITH LOCAL CHECK OPTION으로 VIEW생성시, 해당WHERE절 조건만족못하는 것은 INSERT INTO VIEW로 대입 불가능
8. view1를 이용해 view2를 만든 상태에서
   (1) view1에 local옵션이 없고 & view2 local옵션만 있다면 ==> view2옵션만 체크한다.
   (2) view1에 local옵션이 있고 & view2 local옵션있다면     ==> view1, view2 둘다 체크한다. ( 각 view가 local있다면, 다 체크 )
   (3) view1에 local옵션이 없고 & view2 CASCADED 옵션이 있다면 ==> view1, view2 둘다체크한다. (마지막 view가 cascaded옵션있다면, 다 체크)

DROP TABLE 옵션 :  CASCADE vs CASCADED : VIEW를 포함한 VIEW에서 2개 모두 WHERE절 조건 체크 옵션

DROP

-- DROP TABLE

-- 1) videos테이블에서 users를 참조하고 있는 상황이므로 바로 삭제가 안된다.
DROP TABLE public.users;

-- 2) 강제로 삭제하는 방법은 뒤에 CASCADE를 붙혀 -> 다른테이블과의 관계를 끊는 것이다.
DROP TABLE public.users CASCADE;

-- 3) 관계는 끊어져도 기존에 연결된 값들은 유지되어있다.
--    videos 테이블의 정보를 보면, CONSTRAINT fkey정보가 업어져있다.
SELECT * FROM videos;


-- 4) 만약, videos테이블을 먼저 DROP한다면, uses테이블도 바로 DROP 될 것이다.
DROP TABLE public.videos;

-- 5) 없는 테이블을 삭제하는 오류를 줄이고 싶다면 DROP TABLE <--> 테이블명 사이에 IF EXISTS 를 준다.
--     테이블을 생성하기 전에 적어줘도 좋은 팁이 된다.
DROP TABLE IF EXISTS public.videos;

-- 6) DROP TABLE/DATABASE/SEQUENCE/VIEW 등이 다 DROP이 가능하다.

VIEW

-- VIEW & INSERT VIEW without groupby & WITH LOCAL/CASCADE CHECK OPTION

-- 1) view없이 고객들이 소비한 전체 돈 보기 by GROUP BY 
--     customers별 목록이 1 row로 나타나도록 id로 groupby + 기준테이블 칼럼들은 막 select +  집계테이블은 칼럼없이 sum()으로 1 row로 집계했다.
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id

-- 2) 매번 groupby를 할게 아니라, VIEW를 이용해 해당 결과를 저장하자.
--    CREATE VIEW + view이름 + AS + 저장할 쿼리문 형태이다.
CREATE VIEW total_revenue_per_customer AS
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id

-- 3) 마치 테이블처럼 SELECT할 수 있다.
SELECT * FROM total_revenue_per_customer

-- 4) DROP VIEW view이름으로 삭제할 수 있다.
DROP VIEW total_revenue_per_customer

-- 5) 다시 view를 만드는데, groupby 의 기준이된 customers.id도 같이 SELECT한 view를 만들자
--    새로운 테이블로 보는데, id칼럼이 없기 때문...?
--    이 아니라,, 새로운 구매정보를 늘릴 때 view는 어떻게 변하는지 보기 위해서다.
CREATE VIEW total_revenue_per_customer AS
SELECT customers.id, customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id

SELECT * FROM total_revenue_per_customer;

-- 6) VIEW의 결과에 없는 customers 중에 한명이 item을 구매하도록 해보자.
--    구매정보를 늘이려면 purchases테이블에 추가해야한다.
SELECT * FROM total_revenue_per_customer;    --1,3,4,5는 구매했었음.
SELECT * FROM customers;            --남은건 2번 고객
SELECT * FROM items;                --아이템은 아무거나.. 여기서 6번 아이템


SELECT * FROM purchases;             -- 구매번호는 10번까지 차있구나.

INSERT INTO purchases                -- 구매번호 11, item_id 6번, customer_id 2번
VALUES (11, 6, 2);


-- 7) 기존에 저장한 VIEW를 다시 보자.
--    customers.id 2번이 추가되었다! / 만약 기존고객이 구매정보를 늘렸으면, sum이 증가할 것이다!
--    VIEW는 결과가 아니라 쿼리문 자체를 저장해서 updatable한 테이블을 생성해주는 것
SELECT * FROM total_revenue_per_customer;


-- 8) VIEW로 저장한 쿼리문에는 WHERE문도 달 수 있다.
SELECT * FROM total_revenue_per_customer WHERE sum > 150;

-- 9) VIEW에 WHERE문을 단 것 역시, VIEW로 만들 수 있다.
CREATE VIEW awesome_customer AS
SELECT * FROM total_revenue_per_customer WHERE sum > 150;

SELECT * FROM awesome_customer;

-- 10) VIEW를 table로 취급하여, ORDER BY 한 것도 가능하다.
SELECT * FROM total_revenue_per_customer WHERE sum > 100
ORDER BY sum DESC;



-- INSERT INTO VIEW를 통해 VIEW에 값 대입하기는 group by 절이 포함된 VIEW는 사용하지 못한다.
-- 위에서 만든  total_revenue_per_customer 같은 것들은 INSERT INTO에 view를 사용하지 못한다.

-- 1) group by가 없는 VIEW를 만들자.
CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price > 100;

SELECT * FROM expensive_items

-- 2) 여기에 row를 대입해보자. ( 만약, SEQUENCE를 사용한 테이블이라면 해당값 입력x)
--    VIEW를 만든 테이블(items)에 값이 자동으로 삽입된다!
--     만약 일부칼럼만 가져온 VIEW라면?;; 
INSERT INTO expensive_items(id, name, price)
VALUES (9, 'DSLR',400.00);

SELECT * FROM expensive_items
SELECT * FROM items;            -- items테이블에도 추가되어있다.

-- 3) 만약, VIEW에 포함된 WHERE 조건절을 만족하지 않는 값을 추가한다면?
--     VIEW의 WHERE에는 100달러 이상 아이템만 가져오도록했는데, 5달러짜리를 추가해보자.
INSERT INTO expensive_items(id, name, price)
VALUES (10, 'DSLR', 5.00);

SELECT * FROM expensive_items;    --결과적으로, items테이블에는 추가되지만, VIEW에서는 안보이게 된다. 왜냐 살아있는 쿼리문이 저장되어있는 테이블이므로
SELECT * FROM items;    

-- 4) 그렇다면, INSERT INTO VIEW 를 이용해서, VIEW에 달린 WHERE 조건을 만족하는 값만 입력되도록 하려면?
--    PostgreSQL에만 있는 코드인 WITH LOCAL CHECK OPTION을 VIEW에 추가해줘야한다.
--    WITH LOCAL CHECK OPTION은 데이터를 insert나 update시 where조건절을 항상 체크한다.
DROP VIEW IF EXISTS expensive_items;

CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price > 100
WITH LOCAL CHECK OPTION ;

INSERT INTO expensive_items(id, name, price)
VALUES (11, 'low camera', 5.00);        -- view의 조건을 위반했다고 뜬다.

INSERT INTO expensive_items(id, name, price)
VALUES (11, 'low camera', 101.00);

-- 5) 이번에는 WITH LOCAL CHECK OPTION 없이 만든 expensive_view(100달러이상)에 
--    FROM expensive_view  + WITH LOCAL CHECK OPTION 으로
--    10000달러는 이하인 non_luxury_items VIEW를 
DROP VIEW IF EXISTS expensive_items;

CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price >= 100;

CREATE VIEW non_luxury_items AS
SELECT * FROM expensive_items WHERE price < 10000
WITH LOCAL CHECK OPTION;

-- 6) non_luxury_items VIEW 에다가 2달러짜리를 INSERT INTO 를 해보자.
--    LOCAL CHECK OPTION은 현재하고 있는 WHERE에 대해서만 체크를 한다고한다.

INSERT INTO non_luxury_items(id, name, price)
VALUES (12, 'Pencil', 2.00);
--    결과적으로 2달러도 입력이 된다.
--    직접적으로 달리지 않은 expensive_items에 대한 체크를 하지 않는다.
--    즉, non_luxury_items에 대한 check만 시행한다.


-- 7) LOCAL 이 아니라 종속관계까지 다 체크해주는 CASCADED CHECK 를 맨 마지막 VIEW에 달면,
--    내부의 VIEW(테이블)의 WHERE절까지 체크해준다.
DROP VIEW IF EXISTS non_luxury_items;

CREATE VIEW non_luxury_items AS
SELECT * FROM expensive_items WHERE price < 10000
WITH CASCADED CHECK OPTION;

INSERT INTO non_luxury_items(id, name, price)
VALUES (13, 'PencilTwo', 90.00); -- 내부에 있는 VIEW인 expensive_item에 대한 WHERE절에 위반되고 있다고 경고창이 뜬다.

총정리

1. SEQUENCE 만들기 : CREATE SEQUENCE 시퀀스명 (START n); 
2. 해당테이블의 해당칼럼 default값을 SEQUENCE로 하나씩 꺼내기 : ALTER TABLE + ALTER COLUMN + SET DEFAULT nextval('시퀀스명');
3. SEQUENCE를 해당테이블.해당칼럼에 종속시켜 같이 사라지게 하기 : ALTER SEQUENCE 시퀀스명 OWNED BY 테이블명.칼럼명;
4. 이제 INSERT INTO 테이블시 SEQUENCE칼럼빼고 값 입력하기

INDEX 정리

1. INDEX는 만들기 : CREATE INDEX 인덱스명 ON 테이블명(칼럼명);
2. 자주 검색조건에 들어가는 문자열을 WHERE로 서치해도 속도가 빨라진다. 내부 binary tree파일 생성

SEQUENCE

-- PostgreSQL에서는 autoincrement가 없다. 대신 우리는 SEQUENCE를 생성해야한다.
SELECT * FROM users;

-- users테이블의 (id, name)에서 id는 자동으로 증가하고 name만 넣어도 되도록 해보자.
-- 1) 일단은 name만 입력시키는 것은 안된다.( id가 primary key로 not null인데 null이 들어갔다고 나옴)
INSERT INTO public.users(name)
VALUES ('chojaeseong')

-- 2) 먼저, SEQUENCE 생성하기 [  CREATE SEQUENCE table명_적용할칼럼명_seq ]
--    우리는 이미 3명이 차있기 때문에, default로 SEQUENCE 생성시 1로 시작해서 오류가 날것이다.
--    [  CREATE SEQUENCE table명_적용할칼럼명_seq  START 4; ]로 시작번호도 알려주자.
CREATE SEQUENCE users_id_seq START 4;


-- 3) ALTER TABLE로 users테이블의 id칼럼의 default value를 SEQUENCE로 SET해주기 
--    만약, SET DEFAULT 9 으로 주면,, 칼럼의 모든 값이 9가 되어버린다.
--    9가 아니라 nextval(' seq name ')이라는 pgSQL의 함수로 SEQUENCE에서 하나씩 꺼내어 대입하는 방식이다.
ALTER TABLE public.users
ALTER COLUMN id
SET DEFAULT nextval('users_id_seq');


SELECT * FROM users;

-- 4) 이제 SEQUENCE를 해당 칼럼에 종속시켜 다른데서 안쓰이고, 지울때 같이 지워지도록 설정해준다.
ALTER SEQUENCE users_id_seq OWNED BY public.users.id;


-- 5) SEQUENCE가 적용된 users테이블의 id값을 주지말고, 다시 name만 입력해보자.
--    id칼럼에 autoincrement같은 SEQUENCE가 적용되면, 빼고 INSERT 해줘도 자동으로 증가한다.
INSERT INTO public.users(name)
VALUES ('chojaeseong')

SELECT * FROM users;

INDEX

-- INDEX 
-- 인덱스는 WHERE 서치를 빠르게 해주는데 유용하다.
SELECT * FROM public.users;

-- 1) 먼저, id가 아닌 name으로 사람의 검색해보고 걸리는 시간을 우측하단에서 확인해보자.
--    만약 데이터가 엄청 많다면, 많은 시간이 걸릴 것이다. 왜냐하면 normal column이기 때문!
SELECT * FROM public.users WHERE name = 'chojaeseong'; -- 12~15msec

-- 2) 인덱스는 자주검색하는 문자열(name 등)에 내부적으로 orderd된 index를 주어 직접 문자열 검색안하고도 빠르게 서치가 가능하게 한다.
--    하지만, 인덱스는 공짜가 아니라서, binary tree를 디스크에 저장해야한다.
--    그러므로, 서칭용 칼럼이 아니여서 INSERT나 UPDATE하는 칼럼에는 적용시키면, 느려진다.
--    즉, 자주 검색하는 칼럼에 index를 지정해주고 내부적으로는 파일이 생성될 뿐.
--     primary key는 특별한 종류의 index이다. 하지만, id로 검색하진 않는다.
--    검색을 자주하는 name칼럼을 인덱스로 지정해보자.
--      [CREATE INDEX 인덱스명(테이블명_칼럼명_index) ON 테이블명(칼럼명);]
CREATE INDEX users_name_index ON public.users(name);

-- 3) 다시 한번 name으로 사람을 검색해보고 시간을 측정해보자.
SELECT * FROM public.users WHERE name = 'chojaeseong'; -- 11 msec까지 나온다.


-- 4) 만약, 2개의 칼럼을 동시에 필터링 하는 작업을 자주한다면, 멀티칼럼 인덱스를 생성할 수 있다.
--     마지막 ON 테이블명(칼럼1, 칼럼2) 형식으로 지정해주면 된다. 그러나 필수는 아니다.
-- 예시)    CREATE INDEX index_name On pulbic.movies(id, user_id) 

-- 5) 다양한 UPDATE로 인해서, index가 꼬였다면, REINDEX TABLE 인덱스명;을 통해 해준다.

정리해보기

• pgSQL에서는 테이블생성시 public. 등의 스키마를 테이블명 앞에 붙힌다( 생략시 default - public. 자동)
  
  
• 2번째 테이블 생성부터는, 자신이 필요한 id(PRIMARY KEY)와 칼럼을 입력후 -> 참조 테이블을 고려한다.
  
  1) 참조테이블_id(칼럼명)  +  참조테이블의 PRIMARY KEY와 같은 datatype  +  REFERENCES  +  스키마.참조테이블명 
  2) 이렇게 지정해주면 참조테이블의 PRIMARY KEY는 2번째 테이블의 FOREIGN KEY가 되어버린다
  
  
• 1번째 테이블을 먼저 입력한 뒤, 참조테이블인 2번째 테이블에 데이터 입력한다.
  
  1) 이 때, 1번째 테이블의 PRIMARY KEY = 2번째 테이블의 FOREIGN KEY에 없는 값은 입력되지 않는다.
  
  
• 
  

CREATE TABLE

-- CREATE TABLE --
-- 만약 default schema인 public을 사용할 것이라면, public.은 생략해도 된다.

-- users테이블을 맨 처음 만들기.
CREATE TABLE public.users (
id integer PRIMARY KEY,  --사용자가 많다면 *bigint를 사용하면 된다. datatype다음에 PRIMARY KEY를 지정해주면 된다.
name character varying(100) NOT NULL, --*pgSQL에서는 varchar(255)가 아니라 character varying(255)로 사용한다.
)


-- PRIMARY KEY 지정은, 다 칼럼들 작성하고 난 다음에, CONSTRAINT로 지정해줄 수 있다.
-- CONSTRAINT + constraint's name + PRIMARY KEY + (칼럼명) 
-- ()안에는 여러개의 primary key를 지정해 줄 수 도 있다.
CREATE TABLE public.users (
id integer,
name character varying(100) NOT NULL, 
CONSTRAINT users_id_pkey PRIMARY KEY (id)
)


-- 그러나 *** 추천 ***은 칼럼 만들때 지정해주는 것이다.
CREATE TABLE public.users (
id integer PRIMARY KEY, 
name character varying(100) NOT NULL
)

SELECT * FROM users





-- videos 테이블 만들기
-- 1) videos에 필요한 칼럼들 먼저 넣기
CREATE TABLE public.videos (
id integer PRIMARY KEY,
name character varying(255) NOT NULL
)

-- 2) *** users 테이블과 연관관계를 고려하여, 어떤사람이 업로드 했는지 id가 필요함.
--    *** id와 users_id가 상관관계가 있으니 라인을 비슷한데 두자.
--     (1) user_id는 users 테이블과 같은 datatype을 가져야할 것이다.
CREATE TABLE public.videos (
id integer PRIMARY KEY,
user_id integer,
name character varying(255) NOT NULL
)
--     (2) user_id는 users 테이블의 id만 들어갈 수 있어야한다.******
--          users 테이블을 참조값만 들어가는 테이블임을 < REFRENCES 스키마.테이블 >로 명시해준다.
--        REFERENCES만 정해주면, ***참조테이블의 primay key가 자동적으로 체크된다.
CREATE TABLE public.videos (
id integer PRIMARY KEY,
user_id integer REFERENCES public.users,
name character varying(255) NOT NULL
)

SELECT * FROM videos

INSERT INTO

-- INSERT INTO --
-- 1) user table에 넣기 *** video 테이블은 users 테이블을 참조하고 있기 때문에 값을 바로 넣을 수 없다 ****
--    테이블명다음에 블라켓()에 칼럼리스트를 적어줘도 되지만, 생략해도 된다.
INSERT INTO public.users(id,name)
VALUES (1, 'marysmith');

SELECT * FROM public.users


INSERT INTO public.users
VALUES (2, 'rolfsmith');

SELECT * FROM public.users

INSERT INTO public.users
VALUES (3, 'josesalvatierra');


-- 2) videos 테이블은, user_id칼럼이 users테이블의 primaykey를 참조하고 있기 때문에,
--     uses테이블을 먼저 채워넣고 ----> 거기에 있는 값만 INSERT INTO 할 수 있다.
--    참조하는 순간 uses테이블의 primary key는 **자동으로 videos테이블의 foreign key가 된다.***
SELECT * FROM public.videos;

--    (1) 만약 users테이블에 없는 값을 입력시킨다면, 에러가 난다.
INSERT INTO public.videos
VALUES (1, 10, 'Test Video');

--     (2) users 테이블의 primary key = videos테이블이 참조해서  foreign key가 된다.
INSERT INTO public.videos
VALUES (1, 2, 'Test Video');

SELECT * FROM public.videos;




-- 3) 이제 2개 테이블을 조인시켜보자.
SELECT * FROM public.videos
INNER JOIN public.users ON public.users.id = public.videos.user_id;




-- 4) users테이블에서 primary key(***자동적으로 NOT NULL적용***)을 
--     autoincrementing 로 적용시켜서 -> name만 입력하면 id가 자동으로 올라가도록 해보자.

* 정리해보기

• JOIN한 상태에서 ~별  ~집계를 할때, 기준테이블의 id로 GROUP BY 하되, 기준테이블의 칼럼들은 SELECT할 수 있으나, 집계시에도 COUNT()는 집계테이블의 id로 COUNT()하되, 집계되는 테이블은 1 row로 접혀서 숫자계산되기 때문에 SELECT문에 칼럼명을 못넣는다.
• 만약, 집계테이블의 칼럼을 groupby의 기준에 넣는다면, 집계테이블 칼럼을 SELECT할 수 있다. 그러나 기준테이블은 안해두 가능!!

JOIN 3개 테이블 해보기

SELECT * FROM purchases;

SELECT * FROM items;

SELECT * FROM customers;


-- 1) item들 중에  구매한 items 목록을 원하는 경우
SELECT * FROM items
JOIN purchases ON items.id = purchases.id;


-- left조인을 쓴다면, 구매하지 않은 아이템도 블랭크로 붙는다.
SELECT * FROM items
LEFT JOIN purchases ON items.id = purchases.id;


-- 2) 이제 어느 고객이 어느 아이템을 샀는지 join해보자.
SELECT * FROM customers
JOIN purchases ON customers.id = purchases.customer_id;

--left join
SELECT * FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id;

-- 고객 중에 구매를 한 고객 join데이터 중 이름만 select하기
SELECT customers.first_name, customers.last_name FROM customers
JOIN purchases ON customers.id = purchases.customer_id;



-- 3) ***3개 테이블 한꺼번에 join해보기***
---- (1) items중에 구매한 것들만 item만 나오도록 inner join한다.
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id

---- (2) 여기서 보이는 customer_id로 고객들을 연결할 수 있을 것이다. *** JOIN한것에 바로 JOIN을 해버린다.
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id


---- (3) 필요한 칼럼만 챙겨보자.
SELECT customers.first_name, customers.last_name, items.name, items.price FROM items
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id

GROUP BY

-- GROUP BY --
-- JOIN의 결과는 duplicated된 row들로 나열되서 나타나니 GROUP BY <사람테이블의 id>를 활용해야한다.

SELECT * FROM customers;

-- ***left join***의 결과 ***모든*** 고객들 중에, 구매한 고객들 + 구매안한 고객들의 정보들이 다 나온다
-- left join을 하는 이유는 group by 로 count할 때, *** 구매안한사람도 0으로 count하기위해 ***
-- 하지만, 나는 구매info(item_id) + 연결키(customer_id)가 중요한게 아니라, 얼마나 많이 구매했는지 알고 싶은 상황이다.
-- 각 구매info가 따로따로 row로 나뉘어서 나오므로 이 때 필요한 것이 groupby이다.
SELECT * FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id;


-- 1) 이름정보와, purchases테이블에서는 ***primary key인 id만 가져온다. for COUNT ***
--    하지만, 여기까지도 row가 나뉘어져있다.
SELECT customers.first_name, customers.last_name, purchases.id FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id;

-- 2) 이제 count하고 싶은 칼럼인 puchases의 primary key id칼럼에 count함수를 넣어준다.
--    이대로는 에러가 난다. 그래서 필요한 것이 GROUP BY 이다.
--    *** 이때, 보이지 않더라도 LEFT테이블인 customers의 identity & primary key인 id칼럼으로 groupby 한다***
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id;




-- 3) ***SUM() - GROUP BY를 이용하여, 
--    아이템들 중 구매된 아이템만* 골라내는 INNER JOIN을 한 뒤, 
--     구매된 아이템 중 고객들customers들을 다시 INNER JOIN시켜붙혀보자.
SELECT * FROM items;

SELECT * FROM items 
INNER JOIN purchases ON items.id = purchases.item_id;

SELECT * FROM items 
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id;

-- 4) 여기에 *** 고객별 의 id로 groupby -> 집계함수 SUM()*** 시켜보자.
--    만약 GROUP BY 만 쓸때, *로 모든 칼럼들에 접근한 상태라면, 데이터 접근 중이므로 GROUP BY로 합칠 수가 없다.
--      필요칼럼선택(GROUP BY 기준 테이블안의) + (GROUP BY기준아닌 테이블의 칼럼을 합칠)집계함수를 사용해야한다.
--    my) customers의 id로 GROUP BY를 한다면, customers테이블은 칼럼 정보 살아 있어서 가져올 수 있다.
-- *********GROUP BY기준이 아닌 테이블의 칼럼들은 개별접근x COUNT(), SUM() 등으로 접혀들어가야한다.
SELECT * FROM items 
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id;

SELECT customers.first_name, customers.last_name, SUM(items.price) FROM items 
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id;

-- 5) 숙제 = 모든 고객이 구매한 가격의 합 ( customers 테이블 연결없이 해봤다 )
--    GROUP BY 의 기준을 items.id로 잡았다면 items테이블 칼럼은 접근가능 -> purchases테이블은 접히니까, 집계함수
SELECT items.name, SUM(items.price) FROM items 
INNER JOIN purchases ON items.id = purchases.item_id
GROUP BY items.id;

SELECT SUM(items.price) FROM items 
INNER JOIN purchases ON items.id = purchases.item_id

-- 5-정답)
-- 1) 전체 고객들이 얼마나 많은 돈을 소비했는지 알아보기

--     (1) 구매 정보 테이블 가져오기
SELECT * FROM purchases;

--    (2) 구매정보에 items정보 붙히기 innerjoin    
SELECT * FROM purchases
INNER JOIN items ON purchases.item_id = items.id;

--     (3) SUM()만 해주기
SELECT SUM(items.price) FROM purchases
INNER JOIN items ON purchases.item_id = items.id;

ORDER BY

-- Order by --

-- 이전에 했던대로, *각각의 고객들이 얼마나 많은 *돈을 소비했는지 다시 찾아보자
-- 1) 3개 테이블 다 조인하기 
SELECT * FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id;

-- 2) 필요칼럼 셀렉트하기
SELECT customers.first_name, customers.last_name, items.price FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id;

-- 3) 보이진 않지만, 고객의 primary key인 id칼럼으로 groupby -> SUM()하기
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id;

-- 4) price순으로 정렬하기 ( alis " 띄워쓰기alias시 따옴표 필수 "  -> ORDER BY에 alias적어주기)
SELECT customers.first_name, customers.last_name, SUM(items.price) AS "total_spent" FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id
ORDER BY "total_spent";

SELECT customers.first_name, customers.last_name, SUM(items.price) AS "total_spent" FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id
ORDER BY "total_spent" DESC;

-- 5) TOP 1만 표시해주기
SELECT customers.first_name, customers.last_name, SUM(items.price) AS "total_spent" FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id
ORDER BY "total_spent" DESC
LIMIT 1;

MSSQL 의 sp_help(alt+F1)과 같은 기능은 존재하지 않는다.

SELECT *
FROM information_schema.columns
WHERE table_schema = 'your_schema'; --public
AND table_name = 'your_table'; --customers, purchases, items

위와 같은 구분으로 실행할 순 있다.

join

join은 python에서 set과 마찬가지다.
유니크한 요소들이 순서가 없다.
그러나, 이들은 교집합/ 합집합 / 여집합 계산이 가능하다는 장점이 있다.
SELECT * FROM 테이블1의 ON조건 대해서 INNER JOIN 테이블2의 ON조건을 테이블1에서부터 하나씩 검사하면서 교집합 있는 것들을 붙혀서 row단위로 나타낸다.

1. INNER JOIN = 교집합
   1) SELECT * FROM 테이블1(Customers)로 다 보이게 할 건데,
   2) INNER JOIN 테이블2(Orders) 을 붙힐건데,
   
   3) ON 테이블1.id = 테이블2.id 로 교집합을 row를 붙힌다. 나는 테이블1의 ON인 id 1에 대해서 테이블2가 걸리는 것들을 검사해서 하나씩 붙힌다고 생각했다. 1-jose에 대해서, orders테이블에는 customer_id가 1인데 3개가 있으니 3개가 연달아 붙을 것이다.
   
   
   
   왼쪽테이블의 2는 걸리는게 없다.
   왼쪽테이블의 3-anna와 걸리는게 1개 있으니 붙힌다

2. LEFT JOIN역시 인기가 많은 JOIN으로서,
   LEFT에 있는 테이블1은 모두 살리면서, 교집합을 검사하는데, 왼쪽테이블1으로 검사시 교집합이 없어도 BLANK(MSSQL : NULL)로 나타내면서 테이블1을 다 살린다.
   1) 테이블1을 기준으로 검사하는데 걸리는게 있으면 나타낸다.
   
   
   

   2) 만약 걸리는게 없어도, 테이블1은 살리면서 테이블2는 NULL로 나타낸다.
   
   

3. RIGHT JOIN은 LEFT JOIN과 반대로, 오른쪽에서 검사를 시작하면서, 교집합이 없어도 오른쪽은 살리면 된다
   

4. FULL JOIN은 교집합 + 여집합까지 다나타내는 것이다. LEFT JOIN과 RIGHT JOIN을 모두 생각해야한다. 왼1 -> 오1 -> 왼2 -> 오2 -> 순으로 하나씩 교집합과 여집합을 챙겨준다.
   1) 일단 나는 왼쪽테이블1에 대해 교집합을 찾고, 오른쪽테이블1은 교집합으로 물려갔으니 생각안한다.

   2) 왼쪽테이블2에 대해서는 교집합이 없으니 여집합으로서 나타낸다.

   3) 오른쪽테이블2에 대해서도 교집합이 없으니 여집합으로 챙긴다.

   4) 왼3에서는 교집합이 있으니 교집합으로 챙긴다.
   

   5) 오3은 이미 왼1로서 교집합으로 잡혀갔다.

   6) 왼4는 여집합으로서 챙긴다.
   

   7) 오4는 이미 교집합으로 잡혀갔다.

join examples

SELECT * FROM purchases;

SELECT * FROM items;

SELECT * FROM customers;

-- 1) item들 중에 구매한 items 목록을 원하는 경우  
SELECT * FROM items  
JOIN purchases ON items.id = purchases.id;

-- left조인을 쓴다면, 구매하지 않은 아이템도 블랭크로 붙는다.  
SELECT * FROM items  
LEFT JOIN purchases ON items.id = purchases.id;

-- 2) 이제 어느 고객이 어느 아이템을 샀는지 join해보자.  
SELECT * FROM customers  
JOIN purchases ON customers.id = purchases.customer_id;

--left join  
SELECT * FROM customers  
LEFT JOIN purchases ON customers.id = purchases.customer_id;

-- 고객 중에 구매를 한 고객 join데이터 중 이름만 select하기  
SELECT customers.first\_name, customers.last_name FROM customers  
JOIN purchases ON customers.id = purchases.customer_id;

-- 3) ***3개 테이블 한꺼번에 join해보기***  
---- (1) items중에 구매한 것들만 item만 나오도록 inner join한다.  
SELECT * FROM items  
INNER JOIN purchases ON items.id = purchases.item_id

---- (2) 여기서 보이는 customer\_id로 고객들을 연결할 수 있을 것이다. *** JOIN한것에 바로 JOIN을 해버린다.  
SELECT * FROM items  
INNER JOIN purchases ON items.id = purchases.item_id  
INNER JOIN customers ON purchases.customer_id = customers.id

---- (3) 필요한 칼럼만 챙겨보자.  
SELECT customers.first_name, customers.last_name, items.name, items.price FROM items  
INNER JOIN purchases ON items.id = purchases.item_id  
INNER JOIN customers ON purchases.customer\_id = customers.id

UPDATE

1. UPDATE (테이블명) SET (칼럼) = 대입값 WHERE (특정칼럼 조건) 의 형식이다.
   DML(S, U, I, D) 중 하나이며, U,I,D는 INSERT INTO (테이블) VALUE ()처럼, 테이블명이 먼저나오지만 결국 칼럼을 조작한다.
   numeric(자리수,소수점자리수) 데이터로 integer로 표현할 수 없는 decimal point(소수점)을 표시한다
   .
   UPDATE로 칼럼값을 바꿀 때는, WHERE에 primary key인 id 같은 것으로 검색하자.
   cf) ALTER는 PRIMARY KEY를 주는 등의 테이블 자체 수정을 한다. UPDATE로 칼럼 값을 바꾼다.
   또한, UPDATE와 DELETE는 반드시 WHERE를 달아줘야 전체 데이터를 보호할 수 있다.

DELETE

• DML(S,U,I,D)정리
  SELECT 칼럼 FROM 테이블
  UPDATE 테이블 SET 칼럼=값 WHERE 조건
  INSERT INTO 테이블 VALUES ( , , )
  DELETE FROM 테이블 WHERE 조건

1. DELETE는 위험하기 때문에 SELECT 먼저 하고, DELETE로 수정하라고 배웠다.
   
   

2. 하지만 SQL은 똑똑하기 때문에 아래와 같이 알려준다.
   item 테이블의 id = 4는 purchases테이블에서 fk_purchase_item 칼럼으로 foreign key로 작용하고 있기 때문에 에러가 난다
   

3. 해당 테이블을 조회해보자.
   

4. 맨 처음 purchases테이블을 생성할 때, 아래와 같이 pk, fk를 넣어줬었다.
   
   ALTER TABLE "public"."purchases" ADD CONSTRAINT "fk_purchase_item" FOREIGN KEY ("item_id") **REFERENCES "public"."items" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;

5. 그럼 items 테이블의 id = 4를 지우려면, 먼저 그 것을 foreign key로 걸어둔 purchases 테이블의 id = 4를 삭제해줘야한다.
   
   

   
   

Wildcard for filtering unknowns : LIKE 'wildcard';

여기서는 필드 전체가 아니라 필드의 일부분에 근거해서 필터링 하는 방법을 배워보자.
WHERE 칼럼명 IN ('', '');의 경우에는 해당 값 전체가 있어야한다.
여기서는
WHERE 칼럼명 **LIKE '패턴'**;을 이용해 정규표현식처럼 값 일부로 추출할 수 있다.

1. customers 테이블의 last_name칼럼에는 t가 들어가는 사람이 2명 있다.
   LIKE 칼럼 '%';에서 %는 모든 문자가 줄지어 나타남(any character)을 의미.
   

2. LIKE 칼럼명 '___'; 언더스코어는 갯수만큼 문자열수를 가지는 값(single character)을 필터링한다
   _ 4개
   
   _ 5개
   

3. %를 응용하면,** 중간에 특정문자가 끼여있는 것을 추출**할 수 있다
   예를 들어 %t%
   

4. 또 응용하여, %t_로 모든문자로 시작하다가 t+1개의 문자로 끝나는 것을 추출할 수 있다.
   

DB에서 SQL쿼리 창 띄우고, query문으로 table생성 후 command해보기

1. 돋보기 모양을 클릭하면, Query창이 뜬다. 이것으로 db와 상호작용할 것이다.

2. 쿼리창에는 열기 / 복 / 붙 / 실행 / 실행 후 저장 / 현재 상호작용하는 db명 on 서버명 등이 메뉴로 있다.

3. 가운데 화살표를 내리면, 이전에 작성한 쿼리문 또한 활용할 수 있는 history가 있다.

간단한 DB파일 조작해보기

다운로드

sql-database.zip

select

1. 첨부된 파일에서 7_1, 2, 3 파일을 열고 각각을 실행해서 TABLE들을 생성하고-> 다시 열고 다시 실행을 반복하자. 3가지 테이블이 생성될 것이다.

2. SELECT문으로 customers; 테이블을 가져와보자.
   varchar(100)에서 var는 varying의 의미로 글자수를 제한할때 (숫자)와 같이 쓰는 것 같다.
   
   기본적으로 select 뒤에 칼럼을 가져올 때는, 테이블명.dot 칼럼 을 찍어주고 가져오는 것을 기 본적으로 해야하는 것 같다. F5키는 먹히나, alt+f1의 sp_help의 기능(mssql)은 없는 것 같다.
   

3. alias에 빈칸이 들어갈 경우 " " 반드시 쌍따옴표로 감싸주어야한다. 일반 글자는 쌍따옴표 없이 되는 것을 확인했다.
   또한, 그냥 F5를 누르면 전체 실행, 선택+F5시 해당코드만 실행되는 것 같다.
   

where

4. where 문에 들어가는 조건 칼럼도 테이블명. 을 찍어주자.
   값에 대해서는 ' ' 작은 따옴표(single quote mark)다. alias만 쌍따옴표를 쓰는 것 같다.
   sql문만 single equal =을 쓰는 것도 명심하자.
   WHERE문의 조건에는 AND 와 OR 도 쓸 수 있다.
   
   

limit ***

1. LIMIT row개수는 은 FROM 뒤에서 나타나는 row를 제한하는데,
   단독으로는 useful하지 않지만, 첫번째 손님, 첫번째 가입자 등을 볼 때 사용한다고 한다
   또한 페이지에 100개만 표시할 때도 사용된 다고 한다.

mssql 등의 TOP 1 * 과 차이점있는지는 모르겠음

PosgreSQL 다운 및 설치

1. https://www.postgresql.org/download/ 에 접속하기
   

2. Windwos 클릭 > Download installer 클릭 > x86-64비트 클릭
   

3. 인스톨러 다운받고 설치

• 이 때, 폴더위치나 PORT는 default로 지정하는 것을 추천한다. 마지막에 Builder는 설치해제한다
• C:\Program Files\PostgreSQL\9.5
• C:\Program Files\PostgreSQL\9.5\data
• Port : 5432
  
  

4. 이제 설치폴더 > bin 폴더에 가서 실행파일들을 보자.
   이 가운데 pgAdmin 을 실행시키면, sql서버와 유저에 관한 정보를 쉽게 다룰 수 있다.

• C:\Program Files\PostgreSQL\9.5\bin
  
  

pgAdmin3 - Login Role 만들고 DB 생성하기

1. SQL 서버를 먼저 연결해주자. 우리 컴퓨터가 하나의의 로컬 서버가 된 것이다.
   

2. 마지막의 Login Roles에 등록된 postgres는 절대 관리자에 관한 규칙이니,, 사용하지 않은 편이 좋다.
   Login Roles에서 우클릭을 통해 새로운 유저의룰을 추가해주자.
   

3. definition 탭에서는 비밀번호를 입력해주자.
   Connection Limit을 설정하면, 그 횟수만큼만 접속할 수 있다.
   

4. Role privileges 에서는 기본적으로 부모 역할을 상속받는다고 되어있다.
   이 때, create databases를 선택해서 DB를 생성할 수 있게 끔 해주자.
   

5. •SQL탭에서 보면, 방금 만든 룰이 적용되어있다. 비밀번호는 MD5라는 알고리즘에 의해 암호화 되어있다고 한다.
   

6. 우클릭으로 새 DB 생성
   
   

7. Definition탭에서,
   Encoding은 default로 UTF8로 지정해놓은 상태에서,
   Collation은 아래와 같이 Korea관련된 것으로 했다. 만약 변경이 필요하면 변경해야지..
   그 외의 것들은 default로 생성해보자.
   
   

8. 생성한 DB에서는 여러 기본 메뉴들이 있다. 여기서 가장 중요한 것은
   Schema > Public > Tables 일 것이다.