| 요약 |
| |
| 함수종류 SINGLE ROW FUNCTION,MULTIPLE ROW FUNCTION 존재 |
| 문자대소 UPPER, LOWER, INITCAP |
| 문자조작함수 SUBSTR, LENGTH, INSTR, LPAD, RPAD, CONCAT |
| 열연결함수 CONCATONATION |
| 숫자함수 ROUND, TRUNC, MOD |
| 날짜함수 SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY |
| 형변환함수 암시적 형변환,명시적 형변환 |
| 다중열함수 COUNT(SAL),COUNT(*),MIN(SAL)최소,MAX(SAL)최대,SUM(SAL)더하기,AVG(SAL)평균 등 |
| 일반 함수 DECODE,NVL |
| GROUP BY 원하는 그룹을 묶어 비교하고 정보 출력한다. |
| 절들의 순서 select col,col,alias,'~~~',*,distict,||,f |
| 1.from tab1,tab2... |
| 2.where condition -> dustkswk =>1.>,<,>=,<=,=,!=,<>,between and in like is not null |
| 3.group by 컬럼,컬럼 |
| 4.or5 having condition |
| 6.order by 컬럼 asc 컬럼 asc |
| alias desc desc |
| position |
| |
| |
| --------------------------------------------------------------------------------------------------------------------------------------- |
| function |
| 종류 |
| |
| |
| 1개 input -> 1개 output => single row function |
| n개 input -> 1개 ouptut => multiple row function 이라고 한다. |
| |
| 함수명(input)으로 이루어져 있으며 함수명에 따라 나오는 결과가 다르다 . |
| 함수의 수는 무궁 무진하고 또 각 기업에서 쓰는 함수도 다르니 큰 틀에서의 함수만 배우고 실무에서 익히도록 한다. |
| 데이터의 일부분을 가공하고 싶을 때 함수의 이름과 그안에 넣어야 하는 요소를 알면 함수를 이용해 데이터를 가공할 수 있도록 만든 기능이다. |
| |
| ------------------------------------------------------------------------------------------------ |
| *upper대문자로만 보여주기 . lower 소문자로만 보여주기 .initcap 앞글자만 대문자로 보여주기 |
| ex) select upper(ename),lower(ename), initcap(ename) |
| from emp; |
| |
| |
| UPPER(ENAM LOWER(ENAM INITCAP(EN |
| ---------- ---------- ---------- |
| SMITH smith Smith |
| ALLEN allen Allen |
| WARD ward Ward |
| JONES jones Jones <-single row function |
| MARTIN martin Martin |
| BLAKE blake Blake |
| CLARK clark Clark |
| SCOTT scott Scott |
| KING king King |
| TURNER turner Turner |
| ADAMS adams Adams |
| |
| UPPER(ENAM LOWER(ENAM INITCAP(EN |
| ---------- ---------- ---------- |
| JAMES james James |
| FORD ford Ford |
| MILLER miller Miller |
| |
| |
| ------------------------------------------------------------------------------------------------ |
| *SUBSTR=SUBSTRING으로 이름에서 첫글자부터 3번째 글자 까지만 보여달라 |
| *SUBTSTR(ENAME,4)-> 4번째글자부터 끝까지 보여달라 |
| *LENGTH(ENAME) -> 글자 수를 보여달라 |
| *INSTR(ENAME,'A') ->A라는 글자가 몇번째 글자인지 보여달라 |
| *LPAD(SAL,10'*')->채운다 월급을 10자리에서 급여를 오른쪽에 쓰고 나머지를 왼쪽*으로 채워라) |
| EX)******3000 |
| 10 자리 |
| *RPAD(SAL,10,'*')->왼쪽에 월급을 채우고 10자리에서 월급을 채운 나머지를 *채워라 |
| EX)3000****** |
| 10 |
| *쉽게 설명 |
| RPAD(ENAME,8,' ') <=8칸정도가 주어지는데 그 안에 이름을 쓰고 나머지를 공백으로 채운다. |
| EX SCOTT <-4칸 잡아먹음 그래서 나머지 4칸은 공백으로 채운다. |
| |
| |
| SQL> select substr(ename,1,3), substr(ename,4),length(ename),instr(ename,'A'),LP |
| AD(SAL,10,'*'),RPAD(SAL,10,'*') |
| FROM EMP; |
| SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) INSTR(ENAME,'A') LPAD(SAL,10,'*') |
| ------ -------------- ------------- ---------------- -------------------- |
| RPAD(SAL,10,'*') |
| -------------------- |
| SMI TH 5 0 *******800 |
| 800******* |
| |
| ALL EN 5 1 ******1600 |
| 1600****** |
| |
| WAR D 4 2 ******1250 |
| 1250****** |
| |
| |
| SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) INSTR(ENAME,'A') LPAD(SAL,10,'*') |
| ------ -------------- ------------- ---------------- -------------------- |
| RPAD(SAL,10,'*') |
| -------------------- |
| JON ES 5 0 ******2975 |
| 2975****** |
| |
| MAR TIN 6 2 ******1250 |
| 1250****** |
| |
| BLA KE 5 3 ******2850 |
| 2850****** |
| |
| |
| SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) INSTR(ENAME,'A') LPAD(SAL,10,'*') |
| ------ -------------- ------------- ---------------- -------------------- |
| RPAD(SAL,10,'*') |
| -------------------- |
| CLA RK 5 3 ******2450 |
| 2450****** |
| |
| SCO TT 5 0 ******3000 |
| 3000****** |
| |
| KIN G 4 0 ******5000 |
| 5000****** |
| |
| |
| SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) INSTR(ENAME,'A') LPAD(SAL,10,'*') |
| ------ -------------- ------------- ---------------- -------------------- |
| RPAD(SAL,10,'*') |
| -------------------- |
| TUR NER 6 0 ******1500 |
| 1500****** |
| |
| ADA MS 5 1 ******1100 |
| 1100****** |
| |
| JAM ES 5 2 *******950 |
| 950******* |
| |
| |
| SUBSTR SUBSTR(ENAME,4 LENGTH(ENAME) INSTR(ENAME,'A') LPAD(SAL,10,'*') |
| ------ -------------- ------------- ---------------- -------------------- |
| RPAD(SAL,10,'*') |
| -------------------- |
| FOR D 4 0 ******3000 |
| 3000****** |
| |
| MIL LER 6 0 ******1300 |
| 1300****** |
| |
| |
| --------------------------------------------------------------------------------------------------------------------------- |
| *CONCATNATION 두 열을 연결시키라는 명령어. (연결 연산자 ||와 비슷) |
| ex)SQL> SELECT CONCAT(ENAME,JOB) |
| 2 FROM EMP; |
| ------------------------------------------------------------------------------------------------------------------------ |
| *수학 함수 ROUND 반올림 45.129에서 소수점 기준으로 두번 뒤로 가면 2인데 그 뒷자리수를 반올림할지 말지 결정하라는 뜻 |
| TRUNC 절삭 45.129에서 소수점 기준으로 두번 뒤로 가서 2뒤의 자리수를 잘라라. (-도 가능하다.) |
| SQL> SELECT ROUND (45.129,2), TRUNC(45.129,20) |
| 2 FROM EMP; |
| ------------------------------------------------------------------------------------------------------------------------- |
| *mod 함수 앞에있는 값을 뒤에있는 값으로 나누고 남은 나머지를 말한다 |
| |
| SQL> select mod(101,2) |
| 2 from dual; |
| ------------------------------------------------------------------------------------------------------------------------- |
| *sysdate는 오늘 날짜와 현재 시간을 알려준다 |
| SQL> select sysdate |
| 2 from dual; |
| |
| select sysdate+1 또는 -1을 해주면 내일이나 어제가 나오도록 되어있다. |
| 응용하면 select sysdate-hiredate를 해주면 오늘날짜에서 입사날짜를 빼주는데 근속일수가 나온다. |
| SQL> select sysdate-hiredate |
| 2 from emp; |
| ------------------------------------------------------------------------------------------------ |
| select hiredate, months_between(sysdate,hiredate) |
| |
| 두날짜간의 거리르 월수로 환산해준다. |
| 소수점까지 붙는데 시분초까지 계산해 나온다. |
| |
| 중첩함수 sysdate도 함수 months_between도 함수 함수안에 함수가 존재하는것(과로안에 함수가 먼저 실행된다.) |
| ex) |
| 입사일로부터 6개월 후 금요일 |
| SQL> select ename , hiredate ,next_day(add_months(hiredate,6),'금') |
| 2 from emp; |
| |
| |
| |
| add_months(hiredate,6) |
| 입사 날짜로부터 6개월 후의 날짜를 알려준다. 이용 -> 수습 기간을 계산하기 위해 쓴다. |
| |
| next_day(hiredate,'금') |
| 입사날짜로부터 돌아오는 가장 가까운 금요일을 말한다. |
| |
| last_day(hiredate) |
| 해당 월의 마지막 날을 계산하게 된다. |
| 1 select hiredate, months_between(sysdate,hiredate), |
| 2 add_months(hiredate,6), |
| 3 next_day(hiredate,'금'), |
| 4 last_day(hiredate) |
| 5* from emp |
| |
| ------------------------------------------------------------------------------------------------------------------------------ |
| 암시적 형변환과 명시적 형변환 함수 |
| |
| 형변환에는 암시적 형변환(자동)과 명시적 형변환(수동)이 존재한다. |
| 주의할 점은 암시적 형변환은 버전업을 할때 지원을 안할 수가 있기 때문에 기존에 암시적 형변환으로 짯던 코드가 |
| 버전업을 함과 동시에 에러로 변할 수가 있다 . |
| |
| 명시적 형변환은 수동이기 때문에 구축하는데 시간이 들지만 호환성이 있다는 장점이 있다. |
| |
| 형변환 함수 |
| number <- char(문자) -> date |
| [to_number] [to_date] |
| -> <- |
| [to_char] [to_char] |
| to_변환형(기존값,'형식') |
| |
| ex )select sysdate_'1980/01/01' to_date <-''안의 문자를 날짜로 인식 해야하니 |
| select sysdate-to_date('1980/01/01','yyyy/mm/dd') |
| |
| |
| SQL> select sysdate -to_date('1991/10/21','yyyy/mm/dd') <-yyy mm dd는 연도 달 일을 형식으로 표현한것이다. |
| 2 from emp; |
| |
| SYSDATE-TO_DATE('1991/10/21','YYYY/MM/DD') |
| ------------------------------------------ |
| 10207.5102 |
| 10207.5102 |
| 10207.5102 |
| 10207.5102 |
| 10207.5102 |
| 10207.5102 |
| 10207.5102 |
| 10207.5102 |
| 10207.5102 |
| 10207.5102 |
| 10207.5102 |
| |
| SYSDATE-TO_DATE('1991/10/21','YYYY/MM/DD') |
| ------------------------------------------ |
| 10207.5102 |
| 10207.5102 |
| 10207.5102 |
| |
| SQL> select to_Char(sysdate,'cc yyyy/mm/dd:hh24:mi:ss') <-시간표시 |
| 2 from dual; |
| |
| |
| SQL> select ename, to_char(sal,'$99,999.99') <- 달러표시 |
| 2 from emp; |
| |
| SQL> select ename, to_char(sal,'L99,999.99') <-원화 표시 |
| 2 FROM EMP; |
| |
| |
| 형변환 예시 MGR의 NULL을 CEO로 변환시키기 |
| |
| SQL> SELECT EMPNO,ENAME,NVL(TO_CHAR(MGR,'9999'),'CEO') <- CEO를 숫자로 바꿀순 없다. 수를 문자로 바꾸는것은 가능하기 때문에 |
| 2 FROM EMP; 수 열인 MGR을 문자로 바꾸어준다. |
| |
| EMPNO ENAME NVL(T |
| ---------- ---------- ----- |
| 7369 SMITH 7902 |
| 7499 ALLEN 7698 |
| 7521 WARD 7698 |
| 7566 JONES 7839 |
| 7654 MARTIN 7698 |
| 7698 BLAKE 7839 |
| 7782 CLARK 7839 |
| 7788 SCOTT 7566 |
| 7839 KING CEO |
| 7844 TURNER 7698 |
| 7876 ADAMS 7788 |
| |
| EMPNO ENAME NVL(T |
| ---------- ---------- ----- |
| 7900 JAMES 7698 |
| 7902 FORD 7566 |
| 7934 MILLER 7782 |
| |
| |
| ----------------------------------------------------------------------------------------------------------------------------- |
| NVL 함수 NULL이라면 특정한 것으로 표시하도록 하는 함수다. |
| 대신 비교대상의 데이터 타입이 같아야 한다. |
| COMM은 숫자 -> NULL 지정도 숫자로 해야한다. 0 |
| ENAME은 문자-> NULL 지정도 문자로 해야한다. 'NO NAME' |
| |
| SQL> SELECT ENAME,SAL*12+NVL(COMM,0) |
| 2 FROM EMP; |
| |
| ------------------------------------------------------------------------------------------------------------------------------ |
| DECODE 함수 |
| 연봉 인상 하는 방법 & 근속 상태를 보기위해 쓰이고 있다. |
| |
| EX) JOB ==>MANAGER 10% |
| JOB==>CLERK 5% |
| 그외 1% |
| CASE문 (모든 DB 적용 가능) |
| WHEN JOB='MANAGER' THEN SAL*1.1 |
| WHEN JOB='CLERK' THEN SAL*1.05 |
| ELSE |
| SAL*1.1 |
| END "UPSAL2" |
| |
| (ORACLE에서만 적용가능) |
| SQL> SELECT ENAME, JOB, SAL, |
| 2 DECODE(JOB,'MANAGER',SAL*1.1, |
| 3 'CLERK',SAL*1.05, |
| 4 SAL*1.01)"UPSAL" |
| 5 FROM EMP; |
| |
| --------------------------------------------------------------------------------------------------------------------- |
| multiple row function에 대해서 |
| group function이라는 말을 더 많이 사용한다. |
| sum의경우 여러개의 값을 더해 하나의 값을 출력함. |
| SELECT COUNT(SAL),COUNT(*),MIN(SAL)최소,MAX(SAL)최대,SUM(SAL)더하기,AVG(SAL)평균 |
| |
| ex) |
| |
| 위의 함수는 수를 다룬다 |
| 예외적으로 문자를 함수로 계산할 수 있는데 그게 가능한 이유는 문자를 인식하는데 ascii코드인 수로 인식하기 때문이다 . |
| 즉 문자도 최소 최대가 존재하는 것이다. |
| SELECT MIN(ENAME) |
| |
| SQL> SELECT ROUND(AVG(SAL),1) |
| 2 FROM EMP |
| 3 WHERE DEPTNO=10; |
| |
| ROUND(AVG(SAL),1) |
| ----------------- |
| 2916.7 |
| |
| *특이사항 : MULTIPLE ROW FUNCTION들은 계산시 표에 NULL이 있다면 제외하도록 되어있다. |
| 수를 다 계산하고 끝에 널이 있으면 결국 NULL이 되기 때문에 값을 계산하는게 무의미해지기 때문 |
| |
| ---------------------------------------------------------------------------------------------------------------------------------- |
| 반복적인 계산을 줄이는 방법 |
| groub by (목적) |
| |
| ex)SQL> select avg(sal) |
| 2 from emp |
| 3 group by deptno; <-deptno는 부서번호인데 부서번호별로 소규모 그룹을 묶어주고 select명령어를 따라준다. |
| |
| AVG(SAL) |
| ---------- |
| 1566.66667 |
| 2175 |
| 2916.66667 |
| |
| group별로 표를 만들어 출력하는 방법 |
| order by (목적) |
| select sal |
| from emp |
| order by deptno; |
| |
| select deptno, avg(sal) |
| from emp |
| group by deptno; 여기서 열에선 gorup by절에 적용한 열(deptno)만 같이 출력할 수가 있다. |
| 다른 것은 안된다. |
| group의 조건을 두가지로 하려면 group by 조건, 조건 으로 해주면 된다. |
| |
| > select deptno,job,sum(sal) |
| from emp |
| group by job,deptno; <-deptno,job에 일치하는 것들끼리 묶는다. |
| |
| DEPTNO JOB SUM(SAL) |
| ------- --------- ---------- |
| 20 MANAGER 2975 |
| 10 PRESIDENT 5000 |
| 10 CLERK 1300 |
| 30 SALESMAN 5600 |
| 20 ANALYST 6000 |
| 30 MANAGER 2850 |
| 10 MANAGER 2450 |
| 30 CLERK 950 |
| 20 CLERK 1900 |
| |
| |
| group 이용중 having절 |
| group 중에 having절의 조건을 만족시키는 그룹들만 출력하라는 기능. |
| |
| |
| select deptno,avg(sal) |
| from emp |
| group by deptno |
| having avg(sal) >= 2000; <-이천 이상의 평균 급여를 가진 그룹들만 뽑아라라는 명령어. |
| |
| DEPTNO AVG(SAL) |
| ------ ---------- |
| 20 2175 |
| 10 2916.66667 |
| |
| *where과 having의 차이 |
| where는 row에 조건을 가하는것. |
| having은 그룹에 조건을 가하는것. |
| |
| 1 select deptno,job,sum(sal) |
| 2 from emp |
| 3 group by job,deptno |
| 4* having avg(sal) >=1500 <-group에선 2가지 조건으로 나누고 having을 통해 걸러낸다. |
| SQL> / |
| |
| DEPTNO JOB SUM(SAL) |
| ---------- --------- ---------- |
| 20 MANAGER 2975 |
| 10 PRESIDENT 5000 |
| 20 ANALYST 6000 |
| 30 MANAGER 2850 |
| 10 MANAGER 2450 |
| |
| |
| *절들의 순서 |
| 4.or5select col,col,alias,'~~~',*,distict,||,f <-순서를 그대로 해야 에러없이 할 수 있다. |
| 1.from tab1,tab2... |
| 2.where condition -> dustkswk =>1.>,<,>=,<=,=,!=,<>,between and in like is not null |
| 3.group by 컬럼,컬럼 |
| 4.or5having condition |
| 6.order by 컬럼 asc 컬럼 asc |
| alias desc desc |
| position |
| ex) |
| 1 select deptno,job,avg(sal) |
| 2 from emp |
| 3 where sal >1000 |
| 4 group by job,deptno |
| 5 having avg(sal) >=1300 |
| 6* order by avg(sal) asc |
| SQL> / |
| |
| DEPTNO JOB SUM(SAL) |
| ---------- --------- ---------- |
| 10 CLERK 1300 |
| 30 SALESMAN 5600 |
| 10 MANAGER 2450 |
| 30 MANAGER 2850 |
| 20 MANAGER 2975 |
| 20 ANALYST 6000 |
| 10 PRESIDENT 5000 |