231122 2일차(오라클)

*1121 복습*

DBMS는 무엇의 약자입니까? DataBse Manageement System

SQL은 무엇의 약자입니까? Structured Query Language 에스큐엘, 시퀄

RDBMS에서 데이터를 다루고 관리하는 데 사용하는 데이터베이스 질의 언어

DBMS 제품의 종류는? H2, Oracle, mysql, mqriadb, MongoDB

​

RDBMS에서 R은? : Relational

​

NoSQL 이란? 비관계형 데이터베이스 유형

비정형 데이터: 음악, 이미지, 동영상

​

행(LOW) : 튜플(TUPLE), 레코드(RECORD), 오버더레이즈

열(COLUMN) : 애트리뷰트(ATTRIBUTE), 필드(FIELD)

​

CONN

SHOW USER

​

SELECT*FROM TAB

​

DEPTNO를 중복없이 출력

연봉(COMM포함)을 SAL_YEAR 별칭으로 출력

이름과 직책을 이름별로 정렬

이름과 직책, 입사날짜를 입사날짜로 내림차순 정렬

이름과 직책 입사날짜 부서번호를 부서번호는 내림차순, 이름은 오름차순으로 정렬

​

​

*데이터베이스에 저장

학사프로그램, 장학금 신청 프로그램

>구조화된 데이터의 집합

​

*데이터 모델

-계층형 데이터 모델

-네트워크형 데이터 모델

-객체 지향형 데이터 모델(object-~) :상용화된 제품은 없음

-관계형 데이터 모델(★) : 1970년 에드거 프랭크 커드가 제안

​

-관계형 모델 예시

​

-용어

개체 :데이터베이스에서 데이터화하려는 사물, 개념의 정보 단위. 개체를 구체화 한 것이 테이블(relatioin)

속성

관계(relationship)

​

*특별한 의미를 지닌 열, 키

:수많은 데이터를 구별할 수 있는 유일한 값이라는 뜻. 키는 하나의 테이블을 구성하는 여러 열 중에서 특별한 의미를 지닌 하나 또는 여러 열의 조합을 의미

​

-기본키(★) Primary key: 비어있으면 안되는 키

-외래키 Foreign key

-복합키 Composite key

-후보키 Candidate key 유일한 것이 있다면 기본키가 될 수 있는 후보

-대체키 Alternate key

​

*자료형

-VARCHAR2(길이) 4000byte 만큼의 가변 길이 문자열 데이터를 저장할 수 있다

-NUMBER(★전체 자릿수, 소수점 이하 자릿수): +-38자릿수의 숫자를 저장할 수 있다.

-DATE:날짜 형식을 저장하기 위해 사용하는 자료형

​

*객체

-테이블(table) 데이터를 저장하는 장소

-인덱스(index) 테이블의 검색을 빠르게 하기 위해 사용

-뷰(view)

-시퀀스(sequence)

-시노님(synonym)

​

*오라클 데이터베이스 설치

-전역 데이터베이스 이름 orce

​

-SCOTT 계정이 제공되는데 잠겨있음

시스템으로 들어가서 스콧에서 권한을 주는것

​

ALTER USER SCOTT : SCOTT 계정을 변경하겠다

IDENTIFIED BY tiger : 접속 비밀번호를 소문자 타이거로 지정

ACCOUNT UNLOCK; 계정을 사용 가능 상태로 전환

​

-SELECT문 : [조회할 열1 이름], [열2 이름],..., [열N 이름]

-FROM : [조회할 테이블 이름]

-DISTINCT : 열의 중복 제거(중복 없이 출력)

-AS : 별칭 지정(한칸 띄어쓰기 별칭/ "별칭")

-ORDER BY절(ASC, DESC):정렬을 하지 않으면 결과를 더 빨리 출력할 수 있다. 꼭 필요한 경우가 아니면 쓰지말자

