190524_Day32 DB3 복습,
DDL(객체정의)
- create, drop, alter, rename, truncate
DML(데이터조작)
- insert, delete, update
DCL(권한줬다뺏기)
- grant, revoke
DQL(데이터질의)
- select
select 컬럼명 1
from 테이블명 2
where 조건식 3
order by 컬럼명; 4실행순서) 2 - 3 - 1(조회,인출) - 4 - 출력
select [distinct] 컬럼명1 [as] 별명, 컬럼명2 "별명", 데이터, 산술연산, 컬럼명3 || 데이터
from 테이블명where 조건식
(컬럼명 연산자 비교데이터) ----- 비교연산자(< > <= >= = != ^= <> )
like '패턴%_'
in (value_list)
deptno in (10,20,30)
between 최소값 and 최대값 :최소값이상 ~ 최대값이하
comm = null (X) comm != null (X) comm is null (O) comm is not null (O)order by 컬럼명 [ASC|DESC];
기본값
nvl(인자1,인자2)함수 : null값을 특정값으로 변환하는 기능인자1: null예상되는 컬럼명
인자2: 대체할 데이터주의사항: 인자1과 인자2의 자료형이 같아야 함.
TCL(트랜잭션제어)
- commit, rollback, savepoint
==============================================================================
<숫자함수>
round함수 : 반올림(4이하는 버리고 5이상은 올림)
형식)
round(기준데이터) round(기준데이터, 소수점이하 자리수 표현) select round(45.56) from dual; === SQL> select round(45.573, 2) 라운드 from dual; 라운드 ---------- 45.57 === SQL> select round(45.575, 2) 라운드 from dual; 라운드 ---------- 45.58
TRUNC 함수 : 버림함수
select trunc(45.56) from dual; SQL> select trunc(45.56) from dual; TRUNC(45.56) ------------ 45 === SQL> select trunc(45.56, 1) from dual; TRUNC(45.56,1) -------------- 45.5
FLOOR 함수 : 소수점 이하 버림함수
SQL> select floor(45.56) from dual; FLOOR(45.56) ------------ 45 === SQL> select floor(45.568 2) from dual; SP2-0734: unknown command beginning "SQL> selec..." - rest of line ignored. //소수점 2자리까지만 표현해 달라는 것인데 floor에서 .56을 살릴수는 없다
문제) 10/3의 몫과 나머지를 구하시오
select floor(10/3) 몫, mod(10,3) from dual; ==> mod()함수를 테이블에 적용! 예) 급여를 100으로 나누고 나머지를 구하시오 select sal, mod(sal, 100) from emp;예) 급여를 100으로 나누고 나머지를 구하시오.
select sal, mod(sal,100) from emp; SAL MOD(SAL,100) ---------- ------------ 800 0 1600 0 1250 50 2975 75 1250 50 2850 50 2450 50 3000 0 5000 0 1500 0
문제) 사원번호가 짝수인 사원의 사원번호, 사원명을 출력하시오
select empno, ename from emp where empno%2 = 0; //에러 select empno, ename from emp where mod(empno,2) = 0; 숫자%2 == 0 ==> 짝수 숫자%2 != 0 ==> 홀수 숫자%2 == 1 ==> 홀수
<문자함수>
UPPER함수 : 대문자 변환 함수
select upper('welcome to oracle') from dual; UPPER('WELCOMETOORACLE') ---------------------------------- WELCOME TO ORACLE === select upper('welcome to oracle') from dual; INITCAP('WELCOMETOORACLE') ---------------------------------- Welcome To Oracle문제) 30번 부서의 사우너명과 사우너번호를 출력하시오
(단, 사원명의 첫글자만 대문자로 출력)
select initcap(ename) ename, empno from emp where deptno = 30; ENAME EMPNO -------------------- ---------- Allen 7499 Ward 7521 Martin 7654 Blake 7698 Turner 7844 James 7900LENGTH함수 : 문자열 길이(문자갯수)
- select length('OracleSQL') from dual;
==> 9 - select length('나길동') from dual;
==> 3 - select lengthb('OracleSQL') from dual;
==> 9 - select lengthb('나길동') from dual;
==> 9 (한글 3개문자 * 3byte)
==> lengthB( ) : 바이트 수 표현
- select length('OracleSQL') from dual;
INSTR함수 : 특정문자가 출현하는 위치를 얻어오는 함수 (자바의 indexOf('a'))
형식)
instr(기준데이터, 특정문자) instr(기준데이터, 특정문자, 시작인덱스, 출현횟수) === select instr('welcome to oracle', 'o') //첫번째 o from dual; ==> 5 === 데이터 : 'welcome to oracle' 인덱스 : 1234567890123456 select instr('welcome to orale', 'o' , 1, 2) -- 두번째 'o' from dual; ==>10SUBSTR : 문자열 일부분 추출, 부분 문자열 얻기
형식)
substr( 기준문자열, 시작인덱스, 부분문자열 갯수 ) select substr('welcome to oracle', 12, 6) from dual; // 12번째 문자부터 6개의 문자 얻어오기
- LPAD : 오른쪽 정렬 후 왼쪽 공백에 특정 문자 채우기. 형식) lpad(기준문자열, 전체길이, 채울문자) select lpad('oracle', 10, '#') lpad from dual; LPAD -------------------- ####oracle - RPAD : 왼쪽 정렬 후 오른쪽 공백에 특정 문자 채우기. 형식) rpad(기준문자열, 전체길이, 채울문자) select rpad('oracle', 10, '♥') rpad from dual; RPAD ------------------------------------ oracle♥♥♥♥ - LTRIM: 왼쪽 (연속된)공백 또는 (연속된)특정문자 제거 문자열 : ' oracle ' 앞에 4개의 공백, 뒤에 3개의 공백 select ltrim(' oracle ') ltrim from dual; LTRIM ------------------ oracle select length(' oracle ') from dual; ==> 13 select length(ltrim(' oracle ')) from dual; ==> 9 - RTRIM: 오른쪽 (연속된)공백 또는 (연속된)특정문자 제거 문자열 : ' oracle ' 앞에 4개의 공백, 뒤에 3개의 공백 select rtrim(' oracle ') rtrim from dual; RTRIM -------------------- oracle select length(rtrim(' oracle ')) "오른쪽공백제거" from dual; ===> 10 select rtrim('oracleaaaaaaaaaaaaaaa', 'a') rtrim from dual; RTRIM ------------ oracle - TRIM함수: 좌우의 연속된 공백을 제거해 주는 함수 select length(trim(' oracle ')) from dual; ===> 6 문제) 사원번호와 사원명, 입사일을 출력하되 입사일은 년도만 출력하시오. (예: '80/12/17' ----> '80')
<날짜함수>
SYSDATE ★ : 현재 날짜 구하기
select sysdate 현재날짜 from dual; 현재날짜 -------- 19/05/24 문제) 내일 날짜를 구하시오. select sysdate+1 내일날짜, sysdate-1 어제날짜, sysdate+7 "일주일 후" from dual; 내일날짜 어제날짜 일주일 -------- -------- -------- 19/05/25 19/05/23 19/05/31 - MONTHS_BETWEEN함수: 날짜와 날짜사이의 개월을 계산해 주는 함수 형식) MONTHS_BETWEEN(큰DATE, 작은DATE) 문제) 각 사원의 사원명, 입사일, 현재까지 근무한 개월 수를 출력하시오. select ename, hiredate, floor(months_between(sysdate, hiredate)) 근무개월수,hiredate, floor(months_between(sysdate, hiredate)/12) 근무년수 from emp; ENAME HIREDATE 근무개월수 HIREDATE 근무년수 -------------------- -------- ---------- -------- ---------- SMITH 80/12/17 461 80/12/17 38 ALLEN 81/02/20 459 81/02/20 38 WARD 81/02/22 459 81/02/22 38 JONES 81/04/02 457 81/04/02 38 MARTIN 81/09/28 451 81/09/28 37 BLAKE 81/05/01 456 81/05/01 38 CLARK 81/06/09 455 81/06/09 37 SCOTT 87/04/19 385 87/04/19 32 KING 81/11/17 450 81/11/17 37 TURNER 81/09/08 452 81/09/08 37 ADAMS 87/05/23 384 87/05/23 32 JAMES 81/12/03 449 81/12/03 37 FORD 81/12/03 449 81/12/03 37 MILLER 82/01/23 448 82/01/23 37 문제) 우리 클래스가 개강한 이 후 현재까지의 달수를 출력하시오 select months_between(sysdate, '2019/04/08') from dual; MONTHS_BETWEEN(SYSDATE,'2019/04/08') ------------------------------------ 1.53304585 ADD_MONTHS : 특정날짜에 개월을 더한 날짜 계산 select sysdate 오늘날짜, add_months(sysdate, 1) 한달후 from dual; === 오늘날짜 한달후 -------- -------- 19/05/24 19/06/24 === select add_months('2019/02/28',1) 한달후 from dual; 한달후 -------- 19/03/31 === select add_months('2019/02/28',-1) 한달전 from dual; 한달전 -------- 19/01/31LAST_DAY : 월의 마지막 날짜를 계산
select last_day(sysdate) "lastDay" from dual; lastDay -------- 19/05/31 === 문제) 다음 달의 마지막 날짜는? ==> 다음달의 마지막 날짜는 6월 30일 입니다. select '다음달의 마지막 날짜는' || substr(last_day(add_months(sysdate,1)), 4, 2 ) || '월' || substr(last_day(add_months(sysdate,1)),7,2) || '일 입니다.' "날짜 정보" from dual; 날짜 정보 ------------------------------ 다음달의 마지막 날짜는06월30일 입니다.
```NEXT_DAY : 특정날짜 후의 첫 요일의 날짜를 계산
문제) 다음 주 월요일의 날짜? select next_day(sysdate, '월') from dual; //오늘을 제외한 조건에 맡는 가장 근접한 날짜 NEXT_DAY -------- 19/05/27 === 문제) 사원들이 입사한 달의 마지막 날짜를 출력하시오. select ename, hiredate, last_day(hiredate) from emp; ENAME HIREDATE LAST_DAY -------------------- -------- -------- SMITH 80/12/17 80/12/31 ALLEN 81/02/20 81/02/28 WARD 81/02/22 81/02/28 JONES 81/04/02 81/04/30 MARTIN 81/09/28 81/09/30 BLAKE 81/05/01 81/05/31 CLARK 81/06/09 81/06/30 SCOTT 87/04/19 87/04/30 KING 81/11/17 81/11/30 TURNER 81/09/08 81/09/30 ADAMS 87/05/23 87/05/31 JAMES 81/12/03 81/12/31 FORD 81/12/03 81/12/31 MILLER 82/01/23 82/01/31
<형변환함수>
TO_CHAR TO_CHAR -----------------> <-------------------- NUMBER CHARACTER DATE <------------------ ---------------------> TO_NUMBER TO_DATE 형식) TO_CHAR ( number|date ) TO_CHAR ( number|date , 'format') 포맷형식) YYYY(년도 4자리) YY(년도 2자리), MM(월), DD(일), DAY 또는 DY(요일), HH,HH12(1~12) HH24(0~23) MI(분), SS(초)오늘 날짜를 출력하시오 select sysdate "오늘날짜" from dual; 오늘날짜 -------- 19/05/24 === SQL> select to_char(sysdate, 'YYYY') "오늘날짜" from dual; 오늘날짜 -------- 2019 === select to_char(sysdate, 'YYYY/mm/dd') "오늘날짜" from dual; 오늘날짜 -------------------- 2019/05/24 === 문제) 오늘 날짜를 '2019년 05월 24일' 형태로 출력하시오 select to_char(sysdate,'yyyy')|| '년'|| to_char(sysdate,'mm')|| '월'|| to_char(sysdate,'dd')|| '일' from dual; 혹은 select to_char(sysdate, 'yyyy"년"mm"월"dd"일') "오늘날짜" from dual; 오늘날짜 ---------------------------------- 2019년05월24일 === 문제) 오늘 날짜를 '2019년 05월 24일 13시 12분 30초 (금요일)' 형태로 출력하시오 select to_char(sysdate, ' yyyy"년" mm"월" dd"일" hh"시" mm"분" ss"초" (DaY) ') "오늘날짜" from dual; 오늘날짜 ---------------------------------------------------------------------------------------------------- 2019년 05월 24일 01시 05분 50초 (금요일) 문제) 오늘 날짜에서 2019년 4월 8일을 빼시오 select sysdate - 2019/04/08 from dual; SYSDATE- -------- 19/03/22 select sysdate - '2019/04/08' from dual; ==> 에러: ORA-01722: invalid number 형식) TO_DATE(문자 [,'포맷']) select sysdate - to_date('2019/04/08') from dual; select sysdate - to_date('2019/04/08', 'yyyy/mm/dd') from dual; SYSDATE-TO_DATE('2019/04/08') ----------------------------- 46.6133333 ========================================================================= <논리연산자> - AND : 두조건 모두 만족하는 검색 a,b,c,d : 숫자저장 컬럼(number) 3, 5 , 7, 9 where a>b AND c<d <------------------ sql문 실행순서!! ----> c<d 비교연산을 할까? 정답: 한다!! ----> 이유: sql에서는 뒤에서 부터 조건식을 검사하기 때문!! 문제) 10번 부서에 근무하고 직책이 CLERK인 사원의 사원번호, 사원명, 부서번호, 직책을 출력. select empno, ename, deptno, job from emp where job = 'CLERK' and deptno = 10;
EMPNO ENAME DEPTNO JOB 7934 MILLER 10 CLERK<OR연산자>
둘 중 하나라도 만족하는 행을 검색.
문제) 10번 부서에 근무하거나
또는 직책이 CLERK인 사원의 사원번호, 사원명, 부서번호, 직책을 출력.select empno, ename, deptno, job
from emp
where job='CLERK' or deptno=10;EMPNO ENAME DEPTNO JOB
7369 SMITH 20 CLERK
7782 CLARK 10 MANAGER
7839 KING 10 PRESIDENT
7876 ADAMS 20 CLERK
7900 JAMES 30 CLERK
7934 MILLER 10 CLERK<NOT연산자>
조건에 만족하지 못하는 행을 검색.
위치: 조건식 앞에 사용
in, like, between 앞에 사용 null앞에 사용문제) 30번 부서에 근무하는 사원의 정보(사원번호,사원명,부서번호,직책)를 출력하시오.
select empno, ename, deptno, job
from emp
where not deptno = 30;문제 ) 이름에 'A'를 포함하지 않는 사원의 사원번호, 사원명, 부서번호를 출력하시오
select empno, ename, deptno
from emp
where not ename like '%A%'; //혹은 where ename not like '%A%';문제) 급여가 1600 이상이고 3000 이하인 사원의 사원번호, 사원명, 급여를 출력.
select empno, ename, sal
from emp
where sal between 1600 and 3000;
문제) 급여가 1600 미만 또는 3000 초과인 사원의 사원번호, 사원명, 급여를 출력.
select empno, ename, sal
from emp
where sal < 1600 or sal>3000;
EMPNO ENAME SAL
7369 SMITH 800
7521 WARD 1250
7654 MARTIN 1250
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7934 MILLER 1300 혹은
select empno, ename, sal
from emp
where not (sal >= 1600 and sal <= 3000);
혹은
select empno, ename, sal
from emp
where sal not between 1600 and 3000;
- <그룹함수> ★ 집계함수
- 전체데이터를 그룹별로 구분하여 통계적인 결과를 구하기 위해 사용.
- 결과값은 항상 단행!!
- 그룹함수와 단순컬럼은 함께 사용하는 것이 불가능!!
(만약 그룹함수와 함께 사용하고자 하는 컬럼이 그룹으로 묶여질 수 있다면
group by절과 함께 사용하는 것이 가능)
- NULL은 결과값에서 제외
- 종류)
SUM(총합), AVG(평균), COUNT(행갯수), MIN(최소값), MAX(최대값),
STDDEV(표준편차), VARIANCE(분산)
형식)
그룹함수명(컬럼명)
문제) 사원들의 전체 급여 총합을 구하시오.
select sum(sal)
from emp;
SUM(SAL) 29025 문제) 사원들의 평균 급여를 구하시오.
select avg(sal)
from emp;
AVG(SAL)2073.21429
문제) 전체 사원들 중 가장 적은 급여와 가장 많은 급여를 출력하시오.
select min(sal) 최소급여 , max(sal) 최대급여
from emp;
최소급여 최대급여 800 5000 문제) 부서번호와 급여평균을 출력하시오.
select deptno, avg(sal)
from emp;
---> 에러발생 ORA-00937: not a single-group group function
---> select절에서는 단순컬럼과 함께 그룹함수 사용금지!!
- DQL형식)
1. select 조회컬럼명
2. from 테이블명
3. where (전체행에 대한)조건식
4. group by (그룹을 묶을 수 있는 조건을 가진)컬럼명
5. having (그룹에 대한)조건식
6. order by 정렬컬럼명;
★실행순서!! : 2- 3- 4- 5- 1(조회)- 6 - 출력
select deptno, avg(sal)
from emp
group by deptno; -- 부서별로 그룹을 묶겠음!!
-- 그룹함수와 컬럼이 매핑할 근거 마련
DEPTNO AVG(SAL) 30 1566.66667
20 2175
10 2916.66667
==> 부서별 평균 급여
문제1) 사원들의 직급종류는 몇 개 (= 몇 행)인지 출력하시오.
select job
from emp
group by job;
문제2) 전체사원의 수(=전체 행의 갯수)를 구하시오.
select ename
from emp
group by ename;
문제3) 가장 오래 근무한 사원의 입사일을 구하시오.
select min(hiredate) "입사일"
from emp;
문제4) 부서별 평균 급여를 구하시오.(부서번호가 작은 부서부터 출력)
select floor(avg(sal)) "부서별 평균급여"
from emp
group by deptno;
문제5) 부서의 평균 급여가 2000 이상인 부서의 부서번호와 평균 급여를 출력하시오.
select deptno, avg(sal) "부서별 평균급여"
from emp
group by deptno
having avg(sal)>=2000;
문제6) 평균급여(==> 2073.21429)보다 더 많은 급여를 받은 사원의 사원번호, 사원명, 급여를 출력하시오.
select empno, ename, sal
from emp
where sal > avg(sal);
문제7) 급여가 1000 이상인 사원들에 대해서만 부서별로 평균을 구하고
이 중에 평균 급여가 2000 이상인 부서의 부서번호와 평균급여를 출력하시오. 문제1) 사원들의 직급종류는 몇 개(=몇 행)인지 출력하시오.
select count(job)
from emp;
==> 14
왜 14?SQL> select job from emp;
JOB
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
---> 14명 사원들의 각각 직책 수!!
select distinct job
from emp;
JOB
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
---> 중복제거된 14명 사원들의 직책!!
select count(distinct job)
from emp;
==> 5
문제2) 전체사원의 수(=전체 행의 갯수)를 구하시오.
select count(empno)
from emp;
==> 14
select count(ename)
from emp;
==> 14
select count(hiredate)
from emp;
==> 14
select count(comm)
from emp;
==> 4
select count(mgr)
from emp;
==> 13
select count(*)
from emp;
==> 14!! 문제3) 가장 오래 근무한 사원의 입사일을 구하시오.
select min(hiredate)
from emp;
select to_char(min(hiredate), 'yyyy/mm/dd' ) "오래된 입사일"
, to_char(max(hiredate), 'yyyy/mm/dd' ) "가장 최근 입사일"
from emp;오래된 입사일 가장 최근 입사일
1980/12/17 1987/05/23
문제4) 부서별 평균 급여를 구하시오.(부서번호가 작은 부서부터 출력)
select deptno, avg(sal)
from emp
group by deptno
order by deptno;
DEPTNO AVG(SAL) 10 2916.66667
20 2175
30 1566.66667 문제5) 부서의 평균 급여가 2000 이상인 부서의 부서번호와 평균 급여를 출력하시오.
==> 부서별 그룹에 대한 조건!!
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) >= 2000;
DEPTNO AVG(SAL) 20 2175
10 2916.66667문제6) 평균급여(==> 2073.21429)보다 더 많은 급여를 받은 사원의 사원번호, 사원명, 급여를 출력하시오.
전체(14명 사원)에 대한 조건!!
select empno, ename, sal
from emp
where sal > avg(sal); 에러발생: ORA-00934: group function is not allowed here
===> where절에서는 그룹함수 사용이 불가!!
where 조건식
------
컬럼명 연산자 데이터
데이터 연산자 컬럼명
데이터 연산자 데이터
컬럼명 연산자 컬럼명
select empno, ename, sal
from emp
where sal > 2073.21429;
select empno, ename, sal
from emp
where sal > (select avg(sal) from emp); 문제7) 급여가 1000 이상인 사원들에 대해서만 부서별로 평균을 구하고
이 중에 평균 급여가 2000 이상인 부서의 부서번호와 평균급여를 출력하시오.
select deptno, avg(sal)
from emp
where sal >= 1000
group by deptno
having avg(sal) >= 2000;
NVL, NVL2, NULLIF, COALESCE의 차이점
*NVL은 NULL값을 지정된 값으로 바꾼다.(모든 데이터 타입 적용 가능)
*NVL2(expr, expr1, expr2는 NULL이 아니면 expr1의 값 반환, NULL이면 expr2 값 반환
*NULLIF(exp1, exp2) 는 exp1과 exp2의 값이 동일하면 NULL 그렇지 않으면 exp1
*coalesce(expr1, expr2, expr3.....)은 순서대로 조회하며 NULL이 아닌값 반환 expr1이 NULL이 아니면 expr1 그렇지 않으면 coalesce(exp3,expr3....)값 반환
*여기서 NVL은 oracle함수이고, COALESCE는 ANSI 표준 함수TRUNC와 FLOOR의 차이
-마이너스에서만 차이가 있다.
SQL> select Trunc(-45.245) from dual;
TRUNC(-45.245)
-45SQL> select Floor(-45.245) from dual;
FLOOR(-45.245)
-46위처럼 마이너스에서 TRUNC는 그냥 잘라내기만 하고, FLOOR는 내림처리가 된다.
SQL> select Trunc(-10/3) from dual;TRUNC(-10/3)
-3SQL> select Floor(-10/3) from dual;
FLOOR(-10/3)
-4*이렇게 -10을 3으로 나누는 연산등에서 값이 바뀌는 결과가 발생 할 수 있다.
'클라우드 기반 웹 개발자 과정 공부 > DB' 카테고리의 다른 글
| 190529_DAY35, <복습>, <DDL>, <DML> (0) | 2019.05.29 |
|---|---|
| 190528_DAY ANSI JOIN 복습, 서브쿼리, DDL시작 (0) | 2019.05.28 |
| 190527_DAY33 decode, case , join (0) | 2019.05.27 |
| 190523_Day31 DB2 (0) | 2019.05.23 |
| 190522_Day30 DB(Oracle)-SQL (0) | 2019.05.22 |