컬럼

컬럼 구조 변경

DESCRIBE

DESCRIBE를 사용해 테이블의 컬럼 정보를 한 번에 볼 수 있다.

DESCRIBE 테이블 이름;

줄여서 DESC라고 작성해도 된다.

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL	auto_increment
name	varchar(20)	YES		NULL

Field : 컬럼의 이름
Type : 컬럼의 데이터 타입
Null : 컬럼의 Null 속성 유무
Key : Primary Key, Unique 속성 여부
Default : 컬럼의 기본값
Extra : AUTO_INCREMENT 등의 기타 속성

컬럼 추가

ALTER TABLE 테이블 이름 ADD 컬럼 이름 데이터 타입 컬럼 속성;

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 테이블 이름 DROP COLUMN 컬럼 이름;

ALTER TABLE student DROP COLUMN admission_date;

컬럼의 데이터 타입 변경

ALTER TABLE 테이블 이름 MODIFY 컬럼 이름 데이터 타입;

ALTER TABLE student MODIFY major INT;

잘못된 데이터 타입으로 변경하려 하면 데이터를 잃을 수 도 있으므로 DBMS에서 막고 에러를 발생시킨다.

CHANGE와 MODIFY는 속성을 변경할 때 다른 유지시키고 싶은 모든 속성을 명시해야한다.
그렇지 않으면 다른 속성들이 변경 시 유지되는 게 아니라 DEFAULT 값으로 변경된다.

safe update

UPDATE student SET major = 10; 

UPDATE student SET major = 10 WHERE major = ‘컴퓨터공학과’;

만약 safe update모드가 켜져 있다면

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

이런 에러를 발생시키는데 위와 같이 모든 row의 특정 컬럼을 갱신하는 sql문이나 WHERE절에 PK가 사용되지 않는 UPDATE문이 실행되지 않게 된다.

Edit - Preferences - SQL Editor에서 safe Updates를 끄고 재접속 하면 된다.

컬럼에 속성 주기

NOT NULL 속성 주기

ALTER TABLE 테이블 이름 MODIFY 컬럼이름 데이터타입 컬럼의속성  

ALTER TABLE student MODIFY name VARCHAR(20) NOT NULL;

컬럼에 속성을 변경할 때는 그 앞에 원래의 데이터 타입도 써줘야 한다.
데이터 타입과 속성을 동시에 변경할 수 도 있다.

DEFAULT 속성 주기

ALTER TABLE 테이블 이름 MODIFY 컬럼이름 데이터타입 컬럼의속성 DEFAULT 값;

ALTER TABLE student MODIFY major INT NOT NULL DEFAULT 101;

row추가시에 아무값도 작성하지 않으면 DEFAULT로 설정된 값이 들어가게 된다.
즉 NN이더라도 DEFAULT속성을 주면 값을 입력하지 않아도 에러가 나지 않으며 DEFAULT로 설정된 값이 들어가게 된다.

추가/갱신 시간 속성

NOW()

NOW()는 현재시간을 리턴해준다.
컬럼에 업로드 시간과 갱신 시간을 추가하고 싶다면 NOW()를 사용하면 된다.

INSERT INTO post (title, contnet, upload_time, recent_modified_time)
    VALUES ("제목", "내용", NOW() ,NOW());

이 포스트의 내용을 갱신할때는

UPDATE post
    SET content = "내용수정",
        recent_modified_time = NOW()
    WHERE id = 1;

이런식으로 갱신 시간만 다시 NOW()를 사용해주면 된다.

DEFAULT CURRENT_TIMESTAMP, ON UPDATE CURRENT_TIMESTAMP

NOW()처럼 일일이 작성해 주지 않아도 자동으로 추가되는 기능의 속성이다.
DEFAULT CURRENT_TIMESTAMP 속성은 테이블에 새 row를 추가할 때 따로 그 컬럼에 값을 주지 않아도 현재 시간이 설정되도록 하는 속성이다.
그리고 ON UPDATE CURRENT_TIMESTAMP 속성은 기존 row에서 단 하나의 컬럼이라도 갱신되면 갱신될 때의 시간이 설정되도록 하는 속성이다.

ALTER TABLE post
    MODIFY upload_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    MODIFY recent_modified_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

post 테이블의 두 속성을 다음과 같이 수정해주면 upload_time, recent_modified_time컬럼에는 값을 주지 않아도 자동으로 시간이 추가되고 갱신된다.

각 row마다 시간값에 관한 처리를 다르게 하는 경우(현재 시간에서 +3을 한다던가)는 NOW()를 쓰면되고 그럴 필요가 없다면 DEFAULT CURRENT_TIMESTAMP, ON UPDATE CURRENT_TIMESTAMP속성을 사용하면 된다.

UNIQUE 속성 주기

UNIQUE 속성을 주면 그 컬럼에 같은 값을 가진 또 다른 row가 추가되는 것을 막을 수 있다.

