[13주 4일차] 테이블과 뷰

Chapter05. 테이블과 뷰

 

 

 

 

 

 

05-1. 테이블 만들기

 

 

 

GUI 환경에서 테이블 만들기

 

데이터베이스 생성하기

 

데이터베이스를 생성하는 명령어는 'create database naver_db;이다.

GUI 환경에서 생성은 오른쪽 마우스를 클릭하면 create schema 라는 버튼이 있는데 그것을 클릭하면 생성할 수 있다.

 

 

 

테이블 생성하기

 

member테이블을 GUI 환경에서 생성하려면 naver_db 데이터베이스를 확장 후 tables를 선택하고 오른쪽 마우스를 클릭하면 아래와 같이 create table 버튼을 볼 수 있다.

 

 

 

create tables 버튼을 클릭하면 다음과 같은 화면을 볼 수 있는데 이 화면에서 column name과 datatype 등 설정을 한 후 apply 버튼을 클릭하면 테이블을 생성할 수 있다.

 

 

 

 

 

 

 

 

---

 

 

 

 

05-2. 제약조건으로 테이블을 견고하게

테이블을 만들 떄는 테이블의 구조에 제약조건을 설정해야한다.

HTML이나 java에서 제약조건을 설정해도되지만 DB에서만 설정이 가능한 제약조건도 존재하기 때문에 제약조건을 설정해야한다.

 

 

제약조건에는 대표적으로,

• 기본키(Primary key)
• 외래 키(Foreign key)
• 고유키(Unique)
• 체크(Check)
• 기본값(Default)
• null 값 허용 

이 있다.

 

 

 

 

기본 키 제약조건

 

기본키 제약조건은 테이블의 많은 데이터를 구분할 수 있는 식별자를 이야기한다.

기본 키는 중복이 될 수 없으며 null 값이 입력될 수 없다.

또한, 기본 키로 생성한 것은 자동으로 클러스트형 인덱스가 생성된다 그러므로 PK로 데이터를 검색할 경우 빠르게 검색이 가능하다.

 

 

 

 

 

create table에서 설정하는 기본 키 제약조건

 

create table 문에 primary key 예약어를 넣는 방법은 두가지가 존재한다.

 

첫번째는 아래와 같이 컬럼명 뒤에 붙여주는 방법이 있다.

create table member
(
	mem_id char(8) not null primary key,
    mem_name varchar(10) not null,
    height tinyint unsigned null
);

 

 

두번째는 아래와 같이 제일 마지막 행에 primary key(mem_id)를 추가하는 방법이 있다.

create table member
(
	mem_id char(8) not null,
    mem_name varchar(10) not null,
    height tinyint unsigned null,
    primary key(mem_id)
);

 

 

 

 

 

alter table에서 설정하는 기본 키 제약조건

create table뿐 아니라 alter table에서도 기본 키 제약조건을 변경할 수 있다.

이미 만들어진 테이블에 수정하는 방법이다.

 

create table member
(
	mem_id char(8) not null,
    mem_name varchar(10) not null,
    height tinyint unsigned null,
);


alter table member add constraint primary key(mem_id);

 

 

 

 

 

 

* 테이블 삭제 순서

회원 테이블과 구매 테이블은 기본 키 - 외래 키로 연결되어있을 경우 삭제 순서가 중요하다.

PK를 삭제하고 싶은데 PK를 삭제하려면 FK가  PK를 삭제하기 때문에 그 테이블을 삭제할 수 없다.

그렇기 때문에 FK가 존재하는 테이블을 먼저 삭제한 후 PK가 존재하는 테이블을 삭제해야한다.

 

 

 

 

 

---

 

 

 

 

 

 

외래 키 제약조건

외래 키(Foreign Key) 제약조건은 두개의 테이블 사이의 관계를 연결해주고, 결과 데이터의 무결성을 보장해준다.

외래 키는 다른 테이블의 기본 키와 연결이 된다.

이때 기본 키가 있는 테이블을 기준 테이블, 외래 키가 있는 테이블을 참조 테이블이라고 한다.

 

참조 테이블이 참조하는 기준 테이블의 열은 기본 키라고 했는데 이때 기본 키도 가능하지만 고유 키(Unique)로 설정도 가능하다.

 

 

 

 

 

 

 

 

 

create table에서 설정하는 외래 키 제약조건

 

create table에서 외래 키를 설정하려면 create table 끝에서 foreign key 키워드를 설정한다.

 

