[코드잇] SQL 데이터베이스 - SQL로 하는 데이터 관리

코드잇 Codeit/Back-End

[코드잇] SQL 데이터베이스 - SQL로 하는 데이터 관리

꼽파 2024. 6. 29. 15:37

◆ 데이터베이스와 테이블 구축
◆ 테이블 다루기
◆ Foreign Key 제대로 사용하기

◆ 데이터베이스와 테이블 구축

설치 후 실행

출처 : https://shine-learn.tistory.com/154

데이터베이스 생성하기

CREATE DATABASE course_rating;

같은 이름의 데이터베이스가 여러 개 존재하면 안 돼서 에러가 남.

이런 일이 생기지 않도록 DBMS가 잘 막아줌.

존재하지 않으면 데이터베이스 생성하는 SQL문

CREATE DATABASE IF NOT EXISTS course_rating;

에러가 아니라 경고문이 뜸.

데이터베이스 안 테이블 생성

1) 해당 데이터베이스를 더블클릭 (Schema select)한 후 테이블 추가 아이콘 누르기

2) SQL문 작성 후 테이블 생성 아이콘 누르기

USE course_rating;

데이터베이스 서버에 처음 접속하고 난 후에는, 가장 먼저 어느 데이터베이스에서 작업을 할 것인지를 지정해줘야 함.

→ DBMS가 그 데이터베이스를 '작업 중인 데이터베이스'로 인식함.

→ 데이터베이스 안에 있는 존재를 SQL 문에서 가리킬 때, 데이터베이스 이름을 적어주지 않아도 됨.

만약 A 테이블이 있는 경우

USE A;

이걸 실행하고 나면

테이블 이럼 이렇게 적지 않고,

SELECT * FROM A.animal;

이렇게만 써도 잘 조회됨.

SELECT * FROM animal;

테이블 생성하기

PK : Primary Key
NN : Not Null (Null이 있으면 안 됨)
AI : Auto Increment (증가하는 숫자값을 자동으로 넣어줌), 정수형 데이터타입에서만 가능함.

이름 - 데이터타입 - 속성

NULL

- 이 컬럼에 NULL이 있어도 괜찮음.

- 써주지 않아도 기본으로 적용됨.

- NOT NULL과 반대임.

PRIMARY KEY : 특정 로우를 식별할 수 있게 하는 컬럼

컬럼의 데이터 타입

Numeric types (숫자형 타입)
Date and Time types (날짜 및 시간 타입)
String types (문자열 타입)

Numeric types (숫자형 타입)

• 정수형 타입

TINYINT	작은 범위의 정수들을 저장할 때 쓰는 데이터 타입 · SIGNED : 양수, 0, 음수 · UNSIGNED : 0과 양수 · TINYINT SIGNED : -128 ~ 127 · TINYINT UNSIGNED : 0 ~ 255
SMALLINT	TINYINT 보다 좀더 큰 범위의 정수 · SMALLINT SIGNED : -32768 ~ 32767 · SMALLINT UNSIGNED : 0 ~ 65535
MEDIUMINT	더 넓은 범위를 나타내는 데이터 타입 · MEDIUMINT SIGNED : -8388608 ~ 8388607 · MEDIUMINT UNSIGNED : 0 ~ 16777215
INT	더 넓은 범위를 나타내는 데이터 타입 · INT SIGNED : -2147483648 ~ 2147483647 · INT UNSIGNED : 0 ~ 4294967295
BIGINT	아주 큰 범위의 정수를 저장 · BIGINT SIGNED : -9223372036854775808 ~ 9223372036854775807 · BIGINT UNSIGNED : 0 ~ 18446744073709551615

• 실수형 타입

DECIMAL	DECIMAL(M, D)의 형식으로 나타냄. · M : 최대로 쓸 수 있는 전체 숫자의 자리수 · D : 최대로 쓸 수 있는 소수점 뒤에 있는 자리의 수 DECIMAL (5, 2)라면 -999.99 부터 999.99 까지 M은 최대 65, D는 최대 30까지의 값 DEC, NUMERIC, FIXED
FLOAT	-3.402823466E+38 ~ -1.175494351E-38, 0, 1.175494351E-38 ~ 3.402823466E+38
DOUBLE	-1.7976931348623157E+308 ~ -2.2250738585072014E-308, 0, 2.2250738585072014E-308 ~ 1.7976931348623157E+308

