SQL 기본 - 1(관계형 데이터베이스, SELECT, 함수, WHERE)

관계형 데이터베이스

데이터베이스

파일 시스템은 하나의 파일에 여러 사용자가 동시에 입력/수정/삭제가 불가능해서

동시 사용하려면 데이터 파일을 복사해야 한다.

데이터가 복사되면 데이터의 정합성을 보장하기가 힘들다

물론, 단일 사용자/단일 애플리케이션이라면 DB보다 파일 시스템이 효율적이다

DB가 커지면서

• 효율적 관리
• 손상 예방
• 데이터 복구가 필요해졌다

그래서 나온 것이 DBMS(DataBase Management System).

• 보안 기능 제공(인증된 사용자)
• 제약조건 설정 => 데이터 무결성(Integrity)
• 갑작스런 장애로부터 데이터 보호 및 복구

RDBMS는 정규화, 동시성 관리, 병행 제어 등의 기능을 제공한다

메타 데이터를 총괄 관리할 수 있고

체계화, 표준화하는데 유용하다

SQL

Structured Query Language

관계 데이터베이스에서 데이터 정의/조작/제어하는 언어

독립된 하나의 개발 언어

데이터를 집합처럼 취급한다

• DML 조작: select(retrieve), insert, update, delete
  데이터 조회/변형
• DDL 정의: create, alter, drop, rename
  스키마, 도메인, 뷰, 인덱스, 테이블 정의 생성/변경/삭제
• DCL 제어: grant, revoke
  DB 접근 및 사용 권한 관련 명령
• TCL 트랜잭션 제어: commit, rollback
  작업 단위 별로 제어

호스트 프로그램 속에 삽입되어 사용되는 DML은 데이터 부속어(Data Sub Language)

• AS-IS: DML은 비절차적 데이터 조작어로
  사용자가 무슨 데이터 원하는지만 명세 WHAT
• TO-BE: PL/SQL(오라클), T-SQL(SQL 서버)같은 절차적 데이터 언어는
  어떻게 데이터에 접근해야하는지 명세 HOW

데이터 유형

칼럼을 정의할 때 지정하는 데이터의 유형과 크기

1. Character(s): CHAR

   고정 길이 문자열

   할당된 값이 s보다 작으면 남은 공간을 공백으로 채운다

   기본 길이는 2byte

2. Varchar(s): VARCHAR

   가변 길이 문자열

   일종의 limit 개념으로 할당된 값의 바이트만큼만 차지한다

   최소 길이는 1byte

   ex) a='ㄱㄴㄷ', b='ㄱㄴㄷ '(b에는 공백이 있음)

   ​ char이면 뒤의 공백을 채워서 비교하므로 a = b

   ​ varchar이면 공백도 b의 문자이므로 a != b

3. Numeric: NUMBER

   정수, 실수 등 숫자 정보

   오라클은 NUMBER 타입만 있지만

   SQL 서버는 10개 이상 있다

   NUMBER(8, 2): 전체 자리수는 8자리, 정수 부분은 6자리, 소수점 부분은 2자리

4. DATETIME

   날짜와 시각 정보

   오라클은 1초 단위: DATE

   SQL 서버는 3.33ms 단위: DATETIME

• NUMBER(4)인 컬럼에 1234.5를 넣으려고 하면 반올림해서 1235

• NUMBER(5, 2)의 범위는 -999.99~999.99

  0.0001도 이 범위에 속한다

• NUMBER(2, 5)의 범위는 -0.00099~0.00099

• SELECT '1000'||(40-NULL)||'0' - 100 as NUM;

  '1000'||(40-NULL)||'0'

  = '1000'||NULL||'0'

  = '10000'

  '10000' - 100

  = 10000 - 100 = 9900

SELECT문

SELECT

• SELECT [ALL / DISTINCT] 칼럼명, 칼럼명, ... FROM 테이블명;

  ALL은 기본값으로 중복 데이터도 다 출력한다

  DISTINCT는 중복 데이터는 1건만 출력한다(GROUP BY를 써도 중복 제거 효과)

