본문 바로가기

분류 전체보기78

[SQL]시퀀스 만들기(생성,확인,실행,수정,삭제,동의어) /*시퀀스 만들기*/ CREATE SEQUENCE SEQ_DEPT_SEQUENCE INCREMENT BY 10 START WITH 10 MAXVALUE 90 MINVALUE 0 NOCYCLE CACHE 2; SELECT * FROM USER_SEQUENCES; INSERT INTO DEPT_SEQUENCE (DEPTNO,DNAME,LOC) VALUES (SEQ_DEPT_SEQUENCE.NEXTVAL,'DATABASE','SEOUL'); SELECT * FROM DEPT_SEQUENCE ORDER BY DEPTNO; /*가장 마지막으로 생성된 시퀀스 확인*/ SELECT SEQ_DEPT_SEQUENCE.CURRVAL FROM DUAL; /*순번을 반복사용하여 INSERT문 실행*/ INSERT INTO D.. 2020. 6. 11.
[SQL]ROLLBACK,세션,RENAME,MODIFY,INDEX(생성,삭제),VIEW,인라인뷰(WITH절) /*01.삽입*/ INSERT INTO DEPT_TCL VALUES (50,'DATABASE','SEOUL'); /*02.수정*/ UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40; /*03.삭제*/ DELETE FROM DEPT_TCL WHERE DNAME='RESEARCH'; COMMIT; ROLLBACK; /*P277 테이블에 있는 데이터 수정하기*/ SELECT * FROM DEPT_TEMP2; /* UPDATE SET */ UPDATE DEPT_TEMP2 SET LOC='SEOUL'; /* 수정작업을 되돌리고 싶을 때 ROLLBACK*/ ROLLBACK; /* DELETE FROM */ SELECT * FROM EMP_TEMP2; DELETE FROM .. 2020. 6. 11.
[SQL]서브쿼리(IN,ANY,SOME ALL,EXISTS ) /* 제임스의 급여보다 큰 사원 출력*/ SELECT *FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME = 'JONES'); SELECT * FROM EMP WHERE COMM>(SELECT COMM FROM EMP WHERE ENAME='ALLEN'); /*단일 행 서브 쿼리*/ SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DEPTNO,D.DNAME,D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO = 20 AND SAL>(SELECT AVG(SAL) FROM EMP); /*238P 1분 복습*/ SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DEPTNO,D.. 2020. 6. 11.
[SQL]조인(JOIN) /*JOIN*/ /* 일반적 표현 ,물리적으로 실제로 존재하는 테이블 */ SELECT * FROM EMP; SELECT * FROM DEPT; /*두 테이블을 곱*/ SELECT * FROM EMP,DEPT; SELECT COUNT(*) FROM DEPT; /* JOIN 문 (별칭도 가능) */ SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO; /* 등가 조인 */ SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND SAL>=3000; /* 비등가 조인 */ SELECT * FROM EMP E, SALGRADE S WHERE E.SAL BETWEE.. 2020. 6. 11.

티스토리툴바