Oracle SQL문 + 기타등등 총정리

 

 

데이터 타입 글자별 크기 

한글 2byte 정도

영어, 공백, 특문, 숫자 1byte

 

 

 

컬럼타입

구분	컬럼 타입	설명
숫자	NUMBER
	NUMBER(n)
	NUMBER(n, m)	n: 실수의 전체 자릿수  m: 소수점 자리수 ex. NUMBER(8, 2)  : 정수 6자리와 소수2자리
문자	VARCHAR2(n)	가변길이 문자 데이터 컬럼타입 저장되는 데이터 크기에 맞춰 알맞은 공간 사용 최대크기 4000Byte (한글 2000자 정도, 영어 4000자)
	CHAR(n)	고정길이 문자 데이터 컬럼타입 성능면에선 가변보다 좋지만 해당 컬럼의 성격에 따라 공간이 낭비됨
	VARCHAR2(n CHAR)	byte가 아닌 글자수로 제한 가능 ex.  VARCHAR2(10 CHAR)
날짜	DATE	날짜 및 시간 데이터 컬럼타입 자바로 날짜 데이터 처리 후 DB로 집어넣을 때   java.sql.Date로 변환해서 넣어야 함.  ( .toLocalDate(로컬데이트) 메서드로  자바 날짜 데이터를 오라클 Date형식에 맞게 바꿀 수 있다. )

 

 

 

 

java.util.Date 을 java.sql.Date 로 변환하는 법

 

java.sql.Date 에서 LocalDate로 : .toLocalDate() 메서드 이용

 

java.sql.Date 클래스 문서 참고

https://docs.oracle.com/javase/8/docs/api/java/sql/Date.html#valueOf-java.time.LocalDate-

LocalDate localDate = sqlDate.toLocalDate();

 

+) 걍 로컬데이트, 로컬타임, 로컬타임데이트 상기용

 

LocalDate today = LocalDate.now();

System.out.println(today);  >> 2022-08-22

 

LocalTime time = LocalTime.now();

System.out.println(time); >> 10:41:22.105

 

LocalDateTime dateTime = LocalDateTime.now(); 

System.out.println(dateTime); >> 2022-08-22T10:43:55.603

 

 

LocalDate에서 java.sql.Date로 : java.sql.Date.valueOf()메서드 이용

java.sql.Date sqlDate = java.sql.Date.valueOf( todayLocalDate );

 

 

 

 

 

DESC 

DESC 테이블명

해당 테이블의 컬럼정보를 자세히 볼 수 있음 (타입, 글자수 ...) 

오라클 명령어라 끝에 세미콜론 안 붙여도 됨. 

(SQL문은 끝에 세미콜론 빠지면 안 됨)

 

SELECT 

SELECT 컬럼명1 [, 컬럼명2, 컬럼명3, .... ] FROM 테이블명;

컬럼명에 * 넣으면 모든 컬럼 조회

 

WHERE절 

비교연산자 이용해서 조건 지정

=   : 같음

!=, <>, ^=   : 같지 않음

<, >, <=, >=   : 비교

 

컬럼명 BETWEEN A AND B 로도 비교 가능

 

AND와 OR로 WHERE절에 조건 추가 가능

SELECT * FROM employees WHERE salary >= 2000 AND salary <= 3000;
--근데 이건 컬럼명 BETWEEN A AND B 로 더 간단히 풀 수 있지
SELECT * FROM employees WHERE salary BETWEEN 2000 AND 3000; 

SELECT * FROM employees WHERE department_id = 30 OR department_id = 60;

문자타입으로도 크기 비교가 가능함

SELECT first_name FROM employees WHERE first_name > 'P'

날짜, 시간 타입도 쉽게 비교할 수 있다.

SELECT * FROM employees WHERE hire_date > '2008/01/01'; 
--2008년에 들어온 사람 조회 (08만 적으면 1908인지 2008인지 모를 수도 있으니까 2008다 적든가)

 

LIKE와 와일드카드 ( %, _ ) 로 조회

데이터의 일부분으로 원하는 내용 검색 가능 

문자, 날짜, 시간 타입에 사용 가능

% : 길이 제한 없이 아무 문자나 와도 상관 없는 자리 (0개도 됨)

_ :  하나의 문자가 반드시 와야 하는 자리 

Select * from employees where hire_date LIKE '%/08/%'; --08월의 모든 사람이 걸림 
Select * from employees where hire_date LIKE '__/08/__'; -- __두개 해줘야됨 _하나 쓰면 안 나옴
Select * from employees where hire_date LIKE '08/%'; -- 08년의 모든 사람이 걸림
Select * from employees where hire_date LIKE '%/08'; -- 08일의 모든 사람이 걸림

 