Date and Time types (날짜 및 시간 타입)

DATE	날짜를 저장하는 데이터 타입 (2020-03-26)
DATETIME	날짜와 시간을 저장하는 데이터 타입 (2020-03-26 09:30:27)
TIMESTAMP	날짜와 시간을 저장하는 데이터 타입 (+타임존 정보) (2020-03-26 09:30:27)
TIME	시간을 나타내는 데이터 타입 (09:27:31)

String types (문자열 타입)

CHAR	고정 길이 타입 CHAR(10) 최대 몇 자까지 저장할 수 있는지 괄호 안에는 0부터 255까지의 숫자 가능
VARCHAR	가변 길이 타입 : 저장 용량이 실제 저장된 값에 맞게 최적화 됨. 괄호 안에 최소 0부터 최대 65,535를 쓸 수 있음.
TEXT	최대 65535까지 저장할 수 있음.

테이블에 row 추가하기

모든 컬럼이 있는 row를 추가

INSERT INTO `student` (name, student_number, major, email, phone, admission_date)
VALUES ('성태후', 20142947, '컴퓨터공학과', 'taehos@naver.com', '010-7373-1234', '2014-03-12');

INSERT INTO student 
VALUES (2, '김소원', '20130912', '화학과', 'sungso@google.com', '010-1234-1234', '2013-03-07');

특정 컬럼에만 값이 있는 row를 추가

INSERT INTO student
(id, name, student_number, major, admission_date) 
VALUES (3, '이현승', '20111025', '법학과', '2013-03-07');

INSERT INTO student
(name, student_number, major, admission_date) 
VALUES ('정유진', '20160843', '빅데이터학과', '2016-03-15');

auto_increment 속성이 적용되어서 이전 id값보다 1개 더 큰 id값이 자동으로 생성됨.

테이블의 row 갱신하기

수정 혹은 갱신

update student : student 테이블을 업데이트 해랴
SET : 설정한다
major = '멀티미디어학과' : major 컬럼의 값을 '멀티미디어학과'로 설정해라
WHERE id = 2 : 이 조건을 만족하는 row를 갱신해라. (중요)

UPDATE student SET major = '멀티미디어학과' WHERE id = 2;

만약 WHERE 절이 없으면 모든 row에 major 컬럼을 갱신하게 됨.

UPDATE student SET major = '멀티미디어학과'

UPDATE student 
	SET major = '멀티미디어학과', name = '차소원' 
	WHERE id = 2;

컬럼의 기존 값을 기준으로 갱신하기

UPDATE 문으로 갱신을 할 때는 컬럼의 이름을 활용해서, 기존 값을 기반으로 갱신하는 경우가 있음.

UPDATE final_exam_result SET score = score + 3;

테이블의 row 삭제하기

DELETE FROM student WHERE id = 4;

만약 WHERE 절이 없으면 테이블의 모든 데이터가 삭제됨!

DELETE FROM student;

물리 삭제 vs 논리 삭제

물리 삭제

row를 바로 삭제함.

DELETE FROM order WHERE id = 2;

논리 삭제

삭제해야할 row를 삭제하지 않고, '삭제 여부'를 나타내는 별도의 컬럼을 두고, 거기에 '삭제되었음'을 나타내는 값을 넣는 것.

UPDATE order SET is_cancelled = ‘Y’;

논리 삭제한 경우 나중에 유효한 row들만 조회할 때 조건문을 따로 써줘야함.

SELECT * FROM WHERE is_cancelled != ‘Y’;
SELECT * FROM WHERE is_deleted != ‘Y’;

- 이미 데이터 분석에 활용되었거나
- 고객이 동의한 데이터 보유기간이 지난 row들은
정기적으로 물리 삭제하는 방법을 활용하기도 함.

◆ 테이블 다루기

컬럼 정보를 한 눈에 보여주는 DESCRIBE

