zy._.won

시퀀스 생성 문법

CREATE SEQUENCE 시퀀스이름
[START WITH n] 
[INCREMENT BY n] 
[MAXVALUE n | NOMAXVALUE] 
[MINVALUE n | NOMINVALUE] 
[CYCLE | NOCYCLE] 
[CACHE | NOCACHE] 

시퀀스 수정 문법

ALTER SEQUENCE 시퀀스이름
[필요한 항목 작성];

시퀀스 삭제 문법

DROP SEQUENCE 시퀀스 이름

시퀀스 예제)

CREATE SEQUENCE SEQ_GN;

1부터 1씩 제한, 순환없는 시퀀스.

ALTER SEQUENCE SEQ_GN
INCREMENT BY 2;

2씩 증가되도록 하기위해 INCREMENT BY 2로 해줌

시퀀스 번호 확인하는 방법

Row Number(행 번호 달기)

1. ROWNUM 키워드 사용법

SELECT ROWNUM 번호, GOOS.*
FROM goods
ORDER BY doos.g_name
;

1-1. ROWNUM에 번호를 붙이고 정렬을 수행하는 방법

SELECT ROWNUM 번호, g.*
FROM (
    SELECT * FROM goods
    ORDER BY g_name) g
;

번호를 붙이고 정렬을 수행하는 방법

2. ROW_NUMBER() 함수 사용법

SELECT ROW_NUMBER() OVER(ORDER BY g_name DESC) 번호, GOODS.*
FROM goods;

트랜젝션

데이터 처리의 한 단위

INSERT, UPDATE, DELETE 작업은 트랜젝션 단위로 실행.

CREATE, ALTER, DROP은 ROLLBACK 트랜젝션 불가능함.

트랜젝션의 종류

트랜젝션 주의점

UPDATE, DELETE는 원상복구한다는거 자체가 불가능함.

COMMIT과 ROLLBACK은 이러한 부분을 보완할 수 있음.

트랜젝션 사용 예제)

1. SAVEPOINT를 사용하지 않은 예제

-- 0. 임시테이블 삭제(없을 경우 실행 X)
DROP TABLE TEMP_EMP;
-- 1. 기존 emp 테이블을 복사한 임시 테이블 생성
CREATE TABLE TEMP_EMP
AS
SELECT * FROM EMP;
-- 1-1. 데이터 확인
SELECT * FROM TEMP_EMP;
-- 2. 데이터 삭제(직번 = 7521)
DELETE FROM TEMP_EMP
WHERE EMPNO = 7521;
-- 3. 삭제 승인
COMMIT;
-- 4. 데이터 확인
SELECT * FROM TEMP_EMP
WHERE empno = 7521;
-- 5. 데이터 삽입
INSERT INTO TEMP_EMP(empno, ename, hiredate)
VALUES (9000, 'test', sysdate);
-- 6. 삽입 승인
COMMIT;
-- 7. 데이터 삭제
DELETE FROM TEMP_EMP
WHERE empno = 9000;
-- 8. 삭제 승인 없이 데이터 확인
SELECT * FROM TEMP_EMP
WHERE empno = 9000;
-- 9. 삭제 작업 취소
ROLLBACK;
-- 10. 데이터 확인
SELECT * FROM TEMP_EMP
WHERE empno = 9000;

2. SAVEPOINT를 사용한 예제

