우아 한의원 | 조재성 원장의 한의학, 의학통계, 프로그래밍

알아두어야 할 개념

1. SELECT '문자열' AS 칼럼명으로 새로운 칼럼을 생성할 수 있다.
2. UNION은 2개의 쿼리문의 칼럼의 갯수/이름이 모두 같을 때 합칠 수 있다.
3. 전체범주명을 깔고, 특정 범주명을 UNION하는 것은 안되는 것 같다. CASE WHEN 처럼 범주를 다 나눈 다음 UNIO해주는 방법밖에 없나??
4. UNION은 합집합, UNION ALL은 합집합+교집합 포함

코드

SELECT '5~10만' AS TYPE, * FROM person WHERE personid BETWEEN 50000 AND 100000
UNION 
SELECT '기본 범주' AS TYPE, * FROM person WHERE personid < 50000 OR personid > 100000
ORDER BY personid

csv파일로 보내기

mssql 쿼리문 결과에서 우클릭하기

sql파일

엑셀파일

CREATE DATABASE practice_medi2

USE practice_medi2

-- CodeMaster 칼럼2개 제외 DB우클릭 > T, I > END + W + TAb 아래2칸 + ShiftTaB3칸 파일선택  > N > N > END 위로3칸  > N N F 
-- 가져오기마법사로 실패하는 경우
-- 1. Table 생성 > 2. BELK INSERT 로 csv 파일 집어넣기

create table Person (
personid int,
sex int,
birthday datetime,
ethnicity int
)

--
bulk insert Person from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_person.csv'
with (firstrow=2, format='CSV')



create table Electrocardiogram (
personid int,
ecgdate datetime null,
RR int null,
QT int null,
QTc int null,
ACCI int null,
ecgdept varchar(2) null,
ecgsource varchar(2) null,
)
bulk insert Electrocardiogram from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_Electrocardiogram.csv'
with (firstrow=2, KEEPNULLS, format='CSV')


create table Laboratory (
personid int,
labdate datetime null,
labname varchar(2) null,
labvalue float null
)

bulk insert Laboratory from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_Laboratory.csv'
with (firstrow=2, KEEPNULLS, format='CSV')




create table Diagnosis (
personid int,
diagdate datetime,
diagcode varchar(20),
diaglocalcode varchar(20),
diagdept varchar(2)
)

bulk insert Diagnosis from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_Diagnosis.csv'
with (firstrow=2, KEEPNULLS, format='CSV')




drop table Drug
create table Drug (
personid int,
drugdate datetime null,
druglocalcode varchar(20),
atccode varchar(20),
drugdept varchar(2),
[route] varchar(2),
duration int
)

bulk insert Drug from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_Drug.csv'
with (firstrow=2, KEEPNULLS, format='CSV')



create table DiagnosisCodeMaster (
diaglocalcode varchar(20),
diagnosis varchar(max)
)


bulk insert DiagnosisCodeMaster from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\DiagnosisCodeMaster.csv'
with (firstrow=2, KEEPNULLS, format='CSV')



create table DrugCodeMaster (
druglocalcode varchar(20),
drugigrdname varchar(100)
)
bulk insert DrugCodeMaster from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\DrugCodeMaster.csv'
with (firstrow=2, KEEPNULLS, format='CSV')


-- 실전 분석해보기 --
-- alt+f1, 엑셀--
[dbo].[person]
[dbo].[drug]
[dbo].[DrugCodeMaster]
[dbo].[Diagnosis]
[dbo].[DiagnosisCodeMaster]
[dbo].[Electrocardiogram]
[dbo].[laboratory]

-- 개수 한꺼번에 세기
SELECT COUNT(1) FROM [person]                --18570
SELECT COUNT(1) FROM [drug]                    --1890626
SELECT COUNT(1) FROM [DrugCodeMaster]        --2627
SELECT COUNT(1) FROM [Diagnosis]            --296690
SELECT COUNT(1) FROM [DiagnosisCodeMaster]    --7553
SELECT COUNT(1) FROM [Electrocardiogram]    --35932
SELECT COUNT(1) FROM [laboratory]            --147716


---person table 분석
Person
--칼럼명
personid
sex
birthday
ethnicity

-- 데이터 전체 head보기
SELECT TOP 100 * FROM person
-- key 칼럼의 distinct한 개수 보기
-- person
SELECT COUNT(DISTINCT personid) FROM person        --18570

-- sex    
SELECT TOP 100 sex FROM person                    --head

SELECT DISTINCT sex FROM person                    --sex의 종류 : 1, 0

SELECT COUNT(DISTINCT sex) FROM person            --sex 종류개수 : 2가지 - 종류가 너무 많을 때

SELECT sex, COUNT(DISTINCTpersonid) cnt FROM person        --1    9307 / 0    9263
GROUP BY sex


SELECT 9307 + 9263                                -- 전체(18570)과 sex의 범주별 합(1+0)이 같은지 보고, sex에서 null없나 확인

SELECT * FROM person                            -- sex에 null값 확인
WHERE sex IS NULL

SELECT sex, COUNT(personid) FROM person
GROUP BY sex
ORDER BY 1

--birthday
SELECT TOP 100 birthday FROM person                    --head

SELECT birthday, COUNT(personid) cnt FROM person    --시계열별 groupby count(사람수)
GROUP BY birthday

SELECT birthday, COUNT(personid) cnt FROM person    --시계열별 groupby count(사람수) -> Order by 후 엑셀 시각화
GROUP BY birthday
ORDER BY 1


SELECT GETDATE()

SELECT DATEDIFF(year, birthday, GETDATE()) FROM person        -- 해나이 : interval을 year로
SELECT DATEDIFF(day, birthday, GETDATE()) / 365 FROM person -- 만나이 : interval을 day로 계산한 뒤, 365일을 나눠주기(몫만 남음)

SELECT  DATEDIFF(year, birthday, GETDATE()) 해나이, DATEDIFF(day, birthday, GETDATE()) / 365 만나이 FROM person


SELECT
CASE WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 0 and 9
    THEN '10세 미만'
     WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 10 and 19
    THEN '10대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 20 and 29
    THEN '20대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 30 and 39
    THEN '30대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 40 and 49
    THEN '40대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 50 and 59
    THEN '50대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 60 and 69
    THEN '60대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 70 and 79
    THEN '70대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 80 and 89
    THEN '80대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 >= 90
    THEN '90세 이상'
END AS 연령대, * FROM person

-- 변형칼럼을 groupby 하기 위해, SELECT *INTO* FROM으로 새 테이블 만들기
SELECT
CASE WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 0 and 9
    THEN '10세 미만'
     WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 10 and 19
    THEN '10대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 20 and 29
    THEN '20대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 30 and 39
    THEN '30대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 40 and 49
    THEN '40대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 50 and 59
    THEN '50대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 60 and 69
    THEN '60대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 70 and 79
    THEN '70대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 80 and 89
    THEN '80대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 >= 90
    THEN '90세 이상'
END AS 연령대, * 
INTO person_with_age
FROM person

SELECT TOP 100 * FROM person_with_age

-- 새 테이블로 연령대별 groupby 사람수 카운트하기
SELECT 연령대, COUNT(personid) cnt FROM person_with_age
GROUP BY 연령대
ORDER BY 1


