DBeaver로 postgreSQL 사용하기

두번째 tab의 show all Database 체크하고 확인

복습

NOT NULL 제약 조건 사용한 TABLE 만들어 보기

test_not_null 만든다.

• contact_info TABLE 만들었었다.

JSON vs JSONB

JSON, JSON[], JSONB, JSONB[](B : BLOB Binary large object)

공부

데이터 타입

NOT NULL

UNIQUE

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

DROP TABLE IF EXISTS contact_info;

create table contact_info(
	cont_id NUMERIC(3) UNIQUE NOT NULL,
	name VARCHAR(40) not null,
	tel INTEGER[] not null,
	email VARCHAR
);

• UNIQUE 제약 조건 넣기 전에 테스트 해봅시다!

INSERT INTO contact_info values

(001, 'name', Array[1,2], 'example@naver.com'),
(001, 'name', Array[1,2], 'example@naver.com'),
(001, 'name', Array[1,2], 'example@naver.com');

위와 같이 실행하게 되면 cont_id가 UNIQUE제약 조건에 걸렸기 때문에 ERROR가 발생할 것이다.

프라이머리 키

보통 PK로 표현
PK값은 서로 달라야 하며(UNIQUE) 빈 값을 허용 하지 않는다.

DROP TABLE IF EXISTS contact_info;

create table contact_info(
	cont_id SERIAL PRIMARY KEY,
	name VARCHAR(15) not null,
	tel INTEGER[] not null,
	Email VARCHAR
);

INSERT INTO contact_info(name, tel, Email) values

('name', Array[1,2], 'example@naver.com'),
('name', Array[1,2], 'example@naver.com'),
('name', Array[1,2], 'example@naver.com');

※SERIAL 자료형을 사용하였기 때문에 자동적으로 1씩 증가하는 값이 들어간다!!

이제 cont_id가 1인 놈을 지워보자

외래 키

보통 FK로 표현
다른 테이블의 고유한 값(UNIQUE)을 참조
|과목|테이블|
|---|:---:
|과목_id|numeric|
|과목_name|varchart|

교사 정보 테이블
교사_id numeric
교사_name varchar
과목_id numeric
임용일자

CREATE TABLE guamock (
guamock_id NUMERIC(5) PRIMARY KEY,
guamock_name VARCHAR(20)
);

CREATE TABLE teach_info (
teach_id NUMERIC(5),
teach_name VARCHAR(20),
guamock_id NUMERIC(5) PRIMARY KEY
);

참조되는 테이블(부모 테이블), 참조하는 테이블(자식 테이블)
1. 부모 테이블이 자식 테이블보다 먼저 생성되어야 한다.
2. 부모 테이블은 자식 테이블과 같은 데이터 타입을 가져야 한다.
3. 부모 테이블에서 참조 된 컬럼의 값만 자식 테이블에서 입력 가능하다.
4. 참조되는 칼럼은 모두 프라이머리 키이거나 UNIQUE제약조건 형식이어야 한다.
아래와 같이 입력해보자.

INSERT INTO guamock
values(
00001,'mathematics'),
(00002,'science'),
(00003,'programming');

CREATE TABLE teac (
teac_id NUMERIC(5),
teac_name VARCHAR(20),
guamock_id NUMERIC(5) references guamock,
teac_certifi_date DATE
);

OR

CREATE TABLE teac (
teac_id NUMERIC(5),
teac_name VARCHAR(20),
subj_id NUMERIC(5) references guamock(guamock_id),
teac_certifi_date DATE
);

우선 내 코드로 미리 짜봤는데 REFERENCES 라는 조건문이 없었기 때문에 새로 만들었다.

아래와 같이 실행 했으나 에러가 난다.
앞서서 우리는 guamock table에 3개의 data만 입력했다.
이번에 4번째 data는 참조 가능한 data가 없었기 때문에 error가 난다.

