[DB] 데이터베이스 구축 절차 및 선택 가이드, SQL 작성 방법

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

[목차]

01. 데이터베이스 구축 절차

💡

데이터베이스 만들기 → 테이블 만들기 → 데이터 입력, 수정, 삭제하기 → 데이터를 조회하고 활용하기

➡️ 데이터베이스는 데이터를 저장하는 공간이고, 가장 먼저 데이터 베이스를 준비하고, 그 안에 테이블을 생성해야 한다.

1) 종류
👉🏻
계층형, 망형, 관계형, 객체관계형 등
➡️ 가장 많이 사용하는 관계형 데이터 베이스(RDBMS)
- 데이터가 테이블에 저장(최소 단위)
- 테이블 하나 이상의 열과 행으로 이루어진다.

2) 폴더 구조
➡️ bin(중요) : MySQL의 서버 프로그램, 클라이언트 프로그램 및 유틸리티 프로그램 파일
➡️ docs : 도움말 또는 설정 파일
➡️ etc : 설정 파일 샘플
➡️ include : 응용 프로그램을 개발할 때 필요한 헤더 파일들
➡️ lib : MySQL과 관련된 라이브러리 파일
➡️ share : 기타 지원 파일, 각 언어 별 오류 메시지 파일 등

3) MySQL 환경 변수 설정하기
➡️ 환경 변수 설정 : SETX PATH ”경로;%PATH%”
➡️ mysql -V : 버전 확인하기
➡️ mysql -u root -p : 서버 접속
➡️ show databases; : DB목록
➡️ use DB명 : 해당 DB를 사용
➡️ show tables; : 테이블 목록
➡️ 쿼리문 작성 가능

02. 데이터베이스 선택 가이드

1) KEY - VALUE DATABASE
💡
데이터를 키-값으로 저장하는 데이터베이스

실용성이 떨어지고, 서브DB로 사용한다.

Redis
➡️ 데이터를 하드디스크에 저장. Redis는 RAM에 저장(속도 빠름)
➡️ 메인 DB를 RAM에 복사하고, 필요한 데이터를 RAM에서 꺼내서 사용.

2) RDBMS
💡
데이터를 표 형태로 저장하고 싶을 때, 관계형 데이터베이스를 사용한다.

➡️ 어떻게 데이터를 저장할지 이름(속성)을 작성하고, 데이터를 저장한다.
➡️ 다양한 분야에서 사용할 수 있어서 상위권.
➡️ RDBMS는 데이터를 저장하고 싶으면 SQL을 사용한다.
➡️ RDBMS는 데이터의 중복을 싫어한다. 그래서 반드시 지켜야하는 것이 정규화이다.
➡️ 데이터가 중복되면 다른 테이블로 옮긴다.
➡️ 정규화 진행 > 데이터를 조회하는 문법이 어려워지고, 복잡해진다.(단점)
➡️ 장점 : 기본적으로 트랜잭션을 가지고 있어서, 돈 거래에 유용
➡️ 데이터의 입출력 속도보다는 정확도가 중요한 서비스를 만든다면 일반적으로 RDBMS를 선택한다.

3) Graph DATABASE
💡
graphQL 언어를 사용해야 한다.

SNS의 친구 관계도, 전염병 전염되는 지도, 항공기 노선 등

4) Document DATABASE
💡
폴더를 하나 만들고, 폴더 안에 Document라는 파일을 만들어서 데이터를 JSON형태로 저장한다.

➡️ 관계형 데이터베이스보다 자유롭다.
➡️ 예시) 이름 : KIM 나이 : 30
➡️ 어떤 데이터를 저장할지 속성을 미리 지정할 필요가 없다.
➡️ 예) 기존 구조 : 이름 나이 → 이름 나이 주소
구조가 변경되어도 에러가 발생하지 않는다.
데이터의 중복을 제거하지 않는다.
정규화 없다.
DB의 정확도가 떨어질 수 있다.