--subquery로 테이블 생성없이 하기
SELECT 연령대, COUNT(personid) cnt FROM (
SELECT
CASE WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 0 and 9
    THEN '10세 미만'
     WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 10 and 19
    THEN '10대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 20 and 29
    THEN '20대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 30 and 39
    THEN '30대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 40 and 49
    THEN '40대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 50 and 59
    THEN '50대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 60 and 69
    THEN '60대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 70 and 79
    THEN '70대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 80 and 89
    THEN '80대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 >= 90
    THEN '90세 이상'
END AS 연령대, * FROM person
)Z
GROUP BY 연령대
ORDER BY 1

-- sex > 연령대 순으로 해야지 엑셀로 시각화 하기 쉬움(나중에 앎)
SELECT 연령대, sex,  COUNT(personid) cnt FROM (
SELECT
CASE WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 0 and 9
    THEN '10세 미만'
     WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 10 and 19
    THEN '10대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 20 and 29
    THEN '20대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 30 and 39
    THEN '30대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 40 and 49
    THEN '40대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 50 and 59
    THEN '50대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 60 and 69
    THEN '60대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 70 and 79
    THEN '70대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 80 and 89
    THEN '80대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 >= 90
    THEN '90세 이상'
END AS 연령대, * FROM person
)Z
GROUP BY 연령대, sex
ORDER BY 1, 2

-- 엑셀에서 연령대별 성별별 시각화해보기 : shift+핸들바로 밀어올리기 활용 / 홈>스타일> 조건부서식 > 데이터 막대



--ethnicity 칼럼
SELECT TOP 100 ethnicity FROM person

SELECT DISTINCT ethnicity FROM person

SELECT COUNT(DISTINCT ethnicity) FROM person

SELECT ethnicity, COUNT(personid) cnt FROM person
GROUP BY ethnicity
ORDER BY 1
--0    172
--1    18398




--drug 테이블--
drug
personid
drugdate
druglocalcode
atccode
drugdept
route
duration

SELECT COUNT(1) FROM drug                                --1890626

SELECT TOP 10 * FROM drug

--key칼럼인 personid
SELECT COUNT(DISTINCT personid) FROM person                --18570
SELECT COUNT(DISTINCT personid) FROM drug                --15430  약 안받은 환자 3100명

SELECT cast(15430 as float)/ cast(18570 as float)        --0.830910070005385 = 83%
SELECT 15430 * 1.0 / 18570


--범주형 drugdept
SELECT TOP 100 drugdept FROM drug

SELECT DISTINCT drugdept FROM drug                        -- I E H O

SELECT COUNT(DISTINCT drugdept) FROM drug                -- 범주 4가지

SELECT drugdept, COUNT(personid) cnt FROM drug
GROUP BY drugdept
ORDER BY 1
--E    82257
--H    1770
--I    1431086
--O    375513

SELECT drugdept, COUNT(DISTINCT personid) cnt FROM drug
GROUP BY drugdept
ORDER BY 1
--E    5298
--H    868
--I    8747
--O    11294

SELECT 82257+1770+1431086+375513                        --1890626  == drug전체데이터 1890626 => null값 없음
SELECT * FROM drug WHERE drugdept IS NULL




-- 범주형 route칼럼
SELECT TOP 100 route FROM drug

SELECT DISTINCT route FROM drug                            --E P 
SELECT COUNT(DISTINCT route) FROM drug                    --2종류

SELECT route, COUNT(personid) cnt FROM drug
GROUP BY route
ORDER BY 1
--E    1176263
--P    714363

SELECT * FROM drug WHERE route IS NULL






-- 범주형 같아보이지만 연속형 duration
SELECT TOP 100 duration FROM drug


SELECT DISTINCT duration FROM drug    
SELECT DISTINCT CONVERT(smallint, duration) "duration(int)" FROM drug        --응용                    -- 
ORDER BY 1

SELECT COUNT(DISTINCT duration) FROM drug                    --126종류


ALTER TABLE drug ALTER COLUMN duration int                    --칼럼 타입 변경

SELECT MIN(duration) min, MAX(duration) max, AVG(duration) avg FROM drug -- 0    390    7





--날짜(생년월일x) 칼럼 drugdate
SELECT TOP 100 drugdate FROM drug

ALTER TABLE drug ALTER COLUMN drugdate DATETIME                    --칼럼 타입 변경

SELECT MIN(drugdate) min, MAX(drugdate) max FROM drug        --1994-06-24 00:00:00    2011-08-23 00:00:00

SELECT TOP 100 CONVERT(char(20), drugdate, 112) as YYYYmmdd, * FROM drug -- CONVERT 112 : YYYYmmdd

SELECT TOP 100 SUBSTRING(CONVERT(char(20), drugdate, 112), 1, 4) as year FROM drug -- 필요시 SUBSTRING( CONVERT, 시작,이후갯수) ->: YYYY

SELECT YYYYmmdd
FROM (
SELECT  CONVERT(char(20), drugdate, 112) as YYYYmmdd, * FROM drug 
)Z

SELECT MIN(YYYYmmdd) min, MAX(YYYYmmdd) max                                                                
FROM (
SELECT CONVERT(char(20), drugdate, 112) as YYYYmmdd, * FROM drug 
)Z


SELECT YYYYmmdd year, COUNT(personid)                                                             
FROM (
SELECT CONVERT(char(20), drugdate, 112) as YYYYmmdd, * FROM drug 
)Z
GROUP BY YYYYmmdd
ORDER BY 1





--연속형처럼보이는 범주형 code칼럼 : druglocalcode

SELECT DISTINCT druglocalcode FROM drug                    -- 종류 너무 많음

SELECT COUNT(DISTINCT druglocalcode) FROM drug            --2276

SELECT druglocalcode, COUNT(personid) cnt            -- 범주의 종류가 많으면, 카운트 칼럼으로 머를 많이 썼는지 DESC ORDER BY
FROM drug
GROUP BY druglocalcode
ORDER BY 2 DESC
--Drug2069    27997
--Drug1472    23168
--Drug452    22800
--Drug2163    21542
--Drug2501    20501


SELECT * FROM DrugCodeMaster WHERE druglocalcode = 'Drug2069';    --Drug2069    Tramadol.HCl
--1)
SELECT drugigrname FROM DrugCodeMaster WHERE druglocalcode = 'Drug2069';
--2)
SELECT drugigrname FROM DrugCodeMaster WHERE druglocalcode = druglocalcode;
--3)
SELECT 
(SELECT drugigrname FROM DrugCodeMaster dcm WHERE dcm.druglocalcode = d.druglocalcode) drugigrname, *
FROM drug d
--4)
SELECT 
(SELECT drugigrname FROM DrugCodeMaster dcm WHERE dcm.druglocalcode = d.druglocalcode) drugigrname, d.druglocalcode, COUNT(personid) cnt
FROM drug d
GROUP BY d.druglocalcode
ORDER BY 3 DESC

--join
SELECT * FROM Drug a
JOIN DrugCodeMaster b ON a.druglocalcode = b.druglocalcode

SELECT drugigrname, a.druglocalcode, COUNT(personid) cnt FROM Drug a
JOIN DrugCodeMaster b ON a.druglocalcode = b.druglocalcode
GROUP BY drugigrname, a.druglocalcode
ORDER BY 3 DESC


