수업일지 27일차 (23/10/27) - MySQL

---

 

수업내용 정리 (MySQL)

 

1. 제약 조건

 1-1) 제약 조건 ㄱ 

제약	설명
AUTO_INCREMENT	새 행 생성 시마다 자동으로 1씩 증가
PRIMARY KEY (PK, 기본키)	중복 입력 불가(UQ), NULL(빈 값) 불가 / 테이블에 무조건 하나
FOREIGN KEY (FK, 외래키)	참조되는 부모 테이블의 PK 또는 UQ 칼럼 값 중의 하나를 가진다.
UNIQUE (UQ)	중복 입력 불가 / null 값은 연산에서 제외
NOT NULL	NULL(빈 값) 입력 불가 / 칼럼단에서만 설정 가능
UNSIGNED	(숫자일 시) 양수만 가능
DEFAULT	값 입력이 없을 시 기본 값  / 칼럼단에서만 설정 가능

• 외래키(FK) 설정을 안 하면 자동으로 랜덤한 칼럼에 설정된다.
• DESC로 테이블 구조를 확인할 때 외래키는 MUL이라고 적혀있다.
• 제약 조건 우선 순위: PRIMARY KEY > FOREIGN KEY > UNIQUE

 

• FOREIGN KEY는 3번에서 다룬다.

 

 1-2) 제약 조건 삭제 / 추가

-- 제약조건 삭제
alter table member drop constraint PK_M_ID;	-- oracle 기준
alter table member drop primary key;	-- auto_increment 시 삭제 불가
alter table member drop constraint UQ_M_T;

• PK에 Auto_increment를 설정했을 시, 삭제가 불가능하다.

 

-- 제약조건 설정(추가)
alter table member add constraint PK_M_ID primary key (M_ID);	-- oracle 기준
alter table member add primary key(M_ID);
alter table member add constraint UQ_M_TEL unique(M_tel);

• 제약 조건을 줄 때 이름을 설정해야 나중에 삭제해야 할 때 용이하다.

 

-- not null/ null 수정
-- alter table member modify M_TEL null;	-- oracle 기준
alter table member modify M_TEL char(12) null;	-- 타입도 변경 가능
alter table member modify M_TEL char(11) not null;

-- 기본값 변환
alter table member modify M_age int default 1;
alter table member modify M_age tinyint unsigned default 1;

• 타입을 꼭 적어야 하고, 다른 타입으로 적을 시 타입이 변경된다. (Oracle은 안 적어도 된다)

 

 

2. 

 2-1) 테이블 생성 ㄱ 

drop table if exists member;
create table member(
	M_ID varchar(20),
	M_name varchar(20) not null,
	M_age tinyint unsigned default 0 not null,
	M_tel char(11) not null,
	M_email varchar(40),
	-- - 테이블단 제약 조건
	primary key PK_M_ID (M_ID),
	constraint unique UQ_M_T (M_tel), unique UQ_M_E (M_email)
);

• 칼럼단에서 제약조건을 설정할 수 있지만 테이블단에서 제약 조건을 주는 것이 보기에 좋다.
• not null, default는 칼럼단에서만 가능하다. (따로 생성/삭제 가능하지만 테이블 생성할 때는 테이블단에서 불가능)
• M_tel에 타입을 char로 했기 때문에 값을 '010111122' 로 저장하면 남은 공간은 공백으로 저장된다. select 때 MySQL은 알아서 trim으로 공백을 빼고 가져온다. oracle은 아님!
• MySQL은 PK명이 붙지 않고, ORACLE에서만 붙는다. PK명을 설정한 상태로 테이블을 생성해도 PK명이 저장되지 않는다.
• constraint <- 안 써도 정상적으로 작동되긴 한다.

 

 2-2) 데이터 삽입 ㄱ 

insert into member(M_ID, M_name, M_tel, M_age) values('A', '에이', '0101111', default);
select M_ID, length(M_ID), M_name, length(M_name), M_tel, length(M_tel) from member;
select M_ID, char_length(M_ID), M_name, char_length(M_name), M_tel, length(M_tel) from member;

• 글자 크기와 글자 수를 확인할 수 있다.

 

insert into member(M_id, m_name, m_tel, m_age) values('B', '비', '0102222', 10);
insert into member(M_id, m_name, m_tel, m_age)
	values('C', '씨', '01033332323', 20),
		('D', '디', '01044442323', 30),
		('E', '이', '01055552323', 40);

• 한번에 데이터를 삽입 할 수도 있다.

 

 

3. 제약 조건:  FOREIGN KEY

• 부모 테이블 -------< 관계(R) >------- 자식 테이블

 

drop table if exists board;
create table board(
	B_NO bigint auto_increment primary key,
	B_TITLE varchar(100) not null,
	B_CONTENTS varchar(10000) not null,
	B_DATE datetime not null default now(),
	B_MID varchar(20) not null,
-- ------ 외래키 제약 설정
	constraint FK_B_MID foreign key (B_MID) references member(M_ID)
);

