Lecture 04, SQL (2)

• SQL 조작문 기초
  • 부의 질의문(subquery)
    • 질의문 안에 중첩되어 포함된 또 다른 SELECT 검색문으로 여러번 반복이 가능함
    • 괄호 안의 가장 안쪽 부질의문 먼저 수행하고 바깥쪽 질의문을 가장 나중에 수행
    • WHERE 절의 검색 조건값을 미리 알지 못하거나 실시간으로 조건식을 완성해야 하는 경우 유용
    • inner query: 안쪽에 위치한 부질의문
    • outer query 또는 main query: 바깥에 위치한 주질의문

    SELECT 이름
    FROM 학생
    WHERE 학번 IN (SELECT 학번
                           FROM 수강
                           WHERE 과목번호='c002');

    SELECT 이름
    FROM 학생
    WHERE 학번 IN (SELECT 학번
                       FROM 수강
                      WHERE 과목번호 = (SELECT 과목번호
                                               FROM 과목
                                               WHERE 이름='정보보호'));

• EXISTS 연산자
  • 부질의문의 실행 결과로 반환되는 행이 존재하는지를 확인
  • 왼쪽에 상수 값이나 열 이름을 명세하지 않고 사용

    SELECT 이름
    FROM 학생
    WHERE EXISTS(SELECT *
                   FROM 수강
                   WHERE 수강.학번=학생.`학번` AND 과목번호='c002');

• 상호연관(상관) 질의문(correlated query)
  • 중첩 질의문 중에서 내부 질의의 WHERE절 ‘검색_조건식’이 외부 질의에 선언된 테이블의 일부 열을 참조
  • 안쪽 부질의문의 WHERE절 조건식에 바깥 주질의문의 학생 테이블이 사용되어 바깥 질의와 안쪽 질의가 서로 연관되어 실행
  • 대부분 join으로 대체되어 실행
• 테이블 별칭 사용
  • as 혹은 테이블 명 바로 옆에 별칭을 적음

    SELECT 이름, 과목번호
    FROM 학생 AS S, 수강 E
    WHERE S.학번=E.학번 AND 과목번호='c002';

• Self Join
  • 특정 테이블에 속하는 행들을 같은 테이블에 속하는 행들과 조인
  • 하나의 테이블은 테이블 별칭을 다르게 지정함으로써 마치 복제된 두 테이블이 존재하는 것처럼 조인 가능

    SELECT S1.이름, S2.이름
    FROM 학생 S1 JOIN 학생 S2 ON S1.주소= S2.`주소`
    WHERE S1.학년 > S2.학년;

• Outer Join
  • 일반적으로 Inner Join은 조인 조건을 만족하는 행만 결과에 포함
  • 조인 대상 테이블의 모든 행들이 결과에 포함되게 하려면 Outer Join을 사용해야 함
  • 어느 테이블의 행을 모두 보여줄 것인가에 따라 Left, Right로 구분

   SELECT 학생.`학번`, 이름, 평가학점
   FROM 학생 LEFT OUTER JOIN 수강 ON 학생.학번 = 수강.학번

• left outer join
  • 조인 연산자의 왼쪽 테이블의 모든 행들이 빠짐없이 조인 최종 결과에 포함
• right outer join
  • 조인 연산자의 오른쪽 테이블의 모든 행들이 빠짐없이 조인 최종 결과에 포함
• full outer join
  • 조인 연산자의 왼쪽과 오른쪽 테이블의 모든 행들이 빠짐없이 조인 최종 결과에 포함

---

• Insert, Delete, Update
  • Insert

    # 열리스트의 경우에는 생략 가능하나 이 경우 values의 값 부분이 전체 컬럼수, 순서와 일치해야 함
    INSERT INTO 테이블_이름 [(열리스트)] 
    VALUES (열_값_리스트);

• null값을 입력할 때 null로 표시하거나 아예 입력하지 않음(null 허용하는 컬럼의 경우만)
• default값이 지정되어있는 경우에도 생략할 수 있음
• sub query를 이용해 입력할 수 있음

    INSERT INTO 학생(이름, 주소, 학년, 나이, 성별, 휴대폰번호, 소속학과, 학번)
    VALUES ('홍길동1', DEFAULT, 1, 26, '남', NULL, '통계', 'g002');

    INSERT INTO 학생(이름, 학년, 나이, 성별, 소속학과, 학번)
    VALUES ('홍길동1', 1, 26, '남', '통계', 'g002'); # 위의 쿼리나 이 쿼리를 이용하면 됨

    INSERT INTO 학생(학년, 나이, 성별, 소속학과, 학번, 이름)
    VALUES (3, 30, '남', '정보통신', 'g003', '이승엽1');

• Update

    UPDATE 테이블_이름
    SET 열이름 = 산술식 [,{열이름=산술식}*]
    [WHERE 수정_조건식];

• 단일, 복수 행열 수정 가능

    UPDATE 학생
    SET 학년=3
    WHERE 이름='이은진';

   SELECT * FROM 학생
   WHERE 이름='이은진';

   UPDATE 학생
   SET 학년=학년+1, 소속학과='자유전공학부'
   WHERE 학년=4;

   SELECT * FROM 학생;

    # sub query를 활용한 update 방법
    ## 1
    UPDATE 학생
    SET 소속학과 = NULL
    WHERE 학번 NOT IN (SELECT 학번
                            FROM 수강);

    SELECT * FROM 학생;

    ## 2
    UPDATE 수강
    SET 학번 = (SELECT 학번
                      FROM 학생
                       WHERE 이름='이은진')
    WHERE 학번='s003';

    SELECT * FROM 수강 WHERE 학번='s003' OR 학번='s007';

• Delete

   DELETE FROM 테이블이름
   [WHERE 삭제조건식];

    # 단일 행 삭제
    DELETE FROM 학생
    WHERE 이름='송윤아';

    SELECT * FROM 학생;

    # 복수 행 삭제
    DELETE FROM 학생
    WHERE 학년=3;

    SELECT * FROM 학생1;

   DELETE FROM 과목
   WHERE 과목번호 IN (
                SELECT 과목번호
                   FROM 수강
                  GROUP BY 과목번호
                   HAVING COUNT(*) < 2);

   SELECT * FROM 과목;

  DELETE FROM 학생;
  SELECT * FROM 학생;