기본 미션: p. 226의 market_db의 회원 테이블(member) 생성하고, p. 229 데이터 입력한 후 인증하기
선택 미션: p. 271 확인 문제 4번 풀고 인증하기
GUI환경에서 테이블 만들기
데이터베이스 생성
- 만약 데이터베이스를 잘못만들었을경우 DROP DATABASE naver_db를 입력해주면 됨
테이블 생성
회원테이블과 구매테이블 작성해줌
순번은 자동 증가를 위해서 AI로 지정
데이터 입력
회원테이블과 구매테이블의 데이터를 작성해주는데
구매 테이블의 mem_id는 반드시 회원테이블의 mem_id로 존재해야하기 때문에 오류가 남
SQL로 테이블 만들기
데이터베이스 생성
앞에서 사용한 naver_db를 삭제후 다시 생성
테이블 생성
- NULL = 빈 값 허용, NOT NULL = 반드시 값을 넣어야 함
- 기본키로 설정하기 위해 PRIMARY KEY문을 붙여줌 (NOT NULL을 생략해도 NOT NULL로 취급함)
- AUTO_INCREMENT로 지정한 열은 PRIMARY KEY나 UNIQUE로 꼭 지정해야함
- 이 테이블의 mem_id열은 혼자 존재하는 것이 아니라 꼭 member 테이블의 mem_id에도 값이 있어야 함
데이터 입력
APN이 아직 회원테이블에 존재하지 않아서 워크벤치에서 진행했던 것과 같이 오류가 발생함
기본 키 제약조건
- 데이터를 구분할 수 있는 식별자를 기본 키라고 부름
- 기본키에 입력되는 값은 중복될 수 없으며, NULL값이 입력될 수 없음
CREATE TABLE에서 설정하는 기본 키 제약조건
- 열 이름 위에 PRIMARY KEY를 붙여주면 기본키로 설정됨
회원아이디는 회원테이블의 기본키가 되었고, 앞으로 입력되는 회원 아이디는 중복될 수 없고, 비어있을수도 없음
제일 마지막행에 PRIMARY KEY(mem_id)를 추가해도 기본 키를 지정할 수 있음
ALTER TABLE에서 설정하는 기본 키 제약조건
제약조건을 설정하는 또 다른 방법으로, 이미 만들어진 테이블을 수정하는 ALTER TABLE구문을 사용할 수 있음
외래 키 제약조건
- 외래 키 제약조건은 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 함
- 기본 키가 있는 회원테이블을 기준 테이블이라고 부르고, 외래 키가 있는 구매 테이블을 참조 테이블이라고 부름
CREATE TABLE에서 설정하는 외래 키 제약조건
- CREATE TABLE 끝에 FOREIGN KEY 키워드를 설정하면 외래 키가 생성됨
- 외래 키의 형식은 FOREIGN KEY(열 이름) REFERENCES 기준_테이블(열 이름)
ALTER TABLE에서 설정하는 외래 키 제약조건
기준테이블의 열이 변경될 경우
내부 조인을 사용해서 물품 및 사용자 정보 확인
BLK의 아이디를 PINK로 변경할 경우 오류가 발생함
이럴때 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제되는 기능인 ON UPDATE CASCADE문이 필요함
이제 PINK로 아이디를 변경해도 제대로 출력되는 것을 볼 수 있음
기타 제약조건
고유 키 제약조건
- 중복되지 않는 유일한 값을 입력해야하는 조건
- 고유키는 중복은 허용하지 않지만 비어 있는 값은 허용함
체크 제약조건
- 입력되는 데이터를 점검하는 기능
- 열의 정의 뒤에 조건만 추가해주면 됨
기본값 정의
- 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해놓는 방법
널 값 허용
- NULL 값은 '아무것도 없다'라는 의미임 공백이나 0과는 다름
뷰의 개념
뷰의 기본 생성
뷰를 만드는 형식
CREATE VIEW 뷰_이름
AS
SELECT 문;뷰를 만든 후에 뷰에 접근하는 방식은 테이블과 동일하게 SELECT문을 사용함
필요하다면 조건식도 테이블과 동일하게 사용가능
뷰의 작동
- 사용자는 뷰를 테이블이라고 생각하고 접근
- 뷰는 기본적으로 읽기 전용으로 사용되지만, 뷰를 통해서 원본 테이블의 데이터를 수정할 수도 있음
뷰를 사용하는 이유
-
보안에 도움이 됨
예를들어, 아이디, 이름, 주소만 보이는 뷰를 생성해서 회원 테이블에 접근하지 못하도록 권한을 제한하고, 뷰에만 접근할 수 있도록 권한을 줄 수 있음 -
복잡한 SQL을 단순하게 만들 수 있음
SQL을 뷰로 생성해놓고 사용자들은 해당 뷰에만 접근하도록 하면 복잡한 SQL을 입력할 필요가 없어짐
뷰의 실제 작동
뷰의 실제 생성, 수정, 삭제
- 뷰에서 사용될 열 이름을 테이블과 다르게 지정할 수 있음
- 뷰를 조회할 때 열 이름에 공백이 있다면 백틱( ` ) 으로 묶어줘야 함
뷰의 수정은 ALTER VIEW구문을 사용하며 열 이름에 한글을 사용해도 됨
뷰의 삭제는 DROP VIEW를 사용함
뷰의 정보 확인
DESCRIBE문으로 기존 뷰의 정보 확인 가능
뷰를 통한 데이터의 수정 / 삭제
v_member가 참조하는 member의 열중에서 mem_number열은 NOT NULL로 설정되어서 반드시 입력해줘야 함
하지만 v_number에서는 mem_number열을 참조하고 있지 않아 값을 입력할 방법이 없음
만약 v_member 뷰를 통해서 테이블에 값을 입력하고 싶다면
- v_number에 mem_number 열을 포함하도록 뷰를 재정의
- member에서 mem_number열의 속성을 NULL로 바꾸거나 기본값 재정의
를 해야함
뷰를 통한 데이터의 입력
- 예약어 WITH CHECK OPTION = 뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않도록 함
뷰가 참조하는 테이블의 삭제
여러개의 뷰가 두 테이블과 관련되어 있는데도 쉽게 삭제됨
CHECK TABLE문으로 뷰의 상태를 확인할 수 있는데 뷰가 참조하는 테이블이 없어서 오류가 발생함
