gnuykob_sw

1. 색으로 시각화한 라인 차트

2. 맵에 시각화한 차트

3. 크기로 시각화한 막대 차트

4. 원과 색으로 시각화한 파이 차트

따로 명시하지 않은 모든 데이터는 태블로에 기본적으로 저장되어 있는 Sample - Superstore 데이터를 활용했다.

1. 막대 차트

2. 서버 데이터 - 구글 스프레드 시트 활용하여 연결

www.worldometers.info/coronavirus/

3. 데이터 전처리, 피벗 사용

4. 데이터 관계

0. R과 R Studio 설치

R 설치: http://cran.r-project.org 

R Studio 설치: http://www.rstudio.com 

1. 저장할 폴더 지정하기 (Working Directory)

Session 메뉴에서 Set Working Directory > Choose Directory 를 누른 뒤 원하는 폴더를 선택하면 그 폴더에 앞으로 스크립트 등이 저장된다. 그리고 이 작업은 매번 RStudio를 실행할 때마다 해주어야 한다.

2. attach( ) & detach( )

attach(변수명) , detach(변수명)

원래 R에서 데이터를 이용할 때에는 일일히 데이터명$변수명 형식으로 입력을 해주어야 했다.

예를 들어 student 데이터의 grade 속성의 평균을 구하려면 mean(student$grade) 이렇게 입력해야 하는 것이다.

하지만, attach 함수를 이용하면 student$ 는 입력하지 않고, 그냥 mean(grade) 라고 적을 수 있다.

그리고, attach 함수를 해제하는 함수가 detach() 이다. 

detach를 한 후에는 다시 데이터명$변수명 형식으로 써야한다.

이번 예제에서는 Rstudio에서 기본적으로 제공하는 mtcars 데이터를 이용했다.

3. 산포도 plot( )

plot(x) / plot(x, y)

plot함수는 기본적으로 값을 점으로 나타내는 산포도를 그리는 함수이다.

변수를 한 개만 넣으면 자동으로 index가 생성된다.

이번 예제에서는 자동차의 연비와 무게의 관계를 보기 위해 무게를 x축으로, 연비를 y축으로 설정했다.

plot(x, y, type = "l") 과 같이 타입을 적어주면 직선으로 연결된 그래프를 보여준다.

l(line), d(density) 등이 있다.

col = "blue" 와 같이 입력해주면 색을 바꿀 수 있다.

type = "o" 는 선과 점을 같이 표현한다.

4. abline( )

abline(a= , b= ) / abline(h= ) / abline(v= )

abline은 그래프에 선을 긋는 함수이다.

첫번째 식은 대각선을 긋는 함수이다. y=a+bx 인 직선함수를 넣는 것이다. 따라서 a는 y절편, b는 기울기를 말한다.

두번째 식은 수평선을 긋는 함수이다. h=3 을 넣으면 y=3 직선을 그린다.

세번째 식은 수직선을 긋는 함수이다. v=4 를 넣으면 x=4 직선을 그린다. 

이 식도 col=" " 속성으로 색을 바꿀 수 있다.

이 예제에서는 abline 안에 lm(mpg~wt) 를 넣었다. 

lm( ) 은 단순회귀분석을 하는 함수이다. 

아래 콘솔에 lm(mpg~wt)를 실행시켜보았더니 intercept가 37.285, wt가 -5.344가 나왔다.

이것은 두 변수가 mpg(y) = 37.285 - 5.344wt 의 관계를 갖는다는 것이다.

따라서 이 함수를 abline에 넣으면 위와 같은 직선이 그려진다.

그리고 title(" ") 함수는 plot 위에 제목을 붙여주는 함수이다.

5. 그래프 PDF로 저장

dev( ) 함수는 여러 그래픽 기능을 제어하는 함수이다.

dev.set( )으로 시작하고, dev.off( )로 끝낸다. 사실 dev.set( )은 안해도 된다.

plot결과를 pdf로 저장하기 위해 pdf("제목") 함수를 실행한다.

그리고 위의 예제와 똑같이 그래프를 그려준 뒤 dev.off( )를 해주면 처음에 지정해준 폴더에 pdf파일이 저장된다.

pdf 외에도 이미지 저장 함수로는 win.metafile(), png(), jpeg(), bmp(), tiff(), xfig(), postscript() 가 있다.

5-1. 바로 이미지/PDF로 저장하기 

plot을 만들고 Export를 누르면 다음과 같이 이미지 또는 PDF 파일로 저장할 수 있다.

1. SQL 파싱 (PARSING)

SQL 파싱은

1) SQL 문장에 문법적 오류가 없는지 검사 (Syntax 검사)

2) 의미상 오류가 없는지 검사 (Semantic 검사)

3) 사용자가 발생한 SQL과 그 실행계획시 라이브러리캐시(프로시저캐시)에 캐싱되어 있는지 확인

4) 캐싱되어 있다면 소프트파싱, 캐싱되어있지 않다면 하드파싱

으로 구성된다.

* 소프트파싱: SQL과 실행계획을 캐시에서 찾아 곧바로 실행단계로 넘어가는 경우

* 하드파싱: SQL과 실행계획을 캐시에서 찾지 못해 최적화 과정을 거치고나서 실행단계로 넘어가는 경우

* 라이브러리캐시는 해시구조로 엔진에서 관리된다. SQL마다 해시값에 따라 여러 해시 버킷으로 나뉘며 저장되고, SQL을 찾을 때는 SQL 문장을 해시 함수에 적용하여 반환되는 해시값을 이용해서 해시 버킷을 탐색한다.

2. 규칙기반 옵티마이저 (RBO)

실행계획을 정해진 룰에 따라 만든다.

룰은 데이터베이스 엔진마다 여러 가지가 있다.

예를 들어서 오라클의 RBO는 다음과 같다.

자세한 과정은 잘 모르겠지만, 크게 보면 엑세스를 할 때에 인덱스를 이용하느냐, 전체 테이블을 스캔하느냐 등으로 나눌 수 있다. 1번부터 순서대로 맞는 경우에 진행하며, 아래로 갈수록 data가 흐트러져서 저장되기 때문에 비용이 많이 든다.

그리고 요즘에는 대부분 RBO보다 CBO를 이용한다.

3. 비용기반 옵티마이저 (CBO)

비용을 기반으로 최적화를 수행하는 방식이다. 이때 비용이란 쿼리를 수행하는데 소요되는 일의 양 또는 시간 예상치이다.

딕셔너리에서 테이블과 인덱스를 통해 레코드 개수, 블록 개수, 평균 행 길이, 칼럼 값의 수, 칼럼 값 분포, 인덱스 높이, 클러스터링 팩터 등의 통계값을 기반으로 비용을 예측하는 방식이다. 이 예측된 비용에 따라 최적의 실행 계획을 도출한다. 최근에는 추가적으로 하드웨어적 특성을 반영한 시스템 통계정보 (CPU 속도, 디스크 I/O 속도 등)까지 이용한다.

4. SQL 실행계획

실행 계획은 SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미한다. 즉, SQL을 어떤 순서로 어떻게 진행할 지 결정한다는 것이다.

실행 계획의 구성요소는 다음 다섯 가지가 있다.

• 조인 순서 (Join Order) : JOIN 수행에서 어떤 테이블이 먼저 조회되는가
• 조인 기법 (Join Method) : loop, merge, sorting 등
• 액세스 기법 (Access Method) : Index / Full table 등
• 최적화 정보 (Optimization Information) : 알고리즘
• 연산 (Operation) : 크다, 작다, 함수, MAX, MIN 등

5. INDEX

INDEX는 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조이다.

CREATE INDEX IDX_### ON db, table (column);

ALTER TABLE db.table ADD INDEX IDX_###;

두 코드 모두 column에 대한 INDEX를 생성하는 코드이다.

ALTER TABLE db.table DROP INDEX IDX_###;

테이블에 있는 인덱스를 삭제하는 코드이다.

EXPLAIN SELECT * FROM db.table;

실행 계획을 확인하는 코드이다.

예제) 

DESC kaggle.titanic;

EXPLAIN SELECT * FROM kaggle.titanic WHERE `Age` = 23;

>> Age가 23인 데이터를 찾기 위해 418개의 rows를 모두 검색하는 것이 플랜임을 알 수 있다.

[INDEX 생성 후]

DESC kaggle.titanic;

Age에 인덱스가 생성된 것을 확인할 수 있다.

EXPLAIN SELECT * FROM kaggle.titanic WHERE `Age` = 23;

