SQL Column추가,변경, 삭제, 타입 변경, DB변경 방법

Column 추가

-- 방법
ALTER TABLE 'Table Name' ADD 'Column Name' 'Data Type' NOT NULL;
-- 예시 1
ALTER TABLE users ADD email VARCHAR(100) NOT NULL;
-- 예시 2 + 주석
ALTER TABLE boards ADD comment_id BIGINT NOT NULL COMMENT '댓글 식별자';

특정 Column 뒤에 Column 추가

-- 방법
ALTER TABLE 'Table Name' ADD COLUMN 'Column Nmae' 'DataType' AFTER 'existing Column';
-- 예시1
-- users 테이블에 birth_date 컬럼을 name 컬럼 뒤에 추가
ALTER TABLE users ADD COLUMN birth_date DATE AFTER name;

맨 앞에 Column 추가

-- 방법
ALTER TABLE 'Table Name' ADD COLUMN 'Column Nmae' 'DataType' FIRST;
-- 예시1
-- users 테이블 맨 앞에 email 컬럼 추가
ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL FIRST;

Column 이름 변경

ALTER TABLE 'Table Name'
RENAME COLUMN 'Old Column Name' TO 'New Column Name';
-- 예시1
-- orders 테이블에서 order_date를 purchase_date로 변경
ALTER TABLE orders RENAME COLUMN order_date TO purchase_date;

Column 데이터 타입 변경

ALTER TABLE 테이블이름 MODIFY 컬럼명 데이터타입;
-- 예시1
-- age 컬럼의 데이터 타입을 BIGINT NOT NULL로 변경
ALTER TABLE users MODIFY COLUMN age BIGINT NOT NULL;
-- 예시2
-- salary 컬럼의 데이터 타입을 FLOAT(8,2)로 변경
ALTER TABLE employees MODIFY COLUMN salary FLOAT(8,2);

 

Column 이름, 데이터 타입 모두 변경

ALTER TABLE 'Table Name' CHANGE COLUMN 'Old Column' 'New Column' 'Data Type' NOT NULL;
-- 예시1
-- age → user_age로 변경 + 데이터 타입 INT NOT NULL로 변경
ALTER TABLE users CHANGE COLUMN age user_age INT NOT NULL;
-- 예시2
-- price → product_price로 변경 + 데이터 타입 DECIMAL(10,2)로 변경
ALTER TABLE products CHANGE COLUMN price product_price DECIMAL(10,2);

 

Column 삭제

ALTER TABLE 테이블이름 DROP COLUMN 컬럼명;
-- 예시1
-- users 테이블에서 birth_date 컬럼 삭제
ALTER TABLE users DROP COLUMN birth_date;
-- 예시2
-- orders 테이블에서 shipping_address 컬럼 삭제
ALTER TABLE orders DROP COLUMN shipping_address;

 

 

DB 이름 변경

현재 MySQL에서 데이터베이스 이름을 변경하는 방법은 직접적으로 지원되지 않는다.

그래서 다음과 같은 방법들로 우회해야한다.

 

새로운 DB 생성 후 데이터 옮기기

-- new_database라는 새 데이터베이스를 만들고 old_database의 테이블을 옮기는 방법

CREATE DATABASE new_database;
RENAME TABLE old_database.users TO new_database.users;
RENAME TABLE old_database.orders TO new_database.orders;

-- 예시1
-- company_db라는 새 데이터베이스를 생성
-- sales_db의 customers 테이블을 company_db로 이동 및 이름 유지
-- sales_db의 transactions 테이블을 company_db로 이동 및 이름 유지
CREATE DATABASE company_db;
RENAME TABLE sales_db.customers TO company_db.customers;
RENAME TABLE sales_db.transactions TO company_db.transactions;

-- 예시2
-- archive_db라는 새 데이터베이스를 생성
-- main_db의 logs 테이블을 archive_db로 이동하면서 logs_2024로 이름 변경
-- main_db의 records 테이블을 archive_db로 이동하면서 records_backup으로 이름 변경
CREATE DATABASE archive_db;
RENAME TABLE main_db.logs TO archive_db.logs_2024;
RENAME TABLE main_db.records TO archive_db.records_backup;

 

mysqldump를 사용하여 DB 복사 후 삭제

-- old_database의 모든 데이터를 backup.sql에 백업
-- new_database 생성 후 백업 데이터를 복원
-- old_database 삭제
mysqldump -u root -p old_database > backup.sql
mysql -u root -p -e "CREATE DATABASE new_database"
mysql -u root -p new_database < backup.sql
mysql -u root -p -e "DROP DATABASE old_database"

-- user_db 데이터베이스를 user_backup.sql로 백업
-- new_user_db라는 새 데이터베이스 생성
-- user_backup.sql을 new_user_db로 복원
-- 기존 user_db를 삭제
mysqldump -u root -p user_db > user_backup.sql
mysql -u root -p -e "CREATE DATABASE new_user_db"
mysql -u root -p new_user_db < user_backup.sql
mysql -u root -p -e "DROP DATABASE user_db"

 

이 방법은 터미널 창에서 실행해야한다.

 

 information_schema.tables 와 concat 을 이용한 방법

일단 이 방법은 옮기려는 새로운 DB가 존재 해야한다.

같은 MySQL 서버에서 DB를 바꿀때 빠른 방법 같다(?)

하지만 외래키가 있는 경우엔 어떻게 될지 모른다..

 

-- new_testDB DB의 있는 모든 테이블을 old_testDB에 옮기는 RENAME TABLE SQL문이 결과로 나옴
-- 바로 옮기는 것이 아님

SELECT concat('RENAME TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' TO ','new_testDB.',TABLE_NAME,';')
FROM information_schema.tables
WHERE TABLE_SCHEMA LIKE 'old_testDB';

 

만약에 old_testDB에 users, orders, products 테이블이 있다고 가정하면  위 SQL문의 실행 결과는 다음과 같다.

RENAME TABLE old_testDB.users TO new_testDB.users;
RENAME TABLE old_testDB.orders TO new_testDB.orders;
RENAME TABLE old_testDB.products TO new_testDB.products;

 

그 후 생성된 SQL문을 차례대로 실행하면 된다

1. old_testDB.users → new_testDB.users 로 이동
2. old_testDB.orders → new_testDB.orders 로 이동
3. old_testDB.products → new_testDB.products 로 이동
4. 이제 old_testDB 데이터베이스는 존재하지만, 모든 테이블이 new_testDB 로 이동했기 때문에 비어 있게 된다.

 

 

출처 : https://ryean.tistory.com/41#google_vignette

[MySQL] 데이터베이스 이름 변경 (RENAME DATABASE)