[DB] 오라클 SQL 명령어

 

 

DDL(Data Definition Language): 데이터 정의어

---

테이블 생성

create table 테이블명(
    name varchar2 ...
);

 

 

테이블명 변경

ALTER TABLE ​테이블명

RENAME COLUMN 변경할컬럼명 TO 변경후컬럼명; 

 

 

테이블 삭제

drop table 테이블명 purge --purge를 사용해야만 남은파일없이 삭제

 

  
테이블 복제

​create table 변경후 테이블명
as
select * from 변경전 테이블명

 

 

​컬럼 추가 

ALTER TABLE 테이블명 ADD (컬럼명  타입);

​--가장 마지막에 추가됨 (위치를 변경하고 싶다면 삭제후 테이블 재생성해야함)

 

 

컬럼 속성 변경

ALTER TABLE table_name
MODIFY (column_name VARCHAR2(20));

 

 

컬럼 이름 변경

ALTER TABLE table_name
RENAME COLUMN column_name to new_column_name;

 

 

컬럼 삭제

ALTER TABLE table_name

DROP COLUMN column_name;

 

 

 

DML(Data Manipulation Language): 데이터 조작어

---

​user가 생성한 모든 테이블 조​회

​select table_name from user_tables;

 

 

​모든 테이블 조회(시스템 테이블 포함)
select table_name from ALL_tables;

 

AA테이블에 대한 pk나 index 확인

​select * from ALL_ind_columns
where table_name='AA';

 

컬럼 타입 확인
select * from ALL_TAB_COLUMNS
where TABLE_NAME='ADAM_MEMBER' 

 
string을 date타입으로 변경
to_date(컬럼명,'YYYYMMDD')
date를 string으로 변경
to_char(컬럼명,'YYYYMMDD')

 

 

Insert

모든 데이터를 입력할 경우
SQL> INSERT INTO emp
     VALUES(7369, 'SMITH', 'CLERK', 7902, TO_DATE('80/12/17'),  800, NULL,  20);

 

원하는 데이터만 입력할 경우
SQL> INSERT INTO dept (deptno, dname)
     VALUES(10, 'ACCOUNTING' );

 

SELECT 문장을 이용한 INSERT
SQL> INSERT INTO dept2
     SELECT * FROM dept;

 

변경 후 commit

 

 

Update

사원번호가 7902번인 사원의 부서 번호를 30으로 수정
SQL> UPDATE emp
     SET deptno = 30
     WHERE empno = 7902;

 

부서번호 20의 사원들 급여가 10% 인상됨
SQL> UPDATE emp
     SET sal = sal * 1.1
     WHERE deptno = 20;

 

모든 사원의 입사일을 오늘로 수정
SQL> UPDATE emp
     SET hiredate = SYSDATE

 

변경 후 commit

 

 

Delete

사원번호가 7902번인 사원의 데이터를 삭제.     
SQL> DELETE FROM emp
     WHERE empno = 7902 ;

 

평균급여보다 적게 받는 사원 삭제
SQL> DELETE FROM emp
     WHERE sal < (SELECT AVG(sal) FROM emp);

모든 행이 삭제
SQL> DELETE FROM emp; 

변경 후 commit 
  

 

 

숫자형 함수(www.gurubee.net 참조)

---

ABS 함수는 절대값을 계산하는 함수이다.
-- ABS 예제
SELECT ABS (-1.234) absolute FROM DUAL;
 
ABSOLUTE
----------
     1.234

 

CEIL 함수는 올림값을 반환하는 함수이다.
-- CEIL 양수 예제
SELECT CEIL(10.1234) "CEIL" FROM DUAL;
 
CEIL
----------
        11
 

-- CEIL 음수 예제
SELECT CEIL(-10.1234) "CEIL" FROM DUAL;
 
CEIL
----------
      -10

 

FLOOR 함수는 버림값을 반환하는 함수이다.
-- FLOOR 양수 예제
SELECT FLOOR(10.1234) "FLOOR" FROM DUAL;
 
FLOOR
------
     10
 
 
-- FLOOR 음수 예제
SELECT FLOOR(-10.1234) "FLOOR" FROM DUAL;
 
FLOOR
------
    -11

 

MOD 함수는 m을 n으로 나눈 나머지를 반환 한다. n이 0일 경우 m을 반환 한다.
-- MOD예제
SELECT MOD(9, 4) "MOD" FROM DUAL ;
 
