논리적 데이터베이스 모델링

데이터베이스 모델링은 개념적 데이터베이스 모델링과 논리적 데이터베이스 모델링, 물리적 데이터베이스 모델링의 3단계로 나눠볼 수 있습니다. 간단히 설명해보면,

1. 개념적 데이터베이스 모델링 과정에서는 업무 분석 및 사용자 요구 분석을 통해 데이터베이스화 할 수 있는 형태, 즉, 엔티티(Entity)와 엔티티 타입(Entity type), 속성(Attribute)과 식별자(Identify), 엔티티 타입간의 관계(Relation) 등 설정하는 단계입니다.
2. 논리적 데이터베이스 모델링 과정은 개념적 데이터베이스 모델링 과정을 통해 도출된 개념적 구조로부터 특정 DBMS가 처리할 수 있는 관계 스키마를 생성하는 단계입니다. 보통 실무에서는 논리적 데이터베이스 모델링, 줄여서 논리설계라고 하는 단계부터 바로 시작하기도 합니다. 개념적 데이터베이스 모델링의 경우 특정 DBMS와 상관없는 개념이지만 논리설계 과정에서는 관계형 데이터베이스와 같은 구체적인 데이터베이스 모델에 따른 설계를 진행합니다.
   관계형 데이터베이스를 기반으로 한 논리설계 단계에서의 주요 작업은 부모 테이블과 자식 테이블을 나누고 기본키(Primary key)와 외래키(Foreign key)의 설계와 식별(Identify) 관계와 비식별(Non-identify) 관계의 지정, 카디널리티(Cadinality)의 지정, 정규화 등의 과정을 거치게 됩니다.
3. 마지막으로 물리적 데이터베이스 모델링이 있으며 물리설계라고도 합니다. 물리설계 과정은 MySQL이나 오라클과 같은 구체적인 벤더에 최적화된 설계를 진행하게 됩니다. 물리설계의 주요 과정으로는 데이터의 타입 및 제약 설계, 인덱스와 역정규화 설계외에 트리거 등의 설계가 있습니다.

기본키와 외래키, 그리고 식별과 비식별 관계, 카디널리티에 대해서는 이전 기사에서 설명하였으므로 이번 기사에서는 정규화에 대해 설명하도록 하겠습니다.

제 1 정규화

보통 논리설계 과정에서는 제 1 정규화와 제 2 정규화, 제 3 정규화 과정을 거치게 됩니다. 제 1 정규화의 목표는 반복되는 그룹 속성(attribute)의 제거입니다. 이를 위해 반복되는 그룹 속성을 별도의 테이블로 분리하고 부모 테이블의 기본키를 추가하여 1:M 관계를 형성하는 과정을 거치게 됩니다.

직업훈련생 정보를 저장하기 위해 다음과 같이 테이블을 설계하였다면,

2개 이상의 자격증을 취득한 훈련생의 경우 주요 정보인 등록번호와 이름, 주민번호, 주소, 전화번호가 중복되게 됩니다. 이를 해결하기 위해 제 1 정규화를 적용하여 보겠습니다. 반복되는 그룹 속성인 이름, 주민번호, 주소, 전화번호를 제거하여 별도의 테이블로 만들고, 기본키인 등록정보를 외래키로 지정하여 자격증, 취득일을 남겨두어 기본정보가 저장된 테이블과 1:M 관계를 형성합니다.

이 경우 과목의 수가 3과목이 안된다면 Null이 발생하며, 넘는 경우 테이블의 컬럼을 추가하여야 하므로 좋은 설계가 될 수 없습니다. 이 역시 제 1 정규화의 대상이 됩니다.

제 2 정규화

제 2 정규화의 목표는 복합키의 사용시 복합키에 의존하지 않고 복합키의 일부분에 의존하는 컬럼들을 제거하는 것입니다. 직업훈련생의 평가정보를 저장하기 위해 다음과 같이 테이블을 설계하였습니다.

위의 경우 등록번호와 과정코드가 함께 복합키이며 기본키로 지정되어 있습니다. 그러나 평가 결과의 경우 과정을 수강하는 훈련생의 평가이므로 복합키에 의존하는 반면 기간의 경우 과정코드에만 종속되어 있기 때문에 제 2 정규화를 통해 이를 해결할 수 있습니다.

제 3 정규화

제 3 정규화의 목표는 키본키에 의존하는 않고 일반 컬럼에 의존하는 컬럼들을 제거하는 것입니다. 다음과 같이 주문정보를 저장하였을 경우 고객명과 전화번호는 주문번호가 아닌 고객번호에 종속되어 있으므로 고객번호에 종속적인 컬럼들을 별도의 테이블로 분리시켜 새로운 테이블로 구성합니다.

데이터모델링의 주요 개념

데이터모델링의 세가지 주요 개념

• Entity
• Relation
• Attribute

데이터모델링의 주요 개념은 엔티티와 관계, 속성의 세가지가 있습니다. 이번 시간에는 이 세가지 개념과 ERD(Entity Relation Diagram)를 그리는 방법을 배워보도록 하겠습니다.

1. 엔티티 타입 (Entity type)

업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 것으로 영속적으로 존재하는 단위

엔티티 타입 = 엔티티의 집합

엔티티 = 엔티티 타입의 인스턴스

