9장 : 테이블과 뷰
section1 : 테이블 생성
1. 테이블의 개요
테이블은 행과 열로 구성되며 행은 row 열은 column
2. 테이블 생성
아주 간단한 테이블 생성
CREATE TABLE test (num INT);테이블 생성 시 참고할만한 포인트
- AUTO_INCREMENT 로 지정한 열은 반드시 기본키(PRIMARY KEY) 또는 유일한 값(UNIQUE)으로 지정
- FOREIGN KEY(userID) REFERENCES userTBL(userID) : userTBL 테이블의 userID열과 외래키 관계를 맺으라
INSERT INTO userTBL VALUES('YJS', '유재석', 1972, '서울', '010', '11111111', 178, '2008-8-8');
INSERT INTO userTBL VALUES('KHD', '강호동', 1970, '경북', '011', '22222222', 182, '2007-7-7');
INSERT INTO userTBL VALUES('KKJ', '김국진', 1965, '서울', '019', '33333333', 171, '2009-9-9');
INSERT INTO buyTBL VALUES(NULL, 'KHD', '운동화', NULL , 30, 2);
INSERT INTO buyTBL VALUES(NULL, 'KHD', '노트북', '전자', 1000, 1);
INSERT INTO buyTBL VALUES(NULL, 'KYM', '모니터', '전자', 200, 1);위의 sql문은 에러 발생
ERROR : Cannot add or update a child row : a foreign key constraint fails...
-> 외래키로 연결된 구매 테이블의 userID값은 반드시 회원 테이블의 userID에 존재해야함
-> 회원테이블에 아직 'KYM'(김용만)이라는 회원을 입력하지 않았기 때문에 오류 발생
-> 회원가입을 하지 않으면 물건을 구매하지 못하도록 업부 프로세스를 설정한 것
section2 : 제약 조건
1. 제약 조건의 개요
제약조건
- 데이터의 무결성을 지키기 위해 제한하는 조건
- 특정 데이터를 입력할때 무조건 입력되는것이 아니라 제약 조건을 만족했을때만 입력되도록 설정
- ex> 이미 가입한 쇼핑몰에 동일한 아이디로 다시 가입 불가 : 관리자가 데이터베이스 아이디 열에 동일한 내용 들어갈 수 없도록 제약조건 설정했기 때문
2. 기본키 제약 조건
기본키 : 입력되는 값 중복될 수 없으며 NULL 값 올 수 없음
기본키 생성
1) PRIMARY KEY 적어주기
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL PRIMARY KEY,
userName VARCHAR(10) NOT NULL,
birthYear INT NOT NULL
);
DESCRIBE userTBL;- CREATE TABLE 문안에서 기본키 지정하면 제약조건 이름 알아서 정해짐
2) 사용자가 제약조건 이름 지정하기
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL,
userName VARCHAR(10) NOT NULL,
birthYear INT NOT NULL,
CONSTRAINT PRIMARY KEY PK_userTBL_userID (userID)
);3) 이미 만들어진 테이블 수정
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL,
userName VARCHAR(10) NOT NULL,
birthYear INT NOT NULL
);
ALTER TABLE userTBL
ADD CONSTRAINT PK_userTBL_userID
PRIMARY KEY (userID);- ALTER TABLE userTBL : 회원 테이블 변경
- ADD CONSTRAINT PK_userTBL_userID : 제약 조건 추가하고 제약 조건 이름 'PK_userTBL_userID'로 명명
- PRIMARY KEY (userID) : 추가할 제약 조건은 기본키 제약 조건이고 제약 조건을 설정할 열은 userID
3. 외래키 제약 조건
외래키 제약
- 두 테이블 사이의 관계를 선언함으로써 데이터의 무결성을 보장
- 하나의 테이블이 다른 테이블에 의존
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL PRIMARY KEY,
userName VARCHAR(10) NOT NULL,
birthYear INT NOT NULL
);
CREATE TABLE buyTBL
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY ,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL,
FOREIGN KEY(userID) REFERENCES userTBL(userID)
);- 외래키 테이블(buyTBL)의 열(userID)이 참조하는 기준 테이블(userTBL)의 열(userID)이 기본키로 설정
- 기준 테이블의 열이 기본키 또는 UNIQUE로 설정되지 않았다면 외래키 관계 성립되지 않음!!!
이렇게도 가능하다,,
CREATE TABLE buyTBL
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL
);
ALTER TABLE buyTBL
ADD CONSTRAINT FK_userTBL_buyTBL
FOREIGN KEY (userID)
REFERENCES userTBL(userID);- ADD CONSTRAINT FK_userTBL_buyTBL : 제약조건을 추가하고 제약 조건이름은 명명
- FOREIGN KEY (userID) : 외래키 제약조건을 구매테이블의 아이디열에 설정
- REFERENCES userTBL(userID) : 참조할 기준 테이블은 회원 테이블의 아이디열
ALTER TABLE buyTBL DROP FOREIGN KEY FK_userTBL_buyTBL; -- 외래 키 제거 ALTER TABLE buyTBL ADD CONSTRAINT FK_userTBL_buyTBL FOREIGN KEY (userID) REFERENCES userTBL (userID) ON UPDATE CASCADE;외래키를 설정할때
ON DELETE CASCADE,ON UPDATE CASCADE옵션을 사용할 수 있음.
이는 기준 테이블의 데이터가 변경되었을 때 외래키 테이블에도 변경된 데이터가 자동으로 적용되도록 설정하는 옵션
4. UNIQUE 제약 조건
UNIQUE 제약조건
- 중복되지 않는 유일한 값을 입력해야하는 조건
- 기본키 제약 조건과 거의 비슷하지만 NULL값을 허용함. 즉, NULL 값이 여러 개 입력돼도 상관없음
5. DEFAULT 제약 조건
DEFAULT 제약조건 : 값을 입력하지 않았을 때 자동으로 입력되는 기본 값을 정의하는 조건
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL PRIMARY KEY,
userName VARCHAR(10) NOT NULL,
birthYear int NOT NULL DEFAULT -1,
addr CHAR(2) NOT NULL DEFAULT '서울',
mobile1 CHAR(3) NULL,
mobile2 CHAR(8) NULL,
height smallint NULL DEFAULT 170,
mDate date NULL
);- 출생연도를 입력하지 않았다면 -1, 주소를 입력하지 않았다면 서울, 키를 입력하지 않았다면 170을 자동으로 입력하는 구문
REATE TABLE userTBL
( userID CHAR(8) NOT NULL PRIMARY KEY,
userName VARCHAR(10) NOT NULL,
birthYear int NOT NULL ,
addr CHAR(2) NOT NULL,
mobile1 CHAR(3) NULL,
mobile2 CHAR(8) NULL,
height smallint NULL,
mDate date NULL
);
ALTER TABLE userTBL
ALTER COLUMN birthYear SET DEFAULT -1;
ALTER TABLE userTBL
ALTER COLUMN addr SET DEFAULT '서울';
ALTER TABLE userTBL
ALTER COLUMN height SET DEFAULT 170;- ALTER TABLE 문에서 DEFAULT 제약 조건 지정할때 ALTER COLUMN 구문 사용
6. NULL 값 허용
테이블을 정의할 때 특정 열에 NULL 값이 입력되는 것을 허용하려면 NULL로 설정하고, 허용하지 않으려면 NOT NULL로 설정하는 조건
NULL값은 '아무것도 없다'는 의미로 0,빈문자,공백 과 다르니 주의
section3 : 테이블 압축과 임시 테이블
1. 테이블 압축
CREATE TABLE normalTBL( emp_no INT , first_name VARCHAR(14));
CREATE TABLE compressTBL( emp_no INT , first_name VARCHAR(14))
ROW_FORMAT=COMPRESSED ;- ROW_FORMAT=COMPRESSED 구문 붙여서 압축되도록
INSERT INTO normalTbl
SELECT emp_no, first_name FROM employees.employees;
INSERT INTO compressTBL
SELECT emp_no, first_name FROM employees.employees;- 결과 : normalTBL보다 compressTBL(압축된 테이블)에 데이터를 입력할 때 시간 더 오래 걸림. 압축하면서 데이터 입력하기 떄문에
- 디스크 공간에 여유가 없거나 대용량 데이터를 저장하는 테이블이라면 테이블 압축하는 것도 좋은 방안
2. 임시 테이블
- 세션 내에서만 존재하며 세션이 닫히면 자동으로 삭제
- 테이블을 생성한 클라이언트만 접근할 수 있음
- 테이블 이름은 데이터베이스 내 다른 테이블의 이름과 동일하게 지을 수 있음
- 이름을 동일하게 하는 경우 임시 테이블이 있는 동안 기존 테이블에 접근할 수 없으며 무조건 임시 테이블에만 접근
CREATE TEMPORARY TABLE [ IF NOT EXISTS ] 테이블이름 (열정의,,)
CREATE TEMPORARY TABLE IF NOT EXISTS tempTBL (id INT, userName CHAR(5));section4 : 테이블 삭제와 수정
1. 테이블 삭제
DROP TABLE 테이블이름;- 외래키 제약 조건에 걸려 있는 기준 테이블은 삭제할 수 없으므로 외래키 테이블을 삭제한 후 기준 테이블 삭제
2. 테이블 수정
열추가
ALTER TABLE userTBL
ADD homepage VARCHAR(30) -- 열추가
DEFAULT 'http://www.hanbit.co.kr' -- 디폴트값
NULL; -- Null 허용함열삭제
ALTER TABLE userTBL
DROP COLUMN mobile1;열 이름 및 데이터 형식 변경
ALTER TABLE userTBL
CHANGE COLUMN userName uName VARCHAR(20) NULL ;열의 제약조건 추가 및 삭제
ALTER TABLE userTBL
DROP PRIMARY KEY; 위 구문 오류 발생. 아래 구문 실행 후 위 구문 실행
ALTER TABLE userTBL
DROP FOREIGN KEY 외래키 이름; section5 : 뷰
1. 뷰의 개요
뷰
- 가상의 테이블
- 실체가 없지만 실체가 있는
2. 뷰 생성
CREATE VIEW v_userTBL
AS
SELECT userID, userName, addr FROM userTBL;
SELECT * FROM v_userTBL; -- 뷰를 테이블이라고 생각해도 무방- 뷰를 테이블로 여기고 접근해도 원래 테이블을 이용해 접근한 것과 동일한 결과 얻을 수 있음
3. 뷰의 장점
1) 뷰는 보안에 도움이 됨
2) 뷰는 복잡한 쿼리를 단순화함
SELECT U.userID, U.userName, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM userTBL U
INNER JOIN buyTBL B
ON U.userID = B.userID ;
🔽
CREATE VIEW v_userbuyTBL
AS
SELECT U.userID, U.userName, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM userTBL U
INNER JOIN buyTBL B
ON U.userID = B.userID ;
SELECT * FROM v_userbuyTBL WHERE userName = '강호동';뷰 실습
1) 뷰 생성
CREATE VIEW v_userbuyTBL
AS
SELECT U.userID AS 'USER ID', U.userName AS 'USER NAME', B.prodName AS 'PRODUCT NAME',
U.addr, CONCAT(U.mobile1, U.mobile2) AS 'MOBILE PHONE'
FROM userTBL U
INNER JOIN buyTBL B
ON U.userID = B.userID;
SELECT `USER ID`, `USER NAME` FROM v_userbuyTBL; -- 주의! 백틱(키보드 1의 왼쪽 키)을 사용한다.2) 뷰 수정,삭제
<뷰 수정>
ALTER VIEW v_userbuyTBL
AS
SELECT U.userID AS '사용자 아이디', U.userName AS '이름', B.prodName AS '제품 이름',
U.addr, CONCAT(U.mobile1, U.mobile2) AS '전화 번호'
FROM userTBL U
INNER JOIN buyTBL B
ON U.userID = B.userID ;
SELECT `이름`,`전화 번호` FROM v_userbuyTBL; -- 주의! 백틱을 사용한다.
<뷰 삭제>
DROP VIEW v_userbuyTBL;3) 뷰 정보 확인
REATE OR REPLACE VIEW v_userTBL
AS
SELECT userID, userName, addr FROM userTBL;4) 뷰 데이터 수정
UPDATE v_userTBL SET addr = '부산' WHERE userID='LKK' ;5) 그룹함수 포함하는 뷰 데이터 수정
CREATE OR REPLACE VIEW v_sum
AS
SELECT userID AS 'userID', SUM(price*amount) AS 'total'
FROM buyTBL GROUP BY userID;
SELECT * FROM v_sum;
SELECT * FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'cookDB' AND TABLE_NAME = 'v_sum';6) 지정한 범위로 뷰 생성하고 데이터 입력
7) 복합 뷰 생성하고 데이터 입력
CREATE OR REPLACE VIEW v_userbuyTBL
AS
SELECT U.userID, U.userName, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS mobile
FROM userTBL U
INNER JOIN buyTBL B
ON U.userID = B.userID ;
INSERT INTO v_userbuyTBL VALUES('PKL','박경리','운동화','경기','00000000000','2020-2-2');8) 뷰가 참조하는 테치블 삭제
DROP TABLE IF EXISTS buyTBL, userTBL;
SELECT * FROM v_userbuyTBL;
열심히 굽고 있어요🍞