개발새발

12/05/09

기본적으로 제공되는 scott/tiger 계정으로 연습한 쿼리 예제문

http://www.oracleclub.com => 오라클강좌 참고

-- 테이블 생성 및 제약조건
 CREATE TABLE EMP2(
    EMPNO  NUMBER    CONSTRAINT emp_pk_empno PRIMARY KEY,    
    ENAME  VARCHAR2(20) CONSTRAINT emp_nn_ename NOT NULL,
    JOB    VARCHAR2(40),
    MGR    NUMBER,
    HIREDATE  DATE,
    SAL    NUMBER,
    COMM   NUMBER,
    DEPTNO NUMBER
);

-- SELECT 를 이용한 테이블 생성
CREATE TABLE emp2
  AS SELECT * FROM emp;

-- 테이블 삭제
DROP TABLE EMP2;

-- 테이블 복사
CREATE TABLE EMP3
  AS SELECT * FROM EMP;
 
-- 컬럼 추가
ALTER TABLE emp
  ADD (addr VARCHAR2(50));

-- 컬럼 수정
ALTER TABLE emp
  MODIFY (ename VARCHAR2(50));

-- 컬럼 삭제
ALTER TABLE emp
  DROP COLUMN addr;

-- 데이터 삽입
INSERT INTO dept (deptno, dname)
  VALUES(10, 'ACCOUNTING' );

-- 데이터 수정
UPDATE emp
  SET deptno = 30
  WHERE empno = 7902;
 
-- 데이터 삭제
DELETE FROM emp
  WHERE empno = 7902 ;
 
-- SELECT문( IN )
select ename, empno
  from emp
  where empno IN(7900, 7934);
 
-- SELECT문(BETWEEN)
select empno, ename
  from emp
  where sal between 3000 and 5000;
 
-- SELECT문(LIKE)
select empno, ename
  from emp
  where UPPER(name) LIKE '%K%';

-- SELECT문(ORDER BY) asc=오름차순, desc=내림차순
select empno, ename
  from emp
  where deptno = 30
  ORDER BY ename ASC;
 
-- JOIN(EQUI JOIN)
SELECT  e.ename, d.dname
  FROM   emp e , dept d
  WHERE e.deptno = d.deptno;

-- JOIN(OUTER JOIN)
SELECT DISTINCT(a.deptno), b.deptno
  FROM emp a, dept b
  WHERE  a.deptno(+) = b.deptno;

-- 내장함수(숫자함수)
--    반올림
SELECT ROUND(192.123, 1) test FROM DUAL ;

-- 내장함수(숫자함수)
--    올림
SELECT CEIL(10.1) test FROM DUAL;

-- 내장함수(숫자함수)
--    버림
SELECT FLOOR(10.1) test FROM DUAL;

-- 내장함수(문자열 함수)
--    문자 결합
SELECT CONCAT('Oracle', 'Club') FROM DUAL;

-- 내장함수(문자열 함수)
--    LOWER <= 소문자  , UPPER <= 대문자
SELECT LOWER('KIM JUNG SICK') FROM DUAL;
SELECT UPPER('KIM JUNG SICK') FROM DUAL;

-- 내장함수(문자열 함수)
--    SUBSTR('A', B, C) <= A의 B번째 문자부터 C의 개수만큼 반환
SELECT SUBSTR('JUNG-SICK', 3, 4) FROM DUAL;

-- 내장함수(문자열 함수)
--    LENGTH <= 문자 개수 반환
SELECT LENGTH('JUNG-SICK') TEST FROM DUAL;

-- 내장함수(문자열 함수)
--    REPLACE('A', 'B', 'C') <= 'A'문자 중에 'B'에 해당하는 문자를
--                              'C'로 반환한다
SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;

-- 내장함수(문자열 함수)
--   왼쪽에서 3번째부터 시작을 해서 비교를 하는데 
--   OR이 두 번째 검색되는 지점의 위치를 반환 한다.
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring" FROM DUAL;

