2021-12-01 DB

ansunny1170·2021년 12월 1일
PostgreSQLUVCWSLdblinuxsqlubuntu
0

UVC BOOTCAMP.DB

목록 보기
2/4

복습

나는 CMD를 이용해서 DB에 접근했다.
어제 했던 것 처럼 psql에 로그인, 어제 추가했던 DB명을 확인하기위해 \l 입력.
다행히 book_store DB가 남아있다 ^^ 휴~
\c DB 체인지 명령문으로 book_store로 변경하고, SELECT 문으로 해당 DB를 참조한다.

  • \dt 데이터 테이블 조회
  • \l 데이터 베이스 조회
  • SELECT *FROM [DB명] DB명에 해당하는 데이터 조회
    *은 칼럼명, develop_book;는 TABLE
  • DB TABLE 만들때,
    CREATE TABLE develop_book (
    book_id INTEGER,
    date DATE,
    name VARCHAR(80)
    );

SQL 실습

CRUD(CREATE, READ, UPDATE, DELETE)

CREATE, READ

'',""의 차이

  • INSERT INTO develop_book VALUES(2,'2019-12-25','"자바"');
    이렇게 입력하면 name 칼럼에 "자바"로 데이터 저장
  • INSERT INTO develop_book VALUES(2,'2019-12-25','"자바"');
    이렇게 입력하면 사진과 같은 에러가 발생한다.


psql 에서 ""안의 내용을 칼럼(column)으로 인식

  • INSERT INTO develop_book VALUES(2,'2019-12-25','''자바''');
    이렇게 입력하면 name 칼럼에 '자바'로 데이터 저장

Data 입력 순서 설정

  • INSERT INTO develop_book (book_id,date,name) VALUES(3,'2020-01-03','HTML/CSS');
    이렇게 입력하면 내가 어떤 순서로 data를 입력할지 선언(?),설정 가능하다. bold처리한 data들은 순서를 바꿀 수 있다.
    여기까지 했다면 다시 DATA를 확인해보자.
    SELECT*FROM develop_book;

  • INSERT INTO develop_book (date,book_id,name) VALUES('2020-01-03','3','HTML/CSS');
    이렇게 순서를 바꿔서 입력해보자. 야호!
    data 자료형과 data 매칭을 안하면 자료형이 안 맞다고 에러가 발생한다.
    그러나 자료형이 맞으면 잘못 들어가겠지!

  • INSERT INTO develop_book VALUES(4,'2020-01-24','Python');
  • INSERT INTO develop_book VALUES(5,'2020-02-04','c언어');
  • INSERT INTO develop_book VALUES(6,'2020-02-15','c++');


위와같이 문장씩 칠 수 있지만, 아래와같이 한번에 넣을 수도 있다.

  • INSERT INTO develop_book VALUES
    (4,'2020-01-24','Python'),
    (5,'2020-02-04','c언어'),
    (6,'2020-02-15','c++');

데이터 조회

LIMIT 반환하는 로우(row)의 개수를 지정
OFFSET 반환하는 로우(row)의 시작지점을 지정
ORDER BY 반환하는 로우(row)를 정렬할 때 사용
WHERE 지정한 로우(row)만 조회 되도록 필터 기능

  • LIMIT
    SELECT*FROM develop_book
    Limit 5;
    오타가 잘난다.. 설정한 LIMIT 개수에 따라 조회값이 다르다.

  • OFFSET
    SELECT*FROM develop_book
    Limit 6
    OFFSET 1;


COM의 메모리 번지는 0부터 시작하기 때문에 OFFSET 1로 설정하면 2번째 부터 시작하게된다.

  • ORDER BY
    SELECT*FROM develop_book
    ORDER BY book_id ASC;
    book_id를 오름차순으로 정리했다!
    내림차순으로 참조하는 것은 일회성이다.
    ※오름차순 영문 : ascending order

SELECT*FROM develop_book
ORDER BY book_id DESC;
book_id를 내림차순으로 정리했다!
내림차순으로 참조하는 것도 일회성이다.
※내림차순 영문 : descending order

SELECT*FROM develop_book
ORDER BY date, name;
두가지 동시에 정력 가능하며,
date를 먼저 정리하고, name을 정리한다. 그리고 차순에 대한 정의가 없다면.
default로 설정되는데 ORDER BY는 ASC이다.

