[패스트캠퍼스] 데이터 분석 부트캠프 17기 - 8주차 (1) MySQL 기본

 

1. 조건에 맞는 데이터 가져오기 

 

연산자

==, >=, !=, NOT, AND, OR, BETWEEN, IN

 

와일드 카드

% : 0개 이상의 문자

_ : 1개의 문자

 

'%e' : e로 끝나는 문자열

'%e%' : e를 포함한 문자열

'_e' : e로 끝나고 앞에 1개의 문자가 있는 문자열

'%_e_%' : e를 포함하고 e 앞 뒤로 각각 1개 이상의 문자가 있는 문자열

 

LIKE 사용법

SELECT [컬럼이름]

FROM [테이블이름] 

WHERE [컬럼이름] LIKE [검색할 문자열];

 

NULL 데이터 다루기

[컬럼 이름] IS NULL : NULL인 로우만 선택, IS NOT NULL

* 컬럼이름 == NULL 이런 형식은 사용 불

SELECT [컬럼이름]

FROM [테이블이름] 

WHERE [컬럼이름] IS NULL;

 

2. 실습

MISSION (1)

SELECT type FROM mypokemon WHERE name=='eevee';

 

MISSION (2)  

SELECT attack, defense FROM mypokemon WHERE name == 'caterpie';

 

MISSION (3)

SELECT * FROM mypokemon WHERE weight > 6;

 

MISSION (4)

SELECT name FROM mypokemon WHERE height>0.5 and weight>=6;

 

MISSION (5)

SELECT name as weak_pokemon FROM mypokemon WHERE attack < 50 or defense < 50;

 

MISSION (6)

SELECT * FROM mypokemon WHERE type != 'normal';

 

MISSION (7)

SELECT name, type FROM mypokemon WHERE type in ('normal', 'fire', 'water', 'grass');

 

MISSION (8)

SELECT name, attack FROM mypokemon WHERE attack between 40 and 60;

 

MISSION (9)

SELECT name FROM mypokemon WHERE name LIKE '%e%';

 

MISSION (10)

SELECT * FROM mypokemon WHERE name LIKE '%i%' and speed <= 50;

 

MISSION (11)

SELECT name, height, weight FROM mypokemon WHERE name LIKE '%chu';

 

MISSION (12)

SELECT name, defense FROM mypokemon WHERE name LIKE '%e' and defense <50;

 

MISSION (13)

SELECT name, attack, defense FROM mypokemon WHERE attack-defense <= -10 or attack-defense >= 10;

 

MISSION (14)

SELECT name, attack+defense+speed as total FROM mypokemon WHERE attack+defense+speed >= 150;

 

---

3. 데이터 줄세우기 ORDER BY

 

SELECT [컬럼이름]

FROM [테이블이름] 

WHERE 조건식

ORDER BY [컬럼 이름] ASC/DESC;

 

*SELECT된 컬럼이름들을 1,2,3 번호로 접근 가능하다

 

---

4. 데이터 순위 만들기 RANK, ROW_NUMBER

 

SELECT [컬럼이름], ..., RANK() OVER (ORDER BY [컬럼 이름] DESC)

FROM [테이블이름] 

WHERE 조건식;

 

RANK : 공동 순위가 있으면 다음 순서로 건너 뜀

DENSE_RANK : 공동 순위가 있어도 다음 순위를 뛰어 넘지 않음

ROW_NUMBER : 공동 순위를 무시함

---

5. 문자형 데이터

결과를 새로운 컬럼으로 반환함

 

LOCATE : 문자가 여러 개면 가장 먼저 찾은 문자의 위치 가져옴, 찾는 문자가 없다면 0을 가져옴

SUBSTRING : 입력한 숫자가 문자열의 길이보다 크다면, 아무것도 가져오지 않음

---

6. 숫자형 데이터

 

ROUND : 자릿수에 0을 입력하면 정수를 반환한다

MOD :  숫자B에 2를 넣으면 숫자A가 짝수인지 홀수인지 확인할 수 있

---

 

7. 날짜형 데이터

---

8. 실습(1)

MISSION (1)

SELECT name, length(name) FROM mypokemon ORDER BY 2;

MISSION (2)

SELECT name, RANK() OVER (ORDER BY attack desc) as defense_rank

FROM mypokemon;

MISSION (3) 

SELECT name, timediff( '2022-02-14', capture_date) as days 

FROM mypokemon;

---

9. 실습(2)

MISSION (1)

SELECT right(name, 3) as last_char FROM pokemon;

MISSION (2)

SELECT left(name, 2) as left2 FROM pokemon;

MISSION (3)

SELECT replace(name, 'o', 'O') as bigO FROM pokemon WHERE name LIKE '%o%';

MISSION (4)

SELECT name, upper(concat(left(type,1), right(type,1))) as type_code FROM pokemon;

MISSION (5)

SELECT * FROM pokemon WHERE length(name) > 8;

MISSION (6)

SELECT round(avg(attack)) as avg_of_attack FROM pokemon;

MISSION (7)

SELECT  floor(avg(defense)) as avg_of_defense FROM pokemon;

MISSION (8)

SELECT name, power(attack,2) as attack2 FROM pokemon WHERE length(name) < 8;

MISSION (9)

SELECT name, mod(attack,2) as div2 FROM pokemon;

MISSION (10)

SELECT name, abs(attack-defense) as diff FROM pokemon WHERE attack <= 50;

MISSION (11)

SELECT current_date() as now_date, current_time() as now_time;

MISSION (12)

SELECT month(capture_date) as month_num, monthname(capture_date) as month_eng

FROM pokemon;

MISSION (13)

SELECT dayofweek(capture_date) as day_num. dayname(capture_date) as day_eng

FROM pokemon;

MISSION (14)

SELECT year(capture_date) as year, month(capture_date) as month, day(capture_date) as day

FROM pokemon;