엔티티 타입은 다음과 같은 특징을 가지며 다음 성질을 만족하지 못하면 부적절한 엔티티 타입일 가능성이 높습니다.

첫번째는 설명한 바와 같이 업무에 필요하고 관리되어야 하는 정보를 말합니다. 예를 들어 학원 관리 시스템을 개발한다면 강사, 과목, 수강생 등이 엔티티 타입이 될 수 있습니다. 엔티티는 이러한 엔티티 타입이 구체화된 실체를 의미하며 홍길동 강사와 같이 개별 강사들이 될 수 있습니다.

두번째로 엔티티는 유일한 식별자에 의해 구별이 되어야 합니다. 강사 이름과 같은 엔티티는 동명 이인과 같이 구별이 힘든 경우가 있기 때문에 개별 엔티티를 구별하기 위해 주민등록번호 등과 같은 식별자에 의해 구별될 수 있습니다.

세번째로 엔티티 타입은 엔티티들의 집합이며 엔티티들을 대표할 수 있어야 합니다. 엔티티 타입은 엔티티들을 표현하는 논리적인 단위여야 합니다.

네번째는 엔티티 타입이 업무 프로세스에서 반드시 사용되어야 한다는 점입니다.

다섯번째는 엔티티 타입은 하나 이상의 속성 집합으로 이뤄진다는 특징이 있습니다. 예들 들어, 강사 엔티티 타입은 이름과 경력, 학력 등 다수의 속성들을 가지게 됩니다.

여섯번째 특징은 다른 엔티티 타입과 반드시 한 개 이상의 관계를 가져야 한다는 점입니다. 하지만 간혹 참조를 위해 관계를 생략하여 표현하는 경우도 있을 수 있습니다.

엔티티 타입은 관계형 데이터베이스 모델에서는 테이블로 표현할 수 있습니다.

엔티티 타입은 물리적인 형태의 유무에 따라 유형 엔티티 타입, 개념 엔티티 타입, 사건 엔티티 타입으로 분류합니다.

• 유형(TANGIBLE) 엔티티 타입 - 강사, 강의실, 교재 등이 이에 해당됩니다.
• 개념(CONCEPTUAL) 엔티티 타입 - 조직, 과목 등 물리적 실체보다는 개념적 정보로 구분할 수 있는 엔티티 타입입니다.
• 사건(EVENT) 엔티티 타입 - 업무 수행을 발생하는 엔티티 타입으로 수강 신청, 수강료 입금 등이 해당됩니다.

그리고 발생 시점에 따라 기본 엔티티 타입과 중심 엔티티 타입, 행위 엔티티 타입으로 분류하기도 합니다.

• 기본(FUNDAMENTAL) 엔티티 타입 - 업무의 가장 기본되는 정보로 다른 엔티티 타입과의 관계로 생성되지 않습니다.
• 중심(MAIN) 엔티티 타입 - 기본 엔티티 타입에 의해 발생하는 엔티티 타입으로 기본 엔티티 타입인 수강생으로부터 수강 신청이라는 중심 엔티티 타입이 발생할 수 있습니다.
• 행위(ACTIVE) 엔티티 타입 - 두개 이상 부모 엔티티에 의해 발생됩니다. 쇼핑몰에서 고객과 상품이라는 부모 엔티티로부터 주문이라는 행위 엔티티 타입을 도출할 수 있습니다.

2. 관계 (Relation)

관계란 두 개의 엔티티 타입 사이의 논리적인 관계를 말합니다. 관계를 표현할 때는 다음과 같은 요소를 고려합니다.

2.1 카디넬러티(Cadinality)

• 1:1 (One To One) 관계 - 관계에 참여하는 엔티티는 각각 일대일로 대응됩니다.
• 1:M (One To Many) 관계 - 관계에 참여하는 엔티티는 엔티티당 다수의 엔티티가 대응됩니다.
• M:N (Many To Numerous) 관계 - 관계에 참여하는 엔티티은 양방향 모두 다수의 엔티티들이 대응됩니다.

2.2 참여도

• 필수참여 관계(MANDATORY MEMBERSHIP) - 엔티티에 대해 반드시 대응되는 엔티티가 존재합니다.
• 선택참여 관계(OPTIONAL MEMBERSHIP) - NULL을 허용하는 엔티티가 존재합니다.



3. 속성 (Attribute)

엔티티에서 더이상 분리할 수 없는 최소의 데이터 단위를 속성이라고 하며, 수강생 엔티티의 경우 수강생명, 주소, 전화번호 등이 속성이 될 수 있습니다.

3.1 속성의 특징에 따른 분류

• 기본(Basic) 속성 - 업무 분석을 통해 정의된 속성 (예, 이름, 생년월일)
• 설계(Design) 속성 - 원래 업무에 존재하지 않았으나 설계 과정에서 도출된 속성 (예, 등록번호, 구분코드)
• 파생(Derived) 속성 - 다른 속성으로부터 계산이나 변형되어 생성된 속성 (예, 생년월일로부터 계산된 나이)

3.2 엔티티 구성에 따른 분류

• 기본키(Primary key) 속성 - 엔티티 식별에 사용
• 참조키(Foreign key) 속성 - 다른 엔티티와의 관계에 포함된 속성
• 일반 속성 - PK, FK에 포함되지 않은 속성

4. 엔티티 수퍼 타입과 서브 타입

