유지연

유지연

I’m a junior web developer.
Let’s code with me!

MySQL08 Select문 활용하기

업데이트:
10 분 소요

비트캠프 서초본원 엄진영 강사님의 수업을 듣고 정리했습니다.

1. distinct 와 all

all

  • 모든 데이터를 가져올 때는 all을 붙인다.
select all 컬럼명 from 테이블명;

select all loc from room;
  • all은 생략할 수 있다.
select loc from room;

distinct

  • 중복 값을 한 개만 추출할 때 distinct 를 붙인다.
select distinct 컬럼명 from 테이블명;

select distinct loc from room;
  • 컬럼이 2 개 이상일 때 그 컬럼들의 값이 모두 같은 경우에만 한 개로 간주한다.
select distinct loc, name from room;

2. order by

  • 기본 인덱스 컬럼을 기준으로 정렬한다.
select rno, loc, name
from room;

(1) order by ~ asc

  • 이름의 오름 차순으로 정렬하기 (ascendant)
select rno, loc, name 
from room
order by name asc;
  • asc는 생략 가능하다.
select rno, loc, name 
from room
order by name;
  • 위의 코드에서 select rno, loc만 하면?
    • 실행 순서가 from → order by → select이므로 OK!
select rno, loc
from room
order by name;

(2) order by ~ desc

  • 이름의 내림 차순으로 정렬하기 (descendant)
select rno, loc, name 
from room
order by name desc;
  • 이름은 오름 차순, 지점명도 오름 차순으로 정렬하기
    • 이름으로 먼저 정렬하고, 그 안에서 지점명으로 정렬한다.
select rno, loc, name 
from room
order by name asc, loc asc;
  • 이름은 오름 차순, 지점명은 내림 차순으로 정렬하기
    • 이름으로 먼저 정렬하고, 그 안에서 지점명으로 정렬한다.
select rno, loc, name 
from room
order by name asc, loc desc;
  • 지점명은 오름 차순으로, 이름은 오름 차순 정렬하기
    • 지점명으로 먼저 정렬하고, 그 안에서 이름으로 정렬한다.
select rno, loc, name 
from room
order by loc asc, name asc;

3. 라벨명

(1) 라벨명 붙이기

  • as로 컬럼에 라벨명(별명)을 붙일 수 있다.
  • 라벨명을 지정하지 않으면 컬럼명이 라벨명이 된다.
select rno as room_no, loc as location, name 
from room;
  • as는 생략 가능하다.
select rno room_no, loc location, name 
from room;
  • 라벨명에 공백을 넣고 싶으면 ‘ ‘ 안에 작성한다.
select rno 'room no', loc location, name 
from room;
  • 복잡한 형식으로 출력할 경우 라벨명(별명)을 부여한다.
/* 예) 강의실명(지점명) */
select concat(name, '(', loc, ')')
from room;

select concat(name, '(', loc, ')') title
from room;
  • 컬럼에 저장된 데이터의 개수를 알 수 있다.
select count(*) 
from room;

select count(*) cnt
from room;

select count(*) cnt
from room
where loc='서초';

4. union 과 union all

  • select 결과 합치기

(1) union

  • 중복 값 자동 제거 (union은 합집합을 의미)
select distinct bank from stnt
union
select distinct bank from tcher;

(2) union all

  • 중복 값 제거 안함 (all 생략 불가)
select distinct bank from stnt
union all
select distinct bank from tcher;

5. join

  • 서로 관련된 테이블의 데이터를 연결하여 추출하는 방법

  • 기법

    1) CROSS 조인

    2) NATURAL 조인

    3) JOIN ~ ON

    4) OUTER JOIN

(1) CROSS JOIN

  • 두 테이블의 데이터를 1:1로 모두 연결한다.
  • cross join은 레코드의 상관 관계에 없이 연결되기 때문에 실무에서 거의 사용되지 않는다.
select 컬럼명, 컬럼명, 컬럼명
from 테이블명 cross join 테이블명;
  • mno가 두 테이블에 있을 경우 어떤 테이블의 컬럼인지 지정하지 않으면 실행 오류!
  • memb 테이블의 데이터와 stnt 테이블의 데이터를 cross join하여 레코드를 출력하라.
