[Oracle] SELECT문

# 기본 SELECT문 동작

조회

SELECT empno, ename, sal  
  FROM emp; 


SELECT *  
  FROM emp;

 

사칙연산

일부 컬럼만 읽고자할 때도 데이터를 읽을 때는 행 전체를 읽어온 뒤 출력 형태를 변경

SELECT empno, ename, sal, sal + 100, sal - 100, sal * 10, sal / 10 
  FROM emp;

 

NULL

NULL 값을 포함한 연산의 경우 결과값 또한 NULL 

SELECT empno, ename, sal, comm, sal + comm 
  FROM emp;

  

NULL 값이 아닌 경우에만 산술연산 수행하도록 작성 

SELECT empno, ename, sal, comm, NVL(sal, sal + comm) AS TOTAL_SAL 
  FROM emp;

   

컬럼 별칭

SELECT ename AS name, sal as Salary, sal * 12 AS "Annual Salary" 
  FROM emp;

  

특수문자, 공백등을 사용하고자 할 때는 ""로 묶어야 함 

SELECT ename AS "사원명", sal as "월 급여", sal * 12 AS "총 급여" 
  FROM emp;

   

Literal 문자열(Literal값)

문자, 날짜의 리터럴값은 ''로 묶어야 함 

SELECT empno, ename, sal, comm, 500, 'Hello' 
  FROM emp; 

SELECT empno, ename, sal, comm, to_date('2023-08-28'), to_date('20230828'), to_date('2023/08/28')
  FROM emp; 

SELECT empno, ename, sal, comm, 500 "결과1", 'Hello' "결과2"  
  FROM emp;

 

연결연산자 

SELECT ename||', '||job||', '||empno "이름, 직무, 사번" 
  FROM emp;

   

''와 ""의 용도는 혼용되지 않음 

SELECT ename||'의 직무는 '||job||'이다.' "문장" 
  FROM emp;

   

null값의 경우 결과는 문자열(null값은 산술연산에만 영향을 줌) 

SELECT ename, job, comm, ename||'-'||comm||'-'||job "null 확인" 
  FROM emp;

   

중복행 제거 

SELECT deptno, job  
  FROM emp 
  ORDER BY deptno, job; 

SELECT DISTINCT(deptno)  
  FROM emp 
  ORDER BY deptno;

  

distinct 다음에 여러 컬럼을 지정한 경우 모든 컬럼에 영향 

SELECT DISTINCT deptno, job 
  FROM emp 
  ORDER BY deptno;

  

의사열 

pseudo column(허수의 컬럼): 성능과 매우 밀접한 연관 존재 

SELECT ROWNUM, ROWID, empno, ename, deptno, sal 
  FROM emp;

 

# 행의 제한 (WHERE)  

1. 산술 연산자

SELECT sal, sal * 10 
  FROM emp 
  WHERE sal * 10 >= 20000; -- 순서를 바꿔써도 무방

  

2. 비교연산자

SELECT DISTINCT deptno, COUNT(deptno) 
  FROM emp 
  WHERE deptno <> 30 
  GROUP BY deptno 
  ORDER BY deptno;

  

3. 컬럼, 숫자나 문자상수

SELECT * 
  FROM emp 
  WHERE ename = 'JONES';

  

4. BETWEEN, IN, LIKE

BETWEEN : 순서 중요 

SELECT empno, ename, deptno, sal 

  FROM emp 

  WHERE sal BETWEEN 2500 and 3000; -- sal >= 3000 and AND <= 2500;

  

IN 

SELECT empno, ename 

  FROM emp 

  WHERE empno IN (7788, 7566, 7902);

      

LIKE : 문자열 비교에서만 사용  

•  % : 글자 수와 상관 없이 일치하는지 여부 
•  _ : 한 글자와 대응 

SELECT ename, job 
  FROM emp 
  WHERE JOB LIKE '%ER'; 
   
SELECT ename 
  FROM emp 
  WHERE ename LIKE 'S%'; 

SELECT ename 
  FROM emp 
  WHERE ename LIKE '%LL%'; 

