[펌] 3. SQL

화복동문( 禍福同門 ) | ??
http://blog.naver.com/fantanstic/60013598878

SQL의 3가지  default계정

        sys/change_on_install           -->    DD의 소유자    -->DD(Data Dictionnary)    

              -->system tables핵심데이타(Meta data)

        system/manager         -->    DD외 system tables의 소유자

        scott(교육용계정)/tiger

        sys/change_on_install as sysdba  --> 계정/비밀번호 , DD의 권한으로써 입장합니다.

        = internal (보안으로인해 9i부터 없어짐)    

DBA(권한이 있는..) = sys, system = 계정

===========================================================================

SQL  5종류!!!

Select(질의語) --> Read,DB에 저장안됨!

DDL(Data Definition Language) --> table, structure 작업하는거.

        create

        alter(수정)

        drop

        rename

        ...

DML(Data Manipulation Language) --> data조작어(컬럼수정,삭제,갱신...)   -->>TCL줌

        insert(입력.삽입)

        update(수정)

        delete(삭제)

                -취소대비-

                before img

                snap shot img

                -자물쇠-

                Lock

                        ex) commit ~~ commit

                              commit ~~ rollback ~~ commit

                        Transaction(논리적으로 취급되는 일의 1단위)

                        1.n개의 DML로 이루어짐

                        2.1개의 DDL

                        3.1개의 DCL

TCL(Transation Control Language)

        commit(승인)

        rollback(돌아감) 
        savepoint(오라클에서만 책갈피역할) ex)rollback to 세이브지정명

DCL(Data Control Language)

        grant(권한부여)

        revoke(제거)

 

===========================================================================

 

Single ampersand < & >

sql문장내에서 사용자로부터 입력을 받고자 하는 값의 이름 앞에 '&'를 붙여서 사용한다.

Double ampersand < && >

&와 동일, 차이는 입력받은값을 해당변수에 저장하여 사용한다.

Define(변수지정) 과 UnDefine(등록해제)

변수를 정의하고 해당변수에 값을 할당할 수 있다
ex) define departmen_name = 'accounting'

define 엔터 : 사용된 변수들 확인할수 있다.

Accept

accept 명령어는 변수를 정의하면서 입력을 받을때 관련 메시지를 출력하도록 한다.

accept 변수명 prompt '출력메시지'

# 오라클 실행되는동안만 기억! 재시작하면 사라짐!!

 

===========================================================================

 

show all (모든 환경변수확인)

set 명령확인!

show 환경변수명

오라클 실행과 동시에 명령어 실행될수 있도록.

C:\oracle\ora81\sqlplus\admin\glogin열기!

이쪽에 입력!!

-- For backward compatibility

set pagesize 14

  

set lineszie : 한줄길이지정

set pagesize : 한페이지 행길이!?? ex) 30행으로 한페이지

set sqlprefix "!" 이거는 # --> ! 로 변경!

 

---------------------------------------------------------------------------

명령어입력중 컬럼 기억안날때..

#desc 테이블명

SQL> select empno, ename

  2  #desc emp

 이름                                                  널?      유형

 ----------------------------------------------------- -------- ------------------------------------

 EMPNO                                                 NOT NULL NUMBER(4)

 ENAME                                                          VARCHAR2(10)

 JOB                                                            VARCHAR2(9)

 MGR                                                            NUMBER(4)

 HIREDATE                                                       DATE

 SAL                                                            NUMBER(7,2)

 COMM                                                           NUMBER(7,2)

 DEPTNO                                                         NUMBER(2)

 

  2  from emp;

 

 

INSERT 문

insert into 테이블명 [컬럼명, ...]
values (값, ...);

널값처리!

 insert into dept(deptno,loc) // 컬럼명 지정입력후 값 지정!

 values (70,'D');

 insert into dept

 values (60,'C','NULL'); // NULL사용
다른 테이블의 데이터 입력

insert into test

select *

from dept;

 

UPDATE 문
update 테이블명

set 컬럼 명 = 변경할 값...

where 조건;

 

update dept

set deptno=80

where deptno 50; 

<서브퀘리 가능>

update emp

set sal = (select max(sal) from emp where deptno = 30)