5) Column-family DATABASE
💡
관계형 DB와 같이 표 형식으로 데이터를 저장하고 싶고, 유연하게 사용하고 싶을 때 사용한다.

➡️ 똑같이 테이블을 만들고, ROW를 만드는데, 데이터의 삽입이 자유롭다.
➡️ 행마다 컬럼이 달라도 무관하다.
➡️ 단, 데이터의 입출력 하려면 SQL을 사용하지 않고, 따로 만든 언어를 사용해야 한다.
➡️ 정규화를 안한다. → 데이터 중복 허용
➡️ 장점 : 데이터 입출력이 쉽다. 분산 처리 용이, 많은 양의 데이터 입출력을 감당해야 하는 경우

6) Search engin
💡
index를 보관하는 용도로 사용한다.

빠른 검색을 위한 목차의 역할

검색이 중요한 사이트를 만들 때 사용한다.

03. SQL(Structured Query Language)

SQL
👉🏻
데이터베이스와 대화하기 위해 만들어진 언어

데이버베이스와 통신하는 언어

어떻게 데이터베이스와 대화하는지 알아야 한다.

SQL 표준
👉🏻
어떤 특정한 회사에서 만들지 않는다.

국제 표준화 기구 발표

단, DBMS를 만드는 회사가 다양하기 때문에 위 표준을 전부 지킬 수 없다.

최대한 SQL 표준을 준수하면서 회사별 특성을 반영한 SQL을 사용한다.

릴레이션 스키마와 인스턴스
👉🏻
스키마
➡️ RDBMS의 릴레이션이 어떻게 구성되는지 어떤 정보를 담고 있는지 기본적인 구조를 정의한다. 테이블에서 스키마는 테이블의 첫 행(Header)에 나타나며, 속성과 자료 타입에 대한 정보를 가지고 있다.

인스턴스
➡️ 정의된 스키마에 따라 테이블에 실제로 저장되는 데이터의 집합

데이터 언어
👉🏻
DDL
➡️ 데이터베이스를 구축하거나 수정하는 목적으로 테이블이나 관계의 구조를 생성하는데 사용한다.

DML
➡️ 테이블의 데이터를 검색, 삽입, 수정, 삭제 하는데 사용한다.

DCL
➡️ 데이터의 사용 권한을 관리하는데 사용한다.

SQL 작성 시 주의사항
👉🏻
1️⃣ 가독성을 위해 들여쓰기를 반드시 준수 할 것
2️⃣ 작성 후 세미콜론을 반드시 작성 할 것

SQL 실습
1️⃣ 데이터베이스 생성
```
CREATE DATABASE DB명 DEFAULT CHARACTER SET UTF-8;
```
2️⃣ 사용자 계정 추가(아이디 생성)
```
CREATE USER 'id'@'%'IDENTIFIED BY 'pw';
```
- % : 외부 접근 허용
- 이메일 : 내 아이디@도메인
- @ : 구분자
- mysql 계정 형식 : user id + host
- HOST : 네트워크에 연결되어 있는 컴퓨터
3️⃣ 사용자에게 권한 부여하기
```
GRANT ALL PRIVILEGES ON DB명.* TO 'id'@'%';
```
4️⃣ 변경된 내용을 메모리에 반영하기
```
FLUSH PRIVILEGES;
```
5️⃣ 테이블 만들기
```
CREATE TABLE 테이블명 (
	...
);
```
6️⃣ 생성한 테이블에 데이터를 삽입, 수정, 삭제
```
-- 삽입
INSERT INTO 테이블명 (컬럼명) VALUES (값);

-- 수정
UPDATE 테이블명 SET 컬럼='값' WHERE 컬럼='값';

-- 삭제
DELETE FROM 테이블명 WHERE 컬럼명='값';

-- 백업 테이블 만들기
CREATE TABLE 테이블명 (
	SELECT 컬럼명 FROM 기존테이블명
);
```
➡️ INSERT 시 주의사항
- 테이블 이름 다음에 나오는 열 이름은 생략 가능. 단, 생략하려면 values 뒤에 나오는 값들과 순서, 개수가 테이블을 만들 때 지정한 값, 순서, 개수와 동일해야 한다.
➡️ UPDATE 시 주의사항
- where절이 없다면 테이블의 모든 행의 값이 변경된다.
- 명확한 조건을 명시하지 않으면 테이블의 모든 이름이 똑같이 변경된다.
💡
UPDATE, DELETE 시 SELECT해서 꼭 확인하고 실행한다.