>> IDX_AGE를 사용해서 reference를 통해서 쿼리를 실행하는 것이 플랜임을 알 수 있다.

>> 데이터를 얻기 위해서 11개의 row만 검색하면 된다.

1. 태블로 시작하기

www.tableau.com/ko-kr/trial/tableau-software?utm_campaign_id=2017049&utm_campaign=Prospecting-CORE-ALL-ALL-ALL-ALL&utm_medium=Paid+Search&utm_source=Naver&utm_language=KR&utm_country=SoKOR&kw=TABLEAU&adgroup=brandsearchad&adused=

태블로는 이곳에서 다운로드 받을 수 있다.

원래는 구매를 해야 하는데, 대학생과 교수한테는 학교 이메일을 사용하면 무료로 프로그램을 제공해준다.

태블로는 비즈니스 인텔리전스와 분석 소프트웨어로, 데이터 시각화 프로그램이다. 

아직 우리나라에서 많이 쓰이진 않지만 전세계적으로는 아주 많이 사용되는 프로그램이라고 한다.

2. 데이터 다운로드 하기

직접 데이터를 수집할 수도 있지만, 정부에서 제공하는 공공 데이터를 활용할 수 있다.

data.seoul.go.kr/

www.data.go.kr/

각각 서울시 데이터 광장과 전국 공공데이터 포털이다. 

생각보다 많은 데이터들을 공유하고 있어서 스스로 프로젝트를 진행할 수도 있을 것 같다.

이번 실습은 공공데이터 포털에서 서울과 부산의 상가 정보 데이터를 활용했다.

3. 태블로 시작하기

태블로를 시작하면 이렇게 화면이 뜬다. 

왼쪽 배너에서 사용할 데이터 타입을 선택하고, 파일을 불러오면 된다.

이번 실습에서 사용할 데이터는 csv 파일로, 텍스트 파일을 선택하면 된다.

상가 데이터를 불러온 모습이다. 

가장 위에 파란 글씨로 써져있는 것은 데이터 타입 느낌의 분류체계이다. Abc는 문자열, #은 상수, 지구 모양은 지리에 관한 것이다. 그리고, 이 파일의 경우는 분류가 F1, F2 등으로 되어있는데, 이 부분을 더블클릭해서 원하는 인덱스명으로바꿀 수 있다. 뒤에서 시각화 할 때 구분하기 쉽도록 미리 바꿔놓는 것이 좋다.

이러한 식으로 분류할 수 있다.

본격적으로 시각화를 하기 위해 시트1 옆에 있는 작은 버튼 (새 워크시트) 을 눌러준다.

그러면 이러한 창이 뜬다.

1) 막대 그래프

"필터" 부분에 '시도명' 테이블을 가져와서 서울과 부산 중 서울만 선택했다.

표는 대분류를 열로, 측정값을 행으로 하여 대분류에 따른 데이터 수를 막대 그래프로 시각화했다.

간단하게 옆의 테이블에서 끌어와서 원하는 부분에 놓으면 된다.

필터에 '시군구' 테이블을 가져와서 용산구의 값만 나타냈다. 

그래프 왼쪽에 Y축 부분에 마우스를 가져가면 오름차순, 내림차순 정렬도 할 수 있다.

2) 지도

다음은 지도에 시각화하기 위해 데이터 파일에 포함되어 있었던 '경도'와 '위도'를 각각 열과 행에 넣어줬다.

그러면 이렇게 지도가 나온다. 맵 - 맵 계층에 들어가서 스타일도 변경할 수 있다.

서울에 있는 데이터들만 표현한 것이다.

"마크"에서 모양, 크기, 색상 등을 변경할 수 있다.

확대해보면 한강 위에 정보가 찍혀있는 오류도 발견할 수 있다.

"마크"에서 모양을 변경하여 밀도도 확인할 수 있다.

"필터"에 '시군구'와 '대분류'를 포함하여 용산구에 있는 학문/교육 시설만 나타낸 것이다.

이러한 식으로 원하는 정보를 지도 위에서 시각화하고, 비교할 수 있다.

1. 통계적 추론

통계적 추론은 크게 두 가지로 나눌 수 있다.

1) 모수적 추론: 모집단을 가정하고, 표본을 이용해 모수를 추론한다.

2) 비모수적 추론: 모집단을 가정하지 않기 때문에 특정 모수에 대해서는 관심을 갖지 않으며, 통계량을 활용하여 추론한다. 

또, '베이지안 추론'이 있는데, 이것은 모수가 확률변수라고 가정한 뒤, 모수의 확률분포에 대해 알아본다. 학부 때에는 거의 배우지 않지만 활용도가 매우 높다.

통계적 추론은 목적과 방법에 따라 추정과 가설검정으로 나눌 수 있다.

• 추정: 모수가 얼마인지 (점추정) 또는 모수가 포함되어 있을 것으로 생각되는 구간 (구간추정) 을 확률적으로 찾는 것이다. 
• 가설검정: 모수에 대한 가설을 세우고, 가설의 옳고 그름을 확률적으로 판정하는 방법이다.

2. 점추정

미지의 모수를 표본의 어떤 함수 (통계량) 을 이용해 어떠한 값으로 추정하는 과정이다.

방법으로는 적률법, 최대가능도추정법, 최소제곱법 등이 있는데, 이에 대해서는 이후 '수리통계학'에서 다룬다.

점추정에는 '직관적인 추정량'을 이용한다.

이처럼 통계량을 이용해서 모수를 추정하는 것이다.

이때, 추정량과 추정값에 대한 차이를 알아야 한다.

추정량 (estimator) 은 확률 변수로 실험을 진행하기 전, 아직 결정되지 않은 변수를 말한다. 확률분포가 존재한다.

추정량은 대문자를 이용한다. ex) X1, X2, ..., Xn => x̅ (표본평균)

추정값 (estimate) 은 실제 관측값으로, 변하지 않는 상수이다. ex) x̅, p, s², s

점추정량이 정확히 모수와 일치할 확률은 거의 없다. 대신 구간추정과 가설검정에서의 기준통계량으로 사용된다.

3. 구간추정

미지의 모수가 포함되어 있을 것이라고 생각되는 구간을 확률적으로 찾는 방법이다.

P(L ≤ θ ≤ U) = 1 - α      => L과 U 찾는 것  (θ = 모평균, 모비율, 분산 등)

예를 들어서, θ를 포함할 확률이 95%가 되는 구간을 찾는 것이다. 

이때 100(1 - α)% 는 "신뢰 수준"이라고 하고, [L, U]가 "신뢰 구간"이다.

L과 U를 유도하는 데 점추정량이 중심적 역할을 한다.

예를 들어보자면,

모평균 μ에 대한 95% 신뢰구간을 구하려 한다.

• 모집단 가정: N(μ, σ²)이고, σ²을 알고있는 경우
• 표본 추출: X1, X2, ..., Xn ~ iid N(μ, σ2²)
• μ의 점추정량: x̅ 
• x̅의 통계적 성질: x̅ ~ N(μ, σ² /n)  ->  Z = (x̅ - μ) / (σ² / n) ~ N(0, 1)   #정규분포 따름

표본정규분포로부터

0.95 = P( -1.96 < Z < 1.96 )

         P( -1.96 < (x̅ - μ) / (σ / √n) < 1.96 )

         P( x̅ - 1.96 · σ / √n < μ < x̅ + 1.96 · σ / √n )

∴ 95% 신뢰구간 = [ x̅ - 1.96 · σ / √n, x̅ + 1.96 · σ / √n ]

   100(1 - α)% 신뢰구간 = [ x̅ - Z(α/2) · σ / √n, x̅ + Z(α/2) · σ / √n ]

실제 자료를 통해 신뢰구간을 구했을 때, 

예를 들어 95% 신뢰구간을 구할 때, P(158.78 ≤ μ ≤ 166.62) 이라면 신뢰구간은 [158.78, 166.62]이다.

하지만, 이때 μ가 신뢰구간에 포함될 확률은 0.95가 아니라 0 또는 1이다.

신뢰구간이 [ x̅ - Z(α/2) · σ / √n, x̅ + Z(α/2) · σ / √n ] 와 같이 확률변수로 표현될 때는 신뢰구간에 포함될 확률이 0.95이겠지만, 실제 자료로 구간을 구하면 그 구간은 "상수"로 표현되기 때문에 상수 μ가 상수 범위에 포함될 확률은 0또는1이 되는 것이다.

그렇다면 실제 데이터를 통해 상수로 표현될 때, 0.95는 무엇을 의미할까.