--연도별 처방빈도
SELECT SUBSTRING( CONVERT(varchar(8), drugdate,112), 1, 4) year, * FROM drug

SELECT year, COUNT(personid) cnt
FROM(
SELECT SUBSTRING( CONVERT(varchar(8), drugdate,112), 1, 4) year, * FROM drug
)Z
GROUP BY year
ORDER BY year DESC

--월별별 처방빈도
SELECT months, COUNT(personid) cnt
FROM(
SELECT SUBSTRING( CONVERT(varchar(8), drugdate,112), 5, 2) months, * FROM drug
)Z
GROUP BY months
ORDER BY months ASC



-- 범주형칼럼 route
SELECT TOP 100 route FROM drug

SELECT DISTINCT route FROM drug

SELECT COUNT(DISTINCT route) FROM drug

SELECT route, COUNT(DISTINCT personid) cnt FROM drug 
GROUP BY route
--E    14321
--P    12077

SELECT COUNT(DISTINCT personid) FROM drug            -- 15430 사람수보다 E + P이 크다 = 교집합이 있다.

SELECT * FROM drug WHERE route IS NULL



--의미내포 범주형 code칼럼 : atccode
SELECT TOP 100 atccode FROM drug

SELECT COUNT(DISTINCT atccode) FROM drug            --984

SELECT LEFT(atccode, 1) FROM drug
SELECT LEFT(atccode, 1), * FROM drug

SELECT                                                -- 내포코드별 빈도확인
atc_1, COUNT(personid) cnt
FROM(
SELECT LEFT(atccode, 1) atc_1, * FROM drug
)Z
GROUP BY atc_1
ORDER BY 2 DESC


-- 연도별 내포코드별 빈도확인
SELECT SUBSTRING(CONVERT(varchar(8), drugdate, 112), 1, 4) FROM drug

SELECT                                                
year, atc_1, COUNT(personid) cnt
FROM(
SELECT SUBSTRING(CONVERT(varchar(8), drugdate, 112), 1, 4) year, LEFT(atccode, 1) atc_1, * FROM drug
)Z
GROUP BY year, atc_1
ORDER BY 1 ASC

MSSQL 단축키

1. ALT + F1 : 테이블 정보보기
2. CTRL + K + C : 단번에 주석처리
3. CTRL + K + U : 단번에 주석제거

• DATEDIFF, CONVERT, SUBSTRING()으로 가공한 칼럼을 다시 처리( COUNT, GROUP BY, MIN, MAX )할 때 : SUBQUERY with alias
• WHERE LIKE %(any char) or _(single char)로 필터링
• COUNT(key칼럼)시 중복허용 전체수(약 처방건수, 약 code = 약 몇개 등)이냐 / 유니크한수(Person테이블의 id와 join되는 해당하는 각 테이블별 id칼럼 -> 사람 몇명, 약탄 사람 몇명 등)냐 고려하기
• 백업을 위한 RENAME TABLE a TO b
• 날짜는 CONVERT 후 SUBSTRING or LEFT / 생일은 DATEDIFF로 ( 나이변경 ) -> DATEDIFF + CASE WHEN( 연령대구간 ) 까지 해당테이블에서 한 뒤, subquery로 groupby 
• 집계함수를 적용하는 집계테이블에 대해서는 SELECT로 따로 칼럼 보려면 --> GROUP BY 안에 넣어줘야한다.
• JOIN으로 인해 기준테이이블 + 집계적용할 테이블이   따로 있는 경우는 --> 기준테이블의 id로 GROUP BY하고, 칼럼도 마음대로 SELECT 가능

DB 분석

1. DB 생성 후 use

2. Table을 csv로 가져오기(db우클릭>테스크>데이터가져오기) or CREATE TABLE 생성후 BULK INSERT 칼럼명 FROM '파일'

3. 각 Table 정보보기(ALT+F1) -> 엑셀에서 테이블별 칼럼들 시각화 해보기 + 주석으로 칼럼명 나열해놓기
   이미 했다면, 칼럼명들 복사해놓기

4. 각 테이블의 개수 세기(COUNT(1)) - 여러테이블에 대해 key칼럼을 모르는 상태에서는 1로 count
   -중복허용 전체 데이터수

5. 각 테이블의 각 칼럼들 분석

   - 전체 head보기

   - id칼럼이 겹친다면, 각 테이블마다 DISTINCT id칼럼을 count해서 비율 비교하기 ex> 환자수 : 약을 처방받은 환자수 
       => 각각 cast( as float)한 다음 나눠서 or 분자에 * 1.0 만 곱해서 비율보기
   COUNT(key칼럼)
   ex> 전체 환자수 vs 약을 받은 환자수
   ex> 전체 환자 중 약을 받은 환자의 비율

   각 칼럼이 범주형인지 / 연속형(숫자,코드)/ 시계열인지 보기
   groupby시 항상 key칼럼(id, pk,fk)으로 COUNT하기

범주형

0. 각 칼럼의 head만 보기 (cf. key칼럼 = 사람을 의미)

1. distinct한 범주 종류보기 -> 종류가 너무 많을 때, distinct한 범주의 수 세기(범주가 너무 많으면 세야함)

2. 범주별groupby를 DISTINCT key칼럼(사람수)으로 count -> DISTINCT key칼럼의 전체 개수와 비교해서 범주안의 null찾기or 서로 교집합이 존재유무확인 or where 범주 is null로 확인하기

3. DISTINCT count하는 GROUPBY(환자, 사람) 인지 / 그냥 count하는 GROUPBY(처방된 약의개수, 건수, 빈도) 생각해서 하기
   
   

매우많은 범주형 CODE칼럼 : Drug - druglocalcode

0. 각 칼럼의 head만 보기

1. 많은 범주의 갯수 확인하기 (code들은 DISTINCT한 범주의 수 COUNT하기)
   -범주의 종류는 너무 많으므로, count만 해도 된다.

2. 코드별 빈도 TOP5 확인
   code별로 groupby를 key칼럼 COUNT + code들은 범주의 종류가 너무 많으므로 어떤 약을 많이 썼는지 Cnt로 DESC ORDER BY => TOP5만 복사해놓기

3. 코드로 -> 다른테이블 처방 성분명확인 1)
   top 1 code를 code해석테이블에서 WHERE 코드명 = top1코드명으로 조회하고, 해석칼럼명 기억하기

4. 코드로 -> 다른테이블 처방 성분명확인한 해석칼럼만 SELECT안의 칼럼으로 가져오기(inlineView)   2)
   3.의 조회문장에
   1) 해석테이블의 SELECT 문장에 * 를 해석칼럼명으로 바꾼다.( 그것만 가져올 준비)
   2) 코드테이블안에 1) 쿼리를 ()괄호로 싸서 inlineview형태의 칼럼처럼 가져온다.
   3) 각 테이블에 alias를 주고 -> inlineview 안의 WHERE절에 alias를 달면서 join의 ON처럼 a.코드칼럼 = b.코드칼럼을 조건으로 준다.
   4) 코드테이블안의 다른칼럼들도 다 가져온다 *
   5) 바깥 SELECT 문에서 필요한 칼럼inlineview(해석칼럼), code칼럼 + COUNT()-groupby만 챙기기 + ORDER by cnt DESC 넣기
   inlineview는 가공칼럼이라 group by에 alias가 못들어간다. 그래서 code칼럼만 넣어준다.

