MariaDB, MySql , H2, PostgreSQL

[ParkYK]

MariaDB는 MySQL기반으로 만들어졌기 때문(fork한 서비스)에 쿼리를 비롯한 전반적인 사용법은 MySQL과 유사하다. 

비슷한 사용법 외에도 MariaDB는 MySQL 대비 더 좋은 장점이 있다. 

 - MariaDB 개발이 좀 더 개방적이고 활발한 커뮤니티를 지님

 - 빠르고 투명한 보안패치 릴리즈

 - 다양한 스토리지 엔진

 - 더 나은 성능 및 기능

 - 호환성과 쉬운 마이그레이션

https://mariadb.com/kb/ko/mariadb-mysql/   <- 한글 메뉴얼

https://mariadb.com/kb/ko/

설치 프로그램 다운로드

http://downloads.mariadb.org

****** MariaDb ******  https://mariadb.org/

MariaDb Driver file download 

https://mariadb.com/kb/en/about-the-mariadb-java-client/

MariaDB.pdf

1.35MB

정규화 : https://gomcine.tistory.com/entry/Database-9-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EC%A0%95%EA%B7%9C%ED%99%94-%EA%B0%9C%EB%85%90-%EB%B0%8F-%EB%B0%A9%EB%B2%95

실습용 테이블 자료   

https://github.com/pykwon/etc/blob/master/sample_table_MariaDbl.txt

MariaDB Data Type -----------------------------------------------------------------------------------------------------------------------

- 문자형 (String Type)

  CHAR(n) : 고정길이 데이터 타입 (최대 255byte) - 지정된 길이보다 짧은 데이터 입력시 나머지 공간이 공백(Null)으로 채워짐
  VARCHAR(n) : 가변길이 데이터 타입(최대 65535byte) - 지정된 길이보다 짧은 데이터 입력시 나머지 공간 채우지 않는다
  NVARCHAR : 가변 유니코드 문자열, 모든 문자를 2byte로 저장
  TINYTEXT(n) : 문자열 데이터 타입(최대 255byte )
  TEXT(n) : 문자열 데이터 타입(최대 65535byte)
  MEDIUMTEXT(n) : 문자열 데이터 타입(최대 16777215byte)
  LONGTEXT(n) : 문자열데이터 타입(최대 4294967295byte)
  BINARY(n) & BYTE(n) : char 형태의 이진 데이터 타입(최대 255byte)
  LONGBLOB(n) : 이진 데이터 타입(최대 4294967295byte)
  MEDIUMBLOB(n) : 이진 데이터 타입(최대 16777215byte)
  BLOB(n) : 이진 데이터 타입(최대 65535byte)
  TINYBLOB(n) : 이진 데이터 타입 (최대 255byte)
  VARBINARY(n) : varchar 형태의 이진 데이터 타입 (최대 65536byte)
  ENUM : 문자 형태인 value를 숫자로 저장 value 중에 하나만 저장하며 value가 255 이하인 경우에는 1byte 사용, 65535 이하인 경우에는 2byte 사용
  SET : 목록에서 선택되어야 하는 문자열을 0개 이상 가질 수 있는 객체, 최대64개의 중복되지 않는 문자열을 가질 수 있다.

숫자형 (Numeric Type)

- 정수형 (Integer Type)

Type	Storage (Bytes)	Minimum Value Signed	Minimum Value Unsigned	Maximum Value Signed	Maximum Value Unsigned
TINYINT	1	-128	0	127	255
SMALLINT	2	-32768	0	32767	65535
MEDIUMINT	3	-8388608	0	8388607	16777215
INT	4	-2147483648	0	2147483647	4294967295
BIGINT	8	-263	0	263-1	264-1

고정길이 소수형(Fixed-Point Type)

  DECIMAL (길이,소수) : 고정 소수형 데이터 타입 (길이+1byte) - 소수점을 사용형태
  ex) DECIMAL(5,2) 이면 5자리의 숫자중에 소수점밑에 2자리 = 고정 소수형의 XXX.XX 형태로 나타내며 이 경우 -999.99~999.99 범위를 가진다.

부동 소수형(Floating-Point Type) 근사값을 나타내는 데이터 타입, 표현은 고정길이 소수형과 동일

   FLOAT (길이,소수) : 부동 소수형 데이터 타입 (4byte for single-precision value)
   DOUBLE (길이,소수) : 부동 소수형 데이터 타입( 8byte for double precision value ) 

날짜형(Date and Time Type)

   DATE : 날짜(y,m,d) 형태의 기간 표현 데이터 타입(3byte)
   TIME : 시간(h,m,s) 형태의 기간 표현 데이터 타입(3byte)
   DATETIME : 날짜와 시간 형태의(date+time) 기간 표현 데이터 타입(8byte)
   TIMESTAMP : 날짜와 시간 형태의 기간 표현 데이터 타입(4byte) - 시스템 변경시 자동으로 그 날짜와 시간이 저장된다
   YEAR: 년도 표현 데이터 타입(1byte)

------------- ------------- ------------- -------------

