[23일차] BDA과정 Business Analyst를 위한 핵심 SQL 실전

[KDT] 패스트캠퍼스 비즈니스 데이터 분석가 양성과정 23일 차

BDA과정 Business Analyst를 위한 핵심 SQL 실전

---

안녕하세요 ㅎㅎ 오늘은 어제와 같이 Business Analyst를 위한 핵심 SQL 실전 주제로 실강이 진행되었습니다.

SQL의 여러 쿼리문을 작성해보면서 익숙해지는 시간이였습니다. 오늘 작성했던 쿼리들 올려보겠습니다.

 

 

where 조건절 사용

# where절 명령어 
# 인구가 7000만 ~ 1억인 국가를 출력,code ,name, populatoin
select code ,name , population
from country
where (population >= 7000* 10000) and (population <=10000*10000)

#인구수가 5천만 이상인 아시아와  아프리카대륙의 데이터를 출력하세요 
#code ,name , population, continent
select code ,name , population, continent
from country
where (population >=5000*10000) 
and (continent = "africa" or continent = "Asia" )

 Like 특정문자열 포함 

#Like:특정 문자열 포함 
#GovernmentForm : Republic인 데이터 출력 
select code ,name ,GovernmentForm
from country
where GovernmentForm like "%Republic%"​

Between 범위 출력 시 간결하게 사용 가능 

#between 범위 출력 시 간결하게 가능 
select code ,name , population
from country
where population between >= 7000* 10000 and <=10000*10000​

Order by 데이터정렬(asc 오름차순 , desc 내림차순)

# Order by :데이터 정렬 
# 국가의 인구수가 8천만 이상인 국가들을 출력하고, 인구수 순으로 내림차순 정렬 
select code,name, population
from country
where population >=8000*10000
order by population desc
# asc 오름차순은 default

# 여러개의 컬럼을 기준으로 정렬 
select countrycode , name ,population
from city 
order by countrycode desc ,population asc

Limit 조회되는 데이터의 수를 제한 

# LIMIT :조회되는 데이터의 수를 제한
#인구가 많은 상위 5개 국가의 데이터를 출력 
select code , name , population
from country 
order by population desc limit 5

# 인구수 5위~ 7위까지 출력
#Limit 4(skip), 3(limit)
select code , name , population
from country 
order by population desc 
limit 4,3

DISTINCT 중복데이터제거 

# DISTINCT 중복데이터 제거 
# city 테이블
# 도시의 인구수가 100만 ~ 200만 사이의 도시를 가지고 있는 국가의 코드 출력
select distinct countrycode
from  city
where population between 100*10000 and 200*10000

---

CREATE USE ALTER DROP ( DDL )

 

# 테이블 생성 CREATE TABLE ( column_name_1 column_data_type_1 column_constraint_1, column_name_2 column_data_type_2 column_constraint_2, ... )

 

create table user1 (
     user_id int primary key  auto_increment,
     name varchar(20) ,
     email varchar(30) ,
     age int ,
     rdate date
     );

create table user2 (
     user_id int primary key  auto_increment,
     name varchar(20) not null,
     email varchar(30) not null unique,
     age int default 30,
     rdate timestamp
     );

 

ALTER

# ALTER 수정 명령어 
SHOW VARIABLES LIKE "character_set_database" ; # 사용중인 데이터베이스의 인코딩 방식 확인
alter database test character set = utf8;

 

ADD

# add column
alter table  user2 add tmp text ;

Modify

# modify column
alter table user2 modify column tmp int ;

Drop

# drop column
alter table user2 drop tmp ;

#데이터 베이스 삭제
drop database tmp;

 #테이블 삭제
drop table num1;
drop table num2;

DML :CRUD
create : insert into
read : select from 
upadate : update set
delete : delete from 

 

Insert into  values 데이터 넣기

INSERT INTO user1(user_id, name, email, age, rdate)
VALUES (1, "jin", "pdj@gmail.com", 30, now()),
(2, "peter", "peter@daum.net", 33, '2017-02-20'),
(3, "alice", "alice@naver.com", 23, '2018-01-05'),
(4, "po", "po@gmail.com", 43, '2002-09-16'),
(5, "andy", "andy@gmail.com", 17, '2016-04-28'),
(6, "jin", "jin1224@gmail.com", 33, '2013-09-02');

Insert into select 

insert into city_800  
select countrycode, name , population
from city
where population >= 800*10000;

Update

update user2 
set name = "jin" , age = 40  
where name = "lone"  
limit 5 # workbench 안에서 오류를 줄이기 위해 limit을 설정해줘야 작동

update user2 
set name = "jin"  
# where 조건절 없이 update 실행 시 전체 데이터에 영향 
# 위의 경우가 발생 시 밑에 쿼리 실행 

select * from user2 
show processlist # 현재 진행되고 있는 쿼리 리스트가 나옴 
kill id  # show processlist로 진행되고 있는 쿼리 확인 뒤 kill로 멈춤

Delete 

 #CRUD : Delete
 delete from user2 
 where age < 37
 limit 2 ;

