SQL 뽀개기 - 3 | SELECT

1. SELECT

 

SELECT 필드목록 FROM 테이블 [ WHERE 조건 ] [ ORDER BY 정렬기준 ]

 

# 테이블 내 모든 필드 출력

SELECT * FROM tCity

" * " : ALL, 모든 필드

 

# 일부 필드만 출력하거나 순서를 직접 지정

SELECT name, area FROM tCity;
SELECT regin, name, area FROM tCity;

 

# 별명

// 필드명 [AS] "별명"

SELECT name AS "도시명",  area AS "면적(km^2)", popu AS "인구(만명)" FROM tCity;
SELECT name AS "도시", popu AS "인구(만명)", region AS "지역" FROM tCity;

- 별명은 큰따옴표로 감싸야 한다

- name 필드를 읽어오되 출력을 별명으로 대신 하는것. 

 

 

# 계산식

SELECT name, (popu * 10000) AS "인구(명)" FROM tCity;
SELECT name, area, popu, (popu * 10000 / area) AS "인구밀도" FROM tCity;

- 테이블에 없는 필드도 새로 가공해서 표시할 수 있다. 

 

# 더미 테이블(dual)

SELECT 60 * 60 * 24 AS "하루" FROM dual;
SELECT 60 * 60 * 24 * 365 AS "1년" FROM dual;

- dual은 1행 1열짜리 더미 테이블

- 데이터 조회를 하지 않고 단순계산만 해서 연산결과를 보고싶은 경우를 위해 오라클에서 제공함..

- SELECT문에서 FROM절은 필수로 와야한다 -> 안쓰는 테이블 아무거나 채워넣기 위해 더미테이블 이용

 

 

2. 조건문 (WHERE절)

- 읽어올 레코드의 조건 지정

- WHERE절이 없으면 모든 레코드를 다 읽어온다

- A <> B, A != B 둘이 같은표현

- 문자열과 날짜 상수는 항상 작은따옴표로 감싸야 한다

 

SELECT * FROM tCity WHERE area > 1000;
SELECT name, area AS "면적(km^2)" FROM tCity WHERE area > 1000;

SELECT * FROM tCity WHERE name = '서울';
SELECT * FROM tCity WHERE popu < 10;
SELECT * FROM tCity WHERE region = '전라';
SELECT * FROM tStaff WHERE salary >= 400;

- NOT NULL 필드 : null값을 받지 않는다 -> 값이 반드시 있어야 한다

- NULL 필드 : NULL값도 가능 -> 값이 없어도 된다

- NULL을 확인할때는 ~~ = NULL 이 아니라 IS NULL, IS NOT NULL을 사용

SELECT * FROM tStaff WHERE score IS NULL;
SELECT * FROM tStaff WHERE score IS NOT NULL;

 

3. 논리 연산자 (AND, OR + NOT)

- 복합 조건의 반대를 취할때는 != 를 쓰기보다 NOT을 앞에 붙이는게 편하다

- 괄호로 우선순위 지정 잘하기

SELECT * FROM tCity WHERE region = '전라' OR metro = 'y';
SELECT * FROM tCity WHERE NOT(region = '전라' OR metro = 'y');

SELECT * FROM tStaff WHERE salary < 300 AND score >= 60;
SELECT * FROM tStaff WHERE gender = '여' AND depart = '영업부';

 

4. LIKE

- "=" 연산자는 완전히 일치하는 조건식을 출력

- "LIKE" 연산자는 패턴으로 부분 문자열을 검색

 

% : 복수개의 문자와 대응, % 자리에는 임의 개수의 임의 문자가 올 수 있다

_ : 하나의 문자와 대응, _ 자리에 하나의 임의 문자가 올 수 있다

[] : [] 안에 포함된 문자 리스트 중 하나의 문자와 대응

[^] : [^] 안에 포함된 문자 리스트에 포함되지 않은 하나의 문자와 대응

 

SELECT * FROM tCity WHERE name LIKE '%천%';
SELECT * FROM tCity WHERE name NOT LIKE '%천%';

SELECT * FROM tCity WHERE TRIM(name) LIKE '%천';

SELECT * FROM tStaff WHERE name LIKE '이%';
SELECT * FROM tStaff WHERE name LIKE '%신%';

필드가 CHAR 타입인 경우, 문자열 뒤쪽을 공백으로 채운다.

-> WHERE name LIKE '%천' 으로 할 경우 검색되지 않음. (뒤에 공백이 포함되어 있기 때문에)

-> 따라서 TRIM으로 앞뒤 공백 다 떼고 검색해야 함 (아니면 VARCHAR 타입을 쓰면 해결됨)

 

4-1. ESCAPE 문자

와일드카드 문자 그 자체를 검색하기 위해 쓰인다

ex ) "~~ 30%" 와 같은 문자열을 검색하고자 할 떄

SELECT * FROM table WHERE sale LIKE '%30#%' ESCAPE '#'

'#' 이 이스케이프 문자

# 다음의 와일드카드는 일반 문자로 취급된다

--> 앞쪽의 %은 와일드카드, 뒤쪽의 %는 일반 문자로 해석

- 이스케이프 문자는 고정되어있지 않음.. -> 내 꼴리는거 쓰면 됨

 

5. BETWEEN

"BETWEEN 최소값 AND 최대값" 형식

- 문자열이나 날짜 등에도 사용가능. 문자열은 사전순대로

- 부등호 쓰는것보다 직관적이라 좋다

- 연속된 범위만 검색할 수 있다

SELECT * FROM tStaff WHERE salary BETWEEN 200 AND 299;
SELECT * FROM tCity WHERE area BETWEEN 500 AND 1000;

 

