[20일차] Oracle - SQL 문법

SOSO·2022년 5월 13일
TILsql학원
0

학원

목록 보기
19/59
post-thumbnail

DDL(Data Definition Language)


데이터와 그 구조를 정의

  • create : 데이터베이스 객체를 생성
  • drop : 데이터베이스 객체를 삭제
  • alter : 기존에 존재하는 데이터베이스 객체를 다시 정의하는 역할

DML(Data Manipulation Language)


데이터의 검색과 수정 등의 처리

  • insert : 데이터베이스 객체에 데이터를 입력
  • delete : 데이터베이스 객체에 데이터를 삭제
  • update : 기존에 존재하는 데이터베이스 객체안의 데이터 수정
  • select : 데이터베이스 객체로부터 데이터를 검색
  • merge : 조건에 따라 데이터를 테이블에 삽입 또는 갱신

DCL(Data Control Language)


데이터베이스 트랜잭션 제어

  • commit : 보류 중인 모든 변경 내용을 영구히 저장
  • rollback : 저장점 표시자까지 롤백하는 데 사용
  • savepoint : 보류 중인 데이터 변경 내용을 모두 버림

SELECT문

전체 컬럼과 매칭되는 데이터를 행단위로 모두 검색

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp;

일부 컬럼과 매칭되는 데이터를 행단위로 검색

SELECT ename, job, sal FROM emp;

*를 이용해서 전체 컬럼 호출

SELECT * FROM emp;

주석

SELECT * /*주석*/ FROM emp;

DUAL

함수 및 계산의 결과를 볼 때 사용할 수 있는 공용(PUBLIC)테이블

SELECT SYSDATE FROM dual;
SELECT ASCII('A') FROM dual;
SELECT ASCII(0) FROM dual;
SELECT 7 + 10 FROM dual;

연산

SELECT ename, sal, sal+300 FROM emp;
SELECT ename, sal, sal*12 FROM emp;
SELECT ename, sal, (sal+300)*12 FROM emp;

NULL

  • NULL은 사용할 수 없거나, 할당되지 않았거나, 알 수 없거나,적용할 수 없는 값
  • NULL값과 연산하면 결과값은 NULL임
  • NULL은 0이나 공백과는 다름

열 ALIAS 정의

  • 열 이름을 바꿈
  • 열 이름 바로 뒤에 나옴
  • 열 이름과 alias 사이에 선택 사항인 AS 키워드가 올 수도 있음
SELECT ename, sal*12 ASal FROM emp;
SELECT ename, sal*12 AS ASal FROM emp;

ALIAS에 큰따옴표를 사용하는 경우

1) 대소문자 구별을 원할 때
2) 공백 포함시
3) _,#등 특수문자 사용시
4) 숫자로 시작할 경우

SELECT ename, sal*12 "Annual Salary" FROM emp;

연결 연산자

  • 열이나 문자열을 다른 열에 연결
  • 두 개의 세로선(||)으로 나타냄
  • 연결 연산자와 null값 : 문자열에 null값을 결합할 경우 결과는 문자열
SELECT ename || ' has $' || sal FROM emp;

DISTINCT

중복행 삭제

SELECT DISTINCT deptno FROM emp;

WHERE절

조건을 명시해서 원하는 행을 검색
WHERE절에는 열 Alias를 사용할 수 없음

  • 숫자
SELECT * FROM emp WHERE deptno=10;
  • 문자열
SELECT * FROM emp WHERE ename = 'SMITH';
  • 날짜
SELECT * FROM emp WHERE hiredate = '81/12/03';
SELECT * FROM emp WHERE hiredate = '81-12-03';

비교연산자의 사용

SELECT * FROM emp WHERE hiredate < '81/05/01';
SELECT * FROM emp WHERE hiredate >= '81/05/01';
SELECT * FROM emp WHERE sal != 3000;
SELECT * FROM emp WHERE sal ^= 3000;
SELECT * FROM emp WHERE sal <> 3000;

