쿼리 시작부터 끝까지~ MySql



1. 쿼리문이란
일반적으로 칭하는 데이터베이스(Database)에 data를 입력(insert),수정(update),삭제(delete),조회(select) 할 수 있는 데이터베이스 종속적인 언어이다.
1.1 종류
DDL : 데이터 정의 언어 
- CREATE : 정의, ALTER: 수정, DROP : 삭제, TRUNCATE : DROP 후 CREATE
DML : 데이터 조작 언어
- SELECT, INSERT, DELETE, UPDATE, 기타 LOCK, EXPLAIN, CALL 등도 DML에 포함
- JOIN은?
DCL : 데이터 제어 언어, 데이터의 보안, 무결성, 회복, 병행 수행제어 등을 정의하는데 사용
- COMMIT, ROLLBACK,GRANT, REVOKE
2. 데이터베이스 종류
링크 참고

3. 관계형 데이터베이스의 테이블 개념
Table은 수학시간에 배운 집합과 유사한 개념.
-  Table의 컬럼들 또한  집합(도메인 집합?)으로 이해하자.  도메인 집합에서 정의하려는 테이블에 종속적인 요소들을 모아 하나의 테이블 집합을 정의한다.
Member테이블에 주민번호, 주소, 전화번호등 각각의 도메인 집합들이 존재하며 주민번호에 종속적인 주소, 전화번호만 뽑아 하나의 Member요소가 만들어진다. 

Table이 될 수 있는것은 세상의 모든 분류들 or 객체화 할 수 있는 모든것들 or 프로그래밍에서 클래스화 할 수 있는 것들.
집합에 요소들이 존재하듯 테이블에는 행이 존재.
각행을 구분 할 수 있는 값들을 기본키라 칭함.
다른테이블의 기본키가 현재 테이블에 존재할 때 이를 외래키라 칭함.
* 기본키와 외래키는 두 테이블의 관계가 된다.

- A테이블과 B테이블이 기본키와 외래키가 존재하면  A와 B는 Master(1):Slave(0 or N) 관계이다.
- A와B가 둘다 돌일한 기본키를 가지면 1: 0 or 1관계이다.
이렇게 테이블들이 관계로 연결되어 있다고 해서 관계형 데이터베이스라 칭한다.

참고로) 기본키는 Unique Index, 왜래키는 근냥 Index가 생성됨

4. 테이블 정규화
중복된 데이타 저장을 최소화 하기위한 과정
제3정규화까지가 가장 보편화

5. 쿼리 심화
대부분의 시스템에서 일반적으로 사용되는 다음의 테이블들이 존재한다고 가정하자.
테이블에서 _class로 종료하는 필드들은  CodeMaster에 저장되는 분류를 의미.
- 이런식의 룰을 정하고 항상 테이블 컬럼명을 정의하는 습관을 가지자.
- 필자의 경우 테이블 컬럼명은 영단어_영단어(언드바)로 연결한 표현이 가장 직관적이라 판단.

CodeMaster : 모든 분류코드들 저장용
- 컬럼들 : id, up_id,value, title, order_seq
- 키 : id

User : 회원정보
 - 컬럼 : user_id, name, identity_number,user_class , right_class,address, insert_date
 - 키: user_id

UserJoinLeave : 회원의 가입, 탈퇴 정보
 - 컬럼 : user_id, event_time, join_class, comment

UserLogging : 회원의 시스템 로그인/아웃 로그 
 - 컬럼 : user_id, log_seq, log_class, comment, insert_date
 - 기본키 : user_id, log_seq
 - 외래키 : user_id--> Member 와 MemberLog는 member_id로 1:N 관계를 가진다.

5.1 데이타 입력
5.1.1 CodeMaster테이블 생성 및 데이타 넣기
INSERT INTO CodeMaster VALUES(...) 구문을 사용하여 1건을 넣을 수도 있지만 아래와 같이 UNION ALL을 이용하여 여러건을 넣을 수도 있다.

-- drop table if exists CodeMaster;
CREATE TABLE CodeMaster(id INT, up_id INT, value VARCHAR(50), title VARCHAR(100), order_seq INTPRIMARY KEY(id)) ;
,

INSERT INTO CodeMaster(id, up_id, value, title, order_seq)
SELECT 10, NULL,'user_class', '회원구분', 0
UNION ALL
SELECT 11, 10, '1', '일반회원', 0
UNION ALL
SELECT 12, 10, '2', '기업회원', 1

