2023.01.11 DB

DB

28. NULL 관련 함수(NVL/NVL2/NULLIF)와 DECODE 함수

데이터가 옵션인경우 NULL이들어갈 수있다.
28.1 반환값이 NULL일경우에 대체 값을 제공하는 NVL(NULL, 대체값) 함수
SELECT NVL(AGE, 0 ) FROM MEMBER;
NULL값에 연산을 하면 NULL값이다.
TRUNC(NVL(AGE,0) / 10 *10
28.2NVL에서 조건을 하나 더 확장한 NVL2(입력값 NOTNULL대체값, NULL대체값)함수
SELECT NVL(AGE, 100/AGE, 0 ) FROM MEMBER;
위에거쓰면 모든 레코드에서 연산이 NVL이 일어나게 된다. 비효율적임.
28.3NVL(AGE, TRINC(AGE/10)*10,0) NULL이 아닌경우에만 하기
두 값이 같은 경우 NULL 그렇지 않은 경우 첫번째 값 반환 NULLIF(값1, 값2)함수
NULL값을 만들어내는 함수
SELECT NULLIF(AGE,19) FROM MEMBER;
28.4 조검에 따른 값 선택하기 DECODE(기준값,비교값, 출력값, 비교값, 출력값)
SELECT DECODE(GENDER, '남성', 1 ,2) FROM MEMBER;
성별이 남자면? 1출력 아니면 2출력
SELECT DECODE(SUBSTR(PHONE,1,3),'011',SK,'016','KT','기타') FROM MEMBER
전화번호가 앞3자리가 011이면 SK 016이면 KJ 아니면 기타 삼항연산자의 느낌이다.
가준값 비교출 비출 하나만쓰면 출력값
SELECT DECODE(SUBSTR(PHONE,1,3),
'011',SK,
'016','KT',
'기타') FROM MEMBER
나눠서 작성해서 가독성 높이기

29. SELECT 구절과 정렬(ORDER BY)

집계함수 - SELECT문과 사용되어야한다.
SELECT문의 구절
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
이 순서를 잘외우고 있어야한다. 순서를 지켜야 한다. 순서가 바뀌어서는 안된다.
FROM 데이터를 연산하는 구조 테이블. 테이블들이 가지고 있는 구조 일 수 잇다.
WHERE 필터링
GROUP BY(COUNT, AVG) 집계 함수
HAVING 집계한것으로 필터링 HAVING COUNT(ID)>=2
29.1 ORDER BY 순서정렬
ASC(오름차순작->큰), DESC(내림차순 큰->작)
빼면 ASC가 기본이다.
이름을 기준으로 역순으로 정렬하시오.
SELECT * FROM MEMBER ORDER BY NAME DESC
회원중에서 박씨성을 가진 회원을 조회하시오. 단 나이를 오름차순으로 정렬
SELECT * FROM MEMBER WHERE NAME LIKE '박%' ORDER BY AGE ASC;
순서가 중요하다!!
2차정렬
SELECT * FROM NOTICE ORDER BY HIT DESC, REGDATE DESC;

30. 집계 함수와 GROUP BY

집계함수 SUM MIN MAX COUNT AVG
COUNT NULL값은 집계가 안된다. 그럼 레코드 수를 알고 싶다면? 1.절대 NULL이들어가지않는 값으로 계산 2.COUNT(*) 전자가 빨라서 바람직함.
SELECT COUNT(ID) FROM NOITCE;
SELECT AVG(HIT) FROM NOITCE;
소규모 그룹으로 나눠서 집계 작성자별로 ?
SELECT WRITER_ID, COUNT(ID) FROM NOTICE GROUP BY WRITER_ID;
->격자로 ID와 집계한 갯수로 제공해줌
그런데 중간에 TITLE같은거 넣을 수 있나? 집계는 집계된 단위가 한단위로 표시된것이니 집계에 포함안된것은 포함하면안된다. 오류발생함!
그런데 실행순서?
FROM절 -? CONNECT BY -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
FROM절에서 결정지은 별칭은 모두 사용가능 SELECT절에서 결정지은 별칭은 ORDER BY만 가능하다.

31. HAVING 절

회원별 게시글 수 를 조회하시오 단 게시글 수가 2이하인 레코드만 출력
SELECT WRITER_ID, COUNT(ID) FROM NOTICE WHERE COUNT(ID) < 2 GROUP BY WRITER_ID;
-->X 안됨 WHERE절에서는 집계함수 사용할 수 없다. 서브쿼리로 할 수 있지만 HAVING절로 해결이 가능하다.
SELECT WRITER_ID, COUNT(ID) FROM NOTICE GROUP BY WRITER_ID HAVING COUNT(ID) <2

32. ROW_NUMBER(), RANK(), DENSE_RANK()

정렬된 순서대로 일련번호를 붙이고 싶다.
SELECT ROWNUM, ID, TITLE, WRITER_ID, REGDATE, HIT FROM NOTICE ORDER BY HIT;
WHERE절에서 ROWNUM붙이면? 섞여버림 왜? ORDER BY가 마지막에 되니 순서가 뒤바뀌는거임.
그러면 정렬한후 ROWNUM을 붙이고싶다면
SELECT ROW_NUM() OVER (ORDER BY HIT) , ID, TITLE, WRITER_ID, REGDATE, HIT FROM NOTICE
서브쿼리처럼 ORDER BY한다음 ROWNUM붙이게 하는 함수 정렬된 상태에서 일련번호 붙이고싶을때 사용

등수를 붙이고 싶다면? RANK() 보통 4가 공동이면 다음이 6이다.
DENSE_RANK()하면 이어갈수잇도록 공동4등다음 5나오게 할 수 있다.

SELECT ROW_NUM() OVER (PARTITION BY WRITER_ID ORDER BY HIT) , ID, TITLE, WRITER_ID, REGDATE, HIT FROM NOTICE
이러면 그룹한것처럼 나올 수 있다.그룹별로 정렬되게 할 수 있음.

33. 부조회 서브쿼리

SELECT순서대로 해야하는데 순서를 바꾸고 싶다면 ? 서브쿼리를 사용하면된다.
구절의 순서를 바꿔야하는 경우
SELECT * FROM MEMBER WHERE ROWNUM BETWEEN 1 AND 10
->최신등록순으로 정렬한 결과에서 상위 열명을 원하는 경우라면?
SELECT * FROM MEMBER /ORDER BY REGDATE DESC /WHERE ROWNUM BETWEEN 1 AND 10 -> 순서때문에 오류발생
이럴때 서브쿼리를 사용할 수 있다.
FROM절에는 반드시 테이블이 와야하는게 아니다. 격자형데이터가 있다면 다 올 수 있다. 그래서 먼저 정렬을 한 격자형데이터를 만들고 그것으로 WHERE절을 넣자.
SELECT * FROM (SELECT *FROM MEMBER ORDER BY REGDATE DESC) WHERE ROWNUM BETWEEN 1 AND 10;
3 * (2 + 3)을 하는 느낌이다.
구절의 순서를 바꿀때 서브쿼리를 사용하는 것이다.
예제 나이가 30이상인 회원목록을 조회하시오.
SELECT * FROM MEMBER WHERE AGE >= 30
-> 평균나이 이상인 회원
SELECT FROM MEMBER WHERE AGE >= (SELECT AVG(AGE) FROM MEMBER)
먼저 수행해서 결과를 남겨야하는 경우에 서브쿼리를 떠올려서 사용하는 것이다.

34. 조인(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 가 테이블을 낳을 수도 잇다.
그래서 합치면 아래와 같게된다.

사진참조2
잘라서 저장하고 합쳐서 등등 하면 귀찮지만 수십만개의 레코드가 있다면 ID를 하나로 줄일 수있으니 전체적인 성능을 줄일 수 있게 된다.
합친다면 메모리에서 합치는 것이기 때문에 디스크에 있는 것을 가져오는 것보다는 훨씬 낫다.

SELECT * FROM MEMBER INNER JOIN NOTICE ON MEMBER.ID = NOTICE.WRITER_ID;
회원정보 + 공지사항이 붙어서 표현이 된다.
합칠경우 관계가 있는거만 합치자 공지사항 등록한 사람만 서로 관계가 있는사람들은 INNER 관계가 없는 레코드들은 OUTER이다.
관계가 있는 것만 합치는 것을 INNER JOIN이라ㅓ고 한다.
참조키를 기준으로 일치하는 행만 조인
관계가 깨져있는 경우? ->관계가 있는 것만 한다.
양쪽다 OUTER를 제거하고 JOIN한다.

근데 이 JOIN방법은 오라클의 것이 아닌 ANSI SQL에서 하는 표준 JOIN문장이다.

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 N.WRITER_ID = M.ID;
우측테이블의 OUTER를 없애고 합쳐진다. 값이 없는 부분들은 NULL로 채운다. INNER JOIN에 OUTER JOIN이 늘어나게되는것임.

RIGHT OUTER JOIN시 레코드의 개수는
왼쪽 관계 1 무관계3 오른쪽테이블 관계3 무관계2-> 왼쪽 무관계없어지고 1을 복사하고 나머지는 NULL을 넣은 5개
그러면 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.WRITER_ID 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.WRITER_ID GROUP BY M.ID, M.NAME
항상 주인공이 존재하고 그것을 중심으로 INNER 인지 OUTER인지를 해보자.

37. SELF JOIN

자기조인 데이터가 서로 포함관계를 가지는 경우 : 담당 구성 연락
자기가 자기와 합쳐지는 것임 자기가 자기를 합칠 이유가잇을까 한개의 테이블이 여러개 처럼 될 수 잇을가
사원테이블 에서 홍길동을 찾앗다해보자
홍길동-강호동
-유재석
이럴때 부서원이라는 관계가 있다고 생각해보자. 강호동의 부서장을 출력하거나 홍길동의 부서원을 출력하거나 이럴때 자기가 자신을 조인해서 사용할 수 있다.
관계명? 참조컬럼을 하나더 추가해서 해야한다.

사진참조
그냥 JOIN하면 오류가 발생하니 별칭을 각각 붙여준다.
SELECT M.*, B.NAME BOSS_NAME FROM MEMBER M LEFT OUTER JOIN MEMBER B ON B.ID = M.BOSS_ID;
사원은 다나와야하니 LEFT조인
셀프조인이 많이 사용되나? 의외로 많이 사용됨 댓글이 댓글을 참조할때 대댓글 같은거

38. 오라클 OLD JOIN

지금까지 ANSI JOIN의 표준 것이엇다.
오라클의 조인을 보자.
오라클 INNER JOIN 조인하고자하는것을 그냥 ','로 사용하면된다.
원래 ON으로 구분했는데 WHRER절로 구분을 해서 헷갈려진다.
SELECT N.ID, N.TITLE, M.NAME FROM MEMBER M, NOICE N WHERE M.ID = N.WRITER_ID
추가적인 필터링하려면 AND M.ID = 'newlec'이런식으로 추가해야한다.
오라클 OUTER JOIN
SELECT N.*, M.NAME FROM NOTICE N, MEMBER M WHERE N.WRITER_ID = M.ID(+)
NULL값을 오른쪽에 존재한다는 것임.
FULL OUTER JOIN은 지원하지 않는다.
ANSI CROSS JOIN 관계있다없다와 관계없이 곱하기로 테이블을 만드는것 자주 사용되지는 않는다.
SELECT N.*, M.NAME FROM NOTICE N CROSS JOIN MEMBER M;
오라클 CROSS JOIN
SELECT N.*, M.NAME FROM NOTICE N, MEMBER M;
오라클 조인문장 쓰라는 것이 아니라 과거 문장이 있을 경우 이해하도록 배운 것이다.

39. 유니온(UNION)

JOIN처럼 합치는 것이다. 그러나 JOIN은 컬럼을 합쳣다.
UNION은 레코드를 합치는 것이다. 관련이 없어도된다. 컬럼의 개수만 맞춰주면 레코드를 합칠 수 잇다.
사진참조
어디에 사용하나? 게시판이 3종류인데 별도의 테이블을 만들었다. 합쳐서 통합검색을 할 경우가 있다. 이럴때 사용한다. A조건 결과물 B조건 결과물 등을 일일히합쳐서 만들 수 있다.
SELECT ID, NAME FROM MEMBER;
UNION
SELECT WRITER_ID, TITLE FROM NOTICE;

유니온이라는 것이 사실 두개레코드를 합치는 것뿐만이 아니다.
UNION 같은 내용이면 하나로 합친다. 완전이 데이터가 일치하면? 1테이블 123 2테이블 124면 1234로 4개만 나온다.
MINUS 1테이블 123 2테이블 124일때 2테이블을 기준으로 뺀다. -> 3만남음
INTERSECT 공통분모만 남김 1테이블 123 2테이블 124일때 1,2만남음
UNION ALL 그냥 상관없이 다 합침

그런데 보통 유니온은 하나의 테이블로도 할 수 있다.
SELECT ID, NAME FROM MEMBER WHERE ID LIKE '%n%';
UNION
SELECT ID, NAME FROM MEMBER WHERE ID LIKE '%g%';
필터링한 후 또 필터링 하고싶을때 두개의 결과집합으로 연산하고 싶을때 사용한다.

40. view만들기

view를 통해서 보는 풍경 한정된 부분을 본다.
물리적인 데이터구조(테이블)과 개념적인 데이터구조 view차이 중복을 제거하고 나누어서 저장했다.
매번 같이 보는것은 힘들다. 이것을 합쳐놓고 싶다. 이때 사용되는게 view이다.
합쳐놓는 쿼리를 매번 적는게 너무 어렵다.
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 이걸 응용해서사용할 수 있다.

41.데이터 딕셔너리

오라클 DBMS 사용자정보 권한 테이블/부 등을 저장할수있는 공간이 잇다. 이것들이 데이터 딕셔너리이다.
이정보를 가지고 얻어낼 수 있는것?
요즘에는 잘 사용안한다. 윈도우 형태이기때문에 클릭으로 보면된다.
뷰형태로 보여준다. 하나의 테이블인데도 뷰로 볼필요가 있나? 데이터 딕셔너리는 권한밖을 안보여주기 위해서 뷰를 사용하는 것이다.
조회용으로 읽기 용으로 사용하는데도 효과적인 도구이다.
SELECT * FROM DICT;

DBA_ { TABLES
ALL_ { TAB_COLUMNS 등등
USER_ {

과거에는 CMD 콘솔기반으로 사용해서 테이블을 보기위해서 데이터 딕셔너리로 봣었다. 사실 최근에는 사용하지 않는다.
있다 이 도구가 보여주고 잇다.

42. 도메인 제약조건

ID가 다 똑같으면 중복이 되서 식별을 할 수 없으니 안된다.
제약이란 것이 필요하다.
아무런 데이터가 들어갈수 없도록 제약을 걸고 무결성이 위배되지 않도록 해야한다.
도메인
유요한 값의 범위
컬럼에다가 데이터를 입력한다. 컬럼이란 각각 유효한 값의 형식이 있다.
EX) 학번 0보다 큰정수 이름 20자 내의 문자
어떤 컬럼도 도메인을 가지고 잇고 유효한 범위 안에서 하도록 있는 것이다.
속성에 도메인이 아닌 값이 올 수 없도록 하는 제약 조건
NOT NULL/ DEFAULT/ CHECK
사용자가 전달하기 애매한것들 조회수 등등? 알아서 들어가야한다.
도메인 범위를 체크하는 CHECK도있다.

42.1 NOT NULL 제약조건

테이블을 생성할때 적용방법
CREATE TABLE TEST
(
ID VARHVAR2(50) NOT NULL,
);
이미 테이블이 있다면
ALTER TABLE TEST MODIFY EMAIL VARCHAR2(200) NOT NULL;
코드를 적지 않아도 도ㅓ구를 사용해서 할 수 도 있다.

42.2 DEFAULT 제약조건

PWE VARCHAR2(200) DEFAULT '111'
값을 안넣엇을때 기본으로 지정되는 값을 넣는다.
데이터결함을 없애기위해 제약조건을 잘 알아둘 필요가 있다.

43. 체크 제약조건

도메인제약조건의 가장 꽃이라고 할 수 있다.
CREATE TABLE TEST
(
ID VARCHAR2(50) NULL
PHONE VARCHAR2(200) CHECK(PHONE LIKE '010-%-____') NOT NULL,
EMAIL VARHCAR2(500) NULL

)
나중에 추가하고자한다면?
ALTER TABLE TEST ADD CONSTRAINT CK_TEST_PHONE CHECK(PHONE LIKE '010-%-____');
011111231 이런식으로 넣으면 오류발생한다.
편집-제약조건-새검사 제약조건
제약조건에 맞지않게 데이터를 넣으면 ORA-02290: 체크 제약조건(JAVA.MEMBER_PHONE_CHK1)이 위배되었습니다가 발생한다.

44. 정규식을 이용한 체크 제약조건

오라클의 기능만을 이용해서는 자세한 제약조건을 걸수 없다. 이를 해결하기 위해서는 정규표현식을 사용해야한다.
REGEXP_LIKE(PHONE. '01[01]-\d{3,4}-\d{4}')

제약조건을 명령어로 삭제하고 추가해보자
ALTER TABLE MEMBER
DROP CONSTRAINT MEMBER_PHONE_CHK1;
추가하기
ALTER TABLE MEMBER
ADD CONSTRAINT MEMBER_PHONE_CHK1 CHECK (REGEXP_LIKE(PHONE, '^01[01]-\d{3,4}-\d{4}$'));
전화번호는 딱 그거만 들어가니까 ^$ 해주기!

45.Entity 제약조건(Primary Key, Unique)

테이블 전체로 확대햇을때 정말 올바른게 들어가는가? 그것은 다를 수있다.
중복된 레코드가 없도록 제한해보자.
컬럼이 다 올바르다고해서 값이 다 올바른 것은 아니다. 중복된 데이터가 계속있다면 올바른 데이터가 아니라고 할 수 있다.
식별할 수 있는 무언가가 있어야한다. 이런 컬럼을 식별컬럼, 식별키를 가지고 있는 컬럼이라고 한다.
PRIMARY KEY 전체레코드들이 식별될 수 있는 절대 중복X NULLX
UNIQUE NULL허용 중복X 제약조건과 비슷하지만 좀 다르다.
테이블 처음 만들때
ID NUMBER PRIMARY KEY,해주면된다.
WRTIER_ID VARVHAR2(50) NOU NULL UNIQUE - NOT NULL/ UNIQUE
CREATE구문에 다넣으면 지저분해지는 경우가 있다. 그래서 따로 제약조건만 거는 경우도 있다.
CONSTRAINT NOICE_ID_PK PRIMARY KEY(ID),
CONSTRAINT NOICE_ID_UK UNIQUE(WRITER_ID),
이미 만들어져있다면? ALTER사용하자.
제약조건 - 새 고유제약조건

46.시퀀스(Sequence)

일련번호 일련된번호를 계속뽑아야하는 경우가 있다.
중복이 되면안되고 계속해서 증가해야한다.
쿼리식으로 만들어도되고 도구를 사용해도된다.
CREATE SEQUENCE NOTICE_ID_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 99999999999999999999999 MINVALUE 1;
캐시 자주일어날경우에 미리 뽑아 놓게하기
주기 1~MAX갓다가 정할경우
정렬 캐시데이터로 갈경우 정렬을 하겟다.
사진참조
시퀀스이름.NEXTVAL 을하면 추가된것을 알 수 있다.
SELECT 시퀀스이름.NEXTVAL FROM DUAL; 설렉트에서 사용해도 하나씩 사용되는것을 알 수 있다.

2023.01.11 후기

우매함의 봉우리에 도착했다.
또한 곧 학원이라는 점에서 불안감만 매우 높아지고 있다.
어딜보든 안좋은 얘기만 있을 뿐 결국 본인만 열심히하면 된다는데 그 열심히가 뭔지도 모르겠다.
우매함의 봉우리에서 수박겉핥기만 보고 할 수 있다고 생각되는 이 두려움에 갇혀버렸다.
진짜로 내가 해낼 수 있을까? 의문점만 계속 쌓인다.
잘 하고 싶다 길이 분명하지 않다 그래도 갈 길이 없다. 원래 길보단 나을 것이다라고 마음다짐만 계속 한다.
내가 언젠간 이 글을 다시 보았을때는 좀 더 나은 미래에서 보게 되면 좋겠다.
너무 온실속의 화초인 것 같다.