2023.03.12 DB
DB MySQL ver
34. INNER 조인(JOIN)
조인이 무엇인가?
DB는 중복을 없애고 참조하는 방식으로 데이터가 결합되지 않도록 했엇다.
합쳐서 원래 모양으로 조인을 활용해서 활용할 필요가 있다. 이것을 조인이라고 한다.
조인의 종류
INNER JOIN, OUTER JOIN, SELF JOIN, CROSS JOIN(Cartesian Product)
MEMBER ID NEWLEC NAME 뉴렉
NOTICE ID 1 TITLE ORACLE개요 WRITER_ID NEWELEC
NOTICE ID 2 TITLE SQL구분 WRITER_ID NEWELEC
중복되는 부분을 합친다.
멤버의 ID가 NOTICE에 WRITER_ID라는 것을 가지고 글을 등록하는 것이다.
그래서 멤버가 부모 테이블이고 NOTICE가 자식 테이블이 되는 것이다.
부모는 보통 하나고 많은 쪽인 것이 자식이 된다.
항상 상대적이라 NOITCE 가 테이블을 낳을 수도 잇다.
합칠땐 참조된 데이터 수만큼 데이터를 복제한다.
잘라서 저장하고 합쳐서 등등 하면 귀찮지만 수십만개의 레코드가 있다면 ID를 하나로 줄일 수있으니 전체적인 성능을 줄일 수 있게 된다.
합친다면 메모리에서 합치는 것이기 때문에 디스크에 있는 것을 가져오는 것보다는 훨씬 빠르고 좋다.
결합하는게 불편하지만 가능하면따로 나누는게 쉽다.
SELECT * FROM MEMBER INNER JOIN NOTICE ON member.id = notice.memberId;
회원정보 + 공지사항이 붙어서 표현이 된다.
합칠경우 관계가 있는거만 합쳐야한다.
공지사항 등록한 사람만 서로 관계가 있는사람들은 INNER 관계가 없는 레코드들은 OUTER이다.
관계가 있는 것만 합치는 것을 INNER JOIN이라고 한다.
참조키를 기준으로 일치하는 행만 조인
관계가 깨져있는 경우? -> 관계가 있는 것만 한다.
양쪽 다 OUTER를 제거하고 부모를 복사한다음 join한다.
테이블 2개를 가지고 관계가 있는 것을 기준으로 한다.
35. LEFT/RIGHT/FULL 아웃터 조인(OUTER JOIN)
참조키를 기준으로 일치하지 않는 행도 포함시키는 조인이다.
MEMBER LEFT/RIGHT/FULL OUTER JOIN NOTICE ON MEMBER.ID = NOTICE.WRITER_ID;
관계가 없는 것들도 JOIN할 수 잇다.
부모가 없는 자식테이블의 것은 결함이다.
관계없는 OUTER가 왼쪽테이블에는 3개 오른쪽 테이블엔 2개가 있다고 해보자
왼쪽 OUTER를 포함하겟다 LEFT 오른쪽을 포함하겟다 RIGHT 모두다 하겟다 FULL OUTER JOIN을 사용하면된다.
select * from notice n left join member m on m.id = n.WRITER_ID;
우측테이블의 관계없는 OUTER를 없애고 합쳐진다. 값이 없는 부분들은 NULL로 채운다.
INNER JOIN에 OUTER JOIN이 늘어나게되는것이다.
문제 RIGHT OUTER JOIN시 레코드의 개수는?
왼쪽 관계 1 무관계3 오른쪽테이블 관계3 무관계2
-> 왼쪽 무관계없어지고 1을 복사하고 나머지는 NULL을 넣은 5개가 된다.
mySQL에서는 FULL OUTER조인을 지원하지 않는다.!!
유니온을 사용하면 비슷하게 만들 수 있다. 나중 강의에서 나온다.
오라클에서 FULL OUTER를 사용하면
관계 3 + LEFT 3 + RIGHT 2 = 8개
36. OUTER JOIN을 이용한 게시글 목록 조회
그러면 이너조인 아우터 조인이있는데 뭘 더 많이사용하나? 아우터 조인을 많이 사용한다.
왜그럴까?
충돌되는게 전혀없으면 어떻게 JOIN하나?
컬럼을 합칠때 식별이 애매한 경우가 있다?
컬럼명을 식별하기 위해 테이블명을 사용해야한다. 이렇게 필드이름의 충돌문제가있다.
SELECT NOTICE.ID, NOTICE.NAME, MEMBER.NAME FROM MEMBER
INNER JOIN NOITCE ON MEMBER.ID = NOTICE.WRITER_ID
이런식으로 식별하자. 너무 길어지면 테이블명을 별칭을 사용할 수 있다.
SELECT N.ID, N.NAME, M.NAME FROM MEMBER M
INNER JOIN NOITCE N ON M.ID = N.WRITER_ID
이러면 컬럼명에 대한 것을 해결 할 수 있게 된다.
문제 ID NAME 그리고 회원별 작성한 게시글 수 를 조회하시오.
집게를 해야한다.
SELECT M.ID, M.NAME, COUNT(N.ID) FROM MEMBER M
INNER JOIN NOITCE N ON M.ID = N.memberId GROUP BY M.ID, M.NAME
대충봣을땐 문제가 없어보인다. 치명적인 문제가 있는데 회원이 둘만있나 그건아니다. OUTER도 있어야한다.
주인공을 정해서 OUTER JOIN을 하면된다. 왼쪽이 주인공이면 LEFT 오른쪽이 주인공이면 RIGHT
SELECT M.ID, M.NAME, COUNT(N.ID) FROM MEMBER M LEFT OUTER JOIN NOTICE N ON M.ID = N.memberId GROUP BY M.ID, M.NAME;
항상 주인공이 존재하고 그것을 중심으로 INNER 인지 OUTER인지를 해보자.
37. SELF JOIN
셀프조인은 개념상으로 그렇다는 것이다. 자기가 자기랑합쳐지는 것이다.
자기조인 데이터가 서로 포함관계를 가지는 경우에 사용한다. : 담당 구성 연락
자기가 자기와 합쳐지는 것임 자기가 자기를 합칠 이유가잇을까 한개의 테이블이 여러개 처럼 될 수 잇을까?
사원테이블 에서 홍길동을 찾앗다해보자
홍길동-강호동
홍길동-유재석
이럴때 부서원이라는 관계가 있다고 생각해보자. 강호동의 부서장을 출력하거나 홍길동의 부서원을 출력하거나 이럴때 자기가 자신을 조인해서 사용할 수 있다.
관계명? 참조컬럼을 하나 더 추가해서 해야한다. BOSS_ID같은 하나의 컬럼을 더 넣어준다.
그냥 JOIN하면 테이블이름이 겹쳐서 오류가 발생하니 별칭을 각각 붙여준다.
SELECT M.*, B.NAME BOSS_NAME FROM MEMBER M LEFT OUTER JOIN MEMBER B ON B.ID = M.BOSS_ID;
사원은 다나와야하니 사원이 주인공이다. 사원이 주인공으로 LEFT조인해야한다.
셀프조인이 많이 사용되나? 의외로 많이 사용됨 댓글이 댓글을 참조할때 대댓글 같은 것 관리할때 사용한다.
39. 유니온 union
JOIN처럼 합치는 것이다. 그러나 JOIN은 컬럼을 합쳣다.
UNION은 레코드를 합치는 것이다. 관련이 없어도된다. 컬럼의 개수만 맞춰주면 레코드를 합칠 수 잇다.
어디에 사용하나? 게시판이 3종류인데 별도의 테이블을 만들고 합쳐서 통합검색을 할 경우가 있다.
이럴때 사용한다. A조건 결과물 B조건 결과물 등을 일일히 합쳐서 만들 수 있다.
SELECT ID, NAME FROM MEMBER
UNION
SELECT ID, TITLE FROM NOTICE;유니온이라는 것이 사실 두개 레코드를 합치는 것뿐만이 아니다.
오라클에는 4가지 방식이 있다. MYSQL에는 두가지 방식
UNION 같은 내용이면 하나로 합친다. 완전이 데이터가 일치하면?
1테이블 123 2테이블 124면 1234로 4개만 나온다.
UNION ALL 상관없이 다 합친다. 중복제거 없이 정렬도 하지 않는다.
나머지 두 연산은 MYSQL에서 지원하지 않는다. 오라클의 기준으로 보면
MINUS 1테이블 123 2테이블 124일때 2테이블을 기준으로 뺀다. -> 3만남음
INTERSECT 공통분모만 남김 1테이블 123 2테이블 124일때 1,2만남음
MySQL에서 이것들을 실현시키려면 쿼리를 새롭게 짜서 작성해야한다.
그런데 보통 유니온은 하나의 테이블로도 할 수 있다.
SELECT ID, NAME FROM MEMBER WHERE NAME LIKE '%n%'
UNION
SELECT ID, NAME FROM MEMBER WHERE NAME LIKE '%g%';필터링한 후 또 필터링 하고싶을때 두개의 결과집합으로 연산하고 싶을때 사용한다.
MYSQL에서 FULL OUTER JOIN이 없는데 이것을 UNION으로 할 수 있다.
LFET조인과 OUTERJOIN을 합치면 된다.
SELECT * FROM MEMBER left JOIN NOTICE ON member.id = notice.memberId
UNION
SELECT * FROM MEMBER right JOIN NOTICE ON member.id = notice.memberId;프로젝트 진행하다가 문뜩 필요하다고 기억이나면 사용하면된다.
40. VIEW
사람은 view를 통해서 보는 풍경 한정된 부분을 본다.
물리적인 데이터구조(테이블)과 개념적인 데이터구조 view차이 중복을 제거하고 나누어서 저장했다.
매번 같이 보는것은 힘들다. 이것을 합쳐놓고 싶다. 이때 사용되는게 view이다.
합쳐놓는 쿼리를 매번 적는게 너무 어렵다.
CREATE VIEW 뷰이름
AS
쿼리문
CREATE VIEW NOTICEVIEW
AS
SELECT N.ID, N.TITLE, N.WRITER_ID, M.NAME WRITER_NAME, COUNT(C.ID) COMMENT_CNT
FROM MEMBER M
RIGHT OUTER JOIN NOTICE N ON M.ID = N.WRITER_ID
LEFT OUTER JOIN "COMMENT" C ON N.ID = C.NOTICE_ID
GROUP BY N.ID, N.TITLE, N.WRITER_ID, M.NAME;VIEW를 이용해 쿼리할 수 있다.
SELECT * FROM NOTICEVIEW 이걸 응용해서 사용할 수 있다.
이것을 활용하여 ORDER BY든 WHERE절이든 사용할 수 있게 된다.
매번 데이터를 가공해서 사용하는것이 불편해서 VIEW로 만들어두고 사용하는 것이다.
41. 데이터 딕셔너리
DBMS 사용자정보 권한 테이블/부 등을 저장할수있는 공간이 잇다. 이것들이 데이터 딕셔너리이다.
이정보를 가지고 얻어낼 수 있는것?
요즘에는 잘 사용안한다. 윈도우 형태이기때문에 클릭으로 보면된다.
뷰형태로 보여준다. 하나의 테이블인데도 뷰로 볼필요가 있나? 데이터 딕셔너리는 권한밖을 안보여주기 위해서 뷰를 사용하는 것이다.
조회용으로 읽기 용으로 사용하는데도 효과적인 도구이다.
과거에는 CMD 콘솔기반으로 사용해서 테이블을 보기위해서 데이터 딕셔너리로 봣었다. 최근에는 잘 사용하지 않는다.
42. 도메인 제약조건
ID가 다 똑같으면 중복이 되서 식별을 할 수 없으니 안된다. 그래서 제약이란 것이 필요하다.
아무런 데이터가 들어갈수 없도록 제약을 걸고 무결성이 위배되지 않도록 해야한다.
도메인 : 유효한 값의 범위
컬럼에다가 데이터를 입력한다. 컬럼이란 각각 유효한 값의 형식이 있다.
EX) 학번 0보다 큰정수 이름 20자 내의 문자
어떤 컬럼도 도메인을 가지고 잇고 유효한 범위 안에서 하도록 있는 것이다.
속성에 도메인이 아닌 값이 올 수 없도록 하는 제약 조건
NOT NULL/ DEFAULT/ CHECK
사용자가 전달하기 애매한것들 조회수 등등? 알아서 들어가야한다.
도메인 범위를 체크하는 CHECK도있다.
42.1 NOT NULL 제약조건
테이블을 생성할때 적용방법
CREATE TABLE TEST
(
ID VARCHAR(50) NOT NULL DEFAULT '1'
);
이미 테이블이 있다면
ALTER TABLE TEST MODIFY EMAIL VARCHAR(200) NOT NULL;
코드를 적지 않아도 도구를 사용해서 할 수 도 있다.
42.2 DEFAULT 제약조건
PWD VARCHAR(200) DEFAULT '111'
값을 안넣엇을때 기본으로 지정되는 값을 넣는다.
데이터결함을 없애기위해 제약조건을 잘 알아둘 필요가 있다.
43. 체크 제약조건
도메인제약조건의 가장 꽃이라고 할 수 있다.
CREATE TABLE TEST
(
ID VARCHAR(50) NULL,
PHONE VARCHAR(200) CHECK(PHONE LIKE '010-%-____') NOT NULL,
EMAIL VARCHAR(500) NULL
)나중에 추가하고자한다면?
ALTER TABLE TEST ADD CONSTRAINT CK_TEST_PHONE CHECK(PHONE LIKE '010-%-____');
011111231 이런식으로 넣으면 오류발생한다.
편집-제약조건-새검사 제약조건
제약조건에 맞지않게 데이터를 넣으면 ORA-02290: 체크 제약조건(JAVA.MEMBER_PHONE_CHK1)이 위배되었습니다가 발생한다
제약조건을 보는게 어렵다.
SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM information_schema.table_constraints
WHERE table_name='테이블이름';
2023.03.12 후기
제약 조건을 오라클 sql에서 보는 것은 한눈에 나타나서 좋았다.
그런데 MySQL workbench는 왜인지 모르겠으나 제약 조건의 상세한 내용이 보이질 않는다.
제약조건이 적용이 안되는건 아니다.
검색을 해봐도 제약조건의 이름을 찾는 방법은 나오나 내용을 보는 방법을 찾지 못했다.
제약 조건을 거는 방법만 이해하고 보는 방법은 국비에서 질문을 통해 헤쳐나가야할듯 하다.