위의 경우, 한 번 데이터를 뽑아 만든 신뢰구간이 [158.78, 166.62]이다.

그리고 또 다시 데이터를 뽑아서 신뢰구간을 만들 수 있다. 이렇게 데이터를 새로 뽑으면 표본평균(x̅)이 바뀐다. 

그래서 새로 데이터를 10000번 뽑아서 신뢰구간을 10000개 구했을 때, 그 중 9500번 정도에 모평균μ가 포함된다는 의미이다.

4. 가설 검정

가설을 설정하고, 그에 대한 옳고 그름을 표본으로부터 얻은 정보를 통해 확률적으로 판단하는 방법이다.

가설에는 귀무가설과 대립가설이 있다.

대립가설 (H1) 은 표본으로부터 얻은 증거에 의해 우리가 입증하고자 하는 가설이다.

귀무가설 (H0) 은 대립가설의 반대되는 가설로, 검정의 대상이 되는 가설이다.

수학의 '귀류법'과 유사하게, 직접 대립가설을 입증하기 어려운 경우가 많기 때문에, 그에 반대되는 가설인 귀무가설이 잘못됨을 입증하여 대립가설을 입증하는 방법을 이용한다. 

즉, [정상적인 표본 => 대립가설 참] 을 입증하는 방법이 어렵기 때문에,

[귀무가설 참 => 비정상적인 표본]을 입증하는 방법을 이용하는 것이다.

정상 / 비정상적인 표본을 구분하는 방법은 표본이 발생할 수 있는 가능성을 통해 구분한다. 비정상적인 표본은 자주 발생하지 않는 표본이다. 

이 가능성은 "유의 수준"을 이용해 판단하고 이때 "검정 통계량"을 이용한다.

1) 검정 통계량

귀무가설을 기각시킬 것인가, 채택할 것인가 결정하기 위해 사용되는 통계량이다.

검정 통계량 유도 방법은 '수리통계학'에서 다룬다.

귀무가설 하에서 검정 통계량의 확률 분포를 이용하여 표본의 정상 / 비정상을 판정한다.

비정상 표본은 "기각역"에, 정상적인 표본은 "채택역"에 위치한다.

앞에서 말했듯이 정상, 비정상의 기준은 유의수준으로 결정한다.

예를 들어서, 초코파이를 새로 만든다고 해보자. 기존 파이의 평균 칼로리는 45kcal였고, 가설 검정을 통해 새로운 파이는 기존의 파이보다 칼로리가 낮다는 것을 보이고자 한다. 

이때 대립가설은 H1: μ < 45 이고, 귀무가설은 H0: μ ≥ 45 이다.

그렇다면, 귀무가설과 반대되도록 표본평균 x̅이 45보다 작을수록 비정상 자료라고 할 수 있다.

표본평균이 45보다 작을 경우 귀무가설이 옳지 않음을 보여주는 비정상 자료이기 때문에 귀무가설을 기각시킬 수 있다.

2) 오류의 종류

º α = maxP( 제 1종 오류 ) = P( H0 기각 | H0 사실 ) : 제 1종 오류를 범할 확률

α : 유의 수준

귀무가설의 경계값에서 P( 제 1종 오류)가 최대가 된다.

º β = 1 - P( 제 2종 오류 ) = 1 - P( H0 채택 | H1 사실 ) : 제 2종 오류를 범할 확률

1 - β = P(H0 기각 | H1 사실) : 검정력  (옳은 결정을 할 확률)

앞의 예제와 연결지어서 생각해보자. 추가적으로 파이는 16개를 무작위로 조사하였고, 표준편차가 8인 정규분포를 따른다고 가정하자.

귀무가설은 H0: μ ≥ 45 이고, 대립가설은 H1: μ < 45이지만, 계산을 쉽게 하기위해 저 범위 중 하나로 H0: μ=45, H1: μ=42라고 정하자. μ는 정규분포를 따르기 때문에 각각의 분포를 그려보면 다음과 같다.

이 경우 x̅가 작을 수록 비정상적인 표본이기 때문에, x̅ ≤ k 일때 귀무가설을 기각한다고 하자.

유의수준 α는 귀무가설이 사실일 때 귀무가설을 기각하는 제 1종 오류를 범할 확률이기 때문에 저 면적(α)의 확률이라고 할 수 있다.

β는 대립가설이 사실일 때 귀무가설을 채택하는 제 2종 오류를 범할 확률이기 때문에 저 면적(β)의 확률이라고 할 수 있다. 

오류를 최소화해야 더 정확한 결과를 낼 수 있기 때문에 α와 β 모두 줄이는 것이 좋지만, 

저 그래프를 보면 알 수 있듯이 k를 어떻게 움직이든 둘 중 하나가 줄어들면 다른 하나가 커진다.

따라서 하나를 고정시킨 뒤, 나머지 하나를 최소화하는 방법을 이용한다. 보통 유의수준 α를 고정시킨다.

α와 β의 값을 각각 구해보자.

α = PH0( x̅ < 43 ) = P( (x̅ - 45) / 8 / √16  < (43-45) / 8 / √16 )

   = P( Z < -1 ) = 0.1587 => 유의 수준

μ = 46일 때 => P( x̅ < 43 ) = P( Z < -1.5 ) = 0.0668 < 0.1587 

≫ 귀무가설의 경계값일 때 P( 제 1종 오류 )가 최대

≫ 모든 상황에 적용하기 위해 최대가 될 때의 값을 유의 수준으로 적용한다.

β = PH1( x̅ ≥ 43 ) = P( (x̅-42) / 8 / √16 ≥ (43-42) / 8 / √16 )

   = P( Z ≥ 0.5 ) = 0.1915

검정력(Power) = 1 - β = 0.8085

이 두 확률을 동시에 작게 하는 가장 좋은 검정 방법은 같은 α 하에서 β를 가장 작게 만드는 방법이다.

3) α의 결정

"자료가 비정상적이다" 

-> H0이 사실일 때 그러한 자료를 얻을 가증성이 적어야 함

-> α의 값을 작게 설정해야 함

일반적으로 α = 0.05, 0.01, 0.1을 많이 사용한다.

위의 예제에서는 k값을 설정하고 그에 따른 α를 구했지만, 사실 보통의 경우에는 α를 결정한 뒤, 그에 따른 k를 결정한다. 그리고 그 k (임계값)에 따라 기각역과 채택역을 구분한다.

4) 가설 검정의 순서

1. 귀무가설과 대립가설의 설정
2. 검정 통계량 설정
3. 유의 수준 설정
4. 기각역 계산
5. 판정 (통계량이 어느 범위에 존재하는지)

5) 모평균의 검정

• 가정: X1, X2, ..., Xn ~ iid N( μ, σ²)이고, σ²이 알려진 경우

• 가설: H0: μ = μ0  vs  H1: (a) μ > μ0, (b) μ > μ0, (c) μ ≠ μ0

• x̅ ~ N( μ, σ2²/n ) => 검정 통계량: Z = (x̅ - μ0) / (σ / √n) ~ N( 0, 1 ) 

• 유의 수준을 α라고 하면, 기각역은 (a) Z0 > Zα, (b) Z0 < -Zα, (c) |Z0| > Z(α/2)

예를 들어보자. 기존 치료법의 치료기간은 평균 15일이고 표준편차가 3일인데, 새로운 치료법은 치료기간을 단축시킨다는 의료진의 주장을 확인하고자 한다. 새로운 치료법에 의한 치료기간도 표준편차가 3인 정규분포를 따른다고 가정하자.

• 가설: H0: μ = 15  vs  H1: μ < 15
• 검정 통계량: Z = ((x̅-15) / 3 / √n) ~ N( 0, 1 )
• 유의 수준: α = 0.05 => 기각역: Z < -1.645 (표준정규분포표 참고)
• 36명의 환자 임의 추출 & 실험 결과 

x̅ = 14 => Z0 = (14-15) / 3 / √36 = -2    (x̅ = 14는 대립가설 범위 내의 한 값)

=> 기각역 안에 존재 -> 귀무가설 기각 -> 치료기간 단축 (대립가설) 입증

6) 유의 확률 (P-Value, P-값)

관측값에 의해 귀무가설을 기각시킬 수 있는 검정법의 최소 유의 수준

위의 예제에서는 임계값 Z와 실제 자료를 표준화하여 나온 Z0값을 비교하여 기각역을 설정했다.

하지만, 보통 정규분포를 따르기 때문에 실제 값보다 그 면적을 구해서 비교하는 것이 더 효율적이다.

