Oracle SQL - 구문기초, SELECT, 데이터필터링

DB 컬럼 Data Type

• 문자형

Data Type	설명
CHAR	고정길이, 최대 2000byte, 나머지 공간이 공백으로 채워짐
VARCHAR	가변길이, 최대 4000byte, 공백으로 채우지 않음.
NVARCHAR	유니코드 가변길이 4000byte
CLOB	대용량 TEXT, 최대4G
BLOB	파일/이미지/동영상, 4G

• 숫자형

Data Type	설명
NUMBER(p,s)	10진수 가변숫자, 22byte, p(최대숫자),s(소수점이하 자리수)

• 날짜

Data Type	설명
DATE	고정길이, 7byte, 초까지 표현가능 // 현재날짜: sysdate
TIMESTAMP	밀리초까지 표현, // 현재날짜: systimestamp

SQL 구문 기초

• 테이블 생성

CREATE TABLE 테이블명(
	컬럼명1   CHAR(20) // char 자료형 20byte
	…
);

1. SELECT : 테이블 참조

SELECT  컬럼명
FROM   테이블명;

1. INSERT INTO : 추가

//1. 전체 컬럼 입력 시
INSERT INTO 테이블명
VALUSE        (컬럼1값, 컬럼2값, … ); // 모든 컬럼
//2. 일부 컬럼
INSERT INTO 테이블명(컬럼1, 컬럼2)
VALUSE  (컬럼1값, 컬럼2값);

1. UPDATE : 수정

UPDATE 테이블명
SET       컬럼1=수정값, ...
WHERE   조건 [ AND 조건… ]

1. DELETE 삭제

DELETE FROM 테이블명
WHERE   조건 [ AND 조건… ]

데이터 참조 : SELECT

열 데이터 가져오기

// 하나의 열 가져오기
SELECT prod_name
FROM Products;
// 여러 열 가져오기
SELECT prod_id, prod_name, prod_price
FROM Products;
// 모든 열 가져오기
SELECT *
FROM Products;

데이터 정렬

SELECT 컬럼명
FROM 테이블명
ORDER BY 컬럼명; 

• 정렬 방향 지정
• ASC : 오름차순 (생략시)

• DESC : 내림차순

• 여러열로 정렬가능, 열 위치 기준으로 정렬 가능 EX) ORDER BY prod_id, prod_name ORDER BY 2, 3

데이터 필터링

연산자	설명
=	같음
<>, !=	같지 않음
<,<=,>,>=	부등호 조건
!<, !>	보다 작지(크지)않음
BETWEEN	두 값 사이에있음
IS NULL	NULL값임
IS NOT NULL	NULL값이 아님
LIKE	부분일치, 와일드카드 (ex) prod_id LIKE ‘a%’
AND	연결된 조건을 모두 만족하는 값 필터링
OR	연결된 조건 중 하나를 만족하는 값 필터링
IN	OR과 동일한 기능 수행
NOT	조건에 해당하지 않는 값 필터링
_	단 하나의 문자에 대해 아무 문자나 허용

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01')
// vend_id = 'DLL01' OR vend_id = 'BRS01'  
// OR과 IN은 같은 기능을 수행.

SELECT prod_name, prod_price
FROM Products
WHERE vend_id NOT IN ('DLL01', 'BRS01') // DLL01과 BRS01이 아닌 제품을 가져온다.

WHERE prod_name LIKE '_inch teddy bear';

계산필드 만들기

• 필드 결합 : || 을 통해 연결

// 벤더명(벤더국가) Format
SELECT vend_name || '(' || vend_country || ')'
FROM Vendors

• 별칭사용

// 벤더명(벤더국가) Format의 컬럼이름 : vend_title
SELECT vend_name || '(' || vend_country || ')' as vend_title

• 사칙연산 적용

// 수량 * 가격 컬럼
SELECT quantity * item_price as expanded_price 

+,-,*,/ 적용가능,

[참고] NULL과 더하면 NULL값

데이터 조작함수

1. 텍스트 제어함수

• LENGTH(c) : 문자의 길이 반환

SELECT vend_id, LENGTH(TRIM(vend_name)) FROM vendors;

• TRIM(c) : 공백제거

• LTRIM(c) / RTRIM(c) : 왼쪽/오른쪽 공백 제거

• LOWER(c) / UPPER(c) : 소/대문자로 변경

SELECT vend_id, LOWER(vend_address), UPPER(vend_address) FROM vendors;

• SUBSTR(c,m,[n]) : c문자열의 m부터 n개의 문자열로 자른다. 시작 지점 음수가능.

SELECT vend_id, SUBSTR(vend_address,1,3), SUBSTR(vend_address,4) FROM vendors;	

• REPLACE(c,s1,s2) : c문자열에서 s1을 찾아 s2로 변경.

• COALESCE(exp1,exp2) : NULL이 아닌 첫번째 표현식 반환

SELECT vend_id, vend_state, vend_city, COALESCE(vend_state,vend_city) FROM vendors;

• NULLIF(exp1,exp2) : expr1과 expr2가 같으면 NULL, 다르면 expr1을 반환.

• DECODE(expr,search1,result1, …)

SELECT vend_id, vend_state, DECODE(TRIM(vend_state), 'CA', '1', 'NY', '2', '3') FROM vendors;

• NVL(c,s) : 해당 칼럼값이 NULL일 경우 출력값 지정

SELECT NVL(cust_email, 'test@naver.com') FROM customers;

2. 숫자 제어함수

• ABS(n) : 절대값

• CEIL(n) / FLOOR(n) : 올림, 내림 (정수값으로 만들어준다.)

• ROUND(n, [i]) : i+1번째에서 반올림. Default = 0

• TRUNC(n, [i]) : i+1번째에서 자름. Default = 0

• 일반적으로 소수점 n 번째 까지 추출하는 경우 TRUNC를 사용.

• MOD(n1, n2) : n1을 n2로 나눈 나머지

3. 날짜 제어함수

날짜의 경우, 개발의 편의를 위해 DATE가 아닌 VARCHAR(8)로 생성하여 변환함수를 통해 DATE로 변환한 후 날짜 제어함수를 사용한다.

• MONTHS_BETWEEN(date1, date2) : 두 날짜 사이의 개월 차 수 (큰 값을 앞에 두어야 양수가 나옴)

SELECT MONTHS_BETWEEN(TO_DATE('20200219','YYYYMMDD'),sysdate) FROM dual;

• ADD_MONTHS(date, n) : n개월을 추가함.

SELECT ADD_MONTHS(sysdate, -3) FROM dual;

• SYSDATE : 현재 날짜

// 현재시간 - 1일
SELECT sysdate -1 FROM dual;
// 현재시간 - 1초
SELECT sysdate -1/24/60/60 FROM dual; 

• LAST_DAY(date) : 해당 월의 마지막 일자를 반환

SELECT LAST_DAY(sysdate) FROM dual;

4. 변환 함수

• TO_CHAR(숫자/날짜, [format]) : 문자로 변환, format은 날짜일 때만

SELECT TO_CHAR(sysdate,'YYYY-MM-DD') FROM dual;

• TO_NUMBER(s) : 문자 -> 숫자 변환

TO_NUMBER('123')

• TO_DATE(s) : 문자 -> 날짜 변환

TO_DATE('20200818','YYYYMMDD')