INSERT INTO teac values (00011, '정선생', 00001, '2017-03-11');
INSERT INTO teac values (00021, '홍선생', 00002, '2017-04-12');
INSERT INTO teac values (00031, '박선생', 00003, '2017-04-13');
INSERT INTO teac values (00041, '한선생', 00004, '2018-05-20');

또다른 외래 키 설정 방법

• UNIQUE 제약 조건을 설정하는 방법
  ※ teac table 부터 삭제해야 한다!!! 그다음 guamock이다.
  아마 참조 해제를 먼저 해야하는 것이 아닐까?

DROP TABLE teac;
DROP TABLE guamock;

CREATE TABLE subject(
	subj_id numeric(5) not null primary key,
	subj_name varchar(60) not null,
	stud_count numeric(20) not null,
	unique (subj_id, subj_name)
	);
INSERT INTO subject
values 
(00001, 'mathematics',60),
(00002, 'science', 42),
(00003, 'programming',70);

• 참조하는 다른 방법

CREATE TABLE teacher (
teac_id numeric(5) not null primary key,
teac_name varchar(20) not null,
subj_code numeric(5) not null,
	subj_name varchar(60) not null,
	teac_certifi_date date not null,
	FOREIGN KEY (subj_code, subj_name) REFERENCES subject (subj_id, subj_name)
	);

※해석 FOREIGN KEY라는 제약 조건을 거는데 참조하는 컬럼이 무엇이냐?
teacher TABLE에서 제약 조건을 걸건데 제약 조건을 걸려고 하는 대상이 부모 TABLE로 부터 참조 하는 대상이기 때문에

참조 테이블의 삭제

테이블이 삭제되는 것을 ON DELETE 라고함
부모 테이블은 자식 테이블 보다 먼저 삭제 될 수 없음
즉 subject TABLE을 삭제하려면 teacher TABLE을 먼저 삭제 해야 한다는 것이ㅏㄷ.

ON DELETE의 5가지 유형

1. ON DELETE NO ACTION - 지우면 안되는 경우(삭제 못하게 막는 명령어)
2. ON DELETE RESTRICT - 지우면 안되는 경우(삭제 못하게 막는 명령어)
3. ON DELETE SET NULL - 지워야 하는 경우(삭제는 하되 삭제시 NULL로 설정)
4. ON DELETE CASCADE - 지워야 하는 경우(삭제는 하되 삭제시 자식 칼럼 같이 삭제)
5. ON DELETE SET DEFAULT - 지워야 하는 경우(삭제는 하되 삭제시 미리 설정한 DEFAULT 값으로 설정)

테스트 하기 위해 teacher TABLE에 data를 넣고 지울 것이다.

insert into teacher
values
(00011, '정선생', 00001, 'mathematics', '2018-03-11'),
(00021, '홍선생', 00002, 'science', '2017-04-12'),
(00031, '박선생', 00003, 'programming', '2018-04-13');

select*from teacher;

먼저 부모 TABLE을 지웠는데 아래와 같이 error가 발생했다.

ON DELETE CASCADE

DROP TABLE teacher;
DROP TABLE subject;

CREATE TABLE subject(
	subj_id numeric(5) not null primary key,
	subj_name varchar(60) not null
);
INSERT INTO subject
values 
(00001, 'mathematics'),
(00002, 'science'),
(00003, 'programming');

CREATE TABLE teacher (
teac_id numeric(5) not null primary key,
teac_name varchar(20) not null,
subj_id numeric(5) REFERENCES subject ON DELETE CASCADE,
teac_certifi_date date
);

insert into teacher
values
(00011, '정선생', 00001, '2018-03-11'),
(0021, '홍선생', 00002, '2017-04-12'),
(00031, '박선생', 00003, '2018-04-13');

왜 teacher 홍선생에 00021이 안들어가나????? 일단 0021 넣었다.

자이제 지워보자, 결과를 보니 부모 TABLE을 먼저 지웠더니 자식 TABLE에서 참조하는 칼럼이 같이 지워졌다.!!

