2021.11.26 - 국비수업 4주차 벌써..? database 일주일 수업 정리(1)

공부/database

2021.11.26 - 국비수업 4주차 벌써..? database 일주일 수업 정리(1)

기묜몬 2021. 11. 26. 23:55

오늘 조편성을 위한 개인과제 발표가 있었다.

사이트를 하나 선정해서 어떻게 구성되어있는지 발표하는 과제였다.

나는 되게 쉽게 생각하고선

사용자 입장에서 메뉴구성과 기능 구현에 대해서만 발표했는데

몇몇 사람들은 코드분석까지 해왔따.. 대단..

조 편성은 아쉽게도 1지망이었던 수강생과는 같은조가 되지 못했지만

그래두 아~주 만족스럽다. 조장님의 능력이 매우 기대된다!

오늘 발표자료에 본인의 학습시스템을 설명한 수강생들도 있었는데~

다들 정말 열심이더라!!! 덕분에 좋은 팁도 많이 얻었다!

앞으로 수료까지는 136일, 4달 반 정도 남았는데

좋은 결과를 꼭 얻어가야하기때문에

나도 오늘부터는 복습 방식을 바꾸기로했다.

[ 매일매일 해야할 것 ]

1. 그 날 배운것은 과제로만 끝내지 않고,

시간이 오래 걸리더라도 코드 분석 꼭 해보기(+개념 복습)

2. 1번에 추가로 해당 내용 응용문제 구글링해서 한 두문제라도 직접 코딩해보기

일단 매일 이렇게 진행하는걸로 하고

시간은 2시간 내외로 끝내기로 했다.^^

[ 11월 4주 수업요약 ]

- group by, having

- 정렬처리 order by (오름/내림차순)

- 날짜함수

- 기본연산

- where 조건문

- null인 데이터의 함수처리

- BETWEEN

- in 구문 (SUBQUERY)

- keyword검색을 처리하는 like

- 문자함수

<4주차 수업 내용 정리>

- group by 와 having

1. 집합함수를 처리할때, group by절을 활용한다.

select 그룹할 컬럼, 그룹함수(sum, avg, max, min)

group by 그룹할 대상 컬럼

having 그룹함수의 조건

SELECT deptno, max(sal) "최대급여"

FROM emp

GROUP BY deptno -- 부서번호별 최대 급여

HAVING max(sal)>=3000;

- 정렬 처리

1. 정렬은 데이터의 컬럼별로 정렬 기준을 설정하여,

해당 컬럼의 데이터를 기준으로 내림차순, 오름차순으로 처리할 수 있다.

2. 기본형식

order by 컬럼명 asc|desc; 오름차순\내림차순 정렬

SELECT deptno, empno, ename

FROM EMP e

ORDER BY deptno ASC,empno ASC;

-- 1차로 부서번호를 기준으로 정렬하고, 부서번호가 같으면 사원번호를 기준으로 오름차순

SELECT sal, ename, deptno

FROM emp

ORDER BY sal DESC;

-- 관리자번호 오름차순 정렬, (관리자번호가 같은 경우) 2차로 사원번호 내림차순 정렬

SELECT mgr, empno, ename

FROM emp

ORDER BY mgr ASC, empno DESC;

- 날짜 함수

1. 날짜형 데이터 타입에 사용하는 함수

1970/1/1

1/1000초 ==> 1초 ==> (60)1분 ==> (60)1시간

==> (24)1일(*) ==> calendar규칙에의해 28/30/31

1(월) ==> (12) 1년..

- 기본 연산

기본 +1은 1일을 기준으로 처리된다.

1) 날짜 +/- 숫자 : 해당 일자의 일수를 가/감산하여 처리

2) 날짜 +/- 1/24 : 시간단위 연산 처리

1일이 24시간이기때문에 5시간 이후인 경우 , 날짜 5/24를 처리한다.

cf) sysdate는 현재날짜시간을 나타낸다.

SELECT sysdate "현재날짜/시간",

sysdate +1 "내일 24시간 이후",

sysdate -1 "전날 24이전 날짜",

