Story of Hyun

-- 그룹 함수 : 통계 함수 / 여러 행 또는 테이블 전체의 행에 대해 함수가 적용

-- COUNT : 갯수 구하기

select count(empno) from empcopy;

select count(comm) from empcopy;

select count(ename) from empcopy;

select count(job) from empcopy;

select count(*) from empcopy;

select ename, count(hiredate) from emp; -- error :그룹함수는 그룹함수끼리만 사용 가능

-- 81년도에 입사한 사원수

select count(*) from emp where to_char(hiredate, 'yy')='81';

select count(*) from emp where hiredate like '81%';

-- MANAGER는 몇명인가?

select count(*) from emp where hiredate like '81%' and job='MANAGER';

-- MAX : 최대값

select max(sal) from emp;

select max(comm) from emp;

-- MIN : 최소값

select min(sal) from emp;

select min(comm) from emp;

-- 부서코드 20인 사원은 최대 급여와 최소 급여는 얼마인가?

select max(sal), min(sal) from emp where deptno=20;

-- SUM : 합계

-- 급여 총액

select sum(sal) from emp;

-- 보너스 총액

select sum(comm) from emp;

-- AVG : 평균

select avg(sal) from emp;

select round(avg(sal), 2) from emp;

-- 보너스 평균

select avg(comm) from emp;

-- null을 포함한 보너스 평균

select avg(nvl(comm, 0)) from emp;

select round(avg(nvl(comm, 0)), 2) from emp;

-- 부서코드가 20인 사원의 급여의 합과 평균을 구하라

select sum(sal), round(avg(sal), 2) from emp where deptno=20;

-- 담당업무가 SALESMAN인 사원중 부서코드가 30인 사원의 보너스의 합과 평균을 구하라

select sum(comm), round(avg(nvl(comm, 0)), 2) from emp where job='SALESMAN' and deptno=30;

-- STDDEV : 표준 편차

-- 급여에 대한 표준편차

select round(stddev(sal), 2) from emp;

-- 변환 함수

-- TO_CHAR : 변환함수

select sysdate from dual;

select to_char(sysdate, 'month') from dual; -- 월만 출력

select to_char(sysdate, 'yyyy') from dual;  -- 년도만 출력

select to_char(sysdate, 'dy') from dual;  -- 요일만 출력

-- emp 테이블에서 담당업무가 MANAGER 이거나 부서코드가 20인 사원의 입사월을 출력하라

-- 사원명, 담당업무, 부서코드, 입사일, 입사월

select * from emp;

select ename 사원명, job 담당업무, deptno 부서코드, hiredate 입사일, to_char(hiredate, 'month') 입사월 from emp where job='MANAGER' or deptno=20;

select ename 사원명, job 담당업무, deptno 부서코드, hiredate 입사일, to_char(hiredate, 'mm') 입사월 from emp where job='MANAGER' or deptno=20;

-- TO_NUMBER : 문자를 숫자로 변환 / 안써도 됨

-- select to_number('1234')+100 from dual;

select '1234'+100 from dual;

select '1234'+'100' from dual;

-- 날짜 처리 함수

-- LAST_DAY(d) : (d)달의 마지막 날 표시

select sysdate, last_day(sysdate) from dual;

select ename, hiredate, last_day(hiredate) from emp;  -- 입사한 달의 마지막 날

--ADD_MONTHS(a, b) : a 날짜에 b의 값을 더한 달을 표시

select sysdate, add_months(sysdate, 5) from dual; -- 오늘을 기준으로 5개월 뒤

select add_months('2017-01-31', 1) from dual; --  17년 1월 31일을 기준으로 1개월 뒤

select add_months(sysdate, -3) from dual; -- 오늘을 기준으로 3개월 전

-- emp 테이블의 사원의 입상후 20개월 후는 언제인가?

select * from emp;

select ename, hiredate, add_months(hiredate, 20) from emp;