객체지향 언어에서의 상속과 비슷한 개념으로 일부 속성이나 관계를 제외하고 대부분이 동일한 여러 엔티티 타입을 한 개의 엔티티 수퍼 타입으로 묶어 통합하고 하나의 엔티티 타입안에 다른 엔티티 타입을 서브 타입으로 나누어 표기하기도 합니다.

개별 엔티티 타입의 동일한 속성들을 수퍼 타입으로 묶는 것을 엔티티 타입 통합이라고 하며 분석된 엔티티 타입에서 기능별로 여러 개의 엔티티 서브 타입으로 분화하는 것을 엔티티 타입 세분화라고 합니다.

5. 서브젝트 에리어

업무별로 연관이 많은 엔티티 타입을 그룹으로 묶어서 표시하는 개념을 서브젝트 에리어라고 합니다.

교재 : 뇌를 자극하는 오라클 프로그래밍 SQL&PL/SQL

4장 연습문제 풀이

1. 답

CREATE TABLE star_wars (
    episode_id NUMBER,
    episode_name VARCHAR2(50),
    open_year NUMBER(4),
    CONSTRAINT star_wars_pk PRIMARY KEY (episode_id)
    );

2. 답

CREATE TABLE characters (
    character_id NUMBER(5),
    character_name VARCHAR2(30),
    master_id NUMBER(5),
    role_id NUMBER,
    mail VARCHAR2(40),
    CONSTRAINT characters_pk PRIMARY KEY (character_id)
    );
   
3. 답

CREATE TABLE casting  (
    episode_id NUMBER(5),
    character_id NUMBER(5),
    real_name VARCHAR2(30),
    CONSTRAINT casting_pk PRIMARY KEY (episode_id, character_id)
    );

4. 답

INSERT INTO star_wars VALUES (1, '4. 새로운 희망(A New Hope)', 1977);
INSERT INTO star_wars VALUES (2, '5. 제국의 역습(The Empire Strikes Back)', 1980);
INSERT INTO star_wars VALUES (3, '6. 제다이의 귀환(Return of the Jedi)', 1983);
INSERT INTO star_wars VALUES (4, '1. 보이지 않는 위험(The Phantom Menace)', 1999);
INSERT INTO star_wars VALUES (5, '2. 클론의 습격(Attack of the Clones)', 2002);
INSERT INTO star_wars VALUES (6, '3. 시즈의 복수(Revenge of the Sith)', 2005);

5. 답

INSERT INTO characters VALUES (1, '루크 스카이워커', NULL, NULL, 'luke@jedai.com');
INSERT INTO characters VALUES (2, '한 솔로', NULL, NULL, 'solo@alliance.com');
INSERT INTO characters VALUES (3, '레이아 공주', NULL, NULL, 'leia@alliance.com');
INSERT INTO characters VALUES (4, '오비완 케노비', NULL, NULL, 'obi-wan@jedai.com');
INSERT INTO characters VALUES (5, '다쓰 베이더', NULL, NULL, 'vader@sith.com');
INSERT INTO characters VALUES (6, '다쓰 베이더(목소리)', NULL, NULL, 'chewbacca@alliance.com');
INSERT INTO characters VALUES (7, 'C-3PO', NULL, NULL, 'c3po@alliance.com');
INSERT INTO characters VALUES (8, 'R2-D2', NULL, NULL, 'r2d2@alliance.com');
INSERT INTO characters VALUES (9, '츄바카', NULL, NULL, 'chewbacca@alliance.com');
INSERT INTO characters VALUES (10, '랜도 칼리시안', NULL, NULL, NULL);
INSERT INTO characters VALUES (11, '요다(목소리)', NULL, NULL, 'yuda@jedai.com');
INSERT INTO characters VALUES (12, '다스 시디어스', NULL, NULL, NULL);
INSERT INTO characters VALUES (13, '아나킨 스카이워커', NULL, NULL, 'anakin@jedai.com');
INSERT INTO characters VALUES (14, '콰이곤 진', NULL, NULL, NULL);
INSERT INTO characters VALUES (15, '아미달라 여왕', NULL, NULL, NULL);
INSERT INTO characters VALUES (16, '아나킨 어머니', NULL, NULL, NULL);
INSERT INTO characters VALUES (17, '자자빙크스(목소리)', NULL, NULL, 'jaja@jedai.com');
INSERT INTO characters VALUES (18, '다스 몰', NULL, NULL, NULL);
INSERT INTO characters VALUES (19, '장고 펫', NULL, NULL, NULL);
INSERT INTO characters VALUES (20, '마스터 윈두', NULL, NULL, 'windu@jedai.com');
INSERT INTO characters VALUES (21, '듀크 백작', NULL, NULL, 'dooku@jedai.com');

6. 답

CREATE TABLE roles (
    role_id NUMBER,
    role_name VARCHAR2(30),
    CONSTRAINT role_pk PRIMARY KEY (role_id)
    );

INSERT INTO roles VALUES (1001, '제다이');
INSERT INTO roles VALUES (1002, '시스');
INSERT INTO roles VALUES (1003, '반란군');

7. 답

ALTER TABLE characters ADD CONSTRAINT characters_roles_fk
FOREIGN KEY (role_id) REFERENCES roles (role_id);

8. 답

