4/21 - SQL 데이터 정의어, 오라클 함수

SELECT

구조

SELECT 컬럼 명
FROM 테이블 명
WHERE 조건
GROUP BY 컬럼명
HAVING 조건 (그룹의 조건 != WHERE)
ORDER BY 컬럼명;

모델링

Entity (개체) - table
Attribute (속성) - column
Relation (관계) - fk

PK (Primary Key, 기본키)

데이터 식별을 위한 키
중복 불가 (유니크 제약 조건)
인덱스 자동 추가

FK (Foreign Key, 외래키)

다른 데이터의 PK를 참조하는 키
정규화로 인해 생성
이 컬럼으로 조인

식별, 비 식별 관계

식별 관계 - PK ↔ FK
- 개별 테이블 삭제 가능
- 선
비 식별 관계 - PK ↔ 일반 컬럼
- 테이블 삭제 시 연관된 테이블까지 같이 삭제
- 점선

별칭

최종 출력되는 열 이름을 임의로 지정할 수 있음
NULL값 존재 시 연산을 아예 수행하지 않음

select ename, sal, sal*12+comm, comm
from emp;

출력 결과

ename sal sel*12+comm comm
SMITH	800		
ALLEN	1600	19500	300
WARD	1250	15500	500
JONES	2975  (null) (null)
MARTIN	1250	16400	1400
BLAKE	2850		(null) (null)
CLARK	2450		(null) (null)
SCOTT	3000		(null) (null)
KING	5000		(null) (null)
TURNER	1500	18000	0
ADAMS	1100		(null) (null)
JAMES	950		(null) (null)
FORD	3000		(null) (null)
MILLER	1300		(null) (null)

AS를 사용하여 별칭 지정
- as를 생략할 수도 있다.
- as를 기준으로 뒤의 이름이 별칭으로 지정된다.
- 컬럼명 뒤, 테이블명 뒤, 서브쿼리 뒤
- result set (결과 뒤 집합)에 표시
- 컬럼명에 사용 가능하다.

select ename, sal, sal*12+comm as annsal, comm
from emp;

출력 결과

ename sal  annsal comm
SMITH	800		
ALLEN	1600	19500	300
WARD	1250	15500	500
JONES	2975	(null) (null)
MARTIN	1250	16400	1400
BLAKE	2850		(null) (null)
CLARK	2450		(null) (null)
SCOTT	3000		(null) (null)
KING	5000		(null) (null)
TURNER	1500	18000	0
ADAMS	1100		(null) (null)
JAMES	950		(null) (null)
FORD	3000		(null) (null)
MILLER	1300		(null) (null)

ORDER BY(순서)

데이터의 순서를 정렬 할 때 사용
(열)번호로도 정렬할 수 있다.
오라클에서 Sql 구문의 가장 끝 부분에 작성한다.

ORDER BY 컬럼 명
FROM 테이블 명
ORDER BY 컬럼명 [, 컬럼명...(여러 열 지정 가능)] [정렬 옵션]

정렬하려는 열 이름 하나 이상을 지정해야 함
종류
- ASC - 오름차순
- DESC - 내림차순
- 기본 값은 오름차순
여러 개 사용 시, 앞에 정렬 후 뒤에 정렬

select *
from emp
order by sal;

출력 결과

EMPNO ENAME  JOB  MGR   HIREDATE  SAL  COMM  DEPTNO
7369	SMITH	CLERK	7902	80/12/17	800		20
7900	JAMES	CLERK	7698	81/12/03	950		30
7876	ADAMS	CLERK	7788	87/05/23	1100		20
7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
7934	MILLER	CLERK	7782	82/01/23	1300		10
7844	TURNER	SALESMAN	7698	81/09/08	1500	0	30
7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
7782	CLARK	MANAGER	7839	81/06/09	2450		10
7698	BLAKE	MANAGER	7839	81/05/01	2850		30
7566	JONES	MANAGER	7839	81/04/02	2975		20
7788	SCOTT	ANALYST	7566	87/04/19	3000		20
7902	FORD	ANALYST	7566	81/12/03	3000		20
7839	KING	PRESIDENT		81/11/17	5000		10

WHERE

더 정확하고 다양한 결과를 출력
특정 조건을 기준으로 원하는 행을 출력

SELECT [조회할 열]
FROM [조회할 테이블]
WHERE [조건식]

