[ORACLE] 2일차 : SELECT 기본

 

SELECT문
테이블이나 뷰에 있는 데이터를 조회하여 0개 이상의 행을 반환한다.
SELECT는 가장 일반적으로 사용되는 DQL(data query language) 명령이며, DML(data manipulation language)의 구성 요소로 간주되기도 한다.

 SELECT문은 반드시 SELECT와 FROM의 두 개의 키워드로 구성된다.
SELECT절 다음에는 출력할 컬럼명이나 표현식을 기술하거나 특정 컬럼명 대신 '*' 기술하여 테이블(뷰) 내의 모든 컬럼 정보를 출력할 수 있다.
FROM절 다음에는 조회할 테이블(뷰)의 이름을 기술한다.

 

 

SELECT문 형식

• 테이블 목록확인 : SELECT * FROM tab; 

• 테이블의 컬럼 목록 확인 : SELECT * FROM col WHERE tname = 'EMP' / DESC emp;
• 특정 테이블만 확인 : SELECT empNO,name,sal FROM emp;
• 존재 하지 않는 컬럼 확인 : SELECT no, name, sal FROM emp;
• 모든 컬럼 출력 : SELECT * FROM emp;
• 컬럼명을 변경 하여 출력 :  SELECT empNO AS 사번, name AS 이름, sal AS 급여 FROM emp; / SELECT empNO 사번, name 이름, sal 급여 FROM emp; -- AS 생략 가능

 

SELECT문 구현 순서 및 실행 순서

 

 

1. FROM 절에서 테이블의 목록을 가져온다.
2. WHERE 절에서 검색 조건에 불일치 하는 행을 제외한다. 
3. ROWNUM을 사용한 경우 ROWNUM을 할당한다.
4. GROUP BY 절에서 명시된 행의 값을 그룹화한다.
5. HAVING 절에서 GROUP BY절의 결과 행 중 검색 조건에 불일치 하는 행을 제외한다.
6. SELECT 절에서 명시된 컬럼(표현식)을 처리한다.
7. ORDER BY 절에서 컬럼(표현식)을 기준으로 출력할 대상을 정렬 후 출력한다.

 

 WHERE 조건

선택한 행을 하나 이상의 조건을 만족하는 행으로 제한한다. WHERE절을 생략하면, FROM절의 테이블 또는 뷰에서 모든 행을 리턴 한다. 

조건식(Condition) : 조건 또는 조건식은 한 개 이상의 표현식과 논리 연산자가 결합된 식으로 TRUE, FALSE, UNKNOWN세 가지 타입을 반환한다.

 

SELECT 컬럼명, 컬럼명 FROM 테이블명 WHERE 조건;

 

비교 및 논리연산자

 

 

ANY(SOME), ALL

• ANY(SOME)
  • 값의 목록의 각 값과 비교하거나 쿼리에서 반환한다.(OR 개념과 유사)
  • =,!=,>,<,<=,>= 앞에 기술하며, 하나 이상의 값을 리턴하는 표현식 또는 서브 쿼리가 올 수 있다.
• ALL 
  • 값을 목록의 모든 값과 비교하거나 쿼리에서 반환한다.(AND 개념과 유사)
  • =,!=,>,<,<=,>= 앞에 기술하며, 하나 이상의 값을 리턴하는 표현식 또는 서브 쿼리가 올 수 있다.

emp 테이블 : city가 '서울', '경기', '인천' 인 자료 중 empNo, name, city 컬럼 출력
SELECT empNO,name,city
FROM emp
WHERE city= ANY('서울','경기','인천');

 

emp 테이블 : sal가 2000000원 이상인 자료 중 empNo, name, sal 컬럼 출력
SELECT empNO,name,city
FROM emp
WHERE sal >= 2000000;

 

 

SQL  연산자 

 

• BETWEEN 조건식
  • BETWEEN 조건은 한 식의 값이 다른 두 식으로 정의 된 간격에 있는지 여부를 결정한다.
  • 세가지 표현식은 모두 숫자, 문자 또는 날짜 / 시간 표현식이어야 한다.
  • 표현식이 모두 동일한 데이터 유형이 아닌 경우 암시적으로 표현식을 공통 데이터 유형으로 변환한다. 그렇게 할 수 없으면 오류를 반환한다.

emp 테이블 : sal가 2000000~3000000 사이 인 자료 중 name, sal 컬럼 출력
SELECT name,sal
FROM emp
WHERE sal BETWEEN 2000000 AND 3000000;

emp 테이블 : hireDate가 2022년도인 자료중 name, hireDate 컬럼 출력
SElECT name,hireDate
FROM emp
WHERE hireDate BETWEEN '2022-01-01' AND '2022-12-31'

• IN 조건식
  • list에 있는 값 중 하나라도 일치하면 TRUE다.

