(2주차 11일) 테이블 실습

DB Modeling

> 테이블 생성 연습 문제

/*
* 테이블 생성 연습 문제
* 도서 관리 프로그램을 위한 테이블 만들기
*/

-- 1. 출판사 테이블 생성 및 샘플 데이터 추가
CREATE TABLE tb_publisher (
	pub_no INT AUTO_INCREMENT PRIMARY KEY,
	pub_name VARCHAR(20) NOT NULL,
	phone VARCHAR(20)
);

INSERT INTO tb_publisher(pub_name, phone) VALUES
('감자', '01011112222'),
('고구마', NULL),
('계란', '0233334444');

SELECT * FROM tb_publisher;
DROP TABLE tb_publisher;

-- 2. 도서 테이블 생성 및 샘플 데이터 추가
CREATE TABLE tb_book (
	bk_no INT AUTO_INCREMENT PRIMARY KEY,
	bk_title VARCHAR(20) NOT NULL,
	bk_author VARCHAR(10) NOT NULL,
	bk_price INT,
	bk_pub_no INT REFERENCES tb_publisher(pub_no) ON DELETE CASCADE
);

INSERT INTO tb_book(bk_title, bk_author, bk_price, bk_pub_no) VALUES
('첫번째책', '작가1', 3000, 1),
('두번째책', '작가2', 4000, 3),
('세번째책', '작가3', 5000, 2),
('네번째책', '작가4', NULL, NULL),
('다섯번째책', '작가5', 12000, 1);

-- 3. 회원 테이블 생성 및 샘플 데이터 추가
CREATE TABLE tb_member (
	member_no INT AUTO_INCREMENT PRIMARY KEY,
	member_id VARCHAR(20) NOT NULL UNIQUE,
	member_pwd VARCHAR(20) NOT NULL,
	member_name VARCHAR(20) NOT NULL,
	gender CHAR(2) CHECK(gender IN ('M', 'F')),
	address VARCHAR(20),
	phone VARCHAR(20),
	STATUS CHAR(2) DEFAULT 'N' CHECK(status IN ('Y', 'N')),
	enroll_date DATE DEFAULT CURDATE() NOT NULL
);

INSERT INTO tb_member
(member_id, member_pwd, member_name, gender, address, phone, STATUS, enroll_date) VALUES
('id1', '1234', '회원1', 'F', '서울', '01011111111', 'N', DEFAULT),
('id2', '1234', '회원2', 'F', '경기', NULL, 'N', '2023-11-22'),
('id3', '1234', '회원3', 'M', '강원', '01033333333', DEFAULT, '2022-01-15'),
('id4', '1234', '회원4', 'M', '대전', '01044444444', 'Y', '2018-07-02'),
('id5', '1234', '회원5', 'F', '대구', '01055555555', 'Y', DEFAULT);

-- 4. 대여 목록 테이블 생성 및 샘플 데이터 추가
CREATE TABLE tb_rent (
	rent_no INT AUTO_INCREMENT PRIMARY KEY,
	rent_mem_no INT REFERENCES tb_member(member_no),
	rent_book_no INT REFERENCES tb_book(bk_no),
	rent_date DATE DEFAULT CURDATE()
);

INSERT INTO tb_rent (rent_mem_no, rent_book_no, rent_date) VALUES
	(2, 3, '2024-01-02'),
	(1, 5, DEFAULT),
	(3, 3, '2023-10-11'),
	(5, 1, DEFAULT),
	(2, 2, '2024-03-17');
	
-- 5. 2번 도서를 대여한 회원의 이름, 아이디, 대여일, 반납 예정일(대여일 + 7일)을 조회
SELECT b.member_name, b.member_id, a.rent_date, ADDDATE(a.rent_date, 7) AS retn_date
FROM tb_rent a LEFT JOIN tb_member b ON a.rent_mem_no = b.member_no
WHERE a.rent_book_no = 2;

-- 6.회원번호가 1번인 회원이 대여한 도서들의 도서명, 출판사명, 대여일, 반납예정일을 조회
SELECT c.bk_title, c.pub_name, a.rent_date, ADDDATE(a.rent_date, 7) AS retn_date
FROM tb_rent a LEFT JOIN tb_member b ON a.rent_mem_no = b.member_no
	LEFT JOIN (SELECT * FROM tb_book i LEFT JOIN tb_publisher j ON i.bk_pub_no = j.pub_no) c 
	ON a.rent_book_no = c.bk_no
WHERE b.member_no = 1;

 

> 테이블 수정 및 삭제

