문과생이 공대에서 살아남기

1. ORDER BY 정렬

칼럼명, SELECT의 별명, 또는 순서로 정리 (혼용사용도 가능)

SELECT 칼럼명 [ALIAS명]

FROM 테이블명

[WHERE 조건식]

[GROUP BY 칼럼(Column)이나 표현식]

[HAVING 그룹조건식]

[ORDER BY 칼럼(Column)이나 표현식 [ASC 또는 DESC]] ;

ASC(Ascending) : 조회한 데이터를 오름차순으로 정렬한다.(기본 값이므로 생략 가능)

DESC(Descending) : 조회한 데이터를 내림차순으로 정렬한다.

ORACLE       : 내림차순에서는 NULL값이 앞에 출력, 오름차순에서는 NULL값이 뒤에 출력

SQL SERVER : 내림차순에는 NULL값이 뒤에 출력, 오름차순에는 NULL값이 앞에 출력

기본적인 정렬 순서는 오름차순 (숫자형일 경우는 가장 작은 값부터, 날짜형은 가장 빠른 날짜부터)

2. SELECT 문장의 실행 순서

5. SELECT 칼럼명 [ALIAS명]

1. FROM 테이블명

2. WHERE 조건식

3. GROUP BY 칼럼(Column)이나 표현식

4. HAVING 그룹조건식

6. ORDER BY 칼럼(Column)이나 표현식;

1. 발췌 대상 테이블을 참조한다. (FROM)

2. 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE)

3. 행들을 소그룹화 한다. (GROUP BY)

4. 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING)

5. 데이터 값을 출력/계산한다. (SELECT)

6. 데이터를 정렬한다. (ORDER BY)

ORDER BY 절에서는 SELECT 목록에 없는 문자형 항목 포함 가능

단, SELECT DISTINCT를 지정하더거나 GROUP BY 절, UNION 연산자 있다면 SELECT 목록에 표시 되어야함

예제) SELECT EMPNO, ENAME FROM EMP ORDER BY MGR;

인라인 뷰의 SELECT 절에서 정의한 칼럼은 메인쿼리에서도 사용 가능 그러나 서브쿼리 범위를 벗어나면 더 이상 사용 불가

GROUP BY 이후 SELECT나 ORDER BY 절에서는 개별 데이터 사용 불가

SELECT 절에서 정의하지 않은 MAX, SUM, COUNT 집계 함수도 ORDER BY에서는 사용 가능

예제) SELECT JOB

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY MAX(EMPNO), MAX(MGR), SUM(SAL), COUNT(DEPTNO), MAX(HIREDATE);

3. TOP N 쿼리

가. ROWNUM

ORACLE 순위가 높은 N개의 로우를 추출하기 위해 WHERE절에서 ROWNUM 함수 사용

단, 원하는 데이터를 원할 순 없음 데이터 일부가 먼저 추출된 후 정렬작업이 일어남, ORDER BY절 효과가 없음

인라인 뷰로 먼저 데이터 정렬 후 사용 가능, 인덱스로 추출가능

나. TOP ( )

SQL SERVER는 TOP 조건을 사용하여 사용 가능

TOP (Expression) [PERCENT] [WITH TIES]

WITH TIES : 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우 N+ 동일 정렬 순서를 추가 반환 하도록 지정된 옵션

1. 집계함수

여러행들의 그룹이 모여 그룹당 단 하나의 결과를 돌려줌

GROUP BY는 행들을 소그룹화함

Select, having, order by 절에서 사용가능

2. GROUP BY 절

SELECT [DISTINCT] 칼럼명 [ALIAS명]

FROM 테이블명

[WHERE 조건식]

[GROUP BY 칼럼(Column)이나 표현식]

[HAVING 그룹조건식] ;

GROUP BY절에서 소그룹화 후 SELECT절에서 집계함수 사용

NULL값 제외하고 행 수행

GROUP BY절에서는 별명 사용 불가

집계함수는 WHERE절에는 사용 불가

WHERE절은 전체데이터를 GROUP하기 전에 미리 제거

HAVING은 집계함수를 이용한 조건 표시

3. HAVING 절

WHERE절과 비슷하지만 그룹을 나타낸다는 결과 집합의 행에 조건이 적용된다는 점에서 차이가 있음

GROUP BY와 위치를 바꾸어도 문제가 생기진 않으나, 순서를 지키는 것을 권고

WHRER절에서 조건을 적용하여 데이터만 추출후 GROUP BY도 가능, 이 방법이 데이터 소모를 덜함

SELECT 절에 사용되지 않은 칼럼이나 집계함수가 아니더라고 

GROUP BY절의 기준항목이나 소그룹의 집계함수를 HAVING절에서 사용가능