DESCRIBE 테이블명

DESC 테이블명

컬럼 추가와 컬럼의 이름 변경

한번 만들어진 컬럼은 컬럼구조가 바뀔 수 있음.
시간이 지나면 테이블에 저장해야할 정보가 변하기 때문에
테이블에 새로운 컬럼을 추가하거나 원래의 컬럼을 삭제할 수 있음.

컬럼 추가

ADD gender : gender라는 컬럼을 추가해라
CHAR(1) NULL : 컬럼의 속성

ALTER TABLE student ADD gender CHAR(1) NULL;

RENAME COLUMN : 컬럼 이름을 다시 지어라
student_number TO registration_number : 앞에 있는 이름을 TO 뒤에 있는 것으로 바꿔서 지어라.

ALTER TABLE student 
	RENAME COLUMN student_number TO registration_number;

컬럼 삭제와 컬럼의 데이터 타입 변경

admission_date(입학일)은 이미 registration_number(학번)에 입학년도가 있어서 삭제할 것임.

ALTER TABLE student 
	DROP COLUMN admission_date;

major가 문자열로 다 들어와있어서 전공을 알리는 숫자 코드로 대체할 것임.

ALTER TABLE student
MODIFY major INT;

지금 문자열만 있어서 INT로 데이터타입으로 변경이 불가능함.

UPDATE student SET major = 10 WHERE major = '컴퓨터공학과';
UPDATE student SET major = 12 WHERE major = '멀티미디어학과';
UPDATE student SET major = 7 WHERE major = '법학과';

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Safe Updates

컬럼에 NOT NULL 속성 주기

어떤 컬럼에 꼭 값이 들어가야하는 경우 NOT NULL 속성 주기
name, registration_number, major

ALTER TABLE student MODIFY name VARCHAR(20) NOT NULL;
ALTER TABLE student MODIFY student_number INT NOT NULL;
ALTER TABLE student MODIFY major INT NOT NULL;

데이터타입 뿐만 아니라 컬럼의 속성을 변경할 때도 MODIFY를 사용함.
컬럼 속성 변경시에는 그 앞에 원래의 데이터 타입들도 써줘야 함.
데이터타입과 속성을 동시에 변경할 수도 있음.
VARCHAR(35) NOT NULL => 두 가지 모두 변경

NOT NULL 속성을 가진 컬럼들에 값이 없는 로우를 추가함.

INSERT INTO student (email, phone, gender)
	VALUES ('abc@naver.com', '010-1234-1234', 'm');

컬럼에 DEFAULT 속성 주기

컬럼에 기본값을 설정해두면 로우를 추가할 때 그 컬럼에 값을 주지 않아도 그 기본값이 입력됨.

이 컬럼에 값을 주지 않으면 NULL이 설정된다는 뜻임.

아직 전공이 없는 상태의 전공코드 = 101
101을 major 컬럼의 기본값으로 설정함.

ALTER TABLE student MODIFY major INT NOT NULL DEFAULT 101;

INSERT INTO student (name, registration_number)
	VALUES ('구지섭', 20112405);

DATETIME, TIMESTAMP 타입의 컬럼에 값을 넣는 2가지 방식

테이블에는 어떤 row가 추가되거나 갱신되었을 때 그 시각을 저장해야하는 경우, 2가지 방식이 있음.

ex. 게시글 업로드 시각, 댓글이 달린 시각, 댓글을 수정한 시각

1) NOW() 함수 사용

UPDATE post
	SET content = "오늘 내가 간 곳 어딜까?"
    	recent_modified_time = NOW()
    WHERE id = 1;

2) 컬럼에 DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP 속성 설정하기

DEFAULT CURRENT_TIMESTAMP 속성 : 테이블에 새 row를 추가할 때 따로 그 컬럼에 값을 주지 않아도 현재 시간이 설정되도록 하는 속성
ON UPDATE CURRENT_TIMESTAMP 속성 : 기존 row에서 단 하나의 컬럼이라도 갱신되면 갱신될 때의 시간이 설정되도록 하는 속성

