WHERE절
- 숫자는 그냥 사용할 수 있다.
- 문자는 홑따옴표로 감싸서 사용해야 하고 대소문자를 구분한다.
인코딩에 따라 한글 한글자가 2Byte or 3Byte로 정해진다.
모를 경우 LENGTHB()로 출력해보면 된다.
SUBSTR(문자열, 시작 위치, 추출 길이)
FLOOR(음수) : 음수보다 작은 정수 중 가장 큰 정수
- FLOOR(-123.4) -> -124
--SELECT
--FROM
--WHERE
SELECT empno, ename, sal
FROM emp
WHERE sal > 2000;
SELECT *
FROM emp
WHERE ename='SMITH';
SELECT *
FROM emp
--WHERE hiredate > '81/02/20'
WHERE hiredate > '1981-02-20';
SELECT empno, ename, sal, sal+100 AS SALPlus100, sal*1.05 AS NextYearSal
FROM emp;
SELECT *
FROM emp
--WHERE sal BETWEEN 1000 AND 3000
WHERE sal >= 1000 AND sal <= 3000;
--emp 테이블에서 부서번호 20이고 급여가 2000이상인 직원들의 사번, 이름, 급여, 부서번호
SELECT empno, ename, sal, empno
FROM emp
WHERE deptno = 20 and sal > 2000;
--grade 1 2 3 4학년
SELECT *
FROM student
--WHERE grade in (1, 2, 3);
WHERE birthday BETWEEN '1976-01-01' AND '1977-12-31';
--LIKE
SELECT *
FROM student
--WHERE name LIKE '%an%';
--WHERE name LIKE '_an%'; --an앞에 하나의 글자만 있는 경우
WHERE name LIKE '__an%';
--IS NULL, IS NOT NULL
SELECT *
FROM emp
--WHERE comm IS NULL;
WHERE comm IS NOT NULL;
--값을 입력받아서 출력하기
SELECT *
FROM emp
--WHERE deptno = &dpno;
WHERE deptno = :dpno;
--ORDER BY절, 정렬하기
SELECT *
FROM emp
--ORDER BY deptno ASC ,sal DESC;
ORDER BY job, sal DESC;
SELECT *
FROM student
WHERE grade in (3, 4)
ORDER BY grade, name;
--집합연산자
--UNION ALL(중복 포함, 정렬X)
SELECT *
FROM student
WHERE grade = 4
UNION ALL
SELECT *
FROM student
WHERE grade = 3;
SELECT studno, name
FROM student
WHERE grade = 4
UNION ALL
SELECT studno, name
FROM student
WHERE grade = 3;
--학교(학생, 교수) 학교 안에 속한 사람들 정보
SELECT studno, name, '학생' AS 신분
FROM student
UNION ALL
SELECT profno, name, '교수'
FROM professor;
--INTERSECT, 교집합
SELECT studno, name
FROM student
WHERE deptno1 = 101
INTERSECT
SELECT studno, name
FROM student
WHERE deptno2 = 201;
--단일행 함수
SELECT ename, INITCAP(ename)
FROM emp;
--기능만 확인 or 테스트
SELECT INITCAP('abcd')
FROM dual;
SELECT LOWER('ABC'), UPPER('asdf')
FROM dual;
SELECT studno, name, LOWER(name), UPPER(name)
FROM student;
SELECT LENGTH('abcd'), LENGTH('하나둘'), LENGTHB('하나둘')
FROM dual;
SELECT 'AB' || 'CD', CONCAT('AB', 'CD')
FROM dual;
SELECT 'AB' || 'CD' || 'EF', CONCAT(CONCAT('AB', 'CD'), 'EF')
FROM dual;
SELECT SUBSTR('ABCD', 3, 1)
FROM dual;
SELECT name, SUBSTR(jumin, 3, 4) Birthday
, SUBSTR(jumin, 1, 2) || '년 ' || SUBSTR(jumin, 3, 2) || '월 ' || SUBSTR(jumin, 5, 2) || '일' AS 년월일
FROM student
WHERE deptno1 = 101;
SELECT SUBSTRB('abcd', 1, 2), SUBSTRB('한글입력', 1, 5), LENGTHB('한')
FROM dual;
SELECT INSTR('asdfqwerw', 'w', 7, 2)
FROM dual;
--역순으로 접근
SELECT SUBSTR('A-B-C-D', -5, 3), INSTR('A-B-C-D', '-', -1, 3)
,INSTR('A-B-C-D', '-', -5, 2)
FROM dual;
SELECT name, tel, INSTR(tel, ')')
FROM student
WHERE deptno1 = 201;
SELECT name, tel, INSTR(tel, 3)
FROM student
WHERE deptno1 = 101;
SELECT name, tel, SUBSTR(tel, 1, INSTR(tel, ')')-1) 지역번호, SUBSTR(tel, INSTR(tel, '-')+1, 4) 뒤4자리, SUBSTR(tel, -4, 4)
FROM student
WHERE deptno1 = 201;
SELECT name, id, LPAD(ID, 10, '*')
FROM student;
SELECT ename, LPAD(ename, 9, '123456789'), RPAD(ename, 9, SUBSTR('123456789', LENGTH(ename)+1, 9-LENGTH(ename)))
,SUBSTR('123456789', LENGTH(ename)+1, 9-LENGTH(ename))
,SUBSTR('123456789', LENGTH(ename)+1)
FROM emp
WHERE deptno = 10;
SELECT ename, LTRIM(ename, 'CKM')
FROM emp
WHERE deptno = 10;
SELECT ' ABCD ', LTRIM(' ABCD '), RTRIM(' ABCD '), TRIM(' ABCD ')
FROM dual;
SELECT REPLACE('ABCDEF', 'A', 'Z')
FROM dual;
SELECT ename, REPLACE(ename, SUBSTR(ename, 2, 2), '--')
FROM emp
WHERE deptno = 20;
SELECT name, jumin, REPLACE(jumin, SUBSTR(jumin, 7), '-/-/-/-')
FROM student
WHERE deptno1 = 101;
SELECT name, tel, REPLACE(tel, SUBSTR(tel, INSTR(tel, ')')+1, 3), '***')
FROM student
WHERE deptno1 = 102;
SELECT name, tel
--,REPLACE(tel, SUBSTR(tel, INSTR(tel, '-')+1, 4), '****' )
,REPLACE(tel, SUBSTR(tel, -4, 4), '****' ) REPLACE
FROM student
WHERE deptno1 = 101;
SELECT ROUND(197.8, -1), TRUNC(423.33, 1), MOD(9, 2)
,CEIL(155.1), FLOOR(-123.4)
FROM dual;
SELECT ROWNUM, CEIL(ROWNUM/2) "ROWNUM 팀번호", ename
FROM emp;