240523 MariaDB(6) - 테이블 수정/뷰

일일 정리

240523 MariaDB(6) - 테이블 수정/뷰

햠__ 2024. 5. 24. 08:56

어제 내준 실습 문제를 이어서 풀이해 보자!

-- 실습 문제
-- 도서관리 프로그램을 만들기 위한 테이블 만들기

-- 1. 출판사들에 대한 데이터를 담기 위한 출판사 테이블(tb_publisher) 
--  1) 컬럼 : pub_no(출판사 번호) -- 기본 키
--            pub_name(출판사명) -- NOT NULL
--            phone(출판사 전화번호)

CREATE TABLE tb_publisher (
	pub_no INT AUTO_INCREMENT PRIMARY KEY,
    pub_name VARCHAR(15) NOT NULL,
    phone VARCHAR(15)
);

--  2) 3개 정도의 샘플 데이터 추가하기
INSERT INTO tb_publisher(pub_name, phone) VALUES ('숟가락출판사', '02-1234-5678');
INSERT INTO tb_publisher(pub_name, phone) VALUES ('젓가락출판사', '02-2345-6789');
INSERT INTO tb_publisher(pub_name, phone) VALUES ('다이소출판사', '02-3456-7890');

-- 2. 도서들에 대한 데이터를 담기 위한 도서 테이블 (tb_book)
--  1) 컬럼 : bk_no (도서번호) -- 기본 키
--            bk_title (도서명) -- NOT NULL
--            bk_author(저자명) -- NOT NULL
--            bk_price(가격)
--            bk_pub_no(출판사 번호) -- 외래 키(tb_publisher 테이블을 참조하도록)
--                                      이때 참조하고 있는 부모 데이터 삭제 시 자식 데이터도 삭제 되도록 옵션 지정

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(10),
    bk_pub_no INT(10) REFERENCES tb_publisher(pub_no) ON DELETE CASCADE
);

--  2) 5개 정도의 샘플 데이터 추가하기
-- 1-2와 다른 방법으로 아래와 같이 추가 가능
INSERT INTO tb_book(bk_title, bk_author, bk_price, bk_pub_no)
VALUES ('MariaDB', '숟가락', 25000, 1),
	('SQL', '젓가락', 23000, 2),
    ('테이블수정', '스폰지밥', 13000, 3),
    ('객체지향', '강아지', 35000, 1),
    ('소프트웨어공학', '고양이', 30000, 2);
    
SELECT * FROM tb_book;

테이블 정보를 살펴보았을 때 NOT NULL로 설정도 잘 되어있는 것을 확인하였다.

-- 3. 회원에 대한 데이터를 담기 위한 회원 테이블 (tb_member)
--  1) 컬럼 : member_no(회원번호) -- 기본 키
--            member_id(아이디)   -- 중복 금지
--            member_pwd(비밀번호) -- NOT NULL
--            member_name(회원명) -- NOT NULL
--            gender(성별)        -- 'M' 또는 'F'로 입력되도록 제한
--            address(주소)       
--            phone(연락처)       
--            status(탈퇴 여부)     -- 기본값으로 'N' , 그리고 'Y' 혹은 'N'으로 입력되도록 제약조건
--            enroll_date(가입일)  -- 기본값으로 CURDATE(), NOT NULL

CREATE TABLE tb_member (
	member_no INT AUTO_INCREMENT PRIMARY KEY,
    member_id VARCHAR(10) UNIQUE,
    member_pwd VARCHAR(10) NOT NULL,
    member_name VARCHAR(5) NOT NULL,
    gender CHAR(1) CHECK(gender IN ('M', 'F')),
    address VARCHAR(5),
    phone VARCHAR(15),
    status CHAR(1) DEFAULT 'N' CHECK(stauts IN ('Y', 'N')),
    enroll_date DATE NOT NULL DEFAULT CURDATE()
;

--  2) 3개 정도의 샘플 데이터 추가하기
INSERT INTO tb_member VALUES (NULL, 'abc123', '1234', '김땡땡', 'M', NULL, NULL, 'N', DEFAULT);
INSERT INTO tb_member VALUES (NULL, 'def123', '1234', '박땡땡', 'F', NULL, NULL, 'Y', DEFAULT);
INSERT INTO tb_member VALUES (NULL, 'ghi123', '1234', '이땡땡', 'M', NULL, NULL, 'Y', DEFAULT);

