1. Sub Query

- 서브쿼리는 “쿼리 안의 쿼리” 라는 뜻이다.

- 서브쿼리는 사전에 추출된 내용에서 재 검색 하거나, 검색된 내용을 가상 컬럼을 만들어 추가 할 수 있다.

- 즉, 서브쿼리를 사용하는 이유는 가져온 데이터를 재 정제 하기 위함이라 볼 수 있다.

2. Join

- 서브쿼리 만을 사용 한다면 쿼리가 굉장히 복잡해 진다.

- 그래서 우리는 join 을 활용 한다.

Join sql

-- Join?
-- 둘 이상의 테이블을 연결해서 데이터를 검색하는 방법 이다
-- 각 테이블에는 공통되는 컬럼이 적어도 하나이상으 존재해야 한다
-- 일반적으로는 pk(일반키)와 fk(외래키)를 이용하여 join을 한다

-- join 방법
-- 1.cross joi,Equi join(등가조인, 애부조인)
-- non-equi join(거의 사용하지 않음)
-- self join,outer join
-- 제일 많이쓸거 Equi join,outer join

-- 0.CROSS JOIN
-- cross join은 카다시안콥을 수행 한다
-- 카다시안곱을 행한 조합을 반환 한다
-- emp(10) * dept(4) =40 
-- from [테이블1] cross join [테이블2]
-- select * from emp e, dept d; -- 양컬럼의 테이블을 다보여준다

select d.deptno,d.deptname,e.ename from emp e cross join dept d; -- as 사용안함
-- cross join은 생략이 가능 하다
select d.deptno,d.deptname,e.ename from emp e, dept d;

-- cross join 은 두 테이블의 모든  조합을 변환하기 때문에 의미있는 데이터를 추출하기 어렵다
-- 그래서 특정 조건을 주어 아래 조인등을 수행 한다

-- 1. Equi join
-- 가장 일반적인 조인(=) 을 사용한다
select * from emp;
select * from dept; 

-- 1) 등가조인(가장 기초)
-- emp 테이블에도 있고, dept 테이블에도 deptno가 있으면 가져와라
select d.deptno,d.deptname,e.ename 
	from emp e, dept d where e.deptno = d.deptno; -- 양쪽에 다 존재 했을 경우에만 
	
insert into dept (deptno,deptname,loc)
	values(5,'dev02','FLOLIDA');
-- dept 테이블에 deptno 3,5 가 있지만
-- emp 테이블에는 deptno 가 1,2,4 만 존재 한다
-- 그래서 3,5는 나오지 않는다
-- 등가 조인은 아래와 같이 표시될 일이 없다는 것
-- deptno,deptname,ename
-- 5      dev02    null

-- 2) 내부조인(INNER JOIN)
-- 조인에 대한 조건을 on으로 사용하고
-- 조인하는 테이블 사이에 inner join 을 넣는다(inner 는 생략 가능)
select d.deptno,d.deptname,e.ename 
	from emp e inner join dept d on e.deptno = d.deptno;

-- 둘중 어느 것이 조건이 더 알아보기 쉬운가?
select d.deptno,d.deptname,e.ename 
	from emp e, dept d where e.deptno = d.deptno and e.ename ='kim'; -- 아래꺼하고 이거하고 둘중 자기가 구분이 가능한걸 쓴다

select d.deptno,d.deptname,e.ename 
	from emp e join dept d on e.deptno = d.deptno where e.ename ='kim'; -- inner 생략 가능 결과는 위에 김하고 같음
	
	
-- on 대신 using 을 조건으로 사용 할 수 있다.
-- using 안에는 사용될 컬럼명을 넣을수 있으며, 서브쿼리도 넣을 수 있다
select d.deptno,d.deptname,e.ename 
	from emp e join dept d using (deptno);
 
-- 3) natural join
-- 동일한 값을 갖은 컬럼을 내부적으로 자연스럽게 조인 하므로 조건을 주지 않는다
-- 다만 동일한 컬럼은 단축명을 주지 말자
-- 양쪽에(공통된 명) 다있는것은 단축명 없이 컬럼을 쓰자 == deptno
select deptno,d.deptname,e.ename   
	from emp e natural join dept d; -- 자연스럽게 조인
	
-- 4)OUTER join
-- equi join 에서 할수 없는 걸 한다
-- 기준(데이터르 더 보여줘야 할 곳)이 되는곳을 지목을 한다
-- outer는 생략 가능
-- from[테이블A][left|right|full] outer join [테이블B] on [조건절]
-- left outer join(왼쪽을 기준으로 더 있는 값을 보여줌)
-- right outer join(오른쪽을 기준으로 더 있는 값을 보여줌)
-- full outer join(양쪽 서로에게 없는 값들을 보여줌 - Mariadb 에서는 지원x)
	