6. IN

- 불연속적이고 임의적인 값 여러개를 조사할 때. 목록과 일치하는 레코드를 탐색.

- 괄호 안에 컴마로 구분된 값 목록을 나열, 이 중에 하나에 해당하는지를 점검

- OR로 완전 대체할 수 있음. 값 리스트가 많을 경우 IN을 사용하는게 편하다

SELECT * FROM tCity WHERE region IN ('경상', '전라');
SELECT * FROM tCity WHERE region NOT IN ('경상', '전라');

- LIKE와 IN을 섞어쓸 수 없다!

SELECT * FROM tStaff WHERE name LIKE IN ('이%', '안%'); -- Error
SELECT * FROM tStaff WHERE name LIKE '이%' OR name LIKE '안%'; -- Correct

 

7. 정렬

ORDER BY 기준필드 [ASC | DESC]

(default : 오름차순)

SELECT * FROM tCity ORDER BY popu;
SELECT * FROM tCity ORDER BY popu DESC;

SELECT region, name, area, popu FROM tCity ORDER BY region, name DESC;

- 기준 필드를 콤마로 구분하여 나열하되 각 기준별로 오름/내림차순을 따로 지정할 수 있다

- 1차 정렬 기준이 같으면 2차 정렬 기준으로 정렬하는 방식

- region으로 오름차순 정렬(default) 후 같은 값은 name으로 내림차순 정렬

 

SELECT * FROM tCity ORDER BY 2;
SELECT * FROM tCity ORDER BY area;

- ORDER BY의 기준은 보통 필드명으로 하지만 필드의 순서값으로 지정해도 된다.

- 0번부터 시작이 아님에 유의.. 1번부터 시작임

 

SELECT name, popu * 10000 / area As "인구밀도" FROM tCity ORDER BY popu * 10000 / area DESC;

- 테이블에 존재하는 모든 필드는 정렬 기준으로 사용될 수 있다

- 테이블에 없는 필드도 가공해서 정렬 기준으로 사용할 수 있다

 

- ORDER BY 절은 항상 SELECT 제일 마지막에 와야함.

SELECT * FROM tStaff ORDER BY salary, score DESC;
SELECT * FROM tStaff WHERE depart = '영업부' ORDER BY joindate;

 

8. DISTINCT

- 중복 제거!!

- DISTINCT 키워드가 있는 필드는 중복값을 합쳐 한 번만 출력한다.

SELECT DISTINCT region FROM tCity ORDER BY region;
SELECT DISTINCT depart FROM tStaff;
SELECT DISTINCT depart FROM tStaff WHERE joindate >= '20/01/01';

- DISTINCT의 반대는 ALL인데 기본값이 ALL이라 쓸 일이 없다

- 동의어 UNIQUE는 표준이 아니라 안쓰는게 좋다

 

9. 의사 컬럼, rownum

- 의사 컬럼은 실제 테이블에는 없지만 인위적으로 추가된 가짜 컬럼. (rowid, rownum)

rowid : 행마다 붙이는 고유값

rownum : 인덱스(1, 2, 3, ...)

SELECT * FROM tCity WHERE rownum <= 4;
SELECT name, rowid, rownum FROM tCity;

- 의사컬럼은 오라클에서만 지원하는 문법이다

- 다른데서는 TOP, LIMIT과 같은 명령어를 사용

 

특정 기준으로 정렬해놓고 그중 일부만 뽑아내려면 어떻게 해야할까

-> 서브쿼리 기법

SELECT * FROM (SELECT * FROM tCity ORDER BY area DESC) WHERE rownum <= 4;

 

9.1. OFFSET FETCH

- 표준 문법이다

- ORDER BY의 옵션으로 사용

[ ORDER BY 기준필드 OFFSET 건너뛸행수 ROWS FETCH NEXT 출력할 행수 ROWS ONLY ]

SELECT * FROM tCity ORDER BY area DESC OFFSET 0 ROWS FETCH NEXT 4 ROWS ONLY;
SELECT * FROM tCity ORDER BY area DESC OFFSET 2 ROWS FETCH NEXT 4 ROWS ONLY;

SELECT * FROM tCity WHERE metro = 'N' ORDER BY area DESC OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;
SELECT * FROM tStaff ORDER BY salary DESC OFFSET 12 ROWS FETCH NEXT 4 ROWS ONLY;

 

# 기타 SQL 함수

- ABS(n)

- CEIL(n), FLOOR(n)

- EXP(n)

- POWER(a, b)

- MOD(a, b)

- ROUND(n, i)

- SQRT(n)

- CONCAT(char1, char2) - char1과 char2를 합친 결과 리턴

- LOWER(char) / UPPER(char)

- TRIM(char) - char 양쪽 공백 제거

- SUBSTR(char, n1, n2) - char에서 n1부터 n2까지만 잘라서 리턴

- LENGTH(char) - 글자수 리턴

- SELECT SYSDATE FROM DUAL

 

# 숫자 형변환 (" , " : 컴마 | " . " : 소숫점)

TO_CHAR(123456, '999,999');

>> 123,456

 

TO_CHAR(123456.7, '999,999.9');

>> 123,456.7

 

# CASE문

SELECT name
       ,age
       ,CASE WHEN age BETWEEN 0  AND 19 THEN '10대'
    	     WHEN age BETWEEN 20 AND 29 THEN '20대'
             WHEN age BETWEEN 30 AND 39 THEN '30대'
             WHEN age BETWEEN 40 AND 49 THEN '40대'
             ELSE '50대 이상'
       END ages
FROM table

- 자바에서 Switch문과 비슷하다고 생각하면 된다