SELECT date, name FROM develop_book
ORDER BY 1,2;
SELECT date, name FROM develop_book
ORDER BY 2,1;
해당 명령은 이해를 잘해야 한다.
SELECT할때 순서를 date, name으로 했기때문에 자동지정으로 date=1,name=2가 된다.
여기서 ORDER BY 할 순서로 2,1를 했기 때문에 name먼저 정리 후, date정리를 두번째로 정리한다.

  • ORDER BY 번외
    SELECT date, name FROM develop_book
    ORDER BY book_id ASC, name DESC;

데이터 조회 WHERE

  • 비교 연산자
    /= 같다
    <> 다르다
    /> 크다
    < 작다
    />= 크거나 같다
    <= 작거나 같다

  • =
    SELECT*FROM develop_book
    WHERE book_id = 1;

  • <>
    SELECT*FROM develop_book
    WHERE book_id <> 1;

  • >
    SELECT*FROM develop_book
    WHERE book_id > 3;

  • <
    SELECT*FROM develop_book
    WHERE book_id < 1;
  • >=
    SELECT*FROM develop_book
    WHERE book_id >= 1;
  • <=
    SELECT*FROM develop_book
    WHERE book_id <= 1;

서브쿼리

  • WHERE
    SELECT*FROM develop_book
    WHERE '2020-01-03' = (
    SELECT date FROM develop_book
    WHERE book_id = 3
    );
    뭔가 원하는 방향으로 안나온다.

SELECT date FROM develop_book
WHERE book_id = 3;

SELECT*FROM develop_book
WHERE '2020-01-03' = '2020-01-03';
뭔가 원하는 방향으로 안나온다.22

SELECT*FROM develop_book
WHERE date = '2020-01-03';

SELECT*FROM develop_book
WHERE date = (
SELECT date FROM develop_book
WHERE book_id = 3
);
여전히 이상한걸?333

SELECT*FROM develop_book
WHERE date = (
SELECT date FROM develop_book
WHERE book_id = 2
);
아래 결과와 같이 2개 data에 대해서 오류가 난것이다.

UPDATE

데이터 수정

UPDATE 테이블명
SET 컬럼명 = 바꿀 데이터 내용
WHERE 수정할 로우의 조건
RETURNING *; --수정한 내용 바로조회
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
UPDATE develop_book
SET name = 'Java'
WHERE book_id = 2
RETURNING *;
name칼럼을 Java로 바꿀 것이다. 그런데 조건이 있다. 'WHERE book_id = 2'
book_id 가 2인 data만 원한다.
그리고 방금한 작업에 대한 결과를 보고싶다면 'RETURNING *' 을 사용한다.

데이터 테이블을 다시 조회 해보니 방금 UPDATE한 data가 맨 마지막에 위치해 있다.

AS 명령어

  • SELECT name AS title FROM develop_book;
    name을 title로 변경

  • CREATE TABLE develop_book_2 AS
    SELECT*FROM develop_book
    ORDER BY book_id ASC;
    데이터 베이스를 백업하였다.


  • DROP TABLE develop_book;
    develop_book을 삭제한다.

ALTER TABLE develop_book_2
RENAME TO develop_book;
develop_book_2를 develop_book으로 수정한다.
데이터 테이블 까지 확인해본다.

DELETE

데이터 삭제

DELETE FROM 테이블명 WHERE 컬럼명 = 삭제할 데이터의 컬럼 값;
DELETE FROM developbook WHERE book_id = 6;
※ 삭제할 데이터의 컬럼 값을 안넣으면!! 모두 지워진다._

DELETE FROM 테이블명; 으로 끝내면 테이블에 있는 모든 데이터가 삭제된다.
실습위해 다시한번 백업을 만들고 develop_book_2를 지워보았다.

pgAdmin4

psql(쉘) 혹은 CMD를 이용하여 DB에 접근했었다.
이번에는 pgAdmin4를 이용한 방법을 보인다.
나는 공부중이니 postgres로 비번을 설정하겠다.

진입 화면.


※설정이 필요하다면, 브라우저의 server에 우클릭 -> create -> server 후 아래와 같이 설정, 나는 할 필요 없다.

  • 내 DB를 선택한후 query tool을 누른다.
    ※쉘 명령어는 사용 불가.
  • 아래와 같이 Schemas -> public -> tables 에서 내 DB 확인 가능

  • pgAdmin4를 사용하여 지금까지 했던 Create, Insert, select, 등을 실습해보자.
    실행버튼 F5, 내가 실습한 내용들은 히스토리에 남는다.
    ※브라우저의 DATABASE 우클릭 -> REFRESH 누르면 새로고침 된다.

