sqlplus "/as sysdba" --dbopen
startup -- 인스턴스 가동
---사용자 scott 로 이동
conn scott/tiger
select * from tab;
re:
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
-- 컬럼 내용 확인
desc emp;
결과:
이름 널? 유형
----------------------------------------- -------- --------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
-------------계층쿼리 표시(ex)
select rownum,level,empno,ename,job, mgr from emp;
결과 :
ROWNUM LEVEL EMPNO ENAME JOB MGR
------- ---------- ---------- ---------- --------- ----------
1 0 7369 SMITH CLERK 7902
2 0 7499 ALLEN SALESMAN 7698
3 0 7521 WARD SALESMAN 7698
4 0 7566 JONES MANAGER 7839
5 0 7654 MARTIN SALESMAN 7698
6 0 7698 BLAKE MANAGER 7839
7 0 7782 CLARK MANAGER 7839
8 0 7788 SCOTT ANALYST 7566
9 0 7839 KING PRESIDENT
10 0 7844 TURNER SALESMAN 7698
11 0 7876 ADAMS CLERK 7788
ROWNUM LEVEL EMPNO ENAME JOB MGR
------- ---------- ---------- ---------- --------- ----------
12 0 7900 JAMES CLERK 7698
13 0 7902 FORD ANALYST 7566
14 0 7934 MILLER CLERK 7782
-- 레벨 표시 mgr(직급상사) empno(사원번호) 직급관계 표시
select level,empno,ename,job,mgr from emp start with ename like 'KING'
connect by prior empno=mgr;
결과:
LEVEL EMPNO ENAME JOB MGR
------ ---------- ---------- --------- ----------
1 7839 KING PRESIDENT
2 7566 JONES MANAGER 7839
3 7788 SCOTT ANALYST 7566
4 7876 ADAMS CLERK 7788
3 7902 FORD ANALYST 7566
4 7369 SMITH CLERK 7902
2 7698 BLAKE MANAGER 7839
3 7499 ALLEN SALESMAN 7698
3 7521 WARD SALESMAN 7698
3 7654 MARTIN SALESMAN 7698
3 7844 TURNER SALESMAN 7698
LEVEL EMPNO ENAME JOB MGR
------ ---------- ---------- --------- ----------
3 7900 JAMES CLERK 7698
2 7782 CLARK MANAGER 7839
3 7934 MILLER CLERK 7782
-- lpad(' ',level*3)|| ename
select level,empno,ename as lvl ,job,mgr from emp start with ename like 'KING'
connect by prior empno=mgr;
결과:
LEVEL EMPNO LVL JOB MGR
------- ---------- ------------------------------ --------- ----------
1 7839 KING PRESIDENT
2 7566 JONES MANAGER 7839
3 7788 SCOTT ANALYST 7566
4 7876 ADAMS CLERK 7788
3 7902 FORD ANALYST 7566
4 7369 SMITH CLERK 7902
2 7698 BLAKE MANAGER 7839
3 7499 ALLEN SALESMAN 7698
3 7521 WARD SALESMAN 7698
3 7654 MARTIN SALESMAN 7698
3 7844 TURNER SALESMAN 7698
LEVEL EMPNO LVL JOB MGR
------- ---------- ------------------------------ --------- ----------
3 7900 JAMES CLERK 7698
2 7782 CLARK MANAGER 7839
3 7934 MILLER CLERK 7782
--sql환경에 관한 명령어라 저장이 안됨(출력이 정렬되어서 나옴)
col lvl format a30
col job format a10
col mgr format a10
col mgr format 99999
--------------------
set linesize 100
-- lpad(' ',level*3) 3칸뛰우고 정렬들어감 lpad(' ',level*2) 2칸 뛰우고 정렬.
select lpad(' ',level*3)|| ename as lvl,job,mgr from emp start with ename like 'KING'
connect by prior empno=mgr;
결과 :
LVL JOB MGR
------------------------------ ---------- ----------
KING PRESIDENT
JONES MANAGER ##########
SCOTT ANALYST ##########
ADAMS CLERK ##########
FORD ANALYST ##########
SMITH CLERK ##########
BLAKE MANAGER ##########
ALLEN SALESMAN ##########
WARD SALESMAN ##########
MARTIN SALESMAN ##########
TURNER SALESMAN ##########
LVL JOB MGR
------------------------------ ---------- ----------
JAMES CLERK ##########
CLARK MANAGER ##########
MILLER CLERK ##########
---- 숫자형식은 고쳐줌
col mgr format 99999 -- 숫자 형식 표현
/ -- 이전 명령어 다시 수행
결과:
LVL JOB MGR
------------------------------ ---------- ------
KING PRESIDENT
JONES MANAGER 7839
SCOTT ANALYST 7566
ADAMS CLERK 7788
FORD ANALYST 7566
SMITH CLERK 7902
BLAKE MANAGER 7839
ALLEN SALESMAN 7698
WARD SALESMAN 7698
MARTIN SALESMAN 7698
TURNER SALESMAN 7698
LVL JOB MGR
------------------------------ ---------- ------
JAMES CLERK 7698
CLARK MANAGER 7839
MILLER CLERK 7782
----BLAKE 사람으로 시작 검색
select lpad(' ',level*3)|| ename as lvl,job,mgr from emp start with ename like 'BLAKE'
re:
LVL JOB MGR
------------------------------ ---------- ------
BLAKE MANAGER 7839
ALLEN SALESMAN 7698
WARD SALESMAN 7698
MARTIN SALESMAN 7698
TURNER SALESMAN 7698
JAMES CLERK 7698
------
select lpad(' ',level*3)|| ename as lvl,job,empno,mgr from emp start with ename like 'KING'
connect by prior empno=mgr;
re:
LVL JOB EMPNO MGR
------------------------------ ---------- ---------- ------
KING PRESIDENT 7839
JONES MANAGER 7566 7839
SCOTT ANALYST 7788 7566
ADAMS CLERK 7876 7788
FORD ANALYST 7902 7566
SMITH CLERK 7369 7902
BLAKE MANAGER 7698 7839
ALLEN SALESMAN 7499 7698
WARD SALESMAN 7521 7698
MARTIN SALESMAN 7654 7698
TURNER SALESMAN 7844 7698
LVL JOB EMPNO MGR
------------------------------ ---------- ---------- ------
JAMES CLERK 7900 7698
CLARK MANAGER 7782 7839
MILLER CLERK 7934 7782
---
select lpad(' ',level*3)|| ename as lvl,job,empno,mgr from emp start with ename
like 'KING'
connect by prior mgr=empno;
re:
LVL JOB EMPNO MGR
------------------------------ ---------- ---------- ------
KING PRESIDENT 7839
------
select lpad(' ',level*3)|| ename as lvl,job,empno,mgr from emp start with ename like 'BLAKE'
connect by prior mgr=empno;
re:
LVL JOB EMPNO MGR
------------------------------ ---------- ---------- ------
BLAKE MANAGER 7698 7839
KING PRESIDENT 7839
---가지치기
--ford 가 아닌 사람 검색
select lpad(' ',level*3)|| ename as lvl,job,empno,mgr from emp start with enme like 'KING'
connect by prior empno=mgr and ename <>'FORD';
re:
LVL JOB EMPNO MGR
------------------------------ ---------- ---------- ------
KING PRESIDENT 7839
JONES MANAGER 7566 7839
SCOTT ANALYST 7788 7566
ADAMS CLERK 7876 7788
BLAKE MANAGER 7698 7839
ALLEN SALESMAN 7499 7698
WARD SALESMAN 7521 7698
MARTIN SALESMAN 7654 7698
TURNER SALESMAN 7844 7698
JAMES CLERK 7900 7698
CLARK MANAGER 7782 7839
LVL JOB EMPNO MGR
------------------------------ ---------- ---------- ------
MILLER CLERK 7934 7782
================
conn ejb/ejb
select * from personnel;
---
select sysdate from dual;
결과:
SYSDATE
--------
05/03/24
--행의 갯수만큼 나옴
select sysdate from personnel;
결과:
SYSDATE
--------
05/03/24
05/03/24
05/03/24
05/03/24
05/03/24
05/03/24
05/03/24
05/03/24
05/03/24
05/03/24
-------
select startdate from personnel;
결과:
STARTDAT
--------
90/12/17
91/02/20
92/02/24
90/04/12
89/01/10
91/05/01
91/09/09
90/12/08
91/09/23
90/01/03
-- 시간 형식 바꾸기
alter session set nls_date_format='yyyy-mm-dd hh:mi:ss';
select sysdate from dual;
결과:
SYSDATE
-------------------
2005-03-24 05:51:04
----
alter session set nls_date_format='yyyy-mm-dd hh:mi:ss bc';
select sysdate from dual;
결과:
SYSDATE
--------------------------
2005-03-24 05:52:18 서기
-------------------------
alter session set nls_date_format='yyyy-mm-dd hh:mi:ss am';
select sysdate from dual;
결과:
SYSDATE
------------------------
2005-03-24 05:52:56 오후
----------
alter session set nls_date_format='month "of" day';
select sysdate from dual;
결과:
SYSDATE
-------------------
march of 목요일
---------------------
alter session set nls_date_format='rr-mm-dd';
select sysdate from dual;
결과:
SYSDATE
--------
05-03-24
'ORACLE' 카테고리의 다른 글
| 오라클 연산/집합 (0) | 2024.01.16 |
|---|---|
| 오라클 문자열 검사 (2) | 2024.01.16 |
| 오라클 사용자 접속/ 테이블 생성/ 데이터 삽입/ 라인조절/ 중복제거/ 찾기 (0) | 2024.01.16 |
| 오라클 Database 생성 (0) | 2024.01.16 |
| 오라클 Database 생성 (0) | 2018.12.08 |