2023.01.09 DB

DB

15. 비교연산자(=,!=,^=,...)

데이터를 필터링 할수있는 비교연산자이다. 산술연산자로는 컬럼을 더해서 뽑을 수 있었다.
원하는 레코드만 뽑아서 사용하고 싶다.
그걸위해서 비교연산자와 관계연산자를 사용할 수 있다.
=, != ^=, <>, > ,<, >=, <+, IS NULL, IS NOT NULL
!= ^=, <> 다 같지 않다를 나타내는 연산자이다. 왜 똑같은게 3개나 잇나?
!=은오라클제외로 다썻엇는데 표준이아닌 표준이 되어버렷다.
<> 표준을 정의하자고 한 ASNI SQL에서 정한 것이다.
^=은 오라클에서 만든 같지않다이다.
연습 문제
게시글 중 작성자가 newlec인 게시글만 조회하시오.
SELECT * FROM NOTICE WHERE WRITER_ID = 'newlec';
게시글 중 조회수가 3이 넘는 글만 조회하시오.
SELECT * FROM NOTICE WHERE HIT >= 3;
게시글 중에서 내용을 입력하지 않은 게시글을 조회하시오.
SELECT * FROM NOTICE WHERE CONTENT IS NULL
= 'NULL'은 안된다. NULL이라는 문자가 들어가있을 수도 있기 때문이다.
DB에선 반드시 IS NULL을 사용해야한다.

16. 관계연산자(AND, OR, BETWEEN, IN)

연산자
NOT(부정연산자), AND, OR, BETWEEN, IN
조회수가 0,1,2 인 게시글 조회
SELECT * FROM NOTICE WHERE HIT = 0 OR HIT = 1 OR HIT = 2; 연속되지않을경우
-> SELECT * FROM NOTICE WHERE 0 <= HIT AND HIT <= 2; <=같은게 아닐경우
범위연산은 자주 사용한다 더 가볍게 사용
SELECT * FROM NOTICE WHERE HIT BETWEEN 0 AND 2;
조회수가 0,2,7인 게시글을 조회하시오.
SELECT * FROM NOTICE WHERE HIT = 0 OR HIT = 2 OR HIT = 7;
개수가 많아지면 비효율적이게 됨.
-> SELECT * FROM NOTICE WHERE HIT IN (0,2,7); 연속되지않은 값 또는 또는 또는 이면? 이걸로 사용
반대의 결과를 뽑고싶다.
SELECT * FROM NOTICE WHERE HIT NOT IN (0,2,7);

17. 패턴 비교 연산자(LIKE, %, _ )

LIKE, %, _
%가 애스터리스크 문자역할을 한다.
회원중에서 박씨 성을 조회하시오
SELECT * FROM NOTICE WHERE NAME = '박%';
그러나 = 연산자를 사용하면안된다.
SELECT * FROM NOTICE WHERE NAME LIKE '박%';
그래서 이런거같은 문자를 찾아라 라고 한다. 무조건 패턴을 검색할때는 LIKE를 사용해야한다.

회원중에 박씨 이고 이름이 외자인 회원을 조회
SELECT * FROM NOTICE WHERE NAME = '박'; 세글자면 박_
자릿수를 한정하고 싶다면 _을 하나씩 추가하면된다.

패턴연산자 연습문제
회원 중에서 박씨 성을 제외한 회원을 조회하시오
SELECT * FROM NOTICE WHERE NAME NOT LIKE '박%';
회원중에서 이름에 '도'자가 들어간 회원을 조회하시오.
SELECT * FROM NOTICE WHERE NAME LIKE '%도%';

18. 정규식을 이용한 패턴 비교(REGEX_LIKE)

