[SQL] SELECT 문 필터링 – 조건 검색, NULL 체크, LIKE, BETWEEN, IN 활용

테이블 만들고 값 넣기

drop database if exists sqldb; -- 만약 기존에 sqldb가 있다면 우선 삭제!!
create database sqldb;
use sqldb;
create table usertbl( 
	userID char(8) NOT NULL PRIMARY KEY, -- id컬럼 :PK
	name varchar(10) NOT NULL,           -- 이름
    birthYear int not null,              -- 출생년도
    addr char(2) not null,               -- 지역(경기, 서울)
    mobile1 char(3),                     -- 휴대폰 앞자리
    mobile2 char(8),                     -- 휴대폰 뒤에..
    height smallint,                     -- 키
    mDate DATE                           -- 회원 가입일..
);

create table buytbl (
	num int auto_increment not null primary key, -- 순번
    userID char(8) not null, -- 아이디 FK : 밑에 어디와 연결하는지 세팅..
    prodName char(6) not null,  -- 상풍명
    groupName char(4),          -- 분류
    price int not null,         -- 가격
    amount smallint not null,   -- 
    foreign key( userID ) references usertbl(userID)
);

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL  , NULL      , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL  , NULL      , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL   , 30,   2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200,  1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,  5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50,   3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80,  10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책'    , '서적', 15,   5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL   , 30,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL   , 30,   2);

---

제대로 생성이 되었는지 체크

use sqldb;
show tables;
select * from usertbl;
select * from buytbl;

• ⇒ 실제 구매 이력 : buytbl
• 시스템을 사용하는 유저 정보 : usertbl
• ⇒ 이 2개 테이블의 관계(연결)
  • FK --> 각 테이블의 연결 필드명
  • 운이 좋게 같을 뿐... 꼭 같을 필요는 없음

1개 조건에 대한 필터링을 하고싶다

• 1. 이름이 김범수인 사람의 모든 정보를 보여주세요 (만약 있다면)

select * from usertbl where name = '김범수';
select * from usertbl where name = "김범수";
select * from usertbl where name = `김범수`; -- 에러남
# --> 문자열 값에 대한 비교에서 backtick 쓰지 마세요-> 에러남
# + 정확한 매칭!! 그로므로 공백/대소문자 주의해라 
select * from usertbl where name = "김범수 "; -- 공백까지 가지고있는 사람은 없기때문에 나오지 않는다 

• 1. 이름이 조관우인 고객이 있다면, 그 사람의 모든 정보 보여줘라

select * from usertbl where name = '조관우';

• 1. 주소지가 서울인 사람들만 찾아주세요 (볼 항목 : 전체)

select * from usertbl where addr = '서울';

• 1. 출생년도가 1973년인 고객들이 있다면 다 찾아라

# --> birthYear의 속성 : 정수형
desc usertbl;
select * from usertbl where birthYear = 1973;
select * from usertbl where birthYear = "1973";
# --> birthYear의 원리 필드 속성 : 정수형/숫자
#     이정도에 있어서는 타입이 미스매칭이 나도 적당히 알아서 해준다
#     최대한 타입/필드 속성을 맞춰서 하는 것을 추천함 

• 1. mobile1에 값이 결측/ 누락된 데이터만 찾아주세요
  • 앞의 1 ~ 4번 과 공통점
    • 내가 원하는 조건에 맞는 필터링
    • 단, 조건이 값이 아니라 결측값
  • FM : is null, is not null

select * from usertbl;
select * from usertbl where mobile1=null; -- 에러 없이 공회전;;
# ==> 에러가 나는 것이 아니라 결측값이 없다고 오해할 수 있음!
select * from usertbl where mobile1 is null; -- DB에 명시적인 null 형식 
#****신경쓰셔서 하셔야 할 쿼리문 중 하나임!!!**** 

• • 그러면 핸드폰 정보를 기입한 고객들의 정보만 봅시다 → 몇 명인가

select * from usertbl where mobile1 is not null;

---

조건이 여러개인 경우

• 1. 고객들 중에서 1970년 이후에 태어나신 분들 중에서, 키가 182 이상인 고객들의 정보를 보여줘라 ( 볼 항목 : 이름, 아이디 )
  • 1~5와 차이점 : 조건이 여러개인 경우에 대해서

select name, userID 
from usertbl 
where birthYear >= 1970 and height >= 182;
# ==> 늘 하던대로, 여러 조건들에 대해서 작성하면 되고 
#     그 조건들의 관계를 and/or 표현 

---

• 7-1. 주소지가 서울인 고객들의 모든 정보를 보여주세요

