[Oracle] : DAY20 _ SELECT 기본 문법 및 연산자

DML (SELECT)

 

주요용어 

행 : 가로 

칼럼 :  세로 

기본키 : 한개만 알고 있으면 그 키가 가지고 있는 정보를 다 알 수 있다. 

외래키 : 테이블간의 연결 역할을 해서 관계를 만들어준다. 

Null : 값이 없음  // 자바는 null 참조하는게 없다 

컬럼값 : 특정 값

 

SQL (Structured Query Language) 

구조적으로 물어보는 것에 답변을 한다.

ㄴ 남자 손들어보세요! 가 아닌, 남자인데 25살 이상이고 까만바지에 흰색 옷을 입고 있는 사람 손들어보세요! 

 

관계형 데이터베이스에서 데이터를 조회하거나 조작하기 위해 사용하는 표준 검색 언어

원하는 데이터를 찾는 방법이나 절차를 기술하는 것이 아닌 조건을 기술하여 작성

분류	용도	명령어
DQL	데이터 검색	SELECT
DML	데이터 조작  추가 삭제 수정	INSERT, UPDATE, DELETE
DDL	데이터 정의  데이터 키우거나 줄이거나 컬럼 이름을 바꾸는 것	CREATE, DROP, ALTER
DCL	데이터 제어  권한을 주는 것	GRANT, REVOKE
TCL	트랙젝션 제어	COMMIT, ROLLBACK

 

 

 

 

---

실습 테이블을 다운 받아 데이터를 저장한 뒤 SELECT문 실습을 하였다.

아래는 그에 관련된 EMPLOYEE 테이블이다. (타이핑하는데 오래 걸리기에 제공받아서 실습 진행하였다)

 

- EMPLOYEE 테이블

SELECT 기본 문법 및 연산자 

SELCET (DEL 또는 EQ) 조회라는 뜻을 가지고 있으며, 데이터를 조회(SELECT)하면 조건에 맞는 행들이 조회됨 
이때 조회된 행들의 집합을 "RESULT SET"이라고 한다. 

 

RESULT SET은 0개 이상의 행이 포함될 수 있다. 

SELECT  칼럼명, 칼럼명, FROM  테이블명 SELECT  : 조회하고하는 칼럼명을 작성하며,                     여러 칼럼을 조회하는 경우 쉼표(,)로 구분하며 마지막 칼럼 다음은 쉼표(,)를 사용하지 않는다.                     조회 결과는 기술한 칼럼명 순으로 표시된다.  FROM  :    조회 대상 칼럼이 포함된 테이블명을 작성한다.

 

모든 행 모든 칼럼 조회 

ex) EMPLOYEE 테이블에서 모든 사원 정보를 조회 

SELECT * FROM EMPLOYEE;

 

원하는 조건 칼럼 조회

ex) EMPLOYEE 테이블에서 모든 사원의 사번, 이름, 전화번호 조회 

SELECT EMP_ID,EMP_NAME,EMP_NO FROM EMPLOYEE;

 

산술연산 : 칼럼값에 대해 산술 연산한 결과 조회 가능 

 

ex) EMPLYOEE 테이블에서 모든 사원의 이름, 급여, 연봉(급여*12개월)을 조회

SELECT EMP_ID,EMP_NAME,SALARY,SALARY*12 FROM EMPLOYEE;

        

 

● 칼럼 별칭 지정 

SELECT 조회 결과의 집합인 RESULT SET에 컬럼명을 지정

 

1) 컬럼명 AS 별칭 : 띄어쓰기 X 특수문자 X 문자 O
2) 컬럼명 별칭 : 1번에서 AS만 생략 한 것 

3) 컬럼명 AS "별칭" : 띄어쓰기 O, 특수문자 O, 문자O
4) 컬럼명 "별칭" : 3번에서 AS만 생략 한 것 

 

 

리터럴 : 임의로 지정한 값을 기존 테이블에 존재하는 값처럼 사용 

- 리터럴 표기 법 ' '홑 따옴표 

 

● 중복 값 제외 

DISTINCT 

ㄴ SELECT문에 딱 한번만 작성할 수 있다. ㄴ SELECT문에 가장 앞에 작성되어야한다. 

ex)EMPLOYEE 테이블에 저장된 직원들이 속해있는 부서 코드 종류 조회 

SELECT DISTINCT DEPT_CODE FROM EMPLOYEE;

 

● 오늘 날짜 조회

--SELECT SYSDATE FROM DUAL;

