심플하고 차분하게

■ 시퀀스란?

[1] 시퀀스의 생성

CREATE SEQUENCE seq_테이블명_idx START WITH 1 INCREMENT BY 1;
-- 또는 
CREATE SEQUENCE seq_테이블명_idx;

[2] 데이터 추가 시 시퀀스 추가 - 시퀀스명.nextVal

INSERT INTO sungtb VALUES(seq_sungtb_idx.nextVal, '일길동', 90, 80, 70);

[3] 시퀀스의 삭제

DROP SEQUENCE seq_sungtb_idx

■ 인덱스란?

인덱스를 사용해야 하는 경우 

- 데이터 양이 많고 검색이 수정보다 빈번한 경우

- 도메인의 값이 다양한 값을 가지는 경우 

인덱스를 사용할 때 주의할 점

- 인덱스를 사용하는 열에 NULL이 지나치게 많으면 효율이 떨어진다.

■ NVL함수를 통해 인덱스 형성하기

[문법]

NVL(MAX(idx),0)+1
--> (1) Max(idx)는 idx칼럼에서 가장 큰 수를 반환
--> (2) NVL(a , 0)  :  a라는 칼럼에 NULL이 없으면 (or a라는 데이터가 있으면) 해당 데이터 반환
                    -- 그렇지 않고 NULL만 있으면 0으로 데이터를 대체한다.
    --> 곧, idx에 데이터가 없으면 0을 넣고, 있으면 그 데이터에 +1을 하겠다.

SELECT NVL(MAX(idx), 0) + 1 FROM book;
--> book이라는 테이블에서 가장 큰 인덱스 값을 가져오고 +1을 한다.

[예시]

CREATE TABLE book
(
   idx   INT,
   name  CHAR(3 CHAR) NOT NULL,
   price INT
);

ALTER TABLE book 
  ADD CONSTRAINT pk_book_idx PRIMARY KEY(idx);
  
-- SAMPLE DATA 입력
INSERT INTO book VALUES((SELECT nvl(MAX(idx), 0) + 1 FROM book),'Java',20000);
INSERT INTO book VALUES((SELECT nvl(MAX(idx), 0) + 1 FROM book),'Oracle',30000);
INSERT INTO book VALUES((SELECT nvl(MAX(idx), 0) + 1 FROM book),'HTML',24000);
INSERT INTO book VALUES((SELECT nvl(MAX(idx), 0) + 1 FROM book),'JSP',22000);

[출처]

- 인덱스 참조

https://ltk3934.tistory.com/67

https://coding-factory.tistory.com/746

- 시퀀스 참조

https://coding-factory.tistory.com/420

https://kerpect.tistory.com/17

■ DB모델링

- ERD란? Entity Relationship Diagram, 개체 관계도라 하며,

            개체들 간의 관계를 나타내는 다이어그램이다.

- DB 모델링에 대한 자세한 내용은 아래 포스팅 참조

https://mangkyu.tistory.com/27

- DB 모델링 툴 

■ 정규화란?

>>

* 완전 함수 종속 : 기본키가 여러개일 때, 모든 기본키에 종속적이어야 한다는 것

* 이행적 함수 종속 : 순차적인 이행에 따른 종속 관계 

■ 정규화의 장단점 - 가장 큰 이점 : 이상현상의 제거

■ 역정규화를 하는 이유

■ 이상현상(Anomaly)의 종류

 - 이상현상이란? 테이블을 잘못 설계하여 데이터를 삽입, 삭제, 수정할 때 생기는 논리적 오류

[참조]

- 정규화 참조

https://dev-coco.tistory.com/62
https://mr-dan.tistory.com/10

https://programming119.tistory.com/225

■ 뷰(VIEW)의 정의

- 뷰는 가상 객체로, 하나 이상의 테이블 또는 뷰의 데이터를 볼 수 있게 한다. 

- 실제로 테이블은 아니지만 테이블처럼 사용 가능

- 다른 뷰를 참조해 새로운 뷰를 생성할 수 있다.

■ 뷰(VIEW)의 사용 목적

- [1] 편리성 : 복잡한 명령/수식 등을 간결하게 사용 가능

- [2] 보안성 : 중요 데이터를 외부로 제한적으로 제공

  * DBO의 원천데이터를 선별적으로 가공하여 사용자에게 (조회)권한 부여

■ 뷰(VIEW)의 실제 형태

- .bat 파일과 같이 실제로는 Select ~ from SQL문이 들어가있는 형태

■ 뷰(VIEW)를 생성할 수 있는 권한 부여 *최고 권한자만 권한을 부여할 수 있다.