UPDATE characters SET role_id = '1001' WHERE mail LIKE '%jedai.com';
UPDATE characters SET role_id = '1002'
WHERE character_name IN ('다쓰 베이더', '다쓰 베이더(목소리)', '다스 시디어스', '다스 몰', '듀크 백작');
UPDATE characters SET role_id = '1003'
WHERE role_id IS NULL AND character_name <> '장고 펫';

9. 답

UPDATE characters SET master_id = 4 WHERE character_name IN ('아나킨 스카이워커', '루크 스카이워커');
UPDATE characters SET master_id = 11 WHERE character_name IN ('마스터 윈두', '듀크 백작');
UPDATE characters SET master_id = 12 WHERE character_name IN ('다쓰 베이더', '다쓰 베이더(목소리)', '다쓰 몰');
UPDATE characters SET master_id = 14 WHERE character_name = '오비완 캐노비';
UPDATE characters SET master_id = 21 WHERE character_name = '콰이곤 진';

10. 답

ALTER TABLE casting ADD CONSTRAINT starwars_casting_fk
FOREIGN KEY (episode_id) REFERENCES star_wars (episode_id);
ALTER TABLE casting ADD CONSTRAINT character_casting_fk
FOREIGN KEY (character_id) REFERENCES characters (character_id);

11. 답

DELETE FROM roles WHERE role_id = 1001; -- 데이터 무결성 위배

Q. DATETIME 형식으로 저장된 필드의 값을 읽어서 ○년전, ○달전, ○시간전,○분전으로 출력하세요. (SELECT문을 사용)

A. 아래를 마우스로 끌어보세요. 답을 확인하기 전에 여러가지 모범답안을 기대하겠습니다. 댓글로 달아주세요.

문제> DATETIME 타입으로 저장된 필드값을 읽어들여서 오늘 날짜의 경우 시간(HH시 MM분) 형식으로 출력하고 이전 날짜의 경우 날짜(MM월 DD일) 형식으로 출력하세요.

(조건: SELECT문으로 출력)

풀이>

다음과 같이 데이터를 준비합니다.

답>

데이터 추가 및 삭제와 변경

INSERT문

(형식)

SELECT INTO 테이블명 (컬럼 목록) VALUES (데이터 목록);

INSET문은 하나의 행(row)을 저장하는 명령입니다. 데이터 추가시 컬럼 목록과 데이터 목록의 짝이 반드시 맞아야 하며 컬럼의 순서는 컬럼 목록에 사용된 컬럼 이름을 따릅니다. 사실 데이터베이스의 테이블은 데이터의 저장순으로 로우(행)가 추가되며 로우의 순서는 의미가 없습니다. 대신 PRIMARY KEY(기본키)로 설정된 컬럼의 경우 자동으로 인덱스가 만들어져서 미리 정렬이 되어 있습니다. 인덱스와 연결된 테이블은 SELECT문의 실행시 기본키를 기준으로 정렬되어 출력됩니다.

AUTO_INCREMENT 컬럼 제약이 주어졌을 경우 추가시 컬럼을 생략하면 자동으로 증가된 값이 추가됩니다. NOT NULL 컬럼 제약이 주어진 컬럼은 무조건 추가를 해야합니다.

(사용예)

CREATE TABLE customers (
    customer_id INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    gender ENUM('남', '여') NOT NULL,
    name VARCHAR(8) NOT NULL,
    email VARCHAR(35) NOT NULL,
    address VARCHAR(255) NOT NULL,
    telephone VARCHAR(14) NOT NULL,
    registdate DATE DEFAULT NULL,
    modifydate TIMESTAMP
);

INSERT INTO customers (gender, name, email, address, telephone, registdate)
VALUES ('남', '홍길동', 'kidong@hong.com', '전라도 장성현 아차곡', '010-1234-1234', '2009-09-21');

INSERT INTO customers
VALUES ('2', '남', '철수', 'cs@chol.com', '서울시', '02-1234-1234', '2009-09-21', '2009-09-21');

INSERT INTO customers
VALUES (3, '여', '영희', 'yh@chol.com', '서울시', '02-4321-4321', '2009-09-20', '2009-09-21'),
    (4, '남', '칠숙', 'hwarang7@chol.com', '서울시', '02-1234-4321', '2009-09-20', '2009-09-21'),
    (5, '여', '덕만', 'queen@chol.com', '서울시', '02-4321-1234', '2009-09-20', '2009-09-21');

DELETE문, TRUNCATE문

(형식)

DELETE FROM 테이블명 WHERE 조건문;

DELETE문의 경우 테이블을 대상으로 삭제 작업을 하기 때문에 WHERE절이 없을 경우 테이블의 모든 데이터를 삭제합니다. 이때 로우 단위로 삭제하기 때문에 시간이 좀 걸리는데 만일 전체 데이터를 지우고자 한다면 TRUNCATE 테이블문을 사용하는 것이 더 효율적입니다.

(사용예)

DELETE FROM customers WHERE customer_id = 4;

UPDATE문

(형식)

UPDATE 테이블명 SET 컬럼1 = 데이터, 컬럼2 = 데이터, ... WHERE 조건문;

특정 컬럼의 값을 변경할 때 사용합니다.

(사용예)

UPDATE costomers SET name = '홍삼정', telephone = '031-0123-1234' WHERE name = '홍길동';

