SQL10-14. UNION, JOIN, CONCAT, ALIAS, DISTINCT, LIMIT

hh_binvely·2023년 3월 19일
JOINmysqlunion아자아자
0

5주차_SQL

목록 보기
6/6
post-thumbnail

UNION

실습환경 만들기

  • 테스트용 테이블 생성

    CREATE TABLE test1
    (
    no int
    );
    CREATE TABLE test2
    (
    no int
    );

  • 테스트 데이터 추가

    INSERT INTO test1 VALUES (1);
    INSERT INTO test1 VALUES (2);
    INSERT INTO test1 VALUES (3);
    INSERT INTO test2 VALUES (5);
    INSERT INTO test2 VALUES (6);
    INSERT INTO test2 VALUES (3);

  • 테스트 데이터 확인

    select from test1;
    select     from test2;

UNION

  • 여러 개의 SQL문을 합쳐서 하나의 SQL문으로 만들어주는 방법
    (단! 컬럼의 개수가 같아야한다.)
  • UNION 문법
    * UNION: 중복된 값을 제거하여 알려준다.
    • UNION ALL: 중복된 값도 모두 보여준다.

      SELECT col1, col2, ... FROM tableA
      UNION | UNION ALL
      SELECT col1, col2, ... FROM tableB;

  • 예제1
    * test1의 모든 데이터와 test2의 모든 데이터를 중복된 값을 포함하여 검색
    	> select * from test1
    UNION ALL
    select * from test2;
  • 예제2
    * test1의 모든 데이터와 test2의 모든 데이터를 중복된 값을 제거하여 검색

    select from test1
    UNION
    select     from test2;

  • 예제3
    * 성별이 여자인 데이터를 검색하는 쿼리와 소속사가 YG 엔터테이먼트인 데이터를 검색하는 쿼리를 UNION ALL로 실행

    select from celeb where sex='F'
    UNION ALL
    select     from celeb where agency='YG엔터테이먼트';

  • 예제3-1 => ERROR 발생 (컬럼 개수가 달라서!)
    * 가수가 직업인 연예인 이름, 직업을 검색하는 쿼리와
    1980년대 태어난 연예인의 이름, 생년월일, 나이를 검색하는 쿼리를 UNION으로 실행

    SELECT name, job_title FROM celeb WHERE job_title LIKE '%가수%'
    UNION
    SELECT name, birthday, age FROM celeb WHERE birthday BETWEEN '1980-01-01' AND '1989-12-31';

  • 예제4
    * 직업이 가수인 데이터를 검색하는 쿼리와 직업이 탤런트인 데이터를 검색하는 쿼리를 중복 제거하여 검색

    select from celeb where job_title LIKE '%가수%'
    UNION
    select     from celeb where job_title LIKE '%탤런트%';

  • 예제5
    * 성이 이씨인 데이터를 검색하는 쿼리와 1970년대생을 검색하는 쿼리를 중복 포함하여 실행

    select from celeb where name LIKE '이%'
    UNION ALL
    select     from celeb where birthday BETWEEN '1970-01-01' AND '1980-12-31';

JOIN

실습 환경

  • zerobase 사용(이동)

    use zerobase;
    select * from celeb;

  • 새로운 테이블 생성 -- TV Show에 대한 테이블

    CREATE TABLE snl_show
    (
    ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    SEASON int NOT NULL,
    EPISODE int NOT NULL,
    BROADCAST_DATE date,
    HOST varchar(32) NOT NULL
    );

  • 테이블 정보 확인

    desc snl_show;

  • 데이터 추가

    INSERT INTO snl_show VALUES (1, 8, 7, '2020-09-05', '강동원');
    INSERT INTO snl_show VALUES (2, 8, 8, '2020-09-12', '유재석');
    INSERT INTO snl_show VALUES (3, 8, 9, '2020-09-19', '차승원');
    INSERT INTO snl_show VALUES (4, 8, 10, '2020-09-26', '이수현');
    INSERT INTO snl_show VALUES (5, 9, 1, '2020-09-04', '이병헌');
    INSERT INTO snl_show VALUES (6, 9, 2, '2020-09-11', '하지원');
    INSERT INTO snl_show VALUES (7, 9, 3, '2020-09-18', '제시');
    INSERT INTO snl_show VALUES (8, 9, 4, '2020-09-25', '조정석');
    INSERT INTO snl_show VALUES (9, 9, 5, '2020-10-02', '조여정');
    INSERT INTO snl_show VALUES (10, 9, 6, '2020-10-09', '옥주현');

