테이블 다루기 2

순동·2022년 4월 8일
sql
0

📌 컬럼에 NOT NULL 속성

✅ name, registration_number, major 컬럼에 NOT NULL 속성

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

컬럼의 데이터 타입을 변경할 때도 MODIFY를 사용했다.
MODIFY는 데이터 타입 뿐만 아니라 컬럼의 속성을 변경할 때에도 사용한다.

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

registration_number 컬럼 이름을 registraion_number라고 잘못 입력한 채 사용 중이었단 걸 이번에 깨달았다 ... 🐼 (멍청) ... 아래 코드로 컬럼 이름의 오타를 수정해주었다.

ALTER TABLE student
	RENAME COLUMN registraion_number TO registration_number;

NOT NULL 속성을 가진 컬럼들에 값이 없는 row를 추가해서 속성이 제대로 적용되었는지 확인해보자.

INSERT INTO student (email, phone, gender)
	VALUES ('abc@naver.com', '010-0000-0000', 'm')

에러가 발생한다. NOT NULL 속성이 잘 적용되었다.

📌 컬럼에 DEFAULT 속성

컬럼에는 기본값을 설정해줄 수 있다. 컬럼에 기본값을 설정해두면 row를 추가할 때 해당 컬럼에 값을 주지 않더라도 기본값이 입력된다.

email, phone, gender 컬럼의 Default에 NULL이라고 적혀있다.
해당 컬럼에 값을 입력하지 않으면 기본값으로 NULL이 설정된다라는 의미이다.

name, registration_number, major 컬럼에는 NOT NULL 설정이 되어있다. row를 추가할 때 해당 컬럼에 값을 입력하지 않으면 오류가 발생한다.

하지만 NOT NULL 속성이 있는 컬럼이라도 기본값을 설정해두면 값을 입력하지 않더라도 오류가 발생하지 않는다.

✅ major 컬럼에 기본값 주기 (전공이 없는 상태의 코드번호 : 101)

ALTER TABLE student MODIFY major INT NOT NULL DEFAULT 101;

✅ 위 작업을 완료한 상태에서 row 추가

INSERT INTO student (name, registration_number)
	VALUES ('구지섭', 20112405);
SELECT * FROM course_rating.student;

📌 DATETIME, TIMESTAMP 타입 컬럼에 값 넣기

테이블에는 어떤 row가 추가되거나 갱신되었을 때 시각을 저장해야할 때가 있다.

  • 게시글 업로드 시각
  • 댓글이 달린 시각
  • 댓글을 수정한 시각

컬럼에 현재 시간 값을 넣어줄 때는 크게 두 가지 방법이 있다.

📝 NOW( ) 함수

  • id : Primary Key
  • title : 게시물의 제목
  • content : 게시물의 내용
  • upload_time : 게시물 최초 업로드 시각
  • recent_modified_time : 게시물 최근 갱신 시각

✅ 게시물 하나 추가

INSERT INTO post (title, content, upload_time, recent_modified_time)
	VALUES ("기분 좋은 날이네요~!",
    		"오늘은 경주 불국사를 가보았어요, 파란 하늘과..", NOW() NOW());
SELECT * FROM FOR_TEST.post;

upload_time과 recent_modified_time컬럼에 현재 시각이 입력되었다.

✅ 게시물 갱신

UPDATE post
	SET content = "오늘 제가 간 곳은 어디일까요? 그곳은 바로 경주 불국사..",
    	recent_modified_time = NOW()
	WHERE id = 1;
SELECT * FROM FOR_TEST.post;

📝 DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP 속성 설정

DATETIME 타입 또는 TIMESTAMP 타입의 컬럼에는 두 가지 속성을 줄 수 있다.

  • 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 CRRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  • upload_time 컬럼에는 DEFAULT_TIMESTAMP 속성
  • recent_modified_time 컬럼에는 DEFAULT_TIMESTAMP과 ON UPDATE CURRENT_TIMESTAMP 속성

✅ row 추가

INSERT INTO post (title, content)
	VALUES ("오랜만에 등산을 했습니다!", "여름이 되고 등산을 하고픈 마음에..");

✅ content 컬럼 갱신

