[Oracle] SQL명령 / SELECT / 문자함수

워크시트에서 작성된 SQL 명령을 접속된 오라클 서버에 전달하여 실행하는 방법

• [CTRL]+[ENTER] : 커서 위치의 SQL 명령을 전달하여 실행
• [F5] : 워크시트에 작성된 모든 SQL 명령을 전달하여 실행 
• 범위를 지정하여 [CTRL]+[ENTER] 또는 [F5]를 사용하면 범위 내부의 SQL 명령을 전달하여 실행할 수 있다. 
• 워크시트에 작성되어 실행된 SQL 명령의 결과는 [스크립트 출력] 또는 [질의 결과] 탭에 출력된다.

SQL 명령은 대소문자를 구분하지 않으며 하나의 명령으로 처리되도록 ; 기호를 사용한다.

 

SHOW USER; : 현재 서버에 접속된 사용자정보를 확인하기 위한 명령

 

테이블(TABLE) : 데이터베이스에서 데이터(행)을 저장하기 위한 기본 객체 

 

---

현재 접속 사용자(SCOTT)가 접근 가능한 스키마에 존재하는 테이블 목록을 확인할 때 쓰는 명령

SELECT TABLE_NAME FROM TABS;

SELECT * FROM TAB;

테이블의 속성(구조) 확인 : 컬럼명과 자료형 - 테이블 속성

형식) DESC 테이블명;

예시.

DESC EMP; ()

DEPT : 부서정보를 저장하기 위한 테이블

DESC DEPT;

---

DQL(DATA QUERY LANGUAGE) : 데이터 질의어 – 테이블에 저장된 행을 검색하기 위한 SQL 명령

SELECT : 하나 이상의 테이블에서 행을 검색하기 위한 명령 

형식) SELECT 검색대상, 검색대상, … FROM 테이블명

• 하나의 테이블에 저장된 모든 행을 검색하기 위한 SELECT 명령이다. 
• 검색대상: * (모든 컬럼) – 다른 검색대상과 동시에 사용 불가능, 컬럼명, 연산식, 함수 등 

SELECT * FROM EMP;

SELECT EMPNO,ENAME,SAL FROM EMP; --> 특정 컬럼만 검색

 

 

COLUMN ALIAS: 검색대상에 별칭(임시 컬럼명)을 부여하는 기능

• 검색대상을 명확하게 구분하기 위해 COLUMN ALIAS 기능을 사용한다.
• 특히 테이블에 있는 행들을 연결시킬 때(조인) 많이 사용한다. --> 컬럼명이 중복될 때, 어떤 테이블의 컬럼인지 구분해야 하기 때문

형식) SELECT 검색대상 [AS] 별칭, 검색대상 [AS] 별칭,... FROM 테이블명 -> AS는 생략 가능

SELECT EMPNO AS NO, ENAME AS NAME, DEPTNO AS DNO FROM EMP; 

SELECT EMPNO NO, ENAME NAME, DEPTNO DNO FROM EMP;    (AS 생략 가능) 

>> 컬럼명이 ALIAS 네임으로 변경된다 

 

 

ALIAS NAME은 한글을 써도 상관 없다

검색대상으로 컬럼값을 이용한 연산식 사용이 가능하다.

 

SELECT EMPNO, ENAME, SAL*12 FROM EMP;  -->컬럼값을 뽑아낼 수 없다

 

 

SELECT EMPNO, ENAME, SAL*12 ANNUAL FROM EMP;

 

 >> 이처럼 연산식, 함수..등의 경우, ALIAS 사용해 검색대상을 명확하게 구분한다.

 

 

SQL 명령은 예약어(키워드)와 사용자 정의 명칭(식별자)의 단어로 구성된다.

• 식별자(테이블명, 컬럼명, 별칭 등)은 스네이크 표기법(단어와 단어를 [_]로 구분하여 표현)을 사용하여 작성한다. 

 

 

검색대상의 별칭으로 공백 또는 특수문자 사용이 불가능하다.

 

하지만 검색대상의 별칭을 " " 기호 안에 표현하면 모든 형태의 별칭 표현이 가능하다.

>> " " 기호는 검색대상의 별칭을 표현하기 위해 사용하는 특수문자이다.

 

 

 

