Profile
2024년 12월 29일

SQL로 데이터베이스 정의하기 [개념]

cleanUrl: /SQL로-데이터베이스-정의하기
1. SQL 기본 개념1.1. 구조적 쿼리 언어 (structured query language)1.2. SQL 주요 용어2. MySQL 데이터 유형 (MySQL attribute data type)2.1. 숫자 속성 데이터 유형 (numeric attribute data type)2.2. 문자열 속성 데이터 유형 (string attribute data type)2.3. 날짜와 시간 속성 데이터 유형 (datetime attribute data type)2.4. 그 외 속성 데이터 유형 (etc attribute data type)3. 실습 - IT 회사 관련 RDB 만들기3.1. 스키마 설계3.1. 데이터베이스 목록 (현재 계정이 접근 가능한 데이터베이스)3.2. 데이터베이스 생성3.3. 현재 선택된 데이터베이스 확인3.4. 사용할 데이터베이스 선택하기3.5. 데이터베이스 삭제3.6. 부서(department) 테이블 생성3.7. 직원(EMPLOYEE) 테이블 생성3.8. 프로젝트(PROJECT) 테이블 생성3.9. 배치(WORKS_ON) 테이블 생성3.10. 부서(department) 테이블 외래키 추가3.11. 테이블 삭제4. 결론참고 자료
 

1. SQL 기본 개념

1.1. 구조적 쿼리 언어 (structured query language)

  • 줄여서 SQL
  • 현업에서 쓰이는 relational DBMS의 표준 언어
  • 종합적인 database 언어: DDL + DML + VDL
 

1.2. SQL 주요 용어

relational data model
SQL
relation
table
attribute
column
tuple
row
domain
domain
  • SQL에서 relation이란?
    • multiset(= bag) of tuples
    • 중복된 tuple을 허용한다
  • SQL은 RDBMS의 표준 언어이지만 실제 구현에 강제가 없기 때문에, RDBMS마다 제공하는 SQL의 스펙이 조금씩 다르다.
    • MySQL ≠ PostgreSQL ≠ Oracle
 

2. MySQL 데이터 유형 (MySQL attribute data type)

2.1. 숫자 속성 데이터 유형 (numeric attribute data type)

종류
설명
MySQL
사이즈
정수
정수를 저장할 때 사용
TINYINT
1 byte
ㅤ
ㅤ
SMALLINT
2 byte
ㅤ
ㅤ
MEDIUMINT
3 byte
ㅤ
ㅤ
INT or INTEGER
4 byte
ㅤ
ㅤ
BIGINT
8 byte
부동 소수점 방식 (floating-point)
- 실수(real number)를 저장할 때 사용 - 고정 소수점 방식에 비해 정확하지 않다
FLOAT
4 byte
ㅤ
ㅤ
DOUBLE or DOUBLE PRECISION
8 byte
고정 소수점 방식 (fixed-point)
- 실수를 정확하게 저장할 때 사용 - DECIMAL(5, 2) ⇒ [-999.99 ~ 999.99]
DECIMAL or NUMERIC (널널 vs 엄격 차이)
variable
 

2.2. 문자열 속성 데이터 유형 (string attribute data type)

종류
설명
MySQL
고정 크기 문자열
- 최대 몇 개의 ‘문자’를 가지는 문자열을 저장할지를 지정 - 저장될 문자열 길이가 최대 길이보다 작으면 나머지를 space로 채워서 저장 - name char(4)일 때 다음과 같이 저장: a , 한국
CHAR(n) (0 ≤ n ≤ 255)
가변 크기 문자열
- 최대 몇 개의 ‘문자’를 가지는 문자열을 저장할지를 지정 - 저장될 문자열의 길이 만큼만 저장 - name varchar(4)일 때 다음과 같이 저장: a, 한국
VARCHAR(n) (0 ≤ n ≤ 65,535)
사이즈가 큰 문자열
- 사이즈가 큰 문자열을 저장할 때 사용
TINYTEXT TEXT MEDIUM TEXT LONGTEXT
 

2.3. 날짜와 시간 속성 데이터 유형 (datetime attribute data type)

