DBMS SQL DDL

금송·2024년 10월 9일
DBMSsql이론
0

이론

목록 보기
25/26
post-thumbnail

외래키(FK) : 다른 테이블의 PK를 참조하는 컬럼(키)

DDL

DDL은 Data Definition Language 의 약자.

데이터를 정의하는 언어.

  • DDL: 테이블을 생성한다. → 틀 생성
  • DML: 생성된 테이블에 데이터를 조회, 삽입, 수정, 삭제한다. → 데이터 조작

일반적으로 사용하는 DDL문

CREATE새로운 테이블을 생성
ALTER기존 테이블 구조 변경
DROP테이블 삭제
RENAME기존 테이블 이름 변경
TRUNCATE기존 테이블 초기화

CREATE

데이터베이스

DBMS에는 여러 개의 데이터베이스를 만들 수 있고, 데이터베이스 하위에 여러 개의 테이블을 만들 수 있는 계층 구조.

데이터베이스가 생성되어있지 않다면 테이블의 생성도 할 수 없다. 테이블이 생성되어야 할 데이터베이스가 없기 때문.

데이터베이스 생성

CREATE DATABASE [데이터베이스명]

테이블 생성

CREATE TABLE [데이터베이스명].[테이블 이름](
	[열] [데이터타입] [제약조건],  -- ,(콤마)로 여러 열을 구분해서 적음
	...
	
)
데이터베이스 이름어떤 데이터베이스 하위에 테이블을 생성할 지
테이블 이름생성하려는 테이블의 이름
열열의 이름
데이터 타입해당 열의 데이터 타입 (ex. 정수, 문자열, 날짜, …)
제약조건[NOT NULL 

주로 사용하는 데이터 타입은 아래와 같음.

데이터 타입설명
CHAR(size)고정된 길이의 문자열로 저장합니다.

예를 들어 데이터 타입을 “CHAR(5)”로 설정할 경우 “abcde”처럼 글자수가 5개인 문자열은 저장이 가능하고, “abc”처럼 글자수가 3개인 문자열을 저장할 때에도 “CHAR(5)” 만큼의 크기를 차지합니다. |
| VARCHAR(size) | 가변 길이의 문자열을 저장할 수 있습니다.

예를 들어 데이터 타입을 “VARCHAR(5)”로 설정할 경우 글자수가 1~5개인 문자열을 저장할 수 있습니다. 즉, “abc”, “abcde”는 저장할 수 있지만 5자를 넘어가는 “abcdefg”는 저장할 수 없습니다. |
| INT | 정수형 데이터를 저장할 수 있습니다. (1, 2, 3, …) |
| FLOAT | 실수형 데이터를 저장할 수 있습니다. (1.0, 2.0, 3.123, …) |
| DATE | 날짜를 저장할 수 있습니다. (2023-12-01) |
| TIME | 시간을 저장할 수 있습니다. (08:38:27) |
| DATETIME
TIMESTAMP | 날짜와 시간을 저장할 수 있습니다. (2023-12-01 08:38:27) |

테이블 제약조건 (Constraint)

테이블에 부적절한 데이터가 입력되는 것을 방지하기 위해서, 테이블 생성 시점에 규칙을 정해놓는 것을 이야기함.

컬럼 단위로 정해지는 규칙이고, 해당 컬럼에 입력되는 데이터의 성격을 정해놓는 것.

옵션설명
NOT NULL해당 열에 NULL 값을 허용하지 않는다는 의미입니다.
DEFAULT데이터를 입력 시 해당 열에 아무런 값도 입력되지 않은 경우 기본으로 사용할 값 지정합니다.
UNIQUE해당 테이블 내에서 유일한 속성을 갖는다는 의미로, UNIQUE로 설정된 열에는 중복된 값을 저장할 수 없습니다.
PRIMARY KEY하나의 테이블에 있는 데이터들을 식별하기 위한 기준입니다. (예를 들어서 주문 번호나 아이디)
FOREIGN KEY(외래키)테이블간에 관계를 나타낼 때 사용하는 Key로, 다른 테이블의 기본키를 참조해 외래키로 사용합니다.

PRIMARY KEY ??? 의 특징

  • unique
  • not null
  • 변경 불가!

NOT NULL 제약조건

CREATE TABLE 테이블명 (
		컬럼명 데이터타입 **NOT NULL**
		...
)

테이블 생성시 해당 열에 NULL값을 허용하지 않는다는 의미.

NOT NULL 제약조건이 설정되어있는 열에 NULL 값을 넣거나, 아무 값도 넣지 않을 경우 다음과 같은 에러가 발생한다

ERROR: 실패한 자료: (신기루, 41, null)"address" 칼럼(해당 릴레이션 "students")의 null 값이 not null 제약조건을 위반했습니다.

CREATE TABLE로 테이블 생성시 컬럼 옆에 NOT NULL 이라는 제약조건이 없다면, NULL을 허용한다는 컬럼이다. 즉, default는 NULL 허용이라는 것.

UNIQUE 제약조건

CREATE TABLE 테이블명 (
		컬럼명 데이터타입 **UNIQUE** 
		...
)

테이블의 컬럼 그룹에 대해 유일한 값이여야 한다는 제약조건.

UNIQUE 제약은 데이터 무결성을 유지하는 데 도움이 되며, 일반적으로 고유한 식별자를 갖는 열에 많이 사용된다. 예를 들어, 사용자 ID나 주문 번호 등이 이에 해당.

DEFAULT

  • 컬럼 기본값 지정
CREATE TABLE 테이블명 (
		컬럼명 데이터타입 DEFAULT 기본값
		...
)

DEFAULT 제약조건은 데이터베이스 테이블의 컬럼에 대해 값을 명시적으로 제공하지 않을 경우 사용되는 컬럼 기본 값을 지정한다.
만약 테이블에 새로운 행을 삽입할 때 해당 컬럼에 값을 지정하지 않는 경우, 그 컬럼에 DEFAULT(기본)값을 자동으로 할당한다.

PRIMARY KEY (기본키)

PRIMARY KEY는 하나의 테이블에 있는 데이터들을 식별하기 위한 기준

그렇기 때문에 하나의 테이블에 하나의 기본키만 생성이 가능하고, 기본키는 하나 이상의 컬럼으로 구성이 가능하다.
기본키로 지정이 되면 테이블의 데이터를 쉽고 빠르게 찾도록 도와주는 역할을 한다. 

CREATE TABLE 테이블명 (
		컬럼명 데이터타입 PRIMARY KEY
		...
)

PRIMARY KEY로 설정된 열은 기본적으로 NULL 값을 가질 수 없으며 중복된 값을 가져서도 안됨. 즉, NOT NULL + UNIQUE 제약조건의 속성을 가진다.

FOREIGN KEY (외래키)

FK 제약 조건

  • 원천 데이터를 참조하는 데이터가 있을 경우
    1. 원천 데이터 삭제를 못하도록 제한 - RESTRICT
      1. Default 설정으로 적용됨
      2. 일반적으로 데이터 무결성을 유지하기 위해 가장 안전한 옵션
      3. 왜래키 제약조건이 설정된 경우 참조된 행이 삭제되는 것을 방지하여 데이터의 일관성 유지 → 해당 경우 자식 테이블의 모든 레코드를 삭제하면 부모 테이블의 레코드를 삭제할 수 있다. 즉 자식 테이블의 행이 부모 테이블의 행을 참조하지 않는 상태에선 삭제 가능
    2. 참조하는 데이터까지 같이 삭제 - ON DELETE CASECADE
    3. 참조하는 데이터의 key값을 set null - ON DELETE SET NULL

종속처리 = CASCADE

해당 옵션을 설정하면 참조된 부모 테이블에 대한 DELETE를 허용한다.

그리고 참조된 테이블의 레코드가 삭제될 때, 해당 레코드를 참조하는 다른 테이블의 레코드도 함께 삭제된다. 즉 부모 테이블의 행(row)이 삭제되면 관련된 자식 테이블의 행도 자동 삭제된다.

외래키는 데이터의 무결성을 위해 사용하지만, 테이블간의 의존도를 높여 추후 데이터를 수작업으로 다룰 경우 변경에 불리하게 작용된다.

  1. 수작업으로 테이블 내 데이터를 변경해야 할 때
  2. 초기 테이블 설계 이후에 추가 개발이 이루어질 경우, 데이터 변경이 필요할 때

그래서 보통은 외래키를 따로 설정하지 않고, 어플리케이션 코드에서 데이터의 일관성을 확인한다.

참조 관계에 있는 테이블은 명시만 하되, 실제 테이블간의 의존도는 낮추고 자유도를 높이는 방법.

DBMS 자체 제약

DBMS 자체 제약x (유연성), 자바 코드에서 비즈니스 로직으로 제약

customer, orders 테이블 생성 & row 추가

(orders.customer_id에 FK 제약조건 추가)

customer.id = 1 회원 정보 삭제

  • 코드
    -- FK 제약조건 - RESTRICT 옵션
CREATE TABLE customer (
		id INT PRIMARY KEY, 
		name VARCHAR(10) NOT NULL, 
		address VARCHAR(200), 
		contact VARCHAR(100)
);
CREATE TABLE orders (
		id INT PRIMARY KEY,
		customer_id INT, 		-- 외래키 설정
		date TIMESTAMP DEFAULT now(),
		payment VARCHAR(50),
		amount INT,
		delivery_amount INT,
		FOREIGN KEY (customer_id) REFERENCES customer(id)		-- 제약조건
);
INSERT INTO customer VALUES 
(1, '동해물', '서울', '010-1234-5678'),
(2, '백두산', '부산', '010-8765-4321');

INSERT INTO orders VALUES 
(1, 1, '2023-11-12', '신용카드', 10000, 2500),
(2, 1, '2023-11-13', '신용카드', 20000, 2500),
(3, 2, '2023-11-12', '계좌이체', 30000, 3000);

DELETE FROM orders WHERE customer_id = 1;	-- 이때 참조하고 있는 row를 삭제하고 아래 구문 실행하면 삭제 가능.
DELETE FROM customer WHERE id = 1;	-- 1451 오류. foreign key라 삭제 불가능
SELECT * FROM customer;
SELECT * FROM orders;

INSERT INTO orders VALUES		-- 1452 오류남. customer에 1번 id값이 없어서 안들어가짐. 즉 참조값이 없어서 안됨.
(1, 1, '2023-11-12', '신용카드', 10000, 2500),
(2, 1, '2023-11-13', '신용카드', 20000, 2500);

ALTER

DDL문

기존 테이블의 구조를 변경하기 위해 사용한다.

새로운 열 추가

-- ALTER TABLE 테이블명 ADD 열이름 데이터타입
ALTER TABLE students ADD grade VARCHAR(20)

기존 열 이름 변경

-- ALTER TABLE 테이블명 RENAME COLUMN 열이름 TO 바꿀 열이름
ALTER TABLE students RENAME COLUMN grade TO great

기존 열 데이터 타입 변경

-- ALTER TABLE 테이블명 MODIFY COLUMN 열이름 데이터 타입
ALTER TABLE students MODIFY COLUMN address VARCHAR(100)

데이터 타입을 변경할 때는 기존 열에 저장된 데이터가 새 데이터 타입에 맞는지 확인해야한다. 만약 기존 데이터가 새 데이터 타입에 맞지 않으면 에러가 발생할 수 있다. 예를 들어 문자열로 저장되어 있는데 INT 타입으로 바꾸려고 하면 에러 발생.

열 삭제

-- ALTER TABLE 테이블명 DROP COLUMN 열이름
ALTER TABLE students DROP COLUMN grade

DROP

매우 강력한 명령어이므로 삭제할 테이블이 올바른지 반드시 확인하고 실행해야 한다.

-- DROP TABLE 테이블명
DROP TABLE test_db.students

TRUNCATE

테이블을 최초 생성된 테이블 초기 상태로 만들어 주는 명령어

-- TRUNCATE TABLE 테이블명
TRUNCATE TABLE students

TRUNCATE vs DELETE

둘 다 전체 데이터를 삭제한다.

DELETE보다 TRUNCATE의 속도가 더 빠르다. 그 이유는 TRUNCATE의 경우 테이블을 스캔하는 과정을 거치지 않기 떄문.

하지만 DELETE 명령어는 로그를 남기고 데이터 복구가 가능하다는 특징 떄문에 많이 사용함.

DROP vs TRUNCATE vs DELETE

DROPTRUNCATEDELETE
종류DDLDDL(일부 DML)DML
COMMITAUTO COMMITAUTO COMMIT사용자 COMMIT
ROLLBACK불가불가가능
명령어 수행시테이블 정의 삭제테이블을 최초 생성한 초기 상태로 만들어줌데이터만 삭제
로그남기지 않음남기지 않음남김
속도빠름빠름느림

DB Index

데이터베이스에서 인덱스란 테이블의 특정 열에 대한 검색 및 조회 성능을 향상 시키기 위해 사용되는 자료구조.

SELECT 명령문을 실행할 때 WHERE 조건절로 필터링된 특정 행을 가져온다고 가정

SELECT * FROM students WHERE name = '홍길동';

name 속성에 대한 값이 ‘홍길동’인 행(row)를 가져올텐데 만약 students 테이블의 row 수가 100만개라면 Full Scan을 하기에 성능이 좋지 않을 것이다. 이럴 경우 해당 행에 인덱스를 추가한다.

Index 생성/삭제 문법

-- 생성/추가 문법
-- 단일 컬럼 인덱스
CREATE INDEX [인덱스명] ON [테이블명] ([컬럼명]);
-- 다중 컬럼 인덱스
CREATE INDEX [인덱스명] ON [테이블명] ([컬럼명, ...])
-- 삭제
ALTER TABLE [테이블명] DROP INDEX [인덱스이름];

Index 자료구조

DB Index 의 자료구조는 Hash Table, B-Tree, B+Tree

Hash Table

해시 테이블은 Key-Value 형태로 이루어진 데이터를 저장하는데 특화된 자료구조

해시 테이블 기반의 DB Index는 특정 컬럼의 값과 데이터 위치를 Key-Value로 사용
hash함수로 key를 고유한 hash 값으로 변환시켜 저장
hash함수로 key를 고유한 hash 값으로 변환시켜 저장

해시 함수는 Key가 조금이라도 다르면 완전히 다른 해시 값을 생성한다.

Index의 경우 WHERE 조건의 등호(=)연산에는 효율이 좋지만, 부등호 연산 (>,<)은 부적합하다. 해시 테이블은 내부 데이터들이 정렬되어 있지 않아 탐색이 효율적이지 않다.

B-Tree

B-Tree란 자식 노드가 2개 이상인 트리를 의미한다.

각 Key의 왼쪽 자식은 key보다 작은값을, 오른쪽 자식은 key보다 큰 값을 가진다.
B-Tree의 Key-Value값들은 항상 Key를 기준으로 오름차순 정렬이다. 그렇기 때문에 위에서 언급한 해시 테이블보다 효율적인 데이터 탐색이 가능하다.

Index 고려사항

DBMS 내부적으로 관리하는 인덱스 테이블은 ‘이진트리 검색’을 사용하기 때문에 기본적으로 정렬이 되어있다. 그래서 만약 인덱스 테이블이 참조하는 테이블에서 삽입, 삭제, 수정이 자주 일어나게 된다면 인덱스 테이블에서는 데이터를 정렬하면서 삽입, 삭제, 수정이 이루어지기 때문에 전체적인 성능 저하를 초래할 수 있다.

  1. INSERT : 새로운 데이터에 대한 인덱스가 추가
  2. DELETE : 삭제하는 데이터의 인덱스를 제거
  3. UPDATE : 기존 인덱스를 제거하고, 갱신된 데이터에 대한 인덱스를 추가

따라서 데이터 갱신보다는 조회가 주로 사용되는 컬럼에 INDEX를 생성하는 것이 유리하다.
ex) 사용자명, 제품명, …

