SQL 기초/쿼리문

  SELECT 
  FROM 
  WHERE  
  GROUP BY 
  HAVING  
  ORDER BY  순서 외우세요


CREATE USER 유저명 IDENTIFIED BY 비밀번호;              -계정만들기

GRANT CONNECT , RESOURCE TO 유저명 ;                    -권한설정

CREATE TABLE 테이블명( 컬럼1 타입 , 컬럼2 타입 ...) ;    -테이블만들기

DROP TABLE 테이블명 ;                                   -테이블삭제

INSERT INTO 테이블명 (컬럼1,컬럼2...)                    -값추가
VALUES (값1,값2...) ;

UPDATE 테이블명 SET 컬럼명='바꿀값',컬럼명2='바꿀값'      -값수정
WHERE 컬럼명='고정값';

SELECT * FROM 테이블명 WHERE 컬럼명 LIKE '박%';          -'박'으로 시작값 검색

SELECT * FROM 테이블명 WHERE 컬럼명 LIKE '박_';	         -'박'으로 시작, 두글자만검색

SELECT * FROM 테이블명 WHERE 컬럼명 NOT LIKE '박%';       -'박'으로 시작값 제외검색

ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 새컬럼명;    -컬럼명 바꾸기

ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입()) ;       -데이터타입 수정

ALTER TABLE 테이블명 MODIFY 컬럼명                       -제약조건 NOTNULL 변경
CONSTRAINT 테이블명_컬럼명_NN(제약조건명) NOT NOLL;       

ALTER TABLE 테이블명 DROP COLUMN 컬럼명 ;               - 컬럼삭제(행)

ALTER TABLE 테이블명 ADD (컬럼명 데이터타입()) ;         - 컬럼추가(행)

ALTER TABLE 테이블명                                               -기본키넣기
ADD CONSTRAINT 테이블명_컬럼명_PK(제약조건명) PRIMARY KEY(컬럼명);  

ALTER TABLE 테이블명                                      -제약조건 삭제
DROP CONSTRAINT 테이블명_컬럼명_PK(제약조건명);

SELECT * FROM (SELECT ROWNUM NUM, 테이블.* FROM 테이블)  -ROWNUM 이용 순서대로 값불러오기
WHERE NUM BETWEEN 3 AND 5;

SELECT DISTINCT 컬럼명 FROM 테이블  ;              - 중복된 컬럼명 제외 검색.

SELECT SUBSTR(컬럼 , 시작값 , 끝값) FROM 테이블;  - 원하는 글자만 검색.

SELECT * FROM 테이블 
WHERE SUBSTR (컬럼,시작값,끝값) IN ( 'A','B','C')  - 찾는SUBSTR값이 A,B,C 일때
                           NOT IN ('A','B','C')   - A,B,C 제외해서 .


SELECT CONCAT ( '홍','길동') FROM DUAL ;     - 문자열 합치기
SELECT '홍'||'길동' FROM DUAL;   
   
SELECT LOWER('dfdfd') FROM DUAL;             -문자열 소문자로

SELECT UPPER('DFDFD') FROM DUAL;             -문자열 대문자로

SELECT REPLACE('WHERE WE ARE', 'WE' , 'YOU') FROM DUAL; -   -WE글자를 YOU로 바꿈

SELECT TRANSLATE('WHERE WE ARE','WE','YOU') FROM DUAL;    - W를 Y로, E를 O로 바꿈

SELECT RPAD (컬럼명 ,  6 , '0' ) FROM 테이블 ;  - 컬럼을 6바이트로 채우고,남은자리에 0 을 넣음
       LPAD                                    -왼쪽에 0을 넣음, 한글은 바이트수가 두배
       
SELECT INITCAP ('Sql Database') FROM DUAL;     -문장 앞글자 대문자로.

SELECT INSTR( 문자열,검색문자열,위치,찾을수 ) FROM DUAL -문자열검색,정수값반환




SELECT EXTRACT(YEAR FROM SYSDATE) FROM DAUL; -날짜추출함수

SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;      -날짜누적함수

SELECT MONTH_BETWEEN (SYSDATE,TO_DATE('2013-12-25'))FROM DUAL; -두날짜 차이 알아냄.

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,날짜컬럼)) FROM 테이블;      - 근무개월수

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';  - 시분초 셋팅

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI;SS') FROM DUAL;  - 날짜형식을 원하는형식으로



SELECT TO_CHAR(123456789 ,'9,999,999,999')'원' FROM DUAL; -숫자를 문자로 구분자 넣을수있음.

SELECT NVL(컬럼명 , 0 ) FROM 테이블                   -NULL값 0 으로 출력

SELECT NV2(컬럼명 ,값이있을때 구할값, 0) FROM 테이블   -값이있을때만 계산, NULL값 0 출력