MOD
----
   1

 

ROUND 함수는 n값의 반올림을 하는 함수로 m은 소숫점 아래 자릿수를 나타낸다.
-- ROUND 예제
SELECT ROUND(192.153, 1) "ROUND" FROM DUAL;
 
ROUND
-------
  192.2
 
-- ROUND 예제
SELECT ROUND(192.153, -1) "ROUND" FROM DUAL;
 
ROUND
-------
  190

 

TRUNC 함수는 n값을 절삭하는 함수로 m은 소숫점 아래 자릿수를 나타낸다.

-- TRUNC 예제
SELECT TRUNC(7.5597, 2) "TRUNC" FROM DUAL;
 
TRUNC
-----
 7.55
 
-- TRUNC 예제
SELECT TRUNC(789.5597, -2) "TRUNC" FROM DUAL;
 
TRUNC
-----
700

 

 

문자형 함수(www.gurubee.net 참조)

---

CONCAT 함수는 Concatenation의 약자로 두 문자를 결합하는 역할을 한며, "||" 연산자와 같은 역할을 한다.
-- CONCAT 예제
SELECT CONCAT('www.', 'oracleclub')||'.com' name FROM DUAL;
 
NAME
------------------
www.gurubee.net

 

 

INITCAP : 주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 준다.
LOWER : 주어진 문자열을 소문자로 변환시켜 준다.
UPPER : 주어진 문자열을 대문자로 변환시켜 준다.

-- INITCAP, LOWER, UPPER 예제
SELECT INITCAP('oracleclub') name FROM DUAL
 UNION ALL
SELECT UPPER('oracleclub') name FROM DUAL
 UNION ALL
SELECT LOWER('oracleclub') name FROM DUAL;
 
NAME
----------
Oracleclub
ORACLECLUB
oracleclub

 

LPAD : 왼쪽에 문자열을 끼어 놓는 역할을 한다.
RPAD : 오른쪽에 문자열을 끼어 놓는 역할을 한다.
n은 반환되는 문자열의 전체 길이를 나타내며, char1의 문자열이 n보다 클 경우 char1을 n개 문자열 만큼 반환 한다.
-- LPAD, RPAD 예제
SELECT LPAD('oracleclub', 12, '*') name FROM DUAL
 UNION ALL
SELECT RPAD('oracleclub', 12, '*') name FROM DUAL;
 
NAME
------------
**oracleclub
oracleclub**

 

SUBSTR 함수는 m 번째 자리부터 길이가 n개인 문자열을 반환 한다.
m이 음수일 경우에는 뒤에서 m 번째 문자부터 n개의 문자를 반환한다.
SUBSTRB 함수에서 B는 Byte단위로 처리하겠다는 의미이다.(문자 깨졌을 경우 사용)
-- 세번째 이후 문자열 반환.
SELECT SUBSTR('oracleclub', 3) name FROM DUAL;
 
NAME
--------
acleclub

 

-- 세번째 이후 네개의 문자열 반환.
SELECT SUBSTR('oracleclub', 3, 4) name FROM DUAL;
 
NAME
----
acle

 

-- 뒤에서 세번째아후 두개의 문자열 반환.
SELECT SUBSTR('oracleclub', -3, 2) name FROM DUAL;
 
NA
--
lu

-- DB가 UTF-8인 경우 아래 SQL의 결과는?.
SELECT SUBSTRB('오라클클럽',1) name FROM DUAL
 UNION ALL
SELECT SUBSTRB('오라클클럽',3) name FROM DUAL;

 

 

LENGTH(char),LENGTHB(char)문자열의 길이를 반환 한다.
-- 문자열의 LENGTH를 조회하는 예제.
SELECT LENGTH('오라클클럽') len FROM DUAL
 UNION ALL
SELECT LENGTHB('오라클클럽') len FROM DUAL;
 
-- DB가 UTF-8인 경우
LEN
--------
     5
    15

 

REPLACE는 문자열의 특정 문자를 다른 문자로 변환 한다.
-- 간단한 REPLACE 예이다.
SELECT REPLACE('oracleclub','oracle','db') name FROM DUAL;
 
NAME
------
dbclub


-- 대소문자를 구분한다는 것을 알수 있다.
SELECT REPLACE('OracleClub','oracle','DB') name  FROM DUAL
 UNION ALL