SELECT *
FROM EMP
WHERE DEPTNO = 30;

출력 결과

EMPNO ENAME  JOB  MGR   HIREDATE  SAL  COMM  DEPTNO
7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
7698	BLAKE	MANAGER	7839	81/05/01	2850		30
7844	TURNER	SALESMAN	7698	81/09/08	1500	0	30
7900	JAMES	CLERK	7698	81/12/03	950		30

여러 조건 식

AND, OR 연산자 사용 가능

AND

둘 다 참일 경우
조건이 ~중에서 이거
~하고

SELECT *
FROM EMP
WHERE DEPTNO = 30 AND JOB = 'SALESMAN';

출력 결과

EMPNO ENAME  JOB  MGR   HIREDATE  SAL  COMM  DEPTNO
7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
7844	TURNER	SALESMAN	7698	81/09/08	1500	0	30

SELECT *
FROM EMP
WHERE DEPTNO = 30 OR JOB = 'CHECK';

출력 결과

EMPNO ENAME  JOB  MGR   HIREDATE  SAL  COMM  DEPTNO
7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
7698	BLAKE	MANAGER	7839	81/05/01	2850		30
7844	TURNER	SALESMAN	7698	81/09/08	1500	0	30
7900	JAMES	CLERK	7698	81/12/03	950		30

하나라도 참인 경우
조건이 ~랑 ~
~하거나

연산자

연산자를 사용할 수도있다.

SELECT *
FROM EMP
WHERE SAL * 12 = 36000;

출력 결과

EMPNO ENAME  JOB  MGR   HIREDATE  SAL  COMM  DEPTNO
7788	SCOTT	ANALYST	7566	87/04/19	3000		20
7902	FORD	ANALYST	7566	81/12/03	3000		20

NOT 연산자는 값이 다르면 출력한다.
문자열을 등호로 비교하면 사전 순으로 비교한다.
- <>, ! =, ^= 이 세가지는 같다. (서로 다름을 나타내는 연산자)

SELECT *
FROM EMP
WHERE SAL != 3000;

출력 결과

EMPNO ENAME  JOB  MGR   HIREDATE  SAL  COMM  DEPTNO
7369	SMITH	CLERK	7902	80/12/17	800		20
7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
7566	JONES	MANAGER	7839	81/04/02	2975		20
7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
7698	BLAKE	MANAGER	7839	81/05/01	2850		30
7782	CLARK	MANAGER	7839	81/06/09	2450		10
7839	KING	PRESIDENT		81/11/17	5000		10
7844	TURNER	SALESMAN	7698	81/09/08	1500	0	30
7876	ADAMS	CLERK	7788	87/05/23	1100		20
7900	JAMES	CLERK	7698	81/12/03	950		30
7934	MILLER	CLERK	7782	82/01/23	1300		10

IN, NOTIN 연산자

특정 열에 해당하는 조건을 여러 개 지정

SELECT [조회할 열 이름]
FROM [조회할 테이블 이름]
WHERE 열 이름 IN (데이터1, 데이터2 ...);

SELECT *
FROM EMP
WHERE JOB IN ('MANAGER', 'SALESMAN', 'CLERK')

출력 결과

EMPNO ENAME  JOB        MGR   HIREDATE  SAL  COMM  DEPTNO
7369	SMITH	CLERK	      7902	80/12/17	800		       20
7499	ALLEN	SALESMAN	  7698	81/02/20	1600	300	   30
7521	WARD	SALESMAN	  7698	81/02/22	1250	500	   30
7566	JONES	MANAGER	    7839	81/04/02	2975		     20
7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	 30
7698	BLAKE	MANAGER	    7839	81/05/01	2850		     30
7782	CLARK	MANAGER	    7839	81/06/09	2450		     10
7844	TURNER	SALESMAN	7698	81/09/08	1500	0	     30
7876	ADAMS	CLERK	      7788	87/05/23	1100		     20
7900	JAMES	CLERK	      7698	81/12/03	950		       30
7934	MILLER	CLERK	    7782	82/01/23	1300		     10