Truncate 

 truncate user2;  #테이블 초기화

 

---

Functions 1 (CEIL, ROUND, TRUNCATE, DATE_FORMAT, CONCAT, COUNT)

 

CEIL, ROUND, TRUNCATE는 소수점 올림, 반올림, 버림 함수입니다.

# ceil 실수 데이터를 올림 할 때 사용합니다.
SELECT CEIL(15.25);

#ROUND 실수데이터를 반올림 할 때 사용합니다.
#소수 둘째자리까지 나타내고 소수 셋째자리에서 반올림
SELECT ROUND(15.845, 2);

# TRUNCATE 실수 데이터를 버림 할 때 사용합니다.
# 12.345를 소수 둘째자리까지 나타내고 소수 셋째자리에서 버림
SELECT TRUNCATE(13.541, 2);

 

DateFormat

# DATE_FORMAT 날짜 데이터에 대한 포멧을 바꿔줍니다.
select amount , payment_date , 
		distinct (date_format(payment_date ,"%Y-%m")) as unique_month
from payment

Cocat 컬럼과 컬럼 결합

# CONCAT 문자열을 합쳐주는 기능을 합니다.

select code , name , concat( name , "(", code, ")") as fullname ,population
from country
where population >= 10000*10000

조건문 IF , Case when then

# IF(조건, 참, 거짓 ) 하나의 조건 확인 , 조건이 여러개일 경우에는 case when then 
# 국가인구가 1억이 넘으면 "big" , 아니면 "small"을 출력하는 쿼리 생성 

select code , name , population 
		, if (population >= 10000*10000, "big", "small") as scale 
from country
group by scale



# 국가인구가 1억이 넘으면 "big" , 5천만이 넘으면 "medium" 아니면 "small"을 출력

select code , name, population 
		, case 
				when population >= 10000*10000 then  "big "
            	when population >= 5000*10000  then "medium"
            	else  "small"
            	end    as scale 
from country

GROUP BY 는 여러개의 동일한 데이터를 가지는 특정 컬럼을 합쳐주는 역할을 하는 명령입니다.

 

# 특정컬럼을 기준으로 중복되는 데이터를 결합 > 다른 컬럼은 결합함수로 데이터를 결합 
# 특정컬럼, 결합함수 
# 결합함수: count , max, min , avg , sum ... etc 

# 국가별 도시의 갯수를 출력
select countrycode , count(countrycode)
from city
group by countrycode
order by city_count desc
limit 5 ;

# country :  대륙별 총 GNP  , 인구 출력 
select continent , sum(GNP) as tot_GNP , sum(population) as tot_population 
					, (sum(GNP) / sum(population) ) as gpp
from country
group by continent , population
order by tot_GNP desc, tot_population desc,

# Having 
# 5억 이상의 인구가 있는 대륙을 출력 
# group by 한후에 조건이 실행되길 원할때 having 사용 / table에서 온 데이터에 조건이 실행되길 원하면 where
select continent , sum(population) as tot_population 
from country
group by continent 
having population >=50000*10000 ;

JOINJ 여러개의 테이블에서 데이터를 모아서 보여줄 때 사용됩니다.

JOIN에는 INNER JOIN, LEFT JOIN, RIGHT JOIN이 있습니다. OUTER JOIN은 UNION을 이용해서 할수 있습니다.

# JOIN 여러개의 테이블에서 데이터를 모아서 보여줄 때 사용됩니다. 
# JOIN에는 INNER JOIN, LEFTJOIN, RIGHT JOIN이 있습니다. OUTER JOIN은 UNION을 이용해서 할수 있습니다.
# RDBMS 에서만 사용가능 NOSQL에서는 안됨 

#INNER JOIN 
select user.uid, user.name, addr.addr_name
from user
join addr
on user.uid = addr.uid;

# from 절에서 join
select user.uid, user.name, addr.addr_name
from user, addr
where user.uid = addr.uid;

#Left join
select user.uid, user.name, addr.addr_name
from user
left join addr
on user.uid = addr.uid;


#Right join
select user.uid, user.name, addr.addr_name
from user
left join addr
on user.uid = addr.uid;

UNION UNION

SELECT 문의 결과 데이터를 하나로 합쳐서 출력합니다. 컬럼의 갯수와 타입, 순서가 같아야 합니다.

UNION은 자동으로 distinct를 하여 중복을 제거해 줍니다. 중복제거를 안하고 컬럼 데이터를 합치고 싶으면 UNION ALL을 사용합니다. 또한 UNION을 이용하면 Full Outer Join을 구현할수 있습니다.

# UNION 세로 방향으로 데이터 결합 , 중복데이터 제거
# UNION을 이욯해서 outer join을 할 수 있다.

select user.uid, user.name, addr.addr_name
from user
union 
select addr_name
from addr 

# union을 이용한 ounter join 
select user.uid, user.name, addr.addr_name
from user
left join addr
on user.uid = addr.uid;
union
select user.uid, user.name, addr.addr_name
from user
left join addr
on user.uid = addr.uid;