HAVING절의 조건변경은 결과 데이터 변경은 없고 출력되는 레코드 갯수만 변경

예제) SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키

 FROM PLAYER

 GROUP BY POSITION

 HAVING MAX(HEIGHT) >= 190;

4. CASE 표현을 활용한 월별 데이터 집계

모델링의 제 1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을

정해진 칼럼수만큼 확장해서 집계 보고서를 만드는 유용한 기법

STEP 1. 개별데이터 확인

STEP 2. 월별 데이터 구분 : SIMPLE CASE EXPRESSION을 통해 12월별 칼럼 구분

STEP 3. 부서별 데이터 집계 : 평균급여를 GROUP BY로 AVG하여 집계

DECODE 함수 가능

5. 집계함수와 NULL

NVL/ISNULL을 사용하여 빈칸을 0값으로 변경하는 것이 다중행 함수 안에서는 사용할 필요없음

결과값을 낼 때, NULL값을 제외하고 구함

1. 내장함수

벤더에서 제공하는 함수 내장함수 vs 사용자가 정의할 수 있는 함수

내장 함수 - 단일행 함수

       - 다중행 함수  - 집계함수

   - 그룹함함수

   - 윈도우함수

입력되는 값이 아누리 많아도 출력은 하나만 된다는 M : 1관계

단일행 함수의 종류 (oracle/sqlserver)

특징

1. Select, where, order by 절에서 사용가능
2. 행들에 대한 개별적인 작용하여 값 조작
3. 여러 인자를 입력해도 단 하나의 결과만 리턴
4. 인자로 상수, 변수, 표현식 사용 가능하고 하나의 인수를 가지는 경우도 있고 여러개의 인수도 가질 수 있음
5. 함수의 중첩 가능

2. 문자형함수

DUAL 테이블의 특성

• 사용자 SYS가 소유, 모든 사용자가 엑세스 가능한 테이블
• DUMMY 테이블
• 'X'라는 값이 들어있는 행 1건을 포함
• SQLserver 에서는 select만 있어도가능하여 DUAL이 필요없음

3. 숫자형 함수

4. 날짜형 함수

5. 변환형 함수

특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수

암시적 데이터 변환은 성능저하 발생, 알아서 계산하지 않는 에러 발생할 수 있어서 명시적 데이터 유형 변환 방법 사용 필요

예제  SELECT TEAM_ID, CAST(ZIP_CODE1 AS INT) + CAST(ZIP_CODE2 AS INT) 우편번호합 FROM TEAM;

6. CASE 표현

단일행 CASE 표현의 종류

예시 중첩 : SELECT ENAME, SAL,

CASE WHEN SAL >= 2000

THEN 1000

ELSE (CASE WHEN SAL >= 1000 THEN 500 ELSE 0 END)

END as BONUS FROM EMP;

7. NULL 관련 함수

가. NVL/ISNULL 함수

NULL 특성

• 정의되지 않은 값으로 0 또는 공백과는 다르다
• NOTNULL, PK아니면 널값 포함 가능하다
• 널 값을 포함하는 연산의 경우 결과는 널

나. NULL와 공집합

• 일반적인 NULL/ISNULL 함수 사용
• 공집합의 NULL/ISNULL 함수 사용 : 공집합을 대상으로 하지 않음

다. NULLIF

라. COALESCE

표현식1, 2의 둘중 1번의 값이 있음 1번을 2번에 값이 있음 2번을 1,2번 둘다 있다면 1번을 둘다 없다면 NULL을 표현

 분모에 0이면 무조건 에러, null은 무슨 연산을 하여도 null

1. WHERE 조건절 개요

원하는 자료만 검색하기 위해서나 두 개 이상의 테이블에 대한 조건을 기술하거나 결과를 제한하기 위한 조건을 기술

SELECT [DISTINCT/ALL] 칼럼명 [ALIAS명]

FROM 테이블명

WHERE 조건식;

• 컬럼명
• 비교연산자
• 문자, 숫자, 표현식
• 비교칼럼 (조인 사용시)

2. 연산자의 종류

처리 우선순위

괄호 → NOT 연산자 → 비교연산자, SQL연산자 → AND → OR

3. 비교연산자

Char나 varchar2 같은 문자형 타입을 가진 칼럼은 인용부호( ' ' , " ")로 묶어서 비교 처리 해야함

문자 유형 비교 방법

4. SQL연산자

• IN 연산자 : SQL 문장을 짧게 만들어주면서도 성능측면에서도 장점을 가질수 있는 매우 유용한 연산자, 사용 권장

Ex) WHERE (JOB, DEPTNO) IN (('MANAGER',20),('CLERK',30));