where deptno = (select min(deptno) from dept);

<두개 이상의 컬럼에 대한변경 : 반드시 서브쿼리를 사용하여야한다.

서브쿼리 사용안하고 직접 값을 입력하면 오류발생!!update...set 식은 부속질의이어야...>
update emp

set (ename, sal) = (select ename, sal

from emp

where empno = 7698)

where empno = 7499;

 

DELETE 문

delete 테이블명

where 조건;

 

===========================================================================

Transaction의 시작과 종료

˚ commit 문이나 rollback문을 만났을 때

 

˚ DDL이나 DCL 중 한 문장이 실행되었을때 자동 commit

˚ 정상종료(exit명령어사용) 되었을때 자동 commit

˚ 비정상종료 또는 다운되었을때 자동 rollback

 # 오라클 시작과 동시에 transaction 시작! commit하면 그다음부터 다시 시작!!

  

lock : transaction간 상호파괴적인 행위 막기위한 관리 매카니즘

             auto, 최저level(DML row -> 동일한 row에만 lock걸림), 무한대기

             << dead lock  >>

             [ 명령문 level에 rollback (lock상황에서 마지막명령하나만 취소시킴) ]

                           DML수행전, 암시적savepoint한다.

 

ex)

A사용자 1번째

SQL> update emp

  2  set sal = 100

  3  where ename = 'SCOTT';

 

1 행이 갱신되었습니다.

 

A사용자 3번째

SQL> update emp

  2  set sal = 300

  3  where ename = 'SMITH';

update emp

       *

1행에 오류:

ORA-00060: 자원 대기중 교착상태가 검출되었습니다

<< 데드락 상황 >>

 

B 사용자 2번째

SQL> update emp

  2  set sal = 200

  3  where ename = 'SMITH';

 

1 행이 갱신되었습니다.

 

B사용자 4번째

SQL> update emp

  2  set sal = 400

  3  where ename = 'SCOTT';

<< lock상태 >>

 

 A사용자 마지막 명령하나만 취소시킴, deadlock해제!!

(commit : transaction 반영, t종료, lock해제 ,rollback : transaction 취소, t종료, lock해제)

 

===========================================================================

 

오라클의 테이블( 데이터 딕셔너리 data dictionary)

USER_ : 내가(해당사용자) 소유하고 있는 데이터 딕셔너리를 말한다.

ALL_ : 내가(해당사용자) 접근 가능한 모든 데이터 딕셔너리를 말한다.

DBA_ : DBA의 권한을 갖지고 있는 사용자에게만 허락되는 데이터 딕셔너리다.

V$_ : 서버의 성능과 locking정보...동적성능(메모리사용량..접근가능사용자...) DBA권한임!

 

CREATE TABLE

create table 테이블명 ( 컬럼명1 데이터타입,

                                    컬럼명2, 데이터타입....)

# table명과 컬럼명은 a~z, A~Z, 0~9숫자, 1~30(길이), 첫글자는 반드시 영문, _,$ 사용가능

# 데이터 타입 :

          varchar2(이름,게시판,주소.....) -가변길이..

          char(학번,과목번호,자리수고정인거) : 고정길이

          number(m,n) : 최대자릿수 와 소수점 자릿수 지정

          date : 날짜형의 데이터를 저장

 

서브쿼리를 사용한 테이블생성 (테이블 복사)

。컬럼이름, datatype(size), 데이터까지 복사함!!