/*
* 테이블 수정 및 삭제
*/
-- ##테이블 수정 실습
-- #열 추가
ALTER TABLE usertbl ADD homepage VARCHAR(30);
ALTER TABLE usertbl ADD gender CHAR(2) DEFAULT '남자' NOT NULL;
ALTER TABLE usertbl ADD age TINYINT DEFAULT 0 AFTER birthYear;

-- 테이블 복사(키는 복사되지 않음)
CREATE TABLE dept_copy (SELECT * FROM department);
ALTER TABLE dept_copy ADD lname VARCHAR(20) DEFAULT '대한민국' NOT NULL;

-- 테이블 구조만 복사(키도 복사)
CREATE TABLE dept_copy2  like department;

-- #열 수정: 데이터 타입 변경 시 이미 다른 형식의 값이 저장되어 있는 경우, 오류 발생
ALTER TABLE usertbl MODIFY NAME CHAR(10) NULL;
ALTER TABLE usertbl RENAME COLUMN NAME TO uname;testdb.tb_publisher
ALTER TABLE usertbl CHANGE COLUMN uname NAME VARCHAR(20) DEFAULT '없음' NULL;

-- #열 삭제
ALTER TABLE usertbl DROP COLUMN age;
ALTER TABLE usertbl DROP COLUMN homepage;
ALTER TABLE usertbl DROP COLUMN gender;

-- 칼럼을 삭제하는 경우, 적어도 하나의 칼럼은 남아있어야 함
-- 전체 삭제 시 DROP TABLE을 사용하여야 함
ALTER TABLE dept_copy DROP COLUMN dept_id;
ALTER TABLE dept_copy DROP COLUMN dept_title;
ALTER TABLE dept_copy DROP COLUMN lname;

-- #제약 조건 추가 및 삭제
-- 테이블 생성
CREATE TABLE tb_member_grade (
	grade_code VARCHAR(10),
	grade_name VARCHAR(10) NOT NULL
);

CREATE TABLE tb_member (
	mem_no INT,
	mem_id VARCHAR(20) NOT NULL,
	mem_pass VARCHAR(20) NOT NULL,
	mem_name VARCHAR(20) NOT NULL,
	enroll_date DATE DEFAULT CURDATE()
);

-- 제약 조건 추가
-- (PRIMARY KEY)
ALTER TABLE tb_member_grade ADD CONSTRAINT PRIMARY KEY(grade_code);
ALTER TABLE tb_member ADD CONSTRAINT PRIMARY KEY(mem_no);
ALTER TABLE tb_member MODIFY mem_no INT AUTO_INCREMENT;

-- (UNIQUE)
ALTER TABLE tb_member ADD CONSTRAINT UNIQUE(mem_id);

CREATE TABLE dept_copy (SELECT * FROM department);
ALTER TABLE dept_copy ADD CONSTRAINT PRIMARY KEY(dept_id);
-- 제약조건에 별칭 붙이기 가능
ALTER TABLE dept_copy ADD CONSTRAINT uk_dept_copy_dept_title UNIQUE(dept_title);

-- (FOREIGN KEY)
ALTER TABLE tb_member ADD grade_code VARCHAR(10) AFTER mem_name;
ALTER TABLE tb_member ADD CONSTRAINT FOREIGN KEY(grade_code)
	REFERENCES tb_member_grade(grade_code);

-- e.g. employee 테이블에서 dept_code, job_code에 외래키 제약 조건 추가
ALTER TABLE employee ADD CONSTRAINT FOREIGN KEY(dept_code)
	REFERENCES department(dept_id);
	
ALTER TABLE employee ADD CONSTRAINT FOREIGN KEY(job_code)
	REFERENCES job(job_code);
	
-- (CHECK)
ALTER TABLE tb_member ADD gender CHAR(2) AFTER mem_name;
ALTER TABLE tb_member ADD CONSTRAINT CHECK(gender IN ('남자', '여자'));

ALTER TABLE tb_member ADD age TINYINT AFTER gender;
ALTER TABLE tb_member ADD CONSTRAINT CHECK(age BETWEEN 0 AND 150);

-- 제약 조건 삭제: 옵션명이 아닌 제약조건명으로 삭제함
-- (PRIMARY KEY): 기본키 삭제 시 AUTO_INCREMENT 등 옵션 삭제 필요
ALTER TABLE tb_member MODIFY mem_no INT;
ALTER TABLE tb_member DROP CONSTRAINT PRIMARY KEY;

ALTER TABLE tb_member DROP CONSTRAINT mem_id;
ALTER TABLE tb_member DROP CONSTRAINT tb_member_ibfk_1;
ALTER TABLE tb_member DROP CONSTRAINT CONSTRAINT_1;
ALTER TABLE tb_member DROP CONSTRAINT CONSTRAINT_2;

