postgres실습,docker

C:\Users\User>createdb -U postgres function_example
암호:

C:\Users\User>ipconfig

psql -U postgres -d function_example -f C:/Users/User/Desktop/postgres/function_example.dump 

created -Upostgres

cmd로 postgres암호설정

postgres=# \c function_example
접속정보: 데이터베이스="function_example", 사용자="postgres".
function_example=# drop table develop_book;
DROP TABLE
function_example=# \dt
릴레이션(relation) 목록

스키마 |       이름        |  종류  |  소유주
--------+-------------------+--------+----------
public | assumption_amount | 테이블 | postgres
public | division_by_zero  | 테이블 | postgres
public | exception         | 테이블 | postgres
public | real_amount       | 테이블 | postgres
public | student_score     | 테이블 | postgres
public | timebox           | 테이블 | postgres
(6개 행)

psql-쉘

function_example=# \l
데이터베이스 목록

이름       |  소유주  | 인코딩 |     Collate      |      Ctype       |      액세스 권한
------------------+----------+--------+------------------+------------------+-----------------------
book_store       | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
community_board  | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
function_example | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
postgres         | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
template0        | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
|          |        |                  |                  | postgres=CTc/postgres
template1        | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 | =c/postgres          +
|          |        |                  |                  | postgres=CTc/postgres
(6개 행)

논리연산

A B A A AND B A OR B NOT 
불리언식 표현식 - 모두 ture false

논리 비교연산

where절이 투르일때만 결과
select * from example_table where bool_col = ‘false’
SELECT NULL =’false’ AS equal_false, NULL IS FALSE AS is_false;

null false

5,9만 트루
SELECT * FROM example_table WHERE BETWEEN 1 AND 9;→구문오류
SELECT * FROM example_table WHERE NOT num_col BETWEEN 1 AND 9 ;-컬럼명을 써야함

(0159100
159 not x
0 100 not 
select date ‘2020-07-15’ + integer ‘7’ AS result;

→일수가 올라감

+time ‘13:00” →"2020-07-15 13:00:00”

select date '2020-07-15' - date '2020-07-01' AS result;

→ 14

CURRENT_DATE - 현재 날짜 정보 반환 
time 
timestamp
localtime
local_time_timestamp

EXISTS서브쿼리 -로우가 존재하면 참이다

SELECT * FROM real_amount
WHERE EXISTS(
SELECT * FROM assumption_amount);

SELECT * FROM real_amount
WHERE EXISTS(
    SELECT * FROM exception);

SELECT * FROM real_amount
WHERE 10 <= ALL(
SELECT amount FROM assumption_amount);

패턴매칭 연산
조회할 컬럼명 LIKE ‘패턴’;
특정 값 like 패턴

where phone like ‘iphone’
SELECT * FROM real_amount
WHERE name Like 'bana__'

-바나나 한개

SELECT * FROM real_amount
WHERE name Like '%'

-전부 출력

SELECT

‘pink’ LIKE ‘_in_’
‘pink’ LIKE ‘p%k’,
‘pink LIKE ‘p__’

POSIX 정규식

id체크 정규식 : 숫자 영문만 입력가능

case

when<조건문1> then<결과문1>
SELECT id,
name,
score,
CASE
when score <= 100 AND score >=90 THEN 'A'
when score <= 89 AND score >=80 THEN 'B'
when score <= 79 AND score >=70 THEN 'C'
when score <= 70 THEN 'F'
END grade
FROM student_score;

-조건에 맞게 결과 나옴

JSON {키값1: 밸류값1} 

‘주력언어에서 배열 인덱스 검색’

SELECT절과 GROUP BY절에 
C:\Users\User>psql -U postgres -d union_example -f C:/Users/User/Desktop/postgres/union_example.dump

UION

두개의 쿼리문으로 하나의 테이블을 만드는 명령어
두 sql은 서로 컬럼의 개수가 동일해야함

같은 위치에는 동일한 형식괄 의미의 데이터

(SELECT name, quantity,shelf_life
FROM drink)

UNION ALL
(
SELECT name, quantity, shelf_life
FROM ramen);

UNION vs UNION ALL

SELECT rating.user_id, rating.rating, ramen.name, rame.quqntity, ramen.is_spicy

FORM rating, ramen WHERE [ramen.id] = rating.item_id

CREATE TABLE left_table(
id  integer,
left_contents VARCHAR);

INSERT INTO left_table VALUES
(1,'가'),
(2,'나'),
(3,'다'),
(5,'라');

CREATE TABLE right_table
id INTEGER,
right_contents VARCHAR);

INSERT INTO right_table VALUES
(1,'a')
(2,'b')
(3,'c')

데이터 모델링

이름과 생년월일, 점수를 가진다
게시판 이름을 가진다
게시판 작성가능 제목 과 내용 조쇠수
사용자당 1씩 다시 게시글을 읽어도조회수가 오르지 않는다

Docker Hub

docker run -dit -p 8888:8000 --name fastapi-test python:3.9.13-slim-bullseye

docker run -dit -p 8888:8000 --name [containner name] python:3.9.13-slim-bullseye

로컬에 자길 컨테이너를 만들기때문에

C:\Users\User>docker ps -a
CONTAINER ID   IMAGE                         COMMAND     CREATED         STATUS                       PORTS     NAMES
9637256a3986   python:3.9.13-slim-bullseye   "python3"   5 minutes ago   Exited (137) 4 minutes ago             fastapi-test

