[DB10] INSERT, DELETE, UPDATE SQL 파헤치기
* 12.18 업데이트
해당 게시물은 한양대학교 컴퓨터소프트웨어학부 김상욱 교수님 데이터베이스시스템 온라인 강의를 듣고 정리한 자료입니다.
오류가 있다면 언제든 알려주세요!
● 해당 강의의 목표
1. 다른 SQL commands에 대해 배운다.
▪ INSERT
▪ DELETE
▪ UPDATE
▪ Views and indexes
◆ INSERT Statement
◆ table에 새로운 tuple을 넣는데 사용된다.
▪ table의 상태를 변경시킨다.
◆ Types
▪ 하나의 tuple을 집어넣는 방법
▪ 여러 tuple들의 set을 집어넣는 방법
◆ 하나의 tuple을 집어넣는 방법
▪ INSERT INTO <table name>
VALUES (<list of attributes values>);
▪ list of attributes values는 CREATE TABLE할 당시에 작성했던 attribute의 순서에 맞추어서 value들을 작성해야한다.
▪ 예시
꼭 순서 맞춰야한다.!
▪ 모든 attribute에 해당되는 value들을 집어 넣었는데 부분적으로도 삽입할 수 있다.
▪ INSERT INTO <table name>(<list of attributes>)
VALUES (<list of attributes values>);
▪ NULL 값이 허용되거나 DEFAULT 값이 존재하는 attribute value는 빠져도 된다. -> NULL 값이 채워지거나 DEFAULT 값이 채워져서 들어간다.
▪ 예시
◆ 여러 tuple들을 한번에 집어넣는 방법
▪ 여러 tuple들을 집어넣을 때는 query문을 통해서 retrieve한 후의 결과를 집어넣을 수 있다.
▪ INSERT INTO <table name>(<list of attributes>)<select statement>;
단, list of attributes에 있는 attribute 수와 select statement에 있는 attribute 수가 동일해야한다. 또한, 순서대로 대응되는 각각의 attribute의 type이 동일해야한다.
▪ WORKS_ON_INFO table은 up-to-date하지 않을 수 있다. 즉, 최신의 것이 항상 반영되지 않을 수도 있다.
- 이후 PROJECT, WORKS_ON, EMPLOYEE table에 변경이 있어도 WORKS_ON_INFO에 자동적으로 반영되지 않는다. DBMS는 WORKS_ON_INFO가 앞의 table들과 관련되어있음을 모르기 때문이다.
▪ 따라서 View 형태로 table을 정의하면 up-to-date한 information을 항상 유지할 수 있다.
◆ DELETE Statement
◆ table에서 tuple들을 지우는 statement이다.
▪ table의 instance를 modify하는 statement이다.
◆ WHERE clause를 사용해서 삭제할 tuple들을 선택한다.
◆ 한 번에 오직 하나의 table에서만 삭제가 가능하다.
◆ DELETE FROM <table name>
WHERE <condition>;
where 조건을 만족하는 tuple들을 table에서 지워라.
◆ WHERE clause가 없을 때
▪ 조건 없이 tuple들을 지워라.
▪ 즉, table에 들어있는 모든 tuple들이 지워진다.
◆ 예제
◆ UPDATE Statement
◆ 하나 이상의 선택된 tuple들의 attribute value를 바꾸는 statement이다.
▪ database(table)의 instance를 modify하는 statement이다.
◆ WHERE clause를 통해서 바꿀 tuple들을 select한다.
◆ 한 번에 오직 하나의 table에서만 변경 가능하다.
◆ SET clause를 통해 어떤 attribute 값을 어떤 attribute 값으로 바꿀지 명시에 줄 수 있다.
▪ 변경할 attribute value를 명시하고 어떤 값으로 바꿀지 명시한다.
◆ UPDATE <table name>
SET <list of 'attribute = value' pairs>
WHERE <condition>;
◆ WHERE clause가 없을 때
▪ 조건 없이 tuple들을 변경해라.
▪ 즉, table에 들어있는 모든 tuple들이 변경된다.
◆ 예시
▪ 새로운 Salary값은 예전 값에 계산된 값으로 대입된다.
◆ Views
◆ SQL에서 view의 개념
▪ 다른 table로부터 derived 된 single table -> 다른 table로부터 Select문을 통해 retrieve한 tuple들을 가지고 있는 하나의 table을 view라고 한다.
▪ virtual table이라고 한다. -> 기존에 있는 table로부터 tuple들을 가져와서 새로운 테이블에 저장하는 INSERT문은 가상 table이 아니다. 하지만 여기서는 물리적으로 저장하지 않고 그냥 가져올 table이라는 것을 명시해놓는 것뿐이다.
Q. 왜 할까?
A. view에 query가 던져지면 그때 정의되어 있는 select 문을 통해 view에 해당되는 table을 만들고 그때 요청된 query들을 처리한다.
◆ View의 특징
▪ Physical form으로 존재하지 않는다.
- 여러 table로부터 유도되는데 view를 정의할 때 유도되지 않고 view라는 table에 query가 던져지면 그때 생기는 table이다.
- 따라서 update operation가 되지 않는 경우가 발생할 수 있다. 뒤에서 설명!
▪ view에 대한 query는 일반 table에 비교해서 전혀 제한이 없다.
- query가 던져지면 base table로 부터 tuple들을 가져와서 생긴 table로 query가 처리되기 때문이다.
view를 정의할 때는 tuple들이 없어서 virtual table이라고 한다.
table 1개: single table
여러 table로부터 나중에 가져온다: derived◆ Creating Views
◆ CREATE VIEW statement
▪ CREATE VIEW <view name>
AS <select statement>
▪ <view name> table은 물리적으로 존재하지 않는다. 그 안에 tuple들이 존재하지 않는다.
▪ 누군가 query를 던지면 <select statement>를 통해서 tuple들을 가져온다. 그러면 그 table이 view에 해당되는 table이 될 것이고 query 처리가 가능하다.
▪ <view name>에 해당되는 attribute name을 따로 명시하지 않으면 select statement에 있는 attribute name들을 그대로 사용한다.
◆ 예시
view가 있고 나중에 view에 대해 query가 들어오면 select문을 수행해야한다는 정보가 DBMS에 등록된다. 아직 select statement는 수행되지 않은 상태이다.
▪ attribute name이 없는 aggregate function이 주어졌기 때문에 name을 명시적으로 주어야한다.
◆ Querying Views
◆ table에 query 던지는 것과 똑같다.
◆ 예시
▪ Retrieve the last name and first name of all employees who work on the ‘ProductX’ project
▪ WORKS_ON1 view 없이도 표현 가능하다. view가 훨씬 단순하다. view를 만들어 놓으면 많은 사람들이 select만을 사용해서 쉽게 사용할 수 있다.
◆ View 사용의 장점
▪ query를 명시함에 있어서 단순해진다.
▪ security, authorization mechanism에 사용될 수 있다.
- 원래 table을 감춰서 일반사용자들이 볼 수 있는 attribute을 제한할 수 있다.
▪ 항상 최신 정보를 가지고 있다. (Always up-to-date) INSERT문과 비교된다.
◆ View 사용의 단점
▪ performance issue가 있다.
- INSERT문으로 하나의 물리적 table을 만들었다면 바로 그 table에 접근하면 된다. 하지만 view로 표현하면 base table로부터 항상 새롭게 가져와야한다.
▪ update 면에서 제약이 있다. 자신의 tuple이 없기 때문에 원래의 table로 돌아가서 update를 해야하는데 가능하지 않는 경우가 있다.
◆ Dropping Views
◆ view가 더이상 필요하지 않을 때는 없앨 수 있다.
◆ DROP VIEW <view name>;
▪ view name이 어떤 statement query를 통해서 가져오는 거였는데 이제 DBMS야, 이 view 안 쓸거니까 기억하지마.
◆ 예시
◆ Updating Views
◆ View update의 문제점
▪ view를 update한다는 것은 view가 기반을 두고 있는 base table의 내용이 update되어야한다는 것을 의미한다.
- 일부의 view에 대해서만 update가 가능하다.
- 어떻게 update하는건지 모호한 상황에는 불가능하다.
▪ view에 대한 update는 database 분야에서 연구 대상이다.
◆ 예시
▪ Update the PNAME attribute of `John Smith’ from `ProductX’ to `ProductY’
이 statement를 주는 사람은 WORKS_ON1이 view인지 일반 table인지 모른다.
WORKS_ON1이 view이기 때문에 base table을 변경해야한다.
◆ 예시: 1번 접근
▪ Change the name of the ‘ProductX’ tuple in the PROJECT relation to ‘ProductY’
이렇게 바꾸면 다른 사람들도 모두 ProductX에서 ProductY를 일한 것으로 된다.
project 이름을 바꿔주는 query이다.
◆ 예시: 2번 접근
▪ Relate `John Smith’ to the `ProductY’ PROJECT tuple instead of the `ProductX’ PROJECT tuple
이 query는 관계를 바꿔주는 것이다. 자신이 일하는 project를 바꿔주는 것이다.
이러한 상황이 ambiguous하다고 처리하고 DBMS는 update을 하지 않는다.
◆ 예시: update이 불가능함.
▪ Total_sal는 각 employee의 salary를 더한 결과물이다. 이를 바꿀 수는 없다.
- 합을 가지고 있는 tuple 따로 존재하지 않는다.
◆ Summary
▪ Update이 되지 않는 경우도 존재한다.
▪ 가능성
1. View에서 base relation이 하나의 table이고 view가 가지는 attribute이 base table의 PK를 포함하고 있을 때
base table이 하나의 table + view가 가진 attribute이 그 base table의 primary key를 포함할 때 update가 가능하다.
2. View가 여러 table에서 join으로 정의되어 있으면 update가 일반적으로 되지 않는다.
view가 여러 table의 join으로 연결되면 update는 일반적으로 불가능하다.
3. grouping, aggregate function이 포함되어 view가 정의되어 있다면 update되지 않는다.
◆ Indexes
◆ query를 던졌을 때(특히 select query) 더 빠르게 결과를 보기 위한 meta-data이다.
▪ B-Trees, B+ Trees
◆ Indexing attributes: // index를 attribute에 build한다.
▪ table을 index하기 위해 사용되는 attribute
◆ select문의 WHERE clause에 indexing attributes가 존재한다면 그 query는 매우 빠르게 처리된다.
모든 attribute에 index를 걸어두면 어떻게 될까?
물론 모든 query에 대해 검색이 빨라진다. 다만, attribute이 100개가 있을 때 index가 100개 만들어진다. 그 index가 만들어지기 위한 space가 필요하고 하나의 tuple이 들어오면 모든 attribute에 대해서 만들어진 index에 반영이 되어야하기 때문에 100배의 시간이 걸린다.
◆ Creating Indexes
◆ CREATE INDEX <index name>
ON <table name>(<list of attributes>);
◆ <table name>의 <list of attributes>에 <index name>을 만들어줘.
◆ 예시
▪ CREATE INDEX LnameIndex
ON EMPLOYEE(Lname);
▪ 내부에 tuple들이 많을수록 index가 존재하면 더 큰 효과가 나타난다.
◆ index를 build할 때 attribute value들의 순서를 정할 수 있다.
▪ ASC가 default이다.
▪ DESC keyword를 통해서 내림차순을 만들 수 있다.
◆ 예시
▪ CREATE INDEX LnameIndex
ON EMPLOYEE(Lname DESC);
▪ Lname을 내림차순으로 정리
◆ Index는 여러 attribute에 대해서도 가능하다.
◆ 예시
▪ CREATE INDEX nameIndex
ON EMPLOYEE(Lname, Fname, Minit);
- 세 개의 value를 묶어서 하나의 value처럼 다뤄서 index를 구성한다.
◆ UNIQUE Keyword
◆ indexing attribute에 대해 키 제약 조건을 지정할 때 사용된다.
◆ 예시
▪ CREATE UNIQUE INDEX SsnIndex
ON EMPLOYEE (Ssn);
새로운 tuple이 들어오면 index에 반영을 해야한다. 어떤 tuple이 들어왔을 때 indexing 단계에서 똑같은 ssn 값을 갖는 tuple이 들어오지 않도록 한다.
정렬된 구조:
- 인덱스가 설정된 컬럼은 정렬된 상태로 관리됩니다.
- 예를 들어, CREATE INDEX nameIndex ON EMPLOYEE(Lname);를 생성하면 Lname 컬럼 값이 정렬된 상태로 별도의 인덱스 파일에 저장됩니다.
◆ CLUSTER Keyword
◆ index가 build된 attribute 값이 비슷한 tuple들을 물리적으로 비슷한 거리에 저장하는데 사용된다.
◆ query의 performance가 좋아진다. join할 때도 비슷하게 성능이 좋아진다.
◆ 예시
employee가 저장될 때 Dno가 비슷한 사람들끼리 같이 저장하라는 의미이다. department와 join할 때 department가 같은 사람들끼리 모여있으니 속도가 매우 빨라진다.
◆ Dropping Indexes
◆ index가 더이상 필요하지 않을 때 drop할 수 있다.
◆ DROP INDEX <index name>;
◆ 예시
▪ DROP INDEX DnoIndex; // tuple은 없어지지 않는다.
◆ Summary
1. Database update commands
▪ INSERT
▪ DELETE
▪ UPDATE
2. Views
3. Indexes