# SQL 종류
# DML : 데이터(테이블) : C(create)R(read)U(update)D(delete)
# - command : C(insert into)R(select from)U(update set)D(delete from)
# - between and, in , not in , like
# - order by : asc, desc
# - limit : num(limit) : num(skip), num2(limit)
# DDL : 데이터베이스, 테이블 : CRUD
# - command : C(create)R(show, desc)U(alter)D(drop)
# DCL : 시스템
# DML : READ : select from
select * from world.country;
# 데이터베이스 선택
use world;
# 선택된 데이터베이스 확인
select database();
select * from country;
# MySQL에서는 컬럼이름에 대소문자 구분 x
select Code, Name, Population from country;
# comment : 주석 : --(한줄), /* */ (범위)
# 연산자 : operator
# 산술 : 데이터 + 데이터 = 데이터
# 비교 : 데이터 + 데이터 = 논리값 : 조건 1개
# 논리 : 논리값(비교) + 논리값(비교) = 논리값 : 조건 2개 이상3
# 산술연산자 : +, -, *, /, %
select code, name, population, surfacearea
from country;
# 산술연산자 : +, -, *, /, %
# 인구밀도(인구수/표면적) 컬럼 추가
select code, name, population, surfacearea
, population / surfacearea as pps
from country;
# 국가별 1인당 GNP 컬럼 추가 : code, name, gpp
select *
from country;
# 국가별 1인당 GNP 컬럼 추가 : code, name, gpp
select code, name, gnp / population as gpp
from country;
# 비교연산자 : =, !=, >, <, >=, <= : True(1), False(0)
# 아시아 대륙인 경우 1출력하는 컬럼 추가
select code, name, continent
, continent = 'asia' as is_asia
from country;
# 인구수가 1000만명 넘은 경우 1출력하는 컬럼 추가 : upper_1000
select code, name, population
, population >= 1000 * 10000 as upper_1000
from country;
# 논리연산자 : and(T and T = T), or(F or F = F)
# 아시아 대륙중에 인구수 1000만명 이상이면 1을 출력하는 컬럼 추가
select code, name, continent, population
, continent = 'asia' as is_asia
, population >= 1000 * 10000 as upper_1000
, (continent = 'asia') and (population >= 1000*10000) as is_asia_upper_1000
from country;
# 아시아, 아프리카 대륙이면 1을 출력하는 컬럼 추가 : is_asia_africa
select code, name, continent, population
, continent = 'asia' as is_asia
, continent = 'africa' as is_africa
, (continent ='asia') or (continent = 'africa') as is_asia_africa
from country;
# where : 특정 조건으로 데이터 필터링
select code, name, population
from country
where population >= 1000 * 10000;
# 인구수가 8000만 ~ 1억인 국가 출력
select code, name, population
from country
where population >= 8000 * 10000 and population <= 10000 *10000;
# between and : 범위로 데이터를 필터링할때 사용
select code, name, population
from country
where population between 8000 * 10000 and 10000 * 10000;
# in, not in : 특정 컬럼에서 특정 데이터가 포함된 데이터를 필터링
select code, name, continent
from country
# where (continent = 'asia') or (continent = 'africa');
where continent not in ('asia', 'africa');
# like : 틀정 문자열이 포함된 데이터를 필터링
# % : 아무문자 0개 이상
# 국가코드가 k로 시작하는 데이터 출력
select code, name
from country
where code like '%K%';
# 국가코드에 A가 들어간 국가중에서 인구수가 1000만 ~ 5000만명인 국가를 출력
# 위의 데이터에서 아시아와 아프리카 대륙의 국가만 출력
select code, name, population
from country
where (code like '%A%') and (population between 1000 *10000 and 5000*10000) and (continent in ('asia','africa'));
# order by : 데이터를 정렬하여 출력 : asc, desc
# 인구수 순으로 오름차순하여 정렬
select code, name, continent
from country
order by population asc;
# order by : 데이터를 정렬하여 출력 : asc, desc
# 인구수 순으로 내림차순하여 정렬
select code, name, continent
from country
order by population desc;
# 여러개의 컬럼을 설정하여 정렬
# 국가코드를 내림차순으로 정렬 > 인구수를 오름차순으로 정렬
# 도시 인구수가 50만명 이상인 도시만 출력
select countrycode, name, population
from city
where population >= 50 * 10000
order by countrycode desc, population asc;
# limit : 출력되는 데이터의 갯수를 제한
# 인구수 상위 1위 ~ 5위까지 국가 데이터 출력
# 인구숫 수능로 정렬>5개의 데이터 제한
select code, name, population
from country
where continent = 'asia'
order by population desc
limit 5;
# limit num1 : num1 데이터 갯수까지 출력
# limit num1, num2 : num1만큼 스킵후 num2 데이터 갯수까지 출력
# 아시아 국가 중에서 인구수가 많은 국가 6위 ~ 8위까지 출력
select code, name, population
from country
where continent = 'asia'
order by population desc
limit 5, 3;
# 20개씩 보기 > 3번쨰 페이지
# pageblock = 20, page = 3
# limit (page - 1) * pageblock, pageblock
# distinct : 중복 데이터 제거해서 출력
# 전세계에서 사용되는 언어 목록을 출력
select distinct language
from countrylanguage;
# count() : row 데이터의 갯수를 출력
# 전체 국가의 수를 출력
select *
from country;
# count() : row 데이터의 갯수를 출력
# 전체 국가의 수를 출력
select count(code)
from country;
# 아시아 국가의 수를 출력
select count(code)
from country
where continent = 'asia';
use mgs;
select * from apple;
select count(*) from apple;
# iPhone을 구매한 고객에게 iPad를 구매하라고 추천하는것인 좋을까?
# P(iPad | iPhone) > P(iPad) : 추천하는것이 좋다
# 조건부 확률
# P(iPad|iPhone) = P(iPad & iPhone) / P(iPhone)
# P(iPad) : 0.5004
select
(select count(*) from apple where iPhone =1)
/ (select count(*) from apple) as 'P(iPhone)';
# P(iPad) : 0.4203
select
(select count(*) from apple where iPad =1)
/ (select count(*) from apple) as 'P(iPad)';
# P(iPad & iPhone) : 0.3593
select
(select count(*) from apple where (iPhone = 1) and (iPad = 1))
/ (select count(*) from apple) as 'P(iPad & iPhone)';
# P(iPad | iPhone) = P(iPad & iPhone) / P(iPhone)
select
(select
(select count(*) from apple where (iPhone = 1) and (iPad = 1))
/ (select count(*) from apple))
/
(select
(select count(*) from apple where iPhone = 1)
/ (select count(*) from apple));
# 결론 : P(iPad | iPhone) > P(iPad) : 0.7180 > 0.4203
# 아이폰을 구매한사람에게 아이패드를 추천하는것이 좋다.
############################
# DDL : CREATE, READ
# 데이터베이스 리스트 출력
show databases;
# 테이블 리스트 출력
use world;
show tables;
# 테이블 구조 출력
# 필드명(=컬럼명), 데이터타입, 제약조건(contstraint)
desc country;
# 데이터 타입
# 숫자 : 정수(int), 실수(float)
# 문자열 : 고정길이문자열(char), 가변길이문자열(varchar), 긴문자열(text)
# 날짜 : DATETIME, TIMESTAMP(데이터의 저장날짜시간을 저장)
# 정수 데이터 저장 확인
use mgs;
create table number1(data tinyint);
select * from number1;
show tables;
insert into number1 value(127);
# 실수 데이터 타입
create table number2(data float);
# float은 소수포함 6자리까지만
insert into number2 value(123.456789);
select * from number2;
create table number3(data double);
# double은 17자리 까지만 표현
insert into number3 value(123.567890123456789);
select * from number3;
# 제약조건
# not null : 비어있는 값을 저장 X
# unique : 같은 값을 저장 X
# primary key : not null + unique
# - 일반적으로 하나의 테이블에 1개의 primary key 설정
# default : 데이터 저장시 저장 데이터가 없으면 default 값을 사용해서 저장
# auto_increment : primary key가 설정된 컬럼에 주로 사용
# - 숫자를 1씩 증가시켜 저장하는기능
# DDL : CREATE
# 데이터베이스 생성
create database test;
show databases;
# 테이블 생성 : 필드명, 데이터타입, 제약조건
use test;
create table user(
user_id int primary key auto_increment
, name varchar(20) not null
, email varchar(30) unique not null
, age int default 30
, rdate timestamp default current_timestamp
);
desc user;
# DDL : UPDATE : alter
# database : 인코딩 변경
show variables like 'character_set_database';
alter database test character set = ascii;
alter database test character set = utf8mb4;
# table : column : add, modify, drop
# add
alter table user add contents text;
# modify
alter table user modify column contents varchar(200) not null;
# drop
alter table user drop contents;
desc user;
# DDL : READ(show,desc), CREATE(create), UPDATE(alter)
# DDL : DELETE : drop
# 테이블 삭제
use mgs;
show tables;
drop table number1;
drop table number2;
drop table number3;
show tables;
# 데이터베이스 삭제
create database tmp;
show databases;
drop database tmp;
# DML : CREATE : insert into
use test;
desc user;
# name, email컬럼에 데이터를 저장
insert into user(name, email)
value('peter', 'peter@gmail.com');
select * from user;
insert into user(name, email, age)
values ('alice', 'alice@naver.com', 23)
, ('anchel', 'anchel@daum.net', 32)
, ('jin', 'jin@daum.net', 25);
select * from user;
create table user_30(
user_id int
, name varchar(20)
, age int
);
desc user_30;
# select쿼리를 실행한 결과를 테이블에 저장하기
select user_id, name, age
from user
where age >= 30;
# DML : C(insert into)R(select from)
# DDL : C(create)R(show,desc)U(alter)D(drop)
# DML : UPDATE : update set
select * from user;
update user
set email = 'jin@kakao.com', age = 27
where user_id = 4;
show processlist;
kill 144;
# DML : DELETE : delete from
select * from user;
delete from user
where age <= 30
limit 1;
# 함수들, group by
# functions
# round(), count(), concat(), distinct(), date_format()
# round() : 반올림
select round(12.345, 2);
use world;
select code, name, round(population / surfacearea, 2)
from country;
# count : row 데이터의 갯수 출력
select count(*)
from country;
# concat : 컬럼의 데이터를 결합
# 국가이름(국가 코드)를 출력하는 컬럼을 추가
select code, name, concat(name, '(', code, ')') as name_code
from country;
# distinct : 중복 데이터를 제거
# 전세계 모든 사용 언어를 출력
select distinct(language)
from countrylanguage;
# date_format : 날짜데이터의 포멧을 변경
use sakila;
select * from payment;
# 매출이 발생한 년월 목록을 출력
# 매출일자 데이터 포멧 변경(년월) > 중복데이터 제거
select distinct(date_format(payment_date, '%Y-%m')) as unique_month
from payment;
# 조건문 : if, ifnull, case when then
use world;
# 도시의 인구수가 100만이 넘으면 big, 아니면 small을 출력하는 컬럼 추가
select countrycode, name, population
, if(population >= 100 * 10000, 'big', 'small') as scale
from city;
# 독립년도가 없는 경우에 0 출력
select code, name, ifnull(indepyear, 0)
from country;
# 인구가 1억 이상이면 big, 5000만 이상이면 medium, 5000만 미만이면 small
select name, population
, case
when population >= 10000 * 10000 then 'big'
when population >= 5000 * 10000 then 'medium'
else 'small'
end as scale
from country
order by population desc;
# functions : round, count, concat, distinct, date_format, if, ifnull
# case when then else end
# groupby
# 특정컬럼 :중복데이터 결합, 다른컬럼 : 결합함수로 결합
# 결합함수 : sum(), count(), min(), max(), avg() ...
use world;
# 대륙별 총인구수 출력
select continent, sum(population) as total_population
from country
group by continent
order by total_population desc;
# 국가별 총 도시의 갯수를 출력
select countrycode, count(countrycode) as city_count
, sum(population) as city_population
from city
group by countrycode
order by city_count desc;
use sakila;
# 년월별 총 매출데이터를 출력
select *
from payment;
select date_format(payment_date, '%Y-%m') as date
, sum(amount) as total_amount
from payment
group by date
order by date asc;
# having
use world;
# 대륙별 총 인구수에서 총인구가 5억이상인 대륙 출력
select continent, sum(population) as population
from country
group by continent;
select continent, sum(population) as population
from country
group by continent
having population >= 50000*10000;
# 2개 이상의 컬럼을 기준으로 그룹핑
# with rollup : 그룹핑 된 결과 데이터의 총합을 출력
select continent, region, sum(population) as total_population
from country
group by continent, region
with rollup;
# 스태프와 고객별 매출 데이터의 총합과 매출 횟수를 출력 하세요
# with rollup을 이용해서 스태프별 총 매출과 매출 횟수도 출력.
use sakila;
select * from payment;
select customer_id, staff_id
,sum(amount)
,count(amount)
from payment
group by staff_id, customer_id
with rollup;
################
# SQL 종류
# DML : DATA : C(insert into)R(select from)U(update set)D(delete from)
# - where : operator : 산술, 비교, 논리
# - between and, in, not in, like
# - order by : asc, desc
# - limit : num1(limit) : num1(skip), num2(limit)
# DDL : DATABASE, TABLE : C(create)R(show,desc)U(alter)D(drop)
# - create table : column_name, datatype, constraint
# - datatype
# - 숫자(int,float), 문자열(char,varchar,text), 날짜시간(timestamp,datetime)
# - constraint
# - not null, unique, primary key, default, auto_increment
# DCL : SYSTEM
# Functions : round(), count(), concat(), distinct(), date_format()
# Condition : if(), ifnull(), case when then else end
# group by : 특정컬럼 > 중복결합, 다른컬럼 > 결합함수(min,max,avg,count,sum)
# having : 쿼리 실행 후 데이터 필터링
# join
# 여러개의 테이블을 특정 컬럼을 기준으로 결합해서 데이터를 출력하는 방법
use test;
show tables;
drop table user;
drop table user_30;
create table user(
ui int primary key auto_increment
, name varchar(30)
);
create table addr(
ui int
, an varchar(10)
);
show tables;
insert into user(name)
values ('a'), ('b'), ('c');
select * from user;
insert into addr(ui, an)
values (1, 'Seoul'), (2, 'Pusan'), (4, 'Daegu'), (5, 'Seoul');
select * from addr;
# inner join
select *
from user
join addr
on user.ui = addr.ui;
select user.ui, user.name, addr.an
# inner 조인만 ,가능
from user, addr
where user.ui = addr.ui;
# left join
select user.ui, user.name, addr.an
from user
left join addr
on user.ui = addr.ui;
# right join
select addr.ui, user.name, addr.an
from user
right join addr
on user.ui = addr.ui;
# outer join
# union : select를 실행한 결과를 합쳐서 출력하는 방법
select name from user;
select an from addr;
select user.ui, user.name, addr.an
from user
left join addr
on user.ui = addr.ui
union
select addr.ui, user.name, addr.an
from user
right join addr
on user.ui = addr.ui;
use world;
select * from country;
select * from city;
# 국가코드, 국가이름, 도시이름, 국가인구수, 도시인구수
select country.code
, country.name as country_name
, city.name as city_name
, country.population as country_population
, city.population as city_population
, (city.population / country.population) as rate
from country
join city
on country.code = city.countrycode
having city.population >= 500 * 10000
order by rate desc;
use sakila;
select * from staff;
select * from payment;
select payment.payment_id
, payment.staff_id
, concat(staff.first_name, staff.last_name) as staff_fullname
, payment.amount
, payment.payment_date
from payment
join staff
on payment.staff_id = staff.staff_id;
# sub query : select
use world;
# 전체국가수, 전체도시수, 전체언어수를 1개의 row로 출력
select (select count(*) from country) as total_country
, (select count(*) from city) as total_city
, (select count(distinct(language)) from countrylanguage) as total_language;
select ( select count(*) from country ) as total_country
, ( select count(*) from city ) as total_city
, ( select count(distinct(language)) from countrylanguage ) as total_language;
# sub query : from
# 도시의 인구수가 900만 이상인 도시의 국가코드, 국가이름, 도시이름, 도시인구수 출력
# JOIN(country,city) > filtering(population>900)
select country.code, country.name, city.name, city.population
from country
join city
on country.code = city.countrycode
having city.population >= 900 * 10000;
# filtering(population>900) > join(country,city)
select country.code, country.name, sub.name, sub.population
from country
join (
select countrycode, name, population
from city
where population >= 900 * 10000
) as sub
on country.code = sub.countrycode;
# sub query : where
# 한국(KOR)의 인구수보다 많은 국가의 국가코드, 국가이름, 인구수 출력
# 한국인구수 확인 > 한국인구수 이상의 데이터 출력
select population from country where code = 'kor';
select code, name, population
from country
where population >= 46844000;
select code, name, population
from country
where population >= (
select population from country where code = 'kor'
);
# 지역(region)과 대륙(continent)별 사용하는 언어 목록 출력
# join(country, countrylanguage)
select distinct country.continent, country.region, countrylanguage.language
from country
join countrylanguage
on country.code = countrylanguage.countrycode;
# 지역과 대륙별 사용하는 언어의 갯수를 출력
# sub query + group by
select continent, region, count(language) as language_count
from (
select distinct country.continent, country.region, countrylanguage.language
from country
join countrylanguage
on country.code = countrylanguage.countrycode
) as sub
group by continent, region
order by language_count desc;
# 지역과 대륙별 사용하는 언어의 갯수를 출력
# sub query + group by
select continent, region, count(language) as language_count
from (
select distinct country.continent, country.region, countrylanguage.language
from country
join countrylanguage
on country.code = countrylanguage.countrycode
) as sub
group by continent, region
order by language_count desc;
# index
# 테이블의 데이터를 빠르게 검색해주는 방법
# 테이블의 컬럼 단위로 설정
use employees;
desc salaries;
# 인덱스출력
show index from salaries;
# 인덱스 없이 쿼리 실행 : 0.856 sec
select * from salaries where to_date < '1986-01-01';
# 인덱스 설정
create index tdate on salaries (to_date);
# 인덱스 출력
show index from salaries;
# 인덱스 없이 쿼리 실행 : 0.856 sec > 0.069 sec
select * from salaries where to_date < '1986-01-01';
# 인덱스 삭제
drop index tdate on salaries;
# 인덱스 출력
show index from salaries;
# 인덱스를 사용하면 좋은 경우 : where 절에서 자주 사용되는 column 설정
# 실행계획 : 쿼리를 실행하기 전에 쿼리가 어떻게 실행될지 알려주는 명령
explain select * from salaries where to_date < '1986-01-01';
# 인덱스 설정
create index tdate on salaries (to_date);
explain select * from salaries where to_date < '1986-01-01';
# backup
# 백업시 데이터베이스의 상태
# - hot backup
# - 데이터베이스가 실행되고 있는 상태에서 백업
# - 장점 : 백업동안 서비스 운영 가능
# - 단점 : 완전한 백업이 안될수 있음
# - cold backup
# - 데이터베이스의 실행을 중단하고 백업
# - 장점 : 안정적으로 백업이 가능
# - 단점 : 백업하는 동안 서비스 운영 X
use world;
select countrycode, name, population
from city
where population >= 900 * 10000;
# DML : DATA : C(insert into)R(selet from)U(update set)D(delete from)
# DDL : DATABASE, TABLE : C(create)R(show,desc)U(alter)D(drop)
# - DML : READ
# - where : 산술,비교,논리 : between and, in, like
# - order by : desc, asc
# - limit num1, num2
# - datatype, contraint
# - functions : round, concat, count, distinct, dateformat
# - condition : if, ifnull, case when then else end
# - group by, having
# - join, union, sub query
# - index, backup
가보자가보자~