씅의 기록하는 개발노트

■ 주석

1줄 주석 : -- (블록 잡고 ctrl + /)                                --○ 새로운 개념

여러줄 주석 : /* ~ */                                               --==>> 결과값

  -->> 설명

--○ 현재 접속한 자신의 계정 확인

SELECT USER

FROM DUAL;

--==>> HR

--※ sys로 접속한 상태에서! 시작

--○ 사용자 계정 생성 및 패스워드 설정

create user scott

identified by tiger;

--==>>User SCOTT이(가) 생성되었습니다.

--○ 권한 부여

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT;

--==>>Grant을(를) 성공했습니다.

--테이블스페이스는 테이블들이 논리적으로 저장되는 공간

--○ 구조적인 변경

ALTER USER SCOTT DEFAULT TABLESPACE USERS;

--==>>User SCOTT이(가) 변경되었습니다.

--○ 구조적인 변경

ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;

--==>>User SCOTT이(가) 변경되었습니다.

--지금부터 SCOTT으로 접속~~!

--CONNECT SCOTT/TIGER

-- 사용자 확인

SELECT USER

FROM DUAL;

--==>> SCOTT

--○ 테이블 생성(DEPT)

CREATE TABLE DEPT

(DEPTNO   NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY

, DNAME   VARCHAR2(14) 

, LOC     VARCHAR2(13) 

);

--하나의 항목속성을 컬럼이라 부름

--==>>Table DEPT이(가) 생성되었습니다.

--○ 확인

SELECT *

FROM DEPT;

--○ 테이블 생성(EMP)

CREATE TABLE EMP

       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY

       ,  ENAME VARCHAR2(10)

       ,  JOB VARCHAR2(9)

       ,  MGR NUMBER(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 TABLE)

--약식 구문으로 입력하고 있으나 권장하지는 않음

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 TABLE)

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)

CREATE TABLE BONUS

( ENAME VARCHAR2(10)

  , JOB VARCHAR2(9)  

  , SAL NUMBER

  , COMM NUMBER

) ;

--==>> Table BONUS이(가) 생성되었습니다.

--○ 테이블 생성(SALGRADE)

DROP TABLE SALGRADE;

CREATE TABLE SALGRADE

      ( GRADE NUMBER,

LOSAL NUMBER,

HISAL NUMBER );

--==>> Table SALGRADE이(가) 생성되었습니다.

--○ 데이터 입력(SALGRADE TABLE)

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;

--COMMIT명령어 = 메모리상에 올라가진 내용을 HDD에 기록하라!

--==>> 커밋 완료

--ROLLBACK;

--ROLLBACK명령어 = COMMIT과 반대

/*

DB운영에서 가장 중요한 것은 메모리이다

메모리를 얼만큼 아끼는 지가 시간과 비용을 얼만큼 아끼는가 이기 때문이다.

*/

--○ 현재 SCOTT 계정이 소유하고 있는 테이블 조회

SELECT *

FROM TAB;

--==>> TAB는 TABLE의 약자

/*

BONUS    TABLE

DEPT     TABLE

EMP       TABLE

SALGRADE TABLE

SCOTT이 총 4개의 테이블을 가지고 있다!

*/

중요!!!

--------------------------------------------------

--■■■ SELECT 문의 처리(PARSING) 순서 ■■■--

--------------------------------------------------

                --필수 암기--

