2016년 6월 27일 월요일

02day DB Oracle

데이터베이스

  • 원격 데이터 저장/관리
    • C:\app\user\product\11.2.0\dbhome_1\NETWORK\ADMIN
      • 네트워크 기능 설정 (원격접속을 허용)
        • listener.ora
        • 내컴퓨터>>관리>>서비스>>OracleOraDb11g_home1TNSListener
      • 원격접속을 위한 설정
        • tnsnames.org
        • java library로 접속할 수도 있음


    1. 원격접속 세팅
    tnsname 권한 풀어줌
    상대방의 아이피와 포트를 tnsname에 객체생성 후 적어줌
    (네이버 같은 웹서버는 보통 포트번호 80 널리사용하기에 생략가능)
    sqlplus로 접속하는방법 @
    developer로 접속하는 방법 2가지 
    1. 절대경로 ip적어줌
    2. TNS로 자동으로 찾아줌
    2 *접속방법에 비밀번호를 한줄에 다 적을 수 있지만 권장하지 않음 C:\Users\user>sqlplus scott 비밀번호 입력: C:\Users\user>sqlplus scott/tiger
    *sqlplus의 가로화면 출력크기를 결정함 
    SQL> set linesize 150
    *날짜 연산 날짜 +숫자 : 일수계산 날짜 +숫자/24 : 시간 계산 
    SQL> select sysdate -hiredate from emp; 

    SQL> select (sysdate -hiredate)/7 from emp;  //입사후 몇 주가 경과되었는지 

    SQL> select hiredate, hiredate+3 from emp;
    *날짜 연산 함수 
    SQL> select empno, ename, months_between(sysdate, hiredate) from emp where empno=7839; //날짜차이를 월단위 계산 
SQL> select empno, ename, hiredate, add_months(hiredate, 6) from emp where empno=7839; //날짜에 개월을 더함 
SQL> select empno, ename, hiredate, next_day(hiredate, '수') from emp where empno=7839; //다음 요일을 계산 
SQL> select empno, ename, hiredate, last_day(hiredate) from emp where empno=7839; 
                   //입력날짜 달의 마지막 날 계산 

    SQL> select last_day('88/12/00') from dual; //에러 //일짜는 1~31 값을가져야함 
SQL> select last_day('88/12/01') from dual; 
SQL> select last_day(sysdate) from dual; 

     
select empno, ename, hiredate, round(hiredate, 'MONTH'), round (hiredate,'YEAR') from emp where empno=7839;  //날짜의 반올림 

    SQL> select empno, ename, hiredate, trunc(hiredate, 'MONTH'), trunc(hiredate,'YEAR') from emp where empno=7839; //날짜 내림
    3. 변환함수
    *날짜를 문자로 to_char 
    SQL>select empno, ename, hiredate from emp where deptno=10; 
select empno, ename, to_char(hiredate, 'fmDD MONTH YYYY') from emp where deptno=10;       //fm은 0을 제거함 

    SQL> select empno, ename, to_char(hiredate, 'DD MONTH YYYY') from emp where deptno=10; 

    SQL> select empno, ename, to_char(hiredate, 'YYYY MONTH DD') from emp where deptno=10; 
SQL> select empno, ename, to_char(hiredate, 'YYYY MONTH DD DY') from emp where deptno=10;
    *숫자를 문자로 
    SQL> select empno, ename, to_char(sal, 'L99,999.00') "급여" from emp;  //L은 화폐단위 

    SQL> select sal, to_char(sal, 'L9999.00') "급여" from emp; //9는 숫자
    *문자를 숫자로 to_number 
    SQL> select '100' +10 from dual;  //자동형변환 
SQL> select to_number('100') +10 from dual;
    *문자를 날짜로 to_date 
    SQL> select empno, ename, hiredate from emp 
  2  where hiredate=to_date('DECEMBER 17, 1980','MONTH DD, YYYY'); //월표현 부적합 
SQL> select empno, ename, hiredate from emp 
  2  where hiredate=to_date('12월 17, 1980','MONTH DD, YYYY');
    4. 일반함수 *null 처리함수 nvl 
    SQL> select sal, comm, sal+comm from emp where deptno=10; 
