[혼공S] 4주차_CH5

Ch5. 테이블과 뷰

5.1 테이블 만들기

• 데이터베이스와 테이블을 설계하기 위해서는 테이블 이름, 열이름, 데이터 형식, 기본키, 외래키 등을 설정해야 한다.

(1) 데이터베이스 생성

DROP DATABASE IF EXISTS database_name;
CREATE DATABASE database_name;

한 후 [SCHEMAS]패널의 빈 곳에서 마우스 오른쪽 클릭 후 [Refresh All] 선택

(2) 테이블 생성

USE database_name; -- 만든 데이터베이스에서
DROP TABLE IF EXISTS member; -- 만약 member 테이블있으면 버려
CREATE TABLE member -- 그러고 member이라는 테이블 만들어
(mem_id CHAR(8) NOT NULL PRIMARY KEY, -- 열이름 데이터형식 NULL여부 PK
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
);

✔️ 기본적으로 NULL로 설정되어 있으므로 NULL/NOT NULL은 직접 지정할 것. PK는 NOT NULL안됨!
✔️ PK 지정
💥 주의 : AUTO_INCREMENT로 지정한 열은 PK나 UNIQUE로 꼭 지정해야함!!!!
✔️ FOREIGN KEY 지정은 마지막 열 다음에 FOREIGN KEY(열이름) REFERENCES 참조하는테이블이름(열이름)

DROP TABLE IF EXISTS buy;
CREATE TABLE 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,
FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

(3) 데이터 입력하기

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', '33333333', 166, '2015-1-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);

👆🏻 현재 member 테이블에 'APN'이 입력되지 않아서 오류가 남! 참조하려는 테이블의 해당 열에도 값이 있어야 데이터가 입력됨.

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

• 제약조건 : 데이터의 오류가 적고 튼튼해지도록 제한하는 조건

(1) 기본키(PK) 제약조건
기본키(PRIMARY KEY) : 데이터를 구분할 수 있는 식별자
ex) 아이디, 학번, 사번

💥 주의) 기본키 - 외래키 관계로 연결된 테이블은 외래키가 설정된 테이블을 먼저 삭제할 것!
💥 주의) 테이블을 수정하는 ALTER TABLE에서 설정하는 기본 키 제약조건

• CREATE TABLE에서 설정하는 기본키 제약조건 :
  열이름 데이터형식 NULL여부 PK설정

  USE database_name;
  DROP TABLE IF EXISTS buy, member;
  CREATE TABLE member
  (mem_id CHAR(8) NOT NULL PRIMARY KEY,
  mem_name VARCHAR(10) NOT NULL,
  height TINYINT UNSIGNED NULL
  );

• ALTER TABLE에서 설정하는 기본키 제약조건

  DROP TABLE IF EXISTS member;
  CREATE TABLE member
  (mem_id CHAR(8) NOT NULL,
  mem_name VARCHAR(10) NOT NULL,
  height TINYINT UNSIGNED NULL
  );
  ALTER TABLE member -- member 테이블 변경
  ADD CONSTRAINT -- 제약조건 추가
  PRIMARY KEY(mem_id); -- mem_id열을 기본키로

확인은

DESCRIBE 테이블이름;

으로!

(2) 외래키(FK) 제약조건
외래키(FOREIGN KEY) : 두 테이블 사이의 관계를 연결해주고, 다른 테이블의 기본키와 연결됨.
- 기본키가 있는 테이블이 기준테이블, 외래키가 있는 테이블이 참조테이블
- 기본키와 외래키가 관계를 맺으면 오류가 없는 데이터가 됨.
- (참조테이블에서 참고하는) 기준테이블의 열은 반드시 기본키(PK)나 고유키(Unique)로 설정돼야함.

• CREATE TABLE에서 설정하는 외래키 제약조건 :
  FOREIGN KEY(열이름) REFERENCES 기준테이블이름(열이름)

기준테이블 먼저 만들고

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL
);

참조테이블에 FK설정

CREATE TABLE buy
(num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
user_id CHAR(8) NOT NULL,
prod_name CHAR(6) NOT NULL,
FOREIGN KEY(user_id) REFERENCES member(mem_id)
);

👆🏻 참조테이블의 외래키로 설정할 열이름과 기준테이블의 기본키로 설정한 열이름은 달라도 됨.

