오라클 SQL 입문 (1) 문법규칙, 키워드(예약어), 명령어 정리

오라클 기반으로 데이터베이스 수업을 들으면서 문법규칙, 명령어, 키워드를 정리해보았습니다. 검색은 ctrl + F 로 찾아주세요.

 

 

ORACLE SQL 문법 규칙

 

오라클 SQL은 숫자, 따옴표, 대소문자를 구분합니다.

문장의 마지막은 ; 세미콜론 입력

WHERE에서 표현식 (=리터럴,상수,문자)에 ' ' 빼먹으면 에러

컬럼 별칭 : " ", AS, 한칸 뛰고 이어서

 

산술연산은 그대로 쓰면 됨, 괄호 우선

>=의 순서이며 공백이 들어가면 안됨.

​

함수는 단일행 / 복수행 함수가 있다.

단일행 : 문자, 숫자, 날짜, 변환(묵시적데이터, 명시적데이터 변환), 일반 함수

날짜의 경우 윈도우와 리눅스는 표현 순서가 다르다.

​

 

ORACLE SQL KEYWORD

show user : 접속중인 사용자 조회 (SET sqlprompt "_USER>" 앞쪽에 계속 이름이 나온다.)

DESC : 테이블에 존재하는 컬럼들 조회

SELECT : 열(column) 조회

FROM : 테이블 조회

* : all

WHERE : 행의 조건. 문자는 ' ' 붙여서 조회

COL a FOR 9999 / FOR a8 / LINE 200 / PAGES 60 : 칼럼 길이 조절

& : 값을 입력받으라는 뜻

SET verify OFF : 입력받는 곳에 일부 과정 안보이게 하기.

​

BETWEEN a AND b = a 이상 b 이하. 작은 값을 앞에 씀. 부등호를 권장함

IN (a,b,c) = a or b or c 에 속하는 것. 속도가 빠름

LIKE 특정 패턴의 것. (% : 글자수 제한없음 _ 한글자 표현. 그러나 속도가 느려짐)

IS NULL / IS NOT NULL : 값을 모를 경우. = 연산 사용 불가. 0은 null 아님. null에 어떤값을 더해도 결과는 null​

AND : 조건 동시 만족. and가 or보다 항상 먼저 수행됨, 괄호 사용 가능

OR : 한 가지 이상의 조건 만족

​

DISTINCT : 중복제거. 속도가 느려짐

CONCAT : 연결(합성) 연산자. 문자열 합치기. || 랑 같음. 칼럼끼리 붙여서 출력.

ORDER BY : 정렬. 기본값은 ASC(오름차순). DESC (내림차순). 속도가 느려짐

UNION : 중복값 제거 후 합쳐서 정렬 후 출력. 속도가 느려짐. DECODE나 CASE 추천.

UNION ALL : 중복값 제거 없이 합쳐서 정렬없이 출력

INTERSECT : 교집합 결과 출력, 정렬

MINUS : 차집합 결과 출력, 정렬. 큰 거 작은 거 순.

​​

INITCAP() : 첫번째만 대문자, 나머지 소문자

UPPER() : 다 대문자

LOWER() : 다 소문자

LENGTH() : 문자열 길이값

LENGTHB() : 문자열 길이 바이트값 (영어는 1)

​

SUBSTR(0,a,b) : ~부터 ~까지. 특정 길이의 문자 추출. -를 붙이면 오->왼으로 검색

[ 0이라는 문자의 a번째부터 시작하는 b개까지 ]

SUBSTRB : 문자에서 특정 바이트만 추출

INSTR(0,a,b) : 특정 문자의 위치 조회

INSTRB : 문자에서 특정 위치 바이트 값

​​

LPAD : 문자열에서 왼쪽부터 특정 문자를 채운다 // RPAD : 오른쪽부터

LTRIM : 왼쪽부터 제거 // RTRIM : 오른쪽부터

REPLACE : 특정 문자 대체

 

--[  RPAD 퀴즈  ]
--아래 화면과 같이 emp 테이블에서 deptno가 10번인 사원들의 이름을 총 9자리로 출력하되 
--오른쪽 빈자리에는 해당 자릿수에 해당되는 숫자가 출력되도록 하시오.

SELECT ename, RPAD(ename, 9, SUBSTR('123456789', LENGTHB(ename)+1)) "RPAD"
FROM emp
WHERE deptno=10;

 

* 정규식 함수 : 패턴과 관련

