01. 기본 쿼리문 및 select문

데이터 딕셔너리

Oracle 주석


-- Oracle 주석
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
          

테이블 목록 조회하기


SELECT * FROM 테이블명;
          

테이블 정보 조회하기


DESC 테이블명;
          

SQL 명령문

DDL

  • Data Definition Language(데이터 정의어)
  • 테이블과 인덱스 구조를 관리하는 언어
  • CREATE, DROP, ALTER

DML

  • Data Manipulation Language (데이터 조작어)
  • 테이블 등에 데이터를 저장, 수정, 삭제, 추출 등을 처리하는 언어
  • INSERT, UPDATE, DELETE, SELECT

DCL

  • Data Control Language (데이터 제어어)
  • 사용자 권한, 작업의 취소 등을 처리할 수 있는 언어
  • GRANT, REVOKE, COMMIT, ROLLBACK

DML - Select 기본

데이터 베이스에 저장된 데이터를 가져오는 명령문

모든 컬럼의 데이터 가져오기


SELECT * FROM 테이블 명

-- 부서의 모든 정보를 가져온다
SELECT * FROM dept;

-- 사원의 모든 정보를 가져온다
SELECT * FROM emp;
          

특정 컬럼의 데이터 가져오기


SELECT 컬럼명1, 컬럼명2 FROM 테이블명;

-- 사원의 이름과 사원 번호를 가져온다.
SELECT ename, empno FROM emp;

-- 사원의 이름과 사원 번호, 직무, 급여를 가져온다.
SELECT ename, empno, job, sal FROM emp;

-- 부서 번호와 부서 이름을 가져온다.
SELECT deptno, dname FROM dept;
          

DML - 연산자 사용하기

SELECT문을 사용할때 연산자를 이용하면 컬럼의 값을 연산한 결과를 가져올수 있다.

산술 연산자

종류 설명
+ 더하기
- 빼기
* 곱하기
/ 나누기

-- 각 사원들의 급여약과 급여액에서 1000을 더한 값, 200을 뺀 값, 2를 곱한 값, 2로 나눈 값을 가져온다.
SELECT sal, sal+1000, sal-200, sal/2 FROM emp;

-- 각 사원의 급여액, 커미션, 급여 + 커미션 액수를 가져온다.
-- nvl(comm, 0) => comm에 null 값이 있을 경우 0으로 가져온다
SELECT sal, nvl(comm, 0), sal+nvl(comm, 0) FROM emp;
          

Concat 연산자

문자열과 컬럼의 값을 연결하여 하나의 문자열로 가져올수 있다.


문자열 || 컬럼 || 문자열 || 컬럼

-- 사원들의 이름과 직무를 다음 양식으로 가져온다. 000 사원의 담당 직무는 xxx 입니다.
SELECT ename || '사원의 담당 직무는' || job '입니다'  FROM emp;
          

DIstinct

SELECT 문을 통해 가져온 모든 로우 중에서 중복된 로우를 제거하는 키워드


SELECT DISTINCT 컬럼명 FROM 테이블명;

-- 사원들이 근무하고 있는 근무 부서의 번호를 가져온다.
SELECT DISTINCT deptno FROM emp;
          

조건절 사용하기

조건절을 사용하면 원하는 로우만 가져올 수 있다.

비교 연산자

종류 설명
< 작은가
> 큰가
<= 작거나 같은가
>= 크거나 같은가
= 같은가
<>, !=, ^= 다른가

SELECT의 조건절

SELECT ~ FROM 까지를 통해 모든 로우를 가져오고 조건절과 비교하여 참은 유지 거짓은 제거한다.


SELECT 컬럼명 FROM 테이블명 WHERE 조건절

-- 근무 부서가 10번인 사원들의 사원번호, 이름, 근무 부서를 가져온다.
SELECT empno, ename, deptno FROM emp WHERE deptno=10;

-- 근무 부서 번호가 10번이 아닌 사원들의 사원번호, 이름, 근무 부서 번호를 가져온다.
SELECT empno, ename, deptno FROM emp WHERE deptno!=10;

-- 급여가 1500이상인 사원들의 사원번호, 이름, 급여를 가져온다,
SELECT empno, ename, sal FROM emp WHERE sal >= 1500

-- 이름이 SCOTT 사원의 사원번호, 이름, 직무, 급여를 가져온다.
SELECT empno, ename, job, sal FROM emp WHERE empno='SCOTT';

-- 직무가 SALESMAN인 사원의 사원번호, 이름, 직무, 급여를 가져온다.
SELECT empno, ename, job, sal FROM emp WHERE job='SALESMAN';

-- 직무 CLERK이 아닌 사원의 사원번호, 이름, 직무, 급여를 가져온다.
SELECT empno, ename, job, sal FROM emp WHERE job!='CLERK';

-- 1982년 1월 1일 이후에 입사한 사원의 사원번호, 이름, 입사일을 가져온다
SELECT empno, ename, hiredate FROM emp WHERE hiredate >= '1982/01/01'
          

논리 연산자 사용하기

여러 조건식을 묶어서 하나의 조건식으로 만들수 있다.

종류 설명
and 좌우 조건식이 모두 참일 경우 참
or 좌우 조건식이 모두 거짓일 경우 거짓
not 조건식의 결과를 부정
between, and 범위 조건
in 항목 조건

-- 10번 부서에서 근무하고 있는 직무가 MANAGER인 사원의 사원번호, 이름, 근무부서, 직무를 가져온다.
SELECT empno, ename, sal, job FROM emp WHERE deptno=10 and job='MANAGER';

-- 입사년도가 1981년인 사원중에 급여가 1500 이상인 사원의 사원번호, 이름, 급여, 입사일을 가져온다.
SELECT empno, ename, sal, hiredete FROM emd WHERE hiredete>='1981/01/01' and hiredete<='1981/12/31' and sal>=1500;
-- between 을 사용
SELECT empno, ename, sal, hiredete 
FROM emp
WHERE hiredete between '1981/01/01' and '1981/12/12' and sal>=1500;

-- 20번 부서에 근무하고 있는 사원 중에 급여가 1500 이상인 사원의 회원번호, 이름, 부서번호, 급여를 가져온다
SELECT empno, ename, deptno, sal
FROM emp
WHERE deptno=20 and sla>=1500;

-- 직속상관 사원 번호가 7698번인 사원중에 직무가 CLERK인 사원의 사원번호, 이름, 직속상관번호, 직무를 가져온다
SELECT empno, ename, mrg, job
FROM emp
WHERE mrg=7698 and jab='CLERK';

-- 급여가 2000보다 크거나 1000보다 작은 사원의 사원번호, 이름, 급여를 가져온다
SELECT empno, ename, sal
FROM emp
WHERE sal>2000 and sal<1000;

-- 부서 번호가 20 이거나 30인 사원의 사원번호, 이름, 부서번호를 가져온다.
SELECT empno, ename, deptno
FROM emp
WHERE deptno=20 or deptno=30;

-- 직무가 CLERK, SALESMAN, ANALYST인 사원의 사원번호, 이름, 직무를 가져온다
SELECT empno, ename, job
FROM emp
WHERE job='CLERK' or job='SALESMAN' or job='ANALYST';
-- in을 사용
SELECT empno, ename, job
FROM emp
WHERE job IN('CLERK', 'SALESMAN', 'ANALYST');

