Nyong's Blog
0%

210309_TIL(DB_3)

Posted on Edited on In

오늘 배운 것

DB

DDL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# DDL : 데이터 정의어
# CREATE, ALTER, DROP
# DB와 테이블을 조작
create database test;
use test;
select database();

# alter : 데이터 베이스나 테이블 수정
show variables like "character_set_database";
alter database test character set = utf8;
desc user;

# alter ADD : 테이블에서 컬럼 추가
alter table user add age int;
desc user;

# alter MODIFY : 테이블에서 컬럼 수정
alter table user modify age char(3);
desc user;

# DROP : 데이터 베이스나 테이블 삭제
truncate money; # 테이블 초기화
select * from money;
drop table money;
show tables;

JOIN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
# JOIN : 테이블과 테이블을 결합해서 출력하는 방법

create table user (
	user_id int primary key auto_increment,
    name varchar(20) default null
);
create table addr (
	addr_id int primary key auto_increment,
    addr varchar(20),
    user_id int
);
insert into user(name) values ("jin"), ("po"), ("alice");
select * from user;
insert into addr(addr, user_id) values ("seoul", 1), ("busan", 2), ("daegu", 4), ("seoul", 5);
select * from addr;

# INNER JOIN
select user.user_id, user.name, addr.addr from user join addr on user.user_id = addr.user_id;
select * from user join addr;

# LEFT JOIN
select user.user_id, user.name, addr.addr from user left join addr on user.user_id = addr.user_id;
select addr.user_id, user.name, addr.addr from addr left join user on user.user_id = addr.user_id;

# RIGHT JOIN
select user.user_id, user.name, addr.addr from user right join addr on user.user_id = addr.user_id;
select user.user_id, user.name, addr.addr from addr right join user on user.user_id = addr.user_id;

# world DB에서 국가코드, 도시이름, 국가이름, 국가인구, 도시인구 출력
use world;
select code, city.name as city_name, country.name as country_name,
	country.population as country_pop, city.population as city_pop,
	round(city.Population/country.population * 100, 2) as rate
    from country join city on country.code = city.CountryCode having rate >= 20 order by rate desc;

# 국가별 언어별 사용 인구를 출력
select country.name, countrylanguage.language, round(country.population * countrylanguage.percentage * 0.01) as lp
	from country join countrylanguage on country.code = countrylanguage.CountryCode;

# INNER JOIN 시에 단축할 수 있음
select country.name, countrylanguage.language, round(country.population * countrylanguage.percentage * 0.01) as lp
	from country, countrylanguage where country.code = countrylanguage.CountryCode;

# 대륙별 사용언어의 수를 출력
select distinct(country.continent), count(countrylanguage.language)
from country join countrylanguage on country.code = countrylanguage.CountryCode
group by continent;

UNION

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# UNION : 두개의 쿼리를 실행하고 나온 결과를 합쳐주는 방법
# 중복된 데이터를 제거하여 출력한다
use test;
select name from user union all select addr from addr;

select user.user_id, user.name, addr.addr
from user
left join addr
on user.user_id = addr.user_id;

select addr.user_id, user.name, addr.addr
from user
right join addr
on user.user_id = addr.user_id;

sub Query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# sub query : 쿼리안에 쿼리가 있는 문법
# select, from, where

# select : 전체 나라수, 전체 도시수, 전체 언어수 출력 (한개의 row로)
use world;
select
(select count(*) from country) as total_country,
(select count(*) from city) as total_city,
(select count(distinct(language)) from countrylanguage) as total_language
from dual;

# FROM : 800만 이상이 되는 도시의 국가코드, 국가이름, 도시이름, 도시인구수 출력
# JOIN만 사용
# join은 테이블 끼리의 join을 먼저 실행하기 때문에 모든 조합을 만들고 조건에 따라 filter함
select country.code, country.name, city.name, city.population
from city join country on city.countrycode = country.code having city.population >= 800 * 10000;

# sub query 사용
# join과 다르게 처음부터 조건에 맞는 조합만을 만들기 때문에 서브 쿼리문을 잘 활용하면 효율적인 SQL문 작성이 가능하다.
select country.code, country.name, city.name, city.population
from (select countrycode, name, population from city where population >= 800 * 10000) as city
join country on city.countrycode = country.code having city.population >= 800 * 10000;

# where : 900만 이상의 인구가 있는 도시의 국가코드, 국가이름, 대통령 이름 출력

select code, name, headofstate from country
where code in (select countrycode from city where population >= 900*10000);

ANY / ALL

1
2
3
4
5
6
# ANY(OR) / ALL(AND)
# 한국이나 브라질 보다 인구수가 많은 국가의 국가코드, 국가이름, 인구수 출력
select code, name, population
from country
where population > ANY (
	select population from country where code in ("KOR", "BRA"));

Nyong’s GitHub