파이프 기호(||) : 문자값 결합 기호

검색대상은 , 기호를 사용하여 나열 작성이 가능하다.

SELECT ENAME,JOB FROM EMP;

 

 

검색대상의 ||(파이프) 기호를 사용하여 문자값으로 결합하여 검색이 가능하다.

 

SELECT ENAME||JOB FROM EMP;

 

 

SQL에서 문자값은 ' ' 기호를 사용하여 표현한다.

SELECT ENAME||'님의 업무는 '||JOB||'입니다.' FROM EMP;

 

 

DISTINCT : 검색대상의 중복값을 제외하고 유일한 하나의 컬럼값만 검색하는 기능을 제공하는 키워드

 

EMP 테이블에 저장된 모든 사원의 업무를 검색하면, 중복 컬럼값이 발생될 수 있다.

DISTINCT 키워드를 사용하면 중복 컬럼값을 제외한 유일한 하나의 컬럼값만 검색할 수 있다.

오라클은 DISTINCT 키워드에 검색대상을 여러개 나열하여 작성이 가능하다.

SELECT DISTINCT JOB, DEPTNO FROM EMP;

 

>> JOB(업무), DEPTNO(부서번호)가 같은 행은 검색되지 않는다.

 

 

WHERE : 조건식을 사용하여 조건이 참(TRUE)인 행만 검색하는 기능을 제공한다.

형식) SELECT 검색대상,검색대상... FROM 테이블명 WHERE 조건식

조건식은 일반적으로 컬럼값을 비교하는 연산식이다. 

 

 

EMP 테이블에서 사원번호가 7698인 사원의 사원번호,사원이름,업무,급여 검색

 

EMP 테이블에서 사원이름이 KING인 사원의 사원번호,사원이름,업무,급여 검색

SQL 명령은 대소문자를 구분하지 않지만 문자값은 대소문자를 구분한다.

>> 결과값이 나오지 않음

 

EMP 테이블에서 입사일이 1981년 6월 9일인 사원의 사원번호,사원이름,업무,급여,입사일 검색

>> 날짜값은 문자값처럼 ' ' 안에 [RR/MM/DD] 형식의 패턴으로 표현하여 사용한다. (RR - 두 자리 년도) 

날짜형의 기본 패턴 : [RR/MM/DD]

 

[YYYY-MM-DD] 형식의 패턴으로 표현하여 사용하는 것도 가능하다.

 

<> 기호

 

 

EMP 테이블에서 업무가 SALESMAN이 아닌 사원의 사원번호,사원이름,업무,급여 검색

오라클에서는 <> 기호를 사용한다.

 

표준 SQL에서는 !=를 사용한다. (오라클에서도 != 기호를 허용한다.)

실행 결과

EMP 테이블에서 급여가 2000 이상인 사원의 사원번호,사원이름,업무,급여 검색

 

문자값도 숫자값처럼 비교가 가능하다.

 

EMP 테이블에서 사원이름 A,B,C로 시작되는 사원의 사원번호,사원이름,업무,급여 검색 

EMP 테이블에서 1981년 5월 1일 전에 입사한 사원의 사원번호,사원이름,업무,급여,입사일 검색

 

조건이 여러개인 경우, AND와 OR 이용해 연결해 검색

 

EMP 테이블에서 업무가 SALESMAN인 사원 중 급여가 1500 이상인 사원의 사원번호,사원이름,업무,급여 검색

SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB = 'SALESMAN' AND SAL>=1500;

EMP 테이블에서 부서번호가 10이거나 업무가 MANAGER인 사원의 사원번호,사원이름,업무,급여,부서번호 검색

EMP 테이블에서 급여가 1000~3000 범위에 포함되는 사원의 사원번호,사원이름,업무,급여 검색

 

범위를 표현한 조건식을 이용하여 컬럼값이 작은값부터 큰값 범위에 포함될 경우의 행만 검색 가능 

형식) 컬럼명 BETWEEN 작은값 AND 큰값

 

선택을 표현한 조건식을 이용하여 컬럼값이 나열된 값들중 하나에만 포함되어도 행을 검색

 

형식) 컬럼명 IN (값,값,...)

