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

- SQL문법 순서

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

 

-SQL실행 순서

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

---

--[회원테이블]의 특정 컬럼명 조회 및 임시로 컬럼명 변경하기 (AS)

SELECT [회원번호]
,[이름] AS [성명]
,[가입일자]
,[나이]
FROM [회원테이블]

---

-- 모든 행을 조건 없이, [나이] 30으로 수정하기

UPDATE [회원테이블]
SET [나이] = 30

---

-- [회원번호]가 'A10001'인 [나이] 34로 변경하기

UPDATE [회원테이블]
SET [나이] = 34
WHERE [회원번호] = 'A10001'

---

-- [회원테이블] 모든 행 데이터 삭제

DELETE
 FROM [회원테이블]

---

-- [회원테이블] 특정 조건 데이터 삭제

DELETE
FROM [회원테이블]
WHERE [회원번호] ='A10001'

---

-- DCL (Date Control Language)
-- MWS라는 유저에게 [회원테이블] 권한 부여

GRANT SELECT, INSERT, UPDATE, DELETE ON [회원테이블] TO MWS WITH GRANT OPTION

-- MWS라는 유저에게 [회원테이블] 권한 제거

REVOKE SELECT, INSERT, UPDATE, DELETE ON [회원테이블] TO MWS CASCADE

---

--  TCL (Transaction Control Language)
-- BEGIN TRAN -> DELETE -> COMMIT
-- COMMIT 명령어 실행시 데이터가 복구되지 않음 

USE EDU
BEGIN TRAN /*TCL 시작*/
DELETE FROM [회원테이블] /*회원테이블의 모든 행 데이터 삭제*/
COMMIT /*모든 행 데이터 삭제 실행*/

---

--ROLLBACK : 복구
-- EDU DB 사용 -> TCL시작 -> 조회 -> 모든 행 삭제 -> 조회 -> 삭제 취소(ROLLBACK) -> 조회

USE EDU
BEGIN TRAN
SELECT * FROM [회원테이블]
DELETE FROM [회원테이블]
SELECT * FROM [회원테이블]
ROLLBACK
SELECT * FROM [회원테이블]

---

-- SAVE POINT : 임시저장
-- 트랜잭션을 분할하여 저장시(SAVE TRAN S1) 지정취소(ROLLBACK TRAN)가 가능하다.

USE EDU
BEGIN TRAN
SAVE TRAN S1;
INSERT INTO [회원테이블] VALUES ('A10001','모원서','남',33,100000,'2020-01-01',1);
SAVE TRAN S2;
UPDATE [회원테이블] SET [나이] = 34 WHERE [회원번호] = 'A10001'
SAVE TRAN S3;
DELETE FROM [회원테이블] WHERE [회원번호] = 'A10003'
SELECT * FROM [회원테이블]
ROLLBACK TRAN S3;
SELECT * FROM [회원테이블]
ROLLBACK TRAN S1;
SELECT * FROM [회원테이블]

---

-- FROM : [Member] 테이블 선택

-- WHERE : gender 컬럼값이 'man'으로만 필터링
-- Group BY : addr 컬럼별로 회원(mem_no) 수 집계

SELECT addr
,COUNT(mem_no) AS [회원수집계]
FROM [Member]
WHERE gender = 'man'
GROUP
BY addr

---

-- FROM : [Member] 테이블 선택
-- WHERE : gender 컬럼값이 'man'으로만 필터링
-- GROUPBY : addr 컬럼별로 회원(mem_no)수 집계
-- HAVING : addr 컬럼별로 회원(mem_no) 수가 50이상만 필터링 

SELECT addr
,COUNT(mem_no) AS [회원수집계]
FROM [Member]
WHERE gender = 'man'
GROUP
BY addr
HAVING COUNT(mem_no) >= 50

---

-- INNER JOIN : 두 테이블 간의 공통 값이 매칭되는 데이터만 조회

USE EDU
SELECT *
FROM [Member] AS A
INNER
JOIN [Order] AS B
ON A.mem_no = B.mem_no

---

-- OUTER JOIN : 매칭 되지 않는 데이터도 조회 (3가지 종류 - Left, Right, Full)
-- LEFT JOIN : 왼쪽 테이블 기준 데이터 조회
-- [Member] 및 [Order] 테이블 공통값(mem_no) 결합 + 매칭 안되는 [Member] 데이터 조회

SELECT *
FROM [Member] A
LEFT
JOIN [Order] B
ON A.mem_no = B.mem_no

---