-- MONTHS_BETWEEN(a1, a2) : 날짜와 날짜 사이의 개월수 구하기

select floor(months_between('2016/12/10', '2017/02/25')) from dual;

-- emp 테이블의 사원은 근무기간은 몇개월인가?

select * from emp;

select ename, hiredate, ceil(months_between(sysdate, hiredate)) 근속개월 from emp;

-- ROUND(d, [f]) : 날짜의 반올림 / year, month, day

-- TO_DATE() : 문자열을 날짜로 변환

-- 년도를 기준으로 반올림(7월)

select round(to_date('2016/08/01', 'yyyy/mm/dd'), 'year') from dual;

select * from empcopy;

update empcopy set hiredate='2016/10/10' where empno=6666;

select round(sysdate, 'year') from dual;

-- 입사일 기준 년도를 반올림하여 출력하라.

-- 사원명, 입사일, 반올림한 년도

select * from emp;

select ename 사원명, hiredate 입사일, round(hiredate, 'year') 반올림년도 from emp;

-- 월을 기준으로 반올림

select round(sysdate, 'month') from dual; -- 오늘 기준

select round(to_date('2016/10/17'), 'month') from dual; -- 특정일 기준

select ename, hiredate, round(hiredate, 'month') from emp;  -- emp 테이블 입사일 기준

-- 일을 기준으로 반올림

-- TO_CHAR() : 날짜나 숫자를 문자로 변환

select round(sysdate, 'day') from dual;

select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss pm') from dual;

-- EMP 입사일의 날짜를 반올림하라.

-- 출력은 이름, 입사일, 반올림한 입사일

-- 입사일은 mm/dd hh24:mi:ss 표시

select * from emp;

select ename 사원명, to_char(hiredate, 'mm/dd hh24:mi') 입사일, round(hiredate, 'dd') 반올림 from emp;

-- 날짜에 대한 산술연산

-- 오늘을 기준으로 1000일 후

select sysdate+1000 from dual;

-- 문자형으로 출력

select to_char(sysdate+1000, 'yyyy-mm-dd hh') from dual;  

-- 오늘을 기준으로 100일 전

select sysdate-100 from dual;

-- 2016-11-23 기준으로 960일 후는 언제인가?

select to_date('2016/11/23')+960 from dual;

select to_date('2016-11-23', 'yyyy-mm-dd')+960 from dual;

-- 날짜 경과 계산

select sysdate - to_date('2016/11/23', 'yyyy/mm/dd') from dual;

-- emp 테이블의 사원중 부서코드가 30 사원의 근무일수를 구하라.

-- 사원명, 입사일, 급여, 근무일수, 부서코드

select * from emp;

select ename 사원명, hiredate 입사일, sal 급여, ceil(sysdate-hiredate) 근무일수, deptno 부서코드 from emp where deptno=30;

-- General Function

-- NVL : null값 처리 (not value)

select ename, sal, comm, comm+100 from emp;

select ename, sal, comm, nvl(comm, 0)+100 from emp;

-- emp 테이블에서 사원명, 관리자코드, 급여, 보너스를 출려하되 관리자코드 null인 경우 0000 으로 보너스는 null인 경우 0으로 출력하라.

select ename, nvl(mgr, '0000'), sal, nvl(comm, 0) from emp;

-- DECODE : 조건문 / 데이터를 다른 값으로 변환

select ename, deptno, decode(deptno, 10, '어카운팅', 20, '리서치', 30, '세일즈', 40, '오퍼레이션') 부서명 from emp;

-- emp 테이블에서 사원번호, 사원명, 담당업무, 급여를 출력하되 담당업무는 CLERK-사무직, SALESMAN-세일즈맨, MANAGER-매니저, ANALYST-시스템분석가, PRESIDENT-대표이사로 표시하라.

select * from emp;

select empno, ename, decode(job, 'CLERK', '사무직', 'SALESMAN', '세일즈맨', 'MANAGER', '매니저', 'ANALYST', '시스템분석가', 'PRESIDENT', '대표이사') job, sal from emp;

