21일차 수업 정리

텍스트 파일 복사 붙여넣기시 내용중에 사이트 주소가 있으면 그 주소만 붙여넣는 이상한 오류가 발생하여 코드 블럭에 넣어 놓음. 차후 오류 수정시 다시 작업 

TIP!
면접 질문
 - JOIN, 트렌젝션, 다형성
 - Cross Join, Inner Join, Outer Join, Self Join
 
**데이터베이스 사용
1. 데이터베이스 서버
    => 서버의 IP : 자신의 컴퓨터인 경우는 localhost, 선생님 IP(192.168.0.200)
    => 서비스의 포트번호 : 1521(오라클 기본포트 - 8080번 포트도 사용)
    => 사용가능한 데이터베이스 명 : SID - xe
         (오라클 최신 버전은 SID 대신 Service Name을 기본으로 사용)
    => 계정(8) : 아이디 - user01 ~ user20  /  패스워드 : user01 ~ user20

2. 데이터베이스 접속 프로그램 - DBeaver
    => 다른 프로그램을 사용하는 경우도 많음
    => 접속하고자 하는 데이터 베이스의 자바 드라이버가 필요

**select 
1. 기본 형식
    SELECT [DISTINCT] * 또는 칼럼명 나열
    FROM 테이블 명
    WHERE 조건
    => 테이블에서 조건에 맞는 칼럼 조회(DISTINCT : 중복 제거, * : 모든 열 조회)
    => SELECT와 FROM 절은 생략이 불가능
    => WHERE 절을 제외한 부분은 대소문자 구분을 하지 않음

  ex1) EMP테이블에서 SAL이 1500이상인 데이터의 모든 열을 조회
  ex2) EMP 테이블에서 JOB이 MANAGER인 데이터의 EMPNO, ENAME, JOB을 조회

2. 패턴 일치(검색시 이 방식 사용)
    => LIKE
    => 2개의 wildcard 문자를 이용(_ : 무조건 1글자, % : 0글자 이상)
        (ex - a_ : a로 시작하는 2글자, a% : a로 시작하는 모든 것)
    ex1) emp테이블에서 ENAME이 M으로 시작하는 데이터의 ENAME, JOB을 조회 

3. NULL 값 조회
    => is NULL로 조회
    => '= null'로 조회하면 null이라는 문자열을 가지고 있는 데이터를 조회
    ex) EMP 테이블에서 COMM의 값이 NULL인 데이터의 모든 열을 조회
    SELECT 	*
    FROM 		EMP
    WHERE 	COMM IS NULL;

4. and, or, not
    => and : 2개의 조건을 모두 만족
    => or : 2개의 조건 중 하나만 만족
    => not : 반대로
    ex) not between A and B, not in(데이터 모임), not like 패턴, is not null

<예시>
  - item 테이블에서 name에 바나나 우유가 포함된 데이터를 조회

    SELECT  *
    FROM ITEM
    WHERE NAME LIKE '%바나나 우유%' OR NAME LIKE '%바나나%' AND NAME LIKE '%우유%';

  - 'LG 노트북' 나눠서 검색
    String search = "LG 노트북";
    String [] ar = search.split(" ");

    SELECT * 
    FROM 테이블명
    WHERE ITEM LIKE '%ar[0]%' and ITEM LIKE '%ar[1]%';
    => and가 or보다 먼저

    ex) EMP 테이블에서 ENAME에 T가 포함되어 있지 않고, JOB이 MANAGER인 사원의 ENAME, JOB, SAL값 조회
    SELECT 	ENAME, JOB, SAL 
    FROM 		EMP
    WHERE 	ENAME NOT LIKE '%T%' AND JOB = 'MANAGER';