grant create view to 계정

■ 뷰(VIEW)의 생성 및 수정

CREATE TABLE MEMBER
(
   EIN     NUMBER(5)      NOT NULL, --EMPLOYMENT ID NUMBER
   NAME    CHAR(5 CHAR)   NOT NULL,
   ADDR    VARCHAR2(150)  NOT NULL, 
   SSN     CHAR(15 CHAR) NOT NULL, -- SOCIAL SECURITY NUMBER
   HIREDAY DATE,
   
   CONSTRAINT UNIQUE_MEMBER_EIN UNIQUE(EIN)
);

ALTER TABLE MEMBER
  ADD CONSTRAINT PK_MEMBER_EIN PRIMARY KEY(EIN);
  
INSERT INTO MEMBER VALUES(1, '김길동', '경기도 김포시 장기동', '951221-1354888','1998-07-22');
INSERT INTO MEMBER VALUES(2, '나길동', '서울시 관악구 테헤란로', '931221-2354888','1998-07-22');
INSERT INTO MEMBER VALUES(3, '다길동', '서울시 관악구 문성로', '950821-1354888','1998-07-22');
INSERT INTO MEMBER VALUES(4, '라길동', '천안시 동남구 대흥동', '921221-1354888','1998-07-22');
INSERT INTO MEMBER VALUES(5, '마길동', '천안시 동남구 대흥동', '911221-2354888','1998-07-22');
INSERT INTO MEMBER VALUES(6, '바길동', '삼척시 원덕읍 호산리', '950321-1354888','1998-07-22');
INSERT INTO MEMBER VALUES(7, '사길동', '삼척시 원덕읍 호산리', '650721-2354888','1998-07-22');
INSERT INTO MEMBER VALUES(8, '아길동', '삼척시 원덕읍 호산리', '630121-1354888','1998-07-22');
INSERT INTO MEMBER VALUES(9, '자길동', '서울시 관악구 문성로', '571221-2354888','1998-07-22');
INSERT INTO MEMBER VALUES(10, '차길동', '경기도 김포시 장기동', '881221-1354888','1998-07-22');

SELECT * FROM MEMBER

CREATE OR REPLACE 뷰이름
AS
  /* SQL 문장 */

[예제1 _ 뷰 만들기]

-- 맴버 테이블에서 입사연도/입사월/입사일을 구하는 뷰

CREATE OR REPLACE VIEW MEMBER_HIREDATES
AS
	SELECT
	  MB.*,
	  TO_NUMBER(TO_CHAR(HIREDAY, 'YYYY')) AS HIRE_YEAR,
	  TO_NUMBER(TO_CHAR(HIREDAY, 'MM')) AS HIRE_MONTH,
	  TO_NUMBER(TO_CHAR(HIREDAY, 'DD')) AS HIRE_DAY
	FROM MEMBER MB
	WHERE SAHIRE IS NOT NULL
    
-- 생성한 뷰를 통해 입사연도별 인원수 구하기
SELECT
  HIRE_YEAR,
  COUNT(*) 인원수
FROM MEMBER_HIREDATES
GROUP BY HIRE_YEAR

[예제2_ 뷰의 보안성을 이해하는 예제]

-- 주민 등록번호 마스킹 처리한 MEMBER테이블을 뷰로 저장
CREATE OR REPLACE VIEW SSN_MASKED_VIEW
AS
	SELECT
	  EIN, NAME, ADDR, 
	  (
	    SUBSTR(SSN, 1, 7)
	    ||
	    '*******'
	  )AS SSN,
	  HIREDAY
	FROM MEMBER

SELECT * FROM SSN_MASKED_VIEW;

>> 결과

■ 인라인 뷰 사용하기

- 테이블/뷰에서 모든 데이터 + 별도의 함수식에 의한 데이터를 불러올 때나,

- 코드를 간소화하고 싶을 때에 인라인 뷰를 사용한다.

SELECT
  임시별칭.*,
  CASE문 또는 수식
FROM (SELECT * FROM 테이블/뷰) 임시별칭

[예제]

-- 맴버 테이블에서 계절만 따로 뽑아오기

SELECT
  MB.*,
  CASE FLOOR(TO_NUMBER(TO_CHAR(HIREDAY,'DD')) / 3) 
     WHEN 1 THEN '봄'
     WHEN 2 THEN '여름'
     WHEN 3 THEN '가을'
     ELSE '겨울'
  END AS 생일
FROM (SELECT * FROM MEMBER) MB   -- MB라는 뷰가 임시로 하나 생성된 것과 같은 원리(실제로 생성x)