select mno, name, mno, work, bank
from memb cross join stnt;
/* ERROR 1052 (23000): Column 'mno' in field list is ambiguous */
  • select 컬럼이 두 테이블 모두 있을 경우, 컬럼명 앞에 테이블명을 명시하여 구분하라!
select memb.mno, name, stnt.mno, work, bank
from memb cross join stnt;
  • 예전 문법
select memb.mno, name, stnt.mno, work, bank
from memb, stnt;
  • 컬럼명 앞에 테이블명을 붙이면 너무 길다.
    • 테이블에 별명을 부여하고 그 별명을 사용하여 컬럼을 지정하라.
select 테이블별명.컬럼명, 컬럼명, 테이블별명.컬럼명, 컬럼명
from 테이블명 테이블별명 cross join 테이블명 테이블별명;

select m.mno, name, s.mno, work, bank
from memb m cross join stnt s;
  • 예전 문법
select m.mno, name, s.mno, work, bank
from memb m, stnt s;

(2) NATURAL JOIN

  • Natural Join : 같은 이름을 가진 컬럼 값을 기준으로 레코드를 연결한다.
select 테이블별명.컬럼명, 컬럼명, 테이블별명.컬럼명, 컬럼명
from 테이블명 테이블별명 natural join 테이블명 테이블별명;
  • mno라는 이름을 가진 컬럼 값을 기준으로 레코드를 연결한다.
select m.mno, name, s.mno, work, bank
from memb m natural join stnt s;
  • 예전 문법
select m.mno, name, s.mno, work, bank
from memb m, stnt s
where m.mno=s.mno;

natural join 의 문제점 I

1) 두 테이블의 조인 기준이 되는 컬럼 이름이 다를 때 연결되지 못한다.

2) 상관 없는 컬럼과 이름이 우연히 같을 때 잘못 연결된다.

3) 같은 이름의 컬럼이 여러 개 있을 경우 잘못 연결된다. 모든 컬럼의 값이 일치할 경우에만 연결되기 때문이다.

  • 만약에 두 테이블에 같은 이름을 가진 컬럼이 여러 개 있다면, join ~ using (기준컬럼) 을 사용하여 두 테이블의 데이터를 연결할 때 기준이 될 컬럼을 지정한다.
select m.mno, name, s.mno, work, bank
from memb m join stnt s using (mno);

natural join 의 문제점 II

  • 두 테이블에 같은 이름의 컬럼이 없을 경우 연결하지 못한다.
  • 만약 두 테이블에 같은 이름을 가진 컬럼이 없으면, natural join을 수행하지 못한다. 또한 join using 으로도 해결할 수 없다. 이럴 경우 (inner) join ~ on 컬럼a=컬럼b 문법을 사용하여 각 테이블의 어떤 컬럼과 값을 비교할 것인지 지정하라!
select m.mno, name, s.mno, work, bank
from memb m inner join stnt s on m.mno=s.mno;

(3) INNER JOIN

  • Iner Join : 각 테이블의 어떤 컬럼과 비교할 것인지 지정하여 레코드를 연결한다.
select 테이블별명A.컬럼명, 컬럼명, 테이블별명B.컬럼명, 컬럼명
from 테이블명 테이블별명A inner join 테이블명 테이블별명B on A.컬럼=B.컬럼 ;
  • inner는 생략 가능하다.
select m.mno, name, s.mno, work, bank
from memb m join stnt s on m.mno=s.mno;
/* 즉 inner join 은 기준 컬럼의 값이 일치할 때만 데이터를 연결한다. */
  • 예전 조인 문법 = inner join
select m.mno, name, s.mno, work, bank
from memb m, stnt s
where m.mno=s.mno;

[inner] join ~ on 의 문제점 I

  • 반드시 on 에서 지정한 컬럼의 값이 같을 경우에만 두 테이블의 데이터가 연결된다.

  • 같은 값을 갖는 데이터가 없다면 연결되지 않고, 결과로 출력되지 않는다.

  • 전체 강의 목록
