< REGION 추가 예제 >
region의provinces테이블을 수정하여 마지막 자리에suffix_index열 추가. (DROP 금지)region의provinces테이블의 모든 레코드에 대한suffix_index값을 알맞게 수정. ( 가령,대구라는 이름을 가진 레코드의suffix_index는광역시라는suffix를 가지는 레코드의index값이 된다.region의provinces테이블을 수정하여suffix_index열에는region.suffix_index테이블의index열에 존재하는 값만 들어가게 할 것.
- 테이블 수정시 제약조건 추가는 아래와 같이 한다.
ALTER TABLE `스키마`.`테이블` ADD CONSTRANINT `제약 조건 이름` <제약 조건>.... 가령, `simple` 열에 UNIQUE 키를 추가하려면 아래와 같이 작성 ALTER TABLE `s`.`t` ADD CONSTRAINT`uk-1` UNIQUE (`simple`);
CREATE TABLE `region`.`suffixes`
(
`index` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`suffix` VARCHAR(5) NOT NULL,
CONSTRAINT PRIMARY KEY (`index`),
CONSTRAINT UNIQUE (`suffix`)
);
- suffixes 테이블 추가
INSERT INTO `region`.`suffixes` (`suffix`)
VALUES ('특별시'),
('광역시'),
('특별자치시'),
('도'),
('특별자치도');- suffixes 테이블의 suffix 내용 추가 (5개)
ALTER TABLE `region`.`provinces`
ADD COLUMN `suffix_index` TINYINT UNSIGNED NOT NULL;- provinces 테이블에 suffix_index 열 추가
UPDATE `region`.`provinces`
SET `suffix_index` = 1
WHERE `provinces`.`name` IN ('서울');
UPDATE `region`.`provinces`
SET `suffix_index` = 2
WHERE `provinces`.`name` IN ('인천', '대전', '부산', '울산', '대구', '광주');
UPDATE `region`.`provinces`
SET `suffix_index` = 3
WHERE `provinces`.`name` IN ('세종');
UPDATE `region`.`provinces`
SET `suffix_index` = 4
WHERE `provinces`.`name` IN ('경기', '강원', '충청남', '충청북', '경상북', '경상남', '전라남', '전라북');
UPDATE `region`.`provinces`
SET `provinces`.`suffix_index` = 5
WHERE `name` IN ('제주');- 첫번째꺼를 예시로 들자면
region.provinces테이블에 SET뒤에는 컬럼의 이름을 적고 = 을 통해 추가 할 값을 적어주면 된다.
그리고 WHERE절에서 조건을 달아주는데 IN을 사용할 것이다.
IN 이란..
업데이트 범위를 정할 때 다른 테이블을 참조해야 할 경우 IN을 사용하여 데이터를 포함시키거나 NOT IN을 사용하여 제외시킬 수 있다.
IN의 서브 쿼리에서 키값이 여러 개인 경우 컬럼을 묶어서 조건을 맵핑할 수 있다.
즉, 서울이라는 이름을 가진 열에 suffix_index 1을 추가하겠다는 의미가 된다!
정리를 해보자면!
↓ provinces 테이블
UPDATE `region`.`provinces`
↑ region스키마
↓ 어떤 열에 추가를 할 것인지
SET `suffix_index` = 1
↑ 추가할 값
↓ 조건, provinces테이블의 name에
WHERE `provinces`.`name` IN ('서울');
↑ '서울'이라는 이름을 가진 열에게 추가. 
- null 값이던
suffix_index가 UPDATE문을 통해 정보 추가가 된것을 확인 해볼 수 있다.
ALTER TABLE `region`.`provinces`
ADD CONSTRAINT `fk_suffix_index` FOREIGN KEY (`suffix_index`) REFERENCES `region`.`suffixes` (`index`);SELECT `province`.`code` AS `국번`,
CONCAT(`province`.`name`, `suffix`.`suffix`) AS `이름`
FROM `region`.`provinces` AS `province`
LEFT JOIN `region`.`suffixes` AS `suffix` ON `province`.`suffix_index` = `suffix`.`index`
ORDER BY `province`.`code`;
- SELECT 해서 나온 결과
< SCHOOL예제 >
CREATE SCHEMA `school`;- school스키마 생성
CREATE TABLE `school`.`grades`
(
`grade` TINYINT(1) UNSIGNED NOT NULL,
CONSTRAINT PRIMARY KEY (`grade`)
);
- grades 테이블 생성
CREATE TABLE `school`.`classes`
(
`grade` TINYINT(1) UNSIGNED NOT NULL,
`class` TINYINT(2) UNSIGNED NOT NULL,
CONSTRAINT PRIMARY KEY (`grade`, `class`),
CONSTRAINT FOREIGN KEY (`grade`) REFERENCES `school`.`grades` (`grade`)
);- classes 테이블 생성
CREATE TABLE `school`.`genders`
(
`code` VARCHAR(1) NOT NULL,
`text` VARCHAR(5) NOT NULL,
CONSTRAINT PRIMARY KEY (`code`)
);- genders 테이블 생성
CREATE TABLE `school`.`students`
(
`grade` TINYINT(1) UNSIGNED NOT NULL,
`class` TINYINT(2) UNSIGNED NOT NULL,
`number` TINYINT(2) UNSIGNED NOT NULL,
`name` VARCHAR(10) NOT NULL,
`gender_code` VARCHAR(1) NOT NULL,
`contact` VARCHAR(11) NOT NULL COMMENT '본인 연락처', -- COMMENT 열에 대한 설명
`contact_parent` VARCHAR(11) NOT NULL COMMENT '부모 연락처',
`dropped_flag` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '(우리 학교로부터) 자퇴 여부',
`expelled_flag` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '(우리 학교로부터) 제적 여부',
`transferred_flag` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '(우리 학교로의) 전학 여부',
`admitted_flag` DATE NOT NULL COMMENT '입학 일자',
`transferred_at` DATE DEFAULT NULL COMMENT '전학 일자',
CONSTRAINT PRIMARY KEY (`grade`, `class`, `number`),
CONSTRAINT FOREIGN KEY (`grade`, `class`) REFERENCES `school`.`classes` (`grade`, `class`),
CONSTRAINT FOREIGN KEY (`gender_code`) REFERENCES `school`.`genders` (`code`)
);
- students 테이블 생성
- 여기서
FOREIGN KEY를( grade, class )이렇게 걸어주는 이유는
반과 학년, 이 두 개의 짝궁으로는 겹쳐도 되지만 ' 반, 학년, 번호 ' 는
겹치면 안되기 때문이다.( 반과 학년의 키 안에서 numbers을 배정하기 위해서이다. )
-> 현재 Diagrams
- 학생 열 추가 완료.
Software Developer : -)