이코리아텍 실습과 함께하는 데이터베이스 MYSQL 강의 정리2/3

스키마수정

ALTER TABLE ADD COLUMN 컬럼명 컬럼타입

ALTER TABLE DROP COLUMN 컬럼명

ALTER TABLE CHANGE 원컬럼명 새로운컬럼명 컬럼타입

ALTER TABLE MODIFY 컬럼명 컬럼타입

ALTER TABLE ADD PRIMARY KEY (컬렴명)

ALTER TABLE DROP PRIMARY KEY 컬럼명

ALTER TABLE ADD UNIQUE (컬럼명)

ALTER TABLE ADD CONSTRAINT 제약명 UNIQUE (컬럼명1, 컬럴럼명2)

ALTER TABLE DROP UNIQUE

ALTER TABLE ADD FOREIGHN KEY 컬럼명 REFERENCES 원컬럼명

ALTER TABLE DROP FOREIGHN KEY

ALTER TABLE 테이블명 RENAME 새로운 테이블명

ALTER TABLE ADD CHECK 조건

ALTER TABLE ADD CONTRAINT 조건명 CHECK 조건

ALTER TABLE DROP CHECK

ALTER TABLE 테이블명 ALTER 컬럼명 SET DEFAULT 기본값

ALTER TABLE 테이블명 ALTER 컬럼명 DROP DEFAULT

스키마 삭제

DROP DATABASE 데이터베이스명

테이블삭제

1)DROP TABLE 테이블명 

-> 전체(스키마와 내용) 삭제

2)DELETE * FROM 테이블명 

-> 스키마는 유지 내용은 삭제(레코드를 하나씩 지운다 = 범용명령어)

3)TRUNCATE TABLE 테이블명

-> 내용만 지음 스키마는 유지(전용명령어=빠름)

DCL

-권한 및 역할 설정하는 언어

-GRANT(부여)와 REVOKE(해제)

MYSQL의 유저관련 정보는

USE MYSQL

SELECT USER, HOST FROM USER;

에서 정보가 있다

MYSQL 유저등록하는 방법

CREATE USER  아이디@호스트주소 IDENTIFIED BY 비번

MYSQL 사용자 삭제

DROP USER 아이디@호스트주소

사용자 삭제 반영하기

FLUSH PRIVILEGES;

권한 부여 해제관련

예시)

특정유저의 모든 권한을 보기

SHOW GRANT FOR 아이디@LOCALHOST;

LOCALHOST에서만 접속권한 부여

GRANT ALL ON 데이터베이스명.* TO 아이디@LOCALHOST IDENTIFIED BY 비밀번호;

로컬랜(공유기내 방화벽내)에서만 접속권한 부여

GRANT ALL ON 데이터베이스명.* TO 아이디@공유기주소 IDENTIFIED BY 비밀번호;

GRANT ALL ON 데이터베이스명.* TO 아이디@192.168.0.% IDENTIFIED BY 비밀번호;

인터넷전체에서 접속권한 부여

GRANT ALL ON 데이터베이스명.* TO 아이디@% IDENTIFIED BY 비밀번호;

검색/추가 권한만 부여

GRANT SELECT, INSERT ON 데이터베이스명.* TO 아이디@LOCALHOST IDENTIFIED BY 비밀번호;

특정테이블의 특정컬럼의 업데이트 권한만 부여

GRANT UPDATE(특정컬럼) ON 데이터베이스명.테이블명 TO 아이디@LOCALHOST IDENTIFIED BY 비밀번호;

모든 권한을 삭제

REVOKE ALL ON *.* FROM 아이디@LOCALHOST;

역할관련

MYSQL은 ROLE(역할) 관련된 내용이 없음

GRANT의 문제점을 해결하기 나온 개념이 ROLE(역할)이다

GRANT의 문제점

테이블수*사용자수의 조합*CRUD 만큼의 갯수만큼 종류가 굉장히 많아진다

