SQL로 하는 데이터 분석 | 2. 데이터 조회 | 작성 순서, 문자열 패턴, 날짜, AND-OR, ORDER BY, LIMIT

[코드잇] SQL로 하는 데이터 분석 '강의를 수강하고 정리한 내용입니다.

---

목차

1. 데이터베이스 기본 개념 및 테이블 생성
2. 데이터 조회
3. 데이터 분석
4. 테이블 조인을 통한 깊이있는 데이터 분석
5. 서브쿼리와 뷰를 활용한 유연한 데이터 분석

 

2. 테이블 조회

 

쿼리 실행 - ctrl + enter(드래그 후 실행시 드래그한 쿼리만 실행)

쿼리 전체 실행 - ctrl +shift +enter

 

SQL 작성법

1. SQL문 끝에는 항상 세미 콜론 써줄것
2. SQL 문 안에는 공백이나 개행등을 자유롭게 넣을 수 있음(문장 구분은 세미콜론 기준)
3. SQL문의 대소문자 구분 문자 - 대문자 : 예약어, 사용자지정부분 - 소문
4. 데이터 베이스 이름과 테이블 이름

• 데이터 베이스이름.테이블이름 - 데이터 베이스 이름 뒤에 점.을 붙이고 테이블 이름을 쓰는것 - 해당 데이터 베이스 안의 테이블을 가리키는 것
• SHEMAS 패널 에서 사용할 데이터 베이스 이름을 선택 - from 다음에 테이블 이름만 적어줘도됨
• SQL문 : USE 사용할데이터베이스명 ; - 맨 앞에 사용할 데이터 베이스를 선언하면 다음 쿼리 부터는 테이블 이름만 써도

 

SQL의 작성 순서 SF / WGH / OL

1. SELCET - 어떤 컬럼 가져올거야?

2. FROM - 어떤 TABLE에서 가져올거야?

3. WHERE - 어떤 ROW가져올거야? (행의 조건)

4. GROUP BY - 그룹 어떻게 형성할거야?

5. HAVING - 어떤 그룹이야?(그룹의 조건)

6. ORDER BY - 최종적으로 어떻게 정렬?

7. LIMIT - 몇개 제한으로?

 

SQL의 실행 순서

1. FROM : 어떤 테이블을?

2. WHERE : 어떤 ROW를? // TABLE과 ROW 먼저 지정

3. GROUP BY : 어떤 그룹으로?

4. HAVING : 어떤 조건의 그룹을?

5. SELECT : 어떤 컬럼들을? // SELECT로 다 가져온 후에 , 정렬+제한

6. ORDER BY : 어떻게 정렬을? 

7. LIMIT : 최종 출력할 ROW중 어떤 ROW를?

 

[특정 패턴 조회하기]

문자열 패턴 매칭 (LIKE + 와일드 카드)

• LIKE : 문자열 패턴 매칭 조건을 걸기 위해 사용되는 키워드
• % : 임의의 길의를 가진 문자열(0자도 포함)
• _ : 한 자리의 문자

1. BETWEEN A and B(A부터B까지)
SELECT * FROM [테이블명] WHERE number BETWEEN 1 and 3;
SELECT * FROM [테이블명] WHERE number BETWEEN '2019-01-01' and '2019-08-31';

2. 이 중에 있는~ (IN)
SELECT * FROM [테이블명] WHERE text IN (1, 2, 3);
SELECT * FROM [테이블명] WHERE text IS NULL;

3. 와일드 카드 (임의의 길이의 문자열%, 한 글자를 나타내는 _)
SELECT * FROM [테이블명] WHERE text LIKE '서울%';
SELECT * FROM [테이블명] WHERE text LIKE '%서울%';
SELECT * FROM [테이블명] WHERE date LIKE '____-08%'; // 8월 데이터만 불러오는 쿼
SELECT * FROM [테이블명] WHERE email LIKE 'c___@%'; // 이메일 주소가 c로 시작하고 4글자

4. 같지 않음 (!=, <>)
SELECT * FROM [테이블명] WHERE text != 'male';
SELECT * FROM [테이블명] WHERE text <> 'male';

 

 

 

문자열 패턴 매칭에서 2가지 주의점

 

1. 이스케이핑(escaping) 문자 → \역슬래쉬

 

% 앞에 \역슬래쉬(백슬래쉬, backslash) 기호 → '문자로서의 %'로 인식

예. SELECT * FROM test WHERE sentence LIKE ‘%%%’ → 두번째 %는 문자로 인식 , 냠%뇽 같은 걸 조회

(1) ‘(작은따옴표) 이스케이핑 : LIKE ‘%\’%’ → i’m a teacher

(2) _(언더바) 이스케이핑 : LIKE ‘%_%’ → Nice to meet you_^^!

(3)” (큰따옴표) 이스케이핑 : LIKE ‘%%”\”%’ → “ Spyder man”

 

2. 대소문자 구분 문제

조건식에 소문자를 써도 대문자가 포함된 row도 함께조회

방법1 .MySql 기본설정을 바꿀 수는 있으나 권장되지 않음.

