14장. 부속 질의어 부속 질의어 규칙 열의 범위 상호 관련부속 질의어 복합키의 사용 쉽게 배우는 MySQL 5.x 2019-04-18 쉽게 배우는 MySQL 5.x

14.1 부속 질의어 규칙 -부속 질의어(subquery)는 SELECT 명령문의 조건에 있는 SELECT 명령문 -부속선택문(subselect) 또는 내부선택문(interselect)라 명명 부속 질의어의 정의와 SELECT 명령문의 차이점 3가지 ① 부속 질의어가 EXISTS 연산자를 사용하지 않는다면 SELECT 절에는 오직 하나의 수식만 사용 -조건이 부속 질의어의 형식을 가진다면 SQL은 부속 질의어의 결과를 'Jim', 18, 380.14와 같은 단일 값과 비교 -이러한 값은 <'Jim', 14>, <'Pete', 25>, <'Regina', 83>와 같은 값의 집합과는 완전히 다르다. -특수 문자 *(asterisk)는 EXISTS가 사용될 때 사용된다. ② SELECT 절에 DISTINCT는 사용할 수 없다. -중복된 값이 생략되거나 값이 서로 다르게 배열되었을 경우 값들의 집합적 의미를 변경할 수 없다. -다음의 집합은 부속 질의어의 번역에 대해서는 동일하다.   (1, 4, 8), (8, 1, 4),   (4, 4, 1, 8),   (8, 1, 4, 8, 1, 4) ③ ORDER BY 절은 사용할 수 없다.(DISTINCT와 같은 이유). 2019-04-18 쉽게 배우는 MySQL 5.x

14.2 열의 범위 부속 질의어의 가장 중요한 특성은 열의 범위이다. 14.2 열의 범위 부속 질의어의 가장 중요한 특성은 열의 범위이다. [예제 14-1] 적어도 한 번 이상 등록한 학생의 학번과 이름을 출력하라.  SELECT STU_NO, STU_NAME  FROM   STUDENT  WHERE  EXISTS Q1 (SELECT  *  FROM    FEE  WHERE   STUDENT.STU_NO = STU_NO); Q2 STUDENT 테이블에 있는 열은 선택 블록 Q1, Q2에서 사용할 수 있지만 FEE 테이블에 있는 열은 선택 블록 Q2에서만 사용할 수 있다. STUDENT 테이블로부터 STU_NO열을 취하여 Q2에서 사용할 수 있다. STUDENT.STU_NO = STU_NO STU_NO는 FEE 테이블에서 모든 행에 대하여 정당하기 때문에 각 학생에 대한 STU_NAME도 표현될 것이다. 선택 블록 Q2는 다른 선택 블록에서 지정된 테이블에 포함된 열을 가지고 있기 때문에 상호 관련 부속 질의어(correlated subquery)라 한다. 2019-04-18 쉽게 배우는 MySQL 5.x

Q1에 있는 FROM 절의 중간 결과는 STUDENT 테이블의 복사본이다. +----------+----------+ | stu_no   | stu_name | | 20001001 | 김유신   | | 20001015 | 박도준   | | 20001021 | 이상길   | | 20041002 | 김유미   | | 20041007 | 정인정   | | 20041033 | 연개소문 | | 20061011 | 박정인   | | 20061014 | 고혜진   | | 20061048 | 김영호   | | 20071001 | 장수인   | | 20071010 | 홍길동   | | 20071022 | 이순신   | | 20071300 | 유하나   | | 20071307 | 김문영   | | 20071405 | 최차영   | 15 rows in set (0.00 sec) Q2에 있는 FROM 절의 중간 결과는 FEE 테이블의 복사본이다. +----------+----------+        .....       +----------+---------+ | stu_no   | fee_year |        .....       | fee_term | fee_pay | | 20001015 | 2000     |        .....       |        1 | 2000000 | | 20001015 | 2000     |        .....       |        2 |       0 | | 20001015 | 2001     |        .....       |        1 |  300000 | | 20001015 | 2001     |        .....       |        2 |  300000 | | 20001015 | 2006     |        .....       |        1 |  500000 | | 20001015 | 2006     |        .....       |        2 |  500000 | | 20001015 | 2007     |        .....       |        1 | 1000000 | | 20001015 | 2007     |        .....       |        2 |  500000 | | 20061011 | 2006     |        .....       |        1 | 3000000 | | 20061011 | 2006     |        .....       |        2 |  500000 | | 20061011 | 2007     |        .....       |        1 | 1000000 | | 20061011 | 2007     |        .....       |        2 | 2200000 | | 20071001 | 2007     |        .....       |        1 | 3000000 | | 20071001 | 2007     |        .....       |        2 | 3000000 | | 20071010 | 2007     |        .....       |        1 | 3000000 | | 20071010 | 2007     |        .....       |        2 | 3000000 | | 20071022 | 2007     |        .....       |        1 | 3000000 | | 20071022 | 2007     |        .....       |        2 | 3000000 | | 20071300 | 2007     |        .....       |        1 | 3000000 | | 20071300 | 2007     |        .....       |        2 | 1000000 | | 20071307 | 2007     |        .....       |        1 | 3000000 | | 20071307 | 2007     |        .....       |        2 | 3000000 | | 20071405 | 2007     |        .....       |        1 | 3000000 | | 20071405 | 2007     |        .....       |        2 |  500000 | 24 rows in set (0.00 sec) 2019-04-18 쉽게 배우는 MySQL 5.x