/*

SELECT 컬럼명   -- ⑤   세로방향으로 추려냄

FROM 테이블명   -- ①

WHERE  조건절   -- ②   가로방향으로 추려냄 

GROUP BY   절   -- ③   그룹화

HAVING     절   -- ④   그룹의 조건

ORDER BY   절   -- ⑥   오름차순 / 내림차순

※ 쿼리문 구성할 때 ORDER BY를 사용하지 않는 경우가 있다.

   ORDER BY정렬은 리소스 소모가 굉장히 심해서 

   시스템 부하가 많이 발생하기 때문!

   WHERE 조건절에 만족하는 데이터들만 메모리 상에 올라간다.

   그러므로 WHERE 조건절이 없으면 모든 데이터를 다 스캔하기 때문에 메모리 사용량이 늘어난다.

   SELECT ENAME

   FROM EMP;

   조건이 하나도 없으므로 TABLE 전체를 스캔하여 메모리 상에 올라간다.

   SELECT ENAME, JOB, EMPNO

   FROM EMP

   WHERE ENAME = 'JSW';

   WHERE 조건에 맞는 데이터만 메모리 상에 올라감. 메모리 절약

*/

--○ BONUS 테이블 조회

SELECT *

FROM BONUS;

--○ 부서정보(DEPT) 테이블 조회

SELECT *

FROM DEPT;

--○ 사원정보(EMP) 테이블 조회

SELECT *

FROM EMP;

--○ 급여 등급(SALGRADE) 테이블 조회

SELECT *

FROM SALGRADE;

--○ 테이블의 구조 확인

DESCRIBE EMP;

/* 실행 결과

이름       널        유형           

-------- -------- ------------ 

EMPNO    NOT NULL NUMBER(4)      

        //NOT NULL을 필수 입력사항정도로 이해하자!(비어있으면 안되기 때문!)

ENAME             VARCHAR2(10) 

JOB               VARCHAR2(9)  

MGR               NUMBER(4)    

HIREDATE          DATE         

SAL               NUMBER(7,2)  

COMM              NUMBER(7,2)  

DEPTNO            NUMBER(2) 

*/

/*

---------------------------------

--■■■ 주요 자료형 ■■■--

---------------------------------

1. 숫자형  NUMBER

           NUMBER(3)  = -999~999까지 입력 가능

           NUMBER(4,1) = -999.X ~ 999.X 소숫점 첫번째까지 입력가능

                        전체가 4자리이고 소숫점이 1자리

2. 문자형   고정형 크기

            CHAR(10)  = 10바이트 크기인 문자

                        5바이트 만큼의 데이터를 입력해도 10바이트를 차지한다.

            가변형 크기

            VARCHAR2(10)  = 가변형이라고 해서 명시되어 있는 바이트를 넘어갈 수 없다.

                            오버플로우로 짤리는 것이아니라 처음부터 에러가 난다.

                            헤더에 관련 자료의 크기나 정보의 크기가 더 크기때문에 참조할 때 리소스가 더 많이 소모된다.

                            따라서 고정된 길이의 데이터는 CHAR형을 사용한다.

            숫자로 구성된 데이터지만 숫자로 처리하면 안되는 것

            1. 전화번호

            2. 학번

            3. 주민번호

            첫시작이 0으로 시작하게 된다면 숫자형이면 앞에 0이 삭제되기 때문!!

            NCHAR(N)  -   유니코드기반 글자수 (N글자 입력가능)

            NVARCHAR2(N)  - 유니코드기반 글자수 (N글자 입력가능)

3. 날짜형

          DATE  -   

*/

SELECT SYSDATE

FROM DUAL;

--==>>18/01/05

--SESSION  - 연결과 관련되어 있는 정보를 담아두는 공간

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SELECT SYSDATE

FROM DUAL;

--==>>2018-01-05 14:49:06

SELECT 0123

FROM DUAL;

--==>> 123

SELECT '0123'

FROM DUAL;

--==>>0123

--------------------------------------------------------------------------------

--○ EMP 테이블에서 부서번호가 20번인 사원들의 정보 중 

--   사원번호, 사원명, 급여, 직종명, 부서번호 조회

SELECT EMPNO

      ,ENAME

      , SAL

      , JOB

      , DEPTNO

FROM EMP

WHERE DEPTNO=20;

-- ※ 각 컬럼에 별칭을 부여할 수 있다.

SELECT EMPNO AS "사원번호", ENAME "사원명", SAL 급여, JOB "직종 명", DEPTNO

FROM EMP

