MySQL SQLDB 풀세트 — DB생성·테이블·데이터 한꺼번에 완전 정리

 

 

MySQL 실습 정리

SQLDB 풀세트

회원/구매 테이블 생성부터 샘플 데이터 입력까지 한 번에 구성한다.

1 핵심 개념

회원/구매 테이블 생성부터 샘플 데이터 입력까지 한 번에 구성한다.. 이 묶음은 제약조건으로 데이터 무결성을 지키고, VIEW로 복잡한 조회를 재사용하는 방법을 다룬다.

외래키와 CASCADE는 데이터 관계를 직접 바꾸므로 실행 전후의 행 수와 참조 상태를 반드시 비교해야 한다. VIEW는 실제 데이터를 복사하지 않고 SELECT 문을 저장해 두는 성격에 가깝다.

VIEW는 조회 편의성을 높이고, 제약조건은 잘못된 입력을 막는다. 목적이 다르므로 둘을 분리해서 이해하면 쉽다.

2 원본 예제 코드

CREATE DATABASE sqldb; USE sqldb; CREATE TABLE usertbl ( userID CHAR(8) NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL, birthYear INT NOT NULL, addr CHAR(2) NOT NULL, mobile1 CHAR(3), mobile2 CHAR(8), height SMALLINT, mDate DATE ); CREATE TABLE buytbl ( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, userID CHAR(8) NOT NULL, prodName CHAR(6) NOT NULL, groupName CHAR(4), price INT NOT NULL, amount SMALLINT NOT NULL, FOREIGN KEY (userID) REFERENCES usertbl(userID) ); INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8'); INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL, 30, 2); SELECT * FROM usertbl; SELECT * FROM buytbl;

3 용어 카드

VIEWSELECT 결과를 가상 테이블처럼 저장해 재사용하는 객체

Alias컬럼이나 테이블에 붙이는 임시 이름

백틱공백이나 특수 문자가 있는 별칭을 감싸는 기호

CASCADE부모 데이터 변경/삭제를 자식 데이터에 전파하는 옵션

foreign_key_checks외래키 검사 활성화 여부를 제어하는 설정

CHECK입력값 범위를 제한하는 제약조건

INNER JOIN양쪽에 매칭되는 행만 남기는 조인

LEFT OUTER JOIN왼쪽 기준 행을 보존하는 조인

 

반응형

 

4 실행 흐름

1

원본 테이블 확인usertbl과 buytbl의 컬럼, 키, 데이터 행 수를 먼저 확인한다.

2

관계 확인userID가 부모와 자식 테이블에서 어떻게 연결되는지 본다.

3

SQL 실행ALTER, CREATE VIEW, INSERT, UPDATE를 주제별로 나누어 실행한다.

4

결과 비교JOIN, SELECT, SHOW CREATE TABLE로 변경 전후를 비교한다.

5

예외 확인FK 위반, CHECK 위반, 별칭 조회 오류 같은 실패 케이스를 확인한다.

5 VIEW 기준

항목	설명	확인 방법
CREATE VIEW	SELECT 문을 뷰 이름으로 저장한다	SHOW FULL TABLES로 확인
단순 뷰	한 테이블에서 필요한 컬럼만 보여준다	SELECT * FROM v_usertbl
조인 뷰	여러 테이블 조인을 재사용한다	WHERE 조건을 붙여 조회
별칭	출력 컬럼명을 보기 좋게 바꾼다	AS 'USER ID'
백틱	공백이 있는 별칭을 조회할 때 사용한다	SELECT `USER ID`

6 FK와 CASCADE 기준

옵션	동작	주의
FOREIGN KEY	부모 테이블에 있는 값만 허용	부모 없는 자식 데이터 입력 실패
ON UPDATE CASCADE	부모 키 변경을 자식에게 전파	키 변경 영향 범위 확인
ON DELETE CASCADE	부모 삭제 시 자식도 삭제	실수 삭제 위험
foreign_key_checks=0	검사를 임시 비활성화	재활성화 후 데이터 정합성 직접 확인
DROP FOREIGN KEY	기존 외래키 제거	제약조건 이름 필요

7 JOIN 결과 해석

INNER JOIN은 회원과 구매가 모두 존재하는 행만 보여준다. LEFT OUTER JOIN은 구매 테이블을 기준으로 두면 회원 정보가 없어도 구매 행을 남길 수 있어, 참조 누락 데이터를 찾을 때 유용하다.

SELECT B.userID, U.name, B.prodName FROM buytbl B LEFT OUTER JOIN usertbl U ON B.userID = U.userID WHERE U.userID IS NULL;

8 검산 쿼리

SELECT COUNT(*) FROM usertbl; SELECT COUNT(*) FROM buytbl; SHOW CREATE TABLE buytbl; SHOW FULL TABLES WHERE Table_type = 'VIEW'; SELECT * FROM v_userbuytbl;

CASCADE나 foreign_key_checks를 다룬 뒤에는 반드시 조인 결과와 행 수를 확인한다. VIEW를 만든 뒤에는 원본 SELECT와 뷰 조회 결과가 같은 의미인지 비교한다.

9 자주 하는 실수

실수	증상	해결
별칭에 공백을 넣고 일반 컬럼처럼 조회	Unknown column 오류	백틱으로 감싼다
foreign_key_checks를 다시 켜지 않음	이후 입력 검사가 느슨해짐	작업 직후 1로 복구
CASCADE 삭제 영향 미확인	자식 데이터가 같이 삭제됨	삭제 전 SELECT로 대상 확인
CHECK 추가 전 데이터 미정리	ALTER TABLE 실패	UPDATE로 위반 데이터 수정
VIEW를 실제 테이블로 착각	저장 공간과 수정 가능성 오해	SELECT 저장 객체로 이해

