TIL 1 | Oracle SQL

yoozung·2021년 6월 11일
0

[ TIL ]

목록 보기
1/10
post-thumbnail

DB의 인덱스 번호는 1번부터 시작됨.
desc 테이블명 : 테이블구조 보는 법

오라클데이터타입

  • 숫자
    정수 number, number(길이)
    실수 number(전체길이, 소수이하길이)
     
  • 문자열 '문자열'
    고정문자열 char(길이)
    가변문자열 varchar2(길이)
     
  • 날짜
    문자열 : 날짜형식이 고정되어있는 경우 (입사일이런거), 날짜연산수행하지 않는 경우
    날짜타입 : date라는 타입 // 블로그에 글적으면 오늘쓴건 몇시에 썻다고 뜨고 어제쓴건 어제 날짜만 뜨는거
    => 날짜형식이 가변적인 경우, 날짜연산수행할경우
    => 길이지정하지 않음
    => 날짜, 시간 정보 들어감

데이터표현

  • 문자, 날짜 : 문자열은 반드시 ''로 감싸고 날짜는 '2021-06-10'로 싸
  • 숫자 : 1234, 123.45
  • 별명 : 테이블, 컬럼에 대한 별명지정 "별명"

DQL : 조회

1. 필수형식

SELECT * 컬럼명1, 컬럼명x | 컬럼명1, "별명", 컬럼명x "별명" ---> 조회하는 방법
select 수식(예를들면 3*5) | 함수명() | (SUB-QUERY) 서브쿼리는 쿼리안에 쿼리 from 테이블명;

  • 컬럼에 대한 별명 규칙 :
    "" 으로 꼭 감싸야됨. 아래규칙들은 ""안에 넣는 것들
    대소문자 구분
    공백 가능
    특수문자
    // 별명생략가능(대문자만, 공백없고, 특수문자 없는 경우에는 ""생략가능)

  • DUAL 테이블
    : 오라클에 셀렉트 필수형식을 위한 DUMMY테이블
    주로 연산식을 테스트하거나 함수테스트할 때 가끔 사용

2. 전체형식

SELECT~~
FROM 테이블명1 [, 테이블명X, (SUB-QUERY)] 이런식으로 뒤에 더 올수잇음
WHERE 조건식1 [AND | OR | NOT] 조건식X
GROUP BY 그룹핑대상컬럼명 [, 그룹핑대상컬럼명X]
HAVING 그룹핑결과에대한조건식 // 해빙은 그룹바이없으면 못 씀
ORDER BY 정렬컬럼명 정렬방법, 정렬컬럼명X 정렬방법

order by에 올 수 있는거

  • 정렬항목 : 컬럼명, SELECT 항목에 대한 인덱스번호, 별명, 수식
  • 정렬방법 : 올림차순(ASC, 기본이 올림차순 그래서 ASC생략해도됨), 내림차순(DESC)
  • 정렬기준 :
    숫자, 날짜 (작은것에서 큰 순) (01234....1990, 2000, 2001..)
    영문 : ABC...
    한글 : 가나다...


정렬

  • select ~ from ~ order by
  • order by 컬럼명 | 별명 | 인덱스번호 | 함수호출() 정렬 방법 [asc | desc]

문제 : 직원의 사번, 이름 ,급여, 정보를 제목으로 조회

  • 답:
    select empno "사번", ename "이름", sal "급여" from emp;

    문제 : 직원의 사번 이름 급여 정보를 제목으로 조회 => 급여가 높은 순서대로 정렬조회

  • 답 :
    select empno "사번", ename"이름", sal "급여" from emp order by 급여 desc; order by sal desc; order by 3 desc; // 3은 인덱스번호.



연산자

  • 산술연산자 : + - * /, mod()
  • 비교연산자 : = (조건식 비교할때는 같다의 의미, 레코드에서는 값을 할당할 때 사용), !=(다르다), <>(다르다), <, >, <=, >=
  • 논리연산자 : and, or, not

