국비코딩 [Maria DB] join, maria DB에서 full join 사용하기(union)
join
둘 이상의 테이블을 연결하여 데이터를 검색하는 방법
조건 : 두 개의 테이블을 select문장으로 조인하려면 적어도 하나의 컬럼이 공통으로 존재해야 한다
그래서 일반적으로 pk와 fk를 활용한다(얘넨 너무나도 확실하니까) pk와 fk가 아니어도 됨
join의 종류
1. cross join(거의 쓰이지 않음)
2. equi join (등가조인, 내부조인, natural join 3가지 구분함)
3. non equi join (거의 사용하지 않음, 교재에 있긴해서 설명함)
4. self join
5. outer join
cross join
왜 안쓰이냐? cross join은 카다시안 곱을 수행하고 그 결과를 반환함 -> 많은 데이터가 나옴
from [table A] CROSS JOIN [ table B]
cross join 생략가능
select * from emp; -- 10개
select * from dept; -- 4개
select e.ename, d.deptname from emp e, dept d; -- 어느 테이블에서 뭘 가져왔는지 명시가 돼야함, 그래야 안헷갈려--> 양 테이블의 모든 컬럼의 조합을 보여주므로 의미있는 데이터를 뽑아내기 어렵다.
->그래서 조건을 추가하여 아래 조인등을 수행한다
이렇게 다 합친다음에 조건을 던져서 원하는 결과만 보는거임
equi join
가장 일반적인 조인
여러가지 equi join들이 있는데 다른게 아니라 다 같은 것들임 다만 각 문법에 따른 장단점이 있음
1) 등가조인 (가장 많이 씀)
특징 : 양쪽 테이블에 같은 값이 동등하게 있어야만 사용 가능
단점 : 일치하는 데이터만 볼 수 있기 때문에 만약 일치하지 않는 건 못 찾음
ex) deptno가 5번인 팀엔 팀원이 없네요 같은 걸 못함
select e.ename, d.deptname from emp e, dept d where e.deptno = d.deptno;2) 내부조인
특징 : inner생략 가능, on을 쓰니까 조인에 대한 조건과 그냥 조건을 구분할 수 있다는 편리함이 있음
on을 생략하고 using()을 쓸 수도 있는데 여기 안엔 서브 쿼리도 들어가
select e.ename, d.deptname from emp e inner join dept d on e.deptno = d.deptno;
-- inner 생략 가능, on대신 using 사용 가능
-- using은 ()안에 서브쿼리가 들갈 수 있다
select e.ename, d.deptname from emp e join dept d using (deptno);3) natuaral join
특징 : 공통 컬럼을 조건으로 쓰는건 너무 당연한거아님? 당연한거니까 굳이 조건 주지 않음 -> 알아서 다 잡아줌
단점 : 작성자는 편리하겠으나 읽는 사람은 파악이 어려움
select e.ename, d.deptname from emp e natural join dept d ;self join
등가조인과 같으나, 2개의 테이블이 아닌 1개의 테이블을 대상으로 한다
이것도 카다시안 곱을 해
outer join
등가조인은 2개 테이블에 모든 데이터가 존재해야되는데 외부조인은 둘이 달라도 사용할 수 있음
from [tableA][left|right|full]outer join[tableB] on 조건절
-- left outer join : A테이블 기준으로 더 많은 데이터를 보여줌
-- right outer join : B테이블 기준으로 더 많은 데이터를 보여줌
select d.deptno , e.ename , d.deptname from emp e right join dept d on e.deptno = d.deptno ;
-- full outer join : 양쪽 테이블에 각각 더 있는 값을 보여줌여기서 outer는 생략가능
| 부모자식 관계 끊는 법 (left join을 써보기 위해 그랬으나 보통 잘 사용하지 않음) -- alter table [테이블명] drop sonstraint [제약조건 이름]
-- 제약조건의 이름을 알기 위해선 밑에꺼 해야함 select * from information_schema.table_constraints where TABLE_NAME ='emp'; alter table emp drop constraint emp_ibfk_1; |
SET : maria DB full join 사용하기
maria db엔 full join이 없음 대신 union과 union all을 사용해서 같은 효과를 낸다
1. union (중복을 제거한 합집합)
당연히 동일한 칼럼이 한 개 이상은 있어야함
두 테이블을 합친거니까 right join과 left join 사용한 select문을 union으로 묶어주면 full join의 효과를 낼 수 있음
select e.deptno, e.ename, d.deptname from emp e left outer join dept d on e.deptno = d.deptno
union
select e.deptno, e.ename, d.deptname from emp e right outer join dept d on e.deptno = d.deptno;2. union all (중복이 포함된 합집합)
union보단 unionall이 더 많이 쓰임, 중복제거하는데 시간이 너무 오래걸려서 걍 우리가 수작업하는게 나음
union all을 통해 데이터 확보해 놓고 disctint로 중복제거를 해주면 union과 같은 결과를 낼 수 있음
select distinct u.deptno from
(select deptno from emp union all select deptno from dept order by deptno)u;3. intersect
교집합, 두 쿼리 사이에 중복되는 내용만 가져옴
select deptno from emp intersect select deptno from dept;4. not in(minus) 앞에서 뒤 빼기
-- emp - dept
select deptno from emp where deptno not in(select deptno from dept);
-- dept - emp
select deptno from dept where deptno not in(select deptno from emp);
| 메타데이터 : 데이터의 데이터 (어떤 지식의 또 다른 지식 ex 책 의 출판사, 작가 등) |
IN & EXISTS
OR 조건으로 검색할 때 사용
IN : 서브쿼리가 먼저 동작한 후 메인쿼리가 동작 , 크다 작다 비교 불가
select ename, job from emp e where deptno
IN(select deptno from dept d where loc = 'LA' or loc ='boston');EXISTS : 메인쿼리가 먼저 동작한 후 서브쿼리 동작-> 데이터를 먼저 확보하고 가는게 무조건 유리함
select ename, job, deptno from emp e
where exists(select deptno from dept d where e.deptno = d.deptno
and (loc = 'LA' or loc ='boston'));ANY & ALL
= any
: in와 같은효과 + 크다 작다 비교 가능
= ALL
: ANY와 비슷함(부등호를 쓸 수 있다는 점에서...)
-- >ANY : 최소값보다 크면
-- 최소값을 무조건 가정하기 때문에 min(hiredate) 라고 쓸 필요없음 걍 hiredate
-- 직책이 manager인 직원의 입사일이 가장 낮은 사람보다 큰 사람
select ename, job, hiredate from emp where hiredate >any(select hiredate from emp e where job='manager');
-- <ANY : 최대값보다 작으면
-- 마찬가지로 max 쓸 필요 없음
select ename, job, hiredate from emp where hiredate <any(select hiredate from emp e where job='manager');
-- ALL : ANY와 비슷함(부등호를 쓸 수 있다는 점에서...)
-- 1) =ANY : AND조건을 사용한다(조건의 값이 복수 개로 오면 사용할 수 없다.)
select ename, job from emp where deptno =all(select deptno from dept where loc='newyork');
select ename, job from emp where deptno = all(
select deptno from dept where loc = 'newyork' and deptname ='sales'
);
-- 2) >ALL : 최대값보다 크면
select ename, job, hiredate from emp where hiredate
>all (select hiredate from emp e where job='manager');
-- 3) <ALL: 최소값보다 작으면
select ename, job, hiredate from emp where hiredate
<all(select hiredate from emp e where job='manager');
INDEX
정의 : 목차같은 역할, 데이터를 빠르게 찾을 수 있도록
장점 : 데이터가 많고 수정이 적은 경우에 사용이 용이함, 빠르게 찾을 수 있음
단점 : 데이터가 적다면 굳이, 수정이 많다면 성능저하를 부름
특징 : PK나 UK처럼 중복값을 허용하지 않는 키들이 설정되어있으면 자동으로 걸림
1) unique index (중복이 없는 데이터에 인덱스를 걸어주는 것)
pk나 uk를 설정하면 자동으로 설정됨 (쟤넨 중복값을 허용하지 않는 데이터니까)
db속도가 느려지면 index걸어! -> 이미 pk되어있는데요?
create unique index[index name] on [table] (column)
2) non-unique index (중복이 있는 데이터에 인덱스를 걸어주는 것)
3) 결합 index
create index emp_combi_idx on emp(ename,job,deptno);4) 인덱스 삭제
속성으로 취급
alter table emp drop index emp_combi_idx;VIEW
여러 테이블을 모아 만든 가상의 테이블, 실제 테이블이 아님
장점 : 복잡한 쿼리를 하나의 가상 테이블로 만들었음 -> 한개의 view로 여러 테이블에 대한 데이터를 검색할 수 있다
보안유지에도 용이함, 보지말아야 할 테이블대신 view를 보게끔 할 수 있다
수정할 수 있지만 원본도 함께 수정
1) view 생성
-- creat [or replace] view [view name] AS[query]
-- as 뒤에 있는 쿼리를 뷰로 만들거야
-- or replace : 기존 뷰를 수정할 때 사용
2) 뷰 사용(일반 테이블과 독같다)
3) 뷰 수정 (alter table을 이용해도되긴함 )
-- creat [or replace] view [view name] AS[query]
create or replace view vw_name as
select e.ename, d.deptname, d.loc from emp e, dept d where e.deptno = d.deptno;
create of replace : 만약 해당 뷰가 있으면 수정, 없으면 생성
-- 혹시라도 있으면 덮어씌우고 없으면 만들어라
- 4) with check option : 뷰를 생성한 조건식을 만족하는 컬럼은 update 할 수 없도록 하는 옵션
create view vw_chk as
select ename, job, deptno from emp where deptno=1 with check option;이렇게 하면 조건식 칼럼인 deptno은 update할 수 없음
뷰조회
show full tables where table_type= 'view';뷰삭제
drop view vw_name;