SELECT *
FROM tb_member;

-- 4. 도서를 대여한 회원에 대한 데이터를 담기 위한 대여 목록 테이블(tb_rent)
--  1) 컬럼 : rent_no(대여번호) -- 기본 키
--            rent_mem_no(대여 회원번호) -- 외래 키(tb_member와 참조)
--            rent_book_no(대여 도서번호) -- 외래 키(tb_book와 참조)
--            rent_date(대여일) -- 기본값 CURDATE()
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()
);

--  2) 샘플 데이터 5개 정도 
INSERT INTO tb_rent VALUES (NULL, 1, 4, DEFAULT);
INSERT INTO tb_rent VALUES (NULL, 2, 2, DEFAULT);
INSERT INTO tb_rent VALUES (NULL, 3, 5, DEFAULT);
INSERT INTO tb_rent VALUES (NULL, 1, 2, DEFAULT);

-- 5. 2번 도서를 대여한 회원의 이름, 아이디, 대여일, 반납 예정일(대여일 + 7일)을 조회하시오.
SELECT m.member_no AS '이름',
		 m.member_id AS '아이디',
		 r.rent_date AS '대여일',
		 ADDDATE(r.rent_date, 7) AS '반납 예정일'
FROM tb_member m
INNER JOIN tb_rent r ON m.member_no = r.rent_mem_no
WHERE r.rent_book_no = '2'
ORDER BY m.member_no;

-- 6. 회원번호가 1번인 회원이 대여한 도서들의 도서명, 출판사명, 대여일, 반납예정일을 조회하시오.
SELECT b.bk_title AS '도서명',
		 p.pub_name AS '출판사명',
		 r.rent_date AS '대여일',
		 ADDDATE(r.rent_date, 7) AS '반납예정일'
FROM tb_book b
INNER JOIN tb_publisher p ON b.bk_pub_no = p.pub_no
INNER JOIN tb_rent r ON b.bk_no = r.rent_book_no
WHERE r.rent_mem_no = '1';

테이블 수정

테이블 수정

ALTER TABLE 구문 사용

정의된 열과 제약 조건의 추가, 수정, 삭제, 이름 변경 등을 할 수 있다.

(제약 조건은 추가와 수정만 됨. 삭제 및 이름 변경을 하려면 다시 만들어야 함)

열의 추가

ALTER와 ADD 구문을 이용해서 열을 추가할 수 있다.

열을 추가하면 기본적으로 가장 뒤에 추가가 되는데, 순서를 지정하려면 제일 뒤에 FIRST 또는 AFTER 열 이름을 지정하면 된다.

ALTER TABLE 테이블명 ADD (열 이름1) (데이터 유형) AFTER (열 이름2)

간단한 예시를 들어보자.

-- 테이블 수정 실습
-- 1. 열의 추가, 수정, 삭제
-- 1) 열의 추가

-- usertbl 테이블에 homepage 열을 추가
ALTER TABLE usertbl ADD homepage VARCHAR(30);

-- usertbl 테이블에 gender 열 추가 (단, 기본값을 남자로 지정)
ALTER TABLE usertbl ADD gender DEFAULT '남자' NOT NULL;

테이블 정보를 보면

열을 2개 추가한 뒤 테이블 정보를 확인했더니

위와 같이 아래에 homepage와 gender 열이 새로 생긴 것을 확인할 수 있었다.

또한, gender 열에는 기본값을 남자로 주었고, NOT NULL 조건을 걸어주었기 때문에

NULL 허용이 되지 않는 것을 확인했고,

데이터 부분에서 추가한 gender 열의 모든 값들이 오른쪽처럼

기본값인 '남자'로 삽입된 것을 확인할 수 있었다.

-- usertbl 테이블에 age열 추가
-- (단, 기본 값으로 0으로 지정하고 birthYear 뒤에 생성)
ALTER TABLE age TINYINT DEFAULT 0 AFTER birthYear;

위와 같은 SQL문을 실행해서 맨 마지막에 열이 새로 추가되는 것이 아닌

birthYear 열 다음에 age 열을 추가하였다.

