Lecture 05, Table, View, Index

테이블 생성 Create 문

• Create, Alter, Drop의 특징
  • mysql 계열에서는 database와 테이블의 생성, 수정, 삭제를 위하여 사용
• Create database의 기본 문법
  • create database 데이터베이스_이름;
  • drop database 데이터베이스_이름;

CREATE TABLE 테이블_이름 ( 
({열이름 데이터유형 [null/not null] [default 기본값], }+) 
[primary key (열이름_리스트), ] 
{[unique (열이름_리스트), ]}* 
{[foreign key (열이름_리스트) 
references 테이블_이름(열이름_리스트), ]}* );

-   [ ] : 생략 가능
-   { } : 반복 가능
-   / : 둘중 하나 선택
-   -   : 1회 이상 반복
-   -   : 0회 이상 반복
-   primary key: pk 설정
-   unique: 대체키, 후보키를 지정하는 명령
-   foreign key: 외래키

    | type(정수형) | 크기 | 의미 |
    | --- | --- | --- |
    | Tinyint | 1byte | \-128~127 , 부호 없이 0~255 |
    | Smallint | 2byte | \-32,768~32,767 , 부호 없이 0~65,535 |
    | Mediaumint | 3byte | \-8,388,608~8,388,607 , 부호없이 0~16,777,215 |
    | Int | 4byte | \-2,147,483,648~2,147,483,647 |
    | Bigint | 8byte |   |
    | Boolean(=tinyint(1)) | 1byte | T/F로 입력하게 되지만 실제로는 1/0으로 처리됨 |

        # decimal 특성 파악하기
        # decimal(전체 길이, 소수점 이하 길이) -> 고정 소수점을 포함한 실수
        # 소수점 밑 0은 생략
        # zerofill: 빈 자리를 0으로 채워줌
        DROP TABLE if EXISTS tbl_test;
        CREATE TABLE tbl_test(testField DECIMAL(9, 2) UNSIGNED ZEROFILL);
        INSERT INTO tbl_test VALUES(7777.21);
        INSERT INTO tbl_test VALUES(23222.2);

        SELECT * FROM tbl_test;

CREATE TABLE tbl_test(testField DECIMAL(9, 2), testBoolean BOOLEAN);
INSERT INTO tbl_test VALUES(7777.21, TRUE);
INSERT INTO tbl_test VALUES(23222.2, TRUE);

SELECT * FROM tbl_test;

type(실수형)	크기	의미
Decimal(m, s) / Numeric(m, s)	m, s에 따라 달라짐, m은 최대 자리수, s는 소수점 이하 길이
Float(m[, s])	4byte	소수점 아래 7자리까지 음수0에 근사한 값 혹은 0에 근사한 값양수
Double(m[, s]) / Real(m[, s])	8byte	소수점 아래 15자리, 음수0에 근사한 값 혹은 0에 근사한 값양수

CREATE TABLE tbl_test(testField FLOAT(9, 4));
INSERT INTO tbl_test VALUES(7777.21);
INSERT INTO tbl_test VALUES(23222.2);

SELECT * FROM tbl_test;

type(문자열)	크기	의미
Char	최대 255문자	고정길이 문자열
Varchar	최대 255문자	가변길이 문자열
Text	최대 65535 문자
Longtext	최대 4gb 또는 4,294,967,295문

type(날짜/시간)	크기	의미
Date	3byte	yyyy-mm-dd (1001-01-01 ~ 9999-12-31)
Time	3byte	hh:mm:ss
Datetime	8byte	yyyy-mm-dd hh:mm:ss
Timestamp	4byte	yyyy-mm-dd hh:mm:ss , time_zone과 관련있음
Year	1byte	yyyy (1901~2155)

CREATE TABLE 과목2 (
    과목번호 CHAR(4) NOT NULL PRIMARY KEY, 
    이름 VARCHAR(20) NOT NULL, 
    강의실 CHAR(5) NOT NULL, 
    개설학과 VARCHAR(20) NOT NULL, 
    시수 INT NOT NULL
);

CREATE TABLE 학생2 (
    학번 CHAR(4) NOT NULL, 
    이름 VARCHAR(20) NOT NULL, 
    주소 VARCHAR(50) NULL DEFAULT '미정', 
    학년 INT NOT NULL, 
    나이 INT NULL, 
    성별 CHAR(1) NOT NULL, 
    휴대폰번호 CHAR(13) NULL, 
    소속학과 VARCHAR(20) NULL, 
    PRIMARY KEY (학번), 
    UNIQUE(휴대폰번호)
);

