SQL - MySQL, 문법

MySQL

 

 

✅ ALTER

---

ALTER TABLE 명령어는 꽤 비용이 클 수 있다.(row의 개수가 많다면 새로 생성된 column의 모든 row에 기본값을 줘야 하기 때문)

SQLite에서도 ALTER를 사용할 수 있지만 Table 이름 변경, Column 이름 변경, Column 추가만 가능함

(이미 설정된 column을 제거하거나 데이터 타입을 변경하거나, 제약 조건을 추가 혹은 삭제하는 건 불가능)

 

ALTER TABLE users DROP COLUMN profile_picture;

profile_picture column 삭제

 

ALTER TABLE users
CHANGE COLUMN bio about_me TINYTEXT;

bio -> about_me 이름 변경, data type변경

 

CHANGE

ALTER TABLE users
CHANGE COLUMN about_me about_me TEXT;

data type만 변경

 

MODIFY

ALTER TABLE users
MODIFY COLUMN about_me TEXT;

MODIFY를 사용해 이름 2번 적을 필요없이 간단하게 데이터타입만 변경시킬 수 있다.

 

ALTER TABLE users
MODIFY COLUMN bed_time TIME NULL;

ALTER TABLE users
MODIFY COLUMN bed_time TIME NOT NULL;

nullable도 변경 가능

 

RENAME TO

ALTER TABLE users
RENAME TO customers;

 

DROP CONSTRAINT

ALTER TABLE users DROP CONSTRAINT uq_email;
ALTER TABLE users
DROP CONSTRAINT users_chk_1,
DROP CONSTRAINT username,
DROP CONSTRAINT email;

 

ADD CONSTRAINT

-- adding constraints
ALTER TABLE users
ADD CONSTRAINT uq_email UNIQUE (email),
ADD CONSTRAINT uq_username UNIQUE (username);

ALTER TABLE users
ADD CONSTRAINT chk_age CHECK (age < 100);

 

SHOW

SHOW CREATE TABLE users;

작성한 query를 데이터베이스 관점에서 어떻게 보고 있는지 확인할 수 있다.

 

 

✅ 기존 column의 값을 새 column에 옮기는 방법

---

1.

ALTER TABLE users
ADD COLUMN graduation_date DATE;

UPDATE users SET graduation_date = MAKEDATE(graduation_year, 1);

ALTER TABLE users
DROP COLUMN graduation_year;

ALTER TABLE users
MODIFY COLUMN graduation_date DATE NOT NULL;

 

2.

ALTER TABLE users
ADD COLUMN graduation_date DATE NOT NULL DEFAULT MAKEDATE(graduation_year, 1);

ALTER TABLE users
DROP COLUMN graduation_year;

ALTER TABLE users
MODIFY COLUMN graduation_date DATE NOT NULL;

 

MAKEDATE

https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_makedate

MySQL :: MySQL 8.4 Reference Manual :: 14.7 Date and Time Functions

 

 

✅ Generated Column (Computed Column)

---

다른 column을 사용하여 값을 도출하는 column

SQLite에서도 동일하게 동작

CREATE TABLE users_v2 (
	user_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(100),
	full_name VARCHAR(101) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED
);

INSERT INTO users_v2(first_name, last_name, email) VALUES ('nico', 'nomad', 'nico@nomad.com');

full_name은 Generated Column이라 직접 값을 지정해서 넣을 수 없다.

 

SUBSTRING_INDEX

ALTER TABLE users_v2
ADD COLUMN email_domain VARCHAR(50) GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL;

args로 분리 대상, 분리 기준, 1(앞부분 선택) or -1(뒷부분 선택)를 받는다.

 

STORED vs VIRTUAL

VIRTUAL Generated Column은 디스크나 데이터베이스에 저장되지 않는다.

대신 email_domain column을 조회할 때마다 

SUBSTRING_INDEX(email, '@', -1)

 

매번 이 연산을 수행한다.

 

STORED는 실제로 DB에 저장된다.

 

STORED: 삽입하거나 업데이트할 때 불리

VIRTUAL: 읽어올 때 불리