CREATE TABLE buy 
(  num         INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   mem_id      CHAR(8) NOT NULL, 
   amount        SMALLINT UNSIGNED  NOT NULL ,
   FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

 

외래 키 설정 형식은 foreign key(컬럼 이름) references 기준(PK)테이블 (컬럼 이름); 이다.

이때 컬럼 이름은 달라도 되지만 데이터타입은 같아야한다.

또한, 기준 테이블의 컬럼이 primary key 혹은 unique key여야만 한다.

 

 

 

 

alter table에서 설정하는 외래 키 제약조건

 

이미 테이블을 생성했다면 alter table에서도 외래 키를 설정할 수 있다.

alter table buy add constraint foreign key(mem_id) references member(mem_id);

 

 

 

 

 

 

 

기준 테이블의 열이 변경될 경우

 

 

 

 

BLK의 아이디를 PINK로 변경하고자 했다.

 

drop table if exists buy;
create table buy
(	num int auto_increment not null primary key,
	mem_id char(8) not null,
	prod_name char(6) not null
);


update member set mem_id = 'PINK' where mem_id = 'BLK';

 

 

하지만 아래와 같이 오류가 발생했다.

오류의 이유는 외래키때문이었는데 

기본 키 - 외래 키로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않는다.

열의 이름이 변경되면 참조 테이블과 이름이 일치하지 않는 문제가 발생한다.

 

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`naver_db`.`buy`, CONSTRAINT `buy_ibfk_1` FOREIGN KEY (`mem_id`) REFERENCES `member` (`mem_id`))

 

 

조회를 해보면 아래와 같이 변경되지 않은 것을 볼 수 있다.

 

 

 

 

 

 

- CASCADE

 

이러한 문제점을 위해 있는 것이 cascade이다.

cascade는 기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경되는 것이다.

위와 같이 회원 테이블의 BLK가  PINK로 변경되면 자동으로 구매 테이블의 BLK로 PINK로 변경된다.

 

cascade 명령어를 사용하면 참조하는 데이터를 모두 삭제할 수 있다.

이것이 무슨 뜻이냐면 아래의 그림을 통해 설명할 수 있다.
구매 테이블은 회원 테이블을 참조하고 있다.

이때 FK 테이블에 on udpate cascade; on delete cascade;를 작성하면 변경 혹은 삭제 시 참조하는 데이터까지 모두 변경 혹은 삭제를 한다.

 

 

 

 

아래와 같이 alter table에서 cascade 설정을 추가해줄 수 있다.

alter table에서 on update cascade문 on delete cascade문을 작성해 설정을 추가했다.

 

drop table if exists buy;
create table buy
(	num int auto_increment not null primary key,
	mem_id char(8) not null,
	prod_name char(6) not null
);

alter table buy add constraint foreign key(mem_id) references member(mem_id) on update cascade on delete cascade;

INSERT INTO buy VALUES(NULL, 'BLK', '지갑');
INSERT INTO buy VALUES(NULL, 'BLK', '맥북');

select * from buy;

 

 

 

그 후 이전과 같이 update하는 쿼리문을 작성한 후 실행했다.

 

update member set mem_id = 'PINK' where mem_id = 'BLK';

 

 

그럼 아래와 같이 수정이 완료된 것을 볼 수 있었다.

 

 

 

 

 

 

 

내부 조인을 사용하여 물품 정보 및 사용자 정보를 확인할 수 있다.

필요한 정보를 보기 위해 내부 조인을 사용해 정보를 확인했다.

위에서 기준 테이블의 id를 변경했는데 만약 기준 테이블의 id만 변경된 것이라면 inner join은 불가능하다.

하지만 참조 테이블도 같이 변경되기 때문에 join을 할 수 있다.

select M.mem_id, M.mem_name, b.prod_name from buy B inner join member M on B.mem_id = M.mem_id;

 

그럼 아래와 같은 결과를 볼 수 있는데

on cascade udpate가 설정되어 있기 때문에 기준 테이블이 변경될 때 참조 테이블도 같이 변경 된 것을 볼 수 있다.

 

 

 

 

 

id가 PINK인 데이터를 기준 테이블에서 삭제했다.

delete from member where mem_id='PINK';

 

 

그리고 참조 테이블에서 데이터를 조회해보았다.

select * from buy;

 

 

기본 키 - 외래 키 관계에서는 기준 테이블에서 삭제되어 참조 테이블에서도 함께 삭제된다.

그러므로 참조 테이블에 아무 데이터도 안남아 있는 것을 볼 수 있다.

 

 

 

 

 

 

 

 

 

 

---

 

 

 

기타 제약조건

 

 

 

 

고유 키 제약조건