-WHERE절 : [조회할 행을 선별하기 위한 조건식]; 특정 조건을 기준으로 원하는 행을 출력하는데 사용(여러개의 연산자를 함께 사용)

SELECT FROM WHERE

​

-AND, OR 연산자

-NOT 연산자

-IN 연산자

-BETWEEN A AND B 연산자/ NOT BERWEEN A AND B

-IS NULL: 값이 존재하지않음, 해당 사항 없음

-LIKE 연산자(와일드 카드): _, %

-집합연산자 UNION

​

--*1122 수업

※오라클 함수

*문자함수 :문자 데이터를 가공

대.소문자를 바꿔 주는 UPPER, LOWER, INITCAP함수/정보를 통일화 시키기 좋은 함수

-UPPER(문자열): 괄호 안 문자 데이터를 모두 대문자로 변환하여 반환

-LOWER(문자열) : 소문자로 변환~

-INITCAP(문자열) : 첫 단어만 대문자~나머지 소문자

SELECT ENAME,UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME) FROM EMP;

​

*문자열 길이를 구하는 LENGTH 함수

LENGTH:= LONG, 길이, 크기, 개수, SIZE와 비슷하게 사용

​

--선택한 열의 문자열 길이 구하기

SELECT ENAME, LENGTH(ENAME) FROM EMP;

​

--사원 이름의 길이가 5이상인 행 출력

SELECT ENAME, LENGTH(ENAME) FROM EMP WHERE LENGTH(ENAME)>=5;

​

--직잭 이름이 6글자 이상인 데이터만 출력되도록 코드를 채워 보세요.

SELECT JOB, LENGTH(JOB) FROM EMP WHERE LENGTH(JOB)>=6;

​

--LENGTH 함수와 LENGTHB 함수

LENGTHB :B는 바이트/ 1 바이트= 8비트

영어 알파벳 1글자=1바이트

한글 1글자=2바이트

​

SELECT LENGTH('한글'), LENGTHB('한글') FROM DUAL;

SELECT LENGTH('AB'), LENGTHB('AB') FROM DUAL;

​

*DUAL : 데이터 저장 공간이 아닌 실습하기 위한 (연산, 무언가를 위한) 가상의 테이블

SELECT 1+1 FROM DUAL;

​

*문자열 일부를 추출하는 SUBSTR 함수

STR = STRING

문자(CHAR)= A, 홍

문자열(STRING)= AB, 홍길동

​

SUBSTR(문자열 데이터, 시작 위치, 추출길이)

SUBSTR(문자열 데이터, 시작 위치)

-끝까지 출력은 시작위치만 지정

​

SELECT JOB, SUBSTR(JOB,1,2), SUBSTR(JOB, 3, 2), SUBSTR(JOB, 5)

FROM EMP;

​

--SUBSTR 함수를 사용하여 EMP 테이블의 모든 사원 이름을 세번째 ~끝까지 출력

SELECT ENAME, SUBSTR(ENAME,3) FROM EMP;

​

*특정 문자 위치를 찾는 INSTR 함수(잘모르겠음)

([대상 문자열 데이터(필수) 'HELLO, ORACLE!'],

[위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1/문자로 표기 가능'') 'L']

[시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값은 1) 5

>처음부터 5번째 이후의 처음 오는 L이 검색 시작 위치부터 번호]

​

--INSTR : 시작 위치와 몇 번째 L 인지 정해지지 않아서 처음부터 자동 검색

SELECT INSTR('HELLO, ORACLE!','L') FROM DUAL;

​

--INSTR : 다섯번째 글자 O부터 L을 찾음, L :검색 시작 위치부터 첫번째로 등장한 L

SELECT INSTR('HELLO, ORACLE!','L',5) FROM DUAL;

​

--INSTR : 두번째 글자E부터 시작해서 두번째 L을 찾음

SELECT INSTR('HELLO, ORACLE!','L',2,2) FROM DUAL;

​

​

​

