Mysql Join, union, view

 

출처 : http://rapapa.net/?p=311

 

 

● 샘플 데이타 생성 입력

 

CREATE TABLE user_info (
        id bigint NOT NULL AUTO_INCREMENT COMMENT '고유 번호',
        name varchar(64) NOT NULL DEFAULT '' COMMENT '이름',
        reg_date datetime NOT NULL COMMENT '등록 시각',
        address varchar(64) NOT NULL DEFAULT '' COMMENT '주소',
        phone varchar(64) NOT NULL DEFAULT '' COMMENT '주소',
        PRIMARY KEY (id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='테스트 유저 정보';


CREATE TABLE order_info (
        id bigint NOT NULL DEFAULT 0 COMMENT '고유 번호',
        order_date datetime NOT NULL COMMENT '등록 시각',
        order_no int NOT NULL AUTO_INCREMENT COMMENT '상품 번호',
        order_name varchar(256) NOT NULL DEFAULT '' COMMENT '상품 이름',
        PRIMARY KEY (order_no, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='테스트 주문 정보';

 

 insert into user_info (name, reg_date, address, phone)values
('김서울', now(), 'seoul', '02-555'), ('홍일산', now(), 'goyang','031-555'), ('최부산', now(), 'pusan','043-555'), ('이울산', now(), 'ulsan', '052-555');



insert into order_info (id, order_date, order_name) values
(1, ADDDATE(now() ,INTERVAL -1 day), '김서울 1번 구매'),
(1, ADDDATE(now() ,INTERVAL -3 hour), '김서울 2번 구매'),
(2, ADDDATE(now() ,INTERVAL -13 hour), '홍일산 1번 구매'),
(3, ADDDATE(now() ,INTERVAL -3 day), '최부산 1번 구매'),
(3, ADDDATE(now() ,INTERVAL -2 day), '최부산 2번 구매'),
(3, ADDDATE(now() ,INTERVAL -1 hour), '최부산 3번 구매'),
(14, ADDDATE(now() ,INTERVAL -1 hour), '주인 없는 구매');

 

● INNER 조인

: 테이블간 교집합을 구합니다.

 select * from mr_logdb_2.user_info as a JOIN mr_logdb_2.order_info as b ON a.id = b.id;

 

 

ON 대신 WHERE을 쓸수 있습니다.

select * from mr_logdb_2.user_info as a JOIN mr_logdb_2.order_info as b where a.id = b.id;

 

● LEFT OUTER, RIGHT OUTER 조인

left outer join에서 outer는 옵션이므로 생략해도 됩니다.

 

 select * from mr_logdb_2.user_info as a LEFT JOIN mr_logdb_2.order_info as b ON a.id = b.id;
   A (존재 하지 않는 B는 null로)

 

 select * from mr_logdb_2.user_info as a RIGHT JOIN mr_logdb_2.order_info as b ON a.id = b.id;
   B (존재 하지 않는 A는 null로)

 

● 뷰 (VIEW)

 

허용된 데이터를 제한적으로 보여주기 위해서 하나 이상의 테이블에서부터 유도된 가상 테이블 입니다.

특징

1. 뷰가 정의된 기본테이블이 변경 되면, 뷰도 자동적으로 변경 됩니다.

2. 외부 스키마는 뷰와 기본 테이블 정의로 구성됩니다.

3. 뷰에 대한 검색은 기본테이블과 거의 동일하지만 삽입, 삭제, 갱신은 제약을 받게 됩니다.

4. DBA 는 보안측면에서 뷰를 활용할수 있습니다.

5. 뷰는 CREATE문에 의해 정의 되며 SYSVIEW에 저장됩니다.

6. 한번 정의된 뷰는 변경할수 없으며, 삭제한 후에 다시 생성해야 합니다.

7. 뷰의 정의는 ALTER 문을 이용하여 변경할수 없습니다.

8. 뷰를 제거 할때에는 DROP 문을 사용합니다. (drop view 뷰이름;)

 

장점

1. 논리적 독립성을 제공합니다.

2. 데이터 접근 제어로 보안 가능합니다.

- 뷰를 통해 접근하기 때문에 뷰에 나타나지 않는 데이터를 보호 할수 있습니다.

3. 사용자의 데이터 관리를 간단하게 합니다.

4. 하나의 테이블로 여러개의 상이한 뷰를 정의할수 있습니다.

 

CREATE VIEW user_view AS
SELECT a.id, a.name, a.reg_date, b.order_no, b.order_name, b.order_date
FROM mr_logdb_2.user_info as a JOIN mr_logdb_2.order_info as b
ON a.id = b.id;

select * from user_view;

 

단점

1. 독자적인 인덱스를 가질수 없습니다.

2. 정의를 변경할수 없습니다.

3. 삽입, 삭제, 갱신 연산에 많은 제약이 따릅니다.

 

 

● 유니온 (UNION)

 

규칙

1) 하나의 order by 만 사용할 수 있습니다.

2) 각 select의 열수, 표현식이 같아야 합니다.

3) select 문들 끼리 순서는 상관 없습니다.

4) 유니온을 한 결과가 중복되면 하나만 나옵니다. (default)

5) 열의 타입은 같거나 반환 가능한 형태여야 합니다.

6) 중복값을 나타내고 싶다면 union all을 사용하면 됩니다.

 

 

● 서브 쿼리

서브 쿼리는 join으로 할 수 있는 기능과 유사한 기능을 제공합니다.

조인의 경우는 select로 조회를 하는 기능이지만, 서브 쿼리는 select, insert, update, delete 모두 가능 합니다.

 

출처 : code-factory.tistory.com/43?category=724516