5. 코드로 -> 다른테이블 처방 성분명확인 1-2)
   JOIN문으로 4번을 대체하기
   
   
   

의미를 내포하는 매우많은 범주형 CODE칼럼 : Drug - atccode

0. 각 칼럼의 head만 보기

1. 많은 범주의 갯수 확인하기 (code들은 DISTINCT한 범주의 수 COUNT하기)
   -범주의 종류는 너무 많으므로, count만 해도 된다.

2. 단순코드와 다르게, 먼저 의미 잘라내기
   여기서는 첫번째 시작문자만 잘라준다. DATETIME을 자를 때는 CONVERT 후 SUBSTRING or LEFT, 문자열 코드는 바로 SUBSTRING or LEFT로 자르기 => 변형 칼럼 alias + 다시 전체칼럼(*)들과 같이 SELECT하는 subquery로 필요한 칼럼들만 선택해서 처리 or 같은 테이블내 LEFT()등의 1차 처리는 바로 컬럼으로 사용가능 but GROUPBY 에서도 1차 처리한 문장을 주어야함. alias 안들어감

3. 코드별 빈도확인code별로 groupby를 key칼럼 COUNT + code들은 범주의 종류가 너무 많으므로 어떤 약을 많이 썼는지 Cnt로 DESC ORDER BY => TOP5만 복사해놓기

4. 연도별 + 코드별 빈도확인
   1) datetime칼럼에서 연도만 잘라낸 뒤(CONVERT -> SUBSTRING or LEFT)
   2) 연도칼럼을 2에서 제작한 subquery(가상테이블)에 alias와 함께 합친다.
   3) groupby + order by

5. long데이터 엑셀 시각화

만약, subquery를 안쓰고 가공한 칼럼을 SELECT문에 그대로 사용한다면, GROUP BY 자리에서는 alias가 안통하고 통째로 옮겨줘야한다.

범주형처럼 문자열 되어있는 연속형(숫자) : Drug - duration

0. 각 칼럼의 head만 보기

1. 만약, 범주의 종류를 확인해보고 싶다면, CONVERT(int,칼럼명) + alias한 것의 DISTINCT -> ORDER BY alias

2. int형으로 바꿔준다.문자형에 MIN, MAX를 사용한다면, 맨앞의 숫자를 기준으로 정렬되기 때문에, int나 datetime에서만 MIN, MAX를 사용하자 UPDATE는 값에 대한 것들이다. 컬럼자체의 정보는 table 단위로 해석해서 DDL인 ALTER를 사용한다.

   ALTER TABLE drug ALTER COLUMN duration int

   

3. 연속형은 MIN, MAX, AVG로 처리.3개의 통계함수로 한번에 SELECT 후 => 평균 보고 min,max에서 아웃라이어 판단하기

생년월일(시계열,날짜형1) : Person-birthday**

0. 각 칼럼의 head만 보기 (cf. key칼럼 = 사람을 의미)
   (만약, string타입이면 DATETIME 타입으로 바꾸기)

   ALTER TABLE drug ALTER COLUMN drugdate DATETIME

1. 시계열별groupby를 key칼럼으로 count 하기

2. order by까지 완료 후 -> 엑셀로 시각화하기(셀서식-날짜-피벗)

3. DATEDIFF( year/day , 시작날짜, 현재날짜(GETDATE())를 이용하여 생년월일 -> 나이( 해 나이 / 만 나이 )로 변형된 컬럼 보기

4. DATEDIFF문을 그대로 이용해서 CASE WHEN THEN END로 만 나이를 연령대별 구간으로 짤라 범주형으로 만들고 난뒤 +  * 와 같이보기 

5. 변형 칼럼인 연령대별(범주별) key칼럼으로 개수(사람수) 세기 위해 4번에서 SELECT ~ FROM 사이에 INTO 새 테이블명으로 테이블 만들기 or Subquery를 이용해 테이블로 취급하여 칼럼 SELEC하기

6. 연령대별 사람수를 엑셀로 시각화하기

7. subquery로 만든 연령대별 + 범주별(sex) 카운트해보기

8. 연령대별+성별별 count를 엑셀에서 종모양으로 시각화해보기

날짜(시계열,날짜형2) : Drug-drugdate**

0. 각 칼럼의 head만 보기 (cf. key칼럼 = 사람을 의미)
   (만약, string타입이면 DATETIME 타입으로 바꾸기)

   ALTER TABLE drug ALTER COLUMN drugdate DATETIME

1. MIN, MAX로 데이터의 시작과 끝날짜 확인하여 데이터 구성날짜 알기

2. CONVERT(type,칼럼명,style)로 datetime style 바꿔주기 + TOP 100 붙혀서 조금만 조회하기

   • style
     10 = mm-dd-yy <- char(8)
     12 = yymmdd <- char(8)
     112 = yyyymmdd <- char(8)
     20 = yyyy-mm-dd <- char(10)
   • (필요시)연도만 잘라내기 위해 SUBSTRING(칼럼명,시작index,그 뒤로 개수)

3. 연도별 drug처방빈도 확인하기
   날짜CONVERT칼럼 with alias + 원본(*)칼럼들합해서 subquery로 FROM 뒤에 가상테이블화하는 subquery(변형칼럼 alias필수)를 이용하여 (TOP 100 지우기)
   -SELECT 날짜alias 선택후 COUNT, MIN, MAX, GROUP BY 등 FROM SUBQUERY로 가상테이블

4. order by까지 완료 후 -> 엑셀로 시각화하기(셀서식-날짜-추천 피벗-피벗차트)

def class

DB를 가져와서 엑셀로 시각화하기

각 칼럼 분석하기

JOIN의 핵심

key 칼럼을 잘 설정했느냐

JOIN의 종류

1. (INNER) JOIN : 교집합
2. (LEFT/RIGHT/FULL) OUTER JOIN : 차집합, 여집합
3. CROSS JOIN : 곱집합

for문은 DB에서 크게 의미없다.  오히려 파이썬 for문이 더 빠르다고 한다.

JOIN 연습하기

CREATE TABLE A (
Letter_U CHAR(1),  --문자1개
Number smallint    --126까지 허용
)
INSERT A VALUES ('A', 1)
INSERT A VALUES ('A', 2)
INSERT A VALUES ('B', 1)
INSERT A VALUES ('C', 2)

SELECT * FROM A

CREATE TABLE B (
Letter_U CHAR(1), -- A테이블과 연결 key칼럼
Letter_S CHAR(1)
)

INSERT B VALUES ('A', 'a')
INSERT B VALUES ('D', 'd')

SELECT * FROM A
SELECT * FROM B

--- JOIN 해보기 ---***
-- WHERE가 일반 조건문이라면, JOIN문의 JOIN의 WHERE는 ON이다.!  JOIN ON 테이블.key칼럼!***
-- SELECT FROM A AS - JOIN B AS - ON -.key칼럼 = -.key칼럼
--1. INNER JOIN = JOIN  : 교집합이라 선후관계가 상관없다.
SELECT * FROM A
     JOIN B ON A.Letter_U  = B.Letter_U

--2. alias를 이용해서, 해보기
SELECT * FROM A AS a
     JOIN B AS b ON a.Letter_U = b.Letter_S

--3. OUTER JOIN -LEFT JOIN : 한쪽 테이블을 다 살리면서 붙힌다.***---
-- 실전에서는 잘 안쓰긴 하나, 쓰는 경우는
-- A 진단이 없었던 환자를 찾아줘 -> A 진단 있는 것을 LEFT JOIN 으로 붙힌 다음, B테이블이 NULL인 환자를 찾기

-- 1) LEFT JOIN

