[손에 잡히는 10분 SQL - 벤 포터 지음, 박남혜 옮김] 책의 학습 후 정리자료입니다.
📍 뷰 이해하기
- 뷰는 가상 테이블이다.
- 데이터를 가진 테이블과는 달리, 뷰는 사용될 때, 동적으로 데이터를 가져오는 쿼리드들을 담고 있다.
📌 왜 뷰를 사용하는가?
- 뷰를 사용하는 이유는 아래와 같다.
- SQL 문을 재사용하기 위해서이다.
- 복잡한 SQL 작업을 단순화하려는 것이다.
- 근본적으로 쿼리 그 자체에 대한 상세 내용을 알지 않고도 작성된 쿼리를 쉽게 다시 사용할 수 있다.
- 완전한 테이블이 아니라 테이블의 일부만을 노출하기 위해서이다.
- 데이터를 보호하기 위해서이다. 사용자는 전체 테이블이 아니라 테이블의 특정 부분에만 접근할 수 있다.
- 데이터 형식을 변경하기 위해서이다. 뷰는 원래의 테이블과 다른 형식으로 데이터를 가져올 수 있다.
- 일반적으로 뷰는 생성한 후에 테이블과 같은 방식으로 사용된다.
- SELECT 작업을 할 수도 있고, 데이터를 필터링하거나 정렬할 수도 있으며 뷰를 다른 뷰나 테이블과 조인할 수도 있다.
- 약간의 제약이 있지만, 데이터를 추가하거나 업데이트할 수도 있다.
- 가장 중요한 것은 뷰는 뷰일 뿐이고, 데이터는 다른 곳에 있다는 점이다.
- 뷰 자체는 데이터를 저장하지 않고, 다른 테이블에서 데이터를 가져와 반환한다.
- 테이블에서 데이터가 추가되거나 변경되면, 뷰는 변경된 데이터를 반환한다.
❗️성능 문제
뷰가 데이터를 갖고 있지 않기 때문에 뷰를 사용할 때마다 쿼리가 실행된다.
여러 개의 조인과 필터가 있는 복잡한 뷰를 생성하거나,
여러 겹의 뷰를 생성한다면 성능은 급격히 저하된다.
뷰를 빈번하게 사용하는 프로그램이라면 배포하기 전에 반드시 테스트해보자.📌 뷰 규칙과 제한
- 뷰를 만들기 전에 몇 가지 알아두어야 할 제약 사항이 있다.
- 다음은 가장 자주 사용되는 규칙 몇 가지와 뷰 생성과 사용을 통제하기 위한 제약 사항이다.
- 테이블처럼 뷰는 고유한 이름을 가져야 한다.
- 생성할 수 있는 뷰의 수에는 제한이 없다.
- 뷰를 생성하기 위해 보안 권한을 가져야 한다. 접근 권한 수준은 데이터베이스 관리자가 관리한다.
- 뷰는 뷰를 포함할 수 있다. 즉, 뷰는 다른 뷰에서 데이터를 가져오는 쿼리를 사용하여 만들 수 있다.
- 많은 DBMS는 뷰 쿼리에서 ORDER BY 절의 사용을 금지한다.
- 일부 DBMS에서는 가져오는 모든 열에 이름 혹은 별칭을 부여해야 한다.
- 뷰는 인덱스를 사용할 수 없다. 또한 트리거 또는 그와 연관된 기본값을 사용할 수 없다.
- 일부 DBMS는 뷰를 읽기 전용 쿼리로 처리한다. 뷰에서 데이터를 가져올 수는 있지만, 그 테이블에 데이터를 쓸 수 없다는 의미이다.
- 일부 DBMS에서는 삽입 또는 업데이트한 데이터가 뷰의 범위를 벗어나는 경우는 삽입과 업데이트를 불허하는 뷰를 만들 수 있다.
📍 뷰 생성하기
- 뷰는
CREATE VIEW문을 사용해서 생성한다. - CREATE TABLE 처럼 CREATE VIEW도 존재하지 않는 뷰를 생성할 때만 사용할 수 있다.
✏️ 뷰 이름 바꾸기
뷰를 삭제하려면 DROP 문을 사용한다. 문법은 DROP VIEW 뷰이름; 이다.
뷰를 덮어쓰거나 또는 업데이트하려면, 뷰를 먼저 삭제한 후에 다시 생성해야 한다.📌 복잡한 조인을 단순화힉 위한 뷰 생성하기
- 뷰를 자주 사용하는 이유 중 하나는 복잡한 SQL을 숨기기 위해서이다.
- 복잡한 SQL은 보통 조인을 포함하고 있다.
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num- 제품을 주문한 적이 있는 모든 고객 리스트를 가져오기 위해 세 개의 테이블을 조인하여 ProductCustomers 라는 이름의 뷰를 생성한다.
- 'RGAN01' 제품을 주문한 고객 리스트를 가져오려면 다음과 같이 입력하면 된다.
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
- 뷰를 사용하면 복잡한 SQL 문을 매우 단순화할 수 있다.
- 뷰를 사용해 기본적인 SQL 문을 한번 작성한 후 필요할 대마다 재사용할 수 있다.
💡 재사용이 가능한 뷰 생성하기
특정 데이터에 종속되지 않는 뷰를 생성하는 것은 좋은 생각이다.
뷰의 범위를 확장하면 재사용이 가능하기 때문에 좀 더 유용하다.
또한, 여러개의 비슷한 뷰를 만들고 관리하지 않아도 된다.📌 가져온 데이터의 형식을 변경하기 위해 뷰 사용하기
- 뷰는 가져온 데이터의 형식을 바꾸는 데도 자주 사용된다.
- 아래는 판매처명(
vend_name)과 위치(vend_country)를 하나로 결합한 열로 가져온다.
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title
FROM Vendors
ORDER BY vend_name;- 이런 형식의 결과를 필요할 때마다 작성하는 대신 뷰를 생성하여 사용할 수 있다.
CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title
FROM VendorsSELECT *
FROM vendorLocations;
✏️ SELECT 제약 사항이 그대로 적용된다.
뷰를 생성하는 문법은 DBMS와 상관없이 상당히 일관된다고 언급한 바가 있다.
위 사실과는 별개로 여러 버전의 문장이 쓰이는 이유는 뷰가 단순히 SELECT 문을 감싼 것이기 때문이다.
각 DBMS에서 SELECT 문이 따라야 하는 모든 규칙과 제약을 그대로 적용받기 때문이다.📌 원하지 않는 데이터를 필터링하기 위해 뷰 사용하기
- 뷰는 WHERE 절과 함께 사용할 때도 유용하다.
🧷 예제 1
- 이메일 주소가 없는 고객을 필터링하는 CustomerEmailList 뷰를 정의해보자.
CREATE VIEW CustomerEmailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;- 생성한 CustomerEmailList 뷰는 아래와 같다.
SELECT *
FROM CustomerEmailList;
📌 계산 필드와 함께 뷰 사용하기
- 뷰는 계산 필드를 단순화할 때 특히 더 유용하다.
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
SELECT *
FROM OrderItemsExpanded;
📍 도전 과제
- 주문 내역이 있는 고객의 모든 열을 포함하도록
CustomerWihOrders라는 뷰를 작성하라. 원하는 고객을 거르기 위해Orders테이블과 조인할 수 있다. 그 후 데이터가 맞는지 확인하기 위해 SELECT 문을 작성한다.
CREATE VIEW CustomerWithOrders AS
SELECT Customers.cust_id,
Customers.cust_name,
Customers.cust_contact
FROM Customers, Orders
WHERE Customers.cust_id = Orders.cust_id;
SELECT *
FROM CustomerWithOrders;
- 다음 SQL 문은 무엇이 잘못되었는가?
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
ORDER BY order_num;- CREATE VIEW 문에서는 ORDER BY 절을 사용할 수 없다.
- 따라서 ORDER BY 절을 제거한다.
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
목적 있는 글쓰기