--INSTR 함수로 사원 이름에 문자 S가 있는 행 구하기

SELECT*FROM EMP

WHERE INSTR(ENAME, 'S')>0;

​

​

--LIKE 연산자로 사원 이름에 문자 S가 있는 행 구하기

SELECT ENAME FROM EMP WHERE ENAME LIKE 'S';

>LIKE 'S'에서 S 앞뒤로 %% 붙여줘야한다.

>SELECT ENAME FROM EMP WHERE ENAME LIKE '%S%';

​

​

*특정 문자를 다른 문자로 바꾸는 REPLACE함수

REPLACE([문자열 데이터 또는 열 이름(필수)],[찾는 문자(필수)],[대체할 문자(선택)])

​

SELECT REPLACE('010-1234-5678','-','') FROM DUAL;

​

​

*빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수

LPAD ([문자열 데이터 또는 열이름(필수)],[데이터의 자릿수(필수)],[빈 공간에 채울 문자(선택)])

RPAD ([문자열 데이터 또는 열이름(필수)],[데이터의 자릿수(필수)],[빈 공간에 채울 문자(선택)])

​

SELECT 'ORACLE',

LPAD('ORACLE',10),

RPAD('ORACLE',10),

LPAD('ORACLE',10,'#'),

RPAD('ORACLE',10,'*')

FROM DUAL;

​

​

*두 문자열 데이터를 합치는 CONCAT 함수

SELECT CONCAT(EMPNO, ENAME)FROM EMP;

SELECT CONCAT(CONCAT(EMPNO, ':'), ENAME) AS CON FROM EMP;

SELECT EMPNO ||':'|| ENAME FROM EMP;

​

SELECT CONCAT(CONCAT(EMPNO, ENAME),JOB) FROM EMP;

​

SELECT CONCAT(EMPNO, ENAME),

CONCAT(EMPNO, CONCAT(':',ENAME)) FROM EMP

WHERE ENAME = 'SCOTT';

​

​

※숫자 함수 : 숫자 데이터를 연산하고 수치를 조정하는 숫자 함수

-ROUND : 지정된 숫자의 특정 위치에서 반올림한 값을 반환

([숫자(필수)],[반올림 위치(선택)]) 반올림 후 남는 위치

​

SELECT 1234.5678, ROUND(1234.5678), ROUND(1234.5678,2) FROM DUAL;

​

-TRUNC : 지정된 숫자의 특정 위치에서 버림한 값을 반환, 버린 후 남는 위치

([숫자(필수)],[버림 위치(선택)]) 버림 후 남는 위치

​

SELECT 1234.5678, TRUNC(1234.5678),TRUNC(1234.5678,2)

FROM DUAL;

​

-CEIL : 지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환

-FLOOR : 지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환

-MOD : 지정된 숫자를 나눈 나머지 값을 반환

​

SELECT 1234.5678, MOD(11,2),CEIL(3.14) FROM DUAL;

​

SELECT CEIL(3.14), FLOOR(3.14), CEIL(-3.14), FLOOR(-3.14) FROM DUAL;

​

​

※날짜함수

*SYSDATE 함수: 별다른 입력 데이터 없이, 오라클 데이터베이스 서버가 옳은 OS의 현재 날짜와 시간을 보여줌

​

SELECT

SYSDATE AS NOW,

SYSDATE+1 AS TESTERDAY,

SYSDATE+10 AS TOMORROW

FROM DUAL;

​

*ADD_MONTHS([날짜 데이터(필수)],[더할 개월 수 (정수)(필수)])

--몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수, 3개월 후 날짜 구하기

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM DUAL;

​

​

--입사 10주년이 되는 사원들 데이터 출력/HIREDATE(입사일자)

SELECT EMPNO, ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 120) AS WORK10YEAR FROM EMP;

​

*개월 수 차이를 구하는 MONTHS_BETWEEN 함수