CREATE TABLE 수강2 (
    학번 CHAR(6) NOT NULL, 
    과목번호 CHAR(4) NOT NULL, 
    신청날짜 DATE NOT NULL, 
    중간성적 INT NULL DEFAULT 0, 
    기말성적 INT NULL DEFAULT 0, 
    평가학점 CHAR(1) null, 
    PRIMARY KEY(학번, 과목번호), 
    FOREIGN KEY(학번) REFERENCES 학생2(학번), 
    FOREIGN KEY(과목번호) REFERENCES 과목2(과목번호)
);

• 생성 스크립트 보는 명령어
  • Show create table 학생2;
• 무결성 제약조건의 위반 여부 확인

INSERT INTO 과목2 (과목번호, 이름, 강의실, 개설학과)
VALUES ('c111', 'database', A-123, '산업공학');

# 강의실은 char(5)로 A-123은 이 형태와 일치하지 않음

INSERT INTO 과목2 (과목번호, 이름, 강의실, 개설학과, 시수)
VALUES ('c111', 'database', 'A-123', '산업공학');

# 시수는 null 값 불가능

INSERT INTO 과목2 (과목번호, 이름, 강의실, 개설학과, 시수)
VALUES ('c111', 'database', 'A-123', '산업공학', 3);

# 정상 수행

INSERT INTO 학생2 (학번, 이름, 학년, 나이, 성별, 휴대폰번호, 소속학과)
VALUES ('s111', '박태환', 4, NULL, '남', '010-1111-1111', '산업공학');

# 정상수행

INSERT INTO 학생2 (학번, 이름, 학년, 나이, 성별, 휴대폰번호, 소속학과)
VALUES ('s222', '박태환', 2, NULL, '남', '010-1111-1111', '산업공학'); 

# UNIQUE 제약조건 오류 ('휴대폰번호' 열 값이 중복)

INSERT INTO 학생2 (학번, 이름, 학년, 나이, 성별, 휴대폰번호, 소속학과)
VALUES ('s222', '박태환', 2, NULL, '남', '010-2222-2222', '산업공학');

# 정상수행

INSERT INTO 수강2 (학번, 과목번호, 신청날짜)
VALUES ('s111', 'c111', '2019-12-31');

#정상수행

INSERT INTO 수강2 (학번, 과목번호, 신청날짜, 중간성적, 기말성적, 평가학점)
VALUES ('s111', 'c222', '2019-12-31', 93, 98, 'A');

# 외래키 제약조건 오류 -> 입력한 과목번호 값이 과목2 테이블에 존재하지 않음

INSERT INTO 수강2 (학번, 과목번호, 신청날짜, 중간성적, 기말성적, 평가학점)
VALUES ('s111', 'c111', '2019-12-31', 93, 98, 'A');

# 기본키 제약조건 오류 -> 기본키 학번과 과목번호 열 조합이 중복값이 존재

INSERT INTO 수강2 (학번, 과목번호, 신청날짜, 중간성적, 기말성적, 평가학점)
VALUES ('s222', 'c111', '2019-12-31', 93, 98, 'A');

# 정상수행

• 테이블 수정 Alter 문
  • #1 수정할 테이블 명 기입
  • #2 ADD는 새로운 필드를 추가하는 명령어이며 modify는 존재하는 필드를 수정하는 명령
  • #3 제약조건이 있는 경우 추가가 제한
  • #4 이미 만들어진 열을 제거
  • #5 제약조건이 참조하는 열이 있는 경우 삭제가 제한될 수 있음

ALTER TABLE 테이블명 #1 
{[ADD/MODIFY] 열이름_데이터유형 [NULL/NOT NULL] [DEFAULT 기본값]} #2 
{ADD CONSTRAINT 제약조건_이름 제약조건_상세내용} #3 
{DROP COLUMN 열이름} #4
{DROP CONSTRAINT 제약조건_이름}; #5

ALTER TABLE 학생2 ADD 등록날짜 DATETIME NOT NULL DEFAULT '2019-12-30';
SELECT * FROM 학생2;

