[sql] order by, operators, union, join, concat, alias, distinct, limit

svenskpotatis·2023년 9월 27일

실습할 테이블 생성

CREATE TABLE celab
(
	ID int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    NAME varchar(32) NOT NULL DEFAULT '', 
    BIRTHDAY date, 
    AGE int, 
    SEX char(1),
    JOB_TITLE varchar(32),
    AGENCY varchar(32)
);

INSERT INTO celab VALUES (1, '아이유', 1993-05-16', 29, 'F', '가수, 텔런트', 'EDAM엔터테이먼트');
...

📌 ORDER BY

ORDER BY 문법
SELECT 문에서 데이터를 특정 컬럼을 기준으로 오름차순 혹은 내림차순 정렬하여 조회
- ASC: 오름차순, DESC: 내림차순

SELECT column1, column2, ...
FROM tablename
ORDER BY column1, column2, ... ASC | DESC;

ORDER BY 예제
celab table에서 이름과 나이를 나이순으로 조회

SELECT age, name
FROM celab
ORDER BY age ASC;

celab table에서 이름과 나이를 나이의 역순으로 정렬, 이름순으로 정렬

SELECT age, name
FROM celab
ORDER BY age DESC, name ASC;

📌 Comparison Operators(비교 연산자)

# 나이가 29세인 데이터
SELECT name, age FROM celab WHERE age=29 ORDER BY age;

# 나이가 29세가 아닌 데이터
SELECT name, age FROM celab WHERE age!=29 ORDER BY age;

# 나이가 29세보다 큰 데이터
SELECT name, age FROM celab WHERE age>29 ORDER BY age;

# 나이가 29보다 크거나 작은 (같지 않은) 데이터
SELECT name, age FROM celab WHERE age<>29 ORDER BY age;

📌 Logical Operators

📖 AND

AND 문법
: 조건을 모두 만족하는 경우 TRUE

SELECT column1, column2, ...
FROM tablename
WHERE condition1 AND condition2 AND condition3 ...;

AND 예제 - 성별이 남자이고 나이가 40보다 큰 데이터를 이름의 역순으로 정렬

SELECT * FROM celab WHERE sex='M' AND age<40 ORDER BY name;

📖 OR

OR 문법
: 하나의 조건이라도 만족하는 경우 TRUE

SELECT column1, column2, ...
FROM tablename
WHERE condition1 OR condition2 OR condition3 ...;

예제1 - YG 소속이거나 나무엑터스 소속인 연예인 중, 나이가 30세보다 작은 데이터

SELECT * FROM celab WHERE (agency='YG엔터테이먼트' OR agency='나무엑터스') AND age<30;

예제2 - celab table에서 아이디가 홀수이면서 성별이 여자거나, 아이디가 짝수면서 소속사가 YG 인 데이터를 나이순으로 정렬

📖 NOT

NOT 문법
: 조건을 만족하지 않는 경우 TRUE

SELECT column1, column2, ...
FROM tablename
WHERE NOT condition;

예제1 - 소속사가 YG이면서 남자가 아니거나, 직업이 가수이면서 소속사가 YG가 아닌 데이터

SELECT * FROM celab
WHERE (agency='YG엔터테이먼트' AND NOT sex='M')
	OR (job_title='가수' AND NOT agency='YG엔터테이먼트');

예제2 - 생일이 1990년 이후이면서 여자가 아니거나, 생일이 1979년 이전이면서 소속사가 안테나가 아닌 데이터

SELECT * FROM celab
WHERE (birthday>19891231 AND NOT sex='F')
	OR (birthday<19800101 AND NOT agency='안테나');

📖 BETWEEN

BETWEEN 문법
: 조건값이 범위 사이에 있으면 TRUE

SELECT column1, column2, ...
FROM tablename
WHERE column1 BETWEEN value1 AND value2;

예제1 - 생년원일이 1980년에서 1995년 사이가 아니면서 여자이거나, 소속사가 YG이면서 나이가 20세에서 45세 사이가 아닌 데이터

SELECT * FROM celab
WHERE (NOT birthday BETWEEN 19800101 AND 19951231 AND sex='F')
	OR (agency='YG엔터테이먼트' AND NOT age BETWEEN 20 AND 45);

📖 IN

IN 문법
: 목록 안에 조건이 존재하는 경우 TRUE

SELECT column1, column2, ...
FROM tablename
WHERE column IN (value1, value2, ...);

예제1 - 나이가 28세, 48세 중 하나인 데이터 검색

SELECT * FROM celab WHERE age IN (28, 48);

예제2 - 소속사가 나무엑터스, 안테나, 울림엔터가 아니면서, 성별이 여자이거나 나이가 45세 이상

SELECT * FROM celab
WHERE NOT agency IN ('나무엑터스', '안테나', '울림엔터테이먼트') AND (sex='F' OR age>=45);

📖 LIKE

LIKE 문법
: 조건값이 패턴에 맞으면 TRUE

SELECT column1, column2, ...
FROM tablename
WHERE column LIKE pattern;

예제1 - 'YG'로 시작하는 소속사 이름을 가진 데이터

SELECT * FROM celab WHERE agency LIKE 'YG%';

예제1 - 직업명에 '가수'가 포함된 데이터

SELECT * FROM celab WHERE job_title LIKE '%가수%';

예제3 - 소속사 이름의 두 번째 글자가 G인 데이터

SELECT * FROM celab WHERE agency LIKE '_G%';

📌 UNION

여러 개의 SQL문을 합쳐서 하나의 SQL 문으로 만들어주는 방법(칼럼의 개수가 같아야 함)

UNION: 중복된 값을 제거하여 알려줌
UNION ALL: 중복된 값을 모두 보여줌

SELECT column1, column2, ... FROM tableA
UNION | UNION ALL
SELECT column1, column2, ... FROM tableB

예제1 - test1 의 모든 데이터와 test2의 모든 데이터를 중복된 값을 포함하여 검색

SELECT * FROM test1
UNION ALL
SELECT * FROM test2

예제2 - test1 의 모든 데이터와 test2의 모든 데이터를 중복된 값을 제거하여 검색

SELECT * FROM test1
UNION
SELECT * FROM test2

📌 JOIN

새로운 테이블

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
);

