혼공SQL 4주차

chapter 5

행 = row = record
열 = column 컬럼 = field

테이블을 만들기 전에 설계를 해야 함.
설계 : 테이블 이름, 열 이름, 데이터 형식, PK/FK 설정 등.

< GUI (MySQL Workbench) 환경에서 테이블 만들기 >

1. 데이터베이스 생성 : create database name_of_db; OR SCHEMAS 패널에서 Create Schema

2. 테이블 생성 : 특정 DB 우클릭 -> Create Table -> 컬럼 구성 -> Apply SQL Script to Database 창에서 Create Table 코드 확인.

( 제약 조건
PK : Primary key, 중복 or NULL 불가.
NN : Not Null로 체크 시 NULL은 못 들어옴.
UQ : Unique index, 체크 시 중복 값은 불가.
B : Binary column, 데이터를 이진 문자열로 저장.
UN : Unsigned data type, 수치형일 경우 범위가 음수에서 양수범위인걸 양수에서 더블로 늘어남. 컬럼값이 음수가 될 일이 없으면 쓰는걸 추천.
ZF : Zero-filled, 숫자 앞자리의 공백에 0을 채움. (INT(5)면 00123)
AI : Auto increment, 일련 번호가 자동적으로 증가하며 매겨짐.
G : Generated, 컬럼 값이 계산식에 의해 자동 계산될 때 체크. 타 컬럼을 기반으로 한 수식으로 값이 정해짐. )

3. 데이터 입력 : schemas 패널 내 특정 테이블 우클릭 후 select rows -> result grid 내 작성. PK-FK 연결되어있는 테이블의 경우 반드시 그 관계를 우선적으로 지켜야 함. ( 물건을 구매하고 싶으면 회원가입 먼저 하세요~ 의 의미. )

< SQL로 테이블 만들기 >

• 데이터베이스 생성

drop database if exists name_of_db;
create database name_of_db;

이후 Refresh ALL 클릭.

• 테이블 생성

naver_db라는 DB 내 member 테이블 생성

USE naver_db;
DROP TABLE IF EXISTS member;
CREATE TABLE member
(	mem_id CHAR(8) NOT NULL PRIMARY KEY, #PK
	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
    );

동일 DB 내 buy 테이블 생성

DROP TABLE IF EXISTS buy;
CREATE TABLE buy
(	num INT auto_increment NOT NULL PRIMARY KEY, #PK
	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)
    );

FOREIGN KEY(memid) REFERENCES member(mem_id) 해당 라인은 '이 테이블(buy)의 mem_id column을 member 테이블의 mem_id 열과 외래 키 관계로 연결한다' 의 의미. = FOREIGN KEY(열이름) REFERENCES 기준테이블(기준 테이블의 열이름)
REFERENCES : 참조

• 데이터 입력

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

구매 buy 테이블에 여기서

    INSERT INTO buy VALUES ( NULL, 'BLK', '지갑', 'NULL', 30, 2);
    INSERT INTO buy VALUES ( NULL, 'APN', '아이폰', '디지털', 200, 1);

두 라인을 추가 시, buy 테이블에 BLK라인은 정상적으로 들어가지만, member 테이블에 APN이 없기에 오류 메시지 (Error Code : 1452. Cannot add or update a child row : a foreign key constraint fails ) 발생.

Constraint 제약조건 : 데이터의 무결성 ( = 결함이 없음 )을 지키기 위해 제한하는 조건.

• PK Constraint : 중복 입력 값 불가 / NOT NULL / 1개만

ALTER TABLE member
ADD CONSTRAINT
PRIMARY KEY (mem_id);

위에 한것과 달리 PK가 없는 member 테이블에서 mem_id라는 PK를 추가하고 싶으면 위와 같이 ALTER TABLE을 통해 설정하며 된다.

• FK Constraint : PK-FK 관계를 잘 맺으면 됨. / FK 테이블이 참조하는 기준 테이블 (PK가 있는 테이블)의 해당 컬럼은 반드시 PK(기본 키)나 Unique(고유 키)로 설정되어야 함.

PK-FK 맺어진 후에는 PK의 열 이름이 변경되면 참조 테이브르이 데이터에 문제가 발생하여 기준 테이블의 열 이름이 변경 불가. 여기서 회원 테이블의 BLK가 PINK로 변경되면 자동으로 구매 buy 테이블의 BLK도 PINK로 변경하게 하는 문법이 ON UPDATE CASCADE or ON DELETE CASCADE.

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;

해당 update delete 문은 기존 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제되는 기능.

value를 위와 같이 넣고, 여기서 이제 회원 테이블의 BLK를 PINK로 변경하면,

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;

작성 시 실행 결과 Result Grid에서
PINK - 블랙핑크 - 지갑
PINK - 블랙핑크 - 맥북
으로 나온다.

참고)PK-FK 관계에서 PK 테이블 = 기준 테이블, FK 테이블 = 참조 테이블

• Unique (고유 키) Constraint : 중복되지 않은 유일한 값을 입력.
  PK와 제약조건이 유사한데 차이점은 Unique 제약조건은 NULL값을 허용하고, PK는 테이블에 1개만 설정해야 하지만 Unique는 여러 개 설정 가능.

• Check 체크 Constraint : 입력되는 데이터를 점검하는 기능을 함.
  height 키의 값이 음수가 되지 않게 하거나, 연락처는 010이나 02만 되게 하는 등 조건을 달 때 활용.

create table member
(~~~~,
height tinyint unsigned null CHECK (height >=100),
~~~~);

