MySQL 마지막날, 기본 총 복습.
> 스텝1 DB 생성
CREATE DATABASE course_rating IF NOT EXISTS course_rating;
> 스텝2 조회
USE 문을 써도 다른 데이터베이스 안의 것들을 언제든지 조회할 수 있다
USE course_rating;
> 스텝3 테이블생성
INSERT INTO test(id, DT, TS)
VALUES(1,'1992-03-26 09:13:27', '1992-03-26 09:13:27');
INSERT INTO food_menu (menu, price, ingredient)
VALUES
('라볶이', 5000, '라면, 떡, 양파..'), ('치즈김밥', 3000, '치즈, 김, 단무지..'),
('돈까스', 8000, '국내산 돼지고기, 양배추..'), ('오므라이스', 7000, '계란, 당근..');
>스텝4 컬럼의 기존값 변경
UPDATE student
SET major = '멀티미디어학과', name = '차소원'
WHERE id = 2;UPDATE final_exam_result
SET score = score+3;
>스텝5
- 로우 삭제
DELETE FROM student WHERE id = 4;‘물리 삭제’
DELETE FROM order WHERE id = 2;
‘논리 삭제’
UPDATE order SET is_cancelled = ‘Y’; 처럼 UPDATE
해석
테이블에 is_cancelled 같은 컬럼을 추가하고 해당 컬럼에 주문이 취소되었음을 나타내는 Y(es)라는 값을 넣어줌.(is_cancelled, is_deleted 등 사용할 수 있는 컬럼 이름은 다양합니다)
나중에 삭제되지 않고 유효한 row들만 조회해야할 때는
SELECT * FROM WHERE is_cancelled != ‘Y’;
SELECT * FROM WHERE is_deleted != ‘Y’;
이처럼 WHERE 절에 별도의 조건을 추가해줘야해서 번거롭다는 단점.
사용자가 주문을 취소했다고 해도 해당 주문은 사용자의 취향, 기호 등이 반영된 소중한 데이터입니다. 즉, 기업 입장에서는 고객 분석을 위해 필요한 소중한 데이터라는 거죠. 그래서 설사 소비자는 주문을 취소(삭제)했다고 하더라도 기업은 해당 데이터를 계속 보관하고 있는 겁니다.
뿐만 아니라 이런 논리 삭제는 향후에 범죄 수사 등에 도움이 될 수도 있습니다. 예를 들어, 어떤 사람이 SNS 사이트에 가입해서 게시판에서 남을 비방하는 유언비어 게시물을 여러 개 남기는 등의 행위를 하고 바로 해당 사이트를 탈퇴해도 논리 삭제를 했다면, 관련 계정을 바로 찾을 수 있겠죠?
저장용량은 줄어들지 않는다는 단점도 있습니다. 따라 이미 데이터 분석에 활용되었거나 고객이 동의한 데이터 보유기간이 지난 row들은 정기적으로 물리 삭제하는 방법을 활용하기도 합니다.
ex.
| id | name | type | price | description | upload_date | pucharse | dis_deleted |
| 1 | 신생아 모자 | 의류 | 20000 | 굵은 망사형 제품으로.. | 2019-03-23 | N | Y |
| 2 | 아이팻 프로 | 전자기기 | 300000 | 작동하는데는 큰 문제 없어요, 대신.. | 2020-01-05 | Y | Y |
id : PRIMARY KEY 컬럼
name : 중고 물품 이름
type : 중고 물품 카테고리
price : 판매자가 제시한 가격
description : 판매자의 중고 물품에 대한 설명
upload_date : 판매자가 중고 물품 판매 게시글을 업로드한 날짜
purchased : 판매 완료 여부, 판매가 완료되었으면 ‘Y’ / 아직 판매되지 않았으면 ‘N’ 을 값으로 가짐
is_deleted : 게시글 삭제 여부, 판매자가 게시글을 삭제했으면 ‘Y’ / 게시글을 업로드해둔 상태면 ‘N’을 값으로 가짐
이 사이트에서는 현재 판매자가 자신이 올린 게시글을 다시 삭제할 때, 실제로 row를 삭제하지는 않고 is_deleted 컬럼에 ‘Y’ 값을 적는 ‘논리 삭제’ 방식을 택하고 있는데요.
# (1) ‘남성정장 상하의 세트' 중고 물품이 너무 팔리지 않아서인지 판매자가 삭제해버렸습니다.
# 이 row의 is_deleted 컬럼의 값을 Y로 갱신해주세요.
UPDATE item SET is_deleted = 'Y' WHERE id = 3;
# (2) is_deleted 컬럼의 값이 Y이면서, 그 게시글 업로드일이 2020년 7월 5일 기준으로
# 365일보다 더 오래된 상품들의 row를 물리 삭제하세요.
DELETE FROM item WHERE is_deleted = 'Y'
AND DATEDIFF('2020-07-05', upload_date) > 365;
>스텝6
컬럼 삭제
ALTER TABLE `테이블 이름` DROP COLUMN `컬럼 이름`;
컬럼에 새컬럼 추가
ALTER TABLE student
ADD gender CHAR(1) NULL;
컬럼의 이름 변경
ALTER TABLE `테이블 이름` RENAME COLUMN `기존 이름` TO `새 이름`;
ALTER TABLE student
RENAME COLUMN student_number TO registration_number;
테이블의 기존 컬럼의 데이터 타입을 변경할 때
ALTER TABLE `테이블 이름` MODIFY `새로 설정할 컬럼 정보`;
a 작업 : name 컬럼의 이름을 model로 수정
b 작업 : size 컬럼의 데이터 타입을 INT에서 DOUBLE로 수정
c 작업 : brand 컬럼을 삭제
d 작업 : stock 컬럼(데이터 타입이 INT이고, NOT NULL 속성을 가진 컬럼) 추가
a 작업 - ALTER TABLE shoes RENAME COLUMN name TO model;
b 작업 - ALTER TABLE shoes MODIFY size DOUBLE NOT NULL;
c 작업 - ALTER TABLE shoes DROP COLUMN brand;
d 작업 - ALTER TABLE shoes ADD stock INT NOT NULL;
>스텝7 시간자동함수
#테이블에 게시글 추가
INSERT INTO post(title, content, upload_time, recent_modified_time)
VALUES("기분 좋은 날이네요~!", "오늘은 경주 불국사를 가보았어요, 파란 하늘과...", NOW() NOW());
#확인
SELECT * FROM FOR_TEST.post;
#갱신
UPDATE post
SET content = "오늘 제가 간 곳은 어디일까요? 그곳은 바로 경주 불국사...",
recent_modified_time = NOW()
WHERE id - 1;
#컬럼에 새속성
DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP 속성 설정하기
ALTER TABLE post
MODIFY upload_time DATETIME DEFAULT CURRENT_TIMESTAMP,
MODIFY recent_modified_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
INSERT INTO post(title, content)
VALUES ('오랜만에 등산을 했습니다!', '여름이 되고, 등산을 하고픈 마음에..');
#지금 title, content 컬럼에만 값을 주고, upload_time, recent_modified_time 컬럼에는 값을 주지 않았음에도 현재 시간이 값으로 잘 들어갔네요. --> 두 컬럼에 DEFAULT CURRENT_TIMESTAMP 속성을 줬기 때문입니다.
T_TIMESTAMP 속성, ON UPDATE CURRENT_TIMESTAMP 속성을 설정해서 DBMS가 알아서 관리하도록 하는게 좋겠죠?
>스텝8 유니크주기
ATLER TABLE student
MODIFY registration_number
INT NOT NULL UNIQUE;
>스텝9 제한추가/삭제
제한설정
ALTER TABLE student
ADD CONSTRINT 제한의이름 CHECK (registration_number< 30000);
삭제
ALTER TABLE student DROP CONSTRAINT 제한이름;
두개이상제한
ALTER TABLE student
ADD CONSTRING st_rule
CHECK (email LIKE '%@%' AND gender IN ('m', 'f');