노마드오브

SELECT * FROM TAB; // 테이블 목록보기

SELECT SUBSTR('동해물과',2,1) FROM DUAL; DESC DUAL; 오라클에서 DUAL 이라는 테이블 제공.

SELECT * FROM ORCL_BRD_RPL START WITH BRD_PNO = 0 -- 시작형 CONNECT BY PRIOR BRD_NO = BRD_PNO -- 연결 ORDER SIBLINGS BY BRD_NO DESC; -- 정렬순서 *계층형 쿼리 SELECT * FROM ORCL_BRD_RPL START WITH BRD_PNO = 0 -- 시작형 CONNECT BY PRIOR BRD_NO = BRD_PNO -- 연결 ORDER SIBLINGS BY BRD_NO DESC; -- 정렬순서 *ROWNUM으로 순번 부여 및 ROWNUM 조건 사용 SELECT * FROM ( SELECT ROWNUM AS RNUM, E.* FROM (SELECT * FROM EMP ORDER BY ENAME ASC) ..

* MYSQL에서는 LIMIT이지만, 오라클에서는 ROWNUM SELECT ROWNUM, E.* FROM EMP E ORDER BY E.SAL DESC; -- CTRL + ENTER : 쿼리실행 SELECT ROWNUM, T.* FROM (SELECT * FROM EMP ORDER BY SAL DESC) T; SELECT * FROM (SELECT ROWNUM AS RNUM, T.* FROM (SELECT * FROM EMP ORDER BY SAL DESC) T) WHERE RNUM >=5 AND RNUM

* oracle 셋팅 1. oracle.com 오라클 다운로드 2. oracle developer 툴 다운로드 cmd 창 sqlplus sys as sysdba CREATE USER SCOTT IDENTIFIED BY TIGER; GRANT RESOURCE, CONNECT TO SCOTT;

SELECT SUBSTR(Ename, 1, 1) FROM EMP order by ename ;SELECT * FROM EMP ;SELECT * FROM EMP where SUBSTR(Ename, 1, 1) = 'A' order by ename; SELECT SUBSTR('HELLO JAVA', 7, 2); SELECT * FROM DEPT; SELECT * FROM EMP, DEPTWHERE JOB IN (SELECT JOB FROM EMP WHERE DEPTNO = 20)AND EMP.DEPTNO = 10AND EMP.DEPTNO = DEPT.DEPTNO; ---- CREATE TABLE 학과 ( 학과번호 INT, 학과명 VARCHAR(20),PRIMARY KEY (학과번호) ); SELECT * FR..

SELECT * FROM 제품; SELECT 제조업체 FROM 제품 WHERE 제품명 = '매운쫄면'; SELECT *, (SELECT COUNT(*) FROM 제품) FROM 제품 WHERE 제조업체 = (SELECT 제조업체 FROM 제품 WHERE 제품명 = '매운쫄면'); SELECT *, (SELECT ENAME FROM EMP WHERE EMPNO = E.MGR) FROM EMP E; SELECT ENAME, DEPTNO, (SELECT DNAME FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO) FROM EMP; SELECT * FROM 주문; SELECT * FROM 주문 WHERE 주문고객 = 'banana'; SELECT * FROM 제품 WHERE 제품번호 I..

SELECT * FROM 주문, 제품 WHERE 주문고객 = 'banana' AND 주문.`주문제품` AND 제품.`제품번호`; SELECT O.주문제품, O.주문일자 FROM 고객 AS C, 주문 AS O WHERE C.나이 >= 30 AND C.고객아이디 = O.주문고객; SELECT 고객.`고객이름`, 주문.`주문일자`, 제품.`제품명` FROM 고객, 주문,제품 WHERE 고객.`고객아이디` = 주문.`주문고객` AND 주문.`주문제품` = 제품.`제품번호` AND 고객.`나이` >= 30; SELECT P.제품명 FROM 고객 AS C, 주문 AS O, 제품 AS P WHERE C.고객이름 = '고명석' AND O.주문고객 = C.고객아이디 AND O.주문제품 = P.제품번호; SELECT C...

SELECT AVG(단가) FROM 제품;SELECT COUNT(제조업체) FROM 제품; SELECT COUNT(DISTINCT 제조업체) FROM 제품; SELECT * FROM EMP; SELECT COUNT(*) FROM EMP; SELECT COUNT(*) FROM EMP WHERE MGR IS NOT NULL; SELECT SUM(SAL) FROM EMP WHERE SAL = 50; SELECT 제조업체, COUNT(*) , MAX(단가) FROM 제품 GROUP BY 제조업체; SELECT 주문제품, 주문고객, COUNT(*) FROM 주문 GROUP BY 주문제품, 주문고객; SELECT 주문제품, COUNT(*) FROM 주문 GROUP BY 주문제품; -------------------..

SET @@AUTOCOMMit = 0; // 하이디 sql 툴은 autocommit하기 때문에. -- DDL / DML / DCL /* DCL */ commit; rollback;