대량 데이터 입력 (Bulk operation)

LOAD DATA INFILE

(사용예)

LOAD DATA LOCAL INFILE 'C:\\zip.txt'
    INTO TABLE ziptable
    CHARACTER SET 'utf8'
    COLUMNS TERMINATED BY '\t'
    LINES TERMINATED '\n'
    (seq, sido, gugun, dong, bunji);

mysqlimport 유틸리티

(사용예)

c:\> mysqlimport -u root -p ziptable zip.txt

MySQL 함수

날짜 함수

NOW(), CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP()

현재 시간을 리턴합니다.

SYSDATE()

'YYYY-MM-DD HH:MM:SS' 또는 'YYYYMMDDHHMMSS' 형식으로 현재 날짜나 시간을 리턴합니다. 5.0.13 버전이후부터는 SYSDATE()는 실행 시점의 시간을 표시하므로 NOW()와 차이점이 있습니다. 아래 명령을 실행하여 차이점을 확인하세요.

SELECT NOW(), SLEEP(2), NOW();
SELECT SYSDATE(), SLEEP(2), SYSDATE();

CURDATE(), CURRENT_DATE, CURRENT_DATE()

'YYYY-MM-DD' 또는 'YYYYMMDD' 형식으로 현재 날짜를 리턴합니다.

CURTIME(), CURRENT_TIME, CURRENT_TIME()

'HH:MM:SS' 또는 'HHMMSS.uuuuuu' 형식으로 현재 시간을 리턴합니다.

DATE_FORMAT(date,format)

date로 지정된 날짜른 format에 의해 표시합니다.

GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

특정 국가의 형식으로 표시합니다. 메뉴얼을 참고 하세요.

YEAR(date)

'1000'에서 '9999' 사이의 년도를 리턴합니다.

QUARTER(date)

분기를 1 ~4 로 리턴합니다.

MONTH(date)

월을 1 ~ 12 로 리턴합니다.

MONTHNAME(date)

월 이름을 리턴합니다.

WEEK(date[,mode])

요일 순서를 출력합니다. mode가 지정되면 특정 조건에 맞는 값을 리턴합니다.

WEEKDAY(date)

요일 순서를 리턴합니다. (0=Sunday, 1=Monday, ..., 6=Saturday)

WEEKOFYEAR(date)

년중 주의 순서를 리턴합니다. WEEK(date, 3)과 동일한 표현입니다.

YEARWEEK(date), YEARWEEK(date,mode)

해당되는 년도 및 주를 리턴합니다. mode는 WEEK() 함수와 동일한 동작을 합니다.

DAYNAME(date)

요일 이름을 리턴합니다.

DAYOFYEAR(date)

년중 날짜를 리턴합니다.

DAY(date), DAYOFMONTH(date)

월중 날짜를 리턴합니다.

DAYOFWEEK(date)

주중 순서를 리턴합니다. (1=Sunday, 2=Monday, ..., 7=Saturday)

LAST_DAY(date)

해당 월의 마지막 날짜를 리턴합니다.

DATE(expr)

date나 datetime 형식에서 date 부분만 리턴합니다.

HOUR(time)

시간을 리턴합니다.

MINUTE(time)

분을 리턴합니다.

SECOND(time)

초를 리턴합니다.

TIME(expr)

time이나 datetime 형식에서 time 부분만 리턴합니다.

MICROSECOND(expr)

마이크로초를 리턴합니다.

PERIOD_ADD(P,N)

P기간에 N달만큼 더한 결과를 YYYYMM 형태로 리턴합니다.

PERIOD_DIFF(P1,P2)

YYMM 이나 YYYYMM 형식의 P1과 P2 사이가 몇 달인지를 리턴합니다.

DATE_ADD(date,INTERVAL expr unit), ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

date로부터 expr만큼 type 단위로 더한 날짜를 리턴합니다.

DATE_SUB(date,INTERVAL expr unit), SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)

date로부터 expr만큼 unit 단위로 뺀 날짜를 리턴합니다.

DATEDIFF(expr1,expr2)

expr1과 expr2 사이의 일 수를 리턴합니다.

TIMEDIFF(expr1,expr2)

expr1과 expr2 사이의 시간을 time 형식으로 리턴합니다.

ADDTIME(expr1,expr2)

date나 datetime 형식의 expr1에 time 형식의 expr1을 더한 time 형식의 값을 리턴합니다.

SUBTIME(expr1,expr2)

date나 datetime 형식의 expr1에 time 형식의 expr1을 뺀 time 형식의 값을 리턴합니다.

TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

date나 datetime 형식의 시간을 datetime 형식으로 리턴합니다. expr2가 주어지면 해당 시간으로 시간을 지정합니다.

TIMESTAMPADD(unit,interval,datetime_expr)

datetime_expr로부터 interval만큼 unit 단위로 더한 시간를 리턴합니다.

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

datetime_expr1과 datetieme_expr2 사이를 unit 단위로 리턴합니다.

TIME_FORMAT(time,format)

time 형식의 시간을 format 형식으로 리턴합니다. format은 DATE_FORMAT() 함수와 동일합니다.

EXTRACT(unit FROM date)

date로부터 unit 형식으로 값을 리턴합니다.