-- 11. 임시 테이블의 컬럼 수정(empno의 자릿수 1 증가)
ALTER TABLE TEMP_EMP
MODIFY empno NUMBER(5); -- CREATE, ALTER, DROP은 ROLLBACK 불가능.
-- 12. 데이터 삽입(no commit)
INSERT INTO TEMP_EMP(empno, ename, hiredate)
VALUES(10000, 'test2', sysdate);
-- 13. SAVEPOINT 지정('A'는 지정한 SAVEPOINT의 이름..)
SAVEPOINT A;
-- 14. 데이터 삽입(no commit)
INSERT INTO TEMP_EMP(empno, ename, hiredate)
VALUES(10001, 'test3', sysdate);
INSERT INTO TEMP_EMP(empno, ename, hiredate)
VALUES(10002, 'test4', sysdate);
-- 15. 데이터 확인 
SELECT empno, ename
FROM TEMP_EMP
WHERE empno IN (10000, 10001, 10002);
-- 16. 데이터 삭제
DELETE FROM TEMP_EMP
WHERE empno IN (10000, 10001, 10002);
-- 17. 데이터 확인 
SELECT empno, ename
FROM TEMP_EMP
WHERE empno IN (10000, 10001, 10002);
-- 18. SAVEPOINT까지 작업 취소.
ROLLBACK TO A; -- 지정한 부분까지만 롤백이 됨
-- 19. 데이터 확인
SELECT empno, ename
FROM TEMP_EMP
WHERE empno IN (10000, 10001, 10002);
-- 20. 작업 취소
ROLLBACK;
-- 21. 최종 데이터 확인 
SELECT empno, ename
FROM TEMP_EMP
WHERE empno IN (10000, 10001, 10002); -- 첫번째 입력한거까지 다 삭제됨

JOIN

둘 이상의 테이블을 연결하여 데이터를 검색하는 방법

일반적으로 PRIMARY KEY 및 FOREIGN KEY를 사용

적어도 하나의 컬럼이 두 테이블 사이에서 공유되어야 함.

JOIN 문법

  _SYN
  SELECT    [TAB].[COL], ..., [TAB].[COL]
  FROM      [TAB | VIEW | INLINE-VIEW] [TAB_ALIAS]
            [INNER | [RIGHT | LEFT | FULL] OUTER] JOIN
            [TAB | VIEW | INLINE-VIEW] [TAB_ALIAS]
  ON        [JOIN-CONDITION]
  WHERE

JOIN의 종류

INNER JOIN 예제)

회원별 구매 현황 리스트
---------------------------------------------------------------------------
  회원코드      회원명     구매횟수    평균구매금액  총구매금액
  MM SA SD      MM     COUNT(SA)   AVG(GO*SD)  SUM(GO*SD)   
----------------------------------------------------------------------------

STEP 1
------------------------------------------
  회원코드      회원명     구매횟수  
  MM SA SD      MM      COUNT(SA)
------------------------------------------

MEMBERS 테이블과 SALES 테이블에서 공통되는 컬럼을

잘못된 코드)

SELECT SA.SA_MMCODE AS "MMCODE",
        MM.MM_NAME AS "MMNAME",
        COUNT(SA.SA_DATE) AS "SADATE"
FROM MM INNER JOIN SA
ON MM.MM_CODE = SA.SA_MMCODE;

SYNONYM 해줘서 다시 별칭해줄 필요 없음.

INNER JOIN >> ON 에 해당하는 조건이 완전히 일치할 때만 출력할 준비함.

not a single-group group function >> GROUPING 컬럼과 그렇지 않은 컬럼은 동시 출력 불가함.

수정된 코드)

SELECT SA.SA_MMCODE AS "MMCODE",
        MM.MM_NAME AS "MMNAME",
        COUNT(SA.SA_DATE) AS "SADATE"
FROM MM INNER JOIN SA
ON MM.MM_CODE = SA.SA_MMCODE
GROUP BY SA.SA_MMCODE, MM.MM_NAME;

STEP 2

-------------------------------------------------
    회원코드   평균구매금액   총구매금액     
   MM SA SD   AVG(GO*SD)  SUM(GO*SD)   
-------------------------------------------------

SELECT SD.SD_SAMMCODE AS "MMCODE",
        AVG(GO.GO_PRICE * SD.SD_QTY) AS "AVGPAY",
        SUM(GO.GO_PRICE * SD.SD_QTY) AS "TOTPAY"
FROM GO INNER JOIN SD
ON GO.GO_CODE = SD.SD_GOCODE
GROUP BY SD.SD_SAMMCODE

STEP 3 -> 서로 다른 테이블에서 그룹핑된 데이터를 최종적으로 INNER JOIN하는 단계 

