[데이터베이스] SQL 기초

#SQL 기초

1. SELECT
곱셈 등의 연산이 가능하다.

2. FROM
원하는 테이블을 가져옴, 콤마로 나열 시 자동으로 cartesian join이 적용된다. NATURAL JOIN 이나 JOIN USING을 사용할 수 있다.

3. WHERE
조건을 명시하는 부분, FROM으로부터 생성된 테이블에 조건을 적용한다.
OR, AND, NOT, openrands of the logical connectives, BETWEEN ... AND ...을 사용할 수 있다. 많은 조건이 AND로 연결될 경우 각각의 조건을 ()로 묶은 뒤 = 표시로 한번에 비교할 수 있음.

4. ALL, DISTINCT
중복의 허용, 제거를 정해줌. 디폴트는 ALL (집합은 DISTINCT)

5. 속성명 prefix
원하는 테이블의 속성을 PREFIX로 지정할 수 있음.

6. NATURAL JOIN
공통된 속성에서 같은 값들을 가지는 경우에만 JOIN한다. 따라서 공통된 속성이 여러 개인 경우에 주의 해서 사용할 필요가 있다.

7. JOIN ... USING(~)
속성 OOO을 사용한 JOIN을 진행한다. NATURAL JOIN과 유사하나 원하는 속성들을 지정할 수 있다는 점이 다르다.

8. AS
명칭을 재 설정함, 주로 새로운 속성을 만들어냈거나 혹은 여러 테이블에 같은 속성명을 가질 경우에 사용한다.
또, FROM clause에 내부쿼리를 사용할 경우 반환되는 relation의 이름과 속성을 AS로 정할 수 있다.(correlation variable의 설정) 이는 mysql에서 필수로 사용해야 한다.

9.*
*표시의 경우 일반적으로 모든 속성을 선택할 때 사용. JOIN된 여러 테이블 중 일부 테이블의 모든 속성을 출력할 때 사용도 가능하다. i.e. SELECT teaches.*

10. ORDER BY
특정 속성 값들을 기준으로 정렬할 수 있다. 여러 개의 속성을 지정할 수 있고 각 속성뒤에 ASC, DESC를 붙 여 내림차순, 오름차순을 지정할 수 있음.

11. 집합 연산 : UNION, INTERSECT, EXCEPT
각각의 테이블을 집합으로 보고 집합 연산을 수행할 수 있음. i.e. (...) UNION (...)

12. IS NULL / IS NOT NULL
널 값을 검사하고 불리언 결과를 출력함.

13. 집계 연산 : AVG, MIN, MAX, SUM, COUNT
집계 시 중복되는 값의 처리에 대한 고려가 필요하다.
널 값의 경우 무시하고 처리한다. 카운트는 안 세고, 나머지는 없다 생각하고 집계함.

14. GROUP BY
특정 속성을 그룹으로하여 집계 결과를 출력함. 반드시 그룹지정에 사용되는 속성을 SELECT 해주어야 한다. 집계하지않고 단순히 그룹으로 묶기만 하는 것은 안된다.

15. HAVING
GROUP BY와 함께 사용하여 그룹으로 집계된 테이블에 조건을 적용한다.

16. NESTED QUERY 중첩 질의
쿼리문에서 테이블이 들어갈 수 있는 자리면 어디라도 테이블을 생성하는 쿼리문이 들어갈 수 있다.

17. (...) IN/NOT IN (N.query)
중첩 질의에 포함되어있는지 테스트하는 쿼리. (...)과 (N.query)에는 동일한 수의 attribute이 들어있어야 함. N.query 부분에 임의로 작성한 값들의 집합이 들어가도 사용할 수 있음. i.e. IN(10, 20, 30)

18. SOME(N.query)
ALL과 비교하여 기억해둔다. SOME은 N.query에 있는 모든 값보다 클 필요없이 한 개라도 큰지를 조건화한다.

19. ALL(N.query)
SOME 과 비교된다. ALL은 N.query의 모든 값보다 큰 경우를 조건화한다.

