[1]SQL로 분석하고 Tableau로 시각화하자

-      Ctrl + N = 새 쿼리(N): sql명령어를 작성하는 창 생성

-      F5 = 실행(X): 데이터베이스 생성

-      Ctrl + S = 쿼리 저장

-      USE DB이름: 데이터베이스 사용

 

-      크게 4가지로 분류되는 명령어

 

 1) DDL (Data Definition Language): 테이블 생성, 변경, 삭제

CREATE, ALTHER, RENAME, TRUNCATE, DROP

 

 2) DML (Data Manipulation Language): 데이터 삽입, 조회, 수정, 삭제

INSERT, SELECT, UPDATE, DELETE

 

3) DCL (Data Control Langauge): 특정 사용자에게 데이터 접근 권한 부여, 제거

GRANT, REVOKE

 

4) TCL (Transaction Control Language): DML명령어 실행, 취소, 임시저장

COMMIT, ROLLBACK, SAVEPOINT

 

-      각 열(컬럼) 마다 형식이 정해져있고, 반드시 1가지 데이터 형식만 저장됨

-      데이터 형식 – 문자/숫자/날짜/논리형으로 구분

-      숫자형: BIT(논리형), TINYINT(양의정수/0~255), SMALLINT(정수), INT(정수), BIGINT(정수), DECIMAL(p전체 자릿수, s소수점자릿수), NUMERIC(앞과 같은 형식인데 근사값을 보여줌), FLOAT(p), REAL(=FLOAT(24)), MONEY, SAMLLMONEY

-      문자형: CHAR(n) 고정길이, NCHAR(유니코드 고정길이), VARCHAR(n) 가변길이, NVARCHAR(n) 유니코드 가변길이, BINARY(n) 이진데이터, VARBINARY(n)

-      날짜형: DATETIME, DATETIME2, DATE, TIME

-      명령어 실행 후 바(-) 2개를 입력하여 명령어 비활성화하기

-      바(-) 2개를 앞에 쓸 시 뒤에있는 모든 내용이 주석처리됨

-      블록 주석은 (/**/)

-      열 추가시 반드시 데이터형식 지정해주기 ([열 이름] 뒤에 데이터 형식 입력)

-      이미 개체가 있다고 에러 뜨는 경우 -> 해당 명령어만 드래그하여 실행하기

-      데이터 형식이 문자 혹은 날짜형일 때 작은따옴표(‘) 붙이기

-      열 구분은 쉼표(,)로

-      PRIMARY KEY 제약조건 위반: 중복키 입력 불가능

-      NOT NULL 제약조건 위반: NULL이 허용되지 않은 열에 NULL입력시 오류

-      “AS”는 임시로 별칭을 생성한다. EX. [이름] AS [성명]

-      조회시(SELECT) 열 순서를 변경하여 조회가 가능함

-      Delete / Truncate / Drop 명령어 차이

-  Delete는 데이터만 삭제, Truncate는 데이터+테이블 공간만 삭제, Drop은 테이블 전체가 삭제됨

-      SQL 기본구조는 SQL 서버를 SSMS로 연결하는 것

-      Having은 Groupby 된 테이블을 특정 조건으로 필터하는 명령어이다.

 

-      SQL 명령어 특징

 

-  대소문자 구분이 없다

-  띄어쓰기, 들여쓰기 간격 제한이 없다

-  날짜 및 문자형에는 작은따옴표가 필요하다.

 

-      SQL 문법 순서

 

 SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

 

-      SQL 실행 순서

 

 FROM -> WHERE -> GROUPBY -> HAVING -> SELECT -> ORDER BY

 

-      ERM (Entity-Relationship Modelling): 회원=개체 / 주문=관계 / 상품=개체 <-이 관계를 토대로 회원 및 상품 테이블을 구성할 수 있으며, 관계 설계를 통해 주문테이블을 구성할 수 있다.

 

-      서브쿼리***

1)  서브쿼리 – SELECT

 

SELECT *

, (SELECT 컬럼명 FROM [테이블2] B

           WHERE A.컬럼명 = B.컬럼명) AS 컬럼명

From [테이블1] A     

 

2)  서브쿼리 – FROM

 

SELECT *

FROM (SELECT 컬럼명, SUM(컬럼명) AS 컬럼명

           FROM [테이블1] GROUP BY 컬럼명) A

LEFT JOIN [테이블2] ON A.컬럼명 = B.컬럼명

 

3)  서브쿼리 – WHERE(단일)

 

SELECT *

FROM [테이블2]

WHERE 컬럼명 = (SELECT 컬럼명 Ff

 

4) 서브쿼리 – WHERE(다중)

 

