SQL-연산자02 & 내장함수
WHERE 조건식이 2개 이상일 때
- 논리연산자
- AND : 두조건 모두 만족하는 검색.
- OR : 둘 중 하나라도 만족하는 행을 검색.
//<퀴즈> int a=3, b=5, c=7, d=9; if(a>b && c<d){ System.out.println("안녕"); } //-----> c<d 비교연산을 할까? -> 당연히 안 한다. //이유 : 자바는 앞에서부터 비교연산하기 때문에!!a,b,c,d: 컬럼(number) where a>b AND c<d //-----> c<d 비교연산을 할까? -> SQL은 비교를 한다. //이유 : SQL은 뒤에서부터 비교연산을 하기 때문에!!
- NOT 연산자
- 조건에 만족하지 못하는 행을 검색.
- 위치 : 조건식 앞에 또는 (LIKE, BETWEEN, IN, NULL) 앞에 위치
SELECT EMPNO, ENAME, DEPTNO, JOB FROM EMP WHERE DEPTNO NOT IN (10,20);
EMPNO ENAME DEPTNO JOB
---------- -------------------- ---------- ------------------
7499 ALLEN 30 SALESMAN
7521 WARD 30 SALESMAN
7654 MARTIN 30 SALESMAN
7698 BLAKE 30 MANAGER
7844 TURNER 30 SALESMAN
7900 JAMES 30 CLERK
SELECT EMPNO, ENAME, DEPTNO, JOB FROM EMP WHERE ENAME NOT LIKE '%A%';
EMPNO ENAME DEPTNO JOB
----- -------------------- ---------- ------------------
7369 SMITH 20 CLERK
7566 JONES 20 MANAGER
7788 SCOTT 20 ANALYST
7839 KING 10 PRESIDENT
7844 TURNER 30 SALESMAN
7902 FORD 20 ANALYST
- NULL 비교
- 형식 : 컬럼명 IS NULL; 또는 컬럼명 IS NOT NULL;
- 미확정 데이터 (같은 행에서 입력되지 않는 데이터)
- 연산, 할당, 비교가 불가능!! (결과행에서 배제) → 블랙홀!!
SELECT EMPNO, ENAME, DEPTNO, COMM FROM EMP WHERE COMM IS NOT NULL; EMPNO ENAME DEPTNO COMM ----- -------------------- ---------- ---------- 7499 ALLEN 30 300 7521 WARD 30 500 7654 MARTIN 30 1400 7844 TURNER 30 0
- NVL함수
- null변환 함수
- 형식 : NVL(null값이 예상되는 컬럼명, 대체할 데이터)
- 주의: 첫번째인자와 두번째인자는 서로 자료형이 같아야 함!!
- 자료형 변환함수
- to_number(), to_date(), to_char()
- 문제) 사원들의 사원번호, 사원명, 커미션을 출력하되 커미션을 받지않는(커미션의 값이 null) 사원들은 ‘없음’으로 출력하시오.
SELECT EMPNO, ENAME, NVL(COMM,'없음') from emp;// 이렇게 하면 오류-> 인자의 자료형이 달라서 SELECT EMPNO, ENAME, NVL(TO_CHAR(COMM),'없음') from emp; // 이렇게 하는게 맞음 EMPNO ENAME NVL(TO_CHAR(COMM),'없음') ----- -------------------- ------------------------- 7369 SMITH 없음 7499 ALLEN 300 7521 WARD 500 7566 JONES 없음 7654 MARTIN 1400 7698 BLAKE 없음 7782 CLARK 없음
숫자 함수
- ROUND함수: 반올림(4이하는 버리고 5이상은 올림)
- 형식
select round(45.56) from dual; ROUND(45.56) ------------ 46 SELECT ROUND(45.575,2) FROM DUAL; ROUND(45.575,2) --------------- 45.58 - TRUNC함수 : 버림함수
- 형식
SELECT TRUNC(45.89) FROM DUAL; TRUNC(45.89) ------------ 45 SELECT TRUNC(45.89,1) FROM DUAL; TRUNC(45.89,1) -------------- 45.8 - FLOOR함수
- 실수—> 정수 (소수점 이하 다 버리기)
- 형식
SELECT FLOOR(45.5416178) FROM DUAL; FLOOR(45.5416178) ----------------- 45 - MOD함수 **: 나머지 구하기
- 형식
SELECT MOD(10,3) FROM DUAL; MOD(10,3)
문제) 10을 3으로 나누었을때 몫과 나머지를 구하시오.
SELECT FLOOR(10/3) FROM DUAL;
SELECT MOD(10,3) FROM DUAL;
문제) 사원번호가 짝수인 사원의 사원번호, 사원명을 출력하시오.
SELECT EMPNO, ENAME FROM EMP WHERE MOD(EMPNO,2)=0;
EMPNO ENAME
---------- --------------------
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7844 TURNER
문자 함수
- UPPER함수 : 대문자 변환 함수
- 형식
SELECT UPPER('hello sql') FROM DUAL; - LOWER함수 : 소문자 변환 함수
- 형식
SELECT LOWER('HELLO SQL') FROM DUAL; - INITCAP : 이니셜에 대한 대문자 변환 (빈 칸을 기준으로 이니셜 구분, 나머지는 소문자로)
- 형식
SELECT INITCAP('welcom to ORACLE') FROM DUAL; INITCAP('WELCOMTOORACLE') -------------------------------- Welcom To Oracle문제) 30번 부서의 사원명과 사원번호를 출력하시오. (단, 사원명의 첫글자만 대문자로 출력)
SELECT EMPNO, INITCAP(ENAME) FROM EMP; EMPNO INITCAP(ENAME) ---------- -------------------- 7369 Smith 7499 Allen 7521 Ward 7566 Jones 7654 Martin 7698 Blake 7782 Clark - LENGTH함수 : 문자열 길이
- 형식
SELECT LENGTH('HI HEELLO') FROM DUAL; LENGTH('HIHEELLO') ------------------ 9 SELECT LENGTHB('김명관') FROM DUAL; // LENGTHB는 문자열의 바이트수를 출력/한글은 개장 3바이트, 알파벳은 개당 1바이트 ----------------- 9 - INSTR함수 : 특정문자가 출현하는 위치(숫자)를 얻어오는 함수(자바 indexOf(‘a’))
- DB는 인덱스가 1부터 시작 / 프로그램은 0
- 형식
instr(기준데이터, 특정문자); instr(기준데이터, 특정문자, 시작인덱스, 출현횟수) SELECT INSTR('WELCOME TO ORACLE','O') FROM DUAL; INSTR('WELCOMETOORACLE','O') ---------------------------- 5 SELECT INSTR('WELCOME TO ORACLE','O',1,2) FROM DUAL; //첫번째 부터 시작해 2번째 O찾기 INSTR('WELCOMETOORACLE','O',1,2) -------------------------------- 10 - SUBSTR : 문자열 일부분 추출, 부분 문자열 얻기
- 형식
substr (기준문자열, 시작인덱스, 잘라낼 문자열갯수 ) SELECT SUBSTR('WELCOME TO ORACLE',12,6) FROM DUAL; SUBSTR('WELC ------------ ORACLE - LPAD : 오른쪽 정렬후 왼쪽 공백에 특정 문자 채우기.
- RPAD : 왼쪽 정렬후 오른쪽 공백에 특정 문자 채우기.
- 형식
lpad (기준문자열, 전체길이, 채울문자) rpad(기준문자열, 전체길이, 채울문자) SELECT LPAD('ORACLE',10,'&') FROM DUAL; LPAD('ORACLE',10,'&' -------------------- &&&&ORACLE - TRIM함수 : 좌우의 연속된 공백을 제거해 주는 함수
- RTRIM : (연속되는)오른쪽 공백 또는 (연속되는)특정문자 삭제
- LTRIM : (연속되는)왼쪽 공백 또는 (연속되는)특정문자 삭제
문자열 : ' oracle ' 앞에 4개의 공백, 뒤에 3개의 공백 SELECT TRIM(' oracle ') FROM DUAL; TRIM('ORACLE ------------ oracle SELECT LTRIM(' oracle ') FROM DUAL; LTRIM('ORACLE') ------------------ oracle SELECT RTRIM('ORACLEEEEEEEEEEEE','E') FROM DUAL; RTRIM('ORA ---------- ORACL
문제) 사원번호와 사원명, 입사일을 출력하되 입사일은 년도만 출력하시오. (예: ‘80/12/17’ —> ‘80’)
SELECT EMPNO, ENAME, SUBSTR(TO_CHAR(HIREDATE),1,2) AS YEAR FROM EMP;
EMPNO ENAME YEAR
---------- -------------------- ----------------
7369 SMITH 80
7499 ALLEN 81
7521 WARD 81
7566 JONES 81
7654 MARTIN 81
7698 BLAKE 81
7782 CLARK 81
7788 SCOTT 87
날짜 함수
- SYSDATE 함수 ★ : 현재 날짜 구하기 - 오라클만 가능
SELECT SYSDATE FROM DUAL; //오늘
SYSDATE
--------
19/01/18
SELECT SYSDATE+1 FROM DUAL; //내일
SYSDATE+
--------
19/01/19
- MONTHS_BETWEEN함수 : 날짜와 날짜사이의 개월을 계산해 주는 함수
- 형식
- MONTHS_BETWEEN(큰 DATE ,작은 DATE)
- 문제) 각 사원의 사원명, 입사일, 현재까지 근무한 개월 수를 출력하시오.
SELECT ENAME, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) AS 근무개월수 FROM EMP; ENAME HIREDATE 근무개월수 -------------------- -------- ---------- SMITH 80/12/17 457.052063 ALLEN 81/02/20 454.955289 WARD 81/02/22 454.890772 JONES 81/04/02 453.535934 MARTIN 81/09/28 447.697224 BLAKE 81/05/01 452.568192 - 문제) 작년 광복절 이후 현재까지의 달수를 출력하시오!!
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('18/08/15')) FROM DUAL; MONTHS_BETWEEN(SYSDATE,'18/08/15') ---------------------------------- 5.11661514 - ADD_MONTHS 함수 : 날짜에 개월을 더한 날짜 계산
SELECT ADD_MONTHS(SYSDATE, 2) FROM DUAL; ADD_MONT -------- 19/03/18 - LAST_DAY 함수 : 월의 마직막 날짜를 계산
SELECT LAST_DAY(SYSDATE) FROM DUAL; LAST_DAY -------- 19/01/31 - 문제) 2017년 11월의 마지막 날짜는?
SELECT LAST_DAY(ADD_MONTHS(SYSDATE,1)) FROM DUAL; LAST_DAY -------- 19/02/28 - NEXT_DAY 함수 : 특정날짜 후의 첫 요일의 날짜를 계산.
SELECT NEXT_DAY(SYSDATE, '월') FROM DUAL; NEXT_DAY -------- 19/01/21 - 문제) 사원들이 입사한 달의 마지막 날짜를 출력하시오.
SELECT EMPNO, ENAME, HIREDATE, LAST_DAY(HIREDATE) AS LST FROM EMP; EMPNO ENAME HIREDATE LST ---------- -------------------- -------- -------- 7369 SMITH 80/12/17 80/12/31 7499 ALLEN 81/02/20 81/02/28 7521 WARD 81/02/22 81/02/28 7566 JONES 81/04/02 81/04/30 7654 MARTIN 81/09/28 81/09/30 7698 BLAKE 81/05/01 81/05/31 7782 CLARK 81/06/09 81/06/30 7788 SCOTT 87/04/19 87/04/30
형변환 함수
TO_CHAR TO_CHAR
--------------> <-----------------
NUMBER CHARACTER DATE
<-------------- ------------------>
TO_NUMBER TO_DATE
-
형식 : TO_CHAR(number date, ‘format’) YYYY(년도 4자리), YY(년도 2자리),
-
포맷형식
MM(월), DD(일), DAY또는DY(요일), HH (1~12) HH24(0~23) MI(분) SS(초)
- 문제) 20번 부서에 근무하는 사원의 사원번호, 사원명, 입사일을 출력
===> 입사일의 년도를 4자리로 표현!!
SELECT EMPNO, ENAME, TO_CHAR(HIREDATE, 'YYYY/MM/DD') FROM EMP;
EMPNO ENAME TO_CHAR(HIREDATE,'YY
---------- -------------------- --------------------
7369 SMITH 1980/12/17
7499 ALLEN 1981/02/20
7521 WARD 1981/02/22
7566 JONES 1981/04/02
7654 MARTIN 1981/09/28
7698 BLAKE 1981/05/01
7782 CLARK 1981/06/09
7788 SCOTT 1987/04/19
===> 입사일을 ‘1980년 12월 17일’ ==> 포맷에 지정되지 않은 문(예:년, 월, 일)를 사용할 때는 큰 따옴표와 함께 사용.
SELECT EMPNO, ENAME, TO_CHAR(HIREDATE, 'YYYY"년"MM"월"DD"일"') FROM EMP;
EMPNO ENAME TO_CHAR(HIREDATE,'YYYY"년"MM"월"DD
---------- -------------------- ----------------------------------
7369 SMITH 1980년12월17일
7499 ALLEN 1981년02월20일
7521 WARD 1981년02월22일
7566 JONES 1981년04월02일
7654 MARTIN 1981년09월28일
7698 BLAKE 1981년05월01일
7782 CLARK 1981년06월09일
7788 SCOTT 1987년04월19일
- 문제) 오늘 날짜를 ‘2019년 1월 18일 3시 20분 32초 (금요일)’ 형태로 출력하시오.
SELECT TO_CHAR(SYSDATE, 'YYYY"년 "MM"월 " DD"일 "HH"시 " MI"분 " SS"초 "(DAY)') FROM DUAL; TO_CHAR(SYSDATE,'YYYY"년"MM"월"DD"일"HH"시"MI"분"SS"초"(DAY)') -------------------------------------------------------------------------------- 2019년 01월 18일 03시 37분 24초 (금요일) - 문제) 오늘 날짜에서 2018년 10월 18일을 빼시오.(날짜 차이 구하기)
SELECT SYSDATE - TO_DATE('2018/10/18') FROM DUAL; SYSDATE - TO_DATE('2018/10/18') ----------------------------- 92.6555324형식) TO_DATE(문자 [, ‘포맷’])
그룹함수(집계함수)
- 전체데이터를 그룹별로 구분하여 통계적인 결과를 구하기 위해 사용.
- 결과값은 절대적으로 항상 단행!!
- 그룹함수와 단순컬럼은 함께 사용하는 것이 불가능!
SELECT ENAME, AVG(SAL) FROM EMP; --> 이런게 불가능하다는 뜻 - 만약 그룹함수와 함께 사용하고자 하는 컬럼이 그룹으로 묶여 질 수 있다면 group by절과 함께 사용하는 것이 가능
-
NULL은 결과값에서 제외
- 종류)
- STDDEV(표준편차), VARIANCE(분산)
-
SUM(총합), AVG(평균), COUNT(행갯수), MIN(최소값), MAX(최대값),
-
형식) ==> 그룹함수명(컬럼명)
- 문제) 사원들의 전체 급여 총합을 구하시오.
select sum(sal) from emp; - 문제) 가장 적게 받는 급여를 출력하시오.
select min(sal) from emp; - 문제) 가장 많이 받는 급여를 출력하시오.
select max(sal) from emp; - 문제) 전체 급여에 대한 평균을 구하시오.
select avg(sal) from emp; - 문제) 급여총합, 최소급여, 최대급여, 급여평균을 구하시오.
SELECT SUM(SAL), MIN(SAL), MAX(SAL), AVG(SAL) FROM EMP; SUM(SAL) MIN(SAL) MAX(SAL) AVG(SAL) ---------- ---------- ---------- ---------- 29025 800 5000 2073.21429 - 문제) 부서번호와 급여평균을 출력하시오.★★
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO; DEPTNO AVG(SAL) ---------- ---------- 30 1566.66667 20 2175 10 2916.66667 // -> 부서별 평균급여 - 문제) 사원들의 직급종류는 몇 개인지 출력하시오.
SELECT COUNT(DISTINCT JOB) FROM EMP;
COUNT(DISTINCTJOB)
------------------
- 문제) 전체사원의 수를 구하시오.
SELECT COUNT(*) FROM EMP; COUNT(*) ---------- 14 - 문제) 가장 오래 근무한 사원의 입사일을 구하시오.
SELECT MIN(HIREDATE) FROM EMP; MIN(HIRE -------- 80/12/17 - 문제) 부서의 평균급여가 2000이상인 부서의 부서번호와 평균 금여를 구하시오
- WHERE절에는 그룹함수를 사용할 수 없음!! → 그룹함수는 HAVING절로 ㄱㄱ!
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>=2000; DEPTNO AVG(SAL) ---------- ---------- 20 2175 10 2916.66667 - 문제) 평균급여보다 더 많은 급여를 받는 사원의 번호, 이름, 급여를 출력하시오.
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP); // (SELECT AVG(SAL) FROM EMP) -> 이게 바로 하부쿼리 -> 하나의 데이터로만 (1X1) 표현되기 때문에 가능한 것 EMPNO ENAME SAL ---------- -------------------- ---------- 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7902 FORD 3000
<SELECT형식 - 최종완성>
- SELECT [DISTINCT] 컬럼명 [AS] 별명, 1행1열의 데이터
- FROM 테이블명
- WHERE (전체 행에 대한, 사원테이블의 경우 14행에 대한)조건식 - 사용되는 연산자 (=, LIKE, IN, BETWEEN, IS NULL, NOT, OR, AND)
- GROUP BY (그룹묶일 조건의 ) 컬럼명
- HAVING (그룹에 대한, GROUP BY에 DEPTNO 있을 시 3행에 대한)조건식 - GROUP BY랑만 쓰여야함, 혼자 못쓰임
- ORDER BY (정렬하고자하는) 컬럼명 [DESC];
- 위 형식의 실행순서 : 2 → 3 → 4 → 5 → 1(조회) → 6
댓글남기기