제로베이스 SQL Chapter5~6 비교 및 논리연산자

Comparison Operators(비교연산자)

select * from tablename where age=28;
select * from tablename where age!=29;

A=B;
A!=B;
A>B;
A<B;
A>=B;
A<=B;

Logical Operators(논리연산자)

select * from tablename where age=28 and sex="F";
select * from tablename where age>=30 and sex="F";

1) AND : 조건을 모두 만족하면 TRUE

select * from tablename
where condition1 and condition2 ---;

2) OR : 조건을 하나라도 만족하면 TRUE

select * from tablename
where condition1 or condition2 ---;

and와 or문 중첩

select * from tablename
where (condition1-1 and condition1-2) or (condition2-1 and condition2-2 ---;

AND가 OR보다 우선순위가 높기 때문에 괄호가 필요는 없지만 가독성을 위해 작성

select * from tablename where ((id%2=1) and sex='m') or ((id%2=0) and agency='');

column % number : 나머지

3) NOT : 조건을 만족하지 않으면 TRUE

select * from tablename where condition1 or not condition2;

4) BETWEEN : 조건값이 범위 사이에 있으면 TRUE

select column1, column2 ---
from tablename
where column1 between condition1 and condition2;

select * from celeb where age between 20 and 40;

select * from celeb
where
(not birthday between 198001 and 19951231 and sex='F')
or (not agency='yg' and not age between 20 and 45);

5) IN : 조건값이 목록에 있으면 TRUE

select column1, column2 ---
from tablename
where column in (value1, value2, ---);

select * from tablename where condition in (a, b);

6) LIKE : 조건값이 패턴에 맞으면 TRUE

select column1, column2 ---
from tablename
where column like pattern ---;

select * from celeb where agency like 'YG';
select * from celeb where agency like 'YG%';   #YG로 시작하는 문자열
select * from celeb where agency like '%엔터테인먼트'; #엔터테인먼트로 끝나는 문자열
select * from celeb where agency like '%엔터%'   #엔터가 들어가는 문자열
select * from celeb where agency like '_G%'     #두번째 글자가 G인 문자열
select * from celeb where agency like '__G%'     #세번째 글자가 G인 문자열
select * from celeb where agency like 'Y_%'     #Y로 시작하는 최소 2글자 이상의 문자열