--P.26 모든 컬럼 조회하기
SELECT *
FROM EMP;
--P.28 테이블 컬럼정보 확인
DESC DEPT;
--P.29 사용자가 만든 모든 테이블 조회
SELECT *
FROM TAB;
--P.29 원하는 컬럼만 조회하기
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
FROM EMP;
--P.31 컬럼 길이 조절법
--1)데이터가 숫자일 경우
--EMPNO라는 컬럼의 길이를 숫자 4자리까지 들어가게 설정하기
COL EMPNO FOR 9999;
--2)데이터가 문자일 경우
--ENAME이란 컬럼의 길이를 8바이트까지 들어가게 설정
COL ENAME FOR A8;
--3)한 화면에 출력 가능한 줄 길이 설정(가로 길이 설정)
--한 화며을 가로 200바이트까지 출력되게 하기
SET LINE 200;
--4)한 페이지에 출력 가능한 줄 수 설정(세로 길이 설정)
SET PAGES 50;
--P.32 표현식(리터럴 상수)
--1)컬럼 이름 이외에 출력하기를 원하는 내용을 의미, SELECT 구문 뒤에 ''로 묶어서 사용됨
SELECT ENAME AS ENAME
, 'GOOD MORNING~~!' "GOOD MORNING"
FROM EMP;
SELECT ENAME AS ENAME
, 'GOOD MORNING~~!'
FROM EMP;
--2)표현식에 작은따옴표 들어갈 경우
--작은따옴표 1개를 출력하기 위해서 2개를 사용해야한다!!
--그럴경우 컬럼명은 날것으로 나오는 대신에 인스턴스들의 값들이 정상 출력됨
SELECT DNAME AS DNAME
, ', IT''S DEPTNO : '
, DEPTNO "DNAME AND DEPTNO"
FROM DEPT;
--P.33 컬럼 별칭 사용하여 출력
--ALIAS주는 방법은 다양함
SELECT PROFNO "PROF'NO"
, NAME AS "PROF'S NAME"
,
, PAY PROF_PAY
FROM PROFESSOR;
--P.35 DISTINCT로 중복결과 제거하기
SELECT DISTINCT DEPTNO
FROM EMP;
--P.36 그러나 DISTINCT 명령어는 1개의 컬럼에만 적어주도 모든 컬럼에 적용
--P.37 연결 연산자로 컬럼 붙여서 출력하기
SELECT ENAME || JOB
FROM EMP;
--P.38 연결 연산자로 연결하는 경우, 햇갈림 방지를 위해 리터럴을 넣어 사용하는 경우가 많음
--나아가 연결한 컬럼은 오라클에서 1개의 컬럼으로 인식함
SELECT ENAME || ' ''S JOB IS '|| JOB "NAME AND JOB"
FROM EMP;
--P.39 연습문제1
--Q. STUDENT 테이블에서 모든 학생의 이름과 ID, 체중을 출력하세요.
--컬럼 이름은 "ID AND WEIGHT"로 나오게 하세요
SELECT NAME || ' ''S ID : ' || ID || '
, WEIGHT IS ' || WEIGHT || 'KG' "ID AND WEIGHT"
FROM STUDENT;
--연습문제2
--Q.EMP 테이블을 조회하여 모든 사람의 이름과 직업을 줄력하세요.
SELECT ENAME || '(' || JOB || ')
, ' || ENAME || '''' || JOB || '''' "NAME AND JOB"
FROM EMP;
--연습문제3
--Q.EMP 테이블을 조회하여 모든사원의 이름과 급여를 아래와 같은 형태로 출력하세요.
SELECT ENAME || '''S SAL IS $' || SAL "NAME AND SAL"
FROM EMP;
--P.40 원하는 조건만 골라내기 WHERE절
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
FROM EMP
WHERE EMPNO = 7900;
SELECT ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE SAL < 1000;
--P.41 문자와 날짜를 조건으로 걸 경우에는 반드시 작은따옴표를 붙일 것
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE ENAME = 'SMITH';
--대소문자 역시 구분해주어야한다. 안할 경우, 원하는 값을 출력하지 못할 수도 있음
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE ENAME = 'SMITH';
--P.42 리눅스와 오라클의 날짜표시 방식이 다르다
--날짜 조회시 문자처럼 반드시 작은따옴표를 사용해야한다.
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE HIREDATE = '80/12/17';
--숫자 외에는 꼭 작은따옴표를 사용하자!
--문자는 대소문자 구분을 하고 날짜는 대소문자 구분이 없다!
--P.44 SQL에서 기본 산술 연산자 사용하기
SELECT ENAME AS ENAME
, SAL AS SAL
, SAL+100 AS SAL+100
FROM EMP
WHERE DEPTNO = 10;
--P.45 비교연산자 사용
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE SAL >= 4000;
--문자도 비교가 가능
--문자 'W'보다 크거나 같은 ENAME을 찾아서 출력하기
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE ENAME >= 'W';
--P.46 날짜비교
--날짜는 이전 날짜일수록 더 작은 날짜다(최근일수록 큰날짜)
SELECT ENAME AS ENAME
, HIREDATE AS HIREDATE
FROM EMP
WHERE HIREDATE >= '81/12/25';
--P.46 BETWEEN 연산자로 구간 데이터 조회하기
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;
--P.47 그러나 특정구간의 값을 검색할 때 가급적 BETWEEN 연산자를 쓰지 않는 것이 좋다
--출력결과는 동일하지만 속도면에서 아래의 방법이 데이터가 많을수록 속도 성능에서 우월하다
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE SAL >=2000
AND SAL <= 3000;
--P.47 화면이나 글자도 범위 검색이 가능하다
SELECT ENAME AS ENAME
FROM EMP
WHERE ENAME BETWEEN 'JAMES' AND 'MARTIN'
ORDER BY ENAME;
--P.48 IN 연산자로 여러 조건을 간편하게 검색하기
--!!이 연산자는 속도가 빨라서 애용되며, 문자나 날짜도 올수 있다
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, DEPTNO AS DEPTNO
FROM EMP
WHERE DEPTNO IN (10
, 20);
--P.48 LIKE 연산자로 비슷한 것들 모두 찾기
--LIKE와 함께 사용되는 기호는
--%: 글자수에 제한이 없고(0자 포함) 어떤 글자가 와도 상관없음
--_: 글자수는 한 글자만 올수 있고 어떤 글자가 와도 상관없음
--LIKE 조건문은 작은따옴표로 묶어주지 않으면 에러가 난다
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE SAL LIKE '1%';
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE ENAME LIKE 'A%';
--P.50 그러나 날짜의 경우엔 성능에 매우 안좋기에 지양하는 것이 좋음
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, HIREDATE AS HIREDATE
FROM EMP
WHERE HIREDATE LIKE '%80';
--P.50 _를 사용하여 입사일이 12월인 사람을 모두 출력할 경우
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, HIREDATE AS HIREDATE
FROM EMP
WHERE HIREDATE LIKE '___12%';
--P.51
--LIKE 연산자 뒤에 오는 '%'나 '_'가 LIKE 뒤의 조건 부분에서 가장 먼저 나올 경우
--SQL 성능은 대부분 최악으로 늦어진다
--그러나 인덱스로 인해 먼저 쓰지 않으면 속도가 빨라진다
--!!!따라서 절대로 %나 _를 가장 먼저 쓰지 말자!!!
--P.51 값이 무엇인지 모를 경우 IS NULL/IS NOT NULL
--NULL은 = 연산 사용불가능
--CF)NULL + @ = NULL
SELECT EMPNO AS EMPNO
, ENAME AS ENAEM
, COMM AS COMM
FROM EMP
WHERE COMM IS NULL;
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, COMM AS COMM
FROM EMP
WHERE COMM IS NOT NULL;
--P.54 검색 조건이 두 개 이상일 경우 조회
--1)모두 만족
SELECT ENAME AS ENAME
, HIREDATE AS HIREDATE
, SAL AS SAL
FROM EMP WEHRE HIREDATE > '01-JAN-82'
AND SAL >= 1300;
--2)하나만 만족
SELECT ENAME AS ENAME
, HIREDATE AS HIREDATE
, SAL AS SAL
FROM EMP WEHRE HIREDATE > '01-JAN-82'
OR SAL >= 1300;
--AND와 OR이 동시에 나오는 조건일 경우 항상 AND가 먼저 수행된 후에 OR이 수행
SELECT EMPNO AS EMPNO
, HIREDATE AS HIREDATE
, SAL AS SAL
, COMM AS COMM
FROM EMP
WHERE SAL > 1000
AND (COMM < 1000
OR COMM IS NULL);
SELECT EMPNO AS EMPNO
, HIREDATE AS HIREDATE
, SAL AS SAL
, COMM AS COMM
FROM EMP
WHERE SAL > 1000
AND COMM < 1000
OR COMM IS NULL;
--!!!P.55 사용자에게 조건을 입력받아서 조건에 맞는 값 출력하기!!!
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE EMPNO = &EMPNO;
--깔끔하게 처리하게
SET VERIFY OFF;
--P.57 정렬하여 출력하기 ORDER BY절 사용
SELECT ENAME AS ENAME
, SAL AS SAL
, HIREDATE AS HIREDATE
FROM EMP
ORDER BY ENAME;
--P.58 정렬하고 싶은 컬럼 개별적으로 정렬하기
SELECT DEPTNO AS DEPTNO
, SAL AS SAL
, ENAME AS ENAME
FROM EMP
ORDER BY DEPTNO ASC, SAL DESC;
--꼭 컬럼명이 아니라 이것외에도 여러 정렬방법 존재
SELECT ENAME AS ENAME
, SAL AS SAL
, HIREDATE AS HIREDATE
FROM EMP
WHERE SAL > 1000
ORDER BY 2, 1;
--P.59 그러나 ORDER BY를 많이 쓸수록 SQL 속도가 늦어진다
--그렇기에 정렬도 하고 SQL 속도도 높이면서 실전성있는 방법은 인덱스를 사용하는 방법이다
--P.59 집합연산자
--UNION, UNION ALL, INTERSECT, MINUS
--집합연산자 사용시 주의사항
--1.두집합의 SELECT절에 오는 컬럼 개수가 동일해야 함
--2.두집합의 SELECT절에 오는 컬럼의 데이터 형이 동일해야 함
--3.두 집합의 컬럼명은 달라도 상관없음
--P.60 UNION : 중복제거
SELECT STUDNO AS STUDNO
, NAME AS NAME
, DEPTNO1 AS DEPTNO1
, 1
FROM STUDENT
WHERE DEPTNO1 = 101;
UNION
SELECT PROFNO AS PROFNO
, NAME AS NAME
, DEPTNO AS DEPTNO
, 2
FROM PROFESSOR
WHERE DEPTNO = 101;
--P.61 UNION ALL : 중복포함
SELECT STUDNO AS STUDNO
, NAME AS NAME
, DEPTNO1 AS DEPTNO1
, 1
FROM STUDENT
WHERE DEPTNO1 = 101;
UNION ALL
SELECT PROFNO AS PROFNO
, NAME AS NAME
, DEPTNO AS DEPTNO
, 2
FROM PROFESSOR
WHERE DEPTNO = 101;
--P.63 INTERSECT 연산자 : 교집합
SELECT STUDNO AS STUDNO
, NAME AS NAME
FROM STUDENT
WHERE DEPTNO = 101
INTERSECT
SELECT STUDNO AS STUDNO
, NAME AS NAME
FROM STUDENT
WHERE DEPTNO2 = 201;
--P.63 MINUS 연산자 : 차집합
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
MINUS
SELECT EMPNO AS EMPNO
, ENAME AS ENAME
, SAL AS SAL
FROM EMP
WHERE SAL > 2500;
--P.64 대상 두 테이블 컬럼의 수가 다르거나 타입이 다르면 에러발생
--2장 단일행 함수 : 여러 건의 데이터를 한꺼번에 처리하는 것이 아니라 한번에 하나씩 처리
--복수행 함수는 여러 건의 데이터를 동시에 입력 받아서 결과값 1건 만들어줌
--복수행 함수를 그룹 함수라고도 함
--PG.70 문자함수
--1)INITCAP() : 영어에서 첫 글자만 대문자로 출력하고 나머지는 전부 소문자만 출력
--처음에 공백이 있으면 공백을 제외한 첫 글자부터 대문자 출력
SELECT ENAME AS ENAME
, INITCAP(ENAME) "INITCAP"
FROM EMP
WHERE DEPTNO = 10;
--PG.71
--2)LOWER(): 함수에 입력되는 값을 전부 소문자로 변경해 출력
--3)UPPER(): 입력되는 값을 전부 대문자로 변경해 출력
SELECT ENAME AS ENAME
, LOWER(ENAME) "LOWER"
, UPPER(ENAME) "UPPER"
FROM EMP
WHERE DEPTNO = 10;
--PG.73
--4)LENGTH/LENGHTB(): 입력된 문자열의 길이(바이트수)를 계산해주는 함수
--출력되는 값이 영어면 동일
SELECT ENAME AS ENAME
, LENGTH(ENAME) "LENGTH"
, LENGTHB(ENAME) "LENGTHB"
FROM EMP
WHERE DEPTNO = 20;
--한글이면 LENGTHB는 통상적으로 한글을 2비트씩 간주하여 출력한다
--그러나 설정마다 다르다
SELECT '서진수' AS "NAME"
, LENGTH('서진수') AS "LENGTH"
, LENGTHB('서진수') AS "LENGTHB"
FROM DUAL;
--PG.74 해당 LENGTH 함수는 회원가입 등 글자수 제한에 자주 사용됨
--입력받은 값으로 비교하기
SELECT ENAME AS ENAME
, LENGTH(ENAME) AS LENGTH(ENAME)
FROM EMP
WHERE LENGTH(ENAME) > LENGTH('&ENAME');
--5)CONCAT(): ||연산자와 동일한 기능
SELECT CONCAT(ENAME, JOB) AS CONCAT(ENAME, JOB)
FROM EMP
WHERE DEPTNO = 10;
--PG.75
--6)SUBSTR(): 주어진 문자열에서 특정 길이의 문자만 골라낼 때 사용
--카운트는 맨앞에 1부터 시작한다
--SUBSTR(Z,A,B): Z라는 컬럼에 대해 A부터 B번 세는 글자까지 출력
SELECT SUBSTR('ABCDE', 3, 2) AS "3,2"
, SUBSTR('ABCDE', -3, 2) AS "-3,2"
, SUBSTR('ABCDE', -3, 4) AS "-3,4"
FROM DUAL;
--P.76 STUDENT 테이블에서 JUMIN 컬럼을사용해서
--1전공이 101 번인 학생들의 이름과 태어난 월일，생일 하루전 날짜를 출력하세요
--잘린 문자열들이 숫자인 경우, 연산하여 계산한다
SELECT NAME
, SUBSTR(JUMIN, 3, 4) AS "BIRTHDAY"
, SUBSTR(JUMIN, 3, 4) -1 AS "BIRTHDAY -1"
FROM STUDENT
WHERE DEPTNO1 = 101;
--7)SUBSTRB():추출할 자리가 아닌 추출할 바이트수 지정
--첫번째 글자부터 3바이트 출력
SELECT '서진수' AS "NAME"
, SUBSTR('서진수', 1, 2) AS "SUBSTR"
, SUBSTRB('서진수', 1, 3) AS "SUBSTRB"
FROM DUAL;
--PG.77
--8)INSTR(): 주어진 문자열이나 컬럼에서 특정 글자의 위치를 찾아줌
--INSTR('Z','X',A,B) : Z라는 문자열에서 A번째 문자를 기준으로 B번째 X의 위치를 찾아라
SELECT 'A-B-C-D' AS 'A-B-C-D'
, INSTR('A-B-C-D', '-', 1, 3) AS "INSTR"
FROM DUAL;
--EX1)
SELECT 'A-B-C-D' AS 'A-B-C-D'
, INSTR('A-B-C-D', '-', 3, 1) AS "INSTR"
FROM DUAL;
--EX2)
--시작 지점을 -로 할 경우 오른쪽에서 왼쪽 역방향으로 탐색 : 2 나옴
SELECT 'A-B-C-D' AS 'A-B-C-D'
, INSTR('A-B-C-D', '-', -1, 3) AS "INSTR"
FROM DUAL;
--EX3)
--오른쪽에서 왼쪽 역방향으로 : 0 나옴
SELECT 'A-B-C-D' AS 'A-B-C-D'
, INSTR('A-B-C-D', '-', -6, 2) AS "INSTR"
FROM DUAL;
--P.78
--5TUDENT 테이블의 TEL 컬럼을 사용하여 1 전공번호(DEPTNO1)가 201 번인 학생의
--이름과 전화번호，')'가 나오는 위치를 출력하세요
SELECT NAME AS NAME
, TEL AS TEL
, INSTR(TEL, ')') AS INSTR(TEL, ')')
FROM STUDENT
WHERE DEPTNO1 = 201;
--P.79
--STUDENT 테이블에서 1 전공이 101 번인 학생들의 TEL 컬럼을 조회하여 3 이 첫번째로 나오는
--위치를 이름과 전화번호와 힘께 출력하세요.
--CF)없으면 0 출력
SELECT NAME AS NAME
, TEL AS TEL
, INSTR(TEL, '3') AS INSTR(TEL, '3')
FROM STUDENT
WHERE DEPTNO1 = 101;
--!!!SUBSTR/INSTR 퀴즈!!!
--!!!Q. STUDENT 테이블을 참조해서 아래 화면과 같이 1 전공이 (DEPTNO1 컬럼) 201 번인
--학생의 이름과 전화번호와 지역번호를 출력하세요.
--단，지역번호는 숫자만 나와야 합니다.
SELECT NAME AS NAME
, TEL AS TEL
, SUBSTR(TEL, 1, INSTR(')')) AS "AREA CODE"
FROM DUAL;
--PG80.
--9)LAPD(Z, A, 'B'): Z라는 문자열 또는 컬럼에 총 A자리가 될때까지 문자 B로 채운다
--!!!Q. STUDENT 테이블에서 1 전공이 201 번인 학과 학생들의 ID를 총 10자리로 줄럭하되
--왼쪽 빈자리는 '*' 기호로 채우세요.
SELECT NAME AS NAME
, ID AS ID
, LPAD(ID, 10, '*') AS LPAD
FROM STUDENT
WHERE DEPTNO1 = 201;
--Q.EMP 테이블을 사용하여 DEPTNO가 10번인 사원들의 사원 이름을 총 9바이트로
--줄력하되 빈자리에는 해당자리의 숫자로 채우세요.
SELECT LPAD(ENAME, 9, '1234567890') AS "LPAD"
FROM EMP
WHERE DEPTNO = 10;
--PG.81
--10)RPAD(): 오른쪽부터 해당 문자열로 채움
--Q.EMP 테이블에서 DEPTNO가 10번인 사원들의 ENAME을 10자리로 줄력하되
--오른쪽 빈자리에는 '-'로 채우세요.
SELECT RPAD(ENAME, 10, '-') AS "RPAD"
FROM EMP
WHERE DEPTNO = 10;
--PG.82
--!!!Q.EMP 테이블에서 DEPTNO가 10 번인 사원들의 이름을 종 9자리로 줄력하되
--오른쪽 빈자리에는 해당 자릿수에 해당되는 숫자가 출력되도록 하세요!!!
SELECT RPAD(ENAME, 9, SUBSTR('123456789', LENGTH(ENAME)+1)) AS "RPAD"
FROM EMP
WHERE DEPTNO = 10;
--11)LTRIM('Z','A'): Z라는 컬럼 혹은 문자에서 A라는 문자를 제거하여 출력
SELECT LTRIM(ENAME, 'C') AS "LTRIM"
FROM EMP
WHERE DEPTNO = 10;
--PG.83
--12)RTRIM(): LTRIM의 우측 버전
SELECT RTRIM(ENAME, 'R') AS "RTRIM"
FROM EMP
WHERE DEPTNO = 10;
--13)REPLACE('X','A','B'): 컬럼 혹은 문자열 X에서 A라는 문자를 B로 바꿈
SELECT ENAME AS "ENAME"
, REPLACE(ENAME, SUBSTR(ENAME, 1, 2), '**') AS "REPLACE"
FROM EMP
WHERE DEPTNO = 10;
--PG.84
--!!!Q.EMP 테이블에서 20번 부서에 소속된 직원들의 이름과 3~4번째 글자만 ’-’으로 변경해서 출력!!!
SELECT ENAME AS "ENAME"
, REPLACE(ENAME, SUBSTR(ENAME, 2, 2), ' --') AS "REPLACE"
FROM EMP
WHERE DEPTNO = 20;
--!!!Q.STUDENT 테이블에서 1 전공(DEPTNO1)이 101번인 학생들의 이름과 주민등록번호를
--출력하되 주민등록번호의 뒤 7자리는 '-'과 '/'로 표시되게 출력하세요.
SELECT NAME AS NAME
, JUMIN AS JUMIN
, REPLACE(JUMIN, SUBSTR(JUMIN, 7), '-/-/-/-') AS "REPLACE"
FROM STUDENT
WHERE DEPTNO1 = 101;
--PG.85
--!!!Q. STUDENT 테이블에서 야래 그림과 같이 1 전공이 102 번인 학생들의 이름과 전화번호，
--전화번호메서 국번 부분만 '*' 처리하여 줄력하세요.
--단，모든국번은 3자리로 간주합니다
SELECT NAME AS NAME
, TEL AS TEL
, REPLACE(TEL, SUBSTR(TEL, 5, 3), '***') AS "REPLACE"
FROM STUDENT
WHERE DEPTNO1 = 102;
--!!!Q.STUDENT 테이블에서 야래와 같이 DEPTNO1 이 101 변인 학과 학생들의 이름과 전화번호와
--전화번호에서 지역번호와 국번을 제외한 나머지 번호를 *로 표시해서 출력하세요.
SELECT NAME AS NAME
, TEL AS TEL
, REPLACE(TEL, SUBSTR(TEL, 9), '****') AS "REPLACE"
FROM STUDENT
WHERE DEPTNO1 = 101;
--P.86 숫자함수
--1)ROUND(): N번째 자리에서 반올림
--0이 소수점 첫번째이므로, 0일 경우 = 소수 첫번째 자리에서 반올림과 같은 뜻이 된다
SELECT ROUND(987.654, 2) AS "ROUND1"
, ROUND(987.654, 0) AS "ROUND2"
, ROUND(987.654, -1) AS "ROUND3"
FROM DUAL;
--2)TRUNC(): 무조건 버림
SELECT TRUNC(987.654, 2) AS "TRUNC1"
, TRUNC(987.654, 0) AS "TRUNC2"
, TRUNC(987.654, -1) AS "TRUNC3"
FROM DUAL;
--3)MOD(): 나머지 값
--4)CEIL(): 주어진 수와 가장 가까운 큰 정수 구하기
--5)FLOOR(): 주어진 수와 가장 가까운 작은 정수 구하기
SELECT MOD(121, 10) AS "MOD"
, CEIL(123.45) AS "CEIL"
, FLOOR(123.45) AS "FLOOR"
FROM DUAL;
--PG.88
--!!!EMP 테이블의 13건의 데이터를 출력하되 3줄씩 나누어서 한조로 만드는 예!!!
SELECT ROWNUM AS "ROWNO"
, CEIL(ROWNUM/3) AS "TEAMNO"
, ENAME AS "ENMAE"
FROM EMP;
--6)POWER(A,B): 숫자 A의 B승을 구해줌
SELECT POWER(2, 3) AS "POWER"
FROM DUAL;
--PG.89 날짜 관련 함수
--PG.90 1)SYSDATE: 오늘의 날짜 출력
SELECT SYSDATE AS "SYSDATE"
FROM DUAL;
--PG.91
--2)MONTHS_BETWEEN: 두 날짜를 입력받아 두 날짜 사이의 개월수를 출력하는 함수
SELECT MONTHS_BETWEEN('14/09/30', '14/08/31') AS "MONTH_BETWEEN"
FROM DUAL;
--PG.92
--단, 두 날짜 중 큰 날짜(최근)를 먼저 써야 양수가 나온다
--큰 날짜를 뒤에 쓰면 음수가 나온다
SELECT MONTHS_BETWEEN('14/08/31', '14/09/30') AS "MONTH_BETWEEN"
FROM DUAL;
--두 날짜가 같은 달에 속해 있으면 특정 규칙으로 계산된 값이 나옴(해당 월의 일자에 따라 다름)
SELECT MONTHS_BETWEEN('12/02/29', '12/02/01') AS "MONTH_BETWEEN"
FROM DUAL;
--여담으로 2월은 28일까지 있는 달과 29일 까지 있는 윤일은 구분하지 못한다
SELECT MONTHS_BETWEEN('14/03/01', '14/02/28') AS "MONTH_BETWEEN"
FROM DUAL;
--PG.94
--날짜 계산시 31일로 나누는 것과 MONTHS_BETWEEN은 차이가 나기에
--회사의 규칙 등을 잘 보고 적용시킬 것!
--PG.96
--3)ADD_MONTHS(): 주어진 날짜에 숫자만큼의 달을 더함
SELECT SYSDATE AS "SYSDATE"
, ADD_MONTHS(SYSDATE, 1) AS "ADD_MONTHS"
FROM DUAL;
--4)NEXT_DAY(): 주어진 날짜를 기준으로 돌아오는 가장 요일의 날짜를 반환
--리눅스용
SELECT SYSDATE AS "SYSDATE"
, NEXT_DAY(SYSDATE, 'MON') AS "NEXT_DAY"
FROM DUAL;
--윈도우용
SELECT SYSDATE AS "SYSDATE"
, NEXT_DAY(SYSDATE, '월') AS "NEXT_DAY"
FROM DUAL;
--특정 날짜를 기준으로 돌아오는 요일의 날짜를 찾고 싶을때
SELECT SYSDATE AS "SYSDATE"
, NEXT_DAY('14/05/01', '월') AS "NEXT_DAY"
FROM DUAL;
--PG.97
--5)LAST_DAY(): 주어진 날짜가 속한 달의 가장 마지막 날을 출력해주는 함수
SELECT SYSDATE AS "SYSDATE"
, LAST_DAY(SYSDATE) AS "LAST_DAY(SYSDATE)"
, LAST_DAY('14/05/01') AS "LAST_DAY(140501)"
FROM DUAL;
--PG.98
--6)ROUND(): 정오를 기준으로 날짜 반올림
--7)TRUNC(): 날짜의 TRUNC 함수는 무조건 당일로 출력
SELECT SYSDATE AS "SYSDATE"
, ROUND(SYSDATE) AS "ROUND"
, TRUNC(SYSDATE) AS "TRUNC"
FROM DUAL;
--PG.99 형 변환 함수
--PG.100
--묵시적(자동) 형 변환: 오라클이 자동으로 형 변환
--명시적(수동) 형 변환: 사람이 수동으로 형 변환
--묵시적 형 변환
SELECT 2 + '2' AS "4"
FROM DUAL;
--PG.102
--1)TO_CHAR: 날짜 → 문자로 형 변환
--년도
--YYYY: 연도를 4자리로 표현
--RRRR: 연도 4자리 표기법
--YY: 연도 끝의 2자리만 표기
--RR: 연도 끝의 2자리만 표기
--YEAR: 연도의 영문이름 전체를 표기
SELECT SYSDATE AS "SYSDATE"
, TO_CHAR(SYSDATE, 'YYYY') AS "YYYY"
, TO_CHAR(SYSDATE, 'RRRR') AS "RRRR"
, TO_CHAR(SYSDATE, 'YY') AS "YY"
, TO_CHAR(SYSDATE, 'RR') AS "RR"
, TO_CHAR(SYSDATE, 'YEAR') AS "YEAR"
FROM DUAL;
--PG.103
--월
--MM: 월을 숫자 2자리로 표기
--MON: 윈도우용 오라클은 MONTH와 동일, 리눅스의 경우 영어 3글자(OCT)로 표기
--MONTH: 월을 뜻하는 이름 전체를 표시
SELECT SYSDATE AS "SYSDATE"
, TO_CHAR(SYSDATE, 'MM') AS "MM"
, TO_CHAR(SYSDATE, 'MON') AS "MON"
, TO_CHAR(SYSDATE, 'MONTH') AS "MONTH"
FROM DUAL;
--PG.104
--일
--DD: 일을 숫자 2자리로 표기
--DAY: 요일에 해당하는 명칭을 표기하는데, 유닉스는 영문이며 윈도는 한글로 나옴
--DDTH: 몇 번째 날인지 표기
SELECT SYSDATE
, TO_CHAR(SYSDATE, 'DD') AS"DD"
, TO_CHAR(SYSDATE, 'DAY') AS "DAY"
, TO_CHAR(SYSDATE, 'DDTH') AS "DDTH"
FROM DUAL;
--시간
--HH24: 하루를 24시간으로 표기
--HH: 하루를 12시간으로 표기
--MI: 분 표기
--SS: 초 표기
SELECT SYSDATE AS "SYSDATE"
, TO_CHAR(SYSDATE, 'RRRR-MM-DD:HH24:MI:SS') AS "TO_CHAR"
FROM DUAL;
--PG.105
--!!!Q.STUDENT 테이블의 BIRTHDAY 컬럼을 사용하여 생일이 1월인 학생의 이름과 BIRLHDAY를 출력!!!
--생일이 1월인 것에 대해 TO_CHAR()의 'MM'기능을 써주기
SELECT STUDNO AS "STUDNO"
, NAME AS "NAME"
, BIRTHDAY AS "BIRTHDAY"
FROM STUDENT
WHERE TO_CHAR(BIRTHDAY, 'MM')=1;
--PG.106
--!!!Q.EMP 테이블의 HIREDATE 컬럼을 사용하여 입사일이 1, 2，3월인 사람들의 사번과 이름，입사일을 출력
SELECT EMPNO AS "EMPNO"
, ENAME AS "ENAME"
, HIREDATE AS "HIREDATE"
FROM EMP
WHERE TO_CHAR(HIREDATE, 'MM') IN (1
, 2
, 3);
--PG.107
--2)TO_CHAR: 숫자 → 문자로 형 변환
--!!!Q. EMP 테이블을 조회하여 이름이 ‘ALLEN’인 사원의 사번과 이름과 연봉을 출력
--단，연봉은 (SAL * 12) + COMM로 계산하고 천 단위 구분 기호로 표기할 것
--TO_CHAR(A,'999,999'): A를 출력할 경우, 자릿수 표기법
SELECT EMPNO AS "EMPNO"
, ENAME AS "ENAME"
, TO_CHAR(SAL*12 + COMM, '999,999') AS "SALARY"
FROM EMP
WHERE ENAME = 'ALLEN';
--!!!Q.PROFESSOR 테이블을 조회하여 201번 학과에 근무하는 교수들의 이름과 급여. 보너스. 연봉을 출력
--단，연봉은 (PAY*12)+BONUS로 계산
SELECT NAME AS "NAME"
, PAY AS "PAY"
, BONUS AS "BONUS"
, TO_CHAR((PAY*12)+BONUS, '999,999') AS "TOTAL"
FROM PROFESSOR
WHERE DEPTNO = 201;
--PG.108
--!!!Q.EMP 테이블을 조회하여 COMM 값을 가지고 있는 사람들의 EMPNO, ENAME, HIREDATE,
--총 연봉 15% 인상 후 연봉을 출력
--단. 총 연봉은 (SAL*12)+CΑMM으로 계산하고 SAL로 출력되었으며
--15% 인상한 값은 총 연봉의 15% 인상 값
--(HIREDATE 컬럼의 날짜 형식과 SAL 컬럼， 15%UP 컬럼의 $ 표시와 기호가 나오게 하세요)
SELECT EMPNO AS "EMPNO"
, ENAME AS "ENAME"
, HIREDATE AS "HIREDATE"
, TO_CHAR(SAL*12+COMM, '$999,999') AS "SAL"
, TO_CHAR((SAL*12+COMM)*115/100, '$999,999') AS "15% UP"
FROM EMP
WHERE COMM IS NOT NULL;
--3)TO_NUMBERE(): 숫자가 아닌 숫자처럼 생긴 문자를 숫자로 바꾸어줌
SELECT TO_NUMBER('5') AS "TO_NUMBER"
FROM DUAL;
--PG.109
--4)TO_DATE(): 날짜가 아닌 날짜처럼 생긴 문자를 날짜로 바꾸어줌
SELECT TO_DATE('2014/05/31') AS "TO_DATE"
FROM DUAL;
--PG.110 일반 함수
--1)NVL(): NULL 값을 만나면 다른 값으로 치환해서 출력하는 함수
SELECT ENAME AS "ENAME"
, COMM AS "COMM"
, NVL(COMM, 0) AS "NVL0"
, NVL(COMM, 100) AS "NVL100"
FROM EMP
WHERE DEPTNO = 30;
--PG.111
--Q.PROFESSOR 테이블에서 201 번 학과 교수들의 이름과 급여，BONUS, 총 연봉을 출력
--단，총 연봉은 (PAY*12+BONUS)로 계산하고 BONUS가 없는 교수는 0으로 계산
SELECT PROFNO AS "PROFNO"
, NAME AS "NAME"
, PAY AS "PAY"
, BONUS AS "BONUS"
, TO_CHAR(PAY*12 + NVL(BONUS, 0), '999,999') AS "TOTAL"
FROM PROFESSOR
WHERE DEPTNO = 201;
--2)NVL2(A,B,C): A의 값이 NULL이 아니면 B를, NULL이면 C를 출력
--Q.EMP 테이블에서 DEPTNO가 30번인 사람들의 EMPNO, ENAME， SAL, COMM 값을 출력하되
--COMM값이 NULL이 아니면 SAL + COMM 값을 출력하고 COMM 값이 NULL이면 SAL * 0의 값을 출력
SELECT EMPNO AS "EMPNO"
, ENAME AS "ENAME"
, SAL AS "SAL"
, COMM AS "COMM"
, NVL2(COMM, SAL+COMM, SAL * 0) AS "NVL2"
FROM EMP
WHERE DEPTNO = 30;
--PG.113
--EMP 테이블에서 DEPTNO가 30번인 사원들을 조회하여 COMM 값이 있을 경우
--'EXIST'를 출력하고 COMM 값이 NULL일 경우 ‘NULL’을 출력하세요
SELECT EMPNO AS "EMPNO"
, ENAME AS "ENAME"
, COMM AS "COMM"
, NVL2(COMM, 'EXIST', 'NULL') AS "NVL2"
FROM EMP
WHERE DEPTNO = 30;
--3)DECODE(): IF문 사용해야 하는 조건문을 처리
--DECODE(A, B, '1', NULL): A가 B일 경우, 1을 출력하는 경우
--A가 B가 아니라면 NULL 출력 (마지막 NULL은 생략가능)
--PG.114
--!!!Q.PROFESSOR 테이블에서 학과번호와 교수명，학과명을 줄력하되 DEPTNO가 101 번인
--교수만 학과명을 "COMPUTER ENGINEERING"으로 출력하고 101 번이 아닌 교수들은
--학과명에 아무것도 줄력하지 마세요
SELECT DEPTNO AS "DEPTNO"
, NAME AS "NAME"
, DECODE(DEPTNO, 101, 'COMPUTER ENGINEERING') AS "DNAME"
FROM PROFESSOR;
--PG.115
--DECODE(A, B, 1, 2): A가 B이면 1을, 아니면 2를 출력
--Q. PROFESSOR 테이블에서 학과번호와 교수명과 학과명을 줄력하되 DEPTNO가 101번인
--교수만 "COMPUTER ENGINEERING"으로 줄력하고 101 번이 아닌 교수들은 학과명에 "ETC"로 출력하세요
SELECT DEPTNO AS "DEPTNO"
, NAME AS "NAME"
, DECODE(DEPTNO, 101, 'COMPUTER ENGINEERING', 'ETC') AS "DNAME"
FROM PROFESSOR;
--PG.116
--DECODE(A, B, 1, C, 2, 3): A가 B면 1, C이면 2, 아니면 3을 출력
--Q. PROFESSOR 테이블에서 교수의 이름과 학과명을 줄력하되 학과 번호가 101 번이면
--'COMPUTER ENGINEERING’，102번이면 'MULTIMEDIA ENGINEERING ’，
--103번이면 "SOFTWARE ENGINEERING" 나머지는 'ETC’로 출력
SELECT DEPTNO AS "DEPTNO"
, NAME AS "NAME"
, DECODE(DEPTNO, 101, 'COMPUTER ENGINEERING', 102, 'MULTIMEDIA ENGINEERING', 103, 'SOFTWARE ENGINEERING', 'ETC') AS "DNAME"
FROM PROFESSOR;
--PG.117
--DECODE(A, B, DECODE(C, D, 1, NULL))
--A가 B일 경우 중에서 C가 D를 만족하면 1을 출력하고 C가 D가 아니면 NULL을 출력
--Q.PROFESSOR 테이블에서 교수의 이름과 부서번호를 줄력하고 101 번 부서 중에서
--이름이 "AUDIE MURPHY" 교수에게 "BEST!" 라고 줄력하고 101 번 부서 중에서
--이름이 "AUDIE MURPHY" 교수가 아닌 나머지에는 NULL 값을 줄력하세요.
--만약 101 번 외 다른 학과에 "AUDIE MURPHY" 교수가 있어도 "BEST!"가 줄력되면 안됩니다.
SELECT DEPTNO AS "DEPTNO"
, NAME AS "NAME"
, DECODE(DEPTNO, 101, DECODE(NAME, 'AUDIE MURPHY', 'BEST!')) AS "ETC"
FROM PROFESSOR;
--PG.118
--DECODE(A, B, DECODE(C, D, 1, 2))
--A가 B일 경우 중에서 C가 D를 만족하면 1, 아니면 2를 출력
--Q. PROFESSOR 테이블에서 교수의 이름과 부서변호를 줄력하고 101 번 부서 중에서
--이름이 "AUDIE MURPHY" 교수의 비고란에 “BEST!"라고 출력한 다음 101 번 학과의
--"AUDIE MURPHY" 교수 외에는 비고란에 “GOOD!"을 출력하고 101 번 교수가 아닐
--경우는 비고란이 공란이 되도록 줄력
SELECT DEPTNO AS "DEPTNO"
, NAME AS "NAME"
, DECODE(DEPTNO, 101, DECODE(NAME, 'AUDIE MURPHY', 'BEST!', 'GOOD!')) AS "ETC"
FROM PROFESSOR;
--PG.119
--DECODE(A, B, DECODE(C, D, 1, 2), 3)
--A가 B일 경우 중에서 C가 D를 만족하면 1, 아니면 2를 출력하며, A가 B가 아니면 3을 출력
--Q. PROFESSOR 테이블에서 교수의 이름과 부서변호를 줄력하고 101 번 부서 중에서
--이름이 "AUDIE MURPHY" 교수의 비고란에 “BEST!"라고 출력한 다음 101 번 학과의
--"AUDIE MURPHY" 교수 외에는 비고란에 “GOOD!"을 출력하고 101 번 교수가 아닐
--경우는 비고란에 "N/A" 줄력
SELECT DEPTNO AS "DEPTNO"
, NAME AS "NAME"
, DECODE(DEPTNO, 101, DECODE(NAME, 'AUDIE MURPHY', 'BEST!', 'GOOD!'), 'N/A') AS "ETC"
FROM PROFESSOR;
--PG.120
--!!!Q.STUDENT 테이블을 사용하여 제1 전공(DEPTNO1)이 101 번인 학과 학생들의
--이름과 주민번호，성별을 출력하되 성별은 주민번호(JUMIN) 컬럼을 이용하여
--7번째 숫자가 1 일 경우 “MAN"， 2일 경우 “WOMAN”으로 출력!!!
SELECT NAME AS "NAME"
, JUMIN AS "JUMIN"
, DECODE(SUBSTR(JUMIN, 7, 1), 1, 'MAN', 'WOMAN') AS "GENDER"
FROM STUDENT
WHERE DEPTNO1 = 101;
--!!!Q.STUDENT 테이블에서 1 전공이(DEPTNO1) 101번인 학생의 이름과 연락처와 지역을 출력
--단，지역번호가 02는 “SEOUL"， 031은 “GYEONGGI", 051은 “BUSAN"， 052는 “ULSAN"，055는 “GYEONGNAM"!!!
SELECT NAME AS "NAME"
, TEL AS "TEL"
, DECODE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1), 02, 'SEOUL', 031, 'GYEONGGI', 051, 'BUSAN', 052, 'ULSAN', 055, 'GYEONGNAM') AS "LOC"SELECT EMPNO
, ENAME AS "ENAME"
, HIREDATE AS "HIREDATE"
FROM EMP
WHERE TO_CHAR(HIREDATE, 'MM') IN ('1'
, '2'
, '3');
--PG.121
--4)CASE: DECODE는 주로 =인 것을 처리
--즉, 크거나 작은 조건일 때는 불편하여 CASE를 자주 사용
--CASE에는 조건문끼리 ,를 사용하지 않음!!!
--Q.STUDENT 테이블을 참조하여 DEPTNO1이 201번인 학생의 이름과 전화번호，지역멍을 출력
--단，지역번호가 02면 "SEOUL", 031이면 "GYEONGGI", 051 이면 "BUSAN", 052이면 "ULSAN",
--055이면 "GYEONGNAM", 나머지는 "ETC"로 표시
SELECT NAME AS "NAME"
, TEL AS "TEL"
, CASE(SUBSTR(TEL, 1, INSTR(TEL, ')')-1))
WHEN '02' THEN 'SEOUL'
WHEN '031' THEN 'GYEONGGI'
WHEN '051' THEN 'BUSAN'
WHEN '052' THEN 'ULSAN'
WHEN '055' THEN 'GYEONGNAM'
ELSE 'ETC'
END "LOC"
FROM STUDENT
WHERE DEPTNO1 = 201;
--PG.122
--비교 조건이 '='가 아닌 경우
--Q.STUDENT 테이블의 JUMLN 컬럼을 참조하여 학생들의 이름과 태어난 달이 01~03월은 1/4，
--04~06월은 2/4，07~09월은 3/4, 10-12는 4/4로 출력
SELECT NAME AS "NAME"
, SUBSTR(JUMIN, 3, 2) AS "MONTH"
, CASE
WHEN SUBSTR(JUMIN, 3, 2) BETWEEN '01' AND '03' THEN '1/4'
WHEN SUBSTR(JUMIN, 3, 2) BETWEEN '04' AND '06' THEN '2/4'
WHEN SUBSTR(JUMIN, 3, 2) BETWEEN '07' AND '09' THEN '3/4'
WHEN SUBSTR(JUMIN, 3, 2) BETWEEN '10' AND '12' THEN '4/4'
END "QUARTER"
FROM STUDENT;
--PG.123
--Q.EMP 테이블을 조회하여 EMPNO, ENAME, SAL, LEVEL(급여등급}을 출력
--단，급여등급은 SAL을 기준으로 1 ~ 1000이면 LEVEL 1, 1001 ~ 2000이면 LEVEL 2, 2001 ~ 3000이면
--LEVEI 3, 3001 ~ 4OOO이면 LEVEL 4, 4001보다 많으면 LEVEL 5로 출력
SELECT EMPNO AS "EMPNO"
, ENAME AS "ENAME"
, SAL AS "SAL"
, CASE
WHEN SAL BETWEEN 1 AND 1000 THEN 'LEVEL 1'
WHEN SAL BETWEEN 1001 AND 2000 THEN 'LEVEL 2'
WHEN SAL BETWEEN 2001 AND 3000 THEN 'LEVEL 3'
WHEN SAL BETWEEN 3001 AND 4000 THEN 'LEVEL 4'
WHEN SAL > 4001 THEN 'LEVEL 5'
END "LEVEL"
FROM EMP;
--PG.124 정규식(REGULAR EXPRESSION) 함수로 다양한 조건 조회
--PG.125
--1)REGEXP_LIKE: LIKE 함수처럼 특정 패턴과 매칭되는 결과를 검색해내는 함수
--EX1)영문자가 들어가 있는 행만 출력하기 : 소문자
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z]');
--EX2)영문자가 들어가 있는 행만 출력하기 : 대문자
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z]');
--EX3)영문자가 들어가 있는 행만 출력하기 : 대소문자
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-ZA-Z]');
--EX4)소문자로 시작하고 공백을 포함하는 경우
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z] ');
--PG.127
--EX5)소문자로 시작하고 공백을 포함하며 숫자로 끝나는 행을 찾기
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z] [0-9]');
--EX6)공백이 있는 데이터를 모두 찾고 싶은 경우
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[[:SPACE:]]');
--EX7)연속적인 글자 수 지정하기
--대문자가 연속으로 3글자 오는 경우를 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z]{3}');
--PG.128
--EX8)숫자가 연속적으로 3글자 오는 행 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[0-9]{3}');
--PG.129
--EX9)영어 대문자와 숫자가 함께 오는데, 영어 대문자와 숫자가 각각 3글자가 오는 행 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z][0-9]{3}');
--EX10)대문자와 숫자가 함께 나오되, 숫자가 먼저 나오는 것을 검색
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[0-9][A-Z]{3}');
--EX11)대문자가 들어가는 모든 행을 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z]');
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[[:UPPER:]]');
--PG.130 특정 위치를 지정하여 출력하기
--시작되는 문자 지정: ^(캐럿)
--끝나는 문자 지정: $(달러)
--EX1)첫 시작을 대문자나 소문자로 하는 행을 모두 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[A-ZA-Z]');
--EX2)첫 시작을 숫자나 대문자로 시작하는 모든 행 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[A-Z0-9]');
--EX3)첫 시작을 숫자나 소문자로 시작하는 모든 행 출력
-- |(바)를 사용해 여러 조건을 이을 수도 있다
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[A-Z]|^[0-9]');
--PG.131
--EX4)학생의 ID중 첫 글자가 M으로 시작하고, 두번째 글자가 A나 O가 오는 ID를 이름과 함께 추출
SELECT NAME
, ID
FROM STUDENT
WHERE REGEXP_LIKE(ID, '^M(A|O)');
--EX5)$문자 사용하여 소문자로 끝나는 행을 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[A-Z]$');
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[[:ALPHA:]]$');
--PG.132
--만약 ^문자가 대괄호 안에 들어갈 경우, 다른 것만 출력하라는 역접의 의미로 바뀜
--EX6)소문자로 시작하지 않는 행 모두 추출
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[^A-Z]');
--PG.133
--EX7)숫자로 시작하지 않는 행
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[^0-9]');
--EX8)소문자도 아니고 숫자도 아닌 것으로 시작하는 행
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '^[^0-9A-Z]');
--PG.134
--EX9)위치에 상관없이 "소문자만" 들어있는 행을 모두 제거
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '[^A-Z]');
--EX10)소문자가 들어있는 모든 행 제거
SELECT *
FROM T_REG
WHERE NOT REGEXP_LIKE(TEXT, '[A-Z]');
--PG.135
--EX11)STUDENT 테이블에서 지역번호가 2자리이고 다음 국번이 연속적으로 4자리 나오는 값 출력
SELECT NAME
, TEL
FROM STUDENT
WHERE REGEXP_LIKE(TEL, '^[0-9]{2}\)[0-9]{4}');
--EX12)STUDENT 테이블에서 학생의 ID를 조사해서 4번째 자리에 R(소문자)가 있는 행 출력
SELECT NAME
, ID
FROM STUDENT
WHERE REGEXP_LIKE(ID, '^...R.');
--PG.136
--EX13)IP주소가 10.10.10.1인 행만 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(IP, '^[10]{2}\.[10]{2}\.[10]{2}');
--EX14)172.16.168.XXX의 IP 출력
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(IP, '^[172]{3}\.[16]{2}\.[168]{3}');
--PG.137 특정 조건을 제외한 결과 출력
--EX1)대소문자를 포함하지 않는 모든 행 출력
SELECT *
FROM T_REG
WHERE NOT REGEXP_LIKE(TEXT, '[A-ZA-Z]');
--EX2)숫자를 포함하지 않는 모든 행
SELECT *
FROM T_REG
WHERE NOT REGEXP_LIKE(TEXT, '[0-9]');
--특수문자 찾기
--?나 * 등은 '모든 것'이라는 뜻을 가진 메타-캐릭터 문자이기에 다로 적용할 경우 전체 출력됨
--따라서 기호임을 명시해야 됨
--ESCAPE CHARACTER 이스케이프 문자
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '\?');
SELECT *
FROM T_REG
WHERE REGEXP_LIKE(TEXT, '\*');
--PG.139
--EX1)?문자가 들어가지 않은 모든 행 출력
SELECT *
FROM T_REG
WHERE NOT REGEXP_LIKE(TEXT, '\?');
--PG.141
--REGEXP_REPLACE: 주어진 문자열에서 특정 패턴을 찾아서 주어진 다른 모양으로 치환하는 함수
--EX1)모든 숫자를 특수 기호로 변경하기
SELECT TEXT
, REGEXP_REPLACE(TEXT, '[[:DIGIT:]]', '*') "NO → CHAR"
FROM T_REG;
--PG.142 특정 패턴을 찾아서 패턴 변경하기
--EX2)숫자를 찾아서 그 뒤에 -*를 추가
SELECT TEXT
, REGEXP_REPLACE(TEXT, '([0-9])', '\1-*') "ADD CHAR"
FROM T_REG;
--EX3)IP의 .을 삭제
SELECT NO
, IP
, REGEXP_REPLACE(IP, '\.', '') "DOT REMOVE"
FROM T_REG;
--PG.143
--EX4)첫번째 .만 '/' 기호로 변경
SELECT NO
, IP
, REGEXP_REPLACE(IP, '\.', '/', 1, 1) "REPLACE"
FROM T_REG;
--EX5)사용자에게 입력받은 문자에 공백이 여럿 들어있을 때 공백 제거
--{} 내의 숫자는 앞문자가 나타나는 횟수 또는 범위
SELECT REGEXP_REPLACE('AAA BBB', '( ){1}', '')
FROM T_REG;
--PG.144
--EX6)두칸짜리 공백 지우기
--( )가 2번 연속으로 이어진 것을 찾아서 바꿔라
SELECT REGEXP_REPLACE('AAA BBB', '( ){2}', '') "ONE"
, REGEXP_REPLACE('AAA BBB', '( ){2}', '') "TWO"
FROM T_REG;
--EX7)공백이 두칸 이상인 문자에서 공백을 '*' 한개로 치환
SELECT REGEXP_REPLACE('AAA BBB', '( ){2}', '*') "ONE"
, REGEXP_REPLACE('AAA BBB', '( ){2}', '*') "TWO" REGEXP_REPLACE('AAA BBB', '( ){2}', '*') "THREE"
FROM T_REG;
--PG.145
--[0-9]{2}: 두자리 숫자
--ABC[7-9]{2}: ABC77, ABC87, ABC97 등등
--EX8)사용자가 검색어를 입력할 때 공백 문자를 가장 먼저 입력하고 아이디 중간에도
--공백이 있어서 모든 공백을 제거하기
SELECT STUDNO
, NAME
, ID
FROM STUDENT
WHERE ID = REGEXP_REPLACE('&ID', '( ){1}', '');
--PG.146
--EX9)위의 예제에서 공백 제거후 소문자로 변경
SELECT STUDNO
, NAME
, ID
FROM STUDENT
WHERE ID = LOWER(REGEXP_REPLACE('&ID', '( ){1}', ''));
--EX10)특정 문자열의 형태를 다른 형태로 바꿀 때 ‘20120324’ 형태로 이루어진 데이터를
--‘2012: 03: 24’의 형태로 변형하기
SELECT REGEXP_REPLACE('20141023', '([[:DIGIT:]]{4})([[:DIGIT:]]{2})([[:DIGIT:]]{2})', '\1-\2-\3'
FROM DUAL;
--PG.147
--REGEXP_SUBSTR 함수: 특정 패턴에 주어지는 문자를 추출해내는 함수
--EX1)주어진 문자열에서 첫 글자가 공백이 아니고( ’[^ ]’) 그 후에 ‘DEF’가 나오는 부분을 추출
SELECT REGEXP_SUBSTR('ABC* *DEF $GHI%KJL', '[^ ]+[DEF]')
FROM DUAL;
--EX2)교수테이블(PROFESSOR)테이블에서 홈페이지(HPAGE) 주소가 있는 교수들만 조사해서 출력
SELECT NAME, LTRIM(REGEXP_SUBSTR(HPAGE, '/([[:ALNUM:]]+\.?){3,4}?'), '/') "URL"
FROM PROFESSOR
WHERE HPAGE IS NOT NULL ;
--EX3)PROFESSOR 테이블에서 101번 학과와 201번 학과 교수들의 이름과 메일 주소의 도메인 주소를 출력
--단 메일 주소는 @뒤에 있는 주소만 출력
SELECT NAME, LTRIM(REGEXP_SUBSTR(EMAIL, '@([[:ALNUM:]]+\.?){3,4}?'), '@') DOMAIN
FROM PROFESSOR
WHERE DEPTNO IN (101, 201);
--EX4)특정기호나 문자를 기준으로 데이터를 추출할 때
SELECT REGEXP_SUBSTR('SYS/ORACLE@RACDB:1521:RACDB', '[^:]+', 1, 3) RESULT
FROM DUAL;
--PG.149
--EX5)슬래쉬를 기준으로 출력
SELECT REGEXP_SUBSTR('SYS/ORACLE@RACDB:1521:RACDB', '[^/:]+', 1, 2) RESULT
FROM DUAL;
--PG.150 11G에서 추가된 정규식
--EX6)주어진 문자열에서 대문자 'A'가 몇개인지 찾아 주는 예
SELECT TEXT, REGEXP_COUNT(TEXT, 'A')
FROM T_REG;
--EX7)검색 위치를 3으로 지정해서 3번째 문자 이후부터 해당 소문자 ‘C’ 가 나오는 개수를 세는 예
SELECT TEXT, REGEXP_COUNT(TEXT, 'C', 3)
FROM T_REG;
--EX8)대소문자 구분 없이 (즉 'C' 와 'C' 모두) 몇 개가 나오는 지 세어 출력
SELECT TEXT, REGEXP_COUNT(TEXT, 'C') "RESULT 1", REGEXP_COUNT(TEXT, 'C', 1, 'I') "RESULT 2"
FROM DUAL;
--PG.151
--EX9)어떤 문자를 찾을 때 사용하는 3가지 방법
SELECT TEXT, REGEXP_COUNT(TEXT, 'AA') RESULT1, REGEXP_COUNT(TEXT, 'A{2}') RESULT2, REGEXP_COUNT(TEXT, '(A)(A)') RESULT3
FROM T_REG;
댓글