SELECT * FROM emp WHERE sal>2000 AND sal<5000;
SELECT * FROM emp WHERE sal>=2000 AND sal<=5000;

BETWEEN ... AND ...

두 값 사이(지정한 값 포함)

SELECT * FROM emp WHERE sal BETWEEN 1000 AND 1500;
SELECT * FROM emp WHERE sal NOT BETWEEN 1000 AND 1500;
SELECT * FROM emp WHERE ename BETWEEN 'KING' AND 'SMITH';
SELECT * FROM emp WHERE hiredate BETWEEN '80/12/17' AND '81/09/28';

IN

값 목록 중의 값과 일치

SELECT * FROM emp WHERE sal IN (1300,2450,3000);
SELECT * FROM emp WHERE sal NOT IN (1300,2450,3000);

SELECT * FROM emp WHERE ename IN ('ALLEN','FORD');

LIKE 연산자

패턴을 사용해서 일치하는 정보를 검색
%는 0개 이상의 문자를 나타냄
_는 한 문자를 나타냄

SELECT * FROM emp WHERE ename LIKE '%S%';
SELECT * FROM emp WHERE ename NOT LIKE '%S%';

22로 끝나는 입사일

SELECT * FROM emp WHERE hiredate LIKE '%22';

FOR 다음에 꼭 한 글자

SELECT * FROM emp WHERE ename LIKE 'FOR_';

NULL 조건 사용

SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE comm IS NOT NULL;

논리연산자(AND,OR,NOT)

  • AND 연산자의 사용
SELECT empno,ename,job,sal FROM emp 
WHERE sal>=2000 AND job LIKE '%MAN%';
  • OR 연산자의 사용
SELECT empno,ename,job,sal FROM emp
WHERE sal>=2000 OR job LIKE '%MAN%';
  • NOT 연산자의 사용
SELECT ename,job FROM emp WHERE job NOT IN ('CLERK','SALESMAN');

ORDER BY

정렬

  • 오름차순
SELECT * FROM emp ORDER BY sal ASC;
SELECT * FROM emp ORDER BY sal;
  • 내림차순
SELECT * FROM emp ORDER BY sal DESC;
  • 정렬할 때 기준이 되는 컬럼에 중복값이 있으면 2차 정렬 가능
SELECT * FROM emp ORDER BY sal DESC, ename DESC;
  • 열 ALIAS를 기준으로 정렬
SELECT empno,ename,sal*12 annsal FROM emp ORDER BY annsal;
  • 열의 숫자 위치를 사용하여 정렬
SELECT ename,job,deptno,hiredate FROM emp ORDER BY 3;

NULL 정렬

NULLS FIRST 또는 NULLS LAST 키워드를 사용하여 반환된 행 중
NULL값을 포함하는 행이 정렬 순서상 맨 처음에 나타나거나 마지막에 나타나도록 지정

SELECT * FROM emp ORDER BY comm ASC NULLS FIRST;
SELECT * FROM emp ORDER BY comm DESC NULLS LAST;

함수

문자 함수

대소문자 조작 함수

SELECT LOWER('HELLO') FROM dual;
SELECT UPPER('hello') FROM dual;
SELECT INITCAP('hello wORLD') FROM dual;



SELECT INITCAP(ename) FROM emp;

문자 조작 함수

CONCAT(문자열1,문자열2)

문자열1과 문자열2를 연결하여 하나의 문자열로 반환

SELECT CONCAT('Hello','World') FROM dual;
SELECT CONCAT(ename,job) FROM emp;

SUBSTR

(대상문자열,인덱스)
대상문자열에서 지정한 인덱스부터 문자열을 추출
주의 인덱스1부터 시작

SELECT SUBSTR('Hello World',3) FROM dual;
SELECT SUBSTR('Hello World',3,3) FROM dual; --대상문자열,인덱스,개수
SELECT SUBSTR('Hello World',-3) FROM dual; --뒤에서 3번째부터 끝까지 추출
SELECT SUBSTR('Hello World',-3,2) FROM dual; --뒤에서 3번째부터 2개만 추출