오라클 제공하는 연산자가아닌 정규식이라는 사회표준에서 사용되는 것으로 검색해보자.
정규식이란? https://namu.wiki/w/%EC%A0%95%EA%B7%9C%20%ED%91%9C%ED%98%84%EC%8B%9D
문서내에서 패턴을 찾는것을 유닉스 버전에서 사용하는데 정해놓았다. 그러나 이정규식은 이제 어디에서나 쓰고 여기서도 함수를 제공한다.
https://regexlib.com/CheatSheet.aspx 정규식을 만드는 기호와 사람들이 만들어 놓은 정규표현식을 사용할수도있다.
SELECT * FROM NOTICE WHERE TITLE LIKE '^01[016-9]-\d{3,4}-\d{4}$';
--010-2222-3333
--011-333-4444
--016-234-4353
--017-444-5555
--01[016789] - [0-9] -대괄호안에 쓰면 한글자를 표현 또는또는
--01[016-9]-\d\d\d\d-\d\d\d\d // \d=[0-9]
-- 01[016-9]-\d{3,4}-\d{4} 중괄호는 {반복할 횟수} 숫자 두개이상넣으면 또는
-- ^01[016-9]-\d{3,4}-\d{4}$ 시작할때는 ^ 끝날때는 $을 넣어줘야한다.

정규표현식은 오라클에서 제공하는 문법이 아니기때문에 REGEXP_LIKE라는 특수한 의미의 함수를 사용해야한다.
SELECT * FROM NOTICE WHERE REGEXP_LIKE (COLUNM, ^표현식$);
SELECT * FROM NOTICE WHERE REGEXP_LIKE (TITLE,'^01[016-9]-\d{3,4}-\d{4}$');
그러나 검색안된다 ^ $ 패턴이 시작해서 패턴이 끝나는 경우 딱 바로 010으로 시작해야 검색이 되는 것이다.
포함된것을 하고싶다면? SELECT * FROM NOTICE WHERE REGEXP_LIKE (TITLE,'01[016-9]-\d{3,4}-\d{4}');
^$을 없애면된다. 전화번호만 가지고 있다면 시작끝을 써줘야하는데 다른게 포함되어 있다면 시작끝을 없애자.

19. 문자열 비교를 위한 정규식(^\D\w+@...)

email정규식을 작성해보자
com net org 만있다고 가정해보자
@ .org net com
[0-9a-zA-Z] = \w w 길이제한x 하나이상와야한다.
-> * + ? 0또는 1 혹은 0이상
\w+@\w+.[org net com] -> 이러면 o r g n e t 각각이게 됨
\w+@\w+.(org|net|com) |이또는 이됨 ()감싸서 하나의 문자를 고르게 만들자.
그런데 이렇게하면 숫자로 시작하는 도메인이 허용되게된다. 이걸막고자한다면 어떻게 해야할까?
\D를넣으면 [^0-9] 부정연산자가 된다.
\D\w_@\D\w_.(org|net|com) 뒤로는 가능한데 앞으로는 불가능하게 만들 수 있다.

20. ROWNUM 그리고 행 제한하기

회원목록에서 상위 5명만 조회하시오.
그런데 무엇을 기준으로 뽑을까? 순차적으로 뽑고자하면 정리가 안된거는 뽑을 수 없다.
오라클에서 결과집합을 만들때 우리가 몰랏지만 ROWNUM이 자동으로 추가가 된다.

이 번호를 비교하면 일련번호가 연속되기때문에 나눠서 볼 수 있다.
1페이지
SELECT * FROM NOTICE WHERE ROWNUM BETWEEN 1 AND 5;
2페이지는?
SELECT * FROM NOTICE WHERE ROWNUM BETWEEN 6 AND 10;
아무것도 안나옴? 왜그럴까? 2부터해도 안나온다.
WHERE ROWNUM >= 1 ROWNUM은 결과집합을 만들때 만들어지는 것이다.
SELECT에서 WHERE절을 할때 만들어지는 것이다.
ROWNUM 만들어지고 레코드한개만들어지고 ROWNUM 만들어지고 레코드한게 만들어지고 라서
ROWNUM 1부터 만들어지기때문에 WHERE절 가져오면? 1 ROWNUM >=5가아니네? 삭제 1 ROWNUM >=5가 아니네?삭제 방식으로 가서 아무것도 안나오게됨.
그럼 미리 만들어놓은 결과집합으로 사용하면?
미리 결과집합으로 ROWNUM을 만들고 설렉트를 하자.
SELECT * FROM ()
WHERE NUM BETWEEN 6 AND 10;
->SELECT * FROM (SELECT ROWNUM NUM, MEMBER.* FROM MEMBER) WHERE NUM BETWEEN 6 AND 10;
1.멤버가 가진 컬럼에 로우넘버를 붙이고 2.거기서 로우넘버를 비교하기
원래 NOTICE.ID 이런식으로 사용해야하는데 FROM절에 있으니 안해도 됫었다. NOTICE.로하면 다른게 곁다리로 들어갈 수 있게 된다.
SELECT * FROM (SELECT ROWNUM NUM, NOTICE. FROM NOTICE) WHERE NUM BETWEEN 6 AND 10;
이것은 더이상 NOTICE가 아닌 NOITCE를 활용한 결과집합이 되는 것이다.
그냥 ROWNUM을 쓰면 만들어놓은 ROWNUM을쓰는게아니라 새로 붙여서 사용하려하니 별칭을 꼭 붙여주자.

