오라클 접속 및 구동
오라클 인스턴스 상태 조회
# 오라클 인스턴스 상태 조회
SQL> select status from v$instance;
# 결과값:
STATUS
------------------------
OPEN
# 오라클 서버가 정상적으로 startup 되었음을 의미
일반 사용자 계정인 『hr』로 접속 시도
# 두 번째로... 일반 사용자 계정인 『hr』로 접속 시도
SQL> connect hr/lion
# 결과값
ERROR:
ORA-28000: the account is locked
# 설명
# 일반 사용자 계정인 『hr』은 잠겨있는 상태이므로 오라클 서버 접속이 불가능한 상태
Warning: You are no longer connected to ORACLE.
# 기존에 sys 계정으로 연결되어 있었으나 유효하지 않은 연결 시도로 인해 기존 연결까지 해제되는 상황
현재 오라클 서버에 존재하는 사용자 계정에 대한 정보 조회
# 현재 오라클 서버에 존재하는 사용자 계정에 대한 정보 조회
SQL> set linesize 600;
SQL> select username, account_status from dba_users;
# 결과값
USERNAME ACCOUNT_STATUS
------------------------------------------------------------ --------------------------------
--------------------------------
SYS OPEN
SYSTEM OPEN
ANONYMOUS OPEN
APEX_PUBLIC_USER LOCKED
FLOWS_FILES LOCKED
APEX_040000 LOCKED
OUTLN EXPIRED & LOCKED
DIP EXPIRED & LOCKED
ORACLE_OCM EXPIRED & LOCKED
XS$NULL EXPIRED & LOCKED
MDSYS EXPIRED & LOCKED
USERNAME ACCOUNT_STATUS
------------------------------------------------------------ --------------------------------
--------------------------------
CTXSYS EXPIRED & LOCKED
DBSNMP EXPIRED & LOCKED
XDB EXPIRED & LOCKED
APPQOSSYS EXPIRED & LOCKED
HR EXPIRED & LOCKED
16 rows selected.
# 설명
# 위의 조회 구문을 통해 hr 계정이 존재하지만 유효기간만료 & 잠겨있음을 확인할 수 있다.
※ host 명령어
- 도스 명령 체계로 전환하거나 한 칸 띄운 다음 도스 명령 입력이 가능하다.
- 예를 들면 『host dir』, 『host cls』, 『host ipconfig』등...
- 유닉스 계역에서는 『host』명령어 뿐만 아니라 『!』도 사용 가능.
- 하지만, 윈도우 계역에서는 『host』 명령어만 사용 가능하다.
hr 계정 잠금 해제 (sys로 연결된 상태)
# hr 계정 잠금 해제 (sys로 연결된 상태)
SQL> alter user hr account unlock;
# 결과값
User altered.
계정 잠금이 해제된 hr 계정으로 다시 접속 시도
# 계정 잠금이 해제된 hr 계정으로 다시 접속 시도
SQL> conn hr/lion
# 결과값
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
계정 정보 변경(sys로 연결) → 패스워드 설정 변경
# 계정 정보 변경(sys로 연결) → 패스워드 설정 변경
SQL> alter user hr identified by lion;
# 결과 값
User altered.
패스워드를 유효하게 설정한 이후 hr 계정으로 다시 접속 시도
# 패스워드를 유효하게 설정한 이후 hr 계정으로 다시 접속 시도
SQL> conn hr/lion
# 결과 값
Connected.
# →확인
SQL> show user
# 결과 값
USER is "HR"
오라클 서버에 존재하는 계정 정보 조회 구문 실행
# 오라클 서비에 존재하는 계정 정보 조회 구문 실행
SQL> select username, account_status from dba_users;
# 결과 값
ERROR at line 1:
ORA-00942: table or view does not exist
Oracle에서 주석처리
--1줄 주석문 처리
/*
여러줄
(다중행)
주석문
처리
*/
--1줄 주석문 처리
/*
여러줄
(다중행)
주석문
처리
*/
실습
Oracle SQL Developer 프로그램 사용
새로 만들기/데이터베이스 접속
환경설정
20180105_sys.sql
SELECT USER
FROM DUAL;
--==>>SCOTT
테이블 생성 (DEPT)
--○ 테이블 생성 (DEPT)
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY
, DNAME VARCHAR2(14)
, LOC VARCHAR2(13)
);
--==>>Table DEPT이(가) 생성되었습니다.
확인
--○ 확인
SELECT *
FROM DEPT;
테이블 생성 (EMP)
--○ 테이블 생성 (EMP)
CREATE TABLE EMP
( EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY
, ENAME VARCHAR2(10)
, JOB VARCHAR2(9)
, MGR NUMBR(4)
, HIREDATE DATE
, SAL NUMBER(7,2)
, COMM NUMBER(7,2)
, DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
--==>>Table EMP이(가) 생성되었습니다.
확인
--○ 확인
SELECT *
FROM EMP;
데이터 입력 (DEPT)
--○ 데이터 입력 (DEPT)
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
--==>> 4 행 이(가) 삽입되었습니다.
데이터 입력 (EMP)
--○ 데이터 입력 (EMP)
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-7-1987','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
--==>> 14 행 이(가) 삽입되었습니다.
테이블 생성 (BONUS)
--○ 테이블 생성 (BONUS)
CREATE TABLE BONUS
(ENAME VARCHAR2(10)
, JOB VARCHAR2(9)
, SAL NUMBER
, COMM NUMBER
) ;
--==>>Table BONUS이(가) 생성되었습니다.
테이블 생성 (SALGRADE)
--○ 테이블 생성 (SALGRADE)
CREATE TABLE SALGRADE
( GRADE NUMBER
, LOSAL NUMBER
, HISAL NUMBER
);
--==>>Table SALGRADE이(가) 생성되었습니다.
데이터 입력 (SALGRADE)
--○ 데이터 입력 (SALGRADE)
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
--==>> 5 행 이(가) 삽입되었습니다.
데이터를 하드디스크에 올리는 것을 확정하는 명령
--○ 데이터를 하드디스크에 올리는 것을 확정하는 명령
COMMIT;
--==>>커밋 완료.
-- ROLLBACK;
현재 SCOTT 계정이 소유하고 있는 테이블 확인(조회)
--○ 현재 SCOTT 계정이 소유하고 있는 테이블 확인(조회)
SELECT *
FROM TAB;
--==>>
/*
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
*/
※※※※※ 중요 ※※※※※
SELECT 문의 처리(PARSING) 순서
SELECT 문의 처리(PARSING) 순서
→ 쿼리문 작성하는 순서
SELECT 컬럼명 --⑤
FROM 테이블명 --① 만족하는 테이블
WHERE 조건절 --② 만족하는 행만 찾아서 메모리
GROUP BY 절 --③
HAVING 조건절 --④ 그룹의 조건절
ORDER BY --⑥ 정렬, 부하가 나기 때문에 프로젝트에서 제한되는 경우가 있다보너스 테이블 조회
--○ 보너스 테이블 조회
SELECT *
FROM BONUS;
부서정보 테이블 조회
--○ 부서정보 테이블 조회
SELECT *
FROM DEPT;
사원정보 테이블 조회
--○ 사원정보 테이블 조회
SELECT *
FROM EMP;
급여등급 테이블 조회
--○ 급여등급 테이블 조회
SELECT *
FROM SALGRADE;
테이블의 구조 확인
--○ 테이블의 구조 확인
DESCRIBE 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)
*/
주요 자료형
숫자형
- NUMBER
- NUMBER(3) : -999~999
- NUMBER(4,1) : 7.392 전체가 4자리 중 정수가 1자리
문자형
- 고정형 크기
- CHAR
- CHAR(10) -- 10BYTE '졸려도참자' 10은 바이트 수
- CHAR(10) ← '명소희' 6바이트지만 고정형이므로 10바이트 소모
- CHAR
- CHAR(10) -- 10BYTE '졸려도참자' 10은 바이트 수
- CHAR(10) ← '명소희' 6바이트지만 고정형이므로 10바이트 소모
전화번호, 주민번화, 학번은 숫자타입으로 저장하면 안된다! 왜나햐면 맨 앞의 수가 0이면 탈락되기 때문이다.
- 가변형(자료형 헤더에 들어가야함 몇바이트를 사용하는 지 알기 위한 기능이 있기 때문에 자료형이 비쌈)
- VARCHAR2
- VARCHAR2(10) -- 10 BYTE '졸리면자자'
- VARCHAR2(10) ← '자면 안되나요?' 10BYTE 를 초과하므로 입력 불가.VARCHAR2(10) ← '물먹자' 6BYTE 를 소모
앞에 N이 붙게되면 유니코드 고정/가변형 문자데이터 타입
날짜형
- DATE
SELECT SYSDATE
FROM DUAL;
--==>18/01/05
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--SESSION 어딘가에 담아둔 정보, 지금 연결에 있어서는 이 포멧으로 보여주라
SELECT SYSDATE
FROM DUAL;
--==>>2018-01-05 14:52:11
SELECT 01092836776
FROM DUAL;
--==>1092836776
SELECT '01092836776'
FROM DUAL;
--==>>01092836776
SELECT SYSDATE
FROM DUAL;
--==>18/01/05
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--SESSION 어딘가에 담아둔 정보, 지금 연결에 있어서는 이 포멧으로 보여주라
SELECT SYSDATE
FROM DUAL;
--==>>2018-01-05 14:52:11
SELECT 01092836776
FROM DUAL;
--==>1092836776
SELECT '01092836776'
FROM DUAL;
--==>>01092836776
EMP 테이블에서 부서번호가 20번인 사원들의 정보 중 사원번호, 사원명, 급여, 직종명, 부서번호 조회
SELECT EMPNO, ENAME, SAL, JOB, DEPTNO
FROM EMP
WHERE DEPTNO=20;
-- =은 관계 연산자
--한글로 먼저 써보고 SQL문으로 변환하자
--==>>
/*
7369 SMITH 800 CLERK 20
7566 JONES 2975 MANAGER 20
7788 SCOTT 3000 ANALYST 20
7876 ADAMS 1100 CLERK 20
7902 FORD 3000 ANALYST 20
*/
--※ 각 컬럼에 별칭을 부여할 수 있다.
-- 컬럼의 별칭 alias엘리어스
SELECT EMPNO AS "사원번호", ENAME "사원명", SAL 급여, JOB "직종 명", DEPTNO
FROM EMP
WHERE DEPTNO=20;
-- AS를 사용하면 원본 데이터가 변하는 것은 아니다. 보여질 때만
EMP 테이블에서 부서번호가 20번과 30번 직원들의 정보 중 사원번호, 사원명, 직종명, 급여, 부서번호 항목을 조회한다.
SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", DEPTNO "부서번호"
FROM EMP
WHERE DEPTNO=20 , DEPTNO = 30;
-- 단일조건이아니라 다중조건(부서번호가 20번과 30번 직원)이라면 논리연산자가 등장해야한다
SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", DEPTNO "부서번호"
FROM EMP
WHERE DEPTNO=20 OR DEPTNO = 30;
-- ||는 문자열 결합 연산자다
SELECT EMPNO "사원번호", ENAME "사원명", SAL "급여", DEPTNO "부서번호"
FROM EMP
WHERE DEPTNO IN (20, 30);
EMP 테이블에서 직종이 CLERK 인 사원들의 정보를 모두 조회한다.
SELECT *
FROM EMP
WHERE JOB = 'CLERK';
--※ 오라클에서 ... 입력된 데이터(값) 만큼은 반.드.시. 대소문자 구분을 엄격하게 한다.
테이블 복사
--○ 테이블 복사
--> 내부적으로 대상 테이블 안에 들어있는 데이터 내용만 복사하는 과정
CREATE TABLE TBL_EMP
AS
SELECT *
FROM EMP;
--==>>Table TBL_EMP이(가) 생성되었습니다.
SELECT *
FROM TBL_EMP;
--==>>
/*
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-07-13 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-07-13 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
*/
이미 만들어진 테이블에 컬럼을 추가하거나 제거하는 방법
--○ 이미 만들어진 테이블에 컬럼을 추가하거나 제거하는 방법
-- TBL_EMP 테이블에 주민번호정보를 담을 수 있는 컬럼(SSN) 추가
ALTER TABLE TBL_EMP
ADD SSN CHAR(13);
--==>Table TBL_EMP이(가) 변경되었습니다.
-- DML INSERT/UPDATE/DELETE COMMIT을 받드시 행해야한다. 일단은 AUTO COMMIT
-- 그렇기 때문에 주의해야한다.
-- 컬럼이 추가된 테이블 구조 확인
DESC TBL_EMP;
--==>>
/*
이름 널 유형
-------- - ------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SSN CHAR(13)
*/
SELECT *
FROM TBL_EMP;
SELECT EMPNO, ENAME, SSN
FROM TBL_EMP;
-- 컬럼 순서는 무의미하다
TBL_EMP 테이블에 추가한 SSN(주민번호) 칼럼을 구조적으로 제거
--○ TBL_EMP 테이블에 추가한 SSN(주민번호) 칼럼을 구조적으로 제거
ALTER TABLE TBL_EMP
DROP COLUMN SSN;
--==>>Table TBL_EMP이(가) 변경되었습니다.
SELECT *
FROM TBL_EMP;
SELECT *
FROM TBL_EMP
WHERE EMPNO='7369';
DELETE
FROM TBL_EMP
WHERE EMPNO='7369';
--==>1 행 이(가) 삭제되었습니다.
DROP TABLE TBL_EMP;
SELECT *
FROM TAB;
NULL의 처리
--○ NULL의 처리
SELECT 2, 1+2, 3-1, 2*10, 10/2
FROM DUAL;
--==>>2 3 2 20 5
SELECT 2+NULL, 2-NULL, 2*NULL, 2/NULL
FROM DUAL;
--==>> (NULL) (NULL) (NULL) (NULL)
--※ NULL 은... 현실에 존재하는 특정한 값을 의미하는 것이 아니라 아무것도 들어있지 않은 상태를 의미하는 값이므로 NULL 을 대상으로 연산을 수행할 경우... 또는 특정 연산 과정에 NULL이 포함될 경우 결과는 무조건 NULL이다.
다시 EMP 테이블을 복사하여 TBL_EMP 테이블 생성
--○ 다시 EMP 테이블을 복사하여 TBL_EMP 테이블 생성
CREATE TABLE TBL_EMP
AS
SELECT *
FROM EMP;
--==>>Table TBL_EMP이(가) 생성되었습니다.
TBL_EMP 테이블에서 커미션(COMM) → 수당이 NULL 인 사원들의 사원명, 직종명, 급여, 커미션 조회
--○ TBL_EMP 테이블에서 커미션(COMM) → 수당이 NULL 인 사원들의 사원명, 직종명, 급여, 커미션을 조회한다.
SELECT ENAME "사원명", JOB"직종명", SAL"급여", COMM"커미션"
FROM TBL_EMP
WHERE COMM IS NULL;
--NULL은 관계연산자로 비교할 수 없다
--※ NULL 은 값으로 존재하는 것이 아니기 때문에 값으로 비교할 수 없다.
-- =, >=, <=, >, <, !=, <>, ^= 와 같은 연산자를 사용할 수 없다.
TBL_EMP 테이블에서 20번 부서에 근무하지 않는 사원들의 정보를 사원번호, 사원명, 부서번호로 조회
--○ TBL_EMP 테이블에서 20번 부서에 근무하지 않는 사원들의 정보를 사원번호, 사원명, 부서번호로 조회한다.
SELECT EMPNO, ENAME, DEPTNO
FROM TBL_EMP
WHERE DEPTNO != 20;
SELECT EMPNO, ENAME, DEPTNO
FROM TBL_EMP
WHERE DEPTNO <> 20;
SELECT EMPNO, ENAME, DEPTNO
FROM TBL_EMP
WHERE DEPTNO ^= 20;
SELECT EMPNO, ENAME, DEPTNO
FROM TBL_EMP
WHERE NOT DEPTNO = 20;
SELECT ENAME "사원명", JOB"직종명", SAL"급여", COMM"커미션"
FROM TBL_EMP
WHERE NOT COMM IS NULL;
SELECT ENAME "사원명", JOB"직종명", SAL"급여", COMM"커미션"
FROM TBL_EMP
WHERE COMM IS NOT NULL;
TBL_EMP 테이블에서 모든 사원들의 사원번호, 사원명, 급여, 커미션, 연봉 정보를 조회
--○ TBL_EMP 테이블에서 모든 사원들의
-- 사원번호, 사원명, 급여, 커미션, 연봉 정보를 조회한다.
-- 단, 급여(SAL)는 매월 지급한다.
-- 수당(COMM)은 1회/년 지급한다.
-- 연봉 = (급여 * 12) + 커미션
SELECT EMPNO "사원번호", ENAME "사원명", SAL"급여", COMM"커미션", (SAL*12)+COMM "연봉정보"
FROM TBL_EMP;
SELECT EMPNO "사원번호", ENAME "사원명", SAL"급여", COMM"커미션", (SAL*12+COMM) "연봉정보"
FROM TBL_EMP;
'교육 및 세미나 > 신입사원 교육' 카테고리의 다른 글
| 6일차(2018.01.10) (0) | 2018.01.12 |
|---|---|
| 5일차(2018.01.09) (0) | 2018.01.12 |
| 3일차(2018.01.04) (0) | 2018.01.09 |
| 2일차(2018.01.03) (0) | 2018.01.09 |
| 1일차(2018.01.02) (0) | 2018.01.09 |