emp 테이블 : city가 '서울', '인천', '경기' 인 자료 중 name, city 컬럼 출력
SELECT empNO,city
FROM emp
WHERE city in('서울', '경기', '인천');

emp 테이블 : city와 pos가 '서울 이면서 '부장'이거나 '경기' 이면서 '과장' 인 자료 중 name, city, pos 컬럼 출력
SELECT empNO,city,pos
FROM emp
WHERE (city,pos)IN(('서울','부장'),('경기','과장'));

• LIKE 조건식
  • LIKE 조건은 패턴 일치와 관련된 조건을 지정한다.
  • 패턴 문자 
    • _ :  밑줄(_)은 값에서 정확히 하나의 문자
    • % : 퍼센트 부호(%)는 값에서 0 개 이상의 문자. null과는 일치 할 수 없다.
  • 문자열 처리시 오라클 내부 알고리즘상 LIKE조건식 보다는 INSTR함수가 더 빠르게 처리된다

emp 테이블 : LIKE 예
SELECT empNO, name
FROM emp
WHERE tel Like '%3%'; -- 3이 존재하는 전화번호

SELECT empNO, name
FROM emp
WHERE name LIKE '%3'; -- 3으로 끝나는 전화번호

SELECT empNO, name, rrn
FROM emp
WHERE rrn LIKE '_0%'; -- 생년월의의 연도가 10의 배수

 

 

NULL

행의 열에 값이 없으면 열은  null이거나  null을 포함한다고 한다

NOT NULL 또는 PRIMARY KEY  무결성 제약 조건에 의해 제한되지 않는 모든 데이터 유형의 열에는 널이 나타날 수 있다.

실제 값을 알 수 없거나 값이 의미가 없는 경우 널을 사용한다. 길이가 0인 문자 값은 널로 처리한다. 0이 아닌 숫자 값을 나타내래면 null을 사용하지 않는다 

 

SELECT 10+NULL FROM dual; -- NULL

 

emp 테이블 : tel이 NULL 인 자료 중 name, tel 컬럼 출력
SELECT name,tel
FROM emp
WHERE tel is NULL;

 

CASE표현식

 

IF ~ THEN ~ELSE 논리와 유사한 방식으로 표현식을 작성해서  SQL의 비교 연산 기능을 보완하는 역할을 한다. 

 

• Simple Case Expression (간단한 CASE 표현식)

형식1 : 간단한 CASE 표현식
SELECT name,rrn, CASE SUBSTR(rrn,8,1)
WHEN '1' THEN '남자'
WHEN '2' THEN '여자'
WHEN '3' THEN '남자'
WHEN '4' THEN '여자'
END "성별"
FROM emp;

 

SELECT name,rrn, CASE MOD(SUBSTR(rrn,8,1),2)
WHEN 0 THEN '여자'
WHEN 1 THEN '남자'
END "성별"
FROM emp;

 

• Searched Case Expression

emp : name,sal+bonus, 세금(sal+bonus >= 300만이면 3%, sal+bonus>=200만 2%, 나머지 0)
SELECT name,sal+bonus 총급여,
CASE
WHEN sal+bonus >= 3000000 THEN (sal+bonus) * 0.03
WHEN sal+bonus >= 2000000 THEN (sal+bonus) * 0.02
ELSE 0
END AS 세금
FROM emp;

 

SELECT name,sal+bonus 총급여,
CASE
WHEN sal+bonus >= 3000000 THEN 0.03
WHEN sal+bonus >= 2000000 THEN 0.02
ELSE 0 END *(sal+bonus) AS 세금
FROM emp;

 

 

DECODE 함수

값을 각 검색과 비교하여 같으면 결과를 반환
DECODE (a,'b',1,2) -- a가 'b'이면 1을 반환하고 그렇지 않으면 2를 반환
DECODE (a,'b',1) -- a가 'b'이면 1을 반환하고 그렇지 않으면 NULL을 반환 
DECODE (a,'b',1,'c',2,3) -- a가 'b'이면 1을 반환하고, a가 'c'이면 2를 반환하며 그렇지 않으면 3을 반환

 

SELECT name,rrn,
DECODE(MOD(SUBSTR(rrn,8,1),2),1,'남자','여자')성별
FROM emp; -- 주민번호 8번째가 홀수면 남자, 그렇지 않으면 여자 가장 간단한 방법

 

 

ALL 과 DISTNCT 

ALL 옵션

선택된 모든 행을 반환하는 옵션으로 기본 값은 ALL.

SELECT All dept FROM emp;
SELECT dept FROM emp;

 

DISTINCT , UNIQUE 옵션

선택된 행 중에서 중복적인 행은 하나만 반환하기 위해 DISTINCT를 사용한다. 중복 행은 SELECT 목록의 각 표현식에 대해 일치하는 값을 갖는 행이다. DISTINCT는 UNIQUE와 동일하므로 어느 것을 사용해도 상관 없다.

 