||' '|| :  양 옆으 ㅣ두 문자열을 이어 붙이겠단 뜻 

select first_name||' '||last_name as "full Name" from employees where first_name LIKE '%a__';

 

lower(), upper() 

전달한 문자 값을 소문자, 대문자로 변환 

select * from employees where lower(first_name) LIKE '%e%e%';
select * from employees where upper(first_name) LIKE '%E%E%';

 

nulll 은 크기비교가 불가능하다. 

고로 어떤 비교 연산자도 사용할 수 없음!

= 도 안 되는 거! 그러면 null인 행은 어떻게 찾아?

 

null인 행을 조회하고 싶을 때는 IS NULL을 사용. 

반대로 null이 아닌 행은 IS NOT NULL

SELECT * FROM employees WHERE commission_pct = null; -- 비문
SELECT * FROM employees WHERE commission_pct IS NULL; -- 이렇게 해야 됨!
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

 

mod()

오라클에서는 % 말고 mod 함수 를 사용한다. 

SELECT * FROM employees WHERE mod(employee_id, 2) = 0; 
--사번이 짝수인 행 조회

 

 

 

 

집합

 

INTERSECT 교집합

교집합 SELECT문이 너무 길고 복잡해서 AND로 합치기 어려울 때 INTERSECT를 이용

SELECT first_name, salary FROM employees WHERE first_name LIKE 'J%n'
INTERSECT
SELECT first_name, salary FROM employee WHERE salary BETWEEN 3000 AND 5000;

 

UNION 합집합

SELECT employee_id, first_name, salary FROM employees WHERE first_name LIKE 'J%n'
UNION 
SELECT employee_id, first_name, salary FROM employees WHERE salary BETWEEN 3000 AND 3500;

 

UNION ALL 합집합 (중복제거 x)

SELECT employee_id, first_id, salary FROM employees WHERE first_name LIKE 'J%n'
UNION ALl
SELECT employee_id, firsT_name, salary FROM employees WHERE salary BETWEEN 3000 AND 3500;

 

MINUS 차집합

SELECT department_id FROM employees WHERE department_id IN(10, 20, 30, 40)
MINUS 
SELECT department_id FROM employees WHERE department_id = 30;

 

 

DECODE()

switch-case와 유사한 문법

SELECT 
	first_name, 
        job_id,
        DECODE(
        	10, 'Administration', 
            20, 'Marcketing', 
            30, 'Purchasing',
            40, 'Human Resources',
            'Not yet implementd' -- 마지막은 default
        ) "DEPARTMENT_NAME" -- "큰 따옴표"로 감싸주면 AS 생략 가능
FROM 
        	employees;

DECODE는 LIKE와 함께 쓸 수 없다. 그래서 일일이 알아서 추가해줘야 함

SELECT
    first_name
    || ' '
    || last_name "NAME",
    job_id,
    salary AS before_salary,
    decode(job_id, 
            'IT_PROG', salary * 1.1,
            'FI_ACCOUNT', salary * 1.05,
            'PU_CLERK', salary * 1.03, 
            'SH_CLERK', salary * 1.03,
            'ST_CLERK', salary * 1.03,
        salary
        ) AS after_salary
FROM
    employees;

 

 

DECODE 안에 LIKE를 쓸 수 없는 단점은 UNION 으로 극복할 수 있음

예시.

%CLERK들만 3% 인상해주고 싶을 때,

JOB_ID LIKE '%CLERK'의 salary를 연송해주는 SELECT문을 UNION해주면 됨 

SELECT
    first_name|| ' '|| last_name "NAME",
    job_id,
    salary AS before_salary,
    decode(job_id, 
            'IT_PROG', salary * 1.1,
            'FI_ACCOUNT', salary * 1.05,
        	salary
        ) AS after_salary
FROM
    employees
WHERE 
    JOB_ID NOT LIKE '%CLERK'
UNION 
SELECT
    first_name
    || ' '
    || last_name "NAME",
    job_id,
    salary AS before_salary,
    salary * 1.03 AS after_salary
FROM
    employees
WHERE 
    JOB_ID LIKE '%CLERK';

 

 

CASE

CASE WHEN 컬럼명 = 값 THEN 표시할 값 ELSE 디폴트값

DECODE는 해당 컬럼의 값과 정확히 일치하는 값만 사용할 수 있지만 CASE는 조건을 사용할 수 있다.

 

SElECT first_name ||''||last_name AS "FULL NAME", 
		job_id, 
        salary AS before_salary, 
        CASE
        	WHEN job_id = 'iT_PROG' THEN salary * 1.1
            WHEN job_id = 'FI_ACCOUNT' THEN salary * 105, 
            WHEN job_id LIKE '%CLERK%' THEN salary * 1.03
            ELSE salary
            END As after_salary
FROM 
	employees;