• ALTER TABLE에서 설정하는 외래키 제약조건

  DROP TABLE IF EXISTS buy;
  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 -- buy 테이블을 수정
  ADD CONSTRAINT -- 제약조건 추가
  FOREIGN KEY(mem_id) -- buy테이블의 mem_id를 FK로
  REFERENCES member(mem_id) -- member테이블의 mem_id열 참조

💥 기준 테이블의 열이 변경될 경우
1) ON UPDATE CASCADE : 기준 테이블의 열이름이 변경될 때 참조 테이블의 열이름도 자동으로 변경

2) ON DELETE CASCADE : 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제

DROP TABLE IF EXISTS buy;
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)
ON UPDATE CASCADE
ON DELETE CASCADE;

• 데이터 입력

  INSERT INTO member VALUES('BLK', '블랙핑크', 163);
  INSERT INTO buy VALUES(NULL, 'BLK', '지갑');
  INSERT INTO buy VALUES(NULL, 'BLK', '맥북');

• member테이블의 BLK를 PINK로 변경

  UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';

• 내부 조인을 이용해서 확인

  SELECT M.mem_id, M.mem_name, B.prod_name
  FROM buy B
  INNER JOIN member M
  ON M.mem_id = B.mem_id;

• PINK를 기준 테이블에서 삭제

  DELETE FROM member WHERE mem_id = 'PINK';

• buy테이블 확인

  SELECT * FROM buy;

👆🏻 기준테이블의 PINK를 삭제해서 참조테이블의 PINK도 모두 삭제됨.

(3) 고유키(UNIQUE) 제약조건
고유키(UNIQUE) : 중복되지않는 유일한 값을 입력해야 하는 조건
- 테이블에 여러개 설정가능
- NULL값이 여러개여도 허용
즉, 중복은 허용하지 않지만, 비어있는 값은 허용.

(4) 체크 제약조건
체크(CHECK) : 입력되는 데이터를 점검하는 기능.
- 열이름 데이터형식 NULL여부 등등 적고 맨 뒤에 CHECK(조건)
- 만약, 조건에 위배되는 값을 입력하면 오류 발생

DROP TABLE IF EXISTS member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL CHECK(height>=100), -- 평균키가 100이상인 값만 입력되도록!
phone1 CHAR(3) NULL
);

(5) 기본값 정의 :
값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법.
ex) 키를 입력하지 않았지만 기본적으로 160으로 입력되도록

DROP TABLE IF EXISTS member;
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
);

ALTER TALBE사용시 열에 DEFAULT를 지정

ALTER TABLE member
ALTER COLUMN phone1 SET DEFAULT '02';

기본값이 설정된 열에 기본값을 입력하려면? default로 데이터 입력!

INSERT INTO member VALUES('RED', '레드벨벳', 161, '054');
INSERT INTO member VALUES('SPC', '우주소녀', default, default);
SELECT * FROM member;

(6) NULL값 허용
- NULL은 공백(' ')이나 0과는 다름.
- PRIMARY KEY가 설정된 열에는 NULL값이 있을 수 없다.

5.3 가상의 테이블 : 뷰

뷰 : 데이터베이스 개체 중 하나로, 테이블처럼 데이터를 갖고 있지는 않지만 SELECT가 실행되고 그 결과가 화면에 출력되는 방식

뷰 종류	설명
단순뷰	하나의 테이블과 연관된 뷰
복합뷰	2개 이상의 테이블과 연관된 뷰, 읽기전용이라 데이터 입력/수정/삭제 불가

💡뷰의 이름만 보고도 뷰인지 알 수 있도록 'v_이름'으로 명명.

CREATE VIEW v_이름
AS
SELECT 뷰에 넣을 열이름, 열이름, 열이름;

이때, 필요한 열만 출력하거나 조건식을 만족하는 데이터만도 출력하도록 할 수 있음.

🌟뷰를 왜 사용???
1️⃣ 중요한 개인 정보에는 접근하지 못하도록 사용자별 권한제한이 가능하여 보안에 도움이 된다.
2️⃣ INNER JOIN이나 GROUP BY 등을 포함한 복잡한 쿼리를 미리 정의해두면 이후에는 간단한 SELECT만 실행하여 복잡한 SQL을 단순하게 만들 수 있다.

