데이터 모델링/MySQL 변수

데이터 모델링(소개 간단한 내용)
->정규화, 다른 진도 나가면서 자세히
데이터 타입
이비 설치, 연결
내장 함수, mysql변수
윈도우 함수

 

=========================================

데이터 타입

1) 데이터 타입의 종류가 왜 이렇게 많을까?

RDBMS는 테이블 구조

데이터들이 DB서버의 공간을 계속 차지.

->저장 공간을 효율적으로 사용하는 것이 중요하다.

0-999

6자리

데이터베이스도 낭비를 줄이기 위해 용량을 생각하여 데이터 타입을 지정해야 한다.

 

데이터 형식이 다양한 이유

크게 문자형, 숫자형, 날짜형으로 나누고

실제 저장되는 데이터 타입이 다양하다.

각 데이터에 맞는 타입을 지정해서 효율적으로 저장할 수 있다.

 

숫자형

-자주 사용: SMALLINT, INT, BIGINT, FLOAT

*UNSIGNED : 예약어

 

사람 키 데이터를 삽입하는 컬럼

SMALLINT 사람 키 3만CM없음, 비효율

TINYINT:127키가 2M넘으면 부족함.

-128~+127 또는 0~255

 

사람 정보 관련 테이블 만든다는 가정.

MEMBER_HEIGHT TINYINT UNSIGNED

UNSIGNED 작성으로 범위가 변경

0~255사용 가능

 

문자형 데이터 타입

-자주 사용: CHAR, VARCHAR, LONGTEXT, LONGBLOB

CHAR: CHARACTER의 줄임말, 고정되어 있는 (고정 길이 문자형).

VARCHAR: VARIABLE CHAR(가변 길이 문자형)

CHAR(5)

VARCHAR(5) 

VARCHAR를 사용하면 공간을 더 효율적으로 사용할 수 있는 장점이 있지만, 불필요하게 많이 작성한 VARCHAR의 길이값도 성능을 저하시킬 수 있다.

글자 개수가 정해져있는 CHAR를 사용하는게 좋음.

*CHAR로 되어있는 데이터가 조회할 때 내부적으로 더 좋은 성능을 발휘한다.

 

BLOB(Binary Large Object): 사진 파일, 동영상 파일, 문서 파일과 같은 데이터를 저장하는데 사용.

MySQL에서는 대량의 데이터를 저장하기 위해 LONGTEXT, LONGBLOB타입을 지원. 약 4GB크기의 파일을 하나의 데이터로 저장할 수 있다.

 

*데이터가 숫자로서의 의미를 가지는 경우

063-

010-

0

-> 이런 전화번호들은 "문자"로 지정함.

 

*숫자로서의 의미 같는거는

1)+,-연산에 의미가 있어야한다

2)크다, 작다와 같은 비교 가능

 

날짜 데이터 형식

-DATE, DATETIME

DATE: 날짜 저장

TIME: 시간만 저장.

둘 다 저장 -> DATETIME

예) 구매 테이블에서 기록이 필요하다면, DATETIME사용

날짜와 시간을 입력할 때에는 문자와 동일하게 작은 따옴표로 묶어서 작성한다.

 

DATETIME과 TIMESTAMP비교

TIMESTAMP는 시간대에 영향 받는다. DB서버에 저장되는 시간에 따라서 다르게 저장.

 

기타

JSON: JSON문서를 저장

GEOMETRY: 공간 데이터 형식. 공간 데이터 개체를 저장하고 조작한다.

 

GIS프로젝트에서 사용.

전주천 - CHAR작성

하천의 좌표값 작성 - >GEOMETRY타입으로 저장.

공간 데이터 함수를 함께 사용한다.

 

 

팀플, 현업 유지보수 -> dbeaver

데이터수정 또는 삭제

관계 - 제약 조건

내가 삭제하려는 테이블을 찾아서 어떤 테이블이랑 관계가 맺어있는지 확인하고, 순차적으로 데이터를 삭제한다.

=======================

내장 함수, MySQL 변수

SQL의 함수는 크게 두 종류.

내장 함수: DBMS가 제공하는 함수