SELECT REPLACE('OracleClub','Oracle','DB') name  FROM DUAL;
 
NAME
----------
OracleClub
DBClub

 

INSTR (char1, str1, m, n)
문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환 한다. 지정한 문자열이 발견되지 않으면 0이 반환 된다.
char1 : 지정문자, str1 : 검색문자, m:시작위치, n:검색순위
-- 지정한 문자 OK가 발견되지 않아 0을 반환.
SELECT INSTR('CORPORATE FLOOR','OK') idx FROM DUAL;
 
IDX
----------
         0


-- OR이 있는 위치 2를 반환. 왼쪽부터 비교를 한다는 것을 알 수 있다.
SELECT INSTR('CORPORATE FLOOR','OR') idx FROM DUAL;
 
IDX
----------
         2


-- 왼쪽에서 세 번째부터 시작을 해서 검색된 OR의 위치를 반환 한다.
SELECT INSTR('CORPORATE FLOOR','OR', 3) idx FROM DUAL;
 
IDX
----------
         5
 
 
-- 왼쪽에서 세 번째부터 시작을 해서 비교를 하여 OR이 두 번째 검색되는 지점의 위치를 반환 한다.
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) idx FROM DUAL;

IDX
----------
        14

 

TRIM (char1[,char2]), LTRIM (char1[,char2]), RTRIM (char1[,char2])
TRIM : 특정한 문자를 제거 한다. 제거할 문자를 입력하지 않으면 기본적으로 공백이 제거 된다.
LTRIM : 왼쪽부터 문자를 제거 한다. 제거할 문자를 입력하지 않으면 기본적으로 왼쪽 공백이 제거 된다.
RTRIM : 오른쪽부터 문자를 제거 한다. 제거할 문자를 입력하지 않으면 기본적으로 오른쪽 공백이 제거 된다.

-- o와 공백을 제거 하는 TRIM 예제이다.
SELECT TRIM('o' FROM 'oracleclub') name FROM DUAL
UNION ALL
SELECT TRIM(' oracleclub ') name FROM DUAL;
 
NAME
----------
racleclub
oracleclub


-- 왼쪽의 문자열을 TRIM하는 예제이다.
-- 공백의 경우 왼쪽 공백만 제거 되는 것을 확인 할 수 있다.
SELECT LTRIM('oracleclub','oracle') name FROM DUAL
UNION ALL
SELECT REPLACE(LTRIM(' oracleclub '),' ','*') name FROM DUAL;

NAME
-----------
ub
oracleclub*


-- 오른쪽의 문자열을 TRIM 하는 예제이다.
-- 공백의 경우 오른쪽 공백만 제거 되는 것을 확인 할 수 있다.
SELECT RTRIM('oracleclub','club') name FROM DUAL
UNION ALL
SELECT REPLACE(RTRIM(' oracleclub '),' ','*') name FROM DUAL;
 
NAME
-----------
oracle
*oracleclub

 

 

 

날짜형 함수(www.gurubee.net 참조)

---

ADD_MONTHS 함수는 a의 날짜에 b의 달을 더한 값을 반환 한다.
-- SYSDATE를 이용한 예제
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,3),'RRRR-MM-DD')  "date"
  FROM DUAL;
 
 
-- TIMESTAMP를 이용한 예제
SELECT TO_CHAR(ADD_MONTHS(SYSTIMESTAMP,3),'RRRR-MM-DD')  "date"
  FROM DUAL;

 

 

SYSDATE 함수를 사용하면 현재 일자와 시간(시스템기준)을 얻을 수 있다.
-- SYSDTE 예제
SELECT TO_CHAR(SYSDATE,'RRRR-MM-DD HH24:MI:SS') "지금시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1,'RRRR-MM-DD HH24:MI:SS') "하루전지금시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24,'RRRR-MM-DD HH24:MI:SS') "1시간전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24/60,'RRRR-MM-DD HH24:MI:SS') "1분전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24/60/10,'RRRR-MM-DD HH24:MI:SS') "6초전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-(5/24 + 30/24/60 + 10/24/60/60),'RRRR-MM-DD HH24:MI:SS') "5시간 30분 10초전"
  FROM DUAL ;

 

 

변환형 함수(www.gurubee.net 참조)

---