즉 Z값을 구할 필요가 없다.

그리고 유의 확률이 그 면적을 의미한다. 

· P - Value < α => 귀무가설 기각 (기각역 면적보다 작음)

· P - Value > α => 귀무가설 기각할 수 없음

위의 예제에서 P-값은 P( Z ≤ -2) = 0.0228으로 유의 수준인 0.05보다 작기 때문에 귀무가설을 기각할 수 있다.

create database kaggle;

use kaggle;

CREATE TABLE `org_test_import` (
  `PassengerId` int NULL,
  `Pclass` int NULL,
  `Name` text,
  `Sex` text,
  `Age` text,
  `SibSp` int  NULL,
  `Parch` int  NULL,
  `Ticket` text,
  `Fare` text,
  `Cabin` text,
  `Embarked` text
) ENGINE=InnoDB ; 

SELECT @@sql_mode;

set @@sql_mode = "";    

CREATE TABLE `test` (
  `PassengerId` int NULL,
  `Pclass` int NULL,
  `Name` text,
  `Sex` text,
  `Age` double,
  `SibSp` int  NULL,
  `Parch` int  NULL,
  `Ticket` text,
  `Fare` double,
  `Cabin` text,
  `Embarked` text
) ENGINE=InnoDB ;

INSERT INTO `kaggle`.`test` (
  `PassengerId`,
  `Pclass`,
  `Name`,
  `Sex`,
  `Age`,
  `SibSp`,
  `Parch`,
  `Ticket`,
  `Fare`,
  `Cabin`,
  `Embarked` )
SELECT `org_test_import`.`PassengerId`,
    `org_test_import`.`Pclass`,
    `org_test_import`.`Name`,
    `org_test_import`.`Sex`,
    `org_test_import`.`Age`,
    `org_test_import`.`SibSp`,
    `org_test_import`.`Parch`,
    `org_test_import`.`Ticket`,
    `org_test_import`.`Fare`,
    `org_test_import`.`Cabin`,
    `org_test_import`.`Embarked`
FROM `kaggle`.`org_test_import`;

select age from `org_test_import`;  

select age from `kaggle`.`test`;   

SELECT * FROM `gender_submission`;

ALTER TABLE `test` ADD PRIMARY KEY (`PassengerId`);

ALTER TABLE `gender_submission` ADD PRIMARY KEY (`PassengerId`);

ALTER TABLE `gender_submission` ADD FOREIGN KEY (`PassengerId`) REFERENCES `test` (`PassengerId`);

SELECT COUNT (*) FROM `gender_submission`;
SELECT COUNT (*) FROM `test`;
SELECT COUNT (*) FROM test A JOIN gender_submission B ON A.PassengerId = B.PassengerId;

CREATE TABLE `titanic` (
  `PassengerId` int not NULL,
  `Pclass` int NULL,
  `Name` text,
  `Sex` text,
  `Age` double,
  `SibSp` int  NULL,
  `Parch` int  NULL,
  `Ticket` text,
  `Fare` double,
  `Cabin` text,
  `Embarked` text,             
  `Survived` int ,             
  primary key (`PassengerId` )
) ENGINE=InnoDB ; 

INSERT INTO `kaggle`.`titanic` (
  `PassengerId`,
  `Pclass`,
  `Name`,
  `Sex`,
  `Age`,
  `SibSp`,
  `Parch`,
  `Ticket`,
  `Fare`,
  `Cabin`,
  `Embarked`,
  `Survived`) 
SELECT 
A.`PassengerId`,
  `Pclass`,
  `Name`,
  `Sex`,
  `Age`,
  `SibSp`,
  `Parch`,
  `Ticket`,
  `Fare`,
  `Cabin`,
  `Embarked`, 
B.`Survived`
FROM test A JOIN gender_submission B ON A.PassengerId = B.PassengerId ; 

SELECT COUNT(*) FROM `kaggle`.`titanic` ; 

SELECT * FROM `kaggle`.`titanic` ; 

SELECT MAX(`Age`) FROM `kaggle`.`titanic`;

SELECT MIN(`Age`) FROM `kaggle`.`titanic` WHERE `Age` > 0;

SELECT AVG(`Age`), COUNT(*) FROM `kaggle`.`titanic` WHERE `Age` > 0;

SELECT sum(`Fare`) FROM `kaggle`.`titanic`;

SELECT `Name`, `Pclass`, `Sex`, `Age` FROM `kaggle`.`titanic` ORDER BY 1 ;  ##첫번째 컬럼 기준으로 오름차순 (Name)

SELECT `Name`, `Pclass`, `Sex`, `Age` FROM `kaggle`.`titanic` ORDER BY 3 ;

SELECT `Name`, `Pclass`, `Sex`, `Age` FROM `kaggle`.`titanic` ORDER BY `Name` DESC ;

#10개 제한된 행 조회
SELECT * FROM `kaggle`.`titaic` WHERE `Name` LIKE 'A%' LIMIT 10;   #이름이 A로 시작하는 row 10개

##`PassengerId` 유일한 값만 조회
SELECT DISTINCT `PassengerId` FROM `kaggle`.`titanic`;

SELECT DISTINCT `Sex` FROM `kaggle`.`titanic`;

SELECT `Sex`, COUNT(*) FROM `kaggle`.`titanic` GROUP BY 1;    #첫번째 컬럼을 그룹화해서 카운트

SELECT `Sex`, COUNT(*) CNT FROM `kaggle`.`titanic` GROUP BY `Sex` HAVING CNT > 200;  #count = cnt, cnt가 200이상인 그룹의 카운트 수

SELECT `Sex`, `survived`, COUNT(*) FROM `kaggle`.`titanic` GROUP BY `Sex`, `Survived` ;

##floor = 반올림
#연령 밴드별 조회
SELECT floor(`Age`/10) * 10 + 10, COUNT(*) FROM `kaggle`.`titanic` WHERE `Age` > 0 group by 1; #~대 미만 승객 수

SELECT floor(`Age`/10) * 10 + 10, COUNT(*) FROM `kaggle`.`titanic` WHERE `Age` > 0 group by 1 order by 1;

SELECT floor(`Age`/10) * 10 + 10, COUNT(*) FROM `kaggle`.`titanic` WHERE `Age` > 0 group by 1 order by 1 desc;

SELECT floor(`Age`/10) * 10 + 10, `survived`, COUNT(*) CNT FROM `kaggle`.`titanic` 
WHERE `Age` > 0 group by 1, 1 HAVING CNT > 40 order by 1 desc, 2 desc;


#서브쿼리
SELECT * FROM `kaggle`.`titanic`
WHERE `PassengerId` IN (SELECT `PassengerId` FROM `test` WHERE `Age` = 0);  # NULL 값이었던 passengerId(age=0)를 titanic table에서 조회해라

UPDATE `kaggle`.`titanic`
SET `Age` = 30.272590361445783
WHERE `PassengerId` IN (SELECT `PassengerId` FROM `test` WHERE `Age` = 0) ;

이번 예제는 csv 파일을 import하여 타이타닉 데이터를 활용해보는 예제이다.

이번에 사용할 2개의 예제 데이터는 https://www.kaggle.com/c/titanic/ 에서 받을 수 있다.

(gender_submission, test)

kaggle 테이블을 만들고, 스키마에서 Tables -> Table Data Import Wizard를 누르면 csv파일을 import할 수 있다.

첫번째로 'gender_submission.csv' 파일을 import해주었다.

이때, 현재 sql모드가 data타입을 strict하게 설정하도록 되어있기 때문에,

Wizard로 import한 데이터파일에 NULL값이 있으면 그 값은 truncate (삭제) 된다.

두번째로 import할 'test.csv' 파일에 NULL값이 있기 때문에

이걸 고치기 위해 sql 모드를 strict하지 않게 바꿔주고, 데이터타입을 바꿔주는 테이블을 생성할 것이다.

코드를 살펴보겠다.

create database kaggle; 
use kaggle;

kaggle database를 생성하고, 이용하겠다는 뜻이다.

이후 위에 있는 사진처럼 이 데이터베이스에 'gender_submission.csv'파일을 import 했다.

import할 때에 'gender_submission'이라는 테이블을 함께 형성해서 저장했다.

CREATE TABLE `org_test_import` (
`PassengerId` int NULL,   
`Pclass` int NULL,   
`Name` text,   
`Sex` text,   
`Age` text,   
`SibSp` int  NULL,   
`Parch` int  NULL,   
`Ticket` text,   
`Fare` text,   
`Cabin` text,   
`Embarked` text ) ENGINE=InnoDB ; 

