데이타베이스 CH. 6 - Basic SQL

Alpha, Orderly·2023년 10월 12일

데이터베이스

목록 보기

5/13

배경

SQL

Tuple Calculus 로부터 유래하였다.

관계형 모델에서 데이터 조작을 위한 선언적 데이터베이스 쿼리 언어를 제공하기 위해 Edgar F. Codd가 관계형 모델의 일부로 만들어 도입되었다.
최초로 나온것은 SQUARE 언어이다.

SEQUEL

IBM의 SYSTEM R이라는 언어의 인터페이스로 사용하기 위해 디자인 및 구현 되었다.
Relational DBMS 의 기준 언어가 되었다.
구문을 사용하여 관계형 데이터 모델을 "비공식적" 또는 "실용적"으로 렌더링합니다.

Schema & Catalog

DDL : Data definition language

Schema

동일한 데이터베이스에 존재하는 테이블을 묶어주는 개념이다.
이름으로 구별할수 있다.
스키마를 소유하는 사람의 정보인 Authorization identifier,
스키마 내부 각 원소의 정보를 포함한다 ( CREATE TABLE )

생성하기

CREATE SCHEMA COMPANY AUTHORIZATION ‘Joshua'
-- Joshua 가 가지는 COMPANY 스키마의 생성

권한이 있어야 생성 가능하다.

Catalog

이름을 가지는 스키마 컬렉션
INFORMATION_SCHEMA 라는 특별한 스키마를 가진다.
- 카탈로그의 모든 스키마 및 이러한 스키마의 모든 요소 설명자에 대한 정보 제공
동일 카탈로그에 존재시에만 Integrity constraint 가 성립한다.
동일한 카탈로그 내에서 스키마는 유형/도메인 정의와 같은 특정 요소를 공유할 수 있습니다.

테이블 생성

CREATE TABLE

새로운 Relation 을 만든다.
이름과 안에 들어가는 Attribute의 이름, 타입, 제약조건을 명시한다.
Key, Entity integrity, Regeretial integrity constraint 는 ALTER TABLE을 통해 차후 추가가 가능하다.
스키마를 적어둘수도 있다.

• Can optionally specify “schema”:
  • CREATE TABLE COMPANY.EMPLOYEE ... (explicit)
  • Or simply, CREATE TABLE EMPLOYEE... (implicit)

FOREIGN KEY 에러

만약 FK가 참조하려는 PK를 가지는 테이블이 없을시 FK는 지정될수 없다.
이 때문에, FK를 지정하지 않고 테이블을 전부 만든 뒤 ALTER TABLE을 통해 연결하는 방식을 채용한다.
자기 자신을 참조하려는 경우 에러가 발생할수 있다.

EMPLOYEE 테이블에 FK를 나중에 지정하는 예시

BASE TABLE

DBMS에 의해 실제/물리 적으로 생성되는 테이블/Relation

VIRTUAL RELATIONS ( 가상 테이블 )

1개 혹은 그 이상의 테이블에서 SELECT 문장으로 테이블을 조인하든지 자신이 보고자 하는 컬럼을 넣어서 만든 가상의 테이블

특징

뷰를 만들 때 사용하는 테이블의 변경되면 VIEW도 자동으로 변경됩니다.
뷰는 검색이 자유롭지만 삽입,삭제,갱신은 제약이 있습니다.

장점

원하는 정보만 보여줄수 있어 보안이 강화된다.
쿼리를 단순화할수 있다.

CREATE OR REPLACE VIEW TEST_TABLE_VW
        AS SELECT A.ID
                        ,A.NAME
                        ,A.AMT
                        ,B.CHECK_YN
           FROM TEST_TABLE_ONE A,
                     TEST_TABLE_TWO B
                 WHERE A.IDX = B.IDX

Attribute Data Type

숫자

정수

SQL - INTEGER, INT, SMALLINT
ORACLE - NUMBER ( DECIMAL의 대체 가능 )

실수

SQL - FLOAT, REAL, DOUBLE PRECISION

DECIMAL

형식을 가지는 숫자를 선언하기 위함

DECIMAL(i, j)
DEC(i, j)

i : 유효숫자 갯수
j : 소수점 이후 나올수 있는 자리수

문자(열)

고정 길이 문자열

CHAR(n) / CHARACTER(n)
n : 최대 문자열 길이
짧은 문자열은 우측에 공백으로 채워진다.

가변 길이 문자열

VARCHAR(n) / CHAR VARYING(n) / CHARACTERVARYING
CLOB(CHARACTER LARGE OBJECT)
- 매우 큰 문자열 저장하기 위해 사용한다.
- K - Kilobyte, M - megabate, G - Gigabyte
- CLOB(20M): up to 20MByte

가변 vs 고정

가변 타입은 저장될때 크기가 가변적이라는것이다.
고정 타입은 저장될때 크기가 모두 같다.
고정 타입이 더 빠르나 공간을 더 많이 차지한다.