ALTER TABLE post
	MODIFY upload_time DATETIME DEFAULT CURRENT_TIMESTAMP,
	MODIFY recent_modified_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

컬럼에 UNIQUE 속성 주기

ALTER TABLE student MODIFY student_number INT NOT NULL UNIQUE;

'최태웅'이라는 학생 정보를 담고 있는 row를 추가하려고 함.

그런데 학번에는 '구지섭' 학생의 학번을 넣음.

INSERT INTO student (name, registration_number)
	VALUES ('최태웅', 20112405);

Error Code: 1062. Duplicate entry '20112405' for key 'student.student_number'

→ student_number에 중복된 값을 가진 row가 있어서 새 row가 들어올 수 없다는 말임.

Primary Key와 Unique 속성의 차이 (심화)

	Primary Key	Unique
정의	테이블에서 특정 row 하나를 식별할 수 있도록 해주는 컬럼	어떤 컬럼의 값이 각 row마다 달라야 할 때 필요한 속성
한 테이블 vs 여러 테이블	테이블당 오직 하나만 존재함.	한 테이블에 여러 개의 Unique 속성들이 존재할 수 있음.
NULL 여부	NULL을 가질 수 없음.	NULL을 허용함.

Primary Key가 NULL이면 안 되는 이유

WHERE NULL = NULL 로 입력하면 해당 row를 찾을 수 없음.
Primary Key는 다른 테이블의 Foreign Key에 의해 참조될 수도 있는 컬럼
부모 테이블의 Primary Key가 NULL이라면 그 row를 참조해야하는 자식 테이블의 row들과 제대로 조인할 수 없음.

테이블에 CONSTRAINT 걸기

CONSTRAINT 제약사항 : 테이블에 이상한 로우가 추가되는 것을 막을 수 있음.

CONSTRAINT [제약사항 이름] CHECK (제약사항 내용) 구문

registration_number에 30,000,000보다 큰 값이 들어가면 안 됨.

ADD CONSTRAINT st_rule : st_rule이라는 이름의 제약사항을 건다는 의미
CHECK (registration_number < 30000000) : registration_number의 값이 3천만보다 작아야 함.

ALTER TABLE student
ADD CONSTRAINT st_rule CHECK (registration_number < 30000000);

제약사항 위반한 로우는 추가가 되지 않음.

DROP CONSTRAINT : 제약사항을 삭제해라.

ALTER TABLE student DROP CONSTRAINT st_rule;

제약사항을 두 개 이상 쓸 수 있음.

IN : 값이 ~ 중에 하나는 있어야 함.

ALTER TABLE student
	ADD CONSTRAINT st_rule
	CHECK (email LIKE '%@%' AND gender IN ('m', 'f'));

그밖의 컬럼 관련 작업들

컬럼 가장 앞으로 당기기

ALTER TABLE player_info
MODIFY id INT NOT NULL AUTO_INCREMENT FIRST;

컬럼 간의 순서 바꾸기

ALTER TABLE player_info
MODIFY role CHAR(5) NULL AFTER name;

컬럼의 이름과 컬럼의 데이터 타입 및 속성 동시에 수정하기
RENAME COLUMN A TO B과 MODIFY 절을 동시에 => CHANGE

ALTER TABLE player_info
CHANGE role position VARCHAR(2) NOT NULL;

여러 작업 동시에 수행하기

ALTER TABLE player_info
RENAME COLUMN id TO registration_number,
DROP COLUMN position;

이런식으로 같이 쓰면 됨.

테이블 이름 변경, 복사본 만들기, 삭제

테이블 자체를 다루는 작업 3개 : 이름 변경, 복사본 만들기, 삭제

이름 변경

학생들이 아직 학사 학위 취득 전의 대학생들이라 undergraduate로 테이블 이름 변경

RENAME TABLE student TO undergraduate;

복사본 만들기

기존의 테이블과 똑같은 컬럼 구조, 똑같은 데이터를 가진 테이블을 하나 더 만든다는 뜻
AS 뒤부터는 어떤 테이블에서 로우들을 갖고 와서 테이블을 만들지 나타냄.