SELECT * FROM A AS ta
     LEFT JOIN B tb ON ta.Letter_U = tb.Letter_U

-- 연습. JOIN된 것 중에    칼럼 IS NULL 인것만 뽑아보기
SELECT * FROM A ta
     LEFT JOIN B tb ON ta.Letter_U = tb.Letter_U
     WHERE tb.Letter_U IS NULL

--***참고 DELETE를 할 때는, 먼저 SELECT를 한 뒤 하자.***--
INSERT B VALUES ('C', 'c');
SELECT * FROM B WHERE Letter_S = 'C'
DELETE FROM B WHERE Letter_S = 'C'

-- 2) RIGHT JOIN
-- RIGHT 테이블인 B보다 갯수가 늘었다  = Key칼럼자체가 Unique하지 않은 상태로, 서로 빼먹은 것이 존재해서 늘어남***
SELECT * FROM A ta
    RIGHT JOIN B tb ON ta.Letter_U = tb.Letter_U

-- 3) FULL OUTER JOIN ***
-- 교집합 뿐만 아니라 각각의 테이블이 가지는 여집합(차집합)까지 모두 포함해서(NULL이 한곳에서 뜬 상태)나온다.
SELECT * FROM A ta
     FULL OUTER JOIN B tb ON ta.Letter_U = tb.Letter_U

--- CROSS JOIN (곱집합)---
-- 쓸일 진짜 없다. 데이터 뿔릴 때 씀
-- 예를 들어, 학년 X 반의 모든 콤비네이션을 만들 때,

CREATE TABLE 학년
( 학년 CHAR(1) )

CREATE TABLE 반
( 반 CHAR(1) )

INSERT 학년 VALUES (1)
INSERT 학년 VALUES (2)
INSERT 학년 VALUES (3)
INSERT 학년 VALUES (4)
INSERT 학년 VALUES (5)
INSERT 학년 VALUES (6)

INSERT 반 VALUES (1)
INSERT 반 VALUES (2)
INSERT 반 VALUES (3)
INSERT 반 VALUES (4)

SELECT * FROM 학년
SELECT * FROM 반

SELECT 학년, 반 FROM 학년 ta
     CROSS JOIN 반 tb

-- 2번째 칼럼인 반을 기준으로 정렬되길래 ORDER BY 1로 첫번째 칼럼을 기준으로 정렬시켜보기***
SELECT 학년, 반 FROM 학년 ta
     CROSS JOIN 반 tb
     ORDER BY 1

-- SELECT뒤에 나올 칼럼을 + 연산할 수 있으며, 문자열이 들어가면, 문자열로 연결해준다 ****
-- 이쁘게 만들기 위해, SELECT 부분에 학년 + 반 칼럼을 '-'로 문자열로 이어주면, MSSQL이 알아서 문자열로 취급해준다.
SELECT 학년 + '-' + 반 FROM 학년 ta
     CROSS JOIN 반 tb

-- 연습용 Table 삭제하기
DROP TABLE A
DROP TABLE B
DROP TABLE 학년
DROP TABLE 반

index

인덱스를 해야하는 이유는 쿼리 속도를 늘리기 위해서,

• 원래 50개의 데이터 -> 기계어 -> 메모리 or 하드에 01010101 로 데이터+주소를 파일형태로 저장
• 하나의 쿼리 -> where를 이용해서 특정칼럼을 뽑아오는데, 파일시스템은 전체 데이터를 검색한다.
• 특정칼럼을 인덱스로 지정해놓으면 (여러개 칼럼 중 자주 사용하는 특정칼럼(자주 변하는 체중))
  -> 컴퓨터가 인덱스 칼럼를 뽑아서 새로운 파일로 만들어 놓는다.
  -> 쿼리날릴 때 WHERE조건에 인덱스를 주면, 아주 빠르게 검색해온다.
  
  
• 인덱스의 종류는 2가지가 있는데, Clustered / NonClustered가 있는데 Clustered가 더 빠르다.
• MSSQL에서는 1개의 Clustered 밖에 못 만들고, 보통은 PRIMARY KEY를 인덱스로 지정한다.
  
  
• 이름- 주민번호 / 주민번호-학번  의 2개의 테이블이 있다면,
  주민번호가 한 테이블의 PRIMARY KEY가 되고 -> 다른 테이블의 FOREIGN KEY가 되며 -> 각 KEY는 index로 잡혀 있는게 일반적인 RDB의 기본 index형태이다.
  
  
• T-SQL에서는 1개의 CLUSTERED INDEX를 지정할 수 있으니
  WHERE절이나, JOIN절에 가장 많이 넣는 칼럼을 index로 지정하자.

가진 Table의 index 확인

sp_help 테이블네임 -> index가 있을 경우, index_name이 보인다.

sp_help BANK_A -> index 지정 안해줬으니 안나옴.

--★ Index 예시 ★--------
CREATE CLUSTERED INDEX personid ON Person (personid);
CREATE NONCLUSTERED INDEX birthday ON Person (birthday);

SET SHOWPLAN_ALL { ON | OFF } 
SET SHOWPLAN_TEXT { ON | OFF } 
SET STATISTICS IO { ON | OFF } 
SET STATISTICS TIME { ON | OFF } 
SET STATISTICS PROFILE { ON | OFF } 

Data Science Life Cyle에서의 SQL

1. Business Understanding
2. Data Mining
3. Data Cleaning
4. Data Exploration
5. Feature Engineering
6. Predictive Modeling
7. Data Visualizing

위의 진한 부분에서 SQL은 강력한 툴이 된다.

Data에 대한 이해

1. 빈 값(0)과 Null의 차이 -> Survivalship Bias(생존자 편향의 오류)
   - 빈 값 = 0 = 정보량이 0이라는 말
   - Null 정보량이 아예 없다는 말
   - 0을 빠트리지 말자
   
   
2. 각 부분에 대한 평균이 크다고 해서 전체의 평균이 크지 않다 -> Simpson's Padadox(심슨의 역설)
   - 전체에 대한 이해 = 주요 변수, 주요 차원(한 단계 아래 차원의 변수)에 대한 이해가 없다면 결과를 왜곡
   - 수술 성공률만 보지말고, 무슨 수술인지도 봐야한다.
   
   
   

SQL(Structured Query Language) = 언어의 그룹이다.

BASE : Standard SQL  위에 4가지 종류가 있다.

1. PL/SQL(Procedural Language for SQL)
2. T-SQL(Transaction SQL)
3. Extended SQL/PSM(SQL/Persistent Stored Modules)
4. PL/pgSQL(Procedural Language/PostgreSQL)

