[MySQL] WHERE 조건 확장하는 SQL 연산자(IN, BETWEEN, LIKE, HAVING)

where 조건을 사용할 때 

일반적인 조건 말고도 다양한 연산자를 활용하여 데이터를 출력할 수 있다.

---

연산자 비교를 확인하기 위한 임의의 테이블생성 및 값 추가

-- 테이블 생성하기
create table MyTest (
    idx int auto_increment primary key, 	-- PK
    userid varchar(20) unique not null, 	-- unique, not nul
    userpw varchar(20) not null, 		-- not nul
    name varchar(20) not null,			-- not nul
    hp varchar(20) not null,			-- not nul
    email varchar(50) not null,			-- not nul
    ssn1 char(6) not null,			-- not nul
    ssn2 char(7) not null,			-- not nul
    zipcode varchar(5), 
    address1 varchar(100),
    address2 varchar(100),
    address3 varchar(100),
    regdate datetime default now(), 		-- 기본값
    point int default 1000,			-- 기본값
    gender varchar(10) not null			-- not nul
);

-- 데이터 추가
insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender) 
values ('apple', '1111', '김사과', '010-1111-1111', 'apple@apple.com', '001011', '4011111', '12345', '서울 서초구', '양재동', '111-11', '150', '여자');

insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender) 
values ('banana', '2222', '반하나', '010-2222-2222', 'banana@banana.fruit', '001011', '4011111', '12345', '서울 서초구', '양재동', '111-11', '200', '여자');

insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender) 
values ('orange', '3333', '오렌지', '010-3333-3333', 'orange@orange.jeju', '001011', '4011111', '12345', '서울 서초구', '양재동', '111-11', '100', '남자');

insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender) 
values ('melon', '4444', '이메론', '010-4444-4444', 'melon@melon.com', '001011', '4011111', '12345', '서울 서초구', '양재동', '111-11', '250', '남자');

insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender) 
values ('cherry', '5555', '채리', '010-5555-5555', 'cherry@cherry.com', '001011', '4011111', '12345', '서울 서초구', '양재동', '111-11', '150', '여자');

-- regdate에 null 값 추가
insert into MyTest (userid, userpw, name, hp, email, ssn1, ssn2, zipcode, address1, address2, address3, point, gender, regdate) 
values ('berry', '6666', '배애리', '010-6666-6666', 'berry@berry.fruit', '001011', '4011111', '12345', '서울 서초구', '양재동', '111-11', '100', '여자', null);

---

1) BETWEEN 연산자

BETWEEN 연산자는 특정 범위의 데이터를 조회할 수 있는데

A이상 ~ B이하에 해당하는 값이 있는 데이터를 조회한다는 뜻이다.

-- 기본 문법
~ where 컬럼명 between A and B

 

예제) point가 150이상이고 200이하인 데이터 출력

-- BETWEEM 사용 X
select userid, name, point, gender from MyTest where point >= 150 and point <= 200;

-- BETWEEM 사용 O
select userid, name, point, gender from MyTest where point between 150 and 200;

실행결과 ▶

---

2) IN 연산자

IN 연산자는 조회하려는 데이터값이 여러개일 때 사용할 수 있다.

= 연산자와 비슷하지만 = 연산자는 데이터 값 하나만 조건을 지정할 수 있는데

IN 연산자는 데이터값을 여러개(목록)로 지정할 수 있다.

※ =연산자로 사용 할 경우 or을 함께 사용해주어야한다.

-- 기본 문법
~ where 컬럼명 in (A, B, ...)

이름이 김사과, 반하나, 오렌지인 사람의 모든 정보 검색

-- IN 사용 X
select * from MyTest where name = '김사과' or name = '반하나' or name = '오렌지';

-- IN 사용 O
select * from MyTest where name in ('김사과', '반하나', '오렌지');

실행결과 ▶

 

---

3) 패턴검색(%,  _)

3_1) % 사용

