테이블

제약 조건 Constraint

데이터의 무결성을 지키기 위해 제한하는 조건

대부분의 DBMS는 무결성을 보장하기 위해 다음과 같은 제약 조건 제공

기본키 제약 조건
외래키 제약 조건
UNIQUE 제약 조건
DEFAULT 제약 조건
NULL 값 허용

테이블 값 수정 방법

1)
DROP TABLE IF EXISTS USERTBL;
CREATE TABLE USERTBL 
(USERID CHAR(8) NOT NULL PRIMARY KEY,
USERNAME VARCHAR(10) NOT NULL,
BIRTHYEAR INT NOT NULL);
DESCRIBE USERTBL;

2)
DROP TABLE IF EXISTS USERTBL;
CREATE TABLE USERTBL 
(USERID CHAR(8) NOT NULL,
USERNAME VARCHAR(10) NOT NULL,
BIRTHYEAR INT NOT NULL,
CONSTRAINT PRIMARY KEY PK_USERTVL_USERiD(USERID));

3)
DROP TABLE IF EXISTS USERTBL;
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);
DESCRIBE USERTBL;

기본키 제약 조건

2개 이상의 열을 합쳐서 하나의 기본키로 설정하는 경우

외래키 제약 조건

두테이블 사이의 관계를 선언함으로써 데이터 무결성을 보장

유니크,PK키여야함

외래키 제약 조건

ON DELETE CASCADE

ON UPDATE CASCASE 옵션 사용

DEFAULT 제약조건

DEFAULT 값 주기 + 변경하기

DEFAULT 값 주기 + 변경하기

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

ALTER TABLE USERTBL
	ALTER COLUMN BIRTHYEAR SET DEFAULT -1;

NULL 값 허용

테이블을 정의할 때 NULL 입력 허용 가능

0, 빈문자, 공백과 다르니 주의해야 함

테이블 압축

CREATE DATABASE IF NOT EXISTS COMPRESSDB;
USE COMPRESSDB;
CREATE TABLE NORMALTBL(EMP_NO INT, FIRST_NAME VARCHAR(14));
CREATE TABLE COMPRRESSTBL(EMP_NO INT, FIRST_NAME VARCHAR(14)) ROW_FORMAT=COMPRESSED;

INSERT INTO NORMALTBL SELECT EMP_NO, FIRST_NAME FROM employees.EMPLOYEES;
INSERT INTO COMPRRESSTBL SELECT EMP_NO, FIRST_NAME FROM employees.EMPLOYEES;

시간 비교

3	49	09:42:59	INSERT INTO NORMALTBL SELECT EMP_NO, FIRST_NAME FROM employees.EMPLOYEES	300024 row(s) affected Records: 300024 Duplicates: 0 Warnings: 0	2.859 sec
3	50	09:43:12	INSERT INTO COMPRRESSTBL SELECT EMP_NO, FIRST_NAME FROM employees.EMPLOYEES	300024 row(s) affected Records: 300024 Duplicates: 0 Warnings: 0	4.984 sec

SHOW TABLE STATUS FROM COMPRESSDB;
DROP DATABASE IF EXISTS COMPRESSDB;

VIEW

가상의 테이블

뷰의 작동 방식

뷰를 테이블로 여기고 접근해도 원래 테이블을 이용하여 접근한 것과 동일한 결과를 얻을 수 있음

뷰는 읽기 전용이지만, 뷰를 통해 원래 테이블의 수정도 할 수 있다 > 권고사항은 아니다

원본 테이블을 지우면 뷰 테이블도 불러올 수 없다

뷰의 장점

보안에 도움이 됨
복잡한 쿼리를 단순화 해줌

SELECT U.USERID, U.UASERNAME, 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.UASERNAME, B.PRODNAME, U.ADDR, CONCAT(U.MOBILE1, U.MOBILE2) AS '연락처'
	FROM USERTBL U
		INNER JOIN BUYTBL B
			ON U.USERID = B.USERID;
 
SELECT * FROM USERTBL WHERE USERNAME = '강호동';

USE SQLDB;
CREATE VIEW V_USERBUYTBL
AS
SELECT U.USERID AS 'USERID', U.UASERNAME AS 'USERNAME', B.PRODNAME AS 'PROUDUCT NAME', U.ADDR, CONCAT(U.MOBILE1, U.MOBILE2) AS 'MOBILE PHONE'
	FROM USERTBL U
		INNER JOIN BUYTBL B
			ON U.USERID = B.USERID;
SELECT `USERID`, `USER NAME` FROM V_USERBUYTBL;  --백틱 사용해야함!!!!

DROP VIEW 뷰이름;
CREATE OR REPLACE VIEW 뷰이름
AS
	SELECT * FROM 기존 테이블;
SHOW CREATE VIEW 뷰이름;

UPDATE V_USERTBL SET ADDR = '부산' WHERE USERID='LKK';
INSERT INTO V_USERTBL (USERID, NAME, ADDR) VALUES ('KBM','김병만','충북');

잘 돌아감

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;

뷰를 통해서는 데이터를 삽입, 수정, 삭제할 수 없다

할 수 없는 경우

SUM등 집계함수 사용뷰
UNION, ALL, JOIN을 사용한 뷰
DISTINCT,~ 등을 사용한 뷰

CREATE OR REPLACE VIEW V_HEIGHT180
AS
	SELECT * FROM USERTBL WHERE HEIGHT >= 180;

SELECT * FROM V_HEIGHT180;

//이미 이렇게 만들어서 아래는 없음, 원본에는 있음
DELETE FROM V_HEIGHT WHERE HEIGHT < 180;