ALTER TABLE tb_member_grade DROP CONSTRAINT PRIMARY KEY;

-- #테이블 이름 변경
RENAME TABLE dept_copy TO dept_test;

-- #테이블 삭제
DROP TABLE dept_test;
-- 기준 테이블은 삭제 불가능하므로 외래키를 삭제하고 진행하거나
-- 참조중인 테이블을 먼저 삭제하여야 함
DROP TABLE tb_member, tb_member_grade;

 

> 뷰(View)

• 가상의 테이블으로, 기본적으로 읽기 전용으로 많이 사용
• 뷰를 통해 테이블의 데이터를 수정할 수도 있음

/*
* 뷰(View)
*/
-- #뷰 생성
CREATE VIEW v_employee
AS SELECT emp_id, emp_name, b.dept_title, c.job_name, hire_date
FROM employee a LEFT JOIN department b ON a.dept_code = b.dept_id
	LEFT JOIN job c ON a.job_code = c.job_code;

UPDATE employee SET job_code = NULL WHERE emp_id = '202';

SELECT * FROM v_employee;

-- #뷰 조회
SELECT * FROM v_employee WHERE job_name = '대리';
SELECT * FROM v_employee WHERE dept_title IS NULL;

CREATE OR REPLACE VIEW v_employee
AS SELECT emp_id, emp_name, 
	IF(SUBSTRING(emp_no, 8, 1) = '1', '남자', '여자') AS gender, 
	salary FROM employee;
	
SELECT emp_name, gender FROM v_employee;

CREATE VIEW v_user
AS SELECT a.userID, NAME, b.prodName, addr, CONCAT(mobile1, mobile2)
FROM usertbl a INNER JOIN buytbl b ON a.userID = b.userID;

-- #뷰 수정
ALTER VIEW v_user
AS SELECT a.userID, NAME, b.prodName, addr, CONCAT(mobile1, mobile2) AS mobile
FROM usertbl a INNER JOIN buytbl b ON a.userID = b.userID;

-- #뷰를 이용한 DML(INSERT, UPDATE, DELETE) 사용
CREATE VIEW v_job
AS SELECT * FROM job;

SELECT job_code, job_name FROM v_job;
INSERT INTO v_job VALUES ('J8', '알바');
UPDATE v_job SET job_name = '인턴' WHERE job_code = 'J8';

-- #DML 조작이 불가능한 경우
-- 1. 뷰 정의에 포함되지 않는 열을 조작하는 경우
-- 2. 산풀 표현법으로 정의된 경우: 가상의 열이므로 데이터 조작 불가능
CREATE VIEW v_emp_salary AS SELECT
emp_id, emp_name, emp_no, salary * 12 AS 'salary' FROM employee;
INSERT INTO v_emp_salary VALUES ('100', '홍길동', '940523-1111111', 3000000);
INSERT INTO v_emp_salary(emp_id, emp_name, emp_no) VALUES 
('100', '홍길동', '940523-1111111'); -- 산술 연산과 무관한 경우 조작 가능
UPDATE v_emp_salary SET emp_name = '고길동' WHERE emp_name = '홍길동';
-- DELETE의 경우 단순 검색 조건으로 활용하므로 조작 가능
DELETE FROM v_emp_salary WHERE salary = 36000000;

-- 3. 그룹 함수나 GROUP BY 절을 포함한 경우
CREATE OR REPLACE VIEW v_emp_salary AS SELECT dept_code, 
SUM(salary) AS 'sum', FLOOR(AVG(salary)) AS 'avg' FROM employee GROUP BY dept_code;

INSERT INTO v_emp_salary VALUES ('D7', 8000000, 4000000);
INSERT INTO v_emp_salary(dept_code) VALUES ('D7');

-- 4. JOIN을 이용해 여러 테이블을 연결한 경우
CREATE VIEW v_emp_dept AS 
SELECT emp_id, emp_name, emp_no, b.dept_title FROM employee a
LEFT JOIN department b ON a.dept_code = b.dept_id;

SELECT * FROM v_emp_dept;

-- INSERT, DELETE 불가능
INSERT INTO v_emp_dept(emp_id, emp_name, emp_no) VALUES 
('100', '홍길동', '000523-3123456'); -- 오류
DELETE FROM v_emp_dept WHERE emp_id = '200'; -- 오류

-- UPDATE 일부 가능(DBMS에 따라 불가능하기도 함 - oracle 등)
UPDATE v_emp_dept SET dept_title = '총무1팀' WHERE emp_id = '200';