종류
설명
MySQL
날짜
- 년, 월, 일을 저장 - YYYY-MM-DD
DATE (’1000-01-01’ ~ ‘9999-12-31’)
시간
- 시, 분, 초를 저장 - hh:mm:ss or hhh:mm:ss - hhh:mm:ss → 주로 경과 시간을 기록할 때 사용
TIME (’-838:59:59’ ~ ‘838:59:59’)
날짜와 시간
- 날짜와 시간을 같이 표현 - YYYY-MM-DD hh:mm:ss
DATETIME (’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’)
ㅤ
- TIMESTAMP는 time-zone이 반영됨
TIMESTAMP (’1970-01-01 00:00:01’ UTC ~ ‘2038-01-19 03:14:07’ UTC)
 

2.4. 그 외 속성 데이터 유형 (etc attribute data type)

종류
설명
MySQL
byte-string
- (문자열이 아니라) byte string을 저장 - BLOB type은 주로 암호화 키를 저장할 때 사용
BINARY VARBINARY BLOB type
boolean
- true, false를 저장 - MySQL에는 따로 없음
TINYINT로 대체해서 사용
위치 관련
- 위치 관련 정보를 저장
GEOMETRY etc
JSON
- json 형태의 데이터를 저장 - 예) {”name”: “messi”, “age”: 38}
JSON
 

3. 실습 - IT 회사 관련 RDB 만들기

  • 부서, 사원, 프로젝트 관련 정보들을 저장할 수 있는 관계형 데이터베이스를 만들자
  • 사용할 RDBMS는 MySQL (InnoDB) 기준
 
  • DATABASE vs SCHEMA
    • MySQL에서는 DATABASE와 SCHEMA가 같은 뜻을 의미
      • CREATE DATABASE company = CREATE SCHEMA company
    • 다른 RDBMS에서는 의미가 다르게 쓰임
      • 예) PostgreSQL에서는 SCHEMA가 DATABASE의 namespace를 의미
 

3.1. 스키마 설계

  • DEPARTMENT
      • id
      name
      leader_id
  • EMPLOYEE
      • id
      name
      birth_date
      sex
      position
      salary
      dept_id
  • PROJECT
      • id
      name
      leader_id
      start_date
      end_date
  • WORKS_ON
      • empl_id
      proj_id

3.1. 데이터베이스 목록 (현재 계정이 접근 가능한 데이터베이스)

SHOW DATABASES;
notion image
 

3.2. 데이터베이스 생성

CREATE DATABASE company;
 

3.3. 현재 선택된 데이터베이스 확인

SELECT database();
notion image
 

3.4. 사용할 데이터베이스 선택하기

USE company;
 

3.5. 데이터베이스 삭제

DROP DATABASE company;
 

3.6. 부서(department) 테이블 생성

create table DEPARTMENT (
	id         INT          PRIMARY KEY,
	name       VARCHAR(20)  NOT NULL      UNIQUE,
	leader_id  INT
);
 
PRIMARY KEY Key constraints
  • 선언 방법
    • attribute가 하나로 구성될 때
        • create table PLAYER (
	id         INT          PRIMARY KEY,
	...
);
    • attribute가 하나 이상으로 구성될 때
        • create table PLAYER (
	team_id        VARCHAR(12),
	back_number    INT,
	...
	PRIMARY KEY (team_id, back_number)
);
UNIQUE Key constraints
  • UNIQUE로 지정된 attribute(s)는 중복된 값을 가질 수 없다.
  • 단, NULL은 중복을 허용할 수도 있다 (RDBMS 마다 다름)
  • 선언 방법
    • attribute가 하나로 구성될 때
        • create table PLAYER (
	id     INT    UNIQUE,
	...
);
    • attribute가 하나 이상으로 구성될 때
        • create table PLAYER (
	team_id        VARCHAR(12),
	back_number    INT,
	...
	UNIQUE (team_id, back_number)
);
NOT NULL constraint
  • attribute가 NOT NULL로 지정되면 해당 attribute는 NULL을 값으로 가질 수 없다
  • 선언 방법
      • create table STUDENT (
	phone_number   INT    NOT NULL   UNIQUE,
	...
);
 

3.7. 직원(EMPLOYEE) 테이블 생성