• SELECT * FROM 테이블명;

  테이블의 전체 데이터 조회

• SELECT PLAYER_NAME [AS] 선수명 FROM PLAYER;

  AS는 생략 가능하다

  공백, 특수문자, 대소문자 등을 포함하면 ""를 사용한다

  칼럼 별칭이 다른 칼럼명과 동일하다면 별칭에 우선순위가 간다

• 테이블 별칭을 지정했으면(칼럼 별칭처럼 AS 생략 가능) SELECT, WHERE에서는 별칭을 사용해야만 한다(안 쓰면 에러가 난다)

• 오라클의 경우, SELECT 문장에 Select절과 From 절 두 개가 필수

  테이블이 필요하지 않은 연산을 할 경우('I want to know a length'의 길이를 구하고 싶을 때!) DUAL 테이블을 활용한다

  SELECT LENGTH('I want to know a length') FROM DUAL;

• DUAL 테이블

  사용자 SYS의 소유. 모든 사용자가 접근 가능

  일종의 dummy 테이블로 형식적으로 테이블이 필요할 때 사용한다

  DUMMY라는 문자열 칼럼에 'x'값이 있는 행 1건이 포함되어 있다

산술 연산자와 합성 연산자

• 산술 연산자

  NUMBER, DATE 자료형에 적용

  사칙연산과 동일함

  괄호로 우선순위를 지정할 수 있다

  (), *, /, +, -

  SELECT HEIGHT - WEIGHT AS '키 - 몸무게', PLAYER_NAME FROM PLAYER;

• 합성 연산자(concatenation)

  문자와 문자를 연결하는 합성연산자

  오라클은 ||, SQL 서버는 +

  CONCAT 함수: CONCAT(S1, S2)

  칼럼과 문자 또는 칼럼과 칼럼도 가능

  새로운 칼럼을 생성한다

함수

내장 함수

함수 입력 값이 단일행이면 단일행 함수

아니면 다중행 함수: 집계 함수, 그룹 함수, 윈도우 함수..

• 단일행 함수(/이 있으면 앞은 오라클 뒤는 SQL 서버)

  • 문자형: 입력값 문자 => 출력값 문자/숫자

    lower, upper, ascii, concat, length/len, ltrim, trim...

  • 숫자형: 숫자 => 숫자

    abs, mod, sin, log, round...

  • 날짜형: Date 타입 연산

    sysdate/getdate, to_number(to_char(d, 'yyyy'|'MM'|'DD'))

  • 변환형: 문자/숫자/date 등의 타입 변환

    cast, to_number, to_char, to_date

  • NULL 관련

    NVL/ISNULL, NULLIF, COALESCE특징

    • select, where, order by절에 사용 가능
    • 각 행들에 대해 개별적으로 작용해 데이터 값 조작, 각 행에 대한 결과 리턴
    • 여러 인자여도(argument) 결과는 하나만 리턴
    • 함수의 인자로 상수/변수/표현식 사용 가능
    • 보통 함수의 중첩 가능(함수의 인자로 함수 사용): 특별한 예외 있음

문자형 함수

• 문자 => 문자/숫자

LOWER(s)

UPPER(s)

ASCII(s/n): 아스키 코드 번호 반환

CHR/CHAR(ASCII 번호): 해당 문자 반환

CONCAT(s1, s2): 오라클은 ||, SQL 서버는 +가 같은 역할

SUBSTR/SUBSTRING(s, m[, n]): 문자열의 m 위치에서 n개의 길이에 해당하는 문자 반환
n이 생략되면 마지막까지!

LENGTH/LEN(s)

LTRIM(s[, 지정문자]): 문자열의 왼쪽부터 훑다가 지정문자가 나타나면 제거한다
지정문자 생략시 공백이 제거되는데
SQL 서버는 지정문자 사용이 불가능하다

RTRIM(s[, 지정문자])
공백 제거와 chat-varchar 데이터 비교에 쓰인다