방법 2. BINARY ‘%g%’ → 소문자 g가 포함된 문자열만 조회됨 예. good afternoon!

 

 

[ DATE 데이터 타입 관련 함수 ]

 

1. 연도, 월, 일 추출

(1)1992년에 태어난 회원 조회
SELECT * FROM [테이블명] WHERE YEAR(date) = '1992';

(2) 여름(6, 7, 8월)에 가입한 회원들만 조회하기
SELECT * FROM [테이블명] WHERE MONTH(sign_up_day) IN(6,7,8);

(3) 각 달의 후반부(15~31일)에 가입했던 회원들만 조회
SELECT * FROM [테이블명] WHERE DAYOFMONTH(sign_up_day) BETWEEN 15 AND 31;

 

 

2. 날짜 간의 차이 구하기

DATEDIFF( 날짜 a, 날짜 b): 차이일 수를 알려줌

예: 가입일자가 2019년 기준 며칠 이후 인지확인
SELECT email, sign_up_day, DATEDIFF(sign_up_day, '2019-01-01') FROM 테이블명;

예. 오늘 날짜 기준으로 확인하기
SELECT email, sign_up_day, CURDATE(), DATEDIFF(sign_up_day, CURDATE()) FROM 테이블명;

예. 회원들이 몇살일때 가입했는지(가입일 - 생일 / 365)
SELECT email, sign_up_day, DATEDIFF(sign_up_day, birthday)/ 365 FROM member;

 

 

 

3. 날짜 더하기 빼기

DATE_ADD()더하는 함수 DATE_SUB() 빼는 함수

예. 가입일 기준 300일 이후
SELECT email, sign_up_day , DATE_ADD(sign_up_day, INTERVAL 300 DAY) FROM 테이블명;

예. 가입일 기준 250일 이전의 날짜
SELECT email, sign_up_day, DATE_SUB(sign_up_day, INTERVAL 250 DAY) FROM 테이블명;

 

 

4. UNIX Timestamp 값

UNIX_TIMESTAMP DATE 타입의 값을 Unix Timestamp(1970년 1월 1일 기준 얼마나 시간이 지났는지 확인)로 바꿔주는 함수

FROM_UNIXTIME() Unix timestamp를 읽을 수 있는 datetime날짜로 바꾸는 함수

SELECT email, sign_up_day, UNIX_TIMESTAMP(sign_up_day) FROM member;

SELECT email, sign_up_day, FROM_UNIXTIME(UNIX_TIMESTAMP(sign_up_day)) FROM member;

• 날짜, 시간 관련 데이터 타입 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html
• 날짜, 시간 관련 함수 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

 

 

 

[ 여러 개의 조건 걸기 ]

 

WHERE id = 1 OR id = 2 라고 적었어야할 부분에, WHERE id = 1 OR 2 라고 적어버리는 실수

 

 

AND, OR

SELECT * from member
WHERE gender  ='m'
AND address LIKE '서울%'
AND age BETWEEN 25 and 29;

SELECT * FROM member 
WHERE MONTH(sign_up_day) BETWEEN 3 AND 5
    OR MONTH(sign_up_day) BETWEEN 9 AND 11;

 

 

AND와 OR간의 우선순위

• OR가 AND 보다 우선순위가 높음
• 연결 시에 로 먼저 실행할 조건에 ( )

SELECT * FROM member 
WHERE (gender = 'm' AND height >= 180)
  OR (gender ='f' AND height >= 170);

 

 

AND 조건이 여러개 → IN 으로 묶기

SELECT * FROM member WHERE age = 20 OR age = 30 OR age = 40;
→ SELECT * FROM member WHERE age IN (20, 30, 40);

 

 

 

 

[ 테이블 정렬하기 ASC, DESC ]

 

ORDER BY 컬럼명 DESC(내림차순), ASC(오름차순)

먼저 쓴 컬럼 기준으로 차례대로 정렬, 가입연도 → 이메일 컬럼 기준으로 정렬됨.

SELECT * FROM member
ORDER BY YEAR(sign_up_day) DESC , email ASC;

 

숫자형(INT 등)인 경우와, 문자열형(TEXT 등)에 따른 정렬

• 문자열형일 경우, 수의 크기가 아니라 한 문자씩 비교 → 120, 19, 230, 27 순으로 정렬

CAST(컬럼명 AS signed/decimal) 데이터 타입을 일시적으로 변환하는 함수

signed : 양수와 음수를 포함한 모든 정수 decimal :소수점이 있는 수 타입

SELECT * FROM member ORDSE BY CAST(data AS signed)

 

 

[ 데이터 일부만 추려보기 LIMIT ]

LIMIT n (row의 개수)

LIMIT m, n (row 0을 기준으로 하는 시작 row번호, row의 개수)

FROM - WHERE - ORDER BY - LIMIT 순

 

 

앞에서 10개 데이터까지만 보기 → LIMIT 10

SELECT * FROM member
ORDER BY sign_up_day DESC
LIMIT 10;

앞에서 9번째부터 2개의 데이터 보기 → LIMIT 8, 2 (row는 0부터 셈)

SELECT * FROM member
ORDER BY sign_up_day DESC
LIMIT 8,2;