테이블

Preface

 

이번 장에선 SQL문을 이용해 테이블을 조작하는 방법을 공부했다.

 

앞선 장에서부터 별 생각 없이 사용하던 문장들을 막상 하나 하나 나열하니 내용이 정말 많다고 느껴졌다.

 

또, 여러 제약 조건들을 고려하며 테이블을 정의하고 수정하는 것은 정말 복잡한 것 같다는 생각이 들었다.

 

그래도 책을 공부하며 한 번, 블로그를 업로드하며 한 번 SQL문을 작성하다보니 나름 익숙해져서 내가 원하는 간단한 작업은 큰 어려움 없이 진행할 수 있게 되었다.

 

 

 

아, 그리고 오늘 달력을 보던 도중 알게 되었는데 내가 개발 공부를 시작한지 벌써 1년이 넘었다.

 

그동안 나름 꾸준히 한다고 한 것 같긴 한데 음... 아직 한참 부족한 것 같다.

 

부족한 부분에 스트레스를 받기 보단 간단한 코드라도 짤 수 있게 된 것, 무지했던 IT 세상의 지식을 조금이라도 알게 된 것에 의의를 두자.

 

내년이면 벌써 스물 네 살이고 8월이면 대학교도 졸업이다.

 

스물 다섯이 가기 전엔 무조건 개발자로서의 커리어를 시작하고 싶은데, 아직 갈 길이 멀다.

 

나태해지지 말고 남들보다 더 노력해보자.

 

기업들 딱 대 ㅋㅋ

---

 

1. SQL을 이용한 테이블 생성

 

 

- 간단한 쇼핑몰 DB 생성

drop database if exists tabledb;
create database tabledb;

use tabledb;
drop table if exists buytbl, usertbl;

create table usertbl				-- 회원 테이블
( userID	char(8) not null primary key,	-- 사용자 아이디
  username	varchar(10) not null,		-- 이름
  birthYear int not null,			-- 출생년도
  addr		char(2) not null,		-- 지역(경기, 서울, 경남 등으로 글자만 입력)
  mobile1	char(3) null,			-- 휴대폰의 국번
  mobile2	char(8) null,			-- 휴대폰의 나머지 전화번호
  height	smallint null,			-- 키
  mDate		date null			-- 회원 가입일
);

create table buytbl			-- 구매 테이블
( num		int  auto_increment not null primary key,	-- 순번(PK)
  userid	char(8) not null,	-- 아이디(FK)
  prodName	char(6) not null,	-- 물품명
  groupName	char(4) null,		-- 분류
  price		int not null,		-- 단가
  amount	smallint not null,	-- 수량
  foreign key(userid) references usertbl(userID)
);