SQL> select sal, nvl(comm,0), sal+comm from emp where deptno=10;   //null을 0으로 
SQL> select sal, nvl(comm,0), sal+nvl(comm,0) from emp where deptno=10; 

    SQL> select empno, ename, sal, comm, sal*12+comm, sal*12+nvl(comm,0) from emp; 
//함수의 첫번째 인자가 null이면 두번째 인자를 리턴한다 

    SQL> select empno, ename, sal, comm, sal+comm, nvl2(comm,sal+comm,sal) from emp; 
//첫번째 인자가null이면 두번째 null 이면 세번짜 리턴한다 

    SQL> select empno, ename, job, nullif(length(ename), length(job)) from emp; 
//첫번, 두번째 인자가 같으면 null을 다르면 첫번째 인자를 리턴한다 

    SQL> select empno, ename, sal, comm, coalesce(sal,comm, 10) from emp; 
//인자들 중에 null아닌 최초의 인자를 리턴한다
    *case 구문 
    SQL> select empno, ename, sal, job, 
  2  case job when 'ANALYST' then sal*1.1 
  3           when 'CLERK'   then sal*1.2 
  4           when 'MANAGER' then sal*1.3 
  5           when 'PRESIDENT' then sal*1.4 
  6           when 'SALESMAN' then sal*1.5 
  7           else sal 
  8  end "급여" 
  9  from emp; 
//if then else 문장과 비슷함
    *sqlplus 에서는 복잡한 코드를 sqlscript에 쓰고 불러올수 있음 
    SQL> @c:\oracle\test.sql   //파일읽기 
SQL> l                            //명령문 보기 
                                     //주석(--내용)은 보이지 않음
    *decode 함수 
    SQL> select empno, ename, sal, job, 
  2  decode(job, 'ANALYST'      , sal*1.1, 
  3              'CLERK'        , sal*1.2, 
  4              'MANAGER'      , sal*1.3, 
  5              'PRESIDENT'    , sal*1.4, 
  6              'SALESMAN'     , sal*1.5, sal) "급여" 
  7  from emp;
    *함수의 중첩 가능 
    SQL> select empno, ename, mgr, 
  2  nvl(to_char(mgr), '상위 관리자 없음') 
  3  from emp 
  4  where mgr is null;
    5. 그룹함수 
    SQL> select max(sal), min(sal) from emp; 
SQL> select ename, max(sal) form emp;  //그룹은 낱개와 함께 사용할 수 없음
    *숫자세기 
    SQL> select count(sal), count(comm) from emp; 

    SQL> select count(sal), count(comm), count(*) from emp; 

    SQL> select count(distinct job) from emp; 
SQL> select distinct job from emp;  //distinct 중복제거
    *평균 (null값을 세지않고 구함) 
    SQL> select avg(sal) from emp; 
SQL> select avg(comm), sum(comm)/count(comm), sum(comm)/count(*) from emp; 
SQL> select avg(nvl(comm,0)) from emp;
    6. 그룹함수 + Group by =부분집합 함수 
    SQL> select avg(sal) from emp group by deptno; 
SQL> select deptno, avg(sal) from emp group by deptno; //그룹기준 변수 선택가능 
SQL> select deptno, ename, avg(sal) from emp group by deptno; //일반변수는 에러남 

    SQL> select deptno, max(sal), min(sal) from emp group by deptno;
    *having 
    SQL> select deptno, avg(sal) from emp 
  2  where avg(sal)> 2000   //에러남 where절 뒤에는 그룹함수 쓸수 없음 
  3  group by deptno; 
SQL> select deptno, avg(sal) from emp 
  2  group by deptno 
  3  having avg(sal)>2000;
    *where절에는 각각의 데이터를 묻는 조건절은 사용가능함 
    SQL> select deptno, avg(sal) from emp 
  2  where deptno != 10 
  3  group by deptno;
    *중복 부분그룹도 가능함 
    SQL> select deptno, job, avg(sal) from emp 
  2  group by deptno, job                        //부서별 직업별 평균을 구함 
  3  order by deptno, job; 
SQL> select deptno, job, sal from emp 
  2  order by deptno, job;
    7. 서브쿼리 :매인쿼리 안쪽 쿼리 단일행 연산자(>, =, >=, <>, <=): 결과값이 딱 1개 복수행 연산자(IN, ANY, ALL):  두개이상의 행을 리턴 
    SQL> select sal from emp where ename='SCOTT'; 
