[MySQL & PHP] 8장 다양한 조건으로 데이터 추출하기

정리

1. 검색 조건 설정하기

1. 칼럼 표시 방법을 고려해서 SELECT명령 실행하기

1.1 테이블 tb 준비하기

지금부터는 매출정보 테이블 tb를 사용해서 SELECT를 연습해 보겠습니다. 테이블 tb는 다음과 같이 사원별 매출과 매출 월이 저장된 테이블입니다.

아래 링크를 통해 미리 만들어 둡니다.

http://recoveryman.tistory.com/170

► 테이블 tb의 내용

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

테이블 tb는 사원번호(number)와 매출(sales), 매출 월(month)의 3개 칼럼으로 구성되어 있습니다. 테이블 tb에서 칼럼 number가 'A101'인 사원의 이름은 '강신우'란 것을 사원정보 테이블 tb1을 통해 알 수 있습니다.

여기에서는 MySQL콘솔창을 실행해서 USE db1으로 설정된 상태라는 가정하에 설명하겠습니다.

1.2 칼럼의 순서를 바꿔서 표시하기

SELECT 명령으로 레코드를 추출할 때 지금까지는 SELECT * FROM tb;라는 명령을 사용했습니다. 이 명령은 '테이블 tb에서(FROM tb) 모든 칼럼(*)을 선택하시오(SELECT).'라는 의미 입니다. 별표(*)는 와일드카드로 '모든 문자'라는 의미입니다.

칼럼 이름을 지정하면 지정한 순서대로 표시됩니다. 여러 개의 칼럼을 지정할 때에는 반점(,)으로 칼럼을 구분했습니다.

매출정보 테이블 tb의 내용을 매출(sales), 사원번호(number) 순서로 표시해 보겠습니다.

다음 명령을 실행한다.

SELECT sales,number FROM tb;

실행 결과

같은 칼럼을 몇 번이고 표시할 수도 있습니다. 예를 들어, SELECT number, sales, number, sales, number, sales FROM tb1;이라고 실행하면 number와 sales가 3번씩 표시됩니다.

1.3 별명 사용하기

칼럼 이름에 number나 sales만 표시하면, 일반인들은 어떤 데이터를 표시한 것인지 잘 모를 수 있습니다. 칼럼의 성격을 알기 쉽도록 칼럼에 별명을 붙일 수가 있습니다.

이 별명을 에일리어스(alias)라고도 합니다. 에일리어스는 '가명' 또는 '별명'이라는 의미로, 컴퓨터 세계에서는 '본체를 참조하기 위해 사용자가 자유롭게 붙인 이름'을 의미합니다.

예를 들어 SELECT * FROM tb;를 실행하면 칼럼 이름은 number와 sales, month라고 있는 그대로 표시됩니다. 이것을 알기 쉽게 별명으로 바꿀 수가 있습니다.

칼럼 이름을 별명으로 바꾸기

SELECT 칼럼_이름 AS 별명 FROM 테이블 이름;

