TIL 1 | Oracle SQL

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;