REGEXP_REPLACE (위치, '바꿀 조건', '바꿀 것) : 특정 패턴을 치환

* [[:digit:]] 0-9, [[:alpha:]] : [A-Za-z]

REGEXP_REPLACE(text, '([0-9])', '\1-*') : 숫자를 찾아서 뒤에 -* 추가

REGEXP_REPLACE('aaa bbb','( )', '{2, }', '') : 공백 2칸 이상 제거

​

REGEXP_INSTR : 특정패턴의 시작 위치를 반환

REGEXP_SUBSTR : 특정 패턴을 반환

REGEXP_LIKE : 특정 패턴과 매칭되는 결과 조회

REGEXP_COUNT : 특정패턴 횟수 반환

REGEXP_COUNT(text, 'c', 1, 'i' ) : i는 대소문자 구분 없이 출력

REGEXP_COUNT(text, 'aa' )

REGEXP_COUNT(text, 'a{2}' )

REGEXP_COUNT(text, '(a)(a)'' ) 같은 표현

​

* 정규식 기호

^ : 해당 문자로 시작하는 라인

$ : 해당 문자로 끝나는 라인

. : a로 시작해서 b로 끝나는 라인 . 1글자

* : 모든, 0포함

[] : 해당문자의 한 문자

[^] : 해당 문자에 해당하지 않는 한 문자

 

ROUND : 반올림 후 출력

TRUNC : 버림

MOD(a,b) : 나눈 후 나머지

CEIL : 올림

FLOOR : 가장 근접한 작은 정수

POWER(a,b) : 숫자 a의 숫자 b승

​

SYSDATE : 시스템의 현재 날짜와 시간

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY : 찾고자 하는 기준이 돌아오는 첫 번째 (남은 날 중 가장 빠른 월요일 등)

LAST_DAY : 주어진 날짜가 속한 달의 마지막 날짜

​

CHAR : 2천바이트, 유일한 고정길이 문자

VARCHAR2 : 4천바이트, 가변길이문자

NUMBER (정수, 소수점이하 들어갈 자리수) : 정수 1~38자리, 소수는 -84~127까지 가능

DATE : 7바이트

LONG

CLOB

BLOB

RAW

LONG RAW

BFILE

​

​

* 형변환함수

TO_CHAR(a,b) : 명시적 형변환함수. 날짜에서 문자, 숫자에서 문자

TO_NUMBER : 형변환함수

TO_DATE : 형변환함수 * 년도에서 50 기준 낮으면 4자리 사용할 것.

 

NVL (칼럼명, 바꿀값b) : null 값을 b로 대체

NVL2 (a,b,c) : a가 null이 아니면 b, null이면 c

​

DECODE : IF문과 비슷. 오라클에서만 사용. 함수 중첩 가능. 동격인 값 처리

ex: DECODE (A, B, DECODE(C,D,',null))

 

CASE : CASE 조건 WHEN 결과 THEN 출력 ELSE 출력 END "컬럼명"

DECODE보다 CASE문 지향

 

 

복수행 함수

 

​COUNT() : 개수를 세어줌 (COUNT(*) ; 모두)

SUM() : 합계, AVG() : 평균

MAX() : 최대, MIN() : 최소

GROUP BY : 컬럼명으로 그룹화. Alias x

HAVING 절 : 그룹함수의 조건* where절은 그룹함수를 비교조건으로 쓸 수 없음.

​

분석 함수, 윈도 함수

 

ROLLUP : 소계, 빨라짐

CASE : 소계와 합계도 출력

PIVOT : 행을 열 단위로 변경

UNPIVOT : 열을 행 단위로 변경

SUM() OVER : 누계

RANK(조건값) WITHIN GROUP (ORDER BY 컬럼명 [ASC]) : 순위 출력

RANK(조건값) OVER GROUP (ORDER BY 컬럼명 [ASC]) : 전체순위 출력

DENSE_RANK : 동일 순위를 하나의 건수로 취급. 한국에서 많이 쓰임. (1,2,2,3,)

ROW_NUMBER : 동일값도 고유 순위 부여 (1,2,3,4,)

​

*RANK OVER, DENSE_RANK, ROW_NUMBER 차이

 

​

CREATE AS FROM : 테이블 복사

PARTITON BY : 그룹핑할 구문

ex. RANK() OVER (PARTITON BY deptno ORDER BY sal DESC)

alnum : 알파벳과 숫자

\ : 메타캐릭터의 의미를 제거하는 탈출 문자. ?, *, ' 등과 같이 쓰임

 

 

JOIN : JOIN은 Oracle JOIN, ANSI JOIN 문법이 있다.

 

1) Oracle JOIN

SELECT table1.col1, table2.col2
FROM table1 alias1, table2 alias2
WHERE table1.col2 = table2.col2;

 

​2) ANSI JOIN  

* 선행 테이블은 조회할 적은 데이터의 테이블, 기준이 되는 테이블로 선택할 것.

SELECT a.col1, b.col1
FROM table 1 a [INNER] JOIN table2 b
ON a.col2 = b.col2;

 

카티션 곱 :

Equi Join : 등가 조인 (where에서 =)

Non-Equi Join : 비등가 조인

SELF JOIN : 테이블 안의 데이터 합쳐서 출력

INNER JOIN : 교집합 <-> OUTER JOIN : 없어도 보여줌

​

1) Oracle OUTER JOIN : null 쪽에 (+)

SELECT s.name "A", p.name "B"
FROM student s, professor p
WHERE s.profno(+) = p.profno;

 

2) ANSI OUTER JOIN : 데이터가 존재하는 쪽에 LEFT/RIGHT/FULL OUTER JOIN 로 표시

