-------------------------------------------------------------------
[스키마 설계]
하나의 테이블에 두개의 이름을 지정하는 경우
물리명 : CREATE TABLE '물리명' (item_master) ##실제 컬럼명 item_code
논리명 : 물리명을 설명참조 ex item_code (논리명 :상품 코드)
-------------------------------------------------------------------
고정길이 : CHAR 예) 주민번호 , 휴대폰 번호 등등
가변 문자형 : VARCHAR 변동폭이 클 경우
LOB(LARGE OF OBJECT) : 큰데이터 , 인덱스 지정불가능 !
-------------------------------------------------------------------
AUTO_INCREMENT를 지정할때는
PRIMARY, UNIQUE에만 가능하다 . UNIQUE와 PRIMARY 차이점은 NULL
PRIMARY는 NULL 값 X // UNIQUE는 NULL값 지정 가능
-------------------------------------------------------------------
ER다이어그램
ER DIAGRAM은 개체 간의 연계를 표현한것
테이블을 설계할 때 테이블간의 관계를 명확히 하기 위함
FOREIGN키 설정한것을 보여줌
-------------------------------------------------------------------
<정규화 실습 예제>
쇼핑 사이트 주문처리 시스템 데이터베이스 구축
[실습] 테이블 설계 하기
-------------------------------------------------------------------
< 테이블 압축 >
테이블 압축이란 ? 대용량 테이블의 공간을 절약하는것
실습 예제 __)
CREATE DATABASE IF NOT EXISTS compressDB;
USE compressDB;
CREATE TABLE normalTBL(emp_no int, first_name VARCHAR(14));
CREATE TABLE compressTBL(emp_no int, first_name VARCHAR(14))
ROW_FORMAT = COMPRESSED; --> 테이블 압축 하기
-------------------------------------------------------------------
두테이블에 데이터 30만건 입력하기
INSERT INTO normalTBL
SELECT emp_no,first_name FROM employees.employees;
INSERT INTO compressTBL
SELECT emp_no,first_name FROM employees.employees;
-------------------------------------------------------------------
<두 테이블 상태 확인>
SHOW TABLE STATUS FROM compressDB;
--실습한 DB 제거
DROP DATABASE IF EXISTS compressDB;
-------------------------------------------------------------------
임시로 잠깐 사용되는 테이블
CREATE TEMPORARY TABLE [IF NOT EXISTS] 테이블이름
(열 정의 . . . )
세션내에서만 존재
세션이 닫히면 자동삭제
생성한 클라이언트에서만 접근 가능
-다른 클라이언트에는 접근 불가
임시테이블 삭제 시점
-사용자가 DROP
워크 벤치 종료 ,재시작 쿼리 클라이언트 종료시 삭제
-------------------------------------------------------------------
CREATE TEMPORARY TABLE IF NOT EXISTS temptbl(id INT name CHAR(5));
<실습 >
use employees;
create temporary table if not exists temptbl(id INT, name CHAR(5));
create temporary table if not exists employees(id INT, name char(5));
desc temptbl;
desc employees;
select * from temptbl;
select * from employees;
insert into temptbl values(1, 'this');
insert into employees values(2, 'mysql');
<실습 임시 테이블 삭제 >
DROP TABLE temptbl;
------------------------------------------------------------------
<테이블 삭제 하는방법>
DROP TABLE 테이블명;
외래키 제약 조건의 기준 테이블은 삭제할 수 없음
-먼저 외래기가 생성된 외래 키 테이블을 먼저 삭제해야함
-ex) 구매 테이블(참조)이 존재하는데 회원 테이블이 삭제 될 수 없음.
동시에 여러 테이블 삭제 가능
DROP TABLE 테이블1,테이블2,테이블3;
------------------------------------------------------------------
ALTER TABLE 사용
열 추가
-기본적으로 가장 뒤에 추가
-순서를 지정하려면 제일 뒤에 'FIRST' 또는 'ALTER 열 이름 지정'
ex)회원 테이블(userTBL)에 회원 홈페이지 주소 추가
USE tabledb;
ALTER TABLE usertbl
ADD homepahe VARCHAR(30)
DEFAULT 'http://naver.com' -- 디폴트 값
NUll; - NULL 허용
------------------------------------------------------------------
-열 삭제
ALTER TABLE usertbl
DROP COLUMN mobile; -열 삭제
제약조건이 걸린 열 삭제 할땐 먼저 제약조건 삭제후 열 삭제
------------------------------------------------------------------
<열의 이름 및 데이터 형식 변경 >
ALTER TABLE usertbl
change column name newName varchar(20) null;
제약 조건 삭제 할땐
ALTER TABLE usertbl
DROP PRIMARY KEY;
------------------------------------------------------------------
<인덱스 INDEX>
책의 <찾아보기> 개념과 비슷하다
데이터를 좀 더 빠르게 찾을 수 있도록 해주는 도구
장점 : 검색속도가 빨라질 수 있
쿼리의 부하가 줄어들어 시스템 전체의 성능향상
단점 : 인덱스가 데이터베이스 공간을차지 (약 10%)
처음 인덱스 생성하는데 시간 소요
데이터의 변경 작업(INSERT,UPDATE,DELETE)이 자주 일어나는
경우 성능이 나빠질 수 있음
[클러스터형 인덱스]
'영어사전'과 같은 책
테이블당 한개만 지정 가능
행 데이터를 인덱스로 지정한 열에 맞춰 자동정렬
[보조 인덱스]
책 뒤에 <찾아보기>가 있는 일반 책
테이블당 여러개로 생성가능
------------------------------------------------------------------
[인덱스 구조]
INDEX(EMPNO) TABLE(EMP)
SELECT empno, ename, job
FROM emp
WHERE empno > 7800;
------------------------------------------------------------------
<자동으로 생성되는 인덱스>
[실습] 아래 코드 실행 후 결과 확인 해보기
CREATE TABLE tbl1
(a INT PRIMARY KEY,
b INT,
c INT);
인덱스 상태 확인 : SHOW INDEX FROM 테이블명;
SHOW INDEX FROM tbl1;
#PRIMARY키가 들어간곳이 클러스터형 인덱스가 자동 생성된다.
워크벤치에서
Non_unique 가 0 이면 유니크형 인덱스
1이면 유니크형 인덱스가 아님
KEY_name
--INDEX_name과 같은 의미 . 인덱스 이름
--PRIMARY는 클러스터형 인덱스, 열의 이름이나 키 이름으로 표기되면 보조 인덱스
seq_in_index : 해당열에 여러개의 인덱스가 설정되었을때의 순서. 대부분 1
NULL: NUll값의 허용여부 비어있으면 NO !
------------------------------------------------------------------
[실습] 테이블 tbl2 생성
CREATE TABLE tbl2
(a INT PRIMARY KEY,
b INT UNIQUE,
c INT UNIQUE,
d INT);
SHOW INDEX FROM tbl2;
--> a는 PRIMARY키로 설정,
b 와 c 는 유니크형 인덱스, Null값 yes로 설정 된다.
------------------------------------------------------------------
[실습]
CREATE TABLE tbl3
(a INT UNIQUE,
b INT UNIQUE,
c INT UNIQUE,
d INT);
SHOW INDEX FROM tbl3;
--> a,b,c 모두 유니크형 인덱스 NULL 값은 YES로설정
------------------------------------------------------------------
[실습 테이블 4 생성]
CREATE TABLE tbl4
(a INT UNIQUE NOT NULL,
b INT UNIQUE,
c INT UNIQUE,
d INT);
SHOW INDEX FROM tbl4;
--> a,b,c 모두 유니크형 인덱스
A의 NULL 값은 NO로설정 B,C는 Null 값 YES
------------------------------------------------------------------
[실습]
CREATE TABLE tbl5
(a INT UNIQUE NOT NULL,
b INT UNIQUE,
c INT UNIQUE,
d INT PRIMARY KEY);
SHOW INDEX FROM tbl5;
--> PRIMARY키 d열이 우선순위로 지정 클러스터형 인덱스가
되고,
나머지는 '보조인덱스' 역할을 한다.
------------------------------------------------------------------
[실습 회원 테이블의 열만 정의]
DROP TABLE IF EXISTS usertbl;
CREATE TABLE usertbl
(userID char(8) NOT NULL PRIMARY KEY,
name varchar(10) NOT NULL,
birthYear int NOT NULL,
addr varchar(2) NOT NULL);
SHOW INDEX FROM usertbl;
-->보조 인덱스는 없고 클러스터형 인덱스만 생성
(PRIMARY)
-->USER ID열을 PRIMARY키로 지정
------------------------------------------------------------------
INSERT INTO usertbl VALUES('LSG','이승기',1987,'서울');
INSERT INTO usertbl VALUES('KBS','김범수',1927,'서울');
INSERT INTO usertbl VALUES('KKH','김경호',1937,'서울');
INSERT INTO usertbl VALUES('JYP','제와피',1887,'서울');
INSERT INTO usertbl VALUES('SSK','생시경',1947,'서울');
SELECT * FROM usertbl;
------------------------------------------------------------------
[실습]
ALTER TABLE usertbl DROP PRIMARY KEY;
ALTER TABLE usertbl
ADD CONSTRAINT pk_name PRIMARY KEY(name);
SELECT * FROM usertbl;
기존의 USERID PRIMARY키 제거후,
-->NAME 열을 PRIMARY키로 지정
자동으로 가나다 순으로 오름차순 정렬된다.
------------------------------------------------------------------
인덱스 특징
PRIMARY키로 지정한 열은 클러스터형 인덱스 생성
UNIQUE NOT NULL로 지정한 열은 클러스형 인덱스 생성
------------------------------------------------------------------
<검색 방법>
Full Table Screen --> 오래 걸린다. SELECT WHERE문
Binary TREE -->
중심값에서 큰지 작은지 확인
PRIMARY키로 정렬을 시켜놓은후,기준의 중심값에서
큰지 작은지 확인후 찾는다.
(정렬이 되어있다는 전제)
ex)
WHERE no =23
BALANCE TREE
노드: 데이터가 존재하는 공간 (1 node = 16kbyte)
루트노드 : 노드의 출발점
리프노드: 노드의 끝점
총 9행짜리가 있으면 총 3,3,3으로 쪼개는걸 리프노드
제일 위에 있는 행을 대표행으로 정한후
루트노드로 지정을한다.
------------------------------------------------------------------
페이지 분할 인덱스의 내부작동
인덱스 구성시 SELECT문의 효율성이 향상될 수 있음
인덱스 구성시 INSERT 문이 일어날 경우 속도저하되는 단점
주어진 공간이상으로 데이터락 들어가면 페이지 분할 일어남
------------------------------------------------------------------
인덱스 없는 테이블의 예시
-->
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
DROP TABLE IF EXISTS clustertbl;
CREATE TABLE clustertbl -- CLUSTER Table
(USERID CHAR(8),
name VARCHAR(10)
);
INSERT INTO clustertbl VALUES('LSG','이승기');
INSERT INTO clustertbl VALUES('KBS','김범수');
INSERT INTO clustertbl VALUES('KKH','김경호');
INSERT INTO clustertbl VALUES('JYP','조용필');
INSERT INTO clustertbl VALUES('SSK','성시경');
INSERT INTO clustertbl VALUES('LJB','임재범');
INSERT INTO clustertbl VALUES('YJS','윤종신');
INSERT INTO clustertbl VALUES('EJW','은지원');
INSERT INTO clustertbl VALUES('JKW','조관우');
INSERT INTO clustertbl VALUES('BBK','바비킴');
------------------------------------------------------------------
클러스터형 인덱스 구성한 테이블 구조
USERID를 PRIMARY키로 지정하면 클러스터형 인덱스로 구성됨
ALTER TABLE clustertbl
ADD CONSTRAINT PK_clustertbl_userID
PRIMARY KEY (userID);
--> USER ID로 지정할 경우 스펠링 순서로 정렬된다.
------------------------------------------------------------------
<보조 인덱스를 구성한 테이블 구조 인덱스 내부작동>
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
DROP TABLE IF EXISTS secondarytbl;
CREATE TABLE secondarytbl -- secondaryTable
(USERID CHAR(8),
name VARCHAR(10)
);
INSERT INTO secondarytbl VALUES('LSG','이승기');
INSERT INTO secondarytbl VALUES('KBS','김범수');
INSERT INTO secondarytbl VALUES('KKH','김경호');
INSERT INTO secondarytbl VALUES('JYP','조용필');
INSERT INTO secondarytbl VALUES('SSK','성시경');
INSERT INTO secondarytbl VALUES('LJB','임재범');
INSERT INTO secondarytbl VALUES('YJS','윤종신');
INSERT INTO secondarytbl VALUES('EJW','은지원');
INSERT INTO secondarytbl VALUES('JKW','조관우');
INSERT INTO secondarytbl VALUES('BBK','바비킴');
--> 원본으로 출력된다.
------------------------------------------------------------------
[실습] UNIQUE 제약을 걸기
ALTER TABLE secondarytbl
ADD CONSTRAINT PK_secondarytbl_userID
UNIQUE (userID);
SHOW VARIABLES LIKE 'innodb_page_size'; --> KB표시
보조인덱스를 사용하더라도 원본 그대로 출력이 된다.
우리 눈엔 보이지 않지만 3페이지로 쪼개져있다.