SQL전용연산자

  • BETWEEN A AND B : A는 시작값, B는 종료값 (A에서 B까지)

  • null 여부 체킹 : is null / is not null

  • like 부분 매칭 조회 : %(모든 문자 대체), _ (한문자 대체)
    -- like '%java', like '%java%', like 'java%' 이런식으로 검색이 가능하다.
    -- like '_java'
    -- io가 많이 발생되기 때문에 꼭 필요할 때만 사용해야 함

  • in / not in : 존재 여부 체킹

  • exist / not exist



조건식

  • 값 | 컬럼명 연산자 비교값

  • 결과는 왼쪽을 기준으로 true/false

  • true일 경우 조회, false일 경우 조회x


조건 검색

select ~
from ~
where 조건식1 [and | or | not] 조건식2 인덱스번호 사용 불가
order by 인덱스번호 사용가능

직원의 모든 정보 조회 _ 급여가 2000미만인 직원들 낮은 순서대로 정렬
select * from emp where sal < 2000 order by sal asc;
// 기본정렬이 asc라서 생략가능.

직원의 모든 정보 조회 _ 급여가 2000 ~ 3000 사이인 직원들 낮은 순서대로 정렬

논리연산자:
select * from emp where sal >= 2000 and sal <= 3000 order by sal;

SQL전용연산자:
select * from emp where sal between 2000 and 3000 order by sal;

직원의 사번, 급여, 수당 조회
select empno "사번", sal "급여", comm "수당" from emp;

수당이 null인 직원의 정보 조회
select * from emp comm where comm is null;

수당이 null이 아닌 직원
select * from emp comm where comm is not null;

수당을 받지 않는 직원의 정보 조회 (수당이 null값이거나 0값인 직원들)
select * from emp where comm is null or comm = 0;

수당을 실질적으로 받은 직원의 정보 조회
select * from emp where comm >0;

미션

  • 직원들에게 특별 수당을 지급하기로 함.
    특별수당 = 급여 + 수당한 값에 30% 지급
    ---출력양식---
    사번, 이름, 급여, 수당, 특별수당 조회
    정렬: 특별수당 많은 순

답:
select empno 사번, ename 이름, sal 급여, nvl(comm, 0) 수당, (nvl(comm, 0) + sal) * 0.3 특별수당
from emp
order by 특별수당 desc ;


null

  • 어떠한 데이터도 존재하지 않음
  • 연산을 수행할 수 없음
  • null인 경우에 지정한 값으로 대체하는 함수 :
    NVL(arg1, arg2)
    NVL2(arg1, arg2, arg3)
    => arg1이 0이 아니면 arg2주고 0이면 arg3줘
  • null컬럼 정렬 조회
    -- 올림차순: 작은 거 => 큰 거 => null
    -- 내림차순: null => 큰 거 => 작은 거

직원정보 조회: 수당이 많은 사람 순서대로 정렬 조회
select * from emp order by comm desc;

직원정보 조회: 수당이 적은 사람 순서대로 정렬 조회
select * from emp order by comm asc;

  • null 데이터가 어느 위치에 출력되는지 체크해보기


like

  • like 부분 매칭 조회
    -- like ('검색대상')
    -- % : 모든 문자 대체
    -- _ (밑줄) : 1문자 대체

이름에 A가 들어간 직원 조회
select ename from emp where ename like ('%A%');

이름이 J로 시작하는 직원 조회
select ename from emp where ename like ('J%');

이름이 R로 끝나는 직원 조회
select ename from emp where ename like ('%R');

이름에 두 번째 문자가 L인 직원 조회
select ename from emp where ename like ('_L%');

이름의 길이가 4자리인 직원 조회
select ename from emp where ename like ('____');


길이 관련 함수

  • length() : 문자 길이 반환

  • lengthb() : 문자 byte 단위 길이 반환
    (테이블 설계시 도메인데이터 분석해서 컬럼에 길이를 지정할 때 주로 사용)

  • 한글 1글자가 3byte의 크기로 기본 설정돼 있음(oracle 11g)
    // 보통은 2바이트