-- RIGHT JOIN : 오른쪽 테이블 기준 데이터 조회

SELECT *
FROM [Member] A
RIGHT
JOIN [Order] B
ON A.mem_no = B.mem_no

---

-- FULL JOIN : 양쪽 테이블 기준 테이블 조회
-- [Member] 및 [Order] 테이블 공통 값(mem_no) 결합 + 매칭 안되는 [Member] 및 [Order] 데이터 조회

SELECT * 
FROM [Member] A
FULL
JOIN [Order] B
ON A.mem_no = B.mem_no

---

Other Join (Cross Join , Self Join)
-- Cross join은 두 테이블의 "행"을 결합
-- Self join은 한 테이블의 행과 다른 행들을 결합

-- Cross Join : 두 테이블의 행을 결합한 데이터 조회
--[Member] 행 x [Order] 행

SELECT *
FROM [Member] A
CROSS
JOIN [Order] B
WHERE A.mem_no = '1000001'

--mem_no가 [Member]및 [Order]에도 있으므로, A.mem_no 또는 B.mem_no로 명시해야함

---

--Self Join : 한 테이블(Member)을 두 테이블(A,B)로 하여 행을 결합한 데이터 조회 : 명령어 FROM 사용
--[Member] 행 X [Member] 행

SELECT *
FROM [Member] A, [Member] B
WHERE A.mem_no = '1000001'

--mem_no가 [Member]의 A,B에 있으므로, A.mem_no 또는 B.mem_no로 명시해야함

---

▼서브 쿼리(Sub Query)

 

-- 서브쿼리는 하나의 SQL명령어에 포함된 SELECT 명령어
-- 서브쿼리는 주로 SELECT절, FROM절, WHERE절 뒤에 위치하여 사용됨

--SELECT절

SELECT *
,(SELECT gender 
FROM [Member] B
WHERE A.mem_no = B.mem_no) AS gender
FROM [Order] A

--SELECT절에 사용되는 서브쿼리는 하나의 열처럼 사용되며, 이를 스칼라 서브쿼리라고 한다. 
--스칼라는 한번에 하나의 값만 보유할 수 있는 원자량이다.
-- JOIN의 대체 표현식이지만, 데이터의 양이 많을수록 속도가 느려서 거의 사용되지는 않음

---

--FROM절

SELECT *
FROM (
SELECT mem_no
,SUM(sales_amt) AS tot_amt
FROM [Order]
GROUP
BY mem_no
)A

-- 회원(mem_no)별로 주문금액(sales_amt) 합이 집계된 데이터가 테이블로 실행된다.
-- FROM절에 사용되는 서브쿼리는 테이블처럼 사용되며, "인라인 뷰"라고도 한다.
-- 테이블처럼 사용되기 때문에 열 이름 및 테이블 명을 명시해주어야함
-- AS tot_amt는 집계함수에 대한 열 이름이며
-- A는 서브쿼리에 대한 테이블명이다.

-- FROM절에 사용되는 서브쿼리
-- [Member] 및 [Order] 테이블 공통 값(mem_no) 결합
-- 1:1 관계

SELECT *
FROM (
SELECT mem_no
, SUM(sales_amt) AS tot_amt
FROM [Order]
GROUP 
BY mem_no
)A
LEFT
JOIN [Member] B
ON A.mem_no = b.mem_no

-- 서브쿼리 + LEFT JOIN 사용 => FROM절에 사용된 서브쿼리와 Member 테이블 간의 공통값인 mem_no가 "매칭되는 값만" 조회된다.

---

-- WHERE절 (=일반 서브쿼리, 두가지 종류 - 단일 및 다중)
--WHERE절에 사용되는 서브쿼리(단일 행 서브쿼리)
--단일 행 : 서브쿼리 결과가 단일 행

SELECT *
FROM [Order]
WHERE mem_no = (SELECT mem_no FROM [Member] WHERE mem_no = '1000005')

--단일 행 확인

SELECT mem_no FROM [Member] WHERE mem_no = '1000005'

 

---

--WHERE절에 사용되는 서브쿼리 (다중 행 서브쿼리)

-- 다중행 서브쿼리에는 sql연산자(여기에서는 IN)가 사용된다는 사실 알아두기
-- 다중 행 : 서브쿼리 결과가 여러 행

SELECT *
FROM [Order]
WHERE mem_no IN (SELECT mem_no FROM [Member] WHERE gender = 'man')

--여러 행 확인

SELECT mem_no FROM [Member] WHERE gender = 'man'