SQL이 쉽고 재밌다고 했던 지난 날의 나를 반성한다...☺️
실습환경 만들기
USE dataname
select * from celeb;
조건을 "모두 만족"하는 경우 TRUE
SELECT column1, column2, ...
FROM tablename
WHERE condition1 AND condition2 AND condition3 ...;
예제 1.
SELECT * FROM celeb WHERE age=29 AND sex='F' ;
예제 2.
SELECT * FROM celeb
WHERE sex='M' AND age>40 ORDER BY name;
"하나의 조건"이라도 만족하는 경우 TRUE
SELECT column1, column2, ...
FROM tablename
WHERE condition1 OR condition2 OR ...;
예제 1.
SELECT * FROM celeb
WHERE age<=25 OR age>=30
ORDER BY age;
예제 2.
SELECT * FROM celeb
WHERE (age<=29 AND sex='F')
OR (age>=30 AND sex='M')
ORDER BY age, sex ASC;
예제 3.
SELECT * FROM celeb
WHERE (agency='YG엔터' OR agency='나무엑터스')
AND age<=30;
SELECT * FROM celeb
WHERE (sex='M' AND agency='YG엔터')
OR age<=30
ORDER BY age, agency ASC;
SELECT * FROM celeb
WHERE ((id%2)=1 AND sex='M')
OR ((id%2)=0 AND agency='YG엔터')
ORDER BY age ASC;
조건을 만족하지 못하는 경우 TURE를 return
SELECT column1, column2, ...
FROM tablename
WHERE NOT condition1;
예제 1.
SELECT * FROM celeb WHERE NOT sex='F';
예제 2.
SELECT * FROM celeb
WHERE (NOT sex='M' AND agency='YG엔터')
OR (jdb_title='가수' AND NOT agency='YG엔터');
SELECT * FROM celeb
WHERE (birthday > 19891231 AND NOT sex='F')
OR (birthday < 19800101 AND NOT agency='안테나');
SELECT * FROM celeb
WHERE NOT agency='YG엔터'
AND age<=40
ORDER BY name ASC;
조건 값이 범위 사이에 있으면 TRUE
SELECT column1, column2, ...
FROM tablename
WHERE column1 BETWEEN value1 AND value2;
예제 1.
SELECT * FROM celeb
WHERE age BETWEEN 20 AND 40;
예제 2.
SELECT * FROM celeb
WHERE (NOT birthday BETWEEN 19800101 AND 19951231 AND sex='F')
OR (agency='YG엔터' AND NOT age BETWEEN 20 AND 45);
예제 3.
SELECT * FROM celeb
WHERE
(id BETWEEN 1 AND 5 AND sex='F')
OR
((id%2)=1 AND sex='M' AND age BETWEEN 20 AND 30);
목록 안에 조건이 존재하는 경우 TRUE
SELECT column1, column2, ...
FROM tablename
WHERE column IN (value1, value2, ...);
예제 1.
SELECT * FROM celeb
WHERE age
IN (28,48);
예제 2. ⭐
SELECT * FROM celeb
WHERE
NOT agency IN ('나무엑터스', '안테나', '울림엔터')
AND (sex='F' OR age>=45);
조건 값이 패턴에 맞으면 TURE
SELECT column1, column2, ...
FROM tablename
WHERE column LIKE pettern;
SELECT * FROM celeb
WHERE agency
LIKE 'YG엔터';
예제 2.
SELECT * FROM celeb
WHERE agency
LIKE 'YG%';
예제 3.
SELECT * FROM celeb
WHERE agency
LIKE '%엔터';
예제 4.
SELECT * FROM celeb
WHERE job_title
LIKE '%가수%';
예제 5.
SELECT * FROM celeb
WHERE agency
LIKE '_G%';
예제 6.
SELECT * FROM celeb
WHERE job_title
LIKE '가_%';
예제 7.
SELECT * FROM celeb
WHERE job_title
LIKE '가____%'; # 언더바 4개
예제 8.
SELECT * FROM celeb
WHERE job_title
LIKE '영%모델';
예제 9.
SELECT * FROM celeb
WHERE job_title LIKE '%영화배우%'
AND job_title LIKE '%탈렌드%';
예제 10.
WHERE job_title
LIKE '%,%'
WHERE
NOT
(job_title LIKE '%영화배우%'
OR job_title LIKE '%탈렌트%')
SELECT * FROM celeb
WHERE job_title LIKE '%,%'
AND NOT
(job_title LIKE '%영화배우%' OR job_title LIKE '%탈렌트%');
예제 11.
SELECT * FROM celeb
WHERE name LIKE '__';
SELECT * FROM celeb
WHERE age BETWEEN 30 AND 50 AND job_title LIKE '%개그맨%';