select * from tab;

select * from emp1;

-- 필드 추가

alter table emp1

add (email varchar2(30));

select * from emp1;

-- 이메일 수정

update emp1 set email='ssss@naver.com' where hiredate like '__/02%';

update emp1 set email='aaaaa@nate.com' where ename like '%A%';

update emp1 set email='nnnnn@daum.com' where ename like '%N%';

update emp1 set email='null@hanmail.net' where email is null;

commit;

--------------------------------------------------------------------------------

-- 이름    이메일            아이디 도메인

-- smith  null@hanmail.net  null  hanmail.net

select ename 이름, email 이메일, substr(email, 1, instr(email, '@') -1) 아이디, substr(email, instr(email, '@') +1, length(email)) 도메인 from emp1;

select ename, email, substr(email, 1, instr(email, '@') -1) 아이디, substr(email, instr(email, '@') +1, length(email) - instr(email, '@')) 도메인 from emp1;

-- CONCAT(char1, char2) : 문자 연결

-- select concat('blue', 'color') concat from dual;

-- INITCAP(char) : 단어의 첫번째 문자를 대문자로 변환

-- select initcap('hong gil dong') from dual;

-- LOWER(char) : 모든 문자를 소문자로 변환

-- select lower('HONG GIL DONG') from dual;

select lower(ename) from empcopy;

-- UPPER(char) : 모든 문자를 대문자로 변환

-- select upper('hong gil dong') from dual;

-- 함수 안에 함수 사용

select concat(lower(ename), upper(ename)) 이름 from empcopy;

-- LPAD(char1, n, [char2]) : 왼쪽에 문자열 채우기

-- select lpad('gil dong', 10, '*') from dual;

select lpad(ename, 10, '*') from empcopy;

-- RPAD(char1, n, [char2]) : 오른쪽에 문자열 채우기

-- select rpad('gil dong', 10, '*') from dual;

select rpad(ename, 10, '*') from empcopy;

-- SUBSTR(char, m, [n]) : m 번째 자리부터 길이가 n개인 문자열을 반환 / m이 음수일 경우에는 뒤에서 M번째 문자부터 반대 방향으로 n개의 문자를 반환

-- select substr('hong gil dong', 6, 3) from dual;

-- select substr('hong gil dong', -5, 3) from dual;

select job, substr(job, 2, 3) from empcopy;

-- LENGTH(char) : 글자수 구하기

-- select length('gil dong') from dual;

select ename, length(ename) from empcopy;

-- 이름의 문자길이의 50% 만큼 출력하고, 나머지 문자는 '*' 로 표시하라

--select ename 이름1, rpad(substr(ename, 1, length(ename)/2), length(ename), '*') 이름2 from empcopy;

select ename, rpad(substr(ename, 1, ceil(length(ename)/2)), length(ename), '*') from empcopy;

-- REPLACE(cahr1, str1, str2) : 문자 치환

select ename, replace(ename, 'A', '에이') replace from empcopy;

-- INSTR : 문자위치 구하기

select instr(job, 'SA') instr, job from empcopy;

select instr(job, 'A') instr, job from empcopy;

--                     3번째 문자부터 검색

select instr(job, 'A', 3) instr, job from empcopy;

--                     2번째 문자부터 검색을 시작해서 'A' 문자가 두번째 위치의 값

select instr(job, 'A', 2, 2) instr, job from empcopy;

-- TRIM : 앞뒤 공백 및 특정문자 제거 / 중간의 공백 및 특정문자에는 영향이 없음 / 숫자의 경우 문자로 변환됨

select 'result = ' || trim('   sam   ple   ')  || '결과' from dual;

select ename, trim('S' from ename) from emp;

-- LTRIM : 왼쪽 문자 제거

select ltrim('abcabcTestabc', 'abc') from dual;

-- RTRIM : 오른쪽 문자 제거

