SQL 정리5

경로

• . : 현재 폴더 위치
• . . : 상위 폴더
• 절대 경로 : 어떠한 웹페이지나 파일이 가지고 있는 고유한 경로
• ex) D:\A_TeachngMaterial\2.Oracle
• 상대 경로 : 현재 위치한 곳 기준
• ex) D:\A_TeachngMaterial\2.Oracle -- 절대 경로를 줄여서 사용
• ../1.BasicJava -- 경로 이동을 줄여서

디렉토리 : 절대 경로를 갖고있는 폴더

 

2021-0611-04) 기타 자료형(2진 자료형)

• 2진 데이터를 저장하기 위한 자료형
• BLOB, BFILE, RAW, LONG RAW 등이 제공

1. RAW 가. 상대적으로 작은 2진 자료 저장 나. 인덱스 처리 가능 다. 오라클에서는 변환 또는 해석작업을 수행하지 않음 -- 중요 // 입력된 그대로 화면에 출력 라. 최대 2000BYTE 까지 저장 가능 마. 16진수와 2진수 저장 가능

사용형식)
    컬럼명 RAW(크기);

사용예)
CREATE TABLE TEMP07(
    COL1 RAW(1000),
    COL2 RAW(50)
);

INSERT INTO TEMP07 VALUES('1100110100110111', HEXTORAW('CD37')); -- 16진수를 2진수로 표현, 16진수를 RAW로 변환(HEXTORAW 사용)
INSERT INTO TEMP07 VALUES('1100110100110111', 'CD37');
/* JAVA의 경우 : "12" + 27 -> "12" + "27" = 1227 출력 = 문자 위주
    SQL의 경우 : "12" + 27 -> 12 + 27 = 39 출력
                 "P12" + 27 -> 오류 발생 */

SELECT * FROM TEMP07;

2. BFILE - 데이터베이스에는 2진 자료 경로와 파일정보를 갖고있음 가. 이진자료 저장 나. 대상이 되는 2진자료는 데이터베이스 밖에 저장하고, 데이터베이스에는 경로와 파일명만 저장 다. 4GB까지 저장 가능

사용형식)
    컬럼명 BFILE;
      - 디렉토리 별칭은 30BYTE, 파일명은 256BYTE까지 사용가능

사용예) 저장한 SUNSIM.jpg를 BFILE로 설계된 테이블에 저장하시오.
   (1) 테이블 생성
    CREATE TABLE TEMP08(
        COL1 BFILE );

   (2) 디렉토리 객체 생성
    CREATE DIRECTORY 객체명 -- 경로를 갖고있는 별칭
        AS 절대경로명

    CREATE DIRECTORY TEST_DIR
        AS 'D:\\\\A_TeachngMaterial\\\\2.Oracle';

    (3) 자료저장
      INSERT INTO TEMP08 VALUES (BFILENAME('TEST_DIR', 'SUNSIM.jpg'));

    (4) 자료 확인하기
      SELECT * FROM TEMP08;

 

3. BLOB - 데이터베이스에 2진 자료 저장 가. 이진자료 저장 나. 대상이 되는 2진 자료가 테이블 내부에 저장 다. 4GB까지 저장 가능

사용형식)
  컬럼명 BLOB;

사용예)
  (1) 테이블 생성
  CREATE TABLE TEMP09(
    COL1 BLOB,
    COL2 BLOB
  );

  (2) 일반 SQL문으로 데이터 삽입 불가능(익명 블록 사용)
  DECLARE
    L_DIR VARCHAR(20):='TEST_DIR';
    L_FILE VARCHAR2(30):='SUNSIM.jpg';
    L_BFILE BFILE;
    L_BLOB BLOB;
  BEGIN
    INSERT INTO TEMP09(COL1) VALUES (EMPTY_BLOB())
        RETURN COL1 INTO L_BLOB;

  L_BFILE:=BFILENAME(L_DIR, L_FILE);
  DBMS_LOB.FILEOPEN(L_BFILE, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.LOADFROMFILE(L_BLOB, L_BFILE, DBMS_LOB.GETLENGTH(L_BFILE));
  DBMS_LOB.FILECLOSE(L_BFILE);

  COMMIT;
 END;

2021-0614-01)
SELECT 컬럼명1 [AS] ["][별칭]["][,] -- 열을 담당함 필수, 마지막으로 실행
컬럼명2 [AS] ["][별칭]["][,] -- 사용시 ""사용
:
컬럼명n [AS] ["][별칭]["]
FROM  테이블명 [별칭] --필수, 1번째로 실행 (TABLE, VIEW가 올 수 있음) SUB QUERY가 올 경우 결과는 반드시 TABLE, VIEW 둘 중 한개가 와야함
[WHERE 조건] -- 2번째, 일반적인 조건 사용
[GROUP BY 컬럼명[,컬럼명,...]] -- 3번쨰
[HAVING 조건] -- 3번째, 그룹함수 사용
[ORDER BY 컬럼명 | 컬럼인덱스 [ASC][DESC][,컬럼명 | 컬럼인덱스 [ASC][DESC],...]

SELECT A.FIRST_NAME||' '||A.LAST_NAME -- 열
FROM HR.EMPLOYEES A
WHERE A.DEPARTMENT_ID>=60; -- 행

 

1. 연산자

  가. 산술연산자

  나. 사칙연산자(+, -, /, *)

 

사용예) 회원테이블에서 회원들의 성별을 구분하고 여성회원 중 40대 이상인 회원을 조회하시오
Alias는 회원번호, 회원명, 주민번호, 나이, 성별
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명, MEM_REGNO1 ||'-'|| MEM_REGNO2 AS 주민번호,
EXTRACT (YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) AS 나이,  -- 현재 년도 - 생일
CASE WHEN SUBSTR(MEM_REGNO2, 1, 1) = '2' OR
SUBSTR(MEM_REGNO2, 1, 1) = '4' THEN
'여성회원'
ELSE
'남성회원'
END AS 성별
FROM MEMBER
WHERE (SUBSTR(MEM_REGNO2, 1, 1) = '2' OR SUBSTR(MEM_REGNO2, 1, 1) = '4') -- 1,1 1글자만 추출하여 2 OR 4일 경우 여성
AND EXTRACT (YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) >= 40; -- 현재년도 - 생년월일 계산 후 40살 이상인 경우