SELECT s.name "A", p.name "B"
FROM student s RIGHT OUTER JOIN professor p
ON s.profno = p.profno;

* 합칠 경우 오라클 JOIN은 UNION, ANSI JOIN는 FULL OUTER JOIN 을 사용.

 

 

DDL, DML

데이터베이스 내부에 데이터를 관리하기 위해 다양한 저장 객체Object 를 생성하고 이 중 데이터를 저장하기 위한 별도의 저장 공간Segment라 한다. 이런 오브젝트를 생성, 변경 관리하는 명령어가 DDL이다.

 

DDL (Data Definition Language) : CREATE(생성), ALTER(변경), TRUNCATE(잘라내기), DROP(삭제)

DML (Data Manipulation Language) : INSERT, UPDATE, DELETE, MERGE(병합)

DCL (Data Control Language) : GRANT(권한주기), REVOKE(권한뺏기)

TCL (Transaction Control Language) : COMMIT(확정), ROLLBACK(취소)

​

CREATE : 오브젝트나 스키마 생성. CREAT TABLE 테이블이름(칼럼 이름 형식(자리수) );

지정하지 않으면 null이나 DEFAULT 뒤에 붙은 값은 자동으로 넣을 수 있음

* 테이블 이름은 문자로 시작하며 30바이트까지 가능. 같은 계정 내 중복 불가.

 

CREATE TABLE tt02
(no     NUMBER(3,1)   DEFAULT 0,
name    VARCHAR2(10)  DEFAULT 'NO Name',
hiredate  DATE        DEFAULT SYSDATE);

​

CREATE GLOBAL TEMPORARY TABLE ( ) ON COMMIT delete ROWS; 임시테이블 생성하기

CTAS : 테이블 복사하기 : CREATE TABLE 테이블명 AS~ (where에 false식을 넣으면 테이블의 구조만 가져올 수 있다.)

​

ALTER : 컬럼 추가. 삭제, 이름 변경, 테이블이름변경

DELETE : 컬럼의 데이터 삭제

TRUNCATE : 차지하는 공간 삭제

DROP : 스키마, 테이블 모두 삭제 // 사용 시 주의

ALTER TABLE table1 read only; : 읽기전용

​

데이터 딕셔너리 : 데이터베이스를 운영하기 위한 정보를 모아둔 테이블. 중요해서 data dictionary view로만 조회 가능

base table은 데이터베이스 생성 시점 자동 생성되나 dictionary view는 catalog.sql이 수행되야 만들어짐

INSERT INTO table (column... ) VALUES (value...); (where에 false식을 넣으면 테이블의 구조만 가져올 수 있다.)

INSERT ALL WHEN ( ) THEN ( ) INTO ( ) VALUES ( ) WHEN ( ) THEN ( ) INTO ( ) VALUES ( )

UPDATE table SET column=value WHERE 조건;

 

 

제약조건

​NOT NULL : NULL로 변경하는 것은 ALTER ~ MODIFY를 이용해서 변경

UNIQUE : 중복값이 없어야 함.

PRIMARY KEY : NOT NULL + UNIQUE의 특징을 가지며 테이블들끼리의 유일성을 보장하는 컬럼에 설정. 테이블당 1개만 설정 가능

FOREIGN KEY : 다른 테이블의 컬럼을 참조

CHECK : 설정된 값만 입력 허용

CASCADE : 부모 테이블과 자식 테이블 간의 참조 설정이 되어 있을 때 부모 테이블의 제약 조건을 비활성화하면 이를 참조하고 있는 자식 테이블의 제약 조건까지 같이 비활성화시켜 주는 옵션

​