UNION ALL
SELECT 20, NULL,'right_class', '권한구분', 0
UNION ALL
SELECT 21, 20, '1', '관리자', 0
UNION ALL
SELECT 22, 20, '2', '운영자', 1
;

-- 입력한 CodeMaster 조회 해보기
select * from CodeMaster

5.1.2 User테이블 생성 및 데이타 넣기
CREATE TABLE User(user_id INT, name VARCHAR(50), identity_number  VARCHAR(50),user_class VARCHAR(10), right_class  VARCHAR(10),address  VARCHAR(100), insert_date DATETIME
, PRIMARY KEY (user_id)) ;

INSERT INTO User(
user_id, name, identity_number,user_class , right_class,address, insert_date)
SELECT 1, '홍길동','1111', '1', '1','대한민국 1번지',now()
UNION ALL
SELECT 2, '강길동','2222', '2', '1','대한민국 2번지',now()
UNION ALL
SELECT 3, '정길동','3333', '1', '1','대한민국 3번지',now()
UNION ALL|
SELECT 4, '이길동','4444', '1', '1','대한민국 4번지',now()
UNION ALL
SELECT 5, '고길동','5555', '2', '2','대한민국 5번지',now()
;
select * from User


5.1.3 UserJoinLeave테이블 생성 및 데이타 넣기
-- 먼저 CodeMaster에 가입, 탈퇴 구분을 먼저 입력
INSERT INTO CodeMaster(id, up_id, value, title, order_seq)
SELECT 30, NULL,'user_class', '회원가입 상태구분', 0
UNION ALL
SELECT 31, 30, '1', '가입', 0
UNION ALL
SELECT 32, 30, '2', '탈퇴', 1
;

CREATE TABLE UserJoinLeave(user_id INT, join_class  VARCHAR(10)
insert_date DateTime, invitor INT,comment VARCHAR(50),PRIMARY KEY (user_id, join_class)) ;
-- invitor  : 추천인 ID
-- user_id, join_class 가 합쳐져서 키(복합키)

INSERT INTO UserJoinLeave(
user_id, join_class, insert_date, invitor , comment)
SELECT 1, '1', Now(),NULL,''
UNION ALL
SELECT 2, '1', Now(),1,''
UNION ALL
SELECT 3, '1', Now(),2,'' -- 추천인 2번
UNION ALL
SELECT 4, '1', Now(),2,''
UNION ALL
SELECT 5, '1', Now(),1,''
UNION ALL
SELECT 5, '2', Now(), NULL,'맘에 안듬!!!' -- 5번의 경우 탈퇴를 한 상태
5.2 쿼리문을 익히자!!!
SELECT문은 테이블에서 집합(여러행)을 조회하는 문장으로 JOIN문과 함께 사용하면 관계(키,외래키)로 연결된 여러 테이블에서 데이타를 조회 한다.

*** JOIN을 두려뭐 말라 !!!
개발 초심자 분들은 코딩으로 목록데이타를 다루는데 익숙한지라 쿼리문으로 두 테이블을 병합하는 개념에 익숙하지 않아 하는 경향이 있다. 
하지만 관계형 데이터베이스를 사용하는 이상 JOIN을 두려워 하면 안된다.
JOIN문을 이용해서  데이타를 조회 하라고 만든 데이터베이스가 관계형 데이터 베이스이다.
JOIN문이 성능을 저해한다니 뭐니.. 하는 말을 하는 개발자가 있으면... 버려~~~

5.2.1 회원정보 조회 ( 기본정보, 가입및 탈퇴 시간,추천인. 사유  조회 ).
- 가입자 정보만 조회 하는 경우.
단순히 조인으로 1:1 매핑으로 한행만 가져와야 하는 경우는 다음과 같이 간단하다.
SELECT A.user_id, A.name, A.identity_number,user_class,right_class, address, B.join_class, B.invitor,B.insert_date AS join_date
FROM User AS A
INNER JOIN UserJoinLeave AS B ON A.user_id = B.user_id
WHERE B.join_class = 1 -- 가입 

- 탈퇴시간, 탈퇴 사유는 어떻게 조회?
탈퇴자의 경우는 
UserJoinLeave에 join_class = 2로 하나의 행이 더 존재한다. 이 행을 열로 표현하면 되는데 어쩌지?
여기서 막막해진 개발자들은 대부분 코드에서 User조회해오고 UserJoinLeave조회 해 와서 루프를 뱅글뱅글 돌려가며 결과 셋을 만들어 내려고 삽질을 시작하게 된다.