정보 추가
JOIN: 두 개 이상의 테이블을 결합하는 것

📖 INNER JOIN

INNER JOIN 문법

SELECT column1, column2, ...
FROM tableA
INNER JOIN tableB
ON tableA.column = tableB.column
WHERE condition;

예제1 - snl_show에 호스트로 출연한 celab을 기준으로 celab 테이블과 snl_show 테이블을 INNER JOIN

SELECT celab.id, celab.name, snl_show.id, snl_show.host
FROM celab
INNER JOIN snl_show
ON celab.name = snl_show.host;

📖 LEFT JOIN

LEFT JOIN 문법

SELECT column1, column2, ...
FROM tableA
LEFT JOIN tableB
ON tableA.column = tableB.column
WHERE condition;

📖 RIGHT JOIN

RIGHT JOIN 문법

SELECT column1, column2, ...
FROM tableA
RIGHT JOIN tableB
ON tableA.column = tableB.column
WHERE condition;

📖 FULL OUTER JOIN(SQL)

FULL OUTER JOIN 문법(SQL)

SELECT column1, column2, ...
FROM tableA
FULL OUTER JOIN tableB
ON tableA.column = tableB.column
WHERE condition;

📖 SELF JOIN

SELF JOIN 문법

SELECT column1, column2, ...
FROM tableA, tableB, ...
WHERE condition;

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

SELECT snl_show.id, snl_show.season, snl_show.episode, celab.name, celab.job_title
FROM celab, snl_show
WHERE celab.name = snl_show.host;

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

SELECT name, season, episode, broadcast_date, agency
FROM celab, snl_show
WHERE name=host
	AND (episode IN (7, 9, 10) OR agency LIKE 'YG______')
    AND broadcast_date>'2020-09-15';

📌 CONCAT, ALIAS, DISTINCT, LIMIT

📖 CONCAT

CONCAT: 여러 문자열을 하나로 합치거나 연결

CONCAT 문법

SELECT CONCAT('string1', 'string2', ...);

예제1

SELECT CONCAT('이름:', name) FROM celab;

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

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

📖 ALIAS

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

ALIAS 문법1 - column

SELECT column AS alias
FROM tablename;

ALIAS 문법2 - table

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

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

SELECT name AS '이름', agency AS '소속사' FROM celab;

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

SELECT CONCAT(name, ' : ', job_title) AS protile FROM celab;

예제3 - snl_korea에 출연한 celab을 기준으로 두 table을 join하여 검색 (as는 생략가능)
- 시즌, 에피소드, 방송일을 합쳐서 '방송정보'
- 이름, 직업을 합쳐서 '출연자정보'

SELECT CONCAT(s.season, '-', s.episode, '(', s.broadcast_date, ')') AS '방송정보',
	CONCAT(c.name, '(', c.job_title, ')') AS '출연자정보'
FROM celab AS c, snl_show AS s
WHERE c.name=s.host;

📖 DISTINCT

DISTINCT: 검색한 결과의 중복 제거

DISTINCT 문법

SELECT DISTINCT column1, column2, ...
FROM tablename;

📖 LIMIT

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

LIMIT 문법

SELECT column1, column2, ...
FROM tablename
WHERE condition
LIMIT number;

예제1 - 나이가 가장 적은 연예인 4명 검색

SELECT * FROM celab ORDER BY age LIMIT 4;

svenskpotatis

이전 포스트

[sql] sql 기초, 권한 관리, update, delete 등

다음 포스트