-- 사원 번호가 7499, 7566, 7839가 아닌 사원들의 사원번호, 이름을 가져온다.
SELECT empno, ename
FROM emp
WHERE not(empno = 7499 or empno = 7566 or empno = 7839);
          

Like 연산자

조건문에서 문자열도 =, <>로 비교가 가능하다.

종류 설명
_ 글자 하나를 의미한다
% 글자 0개 이상을 의미한다

SELECT 컬럼명 FROM 테이블명 WHERE 컬럼명 LIKE '검색문자';

-- 이름이 F로 시작하는 사원의 이름, 사원번호를 가져온다
SELECT ename, empno
FROM emp
WHERE ename LIKE 'F%';

-- 이름이 S로 끝나는 사원의 이름, 사원번호를 가져온다
SELECT ename, empno
FROM emp
WHERE ename LIKE '%S';

-- 이름에 A가 포함되어 있는 사원의 이름, 사원 번호를 가져온다.
SELECT ename, empno
FROM emp 
WHERE ename LIKE '%A%';

-- 이름의 두번쨰 글자가 A인 사원의 사원이름, 사원번호를 가져온다.
SELECT ename, empno
FROM emp
WHERE ename LIKE '_A%';

-- 이름이 4글자인 사원의 사원이름, 사원번호를 가져온다
SELECT ename, empno
FROM emp
WHERE ename LIKE '____';
          

null

조건절에서 null 비교는 is null 이나 is not null 을 사용한다.


-- 사원중에 커미션을 받지 않는 사원의 사원번호, 이름, 커미션을 가져온다
SELECT ename, empno, comm
FROM emp
WHERE comm is null

-- 사원중에 커미션을 받는 사원의 사원번호, 이름, 커미션을 가져온다
SELECT ename, empno, comm
FROM emp
WHERE comm is not null

-- 회사 대표(직속상관이 없는 사람)의 이름과 사원번호를 가져온다.
SELECT ename, empno
FROM emp
WHERE mrg is null;
          

정렬

특정 컬럼 기준으로 오름차순 혹은 내림차순으로 정렬할수 있다.

종류 설명
ASC 오름차순 , 생략가능
DESC 내림차순

SELECT 컬럼명 FROM 테이블명 WHERE 조건 ORDER BY 컬럼명[ASC|DESC]

-- 사원의 사원번호, 이름, 급여를 가져온다 . 급여를 기준으로 오름차순 정렬
SELECT ename, empno, sal
FROM emp
ORDER BY ASC;

-- 사원의 사원번호, 이름, 급여를 가져온다. 사원번호를 기준으로 내림차순 정렬을 한다.
SELECT empno, ename, sal
FROM emp
ORDER BY empno desc;

-- 사원의 사원번호, 이름을 가져온다. 사원의 이름을 기준으로 오름차순 정렬을 한다.
SELECT empno, ename
FROM emp
ORDER BY ename asc;

-- 사원의 사원번호, 이름, 입사일을 가져온다. 입사일을 기준으로 내림차순 정렬을 한다.
SELECT empno, ename, hiredate
FROM emp
ORDER BY hiredate DESC;

-- 직무가 SALESMAN인 사원의 사원이름, 사원번호, 급여를 가져온다. 급여를 기준으로 오름차순 정렬을 한다
SELECT ename, empno, sal
FROM emp
WHERE jop='SALESMAN'
ORDER BY sal ASC;

-- 1981년에 입사한 사원들의 사원번호, 사원이름, 입사일을 가져온다. 
-- 사원 번호를 기준으로 내림차순 정렬을 한다
SELECT empno, ename, hiredate
FROM emp
WHERE hiredate between '1981/01/01' and '1981/12/30'
ORDER BY empno DESC;

-- 사원의 이름, 급여, 커미션을 가져온다. 커미션을 기준으로 오름차순 정렬을 한다.
SELECT ename, sal, comm
FROM emp
ORDER BY comm DESC;

-- 사원의 이름, 사원번호, 급여를 가져온다.
-- 급여를 기준으로 오름차순 정렬, 이름을 기준으로 내림차순 정렬
SELECT ename, empno, sal
FROM emp
ORDER BY sal DESC, ename ASC;
          

숫자 함수

숫자 함수를 사용하면 컬럼에 저장되어 있는 숫자 데이터레 대한 작업을 할 수 있다.

dual - 가상의 테이블


-- dual은 가상의 테이블
SELECT 10 + 10 FROM dual;
          

abs - 절대값 구허기


-- 절대값 구하기 abs
SELECT -10, abs(-10) FROM dual;

-- 전직원의 급여를 2000 삭감하고 삭감한 급여액으리 절대값을 구한다.
SELECT sal, sal-2000, abs(sal-2000) FROM emp;
          

floor - 소수점 버림


-- floor
-- 소수점 이하 버림
SELECT 12.3455 floor(12.3455) FROm dual;

-- 급여가 1500 이상인 사원의 급여를 15% 삭감한다. 단 소수점 이하는 버린다
SELECT sal, floor(sal*0.85)
FROM emp;
WHERE sal>=1500;
          

round - 반올림


-- round
-- 반올림
SELECT 12.345, round(123.45) FROM dual;

-- round(숫자 , 소수점자리 지정 (0부터 카운트))
SELECT 666.6666, round(666.8888), round(666.8888, 2) FROM dual;
SELECT 666.6666, round(666.8888), round(666.8888, -2) FROM dual;

-- 급여가 2천 이하인 사원들의 급여를 20%씩 인상한다. 단 10의 자리를 기준으로 반올림 한다
SELECT sal, sal*20%, round(sal*1.2% , -2)
FROM ema
WHERE sal>=2000;
          

trunc - 자리수 버림


-- trunc
-- 자리수 버림
SELECT 123.123, trunc(123.123) FROM dual;

-- trunc(숫자, 버릴자리수 인덱스)
SELECT 123.123, trunc(123.123 , 2) FROM dual;
SELECT 123.123, trunc(123.123 , -2) FROM dual;

-- 전 직원의 급여를 10자리 이하를 삭감한다
SELECT sal, trunc(sal, -2)
FROM emp;
          

mod - 나머지


-- mod
-- 나머지
SELECT mod(10, 3), mod(10, 4) FROM dual;
          

문자열 함수

문자열 함수를 사용하면 컬럼에 저장되어 있는 문자열 데이터에 대한 작업을 할 수 있다.

lower - 대문자 > 소문자


-- lower
-- 대문자 -> 소문자
SELECT 'ABcdefG', lower('ABcdefG') FROM dual;

-- 사원들의 이름을 소문자로 가져온다
SELECT sname, lower(sname)
FROM emp;
          

upper - 소문자 > 대문자


-- upper
-- 소문자 -> 대문자
SELECT 'ABcdefG', upper('ABcdefG') FROM dual;

-- 사원들의 이름을 가져온다. 대문자> 소문자> 대문자
SELECT ename, lower(ename), upper(lower(ename))
FROM emp;
          

initcap - 첫글자만 대문자, 나머지는 소문자로


