DATABASE-VIEW INDEX TRIGGER

Oct 27, 2020 in DATABASE, DDL

VIEW

Explain

가상 테이블로 일부분만 데이터로 보고자 할때 사용합니다.
실제 데이터를 저장하고 있지는 않습니다.
한마디로 특정 컬럼의 데이터를 보여주는 역할만 합니다.
뷰를 사용함으로 쿼리를 더 단순하게 만들수 있습니다.
한번 생성된 뷰는 수정이 불가능 하며 인덱스설정이 불가능 합니다.

Syntax

1	CREATE VIEW <뷰이름> AS (QUERY)

Example

국가코드와 국가이름이 있는 뷰

1
2
3

CREATE VIEW code_name AS
SELECT code, name
FROM country

city 테이블에 국가 이름 추가

SELECT *
FROM city
JOIN code_name
ON city.countrycode = code_name.code

INDEX

Explain

정의 : 테이블에서 데이터를 검색할때 빠르게 찾을수 있도록 해주는 기능입니다.
장점 : 검색속도가 빨라짐
단점 : 저장공간을 10% 정도 더 많이 차지 INSERT, DELETE, UPDATE 할때 속도가 느려짐
사용법 : SELECT시 WHERE 절에 들어가는 컬럼을 Index로 설정하면 좋다.
내부 작동 원리 (B-Tree)
- 루트노드와 리프노드의 계층적 구조로 루트노드를 이용하여 리프노드에서의 데이터를 빠르게 찾을수 있는 자료구조 알고리즘.

Syntax

1	CREATE INDEX <인덱스 이름> AS <테이블 이름(필드 이름)>

Example

employees 데이터 베이스에서 실행

실행계획을 확인하여 인덱스를 사용하는지 확인

EXPLAIN
SELECT *
FROM salaries
WHERE from_date < "1986-01-01"

EXPLAIN
SELECT *
FROM salaries
WHERE to_date < "1986-01-01"

인덱스 확인
1
SHOW INDEX FROM salaries;

인덱스 생성

CREATE INDEX fdate
ON salaries (from_date)

CREATE INDEX tdate
ON salaries (to_date)

여러개의 컬럼을 가지는 인덱스 생성도 가능
1
2
CREATE INDEX ftdate
ON salaries (from_date, to_date)

인덱스 삭제

DROP INDEX fdate
ON salaries

DROP INDEX tdate
ON salaries

DROP INDEX ftdate
ON salaries

여러개의 컬럼을 조건으로 WHERE절에 사용하는 경우 인덱스 확인

인덱스가 하나의 컬럼에 있을때 보다 둘다 있을때가 더 빠름

EXPLAIN
SELECT *
FROM salaries
WHERE from_date < "1986-01-01" AND to_date < "1986-01-01"

TRIGGER

정의 : 특정 테이블을 감시하고 있다가 설정된 조건의 쿼리가 실행되면 미리 지정한 쿼리가 자동으로 실행되도록하는 방법

Syntax

create trigger {trigger name}
{before | after} {insert | update |delete}
on <table name> for each row
begin
    <excute query>
end

Example

database 생성

create table data1 (
    id varchar(20),
    msg varchar(50) not null
);

create table data_backup (
    id varchar(20),
    msg varchar(50) not null
);

Trigger 생성

create trigger backup
before delete on data1
for each row 
begin
    insert into data_backup(id, msg)
    values(old.id, old.msg);
end

Comment and share

DATABASE-Constraint

Oct 26, 2020 in DATABASE, MYSQL, DDL

Constraint : 제약조건

데이터 베이스의 테이블을 생성할때 각 컬럼은 각각의 제약조건을 갖습니다.

NOT NULL

NULL 값 (비어있는 값)을 저장할수 없습니다.
UNIQUE

같은 값을 저장할수 없습니다.
PRIMARY KEY
- NOT NULL과 UNIQUE 의 제약조건을 동시에 만족해야 합니다.
- 그러므로 컬럼에 비어 있는 값과 동일한 값을 저장할수 없습니다.
- 하나의 테이블에 하나의 컬럼만 조건을 설정할수 있습니다.
AUTO_INCREMENT

주로 테이블의 PRIMARY KEY 데이터를 저장할때 자동으로 숫자를 1씩 증가시켜 주는 기능으로 사용합
니다.

FOREIGN KEY

다른 테이블과 연결되는 값이 저장됩니다.

테이블 수정으로 FK 설정

alter table money
add constraint fk_user
foreign key (user_id)       # money의 user_id
references user (user_id);  # user의 user_id

테이블 생성시 FK 설정

create table money(
    money_id int primary key auto_increment,
    income int, 
    user_id int,
    foreign key (user_id) references user(user_id)
);

ON DELETE, ON UPDATE 설정
- 참조를 받는 데이터가 수정하거나 삭제될때 참조 하는 데이터를 설정(수정,삭제 등등)
- CASECADE : 참조 받는 데이터가 수정, 삭제하면, 참조하는 데이터도 수정 삭제
- SET NULL : 참조 받는 데이터가 수정 삭제하면, 참조하는 데이터는 NULL로 수정

DEFAULT

데이터를 저장할때 해당 컬럼에 별도의 저장값이 없으면 DEFAULT로 설정된 값이 저장됩니다.

