DAY14 - SQL문법(SELECT)

📌 1. SELECT 문법

• SELECT문은 DB내 정보를 검색하는 데 사용되는 문법이다.
  • SELECT문 기본 형태 예시

    • SELECT EMPLOYEE_ID, LAST_NAME // 가져올 열 (* = 전부)
      FROM EMPLOYEES // 열을 가져올 테이블
      WHERE LAST_NAME = 'OSTIN'; // 가져올 조건(생략가능)

📍 1-1. SELECT 조건 기능

• AND

  • 2개 이상의 조건을 설정할 때 사용한다.

  	SELECT	EMPLOYEE_ID,	FIRST_NAME,
  			EMAIL,			HIRE_DATE,
  			JOB_ID,			LAST_NAME 
  	FROM	EMPLOYEES e 
  	WHERE 	LAST_NAME = 'Smith'
  	AND		FIRST_NAME = 'William'
  	;

• OR

  • 검색 조건에 'A 또는 B'를 적용하고 싶을 때 사용한다.

  	SELECT	EMPLOYEE_ID ,	DEPARTMENT_ID 
  		,	LAST_NAME 	,	MANAGER_ID 
  	FROM 	EMPLOYEES e 
  	WHERE 	DEPARTMENT_ID = 50
  	OR 		MANAGER_ID =100
  	;

• NOT,<> , !=

  • 부정 조건을 표현할 수 있다.

  	SELECT 	FIRST_NAME
  		,	LAST_NAME 	,	DEPARTMENT_ID 
  	FROM 	EMPLOYEES e 
  	WHERE 	NOT (DEPARTMENT_ID = 50)	// 부정 1
      WHERE 	DEPARTMENT_ID <> 50			// 부정 2
      WHERE 	DEPARTMENT_ID != 50			// 부정 3
      // 부정문 3 가지 모두 동일한 의미이다.
  	;

• between A and B

  • A와 B 사이에 있는 값을 검색한다.

  	SELECT 	FIRST_NAME 
  		,	LAST_NAME 	,	SALARY 
  	FROM 	EMPLOYEES e 
  	WHERE 	SALARY BETWEEN 4000 AND 8000
  	;

• in

  • 한 열 안에서 OR로 값을 찾고 싶을 때 나열식으로 사용할 수 있다.

  	SELECT 	*
  	FROM 	EMPLOYEES e 
  	WHERE 	SALARY IN (6500,7700,13000)
  	;

• like

  • 지정한 형식을 만족하는 값을 찾아 준다.

  	// d로 끝나는 first name을 갖는 값을 찾는다
  	SELECT 	FIRST_NAME 	,	LAST_NAME 
  		,	EMPLOYEE_ID 
  	FROM 	EMPLOYEES e 
  	WHERE 	FIRST_NAME LIKE '%d'
  	;
  	----------------------------------------------------------------
  	// 특정 자리수를 지정할 때에는 자리수만큼 언더바(_)를 사용한다
      // first name 세번째 글자로 a가 오는 값을 찾는다
  	SELECT 	FIRST_NAME 	,	LAST_NAME 
  		,	EMPLOYEE_ID 
  	FROM 	EMPLOYEES e 
  	WHERE 	FIRST_NAME LIKE '__a%'
  	;   

• is (not) null

  • null 값을 찾아 준다. not을 사용하면 null이 아닌 값을 찾는다.

  	SELECT 	FIRST_NAME 	,	LAST_NAME 
  		,	EMPLOYEE_ID ,	COMMISSION_PCT 
  	FROM 	EMPLOYEES e 
  	WHERE 	COMMISSION_PCT IS NOT NULL 
  	;

• order by

  • 명령어 뒤에 명시한 열 값을 기준으로 ASC를 붙이면 오름차순, DESC를 붙이면 내림차순으로 결과를 정렬한다. 오름차순은 생략할 수도 있다.

  	// first name 기준 내림차순으로 정렬
  	SELECT 	FIRST_NAME 	,	LAST_NAME 
  		,	EMPLOYEE_ID ,	COMMISSION_PCT 
  	FROM 	EMPLOYEES e 
  	ORDER BY FIRST_NAME DESC

📍 1-2. SELECT 연산 기능

• ➕ dual 테이블

  • 오라클에서 기본으로 제공하는 dummy table
    • 간단하게 함수를 이용해서 계산결과값을 확인할 때 사용하는 테이블
    • dual 테이블은 사용자가 함수(계산)를 실행할 때 임시로 사용하기에 적합하다.
    • 함수에 대한 쓰임을 알고 싶을 때 특정 테이블을 이용하여 함수의 값을 리턴받을 수 있다.