-- initcap
-- 첫글자만 대문자, 나머지는 소문자로
SELECT 'aBCDEF', initcap('aBCDEF')  from dual;

-- 사원이름을 첫 글자는 대문자로 나머지는 소문자로 가져온다.
SELECT ename, INITCAP(ename)
FROM emp
          

concat - 문자열 연결


-- concat
-- 문자열 연결
SELECT concat('abc', 'def') FROM dual;

-- 사원들의 이름과 직무를 사원의 이름은 000 이고, 직무는 000입니다.
SELECT concat('사원의 이름은 ', sname, '이고, 직무는', job, '입니다. ')
FROM emp;
SELECT '사원들의 이름은' || ename || '이고, 직무는' || job || '입니다.' FROM emp;
          

length - 문자열의 길이


-- length
-- 문자열의 길이
SELECT length('abdc'), length('안녕ㄴ') FROM dual;
          

substr - 문자열 잘라내기


-- substr
-- 문자열 잘라내기
SELECT substr('abdc', 3) FROM dual;
SELECT substr('abcdef', 3, 4) FROM dual;
          

instr - 문자열 찾기


-- 문자열 찾기
SELECT instr('abcdefghr', 'bc') FROM dual;

-- 사원의 이름 중에 A라는 글자가 두번째 이후에 나타나는 사원의 이름을 가져온다.
SELECT ename
FROM emp
WHERE instr(name, 'A') > 1;
          

lpad, rpad - 특정 문자열로 채우기


-- lpad, rpad
-- 특정 문자열로 채우기
SELECT '문자열', lpad('문자열', 20 , '-'), rpad('문자열', 20) FROM dual;
          

replace - 문자열 변경


-- replace
-- 문자열 변경
SELECT replace('abcdefg', 'abc', 'kkk') FROM dual;
          

trim - 공백 제거


-- ltrim, rtrim, trim
-- 공백 제거
SELECT trim('    문자열    '), ltrim('    문자열    '), rtrim('    문자열    ') FROM dual;
          

날짜 함수

제공되는 날짜 함수를 통해 날짜 데이터를 연산할 수 있다.

종류 설명
sysdate 현재 날짜와 시간을 반환한다.
months_between 두 날짜간의 게월수를 구한다.
add_months 주어진 개월 수 만큼 더한다.

시간

종류 설명
CC 년도 두자리 기준으로 반올림
YYYY 월기준 으로 반올림
DDD 시기준 으로 반올림
HH 분을 기준으로 반올림
MI 초을 기준으로 반올림
MM 일을 기준으로 반올림
DAY 주를 기준으로 반올림

-- 현재 날짜 구하기
SELECT sysdate FROM dual;

-- 날짜 데이터 연산 (숫자는 일을 뜻한다)
SELECT sysdate - 10000, sysdate + 10000 FROM dual;

-- 각 사원이 입사한 날짜로 부터 1000일 후가 되는 날짜를 가져온다.
SELECT hiredate + 1000 FROM emp;

-- 직무가 SALESMAN인 사원의 입사일 199일전 날짜를 가져오기
SELECT hiredate - 100 FROM emp WHERE job = 'SALESMAN';

-- 전 사운의 근무일을 가져온다
SELECT trunc(sysdate - hiredate) FROM emp;

-- 반올림
SELECT round(sysdate, 'CC') as "년도 두자리",
round(sysdate, 'YYYY') as "월기준",
round(sysdate, 'DDD') as "시 기준",
round(sysdate, 'HH') as "분 기준",
round(sysdate, 'MI') as "초 기준",
round(sysdate, 'MM') as "일 기준",
round(sysdate, 'DAY') as "주 기준" FROM dual;

-- 각 사원의 입사일을 월 기준으로 반올림 한다
SELECT round, round(hiredata,'YYYY') FROM emp;

-- 버림
SELECT
    sysdate,
    trunc(sysdate, 'CC') as "연도두자리",
    trunc(sysdate, 'YYYY') as "월",
    trunc(sysdate, 'DDD') as "시",
    trunc(sysdate, 'HH') as "분",
    trunc(sysdate, 'MM') as "일",
    trunc(sysdate, 'DAY') as "주",
    trunc(sysdate, 'MI') as "초"
FROM dual;

-- 1981년에 입사한 사원들의 사원번호, 사원이름, 급여, 입사일을 가져온다.
SELECT empno, ename, job, hiredate
FROM emp
WHERE hiredata >= '1981/01/01' and hiredata <= '1981/12/31';

SELECT empno, ename, job, hiredate
FROM emp
WHERE trunc(hiredate, 'YYYY') = '1981/01/01';
          

MONTHS_BETWEEN - 두날짜 개월 수 차이 출력


-- MONTHS_BETWEEN
-- 모든 사원이 근무한 개월 수를 구헌다.
SELECT trunc(MONTHS_BETWEEN(SYSDATE,hire_date)) FROM emp;
          

add_monthe - 개월수를 더한다


-- add_monthe
-- 개월수를 더한다
SELECT add_months(sysdate, 100) FROM dual;

-- 각 사원들의 입사일 후 100 개월 후 날짜를 구한다
SELECT hiredate, add_months(hiredate, 100) FORM emp;
          

next_day - 지정된 날자를 기준으로 지정된 요일이 몇일인지 구하기


-- next_day
-- 지정된 날자를 기준으로 지정된 요일이 몇일인지 구하기
SELECT sysdate, next_day(sysdate, '월요일') FROM dual;
          

last_day - 지정된 날짜의 월의 마지막 날짜를 구한다.


-- last_day
-- 지정된 날짜의 월의 마지막 날짜를 구한다.
SELECT sysdate, last_day(sysdate) FROM dual;
          

to_char - 날자나 숫자를 문자열로 변환(오라클 -> 프로그램)


-- to_char
-- 날자나 숫자를 문자열로 변환(오라클 -> 프로그램)
SELECT sysdate, to_char(sysdate, 'YYYY-MM-DD HH:MI:SS AM') FROM dual;
          

to_date - 프로그램에서 얻은 날자값을 오라클에 저장할때 사용 (프로그램 -> 오라클)


-- to_date
-- 플로그램에서 얻은 날자값을 오라클에 저장할때 사용 (프로그램 -> 오라클)
SELECT to_date('2018-03-20 01:58:20 오후', 'YYYY-MM-DD HH:MI:SS AM') FROM dual;
          

DECODE, CASE

DECODE

값에 따라 반환 값을 결정할 수 있다.


decode(컬럼명, 값1, 반환값1
            , 값2, 반환값2
            , 값3, 반환값3)

-- 각 사원의 부서 이름을 가져온다
-- 10 : 인사과, 20 : 개발부, 30 : 경영지원팀, 40 : 생산부
SELECT
   empno,
   ename,
   decode(deptno, 10, '인사과', 20,'개발부', 30,'경영지원팀', 40,'생산부')
FROM emp;

-- 직급에 따라 인상괸 급여액을 가져온다
-- CLERK:10%, SALESMAN:15%, PRESIDENT:200%, MANAGER:5%, ANAYST:20%
SELECT empno, ename, jop,
       decode(job, 'CLERK', sal * 1.1,
                   'SALESMAN', sal * 1.15,
                   'PRESIDENT', sal * 3,
                   'MANAGER', sal * 1.05,
                   'ANAYST', sal * 1.2)
