[23/08/07] SQL (3)

yeju·2023년 8월 7일
sql
0

SQL

목록 보기
3/3
post-thumbnail

📖 5. 테이블과 뷰

📌 5-1. 테이블 만들기

CREATE TABLE 테이블명 (
	컬럼1 타입[(길이)] [옵션],
    컬럼2 타입[(길이)] [옵션],
    ...
);

NOT NULL : 컬럼에 빈 값을 허용하지 않음
AUTO_INCREMENT : 컬럼 값이 1부터 자동 증가하도록 설정
PRIMARY KEY : 해당 컬럼을 기본 키로 지정
FOREIGN KEY : 해당 컬럼을 외래 키로 지정

FOREIGN KEY(지정할 컬럼명) REFERENCES 다른 테이블명(기본 키);
use naver_db;

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

create table buy (
	num int auto_increment primary key,
    mem_id char(8) not null,
    prod_name char(6) not null,
    group_name char(4),
    price int unsigned not null,
	amount smallint unsigned not null,
    foreign key(mem_id) references 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-08-08');
insert into member values('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015-01-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);

📌 5-2. 제약조건

제약 조건(constraint)이란?
데이터의 무결성을 지키기 위한 조건
테이블을 생성할 때 제약조건을 잘 설정해야 데이터의 오류가 적은 테이블을 만들 수 있음

1. 기본 키(Primary Key)

데이터를 구분할 수 있는 식별자
ex) 회원 테이블의 아이디, 학생 테이블의 학번 등

  • 기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없음
    (UNIQUE + NOT NULL)

  • 각 테이블은 기본 키를 1개만 가질 수 있음

  • 테이블 생성 시 컬럼 이름 뒤에 PRIMARY KEY를 붙이거나,
    마지막 행에 PRIMARY KEY(컬럼명) 추가

CREATE TABLE member (
	mem_id CHAR(8) PRIMARY KEY,
    ...
);

또는

CREATE TABLE member (
	mem_id CHAR(8),
    ...
    PRIMARY KEY(mem_id)
);
  • 이미 생성된 테이블에 기본 키를 추가할 때는 ALTER문 사용
ALTER TABLE member ADD CONSTRAINT PRIMARY KEY(mem_id);

2. 외래 키(Foreign Key)

다른 테이블의 기본 키에 있는 값을 참조하는 키

  • 두 테이블 사이의 관계를 연결해 데이터의 무결성을 보장함

  • 기본 키가 있는 테이블을 기준 테이블, 외래 키가 있는 테이블을 참조 테이블이라고 함

  • 테이블 생성 시 마지막 행에 FOREIGN KEY(컬럼명) REFERENCES 기준 테이블(참조할 기본키) 추가
    참조할 컬럼은 항상 PRIMARY KEY 또는 UNIQUE 여야 함

CREATE TABLE member (
	mem_id CHAR(8) PRIMARY KEY,
    ...
);

CREATE TABLE buy (
	mem_id CHAR(8),
    ...
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);
  • 이미 생성된 테이블에 외래 키를 추가할 때는 ALTER문 사용
ALTER TABLE buy ADD CONSTRAINT FOREIGN KEY(mem_id) REFERENCES member(mem_id);

💡 기본 키-외래 키로 맺어진 후에는 기준 테이블의 기본 키 값 중 참조 테이블에서 사용중인 값은 변경/삭제할 수 없음 (참조 테이블 데이터에 문제가 발생하기 때문)
두 테이블에서 함께 사용 중인 컬럼 값을 변경하고 싶다면 ON UPDATE CASCADE문 사용

3. 고유 키(Unique)

중복되지 않는 값을 입력하도록 하는 제약조건

  • 기본 키와 달리 NULL 값을 허용함
  • 기본 키와 달리 한 테이블에 여러 개 설정할 수 있음
  • NOT NULL을 함께 적용하면 기본 키와 동일한 제약 조건이 됨

4. 체크(Check)

입력되는 데이터를 점검하는 제약조건

  • 테이블을 생성할 때 CHECK(조건)을 추가하면 조건 범위에 벗어나는 값은 입력되지 않음
CREATE TABLE member (
	mem_id CHAR(8) PRIMARY KEY,
    mem_name VARCHAR(10) NOT NULL,
    -- 키가 100 이상인 경우만 데이터 추가 가능
    height TINYINT UNSIGNED CHECK(height >= 100),
	phone1 CHAR(3)
);
  • 이미 생성된 테이블에 추가할 때는 ALTER문 사용
ALTER TABLE member
ADD CONSTRAINT CHECK(phone1 IN ('02','031','032','054','055','061'));
-- IN() 안에 해당하는 값만 phone1 컬럼 값으로 추가하도록 함

5. 기본값(Default)

데이터 추가 시 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정하는 것

  • 테이블을 생성할 때 DEFAULT 값을 추가
CREATE TABLE member (
	mem_id CHAR(8) PRIMARY KEY,
    mem_name VARCHAR(10) NOT NULL,
    -- 값을 입력하지 않으면 기본값을 160으로 입력
    height TINYINT UNSIGNED DEFAULT 160,
	phone1 CHAR(3)
);
  • 이미 생성된 테이블에 추가할 때는 ALTER문 사용
ALTER TABLE member ALTER COLUMN phone1 SET DEFAULT '02';

INSERT INTO member VALUES('SPC', '우주소녀', default, default);
-- height는 160, phone1은 '02'로 기본값이 입력됨

📌 5-3. 가상의 테이블: 뷰

뷰(View)는 실제 테이블에서 생성한 가상의 테이블을 뜻함

바탕 화면의 바로 가기 아이콘처럼, 사용자가 뷰에 접근하면 뷰가 연결된 테이블에 사용자가 요청한 내용을 전달하고 요청에 대한 결과를 전달한다. 사용자는 뷰와 테이블을 동일한 개체로 인식한다.

1. 뷰의 기본 생성

CREATE VIEW 뷰_이름 AS SELECT ~;
-- SELECT 된 내용을 뷰로 생성

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 ('서울','경기');


뷰를 만든 후에는 테이블과 동일하게 사용할 수 있음

2. 뷰를 사용하는 이유

  • 보안에 유리함
    전체 테이블에서 중요한 정보는 접근하지 못하도록 제한하고 필요한 내용만 뷰에서 공개할 수 있음

  • 복잡한 SQL을 단순하게 사용 가능
    복잡한 SQL을 사용해 얻는 테이블을 뷰로 생성해 놓고 사용하면 같은 SQL을 여러 번 사용하지 않고도 해당 데이터에 접근할 수 있음

3. 뷰의 실제 작동

뷰를 생성할 때 컬럼명에 별칭을 사용하는 것을 권장함
조회할 때 컬럼명에 공백이 있으면 백틱 `` 으로 묶어주기

CREATE VIEW v_viewtest1
AS SELECT B.mem_id 'Member ID', M.mem_name 'Member Name', B.prod_name 'Product Name', CONCAT(M.phone1, M.phone2) 'Office Phone'
    FROM buy B
		INNER JOIN member M
        ON B.mem_id = M.mem_id;
-- buy(구매) 테이블을 기준으로 
-- 구매 기록이 있는 멤버 join해 뷰 생성

SELECT * FROM v_viewtest1;
-- SELECT DISTINCT `Member ID`, `Member Name` FROM v_viewtest1;

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

UPDATE v_member SET addr = '부산' WHERE mem_id = 'BLK';
-- 원래 테이블에도 해당 내용이 반영됨

INSERT INTO v_member(mem_id, mem_name, addr) VALUES('SVT','세븐틴','경기');
-- INSERT 불가능

DROP TABLE IF EXISTS buy, member;
-- 뷰가 참조하는 테이블은 뷰가 존재하더라도 삭제는 가능함

뷰에서 데이터를 수정/삭제하면 해당 뷰를 가져온 원래 테이블에 함께 반영됨
위의 INSERT문에서는 해당 뷰에 not null 컬럼값(mem_number)이 없기 때문에 v_member 뷰를 통한 insert가 불가능함

5. 뷰를 통한 데이터의 입력

CREATE VIEW v_height167
AS SELECT * FROM member WHERE height >= 167;
-- 평균키 167 이상인 그룹만 뷰로 생성

INSERT INTO v_height167 VALUES('TRA','티아라',6,'서울',null,null,159,'2005-01-01');

v_height167 뷰에서 평균키 167보다 작은 그룹을 INSERT하면, 테이블에 추가는 되지만 해당 뷰에 나타나지 않으므로 논리적으로 맞지 않음
이러한 상황을 막기 위해 뷰를 설정할 때 예약어 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');
-- 평균키 167 미만이므로 INSERT 불가능
profile
yeju
🌱
이전 포스트

[23/08/03] SQL (2)

0개의 댓글