[목차]
01. 데이터 타입
종류
💡데이터 타입의 종류가 왜 이렇게 많을까요?➡️ RDBMS는 테이블 구조이고, 데이터들이 DB서버의 공간을 계속 차지한다.
➡️ 저장 공간을 효율적으로 사용하는 것이 중요하다.
➡️ 데이터베이스도 낭비를 줄이기 위해 용량을 생각하여 데이터 타입을 지정해야 한다.
💡데이터 형식이 다양한 이유➡️ 크게 문자형, 숫자형, 날짜형으로 나누고 실제 저장되는 데이터 타입이 다양하다.
➡️ 각 데이터에 맞는 타입을 지정해서 호율적으로 저장할 수 있다.
➡️ 숫자형
👉🏻SMALLINT, INT, BIGINT, FLOAT → 자주 사용UNSIGNED(예약어)
예시) 사람 키 데이터를 삽입하는 컬럼
- SMALLINT 사용 시 : 사람 키는 3만까지 필요 없으므로 비효율.
- TINYINT(-128 ~ + 127) 사용 시 : 키가 2미터가 넘으면 부족
- UNSIGNED 작성으로 범위가 0 ~ 255로 변경 가능하다.
➡️ 문자형
👉🏻CHAR, VARCHAR, LONGTEXT, LONGBLOB → 자주 사용- CHAR : CHARACTER의 줄임말, 고정되어 있는 고정 길이 문자형
- CHAR(5) : 공백 포함해서 5길이를 가진다.
- VARCHAR : 가변 길이 문자형
- VARCHER(5) : 공백 미포함. 공간을 더 효율적으로 사용할 수 있는 장점이 있지만, 불필요하게 많이 작성한 VARCHAR의 길이값도 성늘을 저하시킬 수 있다.
➡️ 글자 개수가 정해져 있는 CHAR를 사용하는게 좋다.
CHAR로 되어 있는 데이터가 조회할 때 내부적으로 더 좋은 성능을 가진다.
- BLOB(Binary Large Object) : 사진 파일, 동영상 파일, 문서 파일과 같은 데이터를 저장하는데 사용한다.
MySQL에서는 대량의 데이터를 저장하기 위해 LONGTEXT, LONGBLOB 타입을 지원. 약 4GB 크기의 파일을 하나의 데이터로 저장할 수 있다.
➡️ 날짜형
👉🏻DATE, DATETIME- DATE : 날짜만 저장
- TIME : 시간만 저장
- DATETIME : 둘 다 저장
- DATETIME과 TIMESTAMP 비교
- TIMESTAMP는 시간대에 영향을 받는다. DB서버에 저장되는 시간에 따라서 다르게 저장된다.
➡️ 기타
👉🏻JSON : JSON문서를 저장GEOMETRY : 공간 데이터 형식. 공간 데이터 개체를 저장하고 조작한다. GIS 프로젝트에서 사용.
02. 데이터 모델링
모델링
💡현실에서 사용하는 복잡한 데이터를 어떻게 컴퓨터의 DBMS에 옮겨 놓을지 결정하는 과정이다.➡️ 데이터 모델링 : 건축
- 현실 세계 → 개념 세계 → 컴퓨터 세계
➡️ 네이버로그인 → 회원 정보 입력, IP 등 → 테이블 형태
- 특징
- 추상화
➡️ 현실 세계를 간략히 표현
- 단순화
➡️ 누구나 쉽게 이해할 수 있도록 표시한다.
- 명확성
➡️ 명확하게 의미가 해석되고, 한 가지 의미만 가져야 한다.
- 현실에서 쓰이는 것을 테이블로 변경하기 위한 작업이다.
변환 대상 : 실체가 있는 대상 + 행동까지 변환 가능하다.
➡️ 프로세스를 생각했을 때 필요한 테이블
예시) 상품을 구매 → 상품, 직원, 고객, 구매, 배송 테이블
- 데이터 베이스 생명 주기
- 사용자 요구 사항에 의해 최초 구축, 필요에 따라 개선 또는 재구축
- 단계
➡️ 요구 사항 수집 및 분석, 설계, 구현, 운영, 개선
- 모델링 3단계
- 개념적 모델링
➡️ 업무 분석 단계에 포함
➡️ 사람이 이해할 수 있도록 현실 세계를 개념적인 형태의 모델링 진행
➡️ E-R 다이어그램 작성
➡️ 요구 사항 수집, 분석 → 결과 → 핵심 개념 즉, 개체라고 하는 것을 추출. 개체들의 관례를 정의해서 E-R 다이어그램을 만든다.
- 논리적 모델링
➡️ 업무 분석 후반부와 시스템 설계
➡️ 개념적 구조를 논리적 형태로 모델링
➡️ 데이터베이스의 논리적인 구조로 표현
- 물리적 모델링
➡️ 시스템 설계 후반부에 주로 진행
03. 데이터 모델의 구성 요소
1) 개체(Entity) - 직사각형
👉🏻사람이 생각하는 개념, 정보와 같은 현실 세계의 대상입니다.➡️ 개체와 개체 타입으로 분류할 수 있다.
- 개체 : 업무에 필요한 유용한 정보를 저장하고 관리하기 위해 영속적으로 존재하는 단위
- 개체들의 집합은 개체 타입이다.
2) 속성 - 타원
👉🏻데이터의 가장 작은 단위➡️ 개체에서 관리하고자 하는 더 이상 나눠지지 않는 최소 데이터 단위
➡️ 개체가 가지는 동일한 성격, 특징 파악.
➡️ 예시) 학생(개체) → 학번, 이름, 주소, 연락처, 전공, 학년
3) 관계 - 마름모
👉🏻개체 간 관계 또는 속성 간 논리적 연결- 개체와 개체가 맺고 있는 의미 있는 연관성
➡️ 예시) 고객 - 상품 : 고객 개체와 상품 개체는 구매라는 관계가 있다.
- 종류
➡️ 1:1, 1:n, n:m
04. MySQL 변수
구문
➡️ SET @변수이름 := 값;
SET @num1 := 10; SET @num2 := 20; SELECT @num1 AS '변수1', @num2 AS '변수2';➡️ LIMIT의 경우 변수를 사용할 수 없으나, PREPARE문 EXCUTE문을 사용하여 변수를 사용할 수 있다.
👉🏻PREPARE 쿼리_이름FROM
‘쿼리문’;
➡️ 쿼리문을 준비하고, 실행은 하지 않는다.
➡️ EXECUTE 쿼리_이름을 만나는 순간 실행되며,
➡️ 쿼리문의 ? 자리에는 USING 변수명을 사용하면 값이 대입된다.
SET @cityName := '도시의 이름 :'; SET @num := 10; PREPARE queryTest FROM 'SELECT @cityName, c.Name, c.CountryCode FROM city AS c LIMIT ?'; EXECUTE queryTest USING @num;
05. 데이터 형 변환
종류
➡️ 명시적인 변환
👉🏻내가 직접 함수를 사용해서 변환아래 두 함수는 형식만 다르고 기능은 동일하다.
- CAST(값 AS 데이터_형식[(길이)])
- CONVERT(값, 데이터_형식[(길이)])
- SELECT CAST(’2023%01%15’ AS DATE);
SELECT CAST('2023@01@15' AS DATE);
SELECT CAST('2023/01/15' AS DATE);
➡️ 암시적인 변환
👉🏻별도의 작업 없이 자연스럽게 변환
06. SQL 내장함수
- 내장 함수 : DBMS가 제공하는 함수
- 사용자 정의 함수 : 사용자가 직접 만드는 함수
➡️ 내장 함수는 최초 선언 시, 유효한 값을 입력 받아야 한다.
선언에 위배되는 값을 입력하면, 실행이 중지되고 에러 메시지가 출력된다.
SELECT, UPDATE, DELETE에서 모두 사용 가능하다.
주요 내장 함수의 종류
➡️ 제어 흐름 함수
👉🏻프로그램의 흐름을 제어한다.종류
- IF(수식, 참, 거짓)
➡️ SELECT if(100 > 300, 'T', 'F');
- IFNULL(수식1, 수식2)
➡️ 수식1이 NULL이 아니면, 수식1 반환. NULL이면 수식2 반환
➡️ SELECT IFNULL(NULL, 'NULL입니까'), IFNULL(300, 'NULL입니까');
- NULLIF(수식1, 수식2)
➡️ 수식1과 수식2가 같으면 NULL 반환. 다르면 수식1 반환
➡️ SELECT NULLIF(200, 200), NULLIF(200, 500);
➡️ 숫자 함수
👉🏻종류- ABS(숫자)
➡️ 숫자의 절대값 계산
➡️ SELECT ABS(-27), ABS(27);
- CEIL OR CEILING(숫자)
➡️ 숫자보다 크거나 같은 최솟값의 정수
➡️ SELECT CEIL(4.7)
- FLOOR(숫자)
➡️ 숫자보다 작거나 같은 최소의 정수
➡️ SELECT FLOOR(4.7);
- ROUND(숫자, n)
➡️ 숫자의 반올림. n 반올림 자릿수
➡️ SELECT ROUND(3.14159, 2);
➡️ 문자 함수(문자를 반환하는)
👉🏻종류- CONCAT(str1, str2)
➡️ 문자열 연결
➡️ SELECT CONCAT_WS('-', '010','1234','5678'); : 구분자와 함께 문자열 연결 : 010-1234-5678
- REPLACE(str1, str2, str3)
➡️ 원하는 문자열로 변경
➡️ SELECT REPLACE('마시는 비타민 비타500', '비타민', '비타민C'); : 비타민을 비타민C로 변경
- SUBSTR(str1, num1, num2)
➡️ 시작 위치부터 길이만큼 문자를 반환.
SUBSTR = SUBSTRING = MID
➡️ SELECT SUBSTR('안녕하세요', 1, 2);
SELECT SUBSTRING('안녕하세요', 1, 2);
SELECT MID('안녕하세요', 1, 2);
- TRIM
➡️ 문자열 앞 뒤 공백 제거
➡️ SELECT TRIM(' 안녕하세요 ');
➡️ 문자 함수(숫자를 반환하는)
👉🏻종류- ASCII(char)
➡️ SELECT ASCII('A'), CHAR(65);
- LENGTH(str)
➡️ 할당된 byte 수를 반환
➡️ SELECT LENGTH('가나다'), LENGTH('abc');
➡️ SELECT CHAR_LENGTH('가나다') : 문자의 개수를 반환
➡️ 날짜 및 시간 함수
👉🏻종류- ADDDATE()
➡️ 날짜에서 더한 날짜
➡️ SELECT ADDDATE('2023-01-01', INTERVAL 31 DAY), ADDDATE('2023-01-01', INTERVAL 1 MONTH);
- SUBDATE()
➡️ 날짜에서 뺀 날짜
➡️ SELECT SUBDATE('2023-05-15', INTERVAL 10 DAY);
- ADDTIME()
➡️ 시간에서 더한 시간
- SUBTIME()
➡️ 시간에서 뺀 시간
- CURDATE(), CURTIME(), SYSDATE()
➡️ 현재날짜, 현재시간, 둘다
➡️ SELECT CURDATE();
SELECT CURTIME();
SELECT SYSDATE();
- DATE(), TIME()
- DATEDIFF()
➡️ 날짜 차이를 계산
➡️ SELECT DATEDIFF('2023-12-31', NOW());
➡️ 변환 함수
➡️ NULL 관련 함수
➡️ 기타 : 시스템 함수(시스템 정보 함수)
👉🏻시스템의 정보를 출력하는 함수를 제공한다.종류
- USER()
➡️ SELECT USER();
- DATABASE()
➡️ SELECT DATABASE();
- VERSION
➡️ SELECT VERSION();
- SLEEP(초)
➡️ 의도적으로 쿼리 실행을 멈춘다.
➡️ SELECT SLEEP(3); // 3초 뒤 다음 쿼리 실행
- IF(수식, 참, 거짓)
07. 윈도우 함수
➡️ 윈도우 함수는 GROUP BY와 비슷한 역할
➡️ 집계 함수도 대부분 사용 가능하고, 추가적으로 윈도우 함수에서만 지원하는 기능을 사용할 수 있다.
➡️ 행과 행을 비교하고 연산한다.
➡️ GROUP BY를 사용하지 않고, 속성의 값을 집계할 수 있다.
➡️ GROUP BY는 내용을 축약해서 보여주지만, window function은 축약하지 않고 펼쳐서 보여준다.
구문
➡️ 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 테이블명;➡️ MAX
-- GROUP BY 사용 SELECT c.Name, c.CountryCode, MAX(c.Population) AS 'max_popul' FROM city AS c GROUP BY c.CountryCode; -- PARTITION BY 사용 SELECT c.Name, c.CountryCode, MAX(c.Population) OVER (PARTITION BY c.CountryCode) AS max_popul FROM city AS c;➡️ SUM
SELECT c.Name AS '이름', c.CountryCode AS '코드', c.Population, SUM(c.Population) OVER (PARTITION BY c.CountryCode) AS sum_popul FROM city AS c;- SUM에 ORDER BY를 사용하면, 누적합을 구할 수 있다.
➡️ 순위 함수
👉🏻종류- RANK()
➡️ 값이 동일하다면, 같은 순위를 부여하고, 1이 3개 있었다면 2와 3없이 4로 넘어간다.
- ROW_NUMBER()
➡️ 작은 순서부터 출력. 값이 커지는 순서에 따라 순위도 높아진다. 어떻게든 순서를 정해서 중복되는 순서가 없어요.
- DENSE RANK()
➡️ 값이 동일하다면, 같은 순위를 부여하고, RANK와 다르게 순차적으로 부여한다.
구문
- 순위_함수() OVER (ORDER BY 컬럼_명)
- 인자에 아무 값도 작성하지 않는다.
tag : #데이터베이스 #DB #데이터타입 #개체 #속성 #관계 #데이터모델링 #변수 #변환 #내장함수 #윈도우함수
Uploaded by N2T