대상 컬럼 선정 방법

인덱스 컬럼을 선정하는 방법은 검색 성능에 유리한 방식을 선택하는 것이다.

인덱스 컬럼을 선정하는 방법은, 일반적으로 Cardinality가 높은 컬럼을 우선적으로 인덱싱하는 것이 검색 성능에 유리하다. 여기서 Cardinality란 특정 데이터 집합의 유니크(Unique)한 값의 개수를 의미한다.

예시)

  • 남-여 두가지 값만 존재하는 컬럼은 중복도가 높으며 Cardinality가 낮다.
  • 개인마다 고유한 값이 존재하는 주민등록번호 컬럼은 중복도가 낮고 Cardinality가 높다.

Index를 통해 데이터를 더 많이 필터링할 수 있는 컬럼을 Index 대상 컬럼으로 선정해야 한다.
인덱스 대상 컬럼의 선정 기준

인덱스 설정 기준정도
Cardinality높을수록 적합
활용도높을수록 적합
중복도낮을수록 적합
  • 실습 코드
    USE test_db;

-- NOT NULL 컬럼 테스트
CREATE TABLE nulltest (
	col1 varchar(10) NOT NULL,
    col2 varchar(10)
);
INSERT INTO nulltest (col1, col2) VALUES ('첫번쨰', '1'), ('두번째', '2');

