1. sqlplus login
- sqlplus /nolog
conn /as sysdba
alter user hr identified by hr account unlock (sysdba) e
conn scott (default user : scott / tiger)
- disconn
2. Data Dictionary
desc table_name -> Column, Nullable, Type, Commnet
SELECT * FROM Tab -> 존재하는 테이블 확인 SELECT * FROM USER_TABLES -> 각 테이블 정보 상세하게 확인
COMMENT ON [TABLE/COLUMN] [table_name / table_name.column_name] IS '주석 내용'
- data dictionary
-comments 확인SELECT * FROM USER_TAB_COMMENTSSELECT * FROM USER_COL_COMMENTS
3. 연산
= 같다< > or != 같지 않다IS NULL IS NOT NULL
4. dual table
함수 값 사용 sysdate 또는 입력값 표시
SELECT sysdate FROM dual
5. CONCATENATION
SELECT ENAME " " JOB "ENAME JOB" FROM EMP;
=============================================
복원 지점 COMMIT복원 ROLLBACK
============= 여러 건 insert ================
INSERT INTO EMP (ENPNO, ENAME, JOB, MGR)SELECT 9000+rownum, ENAME, JOB, MGR FROM EMP;
================= ORDER BY ==================
default ASC (오름차순)DESC (내림차순)
ORDER BY Column_name DESC
=============================================
SELECT DEPTNOFROM DEPTWHERE EXISTS (SELECT 'X' FROM EMP WHERE DEPT.DEPTNO = EMP.DEPTNO)
-->
SELECT DEPTNO FROM DEPTWHERE DEPTNO IN (SELECT DEPTNO FROM EMP)
=============================================
subquery SELECT, FROM, WHERE 절에 모두 사용가능
================ JOIN =======================
- INNER JOIN (여러 테이블의 key 값이 일치하는 행에 대해서만 적용)SELECT [column] FROM [table 1] INNER JOIN [table 2] ON [JOIN CONDITION 1] AND [JOIN CONDITION 2]
부서위치가 DALLAS인 모든 사원의 이름, 부서번호를 출력.
SELECT e.ENAME, d.DEPTNO, d.LOC FROM EMP e INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO WHERE d.LOC = 'DALLAS'
- [LEFT/RIGHT] OUTER JOIN (LEFT/RIGHT 를 기준으로 JOIN 기준 테이블의 모든 key 값에 대해 적용)
- NON_Equi JOIN (CROSS JOIN)두 테이블의 같은 column 을 사용하지 않고 join
사원의 이름, 급여, 급여의 등급을 표시SELECT e.ENAME, e.SAL, g.GRADE FROM EMP e, SALGRADE g WHERE e.SAL BETWEEN g.LOSAL AND g.HISAL
================ inline view =====================================
-> 실제 존재하지 않는 테이블이지만 연산을 위해 가상으로 생성
부서평균연봉보다 많이 받는 사원의 이름과 연봉, 부서평균연봉을 조회
SELECT ENAME, SAL, DEPT_AVG FROM EMP INNER JOIN (SELECT DEPTNO, round(AVG(SAL)) AS DEPT_AVG FROM EMP GROUP BY DEPTNO) DEPT ON EMP.DEPTNO = DEPT.DEPTNO WHERE SAL > DEPT_AVG
============================FUNCTION==============================
DECODE - Oracle 함수 ( = CASE WHEN)
DECODE(column_name, value,
CLERK -> GOOD / ANALYST -> NOT GOOD / BAD
SELECT JOB, DECODE(JOB, 'CLERK', 'GOOD', DECODE(JOB, 'ANALYST', 'NOT GOOD', 'BAD')) as test FROM EMP
-->
SELECT JOB, CASE JOB WHEN 'CLERK' THEN 'GOOD' WHEN 'ANALYST' THEN 'NOT GOOD' ELSE 'BAD' END as test FROM EMP
2000까지 A 4000 까지 B 나머지 C
SELECT GRADE, LOSAL, HISAL, CASE WHEN HISAL< salgrade ="=" emp ="=" 1="2" dept =" 10" table_name =" 'DOYOUNG'"> table_name
====================== sequence ==================================
연속적인 숫자값을 자동으로 증가/감소 시켜야 할 경우 자동으로 숫자를 발생시키는 객체 즉, 시퀀스를 생성한 후 호출만하면 연속적으로 번호를 (오라클에서) 증가/감소시켜 제공해 준다. CREATE SEQUENCE [시퀀스명] [INCREMENT BY n] [START WITH n] [MAXVALUE n NOMAXVALUE] [MINVALUE n NOMINVALUE] [CYCLE NOCYCLE] [CACHE NOCACHE]
생성 : CREATE SEQUENCE EMP_seq START WITH 3300 INCREMENT BY 100 MAXVALUE 9900 NOCHACHE NOCYCLE
확인 : SELECT * FROM USER_SEQUENCES삭제 : DROP SEQUENCE sequence_name
함수: sequence_name.nextval sequence_name.currval
======================== 여러 테이블 조인 ===================================
-LOCATIONS, DEPARTMENTS, JOBS, EMPLOYEES 테이블 조인
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, J.JOB_TITLE, L.CITY FROM LOCATIONS L, DEPARTMENTS D, JOBS J, EMPLOYEES E WHERE L.LOCATION_ID = D.LOCATION_ID AND E.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.JOB_ID = J.JOB_ID
->
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, J.JOB_TITLE, L.CITY FROM LOCATIONS L INNER JOIN DEPARTMENTS D ON L.LOCATION_ID = D.LOCATION_ID INNER JOIN EMPLOYEES E ON E.DEPARTMENT_ID = D.DEPARTMENT_ID INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID
- IN 절로 EMPLOYEES 테이블과 DEPARTMENT 테이블을 조인
- employee 테이블을 조회하면서 부서이름을 조회하고 싶은 경우 스칼라 서브쿼리 사용 작성
SELECT E.FIRST_NAME, (SELECT D.DEPARTMENT_NAME FROM DEPARTMENTS D WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID) DEPARTMENT_NAME FROM EMPLOYEES E
--> OUTER JOIN과 동일
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D. DEPARTMENT_ID
========================== VIEW =============================
- 부서별 평균연봉 뷰
CREATE VIEW DEPT_AVG_SAL_VIEW AS SELECT D.DEPTNO, D.DNAME, E.SAL_AVG FROM DEPT D, (SELECT DEPTNO, AVG(SAL) SAL_AVG FROM EMP GROUP BY DEPTNO) E
- 뷰를 이용해서 부서의 평균 급여보다 적은 급여를 받는 사원 조회
SELECT E.ENAME, E.SAL, V.SAL_AVG FROM EMP E INNER JOIN DEPT_AVG_SAL_VIEW V ON E.DEPTNO = V.DEPTNO WHERE E.SAL < sal_avg ="=" index ="=" index_name =" ix.index_name" table_name =" 'EMP'" index_name ="=" recursive ="=" empno =" MGR" mgr =" prior">