서브쿼리는 중복된 값을 나타내지 않기 때문에 이와 같은 과정을 계속 수행하면, 명령문의 최종 결과는 다음과 같다. +----------+----------+ | STU_NO   | STU_NAME | | 20001015 | 박도준   | | 20061011 | 박정인   | | 20071001 | 장수인   | | 20071010 | 홍길동   | | 20071022 | 이순신   | | 20071300 | 유하나   | | 20071307 | 김문영   | | 20071405 | 최차영   | 8 rows in set (0.00 sec) -부속 질의어는 각 학생에 대하여 개별적으로 실행된다. -부속 질의어에 있는 WHERE 절은 항상 참인 조건을 가지고 있다. -부속 질의어는 항상 하나의 행을 반환한다. -결론적으로 이 명령문은 STUDENT 테이블과 FEE 테이블의 학번이 동일한  모 든 학생의 학번과 이름을 반환한다. 2019-04-18 쉽게 배우는 MySQL 5.x

14.3 상호 관련 부속 질의어의 예제 상호 관련 부속 질의어는 다른 선택 블록에서 지정된 테이블에 포함된 열을 사용하는 부속 질의어로 정의 [예제 14-2] 학생중에 동아리의 등급이 일반 회원인 학생의 학번과 이름, 주민등록번호를 출력하라. mysql> select stu_no, stu_name, id_num     -> from student     -> where stu_no in     -> (select stu_no     -> from circle     -> where president = 2); +----------+----------+----------------+ | stu_no   | stu_name | id_num         | | 20071001 | 장수인   | 890209-1616822 | | 20071307 | 김문영   | 880418-2121623 | 2 rows in set (0.00 sec) 회원번호가 2인 회원을 먼저 찾아내고 학적 테이블의 학번과 비교하여 동일하면 학번과 이름, 주민등록번호를 출력 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-3] 두 번 이상 장학금을 지급 받은 학생중 장학금액이 학기별로 서로 다른 경우의 학생의 학번을 출력하라. mysql> select distinct stu_no     -> from fee f     -> where stu_no in     -> (select stu_no     -> from fee     -> where jang_total <> f.jang_total); +----------+ | stu_no   | | 20001015 | | 20061011 | | 20071300 | | 20071405 | 4 rows in set (0.00 sec) 등록테이블에서 동일한 학번을 갖지만 서로 다른 장학금액을 갖는 또 다른 행이 있는지 조사한다. 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-4] 학적 테이블에서 동아리 “Java길라잡이”에 가입하지 않은 학생의 학번과 이름, 주야구분을 출력하라. mysql> select stu_no, stu_name, juya     -> from student     -> where 'Java길라잡이' <> all     -> (select cir_name     -> from circle     -> where stu_no = student.stu_no); +----------+----------+------+ | stu_no   | stu_name | juya | | 20001001 | 김유신   | 야   | | 20001021 | 이상길   | 주   | | 20041002 | 김유미   | 주   | | 20041033 | 연개소문 | 야   | | 20061011 | 박정인   | 주   | | 20061014 | 고혜진   | 주   | | 20061048 | 김영호   | 야   | | 20071010 | 홍길동   | 야   | | 20071022 | 이순신   | 야   | | 20071300 | 유하나   | 주   | | 20071307 | 김문영   | 야   | | 20071405 | 최차영   | 주   | 12 rows in set (0.00 sec) 부속 질의어는 동아리에 가입한 모든 회원의 학번 목록을 생성 주 질의어는 동아리 “Java길라잡이”에 가입한 학생을 제외한 모든 학생 출력 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-5] 학적 테이블에서 학번이 가장 큰 3명의 학번을 내림차순 출력하라. 예제) 학번이 가장 큰 값을 출력하라. mysql> select max(stu_no)     -> from student; +-------------+ | max(stu_no) | | 20071405    | [예제 14-5] 학적 테이블에서 학번이 가장 큰 3명의 학번을 내림차순 출력하라. mysql> select stu_no     -> from student s1     -> where 3 >     -> (select count(*)     -> from student s2     -> where (s1.stu_no < s2.stu_no))     -> order by stu_no desc; +----------+ | stu_no   | | 20071405 | | 20071307 | | 20071300 | 3 rows in set (0.01 sec) 가장 큰 학번보다 더 큰 학번은 없다. 두 번째 큰 학번보다 더 큰 학번은 하나이며, 세 번째 큰 학번보다 더 큰 학번은 두 개다. 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-6] 학적 테이블에서 학번이 가장 작은 학번을 가진 3명의 학생을 오름차순으로 출력하라. mysql> select stu_no     -> from student s1     -> where 3 >     -> (select count(*)     -> from student s2     -> where (s1.stu_no > s2.stu_no))     -> order by stu_no; +----------+ | stu_no   | | 20001001 | | 20001015 | | 20001021 | 3 rows in set (0.00 sec) 이상의 2개의 예제는 중복된 값을 가지고 있지 않는 열과 NULL값을 가지지 않은 열에만 적용된다. 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-7] 등록테이블에서 장학금을 지급 받은 학생 중 가장 작은 장학금액을 지급 받은 학생 8명의 학번, 장학금 총액을 출력하라. mysql> select distinct stu_no, jang_total           -> from fee f1     -> where 8 >     -> (select count(*)     -> from fee f2     -> where f1.jang_total > f2.jang_total)     -> order by f1.jang_total desc; +----------+------------+ | stu_no   | jang_total | | 20061011 |     800000 | | 20061011 |     500000 | | 20071001 |     500000 | | 20071010 |     500000 | | 20071022 |     500000 | | 20071300 |     500000 | | 20071307 |     500000 | | 20071405 |     500000 | | 20071001 |       NULL | | 20071010 |       NULL | | 20071022 |       NULL | | 20071307 |       NULL | 12 rows in set (0.00 sec) 장학금 총액이 NULL인 경우가 4명이므로 NULL 4명을 포함하여 장학금액이 작은 크기순으로 8명, 합계 12명이 출력(NULL은 가장 작은 값으로 취급) 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-8] 등록테이블에서 등록한 학생 중에서 납부 총액이 가장 큰 학생을 포함한 3명의 학번, 납부 총액을 출력하라. mysql> select stu_no, fee_pay                  -> from fee f1     -> where 3 >     -> (select count(*)     -> from fee f2     -> where f1.fee_pay < f2.fee_pay)     -> order by f1.fee_pay desc; +----------+---------+ | stu_no   | fee_pay | | 20061011 | 3000000 | | 20071307 | 3000000 | | 20071300 | 3000000 | | 20071022 | 3000000 | | 20071010 | 3000000 | | 20071001 | 3000000 | | 20071405 | 3000000 | 11 rows in set (0.00 sec) 결과와 같이 동일한 등록금 총액을 가진 학생이 여러 명인 경우에는 예기치 못한 결과가 출력 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-9] “20061011”학생이 가입한 동아리에 소속된 모든 학생의 학번과 이름을 출력하라. mysql> select stu_no, stu_name                   -> from student s     -> where not exists     -> (select * from circle c     -> where c.stu_no = '20061011'     -> and not exists     -> (select * from circle c2     -> where c.cir_name = c2.cir_name     -> and s.stu_no = c2.stu_no)); +----------+----------+ | stu_no   | stu_name | | 20061011 | 박정인   | | 20071300 | 유하나   | | 20071307 | 김문영   | 3 rows in set (0.00 sec) -두 번째 부속질의어는 동아리테이블에  “20061011”학생이  존재여부를 확인 -세 번째 부속질의어에서는 “20061011”학생이 가입한 동아리(“컴맹탈출”)와 동아리 이름이 같은 학생 추출 -첫 번째 부속 질의어에서는 “20061011”학생이 가입한 동아리를 2중 부정(부정에 대한 부정)하여 (“컴맹탈출”)의  모든 회원의 학번과 이름을 출력 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-10] 적어도 한 번 장학금을 받은 학생에 대하여 학번, 등록년도, 학기, 장학금액 중 가장 큰 장학금액, 등록일자를 출력하라. mysql> select stu_no, fee_year, fee_term, jang_total, fee_date     -> from fee f1     -> where jang_total =     -> (select max(jang_total)     -> from fee f2     -> where f1.stu_no = f2.stu_no); +----------+----------+----------+------------+------------+ | stu_no   | fee_year | fee_term | jang_total | fee_date   | | 20001015 | 2000     |        2 |    2500000 | 2000-08-10 | | 20001015 | 2001     |        1 |    2500000 | 2001-02-15 | | 20001015 | 2001     |        2 |    2500000 | 2001-08-16 | | 20001015 | 2006     |        1 |    2500000 | 2006-02-14 | | 20001015 | 2006     |        2 |    2500000 | 2006-08-18 | | 20001015 | 2007     |        2 |    2500000 | 2007-08-19 | | 20061011 | 2006     |        2 |    2500000 | 2006-08-20 | | 20071001 | 2007     |        1 |     500000 | 2007-02-18 | | 20071010 | 2007     |        1 |     500000 | 2007-02-18 | | 20071022 | 2007     |        1 |     500000 | 2007-02-18 | | 20071300 | 2007     |        2 |    2000000 | 2007-08-10 | | 20071307 | 2007     |        1 |     500000 | 2007-02-18 | | 20071405 | 2007     |        2 |    2500000 | 2007-08-10 | 13 rows in set (0.00 sec) 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-11] 적어도 한 번 이상 수강신청을 하고 등록한 학생에 대하여 학번, 이름, 주민등록번호를 출력하라. mysql> select stu_no, stu_name, id_num          -> from student s     -> where not exists     -> (select stu_no     -> from fee f     -> where fee_div = 'Y'     -> and not exists     -> (select * from     -> attend a     -> where s.stu_no = a.stu_no     -> and a.att_div = 'Y')); +----------+----------+----------------+ | stu_no | stu_name | id_num | | 20061011 | 박정인 | 830403-1635213 | | 20071300 | 유하나 | 880921-2573717 | 2 rows in set (0.23 sec) 2019-04-18 쉽게 배우는 MySQL 5.x

