151006 DB 함수

2. FUNCTION

1) Single Row Function : 단일행 함수

1. 문자 함수

Lower(), Upper(), Substr(), Length(), Instr(), Ltrim(), Rtrim(), Translate(), Replace(), Chr(), Ascii()

2. 숫자 함수

Round()반올림, Trunc()버림, Floor()올림, Ceil()내림, Mod()나머지, Power()거듭제곱, Sign()부호, ...

3. 날짜 함수

sysdate. Month_Between(), Add_Months(), Next_Day(), Last_Day(), [Round(), Trunc()]

4. 변환 함수

TO_Char(), To_Date(), To_Number()

5. 기타 함수

nvl(), decode()

6. 정규식 함수(Regular Expression) 함수

Regexp_로 시작하는 함수

2) Aggregate Function : 집합 함수

sum(), avg(), max(), min(), count(), distinct()

3) Annalutic Functions

4) Object Reference Functions

5) Model Functions`

***실습

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

이름이 scott 인 직원의 이름, 부서, 급여를 조회 단, 대소문자 구별없이 검색할 수 있도록 하라.

select ename, deptno, sal from emp where ename='SCOTT' or ename = 'Scott or ename = 'scott' or ename =  'ScoTT' or ename = 'SCott'

select ename, deptno, sal from emp where Upper(ename) = upper('ScoTt');

다음의 주민번호 에서 성별에 해당하는 부분을 추출 하시오.

select Substr('123456-1234567' , 8, 1) from emp;

select Substr('123456-1234567' , 8, 1) from dual;

select Substr('123456-1234567' , 8) from emp;

*****문자열의 길이

select Length('안녕하세요...SQL 연습중입니다.') from dual;

*****문자열의 위치

select Instr('MILLER', 'L') from dual;

select Instr('MILLER', 'L', '1', '1' ) from dual;

    시작 1번째찾은문자

select Instr('MILLER', 'L', '1', '2' ) from dual;

2번째찾은문자

select Instr('MILLER', 'K') from dual; // 찾는값이 없으면 찾는값이 0개라 0이라고나옴

select Instr('MILLER', 'L', '-1', '1' ) from dual; // 시작위치를 뒤에서부터 찾게끔 하려면 -1이라고 써줌 / 결과값은 앞에서부터 4번째라 4

select Instr('MILLER', 'L', '-1', '2' ) from dual;

*****특정 문자열을 제거 ( 왼쪽/ 오른쪽 문자열 제거 )

select ltrim ('MILLER', 'M') from dual;  // 왼쪽에 M이라는글자가있으면 그문자를 지워라

select ltrim ('              MILLER')  from dual;  // 공백을 지워준다 (공백은 지정하지않아도됨)

select translate( 'MILLER', 'L', '*') from dual;  //  L 이라는 문자를 * 로 바꿔준다

select replace( 'MILLER', 'L', '*') from dual;

select sal, translate( sal, '0123456789','영일이삼사오육칠팔구')from emp; // 0=영 1=일 2=이 ...

select sal, replace( sal, '0123456789','영일이삼사오육칠팔구')from emp; // 012... = 영일이...

select replace('JACK and JUE' , 'J', 'BL') from dual; // JACK = J , JUE = BL 

select translate('JACK and JUE' , 'J', 'BL') from dual; // JACK = J , JUE = B 

*****아스키코드

select chr (65), chr (97) from dual; A a

select ascii('A'), ascii('a')from dial; 65 97

*****소수점관리, 나머지, 거듭제곱, 부호

select round(4567.678) from dual; // 소수점반올림  4568

select round(4567.678 , 2 ) from dual; // 2번째자리까지표시 소수점반올림  4567.68

select round(4567.678, -2) from dual; // 소수점반대로 2번째 반올림   4600

select trunc(4567.678 , 0) from dual; // 4567

select trunc(4567.678 , 2) from dual; // 2번째자리에서 버림4567.67

select floor(4567.678) from dual; // 소수점에서 내림 지정 X   4567

select ceil(4567.378 , 2) from dual; // 올림 4568 

select mod(10 / 3) from dual; //  3/10 의 나머지

select power (2, 10) from dual; // 2^10

select sign( 100 ), sign( -100), sign(0) from dual; //

*****날짜함수

select sysdate From dual;

select sysdate +100 from dyal;

select sysdate -10 from dyal;

select sysdate - To_Date('2015/9/7') from dual;

select months_between( sysdate, '2015/1/1') from dual;

select add_maoths(sysdate, 3) from dual; 현재날짜  + 3 월

select next_day('2014/3/16','금')from dual; 2014년 3월 16일이 있던 주의 금요일 

select last_day(sysdate) from dual; 그날짜의 달의 마지막날

select round(sysdate) from dual;

select round(to_date('15/10/20')) from dual;

select round(to_date('15/10/20'),'MONTH') from dual;

select round(to_date('15/10/20'),'YEAR') from dual;

*****변환함수

select ename, sal. to_char(sal) from emp;

select ename, sal, to_char(sal, '$999,999') from dual;

select ename, sal, to_char(sal, 'L999,999') from dual;  // L 현지 로케이션 에 알맞는 단위로 맞춰줌  \999,999

select to_char (sysdate, 'YYYY MM DD HH:MI:SS') from dual;  2015 10 06 12:00:00 

*****nvl() 과 decode()

1) 직원들의 이름, 급여, 커미션, 총급여( 급여 + 커미션 ) 을 조회

select ename, sal, comm, sal + comm as 총급여 from emp;

select ename, sal, comm, sal+nvl(comm, 0 ) as 총급여 from emp;

2) 부서코드가 10번이면 영업부, 그외의 부서는 타부서 라고 출력

select empno, ename, decode( deptno, 10 , 영업부', '타부서' )from emp;

3) 부서코드가 10번이면 영업부, 20번 이면 총무부 그외의 부서는 타부서 라고 출력

select empno, ename, decode( deptno, 10 , 영업부', '20', '총무부', '그외부서' )from emp;

*****집합함수

업무가 salesman인 직원들에 대해 급여의 평균, 최고액, 최저액, 합계를 조회

select avg(sal), max(sal), min(sal), sum(sal), from emp where job like 'SALES%';

직원이 총 몇명인가

select count(*) from emp; // * 필드의 최대값 갯수 14

select count(enpno) from emp; 14

select count (comm) from emp; 4   null값은 안침

**SELECT 의 추가문법

GROUP BY 필드명

HAVING 조건명

부서별로 급여평균, 최고급여, 최저급여, 급여합계를 조회

select deptno, avg(sal), max(sal), min(sal), sum(sal) from emp group by deptno order by sum(sal) desc;

select deptno, avg(sal), max(sal), min(sal), sum(sal), from emp 

// 실행안됨 deptno 는 1실행 집합함수는 14번실행  / 위에껀 그룹으로 묶어줘서 가능

부서별직원수를 조회 

select deptno, count(empno) from emp group by deptno;

각 부서 내에서 업무별 평균 급여, 최고급여 를 조회

select deptno, job, avg(sal), max(sal) from emp group by deptno, job;

전체 급여의 합계가 5000을 초과하는 업무에 대해 급여합계 조회

select job, sum(sal+comm) from emp where sum(sal+comm)>=5000 group by job;

select job, sum(sal) from emp group by job having sum(sal)>5000;

업무가 salesman 이 아닌 다른 업무에 대해 급여 평균을 조회

select job, avg(sal) from emp group by job having job != 'SALESMAN';

select job, avi(sal) from emp where job != 'SALESMAN' group by job;

******DML : INSERT, UPDATE, DELETE

1.계정생성

-id : testUser

-pw : 1111

2.계정에 권한부여

-Connect, Resource

3.testUser계정으로 접속하여 테이블 생성

CREATE TABLE tbltest( id number, name varchar2(10), hiredate date);

1.INSERT

insert into 테이블명[(필드명...)] VALUES(값...)

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

insert into tbltest (id, name, hiredate)

values(1, '홍길동', sysdate);

insert into tbltest(id,name) values(2, '임꺽정');

insert into tbltest values(3, '신돌석', '2015/01/01');

2.UPDATE

UPDATE 테이블명

SET 필드명 = 값[,필드명=값,,,,]

[WHERE 조건식]

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

update tbltest set hiredate = sysdate where id = 2;

3.DELETE

DELETE FROM 테이블명 [WHERE 조건식]

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

delete from tbltest where id = 1;