INLINE VIEW : 서브쿼리를 통해 만들어진 가상의 테이블을 사용하는 것

SELECT  T1.MMCODE,
        T1.MMNAME,
        T1.PCOUNT,
        --T2.AVGPAY,
        CEIL(T2.AVGPAY) AVGPAY,
        T2.TOTPAY
FROM (SELECT SA.SA_MMCODE AS "MMCODE",
        MM.MM_NAME AS "MMNAME",
        COUNT(SA.SA_DATE) AS "PCOUNT"
        FROM MM INNER JOIN SA
        ON MM.MM_CODE = SA.SA_MMCODE
        GROUP BY SA.SA_MMCODE, MM.MM_NAME) T1
INNER JOIN 
      (SELECT SD.SD_SAMMCODE AS "MMCODE",
        AVG(GO.GO_PRICE * SD.SD_QTY) AS "AVGPAY",
        SUM(GO.GO_PRICE * SD.SD_QTY) AS "TOTPAY"
        FROM GO INNER JOIN SD
        ON GO.GO_CODE = SD.SD_GOCODE
        GROUP BY SD.SD_SAMMCODE) T2
ON T1.MMCODE = T2.MMCODE
;

VIEW

데이터 저장하기 위한 임시적인 테이블

VIEW 문법

   _SYN
   CREATE OR REPLACE VIEW [VIEW-NAME]
   AS
   [QUERY]

VIEW를 이용한 JOIN 예제)

CREATE OR REPLACE VIEW T1
AS 
SELECT SA.SA_MMCODE AS "MMCODE",
        MM.MM_NAME AS "MMNAME",
        COUNT(SA.SA_DATE) AS "PCOUNT"
        FROM MM INNER JOIN SA
        ON MM.MM_CODE = SA.SA_MMCODE
        GROUP BY SA.SA_MMCODE, MM.MM_NAME;

CREATE OR REPLACE VIEW T2
AS
SELECT SD.SD_SAMMCODE AS "MMCODE",
        AVG(GO.GO_PRICE * SD.SD_QTY) AS "AVGPAY",
        SUM(GO.GO_PRICE * SD.SD_QTY) AS "TOTPAY"
        FROM GO INNER JOIN SD
        ON GO.GO_CODE = SD.SD_GOCODE
        GROUP BY SD.SD_SAMMCODE;

SELECT T1.MMCODE, T1.MMNAME, T1.PCOUNT, T2.AVGPAY, T2.TOTPAY
FROM T1 INNER JOIN T2
ON T1.MMCODE = T2.MMCODE
;

COUNT

레코드의 전체 개수 구하기 위해 사용

SELECT count(*) FROM employees;

IS NULL, IS NOT NULL

||

속성 결과들의 결합을 하기 위해 사용

SELECT first_name || ' ' || last_name
FROM employees
;

NVL 함수

데이터가 null일 경우 다른 지정값으로 대체

SELECT employee_id 사원번호,
    first_name || ' ' || last_name 사원명,
    salary 월급여, commission_pct 커미션지급률,
    (salary * 12) + ((salary * 12) * NVL(commission_pct, 0)) 연봉
FROM employees
;

commission_pct에 null이 들어가 있을 경우 0으로 대체해서 사용한다.

ORDER BY, ASC, DESC, DISTINCT

오라클 내장 함수

1. 숫자형 함수

2. 문자 함수

* CONCAT과 ||의 공백 사용시의 차이점

SELECT INITCAP('programmiNG') FROM DUAL;

SELECT first_name, LOWER(first_name), UPPER(first_name)
FROM employees;

first_name에서 LOWER를 사용한 부분은 소문자로 출력되고, UPPER를 사용한 부분은 대문자로 출력되었다.

SELECT first_name || ' ' || last_name,
        LENGTH(hire_date) 입사일길이, 
        SUBSTR(hire_date, 1, 4)|| '년' ||
        SUBSTR(hire_date, 6, 2)|| '월' ||
        SUBSTR(hire_date, 9, 2)|| '일' 입사일,
        hire_date 전체입사일                     