고유 키(Unique) 제약조건은 중복되지 않는 유일한 값을 입력하는 조건이다.

앞서 본 기본 키 제약조건과 비슷해보이지만 고유 키는 null 값을 허용한다.

유일 값을 입력하는 조건이지만 null 값은 여러 개가 입력되어도 상관 없다.

또한, 기본 키는 테이블 당 1개만 설정하지만 고유 키는 여러 개를 설정해도 된다.

 

 

 

 

고유 키의 속성을 알아보기 위해 이메일을 고유키로 설정한 다음 insert 해주었다.

drop table if exists buy, member;

create table member
( 
	mem_id char(8) not null primary key,
	mem_name varchar(10) not null,
	height tinyint unsigned null,
	email char(30) null unique
);

 

 

INSERT INTO member VALUES('BLK', '블랙핑크', 163, 'pink@gmail.com' );
INSERT INTO member VALUES('TWC', '트와이스', 167, null );
INSERT INTO member VALUES('WMN', '여자친구', 164, 'pink@gmail.com');

 

세번째 쿼리문은 첫번째와 이메일이 중복되기 때문에 에러가 발생한다.

에러가 발생하면 아래와 같은 에러 코드를 볼 수 있다.

Error Code: 1062. Duplicate entry 'pink@gmail.com' for key 'member.email'

 

 

테이블을 조회하면 세번째 여자친구의 데이터가 들어가지 않은 것을 확인할 수 있다.

 

 

 

 

 

 

 

 

 

체크 제약조건

체크(Check) 제약조건은 입력되는 데이터를 점검하는 기능을 한다.

특정 데이터만 입력하도록 데이터의 유효성 검사를 한다. 설정 시 컬럼 정의 뒤에 check(조건)을 추가한다.

 

 

아래는 height의 값이 100보다 크거나 같을 경우만 입력되도록 설정했다.

drop table if exists buy, member;

create table member
( 
	mem_id char(8) not null primary key,
	mem_name varchar(10) not null,
	height tinyint unsigned null check(height >=100),
	phone1 char(3) null
);

 

 

블랙핑크의  height는 163으로 제약사항에 해당되지 않지만 트와이스는 99이므로 제약조건에 위배된다.

INSERT INTO member VALUES('BLK', '블랙핑크', 163, null);
INSERT INTO member VALUES('TWC', '트와이스', 99, null );

 

 

그렇기 때문에 아래와 같은 오류코드가 발생한다.

Error Code: 3819. Check constraint 'member_chk_1' is violated.

 

 

이후 테이블을 조회하면 트와이스의 데이터는 제약조건에 위배되어 입력되지 않은것을 확인할 수 있다.

 

 

 

 

 

 

체크 제약조건에서 여러 값들 중 하나가 입력되도록 설정할 수 있다.

위와 같은 의도로 설정하려면 check in()을 사용하여 여러 값들을 제약조건으로 설정한다.

아래는 phone1의 제약조건을 설정시 check in()하여 여러 제약조건을 설정한 쿼리문이다.

 

alter table member add constraint check (phone1 in ('02', '031', '032', '054', '055', '061'));

INSERT INTO member VALUES('TWC', '트와이스', 167, '02' );
INSERT INTO member VALUES('OMY', '오마이걸', 167, '010' );

 

 

phone1에 설정한 check 제약조건에는 오마이걸의 phone1인 010이 없다.

그렇기 때문에 아래와 같은 오류 코드가 발생한다.

 

Error Code: 3819. Check constraint 'member_chk_2' is violated.

 

 

조회를 한 결과 제약조건에 위배되는 값을 가진 오마이걸의 데이터를 삽입되지 않은 것을 확인할 수 있다.

 

 

 

 

 

 

 

 

기본값 정의

기본값(Default) 정의는 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.

 

 

아래의 기본값 설정 예시는 height에 값을 입력하지 않거나 default로 입력되었을 경우 160이라는 값을 자동으로 삽입되도록 설정해주었다.

 

drop table if exists member;

create table member 
(
	mem_id char(8) not null primary key,
    mem_name varchar(10) not null,
    height tinyint unsigned null default 160,
    phone1 char(3) null
);

alter table member alter column phone1 set default '02';

INSERT INTO member VALUES('RED', '레드벨벳', 161, '054' );
INSERT INTO member VALUES('SPC', '우주소녀', default, default );

 

 

조회를 해보면 우주소녀 데이터에 height와 phone1을 default로 입력했는데 설정한 기본 값인 02와 160이 각 각 삽입되어 있는 것을 확인할 수 있었다.

 

 

 

 

 

