# 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 : 테이블과 테이블을 결합해서 출력하는 방법

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 : 두개의 쿼리를 실행하고 나온 결과를 합쳐주는 방법
# 중복된 데이터를 제거하여 출력한다
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 : 쿼리안에 쿼리가 있는 문법
# 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(OR) / ALL(AND)
# 한국이나 브라질 보다 인구수가 많은 국가의 국가코드, 국가이름, 인구수 출력
select code, name, population
from country
where population > ANY (
	select population from country where code in ("KOR", "BRA"));

use fds;

# 데이터 타입
# INT
create table number1(data tinyint);
create table number2(Data tinyint unsigned);

# 현재 데이터 베이스의 테이블 확인
show tables;

# 테이블 스키마 확인
desc number1;
desc number2;

# 데이터 저장
insert into number1 value (127);
select * from number1;

insert into number2 value (128);
insert into number2 value (-1); # unsigned에서는 음수는 넣지 못 한다.
select * from number2;

# FLOAT(6자리), DOUBLE(17자리)
create table number3(data float);
insert into number3 value (12.3456789);
select * from number3;

create table number4(data double);
insert into number4 value (1234567890.123456789);
select * from number4;

# DB 생성
create database test;
use test;

# 테이블 생성
create table user1(
	user_id int,
	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) unique,
    age int default 30,
    rdate timestamp
);

# INSERT
desc user1;
insert into user1 (user_id, name, email, age, rdate)
value (1, 'peter', 'peter@gmail.com', 23, '2018-01-23');

insert into user1 (user_id, name, email, age, rdate)
values (2, 'peter2', 'peter@gmail.com', 23, '2018-01-23'),
(3, 'peter3', 'peter@gmail.com', 23, '2018-01-23'),
(4, 'peter4', 'peter@gmail.com', 23, now());

select * from user1;

desc user2;
insert into user2 (name, email, age)
value ('peter', 'peter@gmail.com', 22);

select * from user2;

# select 결과 데이터
use world;

select countrycode, name, population from city where population >= 900* 10000;

create table city_900(
	countrycode char(3),
    name varchar(50),
    population int
);
desc city_900;

insert into city_900
select countrycode, name, population from city where population >= 900* 10000;

select * from city_900;

# update set
use test;

select * from user1;

# update 시 safe mode로 인해 limit을 안 걸면 update 되지 않는다.
#설정 SQL Editor에서 설정 가능
update user1 set age=50, email="peter@naver.com" where name = "peter" limit 3;

# DELETE

select * from user1;

delete from user1 where rdate > '2018-01-23' limit 5;

# 실행 중인 process 보기와 중단
show processlist;
kill 154;

# TRUNCATE : 스키마만 남기고 모두 삭제(DDL)
select * from user1;
truncate user1;

# DROP : 테이블 삭제
drop table user1;
show tables;

# Functions
select ceil(12.345), round(12.345, 2), truncate(12.345, 2);

# date_format
use sakila;
select date_format(payment_date, "%Y-%m") from payment;

# concat
use world;
select code, name, concat(code, "/", name) from country;

# count
select count(*) from country;
select count(distinct(continent)) from country;

# if
# 도시의 인구가 100만 이상이면 big, 100만 미면 small 출력하는 컬럼을 생성
select name, population, if(population > 100*10000, "big", "small") as scale from city;

# case
# 국가별 인구가 10억 이상(big), 1억 이상 (medium), 1억 이하는 (small)
select name, population,
	case
		when population >= 100000 * 10000 then 'big'
        when population >= 10000 * 10000 then 'medium'
        else 'small'
	end as scale
	from country order by population desc;

# GROUP BY, HAVING
# 여러개의 동일한 데이터를 가지는 지정한 컬럼의 데이터를 합쳐주는 방법
# 결합함수 : count, max, min, sum, avg ...

#city 테이블에서 국가코드별 도시의 개수 출력
select countrycode, count(countrycode) as count from city group by countrycode order by count desc;

# country 테이블에서 대륙별 총 인구수 출력 3위까지 출력
select continent, sum(population) as total_population from country group by continent order by total_population desc limit 3;

# having
# 대륙의 인구수가 5억 이상인 대륙만 출력
select continent, sum(population) as total_population from country group by continent having total_population >= 500000000;

select ifnull(continent, 'total'), ifnull(region, 'total'), count(region) from country group by continent, region with rollup;

# 변수 선언
set @data = 1;
select @data;

set @rank =0;
select @rank := @rank + 1 as ranking, countrycode, name, population from city order by population desc limit 5;

# 외례키
# 데이터의 무결성을 지키기 위한 제약조건
use test;
drop table user2;