JOIN

  • 두 개 이상의 테이블을 결합하는 것

  • INNER JOIN
    * 두 개의 테이블에서 공통된 요소들을 통해 결합하는 조인방식

    select col1, col2, ...
    from tableA
    INNER JOIN tableB
    ON tableA.column = tableB.column
    WHERE condition;

    • 예제
      * snl_show에 호스트로 출연한 celeb을 기준으로 celeb 테이블과 snl_show 테이블을 INNER JOIN
      select celeb.id, celeb.name, snl_show.id, snl_show.host
      from celeb
      INNER JOIN snl_show
      ON celeb.name=snl_show.host;

  • LEFT JOIN
    * 두 개의 테이블에서 공통영역을 포함하고 왼쪽 테이블의 다른 데이터롤 포함하는 조인방식

    select col1, col2, ...
    from tableA #얘가 LEFT table
    LEFT JOIN tableB # RIGHT table
    ON tableA.column = tableB.column
    WHERE condition;

    • 예제
      select celeb.id, celeb.name, snl_show.id, snl_show.host
      from celeb
      LEFT JOIN snl_show
      ON celeb.name=snl_show.host;

  • RIGHT JOIN
    * 두 개의 테이블에서 공통영역을 포함하고 오른쪽 테이블의 다른 데이터롤 포함하는 조인방식

    select col1, col2, ...
    from tableA #얘가 LEFT table
    RIGHT JOIN tableB # RIGHT table
    ON tableA.column = tableB.column
    WHERE condition;

    • 예제
      select celeb.id, celeb.name, snl_show.id, snl_show.host
      from celeb
      RIGHT JOIN snl_show
      ON celeb.name=snl_show.host;

  • FULL OUTER JOIN
    * 두 개의 테이블에서 공통영역을 포함하여 양쪽 테이블의 다른영역을 모두 포함하는 조인방식

    select col1, col2, ...
    from tableA #얘가 LEFT table
    FULL OUTER JOIN tableB # RIGHT table
    ON tableA.column = tableB.column
    WHERE condition;

    • 예제 => ERROR 발생! 문법 오류
      * MySQL에서는 FULL OUTER JOIN 문법 지원하지 X
      select celeb.id, celeb.name, snl_show.id, snl_show.host
      from celeb
      FULL OUTER JOIN snl_show
      ON celeb.name=snl_show.host;
    • 대체 문법
      select col1, col2, ...
      from tableA #얘가 LEFT table
      LEFT JOIN tableB ON tableA.column = tableB.column
      UNION
      select col1, col2, ...
      from tableA #얘가 LEFT table
      RIGHT JOIN tableB ON tableA.column = tableB.column
      WHERE condition;
      • 예제
        select celeb.id, celeb.name, snl_show.id, snl_show.host
        from celeb
        LEFT JOIN snl_show ON celeb.name=snl_show.host
        UNION
        select celeb.id, celeb.name, snl_show.id, snl_show.host
        from celeb
        RIGHT JOIN snl_show ON celeb.name=snl_show.host;

  • SELF JOIN
    * 가장 많이 사용

    • INNER JOIN과 같은 결과 출력

      select col1, col2, ...
      from tableA, tableB, ... # Table이 여러 개 명시
      where condition;

    • 예제
      * snl_show에 호스트로 출연한 celeb을 기준으로 celeb테이블과 snl_show 테이블을 SELF JOIN

      SELECT celeb.id, celeb.name, snl_show.id, snl_show.host
      FROM celeb, snl_show
      WHERE celeb.name = snl_show.host; # JOIN 기준 제시

    • 예제2
      • celeb 테이블의 연예인 중, snl_show에 host로 출연했고 소속사가 안테나인 사람의 이름과 직업 검색

        select celeb.name, celeb.job_title
        from celeb, snl_show
        where celeb.name=snl_show.host AND celeb.agency='안테나';

        * 예제3 => 괄호의 중요성

      • cele 테이블의 연예인중, snl_show 에 host로 출연했고, 영화배우는 아니면서 YG엔터테이먼트 소속이거나 40세 이상이면서 YG 엔터테이먼트 소속이 아닌 연예인 이름과 나이, 직업, 소속사, 시즌, 에피소드 정보를 검색

        select celeb.name, celeb.age, celeb.job_title, celeb.agency, snl_show.season, snl_show.episode
        from celeb, snl_show
        where celeb.name=snl_show.host AND ((NOT celeb.job_title LIKE'%영화배우%' AND celeb.agency='YG엔터테이먼트') OR (celeb.age>=40 AND celeb.agency!='YG엔터테이먼트'));

        * 예제4

      • snl_show에 출연한 연예인의 snl_show 아이디, 시즌, 에피소드, 이름, 직업 정보를 검색

        select snl_show.id, snl_show.season, snl_show.episode, snl_show.host, celeb.job_title
        from celeb, snl_show
        where celeb.name=snl_show.host;

    • 예제5
      • snl_show에 출연한 celeb 중, 에피소드 7,9,10 중에 출연했거나 소속사가 YG로 시작하고 뒤에 6글자로 끝나는 사람 중 작년 9월15일 이후 출연했던 사람 검색

        select snl_show.id, snl_show.broadcast_date, snl_show.episode, snl_show.host
        from celeb, snl_show
        where celeb.name=snl_show.host AND ((snl_show.episode IN (7,9,10) OR celeb.agency LIKE 'YG__') AND (snl_show.broadcast_date>'2020-09-15'));