BETWEEN A AND B 연산자
```
SELECT *
FROM EMP
WHERE SAL BETWEEN 2000 AND 3000

출력 결과

EMPNO ENAME  JOB    MGR   HIREDATE  SAL  COMM  DEPTNO
7566	JONES	MANAGER	7839	81/04/02	2975		     20
7698	BLAKE	MANAGER	7839	81/05/01	2850		     30
7782	CLARK	MANAGER	7839	81/06/09	2450		     10
7788	SCOTT	ANALYST	7566	87/04/19	3000		     20
7902	FORD	ANALYST	7566	81/12/03	3000		     20
```
- 최솟값 이상 최댓값 이하로 판단한다. (경계값 포함)
- 날짜를 비교할 때 시간이 들어있으면 검색이 안된다.
  - 예를 들어 '2024-04-03’의 경우 00시 00분 00초 까지만 검색되므로, 23:59:59를 밝혀주거나, 04-04로 해주어야 한다.
  - 날짜의 경우 직접 포맷팅 해주어야 한다. (기본 설정은 81/04/02 형식)
SELECT [조회할 열 이름] FROM [조회할 테이블 이름] WHERE 열 이름 BETWEEN 최솟값 AND 최댓값;
LIKE 연산자와 와일드카드
```
SELECT *
FROM EMP        
WHERE ENAME LIKE 'S%';

출력 결과

EMPNO ENAME  JOB    MGR   HIREDATE  SAL  COMM  DEPTNO
7369	SMITH	CLERK	7902	80/12/17	800		20
7788	SCOTT	ANALYST	7566	87/04/19	3000		20
```
- 종류
  - NOTLIKE
  - _
    - 어떤 값이든 상관없이 한 개의 문자 데이터
    - 한 글자
    - 1개
      - ‘_S’ - 끝이 S인 2글자
      - ‘S_’ - S로 시작하는 2글자
      - ‘S’ - S가 포함된 3글자
  - %
    - 길이와 상관없이(문자 없는 경우 포함) 모든 문자 데이터
    - 여러 글자
    - 0개 이상
      - ‘%S’ - 끝이 S
      - ‘S%’ - S로 시작
      - ‘%S%’ - S가 포함된
컬럼명 LIKE '%'

IS NULL

NULL값을 찾아준다.

집합 연산자

조회한 결과를 하나의 집합처럼 사용 가능하다.

UNION

두 개 이상의 SELECT 결과문을 합쳐서 반환할 수 있다.
타입(열의 자료형)이 다르면 오류가 난다.
갯수가 다르면 오류가 난다.
중복은 제거된다.

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 20;

출력 결과

7369	SMITH	800	20
7566	JONES	2975	20
7782	CLARK	2450	10
7788	SCOTT	3000	20
7839	KING	5000	10
7876	ADAMS	1100	20
7902	FORD	3000	20
7934	MILLER	1300	10

UNION ALL

중복된 결과 값도 모두 출력

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION ALL
SELECT EMPNO, ENAME,DEPTNO , SAL
FROM EMP
WHERE DEPTNO = 10;

출력 결과

7782	CLARK	2450	10
7839	KING	5000	10
7934	MILLER	1300	10
7782	CLARK	10	2450
7839	KING	10	5000
7934	MILLER	10	1300

MINUS
- 차집합의 개념이다.
INTERSECT
- 교집합의 개념이다.

연산자 우선 순위

헷갈릴 때는 괄호로 묶자
가장 높은 연산자는 *,/ 이고, 가장 낮은 것은 OR이다.

데이터 처리와 가공을 위한 오라클 함수

함수
- X → Y 가 되는 식
- 오라클도 함수가 존재
- 내장 함수와 사용자 정의 함수가 존재

문자 데이터 가공 함수

UPPER
- 대문자 변환
LOWER
- 소문자 변환
INITCAP
- 첫 글자는 대문자로, 나머지 문자를 소문자로 반환

SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
FROM EMP;

출력 결과

SMITH	SMITH	smith	Smith
ALLEN	ALLEN	allen	Allen
WARD	WARD	ward	Ward
JONES	JONES	jones	Jones
MARTIN	MARTIN	martin	Martin
BLAKE	BLAKE	blake	Blake
CLARK	CLARK	clark	Clark
SCOTT	SCOTT	scott	Scott
KING	KING	king	King
TURNER	TURNER	turner	Turner
ADAMS	ADAMS	adams	Adams
JAMES	JAMES	james	James
FORD	FORD	ford	Ford
MILLER	MILLER	miller	Miller

문자열 길이

