[데베시] 10강

10강 - SQL commands (INSERT, DELETE, UPDATE, View&Indexes)

 

INSERT Statement

- table에 new tupe을 추가 (table을 변경)

- 타입 : 하나의 튜플 추가, 여러 튜플 추가

 

- 하나의 튜플 추가 : INSERT INTO <table name> VALUES(<list of attributes values>);

  - attribute value list의 순서는 table의 attribute 순서에 맞춰서 넣어줘야 함.

EX) INSERT INTO EMPLOYEE VALUES ('Richard','K','Marini', '123456789', '1962-12-30', '98 Oak Foreast, Karty, TX', 'M', 37000,'987654321', 4);

 

- 일부의 attribute만 넣어줄 수 있다. (제외 가능한 attribute는 NULL 허용 or DEFAULT값이 설정된 attribute)

- INSERT INTO <table name>(<list of attributes>) VALUES(<list of attributes values>);

EX) INSERT INTO EMPLOYEE(Fname, Lname, Dno, Ssn) VALUES ('Richard','K','Marini', '123456789');

 

- 여러 튜플 추가 : INSERT INTO <table name>(<list of attributes>) <SELECT statement>;

EX)

INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week)

SELECT E.Lname, P.Pname, W.hours

FROM PROJECT P, WORKS_ON W, EMPLOYEE E

WHERE P.Pnumber=W.Pno AND W.Essn = E.Ssn;

- 주의할 점 : WORKS_ON_INFO는 항상 최신상태(up-to-date)를 유지하지 않을 수 있음. -> 자동 반영하기 위해선 view를 사용한다.

 

DELETE Statement

- table에서 tuples를 삭제(table 변경)

- WHERE clause를 사용해서 삭제할 tuple을 고름. WHERE절이 없으면 전체 tuple 삭제(table은 그대로)

- 오직 하나의 테이블에서만 사용 가능 (JOIN과 함께 사용 불가)

- DELETE FROM <table name> WHERE <condition>;

EX) DEELETE FROM EMPLOYEE WHERE Lname='Brown'; / DELETE FROM EMPLOYEE Ssn='123456789'; 등

 

UPDATE Statement

- 1개 이상의 선택된 tuple의 attribute value를 변경(table 변경)

- WHERE clause를 통해 변경할 tuple을 고름. WHERE절 없으면 전체 튜플 변경

- 오직 하나의 테이블에서만 사용 가능 (JOIN과 함께 사용 불가)

- SET clause를 통해 어떤 attribute를 어떤 value로 변경할 것인지 지정

- UPDATE <table name> SET <list of 'attribute = value' pairs> WHERE <condition>;

EX) UPDATE PROJECT SET Plocation='Bellaire', Dnum=5 WHERE Pnumber=10;

EX) UPDATE EMPLOYEE SET Salary = Salary*1.1 WHERE Dno=5;

 

Views

- View : 다른 table로부터 derived(파생된) single virtual table(단일 가상 테이블)

- View의 특징 : 물리적으로 저장 X(View에는 Tuple이 존재x) View 생성할 때 tuple을 가져오는 게 아니라 query 할 때 가져옴. -> update 불가, 대신 쿼리를 하는데는 아무 제약이 없음.

 

CREATE VIEW statement

- CREATE VIEW <view name> AS <select statement>

- <view name> table이 이 statement가 실행된 후에 물리적으로 존재하지는 않는다.

- 누군가가 해당 view에 query를 보내면 그 때 select statement로부터 tuple을 가져와서 가상 table을 만들어 tuple을 찾는다.

- 따로 지정하는 것이 아니라면 attribute name은 기존 그대로

EX1)

CREATE VIEW WORKS_ON1

AS SELECT Fname, Lname, Pname, Hours

     From EMPLOYEE, PROJECT, WORKS_ON

     WHERE Ssn=Essn AND Pno=Pnumber;

EX2)

CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal)

AS SELECT Dname, COUNT(*), SUM(Salary)

FROM DEPARMENT, EMPLOYEE

WHERE Dnumber=Dno

GROUP BY Dname;

-> 이 경우 명시적으로 attribute name을 입력해줘야 함

 

Querying Views

- table과 동일하게 query하면 됨.

EX1) SELECT Fname, Lname FROM WORKS_ON1 WHERE Pname='ProductX';

-> 해당 쿼리가 실행되는 순간 WORKS_ON1 View에서 tuple을 검색하고 가상의 table을 생성함.

만약 해당 WORKS_ON1 view가 없었더라면 이렇게 해줘야 함.

SELECT Fname, Lname

From EMPLOYEE, PROJECT, WORKS_ON

WHERE Ssn=Essn AND Pno=Pnumber AND Pname='ProductX';

