2016년 6월 27일 월요일

03day DB Oracle

데이터베이스 연관 프로젝트
  • 기존 프로젝트 투입
    • 먼저 데이터베이스의 구조를 알아야 함
      • ERD (Entity-Relationship Diagram) 그림으로 알 수 있음
        • shopping Mail
          • ERD( logical/ physical)
        • 테이블 명세서
    • 프로그램을 어떤 종류로 사용할 것인지 정함
      • 원격접속이 가능한지
      • 원격접속이 불가능 하면 GUI툴을 사용하지 못할 수 있음
  • 새로운 프로젝트 투입
    • 새로운 문서 - 모델링에 관한 문서
      • UML - 객체에 대한 모델링
      • ERD - 데이터베이스에 대한 모델링
        • ERD tool
          • sql developer data modeler (oracle) -무료
          • ERWin - 유료
    • 문서가 나와야지 프로젝트를 진행할 수 있음
    • 3개월 프로젝트
      • 1개월~1.5 기획
      • 1개월 프로그램
      • 1개월 문서화
데이블 구조
  • select * from table
  • describe table
  • select *|{DISTINCT] colum|expresiion [alias], ...} from table -컬럼절
  • [WHERE condition(s)] - 행선택
  • [GROUP BY column]
  • [HAVING group_condition]
  • [ORDER BY {column, expr} [ASC | DESC]] - 정렬

함수
  • 단일행 함수
    • 문자 함수
    • 숫자 함수
    • 날짜 함수
    • 변환 함수
    • 일반 함수
  • 복수행 함수
    • select *|{DISTINCT] group function(column), ...
    • from table
    • [WHERE condition(s)]
    • [GROUP BY column]
    • [HAVING group_condition]
    • [ORDER BY column];
    • 그룹함수
서브쿼리
  • SELECT select_list
  • from table
  • WHERE expr operator
    • (SELECT select_list FROM table);
  • where 서브쿼리 결과
    • 단일행 서브쿼리
      • 비교연산자
    • 복수행 서브쿼리
      • in, any, all (=, <. >)
  • having
  • from    -inline view
  • 컬럼절
SQL tunning
  • 데이터베이스의 구조를 알아야 속도를 향상시킬 수 있음
SQL ANSI

  • 92 → 99 : MS - join 구문이 달라짐
  • where 분리
    • 조인 조건
    • 일반 조건
데이터 형식

  • 숫자
    • number
  • 문자열
    • char  : 고정길이 2000byte
    • varchar2 : 가변길이 4000byte (약 1000자)
    • long : 가변길이 2GB
    • clob : 4GB
  • 날짜
    • date
  • 기타
    • raw
    • long raw
    • blob
    • bfile
    • rowid


1. join *카테시안 프로덕트 --- 테이블을 무조건 곱해서 합침 select * from dept, emp;
*equi-join ----참조번호가 일치할 경우에 합침 
SQL> select * 
  2  from dept, emp 
  3  where dept.deptno = emp.deptno; 

SQL> select deptno, dname, loc, ename, empno 
  2  from dept, emp 
  3  where dept.deptno = emp.deptno; 
//컬럼을 선택해서 가져올때 어느테이블인지 명시를 해주지 않으면 에러남 
 
SQL> select dept.deptno, dept.dname, dept.loc, emp.ename, emp.empno 
  2  from dept, emp 
  3  where dept.deptno = emp.deptno;
**table aliasing 테이블 이름 재정의 (두 자 이상 하지말것) 
SQL> select d.deptno, d.dname, d.loc, e.ename, e.empno 
  2  from dept d, emp e 
  3  where d.deptno = e.deptno;
**Non-EquiJoin -----부등호 조건으로 테이블을 합침 
SQL> select empno, ename, sal grade, losal, hisal 
  2  from emp, salgrade 
  3  where emp.sal<=salgrade.hisal and emp.sal>=salgrade.losal; 
 
SQL> select empno, ename, sal grade, losal, hisal 
  2  from emp, salgrade 
  3  where emp.sal between salgrade.losal and salgrade.hisal;   //between절 
 
SQL> select empno, ename, sal grade, losal, hisal 
  2  from emp e, salgrade s                                              //table aliasing 
  3  where e.sal between s.losal and s.hisal;
**테이블 3개의 조인 
SQL> select empno, ename, loc, sal, grade 
  2  from emp e, salgrade s, dept d 
  3  where e.sal between s.losal and s.hisal 
  4  and d.deptno=e.deptno;
**Outer-Join  --- 테이블에 일치하지 않는 데이터도 선택함 //온라인 구매에 모든목록을 보여주면서, 구매할 것을 선택할 때 사용함 
SQL> select emp.empno, emp.ename, dept.dname 
  2   from emp, dept 
  3   where emp.deptno(+) = dept.deptno; 

SQL> select emp.empno, emp.ename, dept.dname 
  2   from emp, dept 
  3   where emp.deptno(+) = dept.deptno 
  4  and emp.empno is null;
**Self-Join  ----하나의 테이블 안에 연관된 데이터가 있을 경우 자신을 참조함 
SQL> select w.ename "사원", m.ename "사수" 
  2  from emp w, emp m 
  3  where w.mgr = m.empno; 

SQL> select w.ename "사원", m.ename "사수" 
  2  from emp w, emp m 
  3  where w.mgr = m.empno(+);
2. SQL : 1999 Join **Cross Join  ----카르테시안 프로덕트와 동일 
SQL> select empno, ename, dname 
  2  from dept cross join emp;
**Natural Join 
SQL> select empno, ename, dname  ------Equi-Join과 동일 
  2  from dept natural join emp;