04. SQL 실습

SELECT
💡
가장 많이 사용하며, 데이터베이스 내 테이블에서 원하는 데이터를 추출하는 명령어이다.

SELECT를 잘 사용해야 데이터베이스를 잘 활용할 수 있다.

➡️ 기본 구문 : select 컬럼명 from 테이블명;
➡️ 실행 순서 : from → select

AS(alias)
💡
원하는 이름을 붙일 수 있다.
생략은 가능하다.
열의 이름이 길어지는 경우 별칭을 사용하면 좋다.
테이블 이름, 열 이름, WHERE절 사용 가능.
길어서 보기 힘든 컬럼, 계산식이 복잡한 컬럼에 별칭을 사용하면 좋다.
별칭 사용 시 작은 따옴표를 사용하는 것을 권장한다.

WHERE
💡
조건을 지정하는 where절
조회하려는 결과에 특정한 조건을 줘서 원하는 데이터만 보고 싶을 때 사용한다.
WHERE절 없이 조회하면 모든 데이터를 조회(테이블의 모든 행)

➡️ 기본 형식 : select ~ from ~ where 조건;
➡️ 실행 순서 : from → where → select

BETWEEN ~ AND
💡
범위를 지정한다.

집합, IN()
💡
OR를 사용하면 하나씩 비교하니까 성능 저하

➡️ 컬럼명 IN(”값”, “값”, …); → 코드가 간결해진다.

문자열의 내용을 검색하는 LIKE 연산자
💡
LIKE 키워드를 사용하여 문자를 필터링한다.

%기호는 그 자리에 어떤 값이 들어와도 상관 없다는 의미.

➡️ 예시) LIKE’한국%’ → 한국으로 시작하는 문자열을 찾는데, %자리에 어떤 문자열이 들어와도 상관없다.

➡️ LIKE 심화
💡
LIKE’한국___’ (언더바 3개)
➡️ 5글자 탐색, 한국OOO

LIKE’_국’ (언더바 1개)
➡️ 2글자 탐색, O국

NOT LIKE’a%’
➡️ a로 시작하지 않는 + 아무 문자열
```
-- city 테이블에서 국가 코드가 K, U, A로 시작하는 NAME을 조회하시오.
SELECT DISTINCT
	c.Name AS '나라이름',
	c.CountryCode '코드'
FROM
	city AS c
WHERE
	c.CountryCode LIKE'K%' 
	OR 
	c.CountryCode LIKE'U%'
	OR 
	c.CountryCode LIKE'A%';
```

DISTINCT
💡
중복을 제거한다.

➡️ SQL은 기본적으로 조회 했을 때 중복을 제거하지 않는다.
➡️ 하나의 ROW를 기준으로 전체 컬럼을 비교한다.

LIMIT
💡
조회되는 개수를 제한한다.

➡️ LIMIT 숫자;
➡️ LIMIT 0, 100; → 0~100개
```
SELECT
	*
FROM
	city AS c
WHERE
	c.CountryCode = "KOR"
ORDER BY
	c.Population DESC
LIMIT 10;
```

ORDER BY
💡
데이터를 정렬한다.