insert into usertbl values('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
insert into usertbl values('KBS', '김범수', 1979, '경남', '011', '22222222', 173, '2012-4-4');
insert into usertbl values('KKH', '김경호', 1971, '전남', '019', '33333333', 177, '2007-7-7');
insert into usertbl values('JYP', '조용필' ,1950, '경기', '011', '44444444', 166, '2009-4-4');
insert into usertbl values('SSK', '성시경', 1979, '서울', null, null, 186, '2013-12-12');
insert into usertbl values('LJB', '임재범', 1963, '서울', '016', '66666666', 182, '2009-9-9');
insert into usertbl values('YJS', '윤종신', 1969, '경남', null, null, 170, '2005-5-5');
insert into usertbl values('EJW', '은지원', 1972, '경북', '011', '88888888', 174, '2014-3-3');
insert into usertbl values('JKW', '조관우', 1965, '경기', '018', '99999999', 172, '2010-10-10');
insert into usertbl values('BBK', '바비킴', 1973, '서울', '010', '00000000', 176, '2013-5-5');
insert into buytbl values(null, 'KBS', '운동화', null, 30, 2);
insert into buytbl values(null, 'KBS', '노트북', '전자', 1000, 1);
insert into buytbl values(null, 'JYP', '모니터', '전자' ,200, 1);
insert into buytbl values(null, 'BBK', '모니터', '전자', 200, 5);
insert into buytbl values(null, 'KBS', '청바지', '의류', 50, 3);
insert into buytbl values(null, 'BBK', '메모리', '전자', 80, 10);
insert into buytbl values(null, 'SSK', '책', '서적', 15, 5);
insert into buytbl values(null, 'EJW', '책', '서적', 15, 2);
insert into buytbl values(null, 'EJW', '청바지', '의류', 50, 1);
insert into buytbl values(null, 'BBK', '운동화', null, 30, 2);
insert into buytbl values(null, 'EJW', '책', '서적', 15, 1);
insert into buytbl values(null, 'BBK', '운동화', null, 30, 2);

 

---

 

2. 제약 조건

 

 

- 제약 조건: 데이터의 무결성을 지키기 위한 제한된 조건

→ 특정 조건을 만족했을 때만 데이터가 입력되도록 제약하는 것

---

1. 기본 키(PRIMARY KEY) 제약 조건

 

- 기본 키: 테이블에 존재하는 많은 행의 데이터를 구분할 수 있는 식별자

 

 

- 기본 키에 입력되는 값은 중복될 수 없으며, null 값이 입력될 수 없다.

 

 

- 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다.

 

 

- 두 개의 열을 합쳐서 기본 키로 설정할 수 있다.

 

 

- 기본 키를 지정하는 방법

create table usertbl
( userID	char(8) not null primary key,
  username	varchar(10) not null,
  birthYear int not null
);

 

 

- 테이블의 정보를 보기 위한 방법

describe usertbl;
desc usertbl;
show index from 테이블이름;

 

 

- 기본 키를 지정하면서 키의 이름을 지어주는 방법

-- 짧은 테이블 생성
create table usertbl
( userID	char(8) not null,
  username	varchar(10) not null,
  birthYear int not null,
  constraint primary key PK_userTBL_userID (userID)
  -- constraint는 생략 가능
  -- 이름을 지정할 필요가 없다면 'primary key (userID)'만 써도 됨
);

 

-- alter table 구문을 사용하는 방법
create table usertbl
( userID	char(8) not null,
  username	varchar(10) not null,
  birthYear int not null
);
alter table usertbl
	add constraint PK_userTBL_userID
    	primary key (userID);

→ MySQL은 primary key로 지정하면 항상 키 이름을 PRIMARY로 보여주므로, 기본 키의 이름을 직접 지정하는 것이 의미 없다. 하지만, foreign key는 하나의 테이블에 여러 개가 생성될 수 있으므로 이름을 지정해서 관리하는 것이 좋다.

 

 

- 테이블에 지정된 키를 보는 방법

show keys from 테이블이름;

 

 

- 두 개 이상의 열을 합쳐 기본 키로 구성하는 방법

drop table if exists prodTbl;
create table prodTbl
( prodCode	char(3) not null,
  prodID	char(4) not null,
  prodDate	datetime not null,
  prodCur	char(10) null
/* constraint PK_prodTbl_proCode_proID
		primary key (prodCode, prodID) */
);
alter table prodTbl
	add constraint PK_prodTbl_proCode_proID
		primary key (prodCode, prodID) ;

 

---

 

2. 외래 키(FOREIGN KEY) 제약 조건: 두 테이블 사이의 관계를 선언함으로써 데이터의 무결성을 보장

 

- 외래 키 테이블에 데이터를 입력할 때는 기준 테이블에 이미 데이터가 존재해야 한다.

 

 

- 외래 키 테이블이 참조하는 기준 테이블의 열은 반드시 primary key이거나 unique 제약 조건이 설정되어 있어야 한다.

 

 

- 외래 키를 생성하는 방법

create table buytbl
( num		int  auto_increment not null primary key,
  userid	char(8) not null,
  prodName	char(6) not null,
  foreign key(userid) references usertbl(userID)
);

 

 

- 외래 키를 지정하면서 키의 이름을 지어주는 방법

create table buytbl
( num		int  auto_increment not null primary key,
  userid	char(8) not null,
  prodName	char(6) not null,
  constraint FK_userTBL_buyTBL foreign key(userid) references usertbl(userID)
);

 

 

-- alter table 구문을 사용하는 방법
create table buytbl
( num		int  auto_increment not null primary key,
  userid	char(8) not null,
  prodName	char(6) not null
);
alter table buytbl
	add constraint FK_userTBL_buyTBL
    foreign key (userID)
    references userTBL (userID);

→ 기준 테이블의 열 이름과 외래 키 테이블의 열 이름이 달라도 관계는 없다.

 

---

 

3. UNIQUE 제약 조건: 중복되지 않는 유일한 값을 입력해야 하는 조건

→ PRIMARY KEY와 거의 비슷하지만, null 값을 허용한다.

 

- 제약 조건의 일반적인 이름 지정 방식

1) primary key: PK