EMP 테이블에서 업무가 ANALYST이거나 SALESMAN인 사원의 사원번호,사원이름,업무,급여 검색

EMP 테이블에서 사원이름이 A로 시작되는 사원의 사원번호,사원이름,업무,급여 검색

검색문자를 이용하여 컬럼값 검색 가능하다.

• 검색문자 : [%] - 전체, [_] - 문자 하나
• [=] 연산자로 컬럼값을 비교할 경우 검색문자가 아닌 일반문자로 검색된다.

→ 검색문자를 이용하여 컬럼값을 비교 검색할 경우 [=] 연산자 대신 LIKE 키워드를 사용한다.

 

 

= 와 LIKE는 일반문자로 사용할 것인지, 검색문자로 사용할 것인지의 차이밖에 없다.

따라서 검색문자를 사용하지 않아도 [=] 연산자 대신 LIKE 키워드 사용이 가능하다.

하지만 LIKE는 %나 _를 일반문자가 아닌 검색문자로 사용하고 싶을 때 사용하는 것을 권장한다

 

 

EMP 테이블에서 사원이름이 A가 포함되는 사원의 사원번호,사원이름,업무,급여 검색

EMP 테이블에서 사원이름의 두번째 문자가 L인 사원의 사원번호,사원이름,업무,급여 검색

 

ESCAPE 키워드

: 지정된 문자를 사용하여 문자값으로 표현 불가능한 문자를 회피문자(ESCAPE CHARACTER)로 표현하여 일반문자로 처리하여 검색이 가능하다.

 

 

EMP 테이블에서 사원이름에 [_] 라는 문자가 포함된 사원의 사원번호, 사원이름, 업무, 급여 검색

 

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE ENAME LIKE '%_%';

>> 컬럼값을 LIKE 키워드로 비교할 경우 [%]문자와 [_]문자는 검색문자로 처리되어 검색되어 오류 발생 

 

>> ESCAPE키워드로 지정된 문자를 사용해 문자를 회피문자로 표현하여 일반문자로 처리하여 검색

 

 

NOT 키워드

: 조건식의 결과가 참(TRUE)이 아닌 거짓(FALSE)인 행만 검색 

형식)NOT(조건식)

EMP 테이블에서 업무가 MANAGER가 아닌 사원의 사원번호,사원이름,업무,급여 검색

 

 

NULL : 값이 존재하지 않는 것을 표현하기 위한 키워드

 

EMP 테이블에서 성과급이 없는 사원의 사원번호,사원이름,업무,급여,성과급 검색

>> NULL은 값이 아니므로 비교 불가능 – 검색오류

 

IS 키워드를 사용하여 NULL을 구분하는 조건식 사용이 가능하다.

형식) 컬럼명 IS NULL 또는 컬럼명 IS NOT NULL

EMP 테이블에서 성과급이 존재하는 사원의 사원번호,사원이름,업무,급여,성과급 검색

ORDER BY : 컬럼값을 비교하여 행이 정렬되도록 검색하는 기능을 제공 

형식) SELECT 검색대상,검색대상,...FROM 테이블명 [WHERE 조건식]
     ORDER BY {컬럼명|연산식|별칭|INDEX} {ASC|DESC}, {컬럼명|연산식|별칭|INDEX} {ASC|DESC},...

• ASC : 오름차순 정렬, DESC : 내림차순 정렬 
• 정렬 컬럼값이 같은 경우 다른 컬럼값을 비교하여 정렬되도록 설정을 나열하여 작성 가능

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호 검색

>> 기본적으로 테이블에 행이 삽입된 순서대로 검색된다.

 

 

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호를 부서번호로 오름차순 정렬하여 검색

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY DEPTNO ASC;

 

>> 오름차순 정렬인 경우 ASC 키워드 생략 가능하다

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY DEPTNO;

 

 

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호를 급여로 내림차순 정렬하여 검색

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY SAL DESC;

 

 

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호를 사원이름으로 오름차순 정렬하여 검색

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM EMP ORDER BY ENAME;

 

 

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,입사일,부서번호를 입사일로 내림차순 정렬하여 검색