FROM emp;
          

CASE

조건에 따라 변환 값을 결정할수 있다.


CASE when 조건식1 then 반환값1
    when 조건식3 then 반환값2
end
          

그룹함수

slect문을 통해 가져온 결과를 그룹으로 묶고 총점, 평균 등의 값을 구할 수 있다.

종류 설명
sum 총합
avg 평균
count 로우의 수
max 최대 값
min 최소 값

sum - 합


-- 사원들의 급여 총합을 구한다.
-- null 이 있을경우 null 값 뺴고 계산함
SELECT sum(sal)
FROM emp;

-- 급여가 1500 이상인 사원들의 급여 총합을 구한다.
SELECT sum(sal)
FROM emp
WHERE sal >= 1500;

-- 20번 부서에 근무하고 있는 사원들의 급여 총합을 구한다.
SELECT sum(sal)
FROM emp
WHERE deptno = 20;

-- 직무가 SALESMAN인 사원들의 급여 총합을 구한다.
SELECT sum(sal)
FROM emp
WHERE job = 'SALESMAN';
          

avg - 평균


-- 전 사원의 급여 평균을 구한다.
SELECT avg(sal)
FROM emp;

-- 커미션을 받는 사원들의 커미션 평균을 구한다
SELECT avg(comm)
FROM emp;

-- 전 사원의 커미션의 평균을 구한다.
SELECT avg(nvl(comm, 0))
FROM emp;

-- 커미션을 받는 사원의 급여 평균을 구한다
SELECT avg(sal)
FROM emp
WHERE comm is not null;

-- 30번 부서에 근무하고 있는 사원들의 급여 평균을 구한다
SELECT avg(sal)
FROM emp
WHERE deptno = 30;

-- 직무가 SALESMAN인 사원들의 급여 + 커미션 평균을 구한다.
SELECT avg(sal+comm)
FROM emp
WHERE jog = 'SALESMAN';
          

count - 로우의 수


-- 사원들의 총 수를 가져온다
-- null 을 제외하고 카운트 함
SELECT count(empno)
FROM emp;
          

max, min - 최대, 최소


-- 사원들의 총 수를 가져온다
-- null 을 제외하고 카운트 함
SELECT max(sal), min(sal)
FROM emp;
          

Group By

Group By 절을 사용하면 SELECT문을 통해 얻어온 결과를 정해준 기준에 따라 그룹으로 분류하고 각 그룹내에서 그룹함수를 사용할 수 있다.


SELECT 컬럼명
FROM 테이블명
WHERE 조건절
GROUP BY 그룹 기준
ORDER BY 정렬기준

-- 각 부서별 사원들의 급여 평군을 구한다.
SELECT avg(sal)
FROM emp
GROUP BY deptno;

-- 각 직무별 사우너들의 급여 총합을 구한다.
SELECT sum(sal)
FROM emp
GROUP BY job;

-- 1500 이상 급여를 받는 사원들의 부서별 급여 평균을 구한다.
SELECT avf(sal)
FROM emp
WHERE sla >= 1500
GROUP BY deptno;
          

Having

Having은 Group By의 조건절로 각 그룹중 사용할 그룹을 선택할 수 있는 조건이다,


-- 부서별 평균 급여가 2000이상인 부서의 급여 평균을 가져온다.
SELECT deptno, avf(sal)
FROM emp
GROUP BY deptno
HAVING avg(sal) >= 2000;

-- 부서별 최대 급여액이 3000이하인 부서의 급여 총합을 가져온다.
SELECT deptno, sum(sal)
FROM emp
GROUP BY deptno
HAVING max(sal) <= 3000

-- 부서별 최소 급여액이 1000 이하인 부서에서 직무가 CLERK인 사원들의 급여 총합을 구한다
SELECT sum(sal)
FROM emp
WHERE job = 'CLERK'
GROUP BY deptno
HAVING min(sal) <= 1000

-- 각 부서의 급여 최소가 900이상 최대가 10000이하인 부서의 사원들 중 1500이상의
-- 급여를 받는 사원들의 평균 급여액을 가져온다
SELECT avg(sal)
FROM emp
WHERE sla >= 1500
GROUP BY deptno
HAVING min(sal) >= 900 and max(sal) <= 10000;
          

조인(join)

조인을 사용하면 2개 이상의 테이블의 데이터를 한번에 가져올 수 있다.


SELECT 컬럼명 FROM 테이블1, 테이블2;

-- 사원테이블(emp)와 부서테이블(dept)을 join 한다.
SELECT *
FROM emp a1, dept a2
WHERE a1.deptno = a2.deptno;

-- 사원의 사원번호, 이름, 근무부서 이름을 가져온다.
SELECT a1.empno, a1.ename, a2.dname
FROM emp a1, dept a2
WHERE a1.deptno = a2.deptno;

-- 사원의 사원번호, 이름, 근무지역을 가져온다.
SELECT a1.empno, a1.ename, a2.loc
FROM emp a1, dept a2
WHERE a1.deptno = a2.deptno;

-- DALLS에 근무하고 있는 사원들의 사원번호, 이름, 직무를 가져온다.
SELECT a1.empno, a1.ename, a1.job
FROM emp a1, dept a2
WHERE a1.deptno = a2.deptno and a2.loc = 'DALLS';

-- SALES 부서에 근무하고 있는 사원들의 급여 평균을 가져온다.
SELECT avg(sal)
FROM emp a1, dept a2
WHERE a1.deptno = a2.deptno and a2.dname = 'SALES';

-- 1982년에 입사한 사원들의 사원번호, 이름, 입사일, 근무부서이름을 가져온다
SELECT a1.empno, a1.ename, a1.hiredate, a2.dname
FROM emp a1, dept a2
WHERE a1.deptno = a2.deptno and a1.hiredate between '1982/01/01' and '1982/12/31';

-- 각 사원들의 사원번호, 이름, 급여, 급여등급을 가져온다.
SELECT a1.empno, a1.ename, a1.sal, a2.grade
FROM emp a1, salgrade a2
WHERE a1.sal BETWEEN a2.losal and a2.hisal;

-- SALES 부서에 근무하고 있는 사원의 사원번호, 이름, 급여등급을 가져온다.
SELECT a1.empno, a1.enema, a2.grade
FROM emp a1, salgrade a2, dept a3
WHERE a1.sal BETWEEN a2.losal and a2.hisal and a1.deptno = a3.deptno and a3.dname = 'SALES';

-- 각 급여 등급별 급여의 총합과 평균, 사원의수, 최대급여, 최소급여를 가져온다
SELECT sum(a1.sal), avg(a1.sal), count(a1.sal), max(a1.sal), min(a1.sal)
FROM emp a1, salgrade a2
WHERE a1.sal BETWEEN a2.losal and a2.hisal
GROUP By a2.grade;

-- 급여 등급이 4등급인 사원들의 사원번호, 이름, 급여, 근무부서이름, 근무지역을 가져온다.
SELECT a1.empno, a1.ename, a1.sal, d3.dname, d3.loc
FROM emp a1, salgrade a2, dept a3
WHERE a1.sal BETWEEN a2.losal and a2.hisal and a1.deptno = a3.deptno and a2.grade=4;
          