TRIM([leading|trailing|both] 지정문자 FROM s)
머리말/꼬리말/양쪽의 지정문자 제거. SQL 서버는 [] 설정 불가.
both가 기본값이라 SQL 서버도 both

• LENGTH(C1) - 1 이것을 위치로 한다면?

  만약 C1의 값이 'ABC'라면 B 자리

  마지막보다 하나 앞자리

• LENGTH(SUBSTR('ABCDEF', 2, 4))

  LENGTH(SUBSTR('GHI', 2, 4))

  2번째 자리부터 문자 4개 잘라서 반환해서 그 길이를 구한다

  BCDE => 4

  문자의 길이가 부족해서 4개를 못 자르면? 있는 만큼만 자른다

  HI 두 개만! => 2

• LENGTH(LTRIM('000120300', '0'))

  LENGTH(LTRIM('0760500', '0'))

  왼쪽부터 0을 제거하다가 다른 문자를 만나면 남는 것을 반환해서 그 길이를 구한다

  '120300' => 6

  '760500' => 6

• MIN('12400', '930', '4070', '2340')'

  '12400'

  숫자가 아니라 문자 비유임

  첫번째 문자가 1이라 제일 작아서

• LPAD(SAL, 4, '0') || 'ENAME'

  • SAL이 800이면 => 0800ENAME
  • SAL이 29756이면 => 2975ENAME

  왼쪽부터 4글자를 끊는다.

  만약 주어진 문자가 4자리가 안 되면 0으로 빈자리를 왼쪽부터 채운다

숫자형 함수

• 숫자 => 숫자

ABS(-15) => 15: 절댓값

SIGN(-20) => -1: 음수/0/양수 => -1/0/1

MOD(7, 3) => 1: 7을 3으로 나눈 나머지

CEIL/CEILING(38.123) => 39: 크거나 같은 최소 정수(-38.123이면 -38)

FLOOR(38.123) => 38: 작거나 같은 최대 정수

ROUND(38.5235, 1) => 38.5: 두 번째 인자자리로 반올림. 생략시 기본값은 0(정수)

TRUNC(38.5235, 3) => 38.523: 두 번째 인자자리로 내림. 생략시 기본값은 0.
SQL 서버는 없음
TRUNC(15000/2000) => 7: 몫만 구한다

SIN, COS, TAN(n): 숫자의 삼각함수 값

EXP(2) => 7.3890561: 숫자의 지수 값. 즉 e의 숫자 제곱 값

POWER(2, 3) => 8: 2의 세제곱. 숫자의 거듭제곱 값.

SQRT(4) => 2: 숫자의 제곱근 값

LOG(10, 100) => 2: 10이 밑수일 때 100의 로그 값
SQL 서버는 lOG(100, 10)해야 같은 결과가 나온다

LN(7.3890561) => 2: 숫자의 자연 로그 값(LOGe숫자)
SQL서버는 없음

날짜형 함수

SYSDATE / GETDATE(): 현재 날짜와 시각

EXTRACT('YEAR'|'MONTH'|'DAY' from d)
/ DATEPART('YEAR'|'MONTH'|'DAY', d)
: 날짜 데이터에서 연/월/일 출력. 시/분/초도 가능

TO_NUMBER(TO_CHAR(d, 'YYYY')) / YEAR(d)

TO_NUMBER(TO_CHAR(d, 'MM')) / MONTH(d)

TO_NUMBER(TO_CHAR(d, 'DD')) / DAY(d)

:날짜 데이터에서 연/월/일 출력
TO_NUMBER로 감싸주지 않으면 문자형으로 출력

• DB는 날짜를 숫자로 저장하기 때문에 덧셈/뺄셈이 가능하다

  • 날짜 + 숫자 = 날짜: day를 더함
  • 날짜 - 숫자 = 날짜
  • 날짜1 - 날짜2 = 날짜1에서 날짜2를 뺀 일수
  • 날짜 + 숫자/24 = 날짜: 날짜에 시간을 더한다(숫자는 기본적으로 day인데 그것에 24를 나눴으니까 시간을 더한 것)
    • 1/24/60: 1분
    • 1/24/(60/10): 10분
    • 1/24/60/60: 1초