널 값 허용

너널 값을 허용하려면 생략 혹은 null을 입력하고 허용하지 않으려면 not null을 사용한다.

하지만 primary key인 경우는 null이 있을 수 없으므로 자동으로 not null로 인식된다.

그리고 null은 공백(' ') 이나 0과는 다르다.

 

 

 

 

 

---

 

 

05-3. 가상의 테이블 : 뷰

 

 

 

 

뷰(view)

뷰는 데이터베이스 개체 중에 하나로 가상의 테이블이라고 부른다.

가상의 테이블이란 실체가 없다는 뜻인데 실제 테이블처럼 데이터를 가지고 있지는 않다.(대신 주소로 가짐) select 문으로 만들어져 select문으로 실행해 그 결과가 화면에 출력되는 방식으로 동착한다. 그렇기 때문에 테이블과 동일하게 보인다.

그러므로 실체는 없지만 기능은 일반 테이블과 같다.

 

뷰를 사용하면 사용자에게 테이블의 필요한 내용만 보이도록 할 수 있어 모든 정보를 공개하지 않을 수 있어 보안상 좋기 때문에 사용한다.

 

또한, 뷰는 단순 뷰와 복합 뷰로 나눌 수 있는데 단순 뷰는 하나의 테이블과 연관된 뷰이고 복합 뷰는 2개 이상의 테이블과 연관된 뷰를 말한다.

 

 

 

원래 테이블

 

 

 

뷰의 생성 방법

create view 뷰_이름 as select 문;

 

 

 

다음은 회원 테이블의 id, name ,addr 데이터에 접근하는 v_member라는 뷰를 생성하는 쿼리문이다.

use market_db;
create view v_member as select mem_id, mem_name, addr from member;
select * from v_member;

 

select 쿼리문을 통해 생성한 뷰를 볼 수 있다.

그럼 select해서 접근한 id, name, addr 데이터들이 뷰에 들어가 있는 것을 볼 수 있다.

 

 

 

 

필요한 열만 보기 위해 조건식을 넣을 수 있다.

아래의 쿼리문은 addr가 서울, 경기인 데이터만 보기 위해  where 조건절을 작성했다.

select mem_name, addr from v_member where addr in ('서울', '경기');

 

 

위의 쿼리문을 실행한 결과를 보면 addr가 서울, 경기인 데이터만 볼 수 있다.

 

 

 

 

복잡한 sql 단순화

 

조인한 쿼리문을 조회하려면 매번 복잡한 쿼리문을 작성해야한다.

뷰를 사용하면 그러한 불편함을 편리하게 해줄 수 있다.

아래의 쿼리문과 같이 뷰를 생성하고 뷰에 조인한 테이블을 select해주면 복잡한 쿼리문 작성 필요 없이 뷰를 조회하면 된다.

create view v_memberbuy as select B.mem_id, M.mem_name, B.prod_name, M.addr, concat(M.phone1, M.phone2) '연락처' from buy B inner join member M on B.mem_id = M.mem_id;

select * from v_memberbuy;

 

 

 

 

생성한 뷰에서 where 조건절을 사용해 특정 데이터만 불러오는 것도 가능하다.

select * from v_memberbuy where mem_name='블랙핑크';

 

 

 

 

 

 

 

 

---

 

 

뷰의 실제 작동

 

 

 

 

뷰의 실제 생성, 수정, 삭제

뷰를 생성 시 뷰에 사용될 열 이름을 기존 테이블과 다르게 지정할 수 있다.

별칭(alias)를 사용하면 가능한데 as를 붙이는 형식을 가진다.

 

하지만 뷰에서는 조회할 때 열(컬럼) 이름에 공백이 있으면 백틱(`)으로 묶어줘야한다.

(백틱은 작음따옴표가 아닌 번호 1 옆의 `을 말한다.)

 

 

 

백틱 사용법에 대한 예시이다.

아래의 쿼리에서 설정한 as에 공백이 존재한다.

그러므로 뷰를 조회할 경우 백틱을 사용하여 조회하는 것을 볼 수 있다.

 

create view v_viewtest1 as select B.mem_id 'member ID' , M.mem_name as 'Member Name' , B.prod_name "Product Name" , concat(M.phone1, M.phone2) as "Office Phone" from buy B inner join member M on B.mem_id = M.mem_id;

select distinct `Member ID`, `Member Name` from v_viewtest1;   -- 백틱 사용

 

 

 

 

 

 

 

 

뷰의 수정은 alter view 뷰이름 as select ~ 형식을 사용한다.

또한, as는 영어뿐만 아니라 한글도 가능하다.