■ 뷰(VIEW)의 사용 권한 부여

- DBO(실제 테이블 소유 계정)는 뷰를 통해 DBA(테이블을 사용하는 계정)에게 일부의 데이터만 선택하거나 보안화하여 제공한다.

- 사용 권한을 부여하는 코드는 아래와 같다.

GRANT SELECT ON 뷰이름 TO 계정명

[예제]

-- [새로운 계정에 조회 권한만 부여할 때]
-- SQLPLUS에서 아래와 같이 작성

-- 1. 최고 권한자로 로그인
SQLPLUS SYSTEM/ORACLE;

-- 2. 새로운 계정 생성
CREATE USER MYTEST1 IDENTIFIED BY MYTEST1;

-- 3. 새로운 계정에 연결 권한 부여
GRANT CONNECT TO MYTEST1;

-- 4. 사용할 뷰를 가진 DBO계정으로 연결
CONN TEST1/TEST1;

-- 5. 새로운 계정에 뷰 권한 부여
GRANT SELECT ON SSN_MASKED_VIEW TO MEMBER_TEST;

-- 6. 새로운 계정으로 연결
CONN MYTEST1/MYTEST1;

-- 7. 뷰 조회
SELECT * FROM SSN_MASKED_VIEW;

[출처]

- https://coding-factory.tistory.com/417

- 국비지원 수업 내용 참조

■ DECODE 문

형식 : DECODE(필드(연산식), 비교값1, 결과값1,
                            비교값2, 결과값2,
                            비교값3, 결과값3,
                            기본값)

CREATE TABLE MEMBER
(
   IDX NUMBER(38),
   DEPTNO NUMBER(10),
   NAME CHAR(4 CHAR),
   GENDER CHAR(3 CHAR)
);

/* INSERT 생략 */

SELECT
   IDX, DEPTNO, NAME,
   DECODE(DEPTNO, 10, '총무부',
                  20, '영업부',
                  30, '전산실',
                  40, '관리부',
                  '경리부') AS DEPTNAME;

■ CASE ~ END 문

CASE ~ END

-- 형식 1 : 하나의 조건에 대한 값을 비교하여 결과를 얻는다.
    CASE 필드(연산식)
      WHEN 비교값1 THEN 결과값1
      WHEN 비교값2 THEN 결과값2
      WHEN 비교값3 THEN 결과값3
      ELSE 기본값
    END
    
-- 형식2 : 각각의 조건에 대한 별개의 결과를 얻는다.
   CASE 
     WHEN 조건1 THEN 결과값1
     WHEN 조건2 THEN 결과값2
     ELSE 기본값
   END

-- 형식 1 : 하나의 조건에 대한 값을 비교하여 결과를 얻는다.
        CASE 필드(연산식)
          WHEN 비교값1 THEN 결과값1
          WHEN 비교값2 THEN 결과값2
          WHEN 비교값3 THEN 결과값3
          ELSE 기본값
        END
        
  --CASE ~END문 이용해서 부서명 출력
        
  SELECT 
     SABUN,
     SANAME,
     DEPTNO,
     CASE DEPTNO
       WHEN 10 THEN '총무부'
       WHEN 20 THEN '영업부'
       WHEN 30 THEN '전산실'
       WHEN 40 THEN '관리부'
       ELSE '경리부'
     END AS DNAME
  FROM SAWON
 
 ---형식2 : 각각의 조건에 대한 별개의 결과를 얻는다.
       CASE 
         WHEN 조건1 THEN 결과값1
         WHEN 조건2 THEN 결과값2
         ELSE 기본값
       END

  --CASE ~END 문 이용해서 성별 추출
  SELECT
    GOBUN,
    GONAME,
    GOJUMIN,
    CASE
      WHEN SUBSTR(GOJUMIN,8,1) IN ('1', '3', '5', '7', '9') THEN '남자'
      WHEN SUBSTR(GOJUMIN,8,1) IN ('2', '4', '6', '8', '0') THEN '여자'
    END AS GENDER
  FROM GOGEK;
  
  SELECT
    GOBUN,
    GONAME,
    GOJUMIN,
    CASE
      WHEN TO_NUMBER(SUBSTR(GOJUMIN,8,1)) IN (1,3,5,7,9) THEN '남자'
      WHEN TO_NUMBER(SUBSTR(GOJUMIN,8,1)) IN (2,4,6,8,0) THEN '여자'
    END AS GENDER
  FROM GOGEK;
  
  
 -- Q1. 사원테이블에서 사번, 이름, 입사시즌(입사계절)을 추출
  SELECT
    SABUN AS 사번,
    SANAME AS 이름,
    CASE FLOOR(TO_NUMBER(TO_CHAR(SAHIRE,'MM'))/3)
      WHEN 1 THEN '봄'
      WHEN 2 THEN '여름'
      WHEN 3 THEN '가을'
      ELSE '겨울'
    END AS 입사시즌
  FROM SAWON
  
 -- Q2. 고객테이블에서 고객번호, 고객이름, 고객의 출생 계절 추출
  
  -- [형식1]
  SELECT
    GOBUN,
    GONAME,
    CASE FLOOR(TO_NUMBER(SUBSTR(GOJUMIN,3,2))/3)
      WHEN 1 THEN '봄'
      WHEN 2 THEN '여름'
      WHEN 3 THEN '가을'
      ELSE '겨울'
    END AS 출생계절
  FROM GOGEK;
  
  -- [형식2]
  SELECT
    GOBUN,
    GONAME,
    GOJUMIN,
    CASE 
      WHEN SUBSTR(GOJUMIN,3,2) IN ('03','04','05') THEN '봄'
      WHEN SUBSTR(GOJUMIN,3,2) IN ('06','07','08') THEN '여름'
      WHEN SUBSTR(GOJUMIN,3,2) IN ('09','10','11') THEN '가을'
      ELSE '겨울'
    END 출생계절
  FROM GOGEK