delete from subject where subj_id = 00002;
select * from teacher;

ON DELETE SET NULL

DROP TABLE teacher;
DROP TABLE subject;

CREATE TABLE subject(
	subj_id numeric(5) not null primary key,
	subj_name varchar(60) not null
);
INSERT INTO subject
values 
(00001, 'mathematics'),
(00002, 'science'),
(00003, 'programming');

CREATE TABLE teacher (
teac_id numeric(5) not null primary key,
teac_name varchar(20) not null,
subj_id numeric(5) REFERENCES subject ON DELETE SET NULL,
teac_certifi_date date
);

insert into teacher
values
(00011, '정선생', 00001, '2018-03-11'),
(0021, '홍선생', 00002, '2017-04-12'),
(00031, '박선생', 00003, '2018-04-13');

delete from subject where subj_id = 00002;
select * from teacher;

SET NULL 이름에서 추측 할 수 있듯이 참조하던 부모 칼럼이 삭제 되었다면 해당 칼럼을 NULL로 설정 하는 명령어이다.

ON DELETE SET DEFAULT

DROP TABLE teacher;
DROP TABLE subject;

CREATE TABLE subject(
	subj_id numeric(5) not null primary key,
	subj_name varchar(60) not null
);
INSERT INTO subject
values 
(00001, 'mathematics'),
(00002, 'science'),
(00003, 'programming');

CREATE TABLE teacher (
teac_id numeric(5) not null primary key,
teac_name varchar(20) not null,
subj_id numeric(5) DEFAULT 1 REFERENCES subject ON DELETE SET DEFAULT,
teac_certifi_date date
);

insert into teacher
values
(00011, '정선생', 00001, '2018-03-11'),
(0021, '홍선생', 00002, '2017-04-12'),
(00031, '박선생', 00003, '2018-04-13');

SET DEFAULT를 사용해 보았다.
코드와 같이 미리 1로 미리 DEFAULT 시킨 값으로 설정이 됨을 알 수 있다.

CHECK

CHECK 명령어 뒤에 나오는 식이 불리언 타입의 True를 만족 해야함.

CREATE TABLE order_info (
order_no INTEGER NOT NULL PRIMARY KEY,
cust_name VARCHAR(100),
price MONEY,
order_qty INTEGER CHECK (order_qty >0));

insert into order_info
values
(1, '볶음밥',3000, 1),
(2, '익룡고기',3000, -1);

이렇게 입력을 했더니 error가 발생하는군, 안다 알면서 넣었다.
money 자료형은 앞에 \ 기호가 들어감을 알 수 있다.

둘중 하나만 들어가냐? 아니다. 둘다 못들어 갔다.

제약조건 실습-1

1. country 이름의 테입르 만들기
   칼럼명 : country_cd(PK) SERIAL 국가코드
   칼럼명 : country_name VARCHAR(30) 국가명
   칼럼명 : region VARCHAR(30) 대륙
2. 칼럼명 : user_pk(PK) SERIAL 유저 PK
   칼럼명 : user_id VARCHAR(30) 유저아이디
   칼럼명 : user_pw VARCHAR(16) 유저패스워드
   칼럼명 : user_age NUMERIC(3) 연령
   칼럼명 : register_date DATE 가입일자
   칼럼명 : country_cd(FK) SERIAL 국가코드

CREATE TABLE country
(
country_cd serial primary key,
country_name varchar(30),
region varchar(30)
);

CREATE TABLE users
(
user_pk serial primary key,
	user_id varchar(30),
	user_pw varchar(16),
	user_age numeric(3),
	register_date date,
	country_cd serial references country(country_Cd)
);

※연령에 check 명령문을 쓰면 좋겠다!
※id는 UNIQUE 명령문을 쓰면 좋겠다!
※reference country 까지만 적어도 된다. 그러나 칼럼명이 부자간 서로 다르면 꼭 다 써줘야 한다.

제약조건을 GUI로 확인 가능하다.