LENGTH
- 길이를 출력
- SELECT ENAME, LENGTH(ENAME) FROM EMP; 출력 결과 SMITH 5 ALLEN 5 WARD 4 JONES 5 MARTIN 6 BLAKE 5 CLARK 5 SCOTT 5 KING 4 TURNER 6 ADAMS 5 JAMES 5 FORD 4 MILLER 6
LENGTHB
- 바이트 길이 출력
- SELECT LENGTH('한글'), LENGTHB('한글') FROM DUAL; 출력 결과 LENGTH('한글') LENGTHB('한글') 2 6
MySql은 LENGTH가 오라클의 LENGTHB와 같다. (한글이 3byte)

DUAL 테이블

최고 권한 관리자 계정인 SYS의 소유 테이블로, 더미 테이블
주로 실험용, 값 확인 용도로 사용

문자열 추출

SUBSTR
- 문자열 일부 추출
- 시작 인덱스는 1이다.
- SUBSTR(문자열, 시작 위치, 추출 길이)
  - 문자열의 시작 위치부터 추출 길이까지 추출
  - 시작 위치가 음수일 경우 마지막 위치에서 거슬러 올라간 위치에서 시작
- SUBSTR(문자열, 시작 위치)
  - 시작 위치부터 끝까지 추출
  - 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출

SELECT JOB, SUBSTR(JOB,1,2), SUBSTR(JOB,3,2), SUBSTR(JOB,5)
FROM EMP;

출력 결과

CLERK	    CL	ER	K
SALESMAN	SA	LE	SMAN
SALESMAN	SA	LE	SMAN
MANAGER	  MA	NA	GER
SALESMAN	SA	LE	SMAN
MANAGER	  MA	NA	GER
MANAGER  	MA	NA	GER
ANALYST	  AN	AL	YST
PRESIDENT	PR	ES	IDENT
SALESMAN	SA	LE	SMAN
CLERK   	CL	ER	K
CLERK	    CL	ER	K
ANALYST 	AN	AL	YST
CLERK	    CL	ER	K

SELECT JOB,
       SUBSTR(JOB, -LENGTH(JOB)),
       SUBSTR(JOB, -LENGTH(JOB),2),
       SUBSTR(JOB, -3)
FROM EMP;

출력 결과

CLERK	     CLERK	  CL	ERK
SALESMAN	SALESMAN	SA	MAN
SALESMAN	SALESMAN	SA	MAN
MANAGER	   MANAGER	MA	GER
SALESMAN	SALESMAN	SA	MAN
MANAGER	   MANAGER	MA	GER
MANAGER	   MANAGER	MA	GER
ANALYST	   ANALYST	AN	YST
PRESIDENT	PRESIDENT	PR	ENT
SALESMAN	SALESMAN	SA	MAN
CLERK	     CLERK	  CL	ERK
CLERK	     CLERK	  CL	ERK
ANALYST	  ANALYST	  AN	YST
CLERK	     CLERK	  CL	ERK

특정 문자 위치를 찾는 함수와 같이 쓰인다

특정 위치 찾는 함수

INSTR

INSTR([대상 문자열 데이터(필수)]),
			[위치를 찾으려는 부분 문자(필수)],
		  [위치 찾기를 시작할 대상 문자열의 위치(선택, 기본 값 = 1)],
		  [시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본 값 = 1)]

SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1,
       INSTR('HELLO, ORACLE!', 'L',5) AS INSTR_2,
       INSTR('HELLO, ORACLE!', 'L',2,2) AS INSTR_3
  FROM EMP;
  
  
  
출력 결과
  
3	12	4

INSTR 함수로 사원 이름에 문자 S가 있는 행 구하기

SELECT *
  FROM EMP
WHERE INSTR(ENAME,'S') > 0

또는 LIKE 연산자 예제

SELECT *
FROM EMP
WHERE ENAME LIKE '%S%'

출력 결과

7369	SMITH	CLERK	7902	80/12/17	800		20
7566	JONES	MANAGER	7839	81/04/02	2975		20
7788	SCOTT	ANALYST	7566	87/04/19	3000		20
7876	ADAMS	CLERK	7788	87/05/23	1100		20
7900	JAMES	CLERK	7698	81/12/03	950		30

SQL 실행 시 에러

DBMS에서 응답하는 에러 메세지 확인
에러가 발생하는 위치 찾아 수정
재실행

