데이터베이스 5. 테이블 제약조건, 뷰.

1. 테이블 : 행과열로 구성된 2차원표. 행 = 로우,레코드 열 = 컬럼,필드

 

2. 제약조건 : 데이터 무결성을 지키기 위한 조건, 데이커에 결함이 없다는 의미

 

3. 제약조건 종류 : primarykey, foreignkey, unique, check, default 정의, null값 허용

 

4. primary key 제약조건 : 기본키는 중복값이 입력될 수 없으며 NULL도 입력될 수 없다,

하나의 테이블에는 기본키가 1종류 밖에 없다.

 

5. 생성하면서 primary key 만들기 

create table member

(memid char(8) not null primary key,

memname varchar(10) not null,

height tinyint unsigned null,

);

 

6. 생성한 이후에 키본키를 설정하는 방법

alter table member add consrtaint primary key (memid);

 

7. foregin key제약조건 다른 테이블의 primary key를 자신의 primarykey로 쓰는것

create table buy(

num int auto_increment not null primary key,

mem_id char(8) not null,

prod_name char(8) nit null,

foreign key(mem_id) refrences member(mem_id

);

num은 자동으로 생성되는 값을 기본키로, mem_id는 member 테이블의 mem_id를 가져와 외래키로 사용함

외래키인 buy테이블의 mem_id는 꼭 mem_id라는 이름으로 할 필요는 없다.

 

8. 테이블 생성 이후에 외래키 설정하는 방법

alter table buy

add constraint

foreign key(mem_id)

refrence member(mem_id);

 

9. buy테이블은 member테이블을 외래키로 참조하고 있으므로 

member 테이블에 insert into member valuer('BLK', '블랙핑크', 163); 과 같이 등록 후

buy 테이블에 insert into buy(null, 'BLK', '지갑'); 이런식으로 넣아야한다.

auto incremebt는 반드시 null값을 따로 넣어야 한다.

 

10. 만약 member, buy테이블이 존재하는 상황에서 회원아이디를 수정 또는 삭제하려면?

순서대로 다단계 작업을 해야한다. 

 

11. 편하게 update, delete가 되게 하려면? -> buy테이블을에 cascade옵션을 추가한다.

 

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) refrenses member(mem_id)

on update cascade

on delete cascade;

 

12 unique key 제약조건

고유키의 특징 : 중복되지 않은 유일한 값을 입력해야함

auto_increment는 기본키, 고유키에 적용가능

기본키와 다른점 null이 가능함.

한 테이블에 여러개의 고유키 사용 가능

 

create table member(

mem_id cahr(8) not null primary key,

mem_name varchar(10) not null,

height tinyint unsigned null,

email char(30) null unique

);

 

13. check 제약조건

입력하는 값을 체크해 준다.

create table member(

mem_id cahr(8) not null primary key,

mem_name varchar(10) not null,

height tinyint unsigned null check (height >= 100),

phone1 char(3) null

);

 

alter을 통한 제약조건 추가

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

 

14. defalut 값 추가

값을 default로 하면 기본 값을 넣음

create table member(

mem_id cahr(8) not null primary key,

mem_name varchar(10) not null,

height tinyint unsigned null default 160,

phone1 char(3) null

);

 

insert into member valure('RED', '레드밸벳', default, defalut );

이런식으로 default를 사용 가능

 

15. null(널 허용), not null(널 허용하지 않음)  ' '과 같은 공백, 0과는 다른 값이니 주의할 것

 

16. 가상테이블 뷰

데이터베이스 개체 중 하나로 기존 테이블에서 select한 결과이다.

사용자 입장에서는 실제 테이블고 거의 동일한 개체로 취급된다.

하나로만 이루어진 단순 뷰, 두 개 이상의 테이블로 이루어지느 복합뷰가 있다.

바로가기 아이콘과 비슷함.

테이블에서 selext한 결과를 또 다른 테이블로 보고자하는 개념이다.

 

17. 뷰 생성하기

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

 

 

 

18. 뷰 사용하기

뷰 안에서도 원하는 컬럼만 볼 수도 있고, wherw조건이 사용 가능함.

 

select * from v_member;  이렇게 테이블 처럼 부를 수 있다.

select mem_name, addr from v_member where addr in ('경남', '경북);

 

기본적으로 읽기전용이나 조건을 만족한다면 수정할 수도 있다.

 

19. 뷰를 사용하는 이유

 

(1) 보안에 도움이 된다. 

    테이블에 있는 중요정보를 숨기고 필요정보만 보여줄 수 있음

(2) 사용 편의성

   복잡한 sql을 단순하게 만들 수 있음. 

   자주 사용하는 select문을 미리 뷰로 만들어두면 sql이 간단해짐

   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;

 

  뷰 생성

   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 mem_name = '블랙핑크'; 

   짧아진다.

 

19. 뷰의 컬럼명

   원본과 다르게 생성해도 상관 없다.

   create view v_viewtest1 as

   select B.mem_id 'MemberID', 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 `MemberID`, `Member Name` from v-viewtest1;

불러오는 방법. 작은따옴표가 아니라 백틱을 사용해야 한다.

 

20. alter, drop

 

테이블 처럼 alter, drop도 가능하다

 

변경

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

 

21. 뷰 정보 확인하기

뷰의 필드, 컬럽타입, 키, 널허용 등 정보를 알 수 있다.

desc v_member;

describe v_member;

테이블 또한 describe로 확인할 수 있으나 키 정보는 확인할 수 없다는 점이 다르다.

 

22. show create

생성 시 사용했던 create문을 다시 보고싶을 때 사용

 

23 . 뷰를 사용해서 원본 테이블 값을 update, insert하는 방법

update v_member srt addr='부산' where  mem-id='BLK';

원본데이터가 있는경우에 업데이트 된다.

 

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

오류 발생 원본 테이블에는 있지만 뷰에는 없는 mem_number컬럼이 not null이기 때문.

member테이블에 있지만 view에 없는 값이 모두 null허용이거나, not null이라도 default값이 있었다면 

오류가 발생하지 않았을 것

 

24. 뷰를 사용해 원본데이터 값을 delete하는 방법

delete from v_member where mem_id = 'APN';

 

create or replace view v_height as select * from member where height > 167;

delete from v_height where height < 167;

이렇게 하면 기존 테이블에는 167보다 작은 값이 있으나 v_height테이블에는 값이 없으므로 아무것도 삭제되지 않음

 

25. 기본 테이블의 삭제

기본키 외래키 관계외 다르게 뷰가 있어도 원본 테이블을 drop할 수 있음

drop table if exists buy, member;

테이블 삭제 후 뷰는 사용할 수 없음.