WHERE DEPTNO=20;

--○ EMP 테이블에서 부서번호가 20번과 30번 직원들의 정보 중

--   사원번호, 사원명, 직종명, 급여, 부서번호 항목을 조회한다.

SELECT EMPNO 사원번호, ENAME 사원이름, JOB 직종명, SAL 급여, DEPTNO 부서번호

FROM EMP

WHERE DEPTNO=20 OR DEPTNO=30

ORDER BY SAL ASC;

--위와 아래는 같은 구문

SELECT EMPNO 사원번호, ENAME 사원이름, JOB 직종명, SAL 급여, DEPTNO 부서번호

FROM EMP

WHERE DEPTNO IN(20,30);

--○ || 문자열 결합 연산자

SELECT 'A'||'B'

FROM DUAL;

--○ EMP 테이블에서 직종이 CLERK인 사원들의 정보를 모두 조회한다

SELECT *

FROM EMP

WHERE JOB='CLERK';

-- 오라클에서 입력된 데이터(값) 만큼은 반드시 대소문자 구분을 해야한다.

--○ 테이블 복사

--> 내부적으로 대상 테이블 안에 들어있는 데이터 내용만 복사하는 과정

CREATE TABLE TBL_EMP

AS  -- 저거저거~~ 저거로 만들어달라

SELECT *

FROM EMP;

SELECT *

FROM TBL_EMP;

--○ 이미 만들어진 테이블에 컬럼 추가

-- TBL_EMP 테이블에 주민번호 정보를 담을 수 있는 컬럼(SSN) 추가

ALTER TABLE TBL_EMP

ADD SSN CHAR(13);

--==>> Table TBL_EMP이(가) 변경되었습니다.

/*

INSERT / UPDATE / DELETE 와 같은 DML구문을 작성한 이후에는

선택적으로 COMMIT이나 ROLLBACK을 해야한다.

다른 구문들은 자동적으로 COMMIT을 실행해준다!

한쪽은 오토 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;

/*

SSN이 NULL로 되어있음

1. 컬럼의 구성되어 있는 순서는 중요하지 않다 아니 의미가 없다.

2. 데이터를 채우려면 UPDATE해야함

   레코드 한개가 하나의 데이터인데 SSN에 데이터를 삽입하려면

   UPDATE를 사용해야함

*/

ALTER TABLE TBL_EMP DROP COLUMN SSN;

SELECT *

FROM TBL_EMP;

SELECT *

FROM EMP;

ALTER TABLE TBL_EMP

ADD SSN CHAR(13);

/*

DELETE 실수를 하지 않는 방법

SELECT *

FROM TBL_EMP

WHERE EMPNO=7369;

처음에 삭제해야 하는 것을 확인!!

DELETE

FROM TBL_EMP

WHERE EMPNO=7369;

COMMIT;

SELECT *을 DELETE로 바꾼후 실행!

*/

-- TBL_EMP 테이블 삭제

DROP TABLE TBL_EMP;

--오라클 휴지통에 들어가 있음

SELECT *

FROM TAB;

--이상한 글자가 있음 DROP 한 것

-- BIN$Aa411Q6JRXexdxVM7/zvrw==$0

------------------------------------------------------------------------------

--○ NULL의 처리

SELECT 2, 1+2, 3-1

FROM DUAL;

--==>>2 3 2

SELECT 2+NULL, 2-NULL, 2*NULL, 2/NULL

FROM DUAL;

--==>>NULL NULL NULL NULL

--※ NULL은 현실에 존재하는 특정한 값을 의미하는 것이 아니라

--   아무것도 들어있지 않은 상태를 의미하는 값이므로 

--   NULL을 대상으로 연산을 수행할 경우 또는 특정 연산 과정에 NULL이 포함될 경우

--   결과는 무조건 NULL이다.

--○  다시 EMP 테이블을 복사하여 TBL_EMP테이블을 생성

CREATE TABLE TBL_EMP

AS

SELECT *