■ 정렬 (ORDER BY)

* ASC = ASCENDING (오름차순), DESC = DESCENDING (내림차순)

■ 그룹 통계 (GROUP BY)

* SELECT ~ FROM 사이에 들어올 수 있는 항목, GROUP BY 컬럼, 통계함수(COUTN,SUM,AGV,MIN,MAX..)

[예제1 _ 부서별 인원수, 급여 합계를 구하는 쿼리]

SELECT 
  DEPTNO 부서번호,
  AVG(SALARY) 급여평균,
  MAX(SALARY) 최고급여,
  MIN(SALARY) 최소급여
FROM MEMBER
GROUP BY DEPTNO
ORDER BY DEPTNO

[예제2 _ 연봉 3000이상인 부서간 성별 인원수, 평균급여]

SELECT
   DEPTNO 부서번호,
   GENDER 성별,
   COUNT(*) 인원수,
   AVG(SALARY) 급여평균
FROM MEMBER
WHERE SALARY >= 3000
GROUP BY DEPTNO, GENDER
ORDER BY DEPTNO, GENDER;

■ SELECT 쿼리 진행 순서

▼ 자세한 진행 순서 [더보기]

■ 서브 쿼리

[단일행 서브쿼리 예시]

-- Q1. 사원 테이블에서 이미자와 동일한 계절에 입사한 직원 추출
SELECT * FROM SAWON 
  WHERE TRUNC(TO_NUMBER(TO_CHAR(sahire,'MM')) / 3, 0) = (
                                                            SELECT
                                                               TRUNC(TO_NUMBER(TO_CHAR(sahire,'MM')) / 3, 0)
                                                               FROM SAWON
                                                               WHERE SANAME = '이미자'
                                                         );


-- Q2. 사원 테이블에서 총 급여 평균 보다 더 급여를 많이 받는 직원 추출
SELECT * FROM SAWON
  WHERE SAPAY > (SELECT AVG(SAPAY) FROM SAWON);


-- Q3. 사원 테이블에서 최초 입사한 직원을 추출
SELECT * FROM SAWON
 WHERE SAHIRE = (SELECT MIN(SAHIRE) FROM SAWON)


-- Q4. 사원 테이블에서 가장 최근 입사한 직원을 추출 
SELECT * FROM SAWON
 WHERE SAHIRE = (SELECT MAX(SAHIRE) FROM SAWON)

-- Q5. 사원 테이블에서 일자를 기준으로 최초입사자, 최근입사자를 모두 추출
SELECT * FROM SAWON
  WHERE SAHIRE = (SELECT MIN(SAHIRE) FROM SAWON)
        or
        SAHIRE = (SELECT MAX(SAHIRE) FROM SAWON);

[다중행 연산자]

-- IN은 같냐를 바꾼 것
SELECT * FROM sawon
 WHERE deptno IN (SELECT deptno FROM sawon WHERE saname = '최불암')
   
-- ANY를 붙이면 다중행 있을 때,
-- 3000이상 받는지 OR 3500이상 받는지
-- (이미자가 둘 이상 있을 떄) 사원 테이블에서 이미자와 같거나 더 큰 급여를 받는 직원 추출
SELECT * FROM sawon
 WHERE sajob >= ANY (SELECT sajob FROM sawon WHERE saname = '이미자')