ALTER TABLE 학생2
    DROP COLUMN 등록날짜;

SELECT * FROM 학생2;

• 열의 이름까지 바꾸기 위해서는 modify가 아닌 change 명령어를 사용해야 함
  • alter table 테이블 이름 change 기존열이름 새로운열이름 열유형;
• 테이블 이름 변경 시 rename to를 사용
  • alter table 테이블이름 rename to 새로운테이블이름;
• Drop table 문
  • 테이블 삭제 시 참조하는 테이블이 있는 경우 참조 테이블 먼저 삭제해야 함
  • DROP TABLE 과목2;

• DROP TABLE 테이블명;

DROP TABLE 수강2;  # 먼저 삭제 후
DROP TABLE 과목2;  # 삭제!!!!

SELECT * FROM 과목2;
DESC 과목2;

사용자 권한 및 관리 설정

• Create user
  • user1, 2는 localhost일 때만 접속이 허용, 비밀번호는 각각 1111, 2222
  • user3은 해당 IP에서만 접속 허용, 비밀번호는 3333
  • user4에게는 어느 호스트에서 들어오던 접속과 권한을 주지 않음

CREATE USER 'user1'@'127.1.1.1' IDENTIFIED BY '1111';
CREATE USER 'user2'@'localhost' IDENTIFIED BY '2222';
CREATE USER 'user3'@'192.182.10.2' IDENTIFIED BY '3333';
CREATE USER 'user4'@'%' IDENTIFIED BY '4444';

• Select user();
  • 현재 접속 사용자 확인
• select * from user;
  • 전체 리스트 보기
• 권한부여
  • GRANT문: 지정한 사용자 계정에 권한 지정
  • with grant option을 이용하면 허가를 받은 권한을 다른 계정 사용자에게도 부여할 수 있는 권한을 받음

    CREATE USER 'user1'@'127.1.1.1' IDENTIFIED BY '1111'; 
    CREATE USER 'user2'@'localhost' IDENTIFIED BY '2222'; 
    CREATE USER 'user3'@'192.182.10.2' IDENTIFIED BY '3333'; 
    CREATE USER 'user4'@'%' IDENTIFIED BY '4444';

GRANT INSERT, UPDATE, DELETE ON univdb.\* TO 'user1'@'127.1.1.1';  
GRANT ALL ON _._ TO 'user4'@'%' WITH GRANT OPTION;  
GRANT SELECT ON univDB.학생 TO 'user2'@'localhost';

SHOW GRANTS FOR 'user1'@'127.1.1.1';

# user1의 사용자 권한 보여줌 SHOW GRANTS;

# 현재 접속 사용자의 권한 표시

• 권한의 철회
  • revoke 문의 형식
    • 허가된 권한을 다시 철회/제거할 때 사용

    REVOKE DELETE ON univDB.`*` FROM 'user1'@'127.1.1.1';
    
    SHOW GRANTS FOR 'user1'@'127.1.1.1';

• 계정의 삭제
  • Dorp user
  • DROP USER 'user1'@'127.1.1.1';
  • Root 권한을 가지고 있는 경우 delete from user where user=’id’로도 가능 함

View

• 실제 데이터를 저장하지 않는 가상 테이블 = 데이터베이스를 바라보는 창문
• 뷰에 대해 사용자가 질의를 요청하면, DBMS는 뷰 정의를 참조해 질의를 수행하고 그 결과를 반환
• 주로 기반 테이블로부터 정의되지만 또 다른 뷰를 기반으로 정의할 수도 있음
• 장점
  • 편의성: 복잡한 질의문 작성이 쉽고 간단해짐
  • 보안성: 데이터 보안 유지가 쉬움
  • 재사용성: 반복되는 질의문 작성에 효율적임
  • 독립성: 스키마 변경에도 뷰 질의문은 변경할 필요가 없음

CREATE VIEW V1_고학년학생(학생이름, 나이, 성, 학년)
AS SELECT 이름, 나이, 성별, 학년
    FROM 학생
    WHERE 학년 >= 3 AND 학년 <= 4;

SELECT *
FROM V1_고학년학생;

CREATE VIEW V2_과목수강현황(과목번호, 강의실, 수강인원수)
AS SELECT 과목.과목번호, 강의실, COUNT(과목.`과목번호`)
    FROM 과목 JOIN 수강 ON 과목.과목번호 = 수강.과목번호 
    GROUP BY 과목.과목번호; 