SYSDATE  : 시스템 상의 현재 날짜 ((년/월/일/시/분/초 단위까지 표현 가능하고 디벨로퍼의 날짜 표기 방법 변경 가능) 

DUAL :  가짜 테이블 (임시 테이블)

 

- 날짜 표기 방법 설정 하는 방법 

도구 > 환경설명 > 데이터베이스 > NSL > 날짜형식 'YYYY-MM-DD HH24:MI:SS'으로 세팅하면

'023-05-16 09:34:35'으로 확인할 수 있다.  

 

● WHERE절 

ㄴ 테이블에서 조건을 충족하는 값을 가진 행만 조회하고자 할 때 사용 

-- 비교 연산자 :  >, <, >=, <=, =(같다), != ,<>(같지않다)

 

ex) EMPLOYEE 테이블에서 급여가 3백만원 초과인 직원의 사번 이름 급여 부서코드를 조회 

/*해석순서*/
/*3*/SELECT EMP_ID,EMP_NAME,SALARY,DEPT_CODE 
/*1*/ FROM EMPLOYEE
/*2*/ WHERE SALARY >3000000;

 

● 논리연산자 (AND, OR) 

ex) EMPLOYEE테이블에서 급여가 200만 이상이고 부서코드가 'D6'인 직원의 이름,급여,부서코드를 조회 

SELECT EMP_NAME,SALARY,DEPT_CODE
FROM EMPLOYEE
WHERE SALARY >= 2000000
AND DEPT_CODE ='D6';

 

● BETWEEN 

-- 칼럼명BETWEEN A AND B : 칼럼 값이 A 이상 B이상인 경우

 

-- EMPLOYEE테이블에서
-- 급여가 300만이상, 500만 이하인 직원 
-- 사번, 이름, 급여 조회 

SELECT EMP_ID,EMP_NAME,SALARY
FROM EMPLOYEE
WHERE SALARY BETWEEN 3000000 AND 5000000;

 

--칼럼명 NOT BETWEEN A AND B :  컬럼 값이 A이상 B이하가 아닌경우 (컬럼 값이 A 미만 B초과인 경우)

 

-- EMPLOYEE테이블에서
-- 급여가 200만미만, 500만 초과인 직원의 
-- 사번, 이름, 급여 조회 
SELECT EMP_ID,EMP_NAME,SALARY 
FROM EMPLOYEE
WHERE SALARY NOT BETWEEN 2000000 AND 5000000;

 

● LIKE

WHERE  칼럼명  LIKE '패턴' / NOT LIKE (LIKE 결과를 부정) 

-- LIKE패턴(와일드카드) : '%'(포함),'_'(글자 수)

 

'%'예시 
1) 'A%' : 문자열에 A로 시작하는 모든 컬럼 값 
2) '%A' : 문자열에 A로 끝나는 모든 컬럼 값
3) '%A%' : 문자열에 A가 포함되어 있는 모든 컬럼 값

'_'예시 
1) 'A_': A뒤에 아무거나 한글자 
2) /'___A' : A 앞에 아무거나 세글자 (4글자 문자열이면서 A로 끝나야함)

 

--EMPLOYEE 테이블에서 성이'이'씨인 사원의 사번, 이름 조회 

SELECT EMP_ID, EMP_NAME

FROM EMPLOYEE
WHERE EMP_NAME LIKE '이%';

--EMPLOYEE 테이블에서 이름에'하'가 포함된 사원의 사번, 이름 조회 
SELECT EMP_ID,EMP_NAME
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%하%';

--EMPLOYEE 테이블에서 전화번호가 010으로 시작하는 사원의 사번, 이름,전화번호 조회
SELECT EMP_ID,EMP_NAME,PHONE
FROM EMPLOYEE
WHERE PHONE LIKE '010%';

 

-- EMPLOYEE 테이블에서 
-- 이메일에 _앞글자가 세글자인 사번, 이름, 이메일 조회 

SELECT EMP_ID,EMP_NAME,EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '____%';

이렇게 작성하면 와일드 카드 문자(_)와 패턴에 사용된 일반문자(_)의 모양이 같아서 정확한 값이 나오지 않는다 !

해결방법은 ESCAPE OPTION을 이용하여 일반 문자로 처리할 '_','%'앞에 아무 특수문자나 붙임 

SELECT EMP_ID,EMP_NAME,EMAIL
FROM EMPLOYEE
WHERE EMAIL LIKE '___$_%'ESCAPE'$';

 연산자 우선순위

1. 산술연산자
2. 연결연산자
3. 비교연산자
4. IS NULL / IS NOT NULL, LIKE, IN / NOT IN
5. BETWEEN AND / NOT BETWEEN AND
6. NOT(논리연산자)
7. AND(논리연산자)
8. OR(논리연산자)