FROM employees; 

SUBSTR(데이터, 시작 위치, 단어의 갯수)를 의미한다.

즉, 년도는 1번째부터 4개의 단어까지, 월은 6번째부터 2개의 단어, 일은 9번째부터 2개의 단어를 출력한다.

JAVA의 배열은 0부터 시작하지만, DATABASE의 배열은 1부터 시작한다.

SELECT INSTR('abc.def.ghi.txt', '.', -1, 1)
FROM DUAL;

-1은 음수이기 때문에 'abc.def.ghi.txt'에서 .을 오른쪽에서 첫번째로 위치되어 있는 것을 찾는다.

즉, 3번째 위치하고 있는 . 을 찾는 것이기 때문에 결과는 왼쪽부터 카운트하여 12가 나온다.

3. 날짜 함수

SELECT SYSDATE,        	  	  -- 현재 시간
    SYSDATE - 1 하루전,            -- 현재 시간에서 하루전
    SYSDATE - 1/24 한시간전,       -- 현재 시간에서 한시간전
    SYSDATE - 1/24/60 일분전,      -- 현재 시간에서 1분전
    SYSDATE - 1/24/60/60 일초전    -- 현재 시간에서 1초전
FROM DUAL;   

SELECT SYSTIMESTAMP FROM DUAL;

SELECT ADD_MONTHS(SYSDATE, -2) FROM DUAL;

SELECT MONTHS_BETWEEN(TO_DATE('2020-08-05', 'YYYY-MM-DD')
       ,TO_DATE('2019-02-05', 'YYYY-MM-DD')) 차이달
FROM DUAL;

SELECT SYSDATE, LAST_DAY(SYSDATE)
FROM DUAL;

SELECT NEXT_DAY(SYSDATE, 5) "다음주 수요일"
FROM DUAL;

4. 형변환 함수

NUMBER >> CHAR : TO_CHAR

SELECT 12345678 - 1, TO_CHAR(12345678)- 1 FROM DUAL;

형변환 산술연산이 가능함

SELECT 12345678 || '' FROM DUAL;

NUMBER 뒤에 ' '을 붙이면 문자형으로 변환이 가능함.

SELECT TO_CHAR(12345678, '999,999,999') FROM DUAL;

금액형식으로 출력하는 형식으로 만들 수 있음.

SELECT TO_CHAR(12345678, '999,999') FROM DUAL;

입력 숫자에 비해 자리수를 적게 잡아둔다면 오류 발생함

SELECT TO_CHAR(16, '0009') FROM DUAL;

CHAR >> NUMBER : TO_NUMBER

SELECT TO_NUMBER('012345') FROM DUAL;

숫자이기 때문에 0을 출력 안 함

SELECT TO_NUMBER('012345A') FROM DUAL;

문자가 들어가면 오류가 발생함

DATE >> CHAR : TO_CHAR

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
    TO_CHAR(SYSDATE, 'YYMMDD'),
    TO_CHAR(SYSDATE, 'YYYY') 연도,
    TO_CHAR(SYSDATE, 'MM') 월
FROM DUAL;

원하는 위치의 데이터만 뽑아올 수 있음.

원래의 SYSDATE 출력 형식에는 /가 들어가 있는데 다른 유형으로 출력 가능함.

CHAR >> DATE : TO_DATE

SELECT TO_DATE('20200101132510', 'YYMMDDHH24MISS') FROM DUAL;

GROUP BY & HAVING

원하는 데이터를 컬럼별로 합하여 출력

집계함수 

중간 데이터가 많지만 최종적인 결과는 그룹에 대한 하나만 나옴.

WHERE과 HAVING의 차이점)

SELECT department_id, SUM(salary*12),COUNT(employee_id)
FROM employees
WHERE department_id >= 50
GROUP BY department_id;

SELECT department_id, SUM(salary*12),COUNT(employee_id),
        MAX(salary * 12), MIN(salary * 12)        