• 뷰의 실제 작동
  1) 뷰의 생성은 CREATE VIEW로 하며, 뷰에서 사용될 별칭 지정은 열이름 뒤에 큰 따옴표나 작은 따옴표로 묶어주고 띄어쓰기 포함가능. 형식상 AS도 붙여줌.

  USE market_db;
  CREATE VIEW v_viewtest1 -- 뷰 만들기
  AS
  SELECT B.mem_id 'Member ID', M.mem_name AS 'Member Name',
  B.prod_name "Product Name",
  CONCAT(M.phone1, M.phone2) AS "Office Phone" -- 두 열 묶어줌.
  FROM buy B
  INNER JOIN member M -- 내부 조인
  ON B.mem_id = M.mem_id;
  SELECT DISTINCT Member ID, Member Name FROM v_viewtest1; -- 내부 조인 후 새로운 열이름에 공백이 있어서 백틱(`)로 묶어줌. DISTINCT있으니까 중복없이 출력

2) 뷰의 수정은 ALTER VIEW

ALTER VIEW v_viewtest1
AS
SELECT B.mem_id '회원 아이디', M.mem_name AS '회원 이름',
B.prod_name '제품 이름',
CONCAT(M.phone1, M.phone2) AS '연락처' -- 열이름 모두 바꿈.
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
SELECT DISTINCT 회원 아이디, 회원 이름 FROM v_viewtest1;

3) 뷰의 삭제는 DROP VIEW

DROP VIEW v_viewtest1;

4) 새로운 뷰를 생성할 때, DROP VIEW부터 하고 CREATE VIEW를 하는데 이걸 CREATE OR REPLACE VIEW로 한 번에 가능!!!!!!🤩

USE market_db;
CREATE OR REPLACE VIEW v_viewtest2
AS
SELECT mem_id, mem_name, addr FROM member;

5) 뷰의 정보확인은 DESCRIBE VIEW

DESCRIBE 뷰이름;

💥 주의) 테이블과는 달리, PK 등의 정보는 확인되지 않음.

6) 뷰의 소스코드 확인은 SHOW CREATE VIEW

SHOW CREATE VIEW 뷰이름;

7) 뷰를 통한 데이터의 수정 및 삭제
1️⃣ 데이터 수정

UPDATE 뷰이름 SET addr = '부산' WHERE mem_id = 'BLK';

mem_id가 BLK인 열의 addr을 모두 부산으로

2️⃣ 데이터 입력
💥 주의) 뷰를 통해 데이터를 입력하려면 뷰가 참조하는 테이블 열에 NOT NULL이 없어야함.
💥 주의) 복합뷰는 데이터 입력/삭제/수정 불가.
👉🏻 뷰가 mem_number 열을 참조하지 않더라도 NOT NULL로 설정되어 있으면 데이터를 반드시 입력해줘야하는데 뷰에서는 해당 열을 참조하지 않아서 입력할 수가 없음!!!!
∴ 뷰가 mem_number열을 포함하도록 수정/ 테이블에서 mem_number 열의 속성을 NULL로 수정하거나 기본값(default)을 지정

평균키가 167이상인 뷰 생성

CREATE VIEW v_height167
AS
SELECT FROM member WHERE height >= 167;
SELECT FROM v_height167;

167미만인 데이터 삭제

DELETE FROM v_height167 WHERE height < 167;

💥 테이블, 뷰 등을 지울땐 DROP, 데이터 지울땐 DELETE FROM ~ WHERE 조건;

뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않도록 WITH CHECK OPTION이용

ALTER VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167
WITH CHECK OPTION;
INSERT INTO v_height167 VALUES('TOB', '텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01');

위와 같이 입력하면 오류가 난다! 140 < 167이므로 뷰에 입력되지 않음!!!!

8) 뷰가 참조하는 테이블의 삭제

DROP TABLE IF EXISTS buy, member;

💥 테이블은 관련 뷰가 있어도 쉽게 삭제되므로 주의할것!!!

그러고나서 뷰를 확인하면

SELECT * FROM v_height167;

참조하는 테이블이 사라졌으니 당연히 뷰가 유효할 수가 없어서 오류!

뷰가 조회되지 않는다면? CHECK TABLE 뷰이름;으로 뷰의 상태 확인가능.

P.271 4번) 다음은 기존에 뷰가 있으면 덮어쓰고, 없으면 새로 생성하는 SQL이다. 빈칸에 들어갈 내용?
____ 뷰이름
AS
SELECT문;

답: ② CREATE OR REPLACE VIEW