● IN 연산자 / NOT IN 

비교하려는 값과 목록에 작성된 값 중 일치하는 것이 있으면 조회하는 연산자(OR 연산을 연달아 작성한 효과)

컬럼명 IN (값 1,값2, 값3...)

 

-- EMPLOYEE테이블에서 부서코드가 D1 또는 D6 또는 D9인 사원의 사번,이름, 부서코드 조회 
SELECT EMP_ID,EMP_NAME,DEPT_CODE
FROM EMPLOYEE
(==WHERE DEPT_CODE = 'D1' OR DEPT_CODE = 'D6' OR DEPT_CODE = 'D9'  )
WHERE DEPT_CODE IN ('D1','D6','D9');

 

● 연결연산자(||)

여러 값을 하나의 칼럼 값으로 연결하는 연산자 (자바의 문자열 + (이어쓰기) 효과)

● NULL처리 연산자 

ㄴ여러 값을 하나의 칼럼 값으로 연결하는 연산자 (자바의 문자열 + (이어쓰기) 효과)

JAVA에서 NULL : 참조하는 객체가 없다

DB에서 NULL 칼럼값이 없다 

1) IS NULL :칼럼 값이 NULL인 경우 조회 
2) IS NOT NULL : 칼럼 값이 NULL이 아닌 경우 조회

 

● ORDER BY 절 
- SELECT문의 조회 결과 (RESULT SET)를 정렬할 때 작성하는 구문 
[작성법]
해석순서 3: SELECT 컬럼명 AS 별칭,컬럼명,컬럼명.....
해석순서 1: FROM 테이블명 
해석순서 2: WHERE 조건식 
해석순서 4: ORDER BY 컬럼명 | 별칭 | 컬럼순서 [정렬방식(오름/내림] [NULLS FIRST|LAST]

① 오름차순 : ASC ( ORDER BY 정렬방식은 기본적으로 오름차순이니 생략 가능하다)

ㄴ 오름차순일 때 NULL LAST가 기본값 

② 내림차순  : DESC

ㄴ내림차순 일때 NULLS FIRST가 기본값 

● 정렬중첩

큰 분류를 먼저 정렬하고 내부에 작은 분류를 정렬 

-- EMPLOYEE테이블에서 부서코드 오름차순 정렬후 
-- 부서별 급여 내림차순 정렬 

SELECT EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
ORDER BY DEPT_CODE,SALARY DESC;

---

 

함수

칼럼의 값을 읽어서 연산한 결과를 반환

- 단일행(SINGLE ROW)함수 : N개의 값을 읽어서 N개의 결과를 반환 
- 그룹(GROUP)함수 : N개의 값을 읽어 1개의 결과 반환
- 함수는 SELECT절, WHERE절, ORDER BY, GROUP BY, HAVING사용 가능 

 

1) 단일행 함수

● LENGTH (문자열|칼럼) : 문자열 길이 반환 (띄어쓰기포함)

 

- HELLO WORLD 글자 수 출력

SELECT LENGTH ('HELLO WORLD') FROM DUAL; 

 

- 12글자인 이메일만 조회 
SELECT EMAIL, LENGTH(EMAIL) 

FROM EMPLOYEE
WHERE LENGTH(EMAIL)=12;

 