CREATE TABLE copy_of_undergraduate AS SELECT * from undergraduate;

삭제

DROP TABLE copy_of_undergraduate;

테이블 컬럼 구조만 복사하기

테이블의 데이터(로우) 제외하고 그 컬럼 구조만 복제해서 새 테이블 생성

undergraduate 테이블과 똑같은 컬럼 구조를 가졌으나, 데이터는 없는 비어있는 테이블 만들기
새 테이블의 이름 : copy_of_undergraduate

CREATE TABLE copy_of_undergraduate LIKE undergraduate;

기존 테이블에서 로우 가져오기

만약 이 상태에서 다시 undergraduate 테이블에 있는 로우들을 가져오고 싶다면

INSERT INTO copy_of_undergraduate SELECT * FROM undergraduate;

이렇게 로우를 추가할 때는 두 테이블의 컬럼 구조가 일치해야함.

특정한 로우만 갖고 오고 싶을 때
undergraduate에서 major 컬럼 값이 101만 갖고 옴.

INSERT INTO copy_of_undergraduate 
	SELECT * FROM undergraduate WHERE major = 101;

TRUNCATE으로 데이터 한번에 날리기

DELETE FROM final_exam_result;

TRUNCATE final_exam_result;

◆ Foreign Key 제대로 사용하기

course 테이블과 review 테이블 만들기

course 테이블 : 각 수업들의 정보를 저장
review 테이블 : 학생들이 남긴 강의 평가들을 저장할 review 테이블
- course 테이블의 courseId 컬럼을 참조함(foreign Key).

CREATE TABLE course_rating.course (
	id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(30) NULL,
    semestar VARCHAR(6) NULL,
    maximum INT NULL,
    professor VARCHAR(10) NULL,
    PRIMARY KEY(id)
);

CREATE TABLE course_rating.review (
	id INT NOT NULL AUTO_INCREMENT,
	course_id INT NULL, 
	star INT NULL,
	comment VARCHAR(500) NULL,
	PRIMARY KEY(id)
);

Foreign Key 설정하기

Foreign Key : 한 테이블의 컬럼 중에서 다른 테이블의 특정 컬럼을 식별할 수 있는 컬럼 (외래키)
Foreign Key로 다른 테이블의 Primary Key를 참조(reference)한다고 표현
Foreign Key가 있는 테이블 : '자식 테이블(child table)'이나 '참조하는 테이블(referencing table)'
Foreign Key에 의해 참조 당하는 테이블 : '부모 테이블(parent table)', '참조당하는 테이블(referenced table)'
참조 무결성(Referential Integrity) : 두 테이블 간에 참조 관계가 있을 때 각 데이터 간에 유지되어야 하는 정확성과 일관성
- DBMS 상에서 한 테이블의 컬럼을 '이것이 다른 테이블의 컬럼을 참조하는 Foreign Key'라고 설정해 놓으면 참조 무결성을 지킬 수 있음.

Foreign Key 설정하기

ALTER TABLE `course_rating`.`review` 
ADD CONSTRAINT `fk_review_table`
  FOREIGN KEY (`course_id`)
  REFERENCES `course_rating`.`course` (`id`)
  ON DELETE RESTRICT
  ON UPDATE RESTRICT;

course_id를 외래키로 씀. course 테이블의 id를 찹조함.
CONSTRAINT (제약사항 이름)은 없어도 됨.
하지만 사용자가 직접 이름을 지정해야 나중에 찾기 편함.

SHOW CREATE TABLE 문으로 현제 테이블 어떻게 만들 수 있는지 보기

오른쪽버튼 - Copy Field(unquoted) 클릭

현재 상태의 review 테이블을 만들려면 CREATE TABLE 문을 어떻게 써야하는지 알 수 있음.

review 테이블을 처음 만들때부터 Foreign Key 설정을 해주려면 이런 식으로 문법을 쓰면 됨.

Foreign Key로 보장되는 참조 무결성

foreign key는 자식 테이블의 부모 테이블에 대한 참조 무결성을 지키기 위해 설정함.