-- 오른쪽에 있는 dept 테이블의 deptno 가 더 많으므로 right join을 사용
select d.deptno,d.deptname,e.ename 
	from emp e right outer join dept d on e.deptno = d.deptno; -- 조인을 기준으로 왼쪽 오른쪽 구분
	
-- dept 에 없는 deptno 6을 이용해 emp에 데이터 추가	
-- dept에 없는 구조를 emp에 넣을려고 하기 때문에 에러가 뜨는거다
insert into emp(ename,job,deptno,hiredate)
	value('kim','assistant',6,str_to_date('14-06-02','%Y-%m-%d'));

select * from emp;

-- 위에 날짜 하고 in emp 써있는걸 사용할려면 밑의 같은 방식을 사용해야함
-- dept가 부모이기 때문에 dept 없는 deptno는 emp에 넣을 수 없다.
-- 그래도 어쩔수 없이 넣고 싶다면 부모-자식 관계를 해제해야 한다. == 자식의 외래키를 해제하면 된다
select * from information_schema.TABLE_CONSTRAINTS where table_name ='emp'; -- emp_ibfk_1
-- 외래키 제약조건을 삭제한다
alter table emp drop constraint emp_ibfk_1;



-- emp 값이 더 커졌기 때문에 left 활용
-- 이번에는 왼쪽 테이블(emp) 에는 dept 에 없는 deptno 6 이 있으므로
-- 왼쪽것을 더 보여줄 것이다
select d.deptno,d.deptname,e.ename 
	from emp e left outer join dept d on e.deptno = d.deptno;
	

-- FULL OUTER JOIN 은 마리아DB에서는 UNION으로 사용 할 수 있다

-- 3. SELF join(자기조인)
-- EQUI JOIN 과 같으나 대상 테이블이 스스로라는 것이 차이점이다.
-- 자기조인은 두 데이터 간에 카다시안 곱을 수행한다
select a.deptno,b.deptname,a.job 
	from emp a, emp b where a.deptno = b.deptno;

3. 집합(Set)

- UNION 은 중복 확인 을 위해 전체 검색 후 정렬하여 검사를 수행 하기 때문에 성능에 좋지 않다.

4. Index

- index(색인) 은 검색을 빠르게 하기 위한 수단이다.

- Primary key 와 Unique key 가 지정되면 따로 index 를 생성된다.

5. View

- 뷰는 하나의 가상 테이블이라 생각 하면 된다.

- 뷰는 복잡한 Query를 통해 얻을 수 있는 결과를 간단한 Query로 얻을 수 있게 한다.(중요)

- 한 개의 뷰로 여러 테이블에 대한 데이터를 검색 할 수 있다.

6. Auto_increment

- Auto_increment 는 자동으로 증가하는 속성 이다.

- Table 생성시 속성으로 지정해 주거나 이미 생성된 Table 에 추가 해 줄 수 있다.

- 단 auto increment 속성을 사용되는 컬럼은 키 설정이 되어 있어야 한다.

7. Limit & offset

- 데이터가 많을 경우 한번에 볼 수 없을 수 있다.

- 그래서 우리는 limit 와 offset 을 활용 한다.

- 많은 양의 데이터를 paging 하여 보여줄 때 유용 하다.

Limit & offset sql

select * from employees; -- 18개

-- 5개씩 몇 페이지가 나올까?
-- 0~4/ 5~9 /10~14 / 15~17

-- 1)limit : 특정 갯수만 가져온다.
select * from employees e limit 5; -- 0~4
-- 2)limit m,n : m번(페이지)부터 ,n 개 가져온다.
select * from employees e limit 5,5; -- 5~9

-- 3)offset : 시작할 index 를 지정해 준다.
-- limit n offset m: m 번 부터, n 개 가져온다
select * from employees e limit 5 offset 10; -- 10~14


-- 위 방식은 간단 하지만 속도가 생각보다 나오지 않는다(데이터가 많을 경우)
-- 데이터를 확보해서 처리하는게 속도를 빠르게 할수 있다

-- 1.데이터 정렬후 확보된 데이터로 가져오는 방법
-- select * from employees e order by emp_no; -- 정렬
-- (select * from employees e order by emp_no) -- 서브쿼리로 묶는다
select 
i.emp_no
,i.first_name
,i.family_name
from (select * from employees e order by emp_no) i
limit 0,5;


-- 인덱스를 활용하면 좀 더 빠르다
-- 2. 인덱스가 걸려있는 emp_no 를 이용해 기존 데이터와 조인을 해서 데이터 확보 == 데이터 확보(select * from employees e order by emp_no limit 0,5)
select 
e.emp_no
,e.first_name
,e.family_name
-- emp_no index에 걸리므로 속도가 빠르다
from (select emp_no from employees e order by emp_no limit 0,5) i
join employees e on i.emp_no = e.emp_no;