create table EMPLOYEE (
	id         INT         PRIMARY KEY,
	name       VARCHAR(30) NOT NULL,
	birth_date DATE,
	sex        CHAR(1)     CHECK (sex in ('M', 'F')),
	position   VARCHAR(10),
	salary      INT        DEFAULT 50000000,
	dept_id     INT,
	
	FOREIGN KEY (dept_id) references DEPARTMENT(id)
			on delete SET NULL on update CASCADE,
	CHECK (salary >= 50000000)
);
 
DEFAULT attribute
  • attribute의 default 값을 정의할 때 사용
  • 새로운 tuple을 저장할 때 해당 attribute에 대한 값이 없다면 default 값으로 저장
  • 선언 방법
      • create table ORDERS (
	...
	menu     VARCHAR(15)    DEFAULT '짜장면',
	...
);
CHECK constraint
  • attribute의 값을 제한하고 싶을 때 사용
  • 선언 방법
    • attribute가 하나로 구성될 때
        • create table EMPLOYEE (
	...
	age   INT   CHECK (age >= 20)
);
    • attribute가 하나 이상으로 구성될 때
        • create table PROJECT (
	start_date   DATE,
	end_date     DATE,
	...
	CHECK (start_date < end_date)
);
FOREIGN KEY Referential integrity constraint
  • attribute(s)가 다른 table의 primary key나 unique key를 참조할 때 사용
      • notion image
  • 선언 방법
      • create table EMPLOYEE (
	...
	dept_id     INT,
	
	FOREIGN KEY (dept_id) 
			references DEPARTMENT(id)
			on delete reference_option
			on update reference_option
);
      reference_option
      설명
      CASCADE
      참조값의 삭제/변경을 그대로 반영
      SET NULL
      참조값이 삭제/변경 시 NULL로 변경
      RESTRICT
      참조값이 삭제/변경되는 것을 금지
      NO ACTION
      RESTRICT와 유사
      SET DEFAULT
      참조값이 삭제/변경 시 default 값으로 변경
    • 참고: SET DEFAULT는 MySQL에서 제대로 구현이 안 돼있다고 한다.
constraint 이름 명시하기
  • 이름을 붙이면 어떤 constraint을 위반했는지 쉽게 파악 가능
  • constraint를 삭제하고 싶을 때 해당 이름으로 삭제 가능
  • 예시
      • create table TEST (
		age INT CONSTRAINT age_over_20 CHECK (age > 20)
);
      CONSTRAINT age_over_20 이름을 붙였을 때
      CONSTRAINT age_over_20 이름을 생략했을 때
      Check constraint ‘age_over_20’ is violated.
      Check constraint ‘test_chk_1’ is violated.
 
 

3.8. 프로젝트(PROJECT) 테이블 생성

create table PROJECT (
	id         INT         PRIMARY KEY,
	name       VARCHAR(20) NOT NULL      UNIQUE,
	leader_id  INT,
	start_date DATE,
	end_date   DATE,
	
	FOREIGN KEY (leader_id) references EMPLOYEE(id)
			on delete SET NULL on update CASCADE,
	CHECK (start_date < end_date)
);
 

3.9. 배치(WORKS_ON) 테이블 생성

create table WORKS_ON (
	empl_id     INT,
	proj_id     INT,
	
	PRIMARY KEY (empl_id, proj_id),
	FOREIGN KEY (empl_id) references EMPLOYEE(id)
	on delete CASCADE on update CASCADE,
	FOREIGN KEY (proj_id) references PROJECT(id)
	on delete CASCADE on update CASCADE
);
 

3.10. 부서(department) 테이블 외래키 추가

ALTER TABLE department ADD FOREIGN KEY (leader_id)
	REFERENCES employee(id)
	on update CASCADE
	on delete SET NULL;
 
  • ALTER TABLE 대표 유형
      • 유형
      MySQL
      attribute 추가
      ALTER TABLE employee ADD blood VARCHAR(2);
      attribute 이름 변경
      ALTER TABLE employee RENAME COLUMN phone TO phone_num;
      attribute 타입 변경
      ALTER TABLE employee MODIFY COLUMN blood CHAR(2);
      table 이름 변경
      ALTER TABLE logs RENAME TO backend_logs;
      primary key 추가
      ALTER TABLE log ADD PRIMARY KEY (id);
    • 이미 서비스 중인 table의 schema를 변경하는 것이라면 변경 작업 때문에 서비스의 백엔드에 영향이 없을지 검토한 후에 변경하는 것이 중요!!
 