이 테이블은 두번째로 import할 'test.csv'파일의 값을 저장할 테이블이다. 

현재 sql모드가 데이터를 strict하게 저장하도록 설정되어있기 때문에, 'text.csv' 파일에서 double타입의 'age' 값에 NULL값이 있으면 Wizard로 import 할 때 테이블이 truncate된다. (삭제된다)

따라서, 우선 'age'와 'fare'의 데이터타입을 text로 변경한 테이블 'org_test_import'에 파일을 import하고, 

이후 다시 원래대로 데이터 타입을 바꾼 테이블('test')에 값을 저장해주도록 하겠다.

SELECT @@sql_mode;  

현재 sql_mode를 알려준다.

set @@sql_mode = "" ;

또한, sql_mode를 strict하지 않게 바꿔주었다.

CREATE TABLE `test` (
  `PassengerId` int NULL,
  `Pclass` int NULL,
  `Name` text,
  `Sex` text,
  `Age` double,
  `SibSp` int  NULL,
  `Parch` int  NULL,
  `Ticket` text,
  `Fare` double,
  `Cabin` text,
  `Embarked` text
) ENGINE=InnoDB ; 

이 테이블이 import한 'test.csv'파일을 옮겨 담을 테이블이다.

'age'와 'fare'의 도메인을 원래처럼 double로 바꿔주었다.

INSERT INTO `kaggle`.`test`
(`PassengerId`,
`Pclass`,
`Name`,
`Sex`,
`Age`,
`SibSp`,
`Parch`,
`Ticket`,
`Fare`,
`Cabin`,
`Embarked`)
SELECT `org_test_import`.`PassengerId`,
    `org_test_import`.`Pclass`,
    `org_test_import`.`Name`,
    `org_test_import`.`Sex`,
    `org_test_import`.`Age`,
    `org_test_import`.`SibSp`,
    `org_test_import`.`Parch`,
    `org_test_import`.`Ticket`,
    `org_test_import`.`Fare`,
    `org_test_import`.`Cabin`,
    `org_test_import`.`Embarked`
FROM `kaggle`.`org_test_import`;

'org_test_import'에 저장된 모든 값을 'test'테이블에 삽입하는 코드이다.

이제 NULL값이 있던 'age'값을 각각의 테이블에서 select해보겠다.

select age from `org_test_import` ;       #NULL값이 있음  (text이기 때문)

select age from `kaggle`.`test` ;          #NULL -> 0   (double이기 때문)

test파일에 저장된 'age' 값에는 NULL값이 없고 그 값들이 모두 0으로 바뀐 것을 볼 수 있다.

NULL값이 있는 데이터 파일을 wizard로 import할 때에는 위와 같이 도메인을 text로 바꾼 테이블을 만들어서 import한 뒤, 다시 옮겨주는 방법을 사용하면 된다.

SELECT * FROM `gender_submission` ;

'gender_submission' 테이블의 값을 보여준다.

AlTER TABLE `test` ADD PRIMARY KEY ( `PassengerId` ) ; 

'test' 테이블의 기본키를 'PassengerId'로 바꿔주는 코드이다.

AlTER TABLE `gender_submission` ADD PRIMARY KEY ( `PassengerId` ) ; 

'gender_submission' 테이블의 기본키를 'PassengerId'로 설정하는 코드이다.

AlTER TABLE `gender_submission` ADD FOREIGN KEY ( `PassengerId` ) REFERENCES `test` ( `PassengerId` ) ; 

'gender_submission' 테이블의 외래키를 'PassengerId'로 설정하며, 'test' 테이블의 'PassengerId' 값을 참조하겠다는 코드이다.

SELECT COUNT(*) FROM `gender_submission` ; 
SELECT COUNT(*) FROM `test` ;

count( ) 는 투플의 개수를 세는 함수이다. FROM - 뒤에 WHERE - 을 붙여 조건을 걸 수 있다.

정확한 표현인지는 모르겠지만..

예제에 나온 SELECT COUNT(*) FROM 'gender_submission'; 은 'gender_submission' 테이블에 있는 전체 투플 수 (rows)를 count해준다.

SELECT  COUNT(*) FROM test A JOIN gender_submission B ON A.PassengerId = B.PassengerId ;

'test' 테이블의 'PassengerId'와 'gender_submission'테이블의 'PassengerId'가 같을 경우에, 'test'테이블과 'gender_submission'테이블을 결합한다는 의미이다. 그리고, 그 결합한 것의 투플 수 (row 수)를 세는 코드이다. 

두 테이블 다 418개의 투플을 갖고 있기 때문에, 이 결과도 418이 나온다.

CREATE TABLE `titanic` (
  `PassengerId` int not NULL,
  `Pclass` int NULL,
  `Name` text,
  `Sex` text,
  `Age` double,
  `SibSp` int  NULL,
  `Parch` int  NULL,
  `Ticket` text,
  `Fare` double,
  `Cabin` text,
  `Embarked` text,                   

  `Survived` int ,                       
  primary key (`PassengerId` )
) ENGINE=InnoDB; 

'titanic'이라는 테이블을 생성했다. 이 테이블에는 'test'테이블과 'gender_submission'테이블을 결합하여 저장할 것이다.

INSERT INTO `kaggle`.`titanic`

(`PassengerId`,
 `Pclass`,
 `Name`,
 `Sex`,
 `Age`,
 `SibSp`,
 `Parch`,
 `Ticket`,
 `Fare`,
 `Cabin`,
 `Embarked`,
 `Survived`) 
SELECT 
A.`PassengerId`,
   `Pclass`,
   `Name`,
   `Sex`,
   `Age`,
   `SibSp`,
   `Parch`,
   `Ticket`,
   `Fare`,
   `Cabin`,
   `Embarked`, 
B.`Survived`
FROM test A JOIN gender_submission B ON A.PassengerId = B.PassengerId ;

insert into, select, from join on 함수를 이용하여,

'test'테이블의 PassengerId값과 'gender_submission'테이블의 PassengerId값이 같을 경우,

두 테이블을 결합하여 각각의 값들을 'titanic' 테이블에 저장해 하나의 테이블로 만드는 것이다.

SELECT COUNT(*) FROM `kaggle`.`titanic` ; 

'titanic' 테이블의 투플 수를 보여준다.

'gender_submission'의 'PassengerId'값은 외래키로, 'test' 테이블의 값을 참조한 것이기 때문에 모든 값이 같다.

따라서 모든 투플이 결합하여 저장되었기 때문에 count 수는 동일하게 418이다.

SELECT * FROM `kaggle`.`titanic` ; 

'titanic' 테이블의 모든 값을 보여준다.

SELECT MAX(`Age`) FROM `kaggle`.`titanic`;

MAX함수는 이 속성에서 가장 큰 값을 보여준다.

SELECT MIN(`Age`) FROM `kaggle`.`titanic` WHERE `Age` > 0;

MIN함수는 반대로 이 속성에서 가장 크기가 작은 값을 보여준다.

WHERE은 조건문 느낌으로, 'Age'값이 0보다 큰 범위에서 가장 작은 값을 보여달라는 의미이다.

NULL값이 0으로 변환되었기 때문에 이 조건을 추가한 것이다.

SELECT AVG(`Age`), COUNT(*) FROM `kaggle`.`titanic` WHERE `Age` > 0;

AVG는 평균을 보여주는 값이다. 

COUNT는 뒤에 WHERE `Age` > 0 의 조건이 붙었기 때문에 'Age' 값이 0보다 큰 투플의 개수를 알려준다.

SELECT sum(`Fare`) FROM `kaggle`.`titanic`;

sum은 해당 속성의 투플 값들을 모두 더한 값을 반환한다.

SELECT `Name`, `Pclass`, `Sex`, `Age` FROM `kaggle`.`titanic` ORDER BY 1 ;  

ORDER BY 1은 SELECT 뒤에 나열된 4개의 속성들 중 첫번째 속성을 기준으로 오름차순 정렬하여 반환하라는 의미이다.

SELECT `Name`, `Pclass`, `Sex`, `Age` FROM `kaggle`.`titanic` ORDER BY 3 ;

마찬가지로 3번째 속성인 'Sex'를 기준으로 오름차순 정렬하라는 의미이다.

SELECT `Name`, `Pclass`, `Sex`, `Age` FROM `kaggle`.`titanic` ORDER BY `Name` DESC ;

ORDER BY - DESC는 해당 속성 기준으로 내림차순 정렬하라는 의미이다.