2) foreign key: FK

3) unique: AK(Alternate Key)

 

---

 

4. CHECK 제약 조건: 입력되는 데이터를 점검하는 기능

 

- check 제약 조건 설정 방법

-- 출생년도가 1900년 이후 그리고 2023년 이전, 이름은 반드시 넣어야 함
create table usertbl
( userID	char(8) primary key,
  username	varchar(10),
  birthYear int check (birthYear >= 1900 and birthYear <= 2023),
  mobile1	char(3) null,
  constraint CK_name check (name is not null)
);

 

-- alter table 구문을 사용하는 방법
alter table usertbl
	add constraint CK_mobile1
    check (mobile1 in ('010', '011', '016', '017', '018', '019')) ;

→ check 제약 조건을 만들되 작동하지 않도록 하는 방법: 제약 조건의 제일 뒤에 NOT ENFORCED구문을 추가

 

---

 

5. DEFAULT 정의: 값을 입력하지 않았을 때, 자동으로 입력되는 기본 값을 정의하는 방법

 

- default 값 설정 방법

create table usertbl
( userID	char(8) not null primary key,
  username	varchar(10) not null,
  birthYear int not null default -1,
  addr		char(2) not null default '서울',
  mobile1	char(3) null,
  mobile2	char(8) null,
  height	smallint null default 170,
  mDate		date null
);

 

-- alter table 구문을 사용하는 방법
create table usertbl
( userID	char(8) not null primary key,
  username	varchar(10) not null,
  birthYear int not null,
  addr		char(2) not null,
  mobile1	char(3) null,
  mobile2	char(8) null,
  height	smallint null,
  mDate		date null
);
alter table usertbl
	alter column birthYear set default -1;
alter table usertbl
 	alter column addr set default '서울';
alter table usertbl
 	alter column height set default 170;

 

 

- 디폴트 값이 설정된 열에 데이터를 입력하는 방법

1) default 문은 DEFAULT로 설정된 값을 자동 입력한다.

2) 열 이름이 명시되지 않으면 DEFAULT로 설정된 값을 자동 입력한다.

3) 값이 직접 명기되면 DEFAULT로 설정된 값은 무시된다.

 

---

 

6. Null 값 허용: null 값을 허용하려면 'null'을, 허용하지 않으려면 'not null'을 사용하면 된다.

→ 기본 키가 설정된 열은 자동으로 not null이 적용된다.

---

 

---

 

3. 테이블 압축

 

 

- 테이블 압축: 대용량 테이블의 공간을 절약

→ 테이블을 생성할 때만 지정하면 그 이후로는 일반 테이블과 사용법이 동일하다.

 

 

- 테이블 압축 방법

create database if not exists compressDB;
use compressDB;
create table normalTBL( emp_no int, first_name varchar(14));
create table compressTBL( emp_no int, first_name varchar(14))
	row_format=compressed;

insert into normalTBL
	select emp_no, first_name from employees.employees;
insert into compressTBL	-- 데이터가 압축되며 입력되므로 조금 더 오래 걸림
	select emp_no, first_name from employees.employees;
    
show table status from compressDB;	-- 테이블의 상태 확인

drop database if exists compressDB;

 

---

 

4. 임시 테이블

 

 

- 임시 테이블: 임시로 잠깐 사용되는 테이블

 

 

- 임시 테이블 생성 방법

create temporary table [if not exists] 테이블이름
( 열 정의 )
-- table 위치에 temporary table이라고 써주면 됨

 

 

- 임시 테이블의 특징

1) 생성 구문만 약간 다를 뿐, 나머지 사용법은 일반 테이블과 동일하다.

2) 세션 내에서만 존재하며, 세션이 닫히면 자동으로 삭제된다.

3) 생성한 클라이언트에서만 접근이 가능하며, 다른 클라이언트는 접근할 수 없다.