SELECT EXTRACT(YEAR FROM SYSDATE());
SELECT EXTRACT(MONTH FROM SYSDATE());
SELECT EXTRACT(DAY FROM SYSDATE());

FROM_DAYS(N)

0년부터 계산되 날 수로부터 날짜를 리턴합니다.

TO_DAYS(date)

date를 기준으로 0년부터 계산된 날 수를 리턴합니다. 그레고리력을 따르지 않습니다.

SEC_TO_TIME(seconds)

초를 time 형식으로 리턴합니다.

TIME_TO_SEC(time)

time 형식의 시간을 초로 리턴합니다.

STR_TO_DATE(str,format)

문자열str을 format 형식의 시간으로 리턴합니다. format문자열은 DATE_FORMAT() 과 동일합니다.

MAKEDATE(year,dayofyear)

year와 day-of-year 값으로 date 형식(YYYY-MM-DD)의 날짜를 리턴합니다.

MAKETIME(hour,minute,second)

hour, minute, second 값으로 time 형식(HH:MM:SS)의 시간을 리턴합니다.

CONVERT_TZ(dt,from_tz,to_tz)

시간대 변경 함수.

FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)

유닉스 타입의 시간을 리턴합니다.

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

유닉스 시간 ('1970-01-01 00:00:00' 기준)을 리턴합니다. date가 주어지면 date의 유닉스 시간을 리턴합니다.

UTC_DATE, UTC_DATE()

UTC 시간을 리턴합니다.

UTC_TIME, UTC_TIME()

UTC 시간을 리턴합니다.

UTC_TIMESTAMP, UTC_TIMESTAMP()

UTC 시간을 리턴합니다.

MySQL 함수

수학 함수

산술 연산자

+, -, *, /

더하기, 빼기, 곱하기, 나누기 사칙연산을 처리합니다.

DIV

정수 나누기 연산을 처리합니다. FLOOR() 함수로 비슷한 처리가 가능합니다.

SELECT 5 DIV 2;
SELECT FLOOR(5 / 2);

%

나머지 연산으로 MOD() 함수와 같은 역할을 합니다.

SELECT 5 % 2;
SELECT MOD(5, 2);

수학 함수

MOD(N, M), N % M, N MOD M

N을 M으로 나눈 나머지를 리턴합니다.

ABC(X)

절대값을 리턴합니다.

SIGN(X)

양수, 음수에 따라 부호를 리턴합니다.

CEIL(X), CEILING(X)

X보다 작지 않은 정수의 최소값을 리턴합니다.

FLOOR(X)

X보다 크지 않은 정수의 최대값을 리턴합니다.

ROUND(X), ROUND(X, D)

반올림 값을 리턴합니다. 두번째 문법은 D자리까지의 반올림 값을 리턴합니다.

TRUNCATE(X, D)

D자리에서 버림한 값을 리턴합니다.

FORMAT(X, D)

문자열 함수의 FORMAT() 참고.

RAND(), RAND(N)

0 ~ 1 사이의 난수를 리턴합니다. N은 seed 값을 의미합니다.

POW(X, Y), POWER(X, Y)

X의 Y 제곱한 값을 리턴합니다.

SQRT(X)

X의 제곱근을 리턴합니다.

CONV(X, from_base, to_base)

form_base진수 X를 to_base진수로 리턴합니다.

OCT(N)

8진수로 리턴합니다.

HEX(X or str)

문자열 함수 HEX() 참고.

EXP(X)

X의 제곱에 대한 자연로그의 근 e 값을 리턴합니다.

LN(X)

X의 자연로그를 리턴합니다.

LOG(X), LOG(B, X)

로그 근을 구합니다.

LOG2(X)

e를 밑으로 하는 로그를 리턴합니다.

LOG10(X)

상용로그를 리턴합니다.

CRC32(expr)

CRC값을 계산하여 32비트의 부호화되지 않은 값을 리턴합니다.

PI()

원주율 값을 리턴합니다. 기본적으로 7자리의 값을 리턴하지만 MySQL은 이배정도 값까지 리턴할 수 있습니다.

SELECT PI(); -- 3.141593
SELECT PI() + 0.000000000000000000; -- 3.14159265358979

RADIANS(X)

X각도의 라디안 값을 리턴합니다.

DEGREE(X)

X 라이안 값을 각도로 리턴합니다.

SIN(X)

라디안 X의 사인 값을 리턴합니다.

COS(X)

라디안 X의 코사인 값을 리턴합니다.

TAN(X)

라디안 X의 탄젠트 값을 리턴합니다.

ASIN(X)

라디안 X의 역 사인 값을 리턴합니다.

ACOS(X)

라디안 X의 역 코사인 값을 리턴합니다.

ATAN(X)

라디안 X의 역 탄젠트 값을 리턴합니다.

COT(X)

라디안 X의 코탄젠트 값을 리턴합니다.

MySQL 함수

문자열 함수

코드변환과 관련된 함수들

ASCII(str)

문자열 중 가장 왼쪽 문자의 ASCII 코드 값을 리턴합니다. NULL의 경우 NULL을 출력합니다.

SELECT ASCII('ABC'); -- 65

ORD(str)

멀티 바이트 문자열의 가장 왼쪽 문자의 코드값을 리턴합니다.

CHAR(N, ... [USING character_name])

숫자 값에 해당하는 문자들을 리턴합니다.