SELECT EMPNO,ENAME,JOB,SAL,HIREDATE,DEPTNO FROM EMP ORDER BY HIREDATE DESC;

 

 

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,연봉(급여*12)을 연봉으로 내림차순 정렬하여 검색

>> ORDER BY 뒤에 컬럼명 대신 연산식 사용 가능

SELECT EMPNO,ENAME,SAL*12 ANNUAL FROM EMP ORDER BY SAL*12 DESC;  --> 연산식을 사용하여 정렬

SELECT EMPNO,ENAME,SAL*12 ANNUAL FROM EMP ORDER BY ANNUAL DESC; --> 별칭을 사용하여 정렬

 

SELECT 명령의 검색대상에는 자동으로 첨자(INDEX : 위치를 표현하는 숫자값)가 부여된다. - COLUMN_INDEX

• 오라클에서 첨자는 1부터 1씩 증가되는 숫자값으로 표현 

SELECT EMPNO,ENAME,SAL*12 ANNUAL FROM EMP ORDER BY 3 DESC;  --> 컬럼첨자를 사용하여 정렬

 

실행결과

 

EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,업무,급여,부서번호를 부서번호로 오름차순 정렬하여 검색하고 부서번호가 같은 사원은 급여로 내림차순 정렬하여 검색

EMP 테이블에서 급여가 3000 이상인 사원의 사원번호,사원이름,업무,급여,부서번호를 부서번호로 오름차순 정렬하여 검색

---

함수(FUCTION)

함수(FUCTION) : 매개변수로 전달받은 값을 가공하여 처리 결과값을 반환하는 기능을 제공

• 단일함수 : 하나의 값을 전달받아 가공하여 결과값을 반환하는 함수 - 문자함수,숫자함수,날짜함수,변환함수,일반함수
• 그룹함수 : 다수의 값을 전달받아 가공하여 결과값을 반환하는 함수 
• 문자함수 : 매개변수로 문자값을 전달받아 가공하여 결과값을 반환하는 함수 

문자함수

• UPPER(문자값) : 문자값을 전달받아 대문자로 변환하여 반환하는 함수
• LOWER(문자값) : 문자값을 전달받아 소문자로 변환하여 반환하는 함수

 

문자값은 대소문자를 구분하여 비교한다.

SELECT EMPNO,ENAME,SAL FROM EMP WHERE ENAME = 'smith';  --> 검색오류

 

UPPER 함수 또는 LOWER 함수를 사용하여 대소문자를 구분하지 않고 비교할 경우 사용

INITCAP(문자값)

:문자값을 전달받아 첫번째 문자만 대문자로 변환하고 나머지 문자는 소문자로 변환하여 반환하는 함수

CONCAT(문자값,문자값)

: 두 개의 문자값을 전달받아 결합하여 반환하는 함수 - [||] 기호를 사용하는 것과 유사

 

SUBSTR(문자값,시작위치,갯수) 

: 문자값을 전달받아 시작위치(INDEX)부터 갯수만큼의 문자들을 분리하여 반환하는 함수

LENGTH(문자값)

: 문자값을 전달받아 문자 갯수를 반환하는 함수

INSTR(문자값,검색문자값,시작첨자,검색위치)

: 문자값을 전달받아 검색문자값을 시작위치(첨자)부터 검색하여 원하는 위치의 문자값의 시작첨자를 반환하는 함수

LPAD(문자값,자릿수,채울문자)

: 문자값을 전달받아 자릿수의 길이만큼 오른쪽부터 채우고 왼쪽 남는 자리에는 채울문자로 채운 결과를 반환하는 함수 

RPAD(문자값,자릿수,채울문자)

: 문자값을 전달받아 자릿수의 길이만큼 왼쪽부터 채우고 오른쪽 남는 자리에는 채울문자로 채운 결과를 반환하는 함수

 

TRIM({LEADING|TRAILING} 제거문자 FROM 문자값)

: 문자값을 전달받아 앞(LEADING) 또는 뒤(TRAILING)에 존재하는 제거문자들을 삭제하고 반환하는 함수

REPLACE(문자값,검색문자값,치환문자값)

: 문자값을 전달받아 검색문자값을 찾아 치환문자값으로 변환하여 반환하는 함수