-- 내장함수(문자열 함수)
--    TRIM('A' from 'ABCDE' <= 'A'를 제거한다
SELECT TRIM('A' from 'ABCDE') "TRIM EXAMPLE"
  FROM DUAL;

-- 내장함수(날짜 처리 함수)
--   오늘날짜 출력
SELECT to_char(sysdate, 'YYYY-MM-DD') today
  FROM dual;
 
-- 내장함수(날짜 처리 함수)
--  날짜 출력
SELECT TO_DATE('2011/05/08','YY/MM/DD') FROM DUAL;

-- 내장함수 (General Functions)
--    DECODE
SELECT deptno,
      DECODE(deptno, 10 , 'ACCOUNTING' ,
                     20 , 'RESEARCH' ,
                     30 , 'SALES' ,
                     40 , 'OPERATIONS')
     FROM emp;

-- 내장함수 (CASE)
--    DECODE와 같은 기능
SELECT deptno,
        CASE deptno
          WHEN 10 THEN 'ACCOUNTING'
            WHEN 20 THEN 'RESEARCH'
            WHEN 30 THEN 'SALES'
            ELSE 'OPERATIONS'
          END as "Dept Name"
     FROM emp;

-- GROUP BY
-- 부서별로 그룹한 결과와 사원수 조회
SELECT b.deptno, COUNT(a.empno)
  FROM emp a, dept b
  WHERE a.deptno = b.deptno
  GROUP BY b.deptno;

-- 부서별로 그룹하여  부서번호, 인원수, 급여의 평균,
-- 급여의 합을 조회하는 예제
SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균",
            ROUND(SUM(sal)) "급여합계"
     FROM emp
     GROUP BY deptno;
 
-- 업무별로 그룹하여  업무, 인원수, 평균 급여액,
-- 최고 급여액, 최저 급여액 및 합계를 조회하는 예제
SELECT job, count(empno),AVG(sal)"평균",
       MAX(sal), MIN(sal), SUM(sal)
  FROM emp
  GROUP BY job;

--HAVING
--사원수가 5명이 넘는 부서의 부서명과 사원수 조회
SELECT d.dname, count(e.empno)
  from emp e, dept d
  where e.deptno = d.deptno
  group by d.dname
  having count(e.empno) >= 5;
 
--서브쿼리
SELECT ename,job
  FROM emp
  WHERE job = (SELECT job
              FROM emp
              WHERE empno = 7369);

SQL의 종류

1. DDL (Data Definition Language) : 데이터베이스 객체(테이블,뷰,인덱스..)의 구조를 정의 합니다.

2. DML (Data Manipulation Language) : 데이터의 삽입,삭제,갱신등을 처리

3. DCL (Data Control Language) : 데이터베이스 사용자의 권한을 제어

출처 http://www.oracleclub.com/lecture/1003

SELECT문 세부정리

   SELECT [c_name] FROM [t_name] WHERE [c_name] LIKE [%searching%] ORDER BY [c_name] DESC;
   SELECT * from org_user WHERE email IS NULL;   //이메일이 없는 유저 출력
   SELECT * from org_user WHERE create_dt >to_date('12/03/15') AND ex_user_type='1';
            // 12.3.15일 이후 가입했으면서 비정규직 출력

   SELECT * from org_user WHERE name LIKE '서울%';  //주소가 서울로 시작되는 사람 출력
   SELECT * from org_user WHERE ex_ssn LIKE '_______1%'; //남자인 사람 출력(뒷자리 1로 시작)
     * 가나%     : 앞에 '가나'가 오는 모든 문자열
     * %가나%    : 어느 위치든 '가나'가 포함되어 있는 모든 문자열
     * %가나     : 뒤에 '가나'가 오는 모든 문자열
     * ___가     : 네번째에 '가'가 오는 문자열
     * __가___나 : 세번째에 '가', 일곱번째에 '나'가 오는 문자열

   SELECT * from org_user WHERE name IN('장재순','장잭순'); //이름이 장재순 또는 장잭순 인 사람 출력
   SELECT * from org_user ORDER BY name, id;  // 이름, id 순으로 검색 =>내림차순
   SELECT * from org_user WHERE rownum<4 ORDER BY name DESC; //이름을 상위레코드 3개만 내림차순검색

INSERT INTO [t_name]([c_name) VALUES ([data]);
DELETE FROM [t_name] WHERE [condition];
CREATE TABLE [t_name](c_name type option...);
UPDATE [t_name] SET [c_name]=? WHERE [condition]
ALTER TABLE [t_name] ADD([c_name c_type c_option]);
ALTER TABLE [t_name] MODIFY([c_name c_type c_option]);
ALTER TABLE [t_name] DROP COLUMN [c_name];

//자세한건 차후 정리 예정