SELECT CHAR(65, 66, 15380608 USING utf8); -- AB가

BIN(N)

숫자 값을 이진수로 리턴합니다. CONV(N, 10, 2)와 같습니다.

HEX(N or str)

숫자 N을 16진수로 리턴합니다. 문자열의 경우 두자리의 16진수 문자열로 리턴합니다.

UNHEX(str)

16진수 문자열을 문자열로 리턴합니다.

문자열 길이를 구하는 함수들

BIT_LENGTH(str)

문자열의 길이를 bit 단위로 출력합니다.

SELECT BIT_LENGTH('가A'); -- 32 UTF-8 인코딩일 경우 한글은 3byte이며 영문은 1byte로 처리합니다.

LENGTH(str), OCTET_LENGTH(str)

byte단위의 길이를 리턴합니다.

CHAR_LENGTH(str)

글자 수를 리턴합니다. 멀티 바이트 문자의 경우 한 글자로 취급합니다. LENGTH()의 경우 바이트 단위로 글자 수를 셉니다.

SELECT CHAR_LENGTH('가나다'); -- 3

SELECT LENGTH('가나다'); -- 9 (UTF-8 인코딩일 경우 한글은 3byte 처리)

공백 처리 함수들

LTRIM(str)

문자열 왼쪽의 공백을 모두 제거하여 리턴합니다.

RTRIM(str)

문자열 오른쪽의 공백을 모두 제거하여 리턴합니다.

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

문자열 str에서 양쪽으로 모든 remstr 문자열을 제거합니다. remstr이 지정되지 않으면 공백을 제거합니다. BOTH는 양쪽, LEADING은 문자열 왼쪽, TRAILING은 문자열 오른쪽의 모든 remstr 문자열을 제거합니다.

SPACE(N)

N개의 공백 문자열을 리턴합니다.

문자열 처리 함수들

CONCAT(str1, str2)

문자열들을 연결하여 리턴합니다.

SELECT CONCAT('ABC', 'DEF'); -- ABCDEF

CONCAT_WS(separator, str1, str2)

문자열들은 구분자로 연결하여 연결합니다.

SELECT CONCAT_WS(',', 'ABC', 'DEF'); -- ABC,DEF

INSERT(str, pos, len, newstr)

문자열 str을 POS 위치부터 len 길이만큼 잘라낸 후 그 자리를 newstr로 대체합니다.

INSTR(str, substr)

str에서 substr이 처음 나타나는 지점의 위치를 리턴합니다. LOCATE()와 인자의 순서가 바뀌었을 뿐 기능은 같습니다.

POSITION(substr IN str)

LOCATE(substr, str)와 동일한 표현입니다.

LOCATE(substr, str), LOCATE(substr, str, pos)

문자열 str에서 substr이 처음 나타나는 지점의 위치를 리턴합니다. 두 번째 문법은 str에서 pos 위치부터 시작해서 substr이 나타나는 지점의 위치를 리턴합니다.

SUBSTR(str, pos), SUBSTR(str FROM pos), SUBSTR(str, pos, len), SUBSTR(str FROM pos FOR len)

SUBSTRING()과 동일한 표현입니다.

SUBSTRING(str, pos), SUBSTRING(str FROM pos), SUBSTRING(str, pos, len), SUBSTRING(str FROM pos FOR len)

문자열 str를 pos 위치부터 끝까지 문자열를 리턴합니다. 두번째 문법은 문자열 str를 pos 위치부터 len 길이만큼 리턴합니다.

SUBSTRING_INDEX(str, delim, count)

문자열 str을 구분자 delim으로 분리하여 count번째 위치만큼 리턴합니다. count가 양수이면 문자열의 왼쪽부터 순서를 세고, 음수이면 오른쪽부터 순서를 셉니다.

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -- www.mysql

LEFT(str, len)

문자열 str에서 왼쪽으로부터 len 길이만큼 리턴합니다.

RIGHT(str, len)

문자열 str을 오른쪽에서 len 길이만큼 리턴합니다.

MID(str, pos, len)

SUBSTRING(str, pos, len)과 동일한 표현입니다.

REPLACE(str, from_str, to_str)

문자열 str에서 from_str을 찾아서 to_str로 대치하여 리턴합니다.

REVERSE(str)

문자열을 뒤집어서 리턴합니다.

UPDATE t SET BLOB col=LOAD_FILE('file.txt') WHERE id=1;

LPAD(str, len, padstr)

문자열 str의 len길이가 될 때까지 왼쪽에 padstr을 반복하여 붙여서 리턴합니다.

RPAD(str, len, padstr)

문자열 str의 len길이가 될 때까지 오른쪽에 padstr을 반복하여 붙여서 리턴합니다.

REPEAT(str, count)

문자열 str을 count만큼 반복시켜서 리턴합니다.

FORMAT(X, D)

숫자 X를 "#,###,###.##"과 같은 형식으로 리턴합니다. D는 표시할 소수점 자리를 나타냅니다.

QUOTE(str)

문자열 str내에 작은 따옴표가 있을 경우 SQL 문장이 오류가 발생할 수 있기 때문에 이를 이스케이프 문자를 사용하여 처리합니다. 예를 들어 INSERT할 데이터 안에 작은 따옴표가 들어있을 경우 QUOTE() 함수로 묶어 주면 정상적으로 추가할 수 있습니다.