Self Join, Outer Join

Self join

같은 테이블을 두 번 이상 조인하는 것을 self join 이라고 한다.


-- SMITH 사원의 사원번호, 이름, 직속상관 이름을 가져온다.
-- a1 : SMITH 사원의 정보
-- a2 : 직속상관 정보
SELECT a1.emono
WHERE a1.mgr = a2, a1.ename, a2.ename
FROM emp a1, emp a2.empno and a1.ename = 'SMITH';

-- FORD 사원 밑에서 일하는 사원들의 사원번호, 이름, 직무를 가져온다
-- a1 : FORD 사원 정보
-- a2 : 부하 직원의 정보
SELECT a2.empno, a2.ename, a2.jop
FROM emp a1, emp a2
WHERE a1.empno = a2.mrg and a1.empno = 'FORD'

-- SMITH 서원의 직속상관과 동일한 직무를 가지고 있는 사원들의 사원번호, 이름, 직무를 가져온다
-- a1 :  SMITH의 정보
-- a2 :  SMITH의 직속사관 정보
-- a3 :  직속상관과 동일한 직무를 가지고 있는 사원들의 정보
SELECT a3.empno, a3.ename, a3.jop
FROM emp a1, emp a2, emp a3
WHERE a1.mrg = a2.empno and a2.job = a3.job and a1.ename = 'SMITH';
          

outer join

조인 조건에 만족하지 못해 제거되는 로우까지 모두 가져오는 것을 outer join 이라고 한다.


-- 각 사원의 이름, 사원번호, 직장상사 이름을 가져온다.
-- 단 직속상관이 없는 사원도 가져온다.
-- a1 : 각 사원의 정보
-- a2 : 직장상사의 정보
SELECT a1.ename, a1.empno, a2.ename
FROM emp a1, emp a2
WHERE a1.mgr = a2.empno(+);

-- 모든 부서의 소속 사원의 근무부서명, 사원번호, 사원이름, 급여를 가져온다
-- a1 : 
-- a2 : 
SELECT a2.dname a1.empno, a1.ename, a1.sal
FROM emp a1, dept a2
WHERE a1.deptno(+) = a2.deptno;
          

서브 쿼리(sub qeury)

쿼리문 안의 쿼리문을 의미하며 쿼리문 작성이 값이 들어가는 부분을 구해야 할 경우 사용한다.


-- SCOTT 사원이 근무하고 있는 부서의 이름을 가져온다.
SELECT dname
FROM dept
WHERE deptno = (SELECT deptno
                FROM emp
                WHERE ename = 'SCOTT');
                
-- SMITH와 같은 부서에 근무하고 있는 사원의 사원번호, 이름, 급여액, 부서이름을 가져온다.
SELECT a1.empno, a1.ename, a1.sal, a2.dname
FROM emp a1, dept a2
WHERE a1.depno = a2.depno and a1.deptno = (SELECT deptno
                                            FROM emp
                                            WHERE ename = 'SMITH');
                                            
-- MARTIN 과 같은 직무를 가지고 있는 사원들의 사원번호, 이름, 직무를 가져온다.
SELECT empno, ename, jop
FROM emp
WHERE jop = (SELECT jop
            FROM emp
            WHERE ename = 'MARTIN');
            
-- ALLEN 과 같은 직속상관을 가진 사원의 사원번호, 이름, 직속상관이름을 가져온다.
-- a1 :  사원의 정보
-- a2 : 직속상관 정보

SELECT a1.empno, a1.ename, a2.ename 
FROM emp a1, emp a2
WHERE a1.mgr = a2.empno and a1.mgr = (SELECT mgr
                                        FROM emp
                                        WHERE ename = 'ALLEN');
                                        
-- WARD 와 같은 부서에 근무하고 있는 사원들의 사원번호, 이름, 부서번호를 가져온다.
SELECT empno, ename, deptno
FROM emp a1
WHERE deptno = (SELECT deptnp
                    FROM emp
                    WHERE ename = 'WARD');
                    
-- SALESMAN 의 평균 급여보다 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.
SELECT empno, ename, sal
FROM emp
WHERE sal > (SELECT avg(sal)
            FROM emp
            WHERE job = 'SALEMAN');
            
-- DALLAS 지역에 근무하는 사원들의 평균 급여를 가져온다
SELECT avg(sal)
FROM emp
WHERE deptno = (SELECT deptno
                    FROM dept
                    WHERE dname = 'DALLAS');

-- SALES 부서에 근무하는 사원들의 사원번호, 이름, 근무지역을 가져온다.
SELECT empno, ename, a2.dname
FROM emp a1, dept a2
WHERE a1.deptno = a2.deptno and a1.deptno = (SELECT deptno
                    FROM dept
                    WHERE dname = 'SALES');

-- CHICAGO 지역에 근무하는 사원들 중 BLACKE이 직속상관인 사원들의 사원번호, 이름, 직무를 가져온다.
SELECT empno, ename, job
FROM emp
WHERE deptno = (SELECT deptno
                FROM dept
                WHERE loc = 'CHICAGE') and
    mgr = (SELECT empno
            FROM emp
            WHERE ename = 'BLACK');
          

결과가 하나 이상인 서브쿼리

서브쿼리의 결과가 하나 이상인 경우 IN, ANY, ALL 등의 연산자를 사용한다.

종류 설명
IN 서브쿼리의 결과중 하나라도 일치하면 조건은 참이 된다.
ANY, SOME 서브쿼리의 결과와 하나 이상 일치하면 조건은 참이 된다.
ALL 모두 일치해야 조건은 참이 된다.

-- 3000 이상의 급여를 받는 사원들과 같은 부서에 근무하고 있는 사원의 사원번호, 이름, 급여를 가져온다.
SELECT empno, ename, sal
FROM emp
WHERE deptno IN (SELECT deptno
                FROM emp
                WHERE sal >= 3000);
                
-- 직무가 CLERK인 사원과 동일한 부서에 근무하고 있는 사원들의 사원번호, 이름, 입사일을 가져온다.
SELECT empno, ename, hiredate
FROM emp
WHERE deptno in (SELECT deptno
                FROM emp
                WHERE job = 'CLERK');

-- KING을 직속상관으로 가지고 있는 사원들의 근무하고 있는 근무 부서명, 지역을 가져온다.
SELECT dname, loc
FROM dept
WHERE deptno in (SELECT deptno
                    FROM emp
                    WHERE mgr = (SELECT empno
                                    FROM emp
                                    WHERE ename = 'KING'));

-- CLERK들의 직속상관의 사원번호 이름 급여를 가져온다
SELECT empno, ename, sal
FROM emp
WHERE mrg in (SELECT mgr
                FROM emp
                WHERE job = 'CLERK');
                
-- 각 부서별 급여 평균보다 더 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다
SELECT empno, ename, sal
FROM emp
WHERE sla > ALL (SELECT avg(sal)
                    FROM emp
                    GROUP BY deptno);

SELECT empno, ename, sal
FROM emp
WHERE sla > (SELECT max(avg(sal))
                    FROM emp
                    GROUP BY deptno);
                    