LENGTH

문자열의 개수

SELECT LENGTH('Hello World') FROM dual;
SELECT ename, LENGTH(ename) FROM emp;

INSTR

SELECT INSTR('Hello World','e') FROM dual;
SELECT INSTR('Hello World','E') FROM dual;--검색문자가 없을 경우 0 반환
SELECT INSTR('Hello World','o') FROM dual;
SELECT INSTR('Hello World','o',6) FROM dual;--(대상문자열,검색문자,검색인덱스:해당위치부터 검색)
SELECT INSTR('Hello World','o',1,2) FROM dual;--(대상문자열,검색문자,검색인덱스,반복횟수)

LPAD(대상문자열,총길이,문자)

지정한 길이에 문자열을 출력하는데 공백은 왼쪽에 지정한 문자로 채움

SELECT LPAD('Hello',10,'*') FROM dual;

RPAD(대상문자열,총길이,문자)

지정한 길이에 문자열을 출력하는데 공백은 오른쪽에 지정한 문자로 채움

SELECT RPAD('Hello',10,'*') FROM dual;

TRIM

문자열에서 공백이나 특정 문자를 제거한 다음 값을 반환
방향 -> leading(왼쪽), trailing(오른쪽), both(양쪽,default)

SELECT TRIM(LEADING 'h' FROM 'habchh') FROM dual;
SELECT TRIM(BOTH 'h' FROM 'habchh') FROM dual;

LTRIM(대상문자열,제거할 문자)

문자열의 왼쪽에서 공백이나 특정 문자를 제거

SELECT LTRIM('habchh','h') FROM dual;

RTRIM(대상문자열,제거할 문자)

문자열의 오른쪽에서 공백이나 특정 문자를 제거

SELECT RTRIM('habchh','h') FROM dual;

REPLACE(대상문자열,OLD,NEW)

대상문자열에서 OLD문자를 NEW문자로 대체

SELECT REPLACE('010.1234.5678','.','-') FROM dual;

함수 중첩

SELECT ename, LOWER(SUBSTR(ename,1,3)) FROM emp;


숫자 함수


CEIL(실수)

올림 처리한 정수값을 반환

SELECT CEIL(1.4) FROM dual;

FLOOR(실수)

버림 처리한 정수값을 반환

SELECT FLOOR(1.7) FROM dual;

ROUND(대상숫자,지정자릿수)

반올림

SELECT ROUND(45.926,2) FROM dual;
SELECT ROUND(45.926) FROM dual;

SELECT empno,ename,sal,ROUND(sal*1.15) "New Salary" FROM emp;

TRUNC(대상숫자,지정자릿수)

버림

SELECT TRUNC(45.926,2) FROM dual;
SELECT TRUNC(45.926) FROM dual;

MOD(대상숫자,나눌숫자)

나머지값

SELECT MOD(17,2) FROM dual;

날짜함수

SYSDATE(ORACLE 서버의 현재 날짜와 시간을 반환)

SELECT SYSDATE FROM dual;

날짜에 산술 연산자 사용

SELECT ename, TRUNC((SYSDATE - hiredate)/7) AS WEEKS FROM emp;

MONTHS_BETWEEN

두 날짜 간의 월 수 

SELECT MONTHS_BETWEEN('2012-03-23','2010-01-23') FROM dual;


SELECT ename,hiredate,
       TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) months_worked
FROM emp ORDER BY months_worked;

ADD_MONTHS

특정 날짜의 월에 정수를 더한 다음 해당 날짜를 반환

SELECT ADD_MONTHS('2022-01-01',8) FROM dual;

NEXT_DAY

지정된 요일의 다음 날짜

SELECT NEXT_DAY('2022-05-05','월요일') FROM dual;

LAST_DAY

월의 마지막 날

SELECT LAST_DAY('2022-05-13') FROM dual;

변환함수

