23-01-30(1) 데이터베이스 (데이터 타입, Where절을 이용한 조건 검색)

23-01-30 09:30~13:30분까지의 오전수업 진행

 

 

 

 

1. char 데이터 타입
고정 길이의 문자열을 저장, 최대 2000바이트까지 저장가능
지정된 길이보다 짧은 데이터가 입력될 경우 나머지는 공백으로 채워짐

※ 특징
데이터 입력시 사용자가 데이터를 입력하지 않으면 null이 입력
지정된 길이보다 긴데이터가 입력되면 오류발생
주소 데이터와 같은 편차가 심한 데이터를 입력할 때 사용하면 저장공간이 낭비될 수 있음
주민등록번호와 같이 길이가 일정하거나 비슷한 경우에 사용하는것이 좋음.

 

2. VARCHAR2 데이터 타입

가변길이의 문자열을 저장하기 위해 사용하는 데이터 타입, 최대 4000바이트 저장 가능

지정된 길이보다 짧은 문자열이 입력되면 뒷부분은 NULL로 처리되어 저장공간을 낭비하지 않음.

 

※ 특징

데이터 입력시 사용자가 값을 입력하지 않으면 NULL이 입력

지정된 길이보다 긴 데이터가 입력되면 데이터가 입력되지않고 오류발생

입력될 데이터의 편차가 심하거나 NULL이 많이 입력되는 경우에 사용하는것이 효율적

실무에서 CHAR데이터 타입보다 VARCHAR2를 많이 사용함

VARCHAR라고 사용해도 VARCHAR2로 바뀜

 

 

3. 문자형 데이터 타입의 비교방법

 1)CHAR와 CHAR 데이터 타입의 비교방법 (형변환이 가능함)

 2) CAHR와 VARCHAR2 데이터 타입의 비교방법

※ 비교방법

 1) 문자상수는 문자 데이터 타입으로 내부 변환되어 문자타입의 비교 방식에 의해 처리

 

CREATE TABLE EX_TYPE
    (C CHAR(10), --C라는 이름의 컬럼
    V VARCHAR2(10) -- V라는 이름의 컬럼
    );
-- 길이가 동일하고 타입이 다른 칼럼을 가진 테이블 생성
    
SELECT * FROM EX_TYPE;

INSERT INTO EX_TYPE VALUES ('SQL', 'SQL');

SELECT * FROM EX_TYPE
WHERE C = 'SQL     ';

SELECT * FROM EX_TYPE
WHERE V = 'SQL';

SELECT * FROM EX_TYPE
WHERE C = V || '       '; -- 조건식 (BOOLEAN타입 / 참OR거짓으로 나옴)

-- 실무에서는 VARCHAR2의 비교방식으로만 사용함 --102페이지 보고 좀 더 참고해서 작성

☆예제 보강 필요

글자와 글자사이의 공백은 줄여지면 안됨

 

 

3. NUMBER 데이터 타입

숫자를 저장하며, 가변길이 데이터 타입으로 최대 38자리까지 저장가능

저장가는한 숫자의 범위는 1.0*10(-130승) ~ 96.9... * 10(125승)

소수는 NUMBER(PRECISION, SCALE)형식으로 입력

지정된 자리 이하에서 반올림 되어 저장

PRECISION : 소숫점을 포함하는 전체 자리 수

SCALE : 소숫점 이하 자리 수

precision 을지정하지않고숫자를입력하는경우
–입력되는숫자값의크기만큼저장공간이할당됨
•scale 을지정하지않고소수점을입력하는경우
–소수점이하는반올림되어정수값맊저장됨

 

4. DATE 데이터 타입

1) 개요

세기, 년도, 월, 일, 시간, 분, 초의 날짜와 시간정보를 저장하기 위한 타입

7비트의 고정길이 필드에 저장 (공간이 작아 효율적임)

날짜나 시간에 대한 연산이나 이력 정보를 관리하기 위해 사용

퍼스널 오라클의 기본날짜 형식 : YY/MM/DD형식 (A와,D(DATE)를 구분하는게 좋음)

DAY OF MONTH, DAT OF WEEK 등 알아두는게 좋음