문제로 익숙해지기

  1. snl_show에 출연한 celeb 테이블 연예인 중, 영화배우나 탤런트가 아닌 연예인의 아이디, 이름, 직업, 시즌, 에피소드 정보 검색

    select snl_show.id, snl_show.host, celeb.job_title, snl_show.season, snl_show.episode
    from celeb, snl_show
    where celeb.name=snl_show.host AND NOT (celeb.job_title LIKE '%배우%' OR celeb.job_title LIKE '%연예인%');

  2. snl_show에 출연한 celeb 중, 작년 9월 15일 이후에 출연했거나 소속사 이름이 '엔터테이먼트'로 끝나지 않으면서 영화배우나 개그맨이 아닌 연예인의 celeb 아이디, 이름, 직업, 소속사를 검색

    select celeb.id, celeb.name, celeb.job_title, celeb.agency
    from celeb, snl_show
    where celeb.name=snl_show.host AND (snl_show.broadcast_date>'2020-09-15' OR NOT celeb.agency LIKE '%엔터테이먼트') AND NOT (celeb.job_title LIKE'%배우%' OR celeb.job_title LIKE'%개그맨%');

CONCAT, ALIAS

실습 환경

  • zerobase 이용

    use zerobase

  • 데이터 확인

    select from celeb;
    select     from snl_show;

concat

  • 여러 문자열을 하나로 합치거나 연결해주는 함수

    select concat('str1','str2', ..);

  • 예제

    select concat('concat',' ','test');

  • 예제2

    select concat('이름: ', name) from celeb;

alias

  • 칼럼이나 테이블 이름에 별칭 생성

    select column as alias
    from tablename;

  • 문법1) Table 별칭 생성
    (as 생략도 가능)
    	> select col1, col2, ...
    from tablename as alias;
    • 예제
      • name을 이름으로 별칭을 만들어서 검색

        select name as '이름' from celeb;

        * 예제2

      • name은 이름으로, agency는 소속사로 별칭을 만들어서 검색

        select name as '이름' , agency as '소속사' from celeb;

    • 예제3
      • name과 job_title을 합쳐서 profile이라는 별칭을 만들어서 검색

        select concat(name,': ', job_title) as profile from celeb;

    • 예제4
      • snl_show에 출연한 celeb을 기분으로 두 테이블을 조인하여,
        celeb 테이블은 c, snl_show 테이블은 s 라는 별칭을 만들어 출연한 시즌, 에피소드, 이름, 직업 검색

        select s.season, s.episode, c.name, c.job_title
        from celeb as c, snl_show as s
        where c.name=s.host;

    • 예제5
      • snl_show 에 출연한 celeb을 기준으로 두 테이블을 조인하여 다음과 같이 각 데이터의 별칭을 사용하여 검색 ( 시즌, 에피소드, 방송일을 합쳥서 '방송정보' / 이름, 직업을 합쳐서 '출연자정보'

        select concat(s.season,'-', s.episode,'(', s.broadcast_date,')') as '방송정보' ,
        concat(c.name,'(',c.job_title,')') as '출연자정보'
        from celeb as c, snl_show as s
        where c.name=s.host;

DISTINCT

  • 검색한 결과의 중복 제거

    select distinct col1, co2, ...
    from tablename;

  • 예제1
    * 연예인 소속사 종류를 검색 - 중복 제외

    select distinct agency from celeb;

  • 예제2
    * 가수 중에서 성별과 직업별 종류를 검색 - 중복 제외

    select distinct sex, job_title from celeb where job_title LIKE '%가수%';

LIMIT

  • 검색결과를 정렬된 순으로 주어진 숫자만큼만 조회

    select col, co2, ...
    from table
    where condition
    LIMIT number;

  • 예제1
    * celeb 데이터 3개만 가져오기

    select * from celeb LIMIT 3;

  • 예제2
    * 나이가 가장 적은 연예인 4명 검색

    select name from celeb order by age LIMIT 4;

문제로 익숙해지기

문제1

  • 이름이 3글자인 연예인 정보를 검색하여 다윽뫄 같이 출력

    select concat('이름:',name, ', ', '소속사:',agency) as '연예인 정보' from celeb
    where name LIKE '___';

문제2

  • 앞글자가 2글자이고, '엔터테이먼트'로 끝나는 소속사 연예인 중
    SNL에 출연한 연예인의 신상정보(나이, 성별)와 출연정보(시즌-에피소드, 방송날짜), 소속사 정보를
    방송날짜 최신순으로 정렬하여 다음과 같이 검색

    select celeb.agency as '소속사 정보', concat('나이: ',celeb.age,'(',celeb.sex,')') as '신상정보', concat(snl_show.season,'-',snl_show.episode,',', '방송날짜:',snl_show.broadcast_date) as '출연정보' from celeb, snl_show where celeb.name=snl_show.host AND celeb.agency LIKE '__엔터테이먼트' order by snl_show.broadcast_date desc;

profile
hh_binvely
:D
이전 포스트

SQL7-9. Logical Operations

0개의 댓글