**정렬(Sort)
    => 데이터베이스의 데이터는 저장 순서를 알 수 없음
    => 데이버베이스는 데이터 저장시 검색 속도를 위해 인덱스를 별도로 생성하여 저장(B+Tree, B*Tree등)
    => 데이터를 원하는 순서대로 정렬하고자 할 때 사용하는 절이 order by 절
    => 형식
        SELECT
        FROM
        WHERE
        ORDER BY 정렬기준 [ASC | DESC], 정렬기준 [ASC | DESC]...
    => ORDER BY 절에 정렬할 기준을 설정
    => ORDER BY는 SELECT 다음에 마지막으로 수행되기 때문에 SELECT에서 만든 별명 사용 가능
    => 정렬기준을 설정할 때 SELECT에 기재한 열 이름의 인덱스를 기재하도 가능
    => ASC나 DESC를 생략하면 ASC(오름차순)
    => 2개 이상의 정렬기준을 설정하면 앞의 정렬 기준이 우선하고 그 값이 동일 할 때 뒤의 정렬 기준이 적용
    => 데이터 조회시 2개 이상의 행이 리턴될때, 특별한 경우가 아니라면 정렬하여 출력 해야 함

    ex1) EMP테이블에서 SAL의 내림차순으로 모든 열을 조회
    ex2) EMP테이블에서 JOB의 오름차순으로 조회하고, JOB이 같은 경우 SAL의 오름차순으로 모든 열을 조회

** 오라클의 단일행 함수
1. 오라클의 함수
    => 리턴값이 있는 코드의 모임
    => 종류 : 단일행 함수, 그룹 함수로 분류
    => 단일행 함수 : 하나의 데이터를 가지고 연산하여 하나의 결과를 리턴
         (여러개 데이터 대입시 함수는 각각의 데이터에 동작하여 결과를 여러개로 리턴)
2. 개발자 입장에서 데이터베이스에서 데이터를 가공하는 것 보다는 프로그램 안에서 가공하는 것을 선호
3. Dual Table
    => 오라클의 가상 테이블
    => 연산식이나 오늘 날짜, 시퀀스 등의 값을 알고자 할 때 사용할 수 있는 가상 테이블
    => 12 * 30 * 24의 값을 조회
         SELECT 12 * 30 * 24
         FROM DUAL;
4. ROUND
    => 데이터를 반올림해주는 함수
    => 숫자 데이터와 날짜 데이터에 사용이 가능
    => 형식 : Round(데이터, 반올림할 자리)
    => 자리를 생략하면 0이 설정되어 소수 첫째 자리에서 반올림
    => 음수 대입시 정수부분 반올림
    ex) ROUND(123.87, 1) : 123.9
         ROUND(123.87) : 124
         ROUND(123, -2) : 100
    ex2) EMP 테이블에서 ENAME과 SAL을 조회(SAL의 값은 10의 자리에서 반올림하여 100의 자리까지 나오도록 출력)
         SELECT 	ENAME, ROUND(SAL, -2)
         FROM 	EMP;
 
5. 문자 관련 함수
    => 영문자를 사용 할 때는 대소문자 구분 여부를 판단 : UPPER, LOWER, INICAP(첫자만 대문자)
    => 문자열의 길이 : LENGTH(글자 갯수), LENGTHB(바이트 수)
         - 대다수의 교재들은 한글이 2Byte라고 하지만, 지금 사용중인 utf-8인코딩에서 한글 1글자는 3byte
    => 좌우 공백을 제거하는 부분도 고민해야 함 : LTRIM, RTRIM, TRIM
         - 데이터 생성시 입력 가능한 최대 글자수로 설정하므로 뒤에 공백이 있을 수 있음
    ex1) 글자수와 바이트 수 확인
        SELECT LENGTH('WIRERESS'), LENGTHB('WIRERESS'), LENGTH('무선이어폰'), LENGTH('무선이어폰')

6. 형변환 함수
    => 데이터의 자료형 변환
    => 문자데이터와 숫자 및 날짜 데이터 사이의 변환
    => 사용자나 프로그래밍 언어로 부터 입력 받을 때는 문자열로, 실제 사용시는 변환하여 사용하는 경우가 많음
  1) to_char
    => 날짜 데이터를 문자열로 변환하고자 하는 경우
     - to_char(날짜 데이터, '출력 서식')
    => 숫자 데이터를 문자열로 변환하고자 하는 경우
     - to_char(숫자 데이터, '출력 서식')
  2) to_number
     - to_number(문자 데이터, '숫자 서식')
  3) to_date
     - to_date(문자데이터, '날짜 서식')
    ex) 1986년 5월5일 오후 1시를 날짜로 생성
        - SELECT TO_DATE('1986-05-05 13', 'YYYY-MM-DD HH24')
          FROM DUAL;