create table money(
    money_id int primary key auto_increment,
    income int, 
    user_id int,
    foreign key (user_id) references user(user_id)
    on update cascade on delete set null
);

Comment and share

DATABASE-DATA TYPE

Oct 26, 2020 in DATABASE, DDL

DATA TYPE

데이터 베이스의 테이블을 생성할때 각 컬럼은 데이터 타입을 가집니다.

reference : https://dev.mysql.com/doc/refman/5.7/en/data-types.html

Numberic

reference : https://dev.mysql.com/doc/refman/5.7/en/numeric-types.html

정수 타입 ( integer types )
실수 (floating-point types)

소수점을 나타내기 위한 데이터 타입으로 아래의 두가지 데이터 타입이 있습니다.

두가지의 데이터 타입은 데이터 저장공간의 차이가 있습니다.

FLOAT (4byte), DOUBLE (8byte)

또한 아래와 같이 고정 소수점 타입으로도 사용이 가능합니다.

FLOAT(M,D), DOUBLE(M,D)

Date & Time

reference : https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html

DATE

DATE는 날짜를 저장하는 데이터 타입이며, 기본 포멧은 “년-월-일” 입니다.
DATETIME

DATETIME은 날짜와 시간을 저장하는 데이터 타입이며, 기본 포멧은 “년-월-일 시:분:초” 입니다.
TIMESTAME

TIMESTAME는 날짜와 시간을 저장하는 데이터 타입이며,

DATETIME과 다른점은 날짜를 입력하지 않으면 현재 날짜와 시간을 자동으로 저장할수 있는 특징이 있습니다.
TIME

TIME은 시간을 저장하는 데이터 타입이며, 기본 포멧은 “시:분:초” 입니다.
YEAR

YEAR는 연도를 저장할수 있는 데이터 타입입니다.

YEAR(2)는 2자리의 연도를 저장할수 있으며 YEAR(4)는 4자리의 연도를 저장할수 있습니다.

String

reference
https://dev.mysql.com/doc/refman/5.7/en/string-types.html

CHAR & VARCHAR

TEXT
- CHAR와 VARCHAR는 대체로 크기가 작은 문자열을 저장할때 사용되며 크기가 큰 문자열을 저장할 때는 TEXT를 사용합니다.
- TEXT의 타입에 따라서 아래와 같이 크기를 가집니다.

Comment and share

DATABASE-DDL

Oct 26, 2020 in DATABASE, DDL

CREATE USE ALTER DROP

USE

test 데이터 베이스 선택
1
USE test;
현재 데이터 베이스 확인
1
SELECT DATABASE()

CREATE

데이터 베이스 생성
1
CREATE DATABASE <database_name>;
test 데이터 베이스 생성
1
CREATE DATABASE test;

테이블 생성

CREATE TABLE <table_name> (
    column_name_1 column_data_type_1 column_constraint_1,
    column_name_2 column_data_type_2 column_constraint_2,
    ...
)

제약조건이 없는 user1 테이블 생성

CREATE TABLE user1(
    user_id INT,
    name Varchar(20),
    email Varchar(30),
    age INT(3),
    rdate DATE
)

제약조건이 있는 user2 테이블 생성

CREATE TABLE user2(
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    name Varchar(20) NOT NULL,
    email Varchar(30) UNIQUE NOT NULL,
    age INT(3) DEFAULT '30',
    rdate TIMESTAMP
)

ALTER

Database

사용중인 데이터베이스의 인코딩 방식 확인
1
SHOW VARIABLES LIKE "character_set_database"

test 데이터 베이스의 문자열 인코딩을 utf8으로 변경

1 2	ALTER DATABASE world CHARACTER SET = ascii ALTER DATABASE world CHARACTER SET = utf8

Table

ALTER를 이용하여 Table의 컬럼을 추가하거나 삭제하거나 수정할수 있습니다.
ADD
- user2 테이블에 TEXT 데이터 타입을 갖는 tmp 컬럼을 추가
  1
  ALTER TABLE user2 ADD tmp TEXT
MODIFY
- user2 테이블에 INT 데이터 타입을 갖는 tmp 컬럼으로 수정
  1
  ALTER TABLE user2 MODIFY COLUMN tmp INT
DROP
- user2 테이블의 tmp 컬럼을 삭제
  1
  ALTER TABLE user2 DROP tmp

DROP

DATABASE

tmp 데이터 베이스 생성
1
2
CREATE DATABASE tmp;
SHOW DATABASES;
tmp 데이터 베이스 삭제
1
2
DROP DATABASE tmp;
SHOW DATABASES;

TABLE
- tmp 데이터 베이스 생성
  1
  CREATE DATABASE tmp;
- tmp 데이터 베이스 선택
  1
  USE tmp;
- tmp 테이블 생성
  1
  CREATE TABLE tmp( id INT );
- tmp 테이블 삭제
  1
  DROP TABLE tmp;

Comment and share

DATABASE-VIEW INDEX TRIGGER

VIEW

Explain

Syntax

Example

INDEX

Explain

Syntax

Example

TRIGGER

Syntax

Example

DATABASE-Constraint

Constraint : 제약조건

DATABASE-DATA TYPE

DATA TYPE

Numberic

Date & Time

String

DATABASE-DDL

CREATE USE ALTER DROP

USE

CREATE

ALTER

Database

Table

DROP

Yechan Kim