함수를 사용해서 길이가 4자리인 직원 조회
select ename from emp where length(ename) = 4;

'가'의 길이를 조회
'A1'의 길이를 조회
select length('가'), length('a1'), lengthb('가'), lengthb('a1') from dual;


in / not in

  • in (value, value2, valuex)
  • or 연산자와 같은 개념

10, 20번 부서원의 정보 조회

in 사용 :
select * from emp where deptno in (10, 20) order by deptno;
or 사용 :
select * from emp where deptno=10 or deptno=20 order by deptno;

30번이 아닌 부서원의 정보 조회
not in 사용 : select * from emp where deptno not in (30) order by deptno;
select * from emp where not deptno = 30 order by deptno;

10, 20번 부서원이 아닌 직원의 정보 조회
or 사용 :
select * from emp where not (deptno=10 or deptno=20) order by deptno;


함수(function) 구분1

  1. ANSI 표준 함수
  2. DBMS 전용(종속) 함수 (특정 DB전용 함수) : ORACLE 전용, My SQL 전용
  3. 사용자 정의 함수, 프로시저 : PL/SQL (사용자 필요에 의해 함수나 프로시저를 만드는거. 결과값이 리턴되지 않는 것이 프로시저)

함수(function) 구분2

  1. 단일행 함수
    => 1 : 1 결과 반환
    => NVL(), length(), lengthb()
  2. 복수행 함수 (그룹함수) (평균을 구하는 것처럼 여러행을 쓰는거)
    => N개를 이용한 연산수행 => 결과 반환

함수(function) 구분3

  • 문자 함수
  • 숫자 함수
  • 날짜 함수
  • 변환 함수
  • 통계 함수
  • ...


문자 함수

  • 길이 : length() /
    lengthb() : byte단위로 읽음, 테이블설계시 컬럼길이, 영문자, 숫자(1byte),
    한글(2byte, 3byte)
  • 공백제거 : trim()(양쪽공백제거), ltrim()(왼쪽공백제거), rtim()(오른쪽공백제거)
  • 연결 연산자 : concat()
  • 연결 (결합) 연산자 : 'a' || 'b'
    // 자바에서 ||는 or, sql에서는 연결연산자

직원의 이름, 직무, 정보를 조회
출력형식 : 000 사원님의 직무는 000 입니다.

  1. concat()
    select concat((concat(ename, ' 사원님의 직무는 ')), (concat(job, '입니다.'))) from emp;

  2. ||
    select ename || ' 사원님의 직무는 ' || job || '입니다' from emp;

  • lpad(), rpad()
    아규먼트 : (데이터, 길이, '대체문자')

이름의 전체길이를 15자리로 하고 빈자리는 * 로 대체
왼쪽대체
select lpad(ename, 15, '*') from emp;
오른쪽 대체
select rpad(ename, 15, '*') from emp;

  • 부분문자열 추출 ; substr(문자열, 시작위치, 길이)

직원의 이름 2자리만 보여주고 남은 문자는 * 로 대체처리 조회
select rpad(substr(ename, 1, 2), length(ename), '*') "이름" from emp;


숫자 함수

  • round("값", "자리수") : 반올림, 위치 지정 가능
  • trunc("값", "옵션") : 버림, 위치 지정 가능
  • ceil() : 소수이하 올림처리, 위치 지정 불가
  • floor() : 소수이하 버림처리, 위치 지정 불가
  • mod() : 나머지
    => mod(a, b) : a를 b로 나눴을 때 나머지를 반환한다

1234.45678
1. 소수이하 올림처리
select ceil(1234.45678) from dual;

  1. 100이하 버림처리
    select trunc(1234.45678, -2) from dual;
  1. 소수이하 2자리 버림 처리
    select trunc(1234.45678, 2) from dual;
  1. 소수이하 1자리 올림 처리
    select round(1234.45678, 1) from dual; // 틀림
    select round(trunc(1234.45678, 3), 1) from dual;
    select ceil(1234.4567 * 10) / 10 from dual;
  1. 숫자 5를 2로 나눈 나머지
    select mod(5, 2) from dual;