문제> /* copy_emp1 == 구조 emp 동일, row(x) * ???/

SQL> create table copy_emp1

  2  as

  3  select * from emp

  4  where 0 =1;

 

테이블이 생성되었습니다.

 

SQL> select * from copy_emp1;

 선택된 레코드가 없습니다.

 

문제에 추가>

 # insert(새로삽입) & update(존재하는거 수정) 주의!!

// 삽입값 카피해 오기!!

SQL> insert into copy_emp1

  2  select * from emp;

 

14 개의 행이 만들어졌습니다.

 

새로운 컬럼 삽입 ( 테이블구조 더하기! )

ALTER TABLE 테이블명

ADD (컬럼명 데이터타입,

         컬럼명, 데이터 타입, ....)

SQL> alter table copy_emp

  2  add (MPN varchar2(10));

테이블이 변경되었습니다.

 

컬럼 변경 (테이블구조 수정하기 : 기존의 컬럼의 데이터 타입, 사이즈 등을 변경시킨다)

ALTER TABLE 테이블명

MODIFY (컬럼명 데이터타입,

              컬럼명 데이터타입, ....)

SQL> alter table copy_emp

  2  modify (MPN varchar2(11));

테이블이 변경되었습니다.

 

컬럼삭제 (테이블컬럼삭제)

ALTER TABLE 테이블명

DROP COLUMN 컬럼명 ;

alter table copy_emp

drop column mpn

 

테이블 이름 변경하기!

RENAME 기존 테이블명 TO 새로운 테이블명;

SQL> rename copy_emp1 to cemp;

테이블명이 바뀌었습니다.

 

테이블 삭제

TRUNCATE 는 DDL 이고 WHERE절 없음.

DELETE는 DML(ROLLBACK 됨!) 이고 WHERE절 있음(조건삭제가능!)

truncate : 해당테이블의 모든 롤을 삭제 == delete cemp;

SQL> truncate table cemp;

테이블이 잘렸습니다.

 

 

문제> DB가 큰회사인 경우 delete로 삭제할때 문제생길때.....방법....,10번테이블만 남겨야 한다!

10번테이블만 따로만들고, 삭제!!

1. create table temp1

   as

   select *

   from emp

   where deptno = 10;

2. truncate table emp;

3. insert into emp

   select * from temp1;

4. drop table temp1;

 

테이블 혹은 컬럼에 주석달기!

COMMENT ON TABLE 테이블명

IS '사용자가 입력하는 주석은 여기에...';

주석은 데이터딕셔너리에 저장! ( ~_col_comments, ~_tab_comments)

 

===========================================================================

 

CONSTRAINTS (제약조건)

not null : 널값이 삽입되지 못하도록 한다

unique : 같은값이 중복되지 못하도록 한다, 단 n개의 null이 허용됨!!

primary key : not null + unique

foreign key

check : 명시된 조건에 비교 검사하여 허용되는 값만 입력한다.

 

제약조건생성 : constranit 제약조건명(테이블_컬럼_제약조건) 제약조건타입 컬럼명

제약조건의 이름을 명시안하면 자동으로 지정한다. SYS_Cn 행태로...

 

SQL> ed

file afiedt.buf(이)가 기록되었습니다

 

  1  create table men

  2  ( id varchar2(10) constraint men_id_pk primary key,

  3  pwd varchar2(10) constraint men_pwd_nn not null,

  4  name varchar2(10),

  5  regno number(4) constraint men_regno_nn not null,

  6  point number(4) constraint men_point_nn not null,

  7  rec_id varchar2(10),

  8  constraint men_regno_un unique(regno),

  9  constraint men_point_ck check(point between 0 and 5000),

 10* constraint men_rec_id_fk foreign key (rec_id) references men(id))

SQL> /

 

테이블이 생성되었습니다.

 

1. 테이블 생성때 제약조건 주기!!!

- 컬럼레벨 : 하나의 컬럼에 하나의 제약조건만을 설정할수 있다, 모든 타입의 제약조건 설정이 가능하다.  not null은 무조건 컬럼레벨에서

- 테이블레벨 : 두 개이상의 컬럼에 대하여 제약조건을 설정할 수있다. 단, not null 제약조건은 제외된다

 

# primary key(컬럼,컬럼) 키가 둘개이상일때..

  ex) 1  create table dept4

        2  (deptno number(2),

        3  dname varchar2(10),

        4  loc varchar2(10),

        5  primary key (deptno, dname));

 

# constraint 제약조건명 foreign key (컬럼명) references 테이블명(컬럼명)

'references 테이블명(컬럼명)' 은 pk이어야 한다, 또 데이터 타입일치해야한다!!

 

# 데이터 딕셔너리에서 확인할때 (참고 : not null = c, check = c, unique = u)

SQL> desc user_constraints

SQL>select constraint_name, constraint_type, table_name, search_condition

from user_constraints

SQL> desc user_cons_columns

 

tip> 컬럼 길이 사이즈 조절!

SQL> col search_condition format a10

 

2. 테이블 생성후 제약조건주기

제약조건의 추가

alter table 테이블명

add constraint 제약조건명 제약조건타입 (컬럼명);

 

SQL> alter table dept1

  2  add constraint dept1_loc_uk unique(loc);

테이블이 변경되었습니다.

단, not null 제약조건은 alter table ...modify구문을 사용한다. 기존의 컬럼을 not null제약조건을 설정한 것으로 변경시키는 것이다. 컬럼레벨에서만 설정된다는 것과 연관!!!

 

제약조건의 삭제

alter table 테이블명

drop constraint 제약조건명;

 

===========================================================================

 

오라클 오브젝트

table : 실제 테이터를 저장하는 기본 오브젝트

view : 하나 이상의 테이블로부터 만들어진 논리적인 테이터의 집합

sequence : 기본키의 값을 생성하는 오브젝트

index : 질의문의 성능을 향상시키는 오브젝트

synonym : 다른 오브젝트의 동의어

 

VIEW 의 개념.

<< 물리적 table에 근거한 논리적 가상 table >>

뷰에는 실제 데이터가 저장되지 않는다. 뷰를 생성시키면 뷰를 생성한 select문장이 user_views라는 데이터 딕셔너리에 저장된다. 실제데이터가 아닌 select문이 해당뷰의 이름과 함께 데이터 딕셔너리에 저장되는 것이다. 즉 뷰를 조회하게 되면 뷰에 있는 데이터를 조회하는 것이 아니라 데이터 딕셔너리에 저장되어 있는 select문을 수행시켜 결과를 보는것이다.

view의 사용은 데이터접근을 제한(보안view), query문 간결화, 여러뷰 생성..)

 

VIEW 의 생성

create [or replace] [force | noforce(디폴트값)] view 뷰이름

as

서브쿼리(단, 서브쿼리에는 order by절이 포함될수 없다)

[with check option 제약조건명]

[with read only];

 

뷰의 재생성 create or replace view empvu20....

뷰의 삭제 drop view empvu20;

뷰를 통한 데이터 조작 : 실제 데이터 조작이 발생하는곳은 베이스테이블이다.

뷰를 통해 DML문 사용의 몇가지 제한!!

      그룹함수, group by절, distinct, rownum 이 뷰에 포함되어있을때는 데이터 삭제가

      불가능하다. (= DML이 안되는 경우..)

 

ex)

뷰 내용보기!!

SQL> select view_name, text

  2  from user_views;

 

VIEW_NAME                      TEXT

------------------------------ --------------------------------------------------

EMPVW30                        select empno, ename, sal, deptno

                               from emp

                               where deptno = 10

 

 

TOP-N 분석

select rownum, 컬럼명 ..

from (select 컬럼명 ..

          from 테이블명

          order by top-n-컬럼)

where rownum <= n;

 

문제>

sal상위 세명출력하기!!

SQL> select rownum, ename, sal

         from (select ename, sal

                  from emp

                  order by sal desc) t2

         where rownum < 4

 

    ROWNUM ENAME             SAL

---------- ---------- ----------

         1 KING             5000

         2 SCOTT            3000

         3 FORD             3000

 

 

 

 

SEQUENCE(시퀀스)

create sequence 시퀀스 명

increment by n

start with n

maxvalue n (nomaxvalue)

minvalue n (nominvalue)

cycle ( nocycle)

cache n (nocache) ;

 

ex)

 

SQL> create sequence dept_deptno

  2  start with 1

  3  increment by 1

  4  maxvalue 100

  5  nocycle

  6  nocache; 

주문번호가 생성되었습니다.

 

 

SQL> select dept_deptno.nextval

  2  from emp; <번호보임: nextval(다음값), currval(현재값)>

 

 

alter sequence dept_deptno

cycle;

수정할때 start with (x) 안됌

 

SQL> select * from user_sequences; 

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

------------------------------ ---------- ---------- ------------ - - ---------- -----------

DEPT_DEPTNO                             1        100            1 N N          0         101