UPDATE post
	SET content = "등산 장비를 사고 나니 이제 슬슬 등산이 하고 싶어서.."
    WHERE id = 2;
SELECT * FROM FOR_TEST.post;

만약 각 row마다 시간값에 관한 처리를 다르게 해줘야하는 경우라면 (예를 들어, 어떤 row는 현재 시간에서 +3시간, 어떤 row는 현재 시간에서 -5시간을 해줘야하는 경우) NOW 함수를 쓰는 게 좋다.

하지만 그럴 필요가 없는 상황이고, 굳이 날짜/시간 값을 별도로 신경쓰기가 싫다면 해당 컬럼에 DEFAULT CURRENT_TIMESTAMP 속성, ON UPDATE CURRENT_TIMESTAMP 속성을 설정해서 DBMS가 알아서 관리하도록 하는게 좋다.

📌 컬럼에 UNIQUE 속성

UQ는 UNIQUE로 컬럼에 같은 값을 가진 또 다른 row가 추가되는 걸 방지할 수 있다.

✅ registration_number 컬럼에 UNIQUE 속성

AlTER TABLE student MODIFY registration_number INT NOT NULL UNIQUE;

UNIQUE 속성의 효과를 확인해보기 위해 row를 추가해보자.

✅ registration_number 컬럼에 id 5번 학생과 동일한 학번 추가

INSERT INTO student (name, registration_number)
	VALUES ('최태웅', 20112405);

중복되는 값이 있기 때문에 추가될 수 없다는 오류가 발생한다.

📝 Primary Key와 Unique 속성의 차이

Primary Key는 테이블당 오직 하나만 존재할 수 있다. 테이블에서 특정 row 하나를 식별할 수 있도록 해주는 컬럼이다.
Unique는 컬럼의 값이 각 row마다 달라야할 경우 부여해주는 속성이다.

두 속성의 차이점에 대해 알아보자.

  • Primary Key는 테이블당 오직 하나만 존재할 수 있다.
  • Unique는 한 테이블에 여러 개의 Unique 속성들이 존재할 수 있다.
  • Primary Key는 NULL을 가질 수 없다.
  • Unique는 NULL을 가질 수 있다.

📌 테이블에 CONSTRAINT 1

CONSTRAINT는 제약사항이라고 하며 테이블이 이상한 row가 추가되는 걸 방지할 수 있다.

registration_number 학번 앞 부분에는 입학년도가 입력된다.
따라서 30,000,000 보다 큰 값이 클어가서는 안 된다. 이 제약사항을 걸어보자.

ALTER TABLE student
	ADD CONSTRAINT st_rule CHECK (registration_number < 30000000);
  • ADD CONSTRAINT st_rule 테이블에 st_rule 이름의 제약사항을 건다.
  • CHECK (registration_number < 30000000) : 제약사항의 내용을 의미한다.

✅ 제약사항이 잘 적용되었는지 확인

INSERT INTO student (name, registration_number)
	VALUES ('이대위', 30000000);

제약사항을 위반했다는 오류가 발생한다.

✅ 제약사항 삭제

ALTER TABLE student DROP CONSTRAINT st_rule;

📌 테이블에 CONSTRAINT 2

✅ 두 개 이상의 제약사항

ALTER TABLE student 
	ADD CONSTRAINT st_rule
    CHECK (email LIKE '%@%' AND gender IN ('m', 'f'));
  • email LIKE '%@%' : 이메일 값에 @가 포함되어 있어야 한다.
  • gender IN ('m', 'f') : 성별이 남자 또는 여자여야 한다.

✅ 새로운 row 추가

INSERT INTO student (name, registration_number, email, gender)
	VALUES ('김준성', 20130827, '^^ 장난쳐야지', 'm');
INSERT INTO student (name, registration_number, email, gender)
	VALUES ('김준성', 20130827, 'kjs123@naver.com', 'z');

오류가 발생한다.

✅ 새로운 row 추가

INSERT INTO student (name, registration_number, email, gender)
	VALUES ('김준성', 20130827, 'kjs123@naver.com', 'm');

📝 그 밖의 컬럼 관련 작업들

이때까지 컬럼을 추가/삭제하거나 기존 컬럼의 이름, 데이터 타입, 속성 등을 변경하는 방법을 배웠다.