참고 : DATETIME과 TIMESTAMP는 둘 다 날짜 + 시간을 저장하지만, 가장 큰 차이는 시간대(Time Zone) 처리 방식이다.
구분                                     DATETIME                     TIMESTAMP                        
저장 내용                             입력한 날짜/시간 자체      특정 시점을 기준으로 저장
Time Zone 영향                  거의 없음                         세션 Time Zone에 따라 변환
저장 가능 범위                     매우 넓음                         상대적으로 좁음, 버전에 따라 차이
대표 용도                             생일, 예약일, 일정           생성시간, 수정시간, 로그시간
CURRENT_TIMESTAMP   사용 가능                        매우 흔하게 사용

DATETIME은 날짜와 시간 값을 그대로 표현하는 데 적합하고, TIMESTAMP는 실제 사건이 발생한 시점을 기록하고 Time Zone을 고려해야 할 때 적합하다.
초급 단계에서는 “예약/일정 → DATETIME,   생성일/수정일 → TIMESTAMP”로 기억해도 좋다.

---------------------------------------------------------------------------------------------------------------------------------------------

참고 : 샘플 database 설치 - 테스트용 샘플 데이터베이스(employees) 적재하기

https://dbwriter.io/mysql-sample-dataset/

자바 연동 실습용 드라이버 파일

mariadb-java-client-3.1.0.jar

621.53KB

드라이버 파일은 https://search.maven.org/ 에서 search ==> mariadb-java-client 하면 .jar 파일을 다운로드 할 수 있다.

또는 https://mvnrepository.com/

MySQL과 Oracle도 방법은 같다.

maven : 

  <dependency>
          <groupId>org.mariadb.jdbc</groupId>
          <artifactId>mariadb-java-client</artifactId>
          <version>3.1.1</version>
       </dependency>

자바에서 드라이버 로딩 :

Class.forName("org.mariadb.jdbc.Driver");        // MariaDB 드라이버 이용
String url="jdbc:mariadb://localhost:port번호/DB명";
conn=DriverManager.getConnection(url,username,password);

MariaDB 실행...
# mariadb -uroot -p database명   예) DB 접속
Enter password: 암호입력

MariaDB와 연동 관련 https://reddb.tistory.com/category/MariaDB

*** HeidiSQL ***

무료 DB 관리 툴인데 성능이 좋다. 모델링 기능은 없다. MySQL, MSSQL 대상으로 되어 있지만 MariaDB도 사용 가능.

http://www.heidisql.com/download.php

네트워크 유형은 MySQL로 놔두고 접속하면 된다.

멀티 DB 관리툴 - dbeaver 설치 및 간단 사용방법  https://wylee-developer.tistory.com/39

참고 :

****** MySql ******   http://www.mysql.com/

MySql Driver file download 

 http://dev.mysql.com/downloads/connector/j/#downloads

maven : 

<dependency>

  <groupId>mysql</groupId>

  <artifactId>mysql-connector-java</artifactId>

  <version>8.0.27</version>

</dependency>

자바에서 드라이버 로딩 :

Class.forName("com.mysql.jdbc.Driver");      // Mysql 드라이버 이용
String url="jdbc:mysql://서버명:3306/DB명";
conn=DriverManager.getConnection(url,username,password);

MySql 실행...
# mysql -u root -p database명   예) test 또는 mysql 등 접속하고 픈 DB를 적음
Enter password: 암호입력

mysql>use test

mysql>status           <-- 현재 상태 확인

참고 :

하나 : 한글 자료 입력이 안될 때 create table sangdata(code int primary key,sang varchar(20)) charset=utf8; 해서 테이블을 만든 후 로그아웃하고, 다시 로그인 한 후 insert 하면 된다. 참고 : [MariaDB] DB 캐릭터셋을 utf-8으로 설정하기 

둘 : create table하면서 charset=utf8 을 적자 않아 한글이 깨진 경우 table의 구조를 변경하면된다.

      ALTER TABLE sangdata CONVERT TO CHARSET UTF8;

https://slobell.com/blogs/38

[MariaDB] UTF-8 캐릭터(Character) 설정 변경 방법 (tistory.com)

Mysql/Mariadb DB 백업 및 복원

-- mysql DB 백업하기

# mysqldump -uroot -ppass DBname > backup.sql

  1.root 에 db계정명을 적는다.

  2.pass 부분에 mysql 비밀번호를 적는다. 비밀번호를 적지 않고 넘어가면 mysqldump 명령어 수행시 비밀번호를 물어봄.

  3.dbname 부분에 db명을 적는다,

  4.backup 부분에 백업할 파일명을 적는다.

-- mysql 전체 DB 백업

# mysqldump -uroot -p -A > backup.sql

혹은

# mysqldump -uroot -p --all-databases > backup.sql

-- mysql 특정 db의 특정 테이블만 백업

* dump 데이터베이스의 test 테이블만 백업한다.

# mysqldump -uroot -p dump test > dumptest.sql

-- mysql schema 정보만 백업

# mysqldump -uroot -p --no-data schemainfo > schemainfo.sql

-- 모든 db 복원

# mysql -uroot -p < backup.sql

** DB / H2 데이터베이스 (H2 Database) 생성 및 사용법

https://m.blog.naver.com/hj_kim97/222619660259

https://growing-nyang.tistory.com/88