SELECT *
FROM V2_과목수강현황;

CREATE VIEW V3_고학년여학생
AS SELECT *
    FROM V1_고학년학생
    WHERE 성="여";

SELECT * FROM V3_고학년여학생;

SELECT * FROM V1_고학년학생 WHERE 성="여";

# 이 둘은 결국 같은 것을 의미 함 

SELECT 이름 AS '학생이름', 나이, 성별 AS '성', 학년
FROM 학생
WHERE 학년 >= 3 AND 성별 = "여";

SELECT * FROM V3_고학년여학생;

# 이 둘은 같은 것을 의

SELECT 이름 AS '학생이름', 나이, 성별 AS '성', 학년
FROM 학생
WHERE 학년 >= 3 AND 성별 = '여';

SELECT *
FROM V2_과목수강현황
WHERE 수강인원수 = (SELECT MAX(수강인원수) FROM V2_과목수강현황) OR
        수강인원수 = (SELECT MIN(수강인원수) FROM V2_과목수강현황);

• 뷰를 통한 데이터 변경
  • 뷰 검색은 제한 없이 가능한 반면 뷰 변경은 특정한 경우에만 가능
    • 기반 테이블의 pk를 포함하지 않은 뷰
    • 집계 함수(계산)결과를 포함하는 뷰
    • distinct를 적용하거나 group by를 적용한 경우
    • 다수의 기반 테이블의 조인을 통해 생성된 뷰
    • 뷰에 포함되지 않는 기반 테이블의 열이 not null인 경우
  • 뷰 변경이 제한되는 이유
    • 뷰를 정의하는 ‘select 검색문’의 조건이 다양하기 때문
    • 뷰가 참조하는 기반 테이블을 변경해야 함 → 내부 처리가 어려운 경우는 변경이 허용되지 않음
• 뷰 삭제
• DROP VIEW V1_고학년학생; SHOW TABLES;

인덱스

• 테이블 안의 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터베이스 객체
• 책 페이지를 목차나 색인을 통해 쉽게 찾는 것과 같은 원리
• 필요성 및 목적
  • SQL을 실행할 때 디스크 접근 횟수를 줄여 검색 속도를 높이기 위함
  • 대부분 DBMS는 B-트리 구조의 인덱스를 지원
  • 루트 노드로부터 리프노드까지의 탐색 길이가 같아서 모든 데이터에 대한 일정 수준의 검색시간을 보장
• CREATE INDEX
  • REVERSE 인덱스를 역순으로 생성
  • UNIQUE 테이블의 열값에 중복이 없는 유일한 인덱스를 만드는 명령(해당 열에서 중복된 값이 허용되지 않음)
  • ASC/DESC 열값의 정렬방식을 지정

CREATE INDEX idx_수강 ON 수강(학번, 과목번호); 
CREATE UNIQUE INDEX idx_과목 ON 과목(이름 ASC); 
SHOW INDEX FROM 수강;

• DROP INDEX
  • alter table을 활용해 index 삭제 가능
  • DROP INDEX idx_수강 ON 수강; ALTER TABLE 과목 DROP INDEX idx_과목;
• 주의사항
  • 불필요한 인덱스는 오히려 성능을 떨어뜨리고 저장공간만 낭비
  • 인덱스는 꼭 필요한 만큼만 효과적으로 생성해야 함
  • 하나의 테이블에는 3-5개 정도의 인덱스 생성을 권장
  • 인덱스 생성이 필요한 경우
    • 기본키와 외래키(대부분의 dbms는 자동으로 인덱스 생성)
    • where이나 조인 조건식 또는 order by나 group by에 자주 사용되는 테이블 열
    • 가변길이 문자형이나 실수형, 날짜형 열보다는 정수형, 고정길이 문자형
  • 인덱스 생성이 불필요한 경우
    • 갱신이 빈번한 테이블 열의 경우 인덱스가 바람직하지 않음
    • 집계함수, 내장함수를 적용하여 열값을 변형하는 경우
    • ‘성별’같은 열처럼 도메인이 작아서 열의 선택도가 높을 경우
    • 범위 검색을 하는 경우
    • 테이블의 행 개수가 별로 없는 경우