DBMS(Database Mnangement System)

데이터를 저장하기 위한 [ 데이터 + 스크립트 + 저장장치 ]를 잘 다루기 위해 기능을 제공하는 시스템

SQL의 구조

1. DML(데이터 조작 어) : SELECT, INSERT ,UPDATE, DELETE ( S U I  D)
2. DDL(데이터 정의 어) : CREATE DROP TRUNCATE(메모리절약) ALTER(수정, TSQL용) RENAME(MySQL용) ( C D T A R )
3. DCL(데이터 제어 어) : GRANT REVOKE ( G R )
4. TCL(트랜젝선 제어 어) : COMMIT ROLLBACK - data의 퀄리티를 높힐 수 있다.

Transacation

data가 양방향에서 깨지지 않게, 한 묶음으로 한번에 실행하는 것

1. 이것을 활용하여 UPDATE 전/후의 비교를 통해 쿼리문을 확인할 수 있다.
2. data 백업이 어려운 상황에서수정을 해야할 때 활용할 수 있다.

연습 쿼리문

--DDL(CREATE)--
--계좌 1--
CREATE TABLE BANK_A (
이름 CHAR(20), -- 이름은 조금 크게 잡을 수 있다.
계좌 TEXT,     -- 20글자보다 이상 들어간다.
잔고 INT
)

--계좌 2--
CREATE TABLE BANK_B (
이름 CHAR(20),
계좌 TEXT,
잔고 INT
)

--DML--
--돈 미리 넣어놓기--***
INSERT BANK_A VALUES ('조재성', '1', '500000');
INSERT BANK_B VALUES ('김석영', '2', '0');

--계좌 확인--
SELECT * FROM BANK_A
SELECT * FROM BANK_B

--TRANSACTION으로 송금해보기--***
--UPDATE 테이블 SET 바꾸고 싶은 칼럼 = 가지고 계산 WHERE ***
--DELETE와 UPDATE시 WHERE 조건 꼭 달아줄 것***

-- 1. 먼저 한쪽에서 보낸 것만 해보고 ROLLBACK 해보기
BEGIN TRAN
UPDATE BANK_A SET 잔고 = 잔고 - 50000 WHERE 이름 = '조재성'

SELECT * FROM BANK_A
SELECT * FROM BANK_B

--여기 전까지 한꺼번에 선택해서 F5
ROLLBACK --TRAN이 맘에 안들어서 취소

-- 2. 한쪽에서 보내고, 한쪽은 받는 로직까지
BEGIN TRAN
UPDATE BANK_A SET 잔고 = 잔고 - 50000 WHERE 이름 = '조재성'
UPDATE BANK_B SET 잔고 = 잔고 + 50000 WHERE 이름 = '김석영'

SELECT * FROM BANK_A
SELECT * FROM BANK_B

--여기 전까지 한꺼번에 선택해서 F5
COMMIT   --TRAN이 맘에 든 것을 저장

--- SELECT FROM 구성
-- SELECT ALL | DISTINCT   칼럼명 | *
-- FROM  테이블명
-- WHERE
-- GROUP BY
-- HAVING
-- ORDER BY

--- 데이터 조회---
SELECT * FROM Person;
SELECT * FROM Drug;

--- 필터링 ---
SELECT * FROM Person WHERE sex = 1; --9307
SELECT * FROM Person WHERE sex = 0; --9263

--- 카운팅 ---
SELECT COUNT(*) FROM Person; -- 18570  -- 전체 row개수만

SELECT COUNT(*) FROM Person -- 9263, 9307 -- sex칼럼별로 <sex값 없이 row개수만>***
GROUP BY Sex;

SELECT Sex, COUNT(*) FROM Person -- 9263, 9307 -- sex칼럼별로 + <SEX칼럼표시> + <row개수>***
GROUP BY Sex;

--- GROUP BY with 집계함수---
SELECT * FROM Person GROUP BY Sex; -- GROUP BY 는 항상 집계함수가 포함되어있어야 된다. 안그러면 오류

SELECT COUNT(*) FROM Person GROUP BY Sex; --칼럼자리에 GROUP BY한 칼럼명을 입력하지 않으면, 해당 칼럼의 요소가 보이질 않음

--- 칼럼별 DISTINCT(유니크한 값) 보기 --- ****
SELECT DISTINCT personid FROM Drug; --- SELECT 다음에 DISTINCT 칼럼명

--- 상위 TOP 몇개만 보기 --- ***
SELECT TOP 3 * FROM Drug;
SELECT TOP 10 * FROM Person;

--- WHERE 조건에 칼럼명 BETWEEN AND 넣어보기---***
SELECT * FROM DRUG;
SELECT * FROM Drug WHERE duration BETWEEN 3 AND 5

SELECT * FROM Electrocardiogram;
SELECT * FROM Electrocardiogram WHERE QTc BETWEEN 400 AND 500;

--- WHERE 조건에 칼럼명 = OR , 칼럼명 = OR ---
SELECT TOP 15 * FROM Drug;
SELECT * FROM Drug WHERE atccode = 'N01BB02' OR atccode = 'N07AA02'; --- ***문자열에 쌍따옴표 x 그냥 따옴표

--- WHERE 조건에 칼럼명 IN ( '', '', '') 써보기 ---***
SELECT * FROM Drug WHERE atccode IN ( 'N01BB02', 'N07AA02');

--- WHERE 조건에 칼럼명 LIKE '% , [A-Z] 정규식' 사용하기 ---***
--- %는 앞뒤로 모든 문자가 줄지어 나타남
--- [A-Z]는 한글자에 대해서 매칭
SELECT * FROM Drug WHERE atccode LIKE 'J01%' --*** J01로 시작하는 모든 것
SELECT * FROM Drug WHERE atccode LIKE 'J01[A-Z][A-Z}10' ---*** J01[?][?]10 가운데 2개 아무거나

--- FROM > WHERE GROUP BY HAVING ORDER BY에서 ---

--- ORDER BY 칼럼명 DESC/ASC ---***
SELECT TOP 15 * FROM Drug;

SELECT * FROM Drug ORDER BY personid DESC;
SELECT * FROM Drug ORDER BY personid, drugdate ASC;

SELECT * FROM Drug WHERE duration > 90 
ORDER BY personid ASC,drugdate DESC     -- WHERE (g) (h) ORDER BY 순

--- 집계함수 후 AS 새로운 칼럼명 ---***
-- cast()해주고 계산하기
-- AVG, SUM, COUNT, MAX, MIN
SELECT AVG(duration) AS avg_duration FROM Drug -- 7 : 문자열로 입력되어있을 시, cast( as float)를 안해주면 소수점 다 짤려나온다.
SELECT AVG( cast(duration AS float)) as avg_duration FROM Drug  -- 7.48

SELECT COUNT(*) AS row_cnt FROM Drug;

SELECT drugdept FROM Drug GROUP BY drugdept; -- 그룹별로, 집계없이 해당 칼럼만 보면, 그 종류만 나온다
SELECT drugdept, COUNT(*) FROM Drug GROUP BY drugdept; -- 그룹별로 종류 + cnt
SELECT drugdept, COUNT(*) AS drugdept_cnt FROM Drug GROUP BY drugdept;