• FK(외래키): 부모 테이블과 자식 테이블을 연결하는 데에 사용되는 키
• 부모 테이블의 기본키/고유키를 외래키로 지정할 수 있다.
• 칼럼단에서도 제약 조건을 줄 수 있지만 테이블단에서 주는 것이 보기에 좋다.
• 위 코드처럼 외래키만 설정하고 설정 옵션을 붙이지 않으면 부모 테이블에서 수정/삭제할 수 없게 된다.

 

-- 자식(board) 데이터 추가
insert into board values(null, '제목1', '내용1', default, 'xxx');
insert into board values(null, '제목1', '내용1', default, 'A'),
			(null, '제목2', '내용2', default, 'A'),
			(null, '제목1', '내용1', default, 'B'),
			(null, '제목2', '내용2', default, 'B'),
			(null, '제목1', '내용1', default, 'C'),
			(null, '제목2', '내용2', default, 'C');

• 맨 위의 코드는 참조되는 member(부모 테이블)에 없는 ID라서 에러가 뜨고 저장되지 않는다.
• 두번 코드처럼 여러개를 동시에 추가할 수 있다.

 

-- 'A' 회원 탈퇴
delete from member where M_ID='A';	-- 외래키 때문에 안 됨
delete from member where M_ID='D';	-- 자식 테이블에서 글이 없기 때문에 성공
delete from board where B_MID='A';	-- 자식 레코드(데이터) 삭제
delete from member where M_ID='A';	-- 자식 레코드(데이터)를 삭제했기 때문에 삭제 성공

-- member(부모) 테이블 삭제
drop table member;	-- 자식 테이블이 있기 때문에 안 됨

• MEMBER 테이블을 BOARD 테이블에 외래키로 넣어놨기 때문에 BOARD 테이블에서 데이터를 사용했는데 그냥 삭제하려고 하면 삭제되지 않는다.
• 자식 테이블에서 사용하지 않은 데이터는 바로 삭제할 수 있다.
• 자식 테이블에서 연관된 데이터 삭제 시 부모 테이블에서 데이터를 삭제할 수 있다.
• 테이블 또한 마찬가지로 자식 테이블에서 참조하고있기 때문에 삭제할 수 없다.

 

 

drop table board;
drop table member;

• 밑에서 MEMBER 테이블을 사용해야 하니 둘 다 삭제했다.

 

 3-1) 외래키 설정 옵션 ㄱ 

• 외래키 설정 옵션
  • on update: 수정 시 / on delete: 삭제 시
  • restrict: 참조하는 테이블(자식 테이블)에 참조되는 테이블(부모 테이블)의 데이터가 남아 있으면 참조되는 테이블(부모)에서 수정/삭제를 할 수 없다.
  • cascade: 참조되는 테이블(부모)에서 데이터 수정/삭제를 하면 참조하는 테이블(자식)에서도 수정/삭제가 된다.
  • no action: 참조되는 테이블(부모)에서 데이터 수정/삭제를 해도 참조하는 테이블(자식)의 데이터는 변경되지 않는다.
  • set default: 참조되는 테이블(부모)에서 데이터 수정/삭제를 하면 참조하는 테이블(자식)의 데이터는 default값으로 설정된다.
  • set null: 참조되는 테이블(부모)에서 데이터 수정/삭제를 하면 참조하는 테이블(자식)의 데이터는 null이 된다.

 

 

4. 문제? (외래키를 이용해서 테이블 만들기)

• 조건
  • 모든 칼럼은 NULL 값을 포함하지 않는다.
  • PURCHASE(구매) 테이블의 PRIMARY KEY는 조합키(슈퍼키)로 한다.

MEMBER (고객)
아이디	비밀번호	이름	전화번호	이메일
PRIMARY KEY				UNIQUE

PRODUCT (제품)
제품 번호	제품명	가격	재고	제품 설명
PRIMARY KEY AUTO_INCREMENT

PURCHASE (구매)
주문 번호	구매 고객	구매 제품 번호	구매 수량	구매 시간
AUTO_INCREMENT	FOREIGN KEY	FOREIGN KEY

 

 

create table MEMBER(
	M_ID varchar(20),
	M_PW varchar(20) not null,
	M_NAME varchar(20) not null,
	M_TEL varchar(13) not null,
	M_EMAIL varchar(30) not null,
	constraint primary key PK_M_ID (M_ID),
	constraint unique UQ_M_E (M_EMAIL)
);

• NULL 값을 허용하지 않는다고 했으니 PRIMARY KEY를 넣을 M_ID를 제외한 칼럼에 NOT NULL을 입력했다.
• 테이블 단에서 PK를 설정할 때 MySQL은 이름을 정해줄 수 없지만 Oracle에서는 정할 수 있으므로 연습하는 겸 이름도 적었다.

 