SELECT DISTINCT dept FROM emp; -- 표준
SELECT UNIQUE dept FROM emp;

 

 

ORDER BY절

SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 컬럼을 기준으로 정렬하여 출력하는데 사용한다. ORDER BY절에 컬럼(column)명 대신에 SELECT 절에서 사용한 ALIAS명이나 컬럼 순서를 나타내는 정수도 사용 가능하다. 

 

SELECT name,dept,sal
FROM emp
ORDER BY sal asc; -- sal 오름차순 생략가능

SELECT name,dept,sal
FROM emp
ORDER BY sal DESC; --sal 내림차순

 

 

집합 연산자

두 개의 구성 요소 쿼리 결과를 단일 결과로 결합한다. 집합 연산자를 포함하는 쿼리를 복합 쿼리라고 한다.

 

UNIONS(합집합)

첫 번째 SQL문의 결과와 두 번째 SQL문의 결과 중 중복된 데이터를 제거한다.

SELECT name, dept, pos, city FROM emp WHERE city = '서울'
UNION
SELECT name, dept, pos, city FROM emp WHERE city = '개발부';

 

UNION ALL

첫 번쨰 SQL문의 결과와 두 번째 SQL문의 결과를 모두 출력한다.

SELECT name, dept, pos, city FROM emp WHERE city = '서울'
UNION ALL
SELECT name, dept, pos, city FROM emp WHERE dept = '개발부';

 

 

MINUS (차집합)

첫 번째 SQL문 결과에는 있고, 두 번째 SQL문의 결과에는 없는 데이터를 출력한다.

SELECT name, dept, pos, city FROM emp WHERE city = '서울'
MINUS
SELECT name, dept, pos, city FROM emp WHERE dept = '개발부';

 

INTERSECT(교집합)

인터섹트는 두번째 SQL문의 결과와 첫번째 SQL문의 결과에 중복된 행만 출력한다.

SELECT name, dept, pos, city FROM emp WHERE city = '서울'
INTERSECT
SELECT name, dept, pos, city FROM emp WHERE dept = '개발부';

 

 

pseudo 컬럼

오라클에서 내부적으로 사용되는 컬럼을 수도우 컬럼이라고 하며, ROWID, UROWID,ROWNUM등이 있다. 테이블 열처럼 동작하지만 실제로 테이블에는 저장되지 않는다. pseudo 컬럼중에서 선택할 수 있지만, 삽입,수정,삭제 할 수 없다. pseudo컬럼은 인수가 없는 함수와 비슷하다. 인수가 없는 함수는 일반적으로 결과 집합의 모든 행에 동일한 값을 반환하지만 pseudo 컬럼은 각 행에 대해 다른 값을 반환한다. 

 

ROWID

데이터베이스의 각 행에 대해 ROWID는 행의 주소를 반환한다. ROWID 값에는 행을 찾는데 필요한 정보가 포함된다. 일반적으로 ROWID 값은 데이터베이스에서 행을 고유하게 식별한다. 그러나 동일한 클러스터에 함께 저장된 다른 테이블의 행은 동일한 ROWID를 가질수 있다. ROWID을 이용하여 행수를 제한 할 수 있다. 

 

SELECT ROWID, name,dept FROM emp;

 

 

 

ROWNUM

쿼리에서 반환 된 각 행에 대해 ROWNUM은 테이블 또는 조인된 행 집합에서 행을 선택하는 순서를 나타내는 숫자를 반환한다. 즉, 선택된 첫 번째 행의 ROWNUM 은 1이고 두번째 행은 2이다. 

SELECT * FROM emp WHERE ROWNUM <11; -- 양수보다 큰 ROWNUM 값에 대한 조건 테스트는 항상 거짓 SELECT * FROM emp WHERE ROWNUM >1; -- 거짓, 아무것도 출력안됨 1보다 큰수와 ROWNUM에 대한 동등 비교는 항상 거짓
SELECT * FROM emp WHERE ROWNUM =10; -- 거짓, 아무것도 출력안됨 1과 ROWNUM에 대한 동등 비교는 가능
SELECT * FROM emp WHERE ROWNUM =1; -- 거짓, 아무것도 출력안됨  ORDER BY이 있는 경우 ROWNUM과 비교해서는 안된다

 

 

SUB QUERY 사용

SELECT * FROM( -- sal 오름차순 정렬하여 앞에서 10개만 출력
SELECT * FROM emp
ORDER By sal
) WHERE ROWNUM <11;

 

 페이징 처리에서 자주 사용

SELECT * FROM(
SELECT ROWNUM rnum,tb.*FROM(
SELECT empNO, name, dept, sal
FROM emp
ORDER BY sal DESC
) tb WHERE ROWNUM <= 30
)WHERE rnum >=21;