실습

1. community_board 이름의 데이터 베이스 만들기

2. user이름의 테이블 만들기

칼럼명 : id, 자료형 INTEGER
칼럼명 : user_id, 자료형 VARCHAR(80)
칼럼명 : user_pw, 자료형 VARCHAR(12)
칼럼명 : register_date, 자료형 DATE

3. board 이름의 테이블 만들기

칼럼명 : id, 자료형 INTEGER
칼럼명 : board_user, 자료형 INTEGER
칼럼명 : register_date, 자료형 DATE
칼럼명 : title, 자료형 VARCHAR(30)
칼럼명 : description, 자료형 VARCHAR(3000)
칼럼명 : likes, 자료형 INTEGER
칼럼명 : image_name, 자료형 VARCHAR(50)

CREATE DATABASE community_board;

CREATE TABLE users (
	id INTEGER,
    users_id VARCHAR(80),
    users_pw VARCHAR(12),
    register_date DATE
);

CREATE TABLE board (
    id INTEGER,
    board_user INTEGER,
    register_date DATE,
    title VARCHAR(30),
    description VARCHAR(3000),
	likes INTEGER,
	image_name VARCHAR(50)
);



CMD에서도 확인가능하다

4. users 테이블에 data 입력하기

아래 data를 참조하여 users 테이블에 입력하자
id, user_id, user_pw, register_date

1, Carveinus, car1234, 2020/04/23
2, Jenna, kk3375, 2020/07/12
3, Wlfur, fur0022, 2020/08/31

INSERT INTO users VALUES
(1,'Carveinus','car1234','2020/04/23'),
(2,'Jenna','kk3375','2020/07/12'),
(3,'Wlfur','fur0022', '2020/08/31');


제대로 들어갔는지 확인

5. board 테이블에 data 입력하기

아래 data를 참조하여 board 테이블에 입력하자
id, title, description, image_name, register_date, board_user

1, Developer's essay, Perhaps the reason we develop is because of the sense of accomplishment when we create something useful, , 2020-05-02, 1
2, Why the earth is round, I took a picture myself from space and saw that the earth is round, er.png, 2020-09-28, 3
3, Coffee time, I had a vanilla latte this afternoon at the blue signboard cafe on the boulevard., coffee.jpeg, 2020-07-13, 2
4, Chicken is inefficient, This is because fried chicken is more expensive than other chicken dishes., , 2020-08-14, 2
5, When bothering, Let's get someone else to work., , 2020-06-22, 1

insert into board values 
(1,1,'2020-05-02','Developer''s essay','Perhaps the reson we develop is because of the sense of accomplishment when we create something useful','0',''),
(2,3,'2020-09-28','Why the earth is round', 'I took a picture myself from space and saw that the earth is round','0','er.png'),
(3,2,'2020-07-13','Coffee time', 'I had a vanilla latte this afternoon at the blue signboard cafe on the boulevard.','0','coffee.jpeg'),
(4,2,'2020-08-14','Chicken is inefficient', 'This is because fried chicken is more expensive than other chicken dishes.','0',''),
(5,1,'2020-06-22','When bothering', 'Let''s get someone else to work.','0','');


6. Carveinus 유저의 비밀번호를 4321로 변경

UPDATE users
  SET user_pw = '4321'
  WHERE user_id = 'Carveinus'
RETURNING *;

7. When bothering 게시글 삭제하기

DELETE FROM develop_book WHERE book_id = 6;

상식 : 행,열 / 로우,레이블,칼럼

열칼럼
행으로 부르는 가로를이라고 부르는 세로를이라고 부른다
row라고 부르기도하며
레이블이라고 부르기도 한다

데이터 타입

숫자형

INTEGER

나타낼 수 있는 수의 법위와 저장용량 사이의 밸런스가 적절함 일반적으로 많이 씀, 숫자 길이 제한 불가.

NUMERIC(p,q)

소수점 자리 표시 가능, p=전체 자릿수, q=소수점 자릿수
예) numeric(6) 면 6자리 숫자만 입력 가능 100,000

FLOAT