PostgreSQL이란? https://cloud.google.com/discover/what-is-postgresql?hl=ko

Postgresql 설치및 간단한 사용방법 https://tommypagy.tistory.com/617

Windows OS 환경에서 PostgreSQL 설치하기  https://dev-hyonie.tistory.com/24

   - Download the installer 클릭
   - EDB 다운로드 페이지로 이동
   - 운영체제는 Windows x86-64 선택
   - PostgreSQL 버전은 보통 최신 안정 버전 선택
   - 현재 화면 기준이면 교육용으로는 PostgreSQL 18 또는 안정적으로는 17/16도 가능
   - 다운로드된 .exe 파일 실행
     설치할 때는 보통 아래 항목을 체크하면 됩니다.
       PostgreSQL Server, pgAdmin 4, Command Line Tools
     설치화면 마지막Stack Builder는 필수는 아닙니다. 체크 해제~~~

PostgreSQL설명용문서.docx

26.69KB

PostgreSQL.txt

57.63KB

PostgreSQL은 안정성과 확장성이 강한 오픈소스 관계형 데이터베이스다.

데이터 분석 입장에서 PostgreSQL 장단점
장점
   SQL 분석에 강함
   GROUP BY, JOIN, 서브쿼리, 윈도우 함수를 잘 지원해서 집계·순위·누적합·비율 계산에 좋음.
   대용량 데이터 처리에 안정적
   트랜잭션과 데이터 정합성이 강해서 분석용 원천 데이터를 안정적으로 관리 가능.
   JSON 데이터 분석 가능
   JSONB 타입을 지원해서 반정형 데이터도 저장하고 조회 가능.
   Python, R, BI 도구와 연동이 좋음
   Pandas, SQLAlchemy, Jupyter, Tableau, Power BI 등과 연결하기 쉬움.
   확장 기능이 풍부함
   PostGIS를 쓰면 위치·지도 데이터 분석이 가능하고, 다양한 확장 기능을 활용할 수 있음.
단점
   초기 문법이 조금 엄격함
   MySQL/MariaDB보다 GROUP BY, 자료형, 함수 사용이 엄격해서 처음에는 오류가 자주 날 수 있음.
   초대형 분석 전용 DB는 아님
   빅데이터 분석 전용 시스템인 BigQuery, Redshift, Snowflake, Spark보다는 분산 분석 성능이 제한.
   튜닝 지식이 필요함
   데이터가 많아지면 인덱스, 실행 계획, 파티셔닝, VACUUM 같은 관리 개념을 알아야 함.
   Excel처럼 즉석 분석하기엔 불편함
   SQL 기반이라 비전공자나 초급자에게는 처음 접근이 어렵게 느껴질 수 있음.

데이터 분석 입장에서 PostgreSQL은 정형 데이터 분석, SQL 실습, 업무용 리포트, Python 연동 분석에 매우 좋은 DBMS이다.

PostgreSQL이 실제로 하는 일

- 데이터 저장, 테이블 구조로 데이터 저장, 기본키, 외래키로 데이터 간 관계 관리, 데이터 조회

- 표준 SQL 사용, 복잡한 조인, 서브쿼리, 집계 쿼리 처리에 강함

- 데이터 보호, 트랜잭션 지원(한 작업이 전부 성공하거나 전부 실패하도록 보장)

- 장애 발생 시 데이터 일관성 유지

- 동시성 처리 (여러 사용자가 동시에 접근해도 데이터 충돌 최소화, 읽기와 쓰기를 효율적으로 분리 처리)

PostgreSQL이 강한 이유

- ACID 트랜잭션을 매우 엄격하게 준수

- 대용량 데이터에서도 안정적으로 동작

- 인덱스, 쿼리 최적화 기능이 뛰어남

- 확장 기능이 풍부함

- 확장 기능의 예

: JSON과 JSONB 지원(관계형 + 문서형 데이터 혼합 사용 가능)

: 공간 데이터(지도, 좌표, 거리 계산 가능)

: 사용자 정의 함수(SQL, Python 등으로 로직 확장 가능)

MariaDB(MySQL)와 비교할 경우에 핵심 차이

  : MySQL은 빠르고 단순한 웹 서비스에 적합

  : PostgreSQL은 정확성, 복잡한 쿼리, 대규모 데이터 분석에 더 강함

  : 현업에서는 단순 서비스는 MySQL을 금융, 공공, 분석, 백엔드 핵심 DB: PostgreSQL을 많이 사용

한 마디로 PostgreSQL은 데이터의 정확성과 안정성을 최우선으로 설계된 고급 오픈소스 관계형 데이터베이스다.