-- dept_copy 테이블에 Lname열을 테이블 맨 뒤에 추가한다.
-- 단, 기본값은 대한민국, NULL 값은 허용하지 않음
ALTER TABLE dept_copy ADD Lname VARCHAR(10) DEFAULT '대한민국' NOT NULL;

테이블 내에 열을 추가하고 기본 값도 지정해 주었다.

데이터 타입, DEFAULT값 변경

ALTER와 MODFIY 구문을 이용해서 열의 데이터 타입, DEFAULT 값을 변경할 수 있다.

ALTER TABLE (테이블명) MODIFY (열이름) (데이터유형) DEFAULT (기본값);

-- 2) 열의 수정
ALTER TABLE usertbl MODIFY NAME CHAR(10) NULL;

-- 변경하려는 데이터 유형의 크기보다 이미 큰 값이 존재하기 때문에 에러 발생
ALTER TABLE usertbl MODIFY NAME CHAR(1);

-- 이미 name에 문자열이 저장되어 있기 때문에 에러 발생
ALTER TABLE usertbl MODIFY NAME INT;

-- 값이 없으면 문자 타입을 정수 타입으로 변경 가능
ALTER TABLE usertbl MODIFY homepage INT;

변경 후 테이블 정보를 확인하니 NAME열의 데이터 유형이 VARCHAR에서 CHAR로 바뀐 것을 확인할 수 있었고,

homepage 열에는 데이터가 없기 때문에 문자 타입을 정수 타입으로 변경할 수 있었다.

또한, 여기서 알아야 할 점은 변경하려는 데이터 유형의 크기보다 이미 큰 값이 데이터 값으로 저장되어 있다면 변경이 불가능하다는 점이고,

이미 데이터 값이 존재할 때에는 데이터 유형을 변경할 수 없다는 점이다.

열의 이름 변경

ALTER와 RENAME COLUMN 구문을 이용해서 변경할 수 있다.

ALTER TABLE (테이블명) RENAME COLUMN (열 이름1) TO (열 이름2);

-- name 열의 이름을 uname 으로 변경
ALTER TABLE usertbl RENAME COLUMN NAME TO uname;

-- 이와 같이 한 번에 변경도 가능
ALTER TABLE usertbl CHANGE COLUMN uname VARCHAR(20) DEFAULT '없음' NULL;

열 삭제

ALTER와 DROP COLUMN 구문을 이용해서 열을 삭제할 수 있다.

ALTER TABLE (테이블명) DROP COLUMN (열 이름);

앞서 만든 열들을 다 삭제해 보자!

-- 3) 열의 삭제
ALTER TABLE usertbl DROP COLUMN age;
ALTER TABLE usertbl DROP COLUMN homepage;
ALTER TABLE usertbl DROP COLUMN gender;

-- 참조되고 있는 열이 있다면 삭제가 불가능
-- 제약 조건을 삭제하거나 참조하는 열이 없도록 한 후에 삭제해야 한다.
ALTER TABLE usertbl DROP COLUMN userid;

열을 삭제할 때에는 참조되고 있는 열이 있다면 삭제가 불가능하다.

이런 경우에는 제약 조건을 삭제하거나 참조하는 열이 없도록 한 후에 삭제해야 한다.

또한, 테이블에 컬럼을 지울 때에 모든 컬럼을 지울 수 없다.

테이블에는 최소 한 개의 컬럼이 존재해야 한다.

컬럼을 삭제할 때에는 항상 다시 한번 확인하는 것도 잊지 말자.

열의 제약 조건 추가/삭제

제약 조건을 추가할 때에는 ALTER와 ADD CONSTRAINT 구문을 이용하고,

제약 조건을 삭제할 때에는 ALTER와 DROP 구문을 이용하면 된다.

제약 조건의 수정은 불가능하기 때문에 삭제 후에 다시 제약 조건을 추가해야 한다.

-- 추가
ALTER TABLE (테이블명) ADD CONSTRAINT (제약조건);

-- 삭제
ALTER TABLE (테이블명) DROP CONSTRAINT (제약조건);

앞서 만들었던 테이블을 삭제하고

제약 조건 없이 간단하게 다시 만들어 제약 조건을 추가해 보자!