키는 반드시 100 이상의 값만 입력되게 설정. 테이블을 만든 후에 ATLER TABLE 문으로 제약조건 추가도 가능.

ATLER TABLE member
	ADD CONSTRAINT
    CHECK (phone1 IN ('02','031','032','054','055','061'));

해당 추가 제약조건은 저 6개 중 하나만 입력되게 설정한 것. 참고로 IN()은 괄호 값 안에 잇는 값 중 하나와 같아야 참이 됨.

• Default 기본값 정의 : 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법. height 값이 입력되지 않을 때 기본적으로 160으로 입력되도록 하고 싶으면

create table member
( ~~~,
height tinyint unsigned null DEFAULT 160,
~~~);

OR 

ALTER TABLE member
	ALTER COLUMN height SET DEFAULT 160;

이런 코드를 활용하면 된다.

위 사진을 따라오면 큰 문제 없이 의도를 이해할 수 있다.

뷰 View : 가상의 테이블로 DB 개체 중 하나.
단순 뷰 : 하나의 테이블과 연관된 뷰.
복합 뷰 : 2개 이상의 테이블과 연관된 뷰.

설명을 위해 혼공SQL 자료를 활용. 하단은 소스 코드.

이를 활용해서

use market_db;
select mem_id, mem_name, addr from member;

이와 같이 적으면

SELECT 문으로 실행해 나온 결과를 저 3개의 컬럼을 가진 테이블로 볼 수 있기에, 그래서 뷰의 실체가 SELECT문. 이 실행 결과를 vmember라고 부르고 테이블로 접근. 보통 뷰의 이름만 보고 뷰인지 알 수 있게 앞에 v 를 일반적으로 붙여줌.

< 뷰 생성 >

create view name_of_view
as
	select 문;

뷰를 만든 후에 접근방식은 테이블과 동일하게 select문 사용.

select column_name from name_of_view
	[WHERE 조건];

당연히 필요한 열만 보거나 조건식도 넣을 수 있음.

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

뷰의 장점 = 보안 + 단순화 : 인터넷 마켓 회원의 이름과 주소를 확인시키는 아르바이트를 시키려는데, 그렇다고 회원 테이블(member)에 접근시키면 중요한 개인정보를 모두 노출시키기에 필요한 정보만 보이는 뷰를 생성해 원래 테이블에 접근 권한을 주지 않고 뷰에만 작업 권한을 주면 보안사고를 막을 수 있음.

( concat 는 문자열 합치는 기능)
이 길고 복잡한 쿼리를 뷰로 생성하고 여기에만 접근하게 하면 복잡한 SQL을 입력할 필요가 없음.

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;

이후론 이 v_memberbuy를 테이블로 생각하고 접근하면 됨.

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

뷰 생성하며 사용할 열 이름을 수정 가능. 기존 배운 별칭을 사용하고 중간에 띄어쓰기를 하고, 뒤에 작은 or 큰따옴표로 묶어주고, 형식상 AS를 붙여줌(코드가 명확해보이는 장점). 단 뷰를 조회할 때는 위처럼 백틱 " ` " 으로 묶어줘야 함.
`

뷰의 수정은 ALTER VIEW, 뷰의 삭제는 DROP VIEW.

< 뷰의 정보 확인 >

create or replace view v_viewtest2
as
	select mem_id, mem_name, addr from member;

CREATE OR REPLACE VIEW : create view는 기존 뷰가 있으면 오류를 발생하지만, 상단 구문은 기존에 뷰가 있어도 덮어쓰는 효과를 보여 오류발생X. 즉 drop view 와 create view를 연속으로 작성한 효과.

describe v_viewtest2;

문으로 뷰의 정보 확인 가능.

show create view v_viewtest2;

문으로 뷰의 소스코드도 확인 가능.

update v_member set addr = '부산' where mem_id = 'BLK'; #데이터 수정
insert into v_member(mem_id,mem_name,addr) values ('BTS','방탄소년단','경기'); # 데이터 입력.

단, 여기서 v_member가 참조하는 member 테이블에서 mem_number 컬럼은 NN으로 설정되어 반드시 입력해야 함.
만약 v_member 뷰를 통해 member 테이블에 값을 입력하고 싶으면 v_member에 mem_number 열을 포함하도록 뷰를 재정의하거나, member 테이블에서 mem_number 열의 속성을 Null로 바꾸거나, 기본값 default를 지정해야 함.

create view v_height167
as
	select * from member where height>=167;
select * from v_height167;
#키가 167이상인 뷰를 생성하는 방법.

뷰를 통해 데이터를 입력할 때, 조건에 맞지 않는 데이터가 들어오는 것을 기본 조건으로 방지도 가능하지만, 추가적으로 예약어 with check option 을 통해 뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않게 할 수 있음.

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

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

이렇게 체크 옵션을 하면 체크옵션을 두기 전에는 결과가 1 rows affected로 잘 되는 것처럼 보이고 실제론 이 값이 167보다 작으니 안들어갔지만, 체크옵션을 활용하면 애초에 오류 메시지 Error Code 1369 를 통해 입력이 되지 않아 오해를 방지할 수 있다.

뷰가 참조하는 테이블을 삭제할 경우 뷰 또한 당연히 조회할 수 없다. 만약 뷰가 조회되지 않는다면 CHECK TABLE (check table v_height167;) 을 통해 뷰의 상태 확인가능.

4주차 기본미션 : p.226의 market_db의 회원 테이블(member) 생성하고 p.229 데이터 입력한 후 인증하기.