• LIKE 연산자 : % (0개 이상의 어떤 문자를 의미), _ (1개인 단일 문자 의미)
• Between A and B 연산자
• IS NULL 연산자 : 값이 존재하지 않는 것, 확정되지 않은 값 표현할때 사용

NULL값과의 수치연산은 NULL, NULL과의 비교연산은 FALSE, 어떤 값과도 비교 할 수 없음

5. 논리연산자

우선순위 : ( ) → NOT → AND → OR

6. 부정 연산자

7. ROWNUM, TOP

가. ROWNUM

오라클의 ROWNUM은 임시로 부여되는 일련번호, 원하는 만큼만 가지고 올 때는 where 절에서 행 개수 제한 가능

SELECT 컬럼명 FROM 테이블명 WHERE ROWNUM = N;

SELECT 컬럼명 FROM 테이블명 WHERE ROWNUM <= N+1;

고유한 키나 인덱스 값 만들수 있음

UPDATE 테이블명 SET COLUMN1 = ROWNUM;

나. TOP

SQL server 행 수 제한

TOP (Expression) [PERCENT] [WITH TIES]

• Expression : 반환할  행수
• Rercent : 쿼리 결과 집합에서 처음 Expression의 %에 포함된 행만 반환(백분율 환산)
• With Ties : order by 절이 지정된 경우에만 사용, 같은 값이 있다면 추가 행을 출력할 수 있도록 지정

1. 트랜잭션 개요

데이터 베이스의 논리적 연산 단위

분리될수 없는 한 개 이상의 데이터 베이스 조작

분할할 수 없는 최소의 단위

하나의 논리적인 작업단위를 구성하는 세부적인 연산들의 집합

DML(update, delete, insert ..)등의 데이터 수정문이 대상임

Ex) 은행의 계좌이체

트랜잭션의 특성

동시 접근이 불가능하도록 다양한 레벨의 잠금 기능을 제공

트랜잭션 수행시 → 잠금(LOCKING)상태가 됨

트랜잭션에 대한 격리성이 낮은 경우 발생하는 문제점

1. Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것
2. Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행 했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에

    두 쿼리 결과가 다르게 나타남

3. Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행 했는데, 첫 번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상

2. COMMIT

COMMIT이나 ROLLBACK 이전의 데이터 상태

데이터 변경의 이전 상태로 복구 가능, select문으로 결과 확인 가능, 타인은 현재 사용자가 수행한 명령 결과만 볼수 있음, LOCKING 되어있음

→ COMMIT 이후 상태

변경 사항이 데이터베이스에 반영, 이전 데이터는 영원히 잃어버림, 모든 사용자는 결과 확인 가능, 잠금이 풀리고 다른 사용자들도 행 조작 가능

ORACLE에서는 DML이후 COMMIT과 ROLLBACK 수행해야 트랜잭션 종료

SQL server은 기본적으로 AUTO COMMIT 모드

SQL server 트랜잭션

1. AUTO COMMIT : DBMS가 트랜잭션을 컨트롤
2. 암시적 트랜잭션 : 사용자가 명시적으로 COMMIT 또는 ROLLBACK 처리, 세션 단위로 설정은 SET IMPLICIT TRANSACTION ON 사용
3. 명시적 트랜잭션 : 시작과 끝 모두 사용자가 명시적으로 지정하는 방식 BEGIN으로 시작하고 COMMIT 또는 ROLLBACK으로 종료

3. ROLLBACK

데이터 변경사항이 취소되고 이전상태로 복구

SQL server은 AUTO COMMIT이고 임의적으로 수행하려면 명시적으로 트랜잭션을 선언해야함

COMMIT과 ROLLBACK의 효과

• 데이터 무결성 보장
• 영구적인 변경 전 데이터 변경사항 확인 가능
• 논리적으로 연관된 작업으로 그룹핑하여 처리 가능

4. SAVEPOINT

저장점을 정의하면 현시점에서 SAVEPOINT까지 트랜잭션 일부만 변경 가능

해당 테이블에 데이터의 변경 하는 INSERT, UPDATE, DELETE 수행시 데이터의 무결성을 보장하는 것이 COMMIT&ROLLBACK 의 목적

[Oracle의 트랜잭션]

자동 시작되고 COMMIT 또는 ROLLBACK을 실행한 시점에서 종료

단, 다음의 경우에는 COMMIT과 ROLLBACK을 실행하지 않아도 자동으로 트랜잭션이 종료된다.

• DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다.
• 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
• 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.

[SQL Server의 트랜잭션]

DBMS가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT이 기본 방식

• 애플리케이션의 이상 종료로 데이터베이스(인스턴스)와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.