• sum

  • 괄호 안에 명시한 열 값의 합계를 구한다.

  	SELECT 	SUM(SALARY)
  	FROM 	EMPLOYEES e 
  	;

• count

  • 괄호 안에 명시한 열에 속하는 자료 수를 센다. 이 때 null인 자료의 수는 포함하지 않는다.

  	SELECT 	COUNT(DEPARTMENT_ID) 
  	FROM 	EMPLOYEES e 
  	;

• avg

  • 괄호 안에 명시한 열 값의 평균을 구한다.

  	SELECT 	AVG(SALARY)
  	FROM 	EMPLOYEES e 
  	;

• max , min

  • 각각 괄호 안에 명시한 열 값의 최대값, 최소값을 구한다.

  	SELECT 	MAX(SALARY)
  	FROM 	EMPLOYEES e 
  	;
      ----------------------------
      SELECT 	MIN(SALARY)
  	FROM 	EMPLOYEES e 
  	;

• abs

  • 괄호 안에 있는 숫자값의 절대값을 구한다.

  	SELECT 	ABS(-23)
  	FROM 	dual 
  	;									// 결과 23

• trunc

  • 괄호 안에 있는 값을 절사해 준다. 추가 변수를 입력하지 않으면 소수점 아래를 전부 자르고, 추가로 양수를 입력하면 소수점 아래로 그 자리수만큼 남겨 준다. 음수를 입력하면 1의 자리부터 그 자리수만큼 0으로 처리한다.

  	SELECT 	TRUNC(1234.56789)
  	FROM	dual 
  	;									// 결과 1234
  	SELECT 	TRUNC(1234.56789,2)
  	FROM	dual 
  	;									// 결과 1234.56
  	SELECT 	TRUNC(1234.56789,-1)
  	FROM	dual 
  	;									// 결과 1230

📍 1-3. SELECT 문자열 기능