부동소수점을 사용, REAL 또는 DOUBLE PRECISION으로 인식

SERIAL

INTEGER기본 값으로 1씩 추가되며

문자형

VARCHAR(n)

CHAR(n)

TEXT

날짜 및 시간

TIMESTAMP

2020-01-09 19:00:00.000

TIMESTAMPTZ(TZ : TIME ZONE)

2020-01-09 19:00:00.000(GMT +9)

DATE

2020-01-09

TIME

19:00:00

TIME WITH TIME ZONE

19:00:00.000(GMT +9)

데이터 타입 실습 - 1

1. timezone 데이터베이스 만들기

CREATE DATABASE timezone;

2. datetime_study table 만들기

create table datetime_study (
type_ts timestamp,
type_tstz timestamptz,
type_date date,
type_time time
);

3. data 입력하기

insert into datetime_study(type_ts,type_tstz,type_date,type_time)
values(
	'2020-07-26 20:00:25+09',
	'2020-07-26 20:00:25+09',
	'2020-07-26',
	'18:00:00'
);


4. show TIMEZONE;

show timezone;

set TIMEZONE = 'America/Los_Angeles';
show timezone;


timezone이 미국 LA로 설정되어 type_ts 와 type_tstz의 시차가 16시 인것을 알 수 있다.
전세계 서비스 서버라면 UTC로 설정후 각 유저에 따라 TZ를 설정할 것이다.

timezone string 참조
(참조 : https://en.wikipedia.org/wiki/List_of_tz_database_time_zones)

불리언형(bool,boolen)

TRUE

true, yes, on, 1

FALSE

false, no, off, 0

Null

알 수 없는 정보 또는 일부 불확실

JSON형

  • JSON? '속성-값' 쌍 또는 '키-값' 쌍으로 이루어진 데이터 오브젝트를 전달하기 위해 인간이 읽을 수 있는 텍스트를 사용하는 개방형 표준 포맷
  • Object 오브젝트?
    객체 또는 오브젝트는 메모리에 할당된 프로그램에서 사용되는 데이터

JSON

입력한 텍스트의 정확한 사본 저장 입력이 비교적 빠르지만 처리 속도가 느림

  • JSON 구조
    {"키 값":"밸류 값"}
    우리는 밸류 값을 사용하기 위해 이 구조를 사용한다.
    예){"1":"A"} 로 선언하고 JSON{1} 하면 A가 나온다.

    {"키 값":"밸류 값", "키 값":{"키 값":"밸류 값"}}

JSONB

텍스트를 이진 형태로 분해 후 저장 입력이 비교적 느리지만 처리속도가 빠름

  • JSONB 구조
    JSON을 나열 해준다?
    [{"키 값":"밸류 값",{"키 값":"밸류 값"}}]
    [{"키 값":"밸류 값","키 값":"밸류 값",{"키 값":"밸류 값"}}]
    [] : 배열
    {} : 객체

데이터 타입 실습 -2

1. Table 생성 및 data 입력

create table develop_book_order(
id NUMERIC(3),
order_info JSON NOT NULL
);

INSERT INTO develop_book_order(
values
(001,'{"customer":"Jaejin","books":{"product":"product1","qty":2}}'),
(002,'{"customer":"Yunsang","books":{"product":"product2","qty":3}}'),
(003,'{"customer":"Sojung","books":{"product":"product3","qty":1}}')
)

키 값 1: customer
키 값 2: book
키 값 3: qty

2. DATABASE 'SHOP' 만들기

3. 4개의 TABLE 만들기

  • 제품정보
    제품일련번호(5)
    제품명(40)
    생산일자
    제품가격
    공장번호(7)
CREATE TABLE item_info (
	item_id VARCHAR(5),
	item_name VARCHAR(40),
	production_date DATE,
	item_price INTEGER,
	factory_num VARCHAR(7)
);

강사님코드
CREATE TABLE prod_info(
prod_no NUMERIC(5),
prod_name VARCHAR(40),
prod_date DATE,
prod_price MONEY,
fact-no NUMERIC(7)
);
  • 공장정보
    공장번호(7)
    공장명(45)
    위치(도시)(25)
    책임자(40)
    직통전화(11)
    출하제품
    설립일자
