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

--2011.09.16

   DML
          select : 검색
                select (*|필드명) from table명
                where 조건절 ==> 일반 구절
                group by 필드명 ==> 그룹에서 사용
                having 조건절
                order by 필드명 (asc|desc)
            내장함수
                 단일행 : TO_CHAR =>문자로 변환 (숫자, 날짜)
                              ROUND => 반올림
                              NVL => NULL값을 처리
                              DECODE => 선택문
                              CASE ~ WHEN = if와 동일
                 다중행 : MAX, MIN => 최대, 최소값
                             COUNT => ROW의 갯수
                             COUNT(*) => NULL을 포함
                             COUNT(필드명) => NULL을 제외
                             AVG, SUM
                 연산자
                             산술연산 +,-,*,/
                             관계연산 <,>=,<=,=,!=
                             BETWEEN ~ AND
                             논리연산 AND, NOT, OR (IN)
                              기타     IS NULL, IS NOT NULL
                이후에 배울 것
                         테이블 1개 이상 연결
                         JOIN (INNERJOIN, OUTERJOIN)
                         => NULL포함 여부
                              SUBQUERY
                                    main = subquery
                                    select * from emp where sal > (select avg(sal) from emp)
                             단일
                             다중 : IN, ANY, ALL
                         여러개의 쿼리문장을 동시 수행, 값을 입력
                               PL/SQL, PROCEDUER
                             검색
                                 VIEW, INDEX
        insert : 데이터 추가
              insert into table명 values(필드값)
        delete : 데이터 삭제
              delete from table명 조건절(where)
        update : 데이터 수정
              update table명 set 필드명=값, 필드명=값,.... 조건절(where)
   DDL
        create : 생성(table, view, index, proceduer, sequence)
               create table명 (필드명 데이터형 제약조건...)
             == 데이터형
                     문자형 : char : 고정 (최대길이 : 2000) String
                                 varchar2 : 가변 (최대길이 : 4000)
                     숫자형 : number (1~38, -84~127) : (7)이 디폴트 int
                     날짜형 : date, timestamp(date를 확장) => '11/09/16' Date
                     대용량 : CLOB : 문자(4GB), HTML, 회사소개
                                        ==> Clob
                                 BLOB : 그림파일, 동영상
                                        ==> InputStream
                                 BFILE
                                        == 제약조건
                     중복허용 없는 필드 : PRIMARY KEY (PK)
                     반드시 입력값 : NOT NULL (NN)
                     값의 범위를 한정 : CHECK (CK)
                     디폴트값 설정 : DEFAULT 국적, 오늘 시스템 날짜
                     참조 : FK(forein key)
                     유일값
                     회원가입 : id(pk), jumin(un)
                             alter : table 수정, 삭제, 추가(컬럼)
                             alter table table명 ADD, MODIFY, DROP
                             drop : table, view, index...삭제
                             drop table table명
                             drop view view명
  TCL
          commit : 정상수행
          rollback : 비정상수행, 원상복귀
  DCL
          grant : 권한부여
     revoke : 권한 해제

    ★ 실 습 ★
테이블 생성
CREATE table  CHAT_MEMBER(ID VARCHAR2(16) PRIMARY KEY,
                                                  PWD VARCHAR2(8) NOT NULL,
                                                  NAME VARCHAR2(128) NOT NULL, 
                                                  SEX NUMBER(1) CHECK(SEX IN(1,2,3,4,7)),
                                                  POST1 VARCHAR2(7) NOT NULL,
                                                  ADDR1 VARCHAR2(200) NOT NULL,
                                                  ADDR2 VARCHAR2(200),
                                                  REGDATE DATE DEFAULT SYSDATE,
                                                  CONTENT VARCHAR2(4000));

테이블 변경(컬럼 추가)
ALTER TABLE CHAT_MEMBER ADD TEL VARCHAR2(13);

데이터 추가
INSERT INTO CHAT_MEMBER VALUES('aaa','1234','홍길동',1,'010-150','서울시 강남구 역삼동 353-25',
    '',SYSDATE, '나는 홍길동이다','010-1111-1111');

--게시판
    1. 번호
    2. 이름
    3. 이메일
    4. 제목
    5. 내용
    6. 비번
    7. 등록일
    8. 조회수
    -------------------------
    9. 그룹번호
    10. 그룹별 순서번호
    11. 간견
    -------------------------답변
    -------------------------
    12. 루트번호
    13. 노드갯수
    -------------------------

create table board(no number primary key,
                                        name varchar2(128) not null,
                                         email varchar2(30), 
                                        subject varchar2(1000) not null,
                                        content varchar2(4000) not null, 
                                        pwd varchar2(10) not null,
                                        regdate date default sysdate, 
                                        readnum number default 0,
                                        ref number, 
                                        step number, jump number, 
                                        root number default 0, 
                                        depth number default 0);

자동증가번호(시퀀스)
create sequence board_no_seq start with 1 increment by 1;
insert into board values(board_no_seq.nextVal,'심청이','','오늘은 금요일..',
    '주말 숙제가 많습니다.','1234',sysdate,0,1,0,0,0,0);
insert into board values(board_no_seq.nextVal,'홍길동','','숙제가 뭘까?',
    '게시판(로그인, 회원가입)만들어 오기..','1234',sysdate,0,2,0,0,0,0);