20. EXISTS/NOT EXISTS (N.query)

N.query의 값의 존재 여부에 따라 불리언 값을 출력해준다. Empyty relation test.

이는 직관적이지 못한 설명인 것 같다. 직관적으로 조건을 내부 쿼리문으로써 작성하는 것이다.
반드시 N.query는 correlated subquery여야 한다. 즉 내부 쿼리에서 외부 쿼리의 테이블 값을 조건으로 사용해야 한다. 이 때 EXISTS는 내부 쿼리에 포함된 값만을 출력하고, NOT EXISTS는 내부 쿼리에 포함되지 않은 값만을 출력한다.

21. UNIQUE/NOT UNIQUE (N.query)
중복을 확인하는 용도. EXISTS와 유사하게 사용되는데 MYSQL에는 적용되지 않는다. 그래서 다른 방법을 사용하여 중복 튜플을 찾아내야 한다.

22. Subquery in FROM clause
FROM clause에는 테이블만 들어가면 되므로 내부 쿼리를 사용하여 FROM clause에 테이블을 반환해줄 수 있다. FROM절에서 외부쿼리의 테이블의 correlation variable을 참조하기 위해선 반드시 내부쿼리문 앞에 LATERAL을 붙여야 한다. 하기 코드에서 LATERAL을 생략하면 참조할 수 없게됨.

SELECT name, salary, A.avg_salary
FROM instructor I1, LATERAL (SELECT AVG(salary) AS avg_salary
FROM instructor I2
WHERE I2.dept_name=I1.dept_name) AS A;

23. WITH clause
WITH 절을 사용하면 임시 릴레이션을 정의할 수 있다. 한 개 이상 여러 개도 콤마로 구분하여 작성할 수 있음. 이를 활용하면 복잡한 JOIN을 미리 빼서 정의함으로써 직관적으로 쿼리문 작성이 가능하게 한다. 이는 임시 릴레이션으로 사용 후 사라진다.

24. DELETE FROM r WHERE P;
DB에서의 삭제는 튜플 단위로만 가능하다. 한 튜플의 한 속성만 제거할 순 없다.
r에 릴레이션, R에 조건을 작성하여 삭제한다. 이 때 WHERE 절은 SELECT와 동일하게 여러 표현들을 사용하여 작성하면 된다.

25. INSERT INTO r(att1, att2, ...., attN) VALUES(val1, val2, ..., valN)
한 튜플씩 입력하려면 위와같이 작성하면 된다. 정확하게 모든 속성들을 입력한다면 r뒤의 속성 명세는 작성하지 않을 수 있음. relation 속성들의 순서는 바뀌어도 상관없다. 각 relation의 속성을 누락시켜서 입력할 수도 있는데 누락된 속성에는 널 값이 자동으로 입력되게 된다.
쿼리를 통해 생성된 릴레이션의 모든 튜플을 한번에 입력하고 싶은 경우 VALUES대신에 SELECT 쿼리문을 작성하면 된다. 이 때 필히 쿼리문을 먼저 검사해볼 것.

26. UPDATE r SET ... WHERE ...
조건을 통해 원하는 튜플들을 지정하고 변경할 내용은 SET에 작성한다. 이 때 WHERE내 조건들이 여러 CASE로 나뉘는 경우 SET 내에 CASE문을 사용하여 여러 경우에 따른 SET값을 지정해줄 수 있음.

SET att = CASE WHEN ... THEN ...
               ELSE ...
          END

또 하기와 같은 방식으로 scalar subquery와 CASE문을 사용한 업데이트도 가능하다.

UPDATE student S
SET tot_cred = (
    SELECT CASE
           WHEN SUM(credits)IS NOT NULL THEN SUM(credits)
           ELSE 0
           END
    FROM takes NATURAL JOIN course
    WHERE S.ID=takes.ID AND
             takes.grade <> 'F' AND
             takes.grade IS NOT NULL);