SELECT NULLIF (컬럼명 , 값 ) FROM 테이블 ;         -컬럼명과 값이 같다면 NULL 값을 출력


SELECT DECODE (SUBSTR(PHONE,1,3), '011','SK','016','KT','기타')FROM 테이블명  -IF문과비슷
                  기준          비교값,출력값,비교값,출력값, 그외
       
SELECT ROW_NUMMBER() OVER (ORDER BY 컬럼) , 컬럼1 ,컬럼2 ...
FROM 테이블                                                -컬럼으로 정렬후 ROWNUM을 붙임.

SELECT RANK() OVER (ORDER BY 컬럼) , 컬럼1 ,컬럼2 ...
FROM 테이블                                                -컬럼으로 정렬후 RANK를 맥임. 1 22 4

SELECT DENSE_RANK() OVER (ORDER BY 컬럼) , 컬럼1 ,컬럼2 ...
FROM 테이블                                               -컬럼으로 정렬후 RANK를 맥임. 1 22 3


ORDER BY 컬럼 ASC ;   -컬럼기준으로 오름차순,

ORDER BY 컬럼 DESC;   -컬럼기준으로 내림차순,

ORDER BY 컬럼 DESC , 컬럼 ASC;  -컬럼기준 1,2차 정렬.


-------------------------------------------------------------------------
Example
-------------------------------------------------------------------------
-SCOTT 사원의 부서명
SELECT DNAME FROM DEPT_TBL
WHERE DEPTNO= ( SELECT DEPTNO FROM EMP_TBL WHERE ENAME='SCOTT');

-SCOTT 같은부서 사원과 부서번호
SELECT ename,deptno FROM emp_tbl
WHERE deptno = (select deptno from emp_tbl where ename='SCOTT');

-SCOTT 같은직급의 사원 
SELECT * FROM emp_tbl
where job = (select job from emp_tbl where ename='SCOTT');

-SCOTT 의 급여와 동일하거나 더많이받는 사원
SELECT ENAME , SAL FROM EMP_TBL
WHERE sal>= (select sal from emp_tbl where ename='SCOTT');

-DALLAS에서 근무하는 사원이름,부서번호
SELECT ENAME , DEPTNO FROM EMP_TBL
WHERE deptno = (select deptno from dept_tbl where loc='DALLAS');

-SALES 부서 근무하는 모든사원
SELECT ENAME , SAL FROM EMP_TBL
WHERE DEPTNO = (select deptno from dept_tbl where dname='SALES');

-직속상관이 KING
SELECT ename , sal FROM emp_tbl
WHERE mgr = (select empno from emp_tbl where ename='KING');

-평균급여보다 더 많은사원
SELECT ename , sal FROM emp_tbl
WHERE sal >(SELECT AVG(sal) FROM emp_tbl);


-------------------------------------------------------------------------
Example
-------------------------------------------------------------------------

-3000 이상 받거나 10, 20번 부서 중 하나가 참일때 모두실행
SELECT ename, sal, deptno FROM emp_tbl
WHERE deptno in ( SELECT distinct deptno FROM emp_tbl WHERE sal>= 3000);

-부서별로 가장 급여 많이받는 사원
SELECT empno , ename , sal, deptno from EMP_TBL
WHERE sal in( SELECT MAX(SAL) FROM emp_tbl GROUP BY DEPTNO);

-직급이 manager인 속한 부서의 부서번호와 부서명 지역 출력
SELECT deptno,dname,loc FROM dept_tbl
where deptno in (SELECT deptno FROM emp_tbl where job='MANAGER');

-job 별로 최소값 월급
SELECT empno , ename , job , hiredate, sal , deptno FROM emp_tbl
WHERE sal IN ( SELECT MIN(sal) FROM emp_tbl GROUP BY job);

-30번 소속사원중 제일높은급여보다 더많은급여받는사람 출력
SELECT ename,sal FROM emp_tbl
WHERE SAL >ALL (SELECT sal FROM emp_tbl WHERE DEPTNO=30);

-30번 소속사원중 가장작은 급여보다 더많은급여를받은사람 출력
SELECT ename, sal FROM emp_tbl
WHERE SAL > ANY (SELECT sal FROM emp_tbl WHERE deptno=30);


- 'SCOTT' 사원의 부서이름 찾기.
SELECT e.ename , d.dname , d.deptno
FROM EMP_TBL e , DEPT_TBL d
WHERE e.deptno = d.deptno AND UPPER (e.ename) = 'SCOTT';

-'SCOTT' 사원의 근무지 
SELECT e.ename ,d.ename
FROM EMP_TBL e , emp_tbl d
WHERE e.deptno= d.deptno and
      UPPER(e.ename)='SCOTT'  and
      e.ename <> d.ename;