7. 날짜
  1) 현재 시간 : SYSDATE
  2) 오라클에서는 하루를 숫자 1로 판단
  3) 날짜 데이터와 정수 간의 연산을 지원(+, -만 의미)
  4) 날짜 데이터끼리 뺄셈도 지원
  5) ROUND, TRUNC, FLOOR와 같은 숫자 데이터 함수에 날짜 데이터 사용 가능
    ex) EMP테이블에서 각 사원의 근무 일수를 조회(ENAME, 과 근무일수 / 입사일은 HIREDATE에 저장)

8. NVL
    => null 값을 치환하기 위한 함수
    => null은 아직 알려지지 않은 값(자료구조에서는 nil이라고 하기도 함)
    => null과의 산술연산 결과는 null(null과의 연산은 불가능)
    => null인 데이터의 값을 치환하기 위하여 사용하는 함수가 NVL
    => 형식 : nvl(표현식 또는 열 이름, 대체할 값)
    => 대체할 값은 열 이름의 자료형과 일치해야 함
        - 대체시, 제거하고 연산을 하거나, 빈번히 나오는 값, 평균, 중간값, 0, 머신러닝에 의한 값 등으로 작업

**Group Function
    => 하나 이상의 행을 묶어서 연산한 결과를 리턴하는 함수
1. 함수 종류
  1) count : 데이터 갯수
  2) sum : 데이터 합계
  3) avg : 평균
  4) max : 최대값
  5) min : 최소값

  ===데이터의 분포를 알아보기 위한 함수===
  6) stddev : 표준편차(분산의 제곱근)
  7) variance : 분산(값 - 평균을 뺀 값을 제곱해서 더한 값)

2. SUM, AVG, MAX, MIN
    => NULL값을 제외하고 연산
    => AVG는 NULL을 제외한 것과 포함하고 계산하는 것의 결과가 다름
    ex) EMP테이블에서 COMM의 평균을 조회
    SELECT AVG(COMM)
    FROM EMP;
    => COMM이 NULL이 아닌 데이터가 4개 있으므로 4개의 평균을 구함

    SELECT AVG(NVL(COMM, 0))
    FROM EMP;
    => COMM이 NULL인 데이터의 값을 0으로 하여 14개의 평균을 구하게 됨

3. COUNT
    => NULL을 제외한 행의 갯수를 리턴하는 함수
    => 다른 함수들은 컬럼명을 매개변수로 대입, COUNT의 경우 컬럼명 대신 *을 입력하기도 함
    ex) EMP 테이블의 데이터 갯수 세기
    SELECT COUNT(*)
    FROM EMP;
    => 데이터가 많아 페이지 단위로 나누어 출력하는 경우 이 SQL을 실행하여 데이터 갯수 확인 및 생성 페이지수 계산
    ex) EMP 테이블에서 COMM이 NULL인 데이터의 갯수 조회
     SELECT 	COUNT(*)
     FROM 	EMP
     WHERE 	COMM IS NULL;

4. 그룹함수는 그룹화 하지 않은 열과 같이 출력 할 수 없음
    =>  EMP테이블에서 SAL이 가장 큰 데이터의 ENAME과 SAL의 값을 조회
    SELECT ENAME, MAX(SAL)
    FROM EMP;

*5. GROUP BY
    => WHERE 절 다음에 기재하여 데이터를 그룹화 하는 절
    => 컬럼 명 OR 표현식 모두 가능
    => GROUP BY을 사용하는 경우 GROUP BY에서 그룹화 한 열과 그룹 함수만 SELECT 구문에서 조회 가능
    ex) EMP테이블에서 JOB별로 SAL의 평균을 조회
    SELECT JOB, AVG(SAL)
    FROM EMP
    GROUP BY JOB;

    ex) EMP테이블에서 DEPTNO 별 인원수 조회
    SELECT DEPTNO, COUNT(*)
    FROM EMP
    GROUP BY DEPTNO;

