텍스트 파일 복사 붙여넣기시 내용중에 사이트 주소가 있으면 그 주소만 붙여넣는 이상한 오류가 발생하여 코드 블럭에 넣어 놓음. 차후 오류 수정시 다시 작업
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개 이상 존재
'수업 정리' 카테고리의 다른 글
23일차 수업 정리(트랜잭션, 제약조건, 뷰, 시퀀스) (0) | 2020.05.11 |
---|---|
22일차 수업 정리(DB - Join, DDL, DML, Subquery) (0) | 2020.05.08 |
20일차 수업 정리(Database, Oracle) (0) | 2020.05.06 |
19일차 수업 정리(Network) (0) | 2020.05.04 |
18일차 수업 정리(IO - Input, Output) (0) | 2020.05.01 |