4) DB 내의 다른 테이블과 이름을 동일하게 만들 수 있다.

→ 기존의 테이블은 임시 테이블이 있는 동안에 접근이 불가능하며, 무조건 임시 테이블로 접근한다.

 

 

- 임시 테이블이 삭제되는 시점

1) 사용자가 drop table로 직접 삭제

2) Workbench를 종료하거나 MySQL 클라이언트를 종료하면 삭제

3) MySQL 서비스가 재시작 되면 삭제

 

---

 

5. 테이블 삭제

 

 

- 테이블 삭제 방법

drop table 테이블이름;

 

 

- 외래 키 제약 조건의 기준 테이블은 삭제할 수 없다.

→ 먼저 외래 키가 생성된 외래 키 테이블을 삭제해야 한다.

 

 

- 여러 개의 테이블을 동시에 삭제하려면 콤마(,)를 사용해 나열하면 된다.

 

---

 

6. 테이블 수정

 

 

- 열의 추가

1) 열을 추가하면 기본적으로 가장 뒤에 추가된다.

2) 순서를 지정하려면 제일 뒤에 'FIRST' 또는 'AFTER 열 이름'을 지정하면 된다.

use tabledb;
alter table usertbl
	add homepage varchar(30)	-- 열 추가
		default 'http:/www.hanbit.co.kr'	-- 디폴트 값
        	null;	-- null 허용

 

 

- 열의 삭제

alter table usertbl
	drop column mobile1;

 

 

- 열의 이름 및 데이터 형식 변경

alter table usertbl
	change column username uname varchar(20) null;

 

 

- 열의 제약 조건 추가 및 삭제

alter table usertbl
	drop primary key;

alter table buytbl
	drop foreign key 외래키이름;
    -- 외래 키 이름은 Navigator에서 테이블 아래 Foreign key를 확장하여 확인

 

 

- 외래 키 제약 조건을 비활성화 시킨 후, 데이터를 모두 입력한 후에 다시 활성화 시키는 방법

set foreign_key_checks = 0;
update usertbl set userID = 'VVK' where userID = 'BBK';
set foreign_key_checks = 1;

1) foreign_key_check: 시스템 변수로 외래 키 체크 여부를 설정하는데 기본 값은 1(on)이며, 0은 off이다.

2) 함부로 외래 키 제약 조건을 끊고 데이터를 수정하면 제약 조건에 어긋나는 데이터가 입력되어 원하는 값을 얻지 못할 수도 있다.

 

 

- alter table은 테이블의 속성을, update는 테이블의 행 값을 수정한다.

 

 

- 기존 테이블의 PRIMARY KEY에 변경이 생길 때 이와 관련되는 참조 테이블의 FOREIGN KEY도 자동으로 변경시키는 방법

alter table buytbl
	drop foreign key FK_usertbl_buytbl;
alter table buytbl
	add constraint FK_usertbl_buytbl
	foreign key (userID)
    references usertbl (userID)
    on update cascade	-- 회원 테이블의 userID가 바뀌면 구매 테이블의 userID도 자동 변경
    on delete cascade;	-- 회원 테이블의 userID가 삭제되면 구매 테이블의 userID 관련 항목도 자동 삭제

 

 

- 참조되는 테이블에서 튜플을 삭제할 경우

1) ON DELETE NO ACTION: 튜플을 삭제하지 못하게 한다. (디폴트)

2) ON DELETE CASCADE: 관련 튜플을 함께 삭제한다.

3) ON DELETE SET NULL: 관련 튜플의 외래키 값을 NULL로 변경한다.

4) ON DELETE SET DEFAULT: 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.

 

 

- 참조되는 테이블에서 튜플을 변경할 경우

1) ON UPDATE NO ACTION: 튜플을 변경하지 못하도록 한다. (디폴트)

2) ON UPDATE CASCADE: 관련 튜플에서 외래키 값을 함께 변경한다.

3) ON UPDATE SET NULL: 관련 튜플의 외래키 값을 NULL로 변경한다.

4) ON UPDATE SET DEFAULT: 관련 튜플의 외래키 값을 미리 지정한 기본 값으로 변경한다.

 

 

- MySQL은 check 제약 조건이 설정된 열은 제약 조건을 무시하고 제거한다.

alter table usertbl
	drop column birthYear;