[혼공S] 4주차_제약조건을 활용한 테이블 및 가상 테이블(VIEW) 생성 및 활용

 

 

---

 

 

혼자 공부하는 SQL

✔️ 테이블 생성

✔️ 테이블 제약조건

✔️ 가상 테이블 : VIEW

 

 

---

 

 

✔️ 테이블 생성

 

 

테이블

표 형태로 구성된 2차원 구조(행과 열로 이루어짐)

(행 = 로우, 레코드 / 열 = 컬럼, 필드)

예) Excel 시트, MySQL 테이블 등

 

 

---

 

 

MySQL 워크벤치에서 GUI 환경에서 테이블 생성

 

 

1. 테이블을 생성할 새로운 데이터베이스 생성

-- 새로운 데이터베이스 생성
create database naver_db;

 

 

화면에 바로 적용되지 않기 때문에 SCHEMAS 패널의 새로고침 아이콘

혹은 SCHEMAS 빈공간 우클릭 > Refreash All을 활용하여 확인

 

 

 

2. SCHEMAS의 Tables에서 우클릭 > Create Table 선택하여 member 테이블 생성

 

+

 

동일한 방법으로 buy 테이블 생성

AI 적용하면 num에 AUTO_INCREMENT가 적용되어 +1씩 자동으로 수행됨

 

 

 

3. 테이블을 확인하고 데이터 입력

-- member 테이블 확인

select 
	*
from member;

-- buy 테이블 확인

select 	
	*
from buy;

 

 

각 테이블을 확인하고 데이터 입력 진행

member 테이블 데이터 입력

 

 

APN이 member 테이블에 적용되지 않은 데이터라 buy 테이블에 입력할 수 없다는 에러 메세지 확인

 

 

 

mem_id에 APN이 적용된 데이터를 삭제(테이블 좌측 우클릭 > Delete Row(s))한 후 Apply 진행

buy 테이블의 데이터 입력

 

 

---

 

 

MySQL 워크벤치에서 SQL 활용 테이블 생성

 

1. 테이블을 생성할 새로운 데이터베이스 생성

-- 데이터베이스 생성

drop database if exists naver_db;
create database naver_db;

 

 

 

2. SQL 쿼리문을 활용한 테이블 생성

 

member 테이블 생성 및 데이터 확인

226p naver_db에 member 테이블 생성하고, 229p 데이터 입력 후 인증

-- 같은 이름의 데이터베이스 존재한다면 삭제하고 새로 생성
drop database if exists naver_db;
create database naver_db;

-- 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, -- 주소(2글자만)
  phone1		CHAR(3) NULL, -- 연락처의 국번
  phone2		CHAR(8) NULL, -- 연락처 나머지 번호(8자리 숫자)
  height    	TINYINT UNSIGNED NULL, -- 평균 키
  debut_date	DATE NULL -- 데뷔 일자
);

-- member 테이블 입력

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

select * from member;

 

 

buy 테이블 생성

-- buy 테이블 생성

drop table if exists buy;
create table buy -- 구매 테이블
( 
	num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(기본키 설정)
    mem_id CHAR(8) NOT NULL, -- 아이디(FK)
    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)
);

-- buy 테이블 데이터 입력

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); -- 오류 발생(입력 안됨)

select * from buy;

 

 

---

 

 

✔️ 테이블 제약조건

 

 

제약조건

데이터의 무결성을 지키기 위해 제한하는 조건

(데이터 무결성 = 데이터에 결함 X)

예) 네이버 회원의 아이디가 중복 저장되지 않아야 함(혼란을 야기)

 

 

제약조건의 종류

PRIMARY KEY(기본키)

FOREIGN KEY(외래키)

UNIQUE

CHECK

DEFAULT

NULL 및 NOT NULL

 

 

---

 

 

PRIMARY KEY(기본키)

데이터를 구분할 수 있는 식별자

예) 회원 테이블의 회원 아이디, 학생 테이블의 학번 등

 

값이 중복될 수 없으며, NULL값 입력될 수 없음