C:\Users\User>docker start 963

docker ps

C:\Users\User>docker ps
CONTAINER ID   IMAGE                         COMMAND     CREATED          STATUS          PORTS                    NAMES
9637256a3986   python:3.9.13-slim-bullseye   "python3"   13 minutes ago   Up 10 seconds   0.0.0.0:8888->8000/tcp   fastapi-test

Usage: docker [OPTIONS] COMMAND

A self-sufficient runtime for containers

Options:
--config string      Location of client config files (default
"C:\\Users\\User\\.docker")
-c, --context string     Name of the context to use to connect to the
daemon (overrides DOCKER_HOST env var and
default context set with "docker context use")
-D, --debug              Enable debug mode
-H, --host list          Daemon socket(s) to connect to
-l, --log-level string   Set the logging level
("debug"|"info"|"warn"|"error"|"fatal")
(default "info")
--tls                Use TLS; implied by --tlsverify
--tlscacert string   Trust certs signed only by this CA (default
"C:\\Users\\User\\.docker\\ca.pem")
--tlscert string     Path to TLS certificate file (default
"C:\\Users\\User\\.docker\\cert.pem")
--tlskey string      Path to TLS key file (default
"C:\\Users\\User\\.docker\\key.pem")
--tlsverify          Use TLS and verify the remote
-v, --version            Print version information and quit

Management Commands:
builder     Manage builds
buildx*     Docker Buildx (Docker Inc., v0.8.2)
compose*    Docker Compose (Docker Inc., v2.7.0)
config      Manage Docker configs
container   Manage containers
context     Manage contexts
extension*  Manages Docker extensions (Docker Inc., v0.2.8)
image       Manage images
manifest    Manage Docker image manifests and manifest lists
network     Manage networks
node        Manage Swarm nodes
plugin      Manage plugins
sbom*       View the packaged-based Software Bill Of Materials (SBOM) for an image (Anchore Inc., 0.6.0)
scan*       Docker Scan (Docker Inc., v0.17.0)
secret      Manage Docker secrets
service     Manage services
stack       Manage Docker stacks
swarm       Manage Swarm
system      Manage Docker
trust       Manage trust on Docker images
volume      Manage volumes

Commands:
attach      Attach local standard input, output, and error streams to a running container
build       Build an image from a Dockerfile
commit      Create a new image from a container's changes
cp          Copy files/folders between a container and the local filesystem
create      Create a new container
diff        Inspect changes to files or directories on a container's filesystem
events      Get real time events from the server
exec        Run a command in a running container
export      Export a container's filesystem as a tar archive
history     Show the history of an image
images      List images
import      Import the contents from a tarball to create a filesystem image
info        Display system-wide information
inspect     Return low-level information on Docker objects
kill        Kill one or more running containers
load        Load an image from a tar archive or STDIN
login       Log in to a Docker registry
logout      Log out from a Docker registry
logs        Fetch the logs of a container
pause       Pause all processes within one or more containers
port        List port mappings or a specific mapping for the container
ps          List containers
pull        Pull an image or a repository from a registry
push        Push an image or a repository to a registry
rename      Rename a container
restart     Restart one or more containers
rm          Remove one or more containers
rmi         Remove one or more images
run         Run a command in a new container
save        Save one or more images to a tar archive (streamed to STDOUT by default)
search      Search the Docker Hub for images
start       Start one or more stopped containers
stats       Display a live stream of container(s) resource usage statistics
stop        Stop one or more running containers
tag         Create a tag TARGET_IMAGE that refers to SOURCE_IMAGE
top         Display the running processes of a container
unpause     Unpause all processes within one or more containers
update      Update configuration of one or more containers
version     Show the Docker version information
wait        Block until one or more containers stop, then print their exit codes

Run 'dock
```er COMMAND --help' for more information on a command.

To get more help with docker, check out our guides at [https://docs.docker.com/go/guides/](https://docs.docker.com/go/guides/)

 Remote containers ATTACh running container

\

root@9637256a3986:~# ls
root@9637256a3986:~# touch [test.py](http://test.py/)
root@9637256a3986:~# ls
[test.py](http://test.py/)
root@9637256a3986:~#

root@9637256a3986:~# pip3 install uvicorn

root@9637256a3986:~# uvicorn test:app --reload

https://velog.io/@cho876/요즘-뜨고있다는-FastAPI

파이썬 다운로드

ping www.naver.com

Ping www.naver.com.nheos.com [223.130.200.107] 32바이트 데이터 사용:

C:\Users\User>ping www.naver.com

Ping www.naver.com.nheos.com [223.130.200.107] 32바이트 데이터 사용:

223.130.200.104→www.naver.com

ip address : domain name

127.0.0.1

→1~6xxxxx

hello world

```python
# test.py

from fastapi import FastAPI  # FastAPI import

app = FastAPI()

@app.get("/")
def printHello():
	return "Hello World"

@app.get("/json")
def printJson():
	return {
		"Number" : 12345
	}

container(python container)
python program

local host - 가상 네트워크

C:\Users\User>docker run -dit -p 5555:5432 --name postgres postgres

C:\Users\User>docker run -dit -p 5555:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres --name postgres postgres
851b977bbb127b9483cf190676f6bceec61f8b1c0af3d7d5cbed89aa4754bbc3

create table psyco_test (num integer);