-> View의 장점 : 특정 쿼리 명시를 간편하게 해줌. 보안과 권한 매커니즘에 사용할 수 있음(전체 Table은 감추고 일부만 보여주는 View를 볼 수 있음), 항상 up-to-date(최신 정보) 보장

-> View의 단점 : 성능 문제가 있음.(query 실행 시마다 원래 table에서 tuple을 가져와야 하므로 시간이 소모됨) update에 제약이 있음.

 

Dropping Views

- View를 제거

- DROP VIEW <view name>;

 

Updating Views (중요!)

- View 업데이트는 제약이 있음. view를 update한다는 것은 view가 아닌 view가 참조하는 base table을 update한다는 의미 -> but 일부 view에 대해서만 update가 가능. 항상 가능한 것이 아님! 불명확한(Ambiguous) 경우가 있음.

(update가 안되는 경우 1)

Q) John Smith라는 사람의 Pname을 ProductX에서 ProductY로 변경하라(Ambiguous Query)

만약 WORKS_ON1이 view가 아니라 table이라면 ->

A)

UPDATE WORKS_ON1

SET Pname='ProductY'

WHERE Lname='Smith' AND Fname='John' AND Pname='ProductX';

 

-> 하지만 만약 WORKS_ON1이 view라면 2가지 관점으로 해석할 수 있다.

 

1) Pname이 ProductX인 프로젝트의 Pname이 ProductY로 바뀌었다.

->

UPDATE PROJECT SET Pname='ProductY' WHERE Pname='ProductX';

 

2) 'John Smith'가 관계를 갖고 있던 프로젝트의 Pname이 ProductX에서 ProductY로 바뀌었다.

->

UPDATE WORKS_ON

SET Pno = (SELECT Pnumber FROM PROJECT WHERE Pname='ProductY')

WHERE Essn IN (SELECT Ssn FROM EMPLOYEE WHERE Lname='Smith' AND Fname='John')

AND Pno = (SELECT Pnumber FROM PROJECT WHERE Pname='ProductX');

 

====> 모호성 발생!

(update가 안되는 경우 2)

EX)

UPDATE DEPT_INFO SET Total_sal = 100000 WHERE Dname='Research';

-> 이 경우 Total_sal은 attribute가 아니라 aggregate function의 결과값일 뿐인데 이것을 변경하는 것은 원칙적으로 불가능하다.

 

-> view의 update limitation의 근본적인 문제 : VIEW는 물리적으로 tuple을 가지고 있는 table이 아니기 때문!(중요)

-> 결론 : view의 업데이트는 항상 성공적일 것이란 보장을 할 수 없다.

가능한 경우 : view의 base relation이 하나이고 그 view가 base relation의 primary key를 가지고 있는 경우

불가능한 경우 : multiple table이 join되어 하나의 view를 만든 경우 실패할 수 있음(성공할 수도 있음), View에 grouping & aggregate function을 사용한 경우 원천적으로 불가능

 

 

Indexes

- 빠른 검색을 위한 추가적인 meta-data : B+Trees

- Indexing attributes : 특정 attribute에 대해 index를 building한다.

- Indexing attribute가 select문의 where절에 있다면 빠른 query가 가능

- 단점 : 추가적인 공간을 차지한다, insert/update 할 때마다 index를 정렬해줘야 하므로 성능 문제가 있다. -> inset/update보다 select를 많이 사용하는 attribute에 대해서만 index를 설정해주는게 좋다.

 

Creating Indexes

- CREATE INDEX <index name> ON <table name>(<list of attributes>);

ex) CREATE INDEX LnameIndex ON EMPLOYEE(Lname);

- index의 순서를 정할 수 있다. 기본은 오름차순(ASC), DESC로 내림차순으로 변경 가능

ex) CREATE INDEX LnameIndex ON EMPLOYEE(Lname DESC);

- 여러 attributes를 하나의 Index로 만들 수 있다. 여러 attribute value를 묶어서 하나의 value처럼 indexing

ex) CREATE INDEX NameIndex ON EMPLOYEE(Lname, Fname, Minit);

- UNIQUE Keyword : attribute의 index에 동일한 value가 들어오는 것을 제한

ex) CREATE UNIQUE INDEX SsnIndex ON EMPLOYEE(Ssn);

- CLUSTER Keyword : Index에서 가까운 값들이 실제 저장될 때 물리적으로 가까운 곳에 저장되도록 함. (join이나 ranged condition이 있는 Query의 성능이 향상됨)

ex) CREATE INDEX DnoIndex ON EMPLOYEE(Dno) CLUSTER;

-> 만약 DEPARTMENT와 join을 한다면 빠르게 JOIN이 가능.

 

Dropping Indexes

- INDEX  제거

- DROP INDEX <index name>;