21. 중복 값 제거하기 DISTINCT

중복된 값을 제거 DISTINCT
SELECT AGE FROM MEMBER; 중복되는 나이없이만 뽑고싶다.
SELECT DISTINCT AGE FROM MEMBER;
다른 목록과는 사용할 수 없다. 하나의 열만 보여주기 때문.

22. 중간요약과 함수 단원 안내

간단한 요약 정리

더 나아가면 컬럼에다가 연산하는 것을 함수로 더 다양하게 할 수 있다. 함수 하나하나 하면 시간이 너무 많이 든다.
오라클 내장된 함수는 살펴보는 것은 필요하다. 오라클 교재에 다양한 것이 있다. 함수를 나중에 사용해보고자 하고 온라인에 튜토리얼을 검색해보자.
내장함수 6개 정도 사용해보자.

23. 문자열 내장 함수 #1

컬럼을 원하는 것을 꺼내오거나 필터링해서 격자로 가져올수 있엇다. 더 디테일하게 뽑아오고자하면 함수를 사용해야한다.
문자열, 숫자, 날짜, 변환, NULL관련, 집계함수가 있다.

23.1 문자열 추출 함수 SUBSTR(문자열, 시작위치, 길이)

SELECT SUBSTR('HELLO', 1 ,3) FROM DUAL; HEL
SELECT SUBSTR('HELLO',3) FROM DUAL; LLO 3번째부터 잘라줘
SELECT SUBSTRB('HELLO',3) FROM DUAL; 바이트 단위로 자르기 -> LLO 한글은 한글자가 3바이트니 애매하게 잘리게되느것
많이 사용되는 곳 모든 학생의 이름과 출생월만을 조회하시오.
월만 따로 하는 컬럼이 존재하지 않으니 날짜를 자르면된다.
SELECT NAME, SUBSTR(BIRTHDAY, 6 ,2) FROM STUDENT; 1998.01.05
회원중에서 전화번호가 011으로 싲가하는 회원의 모든정보를 출력
SELECT * FROM MEMBER WHERE SUBSTR(PHONE, 1 ,3) = '011';
WHERE절에 SUBSTR을 넣을 수도 있다. 쿼리를 잘이해햐아한다. 뽑아낼 내용이 무엇인지 조건이 필요한지. 행을 필터링해야하는지 컬럼을 필터링해야하는지.
그러나 함수가 WHERE절에 들어가면 전체 테이블이 1억개면 1억개의 함수가 실행되서 비효율적이게 된다.
SELECT * FROM MEMBER WHERE PHONE LIKE '011%'; 대체할게 있다면 대체하는 것으로 사용하자.
함수 연습 문제
1.회원중에서 생년월이 7,8,9인 회원의 모든 정보를 출력하시요.
SELECT * FROM MEMBER WHERE SUBSTR(BIRTHDAY, 6 ,2) IN (7,8,9);
-> 정답 SELECT * FROM MEMBER WHERE SUBSTR(BIRTHDAY, 6 ,2) IN ('07','08','09');
문자열로 잘라서 가져오니 문자열로 비교를 해야한다!!
2.전화번호를 등록하지 않은 회원중에서 생년월일이 789인 회원의 모든정보를 출력
SELECT * FROM MEMBER WHERE PHONE IS NULL AND SUBSTR(BIRTHDAY, 6 ,2) IN ('07','08','09');

