3. SQL 기본

1. 관계형 DB

DB
데이터를 일정한 형태로 저장해 놓은 것
DBMS를 이용하여 효율적인 데이터 관리와 데이터 손상 복구 가능\

종류

• 계층형 DB : 트리 형태의 자료구조에 데이터 저장, 1:N 관계 표현

• 네트워크형 DB : 오너와 멤버 형태로 데이터 저장, M:N 관계 표현

• 관계형 DB : 릴레이션에 데이터 저장, 집합 연산과 관계 연산 가능

관계형 DB(RDB;Relational Database)

1. 정규화를 통해 이상현상 및 중복 데이터 제거

2. 동시성 관리와 병행 제어를 통해 데이터 동시 조작 가능

집합 연산

• 합집합(Union)

• 차집합(Difference)

• 교집합(Intersection)

• 곱집합(Cartesian Product) : 각 릴레이션에 존재하는 모든 데이터를 조합

관계 연산

• 선택 연산(Selection) : 조건에 맞는 행(튜플) 조회

• 투영 연산(Projection) : 조건에 맞는 컬럼(속성) 조회

• 결합 연산(Join) : 공통 속성을 사용하여 새로운 릴레이션 생성

• 나누기 연산(Division) : 공통 요소를 추출, 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거

SQL

• RDB에서 사용하는 언어

• 데이터 조회,입력,수정,삭제 기능 제공

1. DML(Data Manipulation Language, 데이터 조작어) ISUD

   1. SELECT: 조회

   2. INSERT, UPDATE, DELETE: 변형

2. DDL(Data Definition Language, 데이터 정의어) CADRT

   1. 데이터 구조에 관련 명령어

   2. CREATE, ALTER, DROP, RENAME

3. DCL(Data Control Language, 데이터 제어어)

   1. DB 접근 권한 부여 및 회수

   2. GRANT, REVOKE

4. TCL(Transaction Control Language, 트랜잭션 제어어)

   1. DML로 조작한 결과를 논리적인 작업 단위 별로 제어

   2. COMMIT, ROLLBACK

Table 테이블 RDB의 기본단위, 데이터를 저장하는 객체, column-row 2차원 구조
Field 필드 : 컬럼과 행이 겹치는 하나의 공간

Fig. 22 선수와 관련된 데이터를 저장할 때 모든 데이터를 하나의 테이블로 저장하지 않는다. [그림 Ⅱ-1-5]를 보면 선수와 관련된 데이터를 선수 테이블과 구단 테이블이라는 복수의 테이블로 분할하여 저장하고 있다.

그리고 분할된 테이블은 그 칼럼의 값에 의해 연결된다. 이렇게 테이블을 분할하여 데이터의 불필요한 중복을 줄이는 것을 정규화(Normalization)라고 한다. 데이터의 정합성 확보와 데이터 입력/수정/삭제시 발생할 수 있는 이상현상(Anomaly)을 방지하기 위해 정규화는 관계형 데이터베이스 모델링에서 매우 중요한 프로세스이다.

2. DDL : CADRT

Fig. 23 데이터 유형은 데이터베이스의 테이블에 특정 자료를 입력할 때, 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준이라고 생각하면 된다

데이터 타입(Oracle,SQL Server 형식, 괄호안은 조건)

• CHAR(L) : 고정 길이 문자열, 할당된 변수 값의 길이가 L 이하 일때 차이는 공백으로 채움

• VARCHAR2(L),VARCHAR(L) : 가변 길이 문자열, 할당되는 변수 값의 길이의 최대값이 L임. 문자열은 가능한 최대 길이로 설정

• NUMBER(L,D) : 숫자형(L은 전체자리수, D는 소수점 자리수)

  • SQL Server은 NUMERIC, DECIMAL, FLOAT, REAL 등 존재

• DATE, DATETIME : 날짜형, 데이터 크기 지정이 필요하지 않음

2.1 CREATE TABLE

• 테이블 및 컬럼 명명 규칙

  • 알파벳

  • 숫자

  • _ 언더바

  • 달러 $

  • 샵 #

• 대소문자 구분하지 않음

• 단수형 권고

제약조건

• PRIMARY KEY

  • 테이블 당 하나의 기본키만 정의 가능

  • 기본키 생성시 DBMS가 자동으로 인덱스를 생성

  • NULL 불가