**USING 구문을 이용한 Join  ---두개 이상의 컬럼이 같은 이름을 가졌을 때 
SQL> select empno, ename, dname 
  2  from dept join emp 
  3  using(deptno);
**ON 구문을 이용한 Join  ---테이블 컬럽의 이름이 서로 다를 경우 
SQL> select empno, ename, dname 
  2  from dept 
  3  join emp 
  4  on dept.deptno = emp.deptno;
//예제 : 사원번호 사원명 급여 호봉을 1999 문법으로 작성 
SQL> select empno, ename, sal, grade 
  2  from emp e 
  3  join salgrade s 
  4  on e.sal between s.losal and s.hisal;
**LEFT OUTER JOIN 
SQL> select empno, ename, dname 
  2  from dept left outer join emp 
  3  on dept.deptno = emp.deptno;
**RIGHT OUTER JOIN 
SQL> insert into emp 
  2  values(8000, 'KIM', 'MANAGER',7934, '04/06/22', 2000, NULL, NULL); 
SQL> select empno, ename, dname 
  2  from dept right outer join emp 
  3  on dept.deptno = emp.deptno;
**FULL OUTER JOIN 
SQL> select empno, ename, dname 
  2  from dept full outer join emp 
  3  on dept.deptno= emp.deptno;
3. 집합연산자 
SQL>
CREATE TABLE A(VAL CHAR); 
INSERT INTO A VALUES ('A'); 
INSERT INTO A VALUES ('B'); 
INSERT INTO A VALUES ('C'); 
INSERT INTO A VALUES ('D'); 
INSERT INTO A VALUES ('E'); 
 
CREATE TABLE B(NUM NUMBER, VAL CHAR); 
INSERT INTO B VALUES (1, 'C'); 
INSERT INTO B VALUES (2, 'D'); 
INSERT INTO B VALUES (3, 'E'); 
INSERT INTO B VALUES (4, 'F'); 
INSERT INTO B VALUES (5, 'G'); 
 
**UINION  ---중복을 배제 
SQL> select val from a 
  2  union 
  3  select val from b;
**UINION ALL ----중복을 표시 
SQL> select val from a 
  2  union all 
  3  select val from b; 

SQL> select job from emp where deptno =10 
  2  union 
  3  select job from emp where deptno =30; 
SQL> select job from emp where deptno =10 
  2  union all 
  3  select job from emp where deptno =30;
**INTERSECT   --중복되는 행만 가져옴 
SQL> select val from a 
  2  intersect 
  3  select val from b;
**MINUS    ----차집합 
SQL> select val from a 
  2  minus 
  3  select val from b;
** 
SQL> select val, null from a 
  2  union 
  3  select val, num from b; 
SQL> select job, null from emp where deptno =10 
  2  union 
  3  select job, ename from emp where deptno =30;
4. 테이블 생성 및 관리 **CREATE TABLE create table 테이블명 (          컬럼명 데이터타입(크기),          ......          ... ) 
SQL> create table dept2 
  2  (deptno number(2), 
  3  dname varchar2(10), 
  4  loc varchar2(20) 
  5  ); 
SQL> select * from tab;
5. 서브 쿼리를 이용한 테이블 생성 
SQL> create table emp2 
  2  as select * from emp; 
// 테이블 복사하기 

SQL> create table emp3 
  2  as select empno, ename, sal from emp; 
//컬럼 선택해서 가져오기 

SQL> create table emp4 
  2  as select empno, ename, sal from emp where deptno=30;  
//행갯수 선택해서 가져오기 

SQL> create table emp_year 
  2  as select empno, ename, sal*12+nvl(comm,0) annsal, hiredate 
  3  from emp 
  4  where deptno = 10;  // 컬럼을 계산하고 aliasing 하며 가져오기 
 
SQL> create table emp_year2 
  2  as select empno, ename, sal*12+nvl(comm,0), hiredate  
                                  //컬럼명을 alias하지 않으면 에러남 
  3  from emp 
  4  where deptno=10;
**예제: 사원번호, 사원명, 급여, 호봉을 저장한 emp5번 테이블 생성 
SQL> create table emp6 
  2  as select * 
  3  from emp 
  4  where 1 != 1;   // 부정구문으로 데이터 껍질만 가져오기
6. 테이블 삭제 
SQL> select * from tab; 
SQL> drop table dept2; 
SQL> drop table a; 
SQL> drop table b; 
SQL> select * from tab;   //완전 삭제가 되지 않고 보관함에 보관해 둠
**테이블 복구 
SQL> desc recyclebin; 
SQL> flashback table dept2 to before drop;  //삭제 데이터를 복구 
SQL> select * from tab; 

SQL> drop table dept2 purge; //복원함 넣지 말고 완벽한 삭제 
SQL> purge recyclebin;  //복원함 비우기
7.  테이블 수정 **컬럼추가 
SQL> alter table emp_year 
  2  add (job varchar2(10));
**컬럼변경 //데이터가 없으면 변경이 자유롭고, 데이터가 있으면 증가 방향으로만 가능하다. 
SQL> alter table emp_year 
  2  modify (job varchar2(20));           //데이터 크기 늘리기 

SQL> alter table emp_year 
  2  modify (job number(4));         //데이터 형태 바꾸기 

SQL> alter table emp_year 
  2  rename column job to work;         //컬럼명 바꾸기
**컬럼 삭제 // 하나에 하나씩 삭제가 가능함 
SQL> alter table emp_year 
  2  drop(work);
**테이블명 변경 
SQL> rename emp_year to year_emp;
8
작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)