(최근에는 아이디가 아닌 주민번호, 이메일, 휴대폰 번호 등 중복되지 않는 열을 기본키로 지정하는 경우 존재)

 

기본적으로 테이블은 기본 키를 가져야 합니다.

(기본 키는 1개만 가질 수 있으며, 데이터의 특성을 가장 잘 반영하는 열을 선정하는 것이 좋음)

-- 기본키 설정법

create table 테이블명
( 열이름1  		데이터타입 NOT NULL PRIMARY KEY, -- 회원 아이디(기본키 설정)
	...
);

-- 다른 방법으로는 마지막에 PRIMARY KEY를 지정 가능

create table 테이블명
( 열이름1  		데이터타입 NOT NULL, -- 회원 아이디(기본키 설정)
	...,
  PRIMARY KEY(열이름1)
);

-- member 테이블을 통해 확인

describe member;

 

mem_id에 Key PRI가 설정되어 있는 것을 확인

 

+

 

ALTER 테이블에서 기본 키 설정

drop table if exists member;
create table member
(
	mem_id CHAR(8) NOT NULL,
    mem_name VARCHAAR(10) NOT NULL,
    height TINYINT UNSIGNED NULL,
);

ALTER TABLER member -- member 변경
	add constraint -- 제약조건 추가
    primary key(mem_id); -- mem_id열에 기본키 제약조건 설정

 

 

---

 

 

FOREIGN KEY(외래키)

두 테이블 사이의 관계 연결과 데이터 무결성 보장

(다른 테이블의 기본 키와 연결됨)

 

기본 키가 있는 테이블(기준 테이블)

외래 키가 있는 테이블(참조 테이블)

 

buy 테이블에 APN이 포함된 데이터 입력할 때 발생했던 오류는 

외래 키 제약조건을 위반했기 때문에 발생

 

참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나 고유 키(UNIQUE)로 설정되어야 함

-- buy 테이블 중 외래키 제약조건 설정

 FOREIGN KEY(mem_id) REFERENCES member(mem_id)
 -- buy 테이블의 mem_id는 member 테이블의 기본 키인 mem_id를 참조한다는 의미

 

+

 

ALTER 테이블에서 외래 키 설정

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

ALTER TABLE buy -- buy 수정
	add constraint -- 제약조건 추가
    foreign key(mem_id) -- 외래 키 제약조건을 buy 테이블의 mem_id로 설정
    references member(mem_id); -- 참조할 기준 테이블은 member 테이블의 mem_id로 설정

 

기본 - 외래 키 구조가 설정된 후는 기준 테이블의 참조한 열의 이름은 변경되지 않음

(열 이름이 변경된다면 참조 테이블의 데이터에 문제 발생하기 때문)

-- BLK를 PINK로 변경 시도
update member set mem_id = 'PINK' where mem_id = 'BLK';

-- 기준 테이블의 mem_id 삭제 X
delete from member where mem_id = 'BLK';

 

 

ON UPDATE CASCADE

기준 테이블의 열 이름 변경될 때 참조 테이블의 열 이름이 자동으로 변경되는 기능 제공

-- alter table을 통해 on update 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', '맥북');

-- update문 적용

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

-- 변경된 데이터 확인

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

 

 

delete 문도 수행되는 것을 확인

-- delete 문 수행

delete from member where mem_id = 'PINK';
select * from buy;

 

 

---

 

 

기타 제약조건

 

 

UNIQUE

중복되지 않는 유일한 값

(기본 키와 유사하지만 NULL값을 허용한다는 차이가 존재)

-- UNIQUE 적용