DATE 데이터타입은 ORACLE이 운영체제의 국가코드를 따라감

 

2) 기능

 1) TO_DATE 함수 사용가능

     문자형태로 저장된 데이터를 날짜 형식으로 변환(형변환 사용가능, 문자 -> 날짜 / 날짜 -> 문자) 

 2) SYSDATE 사용가능 (함수는 아님)

     시스템의 현재 날짜와 시간을 반환하는 함수

     SYSDATE를 확인한다는것은 내 시스템의 시간함수를 알아내는것

 주의

      날짜데이터 입력시 월, 일을 지정하지 않으면 입력되는 시점의 기본값이 입력됨 (해당월과 1일이 입력, 시간은 00시로 입력)

      시간 정보가 입력된 경우에는 '=' 비교시 시간정보도 함께 비교됨

 

5. LOB 데이터 타입

 1) 개요

텍스트,그래픽, 이미지, 동영상, 사운드 등 같이 구조화 되지 않은 대용량 텍스트나 멀티미디어 데이터를 저장하기 위한 데이터타입, 최대 4GB까지 저장

 

 2) 종류

     1) BLOB

     2) CLOB

     3) NCLOB

     4) BFILE

 

6. ROWID 데이터 타입

 1) 개요

     - 테이블에서 행의 위치를 지정하는 논리적인 주소 값

     - 데이터베이스 전체에서 중복되지 않는 유일한 값

     - ROWID는 메모리에서 관리됨

     - 읽기전용의 주소(자바)의 개념이랑 일치함

     - 실무에서 사용안함

     - 행의주소(행의ID)

     - 트리구조를 사용함

 

  ※ 참고

 트랜잭션(Transaction)은 여러개의 행위가 하나의 동작처럼 수행을 하는것

 예) 거래, 각종 예매, 수강신청 등

 

 2) ORACLE ROWID의 변화

     USER 프로그램 내부에 하나이상의 트랜잭션이 존재한다면, ROWID가 변화할 수도 있음

     

7. [참조]PSEUDO-COLUMN (수도칼럼)

 참조만 될 뿐 데이터 베이스에 따로 저장되지않음

 ORACLE의 PSEUDO-COLUMN은 테이블의 실제적인 컬럼은 아니므로 테이블의 DESCRIBE을 통해서는 보이지 않지만 매우 유용하게 사용

  - CURRVAL(현재값), NEXTVAL(다음값) (sequence number generator)

  - LEVEL (계층의 단계)

  - ROWNUM 

 

8. ROWNUM

 QUERY에 의해 추출된 각 행의 부여된 일련번호 (오로지 읽기전용)

 쿼리에서 추출되는 결과 행들에 1부터 시작하는 순차 값을 할당

 임의의 순서로 추출된 행에 대해서 ORDER BY절에 의한 정렬을 하기 전에, 즉SELECT절 단계에서 부여

 ROWNUM은 SELECT절에서 정해지는데, 먼저 실행되는 WHERE절에서 ROWNUM을 조건으로 사용할 수 없다.

 

 

WHERE절의 ROWNUM은 '<', '<='조건을 주로 사용하며, '='조건은 WHERE ROWNUM=1인 경우에만 예외적으로 사용할 수 있다.

ROWNUM은 몇 건을 지정하는 목적이지 몇번째 건을 지정하는 용도가 아니다.

 

9. TIMESTAMP 데이터 타입

 - 나노세컨드까지 사용가능

 - DATE타입의 확장된 형태로서 10억분의 일초 단위 까지 표현 가능

 - MILLI SECOND의 기본값은 6자리, 최대 9자리까지 사용가능

 

 2) 종류

   - TIMESTAMP WITH TIME ZONE

   - TIMESTAMP WITH LOCAL TIME ZONE

보통 국제화 작업을 하지 않아 넘어가면 된다.

 

10. DUAL 테이블

   듀얼 테이블이란 자동생성테이블 이다.

   듀얼테이블은 VARCHAR2(1)으로 정의된 DUMMY라는 하나의 열이 있으며 값을 가지는 하나의 행(X)도 포함되어 있다.