CREATE TABLE factory_info (
	factory_num VARCHAR(7),
	factory_name VARCHAR(45),
	place_city VARCHAR(25),
	manager VARCHAR(40),
	hotline VARCHAR(11),
	shipment_product VARCHAR,
	founding_date DATE	
);
강사님코드
CREATE TABLE factory (
fact_no NUMERIC(7),
fact_name VARCHAR(45),
city VARCHAR(25),
fact_admin VARCHAR(40),
fact_tel NUMERIC(11),
prod_name VARCHAR[],
estab_date DATE
  • 상품주문
    주문번호(6)
    고객아이디(8)
    주문상품(40)
    수량(1000)
    상품가격
    주문일시
CREATE TABLE item_order (
	order_num VARCHAR(6),
	user_id VARCHAR(8),
	item_order VARCHAR(40),
	item_qty VARCHAR(1000),
	item_price INTEGER,
	order_date DATE
);
강사님코드
CREATE TABLE prod_order (
ord_no NUMERIC(6),
cust_id CHAR(8),
prod_name VARCHAR(40),
qty NUMERIC(1000),
prod_price MONEY,
ord_date TIMESTAMPTZ
);
  • 고객
    고객아이디(8)
    고객명(40)
    전화번호(11)
    이메일(100)
    생년월일(6)
    신원확인여부
CREATE TABLE users (
	user_id VARCHAR(8),
	user_name VARCHAR(40),
	phone_num VARCHAR(11),
	email VARCHAR(100),
	birth_date INTEGER,
	identity_verification BOOL
);
강사님코드
CREATE TABLE prod_info (
cust_id CHAR(8),
cust_name VARCHAR(40),
cust_tel NUMERIC(11),
email VARCHAR(100),
birth NUMERIC(6),
identify BOOLEAN
);

SELECT FROM item_info;
SELECT FROM factory_info;
SELECT FROM item_order;
SELECT FROM users;

CAST 연산자

data 타입을 변환해준다.(자료형 변환)

CAST(표현식 AS 바꿀 데이터) // 표현식 :: 바꿀 데이터 타입
SELECT CAST('3000' AS INTEGER); == SELECT '3000'::INTEGER; // 등호를 기준으로 양쪽 문법은 동일한 기능을 가진다.

3000이라는 문자열을 integer형으로 바꿨다.

데이터 무결성

데이터베이스 내에 정확하고 유효한 데이터만을 유지시키는 속성
CRUD 수행 시 데이터 값을 일관되고 정확하게 두지하고자 하는것
예) 금액 칼럼에는 문자 및 기호를 입력 할 수 없게 한다.

지키면 좋은점
응용프로그램에서 추가적인 작업을 할 필요가 없다.
단 요즘 트랜드는 FRONT 단에서 제약을 걸고 DB에서는 최소한으로 한다.

무결성 제약 조건

  1. 개체 무결성
    모든 테이블이 프라이머리 키(PK)를 가져야 하며
    프라이머리 키로 선택된 칼럼은 고유하고 null값을 허용하지 않아야 한다.
    데이터를 구분하는 고유한 정보

  2. 참조 무결성
    외래 키(FK) 값이 빈(NULL)값이거나 참조된 테이블의 기본 키 값(PK거나 유일한)과 동이해야 한다.

  3. 범위 무결성
    사용자가 정의한 도메인(domain) 내에서 관계형 데이터베이스의 모든 열을 저의 하도록 규정
    사용자 화면에서 제약조건을 거는 경우가 많음
    예) 휴대폰 번호 입력시 숫자만, 비밀번호 입력시 특수문자 제외 등

데이터 타입 실습-3

NOT NULL

빈(null) 값을 허용하지 않음

  • 아래와 같이 DATABASE 만들고 (GUI사용 시)

  • TABLE 만들고

create table contact_info(
cont_id NUMERIC(3) not null,
	name VARCHAR(15) not null,
	tel INTEGER[] not null,
	Email VARCHAR not null
	);
  • insert로 data 넣어보자
insert into contact_info(Email) values
('example@naver.com');

UNIQUE

유일한 값을 가져야 하는 조건

TO BE CONTINUE... 내일 계속 합시다!

profile
ansunny1170
공정 설비 개발/연구원에서 웹 서비스 개발자로 경력 이전하였습니다. Node.js 백엔드 기반 풀스택 개발자를 목표로 하고 있습니다.
이전 포스트

2021-11-30 DB

다음 포스트

2021-12-02 DB

0개의 댓글