Part I. Tutorial
목차
1. 시작하기
1.1. 설치
1.2. 아키텍 기초
1.3. 데이터베이스 생성
1.4. 데이터베이스에 액세스
2. SQL 언어
2.1. 소개
2.2. 개념
2.3. 새 테이블 만들기
2.4. 행으로 테이블 채우기
2.5. 테이블 쿼리
2.6. 테이블 간 조인
2.7. 집계 함수
2.8. 업데이트
2.9. 삭제
3. 고급 기능
3.1. 소개
3.2. 뷰
3.3. 외래 키
3.4. 트랜잭션
3.5. Window 함수
3.6. 계층
3.7. 결론
1. 시작하기
1.1. 아키텍처
PostgreSQL은 클라이언트/서버 모델을 사용합니다. PostgreSQL 세션 은 다음과 같은 프로세스(프로그램)로 구성됩니다.
- 데이터베이스 서버 프로그램 : 데이터베이스 파일을 관리하는 서버 프로세스는 클라이언트의 연결을 수락하고 데이터베이스 작업을 수행합니다.
- 데이터베이스 작업을 수행하려는 사용자의 클라이언트(프런트엔드) 애플리케이션 : 클라이언트는 텍스트 도구, 그래픽 애플리케이션, 웹 페이지를 표시하기 위해 데이터베이스에 액세스하는 웹 서버 또는 데이터베이스 유지.관리 도구입니다.
일반적인 클라이언트/서버 응용 프로그램과 마찬가지로 클라이언트와 서버는 TCP/IP 네트워크 연결을 통해 통신합니다.
PostgreSQL 서버 는 클라이언트의 여러 동시 연결을 처리할 수 있습니다. 이를 위해 각 연결에 대해 새로운 프로세스를 시작( "fork" )합니다. 그 시점부터 클라이언트와 fork된 서버 프로세스는 원래 postgres프로세스의 개입 없이 통신합니다.
1.2. 설치
https://www.postgresql.org/download/ 에서 설치 환경(OS, Windows, MacOS, Linux, Solaris 등) 에 맞는 설치파일을 다운로드 한 후 설치한다.
윈도우에 설치 시 아래 4개 컴포넌트가 설치된다.
- PostgreSQL Server : PostgreSQL Database Server
- pgAdmin : PostgreSQL database 관리와 작업을 위한 GUI 툴
- Stack Builder : 추가 툴이나 driver 그리고 Application 등을 다운로드를 위한
- Command Line Tools : DB접속을 위한 Cli 툴
윈도우에 설치 시 superuser(posgres)에 대한 패스워드, DB Port, Locale 입력화면이 나오면 원하는 값 입력한다.
Linux나 MacOS 에 설치는 https://www.postgresql.org/download 페이지를 참고한다.
1.3. DB 접속
1) psql 로 접속
Database가 설치된 서버에서 접속 시에는 host를 생략할 수 있고, default port를 사용하는 경우에는 port 생략 가능
$ psql -h {host} -p {port} -U {user} -d {database}2) SQL Shell을 이용한 접속
Windows 에 설치된 "SQL Shell(psql)" 을 실행시킨 후 DB 접속을 위한 정보를 차례로 입력한다.
3) client 툴로 접속
클라이언트 툴(TablePlus, Psequel, PgAdmin, Postico, Azure Data Studio, DBeaver, Command Line Tool 등)을 이용하여 DB에 접속한다. 본 문서에서는 TablePlus로 접속하는 방법을 설명한다.
- TablePlus 실행 화면에서 마우스 오른쪽 버튼을 클릭한 다음 "[New] - PostgreSQL" 선택한다
- 아래와 같이 DB 접속을 위한 화면에 DB 정보를 입력한다.
- Name : 임의의 별칭(Alias) 입력
- Host : DB서버의 IP나 hostname, endpoint 등을 입력. 본 문서에서는 PC에 설치했으므로 localhost 입력
- Port : 설치 시 입력했던 Port 입력(Default Port는 5432)
- User : postgress 입력
- Password : 설치 시 입력했던 password 입력
- Database : 기본적으로 postgres database 가 설치되므로 postgresql을 입력해도 되고 공란으로 유지해 된다.
입력이 완료되면 [Test] 를 수행한 후 정상 접속이 확인되면 [Save] 버튼을 클릭하여 저장한다.
4) java, python 등의 프로그램으로 접속
1.3. 데이터베이스 생성
mydb 데이터베이스 생성:
CREATE DATABASE mydb;
salesspace 테이블스페이스를 사용하여 사용자 salesapp이 소유한 데이터베이스 sales 생성
CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
1.4. 도메인 생성
postgreSQL의 자료형에는 Domain Types이 있습니.
도메인은 선택적 제약 조건(허용되는 값 집합에 대한 제한 사항)이 있는 데이터 유형입니다. 도메인을 정의하는 사용자가 도메인의 소유자가 됩니다.
DOMAIN 생성 문법
CREATE DOMAIN name [ AS ] data_type
[ COLLATE collation ]
[ DEFAULT expression ]
[ constraint [ ... ] ]
where constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }- name: 사용자 정의 데이터 유형의 이름
- data_type: 데이터 유형
- COLLATE : 도메인에 대한 데이터 정렬입니다. 데이터 정렬이 지정되지 않은 경우 도메인은 기본 데이터 유형과 동일한 데이터 정렬 동작을 합니다.
- DEFAULT default_value: 기본값 (선택 사항)
- CONSTRAINT constraint_name: 제약 조건의 이름 (선택 사항)
- NOT NULL: NULL 값을 허용하지 않음 (선택 사항)
- CHECK (expression): 값 유효성 검사를 위한 표현식 (선택 사항)
Domain Examples
이 예에서는 us_postal_code 데이터 유형을 생성한 다음 테이블 정의에 해당 유형을 사용합니다. 정규식 테스트는 값이 유효한 미국 우편번호인지 확인하는 데 사용됩니다. 아래 도메인 제약은 5자리 정수 입력 허용과 5자리-4자리 정수 입력만을 허용하는 규칙입니다.
CREATE DOMAIN us_postal_code AS TEXT
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);
CREATE TABLE us_snail_addy (
address_id SERIAL PRIMARY KEY,
city TEXT NOT NULL,
postal us_postal_code NOT NULL
);
INSERT INTO us_snail_addy(city, postal) VALUES('LOS ANGELES', '12345-1234'); /* 입력성공 */
INSERT INTO us_snail_addy(city, postal) VALUES('LOS ANGELES', '12345-12345'); /* 입력실패 */
INSERT INTO us_snail_addy(city, postal) VALUES('LOS ANGELES', 'a2345-1234'); /* 입력실패 */
현재 접속한 Database 확인
현재 접속한 데이터베이스를 확인한다.
SELECT CURRENT_DATABASE();
현재 접속한 User 확인
현재 접속한 user 를 확인한다.
SELECT CURRENT_USER;
1.5. 스키마(Schema) 생성
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
where role_specification can be:
user_name
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER현재 데이터베이스에 새 스키마를 생성합니다.
스키마 이름은 현재 데이터베이스에 있는 기존 스키마의 이름과 달라야 합니다.
스키마는 본질적으로 네임스페이스입니다.
AUTHORIZATION절이 사용되는 경우 생성된 모든 객체가 해당 사용자의 소유라는 점을 제외하면 스키마를 생성한 후 실행되는 별도의 명령과 기본적으로 동일하게 처리됩니다 .
set search_path To postgres, public;
위와 같은 설정에서 테이블 명칭만 입력하면 DBMS 가 해당 테이블을 찾을 때
postgres ➔ public 순으로 찾게 됩니다.
PostgreSQL에서는 모든 연결이 하나의 데이터베이스에서만 작동합니다. 따라서 데이터베이스를 변경하려면 다른 데이터베이스를 지정하여 다시 연결해야 합니다. MySQL 에 존재하는 'use dbname' 명령어가 없습니다.
1.6. 계정(User) 생성
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SYSID uid
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP groupname [, ...]
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'abstime'
name : 계정명
SYSID : SYSID 절을 사용하여 새 사용자의 PostgreSQL 사용자 ID를 선택할 수 있습니다. 일반적으로 필요하지 않지만 고아 개체( orphaned object)의 소유자를 다시 만들어야 하는 경우 유용할 수 있습니다.
이를 지정하지 않으면 할당된 가장 높은 사용자 ID에 1을 더한 값(최소 100)이 기본값으로 사용됩니다.
CREATEDB | NOCREATEDB :
데이터베이스 생성 권한을 정의합니다. default는 NOCREATEDB입니다.
CREATEUSER | NOCREATEUSER
데이터베이스 생성 권한을 정의합니다. default는 NOCREATEUSER 입니다.
IN GROUP groupname
사용자의 그룹 이름입니다. 여러 그룹 이름이 나열될 수 있습니다.
PASSWORD
사용자의 비밀번호를 설정합니다. 비밀번호 인증을 사용할 계획이 없다면 이 옵션을 생략할 수 있지만, 비밀번호 인증으로 전환하기로 결정하면 사용자는 연결할 수 없습니다. 비밀번호는 나중에 ALTER USER를 사용하여 설정하거나 변경할 수 있습니다.
ENCRYPTED | UNENCRYPTED
비밀번호가 시스템 카탈로그에 암호화되어 저장되는지 여부를 제어합니다. (두 가지 모두 지정되지 않은 경우 매개변수 password_encryption에 의해 결정됩니다.) 제시된 비밀번호 문자열이 이미 MD5 암호화 형식인 경우 ENCRYPTED 또는 UNENCRYPTED가 지정되었는지 여부에 관계없이 있는 그대로 암호화되어 저장됩니다. 시스템은 지정된 암호화된 비밀번호 문자열을 해독할 수 없습니다). 이를 통해 덤프/복원 중에 암호화된 비밀번호를 다시 로드할 수 있습니다.
VALID UNTIL : 비밀번호가 더 이상 유효하지 않게 되는 시간을 설정합니다. 생략하면 비밀번호는 항상 유효합니다.
--Create User
CREATE USER my_user_test WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'xxxxxxx';
-- Grant connect to my data base
GRANT CONNECT ON DATABASE my_db_test TO my_user_test;
-- Grant usage the schema
GRANT USAGE ON SCHEMA my_sch_test TO my_user_test ;
-- Grant all table for SELECT, INSERT, UPDATE, DELETE
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_sch_test TO my_user_test;
1.7. 권한 부여
schema에 읽기 권한 주기
GRANT USAGE ON SCHEMA public TO <계정명>;
schema의 모든 table에 읽기 권한 주기
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;
2. SQL 언어
2.1. 소개
2.2. 개념
2.3. 새 테이블 만들기
2.4. 행으로 테이블 채우기
2.5. 테이블 쿼리
2.6. 테이블 간 조인
2.7. 집계 함수
2.8. 업데이트
2.9. 삭제
3. 고급 기능
3.1. 소개
3.2. 뷰
3.3. 외래 키
3.4. 트랜잭션
3.5. Window 함수
3.6. 계층
3.7. 결론