SELECT 3*6*7*8 FROM STUDENT WHERE ROWNUM = 1;
-- 예상행개수는 대상 테이블의 행개수와 일치한다. 그러므로 DUAL을 사용하여
-- 결과값을 만들어서 보면 된다.
SELECT 3*6*7*8 FROM DUAL;
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH24:MI:SS') FROM DUAL;

 

SYSDATE를 처리하는데 드는 비용

비용

 

11. 데이터타입의 결정 전략

 1) 응용프로그램 개발 시 오류감소

 2) SQL명령문의 검색 성능에 영향

 3) 문자형데이터 (가변인지, 고정인지 고려)

 4) 데이터가 숫자인 경우 (숫자데이터타입 또는 문자형 데이터 타입으로 지정할지 고려)

   - 대부분 숫자타입이지만, 문자열이 들어가는 비밀번호, 전화번호 등 같은 경우는 문자형 데이터 타입을 써야함

 5) 데이터가 날짜 타입인 경우 (문자 데이터 타입으로 지정하는 편이 효율적)

 

 

※ WHERE절을 이용한 조건검색

 

1. WHERE절 (JAVA의 IF라고 생각하면 훨씬 쉽다,)

 1) 테이블에 저장된 데이터 중에서 원하는 데이터만 선택적으로 검색하는 기능

 2) WHERE절의 조건문은 칼럼 이름, 연산자, 상수, 산술 표현식을 결합하여 다양한 형태로 표현 가능

 3) 문자와 날짜타입의 상수값은 작은 따옴표로 ('') 묶어서 표현하고 숫자는 그대로 사용

 4) 상수값에서 영문자는 대소문자를 구별

 5) 비교, 논리 연산자를 WHERE절에서 사용 가능함.

 

2. WHERE를 사용한 조건검색

 1) 사용 예

       학생 테이블에서 1학년 학생만 검색하여 학번, 이름, 학과번호를 출력

SELECT STUDNO, NAME, DEPTNO
FROM STUDENT 
WHERE GRADE = '1';

질의 결과

 2) 비교연산자를 사용한 조건검색

   WHERE절에서 숫자, 문자, 날짜의 크기나 순서를 비교하는 연산자(BOOLEAN타입)

 = 같다

 !=, <> 같지않다

 >  크다

 >= 크거나 같다

 < 작다

 <= 작거나 같다.

 

 3) 학생테이블에서 몸무게가 70KG이하인 학생만 검색하여 학번, 이름, 학년, 학과번호, 몸무게를 출력하여라

 

SELECT STUDNO, NAME, GRADE, DEPTNO, WEIGHT
FROM STUDENT
WHERE WEIGHT <=70;

질의 결과

TIP. 위 예제에서 먼저 SELECT * FROM STUDENT; 로 어떤걸 검색해야 하는지 확인 후 절을 하나하나 늘려나가는 습관을 들이자.

 

4) 논리연산자를 이용한 조건 검색

 - WHERE절에서 여러개의 조건을 결합할 경우

 - AND, OR, NOT과 같은 논리 연산자를 사용

AND : 모든 조건이 참일때, 참값을 변환

 OR : 모든 조건 중에서 하나가 참일때, 참값을 변환

 NOT : 조건과 반대되는 결과를 반환

 

5) AND논리연산자를 이용한 조건검색

학생테이블에서 1학년이면서 몸무게가 70KG이상인 학생만 검색하여 이름, 학년, 몸무게, 학과번호 출력

SELECT STUDNO, NAME, WEIGHT, DEPTNO
FROM STUDENT
WHERE GRADE='1'
AND WEIGHT >=70;

질의 결과

6) OR논리연산자를 이용한 조건검색

학생테이블에서 1학년이거나 몸무게가 70KG이상인 학생만 검색하여 이름, 학년, 몸무게, 학과번호 출력

SELECT 
    NAME,
    GRADE,
    WEIGHT, 
    DEPTNO
FROM 
    STUDENT
WHERE 
    GRADE='1'
OR 
    WEIGHT >=70;

7) NOT논리연산자를 이용한 조건검색

학생테이블에서 학과번호가 101이 아닌 학생의 학번과 이름과 학과번호 출력

 