• FOREIGN KEY

  • 으로 다른 테이블의 기본키를 외래키로 지정

  • 참조 무결성 제약 옵션 선택가능

  • ALTER TABLE 테이블명 ADD CONSTRAINT 컬럼명 FOREIGN KEY (컬럼명) REFERENCES

• UNIQUE KEY

  • 행 데이터를 식별하기 위해 생성

  • NULL 가능

• DEFAULT

  • DEFAULT 값으로 기본값 설정

• NOT NULL

  • NULL : 아직 정의되지 않은 값 또는 현재 데이터를 입력하지 못하는 값 수치연산NULL 비교연산FALSE

• CHECK

  • 입력값의 종류 및 범위 제한

DESCRIBE 테이블명 sp_help ‘dbo.테이블명’ 테이블 구조 확인
DESCRIBE 테이블명 이 ANSI/ISO 표준

2.2 ALTER TABLE

테이블의 컬럼 관련 변경 명령어

1. 컬럼 추가

   1. ALTER TABLE 테이블명 ADD (칼럼명 데이터타입)

   2. 마지막 컬럼으로 추가됨

   3. 컬럼 위치 지정 불가

2. 컬럼 삭제

   1. ALTER TABLE 테이블명 DROP COLUMN 칼럼명

   2. 삭제 후 복구 불가

3. 컬럼 설정 변경

   1. ALTER TABLE 테이블명 MODIFY (칼럼명 데이터타입 제약조건)

   2. NULL만 있거나 행이 없는 경우에만 컬럼 크기 축소 가능

   3. NULL만 있을떄 - 데이터 유형도 변경 가능

   4. NULL이 없으면 NOT NULL 제약조건 추가 가능

   5. 기본값 변경 작업 이후 발생하는 데이터에 대해서만 기본값이 변경됨

4. 컬럼명 변경

   1. ALTER TABLE 테이블명 RENAME COLUMN 칼럼명

   2. ANSI/ISO 표준에 명시된 기능이 아님

5. 제약조건 추가

   1. ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건

6. 제약조건 제거

   1. ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건

2.3 RENAME TABLE

ALTER TABLE 테이블명 RENAME TO 테이블명도 가능\

2.4 DROP TABLE

테이블의 데이터와 구조 삭제, 복구 불가
CASCADE CONSTRAINT 옵션으로 관련 테이블의 참조 제약조건도 삭제하여 참조 무결성을 준수할 수 있음. CREATE TABLE에서 ON DELETE CASCADE 옵션으로도 동일 기능 실현 가능

2.5 TRUNCATE TABLE

테이블의 전체 데이터 삭제 <-> DROP TABLE은 테이블 자체를 제거함
로그를 기록하지 않기 때문에 ROLLBACK 불가

3. DML : IUDS

• INSERT

  • 데이터 입력

  • INSERT INTO 테이블명 (칼럼명, …) VALUES (필드값, …)

  • INSERT INTO 테이블명 VALUES (필드값, …)

• UPDATE

  • 데이터 수정

  • UPDATE 테이블명 SET 칼럼명=필드값;

• DELETE

  • 데이터 삭제

  • DELETE FROM 테이블명 WHERE 조건절

  • DELETE FROM 테이블명

  • DELETE로 데이터를 삭제해도 테이블 용량은 초기화되지 않음

  • TRUNCATE로 삭제하면 초기화됨

  • DROP은 객체 삭제 명령어

• SELECT

  • 컬럼별 데이터 선택 : SELECT 칼럼명 FROM 테이블명

  • 데이터 중복 없이 선택 : SELECT DISTINCT 칼럼명 FROM 테이블명

  • 전체 컬럼의 데이터 선택 : SELECT * FROM 테이블명

  • ALIAS

    • SELECT 컬럼명 AS ‘별명’

    • FROM 테이블명 별명 : 쿼리 내에서 사용할 테이블명 설정, 컬럼명이 중복될 경우 SELECT절에서 앨리어스 필수

• 합성 연산자(문자열)

  • +, CONCAT(ORACLE : ||로도 가능)

• DUAL : 오라클 기본 더미 테이블, 연산 수행을 위해 사용됨

4. TCL : UID

트랜잭션 DB의 논리적 연산 단위, 하나 이상의 SQL문을 포함함
데이터 무결성 보장을 목적으로 함\

