WITH US

* Transaction

: 복수 SQL문을 수행하는 도중 장애가 발생했을때 장애에 대응할 수 있도록 하는 기능

: 전체 수행(Commit)과 전체 취소(Rollback) 두가지 결과값만 가져야함

: 기본적으로 SQL 수행모드는 AutoCommit모드(줄단위 커밋모드)

: 트랜잭션을 지원하기위해서는 AutoCommit모드를 오프시켜야함.

: InnoDB 스토리지엔진만 가능하다.

autocommit 확인

: SELECT @@autocommit (1=true/0=false)

autocommit 지정

: set autocommit = false;

트랜잭션 설정(DB설정이후 해야함.)

: commit or rollback

ACID 특성

: 원자성

: ALL or Nothing, Commit/Rollback

: 일관성

: 트랜잭션 전후에 데이터가 손상을 받으면 안됨

: 같은 데이터가 다른 값을 가지면 안됨

: 고립성/격리수준

: 여러개의 트랜잭션이 수행될 때 성능과 데이터 안전성간의 트레이드오프

: 지속성

: 트랜젝션이 종료된 이후 데이터에 문제가 없어야한다.

락(Lock)

:  공유자원에 대해 여러개의 트랜잭션이 접근하려고 경쟁하려고 할때 제어하는 방법

: 동시성제어(Concurrency Control)이라고 하고 보통 lock 으로 해결

: 프로그래밍에서 동기화라고도 함.

: 일관성과 무결성을 지키기위해 적용

- 테이블단위락 ( Table Lock ) : MyISAM 

: 동일한 테이블을 다른 트랜잭션이 사용하고 있다면 접근 금지

- 줄단위 락( Row Lock ) : InnoDB

: 동일한 줄(Row)만 접근 금지(테이블락에 비해 높은 성능)

: 스토리지 엔진은 기본/DB/테이블 단위로 변경할 수 있다.

