데이터 모델, 테이블 생성하기, ALTER, DROP

• 데이터 모델

현실 세계의 정보들을 컴퓨터에 표현하기 위해서 단순화, 추상화하여 체계적으로 표현한 개념적 모형

 

구성 요소 3가지

개체(개념이나 정보 단위 같은 현실 세계의 대상체), 속성, 모델

 

데이터 모델에 표시할 요소 3가지

 

(1) 구조

논리적으로 표현된 개체 타입들 간의 관계로서 데이터 구조 및 정적 성질 표현

 

ex. 테이블, 필드(속성), 레코드(튜플), 릴레이션

 

(2) 연산

데이터베이스에 저장된 실제 데이터를 처리하는 작업에 대한 명세

데이터베이스를 조작하는 기본 도구

 

ex. SELECT, INSERT, DELETE, JOIN, UPDATE

 

(3) 제약 조건

데이터베이스에 저장될 수 있는 실제 데이터의 논리적인 제약 조건

 

ex. PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE

 

• SQL 주요 용어

relational data model  -  SQL

relation  -  table

attribute  - column

tuple  -  row

domain  - domain

 

• SQL에서 relation이란?

multiset(=bag) of tuples @SQL

중복된 tuple을 허용한다

 

 

• SQL은 RDBMS의 표준 언어이지만 실제 구현에 강제가 없기 때문에

RDBMS마다 제공하는 SQL의 스펙이 조금씩 다르다

 

• 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들자

사용할 RDBMS는 MySQL (InnoDB)

 

• database 정의하기

show databases;

 

create database company;

 

show databases; # 목록에서 company 확인 가능

 

SELECT database(); # 어떤 데이터베이스가 선택된 상태인지 확인 가능, 선택 안 돼 있으면 NULL

 

USE company; # 사용할 데이터베이스 선택

 

SELECT database(); # company 로 나옴

 

DROP DATABASE company; # 지우고 싶을 때

 

• mySQL에서는 DATABASE와 SCHEMA가 같은 뜻을 가진다

다른 RDBMS에서는 의미가 다르다

 

PostgreSQL에서는 SCHEMA가 DATABASE의 namespace를 의미한다

데이터베이스 안에서 스키마가 정의되고,

스키마 안에서 테이블이 정의된다

 

ex. company_db (Database)

├── hr (Schema)

│   ├── employee (Table)

│   ├── department (Table)

│

├── sales (Schema)

│   ├── customer (Table)

│   ├── orders (Table)

 

 

 

• IT 회사 RDB 만들기

 

부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스

 

 

컬럼명에 언더바가 있으면 primary key

 

create table DEPARTMENT (

id INT PRIMARY KEY,

name VARCHAR(20) NOT NULL UNIQUE,

leader_id INT

)

 

PostgreSQL에는 SMALLINT, INT/INTEGER, BIGINT만 있다

 

• 부동 소수점 방식

floating vs double

floating: 소수점 위치가 떠다닌다는 뜻

고정 소수점과 달리, 소수점이 고정되어 있지 않음

ex. 3.14159, 0.000045, 1234567.89 모두 표현 가능

 

double: Double Precision Floating Point

Float보다 더 많은 비트를 사용, 정밀도가 2배

 

• 고정 소수점 방식

mysql은 DECIMAL과 NUMERIC의 차이가 없다

DECIMAL(precision, scale)

scale = 소수점 이하 몇 자리까지 표현할거냐

precision = 전체 몇 자리인지

 

SQL 표준에서는 numeric과 decimal의 차이가 있다

numeric: 5자리 넘으면 저장 안 함

decimal: 5자리 넘어도 저장한다

 

이름 유래?

numeric => 숫자 자체의 정확한 표현에 중점, 수학적/정확성 강조

decimal => 10진수 시스템 기반 표현 강조, 계산 체계 강조

 

CREATE TABLE financial_data (

    id INT PRIMARY KEY,

    salary DECIMAL(10, 2),     -- 고정 소수점 (정확하게 소수 2자리)

    bonus_rate FLOAT,          -- 부동 소수점 (근사값 허용)

    annual_growth DOUBLE,      -- 더 정밀한 부동 소수점

    description VARCHAR(100)

);

 

 

 