➡️ select ~ from ~ where 핵심 구문
➡️ 부가적으로 출력 결과에 대해 중복 제거, 출력 개수 제한, 데이터 정렬이 가능하다.
➡️ 기본값 : 오름차순
➡️ 내림 차순 : DESC(Descending)
➡️ 위치 : select ~ from 다음에 order by 위치
```
SELECT
	c.Nams AS '나라이름',
	c.Population AS '인구수'
FROM
	city AS c
WHERE
	c.CountryCode = "USA"
	AND
	c.Population > 2000000
ORDER BY
	c.Population DESC;
```

정렬 랜덤
💡
아무 조건 없이 ORDER BY를 사용하면 PK 순서대로 정렬한다.

➡️ RAND() 함수를 사용하여 랜덤 출력 가능

➡️ 정렬 기준은 여러 개 가능하다.
ORDER BY addr, name; → 주소 다음 이름으로 정렬

GROUP BY와 Aggregate function(집계함수)
➡️ 기본 구문
👉🏻
SELECT
컬럼명
FROM
테이블명
WHERE
조건식
GROUP BY
그룹 지을 컬럼명
HAVING
조건식
ORDER BY
정렬 컬럼명
LIMIT 숫자;
- 그룹으로 묶어주는 역할
- 속성 값이 같은 값끼리 그룹을 만들 수 있다.
- HAVING으로 그룹 출력 조건식을 작성할 수 있다.
- group by 사용했으면, 그룹 대상 컬럼을 select에 조회해줘야한다.
- group by의 결과에 대한 조건을 제한하는 HAVING
💡
full group by 에러 수정
1️⃣ root 접속
2️⃣ 쿼리문 실행 후 종료
SET GLOBAL sql_mode=(
SELECT REPLACE(@@sql_mode,’ONLY_FULL_GROUP_BY’,’’)
);

집계 함수

💡

종류

➡️ SUM : 합계

➡️ AVG : 평균

➡️ MIN(), MAX() : 최솟값, 최댓값

➡️ COUNT() : 행의 갯수

-- 합계
SELECT c.CountryCode, SUM(c.Population)
FROM city AS c
WHERE c.CountryCode = "KOR"
GROUP BY c.CountryCode;

-- 최소, 최대
SELECT
	MIN(c.Population),
	MAX(c.Population)
FROM
	city AS c;

-- count (테이블 행의 개수)
SELECT
	COUNT(*)
FROM
	city AS c
WHERE
	c.CountryCode = "KOR";

-- 특정 컬럼의 개수를 세고 싶을 때
SELECT
	COUNT(c.Name)
FROM
	city AS c
WHERE
	c.CountryCode = "KOR";

count에 특정 컬럼명을 작성하면 null 제외한다. count(*)은 null 포함

count와 distinct 함께 사용. (SELECT COUNT(DISTINCT 컬럼명)

CASE ~ WHEN ~ THEN ~ ELSE END
💡
WHEN과 THEN은 한 쌍이다.

WHEN과 THEN은 여러 개 존재할 수 있다.

ELSE를 사용하여 조건에 맞지 않을 때, 출력할 값을 작성할 수 있다.
```
SELECT
	컬럼명
CASE
	WHEN 조건1 THEN 조건1만족시 출력
	WHEN 조건2 THEN 조건2만족시 출력
	ELSE 조건에 만족하지 않을 때 출력
	END AS 컬럼명
FROM
	테이블명;
```

tag : #데이터베이스 #DB #구축 #SQL #데이터언어 #DDL #DML #DCL

Uploaded by N2T

[DB] 데이터베이스 구성, 제약조건, 테이블 JOIN (0)	2023.05.28
[DB] 데이터 모델링/타입, SQL 변수/형 변환/함수 (0)	2023.05.20
[DB] JDBC 프로그램 순서 7단계 (0)	2023.04.13
[DB] 데이터 언어(DDL, DML, DCL) + TCL (0)	2023.04.13
[DB] DBMS 구축, DB Table 생성 및 활용 (0)	2023.04.13

멋쟁이v의 개발일지