TO_CHAR 함수는 DATE형, NUMBER형을 문자 타입으로 변환하는 함수이다. TO_CHAR(number), TO_CHAR(number, format), TO_CHAR(date, format) 형식으로 사용할 수 있다.
-- 콤마 예제
SELECT TO_CHAR(12345678,'999,999,999') comma FROM DUAL;

-- 소숫점 예제 
SELECT TO_CHAR(123.45678,'999,999,999.99') period FROM DUAL;

-- $ 표시 예제
SELECT TO_CHAR(12345678,'$999,999,999') dollar  FROM DUAL;

-- Local(현지) 화폐 표시 예제 (한국의 경우 ￦로 자동 변환 됨)
SELECT TO_CHAR(12345678,'L999,999,999') local  FROM DUAL;

-- 왼쪽에 0을 삽입
SELECT TO_CHAR(123,'09999') zero FROM DUAL; 

-- 16진수로 변환
SELECT TO_CHAR(123,'XXXX') hexadecimal  FROM DUAL;

 

 

DATE 타입의 데이터를 아래와 같이 특정 형식의 문자 타입으로 변환 할 수 있다.
-- 년,월,일,시,분,초 예제
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "sysdate"
FROM DUAL;

-- 365일 중 몇 일째인지 조회
SELECT TO_CHAR(SYSDATE, 'DDD') "Day of year" FROM DUAL;

-- 53주 중 몇 주차 인지 조회
SELECT TO_CHAR(SYSDATE, 'IW') "Week of year" FROM DUAL;

-- 해당 월의 이름 조회
SELECT TO_CHAR(SYSDATE, 'MONTH') "Name of month" FROM DUAL;

 

시간을 24시간 기준으로 조회
select to_char(sysdate, 'yyyymmddhh24miss') form dual;

 

TO_DATE 함수는 CHAR, VARCHAR2형을 DATE 타입으로 변환한다.
TO_DATE(char, format) 형식으로 사용 할 수 있다.
주요 Date Format Elements 에서 'W', 'WW' Format을 제외한 나머지는 TO_DATE 함수의 format으로 사용 할 수 있다.
-- DATE 타입으로 변환하는 예제
SELECT TO_DATE('2011-01-01','RRRR-MM-DD') FROM DUAL;

 

 

 

조건형 함수(www.gurubee.net 참조)

---

DECODE 함수는 조건에 따라 데이터를 다른 값이나 컬럼값으로 추출 할 수 있다.
DECODE(VALUE, IF1, THEN1, IF2, THEN2...) 형태로 사용 할 수 있다.
VALUE 값이 IF1일 경우에 THEN1 값을 반환하고, VALUE 값이 IF2일 경우에는 THEN2 값을 반환한다.
DECODE 함수 안에 DECODE함수를 중첩으로 사용 할 수 있다.

-- 부서번호가 10이면 ACCOUNTING, 20이면 RESEARCH, 30이면 SALES
-- 나머지는 OPERATIONS를 출력하는 예제
SELECT deptno, DECODE(deptno, 10 , 'ACCOUNTING' ,
                              20 , 'RESEARCH' ,
                              30 , 'SALES', 'OPERATIONS') name
  FROM dept;
 
DEPTNO NAME
------ ----------
     10 ACCOUNTING
     20 RESEARCH
     30 SALES
     40 OPERATIONS

 

-- 10부서는 급여합계를, 20부서는 최대값을, 30부서는 최소값을 출력하는 예제
SELECT deptno, DECODE(deptno, 10 , SUM(sal),
                              20 , MAX(sal),
                              30 , MIN(sal)) sal
  FROM emp
 GROUP BY deptno;
 
DEPTNO        SAL
--------- --------
       30      950
       20     3000
       10     8750

 

-- 부서별로 급여 합계를 출력한다.
SELECT deptno, NVL(SUM(DECODE(deptno, 10, sal)),0) deptno10,
               NVL(SUM(DECODE(deptno, 20, sal)),0) deptno20,
               NVL(SUM(DECODE(deptno, 30, sal)),0) deptno30,
               NVL(SUM(DECODE(deptno, 40, sal)),0) deptno40
  FROM emp
 GROUP BY deptno;
 
DEPTNO   DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40
------- --------- --------- ---------- ----------
     30         0         0       9400          0
     20         0     10875          0          0
     10      8750         0          0          0

 

-- 부서별로 급여 합계를 행으로 출력한다.
SELECT d.deptno, NVL(SUM(e.sal),0) sal
  FROM emp e, dept d
 WHERE e.deptno(+) = d.deptno
 GROUP BY d.deptno;
 