짜여진 코드를 GUI로 확인 가능하다.

Alter Table

컬럼 추가

ALTER TABLE 테이블명
ADD COLUMN 컬럼명 데이터 타입 제약조건;
이미 추가되었는 TABLE 에서 COLUMN을 추가해보자.

CREATE TABLE book_info (
id integer not null primary key,
name varchar(20) not null
);

insert into book_info values(1,'POSTGRESQL'),(2,'MONGODB');

alter table book_info
add Column published_date date;

select*from book_info;

아래와 같이 나왔다.

alter table book_info
add column null_test date not null;

※여기서 눈여겨 볼 것은
①최초 table 구성하며 추가했던 컬럼에 data를 넣기 전이라면 alter사용하여 not null 컬럼을 add할 수 있다.
②만약 table 구성하며 추가했던 컬럼에 data를 넣었다면 alter 사용하여 not null 컬럼을 add할 수 없다.

NOT NULL 제약조건을 추가(변경)하고 싶다.!

1. NOT NULL 제약 조건 없이 컬럼 생성
2. 컬럼 값 수정
3. NOT NULL 제약조건 추가

• 우선 data가 NULL인 컬럼을 일정 값으로 입력한다.
  이미 published_date라는 not null제약 조건 없는 컬럼을 생성한 상태이다.
  모든 데이터를 수정했다.
  insert도 가능하겠지만 update 명령문을 사용하자.

update book_info set published_date = '2018-01-01' where id = 1
returning *;

• 이미 존재하는 컬럼에 조건을 추가하는 방법
  ALTER TABLE 테이블명
  ALTER COLUMN 컬럼명 SET 셋팅하고자하는 조건;
  성공~

alter table book_info
alter column published_date set not null;

NOT NULL 제약 조건과 DEFAULT 값 설정

되었다.

ALTER TABLE book_info
ADD COLUMN null_test date default '2020-01-03' not null;
select*from book_info;

insert into book_info values(3,'','2020-11-17');

null_test 컬럼을 삭제해 보았다.

alter table book_info
drop column null_test;

삭제하려는 칼럼을 참조하는 칼럼이 있을 경우

아래처럼 지우려 했는데 error가 발생했다.

drop table if exists book_info;

create table book_info (
book_id integer not null primary key,
book_name varchar(20) not null unique
);

insert into book_info values
(1, 'POSTGRESQL'),
(2, 'MONGODB');

CREATE TABLE library (
id integer not null primary key,
name varchar(40) not null,
book_name varchar(20) not null references book_info(book_name)
);

insert into library values
(1, '국립도서관','POSTGRESQL');

ALTER TABLE book_info DROP COLUMN book_name;

• 마지막에 CASCADE문을 추가하여 다시 해보자(ON DELETE CASCADE)

drop table if exists book_info;

create table book_info (
book_id integer not null primary key,
book_name varchar(20) not null unique
);

insert into book_info values
(1, 'POSTGRESQL'),
(2, 'MONGODB');

CREATE TABLE library (
id integer not null primary key,
name varchar(40) not null,
book_name varchar(20) not null references book_info(book_name)
);

insert into library values
(1, '국립도서관','POSTGRESQL');

ALTER TABLE book_info DROP COLUMN book_name CASCADE;

사진을 보면 book_name_fkey 즉 제약조건이 삭제됨을 확인할 수 있다.

컬럼명 변경

ALTER TABLE 테이블명
RENAME 존컬럼명 TO 새컬럼명

ALTER TABLE 테이블명
RENAME COLUNM 기존컬럼명 TO 새컬럼명

1. COLUMN 명령어는 생략 가능
2. 존재하지 않는 컬럼 이름 수정 불가
3. 한번에 한 컬럼의 이름만 수정 가능

• library TABLE의 name을 lib_name으로 변경해보자

ALTER TABLE library
RENAME name TO lib_name;

컬럼명 변경 참조가 되어있는 컬럼이 있다면?

