[혼공SQL] 4주차 - Ch5 테이블과 뷰

cup-wan·2024년 1월 28일
0

벌써 절반이나 온 혼공학습단 c H e E r s. . .🍷 남은 절반도 다들 화이팅!

이번 4주차는 유독 책에 나온 내용 외의 개념 정리가 많습니다! 관심 있으신 분들은 (//TODO: 링크 다음 포스트)를 읽어주시고 태클은 언제든 환영입니다🙃 (1/29 업데이트 예정 ㅠㅠ)

1) 테이블 만들기

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

지금까지 사용한 market_db를 돌아보는 시간

열 이름(한글)열 이름(영문)데이터 형식Not Null기타
아이디mem_idCHAR(8)Y기본 키(PK)
회원 이름mem_nameVARCHAR(10)Y
인원수mem_numberTINYINTY
주소addrCHAR(2)Y
연락처 국번phone1CHAR(3)N
전화번호phone2CHAR(8)N
평균 키heightTINYINTNUNSIGNED
데뷔 일자debut_dateDATEN
  • 아이디를 기본키로 설정
  • 각 열에 적합한 데이터 형식 지정
    • 평균 키 = 양수만 가능 = UNSIGNED
    • 전화번호 = 앞에 0 가능성 = CHAR
    • 회원 이름 = 고정 X = VARCHAR
  • NULL 허용 여부

위와 같은 과정이 DB 설계다!


GUI 환경에서 테이블 만들기

데이터베이스 생성하기

  1. MySQL Workbench 실행 후 새 쿼리창 실행
  2. CREATE DATABASE naver_db;	

실행했지만 SCHEMAS 패널에는 바로 나타나지 않음

  1. [SCHEMAS] 패널의 빈 곳에 Refresh All 선택

naver_db가 생성된 것을 확인할 수 있다

테이블 생성

Ch02 에서 정리한 것과 똑같이 진행

💡 Table 생성 시 제약 조건

  • PK : Primary Key, 중복이나 null값 X
  • NN : Not Null, null값 X
  • UQ : Unique, 중복 X
  • B : 데이터를 이진 문자열로 저장
  • UN : Unsigned, 음수 범위 X
  • ZF : Zero Filled, 컬럼 크기보다 작은 값을 넣으면 남은 공간에 0을 채운 뒤 삽입
  • AI : Auto Increment, 1씩 자동 증가
  • G : Generated column, 다른 열을 기반으로 한 수식으로 생성된 값

💡 GUI에서는 기본 키 - 외래 키 관계 선택이 불가능

따라서 Table 생성 시 쿼리를 다음과 같이 수정해야함

외래키가 잘 설정된 걸 확인하면 끝!

데이터 입력하기

데이터 입력 역시 Ch02 에서 정리한 것과 똑같이 진행

💡 기본키 - 외래키 연결 시 주의사항

기본키에 APN를 생성하지 않음

외래키에 APN을 소환해버림...

친절하게 왜 소환에 실패했는지 알려주는 모습 Workbench는 짱이다

기본키 추가 or 문제되는 외래키 행을 삭제하자 잘 작동한다.

➡️ 기본키, 외래키가 설정된 경우 주의해야함!


SQL로 테이블 만들기

앞서 GUI로 만든 테이블을 SQL로 만드는 과정이다
MySQL Workbench로 테이블을 만드는 방법은 다른 GUI 사용 시 사용 방법이 다를 수 있지만 SQL로 만드는 과정은 모두 동일하게 적용 가능하다!

데이터베이스 생성하기

DROP DATABASE IF EXISTS naver_db;
CREATE DATABASE naver_db;

이전에 만든 naver_db 삭제 후 다시 생성하는 과정

테이블 생성하기

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,
  phone1		CHAR(3),
  phone2		CHAR(8),
  height		TINYINT UNSIGNED,
  debut_date	DATE)

  • NOT NULL 안 쓰면 NULL 자동 입력 (디폴트 = null)
  • primary key 같은 경우 마지막에 PRIMARY KEY (mem_id) 를 추가해서 명시 가능