10 변경 전후 비교

작업 전	작업 후	확인 포인트
복잡한 SELECT 반복	VIEW로 재사용	원본 SELECT와 뷰 결과 비교
부모 키 변경 시 자식 불일치	ON UPDATE CASCADE로 동기화	buytbl.userID 변경 여부
부모 삭제 시 자식 잔존	ON DELETE CASCADE로 함께 삭제	삭제 범위 사전 확인
범위 밖 birthYear 존재	CHECK 추가 후 차단	위반 데이터 정리 여부
FK 때문에 대량 입력 실패	checks 비활성화 후 입력	복구와 정합성 점검

11 실무 메모

CASCADE는 편리하지만 영향 범위가 크다. 특히 ON DELETE CASCADE는 부모 행 삭제가 곧 자식 행 삭제로 이어지므로 운영 데이터에서는 삭제 전 백업과 대상 확인이 필요하다.

VIEW는 보고서성 조회나 반복되는 조인에 적합하다. 다만 별칭에 공백을 넣으면 조회할 때 백틱이 필요하므로, 실무에서는 가능하면 공백 없는 컬럼 별칭을 쓰는 편이 관리하기 쉽다.

12 최종 체크리스트

체크	통과 기준
VIEW 생성	SELECT * FROM 뷰이름이 정상 동작한다.
별칭 조회	공백 별칭은 백틱으로 조회한다.
FK 상태	SHOW CREATE TABLE에서 제약조건을 확인한다.
CASCADE 결과	부모 변경 후 자식 테이블 값을 비교한다.
검사 복구	foreign_key_checks를 1로 되돌린다.

13 한 줄 복습

상황	기억할 문법	핵심
조회 재사용	CREATE VIEW ... AS SELECT	복잡한 SELECT를 이름으로 저장
공백 별칭 조회	`USER ID`	작은따옴표가 아니라 백틱
부모 키 변경 전파	ON UPDATE CASCADE	자식 FK 값도 변경
부모 삭제 전파	ON DELETE CASCADE	자식 행도 삭제
FK 검사 임시 해제	SET foreign_key_checks = 0	작업 후 1로 복구
범위 제한	CHECK (...)	위반 데이터 정리 후 추가

14 실습 시나리오

1

기본 데이터 준비usertbl과 buytbl에 정상 회원과 구매 데이터를 먼저 넣는다.

2

관계 확인INNER JOIN으로 매칭되는 구매만 확인하고, LEFT JOIN으로 누락 관계를 찾는다.

3

제약조건 적용CHECK, FK, CASCADE를 적용하기 전 위반 데이터를 정리한다.

4

VIEW 생성반복 조회할 SELECT를 CREATE VIEW로 저장한다.

5

변경 테스트UPDATE 또는 DELETE 후 자식 테이블과 뷰 조회 결과를 비교한다.

6

정합성 점검행 수, NULL, 참조 누락, 별칭 조회를 마지막에 다시 확인한다.

15 결과 판정표

확인 결과	정상 해석	다음 조치
INNER JOIN 행 수가 buytbl보다 적다	회원 없는 구매가 있을 수 있다	LEFT JOIN으로 누락 행 확인
VIEW 조회가 원본 SELECT와 같다	뷰 정의가 의도대로 저장됐다	WHERE 조건을 붙여 재사용
백틱 없이 별칭 조회 실패	공백 별칭 사용 때문이다	백틱으로 감싸거나 별칭을 단순화
UPDATE 후 buytbl도 바뀐다	ON UPDATE CASCADE 동작	전파 범위가 맞는지 확인
DELETE 후 buytbl 행이 줄었다	ON DELETE CASCADE 동작	삭제 전 백업 필요성 기록

16 복구와 정리

상황	복구 방향	확인 명령
foreign_key_checks를 꺼 둠	SET foreign_key_checks = 1	SELECT @@foreign_key_checks
잘못 만든 뷰	DROP VIEW 후 다시 CREATE VIEW	SHOW FULL TABLES
잘못 건 CASCADE	DROP FOREIGN KEY 후 재추가	SHOW CREATE TABLE buytbl
CHECK 추가 실패	위반 데이터 UPDATE 후 재시도	WHERE 조건으로 위반 값 검색
조인 결과 누락	OUTER JOIN으로 원인 행 찾기	U.userID IS NULL 조건

17 마무리 기준

이 주제는 SQL 실행 성공보다 변경 후 데이터 관계가 의도대로 유지되는지가 더 중요하다. VIEW, FK, CASCADE, CHECK는 모두 결과를 검산해야 완성이다.

뷰는 조회 편의성, 외래키와 CHECK는 데이터 보호, CASCADE는 관계 전파라는 역할로 나누어 기억하면 복습하기 쉽다.

핵심 요약

주제SQLDB 풀세트

핵심회원/구매 테이블 생성부터 샘플 데이터 입력까지 한 번에 구성한다.

VIEW복잡한 SELECT를 저장해 반복 조회를 단순화한다.

FK부모-자식 관계를 강제하고 CASCADE로 전파 정책을 정한다.

검산행 수, 조인 결과, SHOW CREATE TABLE을 함께 확인한다.

Tags

#MySQL #VIEW #CASCADE #FOREIGN KEY #JOIN #CHECK #SQL #티스토리

티스토리 태그 입력용

MySQL, VIEW, CASCADE, FOREIGN KEY, JOIN, CHECK, SQL, 데이터베이스, DB, 티스토리