SQL 기본(2)

● 연산자의 종류

구분	연산자	연산자의 의미
비교연산자	=	같다
	>	보다 크다
	>=	보다 크거나 같다
	<	보다 작다
	<=	보다 작거나 같다
SQL 연산자	BETWEEN A AND B	A와 B 사이에 있는 값
	IN (list)	list에 존재하는 값(하나라도 일치하면 OK)
	LIKE '비교문자열'	비교문자열거ㅏ 형태 일치(&, _ 사용)
	IS NULL	NULL 값인 경우
논리 연산자	AND	앞에 오는 조건과 뒤에 오는 조건이 모두 TRUE여야 만족
	OR	앞에 오는 조건이나 뒤에 오는 조건 중 하나만 TRUE면 만족
	NOT	뒤에 오는 조건의 반대되는 결과
부정 비교 연산자	!=	같지 않다.
	^=	같지 않다.
	<>	같지 않다.(모든 운영체제에서 사용 가능)
	NOT 컬럼명 =	~와 같지 않다
	NOT 컬렴명 >	~보다 크지 않다
부정 SQL 연산자	NOT BETWEEN A AND B	A와 B의 값 사이에 있지 않음
	NOT IN (list)	list에 존재하지 않는 값
	IS NOT NULL	NULL값이 아님

 

● SQL 내장 함수 

  ○ 설명

    - 함수는 벤더에서 제공하는 함수인 내장함수와 사용자가 정의할 수 있는 함수로 나뉜다.

    - 함수의 입력 행수에 따라 단일행 함수와 다중행 함수로 구분된다.

    - 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE, SET절에 사용이 가능하다.

    - 다중행 함수는 SELECT, HAVING, GROUP BY절에 사용 가능하다.

    - 1:M관계의 두 테이블을 조인을 하더라도 M쪽에 출력된 행이 하나씩 단일행 함수의 입력값으로 사용하므로 사용할 수 있다.

    - 다중행 함수도 단일행 함수와 동일하게 단일 값만을 반환한다.

 

  ○ 단일행 함수

 

     1. 문자형 함수

LOWER(문자열)	문자열을 소문자로 변환
UPPER(문자열)	문자열을 대문자로 변환
INITCAP(문자열)	문자열의 첫 문자를 대문자로 변환
ASCII(문자)	문자를 아스키 코드로 변환
CHR(아스키 코드)	아스키 코드를 문자로 변환 (=> SQL Server에서는 CHAR()를 사용)
CONCAT(문자열1, 문자열2)	문자열1과 문자열2를 연결. '문자열1' | | '문자열2'와 같음.
SUBSTR(문자열, 시작위치[, 길이])	문자열의 시작위치에서 길이만큼 반환하며, 길이를 생략하면 마지막 문자열까지 반환 시작위치를 음수로 입력하면 오른쪽 방향으로 계산하여 출력 (=>SQL Server에서는 SUBSTRING()을 사용)
LENGTH(문자열)	문자열의 길이를 출력. 바이트를 반환하려면 LENGTHB()를 사용하며, SQL Server에서는 LEN()을 사용
LTRIM(문자열[, 지정문자])	문자열의 왼쪽에서 연속되는 지정문자를 제거하며, 지정문자가 생략되면 공백을 제거
RTRIM(문자열[, 지정문자])	문자열의 오른쪽에서 연속되는 지정문자를 제거하며, 지정문자가 생략되면 공백을 제거
TRIM([LEADING | TRAILING | BOTH] [지정문자 FROM] 문자열)	문자열의 머릿말, 꼬릿말 또는 양쪽에서 지정문자를 제거하며, 지정문자를 생략하면 공백을 제거
LPAD(문자열, 길이[, 채움문자])	길이만큼 문자열의 왼쪽에 채움문자를 채우며, 채움문자를 생략하면 공백으로 채움
RPAD(문자열, 길이[, 채움문자])	길이만큼 문자열의 오른쪽에 채움문자를 채우며, 채움문자를 생략하면 공백으로 채움
REPLACE(문자열, 지정문자열, 변환문자열)	문자열에서 지정문자열을 변환문자열로 변경
TRANSLATE(문자열, 지정문자열, 변환문자열)	REPLACE와 유사하며, 지정문자열의 한글자씩 변환문자열에 매핑하여 문자열을 변경

 

     2. 숫자형 함수

ABS(숫자)	숫자의 절대값을 반환
SIGN(숫자)	숫자가 양수이면 1, 음수이면 -1, 0이면 0을 반환
MOD(숫자1, 숫자2)	숫자1을 숫자2로 나눈 나머지를 반환
CEIL(숫자)	숫자의 올림값을 반환
FLOOR(숫자)	숫자의 내림값을 반환
ROUND(숫자[, 소수점자리수])	숫자를 소수점 자릿수에서 반올림 소수점자리수를 생략하면 기본값으로 0이 적용
TRUNC(숫자[, 소수점자리수])	숫자의 소수점 자릿수 뒷자리를 버림

   

     3. 날짜형 함수

SYSDATE	현재 날짜와 시간을 반환(=> SQL Server에서는 GETDATE()를 사용)
EXTRACT (YEAR | MONTH | DAY FROM 날짜)	날짜에서 년(YEAR), 월(MONTH), 일(DAY)을 추출 SQL Server에서는 DATEPART()를 사용

   

     4. 형변환 함수 

TO_NUMBER(문자열)	문자열을 숫자로 변환
TO_CHAR(숫자 | 날짜[, 포맷])	숫자 또는 날짜를 포맷에 맞는 문자열로 변환
TO_DATE(문자열[, 포맷])	문자열을 포맷에 맞는 날짜 타입으로 변환

     

     5. NULL 관련 함수