고정 크기 문자열: 저장할 때는 스페이스가 있지만, 읽을 때는 스페이스 떼고 줌

 

char vs varchar

 

RDBMS마다 다르다

postgreSQL: varchar 권장

mySQL: 전화 번호 처럼 고정 크기면 char 권장

 

mySQL에서 varchar보다 큰 크기 저장하려면 MEDIUMTEXT나 LONGTEXT

 

 

hhh: 경과된 시간 표시(하루만에 끝나지 않을 수도 있기 때문)

 

timestamp: UTC(표준시간대)로 저장한다

mysql이 설치되어 있는 서버의 timezone이나

mysql 내부적으로 timezone을 설정할 수 있다

 

mysql에서는,

timezone의 시간 대비 표준 시간대로 변환을 해서 표준 시간대로 저장한다

다시 읽어올 때는 표준 시간에 현재의 timezone을 반영해서 timezone 기준으로 변환하여 리턴한다

timestamp에 time-zone이 반영되어 있다는 것을 기억하자

 

 

 

암호화 키: 보통 byte-string

boolean-postgreSQL에는 있다

 

 

• primary key

table의 tuple을 식별하기 위해 사용, 하나 이상의 attribute로 구성

primary key는 중복된 값을 가질 수 없으며, null도 값으로 가질 수 없다

primary key가 여러 컬럼으로 구성된 경우, 일부만 null인 것도 안 된다

 

 

 

• Key constraints: UNIQUE

 

UNIQUE로 지정된 attribute(s)는 중복된 값을 가질 수 없다

단, Null은 중복을 허용할 수도 있다 (RDBMS마다 다르다) - MySQL과 postgreSQL은 허용, ms sql server에서는 허용하지 않는다(딱 하나만 Null 가능)

 

 

 

• NOT NULL constraint

 

attribute가 NOT NULL로 지정되면 해당 attribute는 NULL을 값으로 가질 수 없다

 

attribute level로만 쓸 수 있다

 

 

NOT NULL  UNIQUE를 같이 쓰는 경우가 많다

unique가 null 중복을 허용하는 경우

 

 

• default

 

새로운 튜플을 저장할 때, 해당 attribute에 대한 값이 없다면 default 값으로 저장한다

 

 

 

• referential integrity constraint: FOREIGN KEY

 

외래키 > 다른 테이블에 있는 key 값을 값으로 가진다.

Primary key 여야하는 것은 아니지만 고유하게 식별될 수 있어야 한다.(unique)

 

 

 

restrict: 참조되고 있으면 삭제/변경하지 못한다

 

restrict vs no action

 

mySQL은 비슷

SQL 표준에서는 차이가 있다.

NO ACTION

트랜잭션(여러 sql문이 있을 수 있음)

한 트랜잭션 내에서 참조값이 삭제/변경되는 것은 허용

그러나 트랜잭션 끝났을 때 referential integrity constraint를 위반하는 것은 금지한다

 

 

 

 

CREATE TABLE 직원 (

사번 CHAR(15),

이름 CHAR(4) NOT NULL,

전화번호 CHAR(20),

부서번호 CHAR(10),

경력 INT,

기본급 INT,

PRIMARY KEY(사번),

UNIQUE(전화번호),

FOREIGN KEY(부서번호) REFERENCES 부서(부서번호),

CHECK (기본급 >= 1000000)

)

 

ALTER

 

 

ALTER 외래키 추가

 

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

 

 

• alter table 테이블명 [foreign key 추가]

 

이미 서비스 중인 table의 schema를 변경하는 것이라면

변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요하다

 

Alter table 테이블명 add 컬럼이름 데이터속성

Alter table student add email varchar(100);

 

Alter table student drop column email;

 

컬럼 이름 바꾸기

Alter table student rename column 기존이름 to 새로운 이름;

 

테이블 이름 바꾸기

Alter table student RENAME TO students;

 

테이블에 있는 모든 데이터 삭제

Truncate table 테이블명;

 

제약조건

Primary key

Foreign key

Unique

Not null

Default

Check

Auto_increment

On delete cascade

On update cascade

 

• 테이블 삭제

drop table 테이블명;

 

• 만들려는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등등을 종합적으로 고려하여

DB 스키마를 적절하게 정의하는 것이 중요하다