-- route의 그룹별, duration의 최대값 구하기
SELECT DISTINCT route FROM Drug;

SELECT route, MAX(duration) AS max_duration FROM Drug
GROUP BY route

설치

• 구글에서 sql exress 2017 검색
• MSSQL 설치 완료시 뜨는 SSMS( SQL Server Managemnet Studio) 설치
• 데이터(private) : https://github.com/is2js/SQL-study

단축키들

1. F5 : 선택쿼리 실행
2. F8 : 개체탐색기로 이동( 새로고침시 )
3. ALT+F1 : 선택칼럼 도움말(index 등 정보) = sp_help 칼럼명

MSSQL - SSMS로 csv파일 7개 연습하기

1. 연결된 나의 SQL server에서 새 데이터베이스 만들기 (csv파일만 가진 상태면, DB 먼저 새로 만들어놓기)

   
   

2. csv파일을 DB로 가져오기 위해선
   *DB 우클릭 > 테스트 > 데이터 가져오기 >
   - 데이터 원본 선택에서 txt, csv, tsv 등 은 플랫 파일 원본을 선택 >
   - 이 때, 파일 이름으로 찾을 때, txt파일만 볼 수 있으니, 우측하단에서 csv 선택해서 하나씩 가져오기 >
   - 미리보기로 데이터 확인하기
   - 대상선택에서는 Microsoft OLE DB Provider for SQL Server 선택 ! MSSQL 깐 순간
   대상이 내 노트북이 SQL server가 된 것임. 이 때, window 인증을 사용해서 sql도 자동인증 되게 하자.
   sql인증을 사용하면 아뒤 비번 따로 사용해서 강력하게 인증함.
   *
   
   
   
   
   
   

3. DB를 선택한 상태에서, 쿼리문으로 한번에 table(BULK INSERT 테이블 from '경로') 다 올리기 ( 쿼리 실행 F5 )
   - DB를 클릭한 상태 > 우클릭 새쿼리 or Ctrl + N 으로 쿼리창 띄우기
   - 미리 빈 테이블(csv와 칼럼명 + 데이터타입 기)을 만들어놔야한다.
   - 만든 뒤, 개체탐색기(F8)에서 새로고침(F5)를 몇번 해야 생긴다.
   - 생긴 table에 BULK INSERT 테이블 '경로' WITH (firstrow = 2, format='CSV')를 이용한다.

   bulk insert Laboratory from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_Laboratory.csv'
   with (firstrow=2, KEEPNULLS, format='CSV')

   
   
   
   

4. 이제부터는 귀찮아서, DB -> 테스트 -> 데이터 가져오기로 table들을 다 만들었다.

인터넷

최소 2대의 컴퓨터가 필요하다.

• Client : 요청하는 사용자(갑)
• Server : 응답하는 서비스 제공자(을)
  - 만약 요청하는 분야가 Web이라면 Web client / Web server라고 할 수 있다.

MySQL을 설치하면, Server와 Client 2개를 모두 설치하는데, 아래와 같다.

• database client : mysql -uroot -p 명령어 기반의 client인  MySQL monitor
  
• database server : 데이터가 저장되고 응답하는 쪽
  
  

MySQL Client - 2번째 database clinet WorkBench

• MySQL monitor : 서버와 함께 설치됨. 명령어기반이라 어디서든(서버컴퓨터에서도) 제어가능
• MySQL Workbench : Gui기반의 client, MySQL 공식제공 client

설치 : https://www.mysql.com/products/workbench/

1. database server에 연결하기
• 참고) MySQL monitor에서 -h   local host or 127.0.0.1은 생략된 것이다.
  c:\Bitnami\wampstack-7.1.25-0\mysql\bin>mysql -uroot -p -hlocalhost
  c:\Bitnami\wampstack-7.1.25-0\mysql\bin>mysql -uroot -p -h127.0.0.1
  
  
• Workbench에서는 북마크 방식으로 서버를 추가할 수 있다. 홈화면에서 +를 누른뒤
  - 서버이름,
  - connection method : Standard TCP/IP 선택
  - hostname : -h에 해당하는 서버컴퓨터 주소 / port는 보통 3306
    * defaul 127.0.0.1 인데, localhost로 입력시 test connection할때 에러가 날 수 있다. ip로 변경하면 된다.
    * port 3306은 처음 APM7으로 설치시 할당되었고, bitnami로 2번째 설치시는 3307로 할당되었으니, 나의 경우 3307로 연결함.
  - username : 우리가 만들었던 username 기본 root
  - default schema : 비워두자.
  
  
  
  
  
  
• 연결이 완료되고 더블클릭해보면, MySQL monitor(명령어)로 작성했던 DATABASE명이 보일 것이다.
  
  
  
  
2. 데이터베이스 선택 및 query문 날려보기
   - 해당 db를 더블클릭하고, Query라는 스크립트에 작성후 [번개]표시를 누르면 작동된다.
   
   
   
   
   
3. schema(db)생성해보기
   - 가장 첫번째 아이콘 클릭
   
   
   - schema(db)이름 선택 및 Apply를 통해 쿼리문 확인
   
   
   
   - 생성확인하기
   
   
   
   
   
   
4. table 만들기
   -  2번째 아이콘을 선택하면 된다.
   
   
   
   
   - 각종 내용을 채워준다.
   - TEXT()는 안되니 TEXT로 바꿔주자.
   
   
   
   
   
   - 마우스를 데면 오른쪽끝에 작은 table이 뜨는데 클릭하면, table의 내용물을 볼 수 있다.
   
   
   
   - 표를 클릭해서 데이터를 입력하면 되지만, NOW()는 입력안되는 것 같다. sql문을 보면 'NOW()'로 문자열로 입혀지기 때문인 것 같음.
   
   

2개의 독립된 테이블을 JOIN을 통해서 하나의 테이블 형태로 보는 방법에 대해 알아보자.

1. 결합고리(joint)는 topic테이블의 author_id와 author테이블의 id이므로
   명령문은 'topic 테이블의 모든 데이터를 출력하는데, author_id의 값과 author테이블의 id값이 같은 것 author테이블의 값을 붙혀'이다.
   
   mysql> SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id;
   
   
   
2. 이제 author_id 칼럼과 id칼럼을 제외하고 특정칼럼들만 가져와서 안보이게 하자.
   - 2개의 테이블 모두 같은명의 id칼럼을 가지고 있으므로, ambiguous라는 에러가 뜬다.

mysql> SELECT id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
ERROR 1052 (23000): Column 'id' in field list is ambiguous


이럴때는 칼럼명앞에 table명.칼럼명을 적어주면 된다.

mysql> SELECT topic.id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;


3. topic 테이블의 id칼럼명을 다른 것으로 변경시켜 보여줄 수 도 있다.
   mysql> SELECT topic.id AS topic_id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
   
   
   

기존의 테이블에서 중복을 제거했다는 것은 매우 중요한 일이다. -> 하나만 바꿔주면 전체가 바뀐다.





topic테이블은 글에 대한 내용이라면, comment 테이블이 존재할 수 있다. 여기에서도 author와 profile이 필요할 것이다.
comment 테이블에 author_id칼럼을 만들고 역시 author 테이블에서 가져오면 될 것이다.