sysdate +(8/24) "현재로부터 8시간 이후", --(원하는 분 단위 /24시간)

sysdate +(30/60/24) "현재로부터 30분 이후 " --(원하는 분 단위 /60분/24시간 )

FROM dual;

-- 1일은 24시간

-- 24/24 1일

-- 8/24 8시간을 계산해서 처리

-- 10/60/24

-- ex) 1시간 30분 이후 => sysdate + 1/24 (1시간) + 30/60/24 (30분)

-- sysdate + (1/24)+(30/60/24);

-- ex1) 현재일자를 기준으로 3일 후 ,100일 후 , 5시간 전 , 3시간 전, 50분 후

SELECT sysdate+3 "3일 후 ",

sysdate+100 "100일 후",

sysdate-(5/24) "5시간 전",

sysdate-(3/24) "3시간 전",

sysdate+(50/60/24) "50분 후"

FROM dual;

SELECT ename, hiredate, hiredate+1 "입사 1일 이후",

hiredate -100 "입사 100일 전"

FROM emp;

--ex) 사원명, 입사일, 입사후 120일, 입사 8시간 전, 근무일수 sysdate(오늘날짜) 활용

SELECT ename, hiredate "입사일", hiredate+120 "입사 120일 이후", hiredate-(8/24) "입사 8시간 전",

floor(sysdate-hiredate)"근무일수"

-- 오늘로부터 입사일 전 까지의 날을 구함(근무일수) /floor (소숫점 반올림 함수 사용)

FROM emp;

- 날짜 함수

1. sysdate : 오라클에서 서버시스템의 현재 날짜와 시간

2. months_between(날짜1,날짜2)

: 날짜 사이의 개월을 월 단위로 계산

기준 단위가 1 ==> 1월

1개월 ==> 1, 15일 ==>0.5

1월을 기준으로 일/시/분/초 단위 등으로 소숫점으로 계산된다.

3. 소숫점 이하에 대한 처리는 trunc, floor를 이용해서 절삭 처리를 해준다.

1.5개월을 개월차로 ceil, 개월 수는 trunc, floor

4. add_months(날짜형 데이터, 추가할 개월 수)

: 해당 날짜에 개월 수 를 더한 날짜 계산

SELECT ename, hiredate, sysdate,

months_between(sysdate, hiredate) "근무 개월수",

floor(months_between(sysdate, hiredate)) "근무개월수2",

floor(months_between(sysdate, hiredate)/12) "근무연수"

FROM emp;

-- ex) dual을 이용하여, 오늘로부터 100일 후의 개월수를 출력

SELECT sysdate,

trunc(months_between(sysdate+100,sysdate)) "100일 후 개월수"

FROM dual;

--15개월인 경우 trunc(15/12) 1년 처리되고, 3개월 => mod(15/12) 12로 나눈 나머지값을 가져와서

-- 3개월을 뽑아낸다