● INSTR('문자열'|컬럼명, '찾을문자' ,[찾을 위치 시작위치,[순번]]
- 지정한 위치부터 지정한 순번째로 검색되는 문자의 시작 위치를 반환

 

- 문자열에서 맨 앞에 있는 B 위치 조회
SELECT INSTR('AABAACAABBAA','B')FROM DUAL; // 3반환 

 

● SUBSTR('문자열'|컬럼명, 잘라내기 시작할 위치 [,잘라낼 길이])
- 컬럼이나 문자열에서 지정한 위치부터 지정된 길이만큼 문자열을 잘라내서 반환
- 잘라낼 길이를 생략 시 끝까지 잘라냄 

 

 EMPLOYEE 테이블에서 사원명, 이메일 중 아이디만 조회 sun_di@or.kr
SELECT EMP_NAME,SUBSTR(EMAIL, 1,INSTR(EMAIL,'@')-1) 아이디
FROM EMPLOYEE
ORDER BY 아이디;

● TRIM([옵션] '문자열'|칼럼명 [FROM '문자열' | 컬럼명] )

- 주어진 칼럼이나 문자열의 앞, 뒤, 양쪽에 있는 지정된 문자를 제거  (보통 양쪽 공백 제거에 많이 사용)

옵션 : LEADING(앞쪽), TRAILING(뒤쪽), BOTH(양쪽, 기본값)

 

SELECT '---KH---',TRIM(TRAILING'-'FROM'---KH---') FROM DUAL;

---

숫자 관련 함수

 

● ABS(숫자|컬럼명) : 절대값 

SELECT ABS(10),ABS(-10) FROM DUAL; // 10 10

 

● MOD(숫자|컬럼명, 숫자|컬럼명) : 나머지 값 반환 

SELECT EMP_NAME, SALARY, MOD(SALARY ,100000)
FROM EMPLOYEE;

 

 

● ROUND(숫자 |칼럼명 [,소숫점위치]):반올림

 

SELECT 123.456, ROUND(123.456)FROM DUAL;    // 123
SELECT 123.456, ROUND(123.456,1)FROM DUAL;  // 123.5
SELECT 123.456, ROUND(123.456,2)FROM DUAL;  //123.46

SELECT 123.456, ROUND(123.456,0)FROM DUAL; //123
SELECT 123.456, ROUND(123.456,-1)FROM DUAL; //120
SELECT 123.456, ROUND(123.456,-2)FROM DUAL;  //100

 

● CEIL(숫자|컬럼명) : 올림 

● FLOOR(숫자 |컬럼명) : 내림 

● TRUNC(숫자|컬럼명 [,위치]) :특정 위치 아래를 버림 (절삭)

SELECT TRUNC(-123.5),FLOOR(-123.5)FROM DUAL; // -123  -124

 

---

 

 날짜(DATE) 관련 함수

 

● SYSDATE 

시스템에 현재 시간(년,월,일,시,분,초)을 반환  

 

● SYSTIMESTAMP

SYSDATE + MS(시차) 단위 추가

 

● MONTHS_BETWEEN(날짜,날짜) :

 두 날짜의 개월 수 차이 반환 ( 만나이에서 자주 나옴!)

SELECT  ROUND( MONTHS_BETWEEN(SYSDATE, '2022/02/21') ) || '개월' AS 수강기간 FROM DUAL;

 

 

● ADD_MONTHS(날짜,숫자)

날짜에 숫자만큼의 개월 수를 더함 

SELECT ADD_MONTHS(SYSDATE,4)+7 FROM DUAL;

SYSDATE 2023-05-23이라고 가정했을때, 

출력결과 :2023-09-30 21:26:47 
		 2023-02023-05+4/+/23+7

 

● LAST_DAY(날짜)

해당 달의 마지막 날짜를 구함 

 

● EXTRACT
년,월,일 정보를 추출하여 리턴 

EXTRACT(YEAR FROM 날짜) : 년도만 추출
EXTRACT(MONTH FROM 날짜) : 월만 추출
EXTRACT(DAY FROM 날짜) : 일도만 추출

---

한줄 정리 

1) SELECT : 조회

데이터를 SELECT 하면 조건에 맞는 행(RESULT SET)들이 조회되며, 0개 이상의 행이 포함될 수 있다. 

 

SELECT 컬럼명  AS "별 칭 " FROM 테이블명

1) 컬럼명 AS 별칭 / 컬럼명 별칭: 띄어쓰기 X 특수문자 X 문자 O
2) 컬럼명 AS "별칭" / 컬럼명 "별칭": 띄어쓰기 O, 특수문자 O, 문자O

 (칼럼값 연결하는 연산자 || (자바의 + 역할) )

 

WHERE [조건을 충족하는 값을 가진 행만 조회 ]

 

BETWEEN A AND B (A 부터 B 까지) 숫자 한글 날짜 가능 

 

DISTINCT : SELECT문의 가장 앞에 딱 한번만 작성가능하다(중복제거)

 

와일드카드 

WHERE 칼럼명 LIKE '패턴'

 

'A%' (A로 시작하는)

'%A' (A로 끝나는)

'%A%' (A가 들어가는)

 

'A_' (A 뒤에 아무거나 한글자)

/'___A' (A앞에 아무거나 세글자(4글자 문자열이면서  A로 끝나야함))

 

LIKE '___$_%'ESCAPE'$' (3글자  + '_'가 들어가는 단어로 시작하는 )

 

IN (200,205,210)

조회할 값 중에 200 OR 205 OR 210이 있는지 

 

IS NULL (칼럼 값이 NULL 칼럼 조회)

IS NOT NULL (칼럼 값이 NULL이 아닌 칼럼 조회)

 

ORDER BY 컬럼명 | 별칭 | 컬럼순서  ( RESULT SET)를 정렬할 때 작성

 ASC(기본) | DESC  NULLS LAST(기본) NULL FIRST

 

정렬중첩  ORDER BY 컬럼명, 컬럼명