데이터베이스와 테이블 구축
데이터베이스 생성하기
CREATE DATABASE 데이터베이스명- 단, 해당 쿼리문을 연달아 실행하면 오류가 발생함. 오류 핸들링을 위해,
CREATE DATABASE IF NOT EXISTS 데이터베이스명의 형태로 작성하면 쿼리문 자체적으로 해당 DB와 동일한 이름의 데이터베이스가 존재하는지 확인한 후 생성함
사용할 DB 지정하기: USE
USE 데이터베이스명을 작성하고 나면 이후에데이터베이스명.컬럼대신 그냥컬럼만 적어도 성립함USE A이후에B.컬럼을 사용할 수 있음
테이블 생성하기
컬럼 데이터타입 종류
CREATE TABLE
- 백틱은 해당 단어가 identifier(컬럼이름, 테이블이름, 데이터베이스이름)임을 나타내는 기호로 사용됨
테이블에 row 추가하기: INSERT
INSERT INTO student
(id, name, student_number, major) -- 컬럼명
VALUES (1, '김민지', 2233333, 'IT공학과'); -- 컬럼값- 처음 컬럼명을 선언한 이후부터는 컬럼명 부분은 생략한 채로 컬럼값 부분만 작성해도 됨
- 만약 몇몇 컬럼값 부분이 생략되어있다면 null값으로 채워져서 삽입됨
- 단, id값(PK)은 비어있더라도 자동으로 채워짐 → auto_increment 속성 덕분!
주로 PK는 테이블을 처음 생성할 때id INT NOT NULL AUTO_INCREMENT속성으로 선언함
CREATE TABLE animal_info (
`id` INT NOT NULL AUTO_INCREMENT,
`type` VARCHAR(30) NOT NULL,
`name` VARCHAR(10) NOT NULL,
`age` TINYINT NOT NULL,
`sex` CHAR(1) NOT NULL,
`weight` DOUBLE NOT NULL,
`feature` VARCHAR(500) NULL,
`entry_date` DATE NOT NULL,
PRIMARY KEY (`id`)
);
/* 테이블이 생성된 후에 아래의
INSERT문들이 실행되면서 3개의 로우가 추가됩니다 */
INSERT INTO animal_info (type, name, age, sex, weight, feature, entry_date) VALUES ('사자', '리오', 8, 'm', 170.5, '상당히 날렵하고 성격이 유순한 편임', '2015-03-21');
INSERT INTO animal_info (type, name, age, sex, weight, feature, entry_date) VALUES ('코끼리', '조이', 15, 'f', 3000, '새끼 때 무리에서 떨어져 길을 잃고 방황하다가 동물원에 들어와서 적응을 잘 마침', '2007-07-16');
INSERT INTO animal_info (type, name, age, sex, weight, feature, entry_date) VALUES ('치타', '매튜', 20, 'm', 62, '나이가 노령이라 최근 활동량이 현저히 줄어든 모습이 보임', '2003-11-20');
# 테스트 코드
SELECT * FROM animal_info;-
이 때, 만약 값이 null인 컬럼이 존재한다면 반드시 컬럼명을 앞서 언급되었다고해서 생략하지 않고 null이 아닌 컬럼명, VALUES(컬럼값) 순으로 적어줘야 함
-
auto_increment 속성을 가진 컬럼값 (예: id)을 생략하는 경우에도 반드시 컬럼명을 전부 기재해야 함
✨ 컬럼명 생략은 컬럼값이 전부 존재하는 열에서만 가능함
INSERT INTO food_menu (id, menu, price, ingredient) VALUES (1, '라볶이', 5000, '라면, 떡, 양파..');
INSERT INTO food_menu (menu, price, ingredient) VALUES ('치즈김밥', 3000, '치즈, 김, 단무지..');
INSERT INTO food_menu (menu, price, ingredient) VALUES ('돈까스', 8000, '국내산 돼지고기, 양배추..');
INSERT INTO food_menu (menu, price, ingredient) VALUES ('오므라이스', 7000, '계란, 당근..');
# 테스트 코드
SELECT * FROM food_menu;
테이블의 row 갱신하기: UPDATE
- id가 2인 학생의 전공을 IT전공으로 수정함
UPDATE student SET major='IT전공' WHERE id=2;
-- 이름도 눈송이로 개명했다고 하자
UPDATE student SET major='IT전공', name='눈송이' WHERE id=2;- 시험 문제 오류로 해당 문제가 전원 정답처리되어서 모든 학생의 점수가 +5점 올랐다고 하자.
UPDATE final_exam_result SET score = score + 5;
-- 이 방식으로 하면 UPDATE문을 학생 수만큼 반복해서 작성하는 것을 막을 수 있음. 테이블의 row 삭제하기: DELETE
- id가 4인 학생정보 row를 삭제하라
DELETE FROM student_table WHERE id=4;✨ WHERE절을 생략해버리면 테이블 전체가 삭제되는 불상사가 발생하게 됨
물리삭제 vs 논리삭제
-
물리삭제: 앞서 배운
DELETE문으로 아예 데이터베이스에서 해당정보를 지워버리는 것
이 경우의 문제점은, 만약 어떤 회원이 사이트에서 모욕성 글을 여러개 작성하고 법적조치를 피하기 위해 탈퇴를 할 경우 해당 회원의 정보를 아예 지워버리면 이후 수사에 혼선이 올 수 있음 -
논리삭제:
UPDATE문으로 아래와 같이 is_cancelled, is_deleted 등 삭제되었음을 나타내는 컬럼을 하나 추가해서 관리하는 것
이 경우의 문제점은, 이후에 유효한 row들을 조회할 경우 아래와 같이 항상 WHERE절로 조건을 걸어가며 조회해야 하고, 데이터베이스 내의 저장공간 이슈가 발생할 수 있다는 점임.
-- 삭제
UPDATE order SET is_cancelled = 'Y';
-- 유효한 row 조회
SELECT * FROM WHERE is_cancelled != 'Y';
SELECT * FROM WHERE is_deleted != 'Y';DESCRIBE (=DESC)
테이블 다루기: ALTER TABLE
컬럼 추가: ADD
ALTER TABLE student_table ADD gender CHAR(1) NULL;ADD뒤에 컬럼 이름 / 컬럼의 데이터 타입 / 컬럼의 속성 순으로 작성
컬럼의 이름 변경: RENAME
ALTER TABLE student_table
RENAME COLUMN student_number TO registration_number;컬럼 삭제: DROP
ALTER TABLE student_table DROP COLUMN admission_date;컬럼의 데이터타입 변경: MODIFY
ALTER TABLE student_table MODIFY major INT;- 이 때, 아래와 같이 컬럼값의 데이터타입을 먼저 변경하고자 하는 타입으로 바꿔준 후 컬럼의 데이터타입을 수정해야 유의미하게 오류 없이 수정할 수 있음
UPDATE student_table SET major = 10 WHERE major = '컴퓨터공학과';
UPDATE student_table SET major = 12 WHERE major = '경영학과';
UPDATE student_table SET major = 5 WHERE major = '법학과';- 총정리
ALTER TABLE shoes RENAME COLUMN name TO model;
ALTER TABLE shoes MODIFY size DOUBLE NOT NULL;
ALTER TABLE shoes DROP COLUMN brand;
ALTER TABLE shoes ADD stock INT NOT NULL;컬럼의 속성들
NOT NULL속성 : 값이 반드시 존재해야 함DEFAULT속성 : 값이 만약 존재하지 않을 경우 기본값(설정 가능)으로 설정됨
ALTER TABLE student_table MODIFY major INT NOT NULL DEFAULT 101;- 현재 시간 / 날짜를 속성값으로 추가하는 법:
NOW()함수 등으로 속성값을 추가하면 현재 시간이 추가됨
https://www.codeit.kr/topics/data-management-using-sql/lessons/3282
UNIQUE속성 : 해당 컬럼에 같은 값을 가진 또다른 row가 추가되는 것을 막을 수 있음
ALTER TABLE student_table MODIFY registration_number INT NOT NULL UNIQUE;- PK와의 차이점: Primary Key는 NULL을 가질 수 없지만, Unique는 NULL을 허용
테이블에 CONSTRAINT 걸기
- 예: 학번이 3천만보다 커서는 안된다고 작성하기
ALTER TABLE student
ADD CONSTRAINT st_rule CHECK (registration_number < 30, 000, 000);-
ADD CONSTRAINT+ 제약이름 +CHECK+ 제약조건 -
제약 조건 삭제:
ALTER TABLE student DROP CONSTRAINT st_rule;- 제약 조건을 여러개 걸기:
ALTER TABLE student
ADD CONSTRAINT st_rule
CHECK (email LIKE '%@%' AND gender IN ('m', 'f'));- 총정리:
ALTER TABLE book MODIFY isbn VARCHAR(50) NOT NULL UNIQUE;
ALTER TABLE book ADD CONSTRAINT page_rule CHECK (page > 0);
ALTER TABLE book CHANGE kind genre_code INT NOT NULL;
ALTER TABLE book MODIFY location VARCHAR(10) NOT NULL DEFAULT 'warehouse';기타 컬럼 관련 작업들
테이블: 이름 변경, 복사본 생성, 삭제
- 이름 변경
RENAME TABLE student_table TO undergrad_table;- 복사본 생성: 복사하려는 테이블의 모든 row를 선택해서 가져와서 생성하는 방식
CREATE TABLE copy_of_undergrad_table AS SELECT * from undergrad_table;- 삭제
DROP TABLE copy_of_undergrad_table;테이블 컬럼 구조만 복사
- 컬럼 구조는 동일하지만 컬럼값은 비어있는 테이블을 생성해보자
CREATE TABLE copy_of_undergrad_table LIKE undergrad_table;- 만약 이후에 컬럼값까지 가져오고 싶다면?
INSERT INTO copy_of_undergrad SELECT * FROM undergrad_table;INSERT INTO, 서브쿼리
TRUNCATE
CREATE TABLE beta_review_20s LIKE beta_review;
INSERT INTO beta_review_20s SELECT * FROM beta_review WHERE age >= 20 AND age < 30;
Foreign Key 사용하기
- Foreign key로 보장되는 참조무결성
Foreign Key 생성하기
FOREIGN KEY (외래키이름)
REFERENCES 부모테이블(참조속성)부모 테이블의 row가 삭제될 때
- RESTRICT 정책: 하나의 자식이라도 참조하고 있는 부모테이블의 row는 아예 삭제되지 못하도록 막아버리는 것
→ 꼭 삭제해야 한다면 자식테이블의 row들을 삭제한 후에야 가능함 - CASCADE 정책: 부모테이블의 row가 삭제되면 연쇄적으로 해당 row를 FK로 참조하던 자식테이블의 row들도 연이어 삭제됨
*CASCADE: 연쇄작용을 일으키다 - SET NULL 정책: 부모테이블의 row가 삭제되었을 때 그것을 참조하던 자식테이블의 FK값을 모두 null로 바꿔줌
부모 테이블의 row에서 참조당하는 컬럼이 갱신될 경우?
- 삭제와 동일하게
RESTRICT정책 (갱신 불가),CASCADE정책(갱신 시 연쇄 갱신),SET NULL정책이 각각 존재함
Foreign Key 삭제하기
DROP FOREIGN KEY fk_student_table;SCHEMA
- 데이터베이스 내에 존재하는 각 테이블의 컬럼 구조, 각 컬럼의 데이터 타입 및 속성, 테이블 간의 관계 등과 같은 ‘데이터베이스 설계사항’