하지만 한글은 인식이되지 않을 수도 있으므로 영어 사용을 권장한다.

 

alter view v_viewtest1 as select B.mem_id '회원 아이디' , M.mem_name as '회원 이름' , B.prod_name "제품 이름" , concat(M.phone1, M.phone2) as "연락처" from buy B inner join member M on B.mem_id = M.mem_id;

select distinct `회원 아이디`, `회원 이름` from v_viewtest1;

 

수정된 데이터 값을 볼 수 있다.

 

 

 

뷰의  삭제는 테이블 삭제와 같은 형식을 가진다.

drop view v_viewtest1;

 

 

 

 

 

 

뷰의 정보 확인

 

생성된 뷰에 대한 정보를 describe 문을 사용하여 확인할 수 있다.

앞에서 배운 desc와 같다.

테이블에서 사용시에는 primary key 등의 정보를 확인할 수 있지만 뷰에서는 확인되지 않는다.

 

 

* create or replace view 뷰_이름 as select ~

그냥 create view는 기존에 뷰가 존재하면 오류가 발생한다.

하지만 create of replace view는 기존에 뷰가 존재해도 덮어쓰는 효과는 낸다.

 

 

 

 

 

 

---

 

 

 

뷰 통한 데이터의 수정/삭제

뷰를 통해서 테이블의 데이터를 수정할 수 있다.

 

 

 

아래와 같이 뷰를 수정하는 쿼리문을 작성하여 실행했다.

update v_member set addr = '부산' where mem_id = 'BLK';

 

 

아래의 결과와 같이 오류 없이 수정을 할 수 있다.

 

 

 

 

 

 

뷰에 값을 삽입하도록 insert쿼리문을 작성하여 실행했다.

 

insert into v_member(mem_id, mem_name, addr) values('BTS', '방탄소년단', '경기');

 

 

하지만 아래와 같은 오류가 발생한다.

이러한 오류가 발생하는 이유는 위의 쿼리문에 작성되지 않은 mem_number이 not null이기 때문이다.

쿼리문을 봤을 때 뷰에서 insert 하지만 원래 member 테이블에 inert 하는 것과 같다. 그렇기 때문에 member테이블의 mem_number not null 설정이 제약조건에 위배되는 것이다.

Error Code: 1423. Field of view 'market_db.v_member' underlying table doesn't have a default value

 

 

member 테이블의 desc 정보이다.

보면 mem_number가 not null로 설정되어 있는 것을 알 수 있다.

 

 

 

 

 

---

 

 

뷰를 통한 데이터의 입력

 

 

아래의 쿼리문을 통해 뷰에서 데이터를 입력했다.

create view v_height167 as select * from member where height >= 167;

insert into v_height167 values('TRA', '티아라', 6, '서울', null, null, 159, '2005-01-01');

 

 

따로 오류 코드가 발생하지 않고 정상적으로 입력되었다는 메세지가 출력되었다.

하지만 조회를 해보면 결과에서 이상한 점이 발생했다.

 

select * from v_height167;

 

 

앞서 삽입한 티아라의 데이터가 삽입되지 않았다.

아래와 같은 결과가 나온 이유는 where 조건 때문이다.

where 조건에서 height의 범위를 167 이상만 보이도록 조건을 설정했다.

하지만 티아라의 height는 159로 167 미만의 값이기 때문에 보이지 않는다.

 

 

 

 

 

위처럼 설정된 값의 범위를 벗어나는 값을 입력하지 않도록 하려면 예약어 with check option을 사용한다.

with check option은 아래와 같이 select 쿼리문 뒤에 붙어 작성한다.

alter view v_height167 as select * from member where height >= 167 with check option;

 

 

이전과 같이 height가 167보다 작은 데이터를 삽입하고자 쿼리문을 작성해 실행했다.

insert into v_height167 values('TOB', '텔레토비', 4, '영국', null, null, 140, '1995-01-01');

 

그럼 이전과는 다르게 에러 코드가 발생하여 데이터 삽입이 되지 않은 것을 확인할 수 있다.

Error Code: 1369. CHECK OPTION failed 'market_db.v_height167'

 

 

 

 

 

뷰가 참조하는 테이블의 삭제

 

뷰가 참조하는 테이블을 삭제하는 쿼리문은 아래와 같다.

drop table if exists buy, member;

 

 

 

* 뷰 상태를 확인하는 명령어 check table 뷰_이름;

 

* 복합 뷰는 읽기 전용이다. (두개 테이블로 만들기 때문에 입력 수정 삭제 할 수 없음)