• ADD_MONTHS(TRUNC(2020-06-16 14:30:30, 'MM'), -1*12)

  TRUNC 결과: 월(MM) 밑으로는 내림 => 2020-06-01 00:00:00

  여기에 -12개월을 더한다 = 1년을 뺀다

  => 2019-06-01 00:00:00

변환형 함수

• 명시적 데이터 유형 변환

  암시적 데이터 유형 변환: 성능 저하 및 에러 가능

TO_NUMBER(s) / CAST(표현식 AS 데이터타입[(길이)])
: 숫자로 변환 가능한 문자열을 숫자로 변환한다 / 표현식을 목표 타입으로 변환한다

TO_CHAR(n/d [, FORMAT]) / CONVERT(타입 [(길이)], 표현식 [, style])
: 주어진 format 형태 문자열로 / 주어진 스타일 형태의 목표 데이터 타입으로

TO_DATE(s [, FORMAT]) / CONVERT(타입 [(길이)], 표현식 [, style])

• TO_CHAR(SYSDATE, 'YYYY/MM/DD') => 2019/09/28
• TO_CHAR(SYSDATE, 'YYYY.MON,DAY') => 2019.9월, 토요일
• CONVERT(VARCHAR(10), GETDATE(), 111) => 2019-09-28(이게 111스타일 형태)
• 숫자형, 날짜형의 포맷은 벤더별로 다양하다

CASE 표현

if-then-else처럼 비교 연산이 가능하다

IF SAL > 2000
    THEN REVISED_SAL = SAL
    ELSE REVISED_SAL = 2000
END IF

CASE
    WHEN SAL > 2000 THEN SAL
    ELSE 2000
END AS REVISED_SAL

위의 두 표현은 같은 결과를 만든다

• CASE
      조건 (WHEN A = B / WHEN A > B THEN C)
    [ELSE 기본값]
  END

  ELSE 구문 생략시 기본값은 NULL

• CASE 표현식에는 searched_case 표현식이 있고 simple_case 표현식이 있다

  /* searched_case */
  
  CASE
      WHEN LOC = 'NY' THEN 'EAST'
      ELSE 'ETC'
  END AS AREA
  
  /* simple_case */
  
  CASE LOC
      WHEN 'NY' THEN 'EAST'
      ELSE 'ETC'
  END AS AREA

• 오라클은 WHEN A = B 조건만 사용 가능하고 DECODE 함수를 쓴다

  DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, ...] [, 기본값])

  표현식이 기준값1과 같다면 값1을 반환하고..

  모든 기준값이 일치하지 않다면 기본값을 반환한다(기본값이 없다면 NULL)

• 조건은 여러개 쓸 수 있다

  CASE LOC
      WHEN 'NEWYORK' THEN 'EAST'
      WHEN 'BOSTON'THEN 'EAST'
      ELSE 'ETC'
  END AS AREA

• 중첩이 가능하다(함수니까)

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

NULL 관련 함수

• NVL / IS NULL(표현식1, 표현식2)

  값이 NULL일 때 다른 값으로 출력하려고 할 때 유용

  표현식1이 NULL이면 표현식2의 값을 출력한다

  두 표현식의 결과 데이터 타입은 같아야 한다

  NVL함수를 다중행 함수의 인자로 사용하면 불필요한 부하가 발생한다
  (어차피 다중행함수는 NULL을 뺀 행에 대해 계산하기 때문)

• NULL과 공집합

  조건에 맞는 데이터가 한 건도 없는 경우가 공집합이다
  SELECT 1 FROM DUAL WHERE 1 = 2; 이게 공집합

  NVL/IS NULL은 공집합을 변환할 수는 없다

  스칼라 서브쿼리나 집계함수를 쓰면(MAX, AVG...) 공집합이 NULL처럼 표시된다
  그 때, NVL/IS NULL을 써서 변환하면 된다