그리고 ORDER BY 뒤에 숫자보다는 이와 같이 속성명을 직접 써주는 것이 더욱 직관적이다.

SELECT * FROM `kaggle`.`titanic` WHERE `Name` LIKE 'A%' LIMIT 10; 

LIKE 'A%' 은 'Name' 값들 중에서 A로 시작하는 값을 반환하라는 조건이다.

LIMIT 10은 그 중에서 상위 10개만 반환하라는 조건이다. 아마 조건에 대한 오름차순 정렬 후 10개인 것 같다.

SELECT DISTINCT `PassengerId` FROM `kaggle`.`titanic`;

DISTINCT 는 해당 속성의 값들 중 유일한 값만 조회하는 함수이다.

'PassengerId'의 경우 기본키로, 모든 투플이 유일하게 갖는 값이기 때문에 모든 값이 반환된다.

SELECT DISTINCT `Sex` FROM `kaggle`.`titanic`;

마찬가지로 'Sex' 의 값들 중 유일한 값들만 반환한다. 

이 경우 투플 값이 male과 female 두 가지로 나뉘기 때문에 두개의 값만 반환된다.

SELECT `Sex`, COUNT(*) FROM `kaggle`.`titanic` GROUP BY 1;  

GROUP BY는 해당 속성 값들을 그룹화하는 함수이다. 이 경우 'Sex'는 male과 female의 두개의 그룹으로 나뉠 것이다.

여기에 COUNT함수를 적용하면, 각각의 그룹에 속하는 투플 수를 보여준다.

SELECT `Sex`, COUNT(*) CNT FROM `kaggle`.`titanic` GROUP BY `Sex` HAVING CNT > 200;  

위의 코드처럼 'Sex' 속성을 그룹화하여 count한 뒤, HAVING (조건) 을 이용해서 count값이 200보다 큰 값만 출력되도록 하는 코드이다.

count값을 CNT로 지정해서 HAVING 조건을 이용한 것이 특징이다.

위의 결과를 보면 male은 count값이 266, female은 152이기 때문에 male의 값만 출력되었다.

SELECT `Sex`, `survived`, COUNT(*) FROM `kaggle`.`titanic` GROUP BY `Sex`, `Survived` ;

'Sex' 속성과 'survived' 속성을 각각 그룹화하여 count값을 출력하는 코드이다.

이 값을 보면 남자는 모두 생존하지 못했고, 여자는 모두 생존했음을 알 수 있다. 

SELECT floor(`Age`/10) * 10 + 10, COUNT(*) FROM `kaggle`.`titanic` WHERE `Age` > 0 group by 1; 

floor는 반올림을 해주는 함수이다. 연령대별로 몇 명의 사람이 있는지 알아보기 위해서 'Age'를 10으로 나눈 값을 반올림하고, 10을 곱한 뒤, 0~9의 값이 있기 때문에 10을 더해준다. 그러면 이 값 미만의 사람들이 몇 명있는지 알아볼 수 있다.

SELECT floor(`Age`/10) * 10 + 10, COUNT(*) FROM `kaggle`.`titanic` WHERE `Age` > 0 group by 1 order by 1;

위의 값들을 오름차순으로 정렬하는 코드이다.

SELECT floor(`Age`/10) * 10 + 10, COUNT(*) FROM `kaggle`.`titanic` WHERE `Age` > 0 group by 1 order by 1 desc;

위의 값들을 내림차순으로 정렬하는 코드이다.

SELECT floor(`Age`/10) * 10 + 10, `survived`, COUNT(*) CNT FROM `kaggle`.`titanic` 
WHERE `Age` > 0 group by 1, 1 HAVING CNT > 40 order by 1 desc, 2 desc;

위의 코드처럼 'Age'를 연령대 별로 나누고, 그 중 count값이 40보다 큰 값에 대해 'Age' 값과 'survived' 값을 각각 내림차순으로 정렬하는 코드이다.

#서브쿼리: 하나의 SQL문 안에 들어있는 또 다른 SQL문(쿼리)! 괄호( )로 나타낸다.

#서브쿼리가 조건절이 됨. 결과에 해당하는 데이터를 조건으로 해서 메인쿼리 실행.

SELECT * FROM `kaggle`.`titanic`
WHERE `PassengerId` IN (SELECT `PassengerId` FROM `test` WHERE `Age` = 0); 

서브쿼리// 'test' 테이블에서 'Age'값이 0인 'PassengerId'를 찾는다.

메인쿼리// 그 'PassengerId'를 가진 값의 모든 데이터를 출력한다. ('PassengerId'가 JOIN으로 연결되어있기 때문에 같은 값임)

UPDATE `kaggle`.`titanic`
SET `Age` = 30.272590361445783
WHERE `PassengerId` IN (SELECT `PassengerId` FROM `test` WHERE `Age` = 0) ;

위의 코드대로 'Age' 값이 0인 데이터를 찾아서 그 값들의 'Age' 값을 평균인 30.272590361445783로 업데이트하는 코드이다.

업데이트한 후 위의 코드를 다시 실행시켜보면 'Age' 값이 모두 변경된 것을 볼 수 있다.

위의 코드는 'test' 테이블에서 'Age' 값이 0인 투플을 찾은 것이기 때문에 'titanic' 테이블에서 값을 변경한 뒤 다시 확인하기에 좋았다.

CREATE DATABASE `university`;

SHOW databases;

USE `university`;

#DROP TABLE `university`.`student`;
CREATE TABLE `university`.`student`(           # ` ` 은 해도 되고 안해도 되는 것 같다. 속성도.
  `student_id` int unsigned NOT NULL ,
  `name` varchar(20) NOT NULL ,
  `address` varchar(100) NULL ,
  `birth_date` char(8) NULL ,
  `major_id` smallint NULL ,
  `enterance_year` smallint NULL ,
  `leave_yn` bit NULL default 0;
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB;

INSERT INTO `university`.`student`
(student_id, name, address, birth_date, major_id, enterance_year, leave_yn)
VALUES
(2016001, '정소정', '서울시 서초구 방배동 911-1', '19971101', 111, 2016, 0),
(2017053,'성민경','경상북도 대구 수성구 삼익 1-1','19980412',111,2017,1),
(2018111,'노규희','충청남도','19991204',130,2018,1),
(2017105,'박성희','강원도','19980908',130,2017,0);

select * from `university`.`student`;

select * from `university`.`student`
where student_id = 2016001;

select student_id, name from `university`.`student`
where major_id = 111;

UPDATE `university`.`student`
SET
address = '부산특별시 해운대 비치333-3',
leave_yn = 1
WHERE student_id = 2016001;

select * from university.student
where student_id = 2016001;

DELETE FROM university.student
WHERE student_id = 2016001;

select * from university.student 
where student_id = 2016001;

select * from university.student;

CREATE TABLE `university`.`class` (
  `class_id` int unsigned NOT NULL ,
  `name` varchar(20) NOT NULL , 
  `professor_id` int NOT NULL , 
  `major_id` smallint NULL ,
  `duration` smallint NULL ,
  `type` tinyint NULL ,
  `start_time` char(2) NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (class_id)
  ) ENGINE = InnoDB;
  
SHOW TABLES;

SHOW CREATE TABLE `university`.`class`;

select * from `university`.`class`;

DESC `university`.`class`;

INSERT INTO `university`.`class`
(class_id, name, professor_id, major_id, duration, type, start_time)
VALUES
(10000, '데이터베이스', 10001, 111, 3, 1, '13'),
(20001, '물리기초1', 20011, 111, 1, 2, '09'),
(30002, '사회학개요', 30001, 120, 2, 3, '15'),
(50003, '통계학1', 50001, 130, 3, 4, '10');

CREATE TABLE `university`.`registerClass` (
  student_id int unsigned NOT NULL ,
  class_id int unsigned NOT NULL ,
  last_update timestamp NOT NULL DEFAULT CURRNET_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (student_id, class_id) ,
  FOREIGN KEY (student_id) REFERENCES `university`.`student` (student_id) ,
  FOREIGN KEY (class_id) REFERENCES `university`.`class` (class_id)
  ) ENGINE=InnoDB;
  
INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2018111, 10000), (2018111, 5003), (2018111, 30002);

INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2017105, 20001), (2017105, 30002), (2017105, 50003);

SELECT * FROM `university`.`registerClass`;

INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2017053, 10000), (2017053, 50003);

  #INSERT INTO `university`.`registerClass`
  #(student_id, class_id)
  #VALUES
  #(2017053, 45003);