1. 영구 변경 전 확인 + 2) 연관작업 동시 처리 가능
   밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작
   하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함
   분할할 수 없는 최소의 단위 DB를 정상적으로 종료하면 자동 커밋, 앱 등의 이상으로 DB접속이 단절되면 자동 롤백

4-1 COMMIT

데이터를 DB에 영구적으로 반영하는 명력어
커밋 시 트랜잭션이 완료되어 LOCKING이 해제됨
SQL SERVER는 기본적으로 자동 커밋

COMMIT 전

• 데이터 변경이 메모리 버퍼에만 영향을 받았기 때문에 복구 가능

  • NOLOGGING 옵션 사용시 버퍼 캐시의 기록을 생략하여 입력 성능이 향상됨

• 사용자는 SELECT 절로 결과를 확인할 수 있으나 다른 사용자는 현재 결과를 볼 수 없음

• 변경된 행에 LOCKING이 설정되어 다른 사용자가 변경할 수 없음

  • LOCKING이 안 걸린 상태일 때 여러 사용자가 데이터를 변경하면 상관없음

COMMIT 후

• 변경 사항이 DB에 반영

• 이전 데이터 복구 불가

• 모든 사용자가 결과를 볼 수 있음

• LOCKING이 해제되어 다른 사용자가 행을 조작할 수 있음

4-2 ROLLBACK

트랜잭션 시작 이전의 상태로 되돌리는 명령어
COMMIT 이전 상태로 돌려줌
ROLLBACK 시 LOCKING이 해재됨

• SAVEPOING : 트랜잭션 일부만 롤백 할 수 있도록 중간상태를 저장하는 명령어

  • ROLLBACK TO 저장점명 : 해당 저장점 상태로 되돌림

  • 동일한 저장점명이 있으면 최후 저장점이 유효

• SQL SERVER에서는 BEGINE TRAN으로 명시해야 하능

5. DCL

유저를 생성하거나 권한을 제어하는 명령어, 보안을 위해 필요함

• GRANT : 권한 부여, GRANT 권한 ON 오브젝트 TO 유저명

• REVOKE : 권한 제거, REVOKE 권한 ON 오브젝트 TO 유저명

권한

• SELECT, INSERT, UPDATE, ALTER, ALL - DML 관련 권한

• REFERENCES : 지정된 테이블을 참조하는 제약조건을 생성하는 권한

• INDEX : 지정된 테이블에서 인덱스를 생성하는 권한

ORACLE 유저

• SCOTT :

• SYS :

• SYSTEM :

ROLE

• 권한의 집합

• 권한을 일일이 부여하지 않고 ROLE로 편리하게 여러 권한을 부여할 수 있음

Fig. 24 ROLE에는 시스템 권한과 오브젝트 권한을 모두 부여할 수 있으며, ROLE은 유저에게 직접 부여될 수도 있고, 다른 ROLE에 포함하여 유저에게 부여될 수도 있다. Oracle에서는 기본적으로 몇 가지 ROLE을 제공하고 있다. 그 중 가장 많이 사용하는 ROLE은 CONNECT와 RESOURCE

CONNCECT ROLE의 권한은 CREATE SESSION 뿐이다. 나머지는 전부 RESOURCE다.

6. WHERE 조건절

SELECT [DISTINCT/ALL] 칼럼명 [ALIAS명] FROM 테이블명 WHERE 조건식

WHERE 절은 FROM 절 다음에 위치하며, 조건식은 아래 내용으로 구성된다
칼럼(Column)명 (보통 조건식의 좌측에 위치) - 비교 연산자 - 문자, 숫자, 표현식 (보통 조건식의 우측에 위치) - 비교 칼럼명 (JOIN 사용시)

Fig. 25 LIKE의 사전적 의미는 ‘~와 같다’이다. 따라서 위와 같은 경우라면 비교 연산자인 ‘=’을 사용해서 작성해도 같은 결과를 얻을 수 있을 것이다. 그러나 만약 “장”씨 성을 가진 선수들을 조회할 경우는 어떻게 할까? 이런 문제를 해결하기 위해서 LIKE 연산자에서는 와일드카드(WildCard)를 사용할 수 있다. 와일드카드(WildCard)란 한 개 혹은 0개 이상의 문자를 대신해서 사용하기 위한 특수 문자를 의미하며, 이를 조합하여 사용하는 것도 가능하므로 SQL 문장에서 사용하는 스트링(STRING) 값으로 용이하게 사용할 수 있다.