* MariaDB vs PostgreSQL Data Type 비교표 *
구  분	MariaDB	PostgreSQL	비고 / 설명
고정 문자열	CHAR(n)	CHAR(n)	동일
가변 문자열	VARCHAR(n)	VARCHAR(n)	동일
유니코드 문자열	NVARCHAR	없음 (기본 UTF-8)	PostgreSQL은 기본 UTF-8
TEXT 계열	TINYTEXT / TEXT / MEDIUMTEXT / LONGTEXT	TEXT	PostgreSQL은 TEXT 하나 (최대 1GB)
이진 데이터	BINARY / VARBINARY	BYTEA	PostgreSQL은 BYTEA 사용
BLOB 계열	TINYBLOB / BLOB / MEDIUMBLOB / LONGBLOB	BYTEA	PostgreSQL은 BYTEA 하나
ENUM	ENUM('a','b')	CREATE TYPE … AS ENUM	PostgreSQL은 별도 타입 생성
SET	SET('a','b')	없음	배열(TEXT[]) 또는 JSONB 사용
TINYINT	1byte	SMALLINT	PostgreSQL에 TINYINT 없음
SMALLINT	SMALLINT	SMALLINT	동일
MEDIUMINT	3byte	INTEGER	PostgreSQL에 MEDIUMINT 없음
INT	INT	INTEGER	동일
BIGINT	BIGINT	BIGINT	동일
UNSIGNED	지원	없음	PostgreSQL은 signed만 지원
AUTO_INCREMENT	AUTO_INCREMENT	GENERATED AS IDENTITY / SERIAL	방식 다름
고정 소수	DECIMAL(p,s)	NUMERIC(p,s) / DECIMAL	동일 개념
FLOAT	FLOAT	REAL	4byte
DOUBLE	DOUBLE	DOUBLE PRECISION	8byte
DATE	DATE	DATE	동일
TIME	TIME	TIME	동일
DATETIME	DATETIME	TIMESTAMP	PostgreSQL에 DATETIME 없음
TIMESTAMP	TIMESTAMP	TIMESTAMP / TIMESTAMP WITH TIME ZONE	PostgreSQL은 timezone 중요
YEAR	YEAR	없음	SMALLINT 또는 DATE 사용
JSON	JSON	JSON / JSONB	PostgreSQL은 JSONB 강력
ARRAY	없음	INT[], TEXT[] 등	PostgreSQL 고유 기능
UUID	없음(확장 필요)	UUID	PostgreSQL 기본 제공
BOOLEAN	TINYINT(1)	BOOLEAN	PostgreSQL은 진짜 Boolean

PostgreSQL은 원래 관계형 데이터베이스지만, pgvector를 추가하면 Chroma, FAISS, Pinecone처럼 벡터 검색 용도로도 사용할 수 있다.
간단한 예 : 
 CREATE EXTENSION vector;
 CREATE TABLE documents ( id SERIAL PRIMARY KEY, content TEXT, embedding VECTOR(3) );
 INSERT INTO documents(content, embedding) VALUES ('PostgreSQL 설명', '[0.1, 0.2, 0.3]'), ('데이터 분석', '[0.2, 0.1, 0.4]');

유사한 문서를 찾을 때는 다음처럼 검색할 수 있습니다.
SELECT content FROM documents ORDER BY embedding <-> '[0.1, 0.2, 0.25]' LIMIT 3;
여기서 <->는 벡터 간 거리를 기준으로 가까운 데이터를 찾는 연산자입니다.

장점
PostgreSQL을 벡터 DB로 쓰면 좋은 점은 기존 정형 데이터와 벡터 데이터를 한 DB에서 같이 관리할 수 있다는 점입니다. 
예를 들어 문서 제목, 작성일, 카테고리, 작성자 같은 일반 컬럼과 embedding 벡터를 함께 저장하고 SQL 조건으로 필터링할 수 있습니다. LangChain도 PostgreSQL과 pgvector를 사용하는 PGVector 벡터스토어 연동을 제공합니다.

SELECT title, content FROM documents WHERE category = 'DB'
ORDER BY embedding <-> '[0.1, 0.2, 0.25]' LIMIT 5;

인덱스도 지원 : 데이터가 많아지면 벡터 검색 속도를 높이기 위해 인덱스를 사용할 수 있습니다.
대표적으로 다음이 있습니다.
  HNSW
  IVFFlat
pgvector는 HNSW와 IVFFlat 같은 벡터 검색용 인덱스를 지원하며, IVFFlat은 벡터를 여러 리스트로 나누어 가까운 후보군을 빠르게 찾는 방식입니다.
예: CREATE INDEX ON documents USING hnsw (embedding vector_l2_ops); 

단점도 있음. 다만 PostgreSQL + pgvector가 항상 전문 벡터 DB를 대체하는 것은 아닙니다.
대규모 벡터 검색, 초고속 실시간 검색, 분산 처리, 수십억 건 규모 검색이 필요하다면 Milvus, Weaviate, Pinecone, Qdrant 같은 전문 벡터 DB가 더 적합할 수 있습니다.

PostgreSQL은 pgvector 확장을 사용하면 벡터 데이터베이스처럼 사용할 수 있으며, RAG, 시맨틱 검색, 추천 시스템 실습에 충분히 활용할 수 있습니다. 특히 기존 SQL 데이터와 임베딩 벡터를 함께 관리할 수 있다는 점이 가장 큰 장점입니다.

** pgAdmin 4 사용법 **

1. 새 Database 만들기
왼쪽 메뉴에서 다음 순서로 클릭하세요.
Servers
 └─ PostgreSQL 18
     └─ Databases

