오라클 강의자료 2011.09.15 (쌍용교육센터)

 
--2011.09.15

 1. 오라클의 데이터형
     문자형
        1)char(숫자) : 정적 char(10) 'aaa' ==> 공간 10 : 고정
            1.size크기가 고정길이 문자데이터형
            2.최대크기 : 2000바이트
            3.최소크기 : 1바이트
        2)varchar2(숫자) : 동적(가변) varchar2(10) ==> 'aaa' 공간3
           1.size크기가 가변길이 문자데이터형
           2.최대크기 : 4000바이트
           3.최소크기 : 1바이트
        3) 4000바이트 이상일 경우 : 회사소개, 이력서, HTML
            CLOB : 4GB(문자열) : 문자 저장
            BLOB : 4GB(바이트로 저장) : 그림, 동영상
     숫자
          NUMBER(숫자(정밀도), 숫자(스케일)) : 디폴트 : 7 ,NUMBER
              1~38 - 84~127
     날짜
          DATE (날짜형식) : 11/09/15 ==> TO_CHAR()
          TIMESTAMP : DATE 데이터형을 확장
     파일 저장
          BFILE : 4GB
 2. DDL (정의 언어)
    1)CREATE (table, sequence, view, index, procedure)
    2)ALTER : 수정, 추가, 삭제 ==> column단위
    3)DROP : table, sequence, view, index, procedure삭제
 
 3. 형식
    CREATE table table명(필드명 데이터형 제약조건...):ArrayList
    -- table 명은 중복없이 사용
    ALTER table table명 ADD 필드명 데이터형(추가)
    ALTER table table명 MODIFY 필드명 데이터형(수정)
    ALTER table table명 DROP COLUMN 필드명(삭제)
    DROP table table명 

 이름 name varchar2(10)
 나이 age number(3)
 생일 birth date

 --게시판--
 --회원가입--(숙제) table
 --공지사항--
 --주소록--

테이블 설계
  게시판
  1)게시물 번호 NUMBER (자동증가) ==> SEQUENCE
  2)제목  VARCHAR2(1000) *
  3)이름  VARCHAR2(128) *
  4)날짜  DATE  *
  5)조회수 NUMBER
  6)이메일 VARCHAR2(50)
  7)비번  VARCHAR2(8) *
  8)내용  VARCHAR2(4000) * NOT NULL
  9)성별  CHAR(4) check('남자','여자')

  테이블 구조 확인
  DESC table명
  만들어져 있는 테이블 확인
  SELECT * FROM tab;

게시판 저장 장소(table)
    테이블 삭제
       DROP table board;
    테이블 생성
       CREATE table board(no NUMBER,
                                     name VARCHAR2(128),
                                     email varchar2(50), 
                                     subject VARCHAR2(1000), 
                                     content VARCHAR2(4000),
                                     pwd VARCHAR2(8), 
                                     writeday DATE, 
                                     hit NUMBER);
    컬럼 추가
       ALTER table board ADD homepage VARCHAR2(50);
    컬럼삭제
       ALTER table board DROP COLUMN hit;
       ALTER table board ADD readnum VARCHAR2(20);
    컬럼 수정
       ALTER table board MODIFY readnum NUMBER;
       DESC board;

테이블 생성 방법
    CREATE table emp2(empno, ename, hiredate, sal, deptno)
             AS select empno, ename, hiredate, sal, deptno from emp;

테이블 생성시에 각 필드에 조건(제약조건) ==> 데이터형 뒤에 붙는다
     name VARCHAR2(10) NOT NULL
       1)NOT NULL : 해당 필드에 값은 NULL을 가질수 없다(반드시 입력)
       2)기본키 : 중복 허용을 안할 경우 PRIMARY KEY
            ==> UNIQUE + NOT NULL
       3)참조키(외래키) : 다른 테이블의 필드값을 참조
       4)DEFAULT : 이미 지정되어 있는 값(국적, 오늘 날짜)
       5)CHECK:값을 입력할떄 지정되어 있는 값만 설정(부서명, 직위, 성별)