-- DEFAULT 제약조건 테스트
CREATE TABLE defaulttable (
	id		INT PRIMARY KEY,	-- 조건으로 선언하면 성능이 빠르다.
    name	VARCHAR(20) NOT NULL,
    defaultcol VARCHAR(20) DEFAULT '-'
);
INSERT INTO defaulttable (id, name) VALUES (1, 'keumsong'), (1, 'minji');	-- 만약 프라이머리 키에 같은 값을 넣어줄 경우 1062 오류 남.
SELECT * FROM defaulttable;

-- AUTO INCREMENT 제약조건
CREATE TABLE autoinctable (
	id INT		PRIMARY KEY AUTO_INCREMENT,		-- 입력을 해주지 않았지만 자동 증가를 해주는 옵션.
    name VARCHAR(20)	
);
INSERT INTO autoinctable (name) VALUES ('이름1'), ('이름2'), ('이름3'),('이름4'),(0310);
SELECT * FROM autoinctable;

-- UNIQUE 제약조건
CREATE TABLE uniquetable (
	id		VARCHAR(30) PRIMARY KEY,
    name	VARCHAR(30) NOT NULL,
    email	VARCHAR(400) UNIQUE		-- 중복으로 작성하면 1062 에러남.
);
INSERT INTO uniquetable (id, name, email) VALUES
('jks', '장금송', 'ks@naver.com'),
('kmj', '김민지', 'mj@naver.com'),
('newjeans', '뉴진스', 'nj@naver.com');
SELECT * FROM uniquetable;
UPDATE uniquetable
SET email = 'mj@naver.com'		-- 유니크하지 않은 값을 넣으려고 해서 1062 에러남.
WHERE id = 'jks';