-- 테이블 만드는 건 길어져서 생략 ..

-- tb_member_grade 테이블에 PRIMARY KEY 제약 조건 추가
ALTER TABLE tb_member_grade ADD CONSTRAINT PRIMARY KEY(grade_code);

-- tb_member 테이블에 PRIMARY KEY 제약 조건 추가
ALTER TABLE tb_member ADD CONSTRAINT PRIMARY KEY(mem_no);
ALTER TABLE tb_member MODIFY mem_no INT AUTO_INCREMENT;

-- tb_member 테이블에 UNIQUE 제약 조건 추가
ALTER TABLE tb_member ADD CONSTRAINT UNIQUE(mem_id);

위 코드 실행 후 테이블 정보를 살펴보면 dept_id와 dept_title에 키 그림이 생긴 것을 확인할 수 있었다.

제약 조건 삭제

ALTER와 DROP 구문을 이용해서 삭제할 수 있다.

-- tb_member 테이블에 PRIMARY KEY 제약 조건 삭제
ALTER TABLE tb_member MODIFY mem_no INT; -- AUTO_INCREMENT 해제 후 삭제
ALTER TABLE tb_member DROP CONSTRAINT PRIMARY KEY;

-- tb_member 테이블에 UNIQUE 제약 조건 삭제
-- 제약 조건을 지울 때는 제약 조건의 이름이 와야한다.
ALTER TABLE tb_member DROP CONSTRAINT UNIQUE mem_id;

-- tb_member_grade 테이블에 PRIMARY KEY 제약 조건 삭제
ALTER TABLE tb_member_grade DROP CONSTRAINT PRIMARY KEY;

-- tb_member 테이블에 CHECK 제약 조건 삭제
ALTER TABLE tb_member DROP CONSTRAINT CONSTRAINT_1;
ALTER TABLE tb_member DROP CONSTRAINT CONSTRAINT_2;

제약 조건을 삭제하는 방법은 간단하다.

단, 주의해야 할 점은 제약 조건을 지울 때는 제약 조건의 이름이 와야 한다.

PRIMARY KEY를 삭제할 경우에는 MODIFY 문을 이용해서 AUTO_INCREMENT를 해제 후에 삭제해주어야 한다.

해제하지 않으면 삭제가 되지 않는다.

또한, 여러 테이블에 연관되어 있는 경우에도 삭제가 되지 않는다.

뷰(View)

뷰(View)

테이블과 똑같은 모습을 하고 있는 가상의 테이블이다.

기본적으로 읽기 전용으로 많이 사용하지만, 뷰를 통해서 테이블의 데이터를 수정할 수도 있다.

뷰의 생성

CREATE VIEW 구문을 사용한다.

CREATE [OR REPLACE] VIEW 뷰 이름
AS 서브 쿼리
[WITH CHECK OPTION];

뷰는 SELECT 문을 가지고 있다.

이걸 서브 쿼리 자리에 사용한다.

뷰 생성 실습을 진행해 보자.

-- 뷰 생성
CREATE VIEW v_employee
AS SELECT e.emp_id,
	 	e.emp_name,
		d.dept_title,
 		j.job_name,
		e.hire_date
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = d.dept_id
LEFT OUTER JOIN job j ON e.job_code = j.job_code;

SELECT * FROM v_employee;

위와 같이 뷰 작성 후 SELECT * FROM 뷰 를 실행해 주면

뷰 생성 전 서브 쿼리만 실행하였을 때 왜 똑같은 결과를 조회해준다.

뷰 내용을 보면 SELECT 위에서 짠 쿼리가 존재한다.

뷰가 가진 쿼리를 실행시켜서 얻는 결과이지

실제적인 데이터가 담겨 있는 것은 아니라는 것을 잊지 말자.

다음 쿼리문을 보자.

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

SELECT *
FROM v_employee;

CREATE OR REPLACE VIEW를 사용하면

동일한 이름의 뷰의 이름이 존재하면 그 위에 서브 쿼리문을 덮어쓰고

만약 동일한 이름의 뷰가 없다면 새로 생성한다.

편한 쿼리문이지만 운영 환경에서 덮어 씌우는 것은 조심해서 사용해야 한다.

만약 위 쿼리문에서 몇 가지 열만 가지고 싶으면 어떨까?

