국비 22 일차

01강 데이터베이스의 개요와 오라클

 

데이터베이스란?

데이터들의 집합체를 의미한다

 

DBMS란?

• 데이터베이스를 관리, 운영하는 시스템을 의미한다.

• 여러 사용자나 프로그램이 데이터를 공유하고 동시에 접근이 가능해야 한다.

 

 DBMS의 특징

• 데이터의 무결성

• 데이터의 독립성

• 데이터의 중복의 최소성 

• 데이터의 보안성

• 데이터의 안정성

 

DBMS의 유형

• 계층형 DBMS

• 망형(네트워크형) DBMS

• 관계형 DBMS

• 객체관계형 DBMS

 

 우리가 주로 사용하게 될 DBMS 는 관계형 데이터베이스로 테이블이라는 최소 단위로 구성되어있다.

• 테이블은 릴레이션, 엔티티등으로도 불린다.

• 테이블은 데이터를 효율적으로 저장하기 위한 구조이다.

 

SQL이란?

• 관계형 데이터 베이스에서 사용하는 언어

• 데이터를 조작하기 위한 언어로 프로그래밍 언어와는 다르다.

• 국제 표준화 기구에서 표준을 제정.(다만 DBMS를 제작하는 회사 마다 특징이 있다

• 대화식 언어이다. 질의를 하고 결과를 얻는 구조

 

다양한 종류의 관계형 데이터베이스

• 대표적으로 oracle, mySQL, SQL server, MariaDB 등

 

오라클 SQL설치

 

데이터베이스 용어

• 테이블 – 데이터를 저장하기 위한 구조를 표(table)로 표현한 것

• 스키마 – 테이블, 뷰등이 저장되는 저장소 • 보통 오라클에서는 DB 사용자 이름과 동일시해서 사용한다.

• 데이터베이스 – 여러 스키마가 저장되는 공간

• 열(컬럼, 필드, 속성) – 데이터를 구분하기 위한 특징들을 컬럼이 라고 부른다. 테이블에서 열로 구성된다.

• 행(로우, 레코드, 튜플) – 실질적인 데이터를 의미한다.

• 차수(degree) : 속성의 개수

• 기수(cardinality) : 튜플의 개수

• 도메인(domain) : 속성이 가질 수 있는 값의 범위

• 트랜젝션 : 데이터베이스의 상태를 변화시키는 논리적 기능을 수행하기 위한 작업의 단위

 

02강 데이터 조회하기

SQL의 개념

• DBMS는 기본적으로 질의요청과 결과응답으로 작동

• 이때 질의를 쿼리(query)라고 한다.

• SQL은 이러한 쿼리를 문법적으로 정리한 것

 

기본적인 SQL의 표준은 국제표준화 기구(ISO)이 정립했지만

각 데이터베이스 회사마다 자기 제품에 특화된 SQL을 사용

• oracle-PL/SQL

• ms-sql- T-SQL

• my-sql-SQL

 

SQL은 크게 3가지로 분류한다.

• DDL (데이터 정의어) -한번 생성,삭제하면 복구 불가

• DML(데이터 조작어) -데이터(테이블의 행),트랜젝션 발생, 완정적용 commit(하드웨어 저장)후엔 변경 불가

• DCL (데이터 제어어) -접근권한 grant(줄때), revoke(뺏을때)

 

 DDL 종류

• 생성 : create • 변경 : alter • 삭제 : drop

DML 종류

• 생성 : insert • 변경 : update • 삭제 : delete • 조회 : select

DCL 종류

• 권한 부여 : grant • 권한 해제 : revoke

 

오늘날짜

select sysdate from dual;

 

데이터 조회 - SELECT

• 데이터베이스의 테이블에서 원하는 정보를 추출하는 명령어

• 가장 기본적이고 가장 많이 사용되는 구문

• 기본 형식 SELECT * FROM 테이블; (*: 모든 컬럼) -기본적으로 테이블을 작성할 때는 스키마.테이블 형식으로 사용해야 하지만 연결된 스키마 와 같은 때는 생략해도 된다

• 조회 하고자 하는 컬럼이 숫자형 데이터인 경우 산술연산을 추가 가능(+,-,*,/)

• 산술연산시 null인 경우(저장된 데이터가 없는 경우) 연산 결과 가 잘못 나올 수 있다.

• 이때 NULL인 데이터를 어떻게 연산할지 도와주는 함수가 있다(NVL)

select salary, salary*12, salary*12+NVL(commission, 0) from employee;

 

연산을 이용 해당 컬럼명이 연산식으로 표시되는 것의 이름을 지어줌-별칭(alias)

• 별칭 뒤에 AS 키워드 (생략도 가능)

• 별칭 내부에 공백이나 대소문자 또는 특수기호($,_,#등)를 포함 해야 한다면 “”를 이용해서 별칭을 부여.

 

• DISTINCT-컬럼의 데이터가 중복되는 경우 중복 데이터를 제거하고 출력 하기 위해 DISTINCT를 사용

SELECT DISTINCT dno FROM employee;

 

데이터 조회 - SELECT

• 데이터 조회 조건문-WHERE 조건절이라고 한다.

SELECT 컬럼명 FROM 테이블 WHERE 조건;

SELECT * FROM employee WHERE salary >=1500;

숫자이외의 데이터는 ‘’로 묶어서 표시

 

• 비교연산

• 논리연산

AND , OR ,NOT

NOT - SELECT * FROM employee WHERE NOT dno = 0;

비슷한 역할을 하는 연산자로 <>가 있다.

 

BETWEEN~AND 연산 - 특정 컬럼의 값이 하한값와 상한값 사이의 데이터를 조회하기 위한 연산

SELECT   * FROM employee WHERE salary BETWEEN 1000 AND 1500;(1000이상 1500이하)

 

IN 연산 -BETWEEN 연산이 연속적인 범위를 조회하기 위한 연산이라면 IN 연산은 불연속적인 값을 조회하기 위한 연산

SELECT   * FROM employee WHERE commission IN (300,500,1400); (300이거나 500이거나 1400인)

 

LIKE 연산자와 와일드카드 - 컬럼에 저장된 문자중 일부만 일치하더라도 조회

와일드 카드 

% 문자가 없거나 하나 이상의 문자가 어떤 값이 와도 상관없음

_ 하나의 문자가 어떤 값이 와도 상관없음

SELECT * FROM employee WHERE ename LIKE '%M%'; (M이 포함되어있는 모든 사원 조회)

SELECT * FROM employee WHERE ename LIKE '__%';이름의 세번째 글자가 A인 사원을 조회하기

SELECT * FROM employee WHERE ename NOT LIKE '%A%' (이름에 A들어가지 않은 사원조회)

 

NULL을 위한 연산자 -is

컬럼의 값이 null인지 확인하기

SELECT * FROM employee WHERE commission is (not) null;

 

정렬하기 ORDER BY (기본값 오름차순)

기준 컬럼 뒤에 ASC를 붙이면 오름차순

기준 컬럼 뒤에 DESC를 붙이면 내림차순

 

여러 개의 컬럼에 대해 정렬을 지정가능

- 먼저 나온 컬럼부터 정렬하고 먼저 나온 컬럼의 값이 같은 경우 다음 나온 컬럼의 정렬을 적용

 

03강 데이터 자료형

오라클에서 지원하는 데이터의 형식

• 숫자 데이터 - 주로 정수 실수 등 숫자 표시 

• NUMBER 사용시 숫자 데이터 있는 그대로 사용

• NUMBER(a) 전체 자리수를 a만큼 표기 ex) NUMBER(3) - 100까지 표기 가능

• NUMBER(a,b) 전체 자리수를 a만큼 표기하고 소수점 아래로 b만큼 표기

 

• 문자 데이터

  주로 쓰는 형식 VARCHAR2

 

• 이진 데이터

  – 사진,영상 BLOB ,BFILE

 

• 날짜 시간 데이터

DATE 저장된 값은 연,월,일,시,분,초가 저장됨

TIMESTAMP  DATE와 같지만 밀리초 단위까지 저장

TIMESTAMP WITH TIME ZONE 날짜와 시간대 형태의 데이터형식

TIMESTAMP WITH LOCAL TIME ZONE 날짜및 시간대 형태의 데이터형식, 단 조회시 클라이언트의 시간대로 보여짐

 

• 기타 데이터

• 주로 테이블을 생성하거나 함수등에서 사용된다

RAWID,XMLType,URIType

 

형변환 함수

TO_CHAR()함수는 숫자나 날짜를 문자열로 바꾸기 위해 사용하는 함수

• TO_CHAR(number | date ,’format’) =>숫자나 날짜을 포맷형식의 문자열로 전환

0 자릿수를 나타내며 자릿수가 맞는 않는 경우 ‘0’으로 표시

9 자릿수를 나타내며 자릿수가 맞는 않는 경우 표시하지 않는다.

SELECT ename, TO_CHAR(salary, 'L999,999') FROM employee;

 

TO_ DATE()함수는 특정 데이터를 날짜형으로 변환하기 위해 사용하는 함수

• TO_ DATE(‘char’ , ’format’) => 특정 데이터를 포맷형식의 문자열로 전환

• 형식을 생략하면 기본 YY/MM/DD 로 인식

 

TO_ NUMBER()함수는 특정 데이터를 숫자형으로 변환하기 위해 사용하는 함수

• TO_ NUMBER() => 특정 데이터를 숫자로 전환

 

범용

• CAST(표현식 AS 데이터형식) -> 표현식을 데이터형식으로 변환한다.

• CAST(xxx AS NUMBER) -> 숫자로 변환

• CAST(‘2020$12$31’ AS DATE) -> 날짜로 변환

 

04강 다양한 함수

내장 함수

 

내장 함수

• 오라클은 많은 내장함수를 가진다. 

 

문자열 함수

ASCII(영문자) 한 문자의 아스키값을 반환

CHR(숫자) 숫자 아스키값에 해당하는 문자반환 //

ASCIISTR(한글) 유니코드문자를 입력하면 16진수의 유니코드 반환

UNISTR(‘유니코드’) 유니코드값을 입력하면 해당 문자를 반환 //

 

문자열 함수

LENGTH(문자열) 문자열의 길이 반환

LENGTHB(문자열) 문자열의 바이트 수 반환

LENGTHC(문자열) 유니코드 문자열 길이 반환

 

CONCAT(문자열1,문자열2) 두개의 문자열을 결합

문자열1 || 문자열2 두개 이상의 문자열 결합

INSTR(기준문자열, 부분문자열, 찾기 시작위치) 기준 문자열에서 부분 문자열을 찾아 문자열이 시작하는 위치 를 반환 INSTRB(기준문자열, 부분문자 열,찾기 시작위치) 기준 문자열에서 부분 문자열을 찾아 문자열이 시작하는 위치 를 반환, 단 Byte단위로 센다

 

LOWER(문자열) 문자열을 소문자로

UPPER(문자열) 문자열을 대문자로

INITCAP(문자열) 단어의 첫글자만 대문자 나머지는 소문자로

REPLACE(문자열, 원래문자열, 바꿀 문자열) 문자열에서 원래 문자열을 찾아서 바꿀 문자열로 바꿔준다.

TRANSLATE(문자열, 원래 문자 열, 바꿀 문자열)

REPLACE는 바꿀 문자열을 통째로 바꾸지만 TRANSLATE는 한 글자씩 찾아서 변환해준다

 

SUBSTR(문자열, 시작위치, 길이) 시작위치에서 길이만큼 문자열을 반환

REVERSE(문자열) 문자열 순서를 거꾸로 한다.

LPAD(문자열,길이,채울 문자열) 문자열을 길이만큼 늘린 다음 빈 곳을 채울 문자열로 채운다. 길이는 바이트 단위로 센다. RPAD(문자열,길이,채울 문자열)

LTRIM(문자열, 제거할 문자) 문자열의 왼쪽/오른쪽에서 제거할 문자를 제거한다.

RTRIM(문자열, 제거할 문자) 생략시 공백을 제거

TRIM(제거할방향 제거할문자 FROM 문자열) 문자열의 앞뒤의 제거할 문자열을 제거한다. 방향 생략시 앞뒤 모두, 제거할 문자열이 없으면 공백을 제거 방향: LEADING(앞) TRAILING(뒤) BOTH(양쪽)

REGEXP__COUNT(문자열, 문자) 문자열에서 문자의 개수를 센다.

 

수학, 숫자 함수

CEIL(숫자),FLOOR(숫자),ROUND(숫자) -올림,내림,반올림

MOD(숫자1, 숫자2) 숫자1을 숫자2로 나눈 나머지

POWER(숫자1, 숫자2) 거듭제곱

SQRT(숫자) 제곱근

SIGN(숫자) 음수인지 양수인지 판단해서 -1,1을 반환(0은0)

TRUNC(숫자,정수) 숫자를 소수점을 기준으로 정수위치까지 구하고 나머지 버린다.

양수 일 경우 소수점 아래로 음수 소수점 위로

 

날짜 시간 함수

ADD__MONTHS(숫자/-숫자) 개월을 더하거나 뺀 결과

+/- 날짜를 더하거나 뺀 결과

ADD_MONTHS(날짜, 숫자) 날짜에 숫자만큼의 개월을 더한다. 

CURRENT_DATE 연/월/일 현재날짜를 구한다.

SYSDATE 연/월/일 현재 날짜는 구한다

CURRENT_TIMESTAMP 연/월/일 시:분:초 지역 형식으로 현재 날짜와 시간을 구한다.

EXTRACT(형식 FROM DATE ‘날짜’) 날짜에서 연,월,일,시,분,초를 구한다

                                             YEAR,MONTH,DAY.HOUR,MINUTE,SECOND

LAST_DAY(날짜) 주어진 날짜의 마지말 날짜를 구한다(해당 달이 며칠까지 있는지 확인)

NEXT_DAY(날짜, 요일) 주어진 날짜의 다음에 오는 요일의 날짜를 구한다.

MONTHS_BETWEEN(날짜1,날짜2) 두 날짜 사이에 오는 개월수를 계산해서 소수점 단위로 반환

NEXT_DAY(날짜,‘요일’) 지정된 날짜 이후에 돌아오는 요일에 해당하는 날을 반환

                              (요일은 숫자로 지정가능 일요일:7, 월요일:1, 화요일:2, 수요일:3, 목요일:4, 금요일:5, 토요일:6)

 

NVL(컬럼명(표현식), 대체데이터) 해당 컬럼의 데이터가 NULL이면 대체 데이터로 변환해서 사용된다.

NVL2(컬럼명, 데이터1, 데이터2) 해당 컬럼의 데이터가 NULL이면 데이터2반환, 아니면 데이터1반환

NULLIF(표현식1,표현식2) 표현식1과 표현식2의 결과가 같으면 NULL반환 아니면 표현식1 반환

COALESCE(표현식1,표현식2…표현식N) 여러 인자들중 NULL이 아닌 첫번째 인자를 반환

DECODE(표현식, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3, …., 기본결과n ) switch ~case문과 비슷하다

CASE 표현식 WHEN 조건1 THEN 결과1 WHEN 조건2 THEN 결과2 WHEN 조건3 THEN 결과3 ELSE 결과N END

if~else문과 비슷하다

 

순위 함수는 RANK(), NTILE(), DENSE_RANK(), ROW__NUMBER()등이 있다

-순위 함수 사용법

순위함수() OVER( PARTITION BY <컬럼> ORDER BY <컬럼> )

 

분석 함수

분석함수는 순위함수와 마찬가지로 여러 데이터를 기반으로 연산한다.

평균, 백분율, 누계등의 계산을 진행하게 된다.

LEAD(), FIRST_VALUE(), CUME_DIST(), PERCENTILE_COUNT()등 이 있다.