-- 각 부서별 급여 최저치보다 더 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.
SELECT empno, ename, sal
FROM emp
WHERE sal > ALL (SELECT min(sal)
                FROM emp
                GROUP BY deptno);

-- SEALSMAN 보다 급여를 적게 받는 사원들의 사원번호, 이름, 급여를 가져온다.
SELECT empno, ename, sal
FROM emp
WHERE sla < ALL (SELECT sal
                FROM emp
                WHERE job = 'SEALSMAN');

-- 각 부서별 급여 최저액 중 제일 작은 액수보다 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.
SELECT empno, ename, sal
FROM emp
WHERE sal > any (SELECT min(sal)
                    FROM emp
                    GROUP BY drptno);
                    
-- DALLAS에 근무하고 있는 사원들 중 가장 나중에 입사한 사원의 입사 날짜보다 더 먼저 입사한
-- 사원들의 사원번호, 이름, 입사일을 가져온다.
SELECT empno, ename, hiredate
FROM emp
WHERE hiredata < any (SELECT hiredate
                        FROM emp
                        WHERE deptno = (SELECT deptno
                                        FROM dept
                                        WHERE loc = 'DALLAS'));
          

SET

SET 구분들을 이용하여 두 select 문의 결과를 집합 연산 할 수 있다.

종류 설명
UNION 합집합, 중복 제거
UNION ALL 합집합, 중복된 데이터를 모두 가져온다.
INTERSECT 교집합, 공통인것만 출력
MINUS 차집함, 공통아닌것만 출력

02. insert, update, delete

Insert

테이블에 새로운 로우를 추가하는 구문이다.

Insert 문법


insert into 테이블 명(컬람명)
values(값 ...)

INSERT INTO 테이블명
서브쿼리

INSERT ALL
INTO 테이블명(컬럼명) VALUES(컬럼명)
INTO 테이블명(컬럼명) VALUES(컬럼명)
서브쿼리
          

Insert 예제 - 기본


-- 다음과 같은 사원 정보를 추가한다.
-- 1111 홍길동 인사
-- 2222 김길동 개발
-- 3333 최길동 인사
-- 4444 박길동 생산
INSERT INTO emp01 (empno, ename, job)
values(1111, '홍길동', '인사');

INSERT INTO emp01 (empno, ename, job)
values(2222, '김길동', '개발');

INSERT INTO emp01 (empno, ename, job)
values(3333, '최길동', '인사');

INSERT INTO emp01 (empno, ename, job)
values(4444, '박길동', '생산');
          

컬럼 목록을 생략하는 경우


-- 컬럼 목록을 생략하는 경우
INSERT INTO emp01
VALUES (5555, '황길동', '개발');
          

null을 명시적을 저장


-- null을 명시적을 저장
INSERT INTO emp01 (empno, ename, job)
VALUES(7777, '박보검', null);
          

서브쿼리로 데이터 저장하기


-- 서브쿼리로 데이터 삽입
INSERT ALL
INTO emp03 (empno, ename, job) VALUES (empno, ename, job)
INTO emp04 (empno, ename, hiredate) VALUES (empno, ename, hiredate)
SELECT empno, ename, job, hiredate FROM emp;

-- 실습#1
-- 사원번호 이름 급여를 저장할수 있는 빈 테이블을 만들고
-- 급여가 1500 이상인 사원들의 사원번호, 이름 급여를 저장한다.
INSERT INTO emp05(empno, ename, sal)
SELECT empno, ename, sal
FROM emp
WHERE sal>=1500;

-- 실습#2
-- 사원번호, 이름, 부서명을 저장할수 있는 빈 테이블 생성
-- DALLAS 지역에 근무하고 있는 사원들의 사원번호, 이름, 부서명을 저장한다.
INSERT INTO emp06(empno, ename, dname)
SELECT a1.empno, a1.ename, a2.dname 
FROM emp a1, dept a2
WHERE a1.deptno = a2.deptno and 1=0;
          

update

컬럼의 값을 수정하는 구문이다.

update 문법


UPDATE 테이블 명
SET 컬럼=값, 컬럼=값....
WHERE 조건문
          

update 예제 - 기본


-- 사원들의 부서 번호를 40번으로 변경한다.
update emp01
set deptno = 40;

-- 사원들의 입사일을 오늘로 변경한다.
update emp01
set hiredate = sysdate;

-- 사원들의 직무를 개발자로 변경
update emp01
set job = '개발자';
          

update 예제 - 여러개 한번에 수정


-- 사원들의 부서번호를 40, 입사일을 오늘, 직무를 개발자로 변경
update emp01
set deptno = 40, hiredate = sysdate, job = '개발자';
          

update 예제 - 조건절 추가


-- 조건절을 추가하여 update
-- SALESMAN 들의 입사일을 오늘, 커미션을 2000으로 수정
update emp01
set hiredate = sysdate, comm = 2000
where job = 'SALESMAN';

-- 10번 부서에 근무하고 있는 사원들을 40번 부서로 수정한다.
update emp01
set deptno = 40
where deptno = 10;

-- 전체 사원의 평균 급여보다 낮은 사원들의 급여를 50% 인상한다.
update emp01
set sal = sal *1.5
WHERE sal < (select avg(sal)
                from emp01);

-- MANAGER인 사원들의 직무를 DEVELOPER 로 변경한다
update emp01
set job = 'developer'
where job = 'manager';

-- 30번 부서에 근무하고 있는 사원들의 급여를 전체 편균 급여로 설정한다.
update emp01
set sal = (select avg(sal) from emp01)
where deptno = 30;

-- BLAKE 밑에서 근무하고 있는 사원들의 부서를 DALLAS 지역의 부서번호로 이동한다
update emp01
set deptno = (select deptno
                from dept
                where loc = 'DALLAS')
where mgr = (select empno
                from emp01
                where ename = 'BLAKE');

-- 20번 부서에 근무하고 있는 사원들의 직속상관을 KING으로 하고 급여를 전체급여의 최고액으로 설정한다.
update emp01
set mgr = (select empno
            from emp01
            where ename = 'KING'),
    sal = (select max(sal)
            from emp01)
where deptno = 20;
          

update 예제 - set을 한번에 수정


-- set을 한번에 처리하는 방법
-- 직무가 CLERK 인 사원들의 직무와 급여액을 20번 부서에 근무하고 잇는 사원 중
-- 급여 최고액을 받는 사원의 직무와 급여액으로 변경한다.
update emp01
set (job, sal) = (select job, sal
                    from emp01
                    where sal = (select max(sal)
                                    from emp01))
where job = 'CLERK';
          

delete

테이블 내의 로우를 삭제하는 구문이다.

delete 문법


delete from 테이블 명
where 조건문
          

delete 예제 - 모든 데이터 삭제


-- emp01 모든 데이터 삭제
delete from emp01;
          

delete 예제 - 조건절 추가


-- 조건절 추가
-- 사원번호가 7499인 사원의 정보를 삭제한다
delete from emp01
where empno = 7499;

-- 사원의 급여가 평균 급여 이하인 사원의 정보를 삭제
delete from emp01
where sal < (select avg(sal)
            from emp01);