아래와 같이 한 축구팀의 선수 정보를 관리하는 player_info 테이블이 있다고 가정하자.

  • role : 선수의 역할(공격수, 수비수 등)
  • name : 선수의 이름
  • id : PRIMARY KEY

📌 컬럼 가장 앞으로 당기기

Primary Key 역할을 하는 id 컬럼이 가장 뒤에 있다. 컬럼을 가장 앞으로 옮기자.

ALTER TABLE player_info
	MODIFY id INT NOT NULL AUTO_INCREAMENT FIRST;

FIRST : 해당 테이블의 가장 첫 번째 컬럼으로 하라.

📌 컬럼 간의 순서 바꾸기

선수 역할을 나타내는 role 컬럼이 선수 이름을 나타내는 name 컬럼보다 이후에 나오는 것이 더 자연스러울 것 같다. 컬럼의 순서를 변경해보자.

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

AFTER name : name 컬럼 바로 다음으로 위치를 변경하라.

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

  • 컬럼의 이름을 수정할 때는 RENAME COLUMN A TO B
  • 컬럼의 타입 및 속성을 수정할 때는 MODIFY

CHANGE는 두 가지 작업을 한번에 수행해주는 절이다.

  1. 컬럼 이름을 role에서 position으로 변경
  2. 데이터 타입을 CHAR(5)에서 VARCHAR(2)로, 그 속성도 NULL에서 NOT NULL로 변경 
ALTER TABLE player_info
	CHANGE role position VARCHAR(2) NOT NULL;

📌 여러 작업 동시에 수행하기

ALTER TABLE 문 뒤에는 컬럼에 관한 작업을 하는 절들을 여러 개 두는 것이 가능하다.

  1. id 컬럼의 이름을 registraion_number로 수정
  2. name 컬럼의 데이터 타입을 VARCHAR(20)로, 속성을 NOT NULL로 수정
  3. position 컬럼을 테이블에서 삭제
  4. 새로운 컬럼 2개(height(키), weight(몸무게)) 추가
ALTER TABLE player_info
	RENAME COLUMN id TO registration_number,
    MODIFY name VARCHAR(20) NOT NULL,
    DROP COLUMN position,
    ADD height DOUBLE NOT NULL,
    ADD weight DOUBLE NOT NULL;

위 SQL 문 중에서 a 작업과 b 작업을 하는 절을 CHANGE 절로 아래와 같이 쓸 수도 있다.

ALTER TABLE player_info
	CHANGE id registration_number INT NOT NULL AUTO_INCREAMENT,
    CHANGE name name VARCHAR(20) NOT NULL,
    DROP COLUMN position,
    ADD height DOUBLE NOT NULL,
    ADD weight DOUBLE NOT NULL;

✅ 과제 : 컬럼에 속성 주기

판매 중인 도서들의 정보를 저장할 book이라는 테이블을 만들려고 한다.

  • id : INT NOT NULL AUTO_INCREMENT
  • isbn : VARCHAR(50) NULL
  • page : INT NOT NULL
  • author : VARCHAR(20) NOT NULL
  • kind : VARCHAR(10) NOT NULL
  • location : VARCHAR(10) NOT NULL
  1. isbn 컬럼에 NOT NULL 속성과 UNIQUE 속성
  2. page 컬럼에 0보다 큰 값이 들어와야 한다는 제약사항 설정하기(제약 사항 이름은 page_rule)
  3. kind 컬럼의 이름을 genre_code로 바꾸면서, 동시에 그 데이터 타입을 INT로 설정
  4. location 컬럼에 기본값으로 ‘warehouse’ 라는 값 설정

💻 풀이

ALTER TABLE book
    MODIFY isbn VARCHAR(50) NOT NULL UNIQUE,
    ADD CONSTRAINT page_rule CHECK (page > 0),
    CHANGE kind genre_code INT NOT NULL,
    MODIFY location VARCHAR(10) NOT NULL DEFAULT 'warehouse';

DESC book;

👉 결과

profile
순동
이전 포스트

테이블 다루기 1

다음 포스트

[SQL] Revising the Select Query I

0개의 댓글

관련 채용 정보