날짜함수

  • 날짜함수를 이용할 때는 date type을 이용해야 함

  • 현재날짜/시간
    : sysdate (함수가 아니라 패키지 또는 키워드라고 봐도 됨)(기본형식 : 21/06/11

  • last_day(date)
    21/06/11
    해당 date의 마지막 날짜 (매월말일)
    -- select last_day(sysdate) from dual;

  • next_day(sysdate, arg) : 아규먼트는 1,2,3 같은 숫자 줄 수 있음.
    요일수 / 지정한 다음요일에 해당하는 날짜 .. 일요일1 월요일2 화요일 ..

  • add_months(sysdate, arg) : 개월 수 의미, arg에 6 넣으면 6개월 후.

  • months_between(startDate, endDate) : 경과 개월수, 그래서 앞에 큰 값 넣음
    select months_between(sysdate, '2000/01/01') from dual;

내가 살아온 개월수를 조회

  • 버림처리
    select trunc(months_between(sysdate, '1999/01/23')) || '개월' from dual;
    날짜형식 '99/01/23'/ '99.01.23' 가능
  • 반올림처리
    select round(months_between(sysdate, '1999/01/23')) from dual;
  • 올림처리
    select ceil(months_between(sysdate, '1999/01/23')) from dual;
  • 산술연산
    -- date + 14
    -- date - 14
    select sysdate + 14, sysdate - 14 from dual;

경과 일수 계산 : sysdate - '21/05/17'
select sysdate - '21/05/17' from dual;
=>날짜를 문자로 인지해서 에러남

날짜로 변환하면 됨.
select sysdate - to_date('21/05/17', 'yy/mm/dd') from dual;


변환함수

  • 숫자 / 날짜 => 문자열 변환
    => to_char(숫자, 'pattern'), to_char(date, 'pattern')
    => 123,456.78, $123, 천단위 컴마표기, 소수이하 자리수지정, 화폐통화기호 표기
    => 날짜를 원하는 형식으로 지정

  • 문자열 => 날짜타입 변환
    => to_date('날자형식문자열', 'pattern')

  • 문자열형식의 숫자 => 숫자 변환
    // 자동형변환 해주기 때문에 거의 쓸 일 없음.
    => to_number()


날짜, 숫자 형식(format, pattern)

  • 요일 : 일요일 1, 월요일 2 ....

  • 년도 : yy or yyyy

  • 월 : mm

  • 일 : dd (대소문자 상관없음)

  • 시간 : hh

  • 분 : mi

  • 초 : ss

  • 숫자 :
    => 999,999.99 => 1234.4567 => 1,234.45
    => 099,999.99 => 1234.4567 => 001,234.45
    => 09999 => 123 => 00123

  • 통화기호
    => $999,999 => 1234 => $1,234
    => $999 => 1234 => #### (제대로 안나옴)

    디폴트로케일 쓰고 싶어요 (통화단위를 고정해두고싶다)
    => L999,999 => 기본 설정 로케일의 화폐기호


오늘의 미션

직원 정보 조회
-조회항목 : 사번, 급여, 수당, 입사일(hiredate)
-급여, 수당은 천단위 마다 컴마 표기
-통화 단위는 기본로케일(한국)
-입사일 : 년도 4자리-월2자리-일2자리 형식으로 조회
-최근 입사자 순서대로 정렬 조회

select empno "사번", to_char(sal, 'L9,999') "급여", to_char(comm, 'L9,999') "수당", to_char(hiredate, 'yyyy-mm-dd') "입사일" from emp order by 입사일 desc;

+추가로 null 값인 부분을 0값으로 대체해주면
select empno "사번", to_char((nvl(sal, 0)), 'L9,999') "급여", to_char((nvl(comm, 0)), 'L9,999') "수당", to_char(hiredate, 'yyyy-mm-dd') "입사일" from emp order by 입사일 desc;

0개의 댓글