Databases에서 마우스 오른쪽 클릭합니다.  Databases 우클릭 → Create → Database...   그러면 창이 뜹니다.
입력 예 ==> 
Database name: testdb
Owner: postgres
입력 후 Save를 클릭합니다.  그러면 왼쪽에 testdb 데이터베이스가 새로 생깁니다.

2. 만든 Database 선택하기. 왼쪽에서 방금 만든 데이터베이스를 클릭합니다.
Databases
 └─ testdb
반드시 SQL을 실행할 데이터베이스를 선택한 상태여야 합니다.

3. SQL 입력 창 열기
testdb를 클릭한 상태에서 위쪽 메뉴를 누릅니다.
Tools → Query Tool
또는 상단에 있는 Query Tool 아이콘을 눌러도 됩니다. 그러면 오른쪽에 SQL을 입력할 수 있는 창이 열립니다.

4. SQL문으로 테이블 만들기
Query Tool에 아래 SQL을 입력해 보세요.
CREATE TABLE dept ( no INT PRIMARY KEY, name VARCHAR(10), tel VARCHAR(15), inwon INT, addr TEXT );
그다음 상단의 ▶ 실행 버튼을 누릅니다. 단축키는 보통 F5 입니다.

5. 데이터 입력하기
INSERT INTO dept(no, name, tel, inwon, addr) VALUES (1, '인사과', '111-1111', 3, '삼성동12');
INSERT INTO dept(no, name, tel, inwon, addr) VALUES (2, '영업과', '111-2222', 5, '서초동12');
실행은 역시 ▶ 버튼 또는 F5입니다.

6. 데이터 조회하기
SELECT * FROM dept;
아래쪽 결과창에 데이터가 보이면 성공입니다.

7. 만든 테이블 확인하기
왼쪽 메뉴에서 다음 경로를 펼치면 됩니다.
Databases
 └─ testdb
     └─ Schemas
         └─ public
             └─ Tables

Tables에서 우클릭 후 Refresh를 누르면 dept 테이블이 보입니다.

기본 화면/구성 먼저 이해하기
  왼쪽 : Object Explorer(트리)
  서버 → Databases → (DB) → Schemas → Tables … 이런 구조

오른쪽 상단 탭: Dashboard / Properties / SQL …
어떤 오브젝트를 클릭하면 속성/SQL이 여기 뜸
데이터를 직접 넣거나 조회하려면 보통 Query Tool(SQL 편집기)을 많이 쓴다.

1. 서버 연결 확인 (처음 1회)
 - 왼쪽 트리에서 Servers (1) → PostgreSQL 18 를 펼친다.
 - 만약 아이콘에 X 표시가 있거나 접속이 안 된 상태면
      PostgreSQL 18을 더블클릭하거나 우클릭 → Connect Server
 - 비밀번호를 묻는 창이 뜨면 입력(필요하면 “Save password” 체크) 
 - 정상 연결되면 Databases (1) 같은 하위 항목들이 잘 펼쳐진다.

2. 데이터베이스(Database) 만들기
 - 현재 화면에 Databases (1) 아래에 postgres DB가 보이죠. 여기에 새 DB를 만들면 된다.
 - 왼쪽 트리에서  PostgreSQL 18 → Databases 를 우클릭
 - Create → Database… 클릭
 - 뜨는 창에서:  
    Database: 예) mydb
    Owner: 보통 기본 계정(예: postgres) 선택
    Save 클릭 →  만들어진 DB가 트리에 보이면 성공이다.

3. (중요) 스키마(Schema) 개념
 PostgreSQL은 보통 “DB 안에 스키마, 스키마 안에 테이블” 구조를 쓴다. 기본 스키마가 public 이다. 처음엔 그냥 public에 테이블 만들면 된다.

4. 테이블 만들기 (GUI로)
 - 왼쪽 트리에서 새로 만든 DB를 펼친다.   예: Databases → mydb
 - Schemas 펼치기 → public 펼치기
 - Tables 를 우클릭 → Create → Table… 클릭
 - 4-1) 테이블 이름 지정 : 탭/섹션에서 General:  Name: 예) posts
 - 4-2) 컬럼(Columns) 추가 : 왼쪽(또는 상단)에서 Columns 탭 선택
         오른쪽에 + (Add) 버튼(또는 “Add”)을 눌러 컬럼을 하나씩 추가

예시로 게시글 테이블을 만들어 본다(많이 쓰는 구성):
 - id 컬럼 - Name: id   Data type: integer   체크: Not NULL  그리고 “Primary key”는 아래 Constraints에서 하거나, GUI가 제공하면 해당 옵션 체크
 - title 컬럼 - Name: title   Data type: text (또는 character varying)   →   Not NULL 체크
 - content 컬럼 -  Name: content    Data type: text  →   Not NULL 체크
 - created_at 컬럼 - Name: created_at   Data type: timestamp with time zone (또는 timestamp)   Default: now() 를 주고 싶으면 Default 칸에 now() 입력