select lno, titl, rno, mno from lect;
  • 전체 강의실 목록
select rno, loc, name from room;
  • 강의 테이블에서 강의명을 가져오고, 강의실 테이블에서 지점명과 강의실명을 가져오자.
select 
    l.lno, 
    l.titl, 
    r.rno, 
    r.loc, 
    r.name
from lect l inner join room r on l.rno=r.rno;
  • inner join의 경우, 강의실이 아직 지정되지 않은 강의의 경우 강의실 테이블의 데이터와 연결하지 못해 결과로 출력되지 않는 문제가 있다.

inner join의 문제점 예 II

  • 모든 강의장 이름을 출력하라.
  • 단 강의장에 강의가 배정된 경우 그 강의 이름도 출력하라.
select 
  r.rno, 
  r.name, 
  r.loc, 
  l.titl
from room r inner join lect l on r.rno = l.rno;
  • 만약 기준 컬럼의 값과 일치하는 데이터가 없어서 다른 테이블의 데이터와 연결되지 않았다 하더라도 결과로 뽑아내고 싶다면 outer join을 사용하라!
  • 즉 아직 강의실이 배정되지 않은 강의 데이터도 출력하고 싶을 때 출력하고 싶은 테이블을 바깥쪽 테이블로 지정하라!

(4) OUTER JOIN

right outer join ~ on

  • 오른쪽(right) 테이블을 기준으로 왼쪽 데이터를 붙여서 볼 때 사용
select 
    l.lno, 
    l.titl, 
    r.rno, 
    r.loc, 
    r.name
from lect l right outer join room r on l.rno=r.rno;
/* room을 기준으로 lect 데이터를 연결한다. 
 * 만약 room과 일치하는 데이터가 lect에 없더라도 room 데이터를 출력한다!
 */

left outer join ~ on

  • 왼쪽(left) 테이블을 기준으로 오른쪽의 데이터를 붙여서 볼때 사용
select 
    l.lno, 
    l.titl, 
    r.rno, 
    r.loc, 
    r.name
from lect l left outer join room r on l.rno=r.rno;
/* lect을 기준으로 room 데이터를 연결한다. 
 * 만약 lect와 일치하는 데이터가 room에 없더라도 lect 데이터를 출력한다!
 */
  • 요구사항
    • 모든 멤버의 번호와 이름을 출력하라!
    • 단 학생의 경우 재직여부도 출력하라!
-- 1) 모든 멤버 데이터 출력하기
select mno, name
from memb;

-- 2) 학생 데이터를 가져와서 연결하기
select mno, name, work /* mno는 fk이면서도 pk */
from memb natural join stnt;

select mno, name, work
from memb join stnt using(mno);

select memb.mno, name, work
from memb, stnt
where memb.mno=stnt.mno;

select memb.mno, name, work
from memb inner join stnt on memb.mno=stnt.mno;

select memb.mno, name, work
from memb join stnt on memb.mno=stnt.mno; /* inner 생략됨 */

select m.mno, name, work
from memb m join stnt s on m.mno=s.mno;

/* 안타깝게도 위의 SQL문은 학생 목록만 출력한다.
    왜? 
		memb테이블의 데이터와 stnt 테이블의 데이터를 
    연결할 때 mno가 같은 데이터만 연결하여 추출하기 때문이다.
   해결책!      
    상대 테이블(stnt)에 연결할 대상(데이터)이 없더라도
    select에서 추출하는 방법 */
select m.mno, name, work
from memb m left outer join stnt s on m.mno=s.mno;

(5) 여러 테이블 연결하기

  • 여러 번의 joining을 통해 여러 테이블의 데이터를 연결할 수 있다.

  • 다음의 결과가 출력될 수 있도록 수강 신청 데이터를 출력하시오.

    수강신청번호, 강의명, 학생명, 재직여부, 수강신청일, 강의실명, 매니저명, 직위