TO_CHAR : 숫자 -> 문자, 날짜 -> 문자
TO_NUMBER : 문자 -> 숫자
TO_DATE : 문자 -> 날짜

  • TO_CHAR
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM dual;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM dual;
  • 실제 자리수와 일치
SELECT TO_CHAR(1234,9999) FROM dual;
SELECT TO_CHAR(1234,'9999') FROM dual;
SELECT TO_CHAR(1234,'0000') FROM dual;
  • ##으로 출력, 오류 발생
SELECT TO_CHAR(1234,0000) FROM dual;
  • 자리수가 모자람 -> ####
SELECT TO_CHAR(1234,999) FROM dual;
SELECT TO_CHAR(1234,'999') FROM dual;
SELECT TO_CHAR(1234,'000') FROM dual;
  • 실제 자리수 보다 많은 자리수 지정
SELECT TO_CHAR(1234,99999) FROM dual; --   1234
SELECT TO_CHAR(1234,'99999') FROM dual;--  1234
SELECT TO_CHAR(1234,'00000') FROM dual;-- 01234
  • 소수점 자리
SELECT TO_CHAR(1234,9999.99) FROM dual;
SELECT TO_CHAR(1234,'9999.99') FROM dual;
SELECT TO_CHAR(1234,'0000.00') FROM dual;
  • 반올림해서 소수점 둘째자리까지 표시
SELECT TO_CHAR(25.897,'99.99') FROM dual;
인상된 급여를 소수점 첫째자리까지 표시
SELECT TO_CHAR(sal*1.15,'9,999.9') FROM emp;
SELECT TO_CHAR(sal*1.15,'9,999.0') FROM emp;

- 통화표시

SELECT TO_CHAR(1234,'$0000') FROM dual;
지역통화표시
SELECT TO_CHAR(1234,'L0000') FROM dual;

SELECT ename,TO_CHAR(sal,'$9,999') FROM emp;

TO_DATE

SELECT TO_DATE('22-05-13','YYYY-MM-DD')FROM dual;
포맷 형식 생략 가능
SELECT TO_DATE('22/05/13') FROM dual;

TO_NUMBER

SELECT TO_NUMBER('100','999') FROM dual;
SELECT TO_NUMBER('100','000') FROM dual;
포맷형식 생략 가능
SELECT TO_NUMBER('200') FROM dual;

일반함수

NVL(value1,value2)

value1이 null이면 value2를 쓴다.
value1과 value2의 자료형이 일치

SELECT ename,sal,comm, NVL(comm,0)+100 FROM emp;  
                           숫자 -> 문자
SELECT ename,comm,NVL(TO_CHAR(comm),'No Commission') FROM emp;

NVL2(value1,value2,value3)

value1 null이면 value3, null이 아니면
value2 사용, 자료형이 일치하지 않아도 됨

SELECT NVL2(comm,'Commission','No commission') FROM emp;

NULLIF(value1,value2)

두개의 값이 일치하면 null, 두개의 값이 일치하지
않으면 value1 사용

SELECT NULLIF(LENGTH(ename),LENGTH(job)) FROM emp;

COALESCE(value1,value2,value3,....)

null값이 아닌 값을 사용(자료형 일치)

SELECT comm,mgr,sal, COALESCE(comm,mgr,sal) FROM emp;

표준 SQL

CASE 컬럼 WHEN 비교값 THEN 결과값
         WHEN        THEN
         WHEN        THEN
         (ELSE 결과값)
END

SELECT ename,sal,job,
       CASE job WHEN 'SALESMAN' THEN sal*0.1
                WHEN 'MANAGER' THEN sal*0.2
                WHEN 'ANALYST' THEN sal*0.3
                ELSE sal*0.4
       END "Bonus"
FROM emp;

select * from tab;
profile
SOSO
한다 열심히
이전 포스트

[19일차] Oracle - Database, SQL

다음 포스트

[21일차] SQL - 집계함수, 그룹함수, 분석함수, JOIN

0개의 댓글