SQL - CONCAT, ALIAS, DISTINCT, LIMIT : 데이터 취업 스쿨 스터디 노트 12/21

1. CONCAT

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

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

2. ALIAS

테이블이나 컬럼 이름에 별칭을 생성할 때

SELECT column1, column2, ...
FROM tablename as alias;

SELECT column as alias
FROM tablename;

select name as '이름' from celeb;

# 컬럼명 자체가 변경되지는 않는다.
select name from celeb;

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

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

>>>
+--------------------+
| profile            |
+--------------------+
| iu: singer, talent |
| lmj: singer        |
| sk: talent         |
| kdw: actor, talent |
| yjs: MC, comedian  |
| csw: actor, model  |
| lsh: singer        |
+--------------------+

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

>>>
+--------+---------+------+---------------+
| season | episode | name | job_title     |
+--------+---------+------+---------------+
|      8 |       7 | kdw  | actor, talent |
|      8 |       8 | yjs  | MC, comedian  |
|      8 |       9 | csw  | actor, model  |
|      8 |      10 | lsh  | singer        |
+--------+---------+------+---------------+

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;

>>>
+------------------+--------------------+
| 방송정보      	   | 출연자정보	        |
+------------------+--------------------+
| 8-7(2020-09-05)  | kdw(actor, talent) |
| 8-8(2020-09-12)  | yjs(MC, comedian)  |
| 8-9(2020-09-19)  | csw(actor, model)  |
| 8-10(2020-09-26) | lsh(singer)        |
+------------------+--------------------+

연습 문제

select concat('name: ', name, ' , ', 'agency: ', agency) as 'celebrity'
    -> from celeb
    -> where name like '___';

select c.agency as '소속사 정보',
	   concat('나이: ', c.age, '(', c.sex, ')') as '신상 정보',
	   concat(s.season, '-', s.episode, ', 방송날짜: ', s.broadcast_date) as '출연 정보'
from celeb as c, snl_show as s
where c.name = s.host
and c.agency='YG'
order by s.broadcast_date desc;
    
>>>
+------------------+---------------+------------------------------+
| 소속사 정보      | 신상 정보     | 출연 정보                     	  |
+------------------+---------------+------------------------------+
| YG               | 나이: 23(F)   | 8-10, 방송날짜: 2020-09-26     |
| YG               | 나이: 48(M)   | 8-9, 방송날짜: 2020-09-19      |
| YG               | 나이: 41(M)   | 8-7, 방송날짜: 2020-09-05      |
+------------------+---------------+------------------------------+

3. DISTINCT

검색 결과의 중복을 제거할 때

select distinct agency from celeb;

select distinct sex, job_title from celeb where job_title like '%singer%';

# 성별과 소속사별 종류를 검색하여 성별, 소속사 순으로 정렬
select distinct sex, agency from celeb order by sex, agency;

>>>
+------+---------+
| sex  | agency  |
+------+---------+
| F    | EDAM    |
| F    | woollim |
| F    | YG      |
| M    | antenna |
| M    | namu    |
| M    | YG      |
+------+---------+

4. LIMIT

검색 결과를 정렬된 순서대로, 주어진 숫자만큼 조회할 때

select * from celeb limit 3;

# 나이가 가장 적은 연예인 4명
select * from celeb order by age limit 4;

select concat('SNL 시즌 ', s.season, ' 에피소드 ', s.episode, ' 호스트 ', s.host) 'SNL 방송정 보',
    -> c.age
    -> from celeb as c, snl_show as s
    -> where c.name = s.host
    -> order by c.age desc
    -> limit 2;

>>>
+-------------------------------------------+------+
| SNL 방송정보                           	| age  |
+-------------------------------------------+------+
| SNL 시즌 8 에피소드 8 호스트 yjs            	|   50 |
| SNL 시즌 8 에피소드 9 호스트 csw            	|   48 |
+-------------------------------------------+------+