회원정보 수정)

1. 'd001'의 주민등록번호를 '460409'에서 '010409'로 생년월일을 '1946/04/09'에서 '20010409'로 변경
2. 'k001'의 주민등록번호를 '620123'에서 '000123'로 생년월일을 '1962/01/23'에서 '20000123'로 변경
3. 'v001'의 주민등록번호를 '520131'에서 '030131'로 생년월일을 '1952/01/31'에서 '20030131'로 변경

   UPDATE MEMBER -- K001 주민등록번호 변경
   SET MEM_REGNO1 = '000123',
   MEM_REGNO2 = '3449311',
   MEM_BIR = TO_DATE('20000123')
   WHERE MEM_ID = LOWER('K001');
   

   UPDATE MEMBER -- V001 주민등록번호 변경
   SET MEM_REGNO1 = '030131',
   MEM_REGNO2 = '4402712',
   MEM_BIR = TO_DATE('20030131')
   WHERE MEM_ID = LOWER('V001');
   

   COMMIT;
   

   SELECT * FROM MEMBER;
   

   SELECT MEM_ID AS 회원번호,
   MEM_NAME AS 회원명, MEM_REGNO1 ||'-'|| MEM_REGNO2 AS 주민번호, -- ||'-'|| : 숫자 사이에 문자 삽입
   EXTRACT (YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) AS 나이,  -- 현재 년도 - 생일
   CASE WHEN SUBSTR(MEM_REGNO2, 1, 1) = '2' OR
   SUBSTR(MEM_REGNO2, 1, 1) = '4' THEN
   '여성회원'
   ELSE
   '남성회원'
   END AS 성별
   FROM MEMBER
   WHERE EXTRACT (YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) <= 25; -- 현재년도 - 생년월일 계산 후 25살 이하인 경우
   

    UPDATE MEMBER -- D001 주민등록번호 변경 SET MEM_REGNO1 = '010409', MEM_REGNO2 = '4000000', MEM_BIR = TO_DATE('20010409') WHERE MEM_ID = LOWER('D001'); -- LOWER : 소문자로 바꾸라는 뜻 대문자로 바꿀 시 UPPER

1. 관계연산자
   • 크기의 비교에 사용(>, <, >=, <=, =, !=(NOT=))
2. 논리연산자
   • NOT, AND, OR
3. 기타연산자 가) IN

• 질의 탐색을 위해 사용될 둘 이상의 표현식을 지정
• ANY, SOME 연산자로 변경 가능(관계연산자 ANY (SOME) => IN) 사용형식) expr IN (값1, 값2, ... 값n)
• expr이 값1 ~ 값n 중에 어느 하나와 일치하면 전체가 참이됨
• OR 연산자나 =ANY, =SOME으로 치환 가능
• SELECT 12345/113 FROM DUAL;

함수 : 모든 사람들이 똑같은 목적으로 사용 SELECT, WHERE 사용가능

1. • 미리 작성하고 컴파일하여 실행 가능한 상태의 서브프로그램
   • 네트워크의 트래픽을 감소시킴

FUNCTION : 반환값 없음(SELECT, WHERE에 쓸 수 없음)

PROSISER : 반환값 있음

•   집계함수 GROUP BY 절 사용해야함
•   HAVING 절

 

NULL 처리함수

NULL인가 아닌가 판별할 때

NULL값인지 아닌지 판별 후 NULL값이면 다른 문자로 대체