6-1 부분 범위 처리

1. ROWNUM(oracle) : SQL 처리 결과 집합의 각 행에 임시로 부여되는 번호, 조건절 내에서 행의 개수를 제한하는 목적으로 사용함

2. TOP(SQL Server) : 출력 행의 수 제한 함수, TOP (N) 로 n개 행 출력, 개수 대신 비율로도 제한 가능. order by 절이 없으면 rownum과 top의 기능이 같음

7. 함수

함수명(컬럼이나 표현식 [,arg1, arg2, …]) 함수3 (함수2 (함수1 (칼럼이나 표현식 [, Arg1]) [, Arg2]) [, Arg3 ])

함수는 크게 벤더(oracle, mysql…) 에서 제공하는 내장 함수와 사용자가 정의할 수 있는 함수로 나눌 수 있다. 기본적으로 내장함수는 sql을 더욱 강력하게 해주고, 데이터 값을 간편하게 조작하는데 사용된다. 내장 함수는 또 단일행 값이 입력되는 단일행 함수(single-row function)과 여러 행의 값이 입력되는 다중행 함수(multi-row function)로 나눌 수 있다. 다중행 함수는 다시 집계함수, 그룹함수, 윈도우함수로 나눌 수 있다.

함수는 아무리 입력되는 값이 많아도 출력은 하나만 되는 m:1 관계라는 것이 중요한 특징이다.

7-1 단일행 함수

Fig. 26 단일행 함수의 중요한 특징은 다음과 같다.

• SELECT, WHERE, ORDER BY 절에 사용 가능하다.\

• 각 행(Row)들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.\

• 여러 인자(Argument)를 입력해도 단 하나의 결과만 리턴한다.\

• 함수의 인자(Arguments)로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있다. \

• 특별한 경우가 아니면 함수의 인자(Arguments)로 함수를 사용하는 함수의 중첩이 가능하다.

Fig. 27 DATE 변수가 데이터베이스에 어떻게 저장되는지 살펴보면, 데이터베이스는 날짜를 저장할 때 내부적으로 세기(Century), 년(Year), 월(Month), 일(Day), 시(Hours), 분(Minutes), 초(Seconds)와 같은 숫자 형식으로 변환하여 저장한다. 날짜는 여러 가지 형식으로 출력이 되고 날짜 계산에도 사용되기 때문에 그 편리성을 위해서 숫자형으로 저장하는 것이다. 데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다. 즉, 날짜에 숫자 상수를 더하거나 뺄 수 있다.

Fig. 28 벤터의 고유항목이 많기 때문에 암기가 따로 필요하다.

Fig. 29 NVL (NULL 판단 대상,‘NULL일 때 대체값’)
ISNULL (NULL 판단 대상,‘NULL일 때 대체값’)
NULLIF (a, b) : return a==b -> NULL, a!=b -> a COALESCE (a, b, c …) : return null이 아닌 첫번째 값

7-2 조건문

IF-THEN_ELSE

Fig. 30 CASE 표현을 하기 위해서는 조건절을 표현하는 두 가지 방법이 있고, Oracle의 경우 DECODE 함수를 사용할 수도 있다.

• ELSE 생략시 NULL 출력\

• ※ ‘CASE WHEN NULL THEN 출력값 ELSE 기본값’은 조건이 없으므로 모든 행에서 기본값 출력 (일반적으로 ‘WHEN 칼럼 IS NULL’로 수정 필요)

• DECODE : Oracle 함수, 기준값n이면 출력값n 출력

8. 집계 함수 : Aggregate Function

집계 함수명 ( [DISTINCT | ALL] 칼럼이나 표현식 )
ALL : Default 옵션이므로 생략 가능함
DISTINCT : 같은 값을 하나의 데이터로 간주할 때 사용하는 옵션임

여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수를 말한다. ROLLUP, CUBE, GROUPING SETS같은 함수는 2장 5절에서 WINDOW 함수는 2장 6절에서 더 구체적으로 설명이 들어갈 것이고 여기서는 기본적인 집계 함수들을 우선적으로 다루게 된다.

