-
002. 컬럼,데이터 추가/삭제, 테이블 제거, NULL 처리/연산, 문자열 결합, WHERE 조건문, BETWEEN ⓐ AND ⓑSsY/Class 2023. 3. 28. 10:32728x90
SCOTT 계정 관찰 / 실습
- 시작은 항상 해당 계정으로 정확히 접속했는지 확인
SELECT USER FROM DUAL; --==>> SCOTT
-- 어제에 이어...
- 컬럼에 대한 코멘트 정보 입력(설정) 후 확인
--○ TBL_EMP 테이블에 소속된(포함된) -- 컬럼에 대한 코멘트 정보를 입력(설정)한다. COMMENT ON COLUMN TBL_EMP.EMPNO IS '사원번호'; COMMENT ON COLUMN TBL_EMP.ENAME IS '사원명'; COMMENT ON COLUMN TBL_EMP.JOB IS '직종명'; COMMENT ON COLUMN TBL_EMP.MGR IS '관리자사원번호'; COMMENT ON COLUMN TBL_EMP.HIREDATE IS '입사일'; COMMENT ON COLUMN TBL_EMP.SAL IS '급여'; COMMENT ON COLUMN TBL_EMP.COMM IS '수당'; COMMENT ON COLUMN TBL_EMP.DEPTNO IS '부서번호'; --==>> /* Comment이(가) 생성되었습니다. * 8 */ --○ 코멘트 데이터가 입력된 테이블 (TBL_EMP)의 컬럼 레벨의 정보 확인 SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'TBL_EMP'; --==>> /* TBL_EMP EMPNO 사원번호 TBL_EMP ENAME 사원명 TBL_EMP JOB 직종명 TBL_EMP MGR 관리자사원번호 TBL_EMP HIREDATE 입사일 TBL_EMP SAL 급여 TBL_EMP COMM 수당 TBL_EMP DEPTNO 부서번호 */- 컬럼 구조의 추가 및 제거
- 컬럼 구조의 추가
- 날짜형식 세션 변경
- 컬럼 추가 = 테이블의 구조를 변경하므로 ALTER 를 사용
- 숫자형이 아닌 문자형으로 데이터 타입을 처리해야하는 경우
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; -- 날짜 형식 세션 변경 --==>> Session이(가) 변경되었습니다. SELECT * FROM TBL_EMP; --○ TBL_EMP 테이블에 주민등록번호 데이터를 담을 수 있는 컬럼 추가 --// 구조 변경 ALTER TABLE TBL_EMP ADD SSN CHAR(13); --// 테이블에 대표적으로 추가할 수 있는게 컬럼이라 명시하지 않아도 됨 --// 문자열 , 고정형 --==>> Table TBL_EMP이(가) 변경되었습니다. --※ 숫자로만 구성된 데이터라 할지라도 맨 앞에 0이 들어올 가능성이 있는 숫자가 조합된 데이터라면 -- 숫자형이 아닌 문자형으로 데이터타입을 처리해야 한다~!!! SELECT 00001213 FROM DUAL; --==>> 1213 SELECT '00001213' FROM DUAL; --==>> 00001213- 컬럼을 추가한 테이블 확인
- 컬럼의 추가한 순서는 상관이 없다.
--○ 확인 SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SSN FROM TBL_EMP; --==>> /* EMPNO ENAME JOB MGR HIREDATE SSN ---------- ---------- --------- ---------- ---------- ------------- 7369 SMITH CLERK 7902 1980-12-17 7499 ALLEN SALESMAN 7698 1981-02-20 7521 WARD SALESMAN 7698 1981-02-22 7566 JONES MANAGER 7839 1981-04-02 7654 MARTIN SALESMAN 7698 1981-09-28 7698 BLAKE MANAGER 7839 1981-05-01 7782 CLARK MANAGER 7839 1981-06-09 7788 SCOTT ANALYST 7566 1987-07-13 7839 KING PRESIDENT 1981-11-17 7844 TURNER SALESMAN 7698 1981-09-08 7876 ADAMS CLERK 7788 1987-07-13 EMPNO ENAME JOB MGR HIREDATE SSN ---------- ---------- --------- ---------- ---------- ------------- 7900 JAMES CLERK 7698 1981-12-03 7902 FORD ANALYST 7566 1981-12-03 7934 MILLER CLERK 7782 1982-01-23 */ DESC TBL_EMP; --==>> /* 이름 널? 유형 -------- -- ------------ EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SSN CHAR(13) */ --> 주민등록번호가 담긴 SSN 컬럼이 추가 되었음을 확인. SELECT EMPNO, MGR, HIREDATE, ENAME, JOB FROM TBL_EMP; /* EMPNO MGR HIREDATE ENAME JOB ---------- ---------- ---------- ---------- --------- 7369 7902 1980-12-17 SMITH CLERK 7499 7698 1981-02-20 ALLEN SALESMAN 7521 7698 1981-02-22 WARD SALESMAN 7566 7839 1981-04-02 JONES MANAGER 7654 7698 1981-09-28 MARTIN SALESMAN 7698 7839 1981-05-01 BLAKE MANAGER 7782 7839 1981-06-09 CLARK MANAGER 7788 7566 1987-07-13 SCOTT ANALYST 7839 1981-11-17 KING PRESIDENT 7844 7698 1981-09-08 TURNER SALESMAN 7876 7788 1987-07-13 ADAMS CLERK EMPNO MGR HIREDATE ENAME JOB ---------- ---------- ---------- ---------- --------- 7900 7698 1981-12-03 JAMES CLERK 7902 7566 1981-12-03 FORD ANALYST 7934 7782 1982-01-23 MILLER CLERK */ --> 테이블 내에서 컬럼의 순서는 구조적으로 의미 없음 (//내부적으로 그냥 선택시에 순서 바꿔서 입력한대로 나오기 때문에)
※ DELETE : 데이터 제거
DROP : 구조적 제거
- 데이터나 테이블등의 구조를 잘못 제거하여 ROLLBACK 해야하는 불상사를 막기 위해
( 자동 COMMIT시에는 ROLLBACK 도 불가능)
SELECT *
FROM 테이블명
WHERE 조건절
과 같이 입력하여 SELECT * 을 DELETE 또는 DROP 을 적어주는 방식을 사용하는것이 좋다.- 컬럼 제거 및 확인
--○ TBL_EMP 테이블에서 추가한 SSN(주민등록번호) 컬럼 제거 ALTER TABLE TBL_EMP --// 테이블의 구조적 변경 + 내부에서 컬럼을 제거 DROP COLUMN SSN; --// 1.컬럼이라는 키워드를 꼭 써줘야한다. (변경할때는 제약사항을 변경하는 등의 다른 선택지가 있기 때문) 2.데이터타입 명시 필요 없음 --==>> Table TBL_EMP이(가) 변경되었습니다. --○ 확인 SELECT * FROM TBL_EMP; DESC TBL_EMP; --> SSN(주민등록번호) 컬럼이 정상적으로 제거되었음을 확인
- 데이터 제거 및 확인
SELECT * FROM TBL_EMP; SELECT * FROM TBL_EMP WHERE EMPNO=7369; DELETE FROM TBL_EMP WHERE EMPNO=7369; --==> 1 행 이(가) 삭제되었습니다. DELETE TBL_EMP; -- 권장하지 않음 DELETE FROM TBL_EMP; -- 권장 --==>> 13개 행 이(가) 삭제되었습니다. --○ 확인 SELECT * FROM TBL_EMP; --==>> 조회 결과 없음 --> 테이블의 구조의 구조는 그대로 남아있는 상태에서 -- 데이터 모두 소실(삭제) 된 상황임을 확인
- 테이블 제거 및 확인
--○ 테이블을 구조적으로 제거 DROP TABLE TBL_EMP; --==> Table TBL_EMP이(가) 삭제되었습니다. --○ 확인 SELECT * FROM TBL_EMP; --==>> /* ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause: *Action: 175행, 6열에서 오류 발생 */- 테이블이 제거되었기 때문에 오류가 발생한다
더보기실습 진행을 위해 다시 테이블을 복사해 생성해주었다.
--○ 테이블 다시 생성(복사) CREATE TABLE TBL_EMP AS SELECT * FROM EMP; --==>> Table TBL_EMP이(가) 생성되었습니다.
- NULL 의 처리
※ 관찰 결과
NULL 은 상태의 값을 의미하며, 실제 존재하지 않는 값이기 때문에 이러한 NULL 이 연산에 포함될 경우
그 결과는 무조건 NULL 이다.
SELECT 10, 10+2, 10-2, 10*2, 10/2 FROM DUAL; --==>> 10 12 8 20 5 SELECT NULL, NULL+2, NULL-2, NULL*2, NULL/2, 10+NULL, 10-NULL, 10*NULL, 10/NULL FROM DUAL; --==>> (null) (null) (null) (null) (null) (null) (null) (null) (null)- NULL 값 조회
- 상기 관찰 결과에 따라 NULL 값은 논리적 구성을 통해서 조회
IS, IS NOT, NOT( TRUE )
- 오라클 (산술적) 일반적 연산자의 종류
--○ TBL_EMP 테이블에서 커미션(COMM, 수당)이 NULL 인 직원의 -- 사원명, 직종명, 급여, 커미션 항목을 조회한다. SELECT 사원명, 직종명, 급여, 커미션 FROM TBL_EMP WHERE 커미션(COMM, 수당)이 NULL; SELECT ENAME"사원명", JOB"직종명", SAL"급여", COMM"커미션" FROM TBL_EMP WHERE 커미션(COMM, 수당)이 NULL; SELECT ENAME"사원명", JOB"직종명", SAL"급여", COMM"커미션" FROM TBL_EMP WHERE COMM = NULL; --==>> 조회 결과 없음 SELECT ENAME"사원명", JOB"직종명", SAL"급여", COMM"커미션" FROM TBL_EMP WHERE COMM = (null); --==>> 조회 결과 없음 SELECT ENAME"사원명", JOB"직종명", SAL"급여", COMM"커미션" FROM TBL_EMP WHERE COMM = 'NULL'; --==>> //(숫자 구성으로 이루어진 타입인데 문자열 조건이기 때문에 발생한 에러) /* ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number. */ DESC TBL_EMP; --==>> /* 이름 널? 유형 -------- -- ------------ EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) */ --> COMM 컬럼은 숫자형 데이터 타입을 취하고 있음을 확인 --※ NULL 은 실제 존재하지 않는 값이기 때문에 일반적인 연산자를 활용하여 비교할 수 없다. -- 즉, 산술적인 비교 연산을 수행할 수 없다는 의미이다. -- NULL 을 대상으로 사용할 수 없는 연산자들.... -- >=, <=, >, <, =, !=, ^=, <> → >< 는 없음 //(자바와 연산자들이 거의 동일함 + '같지 않다'는 오라클은 몇가지 더 있다.) SELECT ENAME"사원명", JOB"직종명", SAL"급여", COMM"커미션" FROM TBL_EMP WHERE COMM IS null; --> 산술적 구성이 아닌, 논리적 구성 (IS) --==>> /* SMITH CLERK 800 JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300 */ --○ TBL_EMP 테이블에서 20번 부서에 근무하지 않는 직원들의 -- 사원명, 직종명, 부서번호 항목을 조회한다. SELECT 사원명, 직종명, 부서번호 FROM TBL_EMP 테이블 WHERE 부서번호가 20이 아니다; SELECT ENAME"사원명", JOB"직종명", DEPTNO"부서번호" FROM TBL_EMP WHERE 부서번호가 20이 아니다; SELECT ENAME"사원명", JOB"직종명", DEPTNO"부서번호" FROM TBL_EMP WHERE DEPTNO != 20; SELECT ENAME"사원명", JOB"직종명", DEPTNO"부서번호" FROM TBL_EMP WHERE DEPTNO ^= 20; SELECT ENAME"사원명", JOB"직종명", DEPTNO"부서번호" FROM TBL_EMP WHERE DEPTNO <> 20; --==>> /* ALLEN SALESMAN 30 WARD SALESMAN 30 MARTIN SALESMAN 30 BLAKE MANAGER 30 CLARK MANAGER 10 KING PRESIDENT 10 TURNER SALESMAN 30 JAMES CLERK 30 MILLER CLERK 10 */ --○ TBL_EMP 테이블에서 커미션이 NULL 이 아닌 직원들의 -- 사원명, 직종명, 급여, 커미션 항목을 조회한다. SELECT 사원명, 직종명, 급여, 커미션 FROM TBL_EMP WHERE 커미션이 NULL 이 아닌; SELECT ENAME "사원명", JOB "직종명", SAL "급여", COMM "수당" FROM TBL_EMP WHERE 커미션이 NULL 이 아닌; SELECT ENAME "사원명", JOB "직종명", SAL "급여", COMM "수당" FROM TBL_EMP WHERE COMM이 NULL 이 아닌; SELECT ENAME "사원명", JOB "직종명", SAL "급여", COMM "수당" FROM TBL_EMP WHERE COMM IS NOT NULL; --==>> /* ALLEN SALESMAN 1600 300 WARD SALESMAN 1250 500 MARTIN SALESMAN 1250 1400 TURNER SALESMAN 1500 0 */ SELECT ENAME "사원명", JOB "직종명", SAL "급여", COMM "수당" FROM TBL_EMP WHERE NOT COMM IS NULL; --// COMM IS NULL 이 TRUE → 거기에 NOT을 붙여 FALSE 로 만듦 = 논리적! --==>> /* ALLEN SALESMAN 1600 300 WARD SALESMAN 1250 500 MARTIN SALESMAN 1250 1400 TURNER SALESMAN 1500 0 */
- NULL 값의 연산 (함수 사용)
- 일반적인 연산을 하게 되면 아래와 같이 연봉이 NULL 이 되는 현상이 발생한다.
- NVL() // 파라미터 2개
- NVL2() // 파라미터 3개
- COALESCE() // 매개변수 제한이 없는 형태
--○ TBL_EMP 테이블에서 모든 사원들의 -- 사원번호, 사원명, 급여, 커미션, 연봉 항목을 조회한다. -- 단, 급여(SAL)는 매월 지급한다. -- 또한, 수당(COMM)은 매년 지급한다. SELECT 사원번호, 사원명, 급여, 커미션, 연봉 FROM TBL_EMP; SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", COMM"커미션", 연봉 FROM TBL_EMP; SELECT * FROM TBL_EMP; SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", COMM"커미션" ,10 "연봉" FROM TBL_EMP; SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", COMM"커미션" ,SAL*12 "연봉" FROM TBL_EMP; SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", COMM"커미션" ,SAL*12 + COMM "연봉" FROM TBL_EMP; --==>> /* 사원번호 사원명 급여 커미션 연봉 ---------- ---------- ---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 300 19500 7521 WARD 1250 500 15500 7566 JONES 2975 7654 MARTIN 1250 1400 16400 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 0 18000 7876 ADAMS 1100 사원번호 사원명 급여 커미션 연봉 ---------- ---------- ---------- ---------- ---------- 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 */
--○ NVL() --// NULL 값을 계산하기 위한 함수 SELECT NULL "ⓐ", NVL(NULL,10) "ⓑ", NVL(500,600) "ⓒ" FROM DUAL; --==>> (null) 10 500 --> 첫 번째 파라미터 값이 NULL 이면, 두 번째 파라미터 값을 반환한다. -- 첫 번째 파라미터 값이 NULL 이 아니면, 그 값(첫번째 파라미터)을 그대로 반환한다. SELECT EMPNO, ENAME, COMM FROM TBL_EMP; SELECT EMPNO, ENAME, NVL(COMM,1234) FROM TBL_EMP; SELECT EMPNO, ENAME, NVL(COMM,1234) "확인" FROM TBL_EMP; SELECT EMPNO, ENAME, NVL(COMM,0) "실제수당" --// 실제수당이 0이다 라고 표현해줄 수 있음 FROM TBL_EMP;
SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", COMM"커미션" ,SAL*12 + NVL(COMM,0) "연봉" FROM TBL_EMP; --> 연봉을 산출하는 과정에서 수당이 NULL 이 아닌 직원들은 -- 그 수당을 그대로 더해주고 -- 수당이 NULL 인 직원들은 0으로 바꾸어 더해주는 연산을 수행
--○ NVL2() --> 첫 번째 파라미터가 NULL 이 아닌 경우, 두번째 파라미터를 반환하고 -- 첫 번째 파라미터가 NULL 인 경우, 세 번째 파라미터를 반환한다. SELECT 10, NVL2(10, '청기올려', '백기올려') "결과확인" FROM DUAL; --==>> 10 청기올려 SELECT NULL, NVL2(NULL,'청기올려', '백기올려') "결과확인" FROM DUAL; --==>> (null) 백기올려
--○ COALESCE() --> 매개변수 제한이 없는 형태로 인지하고 활용한다. -- 맨 앞에 있는 파라미터부터 차례로 NULL 인지 아닌지 확인하여 -- NULL 이 아닐 경우 적용(반환,처리) 하고 -- NULL 인 경우에는 그 다음 매개변수 값으로 적용(반환,처리) 한다. -- NVL() 이나 NVL2() 와 비교하여... -- 모~~~~~~~~든 경우의 수를 고려할 수 있는 특징을 갖고 있다. SELECT NULL "기본확인" , COALESCE(NULL,NULL, 30) "결과확인1" , COALESCE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,100) "결과확인2" , COALESCE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,500) "결과확인3" , COALESCE(10,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,500) "결과확인4" , COALESCE(NULL,NULL,NULL,NULL,80,NULL,NULL,NULL,NULL,NULL,NULL,500) "결과확인5" FROM DUAL; --==>> (null) 30 100 500 10 80DESC TBL_EMP; SELECT * FROM TBL_EMP; --○ 실습을 위한 데이터 추가 입력 INSERT INTO TBL_EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, DEPTNO) VALUES(8000, '파이리', 'SALESMAN', 7839, SYSDATE, 10); --==>> 1 행 이(가) 삽입되었습니다. INSERT INTO TBL_EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, COMM, DEPTNO) VALUES(8001, '메타몽', 'SALESMAN', 7839, SYSDATE,100, 10); --==>> 1 행 이(가) 삽입되었습니다. --○ 확인 SELECT * FROM TBL_EMP; --==>> /* : 8000 파이리 SALESMAN 7839 2023-03-23 (null) (null) 10 8001 메타몽 SALESMAN 7839 2023-03-23 (null) 100 10 */ --○ 커밋 COMMIT; --==>> 커밋 완료. --○ 다시 한 번 COALESCE 로 풀이 SELECT EMPNO"사원번호", ENAME"사원명", SAL"급여", COMM"커미션" ,COALESCE(SAL*12+COMM, SAL*12, COMM, 0)"연봉" FROM TBL_EMP;
- 날짜와 시간에 대한 세션 환경 설정 변경
- SESSION 변경이어 재접속시에는 적용이 되지 않는다
※ 날짜와 시간에 대한 세션 환경 설정 변경 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; --//HH24 : 24시간 기준 //24입력을 안하면 12시간 AM,PM 기반으로 됨. --==>> Session이(가) 변경되었습니다. --○ 현재 날짜 및 시간을 반환하는 함수 --//대표적인 3가지 SELECT SYSDATE "확인1", CURRENT_DATE "확인2", LOCALTIMESTAMP "확인3" FROM DUAL; --==>> /* 2023-03-23 12:30:25 2023-03-23 12:30:25 23/03/23 12:30:25.000000000 */ --※ 날짜와 시간에 대한 세션 환경 설정 다시 변경 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; --==>> Session이(가) 변경되었습니다. --○ 변경 이후 다시 조회 SELECT SYSDATE "확인1", CURRENT_DATE "확인2", LOCALTIMESTAMP "확인3" FROM DUAL; --==>> /* 2023-03-23 2023-03-23 23/03/23 12:33:01.000000000 */
- 컬럼과 컬럼의 연결(결합)
- 문자 타입의 연결 : 『||』
- 결합 시 컬럼의 정체성 = 문자 타입
- 호환이 안되는게 정상이지만 내부적으로 문자 타입으로 인식하게 만들어주어 결합시켜주는 것
--○ 컬럼과 컬럼의 연결(결합) -- 문자 타입의 연결 -- 『+』 연산자를 통한 결합은 불가능 → 『||』 -- //JAVA의 논리OR는 오라클에서는 문자열 결합으로 사용 -- 오로지 문자타입 연결을 해주는 것 SELECT 1+1 FROM DUAL; --==>> 2 SELECT '메타몽'+'리자몽' FROM DUAL; --==>> /* ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number. */ SELECT '메타몽','리자몽' FROM DUAL; --==>> 메타몽 리자몽 // 컬럼 구분 SELECT '메타몽' || '리자몽' FROM DUAL; --==>> 메타몽리자몽 // 하나의 컬럼에 결합 되어있는 것 확인 DESC TBL_EMP; --==>> /* 이름 널? 유형 -------- -- ------------ EMPNO NUMBER(4) → 숫자 타입 ENAME VARCHAR2(10) → 문자 타입 JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) */ SELECT EMPNO, ENAME FROM TBL_EMP; SELECT EMPNO || ENAME "결과확인" FROM TBL_EMP; --==>> /* --// 결합 시 컬럼의 정체성 = 문자 타입 으로 오라클이 설정 해준다. // 호환이 안되는게 정상이지만 내부적으로 문자 타입으로 인식하게 만들어주어 결합시켜주는 것이다. 7369SMITH 7499ALLEN 7521WARD 7566JONES 7654MARTIN 7698BLAKE 7782CLARK 7788SCOTT 7839KING 7844TURNER 7876ADAMS 7900JAMES 7902FORD 7934MILLER 8000파이리 8001메타몽 */ -- 문자 날짜 문자 숫자 문자 타입 -- --------- ------- --------- --- ------------ SELECT '꼬부기는', SYSDATE,'에 연봉',500,'억을 원한다.' FROM DUAL; --==>> 꼬부기는 2023-03-23 에 연봉 500 억을 원한다. -- 문자 날짜 문자 숫자 문자 타입 -- --------- ------- --------- --- ------------ SELECT '꼬부기는'|| SYSDATE || '에 연봉' || 500 || '억을 원한다.' FROM DUAL; --==>> 꼬부기는2023-03-23에 연봉500억을 원한다. --※ 오라클에서는 문자 타입의 형태로 형 변환하는 별도의 과정 없이 -- 위에서 처리하고 확인한 내용처럼 『||』만 삽입해주면 간단히 컬럼과 컬럼을 -- (서로 다른 종류의 데이터) 결합하는 것이 가능하다. -- 단적으로, MS-SQL 에서는 모든 데이터를 문자 타입으로 CONVERT 해야 한다.
--○ TBL_EMP 테이블의 파이리,메타몽을 제거한 후 -- 이 데이터를 활용하여 -- 모든 직원들의 데이터에 대해서 -- 다음과 같은 결과를 얻을 수 있도록 쿼리문을 구성한다. -- SMITH 의 현재 연봉은 9600인데, 희망 연봉은 19200이다. -- ALLEN 의 현재 연봉은 19500인데, 희망 연봉은 39000이다. -- : DELETE FROM TBL_EMP WHERE EMPNO IN (8000, 8001); --==>> 2개 행 이(가) 삭제되었습니다. SELECT * FROM TBL_EMP; --> 원하는 데이터가 제대로 제거되었음을 확인 COMMIT; --==>> 커밋 완료 --방식1 SELECT ENAME || ' 의 현재 연봉은' || NVL(SAL*12+COMM,SAL*12) || '인데, 희망 연봉은 ' || NVL((SAL*12+COMM,SAL*12) *2 || '이다.' FROM TBL_EMP; --방식2 SELECT ENAME || ' 의 현재 연봉은' || NVL2(COMM,SAL*12+COMM,SAL*12) || '인데, 희망 연봉은 ' || NVL2(COMM,SAL*12+COMM,SAL*12)*2 || '이다.' FROM TBL_EMP; --방식3 SELECT ENAME || ' 의 현재 연봉은' || COALESCE(SAL*12+COMM,SAL*12,COMM,0) || '인데, 희망 연봉은 ' || COALESCE(SAL*12+COMM,SAL*12,COMM,0)*2 || '이다.' FROM TBL_EMP; --==>> /* SMITH의 현재 연봉은9600인데, 희망 연봉은 19200이다. ALLEN의 현재 연봉은19500인데, 희망 연봉은 39000이다. WARD의 현재 연봉은15500인데, 희망 연봉은 31000이다. JONES의 현재 연봉은35700인데, 희망 연봉은 71400이다. MARTIN의 현재 연봉은16400인데, 희망 연봉은 32800이다. BLAKE의 현재 연봉은34200인데, 희망 연봉은 68400이다. CLARK의 현재 연봉은29400인데, 희망 연봉은 58800이다. SCOTT의 현재 연봉은36000인데, 희망 연봉은 72000이다. KING의 현재 연봉은60000인데, 희망 연봉은 120000이다. TURNER의 현재 연봉은18000인데, 희망 연봉은 36000이다. ADAMS의 현재 연봉은13200인데, 희망 연봉은 26400이다. JAMES의 현재 연봉은11400인데, 희망 연봉은 22800이다. FORD의 현재 연봉은36000인데, 희망 연봉은 72000이다. MILLER의 현재 연봉은15600인데, 희망 연봉은 31200이다. */
- ※ 문자열을 나타내는 홑따옴표 사이에서(시작과 끝)
-- 홑따옴표 두개가 홑따옴표 하나(어퍼스트로피)를 의미한다.
-- 홑따옴표 『'』 하나는 문자열의 시작을 나타내고
-- 홑따옴표 『''』 두 개는 문자열 영역 안에서 어퍼스트로피를 나타내며
-- 다시 등장하는 홑따옴표 『'』하나가 문자열 영역의 종료를 의미하게 되는 것이다.
--○ 아래와 같이 표현해 보자. -- SMITH's 입사일은 1980-12-17 이다. 그리고 급여는 800 이다. -- ALLEN's 입사일은 1981-02-20 이다. 그리고 급여는 1600 이다. -- : SELECT * FROM TBL_EMP; SELECT ENAME || '''s 입사일은' || HIREDATE || '이다. 그리고 급여는 ' || SAL || ' 이다.' FROM TBL_EMP; --==>> /* SMITH's 입사일은1980-12-17이다. 그리고 급여는 800 이다. ALLEN's 입사일은1981-02-20이다. 그리고 급여는 1600 이다. WARD's 입사일은1981-02-22이다. 그리고 급여는 1250 이다. JONES's 입사일은1981-04-02이다. 그리고 급여는 2975 이다. MARTIN's 입사일은1981-09-28이다. 그리고 급여는 1250 이다. BLAKE's 입사일은1981-05-01이다. 그리고 급여는 2850 이다. CLARK's 입사일은1981-06-09이다. 그리고 급여는 2450 이다. SCOTT's 입사일은1987-07-13이다. 그리고 급여는 3000 이다. KING's 입사일은1981-11-17이다. 그리고 급여는 5000 이다. TURNER's 입사일은1981-09-08이다. 그리고 급여는 1500 이다. ADAMS's 입사일은1987-07-13이다. 그리고 급여는 1100 이다. JAMES's 입사일은1981-12-03이다. 그리고 급여는 950 이다. FORD's 입사일은1981-12-03이다. 그리고 급여는 3000 이다. MILLER's 입사일은1982-01-23이다. 그리고 급여는 1300 이다. */
- UPPER(), LOWER(), INITCAP()
- 웹 페이지 등에서 소문자로 검색했을 때, 결과가 안나오는데 대문자로 검색하면 나오게 되는경우 등
이러한 상황을 피하기 위한 함수
- WHERE 조건문에 입력
(아래의 그림을 보면, 검색할 때, 제목/ 내용/작성자 등으로 제한하여 검색하게 되는 것이 데이터베이스에게 전달되는 쿼리문의 WHERE 조건문에 들어가게 되는 것이다.)
SELECT * FROM EMP WHERE JOB = 'salesman'; --==>> 조회결과 없음 --○ UPPER(), LOWER(), INITCAP() SELECT 'oRaCLe' "확인1" ,UPPER('oRaCLe') "확인2" ,LOWER('oRaCLe') "확인3" ,INITCAP('oRaCLe') "확인4" FROM DUAL; --==>> oRaCLe ORACLE oracle Oracle -- UPPER() 는 모두 대문자로 변환하여 반환 -- LOWER() 는 모두 소문자로 변환하여 반환 -- INITCAP() 는 첫 글자를 대문자로 하고 나머지는 모두 소문자로 변환하여 반환 --'sALEsMaN' --'SaLeSmAn' --'salesMAN' SELECT * FROM EMP WHERE JOB = 'sALEsMaN'; --==>> 조회결과 없음 SELECT * FROM EMP WHERE JOB = UPPER('sALEsMaN'); SELECT * FROM EMP WHERE JOB = UPPER('SaLeSmAn'); SELECT * FROM EMP WHERE JOB = UPPER('salesMAN');
--○ 한 번에 조회 할 수 있도록 만들려면 --○ 실습을 위한 데이터 추가 입력 INSERT INTO TBL_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(8000,'ㄱㅎㅈ','Salesman',7698,SYSDATE,2000,200,30); INSERT INTO TBL_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(8001,'ㅂㄱㅇ','SalesMan',7698,SYSDATE,2000,200,30); INSERT INTO TBL_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(8002,'ㅇㅈㅂ','salesman',7698,SYSDATE,2000,200,30); INSERT INTO TBL_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(8003,'ㅇㅅㅇ','SALESman',7698,SYSDATE,2000,200,30); INSERT INTO TBL_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(8004,'ㅇㄱㅂ','SalesMAN',7698,SYSDATE,2000,200,30); --==>> 1 행 이(가) 삽입되었습니다. * 5 --○ 확인 SELECT * FROM TBL_EMP; --==>> /* EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 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 7839 KING PRESIDENT 1981-11-17 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30 7876 ADAMS CLERK 7788 1987-07-13 1100 20 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 8000 ㄱㅎㅈ Salesman 7698 2023-03-23 2000 200 30 8001 ㅂㄱㅇ SalesMan 7698 2023-03-23 2000 200 30 8002 ㅇㅈㅂ salesman 7698 2023-03-23 2000 200 30 8003 ㅇㅅㅇ SALESman 7698 2023-03-23 2000 200 30 8004 ㅇㄱㅂ SalesMAN 7698 2023-03-23 2000 200 30 */ --○ 커밋 COMMIT; -- 커밋 완료. --○ TBL_EMP 테이블에서 대소문자 구분없이 세일즈맨 직종인 사원의 -- 사원번호, 사원명, 직종명, 입사일, 부서번호 항목을 조회한다. -- ('SALESMAN','Salesman','SalesMan','salesman','SALESman','salesMAN',...) SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", HIREDATE"입사일", DEPTNO"부서번호" FROM TBL_EMP WHERE UPPER(JOB) = UPPER('SALESMAN'); -- 아직은 입력받아오는 형태가 아니기 때문에 그냥 써도 문제는 없음 SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", HIREDATE"입사일", DEPTNO"부서번호" FROM TBL_EMP WHERE LOWER(JOB) = LOWER('salesman'); SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", HIREDATE"입사일", DEPTNO"부서번호" FROM TBL_EMP WHERE INITCAP(JOB) = INITCAP('Salesman');
- TO_DATE()
- 문자열을 날짜형식으로 변환해주는 메소드
- 날짜를 검색하기 위해 사용
--○ TBL_EMP 테이블에서 1981년 9월 28일 입사한 직원의 -- 사원명,직종명,입사일 항목을 조회한다. SELECT ENAME"사원명",JOB"직종명",HIREDATE"입사일" FROM TBL_EMP WHERE HIREDATE = '1981-09-28'; --==>> MARTIN SALESMAN 1981-09-28 // 이 경우, 오라클이 문자열 타입으로 비교를 해준 것 --○ TO_DATE() SELECT ENAME"사원명",JOB"직종명",HIREDATE"입사일" FROM TBL_EMP WHERE HIREDATE = TO_DATE('1981-09-28','YYYY-MM-DD'); -- ---------- : 1981-9-28 -- ------------: 1981-09-28 (문자열) -- --------------------: 1981년 9월 28일 (날짜) // TO_DATE()는 날짜가 맞는지도 확인 가능 -- 즉 13월 (에러), 32일 (에러) 남- ※ 오라클에서는 날짜 데이터에 대한 크기 비교가 가능하다.
오라클에서 날짜 데이터에 대한 크기 비교시 과거보다 현재 또는 현재보다 미래를 더 큰 값으로 간주하여 처리한다. - SYSDATE : 연-월-일-시-분-초 까지 입력 되어있음
이를 연-월-일 만 사용하기 위해서는,
SYSDATE 로 받은 것을 연-월-일만 추출하여 문자열로 변환하고,
변환한 문자열을 TO_DATE() 로 변환한 후 입력해야 한다.
- BETWEEN ⓐ AND ⓑ
날짜형, 숫자형, 문자형 데이터 모두에 적용된다.
단, 문자형일 경우 사전식 구조의 형태로 이해하는 것이 바람직하며
아스키코드 순서를 따르기 때문에 대문자가 앞쪽에 위치하고, 소문자가 뒤쪽에 위치한다.
또한, BETWEEN ⓐ AND ⓑ 는 쿼리문이 수행되는 시점에서
오라클 내부적으로는 부등호 연산자의 형태로 바뀌어 연산이 처리된다.
- 날짜 형식에서 사용
--○ TBL_EMP 테이블에서 입사일이 1981년 4월 2일 부터 -- 1981년 9월 28일 사이에 입사한 직원들의 -- 사원번호, 사원명, 직종명, 입사일 항목을 조회한다. (해당일 포함) SELECT 사원번호, 사원명, 직종명, 입사일 FROM TBL_EMP WHERE 입사일이 1981년 4월 2일 부터 1981년 9월 28일 사이 (해당일 포함); SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", HIREDATE"입사일" FROM TBL_EMP WHERE 1981년 4월 2일 <= 입사일이 <= 1981년 9월 28일 사이 (해당일 포함); SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", HIREDATE"입사일" FROM TBL_EMP WHERE 입사일이 >= 1981년 4월 2일 입사일이 <= 1981년 9월 28일 사이 (해당일 포함); SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", HIREDATE"입사일" FROM TBL_EMP WHERE 입사일이 >= 1981년 4월 2일 AND 입사일이 <= 1981년 9월 28일 사이 (해당일 포함); SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", HIREDATE"입사일" FROM TBL_EMP WHERE 입사일이 >= TO_DATE('1981-04-02','YYYY-MM-DD') AND 입사일이 <= TO_DATE('1981-09-28','YYYY-MM-DD'); SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", HIREDATE"입사일" FROM TBL_EMP WHERE HIREDATE >= TO_DATE('1981-04-02','YYYY-MM-DD') AND HIREDATE <= TO_DATE('1981-09-28','YYYY-MM-DD'); --==>> /* 7566 JONES MANAGER 1981-04-02 7654 MARTIN SALESMAN 1981-09-28 7698 BLAKE MANAGER 1981-05-01 7782 CLARK MANAGER 1981-06-09 7844 TURNER SALESMAN 1981-09-08 */ --○ BETWEEN ⓐ AND ⓑ → ⓐ 와 ⓑ 사이 → 날짜를 대상으로 적용 SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", 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 MANAGER 1981-04-02 7654 MARTIN SALESMAN 1981-09-28 7698 BLAKE MANAGER 1981-05-01 7782 CLARK MANAGER 1981-06-09 7844 TURNER SALESMAN 1981-09-08 */
- 숫자를 대상으로 사용
--○ BETWEEN ⓐ AND ⓑ → ⓐ 와 ⓑ 사이 → 숫자를 대상으로 적용 SELECT EMPNO, ENAME, SAL FROM TBL_EMP WHERE SAL BETWEEN 1600 AND 3000; --==>> /* 7499 ALLEN 1600 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7902 FORD 3000 8000 ㄱㅎㅈ 2000 8001 ㅂㄱㅇ 2000 8002 ㅇㅈㅂ 2000 8003 ㅇㅅㅇ 2000 8004 ㅇㄱㅂ 2000 */
- 문자를 대상으로 사용
--○ BETWEEN ⓐ AND ⓑ → ⓐ 와 ⓑ 사이 → 문자를 대상으로 적용 SELECT EMPNO, ENAME, JOB, SAL FROM TBL_EMP WHERE ENAME BETWEEN 'C' AND 'S'; --==>> /* 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 */ SELECT EMPNO, ENAME, JOB, SAL FROM TBL_EMP WHERE ENAME BETWEEN 'C' AND 's'; --==>> /* 7369 SMITH CLERK 800 7521 WARD SALESMAN 1250 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 1500 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 */
- ASCII()
- ASCII 코드를 확인하고 싶을 때, 사용하는 함수
--○ ASCII() SELECT ASCII('A')"확인1", ASCII('B')"확인2" , ASCII('a')"확인3", ASCII('b')"확인4" FROM DUAL; --==>> 65 66 97 98
더보기이후 강의에서 다시 활용하기 위하여 추가 데이터를 삭제, 확인 후 COMMIT
SELECT * FROM TBL_EMP WHERE EMPNO BETWEEN 8000 AND 8004; DELETE FROM TBL_EMP WHERE EMPNO BETWEEN 8000 AND 8004; --==> 5개 행 이(가) 삭제되었습니다. SELECT * FROM TBL_EMP; COMMIT; --==>> 커밋 완료.
- 조건이 2개 이상일 때 (논리적인 조건이 필요할 때)
※ 아래의 3가지 유형의 쿼리문은 모두 같은 결과를 반환한다.
하지만, 맨 위의 쿼리문이 가장 빠르게 처리된다.
물론, 메모리에 대한 내용이 아니라 CPU 에 대한 내용이므로 이 부분까지 감안해서
쿼리문의 내용을 구성하는 일은 많지 않다.
→『IN』『=ANY』는 같은 연산자 효과를 가진다.
모두 내부적으로는 『OR』 구조로 변경되어 연산 처리된다.
- 메모리파트에서 의 정말 작은 차이는 신경써서 작은 쪽으로 처리해야한다!
(속도가 굉장히 달라진다) 즉, 메모리에 퍼올려지는 속도인 PASING 순서를 잘 기억하자!!
--○ TBL_EMP 테이블에서 직종이 SALESMAN 과 CLERK 인 사원의 -- 사원번호, 사원명, 직종명, 급여 항목을 조회한다. SELECT 사원번호, 사원명, 직종명, 급여 FROM TBL_EMP WHERE 직종이 'SALESMAN' 직종이 'CLERK'; SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", SAL"급여" FROM TBL_EMP WHERE JOB이 'SALESMAN' JOB이 'CLERK'; -- 방법1 SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", SAL"급여" FROM TBL_EMP WHERE JOB = 'SALESMAN' OR JOB = 'CLERK'; -- 방법2 SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", SAL"급여" FROM TBL_EMP WHERE JOB IN('SALESMAN','CLERK'); -- 방법3 SELECT EMPNO"사원번호", ENAME"사원명", JOB"직종명", SAL"급여" FROM TBL_EMP WHERE JOB =ANY ('SALESMAN','CLERK');
--○ 추가 실습 테이블 구성(TBL_SAWON) CREATE TABLE TBL_SAWON ( SANO NUMBER(4) , SANAME VARCHAR2(30) , JUBUN CHAR(13) , HIREDATE DATE DEFAULT SYSDATE --// 추가적인 기능 부여 , SAL NUMBER(10) ); --==>> Table TBL_SAWON이(가) 생성되었습니다. SELECT * FROM TBL_SAWON; --==> 조회 결과 없음 DESC TBL_SAWON; --==>> /* 이름 널? 유형 -------- -- ------------ SANO NUMBER(4) SANAME VARCHAR2(30) JUBUN CHAR(13) HIREDATE DATE SAL NUMBER(10) */- 내일 이어서 진행...
- 데이터에 대한 처리 부분 다룰 예정728x90'SsY > Class' 카테고리의 다른 글
004. 문자열 / 숫자 데이터 / 날짜 데이터 관련 함수, 변환 함수, CASE문(조건문, 분기문) (0) 2023.03.28 003. 문자열 데이터 조회(와일드카드, ESCAPE), 커밋/롤백, 데이터 업데이트, 정렬, 문자열 관련 함수 (0) 2023.03.28 001. 테이블스페이스~컬럼 생성, 파일 조회 , * PASING 순서, 오라클의 주요 자료형(숫자,문자,날짜) , 연산자, 코멘트 (0) 2023.03.27 000. Oracle 설치 및 삭제 // 데이터베이스에 대한 이해 : 데이터, 정보, 데이터베이스(+관리) (0) 2023.03.25 019. 클래스와 인스턴스 : 접근지시제어자 활용, 메소드 중복정의(오버로딩) + 만년 달력, 주민등록번호 유효성 검사 (0) 2023.03.09