3.11. 테이블 삭제

DROP TABLE table_name;
 

4. 결론

  • database 구조를 정의할 때 중요한 점
    • 만들려는 서비스의 스펙과 데이터 일관성, 편의성, 확장성 등등을 종합적으로 고려하여 DB 스키마를 적절하게 정의하는 것이 중요
 
 

참고 자료

YouTubeYouTubeSQL의 개념과 SQL로 데이터베이스를 정의하는 법을 배웁니다. table 생성하기, 각종 데이터 타입, constraints, 키(key)들을 활용해서 DB를 구축해봅시다!
SQL의 개념과 SQL로 데이터베이스를 정의하는 법을 배웁니다. table 생성하기, 각종 데이터 타입, constraints, 키(key)들을 활용해서 DB를 구축해봅시다!

SQL의 개념과 SQL로 데이터베이스를 정의하는 법을 배웁니다. table 생성하기, 각종 데이터 타입, constraints, 키(key)들을 활용해서 DB를 구축해봅시다!

#SQL #DB #database #createtable #constraints #datatype #key #데이터베이스 #DDL #쉬운코드 #백발백중 SQL은 relational database를 구축하고 활용하는데 필요한 언어입니다 이번 영상에서는 SQL로 데이터베이스를 정의하는 법을 배워볼거에요 어떻게 테이블을 생성하는지와 테이블을 생성할 때 필요한 기본 정보들, 가령 데이터 타입, constraints, primary, foreign key 설정 등등을 배우게 됩니다. 아주 꽉꽉 눌러담아서 준비했으니까요 재밌게 봐주세요 :) 00:00 인트로 00:11 SQL 기본 개념 02:29 MySQL로 정의할 예제 DB 소개 03:56 database 만들기 07:02 table 만들기 시작 10:51 attribute data type 소개 19:02 PRIMARY KEY 적용하기 20:35 UNIQUE constraint 적용하기 21:30 NOT NULL constraint 적용하기 22:37 DEFAULT 적용하기 23:36 CHECK constraint 적용하기 25:12 FOREIGN KEY 적용하기 30:03 constraint에 이름 붙이기 33:13 ALTER TABLE로 schema 변경하기 36:41 table 삭제하기 36:52 DB 구조 정의할 때 중요한 점 37:46 마무으리

YouTubeYouTube
TISTORYTISTORY[MySQL] SHOW 명령어
[MySQL] SHOW 명령어

[MySQL] SHOW 명령어

▶MySQL SHOW 명령어 ▶설명 SHOW는 데이터베이스 목록이나, 테이블 목록 등 다양한 정보를 보기 원할 때 사용하는 명령어입니다. ▶데이터베이스/테이블 관련 명령어 목록 데이터베이스 목록 (현재 계정이 접근 가능한 데이터베이스) SHOW DATABASES; 현재 데이터베이스에 테이블 목록SHOW TABLES; 현재 데이터베이스에서 조건이 맞는 테이블 목록SHOW TABLES LIKE '키워드%'; 특정 데이터베이스에 테이블 목록SHOW TABLES FROM 데이터베이스; 특정 데이터베이스에서 조건이 맞는 테이블 목록SHOW TABLES FROM 데이터베이스 LIKE '키워드%'; 특정 테이블에 인덱스 보기SHOW INDEX FROM 테이블; 특정 테이블에 컬럼 보기SHOW COLUMNS FROM ..

TISTORYTISTORY
MySQL :: MySQL 8.4 Reference Manual :: 15.7.7.15 SHOW DATABASES Statement
MySQL :: MySQL 8.4 Reference Manual :: 15.7.7.15 SHOW DATABASES Statement

MySQL :: MySQL 8.4 Reference Manual :: 15.7.7.15 SHOW DATABASES Statement

SHOW DATABASES lists the databases on the MySQL server host. SHOW SCHEMAS is a synonym for SHOW DATABASES. The LIKE clause, if present, indicates which database names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 28.8, “Extensions to SHOW Statements”.

 
이전 글
관계형 데이터베이스 [개념]
관계형 데이터베이스 [개념]
다음 글
SQL로 데이터베이스에 데이터 추가, 수정, 삭제하기
SQL로 데이터베이스에 데이터 추가, 수정, 삭제하기