* 격리(Isolation

: 트랜잭션의 격리수준

: 트랜잭션에 일관성 없는 데이터를 허용하는 레벨(안정성과 성능이 반비례되어 정렬)

: Read Uncommitted : 거이 안씀

: Commit되지 않는 처리중인 데이터를 읽기 허용한다.

: 성능은 높지만 데이터 안정성이 떨어짐

: Read Committed : 많이 사용

: 트랜잭션이 끝난 이후에만 접근하도록 허용(Committed data)

: Repeatable Read : 많이 사용

: 다른 트랜잭션이 UPDATE를 하는 것을 금지하지만, 레코드를 INSERT것은 허용

: Serializable

: 트랜잭션이 동시에 수행되는것이 금지되고 순차적 실행

: 성능저하가 있음.

: 격리수준 확인

: SELECT @@tx_isolation

: SET tx_isolation = 'READ-COMMITED'

: COMMIT

: INSERT 의 경우 레코드를 추가한 후 내부적으로 인덱스 재구성 작업이 필요하다.

: 여러개의 레코드를 입력할 경우 하나의 레코드를 입력할 때마다 연속적으로 발생한다.

: 이 문제를 해결하는 방법이다.

* 인덱스 작업 정지/재설정

* 작업 정지

: ALTER TABLE 테이블명 DISABLE KEYS;

* 작업 재설정

: ALTER TABLE 테이블명 ENABLE KEYS;

* BULK INSERT 방식

: INSERT INTO 테이블명 VALUES() VALUES();

: 파일 백업

: CSV파일을 사용하는 방식

: LOAD DATA INFILE '파일경로' INTO TABLE 테이블명

CSV파일 내보내기

: SELECT * FROM 테이블명 INTO OUTFILE '파일경로'

: 주의

: LOAD DATA/SELECT INTO OUTFILE의 경우 보안문제로 디렉토리가 정해져있음.

* Full Text Search

: 전문검색

: 기존의 Like검색 은 여러개의 검색 필터를 동시에 매칭시키는 방식임.

: 결과는 동일하지만 DB서버에 부담을 주지 않는 방식임.

: 컬럼 내용 전체를 단순문자열로 생각하고 검색하는 방식

: 문자 편집기의 찾기/바꾸기의 메뉴 동작방식과 유사함

: MySQL의 Full Text Search 방식

: 자연어 검색

: WHERE MATCH(컬럼,컬럼...) AGAINST("검색어")

: 검색의 정확도 확인 AS SCORE

: 정확도에 따라 결과 내림차순 정렬

: 불린 검색

: WHERE MATCH(컬럼,컬럼...) AGAINST("단어* - 제외단어" in boolean mode)

: + 필수단어, - 예외단어, + 부분단어

: 연산자 사용한 구문검색

: 쿼리 확장 검색

: Full Text Search 인덱스 지원

: 5.5이하는 MyISAM만 가능, 이후는 둘다 가능

: ALTER TABLE 테이블명 ADD FULLTEXT(컬럼명)

데이터베이스 백업

데이터베이스 복원

데이터베이스 로그

-에러로그

: MySQL 구동과 모니터링, Query 에러에 관련된 메세지

- 일반로그

: 전체쿼리에 대해 General log를 활성화 시켜 저장가능

- 슬로우 쿼리 로그

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

- 이진로그/릴레이 로그

* 데이터베이스 파티셔닝

* VLDB(Very Large DBMS)

: 전체 DB가 하나의 DBMS시스템에 다 들어가기 힘든경우

: 테이블들을 여러 군으로 나눠 분산저장한다

: 하나의 테이블을 사전방식으로 나누어 저장한다

* 파티셔닝

: DBMS 레벨 분할

* 샤딩(Sharding)

: DBMS 외부에서 분할/응용레벨에서 구별해야함.

* 제약사항

* 테이블 단위 연산이 힘들어짐(비용문제)

: 조인연산 어려움 -> 정규화 문제

: 역정규화 -> 중복허용으로 해결

* 외래키의 효용문제

: 레코드 추가시 참조무결성 조건 체크 -> 시스템 부담증가로 수동전환

: CRUD시 위치를 인식해야함.(파티셔닝/샤딩이 다름)

* 파티셔닝

*이점

: 부분탐색으로 인한 성능 증가

: 전체 데이터 손실 가능성이 줄어듬

: 파티션 별 백업/복구 가능

: 파티션 단위로 I/O분산가능 WRITE성능 증가<-- 가장 중요

* 방식

: 범위 기반 - a-m/n-r/s-z 혹은 날짜 기준

: 각 파티션별로 양이 다를 수 있는 문제가 있음

: 해시 - 해시함수 파티션별로 크기를 비슷하게 나눔

: 리스트 - 특정 칼럼 기준

: 컴포지트(섞은거) - range-hash/range-list

* MySQL 파티셔닝

: 5.x 지원

: 최대 1024개

: 모든 파티션은 동일한 스토리지 엔진을 사용해야함

: 외래키/Full text 인덱스 지원하지 않음

: 파티션 값은 정수

파티션 추가/삭제

ALTER TABLE 테이블명 ADD PARTITION( PARTITION p4 VALUES LESS THAN(정수) );

ALTER TABLE 테이블명 DROP PARTITION p4

파티션 분할/병합

ALTER TABLE 테이블명
    REORGANIZE PARTITIOIN p3 INTO(
    PARTITION p3 VALUES LESS THAN(2015),
    PARTITION p4 VALUES LESS THAN MAXVALUE);

ALTER TABLE 테이블명
    REORGANIZE PARTITION p2,p3 INTO(
    PARTITION P23 VALUES LESS THAN (2014);

데이터 베이스 복제(Replication)

: DBMS의 내용을 복제하여 동일한 DB내용을 유지

: 두개 이상의 DBMS시스템을 마스터/슬레이브로 나누어 마스터 DBMS->슬레이브DBMS로 SQL 쿼리 복제(SELECT 제외)

: 데이터 업데이트(CUD)는 마스터에서

: 읽기(R)는 슬레이브에서 함.

: 읽기 성능 향상

: 웹서버 시스템 성능확장에 적합

* 로그기반 복제

: Statement Based

: SQL문장을 복제하는 경우, 결과가 달라질수있음(시간,UUID...)

: Row Based

: SQL문장에 따라 변경된 내용만 기록함. 데이터가 많이 변경되면 데이터가 커짐

: Mixed

: 두방식을 혼합한것

: MySQL 서버가 두대 필요하다

: 데이터를 위한 데이터

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

: DB명, 테이블명, 컬럼명, 사용자명, SHOW명령어의 결과값 등등...

개념

- 데이터 사전 : Information_schema

: DB의 정보저장

: 읽기 전용, 시스템 카탈로그라고도 함.

- 데이터 디렉토리

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

: DB저장, 상태 및 로그 저장

SHOW DATABASES;

SHOW TABLES;

SHOW TABLE STATUS

SHOW COLUMNS FROM 테이블명

SHOW INDEX FROM 테이블명

SHOW TABLES IN INFORMATION_SCHEMA

DESCRIBE INFORMATION_SHEMA.CHARACTERSET

기본 캐릭터셋 확인

Status

스토리지엔진 변경

SELECT engine, support FROM information_schema.engines WHERE support='DEFAULT'

SET default_storage_engine=MyISAM

* CharacterSet/Collation

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

: 문자열의 값을 저장할 때 사용되는 기본정보

: DB/테이블별로 별도 설정 가능

: ASCII/EUC-KR/UTF-8등이 있고

: UTF8이 기본이다.

* Collation

: 데이터를 정렬할 때 사용하는 정보

: 한글의 경우 무의미

: 정렬시에 대소문자를 구분/비구분 여부를 설정하는 것.

: utf8-general-ci가 기본 // 비구분한다는 뜻

* 스토리지 엔진(Strorage Engine)

: 데이터 베이스 엔진이라고도함.

: DBMS가 CRUD를 할때 사용하는 기본 컴포넌트

: MyISAM과 InnoDB등이 있음

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

: 기본값은 InnoDB임.

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

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

: 빠르게 레코드를 찾기 위해서 INDEX를 걸어둔다.

: INDEX가 걸려 있는 COLUMN은 INDEX를 매번 업데이트 하기 때문에 부담되는 작업이다.

: 그래서 경우에 따라 INDEX를 삭제하기 한다.

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

: CRUD를 위해 항상 INDEX를 UPDATE하고 검색속도를 높임. (TRADE OFF 관계이다.)

: 데이터가 100만개 이상 넘어가면 인덱스 유/무에 커다란 차이가 발생한다.

: 조인연산도 테이블 합치는 연산중 검색이 많이 들어간다.

인덱스 추가/삭제

CREATE INDEX 인덱스명 ON 테이블명(컬럼명) <== 괄호쳐야함

CREATE INDEX 인덱스명 ON 테이블명(컬럼1,컬럼2,컬럼3) <== 괄호쳐야함

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

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

DCL(Data Control Language)

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

: 특정 테이블에 대한 CRUD 권한 설정

: 권한부여 (GRANT) / 권한 회수(REVOKE) 로 나뉨

유저 등록

CREATE USER '유저명'@'localhost/로컬랜/인터넷전체' IDENTIFIED BY '비밀번호'

유저 삭제

DROP USER 사용자명@호스트

반영하기

FLUSH PRIVILEGES

권한확인

SHOW GRANTS FOR 유저명@호스트

권한 설정

GRANT ALL ON 데이터베이스.테이블 TO 유저명@호스트 IDENTIFIED BY '비밀번호'

데이터베이스.테이블 ==> *.테이블, 데이터베이스.*, *.* 으로 표현가능

호스트 ==> localhost / '192.168.0.*' / % 으로 표현가능

ALL ==> SELECT, INSERT, UPDATE(컬럼명) 등으로 표현가능

권한 회수

REVOKE ALL ON *.* FROM 유저명@호스트

역할설정

: 사용자를 생성후 역할을 할당해주는 방식으로 업무량 감소

: 사용자가 여러개의 역할을 가지고 있을 수 있음

: MySQL은 지원안됨

역할 생성

CREATE ROLE 역할명

역할 수여

GRANT 제한 ON 데이터베이스/데이터베이스.테이블 TO 역할명

역할 부여

GRANT 역할명 TO 유저명

MySQL  원격접속 설정

: MySQL는 기본적으로 로컬시스템 접속만 가능하다

: 먼저 원격 사용자를 등록하고 필요한 권한을 등록한다

: my.ini(windows) // my.cnf(unix) 의 내용을 수정하여 bind-address...라인을 코멘트 처리해야 한다.(#추가)

: 경우에 따라 시스템 방화벽을 해제한다. 

: MySQL은 보통 3306 포트를 사용하기 때문에 3306을 풀거나 포트포워딩 설정을 한다.

DATABASE 생성

CREATE DATABASE 데이터베이스명

DATABASE 확인

SHOW DATABASES

DATABASE 삭제

DROP DATABASE 데이터베이스명

테이블 생성

CREATE TABLE 테이블명(이름 자료형 제약조건, 이름 자료형 제약조건...)

CREATE TABLE 테이블명(
       컬럼이름 자료형(길이) 제약조건,
ex)    id int (11) not null auto_increment,
       name varchar(10),
       primary key(id)                   // pimary key를 id로 정함.
);

테이블확인

desc 테이블명

테이블 삭제

DROP TABLE 테이블명 : 테이블 전체 삭제

DELETE * FROM 테이블명 : 테이블은 존재

TRUNCATE TABLE 테이블명 : 테이블 내용만 유지 << DELETE 대신 사용함. 빠름.

스키마 수정

테이블 컬럼 추가/삭제/수정

ALTER TABLE 테이블명 ADD 컬럼명 데이터 타입

ALTER TABLE 테이블명 DROP COLUMN 컬럼명

ALTER TABLE 테이블 명 CHANGE 컬럼명 새로운컬럼명 데이터타입(컬렴명변경)

ALTER TABLE 테이블명 MODIFY 컬럼명 데이터 타입 (컬럼타입변경)

기본키 제약조건 수정

ALTER TABLE 테이블명 ADD PRIMARY KEY (컬럼명)   <-- 괄호쳐야합니다.

ALTER TABLE 테이블명 DROP PRIMARY KEY

UNIQUE 제약조건 추가/삭제

ALTER TABLE 테이블명 ADD UNIQUE (컬럼명)   <-- 괄호쳐야합니다.

ALTER TABLE 테이블명 ADD CONSTRAINT 제약이름 UNIQUE (컬럼1,컬럼2...) - 둘이상에 추가하고 싶을때

ALTER TABLE 테이블명 DROP UNIQUE 제약명

외래키 제약조건 수정

ALTER TABLE 테이블명 ADD FOREIGN KEY 컬럼명 REFERENCES

ALTER TABLE 테이블명 DROP FOREIGN KEY 컬럼명

테이블명

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

DEFAULT 제약조건 추가/삭제

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

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

자료형

- 정수형(부호있음/부호없음)

: TINYINT(-128~+127 /255)

: INT(-21억~+21억/43억)

: BIGINT(-9경 ~ +9경 / 18경)

- 실수형(길이, 소수점 이하 자리수)

: FLOAT(size,d)

: DOUBLE(size,d)

: DECIMAL(size,d)

- 문자열

: CHAR 고정길이 문자열 (최대 255)

: VARCHAR 가변길이 문자열(최대 255)

- TEXT 문자열

: TEXT (최대 65,535자)

: MEDIUMTEXT(최대 16,777,215자)

: LONGTEXT(최대 4,294,967,295자)

- BLOB(Binary Large Object)

: BLOB(최대 65,535 바이트)

: MEDIUMBLOB(최대 16,777,215 바이트)

: LARGEBLOB(최대 4,294,967,296 바이트)

- 시간관련

: DATE(YYYY-MM-DD)

: TIME(HH:MI:SS)

: DATETIME(YYYY-MM-DD-HH-MI-SS)

: DATETIMESTAMP

제약조건

: 입력데이터의 제약조건을 걸어 해당되지 않는 데이터는 입력되지 않음

: NOT NULL, UNIQUE, PRIMARY KEY(컬럼<NOT NULL조건을 걸어둬야함.>), FOREIGN KEY(컬럼), CHECK(조건)<MySQL은 사용불가>, DEFAULT

: AUTO_INCREMENT

중복정보제거

: 테이블간의 정보는 중복되지 않아야함.

: 수정 소요 발생 시 일부만 업데이트 될 수 있는 위험이 있기 때문

정규형

: 중복을 제거하기 위한 테이블 정의 규칙

- 제1 정규형 : 나눌 수 있을 만큼 쪼개라

- 제2 정규형 : 테이블의 컬럼들이 기본키와 직접 연관되는 컬럼만으로 구성하라

- 제3 정규형 : 컬럼들 간의 종속관계가 있으면 안됨.

참조무결성

: 외래키에 적용되는 규칙

: 외래키를 참조하면 원래 테이블에 해당 레코드 값이 반드시 존재해야한다.

: 레코드 추가/삭제시 외래키를 null로 만들어야 한다.

 : <테이블명>이나 테이블명 이나 테이블 이나 똑같은 것입니다. (처음에 괄호를 넣어서 적다가 나중에 안적게됨... 추후 수정하겠습니다.)

: 명령문 끝에는 ; 를 넣어야합니다.

 SELECT 문

DELETE 문

DISTINCT 연산자

논리연산자 (AND, OR, NOT)

논리연산자 (IN, BETWEEN)

결과 정렬(ORDER BY)

결과값 일부 조회 (MYSQL - LIMIT, ORACLE - ROWNUM, SQLSever - TOP)

집합함수 (Aggregation Function)

유용한 함수

JOIN 

: 서로다른 테이블을 공통 컬럼을 기준으로 합치는 테이블 단위 연산이다.

: 조인 시 서로 다른 테이블에 같은 컬럼명이 존재할 경우, 테이블명.컬럼명으로 사용하여 구별한다.

: default는 inner join 이다.

ALIAS

: SQL 쿼리 결과 생성시 컬럼명에 대한 별명을 사용해 표시하는 기능

: 조인할 때 많이 사용한다.

VIEW

: SQL 쿼리의 결과 값을 임시테이블로 저장해서 사용함

: 사용용도가 끝나면 삭제해야함. (DROP VIEW 뷰이름)

SELECT INTO

: 쿼리결과로 새 테이블을 만든다.

: 기존에 존재하지 않는 테이블이 새로 생성된다.

INSERT INTO SELECT

: 쿼리 결과를 기존 테이블에 추가한다(기존테이블이 존재해야한다.)

: 컬럼이 모두 매칭이 되야 한다.

CASE...WHEN...END

: 조건에 다른 처리를 할 수 있다.

LIKE 검색

: 정확한 키워드를 모를 경우 일부만으로 검색하는 방법

: 와일드카드를 사용하여 패턴 매칭한다. (% : 0~N 글자 , _ : 1글자)

: LIKE 검색 시 논리 조건자(OR, AND)를 섞지 않는게 좋다

NULL 값

NULL 함수

: 숫자칼럼을 연산할 때 NULL을 처리해주는 함수

: 보통 NULL이나오면 자동으로 제외하고 계산한다.(SUM()같은 함수에 내장되어있음)

GROUP BY

: distinct 와 sort의 기능을 합쳐놓은 듯한 명령어이다.

: 특정 칼럼의 연산 결과를 확인할때 보통 사용한다.

: GROUP BY 에 선언한 컬럼만 출력할 수 있다.

: 정확한 역할은 잘 모르겠다. 

HAVING

: GROUP BY 에서 WHERE 조건절을 대체하여 사용한다.

SUBQUERY

: 쿼리문 내에 또 다른 쿼리문이 있는 형태

: SELECT/FROM/WHERE/HAVING/ORDER BY/VALUES(INSERT)/SET(UPDATE)...등에서 사용가능하다.

- 단일행 서브쿼리

: 결과가 레코드 하나인 서브쿼리

: 일반연산자(=,>,<...) 사용

- 다중행 서브 쿼리

: 결과 레코드가 여러개

: IN, ALL, ANY, EXISTS 사용

ALL == 결과 레코드 중 가장 큰 값과 비교한다.

ANY == 결과 레코드 중 가장 작은 값과 비교한다.

IN/EXISTS == 결과 값 중 모두와 비교하라. ( IN의 경우 값들을 집어 넣고 // EXISTS 의 경우 TRUE/FALSE 의 경우만 생각한다.)

- 멀티 컬럼 서브쿼리

: 결과가 컬럼 여러개인 서브쿼리

: IN, ALL, ANY, EXISTS 사용

: EXISTS 는 잘 모르겠다.

집합연산

: 결과값의 집합연산을 지원한다.

UNION - 합집합

INTERSECT - 교집합

MINUS - 차집합

: 결과 형식이 동일해야하며, DISTINCT 적용이 된다. (UNION ALL 사용시 DISTINCT 미적용)

: 다른테이블이어도 됨.

: MySQL 은 INTERSECT, MINUS를 지원하지 않는다.

1. SQL의 이해와 종류

* SQL의 이해

: 사용방법이나 문법이 다른 언어에 비해 단순함

: 인터프리터

: 대소문자를 구별하지 않음

: 데이터베이스에 있는 필요한 정보를 사용할수있도록 도와주는 언어

* DML(Data Manipulation Language) 

: 테이블의 레코드를 CRUD함.

: INSERT, DELETE, UPDATE, SELECT를 사용

* DDL(Data Definition Language)

: DB, 테이블의 스키마를 정의, 수정하는 기능

: 테이블 생성, 컬럼추가, 타입변경 등등

: CREATE, DROP, ALTER

* DCL(Data Control Language) 

: DB나 테이블의 접근권한이나 CRUD권한을 정의

: GRANT, REVOKE