-- FK 제약조건 - RESTRICT 옵션
CREATE TABLE customer (
		id INT PRIMARY KEY, 
		name VARCHAR(10) NOT NULL, 
		address VARCHAR(200), 
		contact VARCHAR(100)
);
CREATE TABLE orders (
		id INT PRIMARY KEY,
		customer_id INT, 		-- 외래키 설정
		date TIMESTAMP DEFAULT now(),
		payment VARCHAR(50),
		amount INT,
		delivery_amount INT,
		FOREIGN KEY (customer_id) REFERENCES customer(id)		-- 제약조건
);
INSERT INTO customer VALUES 
(1, '동해물', '서울', '010-1234-5678'),
(2, '백두산', '부산', '010-8765-4321');

INSERT INTO orders VALUES 
(1, 1, '2023-11-12', '신용카드', 10000, 2500),
(2, 1, '2023-11-13', '신용카드', 20000, 2500),
(3, 2, '2023-11-12', '계좌이체', 30000, 3000);

DELETE FROM orders WHERE customer_id = 1;	-- 이때 참조하고 있는 row를 삭제하고 아래 구문 실행하면 삭제 가능.
DELETE FROM customer WHERE id = 1;	-- 1451 오류. foreign key라 삭제 불가능
SELECT * FROM customer;
SELECT * FROM orders;

