SQLD 한번에 합격하자 - sqld 개념정리(각종 함수, 명령어조건 등)

반응형

[ORACLE & SQL Server 의 WHERE절 차이점]

ORACLE에서는 WHERE 절에 NULL 조회하려면 IS NOT NULL, NULL

SQL Server에서 NULL 조회하려면 WHERE 컬럼명 =  '';로 조회

 

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

내장 함수는 다시 단일행 함수와 다중행 함수로 나눌 수 있다.

다중행 함수는 집계 함수, 그룹 함수, 윈도우 함수로 구분된다.

 

내장 함수

• 함수의 입력 행수에 따라 단일행 함수와 다중행 함수로 구분할 수 있다.
• 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용이 가능하다.
• 1:M 관계의 두 테이블을 조인할 경우 M쪽에서 출력된 행이 하나의 단일행 함수의 입력값으로 사용되므로 사용할 수 있다.
• 다중행 함수도 단일행 함수와 동일하게 단일 값만을 반환한다.

 

*ASCII(문자) - ASCII 코드 번호를 문자나 숫자로 바꾸어 준다.

*CHR/CHAR(ASCII번호) - ASCII 코드 번호를 문자나 숫자로 바꾸어 준다.

*SUBSTR/SUBSTRING(문자열, m[,n]) - 문자열 중 m 위치에서 n개의 문자 길이에 해당하는 돌려준다.

n이 생략되면 마지막 문자까지이다.

*LTRIM (문자열 [, 지정문자]) - 문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거.

(지정 문자가 생략되면 공백 값이 디폴트, SQL Server에서는 LTRIM함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거가능)

*RTRIM - LTRIM반대

 

오라클에서 날짜의 연산은 숫자의 연산과 같다.

특정 날짜에 1을 더하면 하루를 더한 결과와 같으므로 1/24/60 = 1분을 의미한다.

그렇다면 10분은 어떻게 될까? 1/24/(60/10) = 10분

하루는 1일 /  1일은 24시간 /  24/24 => 1시간 / 1시간은 60분 / (60/60) => 1분 / 60/(60/10) => 10분

 

CASE 문

SEARCHED_CASE_EXPRESSION

-- CASE WHEN LOC = 'NEW YORK' THEN 'EASH'

----ELSE 'ETC'

--END as AREA

 

SIMPLE_CASE_EXPRESSION

-- CASE LOC WHEN  'NEW YORK' THEN 'EASH'

----ELSE 'ETC'

--END as AREA

 

SQL Server(ISNULL) / ORACLE(NVL)

NULLIF(표현식1, 표현식2) : 표현식1이 표현식2와 같으면 NULL, 같지않으면 표현식1을 리턴

 

NULL이 포함된 연산의 결과는 NULL이다.

분모에 0이 들어가는 경우 연산 자체가 에러를 발생한다.

1. 0/300 = 0
2. 5000/0 = 에러 발생
3. 1000/null = null

 

COALESCE (표현식1, 표현식2, ....)

• 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다.
• 모든 표현식이 NULL이라면 NULL을  리턴.

 

집계함수

COUNT(*) : NULL 값을 포함한 행의 수를 출력한다.

COUNT(표현식) : 표현식의 값이 NULL 값인 것을 제외한 행의 수를 출력한다.

SUM([DISTINCT | ALL] 표현식) : 표현식의 NULL 값을 제외한 합계를 출력한다.

AVG([DISTINCT | ALL] 표현식) : 표현식의 NULL 값을 제외한 평균를 출력한다.

MAX([DISTINCT | ALL] 표현식) : 표현식의 최대값을 출력한다. (문자. 날짜 데이터 타입도 사용가능)

MIN([DISTINCT | ALL] 표현식) : 표현식의 최소값을 출력한다. (문자. 날짜 데이터 타입도 사용가능)

STDDEV([DISTINCT | ALL] 표현식) : 표현식의 표준 편차를 출력한다.

VARIAN([DISTINCT | ALL] 표현식) : 표현식의 분산을 출력한다.

 

NULL은 무조건 IS NULL, IS NOT NULL로만 비교.

 

GROUP BY에서 HAVING절은 GROUP BY 없이도 단독으로 사용가능하다.

단, 테이블 전체가 하나의 그룹이 되는 경우.

ex ) SELECT SUM(주문금액) AS 합계

--          FROM 주문

--    HAVING AVG(주문금액) > 100;

 

FROM - WHERE - GORUP BY - HAVING - SELECT - ORDER BY 순

** ORDER BY는 SELECT 결과를 정렬해준다.

주의) SELECT절에 없는 컬럼도 정렬할 때가 있는데 오라클은 행기반 데이터베이스 이기 때문에 데이터를 액세스할 때, 행 전체 컬럼을 메모리에 로드한다.

이와 같은 특성으로 인해 SELECT절에 기술되지 않은 컬럼으로도 정렬을 할 수 있다.

단, GROUP BY와 같이 사용할 경우 GROUP BY로 명시된 SELECT된 컬럼만 올 수 있다.

 

• ORDER BY는 DBMS에 따라 NULL값정렬의 차이가 있는데 Oracle인 경우 NULL을 가장 큰값으로 간주, SQL Server는 가장 작은 값으로 간주한다.

 

SQL Server의 TOP(동일 컬럼개수) WITH TIES  / ORDER BY

EX) SELECT TOP(3) WITH TIES 팀명, 승리건수 FROM 팀별성적 ORDER BY 승리건수 DESC;

=> 승리건수가 높은 순으로 3위까지 출력하되 3위의 승리건수가 동일한 팀이 있다면 함께  출력한다.

 

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

 

Join에 대한 설명

1. 일반적으로 Join은 PK와 FK 값의 연관성에 의해 성립된다.
2. DB옵티마이저는 From 절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝을 지어 Join을 수행한다.
3. EQUI Join은 PK와 FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립된다.
4. EQUI Join은 '=' 연산자에 의해서만 수행되며, 그 이외의 비교연산자를 사용하는 경우에는 모두 Non EQUI Join이다.
5. 대부분 Non EQUI Join을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있다.

 

ANSI문법이란?

표준 SQL문이기 때문에 DBMS의 종류를 제약 받지 않는다.

EX ) 오라클에서 조인할 경우 (+)    /  ANSI 문으로 사용할 경우 LEFT OUTER JOIN