즉, 관리 포인트가 많다

ROLE(역할)의 개념

역할별로 권한을 설정하고 사용자에게 역할을 부여하는 형태로 사용

사용자가 여러개의 롤을 가지는 것이 가능함

예시)

CREATE ROLE 역할명

GRANT INSERT, DELETE ON 테이블명 TO 역할명

GRANT 역할 TO 사용자명

MYSQL 원격접속 설정

1)사용자를 원격사용자로 등록

GRANT ALL PRIVILEGES ON 데이터베이스명.테이블명 TO 아이디@% IDENTIFIED BY 비밀번호

2)MY.INI 또는 MY.CNF파일 수정(BIND-ADDRESS부분 주석처리)

3)MYSQL 서버 재시작

4)방화벽 3306 포트 열기

검색성능개선을 위한 INDEX

인덱스개념

-검색을 빠르게 하기 위한 자료구조(주로 B트리 계열)

레코드 추가/수정/삭제시 해당 컬럼으로 다시 정렬한 후 검색에 필요한 값들을 미리 계산해 검색속도를 높임

-기본키는 자동으로 인덱스 설정

-조인에도 인덱스가 영향을 줌

빠른검색을 위해서는 정렬(쿽정렬, 힙정렬 주로 사용)이 되어있어야 함

인덱스 종류

-이진탐색

-B트리(상용DBMS에서 가장 일반적으로 많이 사용됨)

인덱스 설정

예시)

CREATE INDEX 인덱스명 ON 테이블명(컬럼명)

CREATE INDEX 인덱스명 ON 테이블명(컬럼명1,컬럼명2,...)

CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명)

ALTER TABLE 테이블명 DROP INDEX 인덱스명

메타데이터

:DB, 테이블의 스키마에 대한 정보를 저장하는 테이블

메타데이터의 종류

1)데이터 사전(INFORMATION_SCHEMA)

일반적으로 읽기전용

데이터베이스의 정보저장

데이터의 데이터(DB스키마)가 저장되는 데이터 베이스

2)데이터 디렉토리

DBMS의 모든 데이터가 저장되는 디렉토리

DB저장, 상태 및 로그저장

****my.ini에서 디렉토리를 변경할 수 있음

예시)

SHOW DATABASES;

SHOW TABLES;

SHOW TABLE STATUS;

SHOW COULUMNS FROM 테이블명;

SHOW INDEX FROM 테이블명;

SHOW TABLES IN INFORMATION_SCHEMA;

DESCRIBE INFORMATION_SCHEMA.CHARACTER_SET

USE INFROMATION_SCHEMA;

SHOW TABLES;

1)CHARACTER_SETS

2)COLLATION

3)COLLATION_CHARATER_SET_APPLICABILITY

4)COLUMNS

5)COLUMN_PRIVILEGES

6)KEY_COLUMN_USAGE

7)REFERENTIAL_CONSTRAINTS(외래키)

8)ROUTINES

9)SCHEMATA

10)STATISTICS

11)TABLES

12)TABLE_CONSTRAINTS

13)TRIGGERS

14)USER_PRIVILEGES

15)VIEWS

캐릭터셋

:문자인코딩 정보/메타데이터의 일종

:데이터베이스/테이블별로 별도 설정 가능

예시)

ASCII/ISO-8859-1   아스키계열

EUC-KR/KSC_5601    한글 완성형 계열

UTF-8/UNICODE      유니코드계열

UTF-8(기본)

콜레이션

:데이터를 정렬(문자간의 비교)할때 사용하는 정보

:정렬시에 대소문자를 구분(CASE SENSITIVE)/비구분여부(CASE INSENSITIVE) 설정

예시)

UTF8-GENERAL-CI(기본값)

여기서 CI가 CASE INSENSITIVE임

예시)

MYSQL 기본 캐릭터셋 확인

STATUS;

OR