-- ALL를 붙이면 다중행 있을 때.
-- 3000이상이면서 35000이상이어야한다. 
-- (이미자가 둘 이상 있을 떄) 사원 테이블에서 이미자와 같거나 더 큰 급여를 받는 직원 추출
SELECT * FROM sawon
 WHERE sajob >= ALL (SELECT sajob FROM sawon WHERE saname = '이미자')

[상관쿼리]

- 주 쿼리의 정보를 서브쿼리가 이용해서 추출된 결과를 주쿼리를 선택하는데 이용한다.

-- 사원 테이블에서 각 부서별 최고 급여자를 추출한다고 했을 때

[방법1] 조건을 하나씩 달아준다.
-- >> 아래 코드의 경우, 부서가 증가하거나, 부서번호가 변경되면 일일히 수정해야한다.
SELECT * FROM SAWON
  WHERE SAPAY = (SELECT MAX(SAPAY) FROM SAWON WHERE DEPTNO = 10) and DEPTNO = 10
        OR
        SAPAY = (SELECT MAX(SAPAY) FROM SAWON WHERE DEPTNO = 20) and DEPTNO = 20
        OR
        SAPAY = (SELECT MAX(SAPAY) FROM SAWON WHERE DEPTNO = 30) and DEPTNO = 30
        OR
        SAPAY = (SELECT MAX(SAPAY) FROM SAWON WHERE DEPTNO = 40) and DEPTNO = 40
  ORDER BY DEPTNO ASC;  -- 부서번호별 오름차순

[방법2] 상관쿼리르 사용한다.
-- 위의 SQL문의 문제점(코드가 너무 길다) 해결 
-- >> 상관쿼리 : 주쿼리의 정보를 서브쿼리가 이용해서 추출된 결과를 주쿼리를 선택하는데 이용한다. 
SELECT * FROM SAWON S1
  WHERE SAPAY = (SELECT MAX(SAPAY) FROM SAWON WHERE DEPTNO = S1.DEPTNO)
  ORDER BY S1.DEPTNO ASC;

[출처]

- https://dev-coco.tistory.com/158

- https://myjamong.tistory.com/172

- http://www.gurubee.net/

- 국비지원과정 수업 참조

■ 중복값 제거 DISTINCT

- 하나의 컬럼의 데이터를 뽑아낼 때, 중복값을 제거하고 출력

SELECT DISTINCT AGE FROM MEMBER;

■ 변환 함수

https://webstudynote.tistory.com/60

■ 문자열 함수

■ 숫자 함수

■ 날짜 함수

[SYSDATE를 통해 현재시간, MONTH, HOUR, MINUTE 전 시간 구하기]

-- SYSDTE 예제
SELECT TO_CHAR(SYSDATE,'RRRR-MM-DD HH24:MI:SS') "지금시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1,'RRRR-MM-DD HH24:MI:SS') "하루전지금시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24,'RRRR-MM-DD HH24:MI:SS') "1시간전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24/60,'RRRR-MM-DD HH24:MI:SS') "1분전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24/60/10,'RRRR-MM-DD HH24:MI:SS') "6초전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-(5/24 + 30/24/60 + 10/24/60/60),'RRRR-MM-DD HH24:MI:SS') "5시간 30분 10초전"
  FROM DUAL ;

[SYSTIMESTAMP를 통해 현재시간, MONTH, HOUR, MINUTE 전 시간 구하기]

-- SYSTIMESTAMP
-- SYSTIMESTAMP 함수를 사용하면 현재 일자와 시간(시스템기준)을 얻을 수 있다.
-- SYSTIMESTAMP 예제
SELECT TO_CHAR(SYSTIMESTAMP,'RRRR-MM-DD HH24:MI:SS.FF3')  
  FROM DUAL ;
 
SELECT TO_CHAR(SYSTIMESTAMP,'RRRR-MM-DD HH24:MI:SS.FF9')  
  FROM DUAL ;
 
SELECT TO_CHAR(SYSTIMESTAMP -1/24,'RRRR-MM-DD HH24:MI:SS') "1시간전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSTIMESTAMP -1/24/60,'RRRR-MM-DD HH24:MI:SS') "1분전시간"
  FROM DUAL ;

[내가 살아온 개월 수, 일수]

--Q1. 내가 살아온 월 수 or 일 수
SELECT MONTHS_BETWEEN(TO_DATE(SYSDATE, 'RRRR-MM-DD'),
                      TO_DATE('2000-01-01', 'RRRR-MM-DD')) AS "내가 살아온 월 수"
  FROM DUAL;
  
