SELECT USER
FROM DUAL;
--○ TBL_EMP 테이블에서 모든 사원들의
-- 사원번호, 사원명, 급여, 커미션, 연봉 정보를 조회한다.
-- 단, 급여(SAL)는 매월 지급한다.
-- 수당(COMM)은 1회/년 지급한다.
ALTER TABLE TBL_EMP
ADD MONEY NUMBER(10);
ALTER TABLE TBL_EMP DROP COLUMN MONEY;
SELECT EMPNO 사원번호, ENAME 사원명, SAL 급여, COMM 커미션, (SAL*12)+(COMM) 연봉
FROM TBL_EMP;
--> 이와 같이 연봉을 연산하여 조회할 경우 수당(COMM)이 NULL인 직원들은 연봉까지도 NULL로 연산되는 것을
-- 확인 할 수 있다.
SELECT EMPNO 사원번호, ENAME 사원명, SAL 급여, COMM 커미션, (SAL*12) 연봉
FROM TBL_EMP;
--> 그렇다고 수당(COMM)을 연산 대상에서 제외시키거나
-- 0으로 가정하고 연산을 수행할 경우 수당(COMM)이 존재하는 직원들의 연산 결과가 잘못되어 처리된다.
-- ○NVL() 함수
SELECT NULL "①", NVL(NULL,0)"②",NVL(2,0)"③"
FROM DUAL;
--==>> (NULL) 0 2
--> 첫 번째 파라미터 값이 NULL 이면, 두 번째 파라미터 값을 적용한다.
-- 첫 번째 파라미터 값이 NULL 이 아니면, 그 값을 그대로 적용한다.
SELECT ENAME, NVL(COMM,0)
FROM TBL_EMP;
SELECT EMPNO 사원번호, ENAME 사원명, SAL 급여, NVL(COMM,0) 커미션, (SAL*12)+(NVL(COMM,0)) 연봉
FROM TBL_EMP;
--==>>
/*
7369 SMITH 800 0 9600
7499 ALLEN 1600 300 19500
7521 WARD 1250 500 15500
7566 JONES 2975 0 35700
7654 MARTIN 1250 1400 16400
7698 BLAKE 2850 0 34200
7782 CLARK 2450 0 29400
7788 SCOTT 3000 0 36000
7839 KING 5000 0 60000
7844 TURNER 1500 0 18000
7876 ADAMS 1100 0 13200
7900 JAMES 950 0 11400
7902 FORD 3000 0 36000
7934 MILLER 1300 0 15600
*/
--○ NVL2()
SELECT NVL2(NULL,10,20)"①" , NVL2(0,10,20)"②"
FROM DUAL;
--==>>20 10
/*
첫 번째 인자 값이 NULL이면 세 번째 인자 값을 반환하고
첫 번째 인자 값이 NULL이 아니면 두번째 인자 값을 반환한다.
즉 COMM이 NULL인 직원들은 10만큼 추가 수당을 지급
이라는 조건이 붙은 상황일때 사용한다.
*/
--○ TBL_EMP 테이블에서 COMM 컬럼의 값이 NULL 이 아니면 3000,
-- NULL이면 4000으로 특별 수당을 지급하는 상황이다.
-- 이에 대한 연산 결과를 조회할 수 있도록 한다.
-- 사원명 급여, 수당, 특별수당 항목을 조회한다.
SELECT ENAME "사원명", SAL "급여", NVL(COMM,0) "수당", NVL2(COMM,3000,4000) "특별수당"
FROM TBL_EMP;
/*
SMITH 800 0 4000
ALLEN 1600 300 3000
WARD 1250 500 3000
JONES 2975 0 4000
MARTIN 1250 1400 3000
BLAKE 2850 0 4000
CLARK 2450 0 4000
SCOTT 3000 0 4000
KING 5000 0 4000
TURNER 1500 0 3000
ADAMS 1100 0 4000
JAMES 950 0 4000
FORD 3000 0 4000
MILLER 1300 0 4000
*/
--○ TBL_EMP 테이블에서 모든 사원들의
-- 사원번호, 사원명, 급여, 커미션, 연봉 정보를 조회한다.
-- 단, 급여(SAL)는 매월 지급한다.
-- 수당(COMM)은 1회/년 지급한다.
-- 또한, NVL2() 함수를 활용하여 처리한다.
--○ NVL2()를 이용해서 연봉 구하기
-- 첫 번째 매개변수가 NULL이면 세번째 매개변수를 반환
-- 첫 번재 매개변수가 NULL이 아니면 두 번째 매개변수를 반환
SELECT EMPNO 사원번호, ENAME 사원명, SAL 급여, NVL(COMM,0) 커미션, (SAL*12)+(NVL2(COMM,COMM,0)) 연봉
FROM TBL_EMP;
/*
==========================================
■■■ NVL / NVL2 정리 ■■■
==========================================
-- NVL(매개변수1, 매개변수2)
매개변수1이 NULL이면 매개변수2를 반환
매개변수1이 NULL이 아니면 매개변수1을 반환
-- NVL2(매개변수1, 매개변수2, 매개변수3)
매개변수1이 NULL이면 매개변수3을 반환
매개변수1이 NULL이 아니면 매개변수2를 반환
*/
--○ COALESCE() NULL처리 함수
--> 맨 앞부터 차례로 값이 NULL 이 아니면 적용(반환)하고
-- NULL 인 경우에는 다음 파라미터를 확인하는 연산으로 넘어간다.
SELECT COALESCE(NULL,NULL,NULL,NULL,10)"첫 번째 컬럼"
, COALESCE(10,20,30)"두 번째 컬럼"
FROM DUAL;
-- ※모든 경우의 수를 확인할 수 있다.
-- TBL_EMP 테이블 데이터 수정
UPDATE TBL_EMP
SET SAL=NULL
WHERE EMPNO=7521;
--==>1행이 업데이트 되었습니다.
SELECT *
FROM TBL_EMP;
COMMIT;
--==>커밋 완료.
--○ TBL_EMP 테이블에서 연봉에 대한 연산 결과가 NULL 이 나오는 경우는
-- 급여(SAL)가 NULL, 혹은 수당(COMM)이 NULL, 또는 둘 다 NULL
-- 모든 경우를 고려하여 연봉을 조회할 수 있도록 쿼리문을 구성한다.
-- COALESECE() 함수를 사용하여 사원번호, 사원명, 급여, 수당, 연봉을 조회한다.
-- 단 연봉은 급여*12+수당 으로 연산처리 한다.
SELECT EMPNO "사원번호", ENAME "사원명", COALESCE(SAL,0) "급여", COALESCE(COMM,0) "수당", COALESCE(SAL*12,0)+COALESCE(COMM,0) "연봉"
FROM TBL_EMP;
SELECT EMPNO "사원번호", ENAME "사원명", COALESCE(SAL,0) "급여", COALESCE(COMM,0) "수당", COALESCE(SAL*12+COMM,SAL*12,COMM,0) "연봉"
FROM TBL_EMP;
--둘 중에 하나가 NULL이라고 가질 수 있는 모든 경우의 수를 생각하여
--하나씩 경우의 수를 풀어나간다.
--즉 SAL*12가 NULL일 경우 / COMM이 NULL일 경우 / 둘다 NULL일 경우를 생각!
-------------------------------------------------------------------------------
--※ 날짜에 대한 세션 설정 변경
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
--○ 현재 시간(날짜)을 조회하는 함수
SELECT SYSDATE, CURRENT_DATE, LOCALTIMESTAMP
FROM DUAL;
--사용 빈도는 SYSDATE가 가장 높음
--==>>2018-01-09 10:05:01
--○ 컬럼과 컬럼의 연결(『||』)
SELECT '김선규'||'생선대갈'
FROM DUAL;
SELECT '김선규'||10||SYSDATE
FROM DUAL;
--==>>김선규102018-01-09 10:11:15
--내부적으로 오라클이 형변환을 문자열로 변환해서 문자열끼리 이어주고 출력해줌
--즉 문자열 || 숫자 || 날짜 를
-- 문자열 || 문자열 || 문자열 타입으로 변환하여 컬럼끼리 연결해줌
--※ 오라클에서는 문자열로 형 변환의 과정 없이
-- 『||』만 넣어주면 간단히 컬럼과 컬럼을 연결하는 것이 가능하다.
-- MSSQL 에서는 모든 문자열을 CONVERT 해야한다.
SELECT *
FROM TBL_EMP;
--○ TBL_EMP 테이블의 정보를 활용하여
-- EX) SMITH의 현재 연봉은 9600인데 희망 연봉은 19200이다.
-- ALLEN의 현재 연봉은 19500인데 희망 연봉은 39000이다.
-- 이와 같은 형식으로 조회할 수 있도록 한다.
SELECT ENAME||'의 현재 연봉은'||COALESCE(SAL*12+COMM,SAL*12,COMM,0)||'인데 희망 연봉은'||COALESCE(SAL*12+COMM,SAL*12,COMM,0)*2||'이다.'
FROM TBL_EMP;
DESC TBL_EMP;
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
SELECT ENAME, HIREDATE
FROM TBL_EMP;
SELECT ENAME||'의 입사일은 '||HIREDATE||'입니다. 그리고 급여는 '||NVL(SAL,0)||'입니다.'
FROM TBL_EMP;
/*
SMITH의 입사일은 1980-12-17입니다. 그리고 급여는 800입니다.
ALLEN의 입사일은 1981-02-20입니다. 그리고 급여는 1600입니다.
WARD의 입사일은 1981-02-22입니다. 그리고 급여는 0입니다.
JONES의 입사일은 1981-04-02입니다. 그리고 급여는 2975입니다.
MARTIN의 입사일은 1981-09-28입니다. 그리고 급여는 1250입니다.
BLAKE의 입사일은 1981-05-01입니다. 그리고 급여는 2850입니다.
CLARK의 입사일은 1981-06-09입니다. 그리고 급여는 2450입니다.
SCOTT의 입사일은 1987-07-13입니다. 그리고 급여는 3000입니다.
KING의 입사일은 1981-11-17입니다. 그리고 급여는 5000입니다.
TURNER의 입사일은 1981-09-08입니다. 그리고 급여는 1500입니다.
ADAMS의 입사일은 1987-07-13입니다. 그리고 급여는 1100입니다.
JAMES의 입사일은 1981-12-03입니다. 그리고 급여는 950입니다.
FORD의 입사일은 1981-12-03입니다. 그리고 급여는 3000입니다.
MILLER의 입사일은 1982-01-23입니다. 그리고 급여는 1300입니다.
*/
/*
SMITH'S 입사일은 1980-12-17입니다. 그리고 급여는 800입니다.
ALLEN'S 입사일은 1981-02-20입니다. 그리고 급여는 1600입니다.
WARD'S 입사일은 1981-02-22입니다. 그리고 급여는 0입니다.
:
와 같이 출력하라.
*/
SELECT ENAME||'''s 입사일은 '||HIREDATE||'입니다. 그리고 급여는 '||NVL(SAL,0)||'입니다.'
FROM TBL_EMP;
--※ 오라클은 문자열 영역 안에서 『'』(홑따옴표) 두개가 하나를 표현하는 구조로 기능한다.
--------------------------------------------------------------------------------
--○ 오라클에서 데이터의 값 만큼은 반드시 대소문자 구분을 엄격하게 처리한다.
SELECT EMPnO, EnAME, joB
FROM Tbl_EmP
WHERE job='CLERK';
/*
7369 SMITH CLERK
7876 ADAMS CLERK
7900 JAMES CLERK
7934 MILLER CLERK
*/
SELECT EMPnO, EnAME, joB
FROM Tbl_EmP
WHERE job='CLerk';
--==>>조회 결과 없음
--○ UPPER() , LOWER(), INITCAP() 첫글자만 대문자로 처리
SELECT 'oRAcLE',UPPER('oRAcLE'),LOWER('oRAcLE'),INITCAP('oRAcLE')
FROM DUAL;
--주로 WHERE조건절에서 사용되곤 한다
SELECT EMPnO, EnAME, joB
FROM Tbl_EmP
WHERE UPPER(job)=UPPER('CLerk');
SELECT EMPnO, EnAME, joB
FROM Tbl_EmP
WHERE LOWER(job)=LOWER('CLerk');
SELECT EMPnO, EnAME, joB
FROM Tbl_EmP
WHERE INITCAP(job)=INITCAP('CLerk');
-------------------------------------------------------------------------------
--○ TBL_EMP테이블에서 입사일이 1981년 9월 28일 입사한 직원의
-- 사원명, 직종명, 입사일 항목을 조회한다.
SELECT ENAME "사원명",JOB "직종명",HIREDATE "입사일"
FROM TBL_EMP
WHERE HIREDATE='1981-09-28';
--==>>MARTIN SALESMAN 1981-09-28
--상황에 따라 여건에 따라 형변환이 달라지기 때문에 믿으면 안된다
--따라서 이러한 쿼리문은 잘못된 것이다.
DESC TBL_EMP;
--==>>HIREDATE DATE
SELECT ENAME "사원명",JOB "직종명",HIREDATE "입사일"
FROM TBL_EMP
WHERE HIREDATE=TO_DATE('1981-09-28','YYYY-MM-DD');
--'1981-09-28' -> '1981-09-28'의 문자열
--TO_DATE('1981-09-28','YYYY-MM-DD')안에 넣어주면 -> 1981년 9월 28일이라는 날짜형
--'2018-01-09 10:47:10' -> '2018-01-09 10:47:10'의 문자열
-- TO_DATE('2018-01-09 10:47:10','YYYY-MM-DD HH24:MI;SS') -> 2018년 1월 9일 10시 47분 10초라는 날짜형
--○ TBL_EMP 테이블에서 입사잉ㄹ이 1981년 9월 28일 이후로 입사한 직원의
-- 사원번호 직원명 입사일 항목을 조회한다.
SELECT EMPNO, ENAME, HIREDATE
FROM TBL_EMP
WHERE HIREDATE>=TO_DATE('1981-09-28','YYYY-MM-DD');
--○ TBL_EMP 테이블에서 입사일이 1981년 4월 2일부터 1981년 9월 28일 사이에 입사한 직원의
-- 사원번호, 사원명, 입사일 항목을 조회한다.
SELECT EMPNO, ENAME, HIREDATE
FROM TBL_EMP
WHERE HIREDATE>=TO_DATE('1981-04-02','YYYY-MM-DD') AND HIREDATE<=TO_DATE('1981-09-28','YYYY-MM-DD');
--○ 컬럼명 BETWEEN a(조건) AND b(조건)
-- 숫자 / 문자 에도 통용된다!
SELECT EMPNO, ENAME, HIREDATE
FROM TBL_EMP
WHERE HIREDATE BETWEEN TO_DATE('1981-04-02','YYYY-MM-DD') AND TO_DATE('1981-09-28','YYYY-MM-DD');
/*
7566 JONES 1981-04-02
7654 MARTIN 1981-09-28
7698 BLAKE 1981-05-01
7782 CLARK 1981-06-09
7844 TURNER 1981-09-08
*/
SELECT *
FROM TBL_EMP
WHERE SAL BETWEEN 2000 AND 3000;
/*
7566 JONES MANAGER 7839 1981-04-02 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-07-13 3000 20
7902 FORD ANALYST 7566 1981-12-03 3000 20
*/
SELECT *
FROM TBL_EMP
WHERE ENAME BETWEEN 'C' AND 'S';
-->S까지만 검색을한다
-- 그래서 SCOTT등은 검색이 안됨. 이름이 S이면 거기까지 검색이된다
-- 왜냐하면 사전식배열이기 때문이다.
-- 내부적으로는 ASCII코드 기반으로 변환되기 때문에 대소문자도 주의해야한다.
/*
※ BETWEEN a AND b 는 날짜, 숫자, 문자 모두에 적용된다
하지만 문자일 경우 아스키 코드 순서를 따르기 때문에 대문자가 앞에 있고
소문자가 뒤에있다는 배열과정의 순서를 주의해야 한다.
또한, BETWEEN a AND b는 수행되는 시점에서 오라클 내부적으로는
부등호 연산자의 형태로 바뀌어 연산된다.
마지막으로 문자일 경우 사전식 배열과정의 순서를 주의해야한다.
*/
SELECT *
FROM TBL_EMP;
/*
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 KING PRESIDENT 1981-11-17 5000 10
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10
*/
--○ ASCII()
SELECT ASCII('A'), ASCII('Z'), ASCII('a'), ASCII('z')
FROM DUAL;
--==>>65 90 97 122
--○ TBL_EMP 테이블에서 직종이 SALESMAN과 CLERK인 사원의
-- 사원명 직종명, 급여 항목을 조회한다.
SELECT ENAME, JOB , SAL
FROM TBL_EMP
WHERE JOB ='SALESMAN' OR JOB='CLERK';
SELECT ENAME, JOB , SAL
FROM TBL_EMP
WHERE JOB IN('SALESMAN' ,'CLERK');
SELECT ENAME, JOB , SAL
FROM TBL_EMP
WHERE JOB =ANY('SALESMAN' ,'CLERK');
-- =ANY는 OR연산자와 같다.
-- =ALL은 AND연산자와 같음.
-------------------------------------------------------------------------------
--○ 테이블 생성(TBL_SAWON)
CREATE TABLE TBL_SAWON
( SANO NUMBER(4) PRIMARY KEY
, SANAME VARCHAR2(40)
, JUBUN CHAR(13)
, HIREDATE DATE DEFAULT SYSDATE --자동으로 현재 시간을 입력
, SAL NUMBER --길이를 명시하지 않으면 숫자표현의 최대값 사용
);
--CHAR를 단위를 쓰지 않고 표현하면 문자의 최소값 즉 1개만 사용
--CHAR에서 최대값을 쓰려면 CHAR(2000)이라 써야함
--즉 NUMBER와 CHAR 매개변수를 생략하면 의미가 반대가 된다.
--==>>Table TBL_SAWON이(가) 생성되었습니다.
--○데이터 입력
INSERT INTO TBL_SAWON VALUES(1001, '명소희', '9302272234567', TO_DATE('2017-03-02','YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1002, '임미영', '9310092234567', TO_DATE('2017-03-02','YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1003, '조태희', '9010271234567', TO_DATE('2017-03-02','YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1004, '김선규', '9202231234567', TO_DATE('2017-11-13','YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1005, '서운성', '9307151234567', TO_DATE('2017-11-13','YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1006, '박기범', '9002271234567', TO_DATE('2017-11-13','YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1007, '최진규', '9105071234567', TO_DATE('2017-11-13','YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1008, '김준협', '9212101234567', TO_DATE('2017-11-13','YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1009, '정승우', '9304211234567', TO_DATE('2017-11-13','YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1010, '오승우', '9112271234567', TO_DATE('2017-11-13','YYYY-MM-DD'), 4000000);
INSERT INTO TBL_SAWON VALUES(1011, '선동렬', '0212273234567', TO_DATE('2018-01-02','YYYY-MM-DD'), 2000000);
INSERT INTO TBL_SAWON VALUES(1012, '선우용녀', '0301024234567', TO_DATE('2018-01-02','YYYY-MM-DD'), 2000000);
INSERT INTO TBL_SAWON VALUES(1013, '선우선', '0405064234567', TO_DATE('2018-01-02','YYYY-MM-DD'), 2000000);
INSERT INTO TBL_SAWON VALUES(1014, '스윙스', '0505053234567', TO_DATE('2018-01-02','YYYY-MM-DD'), 2000000);
SELECT *
FROM TBL_SAWON;
COMMIT;
--==>>커밋 완료.
--○ TBL_SAWON 테이블에 존재하는 『최진규』사원의 정보를 조회한다.
SELECT *
FROM TBL_SAWON
WHERE SANAME='최진규';
--==>>1007 최진규 9105071234567 17/11/13 4000000
SELECT *
FROM TBL_SAWON
WHERE SANAME LIKE '최진규';
--※ LIKE 동사 : 좋아하다
-- 부사 : ~~처럼 / ~~와 같이
--※ WILD CHARACTER --> 『%』
-- 『LIKE』 키워드와 함께 사용되는 『%』 는 모든 글자를 의미하고
-- 『LIKE』 키워드와 함께 사용되는 『_』 는 아무 글자 1개를 의미한다.
--○ TBL_SAWON 테이블에서 성이 『김』씨인 사원들의
-- 사원명, 주민번호, 급여를 조회한다.
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME = '김';
--==>> 조회결과 없음
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME = '김__';
--> 이름이 김__인 사원을 찾아라 존재하지 않음!
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '김__';
--> 김 ㅇ ㅇ 이름이 총 3글자인 사원만 검색된다
--==>>김선규 9202231234567 4000000
--==>>김준협 9212101234567 4000000
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '김%';
--> 김 ~~ 인 김씨인 모든 사원이 검색된다(글자수 제한이 없음) 김 도 찾아짐
--==>>김선규 9202231234567 4000000
--==>>김준협 9212101234567 4000000
--○ TBL_SAWON 테이블에서 이름이 『승우』인 사원의
-- 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '%승우';
--○ TBL_SAWON 테이블에서 이름의 두 번째 글자가 『선』인 사원의
-- 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SANAME LIKE '_선%';
--○ TBL_SAWON 테이블에서 이름에서 성이 『선』인 사원의
-- 사원명, 주민번호, 급여 항목을 조회한다.
/*
=========================================================
■■■■ 결론 ■■■■
=========================================================
결론부터 말하면 불가능하다.
※ 데이터베이스 설계 시 성과 이름을 분리해서 처리해야 할 업무 계획이 있다면
테이블 구성(생성) 과정에서 성 컬럼과 이름 컬럼을 분리하여 구성해야 한다!
*/
--○ TBL_SAWON 테이블에서 여직원들의 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE JUBUN LIKE '______2%' OR JUBUN LIKE '______4%';
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SUBSTR(JUBUN,7,1) LIKE '2' OR SUBSTR(JUBUN,7,1) LIKE '4';
--○ 테이블 생성
CREATE TABLE TBL_WATCH
( WATCHNAME VARCHAR2(40)
, BIGO VARCHAR2(100)
);
--==>>Table TBL_WATCH이(가) 생성되었습니다.
INSERT INTO TBL_WATCH VALUES('금시계', '순금 99.9% 함유된 최고급 시계');
INSERT INTO TBL_WATCH VALUES('은시계', '고객 만족도 99.99점 획득 시계');
--==>>2 행 이(가) 삽입되었습니다
--○ 커밋
COMMIT;
UPDATE TBL_WATCH
SET BIGO='순금 99.99% 함유된 최고급 시계'
WHERE BIGO='순금 99.9% 함유된 최고급 시계';
--○ TBL_WATCH 테이블의 BIGO(비고) 컬럼에
-- 『99.99%』라는 글자가 들어있는 행의 정보를 조회한다.
SELECT *
FROM TBL_WATCH
WHERE BIGO LIKE'%99.99%%';
-->쿼리문에 사용한 모든 %를 WILD CHARACTER로 인식하기 때문에
-- 원하는 결과를 얻을 수 없는 상황이다.
-- 즉, BIGO 컬럼의 데이터 중 문자열에 99.99가 포함된 행의 정보 조회
SELECT *
FROM TBL_WATCH
WHERE BIGO LIKE '%99.99/%%' ESCAPE '/';
SELECT *
FROM TBL_WATCH
WHERE BIGO LIKE '%99.99#%%' ESCAPE '#';
--※ ESCAPE로 설정한 문자는 와일드 캐릭터에서 탈출시켜라!
-- 즉 탈출시키고 싶은 문자 앞에 붙인다!
-- 일반적으로 ESCAPE로 설정하는 한 문자는
-- 사용 빈도가 비교적 적은 특수문자로 설정한다!
--■■■ 정렬(ORDER BY) ■■■--
/*
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
순으로 파싱이 일어남
ORDER BY는 리소스 소모가 크므로 사용할 때 주의해야 한다.
ASC : 오름차순(DEFAULT 값이므로 생략가능)
DESC : 내림차순(생략 불가능)
*/
SELECT ENAME"사원명",DEPTNO"부서번호",JOB"직종",SAL"급여",
(SAL*12)+NVL(COMM,0)"연봉"
FROM EMP;
SELECT ENAME"사원명",DEPTNO"부서번호",JOB"직종",SAL"급여",
(SAL*12)+NVL(COMM,0)"연봉"
FROM EMP
ORDER BY DEPTNO ASC; -- 정렬 기준 컬럼 : DEPTNO(부서번호)
-- ASC : 오름차순(생략가능 DEFAULT 값)
SELECT ENAME"사원명",DEPTNO"부서번호",JOB"직종",SAL"급여",
(SAL*12)+NVL(COMM,0)"연봉"
FROM EMP
ORDER BY DEPTNO DESC; -- DESC : 내림차순
DESC EMP; --DESC는 위치가 어디에 쓰이는 지에 따라 다르게 사용된다.
SELECT ENAME"사원명",DEPTNO"부서번호",JOB"직종",SAL"급여",
(SAL*12)+NVL(COMM,0)"연봉"
FROM EMP
ORDER BY 부서번호 DESC; -- DESC : 내림차순
SELECT ENAME"사원명",DEPTNO"부서번호",JOB"직종",SAL"급여",
(SAL*12)+NVL(COMM,0)"연봉"
FROM EMP
ORDER BY 2 DESC; -- 2번째 컬럼을 의미(부서번호) 오라클은 1부터 시작
--※ 테이블을 구성하는 컬럼 순서가 아닌
-- SELCET문에서의 2번째 컬럼을 의미한다
--> ORDER BY절보다 SELECT가 먼저 실행되기 때문에
-- 컬럼명 대신 ALIAS(별칭)을 사용하는 것도 가능하고,
-- SELECT 되는 컬럼의 순서를 사용하는 것도 가능하다.
SELECT ENAME"사원명",DEPTNO"부서번호",JOB"직종",SAL"급여",
(SAL*12)+NVL(COMM,0)"연봉"
FROM EMP
ORDER BY 2,4;
--> 부서번호 내에서 급여가 오름차순 정렬
SELECT ENAME, DEPTNO, JOB,SAL
FROM EMP
ORDER BY 2,3,4 DESC;
--> DESC가 적용되는 것은 4번째 컬럼만 적용된다
--> 부서번호별 오름차순 -> 직종별 오름차순 -> 급여별 내림차순
--------------------------------------------------------------------------------
---■■■ 문자열 처리 함수 ■■■---
--------------------------------------------------------------------------------
--○ CONCAT()
--> 2개의 문자열을 연결하는 기능을 가진 함수(문자열 결합 함수)
-- 단, 오직 두 개만 연결할 수 있다.
SELECT ENAME||JOB , CONCAT(ENAME,JOB)
FROM EMP;
SELECT CONCAT(CONCAT(ENAME,JOB),SAL)
FROM EMP;
--> 파이프와 마찬가지로 내부적인 형 변환이 일어나서 연결시켜준다.
-- CONCAT()은 문자열과 문자열을 연결하는 기능을 가진 함수이지만,
-- 내부적으로 숫자나 날짜를 문자로 바꾸는 과정이 포함되어 있는 것이다.
--○ SUBSTR()
--> 문자열을 추출하는 기능을 가진 함수
-- 첫 번째 파라미터 값은 대상 문자열(추출의 대상)
-- 두 번째 파라미터 값은 추출을 시작하는 위치(1번째 인덱스부터 시작)
-- 세 번째 파라미터 값은 추출할 문자열의 갯수
-- 생략이 가능하며, 생략시 시작 위치부터 해당 문자열의 길이만큼! 추출한다)
SELECT ENAME, SUBSTR(ENAME,1,2)
FROM EMP;
--○ SUBSTRB()
-- 바이트로 끊어서 문자열을 추출하는 함수
-- 한글은 2바이트 이므로 주의해야한다!
-- 인코딩 방식에도 주의할 것!!
SELECT SANAME, SUBSTRB(SANAME,1,2), SUBSTRB(SANAME,1,3)
FROM TBL_SAWON;
SELECT PARAMETER, VALUE
FROM SYS.NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';
--==>>NLS_CHARACTERSET AL32UTF8
--기본적으로 사용하는 것은 UTF8
SELECT *
FROM TBL_SAWON;
--○ TBL_SAWON 테이블에서 남직원들의 사원명, 주민번호, 급여 항목을 조회한다.
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SUBSTR(JUBUN,7,1)='1' OR SUBSTR(JUBUN,7,1)='3';
--> SUBSTR은 문자열을 추출하는 것이므로 비교 대상도 문자열이여야지 정확하다!
--> 문자열 처리를 안해도 같은 값이 나오는 것은 자동 형변환때문
SELECT SANAME, JUBUN, SAL
FROM TBL_SAWON
WHERE SUBSTR(JUBUN,7,1) IN ('1','3');
--○ LENGTH(), LENGTHB()
SELECT LENGTH(ENAME), LENGTHB(ENAME)
FROM EMP;
--○ INSTR()
--> 첫 번재 파라미터 값에 해당하는 문자열에서(대상 문자열)
-- 두 번째 파라미터 값에서 정의한 문자열이 등장하는 위치를 검색
-- 세 번재 파라미터 값은 스캔을 시작하는 위치
-- 네 번째 파라미터 값은 몇 번째 등장하는 지를 결정(1일때는 생략가능)
SELECT INSTR('oracle orahome biora','ora',1,1) --첫문자부터에서 1번째 ora가 등장하는 위치
,INSTR('oracle orahome biora','ora',1,2) --첫문자부터에서 2번째 ora가 등장하는 위치
,INSTR('oracle orahome biora','ora',2,1) --두번째문자에서 1번째 ora가 등장하는 위치
,INSTR('oracle orahome biora','ora',2) --두번째문자에서 1번째 ora가 등장하는 위치(1은 생략 가능)
,INSTR('oracle orahome biora','ora',2,2) --두번째문자에서 2번째 ora가 등장하는 위치 문자열 처음부터 스캔한 값을 반환
FROM DUAL;
SELECT INSTR('나의 오라클 집으로오라 합니다','오라',1) --첫문자부터에서 1번째 ora가 등장하는 위치
,INSTR('나의 오라클 집으로오라 합니다','오라',2) --첫문자부터에서 2번째 ora가 등장하는 위치
,INSTR('나의 오라클 집으로오라 합니다','오라',10) --두번째문자에서 1번째 ora가 등장하는 위치
,INSTR('나의 오라클 집으로오라 합니다','오라',11) --두번째문자에서 1번째 ora가 등장하는 위치(1은 생략 가능)
,INSTR('나의 오라클 집으로오라 합니다','오라',12) --두번째문자에서 2번째 ora가 등장하는 위치 문자열 처음부터 스캔한 값을 반환
FROM DUAL;
--> 한글과 상관없이 문자열 위치 기반으로 반환
-- INSTAR('대상문자열','찾을문자열',검색시작위치,몇 번째 등장하는지)
--○ REVERSE()
-- 문자열을 거꾸로 처리한 결과값을 반환한다.
-- 단, 한글은 사용할 수 없다.
SELECT 'ORACLE', REVERSE('ORACLE')
FROM DUAL;
SELECT '오라클', REVERSE('오라클')
FROM DUAL;
-->비트 연산 기반이므로 한글은 처리가 되지 않음.
/*
테이블명 : TBL_FILES
--------------------------------------------------------------------------------
FILENO FILENAME
1 C:\AAA\BBB\CCC\SALES.XXLS
2 C:\AAA\STUDY.HWP
3 C:\RESEARCH\WORK\WORK.TXT
4 C:\TEST.PPTX
--------------------------------------------------------------------------------
조회 결과
파일 번호 파일명
1 SALES.XXLS
2 STUDY.HWP
3 WORK.TXT
4 TEST.PPTX
*/
CREATE TABLE TBL_FILES
( FILENO NUMBER
, FILENAME VARCHAR2(100)
);
INSERT INTO TBL_FILES VALUES(1,'C:\AAA\BBB\CCC\SALES.XXLS');
INSERT INTO TBL_FILES VALUES(2,'C:\AAA\STUDY.HWP');
INSERT INTO TBL_FILES VALUES(3,'C:\RESEARCH\WORK\WORK.TXT');
INSERT INTO TBL_FILES VALUES(4,'C:\TEST.PPTX');
COMMIT;
--내 풀이
SELECT FILENO "파일번호", REVERSE(SUBSTR(REVERSE(FILENAME),1,INSTR(REVERSE(FILENAME),'\',1)-1)) "파일 명"
FROM TBL_FILES;
SELECT FILENO "파일번호", REVERSE(FILENAME)"거꾸로된 파일명"
,REVERSE(SUBSTR(REVERSE(FILENAME), 1, INSTR(REVERSE(FILENAME),'\')-1))
FROM TBL_FILES;
--○ LPAD(), RPAD()
--> BYTE 수를 확보하여 왼쪽(오른쪽)부터 문자열로 채우는 기능을 가진 함수
SELECT 'ORACLE', LPAD('ORACLE',10,'*')
FROM DUAL;
--① 10BYTE 공간을 확보한다.( 두 번재 파라미터 값에 의해)
--② 확보한 공간에 'ORACLE'(첫번째 파라미터 값) 문자열을 담는다.
--③ 남아있는 BYTE 공간에 왼쪽부터(오른족부터) '*'(세번째 파라미터 값)로 채운다.
SELECT 'ORACLE', RPAD('ORACLE',10,'*')
FROM DUAL;
--==>>ORACLE ORACLE****
--○ LTRIM(), RTRIM()
SELECT 'ORAORAORACLEORACLE'
, LTRIM('ORAORAORACLEORACLE','ORA')
, LTRIM('ORAoRAORACLEORACLE','ORA')
, LTRIM('ORAARAORACLEORACLE','ORA')
, LTRIM('ORA ORAORACLEORACLE','ORA')
FROM DUAL;
--==>>ORAORAORACLEORACLE CLEORACLE oRAORACLEORACLE CLEORACLE ORAORACLEORACLE
--> 첫 번째 파라미터값에 해당하는 문자열을 대상으로
-- 왼쪽부터(오른쪽부터) 연속적으로 두 번재 인자값과
-- 같은 글자가 등장할 경우 제거한다.
-- 완성형으로 처리되지 않는다. O 또는 R 또는 A가 있으면 잘라낸다.
-- 실무적으로 공백을 제거할 대 많이 사용된다.
--○ TRANSLATE()
-- 1 : 1로 바꿔준다!!
-- 첫번째 매개변수에 대해 두번째 매개변수에 해당하는 순서에 맞게 세번째 매개변수로 바꿔준다(1:1)로
-- 두번재 매개변수에 존재하지 않으면 바꾸지 않는다.
SELECT TRANSLATE('MY ORACLE SERVER', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')
FROM DUAL;
--==>>my oracle server
SELECT '010-2546-5187', TRANSLATE('010-2546-5187','0123456789','공일이삼사오육칠팔구')
FROM DUAL;
--==>>010-2546-5187 공일공-이오사육-오일팔칠
--○ REPLACE()
-- TRANSLATE와 다르게 뭉텅이로 바꿔주는 구조
-- 일대일로 치환하지 않는다. 즉 완성형으로 처리
SELECT REPLACE('MY ORACLE SERVER','ORA','오라')
FROM DUAL;
-- 대소문자 구분을 명확히 하며, 완성형으로 처리된다.
-- 1 : 1로 사상되는 TRANSLATE() 함수와 비교해서 정리하자!
--■■■ 숫자형 함수 ■■■--
--○ ROUND()
--> 반올림을 처리하는 함수
-- 첫번째 인자값은 반올림할 숫자
-- 두번재 인자값은 표현할 자릿수!
--> 두번째 인자값이 2이면 소수점 2번째 자리까지 표현하라! -> 3번째에서 반올림
SELECT 48.678, ROUND(48.678,2)
,ROUND(48.678,0) --정수부까지 표현 0은 생략가능!!
,ROUND(48.678) --정수부까지 표현
,ROUND(48.678,-1) --10의자리까지 유효한 숫자를 표현
,ROUND(48.678,-2) --100의자리까지 유효한 숫자를 표현
FROM DUAL;
--○ TRUNC()
--> 절삭하는 함수
SELECT 48.678, TRUNC(48.678,2)
,TRUNC(48.678,0)
,TRUNC(48.678,-1)
FROM DUAL;
--○ MOD() 나머지 연산자(%)
SELECT MOD(5,2) --5를 2로 나눈 나머지
FROM DUAL;
--==>1
--○ POWER() 제곱(^)
SELECT POWER(5,3) --5^3
FROM DUAL;
--○ SQRT() 루트
SELECT SQRT(2) --ROOT 2
FROM DUAL;
--○ LOG() 로그 함수 --> 오라클은 상용로그만 지원
--MSSQL은 자연로그까지 지원
SELECT LOG(2,100)
FROM DUAL;
--○ 삼각함수
--SIN(), COS(), TAN()
SELECT SIN(1), COS(1), TAN(1)
FROM DUAL;
--○ 삼각함수의 역함수(범위 : -1 ~ 1 )
SELECT ASIN(0.5),ACOS(0.5),ATAN(0.5)
FROM DUAL;
--○ SIGN() 부호, 서명, 특징
--> 연산 처리에 대한 결과값이 양수이면 1, 0이면 0, 음수이면 01을 반환한다.
SELECT SIGN(5-2), SIGN(5-5), SIGN(2-5)
FROM DUAL;
--○ ASCII(), CHR()
-- CHR은 아스키코드값을 문자로 반환해줌
SELECT ASCII('A'), CHR(65)
FROM DUAL;
--==>>65 A
--결과값에서 오른쪽 정렬이면 숫자
--결과값에서 왼쪽 정렬이면 문자
--■■■ 날짜 연산 ■■■--
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
--==>>Session이(가) 변경되었습니다.
--※ 날짜연산의 기본단위는 DAY(일수)이다.
-- (2018-01-09 16:48:27) + 2 더하면 기본적으로 날자에 더해야한다
SELECT SYSDATE, SYSDATE+1 , SYSDATE-2
FROM DUAL;
--==>>2018-01-09 16:50:01 2018-01-10 16:49:40 2018-01-07 16:49:40
--시간 단위 연산
SELECT SYSDATE, SYSDATE+1/24, SYSDATE-2/24
FROM DUAL;
--==>>2018-01-09 16:50:41 2018-01-09 17:50:41 2018-01-09 14:50:41
--○ 문제
/*
현재 시간과, 1일 2시간 3분 4초 후를 조회(출력)한다.
---------------------------------------------------
현재 시간 | 연산 후 시간
2018-01-09 16:50:41 2018-01-10 18:53:40
*/
SELECT SYSDATE "현재 시간", SYSDATE+1+2/24+3/(24*60)+4/(24*60*60)
FROM DUAL;
COMMIT;