• NULLIF(표현식1, 표현식2)

  표현식1 = 표현식2이면 NULL, 아니면 표현식1을 반환한다

  똑같은 데이터가 있으면 NULL을 표시하는 칼럼을 만들 때 쓴다

  SELECT NAME, MGR, NULLIF(MGR, 7098) AS NUIF FROM EMP;

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

  인수의 숫자는 제한되어 있지 않음

  표현식1부터 차례대로 훑어서 NULL이 아닌 최초의 표현식을 반환한다

  모두 널이면 널을 반환한다

• SELECT NVL(MAX(주문순번), 0) + 1 FROM 주문상세
  WHERE 주문번호 = V_주문번호;

  이 주문에서(WHERE로 같은 주문번호 제한을 뒀으니까) 이미 존재하는 주문순번 중 가장 큰 값에 1을 더하겠다

  이미 3건의 주문상세가 있다면 이번 주문상세는 4가 된다

  NVL은 맨 처음 주문상세는 기존 주문순번이 없으므로 그것을 대비함

  MAX(NVL(주문순번, 0)) + 1은 비효율적이다. 상기했듯이 다중행함수의 인자로 NVL을 쓰지 말자

WHERE절

원하는 데이터 검색을 위해 where를 쓴다

FROM절 뒤에 위치한다(조회하려는 데이터에 조건을 다는 거라서)

where절에 조건이 없는 FTS(Full Table Sca) 문장은 SQL 튜닝의 1차 검토 대상이 된다. (무조건 나쁘다는 건 아니다!)

• 사용되는 연산자

  • 비교 연산자: =, >, >=, <, <=

    부정비교 연산자: !=, ^=, <>(IOS표준, 모든 OS 가능),

    ​ NOT 칼럼명 = (와 같지 않다), NOT 칼럼명 > (보다 크지 않다)

  • SQL 연산자

    [NOT] BETWEEN a AND b: a와 b 사이(a, b 포함 - not이면 포함 안 함)

    [NOT] IN (list)

    LIKE '비교문자열'

    IS [NOT] NULL

  • 논리 연산자: AND, OR, NOT

  • 연산자 우선순위

    1. 괄호
    2. 비교 연산자, SQL 연산자
    3. NOT
    4. AND
    5. OR

• WHERE (year_date = '2014' AND month_date BETWEEN '11' AND '12') OR (year_date = '2015' AND month_date BETWEEN '01' AND '03')
  => 2014/11 ~ 2015/03

• 비교 연산자

  char, varchar같은 문자형 타입을 가진 칼럼을 특정 값과 비교하려면 특정값을 따옴표로 묶어야 한다.(숫자형은 불필요)

  두 char 타입을 비교하는 경우 길이가 다르면,
  짧은 쪽의 뒤에 공백을 추가해 길이를 같게 한 뒤 비교한다

  숫자유형 칼럼이 숫자로 변환 가능한 문자열(Alpha Numeric)과 비교되면 문자열을 숫자로 바꾼다
  where height >= '170': 문자형이지만 170으로 변환해서 비교한다

• SQL 연산자

  • IN 연산자의 다중 리스트

    MGR이면서 20번 부서이거나 CLERK이면서 30번 부서?

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

    WHERE JOB IN ('MGR', 'CLERK') AND DEPTNO IN (20, 30);은 직업이 MGR이거나 CLERK이고 부서가 20이거나 30인 경우이므로 결과가 달라진다

  • LIKE

    WHERE A LIKE 'abc'; A의 값이 abc와 같을 때

    • 와일드카드: 문자를 대신해서 사용하는 특수문자

      % 0개 이상의 문자

      _ 문자 1개

    WHERE NAME LIKE '장%'; 장씨 성을 가진 사람

    

  • IS NULL

    NULL은 값이 존재하지 않으므로 어떤 값보다 크거나 작지도 않고
    공백이나 0과 달리 비교도 불가능하다

    수치 연산은 NULL값을 리턴한다

    비교 연산은 FALSE를 리턴한다

    WHERE POSITION = NULL은 거짓을 리턴하므로 데이터를 찾을 수 없다

    WEHRE POSITION IS NULL을 해야 NULL값을 찾을 수 있다