SELECT TRUNC(MONTHS_BETWEEN(TO_DATE(SYSDATE, 'RRRR-MM-DD'),
                      TO_DATE('2000-01-01', 'RRRR-MM-DD')),1) AS "내가 살아온 월 수"
  FROM DUAL;

SELECT TO_DATE(SYSDATE, 'RRRR-MM-DD') -
       TO_DATE('2000-01-01', 'RRRR-MM-DD') as "내가 살아온 날 수"
  FROM DUAL;

■ 집계 함수

■ NULL과 관련된 함수

CREATE TABLE MEMBER
(
   DEPTNO NUMBER(5),
   NAME CHAR(3 CHAR),
   RECDATE DATE,
   SECTOR VARCHAR2(100)
)

SELECT  
    NAME, RECDATE, 
    NVL(SECTOR, '미지정') AS SECTOR, 
    NVL2(SECTOR, '지정 완료', '미지정') AS SECTOR
FROM MEMBER

[출처]

- http://www.gurubee.net/

- 뉴렉처 강의

- 국비 수업 과정 참조

■ 연산자의 종류

- 산술을 제외하고, 관계/논리/기타 모두 문자열에 사용 가능하다. 
**산술은 문자열에 사용 불가하다.

■ 기본 필드와 연산 필드

--heading : select의 결과로 추출된 임시 컬럼명
--연산 필드 : 기본 필드의 연산에 의해서 형성된 필드

select
   sabun as 사번,       -- 기본 필드
   saname 사원명,       -- as 생략가능 
   --sajob as 직 급,    -- 에러! 공백을 띄어서 표준명명법에 어긋남  
   sajob as "직 급",    -- OK 
   sapay,
   sapay * 0.1 as bonus  -- 연산 필드
from sawon

■ 문자의 결합 ||

select (문자데이터)칼럼명 || '추가하고자 하는 문자열' from 테이블명

■ 패턴 비교 연산자 : LIKE, %, _

[1] % (나 다음 모든문자)

select * from 테이블
  where (문자데이터)칼럼명 like '김%'   -- 맨앞글자가 김인 데이터(그 뒤는 길이도 상관없음)

[2] _  (모든 문자에서 1개)

select * from 테이블
  where (문자데이터)칼럼명 like '____-1%' 
  
  -- 앞에 6글자는 아무것이나 와도 되나, 그 뒤는 '-1'이 오는 데이터만 추출

■ 정규식을 이용한 패턴 비교(REGEX_LIKE)

▼  연산자 예시 *출처 : 뉴렉처  

[1] 산술 연산 / 문자열 결합 예시

--덧셈은 숫자만 더하기 때문에 숫자 + 문자면 숫자로 더해진다 
SELECT 1+'3' FROM DUAL;
--문자와 문자를 더하기
SELECT 1 || '3' FROM DUAL;

--Q. 모든 회원의 이름을 조회하시오. 단 이름은 ID를 붙여서
SELECT NAME || '(' || ID || ')' "이름(ID)" FROM MEMBER;

>> 마지막 Q결과

[2] 비교연산자 예시

-- 게시글 중에서 작성자가 '홍길동'인 게시글만 조회
SELECT * FROM NOTICE WHERE WRITER_ID = '홍길동';

-- 게시글 중에서 조회수가 100이 넘는 글만 조회
SELECT * FROM NOTICE WHERE HIT > 100;

-- 게시글 중에서 내용을 입력하지 않은 게시글을 조회
-- ★ NULL의 경우 = 연산자 사용 불가. 반드시 IS를 써야한다.
SELECT * FROM NOTICE WHERE CONTENT IS NULL;

[3] 논리연산자 예시

-- 조회수가 0, 1, 2인 게시글을 조회하시오.
SELECT * FROM NOTICE WHERE HIT IN (0, 1, 2);
SELECT * FROM NOTICE WHERE HIT = 0 OR HIT = 1 OR HIT = 2;
SELECT * FROM NOTICE WHERE HIT BETWEEN 0 AND 2;
SELECT * FROM NOTICE WHERE 0 <= HIT AND HIT <= 2;

--조회수가 0, 2, 7인 게시글을 조회하시오.
SELECT * FROM NORICE WHERE HIT IN (0, 2, 7);
SELECT * FROM NOTICE WHERE HIT = 0 OR HIT = 2 OR HIT = 7; -- 비효율적

--조회수가 0, 2, 7이 아닌 게시글을 조회하시오.
SELECT * FROM NOTICE WHERE HIT NOT IN(0, 2, 7);