FROM employees
GROUP BY department_id
HAVING department_id >= 50;

테이블 관리(ALTER TABLE)

DML(Data Manipulation Lanuage, 데이터 조작어)

데이터의 삽입, 수정, 삭제

DQL(Data Query Lanuage, 데이터 질의어)

ASC - 오름차순, DESC - 내림차순

DML 예제)

1. INSERT

INSERT INTO EMP
VALUES (7963, 'SMITH', 'CLERK', 7902, TO_DATE('80/12/17'), 800, NULL, 20);

EMP 테이블에 데이터 추가.

테이블에 있는 컬럼 전체에 데이터가 추가되기 때문에 컬럼명을 따로 적지 않음.

NULL 값이 들어가는 부분은 비워두지 않고, NULL 문자를 넣어준다.

2. UPDATE

UPDATE ADDRESSBOOK
SET BIRTH = SYSDATE;

SYSDATE는 시스템 상의 날짜를 의미하며 ADDRESSBOOK 테이블의 모든 사람의 BIRTH를 동일하게 수정한다.

UPDATE EMP
SET SAL = SAL * 1.1
WHERE DEPTNO = 20;

부서번호 20번의 사원들의 급여를 10% 인상해준다.

3. DELETE

DELETE EMP
WHERE EMPNO = 7963;
COMMIT;

사원번호 7963번 사원의 데이터를 삭제한다.

별칭 사용(ALIAS)

SELECT ENAME AS 이름, SAL AS 급여
FROM EMP;

WHERE(조건절)

조건을 지정할 때 사용하는 명령어절

이 위치에 설정된 조건에 맞는 튜플(레코드)만 SQL 명령어의 영향을 받는다.

예제)

SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 10;

부서번호가 10번인 부서의 사원번호와 이름을 구할 수 있다.

WHERE 절에서 사용하는 연산자

JAVA, 오라클에서의 '=' 연산자 비교

오라클에서는 == 을 사용하지 않음.

WHERE절에 있으면 '같음'의 의미, SET 뒤에 있는건 '대입'의 의미

예제)

SELECT EMPNO, ENAME
FROM EMP
WHERE job = 'MANAGER' 
AND SAL >= 2500
;

job이 MANAGER이고, 급여가 2500 이상인 사원번호와 이름을 출력함.

BETWEEN a AND b 연산

SELECT ename 이름, empno 사원번호
FROM emp
WHERE sal BETWEEN 1000 AND 3000
;

급여가 1000이상, 3000이하인 사원의 이름과 사원번호를 출력한다.

LIKE

LIKE와 함께 사용하는 표현법

예제)

SELECT * 
FROM emp
WHERE ename like 'A%'
;

'A'로 시작하는 사원을 출력한다.

LIKE의 문제점

데이터에 '%'나 '_'가 포함되어 있다면 검색할 수 있을까?

INSERT INTO emp (empno, ename)
VALUES (9000, '%PARK');
COMMIT;

해결방법 => ESCAPE을 사용한다.

SELECT *
FROM emp
WHERE ename LIKE '%\%%' ESCAPE'\'
;

ESCAPE로 ＼를 지정해줬기 때문에 '＼%'을 한 단어로 취급해서 검색한다.

SQL(Structured Query Language)

구조적 질의 언어

SQL 종류

1. DDL(Data Definition Language) - 정의어

2. DML(Data Mnipulation Language) - 조작어

3. DCL(Data Control Language) - 제어어

사용자(계정) 생성

CREATE USER 이름

IDENTIFIED BY "비밀번호"

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON USERS;

생성된 계정에 권한(DB Adminstrator, DB관리자) 부여

GRANT DBA TO 이름;

TABLESPACE란?

오라클 db가 데이터를 저장하는 논리 구조(파일) 하나 또는 여러개의 파일로 구성.

테이블 생성 방법 

CREATE TABLE 테이블명 (

속성명1 타입 제약조건,

속성명2 타입 제약조건,

...

);

제약조건