데이터 입력하기

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016-8-8');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '11111111', 166, '2015-1-15');
  • 위와 똑같이 진행됨
  • 기본키 - 외래키 설정에 맞게 입력하지 않으면 에러 발생
  • GUI로 INSERT한 것과 마찬가지로 진행하면 해결 가능

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

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

제약이 있는 것이 좋은 프로그래밍이다.

김영한 선생님 강의를 들으면 항상 하시는 말씀이시다. DB 또한 마찬가지다. DB에서의 제약조건은 데이터의 무결성을 지키기 위함이다.

  • 데이터의 무결성 : 데이터의 생명 주기(Life Cycle) 동안 결함이 없다는 의미
  • MySQL이 제공하는 제약조건
    • PRIMARY KEY
    • FOREIGN KEY
    • UNIQUE
    • CHECK
    • DEFAULT 정의
    • NULL 값 허용

기본 키, 외래 키 제약 조건

  • 기준 테이블 : 기본키 - 외래키 설정된 테이블 중 기본키가 있는 테이블
  • 참조 테이블 : 기본키 - 외래키 설정된 테이블 중 기본키가 있는 테이블

제약 설정 방법

  • CREATE TABLE 마지막 줄에 PRIMARY or FOREIGN KEY (~~) 추가
    • PRIMARY KEY는 원하는 열 마지막에 PRIMARY KEY 추가로도 가능
  • ALTER TABLE 구문작성
    • ADD CONSTRAINT : 제약조건 추가
    • PRIMARY or FOREIGN KEY (~~~) 로 추가

주의할 점

  • 기본키와 외래키가 설정되면 열을 수정, 삭제가 안된다.
  • 해결방법
    • ON UPDATE CASCADE : 기준 테이블과 참조 테이블 수정 동시 실행
    • ON DELETE CASCADE : 기준 테이블과 참조 테이블 삭제 동시 실행

기타 제약 조건

  • UNIQUE : 중복 값 X
  • CHECK : 생성 전에 조건에 부합하는지 확인
  • 기본값 정의 : DEFAULT 값을 설정해 비워두면 DEFAULT 값이 적용됨
  • NULL값 허용 : NULL을 사용하지 않으면 NOT NULL 사용하려면 생략(DEFAULT)

3) 가상의 테이블 : 뷰

뷰의 개념

뷰의 정체는 - SELECT 문

  • 뷰는 데이터 베이스의 객체이다. (SELECT문)
  • 왜 쓰나요?
    • 보안을 위해
    • 단순화를 위해
  • 뷰 생성 방법
CREATE VIEW 뷰_이름
AS
	SELECT;


뷰의 실제 작동

기본 생성, 수정, 조회, 삭제

  • 뷰 조회 시 열 이름에 공백 있으면 ` `(백틱)으로 묶어줘야 함
  • 수정 : ALTER VIEW
  • 삭제 : DROP VIEW
  • 정보 조회 : DESCRIBE
  • 뷰 코드 조회 : SHOW CREATE VIEW

뷰를 통한 데이터 접근

  • WITH CHECK OPTION : 조건이 있는 데이터를 입력해야할 때 조건에 부합한지 확인하는 용도
  • 뷰는 기존 테이블의 SELECT문이 정체이기 때문에 기존 테이블이 삭제되면 뷰는 작동하지 않는다(조회가 불가능)
    • 이런 경우 CHECK TABLE 뷰_이름 을 통해 왜 안되는지 확인할 수 있다

4) 미션

기본 미션

업로드중..

선택 미션

뷰 생성 = CREATE
기존에 있는 뷰 덮어쓰기 = REPLACE

답) 2

profile
아무것도 안해서 유죄 판결 받음

0개의 댓글