[4] 패턴 비교 연산자 예시

-- 회원 중에서 '박'씨 성을 조회하시오
SELECT * FROM MEMBER WHERE NAME = '박%'; -- 박% 이라는 문자를 찾는다
SELECT * FROM MEMBER WHERE NAME LIKE '박%';

-- 회원 중에서 '박'씨이고 이름이 외자인 회원을 조회하시오.
SELECT * FROM MEMBER WHERE NAME LIKE '박_';

-- 회원 중에서 '박'씨 성을 제외한 회원을 조회하시오.
SELECT * FROM MEMBER WHERE NAME NOT LIKE '박%';

-- 회원 중에서 이름에 '도'자가 들어간 회원을 조회하시오.
SELECT * FROM MEMBER WHERE NAME LIKE '%도%';

[예시 코드 출처]

- 뉴렉쳐 강의

■ 제약조건(Constraint)이란?

- 데이터베이스에 들어있는 데이터의 무결성(정확성/일관성)을 보장하기 위해

  부정확한 자료가 데이터베이스 내에 저장되는 것을 방지하는 차원에서 걸은 제약 조건

- 모든 제약조건은 데이터사전(Dictionary)에 저장된다.

- 제약조건에 사용자 지정 이름을 달면 Constraint를 쉽게 참조할 수 있다.

  *사용자 지정 이름 생성시, 표준 객체 명명법을 따르는 것이 좋다. 

■ 제약조건의 종류

★ 무결성의 종류

■ 제약조건 조회 - 딕셔너리에서 조회 

※ 유의할 점! 딕셔너리 내의 모든 정보는 대문자로 저장된다.

   따라서, where문을 통해 특정 테이블을 지정할 때는 꼭 upper('   ')를 쓰도록 하자.

[1] CMD에서 아래와 같이 제약조건에 관한 데이터들 확인

desc user_constraints

>> 결과

[2] 찾고자 하는 정보에 대한 명령어 입력

select owner, constraint_name, constraint_type, table_name from user_constraints

>> 결과

[3] 특정 테이블에 대한 정보만 뽑아서 보기

select owner, constraint_name, constraint_type, table_name from user_constraints
  where table_name = upper('userInfo3')

>> 결과

■ 제약조건 생성

[방법1] 테이블 생성 시 제약에 대한 명칭 없이 조건만 다는 방법

create table userInfo
(
    name      varchar2(100) not null,
    nickname  varchar2(100) null       
)
/

[방법2] 테이블 생성 시 제약에 대한 명칭과 함께 조건을 다는 방법

create table userInfo2
(
    name      varchar2(100) not null,
    id        varchar2(100) not null unique,
    password  varchar2(100) not null,
    
    constraint unique_userInfo2_password unique(id)
)
/

[방법3] 테이블 생성 후 제약에 대한 명칭과 함께 조건을 다는 방법  ** 가장 많이 사용되는 코드이다.

create table userInfo3
(
    name      varchar2(100) not null,
    id        varchar2(100) not null unique,
    password  varchar2(100) not null
)
/

alter table userInfo3 
  add constraint unique_userInfo3_password unique(password);

더보기

방법2,3과 같이 제약조건에 사용자지정 명칭을 다는 경우

이클립스 안에서 에러 메세가 나타날 때,사용자 지정 명칭을 확인할 수 있다.

 

[예시] 괄호 안에,   계정.사용자지정명칭   양식으로 나타난다.

※ 제약 조건의 명칭은 모두 대문자로 저장된다.

■ 제약조건 삭제

alter table 테이블명 drop constraint 제약조건명

  NOT NULL & UNIQUE  

create table userInfo3
(
    name      varchar2(100) not null,
    id        varchar2(100) not null unique,
    password  varchar2(100) not null
)

alter table userInfo3
  add constraint unique_userInfo3_password unique(password);
  
insert into userInfo3 values('홍길동','hong123','1234'); -- OK
insert into userInfo3(name) values('나길동'); -- 에러! 데이터 미삽입 : 널 무결성 위배 
                                            --ORA-01400: NULL을 ("TEST1"."USERINFO3"."ID") 안에 삽입할 수 없습니다
insert into userInfo3 values('다길동','hong123','2345'); -- 에러! 중복값 추가 : 고유 무결성 위배
                                                       -- ORA-00001: 무결성 제약 조건(TEST1.SYS_C004094)에 위배됩니다

위 코드를 이클립스에서 하나씩 [ALT]+[X]해서 실행해보면 먼저, 아래와 같이 에러 메세지가 나타난다. 