14.4 복합키의 사용 -STUDENT1 테이블에서 사용하는 기본 키는 STU_NAME의 “강성희”가 동명이인이기 때문에 STU_NAME과 STU_ENAME을 조합하여 구성 -FEE1 테이블의 기본 키는STU_NO으로 변경하여 사용 < STUDENT1 테이블의 구조 > STU_NAME      STU_ENAME      TOWN ----------    ---------   ---------- 강성희          G            순천     강성희          K            순천 강국원          K            여수 < FEE1 테이블의 구조 > STU_NO          STU_NAME    STU_ENAME    JANG_TOTAL ---------       ----------  -------      -----------         1       강성희      G                1000000         2       강국원      K                2000000 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-12] 장학금을 지급 받은 학생의 이름과 영문이름을 출력하라. SELECT STU_NAME, STU_ENAME, TOWN SELECT  S.STU_NAME, S.STU_ENAME, TOWN FROM   STUDENT1 FROM  STUDENT1 S, FEE1 F WHERE  STU_NAME IN WHERE S.STU_NAME = F.STU_NAME               (SELECT  STU_NAME AND   S.STU_ENAME = F.STU_ENAME;                FROM   FEE1) AND   STU_ENAME IN STU_NAME STU_ENAME    TOWN               (SELECT  STU_ENAME ---------- ----------   ----                FROM   FEE1); 강성희        G         순천     STU_NAME   STU_ENAME    TOWN 강국원        K         여수 ---------- ----------   ---- 강성희     G            순천 강국원     K            여수 강성희     K            순천 SELECT 명령문은 결과처럼 선수 “강성희 K”는 장학금을 받지 못한 학생이 출력되어 질문에 대한 올바른 결과가 아니다. 그러므로 우측 SELECT 명령문처럼 사용한다. 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-12]를 다음과 같이 IN 연산자를 사용하여 작성할 수 있다. SELECT  STU_NAME, STU_ENAME, TOWN FROM STUDENT1 S WHERE STU_NAME IN   (SELECT  STU_NAME    FROM    FEE1 F    WHERE   S.STU_ENAME = F.STU_ENAME); STU_NAME      STU_ENAME    TOWN ----------    ---------   ---------- 강성희        G            순천     강국원        K            여수 부속 질의어를 사용하여 FEE1 테이블에서 동일한 영문이름을 찾는다. FEE1 테이블의 학생이름 STU_NAME이 주 질의어 STUDENT1 테이블의 학생이름 STU_NAME과 이름이 같은지를 확인한다.(WHERE STU_NAME IN ...) 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-13] 장학금을 지급 받지 못한 학생의 이름, 영문이름, 도시를 출력하라. SELECT  S.STU_NAME, S.STU_ENAME, TOWN SELECT  DISTINCT S.STU_NAME, S.STU_ENAME, TOWN FROM  STUDENT1 S, FEE1 F FROM  STUDENT1 S, FEE1 F WHERE S.STU_NAME <> F.STU_NAME WHERE NOT EXISTS AND   S.STU_ENAME <> F.STU_ENAME; (SELECT   *  FROM     FEE1 STU_NAME   STU_ENAME  TOWN   WHERE    S.STU_NAME = F.STU_NAME ---------- ---------- ----- AND  S.STU_ENAME = F.STU_ENAME); 강성희     G          순천 STU_NAME      STU_ENAME    TOWN 강국원     K          여수 ----------    ---------   ---------- 강성희        K            순천 위의 예제를 처리하기 위해서 예제 14-12의 명령문에서 = 대신에 <>을 사용함으로써 해결할 수는 없다. 위의 조인(JOIN)은 요구하는 결과를 반환하지 않는다. 따라서 우측의  NOT EXISTS 연산자를 사용하여 올바른 출력을 얻을 수 있다. 기본 키가 하나 이상의 열로 구성되어 있는 2개의 테이블을 조인할 때는 아주 조심스럽게 연산자를 사용해야 한다. 2019-04-18 쉽게 배우는 MySQL 5.x