LCASE(str), LOWER(str)

문자열을 소문자로 변환하여 리턴합니다.

UCASE(str), UPPER(str)

문자열을 대문자 문자열로 리턴합니다.

LOAD_FILE(file_name)

문자열 파일을 읽어들입니다.

-- 9월 16일 실습
-- 실습용 데이터 준비
CREATE TABLE jumsu (
    seq SERIAL,
    name VARCHAR(8) NOT NULL,
    java TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    mysql TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    html TINYINT(3) UNSIGNED,
    PRIMARY KEY (seq)
    ) ENGINE = MyISAM;

INSERT INTO jumsu (name, java, mysql, html)
VALUES ('변악도', '100', '100', '100');
INSERT INTO jumsu (name, java, mysql)
VALUES ('성춘향', '60', '85');
INSERT INTO jumsu (name, java, mysql)
VALUES ('홍길동', '80', '80');
INSERT INTO jumsu (name, java, mysql)
VALUES ('이몽룡', '60', '75');

-- 계산식으로 컬럼을 지정
SELECT seq AS `번호`, name AS `이름`, java AS `자바`,
    mysql AS `MySQL`, java + mysql AS `합계`
FROM jumsu
ORDER BY `합계` DESC;

-- 비교 연산자 실습
SELECT '7' > '8';
SELECT 7 = 8;
SELECT 7 <> 8;
SELECT 7 <=> NULL;
SELECT NULL <=> NULL;

-- NULL과의 비교
SELECT * FROM jumsu WHERE html IS NOT NULL;

-- 자바 점수가 60점이상 80점 이하인 행(row) 출력
SELECT * FROM jumsu WHERE java >= 60 AND java <= 80;
SELECT * FROM jumsu WHERE java BETWEEN 60 AND 80;

-- 자바 점수가 60점미만 80점 초과인 행(row) 출력
SELECT * FROM jumsu WHERE java < 60 OR java > 80;
SELECT * FROM jumsu WHERE java NOT BETWEEN 60 AND 80;

-- 홍길동과 성춘향의 점수
SELECT * FROM jumsu WHERE name = '홍길동' OR name = '성춘향';
SELECT * FROM jumsu WHERE name IN ('홍길동', '성춘향');

-- 홍길동과 성춘향을 제외한 사람들의 점수
SELECT * FROM jumsu WHERE name <> '홍길동' AND name <> '성춘향';
SELECT * FROM jumsu WHERE name NOT IN ('홍길동', '성춘향');

-- 우편번호 테이블에서 서울, 인천, 경기 지역의 구군은 총 몇 개?
SELECT COUNT(DISTINCT gugun) FROM ziptable
WHERE sido IN ('서울', '인천', '경기');

-- 홍씨 성을 가진 사람의 자바 점수
SELECT name, java FROM jumsu WHERE name LIKE '홍%';

-- 우편번호 테이블에서 '하'로 시작하는 구군은 몇 개? (중복 무시)
SELECT COUNT(*) FROM ziptable WHERE gugun LIKE '하%';

-- 우편번호 테이블에서 구군의 이름이 3글자인 곳은 몇 개?(중복 무시)
SELECT COUNT(*) FROM ziptable WHERE gugun LIKE '___'; -- "_" 3개 사용

SELECT COUNT(DISTINCT sido) FROM ziptable;
SELECT COUNT(*) FROM ziptable;

-- 우편번호 테이블에서 각 시도별 구군별 동의 개수? (중복 무시)
SELECT sido, gugun, COUNT(*) FROM ziptable GROUP BY sido, gugun;

-- 우편번호 테이블에서 서울, 인천, 경기의  구군별 동의 개수? (중복 무시)
SELECT sido, gugun, COUNT(*) FROM ziptable
WHERE sido IN ('서울', '인천', '경기')
GROUP BY sido, gugun;

-- 우편번호 테이블에서 서울, 인천, 경기의  구군별 동의 개 수를 내림차순 출력? (중복 무시)
SELECT sido, gugun, COUNT(*) FROM ziptable
WHERE sido IN ('서울', '인천', '경기')
GROUP BY sido, gugun
ORDER BY COUNT(*) DESC;

-- 자바와 MySQL 과목의 평균 점수
SELECT AVG((java + mysql) / 2) AS `자바와 MySQL 합계 평균` FROM jumsu;
SELECT java, mysql, java + mysql AS `sum`, (java + mysql) / 2 AS `avg` FROM jumsu; 
SELECT name, java, mysql, (java + mysql) / 2 FROM jumsu;

-- GROUP_CONCAT() 함수 사용 예
CREATE TABLE score (
    seq SERIAL,
    name VARCHAR(8),
    test_score TINYINT UNSIGNED,
    PRIMARY KEY (seq)
    ) ENGINE = MyISAM;

INSERT INTO score (name, test_score) VALUES ('홍길동', '95');
INSERT INTO score (name, test_score) VALUES ('김제동', '95');
INSERT INTO score (name) VALUES ('홍길동');
INSERT INTO score (name, test_score) VALUES ('홍길동', '100');
INSERT INTO score (name, test_score) VALUES ('김제동', '90');
INSERT INTO score (name) VALUES ('홍길동');

SELECT name, GROUP_CONCAT(test_score)
FROM score
GROUP BY name;