23.2 SUBSTR의 반대 문자열 덧셈 함수

SELECT CONCAT('홍','길동' FROM DUAL;
문자열연산
SELECT 3 || '4' FROM DUAL 성능면에선 이게 훨씬빠르니 이거 사용하는게 대부분이다.

23.3 문자열 트림함수

빈공백을 없애는 함수
SELECT LTRIM (' HELLO ') FROEM DUAL; 왼쪽공백
SELECT RTRIM (' HELLO ') FROEM DUAL; 우측공백
SELECT TRIM (' HELLO ') FROEM DUAL; 공백 다

23.4 문자열 솜누자또는 댐누자로 변경하기

SELECT LOWER | UPPER('NeWLEC') 전자는 소문자로 후자는 대문자로 바꿔준다. newlec NEWLEC
이걸 왜쓰나?
회원의 아이디가 newlec인 회원을 조회하시오 단 소/대문자를 가리지 않음.
SELECT * FROM MEMBER WHERE UPPER(MID) = 'NEWLEC'; 컬럼 전체를 대소문자 가리지 않게 만들어줌 UPPER('NEWLEC')해도 상관없음.

23.5 문자열 대치 함수 REPLACE(문자열, 찾는 문자열, 대치할 문자열) / TRANSLATE()

SELECT REPLACE('WHERE WE ARE', 'WE', 'YOU') FROM DUAL; ->WHERE YOU ARE
SELECT TRANSLATE('WHERE WE ARE', 'WE', 'YOU') FROM DUAL; ->YHORO YO ARO
W는 Y로 E는 O로 바꿔주는 것임. 각각 나눠져셔 각각이 대치 된다.
회원의 이름과 주소를 조회하시오.(단 주소는 빈칸없이 출력하시오.)
SELECT NAME, REPLACE(ADDRESS,' ', '') FROM MEMBER;

24.문자열 내장 함수 #2

데이터는 우리가 사용하는 틀,격자안에 들어가잇다. 격자에 들어갓다는 것은 개념상이지 어떻게 분리해서 저장하는지는 알수 없다.
ROW를 필터링해달라거나 컬럼을 필터링해달라고 할 수 있다. 함수를 사용하면 더 다양하게 뽑을 수 있다.
이름은 몇 자인가? 성은 제외하고 일므만 어떻게 되는가? 이름이 외자인경우 뒤에 자를 붙일 것, 이름에 빈공백은 제거하고 출력할 것. 영문이름인 경우 첫글자는 소문자로
등등 자바같은 프로그램 언어로 조작을 해야한다. 이런것들을 함수로 다양하게 뽑을 수 있다.

24.1 문자열 패딩함수

SELECT LPAD('HELLO', 5) FROM DUAL;
5자가 안되면 5자를 채워준다..
SELECT LPAD('HELLO', 10, '0') FROM DUAL;
10개의 너비로 뽑아야하는데 모자라니 0으로 채워준다. -> 00000HELLO
LPAD이니 왼쪽에 채워줌 RPAD 오른쪽으로 채워줌
PAD는 너비로 고정을 하고 싶을때 사용한다.
회원의 이름을 조회하는데 이름의 길이가 3자가 안되는 경우 오른쪽을 밑줄 _ 로 채우시오
SELECT RPAD(NAME, 3, '_') FROM MEMBERS -> 글자수가아니라 바이트 기준이다. 한글일경우 .5가 나올수 없어서 한글자만 나온다.
한글이냐 영문이냐 에따라 잘 사용하자.

24.2 첫문자를 대문자로 바꿔주는 함수 INITCAP(문자열)

SELECT INITCAP('the ..') FROM DUAL;
SELECT INITCAP('the most important thing is ...') FROM DUAL; 과연 긴문자열일때 어디만 대문자가 될까?
-> The Most Important Thing Is 다 된다. 중간에 한글이 들어가도 한글 다음거도 대문자가 된다.

24.3 문자열 검색함수 INSTR(문자열, 검색문자열, 위치, 찾을 수)

SELECT INSTR('ALL WE NEED TO IS JUST TO...', 'TO') FROM DUAL;
SELECT INSTR('ALL WE NEED TO IS JUST TO...', 'TO', 15) FROM DUAL; 시작위치 지정 위치를 알아야만 사용가능함.
SELECT INSTR('ALL WE NEED TO IS JUST TO...', 'TO', 1, 2) FROM DUAL; 4번째인자는 순번이다. 첫번째부터 시작해서 2번쩨 TO의 위치를 달라.
회원 전화번호에서 두번째 대시가 문자가 존재하는 위치 출력
SELECT INSTR(PHONE, '-', 1, 2) FROM MEMBER;
회원 전화번호에서 첫번째 대시 문자와 두번째 대시 문자 사이의 간격은? ->가운데 숫자가 몇개인지를 해봐라
SELECT INSTR(PHONE, '-', 1, 2) - INSTR(PHONE, '-', 1, 1) - 1 FROM MEMBER;
-> 가운데 숫자가 몇개인지 알수잇게된다.
회원의 전화번호에서 첫번째와 두번째 사이의 국번을 출력하시오.
SELECT SUBSTR(PHONE, 5, INSTR(PHONE, '-', 1, 2) - INSTR(PHONE, '-', 1, 1) - 1) FROM MEMBER;
함수는 중복이 될 수 있다.

24.4 문자열 길이를 얻는 함수

SELECT PHONE, LENGTH(PHONE) FROM MEMBER;
함수 중첩 만약에 회원의 전화번호 컬러멩 포함된 문자'-'를 없앤 전화번호의 길이를 출력하시오
SELECT LENGTH(REPLACE(PHONE,'-','') FROM MEMBER;

24.5 코드값을 변환하는 함수

SELECT ASCII('A') FROM DUAL;

24.6코드값으로 문자열을 반환하는 함수

SELECT CHR(65) FROM DUAL;

25. 숫자 내장 함수(ABS/SIGN/ROUND/TRUNC/CEIL/FLOOR)

25.1 절대값을 구하는 함수 ABS(n)

SELECT ABS(35), ABS(-35) FROM DUAL; -> 35 / 35

25.2 음수 양수 를 알려주는 함수 SIGN(n)

SELECT SIGN(35), SIGN(-35), SIGN(0) FROM DUAL; -? 1 / -1 / 0

25.3 숫자의 반올림 값으르 알려주는 함수 ROUND(n,1)

SELECT ROUND(34.456789) FROM DUAL; -> 34  
SELECT ROUND(34.456789 , 2) FROM DUAL; -> 34.46  
몇째짜리에서 반올림할지 정할 수 있엇음. 사용할때마다 필요할때마다 검색해서 사용법 알아보자.

25.4 숫자의 나머지 값을 반환하는 함수 MOD(n1, n2)

SELECT TRUNC(17/5) 몫, MOD(17,5) 나머지 FROM DUAL; -> 3 / 2

25.5 숫자의 제곱을 구하는 함수와 제곱근을 구하는 함수 POWER(n1, n2) / SQRT(n)

SELECT POWER(5,2) SQRT(25) FROM DUAL; -> 25 / 5

31.날짜 함수

32. 1 현재 시간을 얻은 함수
    SELECT SYSDATE(연월일시분초), SYSTIMESTAMP(시분초에 추가적으로 밀리세컨드) , CURRENT_DATE, CURRENT_TIMESTAMP
    SYS는 현재 오라클 서버가 설치되어잇는 위치 , CURRENT사용자의 기반으로 접속자의 시간설정 별도로 전달할 파라미터가 없어서 괄호를 안쓴다.
33. 2 세션시간SYSDATE
    ALTER SESSION SET TIME_ZONE = '-1:0' 한국 '09:00' LA '-08:00' 그리니치 천문대 기준으로 타임존이 설정되어있음.
34. 3 포맷변경
    SYSDATE 시분초가 안나옴? 포맷을 설정해주자.
    ALTER SESSION SET NLS_DATE_FORMAT = "YYYY-MM-DD HH24:MI:SS"
35. 4 날짜 추출함수 EXTRACT(YEAR/MONTH/DAY/HOUR/MIMUTE/SECOND FROM ...)
    SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
    가입회원중에서 비수기(2,3,11,12)월 달에 가입한 회원을 조회하시오.
    SELECT * FROM MEMBER WHERE EXTRACT(MONTH FROM REGDATE) IN (2,3,11,12);
36. 5 날짜를 누적하는 함수 ADD_MONTH(날짜, 정수)
    SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;
    가입 회원 중에 가입한지 6개월이 안되는 회원을 조회하시오.
    SELECT * FROM MEMBER WHERE ADD_MONTHS(SYSDATE, -6) < REGDATE;
37. 6 날짜의 차이를 알아내는 함수 MONTHS_BETWEEN(날짜, 날짜)
    SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2013-12-25')) FROM DUAL;
    위의걸 변형해보자.
    SELECT * FROM MEMBER WHERE MONTHS_BETWEEN(SYSDATE, REGDATE) < 6;
38. 7 다음 요일을 알려주는 함수 NEXT_DAY(현재날짜, 다음요일)
    SELECT NEXT_DAY(SYSDATE,'토') FROM DUAL; 돌아오는 토요일이 언제인가? 토요일도 가능 숫자로도 가능 1일요일~7토요일
39. 8 월의 마지막 일자를 알려주는 함수 LAST_DAY(날짜)
    SELECT LAST_DAY(SYSDATE) FROM DUAL;
40. 9 지정된 범위에서 날짜를 반올림하는 / 자르는 함수 ROUND TRUNC(날짜, 포맷)
    숫자에서 사용한 것을 또 사용할 수 잇다.
    SELECT ROUND(SYSDATE, 'CC'), TRUNC(SYSDATE, 'CC') FROM DUAL; 'CC'->세기 'Q'->분기 등등이있다.
    SELECT ROUND(TO_DATE('2051-02-03'), 'CC'), TRUNC(TO_DATE('2051-02-03'), 'CC') FROM DUAL;

->2101-01-01 00:00:00 / 2001-01-01 00:00:00

27. 형식 변환 함수(TO_DATE/TO_CHAR/TO_NUMBER/TO_TIMESTAMP)
    숫자 문자열 날짜 서로 바꾸는 작업을 할 수 있다.
    TO_CHAR() / TO_DATE()
    숫자 <-> 문자열 <-> 날짜
    TO_NUMBER / TO_CHAR()
28. 1 NUMBER형식을 문자열(VARCHAR2)로 변환 TO_CHAR(NUMBER)
    SELECT TO_CHAR(12345678,'$99,999,999,999.99') FROM DUAL; 포맷 표현식은 숫자 길이보다 길어야한다.
    사진참조
    모자라면 빈칸으로 채워주는데 0을 붙이면 빈자리가 0으로 채워진다. 빈공백을 없애고 싶다면? 이전에 배운 TRIM으로 감싸면된다.
    DATE 형식을 문자열(VARCHAR2)로 변환 TO_CHAR(DATETIME)
    SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
    형태가 지정되어 있기때문에 원하는 포맷을 제공함으로써 원하는 형태로 문자열로 바꿀 수 있다.
    사진참조2
    문자열을 DATE 형식으로 변환하는 함수
    TO_DATE(문자열, 날짜 포맷) & TO_TIMESTAMP
    SELECT TO_DATE('1994-01-01', 'YYYY-MM-DD') FROM DUAL;
    포맷은 위의 목록과 같다.
    문자열을 숫자 형식으로 변환하는 함수 TO_NUMBER
    SELECT TO_NUMBER('1994) FROM DUAL;
    '2' + 3 하면 숫자로 계산되지만 TO_NUMBER('2') + 3 으로 해주는게 더 바람직한 표현방법이라고 할 수 잇다.