3_1_a) 아이디가 a로 시작하는 멤버 정보 검색

select * from MyTest where userid like 'a%';

실행결과 ▶

 

3_1_b) 아이디가 a로 끝나는 멤버 정보 검색

select * from MyTest where userid like '%a';

실행결과 ▶

 

 

3_1_c) 아이디에 a가 포함되는 멤버 정보 검색

select * from MyTest where userid like '%a%';

실행결과 ▶

---

3_2) _ 사용

 이름이 첫글자 김씨이고 3글자인 멤버 정보 검색

select * from MyTest where name like '김__';

실행결과 ▶

---

4) ORDER BY

4_1) ASC, DESC

ASC) idx를 오름차순으로 정렬

※ asc 생략 가능

select * from MyTest order by idx;

 

DESC) idx를 내림차순으로 정렬

select * from MyTest order by idx desc;

 

예제) 포인트순으로 내림차순, 포인트가 같으면 regdate 내림차순(가입 최신순)

select * from MyTest order by point desc, regdate desc;

실행결과▶

---

4_3) LIMIT 함께 사용

 

4_3_a) 앞에서부터 2개 데이터행을 가져와라

select * from MyTest limit 2;

 

4_3_b) 인덱스로 사용 : 인덱스 2번부터 3개를 가져와라

select * from MyTest limit 2, 3;

 

예제) 멤버에서 포인트순 내림차순정렬 후 포인트가 같다면 가입순으로 내림차순 한 뒤 top3개를 가져와라

select * from MyTest order by point desc, regdate desc limit 3;

실행결과▶

---

5) GROUP BY

-- 기본 문법
select 그룹 맺을 컬럼 또는 집계함수 from 테이블 group by 그룹맺을 컬럼

---

5_1) 컬럼만 사용

성별(gender)을 기준으로 그룹 생성하기

※ 중복된 성별은 하나로 묶여서 출력된다.

select gender from MyTest group by gender;

실행결과▶

---

5_2) 집계함수와 함께 사용

그룹으로 묶인 성별별 인원수를 출력한다.

select gender, count(idx) as 인원수 from MyTest group by gender;

실행결과▶

---

5_3) HAVING

HAVING은 GROUP BY 뒤에 작성되며, 그룹에 조건을 지정해야할 때 사용한다.

 

WHERE는 개별 데이터(행)에 조건을 지정하는 방식이였다면

HAVING은 그룹 결과에 조건을 지정하는 방식이다.

※ WHERE절에서는 집계함수를 사용할 수 없다. (COUNT, SUM, MAX, MIN)

 

💡 실행순서!!!!!

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

 

5_3_a) HAVING으로 특정 그룹만 조회

성별이 여자인 그룹을 조회하고 해당 그룹의 인원수 함께 출력

select gender, count(idx) as 인원수
from MyTest
group by gender
having gender = '여자';

 

실행결과▶

 

5_3_b) WHERE와 HAVING 차이

select gender, count(idx) as 인원수
from MyTest
where gender = '여자'
group by gender
having gender = '여자';​

 

5_3_a와 실행결과는 같지만 처리 순서가 다르게 흘러간다.

• WHERE : 그룹 생성 전 데이터 필터링
• HAVING : 그룹 생성 후 그룹 필터림

 

5_3_c) 인원수가 3명 이상인 그룹 조회

※ HAVING절에선 별칭 사용을 할 수 있다.

select gender, count(idx) as 인원수
from MyTest
group by gender
having 인원수 >= 3;

실행결과▶

 

5_3_d) WHERE + GROUP BY + HAVING + ORDER BY 함께 사용

포인트가 50 이상인 회원을 선택한 후,

성별별 평균 포인트를 구하고, 평균 포인트가 100이상인 성별만 출력.

단, 성별이 여러개라면 평균 포인트가 높은순으로 정렬하여 출력

select gender, AVG(point) 평균 
from MyTest 
where point >= 50
group by gender 
having 평균 >= 100 
order by 평균 desc;

실행결과▶