/* 1단계: 수강신청 데이터를 출력 */
select la.lano, la.lno, la.mno, la.rdt
from lect_appl la;  

/* 2단계: 수강신청한 학생의 번호 대신 이름을 출력 */
select la.lano, la.lno, m.name, la.rdt
from lect_appl la 
			inner join memb m on la.mno=m.mno;
			/* lect_appl 테이블에 memb 테이블을 연결 */

/* 3단계: 수강 신청한 학생의 재직 여부 출력
 * => inner join 에서 inner는 생략 가능 
 */
select la.lano, la.lno, m.name, s.work, la.rdt
from lect_appl la 
        inner join memb m on la.mno=m.mno
        inner join stnt s on la.mno=s.mno;
				/* lect_appl 테이블에 memb, stnt 테이블을 연결 */

/* 4단계: 수상신청한 강의 번호 대신 강의명을 출력 */
select la.lano, l.titl, m.name, s.work, la.rdt, l.rno
from lect_appl la 
        inner join memb m on la.mno=m.mno
        inner join stnt s on la.mno=s.mno 
        inner join lect l on la.lno=l.lno;
				/* lect_appl 테이블에 memb, stnt, lect 테이블을 연결 */

/* 5단계: 강의실 이름을 출력한다.
 * => 강의실 번호는 lect 테이블 데이터에 있다.
 * => 강의실 이름은 room 테이블 데이터에 있다. 
 */
select la.lano, l.titl, m.name, s.work, la.rdt, r.name, l.mno
from lect_appl la 
        inner join memb m on la.mno=m.mno
        inner join stnt s on la.mno=s.mno 
        inner join lect l on la.lno=l.lno
        left outer join room r on l.rno=r.rno;
				/* lect 테이블을 기준으로 room 데이터를 연결 */

/* 6단계: 매니저 이름을 출력
 * => 매니저 번호는 lect 테이블에 있다.
 * => 매니저 이름은 memb 테이블에 있다. 
 */
select 
  la.lano, 
  l.titl, 
  m.name member_name, /* 데이터를 보기 편하도록 별명 추가 */
  s.work, 
  la.rdt, 
  r.name room_name, 
  m2.name manager_name
from lect_appl la 
        inner join memb m on la.mno=m.mno
        inner join stnt s on la.mno=s.mno 
        inner join lect l on la.lno=l.lno
        inner left outer join room r on l.rno=r.rno 
        left outer join memb m2 on l.mno=m2.mno; 
				/* lect 테이블을 기준으로 memb 데이터를 연결 */

/* 7단계: 매니저의 직위 출력
 * => 매니저 번호는 lect 테이블 있다.
 * => 매니저 직위는 mgr 테이블에 있다.  
 */
select 
  la.lano, 
  l.titl, 
  m.name student_name, 
  s.work, 
  la.rdt, 
  r.name room_name, 
  m2.name manager_name, 
  mr.posi
from lect_appl la 
        inner join memb m on la.mno=m.mno
        inner join stnt s on la.mno=s.mno 
        inner join lect l on la.lno=l.lno
        inner left outer join room r on l.rno=r.rno 
        inner left outer join memb m2 on l.mno=m2.mno 
        left outer join mgr mr on l.mno=mr.mno;
				/* lect 테이블을 기준으로 mgr 데이터를 연결 */

6. 서브 쿼리

  • 쿼리문 안에 쿼리문을 실행하는 기법
  • 직관적이기 때문에 사용하기가 편리하다.
  • 하지만, 성능 문제를 생각하면서 사용해야 한다.

(1) select 절에 서브쿼리 사용하기

/* 1단계: 수강신청 데이터를 출력 */
select 
  la.lano, 
  la.lno, 
  la.mno, 
  date_format(la.rdt, '%Y-%m-%d') reg_dt
from lect_appl la;

/* 2단계 : 서브 쿼리를 이용하여 강의명을 가져오기 
 * => 단, 컬럼 자리에 사용할 때는 결과값이 한 개여야 한다.
 *    결과 값이 여러 개가 리턴된다면 컬럼 값으로 사용할 수 없기 때문에 오류이다.
 *    또한 컬럼 개수도 한 개여야 한다.
 */
