-
명령어 버퍼(command buffer) : SQL*PLUS가 현재의 SQL문을 보관하는 기억공간
- 특징: 각각의 줄은 번호가 부여, 명령어 버퍼의 내용을 편집/호출/저장 가능
-
편집명령어
- L(LIST) : 명령어 버퍼의 내용을 보기
- / 또는 R(RUN) : 버퍼에 저장된 쿼리를 실행 (두 개의 차이점은?)
- ED(EDIT) : 버퍼에 저장된 명령어를 메모장에서 편집(주의 ;가 /로 대체) ~ afiedt.buf
- HOST : 현 운영체제의 명령어 입력 프롬포트 실행
-
SAVE : 최근에 수행한 쿼리문 저장(확장자 생략시 sql이 자동 붙음)
-
지정한 파일이 이미 존재하면? 에러 -> REPLACE 옵션 사용
- SQL> SAVE q1.sql REPLACE
-
파일로 저장된 쿼리문 실행? 파일명 앞 @ 붙임
- SQL> @q1
-
- GET : 저장된 쿼리문을 버퍼에 불러옴(/로 실행)
-
SPOOL : 화면을 갈무리
- 시작: SPOOL 파일명
- 끝: SPOOL OFF
- N : N을 편집될 현재의 줄로 만듦(N은 숫자를 의미)
- I(INSERT) : 현재의 줄 다음에 새로운 줄을 삽입. 새로운 줄이현재의 줄이 됨
- DEL(DELETE) : 현재의 줄을 삭제
-
A(APPEND) text : 현재의 줄에 텍스트를 첨가 라인의 마지막에 text를 추가
-
주의 사항: ~~~E라는 줄에 ABC를 추가하여 "~~~E ABC"로 만드려면 공백' ' 을 2칸을 쓴다
- 예:바뀌기 전 행 내용: select empno, ename
- SQL> A, sal
- 1* select empno, ename ,sal
-
- C[HANGE] / old_text/new_text : old_text를 new_text로 변경
- C[HANGE] /text/ : 현재 행으로부터 text를 삭제
- CL(CLEAR) BUFF(BUFFER) : SQL버퍼로부터 모든 라인을 삭제
-
형식 지정
-
COLUMN - 컬럼에 대한 설정된 값을 확인
- SQL> COLUMN empno
-
COLUMN HEADING - 컬럼 제목을 설정
- SQL> COLUMN empno HEADING '사원번호'
- COLUMN 컬럼명 CLEAR - 컬럼에 설정된 갑을 해제
-
COLUMN FORMAT - 컬럼 제목의 출력형식을 변경
- SQL> COLUMN dname FORMAT A20 // 컬럼의 너비를 20으로 지정
- SQL> COLUMN sal FORMAT 0,000,000 // 숫자 데이터를 총 7자리로 출력, 세자리마다 콤마(,)로 구분
- SQL> COLUMN sal FORMAT 999999 // 원상복귀
-
-
시스템 변수 설정
- 형식) SET 시스템변수명 값
- HE
- ADING : 컬럼 제목의 출력 여부를 결정(값: ON/OFF)
- LINESIZE : 한 화면에 출력되는 라인의 수 결정(값: 숫자)
- PAGESIZE : 한 페이지에 출력되는 페이지 크기 결정(값: 숫자)
-
테이블 정의 조사하기
- DESC(RIBE) 이용
- 예)
- SQL> describe egw_emp
Name Type Nullable Default Comments
-------------- -------------- -------- ------- --------
EMP_ID NUMBER(15)
LAST_NAME VARCHAR2(512)
FIRST_NAME VARCHAR2(2048) Y
SALARY NUMBER(15) Y
TITLE VARCHAR2(32) Y
COMMISSION_PCT NUMBER(15) Y
DEPT_ID NUMBER(15)
START_DATEDATE DATE Y
- Name: 컬럼명, Type: 컬럼의 데이터형과 길이, Nullable: 널이 가능한 여부, Default: 기본값, Comments: 설명
-
데이터형
-
숫자(NUMBER) 데이터형 : 0/음수/양수, 고정 소수점/부동 소수점의 수를 저장
- NUMBER (precision, scale)
- precision: 38자리까지의 정밀도
- scale : 양수(소수점 이하 자리 수), 음수(소수점 위의 자리를 0으로 처리하는 자리 수)
-
|
실제 데이터 |
정의 | 저장되는 데이터 |
|---|---|---|
| 123456.789 | Number(8,2) | 123456.79 |
| 123456.789 | Number(8) | 123456.7 |
| 123456.789 | Number(8,-2) | 123500 |
| 123456.789 | Number | 123456.789 |
-
날짜(DATE) 데이터형 : 날짜 및 시간 데이터를 저장
- 세기(Century), 년(Year), 월(Month), 일(Day), 시간(Hour), 분(Minute), 초(Second)에 대한 정보 포함
- 범위 : B.C. 4712년 1월 1일 ~ A.D. 9999년 12월 31일
- 예) OS의 날자 조회 하기
- SQL> SELECT sysdate FROM dual;
SYSDATE
-----------
2007-11-08
-
DUAL 테이블 :
- SYS사용자의 소유 테이블(SYS.DUAL), 모든 사용자가 질의 가능
- DUMMY라는 단 하나의 컬럼에 X라는 단 하나의 로우만 가짐
-
일시적인 산술연산, 가상 컬럼 등의 값을 보고 싶을 때 이용가능
- 실습! dual 대신에 실제 존재하는 테이블의 이름을 넣으면?
-
한글 '월'~'일' 사용:
- SQL> ALTER SESSION SET NLS_LANGUAGE=Korean;
-
문자 데이터형(CHAR, VARCHAR2, LONG)
-
CHAR 데이터형 : 고정 길이 문자열 저장: 1~255 바이트
- 입력하고 남은 길이는 빈칸으로 채워짐(저장 공간 낭비 고려 필요)
-
VARCHAR2 데이터형 : 2000 바이트까지 저장 (메모 등에 이용)
- 가변적인 길이의 문자열 저장
-
LONG 데이터형 : 2GB까지 길이가 허용
- 제약조건: 테이블 중의 한 컬럼만이 정의가능, 인덱스로 정의 불가, 프로시저로 파라미터 못 넘김/리턴되는 함수를 이용 불가, WHERE/ORDER BY/GROUP BY/CONNECT BY절에 사용 불가
- CLOB : 4GB까지 문자 저장
-
-
이진(Binary) 데이터형
- RAW : 255바이트까지 수용
-
LONG RAW: 각각의 로우에 대해 2GB까지 수용
- 제약조건: 어떠한 내장 함수도 사용 불가
- BLOB : 4GB까지 저장
- BFILE : 외부파일에 4GB까지 저장
-
ROWID : 로우에 대한 논리적 주소(DB상 유일)
- SQL> SELECT ROWID FROM DEMONAMO;
ROWID
------------------
AAGwp+AAqAAAEClAAA
- 문자구성: A~Z, a~z, 0~9, +, /
- 네 부분으로 구성: 6자리(객체번호), 3자리(파일번호), 6자리(블록번호), 3자리(슬롯 번호)
- 대용량 데이터베이스(Very Large Database:VLDB) 지원을 위한 파티셔닝과 관련
- WHERE절, SELECT절에서 사용 가능
- 익스포트/임포트를 통해서만 변경 가능(DML(I/U/D)을 통해서는 불가)
- SQL> SELECT ROWID FROM DEMONAMO;
-
단일행 함수(Single-row function): 적용(단일행)-> 결과(하나)
- 중첩사용 가능: 가장 안의 함수부터 실행 예) Func1(Func2(Func3( , ) , ) .. ) => Func3() , Func2(), Func1()의 순
-
문자 처리 함수
-
LOWER - 문자열을 모두 소문자로 변경 예) LOWER('SQL Plus') -> sql plus
-
테스트는 더미테이블(DUAL)로 해볼 수 있다.
- SQL> select LOWER('SQL Plus') from dual;
LOWER('SQLPLUS')
----------------
sql plus
- SQL> select LOWER('SQL Plus') from dual;
-
- UPPER - 문자열을 모두 대문자로 변경 예) LOWER('SQL Plus') -> SQL PLUS
- INITCAP - 문자열에 속한 각 단어의 첫 글자를 대문자, 나머지는 소문자로 변경 예) LOWER('SQL Plus') -> Sql Plus
-
CONCAT - 두 문자열을 합쳐줌 예) CONCAT('SQL', 'Plus') ------> SQL Plus
- 합성연산자('||')와 같은 역할을 하나 인수를 2개 밖에 가질 수 없다는 차이가 있음
-
SUBSTR - 지정된 위치에서 길이만큼의 문자열을 추출 예) SUBSTR('SQL Plus', 2, 4) ----> QL P
- 두 번째 인자 음수 가능 : 문자열의 긑에서부터 추출
- 세 번째 인자 생략 가능: 생략시 문자열의 끝가지 추출
- LENGTH - 문자열의 길이 반환 예) LENGTH('SQL Plus') ------> 8
-
INSTR - 특정 문자의 위치 반환 예) INSTR('SQL Plus', 'Q') -------> 2
- 문자가 여러곳에 위치할 시: 첫 번째 자릿수만 반환
- 0 : 지정된 문자를 찾을 수 없는 경우의 반환값
- LPAD - 왼쪽에 지정된 문자를 지정된 길이만큼 채움 예) LPAD('SQL Plus',15, '#') -----> #######SQL Plus
- RPAD - 오른쪽에 지정된 문자를 지정된 길이만큼 채움 예) RPAD('SQL Plus',15, '#') ----> SQL Plus#######
-
-
숫자 처리 함수
- ROUND - 지정된 소수점 위치까지 값을 반올림 예) ROUND(123.45,1) ------> 123.5
- TRUNC - 지정된 소수점 위치까지 값을 절삭(잘라냄) 예) TRUNC(123.45,-1) ------> 120
- FLOOR - 뒤에 나오는 인수보다 같거나 작은 정수를 반환 예) FLOOR(123.45) ------>123
- CEIL - 뒤에 나오는 인수보다 가거나 큰 정수를 반환 예) CEIL(123.45) ------>124
- MOD- 첫 번째 인수를 두 번째 인수로 나누었을 때의 나머지 반환 예) MOD(5, 2) -----> 1 // 짝/홀수 판단에 응용...
-
날짜 처리 함수 : 내부적으로 숫자값과 같이 저장 -> 날짜 데이터끼리 산술연산 가능
범위 : B.C. 4712년 1월 1일 ~ A.D. 9999년 12월 31일-
SYSDATE - 현재의 날짜와 시간을 보여줌
- SQL> SELECT sysdate from dual;
SYSDATE
-----------
2007-11-08
- SQL> SELECT sysdate from dual;
-
ADD_MONTHS - 첫 번째 인수가 나타내는 날짜에 두 번째 인수가 나타내는 달수를 더한 날자를 반환
- SQL> select add_months(sysdate, -5) from dual;
ADD_MONTHS(SYSDATE,-5)
----------------------
2007-06-08 오전 11:38:
- 5개월 전의 날짜
- SQL> select add_months(sysdate, -5) from dual;
-
MONTHS_BETWEEN - 첫 번재 날짜와 두 번째 날짜 사이의 달수를 계산(첫 번재 날짜 - 두 번째 날짜)
- SQL> select MONTHS_BETWEEN(SYSDATE,'1992-1-1') from dual;
MONTHS_BETWEEN(SYSDATE,'1992-1
------------------------------
190.241535991637
- 1992년 1월 1일부터 현재 시스템 날짜 사이의 달수
- SQL> select MONTHS_BETWEEN(SYSDATE,'1992-1-1') from dual;
- NEXT_DAY - 첫 번째 인수가 나타내는 날짜 다음으로 나타나는 두 번째 인수의 요일에 대한 날짜 반환
-
LAST_DAY - 지정된 날짜를 포함하고 있는 달의 끝 날짜를 반환
- SQL> select NEXT_DAY(sysdate, 1), LAST_DAY(sysdate) from dual;
NEXT_DAY(SYSDATE,1) LAST_DAY(SYSDATE)
------------------- -----------------
2007-11-11 오전 11: 2007-11-30 오전 1
- 참고: 1-> SUNDAY
- SQL> select NEXT_DAY(sysdate, 1), LAST_DAY(sysdate) from dual;
- ROUND(date, [format]) - 반올림 , 달의 경우 16일이 반올림의 기준일(28,30,31일과 관계 없이)
-
TRUNC(date, [format]) - 잘라내기
- SQL> select ROUND(sysdate, 'YEAR'), TRUNC(sysdate, 'YEAR') from dual;
ROUND(SYSDATE,'YEAR') TRUNC(SYSDATE,'YEAR')
--------------------- ---------------------
2008-01-01 2007-01-01
- SQL> select ROUND(sysdate, 'YEAR'), TRUNC(sysdate, 'YEAR') from dual;
-
-
데이터 변환 함수
-
TO_CHAR(date/number, format) - 숫자 데이터/날자 데이터 -> VARCHAR2 형태로 변환(지정된 포맷에 맞추어)
-
형식요소 설명 범위 SS 초 0~59 SSSSS 자정을 넘긴 초 0~86399 MI 분 0~59 HH / HH12 12시간제 시간 0~12 HH24 24시간제 시간 0~23 DD 월의 날짜 1~28,30,31(달의 마지막날에 따라 다름) DAY 요일의 전체이름 SUNDAY ~ SATURDAY DY 세 자로 축약된 요일 이름 SUN ~ SAT D 주의 날짜 1 ~7 DDD 년도의 날짜 1 ~ 365, 166 (윤년에 따라 다름) W 달의 몇 번째 주인가 표기 1 ~ 5 WW 년의 몇 번째 주인가 표기 1 ~ 53 MM 월 1 ~ 12 MON 세 자로 축약된 월 이름 JAN ~ DEC MONTH 월의 전체 이름 JANUARY ~ DECEMBER RM 월의 로마자 표기 예) 12월 => xii YY 년도의 마지막 두 자리 예) 1997년 => 97 YYYY 년도의 전체 자리 예) 1997년 => 1997 YEAR 연도의 알파벳 표기 예) 1997년 => NINETEEN NINETY SEVEN CC 세기 예) 1997년 => 19 Q 분기 1 ~ 4 J 로마 달력으로 BC 4713년 12월 31일 이후의 지금가지의 날 수를 반환환 예) 2454413 -
형식요소는 작은따옴표(')를 씌워서 사용한다.
- SQL> SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;
TO_CHAR(SYSDATE,'J')
--------------------
2454413
- SQL> SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;
-
YY포맷과 RR 포맷
-
RR 포맷 : 현재의 연도의 긑 두 자리와 처리하려는 데이터의 연도 긑두 자리의 범위가 '0~49'/'50~99'로 같을 경우 같은 세기를 반환
현재의 연도('0~49')+처리 연도('50~99')-> 이전 세기 반환, 현재의 연도('50~99')+처리 연도('0~49')-> 이후 세기 반환- 예: 현재해(2007년) : 06년 -> 2006년, 16년 -> 1916년, 현재해(1982년) : 68년 -> 1968년 / 19년 -> 2019년
-
-
-
TO_NUMBER - 문자열을 숫자 데이터로 바꾸는 기능⇔ TO_CHAR()
- SQL> select to_number('$145,000','fm$999,999') from dual;
TO_NUMBER('$145,000','FM$999,9
------------------------------
145000
- '$145,000' (문자형), 'fm$999,999'(포맷) :: fm - 공백을 없애줌
- SQL> select to_number('$145,000','fm$999,999') from dual;
-
TO_DATE - 문자열을 현재 기본 포맷의 날짜 데이터로 바꾸어 주는 기능⇔ TO_CHAR()
- SQL> SELECT SYSDATE - to_date('1982-01-06') "Past Days" from dual;
Past Days
----------
9437.57405
- SYSDATE가 날짜형이므로 '1982-01-06'이 문자형이므로 날짜형으로 변환후 산술연산 실시
- SQL> SELECT SYSDATE - to_date('1982-01-06') "Past Days" from dual;
-
CHR(number) - 해당 아스키 코드에 해당하는 문자를 반환
- SQL> select chr(80)||chr(76)||chr(85)||chr(83) SQL from dual;
SQL
----
PLUS
- SQL> select chr(80)||chr(76)||chr(85)||chr(83) SQL from dual;
-
일반 단일행 함수
-
DECODE : IF-THEN=ELSE 문장과 유사
- IF column_name or expression = condition1 THEN value1
IF column_name or expression = condition2 THEN value2
...
ELSE default value -
DECODE (column_name or expression, condition1, value1, condition2, value2, ... , default value)
- SQL> SELECT ename, deptno,
DECODE(deptno, 10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATIONS') dname
FROM emp;
- SQL> SELECT ename, deptno,
- IF column_name or expression = condition1 THEN value1
-
CASE
-
DECODE와 비슷(조건부: 다양한 비교 연산자를 이용가능)
- SELECT job, sal,
CASE WHEN job='CLERK' THEN sal*1.20
WHEN job='ANALYST' THEN sal*1.15
WHEN job='MANAGER' THEN sal*1.10
ELSE sal
END AS salary
FROM emp;
- SELECT job, sal,
-
-
NVL (column_name or expression, value) - 널(NULL) 값을 특정 값으로 변경
- column_name or expression : NULL값을 포함할 수 있는 값
- value)
- 문자/숫자/날짜 데이터에 모두 사용 가능
- 주의: 첫 번째 인수와 두번째 인수가 반드시 같은 데이터형(항상 나오는 커미션 예제)
- SQL> SELECT id, last_name, nvl(to_char(commission_pct), 'No Commission') commission from s_emp;
- 의미: commission_pct가 있을 경우는 출력하고 없을 경우는 'No Commission'라는 메시지를 출력
-
-
복수행 함수(Multiple-row function): 적용(여러 로우)-> 결과(하나)
-
내장 그룹 함수(COMPUTE 명령에 사용가능) : NULL값을 제외하고 결과를 산출(주의!)
-
COMP[UTE] 함수(아래) [LAB[EL] 텍스트] ...
- OF 계산을 수행할 대상 컬럼명
- ON 구분이 되는 (BREAK에 사용되는) 컬럼명, 식, 별칭, REPORT
-
COU[NT](컬럼명) : 조건을 만족하는 로우(행)의 개수를 반환
- SQL> SELECT COUNT(job) FROM emp;
- SQL> SELECT COUNT(DISTINCT job) FROM emp;
- SQL> SELECT COUNT(*) FROM emp; // 널 값이 있어도 카운팅 함(그룹함수의 예외)
- AVG : 평균값
- SUM : 값의 합계
- MAX[IMUM] : 최대값
- MIN[IMUM] : 최소값
- STD[DEV] : 표준편차
- VAR[IANCE] : 분산값
-
-
로우를 그룹으로 만듬
-
DISTINCT
- 중복행을 제거, 아래 예에서 DISTINCT를 넣고/빼어 실행해 보면 차이를 알 수 있다.
- 예) SELECT dep DISTINCT FROM egw_emp;
- 의미) 사원테이블(egw_emp)에서 부서를 가져오데 중복은 한번만 표현한다.
-
GROUP BY - 어떤 컬럼을 기준으로 그 컬럼의 값별로 보고자 할 때
-
예) 소속 부서별 평균 급여? (출력: 부서명 평균급여)
- SQL> SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;SQL>
- SELECT deptno, sal FROM emp GROUP BY deptno;
SELECT deptno, sal FROM emp; 와 비교를 해보기. - 주의: 컬럼의 별칭 사용불가(반드시 컬럼명을 기술)
-
-
HAVING - GROUP BY 절에 의해 생성된 결과 값 중 원하는 조건에 부합하는 자료만 볼 때
비교) SELECT ~ WHERE관계와 GROUP BY ~ HAVING 의 관계- 부서별 평균 급여? -> 급여 평균이 2000 이상인 부서만?
-
-
조인(Join)
-
Equi-join- 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결
- SQL> SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
- 컬럼명의 모호성 해결책 : 테이블명.컬럼명
-
테이블명.컬럼명이 길어짐 대책 : 별칭(alias) 지정
- SQL> SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno;
- WHERE절을 없애고 비교해본다 : 곱집합(Cartesian Product)생성
-
Non-equijoin - 등호(=) 연산자 대신 비교 연산자 사용하는 조인
- 비교연산자: <, >, <=, >=, BETWEEN ~ AND ~
-
Self-join - 자신의 테이블을 두개 인 것처럼 만들어 자기 자신 테이블과 Equi-join하는 것
- 별칭을 이용
- SQL> SELECT name, manager FROM emp e, emp m WHERE e.mgr = m.empno;
-
Outer join
- (+) 사용 : 배제된 행을 결과에 포함
- 예) 위의 Self-join의 경우 사장의 경우 자신의 매니저가 없게 되므로 출력 안됨
아래와 같이 하면 NULL인 로우도 출력
- SQL> SELECT name, manager FROM emp e, emp m WHERE e.mgr = m.empno(+);
-
-
Set 연산자
- UNION(UNION ALL)
- INTERSECT
- MINUS
-