-- 1번
SELECT emp_name,
		 if (SUBSTRING(emp_no, 8, 1) = '1', '남자', '여자'),
		 salary
FROM v_employee;

-- 2번
SELECT emp_name,
		 `if (SUBSTRING(emp_no, 8, 1) = '1', '남자', '여자')`,
		 salary
FROM v_employee;

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

1번 쿼리문과 같이 두 개의 열을 가져오려고 하는 경우에는 에러가 발생한다.

왜냐하면 if 문을 열의 이름이 아닌 함수로 인식하기 때문이다.

그렇기 때문에 2번처럼 백틱 기호를 붙여서 적어주거나

3번처럼 아예 뷰 생성 시에 별칭을 지정해주어야 한다.

즉, SELECT 쿼리에 함수나 산술연산이 기술되어 있는 경우 별칭을 지정해주어야 한다.

-- 실습
-- 회원의 아이디, 이름, 구매 제품, 주소, 연락처를 조회하는 뷰 생성
CREATE VIEW v_info
AS SELECT u.userID,
		 u.name,
		 b.prodName,
		 u.addr,
		 CONCAT(mobile1, mobile2) AS 'tel'
FROM usertbl u
INNER JOIN buytbl b ON u.userID = b.userID;

SELECT *
FROM v_info;

또한 왼쪽 데이터베이스 창을 새로고침 했더니 뷰가 생성된 것도 확인했고,

위와 같이 올바르게 데이터가 조회되는 것도 확인했다.

뷰의 수정

ALTER VIEW 구문을 사용한다.

ALTER VIEW 뷰 이름 AS 서브 쿼리;

수정은 매우 간단하다.

맨 처음 뷰를 작성할 때 썼던 서브 쿼리를 가져와서

서브 쿼리 자리에서 수정하면 된다.

근데 보통 수정할 때에는 ALTER VIEW 사용 안 하고

REPLACE 사용해서 한다.

뷰를 이용해서 DML(INSERT, UPDATE, DELETE) 사용

-- 뷰를 이용해서 DML(INSERT, UPDATE, DELETE) 사용
CREATE VIEW v_job;
AS SELECT *
	FROM job;
	
-- VIEW에 SELECT
SELECT job_code, job_name FROM v_job;

-- VIEW INSERT
INSERT INTO v_job VALUES ('J8', '알바');

-- VIEW UPDATE (J8 직급명을 인턴으로 변경)
UPDATE v_job 
SET job_name = '인턴'
WHERE job_code = 'J8';

-- VIEW DELETE 
DELETE FROM v_job
WHERE job_code = 'J8';

뷰를 이용해서 삽입, 삭제, 수정을 진행할 경우 뷰는 물론이고

기존의 테이블에도 영향을 받는 것을 알 수 있었다.

그렇다고 모든 데이터 값들에 DML 조작이 가능한 것은 아니다.

아래의 경우에는 불가능하다.

1) 뷰 정의에 포함되지 않는 열을 조작하는 경우

2) 산술 표현법으로 정의된 경우

3) 그룹 함수나 GROUP BY 절을 포함한 경우

4) JOIN을 이용해서 여러 테이블을 연결한 경우 (UPDATE는 됨.. 왜지)

근데 뷰를 통해서 삽입, 삭제, 업데이트하는 경우는 드물다고 한다.

민감한 내용들을 외부에 보여줄 때 정도 뷰를 이용한다고 한다. (제공할 때 SELECT 권한만 주게 됨)

'일일 정리' 카테고리의 다른 글

240527 MariaDB(8) - 리눅스에 MariaDB 설치/NOT EQUAL JOIN 실습/조인 실습 문제 (0)	2024.05.28
240524 MariaDB(7) - 인덱스/스토어드 프로시져/트리거 (0)	2024.05.26
240522 MariaDB(5) - DB 모델링/테이블/제약 조건 (0)	2024.05.22
240521 MariaDB(4) - 조인/UNION 연산자/인라인 뷰 (0)	2024.05.21
240520 MariaDB(3) - 함수/조인/UNION 연산자 (0)	2024.05.20

현재글240523 MariaDB(6) - 테이블 수정/뷰

으아악

기록

Today :
Yesterday :

으아악