바이너리 타입

고정길이

BIT(n)
- n : 저장가능한 최대 비트수

가변길이

BIT VARYING(n)
큰 파일의 경우 BLOB(BINARY LARGE OBJECT) 사용 가능
- K - Kilobyte, M - megabate, G - Gigabyte
- BLOB(20M): up to 20MByte

불대수

TRUE / FALSE
BOOLEAN

날짜

DATE
기본적으로 YYYY-MM-DD 의 형식을 가진다.
- EX. 2023-10-02
Oracle에서는 to_date('2022-10-22', 'yyyy-mm-dd')와 같이 저장한다.
반드시 가능한 날짜만 저장 가능하다.

시간

TIME
기본적으로 HH:MM:SS 의 형식을 가진다.
반드시 가능한 시간만 저장 가능하다.
부등호를 이용, 서로 비교가 가능하다.

TIMESTAMP

날짜와 시강늘 포함한다.
초보다 더 자세한 6자리 숫자를 포함한다.
TIMESTAMP‘2014-09-27 09:12:47.64'
WITH TIME ZONE 으로 타임존 설정 가능

INTERVAL

특정 날짜/시간/타임스탬프에서 특정기간을 줄이거나 늘리기 위해 사용되는 타입
40달 (MONTH) / 3년 4개월 ('3-4' == YEAR TO MONTH)

DOMAIN

데이터 타입 대신 칼럼에 선언될수 있다.
- 데이터 타입 변경이 쉬워진다.
- 스키마가 읽기 편해진다.

CREATE DOMAIN SSN_TYPE AS CHAR(9)
-- CHAR(9)를 SSN_TYPE으로

TYPE

사용자 지정 타입을 만들기 위해 사용한다.
Attribution을 정할때 특정한 이름을 지을수 있다.
OOP를 지원한다.

CREATE TYPE type_demo as object {
	customer_id NUMBER(6),
    customer_last_name VARCHAR2(20)
};

SQL Constraint

SQL의 제약조건, 테이블 생성시 적시한다.

Key constraint
- Primary Key는 Unique 해야 한다.
Entity Integrity Constraint
- Primary Key는 NULL이 되어선 안된다.
Referential Constraint
- FK가 있으면 그에 해당하는 PK가 있어야 한다.

Attribute Constraint

DEFAULT

기본값을 정한다.

ROW DECIMAL(4, 10) DEFAULT 11.1

NOT NULL

NULL값이 되면 안된다.

CHECK

들어오는 값을 확인한다.

Dnumber INT NOT NULL CHECK (Dnumber > 0 and Dnumber < 10)
-- Dnumber가 0초과 10미만인지 확인한다.

PRIMARY KEY

PK 지정

Dnumber INT PRIMARY_KEY
-- 하나의 Column을 PK로 지정
PRIMARY KEY(State, Number)
-- 여러 Column을 PK로 지정

UNIQUE

Candidate Key를 지정한다.
값이 중복되지 않아야 한다.

FOREIGN KEY

Refrential Integrity 위반시 INSERT/DELETE를 reject 가능하다.

옵션

SET NULL, CASCADE, (SET DEFAULT - Oracle에선 미지원)
CASCADE : 관계의 일관성을 유지하기 위해 필요한 옵션
- 참조되는 튜플 제거시 참조하는 튜플도 제거하게 할수 있다.
  - ON DELETE CASCADE
- 참조되는 튜플 업데이터시 참조하는 튜플이 업데이트 되게 할수 있다.
  - ON UPDATE CASCADE
- 참조되는 튜플 제거시 참조하는 튜플의 FK를 NULL이 되게 할수 있다.
  - ON DELETE SET NULL

CONSTRAINTS

제약조건에 이름을 짓기
나중에 ALTER TABLE 할때 쉽다
CREATE TABLE에 작성한다.

CONSTRAINT [제약조건명] [제약조건](컬럼명)
CONSTRAINT PK_B_NUM PRIMARY KEY(b_num);
ALTER TABLE board ADD CONSTRAINT PK_B_NUM PRIMARY KEY(b_num);

CREATE TABLE TEST(
    id NUMBER(5),
    name VARCHAR(20),
    title VARCHAR(30),
    CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES apple (id)
);
-- FK를 CONSTRAINT 로 지정하는 예시

CHECK

조건에 맞는지 안 맞는지를 확인한다.

CHECK 조건

개별 튜플 혹은 전체 테이블에 대해 적용 가능하다.

SQL QUERY

기본 문법

SELECT * -- 혹은 가져올 Attribution 이름
FROM {테이블이름} 
WHERE {조건} -- 튜플을 선택하는 조건
ORDER BY {Attribution 이름} (DESC / 내림차순으로 할때 사용 ) -- 특정 Attribution으로 정렬
GROUP BY {Attribution 이름} -- 집계 되는 대상을 그룹으로 지정한다.
HAVING {조건} -- GROUP을 선택할때 조건이다.