Fig. 31 ALL,DISTINCT는 전체를 출력해주고 중복을 제외하고 출력한다.
COUNT 함수를 제외하고는 전부 NULL값을 제외하고 계산한다.
COUNT도 표현식이 들어오면 NULL 을 제외하고 연산을 해준다.
GROUP BY 기준으로 나눠진 각 그룹을 연산하는 역할
하나 이상의 행을 그룹으로 묶어 연산하기 위한 함수이다.

8-1 GROUP BY

SELECT [DISTINCT] 칼럼명 [ALIAS명] FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼(Column)이나 표현식] [HAVING 그룹조건식] ;

pandas에서 많이 사용하던 그룹바이다. 데이터를 특정 컬럼을 기준으로 그룹화시키는 명령어이다. 그룹을 나누고 다시 그 그룹 안에서 세부그룹으로 나눌 수 있다. ALIAS는 사용불가능 하다.

알아야 할 것은 GROUP BY 구에 있는 컬럼은 반드시 SELECT 절에도 존재해야 한다는 것이다.
GROUP BY 구에도 다양한 함수가 사용 가능하다. SUBSTR, INSTR 등이 가능ㅎ다ㅏ.

8-2 HAVING

일반적으로 GROUPBY 절에 의한 집계 데이터의 출력 조건을 건다.
때문에 일반적으로 GROUP BY 뒤에 위치한다.

GROUP BY 절에 의한 집계 데이터에 출력 조건을 건다.
WHERE 절과 헷갈리지 않게 조심해야한다.
처음부터 테이블 자체에 조건을 걸고 싶으면 WHERE를 사용하고
그룹별로 묶인 컬럶에 조건을 걸고 싶다면 HAVING을 사용해야한다.

9. ORDER BY

SELECT 칼럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 칼럼(Column)이나 표현식]
[HAVING 그룹조건식]
[ORDER BY 칼럼(Column)이나 표현식 [ASC 또는 DESC]] ;

위의 SELECT문은 기본적으로 테이블에서 출력 대상이 아닌 것은 제거하고 그룹핑해서 그룹핑된 값이 조건에 맞는 데이터를 계산 및 출력하고 정렬한다... 라는 것이고 선택적으로 []에 있는 것들은 사용하지 않아도 가능하다.

특정 컬럼을 기준으로 정렬하는 명령문이다. 기본 정렬은 오름차순이다.
컬럼명 대신 SELECT 절에서 사용한 ALIAS명이나 순서를 나타내는 정수로 사용 가능하다.
문장의 제일 마지막에 위치한다.

oracle은 null을 가장 큰 값으로 취급하며, 반대로 SQL Server에서는 반대로 가장 작은 값으로 취급한다.

10. JOIN

앞에서 배운 정규화를 통해서 우리는 불필요한 데이터의 정합성을 확보하고, 이상현상의 발생을 피하기 위해 테이블을 분할하여 생성했다. 사실 데이터웨어하우스 모델처럼 하나의 테이블에 모든 데이터를 집중시켜놓고 그 테이블로부터 필요한 데이터를 조회할 수 있다. 하지만 이렇게 됐을 겨우, 가장 주요한 데이터의 정합성에 더 큰 비용을 지불해야하며, 데이터 추가-삭제-수정 작업에도 더 많은 품이 든다. 조회도 속도면에서 장점이 있기는 힘들다. 이러한 상황에서 특정 요구조건을 만족하는 분할된 테이블들을 모으는 것이 join의 역할이다.

여러 테이블을 연결 또는 결합하여 데이터 출력
일반적으로 PK, FK의 연관성에 의해 성립된다

• 등가조인(EQUI JOIN) : 두 테이블의 컬럼 값이 정확하게 일치하는 경우, 대부분 PK,FK 관계를 기반으로 함

  • SELECT 칼럼s FROM 테이블1 A, 테이블2 B WHERE A.칼럼명=B.칼럼명;

  • SELECT 대상 컬럼이 두 테이블 모두에 있는 경우 ALIAS를 지정해야함(양쪽 다해도됨)

• 비등가조인(Non EQUI JOIN) : 두 테이블의 컬럼 값이 정확하게 일치하지 않는 경우, 부등호나 BETWEEN 연산자를 통해 조인