-
DDL문장 Data difinition language : create alter drop
create table, view, sequence, index , ,,,,
create table 테이블(
컬럼명 데이타형 [제약조건들], --쉼표로 컬럼 구분함 / [제약조건들] 0개 이상/
컬럼명 데이타형 [제약조건들],
컬럼명 데이타형 [제약조건들],
컬럼명 데이타형 [제약조건들] --마지막 컬럼 다음은 쉼표가 없다.
) ; -- 세미콜론 마감을 해야 실행 가능함
오라클 데이터형
char(n) : 고정문자열 n바이트 이내 : 1~2000 byte
varchar2(n) : 가변문자열 n바이트 이내 : 1~4000 byte , varchar(n)과 호환됨
nvarchar2(n) : 국가별 국가집합에 따른 크기와 문자 또는 바이트 1~4000 byte. 거의사용안함
number(p,s) : 전체자리수p 소숫점자리수 s
ex) number(5) : 정수 5자리 / number(7,2) 전체 7자리 소숫점2자리 수
cf) int 호환됨
date : 날짜
rowid : 테이블내부의 고유 행 주소 80bit = 10byte , 내부적으로 사용됨 유저가 사용할일이 거의없다.
참조) 내부 예약어 : rownum : 결과 행 번호
blob : 대용량 바이러리 데이터 저장 / ~4GB
clob : 대용량 텍스트 데이저 저장 / ~4BG
bfile : 대용량 바이러리 파일 저장 / ~4GB
timestamp(n) : date의 확장형태
interval year to month : 년과 월을 이용해서 기간을 저장
interval day to second : 일,시,분,초 를 이용해서 기간을 저장
create table sam02 (year01 interval year(3) to month); --3자리 년도 저장가능 즉 999년까지
insert into sam02 values (interval '36' month(3)); -- 3자리 개월, '36'개월을 입력한다 -- 3년입력됨
SQL> select * from sam02; --- 3년이 입력되어 있다.
YEAR01
-------------------
+003-00select year01, sysdate, sysdate + year01 from sam02;
YEAR01 SYSDATE SYSDATE+
---------------------- ----------- ----------------
+003-00 21/06/08 24/06/08
테이블 복제하기
기본형 : create table 새테이블 as select * / 컬럼선택가능 from 원본테이블;
emp를 복제하여 em02를 생성하시오
create table em02 as select * from emp;
구조만 복제하기
기본형 : create table 새테이블 as select * /컬럼선택가능 from 원본테이블 where 1=0;
create table em03 as select * from emp where 1=0;
테이블 구조 바꾸기 alter
1. emp를 복제하여 em05를 만드시오
create table em05 as select * from emp;
2. em05에 email vachar2(10) 컬럼을 추가하라
alter table em05 add (email varchar2(10));
3. em05에 email2 email3 컬럼을 동시에 추가하라
alter table em05 add (email2 varchar2(10), email3 varchar2(10));
4. em05에서 email의 크기를 40으로 변경하라
alter table em05 modify (email varchar2(40));
5. em05에서 email2 의 컬럼명을 phone으로 변경하고 크기를 char(15) 로 바꾸어라
alter table 테이블명 rename column 컬럼명 to 새컬럼명;
alter table em05 rename column email2 to phone;
alter table em05 modify (phone char(15));
6. em05의 테이블 이름을 emp05로 변경하라
alter table 테이블명 em05 rename to 새테이블명
alter table em05 rename to emp05;
컬럼사용중지
alter table 테이블면 set unused(컬럼명); -- 컬럼을 drop 하기전의 전초 작업
용도 : 컬럼을 제거하기 전에 사용하지못하게 만들어 둔다
주의) 일단 실행하면 되돌리지 못한다
7. emp05에서 email3를 사용하지 못하도록 설정하시오.
alter table emp05 set unused(email3);
8. 사용하지 못하는 컬럼을 삭제하시오.
alter table 테이블명 drop unused columns;
alter table emp05 drop unused columns;
컬럼삭제
alter table 테이블명 drop column 컬럼명;
9. emp05에서 phone컬럼을 삭제하시오
alter table emp05 drop column phone;
궁금증 ) 트랜잭션 안먹나? DML문장에서만 효과가 있다. ( DDL문장에서는 효과가 없다)DDL 문장은 auto commit문장이다.테이블제거
drop tale 테이블명
10. emp05를 삭제하라
drop table emp05;
모든 데이터 삭제하기
turncate table 테이블명;
1) 모든 데이터가 삭제된다. -- 삭제 속도가 빠르다.
2) 복구(transaction)지원안됨 참조) delete는 지원됨
3) 데이터 환경정보를 지워서 초기화 시킨다. -- 처음생성한형태로 초기화 시키는 경우
데이터 딕셔너리와 딕셔너리 뷰
데이터 딕셔너리 : 데이터베이스 자원을 효율적으로 관리하기 위해서 다양한 정보를 저장하는 시스템 테이블이다.
오라클 시스템 테이블
DBA_xxxx : 관리자가 접근하는 객체에 대한 정보조회
ALL_xxxx : 자신계정이나 권한을 부여받은 객체에 대한 정보조회
USER_xxxx : 해당 계정이 소유한 객체에 대한 정보 등을 제공
우리가 테이블을 수정하거나 권한을 부여하거나 제약을 추가하거나 삭제하거나 하면 시스템 테이블에서 확인해야 한다.
user_tables --유저가 소유한 테이블 정보
user_sequences -- 유저가 소유한 시퀀스정보
user_views -- 유저가 소유한 뷰 정보 - 쿼리축약, 보안강화 등을 목적으로함
user_indexs -- 유저가 소유한 인덱스 정보 - 빠른검색
show user;
desc user_tables;
DML : 데이터 조작어 / 트랜잭션 대상어
insert / update / delete
INSERT
1. 모든 컬럼 입력 -- 값의 입력순서가 테이블구조 순서와 같다.
insert into 테이블명 values (값,값,,,,) ; -- 컬럼의 나열순서와 값의 나열 순서가 같다
1) dept를 복제하여 dept01을 생성하시오
create table dept01 as select * from dept;
2) dept01 에서 부서번호 60, 부서명 '회계과' , 지역 '서울'을 입력하라.
insert into dept01 values ('60','회계과','서울');
2. 선택 컬럼 입력 : 컬럼의 나열순서와 값의 나열 순서가 같다.
컬럼의 갯수와 값의 갯수가 불일치하면 오류난다.
insert into 테이블명 (컬럼,컬럼,,,,,,) values (값,값,,,,,,,,);
3)dept01에 deptno, dname, loc에 70, 무역과, 인천을 컬럼선택방식으로 입력하라
insert into dept01 (deptno, dname, LOC) values ('70','무역과','인천');
4)dept01에 deptno, dname, loc에 80,총무과, 인천, 88 을 입력하고 오류를 확인하라.
insert into dept01 (deptno, dname, LOC) values ('80','총무과','인천');
1행에 오류:
ORA-00913: 값의 수가 너무 많습니다5) dept01에 deptno, dname, loc에 90, 비서실을 컬럼선택방식으로 입력하고 오류를 확인하라.
insert into dept01 (deptno, dname, LOC) values ('90','비서실');
1행에 오류:
ORA-00947: 값의 수가 충분하지 않습니다null을 입력하는 방법
1) null or ''
insert into dept01 (deptno, dname, LOC) values ('80','해외영업','');
insert into dept01 (deptno, dname, LOC) values ('90','생산부','null');
치환변수 &
SQL> select * from &table; <---------------------- 테이블자리 변수 설정
table의 값을 입력하십시오: dept <----------------- 값입력
구 1: select * from &table
신 1: select * from dept
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> / <---------------------------------------------- 재실행
table의 값을 입력하십시오: salgrade <------------- 값 재입력
구 1: select * from &table
신 1: select * from salgrade
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
서브쿼리를 이용한 행추가
소스테이블을 읽어서 타겟테이블에 입력함
기본형 : insert into 타겟 select * from 소스 ;
1. dept 구조를 복제하여 dept02를 생성하시오
create table dept02 as select * from dept where 0=1;
2.dept를 읽어서 dept02에 입력하시오.
insert into dept02 select * from dept;
다중테이블 다중로우 입력하기
기본형 : insert all into 타겟테이블명 values (값,,,,)
into 타겟테이블명 values (값,,,,)
into 타겟테이블명 values (값,,,,)
select 컬럼,,,,,,, from 소스 where 조건절 ;
1. emp에서 사번, 이름, 입사일자를 선택하여 emp_hir를 구조만 생성하고
create table emp_hir as select empno, ename, hiredate from emp where 0=1;
2. emp에서 사번, 이름, 매니저를 선택하여 emp_mgr을 구조만 생성하고
create table emp_mgr as select empno, ename, mgr from emp where 0=1;
3. emp로부터 부서번호가 20인 직원의 사번, 이름, 입사일자 , 매니저를 선택하여
사번, 이름, 입사일자를 선택하여 emp_hir에 입력하고
사번, 이름, 매니저를 선택하여 emp_mgr에 입력하라.
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;
SQL> select * from emp_hir;
EMPNO ENAME HIREDATE
---------- -------------------- --------
7369 SMITH 80/12/17
7566 JONES 81/04/02
7902 FORD 81/12/03
SQL> select * from emp_mgr;
EMPNO ENAME MGR
---------- -------------------- ----------
7369 SMITH 7902
7566 JONES 7839
7902 FORD 7566
다중테이블 다중로우 입력하기 + 조건
insert all when 조건 then into 타겟테이블명 values (값,,,,,,)
when 조건 then into 타겟테이블명 values (값,,,,,,)
when 조건 then into 타겟테이블명 values(값,,,,,,)
select 컬럼,,,, from 소스
1) emp에서 사번, 이름, 입사일자를 선택하여 emp_hir02를 구조만 생성하고
create emp_hir02 as select empno, ename, hiredate from emp where 0=1;
2) emp에서 사번, 이름, 급여를 선택하여 emp_sal을 구조만 생성하고
create emp_sal as select empno, ename, sal from emp where 0=1;
3) emp에서 사번, 이름, 입사일자, 급여 를 선택하여 입사일자가 82년 이후는 emp_hire02에 입력하고 급여가 2000이상이면 emp_sal에 입력하시오.
insert all when hiredate >= '82/01/01' then into emp_hir02 values(empno, ename, hiredate)
when sal>=2000 then into emp_sal values(empno, ename, hiredate)
select empno, ename, hiredate, sal from emp;
SQL> select * from emp_hir02;
EMPNO ENAME HIREDATE
---------- -------------------- --------
7934 MILLER 82/01/23SQL> select * from emp_sal;
EMPNO ENAME SAL
---------- -------------------- ----------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7839 KING 5000
7902 FORD 3000
다중테이블 다중로우 입력하기 + 피봇팅
피봇팅 : 행데이터를 열데이터로, 열데이터를 행데이터로 회전시켜서 표현하는것
------------------------------------- 판매 요일 테이블 생성
create table sales( sales_id number(4), -- 판매자번호
week_id number(4), -- 주차
mon_sales number(8,2),
tue_sales number(8,2),
wed_sales number(8,2),
thu_sales number(8,2),
fri_sales number(8,2)
);
insert into sales values(1001,1,200,100,300,400,500);
insert into sales values(1002,2,100,300,200,500,350);
SQL> select * from sales;
SALES_ID WEEK_ID MON_SALES TUE_SALES WED_SALES THU_SALES FRI_SALES
---------- ---------- ---------- ---------- ---------- ---------- ----------
1001 1 200 100 300 400 500
1002 2 100 300 200 500 350-------------------------------------- 판매 일일 데이터
create table sales_data(
sales_id number(4), --판매자번호
week_id number(4), --주차
daily_number(4),--일일판매
sales number(8,2)
);
------------------------------------------ 다중테이블 다중행 입력
insert all into sales_data values (sales_id, week_id, 1, mon_sales)
into sales_data values (sales_id, week_id, 2, tue_sales)
into sales_data values (sales_id, week_id, 3, wed_sales)
into sales_data values (sales_id, week_id, 4, thu_sales)
into sales_data values (sales_id, week_id, 5, fri_sales)
select sales_id, week_id, mon_sales,tue_sales,wed_sales,thu_sales,fri_sales from sales;
------------------------------------------- 확인
select * from sales_data;
SALES_ID WEEK_ID DAILY_ID SALES
---------- ---------- ---------- ----------
1001 1 1 200
1002 2 1 100
1001 1 2 100
1002 2 2 300
1001 1 3 300
1002 2 3 200
1001 1 4 400
1002 2 4 500
1001 1 5 500
1002 2 5 350
UPDATE
기본형 : update 테이블명 set 컬럼 = 새값, 컬럼 = 새값, 컬럼 = 새값, 컬럼 = 새값
where 조건절 ;
1. dept를 복제하여서 dept02를 만드시오.
create table dept02 select * from dept;
2. 확인
select * from dept02;
3. dept에서 부서번호가 10인 지역을 '서울'로 바꾸시오
update dept02 set LOC ='서울' where deptno =10;
4.확인
select * from dept02;
--------------------------------------
1.emp의 사번 이름 급여를 복제하여 em06을 생성하자
create table em06 as select * from emp;
2. em06의 급여를 10% 인상하라.
update em06 set sal = sal*1.10;
3.확인
select ename, sal from em06;
------------------------------------서브쿼리를 이용한 수정문
기본문형 : update 타겟테이블 set (컬럼,컬럼,,) = (select 컬럼,컬럼,, from 소스테이블 where 조건절)
1. dept를 복제하여 dept03을 만드시오.
create table dept03 as select * from dept;
2. dept03에서 부서번호가 40인 부서를 선택하여
dept03의 부서번호가 20인 부서명과 지역을 수정하시오
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
update dept03 set (dname, loc) = (select dname, loc from dept03 where deptno = 40)
where deptno = 20;
3. 확인
select * from dept03;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 OPERATIONS BOSTON
30 SALES CHICAGO
40 OPERATIONS BOSTON
DELETE
기본형 : delete from 테이블명 where 조건절 ;
1) dept03 테블에서 부서번호가 30인 레코드를 삭제하시오
delete from dept03 where deptno=30;
2) 확인
select * from dept03;
3) emp를 복제하여 em07을 생성하고
create table em07 as select * from emp;
4) 부서명이 sales인 부서를 em07에서 삭제하시오 -- 서브쿼리
delete from em07 where deptno in (select deptno from dept where dname='SALES')
서브쿼리는 update문, delete문에서도 자주 사용된다.
MERGE문 병합문
기본문형 : merge into 타겟테이블 using 소스테이블 on (조인절)
when matched then 수정
when not matched then 입력
1) emp를 복제하여 em08을 만들자
create table em08 as select * from emp;
2) emp에서 직책이 manager인 데이터를 선택하여 em09를 만들자
create table em09 as select * from emp where job='MANAGER';
3) em09에서 직책이 manager인 데이터의 직책을 TEST로 변경하라.
update em09 set job='TEST' where job='MANAGER';
4) em09에 (8000,'SYJ','TOP',7566,'2005/01/02',1200,10,20)을 입력하라.
insert into em09 values (8000,'SYJ','TOP',7566,'2005/01/02',1200,10,20);
5) 확인
selec * from em09;
------------------------------병합
em09에서 데이터터를 선택하여 사번이 일치하면 em08의 데이터를 수정하고(동일컬럼 수정),일치하지 않으면 입력하시오.
merge into em08 t using em09 s on (t.empno=s.empno)
when matched then update set t.ename = s.ename, t.job = s.job,t.mgr = s.mgr, t.hiredate = s.hiredate,
t.sal = s.sal,t.comm = s.comm, t.deptno = s.deptno
when not matched then insert values(s.empno,s.ename,s.job,s.mgr,s.hiredate,s.sal,s.comm,s.deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES TEST 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE TEST 7839 81/05/01 2850 30
7782 CLARK TEST 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
8000 SYJ TOP 7566 05/01/02 1200 10 20
트랜잭션 transaction
트랜잭션이란 데이터 처리의 단위다. 오라클에서 실행되는 sql 명령어들을 하나의 논리적인 작업단위로 처리하는데 이것을 트랜잭션이라고 한다.
all - or - nothing 방식으로 동작하고 트랜잭션 전체단위에서 오류가 발생하지 않으면 완료처리하고, 하나라도 오류가 발생하면 전체를 취소한다.
TCL 문장
DCL ( grant, revoke ) TCL ( commit, rollback, savepoint )
commit -- 전체단위에서 오류가 발생하지 않으면 완료처리하고
rollback -- 하나라도 오류가 발생하면 전체를 취소
savepoint -- 지점저장
동작과정
--------- insert --------------> update --------------> delete ----------->
commit savepoint A savepoint B commit
<------------------ rollback to B
<------------------------------------------- rollback to A
<---------------------------------------------------------------- rollback
연습
1. 커밋한다.
commit;
2. dept01에 데이터가 있는지 확인한다.
select * from dept01;
3. dept01 의 데이터를 모두 삭제한다.
delete from dept01;
4. 확인한다.
select * from dept01;
5. 롤백한다.
rollback;
6. 확인한다.
select * from dept01;
7. dept01 에서 부서번호가 20인 레코드를 삭제한다.
delete from dept01 where deptno=20;
8. 확인
select * from dept01;
9. commit 한다
commit;
10. rollback한다
rollback;
11. 확인
select * from dept01;
자동커밋 문장 auto commit
일반적으로 create, drop, rename, truncate 등등의 문장은 실행하면 곧바로 auto commit 되며 rollback이 불가능하다.
savepoint 실습
1. 커밋한다
commit;
2. dept 복제, dept 05생성
create table dept05 as select * from dept;
3. 확인
select * from dept05;
4. 부서번호 20데이터 삭제
delete from dept05 where deptno=20;
5. 확인
select * from dept05;
6. savepoint A 설정
savepoint A;
7. 부서번호 10 데이터 삭제
delete from dept05 where deptno=10;
8. 확인
select * from dept05;
9. savepoint B 설정;
savepoint B;
10. 부서번호 30 데이터 삭제
delete from dept05 where deptno=30;
11. 확인
select * from dept05;
12. B까지 복구
rollback to B
13. 확인
select * from dept05;
14. A까지 복구
rollback to A;
15. 확인
select * from dept05;
16. 처음까지 복구
rollback;
17. 확인
select * from dept05;
'DataBase(Sol)(정리대기중..언젠가) > Oracle' 카테고리의 다른 글
210610_1(데이터베이스6 뷰VIEW, 시퀀스SEQUENCE, 인덱스 INDEX) (0) 2021.06.10 210609_1(데이터베이스5, 락&데드락, 제약조건) (0) 2021.06.09 210607_1(데이터베이스3, 그룹함수, 조인, 서브쿼리) (0) 2021.06.07 210604_1(데이터베이스2 sql문 order by, 내장함수(문자,숫자)) (0) 2021.06.04 210603_1(데이터베이스1 오라클서버/ 클라이언트 이론/ 데이터베이스이론) (0) 2021.06.03