select 
    la.lano, 
    select titl from lect where lno=la.lno as lect_title, 
    la.mno, 
    la.rdt
from lect_appl la;

/* 3단계 : 서브 쿼리를 이용하여 학생명을 가져오기 */
select 
    la.lano, 
    (select titl from lect where lno=la.lno) as lect_title, 
    (select name from memb where mno=la.mno) as stud_name,
    la.rdt
from lect_appl la;

(2) from 절에 서브쿼리 사용하기

/* 0단계 : 강의 정보를 가져온다. */
select
    l.lno,
    l.titl,
    l.rno,
    l.mno
from lect l;

/* 1단계 : 강의 상세 정보를 가져오는 select를 준비한다.
 * => 서브 쿼리를 이용하여 강의실 이름과 매니저 이름, 직위 정보를 가져오기 */
select 
    l.lno, 
    l.titl, 
    (select name from room where rno=l.rno) as room_name, 
    (select name from memb where mno=l.mno) as manager_name,
    (select posi from mgr where mno=l.mno) as manager_posi
from lect l;

/* 2단계: 위에서 준비한 select 결과를 가상 테이블로 사용하여 
         기존의 lect_appl 테이블과 조인한다. */
select 
    la.lano, 
    /*(select titl from lect where lno=la.lno) as lect_title,*/ 
    (select name from memb where mno=la.mno) as stud_name,
    lec.titl,
    lec.room_name,
    lec.manager_name,
    lec.manager_posi
from lect_appl la 
    join (select /* 가상 테이블 */
              l.lno, 
              l.titl, 
              (select name from room where rno=l.rno) as room_name, 
              (select name from memb where mno=l.mno) as manager_name,
              (select posi from mgr where mno=l.mno) as manager_posi
          from lect l) as lec on la.lno=lec.lno;
  • from 절에서 반복적으로 사용하는 서브쿼리가 있다면, 가상 테이블인 view로 정의해놓고 사용하는 것이 편하다.
/* from 절에서 반복적으로 사용하는 서브 쿼리를 가상 테이블인 view로 정의하기 */ 
create view lect2 as
select 
    l.lno, 
    l.titl, 
    (select name from room where rno=l.rno) as room_name, 
    l.mno as manager_no,
    (select name from memb where mno=l.mno) as manager_name,
    (select posi from mgr where mno=l.mno) as manager_posi
from lect l;

/* 위의 질의문을 view를 사용하여 다시 작성해보자! */
select 
    la.lano, 
    (select name from memb where mno=la.mno) as stud_name,
    lec.titl,
    lec.room_name,
    lec.manager_name,
    lec.manager_posi
from lect_appl la 
    join lect2 lec on la.lno=lec.lno;

(3) where 절에 서브쿼리 사용하기

  • where ~ in ~
  • where xxx in (‘aaa’, ‘bbb’) : xxx가 ‘aaa’이거나 ‘bbb’인 경우
/* 과장 또는 대리 매니저가 담당하고 있는 수강 신청만 추출하기 */
select 
    la.lano, 
    /* (select titl from lect where lno=la.lno) as lect_title, */ 
    (select name from memb where mno=la.mno) as stud_name,
    lec.titl,
    lec.room_name,
    /* lec.manager_no, */
    lec.manager_name,
    lec.manager_posi
from lect_appl la 
    join lect2 as lec on la.lno=lec.lno 
where
    lec.manager_no in (select mno from mgr where posi in ('과장', '주임'));

태그:

카테고리:

업데이트:

참고

2022-02-22 TIL(at work)

업데이트:
5 분 소요

업무 배경 이 업무가 할당된 이유 회사에서 맡고 있는 서비스 중, 회원이 구매를 신청하면 정부24에서 그 컨텐츠를 구매하여 제공을 하는 서비스가 있다. (참고로, 구매를 한다고 바로 완료되는게 아니라 구매 로직을 태운 후 실제로 컨텐츠를 가져오기까지 어느정도 시간이 소요된다....