본 글은 갖고노는 MySQL 데이터베이스 by 얄코 강의를 수강하고 이를 정리한 글입니다.
데이터 조작하기
1. MySQL 설치하기
MacOS에서는 homebrew를 이용해서 간단하게 설치할 수 있다.
$ brew install mysqlMySQL을 설치하고서는, 보안을 강화하기 위해 다음 명령어를 실행하여 보안 패키지를 설치할 것이 권장된다.
$ mysql_secure_installation그렇다면 여러 가지 옵션들이 나오는데(패스워드 설정, Anonymous 유저 삭제, 기본 테스트 스키마 삭제할 것인지 등), 원하는 것을 선택해주면 된다. 나의 경우는 전부 Y로 체크했다.
비밀번호같은 경우는 강도를 설정하라는 안내도 나올 수 있다.
MySQL 서버를 실행하려면 다음 두 가지 명령어 중 하나를 이용하면 된다. 어느 것을 사용해도 무방하다.
$ brew services start mysql
$ mysql.server start서버에 접속하려면 다음과 같이 접속한다.
$ mysql -uroot -p그렇다면 패스워드를 입력하라는 커맨드가 뜨는데, 이때 이전에 설정해둔 패스워드를 입력하고 진입하면 된다. MySQL 커맨드 창이 뜰 것이다.
MySQL 프로그램을 종료하려면 \q를 누르면 된다. 서버를 종료하려면 시작할 때와 비슷하게 아래 두 명령어 중 하나를 입력하면 된다.
$ brew services stop mysql
$ mysql.server stop2. 테이블 만들고 데이터 입력하기
한 가지 유의할 것은, 실제로 테이블을 만들고 조작하는 것은 DataGrip 등과 같은 DB 전문 GUI 어플리케이션을 이용하는 것이 더 좋다는 것을 기억하자.
1. 테이블 생성/수정/삭제
CREATE TABLE : 테이블 만들기
CREATE TABLE people (
person_id INT,
person_name VARCHAR(10),
age TINYINT,
birthday DATE
);
-- DataGrip 등에서는 PK를 지정해주지 않았기 때문에, 이 쿼리는 자체적으로 오류/경고가 발생할 수 있음ALTER TABLE : 테이블 변경하기
-- 테이블명 변경
ALTER TABLE people RENAME TO friends,
-- 컬럼 자료형 변경
CHANGE COLUMN person_id person_id TINYINT,
-- 컬럼명 변경
CHANGE COLUMN person_name person_nickname VARCHAR(10),
-- 컬럼 삭제
DROP COLUMN birthday,
-- 컬럼 추가
ADD COLUMN is_married TINYINT AFTER age;DROP TABLE : 테이블 삭제하기
DROP TABLE friends;
-- 보통은 안전 장치 옵션을 꺼야 작동함2. INSERT INTO : 데이터 삽입
INSERT INTO people
(person_id, person_name, age, birthday)
VALUES (1, '홍길동', 21, '2000-01-31');
-- 모든 컬럼에 값 넣을 때는 컬럼명들 생략 가능
INSERT INTO people
VALUES (2, '전우치', 18, '2003-05-12');
-- 일부 컬럼에만 값 넣기 가능 (NOT NULL은 생략 불가)
INSERT INTO people
(person_id, person_name, birthday)
VALUES (3, '임꺽정', '1995-11-04');
-- 자료형에 맞지 않는 값은 오류 발생
INSERT INTO people
(person_id, person_name, age, birthday)
VALUES (1, '임꺽정', '스물여섯', '1995-11-04');
-- TINYINT인 age에 문자열이 들어가므로 오류 발생
-- 여러 행을 한 번에 입력 가능
INSERT INTO people
(person_id, person_name, age, birthday)
VALUES
(4, '존 스미스', 30, '1991-03-01'),
(5, '루피 D. 몽키', 15, '2006-12-07'),
(6, '황비홍', 24, '1997-10-30');3. 테이블 생성 시 제약 넣기
CREATE TABLE people (
person_id INT AUTO_INCREMENT PRIMARY KEY,
person_name VARCHAR(10) NOT NULL,
nickname VARCHAR(10) UNIQUE NOT NULL,
age TINYINT UNSIGNED,
is_married TINYINT DEFAULT 0
);| 제약 | 설명 |
|---|---|
| AUTO_INCREMENT | 새 행 생성시마다 자동으로 1씩 증가 |
| PRIMARY KEY | 중복 입력 불가, NULL(빈 값) 불가 |
| UNIQUE | 중복 입력 불가 |
| NOT NULL | NULL(빈 값) 입력 불가 |
| UNSIGNED | (숫자일시) 양수만 가능 |
| DEFAULT | 값 입력이 없을 시 기본값 |
PRIMARY KEY:
- 테이블마다 하나만 가능
- 자동으로 PK 기준 인덱스를 생성함
- 보통은
AUTO_INCREMENT와 같이 붙음
INSERT INTO people
(person_name, nickname, age)
VALUES ('김철수', '아이언워터', 10);
-- OK아래는 에러난다
INSERT INTO people
(person_name, nickname, age)
VALUES ('이불가', '임파서블', -2);
-- age: TINYINT UNSIGNED에 음수가 들어가므로 에러
INSERT INTO people
(person_name, nickname, age, is_married)
VALUES ('박쇳물', NULL, NULL, 1);
-- nickname에 NULL, '아이언수' 넣어보기
-- nickname: NOT NULL에 NULL이 들어오므로 에러3. 자료형
숫자 자료형
정수
| 자료형 | 바이트 | SIGNED | UNSIGNED |
|---|---|---|---|
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
| SMALLINT | 2 | -32,768 ~ 32,767 | 0 ~ 65,535 |
| MEDIUMINT | 3 | -8,388,608 ~ 8,388,607 | 0 ~ 16,777,215 |
| INT | 4 | -2,147,483,648 ~ 2,147,483,647 | 0 ~ 4,294,967,295 |
| BIGINT | 8 | -2^63 ~ 2^63 - 1 | 0 ~ 2^64 - 1 |
고정 소수점(Fixed Point)
| 자료형 | 설명 | 범위 |
|---|---|---|
| DECIMAL( s, d ) | 실수 부분 총 자릿수( s ) & 소수 부분 자릿수 ( d ) | s 최대 65 |
DECIMAL(5, 3) 이라는 것은 25.123, 36.012 와 같이, 소수점 이하 포함 전체 자리수는 5 자리이고, 소수점 이하 세 자리까지만 표시하겠다는 것을 의미
부동 소수점(Floating Point)
| 자료형 | 바이트 | 표현 범위 |
|---|---|---|
| FLOAT | 4 | -3.402...E+38 ~ -1.175...E-38 , 0 , 1.175...E-38 ~ 3.402...E+38 |
| DOUBLE | 8 | -1.797...E+308 ~ -2.225E-308 , 0 , 2.225...E-308 ~ 1.797...E+308 |
문자 자료형
문자열
| 자료형 | 설명 | 차지하는 바이트 | 최대 바이트 |
|---|---|---|---|
| CHAR( s ) | 고정 사이즈 (남는 글자 스페이스로 채움) | s (고정값) | 255 |
| VARCHAR ( s ) | 가변 사이즈 | 실제 글자 수[최대 s] + 1 [글자수 정보] | 65,535 |
| Value | CHAR(4) | Storage Required | VARCHAR(4) | Storage Required |
|---|---|---|---|---|
| '' | ' ' | 4 bytes | '' | 1 byte |
| 'ab' | 'ab ' | 4 bytes | 'ab' | 3 bytes |
| 'abcd' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
| 'abcdefgh' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
- 검색 성능은
CHAR이 더 빠르다 VARCHAR은 문자열의 길이 정보를 저장하기 위해, 한 바이트가 더 필요하다. 즉 매번 길이가 최대인 문자열이 들어올 때는CHAR이 더 유리하다- 그렇지 않을 때는 길이를 가변적으로 조절 가능한
VARCHAR이 더 유리하다 - 4 미만의 문자열은
VARCHAR이여도 자동으로CHAR로 바뀌어서 저장됨 - 참고로
VARCHAR에서 길이 제한이 있는 것은 용량 조절보다는 입력되는 데이터를 통제하는 것이 주요 목적이다 (누군가 실수나 악의적으로 최대 길이의 문자열만 넣는 것을 방지)
텍스트
| 자료형 | 최대 바이트 크기 |
|---|---|
| TINYTEXT | 255 |
| TEXT | 65,535 |
| MEDIUMTEXT | 16,777,215 |
| LONGTEXT | 4,294,967,295 |
텍스트같은 경우는 블로그의 포스트 등, 길이가 매우 큰 데이터를 저장할 때 사용한다. VARCHAR같은 경우는 주소 등을 저장할 때 쓴다는 차이점이 있다.
일반적으로 텍스트는 부분적으로만 인덱스가 가능하다. MySQL같은 경우에는 TEXT가 VARCHAR 보다 성능이 떨어진다.
VARCHAR의 MAX SIZE인 65,535바이트 행의 최대 용량과 같다. 그래서 VARCHAR에서 데이터를 MAX로 차지하는 순간 각 행의 다른 열에 데이터 추가가 불가능하다. 이와 반대로 TEXT는 자체적으로 다른 곳에 데이터를 저장하기에, 65,535바이트 이상의 데이터를 저장할 때는 TEXT 를 이용하는 게 유리하다.
시간 자료형
| 자료형 | 설명 | 비고 |
|---|---|---|
| DATE | YYYY-MM-DD | |
| TIME | HHH:MI:SS | HHH: -838 ~ 838까지의 시간 |
| DATETIME | YYYY-MM-DD HH:MI:SS | 입력된 시간을 그 값 자체로 저장 |
| TIMESTAMP | YYYY-MM-DD HH:MI:SS | MySQL이 설치된 컴퓨터의 시간대를 기준으로 저장 |
- DATETIME은 시간 자체를 저장(모든 시간대에서 똑같음)
- TIMESTAMP는 기준시(1970년 1월 1일 00:00 AM으로부터 얼마나 시간이 지났는지를 기록)
- 시간대에 따라서 달라진다
- 2038 Problem이 존재한다는 것에 유의
- https://medium.com/finda-tech/mysql-timestamp-와-y2k38-problem-d43b8f119ce5
4. 데이터의 변경과 삭제
DELETE
DELETE FROM businesses
WHERE status = 'CLS';
-- status가 'CLS'인 행 삭제
DELETE FROM businesses;
-- 🚨 행 전체 삭제, 주의해서 사용 🚨TRUNCATE
TRUNCATE businesses;
-- PK 넘버링을 포함해서, 전체 행이 삭제된다INSERT INTO businesses (fk_section_id, business_name, status, can_takeout)
VALUES (3, '화룡각', 'OPN', 1),
(2, '철구분식', 'OPN', 1),
(5, '얄코렐라', 'RMD', 1);| business_id | fk_section_id | business_name | status | can_takeout |
|---|---|---|---|---|
| 1 | 3 | 화룡각 | OPN | 1 |
| 2 | 2 | 철구분식 | OPN | 1 |
| 3 | 5 | 얄코렐라 | RMD | 1 |
일반적으로는 DELETE를 해도, PK 번호는 초기화되지 않는다. 그러나 초기화 명령인 TRUNCATE를 사용하면, PK도 초기화된다.
UPDATE : 행 수정하기
UPDATE menus
SET menu_name = '삼선짜장'
WHERE menu_id = 12;
-- menu_id 12의 menu_name을 삼선짜장으로 변경아래는 여러 컬럼을 수정하는 예시
UPDATE menus
SET
menu_name = '열정떡볶이',
kilocalories = 492.78,
price = 5000
WHERE
fk_business_id = 4
AND menu_name = '국물떡볶이';아래는 컬럼 데이터를 이용해서 수정하는 방식
UPDATE menus
SET price = price + 1000
WHERE fk_business_id = 8;
-- business_id가 8인 식당의 모든 메뉴 가격을 1000원 인상UPDATE menus
SET menu_name = CONCAT('전통 ', menu_name)
WHERE fk_business_id IN (
SELECT business_id
FROM sections S
LEFT JOIN businesses B
ON S.section_id = B.fk_section_id
WHERE section_name = '한식'
);
-- section_name이 한식인 business의 메뉴 이름 앞에 '전통 ' 붙이기⚠️ 조건문 없이는 모든 행이 변경될 수 있다. 일반적으로 안전 모드가 있어서 이런 위험이 1차적으로 걸러지기는 하는데, 항상 안전 모드가 걸려있는 것은 아니니 매우 조심해야 한다.
UPDATE menus
SET menu_name = '획일화';