SHOW VARIABLES LIKE CHARACTER_SET_SYSTEM;

예시)

WORLD/CITY 캐릭터셋/콜레이션을 각각 UTF8/UTF8_GENERAL_CI로 설정

ALTER DATABASE WORLD CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

ALTER TABLE CITY CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

예시)

SAMPETESTDB를 UTF8/UTF8_GENERAL_CI로 지정하고 생성하시오

CREATE DATABASE SAMPLETESTDB DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

스토리지 엔진

:인덱스와 유사

MYSQL의 여러엔진중에 스토리지엔진이라는 것이 있음

외래키를 이용하려면 엔진의 기능중에 외래키 지원하는 엔진을 써야함

대표적인 엔진은 MyISAM과 InnoDB가 있음

데이터 접근속도/안정성/트랜잭션의 지원 여부 등의 차이가 있음

기본값은 InnoDB 임

InnoDB - 트랜잭션 지원 / 업데이트 위주 / 줄단위락 / 복구용이 / 동시처리성 높음

MyISAM - 상대적으로 높은 성능 / 읽기 위주 / 테이블단위 락

예시)

MySQL 기본 스토리지 엔진 확인

SELECT ENGINE, SUPPORT FROM INFORMATION_SCHEMA.ENGINES WHERE SUPPORT='DEFAULT';

예시)

기본 스토리지 엔진을 변경

SET DEFAULT_STORAGE_ENGINE=MyISAM;(리부팅시 리셋)

OR

my.ini변경(리부팅시에도 계속 적용)

[mysqld]default-storate-engine=MyISAM

데이터베이스 백업(콘솔창에서)

1)전체 데이터베이스 백업

mysqldump -u아이디 -p --all-databases > 덤프파일명.sql

2)특정 데이터베이스 백업

mysqldump -u아이디 -p --databases DB명 > 덤프파일명.sql

3)특정 테이블 백업(데이터포함)

mysqldump -u아이디 -p DB명 테이블명 > 덤프파일명.sql

4)스키마만 백업

mysqldump -u아이디 -p --no-data world(DB명) country(테이블명) > country_schema.sql

5)데이터만 백업

mysqldump -u아이디 -p --no-create-info world country > country_data.sql

데이터베이스 백업(워크벤치에서)

1)Data Export -> Export Options -> Export to Self-Contained File

2)Tables to Export

3)테이블 선택

4)Dump Structure and Data/Structure Only/Data Only

5)Export 버튼

데이터베이스 복원(콘솔)

mysql -u아이디 -p DB명 < 파일명

mysql -u아이디 -p < 파일명

mysql -u아이디 -p DB명 테이블명 < 파일명

데이터베이스 복원(prompt)

SQL>source 파일명

데이터베이스 복업(워크벤치)

1)Data Import -> Import from Self-Contained File

2)Default Schema to be imported to DB명

3)Data Structure and Data/Dump Data Only/Dump Structure only

4)Import 버튼

데이터베이스 로그(데이터베이스 운영 상황을 별도의 파일에 저장)

1)에러로그(일반적으로 사용하는 로그)

Mysql 구동과 모니터링, Query 에러에 관련된 메시지를 포함

2)일반로그(일반적으로 사용하는 로그)

전체 쿼리에 대하여 General log를 활성화 시켜서 저장 가능

일반적으로 성능상의 문제로 남기지 않는다

3)슬로우 쿼리 로그(일반적으로 사용하는 로그)

long_query_time에 설정된 시간 이상을 소요한 쿼리를 기록

4)이진 로그/릴레이 로그(리플리케이션 복제에서 사용하는 특정 로그임)

-mysql쿼리를 수행하면서 쌓는 로그, 시점 복구 등을 수행하는 역할

-replication에서 사용

-바이너리 로그(마스터-CUD를 남기게 된다)/릴레이 로그(슬레이브)에서 바이너리 로그를 사용해서 입력한다(내용동일하게)