FROM EMP;

--==>>Table TBL_EMP이(가) 생성되었습니다.

--○ TBL_EMP 테이블에서 커미션(COMM) → 수당이 NULL인 사원들의

--   사원명, 직종명, 급여, 커미션을 조회한다.

SELECT ENAME 사원명, JOB 직종명, SAL 급여, COMM 커미션

FROM TBL_EMP

WHERE COMM IS NULL;

--※ NULL은 값으로 존재하는 것이 아니기 때문에 값으로 비교할 수 없다.

--   =, >=, <=, !=, <>, >, < 와 같은 연산자를 사용할 수 없다.  !=와 <> ^= 모두 같지 않다는 의미

--○ TBL_EMP 테이블에서 20번 부서에 근무하지 않는 사원들의 정보를 

--   사원번호, 사원명, 부서번호로 조회한다.

SELECT EMPNO 사원번호, ENAME 사원명, DEPTNO 부서번호 

FROM TBL_EMP

WHERE DEPTNO!=20;

SELECT EMPNO 사원번호, ENAME 사원명, DEPTNO 부서번호

FROM TBL_EMP

WHERE NOT DEPTNO=20;

--TBL_EMP 테이블에서 COMM(커미션)이 NULL이 아닌 것을 조회

SELECT *

FROM TBL_EMP

WHERE COMM IS NOT NULL;

SELECT *

FROM TBL_EMP

WHERE NOT COMM IS NULL;

--○ TBL_EMP 테이블에서 모든 사원들의 

--   사원번호, 사원명, 급여, 커미션, 연봉 정보를 조회한다.

--   단, 급여(SAL)는 매월 지급한다.

--   수당(COMM)은 1회/년 지급한다.

ALTER TABLE TBL_EMP

ADD MONEY NUMBER(10);

ALTER TABLE TBL_EMP DROP COLUMN MONEY;

SELECT EMPNO 사원번호, ENAME 사원명, SAL 급여, COMM 커미션, (SAL*12)+(NVL(COMM,0)) 연봉

FROM TBL_EMP;

■■■ 데이터베이스 DBMS 개념 ■■■

DBMS전에는 파일시스템으로 데이터를 관리했다.

데이터 - 데이터는 정보보다 작은 단위 즉, 데이터를 여러개 모으면 정보가 된다.

베이스 - 데이터를 모아두는 기반/장소/장치

데이터란 현실 세계에서 관찰이나 측정을 통해 수집된

사실(fact)이나 값(value)을 말한다.

정보란 데이터의 유효한 해석이나 데이터 상호간의 관계를

의사 결정에 도움이 되도록 가공한 것이다.

데이터베이스란 여러 응용시스템이 공유할 수 있도록

통합, 저장된 운영 데이터의 집합이다.

■■■ DBMS ■■■

○ DBMS 란?

   파일 관리 시스템의 단점을 개선하여 데이터를 통합적으로 관리하는 소프트웨어로

   컴퓨터에 수록한 수많은 자료들을 쉽고 빠르게 추가 / 삭제 / 검색할 수 있는 시스템이다.

   흔히 DBMS라고 하면 어떤 하드웨어적인 서버를 생각하는 경우가 많은데

   데이터베이스 관리 시스템은 시스템이나 장비가 아닌 논리적인 소프트웨어이다.

   즉, 사용자들은 응용프로그램을 이용하여 SQL명령을 DBMS에 전달하며

   DBMS는 이러한 사용자들의 명령을 받아 해석하고 이를 컴퓨터에서 처리할 수 있는 형태로 변환하여 실행하며

   이와 관련된 모든 프로세서를 관리하는 논리적인 소프트웨어인 것이다.

데이터베이스 관리 시스템(DBMS)

DBMS : DataBase Management System이란

모든 응용 프로그램들이 데이터베이스를 공유할 수 있도록 관리해주고,

데이터베이스를 유지하기 위한 일련의 소프트웨어 시스템이다.