review 테이블 (자식 테이블, 참조함)
course 테이블 (부모 테이블, 참조 당함)

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`course_rating`.`review`, CONSTRAINT `fk_review_table` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT)

child row = 자식 테이블(review 테이블)의 row

review 테이블에 로우를 추가할 수 없다는 말임.

결론 : 참조무결성을 깨는 row는 아예 추가가 되지 않음.

부모 테이블의 row가 삭제될 때 - RESTRICT

restrict 정책 : 자신을 참조하고 있는 자식 테이블의 로우가 하나라도 있는 부모 테이블의 로우는 아예 삭제할 수 없음.
(= no action)

자식 테이블의 로우들이 어떻게 될지 고민할 필요도 없이
애초에 부모 테이블의 로우를 삭제하지 못하도록 함.

만약 삭제하고 싶으면 이 자식테이블의 로우를 먼저 삭제한 후 부모테이블의 로우를 지워야 함.

부모 테이블의 row가 삭제될 때 - CASCADE

CASCADE : 폭포수처럼 떨어지다, 연쇄 작용을 일으키다

부모 테이블의 row가 삭제되면 그것을 참조하고 있던 자식 테이블의 로우도 같이 삭제됨.

course id가 5였던 리뷰들이 다 사라짐.

부모 테이블의 row가 삭제될 때 - SET NULL

부모 테이블의 row가 삭제되면 그것을 참조하던 자식 테이블의 foreign key 컬럼의 값을 null로 변경함.

부모 테이블의 row에서 참조당하는 컬럼이 갱신될 때는?

On Update에서

RESTRICT : 부모 테이블에 참조당하는 컬럼은 갱신되지 않음. 에러가 남.
CASCADE : 부모 테이블에 참조당하던 컬럼이 갱신되면 그것을 참조하던 자식 테이블의 foreign key 컬럼도 따라서 갱신되도록 함.
SET NULL : 부모 테이블에 참조당하던 컬럼이 갱신되면 그것을 참조하던 자식 테이블의 foreign key 컬럼은 NULL이 됨.

논리적 Foregin Key, 물리적 Foreign Key

논리적 Foreign Key이지만 물리적 Foreign Key로 굳이 설정하지 않는 이유

성능 문제

물리적 Foreign Key가 있는 자식 테이블의 경우에는 INSERT, UPDATE 문 등이 실행될 때 약간의 속도 저하가 발생할 가능성
일단 당장 빠른 성능이 중요하다면 물리적 Foreign Key를 굳이 설정하지 않을 수 있음.
일단은 INSERT, UPDATE 문 등이 보다 더 빠르게 실행되도록 하고, 참조 무결성을 어기는 데이터들은 정기적으로 별도 확인 후에 삭제해주는 방식

레거시(Legacy) 데이터의 참조 무결성

레거시 : 프로그램의 기존 코드, 기존 데이터 등
그동안 물리적 Foreign Key 없이 데이터를 쌓아와서 참조 무결성을 어기는 row들이 생겨버린 상황

Foreign Key를 삭제하는 방법

 DROP FOREIGN KEY (테이블명)

스키마(Schema)

스키마(Schema)
- 데이터베이스에 관한 모든 설계사항
- 각 테이블의 컬럼 구조와 각 컬럼의 데이터 타입 및 속성이 어떻게 되고, 테이블 간의 관계는 어떻게 되는지 등
데이터베이스 모델링 (데이터베이스 디자인) : 스키마를 짜는 것
개념적 스키마(Conceptual Schema)
하나의 조직, 하나의 기관, 하나의 서비스 등에서 필요로 하는 데이터베이스 설계사항
물리적 스키마(Physical Schema)
- 물리적 스키마는 데이터를 실제로 컴퓨터의 저장장치에 어떤 방식으로 저장할지를 결정
- 데이터를 실제로 컴퓨터의 저장장치에 어떤 방식으로 저장할지를 결정
- 저장 스키마(Storage Schema), 내부 스키마(Internal Schema)
MySQL에서는 스키마를 그냥 Database와 같은 의미로 혼용
Oracle에서 스키마는 하나의 사용자가 만든 각종 객체(테이블, 뷰 등)의 집합

728x90