사용자 정의 함수: 사용자가 직접 만드는 함수

 

내장 함수는 최초 선언 시, 유효한 값을 입력 받아야 한다.

선언에 위배되는 값을 입력하면, 실행이 중지되고 에러 메시지가 출력된다. SELECT, UPDATE, DELETE에서 모두 사용 가능하다.

 

주요 내장 함수의 종류

-제어 흐름 함수

-숫자 함수

-문자 함수(문자를 반환하는)

-문자 함수(숫자를 반환하는)

-날짜 밑 시간 함수

-변환 함수

-NULL 관련 함수

 

기타: 시스템 함수

 

-제어 흐름 함수:프로그램의 흐름을 제어한다.

종류

IF(수식, 참, 거짓)

IFNULL(수식1, 수식2)

-> 수식1이 NULL이 아니면 수식1 반환. NULL이면 수식2 반환

NULLIF(수식1, 수식2)

->수식1과 수식2가 같으면, NULL가 반환. 다르면 수식1반환

 

숫자 함수

종류

-ABS(숫자) : 숫자의 절댓값 계산

-CEIL(숫자) OR CEILING : 숫자보다 크거나 같은 최솟값의 정수 

-FLOOR(숫자): 숫자보다 작거나 같은 최소의 정수

-ROUND(숫자, n): 숫자의 반올림. n 반올림 자릿수

===============================

문자함수(문자를 반환하는)

종류

-CONCAT(str1, str2): 문자열 연결

-CONCAT_WS:구분자와 함께 문자열 연결

-REPLACE(str1, str2, str3): 원하는 문자열로 변경

-SUBSTR((str1, num1, num2) : 시작 위치부터 길이만큼 문자를 반환 = SUBSTRING =MID

-TRIM: 문자열 앞 뒤 공백 제거

 

문자함수(숫자를 반환하는)

종류

ASCII(char)

LENGTH(str)

 

ASCII(char)

LENGTH(str) - 할당된 byte수를 반환

CHAR_LENGTH : 문자의 개수를 반환

 

 

==============================

날짜 및 시간 함수

종류

-ADDDATE()

-SUBDATE()

-ADDTIME()

-SUBTIME()

-CURDATE(), CURTIME(), SYSDATE()

-DATEDIFF(), 

 

 

시스템 함수(=시스템 정보 함수)

-시스템의 정보를 출력하는 함수를 제공한다.

종류

-user()

-DATABASE();

-VERSION

-SLEEP(초):의도적으로 쿼리 실행을 멈춘다.

3초 후에 실행됨

 

 

 

==============================================

MySQL 변수

-다른 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있는데, 형태가 약간 다르다

 

구문

SET @변수_이름 := 대입값;

LIMIT의 경우, 변수를 사용할 수 없으나,

PREPARE문 EXECUTE문을 사용하여 변수를 사용할 수 있다.

 

구문

PREPARE 쿼리_이름

FROM

'쿼리문'

->쿼리문을 준비하고, 실행은 하지 않는다

->EXECUTE 쿼리_이름을 만나는 순간 실행되며,

->쿼리문의 ? 자리: USING변수명을 대입한다.

===============================================

데이터의 형 변환

종류

명시적인 변환: 내가 직접 함수를 사용해서 변환

암시적인 변환: 별도의 작업 없이 자연스럽게 변환

명시적인 형 변환(아래 두 함수는 형식만 다르고 기능은 동일하다)

-CAST(값 AS 데이터_형식[(길이)]

-CONVERT(값, 데이터_형식[(길이)]

=====================================================

윈도우함수(WINDOW FUNCTION)

-MySQL 8버전 이상에서 지원

*낮은 버전의 개발 환경에서는 사용할 수 없다.

-원도우 함수는 GROUP BY와 비슷한 역할

-집계 함수도 대부분 사용 가능하고, 추가적으로 윈도우 함수에서만 지원하는 기능을 사용할 수 있다.

 

1)행과 행을 비교

2)행과 행에 대한 연산

3)GROUP BY를 사용하지 않고, 속성의 값을 집계할 수 있다.

 

구문(OVER,PARTITION BY ORDER BY)

