DROP * ( with CASCADE ) and VIEW* ( with LOCAL / CASECADE CHECK OPTION )

총 정리

DROP 정리

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절에 위반되고 있다고 경고창이 뜬다.