/*ex2) 오늘로부터 1000일 후 @@년 @@개월로 표기하세요 .

SELECT

trunc(months_between(sysdate+1000,sysdate)) "1000일후 개월수",

trunc(months_between(sysdate+1000,sysdate)/12) "1000일후 연수",

mod(trunc(months_between(sysdate+1000,sysdate)),12) "개월 나머지",

trunc(months_between(sysdate+1000,sysdate)/12)

||'년||'MOD(trunc(months_between(sysdate+1000,sysdate)),12)

||'개월' "1000일 후"

FROM dual;

SELECT sysdate, ADD_MONTHS(SYSDATE,4) "오늘로부터 4개월 후 ",

ADD_MONTHS(SYSDATE,-3) "오늘로부터 3개월 전 "

FROM dual;

-- ex) 사원정보를 이용해서 입사후 1년, 6개월 후를 출력

SELECT ename, HIREDATE

hiredate, ADD_MONTHS(hiredate,12) "입사로부터 1년 후",

ADD_MONTHS(hiredate,6) "입사로부터 6개월 후"

FROM emp;

--ex2) 사원정보 부서별로 인턴기간 다르게 처리

-- 10 ==> 1개월 , 20 ==> 2개월, 30 ==>3개월

-- 사원명 부서번호 인턴기간 (@개월) 입사일 인턴만료일

SELECT ename, deptno, hiredate, deptno/10 ||'개월' "인턴기간 개월수",

ADD_months(hiredate,deptno/10)"인턴기간 만료일"

FROM emp;

5. next_day(날짜,'요일') : 대상 날짜로부터 다가오는 첫번째 요일에 해당하는 날짜

6. last_day(날짜): 대상 날짜의 월의 마지막 날짜.

SELECT sysdate, NEXT_DAY(sysdate,'금'),

NEXT_DAY(NEXT_DAY(sysdate,'금'),'금'),

last_day(sysdate),

last_day(sysdate)+1

FROM dual;

- where 조건문

1. 질의에서 필요한 관계대수(=,>=,<=,>,<)로 select 된 컬럼의 데이터에 대하여

row단위로 데이터를 filtering하는 작업을 수행하게 해준다.

2. 관계대수를 논리식으로 연결하는 and, or, subquery를 추가적으로 지원하고 있다.

SELECT *

FROM EMP

WHERE sal>=2000;

# 비교 연산자

1. != : 같지않을때 조건문에서 검색해준다.

2. not(조건문) : 해당 조건문이 아닐때, 검색해준다.

3. 컬럼명 is not null : null이 아닌 데이터를 검색해준다.

주의)컬럼 = null(x) ==> 컬럼명 is null

컬럼 != null(x) ==> 컬럼명 is not null

SELECT *

FROM emp

WHERE deptno != 20;

-- 숫자형 데이터를 통해서 부서가 20이 아닐때 ..

SELECT*

FROM emp

WHERE NOT (deptno = 20);

--ex) 급여가 1600이 아닌 경우를 위 2가지 비교영ㄴ산자로 처리하세요.

SELECT*

FROM emp

WHERE NOT (sal = 1600);

-- 부서번호가 20이고, 급여가 3000이상인 때와

-- 검색조건이 아닌 데이터를 비교연산자에 의해 출력하세요.

SELECT*

FROM EMP

WHERE deptno = 20 AND sal >=3000;

-- 검색조건이 아닌 where not 사용..

SELECT*

FROM EMP

WHERE NOT (deptno = 20 AND sal >=3000);

-- null 에 대한 검색

SELECT ename, comm

FROM emp;

WHERE comm = NULL;

SELECT ename, comm

FROM emp

WHERE comm IS NULL;

-- null값이 아닐때

SELECT ename, comm

FROM emp

WHERE comm IS NOT NULL;

-- cf) 데이터 유형이 숫자인 경우, database에서는 0인 경우와 null인 경우가 구분되지만

-- 이 내용이 객체로 처리된 경우에는 동일하게 처리된다.

-- 참고로 문자열인 경우에는 프로그램이나 DB둘다 null일 경우와 ""인 경우를 구분할 수 있다.

-- ex1) 사원정보를 급여와 보너스(comm)를 합산을 이름, 급여,보너스 합산으로 처리하되

-- 보너스가 null이 아닌 경우에 처리하세요.

SELECT ename, sal, comm, sal+comm "합산"

FROM emp

WHERE comm IS NOT NULL;

SELECT ename, job, mgr

FROM emp

WHERE mgr IS NULL;

- null인 데이터의 함수처리

1. 위의 예제와 같이 데이터가 null인 경우에 연산처리가 불가능해진다.

2. 이럴 경우, 특정한 null인 경우에 default데이터를 설정함으로 효과적인

데이터처리를 할 수 있는데

3. 이것이 함수를 통한 null 의 기본값 설정이라고 한다.

4. 위 내용을 지원하는 함수가 nvl(데이터, null일때 초기값) 이다.

5. 기본형식

숫자형 nvl(comm,0) : 기본 데이터를 숫자 0으로 설정

문자열 형 nvl(ename, ' ') : 기본 데이터를 문자 ' '로 설정

6. 위와 같이 nvl은 기본 데이터를 설정할 수 있는데, 주의할 점은

반드시, 해당 데이터 유형을 default 데이터를 설정해야한다.

nvl(comm, 0) : comm 이 숫자형이기에 default로 숫자형이 설정가능하다.

nvl(ename, ' ') : ename이 문자형이기에 default로 문자열형인 ' '이 설정가능하다.

select뿐만 아니라 조건문에도 활용하여 효과적으로 프로그램을 처리할 수 있다.

SELECT ename, sal, comm, nvl(comm, 0) "보너스 초기처리", sal+nvl(comm,0) "합산"

FROM emp;

SELECT ename, mgr, comm

FROM emp

WHERE mgr IS NULL

OR nvl(comm,0) = 0;

-- OR nvl(comm,0); = 0 을 통해서 0이거나 null검색해 줄 수 있다 ..

- BETWEEN

1) 특정한 데이터를 범위를 설정하여 검색할때, 사용된다.

2) 형식

컬럼명 between 시작범위 and 마지막 범위

==> 컬럼명 >= 시작범위 and컬럼명 <= 마지막 범위

SELECT*

FROM emp

WHERE sal BETWEEN 1000 AND 2000;

SELECT *

FROM emp

WHERE empno BETWEEN 7600 AND 7900;

- in 구문

1. 데이터는 논리연산자 or 에 의해서, 여러가지 데이터의 합집합에 대한 처리를 할 수 있다.

2. or 처리 형식은 컬럼명 = 데이터1 or 컬럼명 = 데이터2로 컬럼명과 데이터를

or로 계속 나열해야한다.

3. in 구문을 효과적으로 활용하면 보다 간편하게 or 데이터를 처리할 수 있다.

4. 기본형식

컬럼명 in (데이터1, 데이터2 ...)

SELECT ename, deptno

FROM emp

WHERE deptno = 10 OR deptno =20;

SELECT ename, deptno

FROM emp

WHERE deptno in (10,20);

SELECT *

FROM emp

WHERE empno in(7499,4566,7369);

SELECT *

FROM emp

WHERE job in('SALESMAN', 'MANAGER');

- IN 구문을 활용한 SUBQUERY

SELECT empno

FROM emp

WHERE deptno = 30; -- (1)부서번호가 30번인 사원번호를 추출

SELECT*

FROM emp --(2) IN 구문 사용

WHERE empno in (

SELECT empno

FROM emp -- (1) 붙여넣기

WHERE deptno = 30

-- 도출된 사원번호를 or 검색을 효과적으로 처리하는 in 구문을 이용해서 검색처리..

);

-- ex) (1)연봉이 1000~3000사이의 직책 / 을 뽑아내어 subquery로 활용하여

-- (2)이 직책(뽑은직책 직접 입력..) 에 해당하는 사원정보를 출력하세요...

SELECT job -- (1)

FROM emp

WHERE sal BETWEEN 1000 AND 3000;

-- 'SALESMAN', 'MANAGER', 'ANNLYST', 'CLERK'

SELECT*

FROM emp

WHERE job in(

SELECT job

FROM emp

WHERE sal BETWEEN 1000 AND 3000

);

SELECT *

FROM emp -- (2)

WHERE job IN ('SALESMAN', 'MANAGER', 'ANNLYST', 'CLERK');

- keyword검색을 처리하는 like

1. 저장된 데이터가 비슷한 키워드를 포함할때, 검색되게 하는것을 keyword검색이라고 한다.

2. 기본형식

where 컬럼명 like '%검색할문자열%';

- 해당 컬럼안에 있는 데이터가 검색할 문자열을 포함하고 있으면 검색되게 처리하는 것을 말한다.

3. KEYWORD 검색 옵션

1) 문자열의 위치에 상관없이 검색 : 컬럼명 LIKE '%문자열%'

2) 문자열이 시작위치에 있을때 검색 : 컬럼명 LIKE '문자열%'

-- 검색 내용중에 A가 포함되어있으면 검색되게 처리..

SELECT empno, ename

from emp

WHERE ename LIKE '%A%';

-- 검색 내용중에 A로 시작되는 문자열이 있을때 검색되게 처리..

SELECT empno, ename

from emp

WHERE ename LIKE 'A%';

-- 검색 내용중에 N으로 끝나는 문자열이 있을때 검색되게 처리..

SELECT empno, ename

from emp

WHERE ename LIKE '%N';

--ex1) 직책이 ER로 끝나는 문자열이 있는 사원명과 직책 출력

SELECT ename, job

FROM emp

WHERE job LIKE '%ER';

--ex2) 사원명이 S로 시작하는 문자열이 있는 사원명과 부서번호 출력

SELECT ename, deptno

from emp

WHERE ename LIKE 'S%';

-- ex3) 사원번호, 사원이름, 직책을 불러내고 직책 내용 중 man 이

-- 포함되어있는 것을 검색되게 처리

SELECT empno, ename, job

from emp

WHERE job LIKE '%MAN%';

- 문자함수

1. 문자 데이터를 입력하여 문자나 숫자를 결과로 반환하는 함수

2. 문자함수의 종류

1)대소문자 변환 함수

2) 문자조작 함수

3) 문자열 길이 반환 함수

3. initcal : 문자열의 첫 번째 문자만 대문자로 변환 처리 함수

lower : 문자열 전체를 소문자로 변환

upper : 문자열 전체를 대문자로 변환

SELECT ename, initcap(ename), lower(ename), upper(ename)

FROM emp;

-- ex) 함수를 이용하여 사원정보에 있는 컬럼에 맞추어 아래 형식으로 출력

SELECT 'the job of '||initcap(ename)||'is a '||lower(job)||'!!'show

FROM emp

WHERE initcap(ename)='Smith';

- 프로그램에 연동이 되어, 검색되는 데이터가 대소문자와 상관없이 활용하고자 할때는 아래와같은 형식을 사용한다.

사원명:[ ] [조회]

- 입력되는 데이터와 데이터베이스에 있는 데이터를 동일한 형식으로 처리해야한다.

1) 저장되어 있는 데이터 대문자로 변환

WHERE upper(검색할 컬럼)

2) 입력되는 데이터를 대문자로 변환

WHERE upper(검색할 컬럼) =('입력되는 문자열 ')

3) keyword 검색까지 혼용하면

where upper(검색할 컬럼) like'%'||upper('입력되는 문자열')||'%';

==> '%실제 입력된 문자를 대문자로 변환%'

-- 사원명을 대소문자 상관없이 keyword검색하는 sql

SELECT a.*, lower(ename),lower('O')

FROM emp a

WHERE lower(ename) LIKE '%'||lower('O')||'%';

SELECT *

FROM emp

WHERE upper(job) LIKE '%'||upper('man')||'%';

- 문자열 길이 반환

1. length(데이터/컬럼명) : 입력되는 문자열의 길이(글자수)를 반환하는 함수.

2. length(데이터/컬럼명) : 입력되는 문자열의 바이트를 반환하는 함수

한글이나 특수문자는 1글자가 3bytes인 경웅가 있어,

영문과 한글은 글자 수에 따라 차이가 있다.

cf)dual : 오라클에서 지원되는 가상테이블로 테스트 용으로 한 라인에

데이터와 함수의 적용결과를 확인할 수 있다.

* */

SELECT 'himan' 이름, 25 나이, '서울시송파구' 사는 곳 FROM dual;

SELECT ename, length(ename) "이름의 길이"

FROM emp;

--ex) 사원명과 직책의 길이를 출력하고,

SELECT ename, length(ename)

FROM emp;

-- ex2) 사원명이 가장 긴 데이터를 확인하여 where조건으로 가장 긴 길이와 동일한 데이터를 검색.

SELECT ename, length(ename)

FROM emp

WHERE length(ename) = 6; (제일 긴 글자수)

-- ex) length와 lengthb의 차이

SELECT '안녕하세요' g1, length('안녕하세요')g2, lengthb('안녕하세요')g3

FROM dual;

-- 사원명이 5byte인 경우를 length를 통해서 확인하여 사원번호, 사원명(첫자 대문자처리), 사원명 byte수를 출력하세요.

SELECT empno, ename, initcap(ename), lengthb(ename)"사원명의 byte수"

FROM emp

WHERE lengthb(ename) = 5;