*PARTITION BY가 ORDER BY보다 앞에 나와야함

*PARTITION BY와 ORDER BY가 둘 다 있어야하는건 아니다.

 

함수(컬럼) OVER(PARTITION BY 컬럼명 ORDER BY컬럼명)

 

함수:집계 함수

컬럼: 집계 대상 열 이름 작성

PARTITION BY: 어떤 컬럼을 그룹으로 만들지 기준이 되는 컬럼을 작성한다.

ORDER BY 컬럼명: 데이터 정렬

 

SELECT 

   함수()OVER (PARTITION BY 컬럼명 ORDER BY 컬럼명 내림차순 또는 오름차순)

FROM

   테이블_이름;

 

국가 코드 기준으로 GROUP BY사용

이름, 국가코드, 인구 최댓값 구해보세요.

집계 함수: MAX

 

GROUP BY와 Window Fn 차이

-GROUP BY는 내용을 축약해서 보여준다.

-Window Fn는 축약하지 않고 펼쳐서

 

 

 

 

SUM

window fn sum

국가코드 별 인구수의 합 구하는데

조회

name, countryCode, population, windowfn 조회 (4개)

 

*SUM에 ORDER BY를 사용하면, 누적합을 구할 수 있다.

==========================================

순위

종류

-RANK

-ROW_NUMBER

-DENSE RANK

구문

순위_함수() OVER (ORDER BY 컬럼_명)

*인자에 아무 값도 작성하지 않는다.

 

ROW_NUMBER

-작은 순서부터 출력. 값이 커지는 순서에 따라 순위도 높아진다.

-어떻게든 순서를 정해서 중복되는 순서가 없어요.(X)

 

RANK

-값이 동일하다면, 같은 순위를 부여해요.

 

DENSE RANK

-rank는 1이 3개 있었다면, 2와 3없이 4로 넘어가지만,

-dense rank는 순차적으로 부여.

 

================================

데이터 모델링

-모델링?

현실에서 사용하는 복잡한 데이터를 어떻게 컴퓨터의 DBMS에 옮겨 놓을지 결정하는 과정

 

과거: 프로젝트이 규모 작았고, 사용자 요구 사항도 단순

현재: 프로젝트의 규모 크고, 사용자 요구 사항도 복잡해짐.

 

데이터 모델링 - 건축

 

데이터 모델링

현실 세계>개념 세계> 컴퓨터 세계

네이버 로그인 >  회원 아이디, 비밀번호, 로그인 날짜, IP 등등> 테이블 형태로

 

특징

1.추상화: 현실 세계를 간략히 표현

2.단순화: 누구나 쉽게 이해할 수 있도록 표시한다.

3.명확성: 명확하게 의미가 해석되고, 한 가지 의미만 가져야 한다.

 

현실에서 쓰이는 것을 테이블로 변경하기 위한 작업이다.

변환 대상:실체가 있는 대상 + 행동까지 변환 가능하다.

 

나 > 올리브영

직원 상담, 상품을 구매(현실)

-상품 테이블, 직원 테이블, 고객 테이블, 구매 테이블, 배송 테이블

 

사람의 신분을 증명하는 신분증

-> 사람 정보_테이블: 주민번호, 이름, 주소, 지문

 

모델링 단계

크게 3개의 단계

 

데이터 베이스 생명주기

- 사용자 요구 사항에 의해 최초 구축, 필요에 따라 개선 또는 재구축

-단계: 요구 사항 수집 밑 분석, 설계, 구현, 운영, 개선

 

개념적 모델링: 업무 분석 단계에 포함

논리적 모델링: 업무 분석 후반부와 시스템 설계

물리적 모델링: 시스템 설계 후반부에 주로 진행

 

개념적 모델링

-사람이 이해할 수 있도록 현실 세계를 개념적인 형태의 모델링 진행

-E-R다이어그램 작성

요구 사항 수집, 분석 > 결과 > 핵심 개념 즉, 개체라고 하는것을 추출.개체들의 관계를 정의해서 E-R다이어그램을 만든다.

 

논리적 데이터 모델

-개념적 구조를 논리적 형태로 모델링

-데이터베이스의 논리적인 구조로 표현