테이블에 부적절한 자료(데이터)가 입력되는 것을 방지하기 위해서 여러가지 규칙을 적용하는 것.

-데이터의 무결성 유지를 위해 사용자가 지정할 수 있는 성질

-의미 있는 이름을 부여하여 쉽게 참조할 수 있음.

-제약 조건은 테이블 생성 시에 지정하거나 수정(ALTER) 명령으로 추가할 수 있음.

제약조건의 종류

테이블 만들기 예제)

CREATE TABLE EMP2(
    EMPNO NUMBER CONSTRAINT emp_pk_empno PRIMARY KEY,
    ENAME VARCHAR2(10) NOT NULL, --NULL 허용하지 않음 
    JOB VARCHAR2(9),  -- 업무
    MGR NUMBER, -- 부서장 직원 번호
    HIREDATE DATE, -- 고용일자
    SAL NUMBER, -- 급여
    COMM NUMBER, -- 수수료
    DEPTNO NUMBER -- 부서번호
);

NOT NULL 예제)

CREATE TABLE EMP3(
    ENAME VARCHAR(20) NOT NULL
);

반드시 값을 입력해야 한다는 제약조건 

    --ENAME VARCHAR(20) CONSTRAINT emp_nn_ename NOT NULL :

정식적으론 이렇게 쓰는게 맞지만 예제처럼 간단하게 써도 됨.

UNIQUE 예제)

CREATE TABLE DEPT3(
    DEPTNO NUMBER UNIQUE
);

데이터의 유일성을 보장한다는 조건(중복 불가)

   --DEPTNO NUMBER CONSTRAINT dept_uk_deptno UNIQUE

CHECK 예제)

CREATE TABLE EMP4(
    GENDER VARCHAR2(1) CONSTRAINT emp_ck_gender CHECK (GENDER IN ('M','F'))
);

데이터를 특정 범위로 제한한다는 제약조건

DEFAULT 예제)

CREATE TABLE MEMBERTBL (
    MID VARCHAR2(20) CONSTRAINT mpk_mid PRIMARY KEY,
    MNAME VARCHAR2(20) NOT NULL,
    MPASS VARCHAR2(16) NOT NULL,
    MGRADE VARCHAR2(10) DEFAULT 'silver'
);

값을 입력하지 않아도 지정한 값으로 입력 처리

컬럼에 데이터 넣는 예제)

INSERT INTO MEMBERTBL (MID, MNAME, MPASS)
VALUES ('tester', '홍길동', '1234');

실제 테이블의 컬럼이름, 테이블 순서대로 쓸 필요는 없음.

앞에 작성한 컬럼의 순서대로 넣어주면 됨.

기본키 설정하는 두번째 방법)

CREATE TABLE EMP10(
    EMPNO NUMBER,
    ENAME VARCHAR2(10) NOT NULL,
    DEPTNO NUMBER, --부서 번호
    CONSTRAINT EMP10_PK_EMPNO PRIMARY KEY (EMPNO),
    CONSTRAINT EMP10_FK_EMPNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);

기본키 설정하는 세번째 방법)

ALTER TABLE DEPT
ADD CONSTRAINT dept_pk_deptno PRIMARY KEY (DEPTNO);

CREATE TABLE ADDRESSBOOK(
    STUDENT VARCHAR2(3),
    BIRTH DATE,
    PHONE VARCHAR2(11),
    EMERGENCY VARCHAR2(11)
);

키의 추가/삭제 방법)

ALTER TABLE ADDRESSBOOK
ADD CONSTRAINT ab_pk_sn PRIMARY KEY (SNUMBER);

기본 키 삭제 방법 1.

ALTER TABLE ADDRESSBOOK
DROP PRIMARY KEY;

기본 키 삭제 방법 2.

ALTER TABLE ADDRESSBOOK
DROP CONSTRAINT ab_pk_sn;

테이블의 복사

CREATE TABLE TEMP_EMP
AS 
SELECT * FROM EMP;

새로운 테이블을 만들면서 기존 데이터를 그대로 복사