혼자 공부하는 SQL - 4주차

• 4주차의 미션은 다음과 같습니다.

• 이번 장에서는 테이블과 뷰에 대해 알아보았습니다.

테이블

• create table 문으로 테이블을 생성할 수 있습니다. 해당 sql 문 내에 테이블 이름, 열 이름, 데이터 형식 등을 지정합니다.
• auto_increment를 사용하면 열에 입력되는 값이 1부터 자동 증가하게 됩니다. 단, primary key 또는 unique 열이어야 합니다.
• primary key 는 기본 키로 지정하는 것이고, not null은 열에 빈 값을 허용하지 않는 것입니다. 또한, foreign key 를 사용해 열을 외래 키로 지정할 수 있습니다.

테이블 제약조건

• 제약 조건은 기본키, 외래 키, 고유 키, 체크, 기본값, not null 제약 조건 등이 있습니다.
• 제약조건은 데이터의 무결성을 지키기 위한 제한 조건입니다. 데이터의 무결성이란 데이터에 결함이 없다는 의미입니다.

기본 키 제약 조건

• 기본 키 제약 조건은 데이터를 구분할 수 있는 식별자를 지정해 주는 것을 의미합니다. 중복 불가, not null 이됩니다. create table 문으로 열의 형식 뒤에 primary key 를 붙이거나, create table 의 마지막 행에 primary key (열_이름) 또는, constriant primary key pk_이름 (얄_이름), alter table 문으로 아래와 같이 지정할 수 있습니다.

alter table 테이블명
	add constraint
    primary key (열_이름);

외래 키 제약 조건

• 외래 키 제약 조건은 두 테이블 사이를 연결해주고 데이터 무결성을 보장해 주는 역할을 합니다. 외래 키는 꼭 다른 테이블의 기본 키와 연결됩니다.
• create table 문에서 끝에 foreign key(열_이름) reference 기준_테이블(열_이름)으로 설정할 수 있습니다. alter table 문으로는 아래와 같이 지정할 수 있습니다.

alter table 테이블_이름
	add constraint
    foreign key(열_이름)
    reference 기준테이블_이름(기준테이블_열_이름);

• 기준 테이블의 열이 변경/삭제는 불가합니다. 하지만 자동으로 변경 시키는 제약 조건이 존재합니다. on update cascade, on delete cascade 문으로 변경/삭제 시 자동으로 참조 테이블의 데이터도 변경/삭제됩니다.

기타 제약 조건

• 고유 키 제약조건으로 '중복되지 않는 유일한 값'을 입력해야 하는 조건으로 기본 키 제약 조건과 유사하지만 null 값을 허용합니다.
• 체크 제약조건으로 데이터를 점검하는 기능을 추가할 수 있습니다. 열의 정의 뒤에 check(조건)을 추가해 주는 조건입니다.
• 기본값 정의를 이용해 값을 입력하지 않았을 때 자동으로 입력될 값을 지정해 놓는 방법이 있습니다.

뷰

• 뷰는 데이터베이스 개체 중 하나로 가상 테이블이라고도 부릅니다. 테이블처럼 데이터를 가지고 있지는 않지만 select 문으로 만들어져 뷰에 접근하는 순간, select 가 실행됩니다. 윈도우의 바로가기 아이콘과 비슷한 개념입니다.

create view 뷰_이름
as
	select 문;

• 뷰를 사용하는 이유는 보안에 도움이 되고, 복잡한 sql을 단순하게 만들 수 있습니다.

뷰를 통한 데이터의 수정, 삭제

• 뷰를 이용해 데이터를 수정도 가능합니다. 단, 몇 가지 조건이 필요합니다.
• 뷰를 통해 데이터를 입력하려면 뷰에서 보이지 않는 테이블의 열에 not null이 없어야 합니다.
• 또한, with check option을 통해 뷰에서 설정한 범위의 데이터만 입력되도록 제한합니다.
• 뷰를 참조하는 테이블이 삭제되면 뷰 또한 조회할 수 없습니다. 뷰가 조회되지 않으면 check table 로 뷰의 상태를 확인해볼 수 있습니다.

기본 미션

• market_db의 회원 테이블(member) 생성하고, 데이터 입력한 후 인증하기

member 테이블 생성

drop table if exists member;
create table member(
    mem_id      char(8) not null primary key,
    mem_name    varchar(10) not null,
    mem_number  tinyint not null,
    addr        char(2) not null,
    phone1      char(3) null,
    phone2      char(8) null,
    height      tinyint unsigned null,
    debut_date  date null
);

• 각 열마다 데이터 형식과 null 또는 not null 조건 지정했습니다.
• 기본 키인 mem_id 는 primary key로 기본 키 지정했습니다.

buy 테이블 생성

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,
    group_name  char(4) null,
    price       int unsigned not null,
    amount      smallint unsigned not null,
    foreign key(mem_id) references member(mem_id)
);

• 각 열마다 데이터 형식과 null 또는 not null 조건 지정했습니다.
• num은 auto_increment로 지정 -> primary key 또는 unique로 지정해야 함. 여기서는 primary key로 지정했습니다.
• foreign key를 지정했습니다. mem_id 열을 member 테이블의 mem_id 열과 외래 키 관계로 연결해라 라는 의미입니다.

데이터 입력하기

insert into member values('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-19');
insert into member values('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016-8-8');
insert into member values('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015-1-15');

insert into buy values(null, 'BLK', '지갑', null, 30, 2);
insert into buy values(null, 'BLK', '맥북프로', '디지털', 1000, 1);
insert into buy values(null, 'APN', '아이폰', '디지털', 200, 1);

• APN 은 아직 회원 테이블에 존재하지 않아 오류가 발생했습니다.

ERROR 1452 (23000): 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`))

선택 미션

• p. 271 확인 문제 4번 풀고 인증하기
• 기존에 뷰가 있으면 덮어쓰고, 없으면 새로 생성하는 SQL 빈칸 채우기

[빈칸] 뷰_이름
as
	select 문;

• 빈칸에 들어가야 할 내용은 아래와 같습니다.

create or replace view

• 즉,

create or replace view 뷰_이름
as
	select 문;

• 위와 같은 형태가 됩니다.