ALTER TABLE student MODIFY registration_number INT NOT NULL UNIQUE;

PK와 UNIQUE의 차이점은 PK는 테이블당 하나만 존재할 수 있고 NULL을 가질 수 없지만 UNIQUE는 테이블에 여러개의 UNIQUE속성이 존재할 수 있고 NULL을 허용한다는 차이가 있다.

CONSTRAINT

테이블에 이상한 로우가 추가되는 것을 막을 수 있다.

ALTER TALBE 테이블이름
    ADD CONSTRAINT 제약이름 CHECK 제약조건;

ALTER TABLE student
	ADD CONSTRAINT st_rule CHECK (registration_number < 300000);

CONSTRAINT 추가

ALTER TABLE 테이블이름 DROP CONSTRAINT 제약이름;

ALTER TABLE student DROP CONSTRAINT st_rule;

CONSTRAINT 삭제

ALTER TALBE 테이블이름
    ADD CONSTRAINT 제약이름
    CHECK (제약조건 AND 제약조건);

ALTER TABLE student
    ADD CONSTRAINT st_rule
    CHECK (email LIKE '%@%' AND gender IN ('m', 'f'));

여러 조건을 한번에 걸 수도 있다.

SELECT * FROM information_schema.table_constraints;

만들어둔 CONSTRAINT는 이렇게 확인할 수 있다.

컬럼 가장 앞으로 당기기

ALTER TABLE 테이블 이름
    MODIFY 컬럼이름 데이터타입 컬럼속성 FIRST;

ALTER TABLE player
    MODIFY id INT NOT NULL AUTO_INCREMENT FIRST;

컬럼 간의 순서 바꾸기

ALTER TABLE 테이블 이름
    MODIFY 컬럼이름 데이터타입 컬럼속성 AFTER 컬럼이름;

ALTER TABLE player
    MODIFY role CHAR(5) NULL AFTER name;

role컬럼을 name컬럼 다음으로 위치를 바꾼다.

컬럼의 이름과 데이터 타입 및 속성 동시에 수정

CHANGE를 사용하면 된다.

ALTER TABLE 테이블 이름
    CHANGE 기존컬럼이름 바꿀컬럼이름 데이터타입 컬럼속성;

ALTER TABLE player
    CHANGE role position VARCHAR(2) NOT NULL;

role컬럼이름을 position으로 바꾸고 데이터타입 속성도 바꾸게된다.

여러 작업 동시에 수행

ALTER TABLE문 뒤에는 컬럼에 관한 작업을 하는 절들을 여러 개 둘 수도 있다.

ALTER TABLE player
    RENAME COLUMN ~~,
    MODIFY ~~,
    DROP COLUMN ~~

테이블 이름 변경, 복사본, 삭제

이름 변경

RENAME TABLE 기존이름 TO 바꿀이름;

RENAME TABLE student TO undergraduate;

student 테이블 이름이 undergraduate로 변경된다.

복사본

CREATE TABLE 복사본이름 AS SELECT * FROM 기존이름;

CREATE TABLE copy_of_undergradutate AS SELECT * from undergraduate;

undergraduate테이블을 복사해 copy_of_undergradutate이름의 복사본을 새로 만든다.
테이블을 바로 수정하기 부담스러울때 복사본으로 사용하면 된다.

삭제

DROP TABLE 테이블이름;

DROP TABLE copy_of_undergradutate;

copy_of_undergradutate테이블을 삭제한다.

구조만 복제

CREATE TABLE 복사본이름 LIKE 기존이름;

CREATE TABLE copy_of_undergraduate LIKE undergraduate;

undergraduate의 구조만 복사해서 copy_of_undergraduate이름으로 복제본을 만든다.

row가져오기

INSERT INTO 값넣을 테이블 이름 SELECT * FROM 복사될 테이블 이름;

INSERT INTO copy_of_undergraduate SELECT * FROM undergraduate;

undergraduate테이블의 로우들을 복사해 copy_of_undergraduate에 붙여넣기 한다.
두 테이블간의 컬럼의 구조가 일치해야 가능하다.
잘보면 INSERT INTO 문 안에 SELECT문이 들어가있는 서브쿼리 형태이다.

INSERT INTO copy_of_undergraduate 
	SELECT * FROM undergraduate WHERE major = 101;

이런식으로 원하는 로우만 가져올 수 도 있다.

TRUNCATE

테이블의 뼈대는 남겨두고 모든 로우들을 삭제하고 싶다면

DELETE FROM 테이블이름;

이렇게 해도 되지만 TRUNCATE를 사용해도 된다.

TRUNCATE 테이블이름;

실행 결과는 같지만 내부적인 동작의 차이가 있다. TRUNCATE의 속도가 더 빠르지만 DELETE는 데이터 복구가 가능하고 로그를 남긴다는 차이가 있다.