INSERT INTO orders VALUES		-- 1452 오류남. customer에 1번 id값이 없어서 안들어가짐. 즉 참조값이 없어서 안됨.
(1, 1, '2023-11-12', '신용카드', 10000, 2500),
(2, 1, '2023-11-13', '신용카드', 20000, 2500);

DROP TABLE orders;		-- 테이블 삭제에도 순서가 생김.
DROP TABLE customer;

CREATE TABLE customer (
		id INT PRIMARY KEY, 
		name VARCHAR(10) NOT NULL, 
		address VARCHAR(200), 
		contact VARCHAR(100)
);
CREATE TABLE orders (
		id INT PRIMARY KEY,
		customer_id INT, 		-- 외래키 설정
		date TIMESTAMP DEFAULT now(),
		payment VARCHAR(50),
		amount INT,
		delivery_amount INT,
		FOREIGN KEY (customer_id) REFERENCES customer(id) ON DELETE CASCADE
);
INSERT INTO customer VALUES 
(1, '동해물', '서울', '010-1234-5678'),
(2, '백두산', '부산', '010-8765-4321');

INSERT INTO orders VALUES 
(1, 1, '2023-11-12', '신용카드', 10000, 2500),
(2, 1, '2023-11-13', '신용카드', 20000, 2500),
(3, 2, '2023-11-12', '계좌이체', 30000, 3000);