NVL(표현식1, 표현식2)	표현식1이 NULL이면 표현식2를 반환 SQL Server에서는 ISNULL()을 사용
NVL2(표현식1, 표현식2, 표현식3)	표현식1이 NULL이 아니면 표현식2를, NULL이면 표현식3을 반환
IFNULL(표현식1, 표현식2)	표현식1과 표현식2가 같으면 NULL을, 같지않으면 표현식1을 반환
COALESE(표현식1, 표현식2, …)	표현식 중 NULL이 아닌 첫번째 값을 반환

 

 ○ 다중행 함수

COUNT(*)	값을 포함한 컬럼 전체 행의 수를 반환
COUNT(컬럼 | 표현식)	값을 제외한 컬럼이나 표현식에 해당하는 행의 수를 반환
SUM(컬럼 | 표현식)	컬럼이나 표현식에 해당하는 값의 합계를 반환
AVG(컬럼 | 표현식)	컬럼이나 표현식에 해당하는 값의 평균을 반환
MAX(컬럼 | 표현식)	컬럼이나 표현식에 해당하는 값 중 최대값을 반환
MIN(컬럼 | 표현식)	컬럼이나 표현식에 해당하는 값 중 최소값을 반환
STDDEV(컬럼 | 표현식)	컬럼이나 표현식에 해당하는 값들의 표준편차를 반환
VARIANCE(컬럼 | 표현식)	컬럼이나 표현식에 해당하는 값들의 분산을 반환

 

● 문제 풀이 (SQL 내장 함수에 대하여)

  ○ 아래 TAB1 테이블 

ROWNUM	C1
1	A A
2	B B B

SELECT SUM(CC)
 FROM (
 	SELECT(LENGTH(C1) - LENGTH(REPLACE(C1, CHR(10))) + 1) CC
 )
 FROM TAB1
 
 
 /*
 - 문제 풀이
 LENGTH(C1) : C1문자열의 길이를 구하세요. 
 	ROWNUM이 1인 C1문자열의 길이는 3 (줄바꿈도 문자 1개로 침)
 	ROWNUM이 2인 C1문자열의 길이는 5
 CHR(10) : 아스키코드 값으로 줄바꿈을 의미
 REPLACE(C1, CHR(10)) : C1의 줄바꿈을 뭐로 바꾸라는 말이 없으므로 ''으로 바꾼다.
    ROWNUM이 1인 C1문자열의 길이는 A  -> AA  : 길이 2
    						  A
 	ROWNUM이 2인 C1문자열의 길이는 B
    						  B  -> BBB : 길이 3
                              B
                              
(LENGTH(C1) - LENGTH(REPLACE(C1, CHR(10)) + 1
	ROWNUM이 1인 C1문자열의 길이는 3 - 2 = 1 + 1 = 2
 	ROWNUM이 2인 C1문자열의 길이는 5 - 3 = 1 + 1 = 3
    
    2 + 3 = 5 가 답이 된다.
 */

 

● ORCALE 날짜 연산

1/24	1시간
1/24/60	1분
1/24/30	2분
1/24/12	5분
1/24/6	10분
1/24/(60/10)	10분

 

● Simple Expression과 SearchedExpression

/* SEARCHED_CASE_EXPRESSION */
SELECT LOC,
  CASE WEHN LOC = 'NEW YORK' TEHN 'EAST'
  ELSE 'ETC'
  END as AREA
 FROM DEPT;
 
 
/* SIMPLE_CASE_EXPRESSION */
SELECT LOC,
  CASE LOC WHEN 'NEW YORK' TEHN 'EAST' -- 상태를 앞으로 가져와 WHEN 뒤의 단어에 비교할 대상의 값을 넣는다.
  ELSE 'ETC'
  END as AREA
 FROM DEPT;

 

● SELECT 문장의 실행 순서

  FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

● TOP() 함수 

/* 성적이 상위 5위까지의 학생 출력 */
SELECT TOP(5) WHITH TIES STUDENT, SCORE -- 기준이 되는 값과 순위를 매길 수 있는 값
 FROM SCHOOL_STUDENT
 ORDER BY SOCRE DESC;

 

● 테이블 조인과 관련해서

 - 여러 테이블(N)로부터 원하는 데이터를 조회하기 위해서는 전체 테이블 개수에서 최소 N-1개 만큼의 조인 조건이 필요하다.

 

● 조인(Join)에 대한 설명

  - 일반적으로 Join은 PK와 FK 값의 연관성에 의해 성립 (-> 드시 PK ↔ FK의 관계로만 EQUI JOIN이 성립하는 것 X)

  - DBMS 옵티마이저는 FROM 절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝을 이뤄 Join을 수행

  - EQUI Join은 Join에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용되는 방법

  - EQUI Join은 '=' 연산자에 위해서만 수행되면, 그 외의 비교 연산자를 사용하는 경우에는 모두 Non EQUI Join

  - 대부분 Non EQUI Join을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 존재

 

● EQUI Join

   - EQUI Join은 동등 조인, Inner Join의 한 종류

   - 이 조인은 두 테이블 간의 열(Column) 간의 동등한 값에 기반

   - 일반적으로 등호(=)를 사용하여 조인 조건을 표현

   - 결과는 두 테이블 간에 일치하는 값만 반환

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

 

● NON-EQUI Join

   - NON-EQUI Join은 동등하지 않은 값을 기반으로 두 테이블을 조인, Inner Join의 한 종류

   - 일반적으로 등호(=)가 아닌 비교 연산자(>, <, >=, <=) 등을 사용하여 조인 조건을 정의

   - 이 유형의 조인은 일반적으로 범위나 부등호 기반의 비교를 수행할 때 사용

SELECT orders.order_id, orders.order_date, products.product_name
FROM orders
INNER JOIN products ON orders.product_id > products.product_id;