참조가 되어있는 부모의 컬럼의 이름을 변경했다면...
자동으로 바뀐 커럼을 참조하도록 수정되어있다.

DROP TABLE IF EXISTS library;
DROP TABLE iF EXISTS book_info;

create table book_info (
book_id integer not null primary key,
book_name varchar(20) not null unique
);

insert into book_info values
(1, 'POSTGRESQL'),
(2, 'MONGODB');

CREATE TABLE library (
	id integer not null primary key,
	name varchar(40) not null,
	book_name varchar(20) not null references book_info(book_name)
);

insert into library values
(1, '국립도서관','POSTGRESQL');

ALTER TABLE book_info RENAME book_name TO name;
select * from book_info;

아래 사진은 컬럼명 변경후 제약조건도
book_name -> 현재 테이블 컬럼
name & public.book_info -> 참조 대상

한번더 컬럼명을 수정한다면?
최초 book_name과 변경된 name1111111만 남아있다.

컬럼명을 수정하는 다른방법

아래사진 순서대로 조작하면 문법까지 확인 가능하다.

PK, FK 추가

PK 추가
ALTER TABLE 테이블이름
ADD PRIMARY KEY (칼럼이름);
FK 추가
ALTER TABLE 테이블이름
ADD FOREIGN KEY (칼럼이름1) REFERENCES 부모테이블(칼럼이름2);

PK 추가하는 실습

DROP TABLE IF EXISTS book;

create table book (
id integer not null,
name varchar(20) not null
);

alter table book
add primary key (id)

FK 추가하는 실습

DROP TABLE IF EXISTS library;

create table library(
lib_id integer not null primary key,
lib_name varchar(30) not null,
book_id integer not null
);

alter table library
add foreign key (book_id) references book (id);

데이터 타입 변경

데이터 타입 변경 실습

ALTER TABLE 테이블 이름
ALTER COLUMN 컬럼이름 TYPE 새로운 데이터 타입,
ALTER COLUMN 컬럼이름 SET DATA TYPE 새로운 데이터 타입;
아래와 같이 만들고 나서

create table water (
id smallint not null primary key,
name text not null,
location_no varchar not null,
description text
);

insert into water values(01,'천지','02','백두산 천지');

아래와 같이 데이터 타입(자료형) 변경을 해보았다.

alter table water
alter column id type integer,
alter column description type varchar;

alter table water
alter column location_no type integer;

형 변환 후 데이터 타입 변환

USING 컬럼이름::새로운데이터 타입

ALTER TABLE water
ALTER COLUMN location_no TYPE INTEGER USING location_no::INTEGER;

변경이 되는 data가 있고, 안되는 data가 있다.
되는것 : '01'::INTEGER
안되는것 : '안녕'::INTEGER

연산자/함수

논리,비교 연산자

• 실습

drop table if exists example_table;

create table example_table (
bool_col boolean
);
	
insert into example_table
values
(true),
(false),
(NULL);

select*from example_table;

select*from example_table where bool_col = 'false';
select*from example_table where bool_col is FALSE;
둘다 가능하다.
'false' 문자열 처럼 보이겠지만 boolen의 false다.

• !=을 사용해 봤다.. NULL은 안나오는군!

select*from example_table where bool_col !='false';

• IS NOT을 사용해 봤다.. NULL이 나오는군!
  select*from example_table where bool_col is NOT FALSE;

논리연산 및 AS사용하여 컬럼명 변경

• 어려운 내용
  SELECT NULL = 'false' AS equal_false, NULL IS FALSE AS is_false;

범위 연산

SELECT * FROM example_table where BETWEEN 1 AND 9;
SELECT * FROM example_table where NOT BETWEEN...

날짜 및 시간 연산

• DATE +DATE = DATE

SELECT date '2020-07-15' + integer '7' AS result;

• DATE + TIME = TIMESTAMP

SELECT date '2020-07-15' + time '13:00' AS result;

• DATE - DATE = INTEGER
  SELECT date '2020-07-15' - date '2020-07-01' AS result;