SELECT ename 
  FROM emp 
  WHERE ename LIKE '_C%'; 
   
SELECT ename 
  FROM emp 
  WHERE ename LIKE '_____N'; 

SELECT ename 
  FROM emp 
  WHERE ename LIKE '%N';

   

ESCAPE 구분자로 #를 사용하겠다는 의미 

SELECT *  
  FROM emp_mast 
  WHERE job LIKE '%#%%' ESCAPE '#';

   

5. IS NULL, IS NOT NULL

SELECT ename, comm 
  FROM emp 
  WHERE comm IS NOT NULL; 

SELECT ename, comm 
  FROM emp 
  WHERE comm IS NULL;

 

6. 논리연산자 (AND, OR, NOT)

NOT : 부정형  

SELECT empno, deptno 
  FROM emp 
  -- 비교 연산의 경우!=, ^=, <> 또한 사용 가능 
  WHERE NOT deptno <= 10;
  
SELECT ename, sal 
  FROM emp 
  -- WHERE NOT sal BETWEEN 2500 and 3000도 가능; 
  WHERE sal NOT BETWEEN 2500 AND 3000;

 

OR : 예전에는 IN보다 성능이 좋지 않았으나 현재는 내부적으로 IN으로 변경하여 처리하므로 성능 동일 

SELECT empno, ename 
  FROM emp 
  -- 조건이 여러번 연결될 경우 매번 컬럼명을 써주어야함 
  WHERE empno = 7788 
     OR empno = 7566 
     OR empno = 7902;

 

우선순위 : AND와 OR가 동시에 쓰인 조건 절의 경우 AND를 먼저 수행한 결과에서 OR 연산 수행 

SELECT empno, ename, job, sal 
  FROM emp 
  WHERE sal > 1500 AND job = 'PRESIDENT' OR job = 'SALESMAN'; 

SELECT empno, ename, job, sal 
  FROM emp 
  WHERE sal > 1500 AND (job = 'PRESIDENT' OR job = 'SALESMAN'); 

SELECT empno, ename, job, sal 
  FROM emp 
  WHERE sal > 1500 AND job IN('PRESIDENT', 'SALESMAN');

 

# 데이터 정렬 

컬럼명, ALIAS명, 순서 나타내는 정수 가능하며 항상 SELECT문의 마지막에 위치

SELECT * 
  FROM emp 
  ORDER BY 6 DESC;   

SELECT empno, ename, deptno 부서번호, sal AS 급여, comm 
  FROM emp 
  ORDER BY 부서번호;

NULL값은 오름차순일 때 마지막, 내림차순일 때는 제일 처음 

NULLS FIRST, NULLS LAST로 순서 변경 가능하나 동시 사용은 불가

SELECT empno, ename, deptno 부서번호, sal AS 급여, comm, sal * 12 ANN_SAL 
  FROM emp 
  ORDER BY comm NULLS FIRST, ANN_SAL ASC;

DISTINCT를사용하지 않을 경우 SELECT절에 사용하지 않은 컬럼 기준으로 정렬 가능

DISTINCT를사용했다면 ORDER BY절 컬럼은 SELECT절에서 사용한 컬럼만 사용 가능

SELECT DISTINCT ename, comm 
  FROM emp 
  ORDER BY comm; 

SELECT DISTINCT job, sal + comm 
  FROM emp 
--  ORDER BY sal; -- 실행 불가 
  ORDER BY 2;

여러 정렬을 혼용하여 사용 가능하며 작성한 순서대로 정렬 적용

SELECT ename, sal 
  FROM emp 
  ORDER BY sal DESC, ename ASC;

 

# 연습문제   

사원의 이름의 세번째 위치하고 있는 문자값이 'A'이거나 'R'인 사원의 이름, 입사일자, 부서, 급여를 출력하는 SQL 문장을 작성합니다. 입사일자 및 급여를 기준으로 내림차순으로 데이터를 출력합니다.

SELECT ename, hiredate, deptno, sal 
  FROM emp 
  WHERE ename LIKE '__A%'  
     OR ename LIKE '__R%' 
  ORDER BY hiredate DESC, sal DESC;