위의 SELECT 명령문은 질문에 대한 올바른 결과를 출력하지 않는다. [예제 14-14] 2007년 1학기에 등록한 학생이 같은 연도, 학기에 수강 신청한 학생의 학번과 등록년도, 등록학기, 수강신청년도, 학기를 출력하라.(중복된 출력자료를 제거하기 위해서 DISTINCT를 사용) mysql> select distinct f.stu_no, fee_year, fee_term,     -> att_year, att_term     -> from fee f, attend a     -> where f.stu_no = a.stu_no     -> and fee_year = '2007'     -> and fee_term = '1'; +----------+----------+----------+----------+----------+ | stu_no   | fee_year | fee_term | att_year | att_term | | 20061011 | 2007     |        1 | 2006     |        1 | | 20061011 | 2007     |        1 | 2006     |        2 | | 20071300 | 2007     |        1 | 2007     |        1 | 3 rows in set (0.00 sec) 위의 SELECT 명령문은 질문에 대한 올바른 결과를 출력하지 않는다. 20061011학생의 등록년도(2007)와 수강년도(2006)가 서로 다르다. 2019-04-18 쉽게 배우는 MySQL 5.x

[예제 14-14] 를 다음과 같이 수정하면 올바른 결과가 출력된다. mysql> select distinct f.stu_no, fee_year, fee_term,     -> att_year, att_term     -> from fee f, attend a     -> where f.stu_no = a.stu_no     -> and fee_year = '2007'     -> and fee_term = '1'     -> and fee_year = att_year     -> and fee_term = att_term; +----------+----------+----------+----------+----------+ | stu_no   | fee_year | fee_term | att_year | att_term | | 20071300 | 2007     |        1 | 2007     |        1 | 1 row in set (0.00 sec) 2019-04-18 쉽게 배우는 MySQL 5.x