[ORACLE SQL] 데이터 조회 기본 문법 !!

 

▶ DML* (Data Manipulation Language) ; 데이터 조작어

 

- SELECT : 데이터를 조회 (SELECT*FROM) 

SELECT * FROM MEMBER;

 

 

- INSERT : 데이터를 추가 (INSERT INTO ~ VALUES( , );)

INSERT INTO MEMBER VALUES(50, 'YH', '010-1234-1212'); -- ID가 50인 회원의 레코드(로우)를 추가 

INSERT INTO TABLE VALUES('IDXX', 'HYONI', '010~');

 

 

- UPDATE : 데이터를 변경 (UPDATE ~ SET 00 = ' ' WHERE ID = ' ';) 

UPDATE MEMBER SET NAME = 'HYOEUN' WHERE ID = 'IDXX';

 

- DELETE : 데이터를 삭제 (DELETE FROM ~ WHERE ID = ' ';)

DELETE FROM MEMBER WHERE ID = 'IDXX';

 

 

▶ TCL* (Transation Control Language) ; 데이터 조작어

트랜젝션 ; 논리적인 실행(작업 수행) 단위 

(즉각 반영 및 저장되는 것이 아님)에 의해 변경된 내용을 관리   

 

- COMMIT : 변경된 내용을 영구적으로 저장

- ROLLBACK : 변경되기 이전 상태로 되돌림

 

- SAVEPOINT : 특정 위치까지 저장 혹은 이전 상태로 되돌릴 수 있는 지정점을 만듦

 

 

▶ DDL* (Data Definiton Language) ; 데이터 정의어 

(테이블, 인덱스, 뷰, 트리거 등 SQL문을 수행하는 대상을 의미하는 '객체'를 생성, 변경, 제거 할 때 사용)

 

- CREATE : 스키마, 도메인, 테이블, 뷰, 인덱스를 정의 및 생성    

- CREATE TABLE : 새로운 테이블 정의 및 생성 

CREATE TABLE DEPT01(
     DEPTNO   NUMBER(4),
     DNAME   VARCHAR2(10),
     LOC   VARCHAR2(10),
     AGE   NUMBER,
     REGDATE   DATE
);

 

- CREATE SCHEMA : 스키마를 정의하는 명령어

(스키마는 하나의 응용(사용자)에 속하는 테이블과 구성 요소 등을 그룹 지을 때 사용)

CREATE SCHEMA 스키마명 AUTHORIZATION 사용자ID;

 

- CREATE DOMAIN : 도메인을 정의하는 명령어

(도메인이란 하나의 속성이 취할 수 있는 값의 범위가 SQL에서 지원하는 전체 데이터 타입의 값이 아니고 일부분 일 때, 사용자는 그 값의 범위를 도메인으로 정의할 수 있음)

CREATE DOMAIN 도메인명 data_type[Default 묵시값 정의] [CONSTRAINT VALID 도메인명 CHECK(범위값)];

 

- CREATE INDEX : 인덱스를 정의하는 명령어

(인덱스는 검색을 빠르게 하기 위해 만든 보조적인 데이터 구조)