-- 커미션을 받지 않는 사원들의 정보를 삭제한다.
delete from emp01
where comm is null;
          

03. 테이블 만들기

테이블 만들기

컬럼의 자료형

종류 설명
char(size) 고정 길이 문자열 타입
varchar2(size) 가변 길이 문자열 타입
number 최고 40자리까지 저장할수 있는 숫자 타입
data 날짜
long 가변길이 문자열 타입, 최대 2Gbyte

테이블 생성 문법


create table 테이블명(
  컬럼명 자료형 제약조건,
  컬럼명 자료형 제약조건
)
          

서브쿼리로 테이블 만들기


-- 서브쿼리로 테이블 만들기
create table 테이블명
as
서브쿼리
          

테이블 생성


-- 테이블 기본 생성
-- 다음과 같은 정보를 저장하기 위한 테이블을 만든다.
-- 학생번호, 학생이름, 학생나이, 국어점수, 영어점수, 수학점수
CREATE TABLE stu_table(
    stu_idx number,
    stu_name char(10),
    stu_age number,
    stu_kor number,
    stu_eng number,
    stu_math number
);
-- stu_table에 insert
insert into stu_table(stu_idx, stu_name, stu_age, stu_kor, stu_eng, stu_math) 
values (1111, '홍길동', 30, 100, 80, 70);
-- stu_table 확인
select * from stu_table;
          

number 타입 테이블 생성


-- number 타입 테이블 생성
create table number_table(
number1 number,
number2 number(3), -- 3자리까지만 가능
number3 number(5, 2) -- 앞에서 5자리 소수점은 2자리까지만 저장
);

insert into number_table(number1)
values(10000);

select * from number_table;
          

서브쿼리로 테이블 만들기


-- 서브쿼리로 테이블 만들기
-- emp 테이블 복제한 emp01 테이블을 만드시오
create table emp01
as
select * from emp;

select * from emp01;

-- emp 테이블에서 사원의 번호, 이름, 급여 정보를 가지고 있는 테이블을 생성하시오
create table emp02
as
select empno, ename, sal from emp;

select * from emp02;

-- 30번 부에서 근무하고 있는 사원들의 사원번호, 이름, 근무부서 이름을 가지고 있는 테이블을 생성한다.
create table emp03
as
select a1.empno, a1.ename, a2.
dname
from emp a1, dept a2
where a1.deptno = a2.deptno and a1.deptno = 30;

-- 각 부서별 급여 총합, 평균, 최고액, 최저액, 사원수를 가지고 있는 테이블을 생성하시오
create table emp04
as
select sum(sal) as sum, avg(sal) as avg, max(sal) as max, min(sal) as min, count(sal) as count 
from emp
group by deptno;

select * from emp04;
          

제약조건

테이블의 데이터를 저장 혹은 수정할떄 컬럼의 값에 대한 조건을 설정하는 것을 제약 조건이라고 한다.

설정된 조건에 위배되는 값을 컬럼에 저장할 수 없으며 데이터의 무결성을 위한 구문이다.

종류 설명
not null 컬럼에 null을 허용하지 않는다.
unique 중복된 값을 허용하지 않는다.(null 은 허용)
primary key 중복값을 허용하지 않으며 null 값을 허용하지 않는다.
foreign key 다른 테이블 혹은 같은 테이블의 컬럼을 참조하는 제약 조건이다.
참조하는 컬럼에 저장되어 있는 값만 컬럼에 저장할 수 있다.
일반적으로 primary key 제약 조건이 설정된 컬럼을 참조한다.
check 조건메 만족할 경우 컬럼에 저장할 수 있도록 한다.

NOT NULL

해당 컬럼에는 NULL을 저장할 수 없다.


create table test_table(
    data1 number,
    data2 number not null
);
          

unique

중복된 값을 허용하기 않고 null은 무한대로 허용한다.


-- unique : 중복된 값을 허용하기 않고 null은 무한대로 허용한다.
create table test_table2(
    data1 number,
    data2 number CONSTRAINT TEST_TABLE2_DATA_UK unique
);
          

primary key

중복을 허용하지 않고 null을 허용하지 않는다.


-- primary key : 중복을 허용하지 않고 null을 허용하지 않는다.
create table test_table3(
  data1 number,
  data2 number constraint TEST_TABLE_DATA_PK primary key
);    
          

foreign key

특정 테이블의 컬럼을 참조하는 제약조건


-- 외래키 : 특정 테이블의 컬럼을 참조하는 제약조건
create table test_table4(
    data1 number constraint TEST_TABLE4_PK primary key,
    data2 number not null
);
create table test_table5(
    data3 number not null,
    data4 number constraint TEST_TABLE5_DATA4_FK 
                    references test_table4(data1)
);
          

check

컬럼에 저장될 값을 지정한다


-- check 제약조건 : 컬럼에 저장될 값을 지정한다.
create table test_table6(
    data1 number constraint TEST_TABLE6_DATA1_CK
                check(data1 between 1 and 10), -- 1부터 10까지만 지정
    data2 number constraint TEST_TABLE6_DATA2_CK
                check(data2 in(10, 20, 30)) -- 10,20,30만 지정
);
          

테이블 레벨 제약조건

제약 조건을 설정할 때 각 컬럼 마다 지정할 수도 있지만 하단 부분에 몰아서 자정할 수도 있다.

컬럼 명 옆에 기술하는 것을 컬럼 레벨, 하단에 몰아서 기술하는 것을 테이블 레벵 제약조건 이라고 한다.

컬럼 레벨 제약조건


-- 컬럼 레벨 제약조건
create table test_table10(
    data1 number constraint TEET_TABLE10_DATA1_PK primary key,
    data2 number not null constraint TEET_TABLE10_DATA2_UK unique,
    data3 number not null constraint TEET_TABLE10_DATA3_FK
                            references emp(empno),
    data4 number not null constraint TEET_TABLE10_DATA4_CK
                            check(data4 between 1 and 10),
    data5 number not null constraint TEET_TABLE10_DATA5_CK
                            check(data5 in(10,20,30))
);
          

테이블 레벨 제약조건


-- 테이블 레벨 제약조건
create table test_table11(
 data1 number,
 data2 number not null,
 data3 number not null,
 data4 number not null,
 data5 number not null,
 
 constraint TEST_TABLE11_DATA1_PK primary key(data1),
 constraint TEST_TABLE11_DATA2_UK unique(data2),
 constraint TEST_TABLE11_DATA3_FK foreign key(data3)
                                    references emp01(empno),
 constraint TEST_TABLE11_DATA4_CK check(data4 between 1 and 10),
 constraint TEST_TABLE11_DATA5_CK check(data5 in(10, 20, 30))
);
          

복합키 제약조건

테이블 레벨 제약조건을 설정할 떄 하나 이상의 컬럼을 하나의 primary key로 묶어서 사용할 수 있다.

복합키의 경우 각 컬럼에 중복된 데이터가 허용이 되지만 한 로우의 모든 복합키 컬럼이 중복되는 것은 허용하지 않는다.


-- 복합키
create table test_table12(
 data1 number,
 data2 number,
 
 constraint TEST_TABLE12_COMBO_PK primary key(data1, data2)
);
          

제약조건 추가하기