그 다음으로 나타나는 에러 메세지는 아래와 같은데, unique로 id에 중복을 불허했는데 중복된 id를 넣어버렸다는 의미

  CHECK  

- 문법

alter table 테이블명
  add constraint 제약조건명 check(조건절)

- 실제 코드

alter table userInfo3
  add constraint check_userInfo3_mat check(mat between 0 and 100)

  DEFAULT  

- 문법

alter table 테이블명 
  modify 칼럼헤딩 타입 default 'UNKNOWN'

- 실제코드

alter table userInfo3 
  modify name varchar2(100) default 'UNKNOWN'

  Primary Key  

※ 기본키를 지정해주면 해당키 칼럼에 not null과 unique제약이 걸리며, 해당키 칼럼은 테이블의 index가 된다. 

※ 모든 테이블은 키 무결성 원칙을 지키기 위해 최소 1개의 primary key를 가지고 있다.

[1] 하나의 키를 지정하는 방법

- 문법

alter table 테이블명
  add constraint 제약조건명 primary key(칼럼명);

- 실제코드

alter table tb5
  add constraint pk_tb5_idx primary key(idx);

alter table 테이블명
  add constraint 제약조건명 primary key(칼럼명,칼럼명)

alter table tb5
  add constraint pk_tb55_idx_regdate primary key(idx,regdate)

  Foreign Key  

※ 테이블을 정규화하여 두 개의 테이블(A테이블, B테이블)로 나누었다고 가정

   -  A테이블의 XXX칼럼이 A테이블의 index(primary key)이며, B테이블에도 XXX칼럼이 있다고 할 때,

   -  B테이블의 XXX칼럼을 foreign key로 주고 A테이블의 primary key를 참조하게 한다.

- 문법

-- A테이블의 기본키
alter table A테이블
  add constraint 제약명칭 primary key(XXX칼럼명)

-- B테이블의 외래키
alter table B테이블
  add constraint 제약명칭 foreign key(XXX칼럼명) references A테이블명(XXX칼럼명);

- 실제 코드

더보기

[원본 테이블 : userInfo3]

-- 원본 테이블 ) userInfo3
create table userInfo3
(
    SID       int,           
    name      varchar2(100) not null,
    id        varchar2(100) not null unique,
    password  varchar2(100) not null,
    eng       int           check(eng between 0 and 100),
    mat       int           check(mat between 0 and 100)
)

alter table userInfo3 
  add constraint pk_userInfo3_SID primary key(SID)

insert into userInfo3 values(1001, '김길동', 'kim1234', '1234', 88, 100);
insert into userInfo3 values(1002, '나길동', 'nah1234', '2345', 78, 90);
insert into userInfo3 values(1003, '다길동', 'dah1234', '2345', 98, 50);

select * from userInfo3

>> 결과

[A테이블 : userInfoRevised1]

-- A테이블 ) userInfoRevised1
create table userInfoRevised1
(
    SID       int,           
    name      varchar2(100) not null,
    id        varchar2(100) not null unique,
    password  varchar2(100) not null
)

alter table userInfoRevised1
  add constraint pk_userInfoRevised1_SID primary key(SID)

insert into userInfoRevised1 values(1001, '김길동', 'kim1234', '1234');
insert into userInfoRevised1 values(1002, '나길동', 'nah1234', '2345');
insert into userInfoRevised1 values(1003, '다길동', 'dah1234', '2345');

select * from userInfoRevised1

>> 결과

[B테이블 : userInfoRevised2]

-- B테이블 : userInfoRevised2
create table userInfoRevised2
(
    idx       int,
    SID       int,           
    name      varchar2(100) not null,
    eng       int           check(eng between 0 and 100),
    mat       int           check(mat between 0 and 100)
)

alter table userInfoRevised2 
  add constraint pk_userInfoRevised2_idx primary key(idx)
  
alter table userInfoRevised2
  add constraint fk_userInfoRevised2_SID foreign key(SID) references userInfoRevised1(SID)

insert into userInfoRevised2 values(1, 1001, '김길동', 88, 100); -- OK
insert into userInfoRevised2 values(2, 1002, '나길동', 78, 90);  -- OK
insert into userInfoRevised2 values(3, 1005, '다길동', 98, 50);  -- 에러! 없는 SID 코드

>> 결과

[출처]

http://www.gurubee.net/lecture/1013

https://programming119.tistory.com/225

https://coding-factory.tistory.com/221

[참조]

https://ltk3934.tistory.com/67

https://all-record.tistory.com/151

국비지원 수업 내용 참조