create table PRODUCT(
	P_NO int auto_increment,
	P_NAME varchar(30) not null,
	P_PRICE int unsigned not null default 10000,
	P_Inven int unsigned not null default 0,
	P_Intro text not null,
	constraint primary key PK_P_NO (P_NO) 
);

• DEFAULT 값으로 P_PRICE(가격)에는 10,000, P_INVEN(재고)에는 0을 입력했다.

 

create table PURCHASE(
	PR_NO int auto_increment,
	PR_MID varchar(20),
	PR_PNO int,
	PR_Quti tinyint not null,
	PR_DATE datetime default now(),
	constraint primary key PK_PR (PR_NO, PR_MID, PR_PNO, PR_DATE),	-- 조합키(슈퍼키)
	constraint foreign key FK_PR_MID (PR_MID) references MEMBER (M_ID)
							on delete no action on update cascade,
	constraint foreign key FK_PR_PNO (PR_PNO) references PRODUCT (P_NO)
							on delete no action on update cascade
);

• FK로 데이터를 가져올 칼럼에는 해당 데이터 칼럼의 타입을 그대로 적는다. (변경할 수는 있지만 그대로인 경우가 헷갈리지 않고 오류날 일 없이 편하다.)
• PRIMARY KEY에 여러가지 칼럼을 설정하면 조합(슈퍼)키가 된다.
• FOREIGN KEY를 각 각 PR_MID, PR_PNO에 설정해준다. PR_MID는 MEMBER 테이블에서 M_ID를, PR_PNO는 PRODUCT 테이블에서 P_NO를 참조한다.
• FK 설정 옵션 중, ON DELETE에는 NO ACTION으로 부모 테이블에서 데이터를 삭제한다고 해도 해당 데이터는 삭제되지 않도록 했다.
• ON UPDATE는 CASCADE로 부모 테이블에서 데이터를 변경하면 해당 데이터도 변경되도록 했다.

 

insert into member(M_ID, M_PW, M_NAME, M_TEL, M_EMAIL)
			values ('A', 'AAA', '에이', '010-1111', 'AA@AAAAA.AAA');
            
insert into member values('B', 'BBB', '비비', '010-2222', 'BB@BBBB.BBB'),
			('C', 'CCC', '씨씨', '010-3333', 'CC@CCCCC.CCC'),
			('D', 'DDD', '디디', '010-4444', 'DD@DDDDD.DDD');

• 위 코드처럼 칼럼을 적고 추가하는 것이 좋다. (많은 칼럼이 있을 경우 순서를 헷갈릴 수 있기 때문에)

 

insert into product
	values (null, 'TV', default, 20, 'TVTVTVTVTVTVTVTVTVTVTVTVTTVTVTVVTTVTVTVTVTVTVTVTV'),
		(null, '냉장고', 20000, 15, 'Coolllllllllllllllllllllllllllllllllllllllllllllllll'),
		(null, '오븐', 30000, 10, 'Hottttttttttttttttttttttttttttttttttttttttttttttttttt'),
		(null, 'PC', 40000, 5, 'PCPCPPCPPCPCPCPCPCPCPPCPCPCPCPPCPCPCPCPPCPCPCPPCPCPCPCPPC');

insert into purchase values (null, 'A', 1, 10, default),
			(null, 'B', 2, 2, default),
			(null, 'B', 3, 4, default),
			(null, 'C', 4, 3, default),
			(null, 'D', 4, 2, default);

 

select PR_NO '구매 번호', PR_MID '구매자', P_NO '구매한 상품 번호', P_NAME '구매한 상품', PR_DATE '구매한 날짜'
from purchase PR join product P on P.P_NO=PR.PR_PNO where P.P_Inven-PR.PR_Quti>=0;

 

update member set M_ID='AA' where M_ID='A';

• 부모 테이블에서 데이터를 변경해도 자식 테이블에서 문제없이 변경된다.

 

---

 

 

 - 쌤이 정리해주신 테이블 관련 코드

-- ALTER TABLE - 테이블 변경, COLUMN 생략 가능
-- 테이블명 변경
ALTER TABLE people RENAME TO  friends,
-- 컬럼 자료형 변경
CHANGE COLUMN person_id person_id TINYINT,
-- 컬럼명 변경
CHANGE COLUMN person_name person_nickname VARCHAR(10), 
-- 컬럼 삭제
DROP COLUMN birthday,
-- 컬럼 추가
ADD COLUMN is_married TINYINT AFTER age,
-- 기본키, AUTO_INCREMENT 설정
Change COLUMN m_id m_id int auto_increment PRIMARY KEY,
-- AUTO_INCREAMENT 제거
change COLUMN m_id m_id int,
-- PK 제거
drop primary key;

 

---

 

전체 피드백

• 역시 내가 직접 무언가 해봐야 식곤증이 덜 오는 것 같다..

 

---