CREATE [UNIQUE] INDEX <인덱스명> ON 테이블명({속성명[ASC[DESC][,속성명[ASC | DESC]]}) [CLUSTER];

- CREATE VIEW : 뷰를 정의하는 명령어

(뷰는 하나 이상의 테이블로부터 유도되는 이름을 갖는 가상 테이블(Virtual Table))

CREATE VIEW 뷰명[(속성명[(속성명[,속성,..])] AS SELECT문;

 

- UNIQUE : 사용된 경우 - 중복값 없는 속성으로 인덱스를 생성 / 생략된 경우 - 중복값을 허용하는 속성으로 인덱스를 생성

- ALTER : 기존 테이블을 변경 (ALTER TABLE ~ MODIFY수정/ADD추가/DROP삭제. . ( );)

ALTER TABLE DEPT01 MODIFY DEPTNO(40);

ALTER TABLE DEPT01 ADD DNAME(100);

ALTER TABLE DEPT01 DROP COLUMN AGE;

 

- RENAME : 테이블 이름 변경 (RENAME ~ TO ~;)

RENAME DEPT01 TO DEPT02;

 

- TRUNCATE : 테이블의 모든 내용을 제거 (TRUNCATE TABLE ~;)

TRUNCATE TABLE DEPT02;

 

- DROP : 객체(테이블, 인덱스, 뷰, 트리거 등)를 제거

DROP TABLE DEPT02;

▶ DCL* (Data Control Language)

; 데이터를 사용할 수 있는 (CREATE SESSION) 권한을 사용자에게 부여하거나 제거하기 위해 사용하는 명령어 

- GRANT : 사용자에게 작업을 허용하는 특정 권한을 부여

GRANT CREATE SESSION TO OraUser01;

 

- REVOKE : 사용자로부터 특정 권한을 제거

REVOKE CREATE SESSION FROM OraUser01;

 

● SELECT 문으로 특정 데이터를 추출하기

- 테이블 내 특정 데이터 조회

SELECT DNAME, REGDATE FROM DEPT01;

SELECT COUNT(DEPTNO) FORM DEPT01;  -- 총 사원의 수 구하기

SELECT JOB FROM DEPT01;  -- 부서테이블에 있는 직책의 목록 조회

 

- 테이블 내 컬럼에 별칭 지정

SELECT DNAME N, REGDATE D FROM DEPT01;

→ 별칭에 공백, 특수문자, 대소문자, 예약어와 동일한 이름으로 하고싶다면 큰따옴표" "로 표기!!

 

 

- [ 중복제거 DISTINT ] 중복된 데이터를 한번씩 만 출력

SELECT DISTINCT LOC FROM DEPT01;

● WHERE 조건과 연산자 사용  

 

SELECT [DISTINCT] {*, COLUMN[..], . .} FROM ~ WHERE 조건들

 

예문:

SELECT DEPTNO, DNAME, SAL FROM DEPT01;  -- 전체 사원을 대상으로 함

SELECT DEPTNO, DNAME FROM DEPT01 WHERE SAL >= 3000;  --급여를  3000 이상 받는 사원을 대상으로 함

SELECT DEPTNO, DNAME FROM DEPT01 WHERE SAL < 2000;

 

▶ 비교 연산자 :  = 같다  |  > 보다 크다  |  < 보다 작다  |  <>, !=, ^= 같지 않다

예문 :

 

테이블 EMP에서 부서번호가 10번인 사원에 관한 모든 정보를 출력

SELECT * FROM DEPT01 WHERE DEPTNO = 10;

 

 

문제 :   

월급여가 2500이상인 사원 조회

SELECT * FROM DEPT01 WHERE SAL >= 2500;

이름이 'KING'인 사원조회

SELECT * FROM DEPT01 WHERE DNAME = KING;

 

 

 

▶ 관계 연산자 : NOT, AND, OR, BETWEEN, IN (ALL, ANY, EXISTS, LIKE, SOME)
 

 

 

- AND : 여러 조건을 모두 반족 할 경우 사용

 

예문 : 부서번호가 10번이고, 직급이 매니저인 사원

SELECT DNAME, DEPTNO, JOP FROM DEPT01 WHERE DEPTNO=10 AND JOP='MANAGER';

 

급여가 1000인 사원과 3000인 사원

SELECT DNAME, DEPTINO, JOP FROM DEPT01 WHERE SAL = 1000 AND SAL = 3000;

 

 

- OR : 두 가지 조건 중에서 한 가지만 만족하더라도 검색할 수 있도록 할 때 사용

 

예문 : 부서번호가 10번이거나 직급이 매니거인 사원

SELECT DNAME, DEPTNO, JOB FROM DEPT01 WHERE DEPTNO=10 OR JOB='MANAGER';

 

 

- NOT : 반대되는 논리값을 구함

 

예문 : 부서번호가 10번이 아닌 사원

SELECT DNAME, DEPTNO, JOB FROM DEPT01 WHERE NOT DEPTNO = 10;

 

- BETWEEN AND : 특정 범위 내에 속하는 데이터을 알아보고자 할 때 사용

 

예문 : 급여가 1000에서 3000 사이에 있는 사원

SELECT DNAME, DEPTINO, JOB FROM DEPT01 WHERE SAL BETWEEN 1000 AND 3000;

 

- IN : 동일한 칼럼이 여러 개의 값 중에 하나인지 알아보고자 할 때 사용  

Column_name IN(A, B, C);  -- (OR과 유사하다)

 

예문 : 사원번호가 775거나 655이거나 885인 사원

SELECT DNAME, SAL FROM DEPT01 WHERE DEPTNO=775 OR DEPTNO=655 OR DEPTNO=885;

SELECT DNAME, SAL FROM DEPT01 WHERE DEPTNO IN(775, 655, 885);

 

 

▶ 패턴 연산자 : LIKE 연산자와 와일드카드 %, _ 

 

 

 

- LIKE 연산자 : 검색하고자 하는 값을 정확히 모를 경우 와일드카드와 함께 사용하여 원하는 내용을 검색

columnName DNAME LIKE pattern;

→ 대입연산자(=) 없이 작성!!

 

 

 

- 와일드카드

 

 %  : 문자가 없거나 하나 이상의 문자가 오든 상관없고, 자릿수 제한 없음

 _  : 하나의 문자가 어떤 값이 오든 상관없고, 문자의 자릿수를 나타 냄
   

 

예문 : 

사원들 중 이름이 S로 시작하는 사원의 사원번호와 이름 조회

SELECT DNAME, DEPTNO FROM DEPT01 WHERE DNAME LIKE 'S%';

 

 

문제 : 

사원 이름에 T가 포함되는 사원번호와 이름을 조회하시오

SELECT DNAME, DEPTNO FROM DEPT01 WHERE DNAME LIKE '%T%';

사원 이름이 김으로 시작하지 않는 문자열 조회

SELECT * FROM DEPT01 WHERE NAME NOT LIKE '김%';

1985년 이후에 입사한 사원 조회 (HIREDATE입사일)

SELECT DNAME, HIREDATE FROM DEPT01 WHERE HIREDATE >= '1985/01/01';
-- 날짜는 반드시 단일 따옴표 안에 표시!!

사원들 중 휴대번호가 010으로 시작하고 1234로 끝나는 번호를 조회

SELECT*FROM DEPT01 WHERE PHONE LIKE '010-____-1234';

4글자로 구성되어야 하되 세번째 글자는 반드시 소문자 a여야 하는 사원 이름을 조회

SELECT*FROM DEPT01 WHERE NAME LIKE '__a_';

▶ NULL을 위한 연산자 : 오라클에서는 칼럼에 NULL값이 저장되는 것이 허용 됨 

 

- NULL : 미확정, 알 수 없는 (nuknown) 값을 의미. 

( 0도, 빈 공간도 아닌 어떠한 값이 존재하기는 하지만 어떤 값인지를 알아낼 수 없는 것을 의미 )

100 + NULL = NULL 과 같은 연산, 할당, 비교 불가!!

 

 

예문 :

 

커미션을 받지 않는 사원에 대한 검색

SELECT DNAME, COMM, JOB
FROM DEPT01
WHERE COMM = NULL;  

→ NULL이 저장되어 있는 경우에는 대입연산자(=)로 판단 불가

 

 

- IS [NOT] NULL*

특정 칼럼 값인지를 비교할 경우에는 비교연산자(=)를 사용하지 않고 IS NULL 연산자를,
NULL 값이 아닌지를 알아보려면 비교연산자(<>)를 사용하지 않고 IS NOT NULL 연산자를 사용

 

NULL은 값이 아니므로 = 또는 !=으로 비교 불가!! 

→ IS NULL : NULL이면 만족 (입력하지 않은 것만 출력)

→ IS NOT NULL : NULL이 아니면 만족 (입력한 것만 출력)
 

 

예문 :

 

커미션을 받지 않는 사원 

SELECT DNAME, COMM, JOB
FROM DEPT01
WHERE COMM IS NULL;

커미션을 받은 사원

SELECT DNAME, COMM, JOB
FROM DEPT01
WHERE COMM IS NOT NULL;

 

● ORDER BY로 정렬하여 조회

 

- ORDER BY : 로우(행) 정렬

→ 쿼리문 맨 뒤에 ASC / DESC와 함께 작성
→ 정렬의 기준이 되는 칼럼 이름 또는 SELECT 절에서 명시된 별칭 사용 가능

 

- ASC : 오름차순 정렬 (default)

 

→ 숫자 : 작은 값부터 정렬

→ 문자 : 사전 순으로 정렬

→ 날짜 : 빠른 날짜 순으로 정렬

→ NULL 가장 마지막에 나옴

 

 

예문: 

SELECT EMPTNO, DNAME 
FROM DEPT01
ORDER BY EMPTNO ASC;  -- 사원번호 기준으로 오름차순 정렬

 

- DESC : 내림차순 정렬

→ 숫자 : 큰 값부터 정렬

→ 문자 : 사전 반대 순으로 정렬

→ 날짜 : 늦은 날짜 순으로 정렬

→ NULL 가장 먼저 나옴

 

 

예문 :

 

사원번호 기준으로 내림차순 정렬 

SELECT EMPTNO, DNAME
FROM DEPT01
ORDER BY EMPTNO DESC;  

 

문제 : 

 

사원의 사번, 이름, 급여를 급여가 높은 순으로 출력

SELECT DEPTNO, DNAME, SAL  
FROM DEPT01 
ORDER BY SAL DESC; 

 
입사일이 가장 최근인 사원 순으로 사번, 이름, 입사일 출력 

SELECT DEPTNO, DNAME, REGDATE 
FROM DEPT01 
ORDER BY REGDATE DESC;

 

 

● DUAL 테이블

 

오라클에서 1일이 몇 초인지 환산하고자 한다. 

1일은 24시간이고 1시간은 60분이며 1분은 60초이므로 24*60*60하면 하루가 몇 초인지 계산된다.

 

SELECT 24*60*60 FROM DEPT;  -- DEPT 테이블이 4개의 로우로 구성 되있음

이 산술식을 오라클 프롬프트에 바로 입력하면 (SQL문의 종류에 속하지 않는 명령어이기 때문에) 오류가 발생한다.

SELECT 24*60*60 FROM DUAL;  -- DUAL 테이블은 DUMMY라는 1개의 칼럼(1개의 문자만을 저장 / X라는 값을 가짐)으로 구성되있다.

이 DUAL 테이블은 산술 연산이나 가상 칼럼 등의 값을 한번만 출력하고 싶을 때 사용하는데, 아주 많이 사용 된다.


​
​

● 단일 행 함수와 그룹 함수로 SQL 함수 분류

- 단일 행 함수 : 행 마다 함수가 적용되어 결과를 반환

 

→ 단일 행 함수 종류 : 문자 함수 / 숫자 함수 / 날짜 함수 / 변환 함수 / 일반 함수

 

- 그룹 함수 : 하나 이상의 행을 그룹으로 묶어 연산(총합, 평균 등)하여 하나의 결과를 반환 

→ 그룹 함수 종류 : SUM (누적 합계) / AVG (평균) / COUNT (총 개수) / MAX (최댓값) / MIN (최솟값) / STDDEV (표준편차) / VARIANCE (그룹의 분산을 반환)

 

 

▶ 문자함수 : 문자형의 값을 조작하여 변환된 문자 값을 반환

 

 

→ SUBSTR* : 문자를 추출 (한글 1Byte)

→ SUBSTRB : 문자를 추출 (한글 2Byte)
→ LENGTH* : 문자의 길이를 반환 (한글 1Byte)

(문자나 상수, 칼럼에 저장된 데이터 값이 몇 개의 문자로 구성되었는지 길이를 알려주는 함수)

→ LENGTHB : 문자의 길이를 반환 (한글 2Byte)
→ INSTR* : 특정 문자의 위치 값을 반환 (한글 1Byte)

→ INSTRB : 특정 문자의 위치 값을 반환 (한글 2Byte)

→ LOWER : 소문자로 변환 

→ UPPER : 대문자로 변환 

→ INTICAP : 첫글자만 대문자로 변환 

→ CONCAT* : 문자의 값을 연결 

→ LPAD, RPAD : 입력 받은 문자열과 기호를 정력하여 특정 길이의 문자열로 반환
→ TRIM : 잘라내고 남은 문자를 표시

→ CONVERT : CHAR SET을 변환  
→ CHAR : ASCII 코드 값읋 변환

→ ASCII : ASCII 코드 값을 문자로 변환

→ REPLACE : 문자열에서 특정 문자를 변경

 

예문 :

 

글자의 개수를 구함 

SELECT LENGTH('DataBase'), LENGTH('데이터베이스') FROM DUAL; 

메모리에 차지하는 바이트 수를 구함 

SELECT LENGTHB('DataBase'), LENGTHB('데이터베이스') FROM DUAL; -- 해보기 

 

입력한 문자 값을 소문자로 변환

SELECT 'DataBase', LOWER('DataBase') FROM DUAL;

사원 테이블에서 부서번호가 10번인 사원명을 모두 소문자로 변환

SELECT DNAME, LOWER('DNAME') FROM DEPT01 WHERE DEPTNO=10;

입력한 문자 값을 대문자로 변환

SELECT 'DataBase' UPPER('DataBase') FROM DUAL;

직급이 'manager'인 사원을 검색

SELECT DNAME, JOB FROM DEPT01 WHERE JOB = UPPER('manager');

첫 글자만 대문자로 나머지는 소문자로 변환 

SELECT INITCAP('DATA BASE PROGRAM') FROM DUAL;

사원 테이블의 10번 부서 소속의 사원이름의 첫 글자만 대문자로

SELECT DEPTNO, INITCAP(DNAME) FROM DEPT01 WHERE DNAME = 10;

​
두 문자를 연결하는 함수

SELECT CONCAT('Data', 'Base') FROM DUAL;

 

 

문제 : 

 

10번 부서 소속 사원들의 이름의 길이를 출력 

SELECT DEPTNO, DNAME, LENGTH(DNAME) FROM DEPT01 WHERE DNAME=10; 

직원 중 이름이 4글자인 직원의 이름을 소문자로 출력 

SELECT DEPTNO, LOWER(DNAME) FROM DEPT01 WHERE LENGTH(DNAME)=4; 

 

- SUBSTR / SUBSTRB : 문자열 일부만 추출하는 함수

SUBSTR[B](대상, 시작위치, 추출할 개수)

SELECT SUBSTR('DataBase', 1, 3) FROM DUAL;  -- Dat

SELECT SUBSTR('DataBase', -4, 3) FROM DUAL;  -- Bas / 음수는 뒤쪽부터 세어 시작위치를 잡는다

 

 

문제 : 

 

20번 부서 사원들 중의 입사 '년도' 알아내기

SELECT DNAME, HIRDATE, SUBSTR(HIRDATE, 1, 2) FROM DEPT01 WHERE DEPTNO = 20;

20번 부서 사원들 중의 입사 '년도' 알아내고 87년도에 입사한 사원 알아내기

SELECT DNAME, HIRDATE, SUBSTR(HIRDATE, 1, 2) FROM DEPT01 WHERE DEPTNO=20 AND DEPTNO=87;

이름이 K로 끝나는 직원을 검색

SELECT DNAME FROM DEPT01 WHERE DNAME LIKE '%K';