--HIREDATE와 SYSDATE 사이의 개월 수를 MONTHS_BETWEEN 함수로 출력

SELECT MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP;

​

SELECT EMPNO, ENAME, HIREDATE, SYSDATE,

MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTHS1,

MONTHS_BETWEEN(SYSDATE,HIREDATE) AS MONTHS2,

TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) AS MONTHS3

FROM EMP;

​

​

※형변환함수: 자료형을 변환하는 형 변환 함수

★문자를 중심으로 숫자 또는 날짜 데이터의 변환이 가능

​

*날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수

--원하는 출력 형태로 날짜 출력하기/SYSDATE 날짜 형식 지정하여 출력

SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') AS 현재날짜시간 FROM DUAL;

​

--월과 요일을 다양한 형식으로 출력하기

SELECT SYSDATE,

TO_CHAR(SYSDATE,'MM')AS MM,

TO_CHAR(SYSDATE,'MON')AS MON,

TO_CHAR(SYSDATE,'MONTH')AS MONTH,

TO_CHAR(SYSDATE,'DD')AS DD,

TO_CHAR(SYSDATE,'DY')AS DY,

TO_CHAR(SYSDATE,'DAY')AS DAY

FROM DUAL;

​

--SYSDATE 시간 형식 지정하여 출력

SELECT SYSDATE,

TO_CHAR(SYSDATE, 'HH24:MI:SS') AS HH24MISS,

TO_CHAR(SYSDATE, 'HH12:MI:SS') AS HH12MISS,

TO_CHAR(SYSDATE, 'HH:MI:SS') AS HHMISS

FROM DUAL;

​

*문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수

SELECT '1300'-'1500' FROM DUAL;

SELECT TO_NUMBER('1,300')-TO_NUMBER('1,500') FROM DUAL;>결과없음

​

--TO_NUMBER함수로 연산하여 출력하기>9999,9999는 자릿수를 표시(사실 이해가 잘 안됨)

SELECT TO_NUMBER('1,3000','9999,9999')-TO_NUMBER('1,5000','9999,9999')

FROM DUAL;

​

​

*문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수

SELECT TO_DATE('2018-07-14', 'YYYY-MM-DD')AS TODATE1,

TO_DATE('20180714', 'YYYY-MM-DD')AS TODATE2 FROM DUAL;

​

--2023년 12월 25일 크리스마스까지 며칠 남았습니까? 크리스마스-현재날짜

SELECT ROUND(TO_DATE('2023-12-25')-SYSDATE)

FROM DUAL;

​

​

※NULL 처리 함수

*NVL함수의 기본 사용법

열 또는 데이터를 입력하여 해당 데이터가 NULL이 아닐 경우 데이터를 그대로 반환하고,

NULL인 경우 지정한 데이터를 반환

​

NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)],

[앞의 데이터가 NULL일 경우 반환할 데이터(필수)])

​

SELECT EMPNO,ENAME, SAL, COMM, SAL+COMM,

NVL(COMM,0),

SAL+NVL(COMM,0) FROM EMP;

​

​

​

*NVL2 함수의 기본 사용법 NVL2(데이터, 값이 있을때, 값이 없을 때)

열 또는 데이터를 입력하여 해당 데이터가 NULL이 아닐 떄와 NULL일 때 출력 데이터를 각각 지정

NVL2([NULL인지 여부를 검사할 데이터 또는 열(필수)],

[앞의 데이터가 NULL이 아닐 경우 반환할 데이터 또는 계산식(필수)],

[앞의 데이터가 NULL일 경우 반환할 데이터 또는 계산식(필수)])

​

SELECT EMPNO, ENAME, COMM,

NVL2(COMM, 'O','X') FROM EMP;

​

SELECT EMPNO, ENAME, COMM,

NVL2(COMM, 'O','X'),

NVL2(COMM,SAL*12+COMM, SAL*12) AS ANNSAL

FROM EMP;

​