• concat

  • 문자열을 연결해 준다. ||로도 사용 가능하다.

  	SELECT	CONCAT( 'Hello', 'Bye' 		// 결과 HelloBye
  		,	CONCAT( 'Good', 'Bad')		// 결과 GoodBad
  		,	'good' || 'bad'				// 결과 goodbad
  	FROM	dual
  	;

• initcap

  • 문자열의 각 단어 첫 글자를 대문자, 나머지 소문자로 변환해 준다.

  	SELECT 	INITCAP('good morning')
  	FROM 	dual 						// 결과 Good Morning
  	;

• lower , upper

  • 각각 문자열을 전체 대문자, 전체 소문자로 변환해 준다.

  	SELECT 	LOWER('GOOD')				// 결과 good
  		,	UPPER('good')				// 결과 GOOD
  	FROM 	dual 
  	;

• lpad , rpad

  • lpad는 문자열의 가장 왼쪽, rpad는 가장 오른쪽부터 지정한 자리수만큼 지정한 문자로 채워 준다. 문자를 지정하지 않으면 공백으로 채운다.

  	SELECT 	LPAD('good',6)				// 결과 (공백 2자리)good
  		,	LPAD('good',7,'#')			// 결과 ###good
  		,	LPAD('good',8,'L') 			// 결과 LLLLgood
  	FROM 	dual 
  	;
      ----------------------------------
      SELECT 	RPAD('good',6)				// 결과 good(공백 2자리)
  		,	RPAD('good',7,'#')			// 결과 good###
  		,	RPAD('good',8,'L') 			// 결과 goodLLLL
  	FROM 	dual 
  	;

• ltrim , rtrim

  • ltrim은 문자열의 가장 왼쪽, rtrim은 가장 오른쪽부터 지정한 문자를 잘라 준다. 양극단에 있는 문자가 아닌 문자를 지정하면 절사가 일어나지 않고, 해당 문자가 여러 개 있으면 전부 잘라 준다. 양극단에 있는 문자 조합이기만 하면 순서가 섞여도 관계없다.

  	SELECT 	LTRIM('goodbye','g') 		// 결과 oodbye
  		,	LTRIM('goodbye','o') 		// 결과 goodbye
  		,	LTRIM('goodbye','go') 		// 결과 dbye
          ,	LTRIM('goodbye','og') 		// 결과 dbye
  	FROM 	dual 
  	;
      ----------------------------------
  	SELECT 	RTRIM('goodbye','e') 		// 결과 goodby
  		,	RTRIM('goodbye','y')		// 결과 goodbye
  		,	RTRIM('goodbye','yebdo')	// 결과 g
  	FROM 	dual 
  	;

• substr

  • 숫자 a,b를 지정하면 a번째 문자부터 b개의 문자를 잘라 준다. 인덱스를 하나만 입력하면 해당 인덱스부터 끝까지 자른다. 음수를 이용해 오른쪽 끝에서부터 자를 수도 있다.

  	SELECT 	SUBSTR('good morning john',1,4)	// 결과 good
  		,	SUBSTR('good morning john',8,4)	// 결과 rnin
  		,	SUBSTR('good morning john',8)	// 결과 rning john
  		,	SUBSTR('good morning john',-4)	// 결과 john
  	FROM	dual 
  	;

• replace

  • 문자열에서 지정한 부분을 원하는 문자열로 바꿔 준다. REPLACE('바꿀 문자열', '변환될 문자열', '변환할 문자열') 형태로 사용한다.

  	SELECT 	REPLACE ('good morning','morning','evening')
  	FROM	dual 							// 결과 good evening
  	;

📍 1-4. SELECT 날짜 기능

• sysdate

  • 시스템의 현재 날짜와 시간을 반환한다. 이 결과는 date 자료형을 가진다.

  	SELECT 	SYSDATE 
  	FROM	dual 							// 결과 2023-02-09 15:39:11.000
  	;

  • java에서와 같이 날짜를 조작하거나 특정 값을 얻는 명령어도 존재한다.

  	SELECT 	ADD_MONTHS(SYSDATE , 7) 
  	FROM	dual 					
  	;										// 현재 달에 7을 더한다
      -------------------------------------------------
  	SELECT 	LAST_DAY(SYSDATE)
  	FROM	dual 
  	;										// 현재 달에 존재하는 마지막 날짜를 가져온다

• to_char , to_date

  • to_char로 날짜를 문자열 형식으로 바꿀 수 있다. 반대로 to_date를 사용하면 문자열을 날짜로 변환할 수 있다.

  	SELECT	SYSDATE 
  		,	TO_CHAR(SYSDATE,'yyyy/mm/dd')	"yyyy/mm/dd"
  		,	TO_CHAR(SYSDATE, 'yyyymmdd') 	"yyyymmdd"
  		,	TO_CHAR(SYSDATE, 'yyyy-mm-dd') 	"yyyy-mm-dd"
  		,	TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:MI:SS') "yyyy-mm-dd HH24:MI:SS"
  	FROM	dual
  	;
  	------------------------------------------------
  	SELECT 	TO_DATE('2023-02-09','yyyy-mm-dd')
  	FROM	dual 
  	;

📍 1-5. SELECT 기타 기능, 다중 조건문

• AS(알리아스 alias)

  • 검색 열의 별칭을 설정할 수 있다. AS 명령어는 생략할 수도 있다.

  	SELECT 	e.EMPLOYEE_ID AS "ID"
  		,	SALARY "SAL"
  	FROM 	EMPLOYEES e 
  	;

• DISTINCT

  • 검색 결과에서 중복을 제거한다. 해당 열에 몇 가지의 값이 존재하는지 구할 수 있다.

  	SELECT	DISTINCT JOB_ID 
  	FROM	EMPLOYEES e 
  	;

• nvl()

  • null이면 지정한 값으로 바꿔 준다.

  	SELECT 	FIRST_NAME 	,	LAST_NAME 
  		,	NVL(COMMISSION_PCT,0)			
          // COMMISSION_PCT값이 null이면 null 대신 0을 넣어 준다
  	FROM 	EMPLOYEES e 
  	;

• decode()

  • java의 switch와 비슷한 기능으로, 지정한 조건과 일치하는 값을 바꾸어 출력한다. 여러 조건 쌍을 나열해서 사용할 수 있고, 나열한 조건 중 무엇에도 해당하지 않는 다른 모든 값들에 적용할 결과를 맨 마지막에 적는다.

  	SELECT	DEPARTMENT_ID 
  		,	DECODE(DEPARTMENT_ID, 20, 'MA',60,'IT',90,'EX','ETC')
       	// DEPARTMENT_ID가 20이면 MA, 60이면 IT, 90이면 EX로 반환하고 그 중 무엇에도
          해당하지 않는 값들은 ETC로 반환한다.
  	FROM	DEPARTMENTS d 
  	;

• case

  • java의 switch나 if~else if~else문과 비슷한 기능으로 CASE+WHEN 조건 THEN 결과*(조건문 중첩) +ELSE +END로 사용한다.

  	SELECT 	FIRST_NAME 	,	DEPARTMENT_ID 
  		,	CASE 	WHEN DEPARTMENT_ID  = 20 THEN 'MA'
  					WHEN DEPARTMENT_ID  = 60 THEN 'IT'
  					WHEN DEPARTMENT_ID  = 90 THEN 'EX'
  					ELSE ''					// 모든 조건에 해당하지 않을 때의 결과
  			END 	department 
  	FROM 	EMPLOYEES e 
  	ORDER BY DEPARTMENT_ID 
  	;