사원 관리
    1. 사번 NUMBER PRIMARY KEY
    2. 이름 VARCHAR2 NOT NULL
    3. 부서 VARCHAR2 CHECK('영업','기획','개발','신입') DEFAULT '신입'
    4. 직위 VARCHAR2 CHECK('사장','부장','과장','대리','사원') NOT NULL
    5. 입사일 DATE DEFAULT SYSDATE
    6. 근무지 VARCHAR2 CHECK('서울','부산','제주')
    7. 연봉 NUMBER(14)

사원 관리 테이블
  CREATE table sawon(sabun NUMBER PRIMARY KEY,
                                         name VARCHAR2(128) NOT NULL,
                                         dept VARCHAR2(4) NOT NULL
                                         CHECK(dept IN('영업','기획','개발','신입')),
                                         job VARCHAR2(4) NOT NULL
                                         CHECK(job IN('사장','부장','과장')),
                                         regdate DATE DEFAULT SYSDATE,
                                         loc VARCHAR2(4) CHECK(loc IN('서울','부산','제주')),
                                         pay NUMBER(13));
데이터 첨부
   INSERT INTO sawon VALUES(7000, '홍길동','기획','과장','10/02/01','서울',3000);
   INSERT INTO sawon(sabun,name,dept,job,loc,pay) VALUES(7001,'심청이','개발','부장','부산',4500);
   INSERT INTO sawon VALUES(7002, '춘향이','신입','부장','09/02/01','부산',4500);

추가 sex(1,2,3,4)
   ALTER table sawon ADD sex NUMBER(1) CHECK(sex IN(1,2,3,4));
   ALTER TABLE sawon MODIFY name NOT NULL;

테이블명 변경
    RENAME sawon TO company;

회원 테이블(member)

      컬럼명 데이터타입   FK         PK       제약조건
      id        VARCHAR2  0
      name   VARCHAR2                          NOT NULL
      pwd     VARCHAR2                          NOT NULL
      sex      NUMBER                            CHECK(1,2,3,4)
      addr     VARCHAR2
      phone   VARCHAR2
      regdate DATE                                 DEFAULT
      content VARCHAR2

회원 테이블(web_member)

       컬럼명                  데이터타입           FK(외래키) PK(기본키) 제약조건
       name                   VARCHAR2(128)                                    NOT NULL
       id                        VARCHAR2(8)        o                              NOT NULL
       password             VARCHAR2(16)                                      NOT NULL
       PersonalNumber   CHAR(13)                                              NOT NULL
       Gender                CHAR(4)                                                NOT NULL CHECK('남자', '여자')
       phonenumber1      NUMBER(3)                                           NOT NULL 
             CHECK ('02','031','032','033','041','042','043','051','052','053','054','055','061','062','063','064')
       phonenumber2      NUMBER(4)                                           NOT NULL
       phonenumber3      NUMBER(4)                                            NOT NULL
       cellphonenubmer1 NUMBER(3)                                            NOT NULL 
                       CHECK('010','011','016','017','018','019')
       cellphonenubmer2 NUMBER(4)                                            NOT NULL
       cellphonenubmer3 NUMBER(4)                                            NOT NULL
       zipcode                NUMBER(6)                                            NOT NULL
       address                VARCHAR2(256)
       e-mail                  VARCHAR2(64)                                        NOT NULL
       homepage            VARCHAR2(64)

CREATE TABLE web_member(name VARCHAR2(128) NOT NULL,
                                               id VARCHAR2(8) PRIMARY KEY,
                                               password VARCHAR2(16) NOT NULL,
                                               PersonalNumber CHAR(13) NOT NULL,
                                               Gender CHAR(4) NOT NULL CHECK(Gender IN('남자', '여자')),
                                               phonenumber1 NUMBER(3) NOT NULL
                                               CHECK(phonenumber1
                   IN('02','031','032','033','041','042','043','051','052','053','054','055','061','062','063','064')),
                                               phonenumber2 NUMBER(4) NOT NULL,
                                               phonenumber3 NUMBER(4) NOT NULL,
                                               cellphonenubmer1 NUMBER(3) NOT NULL
                                                      CHECK(cellphonenubmer1 IN('010','011','016','017','018','019')),
                                                cellphonenubmer2 NUMBER(4) NOT NULL,
                                                cellphonenubmer3 NUMBER(4) NOT NULL,
                                                zipcode NUMBER(6) NOT NULL,
                                                address VARCHAR2(256),
                                                email VARCHAR2(64) NOT NULL,
                                                 homepage VARCHAR2(64));