select rtrim('abcacbcSampleTest', 'abt') from dual;

-- comm(보너스)가 null인 값을 -100으로 수정

update empcopy set comm=-100 where comm is null;

-- ABS(n) : 절대값을 계산하는 함수

-- select abs(음수) from dual;

select ename 이름, sal 급여, abs(comm) 보너스 from empcopy;

-- CEIL(n) : 올림

-- select ceil(소숫점 수) from dual;

select ename 이름, sal*1.15 급여1, ceil(sal*1.15) 급여2 from empcopy;

-- FLOOR(n) : 버림

-- select floor(소숫점 수) from dual;

select ename 이름, sal*1.15 급여1, floor(sal*1.15) 급여2 from empcopy;

-- MOD(m, n) : 나머지 구하기

-- select mod(10, 3) from dual;

select ename, sal, comm, mod(sal, comm) from empcopy;

-- POWER(m, n) : m의 n 승 구하기

-- select power(5, 8) from dual;

-- ROUND(n, [m]) : n 값의 반올림 / m은 양수일 경우 소숫점 반올림 위치, 음수일 경우 정수 반올림 위치

-- select round(12345.258, 1) from dual;

select ename 이름, sal 급여1, round(sal, -2) 급여2 from empcopy;

-- SQRT(n) : n의 루트값 구하기

-- select sqrt(10) from dual;

-- TRUNC(n, m) : n의 값에서 m 이하의 수 버리기 / m은 양수일 경우 소숫점 위치, 음수일 경우 정수 위치

-- select trunc(7.5597, 2) from dual;

-- DISTINCK : 하나의 컬럼에서 중복된 값을 제거하고 보여주기

-- select distinck 컬럼명 from 테이블명;

select distinct job from emp;

select * from tab;

select * from emp;

-- 테이블 복사

create table empCopy

as

select * from emp;

select * from empcopy;

-- 사원번호, 이름, 입사일이 있는 테이블 복사

create table emp1

as

select empno, ename, hiredate from emp;

select * from emp1;

-- 원하는 레코드를 포함한 테이블 복사

-- 담당 업무가 MANAGER이거나 부서코드 30인 사원을 포함한 테이블 복사

create table emp2

as

select * from emp where job='MANAGER' or deptno=30;

select * from emp2;

-- emp3 테이블 생성, 이름, 담당업무, 입사일, 급여

-- 레코드는 급여가 2500 이상인 레코드 포함

create table emp3

as

select ename, job, hiredate, sal from emp where sal>=2500;

select * from emp3;

-- 테이블 구조만 복사

create table emp4

as

select * from emp where 1=2;

select * from emp4;

desc emp4;

-- 테이블 구조를 복사하지만 필드명은 사번, 사원명, 입사일, 부서코드만 포함

create table emp5

as

select empno, ename, hiredate, deptno from emp where 2=3;

select * from emp5;

-- 테이블 목록

select * from tab;

write 화면

import java.io.File;

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelWrite {

public ExcelWrite() {

try{

File f = new File("z://fileFolder", "excelTest.xls");

//WorkBook 생성

HSSFWorkbook workbook = new HSSFWorkbook();

//sheet 생성

HSSFSheet sheet1 = workbook.createSheet("주소록");

HSSFSheet sheet2 = workbook.createSheet();

//행 만들기

HSSFRow row1 = sheet1.createRow(0);

HSSFRow row2 = sheet1.createRow(1);

//cell 만들기

row1.createCell(0).setCellValue("번호");

row1.createCell(1).setCellValue("이름");

row1.createCell(2).setCellValue("전화번호");

row2.createCell(0).setCellValue(1);

row2.createCell(1).setCellValue("홍길동");

row2.createCell(2).setCellValue("010-2345-5678");

FileOutputStream fos = new FileOutputStream(f);

workbook.write(fos);

}catch(Exception e){

System.out.println(e.getMessage());

}

}

public static void main(String[] args) {

new ExcelWrite();

}

}