#혼공학습단 #혼공 #혼자공부하는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
복사