SQL> select ename from emp where sal>3000; 
SQL> select ename from emp where sal>(select sal from emp where ename='SCOTT'); 
//SCOTT보다 높은 봉급을 받는 사람을 구함 

    SQL> select empno, ename, job from emp where job=(select job from emp where empno=7844); 
//7844사원과 같은 직종을 가진 사람의 번호,이름,직종을 구함 

    SQL> select empno, ename, sal from emp 
  2  where sal = (select min(sal) from emp); 
 

    SQL> select deptno, min(sal) from emp 
  2  group by deptno 
  3  having min(sal)>(select min(sal) from emp where deptno =20); 
//20번부서의 봉급최소값보다 높은 봉급최소값을 받는 부서를 구함 

    SQL> select ename from emp    
  2  where sal = (select min(sal) from emp group by deptno); 
//오류남 복수행 연산자 필요
    *복수행 연산자 IN 
    SQL> select ename, sal 
  2  from emp 
  3  where empno in (select empno from emp where job= 'CLERK'); 
//직종이 CLERK인 사번의 이름 연봉을 구함
    *ALL 
    SQL> select sal 
  2  from emp 
  3  where job = 'MANAGER'; 
SQL> select empno, ename, sal 
  2  from emp 
  3  where sal< all(select sal from emp where job= 'MANAGER');
    *ANY 
    SQL> select empno, ename, sal 
  2  from emp 
  3  where sal> ANY(select sal from emp where job= 'MANAGER');
    8.예제 풀기 ---사원 테이블에서 입사일이 81년도인 직원의 사번, 사원명, 입사일, 업무, 급여를 검색 
    SQL> select empno, ename, hiredate, job, sal
  2  from emp
  3  where hiredate like '81%';

---사원 테이블에서 입사일 81년도 업무가 'SALESMAN'이 아닌 직원 
SQL> R 
  1  select empno, ename, hiredate, job, sal 
  2  from emp 
  3  where hiredate like '81%' 
  4* and job <> 'SALESMAN' 
--사원 테이블에서 사원명의 세 번째 알파벳이 'N'인 사원의 사번, 사원명을 검색 
SQL> select empno, ename, sal 
  2  from emp 
  3  where ename like '__N%'; 
--사원 테이블의 사원명에서 2번째 문자부터 3개의 문자를 추출 
SQL> select substr(ename, 2,3) from emp; 
--사원 테이블에서 입사일이 12월인 사원의 사번, 사원명, 입사일을 검색 
SQL> select empno, ename, hiredate 
  2  from emp 
  3  where to_char(hiredate, 'MM')=12; 
--사원 테이블에서 급여에 따라 사번, 이름, 급여, 등급을 검색
    //방법1 
    SQL> select empno, ename, sal, 
  2  case trunc(sal-1,-3) when 0 then 'E' 
  3                       when 1000 then 'D' 
  4                       when 2000 then 'C' 
  5                       when 3000 then 'B' 
  6                       else 'A' 
  7  end "등급" 
  8  from emp; 
//방법2 
SQL> select empno, ename, sal, 
  2  case when sal between 0 and 1000 then 'E' 
  3       when sal between 1001 and 2000 then 'D' 
  4       when sal between 2001 and 3000 then 'C' 
  5       when sal between 3001 and 4000 then 'B' 
  6       when sal between 4001 and 5000 then 'A' 
  7  end "등급" 
  8  from emp; 
---사원 테이블에서 최대 급여, 최소 급여, 전체 급여 합, 평균 급여를 검색 
SQL> select max(sal), min(sal), sum(sal), avg(sal) from emp; 
---사원 테이블에서 부서별 인원수를 검색 
SQL> select deptno, count(ename) from emp group by deptno; 
---사원 테이블에서 부서별 인원수가 6명 이상인 부서코드를 검색 
SQL> select deptno from emp 
  2  group by deptno 
  3  having count(ename)>=6; 
---사원 테이블에서 급여가 높은 순서대로 등수를 부여하고자 한다. 다음과 같은 결과를 출 
력할 수 있는 SQL 문장을 작성하시오 
(Hint : Self Join, Non-Equi Join, GROUP BY, COUNT 사용)
    작성자: 시간:

    댓글 없음:

    댓글 쓰기

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