- 다음의 쿼리문으로 간단히 해결된다.
SELECT A.user_id, A.name, A.identity_number,user_class,right_class, address, B.join_class, B.invitor,B.insert_date AS join_date
,(SELECT insert_date FROM UserJoinLeave WHERE user_id = A.user_id AND join_class = '2') AS leave_date
,(SELECT comment FROM UserJoinLeave WHERE user_id = A.user_id AND join_class = '2') AS leave_reason
FROM User AS A
INNER JOIN UserJoinLeave AS B ON A.user_id = B.user_id
WHERE B.join_class = '1'
기본 조회 행에 컬럼으로 SELECT문을 이용하여 탈퇴정보를 조인과함께 조회하는 형식의 쿼리문이다.
추가되는 컬럼이 2개라서 그나마 똑같은 쿼리가 2번 나타나지만 추가되는 컬럼개수만큼 많아지는 단점이 있다.
건수가 많아지면.. 쿼리가 쬐금 느려지겠죠?

- 더 나은 방법은 없는가?
다음과 같이 GROUP BY 절을 사용하여 두행으로 나타난 UserJoinLeave테이블을 user_id별로 Case문과 Max집계함수를 이용하여 훨씬 성능적으로 이전보다 더 나은 쿼리문을 만들 수 있다.

SELECT A.user_id
, MAX(CASE WHEN A.join_class = '1' THEN A.insert_date ELSE NULL END) AS join_date
, MAX(CASE WHEN A.join_class = '2' THEN A.insert_date ELSE NULL END) AS leave_date
, MAX(CASE WHEN A.join_class = '1' THEN A.comment ELSE NULL END) AS join_reason
, MAX(CASE WHEN A.join_class = '2' THEN A.comment ELSE NULL END) AS leave_reason
FROM  UserJoinLeave A
GROUP BY A.user_id

어? 왜 다른 유저정보는 없나요? 라고 이제는 의문을 가질 필요 없습니다. 조회한것과 User테이블간 조인만 하면 그만...
다음과 같이...위 쿼리를 서브쿼리로 넣으면 된다.

SELECT  A.user_id, A.name, A.identity_number,user_class,right_class, address , B.invitor , B.join_date , B.leave_date, B.join_reason, B.leave_reason
FROM User A
INNER JOIN (
SELECT A.user_id
, MAX(CASE WHEN A.join_class = '1' THEN A.invitor ELSE NULL END) AS invitor
, MAX(CASE WHEN A.join_class = '1' THEN A.insert_date ELSE NULL END) AS join_date
, MAX(CASE WHEN A.join_class = '2' THEN A.insert_date ELSE NULL END) AS leave_date
, MAX(CASE WHEN A.join_class = '1' THEN A.comment ELSE NULL END) AS join_reason
, MAX(CASE WHEN A.join_class = '2' THEN A.comment ELSE NULL END) AS leave_reason
FROM  UserJoinLeave A
GROUP BY A.user_id
) B ON A.user_id = B.user_id

이처럼 Case문 + Group By문을 이용하면 1:N의 데이타를 컬럼으로 쉽게 데이타를 가공할 수 있음!!!
( 행을 컬럼으로 변환하는 Case문의 응용에 해당 )


*** Case문에 대한 더 많은 응용은 여기를 참고!

*** 행열 변환에 관련된 더 상세한 내용은 여기를 참고 ! ( TSQL 예이지만 MySql에서도 거의 작동하는 쿼리문들임 )


5.2.2 회원정보 접속정보 조회.
-- CodeMaster에 로그인/아웃  구분을 먼저 입력
INSERT INTO CodeMaster(id, up_id, value, title, order_seq)
SELECT 40, NULL,'log_class', '로그인/아웃  구분', 0
UNION ALL
SELECT 41, 40, '1', '로그인', 0
UNION ALL
SELECT 42, 40, '2', '로그아웃', 1


-- UserLogging 테이블생성 : 회원의 시스템 로그인/아웃 로그 
-- drop table UserLogging;
-- log_seq는 user_id별로 계속 증가하는 값
CREATE TABLE UserLogging(user_id INT,log_seq INT, log_class  VARCHAR(10),comment VARCHAR(50), insert_date DateTime,PRIMARY KEY (user_id, log_seq)) ;


-- 데이타 넣기 : 유저별, 기간별 데이타를 넣어야 하므로 날짜처리에 대한 이해를 하고 지나가자.
-- MySql에서 데이타 연산함수는 DATE_ADD
SET @insert_date = DATE_ADD(NOW(), INTERVAL -2 MONTH);
SELECT @insert_date;

tobe continue....























구글광고