SELECT *

FROM [테이블2]

WHERE 컬럼명 IN (SELECT 컬럼명 FROM [테이블1] WHERE 조건문)

 

-      자주 쓰이는 SQL문법

 

-  LIKE: 비교 문자열

•  (‘모%’ : 모로 시작하는 / ‘%모%’ : 모를 포함하는 / ‘%모’ : 모로 끝나는)
• Q. 컬럼 addr에서 'ae'를 포함하는 값만 조회하여라. => WHERE addr LIKE '%ae%'

-  NOT LIKE ‘비교 문자열’ : 비교 문자열 형태가 아님

-  <>: 같지 않음

-      자주 쓰이는 SQL함수 (모든 함수 사용시 앞에 SELECT 입력)

 

-  LEFT(‘문자열’,n) : 왼쪽부터 지정한 수(n)만큼 문자 반환

-  RIGHT(‘문자열’,n) : 오른쪽부터 지정한 수(n)만큼 문자 반환

-  LEN(‘문자열’): 문자수 반환, 오른쪽 공백 제외

-  REPLACE(‘문자열’,a,b)

-  CONCAT(‘문자열’,’문자열’): 둘 이상의 문자열 연결

-  LTRIM: 왼쪽 공백 제외 문자 반환 (RTRIM 오른쪽, TRIM 양쪽)

-  SUBSTRING(‘문자열’,m,n): 문자열의 m위치에서 n개 길이만큼 문자 반환

-  CHARINDEX(a,’문자열’,n): 문자열 중 a가 있으면 위치를 반환, 없을시 0 반환, 옵션(n)은 ‘검색시작 위치를 지정할 때’

-  SPACE(n): n만큼 공백 추가

-  STUFF(‘문자열’,m,n,a): 문자열의 m위치에서 n개 길이만큼 삭제후, a로 문자 변환

-  GET-DATE():현재 날짜 및 시간 변환

-  YEAR/MONTH/DAY

-  DATEPART (기준, 날짜열) ex. DATEPART(YEAR,’2020-12-31 10:20:30

-  1~7 순서대로 일~토요일

-  DATEADD (기준, n, 날짜열): 지정한 기준에서 n숫자만큼 더한 데이터 반환

-  DATE-DIFF (기준, 날짜열a, 날짜열b): 지정한 기준에서 날짜열 a,b차이 반환

 

-      행 변환 함수

 

-  CONVERT (VARCHAR, ‘날짜열’, 기준): 지정 기준으로 날짜->문자열로 데이터 형식을 변환시킨다.

-  위에서 VARCHAR안에 VARCHAR(n)을 넣을시, n숫자만큼 형식 변환

-  CAST(‘특정열’ AS 기준): 지정 기준으로 특정 열, 데이터 형식을 반환

-  EX. CAST(‘100’ AS INT)

 

-      일반 함수

 

-  ISNULL

-  NULLIF (특정 열a, 특정 열b): 특정 열 a,b가 같은 경우 NULL, 다를경우 특정열a 변환

-  CASE WHEN[조건1] THEN [지정 값1]       WHEN [조건2] THEN [지정 값2]        END

: 순차적으로 지정값이 변환되는 함수이며, 지정값은 한가지 형식만 가능

SELECT *

       ,CASE WHEN ageband BETWEEN 20 AND 30 THEN '2030세대'

               WHEN ageband BETWEEN 40 AND 50 THEN '4050세대'

               ELSE 'OTHER세대' END AS ageband_seg

              FROM [Member]

-      복수행 함수

-      집계함수: COUNT, SUM, AVG, MAX, MIN, STDEV, VAR

-      그룹함수: WITH ROLLUP, WITH CUBE, GROUPING SET, GROUPING

 

SELECT COUNT(order_no) AS 주문수

       ,SUM(sales_amt) AS 주문금액

       ,AVG(sales_amt) AS 평균주문금액

       ,MAX(order_date) AS 최근구매일자

       ,MIN(order_date) AS 최초구매일자

       ,STDEV(sales_amt) AS 주문금액_표준편차

       ,VAR(sales_amt) AS 주문금액_분산

       FROM [Order]

SELECT mem_no

       ,COUNT(order_no) AS 주문수

       ,SUM(sales_amt) AS 주문금액

       ,AVG(sales_amt) AS 평균주문금액

       ,MAX(order_date) AS 최근구매일자

       ,MIN(order_date) AS 최초구매일자

       ,STDEV(sales_amt) AS 주문금액_표준편차

       ,VAR(sales_amt) AS 주문금액_분산

       FROM [Order]

      GROUP

      BY mem_no