* PK(기본키)와 자동증가(Identity) 설정 (초보가 자주 막히는 부분)
  PostgreSQL은 MariaDB의 AUTO_INCREMENT에 해당하는 걸 보통 Identity로 쓴다.
  가장 쉬운 방법(권장): id를 Generated as Identity로 설정
    - gAdmin 테이블 생성 화면에서 id 컬럼 편집을 열면 “Identity” 관련 옵션이 보이는 경우가 많다.
    - Identity: Generated by default as identity (또는 Always)
    그리고 PK 지정
    만약 생성 화면에서 PK/Identity 옵션이 헷갈리면 일단 컬럼만 만들고 Save 한 뒤, 테이블 우클릭 → Properties → Constraints 쪽에서 PK를 추가해도 된다.
    마지막으로 Save 클릭 → 테이블 생성 완료.

5. 데이터 입력 방법 2가지 (초보는 1번이 쉬움)
 - 방법 A) GUI로 직접 행 입력 (가장 쉬움)
 - 왼쪽 트리에서 mydb → Schemas → public → Tables → posts 선택
 - posts 를 우클릭
 - View/Edit Data → All Rows 클릭
   아래쪽에 스프레드시트처럼 뜬다. 빈 행에 값 입력. id가 Identity라면 보통 id 칸은 비워도 자동으로 들어감. 
 - 상단에 Save(디스크 아이콘) 눌러 저장

방법 B) SQL로 입력 (실무에서 더 많이 씀)
 - 왼쪽 트리에서 mydb 선택
 - 상단 메뉴에서 Tools → Query Tool 클릭 (또는 DB 우클릭 메뉴에 Query Tool이 보이는 경우도 있음)
   CREATE TABLE public.sangdata (
       code INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       sang VARCHAR(20) NOT NULL,
       su INT,
       dan INT
   );
   code INT GENERATED BY DEFAULT AS IDENTITY   → 자동 증가 컬럼.  

   SERIAL 도 가능 : code SERIAL PRIMARY KEY,

 - 자료 추가는 insert SQL 실행: INSERT INTO public.sangdata (sang, su, dan) VALUES ('사과', 10, 2000);

6. 데이터 조회(확인)
 - Query Tool에서:  SELECT * FROM public.sangdata;
 - 또는 GUI로:  테이블 우클릭 → View/Edit Data → All Rows

7. 자주 헷갈리는 포인트 (MariaDB 사용자 기준)
 - “DB 만들었는데 테이블이 안 보여요”  해당 DB 아래의 Schemas → public → Tables 쪽을 확인 필요.
 - “INSERT 했는데 반영이 안 된 것 같아요”  Query Tool에서 트랜잭션이 걸려 있으면 Commit이 필요할 수 있다.
    (보통 자동 커밋이 켜져 있거나 큰 문제는 없지만, 뭔가 이상하면 Commit 버튼 확인)
 - “id 자동증가가 안 돼요”  id가 Identity로 설정됐는지 확인.

참고 : pgAdmin 4의 Query Tool에서 보이는 ▶ 버튼 두 개는 실행 범위와 방식이 다르다.
1) ▶ : Execute / Refresh (F5)  - Execute the query (선택 영역 또는 현재 문장 실행)
  - 선택한 SQL만 실행
  - 선택이 없으면 커서가 위치한 하나의 문장만 실행
  - 세미콜론(;) 기준으로 한 문장 단위 실행
  - 보통 테스트용으로 가장 많이 사용
  예시
     SELECT * FROM dept;
     SELECT * FROM emp;
     커서를 첫 번째 SELECT 안에 두고 ▶ 실행 → dept만 실행
     두 줄 모두 드래그 후 실행 → 두 개 모두 실행
2) ▶q :  Execute Script (Alt+Shift+F5)  - Execute the entire script
  - 선택 여부와 상관없이 편집기 안의 전체 SQL 스크립트를 순서대로 전부 실행
  - 여러 DDL, DML, 함수 생성 등 일괄 실행할 때 사용

----------------------------------------------------
** SQL Shell (psql) 도구 사용법 **

1) SQL Shell(PSQL)로 “연결”하기 - 처음 뜨는 5개 질문 
  - 화면에 이런 식으로 하나씩 물어본다. [] 안은 기본값이라 그냥 Enter 치면 그 값으로 들어간다.
  - Server [localhost]    :  내 PC에 설치한 PostgreSQL이면 Enter (localhost)
  - Database [postgres] :  처음엔 기본 DB로 들어가도 되니 Enter (postgres)
  - Port [5432]             :  기본 포트면 Enter
  - Username [postgres] : 설치할 때 만든 계정이 postgres면 Enter  다른 계정이면 그 이름 입력
  - Password for user postgres :   설치할 때 지정한 비밀번호 입력 (입력해도 화면에 안 보이는 게 정상)

  성공하면 마지막에 postgres=# 같은 프롬프트가 보인다.  (예: postgres=#)

2) 데이터베이스 목록 보기
  - 프롬프트에서 아래 명령 입력:  \l    또는   \list

    Database 생성 :  CREATE DATABASE mydb;
  - 그러면 DB 목록이 표로 나온다. 거기에 mydb가 있는지 확인한다.

3) mydb로 “접속(전환)”하기
  - DB를 바꾸는 명령은 \c 이다.  \c mydb
  - 성공하면 프롬프트가 mydb=# 처럼 바뀐다.  (이게 “mydb에 접속했다”는 뜻)

