게시 됨 업데이트 됨sql1분안에 읽기 (약 207 단어)

postgresql 외부 접속 가능하게 설정하기

상황

  • vercel에 배포한 앱이 db 커넥션이 안되었다
  • aws 프리티어가 끝나서 로컬 컴퓨터에서 postgresql를 돌렸다
  • 공유기 포트 포워딩, 방화벽 인바운드 규칙 설정, 커넥션 스트링도 잘 작성해줬는데 이상하게 안되었다
  • 찾아보니까 postgresql 설정도 건드려야 했다

해결

  • C:\Program Files\PostgreSQL\13\data ; 내 postgresql 설치 경로이다
  • 바로 여기에 pg_hba.conf을 수정해주어서 해결했다

pg_hba.conf:86
1
2
# IPv4 local connections:
host    all             all             0.0.0.0/0               scram-sha-256
  • 86라인 정도에 127.0.0.1/32를 0.0.0.0/0으로 수정해주면 된다
  • 따로 dbms를 재부팅 안 해줘도 바로 적용되었다

참고

 게시 됨 업데이트 됨sql2분안에 읽기 (약 241 단어)

postgresql commands

SQL Shell (psql) 에서 명령
postgreql 처음 써보는 데 사용한 명령어 및 커넥션 설정 정리

psql

psql commands
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
-- 데이터베이스 생성
CREATE DATABASE student;

-- 유저 생성
CREATE USER sung WITH PASSWORD '1234';

-- 권한 부여
GRANT ALL PRIVILEGES ON DATABASE student TO sung;

-- 특정 유저로 데이터베이스 접속
\c student sung

-- 데이터베이스 목록 보기
\l

-- 테이블 목록 보기
\d
\dt

-- 테이블 칼럼 보기
\d "User"

-- select query
-- 릴레이션을 못 찾았다고 하면 테이블명에 따옴표 붙이기
select * from "User";
  • 대소문자 구분은 안 해도 된다

connection

application.properties
1
2
3
4
5
6
7
spring.datasource.url=jdbc:postgresql://localhost:5432/student
spring.datasource.username=sung
spring.datasource.password=1234
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.format_sql=true

jdbc

pgAdmin

  • GUI로 DBMS 다루기

참고

 게시 됨 업데이트 됨sql몇 초안에 읽기 (약 94 단어)

mysql replace 안될 때

  • ON DUPLICATE KEY 를 사용한다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO
    department (
        `departmentId`,
        `companyId`,
        `name`,
        `memo`,
        `parentId`
    )
VALUES
    (?, ?, ?, ?, ?,) ON DUPLICATE KEY
UPDATE
    name = ?,
    memo = ?,
    parentId = ?;

상황

  • 제약 조건이 걸려있는 pk를 사용하였을 때 replace 사용이 불가해진다
  • 이럴 때 ON DUPLICATE KEY를 사용해서 중복된 키일 때 키를 제외한 나머지 칼럼 값들을 변경할 수 있다

참고

 게시 됨 업데이트 됨sql몇 초안에 읽기 (약 32 단어)

mysql datetime칼럼 날짜로 조회하기

1
SELECT * FROM commuteLog where companyId=12 and date(commuteDatetime) = curdate();
  • datetime(YYYY-MM-DD hh:mm:ss)을 date(YYYY-MM-DD) 기준으로 조회하는 방법
 게시 됨 업데이트 됨sql몇 초안에 읽기 (약 3 단어)

mysql 버전 확인

1
2
SELECT
    version()
 게시 됨 업데이트 됨sql몇 초안에 읽기 (약 88 단어)

mysql 현재시간 구하기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 현재시간 구하기
SELECT
    curtime()
-- 07:35:51


-- 시간 값만 가져오기 ; %H ; 00~23
SELECT
    date_format(curtime(), '%H')
-- 07


-- 앞에 0 패딩 없애기 1 ; 숫자를 더해서 캐스팅
SELECT
    date_format(curtime(), '%H') + 0
-- 7


-- 앞에 0 패딩 없애기 2 ; k로 가져오면 0~23
SELECT
    date_format(curtime(), '%k')
-- 7
  • 현재 시간을 얻는 법

참고

 게시 됨 업데이트 됨sql1분안에 읽기 (약 119 단어)

insert, update시 자동갱신 TIMESTAMP

1
2
3
4
5
6
7
8
9
CREATE TABLE LOG
(
    `idx`     INT            NOT NULL    AUTO_INCREMENT,
    `ip`      VARCHAR(45)    NOT NULL,
    `method`  VARCHAR(45)    NOT NULL,
    `url`     VARCHAR(45)    NOT NULL,
    `date`    TIMESTAMP      NOT NULL	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (idx)
);
  • CURRENT_TIMESTAMP은 현재 시각을 의미한다
  • DEFAULT CURRENT_TIMESTAMP ; insert 시 자동으로 현재 시각이 들어간다
  • ON UPDATE CURRENT_TIMESTAMP ; update 시 자동으로 현재 시각으로 갱신된다

이미 만든 칼럼에 적용하기

1
2
3
ALTER TABLE LOG
MODIFY
    `date` datetime DEFAULT CURRENT_TIMESTAMP

참고

 게시 됨 업데이트 됨sql1분안에 읽기 (약 147 단어)

connection vs connection pool

  • connection pool를 써보자

Connection pool

  • 기존 connection은 데이터베이스에 동시 접속 허용량을 초과하는 연결을 할 경우 에러 발생
  • connection pool을 사용하면 대기상태가 되었다가 수행됨
  • pool 에 여러 미리 생성되어 있는 connection을 가져가 쓴다
  • connection이 미리 생성되어있기 때문에 생성시간 소모되지 않음
  • 따라서 속도 빨라짐. 쉽게 다운되지 않음

참고

 게시 됨 업데이트 됨sql1분안에 읽기 (약 136 단어)

게시판 페이지네이션 SQL

sql
1
2
3
4
5
6
const page = 1;

const query = `SELECT * FROM notice WHERE use_flag='Y' ORDER BY idx DESC LIMIT ?,?`;
const queryArgs = [10 * (page - 1), 10];

return client.query(query, queryArgs);
  • notice 테이블에서 page값에 따라 게시글 10개를 가져오는 쿼리이다
  • limit 을 이용해서 구현하였다

SELECT * FROM notice LIMIT 0,10
// 0번째부터 10개를 가져온다는 의미이다

SELECT * FROM notice LIMIT 20,10
// 20번째부터 10개를 가져온다는 의미이다

참고

카테고리

아카이브

광고

×