SELECT STUDNO, NAME, WEIGHT, DEPTNO
FROM STUDENT
WHERE NOT DEPTNO = 101;

SELECT STUDNO, NAME, WEIGHT, DEPTNO
FROM STUDENT
WHERE DEPTNO != 101;

질의 결과

 

 

8) SQL연산자를 이용한 조건 검색

 SQL 연산자는 SQL언어에만 제공

 SQL은 0부터가 아닌 이름 부터

 이상이하

BETWEEN은 범위 (날짜에도 해당, 문자에도 사용가능함, 이상~이하로 가능)

IN : OR연산자를 나열한것 

NULL과 연산하면 대부분 FALSE가 출력됨

 

9) BETWEEN 연산자를 이용한 조건 검색

몸무게가 50KG~70KG 사이인 학생의 학번, 이름, 몸무게 출력

SELECT STUDNO, NAME, WEIGHT
FROM STUDENT
WHERE WEIGHT BETWEEN 50 AND 70;

질의 결과

 

학생 테이블에서 81년에서 83년도에 태어난 학생의 이름과 생년월일 출력

SELECT NAME, BIRTHDATE
FROM STUDENT
WHERE BIRTHDATE BETWEEN '81/01/01' AND '83/12/31';

SELECT NAME, BIRTHDATE
FROM STUDENT
WHERE TO_CHAR(BIRTHDATE, 'YY') BETWEEN 81 AND 83;

SELECT NAME, BIRTHDATE
FROM STUDENT
WHERE TO_CHAR(BIRTHDATE, 'YY') IN(81,82,83);

질의 결과

10) IN(A,B,C ...)연산자

 -

 

 

 

 

 ※ IN연산자를 이용한 조건 검색

IN연산자를 사용하여 102번 학과와 201번 학과 학생의 이름, 학년, 학과번호 출력

SELECT NAME, GRADE, DEPTNO
FROM STUDENT
WHERE DEPTNO IN(102,201);

SELECT NAME, GRADE, DEPTNO
FROM STUDENT
WHERE DEPTNO != 101;

질의 결과

11 ) LIKE 연산자 (~같은)

 칼럼에 저장된 문자열중에서 LIKE연산자에서 지정한 문자패턴과 부분적으로 일치하면 참이되는 연산자

※ LIKE연산자를 이용한 조건 검색

학생테이블에서 성이 '김'씨인 학생의 이름, 학년, 학과번호 출력

 

SELECT NAME, GRADE, DEPTNO
FROM STUDENT
WHERE NAME LIKE '김%';

3번째줄 '김%'에서 김__ 언더바를 2개 붙이면 김XX 3글자인 학생 검색 만약 김_ 언더바 하나면 김X 2글자인 학생 검색

 

이름이 3글자 성은 '김'씨고 마지막글자가 '영'으로 끝나는 학생의 이름,학년,학과번호 출력

SELECT NAME, GRADE, DEPTNO
FROM STUDENT
WHERE NAME LIKE'김_영';

질의 결과

 

12) ESCAPE 옵션

 ESCAPE옵션은 LIKE연산자에서 사용하는 와일드문자(%,_)자체를 포함하는 문자열을 검색할 때 와일드문자를 일반 문자처럼 취급하기 위한 옵션

 

INSERT INTO STUDENT(STUDNO, NAME)
VALUES(33333, '황보_정');
-- 컬럼에 비해 값이 너무 크다. 입력한 바인드가 13이고 맥시멈은 10이라는 뜻
-- UTF-8 을 사용하기 때문에 황보_정호는 13바이트다. (한글 3바이트, 특수문자 1바이트)

INSERT INTO STUDENT(STUDNO, NAME)
VALUES(33334, '황보영');

SELECT STUDNO, NAME FROM STUDENT WHERE NAME LIKE '황보_%';

질의 결과

위 예제에서는 와일드문자를 삽입하지 않아 앞에 황보라고 적혀있는 모든 학생의 이름을 출력했다.

와일드문자를 삽입하면 아래 예제처럼 질의결과가 나온다.

SELECT NAME
FROM STUDENT
WHERE NAME LIKE '황보\_%' ESCAPE'\'

질의 결과