# 아래는 무결성이 깨지는 경우
create table user(
	user_id int primary key auto_increment,
    name varchar(20),
    addr varchar(20)
);

create table money(
	money_id int primary key auto_increment,
    income int,
    user_id int
);

insert into user(name, addr) values ('po', 'seoul'), ('jo', 'pusan');
select * from user;

insert into money(income, user_id) values (5000, 1), (6000, 3);
select * from money;

#  테이블을 생성할 때 설정, 무결성을 위한 외례키
drop table money;

create table money(
	money_id int primary key auto_increment,
    income int,
    user_id int,
    foreign key (user_id) references user(user_id)
);
insert into money(income, user_id) values (5000, 1), (6000, 3); # user_id가 서로 맞지 않으면 에러 발생
insert into money(income, user_id) value (5000, 1);
select * from money;

# 테이블 생성 후 수정해서 설정
drop table money;

create table money(
	money_id int primary key auto_increment,
    income int,
    user_id int
);
alter table money add constraint fk_user foreign key (user_id) references user (user_id);
desc money;
insert into money(income, user_id) values (5000, 1), (6000, 3); # user_id가 서로 맞지 않으면 에러 발생
insert into money(income, user_id) value (5000, 1);
select * from money;

# user(1, 2), money(1, 2)
delete from user where user_id =1 limit 10; # 무결성을 위해 서로 참조하는 데이터는 삭제가 불가능

# on delete, on update 설정
# cascade : 참조되는 테이블에서 데이터를 삭제하거나 수정하면 참조하는 테이블에서도 삭제, 수정한다.
# set null : 삭제, 수정하면 참조하는 테이블에서는 null로 바뀐다.
# no action : 참조하는 테이블에서 삭제하거나 수정하면 변경되지 않는다.(무결성이 깨지기 때문에 지양)
# set default : 참조하는 테이블에서 삭제하거나 수정하면 기본값으로 설정된다.
# restrict : 삭제, 수정에 대해 불가능하게 본다. (strict mode)

# 업데이트되면 같이 업데이트, 삭제되면 null로 변경
# on update > cascade, on delete > set null 설정
drop table money;

create table money(
	money_id int primary key auto_increment,
    income int,
    user_id int,
    foreign key (user_id) references user(user_id)
    on update cascade on delete set null
);
insert into money(income, user_id) values (5000, 1), (6000, 2);
select * from money;

update user set user_id = 3 where user_id = 2 limit 1;
select * from user;

delete from user where user_id = 3 limit 1;

use world;

# SELECT: 데이터 조회
select * from country;

select code, name, continent, population from country;

select countrycode as code, name from city;

# where : 조건으로 데이터를 검색
# 인구가 1억명이 넘는 국가의 국가코드와 국가이름, 인구수를 출력

select code, name, population from country where population >= 100000000;

# mysql 주석
-- 한줄 주석alter
/*
여러줄 주석
*/

# 연산자 : 산술, 비교, 논리
# 산술 : +. -. *, /, %, DIV(몫)

# 인구밀도
select name, surfaceArea, population, population / surfacearea as pps from country;

# 국가별 1인당 GNP 출력
select name, gnp, population, gnp / population * 1000000 as gpp from country;

# 비교 연산자
# =, !=, >, <, >=, <=
select name, population from city where population >= 800*10000;

# 논리연산자 : AND, OR
select name, population from country where population >= 800*10000 and population <= 1000*10000;
# BETWEEN : 범위 연산 시 사용
select name, population from country where population between 800*10000 and 1000*10000;

# 아시아와 아프리카가 대륙의 국가 데이터 출력
select name, continent from country where continent = 'asia' OR continent = 'africa';

# IN, NOT IN : 컬럼에 여러개의 데이터를 포함하는 데이터를 출력할 때
select name, continent from country where continent in ('asia', 'africa');

# LIKE : 특정한 문자열이 포함된 데이터를 출력
# governmentform 이 Republic 포함된 데이터를 출력
select name, governmentform from country where govermentform like "%Republic%";

# 국가 코드가 ar로 시작하는 국가의 이름을 출력
select code, name from country where code like "ar%";

# ORDER BY : 결과 데이터 정렬
# asc: 오름차순(생략 가능)
select  code, name, gnp from country order by gnp asc;

# desc: 내림차순
select  code, name, gnp from country order by gnp desc;

# 국가 코드 순으로 내림차순, 같은 국가 코드는 인구수 순으로 오름차순 정렬
select countrycode, name, population from city order by countrycode desc, population asc;

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

# 인구수가 상위 5위에서 8위까지 출력
select name, population from country order by population desc limit 5, 4;

# DISTINCT : 출력되는 데이터에서 중복을 제거해서 출력
select distinct continent from country