DEPTNO        SAL
-------- ----------
      10       8750
      20      10875
      30       9400
      40          0


-- 부서별로 급여 합계를  열로 출력한다.
SELECT MAX(NVL(SUM(DECODE(deptno, 10, sal)),0)) deptno10,
       MAX(NVL(SUM(DECODE(deptno, 20, sal)),0)) deptno20,
       MAX(NVL(SUM(DECODE(deptno, 30, sal)),0)) deptno30,
       MAX(NVL(SUM(DECODE(deptno, 40, sal)),0)) deptno40
  FROM emp
 GROUP BY deptno;
 
DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40
--------- ---------- ---------- ----------
    8750      10875       9400          0

 

CASE 함수는 DECODE함수가 제공하지 못하는 비교연산의 단점을 해결할 수 있는 함수이다.
DECODE함수에서 비교연산을 수행하기 위해서는 GREATEST, LEAST등의 함수를 사용해야 하지만, CASE함수에서는 조건 연산자를 모두 사용 할 수 있다.
CASE함수는 IF.. THEN .. ELSE 구문과 비슷 하다. WHEN절 다음에 여러 조건이 올 수 있다.
--위의  DECODE예제를 CASE함수로 변환한 예이다. (case시작 end로 종료)
SELECT deptno,
       CASE deptno
         WHEN 10 THEN 'ACCOUNTING'
         WHEN 20 THEN 'RESEARCH'
         WHEN 30 THEN 'SALES'
         ELSE 'OPERATIONS'
       END as "Dept Name"
  FROM dept;
 
DEPTNO Dept Name
------- ----------
     10 ACCOUNTING
     20 RESEARCH
     30 SALES
     40 OPERATIONS

 

--급여별로 인상율을 다르게 계산하였다. (when절 다음에 위치)
SELECT ename ,
       CASE
          WHEN sal < 1000  THEN sal+(sal*0.8)
          WHEN sal BETWEEN 1000 AND 2000 THEN sal+(sal*0.5)
          WHEN sal BETWEEN 2001 AND 3000 THEN sal+(sal*0.3)
          ELSE sal+(sal*0.1)
       END sal
  FROM emp;

 

 

NVL, NVL2(www.gurubee.net 참조)

---

NVL 함수는 NULL 값을 다른 값으로 바꿀 때 사용하며, 모든 데이터 타입에 적용이 가능하다.
-- 매니저가 없는 값을 0으로 바꾸어서 출력하는 예제.
SELECT empno, NVL(mgr, 0) mgr
  FROM emp 
 WHERE deptno = 10;
 
EMPNO      MGR
------- -------
  7782    7839
  7839       0
  7934    7782

 

 

NVL2라는 함수는 NVL함수의 DECODE 함수의 개념을 합쳤다고 생각하면 쉽습니다.
NVL2(expr, expr1, expr2)
expr의 값이 NULL이 아닐 경우에는 expr1의 값을 반환 하고, NULL일 경우에는 expr2의 값을 반환 한다.
-- 매니저가 있는경우 1을 없는경우 0을 출력하는 예제이다.
SELECT empno, NVL2(mgr, 1, 0) mgr
  FROM emp 
 WHERE deptno = 10;
 
EMPNO        MGR
------- ----------
  7782          1
  7839          0
  7934          1 

 

 

 

GROUP BY/HAVING(www.gurubee.net 참조)

---

GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.
집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다. (개발자 분들이 많이 실수 함)
아래는 집계 함수와 상수가 함께 SELECT 절에 사용되는 예이다.
-- 부서별 사원수 조회
SELECT '2005년' year, deptno 부서번호, COUNT(*) 사원수
  FROM emp
 GROUP BY deptno
 ORDER BY COUNT(*) DESC;
 
 
YEAR     부서번호     사원수
------ ---------- ----------
2005년         30          6
2005년         20          5
2005년         10          3

 

HAVING
WHERE 절에서는 집계함수를 사용 할 수 없다.
HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
HAVING절은 GROUP BY절과 함께 사용이 된다.
SELECT b.dname, COUNT(a.empno) "사원수"
  FROM emp a, dept b
 WHERE a.deptno = b.deptno
 GROUP BY dname
HAVING COUNT(a.empno) > 5;
 
 
DNAME          사원수
------------ -------
SALES              6