drop table if exists buy, member;
create table member
(
	mem_id char(8) not null primary key,
    mem_name varchar(8) 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('APN', '에이핑크', 164, 'pink@gmail.com');

고유값은 중복은 허용하지 않지만, 비어 있는 값은 허용

 

 

CHECK

입력되는 데이터를 점검하는 기능

(평균키가 -값을 가지지 않도록 설정, 국번에 우리나라의 국번만 입력되도록 설정하는 등)

 

평균 키가 100 이상의 값만 입력되도록 설정, CHECK로 설정한 국번 외의 다른 번호는 입력되지 않도록 적용

-- CHECK 제약조건 적용

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

-- ALTER TABLE에서 CHECK 제약조건 적용
-- 적용된 국번 이외의 번호는 입력되지 않고 에러 메세지 보냄

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

 

 

DEFAULT(기본값 정의)

값이 입력하지 않았을 때 자동으로 입력될 값을 미리 지정

 

키를 입력하지 않고 기본적으로 160, phone1에 국번에는 02가 입력되도록 정의

-- default 값 적용

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

-- ALTER TABLE로 적용

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

 

 

NULL 및 NOT NULL

NULL값 허용하는 경우 NULL(null) 입력

NULL값 허용하지 않는 경우는 NOT NULL(not null) 입력하여 적용

(NULL값과 공백(' ')이나 0과는 다르다는 점을 주의)

 

 

---

 

 

✔️ 가상 테이블 : VIEW

 

 

뷰(VIEW)

데이터베이스 개체 중 하나로, 테이블과 밀접히 연관되어 

테이블과 동일한 개체(가상 테이블)라고 생각하고 활용 가능

 

뷰와 테이블은 동일하지 X

뷰는 SELECT문으로 이루어져 있기 때문에

뷰에 접근하는 순간 SELECT문이 실행되고 그 결과가 출력되는 방식

 

단순 뷰 : 하나의 테이블과 연관된 뷰

복합 뷰 : 2개 이상의 테이블과 연관된 뷰

(뷰와 테이블을 구분하기 위해 v_를 붙여서 구분)

 

 

 

뷰(VIEW) 생성

-- VIEW 기본 형식

create view v_뷰이름
	as select문;

-- 조건식을 활용하여 뷰 생성 가능

create view v_뷰이름
	as where 조건 포함 select문;
    
-- member 테이블을 활요한 v_member 뷰 생성

create view v_member
as 
	select mem_id, mem_name, addr from member;
    
-- 생성된 뷰의 데이터 확인
select * from v_member;

-- 조건식 넣어서 확인 가능
select 
	mem_name,
    addr
from v_member
where addr in ('경기', '서울');

 

 

뷰(VIEW)의 작동 과정

 

 

뷰(VIEW) 사용하는 이유

 

 

보안(security)에 도움이 된다

중요도에 따른 원본 데이터에서 필터링된 데이터를 바탕으로 뷰를 생성

원본 테이블이 아닌 뷰 접근 권한만 준다면 보안 강화 가능

(DB에서 사용자마다 접근 권한의 차이를 두어 처리하면 보안 강화 가능)

 

 

복잡한 SQL을 단순하게 만들 수 있다

물건을 구매한 회원들에 대한 SELECT문 조회

-- 기존의 쿼리

select 
	b.mem_id,
    m.mem_name,
    b.prod_name,
    m.addr
from buy as b
join member as m on b.mem_id = m.mem_id;


-- 뷰를 생성한다면 간단히 접근 가능

create view v_member_buy as (
	select 
		b.mem_id,
    	m.mem_name,
    	b.prod_name,
   		m.addr,
        concat(m.phone1, m.phone2) as '연락처'
	from buy as b
	join member as m on b.mem_id = m.mem_id;
);

-- 생성된 뷰에서 데이터 조회
select 
	*
from v_member_buy 
where mem_name = '블랙핑크';

 

 

---

 

 

뷰(VIEW)의 실제 작동

 

뷰에서 사용될 열 이름을 테이블과 다르게 지정 가능

(별칭으로 활용하며, 열 이름에 공백이 있다면 백틱(')으로 묶어서 표현)

+ 백틱 키 : 키보드 1번 왼쪽에 위치한 키

use market_db;

-- 뷰 생성
create view v_viewtest1 as (
	select 
    	b.mem_id as 'Member ID',
        m.mem_name as 'Member Name',
        b.prod_name as 'Product Name',
        concat(phone1, phone2) as 'Office Phone'
    from buy as b
    join member as m on b.mem_id = m.mem_id
);

-- 생성된 뷰의 데이터 확인
-- 백틱 입력이 안되는 경우 영어로 전환하여 키를 누르면 입력됩니다

select distinct 
	`Mem+ber ID`,
    `Member Name` 
from v_viewtest1;

-- ALTER TABLE 활용

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

-- 백틱 활용
select distinct
	`회원 아이디`,
    `회원 이름`
from v_viewtest1;

 

+

 

데이터베이스 생성 / 수정 / 삭제

 

생성 : CREATE 개체_종류

예) create view ~

 

수정 : ALTER 개체_종류 

예) alter table ~

 

삭제 : DROP 개체_종류

-- 뷰의 삭제

drop view v_viewtest1;

 

 

---

 

 

뷰(VIEW)의 정보 확인

 

 

create or replace view

기존의 뷰가 존재하여도 덮어쓰기 효과를 내기 때문에 오류 발생하지 않음

(drop view와 create view를 연속으로 작성한 것과 동일)

-- create or replace view 실행

use market_db;
create or replace view v_viewtest2 as (
	select 
    	mem_id,
        mem_name,
        addr
    from member
);

-- 뷰 정보 확인

describe v_viewtest2;

 

+

 

뷰(VIEW)의 소스 코드 확인

-- 뷰의 소스 코드 확인

show create view v_viewtest2;

 

 

---

 

 

뷰(VIEW)를 통한 데이터의 수정 및 삭제

 

 

UPDATE문을 활용한 데이터 수정

-- 뷰 데이터 수정

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

 

 

지정한 범위로 뷰 생성

-- 지정한 범위의 값의 뷰 생성

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

-- 생성된 데이터 확인

select 
	*
from v_height167;

 

 

뷰를 통한 데이터 삭제 및 입력

-- 생성된 뷰를 통해 데이터 삭제

delete from v_height167 where height > 167;

-- height 167 미만인 데이터 입력

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

-- 데이터 확인

select 
	*
from v_height167;

 

뷰에 데이터를 입력했지만 167 이상만 조회되기 때문에 입력한 데이터가 보이지 않음

키가 167 이상으로 구성된 뷰이므로 167 이상의 데이터를 입력한다면 보일 것으로 예상

 

 

예약어 WITH CHECK OPTION 활용

뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않도록 설정 가능

(위처럼 167 미만의 값이 입력되는 것이 아니라 167 미만의 값을 입력한다면 에러 메세지가 나오도록 설정)

 -- ALTER TABLE 활용 WITH CHECK OPTION 수행
 alter view v_height167 as
	select 
    	*
    from member
    where height >= 167	
	with check option;

-- 167 미만의 값 입력
-- 에러 메세지가 나오며 입력되지 않음

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

 

+

 

복합 뷰 생성

-- 2개 이상의 테이블의 데이터를 활용한 복합 뷰 생성

create view v_complex as (
	select 
    	b.mem_id,
        m.mem_name,
        b.prod_name,
        m.addr
    from buy as b
    join member as m on b.mem_id = m.mem_id
);

-- 복합 뷰 확인

select
	*
from v_complex;

 

 

---

 

 

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

 

 

참조되어야 하는 buy, member 테이블 삭제 후 뷰 확인

-- 참조하는 테이블 삭제

drop table if exists buy, member;

-- 생성되었던 뷰 확인
-- 참조되어야 하는 테이블이 없기 때문에 에러 발생

select 
	*
from v_height167;

-- 생성된 뷰의 상태 확인

check table v_height167;

 

+

 

추가숙제

 

 

➀ CREATE AND REPLACE VIEW

➁ CREATE OR REPLACE VIEW

➂ CREATE AND OVERWRITE VIEW

➃ CREATE OR OVERWRITE VIEW

 

 

---

 

 

이상으로 4주차 내용 정리를 마무리 하겠습니다.

혹시나 공부를 원하시는 분은 해당 도서를 구매하셔서 공부하는 더 좋을 것 같습니다.

혼자 공부하는 SQL 도서

혼자 공부하는 SQL

 

 

12강부터 14강까지의 내용을 다루고 있으니 궁금하신 분은 유튜브를 참고하여 공부하면 좋을 것 같습니다.