Oracle 쿼리 정리 [Ctrl + F 용]
#개요
옛~날에 학원을 다닐때 Oracle 수업을 들으며 열심히 쿼리를 정리해뒀던 문서를 발견했다.
오라클을 잘 쓰진 않지만, 필요로 할때 쿼리 예제를 빠르게 찾기위해 여기에 올려두려고 한다.
1. SQL 기본
cmd에서 실행:
sqlplus scott/tiger
cmd 한줄에서 보기:
set linesize 150
유저접속:
conn xxxxx(계정명) /
disc
유저확인:
show user
구조보기:
desc XXXX(테이블명)
============================
1.컬럼에 산술연산 가능
select ename, sal, sal*12 from emp;
(널 처리 주의!) > (x+Null = null)
2. 별칭지정 (헤딩)
select ename as "사원 이름", sal as"급여", sal*12 as "연봉" from emp
3. 널처리
함수이름(널 있는컬럼,대채값)
nvl(comm,0)
ex) select ename as"사원 이름" , sal as "급여" , (sal*12)+comm as "함수없는 보너스연봉", (sal*12)+ nvl(comm,0) as "함수포함 보너스연봉" from emp;
4.중복된행 제거
select DISTINCT deptno from emp;
2. SQL 명령어
조건연산자 : where
select * from emp where sal <> 2000;
ex) 부서번호가 10번인 사람들을 찾으시오
select * from emp where deptno = 10;
1. 비교연산자
=, <, >, <> .....
select * from emp where sal > 3000;
2. 문자데이터 조회
where aa = 'X' -> 단일따옴표 안의 값을 찾음, 대소문자 구분
select * from emp where ename = 'FORD';
3.날짜데이터 조회
날짜도 문자열과 마찬가지로 단일 따옴표 안에 기술
select * from emp where hiredate <= '1982/01/01';
4. 논리연산자
AND , OR, NOT
5. BETWEEN AND 연산자
사이값 출력
select * from emp where sal >= 2000 AND sal <= 3000;
select * from emp where sal BETWEEN 2000 AND 3000; //같은값
6. IN연산자
OR연산자와 같은 기능
커미션이 300,500,1400 검색 > where comm IN(300,500,1400);
7. like연산자와 와일드카드
%는 뒤에 문자 모르겠을 때, 그 뒤로 문자 상관없이 출력
select * from emp where ename like 'F%';
select * from emp where ename like 'FORD%';
select * from emp where ename like '%A%';
_는 %와 마찬가지로 어떤 문자든 상관 X , 그러나 단 한문자에만 해당
select * from emp where ename like '_A%';
select * from emp where ename like '__R%';
NOT LIKE도 활용
검색할 값중에 _문자가 있는경우 : where ename LIKE '%_%' ESCAPE '_';
8. NULL
NULL은 =가 아닌 is를 써야함
select * from emp where COMM is NULL
9.ORDER BY 절 : ASC(오름차순) / DESC (내림차순)
where절 다음에 나옴, 생략할 시 ASC로 정렬;
select * from emp order by sal DESC
동일한 값의 정렬 > order by job ASC, ename ASC
3. SQL 주요 함수
DUAL 테이블 : 계산을 테스트해보기 위한 테이블
1. 숫자함수
ABS(대상) : 절대값을 구함
select -10 , ABS(-10) from dual;
FLOOR(대상) : 소수점아래 버림
select 34.5678, FLOOR(34.5678) from dual;
ROUND(대상, 자릿수) : 특정 자릿수에서 반올림
select 34.5678, ROUND(34.5678) from dual;
TRUNC(대상, 자릿수) : 특정 자릿수에서 버림 : ROUND와 동일
자릿수 음수도 가능
MOD : 나머지값 반환
select MOD(27,2) from dual;
2.문자처리 함수
UPPER(대상) : 입력값을 모두 대문자로 변환
LOWER(대상) : 입력값을 모두 소문자로 변환 : 위와 동일
INITCAP(대상) : 이니셜만 대문자로 변환하는 함수 : 위와 동일
select UPPER('Welcome to Oracle') from dual;
* 직급이 manager(소문자) 인 사원을 검색 :
select * from emp where LOWER(job) = 'manager';
LENGTH(대상) : 문자 길이를 구함
LENGTHB(대상) : 바이트 수를 알려줌
select LENGTH('오라클') from dual;
SUBSTR(대상,시작위치,갯수) : 문자열 일부를 출력
SUBSTRB(대상,시작위치,갯수) : 문자열 일부 바이트를 세서 출력
1 2 3 4 5 5 6 7 8 9 10...
시작위치를 음수로 표현할경우 뒤에서 추출 : ...-5 -4 -3 –2 -1
select SUBSTR('Hello Oracle',2,5) from dual;
INSTR(대상,찾을대상,x,y) : x자리부터 y번째 '대상'이 몇번짼지 추출
INSTRB(대상,찾을대상,x,y) : 바이트를 추출
select INSTRB('데이터베이스','이',3,1) from dual;
* 이름의 세번째 자리가 R로 끝나는 사원을 검색 (SUBSTR, INSTR 이용)
where INSTR(ename,'R',3,1) = 3; / where SIBSTR(ename,3,1) = 'R'
LPAD/RPAD(대상,x,y) : x자리중 대상을 제외한 나머지를 y로 채움
select LPAD('oracle', 20,'#') from dual;
ex)주민번호 뒷자리를 감춤 등등...
LTRIM/RTRIM(대상) : 대상의 왼/오른쪽 공백문자를 삭제
select LTRIM(' Oracle ') from dual;
3. 날짜함수
SYSDATE : 시스템상의 날짜(오늘날짜)
select sysdate from dual;
* 근무일수 구하기
select sysdate-hiredate from emp;
ROUND(대상,포멧) : 포멧모델을 참조하여 반올림
select ROUND(hiredate, 'MONTH') from emp;
TRUNC(대상,포멧) : 포멧모델을 참조하여 버림
select hiredate, TRUNC(hiredate,'month') from emp;
MONTHS_BETWEEN(대상1,대상2) : 두 날짜 사이 간격을 구함
* 근무개월수 :
select ename, MONTHS_BETWEEN (SYSDATE,hiredate) from emp;
ADD_MONTHS(대상,숫자) : 개월 수를 더함
*오늘부터 6개월을 더함 :
select sysdate , ADD_MONTHS(SYSDATE, 3) from dual;
NEXT_DAY(date,요일) : 해당 요일의 가장 가까운 날짜 반환
*오늘기준부터 수요일은 언제인지 :
select SYSDATE, NEXT_DAY(SYSDATE, '수요일') from dual;
LATE_DAY(대상) : 해당달의 마지막 날짜 반환
* 입사한 달의 마지막 날짜 :
select HIREDATE, LAST_DAY(hiredate) from emp;
4. 형변환함수
4-1. TO_CHAR(날짜데이터,'출력형식') : 날짜를 문자열 형식으로 바꿈
날짜출력 : YYYY,YY,MM,MON,DAY,DY
시간출력 : AM,PM,HH12,HH24,MI,SS
숫자출력 : 0 9 L . ,
select TO_CHAR(sysdate, 'YYYY/MM/DD, HH24:MI:SS') from dual;
4-2. TO_DATE('문자', '형식') : 문자열을 날짜형식으로 바꿈
select SYSDATE-TO_DATE('2008/01/01', 'yyyy/mm/dd') from dual;
4-3 TO_NUMBER : 특정 데이터를 숫자로 변환
5.NVL(컬럼,대체값) : NULL을 대체값으로 변환
select comm, nvl(comm,0) from emp;
6. DECODE(컬럼, 조건 1, 결과 1, 조건2. 결과2, 조건3 결과3...) as 컬럼명 : 조건함수
select empno, ename, job, sal, decode(job,'ANAIYST', sal*1.05,'SALESMAN',sal*1.1,'MANAGER',sal*1.15,'CLERK',sal*1.2)
as upsal from emp;
7. CASE 표현식 WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
WHEN 조건3 THEN 결과3
ELSE 결과n
END
4. 그룹함수
1. SUM, AVG, COUNT,MAX, MIN
널 제외하고 연산, 따로 널처리 안해줘도 가능
단일행 쿼리랑 중복사용불가
select COUNT(DISTINCT deptno) from emp;
2.GROUP BY : 동일한 칼럼을 묶음
where절 이후에 사용
select avg(sal) from emp GROUP BY deptno;
3.HAVING : 그룹의 결과를 제한하는 함수
부서별로 그룹 후 , 부서별 평균급여가 2000 이상인 값만 출력 :
select deptno, avg(sal) from emp group by deptno having avg(sal) >= 2000;
5. 조인
1.Cross Joine - 아무런 의미없음
select * from emp, dept;
2.Equi Join - 일치하는 값 찾음
* EMP테이블의 부서번호와 DEPT 테이블의 부서명을 조인하라 :
select * from emp, dept where emp.deptno = dept.deptno;
* SCOTT의 부서명을 출력하라
select ename,dname,emp.deptno from emp, dept
where emp.deptno = dept.deptno ANd ename = 'SCOTT';
## 테이블 별칭지정 : 모든 select 컬럼에 별칭소속을 명시해줘야함
select e. ename,d.dname,e.deptno from emp e, dept d
where e.deptno = d.deptno AND e.ename = 'SCOTT';
1). 뉴욕에 근무하는 사원의 이름과 급여를 출력
select ename, sal from emp, dept
where emp.deptno = dept.deptno AND loc = 'NEW YORK';
2). ACCOUNTING 부서 소속 사원의 이름과 입사일을 출력
select ename, hiredate from emp,dept
where emp.deptno = dept.deptno AND dname = 'ACCOUNTING';
3). 직급이 MANAGER 인 사원의 이름 , 부서명을 출력
select ename, dname from emp,dept
where emp.deptno = dept.deptno AND job = 'MANAGER';
3.Non-Equi Join : 특정 벙위 내에 있는지를 조사
*급여와 급여등급을 참조하여 출력
select ename,sal,grade from emp, salgrade
where sal BETWEEN losal AND hisal;
4.Self Join : 테이블 자기 자신을 참조하여 조인
* self join 하여 매니저의 이름까지 출력
select employee.ename ,manager.ename from emp employee, emp manager
where employee.mgr = manager.empno;
1) 매니저가 KING인 사원들의 이름과 직급을 출력하시오.
select e1.ename, e1.job from emp e1, emp e2
where e1.mgr = e2.empno AND e2.ename = 'KING';
2) SCOTT과 동일한 부서에서 근무하는 사원의 이름을 출력
select e1.ename, e2.ename from emp e1, emp e2
where e1.deptno = e2.deptno
AND e1.ename = 'SCOTT’
AND e2.ename <> 'SCOTT';
5. Outer Join : 셀프조인시 널값도 처리
* 매니저 이름까지 출력시, KING의 정보까지도 출력
select employee.ename , manager.ename from emp employee, emp manager
where employee.mgr = manager.empno(+);
* 사원 테이블과 부서 테이블을 조인하여 사원명, 부서번호, 부서명을 출력
select e.ename, d.deptno, d.dname from emp e, dept d
where e.deptno(+) = d.deptno order by d.deptno;
6. ANSI Cross Join : 영어 구문에 맞게끔 풀어서 작성되는 새로운 코드작성
- Full outer join 을 제외하고는 내용 중복
select *
from emp inner join dept
on emp.deptno = dept.deptno; -> USING (deptno)
<예제 테이블 만들기>
create table dept02(
deptno number(2),
dname varchar2(14));
insert into dept02 values (10,'accounting');
insert into dept02 values (30,'sales');
select * from dept01, dept02
where dept01.deptno = dept02.deptno; : 양쪽 (+) 못붙임
------------------------------------------------
select * from dept01 full outer join dept02
using (deptno) order by deptno; : 양쪽누락정보 출력
6. 서브쿼리 : 하나의 select 문장의 절 안에 포함된 또 하나의 select 문장
1. 단일행 서브쿼리 : 서브쿼리의 수행결과 값이 오직하나의 로우(행)을 반환하는 쿼리
* SCOTT의 부서명을 알아내기 위한 서브쿼리문
select dname from dept where deptno =
(select deptno from emp where ename = 'SCOTT');
* 평균 급여보다 더 많은 급여를 받는 사원을 검색
select ename,sal from emp where sal >
(select avg(sal) from emp);
1. SCOTT과 같은 부서에서 근무하는 사원 이름과 부서 번호를 출력
select ename, deptno from emp where deptno =
(select deptno from emp where ename='SCOTT')
2. SCOTT과 동일한 직급을 가진 사원을 출력하
select * from emp where job =
(select job from emp where ename = 'SCOTT')
3. SCOTt과 급여가 동일하거나 더 많이 받는 사원명과 급여를 출력
select ename, sal from emp where sal >=
(select sal from emp where ename = 'SCOTT');
4. DALLAS에서 근무하는 사원의 이름, 부서번호를 출력
select ename, deptno from emp where deptno =
(select deptno from dept where loc = 'DALLAS');
5. SALES 부서에서 근무하는 모든 사원의 이름과 급여를 출력
select ename, sal from emp where deptno =
(select deptno from dept where dname = 'SALES');
6. 직속상관이 KING인 사원의 이름과 급여를 출력
select ename, sal from emp where mgr =
(select empno from emp where ename = 'KING');
2. 다중행 서브쿼리 : 결과가 2개 이상 구해지는 쿼리문
2-1. IN 연산자 : 서브쿼리 연산결과의 모든 값에 대한 결과출력
* 급여를 3000이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원
select ename, sal, deptno from emp where deptno IN
(select DISTINCT deptno from emp where sal >= 3000);
7. 부서별로 가장 급여를 많이 받는 사원의 정보
(사원번호, 사원이름, 급여, 부서번호)를 출력하시오.(IN)
select empno,ename,sal,deptno from emp where sal in
(select max(sal) from emp group by deptno);
8. 직급이 MANAGER인 사람의 속한 부서의 부서 번호와 부서명, 지역을 출력
select deptno,dname,loc from dept where deptno IN
(select deptno from emp where job = 'MANAGER');
2-2. ALL 연산자 : 서브쿼리의 검색결과와 모든 값이 일치 ( ex 최대값 )
* 30번 소속사원들 중에서 급여를 가장 많이 받는 사원보다
더 많은 급여를 받는 사람의 이름,급여를 출력
select ename,sal from emp where sal >
all (select sal from emp where deptno = 30);
9. 영업 사원들 보다 급여를 많이 받는 사원들의 이름과 급여와 직급을 출력하되,
영업사원은 출력X
select ename, sal, job from emp where sal >
all (select sal from emp where job ='SALESMAN');
2-3. ANY 연산자 : 서브쿼리의 검색 결과와 하나이상 일치하면 참 ( ex 최소값)
* 부서번호가 30번인 사원들의 급여 중 가장 작은 값보다
많은 급여를 받는 사원 이름,급여 출력
select ename,sal from emp where sal >
any(select sal from emp where deptno = 30);
10. 영업 사원들의 최소 급여보다 많이 받는 사원들의
이름과 급여와 직급을 출력하되, 영업사원 출력X
select ename, sal, job from emp where sal >
any(select sal from emp where job = 'SALESMAN') AND job <> 'SALESMAN';
11. SMITH와 동일한 직급을 가진 사원의 이름과 직급을 출력.(단일행 서브쿼리)
select ename,job from emp where job =
(select job from emp where ename = 'SMITH');
12. 직급이 'SALESMAN'인 사원이 받는 급여들의 최대 급여보다 많이 받는
사원들의 이름,급여를 출력하되, 부서번호가 20번인 사원은 제외한다.
(ALL연산자 사용)
select ename, sal from emp where sal >
all (select sal from emp where job = 'SALESMAN') AND deptno != 20;
13.직급이 'SALESMAN'인 사원이 받는 급여들의 최소 급여보다 많이 받는
사원들의 이름과 급여를 출력하되, 부서번호가 20번인 사원은 제외한다.
(ANY연산자 사용)
select ename, sal from emp where sal >
any (select sal from emp where job = 'SALESMAN') AND deptno != 20;
7. DDL – 테이블 자체 구조 생성,수정,삭제
1. CREATE TABLE - 테이블 정의
create table example( 컬럼명 데이터타입() ,...... )
create table emp01 (
empno number(4),
ename varchar(20),
sal number(7,2)
);
create table dept01 (
deptno number(2),
dname varchar2(14),
loc varchar(13)
);
1.1 서브쿼리로 테이블 생성하기 (테이블 복사)
create table 테이블명
as 서브쿼리
create table emp02 as select * from emp;
create table emp03 as select empno, ename from emp;
create table emp04 as select empno, ename, sal from emp;
1.2 (테이블 틀(컬럼명, 자료유형)만 복사)
create table dept02 as select * from dept where 1=0;
2.ALTER TABLE - 테이블 구조변경
2-1. ADD : 컬럼 추가
* DEPT02 테이블에 문자 타입의 부서장(DMGR) 칼럼을 추가해 봅시다.
alter table emp01
add(jop varchar2(9));
2-2. MODIFY : 컬럼 데이터타입 변경
* 직급(JOB) 칼럼을 최대 30글자까지 저장할 수 있게 변경해 보도록 하자.
alter table emp01
modify(JOB varchar2(30));
2-3. DROP : 컬럼 삭제
* EMP01 테이블의 직급 칼럼을 삭제해 보도록 합시다.
alter table emp01 drop column job;
2-4. SET UNUSED : 테이블 논리적으로 사용제한
alter table emp02 set unused(job);
3. DROP TABLE 테이블 구조 삭제
drop table emp02;
4. TRUCATE : 테이블 틀은 놔두고 로우만 삭제
truncate table emp02;
5. RENAME 테이블명 변경
rename old_name to new name;
rename table dept6 to dept7
8. DML – 테이블 내용 추가,수정,삭제
1. INSERT – 테이블 내용 추가
insert into dept01
(deptno, dname, loc)
values(10,'accounting','new york');
-모든 컬럼에 맞게 채워넣는경우, 컬럼이름 생략가능
insert into dept01
values(20,'accounting','korea');
*서브 쿼리문을 이용하여 다음과 같은 구조로 SAM01 테이블을 생성하시오.
create table SAM01
as select empno, ename, job, sal from emp where 1=0;
*SAM01 테이블에 다음과 같은 데이터를 추가하시오.
insert into SAM01
values ( 1030, 'BANANA','NURSE',15000);
1.1 .NULL
- NULL값 입력방법 : 암시적, 명시적
암시적 - 컬럼명 리스트에 컬럼을 생략 > 암시적으로 null값 할당
명시적 - values 리스트에 null을 명시적으로 입력
- desc로 검색 후, 컬럼에 not null 제약조건이 있는경우 null값 입력 불가능
- 암시적으로 null값의 삽입
*지역명이 결정되지 않은 30번 부서에 부서명만 입력
insert into dept01
(deptno, dname)
values (30,'sales');
- 명시적으로 null값의 삽입
insert into dept01
values (40,'operations',null);
- null 대신에 ''를 사용할 수 있음
insert into dept01
values (50, '','chicago');
1.2. 서브쿼리로 데이터 삽입
- INSERT 명령문에서 지정한 컬럼의 개수나 데이터 타입이
서브 쿼리를 수행한 결과와 동일해야 함
1) creat table dept02 as select * from dept where 1=0;
2) insert into dept02 select * from dept; - 한번에 여러개 행 등록!
*4.문제 1에서 생성한 SAM01 테이블에 서브 쿼리문을 사용하여
EMP 에 저장된 사원 중 10번 부서 소속 사원의 정보를 추가하시오.
insert into sam01
select empno, ename, job, sal from emp where deptno = 10;
1.3. 다중 테이블에 다중 행 입력하기
insert all
into emp_hir values (empno, ename, hiredate)
into emp_mgr values (empno, ename, mgr)
select empno, ename, hiredate, mgr from emp where deptno = 20;
- 하나의 테이블에 다중행 입력하기
insert all
into dept01 values(40,'총무부','서울')
into dept01 values(50,'인사부','부산')
select * from dual;
1.4 조건에 맞게 테이블값 삽입
insert all
when profno between 1000 and 1999 then
into p_01 values(profno,name)
when profno between 2000 and 2999 then
into p_02 values(profno,name)
select profno,name from professor;
2. UPDATE - 테이블 내용 수정
2.1 모든행 변경
* 모든 사원의 부서번호를 30번으로 수정합시다.
update emp01
set deptno = 30;
* 이번엔 모든 사원의 급여를 10% 인상시키는 UPDATE 문을 보겠습니다.
update emp01
set sal = sal*1.1;
* 모든 사원의 입사일을 오늘로 수정하려면 다음과 같이 합니다.
update emp01
set hiredate = sysdate;
2.2 특정행 변경
* 부서번호가 10번인 사원의 부서번호를 30번으로 수정합시다.
update emp01
set deptno = 30
where deptno = 10;
* 급여가 3000 이상인 사원만 급여를 10% 인상합시다.
update emp01
set sal = sal*1.1
where sal >= 3000;
*1987년에 입사한 사원의 입사일이 오늘로 수정합시다.
사원의 입사일을 오늘로 수정한 후에 테이블 내용을 살펴봅시다.
update emp01
set hiredate = sysdate
where substr(hiredate,1,2) = '87';
* SAM01 테이블에 저장된 사원 중 급여가 10000 이상인 사원들의 급여만
5000원씩 삭감하시오.
update sam01 set sal = sal-5000 where sal >= 10000;
2.3 2개이상 컬럼값 변경
* SCOTT 사원의 입사일자는 오늘로, 급여를 50 으로 커미션을 4000 으로 수정
update emp01
set hiredate = sysdate, sal = 50, comm = 4000
where ename = 'SCOTT';
2.4 서브쿼리로 수정하기
update dept02
set loc= (select loc from dept02 where deptno = 40)
where deptno = 20;
* 서브 쿼리문을 사용하여 EMP 테이블의 저장된 데이터의 특정 컬럼만으로
구성된 SAM02 테이블을 생성 후 DALLAS 에 위치한 부서 소속 사원들의
급여를 1000 인상
update sam02
set sal = sal+1000
where deptno = (select deptno from dept where loc = 'DALLAS');
2.5 서브쿼리를 이용해 두개 이상의 칼럼에 대한 값 변경
* 서브 쿼리를 이용해서 부서번호가 20인 부서의 부서명과 지역명을
부서 번호가 40번인 부서와 동일하게 변경하도록 해 봅시다.
update dept01
set (dname, loc) = (select dname, loc
from dept01
where deptno = 40)
where deptno = 20;
*서브 쿼리문을 사용하여 SAM02 테이블의 모든 사원의 급여와 입사일을
이름이 KING 인 사원의 급여와 입사일로 변경하시오.
update sam02
set (sal,hiredate) =
(select sal,hiredate from sam02 where ename = 'KING')
2.DELETE - 테이블의 행(로우) 삭제
* SAM01 테이블에서 직급이 정해지지 않은 사원을 삭제하시오.
delete from sam02
where job is null;
* 사원 테이블에서 부서명이 SALES인 사원을 모두 삭제해봅시다.
delete from emp01
where deptno = (select deptno from dept where dname = 'SALES');
* SAM02 테이블에서 RESEARCH 부서 소속 사원들만 삭제하시오.
delete from sam02
where deptno = (select deptno from dept where
dname = 'RESEARCH')
9. 트랜젝션 관리
1. COMMIT
- Transaction 작업 내용을 실제 DB에 저장
- 이전 데이터가 완전히 업데이트 됨
2. ROLLBACK
- Transaction 작업 내용을 취소
- 이전 commit 한 곳 까지만 복구
3. 자동 commit 혹은 ROLLBACK 되는경우
-sql plus가 정상 종료되었다면 자동으로 commit 되지만,
비정상적으로 종료되었을땐 자동으로 rollback
- ddl과 dcl 명령문이 수행된 경우 자동으로 commit (쿼리 잘못쳐도 commit)
4. SAVEPOINT : 트랜젝션 분할
savepoint c1 / save point c2;
rollback to c1 / rollback to c2
10. 무결성 제약조건
<emp와 dept의 제약조건을 살펴보자>
select table_name, constraint_name, constraint_type, r_constraint_name
from user_constraints
where table_name in('DEPT','EMP');
1. NOT NULL - null을 허용하지 않는다.
create table emp02 (
empno number(4) NOT NULL,
ename varchar2(10) NOT NULL,
job varchar2(9),
deptno number(2) );
2. UNIQUE - 중복된 값을 허용하지 않는다. 항상 유일한 값, 널 허용
create table emp03 (
empno number(4) UNIQUE,
ename varchar2(10) NOT NULL,
job varchar2(9),
deptno number(2) );
3. PRIMARY KEY - null을 허용하지 않고 중복된 값을 허용하지 않는다.
- not null과 unique를 합친 제약조건
create table emp03(
empno number(4) CONSTRAINTS emp03_empno_pk PRIMARY KEY,
ename varchar2(10) CONSTRAINTS emp03_ename_nn NOT NULL,
job varchar2(10),
deptno number(2) );
4. FOREIGN KEY : 참조되는 테이블의 칼럼의 값이 존재하면 안된다.
create table emp12 (
empno number(4) PRIMARY KEY,
varchar2(10) NOT NULL,
deptno number(2) REFERENCES dept(deptno)
);
5. CHECK : 저장 가능한 데이터의 값의 범위나 조건을 지정
create table emp13 (
empno number(4) PRIMARY KEY,
ename varchar2(10) NOT NULL,
deptno number(2) REFERENCES dept(deptno),
sal number(7,2) CHECK(sal BETWEEN 500 AND 5000),
gender varchar2(1) CHECK (gender IN('M','F'))
);
6. 제약조건명 사용자가 지정
테이블명_ 칼럼명 _제약조건 유형
EMP04 _ EMPNO _ UK
create table emp06 (
empno number(4) CONSTRAINT emp06_empno_pk PRIMARY KEY,
ename varchar2(10) CONSTRAINT emp06_ename_nn NOT NULL,
deptno number(2) CONSTRAINT emp06_deptno_fk
REFERENCES dept(deptno),
sal number(7,2) CONSTRAINT emp06_sal_ck
CHECK(sal BETWEEN 500 AND 5000),
gender varchar2(1) CONSTRAINT emp06_gender_ck
CHECK (gender IN('M','F')),
loc varchar2(13) DEFAULT '서울'
);
7. 복합키 설정
create table member01(
name varchar2(10),
address varchar2(30),
hphone varchar2(16),
CONSTRAINT member01_combo_pk PRIMARY KEY(name,hphone)
);
8. 제약조건 추가하기
alter table emp01
add CONSTRAINT emp_empno_pk PRIMARY KEY(empno);
alter table emp01
add CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno)
REFERENCES dept(deptno);
8-1 NOT NULL 추가하기
alter table emp01
modify ename CONSTRAINT emp01_ename_nn NOT NULL;
8-2 제약조건 제거하기
alter table emp01
drop constraint emp01_ename_nn;
11. 사용자관리
1. 사용자 생성
CREATE USER user01 identified by 1234;
2. 데이터베이스에 접속할 수 있는 권한
GRANT create session to user01;
3. 테이블 생성할 수 있는 권한
GRANT create table to user01;
4. 객체 권한부여
scott -> grant select on emp to user01;
user01 -> select * from scott.emp
12. 뷰(VIEW)
1. 뷰 만들기
권한부여 : grant create view to scott;
뷰만들기 : create view emp_view30
as
select empno, ename, deptno from emp_copy
where deptno = 30
2. 그룹연산자 이용해서 뷰 만들기
2.1 부서별 평균급여 뷰
create view view_sal
as
select deptno, sum(sal) 총급여, avg(sal) 평균급여
from emp_copy group by deptno;
2.2 부서별 최대,최소급여 뷰
create view MaxMin_emp
as
select d.dname 부서번호, max(e.sal) 최대급여, min(e.sal) 최소급여
from emp_copy e, dept_copy d
where e.deptno = d.deptno
group by d.dname;
3. 뷰 기능
3.1 CREATE OR REPLAC
- 존재하지 않는 않는 뷰일 경우 생성, 존재하는 뷰일 경우 변경
create or replace view emp_view30
as
select empno, ename, sal from emp_copy
where deptno = 30;
3.2 FORCE 옵션
- 기본 테이블 없이 뷰 생성, 일반적으로는 NOFORCE가 디폴트값
create or replace force view notable_view
as
select empno, ename, deptno
from employees
where deptno = 30;
3.3 WITH CHECK OPTION
- 조건 컬럼 값 변경 못하게 함
create or replace view emp_view30
as
select empno, ename, deptno
from emp_copy
where deptno = 30 with check option;
3.4 WITH READ ONLY
- 뷰를 통해 기본 테이블의 변경을 못하게 제한
create or replace view emp_view30
as
select empno, ename, deptno
from emp_copy
where deptno = 30 with read only;
3.5 TOP-N
- N명 까지만 구함
1) ROWNUM
create or replace view view_hire
as
select empno, ename, hiredate
from emp
order by hiredate;
select rownum,empno,ename,hiredate
from view_hire
where rownum <= 5;
2) 인라인 뷰
select rownum, empno, ename, hiredate
from (select empno, ename,hiredate
from emp order by hiredate)
where rownum <= 5;
13. 시퀸스
시퀸스 : 행 구분을 위한 자동번호생성기
생성 양식
create sequence sequence_name
start witn n
increment by n
시퀸스 생성
create sequence dept_deptno_seq
start with 10
increment by 10;
기본키에 시퀸스 접목
insert into emp01
values(emp_seq.nextval, 'julia',sysdate);