관계형 데이터베이스

위의 table에서 데이터가 중복되는 부분이 있다. -> 개선의 여지가 있다.
만약, 중복된 데이터의 수정이 필요한 상황이라면 현재의 상태에서는 모든 데이터를 수정해야할 것이다.

1. 중복되는 데이터를 가진 author와 profile을 새로운 author라는 table을 새로 만들어보자.
   
   
   
2. 기존의 table에서 author와 profile을 빼고, author_id라는 칼럼으로 대체 + author table의 id값을 대체하자.
   
   
   
3. 이제 author테이블 값만 바꾸면 -> topic 테이블의 모든 author_id는 자동으로 변경된 내용이 관계될 것이다.
   유지보수가 훨씬 편해진다.
   
   
   
   

중복을 제거하여 별도의 테이블을 만들고 참조값만 적어주면, 유지/보수의 장점이 생긴다.

하지만, 직관적이지 못한, 별도의 테이블도 열어봐야하는 단점도 생긴다.

그래서 필요한 것이  별도의 테이블로 중복을 방지하여, 유지보수를 편하게 + 볼때는, 하나의 table로 합쳐진 형태의 직관적으로 만들어야하는데, 이것을 관계형 데이터베이스이고 저장할때는 별도로 + 보여줄때는 합쳐서를 실현해보자.

기존 테이블 2개로 분리하기

1. 기존의 테이블의 이름 바꿔서 백업하기
   RENAME TABLE topic TO topic_backup;
   
   
   
2. author+profile을 author_id로 참조하는 형태의 새로운 테이블 만들기
   mysql> CREATE TABLE topic(
        -> id INT(11) NOT NULL AUTO_INCREMENT,
        -> title VARCHAR(30) NOT NULL,
        -> description TEXT NULL,
        -> created DATETIME NOT NULL,
        -> author_id INT(11) NULL,
        -> PRIMARY KEY(id)
        -> );
   
   
   
3. author 테이블 새로 만들기
   mysql> CREATE TABLE author(
        -> id INT(11) NOT NULL AUTO_INCREMENT,
        -> name VARCHAR(20) NOT NULL,
        -> profile VARCHAR(200) NULL,
        -> PRIMARY KEY (id)
        -> );
   
   
   
4. author 테이블을 기존 백업한 테이블을 보며 데이터 입력하기
   INSERT INTO author ( ) VALUES( );
   - *id값은 칼럼괄호에 안넣으면 자동으로 1씩 증가하지만, 여기에선 직접 증가한다.
   mysql> INSERT INTO author (id, name, profile) VALUES(1, 'egoing', 'developer');
   
   
   
5. 새로운 topic 테이블에 author_id값을 입력하면서 새로 작성하자.
   mysql> INSERT INTO topic (id, title, description, created, author_id)
        -> VALUES(1, 'My SQL', 'MySQL is ...', '2018-02-09 17:49:59', 1);
   
   
   
6. 나머지 데이터도 이런식으로 입력하다가, 새로운 author가 등장하면
   1) author 테이블에서 추가 -> 2) topic테이블에서 author_id 입력을 해주자.
   
   mysql> INSERT INTO topic (id, title, description, created, author_id)
        -> VALUES(2, 'Oracle', 'Oracle is ...', '2019-01-04 10:48:24', 1);
   

mysql> INSERT INTO author (id, name, profile) VALUES(2, 'duru', 'data administrator');



mysql> INSERT INTO topic (id, title, description, created, author_id)
     -> VALUES(3, 'SQL server', 'SQL server is ...', '2019-01-04 10:49:23', 2);
mysql> INSERT INTO author (id, name, profile) VALUES(3, 'taeho', 'data scientist, developer');


mysql> INSERT INTO topic (id, title, description, created, author_id)
     -> VALUES(5, 'MongoDB', 'MongoDB is ...', '2019-01-04 10:52:30', 1);

Update(수정)

• UPDATE table명  SET 칼럼1=값1, 칼럼2=값2 WHERE id=로 필터링
  만약, WHERE 조건을 달지 않으면 , 모든 row가 그렇게 바뀐다.

• 위 table에서 2번째 데이터에 내용을 수정해보자.
  UPDATE topic SET description = 'Oracle is ...', title = 'Oracle' WHERE id=2;
  
  
  
  
  
• 
  

DELETE(삭제)

UPDATE와 마찬가지로, WHERE 조건을 달지 않으면 전체 데이터가 삭제되니 조심해야한다.

• 5번째 데이터를 삭제해보자.
  DELETE FROM topic WHERE id = 5;
  
  
  

CRUD

• Create(데이터 추가, row 추가)와 Read(가져오기 +  필터링, 정렬, 데이터수 제한)는 중요하다
• Update(수정)와 Delete는 없을 수 도 있다.

Create & READ: INSERT문과 SELECT문

참고 사이트 : https://www.w3schools.com/sql/sql_insert.asp

우리가 만들어야할 table예시

• table구조 확인하는 명령어 : DESC topic;
  
  
  
• AUTO_INCREMENT되는 id칼럼을 입력해주지 않으면, 값은 자동으로 +1 될 것이다.
• 먼저, row를 삽입하는 것이므로,
  INSERT INTO topic () VALUES(); 형태를 작성하고, 앞()에는 칼럼명, 뒤 ()에는 value값을 입력할 준비를 한다.
  
  
  
• 칼럼-value를 하나씩 채워나간다.
  
  
  
  
• create라는 날짜시간 칼럼에서는 직접 값을 입력해도 되지만, NOW()라는 함수를 통해 현재 날짜가 자동입력되도록 한다.
  
  
  
• table의 내용물(모든 칼럼)을 확인하는 명령어 : SELECT * FROM topic;
  
  
  나머지도 다 입력하여 SELECT * FROM table;로 확인한다.
  
  
  
  
  

READ

• 일부 칼럼만 보기 : * 대신 컬렴명을 적어준다.
  mysql> SELECT id, title, created, author FROM topic;
  
  
  
  
• mysql select syntax를 구글에서 검색하여 나온 공식문서의 syntax설명을 보자.
  
  공식 문서에서 SELECT문을 보면,
  [ ]는 생략가능하다는 뜻, select_expr = projection = 표현되어야할 칼럼들을 의미한다.
  
  
• 만약, SELECT 다음에 FROM 테이블명이 없다면?
  해당 projection을 컬럼으로 하는 어떤 데이터가 보여진다.
  
  
  
  
• FROM table명 다음에 올 수 있는 WHERE 칼럼명=value; -> 엑셀의 칼럼 필터링같이, 특정칼럼 = 값의 형태로 author = 'egoing'인 것만 골라 올 수 있다.
  mysql> SELECT id, title, created, author FROM topic WHERE author='egoing';
  
  
  
  
• FROM -> WHERE 다음에 올 수 있는 ORDER BY = 칼럼명 ASC/DESC ; 는 오름/내림차순 정렬을 할 수 있다.
  SELECT id, title, created, author FROM topic WHERE author='egoing' ORDER BY id DESC;
  
  
  
• row의 개수를 제한할 때는 LIMIT row수;를 달아준다.
  mysql> SELECT id, title, created, author FROM topic WHERE author='egoing' ORDER BY id DESC LIMIT 2;