SELECT EMPNO, ENAME, COMM, NVL2(COMM, SAL*12+COMM, 0) AS 연봉 FROM EMP;

​

​

※다중행 함수 : 하나의 열에 출력 결과를 담는 다중행 함수

-SUM : 지정한 데이터의 합

-COUNT : 지정한 데이터의 개수

-MAX : 지정한 데이터의 최댓값

-MIN : 지정한 데이터의 최솟값

-AVG : 지정한 데이터의 평균값

​

*SUM함수

--SUM 함수를 사용하여 급여 합계

SELECT SUM(SAL) FROM EMP;

​

​

--SUM 함수를 사용하여 사원 이름과 급여 합계 출력> 안됨

​

--추가 수당 합계

SELECT SUM(COMM) FROM EMP;

​

--급여 합계 구하기 DISTINCT : 중복제거

SELECT SUM(DISTINCT SAL),

SUM(ALL SAL),

SUM(SAL) FROM EMP;

​

*COUNT 함수

--COUNT 함수를 사용하여 급여 합계

SELECT SUM(SAL),COUNT(ENAME),COUNT(SAL),AVG(SAL) FROM EMP;

​

--EMP 테이블의 데이터 개수 출력 카운트 뒤에(*)을 붙이면 행의 개수를 카운트 해준다.

SELECT COUNT(*) FROM EMP;

SELECT COUNT(*) FROM EMP WHERE DEPTNO=10;

​

--COUNT 함수 사용하여 급여 개수 구하기

SELECT COUNT(DISTINCT SAL),

COUNT(ALL SAL),

COUNT(SAL) FROM EMP;

​

--COUNT 함수를 사용하여 추가 수당 열 개수 출력하기

SELECT COUNT(COMM)FROM EMP;

​

*최댓값과 최솟값을 구하는 MAX,MIN 함수

--부서 번호가 10번인 사원들의 최대 급여 출력

SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10;

​

--10번부서에서 급여가 가장 높은 사원의 이름과 급여는?

SELECT ENAME, SAL FROM EMP WHERE DEPTNO=10;

​

*평균 값을 구하는 AVG 함수

--부서번호가 30인 사원들의 평균 추가 수당을 출력하세요

SELECT AVG(NVL(COMM,0)) FROM EMP WHERE DEPTNO=10;

​

--부서별 급여의 최대값은?

SELECT MAX(SAL)

FROM EMP

GROUP BY DEPTNO;

​

​

※결과 값을 원하는 열로 묶어 출력하는 GROUP BY절

SELECT[조회할 열1 이름],[열2 이름],...,[열N 이름]

FROM [조회할 테이블 이름]

WHERE[조회할 행을 선별하는 조건식]

GROUP BY[그룹화할 열을 지정(여러개 지정 가능)]

HAVING

ORDER BY[정렬하려는 열 지정]

​

--GROUP BY를 사용하여 부서별 평균 급여 출력

SELECT AVG(SAL), DEPTNO FROM EMP

GROUP BY DEPTNO;

​

--부서번호 및 직책별 평균 급여로 정렬

SELECT AVG(SAL), DEPTNO, JOB FROM EMP

GROUP BY DEPTNO, JOB

ORDER BY DEPTNO, JOB;

​

--GROUP BY절을 사용하여 부서 번호별 평균 추가 수당을 출력하도록 한 다음 SQL의 빈칸을 채워 보세요

​

SELECT DEPTNO, AVG(NVL2(COMM,COMM,0))

FROM EMP

GROUP BY DEPTNO;

​

※WHERE절과 HAVING절의 차이점

WHERE절은 출력 대상 행을 제한하고,

HAVING절은 그룹화된 대상을 제한(GROUP BY에만 적용)

​

SELECT DEPTNO, AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING AVG(SAL)>2000;

​

SELECT DEPTNO, AVG(SAL)

FROM EMP

WHERE SAL>2000

GROUP BY DEPTNO;