SELECT * FROM customer;
SELECT * FROM orders;

DELETE FROM customer WHERE id = 1;
-- DELETE FROM orders WHERE customer_id = 1; 도 함께 자동 삭제 실행 되었음.

-- FK 제약조건 - SET NULL
DROP TABLE customer;
DROP TABLE orders;

-- creat table customer, orders (customer_id 칼럼에 FK 제약조건 SET NULL 옵션)
CREATE TABLE customer (
		id INT PRIMARY KEY, 
		name VARCHAR(10) NOT NULL, 
		address VARCHAR(200), 
		contact VARCHAR(100)
);
CREATE TABLE orders (
		id INT PRIMARY KEY,
		customer_id INT, 		-- 외래키 설정
		date TIMESTAMP DEFAULT now(),
		payment VARCHAR(50),
		amount INT,
		delivery_amount INT,
		FOREIGN KEY (customer_id) REFERENCES  customer(id) ON DELETE SET NULL -- REFERENCES 테이블명(컬럼명)
);
INSERT INTO customer VALUES 
(1, '동해물', '서울', '010-1234-5678'),
(2, '백두산', '부산', '010-8765-4321');

INSERT INTO orders VALUES 
(1, 1, '2023-11-12', '신용카드', 10000, 2500),
(2, 1, '2023-11-13', '신용카드', 20000, 2500),
(3, 2, '2023-11-12', '계좌이체', 30000, 3000);
SELECT * FROM customer;
SELECT * FROM orders;
-- 회원 id = 1 삭제했을 때 orders customer_id(참조값)은 어떻게 되었는지 확인
DELETE FROM customer WHERE id = 1;	-- orders의 id값이 null값으로 바뀌고 customer의 id = 1 은 삭제된다.

-- 열 추가, 수정, 삭제
-- ADD 열 추가
SELECT * FROM students;
ALTER TABLE students
ADD grade VARCHAR(200);
-- RENAME 열 이름 변경
ALTER TABLE students
RENAME COLUMN grade TO great;
-- 추가한 열 타입 수정
ALTER TABLE students
MODIFY COLUMN great VARCHAR(300);
-- 열 삭제
ALTER TABLE students
DROP COLUMN great;

-- Q1
CREATE TABLE employees (
	id			INT PRIMARY KEY,
    name		VARCHAR(50),
    position 	VARCHAR(100),
    hire_date 	DATE
);

-- Q2
CREATE TABLE departments (
	department_id	INT PRIMARY KEY,
    department_name	VARCHAR(100),
    manager_id		INT,
    FOREIGN KEY (employees_id) REFERENCES employees(id)	
)
profile
금송
goldsong
이전 포스트

DBMS SQL 데이터 조회 심화

다음 포스트

DBMS SQL DCL, 데이터 모델링과 ERD

0개의 댓글

관련 채용 정보