4) 테이블 목록 보기
  - PostgreSQL은 보통 public 스키마에 테이블을 만든다.

    Table 생성 : Database에 접속한 상태에서 CREATE TABLE test (id INT PRIMARY KEY, name VARCHAR(50)); 
    (1) 현재 DB의 테이블 목록(주로 public)  \dt     또는  \d
    (2) public 스키마만 정확히 보고 싶으면  \dt public.*
  - 테이블이 하나도 없으면 “Did not find any relations.” 같은 메시지가 나올 수 있다(정상).

5) (추가로 자주 쓰는 것들) 스키마/테이블 구조 보기
  - 스키마 목록  \dn

  - 특정 테이블 구조 보기 \d table    :  (예: test)   \d test 

  - 더 상세히 \d+ table

6) 종료는   \q

참고 : ** root 계정(postgres) 말고 사용자 계정 만들기  ~~~~~~~~~~~~~~~~~~
create user 계정명 with password '비밀번호';
postgres=# create user myuser with password '123ok';
--- db 작성 ---
create database db명 owner 계정명;
postgres=# create database mydb2 owner myuser;
--- 작성된 db에 대해 특정 계정에게 권한 부여 ---
postgres=# grant all privileges on database mydb2 to myuser;
--- 현재 PostgreSQL 서버에 존재하는 사용자(= role) 목록을 보여주는 명령어 ---
postgres=# \du       또는   \du+
postgres=# \q

--- cmd 창에서 PostgreSQL에 myuser 계정으로 접속하기 ---

python과 연동하기 -------------

# pip install psycopg2-binary
import psycopg2

# PostgreSQL 접속 정보
conn = psycopg2.connect(
    host="localhost",      # PostgreSQL 서버 주소
    port=5432,             # PostgreSQL 기본 포트
    database="testdb",     # 사용할 데이터베이스 이름
    user="postgres",       # PostgreSQL 사용자명
    password="1234" 
)

cursor = conn.cursor()
sql = "SELECT no, name, tel, inwon, addr FROM dept"
cursor.execute(sql)
rows = cursor.fetchall()

for row in rows:
    no, name, tel, inwon, addr = row
    print(f"번호: {no}, 부서명: {name}, 전화: {tel}, 인원: {inwon}, 주소: {addr}")

cursor.close()
conn.close()

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

* PostgreSQL + SQLAlchemy ORM으로 sangdata 전체 CRUD 코드
ORM(Object Relational Mapping)은 테이블 ↔ 클래스, 레코드 ↔ 객체  로 매핑해서 SQL을 직접 안 써도 되게 해주는 기술을 말한다.
SQLAlchemy 라이브러리가 ORM 기능 제공

# pip install sqlalchemy psycopg2-binary

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker

# 1. DB 연결 정보
DATABASE_URL = "postgresql://postgres:123@localhost:5432/mydb"

engine = create_engine(DATABASE_URL, echo=True)
SessionLocal = sessionmaker(bind=engine)

Base = declarative_base()

# 2. ORM 모델 정의
class SangData(Base):
    __tablename__ = "sangdata"

    code = Column(Integer, primary_key=True, index=True)
    sang = Column(String(20), nullable=False)
    su = Column(Integer)
    dan = Column(Integer)

    def __repr__(self):
        return f"SangData(code={self.code}, sang='{self.sang}', su={self.su}, dan={self.dan})"

# 3. 테이블 자동 생성
def create_table():
    Base.metadata.create_all(engine)

# 4. INSERT
def insert_data():
    session = SessionLocal()
    data1 = SangData(sang="딸기", su=10, dan=2000)
    data2 = SangData(sang="바나나", su=5, dan=1500)
    session.add_all([data1, data2])
    session.commit()
    session.close()

# 5. SELECT
def select_all():
    session = SessionLocal()
    rows = session.query(SangData).all()

    for row in rows:
        print(row)

    session.close()

# 6. UPDATE
def update_data(code):
    session = SessionLocal()
    item = session.query(SangData).filter(SangData.code == code).first()
    if item:
        item.dan = 3000
        session.commit()
        print("수정 완료")

    session.close()


# 7. DELETE
def delete_data(code):
    session = SessionLocal()
    item = session.query(SangData).filter(SangData.code == code).first()
    if item:
        session.delete(item)
        session.commit()
        print("삭제 완료")

    session.close()


if __name__ == "__main__":
    create_table()      # 테이블 없으면 생성
    insert_data()        # 데이터 추가
    select_all()           # 전체 조회
    update_data(1)    # 1번 수정
    delete_data(2)     # 2번 삭제
    select_all()           # 다시 조회