날짜 및 시간 함수

• CURRENT_DATE 현재 날짜 정보 반환 SELECT CURRENT_DATE;
  
• CURRENT_TIME 현재 시간 + 시간대 정보 반환 SELECT CURRENT_TIME(2);
• CURRENT_TIMESTAMP 현재 날짜 및 시간 + 시간대 정보 반환 SELECT CURRENT_TIMESTAMP(2);
• LOCALTIME 현재 시간 정보 반환 (로컬) SELECT LOCALTIME(2);
• LOCALTIME_TIMESTAMP 현재 날짜 및 시간정보 반환(로컬) SELECT LOCALTIMESTAMP;

서브쿼리 연산

파일형식DB(.dump)를 추가하기

• 실습데이터 다운로드
  https://github.com/BJPUBLIC/postgresql
  아래와 같이 준비한다.
  
  
  
  .dump파일을 DB에 추가하였다. (파일형식으로 DB를 추가한 것임)
  
• error 유형
  확인해보니 처음부터 funcion 이라고 오타가 나서 이렇게 되었다.
  
  

서브쿼리 연산 실습

SELECT * FROM real_amount;

SELECT * FROM assumption_amount;

• EXISTS
  SELECT * FROM real_amount where exists ( select * from assumption_amount);

SELECT * FROM real_amount where exists ( select * from exception);
  

• IN
  SELECT * FROM real_amount where amount IN ( 10, 20, 30);

SELECT * FROM real_amount where amount IN ( 10, 20);
  

• NOT IN
  SELECT * FROM real_amount where amount NOT IN ( 10, 20, 30);

SELECT * FROM real_amount where amount IN ( 10, 20);
  

• ANY
  SELECT * FROM real_amount where 10 = ANY (SELECT amount FROM assumption_amount);

SELECT * FROM real_amount where 11 = ANY (SELECT amount FROM assumption_amount);
  

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

SELECT * FROM real_amount where 11 <= ALL (SELECT amount FROM assumption_amount);
  

패턴매칭 연산 많이씀

조회할 컬럼명 LIKE '패턴';
특정 값 LIKE '패턴';

패턴?
% -> 모든 문자열
_ -> 문자열 하나

WHERE phone LIKE 'iphone';

'pink' LIKE '_in_, _에 a~z까지 조회후 -> true로 리턴
'pink' LIKE 'p%k', %에 a~z까지의 문자열 조합 조회후 -> true로 리턴
'pink' LIKE 'p__' 글자수가 안맞네 -> false로 리턴

연산자	동일 연산자
LIKE	~~
NOT LIKE	!~~
ILIKE	~~*	대소문자 ㄴㄴ
NOT ILIKE	!~~*	대소문자 ㄴㄴ

SIMILAR TO 연산자

LIKE와 유사하나 POSIX 정규식을 따름

CASE 함수

SELECT * FROM student_score;

COALESCE 함수

COALESCE(<매개변수1>, <매개변수2>, ...)

• 매개변수
  파라미터 혹은 매개변수 , 함수(등과 같은 서브루틴)에 인픗으로 제공되는 여러 데이터 중 하나

SELECT COALESCE(null, null,'빈 값') AS column;

배열연산

JSON 연산

월요일.....

CMD를 이용한 실습

ALTER와 UPDATE 명령어의 차이점은?

ALTER 및 UPDATE는 SQL 의 두 가지 수정 명령이다. ALTER는 데이터베이스의 관계 (테이블) 구조를 수정하는 데 사용된다. UPDATE 명령은 데이터베이스 관계에 저장된 데이터를 수정하는 데 사용된다. ALTER 및 UPDATE 명령의 기본적인 차이점은 ALTER 명령은 데이터 정의 언어 명령 이고 UPDATE 명령은 데이터 조작 언어 명령이다.

출처 (참조 : https://ko.gadget-info.com/difference-between-alter)

매뉴얼에서 제약조건 보는방법