메모

ORDER BY는 항상 끝에
from 안에 select 문 → 서브 쿼리
oder by, join 은 연산이 많아 느려질 수 있다.
SQL에서 문자열은 홑 따옴표(’’)를 쓴다.
집계 함수는 null연산을 제외한다.
심화 - 각 select문에 해당하는 것을 1,2로 매칭 후 1,2를 오름차순 정렬하고, 6번째 열인 sal을 그 안에서 오름차순 정렬

SELECT emp.*, 1
from emp
where deptno= 10
union
select emp.*, 2
from emp
where deptno = 20
order by 9, 6;

출력 결과

EMPNO ENAME    JOB    MGR   HIREDATE  SAL  COMM  DEPTNO
7934	MILLER	CLERK	7782	82/01/23	1300		10	1
7782	CLARK	 MANAGER	7839	81/06/09	2450		10	1
7839	KING	PRESIDENT		81/11/17	5000		10	1
7369	SMITH	  CLERK	7902	80/12/17	800		20	2
7876	ADAMS	  CLERK	7788	87/05/23	1100		20	2
7566	JONES	 MANAGER	7839	81/04/02	2975		20	2
7788	SCOTT	 ANALYST	7566	87/04/19	3000		20	2
7902	FORD	 ANALYST	7566	81/12/03	3000		20	2

SELECT 구문에서 문자 데이터 가공 함수를 쓸 때, 값 불일치가 나게 되면 오류가 난다.

예를 들어 SUM AVG와 같이 UPPER를 썼을 경우, SUM, AVG 같은 함수는 총합으로 1번만 실행하는 반면, 1행 씩 총 14번을 출력하는 UPPER함수는 오류가 남
ENAME은 14번 출력, SUM은 1번 출력 이므로

SELECT ENAME, UPPER(ENAME), SUM(SAL)
FROM EMP;

출력 결과

ORA-00937: not a single-group group function

<https://docs.oracle.com/error-help/db/ora-00937/00937>. 00000 -  "not a single-group group function"
*Cause:    A SELECT list cannot include both a group function,
           such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an
           individual column expression, unless the individual column
           expression was included in a GROUP BY clause.
*Action:   Drop either the group function or the individual
           column expression from the SELECT list or add a GROUP BY
           clause that includes all individual column expressions listed.
1행, 8열에서 오류 발생

GROUP BY 함수를 이용하여 해결 해야 함
오라클은 무존건 FROM을 써야함
- 쓸 것이 없을 때 DUAL을 쓰면 됨
중첩해서 함수를 쓸 경우 안쪽 우선

728x90

'Database' 카테고리의 다른 글

4/24 - DDL (0)	2025.04.27
4/23 - 조인, 서브 쿼리 (0)	2025.04.27
4/22 - 데이터베이스, 데이터 처리와 가공을 위한 오라클 함수, 다중행 함수와 데이터 그룹화 (0)	2025.04.27
4/18 - 데이터 베이스, 관계형 데이터 베이스 (0)	2025.04.19
4/17 - 데이터베이스 시작 (0)	2025.04.19

지원씨의 안드로이드 공부방

4/21 - SQL 데이터 정의어, 오라클 함수

SELECT

모델링

PK (Primary Key, 기본키)

FK (Foreign Key, 외래키)

식별, 비 식별 관계

별칭

ORDER BY(순서)

WHERE

연산자

IS NULL

집합 연산자

연산자 우선 순위

데이터 처리와 가공을 위한 오라클 함수

문자 데이터 가공 함수

문자열 길이

DUAL 테이블

문자열 추출

특정 위치 찾는 함수

SQL 실행 시 에러

메모

'Database' 카테고리의 다른 글

티스토리툴바

4/21 - SQL 데이터 정의어, 오라클 함수

SELECT

모델링

PK (Primary Key, 기본키)

FK (Foreign Key, 외래키)

식별, 비 식별 관계

별칭

ORDER BY(순서)

WHERE

연산자

IS NULL

집합 연산자

연산자 우선 순위

데이터 처리와 가공을 위한 오라클 함수

문자 데이터 가공 함수

문자열 길이

DUAL 테이블

문자열 추출

특정 위치 찾는 함수

SQL 실행 시 에러

메모

'Database' 카테고리의 다른 글

관련글

티스토리툴바