실행 결과 보기
PS D:\works\pysou\tfex> python a.py
18:46:19,383 INFO sqlalchemy.engine.Engine select pg_catalog.version()
18:46:19,384 INFO sqlalchemy.engine.Engine [raw sql] {}
18:46:19,385 INFO sqlalchemy.engine.Engine select current_schema()
18:46:19,386 INFO sqlalchemy.engine.Engine [raw sql] {}
18:46:19,387 INFO sqlalchemy.engine.Engine show standard_conforming_strings
18:46:19,387 INFO sqlalchemy.engine.Engine [raw sql] {}
18:46:19,389 INFO sqlalchemy.engine.Engine BEGIN (implicit)
18:46:19,397 INFO sqlalchemy.engine.Engine SELECT pg_catalog.pg_class.relname
FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_catalog.pg_class.relnamespace
WHERE pg_catalog.pg_class.relname = %(table_name)s AND pg_catalog.pg_class.relkind = ANY (ARRAY[%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s, %(param_5)s]) AND pg_catalog.pg_table_is_visible(pg_catalog.pg_class.oid) AND pg_catalog.pg_namespace.nspname != %(nspname_1)s
18:46:19,397 INFO sqlalchemy.engine.Engine [generated in 0.00088s] {'table_name': 'sangdata', 'param_1': 'r', 'param_2': 'p', 'param_3': 'f', 'param_4': 'v', 'param_5': 'm', 'nspname_1': 'pg_catalog'}
18:46:19,401 INFO sqlalchemy.engine.Engine COMMIT
18:46:19,404 INFO sqlalchemy.engine.Engine BEGIN (implicit)
18:46:19,407 INFO sqlalchemy.engine.Engine INSERT INTO sangdata (sang, su, dan) SELECT p0::VARCHAR, p1::INTEGER, p2::INTEGER FROM (VALUES (%(sang__0)s, %(su__0)s, %(dan__0)s, 0), (%(sang__1)s, %(su__1)s, %(dan__1)s, 1)) AS imp_sen(p0, p1, p2, sen_counter) ORDER BY sen_counter RETURNING sangdata.code, sangdata.code AS code__1
18:46:19,407 INFO sqlalchemy.engine.Engine [generated in 0.00014s (insertmanyvalues) 1/1 (ordered)] {'dan__0': 2000, 'su__0': 10, 'sang__0': '딸기', 'dan__1': 1500, 'su__1': 5, 'sang__1': '바나나'}
18:46:19,411 INFO sqlalchemy.engine.Engine COMMIT
18:46:19,413 INFO sqlalchemy.engine.Engine BEGIN (implicit)
18:46:19,415 INFO sqlalchemy.engine.Engine SELECT sangdata.code AS sangdata_code, sangdata.sang AS sangdata_sang, sangdata.su AS sangdata_su, sangdata.dan AS sangdata_dan
FROM sangdata
18:46:19,416 INFO sqlalchemy.engine.Engine [generated in 0.00065s] {}
SangData(code=1, sang='사과', su=10, dan=2000)
SangData(code=2, sang='딸기', su=10, dan=2000)
SangData(code=3, sang='바나나', su=5, dan=1500)
18:46:19,420 INFO sqlalchemy.engine.Engine ROLLBACK
18:46:19,423 INFO sqlalchemy.engine.Engine BEGIN (implicit)
18:46:19,425 INFO sqlalchemy.engine.Engine SELECT sangdata.code AS sangdata_code, sangdata.sang AS sangdata_sang, sangdata.su AS sangdata_su, sangdata.dan AS sangdata_dan
FROM sangdata
WHERE sangdata.code = %(code_1)s
 LIMIT %(param_1)s
18:46:19,428 INFO sqlalchemy.engine.Engine [generated in 0.00225s] {'code_1': 1, 'param_1': 1}
18:46:19,431 INFO sqlalchemy.engine.Engine UPDATE sangdata SET dan=%(dan)s WHERE sangdata.code = %(sangdata_code)s
18:46:19,432 INFO sqlalchemy.engine.Engine [generated in 0.00072s] {'dan': 3000, 'sangdata_code': 1}
18:46:19,434 INFO sqlalchemy.engine.Engine COMMIT
수정 완료
18:46:19,437 INFO sqlalchemy.engine.Engine BEGIN (implicit)
18:46:19,438 INFO sqlalchemy.engine.Engine SELECT sangdata.code AS sangdata_code, sangdata.sang AS sangdata_sang, sangdata.su AS sangdata_su, sangdata.dan AS sangdata_dan
FROM sangdata
WHERE sangdata.code = %(code_1)s
 LIMIT %(param_1)s
18:46:19,439 INFO sqlalchemy.engine.Engine [cached since 0.01398s ago] {'code_1': 2, 'param_1': 1}
18:46:19,442 INFO sqlalchemy.engine.Engine DELETE FROM sangdata WHERE sangdata.code = %(code)s
18:46:19,442 INFO sqlalchemy.engine.Engine [generated in 0.00055s] {'code': 2}
18:46:19,444 INFO sqlalchemy.engine.Engine COMMIT
삭제 완료
18:46:19,446 INFO sqlalchemy.engine.Engine BEGIN (implicit)
18:46:19,447 INFO sqlalchemy.engine.Engine SELECT sangdata.code AS sangdata_code, sangdata.sang AS sangdata_sang, sangdata.su AS sangdata_su, sangdata.dan AS sangdata_dan
FROM sangdata
18:46:19,447 INFO sqlalchemy.engine.Engine [cached since 0.03159s ago] {}
SangData(code=3, sang='바나나', su=5, dan=1500)
SangData(code=1, sang='사과', su=10, dan=3000)
18:46:19,449 INFO sqlalchemy.engine.Engine ROLLBACK