Search

Chapter 05. 테이블과 뷰

#혼공학습단 #혼공 #혼자공부하는SQL #혼공S
본 글은 2023년 7월 3일 ~ 2023년 8월 20일까지 진행하는 혼공학습단에 참여하며 정리한 글입니다.
이번 장에서는 테이블을 만들고 그 테이블에 제약조건을 거는 방법, 그리고 뷰를 이용한 SQL 활용에 대해 알아본다.

05-1 테이블 만들기

이 챕터에서는 MySQL에서 사용하는 테이블 형식과 그 만드는 방법에 대해 알아본다. 테이블(table)은 표 형태로 구성된 2차원 구조이다. 테이블은 행과 열로 구성되는데, 행은 로우(row), 레코드(record)라 부른다. 열은 컬럼(column), 필드(field)라고 부른다.

데이터베이스와 테이블 설계하기

데이터베이스는 각 컬럼에 적합한 데이터 형식을 지정하면서 정해진다.
아이디 형식을 기본 키 (primary key) 및 외래 키(foreign key)로 지정한다.

데이터베이스 생성하기

DROP DATABASE IF EXISTS naver_db; CREATE DATABASE naver_db;
SQL
복사

테이블 생성하기

CREATE TABLE 'naver_db'.'member' ( 'mem_id' CHAR(8) NOT NULL, '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, PRIMARY KEY ('mem_id')); )
SQL
복사
DROP TABLE IF EXISTS buy; CREATE TABLE naver_db.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 )
SQL
복사
AUTO_INCREMENT로 지정한 열은 PRIMARY KEY나 UNIQUE로 꼭 지정해야한다!

05-2 제약조건으로 테이블을 견고하게

이번 장에서는 테이블에 기본키, 외래키와 같은 제약조건을 설정함으로써 데이터의 오류를 줄여 완전 무결한 DB 를 만드는 방법에 대해 살펴본다.
기본키 : 아이디, 학번, 사번 등과 같은 고유한 번호를 의미하는 열
외래 키 : 기본 키와 연결되는 열

제약 조건의 기본 개념과 종류

제약조건(constraint)은 데이터의 무결성을 지키기 위해 제한하는 조건이다. 데이터의 무결성이란 데이터에 결함이 없음을 의미한다.
제약조건에는 다음과 같음 예들이 있다.
PRIMARY KEY
FORIEGN KEY
UNIQUE
CHECK
DEFAULT
NULL
기본 키(PRIMARY KEY) 제약조건
기본 키는 중복될 수 없다.
NULL값이 입력될 수 없다.
테이블은 기본 키를 1개만 가질 수 있다.
기본 키 설정방법
-- 방법 1. CREATE TABLE member ( mem_id CHAR(8) NOT NULL PRIMARY KEY, mem_name VARCHAR(10) NOT NULL, height TINYINT UNSIGNED NULL ); -- 방법 2. CREATE TABLE member ( mem_id CHAR(8) NOT NULL, mem_name VARCHAR(10) NOT NULL, height TINYINT UNSIGNED NULL, PRIMARY KEY (mem_id) ); -- 방법 3. CREATE TABLE member ( mem_id CHAR(8) NOT NULL, mem_name VARCHAR(10) NOT NULL, height TINYINT UNSIGNED NULL, ); ALTER TABLE member ADD CONSTRAINT PRIMARY KEY (mem_id);
SQL
복사
외래 키(FOREIGN KEY) 제약조건
외래 키는 두 테이블 사이의 관계를 연결
외래 키가 설정된 열은 꼭 다른 테이블의 기본키와 연결
기본 키가 있는 테이블을 기준 테이블이라 하며, 외래 키가 있는 테이블을 참조 테이블이라 한다.
외래 키 설정 방법
-- 방법 1. CREATE TABLE buy ( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, mem_id CHAR(8) NOT NULL, prod_name CHAR(6) NOT NULL, FOREIGN KEY(mem_id) REFERENCES member(mem_id) ); -- 방법 2. 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);
SQL
복사
고유 키(UNIQUE) 제약조건
중복되지 않는 유일한 값을 입력해야하는 조건
기본 키와 비슷하지만 고유 키 제약조건은 NULL 을 허용
NULL은 여러 개가 되어도 상관없음
외래 키 설정방법
CREATE TABLE member ( mem_id CHAR(7) NOT NULL PRIMARY KEY, mem_name VARCHAR(10) NOT NULL, height TINYINT UNSIGNED NULL, email CHAR(30) NULL UNIQUE );
SQL
복사
체크 (CHECK) 제약조건
입력되는 데이터를 점검하는 기능
조건을 주어 해당 조건에 부합하는지 확인하는 기능이다
체크 설정방법
CREATE TABLE member ( mem_id CHAR(8) NOT NULL PRIMARY KEY, mem_name VARCHAR(10) NOT NULL, height TINYINT UNSIGNED NULL CHECK (height >=100), phone1 CHAR(3) NULL );
SQL
복사
기본값(DEFAULT) 정의
기본값 정의는 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법
기본값 설정방법
CREATE TABLE member ( mem_id CHAR(8) NOT NULL PRIMARY KEY, mem_name VARCHAR(10) NOT NULL, height TINYINT UNSIGNED NULL DEFAULT 160, phone1 CHAR(3) NULL );
SQL
복사

05-3 가상의 테이블: 뷰

이번 장에서는 가상의 테이블인 뷰에 대해 살펴본다. 가상의 테이블이기에 테이블과 데이터를 확인하는 측면에서는 다르지 않지만 왜 뷰를 사용하는지에 대해 살펴본다.
뷰는 데이터베이스 개체 중 하나.
뷰는 테이블처럼 데이터를 가지고 있지는 않음.
뷰는 단순 뷰와 복합 뷰로 나뉨
단순 뷰 : 하나의 테이블과 연관된 뷰
복합 뷰 : 2개 이상의 테이블과 연관된 뷰

뷰의 생성

CREATE VIEW 뷰_이름 AS SELECT; -- 예시 CREATE VIEW v_member AS SELECT mem_id, mem_name, addr FROM member; -- 뷰의 활용 SELECT mem_name, addr FROM v_member WHERE addr IN ('서울', '경기');
SQL
복사

뷰를 사용하는 이유

1.
보안에 도움이 된다.
2.
복잡한 SQL을 단순하게 만들 수 있다
복잡한 SQL문을 미리 VIEW로 만들어 놓고, 우리는 단순하게 WHERE 만 사용해서 확인할 수 있다.

[혼공학습단 10기 미션]

기본미션

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

선택미션

p. 271 확인문제 4번 풀고 인증하기
Question. 다음은 기존에 뷰가 있으면 덮어쓰고, 없으면 새로 생성하는 SQL이다. 빈칸에 들어갈 내용을 고르자.
CREATE OR OVERWRITE VIEW 뷰_이름 AS SELECT;
SQL
복사