SELECT S.NAME, C.NAME, R.last_update
FROM student S join registerClass R on S.student_id = R.student_id
join class C on R.class_id = C.class_id
where S.student_id = 2017053;

SELECT S.NAME, C.NAME, R.last_update
FROM student S join registerClass R on S.student_id = R.student_id
join class C on R.class_id = C.class_id
where S.student_id = 2018111;

SELECT S.NAME, C.NAME, R.last_update
FROM student S join registerClass R on S.student_id = R.student_id
join class C on R.class_id = C.class_id
where S.student_id = 2017105;

CREATE DATABASE `university`;

`university`라는 데이터베이스를 생성한다.

SHOW databases;

현재 생성되어있는 데이터베이스들을 보여준다.

USE `university`;

`university` 데이터베이스를 이용할 것임을 선언한다.

#DROP TABLE `university`.`student`;

DROP TABLE은 이미 존재하는 이 테이블을 삭제하겠다는 의미이다.

CREATE TABLE `university`.`student`(          
  `student_id` int unsigned NOT NULL ,
  `name` varchar(20) NOT NULL ,
  `address` varchar(100) NULL ,
  `birth_date` char(8) NULL ,
  `major_id` smallint NULL ,
  `enterance_year` smallint NULL ,
  `leave_yn` bit NULL default 0;
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB;

university DB 안에 'student'라는 테이블을 생성한 것이다.

student_id, name, address, birth_date, major_id, enterance_year, leave_yn, last_update가 속성이다.

속성 이름 뒤에 도메인을 적어준다. 도메인은 속성의 특성에 맞는 자료형이다.

unsigned는 부호가 없다는 뜻이다. 예를 들어 tinyint의 경우, (signed) tinyint의 범위는 -128~127이며, 

unsigned tinyint의 범위는 0~255이다.

NULL, NOT NULL은 NULL값이 될 수 있냐 없냐를 정해주는 것이다. 예제의 경우 학번과 이름은 NULL값이 될 수 없도록 했다.

varchar( )의 괄호 속 숫자는 최대로 들어갈 수 있는 byte 값이다.

`leave_yn`는 휴학여부 속성으로 0과 1로 구분되고, 투플값이 없을 경우 default 값은 0이 들어가도록 했다.

`last_update`는 업데이트 시각으로, 직접 적는 것이 아니라 CURRNET_TIMESTAMP를 이용하여 자동으로 저장된다.

이 테이블의 기본키는 `student_id`로, 학번만 가지고도 투플들을 구분할 수 있다는 뜻이다.

MySQL의 스토리지 엔진으로 주로 MyISAM과 InnoDB가 사용된다.

스토리지 엔진은 데이터베이스 엔진이라고도 불리며, RDBMS가 데이터베이스에 대해 데이터를 삽입, 추출, 업데이트, 삭제하는 데 사용하는 기본 소프트웨어 컴포넌트이다. 

MyISAM은 항상 테이블에 ROW COUNT를 가지고 있기 때문에 SELECT 명령 시 속도가 빠르고, '풀텍스트 인덱스'를 지원하는데, 이는 자연 언어를 이용해 검색할 수 있는 특별한 인덱스로 모든 데이터 문자열의 단어를 저장한다는 것이다. 이때문에 Read only 기능이 많은 서비스일수록 효율적으로 사용할 수 있는 엔진이다. 단점으로는, row level locking을 지원하지 못해서 select, insert, update, delete 명령 시 해당 테이블 전체에 locking이 걸린다는 것이다. 그래서 갱신이 많이 필요한 경우에는 유용하지 못하다.

InnoDB는 MyISAM과 대조적인 엔진으로, 우선 row level locking이 지원된다는 장점이 있고, 트랜잭션 처리가 필요한 대용량 데이터를 다룰 때 효율적이다. 데이터의 변화가 많은 서비스에 적합하다. 또한, 유일하게 외래키를 지원하는 표준적인 스토리지 엔진이다.단점으로는, 풀텍스트 인덱스를 지원하지 않는다는 것이 있다.

주로 InnoDB를 많이 사용한다고 하고, 이번 예제에서도 InnoDB를 사용하였다. 

INSERT INTO `university`.`student`
(student_id, name, address, birth_date, major_id, enterance_year, leave_yn)
VALUES
(2016001, '정소정', '서울시 서초구 방배동 911-1', '19971101', 111, 2016, 0),
(2017053,'성민경','경상북도 대구 수성구 삼익 1-1','19980412',111,2017,1),
(2018111,'노규희','충청남도','19991204',130,2018,1),
(2017105,'박성희','강원도','19980908',130,2017,0);

student 테이블에 투플들을 삽입하는 코드이다. 

INSERT INTO로 속성을 한 번 나열하고, VALUES를 통해 각각의 개체들을 추가한다.

select * from `university`.`student`;

select from은 테이블에서 정보를 가져온다는 뜻이고, *은 테이블에 있는 모든 데이터를 가져온다는 뜻이다.

따라서 이 코드를 실행하면 university.student에 있는 4개의 투플들을 보여준다.

select * from `university`.`student`
where student_id = 2016001;

where은 조건의 개념으로, 이 경우에는 학번이 2016001인 투플의 모든 데이터를 가져온다는 뜻이 된다.

select student_id, name from `university`.`student`
where major_id = 111;

이 경우에는 selcet student_id, name 이므로, 전공번호가 111인 투플의 학번과 이름을 가져온다.

UPDATE `university`.`student`
SET
address = '부산특별시 해운대 비치333-3',
leave_yn = 1
WHERE student_id = 2016001;

update는 말 그대로 테이블을 새로운 정보로 업데이트한다는 뜻이다.

이 경우, 학번이 2016001인 투플의 주소와 휴학여부 정보를 변경하게 된다.

select * from university.student
where student_id = 2016001;

DELETE FROM university.student
WHERE student_id = 2016001;

select * from university.student 
where student_id = 2016001;

select * from university.student;

select문은 마찬가지로 정보를 가져오는 코드이고,

DELETE문은 university DB의 student 테이블에서 student_id가 2016001인 투플을 삭제하는 코드이다.

CREATE TABLE `university`.`class` (
  `class_id` int unsigned NOT NULL ,
  `name` varchar(20) NOT NULL , 
  `professor_id` int NOT NULL , 
  `major_id` smallint NULL ,
  `duration` smallint NULL ,
  `type` tinyint NULL ,
  `start_time` char(2) NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (class_id)
  ) ENGINE = InnoDB;

두번째로 universityDB 안에 'class'라는 테이블을 생성한 것이다.

속성으로는 class_id, name, professor_id, major_id, duratin, type, start_time, last_update가 있고 각각의 도메인이 달려있다.

투플을 구분할 수 있는 기본키는 class_id이다.

SHOW CREATE TABLE `university`.`class`;

class 테이블에 대한 정보를 알려준다.

select * from `university`.`class`;

DESC `university`.`class`;

테이블의 속성과 도메인, NULL 여부 등의 모든 정보를 알려주는 DESC문이다.

INSERT INTO `university`.`class`
(class_id, name, professor_id, major_id, duration, type, start_time)
VALUES
(10000, '데이터베이스', 10001, 111, 3, 1, '13'),
(20001, '물리기초1', 20011, 111, 1, 2, '09'),
(30002, '사회학개요', 30001, 120, 2, 3, '15'),
(50003, '통계학1', 50001, 130, 3, 4, '10');

class테이블에 4개의 투플을 생성하는 코드이다. 위의 INSERT구문과 같다.

CREATE TABLE `university`.`registerClass` (
  `student_id` int unsigned NOT NULL ,
  `class_id` int unsigned NOT NULL ,
  `last_update` timestamp NOT NULL DEFAULT CURRNET_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  PRIMARY KEY (student_id, class_id) ,
  FOREIGN KEY (student_id) REFERENCES `university`.`student` (student_id) ,
  FOREIGN KEY (class_id) REFERENCES `university`.`class` (class_id)
  ) ENGINE=InnoDB;

세번째로 registerClass 테이블을 생성하는 코드이다. 

속성으로는 student_id, class_id, last_update가 있고, 기본키는 student_id와 class_id이다.

이 테이블에서는 외래키를 사용하였는데, 외래키는 다른 테이블의 속성을 참조하는 속성이다.

student_id는 student 테이블의 student_id를 참조하였고,

class_id는 class 테이블의 class_id를 참조하였다. 그곳에서 값을 가져올 수 있다고 생각하면 될 것 같다.

INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2018111, 10000), (2018111, 5003), (2018111, 30002);

INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2017105, 20001), (2017105, 30002), (2017105, 50003);

SELECT * FROM `university`.`registerClass`;

INSERT INTO `university`.`registerClass`
(student_id, class_id)
VALUES
(2017053, 10000), (2017053, 50003);

resigterClass 테이블에 투플들을 생성하는 코드들이다.

코드를 보면 student_id와 class_id 투플값이 모두 student테이블의 student_id와 class테이블의 class_id에 있는 값들인 것을 확인할 수 있다. 외래키를 사용했기 때문이다.

  #INSERT INTO `university`.`registerClass`
  #(student_id, class_id)
  #VALUES
  #(2017053, 45003);

이처럼 만약 참조되는 테이블에 없는 값을 value로 입력하면 오류가 뜬다.

SELECT S.NAME, C.NAME, R.last_update                        #조회할 것 -> 결합할 것
FROM student S join registerClass R on S.student_id = R.student_id         #조건
join class C on R.class_id = C.class_id
where S.student_id = 2017053;

SELECT S.NAME, C.NAME, R.last_update
FROM student S join registerClass R on S.student_id = R.student_id
join class C on R.class_id = C.class_id
where S.student_id = 2018111;

SELECT S.NAME, C.NAME, R.last_update
FROM student S join registerClass R on S.student_id = R.student_id
join class C on R.class_id = C.class_id
where S.student_id = 2017105;

join은 릴레이션 간의 조합을 검색하는 키워드이다.

예를 들어, 첫번째 예시는 학번이 2017053인 학생의 이름(from S)과 수강한 과목(C), 수강신청시간(R)을 조회하는 코드이다.

우선 join 다음에 나오는 on은 두 테이블이 결합할 조건을 의미한다.

이 예제에서는 student테이블의 student_id와 registerClass테이블의 student_id가 같을 경우, 

student테이블의 name과 class테이블의 name과 registerClass테이블의 last_update를 결합한다는 의미이다.

결과는 다음과 같이 나온다.

두번째, 세번째 예제도 같은 코드이고 학번만 바꾼 예제이다.

1. 데이터베이스

데이터베이스는 정보 시스템 내부에서 데이터를 저장하고 있다가 필요할 때 제공하는 역할을 담당한다.

정보시스템은 조직 운영에 필요한 데이터를 수집하여 저장해두었다가 필요할 때 유용한 정보를 만들어주는 수단이다.

데이터베이스는 특정 조직의 여러 사용자가 공유하여 사용할 수 있도록 통합해서 저장한 운영데이터의 집합체이다.

특징으로는 1) 실시간 접근, 2) 계속 변화, 3) 내용 기반, 4) 동시 공유 가 있다.

데이터베이스 관리 시스템(DBMS)은 데이터베이스를 생성하여 안정적이고 효율적으로 운영하는데 필요한 기능들을 제공하는 소프트웨어이다.

DBMS의 종류에는 RDBMS(관계형 데이터베이스)와 NoSQL(Non SQL 또는 Non relational)이 있다.

1) RDBMS

RDBMS는 관계형 모델을 기반으로 하는 데이터베이스 관리 시스템인데, 관계형 모델은 술어 논리와 집합론에 기반을 두어 데이터를 "관계로서" 표현한다.

즉, 행과 열의 집합으로 구성된 테이블 묶음 형식으로 데이터를 제공한다. 

그리고 테이블 형식의 데이터를 조작할 수 있는 관계 연산자가 SQL이다. RDBMS는 SQL을 제공한다.

RDBMS에서는 '무결성 원칙'이 가장 중요한데, 이는 데이터베이스에 정확한 데이터가 유지되고 있음을 보장하는 것이다.

RDBMS의 종류로는 MySQL, Microsoft SQL Server가 있다.

2) NoSQL

NoSQL의 데이터베이스는 전통적인 관계형 데이터베이스보다 덜 제한적인 일관성 모델을 이용하는 데이터의 저장 및 검색을 위한 매커니즘을 제공한다.

빅데이터, 대량 트래픽 저장과 동시성이 높은 조회에 뛰어나다.

"많은 사용자들이 늘 동일한 데이터를 보지 않아도 된다"는 전제 하에 개발되었다.

NoSQL의 종류로는 mongoDB, cassandra, redis가 있다.

mongoDB는 문서 등의 대용량 파일에 적합하다.

cassandra는 data 속성을 특정 값으로 하나의 컬럼에 넣는다는 특징이 있다.

redis는 <key-value> 메모리성 데이터베이스로 "=" 연산을 빠르게 할 수 있다는 특징이 있다.

2. 관계 데이터 모델

데이터 모델링이란 현실 세계에 존재하는 데이터를 컴퓨터 세계의 데이터베이스로 옮기는 변환 과정이다.

관계 데이터 모델은 개념적 구조를 논리적 구조로 표현하는 논리적 데이터 모델이다.

하나의 개체에 대한 데이터를 하나의 릴레이션(Relation)에 저장한다.

릴레이션이란 하나의 개체에 관한 데이터를 2차원 데이블의 구조로 저장한 것이다. 

위의 예시가 하나의 릴레이션이고, 현재 두 개의 개체가 저장되어 있다.

① 관계 데이터 모델의 기본 용어

1) 속성 attribute (열) : 파일 관리 시스템 관점에서 '필드'에 대응

2) 투플 tuple (행): 파일 관리 시스템 관점에서 '레코드'에 대응

3) 도메인 domain: 일반적으로 속성의 특성을 고려한 데이터 타입으로 정의

                        속성 값을 입력 및 수정할 때 적합성의 판단 기준이 됨

위의 예시에서 학번, 학생이름, 주소, 생년월일, 전공번호, 휴학여부가 속성이다.

홍길동, 이지은이 투플이며, INT, VARCHAR, CHAR가 도메인이다.

② 릴레이션의 구성

1) 릴레이션 스키마

릴레이션 스키마는 릴레이션의 논리적 구조로, 릴레이션의 이름과 릴레이션에 포함된 모든 속성 이름으로 정의된다.

정적인 특징이 있다. (한번 만들어지면 거의 변경이 없다.)

ex) 학생 (학번, 학생이름, 주소, 생년월일, 전공번호, 휴학여부)

2) 릴레이션 인스턴스

릴레이션 인스턴스는 어느 한 시점에 릴레이션에 존재하는 투플들의 집합으로, 동적인 특징이 있다.

위의 예시에서는 학생 한 명 한 명이 각각 인스턴스가 된다.

③ 데이터베이스의 구성

1) 데이터베이스 스키마

데이터베이스 스키마는 데이터베이스의 전체 구조로, 데이터베이스를 구성하는 각각의 릴레이션 스키마의 모음이다.

2) 데이터베이스 인스턴스

데이터베이스 인스턴스는 데이터베이스를 구성하는 각각의 릴레이션 인스턴스의 모음이다.

④ 릴레이션의 특성

1) 투플의 유일성: 하나의 릴레이션에는 동일한 투플이 존재할 수 없다. (모든 속성의 값이 동일한 투플 X)

2) 투플의 무순서: 하나의 릴레이션에서 투플 사이의 순서는 무의미하다.

3) 속성의 무순서: 하나의 릴레이션에서 속성 사이의 순서는 무의미하다.

4) 속성의 원자성: 속성 값으로 원자 값만 사용할 수 있다.

(더이상 분해할 수 없는 속성 = 단순 속성 -> data의 일관성)

단순속성 ↔ 복합 속성 ex) 주소는 '시', '도', '군' 의 단순 속성 여러 개를 활용하여 구현이 가능하다.

⑤ 키(KEY) 정의와 특성

1) 키의 정의: 릴레이션에서 투플들을 유일하게 구별하는 속성 또는 속성들의 집합

2) 키의 특성

 - 유일성: 하나의 릴레이션에서 모든 투플은 서로 다른 키 값을 가져야 한다.

 - 최소성: 꼭 필요한 최소한의 속성들로만 키를 구성해야 한다.

⑥ 키의 종류와 관계

1) 수퍼키(super key): 유일성을 만족하는 속성 또는 속성들의 집합

2) 후보키(candidate key): 유일성과 최소성을 만족하는 속성 또는 속성들의 집합

3) 기본키(primary key): 후보키 중에서 기본적으로 사용하기 위해 선택한 키

4) 대체키(alternate key): 기본키로 선택되지 못한 후보키

5) 외래키(foreign key): 다른 릴레이션의 기본키를 참조하는 속성 또는 속성들의 집합

                              릴레이션들 간의 관계를 표현