테이블을 생성한 후 제약 조건을 추가 하거나 제거하고 싶다면 alter 구문을 이용한다.

제약 조건 추가 기본 문법


alter table 테이블명
add 제약조건
          

null 제약조건 추가


alter table test_table20
modify data1 not null;
          

primary key 제약 조건 추가


alter table test_table20
add constraint TEST_TABLE20_DATA2_PK primary key(data2);
          

foreign key 제약조건 추가


alter table test_table20
add constraint TEST_TABLE20_DATA3_fK foreign key(data3)
                    references emp(empno);
          

unique 제약조건 추가


alter table test_table20
add constraint TEST_TABLE20_DATA4_UK unique(data4);
          

check 제약조건 추가


alter table test_table20
add constraint TEST_TABLE20_DATA5_CK check(data5 between 1 and 10);

alter table test_table20
add constraint TEST_TABLE20_DATA6_CK check(data6 in(10,20,30));
          

제약조건 삭제하기

테이블을 생성한 후 제약 조건을 추가 하거나 제거하고 싶다면 alter 구문을 이용한다.

제약 조건 삭제 기본 문법


alter table 테이블명
drop 제약조건
          

not null 제약조건 제거


alter table test_table30
modify data1 null;
          

primary key 제약조건 제거


alter table test_table30
drop constraint TEST_TABLE30_DATA2_PK;
          

foreign key 제약조건 제거


alter table test_table30
drop constraint TEST_TABLE30_DATA3_FK;
          

unique 제약 조건 제거


alter table test_table30
drop constraint TEST_TABLE30_DATA4_UK;
          

check 제약조건 제거


alter table test_table30
drop constraint TEST_TABLE30_DATA5_UK;
          

제약조건 활성/비활성

disable을 사용하면 제약 조건이 비활성화 되고 enable을 사용하면 제약조건이 활성화 된다.

제약조건 활성/비활성 기본 문법


-- 활성화
alter table 테이블명
enable 제약조건

-- 비활성화
alter table 테이블명
disable 제약조건
          

제약조건 비활성화


alter table test_table40
disable constraint TEST_TABLE40_DATA1_PK;
          

제약조건 활성화


-- 제약조건 활성화
-- **제약조건에 위배되는 값이 컬럼에 있을 경우 에러뜸**
alter table test_table40
enable constraint TEST_TABLE40_DATA1_PK;
          

테이블 구조 변경하기

alter 구문을 이용하면 테이블 구조를 변경할 수 있다.

테이블 구조 변경 기본 문법


-- 추가
alter table 테이블명
add (컬럼명 자료형 제약조건)

-- 수정
alter table 테이블명
modify (컬럼명 자료형 제약조건)

-- 테이블 이름 변경
alter table 테이블명
rename to 테이블 명

-- 컬럼의 이름 변경
alter table 테이블명
rename column 컬럼명1 컬럼명2

-- 컬럼 삭제
alter table 테이블명
drop column 컬럼명

-- 테이블 삭제
drop table 테이블명
          

컬럼 추가


alter table test_table1
add (data3 number not null);
          

컬럼의 데이터 타입 변경


alter table test_table1
modify (data3 VARCHAR2(100));
          

테이블 이름 변경


alter table test_table1
rename to test_table2;
          

컬럼의 이름을 변경


alter table test_table1
rename column data3 to data4;
          

컬럼 삭제


alter table test_table1
drop column data4;
          

테이블 삭제


drop table test_table1;
          

04. 트랜잭션 관리 및 인덱스

트랜잭션 관리

트랜잭션이란 최종 결과를 내기까지 위한 하나의 작업 다위를 의미한다.

오라클 데이터베이스는 개발자가 전달한 insert, update, delete 문을 메모리상에서만 수행하고 디스크에 반영하지 않는다. (실수로 인한 데이터의 유실을 막기 위함)

데이터 베이스를 조작하는 작업이 완료되고 모두 정상적으로 되었다면 이를 디스크에 반영해야 한다.

작업이 시작되고 디스크에 반영될 때 까지의 작업의 단위를 트랜잭션이라 부르며 트랜잭션이 완료되면 디스크에 반영하여 저장하게 한다.

트랜잭션 명령어

종류 설명
commit 트랜잭션을 완료하고 디스크에 반영한다. (복구불가)
rollback 트랜잭션을 취소한다.
savepoint rollback의 단위를 저장한다.

시퀀스

테이블 내의 컬럼 중 primary key 를 지정하기 애매한 경우 1부터 1씩 증가되는 값을 저장하는 컬럼을 위해 1부터 1씩 증가되는 값을 구하기 위해 시퀀스를 사용한다.

종류 설명
start with 숫자 시작 값, 시작 값은 절대 최소 값보다 작을 수 없다.
increment by 증가 값
maxvalue 시퀀스가 가질 수 있는 최대 값. (생략하거나 nomaxvalue일 경우 10의 27승)
minvalue 시퀀스가 가질 수 있는 최소 값. (생략하거나 niminvalue일 경우 1)
cycle 최대 혹은 최소값까지 갈 경우 순환한다.
nextval 다음 시퀀스 값을 가지고 온다.
currval 현재 시퀀스 값을 가지고 온다.

create sequence 시퀀스 이름
start whith 숫자
increment by 숫자
maxvalue 숫자 or nomaxvalue
minvalue 숫자 or nominvalue
cycle or nocycle
cache 숫자 or nocache
          

시퀀스 예제


create sequence test_seq1
start with 0
increment by 1
minvalue 0;
          

인덱스(index)

데이터 베이스에서 검색속도를 빠르게 하기 위해 사용하는 기능

인덱스의 장점

  • 검색 속도가 빨라진다
  • 시스템의 부하를 줄여 성능 향상

인덱스의 단점

  • 추가적인 기억공간이 필요
  • 인덱스 생성 시간이 오래 걸림
  • insert, update, delete 와 같은 변경 작업이 자주 일어나면 오리혀 성능 저하됨

인덱스 정보 조회하기


select index_name, table_name
column_name from user_ind_colums
where table_name = '테이블명';
          

뷰(view)

뷰는 데이터베이스에서 제공하는 가상의 테이블을 의미한다.

뷰를 사용하면 복잡한 쿼리문을 대신할 수 있기 떄문에 개발의 용의성을 가질수 있다.

뷰는 뷰를 만들 때 사용한 쿼리문을 저장하는 것이며 뷰를 조회할 때 뷰를 만들떄 사용한 쿼리문이 동작하게 된다.

뷰 생성하기


create view 뷰이름
as
서브쿼리
          

뷰 생성 권한 설정

일반 계정의 경우 뷰를 생성할 수 있는 권한이 없기 때문에 뷰 생성 권한을 설정해 줘야 한다.


grant create view to 계정이름
          

뷰 기본 예제


-- 사원의 사원번호, 이름, 급여, 근무부서이름, 가지고 있는 뷰를 생성한다,
create view emp_dept_view
as
select a1.empno, a1.ename
from emp a1, dept a2
where a1.deptno = a2.deptno;

-- 뷰를 조회한다
select * from emp_dept_view;
          

05. 프로그램과 연동하기

테이블 스페이스

프로그램과 연동하기