여러 개의 칼럼을 지정할 때에는 반점(,)으로 구분하고, '칼럼_이름 AS 별명'이라고 기술합니다. 별명에 특수 문자를 사용할 때에는 별명을 큰따옴표(")로 감쌉니다.

그러면 칼럼 number에는 '사원번호', sales에는 '매출'이라는 별명을 붙여서 테이블 tb의 모든 레코드를 표시해 보겠습니다.

다음 명령을 실행한다.

SELECT number AS 사원번호, sales AS 매출 FROM tb;

실행 결과

별명을 사용하기 전보다 훨씬 보기 편해졌습니다. 표시된 문자열을 복사해서 문서에 바로 사용할 수도 있습니다.

2. 계산하거나 문자열을 결합해서 표시하기

2.1 칼럼의 값을 계산해서 표시하기

칼럼의 데이터를 사용해서 × 나 ÷ 등의 사칙연산을 할 수 있습니다. 단, 컴퓨터 세계에서는 × 나 ÷ 기호를 그대로 사용하지 않고 다음과 같은 사칙연산 기호를 사용합니다.

사칙연산 기호

연산기호	연산자
곱하기 (×)	*
나누기 (÷)	/
더하기 (+)	+
빼기 (-)	-

매출정보 테이블 tb의 칼럼 sales의 값을 '만원' 단위로 가정 합니다. 예를 들어, 'A101'의 강신우는 5월에 300만 원의 매출이 있었던 것이 됩니다.(다음 표의 6번째 데이터).

칼럼 sales의 값을 10,000배로 하고, 별명을 '매출'로 설정하고 나서 모든 레코드를 표시해 보겠습니다.

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

⬇︎

매출
1010000
540000
1810000
1840000
170000
3000000
2050000
930000
120000
870000

다음 명령을 실행합니다.

SELECT sales * 10000 AS 매출 FROM tb;

만약, 매출(원)과 같이 괄호()를 입력하고자 할 때에는 "매출(원)"처럼 큰 따옴표(")로 감싸서 입력하면 오류가 발생하니 주의해야 합니다.

이처럼 숫자를 곱하거나 나누는 것뿐만 아니라, 칼럼의 값들을 이용해서 계산할 수도 있습니다. 다음은 '칼럼 a의 값을 칼럼 b의 값으로 나누기'를 하는 명령문의 예입니다.

SELECT a/b FROM 테이블_이름;

마찬가지로 더하기를 할 때에는 다음과 같은 명령문을 실행합니다.

SELECT a+b FROM 테이블_이름;

물론, 같은 레코드에 있는 칼럼 a와 b의 값끼리 계산됩니다.

2. 함수 이용하기

2.2 함수를 사용해서 계산하기

함수란 주어진 데이터로 정해진 처리를 하고 나서 그 결과를 돌려주는 것입니다. 예를 들어, AVG()라는 함수는 주어진 데이터의 평균값을 계산해 줍니다. 함수 이름의 뒤에 괄호()를 붙히고서, 이 괄호 안에 처리할 데이터를 입력합니다. 괄호에 들어갈 데이터를 인수라고 합니다.

Excel에서 표 계산을 할 때 사용하는 함수와 비슷하지만, SQL에서 괄호 안에 지정하는 값은 대부분 칼럼 이름입니다. 예를 들어, AVG(X)는 칼럼 x의 평균값을 반환합니다. 칼럼 x에는 레코드 숫자만큼 값이 존재하기 때문에, '칼럼 x 에 있는 모든 레코드의 평균'을 구하는 것이 됩니다.

함수 처리의 대상이 되는 레코드를 한정할 때에는 WHERE 등으로 조건을 설정해서 데이터를 추출합니다. 또한, GROUP BY를 사용해서 그룹별로 계산할 수도 있습니다.

그러면 D 주식회사의 2013년 제2사분기 매출 1건당 평균을 계산해 보겠습니다. 즉, 데이터를 tb에서 칼럼 sales의 평균을 표시합니다.

► 실행할 내용

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

⬇︎ 실행 후

AVG(sales)

123,4000

다음 명령을 실행한다.

SELECT AVG(sales) FROM tb;

모든 매출(sales)의 평균이 123.4라는 것을 확인할 수 있습니다.

합계표시하기

다음은 SUM() 함수로 합계를 계산하는 예입니다.

SELECT SUM(sales) FROM tb;

합계는 1,234입니다.

개수 표시하기

다음은 COUNT() 함수로 데이터의 개수를 계산하는 예입니다.

SELECT COUNT(sales) FROM tb;

총 건수는 10입니다. 합계가 1,234이고 건수가 10이므로, 평균값은 좀 전에 AVG(sales)로 계산한 값과 같다는 것을 확인할 수 있습니다.

MySQL에는 이 외에도 많은 함수가 있습니다. 앞에서 소개한 평균과 합계, 개수 등 통계를 계산하는 함수뿐만 아니라, sin이나 cos과 같은 수학적 처리를 하는 함수도 있습니다. 또한, 문자열이나 날짜, 시간 등을 계산하는 함수도 있습니다. 이 책에서 소개하는 함수는 이렇게 수많은 함수 중 일부에 지나지 않습니다.

2.3 각종 정보를 표시하는 함수

테이블과는 전혀 상관없는 함수를 소개하겠습니다. PI()는 원주율을 계산하는 함수입니다. 다음 명령을 실행해 봅니다.

SELECT PI();

이처럼 SELECT에 함수를 사용하면 칼럼이나 테이블과는 전혀 상관없는 데이터도 표시할 수가 있습니다.

다음은 현재 사용하는 MySQL의 환경을 알아보는 함수의 예입니다.

MySQL 서버의 버전 표시하기

SELECT VERSION();

현재 사용하는 데이터베이스 표시하기

SELECT DATABASE();

현재 사용자 표시하기

SELECT USER();

인수로 설정한 문자의 문자 코드(문자 세트) 표시하기

SELECT CHARSET('이 문자');

함수 이름 뒤에 있는 괄호()는 인수를 설정하지 않을 때에도 입력해야 합니다. 생략하면 오류가 발생합니다. 함수에 인수 값이 필요 없어도 반드시 괄호를 붙이는 것은 프로그래밍 언어에서는 공통된 내용입니다.

원의 면적 계산하기

예를 들어, 반지를이 입력된 칼럼 r의 값을 이용해서 원의 면적을 계산하려면 다음 명령을 실행합니다.

SELECT r*r*PI() FROM ~

2.4 문자열 결합하기

MySQL에는 문자열을 처리하는 함수도 많습니다.

예를 들어, 문자열을 연결할 때는 CONCAT() 함수를 사용합니다. CONCAT() 함수에서는 문자열 칼럼을 반점(,)으로 구분해서 나열합니다. 칼럼 a와 b, c의 문자를 연결한다고 할 때, CONCAT(a, b, c)라고 기술합니다. 또한, 문자열을 직접 지정할 수도 있습니다.

3장에서 사용했는 사원정보 테이블 tb1의 칼럼 number에는 '사원번호'가 있고, 칼럼 name에는 '이름'이 있습니다. 이번에는 '사원번호 + 이름 + 님'의 형태로 문자열을 표시해 보겠습니다.

테이블 tb1에 있는 칼럼 number와 name에 '님'을 결합해서 표시합니다.

▶︎ 실행 전(테이블 tb1)

number	name	age
A101	강신우	40
A102	김기덕	28
A103	김민호	20
A104	문소리	23
A105	박문수	35

▶︎ 실행 후

CONCAT(number,name,'님')

A101강신우님

A102김기덕님

A103김민호님

A104문소리님

A105박문수님

다음 명령을 실행한다.

SELECT CONCAT(number,name,'님') FROM tb1;

실행 결과

물론, CONCAT() 함수는 3개 이상의 데이터에도 사용할 수 있습니다. 또한, '님'은 문자열 데이터이므로 작은 따옴표(')로 감싸야 합니다.

2.5 문자열을 조작하는 함수

문자열을 조작하는 함수 중에서 자주 쓰이는 함수를 몇가지 소개하겠습니다. 사원정보 테이블 tb1을 이용해서 설명합니다.

오른쪽 문자부터 추출하기 : RIGHT() 함수

다음 명령은 칼럼 number의 데이터를 오른쪽2문자만 표시합니다.

SELECT RIGHT(number,2) FROM tb1;

예를 들어, 칼럼 number가 A101인 경우, 01이 표시됩니다.

왼쪽 문자부터 추출하기 : LEFT()함수

다음 명령은 칼럼 number의 데이터를 왼쪽 2문자만 표시합니다.

SELECT LEFT (number,2) FROM tb1;

이때 추출되는 문자는 모두 A1입니다.

x번째부터 △개 추출하기 : SUBSTRING() 함수

다음 명령은 칼럼 number의 데이터를 2번째 문자부터 3개의 문자만 표시합니다.

SELECT SUBSTRING(number,2,3) FROM tb1;

반복해서 표시하기 : REPEAT() 함수

다음 명령은 문자 온점(.)을 칼럼 age에 있는 숫자만큼 반복해서 표시합니다.

SELECT REPEAT ('.',age) FROM tb1;

이 함수를 이용하면 D주식회사 사원의 나이를 간단한 그래프로 표시할 수 있습니다. ♪등의 문자를 사용할 수도 있습니다.

▶︎ 실행 결과

문자열을 거꾸로 표시하기 : REVERSE() 함수

다음 명령은 칼럼 name의 문자열을 거꾸로 표시합니다.

SELECT REVERSE (name) FROM tb1;

2.6 날짜와 시간을 표시하는 함수

날짜와 시간을 다루는 함수도 많이 있습니다. 그 중에서 NOW() 함수를 소개 하겠습니다.

NOW() 함수는 현재 날짜와 시간을 반환하는 함수입니다. 데이터를 처리한 날짜와 시간을 자동으로 입력하려면, 칼럼 이름 대신 NOW() 함수를 입력합니다. NOW() 함수는 날짜와 시간을 모두 반환하는 함수이므로 입력한 칼럼은 DATETIME형으로 설정합니다. NOW() 함수는 PART 5에서도 사용하니까 기억해 두기 바랍니다.

여기에서는 연습용으로 INT형의 칼럼 a와 DATETIME형의 칼럼 b를 만들고, 연속 번호와 함께 데이터를 입력한 날짜를 저장하는 테이블 rightnow를 만들겠습니다. 그리고 나서 칼럼 b에 현재 날짜와 시간을  입력하는 레코드를 5건 추가합니다. 참고로, 6장의 연습문제에서 이미 비슷한 문제를 소개했습니다.

▶︎ 실행할 테이블 rightnow

칼럼	a	b
자료형	INT   AUTO_INCREMENT   PRIMARY KEY	DATETIME

▶︎ 실행 후

a	b
1	입력 날짜와 시간
2	입력 날짜와 시간
3	입력 날짜와 시간
4	입력 날짜와 시간
5	입력 날짜와 시간

다음 명령을 실행한다.

CREATE TABLE rightnow (a INT AUTO_INCREMENT PRIMARY KEY, b DATETIME);

다음 명령을 실행한다.

INSERT INTO rightnow (b) VALUES (NOW());

위의 명령을 4번 반복합니다.

입력한 레코드를 확인하기 위해 SELECT * FROM rightnow;를 실행합니다.

칼럼 a에는 연속 번호 기능을 설정했기 때문에 1부터 순서대로 입력됩니다.

3. 조건을 설정해서 정렬하기, 4 표시할 레코드 수 지정하기

3. SELECT에 조건 설정하기

여기서는 10개의 레코드로 구성된 간단한 테이블을 다루고 있습니다. 그래서 SELECT 명령을 실행하면 곧바로 모든 결과가 표시 됩니다.  그러나 실전에서 다루는 테이블에는 몇천, 몇만 건의 레코드가 있습니다.

의미도 없이 대량의 레코드를 표시하게 되면 작업 효율이 떨어집니다. 이번에는 SELECT 명령으로 표시할 레코드 수를 제한하는 방법을 살펴보겠습니다.

3.1 레코드 수를 제한해서 표시하기

표시할 레코드 수를 제한하려면 LIMIT를 사용합니다.

표시할 레코드 수 제한하기

SELECT 칼럼_이름 FROM 테이블_이름 LIMIT 표시할_레코드_수;

매출정보 테이블 tb는 기껏해야 10건의 레코드 밖에 없지만, 이 중에서 3건만 표시해 보겠습니다.

▶︎ 실행 전(테이블 tb1)

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

▶︎ 실행 후

number	sales	month
A103	101	4
A102	54	5
A104	181	4

다음 명령을 실행한다.

SELECT * FROM tb LIMIT 3;

▶︎ 실행 결과

데이터베이스의 처리 속도에 영향을 미치는 요소는 다양합니다. 처리에 필요한 시간은 대개 경험으로 예상할 수밖에 없습니다. 그러나 소요 시간을 산정할때, '해보지 않으면 모른다.' 라고 하면 업무를 진행하기가 답답할 수밖에 없습니다. LIMIT를 사용해서 제한을 두면 결과를 보다 빨리 확인할 수 있습니다. 시간이 걸릴 것 같은 처리는 LIMIT를 사용해서 몇 줄만 표시하는 것도 좋은 방법입니다.

3.2 WHERE를 사용해서 데이터 추출하기

다음은 WHERE를 사용해서 데이터를 추출하는 방법입니다.

WHERE를 사용하면 조건을 설정해서 그 조건에 일치하는 레코드만 추출할 수 있습니다. 데이터를 얼마나 효율적으로 추출하느냐는 WHERE를 얼마나 잘 사용하느냐에 달렸다고 해도 과언이 아닙니다.

예를 들어, DELETE나 UPDATE를 아무 조건 없이 실행하게 되면 모든 레코드가 삭제되거나 변경됩니다. 이 명령에 WHERE로 조건을 설정하면 필요한 레코드에만 명령을 실행할 수 있습니다.

다음은 WHERE를 사용해서 조건에 일치하는 레코드만 표시하는 명령입니다.

조건에 일치하는 레코드만 표시하기

SELECT 칼럼_이름 FROM 테이블_이름 WHERE 조건;

'조건'을 작성하는 방법은 다음과 같습니다.

예를 들어, 조건이 '칼럼 a의 값이 10 이상'이라고 하면, a>=10이라고 기술합니다. 매출정보 테이블 tb1에서는 칼럼 sales가 매출액을 나타냅니다. 그러면 sales가 100 이상인 레코드만 표시해 보겠습니다.

▶︎ 실행할 내용

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

▶︎ 실행 후

number	sales	month
A103	101	4
A104	181	4
A101	184	4
A101	300	5
A102	205	6

다음 명령을 실핸한다.

SELECT * FROM tb WHERE sales>=100;

▶︎ 실행 결과

3.3 비교 연산자

앞에서 '× × 이상'이라는 조건을 설정할 때, >=라는 기호를 사용했습니다. 이런 기호를 비교 연산자라고 합니다. MySQL에서는 다음과 같은 비교 연산자를 사용합니다.

주요 비교 연산자

비교연산자	의미
=	같다
>	보다 크다
>=	이상
<	보다 작다
<=	이하
<>	다르다
◯ IN ✕	✕ 중에 ◯가 있다
◯ NOT IN ✕	✕ 중에 ◯가 없다
◯ BETWEEN ✕ AND ✕✕	✕부터 ✕✕의 사이에 ◯가 있다
◯ NOT BETWEEN ✕ AND ✕✕	✕부터 ✕✕의 사이에 ◯가 없다

숫자를 사용해서 WHERE조건을 설정하는 예를 몇 가지 소개하겠습니다. 매출정보 테이블 tb를 사용해서 연습합니다.

칼럼 sales가 50보다 작다

SELECT * FROM tb WHERE sales<50;

▶︎ 실행 결과

칼럼 month가 4가 아니다

SELECT * FROM tb WHERE month<>4;

칼럼 sales가 50과 100 사이에 있다(50 이상 100 이하)

SELECT * FROM tb WHERE sales BETWEEN 50 AND 100;

칼럼 sales가 50과 200 사이에 없다(50 미만 또는 200 초과)

SELECT * FROM tb WHERE sales NOT BETWEEN 50 AND 200;

칼럼 month가 5와 6 중에 있다

SELECT * FROM tb WHERE month IN (5,6);

▶︎ 실행 결과

3.4 조건에 문자열 사용하기

지금까지는 숫자를 이용해서 조건을 설정하는 방법에 대해 배웠습니다. 이번에는 문자열을 사용하여 조건을 설정하는 방법입니다. 예를 들어, 테이블 tb에서 칼럼 number는 문자열 입니다. 이 칼럼 number의 값이 'A101'인 레코드를 표시할 때도 등호(=)를 사용해서 조건을 설정할 수 있습니다.

SELECT * FROM tb WHERE number='A101';

이 때는 칼럼 number의 내용이 'A101'과 일치해야 합니다. 예를 들어, 'A0101'이나 'AA101'은 추출할 수 없습니다.

LIKE : 퍼지 검색

흔히, 사람 이름을 떠올릴 때 "김민호 인지, 김민우인지 어쨌든 '김민'이라는 글자가 들어갔던것 같은데..."라고 검색 대상이 명확하지 않을 때가 있습니다. 데이터베이스를 검색할 때에도 이렇게 일부를 포함하는 데이터를 검색할 수 있습니다. 이러한 검색을 퍼지 검색이라고도 합니다.

'◯ ◯ 문자를 포함한다.'라는 모호한 조건으로 검색할 때, LIKE를 사용합니다. 예를 들어, '아'라는 문자를 포함한다는 조건은 LIKE '아'라고 표현합니다. 앞의 SQL 문을 다음과 같이 표현할 수 있습니다.

SELECT * FROM tb WHERE number LIKE 'A101';

LIKE를 사용하면 지정한 문자열을 포함하는 모든 데이터를 대상으로 검색할 수 있습니다. 이렇게 하려면, 퍼센트(%)나 밑줄(_) 등의 와일드카드를 사용합니다. 퍼센트(%)는 '임의의 문자열', 밑줄(_)은 '임의의 한  문자'를 검색 조건으로 설정할 수 있습니다.

문자열을 대상으로 한 와일드 카드 사용 예

와일드카드	의미
%	임의의 문자열
_	임의의 한글자

지정한 문자열	해당 예
%시	서울시, 인천시, 시, 홍시
서%	서울시, 서쪽, 서
경_도	경기도, 경상도
%산%	부산시, 울산시, 산, 산수

예를 들어, 칼럼 number에서 조건을 number='A101'이라고 하지 않고, 다음과 같이 마지막 문자가 1인 조건으로 A101이 들어 있는 레코드를 검색할 수 있습니다.

SELECT * FROM WHERE number LIKE '%1';

%1이라는 것은 '임의의 문자열 + 1'이라는 의미입니다. 그러면 칼럼 name에 '김민'이라는 문자를 포함하는 레코드만 표시해 보겠습니다.

▶︎ 실행 전(테이블 tb1)

number	name	age
A101	강신우	40
A102	김기덕	28
A103	김민호	20
A104	문소리	23
A105	박문수	35

▶︎ 실행 후

number	name	age
A103	김민호	20

다음 명령을 실행한다

SELECT * FROM tb1 WHERE name '%김민%';

▶︎ 실행 결과

만일, '김민'과 '김민정', '과장 김민수'라는 이름이 저장되어 있다면 이 데이터들도 모두 추출됩니다.

name LIKE '%김민%'은 전방 일치, name LIKE '%김민'은 후방 일치, name LIKE '%김민%'은 부분 일치를 의미합니다.

지정한 문자열을 포함하지 않는 데이터 추출하기

반대로 특정 문자열을 포함하지 않는 데이터를 검색하려면 NOT LIKE를 사용합니다. 다음은 테이블 tb1에서 칼럼 name이 '김'으로 시작하지 않는 데이터를 표시하는 예입니다.

SELECT * FROM tb1 WHERE name NOT LIKE '김%';

3.5 조건에 NULL 사용하기

NULL이란 아무것도 없는 값입니다. 칼럼에 데이터를 입력하지 않고, 초깃값이 설정되어 있지 않으면 NULL이 입력됩니다. 예를 들어, 테이블 tb1J의 칼럼 name에만 데이터를 입력하면, 다른 칼럼에는 NULL이 입력됩니다.

INSERT INTO tb1J (name) VALUES ('이름만');

▶︎ 실행 결과

NULL인 경우

칼럼의 값이 NULL인 레코드를 추출할 때에는 IS NULL을 사용합니다. 다음은 테이블 tb1J에서 칼럼 age가 NULL인 레코드를 추출하는 예입니다.

SELECT * FROM tb1J WHERE age IS NULL;

NULL이 아닌 경우

반대로 칼럼의 값이 NULL이 아닌 레코드를 추출할 때에는 IS NOT NULL을 사용합니다. 다음은 테이블 tb1J에서 칼럼 age가 NULL이 아닌 레코드를 추출하는 예입니다.

SELECT * FROM  tb1J WHERE age IS NOT NULL;

참고로, '칼럼 age가 NULL인 레코드만'이라는 조건을 설정할 때, WHERE age=NULL이라고 하면 검색되지 않습니다.

중복된 데이터는 제외하고 검색하기

예를 들어, SELECT number FROM tb;를 실행하면 'A103'이 3건 검색됩니다.

▶︎ 실행 결과

검색된 10건의 레코드 중에는 중복된 값도 모두 표시됩니다. 이때, 다음과 같이 DISTINCT를 사용하면 중복된 데이터는 1번만 표시할 수 있습니다.

SELECT DISTINCT number FROM tb;

▶︎ 실행 결과

5. 여러 개의 조건을 설정하기

4. SELECT에 여러 개의 조건 설정하기

'◯ ◯ 이며 ✕ ✕ 이다.'를 나타내는 AND, '◯ ◯ 이거나 ✕ ✕ 이다.'를 나타내는 OR는 많은 프로그래 언어에서 사용하는 논리 연산자입니다. 이 AND와 OR를 조합해서 WHERE 등에 여러개의 조건을 설정해 보겠습니다.

4.1 AND 사용하기

'◯ ◯ 이며 ✕ ✕ 이다.'라는 2개의 조건을 설정하려면 AND로 연결해야 합니다.

예를 들어, 조건 '50 이상, 100 이하'를 SELECT * FROM tb WHERE sales BETWEEN 50 AND 100;으로 표현했습니다. 이 명령문을 AND를 사용해서 표현하면 다음과 같습니다.

SELECT * FROM tb WHERE sales>=50 AND sales<=100;

AND를 사용하면 다른 칼럼에 대한 조건을 중첩해서 설정할 수 있습니다.

테이블 tb는 사원번호와 월별로 매출이 저장되어 있습니다. 그러면 다음 조건을 검색하려면 어떻게 해야 할까요?

◼︎ 사원 번호가 A101인 사원의 4월 매출

LIKE를 사용해서 검색해 봅시다. 테이블 tb에서 칼럼 number의 마지막 문자가 1이고, 칼럼 month가 4인 레코드를 표시합니다.

▶︎ 실행 전(테이블 tb)

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

▶︎ 실행 후

number	sales	month
A101	184	4

다음 명령을 실행한다.

SELECT * FROM tb WHERE number LIKE '%1' AND month=4;

사원번호가 A101인 사원의 4월 매출은 184인 것을 할 수 있습니다.

이 예에서는 number LIKE 'A101'이나 number='A101'을 사용해도 되지만, 후방 일치 검색 방법을 사용해 보았습니다.

4.2 OR 사용하기

이번에는 '◯ ◯ 이거나 ✕ ✕ 이다.' 라는 조건을 설정하는 OR를 설명하겠습니다. 전의 예에서는 '50 미만 이거나 200을 초과한다.'라는 조건을 SELECT * FROM tb WHERE sales NOT BETWEEN 50 AND 200;이라고 표현했습니다. 이 명령문도 마찬가지로 OR로 표현할 수 있습니다.

테이블 tb에서 칼럼 sales가 50 미만이거나 200을 초과하는 레코드를 표시해 보겠습니다.

▶︎ 실행 전(테이블 tb)

number	sales	month
A103	101	4
A102	54	5
A104	181	4
A101	184	4
A103	17	5
A101	300	5
A102	205	6
A104	93	5
A103	12	6
A107	87	6

▶︎ 실행 후

number	sales	month
A103	17	5
A101	300	5
A102	205	6
A103	12	6

다음 명령을 실행한다.

SELECT * FROM tb WHERE sales<50 OR sales>200;

▶︎ 실행 결과

4.3 AND와 OR를 함께 사용하기

AND와 OR 조건은 몇 번이고 중첩해서 사용할 수 있습니다. 다음은 AND와 OR를 함께 사용해서 검색하는 예입니다.

◼︎ 'number가 A101이고 month가 4', 또는 'sales가 200 이상'

즉, '사원번호가 A101인 사원의 4월 매출'에 '매출이 200 이상'인 레코드를 추가합니다. 이 조건을 연결하면 다음과 같습니다.

SELECT * FROM tb WHERE number LIKE '%1' AND month=4 OR sales>=200;

▶︎ 실행 결과

사원번호 A101의 4월분 레코드에, month가 200 이상인 레코드가 2건 추가되었습니다. 조건의 순서를 바꿔서 설정해도 결과는 같습니다.

SELECT * FROM tb WHERE sales>=200 OR number LIKE '%1' AND month=4;

즉, 순서를 어떻게 설정하건 number LIKE '%1' AND month=4가 먼저 처리되고 나서, OR로 설정한 sales>=200이 나중에 처리됩니다. 다음 사항을 기억해 둡시다.

◼︎ AND와 OR가 함께 사용될 때에는 AND가 먼저 처리된다.

그러면 다음과 같은 처리는 어떻게 하면 좋을까요?

◼︎ 'sales가 200 이상이거나 number가 A101'이고, 'month가 4'

이 조건은 '200 이상의 매출' 과 '사원 A101'을 먼저 추출하고, 그중에서 4월에 해당하는 데이터만 다시 추출해야 합니다. OR 부분을 먼저 처리하고 싶지만, 이대로라면 AND가 먼저 실행됩니다.

이럴 때에는 먼저 실행하고자 하는 부분을 괄호()로 감쌉니다.

SELECT * FROM tb WHERE (sales>=200 or number LIKE '%1') AND month=4;

▶︎ 실행 결과

테이블 tb를 보면 4월에 200 이상의 매출은 없습니다. 따라서 A101의 4월분 레코드만 추출됩니다.

4.4 CASE WHEN 사용하기

이번에는 '조건에 따라 다른 값 입력하기'라는 조금은 어려운 명령을 설명하겠습니다. 예를 들어, 80점 이상이면 '우', 60점 이상이면 '양', 40점 이상이면 '가' 그 외에는 '낙제'를 입력하려 할 때, CASE WHEN을 사용합니다.

조건에 따라 값을 바꿔서 표시

CASE

WHEN 조건1 THEN 표시할 _값

WHEN 조건1 THEN 표시할 _값

WHEN 조건1 THEN 표시할 _값

......

ELSE 모든 조건에 해당하지 않을 때의 값

END

앞의 구문은 SELECT 명령으로 칼럼의 값을 표시할 때 칼럼의 설명 부분이 됩니다. 여러 칼럼을 표시하려면 평소대로 반점(,)으로 구분하여 기술합니다.

매출정보 테이블 tb를 이용해서 다음과 같이 매출에 대한 평가를 표시해 보겠습니다.

● 매출(sales)이 100 이상이면 '많음', 50 이상이면 '중간', 둘다 아니면 '적음'이라고 표시한다.

이 조건을 표시할 SQL문은 다음과 같습니다.

SELECT

CASE

WHEN sales>=100 THEN '많음'

WHEN sales>=50 THEN '중간'

ELSE '적음'

END

FROM tb;

한 줄에 모두 작성하기에는 명령문이 좀 길기 때문에 줄바꿈과 들여쓰기를 이용해서 입력했습니다. 먼저, 이 CASE WHEN~의 사용 방법을 기억하도록 합니다. 칼럼 sales의 값에 따라 CASE를 사용해서 '많음'과 '중간', '적음'중 하나를 표시하고 있습니다.

그러나 이 명령을 실행하면 예상 외의 결과가 표시됩니다.

▶︎  실행 결과

표시된 내용만 보면 무엇을 검색했는지 한눈에 알기 어렵습니다. SELECT 명령에는 칼럼 이름 등 여러 항목을 기술할 수 있습니다. 이제까지는 number나 sales와 같이 짧은 칼럼 이름을 설정했었는데, 이번에는 CASE WHEN ... END 자체가 칼럼 이름으로 표시되었습니다.

이대로는 보기에 불편하기 때문에 CASE ~ END 부분에 '평가'라는 별명을 붙여 보겠습니다. 그냥 평가라고만 표시하면 무엇을 평가한 내용인지 알 수 없으므로, 칼럼 number와 sales도 함께 표시하겠습니다.

다음 명령을 실행한다.

SELECT number, sales,

CASE

WHEN sales>=100 THEN '많음'

WHEN sales>=50 THEN '중간'

ELSE '적음'

END AS 평가

FROM tb;

▶︎ 실행 결과

CASE ~ END에 별명을 설정하고자 'AS 평가'를 추가했습니다. 표시할 항목은 number, sales, CASE ~와 같이 반점(,)으로 구분합니다.

참고로 앞의 명령문을 한 줄에 모두 입력하면 다음과 같습니다.

SELECT number, sales, CASE WHEN sales>=100 THEN '많음' WHEN sales>=50 THEN '중간' ELSE '적음' END AS 평가 FROM tb;

5. 정렬하기

5.1 오름차순으로 정렬하기

SELECT 명령으로 표시되는 레코드 순서는 불규칙합니다. 여기서 다루는 10건 정도의 레코드라면 아무 문제 없지만, 몇천 건, 몇만 건의 레코드를 표시할 때에는 표시하는 순서가 중료합니다.

레코드를 지정한 칼럼의 값에 따라 순서대로 표시하려면 ORDER BY를 사용합니다.

레코드를 오름차순으로 표시하기

SELECT 칼럼_이름 FROM 테이블_이름 ORDER BY 기준_칼럼_이름;

이렇게 하면 오름차순, 즉 작은 값에서 큰 값으로 정렬 됩니다. 다음은 테이블 tb의 매출 sales를 오름차순으로 표시하는 예입니다.

다음 명령을 실행한다.

SELECT * FROM tb ORDER BY sales;

ORDER BY에 아무것도 설정하지 않으면 오름차순으로 정렬되지만, 오름차순으로 정렬할 것을 명시하려면 ASC를 기술합니다. 실행 결과는 같습니다.

SELECT * FROM tb ORDER BY sales ASC;

5.2 내림차순으로 정렬하기

내림차순으로 정렬할 때에는 DESC를 사용합니다. 내림차순으로 정렬하려면 반드시 DESC를 붙혀야 합니다. DESC나 ASC는 'ORDER BY 기준_이름'뒤에 기술합니다.

매출정보 테이블 tb의 칼럼 sales를 내림차순으로 모두 정렬하고 상위 5건만 표시해 보겠습니다.

다음 명령을 실행한다.

SELECT * FROM tb ORDER BY sales DESC LIMIT 5;

▶︎ 실행 결과 (사진에 DESC 를 빼먹었습니다.  다른 결과가 출력되니 꼭 DESC를 붙혀서 작성해 보시기 바랍니다.)

 ORDER BY는 단순하게 순서대로 표시만 하는 기능은 아닙니다. 예를 들어, 정렬하고 나서 아래에서 10건만 삭제 하는 것처럼, 삭제나 변경 등의 조건을 설정할 때에도 사용할 수 있습니다.

5.3 표시할 레코드의 범위 지정하기

레코드를 순서대로 표시할 때, '3번째부터 7번째까지'와 같이 표시할 범위를 지정할 수 있으면 편리할 때가 있습니다. 이전엔 LIMIT를 사용해서 표시할 레코드의 수를 제한했습니다. LIMIT를 사용한 예에서는 단순히 3건만 선택했지만, OFFSET을 사용하면 표시 범위를 지정할 수가 있습니다.

표시할 범위 지정하기

SELECT * 칼럼_이름 FROM 테이블_이름 LIMIT 표시할_레코드_수 OFFSET 이동할_레코드_수;

'이동할_레코드_수'란 '표시를 시작할 지점까지 몇 개의 레코드를 이동할 것인가'를 지정하는 수입니다. 예를 들어, OFFSET 3이라고 하면, 원래는 1번째 레코드부터 표시하지만, 3개의 레코드를 이동하고서 4번째 레코드부터 표시하게 됩니다.

매출정보 테이블 tb에서 매출(칼럼 sales)이 많은 순서 중에서 4번째와 5번째 레코드를 표시해 보겠습니다.

다음 명령을 실행한다.

SELECT * FROM tb ORDER BY sales DESC LIMIT 2 OFFSET 3;

▶︎ 실행 결과

6. 그룹별로 표시하기

6.1 그룹별로 표시하기

테이블 tb에는 칼럼 number가 A101인 레코드가 여러 개 있습니다. 이처럼, 같은 값이 레코드르르 그룹으로 만들 수 있습니다. '그룹'으로 설정된 레코드는 합계와 평균을 비롯한 각종 데이터 처리를 그룹 단위로 할 수 있습니다.

그룹화를 하면 편리한 점도 있지만, 그룹화한 대상이 무엇인지 알기 어렵다는 단점이 있습니다. 그렇게 때문에, 그룹화할 때에는 그 대상이 무엇인지 항상 인식해야 합니다.

그룹화할 때에는 GROUP BY를 사용합니다.

그룹별로 표시하기

SELECT 칼럼_이름 FROM 테이블_이름 GROUP BY 그룹화할_칼럼_이름;

그룹별로 표시만 하는 것은 아무 의미가 없지만, 일단 칼럼 number를 그룹화해서 어떻게 표시되는지 확인해 보겠습니다. 다음은 칼럼 number를 그룹화하는 명령입니다.

SELECT * FROM tb GROUP BY number;

▶︎ 실행 결과

사원번호 number가 1종류씩만 표시되었습니다. GROUP BY number에 따라 number별로 그룹화된 결과입니다. 실행 결과를 통해 칼럼 number에는 A101, A102, ..., A107의 5종류의 데이터가 존재한다는 사실을 알 수 있습니다.

그룹별로 처리하기 때문에 1종류씩 표시된 레코드는 그룹에 속해 있는 레코드 중에 임의로 선택되었다고 생각할 수 있습니다. 앞선 결과에서 number가 A101인 레코드의 sales는 184이지만, 이는 우연히 선택된 데이터라고 해도 과언이 아닙니다. 단, 선택되는 후보 대상은 같은 그룹에 속한 모든 레코드라는것을 기억하도록 합니다.

6.2 그룹에 속한 레코드의 수

이번엔는 칼럼 number별 그룹에 속한 레코드의 수를 알아보겠습니다. 개수를 구하는 함수는 COUNT()입니다. COUNT(✕)를 사용하면 칼럼 ✕의 개수(NULL을 제외한 값)를 알 수 있습니다.

그룹화 했을 때 그 처리 대상은 같은 그룹에 속해 있는 모든 레코드라고 앞서 설명했습니다. 예를 들어, number가 A101인 그룹에서 sales는 184만 표시되었습니다. 하지만 실제로는 A101에 속해 있는 모든 sales의 값이 대상입니다. 따라서 number로 그룹화하고 나서 COUNT(sales)를 실행하면 그룹별 칼럼 sales의 개수가 표시됩니다.

또한, 레코드의 개수를 알아보는 것이기 때문에 NULL만 없다면 어떤 칼럼을 대상으로 해도 결과는 마찬가지 입니다. COUNT(sales)가 아닌 COUNT(number)나 COUNT(month)를 실행해도 결과는 같습니다. 또한, 모든 칼럼을 나타내는 별표(*)를 사용하여 COUNT(*)를 실행하면 편리합니다. 이렇게 NULL을 포함하여 레코드 개수를 계산합니다.

SELECT COUNT(*) FROM tb GROUP BY number;

▶︎ 실행 결과

칼럼 number의 그룹별로 존재하는 레코드 수가 표시되었습니다. 하지만, 이 결과만으로는 무엇에 대한 개수인지 알 수가 없습니다. 그래서 다음과 같이 명령을 변경해 보겠습니다.

SELECT

number, COUNT(*) AS 건수

FROM tb

GROUP BY number;

▶︎ 실행 결과

6.3 그룹별 합계와 평균 표시하기

이번에는 사원별로 매출의 합계를 표시해 보겠습니다. 합계를 계산하는 함수는 SUM()입니다. 칼럼 sales의 합계를 구하려면 SUM(sales)라고 기술하면 됩니다.

테이블 tb에서 칼럼 number별로 칼럼 sales의 합계를 표시해 보겠습니다. 단, 칼럼 sales의 합계에서는 '합계'라는 별명을 붙입니다.

다음 명령을 실행한다.

SELECT

number, SUM(sales) AS 합계

FROM tb

GROUP BY number;

▶︎ 실행 결과

이 예에서는 사원 1명당 매출의 합계를 표시하고 있습니다. 예를 들어, 사원번호가 A101인 사원은 184 + 300을 계산한 값인 484가 매출의 합계입니다.

다음은 칼럼 sales의 평균을 칼럼 number로 그룹화해서 계산하는 예입니다.

SELECT

number, AVG(sales)

FROM tb

GROUP BY number;

▶︎ 실행 결과

7. 그룹화에 조건 설정하기

7.1 그룹별로 데이터 처리하기

조금 복잡하긴 하지만, GROUP BY로 그룹화 하고 나서, 검색 조건을 설정해 보겠습니다.

예를 들어, 매출의 합계를 사원번호별로 계산하고 나서, 합계가 ✕ ✕ 이상인 것만 표시할 때에는 그룹화한 값에 조건을 설정하는 HAVING을 사용합니다.

그룹화에 검색 조건 설정하기

SELECT 집계한_칼럼 FROM 테이블_이름 GROUP BY 그룹화할_칼럼 HAVING 조건;

HAVING에 설정한 조건은 그룹화한 값에 대한 것입니다. 그러면 실제로 조건을 설정해 보겠습니다. 매출의 합계를 사원별로 계산하고 나서, 그룹의 합계가 200 이상인 것만 표시하는 조건을 설정합니다.

즉, 칼럼 number별로 '칼럼 sales의 합계가 200 이상'일 때에만 '칼럼 number와 칼럼 sales의 합계'를 표시해 보겠습니다.

다음 명령을 실행한다.

SELECT

number, SUM(sales)

FROM tb

GROUP BY number

HAVING SUM(sales)>=200;

▶︎ 실행 결과

만약, HAVING SUM(sales)>=200이라는 조건을 설정하지 않고 실행한다면 실행결과는 다음과 같습니다.

▶︎ 실행결과

HAVING을 사용하지 않은 결과와 비교했을 때, 조건을 설정했을 때에는 A103의 합계 130과 A107의 합계 87처럼 200 미만인 레코드는 표시되지 않는 것을 확인할 수 있습니다.

여기에서는 매출의 합계를 사원번호별로 계산하고 나서, 그룹의 합계가 200 이상인 것만 표시해 보았습니다. HAVING을 이용해서 데이터를 추출하는 방법은 먼저 그룹화하고 나서 데이터를 추출하는 것입니다. 다음에 소개하는 먼저 데이터를 추출하고 나서 그룹화하는 방법과 어떻게 다른지 확인해 보겠습니다.

7.2 먼저 데이터를 추출하고 그룹화 하기

앞에서는 먼저 그룹별로 분류하고 결과를 추출했지만, 이번에는 먼저 데이터를 추출하고 그룹화하는 방법입니다. 예를 들어, 매출이 10,000 이상인 레코드를 대상으로 사원별 매출의 평균을 계산하는 것입니다.

그룹화하기 전에 데이터를 추출하는 조건은 WHERE를 사용합니다. 여기에서 다른 점은 GROUP BY를 맨 마지막에 사용한다는 점입니다.

그러면 먼저 매출(sales)이 50 이상인 거래만 추출하고 나서, 그 레코드를 대상으로 사원별(number) 매출의 평균을 구해보겠습니다. 즉, 칼럼 sales의 값이 50 이상인 레코드를 대상으로, 칼럼 number별로 그룹화하여 칼럼 number와 sales의 평균을 표시합니다.

다음 명령을 실행한다.

SELECT

number, AVG(sales)

FROM tb

WHERE sales>=50

GROUP BY number;

▶︎ 실행 결과

SELECT number, sales FROM tb;로 모든 레코드를 표시해서 결과를 비교해 보겠습니다.

▶︎ 실행 결과

내용을 확인해 보겠습니다. A101의 sales 데이터는 184와 300입니다. 이 두 데이터 모두 50 이상이므로 이 두 데이터의 평균을 계산한 값 242가 표시되었습니다. 이에 비해 A103의 sales 데이터는 101, 17, 12입니다. 이 중 17과 12는 50 미만이므로 제외되고, 결국 101만 표시되었습니다.

7.3 그룹화하고 나서 정렬하기

이번에는 그룹화한 결과를 정렬해서 표시해 보겠습니다. SELECT 명령에 GROUP BY ... 와 ORDER BY ... 을 모두 사용해야 합니다. 작성 순서는 먼저 GROUP BY ... 를 사용하고, 다음으로 ORDER BY ... 를 사용해야 합니다.

테이블 tb에서 사원별 매출의 평균 AVG(sales)를 높은 순서대로 표시해 보겠습니다. 즉, 칼럼 number별로 그룹화하고, 칼럼 number와 sales의 평균을 내림차순으로 정렬해서 표시하겠습니다.

다음 명령을 실행한다.

SELECT

number, AVG(sales)

FROM tb

GROUP BY number

ORDER BY AVG(sales)

DESC;

▶︎ 실행 결과

number별고 그룹화되어 sales의 평균값이 높은 순서대로 표시되었습니다.

7.4 그룹화 과정 정리

그룹화에서 조건을 설정하는 방법을 다시 한번 정리해 보겠습니다. 크게는 2가지가 있습니다.

● 먼저 데이터를 추출하고 나서 그룹화하기

● 먼저 그룹화하고 나서 데이터 추출하기

첫 번째 경우에서, 그룹화하는 순서는 먼저 WHERE로 조건을 설정하고 GROUP BY로 그룹화합니다. 두 번째 경우에서는 GROUP BY로 먼저 그룹화 하고 HAVING으로 조건을 설정합니다. 또한, 그룹화한 결과를 정렬할 때에는 ORDER BY~를 사용합니다.

그러면 이 조건들을 모두 사용하는 예를 실행해 보겠습니다. 매출이 50 이상인 데이터를 대상으로 사원(number)별 매출의 평균 AVG(sales)를 높은 순으로 표시해 보겠습니다.

다음 명령을 실행한다.

SELECT

number, AVG(sales)

FROM tb

WHERE sales>=50

GROUP BY number

ORDER BY AVG(sales)

DESC;

▶︎ 실행 결과

추출과 정렬, 그룹화를 모두 사용해야 할 때에는 아무리 MySQL 숙련자라 해도 어떻게 하면 효율적인 SQL 문을 작성할지 고민하게 마련입니다. SELECT와 GROUP BY, ORDER BY ... 등 여러 구문을 사용할 때는 반드시 작성 순서를 지켜야 합니다. 순서를 틀리면 오류가 발생합니다.

주요 명령인 WHERE와 GROUP BY, ORDER BY의 기술 순서는 다음과 같습니다.

① WHERE 조건

⬇︎

② GROUP BY ✕ ✕

⬇︎

③ ORDER BY ◯ ◯ (DESC)

WHERE와 GROUP BY, ORDER BY를 모두 사용해서 SQL문을 작성할 경우, 작성 순서를 정확히 기억하도록 합니다. 분명히 작업 효율이 크게 향상될 것입니다.

체크!

★ WHERE로 조건을 설정해서 SELECT할 수 있다.

★ LIMIT로 레코드 수를 제한해서 SELECT할 수 있다.

★ ORDER BY ... (DESC)를 사용해서 오름차순(내림차순)으로 SELECT할 수 있다.

★ GROUP BY를 사용해서 그룹별로 SELECT할 수 있다.

★ WHERE나 HAVING으로 조건을 설정하고, GROUP BY를 사용해서 그룹별로 SELECT할 수 있다.

★ WHERE와 GROUP BY, ORDER BY의 작성 순서를 이해하고 있다.