6. HAVING
    => GROUP BY 이후의 조건
    => WHERE는 GROUP BY 이전에 수행되기 때문에 그룹화 한 이후의 조건 설정에는 사용 불가

*7. SELECT 구조
    5 - SELECT       조회할 열 이름, 표현식, *
    1 - FROM        조회할 테이블 이름
    2 - WHERE       조회할 조건
    3 - GROUP BY  그룹화 할 열이름이나 표현식
    4 - HAVING     그룹화 한 이후의 조건
    6 - ORDER BY  정렬할 열 이름이나 표현식
    => GROUP 함수는 GROUP BY절을 넘어가야만 사용이 가능

    ex) EMP 테이블에서 JOB별로 SAL의 평균을 조회
         JOB에 종사하는 인원수가 3명 이상인 경우만 조회
    SELECT 	JOB, ROUND(AVG(SAL), -2) 평균급여
    FROM 		EMP
    GROUP BY	JOB
    HAVING 	COUNT(*) >= 2
    ORDER BY 	AVG(SAL);

연습문제
EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성
H_YEAR  COUNT(*)  MIN(SAL)  MAX(SAL)  AVG(SAL)  SUM(SAL)
--------  -----------  ----------  -----------  ----------  -----------
80 	1	800	800	   800	   800
81 	10	950	5000	   2282.5	   22825
82 	2	1300	3000	   2150	   4300

=> EMP 테이블에서 HIREDATE의 년도 별 데이터 갯수, SAL의 최소값, SAL의 최대값, SAL의 평균값, SAL의 합계조회
=> 데이터는 년도의 오름차순으로 조회
=> 평균은 소수 첫째 자리까지만 조회
=> 년도별로 묶을 때는 SUBSTR이나 TO_CHAR함수를 이용

**JOIN
    => 2개 이상의 테이블에서 데이터를 조회하는 것
1. Cross Join(Cartesian Product)
    => 2개 테이블의 모든 조합이 생성
    => 열의 갯수는 2개 테이블의 열의 갯수의 합
         (행의 갯수는 2개 테이블의 행의 갯수의 곱)
    => From 절에 테이블 이름을 2개 기재하면 CrossJoin이 됨
        EMP 테이블은 8열 14행, DEPT 테이블은 3열 4행
        ex) emp테이블과 dept테이블의 CrossJoin
            SELECT * FROM EMP, DELP;

2. Inner Join
    => 2개 테이블의 공통된 의미가 있는 열이 있을 때 수행 가능한 Join
    => where 절에 2개 테이블의 공통된 의미의 열 값이 같은 경우에만 결합하도록 하는 Join으로 equi Join이라고도 함
         EMP 테이블의 DEPTNO는 부서번호, DEPT 테이블의 DEPTNO도 부서 번호
         ex) EMP 테이블과 DEPT 테이블을 DEPTNO열을 가지고 inner Join
             select *
             from emp, dept
             where emp.deptno = dept.deptno;
    => 양쪽 테이블에 동일한 이름의 열이 있을 경우 테이블 이름을 앞에 명시. 명시하지 않으면 에러메시지 출력

3. Outer Join
    => 어느 한쪽에만 존재하는 데이터도 Join에 참여 하는 것
    => 왼쪽 테이블의 데이터가 참여시 Left Outer Join, 오른쪽 테이블의 데이터가 참여시 Right Outer Join이라고 함
    => Join 조건을 만들 때 참여하고 싶은 테이블의 컬럼 뒤에 (+)을 붙이면 됨
    => 표준 SQL에서는 (+)을 한쪽에만 붙여야 함
         EMP 테이블의 DEPTNO는 10, 20, 30이 존재, DEPT 테이블의 DEPTNO는 10, 20, 30, 40이 존재
         ex) EMP테이블과 DEPT테이블의 OUTER JOIN
             select *
             from emp, dept
             where emp.deptno(+) = dept.deptno;

4. Self Join
    => 자기자신과 Join
    => 동일한 의미를 갖는 컬럼이 하나의 테이블에 2개 이상 존재