select * from usertbl where addr = '서울';

• 7-2. 주소지가 서울인 사람들 중에서 핸드폰 앞자리(mobile1)가 011을 사용하는 모든 고객들의 정보를 보여주세요

select * from usertbl where addr = '서울' and mobile1 = '011';

select * from usertbl where addr = '서울' and mobile1 = 011; -- 되긴 함 
desc usertbl;
#==> 기본적인 추천은 주어진 타입에 맞춰서 하시는 것을 추천 

---

• 1. 위의 7번 결과를 바탕으로... 볼 항목 : 아아디, 이름, 연락처 ⇒ 고객들한테 문자 발송하기 위해서 ( 앞자리, 뒤에 번호 같이)

select userID, name, mobile1, mobile2 
from usertbl 
where addr = '서울' and mobile1 = '011';
# ==> DB/Table을 설계하는 입장 : 중복을 최대한 피하기 위해서 상당히 쪼개는 스타일을 선호함
#     저장 : 사소한 단위로 쪼개져서 저장됨 
#     보려고 하면 : 흩어진 정보를 모아야 함 
# ==> 전화번호가 쪼개져있음

select userID, name, concat(mobile1, mobile2) 
from usertbl 
where addr = '서울' and mobile1 = '011';

select userID, name, concat(mobile1, mobile2) as `Mobile`
from usertbl 
where addr = '서울' and mobile1 = '011';

select userID, name, concat(mobile1,"-", mobile2) as `Mobile`
from usertbl 
where addr = '서울' and mobile1 = '011';

---

• 1. +++ 조건이 숫자인 경우들에 대해서 : 연속된 범위
  • 1970 ~ 1980 사이 출생년도,,, 170~180
  • 예) 고객들 중에서 키가 180 이상 183d이하인 고객들의 이름, 키만 조회
    • sol1 ) 부등식 사용하면 됨 -> height >= 180 and height <= 183

    select name, height from usertbl;
    select name, height from usertbl where height >= 180 and height <= 183;
    # --> 쿼리문에 중복에 대한 조건이 보임
    

    • sol2 ) between A(포함) and B(포함) → A이상 B이하 값들에 대해서 ⇒ 주의 : A/B 기준값이 다 포함된다

    select name, height from usertbl where height between 180 and 183; -- height라는 속성명을 두번 반복하지 않는다는 점이 위 코드와 다름 
    # +++ tip) 응용하면 -> 180초과 183 미만 : between 180.1 and 182.999
    #      ==> between을 기반으로 우회적으로 표현! 
    

---

• 1. 조건이 항목/카테고리/특정한 값이 여러개인 경우 Q ) 수도권에 있는 고객들을 찾아주세요 -- 서울/경기
  • sol1) 필요한 조건들을 주절주절 나열하는 방법

  select * from usertbl where addr = '서울' or addr = '경기';
  

  • sol2) in (리스트업) // python : if a in ["a","b","c"]:

  select * from usertbl where addr in ('서울','경기');
  

---

• 1. ++ 조건에 들어가는 것 : 문자열
  • 1. 정확한 매칭 : 공백/대소문자
  • 1. 유사매칭 : 성이 김이신 고객분들에게만 ~~ 발송하겠다
  • 1. 좀 더 규칙성 기반 매칭 : 정규식을 사용하면 되긴 하는데 일단 skip하겠다
  • ex) 고객들 중에서 성이 김씨인 분들만 검색해서 이름과 지역을 출력해보자

  select name, addr 
  from usertbl 
  where name like "김%"; 
  -- %: 그 이상도 가능 (김 뒤에 몇 글자 오든 다 가능)
  

  • ex) 고객들 중에서 성이 김씨이면서, 외자이신 분들을 찾아주세요

  select name, addr 
  from usertbl 
  where name like "김_"; -- _ : 1칸의 공간을 지정함
  

  • ex) 이름에 2번쨰 글자가 용 이신 분들이 있다면 다 찾아라

  select * 
  from usertbl 
  where name like "_용%";
  

  • 문자열 유사 검색 : like
    • % : 포함해도 되고 그 이상 몇 글자가 오든 상관 없음
    • _ : 이 칸은 꼭 뭐라도 있어야함 공간지정
  • ==> 정규식 까지는 아니어도 간단한 부분들에 대한 검색할 떄 사용!!!

---

정리

• 필터링
  • = 정확한 필터링 규칙 + null 체크 : is null, is not null
  • like : 문자열에서 유사 검색으로 규칙
  • 여러개 조건 : and/ or etc
    • 숫자 범위 : between A and B
    • 여러 값 : in (리스트업)